frm_clientelecask.pas

来自「一个电力企业的后台管理程序」· PAS 代码 · 共 380 行

unit Frm_ClientElecAsk;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DsFancyButton, StdCtrls, Spin, ComCtrls, ExtCtrls, pubReport, dbtables;

type
  TForm_ClientElecAsk = class(TForm)
    PC_Content: TPageControl;
    TabSheet1: TTabSheet;
    Panel_Condition: TPanel;
    Lbl_Condition: TLabel;
    Panel_Type: TPanel;
    Lbl_Type: TLabel;
    RB_ByType: TRadioButton;
    RB_ByUnit: TRadioButton;
    Panel_Date: TPanel;
    Lbl_AskDate: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    Bevel1: TBevel;
    DTP_CountDate: TDateTimePicker;
    RB_ByCountDay: TRadioButton;
    RB_ByCountMonth: TRadioButton;
    SE_CountYear: TSpinEdit;
    SE_CountMonth: TSpinEdit;
    Panel_Format: TPanel;
    Label3: TLabel;
    Bevel2: TBevel;
    BTN_: TDsFancyButton;
    Bevel6: TBevel;
    RB_ByCountRep: TRadioButton;
    RB_CountWord: TRadioButton;
    TabSheet2: TTabSheet;
    Panel2: TPanel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    RB_ByTypeDay: TRadioButton;
    RB_ByTypeMonth: TRadioButton;
    DTP_TypeDate: TDateTimePicker;
    SE_TypeYear: TSpinEdit;
    SE_TypeMonth: TSpinEdit;
    Panel6: TPanel;
    Label11: TLabel;
    Panel3: TPanel;
    Label7: TLabel;
    Bevel3: TBevel;
    DsFancyButton5: TDsFancyButton;
    Bevel7: TBevel;
    RB_TypeNormal: TRadioButton;
    RB_TypeWord: TRadioButton;
    TabSheet3: TTabSheet;
    Panel4: TPanel;
    Label9: TLabel;
    DsFancyButton6: TDsFancyButton;
    Bevel4: TBevel;
    Bevel5: TBevel;
    Label8: TLabel;
    SE_FxYear: TSpinEdit;
    RB_FxWord: TRadioButton;
    RB_FxNormal: TRadioButton;
    Panel5: TPanel;
    Label10: TLabel;
    Panel1: TPanel;
    BTN_OK: TDsFancyButton;
    BTN_Cancel: TDsFancyButton;
    procedure FormCreate(Sender: TObject);
    procedure RB_ByCountDayClick(Sender: TObject);
    procedure BTN_CancelClick(Sender: TObject);
    procedure RB_ByCountMonthClick(Sender: TObject);
    procedure RB_ByTypeMonthClick(Sender: TObject);
    procedure RB_ByTypeDayClick(Sender: TObject);
    procedure BTN_OKClick(Sender: TObject);
    procedure DsFancyButton5Click(Sender: TObject);
    procedure BTN_Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
   function CreateAskSql: String;
   function CreateAskTypeSql: String;
   function CreateAskFxSql(Str: String): String;
   procedure FillCount(Str:String);
  end;

var
  Form_ClientElecAsk: TForm_ClientElecAsk;

implementation

uses systemDM,CommonFunc;

{$R *.DFM}

{ TForm_ClientElecAsk }

function TForm_ClientElecAsk.CreateAskFxSql(Str: String): String;
var
 Query,LastQuery: TQuery;
begin
 IniTable(Str) ;
 FillCount(Str);
 Query := TQuery.Create(nil);
 LastQuery := TQuery.Create (nil);
 try
  Query.DataBaseName := SysDm.DBMain.DatabaseName;
  Query.Close;
  Query.SQL.Text := ('Select * From Elecoutcount');
  Query.Open;
  LastQuery.DataBaseName := SysDm.DBMain.DatabaseName;
  LastQuery.Close;
  LastQuery.SQL.Text := ('Select * From temlastyear');
  LastQuery.Open;
  FillAllCount(Query,LastQuery);
 finally
   LastQuery.Free;
   Query.Free;
 end;
 Result := 'SELECT * FROM ELECOUTCOUNT';
