<?php
namespace app\models\common;

use yii\db\ActiveRecord;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use app\models\common\Power;
class Export extends ActiveRecord{
    //导出财务订单概况
    public static function BillOrderExport($uniacid,$storeId,$time){
        $store=(new \yii\db\Query())
            ->from('{{%ybwm_store}}')
            ->where('id=:id',[':id'=>$storeId])->one();

        $startTime=strtotime($time." 00:00:00");
        $endTime=strtotime($time." 23:59:59");

        $table = (new \yii\db\Query())
            ->select(['ifnull(b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential,0) as originMoney','ifnull((b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential)-b.money,0) as discountMoney','a.subsidy','a.orderId','b.profitSharing','b.profitSharingState','b.preferentialMoney','b.newMoney','b.deliveryPreferential','IFNULL(b.platformCouponPreferential,0) platformCouponPreferential','IFNULL(b.couponPreferential,0) couponPreferential','(b.preferentialMoney+b.newMoney+b.deliveryPreferential+IFNULL(b.platformCouponPreferential,0)+IFNULL(b.couponPreferential,0)) allPreferential','a.payMode','b.deliveryMode','b.takeNo','b.state','a.outTradeNo', 'a.origin', 'a.storeActualMoney', 'a.storeDeliveryMoney', 'a.storeBoxMoney', 'a.storeGoodsMoney', 'a.money', 'TRUNCATE(a.money-a.storeActualMoney+a.subsidy,2) serviceMoney', 'from_unixtime(a.createdAt) createdAt'])
            ->from('{{%ybwm_bill}} as a')
            ->join('LEFT JOIN', '{{%ybwm_takeout_order}} as b', 'b.outTradeNo = a.outTradeNo')
            ->where('a.uniacid=:uniacid AND a.storeId=:storeId AND a.statisticsAt>=:startTime AND a.statisticsAt<=:endTime AND a.origin=1',
                ['startTime' => $startTime, 'endTime' => $endTime, ':uniacid' => $uniacid, 'storeId' => $storeId]);
        $list=$table->orderBy('a.id desc')->all();
        for($i=0;$i<count($list);$i++){
            if ($list[$i]['state'] == 5 || $list[$i]['state'] == 6) {
                $stateName = '已完成';
            } elseif ($list[$i]['state'] == 2 || $list[$i]['state'] == 3 || $list[$i]['state'] ==4) {
                $stateName = '待完成';
            } elseif ($list[$i]['state'] == 9) {
                $stateName = '退款待处理';
            } elseif ($list[$i]['state'] == 10 || $list[$i]['state'] == 8) {
                $stateName = '已退款';
            }
            if($list[$i]['deliveryMode']==10){
                $deliveryModeName = '自提';
            }else{
                $deliveryModeName = '外卖';
            }
            if ($list[$i]['payMode'] == 1) {
                $payModeName = '微信支付';
            }elseif ($list[$i]['payMode'] ==2) {
                $payModeName = '支付宝支付';
            } elseif ($list[$i]['payMode'] == 5) {
                $payModeName = '余额支付';
            } elseif ($list[$i]['payMode'] == 10) {
                $payModeName = '货到付款';
            }
            $newList[] = [
                $list[$i]['takeNo'], $list[$i]['outTradeNo'], $deliveryModeName,$list[$i]['originMoney'],$list[$i]['discountMoney'],
                $list[$i]['money'],$payModeName,$list[$i]['subsidy'],$list[$i]['serviceMoney'],$list[$i]['storeActualMoney'],
                $list[$i]['storeBoxMoney'],$list[$i]['storeDeliveryMoney'],$stateName
            ];
        }
        $titleArr = ['订单序号', '订单编号','订单类型', '应收金额', '优惠金额', '实付金额','支付方式', '补贴',
            '平台服务费', '预计收入', '包装费', '配送费', '订单状态'];
        exportExcel($titleArr, $newList, $time."(".$store['name'].")订单概况");
    }
    //导出财务订单概况1
    public static function BillOrderExport1($uniacid,$storeId,$result){
        $store=(new \yii\db\Query())
            ->from('{{%ybwm_store}}')
            ->where('id=:id',[':id'=>$storeId])->one();
        $timeType = $result['timeType'];
        switch ($timeType){
            case 2://近七天
                $startTime=strtotime(date("Y-m-d",strtotime("-7 day")));
                $endTime=time();
                break;
            case 3://近十五天
                $startTime=strtotime(date("Y-m-d",strtotime("-15 day")));
                $endTime=time();
                break;
            case 4://具体日期
                $startTime=strtotime($result['startTime']);
                $endTime=strtotime($result['endTime'])+86399;
                break;
            case 1://今天
            default:
                $startTime=strtotime(date("Y-m-d",time()));
                $endTime=strtotime(date("Y-m-d",time()))+86399;
                break;
        }

        $table = (new \yii\db\Query())
            ->select(['ifnull(b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential,0) as originMoney','ifnull((b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential)-b.money,0) as discountMoney','a.subsidy','a.orderId','b.profitSharing','b.profitSharingState','b.preferentialMoney','b.newMoney','b.deliveryPreferential','IFNULL(b.platformCouponPreferential,0) platformCouponPreferential','IFNULL(b.couponPreferential,0) couponPreferential','(b.preferentialMoney+b.newMoney+b.deliveryPreferential+IFNULL(b.platformCouponPreferential,0)+IFNULL(b.couponPreferential,0)) allPreferential','a.payMode','b.deliveryMode','b.takeNo','b.state','a.outTradeNo', 'a.origin', 'a.storeActualMoney', 'a.storeDeliveryMoney', 'a.storeBoxMoney', 'a.storeGoodsMoney', 'a.money', 'TRUNCATE(a.money-a.storeActualMoney+a.subsidy,2) serviceMoney', 'from_unixtime(a.createdAt) createdAt'])
            ->from('{{%ybwm_bill}} as a')
            ->join('LEFT JOIN', '{{%ybwm_takeout_order}} as b', 'b.outTradeNo = a.outTradeNo')
            ->where('a.uniacid=:uniacid AND a.storeId=:storeId AND a.statisticsAt>=:startTime AND a.statisticsAt<=:endTime AND a.origin=1',
                ['startTime' => $startTime, 'endTime' => $endTime, ':uniacid' => $uniacid, 'storeId' => $storeId]);


        $list=$table->orderBy('a.id desc')->all();
        for($i=0;$i<count($list);$i++){
            if ($list[$i]['state'] == 5 || $list[$i]['state'] == 6) {
                $stateName = '已完成';
            } elseif ($list[$i]['state'] == 2 || $list[$i]['state'] == 3 || $list[$i]['state'] ==4) {
                $stateName = '待完成';
            } elseif ($list[$i]['state'] == 9) {
                $stateName = '退款待处理';
            } elseif ($list[$i]['state'] == 10 || $list[$i]['state'] == 8) {
                $stateName = '已退款';
            }
            if($list[$i]['deliveryMode']==10){
                $deliveryModeName = '自提';
            }else{
                $deliveryModeName = '外卖';
            }
            if ($list[$i]['payMode'] == 1) {
                $payModeName = '微信支付';
            }elseif ($list[$i]['payMode'] ==2) {
                $payModeName = '支付宝支付';
            } elseif ($list[$i]['payMode'] == 5) {
                $payModeName = '余额支付';
            } elseif ($list[$i]['payMode'] == 10) {
                $payModeName = '货到付款';
            }
            $newList[] = [
                $list[$i]['takeNo'], $list[$i]['outTradeNo'], $deliveryModeName,$list[$i]['originMoney'],$list[$i]['discountMoney'],
                $list[$i]['money'],$payModeName,$list[$i]['subsidy'],$list[$i]['serviceMoney'],$list[$i]['storeActualMoney'],
                $list[$i]['storeBoxMoney'],$list[$i]['storeDeliveryMoney'],$stateName
            ];
        }
        $titleArr = ['订单序号', '订单编号','订单类型', '应收金额', '优惠金额', '实付金额','支付方式', '补贴',
            '平台服务费', '预计收入', '包装费', '配送费', '订单状态'];
        exportExcel($titleArr, $newList, $result['startTime'].'~'.$result['endTime']."(".$store['name'].")订单概况");
    }


