unit u_rp_fenzhang;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, bsSkinCtrls, dxExEdtr, dxCntner, dxTL, dxDBCtrl, dxDBGrid, DB,
  ADODB, dxDBTLCl, dxGrClms, ComCtrls, bsSkinTabs, wwdblook, RxLookup,
  wwdbdatetimepicker, StdCtrls, Menus, ExtCtrls, XPMenu;

type
  Tfrm_rp_fenzhang = class(TForm)
    p_fenzhang: TADOStoredProc;
    p_fenzhang1: TDataSource;
    bsSkinPageControl1: TbsSkinPageControl;
    bsSkinTabSheet1: TbsSkinTabSheet;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridColumn;
    dxDBGrid1Column2: TdxDBGridColumn;
    dxDBGrid1Column59: TdxDBGridColumn;
    dxDBGrid1DEBIT: TdxDBGridColumn;
    dxDBGrid1CREDIT: TdxDBGridColumn;
    dxDBGrid1Column3: TdxDBGridColumn;
    dxDBGrid1Column4: TdxDBGridColumn;
    dxDBGrid1Column5: TdxDBGridColumn;
    dxDBGrid1Column6: TdxDBGridColumn;
    dxDBGrid1Column7: TdxDBGridColumn;
    dxDBGrid1Column8: TdxDBGridColumn;
    dxDBGrid1Column9: TdxDBGridColumn;
    dxDBGrid1Column10: TdxDBGridColumn;
    dxDBGrid1Column11: TdxDBGridColumn;
    dxDBGrid1Column12: TdxDBGridColumn;
    dxDBGrid1Column13: TdxDBGridColumn;
    dxDBGrid1Column14: TdxDBGridColumn;
    dxDBGrid1Column15: TdxDBGridColumn;
    dxDBGrid1Column16: TdxDBGridColumn;
    dxDBGrid1Column17: TdxDBGridColumn;
    dxDBGrid1Column18: TdxDBGridColumn;
    dxDBGrid1Column19: TdxDBGridColumn;
    dxDBGrid1Column20: TdxDBGridColumn;
    dxDBGrid1Column21: TdxDBGridColumn;
    dxDBGrid1Column22: TdxDBGridColumn;
    dxDBGrid11: TdxDBGridColumn;
    dxDBGrid12: TdxDBGridColumn;
    dxDBGrid13: TdxDBGridColumn;
    dxDBGrid14: TdxDBGridColumn;
    dxDBGrid15: TdxDBGridColumn;
    dxDBGrid16: TdxDBGridColumn;
    dxDBGrid17: TdxDBGridColumn;
    dxDBGrid18: TdxDBGridColumn;
    dxDBGrid19: TdxDBGridColumn;
    dxDBGrid110: TdxDBGridColumn;
    dxDBGrid111: TdxDBGridColumn;
    dxDBGrid112: TdxDBGridColumn;
    dxDBGrid113: TdxDBGridColumn;
    dxDBGrid114: TdxDBGridColumn;
    dxDBGrid115: TdxDBGridColumn;
    dxDBGrid116: TdxDBGridColumn;
    dxDBGrid117: TdxDBGridColumn;
    dxDBGrid118: TdxDBGridColumn;
    dxDBGrid119: TdxDBGridColumn;
    dxDBGrid120: TdxDBGridColumn;
    dxDBGrid1Column43: TdxDBGridColumn;
    dxDBGrid1TEU: TdxDBGridColumn;
    dxDBGrid1Column45: TdxDBGridColumn;
    dxDBGrid1Column58: TdxDBGridColumn;
    dxDBGrid1Column48: TdxDBGridColumn;
    dxDBGrid1Column49: TdxDBGridColumn;
    dxDBGrid1Column50: TdxDBGridColumn;
    dxDBGrid1Column57: TdxDBGridColumn;
    dxDBGrid1Column56: TdxDBGridColumn;
    dxDBGrid1Column46: TdxDBGridColumn;
    dxDBGrid1Column47: TdxDBGridColumn;
    dxDBGrid1Column51: TdxDBGridColumn;
    dxDBGrid1Column52: TdxDBGridColumn;
    dxDBGrid1Column53: TdxDBGridColumn;
    dxDBGrid1Column54: TdxDBGridColumn;
    dxDBGrid1Column55: TdxDBGridColumn;
    bsSkinTabSheet2: TbsSkinTabSheet;
    p_sum_daili: TADOStoredProc;
    p_sum_daili1: TDataSource;
    p_sum_currency: TADOStoredProc;
    p_sum_currency1: TDataSource;
    dxDBGrid2: TdxDBGrid;
    dxDBGrid2Column1: TdxDBGridColumn;
    dxDBGrid2DEBIT: TdxDBGridColumn;
    dxDBGrid2CREDIT: TdxDBGridColumn;
    dxDBGrid3: TdxDBGrid;
    dxDBGridColumn1: TdxDBGridColumn;
    dxDBGridColumn2: TdxDBGridColumn;
    dxDBGridColumn3: TdxDBGridColumn;
    dxDBGrid3Column4: TdxDBGridColumn;
    PopupMenu2: TPopupMenu;
    N10: TMenuItem;
    N11: TMenuItem;
    N14: TMenuItem;
    N13: TMenuItem;
    N12: TMenuItem;
    N15: TMenuItem;
    N16: TMenuItem;
    Panel1: TPanel;
    bsSkinPanel1: TbsSkinPanel;
    Label6: TLabel;
    Label1: TLabel;
    Label10: TLabel;
    Label5: TLabel;
    Label11: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label23: TLabel;
    Label22: TLabel;
    Label124: TLabel;
    Label16: TLabel;
    Label14: TLabel;
    Label3: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label7: TLabel;
    Edit1: TEdit;
    Edit4: TEdit;
    Edit3: TEdit;
    Edit2: TEdit;
    wwDBDateTimePicker1: TwwDBDateTimePicker;
    wwDBDateTimePicker2: TwwDBDateTimePicker;
    wwDBDateTimePicker4: TwwDBDateTimePicker;
    wwDBDateTimePicker3: TwwDBDateTimePicker;
    RxDBLookupCombo2: TRxDBLookupCombo;
    RxDBLookupCombo10: TRxDBLookupCombo;
    RxDBLookupCombo12: TRxDBLookupCombo;
    wwDBLookupCombo52: TwwDBLookupCombo;
    RxDBLookupCombo6: TRxDBLookupCombo;
    RxDBLookupCombo3: TRxDBLookupCombo;
    RxDBLookupCombo1: TRxDBLookupCombo;
    wwDBLookupCombo3: TwwDBLookupCombo;
    bsSkinPanel2: TbsSkinPanel;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton6: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    XPMenu1: TXPMenu;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bsSkinButton5Click(Sender: TObject);
    procedure bsSkinButton6Click(Sender: TObject);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure N10Click(Sender: TObject);
    procedure N14Click(Sender: TObject);
    procedure N12Click(Sender: TObject);
    procedure N16Click(Sender: TObject);
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_rp_fenzhang: Tfrm_rp_fenzhang;

