// ==================================================================================
// 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