/*
身份校驗行數
*/
if exists(select * from sysobjects where name='fun_utils_idnumberoprater' and type='FN')
	drop function fun_utils_idnumberoprater
go
create function fun_utils_idnumberoprater
(
@idnumber varchar(50)=''
) 
returns varchar(500)
as
/*
公民身份號碼是由17位數字碼和1位校驗碼組成。排列順序從左至右分別為：6位地址碼，8位出生日期碼，3位順序碼和1位校驗碼。
地址碼（身份證地址碼對照表見下面附錄）和出生日期碼很好理解，順序碼表示在同一地址碼所標識的區域范圍內，對同年同月同日出生的人編定的順序號，順序碼的奇數分配給男性，偶數分配給女性。
身份證最后一位校驗碼算法如下：
1. 將身份證號碼前17位數分別乘以不同的系數，從第1位到第17位的系數分別為：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
2. 將得到的17個乘積相加。
3. 將相加后的和除以11并得到余數。
4. 余數可能為0 1 2 3 4 5 6 7 8 9 10這些個數字，其對應的身份證最后一位校驗碼為1 0 X 9 8 7 6 5 4 3 2。
function：
	1.校驗身份證有效性
	2.15位身份證轉18位
*/
begin

	declare @ReturnText varchar(1000) --返回值
			,@Separator varchar(1) --分隔符
			
			,@idnumberElement varchar(1) --身份證每位元素
			,@CurrentIndex int --身份證號當前索引位
			,@NextIndex int --身份證號下一個索引位
			
			,@xsnumber varchar(50) --系數
			,@xsElement varchar(2) --系數每位元素
			,@xsCurrentIndex int --系數當前索引位
			,@xsNextIndex int --系數下一個索引位
			
			,@jym varchar(11) --校驗碼
			,@sum int --身份證元素*系數求和
			,@div int --合計對11求余數
			
			,@CurrentJym varchar(1) --當前校驗碼
			,@CurrentWs int --當前身份證位數
			,@NewJym varchar(1000) --新校驗碼
			,@NewWs int --新身份證位數
			,@oldId varchar(20)

	--處理身份證號并校驗位數有效性
	set @Separator =',';
	set @CurrentIndex = 1;
	set @xsnumber = '7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2';
	set @xsCurrentIndex = 1;
	set @jym = '10X98765432';
	set @sum = 0;
	set @CurrentWs = 0;
	set @CurrentJym = '';
	set @NewJym = '';
	set @NewWs = 0;
	set @CurrentWs=len(@idnumber);
	set @oldId = @idnumber;
	
	
	if @CurrentWs=15
	begin
		set @idnumber=stuff(@idnumber,7,0,case when substring(@idnumber,13,3) in ('999','998','997','996') then '18' else '19' end);
		
		SET @CurrentJym = '';
	end
	else if @CurrentWs>=18
	begin
		set @CurrentJym=substring(@idnumber,18,1);
		set @idnumber=substring(@idnumber,1,17);
	end 
	else
	begin
		set @ReturnText='校驗失敗_'
		goto TheEnd;
	end
	if isdate(substring(@idnumber,7,8))=0 
	begin
		set @ReturnText='校驗失敗_'
		goto TheEnd;
	end
	--計算身份證校驗位
	while(@CurrentIndex<=len(@idnumber))
	begin
	 set @NextIndex=@CurrentIndex+1;
		set @xsNextIndex=charindex(@Separator,@xsnumber,@xsCurrentIndex);
		
		if(@xsNextIndex=0 OR @xsNextIndex IS NULL)
			set @xsNextIndex=len(@xsnumber)+1;
		
		set @idnumberElement=substring(@idnumber,@CurrentIndex,@NextIndex-@CurrentIndex);
		set @xsElement=substring(@xsnumber,@xsCurrentIndex,@xsNextIndex-@xsCurrentIndex);
		
		if ISNUMERIC(@idnumberElement) > 0
			set @sum=@sum+cast(@idnumberElement as int)*cast(@xsElement as int)
		else
			begin
				set @ReturnText='校驗失敗_';
				goto TheEnd
			end

	
		set @CurrentIndex=@CurrentIndex+1;
		set @xsCurrentIndex=@xsNextIndex+1;
		
	end	
	set @div=@sum%11;
	set @NewJym=substring(@jym,@div+1,1);
	--校驗身份證校驗位是否出錯
	if (@CurrentJym<>'') and (@NewJym<>@CurrentJym)
	begin
		set @ReturnText='校驗失敗_';
		goto TheEnd
	end
	--輸出新的身份證號
	if len(@oldId) > 18
		set @ReturnText='校驗通過，原始身份證長度非15位或18位_'+(@idnumber+@NewJym)
	else
		set @ReturnText='校驗通過，原始身份證長度為'+cast(len(@oldId) as varchar)+'位_'+(@idnumber+@NewJym)
	goto TheEnd	

TheEnd:
	return @ReturnText
end
go

-- ----------------------以下存儲過程是遍歷整個表，添加字段并修改校驗------------------------