    //导出财务其他订单概况
    public static function BillOtherOrderExport($uniacid,$storeId,$time){
        $store=(new \yii\db\Query())
            ->from('{{%ybwm_store}}')
            ->where('id=:id',[':id'=>$storeId])->one();
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $startTime=strtotime($time." 00:00:00");
        $endTime=strtotime($time." 23:59:59");
        $table = (new \yii\db\Query())
            ->select('a.outTradeNo,a.origin,a.money,a.payMode,b.userName,a.userId,from_unixtime(a.createdAt) createdAt,a.storeActualMoney')
            ->from('{{%ybwm_bill}} as a')
            ->join('LEFT JOIN', '{{%ybwm_member}} as b', 'b.id = a.userId')
            ->where('a.uniacid=:uniacid AND a.storeId=:storeId AND a.statisticsAt>=:startTime AND a.statisticsAt<=:endTime  AND a.origin!=1',
                ['startTime' => $startTime, 'endTime' => $endTime, ':uniacid' => $uniacid, 'storeId' => $storeId]);
        $res=$table->orderBy('a.id desc')->all();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle($time.$store['name']."其他订单概况");  //设置当前sheet的标题
        for($i=0;$i<11;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '订单编号')
            ->setCellValue('B1', '订单类型')
            ->setCellValue('C1', '用户实付')
            ->setCellValue('D1', '支付方式')
            ->setCellValue('E1', '用户信息')
            ->setCellValue('F1', '购买时间')
            ->setCellValue('G1', '结束金额')
            ->setCellValue('H1', '订单状态');

        //->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
        foreach ($res as $k => $val) {
            $k = $k + 2;
            if ($val['payMode'] == 1) {
                $payModeName = '微信支付';
            } elseif ($val['payMode'] == 5) {
                $payModeName = '余额支付';
            }
            if ($val['origin'] == 4) {
                $originName = '券包';
            }
            $userName=$val['userName']."（ID：".$val['userId']."）";

            $objSheet->setCellValue('A' . $k, $val['outTradeNo'])
                ->setCellValue('B' . $k, $originName)
                ->setCellValue('C' . $k, $val['money'])
                ->setCellValue('D' . $k, $payModeName)
                ->setCellValue('E' . $k, $userName)
                ->setCellValue('F' . $k, $val['createdAt'])
                ->setCellValue('G' . $k, $val['storeActualMoney'])
                ->setCellValue('H' . $k, '已完成');

        }

        downloadExcel($newExcel, $time.$store['name']."其他订单概况", 'Xls');
    }
    //导出财务其他订单概况1
    public static function BillOtherOrderExport1($uniacid,$storeId,$result){
        $store=(new \yii\db\Query())
            ->from('{{%ybwm_store}}')
            ->where('id=:id',[':id'=>$storeId])->one();

        $timeType = $result['timeType'];
        switch ($timeType){
            case 2://近七天
                $startTime=strtotime(date("Y-m-d",strtotime("-7 day")));
                $endTime=time();
                break;
            case 3://近十五天
                $startTime=strtotime(date("Y-m-d",strtotime("-15 day")));
                $endTime=time();
                break;
            case 4://具体日期
                $startTime=strtotime($result['startTime']);
                $endTime=strtotime($result['endTime'])+86399;
                break;
            case 1://今天
            default:
                $startTime=strtotime(date("Y-m-d",time()));
                $endTime=strtotime(date("Y-m-d",time()))+86399;
                break;
        }


        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
//        $startTime=strtotime($time." 00:00:00");
//        $endTime=strtotime($time." 23:59:59");
        $table = (new \yii\db\Query())
            ->select('a.outTradeNo,a.origin,a.money,a.payMode,b.userName,a.userId,from_unixtime(a.createdAt) createdAt,a.storeActualMoney')
            ->from('{{%ybwm_bill}} as a')
            ->join('LEFT JOIN', '{{%ybwm_member}} as b', 'b.id = a.userId')
            ->where('a.uniacid=:uniacid AND a.storeId=:storeId AND a.statisticsAt>=:startTime AND a.statisticsAt<=:endTime  AND a.origin!=1',
                ['startTime' => $startTime, 'endTime' => $endTime, ':uniacid' => $uniacid, 'storeId' => $storeId]);
        $res=$table->orderBy('a.id desc')->all();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle($result['startTime'].$store['name']."其他订单概况");  //设置当前sheet的标题
        for($i=0;$i<11;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '订单编号')
            ->setCellValue('B1', '订单类型')
            ->setCellValue('C1', '用户实付')
            ->setCellValue('D1', '支付方式')
            ->setCellValue('E1', '用户信息')
            ->setCellValue('F1', '购买时间')
            ->setCellValue('G1', '结束金额')
            ->setCellValue('H1', '订单状态');

        //->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
        foreach ($res as $k => $val) {
            $k = $k + 2;
            if ($val['payMode'] == 1) {
                $payModeName = '微信支付';
            } elseif ($val['payMode'] == 5) {
                $payModeName = '余额支付';
            }
            if ($val['origin'] == 4) {
                $originName = '券包';
            }
            $userName=$val['userName']."（ID：".$val['userId']."）";

            $objSheet->setCellValue('A' . $k, $val['outTradeNo'])
                ->setCellValue('B' . $k, $originName)
                ->setCellValue('C' . $k, $val['money'])
                ->setCellValue('D' . $k, $payModeName)
                ->setCellValue('E' . $k, $userName)
                ->setCellValue('F' . $k, $val['createdAt'])
                ->setCellValue('G' . $k, $val['storeActualMoney'])
                ->setCellValue('H' . $k, '已完成');

        }

        downloadExcel($newExcel, $time.$store['name']."其他订单概况", 'Xls');
    }

