zhrepfrm.pas

来自「一个电力企业的后台管理程序」· PAS 代码 · 共 405 行 · 第 1/2 页

                     '(SELECT B.USERSATISFY FLAG,COUNT(*)RECCOUNT FROM '+
                     '(SELECT A.* FROM (SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,10) TEMPDATE FROM '+
                     'CLIENTELECOUT)A '+
                     'WHERE TEMPDATE LIKE ''%S'')B '+
                     'GROUP BY B.USERSATISFY) C '+
                     'RIGHT JOIN USERSATISFY D ON C.FLAG = D.ID',[DateStr]);
   if RB_UserAsk.Checked then
    Result := Format('SELECT D.DESCRIPTION DES,C.RECCOUNT RECCOUNT FROM '+
                     '(SELECT B.USERSATISFY FLAG,COUNT(*)RECCOUNT FROM '+
                     '(SELECT A.* FROM (SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                     'CLIENTASK)A '+
                     'WHERE TEMPDATE LIKE ''%S'')B '+
                     'GROUP BY B.USERSATISFY) C '+
                     'RIGHT JOIN USERSATISFY D ON C.FLAG = D.ID',[DateStr]);
   if RB_UserComp.Checked then
    Result := Format('SELECT D.DESCRIPTION DES,C.RECCOUNT RECCOUNT FROM '+
                     '(SELECT B.USERSATISFY FLAG,COUNT(*)RECCOUNT FROM '+
                     '(SELECT A.* FROM (SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                     'CLIENTCOMP)A '+
                     'WHERE TEMPDATE LIKE ''%S'')B '+
                     'GROUP BY B.USERSATISFY) C '+
                     'RIGHT JOIN USERSATISFY D ON C.FLAG = D.ID',[DateStr]);
  end;
end;

function TFrm_ZhRep.MakeSql: String;
var
 DateStr : String;
begin
 if RB_ByBackDay.Checked then
  begin
   DateStr := FormatDateTime('YYYY-MM-DD',DTP_BackDate.Date);
   Result := Format('SELECT  X.ASKBFL ASKBFL,Y.COMPBFL COMPBFL,Z.ELECOUTBFL ELECOUTBFL FROM '+
                    '(SELECT * FROM '+
                    '(SELECT 1 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%s''  ASKBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTASK)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE = ''%s'' )A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM (SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTASK)TTT WHERE TTT.TEMPDATE = ''%s'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)X, '+
                    '(SELECT * FROM '+
                    '(SELECT 2 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%S''  COMPBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTCOMP)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE = ''%s'')A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTCOMP)TTT WHERE TTT.TEMPDATE = ''%s'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)Y, '+
                    '(SELECT * FROM '+
                    '(SELECT 3 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%s''  ELECOUTBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTELECOUT)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE = ''%S'')A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTELECOUT)TTT WHERE TTT.TEMPDATE = ''%S'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)Z '
                  ,['%',DateStr,DateStr,'%',DateStr,DateStr,'%',DateStr,DateStr]);

  end;
 if RB_ByBackMonth.Checked then
  begin
   if SE_BackMonth.Value < 10 then
    DateStr := IntToStr(SE_BackYear.Value) + '-0' + IntToStr(SE_BackMonth.Value) + '%'
   else
    DateStr := IntToStr(SE_BackYear.Value) + '-' + IntToStr(SE_BackMonth.Value) + '%';
   Result := Format('SELECT  X.ASKBFL ASKBFL,Y.COMPBFL COMPBFL,Z.ELECOUTBFL ELECOUTBFL FROM '+
                    '(SELECT * FROM '+
                    '(SELECT 1 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%s''  ASKBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTASK)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE LIKE ''%s'' )A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM (SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTASK)TTT WHERE TTT.TEMPDATE LIKE ''%s'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)X, '+
                    '(SELECT * FROM '+
                    '(SELECT 2 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%S''  COMPBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTCOMP)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE LIKE ''%s'')A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,SLSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTCOMP)TTT WHERE TTT.TEMPDATE LIKE ''%s'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)Y, '+
                    '(SELECT * FROM '+
                    '(SELECT 3 TEMPNO,SUBSTRING(CONVERT(VARCHAR,(CONVERT(FLOAT,A.FEEDBACK)/CONVERT(FLOAT,B.ALLCOUNT) * 100)),1,5)+''%s''  ELECOUTBFL FROM '+
                    '(SELECT COUNT(*) FEEDBACK FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTELECOUT)TT WHERE TT.FEEDBACK_FLAG = 1 AND TT.TEMPDATE LIKE ''%S'')A, '+
                    '(SELECT COUNT(*) ALLCOUNT FROM '+
                    '(SELECT *,SUBSTRING(CONVERT(CHAR,ZZSBSJ,120),1,10) TEMPDATE FROM '+
                    'CLIENTELECOUT)TTT WHERE TTT.TEMPDATE LIKE ''%S'')B '+
                    ' WHERE B.ALLCOUNT <> 0) C RIGHT JOIN (SELECT 1 TEMP_NO)W ON C.TEMPNO = W.TEMP_NO)Z '
                  ,['%',DateStr,DateStr,'%',DateStr,DateStr,'%',DateStr,DateStr]);
  end;
