// Скрипт импорт из Фармнет (Саратов)

// В скрипте реализована возможность импорта кодов товаров и производителей из OSA
const
   // оставить пустым, если не нужно, чтобы механизм запроса кодов был включен
   OSA_DATABASE = 'localhost:c:\osa\db\data.fdb';     //'localhost:c:\database\farmnet\fornewscript\osa.fdb';       //'server/port:databasename';
   OSA_LIBRARY  = 'c:\osa\fbembed.dll';        // 'c:\utils\firebird\server\bin\fbclient.dll';

var
    Dirname :string = ''; // каталог почты. Если не указано, то скрипт будет запрашивать его при каждом открытии.


const UseBarcode = true; // определяет, будет ли использоваться штрихкод, поставляемый поставщиком
      UseOutPrice = true;


      OUTPRICE_NAME = 'PROZNWNDS';
      SUPPLIER_NAME = 'DISTR';
      BARCODENAME = 'BARCODE1';

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

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

    Cena :currency;


const ListSeparator1 = ' от ';
      ListSeparator2 = ' пост.: ';
      ListSeparator3 = ' скл.: ';
      ListSeparator4 = ' файл: ';



function ifthenstrmy(AValue :boolean; ATrue :string; AFalse :string) :string;
begin
  if AValue
  then result := atrue
  else Result := aFalse;
end;

var Table :TTable;
    num_doc :string;
    Item :variant;
    i :integer;
    Dirname :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,thF_FirstProcent :TField;
    thF_Price_Reg :TField;
    thF_Price_RS :Tfield;
    thf_action_lab :tfield;
    str,str1 :string;
    Field :TField;

    InvoicesList :TStringList;

    FileList :tstringlist;

    FibDB :TpFibDatabase = nil;

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

    procedure AddStr(var Destination :string; const thField :tField; NULLVal :string = '');
    begin
      if (thField<>nil) and (thfield.asstring<>nullval)
      then Destination := Destination + thField.asstring;
      Destination := Destination + #9;
    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;

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


    function GetValueFromListDialog(List :Tstrings) :string;
    var Form :TForm;
        ListBox :TListBox;
        Panel :TPanel;
        Btn :TButton;
    begin

      result := '';
      if List=nil then  exit;

      Form := TForm.create(nil);

      try
        ListBox := TListBox.create(Form);
        with Listbox do
        begin
          parent :=Form;
          Items.text := List.text;
          if List.count>0 then ItemIndex := 0;
          align :=alclient;
          Sorted := true;
        end;


        Form.caption := 'Выберите накладную:';
        form.position := poScreenCenter;
        form.borderstyle := bsDialog;
        form.width := 700;
        form.height := 400;
        Form.Font.Size := Form.Font.Size +4;

        Panel := tPanel.create(Form);
        with Panel do
        begin
          parent := Form;
          align := albottom;
          height := 35;
          BevelInner :=bvNone;
          BevelOuter := bvNone;

          (*
          with TBevel.create(Form) do
          begin
            Parent := panel;
            Align :=alTop;
            Shape := bsTopLine;
            Height := 4;
          end;
          *)

          Btn := TButton.create(Form);
          with Btn do
          begin
            Parent := Panel;
            Caption := 'Отмена';
            ModalResult := mrCancel;
            Cancel := true;
            width := 70;
            Top := 6;
            Left := Form.ClientWidth - Width - 20;
          end;


          with TButton.create(Form) do
          begin
            Parent := Panel;
            Caption := 'Ok';
            ModalResult := mrOk;
            Default := true;
            Width := Btn.width;
            Top := Btn.Top;
            Left := Btn.left- Width -10;
          end;

        end;

        if form.showmodal = mrok then
        begin
          if ListBox.Itemindex <0
          then CreateHintW('Вы не выбрали ни одной накладной')
          else Result := ListBox.Items[ListBox.Itemindex];
        end

      finally
        Form.free
      end;
    end;


