核实某公交公司免费和优惠公交卡 应补贴金额审计事项案例
一、项目背景
T市某公交公司于2011年启用IC卡收费系统。根据国家、省相关文件精神和C市人民政府文件《关于优先发展城市公共交通的若干意见》，T市人民政府每年按免费人群（70周岁以上老人）0.80元每次，学生0.30元每次的标准对公交公司予以补贴。根据T市政府第19次常务会议纪要（2014年9月24日），对户籍在本市，持有第二代《中华人民共和国残疾人证》的肢体、视力二级以上70周岁以下的残疾人，可以免费乘坐市区公交车。对公交公司按照老年卡结算方式予以补贴。2016年初，公交公司按惯例申请财政补贴，市政府将此项目交由市审计局核实。
二、审计范围和内容
（一）时间范围：2015年度
（二）对象范围：T市某公交公司
（三）主要内容：核实2015年该公司老年卡、学生卡、爱心卡刷卡次数，以此为基础计算应获取市政府财政补贴金额。
三、审计步骤和方法
（一）采集数据
1.公交公司使用的公交IC卡收费刷卡管理系统，后台为SQL2000数据库。因初次要求提供的数据库备份文件数据量较大，还原未成功。故要求系统管理人员将2015年老年卡、学生卡、爱心卡刷卡明细表及学生卡年审表、售卡明细表单独导出为新数据库，提供给审计组，包括两个数据文件：tk_center2015_Data.MDF和tk_center2015_Log.LDF
主要数据表有：
TK_CARD_TRADELNK 老年卡刷卡明细
TK_CARD_TRADEXSK 学生卡刷卡明细
TK_CARD_TRADEXSK 爱心卡刷卡明细
ZY_CARD_SELL 售卡明细
ZY_CARD_YEARCHECK 年审明细
各表主要字段内容如下：
card_no卡编号
ss_times充值次数
card_serial卡号
card_type卡类型（2为老年卡，3为学生卡，6为爱心卡）
riding_time乘车时间（刷卡时间）
paid消费金额
card_money卡内剩余金额
intt_city_code城市编号
group_no线路号
bus_no车辆号
collect_time数据采集日期
id_no 身份证号码
2.T市残联提供的“残疾人免费乘车爱心卡”办理情况登记表为EXCEL表格，主要内容有：
姓名，性别，年龄，残疾类别，残疾人证号，住址，电话，办理时间
（二）导入数据
1.附加所采集的公交公司数据库，数据库名称为[tk_center2015]。
2.将残联提供的电子表格导入到附加后的[tk_center2015]数据库中。
（三）审计思路：审计所需核实的为公交公司老年卡、学生卡、爱心卡刷卡次数，该公司已分别提供3种公交卡2015年刷卡明细，每条记录即一次刷卡记录。审计人员除了验证所提供数据是否为2015年度数据，有无人为增加记录等事项外，最重要的是根据售卡明细表中的办理人身份证号码，查找不符合学生卡、老年卡的人员信息；根据残联提供的资料，核对不符合爱心卡办理条件的人员信息。在此基础上查询这些不符合的卡号在2015年刷卡次数，从查询的总次数中扣减，分别计算出财政应补贴金额。
（四）分析数据
/***************学生卡***************/
--查询学生卡刷卡记录中有无超出年范围的
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where year(riding_time)<>2015
--查询有无同一张学生卡刷卡时间相同的
SELECT distinct [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
group by [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
having count([RIDING_TIME])>1
--查询学生卡年度刷卡次数
SELECT count(*)
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [PAID]=0.5
--查询身份证号为位的学生卡，已满周岁即年之前出生的人员年刷卡次数（对于高中已毕业的人员年龄只能为估算）
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] in
(SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)<'1995'
and CARD_TYPE=3 and len(ID_NO)=18)
and [PAID]=0.5
--查询已满周岁的人员且在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in(
SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [PAID]=0.5
and [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)<'1995'
and CARD_TYPE=3 and len(ID_NO)=18))
--查询身份证号为位的学生卡，已满周岁即年之前出生的人员年刷卡次数（无结果）
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [PAID]=0.5
and [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where '19'+substring(ID_NO,7,2)<'1995'
and CARD_TYPE=3 and len(ID_NO)=15 )
--查询身份证号非位也非位,或者为空白或空值的学生卡于年使用的次数
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [PAID]=0.5
and [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=3
and (len(ID_NO)<>18 and len(ID_NO)<>15) or ID_NO='' or ID_NO is null)
--查询上述学生卡在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in
(SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [PAID]=0.5
and [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=3
and (len(ID_NO)<>18 and len(ID_NO)<>15) or ID_NO='' or ID_NO is null))
--查询身份证号为位的学生卡，未满周岁即年之前出生的人员年使用次数（公交公司规定只要满足身高条件，不管年龄多小，都得办卡，因此将下限年龄放宽到周岁。查询结果基本为身份证号输入有误。）
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)>'2012'
and CARD_TYPE=3 and len(ID_NO)=18)
and [PAID]=0.5
--查询上述学生卡在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in
( SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)>'2012'
and CARD_TYPE=3 and len(ID_NO)=18)
and [PAID]=0.5)
--查询年月日之前办卡，年月日之前未年审，月日之后仍能刷卡的人员具体信息（公交公司将每年的月日设为学生卡年审截止日期）
SELECT *
into 未年审学生卡月日之后
FROM dbo.ZY_CARD_SELL
where CARD_NO in
(SELECT distinct [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] not in(
SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[ZY_CARD_YEARCHECK]
where CARD_TYPE='3' )
and riding_time>='2015-10-01'
and [PAID]=0.5)
and SELL_TIME<'2015-01-01'
--查询上述学生卡在年月日之后刷卡次数
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] in
(SELECT [CARD_NO] from 未年审学生卡月日之后)
and riding_time>='2015-10-01'
and [PAID]=0.5
--查询身份证为位但日期不规范的学生卡年刷卡次数（须排除与其他几种情况重复的卡号）
SELECT *
into 身份证日期不规范的学生卡位不含其他情况次数
FROM [tk_center2015].[dbo].[TK_CARD_TRADEXSK]
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL where
isdate(substring(cast(ID_NO as varchar),7,8))=0
and len(ID_NO)=18 and CARD_TYPE=3
and [CARD_NO] not in
(SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)<'1995'
and CARD_TYPE=3 and len(ID_NO)=18 )
and [CARD_NO] not in
(SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=3
and (len(ID_NO)<>18 and len(ID_NO)<>15) or ID_NO='' or ID_NO is null)
and [CARD_NO] not in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL where substring(ID_NO,7,4)>'2012'
and CARD_TYPE=3 and len(ID_NO)=18))
and CARD_NO not in
(SELECT [CARD_NO] from 未年审学生卡月日之后)
and [PAID]=0.5
--查询上述学生卡人员具体信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in(
SELECT distinct [CARD_NO]
FROM 身份证日期不规范的学生卡位不含其他情况次数)
/***************老年卡***************/
--查询老年卡刷卡记录中有无超出年范围的
SELECT *
FROM [tk_center2015].[dbo].TK_CARD_TRADELNK
where year(riding_time)<>2015
--查询有无同一张老年卡刷卡时间相同的（无结果）
SELECT distinct [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
FROM [tk_center2015].dbo.TK_CARD_TRADELNK
group by [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
having count([RIDING_TIME])>1
--查询老年卡年度刷卡总次数
SELECT count(*)
FROM dbo.TK_CARD_TRADELNK
--查询身份证号为位的老年卡，未满周岁即年之后出生的人员年刷卡次数
SELECT *
FROM [tk_center2015].[dbo].TK_CARD_TRADELNK
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL
where substring(ID_NO,7,4)>'1945'
and CARD_TYPE=2 and len(ID_NO)=18 )
--查询上述老年卡在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in
( SELECT [CARD_NO]
FROM [tk_center2015].[dbo].TK_CARD_TRADELNK
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL
where substring(ID_NO,7,4)>'1945'
and CARD_TYPE=2 and len(ID_NO)=18 ) )
--查询身份证号为位的老年卡，未满周岁即年之后出生的人员年刷卡次数（无结果）
SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL
where '19'+substring(ID_NO,7,2)>'1945'
and CARD_TYPE=2 and len(ID_NO)=15
--查询身份证号非位也非位,或者为空白或空值的老年卡于年使用的次数
SELECT *
FROM [tk_center2015].[dbo].TK_CARD_TRADELNK
where [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=2
and len(ID_NO)<>18 and len(ID_NO)<>15 or ID_NO='' or ID_NO is null)
--查询上述老年卡在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in
(SELECT [CARD_NO]
FROM [tk_center2015].[dbo].TK_CARD_TRADELNK
where [CARD_NO] in(
SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=2
and len(ID_NO)<>18 and len(ID_NO)<>15 or ID_NO='' or ID_NO is null))
--查询身份证为位但日期不规范的老年卡年刷卡次数（须排除与其他几种情况重复的卡号）
SELECT *
into 身份证日期不规范的老年卡位不含其他情况次数
FROM [tk_center2015].[dbo].[TK_CARD_TRADELNK]
where [CARD_NO] in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL where
isdate(substring(cast(ID_NO as varchar),7,8))=0
and len(ID_NO)=18 and CARD_TYPE=2)
and [CARD_NO] not in
(SELECT CARD_NO
FROM dbo.ZY_CARD_SELL where CARD_TYPE=2
and len(ID_NO)<>18 and len(ID_NO)<>15 or ID_NO='' or ID_NO is null)
and [CARD_NO] not in
(SELECT [CARD_NO]
FROM dbo.ZY_CARD_SELL
where substring(ID_NO,7,4)>'1945'
and CARD_TYPE=2 and len(ID_NO)=18)
--查询上述老年卡在年有刷卡记录的具体人员信息
SELECT *
FROM dbo.ZY_CARD_SELL where
[CARD_NO] in(
SELECT distinct [CARD_NO]
FROM 身份证日期不规范的老年卡位不含其他情况次数)
--查询年月日之前出售的，年月日之前未年审但月日之后仍在使用的老年卡（公交公司考虑到老年人出行方便，将每年的月日设为老年卡年审最后截止日期）
SELECT * from
dbo.ZY_CARD_SELL
where [CARD_NO] in(
SELECT distinct [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADELNK]
where [CARD_NO] not in(
SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[ZY_CARD_YEARCHECK]
where CARD_TYPE='2' )
and riding_time>='2015-05-01')
and SELL_TIME<'2015-01-01'
--查询上述老年卡年月日之后刷卡次数
SELECT * from dbo.TK_CARD_TRADELNK
where [CARD_NO] in(
SELECT [CARD_NO] from
dbo.ZY_CARD_SELL
where [CARD_NO] in(
SELECT distinct [CARD_NO]
FROM [tk_center2015].[dbo].[TK_CARD_TRADELNK]
where [CARD_NO] not in(
SELECT [CARD_NO]
FROM [tk_center2015].[dbo].[ZY_CARD_YEARCHECK]
where CARD_TYPE='2' )
and riding_time>='2015-05-01')
and SELL_TIME<'2015-01-01')
and riding_time>='2015-05-01'
/***************爱心卡***************/
--查询爱心卡有无在年月日政府会议纪要决定之前办理的（无结果）
SELECT *
FROM dbo.ZY_CARD_SELL
where
CARD_TYPE=6 and SELL_TIME<='2014-10-07'
--查询爱心卡刷卡记录中有无超出年范围的
SELECT *
FROM [tk_center2015].[dbo].[TK_CARD_TRADEAXK]
where year(riding_time)<>2015
--查询爱心卡年度刷卡总次数
SELECT count(*)
FROM
dbo.TK_CARD_TRADEAXK
--查询有无同一张爱心卡刷卡时间相同的
SELECT distinct [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
FROM [tk_center2015].dbo.TK_CARD_TRADEAXK
group by [CARD_NO],[CARD_SERIAL],[RIDING_TIME]
having count([RIDING_TIME])>1
--将市残联备案的办卡登记表中残疾证号码减去后两位即得出办卡人身份证号
alter table dbo.残疾人办卡名单 add 身份证号 varchar(18)
update dbo.残疾人办卡名单 set 身份证号=left(残疾人证号,18)
--根据姓名与身份证号查询办理爱心卡人员名单不在残疾人备案名单中的具体信息
select * from dbo.ZY_CARD_SELL a
left join dbo.残疾人办卡名单 b
on a.CARD_NAME=b.姓名
and a.ID_NO=b.身份证号
where a.CARD_TYPE='6'
and b.序号 is null
--查询上述爱心卡年刷卡次数(须向公交公司了解，排除因非机器读卡，由人工输入造成的姓名或身份证号错误情况)
select * from dbo.TK_CARD_TRADEAXK
where CARD_NO in
(select [CARD_NO] from dbo.ZY_CARD_SELL a
left join dbo.残疾人办卡名单 b
on a.CARD_NAME=b.姓名
and a.ID_NO=b.身份证号
where a.CARD_TYPE='6'
and b.序号 is null)
