usercomprepfrm.pas

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

unit UserCompRepFrm;

interface

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

type
  TFrm_ClientComp = 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;
   procedure FillCount(Str:String);
   function CreateFxSql(Str:String): String; 
  public
    { Public declarations }
  end;

var
  Frm_ClientComp: TFrm_ClientComp;

implementation

{$R *.DFM}

{ TFrm_Base1 }
uses pubReport, systemDM,dbtables,CommonFunc;
function TFrm_ClientComp.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 D.EXPLAIN TYPENAME,C.COMPCOUNT COMPCOUNT FROM '+
                     '(SELECT B.COMP_TYPE TYPE_NO,COUNT(*) COMPCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                     'WHERE A.TEMPDATE = ''%s'')B '+
                     ' GROUP BY B.COMP_TYPE)C RIGHT JOIN COMP_TYPE D '+
                     'ON C.TYPE_NO = D.ID',[DateStr]);
    if RB_ByUnit.Checked then
     Result := Format('SELECT D.DEALDEPT_NAME UNIT_NAME,C.COMPCOUNT COMPCOUNT FROM '+
                      '(SELECT B.DEALDEPT_NO UNIT_NO,COUNT(*)COMPCOUNT FROM '+
                      '(SELECT A.* FROM '+
                      '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                      'WHERE A.TEMPDATE = ''%s'')B '+
                      'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_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.EXPLAIN TYPENAME,C.COMPCOUNT COMPCOUNT FROM '+
                     '(SELECT B.COMP_TYPE TYPE_NO,COUNT(*) COMPCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                     'WHERE A.TEMPDATE LIKE ''%s'')B '+
                     ' GROUP BY B.COMP_TYPE)C RIGHT JOIN COMP_TYPE D '+
                     'ON C.TYPE_NO = D.ID',[DateStr]);
    if RB_ByUnit.Checked then
     Result := Format('SELECT D.DEALDEPT_NAME UNIT_NAME,C.COMPCOUNT FROM '+
                      '(SELECT B.DEALDEPT_NO UNIT_NO,COUNT(*)COMPCOUNT FROM '+
                      '(SELECT A.* FROM '+
                      '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                      'WHERE A.TEMPDATE LIKE ''%s'')B '+
                      'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                      'ON C.UNIT_NO = D.DEALDEPT_NO',[DateStr]);
  end;
end;

procedure TFrm_ClientComp.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('USERCOMPTYPE',CreateCountSql,'','','用户投诉报表');
     if RB_ByUnit.Checked then
       PreviewReport('USERCOMPUNIT',CreateCountSql,'','','用户投诉报表');
    end;
   if RB_CountWord.Checked then
    begin
     if RB_ByType.Checked then
      MakeWordReport(SysDM.WReport,CreateCountSql,CompTypeStr,'用户投诉报表');
     if RB_ByUnit.Checked then
      MakeWordReport(SysDM.WReport,CreateCountSql,CompUnitStr,'用户投诉报表')
    end;
  end;
 if PC_Content.ActivePageIndex = 1 then
  begin
   if RB_TypeNormal.Checked then
     PreviewReport('USERCOMPCOUNT',CreateTypeSql,'','','用户投诉报表');
   if RB_TypeWord.Checked then
     MakeWordReport(SysDM.WReport,CreateTypeSql,CompCountStr,'用户投诉处理报表')
  end;
 if PC_Content.ActivePageIndex = 2 then
  begin
   if RB_FxNormal.Checked then
    PreviewReport('USERCOMPFX',CreateFxSql(IntToStr(SE_FxYear.Value)),'','','用户投诉分析报表');
   if RB_FxWord.Checked then
    MakeWordReport(SysDM.WReport,CreateFxSql(IntToStr(SE_FxYear.Value)),FxStr,'用户投诉分析报表')
  end;
end;

function TFrm_ClientComp.CreateTypeSql: 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.NOTCOUNT INTIMECOUNT,Y.NOTCOUNT OUTTIMECOUNT,Z.NOTCOUNT ALLCOUNT FROM '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND STATE = 0)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)W,'+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND (STATE = 2 OR STATE=9) AND FHSJ<XDSJ)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)X, '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND (STATE = 2 OR STATE=9) AND FHSJ>XDSJ)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)Y, ' +
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM ' +
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' )B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_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.NOTCOUNT INTIMECOUNT,Y.NOTCOUNT OUTTIMECOUNT,Z.NOTCOUNT ALLCOUNT FROM '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND STATE = 0)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)W,'+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND (STATE = 2 OR STATE=9) AND FHSJ<XDSJ)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)X, '+
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM '+
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' AND (STATE = 2 OR STATE=9) AND FHSJ>XDSJ)B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_DEALDEPT D '+
                    'ON C.DEALDEPT_NO = D.DEALDEPT_NO)Y, ' +
                    '(SELECT D.DEALDEPT_NAME,C.NOTCOUNT FROM ' +
                    '(SELECT B.DEALDEPT_NO,COUNT(*) NOTCOUNT FROM '+
                    '(SELECT A.* FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTCOMP)A '+
                    'WHERE A.TEMPDATE like ''%s'' )B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN COMP_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 TFrm_ClientComp.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,SLSJ,120),1,7) TEMPDATE FROM CLIENTCOMP)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,SLSJ,120),1,7) TEMPDATE FROM CLIENTCOMP)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_ClientComp.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_ClientComp.BTN_Click(Sender: TObject);
begin
  inherited;
  if RB_ByType.Checked then
     DesignReport('USERCOMPTYPE',CreateCountSql,'','','用户投诉报表');
  if RB_ByUnit.Checked then
     DesignReport('USERCOMPUNIT',CreateCountSql,'','','用户投诉报表');
end;

procedure TFrm_ClientComp.DsFancyButton5Click(Sender: TObject);
begin
  inherited;
  DesignReport('USERCOMPCOUNT',CreateTypeSql,'','','用户投诉报表');
end;

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

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

end.