unit u_rp_no_histry;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, bsSkinCtrls, ExtCtrls, ComCtrls, bsSkinTabs, DB, ADODB,DateUtils,
  dxExEdtr, dxDBTLCl, dxGrClms, dxDBGrid, dxTL, dxDBCtrl, dxCntner,
  kbmMemTable, kbmMemBinaryStreamFormat, Menus, StdCtrls,
  wwdbdatetimepicker, Grids, DBGridEh, Mask, wwdbedit, Wwdotdot, Wwdbcomb,teeprevi,
  RxLookup, TeEngine, Series, TeeProcs, Chart, DbChart, DBGrids;

type
  Tfrm_rp_no_histry = class(TForm)
    Panel1: TPanel;
    bsSkinPanel2: TbsSkinPanel;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    bsSkinButton1: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    SaveDialog: TSaveDialog;
    kbmBinaryStreamFormat1: TkbmBinaryStreamFormat;
    kbmThreadDataSet1: TkbmThreadDataSet;
    PopupMenu1: TPopupMenu;
    N1101: TMenuItem;
    memtblDetail: TkbmMemTable;
    p_total1: TDataSource;
    bsSkinPanel1: TbsSkinPanel;
    Label5: TLabel;
    Label10: TLabel;
    Label1: TLabel;
    cbdate: TwwDBComboBox;
    Label2: TLabel;
    wwDBComboBox2: TwwDBComboBox;
    Label3: TLabel;
    cbYear: TwwDBComboBox;
    cbbmonth: TwwDBComboBox;
    cbemonth: TwwDBComboBox;
    Label13: TLabel;
    RxDBLookupCombo5: TRxDBLookupCombo;
    Label14: TLabel;
    wwDBComboBox14: TwwDBComboBox;
    RxDBLookupCombo3: TRxDBLookupCombo;
    bsSkinPageControl1: TbsSkinPageControl;
    bsSkinTabSheet1: TbsSkinTabSheet;
    bsSkinTabSheet2: TbsSkinTabSheet;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column5: TdxDBGridColumn;
    dxDBGridMaskColumn95: TdxDBGridMaskColumn;
    DBChart3: TDBChart;
    Series1: TLineSeries;
    memtotal1: TDataSource;
    memtotal: TkbmMemTable;
    memtotalField: TStringField;
    memtotalField2: TFloatField;
    procedure bsSkinButton5Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Panel1Resize(Sender: TObject);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure bsSkinCheckRadioBox3Click(Sender: TObject);
    procedure bsSkinCheckRadioBox2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure N1101Click(Sender: TObject);
  private
    procedure GetNoFee;
    procedure Creatememtbl;

    { Private declarations }
  public
    sqlstr:WideString;
    { Public declarations }
  end;

var
  frm_rp_no_histry: Tfrm_rp_no_histry;
  FmDate,ToDate:String;

implementation

uses u_main, u_rp_no_seae_query,my_sys_function, u_rp_total_query,
  u_rp_no_total, u_sys_progress, u_rp_no_selfno;

{$R *.dfm}


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

procedure Tfrm_rp_no_histry.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  frm_rp_no_histry.Hide;
  frm_rp_no_histry.ManualFloat(frm_rp_no_histry.BoundsRect );
  frm_main.freeTabs('frm_rp_no_histry');
  action:=cafree;
  frm_rp_no_histry:=nil;
end;

procedure Tfrm_rp_no_histry.Panel1Resize(Sender: TObject);
var
  i:integer;
begin
  i:=round(bsSkinPanel2.Width/7);
  bsSkinButton10.Width:=i;
  bsSkinButton9.Width:=i;
  bsSkinButton1.Width:=i;
end;

procedure Tfrm_rp_no_histry.bsSkinButton10Click(Sender: TObject);
begin
  if cbyear.Text='' then begin
    MessageDlg('统计年不能为空!',mtWarning,[mbOk],0);
    exit;
  end;

  if cbbmonth.Text='' then begin
    MessageDlg('开始月份不能为空!',mtWarning,[mbOk],0);
    exit;
  end;
  if cbemonth.Text='' then begin
    MessageDlg('结束月份不能为空!',mtWarning,[mbOk],0);
    exit;
  end;

  if strtoint(cbemonth.Text)<strtoint(cbbmonth.Text) then begin
    MessageDlg('开始月份不能大于结束月份!',mtWarning,[mbOk],0);
    exit;
  end;

  if cbdate.Text='' then begin
    MessageDlg('统计日期不能为空!',mtWarning,[mbOk],0);
    exit;
  end;
  if wwDBComboBox2.Text='' then begin
    MessageDlg('统计标准不能为空!',mtWarning,[mbOk],0);
    exit;
  end;

