unit u_rp_zj_zou;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, Series, TeEngine, ExtCtrls, TeeProcs, Chart, DbChart, DB,
  ADODB, bsSkinCtrls, StdCtrls, Mask, wwdbedit, Wwdotdot, Wwdbcomb,
  RxLookup,teeprevi, XPMenu;

type
  Tfrm_rp_zj_zou = class(TForm)
    bsSkinPanel1: TbsSkinPanel;
    Label16: TLabel;
    Label14: TLabel;
    Label23: TLabel;
    Label22: TLabel;
    Label10: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    Label9: TLabel;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    RxDBLookupCombo6: TRxDBLookupCombo;
    RxDBLookupCombo3: TRxDBLookupCombo;
    RxDBLookupCombo12: TRxDBLookupCombo;
    RxDBLookupCombo10: TRxDBLookupCombo;
    wwDBComboBox1: TwwDBComboBox;
    RxDBLookupCombo2: TRxDBLookupCombo;
    bsSkinButton1: TbsSkinButton;
    bsSkinCheckRadioBox2: TbsSkinCheckRadioBox;
    bsSkinCheckRadioBox1: TbsSkinCheckRadioBox;
    wwDBComboBox2: TwwDBComboBox;
    wwDBComboBox3: TwwDBComboBox;
    wwDBComboBox5: TwwDBComboBox;
    wwDBComboBox4: TwwDBComboBox;
    bsSkinPanel2: TbsSkinPanel;
    bsSkinCheckRadioBox3: TbsSkinCheckRadioBox;
    bsSkinCheckRadioBox4: TbsSkinCheckRadioBox;
    p_no_accept: TADOStoredProc;
    bsSkinPanel3: TbsSkinPanel;
    bsSkinPanel4: TbsSkinPanel;
    DBChart2: TDBChart;
    LineSeries1: TBarSeries;
    DBChart1: TDBChart;
    Series1: TLineSeries;
    bsSkinPanel5: TbsSkinPanel;
    DBChart3: TDBChart;
    LineSeries2: TLineSeries;
    DBChart4: TDBChart;
    BarSeries1: TBarSeries;
    p_no_pay: TADOStoredProc;
    PopupMenu1: TPopupMenu;
    N5: TMenuItem;
    N1: TMenuItem;
    N2: TMenuItem;
    XPMenu1: TXPMenu;
    bsSkinCheckRadioBox5: TbsSkinCheckRadioBox;
    bsSkinPanel6: TbsSkinPanel;
    DBChart5: TDBChart;
    BarSeries2: TBarSeries;
    DBChart6: TDBChart;
    LineSeries3: TLineSeries;
    p_accept: TADOStoredProc;
    procedure bsSkinButton5Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure bsSkinCheckRadioBox4Click(Sender: TObject);
    procedure bsSkinCheckRadioBox3Click(Sender: TObject);
    procedure N5Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure bsSkinPanel3Resize(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_rp_zj_zou: Tfrm_rp_zj_zou;

implementation

uses u_main, my_sys_function;

{$R *.dfm}

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

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

procedure Tfrm_rp_zj_zou.bsSkinButton10Click(Sender: TObject);
begin
  wwDBComboBox2.text:='';
  wwDBComboBox3.text:='';
  wwDBComboBox4.text:='';
  wwDBComboBox5.text:='';
  wwDBComboBox1.text:='';
  RxDBLookupCombo2.ClearValue;
  RxDBLookupCombo6.ClearValue;
  RxDBLookupCombo12.ClearValue;
  RxDBLookupCombo3.ClearValue;
  RxDBLookupCombo10.ClearValue;
end;

procedure Tfrm_rp_zj_zou.bsSkinButton9Click(Sender: TObject);
var
  Pnt:Tpoint;
begin
  Pnt:=bsSkinButton9.ClientToScreen(Point(0,bsSkinButton9.Height));
  PopupMenu1.Popup(Pnt.x,Pnt.y);
end;

procedure Tfrm_rp_zj_zou.bsSkinButton1Click(Sender: TObject);
var
  str_op,str:widestring;
  str_start,str_end:string;
  str_date:string;
begin
  if (wwDBComboBox2.Text='')or(wwDBComboBox3.Text='')or
     (wwDBComboBox4.Text='')or(wwDBComboBox5.Text='') then
  begin
    showmessage('请将开始年月和结束年月输入完整！！');
    exit;
  end;
  try
    if strtoint(wwDBComboBox2.text)>strtoint(wwDBComboBox5.text) then
    begin
      showmessage('开始年月不能大于结束年月！！');
      exit;
    end;
    if strtoint(wwDBComboBox2.text)=strtoint(wwDBComboBox5.text) then
    begin
      if strtoint(wwDBComboBox3.text)>strtoint(wwDBComboBox4.text) then
      begin
        showmessage('开始年月不能大于结束年月！！');
        exit;
      end;
    end;
  except
    showmessage('请将开始年月和结束年月输入完整！！');
    exit;
  end;  


  if bsSkinCheckRadioBox2.Checked then
    str_date:='业务日期'
  else  if bsSkinCheckRadioBox5.Checked then
    str_date:='预抵日期'
  else
    str_date:='会计期间';
  str_start:=wwDBComboBox2.text+'-'+wwDBComboBox3.text+'-'+'01';
  str_end:=wwDBComboBox5.text+'-'+wwDBComboBox4.text+'-'+'01';

  str_op:=open_data('0036','v_op_no.揽货人','v_op_no.操作员','v_op_no.客服员','no','no');
  if wwDBComboBox1.ItemIndex<>-1 then
    str_op:=str_op+' and v_op_no.业务种类='+''''+wwDBComboBox1.text+'''';
  if RxDBLookupCombo6.DisplayValue<>''then
    str_op:=str_op+' and v_op_no.操作员='+''''+RxDBLookupCombo6.DisplayValue+'''';
  if RxDBLookupCombo12.DisplayValue<>''then
    str_op:=str_op+' and v_op_no.操作部门='+''''+RxDBLookupCombo12.DisplayValue+'''';
  if RxDBLookupCombo3.DisplayValue<>''then
    str_op:=str_op+' and v_op_no.揽货人='+''''+RxDBLookupCombo3.DisplayValue+'''';
  if RxDBLookupCombo10.DisplayValue<>''then
    str_op:=str_op+' and v_op_no.销售部门='+''''+RxDBLookupCombo10.DisplayValue+'''';
  if RxDBLookupCombo2.DisplayValues[1]<>''then
    str_op:=str_op+' and v_op_no.委托单位='+''''+RxDBLookupCombo2.DisplayValues[1]+'''';

  str:='CREATE  TABLE  #t_sum_no    '
       +'(	                    '
       +' 年月		varchar(6), '
       +' 欠款金额  	float       '
       +') '
       +' DECLARE @cha int,@begin_date smalldatetime,@NXYF CHAR(6),@no_money float '
       +' set @begin_date='+''''+str_start+''''
       +' set @cha=datediff(month,@begin_date,'+''''+str_end+''''+')'
       +' set @begin_date=DATEADD(month,-1,@begin_date)'
       +' while @cha>=0 '
       +' begin  '
       +'   SET @begin_date= DATEADD(month, 1, @begin_date) '
       +'   SET @NXYF=CAST(DATEPART(Year,@begin_date) AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,@begin_date)+100 AS CHAR(3)),2) '
       +'   set @no_money=isnull((SELECT SUM(t_ch_fee.金额*t_ch_fee.汇率) FROM t_ch_fee INNER JOIN '
       +'   v_op_no ON t_ch_fee.编号=v_op_no.编号 where t_ch_fee.类型='+''''+'收'+''''+' and '+str_op+' and '
       +'   (CAST(DATEPART(Year,v_op_no.'+str_date+') AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,v_op_no.'+str_date+')+100 AS CHAR(3)),2))=@NXYF),0) '
       +' INSERT #t_sum_no (年月,欠款金额) VALUES (@NXYF,@no_money) '
       +' set @cha=@cha-1  '
       +' end '
       +' select * from #t_sum_no '
       +' order by 年月 ';
  p_accept.close;
  p_accept.Parameters.ParamByName('SQLStr').value:=str;
  p_accept.Open;

    
  str:='CREATE  TABLE  #t_sum_no    '
       +'(	                    '
       +' 年月		varchar(6), '
       +' 欠款金额  	float       '
       +') '
       +' DECLARE @cha int,@begin_date smalldatetime,@NXYF CHAR(6),@no_money float '
       +' set @begin_date='+''''+str_start+''''
       +' set @cha=datediff(month,@begin_date,'+''''+str_end+''''+')'
       +' set @begin_date=DATEADD(month,-1,@begin_date)'
       +' while @cha>=0 '
       +' begin  '
       +'   SET @begin_date= DATEADD(month, 1, @begin_date) '
       +'   SET @NXYF=CAST(DATEPART(Year,@begin_date) AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,@begin_date)+100 AS CHAR(3)),2) '
       +'   set @no_money=isnull((SELECT SUM(t_ch_fee.金额*t_ch_fee.汇率) FROM t_ch_fee INNER JOIN '
       +'   v_op_no ON t_ch_fee.编号=v_op_no.编号 where t_ch_fee.类型='+''''+'付'+''''+' and '+str_op+' and '
       +'   (CAST(DATEPART(Year,v_op_no.'+str_date+') AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,v_op_no.'+str_date+')+100 AS CHAR(3)),2))=@NXYF),0) '
       +' INSERT #t_sum_no (年月,欠款金额) VALUES (@NXYF,@no_money) '
       +' set @cha=@cha-1  '
       +' end '
       +' select * from #t_sum_no '
       +' order by 年月 ';
  p_no_accept.close;
  p_no_accept.Parameters.ParamByName('SQLStr').value:=str;
  p_no_accept.Open;

  str:='CREATE  TABLE  #t_sum_no    '
       +'(	                    '
       +' 年月		varchar(6), '
       +' 欠款金额  	float       '
       +') '
       +' DECLARE @cha int,@begin_date smalldatetime,@NXYF CHAR(6),@no_money float '
       +' set @begin_date='+''''+str_start+''''
       +' set @cha=datediff(month,@begin_date,'+''''+str_end+''''+')'
       +' set @begin_date=DATEADD(month,-1,@begin_date)'
       +' while @cha>=0 '
       +' begin  '
       +'   SET @begin_date= DATEADD(month, 1, @begin_date) '
       +'   SET @NXYF=CAST(DATEPART(Year,@begin_date) AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,@begin_date)+100 AS CHAR(3)),2) '
       +'set @no_money=isnull((SELECT SUM(t_ch_fee_do.原始金额*t_ch_fee.汇率) FROM dbo.t_ch_fee_do INNER JOIN '
       +'t_ch_balance ON t_ch_fee_do.工作编号 =t_ch_balance.帐单编号 INNER JOIN '
       +'t_ch_fee ON t_ch_fee_do.CH_ID = t_ch_fee.CH_ID where t_ch_fee.类型='+''''+'付'+''''+' and t_ch_fee_do.类型='
       +''''+'4'+''''+' and t_ch_fee.编号 in (select 编号 from v_op_no where '+str_op+')'+' and '
       +'(CAST(DATEPART(Year,t_ch_balance.结算日期)AS CHAR(4))+RIGHT(CAST(DATEPART(MONTH,t_ch_balance.结算日期)+100 AS CHAR(3)),2))=@NXYF),0) '
       +' INSERT #t_sum_no (年月,欠款金额) VALUES (@NXYF,@no_money) '
       +' set @cha=@cha-1  '
       +' end '
       +' select * from #t_sum_no '
       +' order by 年月 ';
  p_no_pay.close;
  p_no_pay.Parameters.ParamByName('SQLStr').value:=str;
  p_no_pay.Open;
