u_gzfltj.~pas

语音坐席,支持录音及语音回访,和自动催款

unit U_GZFLTJ;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGridEh, ComCtrls, StdCtrls, Buttons, ComObj, ShellAPI;

type
  TGZFLTJ = class(TForm)
    Query1: TQuery;
    DataSource1: TDataSource;
    Label1: TLabel;
    RQ1: TDateTimePicker;
    RQ2: TDateTimePicker;
    DBGridEh1: TDBGridEh;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    SaveDialog1: TSaveDialog;
    Label2: TLabel;
    ComboBox1: TComboBox;
    Label3: TLabel;
    QH: TComboBox;
    procedure FormShow(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure RQ1Change(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
    Procedure QueryData;
  public
    { Public declarations }
  end;

var
  GZFLTJ: TGZFLTJ;

implementation
Uses U_Main;
{$R *.dfm}

procedure TGZFLTJ.FormShow(Sender: TObject);
begin
     RQ1.Date:=Date-30;
     RQ2.Date:=Date;

     QH.Items.Add('全部');
     QH.Items.AddStrings(MCTIForm.DBComboBoxEh3.Items);
end;

//查询数据
Procedure TGZFLTJ.QueryData;
Begin
     Query1.Close;
     While Query1.SQL.Count>2 Do Query1.SQL.Delete(2);
     Query1.SQL.Add('And To_Char(时间,''YYYY-MM-DD'')>='+QuotedStr(FormatDateTime('YYYY-MM-DD',RQ1.Date)));
     Query1.SQL.Add('And To_Char(时间,''YYYY-MM-DD'')<='+QuotedStr(FormatDateTime('YYYY-MM-DD',RQ2.Date)));
     Query1.SQL.Add('And 业务类型='+QuotedStr(ComboBox1.Text));
     If QH.ItemIndex>0 Then Query1.SQL.Add('And 片区='+QuotedStr(QH.Text));
     Query1.SQL.Add('GROUP BY 维修结果及原因');
     Query1.Open;
End;

procedure TGZFLTJ.BitBtn2Click(Sender: TObject);
begin
     DBGridEh1.OnSaveGridData(Sender);
end;

procedure TGZFLTJ.RQ1Change(Sender: TObject);
begin
     QueryData;
end;

//报表核心 生成报表
procedure TGZFLTJ.BitBtn1Click(Sender: TObject);
Var X,WB,WS:Variant;  //文档和工作表
    I:Integer;
    FLMC:String;

  //创建 Excel 和一个工作表
  Procedure CreateExcel;
  Var FTempName:String;
  begin
       FTempName:=ExtractFilePath(Application.ExeName)+'统计模版\分类统计表.xls';
       X:=CreateOleObject('Excel.Application');
       X.DisplayAlerts:=True;
       WB:=X.WorkBooks.Open(FTempName);
       WS:=WB.WorkSheets.Item[1];
  end;

  Function C2I(S:String):Integer;
  Var C:Char;
  Begin
       If (((S>='a') AND (S<='z')) OR ((S>='A') AND (S<='Z'))) Then
       Begin
            C:=S[1];
            If ((ORD(C)>=65) AND (ORD(C)<=97)) Then
               Result:=ORD(C)-64
            Else
                Result:=ORD(C)-96;
       End
       Else
           Result:=-1;
  End;

  Function I2C(I:Integer):Char;
  Begin
       If ((I>=1) AND (I<=26)) Then
          Result:=Char(I+64)
       Else
           Result:=#0;
  End;

  Function R2Str(C1,R1,C2,R2:Integer):String;
  Begin
       Result:=I2C(C1)+IntToStr(R1)+':'+I2C(C2)+IntToStr(R2);
  End;
begin
     If Not Query1.Active Then Exit;
     If Not SaveDialog1.Execute Then Exit;
     If FileExists(SaveDialog1.FileName) Then DeleteFile(SaveDialog1.FileName);
     CreateExcel;

     If Not VarIsNull(X) Then
     If Not VarIsNull(WB) Then
     If Not VarIsNull(WS) Then
     Try
        {WS.Cells[1,1].Value:='故障分类统计';
        WS.Range[R2Str(1,1,L1.Count+L2.Count+1,1)].Merge(True);

        WS.Cells[2,1].Value:='故障数';
        WS.Range[R2Str(1,2,1,3)].Merge(True);

        WS.Cells[2,2].Value:='系统内故障';
        WS.Range[R2Str(2,2,L1.Count+1,2)].Merge(True);

        WS.Cells[2,2+L1.Count].Value:='系统外故障';
        WS.Range[R2Str(2+L1.Count,2,L1.Count+1+L2.Count,2)].Merge(True);

        For I:=0 To L1.Count-1 Do WS.Cells[3,1+I+1].Value:=L1.Items[I];
        For I:=0 To L2.Count-1 Do WS.Cells[3,1+I+1+L1.Count].Value:=L2.Items[I];}

        //生成统计表 1 清除数据
        For I:=2 To 36 Do
        If WS.Cells[4,I].Text<>'' Then WS.Cells[4,I].Value:=0;

        Query1.First;
        While Not Query1.Eof Do
        Begin
             FLMC:=Query1.FieldByName('维修结果及原因').AsString;
             For I:=2 To 36 Do
             If WS.Cells[3,I].Text=FLMC Then
             Begin
                  WS.Cells[4,I].Value:=Query1.FieldByName('数量').AsInteger;
                  Break;
             End;

             Query1.Next;
        End;
     Finally
            WB.SaveAs(SaveDialog1.FileName);
            X.Quit;
            ShellExecute(Handle,PChar('open'),PChar(SaveDialog1.FileName),Nil,Nil,1);
     End;
end;

end.