var
  //CDSTMP :TClientDataset = nil;
  TabTovar :ttable = nil;
  TabProducer :TTable = nil;

    function GetCodeFromOSA(SearchName :string; ForProducer :boolean) :string;
    var
      strval :string;
      currtab :ttable;
      arr :variant;
      i :integer;
    begin
      result := '';

      searchname := trim(searchname);
      if searchname = '' then exit;

      if FibDB=nil then exit; // не требуется, либо была ошибка

      //if cdstmp=nil then cdstmp  := TClientDataset.create(selfscript);

      if tabtovar=nil then
      begin
        tabtovar := ttable.create(selfscript);
        tabtovar.databasename:= 'dbkassa';
        tabtovar.tablename := 'tovar.db';
        tabtovar.open;
      end;

      if tabproducer=nil then
      begin
        tabproducer := ttable.create(selfscript);
        tabproducer.databasename := 'dbkassa';
        tabproducer.tablename := 'producer.db';
        tabproducer.open;
      end;

      if not ForProducer
      then
        strval :=
          'SELECT S.COD FROM FN_SPRTOV FS '+
          ' INNER JOIN SPRTOV S ON S.FN_SPRTOVID = FS.ID '+
          ' INNER JOIN DRUG D ON D.DRUGID = FS.DRUGID '+
          ' INNER JOIN FORM F ON F.FORMID = FS.FORMID '+
          ' WHERE D.DRUG||'' ''||F.FORM  = :TOVNAME '
      else
        strval :=

          ' SELECT S.COD FROM FABR F '+
          ' INNER JOIN SPRPROIZ S ON S.FABRID = F.FABRID '+
          ' WHERE F.FABR = :FABR ';



      //функция не работает, надо заниматься ошибкой
      //FibReadQuery(cdstmp, strval, [SearchName], FibDB.DefaultTransaction);

      Arr := FibQueryValues(strval, [SearchName], FibDB.DefaultTransaction);
      if not varisnull(arr) then
      try
        if not ForProducer
        then
          currtab := tabTovar
        else
          Currtab := tabProducer;


        for i := vararraylowbound(arr,2) to vararrayhighbound(arr,2) do
        begin
          if Currtab.findkey([arr[0,i]  ])
             and (currtab.fieldbyname('notused').asinteger=0)
          then begin
            Result := arr[0,i];
            exit;
          end;
        end;

        {
        cdstmp.first;
        while not cdstmp.eof do
        begin
          if Currtab.findkey([cdstmp.fields[0].asstring ])
             and (currtab.fieldbyname('notused').asinteger=0)
          then begin
            Result := cdstmp.fields[0].asstring;
            exit;
          end;
          cdstmp.next;
        end;
        }
      finally
        arr := nil;
      end;



    end;



 begin

     if filename = ''
     then begin
       if DirName = ''
       then begin
           DirName :=  AppIniFile.readstring('plugins_farmnet','dirname','');
           if not  SElectDirectory('Выберите каталог с накладными:','',DirName) then exit;
           DirName := Includetrailingpathdelimiter(DirName);
           AppIniFile.writestring('plugins_farmnet','DirName',DirName);
       end
       else
         DirName := Includetrailingpathdelimiter(DirName);
     end
     else DirName := extractfilepath(filename);


     InvoicesList:= Tstringlist.create;
     FileList := tstringlist.create;


     if OSA_DATABASE<>''
     then
     try
       FibDB := tpFibDatabase.create(selfscript);
       fibdb.sqldialect := 3;
       FibDB.Connected := False;
       FibDB.ConnectParams.UserName := 'sysdba';
       Fibdb.ConnectParams.Password := 'masterkey';
       if OSA_Library<>'' then FibDB.LibraryName := OSA_LIBRARY;
       FibDB.DBName := OSA_DATABASE;
       fibdb.ConnectParams.CharSet := 'WIN1251';
       fibdb.connected := true;

       fibdb.defaulttransaction :=  tpfibtransaction.create(fibdb);
       with fibdb.defaulttransaction.trparams do
       begin
          text := 'read';
          add('nowait');
          //add('concurrency');   //snapshot
          add('rec_version');
          add('read_committed');
       end;


       FibDB.Connected := true;
     except
       CreatehintW(exceptmessage+#13+'Обратитесь в техподдержку','Ошибка при инициализации базы OSA');
       FibDB.free;
       FibDb := nil;
     end;

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

       if filename ='' then GetFileList(DirName,FileList,'*.dbf',false)
       else FileList.text := Filename;


       Table := TTable.Create(nil);
       try

        table.tabletype := ttFoxpro;

        for i := 0 to FileList.count-1 do
        begin

          FileName := DirName + FileList[i];


          table.close;
          Table.TableName := gettempdir + extractfilename(FileName);
          filedelete(Table.tablename);
          Table.tabletype := ttfoxpro;
          copyfile(FileName,Table.TableName,False);

          //createhinti(table.tablename);


          //SetTableLangDriver(Table,'db866ru0');
          //CheckDBFCP(Table.TableName,38); //866DOS


          Table.Open;

          Table.First;

          if not table.isempty and (table.findfield('docnumber')<>nil)
          then begin
            table.first;
            while not table.eof do
            begin
              Str := Table.fieldbyname('docnumber').asstring + ListSeparator1
                     + Table.fieldbyname('docdate').asstring + ListSeparator2
                     + Table.fieldbyname(SUPPLIER_NAME).asstring + ListSeparator3
                     + Table.fieldbyname('branch').asstring + ListSeparator4
                     + extractfilename(FileName);

              If InvoicesList.indexof(Str)<0
              then InvoicesList.add(Str);

              table.next;
            end;
          end;
        end;


        if InvoicesList.count=0
        then begin
            CreateHintW('Накладных не найдено');
            exit;
        end
        else begin
          if Str='' then begin
            CreateHintW('Не выбрана накладная');
            exit;
          end;

          if InvoicesList.count>1
          then begin
            Str := GetValueFromListDialog(InvoicesList);
            if str = '' then exit;
          end
          else Str := INvoicesList[0];
        end;


        Header.Values[ExportNakladNom] := copy(str,1,pos(listSeparator1,Str)-1);


        FileName := copy(str,pos(Listseparator4,Str)+length(ListSeparator4),length(Str));

        table.close;
        table.tablename := Gettempdir + Filename;

        filedelete(Table.tablename);
        Table.tabletype := ttfoxpro;
        copyfile(dirname +  FileName,Table.TableName,False);

        //SetTableLangDriver(Table,'db866ru0');

        //bvmessage(table.tablename);


        table.open;
        table.filter := '[DOCNUMBER]='+Quotedstr(Header.Values[ExportNakladNom]);  // если в одном файле оказалось несколько накладных, то отфильтруем по номеру
        Table.filtered := true;

        Header.Values[ExportNakladData] := copy(str,
                                                pos(listSeparator1,Str)+length(ListSeparator1),
                                                Pos(ListSeparator2,Str)-pos(listSeparator1,Str)-length(ListSeparator1));

        Header.Values[ExportAgentName] := copy(str,
                                                pos(listSeparator2,Str)+length(ListSeparator2),
                                                Pos(ListSeparator3,Str)-pos(listSeparator2,Str)-length(ListSeparator2));

        Header.Values[ExportSkladName] := copy(str,
                                                pos(listSeparator3,Str)+length(ListSeparator3),
                                                Pos(ListSeparator4,Str)-pos(listSeparator3,Str)-length(ListSeparator3));


        CreateHintI('Дата: ' + Header.Values[ExportNakladData]
                    +#13+' Номер: '+ Header.Values[ExportNakladNom]
                    +#13+' Пост.: '+ Header.Values[ExportAgentName]
                    );


        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_NAME := FindField(Table,'TOVNAME','Наименование товара');
        thF_NameProducer := FindField(Table,'FABRNAME','Производитель');
        thf_country := findfield(table,'COUNTRY','Страна');
        thF_AMOUNT := FindField(Table,'QUANTITY','Количество единиц');
        thF_Seria := FindField(Table,'SERIA','Серия');
        thF_SROK :=  FindField(Table,'SROKG','Срок годности');
        thF_tamog := FindField(Table,'NUMBERGTD','Номер таможенной декларации');
        thF_Sertif := FindField(Table,'CERTIF','Сертификат');
        thF_DateSertif := nil; //findfield(table,'CERTIFDATE','Дата выдачи сертификата');
        thF_NAMESERTIF := FindField(Table,'CERTIFLAB','Кем выдан сертификат');
        thF_RegN := FindField(Table,'REGNUMBER','Регистрационный номер');
        thF_NDS := FindField(Table,'NDS','Ставка НДС');

        thF_FIRSTPRICE := FindField(Table, 'PFABRNONDS','Цена завода');
        thF_FIRSTPROCENT := nil; //FindField(Table, 'PRC_NAC','% наценки');
        thF_SROKSERTIF := FindField(Table,'CERTIFDATE','Срок действия сертификата');

        thF_Price := FindField(table,'SOPTWNDS','Сумма итого');
        thf_action_lab := findfield(table,'adelta','');

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

        thF_Em := nil; //findfield(table,'package','');
        thF_Price_reg := findfield(table,'RPRICE','Цена реестра');


        thF_Price_RS := FindField(table,OUtPrice_NAME,'');

        data.Add('srok'#9'sroksertif'#9'sertifname'#9+
          'kol'#9'nds'#9+
          'name'#9+
          'firstcena'#9 +
          'seria'#9'sertif'#9+
          'producername'#9+
          'tamog'#9+
          'countryname'+#9+
          'regn'#9+
          'SUMMA'#9+ //ifstr(table.findfield('num_nak')<>nil,'cenanotnds','cena')+#9

          'barcode'+#9+
          'datesertif'+#9+
          'em'+#9+
          'price_reg'+#9+
          ifthenstrmy(useoutprice,'rs_cena'+#9,'')+
          'action_lab'+#9+
          'nn'+#9+
          'producer'+#9
          );

        table.first;

        while not Table.eof do begin
          Str := '';

          Addstr(str,thF_Srok);
          Addstr(str,thF_SrokSertif);
          Addstr(str,thF_NameSertif);
          Addstr(str,thF_Amount);
          Addstr(str,thF_NDS);
          Addstr(str,thF_Name);

          Addstr(str,thF_firstprice);


          Addstr(str,thF_Seria);
          Addstr(str,thF_Sertif);
          Addstr(str,thF_nameproducer);
          Addstr(str,thF_tamog);
          Addstr(str,thF_country);
          Addstr(str,thF_regn);
          Addstr(str,thF_price);
          Addstr(str,thF_barcode);
          Addstr(str,thF_datesertif);
          Addstr(str,thF_em);
          Addstr(str,thF_price_reg);
          if useoutprice then addstr(str,thf_price_rs);
          Addstr(str,thF_action_lab,'0');

          str :=  str +  GetCodeFromOSA(thf_name.asstring,false)  + #9;
          str :=  str +  GetCodeFromOSA(thf_nameproducer.asstring,true)  + #9;

          data.Add(str);
          Table.Next;
        end;
       finally
          Table.free;
       end;

     finally
       FileList.free;
       Invoiceslist.free;
       free
     end;

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