end;

function TForm_ClientElecAsk.CreateAskSql: String;
var
 DateStr: String;
begin
 if RB_ByCountDay.Checked then
  begin
   DateStr := FormatDateTime('YYYY-MM-DD',DTP_CountDate.Date);
   if RB_ByType.Checked then
    Result := Format('SELECT D.CLASSName TYPENAME,C.ASKCOUNT ASKCOUNT FROM '+
                     '(SELECT B.URGE_WORKID TYPE_NO,COUNT(*) ASKCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                     'WHERE A.TEMPDATE = ''%S'')B '+
                     ' GROUP BY B.URGE_WORKID)C RIGHT JOIN ClientElecApply_Class D '+
                     'ON C.TYPE_NO = D.CLASSName ',[DateStr]);
    if RB_ByUnit.Checked then
     Result := Format('SELECT D.DEALDEPT_NAME UNITNAME,C.ASKCOUNT ASKCOUNT FROM '+
                      '(SELECT B.DEALDEPT_NO UNIT_NO,COUNT(*) ASKCOUNT FROM '+
                      '(SELECT A.* FROM '+
                      '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                      'WHERE A.TEMPDATE = ''%S'')B '+
                      ' GROUP BY B.DEALDEPT_NO)C RIGHT JOIN ASKELEC_DEALDEPT D '+
                      'ON C.UNIT_NO = D.DEALDEPT_NO ',[DateStr]);
  end;
 if RB_ByCountMonth.Checked then
  begin
   if SE_CountMonth.Value < 10 then
    DateStr := IntToStr(SE_CountYear.Value) + '-0' + IntToStr(SE_CountMonth.Value) + '%'
   else
    DateStr := IntToStr(SE_CountYear.Value) + '-' + IntToStr(SE_CountMonth.Value) + '%';
   Result := Format('SELECT D.CLASSName TYPENAME,C.ASKCOUNT ASKCOUNT FROM '+
                     '(SELECT B.URGE_WORKID TYPE_NO,COUNT(*) ASKCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                     'WHERE A.TEMPDATE LIKE ''%S'')B '+
                     ' GROUP BY B.URGE_WORKID)C RIGHT JOIN ClientElecApply_Class D '+
                     'ON C.TYPE_NO = D.CLASSName ',[DateStr]);
    if RB_ByUnit.Checked then
     Result := Format('SELECT D.DEALDEPT_NAME UNITNAME,C.ASKCOUNT ASKCOUNT FROM '+
                      '(SELECT B.DEALDEPT_NO UNIT_NO,COUNT(*) ASKCOUNT FROM '+
                      '(SELECT A.* FROM '+
                      '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                      'WHERE A.TEMPDATE LIKE ''%S'')B '+
                      ' GROUP BY B.DEALDEPT_NO)C RIGHT JOIN ASKELEC_DEALDEPT D '+
                      'ON C.UNIT_NO = D.DEALDEPT_NO ',[DateStr]);
  end;
end;

function TForm_ClientElecAsk.CreateAskTypeSql: String;
var
 DateStr: String;