implementation

uses u_main, u_data_share, my_sys_function;

{$R *.dfm}

procedure Tfrm_rp_fenzhang.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  savereggrid(dxdbgrid1,caption);
  frm_rp_fenzhang.Hide;
  frm_rp_fenzhang.ManualFloat(frm_rp_fenzhang.BoundsRect );
  frm_main.freeTabs('frm_rp_fenzhang');
  action:=cafree;
  frm_rp_fenzhang:=nil;
end;

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

procedure Tfrm_rp_fenzhang.bsSkinButton6Click(Sender: TObject);
begin
  Edit1.text:='';
  Edit4.text:='';
  Edit2.text:='';
  Edit3.text:='';
  wwDBDateTimePicker1.text:='';
  wwDBDateTimePicker2.text:='';
  wwDBDateTimePicker3.text:='';
  wwDBDateTimePicker4.text:='';
  RxDBLookupCombo2.ClearValue;
  RxDBLookupCombo1.ClearValue;
  wwDBLookupCombo52.text:='';
  wwDBLookupCombo3.text:='';
  RxDBLookupCombo6.ClearValue;
  RxDBLookupCombo12.ClearValue;
  RxDBLookupCombo3.ClearValue;
  RxDBLookupCombo10.ClearValue;
end;

procedure Tfrm_rp_fenzhang.bsSkinButton10Click(Sender: TObject);
var
  str:widestring;
  str_fields:widestring;
