Mais outro.
Era assim em Harbour:
WITH OBJECT cnSQL
:ExecuteCmdProcedure( "ze_PedidoRelaciona", nIdPedido, nIdPedidoRel, ::cOpc )
/*
IF nIdPedidoRel == 0
:QueryCreate()
:QueryAdd( "PDPEDREL", StrZero( 0, 6 ) )
:QueryAdd( "PDNOTREL", StrZero( 0, 9 ) )
:QueryExecuteUpdate( "JPPEDIDO", "IDPEDIDO = " + NumberSQL( nIdPedido ) )
ELSE
:cSQL := "SELECT JPPEDIDO.*, JPITPED.*, JPNOTFIS.NFNOTFIS" + ;
" FROM JPPEDIDO" + ;
" LEFT JOIN JPITPED ON JPITPED.IPPEDIDO = JPPEDIDO.IDPEDIDO" + ;
" LEFT JOIN JPNOTFIS ON JPNOTFIS.NFPEDIDO = JPPEDIDO.IDPEDIDO" + ;
" WHERE IDPEDIDO = " + NumberSQL( nIdPedidoRel )
:Execute()
IF :Eof()
:QueryCreate()
:QueryAdd( "PDPEDREL", StrZero( 0, 6 ) )
:QueryAdd( "PDNOTREL", StrZero( 0, 9 ) )
:QueryExecuteUpdate( "JPPEDIDO", "IDPEDIDO = " + NumberSQL( nIdPedido ) )
ELSE
:QueryCreate()
:QueryAdd( "PDPEDREL", StrZero( nIdPedidoRel, 6 ) )
:QueryAdd( "PDNOTREL", StrZero( ADOField( "NFNOTFIS", "N", "JPNOTFIS", "NFPEDIDO = " + NumberSQL( nIdPedidoRel ) ), 9 ) )
:QueryExecuteUpdate( "JPPEDIDO", "IDPEDIDO=" + NumberSQL( nIdPedido ) )
IF ::cOpc == "I"
:QueryCreate()
:QueryAdd( "PDVALSEG", :Number( "PDVALSEG" ) )
:QueryAdd( "PDVALOUT", :Number( "PDVALOUT" ) )
:QueryAdd( "PDVALEXT", :Number( "PDVALEXT" ) )
:QueryAdd( "PDVALFRE", :Number( "PDVALFRE" ) )
:QueryAdd( "PDVALDES", :Number( "PDVALDES" ) )
:QueryExecuteUpdate( "JPPEDIDO", "IDPEDIDO = " + NumberSQL( nIdPedido ) )
DO WHILE ! :Eof()
:QueryCreate()
:QueryAdd( "IPPEDIDO", StrZero( nIdPedido, 6 ) )
FOR EACH cField IN { "IPPRODUTO", "IPTRIBUT" }
:QueryAdd( cField, StrZero( :Number( cField ), 6 ) )
NEXT
FOR EACH cField IN { "IPCFOP", "IPPEDCOM", "IPLEIS", "IPORIGEM", "IPIPIICM", ;
"IPIPICST", "IPIPIENQ", "IPICMCST", "IPDIFCAL", "IPPISCST", "IPPISENQ", ;
"IPCOFCST", "IPCOFENQ" }
:QueryAdd( cField, :String( cField ) )
NEXT
FOR EACH cField IN { "IPPRECUS", "IPPREPED", "IPQTDE", "IPVALCUS", "IPGARANTIA", ;
"IPPRENOT", "IPVALADI", "IPVALFRE", "IPVALSEG", "IPVALOUT", "IPVALEXT", ;
"IPVALADU", "IPVALIOF", "IPVALDES", "IPVALPRO", "IPVALNOT", "IPIIBAS", ;
"IPIIALI", "IPIIVAL", "IPIPIBAS", "IPIPIALI", "IPIPIVAL", "IPICMBAS", ;
"IPICMALI", "IPICMRED", "IPICMVAL", "IPFCPALI", "IPFCPVAL", "IPICSBAS", ;
"IPICSALI", "IPICSVAL", "IPSUBIVA", "IPSUBBAS", "IPSUBRED", "IPSUBALI", ;
"IPSUBVAL", "IPDIFBAS", "IPDIFALIF", "IPDIFALIU", "IPDIFALII", "IPDIFVALI", ;
"IPDIFVALF", "IPPISBAS", "IPPISALI", "IPPISVAL", "IPCOFBAS", "IPCOFALI", ;
"IPCOFVAL", "IPISSBAS", "IPISSALI", "IPISSVAL" }
:QueryAdd( cField, :Number( cField ) )
NEXT
:QueryExecuteInsert( "JPITPED" )
:MoveNext()
ENDDO
ENDIF
ENDIF
:CloseRecordset()
ENDIF
SubPedidoClass():CalculaValores( nIdPedido )
*/
:ExecuteCmdProcedure( "ze_PedidoCalculo", nIdPedido )
ENDWITH
Agora fica no servidor, assim em SQL
CREATE PROCEDURE ze_PedidoRelaciona( nIdPedido INT(11), nIdPedidoRel INT(11), cOpcao VARCHAR(5) )
THIS:BEGIN
IF nIdPedidoRel = 0 THEN
UPDATE
JPPEDIDO SET
PDPEDREL = '000000', PDNOTREL = '000000000'
WHERE IDPEDIDO = nIdPedido;
LEAVE THIS;
END IF;
IF ( SELECT IDPEDIDO FROM JPPEDIDO WHERE IDPEDIDO = nIdPedidoRel ) = 0 THEN
UPDATE
JPPEDIDO SET
PDPEDREL = '000000', PDNOTREL = '000000000'
WHERE IDPEDIDO = nIdPedido;
LEAVE THIS;
END IF;
UPDATE
JPPEDIDO SET
PDPEDREL = LPAD( nIdPedidoRel, 6, '0' ),
PDNOTFIS = LPAD( COALESCE( ( SELECT NFPEDIDO FROM JPNOTFIS WHERE NFPEDIDO = nIdPedidoRel ), 0 ), 9, '0' )
WHERE IDPEDIDO = nIdPedido;
IF cOpcao != "I" THEN
LEAVE THIS;
END IF;
UPDATE
JPPEDIDO
INNER JOIN JPPEDIDO AS B ON B.IDPEDIDO = nIdPedidoRel
SET
JPPEDIDO.PDVALSEG = COALESCE( B.PDVALSEG, 0 ),
JPPEDIDO.PDVALOUT = COALESCE( B.PDVALOUT, 0 ),
JPPEDIDO.PDVALEXT = COALESCE( B.PDVALEXT, 0 ),
JPPEDIDO.PDVALFRE = COALESCE( B.PDVALFRE, 0 ),
JPPEDIDO.PDVALDES = COALESCE( B.PDVALDES, 0 )
WHERE JPPEDIDO.IDPEDIDO = nIdPedido;
INSERT
INTO JPITPED
( IPPEDIDO, IPPRODUTO, IPTRIBUT,
IPCFOP, IPPEDCOM, IPLEIS, IPORIGEM, IPIPIICM,
IPIPICST, IPIPIENQ, IPICMCST, IPDIFCAL, IPPISCST, IPPISENQ,
IPCOFCST, IPCOFENQ,
IPPRECUS, IPPREPED, IPQTDE, IPVALCUS, IPGARANTIA,
IPPRENOT, IPVALADI, IPVALFRE, IPVALSEG, IPVALOUT, IPVALEXT,
IPVALADU, IPVALIOF, IPVALDES, IPVALPRO, IPVALNOT, IPIIBAS,
IPIIALI, IPIIVAL, IPIPIBAS, IPIPIALI, IPIPIVAL, IPICMBAS,
IPICMALI, IPICMRED, IPICMVAL, IPFCPALI, IPFCPVAL, IPICSBAS,
IPICSALI, IPICSVAL, IPSUBIVA, IPSUBBAS, IPSUBRED, IPSUBALI,
IPSUBVAL, IPDIFBAS, IPDIFALIF, IPDIFALIU, IPDIFALII, IPDIFVALI,
IPDIFVALF, IPPISBAS, IPPISALI, IPPISVAL, IPCOFBAS, IPCOFALI,
IPCOFVAL, IPISSBAS, IPISSALI, IPISSVAL )
SELECT
nIdPedido, IPPRODUTO, IPTRIBUT,
IPCFOP, IPPEDCOM, IPLEIS, IPORIGEM, IPIPIICM,
IPIPICST, IPIPIENQ, IPICMCST, IPDIFCAL, IPPISCST, IPPISENQ,
IPCOFCST, IPCOFENQ,
IPPRECUS, IPPREPED, IPQTDE, IPVALCUS, IPGARANTIA,
IPPRENOT, IPVALADI, IPVALFRE, IPVALSEG, IPVALOUT, IPVALEXT,
IPVALADU, IPVALIOF, IPVALDES, IPVALPRO, IPVALNOT, IPIIBAS,
IPIIALI, IPIIVAL, IPIPIBAS, IPIPIALI, IPIPIVAL, IPICMBAS,
IPICMALI, IPICMRED, IPICMVAL, IPFCPALI, IPFCPVAL, IPICSBAS,
IPICSALI, IPICSVAL, IPSUBIVA, IPSUBBAS, IPSUBRED, IPSUBALI,
IPSUBVAL, IPDIFBAS, IPDIFALIF, IPDIFALIU, IPDIFALII, IPDIFVALI,
IPDIFVALF, IPPISBAS, IPPISALI, IPPISVAL, IPCOFBAS, IPCOFALI,
IPCOFVAL, IPISSBAS, IPISSALI, IPISSVAL
FROM JPITPED
WHERE IPPEDIDO = nIdPedidoRel;
END
Fonte final Harbour:
WITH OBJECT cnSQL
:ExecuteCmdProcedure( "ze_PedidoRelaciona", nIdPedido, nIdPedidoRel, ::cOpc )
:ExecuteCmdProcedure( "ze_PedidoCalculo", nIdPedido )
ENDWITH