unit u_op_seae_huanhuo;

interface

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

type
  Tfrm_op_seae_huanhuo = class(TForm)
    Panel1: TPanel;
    dxDBGrid2: TdxDBGrid;
    p_huanhuo: TADOStoredProc;
    p_huanhuo1: TDataSource;
    p_huanhuo_sum1: TDataSource;
    p_huanhuo_sum: TADOStoredProc;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridMaskColumn;
    dxDBGrid1Column4: TdxDBGridMaskColumn;
    dxDBGrid1Column5: TdxDBGridMaskColumn;
    dxDBGrid1Column6: TdxDBGridMaskColumn;
    dxDBGrid1Column7: TdxDBGridMaskColumn;
    dxDBGrid1Column8: TdxDBGridMaskColumn;
    dxDBGrid1Column14: TdxDBGridMaskColumn;
    dxDBGrid1Column15: TdxDBGridMaskColumn;
    dxDBGrid1Column16: TdxDBGridDateColumn;
    dxDBGrid1Column19: TdxDBGridMaskColumn;
    dxDBGrid1Column20: TdxDBGridMaskColumn;
    dxDBGrid1Column37: TdxDBGridMaskColumn;
    dxDBGrid1Column38: TdxDBGridMaskColumn;
    dxDBGrid1Column39: TdxDBGridColumn;
    dxDBGrid1Column40: TdxDBGridColumn;
    dxDBGrid1Column64: TdxDBGridMaskColumn;
    dxDBGrid1Column65: TdxDBGridDateColumn;
    dxDBGrid1Column66: TdxDBGridMaskColumn;
    dxDBGrid1Column67: TdxDBGridMaskColumn;
    dxDBGrid1Column21: TdxDBGridColumn;
    dxDBGrid1Column22: TdxDBGridColumn;
    dxDBGrid1Column23: TdxDBGridColumn;
    dxDBGrid1Column24: TdxDBGridColumn;
    dxDBGrid2Column1: TdxDBGridColumn;
    dxDBGrid2Column2: TdxDBGridColumn;
    dxDBGrid2Column3: TdxDBGridColumn;
    dxDBGrid2Column4: TdxDBGridColumn;
    Panel2: TPanel;
    PopupMenu2: TPopupMenu;
    N10: TMenuItem;
    N11: TMenuItem;
    N14: TMenuItem;
    N13: TMenuItem;
    N12: TMenuItem;
    N15: TMenuItem;
    N16: TMenuItem;
    XPMenu1: TXPMenu;
    bsSkinPanel1: TbsSkinPanel;
    Label3: TLabel;
    Label10: TLabel;
    wwDBDateTimePicker1: TwwDBDateTimePicker;
    Edit4: TEdit;
    Label1: TLabel;
    Label5: TLabel;
    Edit2: TEdit;
    wwDBDateTimePicker2: TwwDBDateTimePicker;
    Label2: TLabel;
    Label4: TLabel;
    Edit3: TEdit;
    RxDBLookupCombo1: TRxDBLookupCombo;
    Label16: TLabel;
    Label7: TLabel;
    wwDBComboBox1: TwwDBComboBox;
    RxDBLookupCombo6: TRxDBLookupCombo;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton6: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bsSkinButton5Click(Sender: TObject);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton6Click(Sender: TObject);
    procedure dxDBGrid1ColumnSorting(Sender: TObject;
      Column: TdxDBTreeListColumn; var Allow: Boolean);
    procedure N10Click(Sender: TObject);
    procedure N14Click(Sender: TObject);
    procedure N12Click(Sender: TObject);
    procedure N16Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
    procedure RxDBLookupCombo6Exit(Sender: TObject);
    procedure RxDBLookupCombo6Enter(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_op_seae_huanhuo: Tfrm_op_seae_huanhuo;

implementation

uses u_main, u_data_share, my_sys_function;

{$R *.dfm}

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

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

procedure Tfrm_op_seae_huanhuo.bsSkinButton10Click(Sender: TObject);
var
  str,str_detail,str_sum,str_jiezhuan:widestring;
begin
  str:='where '+open_data('0001','t_op_seae.录入人','t_op_seae.操作员','t_op_seae.客服员','t_op_seae.报关操作','t_op_seae.报检操作');
  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 wwDBDateTimePicker2.text<>''then
    str:=str+' and t_op_seae.开船日期<='+''''+wwDBDateTimePicker2.text+'''';
  if RxDBLookupCombo6.DisplayValues[1]<>''then
    str:=str+' and t_op_seae.操作员='+''''+RxDBLookupCombo6.DisplayValues[1]+'''';
  if RxDBLookupCombo1.DisplayValues[1]<>''then
    str:=str+' and t_op_seae_huanhuo.换货客户='+''''+RxDBLookupCombo1.DisplayValues[1]+'''';
  if wwDBComboBox1.text<>''then
    str:=str+' and t_op_seae_huanhuo.交换方式='+''''+wwDBComboBox1.text+'''';
  if wwDBDateTimePicker1.text<>''then
  begin
    str_jiezhuan:=str+' and t_op_seae.开船日期<'+''''+wwDBDateTimePicker1.text+'''';
    str:=str+' and t_op_seae.开船日期>='+''''+wwDBDateTimePicker1.text+'''';
  end;

  str_detail:='SELECT t_op_seae_huanhuo.编号, t_op_seae_huanhuo.换货客户,'
             +'t_op_seae_huanhuo.交换方式, t_op_seae_huanhuo.录入人,'
             +'t_op_seae_huanhuo.录入日期, t_op_seae.业务状态, t_op_seae.费用状态,'
             +'t_op_seae.业务编号, t_op_seae.会计期间, t_op_seae.周次,'
             +'t_op_seae.主提单号, t_op_seae.分提单号, t_op_seae.委托编号,'
             +'t_op_seae.委托单位, t_op_seae.装运方式, t_op_seae.发货人,'
             +'t_op_seae.收货人, t_op_seae.通知人, t_op_seae.代理,'
             +'t_op_seae.场站, t_op_seae.船名, t_op_seae.航次,'
             +'t_op_seae.开船日期, t_op_seae.截港日期, t_op_seae.预抵日期,'
             +'t_op_seae.起运港, t_op_seae.装货港, t_op_seae.卸货港,'
             +'t_op_seae.二程开船日期, t_op_seae.二程港口, t_op_seae.二程船名,'
             +'t_op_seae.二程航次, t_op_seae.目的地, t_op_seae.交货地点,'
             +'t_op_seae.提单份数, t_op_seae.签单方式, t_op_seae.签单地点,'
             +'t_op_seae.签单日期, t_op_seae.付费方式, t_op_seae.预付地点,'
             +'t_op_seae.到付地点, t_op_seae.运输条款, t_op_seae.件数,'
             +'t_op_seae.包装, t_op_seae.重量, t_op_seae.尺码, t_op_seae.操作员,'
             +'t_op_seae.揽货人, t_op_seae.客服员, t_op_seae.航线,'
             +'t_op_seae.船公司, t_op_seae.货代公司, t_op_seae.报关行,'
             +'t_op_seae.承运车队, t_op_seae.报关服务, t_op_seae.报验服务,'
             +'t_op_seae.熏蒸服务, t_op_seae.仓储服务, t_op_seae.拖车服务,'
             +'t_op_seae.报关日期, t_op_seae.报关员, t_op_seae.报关单号,'
             +'t_op_seae.核销单号, t_op_seae.手册号, t_op_seae.经营单位,'
             +'t_op_seae.单位代码, t_op_seae.合同号, t_op_seae.报关备注,'
             +'t_op_seae.是否退税, t_op_seae.退税日期, t_op_seae.是否通关,'
             +'t_op_seae.通关日期, t_op_seae.危险品分类, t_op_seae.危险品编号,'
             +'t_op_seae.冷藏通风量, t_op_seae.温度单位, t_op_seae.设置温度,'
             +'t_op_seae.最低温度, t_op_seae.最高温度, t_op_seae.货物标识,'
             +'t_op_seae.发票号, t_op_seae.商品编码, t_op_seae.销售部门,'
             +'t_op_seae.操作部门, t_op_seae.工作量, t_op_seae.业务来源,'
             +'(CASE 交换方式 WHEN '
             +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 ELSE '
             +'尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 ELSE '
             +'尺码 END) ELSE 1 END) else 0 end) AS 换出计费吨,'

             +'(CASE 交换方式 WHEN '
             +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 ELSE '
             +'尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 ELSE '
             +'尺码 END) else 1 end) else 0 end) AS 换入计费吨 '
             +'FROM t_op_seae_huanhuo INNER JOIN '
             +'t_op_seae ON t_op_seae_huanhuo.编号 = t_op_seae.编号 '
             +str
             +default_sort(53,'t_op_seae','','');
  if str_jiezhuan=''then
  begin
    str_sum:='SELECT t_op_seae_huanhuo.换货客户,'
          +'SUM(CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) else 0 end) AS 换出,'
          
          +'SUM(CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) else 0 end) AS 换入,'
          
          +'SUM((CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) else 0 end) '
          +'- (CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +' ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +' ELSE 尺码 END) ELSE 0 END) else 0 end)) AS 差额 '
          +'FROM t_op_seae_huanhuo INNER JOIN '
          +'t_op_seae ON t_op_seae_huanhuo.编号 = t_op_seae.编号 '
          +str
          +' GROUP BY t_op_seae_huanhuo.换货客户 '
          +' order by 换货客户';
  end
  else
  begin
    str_sum:='SELECT t_op_seae_huanhuo.换货客户,'
          +'SUM(CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) AS 换出,'
          
          +'SUM(CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) AS 换入,'
          
          +'SUM((CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) '
          
          +'- (CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END)) AS 差额 '
          +'FROM t_op_seae_huanhuo INNER JOIN '
          +'t_op_seae ON t_op_seae_huanhuo.编号 = t_op_seae.编号 '
          +str
          +' GROUP BY t_op_seae_huanhuo.换货客户 '
          
          +'union SELECT t_op_seae_huanhuo.换货客户,'
          +'SUM(CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) AS 换出,'

          +'SUM(CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) AS 换入,'

          +'SUM((CASE 交换方式 WHEN '
          +''''+'换出'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END) '

          +'- (CASE 交换方式 WHEN '
          +''''+'换入'+''''+' THEN (case when (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END)>1 then (CASE WHEN 重量 / 1000 > 尺码 THEN 重量 / 1000 '
          +'ELSE 尺码 END) else 1 end) ELSE 0 END)) AS 差额 '
          
          +'FROM t_op_seae_huanhuo INNER JOIN '
          +'t_op_seae ON t_op_seae_huanhuo.编号 = t_op_seae.编号 '
          +str_jiezhuan
          +' GROUP BY t_op_seae_huanhuo.换货客户 ' ;
  end;
  p_huanhuo.close;
  p_huanhuo.Parameters.ParamByName('SQLStr').value:=str_detail;
  p_huanhuo.open;

  p_huanhuo_sum.close;
  p_huanhuo_sum.Parameters.ParamByName('SQLStr').value:=str_sum;
  p_huanhuo_sum.open;
end;

procedure Tfrm_op_seae_huanhuo.bsSkinButton6Click(Sender: TObject);
begin
  Edit4.text:='';
  Edit2.text:='';
  Edit3.text:='';
  wwDBDateTimePicker1.text:='';
  wwDBDateTimePicker2.text:='';
  RxDBLookupCombo6.ClearValue;
  RxDBLookupCombo1.ClearValue;
  wwDBComboBox1.text:='';
end;

procedure Tfrm_op_seae_huanhuo.dxDBGrid1ColumnSorting(Sender: TObject;
  Column: TdxDBTreeListColumn; var Allow: Boolean);
begin
  Column_sort_pro(Column,p_huanhuo,'业务编号');
end;

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

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

procedure Tfrm_op_seae_huanhuo.N12Click(Sender: TObject);
begin
  update_grid(dxdbgrid1,96);
end;

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

procedure Tfrm_op_seae_huanhuo.FormShow(Sender: TObject);
begin
  loadreggrid(dxdbgrid1,false,caption);
end;

procedure Tfrm_op_seae_huanhuo.bsSkinButton9Click(Sender: TObject);
begin
  sys_print('海运出口拼箱换货',2,nil,nil,nil,nil,p_huanhuo1,p_huanhuo_sum1,nil,nil,nil,nil,nil);
end;

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

procedure Tfrm_op_seae_huanhuo.RxDBLookupCombo6Exit(Sender: TObject);
begin
TRxDBLookupCombo(Sender).LookupDisplay:='姓名';
end;

procedure Tfrm_op_seae_huanhuo.RxDBLookupCombo6Enter(Sender: TObject);
begin
TRxDBLookupCombo(Sender).LookupDisplay:='代码;姓名';
end;

end.