begin
 if RB_ByTypeDay.Checked then
  begin
   DateStr := '%'+FormatDateTime('YYYY-MM-DD',DTP_TypeDate.Date)+'%';
   Result := Format('SELECT W.DEALDEPT_NAME TYPENAME,W.NOTCOUNT NOTCOUNT,X.INTIMECOUNT INTIMECOUNT,Y.OUTTIMECOUNT OUTTIMECOUNT,Z.ALLCOUNT ALLCOUNT FROM '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT NOTCOUNT FROM ' +
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND STATE = 0) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)W, '+
                    '(SELECT D.DEALDEPT_NAME,C.INTIMECOUNT INTIMECOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) INTIMECOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FINISH_DATETIME<LIMIT_DATETIME) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO )X, '+
                    '(SELECT D.DEALDEPT_NAME,C.OUTTIMECOUNT OUTTIMECOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) OUTTIMECOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FINISH_DATETIME>LIMIT_DATETIME) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)Y , '+
                    '(SELECT D.DEALDEPT_NAME,C.ALLCOUNT ALLCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) ALLCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'') B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)Z '+
                    'WHERE W.DEALDEPT_NAME = X.DEALDEPT_NAME AND W.DEALDEPT_NAME = Y.DEALDEPT_NAME AND W.DEALDEPT_NAME = Z.DEALDEPT_NAME'
                    ,[DateStr,DateStr,DateStr,DateStr]);

  end;
 if RB_ByTypeMonth.Checked then
  begin
   if SE_TypeMonth.Value < 10 then
    DateStr := IntToStr(SE_TypeYear.Value) + '-0' + IntToStr(SE_TypeMonth.Value) + '%'
   else
    DateStr := IntToStr(SE_TypeYear.Value) + '-' + IntToStr(SE_TypeMonth.Value) + '%';
   Result := Format('SELECT W.DEALDEPT_NAME TYPENAME,W.NOTCOUNT NOTCOUNT,X.INTIMECOUNT INTIMECOUNT,Y.OUTTIMECOUNT OUTTIMECOUNT,Z.ALLCOUNT ALLCOUNT FROM '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT NOTCOUNT FROM ' +
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND STATE = 0) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)W, '+
                    '(SELECT D.DEALDEPT_NAME,C.INTIMECOUNT INTIMECOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) INTIMECOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FINISH_DATETIME<LIMIT_DATETIME) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO )X, '+
                    '(SELECT D.DEALDEPT_NAME,C.OUTTIMECOUNT OUTTIMECOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) OUTTIMECOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FINISH_DATETIME>LIMIT_DATETIME) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)Y , '+
                    '(SELECT D.DEALDEPT_NAME,C.ALLCOUNT ALLCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) ALLCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,APPLY_DATETIME,120),1,10) TEMPDATE FROM CLIENT_ELEC_APPLY)A '+
                    'WHERE A.TEMPDATE like ''%S'') B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASKELEC_DEALDEPT D ON C.DEALDEPT_NO = D.DEALDEPT_NO)Z '+
                    'WHERE W.DEALDEPT_NAME = X.DEALDEPT_NAME AND W.DEALDEPT_NAME = Y.DEALDEPT_NAME AND W.DEALDEPT_NAME = Z.DEALDEPT_NAME'
                    ,[DateStr,DateStr,DateStr,DateStr]);
  end;
end;

procedure TForm_ClientElecAsk.FillCount(Str: String);
begin

end;

procedure TForm_ClientElecAsk.FormCreate(Sender: TObject);
begin
 SE_CountYear.Enabled := False;
 SE_CountMonth.Enabled := False;
 SE_TypeYear.Enabled := False;
 SE_TypeMonth.Enabled := False;
 
  PC_Content.ActivePageIndex :=0;
  DTP_CountDate.DateTime:=now;
  DTP_TypeDate.DateTime:=now;
end;

procedure TForm_ClientElecAsk.RB_ByCountDayClick(Sender: TObject);
begin
 if RB_ByCountDay.Checked then
  begin
   DTP_CountDate.Enabled := True;
   SE_CountYear.Enabled := False;
   SE_CountMonth.Enabled := False;
  end;
 if RB_ByCountMonth.Checked then
  begin
   DTP_CountDate.Enabled := False;
   SE_CountYear.Enabled := True;
   SE_CountMonth.Enabled := True;
  end;
end;

procedure TForm_ClientElecAsk.BTN_CancelClick(Sender: TObject);
begin
  close;
end;

