unitdm.pas

串口短信猫收发软件。支持西门子TC35i设备

{*******************************************************}
{                                                       }
{  名称：TDM类                                          }
{  功能：                                               }
{      1.数据库管理                                     }
{  调用：                                               }
{                                                       }
{                                                       }
{*******************************************************}
{********************************************************
drop table Tab_basepara;
drop table Tab_request;
drop table Tab_response;
drop table Tab_addrgroup;
drop table Tab_addr;
drop table Tab_textgroup;
drop table Tab_text;
drop table Tab_phoneplace;
drop table Tab_autoreply;

create table Tab_basepara (
para_name       varchar(20)     not null,
para_value      varchar(200)    not null,
constraint PK_base_id primary key  (para_name)
);
insert into Tab_basepara(para_name, para_value)values('limitfee', '0');
insert into Tab_basepara(para_name, para_value)values('autostopoverfee', '0');
insert into Tab_basepara(para_name, para_value)values('feebalance', '0.1');
insert into Tab_basepara(para_name, para_value)values('unicomlocalnetdefine', '130*;131*;132*;133*;');
insert into Tab_basepara(para_name, para_value)values('mobilelocalnetdefine', '134*;135*;136*;137*;138*;139*;');
insert into Tab_basepara(para_name, para_value)values('localnetfee', '0.1');
insert into Tab_basepara(para_name, para_value)values('remotenetfee', '0.15');
insert into Tab_basepara(para_name, para_value)values('autoreply', '0');

create table Tab_autoreply (
reci_prefix     varchar(20)     not null,
send_prefix     varchar(255)    null,
send_sql        varchar(255)    null,
send_params     varchar(255)    null,
send_suffix     varchar(255)    null,
remark          varchar(255)    null,
constraint PK_reci_prefix primary key  (reci_prefix)
);

create table Tab_request (
req_id          Counter,
req_time 	varchar(20)	not null,
send_mobile 	varchar(20) 	not null,
send_data 	varchar(140) 	not null,
localnet        int             null,
send_result 	int 		not null,
send_num        int             not null,
send_time 	varchar(20) 	null,
send_remark 	varchar(100) 	null,
constraint PK_req_id primary key  (req_id)
);
create index Idx_req_time on Tab_request (
req_time
);
create index Idx_send_time on Tab_request (
send_time
);

create table Tab_response (
res_id          Counter,
res_time 	varchar(20)	not null,
reci_mobile 	varchar(20) 	not null,
reci_data 	varchar(140) 	not null,
reci_time 	varchar(20) 	not null,
do_result       int             not null,
constraint PK_res_id primary key  (res_id)
);
create index Idx_reci_time on Tab_response (
reci_time
);

create table Tab_addrgroup (
addrgroup_id      Counter,
addrgroup_name 	  varchar(100)	not null,
constraint PK_addrgroup_id primary key  (addrgroup_id)
);

create table Tab_addr (
addr_id           Counter,
addrgroup_id      varchar(20)	not null,
addr_name 	  varchar(100)	not null,
addr_tel          varchar(20)	not null,
addr_remark       varchar(200),
constraint PK_addr_id primary key  (addr_id)
);
create index Idx_addrgroup_id on Tab_addr (
addrgroup_id
);

create table Tab_textgroup (
textgroup_id      Counter,
textgroup_name 	  varchar(100)	not null,
constraint PK_textgroup_id primary key  (textgroup_id)
);

create table Tab_text (
text_id           Counter,
textgroup_id      varchar(20)	not null,
text_name         varchar(200)	not null,
constraint PK_text_id primary key  (text_id)
);
create index Idx_textgroup_id on Tab_text (
textgroup_id
);
create table Tab_phoneplace(
phone_id          Counter,
phone_prefix 	varchar(20) 	 not null,
phone_city    varchar(20)  null,
phone_area    varchar(20)  null,
phone_type    varchar(100) null,
phone_region  varchar(20)  null,
constraint PK_phone_id primary key  (phone_id)
);
create unique index Idx_phone_prefix on Tab_phoneplace (
phone_prefix
);
create index Idx_phone_region on Tab_phoneplace (
phone_region
);

*******************************************************}

unit UnitDM;

{$DEFINE DATABASE_ACCESS2000}

interface

uses
  Windows, SysUtils, Classes, DB, ADODB, Registry, Forms, Math, UnitPublicFun;