    //导出兑换码
    public static function ExchangeCodeExport($id){
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $exchangeCode=(new \yii\db\Query())
            ->select('name')
            ->from('{{%ybwm_exchange_code}}')
            ->where('id=:id',[':id'=>$id])->one();
        $exchangeCodeInfo=(new \yii\db\Query())
            ->select('code,state,changeAt')
            ->from('{{%ybwm_exchange_code_info}}')
            ->where('codeId=:codeId',[':codeId'=>$id]);
        $res=$exchangeCodeInfo->orderBy('id desc')->all();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle($exchangeCode['name']."兑换码列表");  //设置当前sheet的标题
        for($i=0;$i<3;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '兑换码')
            ->setCellValue('B1', '状态')
            ->setCellValue('C1', '兑换时间');
        foreach ($res as $k => $val) {
            $k = $k + 2;
            if ($val['state'] == 1) {
                $stateName = '已兑换';
            } else{
                $stateName = '未兑换';
            }
            $val['changeAt']=$val['changeAt']?date("Y-m-d H:i:s",$val['changeAt']):'--';
            $objSheet->setCellValue('A' . $k, $val['code'])
                ->setCellValue('B' . $k, $stateName)
                ->setCellValue('C' . $k, $val['changeAt']);

        }

        downloadExcel($newExcel, $exchangeCode['name']."兑换码列表", 'Xls');
    }
    //订单导出
    public static function OrderExport($res,$uniacid){
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle("订单列表");  //设置当前sheet的标题
        for($i=0;$i<13;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '订单编号')
            ->setCellValue('B1', '商品信息')
            ->setCellValue('C1', '订单状态')
            ->setCellValue('D1', '订单备注')
            ->setCellValue('E1', '收货人')
            ->setCellValue('F1', '收货电话')
            ->setCellValue('G1', '收货地址')
            ->setCellValue('H1', '配送方式')
            ->setCellValue('I1', '订单金额')
            ->setCellValue('J1', '退款金额')
            ->setCellValue('K1', '付款方式')
            ->setCellValue('L1', '付款时间')
            ->setCellValue('M1', '创建时间');
        $set=Power::getPower($uniacid,'makeName');
        $yunbeiName=Power::getPower($uniacid,'yunbeiName');

        foreach ($res as $k => $val) {
            $goods='';
            $k = $k + 2;
            $goodsInfo=(new \yii\db\Query())
                ->select('name,data,material,attribute,num')
                ->from('{{%ybwm_order_goods}}')
                ->where('orderId=:orderId AND item=1',[':orderId'=>$val['id']])->all();
            for($o=0;$o<count($goodsInfo);$o++){
                $spec = '';
                $attribute = '';
                if ($goodsInfo[$o]['data']) {
                    $goodsInfo[$o]['data'] = str_replace(",", "，", $goodsInfo[$o]['data']);
                    $spec = '规格：' . $goodsInfo[$o]['data'] . " ";
                }
                if ($goodsInfo[$o]['material']) {
                    $material = "加料：" . $goodsInfo[$o]['material'] . " ";
                }
                if ($goodsInfo[$o]['attribute']) {
                    $goodsInfo[$o]['attribute'] = str_replace(",", "，", $goodsInfo[$o]['attribute']);
                    $attribute = '属性：' . $goodsInfo[$o]['attribute'] . " ";
                }
                if ($spec || $material || $attribute) {
                    $goods .= $goodsInfo[$o]['name'] . "(" . $spec . $material . $attribute . ")×" . $goodsInfo[$o]['num'] . "；";
                } else {
                    $goods .= $goodsInfo[$o]['name'] . "×" . $goodsInfo[$o]['num'] . "；";
                }
            }
            if($val['state'] == 2){
                $stateName = '已付款';
            }elseif($val['state'] == 3){
                $stateName = '已接单';
            }elseif($val['state'] == 4){
                $stateName = '配送中';
            }elseif($val['state'] == 5){
                $stateName = '已收货';
            }elseif($val['state'] == 6){
                $stateName = '已评价';
            }elseif($val['state'] == 7){
                $stateName = '已取消';
            }elseif($val['state'] == 8){
                $stateName = '拒单';
            }elseif($val['state'] == 9){
                $stateName = '申请退款';
            }elseif($val['state'] == 10){
                $stateName = '已退款';
            }else{
                $stateName = '未付款';
            }
            if($val['deliveryMode']==2){
                $deliveryName='达达配送';
            }elseif($val['deliveryMode']==3){
                $deliveryName='点我达配送';
            }elseif($val['deliveryMode']==4){
                $deliveryName=$set['makeName']?:'码科配送';
            }elseif($val['deliveryMode']==5){
                $deliveryName='顺丰配送';
            }elseif($val['deliveryMode']==6){
                $deliveryName='蜂鸟跑腿';
            }elseif($val['deliveryMode']==7){
                $deliveryName='闪送';
            }elseif($val['deliveryMode']==8){
                $deliveryName='UU跑腿';
            }elseif($val['deliveryMode']==9){
                $deliveryName=$yunbeiName['yunbeiName']?:'云贝配送';
            }elseif($val['deliveryMode']==10){
                $deliveryName='到店自提';
            }else{
                $deliveryName='商家配送';
            }
            if($val['payMode']==5){
                $payMode='余额支付';
            }else{
                $payMode='微信支付';
            }

            $val['payAt']=$val['payAt']?date("Y-m-d H:i:s",$val['payAt']):'--';
            $objSheet->setCellValue('A' . $k, $val['outTradeNo'])
                ->setCellValue('B' . $k, $goods)
                ->setCellValue('C' . $k, $stateName)
                ->setCellValue('D' . $k, $val['userNote']?:'--')
                ->setCellValue('E' . $k, $val['receivedName']?:'--')
                ->setCellValue('F' . $k, $val['receivedTel']?:'--')
                ->setCellValue('G' . $k, $val['receivedAddress']?:'--')
                ->setCellValue('H' . $k, $deliveryName)
                ->setCellValue('I' . $k, $val['money'])
                ->setCellValue('J' . $k, $val['refundMoney']?:'--')
                ->setCellValue('K' . $k, $payMode)
                ->setCellValue('L' . $k, $val['payAt'])
                ->setCellValue('M' . $k, $val['createdAt']);
        }

        downloadExcel($newExcel, "订单列表", 'Xls');
    }
    //储值订单
    public static function RechargeExport($res){
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle("储值列表");  //设置当前sheet的标题
        for($i=0;$i<5;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '用户信息')
            ->setCellValue('B1', '储值金额')
            ->setCellValue('C1', '赠送信息')
            ->setCellValue('D1', '订单编号')
            ->setCellValue('E1', '储值时间');


        foreach ($res as $k => $val) {
            $k = $k + 2;
            $giveName='';
            if($val['giveMoney']){
                $giveName.="赠送余额".$val['giveMoney'];
            }
            if($val['giveScore']){
                $giveName.="赠送积分".$val['giveScore'];
            }
            if($val['giveGrow']){
                $giveName.="赠送成长值".$val['giveGrow'];
            }
            $objSheet->setCellValue('A' . $k, $val['userName']."（用户ID：".$val['userId']."）")
                ->setCellValue('B' . $k, $val['money'])
                ->setCellValue('C' . $k, $giveName)
                ->setCellValue('D' . $k, $val['outTradeNo'])
                ->setCellValue('E' . $k, $val['createdAt']?:'--');

        }

        downloadExcel($newExcel, "储值列表", 'Xls');
    }
    //兑换记录
    public static function ExchangeExport($res){
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle("兑换记录");  //设置当前sheet的标题
        for($i=0;$i<11;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '用户信息')
            ->setCellValue('B1', '订单编号')
            ->setCellValue('C1', '商品名称')
            ->setCellValue('D1', '商品类型')
            ->setCellValue('E1', '兑换数量')
            ->setCellValue('F1', '兑换价格')
            ->setCellValue('G1', '兑换时间')
            ->setCellValue('H1', '收货人')
            ->setCellValue('I1', '收货电话')
            ->setCellValue('J1', '收货地址')
            ->setCellValue('K1', '状态');
        foreach ($res as $k => $val) {
            $k = $k + 2;
            if($val['goodsType']==1){
                $goodsType='实物';
            }else{
                $goodsType='虚拟';
            }
            $money=$val['score']."积分";
            if($val['money']){
                $money.="+".$val['score']."元";
            }
            if($val['state'] == 2){
                $stateName = '已付款';
            }elseif($val['state'] == 3){
                $stateName = '已发货';
            }elseif($val['state'] == 4){
                $stateName = '已完成';
            }else{
                $stateName = '未付款';
            }
            $objSheet->setCellValue('A' . $k, $val['userName']."（用户ID：".$val['userId']."）")
                ->setCellValue('B' . $k, $val['outTradeNo'])
                ->setCellValue('C' . $k, $val['goodsName'])
                ->setCellValue('D' . $k, $goodsType)
                ->setCellValue('E' . $k, $val['convertNum'])
                ->setCellValue('F' . $k, $money)
                ->setCellValue('G' . $k, $val['createdAt'])
                ->setCellValue('H' . $k, $val['receiveName']?:'--')
                ->setCellValue('I' . $k, $val['receiveTel']?:'--')
                ->setCellValue('J' . $k, $val['receiveAddress']?:'--')
                ->setCellValue('K' . $k, $stateName);

        }

        downloadExcel($newExcel, "兑换记录", 'Xls');
    }
    //用户导出
    public static function UserExport($res){
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objSheet->setTitle("用户导出");  //设置当前sheet的标题
        for($i=0;$i<10;$i++){
            $newExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(15);
        }
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '用户信息')
            ->setCellValue('B1', '用户来源')
            ->setCellValue('C1', '用户手机号')
            ->setCellValue('D1', '会员等级')
            ->setCellValue('E1', '订单数')
            ->setCellValue('F1', '订单金额')
            ->setCellValue('G1', '积分')
            ->setCellValue('H1', '余额')
            ->setCellValue('I1', '注册时间')
            ->setCellValue('J1', '拉黑状态');
        foreach ($res as $k => $val) {
            $k = $k + 2;
            $bindType='--';
            if($val['bindType']==1){
                $bindType='微信小程序';
            }elseif($val['bindType']==3){
                $bindType='支付宝小程序';
            }elseif($val['bindType']==4){
                $bindType='百度小程序';
            }elseif($val['bindType']==7){
                $bindType='头条小程序';
            }
            if($val['userName'] && strpos($val['userName'],'=') === 0 ){
                $val['userName'] = "'".$val['userName'];
            }
            $objSheet->setCellValue('A' . $k, $val['userName']."（用户ID：".$val['id']."）")
                ->setCellValue('B' . $k, $bindType)
                ->setCellValue('C' . $k, $val['userTel']?:'--')
                ->setCellValue('D' . $k, $val['level']?"等级".$val['level']:"--")
                ->setCellValue('E' . $k, $val['payNum'])
                ->setCellValue('F' . $k, $val['payMoney'])
                ->setCellValue('G' . $k, $val['integral'])
                ->setCellValue('H' . $k, $val['balance'])
                ->setCellValue('I' . $k, $val['createdAt']?:'--')
                ->setCellValue('J' . $k, $val['isBlack']==1?'拉黑':'正常');

        }

        downloadExcel($newExcel, "用户列表", 'Xls');
    }

}