// скрипт синхронизации с солярис-bde

const
   solaris_alias = 'farmcenter';

   use_producer_barcodes = true;

   IsDebug = false;



var
  //TaGoods,TaProducers,TaCountries :TTable;

  table :ttable;
  table1 :ttable;

  //cdsGoods,cdsProducers,cdsCountries :TClientdataset;

  cds :tclientdataset;
  changed_cnt,inserted_cnt :integer;

  id :integer;

  id_producers,id_goods :int64;
  barcode :string;

function asvariantwithnull1(Source :string) :variant;
begin
  if trim(source) = ''
  then result := null
  else result := source;
end;



procedure SyncSolarisBDe;
begin
   // comment
   with tmywait.create1('Идет синхронизация данных с каталогом: '+solaris_alias,selfscript) do
   begin
     {
      taGoods := ttable.create(selfscript);
      taGoods.databasename := solaris_alias;
      taProducers := ttable.create(selfscript):
      taProducers.databasename := solaris_alias;
      taCountries := ttable.create(selfscript):
      taCountries.databasename := solaris_alias;

      cdsGoods := tclientdataset.create(selfscript);
      cdsProducers := tclientdataset.create(selfscript);
      cdsCountries := tclientdataset.create(selfscript);
      }

      if session.netfiledir <> getaliaspath(solaris_alias)
      then  Session.netfiledir := getaliaspath(solaris_alias);



      table := ttable.create(selfscript);
      table.databasename := solaris_alias;

      table1 := ttable.create(selfscript);
      table1.databasename := solaris_alias;

      cds := tclientdataset.create(selfscript);

      advancedtext := 'Страны';
      table.close;
      table.tablename := 'country';
      table.open;
      advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';


      dbreadquery(cds,'select * from countries',[null]);


      cds.indexfieldnames := 'code';

      changed_cnt := 0;
      inserted_cnt := 0;

      progressbarpos := 0;
      progressbarmax := table.recordcount;
      table.first;
      while not table.eof do
      begin
        if not (table.fieldbyname('name').asstring='')
           and
           (not cds.findkey([table.fieldbyname('id').asstring])
            or (cds.fieldbyname('name').asstring <> firstuppercase(table.fieldbyname('name').asstring))
            or (cds.fieldbyname('code_iso_num').asstring <> table.fieldbyname('code_iso_num').asstring)
            or (cds.fieldbyname('code_iso_alpha2').asstring <> table.fieldbyname('code_iso_alpha2').asstring)
            or (cds.fieldbyname('code_iso_alpha3').asstring <> table.fieldbyname('code_iso_alpha3').asstring)
           )
        then begin

          {
           createhinti(
            cds.fieldbyname('CODE').asstring+' : '+ table.fieldbyname('ID').asstring +#13+
            cds.fieldbyname('name').asstring+' : '+ table.fieldbyname('name').asstring +#13+
            cds.fieldbyname('code_iso_num').asstring+' : '+ table.fieldbyname('code_iso_num').asstring  +#13+
            cds.fieldbyname('code_iso_alpha2').asstring+' : '+ table.fieldbyname('code_iso_alpha2').asstring  +#13+
            cds.fieldbyname('code_iso_alpha3').asstring+' : '+ table.fieldbyname('code_iso_alpha3').asstring);

           exit;
          }

           if table.fieldbyname('id').asstring = cds.fieldbyname('code').asstring
           then inc(changed_cnt)
           else inc(inserted_cnt);


            dbExecSQL('update or insert into countries (code,name,code_iso_num,code_iso_alpha2,code_iso_alpha3) '+
                    ' values (:code,:name,:code_iso_num,:code_iso_alpha2,:code_iso_alpha3) '+
                    ' matching (code)',
                    [table.fieldbyname('id').asstring,
                    table.fieldbyname('name').asstring,
                    asvariantwithnull1(table.fieldbyname('code_iso_num').asstring),
                    asvariantwithnull1(table.fieldbyname('code_iso_alpha2').asstring),
                    asvariantwithnull1(table.fieldbyname('code_iso_alpha3').asstring)
                    ]);


        end;



        table.next;
        incprogress;
        advancedtext := 'Страны - изменено: '+inttostr(changed_cnt)+', добавлено: '+inttostr(inserted_cnt);
        application.processmessages;

      end;


      advancedtext := 'Производители';
      table.close;
      table.tablename := 'producer';
      table.open;
      advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';



      dbreadquery(cds,'select a.*,(select b.code from countries b where b.id =  a.id_countries) as code_country from producers a',[null]);


      cds.indexfieldnames := 'code';

      changed_cnt := 0;
      inserted_cnt := 0;


      progressbarpos := 0;
      progressbarmax := table.recordcount;
      table.first;
      while not table.eof do
      begin
        if not (table.fieldbyname('name').asstring='')
           and
           (not cds.findkey([table.fieldbyname('id').asstring])
            or (cds.fieldbyname('name').asstring <> firstuppercase(table.fieldbyname('name').asstring))
            or (cds.fieldbyname('address').asstring <> table.fieldbyname('address').asstring)
            or (cds.fieldbyname('isdisabled').asinteger <> table.fieldbyname('notused').asinteger)
            or (cds.fieldbyname('code_country').asstring <> table.fieldbyname('country').asstring)
           )
        then begin
           {
           createhinti(
            cds.fieldbyname('CODE').asstring+' : '+ table.fieldbyname('ID').asstring +#13+
            cds.fieldbyname('name').asstring+' : '+ firstuppercase(table.fieldbyname('name').asstring) +#13+
            cds.fieldbyname('address').asstring+' : '+ table.fieldbyname('address').asstring  +#13+
            inttostr(cds.fieldbyname('isdisabled').asinteger)+' : '+ inttostr(table.fieldbyname('notused').asinteger)  +#13+
            cds.fieldbyname('code_country').asstring+' : '+ table.fieldbyname('country').asstring);
            }


           if table.fieldbyname('id').asstring = cds.fieldbyname('code').asstring
           then inc(changed_cnt)
           else inc(inserted_cnt);

            dbExecSQL('update or insert into producers (code,name,id_countries,address,isdisabled) '+
                    ' values (:code,:name,(select first 1 id from countries where  code = :code_countries),:address,:isdisabled) '+
                    ' matching (code)',
                    [table.fieldbyname('id').asstring,
                    table.fieldbyname('name').asstring,
                    asvariantwithnull1(table.fieldbyname('country').asstring),
                    asvariantwithnull1(table.fieldbyname('address').asstring),
                    table.fieldbyname('notused').asinteger
                    ]);



        end;



        table.next;
        incprogress;
        advancedtext := 'Производители - изменено: '+inttostr(changed_cnt)+', добавлено: '+inttostr(inserted_cnt);
        application.processmessages;

      end;





      advancedtext := 'Товар';
      table.close;
      table.tablename := 'tovar';
      table.open;

      table1.close;
      table1.tablename := 'producer';
      table1.open;

      advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';


      dbreadquery(cds,'select a.*,(select code from producers where id =  a.id_producers) as code_producer from goods a',[null]);

      cds.indexfieldnames := 'code';

      changed_cnt := 0;
      inserted_cnt := 0;

      progressbarpos := 0;
      progressbarmax := table.recordcount;
      table.first;
      while not table.eof do
      begin
        if not (table.fieldbyname('name').asstring='')
           and
           (not cds.findkey([table.fieldbyname('nn').asstring])
            or (cds.fieldbyname('name').asstring <> firstuppercase(table.fieldbyname('name').asstring))
            or (cds.fieldbyname('name_small').asstring <> table.fieldbyname('smallname').asstring)
            or (cds.fieldbyname('mnn').asstring <> table.fieldbyname('intername').asstring)
            or (cds.fieldbyname('nds').asinteger <> table.fieldbyname('nds').asinteger)
            or (cds.fieldbyname('units').asstring <> table.fieldbyname('ed').asstring)
            or (cds.fieldbyname('isliveneed').asinteger <> table.fieldbyname('isliveneed').asinteger)
            or (cds.fieldbyname('isrecepted').asinteger <> table.fieldbyname('isrecepted').asinteger)
            or (cds.fieldbyname('ispril').asinteger <> table.fieldbyname('ispril').asinteger)
            or (cds.fieldbyname('isprotocol').asinteger <> table.fieldbyname('isprotocol').asinteger)
            or (cds.fieldbyname('isfloatbarcode').asinteger <> table.fieldbyname('floatbarcode').asinteger)
            or (cds.fieldbyname('ispartial_discount').asinteger <> table.fieldbyname('partdiscount').asinteger)
            or (cds.fieldbyname('iswithoutbarcode').asinteger <> table.fieldbyname('withoutbarcode').asinteger)
            or (cds.fieldbyname('maxpercent').asinteger <> table.fieldbyname('maxprocent').asinteger)
            or (cds.fieldbyname('okdp').asstring <> table.fieldbyname('okdp').asstring)
            or (cds.fieldbyname('article').asstring <> table.fieldbyname('article').asstring)
            or (cds.fieldbyname('barcode').asstring <> table.fieldbyname('barcode').asstring)


            or (table.fieldbyname('default_id_producer').asinteger<>0)
               and table1.findkey([table.fieldbyname('default_id_producer').asinteger])
               and (trim(table1.fieldbyname('name').asstring)<>'')
               and (cds.fieldbyname('code_producer').asstring <> table.fieldbyname('default_id_producer').asstring)

           or (inttostr(cds.fieldbyname('isdisabled').asinteger) <> inttostr(table.fieldbyname('notused').asinteger))


           )

              {
              FibQuery.ParamByName('name').AsString := coalesce(Table.FieldByName('name').AsString,'?');
              FibQuery.ParamByName('name_small').Value  := AsVariantWithNull( Table.FieldByName('smallname').AsString);
              FibQuery.parambyname('name_english').Clear;

              FibQuery.ParamByName('mnn').Value :=  AsVariantWithNull( Table.FieldByName('intername').AsString);
              FibQuery.ParamByName('nds').AsInteger := Table.FieldByName('nds').asinteger;
              FibQuery.ParamByName('units').Value := AsVariantWithNull( Table.FieldByName('ed').AsString);
              FibQuery.ParamByName('isliveneed').AsInteger := ifthen( Table.FieldByName('isliveneed').asinteger=0,0,1);
              FibQuery.ParamByName('isrecepted').AsInteger := ifthen(Table.FieldByName('isrecepted').asinteger=0,0,1);
              FibQuery.ParamByName('ispril').AsInteger := ifthen(Table.FieldByName('ispril').asinteger=0,0,1);
              FibQuery.ParamByName('isprotocol').AsInteger := ifthen(Table.FieldByName('isprotocol').asinteger=0,0,1);;
              FibQuery.ParamByName('isdisabled').AsInteger := ifthen(Table.FieldByName('notused').asinteger=0,0,1);;
              FibQuery.ParamByName('isfloatbarcode').AsInteger := ifthen(Table.FieldByName('floatbarcode').asinteger=0,0,1);;
              FibQuery.ParamByName('ispartial_discount').AsInteger := ifthen(Table.FieldByName('partdiscount').asinteger=0,0,1);;
              FibQuery.ParamByName('iswithoutbarcode').AsInteger := ifthen(Table.FieldByName('withoutbarcode').asinteger=0,0,1);;
              FibQuery.ParamByName('maxpercent').AsInteger := Table.FieldByName('maxprocent').asinteger;


              if table.fieldbyname('divnum').asinteger>1
              then
                FibQuery.ParamByName('dnmr').AsInteger := Table.FieldByName('divnum').asinteger
              else
                FibQuery.ParamByName('dnmr').clear;

            if (Table.FieldByName('em').asfloat<=1) or (Table.fieldbyname('em').AsFloat>100000)
            then FibQuery.ParamByName('place_cap').Clear
            else FibQuery.ParamByName('place_cap').asinteger := round(Table.FieldByName('em').asfloat);

              if Pos('$',Table.FieldByName('nn').AsString)>0
              then FibQuery.parambyname('id_currencies').AsInteger := MaxStorages +MyID
              else FibQuery.ParamByName('id_currencies').clear;

              //if Int64VAl3< table.fieldbyname('id_brak_sn').AsInteger
              //then Int64VAl3:= table.fieldbyname('id_brak_sn').AsInteger;

              if Table2.FindKey([Table.FieldByName('nn').AsString])
              then begin
                 int64val5 := table2.FieldByName('idgroup').AsInteger;
                 if int64val4 < int64val5 then Int64VAl4:= int64VAl5;
              end
              else
                Int64VAl5:= 0;

              if Table3.FindKey([Table.FieldByName('nn').AsString])
              then begin
                 int64val7 := table3.FieldByName('idgroup').AsInteger;
                 if int64val6 < int64val7 then Int64VAl6:= int64VAl7;
              end
              else
                Int64VAl7:= 0;


              FibQuery.ParamByName('id_group_assort').Value  := AsVariantWithNull( Int64VAl5);
              FibQuery.ParamByName('id_group_bad_series').Value  := AsVariantWithNull( Int64VAl7);


              //FibQuery.ParamByName('id_group_bad_series').Value  := AsVariantWithNull( table.fieldbyname('id_brak_sn').AsInteger);
              FibQuery.ParamByName('okdp').Value :=  AsVariantWithNull( Table.FieldByName('okdp').AsString);
              FibQuery.ParamByName('article').Value :=  AsVariantWithNull( Table.FieldByName('article').AsString);
              FibQuery.ParamByName('barcode').Value :=  AsVariantWithNull( Table.FieldByName('barcode').AsString);
              FibQuery.ParamByName('id_producers').AsInt64 :=  Int64(Table.FieldByName('default_id_producer').asinteger) * MaxStorages +MyID;

              }




        then begin

           {
           createhinti(
            quotedstr(cds.fieldbyname('CODE').asstring)+' : '+ quotedstr(table.fieldbyname('nn').asstring) +#13+
            quotedstr(cds.fieldbyname('name').asstring)+' : '+ quotedstr(firstuppercase(table.fieldbyname('name').asstring)) +#13+
            quotedstr(cds.fieldbyname('name_small').asstring)+' : '+ quotedstr(table.fieldbyname('smallname').asstring)+#13+
            quotedstr(cds.fieldbyname('mnn').asstring)+' : '+ quotedstr(table.fieldbyname('intername').asstring) +#13+
            quotedstr(inttostr(cds.fieldbyname('nds').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('nds').asinteger)) +#13+
            quotedstr(cds.fieldbyname('units').asstring)+' : '+ quotedstr(table.fieldbyname('ed').asstring) +#13+
            quotedstr(inttostr(cds.fieldbyname('isliveneed').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('isliveneed').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('isrecepted').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('isrecepted').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('ispril').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('ispril').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('isprotocol').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('isprotocol').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('isfloatbarcode').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('floatbarcode').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('ispartial_discount').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('partdiscount').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('iswithoutbarcode').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('withoutbarcode').asinteger)) +#13+
            quotedstr(inttostr(cds.fieldbyname('maxpercent').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('maxprocent').asinteger)) +#13+
            quotedstr(cds.fieldbyname('okdp').asstring)+' : '+ quotedstr(table.fieldbyname('okdp').asstring)+#13+
            quotedstr(cds.fieldbyname('article').asstring)+' : '+ quotedstr(table.fieldbyname('article').asstring)+#13+
            quotedstr(cds.fieldbyname('barcode').asstring)+' : '+ quotedstr(table.fieldbyname('barcode').asstring)+#13+
            quotedstr(inttostr(cds.fieldbyname('isdisabled').asinteger))+' : '+ quotedstr(inttostr(table.fieldbyname('notused').asinteger)) +#13+

            quotedstr(cds.fieldbyname('code_producer').asstring)+' : '+ quotedstr(table.fieldbyname('default_id_producer').asstring)+#13

             );

           exit;
           }



           if table.fieldbyname('nn').asstring = cds.fieldbyname('code').asstring
           then inc(changed_cnt)
           else inc(inserted_cnt);


            dbExecSQL(
                    ' update or insert into goods( '+
                    ' code,name,name_small,nds,units,isliveneed,isrecepted '+
                    ' ,maxpercent,ispril,isprotocol'+
                    ' ,mnn,isdisabled,okdp,article,barcode,isfloatbarcode'+
                    ' ,ispartial_discount,id_producers,iswithoutbarcode'+
                    ')' +
                    ' values( '+
                    ' :code,:name,:name_small,:nds,:units,:isliveneed,:isrecepted '+
                    ' ,:maxpercent,:ispril,:isprotocol'+
                    ' ,:mnn,:isdisabled,:okdp,:article,:barcode,:isfloatbarcode'+
                    ' ,:ispartial_discount,(select first 1 id from producers where code = :code_producer),:iswithoutbarcode'+
                    ') matching (code)',

                    [table.fieldbyname('nn').asstring,
                    table.fieldbyname('name').asstring,
                    asvariantwithnull1(table.fieldbyname('smallname').asstring),
                    table.fieldbyname('nds').asinteger,
                    asvariantwithnull1(table.fieldbyname('ed').asstring),
                    table.fieldbyname('isliveneed').asinteger,
                    table.fieldbyname('isrecepted').asinteger,
                    table.fieldbyname('maxprocent').asinteger,
                    table.fieldbyname('ispril').asinteger,
                    table.fieldbyname('isprotocol').asinteger,
                    asvariantwithnull1(table.fieldbyname('intername').asstring),
                    table.fieldbyname('notused').asinteger,
                    asvariantwithnull1(table.fieldbyname('okdp').asstring),
                    asvariantwithnull1(table.fieldbyname('article').asstring),
                    asvariantwithnull1(table.fieldbyname('barcode').asstring),
                    table.fieldbyname('floatbarcode').asinteger,
                    table.fieldbyname('partdiscount').asinteger,
                    table.fieldbyname('default_id_producer').asstring,
                    table.fieldbyname('withoutbarcode').asinteger
                    ]);

        end;



        table.next;
        incprogress;
        advancedtext := 'Товары - изменено: '+inttostr(changed_cnt)+', добавлено: '+inttostr(inserted_cnt);
        application.processmessages;

      end;


      if not isdebug
      then begin
        advancedtext := 'Производители  - глоссарии';
        table.close;
        table.tablename := 'prodglos';
        table.open;
        advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';


        progressbarpos := 0;
        progressbarmax := table.recordcount;
        table.first;
        while not table.eof do
        begin
           try
              id := asinteger(DBQueryValue('select id from producers where code = :code',[table.fieldbyname('id').asstring]));


              if id<>0
              then
                dbExecSQL('execute procedure save_glossary_producers(:nm,null, :id,1)',[table.fieldbyname('name').asstring,id]);

              {
              if id=2487001
              then begin
                 createhinti(inttostr(id)+' : '+table.fieldbyname('name').asstring);
                 exit;
              end;
              }


           except

              createhinte(table.fieldbyname('name').asstring+' : '+table.fieldbyname('nn').asstring+' : '+inttostr(id)+#13+exceptmessage,'Ошибка заполнения глоссария производителей');
              exit;

           end;


          table.next;
          incprogress;
          application.processmessages;

        end;
      end;


      if not isdebug
      then begin
        advancedtext := 'Товары  - глоссарии';
        table.close;
        table.tablename := 'nnglos';
        table.open;
        advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';


        progressbarpos := 0;
        progressbarmax := table.recordcount;
        table.first;
        while not table.eof do
        begin
           try

              {
              dbExecSQL('execute block (NM varchar(300) = :NM, NN varchar(10)  = :NN) as  '+
                       ' declare variable nm_norm type of column goods_glossary.name; '+
                       ' declare variable ID type of column goods.id = null; '+
                       ' begin ' +
                       '   select result from get_normalize_name_goods(:nm) into :nm_norm; '+
                       '   select id from goods where code = :nn into :id; '+
                       '   if(nm_norm <> ''-'' and id is not null '+
                       '      and not exists(select id from goods_glossary where name = :nm_norm and id_goods = :ID and name_producer is null and name_country is null) '+
                       '   ) '+
                       '   then  '+
                       '     insert into goods_glossary(name,id_goods) values (:nm,:id);  '+
                       ' end',[table.fieldbyname('name').asstring,table.fieldbyname('nn').asstring]);
                       }
              id := asinteger(DBQueryValue('select id from goods where code = :code',[table.fieldbyname('nn').asstring]));
              if id<>0
              then
                dbExecSQL('execute procedure save_glossary_goods(:NM,null,null, :id,1)',[table.fieldbyname('name').asstring,id]);
           except

              createhinte(table.fieldbyname('name').asstring+' : '+table.fieldbyname('nn').asstring+' : '+inttostr(id)+#13+exceptmessage,'Ошибка заполнения глоссария товаров');
              exit;

           end;


          table.next;
          incprogress;
          application.processmessages;

        end;
      end;


      if use_producer_barcodes
      then begin
        advancedtext := 'Штрихкоды от производителей';
        table.close;
        table.tablename := 'barcodpr';
        table.open;
        advancedtext := advancedtext + ' - '+inttostr(table.recordcount)+ ' записей';

        inserted_cnt := 0;
        changed_cnt := 0;


        progressbarpos := 0;
        progressbarmax := table.recordcount;
        table.first;
        while not table.eof do
        begin
           try
              barcode :=  CorrectBarCode(table.fieldbyname('barcode').asstring,
                             false,true,false,false);

              if (barcode<>'')
                 and (trim(table.fieldbyname('nn').asstring)<>'')
                 and (table.fieldbyname('producer').asinteger<>0)
              then begin

                id_goods := asinteger(DBQueryValue('select id from goods where code = :code',[trim(table.fieldbyname('nn').asstring)]));
                id_producers := asinteger(DBQueryValue('select id from producers where code = :code',[table.fieldbyname('producer').asstring]));


                if (id_goods<>0) and (id_producers<>0) // если не найдены, то бросим эту позицию
                then begin

                  FIBReadQuery(cds,'select * from producers_barcodes where barcode= :barcode',[barcode], MainReadTransaction);



                  cds.first;
                  if cds.isempty
                     or (asint64(cds.fieldbyname('id_goods').asvariant)<>id_goods)
                     or (asint64(cds.fieldbyname('id_producers').asvariant)<>id_producers)
                  then begin
                   DBExecSQL('update or insert into producers_barcodes (barcode,id_goods,id_producers) '+
                              ' values (:barcode,:id_goods,:id_producers) '+
                              ' matching (BARCODE) ',
                              [barcode,id_goods,id_producers]);

                 //createhinti(barcode);

                    if cds.isempty
                    then
                      inc(inserted_cnt)
                    else
                      inc(changed_cnt);

                    advancedtext := 'Штрихкоды от производителей - изменено: '+inttostr(changed_cnt)+', добавлено: '+inttostr(inserted_cnt);

                  end;
                end;
              end;


           except

              createhinte(table.fieldbyname('barcode').asstring+' : '+table.fieldbyname('nn').asstring+' : '+table.fieldbyname('producer').asstring+#13+exceptmessage,'Ошибка заполнения штрихкодов от производителей');
              exit;

           end;


          table.next;
          incprogress;
          application.processmessages;

        end;
      end;


   end;

   createhinti('Операция синхронизации справочников и глоссариев завершена');
end;

begin
  syncsolarisbde;

end.