// Экспорт прайс-листа в фармнет, используется MailProcessor

var
    MailMessage :Variant;
    Agents :Variant;

var query :TQuery;
    s :string;
    Stream :tMemoryStream;
    CurrNN :string;
    List1 :tstringlist;

const hourofexport = 10; // время, во сколько данный экспорт должен произойти
      UseFromMenu = true;
      price_recipient = 'saratov-spr@farmnet.ru' ;

      DiscountPercent = 0;
      DiscountPercentForLiveneed = 0;

      Storage = 0; // склад

      UsePartial = false; // false - передать все остатки (непроданные), true - передать часть прайса, в зависммости от UsePartialByLastDAte
      UsePartialByLastDate = true; // Если UsePartial = true, то: false = по самым дорогим ценам, true - по самому последнему приходу

var
      Price :currency;

begin
  //if (dayofweek(date()) = 2) and (hourof(now())=10)
  //if (dayofweek(date()) = 5) and (hourof(now())=12)
  if useFromMenu or (hourof(now())=hourofexport)
  then // Экспорт прайс-листа
  with TMyWait.create('Идет сбор данных для прайс-листа') do
  try
    Query := TQuery.create(nil);
    try
      Query.sql.text := 'select a.nn,b.name,c.name as name_producer,d.name as name_country '+
        //Roundsqlto(' a.rs_cena * (1 + '+inttostr(discountPercent)+' / 100) ',-2)+' as price, '+
        ' ,a.rs_cena as price '+
        //' a.rs_cena as price_base, ' +
        ' ,pr.data as date_doc '+
        ' ,b.isliveneed '+
        ' from pr_nakl pr left join pr_tovar a on pr.id= a.id_nakl '+
        ' left join tovar b on a.nn = b.nn'+
        ' left join producer c on a.producer = c.id'+
        ' left join country d on c.country = d.id'+
        ' where ostat >0 ';

      if Storage <> 0 then Query.sql.add(' and a.sklad = '+inttostr(Storage));

      Query.sql.add(' order by b.name,a.nn');

      if UsePartial
      then begin
        if UsePartialByLastDAte
        then begin
          Query.sql.add(',pr.data desc');
        end
        else begin
          Query.sql.add(',a.rs_cena desc');
        end;
      end;

      Query.databasename := 'dbkassa';
      query.open;

      Query.first;
      s := 'nn'+#9+'name'+#9+'name_producer'+#9+'name_country'
           +#9+'price'
           //+#9+'price_base'
           //+#9+'isliveneed'
           +#9+'date_doc'+ #13#10;

      CurrNN := '---------------------------';

      while not Query.eof do
      begin
        if not UsePartial or ( CurrNN <> Query.fieldbyname('nn').asstring)
        then begin
            Price := Query.fieldbyname('Price').asFloat;

            if (Query.fieldbyname('isliveneed').asinteger = 1)
            then begin
              Price := bvroundto( Price * (1 + DiscountPercentForLiveneed / 100),-2);
            end
            else begin
              Price := bvroundto( Price * (1 + DiscountPercent / 100),-2);
            end;


            S := S + Query.fieldbyname('NN').asstring + #9 + Query.fieldbyname('name').asstring + #9
                   + Query.fieldbyname('name_producer').asstring + #9
                   + Query.fieldbyname('name_country').asstring + #9
                   + floattostr(Price) + #9
                   //+ Query.fieldbyname('price').asstring + #9
                   //+ Query.fieldbyname('isliveneed').asstring + #9
                   + Query.fieldbyname('date_doc').asstring+ #13#10;
            CurrNN := Query.fieldbyname('nn').asstring;
        end;

        Query.next;
      end;



      list1 := tstringlist.create;
      STream := TMemoryStream.create;
      try
        list1.text := s;
        list1.savetostream(stream);
        //Stream.write(S,length(S));
        //StringToFile(gettempdir+'price.txt' ,s);

        //ZipAddFS(const ZipName :string; SourceStream: TStream; const FileName :string; const BaseDirectory :string = '')
        deletefile(gettempdir+'price.zip');
        ZipAddFS(gettempdir+'price.zip', Stream, 'price.txt');
        //ZipAdd(gettempdir+'price.zip',gettempdir+'price.txt', gettempdir );
      finally
        Stream.free;
        list1.free;
      end;




  Agents := CreateOleObject('mailprocessor.addressbook');
  MailMessage := CreateOleObject('mailprocessor.mpmailmessage');
  Agents.SRV.ConnectWP('Пользователь','1');
  if not agents.srv.connected
  then begin
    CreateHintE('Не могу присоединиться.');
    exit;
  end;

  MailMessage.ID := 0;
  MailMessage.Recipient :=  price_recipient;
  MailMessage.Subject := 'Прайс-лист';
  MailMessage.AddAttachment(gettempdir+'price.zip');
  MailMessage.Save;

  //Application.Terminate;


    finally
      Query.free
    end

  finally
    free
  end;


end.