安徽两种方法验证身份证号码合规性
——公交卡优惠乘车补贴核实审计运用实例
查看隐藏内容（*）需先登录
在对某市公交车优惠乘车补贴核实的审计过程中，审计人员发现，部分持卡人员预留身份证号不符合国家规定的第十八位验证码规则，即第十八位与前十七位的加权平均存在一一对应关系。此问题的存在可能是由于工作人员手工输入导致，但也不排除一些虚假身份证号骗取财政补助的现象。经询问，******于****年*月*日起使用了身份证电子录入系统，因此对****年*月*日之后售卡且身份证号不合规人员的乘坐次数就要予以核减。通过excel函数或SQL语句筛选均可将身份证号码不符合第十八位验证码的人员乘车信息核减，具体的审计思路如下：
******获得需要审计年度内的后台乘车数据，分别为tk_lnk（老年卡乘车表）、tk_axk（爱心卡乘车表）、tk_xsk（学生卡乘车表）、tk_yjk（拥军卡乘车表）、tk_check（年审表）、tk_sell（售卡表）*张表。
二、将上述获得的数据导入到数据库中，******后台乘车数据较大，因此直接提供了****年度老年卡、学生卡、爱心卡、拥军卡刷卡明细表及年审表、售卡明细表单独导出为新数据库，提供给审计组，包括两个数据文件：CENTER_Data.MDF和CENTER_Log.LDF******提供的数据导入到数据库中。
以老年卡乘车信息表为例，对有刷卡记录的人员身份证号进行验证。
三、通过excel函数进行**位身份证号的合规性验证：将售卡表中老年卡售卡人员身份证号导出到excel表格，单元格A*为人员身份证号，B*为验证最终结果。根据第十八位验证码规则首先将身份证号码**位数分别乘以不同的系数，从第一位到第十七位的系数分别为：*－*－**－*－*－*－*－*－*－*－*－*－**－*－*－*－*，将这**位数字和系数相乘的结果相加，然后用加出来的和除以**，由于余数只可能有*－*－*－*－*－*－*－*－*－*－**这**个数字，其分别对应的最后一位身份证的号码为*－*－X －*－*－*－*－*－*－*－*。因此，运用excel函数，在结果单元格B*中输入函数令B*=IF(LEN($A*)**,"长度错误 ",if(right(A*,*)=loopup(MOD(SUMPRODUCT((MID(A*,ROW($*:$**),*)*({*;*;**;*;*;*;*;*;*;*;*;*;**;*;*;*;*}))),**),{*;*;*;*;*;*;*;*;*;*;**},{"*";"*";"X";"*";"*";"*";"*";"*";"*";"*";"*"}),"正确","证号错误"))。即可将身份证号错误的筛选出来，将筛选出来的含有验证结果的excel表格导入到数据库中与乘车信息表进行关联，从而可以得出身份证号不符合验证规则的人员乘车情况，并进行核减。四、通过SQL语句进行**位身份证号的合规性验证：
--生成**位身份证号表
select * into 身份证号为**位
from tk_sell
where CARD_TYPE=* and LEN(id_no)=**
--将**位身份证号转换成**位身份证号
create view v**
as
select *,left(ID_no,*)+'**'+SUBSTRING(ID_no,*,*)+
SUBSTRING('**X********',
(cast(substring(ID_no,*,*) as int)** +cast(substring(ID_no,*,*) as int)** + cast(substring(ID_no,*,*)as int)*** + cast(substring(ID_no,*,*)as int)** + cast(substring(ID_no,*,*)as int)** + cast(substring(ID_no,*,*)as int)** + *** + *** + cast(substring(ID_no,*,*)as int)** + cast(substring(ID_no,**,*)as int)** + cast(substring(ID_no,**,*)as int)** + cast(substring(ID_no,**,*)as int)** +cast(substring(ID_no,**,*)as int)*** +cast(substring(ID_no,**,*)as int)** + cast(substring(ID_no,**,*)as int)** + cast(substring(ID_no,**,*)as int)** + cast(substring(ID_no,**,*)as int)**)%**+*,*) as 身份证号码
from dbo.身份证号为**位
select *,substring(身份证号码,**,*) 身份证最后一位, (cast(substring(身份证号码,*,*) as int)** +cast(substring(身份证号码,*,*) as int)** + cast(substring(身份证号码,*,*)as int)*** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,*,*)as int)** + cast(substring(身份证号码,**,*)as int)** + cast(substring(身份证号码,**,*)as int)** + cast(substring(身份证号码,**,*)as int)** +cast(substring(身份证号码,**,*)as int)*** +cast(substring(身份证号码,**,*)as int)** + cast(substring(身份证号码,**,*)as int)** + cast(substring(身份证号码,**,*)as int)** + cast(substring(身份证号码,**,*)as int)**)%** 余数
into 身份证号**位新表
from v**
where LEN(身份证号码)=** and CARD_TYPE =*
--通过验证码规则对身份证号第十八位合规性进行验证（由于‘x’转换成int整型失败，因此分情况进行验证）
create view v**_*
as
select *,case when 余数=* then *when 余数=* then *when 余数=* then *when 余数=* then *when 余数=* then *when 余数=* then *when 余数=* then *when 余数=* then * when 余数=* then *when 余数=** then *end 校验码
from dbo.身份证号位新表
--身份证号为**位但不合规人员信息：
select * from v**_* where 校验码身份证最后一位 and 校验码 is not null and 身份证最后一位'x'
union
select * from v**_* where 校验码 is null and 身份证最后一位'x'
union
select * from v**_* where 身份证最后一位='x' and 余数*
将上述筛选出来的**位及**位不符合验证码规则的身份证号与老年卡乘车信息表进行关联，从而筛选出身份证号不合规人员的乘车次数，并进行核减。以同样的方法对学生卡、爱心卡、拥军卡乘车次数进行核减，从而得出四种类型的公交卡共需核减的乘车次数。
