php query

include('inc/mlog.php');
include('inc/head.php');
include('inc/tbs.php');

function cnId18to15($id)
{
if(strlen($id)!=18) return '';
return substr($id,0,6).substr($id,8,9);
}

function cnId15to18($id)
{
if(strlen($id)!=15) return '';
$nid=substr($id,0,6).'19'.substr($id,6);
$prm=array(7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2);
$sum=0;
for($i=0;$i<17;$i++)
$sum+=$nid[$i]*$prm[$i];
$map=array('1','0','x','9','8','7','6','5','4','3','2');
$chk=$map[$sum%11];
return $nid.$chk;
}

function days_interval($date0, $date1)
{
$dt0=strtotime($date0);
$dt1=strtotime($date1);
return (int)(($dt0-$dt1)/86400);
}

function do_blacklist($idb)
{
$len=strlen($idb);
if ($len==15 || $len==18){
$id1=$len==15?cnId15to18($idb):cnId18to15($idb);
$sqlb="select cust_id,ids from t_blacklist where cust_id='$idb' or cust_id='$id1'";
}else{
$sqlb="select cust_id,ids from t_blacklist where cust_id='$idb'";
}
$resb=mssql_query($sqlb) or die(mssql_error());
$rowb=mssql_num_rows($resb);
$rowb>0?$blacklist='Y':$blacklist='N';
/*if (mssql_num_rows($resb)>0){
$black='Y';
list($black_iden,$black_ids)=mssql_fetch_row($resb);
$blackinfo="Blacklist";
}else{
$black='N';
}*/
return $blacklist;

}
function level($e,$ssiarr)
{
if (strlen($e)>6){
$e=substr($e,0,1);}

$sh=$ssiarr[$e];
return $sh;
}

function do_non($e)
{
if (($e)==''){$e='?¡ä?a';}

return $e;
}
function do_date($d)
{
if ($d=='1900-01-01')
{
$d="";
return $d;
}
else
{

return $d;
}
}

$typs=array(1=>'Éí·ÝÖ¤', '»¤ÕÕ', '¾ü¹ÙÖ¤', '̨°ûÕ÷', 6=>'ÆäËûÖ¤¼þ', 'Ïã¸ÛÉí·ÝÖ¤', 'Éç±£¿¨');
$gdrs=array(0=>'δ֪', 1=>'ÄÐ', 'Å®',4=>'δ˵Ã÷');
$mrgs=array(0=>'δ֪', 'δ»é', 'ÒÑ»é', 'ɥż', 'Àë»é', 9=>'ÆäËû');

if(isset($_GET['id'])){
$id=trim($_GET['id']);
$sql="INSERT sys_hist(pwid,act,cont,tm) VALUES('$pwid','ENQID','$id',getdate())";
mssql_query($sql);
$len=strlen($id);
if($len==15 || $len==18){
$id1=$len==15?cnId15to18($id):cnId18to15($id);
$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE identity_no='$id' OR identity_no='$id1' ORDER BY report_time DESC";
}else{
$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE identity_no='$id' ORDER BY report_time DESC";
}

}elseif(isset($_GET['nam'])){
$id='';
$nam=trim($_GET['nam']);
$sql="INSERT sys_hist(pwid,act,cont,tm) VALUES('$pwid','ENQNAM','$nam',getdate())";
mssql_query($sql);
$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE name=('$nam') ORDER BY report_time DESC";
//die($sql);
}elseif(isset($_GET['rid'])){
$rid=trim($_GET['rid']);
$sql="INSERT sys_hist(pwid,act,cont,tm) VALUES('$pwid','VIEW','$rid',getdate())";
mssql_query($sql);
$sql="SELECT top 1 identity_no FROM dat_info WHERE rpt_id='$rid'";
$res=mssql_query($sql);
mssql_num_rows($res);// or rmdie('no records');
$row=mssql_fetch_row($res);
$id=$row[0];
$len=strlen($id);
if($len==15 || $len==18){
$id1=$len==15?cnId15to18($id):cnId18to15($id);
$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE identity_no='$id' OR identity_no='$id1' ORDER BY report_time DESC";
}else{
$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE identity_no='$id' ORDER BY report_time DESC";
}
}else exit;

