//执行导出Excel
function exporder ()
{
$_GET=array_merge($_GET,$_POST);
$_POST=array_merge($_GET,$_POST);
if(strlen(I('orderid'))>0) {
$where['orderid']=array('like',"%".I('orderid')."%");
}
$start_time = I('start_time');
$end_time = I('end_time');
if(!empty($start_time) && !empty($end_time)) {
$where["createtime"] = array(between,array($start_time,$end_time));
}else{
if(empty($start_time) && !empty($end_time)) {
$where["createtime"] = array("ELT",$end_time);
}
if(!empty($start_time) && empty($end_time)) {
$where["createtime"] = array("EGT",$start_time);
}
}
if(strlen(I('merchantcode'))>0) {
$where['merchantcode']=array('like',"%".I('merchantcode')."%");
}
if(strlen(I('storename'))>0) {
$where['storename']=array('like',"%".I('storename')."%");
}
if(strlen(I('devicename'))>0) {
$where['devicename']=array('like',"%".I('devicename')."%");
}
if(strlen(I('agentname'))>0) {
$where['agentname|agentid']=array('like',"%".I('agentname')."%");
}
if(strlen(I('paystate'))>0) {
$where['paystate']=I('paystate');
}
//$where['parentid']='0';
$countsql = "CALL weyun_procedure_ordersearch('0','".I('merchantcode')."','".I('storename')."','".I('devicename')."','','".I('agentname')."','".I('orderid')."','".I('paystate')."','".I('start_time')."','".I('end_time')."','')";
$countsql = M()->query($countsql);
$count=$countsql[0]['count'];
if (empty($count))
{
$page = $this->page($count, 15);
}
else
{
$page = $this->page($count, $count);
}
$limit=$page->firstRow.','.$page->listRows;
$sql = "CALL weyun_procedure_ordersearch('1','".I('merchantcode')."','".I('storename')."','".I('devicename')."','','".I('agentname')."','".I('orderid')."','".I('paystate')."','".I('start_time')."','".I('end_time')."','".$limit."')";
$data = M()->query($sql);
$this->order_export($data);
}
//导出数据方法
protected function order_export($order_list=array())
{
$paystates=array("0"=>未支付,"1"=>已支付,"2"=>支付失败,"3"=>退款);
//print_r($goods_list);exit;
$order_list = $order_list;
$data = array();
foreach ($order_list as $k=>$order_info){
$data[$k][orderid] = $order_info['orderid'].' ';
$data[$k][createtime] = $order_info['createtime'];
$data[$k][merchantcode] = $order_info['merchantcode'].' ';
$data[$k][merchantname] = $order_info['merchantname'];
$data[$k][storename] = $order_info['storename'];
$data[$k][deviceid] = $order_info['deviceid'].' ';
$data[$k][devicename] = $order_info['devicename'];
$data[$k][agentid] = $order_info['agentid'].' ';
$data[$k][agentname] = $order_info['agentname'];
$data[$k][paystate] = $paystates[$order_info['paystate']];
$data[$k][price] = $order_info['price'];
$data[$k][cash_fee] = $order_info['cash_fee'];
$data[$k][coupon_fee] = $order_info['coupon_fee'];
$data[$k][poundage] = $order_info['raremoney'];
$data[$k][cardtype] = $order_info['cardtype'];
$data[$k][cardbank] = $order_info['cardbank'];
$data[$k][bankname] = $order_info['bankname'].' ';
$data[$k][idcard] = $order_info['idcard'].' ';
$data[$k][bank] = $order_info['bank'].' ';
$data[$k][bankid] = $order_info['bankid'].' ';
$data[$k][cnaps] = $order_info['cnaps'].' ';
$data[$k][bizLIC] = $order_info['bizLIC'].' ';
}
//print_r($goods_list);
//print_r($data);exit;
foreach ($data as $field=>$v){
if($field == 'orderid'){
$headArr[]='订单编号';
}
if($field == 'createtime'){
$headArr[]='交易时间';
}
if($field == 'merchantcode'){
$headArr[]='商户号';
}
if($field == 'merchantname'){
$headArr[]='商户名称';
}
if($field == 'storename'){
$headArr[]='门店名称';
}
if($field == 'deviceid'){
$headArr[]='设备号';
}
if($field == 'devicename'){
$headArr[]='设备名称';
}
if($field == 'agentid'){
$headArr[]='所属渠道编号';
}
if($field == 'agentname'){
$headArr[]='所属渠道名称';
}
if($field == 'paystate'){
$headArr[]='交易状态';
}
if($field == 'price'){
$headArr[]='交易金额';
}
if($field == 'cash_fee'){
$headArr[]='实付金额';
}
if($field == 'coupon_fee'){
$headArr[]='优惠金额';
}
if($field == 'poundage'){
$headArr[]='交易手续费';
}
if($field == 'cardtype'){
$headArr[]='付款人卡类型';
}
if($field == 'cardbank'){
$headArr[]='付款人发卡行';
}
if($field == 'bankname'){
$headArr[]='收款人姓名';
}
if($field == 'idcard'){
$headArr[]='收款人证件号';
}
if($field == 'bank'){
$headArr[]='收款人开户行';
}
if($field == 'bankid'){
$headArr[]='收款人银行卡号';
}
if($field == 'cnaps'){
$headArr[]='收款人联行卡号';
}
if($field == 'bizLIC'){
$headArr[]='收款人营业执照注册号';
}
}
$filename="订单报表";
$this->getExcel($filename,$headArr,$data);
}
/**
+----------------------------------------------------------
* getExcel | 2016/11/15
* Author:JBR
+----------------------------------------------------------
* @param $fileName string File name
+----------------------------------------------------------
* @param $headArr array Column name
+----------------------------------------------------------
* @param $data array Table data
+----------------------------------------------------------
*/
private function getExcel($fileName,$headArr,$data){
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Writer.Excel5");
import("Org.Util.PHPExcel.IOFactory.php");
$date = date("Y_m_d",time());
$fileName .= "_{$date}.xls";
//创建PHPExcel对象，注意，不能少了\
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Document")
->setSubject("Office 2007 XLSX Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$objProps = $objPHPExcel->getProperties();
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension(1)->setRowHeight(35);
$objPHPExcel->setActiveSheetIndex(0)->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置表头
$key = ord("A");
//print_r($headArr);exit;
foreach($headArr as $v){
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension($colum)->setWidth(20);
$key += 1;
}
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
//print_r($data);exit;
foreach($data as $key => $rows){ //行写入
$span = ord("A");
foreach($rows as $keyName=>$value){// 列写入
$j = chr($span);
$objActSheet->setCellValue($j.$column, $value);
$span++;
}
$column++;
}
//$fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
//$objPHPExcel->getActiveSheet()->setTitle('test');
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
exit;
}
