unit u_rp_no_seae_query; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, BusinessSkinForm, RxLookup, wwdbdatetimepicker, StdCtrls, Mask, wwdbedit, Wwdotdot, Wwdbcomb, bsSkinCtrls, ComCtrls; type Tfrm_rp_no_seae_query = class(TForm) Label10: TLabel; Label5: TLabel; Label8: TLabel; Label11: TLabel; Label9: TLabel; Label22: TLabel; Label23: TLabel; Label14: TLabel; Label16: TLabel; Label15: TLabel; bsSkinButton1: TbsSkinButton; bsSkinButton10: TbsSkinButton; bsSkinButton5: TbsSkinButton; wwDBComboBox18: TwwDBComboBox; wwDBDateTimePicker1: TwwDBDateTimePicker; wwDBComboBox6: TwwDBComboBox; wwDBDateTimePicker2: TwwDBDateTimePicker; wwDBComboBox19: TwwDBComboBox; wwDBDateTimePicker3: TwwDBDateTimePicker; wwDBComboBox11: TwwDBComboBox; wwDBDateTimePicker4: TwwDBDateTimePicker; wwDBComboBox21: TwwDBComboBox; RxDBLookupCombo2: TRxDBLookupCombo; wwDBComboBox28: TwwDBComboBox; RxDBLookupCombo10: TRxDBLookupCombo; RxDBLookupCombo12: TRxDBLookupCombo; wwDBComboBox29: TwwDBComboBox; wwDBComboBox14: TwwDBComboBox; RxDBLookupCombo3: TRxDBLookupCombo; wwDBComboBox25: TwwDBComboBox; RxDBLookupCombo6: TRxDBLookupCombo; wwDBComboBox24: TwwDBComboBox; RxDBLookupCombo4: TRxDBLookupCombo; bsBusinessSkinForm1: TbsBusinessSkinForm; Label1: TLabel; wwDBComboBox3: TwwDBComboBox; Edit1: TEdit; Label32: TLabel; wwDBComboBox38: TwwDBComboBox; Edit9: TEdit; Label75: TLabel; RxDBLookupCombo19: TRxDBLookupCombo; procedure bsSkinButton10Click(Sender: TObject); procedure bsSkinButton5Click(Sender: TObject); procedure FormShow(Sender: TObject); procedure bsSkinButton1Click(Sender: TObject); function get_no_str(table_str:string;date_query:string):widestring; procedure do_10(str_bill:widestring); procedure wwDBComboBox18KeyPress(Sender: TObject; var Key: Char); private { Private declarations } public { Public declarations } end; var frm_rp_no_seae_query: Tfrm_rp_no_seae_query; no_seae_query_bs:integer; no_seae_query_limited:string; no_seae_query_fee:integer; no_seae_query_table:string; no_seae_query_date:string; no_seae_query_no:integer; implementation uses my_sys_function, u_rp_no_seae_accept, u_rp_no_total, u_rp_in_seae, u_rp_no_seai, u_rp_in_seai, u_rp_no_aire, u_rp_no_airi, u_rp_in_aire, u_rp_in_airi, u_rp_in_total, u_data_share, u_rp_zj_hs; {$R *.dfm} procedure Tfrm_rp_no_seae_query.do_10(str_bill:widestring); begin frm_rp_in_total.p_total.close; frm_rp_in_total.p_total.Parameters.ParamByName('SQLStr').value:= 'SELECT SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额 else 0 end) as 应开USD,' +'SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then t_ch_fee.开票金额' +' else 0 end) as 已开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额-t_ch_fee.开票金额' +' else 0 end) as 未开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.金额*t_ch_fee.汇率' +' end) as 应开RMB,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.开票金额*t_ch_fee.汇率' +' end) as 已开RMB,SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then 0' +' else (t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率' +' end) as 未开RMB,sum(t_ch_fee.金额*t_ch_fee.汇率) as 应开合计,' +' sum(t_ch_fee.开票金额*t_ch_fee.汇率) as 已开合计,' +' sum((t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率) as 未开合计,' +'v_op_no.业务种类' +' FROM t_ch_fee inner join v_op_no on v_op_no.编号=t_ch_fee.编号 ' +str_bill+' and t_ch_fee.类型='+''''+'收'+'''' +' GROUP BY v_op_no.业务种类' +' order by v_op_no.业务种类'; frm_rp_in_total.p_total.open; frm_rp_in_total.p_client.close; frm_rp_in_total.p_client.Parameters.ParamByName('SQLStr').value:= 'SELECT t_ch_fee.客户名称,v_op_no.业务种类,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额 else 0 end) as 应开USD,' +'SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then t_ch_fee.开票金额' +' else 0 end) as 已开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额-t_ch_fee.开票金额' +' else 0 end) as 未开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.金额*t_ch_fee.汇率' +' end) as 应开RMB,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.开票金额*t_ch_fee.汇率' +' end) as 已开RMB,SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then 0' +' else (t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率' +' end) as 未开RMB,sum(t_ch_fee.金额*t_ch_fee.汇率) as 应开合计,' +' sum(t_ch_fee.开票金额*t_ch_fee.汇率) as 已开合计,' +' sum((t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率) as 未开合计' +' FROM t_ch_fee inner join v_op_no on v_op_no.编号=t_ch_fee.编号 ' +str_bill+' and t_ch_fee.类型='+''''+'收'+'''' +' GROUP BY t_ch_fee.客户名称,v_op_no.业务种类' +' order by t_ch_fee.客户名称,v_op_no.业务种类'; frm_rp_in_total.p_client.open; frm_rp_in_total.p_sales.close; frm_rp_in_total.p_sales.Parameters.ParamByName('SQLStr').value:= 'SELECT v_op_no.揽货人,v_op_no.业务种类,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额 else 0 end) as 应开USD,' +'SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then t_ch_fee.开票金额' +' else 0 end) as 已开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then t_ch_fee.金额-t_ch_fee.开票金额' +' else 0 end) as 未开USD,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.金额*t_ch_fee.汇率' +' end) as 应开RMB,SUM(case t_ch_fee.币别' +' when '+''''+'USD'+''''+' then 0 else t_ch_fee.开票金额*t_ch_fee.汇率' +' end) as 已开RMB,SUM(case t_ch_fee.币别 when '+''''+'USD'+''''+' then 0' +' else (t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率' +' end) as 未开RMB,sum(t_ch_fee.金额*t_ch_fee.汇率) as 应开合计,' +' sum(t_ch_fee.开票金额*t_ch_fee.汇率) as 已开合计,' +' sum((t_ch_fee.金额-t_ch_fee.开票金额)*t_ch_fee.汇率) as 未开合计' +' FROM t_ch_fee inner join v_op_no on v_op_no.编号=t_ch_fee.编号 ' +str_bill+' and t_ch_fee.类型='+''''+'收'+'''' +' GROUP BY v_op_no.揽货人,v_op_no.业务种类' +' order by v_op_no.揽货人,v_op_no.业务种类'; frm_rp_in_total.p_sales.open; end; function Tfrm_rp_no_seae_query.get_no_str(table_str:string;date_query:string):widestring; var str_bill:widestring; begin str_bill:=query_tiaojian(1,str_bill,table_str+'会计期间',wwDBComboBox19.text,wwDBDateTimePicker3.text); str_bill:=query_tiaojian(1,str_bill,table_str+'会计期间',wwDBComboBox11.text,wwDBDateTimePicker4.text); str_bill:=query_tiaojian(1,str_bill,table_str+'客服员',wwDBComboBox24.text,RxDBLookupCombo4.DisplayValue); str_bill:=query_tiaojian(1,str_bill,table_str+'揽货人',wwDBComboBox14.text,RxDBLookupCombo3.DisplayValue); str_bill:=query_tiaojian(1,str_bill,table_str+'操作员',wwDBComboBox25.text,RxDBLookupCombo6.DisplayValue); str_bill:=query_tiaojian(1,str_bill,table_str+'销售部门',wwDBComboBox28.text,RxDBLookupCombo10.DisplayValue); str_bill:=query_tiaojian(1,str_bill,table_str+'操作部门',wwDBComboBox29.text,RxDBLookupCombo12.DisplayValue); str_bill:=query_tiaojian(2,str_bill,table_str+'周次',wwDBComboBox3.text,Edit1.text); str_bill:=query_tiaojian(2,str_bill,table_str+'周次',wwDBComboBox38.text,Edit9.text); /////////////////////////? if no_seae_query_limited<>'no'then begin if trim(str_bill)='' then begin str_bill:='where '+open_data(no_seae_query_limited,table_str+'揽货人',table_str+'操作员',table_str+'客服员','no','no'); end else begin str_bill:=str_bill+' and '+open_data(no_seae_query_limited,table_str+'揽货人',table_str+'操作员',table_str+'客服员','no','no'); end; end; str_bill:=query_tiaojian(1,str_bill,table_str+date_query,wwDBComboBox18.text,wwDBDateTimePicker1.text); str_bill:=query_tiaojian(1,str_bill,table_str+date_query,wwDBComboBox6.text,wwDBDateTimePicker2.text); str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.客户名称',wwDBComboBox21.text,RxDBLookupCombo2.DisplayValues[1]); case no_seae_query_fee of 1: begin if str_bill='' then str_bill:='where t_ch_fee.金额<>t_ch_fee.结算金额' else str_bill:=str_bill+' and t_ch_fee.金额<>t_ch_fee.结算金额'; end; 2: begin if str_bill='' then str_bill:='where t_ch_fee.结算金额<>0' else str_bill:=str_bill+' and t_ch_fee.结算金额<>0'; end; 3: begin if no_seae_query_no=1 then begin if str_bill='' then str_bill:='where t_ch_fee.应结日期<='+''''+no_seae_query_date+'''' else str_bill:=str_bill+' and t_ch_fee.应结日期<='+''''+no_seae_query_date+''''; end; end; end; result:=str_bill; end; procedure Tfrm_rp_no_seae_query.bsSkinButton10Click(Sender: TObject); begin wwDBDateTimePicker1.Clear; wwDBDateTimePicker2.Clear; wwDBDateTimePicker3.Clear; wwDBDateTimePicker4.Clear; RxDBLookupCombo4.ClearValue; RxDBLookupCombo3.ClearValue; RxDBLookupCombo6.ClearValue; RxDBLookupCombo2.ClearValue; RxDBLookupCombo10.ClearValue; RxDBLookupCombo12.ClearValue; end; procedure Tfrm_rp_no_seae_query.bsSkinButton5Click(Sender: TObject); begin close; end; procedure Tfrm_rp_no_seae_query.FormShow(Sender: TObject); begin wwDBComboBox18.text:='大于'; wwDBComboBox6.text:='小于'; wwDBComboBox11.text:='小于'; wwDBComboBox19.text:='大于'; wwDBComboBox21.text:='等于'; wwDBComboBox24.text:='等于'; wwDBComboBox25.text:='等于'; wwDBComboBox28.text:='等于'; wwDBComboBox29.text:='等于'; wwDBComboBox14.text:='等于'; wwDBComboBox3.text:='等于'; wwDBComboBox38.text:='等于'; frm_data_share.t_crm_client_all.requery; frm_data_share.t_sys_employee.requery; frm_data_share.t_sys_department.requery; end; procedure Tfrm_rp_no_seae_query.bsSkinButton1Click(Sender: TObject); var str_bill,str_seae,str_seai,str_aire,str_airi:widestring; sl:tstrings; begin if no_seae_query_bs=5 then begin str_seae:=frm_rp_no_seae_query.get_no_str('t_op_seae.','开船日期'); str_seai:=frm_rp_no_seae_query.get_no_str('t_op_seai.','进口日期'); str_aire:=frm_rp_no_seae_query.get_no_str('t_op_aire.','一程开航日期'); str_airi:=frm_rp_no_seae_query.get_no_str('t_op_airi.','进口日期'); if RxDBLookupCombo19.DisplayValues[1]<>'' then begin if str_seae='' then str_seae:='where t_ch_fee.客户名称 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo19.DisplayValues[1]+''') ' else str_seae:=str_seae+' and t_ch_fee.客户名称 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo19.DisplayValues[1]+''') ' end; frm_rp_no_total.p_total_client.close; frm_rp_no_total.p_total_client.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' GROUP BY t_ch_fee.客户名称 '; frm_rp_no_total.p_total_client.Open; frm_rp_no_total.p_total_sales.close; frm_rp_no_total.p_total_sales.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_op_seae.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' GROUP BY t_op_seae.揽货人 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_op_seai.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' GROUP BY t_op_seai.揽货人 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_op_aire.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' GROUP BY t_op_aire.揽货人 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_op_airi.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' GROUP BY t_op_airi.揽货人 '; frm_rp_no_total.p_total_sales.Open; frm_rp_no_total.p_total.close; frm_rp_no_total.p_total.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未收USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未收RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额 else 0 end) ELSE 0 END) as 应付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 已付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 未付USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.金额*t_ch_fee.汇率 end) ELSE 0 END) as 应付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else t_ch_fee.结算金额*t_ch_fee.汇率 end) ELSE 0 END) as 已付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 未付RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未付合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as 差额USD,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (case t_ch_fee.币别 when '+''''+'USD'+'''' +' then 0 else (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 end) ELSE 0 END) as 差额RMB,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END' +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*汇率 ELSE 0 END) as 差额合计' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi; frm_rp_no_total.p_total.Open; frm_rp_no_total.p_total_client.Sort:='客户名称,业务种类'; frm_rp_no_total.p_total_sales.Sort:='揽货人,业务种类'; close; exit; end; if no_seae_query_bs=13 then begin str_seae:=frm_rp_no_seae_query.get_no_str('t_op_seae.','开船日期'); str_seai:=frm_rp_no_seae_query.get_no_str('t_op_seai.','进口日期'); str_aire:=frm_rp_no_seae_query.get_no_str('t_op_aire.','一程开航日期'); str_airi:=frm_rp_no_seae_query.get_no_str('t_op_airi.','进口日期'); frm_rp_zj_hs.p_total_client.close; frm_rp_zj_hs.p_total_client.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0 and t_ch_fee.应结日期<=GETDATE()) GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_ch_fee.客户名称 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_ch_fee.客户名称,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_ch_fee.客户名称 '; frm_rp_zj_hs.p_total_client.Open; frm_rp_zj_hs.p_total_sales.close; frm_rp_zj_hs.p_total_sales.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_op_seae.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率 ' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0 and t_ch_fee.应结日期<=GETDATE()) GROUP BY t_op_seae.揽货人 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_op_seai.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_seai.揽货人 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_op_aire.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_aire.揽货人 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_op_airi.揽货人,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_airi.揽货人 '; frm_rp_zj_hs.p_total_sales.Open; frm_rp_zj_hs.p_total_saleDept.close; frm_rp_zj_hs.p_total_saleDept.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_op_seae.销售部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0 and t_ch_fee.应结日期<=GETDATE()) GROUP BY t_op_seae.销售部门 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_op_seai.销售部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_seai.销售部门 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_op_aire.销售部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_aire.销售部门 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_op_airi.销售部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_airi.销售部门 '; frm_rp_zj_hs.p_total_saleDept.Open; frm_rp_zj_hs.p_total_opdept.close; frm_rp_zj_hs.p_total_opdept.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,t_op_seae.操作部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0 and t_ch_fee.应结日期<=GETDATE()) GROUP BY t_op_seae.操作部门 ' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,t_op_seai.操作部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_seai.操作部门 ' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,t_op_aire.操作部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_aire.操作部门 ' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,t_op_airi.操作部门,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0) GROUP BY t_op_airi.操作部门 '; frm_rp_zj_hs.p_total_opdept.Open; frm_rp_zj_hs.p_total.close; frm_rp_zj_hs.p_total.Parameters.ParamByName('SQLStr').value:= 'SELECT '+''''+'海运出口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +str_seae+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0 and t_ch_fee.应结日期<=GETDATE())' +' union SELECT '+''''+'海运进口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_seai on t_op_seai.编号=t_ch_fee.编号 ' +str_seai+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0)' +' union SELECT '+''''+'空运出口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_aire on t_op_aire.编号=t_ch_fee.编号 ' +str_aire+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0)' +' union SELECT '+''''+'空运进口'+''''+' as 业务种类 ,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END) as 应收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END) as 已收合计,' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,' +'(SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.结算金额*t_ch_fee.汇率 ELSE 0 END)/' +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+'''' +' THEN t_ch_fee.金额*t_ch_fee.汇率 ELSE 0 END))*100 as 回款率' +' FROM t_ch_fee inner join t_op_airi on t_op_airi.编号=t_ch_fee.编号 ' +str_airi+' and (t_ch_fee.类型='+''''+'收'+''''+' and t_ch_fee.金额<>0)'; frm_rp_zj_hs.p_total.Open; frm_rp_zj_hs.p_total_client.Sort:='客户名称,业务种类'; frm_rp_zj_hs.p_total_sales.Sort:='揽货人,业务种类'; close; exit; end; case no_seae_query_bs of 10: begin str_bill:=frm_rp_no_seae_query.get_no_str(no_seae_query_table,'业务日期'); end; end; case no_seae_query_bs of 10: begin frm_rp_no_seae_query.do_10(str_bill); end; end; close; end; procedure Tfrm_rp_no_seae_query.wwDBComboBox18KeyPress(Sender: TObject; var Key: Char); begin if key=#13 then begin key:=#0; frm_rp_no_seae_query.bsSkinButton1Click(Sender); end; end; end.