type
  TDM = class(TDataModule)
    Con: TADOConnection;
    DSet_Request: TADODataSet;
    DSrc_Request: TDataSource;
    DSrc_Response: TDataSource;
    DSet_Response: TADODataSet;
    DQ: TADOQuery;
    DSet_Requestreq_time: TWideStringField;
    DSet_Requestsend_mobile: TWideStringField;
    DSet_Requestsend_data: TWideStringField;
    DSet_Requestsend_result: TIntegerField;
    DSet_Requestsend_time: TWideStringField;
    DSet_Requestreq_id: TAutoIncField;
    DSet_Requestresult: TStringField;
    DSet_Requestsend_num: TIntegerField;
    DSet_AddrGroup: TADODataSet;
    DSet_Addr: TADODataSet;
    DSet_TextGroup: TADODataSet;
    DSet_Text: TADODataSet;
    DSrc_AddrGroup: TDataSource;
    DSrc_Addr: TDataSource;
    DSrc_TextGroup: TDataSource;
    DSrc_Text: TDataSource;
    DSet_AddrGroupaddrgroup_id: TAutoIncField;
    DSet_AddrGroupaddrgroup_name: TWideStringField;
    DSet_TextGrouptextgroup_id: TAutoIncField;
    DSet_TextGrouptextgroup_name: TWideStringField;
    DSet_Addraddr_id: TAutoIncField;
    DSet_Addraddrgroup_id: TWideStringField;
    DSet_Addraddr_name: TWideStringField;
    DSet_Addraddr_tel: TWideStringField;
    DSet_Addraddr_remark: TWideStringField;
    DSet_Texttext_id: TAutoIncField;
    DSet_Texttextgroup_id: TWideStringField;
    DSet_Texttext_name: TWideStringField;
    DSet_Responseres_id: TAutoIncField;
    DSet_Responseres_time: TWideStringField;
    DSet_Responsereci_mobile: TWideStringField;
    DSet_Responsereci_data: TWideStringField;
    DSet_Responsereci_time: TWideStringField;
    DSet_Requestsend_remark: TWideStringField;
    DSrc_RequestFee: TDataSource;
    DSet_RequestFee: TADODataSet;
    WideStringField1: TWideStringField;
    WideStringField2: TWideStringField;
    WideStringField3: TWideStringField;
    WideStringField4: TWideStringField;
    IntegerField1: TIntegerField;
    IntegerField2: TIntegerField;
    AutoIncField1: TAutoIncField;
    StringField1: TStringField;
    WideStringField5: TWideStringField;
    DSet_Requestlocalnet: TIntegerField;
    DSet_RequestFeelocalnet: TIntegerField;
    DSet_RequestFeenet: TStringField;
    DSet_RequestFeefee: TFloatField;
    DSet_Responsedo_result: TIntegerField;
    DSrc_AutoReply: TDataSource;
    DSet_AutoReply: TADODataSet;
    DSet_AutoReplyreci_prefix: TWideStringField;
    DSet_AutoReplysend_prefix: TWideStringField;
    DSet_AutoReplysend_sql: TWideStringField;
    DSet_AutoReplysend_params: TWideStringField;
    DSet_AutoReplysend_suffix: TWideStringField;
    DSet_AutoReplyremark: TWideStringField;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
    procedure DSet_RequestCalcFields(DataSet: TDataSet);
    procedure DSet_RequestNewRecord(DataSet: TDataSet);
    procedure DSet_AddrNewRecord(DataSet: TDataSet);
    procedure DSet_TextNewRecord(DataSet: TDataSet);
    procedure DSet_AddrGroupBeforePost(DataSet: TDataSet);
    procedure DSet_TextGroupBeforePost(DataSet: TDataSet);
    procedure DSet_AddrBeforePost(DataSet: TDataSet);
    procedure DSet_TextBeforePost(DataSet: TDataSet);
    procedure DSet_RequestFeeCalcFields(DataSet: TDataSet);
  private
    procedure AutoRegDSN;                                                       //自动注册ODBC数据源
    procedure LoadBasePara;                                                     //读入基本参数
    function GetParamNum(flag: string; const data: string): integer;            //获取参数数量
    function GetIndexParam(index: integer; flag: string; const data: string): string;//获取第index个参数    
  public
    FbAutoReply: boolean;
    FbLimitFee, FbAutoStopOverFee: boolean;                                     //费用控制开关(0-不限额,1-限额)、自动停止超额短信发送
    FUnicomLocalNetDefine, FMobileLocalNetDefine, FLocalNetFee, FRemoteNetFee: string;//联通网内标识、移动网内标识、网内费率、网外费率
    function AddRequest(req_time, send_mobile, send_data: string): boolean;
    function AddAutoReply(req_time, send_mobile, send_data: string): boolean;
    function QueryRequest(var MsgNum: integer; var MsgArray: array of TRecieveSMS): boolean;
    function UpdateRequest(req_id, send_mobile, send_result, send_time, netdefine: string): boolean;
    function AddResponse(res_time, reci_mobile, reci_data, reci_time: string): boolean;
    function AddAddrGroup(addrgroup_name: string): boolean;
    function UpdateAddrGroup(addrgroup_id, addrgroup_name: string): boolean;
    function DelAddrGroup(addrgroup_id: string): boolean;
    function AddTextGroup(textgroup_name: string): boolean;
    function UpdateTextGroup(textgroup_id, textgroup_name: string): boolean;
    function DelTextGroup(textgroup_id: string): boolean;
    function SelectAddrGroup(var ts: TStringList): boolean;
    function SelectAddr(var ts: TStringList): boolean;
    function SelectTextGroup(var ts: TStringList): boolean;
    function SelectText(var ts: TStringList): boolean;
    function QueryCount(dtStart, dtEnd: string; var RequestTotNum, RequestYesNum, RequestNoNum, ResponseTotNum: string): boolean;
    function QueryCountChart(dtStart, dtEnd: string;  var ReqTot: integer; var ResTot: integer; var arRequest: array of TQueryCountChart; var arResponse: array of TQueryCountChart): boolean;
    function HasFee: boolean;
    function UpdateRequestOverFee: boolean;
    function GetSMSFee(send_mobile, netdefine: string): real;
    function QueryFeeBalance: string;
    function DelRequestAndResponse(dtStart, dtEnd: string): boolean;
    function GetLocalNetDefine(mobile, netdefine: string): integer;
    function GetPhonePlace(phone_prefix: string; var phone_city, phone_area, phone_type, phone_region: string): boolean;
    function SumFee(var LocalNum, LocalFee, RemoteNum, RemoteFee: real): boolean;    
    function UpdateFeeCtrlBasePara(bLimitFee, bAutoStopOverFee: boolean; FeeBalance, UnicomLocalNetDefine, MobileLocalNetDefine, LocalNetFee, RemoteNetFee: string): boolean;
    function UpdateAutoReplyBasePara(bAutoReply: boolean): boolean;
    function UpdatePhonePlace(phone_prefix, phone_city, phone_area, phone_type, phone_region: string): boolean;
    procedure SelectRequest(dtStart, dtEnd: string; send_result: integer);
    procedure SelectResponse(dtStart, dtEnd: string);
    procedure SelectRequestFee(dtStart, dtEnd: string; send_result, localnet: integer);
  published
    property UnicomTelPreFix: string read FUnicomLocalNetDefine;//联通电话前缀
    property MobileTelPreFix: string read FMobileLocalNetDefine;//移动电话前缀
  end;

