clientaskrepfrm.pas

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

unit ClientAskRepFrm;

interface

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

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

var
  Frm_ClientAsk: TFrm_ClientAsk;

implementation

uses systemDM,CommonFunc;

{$R *.DFM}

{ TFrm_ClientAsk }

function TFrm_ClientAsk.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.EXPLAIN TYPENAME,C.ASKCOUNT ASKCOUNT FROM '+
                     '(SELECT B.dfdw TYPE_NO,COUNT(*) ASKCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                     'WHERE A.TEMPDATE = ''%S'')B '+
                     ' GROUP BY B.dfdw)C RIGHT JOIN CLIENTASK_TYPE D '+
                     'ON C.TYPE_NO = D.classno ',[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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                      'WHERE A.TEMPDATE = ''%S'')B '+
                      ' GROUP BY B.DEALDEPT_NO)C RIGHT JOIN ASK_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.ASKCOUNT ASKCOUNT FROM '+
                     '(SELECT B.dfdw TYPE_NO,COUNT(*) ASKCOUNT FROM '+
                     '(SELECT A.* FROM '+
                     '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                     'WHERE A.TEMPDATE LIKE ''%S'')B '+
                     ' GROUP BY B.dfdw)C RIGHT JOIN CLIENTASK_TYPE D '+
                     'ON C.TYPE_NO = D.CLASSNO ',[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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                      'WHERE A.TEMPDATE LIKE ''%S'')B '+
                      ' GROUP BY B.DEALDEPT_NO)C RIGHT JOIN ASK_DEALDEPT D '+
                      'ON C.UNIT_NO = D.DEALDEPT_NO ',[DateStr]);
  end;
end;

procedure TFrm_ClientAsk.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('CLIENTASKTYPE',CreateAskSql,'','','用户咨询报表');
     if RB_ByUnit.Checked then
       PreviewReport('CLIENTASKCLJ',CreateAskSql,'','','用户咨询报表');
    end;
   if RB_CountWord.Checked then
    begin
     if RB_ByType.Checked then
      MakeWordReport(SysDm.WReport,CreateAskSql,AskTypeStr,'用户咨询报表');
     if RB_ByUnit.Checked then
      MakeWordReport(SysDm.WReport,CreateAskSql,AskUnitStr,'用户咨询报表');
    end;
  end;
 if PC_Content.ActivePageIndex = 1 then
  begin
   if RB_TypeNormal.Checked then
    PreviewReport('CLIENTASKCOUNT',CreateAskTypeSql,'','','用户咨询报表');
   if RB_TypeWord.Checked then
    MakeWordReport(SysDm.WReport,CreateAskTypeSql,AskCountStr,'用户咨询处理报表');
  end;
 if PC_Content.ActivePageIndex = 2 then
  begin
   if RB_FxNormal.Checked then
    PreviewReport('CLIENTASKFX',CreateAskFxSql(IntToStr(SE_FxYear.Value)),'','','用户咨询分析报表');
   if RB_FxWord.Checked then
    MakeWordReport(SysDm.WReport,CreateAskFxSql(IntToStr(SE_FxYear.Value)),FxStr,'用户咨询分析报表');
  end;
end;

function TFrm_ClientAsk.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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND STATE = 0) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FHSJ<XDSJ) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FHSJ>XDSJ) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'') B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND STATE = 0) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FHSJ<XDSJ) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'' AND (STATE = 2 OR STATE=9) AND FHSJ>XDSJ) B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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,SLSJ,120),1,10) TEMPDATE FROM CLIENTASK)A '+
                    'WHERE A.TEMPDATE like ''%S'') B '+
                    'GROUP BY B.DEALDEPT_NO) C RIGHT JOIN ASK_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;

function TFrm_ClientAsk.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;

procedure TFrm_ClientAsk.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 CLIENTASK)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 CLIENTASK)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;

procedure TFrm_ClientAsk.BTN_Click(Sender: TObject);
begin
  inherited;
   if RB_ByType.Checked then
     DesignReport('CLIENTASKTYPE',CreateAskSql,'','','用户咨询报表');
   if RB_ByUnit.Checked then
     DesignReport('CLIENTASKCLJ',CreateAskSql,'','','用户咨询报表');

end;

procedure TFrm_ClientAsk.DsFancyButton5Click(Sender: TObject);
begin
  inherited;
  DesignReport('CLIENTASKCOUNT',CreateAskTypeSql,'','','用户咨询报表');
end;

procedure TFrm_ClientAsk.DsFancyButton6Click(Sender: TObject);
begin
  inherited;
  DesignReport('CLIENTASKFX',CreateAskFxSql(IntToStr(SE_FxYear.Value)),'','','用户咨询分析报表');
end;

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

end.