u_cssz.pas

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

unit U_CSSZ;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, ComCtrls, ExtCtrls, DBCtrls, StdCtrls, Buttons, Mask,
  Grids, DBGridEh, DBTables, DateUtils;

type
  TCSSZ = class(TForm)
    PageControl1: TPageControl;
    DBNavigator1: TDBNavigator;
    BitBtn1: TBitBtn;
    Query1: TQuery;
    UpdateSQL1: TUpdateSQL;
    DataSource1: TDataSource;
    TabSheet1: TTabSheet;
    DBMemo1: TDBMemo;
    TabSheet2: TTabSheet;
    DBMemo2: TDBMemo;
    TabSheet3: TTabSheet;
    DBMemo3: TDBMemo;
    TabSheet4: TTabSheet;
    DBMemo4: TDBMemo;
    TabSheet5: TTabSheet;
    DBMemo5: TDBMemo;
    TabSheet6: TTabSheet;
    TabSheet7: TTabSheet;
    DBMemo6: TDBMemo;
    DBMemo7: TDBMemo;
    TabSheet8: TTabSheet;
    DBMemo8: TDBMemo;
    QPub: TQuery;
    TabSheet9: TTabSheet;
    DBMemo9: TDBMemo;
    TabSheet10: TTabSheet;
    TabSheet11: TTabSheet;
    TabSheet12: TTabSheet;
    TabSheet13: TTabSheet;
    DBMemo10: TDBMemo;
    DBMemo11: TDBMemo;
    DBMemo12: TDBMemo;
    DBMemo13: TDBMemo;
    TabSheet14: TTabSheet;
    DBMemo14: TDBMemo;
    TabSheet15: TTabSheet;
    DBMemo15: TDBMemo;
    TabSheet16: TTabSheet;
    DBMemo16: TDBMemo;
    QPubYH: TQuery;
    procedure FormShow(Sender: TObject);
    procedure Query1AfterPost(DataSet: TDataSet);
  private
    { Private declarations }
    LastJFRQ:TDate;             //最后计费日期
    LastJFYS:Integer;           //最后一次交费月数
    LastJFJE:Single;            //最后一次交费金额

    Procedure LastJFXH(ID:Integer);
  public
    { Public declarations }
    Function SysDateTime:TDateTime;
    Procedure CalaIP(ID:Integer);
  end;

var
  CSSZ: TCSSZ;

implementation
uses U_Main;
{$R *.dfm}

procedure TCSSZ.FormShow(Sender: TObject);
begin
     If Not Query1.Active Then Query1.Open;
end;

procedure TCSSZ.Query1AfterPost(DataSet: TDataSet);
begin
     Query1.ApplyUpdates;
     Query1.CommitUpdates;
end;

Function TCSSZ.SysDateTime:TDateTime;
Begin
     QPub.Close;
     QPub.SQL.Clear;
     QPub.SQL.Add('Select SysDate From Dual');
     QPub.Open;

     Result:=QPub.FieldByName('SysDate').AsDateTime;
     QPub.Close;
End;

Procedure TCSSZ.LastJFXH(ID:Integer);
Begin
     QPubYH.Close;
     QPubYH.SQL.Clear;
     QPubYH.SQL.Add('Select * From CATVIP.交费信息 a,CATVIP.收费项目 b Where ID='+IntToStr(ID));
     QPubYH.SQL.Add('And a.收费类型=b.项目名称');
     QPubYH.SQL.Add('And 是否月租费=''1''');
     QPubYH.SQL.Add('Order By XH');
     QPubYH.Open;

     LastJFYS:=0;
     LastJFJE:=0.0;
     QPubYH.Last;
     While Not QPubYH.Bof Do
     Begin
          LastJFYS:=LastJFYS+QPubYH.FieldByName('交费月数').AsInteger;
          LastJFJE:=LastJFJE+QPubYH.FieldByName('交费金额').AsCurrency;
          If Not QPubYH.FieldByName('计费日期').IsNull Then
          Begin
               LastJFRQ:=QPubYH.FieldByName('计费日期').AsDateTime;
               Break;
          End;

          QPubYH.Prior;
     End;
