代码如下,其中C_staty是客户端的clientdataset,通过服务器端的datasetprovider连接adoquery来查询,用的sql2000.
C_staty.Close;
C_staty.CommandText:=maketysql;
C_staty.Open;
function Tb_sta_Form.maketysql:string;
var
sql:string;
begin
sql:='select a.job_no as 工作号,i.应收,i.未收,i.应付,i.未付,i.预计利润,i.实际利润,isnull(o.柜量,0) as 柜量,a.sales_id,a.cli_cname,isnull(a.biz_no,''---'') as 业务编号,a.sailingdt,a.load_port,a.dest_port '
+'from '
+'(select distinct l.job_no,m.应收,m.未收,n.应付,n.未付,(m.应收-n.应付)as 预计利润,(m.应收-m.未收-n.应付+n.未付) as 实际利润 from b_fee l, '
+'(select t.job_no,sum(t.应收) as 应收,sum(t.未收)as 未收 from '
+'(select job_no,cur_code, case cur_code '
+'when ''USD'' then
sum(amount)*8.0852 '
+'when ''HKD'' then
sum(amount)*1.0765 '
+'else
sum(amount) end as 应收, '
+'case cur_code '
+'when ''USD'' then
sum(amount_left)*8.0852 '
+'when ''HKD'' then
sum(amount_left)*1.0765 '
+'else
sum(amount_left) end as 未收 from b_fee where rp_flag=''R'' group by job_no,cur_code) as t group by t.job_no) as m, '
+'(select p.job_no,sum(p.应付) as 应付,sum(p.未付)as 未付 from '
+'(select job_no,cur_code, '
+'case cur_code '
+'when ''USD'' then
sum(amount)*8.0852 '
+'when ''HKD'' then
sum(amount)*1.0765 '
+'else
sum(amount) end as 应付, '
+'case cur_code '
+'when ''USD'' then
sum(amount_left)*8.0852 '
+'when ''HKD'' then
sum(amount_left)*1.0765 '
+'else
sum(amount_left) end as 未付 from b_fee where rp_flag=''P'' group by job_no,cur_code) as p group by p.job_no) as n '
+'where l.job_no*=m.job_no and l.job_no*=n.job_no '
+') as i, '
+'(select y.job_no,sum(y.柜量)as 柜量,y.dep_code from (select t.job_no,sum(t.柜量) as 柜量,t.dep_code from (select a.ctn_size,case a.ctn_size when ''40'' then
sum(a.ctn_number)*2 when ''45'' then
sum(a.ctn_number)*2 '
+'else
sum(a.ctn_number) end as 柜量,d.job_no,d.dep_code from b_imex_ctn a,b_oceanimport b,b_jobsheet d '
+'where a.job_no=b.job_no and b.job_no=d.job_no group by a.ctn_size,d.job_no,d.dep_code )as t group by t.job_no,t.dep_code '
+'union '
+'select t.job_no,sum(t.柜量) as 柜量,t.dep_code from (select a.ctn_size,case a.ctn_size when ''40'' then
sum(a.ctn_number)*2 when ''45'' then
sum(a.ctn_number)*2 '
+'else
sum(a.ctn_number) end as 柜量,d.job_no,d.dep_code from b_imex_ctn a,b_oceanexport b,b_jobsheet d '
+'where a.job_no=b.job_no AND b.job_no=d.job_no '
+'group by a.ctn_size,d.job_no,d.dep_code)as t group by t.job_no,t.dep_code) as y group by y.job_no,y.dep_code)as o, '
+'(select a.job_no,a.job_type,b.sailingdt,a.sales_id,a.status,a.biz_no,a.set_flag,a.dep_code,a.cli_cname,ship_owner,load_port,dest_port from b_jobsheet a,b_oceanexport b where a.job_no=b.job_no '
+'union select a.job_no,a.job_type,b.importdt,a.sales_id,a.status,a.biz_no,a.set_flag,a.dep_code,a.cli_cname,ship_owner,delivery_port,load_port from b_jobsheet a,b_oceanimport b '
+'where a.job_no=b.job_no '
+'union select a.job_no,a.job_type,b.flight_dt,a.sales_id,a.status,a.biz_no,a.set_flag,a.dep_code,a.cli_cname,first_carrier,dept_airport,dest_airport from b_jobsheet a,b_airwayint b where a.job_no=b.job_no '
+'union select a.job_no,a.job_type,b.flight_dt,a.sales_id,a.status,a.biz_no,a.set_flag,a.dep_code,a.cli_cname,first_carrier,dept_airport,dest_airport from b_jobsheet a,b_airwaydom b where a.job_no=b.job_no) as a, '
+'(select line_cname,port_ename from c_port) as pp '
+'where a.job_no*=i.job_no and a.job_no*=o.job_no and a.dest_port*=pp.port_ename ';
if dt_CheckBox.Checked=true
then
sql:=sql+' and a.sailingdt>='''+datetostr(f_DateTimePicker.Date)+''' and a.sailingdt<='''+datetostr(t_DateTimePicker.Date)+'''';
if status_CheckBox.Checked=true
then
sql:=sql+' and a.status='+status_wwDBComboBox.Value;
if jobtype_CheckBox.Checked=true
then
sql:=sql+' and a.job_type='''+jobtype_wwDBComboBox.Value+'''';
if setflag_CheckBox.Checked=true
then
sql:=sql+' and a.set_flag='+setflag_wwDBComboBox.Value;
if dep_CheckBox.Checked=true
then
sql:=sql+' and a.dep_code='''+dep_wwDBComboBox.Value+'''';
if hangxian_CheckBox.Checked=true
then
sql:=sql+' and pp.line_cname='''+hangxian_ComboBox.Text+'''';
if shipowner_CheckBox.Checked=true
then
sql:=sql+' and a.ship_owner='''+shipowner_ComboBox.Text+'''';
if sales_CheckBox.Checked=true
then
sql:=sql+' and a.sales_id='''+salesid_wwDBLookupCombo.Text+'''';
if clicname_CheckBox.Checked=true
then
sql:=sql+' and a.cli_cname='''+clicname_wwDBComboDlg.Text+'''';
result:=sql;
end;