/*
* 一下存儲過程是在原表的基礎上，添加字段保存校驗結果，需要替換掉表明和字段名
*/
-- 表名：a_idcard , 列 idcard 原身份證， card 標準(新添加)，result校驗結果（新添加）

-- 添加標準身份證字段
begin
begin
if not exists (select * from syscolumns where id=object_id('a_idcard') and name='card') 
		alter table a_idcard add [card] varchar(20);
end
-- 添加校驗結果字段
begin
if not exists (select * from syscolumns where id=object_id('a_idcard') and name='result') 
alter table a_idcard add [result] nvarchar(20);
end
end
go

--遍歷結果
BEGIN
declare @id_card varchar(100), @res varchar(200),@c_index int
declare cur cursor 

for 
	select idcard from a_idcard -- 這里需要替換

open cur
fetch next from cur into @id_card
while @@FETCH_STATUS = 0 
begin 
set @res = dbo.fun_utils_idnumberoprater(@id_card)
set @c_index = charindex('_',@res)
print substring(@res,0,@c_index)
print substring(@res, @c_index+1, len(@res))

-- 這里需要替換
update a_idcard set card=substring(@res, @c_index+1, len(@res)),result=substring(@res,0,@c_index)
where idcard=@id_card

fetch next from cur into @id_card
end 
close cur
deallocate cur
end 
go

//這個可以驗證15位和18位的身份證，并且包含生日和校驗位的驗證。 
//如果有興趣，還可以加上身份證所在地的驗證，就是前6位有些數字合法有些數字不合法。 
function isIdCardNo(num) {
  if(num.indexOf("x")!=-1){
   return false;
  }

 num = num.toUpperCase();
 //身份證號碼為15位或者18位，15位時全為數字，18位前17位為數字，最后一位是校驗位，可能為數字或字符X。   
 if (!(/(^\d{15}$)|(^\d{17}([0-9]|X)$)/.test(num))) {
  alert('輸入的身份證號長度不對，或者號碼不符合規定！\n15位號碼應全為數字，18位號碼末位可以為數字或X。');
  return false;
 }
 //校驗位按照ISO 7064:1983.MOD 11-2的規定生成，X可以認為是數字10。
 //下面分別分析出生日期和校驗位
 var len, re;
 len = num.length;
 if (len == 15) {
  re = new RegExp(/^(\d{6})(\d{2})(\d{2})(\d{2})(\d{3})$/);
  var arrSplit = num.match(re);
  //檢查生日日期是否正確
  var dtmBirth = new Date('19' + arrSplit[2] + '/' + arrSplit[3] + '/' + arrSplit[4]);
  var bGoodDay;
  bGoodDay = (dtmBirth.getYear() == Number(arrSplit[2]))
     && ((dtmBirth.getMonth() + 1) == Number(arrSplit[3]))
     && (dtmBirth.getDate() == Number(arrSplit[4]));
  if (!bGoodDay) {
   alert('輸入的身份證號里出生日期不對！');
   return false;
  } else {
   //將15位身份證轉成18位
   //校驗位按照ISO 7064:1983.MOD 11-2的規定生成，X可以認為是數字10。   
   var arrInt = new Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2);
   var arrCh = new Array('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2');
   var nTemp = 0, i;
   num = num.substr(0, 6) + '19' + num.substr(6, num.length - 6);
   for (i = 0; i < 17; i++) {
    nTemp += num.substr(i, 1) * arrInt[i];
   }
   num += arrCh[nTemp % 11];
   return num;
  }
 }
 if (len == 18) {
  re = new RegExp(/^(\d{6})(\d{4})(\d{2})(\d{2})(\d{3})([0-9]|X)$/);
  var arrSplit = num.match(re);
  //檢查生日日期是否正確
  var dtmBirth = new Date(arrSplit[2] + "/" + arrSplit[3] + "/" + arrSplit[4]);
  var bGoodDay;
  bGoodDay = (dtmBirth.getFullYear() == Number(arrSplit[2]))
     && ((dtmBirth.getMonth() + 1) == Number(arrSplit[3]))
     && (dtmBirth.getDate() == Number(arrSplit[4]));
  if (!bGoodDay) {
   alert(dtmBirth.getYear());
   alert(arrSplit[2]);
   alert('輸入的身份證號里出生日期不對！');
   return false;
  } else {
   //檢驗18位身份證的校驗碼是否正確。
   //校驗位按照ISO 7064:1983.MOD 11-2的規定生成，X可以認為是數字10。
   var valnum;
   var arrInt = new Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2);
   var arrCh = new Array('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2');
   var nTemp = 0, i;
   for (i = 0; i < 17; i++) {
    nTemp += num.substr(i, 1) * arrInt[i];
   }
   valnum = arrCh[nTemp % 11];
   if (valnum != num.substr(17, 1)) {
    alert('18位身份證的校驗碼不正確！應該為：' + valnum);
    return false;
   }
   return num;
  }
 }
 return false;
}