var
  DM: TDM;

implementation

{$R *.dfm}

procedure TDM.DataModuleCreate(Sender: TObject);
var
  ConnctStr: string;
begin
  AutoRegDSN;
  //初始化连接字符串
  ConnctStr := 'Provider=MSDASQL.1;'
              +'Password='
              +DATABASE_PASSWORD
              +';Persist Security Info=True;User ID=Admin;Extended Properties="DSN='
              +ODBC_DSN_NAME
              +';DBQ='
              +ExtractFilePath(Application.ExeName) + DATABASE_NAME
              +';DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD='
              +DATABASE_PASSWORD
              +';UID=admin;"';
  try
    if not FileExists(ExtractFilePath(Application.ExeName) + DATABASE_NAME) then
    begin
      Application.MessageBox(PChar(ExtractFilePath(Application.ExeName) + DATABASE_NAME + ' 文件丢失，系统退出！'), '提示', MB_OK or MB_ICONERROR);
      Application.Terminate;
    end;
    DSet_Request.Close;
    DSet_RequestFee.Close;    
    DSet_Response.Close;
    DSet_AddrGroup.Close;
    DSet_Addr.Close;
    DSet_TextGroup.Close;
    DSet_Text.Close;
    DQ.Close;    
    Con.Close;
    Con.ConnectionString := ConnctStr;
    Con.Open;
    LoadBasePara;                                                               //读入基本参数
  except
    Application.MessageBox('数据库出错，请联系系统管理员!', '提示', MB_OK or MB_ICONERROR);
    Application.Terminate;
  end;
end;

procedure TDM.DataModuleDestroy(Sender: TObject);
var
  strDBPATH, strBAKDBPATH: string;
