gzrepfrm.pas

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

unit GzRepFrm;

interface

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

type
  TFrm_GzRep = class(TFrm_Base)
    procedure BTN_OKClick(Sender: TObject);
    procedure BTN_Click(Sender: TObject);
    procedure DsFancyButton5Click(Sender: TObject);
    procedure DsFancyButton6Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
   function CreateCountSql: String;  
   function CreateTypeSql: String;
   function CreateFxSql(Str: String): String;
   procedure FillCount(Str:String);
  public
    { Public declarations }
  end;

var
  Frm_GzRep: TFrm_GzRep;

implementation

{$R *.DFM}
 uses pubReport, systemDM,dbtables,CommonFunc;
{ TFrm_GzRep }

function TFrm_GzRep.CreateTypeSql: String;
var
 DateStr: String;
begin
 if RB_ByTypeDay.Checked then
  begin
   DateStr := '%'+FormatDateTime('YYYY-MM-DD',DTP_TypeDate.Date);
   Result := Format('SELECT X.DEALDEPT_NAME EXPLAIN,X.COUNTNOT NOTCOUNT,Y.COUNTNOT INTIMECOUNT,Z.COUNTNOT OVERCOUNT,Q.COUNTNOT OVERNOTCOUNT,W.COUNTNOT ALLCOUNT FROM '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE STATE= 0)U WHERE U.TEMPDATE LIKE ''%s''  )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO) X, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE STATE= 2 OR STATE=9)V WHERE V.TEMPDATE LIKE ''%s''  )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Y, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE '+
                    '((IsOverTime=1 and datediff(minute,zzflsj,pcsj)>45)OR(IsOverTime=2 and datediff(minute,zzflsj,pcsj)>90)OR(IsOverTime=3 and datediff(HOUR,zzflsj,pcsj)>2))AND State=2)W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Z, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE '+
                    '((IsOverTime=1 and datediff(minute,zzflsj,getdate())>45)OR(IsOverTime=2 and datediff(minute,zzflsj,getdate())>90)OR(IsOverTime=3 and datediff(HOUR,zzflsj,getdate())>2))AND State=0)W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Q, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT )W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)W '+
                    'WHERE X.DEALDEPT_NAME = Y.DEALDEPT_NAME AND X.DEALDEPT_NAME = Z.DEALDEPT_NAME AND X.DEALDEPT_NAME = Q.DEALDEPT_NAME AND X.DEALDEPT_NAME = W.DEALDEPT_NAME'
                    ,[DateStr + '%',DateStr + '%',DateStr + '%',DateStr + '%',DateStr + '%']);

  end;
 if RB_ByTypeMonth.Checked then
  begin
   DateStr := '%'+IntToStr(SE_TypeYear.Value) + '-' + IntToStr(SE_TypeMonth.Value);
   Result := Format('SELECT X.DEALDEPT_NAME EXPLAIN,X.COUNTNOT NOTCOUNT,Y.COUNTNOT INTIMECOUNT,Z.COUNTNOT OVERCOUNT,Q.COUNTNOT OVERNOTCOUNT,W.COUNTNOT ALLCOUNT FROM '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE STATE= 0)U WHERE U.TEMPDATE LIKE ''%s''  )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO) X, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE STATE= 2 OR STATE=9)V WHERE V.TEMPDATE LIKE ''%s''  )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Y, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE '+
                    '((IsOverTime=1 and datediff(minute,zzflsj,pcsj)>45)OR(IsOverTime=2 and datediff(minute,zzflsj,pcsj)>90)OR(IsOverTime=3 and datediff(HOUR,zzflsj,pcsj)>2))AND State=2)W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Z, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT WHERE '+
                    '((IsOverTime=1 and datediff(minute,zzflsj,getdate())>45)OR(IsOverTime=2 and datediff(minute,zzflsj,getdate())>90)OR(IsOverTime=3 and datediff(HOUR,zzflsj,getdate())>2))AND State=0)W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)Q, '+
                    '(SELECT C.DEALDEPT_NAME,COUNTNOT FROM '+
                    '(SELECT A.DEALDEPT_NO, COUNT(DEALDEPT_NO) COUNTNOT FROM '+
                    '(SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT)W WHERE W.TEMPDATE LIKE ''%s'' )  A '+
                    'GROUP BY A.DEALDEPT_NO) B RIGHT JOIN ELECOUT_DEALDEPT C ON B.DEALDEPT_NO = C.DEALDEPT_NO)W '+
                    'WHERE X.DEALDEPT_NAME = Y.DEALDEPT_NAME AND X.DEALDEPT_NAME = Z.DEALDEPT_NAME AND X.DEALDEPT_NAME = Q.DEALDEPT_NAME AND X.DEALDEPT_NAME = W.DEALDEPT_NAME'
                    ,[DateStr + '%',DateStr + '%',DateStr + '%',DateStr + '%',DateStr + '%']);

  end;