if($_SESSION['type']=='S'){
$tsql="SELECT COUNT(*) FROM sys_enqids WHERE pwid='$pwid' AND iden_id='$id'";
list($nr)=mssql_fetch_row(mssql_query($tsql));
if($nr==0) chk_priv('Q');
}else{
chk_priv('Q');
}

//die($sql);
//iconv('GBK','UTF-8',$sql);
//mssql_query("SET NAMES gb2312");
//$sql="SELECT rpt_id,enquiry_reason,report_time,identity_type,identity_no FROM dat_info WHERE name='ÖܺâȪ'";

$res=mssql_query($sql);// or die(mssql_error());
//$res=mssql_query(iconv('UTF-8','GBK',$sql));
//die("222");
//die($sql);
//if(!mssql_num_rows($res))
//{
/*$n=mssql_num_rows($res);
echo $sql;
echo $n;
echo "no data";*/
//}
if(mssql_num_rows($res)){
//die("1111");
$row=mssql_fetch_row($res);
$id=$row[4];
$idtyp=$typs[$row[3]];
echo <<

$idtyp: $id

ID	Report ID	Enquiry Reason	Report Time

'.$qrid.'':$qrid;
echo ' 1 '.$sqrid.'';
echo ' '.$row[1].' '.$row[2]."n";

for($i=2; $row=mssql_fetch_row($res); $i++){
$qrid=$row[0];
$sqrid=isset($_GET['rid'])&&!strcmp($_GET['rid'],$qrid)?''.$qrid.'':$qrid;
echo '

'.$i.' '.$sqrid.'';
//echo ' '.iconv('GBK','UTF-8',$row[1]).' '.$row[2]."n";
echo ' '.$row[1].' '.$row[2]."n";
}
echo "n";
}else{
$sql="SELECT pwid,time FROM sys_enqids WHERE iden_id='$id'";
$res=mssql_query($sql);
echo '

'.$id.'

';
if(mssql_num_rows($res)){
while(list($pwid,$tm)=mssql_fetch_row($res))
echo 'this id "'.$id.'" has been enquired by sales "'.$pwid.'" at '.$tm.'
';
}else{
echo 'no record in database, enquire again?';
}
}

if(empty($_GET['rid'])){
include('inc/foot.php');
exit;
}

$rid=$_GET['rid'];
//$sql="SELECT top 1 * FROM dat_info where rpt_id='$rid'";
$sql="select top 1 rpt_id,rpt_typ,rpt_src,enquiry_reason,report_time,card_no,credit_limit,mon_out_amt,mon_out_amt_prev,dishonest_overdraft,loan_no,loan_amt,loan_out_amt,mon_inst_amt,overdue_amt,non_banking_info,special_record,common_record,enquiry_record,dispute_record,convert(varchar(10),date_first_card,120) as date_first_card,convert(varchar(10),date_first_loan,120) as date_first_loan,name,identity_type,identity_no,
gender,convert(varchar(10),birthday,120) as birthday,job_title,convert(varchar(10),job_getdate,120) as job_getdate,marriage,convert(varchar(10),marriage_getdate,120) as marriage_getdate,education,convert(varchar(10),education_getdate,120) as education_getdate from dat_info where rpt_id='$rid'";
//die($sql);
$res=mssql_query($sql) or rmdie(mssql_error());
mssql_num_rows($res) or rmdie('no record in database');
$arr=mssql_fetch_array($res);

$arr['birthday']=='1900-01-01' and $arr['birthday']='';

