unit u_rp_rp_seae; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, ComCtrls, bsSkinTabs, bsSkinCtrls, dxExEdtr, dxDBGrid, dxDBTLCl, dxGrClms, dxTL, dxDBCtrl, dxCntner, DB, ADODB, Menus, XPMenu, RxMemDS, DBTables, RxQuery, StdCtrls, Buttons, Grids, DBGrids, kbmMemTable, kbmMemBinaryStreamFormat; type Tfrm_rp_rp_seae = class(TForm) Panel1: TPanel; bsSkinTabSheet1: TbsSkinTabSheet; bsSkinTabSheet2: TbsSkinTabSheet; bsSkinTabSheet3: TbsSkinTabSheet; bsSkinPanel2: TbsSkinPanel; bsSkinButton10: TbsSkinButton; bsSkinButton9: TbsSkinButton; bsSkinButton5: TbsSkinButton; bsSkinTabSheet4: TbsSkinTabSheet; bsSkinTabSheet5: TbsSkinTabSheet; bsSkinTabSheet6: TbsSkinTabSheet; bsSkinTabSheet7: TbsSkinTabSheet; bsSkinTabSheet8: TbsSkinTabSheet; aa: TbsSkinPageControl; dxDBGrid1: TdxDBGrid; dxDBGrid1Column5: TdxDBGridMaskColumn; dxDBGrid1Column3: TdxDBGridMaskColumn; dxDBGrid1Column4: TdxDBGridMaskColumn; dxDBGrid1Column6: TdxDBGridDateColumn; dxDBGrid1Column7: TdxDBGridMaskColumn; dxDBGrid1Column8: TdxDBGridMaskColumn; dxDBGrid1Column9: TdxDBGridMaskColumn; dxDBGrid1Column10: TdxDBGridMaskColumn; dxDBGrid1Column11: TdxDBGridMaskColumn; dxDBGrid1Column21: TdxDBGridMaskColumn; dxDBGrid1Column23: TdxDBGridMaskColumn; dxDBGrid1Column24: TdxDBGridMaskColumn; dxDBGrid1Column25: TdxDBGridMaskColumn; dxDBGrid1Column26: TdxDBGridDateColumn; dxDBGrid1Column27: TdxDBGridDateColumn; dxDBGrid1Column28: TdxDBGridDateColumn; dxDBGrid1Column29: TdxDBGridMaskColumn; dxDBGrid1Column31: TdxDBGridMaskColumn; dxDBGrid1Column43: TdxDBGridMaskColumn; dxDBGrid1Column51: TdxDBGridMaskColumn; dxDBGrid1Column54: TdxDBGridMaskColumn; dxDBGrid1Column55: TdxDBGridMaskColumn; dxDBGrid1Column56: TdxDBGridColumn; dxDBGrid1Column57: TdxDBGridColumn; dxDBGrid11: TdxDBGridMaskColumn; dxDBGrid12: TdxDBGridMaskColumn; dxDBGrid13: TdxDBGridMaskColumn; dxDBGrid14: TdxDBGridMaskColumn; dxDBGrid15: TdxDBGridMaskColumn; dxDBGrid16: TdxDBGridMaskColumn; dxDBGrid17: TdxDBGridMaskColumn; dxDBGrid18: TdxDBGridMaskColumn; dxDBGrid19: TdxDBGridMaskColumn; dxDBGrid110: TdxDBGridMaskColumn; dxDBGrid1Column80: TdxDBGridMaskColumn; dxDBGrid1TEU: TdxDBGridMaskColumn; dxDBGrid1Column82: TdxDBGridMaskColumn; dxDBGrid1Column83: TdxDBGridMaskColumn; dxDBGrid1Column84: TdxDBGridDateColumn; dxDBGrid1Column88: TdxDBGridMaskColumn; dxDBGrid1Column89: TdxDBGridMaskColumn; dxDBGrid1Column90: TdxDBGridMaskColumn; dxDBGrid1Column92: TdxDBGridMaskColumn; dxDBGrid1Column93: TdxDBGridMaskColumn; dxDBGrid1Column85: TdxDBGridMaskColumn; dxDBGrid1Column87: TdxDBGridMaskColumn; dxDBGrid1Column86: TdxDBGridMaskColumn; dxDBGrid1USD: TdxDBGridColumn; dxDBGrid1RMB: TdxDBGridColumn; dxDBGrid1Column102: TdxDBGridColumn; dxDBGrid1Column105: TdxDBGridColumn; dxDBGrid1Column108: TdxDBGridColumn; dxDBGrid1Column111: TdxDBGridColumn; dxDBGrid1Column114: TdxDBGridColumn; dxDBGrid1Column115: TdxDBGridColumn; dxDBGrid1Column116: TdxDBGridColumn; dxDBGrid1Column117: TdxDBGridMaskColumn; XPMenu1: TXPMenu; p_detail: TADOStoredProc; p_detail1: TDataSource; t_sum1: TDataSource; t_sum: TRxMemoryData; dxDBGrid1Column68: TdxDBGridColumn; dxDBGrid1Column69: TdxDBGridColumn; dxDBGrid1Column70: TdxDBGridColumn; p_sum: TADOStoredProc; PopupMenu2: TPopupMenu; N10: TMenuItem; N11: TMenuItem; N14: TMenuItem; N13: TMenuItem; N12: TMenuItem; N15: TMenuItem; N16: TMenuItem; dxDBGrid2: TdxDBGrid; dxDBGrid2Column1: TdxDBGridMaskColumn; dxDBGrid2Column2: TdxDBGridMaskColumn; dxDBGrid21: TdxDBGridMaskColumn; dxDBGrid22: TdxDBGridMaskColumn; dxDBGrid23: TdxDBGridMaskColumn; dxDBGrid24: TdxDBGridMaskColumn; dxDBGrid25: TdxDBGridMaskColumn; dxDBGrid26: TdxDBGridMaskColumn; dxDBGrid27: TdxDBGridMaskColumn; dxDBGrid28: TdxDBGridMaskColumn; dxDBGrid29: TdxDBGridMaskColumn; dxDBGrid210: TdxDBGridMaskColumn; dxDBGrid2Column23: TdxDBGridMaskColumn; dxDBGrid2TEU: TdxDBGridMaskColumn; dxDBGrid2Column25: TdxDBGridMaskColumn; dxDBGrid2Column26: TdxDBGridColumn; dxDBGrid2Column27: TdxDBGridColumn; dxDBGrid2USD: TdxDBGridColumn; dxDBGrid2Column29: TdxDBGridColumn; dxDBGrid2Column30: TdxDBGridColumn; dxDBGrid2RMB: TdxDBGridColumn; dxDBGrid2Column32: TdxDBGridColumn; dxDBGrid2Column33: TdxDBGridColumn; dxDBGrid2Column34: TdxDBGridColumn; dxDBGrid2Column35: TdxDBGridColumn; dxDBGrid2Column36: TdxDBGridColumn; dxDBGrid2Column37: TdxDBGridColumn; bsSkinTabSheet9: TbsSkinTabSheet; memtblrpt_total: TkbmMemTable; kbmbnrystrmfrmt1: TkbmBinaryStreamFormat; kbmThreadDataSet1: TkbmThreadDataSet; memtblrpt_totalField: TIntegerField; memtblrpt_totalField1: TIntegerField; memtblrpt_totalField2: TIntegerField; memtblrpt_totalField3: TIntegerField; memtblrpt_totalField4: TIntegerField; memtblrpt_totalField5: TIntegerField; memtblrpt_totalField6: TIntegerField; memtblrpt_totalField7: TIntegerField; memtblrpt_totalField8: TIntegerField; memtblrpt_totalField9: TIntegerField; memtblrpt_totalField10: TIntegerField; memtblrpt_totalField11: TIntegerField; memtblrpt_totalField12: TIntegerField; memtblrpt_totalField13: TFloatField; memtblrpt_totalField14: TFloatField; memtblrpt_totalUS: TFloatField; memtblrpt_totalUSD: TFloatField; memtblrpt_totalUSD2: TFloatField; memtblrpt_totalRMB: TFloatField; memtblrpt_totalField15: TFloatField; memtblrpt_totalField16: TFloatField; memtblrpt_totalRMB2: TFloatField; memtblrpt_totalRMB3: TFloatField; memtblrpt_totalField17: TFloatField; memtblrpt_totalField18: TStringField; memtblrpt_totalbase: TStringField; memtblrpt_totalField19: TStringField; memtblrpt_totalField20: TFloatField; dxDBGrid2Column28: TdxDBGridColumn; memtblrpt_totalTEU: TIntegerField; dxDBGrid1Column62: TdxDBGridColumn; dxDBGrid2Column31: TdxDBGridColumn; memtblrpt_totalField21: TFloatField; dxDBGrid1Column63: TdxDBGridColumn; dxDBGrid1Column64: TdxDBGridColumn; dxDBGrid1Column65: TdxDBGridColumn; bsSkinTabSheet10: TbsSkinTabSheet; bsSkinTabSheet11: TbsSkinTabSheet; bsSkinTabSheet12: TbsSkinTabSheet; bsSkinTabSheet13: TbsSkinTabSheet; dxDBGrid1Column66: TdxDBGridColumn; dxDBGrid1Column67: TdxDBGridColumn; dxDBGrid1Column71: TdxDBGridColumn; dxDBGrid2Column38: TdxDBGridColumn; memtblrpt_totalField22: TStringField; bsSkinTabSheet14: TbsSkinTabSheet; bsSkinTabSheet15: TbsSkinTabSheet; bsSkinTabSheet16: TbsSkinTabSheet; dxDBGrid1Column72: TdxDBGridColumn; dxDBGrid1Column73: TdxDBGridColumn; ADOConnection1: TADOConnection; dxDBGrid2Column39: TdxDBGridColumn; dxDBGrid1Column74: TdxDBGridColumn; memtblrpt_totalField23: TStringField; dxDBGrid1Column75: TdxDBGridColumn; dxDBGrid2Column40: TdxDBGridColumn; memtblrpt_totalField24: TFloatField; dxDBGrid1Column76: TdxDBGridColumn; dxDBGrid1Column77: TdxDBGridColumn; dxDBGrid1Column78: TdxDBGridColumn; procedure bsSkinButton5Click(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure Panel1Resize(Sender: TObject); procedure bsSkinButton10Click(Sender: TObject); procedure FormShow(Sender: TObject); procedure aaChange(Sender: TObject); procedure bsSkinButton9Click(Sender: TObject); procedure N10Click(Sender: TObject); procedure N14Click(Sender: TObject); procedure N12Click(Sender: TObject); procedure N16Click(Sender: TObject); procedure dxDBGrid1ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); procedure dxDBGrid2ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); procedure dxDBGrid1DblClick(Sender: TObject); procedure dxDBGrid1Column5CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); private { Private declarations } public { Public declarations } end; var frm_rp_rp_seae: Tfrm_rp_rp_seae; rp_rp_seae_open:boolean; rp_rp_seae_str:widestring; implementation uses u_main, my_sys_function, u_data_share; {$R *.dfm} procedure Tfrm_rp_rp_seae.bsSkinButton5Click(Sender: TObject); begin close; end; procedure Tfrm_rp_rp_seae.FormClose(Sender: TObject; var Action: TCloseAction); begin savereggrid(dxdbgrid1,caption+'11'); savereggrid(dxdbgrid2,caption+'12'); rp_rp_seae_open:=false; frm_rp_rp_seae.Hide; frm_rp_rp_seae.ManualFloat(frm_rp_rp_seae.BoundsRect ); frm_main.freeTabs('frm_rp_rp_seae'); action:=cafree; frm_rp_rp_seae:=nil; end; procedure Tfrm_rp_rp_seae.Panel1Resize(Sender: TObject); begin bsSkinPanel2.Left:=Panel1.Width-bsSkinPanel2.Width; end; procedure Tfrm_rp_rp_seae.bsSkinButton10Click(Sender: TObject); begin bsSkinTabSheet1.show; query_seae(6,'0037'); end; procedure Tfrm_rp_rp_seae.FormShow(Sender: TObject); begin loadreggrid(dxdbgrid1,true,caption+'11'); loadreggrid(dxdbgrid2,true,caption+'12'); rp_rp_seae_open:=true; if frm_data_share.t_load_employee.FieldByName('应收').asboolean then begin dxDBGrid1.Bands[2].Visible:=true; dxDBGrid2.Bands[2].Visible:=true; end else begin dxDBGrid1.Bands[2].Visible:=false; dxDBGrid2.Bands[2].Visible:=false; end; if frm_data_share.t_load_employee.FieldByName('应付').asboolean then begin dxDBGrid1.Bands[3].Visible:=true; dxDBGrid2.Bands[3].Visible:=true; end else begin dxDBGrid1.Bands[3].Visible:=false; dxDBGrid2.Bands[3].Visible:=false; end; if frm_data_share.t_load_employee.FieldByName('利润').asboolean then begin dxDBGrid1.Bands[4].Visible:=true; dxDBGrid2.Bands[4].Visible:=true; end else begin dxDBGrid1.Bands[4].Visible:=false; dxDBGrid2.Bands[4].Visible:=false; end; end; procedure Tfrm_rp_rp_seae.aaChange(Sender: TObject); var str,sumfield:widestring; aQuery:TADOQuery; begin if not rp_rp_seae_open then exit; IF bsSkinTabSheet1.Showing then exit; dxDBGrid2.Parent:=aa.ActivePage; dxDBGrid2Column1.Caption:=aa.ActivePage.Caption; // try if not p_detail.active then exit; if p_detail.IsEmpty then exit; str:=rp_rp_seae_str; if str<>'no'then begin if aa.ActivePage.Caption='委托单位' then begin if (Pos('会计月份',str)>0 ) then begin p_sum.close; p_sum.Parameters.ParamByName('SQLStr').Value:= 'SELECT COUNT(t_op_seae.编号) as 票数, sum(case t_op_seae.装运方式 when ''拼箱单票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE ' +' case t_op_seae.装运方式 when ''拼箱分票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE 0 END END) ' +'AS 计费吨,sum(t_op_seae.箱型1) as 箱型1, sum(t_op_seae.箱型2) as 箱型2' +', sum(t_op_seae.箱型3) as 箱型3, sum(t_op_seae.箱型4) as 箱型4, sum(t_op_seae.箱型5) as 箱型5' +', sum(t_op_seae.箱型6) as 箱型6, sum( t_op_seae.箱型7) as 箱型7, sum(t_op_seae.箱型8) as 箱型8' +', sum(t_op_seae.箱型9) as 箱型9, sum(t_op_seae.箱型10) as 箱型10' +', sum(t_op_seae.其他箱型) as 其他箱型, sum( t_op_seae.箱TEU) as 箱TEU, sum(t_op_seae.件数) as 件数' +', sum(t_op_seae.重量) as 重量, sum( t_op_seae.尺码) as 尺码, sum(v_op_gain.利润USD) as 利润USD' +', sum(v_op_gain.应收USD) as 应收USD, sum( v_op_gain.应付USD) as 应付USD, sum(v_op_gain.利润RMB) as 利润RMB' +', sum(v_op_gain.应收RMB) as 应收RMB, sum( v_op_gain.应付RMB) as 应付RMB, sum(v_op_gain.毛利润) as 毛利润' +', sum(v_op_gain.合计应收) as 合计应收, sum( v_op_gain.合计应付) as 合计应付' +',(case when isnull(sum(v_op_gain.合计应付),0)=0 then '+''''+'无法计算'+''''+' else ' +'ltrim(str(sum(v_op_gain.毛利润)/sum(v_op_gain.合计应付)*100,10,2))+'+''''+'%'+''''+' end) as 利润率' +' ,CAST(CASE WHEN sum( t_op_seae.箱TEU)=0 THEN 0 ELSE sum(v_op_gain.毛利润)/sum( t_op_seae.箱TEU) END AS decimal(10,2)) 标箱利润' +',t_op_seae.'+aa.ActivePage.Caption+' as base,t_crm_client_class.客户等级,(select top 1 客户全称 from t_crm_client where 客户简称=t_op_seae.委托单位) 委托单位全称 ' +','+''''+aa.ActivePage.Caption+''''+' as 统计标准' +' FROM t_op_seae LEFT JOIN' +' v_op_gain ON t_op_seae.编号 = v_op_gain.编号 ' +' left join t_crm_client_class on t_op_seae.委托单位=t_crm_client_class.客户简称 and t_crm_client_class.开始日期<=t_op_seae.预抵日期 and convert(varchar(100),t_op_seae.预抵日期,23)<=t_crm_client_class.结束日期 and t_crm_client_class.状态=''审核'' ' +str+' group by '+'t_op_seae.'+aa.ActivePage.Caption+',t_crm_client_class.客户等级' +' order by '+'t_op_seae.'+aa.ActivePage.Caption; p_sum.open; end else begin p_sum.close; p_sum.Parameters.ParamByName('SQLStr').Value:= 'SELECT COUNT(t_op_seae.编号) as 票数, sum(case t_op_seae.装运方式 when ''拼箱单票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE ' +' case t_op_seae.装运方式 when ''拼箱分票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE 0 END END) ' +'AS 计费吨,sum(t_op_seae.箱型1) as 箱型1, sum(t_op_seae.箱型2) as 箱型2' +', sum(t_op_seae.箱型3) as 箱型3, sum(t_op_seae.箱型4) as 箱型4, sum(t_op_seae.箱型5) as 箱型5' +', sum(t_op_seae.箱型6) as 箱型6, sum( t_op_seae.箱型7) as 箱型7, sum(t_op_seae.箱型8) as 箱型8' +', sum(t_op_seae.箱型9) as 箱型9, sum(t_op_seae.箱型10) as 箱型10' +', sum(t_op_seae.其他箱型) as 其他箱型, sum( t_op_seae.箱TEU) as 箱TEU, sum(t_op_seae.件数) as 件数' +', sum(t_op_seae.重量) as 重量, sum( t_op_seae.尺码) as 尺码, sum(t_op_gain.利润USD) as 利润USD' +', sum(t_op_gain.应收USD) as 应收USD, sum( t_op_gain.应付USD) as 应付USD, sum(t_op_gain.利润RMB) as 利润RMB' +', sum(t_op_gain.应收RMB) as 应收RMB, sum( t_op_gain.应付RMB) as 应付RMB, sum(t_op_gain.毛利润) as 毛利润' +', sum(t_op_gain.合计应收) as 合计应收, sum( t_op_gain.合计应付) as 合计应付' +',(case when isnull(sum(t_op_gain.合计应付),0)=0 then '+''''+'无法计算'+''''+' else ' +'ltrim(str(sum(t_op_gain.毛利润)/sum(t_op_gain.合计应付)*100,10,2))+'+''''+'%'+''''+' end) as 利润率' +' ,CAST(CASE WHEN sum( t_op_seae.箱TEU)=0 THEN 0 ELSE sum(t_op_gain.毛利润)/sum( t_op_seae.箱TEU) END AS decimal(10,2)) 标箱利润' +',t_op_seae.'+aa.ActivePage.Caption+' as base,t_crm_client_class.客户等级,(select top 1 客户全称 from t_crm_client where 客户简称=t_op_seae.委托单位) 委托单位全称 ' +','+''''+aa.ActivePage.Caption+''''+' as 统计标准' +' FROM t_op_seae LEFT JOIN' +' t_op_gain ON t_op_seae.编号 = t_op_gain.编号 ' +' left join t_crm_client_class on t_op_seae.委托单位=t_crm_client_class.客户简称 and t_crm_client_class.开始日期<=t_op_seae.预抵日期 and convert(varchar(100),t_op_seae.预抵日期,23)<=t_crm_client_class.结束日期 and t_crm_client_class.状态=''审核'' ' +str+' group by '+'t_op_seae.'+aa.ActivePage.Caption+',t_crm_client_class.客户等级' +' order by '+'t_op_seae.'+aa.ActivePage.Caption; p_sum.open; end; dxDBGrid2Column39.Visible:=True; end else begin sumfield:=aa.ActivePage.Caption; if (sumfield='订舱代理') then sumfield:='货代公司'; p_sum.close; if (Pos('会计月份',str)>0 ) then begin p_sum.Parameters.ParamByName('SQLStr').Value:= 'SELECT COUNT(t_op_seae.编号) as 票数, sum(case t_op_seae.装运方式 when ''拼箱单票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE ' +' case t_op_seae.装运方式 when ''拼箱分票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE 0 END END) ' +'AS 计费吨,sum(t_op_seae.箱型1) as 箱型1, sum(t_op_seae.箱型2) as 箱型2' +', sum(t_op_seae.箱型3) as 箱型3, sum(t_op_seae.箱型4) as 箱型4, sum(t_op_seae.箱型5) as 箱型5' +', sum(t_op_seae.箱型6) as 箱型6, sum( t_op_seae.箱型7) as 箱型7, sum(t_op_seae.箱型8) as 箱型8' +', sum(t_op_seae.箱型9) as 箱型9, sum(t_op_seae.箱型10) as 箱型10' +', sum(t_op_seae.其他箱型) as 其他箱型, sum( t_op_seae.箱TEU) as 箱TEU, sum(t_op_seae.件数) as 件数' +', sum(t_op_seae.重量) as 重量, sum( t_op_seae.尺码) as 尺码, sum(v_op_gain.利润USD) as 利润USD' +', sum(v_op_gain.应收USD) as 应收USD, sum( v_op_gain.应付USD) as 应付USD, sum(v_op_gain.利润RMB) as 利润RMB' +', sum(v_op_gain.应收RMB) as 应收RMB, sum( v_op_gain.应付RMB) as 应付RMB, sum(v_op_gain.毛利润) as 毛利润' +', sum(v_op_gain.合计应收) as 合计应收, sum( v_op_gain.合计应付) as 合计应付' +',(case when isnull(sum(v_op_gain.合计应付),0)=0 then '+''''+'无法计算'+''''+' else ' +'ltrim(str(sum(v_op_gain.毛利润)/sum(v_op_gain.合计应付)*100,10,2))+'+''''+'%'+''''+' end) as 利润率' +' ,CAST(CASE WHEN sum( t_op_seae.箱TEU)=0 THEN 0 ELSE sum(v_op_gain.毛利润)/sum( t_op_seae.箱TEU) END AS decimal(10,2)) 标箱利润' +',t_op_seae.'+sumfield+' as base' +','+''''+sumfield+''''+' as 统计标准' +' FROM t_op_seae LEFT JOIN' +' v_op_gain ON t_op_seae.编号 = v_op_gain.编号 ' +str+' group by '+'t_op_seae.'+sumfield +' order by '+'t_op_seae.'+sumfield; p_sum.open; end else begin p_sum.Parameters.ParamByName('SQLStr').Value:= 'SELECT COUNT(t_op_seae.编号) as 票数, sum(case t_op_seae.装运方式 when ''拼箱单票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE ' +' case t_op_seae.装运方式 when ''拼箱分票'' THEN (case when isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0)>1 then ' +' isnull((CASE WHEN t_op_seae.重量 / 1000 > t_op_seae.尺码 THEN t_op_seae.重量 / 1000 ELSE t_op_seae.尺码 END), 0) else 1 end) ELSE 0 END END) ' +'AS 计费吨,sum(t_op_seae.箱型1) as 箱型1, sum(t_op_seae.箱型2) as 箱型2' +', sum(t_op_seae.箱型3) as 箱型3, sum(t_op_seae.箱型4) as 箱型4, sum(t_op_seae.箱型5) as 箱型5' +', sum(t_op_seae.箱型6) as 箱型6, sum( t_op_seae.箱型7) as 箱型7, sum(t_op_seae.箱型8) as 箱型8' +', sum(t_op_seae.箱型9) as 箱型9, sum(t_op_seae.箱型10) as 箱型10' +', sum(t_op_seae.其他箱型) as 其他箱型, sum( t_op_seae.箱TEU) as 箱TEU, sum(t_op_seae.件数) as 件数' +', sum(t_op_seae.重量) as 重量, sum( t_op_seae.尺码) as 尺码, sum(t_op_gain.利润USD) as 利润USD' +', sum(t_op_gain.应收USD) as 应收USD, sum( t_op_gain.应付USD) as 应付USD, sum(t_op_gain.利润RMB) as 利润RMB' +', sum(t_op_gain.应收RMB) as 应收RMB, sum( t_op_gain.应付RMB) as 应付RMB, sum(t_op_gain.毛利润) as 毛利润' +', sum(t_op_gain.合计应收) as 合计应收, sum( t_op_gain.合计应付) as 合计应付' +',(case when isnull(sum(t_op_gain.合计应付),0)=0 then '+''''+'无法计算'+''''+' else ' +'ltrim(str(sum(t_op_gain.毛利润)/sum(t_op_gain.合计应付)*100,10,2))+'+''''+'%'+''''+' end) as 利润率' +' ,CAST(CASE WHEN sum( t_op_seae.箱TEU)=0 THEN 0 ELSE sum(t_op_gain.毛利润)/sum( t_op_seae.箱TEU) END AS decimal(10,2)) 标箱利润' +',t_op_seae.'+sumfield+' as base' +','+''''+sumfield+''''+' as 统计标准' +' FROM t_op_seae LEFT JOIN' +' t_op_gain ON t_op_seae.编号 = t_op_gain.编号 ' +str+' group by '+'t_op_seae.'+sumfield +' order by '+'t_op_seae.'+sumfield; p_sum.open; end; dxDBGrid2Column39.Visible:=false; end; memtblrpt_total.DisableControls; memtblrpt_total.LoadFromDataSet(p_sum,[]); { if aa.ActivePage.Caption='销售部门' then begin aQuery:=CreateAdoQuery; try memtblrpt_total.First; while not memtblrpt_total.Eof do begin if memtblrpt_total.FieldByName('base').AsString='海外部' then begin with aQuery do begin Close;SQL.Clear; SQL.Add('select sum(金额*汇率) as 内部应收 from t_ch_fee where 类型=''付'' AND 客户名称='''+memtblrpt_total.FieldByName('base').AsString+''' and 编号 in (select 编号 from t_op_seae '+Str+')'); Open; if FieldByName('内部应收').AsFloat<>0 then begin memtblrpt_total.Edit; memtblrpt_total.FieldByName('内部应收').AsFloat:=FieldByName('内部应收').AsFloat; memtblrpt_total.FieldByName('合计应收').AsFloat:=memtblrpt_total.FieldByName('合计应收').AsFloat+FieldByName('内部应收').AsFloat; memtblrpt_total.FieldByName('毛利润').AsFloat:=memtblrpt_total.FieldByName('毛利润').AsFloat+FieldByName('内部应收').AsFloat; memtblrpt_total.Post; end; end; end; memtblrpt_total.Next; end; finally FreeAndNil(aQuery); end; end; } memtblrpt_total.EnableControls; end; { except ; end; } end; procedure Tfrm_rp_rp_seae.bsSkinButton9Click(Sender: TObject); begin sys_print('海运出口业务统计',2,nil,nil,nil,nil,p_detail1,t_sum1,nil,nil,nil,nil,nil); end; procedure Tfrm_rp_rp_seae.N10Click(Sender: TObject); begin if bsSkinTabSheet1.showing then begin update_grid_view_no(dxdbgrid1); end else begin update_grid_view_no(dxdbgrid2); end; end; procedure Tfrm_rp_rp_seae.N14Click(Sender: TObject); begin if bsSkinTabSheet1.showing then begin update_grid_view_all(dxdbgrid1); end else begin update_grid_view_all(dxdbgrid2); end; end; procedure Tfrm_rp_rp_seae.N12Click(Sender: TObject); begin if bsSkinTabSheet1.showing then begin update_grid(dxdbgrid1,22); end else begin update_grid(dxdbgrid2,23); end; end; procedure Tfrm_rp_rp_seae.N16Click(Sender: TObject); begin if bsSkinTabSheet1.showing then begin grid_save_xls(dxdbgrid1); end else begin grid_save_xls(dxdbgrid2); end; end; procedure Tfrm_rp_rp_seae.dxDBGrid1ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); begin Column_sort_pro(Column,p_detail,'业务编号'); end; procedure Tfrm_rp_rp_seae.dxDBGrid2ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); begin Column_sort_pro(Column,P_sum,'base'); end; procedure Tfrm_rp_rp_seae.dxDBGrid1DblClick(Sender: TObject); begin fee_view(p_detail.fieldbyname('编号').asstring); end; procedure Tfrm_rp_rp_seae.dxDBGrid1Column5CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); begin if (ANode.Strings[dxDBGrid1Column78.Index]='0') then begin AColor:=clLtGray; end; if (ANode.Strings[dxDBGrid1Column77.Index]='0') then begin AFont.Color:=clBlue; end; end; end.