// Импорт накладных

const UseBarcode = true; // определяет, будет ли использоваться штрихкод, поставляемый поставщиком
const BasePath = 'C:\MedZakaz\medzakaz.fdb'; //Путь к базе в формате <компьютер>:<локальный путь>


var
    Header :TStringList; // предопределена приложением
    Data :TStringList; // предопределена приложением
    ExportAgent :string; // все определены приложением

    ExportAgentName :string;
    ExportNakladNom :string;
    ExportNakladID_DOC :string;
    ExportNakladData :string;
    ExportNakladSklad :string;
    ExportSkladName :string;

var Table :TTable;
    Form : TForm;
    Grid : TListBox;
    lbl1, lbl2 : TLabel;
    dtEditFrom, dtEditTo : TDateTimePicker;
    btnUpdate, btnSelect : TBitBtn;
    NumNakl : TStringList;


    TovList  : TIBQuery;


    TableAgent : TTable;
    CDS :TClientDAtaset;
    num_doc :string;
    Item :variant;
    i :integer;
    FileName :string;



    OpenDialog :TOpenDialog;
    ThF_FIRSTPRICE,thf_SROK,thF_SrokSertif,ThF_NameSertif,thF_Amount,thF_NDS,
    thF_Name,thF_Seria,thF_Sertif,thF_NameProducer,thf_Tamog,
    thF_Country,thF_Regn,thF_Price,thF_BarCode,thF_DateSertif,thF_Em :TField;


    thF_Code,thF_CountryCode,thF_ProducerCode,thf_AnalizeCode,thF_SertifCode :TField;

    thF_Price_Reg :TField;
    PostName,
    str,str1 :string;
    Field :TField;

    function GetItemValue(Item :Variant; Name :String) :string;
    begin
       try
         result := vartostr(Item.attributes.getNamedItem(Name).value);
       except
         result := ''
       end;
    end;

    function FindField(const Table :tDataset; const FieldNames :string; const FieldDescription :string) :TField;
    var List :TstringList;
        i :integer;
    begin
       result := nil;
       List := tstringlist.create;
       try
          stringtolist(FieldNames,List,';,');
          if list.count=0 then RaiseException('не указаны имена полей в функции FindField');

          for i := 0 to List.count-1 do
          begin
            Result := Table.findfield(List[i]);
            if result<>nil then break;
          end;
          if (Result=nil) and (FieldDescription>'') then RaiseException('не найдено поле "'+FieldDescription+'" ('+FieldNames+')');
       finally
          List.free;
       end;

    end;

    procedure AddStr(var Destination :string; const thField :tField);
    begin
      if thField<>nil
      then Destination := Destination + thField.asstring;
      Destination := Destination + #9;
    end;

    function IFSTR(BoolVal :boolean; const TrueVal :string; const FAlseVal :string = '') :string;
    begin
      if BoolVal then Result := TrueVal
      else Result := FalseVal;
    end;

    procedure Update(Sender : TObject);
    var
      DataSource : TDataSource;
      DB : TIBDatabase;
      Tr : TIBTransaction;
      NaklList : TIBQuery;
    begin
      DB:=TIBDatabase.Create(nil);
       DB.Params.Add('Password=masterkey');
       DB.Params.Add('User_Name=SYSDBA');
       DB.Params.Add('lc_ctype=WIN1251');
       DB.DatabaseName:=BasePath;
       DB.LoginPrompt:=False;

       Tr:=TIBTransaction.Create(nil);
       Tr.DefaultDatabase:=DB;
       DB.DefaultTransaction:=Tr;

       Datasource:=TDataSource.Create(nil);

       NaklList:=TIBQuery.Create(nil);
       NaklList.Database:=DB;
       NaklList.Transaction:=Tr;

       TovList:=TIBQuery.Create(nil);
       TovList.Database:=DB;
       TovList.Transaction:=Tr;

       DB.Connected:=True;
       Tr.StartTransaction;

       NaklList.SQL.Text:=
        'select tpd.id as IDDoc, tpd.DATA_DOC, tpd.distrib_data_doc, tpd.distrib_num_doc, tpd.distrib_id , tpd.sum_by_distrib, ' +
        'tpd.sum_nds_distrib, tpd.data_doc, tpd.sum_rozn, tpd.num_doc, tpd.sklad_id, td.name_distrib ' +
        'from tbl_prihod_doc tpd left outer join tbl_distribs td on td.id=tpd.distrib_id ' +
        'where distrib_data_doc>=:ds and distrib_data_doc<=:de order by tpd.data_doc desc ';
       NaklList.Params[0].AsDateTime:=dtEditFrom.Date;
       NaklList.Params[1].AsDateTime:=dtEditTo.Date;
       NaklList.Open;


       Grid.Items.Clear;
       NumNakl.Clear;
       while not NaklList.Eof do
        begin
          NumNakl.Add(NaklList.FieldByName('IDDoc').AsString);
          Grid.Items.Add(
            FormatDateTime('DD.MM.YY', NaklList.FieldByName('distrib_data_doc').AsDateTime) + ' - ' +
            Format(' - №%.10s - %10.2f - %s',
               [Trim(NaklList.FieldByName('distrib_num_doc').AsString),
                NaklList.FieldByName('sum_by_distrib').AsFloat,
                Trim(NaklList.FieldByName('name_distrib').AsString)]
              ));
          NaklList.Next;
        end;
       Grid.ItemIndex:=0;


    end;

    procedure DblClickGrid(Sender : TObject);
    begin
    end;

 begin
     Form:=TForm.Create(nil);
     try
       Form.Width:=400;
       Form.Height:=450;
       Form.Caption:='Импорт накладных из программы Med-Zakaz';

       NumNakl:=TStringList.Create;

       Grid:=TListBox.Create(Form);
       Grid.Parent:=Form;
       Grid.SetBounds(10,50, Form.Width-20, Form.Height-150);
       Grid.OnDblClick:=@DblClickGrid;

       lbl1:=TLabel.Create(nil);
       lbl1.Caption:='С';
       lbl1.Parent:=Form;
       lbl1.SetBounds(5, 15, 10, 20);
       dtEditFrom := TDateTimePicker.Create(form);
       dtEditFrom.Parent:=Form;
       dtEditFrom.SetBounds(20, 10, 150, 20);
       dtEditFrom.Date:=Now-2;

       lbl2:=TLabel.Create(nil);
       lbl2.Caption:='По';
       lbl2.Parent:=Form;
       lbl2.SetBounds(210, 15, 20, 30);
       dtEditTo := TDateTimePicker.Create(form);
       dtEditTo.Parent:=Form;
       dtEditTo.SetBounds(230, 10, 150, 20);
       dtEditTo.Date:=Now;

       btnUpdate:=TBitBtn.Create(Form);
       btnUpdate.Parent:=Form;
       btnUpdate.SetBounds(175, 5, 30, 30);
       btnUpdate.Kind:= bkRetry;
       btnUpdate.Caption:='';
       btnUpdate.ModalResult:=mrNone;
       btnUpdate.OnClick:=@Update;


       btnSelect:=TBitBtn.Create(Form);
       btnSelect.Parent:=Form;
       btnSelect.SetBounds((Form.Width div 2)-75, Form.Height-80, 150, 40);
       btnSelect.Kind:= bkOk;
       btnSelect.Caption:='Загрузить';


       Update(nil);

       Form.Position:=poScreenCenter;
       if Form.ShowModal=mrOk then
         begin
           TovList.SQL.Text:=
             'select tpd.ID, tpd.prihod_doc_id, tpd.trade_name_id, tn.trade_name as name, tpd.qnt as kol, ' +
             'tpd.price_izg as firstcena, tpd.price_reestr as reestrcena, tpd.price_distr_no_nds, tpd.series_tovar as seria, ' +
             'tpd.nds, tpd.price_max, tpd.price_rozn, tpd.fabr_name_id, tf.fabr_name, tpd.ean13 as barcode, ' +
             'tpd.price_distr*tpd.qnt as summa, tpd.gdate as srok, tpd.numgtd as tamog, tpd.sertif, tpd.sertdate as sroksertif, tpd.sertorg as sertifname, ' +
             'reverse(right(reverse(tf.fabr_name), char_length(tf.fabr_name) - position(''/'', reverse(tf.fabr_name)))) as producername, ' +
             'reverse(left(reverse(tf.fabr_name), position(''/'', reverse(tf.fabr_name))-1)) as producercountryname ' +
             'from tbl_prihod_doc_lines tpd ' +
             'left outer join tbl_trade_name tn on tn.server_id=tpd.trade_name_id ' +
             'left outer join tbl_fabr_name tf on tf.server_id=tpd.fabr_name_id ' +
             'where tpd.prihod_doc_id=:did';
           TovList.Params[0].AsString:=NumNakl[Grid.ItemIndex];
           TovList.Open;

           data.Clear;

           {
           list.Add('srok'#9'barcode'#9'sroksertif'#9'sertifname'#9+
             'upa'#9'nn'#9'article'#9'kart'#9'kol'#9'em'#9'rosn'#9'nds'#9'ed'#9+
             'name'#9'firstcena'#9'seria'#9'sertif'#9'analize'#9'producer'#9+
             'producername'#9'nalog5'#9'idanalize'#9'analizename'#9'datasertif'#9+
             'dateanalize'#9'reestrcena'#9'tamog'#9'producercountry'#9+
             'producercountryname'#9'maxprocent'#9'passport'#9'datepassport'#9+
             'sorti'#9'gost'#9'regn'#9'summa'#9'cena'#9'itogcena'#9'itogsumma');
           }


           thF_Price := FindField(TovList,'summa','Суммa');
           thF_AMOUNT := FindField(TovList,'kol','Количество единиц');
           thF_Seria := FindField(TovList,'seria','Серия');
           thF_Barcode := FindField(TovList,'barcode','');
           thF_SROK :=  FindField(TovList,'srok','Срок годности');
           thF_NAME := FindField(TovList,'name','Наименование товара');
           thf_country := findfield(TovList,'producercountryname','Страна-производитель');
           thF_NameProducer := FindField(TovList,'ProducerName','Производитель');
           thF_NDS := FindField(TovList,'nds','Ставка НДС');
           thF_tamog := FindField(TovList,'tamog','Номер таможенной декларации');
           thF_Sertif := FindField(TovList,'sertif','Сертификат');
           thF_DateSertif := findfield(TovList,'sroksertif','');
           thF_NAMESERTIF := FindField(TovList,'sertifname','Кем выдан сертификат');
           //thF_RegN := FindField(TovList,'regnomer','Регистрационный номер');
           thF_FIRSTPRICE := FindField(TovList, 'firstcena','Цена завода');
           thF_Price_reg := findfield(TovList,'reestrcena','Цена реестра');

           data.Add(
           'summa'#9+
           'kol'#9+
           'seria'#9+
           'barcode'#9+
           'srok'#9+
           'name'#9+
           'producercountryname'#9+
           'producername'#9+
           'nds'#9+
           'tamog'#9+
           'sertif'#9+
           'sroksertif'#9+
           'sertifname'#9+
           'firstcena'#9+
           'reestrcena');


          while not TovList.eof do begin
           Str := '';
             Addstr(str,thF_Price);
             Addstr(str,thf_amount);
             Addstr(str,thf_SEria);
             Addstr(str,thF_Barcode);
             Addstr(str,thF_Srok);
             Addstr(str,thf_name);
             Addstr(str,thf_Country);
             Addstr(str,thf_NameProducer);
             Addstr(str,thf_NDS);
             Addstr(str,thf_Tamog);
             Addstr(str,thf_SErtif);
             Addstr(str,thF_DateSErtif);
             Addstr(str,thf_namesertif);
             Addstr(str,thF_FIRSTPRICE);
             Addstr(str,thF_price_reg);

          data.Add(str);
          TovList.Next;
        end;

         end;

     finally
       Form.Free;
     end;
     exit;


     FileName := '';
     OpenDialog := TOpenDialog.create(nil);
     try
       OpenDialog.Title := 'Укажите файл';
       OpenDialog.FileName  := AppIniFile.readstring('plugins_kruchinin','filename','');
       OpenDialog.initialdir := extractfilepath(opendialog.filename);
       OpenDialog.Filter := 'Файлы-накладные(*.dbf,*.csv)|*.dbf;*.csv|Все файлы(*.*)|*.*';
       while  OpenDialog.execute do begin
         if not fileexists(OpenDialog.FileName) then CreateHint('Укажите правильно файл ')
         else begin
           Filename := OpenDialog.FileName;
           AppIniFile.writestring('plugins_kruchinin','filename',OpenDialog.filename);
           break;
         end;
       end;
     finally
       OpenDialog.free;
     end;

     if FileName = '' then exit;

     with TMyWait.create('Идет импорт накладной') do
     try

       CDS := TClientdataset.create(nil);
       try

        if uppercase(extractfileext(filename))='.DBF'
        then begin
            Table := TTable.Create(nil);
            try
              table.tabletype := ttFoxpro;

              Table.TableName := gettempdir + extractfilename(FileName);
              filedelete(Table.tablename);
              Table.tabletype := ttfoxpro;
              copyfile(FileName,Table.TableName,False);
              //SetTableLangDriver(Table,'db866ru0');
              //CheckDBFCP(Table.TableName,38); //866DOS
              Table.Open;
              Table.First;

              readdataset(cds,table);

            finally
              table.free;
            end;
        end
        else begin
          ReadTXT( CDS, FileName, '|');
        end;

        {PostName:=ExtractFileName(filename);
        if pos('.dbf', PostName)>0 then
           delete(PostName, pos('.dbf', PostName), length(postName));
        if length(PostName)>1 then
         begin
           if PostName[1]='n' then
             delete(PostName, 1, 1);
           if pos('_', PostName)>0 then
             delete(PostName, pos('_', PostName), length(PostName));
           if ValidInt(PostName) then
             PostName:='';
         end;}

        cds.first;

        if cds.findfield('ndoc')<>nil
        then begin
          Header.Values[ExportNakladNom] := cds.fieldbyname('ndoc').asstring;
          Header.Values[ExportNakladData] := cds.fieldbyname('datedoc').asstring;
          PostName:=Trim(cds.fieldbyname('distrib').asstring);
          if PostName<>'' then
           begin
             TableAgent:=TTable.Create(nil);
             try
               try
                 TableAgent.DatabaseName:='SOLARIS';
                 TableAgent.TableName:='AGENTS.DB';
                 TableAgent.OPen;
                 TableAgent.IndexFieldNames:='NAME';
                 if not TableAgent.FindKey([PostName]) then
                   begin
                     TableAgent.Append;
                     TableAgent.FieldByName('ID').AsInteger:=BDEQueryValue('select max(id) from agents',[NULL],'dbkassa')+1;
                     TableAgent.FieldByName('NAME').AsString:=PostName;
                     TableAgent.Post;
                   end;
                 Header.Values[ExportAgent]:=TableAgent.FieldByName('id').AsString;
               finally
                 TableAgent.Close;
                 TableAgent.Free;
               end;
             except
               ShowMessage(ExceptionMessage);
             end;
           end;
        end
        else begin
          raiseexception('Таблица имеет формат, не согласованный с поставщиком. Обратитесь в техподдержку');
        end;

        data.Clear;

        {
        list.Add('srok'#9'barcode'#9'sroksertif'#9'sertifname'#9+
          'upa'#9'nn'#9'article'#9'kart'#9'kol'#9'em'#9'rosn'#9'nds'#9'ed'#9+
          'name'#9'firstcena'#9'seria'#9'sertif'#9'analize'#9'producer'#9+
          'producername'#9'nalog5'#9'idanalize'#9'analizename'#9'datasertif'#9+
          'dateanalize'#9'reestrcena'#9'tamog'#9'producercountry'#9+
          'producercountryname'#9'maxprocent'#9'passport'#9'datepassport'#9+
          'sorti'#9'gost'#9'regn'#9'summa'#9'cena'#9'itogcena'#9'itogsumma');
        }


        thF_Price := FindField(cds,'sumpay','Суммa');
        thF_AMOUNT := FindField(cds,'qnt','Количество единиц');
        thF_Seria := FindField(cds,'ser','Серия');

        if UseBarcode then
          thF_Barcode := FindField(cds,'ean13','') // если нет, то не будет ошибки
        else
          thF_Barcode := nil;

        thF_SROK :=  FindField(cds,'gdate','Срок годности');
        thF_NAME := FindField(cds,'name','Наименование товара');
        thf_country := findfield(cds,'cntr','Страна-производитель');
        thF_NameProducer := FindField(cds,'firm','Производитель');
        thF_NDS := FindField(cds,'nds','Ставка НДС');
        thF_tamog := FindField(cds,'numgtd','Номер таможенной декларации');
        thF_Sertif := FindField(cds,'sertif','Сертификат');
        thF_DateSertif := findfield(cds,'sertdate','');
        thF_NAMESERTIF := FindField(cds,'sertorg','Кем выдан сертификат');
        thF_RegN := FindField(cds,'regnomer','Регистрационный номер');

        thF_FIRSTPRICE := FindField(cds, 'price1','Цена завода');
        thF_Price_reg := findfield(cds,'regprc','');

        //thF_SROKSERTIF := FindField(Table,'srok','Срок действия сертификата');


        //thF_Em := findfield(table,'package','');








       finally
          cds.free;
       end;

     finally
       free
     end;

     createhint('Операция завершена');
 end.