$loans=array();
$sql="SELECT loan_type,loan_limit,security_type,convert(varchar(10),disburse_date,120) as disburse_date,account_status,pastdue_amt,loan_out,repay_freq,
remain_repay_mons,mon_inst_amt,convert(varchar(10),latest_repay_date,120) as latest_repay_date,latest_repay_amt,convert(varchar(10),getdate,120) as getdate,
arrear_hist_30,arrear_hist_60,arrear_hist_90,
mr01,mr02,mr03,mr04,mr05,mr06,mr07,mr08,mr09,mr10,mr11,mr12,mr13,mr14,mr15,mr16,mr17,mr18,mr19,mr20,mr21,mr22,mr23,mr24 FROM dat_loan where rpt_id='$rid'";

//die($sql);
$res=mssql_query($sql);
if(mssql_num_rows($res)){
while($row=mssql_fetch_row($res))
array_push($loans, $row);
}

$sl_nr=0;
$ul_nr=0;
$sl_amt=0;
$ul_amt=0;
$sl_os=0;
$ul_os=0;
$sl_mi=0;
$ul_mi=0;

$sl_nd6=0;
$ul_nd6=0;

$sl_ndc=0;
$ul_ndc=0;

$mg_nr=0;

$mit=39;
for($i=0; $i $ln=$loans[$i];
$typ=$ln[2];
if($ln[4]=='½áÇå') continue;
if($typ=='µÖѺ'||$typ=='ÖÊѺ'){
$sl_nr++;
$sl_amt+=$ln[1];
$sl_os+=$ln[6];
$sl_mi+=$ln[9];
for($j=$mit;$j>$mit-6;$j--)
if($ln[$j]=='-') $sl_nd6++;
if($ln[$mit]=='-') $sl_ndc++;
}else{
$ul_nr++;
$ul_amt+=$ln[1];
$ul_os+=$ln[6];
$ul_mi+=$ln[9];
for($j=$mit;$j>$mit-6;$j--)
if($ln[$j]=='-') $ul_nd6++;
if($ln[$mit]=='-') $ul_ndc++;
}

if($ln[0]=='ס·¿¹«»ý½ð´û¿î' or $ln[0]=='ס·¿ÉÌÒµÐÔ´û¿î' or $ln[0]=='ÉÌÆÌ´û¿î'){
$mg_nr++;
}
}

$teles=array();
$sql="SELECT telecom_type,convert(varchar(10),date_issuance,120) as date_issuance,status,dlq_amt,dlq_no,convert(varchar(10),getdate,120) as getdate FROM dat_telecom where rpt_id='$rid'";
$res=mssql_query($sql);
if(mssql_num_rows($res)){
while($row=mssql_fetch_row($res))
array_push($teles, $row);
}

$tl_nr=0;
$tl_amt=0;

for($i=0; $i $tl=$teles[$i];
$tl_nr+=$tl[4];
$tl_amt+=$tl[3];
}

$enqrs=array();
$sql="SELECT reason,type,convert(varchar(10),getdate,120) as getdate FROM dat_enquiry where rpt_id='$rid'";
$res=mssql_query($sql);
if(mssql_num_rows($res)){
while($row=mssql_fetch_row($res))
array_push($enqrs, $row);
}

$eq_ne3=0;
$eq_nec12=0;

for($i=0; $i $eq=$enqrs[$i];
$itv=days_interval($arr['report_time'], $eq[2]);
$itv<90 and $eq_ne3++;
$itv<365 && strpos($eq[0],'ÐÅÓÿ¨')!==false and $eq_nec12++;
}

$sql="
SELECT MAX(nr) FROM (
SELECT ym, COUNT(*) AS nr FROM (
SELECT convert(char(7),getdate,20) as ym FROM dat_enquiry where rpt_id='$rid'
) AS m
GROUP BY ym
) AS n
";
$res=mssql_query($sql);
list($eq_emax)=mssql_fetch_row($res);

?>

Summary

