Aleksnick в 13:33, 20 июля 2016

2016-07-20T13:33:28Z

Aleksnick: Новая страница: «ТМС

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

2016-07-20T12:27:57Z

Новая страница: «ТМС <pre> //Выгрузить остатки в DBF на определенную дату program GroupProgram; var s,katalog: string; sl: TStringList;…»

Новая страница

ТМС
<pre>
//Выгрузить остатки в 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.
</pre>

Выгрузка остатков в АСНУ разовая - История изменений

Aleksnick в 13:33, 20 июля 2016

Aleksnick: Новая страница: «ТМС
//Выгрузить остатки в DBF на определенную дату program GroupProgram; var s,katalog: string; sl: TStringList;…»

@@ Строка 1: / Строка 1: @@
-ТМС
+=Формат файла выгрузки=
 <pre>
 //Выгрузить остатки в DBF на определенную дату
@@ Строка 46: / Строка 128: @@
     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 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,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'+
+        ' 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';