maing.pas

中国联通 CDMA点对点短信话单分析入库的程序

unit mainG;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, RzPanel, RzSplit, StdCtrls, RzEdit, Mask, RzButton,
  DB, DBAccess, Ora, RzRadChk, ADODB, DBXpress, SqlExpr;

type
  TForm1 = class(TForm)
    RzSplitter1: TRzSplitter;
    RzMemo1: TRzMemo;
    RzPanel1: TRzPanel;
    RzButton1: TRzButton;
    RzButton2: TRzButton;
    RzButton3: TRzButton;
    RzDateTimeEdit1: TRzDateTimeEdit;
    Timer1: TTimer;
    Conn: TOraSession;
    LEdit1: TLabeledEdit;
    CheckBox1: TCheckBox;
    ss_conn: TADOConnection;
    CreateRpt: TADOCommand;
    queryrpt: TADOQuery;
    insertora: TOraSQL;
    queryrptsendtime: TDateTimeField;
    queryrptcallingnumber: TStringField;
    queryrptsendcount: TIntegerField;
    DelOra: TOraSQL;
    procedure RzButton1Click(Sender: TObject);
    procedure RzButton2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure RzButton3Click(Sender: TObject);
  private
    { Private declarations }
    procedure Display(logstr: string);
    procedure DoSync;
    procedure DeleteRptSqlserver;
    procedure CreateRptSqlserver(rptdate:TDateTime);
    procedure LoadToOra;
    procedure instora(v_phonenum:String;v_sendtime:TDateTime;v_sendcount:Integer);
    procedure DeleteOra(rptdate:TDateTime);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
implementation
uses IniFiles;
{$R *.dfm}

procedure TForm1.RzButton1Click(Sender: TObject);
begin
 try
  Timer1.Enabled := true;
  RzButton1.Enabled := false;
  RzButton2.Enabled := true;
 except
   on E: Exception do Display(E.Message);
 end;
end;

procedure TForm1.RzButton2Click(Sender: TObject);
begin
  Timer1.Enabled := false;
  RzButton2.Enabled := false;
  RzButton1.Enabled := true;
end;
procedure TForm1.Display(logstr: string);
begin
  RzMemo1.Lines.Add(FormatDateTime('YYYY-MM-DD hh:mm:ss', Now()) + ' ' + logstr)
end;

procedure TForm1.FormCreate(Sender: TObject);
var RunConfig: TIniFile;
begin
  RunConfig := TIniFile.Create(ExtractFileDir(Application.ExeName) + '\\Config.ini');
  with RunConfig do begin
  Conn.Server := ReadString('dbset', 'server', 'ucsys');
  Conn.Username := ReadString('dbset', 'user', 'ucsys');
  Conn.Password := ReadString('dbset', 'password', 'ucsys');
  ss_conn.ConnectionString:=ReadString('dbset', 'sqlconnstr', 'Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=mobile;Data Source=localhost');
  if (ReadString('runparam', 'auto_start', '0') = '1') then CheckBox1.Checked := true;
  LEdit1.Text:=ReadString('runparam','gsm_start_time','01:00:00');
  Free;
  end;
  if (CheckBox1.Checked) then RzButton1Click(Application);
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
RzDateTimeEdit1.Date:=Date()-1;
if formatDateTime('hh:nn:ss',Now())=LEdit1.Text then begin
  DoSync;
end;
end;

procedure TForm1.DoSync;
begin
  try
  if not ss_conn.Connected then ss_conn.Open ;
  Display('删除在SQL Server上的临时数据...');
    DeleteRptSqlserver;
  Display('在SQL Server上产生新报表...');
    CreateRptSqlserver(RzDateTimeEdit1.Date);
  Display('删除在Oracle上的原有数据...');
  try
  conn.Open;
  conn.StartTransaction;
    DeleteOra(RzDateTimeEdit1.Date) ;
  Display('从SQL Server上将报表导入到Oracle...');
    LoadToOra;
  conn.Commit;
  Display('成功导入');
  conn.Close;
  except
    on E: Exception do begin
      Display(E.Message);
    conn.Rollback;
    end;
  end;
  ss_conn.Close;
  except
    on E: Exception do begin
     Display(E.Message);
    end;
  end;
end;
procedure TForm1.DeleteRptSqlserver;
begin
    with CreateRpt do begin
     CommandText:='delete from t_rpt_count';
     Execute;
    end;
end;
procedure TForm1.CreateRptSqlserver(rptdate:TDateTime);
var sqlstr:string;
begin
    sqlstr:='INSERT INTO t_rpt_count (sendtime, callingnumber, sendcount) ';
    sqlstr:=sqlstr+'SELECT CONVERT(char(10), send_time, 20) AS sendtime, orig_mp AS callingnumber, COUNT(*) AS sendcount ';
    sqlstr:=sqlstr+'FROM GSM'+formatDateTime('yyyymmdd',rptdate)+' WHERE (status = 1) AND (orig_mp LIKE ''130%'' OR orig_mp LIKE ''131%'' OR orig_mp LIKE ''132%'') AND (dest_mp LIKE ''13%'')';    // and (sm_type = 1 OR sm_type = 2 OR sm_type = 3) modify by cs 2005.1.26
    sqlstr:=sqlstr+' GROUP BY CONVERT(char(10), send_time, 20), orig_mp ' ;
    with CreateRpt do begin
     CommandText:=sqlstr;
     Execute;
    end;
end;
procedure TForm1.LoadToOra;
var phonenum:string;
    sendtime:TDateTime;
    sendcount,count:Integer;
begin
  with queryrpt do begin
    Open;
    while not Eof do begin
    phonenum:=Fields.FindField('callingnumber').Value;
    sendtime:=Fields.FindField('sendtime').Value;
    sendcount:=Fields.FindField('sendcount').Value;
      instora(phonenum,sendtime,sendcount);
    next;
    inc(count);
    end;
    Display('导入条数:'+IntToStr(count));
    if Active then Close;
  end;
end;
procedure TForm1.DeleteOra(rptdate:TDateTime);
begin
  with DelOra  do begin
    params.ParamByName('rptdate').value:=rptdate;
    Execute;
  end;
end;
procedure TForm1.instora(v_phonenum:String;v_sendtime:TDateTime;v_sendcount:Integer);
begin
 with insertora do begin
    params.ParamByName('phonenum').Value:= v_phonenum;
    params.ParamByName('sendtime').Value:=v_sendtime;
    params.ParamByName('sendcount').Value:= v_sendcount;
    Execute;
 end;
end;
procedure TForm1.RzButton3Click(Sender: TObject);
begin
  DoSync;
end;

end.