<?php
|
//分页信息
|
$page=empty($_REQUEST['page'])!=false ? 1 : $_REQUEST['page'];
|
$QuantityInt = 20; //每页显示数量
|
$acc1=$page; //当前页数
|
$acc2=0; //总页数
|
$acc3=0; //总记录数
|
|
//查询条件
|
$searchSql="";
|
/*
|
$searchTXT=empty($searchTXT)!=false ? "" : $searchTXT;
|
if ($searchTXT!=""){
|
$searchSql=" and (OA_Name like '%$searchTXT%') ";
|
}else{
|
$searchSql="";
|
}
|
*/
|
if (!empty($OrdAreaTypeID)) {
|
$searchSql=$searchSql." and ServiceOrdAreaType=".$OrdAreaTypeID;
|
}
|
if (!empty($OrdTypeID)) {
|
$searchSql=$searchSql." and ServiceOrdType=".$OrdTypeID;
|
}
|
|
|
//$OrdClassListSql="";
|
//查看各分公司报表权限
|
if (isDepartment("070109")==1) {
|
$OrdClassListSql="";
|
//$UnitNameID="1";
|
If ($UnitNameID!="" and $UnitNameID!="0") {
|
$sql="select vOrder2 from dictionary where vtitle='UnitName' and vID=".$UnitNameID;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
if (sqlsrv_rows_affected($data)!=0) {
|
while($rs = sqlsrv_fetch_array($data) ) {
|
$UnitTypeID = $rs[0];
|
$sql="select vID,vtext,vOrder2 from dictionary where vID in (".$UnitTypeID.") and vtitle='OrderClass' order by vOrder";
|
$data1 = sqlsrv_query($conn,$sql);
|
if($data1 == true){
|
while($rs1 = sqlsrv_fetch_array($data1) ) {
|
$OrderClassID = $rs1['vOrder2'];
|
$OrdClassListSql .= ",".$OrderClassID;
|
}
|
}
|
$OrdClassListSql=substr($OrdClassListSql,1);
|
$OrdClassListSql=str_replace(",","','",$OrdClassListSql);
|
}
|
}
|
}
|
}else{
|
$sql="select vID,vtext,vOrder2 from dictionary where vType in (1,2) and vtitle='OrderClass' order by vOrder";
|
$data1 = sqlsrv_query($conn,$sql);
|
if($data1 == true){
|
while($rs1 = sqlsrv_fetch_array($data1) ) {
|
$OrderClassID = $rs1['vOrder2'];
|
$OrdClassListSql .= ",".$OrderClassID;
|
}
|
}
|
$OrdClassListSql=substr($OrdClassListSql,1);
|
$OrdClassListSql=str_replace(",","','",$OrdClassListSql);
|
}
|
}
|
|
$orderby=empty($orderby)!=false ? "" : $orderby;
|
If (strrpos($orderby," desc")>1){
|
$orderbydesc="";
|
$descTXT="↓";
|
}else{
|
$orderbydesc=" desc";
|
$descTXT="↑";
|
}
|
|
If ($orderby=="") {
|
$orderby="OA_DepartmentID,OA_User_ID";
|
$descTXT="↓";
|
}
|
|
if ($ServiceBranch!="") {
|
$OrdClassListSql=$ServiceBranch;
|
}
|
|
//查询开始
|
$SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType;
|
$OrdClassType=empty($OrdClassType)!=false ? "1" : $OrdClassType;
|
|
$sql="select (case UnitShort when '微信小程序' then '广州' else UnitShort end) UnitShort,ServiceBranch,CONVERT(VARCHAR(20),ServiceOrd_CC_Time,120) ServiceOrd_CC_Time,ServiceOrdCoPhone,ServiceOrdTraTxnPrice,ServiceOrdID,DispatchOrdID
|
,ServiceOrdNo=ServiceOrdClass+convert(nvarchar(10),year(ServiceOrd_CC_Time))+right('0'+convert(nvarchar(10),month(ServiceOrd_CC_Time)),2)+right('0'+convert(nvarchar(10),day(ServiceOrd_CC_Time)),2)+'-'+right('00'+convert(nvarchar(10),ServiceOrdNo),3)
|
,DispatchOrdNo=DispatchOrdClass+convert(nvarchar(10),year(DispatchOrd_NS_Time))+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+right('0'+convert(nvarchar(10),day(DispatchOrd_NS_Time)),2)+'-'+right('00'+convert(nvarchar(10),DispatchOrdNo),3)
|
,ConsumptionPoints=(select isnull(sum(Points_Score),0) from UserPoints where Points_State=1 and ServiceOrdIDDt=ServiceOrdID and Points_Type=1)
|
,RecommendPoints=(select isnull(sum(Points_Score),0) from UserPoints where Points_State=1 and ServiceOrdIDDt=ServiceOrdID and Points_Type=3)
|
,UsePoints=(select isnull(sum(Points_Score),0) from UserPoints where Points_State=1 and ServiceOrdIDDt=ServiceOrdID and Points_Type=6)
|
from ServiceOrder
|
LEFT JOIN DispatchOrd on ServiceOrdID=DispatchOrd.ServiceOrdIDDt and DispatchOrdState<>4
|
LEFT JOIN IntroducerUnitData on ServiceBranch=ServiceOrdClass and UnitState>=1
|
where ServiceOrdID in (select ServiceOrdIDDt from UserPoints where Points_State=1 and ServiceOrdIDDt<>0 and ServiceOrdIDDt is not null and Points_Time ".$SqlOrdDateType.") and ServiceOrdClass in ('".$OrdClassListSql."')
|
order by ServiceOrd_CC_Time desc";
|
//echo $sql;exit;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$FormArray=array();
|
//表头
|
$FormArray[0]=array("序号","分支机构名称","任务生成时间","服务单号","调度单号","任务金额","客户电话","消费积分金额","消费积分比例","推荐积分金额","推荐积分比例","积分收入金额");
|
$FormArrayX=count($FormArray[0]);
|
if (sqlsrv_rows_affected($data)==0) {
|
$SystemMessageType=2;
|
$SystemMessageTXT="数据库中相关无数据！";
|
$y=0;
|
} else {
|
$y=0;
|
$i=0;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
//读取数据
|
$ServiceOrdPhone = $rs['ServiceOrdCoPhone'];
|
$ConsumptionPoints = intval($rs['ConsumptionPoints']);
|
$RecommendPoints = intval($rs['RecommendPoints']);
|
$UsePoints = intval($rs['UsePoints']);
|
$ServiceOrd_Time = $rs['ServiceOrd_CC_Time'];
|
$UnitShort = $rs['UnitShort'];
|
$ServiceOrdTraTxnPrice = intval($rs['ServiceOrdTraTxnPrice']);
|
$ServiceOrdID = $rs['ServiceOrdID'];
|
$DispatchOrdID = $rs['DispatchOrdID'];
|
$DispatchOrdNo = $rs['DispatchOrdNo'];
|
$ServiceOrdNo = $rs['ServiceOrdNo'];
|
//读取数据--End
|
|
//写入数组
|
$i++;
|
$FormArray[$i][0]=$i;
|
$FormArray[$i][1]=$UnitShort;
|
$FormArray[$i][2]=$ServiceOrd_Time;
|
$FormArray[$i][3]="<a href=/ServiceOrder.gds?ServiceOrdID=".$ServiceOrdID."&OrdDateType=4&OrdClassList=&h_menu1_1=1 target=_blank>".$ServiceOrdNo."</a>";
|
$FormArray[$i][4]="<a href=/DispatchOrder.gds?DispatchOrdID=".$DispatchOrdID."&OrdDateType=4&OrdClassList=&h_menu1_1=1 target=_blank>".$DispatchOrdNo."</a>";
|
$FormArray[$i][5]=$ServiceOrdTraTxnPrice;
|
$FormArray[$i][6]=$ServiceOrdPhone;
|
$FormArray[$i][7]=$ConsumptionPoints;
|
if ($ConsumptionPoints>0){
|
$FormArray[$i][8]=round($ConsumptionPoints/$ServiceOrdTraTxnPrice*100).'%';
|
}else{
|
$FormArray[$i][8]='0%';
|
}
|
$FormArray[$i][9]=$RecommendPoints;
|
if ($RecommendPoints>0){
|
$FormArray[$i][10]=round($RecommendPoints/$ServiceOrdTraTxnPrice*100).'%';
|
}else{
|
$FormArray[$i][10]='0%';
|
}
|
$FormArray[$i][11]=$UsePoints;
|
|
|
//写入数组--End
|
}
|
|
}
|
}
|
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