Profile
Name		Gender		DOB		Marriage		Education
Credit Card
Total #		Total Lmt
Secured Loan										#Mortgage
Total #		Total Amt		Total OS		Total Inst		#DPD L6M		#DPD Cur
Unsecured Loan
Total #		Total Amt		Total OS		Total Inst		#DPD L6M		#DPD Cur
Telecom
Dlq #		Dlq Amt
Enquiry
# L3M		# CC L1Y		# MAX / M
Others
Special		Public		Dispute

Ïû·ÑÕßÐÅÓñ¨¸æ
²éѯԭÒò:	±¨¸æ±àºÅ:	±¨¸æʱ¼ä:
ÒÔϱ¨¸æÄÚÈÝÒÀ¾Ý½ØÖÁ±¨¸æʱ¼äΪֹµÄ¸öÈËÐÅÓÃÁªºÏÕ÷ÐÅϵͳÖеÄÓйظÃÏû·ÑÕßµÄÐÅÓÃÐÅÏ¢£¬¸ÃÐÅÏ¢ÔÚÐÅÓÃÁªºÏÕ÷ÐŹý³ÌÖб»Óйػú¹¹ºÍ¸öÈ˱¨¸æ£¬ÓÉÕ÷ÐÅ»ú¹¹²É¼¯²¢ÄÉÈë¸öÈËÐÅÓÃÁªºÏÕ÷ÐÅϵͳ¡£

	Ïû·ÑÕß»ù±¾ÐÅÏ¢
±¾²¿·ÖÐÅÏ¢Ò»°ãÊÇÓÉÏû·ÑÕß±¾ÈËÔÚÉêÇëÐÅ´û²úƷʱÖ÷¶¯É걨£¬²¢¸ù¾ÝÏû·ÑÕߵIJ»Ê±ÉêÇë½øÐÐÐÞÕý¡£

ÐÕÃû		Ö¤¼þºÅ	£¨£©
ÐÔ±ð		³öÉúÈÕ

		ÐÅÏ¢»ñÈ¡ÈÕÆÚ
ÐÅÏ¢²É¼¯Ê±±»±¨¸æµÄ»éÒö×´¿ö
ÐÅÏ¢²É¼¯Ê±±»±¨¸æµÄ×î¸ßѧÀú
ÐÅÏ¢²É¼¯Ê±±»±¨¸æµÄÖ°³Æ

	Ôø¾­±»±¨¸æµÄסַ	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
'.$i.'	'.$row[0].'	'.do_date($row[1]).'
ÎÞÐÅÏ¢

Ôø¾­±»±¨¸æµÄÖ°ÒµÐÅÏ¢
	¹¤×÷µ¥Î»	Ö°Òµ	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
'.$i.'	'.$row[0].'	'.$row[1].'	'.do_date($row[2]).'
ÎÞÐÅÏ¢

	ÐÅÏ¢¸ÅÒª

ÐÅÓÿ¨Êý:	Ê׿¨ÈÕ:	͸֧¶î¶È:	ÔÂ͸Óà¶î:	ÔÂ͸×ܶî:	¶ñÒâ͸֧:
´û¿î±ÊÊý:	Ê×´ûÈÕ:	´û¿î×ܶî:	´û¿îÓà¶î:	Э¶¨Ô»¹¿î:	ÓâÆÚ×ܶî:
·ÇÒøÐÐÐÅÓÃ:	Ìرð¼Ç¼:	²éѯ¼Ç¼:	ÒìÒé¼Ç¼:	¹«¹²¼Ç¼:

	ÒøÐÐÐÅÓÃÐÅÏ¢

ÐÅÓÿ¨ÏîÄ¿

¿ª»§ÈÕÆÚ

ÕÊ»§×´Ì¬

ÔÂĩ͸֧Óà¶î

24ÔÂÄÚ¸÷ÔÂ͸֧×ܶî×´¿ö

ÐÅÏ¢»ñÈ¡ÈÕÆÚ

