<?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;
}
?>