end;

procedure Tfrm_rp_zj_zou.bsSkinCheckRadioBox4Click(Sender: TObject);
begin
  DBChart1.Visible:=true;
  DBChart2.Visible:=false;
  DBChart3.Visible:=true;
  DBChart4.Visible:=false;
end;

procedure Tfrm_rp_zj_zou.bsSkinCheckRadioBox3Click(Sender: TObject);
begin
  DBChart2.Visible:=true;
  DBChart1.Visible:=false;
  DBChart4.Visible:=true;
  DBChart3.Visible:=false;
end;

procedure Tfrm_rp_zj_zou.N5Click(Sender: TObject);
begin
  if  DBChart1.Visible=true then
    chartpreview(frm_rp_zj_zou,dbchart1)
  else
    chartpreview(frm_rp_zj_zou,dbchart2);
end;

procedure Tfrm_rp_zj_zou.N2Click(Sender: TObject);
begin
  if  DBChart3.Visible=true then
    chartpreview(frm_rp_zj_zou,dbchart3)
  else
    chartpreview(frm_rp_zj_zou,dbchart4);
end;

procedure Tfrm_rp_zj_zou.bsSkinPanel3Resize(Sender: TObject);
begin
  bsSkinPanel4.Height:=round(bsSkinPanel3.Height/3);
  bsSkinPanel6.Height:=round(bsSkinPanel3.Height/3);
end;

end.