1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
|
IBDatabase1.GetTableNames(listbox1.Items,false);
ibquery1.SQL.clear;
for i:=0 to listbox1.Items.count-1 do
begin
if (copy(listbox1.Items[i],1,1)='R') then
begin
for j:=Yearof(DATE_INSTALL) to Yearof(DATE_JOUR) do
begin
if trim(ibquery1.SQL.Text)<>'' then
ibquery1.sql.add('UNION ALL');
ibquery1.sql.add('select s.SF_HORSCATVA,c.SF_CODE,upper(b.cl_nom), a.cl_num,cast(0 as integer) as GR_CODE,b.cl_nom');
ibquery1.sql.add(', rr.R1_NUM, c.FA_DATE,c.AR_CODE, c.AR_LIBELLE, c.AR_QTE, c.AR_PRIX, c.AR_REMISE, c.AR_COMM, c.AR_OFFERT, c.AR_TIME,');
ibquery1.sql.add('rr.AR_TRANSFERT, c.AR_TYPE, c.AR_TYPEVENTIL, c.AR_VENTIL, c.FA_DATE, c.COM_CODE, c.FA_NUM, rr.FA_FOLIO,');
ibquery1.sql.add('c.FA_NUMLIGNE, c.REM_CODE, c.RF_MENU, c.RF_NUMMASTER, s.SF_LIBELLE,rr.AR_PRIX as prix1,f.fa_reglee as prix2');
ibquery1.sql.add('from RH'+copy(inttostr(j),3,2)+' rr ');
ibquery1.sql.add('join '+listbox1.Items[i]+' c on c.fa_num=rr.AR_TRANSFERT' );
ibquery1.sql.add('join F'+copy(listbox1.Items[i],2,length(listbox1.Items[i])-1)+' f on f.fa_num=c.fa_num');
ibquery1.sql.add('join T_RESAH a on a.r1_num=rr.r1_num and a.cl_num<>0 ');
ibquery1.sql.add('join T_CLIENT b on b.cl_num=a.cl_num and B.cl_type<>2');
ibquery1.sql.add('join T_serv s on s.sf_code=c.sf_code');
ibquery1.SQL.add('where rr.ar_type=0 and rr.fa_num=0');
ibquery1.sql.add('UNION ALL');
ibquery1.sql.add('select SF_HORSCATVA, c.SF_CODE,upper(b.cl_nom),a.cl_num,b.GR_CODE,b.cl_nom');
ibquery1.sql.add(', rr.R1_NUM, c.FA_DATE,c.AR_CODE, c.AR_LIBELLE, c.AR_QTE, c.AR_PRIX, c.AR_REMISE, c.AR_COMM, c.AR_OFFERT, c.AR_TIME,');
ibquery1.sql.add('rr.AR_TRANSFERT, c.AR_TYPE, c.AR_TYPEVENTIL, c.AR_VENTIL, c.FA_DATE, c.COM_CODE, c.FA_NUM, rr.FA_FOLIO,');
ibquery1.sql.add('c.FA_NUMLIGNE, c.REM_CODE, c.RF_MENU, c.RF_NUMMASTER,s.SF_LIBELLE,rr.AR_PRIX as prix1,f.fa_reglee as prix2');
ibquery1.sql.add('from RH'+copy(inttostr(j),3,2)+' rr ');
ibquery1.sql.add('join '+listbox1.Items[i] +' c on c.fa_num=rr.AR_TRANSFERT ' );
ibquery1.sql.add('join F'+copy(listbox1.Items[i],2,length(listbox1.Items[i])-1)+' f on f.fa_num=c.fa_num');
ibquery1.sql.add('left outer join T_RESAH a on a.r1_num=rr.r1_num and a.cl_num=0 and a.gr_code<>0 ');
ibquery1.sql.add('join T_CLIENT b on b.gr_code=a.gr_code ');
ibquery1.sql.add('join T_serv s on s.sf_code=c.sf_code');
ibquery1.SQL.add('where rr.ar_type=0 and rr.fa_num=0');
end;
end;
ibquery1.sql.add('ORDER BY 1,2');
ibquery1.Open; |
Partager