由于要保证数据的绝对安全,我采用将过滤的数据导出Excel,并将数据存入session然后人工查看没问题之后,再批量删除.
1.先读取sql server中的所有数据,该数据量很大,而且由于sql server不支持limit但是支持top,而且本次的his系统也没有预留自增ID,所以只有使用倒序来曲线救国,以达到类似limit功能.
2.然后开始过滤电话号码以及手机号码.然后将唯一ID作为数组键值,
3.去除,过滤,然后导出Excel表格,并存入session.
4.人工查询没问题之后,再进行删除.
<?php
error_reporting(E_ERROR | E_CORE_ERROR | E_COMPILE_ERROR | E_USER_ERROR);
header('Content-type:text/html;charset=utf-8');
session_start();
//对象转数组
function object_array($array) {
if(is_object($array)) {
$array = (array)$array;
} if(is_array($array)) {
foreach($array as $key=>$value) {
$array[$key] = object_array($value);
}
}
return $array;
}
//判断是否为电话号码
/**
* 验证手机号是否正确
* @author isle
* @param unknown_type $mobile
*/
function validateMobile($mobile){
$mobile=trim($mobile);
/**
* 手机号码
* 移动：134[0-8],135,136,137,138,139,150,151,157,158,159,182,187,188
* 联通：130,131,132,152,155,156,185,186
* 电信：133,1349,153,180,189
*/
$res1= preg_match('/^1(3[0-9]|4[0-9]|5[0-9]|6[1-9]|7[0-9]|8[0-9]|9[3-9])\\d{8}$/', $mobile);
/**
* 中国移动：China Mobile
11 * 134[0-8],135,136,137,138,139,150,151,157,158,159,182,187,188
*/
$res2= preg_match('/^1(34[0-8]|(3[5-9]|5[017-9]|8[278])\\d)\\d{7}$/', $mobile);
/**
* 中国联通：China Unicom
* 130,131,132,152,155,156,185,186
*/
$res3= preg_match('/^1(3[0-2]|5[256]|8[56])\\d{8}$/', $mobile);
/**
* 中国电信：China Telecom
* 133,1349,153,180,189
*/
$res4= preg_match('/^1((33|53|8[09])[0-9]|349)\\d{7}$/', $mobile);
/**
* 大陆地区固话及小灵通
* 区号：010,020,021,022,023,024,025,027,028,029
* 号码：七位或八位
*/
//$res5= preg_match('/^0(10|2[0-5789]|\\d{3})\\d{7,8}$/', $mobile);
//其他
//$res6= preg_match('/^1(3[0-2]|5[256]|8[56])\\d{8}$/', $mobile);
//if ( $mobile && ( $res1 ||$res2 ||$res3 ||$res4 ) )
if ( $mobile && ( $res1 ||$res2 ||$res3 ||$res4 ||$res5 ) )
{
return true;
}else{
return false;
}
}
//var_dump(validateMobile("15828138456"));die;
//把客户信息数组中组成新的以ctm_id为键名的新数组
function get_customer($customer)
{
$customernew = array();
foreach($customer as $k=>$v)
{
foreach($v as $key=>$value)
{
$customernew[$value['ctm_id']] = $value;
}
}
unset($customer);
return $customernew;
}
//从以ctm_id为键名的客户信息数组中找出不符合电话号码的数据组成数组
function get_customerinfo($customer)
{
$customerinfo = array();
foreach($customer as $key=>$value)
{
if(false == validateMobile($value['ctm_tel']) and false == validateMobile($value['ctm_mobile']))
{
$customerinfo[$key] = $value;
}
}
unset($customer);
return $customerinfo;
}
//导出excel
function createtable($list,$filename,$header=array(),$index = array()){
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
header("Content-Disposition:filename=".$filename.".xls");
$teble_header = implode("\t",$header);
$strexport = $teble_header."\r";
foreach ($list as $row){
foreach($index as $val){
$strexport.=$row[$val]."\t";
}
$strexport.="\r";
}
$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);
exit($strexport);
}
//本地数据库
$loca_servname = "192.168.1.10";
$loca_conninfo = ["Database"=>"his", "UID"=>"sa", "PWD"=>"sa"];
$loca_connection = sqlsrv_connect($loca_servname, $loca_conninfo);
$sql="select COUNT(*) from [dbo].[zsb_customer]";
$num=sqlsrv_fetch_array(sqlsrv_query($loca_connection,$sql))['0'];
$overplus=$num%1000;//剩余
$row=floor($num/1000);//遍历多少次
//select top 3 * from (select top 5 * from [test1].[dbo].[zsb_customer] order by ctm_id) aa order by ctm_id desc
$sql_start ="select top 1000 ctm_id,ctm_code,ctm_tel,ctm_mobile from [dbo].[zsb_customer]";
$sql="select top 1000 ctm_id,ctm_code,ctm_tel,ctm_mobile from (select top 2000 * from [dbo].[zsb_customer] order by ctm_id) aa order by ctm_id desc";
//$sql ="select top 1000 ctm_id,ctm_code,ctm_tel,ctm_mobile from [dbo].[zsb_customer]";
$customer = array();
$customer_over = array();
for($i=0;$i<$row;$i++)
{
$rows=1000+$i*1000;
$sql="select top 1000 ctm_id,ctm_code,ctm_tel,ctm_mobile from (select top ".$rows." * from [dbo].[zsb_customer] order by ctm_id) aa order by ctm_id desc";
$query = sqlsrv_query($loca_connection,$sql);
while ($result = sqlsrv_fetch_object($query)){
$customer[$i][] = object_array($result);
}
}
$sql=$sql_start ="select top ".$overplus." ctm_id,ctm_code,ctm_tel,ctm_mobile from [dbo].[zsb_customer]";
$query = sqlsrv_query($loca_connection,$sql);
while ($result = sqlsrv_fetch_object($query)){
//$customer[$row+1][] = object_array($result);
$customer_over[$row+1][] = object_array($result);
}
//开始处理数组
$customernew=get_customer($customer);
$customerinfo=get_customerinfo($customernew);
$customernew_over=get_customer($customer_over);
$customerinfo_over=get_customerinfo($customernew_over);
array_push($customerinfo,$customerinfo_over);//将最开始剩余数据拼接进数组
ksort($customerinfo); //正序排序
$_SESSION['customerinfo'] =$customerinfo;
//echo 'success';die;//这里开始断点
echo '<pre>';
var_dump($customerinfo);die;
$filename = '有错误的数据'.date('YmdHis');
$header = array('客户ID','客户码','客户电话','客户手机');
$index = array('ctm_id','ctm_code','ctm_tel','ctm_mobile');
createtable($customerinfo,$filename,$header,$index);
die;
?>
下面是执行的删除:
<?php
error_reporting(E_ERROR | E_CORE_ERROR | E_COMPILE_ERROR | E_USER_ERROR);
header('Content-type:text/html;charset=utf-8');
session_start();
//本地数据库
$loca_servname = "192.168.1.10";
$loca_conninfo = ["Database"=>"his", "UID"=>"sa", "PWD"=>"sa"];
$loca_connection = sqlsrv_connect($loca_servname, $loca_conninfo);
$customerinfo = $_SESSION['customerinfo'];
$ids="'";
$codes="'";
$sql="delete from info where code in(".$ids.")";
foreach ($customerinfo as $key => $value)
{
if(!empty($value['ctm_id']))
{
$ids.=$value['ctm_id']."','";
}
if(!empty($value['ctm_code']))
{
$codes.=$value['ctm_code']."','";
}
}
//开始拼接错误的ID和code
$ids=rtrim($ids,",'")."'";
$codes=rtrim($codes,",'")."'";
//执行删除
$sql="delete from [dbo].[zsb_customer] where ctm_id in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_customer表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmzpt] where ctp_ctmcode in({$codes})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmzpt表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmzpt_rec] where ctp_ctmcode in({$codes})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmzpt_rec表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctm_amt] where ctm_code in({$codes})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctm_amt表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmcallinfo] where ctf_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmcallinfo表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmcallinfo_rec] where ctf_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmcallinfo_rec表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmcallpdu] where ctp_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmcallpdu表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmcallpdu_rec] where ctp_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmcallpdu_rec表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmicall] where ctf_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmicall表内容成功<br>';
}
$sql="delete from [dbo].[zsb_ctmicallpdu] where ctp_ctmid in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_ctmicallpdu表内容成功<br>';
}
$sql="delete from [dbo].[zsb_rvinfo] where ctm_code in({$ids})";
if(sqlsrv_query($loca_connection,$sql)){
echo '删除zsb_rvinfo表内容成功<br>';
}
die;
?>