* ointroduceri_efactura.prg *!* 13.08.2024 *!* vizImportEFactura + discountfaratva Procedure importEfacturaPrimite DO vizImportEFactura WITH 'PRIMITE' ENDPROC PROCEDURE importEfacturaTrimise DO vizImportEFactura WITH 'TRIMISE' ENDPROC PROCEDURE vizImportEFactura LPARAMETERS tcTip * tcTIP: PRIMITE/TRIMISE Private poFacturi, poFacturiDetalii Local loFrmFacturi As "frm_efactura_import", lcTip, llPrimite Local lcData1, lcFiltru, lcFiltruOriginal, lcOrder, lcSchema, lcgroup, llAfiseaza, llModParam poFacturi = Null poFacturiDetalii = Null lcTip = IIF(TYPE('tcTip') = 'C' and !EMPTY(m.tcTip), UPPER(ALLTRIM(m.tcTip)), 'PRIMITE') llPrimite = (m.lcTip = 'PRIMITE') lcTabel = IIF(m.lcTip = 'PRIMITE', 'anaf_vefactura_primit', 'anaf_vefactura_trimis') * FACTURI PRIMITE/TRIMISE lcData1 = '01' + Padl(Int(m.gnLuna),2,'0') + Alltrim(Str(m.gnAn)) TEXT To lcSchema Noshow ales N(1), id N(20), id_fact N(20), data_act D, data_scad D, numar_act C(30), xfurnizor C(200), xclient C(200), partener C(200), id_incarcare C(36), id_descarcare c(36) null, tip_mesaj_raspuns C(50) null,mesaj_raspuns C(250) null, data_raspuns D null, trimis N(1) null, data_trimis T null, cod_fiscal C(30) null, cod_fiscal_emitent C(30) null, cod_fiscal_beneficiar C(30) null, detalii M null, total_fara_tva N(16,4), total_tva N(16,4), total_tva_ron N(16,4), total_cu_tva N(16,4), discount_fara_tva N(16,4), taxe_fara_tva N(16,4), valoare_fara_tva N(16,4), total_de_plata N(16,4), nume_valuta C(5), test N(1) null, jtotctva N(16,4), descriere M null, detalii_plata M null, diferenta N(16,4), procesat N(1), SerieActRoa V(10) Null, NrActRoa N(14) Null, IdPartRoa N(10) Null, PartenerRoa V(100) null, codFiscalROA V(20), idValutaROA N(5) null,numeValutaROA V(5) null, cursROA N(16,6) null,Cont c(4) Null,ACont c(4) Null,TVAIncasare N(1),Fdoc V(20) null,Id_Fdoc N(5) Null,Nrord V(100) null,Id_Lucrare I Null,Contract V(100) Null,Id_Ctr I Null,Sectie V(100) null,Id_Sectie I Null,dst_chlt V(100) null, Id_venchelt I NUll, nresp V(100) null, Id_responsabil I NUll , completat N(1), completatdet N(1), creditnote N(1), explicatiaROA V(100) null ENDTEXT TEXT To lcSelect TEXTMERGE Noshow SELECT 0 as ales, id, id_fact, data_act, data_scad, numar_act, xfurnizor, xclient, partener, id_incarcare, id_descarcare, tip_mesaj_raspuns, mesaj_raspuns, data_raspuns, NVL(trimis,0) as trimis, data_trimis, cod_fiscal, cod_fiscal_emitent, cod_fiscal_beneficiar, '' as detalii, total_fara_tva, total_tva, total_tva_ron, total_cu_tva, discount_fara_tva, taxe_fara_tva, valoare_fara_tva, total_de_plata, nume_valuta, test, jtotctva, descriere, detalii_plata, NVL(total_cu_tva, 0.00)-NVL(jtotctva, 0.00) as diferenta, procesat, '' as SerieActRoa, CAST(null as Number(14)) as NrActRoa, CAST(null as Number(20)) as idPartRoa, '' as PartenerRoa, '' as codFiscalROA, CAST(null as Number(10)) as idValutaROA, '' as numeValutaROA, CAST(null as Number(16,6)) as cursROA, '' as Cont, '' as acont, 0 as TVAIncasare, '' as Fdoc, CAST(0 as Number(5)) as Id_Fdoc, '' as Nrord, CAST(0 as Number(10)) as id_Lucrare, '' as Contract, CAST(null as Number(10)) as id_ctr, '' Sectie, CAST(0 as Number(10)) as id_Sectie, '' as dst_chlt, CAST(0 as Number(10)) as id_Venchelt, '' as nresp, CAST(0 as Number(10)) as id_responsabil, 0 as completat, 0 as completatdet, creditnote, '' as explicatiaROA FROM <> ENDTEXT lcOrder = [data_act,numar_act,data_raspuns] lcgroup = [] lcFiltru = [(extract(year from data_act) = ?gnAn and extract(month from data_act) = ?gnLuna) or (data_act is null and data_raspuns >= to_date('] + m.lcData1 + [','ddmmyyyy'))] lcFiltruOriginal = [] llModParam = .T. llAfiseaza = .F. gencursor('poFacturi', 'crsFacturi', lcSelect, lcFiltru, lcSchema, lcOrder, llAfiseaza, lcgroup, llModParam, lcFiltruOriginal) poFacturi.ca_baza1.afisare() Select crsFacturi Go Top * DETALII FACTURI PRIMITE/TRIMISE/EMISE TEXT To lcSchema Noshow distribuie N(1), id_tip N(10) null, id_articol N(20) null, id_gestiune N(20) null, cont C(4) null, acont C(4) null, id N(20), id_efactura N(20), nr N(5), articol V(250), descriere V(250), detalii M, cantitate N(18,6), um V(20), um_iso V(50), um_roa V(50), pret N(20,6), proctva N(7,2), tiptva V(2), valoarefaratva N(20,6), discountfaratva N(20,6), in_stoc N(1), articol_roa V(250), codmat_roa V(50), codbare V(50), codclient V(100), codfurnizor V(100), codcpv V(50), codnc8 V(50), nume_gestiune V(50), cgest V(20), tva N(20,6), pretv N(20,6), tvav N(20,6), pretvtva N(20,6) ENDTEXT TEXT To lcSelect Noshow select 0 as distribuie, CAST(0 as number(10)) as id_tip, id_articol, id_gestiune, cont, acont, id, id_efactura, nr, articol, descriere, detalii, cantitate, um, um_iso, um_roa, pret, proctva, tiptva, valoarefaratva, discountfaratva, in_stoc, articol_roa, codmat_roa, codbare, codclient, codfurnizor, codcpv, codnc8, nume_gestiune, cgest, 0 as tva, 0 as pretv, 0 as tvav, 0 as pretvtva from anaf_vefactura_detalii ENDTEXT lcOrder = [nr] lcgroup = [] lcFiltru = [1=2] && IIF(!EMPTY(NVL(crsFacturiPrimite.id,0)), [id_efactura=] + ALLTRIM(STR(crsFacturiPrimite.id)), [1=2]) lcFiltruOriginal = [] llModParam = .T. llAfiseaza = .F. * In acest cursor se iau doar detaliile pentru factura curenta gencursor('poFacturiDetalii', 'crsDetaliiFacturiTemp', lcSelect, lcFiltru, lcSchema, lcOrder, llAfiseaza, lcgroup, llModParam, lcFiltruOriginal) poFacturiDetalii.ca_baza1.afisare() * In acest cursor se vor adauga detaliile de la factura curenta, daca nu exista * Acest cursor se foloseste in formular SELECT * FROM crsDetaliiFacturiTemp INTO CURSOR crsDetaliiFacturi READWRITE * Cursoare helper CREATE CURSOR cTipArticoleP (id I, in_stoc N(1), tip V(50), cont V(4), ordine I) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (0, 'Nedefinit','', 0, 0) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (1, 'Mat. nestoc.','604', 0, 1) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (2, 'Energie','6051', 0, 2) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (3, 'Apa','6052', 0, 3) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (4, 'Gaze','6053', 0, 4) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES ( 5, 'Chirii','6123', 0, 5) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (6, 'Colaboratori','621', 0, 6) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (7, 'Comisioane','622', 0, 7) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (8, 'Protocol','623', 0, 8) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (9, 'Transp. bunuri/pers.','624', 0, 9) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (10, 'Deplasari','625', 0, 10) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (11, 'Posta/telecom.','626', 0, 11) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (12, 'Terti','628', 0, 12) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (13, 'Altele','461', 0, 13) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (14, '-----------------','', 0, 14) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (15, 'Marfuri','371', 1, 15) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (16, 'Materii prime','301', 1, 16) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (17, 'Materiale auxiliare','3021', 1, 17) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (18, 'Ambalaje','381', 1, 18) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (19, 'Obiecte de inventar','303', 1, 19) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (20, 'Amenajari provizorii','323', 1, 20) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (21, 'Mat. spre prelucrare','8032', 1, 21) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (22, 'Mat. in pastrare/consig.','8033', 1, 22) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (23, 'Discount financiar intrari','767', 0, 23) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (24, 'Combustibili','3022', 1, 24) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (25, 'Piese de schimb','3024', 1, 25) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (26, 'Alte mat. consumabile','3028', 1, 26) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (27, 'Discount comercial intrari','609', 0, 27) INSERT INTO cTipArticoleP (id, tip, cont, in_stoc, ordine) VALUES (28, 'Ambalaje SGR','461', 0, 28) SELECT cTipArticoleP GO TOP IN cTipArticoleP INDEX on ordine TAG ordine CREATE CURSOR cTipArticoleE (id I, in_stoc N(1), tip V(50), cont V(4), ordine I) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (0, 'Nedefinit','', 0, 0) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (29, 'Marfuri','707', 1, 1) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (30, 'Produse finite','701', 1, 2) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (31, 'Produse reziduale','703', 1, 3) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (32, 'Semifabricate','702', 1, 4) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (33, 'Discount financiar iesiri','667', 0, 5) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (34, 'Servicii vandute','704', 0, 6) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (35, 'Discount comercial iesiri','709', 0, 7) INSERT INTO cTipArticoleE (id, tip, cont, in_stoc, ordine) VALUES (36, 'Ambalaje SGR','461', 0, 8) SELECT cTipArticoleE GO TOP IN cTipArticoleE INDEX on ordine TAG ordine IF m.llPrimite SELECT id, tip, cont, in_stoc, ordine FROM cTipArticoleP ORDER BY ordine INTO CURSOR cTip ELSE SELECT id, tip, cont, in_stoc, ordine FROM cTipArticoleE ORDER BY ordine INTO CURSOR cTip ENDIF SELECT cTip INDEX on id TAG id *!* llSucces = goExecutor.oExecuta("select id_articol, denumire, codmat from vnom_articole where inactiv = 0", 'cArticole') *!* SELECT cArticole *!* INDEX on id_articol TAG id_articol llSucces = goExecutor.oExecuta([select distinct nume_gestiune,cgest,id_gestiune,nr_pag,cont from vgest_gestiuni_util where inactiv = 0 and id_util = ] + Alltrim(Str(m.gnIdUtil)), 'cGestiuni') SELECT cGestiuni APPEND BLANK INDEX on id_gestiune TAG id_gest select cast(alltrim(nume_gestiune) + '-' + alltrim(nvl(cgest, '')) as V(50)) as nume_gestiune, id_gestiune from cGestiuni order by nume_gestiune into cursor cGestiuni2 llSucces = goExecutor.oExecuta("select cod_um_iso, um_iso from syn_vnom_um_iso", 'cUMISO') SELECT cUMISO INDEX on cod_um_iso TAG cod_um_iso llSucces = goExecutor.oExecuta("select id, um, cod_um_iso, um_iso from vnom_um", 'cUM') SELECT cUM INDEX on id TAG id Select crsFacturi loFrmFacturi = Createobject("frm_import_efactura", m.llPrimite) * Do Form frm_import_efactura Name loFrmFacturi Linked With m.llPrimite Noshow loFrmFacturi.Show(1) Use In (Select('crsFacturi')) Use In (Select('crsDetaliiFacturi')) USE IN (SELECT('crsDetaliiFacturiTemp')) Use In (Select('cTip')) Use In (Select('cArticole')) Use In (Select('cGestiuni')) Use In (Select('cGestiuni2')) Use In (Select('cTip')) Use In (Select('cTipArticoleP')) Use In (Select('cTipArticoleE')) Endproc && vizFacturiPrimite ********************** * Completez anaf_efactura_detalii.id_articol la importul eFactura ********************** PROCEDURE UpdateEFacturaArticolROA LPARAMETERS tnIdEFactura Local lcSql, llSucces, lcSelect PRIVATE pnIdArticol, pnId, pnIdGestiune, pcCont lcSelect = SELECT() lcSql = "update anaf_efactura_detalii set id_articol = ?pnIdArticol, id_gestiune = ?pnIdGestiune, cont = ?pcCont, acont = ?pcAcont where id = ?pnId" llSucces = .T. SELECT id, id_articol, id_gestiune, cont, acont FROM crsDetaliiFacturi INTO CURSOR cArticoleTemp SELECT cArticoleTemp SCAN pnId = id pnIdArticol = IIF(EMPTY(NVL(id_articol, 0)), NULL, id_articol) pnIdGestiune = IIF(EMPTY(NVL(id_gestiune, 0)), null, id_gestiune) pcCont = ALLTRIM(NVL(cont, '')) pcAcont = ALLTRIM(NVL(acont, '')) llSucces = goExecutor.oExecuta(m.lcSql) IF !m.llSucces EXIT ENDIF ENDSCAN USE IN (SELECT('cArticoleTemp')) SELECT (m.lcSelect) RETURN m.llSucces ENDPROC && UpdateEFacturaArticolROA ********************** * Completez anaf_efactura_detalii.id_fact la importul eFactura achizitie ********************** PROCEDURE UpdateEFacturaIdFact LPARAMETERS tnIdEFactura, tnIdFact Local lcSql, llSucces, lcSelect PRIVATE pnId, pnIdFact lcSelect = SELECT() lcSql = "update anaf_efactura set id_fact = ?pnIdFact where id = ?pnId and NVL(id_fact,0) = 0" llSucces = .T. pnId = m.tnIdEfactura pnIdFact = m.tnIdFact llSucces = goExecutor.oExecuta(m.lcSql) SELECT (m.lcSelect) RETURN m.llSucces ENDPROC && UpdateEFacturaIdFact