begin
  str_fields:='t_op_seae.编号, t_op_seae.业务编号, t_op_seae.会计期间, t_op_seae.周次, t_op_seae.主提单号,';
  str_fields:=str_fields+'t_op_seae.分提单号, t_op_seae.委托编号, t_op_seae.装运方式, t_op_seae.委托单位, ';
  str_fields:=str_fields+'t_op_seae.发货人, t_op_seae.收货人, t_op_seae.通知人, t_op_seae.场站, t_op_seae.船名, ';
  str_fields:=str_fields+'t_op_seae.航次, t_op_seae.开船日期, t_op_seae.截港日期, t_op_seae.预抵日期, ';
  str_fields:=str_fields+'t_op_seae.装货港, t_op_seae.卸货港, t_op_seae.件数, t_op_seae.包装, t_op_seae.重量, ';
  str_fields:=str_fields+'t_op_seae.尺码, t_op_seae.箱型1, t_op_seae.箱型2, t_op_seae.箱型3, t_op_seae.箱型4, ';
  str_fields:=str_fields+'t_op_seae.箱型5, t_op_seae.箱型6, t_op_seae.箱型7, t_op_seae.箱型8, t_op_seae.箱型9, ';
  str_fields:=str_fields+'t_op_seae.箱型10,t_op_seae.其他箱型, t_op_seae.箱TEU, t_op_seae.集装箱, t_op_seae.录入人, ';
  str_fields:=str_fields+'t_op_seae.录入日期, t_op_seae.操作员, t_op_seae.揽货人, t_op_seae.客服员, t_op_seae.航线, ';
  str_fields:=str_fields+'t_op_seae.船公司, t_op_seae.货代公司, t_op_seae.报关行, t_op_seae.承运车队, ';
  str_fields:=str_fields+'t_op_seae.销售部门, t_op_seae.操作部门, t_op_seae.业务来源, t_ch_fee_fenzhang.代理名称,t_ch_fee_fenzhang.币别';
  str:=' where '+open_data('0037','t_op_seae.揽货人','t_op_seae.操作员','t_op_seae.客服员','t_op_seae.报关操作','t_op_seae.报检操作');
  if Edit1.text<>''then
    str:=str+' and t_op_seae.业务编号 like '+''''+Edit1.text+'''';
  if Edit4.text<>''then
    str:=str+' and t_op_seae.委托编号 like '+''''+Edit4.text+'''';
  if Edit2.text<>''then
    str:=str+' and t_op_seae.主提单号 like '+''''+Edit2.text+'''';
  if Edit3.text<>''then
    str:=str+' and t_op_seae.分提单号 like '+''''+Edit3.text+'''';
  if wwDBDateTimePicker1.text<>''then
    str:=str+' and t_op_seae.开船日期>='+''''+wwDBDateTimePicker1.text+'''';
  if wwDBDateTimePicker2.text<>''then
    str:=str+' and t_op_seae.开船日期<='+''''+wwDBDateTimePicker2.text+'''';
  if wwDBDateTimePicker3.text<>''then
    str:=str+' and t_op_seae.会计期间>='+''''+wwDBDateTimePicker3.text+'''';
  if wwDBDateTimePicker4.text<>''then
    str:=str+' and t_op_seae.会计期间<='+''''+wwDBDateTimePicker4.text+'''';
  if RxDBLookupCombo2.DisplayValue<>''then
    str:=str+' and t_op_seae.委托单位='+''''+RxDBLookupCombo2.DisplayValues[1]+'''';
  if RxDBLookupCombo1.DisplayValue<>''then
    str:=str+' and t_ch_fee_fenzhang.代理名称='+''''+RxDBLookupCombo1.DisplayValues[1]+'''';
  if RxDBLookupCombo6.DisplayValue<>''then
    str:=str+' and t_op_seae.操作员='+''''+RxDBLookupCombo6.DisplayValue+'''';
  if RxDBLookupCombo12.DisplayValue<>''then
    str:=str+' and t_op_seae.操作部门='+''''+RxDBLookupCombo12.DisplayValue+'''';
  if RxDBLookupCombo3.DisplayValue<>''then
    str:=str+' and t_op_seae.揽货人='+''''+RxDBLookupCombo3.DisplayValue+'''';
  if RxDBLookupCombo10.DisplayValue<>''then
    str:=str+' and t_op_seae.销售部门='+''''+RxDBLookupCombo10.DisplayValue+'''';

  if wwDBLookupCombo52.text<>''then
    str:=str+' and t_op_seae.卸货港 like '+''''+wwDBLookupCombo52.text+'''';
  if wwDBLookupCombo3.text<>''then
    str:=str+' and t_ch_fee_fenzhang.币别='+''''+wwDBLookupCombo3.text+'''';

  p_fenzhang.close;
  p_fenzhang.Parameters.ParamByName('SQLStr').value:='select '
    +str_fields
    +', SUM(t_ch_fee_fenzhang.应收金额) AS DEBIT,'
    +'SUM(t_ch_fee_fenzhang.应付金额) AS CREDIT '
    +'FROM t_op_seae INNER JOIN '
    +'t_ch_fee_fenzhang ON '
    +'t_op_seae.编号 = t_ch_fee_fenzhang.编号'
    +str
    +' group by '+str_fields
    +default_sort(53,'t_op_seae','','desc');
  p_fenzhang.open;

  p_sum_daili.close;
  p_sum_daili.Parameters.ParamByName('SQLStr').value:=
    'select t_ch_fee_fenzhang.代理名称,t_ch_fee_fenzhang.币别'
    +', SUM(t_ch_fee_fenzhang.应收金额) AS DEBIT,'
    +'SUM(t_ch_fee_fenzhang.应付金额) AS CREDIT '
    +'FROM t_op_seae INNER JOIN '
    +'t_ch_fee_fenzhang ON '
    +'t_op_seae.编号 = t_ch_fee_fenzhang.编号'
    +str
    +' group by t_ch_fee_fenzhang.代理名称,t_ch_fee_fenzhang.币别'
    +' order by t_ch_fee_fenzhang.代理名称,t_ch_fee_fenzhang.币别';
  p_sum_daili.open;

  p_sum_currency.close;
  p_sum_currency.Parameters.ParamByName('SQLStr').value:=
    'select t_ch_fee_fenzhang.币别'
    +', SUM(t_ch_fee_fenzhang.应收金额) AS DEBIT,'
    +'SUM(t_ch_fee_fenzhang.应付金额) AS CREDIT '
    +'FROM t_op_seae INNER JOIN '
    +'t_ch_fee_fenzhang ON '
    +'t_op_seae.编号 = t_ch_fee_fenzhang.编号'
    +str
    +' group by t_ch_fee_fenzhang.币别'
    +' order by t_ch_fee_fenzhang.币别';
  p_sum_currency.open; 
end;

procedure Tfrm_rp_fenzhang.bsSkinButton9Click(Sender: TObject);
begin
  sys_print('海运代理分帐统计',2,nil,nil,nil,nil,p_fenzhang1,p_sum_daili1,p_sum_currency1,nil,nil,nil,nil);
end;

procedure Tfrm_rp_fenzhang.FormShow(Sender: TObject);
begin
  loadreggrid(dxdbgrid1,true,caption);
end;

procedure Tfrm_rp_fenzhang.N10Click(Sender: TObject);
begin
  update_grid_view_no(dxdbgrid1);
end;

procedure Tfrm_rp_fenzhang.N14Click(Sender: TObject);
begin
  update_grid_view_all(dxdbgrid1);
end;

procedure Tfrm_rp_fenzhang.N12Click(Sender: TObject);
begin
  update_grid(dxdbgrid1,97);
end;

procedure Tfrm_rp_fenzhang.N16Click(Sender: TObject);
begin
  grid_save_xls(dxdbgrid1);
end;

procedure Tfrm_rp_fenzhang.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
  if key=#13 then
  begin
    key:=#0;
    frm_rp_fenzhang.bsSkinButton10Click(Sender);
  end;
end;

end.