unit u_rp_total_query;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dxExEdtr, DB, ADODB, BusinessSkinForm, wwdblook, dxDBCtrl,
  dxDBGrid, dxTL, dxDBTLCl, dxGrClms, dxCntner, RxLookup,
  wwdbdatetimepicker, StdCtrls, Mask, wwdbedit, Wwdotdot, Wwdbcomb,
  bsSkinCtrls, ComCtrls, bsSkinTabs;

type
  Tfrm_rp_total_query = class(TForm)
    bsSkinPageControl1: TbsSkinPageControl;
    bsSkinTabSheet1: TbsSkinTabSheet;
    bsSkinGroupBox1: TbsSkinGroupBox;
    bsSkinPanel1: TbsSkinPanel;
    bsSkinButton2: TbsSkinButton;
    bsSkinButton4: TbsSkinButton;
    bsSkinPanel2: TbsSkinPanel;
    Label33: TLabel;
    Label34: TLabel;
    Label35: TLabel;
    Label36: TLabel;
    RxDBLookupCombo14: TRxDBLookupCombo;
    wwDBComboBox39: TwwDBComboBox;
    wwDBComboBox40: TwwDBComboBox;
    query_bit: TwwDBComboBox;
    query_str_int: TEdit;
    query_date: TwwDBDateTimePicker;
    RxDBLookupList1: TRxDBLookupList;
    bsSkinGroupBox2: TbsSkinGroupBox;
    bsSkinPanel3: TbsSkinPanel;
    bsSkinButton3: TbsSkinButton;
    bsSkinButton7: TbsSkinButton;
    bsSkinButton6: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridPickColumn;
    dxDBGrid1Column2: TdxDBGridMaskColumn;
    dxDBGrid1Column3: TdxDBGridPickColumn;
    dxDBGrid1Column4: TdxDBGridMaskColumn;
    dxDBGrid2: TdxDBGrid;
    dxDBGrid2SQ_ID: TdxDBGridMaskColumn;
    bsSkinTabSheet2: TbsSkinTabSheet;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    bsSkinButton1: TbsSkinButton;
    bsBusinessSkinForm1: TbsBusinessSkinForm;
    t_sys_queryfields: TADOQuery;
    t_sys_queryfields1: TDataSource;
    t_sys_query_detail1: TDataSource;
    t_sys_query: TADOQuery;
    t_sys_query1: TDataSource;
    t_sys_query_detail: TADOQuery;
    bsSkinGroupBox3: TbsSkinGroupBox;
    Label6: TLabel;
    wwDBComboBox15: TwwDBComboBox;
    Edit1: TEdit;
    Label3: TLabel;
    wwDBComboBox4: TwwDBComboBox;
    Edit4: TEdit;
    Edit3: TEdit;
    wwDBComboBox5: TwwDBComboBox;
    Label2: TLabel;
    Edit2: TEdit;
    wwDBComboBox16: TwwDBComboBox;
    Label1: TLabel;
    wwDBComboBox2: TwwDBComboBox;
    wwDBComboBox7: TwwDBComboBox;
    Label4: TLabel;
    wwDBComboBox17: TwwDBComboBox;
    Label7: TLabel;
    wwDBComboBox1: TwwDBComboBox;
    Label10: TLabel;
    wwDBComboBox18: TwwDBComboBox;
    wwDBDateTimePicker1: TwwDBDateTimePicker;
    Label5: TLabel;
    wwDBComboBox6: TwwDBComboBox;
    wwDBDateTimePicker2: TwwDBDateTimePicker;
    wwDBDateTimePicker4: TwwDBDateTimePicker;
    wwDBComboBox11: TwwDBComboBox;
    Label11: TLabel;
    wwDBDateTimePicker3: TwwDBDateTimePicker;
    wwDBComboBox19: TwwDBComboBox;
    Label8: TLabel;
    Label21: TLabel;
    wwDBComboBox10: TwwDBComboBox;
    RxDBLookupCombo20: TRxDBLookupCombo;
    RxDBLookupCombo1: TRxDBLookupCombo;
    wwDBComboBox9: TwwDBComboBox;
    Label12: TLabel;
    RxDBLookupCombo2: TRxDBLookupCombo;
    wwDBComboBox21: TwwDBComboBox;
    Label9: TLabel;
    Label19: TLabel;
    wwDBComboBox22: TwwDBComboBox;
    wwDBComboBox8: TwwDBComboBox;
    Label18: TLabel;
    RxDBLookupCombo8: TRxDBLookupCombo;
    Edit7: TEdit;
    wwDBComboBox35: TwwDBComboBox;
    Label30: TLabel;
    RxDBLookupCombo10: TRxDBLookupCombo;
    wwDBComboBox28: TwwDBComboBox;
    Label22: TLabel;
    Label23: TLabel;
    wwDBComboBox29: TwwDBComboBox;
    RxDBLookupCombo12: TRxDBLookupCombo;
    RxDBLookupCombo3: TRxDBLookupCombo;
    wwDBComboBox14: TwwDBComboBox;
    Label14: TLabel;
    RxDBLookupCombo6: TRxDBLookupCombo;
    wwDBComboBox25: TwwDBComboBox;
    Label16: TLabel;
    Label15: TLabel;
    wwDBComboBox24: TwwDBComboBox;
    RxDBLookupCombo4: TRxDBLookupCombo;
    wwDBLookupCombo52: TwwDBLookupCombo;
    wwDBComboBox27: TwwDBComboBox;
    Label124: TLabel;
    Label20: TLabel;
    wwDBComboBox26: TwwDBComboBox;
    wwDBLookupCombo3: TwwDBLookupCombo;
    wwDBComboBox36: TwwDBComboBox;
    wwDBLookupCombo49: TwwDBLookupCombo;
    Label125: TLabel;
    bsSkinGroupBox4: TbsSkinGroupBox;
    Label13: TLabel;
    wwDBComboBox13: TwwDBComboBox;
    RxDBLookupCombo5: TRxDBLookupCombo;
    Label25: TLabel;
    wwDBComboBox30: TwwDBComboBox;
    wwDBComboBox31: TwwDBComboBox;
    RxDBLookupCombo15: TRxDBLookupCombo;
    Label37: TLabel;
    wwDBComboBox41: TwwDBComboBox;
    Label38: TLabel;
    wwDBComboBox42: TwwDBComboBox;
    RxDBLookupCombo16: TRxDBLookupCombo;
    wwDBComboBox3: TwwDBComboBox;
    Label17: TLabel;
    wwDBComboBox12: TwwDBComboBox;
    RxDBLookupCombo7: TRxDBLookupCombo;
    Label24: TLabel;
    wwDBComboBox20: TwwDBComboBox;
    wwDBDateTimePicker5: TwwDBDateTimePicker;
    Label26: TLabel;
    wwDBComboBox23: TwwDBComboBox;
    wwDBDateTimePicker6: TwwDBDateTimePicker;
    Label27: TLabel;
    wwDBDateTimePicker7: TwwDBDateTimePicker;
    Label28: TLabel;
    wwDBComboBox32: TwwDBComboBox;
    wwDBDateTimePicker8: TwwDBDateTimePicker;
    Label29: TLabel;
    wwDBComboBox33: TwwDBComboBox;
    wwDBDateTimePicker9: TwwDBDateTimePicker;
    RxDBLookupCombo19: TRxDBLookupCombo;
    Label75: TLabel;
    Label31: TLabel;
    RxDBLookupCombo9: TRxDBLookupCombo;
    wwDBDateTimePicker10: TwwDBDateTimePicker;
    wwDBComboBox49: TwwDBComboBox;
    Label45: TLabel;
    wwDBDateTimePicker11: TwwDBDateTimePicker;
    wwDBComboBox48: TwwDBComboBox;
    Label44: TLabel;
    Label48: TLabel;
    Edit12: TEdit;
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton5Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure RxDBLookupCombo14Change(Sender: TObject);
    procedure bsSkinButton2Click(Sender: TObject);
    procedure bsSkinButton4Click(Sender: TObject);
    procedure RxDBLookupList1DblClick(Sender: TObject);
    procedure wwDBComboBox15KeyPress(Sender: TObject; var Key: Char);
    procedure t_sys_queryAfterInsert(DataSet: TDataSet);
    procedure bsSkinButton6Click(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure t_sys_query_detailBeforePost(DataSet: TDataSet);
    procedure t_sys_query_detailAfterScroll(DataSet: TDataSet);
    procedure do_1(str_bill:widestring);
    procedure do_2(str_bill:widestring);
//    procedure do_2(str_bill:widestring);
    procedure do_5(str_bill:widestring);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_rp_total_query: Tfrm_rp_total_query;
  rp_seae_query_num,rp_seae_query_type:integer;
  rp_seae_query_str,rp_seae_query_limited:string;

implementation

uses u_main, u_data_share, my_sys_function,
  u_rp_no_total, u_rp_no_selfno, u_rp_no_accept, u_rp_drstl_self;

{$R *.dfm}


procedure Tfrm_rp_total_query.do_1(str_bill:widestring);
var
  sl:TStrings;
  sldate:string;
begin

  sldate:='开船日期';
  if (wwDBDateTimePicker1.Text='') and (wwDBDateTimePicker8.Text<>'')  then sldate:='预抵日期';


      frm_rp_no_selfno.p_accept_bill.close;
      frm_rp_no_selfno.p_accept_bill.Parameters.ParamByName('SQLStr').value:=
         'SELECT t_ch_fee.编号, t_ch_fee.客户名称,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 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.结算金额)*t_ch_fee.汇率 ELSE 0 END'
        +'-CASE t_ch_fee.类型 WHEN '+''''+'付'+''''
        +' THEN (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)-'
        +'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 '+''''+'RMB'+''''
        +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END)-'
        +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'付'+''''
        +' THEN (case t_ch_fee.币别 when '+''''+'RMB'+''''
        +' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) AS 差额RMB,'


        +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号,'
        +'v_op_bscard.委托单位,'
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,SUBSTRING(CONVERT(char(15), dbo.v_op_bscard.'+sldate+', 111), 1, 7) as 月份,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港,v_op_bscard.预抵日期,'
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10, v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.销售部门,v_op_bscard.航线,'
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,v_op_bscard.合计应收,v_op_bscard.合计应付,v_op_bscard.利润率,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) as 计费吨,'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()) AS 超期天数,'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()) AS 超期月数,0 as 是否超期,'
        +'isnull(t_crm_client.结费期限,0) as 结费期限,t_crm_client.客户全称,t_crm_client.客商编码,'
        +'t_ch_fee.应结日期,''                                                                                                               '' 客户合计'
        +',''                                                                                                             '' 月份合计'
        +',''                                                                     '' 客户账期'
        +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 left join t_crm_client '
        +' on t_ch_fee.客户名称=t_crm_client.客户简称 '
        +str_bill
        +' and t_ch_fee.类型='+''''+'收'+''''
        +' GROUP BY t_ch_fee.编号, t_ch_fee.客户名称,t_ch_fee.会计月份,'
        +'v_op_bscard.业务编号, v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位, '
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,SUBSTRING(CONVERT(char(15), dbo.v_op_bscard.'+sldate+', 111), 1, 7),'
        +'v_op_bscard.装货港, v_op_bscard.卸货港,v_op_bscard.预抵日期, '
        +'v_op_bscard.件数, v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10,v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU, v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.销售部门,v_op_bscard.航线, '
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,v_op_bscard.合计应收,v_op_bscard.合计应付,v_op_bscard.利润率,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end),DATEDIFF([day],v_op_bscard.会计期间, GETDATE()),'
        +' DATEDIFF([day],v_op_bscard.开船日期, GETDATE()),'
        +' DATEDIFF([day],v_op_bscard.开船日期, GETDATE()),'
        +'isnull(t_crm_client.结费期限,0),t_crm_client.客户全称,t_crm_client.客商编码,'
        +'t_ch_fee.应结日期 '
        +'order by v_op_bscard.业务类型,v_op_bscard.开船日期, v_op_bscard.船名, v_op_bscard.航次, v_op_bscard.主提单号 ';
//        +default_sort_two(53,'v_op_bscard','t_ch_fee.客户名称','','');

 //       showmessage(frm_rp_no_total.p_accept_bill.Parameters.ParamByName('SQLStr').value);

      frm_rp_no_selfno.p_accept_bill.Open;

{
      frm_rp_no_selfno.p_accept_fee.close;
      frm_rp_no_selfno.p_accept_fee.Parameters.ParamByName('SQLStr').value:=
        'SELECT t_ch_fee.编号, t_ch_fee.客户名称, t_ch_fee.费用名称,t_ch_fee.币别,t_ch_fee.金额 as 应收金额,'
        +'t_ch_fee.结算金额 as 已收金额,(t_ch_fee.金额-t_ch_fee.结算金额) as 未收金额,'
        +'t_ch_fee.标准 as 标准,t_ch_fee.单价 as 单价,t_ch_fee.数量 as 数量,t_ch_fee.备注 as 费用备注,'
        +'v_op_bscard.业务编号,  v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位,'
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港, '
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10,v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员, '
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) as 计费吨'
        +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill+' and t_ch_fee.类型='+''''+'收'+''''
        +'order by v_op_bscard.业务类型,v_op_bscard.开船日期, v_op_bscard.船名, v_op_bscard.航次, v_op_bscard.主提单号 ';
//        +default_sort_two(53,'v_op_bscard','t_ch_fee.客户名称','','');
      frm_rp_no_selfno.p_accept_fee.Open;
}
{
      frm_rp_no_selfno.p_pay_bill.close;
      frm_rp_no_selfno.p_pay_bill.Parameters.ParamByName('SQLStr').value:=
         'SELECT t_ch_fee.编号, t_ch_fee.客户名称,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_bscard.业务编号, v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位, '
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港, '
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10,v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员, '
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) as 计费吨'
        +''
        +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill+' and t_ch_fee.类型='+''''+'付'+''''
        +' GROUP BY t_ch_fee.编号, t_ch_fee.客户名称,'
        +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位, '
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港, '
        +'v_op_bscard.件数, v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10, v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员, v_op_bscard.航线,'
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) '
        +''
//        +default_sort_two(53,'v_op_bscard','t_ch_fee.客户名称','','');
         +'order by v_op_bscard.业务类型,v_op_bscard.开船日期, v_op_bscard.船名, v_op_bscard.航次, v_op_bscard.主提单号 ';
     frm_rp_no_selfno.p_pay_bill.Open;

      frm_rp_no_selfno.p_pay_fee.close;
      frm_rp_no_selfno.p_pay_fee.Parameters.ParamByName('SQLStr').value:=
        'SELECT t_ch_fee.编号, t_ch_fee.客户名称, t_ch_fee.费用名称,t_ch_fee.币别,t_ch_fee.金额 as 应付金额,'
        +'t_ch_fee.结算金额 as 已付金额,(t_ch_fee.金额-t_ch_fee.结算金额) as 未付金额,'
        +'t_ch_fee.标准 as 标准,t_ch_fee.单价 as 单价,t_ch_fee.数量 as 数量,t_ch_fee.备注 as 费用备注,'
        +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位,'
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港, '
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10, v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员, v_op_bscard.航线,'
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) as 计费吨'
        +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill+' and t_ch_fee.类型='+''''+'付'+''''
        +'order by v_op_bscard.业务类型,v_op_bscard.开船日期, v_op_bscard.船名, v_op_bscard.航次, v_op_bscard.主提单号 ';

//        +default_sort_two(53,'v_op_bscard','t_ch_fee.客户名称','','');
      frm_rp_no_selfno.p_pay_fee.Open;

      frm_rp_no_selfno.p_total_client.close;
      frm_rp_no_selfno.p_total_client.Parameters.ParamByName('SQLStr').value:=
         'SELECT t_ch_fee.客户名称,v_op_bscard.业务类型,'
        +'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 v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill+' GROUP BY t_ch_fee.客户名称,v_op_bscard.业务类型 order by t_ch_fee.客户名称,v_op_bscard.业务类型';
      frm_rp_no_selfno.p_total_client.Open;

      frm_rp_no_selfno.p_total_sales.close;
      frm_rp_no_selfno.p_total_sales.Parameters.ParamByName('SQLStr').value:=
         'SELECT v_op_bscard.揽货人,v_op_bscard.业务类型,'
        +'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 v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill+' GROUP BY v_op_bscard.揽货人,v_op_bscard.业务类型 order by v_op_bscard.揽货人,v_op_bscard.业务类型';
      frm_rp_no_selfno.p_total_sales.Open;

      frm_rp_no_selfno.p_total.close;
      frm_rp_no_selfno.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 v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
        +str_bill;
      frm_rp_no_selfno.p_total.Open;
}

end;

procedure Tfrm_rp_total_query.do_2(str_bill:widestring);
begin
  frm_rp_no_accept.p_accept_bill.close;
  frm_rp_no_accept.p_accept_bill.Parameters.ParamByName('SQLStr').value:=
         'SELECT t_ch_fee.编号, 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 未收合计,'
        +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号,'
        +'v_op_bscard.委托单位,'
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期, v_op_bscard.预抵日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港,'
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10, v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员,'
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end) as 计费吨,DATEDIFF([day],v_op_bscard.会计期间, GETDATE()) AS 会计天数,'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()) AS 超期天数,'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()) AS 超期月数,'
        +'t_ch_fee.应结日期'
        +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 left join t_crm_client '
        +' on t_ch_fee.客户名称=t_crm_client.客户简称 '
        +str_bill
        +' GROUP BY t_ch_fee.编号, t_ch_fee.客户名称,'
        +'v_op_bscard.业务编号, v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
        +'v_op_bscard.委托单位, '
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期, v_op_bscard.预抵日期,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港, '
        +'v_op_bscard.件数, v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10,v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU, v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.客服员, '
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,'
        +'(case when round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 '
        +'else v_op_bscard.尺码 end),4)>1 then '
        +'round((case when v_op_bscard.重量/1000>v_op_bscard.尺码 then v_op_bscard.重量/1000 else '
        +'v_op_bscard.尺码 end),4) else '
        +'1 end),DATEDIFF([day],v_op_bscard.会计期间, GETDATE()),'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()),'
        +' DATEDIFF([day],t_ch_fee.应结日期, GETDATE()),'
        +'t_ch_fee.应结日期 '
        +'order by v_op_bscard.揽货人,t_ch_fee.应结日期,v_op_bscard.主提单号 ';
      frm_rp_no_accept.p_accept_bill.Open;


end;


procedure Tfrm_rp_total_query.bsSkinButton10Click(Sender: TObject);
begin
  Edit1.text:='';
  Edit2.text:='';
  Edit3.text:='';
  Edit4.text:='';
  Edit12.text:='';
  wwDBComboBox1.text:='';
  wwDBComboBox2.text:='';
  wwDBComboBox3.text:='';
  wwDBDateTimePicker1.Clear;
  wwDBDateTimePicker2.Clear;
  wwDBDateTimePicker3.Clear;
  wwDBDateTimePicker4.Clear;
  RxDBLookupCombo20.ClearValue;
  RxDBLookupCombo2.ClearValue;
  RxDBLookupCombo1.ClearValue;
  RxDBLookupCombo8.ClearValue;
  wwDBLookupCombo3.text:='';
  wwDBLookupCombo52.text:='';
  RxDBLookupCombo4.ClearValue;
  RxDBLookupCombo3.ClearValue;
  RxDBLookupCombo6.ClearValue;
  RxDBLookupCombo5.ClearValue;
  RxDBLookupCombo10.ClearValue;
  RxDBLookupCombo12.ClearValue;
  RxDBLookupCombo7.ClearValue;
  wwDBComboBox31.text:='';
  wwDBComboBox31.text:='';
  RxDBLookupCombo16.ClearValue;
  RxDBLookupCombo15.ClearValue;
  wwDBDateTimePicker8.Clear;
  wwDBDateTimePicker9.Clear;
end;

procedure Tfrm_rp_total_query.bsSkinButton5Click(Sender: TObject);
begin
  close;
end;

procedure Tfrm_rp_total_query.FormShow(Sender: TObject);
begin
  wwDBComboBox15.text:='模糊';
  wwDBComboBox16.text:='模糊';
  wwDBComboBox4.text:='模糊';
  wwDBComboBox5.text:='模糊';
  wwDBComboBox35.text:='模糊';
  wwDBComboBox36.text:='模糊';
  wwDBComboBox17.text:='等于';
  wwDBComboBox7.text:='等于';
  wwDBComboBox18.text:='大于';
  wwDBComboBox6.text:='小于';
  wwDBComboBox11.text:='小于';
  wwDBComboBox19.text:='大于';
  wwDBComboBox10.text:='等于';
  wwDBComboBox21.text:='等于';
  wwDBComboBox22.text:='等于';
  wwDBComboBox24.text:='等于';
  wwDBComboBox25.text:='等于';
  wwDBComboBox26.text:='模糊';
  wwDBComboBox27.text:='模糊';
  wwDBComboBox9.text:='等于';
  wwDBComboBox8.text:='等于';
  wwDBComboBox13.text:='等于';
  wwDBComboBox14.text:='等于';
  wwDBComboBox28.text:='等于';
  wwDBComboBox29.text:='等于';
  wwDBComboBox30.text:='等于';
  wwDBComboBox13.text:='等于';
  wwDBComboBox41.text:='等于';
  wwDBComboBox30.text:='等于';
  wwDBComboBox42.text:='等于';
  wwDBComboBox12.text:='等于';
  wwDBComboBox20.text:='大于';
  wwDBComboBox23.text:='小于';
  wwDBComboBox32.text:='大于';
  wwDBComboBox33.text:='小于';
  wwDBComboBox48.text:='大于';
  wwDBComboBox49.text:='小于';

  frm_data_share.t_code_state.Filtered:=false;
  frm_data_share.t_code_state.Filter:='业务种类='+''''+'海运出口'+'''';
  frm_data_share.t_code_state.Filtered:=true;
  frm_data_share.t_code_fee.Filtered:=false;
  frm_data_share.t_code_fee.Filter:='海运=1';
  frm_data_share.t_code_fee.Filtered:=true;

  frm_data_share.t_code_state.first;
  while not frm_data_share.t_code_state.eof do
  begin
    wwDBComboBox1.Items.Add(frm_data_share.t_code_state.fieldbyname('业务状态').asstring);
    frm_data_share.t_code_state.next;
  end;
  wwDBComboBox1.Items.Add('业务封帐');

  t_sys_queryfields.open;

  t_sys_query.Parameters.ParamByName('查询类型').Value:=rp_seae_query_str;
  t_sys_query.Parameters.ParamByName('录入人').Value:=employee;
  t_sys_query.open;
  t_sys_query_detail.open;
  wwDBComboBox39.ItemIndex:=0;
end;

procedure Tfrm_rp_total_query.bsSkinButton1Click(Sender: TObject);
var
  str_bill:widestring;
begin
  str_bill:='';
  if bsSkinTabSheet2.Showing then
  begin
    str_bill:=query_tiaojian(1,str_bill,'v_op_bsCard.业务编号',wwDBComboBox15.text,Edit1.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.委托编号',wwDBComboBox4.text,Edit4.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.主提单号',wwDBComboBox16.text,Edit2.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.分提单号',wwDBComboBox5.text,Edit3.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.业务状态',wwDBComboBox17.text,wwDBComboBox1.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.费用状态',wwDBComboBox7.text,wwDBComboBox2.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.开船日期',wwDBComboBox18.text,wwDBDateTimePicker1.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.开船日期',wwDBComboBox6.text,wwDBDateTimePicker2.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.会计期间',wwDBComboBox19.text,wwDBDateTimePicker3.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.会计期间',wwDBComboBox11.text,wwDBDateTimePicker4.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.航线',wwDBComboBox10.text,RxDBLookupCombo20.DisplayValues[1]);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.委托单位',wwDBComboBox21.text,RxDBLookupCombo2.DisplayValues[1]);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.船公司',wwDBComboBox9.text,RxDBLookupCombo1.DisplayValues[1]);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.业务类型',wwDBComboBox22.text,wwDBComboBox3.Text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.货代公司',wwDBComboBox8.text,RxDBLookupCombo8.DisplayValues[1]);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.装货港',wwDBComboBox26.text,wwDBLookupCombo3.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.卸货港',wwDBComboBox27.text,wwDBLookupCombo52.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.客服员',wwDBComboBox24.text,RxDBLookupCombo4.DisplayValue);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.揽货人',wwDBComboBox14.text,RxDBLookupCombo3.DisplayValue);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.操作员',wwDBComboBox25.text,RxDBLookupCombo6.DisplayValue);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.销售部门',wwDBComboBox28.text,RxDBLookupCombo10.DisplayValue);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.操作部门',wwDBComboBox29.text,RxDBLookupCombo12.DisplayValue);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.船名',wwDBComboBox36.text,wwDBLookupCombo49.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.航次',wwDBComboBox35.text,Edit7.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.预抵日期',wwDBComboBox32.text,wwDBDateTimePicker8.text);
    str_bill:=query_tiaojian(1,str_bill,'v_op_bscard.预抵日期',wwDBComboBox33.text,wwDBDateTimePicker9.text);
    if RxDBLookupCombo9.DisplayValues[1]<>'' then
    begin
        if str_bill='' then
          str_bill:='where v_op_bscard.委托单位 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo9.DisplayValues[1]+''') '
        else
          str_bill:=str_bill+' and v_op_bscard.委托单位 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo9.DisplayValues[1]+''') '
    end;

    if (rp_seae_query_num<>5) then begin
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.客户名称',wwDBComboBox13.text,RxDBLookupCombo5.DisplayValues[1]);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.币别',wwDBComboBox41.text,RxDBLookupCombo15.DisplayValues[0]);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.费用名称',wwDBComboBox42.text,RxDBLookupCombo16.DisplayValues[1]);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.类型',wwDBComboBox30.text,wwDBComboBox31.text);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.所属部门',wwDBComboBox12.text,RxDBLookupCombo7.DisplayValue);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.应结日期',wwDBComboBox20.text,wwDBDateTimePicker5.text);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.应结日期',wwDBComboBox23.text,wwDBDateTimePicker6.text);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.会计月份',wwDBComboBox48.text,wwDBDateTimePicker11.text);
      str_bill:=query_tiaojian(1,str_bill,'t_ch_fee.会计月份',wwDBComboBox49.text,wwDBDateTimePicker10.text);

    if RxDBLookupCombo19.DisplayValues[1]<>'' then
    begin
        if str_bill='' then
          str_bill:='where t_ch_fee.客户名称 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo19.DisplayValues[1]+''') '
        else
          str_bill:=str_bill+' and t_ch_fee.客户名称 in (select 客户简称 from t_crm_client where 客户全称='''+RxDBLookupCombo19.DisplayValues[1]+''') '
    end;            
    end;

  end
  else
  begin
    if query_wanneng_error(t_sys_query_detail)then
    begin
      showmessage('‘或’的条件必须在2个以上！！');
      exit;
    end;
    str_bill:=query_wanneng(t_sys_query_detail);
  end;
  if rp_seae_query_limited<>'no'then
  begin
    if trim(str_bill)='' then
    begin
      str_bill:='where '+open_data(rp_seae_query_limited,'v_op_bscard.揽货人','v_op_bscard.操作员','v_op_bscard.客服员','no','no','no','no');
    end
    else
    begin
      str_bill:=str_bill+' and '+open_data(rp_seae_query_limited,'v_op_bscard.揽货人','v_op_bscard.操作员','v_op_bscard.客服员','no','no','no','no');
    end;
  end;
  case rp_seae_query_type of
    1,3:
    begin
      if str_bill='' then
        str_bill:='where t_ch_fee.金额<>t_ch_fee.结算金额  and (t_ch_fee.费用名称 <> ''汇兑损益'') '
      else
        str_bill:=str_bill+' and t_ch_fee.金额<>t_ch_fee.结算金额 and (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;
    5:
    begin
{
      if str_bill='' then
        str_bill:='where 合计应收=合计已收 and isnull(合计已收,0)<>0 '
      else
        str_bill:=str_bill+' and 合计应收=合计已收 and isnull(合计已收,0)<>0 ';
}        
    end;
  end;

  case rp_seae_query_num of
    1:
    begin
      if (wwDBDateTimePicker1.text<>'') and (wwDBDateTimePicker2.text<>'') then begin
        FmDate:=FormatDateTime('YYYY-MM-DD',wwDBDateTimePicker1.Date);
        ToDate:=FormatDateTime('YYYY-MM-DD',wwDBDateTimePicker2.Date);
      end;
      if (wwDBDateTimePicker7.text<>'') then begin
        ZhangDate:=FormatDateTime('YYYY-MM-DD',wwDBDateTimePicker7.Date);
      end;
      sqlstr:=str_bill;
      frm_rp_total_query.do_1(str_bill);

    end;
    3:
    begin
      frm_rp_total_query.do_2(str_bill);
    end;
    5:
    begin
      frm_rp_total_query.do_5(str_bill);
    end;
  end;
  close;
end;

procedure Tfrm_rp_total_query.RxDBLookupCombo14Change(Sender: TObject);
begin
  query_str_int.text:='';
  query_bit.text:='';
  query_date.text:='';
  if t_sys_queryfields.Locate('字段名称',RxDBLookupCombo14.DisplayValue,[])then
  begin
    wwDBComboBox40.ItemIndex:=-1;
    wwDBComboBox40.Items.Clear;
    if (t_sys_queryfields.FieldByName('类型').asstring='varchar') then
    begin
      query_str_int.Visible:=true;
      query_bit.Visible:=false;
      query_date.Visible:=false;
      wwDBComboBox40.Items.Add('等于');
      wwDBComboBox40.Items.Add('不等于');
      wwDBComboBox40.Items.Add('模糊');
            wwDBComboBox40.Items.Add('不包含');
      wwDBComboBox40.ItemIndex:=2;
    end;
    if (t_sys_queryfields.FieldByName('类型').asstring='int') then
    begin
      query_str_int.Visible:=true;
      query_bit.Visible:=false;
      query_date.Visible:=false;
      wwDBComboBox40.Items.Add('等于');
      wwDBComboBox40.Items.Add('不等于');
      wwDBComboBox40.Items.Add('大于');
      wwDBComboBox40.Items.Add('小于');
      wwDBComboBox40.ItemIndex:=0; 
    end;
    if (t_sys_queryfields.FieldByName('类型').asstring='bit') then
    begin
      query_str_int.Visible:=false;
      query_bit.Visible:=true;
      query_date.Visible:=false;
      wwDBComboBox40.Items.Add('等于');
      wwDBComboBox40.Items.Add('不等于');
      wwDBComboBox40.ItemIndex:=0;
    end;
    if (t_sys_queryfields.FieldByName('类型').asstring='date') then
    begin
      query_str_int.Visible:=false;
      query_bit.Visible:=false;
      query_date.Visible:=true;
      wwDBComboBox40.Items.Add('等于');
      wwDBComboBox40.Items.Add('不等于');
      wwDBComboBox40.Items.Add('大于');
      wwDBComboBox40.Items.Add('小于');
      wwDBComboBox40.ItemIndex:=0;
    end;  
  end
  else
  begin
    query_str_int.Visible:=false;
    query_bit.Visible:=false;
    query_date.Visible:=false;
  end;
end;

procedure Tfrm_rp_total_query.bsSkinButton2Click(Sender: TObject);
begin
  if RxDBLookupCombo14.DisplayValue='' then
  begin
    showmessage('请输入条件名称！！');
    exit;
  end;
  if wwDBComboBox39.ItemIndex=-1 then
  begin
    showmessage('请输入条件关系！！');
    exit;
  end;
  if wwDBComboBox40.ItemIndex=-1 then
  begin
    showmessage('请输入查询类型！！');
    exit;
  end;

  if query_str_int.Visible then
  begin
    if query_str_int.text='' then
    begin
      showmessage('请输入条件值！！');
      exit;
    end;
    if (t_sys_queryfields.FieldByName('类型').asstring='int') then
    begin
      try
        strtoint(query_str_int.text);
      except
        showmessage('条件值只能输入数字！！');
        exit;
      end;
    end;
  end;
  if query_bit.Visible then
  begin
    if query_bit.ItemIndex=-1 then
    begin
      showmessage('请输入条件值！！');
      exit;
    end;
  end;
  if query_date.Visible then
  begin
    if query_date.text='' then
    begin
      showmessage('请输入条件值！！');
      exit;
    end;
  end;
  if t_sys_query.IsEmpty then
  begin
    t_sys_query.Insert;
    t_sys_query.post;
  end;
  t_sys_query_detail.Append;
  t_sys_query_detail['表']:=t_sys_queryfields['表'];
  t_sys_query_detail['字段名']:=t_sys_queryfields['字段名称'];
  if query_str_int.Visible then
  begin
    t_sys_query_detail['条件值']:=query_str_int.text;
  end;
  if query_bit.Visible then
  begin
    t_sys_query_detail['条件值']:=query_bit.text;
  end;
  if query_date.Visible then
  begin
    t_sys_query_detail['条件值']:=query_date.text;
  end;
  t_sys_query_detail['字段类型']:=t_sys_queryfields['类型'];
  t_sys_query_detail['条件关系']:=wwDBComboBox39.text;
  t_sys_query_detail['查询类型']:=wwDBComboBox40.text;
  t_sys_query_detail.post;
end;

procedure Tfrm_rp_total_query.bsSkinButton4Click(Sender: TObject);
begin
  if not t_sys_query_detail.IsEmpty then
    t_sys_query_detail.Delete;
end;

procedure Tfrm_rp_total_query.RxDBLookupList1DblClick(Sender: TObject);
begin
  RxDBLookupCombo14.DisplayValue:=RxDBLookupList1.DisplayValue;
end;

procedure Tfrm_rp_total_query.wwDBComboBox15KeyPress(Sender: TObject;
  var Key: Char);
begin
  if key=#13 then
  begin
    key:=#0;
    frm_rp_total_query.bsSkinButton1Click(Sender);
  end;
end;

procedure Tfrm_rp_total_query.t_sys_queryAfterInsert(DataSet: TDataSet);
begin
  t_sys_query['查询类型']:=rp_seae_query_str;
  t_sys_query['录入人']:=employee;
  t_sys_query['录入日期']:=date;
end;

procedure Tfrm_rp_total_query.bsSkinButton6Click(Sender: TObject);
begin
  t_sys_query.Insert;
  t_sys_query.post;
end;

procedure Tfrm_rp_total_query.bsSkinButton9Click(Sender: TObject);
begin
  while not t_sys_query_detail.eof do
  begin
    t_sys_query_detail.Delete;
  end;
  if not t_sys_query.Eof then
    t_sys_query.delete;
end;

procedure Tfrm_rp_total_query.t_sys_query_detailBeforePost(
  DataSet: TDataSet);
begin
  if t_sys_query_detail.fieldbyname('字段类型').asstring='int' then
  begin
    try
      strtoint(t_sys_query_detail.fieldbyname('条件值').asstring);
    except
      showmessage('条件值只能输入数字！！');
      abort;
    end;
  end;
  if t_sys_query_detail.fieldbyname('字段类型').asstring='date' then
  begin
    try
      strtodate(t_sys_query_detail.fieldbyname('条件值').asstring);
    except
      showmessage('条件值只能输入日期！！');
      abort;
    end;
  end;
  if t_sys_query_detail.fieldbyname('字段类型').asstring='bit' then
  begin
    if (t_sys_query_detail.fieldbyname('条件值').asstring<>'否')
    and(t_sys_query_detail.fieldbyname('条件值').asstring<>'是')then
    begin
      showmessage('条件值只能输入是\否！！');
      abort;
    end;
  end;

  if (t_sys_query_detail.fieldbyname('条件关系').asstring<>'且')
  and(t_sys_query_detail.fieldbyname('条件关系').asstring<>'或')then
  begin
    showmessage('条件关系只能输入且\或！！');
    abort;
  end;

  if (dxDBGrid1Column3.Items.IndexOf(t_sys_query_detail.fieldbyname('查询类型').asstring)=-1)
  and (t_sys_query_detail.state<>dsinsert) then
  begin
    showmessage('查询类型只能输入'+dxDBGrid1Column3.Items.text+'！！');
    abort;
  end;
end;

procedure Tfrm_rp_total_query.t_sys_query_detailAfterScroll(
  DataSet: TDataSet);
begin
  if (t_sys_query_detail.fieldbyname('字段类型').asstring='varchar') then
  begin
    dxDBGrid1Column3.Items.Clear;
    dxDBGrid1Column3.Items.Add('等于');
    dxDBGrid1Column3.Items.Add('不等于');
    dxDBGrid1Column3.Items.Add('模糊');
  end;
  if (t_sys_query_detail.fieldbyname('字段类型').asstring='int') then
  begin
    dxDBGrid1Column3.Items.Clear;
    dxDBGrid1Column3.Items.Add('等于');
    dxDBGrid1Column3.Items.Add('不等于');
    dxDBGrid1Column3.Items.Add('大于');
    dxDBGrid1Column3.Items.Add('小于');
  end;
  if (t_sys_query_detail.fieldbyname('字段类型').asstring='bit') then
  begin
    dxDBGrid1Column3.Items.Clear;
    dxDBGrid1Column3.Items.Add('等于');
    dxDBGrid1Column3.Items.Add('不等于');
  end;
  if (t_sys_query_detail.fieldbyname('字段类型').asstring='date') then
  begin
    dxDBGrid1Column3.Items.Clear;
    dxDBGrid1Column3.Items.Add('等于');
    dxDBGrid1Column3.Items.Add('不等于');
    dxDBGrid1Column3.Items.Add('大于');
    dxDBGrid1Column3.Items.Add('小于');
  end;
end;

procedure Tfrm_rp_total_query.do_5(str_bill: widestring);
var
  sl:TStrings;
  sldate,stlstr:string;
begin

  sldate:='开船日期';
  if (wwDBDateTimePicker1.Text='') and (wwDBDateTimePicker8.Text<>'')  then sldate:='预抵日期';
      stlstr:=query_tiaojian(1,stlstr,'结算日期','大于',frm_rp_drstl_self.wwDBDateTimePicker5.text);
      stlstr:=query_tiaojian(1,stlstr,'结算日期','小于',frm_rp_drstl_self.wwDBDateTimePicker6.text);
      if (stlstr='') then stlstr:=' where 1=1 ';

      frm_rp_drstl_self.p_accept_bill.close;
      frm_rp_drstl_self.p_accept_bill.Parameters.ParamByName('SQLStr').value:=
         'SELECT v_op_bscard.编号,利润USD,应收USD,应付USD,已收USD,已付USD,利润RMB,应收RMB,应付RMB'
        +',已收RMB,已付RMB,合计应收 应收合计,合计应付 应付合计,毛利润,利润率,合计已收 已收合计,合计已付 已付合计,合计应收-合计已收 未收合计,'
        +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
        +'v_op_bscard.分提单号, v_op_bscard.委托编号,'
        +'v_op_bscard.委托单位,'
        +'v_op_bscard.航次, v_op_bscard.船名, v_op_bscard.开船日期,SUBSTRING(CONVERT(char(15), dbo.v_op_bscard.'+sldate+', 111), 1, 7) as 月份,'
        +'v_op_bscard.装货港, v_op_bscard.卸货港,v_op_bscard.预抵日期,'
        +'v_op_bscard.件数,  v_op_bscard.重量, v_op_bscard.尺码,'
        +'v_op_bscard.箱型1, v_op_bscard.箱型2, v_op_bscard.箱型3,'
        +'v_op_bscard.箱型4, v_op_bscard.箱型5, v_op_bscard.箱型6,'
        +'v_op_bscard.箱型7, v_op_bscard.箱型8, v_op_bscard.箱型9,'
        +'v_op_bscard.箱型10, v_op_bscard.其他箱型,'
        +'v_op_bscard.箱TEU,  v_op_bscard.操作员,'
        +'v_op_bscard.揽货人, v_op_bscard.销售部门,v_op_bscard.航线,(select top 1 客户全称 from t_crm_client where 客户简称=v_op_bscard.委托单位) 委托单位全称,'
        +'v_op_bscard.船公司, v_op_bscard.货代公司, v_op_bscard.报关行,j.结算金额,j.结算日期 结算完毕时间,j.工作编号 结算完毕账单号,j.客户名称,isnull(j.rowno,1) ISADD,'
        +'0 奖励合计,0 收回天数,''       '' 奖励比例,''                          '' 收款区间 '
        +' FROM v_op_bscard left join (select 业务编号,结算日期,工作编号,客户名称,SUM(原始金额*汇率) 结算金额,row_number() over (partition by 业务编号,结算日期,工作编号,客户名称 order by 结算日期) as rowno from v_fee_do_jie '+stlstr+' GROUP BY 业务编号,结算日期,工作编号,客户名称) j on  j.业务编号=v_op_bscard.编号 '
//        +' FROM v_op_bscard left join v_fee_do_jie j on  j.业务编号=v_op_bscard.编号 and rowno=1 '

        +str_bill
        +'order by v_op_bscard.业务类型,v_op_bscard.开船日期, v_op_bscard.船名, v_op_bscard.航次, v_op_bscard.主提单号 ';

      frm_rp_drstl_self.p_accept_bill.Open;
end;

end.