//    sDate:=FormatDateTime('YYYYMM',aDate);
//    aYear:=FormatDateTime('YYYY',aDate);
    sqlstr:='';
    query_seae(12,'0033');
    if sqlstr='' then sqlstr:=' where 1=1 ';
    Creatememtbl;
    GetNoFee;
  
end;

procedure Tfrm_rp_no_histry.bsSkinButton1Click(Sender: TObject);
begin
  with SaveDialog do
  begin
    DefaultExt :='xls';
    Filter := '(*.xls)|*.xls';
    if Execute then
    begin
      dxDBGrid1.SaveToXLS(FileName,true)
    end;
  end;
end;

procedure Tfrm_rp_no_histry.bsSkinButton9Click(Sender: TObject);
begin
  sys_print('业务综合欠费',2,nil,nil,nil,nil,p_total1,nil,nil,nil,nil,nil,nil);
end;

procedure Tfrm_rp_no_histry.GetNoFee;
var
  aQuery:TAdoQuery;
  aDate,aToDate:TDate;
  strdate,sDate:String;
  i:integer;

begin
  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
      aDate:=EncodeDate(strtoint(cbYear.Text),strtoint(cbbmonth.Text),1);
      aToDate:=EncodeDate(strtoint(cbYear.Text),strtoint(cbemonth.Text),2);
      if not ASSIGNED(frm_sys_progress) then
        frm_sys_progress:=tfrm_sys_progress.Create(application);
      frm_sys_progress.Show;
      frm_sys_progress.bsSkinGauge1.MaxValue:=MonthsBetween(aDate,aToDate)*2;
      frm_sys_progress.bsSkinGauge1.MinValue:=0;
      frm_sys_progress.bsSkinGauge1.Value:=0;
      frm_sys_progress.Update;

      while not (aDate>aToDate) do begin
        if MonthDays[isLeapYear(strtoint(cbYear.Text)),strtoint(FormatDateTime('MM',aDate))]<strtoint(cbdate.text) then
          strdate:=FormatDateTime('YYYY-MM-DD',EncodeDate(strtoint(cbYear.Text),strtoint(FormatDateTime('MM',aDate)),MonthDays[isLeapYear(strtoint(cbYear.Text)),strtoint(FormatDateTime('MM',aDate))]))
        else
          strdate:=FormatDateTime('YYYY-MM',aDate)+'-'+cbdate.Text;
        sDate:=FormatDateTime('YYYYMM',aDate);
        CLOSE;
        SQL.Clear;
        SQL.Add('Select t_ch_fee.客户名称, ');
        SQL.Add('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,');
        SQL.Add('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,');
        SQL.Add('sum(case t_ch_fee.类型 when ''收'' then (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 else 0 end) as 未收合计');
        if RxDBLookupCombo3.DisplayValue<>'' then begin
          SQL.Add('from t_ch_fee inner join t_op_seae on t_ch_fee.编号=t_op_seae.编号 ');
          SQL.Add(sqlstr);
          sql.Add(' and t_op_seae.开船日期<='''+strdate+'''');
          if RxDBLookupCombo5.DisplayValues[1]<>'' then
            SQL.Add(' and t_ch_fee.客户名称='''+RxDBLookupCombo5.DisplayValues[1]+'''');
          if wwDBComboBox14.Text='不等于' then
            SQL.Add(' and t_op_seae.揽货人<>'''+RxDBLookupCombo3.DisplayValues[0]+'''')
          else
            SQL.Add(' and t_op_seae.揽货人='''+RxDBLookupCombo3.DisplayValues[0]+'''');

          SQL.Add('group by t_ch_fee.客户名称 order by t_ch_fee.客户名称 ');
        end else begin
          SQL.Add('from t_ch_fee inner join t_op_seae on t_ch_fee.编号=t_op_seae.编号 ');
          SQL.Add(sqlstr);
          sql.Add(' and t_op_seae.开船日期<='''+strdate+'''');
          if RxDBLookupCombo5.DisplayValues[1]<>'' then
            SQL.Add(' and t_ch_fee.客户名称='''+RxDBLookupCombo5.DisplayValues[1]+'''');
          SQL.Add('group by t_ch_fee.客户名称 order by t_ch_fee.客户名称 ');
        end;
        Open;
        if not IsEmpty then begin
          first;
          while not eof do begin
            if memtblDetail.Locate('统计标准',FieldbyName('客户名称').AsString,[]) then begin
              memtblDetail.Edit;
              memtblDetail.FieldByName(sDate+'USD').AsFloat:=memtblDetail.FieldByName(sDate+'USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName(sDate+'RMB').AsFloat:=memtblDetail.FieldByName(sDate+'RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName(sDate+'合计').AsFloat:=memtblDetail.FieldByName(sDate+'合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtblDetail.FieldByName('合计USD').AsFloat:=memtblDetail.FieldByName('合计USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName('合计RMB').AsFloat:=memtblDetail.FieldByName('合计RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName('合计未收').AsFloat:=memtblDetail.FieldByName('合计未收').AsFloat+FieldbyName('未收合计').AsFloat;
              memtblDetail.Post;
            end else begin
              memtblDetail.Insert;
              for i:=0 to memtbldetail.FieldCount-1 do begin
                if memtbldetail.Fields[i].FieldName='统计标准' then
                   memtbldetail.Fields[i].AsString:=FieldByName('客户名称').AsString
                else
                   memtbldetail.Fields[i].AsFloat:=0;
              end;
              memtblDetail.FieldByName(sDate+'USD').AsFloat:=memtblDetail.FieldByName(sDate+'USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName(sDate+'RMB').AsFloat:=memtblDetail.FieldByName(sDate+'RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName(sDate+'合计').AsFloat:=memtblDetail.FieldByName(sDate+'合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtblDetail.FieldByName('合计USD').AsFloat:=memtblDetail.FieldByName('合计USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName('合计RMB').AsFloat:=memtblDetail.FieldByName('合计RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName('合计未收').AsFloat:=memtblDetail.FieldByName('合计未收').AsFloat+FieldbyName('未收合计').AsFloat;

              memtblDetail.Post;
            end;
            if memtotal.Locate('月份',SDate,[]) then begin
              memtotal.Edit;
              memtotal.FieldByName('合计').AsFloat:=memtotal.FieldByName('合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtotal.Post;
            end else begin
              memtotal.last;
              memtotal.Insert;
              memtotal.FieldByName('月份').AsString:=sDate;
              memtotal.FieldByName('合计').AsFloat:=memtotal.FieldByName('合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtotal.Post;
            end;

            Next;
          end;
        end;
        frm_sys_progress.bsSkinGauge1.Value:=frm_sys_progress.bsSkinGauge1.Value+1;


        CLOSE;
        SQL.Clear;
        SQL.Add('Select v_fee_do_detail.客户名称, ');
        SQL.Add('sum(case v_fee_do_detail.类型 when ''收'' then (case v_fee_do_detail.原始币别 when ''USD'' then v_fee_do_detail.原始金额 else 0 end) else 0 end) as 未收USD,');
        SQL.Add('sum(case v_fee_do_detail.类型 when ''收'' then (case v_fee_do_detail.原始币别 when ''USD'' then 0 else v_fee_do_detail.原始金额 end) else 0 end) as 未收RMB,');
        SQL.Add('sum(case v_fee_do_detail.类型 when ''收'' then v_fee_do_detail.原始金额*v_fee_do_detail.汇率 else 0 end) as 未收合计');
        if RxDBLookupCombo3.DisplayValue<>'' then begin
          SQL.Add('from v_fee_do_detail inner join t_op_seae on v_fee_do_detail.编号=t_op_seae.编号 ');
          SQL.Add(sqlstr);
          sql.Add(' and t_op_seae.开船日期<='''+strdate+''' and v_fee_do_detail.结算日期>'''+strdate+'''');
          if RxDBLookupCombo5.DisplayValues[1]<>'' then
            SQL.Add(' and v_fee_do_detail.客户名称='''+RxDBLookupCombo5.DisplayValues[1]+'''');
          if wwDBComboBox14.Text='不等于' then
            SQL.Add(' and t_op_seae.揽货人<>'''+RxDBLookupCombo3.DisplayValues[0]+'''')
          else
            SQL.Add(' and t_op_seae.揽货人='''+RxDBLookupCombo3.DisplayValues[0]+'''');

          SQL.Add('group by v_fee_do_detail.客户名称 order by v_fee_do_detail.客户名称 ');
        end else begin
          SQL.Add('from v_fee_do_detail inner join t_op_seae on v_fee_do_detail.编号=t_op_seae.编号 ');
          SQL.Add(sqlstr);
          sql.Add(' and t_op_seae.开船日期<='''+strdate+''' and v_fee_do_detail.结算日期>'''+strdate+'''');
          if RxDBLookupCombo5.DisplayValues[1]<>'' then
            SQL.Add(' and v_fee_do_detail.客户名称='''+RxDBLookupCombo5.DisplayValues[1]+'''');
          SQL.Add('group by v_fee_do_detail.客户名称 order by v_fee_do_detail.客户名称 ');
        end;
        Open;
        if not IsEmpty then begin
          first;
          while not eof do begin
            if memtblDetail.Locate('统计标准',FieldbyName('客户名称').AsString,[]) then begin
              memtblDetail.Edit;
              memtblDetail.FieldByName(sDate+'USD').AsFloat:=memtblDetail.FieldByName(sDate+'USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName(sDate+'RMB').AsFloat:=memtblDetail.FieldByName(sDate+'RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName(sDate+'合计').AsFloat:=memtblDetail.FieldByName(sDate+'合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtblDetail.FieldByName('合计USD').AsFloat:=memtblDetail.FieldByName('合计USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName('合计RMB').AsFloat:=memtblDetail.FieldByName('合计RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName('合计未收').AsFloat:=memtblDetail.FieldByName('合计未收').AsFloat+FieldbyName('未收合计').AsFloat;

              memtblDetail.Post;
            end else begin
              memtblDetail.Insert;
              for i:=0 to memtbldetail.FieldCount-1 do begin
                if memtbldetail.Fields[i].FieldName='统计标准' then
                   memtbldetail.Fields[i].AsString:=FieldByName('客户名称').AsString
                else
                   memtbldetail.Fields[i].AsFloat:=0;
              end;
              memtblDetail.FieldByName(sDate+'USD').AsFloat:=memtblDetail.FieldByName(sDate+'USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName(sDate+'RMB').AsFloat:=memtblDetail.FieldByName(sDate+'RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName(sDate+'合计').AsFloat:=memtblDetail.FieldByName(sDate+'合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtblDetail.FieldByName('合计USD').AsFloat:=memtblDetail.FieldByName('合计USD').AsFloat+FieldbyName('未收USD').AsFloat;
              memtblDetail.FieldByName('合计RMB').AsFloat:=memtblDetail.FieldByName('合计RMB').AsFloat+FieldbyName('未收RMB').AsFloat;
              memtblDetail.FieldByName('合计未收').AsFloat:=memtblDetail.FieldByName('合计未收').AsFloat+FieldbyName('未收合计').AsFloat;

              memtblDetail.Post;
            end;
            if memtotal.Locate('月份',SDate,[]) then begin
              memtotal.Edit;
              memtotal.FieldByName('合计').AsFloat:=memtotal.FieldByName('合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtotal.Post;
            end else begin
              memtotal.last;
              memtotal.Insert;
              memtotal.FieldByName('月份').AsString:=sDate;
              memtotal.FieldByName('合计').AsFloat:=memtotal.FieldByName('合计').AsFloat+FieldbyName('未收合计').AsFloat;
              memtotal.Post;
            end;
            Next;
          end;
        end;
        frm_sys_progress.bsSkinGauge1.Value:=frm_sys_progress.bsSkinGauge1.Value+1;



        aDate:=IncMonth(aDate,1);
      end;
    end;
  finally
    FreeAndNil(aQuery);
    freeAndNil(frm_sys_progress);
  end;

end;

procedure Tfrm_rp_no_histry.Creatememtbl;
var
  aDate,aToDate:TDate;
  sDate,aYear,aMonth:String;
  aColumn:TdxDBTreeListColumn;
  aBand:TdxTreeListBand;
  i:integer;
begin

  dxDBGrid1.Bands.Clear;
//  dxDBGrid1.DestroyColumns;
  while dxDBGrid1.columncount>0 do
  begin
    dxDBGrid1.Columns[0].Destroy;
  end;

  memtblDetail.Close;
  memtblDetail.EmptyTable;
  memtblDetail.Fields.Clear;
  memtblDetail.FieldDefs.Clear;
  memtblDetail.FieldDefs.Add('统计标准', ftString, 20, false);

  aBand:=dxDBGrid1.Bands.Add;
  aBand.Caption:='基本信息';
  aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
  aColumn.Caption:=wwDBComboBox2.Text;
  aColumn.BandIndex:=0;
  aColumn.FieldName:='统计标准';
  i:=1;

  aDate:=EncodeDate(strtoint(cbYear.Text),strtoint(cbbmonth.Text),1);
  aToDate:=EncodeDate(strtoint(cbYear.Text),strtoint(cbemonth.Text),2);
  while not (aDate>aToDate) do begin
    sDate:=FormatDateTime('YYYYMM',aDate);
    aYear:=FormatDateTime('YYYY',aDate);
    aMonth:=FormatDateTime('MM',aDate);
    aBand:=dxDBGrid1.Bands.Add;
    aBand.Caption:=aYear+'年'+aMonth+'月';
    aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
    aColumn.Caption:='未收USD';
    aColumn.FieldName:=sDate+'USD';
    aColumn.SummaryFooterType:=cstSum;
    aColumn.BandIndex:=i;
    aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
    aColumn.Caption:='未收RMB';
    aColumn.FieldName:=sDate+'RMB';
    aColumn.SummaryFooterType:=cstSum;
    aColumn.BandIndex:=i;
    aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
    aColumn.Caption:='未收合计';
    aColumn.FieldName:=sDate+'合计';
    aColumn.SummaryFooterType:=cstSum;
    aColumn.BandIndex:=i;
    memtblDetail.FieldDefs.Add(sDate+'USD',ftFloat, 0, false);
    memtblDetail.FieldDefs.Add(sDate+'RMB',ftFloat, 0, false);
    memtblDetail.FieldDefs.Add(sDate+'合计',ftFloat, 0, false);
    aDate:=IncMonth(aDate,1);
    Inc(i);
  end;

  aBand:=dxDBGrid1.Bands.Add;
  aBand.Caption:='合计';

  aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
  aColumn.Caption:='未收USD';
  aColumn.FieldName:='合计USD';
  aColumn.SummaryFooterType:=cstSum;
  aColumn.BandIndex:=i;
  aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
  aColumn.Caption:='未收RMB';
  aColumn.FieldName:='合计RMB';
  aColumn.SummaryFooterType:=cstSum;
  aColumn.BandIndex:=i;
  aColumn:=dxDBGrid1.CreateColumn(TdxDBGridColumn);
  aColumn.Caption:='未收合计';
  aColumn.FieldName:='合计未收';
  aColumn.SummaryFooterType:=cstSum;
  aColumn.BandIndex:=i;

  memtblDetail.FieldDefs.Add('合计USD',ftFloat, 0, false);
  memtblDetail.FieldDefs.Add('合计RMB',ftFloat, 0, false);
  memtblDetail.FieldDefs.Add('合计未收',ftFloat, 0, false);
  memtblDetail.CreateTable;
  memtblDetail.Open;
  memtotal.close;
  memtotal.EmptyTable;
  memtotal.Open;
  memtotal.SortOn('月份',[]);

end;

procedure Tfrm_rp_no_histry.bsSkinCheckRadioBox3Click(Sender: TObject);
begin
  GetNoFee;
end;

procedure Tfrm_rp_no_histry.bsSkinCheckRadioBox2Click(Sender: TObject);
begin
  GetNoFee;
end;


procedure Tfrm_rp_no_histry.FormShow(Sender: TObject);
begin
  cbyear.Items.IndexOf(FormatDateTime('YYYY',Now));
  cbyear.text:=FormatDateTime('YYYY',Now);
  cbbmonth.Items.IndexOf('01');
  cbbmonth.text:='01';
  cbemonth.Items.IndexOf(FormatDateTime('MM',Now));
  cbemonth.text:=FormatDateTime('MM',Now);
  cbdate.Items.IndexOf('31');
  cbdate.text:='31';
end;

procedure Tfrm_rp_no_histry.N1101Click(Sender: TObject);
begin
  chartpreview(frm_rp_no_histry,dbchart3);
end;

end.