begin
  //关闭数据库
  DSet_Request.Close;
  DSet_RequestFee.Close;
  DSet_Response.Close;
  DSet_AddrGroup.Close;
  DSet_Addr.Close;
  DSet_TextGroup.Close;
  DSet_Text.Close;
  DQ.Close;
  Con.Close;
  //备份文件
  strDBPATH := ExtractFilePath(Application.ExeName)+ DATABASE_NAME;
  strBAKDBPATH := ExtractFilePath(Application.ExeName)+ BAK_DATABASE_NAME;
  CopyFile(PChar(strDBPATH), PChar(strBAKDBPATH), False);
end;

procedure TDM.AutoRegDSN;
var
  registerTemp: TRegistry;
  bData: array[0..0]of byte;
  SysDir: array[0..255] of char;
begin
  try
    registerTemp := TRegistry.Create;
    registerTemp.RootKey := HKEY_LOCAL_MACHINE;

    if not registerTemp.OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources', True) then Exit;
    registerTemp.WriteString(ODBC_DSN_NAME, 'Microsoft Access Driver (*.mdb)'); //数据源名称和数据库类型
    registerTemp.CloseKey;

    if not registerTemp.OpenKey('Software\ODBC\ODBC.INI\'+ODBC_DSN_NAME, True) then Exit;
    registerTemp.WriteString('DBQ', ExtractFilePath(Application.ExeName) + DATABASE_NAME);//数据库路径
    registerTemp.WriteString('Description', '短信网关系统(CrystalSMS GateWay)数据源');    //数据库描述
    GetSystemDirectory(SysDir, sizeof(SysDir)-1);
    if not FileExists(SysDir + '\odbcjt32.dll') then
    begin
      registerTemp.CloseKey;    
      Application.MessageBox(PChar('数据源驱动程序 '+SysDir + '\odbcjt32.dll'+' 没有安装'), '错误', MB_OK);
      Exit;
    end;    
    registerTemp.WriteString('Driver', SysDir + '\odbcjt32.dll');               //驱动程序，可见ODBCINST.INI
    registerTemp.WriteInteger('DriverId', 25 );                                 //驱动程序标识，0x00000019(25)数字，表示驱动程序标识，不能改变
    registerTemp.WriteString('FIL', 'Ms Access;');                              //Filter依据
    registerTemp.WriteInteger('SafeTransaction', 0 );                           //支持的事务操作数目
    registerTemp.WriteString('UID', '');                                        //用户名称
    bData[0] := 0;
    registerTemp.WriteBinaryData('Exclusive', bData, 1);                        //非独占方式
    registerTemp.WriteBinaryData('ReadOnly', bData, 1);                         //非只读方式
    registerTemp.CloseKey;

    if not registerTemp.OpenKey('Software\ODBC\ODBC.INI\'+ODBC_DSN_NAME+'\Engines\Jet', True) then Exit;
    registerTemp.WriteString('ImplicitCommitSync', 'Yes');                      //是否立即反映数据修改
    registerTemp.WriteInteger('MaxBufferSize', 512 );                           //缓冲区大小
    registerTemp.WriteInteger('PageTimeout', 10 );                              //页超时
    registerTemp.WriteInteger('Threads', 3 );                                   //支持的线程数目
    registerTemp.WriteString('UserCommitSync', 'Yes');                          //是否立即将数据修改反映到用户
    registerTemp.CloseKey;
  finally
    registerTemp.CloseKey;
    registerTemp.Free;
  end;
end;

procedure TDM.LoadBasePara;
var
  sql: string;
begin
  sql := ' select * from Tab_basepara ';
  FUnicomLocalNetDefine := '';
  FMobileLocalNetDefine := '';
  FLocalNetFee := '0.1';
  FRemoteNetFee := '0.15';
  FbLimitFee := False;
  FbAutoStopOverFee := False;
  FbAutoReply := False;
  try
    DQ.Close;
    DQ.SQL.Text := sql;
    DQ.Open;
    DQ.First;
    while not DQ.Eof do
    begin
      if DQ.FieldByName('para_name').AsString='unicomlocalnetdefine' then
        FUnicomLocalNetDefine := DQ.FieldByName('para_value').AsString
      else if DQ.FieldByName('para_name').AsString='mobilelocalnetdefine' then
        FMobileLocalNetDefine := DQ.FieldByName('para_value').AsString
      else if DQ.FieldByName('para_name').AsString='localnetfee' then
        FLocalNetFee := DQ.FieldByName('para_value').AsString
      else if DQ.FieldByName('para_name').AsString='remotenetfee' then
        FRemoteNetFee := DQ.FieldByName('para_value').AsString