end;

procedure TFrm_ZhRep.DsFancyButton4Click(Sender: TObject);
begin
 DesignReport('FEEDBACK',CreateBackSql,'','','回访率统计报表');
end;

function TFrm_ZhRep.CreateBackSql: String;

begin
 FillTable;
 Result := 'SELECT TYPE,BFL FROM TEMPCOUNT'; 
end;

procedure TFrm_ZhRep.DsFancyButton1Click(Sender: TObject);
begin
   if RB_EleCout.Checked then
    DesignReport('ZHSATISFY',CreateSatSql,'','','故障报修满意度报表');
   if RB_UserAsk.Checked then
    DesignReport('SATISFYASK',CreateSatSql,'','','用户咨询满意度报表');
   if RB_UserComp.Checked then
    DesignReport('SATISFYCOPM',CreateSatSql,'','','用户投诉满意度报表');end;

procedure TFrm_ZhRep.DsFancyButton3Click(Sender: TObject);
begin
 Close;
end;

procedure TFrm_ZhRep.RB_BySatDayClick(Sender: TObject);
begin
 if RB_BySatDay.Checked then
  begin
   DTP_SatDate.Enabled := True;
   SE_SatYear.Enabled := False;
   SE_SatMonth.Enabled := False;
  end;
 if RB_BySatMonth.Checked then
  begin
   DTP_SatDate.Enabled := False;
   SE_SatYear.Enabled := True;
   SE_SatMonth.Enabled := True;
  end;
end;

procedure TFrm_ZhRep.RB_BySatMonthClick(Sender: TObject);
begin
 if RB_BySatDay.Checked then
  begin
   DTP_SatDate.Enabled := True;
   SE_SatYear.Enabled := False;
   SE_SatMonth.Enabled := False;
  end;
 if RB_BySatMonth.Checked then
  begin
   DTP_SatDate.Enabled := False;
   SE_SatYear.Enabled := True;
   SE_SatMonth.Enabled := True;
  end;
end;

procedure TFrm_ZhRep.FormCreate(Sender: TObject);
begin
 SE_SatYear.Enabled := False;
 SE_SatMonth.Enabled := False;
 SE_BackYear.Enabled := False;
 SE_BackMonth.Enabled := False;
 DTP_SatDate.DateTime :=now;
end;

procedure TFrm_ZhRep.RB_ByBackDayClick(Sender: TObject);
begin
 if RB_ByBackDay.Checked then
  begin
   DTP_BackDate.Enabled := True;
   SE_BackYear.Enabled := False;
   SE_BackMonth.Enabled := False;
  end;
 if RB_ByBackMonth.Checked then
  begin
   DTP_BackDate.Enabled := False;
   SE_BackYear.Enabled := True;
   SE_BackMonth.Enabled := True;
  end;

end;

procedure TFrm_ZhRep.RB_ByBackMonthClick(Sender: TObject);
begin
 if RB_ByBackDay.Checked then
  begin
   DTP_BackDate.Enabled := True;
   SE_BackYear.Enabled := False;
   SE_BackMonth.Enabled := False;
  end;
 if RB_ByBackMonth.Checked then
  begin
   DTP_BackDate.Enabled := False;
   SE_BackYear.Enabled := True;
   SE_BackMonth.Enabled := True;
  end;

end;

end.