end;

procedure TFrm_GzRep.BTN_OKClick(Sender: TObject);
begin
  inherited;
 if PC_Content.ActivePageIndex = 0 then
  begin
   if RB_ByCountRep.Checked then
    begin
     if RB_ByType.Checked then
      PreviewReport('GZSL',CreateCountSql,'','','故障报修报表');
     if RB_ByUnit.Checked  then
      PreviewReport('CZSLCLJ',CreateCountSql,'','','故障报修报表');
    end;
   if RB_CountWord.Checked then
    begin
     if RB_ByType.Checked then
      MakeWordReport(SysDm.WReport,CreateCountSql,GzCountStr,'故障报修报表');
     if RB_ByUnit.Checked  then
      MakeWordReport(SysDM.WReport,CreateCountSql,GzUnitStr,'故障报修报表');
    end;
  end;
 if PC_Content.ActivePageIndex = 1 then
  begin
   if RB_TypeNormal.Checked then
    PreviewReport('GZSLCOUNT',CreateTypeSql,'','','故障报修处理报表');
   if RB_TypeWord.Checked then
    MakeWordReport(SysDM.WReport,CreateTypeSql,GzTypeStr,'故障报修处理报表');
  end;
 if PC_Content.ActivePageIndex = 2 then
  begin
   if RB_FxNormal.Checked then
    PreviewReport('GZFX',CreateFxSql(IntToStr(SE_FxYear.Value)),'','','故障受理分析报表');
   if RB_FxWord.Checked then
    MakeWordReport(SysDm.WReport,CreateFxSql(IntToStr(SE_FxYear.Value)),FxStr,'故障受理分析报表');
  end;
end;

function TFrm_GzRep.CreateCountSql: 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 B.EXPLAIN,A.RECCOUNT TYPECOUNT FROM '+
              '(SELECT Y.xcgzqk,COUNT(Y.xcgzqk) RECCOUNT '+
              ' FROM (SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT)X WHERE X.TEMPDATE like ''%s'')Y '+
              ' GROUP BY Y.xcgzqk) A '+
              ' RIGHT JOIN ELECOUTTYPE B ON A.xcgzqk = B.Explain ',[DateStr]);
   if RB_ByUnit.Checked then
    begin
     Result := Format('SELECT B.DEALDEPT_NAME,A.RECCOUNT CLJ FROM '+
                      ' (SELECT Y.DEALDEPT_NO, COUNT(Y.DEALDEPT_NO) RECCOUNT '+
                      'FROM (SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT)X WHERE X.TEMPDATE like ''%s'')Y '+
                      ' GROUP BY Y.DEALDEPT_NO) A '+
                      'RIGHT JOIN ELECOUT_DEALDEPT B ON A.DEALDEPT_NO = B.DEALDEPT_NO',[DateStr ]);
    end;
  end;
 if RB_ByCountMonth.Checked then
  begin
   DateStr := '%'+IntToStr(SE_CountYear.Value) + '-' + IntToStr(SE_CountMonth.Value)+'%';
   if RB_ByType.Checked then
    Result := Format('SELECT B.EXPLAIN,A.RECCOUNT TYPECOUNT FROM '+
              '(SELECT Y.xcgzqk,COUNT(Y.xcgzqk) RECCOUNT '+
              ' FROM (SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT)X WHERE X.TEMPDATE like ''%s'')Y '+
              ' GROUP BY Y.xcgzqk) A '+
              ' RIGHT JOIN ELECOUTTYPE B ON A.xcgzqk = B.Explain ',[DateStr ]);
   if RB_ByUnit.Checked then
    begin
     Result := Format('SELECT B.DEALDEPT_NAME,A.RECCOUNT CLJ FROM '+
                      ' (SELECT Y.DEALDEPT_NO, COUNT(Y.DEALDEPT_NO) RECCOUNT '+
                      'FROM (SELECT * FROM (SELECT *,CONVERT(CHAR,ZZSBSJ,120) TEMPDATE FROM CLIENTELECOUT)X WHERE X.TEMPDATE like ''%s'')Y '+
                      ' GROUP BY Y.DEALDEPT_NO) A '+
                      'RIGHT JOIN ELECOUT_DEALDEPT B ON A.DEALDEPT_NO = B.DEALDEPT_NO',[DateStr]);

     end;
  end;   
