<?php require '/inc/odbc.php';?>
|
<?php require '/inc/function.php';?>
|
<?php
|
if(isDepartment("0302")==0){
|
header("Location: /");
|
exit;
|
}
|
|
$searchTXT=empty($_REQUEST['searchTXT'])==true ? "" : addslashes($_REQUEST['searchTXT']);
|
$orderby=empty($_REQUEST['orderby'])==true ? "" : addslashes($_REQUEST['orderby']);
|
$OrdDateType=empty($_REQUEST['OrdDateType'])==true ? "7" : addslashes($_REQUEST['OrdDateType']);
|
$OrdDateStart=empty($_REQUEST['OrdDateStart'])==true ? "" : addslashes($_REQUEST['OrdDateStart']);
|
$OrdDateEnd=empty($_REQUEST['OrdDateEnd'])==true ? "" : addslashes($_REQUEST['OrdDateEnd']);
|
$AuditStatus=empty($_REQUEST['AuditStatus'])==true ? "0" : addslashes($_REQUEST['AuditStatus']);
|
|
$InvoiceType=empty($_REQUEST['InvoiceType'])==true ? "0" : addslashes($_REQUEST['InvoiceType']);
|
$page=empty($_REQUEST['page'])==true ? 1 : $_REQUEST['page']; //当前页数
|
|
$OrdDateTypeName="";
|
if ($OrdDateType=="-1") {
|
if ($OrdDateStart=="" and $OrdDateEnd=="") {
|
$OrdDateStart=date("Y-m-d");
|
$OrdDateEnd=date("Y-m-d");
|
}
|
elseif ($OrdDateStart!="" and $OrdDateEnd=="") {
|
$OrdDateEnd=$OrdDateStart;
|
}
|
elseif ($OrdDateStart=="" and $OrdDateEnd!="") {
|
$OrdDateStart=$OrdDateEnd;
|
}
|
If ($OrdDateStart>$OrdDateEnd) {
|
$OrdDateStart1=$OrdDateStart;
|
$OrdDateStart=$OrdDateEnd;
|
$OrdDateEnd=$OrdDateStart1;
|
}
|
If ($OrdDateStart==$OrdDateEnd) {
|
$OrdDateTypeName=$OrdDateStart;
|
$OrdDateTypeName1=$OrdDateTypeName;
|
}
|
Else {
|
$OrdDateTypeName=$OrdDateStart." 至 ".$OrdDateEnd;
|
$OrdDateTypeName1="时间段 ".$OrdDateTypeName;
|
}
|
$SqlOrdDateType=" between '".$OrdDateStart."' and '".$OrdDateEnd." 23:59:59'";
|
}Else {
|
$sql = "select top 1 vID,vtext,vOrder2 from dictionary where vType>=1 and vtitle='OrdDateType' and vID=".$OrdDateType ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
if (sqlsrv_rows_affected($data)!=0) {
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$OrdDateTypeName = $rs['vtext'];
|
$OrdDateTypeName1 = $OrdDateTypeName;
|
$SqlOrdDateType = $rs['vOrder2'];
|
}
|
} else {
|
$OrdDateType="4";
|
$OrdDateTypeName="本月";
|
$OrdDateTypeName1=$OrdDateTypeName;
|
$SqlOrdDateType="between DATEADD(mm,DATEDIFF(mm,0,getdate()),0) and dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))";
|
|
}
|
}
|
|
}
|
|
//发票处理状态
|
$AuditStatusName="全部";
|
$AuditStatusArray=array();
|
$AuditStatusArray[0]=array(-1,"全部");
|
$sql = "select vID,vtext from dictionary where vType>=1 and vtitle='AuditStatus' order by vOrder" ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$i=1;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$AuditStatusArray[$i]=array($rs['vID'],$rs['vtext']);
|
if (strval($rs['vID'])==$AuditStatus){$AuditStatusName = $rs['vtext'];}
|
$i++;
|
}
|
}
|
|
//分公司名称列表
|
$UnitNameListArray=array();
|
$sql = "select ServiceBranch,case when UnitShort='微信小程序' then '广州' else UnitShort END as UnitShort from IntroducerUnitData where ServiceBranch<>'' and UnitID<>32" ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$i=1;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$UnitNameListArray[$i]=array($rs['ServiceBranch'],$rs['UnitShort']);
|
$i++;
|
}
|
}
|
|
//各种返回信息
|
$SystemMessageTXT="";
|
$SystemMessageType="";
|
|
//默认显示字段
|
$Report_Name = "发票管理";
|
|
$page_URL_noDate="h_menu1_1=2&searchTXT=".$searchTXT."&orderby=".$orderby."&InvoiceType=".$InvoiceType."&AuditStatus=".$AuditStatus."&OrdDateType=".$OrdDateType."&OrdDateStart=".$OrdDateStart."&OrdDateEnd=".$OrdDateEnd;
|
$page_URL="h_menu1_1=2&searchTXT=".$searchTXT."&orderby=".$orderby."&InvoiceType=".$InvoiceType."&AuditStatus=".$AuditStatus."&OrdDateType=".$OrdDateType."&OrdDateStart=".$OrdDateStart."&OrdDateEnd=".$OrdDateEnd;
|
?>
|
|
<?php
|
//分页信息
|
$page=empty($_REQUEST['page'])==true ? 1 : $_REQUEST['page']; //当前页数
|
$page=empty($draw)==true ? $page : $draw; //当前页数
|
$page_size = 20; //每页显示数量
|
$total_page=0; //总页数
|
$total_records=0; //总记录数
|
//查询条件
|
$searchSql="";
|
$searchTXT=empty($searchTXT)==true ? "" : $searchTXT;
|
if ($searchTXT!=""){
|
$searchSql=" and (InvoiceName like '%$searchTXT%' or InvoiceNo='$searchTXT') ";
|
}
|
elseif ($AuditStatus!=-1) {
|
$searchSql=" and AuditStatus=$AuditStatus ";
|
}else{
|
$searchSql=" and AuditStatus<>4 ";
|
}
|
$searchSql = $searchSql." and ApplicationTime ".$SqlOrdDateType;
|
|
$orderby=empty($orderby)==true ? "" : $orderby;
|
If (strrpos($orderby," desc")>1){
|
$orderbydesc="";
|
$descTXT="↓";
|
}else{
|
$orderbydesc=" desc";
|
$descTXT="↑";
|
}
|
|
If ($orderby=="") {
|
$orderby="OutHosp|InHosp desc,OutHospPrice|InHospPrice desc";
|
$descTXT="↓";
|
}
|
//echo $OrdClassListSql;
|
|
//查询开始
|
$InvoiceType=empty($InvoiceType)==true ? "1" : $InvoiceType;
|
//按调度单统计
|
//SQL查询条件
|
$sqlWhere=" from InvoiceData,ServiceOrder where ServiceOrderIDPK=ServiceOrdID".$searchSql;
|
//组成计算总页数SQL
|
$total_sql = "select InvoiceID $sqlWhere";
|
//组成显示结果SQL
|
$Result_sql = "select InvoiceID,InvoiceType,InvoiceName,InvoiceMakeout,InvoiceCompanyPhone,InvoiceCompanyID,InvoiceCompanyAdd,InvoiceCompanyBank,InvoiceCompanyBankNo,InvoiceZipCode,Invoice_strAdd,Invoice_strEmail,Invoice_strName,Invoice_strPhone,ApplicationTime=CONVERT(nvarchar(20),ApplicationTime,120),AuditTime=CONVERT(nvarchar(20),AuditTime,120),AuditStatus,AuditOAID,ApplyOAID,AuditMakeout,InvoiceMoney,InvoiceNo,InvoiceURL,ServiceOrdID,ServiceOrdType,ServiceOrdClass,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),OrdTraPrice=ServiceOrdTraTxnPrice+(select isnull(SUM(AddMoney),0) from PaidMoney_Add LEFT JOIN dictionary on vtitle='AddMoney' and vID=AddMoneyType where ToServiceOrdID=ServiceOrdID)-(select isnull(SUM(PaidMoney),0) from PaidMoney where (ServiceOrdIDDt=ServiceOrdID) and PaidMoney_AP_Check<>-1 and PaidMoneyType=9),EleCloud_ZTDM,EleCloud_ZTXX,EleCloud_Time=CONVERT(nvarchar(20),EleCloud_Time,120),row_number() over (order by ApplicationTime desc) n $sqlWhere";
|
//附加结果SQL
|
$Additional_sql = ",DispatchOrdNo=(select top 1 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) from DispatchOrd where ServiceOrdIDDt=ServiceOrdID order by DispatchOrdID desc)";
|
|
$sqlPage="" ;
|
|
$sql = "select * $Additional_sql from ($Result_sql) sqlData ".$sqlPage." order by sqlData.n" ;
|
//echo $sql;exit;
|
$data = sqlsrv_query($conn,$sql);
|
if (sqlsrv_rows_affected($data)==0) {
|
$SystemMessageType=2;
|
$SystemMessageTXT="数据库中相关无数据！";
|
}
|
$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 {
|
$i=1;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
//读取数据
|
$ServiceOrdID = $rs['ServiceOrdID']; //服务单ID
|
$ServiceOrdNo = $rs['ServiceOrdNo']; //服务单号
|
//$DispatchOrdID = $rs['DispatchOrdID']; //调度单ID
|
$DispatchOrdNo = $rs['DispatchOrdNo']; //调度单号
|
$ServiceOrdClass = $rs['ServiceOrdClass']; //对应服务单类型
|
|
$InvoiceID = $rs['InvoiceID']; //开票信息ID
|
$InvoiceType = $rs['InvoiceType']; //开票类型
|
$InvoiceName = $rs['InvoiceName']; //发票抬头
|
$InvoiceMakeout = $rs['InvoiceMakeout']; //发票备注
|
$InvoiceCompanyPhone= $rs['InvoiceCompanyPhone'];//企业电话
|
$InvoiceCompanyID = str_replace(array("\r\n", "\r", "\n"),'',$rs['InvoiceCompanyID']); //纳税识别号
|
$InvoiceCompanyAdd = $rs['InvoiceCompanyAdd']; //企业注册地址
|
$InvoiceCompanyBank = $rs['InvoiceCompanyBank'];//企业开户银行
|
$InvoiceCompanyBankNo=$rs['InvoiceCompanyBankNo'];//企业银行账号
|
$InvoiceZipCode = $rs['InvoiceZipCode']; //邮编
|
$Invoice_strAdd = $rs['Invoice_strAdd']; //邮寄地址
|
$Invoice_strEmail = $rs['Invoice_strEmail']; //电子邮箱
|
$Invoice_strName = $rs['Invoice_strName']; //联系人
|
$Invoice_strPhone = $rs['Invoice_strPhone']; //联系电话
|
$ApplicationTime = $rs['ApplicationTime']; //申请时间
|
$AuditTime = $rs['AuditTime']; //处理时间
|
$AuditStatus = $rs['AuditStatus']; //处理状态
|
$AuditOAID = $rs['AuditOAID']; //处理人
|
$AuditMakeout = $rs['AuditMakeout']; //处理备注
|
$ApplyOAID = $rs['ApplyOAID']; //申请人(0为客户申请)
|
$InvoiceMoney = $rs['InvoiceMoney']; //发票金额
|
$InvoiceNo = $rs['InvoiceNo']; //发票编号
|
$InvoiceURL = $rs['InvoiceURL']; //发票链接
|
$EleCloud_ZTDM = $rs['EleCloud_ZTDM']; //大象慧云-申请状态
|
$EleCloud_ZTXX = $rs['EleCloud_ZTXX']; //大象慧云-状态说明
|
$EleCloud_Time = $rs['EleCloud_Time']; //大象慧云-开票时间
|
|
$AuditOAName=empty($AuditOAID)==true ? "--" : OAUser($AuditOAID,"UserName");
|
if ($InvoiceID<=597) {
|
$ApplyOAName="--";
|
}else{
|
$ApplyOAName=empty($ApplyOAID)==true ? "客户" : OAUser($ApplyOAID,"UserName");
|
}
|
|
|
if ($InvoiceMoney==0) {$InvoiceMoney=$rs['OrdTraPrice'];}
|
|
//写入数组
|
$FormArray[$i][0]=$ServiceOrdNo;
|
$FormArray[$i][1]=$DispatchOrdNo;
|
$FormArray[$i][2]='';
|
for($z=1;$z<=count($UnitNameListArray);$z++) {
|
if ($UnitNameListArray[$z][0]==$ServiceOrdClass){
|
$FormArray[$i][2]=$UnitNameListArray[$z][1];
|
}
|
}
|
$FormArray[$i][3]=$ApplicationTime;
|
$FormArray[$i][4]=$InvoiceName;
|
$FormArray[$i][5]=floatval(number_format($InvoiceMoney,2,".",""));
|
$FormArray[$i][6]=$InvoiceMakeout;
|
for($z=0;$z<count($AuditStatusArray);$z++) {if ($AuditStatusArray[$z][0]==$AuditStatus){$FormArray[$i][7]=$AuditStatusArray[$z][1];}}
|
$FormArray[$i][8]=$InvoiceNo;
|
$FormArray[$i][9]=$ApplyOAName;
|
$FormArray[$i][10]=$AuditOAName;
|
$FormArray[$i][11]=$AuditTime;
|
$FormArray[$i][12]=$AuditMakeout;
|
$i++;
|
|
}
|
|
|
}
|
}
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
|
<?php
|
if ($SystemMessageType!="2"){
|
$dateTiem = date("YmdHis",time());
|
$filename=$Report_Name."(".TXTDateToDate($OrdDateTypeName).")".$dateTiem;
|
include_once('/PHPExcel/PHPExcel.php');
|
error_reporting(E_ALL);
|
ini_set('display_errors', TRUE);
|
ini_set('display_startup_errors', TRUE);
|
|
//创建对象
|
$excel = new PHPExcel();
|
|
//填充表头信息
|
for($x=0;$x<$FormArrayX;$x++) {
|
$excel->getActiveSheet()->setCellValue(IntToChr($x)."1",$FormArray[0][$x]);
|
}
|
|
//填充表格信息
|
for($y=1;$y<count($FormArray);$y++) {
|
for($x=0;$x<count($FormArray[$y]);$x++) {
|
$excel->getActiveSheet()->setCellValue(IntToChr($x).($y+1),strip_tags($FormArray[$y][$x]));
|
}
|
}
|
|
//创建Excel输入对象
|
$write = new PHPExcel_Writer_Excel5($excel);
|
ob_end_clean();
|
ob_start();
|
header("Pragma: public");
|
header("Expires: 0");
|
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
|
header("Content-Type:application/force-download");
|
header("Content-Type:application/vnd.ms-execl");
|
header("Content-Type:application/octet-stream");
|
header("Content-Type:application/download");;
|
header('Content-Disposition:attachment;filename="'.$filename.'.xls"');
|
header("Content-Transfer-Encoding:binary");
|
$write->save('php://output');
|
}else{
|
echo $SystemMessageTXT;
|
}
|
?>
|
