unit U_BCHZ_Year_JSYSF;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, cxGridCustomTableView,
  cxGridTableView, cxGridBandedTableView, cxGridDBBandedTableView,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridDBTableView,
  cxGrid, StdCtrls, ComCtrls, ExtCtrls, ToolWin, cxGridCustomPopupMenu,
  cxGridPopupMenu, ADODB, DBClient, cxDropDownEdit, cxCheckBox, RM_Common,
  RM_Class, RM_e_Xls, RM_Dataset, RM_System, RM_GridReport, Menus,
  cxCalendar, cxButtonEdit, cxTextEdit, cxPC;

type
  TfrmBCHZ_Year_JSYSF = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label1: TLabel;
    BegDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    RM1: TRMGridReport;
    RMDBMain: TRMDBDataSet;
    RMXLSExport1: TRMXLSExport;
    RMDBHZ: TRMDBDataSet;
    CDS_HZ: TClientDataSet;
    CDS_PRT: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    Label12: TLabel;
    Label3: TLabel;
    SJName: TEdit;
    Label5: TLabel;
    CheHao: TEdit;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1JSYName: TcxGridDBColumn;
    v1Month3: TcxGridDBColumn;
    v1CLName: TcxGridDBColumn;
    v1ZMoney: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    CLName: TEdit;
    v1GZPJMoney: TcxGridDBColumn;
    v1Month4: TcxGridDBColumn;
    v1Month5: TcxGridDBColumn;
    v1Month6: TcxGridDBColumn;
    v1Month7: TcxGridDBColumn;
    v1Month8: TcxGridDBColumn;
    v1Month9: TcxGridDBColumn;
    v1Month10: TcxGridDBColumn;
    v1Month11: TcxGridDBColumn;
    v1Month12: TcxGridDBColumn;
    Panel2: TPanel;
    v1PJCFMoney: TcxGridDBColumn;
    v1PJYHMoney: TcxGridDBColumn;
    v1Month1: TcxGridDBColumn;
    v1Month2: TcxGridDBColumn;
    CheckBox1: TCheckBox;
    YFCK: TToolButton;
    cxTabControl1: TcxTabControl;
    v1CheHao: TcxGridDBColumn;
    v1CLSpec: TcxGridDBColumn;
    Label2: TLabel;
    CLSpec: TEdit;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ConNoMChange(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SPIDChange(Sender: TObject);
    procedure SPSpecChange(Sender: TObject);
    procedure GCNameChange(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure YFCKClick(Sender: TObject);
    procedure Tv1CellDblClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
  private
    canshu1,canshu2,canshu3:string;
    procedure InitGrid();
    procedure setstatus();
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmBCHZ_Year_JSYSF: TfrmBCHZ_Year_JSYSF;

implementation
uses
  U_DataLink,U_RTFun,U_SXCKInPut, U_JGSXCKInPut, U_CLShuiPiao_Sub,U_CLYouliang_Sub,U_CLYouKa_GZMX;

{$R *.dfm}
procedure TfrmBCHZ_Year_JSYSF.setstatus();
begin
  v1PJCFMoney.Visible:=false;
  if cxTabControl1.TabIndex=1 then
  begin
    v1PJCFMoney.Visible:=true;
  end;
  if CheckBox1.Checked=True then
  begin
    v1Month1.Visible:=false;
    v1Month2.Visible:=false;
  end;
end;

procedure TfrmBCHZ_Year_JSYSF.FormDestroy(Sender: TObject);
begin
  frmBCHZ_Year_JSYSF:=nil;
end;

procedure TfrmBCHZ_Year_JSYSF.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TfrmBCHZ_Year_JSYSF.FormCreate(Sender: TObject);
begin
  //cxGrid1.Align:=alClient;
  BegDate.DateTime:=SGetServerDate(ADOQueryCmd);
  canshu1:=Trim(DParameters1);
  canshu2:=Trim(DParameters2);
  canshu3:=Trim(DParameters3);
end;

procedure TfrmBCHZ_Year_JSYSF.InitGrid();
begin
  Panel2.Visible:=True;
  Panel2.Refresh;
  with ADOQueryMain do
  begin
    Filtered:=False;
    Close;
    sql.Clear;
    sql.Add(' select AA.*,ZMoney=isnull(Month1,0)+isnull(Month2,0)+isnull(Month3,0)+isnull(Month4,0)+isnull(Month5,0)+isnull(Month6,0)+isnull(Month7,0)+isnull(Month8,0)+isnull(Month9,0)');
    sql.Add('+isnull(Month10,0)+isnull(Month11,0)+isnull(Month12,0)');
    sql.add(',GZPJMoney=cast((case when isnull(GSMonth,0)=0 then 0 else (isnull(Month1,0)+isnull(Month2,0)+isnull(Month3,0)+isnull(Month4,0)+isnull(Month5,0)+isnull(Month6,0)+isnull(Month7,0)+isnull(Month8,0)+isnull(Month9,0)');
    sql.Add('+isnull(Month10,0)+isnull(Month11,0)+isnull(Month12,0))/GSMonth end) as decimal(18,2))');
    sql.Add(',PJYHMoney=cast((case when isnull(GSMonth,0)<0 then 0 else YHMoney/GSMonth end) as decimal(18,2)),PJCFMoney=cast((case when isnull(GSMonth,0)=0 then 0 else CFMoney/GSMonth end) as decimal(18,2))');
    sql.Add(' from (select substring(A.GZMonth,1,4) Date,A.JSYName');
    sql.add(',CLName=(select Top 1 B.CLName from CK_CP_CR B where B.SJName=A.JSYName Order by B.CRTime desc)');
    sql.add(',CheHao=(select Top 1 B.CheHao from CK_CP_CR B where B.SJName=A.JSYName Order by B.CRTime desc)');
    sql.add(',CLSpec=(select Top 1 B.CLSpec from CK_CP_CR B where B.SJName=A.JSYName Order by B.CRTime desc)');
    if CheckBox1.Checked=True then
    begin
      sql.Add(',GSMonth=(select Top 1 substring(Max(B.GZMonth),6,7)-2 from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName group by B.GZMonth order by B.GZMonth desc)');
      sql.Add(',Month1=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''01'')');
      sql.Add(',Month2=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''02'')');
    end
    else
    begin
      sql.Add(',GSMonth=(select Top 1 substring(Max(B.GZMonth),6,7) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName group by B.GZMonth order by B.GZMonth desc)');
      sql.Add(',Month1=cast(0 as decimal(18,2)),Month2=cast(0 as decimal(18,2))');
    end;
    sql.Add(',Month3=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''03'')');
    sql.Add(',Month4=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''04'')');
    sql.Add(',Month5=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''05'')');
    sql.Add(',Month6=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''06'')');
    sql.Add(',Month7=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''07'')');
    sql.Add(',Month8=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''08'')');
    sql.Add(',Month9=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''09'')');
    sql.Add(',Month10=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''10'')');
    sql.Add(',Month11=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''11'')');
    sql.Add(',Month12=(select Sum(B.ZMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName and substring(B.GZMonth,6,7)=''12'')');
    sql.Add(',YHMoney=(select Sum(B.YHMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName)');
    sql.Add(',CFMoney=(select Sum(B.CFMoney) from CLJSY_Main B where substring(B.GZMonth,1,4)=substring(A.GZMonth,1,4) and B.JSYName=A.JSYName)');
    sql.Add(' from CLJSY_Main A ');
    sql.Add('where substring(A.GZMonth,1,4)='''+trim(FormatDateTime('yyyy',begDate.Date))+'''');
    sql.Add(' group by substring(A.GZMonth,1,4),A.JSYName) AA');
    if cxTabControl1.TabIndex=0 then
    begin
      sql.Add(' where isnull(AA.CLName,'''')=''搅拌车''');
    end;
    if cxTabControl1.TabIndex=1 then
    begin
      sql.Add(' where isnull(AA.CLName,'''')<>''搅拌车''');
    end;
    Open;
  end;
  SCreateCDS20(ADOQueryMain,CDS_Main);
  SInitCDSData20(ADOQueryMain,CDS_Main);
  Panel2.Visible:=false;
end;

procedure TfrmBCHZ_Year_JSYSF.TBRafreshClick(Sender: TObject);
begin
  BegDate.SetFocus;
  InitGrid();
end;

procedure TfrmBCHZ_Year_JSYSF.ConNoMChange(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
  end;
end;

procedure TfrmBCHZ_Year_JSYSF.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid('数据列表FM',Tv1,'数据');
  Close;
end;

procedure TfrmBCHZ_Year_JSYSF.FormShow(Sender: TObject);
begin
  ReadCxGrid('数据列表FM',Tv1,'数据');
  setstatus();
  //InitGrid();
end;

procedure TfrmBCHZ_Year_JSYSF.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then exit;
  TcxGridToExcel('驾驶员实发工资年份汇总',cxGrid2);
end;

procedure TfrmBCHZ_Year_JSYSF.TBFindClick(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

procedure TfrmBCHZ_Year_JSYSF.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,True);
end;

procedure TfrmBCHZ_Year_JSYSF.N2Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,False);
end;

procedure TfrmBCHZ_Year_JSYSF.SPIDChange(Sender: TObject);
begin
  
  TBFind.Click;
end;

procedure TfrmBCHZ_Year_JSYSF.SPSpecChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmBCHZ_Year_JSYSF.GCNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmBCHZ_Year_JSYSF.cxTabControl1Change(Sender: TObject);
begin
  setstatus();
  InitGrid();
end;

procedure TfrmBCHZ_Year_JSYSF.CheckBox1Click(Sender: TObject);
begin
  if CheckBox1.Checked=True then
  begin
    v1Month1.Visible:=false;
    v1Month2.Visible:=false;
  end
  else
  begin
    v1Month1.Visible:=true;
    v1Month2.Visible:=true;
  end;
  TBRafresh.Click;
end;

procedure TfrmBCHZ_Year_JSYSF.YFCKClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then exit;
  if (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month1')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month2')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month3')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month4')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month5')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month6')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month7')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month8')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month9')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month10')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month11')
     or (Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month12')then
  begin
    frmCLYouKa_GZMX:=TfrmCLYouKa_GZMX.create(self);
    with frmCLYouKa_GZMX do
    begin
      cxTabControl1.TabIndex:=Self.cxTabControl1.TabIndex;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month1' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-01-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month2' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-02-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month3' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-03-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month4' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-04-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month5' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-05-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month6' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-06-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month7' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-07-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month8' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-08-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month9' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-09-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month10' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-10-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month11' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-11-01');
      end;
      if Self.Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName='Month12' then
      begin
        Begdate.Date:=StrToDate(Formatdatetime('yyyy',Self.BegDate.Date)+'-12-01');
      end;
      //SJName.text:=Trim(Self.CDS_Main.fieldbyname('JSYName').AsString);
      //CheHao.Text:=Trim(Self.CDS_Main.fieldbyname('CheHao').AsString);
      //CLName.Text:=Trim(self.CDS_Main.fieldbyname('CLName').AsString);
      if showmodal=1 then
      begin

      end;
      free;
    end;
  end;
end;

procedure TfrmBCHZ_Year_JSYSF.Tv1CellDblClick(
  Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin

    YFCK.Click;
end;

end.