Beispiel - Iris MasterData erzeugen
- Former user (Deleted)
Owned by Former user (Deleted)
Feb. 06, 2017
8 min read
Loading data...
Iris MasterData erzeugen
Das nachfolgende Script erzeugt die von Iris benötigten Master-Dateien. Als Quelle werden die CLARC XCONTROL Tabellen verwendet.
// ================================================================================== // CompileMasterData_Iris // ================================================================================== var f : TextFile; begin // ------------------------------------------------------------ // Init // ------------------------------------------------------------ LFA_CSVHeader := 'CREDITORID;NAME1;NAME2;CITY;ZIP;BANKCOUNTRY;BANKSORTCODE;BANKACCOUNT;COUNTRYCODE;COMPANYCODE;IBAN;VATREGNO;NATIONALTAX;XPRO1;XPRO2;XPRO3;XPRO4;XPRO5;CLIENT'; LFA_Fields := ['LIFNR','NAME1','NAME2','ORT01','PSTLZ','BANKS','BANKL','BANKN','LAND1','BUKRS','IBAN','VATID','STCD1','','','','','','MANDT']; LFA_CSVPath := ccScriptEngine.GetResource('MasterCreditorCSVPath',''); LFA_CSVPathTmp := LFA_CSVPath + '.~~~'; EKP_CSVHeader := 'CLIENT;ORDERNUMBER;ORDERITEM;CREDITORID;ARTICLENUMBER;SHORTTEXT;QUANTITY;QUANTITYUNIT;PRICINGUNIT;SINGLEPRICE;DELIVERYNUMBER'; EKP_Fields := ['MANDT','EBELN','EBELP','LIFNR','MATNR','TXZ01','WEMNG','MEINS','BPRME','NETPR','XBLNR']; EKP_CSVPath := ccScriptEngine.GetResource('MasterOrdersCSVPath',''); EKP_CSVPathTmp := EKP_CSVPath + '.~~~'; DEB_DB_Header := 'DPRO1;DPRO2;SIMPLE;COMPANYCODE;DRE;DPRO3;DPRO4;DPRO5;CLIENT'; DEB_Fields := ['MANDT','BUKRS','STCEG','NAME1','STREET','POST_CODE1','CITY1','LAND1']; DEB_DB_Path := ccScriptEngine.GetResource('MasterDebitorCSVPath',''); DEB_DB_PathTemp := DEB_DB_Path + '.~~~'; VAT_DB_Header := 'VATREGNO;COMPANYCODE;CLIENT'; VAT_DB_Path := ccScriptEngine.GetResource('MasterOwnVatRegNoCSVPath',''); VAT_DB_PathTemp := VAT_DB_Path + '.~~~'; CmdOnStart := ccScriptEngine.GetResource('CmdOnStart',''); ParamOnStart := ccScriptEngine.GetResource('CmdOnStart_Param',''); CmdOnFinish := ccScriptEngine.GetResource('CmdOnFinish',''); ParamOnFinish := ccScriptEngine.GetResource('CmdOnFinish_Param',''); // ------------------------------------------------------------ if length(CmdOnStart) > 0 then Execute(CmdOnStart,ParamOnStart,sw_hide); // ------------------------------------------------------------ ObjConnC := CreateOleObject('ADODB.Connection'); ObjConnC.Open(ccScriptEngine.GetResource('ConnectionString','')); ObjRSC := CreateOleObject('ADODB.RecordSet'); ObjRSC.ActiveConnection := ObjConnC; try WriteLog('compiling creditor records'); Query := 'SELECT * FROM cc_xcontrol_creditors'; ObjRSC.Open(Replace(Query, '"',chr(39))); if ObjRSC.Eof = false then begin WriteLog('writing csv file'); BakFile := LFA_CSVPath + '.bak'; DeleteFile(BakFile,0,false); RenameFile(LFA_CSVPath,BakFile); try AssignFile(f, LFA_CSVPathTmp); try Rewrite(f); Writeln(f,LFA_CSVHeader); while ObjRSC.Eof = false do begin Row := ''; for j:=0 to 18 do begin if length(LFA_Fields[j]) > 0 then begin Row := Row + VarToStr(ObjRSC.Fields(LFA_Fields[j]).Value); end; if j < 18 then Row := Row + ';'; end; Append(f); Writeln(f,Row); ObjRSC.MoveNext; end; finally CloseFile(f); RenameFile(LFA_CSVPathTmp,LFA_CSVPath); end; DeleteFile(BakFile,0,false); except DeleteFile(LFA_CSVPath,0,false); RenameFile(BakFile,LFA_CSVPath); end; end; // ------------------------------------------------------------ // Bestelldaten Download // ------------------------------------------------------------ WriteLog('compiling order records'); ObjRSC.Close; Query := 'SELECT * FROM cc_xcontrol_orders'; ObjRSC.Open(Replace(Query, '"',chr(39))); if ObjRSC.Eof = false then begin WriteLog('writing csv file'); BakFile := EKP_CSVPath + '.bak'; DeleteFile(BakFile,0,false); RenameFile(EKP_CSVPath,BakFile); try AssignFile(f, EKP_CSVPathTmp); try Rewrite(f); Writeln(f,EKP_CSVHeader); while ObjRSC.Eof = false do begin Row := ''; for j:=0 to 10 do begin if length(EKP_Fields[j]) > 0 then begin Row := Row + VarToStr(ObjRSC.Fields(EKP_Fields[j]).Value); end; if j < 10 then Row := Row + ';'; end; Append(f); Writeln(f,Row); ObjRSC.MoveNext; end; finally CloseFile(f); RenameFile(EKP_CSVPathTmp,EKP_CSVPath); end; DeleteFile(BakFile,0,false); except DeleteFile(EKP_CSVPath,0,false); RenameFile(BakFile,EKP_CSVPath); end; end; // ------------------------------------------------------------ // Debitorendaten Download // ------------------------------------------------------------ WriteLog('compiling debitor records'); ObjRSC.Close; Query := 'SELECT * FROM cc_xcontrol_debitors'; ObjRSC.Open(Replace(Query, '"',chr(39))); if ObjRSC.Eof = false then begin WriteLog('writing master debitor csv file'); BakFile := DEB_DB_Path + '.bak'; DeleteFile(BakFile,0,false); RenameFile(DEB_DB_Path,BakFile); try AssignFile(f, DEB_DB_PathTemp); try Rewrite(f); Writeln(f,DEB_DB_Header); while ObjRSC.Eof = false do begin Name := VarToStr(ObjRSC.Fields('NAME1')); BuKrs := VarToStr(ObjRSC.Fields('BUKRS')); Mandt := VarToStr(ObjRSC.Fields('MANDT')); Row := Name + ';' + '' + ';' + 'J' + ';' + BuKrs + ';' + '' + ';' + '' + ';' + '' + ';' + '' + ';' + Mandt; Append(f); Writeln(f,Row); ObjRSC.MoveNext; end; finally CloseFile(f); RenameFile(DEB_DB_PathTemp,DEB_DB_Path); end; DeleteFile(BakFile,0,false); except DeleteFile(DEB_DB_Path,0,false); RenameFile(BakFile,DEB_DB_Path); end; end; // ------------------------------------------------------------ // Debitorendaten Download // ------------------------------------------------------------ WriteLog('compiling debitor records'); ObjRSC.Close; Query := 'SELECT * FROM cc_xcontrol_debitors'; ObjRSC.Open(Replace(Query, '"',chr(39))); if ObjRSC.Eof = false then begin WriteLog('writing master OwnVatRegNo csv file'); BakFile := VAT_DB_Path + '.bak'; DeleteFile(BakFile,0,false); RenameFile(VAT_DB_Path,BakFile); try AssignFile(f, VAT_DB_PathTemp); try Rewrite(f); Writeln(f,VAT_DB_Header); while ObjRSC.Eof = false do begin VatNo := VarToStr(ObjRSC.Fields('STCEG')); BuKrs := VarToStr(ObjRSC.Fields('BUKRS')); Mandt := VarToStr(ObjRSC.Fields('MANDT')); if length(trim(VatNo)) > 0 then begin Row := VatNo + ';' + BuKrs + ';' + Mandt; Append(f); Writeln(f,Row); end; ObjRSC.MoveNext; end; finally CloseFile(f); RenameFile(VAT_DB_PathTemp,VAT_DB_Path); end; DeleteFile(BakFile,0,false); except DeleteFile(VAT_DB_Path,0,false); RenameFile(BakFile,VAT_DB_Path); end; end; finally ObjConnC.Close; if length(CmdOnFinish) > 0 then Execute(CmdOnFinish,ParamOnFinish,sw_hide); end; end; // ================================================================================== // eof