Aproveitando...
Hoje sim, emiti as duas primeiras notas, com XML gerado totalmente pelo MySQL.
Até agora era parcial, hoje foi total.
Esse fuso horário foi um dos problemas, que eu me esqueci dele.

Moderador: Moderadores
SELECT
CPNOME,
ze_ContabilSaldoExercicio( IDCTCONTA, '2021-12-31', '2021-12-31' ) AS SDANT,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-01-01','2022-01-31') AS DEB01,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-01-01','2022-01-31') AS CRE01,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-01-31','2021-12-31' ) AS ACU01,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-01-31', '2021-12-31' ) AS EXE01,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-02-01','2022-02-28') AS DEB02,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-02-01','2022-02-28') AS CRE02,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-02-28','2021-12-31' ) AS ACU02,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-02-28', '2021-12-31' ) AS EXE02,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-03-01','2022-03-31') AS DEB03,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-03-01','2022-03-31') AS CRE03,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-03-31','2021-12-31' ) AS ACU03,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-03-31', '2021-12-31' ) AS EXE03,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-04-01','2022-04-30') AS DEB04,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-04-01','2022-04-30') AS CRE04,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-04-30','2021-12-31' ) AS ACU04,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-04-30', '2021-12-31' ) AS EXE04,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-05-01','2022-05-31') AS DEB05,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-05-01','2022-05-31') AS CRE05,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-05-31','2021-12-31' ) AS ACU05,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-05-31', '2021-12-31' ) AS EXE05,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-06-01','2022-06-30') AS DEB06,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-06-01','2022-06-30') AS CRE06,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-06-30','2021-12-31' ) AS ACU06,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-06-30', '2021-12-31' ) AS EXE06,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-07-01','2022-07-31') AS DEB07,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-07-01','2022-07-31') AS CRE07,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-07-31','2021-12-31' ) AS ACU07,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-07-31', '2021-12-31' ) AS EXE07,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-08-01','2022-08-31') AS DEB08,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-08-01','2022-08-31') AS CRE08,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-08-31','2021-12-31' ) AS ACU08,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-08-31', '2021-12-31' ) AS EXE08,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-09-01','2022-09-30') AS DEB09,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-09-01','2022-09-30') AS CRE09,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-09-30','2021-12-31' ) AS ACU09,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-09-30', '2021-12-31' ) AS EXE09,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-10-01','2022-10-31') AS DEB10,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-10-01','2022-10-31') AS CRE10,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-10-31','2021-12-31' ) AS ACU10,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-10-31', '2021-12-31' ) AS EXE10,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-11-01','2022-11-30') AS DEB11,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-11-01','2022-11-30') AS CRE11,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-11-30','2021-12-31' ) AS ACU11,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-11-30', '2021-12-31' ) AS EXE11,
ze_ContabilSaldoDebito( IDCTCONTA, '2022-12-01','2022-12-31') AS DEB12,
ze_ContabilSaldoCredito( IDCTCONTA, '2022-12-01','2022-12-31') AS CRE12,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-12-31','2021-12-31' ) AS ACU12,
ze_ContabilSaldoExercicio( IDCTCONTA,'2022-12-31', '2021-12-31' ) AS EXE12
FROM JPCTCONTA
WHERE IDCTCONTA = 5
/*
PCONTSALDO - CONSULTA AOS VALORES DO PLANO DE CONTAS
1991.04 - José Quintas
*/
#include "inkey.ch"
#include "josequintas.ch"
PROCEDURE pContSaldo
LOCAL nIdCtConta := 0, nCont, nAnoAtual, GetList := {}, cnSQL := ADOLocal(), dInicial, dFinal
LOCAL nFechaMes, nFechamento, dFechamento, dFechaExe
@ 2, 2 SAY "Ano. . . .:"
@ 4, 2 SAY "Conta. . .:"
@ 6, 2 SAY "--mes--"
@ 6, 10 SAY " " + Padc( "Débitos", 21, "-" )
@ 6, 32 SAY " " + Padc( "Créditos", 21, "-" )
@ 6, 54 SAY " " + Padc( "Saldo ExercÃcio", 21, "-" )
@ 6, 76 SAY " " + Padc( "Saldo PerÃodo", 21, "-" )
@ 7, 2 SAY "DEZ/"
@ 9, 2 SAY "JAN/"
@ 11, 2 SAY "FEV/"
@ 13, 2 SAY "MAR/"
@ 15, 2 SAY "ABR/"
@ 17, 2 SAY "MAI/"
@ 19, 2 SAY "JUN/"
@ 21, 2 SAY "JUL/"
@ 23, 2 SAY "AGO/"
@ 25, 2 SAY "SET/"
@ 27, 2 SAY "OUT/"
@ 29, 2 SAY "NOV/"
@ 31, 2 SAY "DEZ/"
nAnoAtual := 0
DO WHILE .T.
Mensagem( "F9 pesquisa, ESC sai" )
@ 2, 13 GET nAnoAtual PICTURE "9999" VALID nAnoAtual > 2000
@ 4, 13 GET nIdCtConta PICTURE "999999" VALID JPCTCONTAClass():New():Valida( @nIdCtConta )
READ
Mensagem()
IF LastKey() == K_ESC
EXIT
ENDIF
Altd()
WITH OBJECT cnSQL
:Execute( "SELECT EMFECHA FROM JPEMPRESA WHERE IDEMPRESA=1" )
nFechamento := :Number( "EMFECHA" )
:CloseRecordset()
dFechaExe := Stod( StrZero( nAnoAtual - 1, 4 ) + "1231" )
:cSQL := "SELECT CPNOME," + ;
"ze_ContabilSaldoExercicio( IDCTCONTA, " + DateSQL( dFechaExe ) + ", " + DateSQL( dFechaExe ) + " ) AS SDANT," + hb_Eol()
FOR nCont = 1 TO 12
dInicial := Stod( StrZero( nAnoAtual, 4 ) + StrZero( nCont, 2 ) + "01" )
dFinal := Last_Day( dInicial )
nFechaMes := Int( ( nCont - 1 ) / nFechamento )
IF nFechaMes == 0
dFechamento := Stod( StrZero( nAnoAtual - 1, 4 ) + "1231" )
ELSE
dFechamento := Last_Day( Stod( StrZero( nAnoAtual, 4 ) + StrZero( nCont, 2 ) + "01" ) )
ENDIF
:cSQL += "ze_ContabilSaldoDebito( IDCTCONTA, " + DateSQL( dInicial ) + "," + DateSQL( dFinal ) + ") AS DEB" + StrZero( nCont, 2 ) + "," + ;
"ze_ContabilSaldoCredito( IDCTCONTA, " + DateSQL( dInicial ) + "," + DateSQL( dFinal ) + ") AS CRE" + StrZero( nCont, 2 ) + "," + ;
"ze_ContabilSaldoExercicio( IDCTCONTA," + DateSQL( dFinal ) + "," + DateSQL( dFechaExe ) + " ) AS ACU" + StrZero( nCont, 2 ) + "," + ;
"ze_ContabilSaldoExercicio( IDCTCONTA," + DateSQL( dFinal ) + ", " + DateSQL( dFechamento ) + " ) AS EXE" + StrZero( nCont, 2 ) + ;
iif( nCont == 12, "", "," ) + hb_Eol()
NEXT
:cSQL += " FROM JPCTCONTA" + ;
" WHERE IDCTCONTA = " + NumberSQL( nIdCtConta )
:Execute()
@ 7, 6 SAY StrZero( nAnoAtual - 1, 4 )
@ 7, 52 SAY :Number( "SDANT" ) PICTURE PicVal(14,2)
@ 7, 74 SAY :Number( "SDANT" ) PICTURE PicVal(14,2)
FOR nCont = 1 TO 12
@ nCont * 2 + 7, 0 SAY ""
@ Row(), 6 SAY StrZero( nAnoAtual, 4 )
@ Row(), 10 SAY :Number( "DEB" + StrZero( nCont, 2 ) ) PICTURE PicVal(14,2)
@ Row(), 32 SAY :Number( "CRE" + StrZero( nCont, 2 ) ) PICTURE PicVal(14,2)
@ Row(), 52 SAY :Number( "ACU" + StrZero( nCont, 2 ) ) PICTURE PicVal(14,2)
@ Row(), 74 SAY :Number( "EXE" + StrZero( nCont, 2 ) ) PICTURE PicVal(14,2)
NEXT
ENDWITH
ENDDO
CLOSE DATABASES
RETURN
:ExecuteProcedure( "ze_ContabilBalancete", dInicial, dFinal, iif( nOpcResultado == 1, 0, 12 ), nOpcGrau, iif( nOpcResultado == 3, 1, 0 ) )
nIdPedido := cnSQL:ReturnFunction( "ze_PedidoDuplica", nIdPedido, Date(), LogInfo() )
cnSQL:ExecuteProcedureNoReturn( "ze_PedidoCalculo", nIdPedido )
Mario Mesquita escreveu:Quintas, então aquela função naquele Select enorme não está no programa e sim numa Stored Procedure do BD?
Poderoso mesmo...
CREATE FUNCTION ze_XmlNfeProduto( nIdItPed INT(11), nNumItem INT(11) )
RETURNS TEXT CHARSET latin1
PRODUTO: BEGIN
DECLARE cXml TEXT;
DECLARE nIdProduto, nSomenteIcms INT(11);
DECLARE nQtde DECIMAL(16,4);
DECLARE nValFre, nValSeg, nValDes, nValOut, nImpVal DECIMAL(16,2);
DECLARE nPreNot DECIMAL(16,5);
DECLARE cNome VARCHAR(60);
DECLARE cPedCom, cUnid, cGtin, cGtinTri, cNcm, cCest, cPedCli, cCfop VARCHAR(20);
SET cXml := '';
SELECT IPPRODUTO, IPQTDE, IPPRENOT, IPVALFRE, IPVALSEG, IPVALDES, IPVALOUT + IPVALEXT, IPCFOP, ze_SoNumeros( IPPEDCOM ), IPIMPVAL,
IENOME, IEUNID, IEGTIN, IEGTINTRI, IENCM, IECEST,
PDPEDCLI
FROM JPITPED
LEFT JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
LEFT JOIN JPNOTFIS ON NFPEDIDO = IDPEDIDO
LEFT JOIN JPITEM ON IDPRODUTO = IPPRODUTO
WHERE IDITPED = nIdItPed
INTO nIdProduto, nQtde, nPreNot, nValFre, nValSeg, nValDes, nValOut, cCfop, cPedCom, nImpVal,
cNome, cUnid, cGtin, cGtinTri, cNcm, cCest, cPedCli;
SET nSomenteIcms := IF( cCfop = '1.604' OR INSTR( cNome, ' DE ICMS' ) != 0, 1, 0 );
SET cXml := CONCAT( cXml,
'<det nItem="', nNumItem, '">',
'<prod>',
ze_XmlTag( 'cProd', LPAD( nIdProduto, 6, '0' ) ),
ze_XmlTag( 'cEAN', ze_XmlNfeGtin( cGtin ) ),
ze_XmlTag( 'xProd', cNome ),
ze_XmlTag( 'NCM', cNcm ),
IF( LENGTH( cCest ) = 0, '', ze_XmlTag( 'CEST', cCest ) ),
ze_XmlTag( 'CFOP', ze_SoNumeros( cCFOP ) ),
ze_XmlTag( 'uCom', LEFT( CONCAT( cUnid, '.' ), 6 ) ),
ze_XmlTag( 'qCom', nQtde ),
ze_XmlTag( 'vUnCom', IF( nSomenteIcms = 1, 0.00, nPreNot ) ),
ze_XmlTag( 'vProd', IF( nSomenteIcms = 1, 0.00, ROUND( nQtde * nPreNot, 2 ) ) ),
ze_XmlTag( 'cEANTrib', ze_XmlNfeGtin( cGtinTri ) ),
ze_XmlTag( 'uTrib', LEFT( CONCAT( cUnid, '.' ), 6 ) ),
ze_XmlTag( 'qTrib', nQtde ),
ze_XmlTag( 'vUnTrib', IF( nSomenteIcms = 1, 0.00, nPreNot ) ),
IF( nValFre = 0, '', ze_XmlTag( 'vFrete', nValFre ) ),
IF( nValSeg = 0, '', ze_XmlTag( 'vSeg', nValSeg ) ),
IF( nValDes = 0, '', ze_XmlTag( 'vDesc', nValDes ) ),
IF( nValOut = 0, '', ze_XmlTag( 'vOutro', nValOut ) ),
ze_XmlTag( 'indTot', '1' ) );
PRODUTOIMPORTACAO: BEGIN
SET cXml := CONCAT( cXml, '' );
END PRODUTOIMPORTACAO;
SET cXml := CONCAT( cXml,
IF( LENGTH( cPedCli ) = 0, '', ze_XmlTag( 'xPed', cPedCli ) ),
IF( LENGTH( cPedCom ) = 0, '', ze_XmlTag( 'nItemPed', cPedCom ) ),
IF( 1 = 1, '', ze_XmlTag( 'cBenef', '' ) ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutoveiculo( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutomedicamento( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutoarmamento( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlNfeprodutocombustivel( nIdItPed ) );
SET cXml := CONCAT( cXml, '</prod>' );
SET cXml := CONCAT( cXml, '<imposto>', IF( nImpVal = 0, '', ze_XmlTag( 'vTotTrib', nImpVal ) ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutoicms( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutoipi( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutoii( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutopis( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_xmlnfeprodutocofins( nIdItPed ) );
SET cXml := CONCAT( cXml, ze_XmlNfeProdutoISS( nIdItPed ) );
SET cXml := CONCAT( cXml, '</imposto>', '</det>' );
RETURN cXml;
END PRODUTO;
FUNCTION LoadBoletoSetup( oBolSetup, nIdPortador )
LOCAL cnSQL := ADOLocal()
WITH OBJECT cnSQL
:Execute( "SELECT * FROM JPTABFINPOR WHERE IDFINPOR=" + NumberSQL( nIdPortador ) + ;
" UNION ALL SELECT * FROM JPTABFINPOR WHERE FINPORBANCO=" + NumberSQL( nIdPortador ) )
oBolSetup:nBanco := :Number( "FINPORBANCO" )
oBolSetup:nJuros := :Number( "FINPORJUROS" )
oBolSetup:nAgencia := :Number( "FINPORAGENCIA" )
oBolSetup:nConta := :Number( "FINPORCONTA" )
oBolSetup:nCarteira := :Number( "FINPORCARTEIRA" )
oBolSetup:nMulta := :Number( "FINPORMULTA" )
oBolSetup:nBancario := :Number( "FINPORBANCARIO" )
oBolSetup:nRemessa := :Number( "FINPORREMESSA" ) + 1
oBolSetup:nEspecie := :Number( "FINPORNESPECIE" )
oBolSetup:cBolEspecie := :String( "FINPORCESPECIE", 2 )
nIdPortador := :Number( "IDFINPOR" )
:CloseRecordset()
ENDWITH
IF nIdPortador == 0
oBolSetup:nBanco := 341
oBolSetup:nBancario := Val( LeCnf( "BOLETO NOSSO" ) )
oBolSetup:nJuros := Val( LeCnf( "BOLETO JUROS" ) )
oBolSetup:nAgencia := Val( LeCnf( "BOLETO AGENCIA" ) )
oBolSetup:nConta := Val( LeCNf( "BOLETO CONTA" ) )
oBolSetup:nCarteira := Val( LeCnf( "BOLETO CARTEIRA" ) )
oBolSetup:nCarteira := iif( oBolSetup:nCarteira == 0, 109, oBolSetup:nCarteira )
oBolSetup:nRemessa := 1
ENDIF
oBolSetup:nBancario += 1
RETURN nIdPortador
:Execute( "SELECT * FROM JPTABFINPOR WHERE IDFINPOR=" + NumberSQL( nIdPortador ) + ;
" UNION ALL SELECT * FROM JPTABFINPOR WHERE FINPORBANCO=" + NumberSQL( nIdPortador ) )
Retornar para Contribuições, Dicas e Tutoriais
Usuários vendo este fórum: Nenhum usuário registrado online e 20 visitantes