|
一、引言
在通信计费工作中，数据具有时效性，一些数据保存时间越长，可利用价值越小。当利用价值小于保存价值的时候，就需要定期、不定期地把一些保存的数据删除，以便腾出空间，存放其他数据。人工完成涉及表目太多，过程繁琐，这个时候可以进行激活程序进行自动删除操作。自动归档即对数据进行整理分类，自动删除无效数据，把其中的有效数据进行针对性保存。
下面介绍一种利用SQL语言编写程序实现计费过期数据自动清除的方法。
二、电信业务综合管理系统简介
电信业务综合管理系统为程控电话交换机提供配套业务管理服务，此服务平台上有：计费采集和话单分拣系统、计费管理系统、话费营收系统、112故障申告、112全自动测试、号线资料管理系统、改号通知系统、114号码查询系统、170自动话费查询系统、语音话费催缴系统、自动停复机系统、电信生产调度系统等子系统。所有的这些系统从同一个数据库内调用数据。电信业务综合管理系统结构如图１所示。
图1 电信业务综合管理系统结构图
电信业务管理系统后台数据库部分采用MS-SQL Server 2000开发。SQL Server 2000 是基于结构化查询语言的可伸缩的关系数据库。SQL 语言是一种处理关系数据库的语言，使用一套数据库管理系统定义的命令和语句来处理数据库中的数据。
计费管理子系统中的自动归档程序就是使用SQL语言编写的一个存储过程，是电信业务综合管理系统的一部分。目的是将不在保存时间内的话单和汇总数据从数据库内各个相关联的表中高效规范地自动归档删除，从而释放数据所占用的空间。
在数据库中，自动归档所涉及到的表包括：电话汇总表、合同号汇总表、用户信息历史档案表、收费汇总表（将收费数据作备份至收费汇总历史表中）、电子工单、催缴日志、调级日志、操作日志。
打开企业管理器下数据库中表，如图２所示。
图2 SQL Server 2000企业管理器中数据库下存储过程
通过调用这一存储过程来满足我们清除旧数据的要求，后台存储数据库程序，前台激活存储过程，提供人性化的界面，数据是实时产生的。自动归档相关存储过程执行频率由作业调度来进行人为控制。
策略上，过期数据自动清除实现的方式是：对于系统的过期数据，可以在系统参数中设置保留的时间。具体清除时不真正删除，而只是将不在保存时间内的话单和汇总数据从计费数据库内相关联的各个表中自动转移到数据仓库中，以释放表的空间资源。这样，既不影响数据处理的速度，也不真正删除。当然，也可以选择真正删除过期数据。
过期数据自动清除流程如图３所示。
图3 过期数据自动清除流程分析图
三、参数说明
1．数据项
该表的用户信息中的字段信息与用户档案信息对应
表1 数据项字段名、类型及描述
|
功能 |
存储用户信息 |
|
字段中文名称 |
字段名称 |
类型 |
字段描述 |
|
汇总月份 |
vcPeriod |
varchar |
汇总时，这些属性都和表Jfgl_UserInfo中一一对应。 |
|
帐务类型 |
vcAccType |
varchar |
|
合同号 |
vcAccNo |
varchar |
|
电话 |
vcTel |
varchar |
|
应收款 |
mUnReceive |
Money |
该月欠费金额 |
|
是否交费 |
bPay |
bit |
0：未交费 1：交费 |
|
拆机日期 |
dtDel |
datetime |
|
|
催缴时间 |
dtCallTime |
datetime |
|
|
注册日期 |
datRegDate |
datetime |
|
|
是否删除用户 |
bdelUser |
bit |
0：已删除 1：未删除 |
2．数据库结构
数据库结构用若干个表进行描述，相关列表如表２所示。
表2 数据表名称列表
|
表名称 |
表描述 |
|
Hfys_ChargeStat_DataBak |
收费汇总历史表 |
|
Hfys_ChangeStat |
收费汇总 |
|
Jfgl_TelStat |
电话汇总表 |
|
Jfgl_AccStat |
合同号汇总表 |
|
Jfgl_UserInfo_His |
用户信息历史档案表 |
|
Jfgl_custInfo |
客户档案 |
|
T170_CallLog |
170催缴日志 |
|
TOFM_OperateData |
业务受理表 |
|
Ctrl_TelHistory |
电话调级历史档案 |
|
PlatOperInfo |
操作日志 |
|
PlatloginInfo |
登录日志 |
3．操作权限
删除符合条件范围的记录和日志。
四、编码实现
创建数据库的一个SQL脚本，用SQL Server的查询分析器执行代码。
其存储过程 Jfgl_Sp_AutoDelData的主要SQL语句如下所示：
CREATE PROCEDURE Jfgl_Sp_AutoDelData
AS
//1－－声明变量
DECLARE @vcTemp VARCHAR(20)
DECLARE @vcSql VARCHAR(1000)
DECLARE @nTemp INT
//2－－判断话单归档时间，按照明细话单保存时间删除不在保存时间内的话单
//――根据系统参数设置，判断明细话单的保留月数
IF EXISTS(SELECT * FROM smParam WHERE TSection='归档设置' AND TIdent='明细话单保留月数')
SELECT @nTemp=CAST(TValues AS INT) FROM smParam WHERE TSection='归档设置' AND TIdent='明细话单保留月数'
ELSE SET @nTemp=6
//――取当前日期并强制转化为我们需要的格式，
SET @vcTemp = CONVERT(CHAR(10),DATEADD(m,-@nTemp,GETDATE()),20)
SET @vcTemp = SUBSTRING(@vcTemp,1,4)+SUBSTRING(@vcTemp,6,2)
//声明变量
DECLARE @tblBill VARCHAR(16)
DECLARE Bill SCROLL CURSOR FOR
//从系统数据库里搜索满足条件的表名
SELECT name FROM sysobjects
WHERE objectproperty(id, N'IsUserTable') = 1
AND SUBSTRING(name,1,4) IN (SELECT vcPre FROM Jfgl_CallType)
AND LEN(name)=10
AND ISDATE(SUBSTRING(name,5,4)+'-'+SUBSTRING(name,9,2)+'-01')=1
AND SUBSTRING(name,5,6)<@vcTemp
//打开游标
OPEN Bill
//检索并将提取的数据放入变量
FETCH NEXT FROM Bill INTO @tblBill
WHILE @@FETCH_STATUS = 0
//删除表
BEGIN
SET @vcSql = 'DROP TABLE '+ @tblBill
EXEC(@vcSql)
FETCH NEXT FROM Bill INTO @tblBill
END
//关闭游标
CLOSE Bill
DEALLOCATE Bill
3----判断汇总数据保存时间，删除不在保存范围内的数据
//判断是否存在
IF EXISTS(SELECT * FROM smParam WHERE TSection='归档设置' AND TIdent='汇总话单保留月数')
//搜索月份并赋值变量
SELECT @nTemp=CAST(TValues AS INT) FROM smParam WHERE TSection='归档设置' AND TIdent='汇总话单保留月数'
ELSE SET @nTemp=6
//――取当前的最大帐务周期赋予变量@vcTemp
Select @vcTemp=max(vcPeriod) from Hfys_ChargeStat
SET @vcTemp =
CONVERT(CHAR(10),DATEADD(m,-@nTemp,convert(datetime,substring(@vcTemp,1,4)+'-'+substring(@vcTemp,5,2)+'-1')),20)
SET @vcTemp = SUBSTRING(@vcTemp,1,4)+SUBSTRING(@vcTemp,6,2)
//从电话汇总表中删除数据
DELETE FROM Jfgl_TelStat WHERE vcPeriod<=@vcTemp
//从合同号汇总表中删除数据
DELETE FROM Jfgl_AccStat WHERE vcPeriod<=@vcTemp
//从用户信息历史档案表中删除数据
DELETE FROM Jfgl_UserInfo_His WHERE vcPeriod<=@vcTemp
//从收费汇总表中将已交费、当前欠费值为0并打印过票据的数据作备份至收费汇总历史表中再将其删
//除――数据归档
Insert Hfys_ChargeStat_DataBak Select * from Hfys_ChargeStat WHERE vcPeriod<=@vcTemp AND bPay=1 AND mUnReceive=0 AND nPrinted>0
DELETE FROM Hfys_ChargeStat WHERE vcPeriod<=@vcTemp AND bPay=1 AND mUnReceive=0 AND nPrinted>0
//删除电子工单中不在保存时间范围内的数据
DELETE FROM TOFM_OperateData WHERE
CONVERT(CHAR(10),DATEADD(mm,@nTemp,datRegDate),20)<CONVERT(CHAR(10),GETDATE(),20)
4－－判断归档设置里面催缴日志保留天数，不在保留天数内的数据直接删除
IF EXISTS(SELECT * FROM smParam WHERE TSection='归档设置' AND TIdent='催缴日志保留天数')
//取日期
SELECT @nTemp=CAST(TValues AS INT) FROM smParam WHERE TSection='归档设置' AND TIdent='催缴日志保留天数'
ELSE SET @nTemp=60
//从170催缴日志中删除
DELETE FROM T170_CallLog WHERE
CONVERT(CHAR(10),DATEADD(DAY,@nTemp,dtCallTime),20)<CONVERT(CHAR(10),GETDATE(),20)
5－－判断归档设置里面调级日志保留天数，不在保留天数内的数据直接删除
IF EXISTS(SELECT * FROM smParam WHERE TSection='归档设置' AND TIdent='调级日志保留天数')
SELECT @nTemp=CAST(TValues AS INT) FROM smParam WHERE TSection='归档设置' AND TIdent='调级日志保留天数'
ELSE SET @nTemp=60
//从调级日志中删除
DELETE FROM Ctrl_TelHistory WHERE
CONVERT(CHAR(10),DATEADD(DAY,@nTemp,dtRegDate),20)<CONVERT(CHAR(10),GETDATE(),20)
6－－判断归档设置里面操作日志保留天生活，不在保留天数内的数据直接删除
IF EXISTS(SELECT * FROM smParam WHERE TSection='归档设置' AND TIdent='操作日志保留天数')
SELECT @nTemp=CAST(TValues AS INT) FROM smParam WHERE TSection='归档设置' AND TIdent='操作日志保留天数'
ELSE SET @nTemp=60
//从操作日志中删除
DELETE FROM PlatLoginInfo WHERE CONVERT(CHAR(10),DATEADD(DAY,@nTemp,LoginDate),20)
<CONVERT(CHAR(10),GETDATE(),20)
DELETE FROM PlatOperInfo WHERE CONVERT(CHAR(10),DATEADD(DAY,@nTemp,OperDate),20)
<CONVERT(CHAR(10),GETDATE(),20)
7 －－每月1日清除掉拆机用户档案
IF DAY(GETDATE())=1
BEGIN
//搜索拆机日期符合条件的电话
SELECT vcTel,dtDel Into #Tmp_DelUser FROM Jfgl_TelDelete WHERE bDelUser = 0 AND dtDel<GETDATE()
-- AND vcTel in (select b.vcTel from hfys_chargestat a, Jfgl_UserInfo b where a.vcPeriod=@vcPeriod and a.bPay=1 and a.vcAccNo=b.vcAccNo)
//从用户信息档案中将电话删除
DELETE FROM Jfgl_UserInfo WHERE vcTel IN (SELECT vcTel FROM #Tmp_DelUser)
//更新数据
UPDATE a Set bDelUser = 1 From Jfgl_TelDelete a, #Tmp_DelUser b WHERE a.vcTel =b.vcTel and a.dtDel=b.dtDel
END
GO
五、结语
本文介绍了一种在程控电话交换机业务管理中，利用SQL实现计费数据自动归档的方法，即对数据进行整理分类，把其中的有效数据进行针对性保存，将无效数据自动删除，从而达到释放过期数据所占用表空间的目的。此方法可以满足在数据库管理中删除过期数据的需要，所述代码均已在通信计费管理中运行通过。其设计思路具有一定的通用性，可以用于相关计费过期数据自动清除的程序开发中。
|