procedure TForm_ClientElecAsk.RB_ByCountMonthClick(Sender: TObject);
begin
 if RB_ByCountDay.Checked then
  begin
   DTP_CountDate.Enabled := True;
   SE_CountYear.Enabled := False;
   SE_CountMonth.Enabled := False;
  end;
 if RB_ByCountMonth.Checked then
  begin
   DTP_CountDate.Enabled := False;
   SE_CountYear.Enabled := True;
   SE_CountMonth.Enabled := True;
  end;
end;

procedure TForm_ClientElecAsk.RB_ByTypeMonthClick(Sender: TObject);
begin
 if RB_ByTypeDay.Checked then
  begin
   DTP_TypeDate.Enabled := True;
   SE_TypeYear.Enabled := False;
   SE_TypeMonth.Enabled := False;
  end;
 if RB_ByTypeMonth.Checked then
  begin
   DTP_TypeDate.Enabled := False;
   SE_TypeYear.Enabled := True;
   SE_TypeMonth.Enabled := True;
  end;
end;

procedure TForm_ClientElecAsk.RB_ByTypeDayClick(Sender: TObject);
begin
 if RB_ByTypeDay.Checked then
  begin
   DTP_TypeDate.Enabled := True;
   SE_TypeYear.Enabled := False;
   SE_TypeMonth.Enabled := False;
  end;
 if RB_ByTypeMonth.Checked then
  begin
   DTP_TypeDate.Enabled := False;
   SE_TypeYear.Enabled := True;
   SE_TypeMonth.Enabled := True;
  end;
end;

procedure TForm_ClientElecAsk.BTN_OKClick(Sender: TObject);
begin
 if PC_Content.ActivePageIndex = 0 then
  begin
   if RB_ByCountRep.Checked then
    begin
     if RB_ByType.Checked then
       PreviewReport('CLIENTELECASKTYPE',CreateAskSql,'','','用电申请报表');
     if RB_ByUnit.Checked then
       PreviewReport('CLIENTELECASKCLJ',CreateAskSql,'','','用电申请报表');
    end;
   if RB_CountWord.Checked then
    begin
     if RB_ByType.Checked then
      MakeWordReport(SysDm.WReport,CreateAskSql,AskElecTypeStr,'用电申请报表');
     if RB_ByUnit.Checked then
      MakeWordReport(SysDm.WReport,CreateAskSql,AskElecUnitStr,'用电申请报表');
    end;
  end;
 if PC_Content.ActivePageIndex = 1 then
  begin
   if RB_TypeNormal.Checked then
    PreviewReport('CLIENTELECASKCOUNT',CreateAskTypeSql,'','','用电申请报表');
   if RB_TypeWord.Checked then
    MakeWordReport(SysDm.WReport,CreateAskTypeSql,AskElecCountStr,'用电申请处理报表');
  end;
 if PC_Content.ActivePageIndex = 2 then
  begin
   if RB_FxNormal.Checked then
    PreviewReport('CLIENTELECASKFX',CreateAskFxSql(IntToStr(SE_FxYear.Value)),'','','用电申请分析报表');
   if RB_FxWord.Checked then
    MakeWordReport(SysDm.WReport,CreateAskFxSql(IntToStr(SE_FxYear.Value)),FxStr,'用电申请分析报表');
  end;
end;

procedure TForm_ClientElecAsk.DsFancyButton5Click(Sender: TObject);
begin
  DesignReport('CLIENTELECASKCOUNT',CreateAskTypeSql,'','','用电申请报表');
end;

procedure TForm_ClientElecAsk.BTN_Click(Sender: TObject);
begin
   if RB_ByType.Checked then
     DesignReport('CLIENTELECASKTYPE',CreateAskSql,'','','用电申请报表');
   if RB_ByUnit.Checked then
     DesignReport('CLIENTELECASKCLJ',CreateAskSql,'','','用电申请报表');
end;

end.