//die($sql);
$res=mssql_query($sql);
if(mssql_num_rows($res)){
for($i=1; $row=mssql_fetch_row($res); $i++){
echo '

'.$i.'.'.$row[0].' '.$row[1].' '.$row[2].' '.$row[3].' ';
$init=5;
for($j=$init;$jecho $row[$j];
echo ' '.date($row[4]).'';
}
}else{
echo ' ÎÞÐÅÏ¢';
}
?>

´û¿îÏîÄ¿	´û¿î½ð¶î	µ£±£·½Ê½	¿ª»§ÈÕÆÚ	ÕÊ»§×´Ì¬	ÔÂÄ©ÀÛ¼ÆÓâÆÚ/´ú³¥/ºËÏú	ÔÂÄ©´û¿îÓà¶î	»¹¿îÖÜÆÚ
	Ê£Ó໹¿îÔÂÊý	Э¶¨Ô»¹¿î¶î	×î½ü»¹¿îÈÕÆÚ	×î½ü»¹¿î½ð¶î	24ÔÂÄÚ¸÷Ô»¹¿î×´¿ö	ÍÏÇ·ÀúÊ·	ÐÅÏ¢»ñÈ¡ÈÕÆÚ

if(count($loans)>0){
$arrfreq=array(2=>'°´ÔÂ','°´¼¾','°´Äê','Ò»´ÎÐÔ»¹Çå','²»¶¨ÆÚ','°´¹Ì¶¨ÖÜÆÚ¸¶Ï¢£¬µ½ÆÚ»¹±¾','°´¹Ì¶¨ÖÜÆÚ¸¶Ï¢£¬°´¼Æ»®»¹±¾');
for($i=0; $i $row=$loans[$i];
echo '

'.($i+1).'.'.$row[0].' '.$row[1].' '.$row[2].' '.$row[3].' '.$row[4].' '.$row[5].' '.$row[6].' '.$arrfreq[$row[7]].'';
echo ' '.$row[8].' '.$row[9].' '.$row[10].' '.$row[11].' ';
$init=16;
for($j=$init;$jecho $row[$j];
echo ' '.$row[13].'/30 '.$row[14].'/60 '.$row[15].'/90 '.$row[12].'';
echo ' ';
}
}else{
echo ' ÎÞÐÅÏ¢';
}

?>

	·ÇÒøÐÐÐÅÓÃÐÅÏ¢

¹«»ý½ðÏîÄ¿	¿ª»§ÈÕÆÚ	ÕÊ»§×´Ì¬	×î½ü12¸öÔ½ÉÄÉ×´¿ö	¹«»ý½ðʹÓÃÇé¿ö	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
δ֪Êý¾Ý¸ñʽ

ͨÐÅÏîÄ¿

¿ª»§ÈÕÆÚ

״̬

Ç··Ñ½ð¶î

Ç··Ñ´ÎÊý

ÐÅÏ¢»ñÈ¡ÈÕÆÚ

if(count($teles)>0){
for($i=0; $i $row=$teles[$i];
echo '

'.($i+1).'.'.$row[0].' '.$row[1].' '.$row[2].' '.$row[3].' '.$row[4].' '.$row[5].'';
}
}else{
echo ' ÎÞÐÅÏ¢';
}
?>


 

¹«ÓÃÊÂÒµÏîÄ¿	µ±Ç°Ç··ÑÇé¿ö	×î½ü2ÄêÄÚÓÐÎÞÑÏÖØÍÏÇ·¼Ç¼	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
δ֪Êý¾Ý¸ñʽ

	Ö´ÒµÐÅÏ¢

Ö´ÒµÆÀ¶¨Ãû³Æ	ÆÀ¶¨Ê±¼ä	Äê¼ìÇé¿ö	ÐÐҵΥ¼ÍÎ¥¹æ	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
δ֪Êý¾Ý¸ñʽ

	Ìرð¼Ç¼

¼Ç¼ÀàÐÍ	¼Ç¼À´Ô´	·¢ÉúÈÕÆÚ	Éæ¼°ÄÚÈÝ	Éæ¼°½ð¶î	´¦Àí½á¹û	ÐÅÏ¢»ñÈ¡ÈÕÆÚ
'.$row[0].'	'.$row[1].'	'.$row[2].'	'.$row[3].'	'.$row[4].'	'.$row[5].'	'.$row[6].'
ÎÞÐÅÏ¢

	²éѯ¼Ç¼

²éѯԭÒò

²éѯÕßÀàÐÍ

²éѯÈÕÆÚ

if(count($enqrs)){
for($i=0; $i $row=$enqrs[$i];
echo '

'.($i+1).'.'.$row[0].' '.$row[1].' '.$row[2].'';
}
}else{
echo ' ÎÞÐÅÏ¢';
}
?>

	ÒìÒé¼Ç¼

ÒìÒéÏîÄ¿	ÒìÒéÄÚÈÝ	ÒìÒé״̬	ÒìÒéÈÕÆÚ
'.$row[0].'	'.$row[1].'		'.$row[2].'
ÎÞÐÅÏ¢

	¸öÈËÉùÃ÷

ÐòºÅ	¸öÈËÉùÃ÷ÄÚÈÝ	ÉùÃ÷ÈÕÆÚ
δ֪Êý¾Ý¸ñʽ

¡¡	¡¡	¡¡
	×Ê ÐÅ Ìá ʾ
¡¡	¡¡	¡¡
colSpan=3>Ìرð¸æÖª£º×ÊÐÅÌáʾÄÚÈݽö¹©ÊÚÐÅ»ú¹¹ÔÚÊÚÐŹý³ÌÖвο¼£¬ÊÚÐÅ»ú¹¹²»µÃÒÔÈκÎÀíÓÉÏòÈκÎËû·½£¨°üÀ¨Ïû·ÑÕß±¾ÈË£©ÌṩºÍÅû¶£¬Õ÷ÐÅ»ú¹¹Ò²²»ÊÜÀíÈκÎͬÓйØÏÂÁÐÄÚÈݵÄÒìÒé¡£

ÏîÄ¿	ÌáʾÄÚÈÝ	Ìáʾʱ¼ä
0$i. $row[0]	$row[1]	$row[2]
ÎÞÐÅÏ¢

if ($bidui==1){
$sql="select * from dat_ssi where rpt_id='$rid'";
$res=mssql_query($sql);
$row=mssql_fetch_row($res);
for ($i=0;$i<14;$i++){$row[$i]=do_non($row[$i]);
}
?>

¡¡	¡¡	¡¡
	Éç ±£ ЊϢ Ìá ʾ
¡¡	¡¡	¡¡

if ($row[1]==$arr['name']){$row[1]=$row[1]."*";}else{$row[1]=" ".$row[1]."";}
if ($row[3]==$mrgs[$arr['marriage']]){$row[3]=$row[3]."*";}else{$row[3]=" ".$row[3]."";}
?>

01.¸öÈËÉí·ÝÐÅÏ¢±È¶Ô
ÐÕÃû
³öÉúÈÕÆÚ
»éÒö×´¿ö
µ¥Î»
»§¼®µØÖ·
02.¸öÈËÉç±£ÑøÀϽðÐÅÏ¢
µ±ÄêÔ½ɽð¶î
ÀÛ¼ÆÓà¶î
ÀۼƽÉÄÉÔÂÊý
½É½»µ¥Î»
03.¸öÈËס·¿¹«»ý½ðÐÅÏ¢
¿ª»§ÄêÔÂ
ÕË»§×´Ì¬
µ±ÄêÔ½ɽð¶î
ÀÛ¼ÆÓà¶î

	±¨¸æÍê±Ï

include('inc/foot.php');
?>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91551/viewspace-998354/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91551/viewspace-998354/