guoyao-jiankang/application/admin/controller/DinnerOrder.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;
use think\Exception;

class DinnerOrder extends Auth
{

    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->table='dinner_order';
        $this->model= new \app\common\model\DinnerOrder();
    }
    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $offset = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('title','','trim');
            if($title){
                $map[] = ['a.name|a.sn','like','%'.$title.'%'];
            }
            $is_ok = input('is_ok',0,'trim');
            if($is_ok>=0){
                $map[] = ['a.is_ok','=',$is_ok];
            }
            $is_spot = input('is_spot/d',0);
            if($is_spot == 2){
                $map[] = ['a.cate','=',1];

                $start = input('start',date('Y-m-d'),'trim');
                $oids = Db::name('dinner_order_item')
                    ->where('day',$start)
                    ->group('order_id')
                    ->distinct(true)
                    ->column('order_id');
                if($oids){
                    $map[] = ['a.id','in',$oids];
                }else{
                    $map[] = ['a.id','=',-1];
                }

            }else{
                $map[] = ['a.is_spot','=',$is_spot];
                $map[] = ['a.cate','=',0];

                $start = input('start',date('Y-m-d'),'trim');
                $map[] = ['a.ymd','=',date('Ymd',strtotime($start))];
            }

            $dinner_type_id = input('type','','trim');
            if($dinner_type_id != ''){
                $dinner_order_item = Db::name('dinner_order_item')
                    ->where('dinner_type_id',$dinner_type_id)
                    ->where('is_refuse',0)
                    ->column('order_id');
                if(empty($dinner_order_item)){
                    $map[] = ['a.id','=',-1];

                }else{
                    $map[] = ['a.id','in',$dinner_order_item];

                }
            }
            $map[] = ['a.org_id','=',$this->orgId];
            $map[] = ['a.state','<>',0];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = Db::name($this->table)
                ->alias('a')
                ->field('a.*')
                ->where($map)->limit($offset,$length)->order(
                ['a.id'=>'desc']
            )->select();
            foreach ($lists as $k=>$v){
                $dmap = [];
                $dmap[] = ['a.order_id','=',$v['id']];
                $dmap[] = ['a.is_refuse','=',0];
                if($dinner_type_id > 0){
                    $dmap[] = ['a.dinner_type_id','=',$dinner_type_id];
                }
                if($is_spot == 2){
                    $dmap[] = ['a.day','=',$start];
                }
                $all = Db::name('dinner_order_item')
                    ->alias('a')
                    ->join('dinner b','a.dinner_id=b.id')
                    ->join('dinner_type c','a.dinner_type_id=c.id')
                    ->field('a.*,b.money as oldmoney,b.name as dinner_name,c.color,c.name as dinner_type_name')
                    ->where($dmap)
                    ->select();

                $typeIdS = $all?array_unique(array_column($all,'dinner_type_id')):[];
                $typeName = '';
                foreach ($typeIdS as $k1=>$v1){
                    $tInfo = Db::name('dinner_type')
                        ->where('id',$v1)
                        ->find();
                    $typeName.='<span class="label" style="border-radius:0rem !important;background-color: '.$tInfo['color'].';color: white">'.$tInfo['name'].'</span>';
                    if(count($typeIdS)>1 && $k1<count($typeIdS)-1){
                        $typeName.='<br/>';
                    }
                }
                $lists[$k]['tName'] = $typeName;
                $item = '';
                foreach ($all as $k2=>$v2){
                    if($v2['money'] == 0 && $v2['oldmoney'] > 0){
                        $v2['money'] = $v2['oldmoney'];
                    }
                    $ss = $k2+1;
                    if($ss<10){
                        $ss= '0'.$ss;
                    }
                    $ur = url('dinner/detail').'?id='.$v2['dinner_id'];
                    $item.=''.$ss.',<span class="label" style="border-radius:0rem !important;background-color: '.$v2['color'].';color: white">'.$v2['dinner_type_name'].'</span><a  data-title="详情" href="javascript:;" url="'.$ur.'">'.$v2['dinner_name'].'*'.$v2['num'];
                    if(count($all)>1 && $k2<count($all)){
                        $item.='<br/>';
                    }
                }
                $lists[$k]['item'] = $item;

            }
            //数据返回
            $totalCount = db($this->table)
                ->alias('a')
                ->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{

            $this->assign('start',date('Y-m-d'));
            $typeList = model('DinnerType')->getList($this->orgId);
            $this->assign('typeList',$typeList);
            return $this->fetch();
        }
    }


    public function index1(){

        $is_spot = input('is_spot/d',0);
        if($is_spot == 2){
            $map[] = ['cate','=',1];

            $start = input('start',date('Y-m-d'),'trim');
            $oids = Db::name('dinner_order_item')
                ->where('day',$start)
                ->group('order_id')
                ->distinct(true)
                ->column('order_id');
            if($oids){
                $map[] = ['id','in',$oids];
            }else{
                $map[] = ['id','=',-1];
            }

        }else{
            $map[] = ['is_spot','=',$is_spot];
            $map[] = ['cate','=',0];

            $start = input('start',date('Y-m-d'),'trim');
            $map[] = ['ymd','=',date('Ymd',strtotime($start))];
        }

        $map[] = ['org_id','=',$this->orgId];
        $map[] = ['state','<>',0];
        $map= empty($map) ? true: $map;
        //数据查询
        $lists = Db::name($this->table)->where($map)->column('id');
        $newArr = [];
        $type = [];
        $rows = [];
        if(!empty($lists)){
            $dmap[] = ['a.order_id','in',$lists];
            $dmap[] = ['a.is_refuse','=',0];
            if($is_spot == 2){
                $dmap[] = ['a.day','=',$start];
            }

            $all = Db::name('dinner_order_item')
                ->alias('a')
                ->join('dinner_type c','a.dinner_type_id=c.id')
                ->field('c.id,a.order_id,c.color,c.name as dinner_type_name')
                ->where($dmap)
                ->group('a.dinner_type_id')
                ->select();
            $type = $all;
            $maxrow = 0;
            foreach ($type as $k=>$v){
                $total = Db::name('dinner_order_item')
                    ->alias('a')
                    ->join('dinner b','a.dinner_id=b.id')
                    ->join('dinner_type c','a.dinner_type_id=c.id')
                    ->field('sum(a.num) as nums,b.name as dinner_name,c.color,c.name as dinner_type_name')
                    ->where('a.dinner_type_id',$v['id'])
                    ->where($dmap)
                    ->group('a.dinner_id')
                    ->select();
                $total = $total?$total:[];
                $count = count($total);
                if($count > $maxrow){
                    $maxrow = $count;
                }
                $newArr[] = $total;
            }


            $i = 0;
            while (true){
                if($maxrow <= 0){
                    break;
                }

                $d = [];
                foreach ($type as $k=>$v){
                    $dd = $newArr[$k];
                    $d['name'.$v['id']] = isset($dd[$i])?$dd[$i]['dinner_name']:'';
                    $d['nums'.$v['id']] = isset($dd[$i])?$dd[$i]['nums']:'';
                }
                $rows[] = $d;
                $maxrow--;
                $i++;
            }
        }

        $this->assign('type',$type);
        $this->assign('data',$rows);
        $this->assign('is_spot',$is_spot);
        $this->assign('start',$start);
        return $this->fetch();
    }
    public function day(){
        $cate = input('cate/d',0);
        $this->assign('cate',$cate);
        $map[] = ['cate','=',$cate];
        if($cate == 1){
            $start = input('start',date('Y-m-d'),'trim');
            $oids = Db::name('dinner_order_item')
                ->where('day',$start)
                ->group('order_id')
                ->distinct(true)
                ->column('order_id');
            if($oids){
                $map[] = ['id','in',$oids];
            }else{
                $map[] = ['id','=',-1];
            }
        }else{
            $start = input('start',date('Y-m-d'),'trim');
            $map[] = ['ymd','=',date('Ymd',strtotime($start))];
        }

        $map[] = ['org_id','=',$this->orgId];
        $map[] = ['state','<>',0];
        $map= empty($map) ? true: $map;


        //数据查询
        $lists = Db::name($this->table)
            ->where($map)->column('id');
        $newArr = [];
        $type = [];
        $rows = [];
        if(!empty($lists)){
            $dmap = [];
            $dmap[] = ['a.order_id','in',$lists];
            $dmap[] = ['a.is_refuse','=',0];
            if($cate == 1){
                $dmap[] = ['a.day','=',$start];
            }

            $all = Db::name('dinner_order_item')
                ->alias('a')
                ->join('dinner_type c','a.dinner_type_id=c.id')
                ->field('c.id,c.color,c.name as dinner_type_name')
                ->where($dmap)
                ->group('a.dinner_type_id')
                ->select();
            $type = $all;
            $maxrow = 0;
            foreach ($type as $k=>$v){
                $total = Db::name('dinner_order_item')
                    ->alias('a')
                    ->join('dinner b','a.dinner_id=b.id')
                    ->join('dinner_type c','a.dinner_type_id=c.id')
                    ->field('sum(a.price) as nums,b.name as dinner_name,c.color,c.name as dinner_type_name')
                    ->where('a.dinner_type_id',$v['id'])
                    ->where($dmap)
                    ->group('a.dinner_id')
                    ->select();
                $count = Db::name('dinner_order_item')
                    ->alias('a')
                    ->join('dinner b','a.dinner_id=b.id')
                    ->join('dinner_type c','a.dinner_type_id=c.id')
                    ->where('a.dinner_type_id',$v['id'])
                    ->where($dmap)
                    ->sum('a.price');
                $type[$k]['total'] = sprintf("%.2f",$count);
                $newArr[] = $total;

                $count = count($total);
                if($count > $maxrow){
                    $maxrow = $count;
                }
            }


            $i = 0;
            while (true){
                if($maxrow <= 0){
                    break;
                }

                $d = [];
                foreach ($type as $k=>$v){
                    $dd = $newArr[$k];
                    $d['name'.$v['id']] = isset($dd[$i])?$dd[$i]['dinner_name']:'';
                    $d['price'.$v['id']] = isset($dd[$i])?$dd[$i]['nums']:'';
                }
                $rows[] = $d;
                $maxrow--;
                $i++;
            }

        }
        $this->assign('type',$type);
        $this->assign('data',$rows);
        $this->assign('start',$start);
        return $this->fetch();
    }

    public function typeTj(){

        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');

        $data = $this->typeTjData($start,$end);
        $this->assign('total',$data['b']);
        $this->assign('data',$data['a']);
        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }
    public function typeTjData($start,$end){
        $cate = input('cate/d',0);
        $this->assign('cate',$cate);
        $map[] = ['org_id','=',$this->orgId];
        $map[] = ['state','<>',0];
        $map[] = ['cate','=',$cate];



        $map= empty($map) ? true: $map;
        //数据查询
        $day = getDateFromRange($start,$end);
        $a = [];
        $b0 = $b1 = $b2=$b3=$b4=0;
        foreach ($day as $k=>$v){
            $map1 = $map;
            $day = date('Ymd',strtotime($v));
            if($cate == 1){
                $oids = Db::name('dinner_order_item')
                    ->where('day',$day)
                    ->group('order_id')
                    ->distinct(true)
                    ->column('order_id');
                if($oids){
                    $map1[] = ['id','in',$oids];
                }else{
                    $map1[] = ['id','=',-1];
                }
            }else{
                $map1[] = ['ymd','=',$day];
            }
            $a0 = Db::name($this->table)
                ->where($map1)
                ->column('id');
            if(empty($a0)){
                $k0 = 0;
            }else{
                $map2 = [];
                $map2[] = ['a.is_refuse','=',0];
                $map2[] = ['a.order_id','in',$a0];
                if($cate == 1){
                    $map2[] = ['a.day','=',$day];
                }
                $k0= Db::name('dinner_order_item')
                    ->alias('a')
                    ->where($map2)
                    ->sum('a.price');
            }
            $b0+=$k0;
            $a1 = Db::name($this->table)
                ->where($map)
                ->where('ymd',date('Ymd',strtotime($v)))
                ->where('order_type',1)
                ->column('id');
            if(empty($a1)){
                $k1 = 0;
            }else{
                $k1 = Db::name('dinner_order_item')
                    ->alias('a')
                    ->where('a.is_refuse',0)
                    ->where('a.order_id','in',$a1)
                    ->sum('a.price');
            }
            $b1+=$k1;
            $a2 = Db::name($this->table)
                ->where($map)
                ->where('ymd',date('Ymd',strtotime($v)))
                ->where('order_type',2)
                ->column('id');
            if(empty($a1)){
                $k2 = 0;
            }else{
                $k2 = Db::name('dinner_order_item')
                    ->alias('a')
                    ->where('a.is_refuse',0)
                    ->where('a.order_id','in',$a2)
                    ->sum('a.price');
            }
            $b2+=$k2;
            $a3 = Db::name($this->table)
                ->where($map)
                ->where('ymd',date('Ymd',strtotime($v)))
                ->where('order_type',3)
                ->column('id');
            if(empty($a1)){
                $k3 = 0;
            }else{
                $k3 = Db::name('dinner_order_item')
                    ->alias('a')
                    ->where('a.is_refuse',0)
                    ->where('a.order_id','in',$a3)
                    ->sum('a.price');
            }
            $b3+=$k3;
            $a4 = Db::name($this->table)
                ->where($map)
                ->where('ymd',date('Ymd',strtotime($v)))
                ->count();
            $b4+=$a4;
            $a[] = [
                'title'=>$v,
                'a0'=>$k0,
                'a1'=>$k1,
                'a2'=>$k2,
                'a3'=>$k3,
                'a4'=>$a4,
            ];

        }
        $b = [
            'b0'=>$b0,
            'b1'=>$b1,
            'b2'=>$b2,
            'b3'=>$b3,
            'b4'=>$b4,
        ];
        return ['a'=>$a,'b'=>$b];
    }
    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField('del',1);
        if($res){
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }
    }

    /**
     * 改变字段值
     * @param int $fv
     * @param string $fn
     * @param int $fv
     */
    public function changeField($id=0,$fn='',$fv=0){
        if(!$fn||!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField($fn,$fv);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }

    public function print($id=0){
        $info = db($this->table)->where('id',$id)->find();
        $all = Db::name('dinner_order_item')
            ->alias('a')
            ->join('dinner b','a.dinner_id=b.id')
            ->join('dinner_type c','a.dinner_type_id=c.id')
            ->field('a.*,b.name as dinner_name,c.color,c.name as dinner_type_name')
            ->where('a.order_id',$id)
            ->where('a.is_refuse',0)
            ->select();
        $info['item'] = $all;
        $this->assign('info',$info);
        return $this->fetch();
    }
    public function option($fv=0,$id=0){
        $res = db($this->table)
            ->where('org_id',$this->orgId)
            ->where('id',$id)
            ->update([
                'is_ok'=>$fv,
                'update_time'=>getTime()
            ]);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }

    public function export(){
        $ex_type = input('ex_type',1);//1导出2发饭表导出
        if($ex_type==1){
            $data = $this->ex_type_data1();
            $this->ex_type_export1($data);
        }elseif ($ex_type==2){
            $data = $this->ex_type_data2();
            $this->ex_type_export2($data);
        }
    }
    public function ex_type_export1($data){
         $lists = $data['list'];
         $count = $data['total'];
        //实例化PHPExcel类
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '序号')
            ->setCellValue('B1', '单号')
            ->setCellValue('C1', '总价')
            ->setCellValue('D1', '下单人')
            ->setCellValue('E1', '下单人电话')
            ->setCellValue('F1', '送货地址')
            ->setCellValue('G1', '床号及备注')
            ->setCellValue('H1', '下单时间')
            ->setCellValue('I1', '类别')
            ->setCellValue('J1', '购买物品');


        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(20);


        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($lists); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['sn']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['price']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['mobile']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['address']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $lists[$i]['remark']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $lists[$i]['pay_time']);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 2), $lists[$i]['tName']);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 2), $lists[$i]['item']);
        }
        //$objPHPExcel->getActiveSheet()->setCellValue('J' .(count($lists)+3), '总价:'.$count);
        $titles = array('序号','单号','总价','下单人','下单人电话','送货地址','床号及备注','下单时间','类别','购买物品');
        $count_length = count($titles)-1;
        $num =count($lists)+2;
        $sheet->setCellValue(\PHPExcel_Cell::stringFromColumnIndex(0).$num, '总价');
        $sheets = sprintf('%s:%s', chr(65).$num, \PHPExcel_Cell::stringFromColumnIndex($count_length-1).$num);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells($sheets);
        $objPHPExcel->getActiveSheet()->getStyle($sheets)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        //
        $sheet->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($count_length).$num,$count.'元');
        //设置保存的Excel表格名称
        $filename = '订单列表' . date('YmdHis', time()) . '.xls';

        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('订单列表');

        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }
    public function ex_type_data1(){
        $title = input('title','','trim');
        if($title){
            $map[] = ['a.name|a.sn','like','%'.$title.'%'];
        }
        $is_ok = input('is_ok',0,'trim');
        if($is_ok>=0){
            $map[] = ['a.is_ok','=',$is_ok];
        }
        $is_spot = input('is_spot',0,'trim');
        if($is_spot>=0){
            $map[] = ['a.is_spot','=',$is_spot];
        }
        $start = input('start','','trim');
        if($start){
            $map[] = ['a.ymd','=',date('Ymd',strtotime($start))];
        }
        $dinner_type_id = input('type','','trim');
        if($dinner_type_id != ''){
            $dinner_order_item = Db::name('dinner_order_item')
                ->where('dinner_type_id',$dinner_type_id)
                ->where('is_refuse',0)
                ->column('order_id');
            if(empty($dinner_order_item)){
                $map[] = ['a.id','=',-1];

            }else{
                $map[] = ['a.id','in',$dinner_order_item];

            }
        }
        $map[] = ['a.org_id','=',$this->orgId];
        $map[] = ['a.state','<>',0];
        $map= empty($map) ? true: $map;
        //数据查询
        $lists = db($this->table)
            ->alias('a')
            ->field('a.*')
            ->where($map)->order(
                ['a.id'=>'desc']
            )->select();
        foreach ($lists as $k=>$v){
            $all = Db::name('dinner_order_item')
                ->alias('a')
                ->join('dinner b','a.dinner_id=b.id')
                ->join('dinner_type c','a.dinner_type_id=c.id')
                ->field('a.*,b.name as dinner_name,c.color,c.name as dinner_type_name')
                ->where('a.order_id',$v['id'])
                ->where('a.is_refuse',0)
                ->select();
            $typeIdS = $all?array_unique(array_column($all,'dinner_type_id')):[];
            $typeName = '';
            foreach ($typeIdS as $k1=>$v1){
                $tInfo = Db::name('dinner_type')
                    ->where('id',$v1)
                    ->find();
                $typeName.=$tInfo['name'];
                if(count($typeIdS)>1 && $k1<count($typeIdS)-1){
                    $typeName.=',';
                }
            }
            $lists[$k]['tName'] = $typeName;
            $item = '';
            foreach ($all as $k2=>$v2){
                $item.=$v2['dinner_name'].'('.$v2['dinner_type_name'].')'.'*'.$v2['num'];
                if(count($all)>1 && $k2<count($all)-1){
                    $item.=',';
                }
            }
            if(!$item){
                unset($lists[$k]);
                continue;
            }else{
                $lists[$k]['item'] = $item;

            }
        }
        sort($lists);
        $num = db($this->table)
            ->alias('a')
            ->where($map)->sum('price');
        $data = [
            'list'=>$lists,
            'total'=>$num
        ];
        return $data;
    }

    public function ex_type_data2(){
        $title = input('title','','trim');
        if($title){
            $map[] = ['a.name|a.sn','like','%'.$title.'%'];
        }
        $is_ok = input('is_ok',0,'trim');
        if($is_ok>=0){
            $map[] = ['a.is_ok','=',$is_ok];
        }
//        $is_spot = input('is_spot',0,'trim');
//        if($is_spot>=0){
//            $map[] = ['a.is_spot','=',$is_spot];
//        }
//        $start = input('start','','trim');
//        if($start){
//            $map[] = ['a.ymd','=',date('Ymd',strtotime($start))];
//        }

        $is_spot = input('is_spot/d',0);
        if($is_spot == 2){
            $map[] = ['a.cate','=',1];

            $start = input('start',date('Y-m-d'),'trim');
            $oids = Db::name('dinner_order_item')
                ->where('day',$start)
                ->group('order_id')
                ->distinct(true)
                ->column('order_id');
            if($oids){
                $map[] = ['a.id','in',$oids];
            }else{
                $map[] = ['a.id','=',-1];
            }

        }else{
            $map[] = ['a.is_spot','=',$is_spot];
            $map[] = ['a.cate','=',0];

            $start = input('start',date('Y-m-d'),'trim');
            $map[] = ['a.ymd','=',date('Ymd',strtotime($start))];
        }

        $dinner_type_id = input('type','','trim');
        if($dinner_type_id != ''){
            $dinner_order_item = Db::name('dinner_order_item')
                ->where('dinner_type_id',$dinner_type_id)
                ->where('is_refuse',0)
                ->column('order_id');
            if(empty($dinner_order_item)){
                $map[] = ['a.id','=',-1];

            }else{
                $map[] = ['a.id','in',$dinner_order_item];

            }
        }
        $map[] = ['a.org_id','=',$this->orgId];
        $map[] = ['a.state','<>',0];
        $map= empty($map) ? true: $map;
        //数据查询
        $lists = Db::name($this->table)
            ->alias('a')
            ->field('a.*')
            ->where($map)->order(
                ['a.address'=>'asc','a.id'=>'desc']
            )->select();
        foreach ($lists as $k=>$v){
            $dmap = [];
            $dmap[] = ['a.order_id','=',$v['id']];
            $dmap[] = ['a.is_refuse','=',0];
            if($dinner_type_id > 0){
                $dmap[] = ['a.dinner_type_id','=',$dinner_type_id];
            }
            if($is_spot == 2){
                $dmap[] = ['a.day','=',$start];
            }
            $all = Db::name('dinner_order_item')
                ->alias('a')
                ->join('dinner b','a.dinner_id=b.id')
                ->join('dinner_type c','a.dinner_type_id=c.id')
                ->field('a.*,b.name as dinner_name,c.color,c.name as dinner_type_name')
                ->where($dmap)
                ->select();
            $typeIdS = $all?array_unique(array_column($all,'dinner_type_id')):[];
            $typeName = [];
            foreach ($typeIdS as $k1=>$v1){
                $tInfo = Db::name('dinner_type')
                    ->where('id',$v1)
                    ->find();
                $typeName[] = $tInfo['name'];
            }
            $lists[$k]['tName'] = implode(',',$typeName);
            $item = '';
            foreach ($all as $k2=>$v2){
                $item.=$v2['dinner_name'].'('.$v2['dinner_type_name'].')'.'*'.$v2['num'];
                if(count($all)>1 && $k2<count($all)-1){
                    $item.=',';
                }
            }
            if(!$item){
                unset($lists[$k]);
                continue;
            }else{
                $lists[$k]['item'] = $item;

            }
        }
        sort($lists);
        $orderId = array_column($lists,'id');

        $dmap2 = [];
        $dmap2[] = ['a.order_id','in',$orderId];
        $dmap2[] = ['a.is_refuse','=',0];
        if($dinner_type_id > 0){
            $dmap2[] = ['a.dinner_type_id','=',$dinner_type_id];
        }
        if($is_spot == 2){
            $dmap2[] = ['a.day','=',$start];
        }

        $total = Db::name('dinner_order_item')
            ->alias('a')
            ->join('dinner b','a.dinner_id=b.id')
            ->join('dinner_type c','a.dinner_type_id=c.id')
            ->field('sum(a.num) as nums,b.name as dinner_name,c.color,c.name as dinner_type_name')
            ->where($dmap2)
            ->group('a.dinner_id')
            ->select();
        $items = '';
        foreach ($total as $k3=>$v3){
            $items.=$v3['dinner_name'].'('.$v3['dinner_type_name'].')'.'*'.$v3['nums'];
            if(count($total)>1 && $k3<count($total)-1){
                $items.=',';
            }
        }
        $data = [
            'list'=>$lists,
            'total'=>$items
        ];
        return $data;
    }

    public function print_all(){
        $data = $this->ex_type_data2();
        $this->assign('day',date('Y年m月d日'));
        $this->assign('data',$data);
        return $this->fetch();
    }
    public function ex_type_export2($data){
        $lists = $data['list'];
        $count = $data['total'];
        //实例化PHPExcel类
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $titles = array('地址'=>30,'床号及备注'=>14,'姓名'=>10,'手机号'=>14,'餐别'=>8,'总价'=>8,'内容'=>60);
        $count_length = count($titles)-1;
        $objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('A1', '分餐列表', \PHPExcel_Cell_DataType::TYPE_STRING);
        $sheet = sprintf('%s:%s', chr(65).'1', \PHPExcel_Cell::stringFromColumnIndex($count_length).'1');
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells($sheet);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle($sheet)->getFont()->getColor($sheet)->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $as = $objPHPExcel->getActiveSheet();
        $as->getStyle($sheet)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle($sheet)->getFont()->setBold(true);
        $as->getStyle($sheet)->getFont()->setSize(18);
        $column=0;
        foreach ($titles as $key => $value) {
            $k = \PHPExcel_Cell::stringFromColumnIndex($column);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($k.'2', $key);
            $objPHPExcel->getActiveSheet()->getColumnDimension($k)->setWidth($value);
//            $objPHPExcel->getActiveSheet()->getColumnDimension($k)->setAutoSize(true);
            $objPHPExcel->getActiveSheet(0)->getStyle($k.'2')->getFont()->setBold(true);
            $column++;
        }
        $num=3;
        $cell = $objPHPExcel->setActiveSheetIndex(0);
        // 订餐
        $dinner_order = array();
        foreach ($lists as $key => $value) {
            if(empty($value['item'])){
                continue;
            }
            $column = 0;
            // 床号
            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['address'].' ');
            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['remark'].' ');
            // 姓名
            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['name']);
            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['mobile'].' ');

            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['tName']);
            // 总价
            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['price'].' ');
            // 内容

            $objPHPExcel->getActiveSheet()->getStyle(\PHPExcel_Cell::stringFromColumnIndex($column).$num)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($column++).$num, $value['item']);
            $num++;
        }

        $sheet = sprintf('%s:%s', chr(65).$num, \PHPExcel_Cell::stringFromColumnIndex($count_length).$num);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells($sheet);
        $objPHPExcel->getActiveSheet()->getStyle($sheet)->getAlignment()->setWrapText(true)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle($sheet)->getFont()->setBold(20);

        $cell->setCellValue(\PHPExcel_Cell::stringFromColumnIndex(0).$num, '合计:'.$count);


        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //粗的是thick
                    'color' => array('argb' => 'FF000000'),
                ),

            ),
        );
        $count = \PHPExcel_Cell::stringFromColumnIndex($count_length).$num;
        // var_dump(sprintf('A1:%s',$count));exit;
        $objPHPExcel->getActiveSheet()->getStyle(sprintf('A1:%s', $count))->applyFromArray($styleThinBlackBorderOutline);
        //设置保存的Excel表格名称
        $filename = '分餐列表' . date('YmdHis', time()) . '.xls';

        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('分餐列表');

        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }
    public function exportTypeTj(){
        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');

        $data = $this->typeTjData($start,$end);
        //实例化PHPExcel类
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '订餐日期')
            ->setCellValue('B1', '订单金额（元）')
            ->setCellValue('C1', '微信支付（元）')
            ->setCellValue('D1', '食堂卡（元）')
            ->setCellValue('E1', '现金（元）')
            ->setCellValue('F1', '订单数');

        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(20);



        $lists = $data['a'];
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($lists); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['a0']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['a1']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['a2']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['a3']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['a4']);

        }
        $objPHPExcel->getActiveSheet()->setCellValue('A' . (count($lists) + 2), '合计:');
        $objPHPExcel->getActiveSheet()->setCellValue('B' . (count($lists) + 2), $data['b']['b0']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . (count($lists) + 2), $data['b']['b1']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . (count($lists) + 2), $data['b']['b2']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . (count($lists) + 2), $data['b']['b3']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . (count($lists) + 2), $data['b']['b4']);

        //设置保存的Excel表格名称
        $filename = '订餐统计表' . date('YmdHis', time()) . '.xls';


        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }




    public function orgasdinner(){
        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');
        $type = input('type',0);
        $orgId = input('org_id',0);

        $data = $this->orgasdinnerData($start,$end,$type,$orgId);
        $this->assign('total',$data['b']);
        $this->assign('data',$data['a']);
        $this->assign('start',$start);
        $this->assign('end',$end);
        $this->assign('type',$type);
        $this->assign('orgId',$orgId);
        $orgList = Db::name('org')
            ->where('del',0)
            ->where('enable',1)
            ->where('type',2)
            ->where('parent_id','>',0)
            ->select();
        $this->assign('orgList',$orgList);

        return $this->fetch();
    }
    public function orgasdinnerData($start,$end,$type,$orgId){
        $cate = input('cate/d',0);
        $this->assign('cate',$cate);

        if($orgId >0){
            $map[] = ['org_id','=',$orgId];
        }
        $map[] = ['cate','=',0];
        $map[] = ['state','<>',0];
        $map= empty($map) ? true: $map;
        //数据查询
        if($type==0){//按天
            $day = getDateFromRange($start,$end);
            $a = [];
            $b0 = $b1 = $b2=$b3=$b4=0;
            foreach ($day as $k=>$v){
                $a0 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->column('id');
                if(empty($a0)){
                    $k0 = 0;
                }else{
                    $k0= Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a0)
                        ->sum('a.price');
                }
                $b0+=$k0;
                $a1 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',1)
                    ->column('id');
                if(empty($a1)){
                    $k1 = 0;
                }else{
                    $k1 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a1)
                        ->sum('a.price');
                }
                $b1+=$k1;
                $a2 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',2)
                    ->column('id');
                if(empty($a1)){
                    $k2 = 0;
                }else{
                    $k2 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a2)
                        ->sum('a.price');
                }
                $b2+=$k2;
                $a3 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',3)
                    ->column('id');
                if(empty($a1)){
                    $k3 = 0;
                }else{
                    $k3 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a3)
                        ->sum('a.price');
                }
                $b3+=$k3;
                $a4 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->count();
                $b4+=$a4;
                $a[] = [
                    'title'=>$v,
                    'a0'=>$k0,
                    'a1'=>$k1,
                    'a2'=>$k2,
                    'a3'=>$k3,
                    'a4'=>$a4,
                ];

            }
            $b = [
                'b0'=>$b0,
                'b1'=>$b1,
                'b2'=>$b2,
                'b3'=>$b3,
                'b4'=>$b4,
            ];
        }else{//按项目
            $map[] = ['ymd','>=',date('Ymd',strtotime($start))];
            $map[] = ['ymd','<=',date('Ymd',strtotime($end))];
            $ow = [];
            if($orgId >0){
                $ow[] = ['id','=',$orgId];
            }
            $day = Db::name('org')
                ->where('del',0)
                ->where('enable',1)
                ->where('type',2)
                ->where('parent_id','>',0)
                ->where($ow)
                ->select();
            $a = [];
            $b0 = $b1 = $b2=$b3=$b4=0;
            foreach ($day as $k=>$v){
                $a0 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->column('id');
                if(empty($a0)){
                    $k0 = 0;
                }else{
                    $k0= Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a0)
                        ->sum('a.price');
                }
                $b0+=$k0;
                $a1 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',1)
                    ->column('id');
                if(empty($a1)){
                    $k1 = 0;
                }else{
                    $k1 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a1)
                        ->sum('a.price');
                }
                $b1+=$k1;
                $a2 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',2)
                    ->column('id');
                if(empty($a1)){
                    $k2 = 0;
                }else{
                    $k2 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a2)
                        ->sum('a.price');
                }
                $b2+=$k2;
                $a3 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',3)
                    ->column('id');
                if(empty($a1)){
                    $k3 = 0;
                }else{
                    $k3 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a3)
                        ->sum('a.price');
                }
                $b3+=$k3;
                $a4 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->count();
                $b4+=$a4;
                $a[] = [
                    'title'=>$v['name'],
                    'a0'=>$k0,
                    'a1'=>$k1,
                    'a2'=>$k2,
                    'a3'=>$k3,
                    'a4'=>$a4,
                ];

            }
            $b = [
                'b0'=>$b0,
                'b1'=>$b1,
                'b2'=>$b2,
                'b3'=>$b3,
                'b4'=>$b4,
            ];
        }

        return ['a'=>$a,'b'=>$b];
    }

    public function exportorgasdinner(){
        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');
        $type = input('type',0);
        $orgId = input('org_id',0);

        $data = $this->orgasdinnerData($start,$end,$type,$orgId);
        //实例化PHPExcel类
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', $type==0?'订餐日期':'项目')
            ->setCellValue('B1', '订单金额（元）')
            ->setCellValue('C1', '微信支付（元）')
            ->setCellValue('D1', '食堂卡（元）')
            ->setCellValue('E1', '现金（元）')
            ->setCellValue('F1', '订单数');

        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(20);



        $lists = $data['a'];
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($lists); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['a0']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['a1']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['a2']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['a3']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['a4']);

        }
        $objPHPExcel->getActiveSheet()->setCellValue('A' . (count($lists) + 2), '合计:');
        $objPHPExcel->getActiveSheet()->setCellValue('B' . (count($lists) + 2), $data['b']['b0']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . (count($lists) + 2), $data['b']['b1']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . (count($lists) + 2), $data['b']['b2']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . (count($lists) + 2), $data['b']['b3']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . (count($lists) + 2), $data['b']['b4']);

        //设置保存的Excel表格名称
        $filename = '各项目营业报表' . date('YmdHis', time()) . '.xls';


        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }




    public function orgcasdinner(){
        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');
        $type = input('type',0);
        $orgId = input('org_id',0);
        $data = $this->orgcasdinnerData($start,$end,$type,$orgId);

        $this->assign('total',$data['b']);
        $this->assign('data',$data['a']);
        $this->assign('start',$start);
        $this->assign('end',$end);
        $this->assign('type',$type);
        $this->assign('orgId',$orgId);
        $orgList = Db::name('org')
            ->where('del',0)
            ->where('enable',1)
            ->where('type',2)
            ->where('parent_id','>',0)
            ->select();
        $this->assign('orgList',$orgList);

        return $this->fetch();
    }
    public function orgcasdinnerData($start,$end,$type,$orgId){
        $cate = input('cate/d',0);
        $this->assign('cate',$cate);

        if($orgId >0){
            $map[] = ['org_id','=',$orgId];
        }
        $map[] = ['state','<>',0];
        $map[] = ['cate','=',0];
        $map= empty($map) ? true: $map;
        //数据查询
        if($type==0){//按天
            $day = getDateFromRange($start,$end);
            $a = [];
            $b0 = $b1 = $b2=$b3=$b4=0;
            foreach ($day as $k=>$v){
                $a0 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->column('id');
                if(empty($a0)){
                    $k0 = 0;
                }else{
                    $k0= Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a0)
                        ->sum('a.price');
                }
                $b0+=$k0;
                $a1 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',1)
                    ->column('id');
                if(empty($a1)){
                    $k1 = 0;
                }else{
                    $k1 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a1)
                        ->sum('a.price');
                }
                $b1+=$k1;
                $k2 = round($k1*0.006,2);
                $b2+=$k2;
                $a3 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',2)
                    ->column('id');
                if(empty($a3)){
                    $k3 = 0;
                }else{
                    $k3 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a3)
                        ->sum('a.price');
                }
                $b3+=$k3;
                $a4 = Db::name($this->table)
                    ->where($map)
                    ->where('ymd',date('Ymd',strtotime($v)))
                    ->where('order_type',3)
                    ->column('id');
                if(empty($a1)){
                    $k4 = 0;
                }else{
                    $k4 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a4)
                        ->sum('a.price');
                }
                $b4+=$k4;
                $a[] = [
                    'title'=>$v,
                    'a0'=>$k0,
                    'a1'=>$k1,
                    'a2'=>$k2,
                    'a3'=>$k3,
                    'a4'=>$k4,
                ];

            }
            $b = [
                'b0'=>$b0,
                'b1'=>$b1,
                'b2'=>$b2,
                'b3'=>$b3,
                'b4'=>$b4,
            ];
        }else{//按项目
            $map[] = ['ymd','>=',date('Ymd',strtotime($start))];
            $map[] = ['ymd','<=',date('Ymd',strtotime($end))];
            $ow = [];
            if($orgId >0){
                $ow[] = ['id','=',$orgId];
            }
            $day = Db::name('org')
                ->where('del',0)
                ->where('enable',1)
                ->where('type',2)
                ->where('parent_id','>',0)
                ->where($ow)
                ->select();
            $a = [];
            $b0 = $b1 = $b2=$b3=$b4=0;
            foreach ($day as $k=>$v){
                $a0 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->column('id');
                if(empty($a0)){
                    $k0 = 0;
                }else{
                    $k0= Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a0)
                        ->sum('a.price');
                }
                $b0+=$k0;
                $a1 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',1)
                    ->column('id');
                if(empty($a1)){
                    $k1 = 0;
                }else{
                    $k1 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a1)
                        ->sum('a.price');
                }
                $b1+=$k1;
                $k2 = round($k1*0.006,2);
                $b2+=$k2;
                $a3 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',2)
                    ->column('id');
                if(empty($a3)){
                    $k3 = 0;
                }else{
                    $k3 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a3)
                        ->sum('a.price');
                }
                $b3+=$k3;

                $a4 = Db::name($this->table)
                    ->where($map)
                    ->where('org_id',$v['id'])
                    ->where('order_type',3)
                    ->column('id');
                if(empty($a1)){
                    $k4 = 0;
                }else{
                    $k4 = Db::name('dinner_order_item')
                        ->alias('a')
                        ->where('a.is_refuse',0)
                        ->where('a.order_id','in',$a4)
                        ->sum('a.price');
                }
                $b4+=$k4;
                $a[] = [
                    'title'=>$v['name'],
                    'a0'=>$k0,
                    'a1'=>$k1,
                    'a2'=>$k2,
                    'a3'=>$k3,
                    'a4'=>$k4,
                ];

            }
            $b = [
                'b0'=>$b0,
                'b1'=>$b1,
                'b2'=>$b2,
                'b3'=>$b3,
                'b4'=>$b4,
            ];
        }

        return ['a'=>$a,'b'=>$b];
    }


    public function exportorgcasdinner(){
        $start = input('start',date('Y-m-d'),'trim');
        $end = input('end',date('Y-m-d'),'trim');
        $type = input('type',0);
        $orgId = input('org_id',0);

        $data = $this->orgcasdinnerData($start,$end,$type,$orgId);
        //实例化PHPExcel类
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', $type==0?'订餐日期':'项目')
            ->setCellValue('B1', '订单金额（元）')
            ->setCellValue('C1', '微信支付（元）')
            ->setCellValue('D1', '微信手续费0.6%（元）')
            ->setCellValue('E1', '食堂卡（元）')
            ->setCellValue('F1', '现金（元）');

        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(20);



        $lists = $data['a'];
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($lists); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['a0']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['a1']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['a2']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['a3']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['a4']);

        }
        $objPHPExcel->getActiveSheet()->setCellValue('A' . (count($lists) + 2), '合计:');
        $objPHPExcel->getActiveSheet()->setCellValue('B' . (count($lists) + 2), $data['b']['b0']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . (count($lists) + 2), $data['b']['b1']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . (count($lists) + 2), $data['b']['b2']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . (count($lists) + 2), $data['b']['b3']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . (count($lists) + 2), $data['b']['b4']);

        //设置保存的Excel表格名称
        $filename = '各项目财务报表' . date('YmdHis', time()) . '.xls';


        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }

    public function refund($id=0){
        $info = Db::name('dinner_order')->where('org_id',$this->orgId)->where('id',$id)->find();
        if(request()->isPost()){
            if(!$info){
                $this->error('订单不存在');
            }
            if($info['cate'] != 1){
                $this->error('特殊套餐才能退餐');
            }

            $days = input('days','','trim');
            $remark = input('remark','','trim');
            if(!$days){
                $this->error('请选择退餐日期');
            }
            $days = explode(',',$days);
            $olddays = Db::name('dinner_order_item')->where('order_id',$id)->where('is_refuse',0)->group('day')->distinct(true)->column('day');
            $olddays = $olddays?$olddays:[];

            foreach ($days as $v){
                if(!in_array($v,$olddays)){
                    $this->error($v.'已退餐，勿重复操作');
                }
            }
            $dinners = Db::name('dinner_order_item')
                ->where('order_id',$id)
                ->where('day','in',$days)
                ->where('is_refuse',0)
                ->select();
            Db::startTrans();
            try{

                Db::name('dinner_order_item')
                    ->where('order_id',$id)
                    ->where('day','in',$days)
                    ->update(['is_refuse'=>1,'refuse_time'=>date('Y-m-d H:i:s')]);

                $rid = Db::name('dinner_order_refuse')->insertGetId([
                    'dinner_order_id'=>$id
                    ,'refuse_price'=>0
                    ,'refuse_y'=>date('Y')
                    ,'refuse_ymd'=>date('Ymd')
                    ,'refuse_ym'=>date('Ym')
                    ,'created_user_id'=>$this->userId
                    ,'create_time'=>date('Y-m-d H:i:s')
                    ,'refuse_w'=>date('Y').date('W')
                    ,'order_type'=>$info['order_type']
                    ,'is_replace'=>$info['is_replace']
                    ,'org_id'=>$this->orgId
                    ,'status'=>5
                    ,'op_user_id'=>$this->userId
                    ,'op_time'=>date('Y-m-d H:i:s')
                    ,'refuse_remark'=>$remark
                ]);
                if(!$rid){
                    \exception('操作失败');
                }

                $items = [];
                foreach ($dinners as $k=>$v){
                    $items[] = [
                        'dinner_id' => $v['dinner_id'],
                        'order_refuse_id' => $rid,
                        'price'=>$v['price'],
                        'num'=>$v['num'],
                        'dinner_type_id'=>$v['dinner_type_id'],
                        'day' => $v['day'],
                        'group_id' => $v['group_id']
                    ];
                }
                $rr = Db::name('dinner_order_refuse_item')->insertAll($items);
                if(!$rr){
                    \exception('操作失败');
                }


                Db::commit();
            }catch (Exception $e){
                Db::rollback();
                $this->error($e->getMessage());
            }

            $this->success('操作成功');
        }else{
            if(!$info){
                exit('订单不存在');
            }
            if($info['cate'] != 1){
                exit('特殊套餐才能退餐');
            }

            $days = Db::name('dinner_order_item')->where('order_id',$id)->where('is_refuse',0)->group('day')->distinct(true)->column('day');
            $news = [];
            foreach ($days as $v){
                $news[] = ['id'=>$v,'title'=>$v];
            }

            $this->assign('days',$news);
            $this->assign('id',$id);

            return $this->fetch();
        }
    }
}