Выгрузка остатков в АСНУ разовая — различия между версиями

Версия 17:27, 20 июля 2016

ТМС

//Выгрузить остатки в DBF на определенную дату
program GroupProgram;
var s,katalog: string;
    sl: TStringList;
    trn,query,con: cardinal;
    dateload: string;
begin

  sl:=TStringList.Create;
  s:='[dateload]'#13#10+
  'type=date'#13#10+
  'caption=Дата'#13#10+
  'default=now';
  sl.text:=s;
  if GetCustomParams(sl,'заполните значения') then
  begin      
  end
  else
   begin
    showmessage('Действие отменено пользователем');
    exit;
   end; 
  dateload:=sl.values['dateload'];

  trn:=CREATEIBTRANSACTION;    
  STARTIBTRANSACTION(trn,1);                    
                                                                          
                                                                                  
  try
//    s:='select PARAM_VALUE from PARAMS where PARAM_ID=''PARAMS_TEMP_PATH''';
//    query:=ExecIBQuery(trn,s,1);
//    if not IBQUERY_EOF(query) then                                          
//     katalog:=IBQuery_FieldByName_s(query, 'PARAM_VALUE')+'\';
    katalog:='d:\';                                                              
    deletefilemask(katalog+'*temp.dbf');
    
    con:=CreateADOConnection('Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source='+katalog);
    if not FileExists(katalog+'temp.dbf') then
    begin
     s:='CREATE TABLE temp (NDOC CHAR(20), DATEDOC DATE, CODEPST CHAR(12), EAN13 CHAR(13), PRICE1 NUMERIC(9,2), PRICE2 NUMERIC(9,2), PRICE2N NUMERIC(9,2), QNT NUMERIC(9,2), SER CHAR(12), GDATE DATE, NAME CHAR(80), PRICER NUMERIC(9,2))';
     m_manager.Logit(s);
     ExecADOQuery(con, s);
    end                                            
  
   s:= 'select u.* from '+
       ' (select 1 as NDOC, current_date as DATEDOC, LEFT(p.NAME_id,12) as CODEPST, LEFT(p.barcode,13) as EAN13, round(coalesce(p.price_z,0),2) as PRICE1,round(p.price_o,2) as PRICE2, round((p.price_o - p.price_o*p.nds/100),2) as PRICE2N, round(w.quant,2) as QNT, LEFT(p.seria,12) as SER, coalesce(cast(p.godendo as dm_date), ''01.01.2000'') as GDATE,  LEFT(p.sname,80) as NAME, round(coalesce(p.price_r,0),2) as PRICER from '+
       '  (select part_id, sum(quant) as quant, sum(sum_ndso) as sum_ndso, sum(sum_ndsr) as sum_ndsr from doc_detail '+
       ' where part_type=0 and doc_commitdate<'''+dateload+''' group by part_id having abs(sum(quant))>0.01) w'+
       '                left join vw_parts p on p.part_id=w.part_id) '+
       ' u order by u.name';
    m_manager.Logit(s);       
   query:=ExecIBQuery(trn,s,1);
   while not IBQUERY_EOF(query) do
   begin
    s:='insert into temp (NDOC,DATEDOC,CODEPST,EAN13,PRICE1,PRICE2,PRICE2N,QNT,SER,GDATE,NAME,PRICER) values ('''+
    IBQuery_FieldByName_s(query, 'NDOC')+''','''+IBQuery_FieldByName_s(query, 'DATEDOC')+''','''+IBQuery_FieldByName_s(query, 'CODEPST')+''','''+
    IBQuery_FieldByName_s(query, 'EAN13')+''','+stringreplace(IBQuery_FieldByName_s(query, 'PRICE1'),',','.',1)+','+stringreplace(IBQuery_FieldByName_s(query, 'PRICE2'),',','.',1)+','+
    stringreplace(IBQuery_FieldByName_s(query, 'PRICE2N'),',','.',1)+','+stringreplace(IBQuery_FieldByName_s(query, 'QNT'),',','.',1)+','''+IBQuery_FieldByName_s(query, 'SER')+''','''+IBQuery_FieldByName_s(query, 'GDATE')+''','''+
    stringreplace(stringreplace(IBQuery_FieldByName_s(query, 'NAME'),'''','',1),'"','',1)+''','+stringreplace(IBQuery_FieldByName_s(query, 'PRICER'),',','.',1)+')';
    m_manager.Logit(s);
    ExecADOquery(con, s);
    IBQUERY_NEXT(query);
   end;
   m_manager.Logit('Идет формирование файла, ожидайте ...');
         
    s:='Выгрузка на '+dateload+' (сформировано '+IBQuery_FieldByName_s(query, 'DATEDOC')+').dbf';
//    copyfile(katalog+'temp.dbf',s,true);
   //RenameFile(katalog+'temp.dbf',katalog+s);
   m_manager.Logit(katalog+'temp.dbf');
   ShellExecute( 0, 'open', 'explorer', PChar(katalog), '', SW_NORMAL );   
      
  except
   showmessage('err'); 
   EndIBTransaction(trn,0);
   exit;
  end;    
  FREEIBQUERY(query);        
  FREEIBTRANSACTION(trn);
  
  m_manager.Logit('Готово!'); 
  
end.