End;

//计算宽带用户到费日期
Procedure TCSSZ.CalaIP(ID:Integer);
Var JFYS,BTYS,I:INteger;
    JFRQ,CurrentDay:TDate;
    Y,M,D:Word;
    YHZT:String;
Begin
     //IP 绑定-〉当前IP
     QPubYH.Close;
     QPubYH.SQL.Clear;
     QPubYH.SQL.Add('Update CATVIP.用户信息 a Set a.IP地址=(Select IP地址 From CATVIP.绑定IP b Where b.ID=a.ID And SysDate>=绑定日期 And SysDate<=取消日期) Where ID='+IntToStr(ID));
     QPubYH.ExecSQL;

     //开通/退网
     QPubYH.Close;
     QPubYH.SQL.Clear;
     QPubYH.SQL.Add('Select * From CATVIP.用户信息 Where ID='+IntToStr(ID));
     QPubYH.Open;
     If QPubYH.FieldByName('计费日期').IsNull Then Exit;
     If Not QPubYH.FieldByName('退网日期').IsNull Then Exit;
     LastJFRQ:=QPubYH.FieldByName('计费日期').AsDateTime;

     //最后一次的缴费
     LastJFXH(ID);
     If LastJFYS<=0 Then Exit;
     JFRQ:=LastJFRQ;
     JFYS:=LastJFYS;

     //期间的报停月数
     QPubYH.Close;
     QPubYH.SQL.Clear;
     QPubYH.SQL.Add('Select * From CATVIP.报停信息 Where ID='+IntToStr(ID));
     QPubYH.SQL.Add('And To_Char(停机日期,''YYYY-MM-DD'')>='+QuotedStr(FormatDateTime('YYYY-MM-DD',LastJFRQ)));
     QPubYH.Open;
     BTYS:=0;
     While Not QPubYH.Eof Do
     Begin
          BTYS:=BTYS+QPubYH.FieldByName('报停月数').AsInteger-QPubYH.FieldByName('提前开通月数').AsInteger;
          QPubYH.Next;
     End;

     DeCodeDate(JFRQ,Y,M,D);
     For I:=1 To JFYS+BTYS Do
     Begin
          If M<12 Then
             M:=M+1
          Else
          Begin
               M:=1;
               Y:=Y+1;
          End;
     End;

     While Not IsValidDate(Y,M,D) Do
     Begin
          If D>1 Then
             D:=D-1
          Else
          Begin
               D:=31;
               If M>1 Then
                  M:=M-1
               Else
               Begin
                    M:=12;
                    Y:=Y-1;
               End;
          End;
     End;

     JFRQ:=IncDay(EnCodeDate(Y,M,D),-1);
     CurrentDay:=SysDateTime;

     //用户状态
     YHZT:='正式开通';
     If SysDateTime>JFRQ Then
        YHZT:='停机'
     Else
     Begin
          QPubYH.First;
          While Not QPubYH.Eof Do
          Begin
               If ((CurrentDay>=QPubYH.FieldByName('停机日期').AsDateTime) AND (CurrentDay<QPubYH.FieldByName('开通日期').AsDateTime)) Then
               Begin
                    YHZT:='报停';
                    Break;
               End;               

               QPubYH.Next;
          End;
     End;
     
     QPubYH.Close;
     QPubYH.SQL.Clear;
     QPubYH.SQL.Add('Update CATVIP.用户信息 Set 到费日期=To_Date('''+FormatDateTime('YYYY-MM-DD',JFRQ)+''',''YYYY-MM-DD''),');
     QPubYH.SQL.Add('用户状态='+QuotedStr(YHZT));
     QPubYH.SQL.Add('Where ID='+IntToStr(ID));
     QPubYH.ExecSQL;
End;

end.