end;

procedure TFrm_GzRep.FillCount(Str: String);
var
 Query,LastQuery : TQuery;
begin
 Query := TQuery.Create(nil);
 LastQuery := TQuery.Create(nil);
 try
  Query.DatabaseName := SysDM.DBMain.DatabaseName;
  LastQuery.DatabaseName := SysDM.DBMain.DatabaseName;
  Query.Close;
  LastQuery.Close;
  Query.SQL.Text := Format('SELECT D.MON MON,C.MONCOUNT FROM '+
                           '(SELECT B.TEMPDATE,COUNT(*) MONCOUNT FROM '+
                           '(SELECT A.* FROM '+
                           '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,7) TEMPDATE FROM CLIENTELECOUT)A '+
                           'WHERE A.TEMPDATE LIKE ''%s'')B '+
                           'GROUP BY B.TEMPDATE) C RIGHT JOIN ELECOUTCOUNT D '+
                           'ON C.TEMPDATE = D.MON',[Str + '%']);
  LastQuery.SQL.Text := Format('SELECT D.MON MON,C.MONCOUNT FROM '+
                           '(SELECT B.TEMPDATE,COUNT(*) MONCOUNT FROM '+
                           '(SELECT A.* FROM '+
                           '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,7) TEMPDATE FROM CLIENTELECOUT)A '+
                           'WHERE A.TEMPDATE LIKE ''%s'')B '+
                           'GROUP BY B.TEMPDATE) C RIGHT JOIN TEMLASTYEAR D '+
                           'ON C.TEMPDATE = D.MON',[IntToStr(StrToInt(Str) - 1) + '%']);
  Query.Open;
  LastQuery.Open;
  Query.First;
  LastQuery.First;
  InsertCount(Query,LastQuery);
 finally
  Query.Free;
  LastQuery.Free;
 end;
end;

function TFrm_GzRep.CreateFxSql(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;

procedure TFrm_GzRep.BTN_Click(Sender: TObject);
begin
  inherited;
   if RB_ByType.Checked then
    DesignReport('GZSL',CreateCountSql,'','','故障报修报表');
   if RB_ByUnit.Checked  then
    DesignReport('CZSLCLJ',CreateCountSql,'','','故障报修报表');
end;

procedure TFrm_GzRep.DsFancyButton5Click(Sender: TObject);
begin
  inherited;
  DesignReport('GZSLCOUNT',CreateTypeSql,'','','故障报修处理报表');
end;

procedure TFrm_GzRep.DsFancyButton6Click(Sender: TObject);
begin
  inherited;
  DesignReport('GZFX',CreateFxSql(IntToStr(SE_FxYear.Value)),'','','用户投诉分析报表');
end;

procedure TFrm_GzRep.FormCreate(Sender: TObject);
begin
  inherited;
  PC_Content.ActivePageIndex :=0;
  DTP_CountDate.DateTime :=now;
  DTP_TypeDate.DateTime :=now;
end;

end.