jya-oa-api/application/common/model/DinnerOrders.php

<?php
namespace app\common\model;

use app\hander\HelpHander;
use think\Db;
use think\Exception;
use think\Model;

class DinnerOrders extends Model
{

    public function lists(){
        $page = input('page/d',1);
        $size = input('size/d',10);
        $map[] = ['b.del','=',0];
        $title = input('title','','trim');
        if($title != ''){
            $map[] = ['b.name','like','%'.$title.'%'];
        }
        $status = input('status/d',-1);
        if($status >= 0){
            $map[] = ['b.status','=',$status];
        }

        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyId = input('companyId/d',0);
        if($companyId > 0){
            $map[] = ['b.company_id','=',$companyId];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }
        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->page($page,$size)
            ->order('b.id desc')
            ->group('a.order_id')
            ->distinct(true)
            ->field('b.*,d.title as company,u.phone,sum(a.price*a.nums) as money')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $m = [];
            if($cateId > 0){
                $m[] = ['a.cate_id','=',$cateId];
            }
            $m[] = ['a.order_id','=',$v['id']];
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where($m)
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title,c.color')
                ->select();
            $lists[$k]['goods'] = $goods?$goods:[];
        }
        $total = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_orders b','a.order_id = b.id')
                ->join('dinner_company d','d.id = b.company_id')
                ->join('dinner_user u','u.id = b.user_id')
                ->group('a.order_id')
//                ->distinct(true)
//                ->fetchSql(true)
                ->where($map)->count();
//        dump($lists);
//        halt($total);

        $totalMoney = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->sum('a.price*a.nums');

        $goods = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->join('dinner_cate c','c.id = a.cate_id')
            ->join('dinner e','e.id = a.dinner_id')
            ->where($map)
            ->group('a.dinner_id')
            ->distinct(true)
            ->field('a.dinner_id,sum(a.nums) as tnums,e.title,c.title as cate_title')
            ->select();
        $goods = $goods?$goods:[];
        $garr = [];
        foreach ($goods as $k=>$v){
            $garr[] = '['.$v['cate_title'].']'.$v['title'].'*'.$v['tnums'];
        }


        $data = [
            'total_money' => $totalMoney,
            'total' => $total,
            'list' => $lists,
            'goods' => implode(';',$garr)
        ];
        return $data;
    }

    public function applys($userId){
        $page = input('page/d',1);
        $size = input('size/d',10);
        $map[] = ['b.del','=',0];
        $title = input('title','','trim');
        if($title != ''){
            $map[] = ['b.name','like','%'.$title.'%'];
        }
        $status = input('status/d',-1);
        if($status >= 0){
            $map[] = ['b.status','=',$status];
        }

        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if($companyIds){
            $map[] = ['b.company_id','in',$companyIds];
        }else{
            $map[] = ['b.company_id','=',-1];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }

        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->page($page,$size)
            ->order('b.id desc')
            ->group('a.order_id')
            ->distinct(true)
            ->field('b.*,d.title as company,u.phone,sum(a.price*a.nums) as money')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $m = [];
            if($cateId > 0){
                $m[] = ['a.cate_id','=',$cateId];
            }
            $m[] = ['a.order_id','=',$v['id']];
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where($m)
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title,c.color')
                ->select();
            $lists[$k]['goods'] = $goods?$goods:[];
        }
        $total = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->group('a.order_id')
//            ->distinct(true)
            ->where($map)->count();
        $totalMoney = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->sum('a.price*a.nums');
        $data = [
            'total_money' => $totalMoney,
            'total' => $total,
            'list' => $lists
        ];
        return $data;
    }

    public function applyexport($userId){
        $map[] = ['b.del','=',0];
        $title = input('title','','trim');
        if($title != ''){
            $map[] = ['b.name','like','%'.$title.'%'];
        }
        $status = input('status/d',-1);
        if($status >= 0){
            $map[] = ['b.status','=',$status];
        }

        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if($companyIds){
            $map[] = ['b.company_id','in',$companyIds];
        }else{
            $map[] = ['b.company_id','=',-1];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }

        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->order('b.id desc')
            ->group('a.order_id')
            ->distinct(true)
            ->field('b.*,d.title as company,u.phone,sum(a.price*a.nums) as money')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $m = [];
            if($cateId > 0){
                $m[] = ['a.cate_id','=',$cateId];
            }
            $m[] = ['a.order_id','=',$v['id']];
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where($m)
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title,c.color')
                ->select();
            $goods = $goods?$goods:[];

            if($v['status'] == 0){
                $lists[$k]['status_text'] = '已取消';
            }else if($v['status'] == 1){
                $lists[$k]['status_text'] = '待确认';
            }else{
                $lists[$k]['status_text'] = '已确认';
            }
            $strs = [];
            foreach ($goods as $kk=>$vv){
                $strs[] = '['.$vv['cate_title'].']'.$vv['dinner_title'] .' * '.$vv['nums'];
            }
            $lists[$k]['goods'] = $strs?implode(';',$strs):'';
        }
        $columns[] = ["title" => "单位","key" => "company"];
        $columns[] = ["title" => "姓名","key" => "name"];
        $columns[] = ["title" => "手机号","key" => "phone"];
        $columns[] = ["title" => "配送日期","key" => "day"];
        $columns[] = ["title" => "金额","key" => "money"];
        $columns[] = ["title" => "状态","key" => "statusText"];
        $columns[] = ["title" => "购买商品","key" => "goods"];
        $columns[] = ["title" => "备注","key" => "remark"];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];

        return $data;
    }

    public function agree($id,$userId){
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if(!$companyIds){
            HelpHander::error('无权限操作');
        }
        $info = Db::name('dinner_orders')->where('del',0)->where('id',$id)->find();
        if(!$info){
            HelpHander::error('订单不存在');
        }
        if($info['status'] != 1){
            HelpHander::error('订单已处理');
        }
        if(!in_array($info['company_id'],$companyIds)){
            HelpHander::error('无权限操作');
        }
        $vals = Db::name('config')->where('name','dinner_apply_limit')->value('value');
        if($vals){
            $limitTime = date('Y-m-d').' '.$vals;
            $curTime = date('Y-m-d H:i');
            if($curTime > $limitTime){
                HelpHander::error('审核时间已过');
            }
        }
        $ret = Db::name('dinner_orders')->where('id',$id)->update(['status'=>2,'update_time'=>date('Y-m-d H:i:s')]);
        if(!$ret){
            HelpHander::error('操作失败');
        }
        return true;
    }

    public function agreeall($userId){
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if(!$companyIds){
            HelpHander::error('无权限操作');
        }
        $ids = Db::name('dinner_orders')
            ->where('del',0)
            ->where('company_id','in',$companyIds)
            ->where('status',1)
            ->column('id');
        if(!$ids){
            HelpHander::error('无可处理的订单');
        }
        $vals = Db::name('config')->where('name','dinner_apply_limit')->value('value');
        if($vals){
            $limitTime = date('Y-m-d').' '.$vals;
            $curTime = date('Y-m-d H:i');
            if($curTime > $limitTime){
                HelpHander::error('审核时间已过');
            }
        }

        $ret = Db::name('dinner_orders')->where('id','in',$ids)->update(['status'=>2,'update_time'=>date('Y-m-d H:i:s')]);
        if(!$ret){
            HelpHander::error('操作失败');
        }
        return true;
    }

    public function del($id,$userId){
        $ret = $this->where('id',$id)->update(['del'=>1,'del_user_id'=>$userId,'del_time'=>date('Y-m-d H:i:s')]);
        if(!$ret){
            HelpHander::error('删除失败');
        }
        return true;
    }


    public function export(){
        $map[] = ['b.del','=',0];
        $title = input('title','','trim');
        if($title != ''){
            $map[] = ['b.name','like','%'.$title.'%'];
        }
        $status = input('status/d',-1);
        if($status >= 0){
            $map[] = ['b.status','=',$status];
        }

        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyId = input('companyId/d',0);
        if($companyId > 0){
            $map[] = ['b.company_id','=',$companyId];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }
        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner_company d','d.id = b.company_id')
            ->join('dinner_user u','u.id = b.user_id')
            ->where($map)
            ->order('b.id desc')
            ->group('a.order_id')
            ->distinct(true)
            ->field('b.*,d.title as company,u.phone,sum(a.price*a.nums) as money')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $m = [];
            if($cateId > 0){
                $m[] = ['a.cate_id','=',$cateId];
            }
            $m[] = ['a.order_id','=',$v['id']];
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where($m)
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title,e.remark as dinner_remark')
                ->select();
            $goods = $goods?$goods:[];
            if($v['status'] == 0){
                $lists[$k]['status_text'] = '已取消';
            }else if($v['status'] == 1){
                $lists[$k]['status_text'] = '待确认';
            }else{
                $lists[$k]['status_text'] = '已确认';
            }
            $strs = [];
            foreach ($goods as $kk=>$vv){
                if($vv['dinner_remark']){
                    $strs[] = '['.$vv['cate_title'].']'.$vv['dinner_title'] .'('.$vv['dinner_remark'].')'.' * '.$vv['nums'];
                }else{
                    $strs[] = '['.$vv['cate_title'].']'.$vv['dinner_title'] .' * '.$vv['nums'];
                }

            }
            $lists[$k]['goods'] = $strs?implode(';',$strs):'';
        }


        $columns[] = ["title" => "单位","key" => "company"];
        $columns[] = ["title" => "姓名","key" => "name"];
        $columns[] = ["title" => "手机号","key" => "phone"];
        $columns[] = ["title" => "配送日期","key" => "day"];
        $columns[] = ["title" => "金额","key" => "money"];
        $columns[] = ["title" => "状态","key" => "statusText"];
        $columns[] = ["title" => "购买商品","key" => "goods"];
        $columns[] = ["title" => "备注","key" => "remark"];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];

        return $data;
    }

    public function book($data){
        $result = validate('DinnerOrders')->check($data,[],'');
        if(true !== $result){
            HelpHander::error(validate('DinnerOrders')->getError());
        }

        $data['order_sn'] = get_unique_id('D');
        $data['status'] = 1;
        $data['create_time'] = date('Y-m-d H:i:s');

        $user = Db::name('dinner_user')->where('id',$data['user_id'])->find();
        $data['name'] = $user['name'];
        $data['company_id'] = $user['company_id'];

        $amount = 0;
        $goods = json_decode($data['goods'],true);
        unset($data['goods']);
        $day = '';
        foreach ($goods as $k=>$v){
            $info = Db::name('dinner')->where('id',$v['dinner_id'])->find();
            $goods[$k]['price'] = $info['price'];
            $goods[$k]['cate_id'] = $info['cate_id'];
            $amount = $amount + $v['nums']*$info['price'];
            $day = $info['day'];
        }
        $data['amount'] = round($amount,2);
        $data['day'] = $day;

        Db::startTrans();
        try {

            $orderId = Db::name('dinner_orders')->insertGetId($data);
            if(!$orderId){
                \exception('操作失败');
            }

            foreach ($goods as $k=>$v){
                $goods[$k]['order_id'] = $orderId;
            }
            $res = Db::name('dinner_order_goods')->insertAll($goods);
            if($res != count($goods)){
                \exception('操作失败');
            }

            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            HelpHander::error('操作失败');
        }

        return true;
    }

    public function delgoods($id,$userId){
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if(!$companyIds){
            HelpHander::error('无权限操作');
        }

        $vals = Db::name('config')->where('name','dinner_apply_limit')->value('value');
        if($vals){
            $limitTime = date('Y-m-d').' '.$vals;
            $curTime = date('Y-m-d H:i');
            if($curTime > $limitTime){
                HelpHander::error('审核时间已过');
            }
        }

        $info = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','b.id = a.order_id')
            ->where('a.id',$id)
            ->where('b.del',0)
            ->field('b.*,a.nums,a.price')
            ->find();
        if(!$info){
            HelpHander::error('订单不存在');
        }
        if($info['status'] != 1){
            HelpHander::error('订单已处理');
        }
        if(!in_array($info['company_id'],$companyIds)){
            HelpHander::error('无权限操作');
        }

        Db::startTrans();
        try {

            $ret = Db::name('dinner_order_goods')->where('id',$id)->delete();
            if(!$ret){
                \exception('操作失败');
            }
            $amount = round($info['amount'] - $info['price']*$info['nums'],2);
            $res = Db::name('dinner_orders')->where('id',$info['id'])->update([
                'amount'=>$amount,
                'update_time'=>date('Y-m-d H:i:s')
            ]);
            if(!$res){
                \exception('操作失败');
            }

            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            HelpHander::error('操作失败');
        }
        return true;
    }

    public function addgoods($orderId,$dinnerId,$nums,$userId){
        if($dinnerId <= 0){
            HelpHander::error('参数错误');
        }
        if($nums <= 0){
            HelpHander::error('商品数量错误');
        }
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if(!$companyIds){
            HelpHander::error('无权限操作');
        }

        $vals = Db::name('config')->where('name','dinner_apply_limit')->value('value');
        if($vals){
            $limitTime = date('Y-m-d').' '.$vals;
            $curTime = date('Y-m-d H:i');
            if($curTime > $limitTime){
                HelpHander::error('审核时间已过');
            }
        }

        $info = Db::name('dinner_orders')
            ->where('id',$orderId)
            ->where('del',0)
            ->find();
        if(!$info){
            HelpHander::error('订单不存在');
        }
        if($info['status'] != 1){
            HelpHander::error('订单已处理');
        }
        if(!in_array($info['company_id'],$companyIds)){
            HelpHander::error('无权限操作');
        }

        $dinner = Db::name('dinner')->where('id',$dinnerId)->where('del',0)->where('enable',1)->find();
        if(!$dinner){
            HelpHander::error('商品已下架');
        }

        $og = Db::name('dinner_order_goods')->where('order_id',$orderId)->where('dinner_id',$dinnerId)->find();
        if($og){
            HelpHander::error('商品已存在');
        }

        Db::startTrans();
        try {

            $ret = Db::name('dinner_order_goods')->insert([
                'order_id' => $orderId,
                'dinner_id' => $dinnerId,
                'nums' => $nums,
                'price' => $dinner['price'],
                'cate_id' => $dinner['cate_id']
            ]);
            if(!$ret){
                \exception('操作失败');
            }
            $amount = round($info['amount'] + $dinner['price']*$nums,2);
            $res = Db::name('dinner_orders')->where('id',$info['id'])->update([
                'amount'=>$amount,
                'update_time'=>date('Y-m-d H:i:s')
            ]);
            if(!$res){
                \exception('操作失败');
            }

            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            HelpHander::error('操作失败');
        }
        return true;
    }

    public function editgoods($id,$nums,$userId){
        if($nums <= 0){
            HelpHander::error('商品数量必须大于0');
        }
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if(!$companyIds){
            HelpHander::error('无权限操作');
        }

        $vals = Db::name('config')->where('name','dinner_apply_limit')->value('value');
        if($vals){
            $limitTime = date('Y-m-d').' '.$vals;
            $curTime = date('Y-m-d H:i');
            if($curTime > $limitTime){
                HelpHander::error('审核时间已过');
            }
        }

        $info = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','b.id = a.order_id')
            ->where('a.id',$id)
            ->where('b.del',0)
            ->field('b.*,a.nums,a.price')
            ->find();
        if(!$info){
            HelpHander::error('订单不存在');
        }
        if($info['status'] != 1){
            HelpHander::error('订单已处理');
        }
        if(!in_array($info['company_id'],$companyIds)){
            HelpHander::error('无权限操作');
        }
        if($info['nums'] == $nums){
            HelpHander::error('未做任何修改');
        }

        Db::startTrans();
        try {

            $ret = Db::name('dinner_order_goods')->where('id',$id)->setField('nums',$nums);
            if(!$ret){
                \exception('操作失败');
            }

            $lnums = $nums - $info['nums'];
            $amount = round($info['amount'] + $info['price']*$lnums,2);
            $res = Db::name('dinner_orders')->where('id',$info['id'])->update([
                'amount'=>$amount,
                'update_time'=>date('Y-m-d H:i:s')
            ]);
            if(!$res){
                \exception('操作失败');
            }

            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            HelpHander::error('操作失败');
        }
        return true;
    }

    public function orders($page,$size,$userId){
        $lists = Db::name('dinner_orders')
            ->where('user_id',$userId)
            ->where('del',0)
            ->order('id desc')
            ->page($page,$size)
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where('a.order_id',$v['id'])
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title,c.color,e.img,e.remark')
                ->select();
            $goods = $goods?$goods:[];
            foreach ($goods as $kk=>$vv){
                $goods[$kk]['img'] = $vv['img']?$vv['img']:request()->domain().'/static/dinner.png';
            }
            $lists[$k]['goods'] = $goods;

            $lists[$k]['cancel'] = 0; // 可取消
            $curTime = time();
            $limitTime = strtotime($v['day'].' 08:30:00');
            if($curTime > $limitTime){
                $lists[$k]['cancel'] = 1; //不可取消
            }

        }

        return $lists;
    }

    public function pclists(){
        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyId = input('companyId/d',0);
        if($companyId > 0){
            $map[] = ['b.company_id','=',$companyId];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }else{
            $map[] = ['b.day','=',0];
        }
        $map[] = ['b.status','=',2];
        $map[] = ['b.del','=',0];

        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner e','e.id = a.dinner_id')
            ->join('dinner_cate c','c.id = a.cate_id')
            ->where($map)
            ->order('a.cate_id asc,a.id asc')
            ->group('a.dinner_id')
            ->distinct(true)
            ->field('sum(a.nums) as tnums,a.dinner_id,e.title,c.title as cate_title')
            ->select();
        $lists = $lists?$lists:[];
        return $lists;
    }

    public function pcexport(){
        $cateId = input('cateId/d',0);
        if($cateId > 0){
            $map[] = ['a.cate_id','=',$cateId];
        }

        $companyId = input('companyId/d',0);
        if($companyId > 0){
            $map[] = ['b.company_id','=',$companyId];
        }

        $day = input('day','','trim');
        if($day){
            $map[] = ['b.day','=',$day];
        }else{
            $map[] = ['b.day','=',0];
        }
        $map[] = ['b.status','=',2];
        $map[] = ['b.del','=',0];

        $lists = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','a.order_id = b.id')
            ->join('dinner e','e.id = a.dinner_id')
            ->join('dinner_cate c','c.id = a.cate_id')
            ->where($map)
            ->order('a.cate_id asc,a.id asc')
            ->group('a.dinner_id')
            ->distinct(true)
            ->field('sum(a.nums) as tnums,a.dinner_id,e.title,c.title as cate_title')
            ->select();
        $lists = $lists?$lists:[];

        $columns[] = ["title" => "类型","key" => "cateTitle"];
        $columns[] = ["title" => "菜品","key" => "title"];
        $columns[] = ["title" => "数量","key" => "tnums"];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];

        return $data;
    }

    //
    public function finance(){
        $page = input('page/d',1);
        $size = input('size/d',10);
//        $companyId = input('companyId/d',0);
//        if($companyId > 0){
//            $map[] = ['company_id','=',$companyId];
//            $map2[] = ['b.company_id','=',$companyId];
//        }
        $start = input('start','','trim');
        $end = input('end','','trim');
        if($start){
            $map[] = ['day','>=',$start];
            $map2[] = ['b.day','=',$start];
        }
        if($end){
            $map[] = ['day','<=',$end];
            $map2[] = ['b.day','=',$end];
        }
        $map[] = ['del','=',0];
        $map[] = ['status','=',2];
        $map2[] = ['b.del','=',0];
        $map2[] = ['b.status','=',2];

        $cates = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','b.id = a.order_id')
            ->join('dinner_cate c','c.id = a.cate_id')
            ->where($map2)
            ->group('a.cate_id')
            ->distinct(true)
            ->field('c.id,c.title')
            ->order('id asc')
            ->select();
        $cates = $cates?$cates:[];

        $companys = Db::name('dinner_company')
            ->where('del',0)
            ->where('enable',1)
            ->order('sorts asc,id asc')
            ->select();
        $companys = $companys?$companys:[];

        $lists = Db::name('dinner_orders')
            ->where($map)
            ->group('day')
            ->distinct(true)
            ->field('day,sum(amount) as money')
            ->order('day desc')
            ->page($page,$size)
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            // 各分类
            foreach ($cates as $kk=>$vv){
                $m = $map2;
                $m[] = ['b.day','=',$v['day']];
                $m[] = ['a.cate_id','=',$vv['id']];
                $money = Db::name('dinner_order_goods')
                    ->alias('a')
                    ->join('dinner_orders b','b.id = a.order_id')
                    ->where($m)
                    ->sum('a.nums*a.price');
                $lists[$k]['money'.$vv['id']] = $money;
            }
            $lists[$k]['company_id'] = 0;
            $lists[$k]['day2'] = $v['day'];
            $lists[$k]['id'] = md5($v['day'].'|0');
            // 各单位
            $children = [];
            foreach ($companys as $ck=>$cv){
                $a = [];
                $a['day'] = $cv['title'];
                $a['company_id'] = $cv['id'];
                $a['day2'] = $v['day'];
                $lists[$k]['id'] = md5($v['day'].'|'.$cv['title']);
                $money = Db::name('dinner_orders')
                    ->where('day',$v['day'])
                    ->where('company_id',$cv['id'])
                    ->where('status',2)
                    ->where('del',0)
                    ->sum('amount');
                $a['money'] = $money;
                // 各分类
                foreach ($cates as $kk=>$vv){
                    $m = $map2;
                    $m[] = ['b.company_id','=',$cv['id']];
                    $m[] = ['b.day','=',$v['day']];
                    $m[] = ['a.cate_id','=',$vv['id']];
                    $money = Db::name('dinner_order_goods')
                        ->alias('a')
                        ->join('dinner_orders b','b.id = a.order_id')
                        ->where($m)
                        ->sum('a.nums*a.price');
                    $a['money'.$vv['id']] = $money;
                }
                $children[] = $a;
            }
            if($children){
                $lists[$k]['children'] = $children;
            }
        }

        $total = Db::name('dinner_orders')
            ->where($map)
            ->group('day')
//            ->distinct(true)
            ->count();
        $totalMoney = Db::name('dinner_orders')
            ->where($map)
            ->sum('amount');
        $data = [
            'total_money' => $totalMoney,
            'cates' => $cates,
            'total' => $total,
            'list' => $lists
        ];

        return $data;
    }

    public function finexport(){
        $companyId = input('companyId/d',0);
        if($companyId > 0){
            $map[] = ['a.company_id','=',$companyId];
        }
        $day = input('day','','trim');
        if($day){
            $map[] = ['a.day','=',$day];
        }else{
            $map[] = ['a.day','=',0];
        }
        $map[] = ['a.status','=',2];
        $map[] = ['a.del','=',0];

        $lists = Db::name('dinner_orders')
            ->alias('a')
            ->join('dinner_company b','b.id = a.company_id')
            ->join('dinner_user c','c.id = a.user_id')
            ->where($map)
            ->field('a.*,b.title as company,c.phone')
            ->order('a.id asc')
            ->select();
        foreach ($lists as $k=>$v){
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where('a.order_id',$v['id'])
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title')
                ->select();
            $goods = $goods?$goods:[];
            $strs = [];
            foreach ($goods as $kk=>$vv){
                $strs[] = '['.$vv['cate_title'].']'.$vv['dinner_title'] .' * '.$vv['nums'];
            }
            $lists[$k]['goods'] = $strs?implode(';',$strs):'';
        }

        $columns[] = ["title" => "单位","key" => "company"];
        $columns[] = ["title" => "姓名","key" => "name"];
        $columns[] = ["title" => "手机号","key" => "phone"];
        $columns[] = ["title" => "配送日期","key" => "day"];
        $columns[] = ["title" => "金额","key" => "amount"];
        $columns[] = ["title" => "购买商品","key" => "goods"];
        $columns[] = ["title" => "备注","key" => "remark"];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];

        return $data;

    }

    public function cfinance($userId){
        $page = input('page/d',1);
        $size = input('size/d',10);
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if($companyIds){
            $map[] = ['company_id','in',$companyIds];
            $map2[] = ['b.company_id','in',$companyIds];
        }else{
            $map[] = ['company_id','=',-1];
            $map2[] = ['b.company_id','=',-1];
        }

        $start = input('start','','trim');
        $end = input('end','','trim');
        if($start){
            $map[] = ['day','>=',$start];
            $map2[] = ['b.day','=',$start];
        }
        if($end){
            $map[] = ['day','<=',$end];
            $map2[] = ['b.day','=',$end];
        }
        $map[] = ['del','=',0];
        $map[] = ['status','=',2];
        $map2[] = ['b.del','=',0];
        $map2[] = ['b.status','=',2];

        $cates = Db::name('dinner_order_goods')
            ->alias('a')
            ->join('dinner_orders b','b.id = a.order_id')
            ->join('dinner_cate c','c.id = a.cate_id')
            ->where($map2)
            ->group('a.cate_id')
            ->distinct(true)
            ->field('c.id,c.title')
            ->order('id asc')
            ->select();
        $cates = $cates?$cates:[];

        $lists = Db::name('dinner_orders')
            ->where($map)
            ->group('day')
            ->distinct(true)
            ->field('day,sum(amount) as money')
            ->order('day desc')
            ->page($page,$size)
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            // 各分类
            foreach ($cates as $kk=>$vv){
                $m = $map2;
                $m[] = ['a.cate_id','=',$vv['id']];
                $money = Db::name('dinner_order_goods')
                    ->alias('a')
                    ->join('dinner_orders b','b.id = a.order_id')
                    ->where($m)
                    ->sum('a.nums*a.price');
                $lists[$k]['money'.$vv['id']] = $money;
            }
        }

        $total = Db::name('dinner_orders')
            ->where($map)
            ->group('day')
//            ->distinct(true)
            ->count();
        $totalMoney = Db::name('dinner_orders')
            ->where($map)
            ->sum('amount');
        $data = [
            'total_money' => $totalMoney,
            'cates' => $cates,
            'total' => $total,
            'list' => $lists
        ];

        return $data;
    }

    public function cfinexport($userId){
        $companyIds = Db::name('dinner_company')->where('user_id',$userId)->where('del',0)->where('enable',1)->column('id');
        if($companyIds){
            $map[] = ['a.company_id','in',$companyIds];
        }else{
            $map[] = ['a.company_id','=',-1];
        }
        $day = input('day','','trim');
        if($day){
            $map[] = ['a.day','=',$day];
        }else{
            $map[] = ['a.day','=',0];
        }
        $map[] = ['a.status','=',2];
        $map[] = ['a.del','=',0];

        $lists = Db::name('dinner_orders')
            ->alias('a')
            ->join('dinner_company b','b.id = a.company_id')
            ->join('dinner_user c','c.id = a.user_id')
            ->where($map)
            ->field('a.*,b.title as company,c.phone')
            ->order('a.id asc')
            ->select();

        foreach ($lists as $k=>$v){
            $goods = Db::name('dinner_order_goods')
                ->alias('a')
                ->join('dinner_cate c','c.id = a.cate_id')
                ->join('dinner e','e.id = a.dinner_id')
                ->where('a.order_id',$v['id'])
                ->order('a.cate_id asc')
                ->field('a.*,c.title as cate_title,e.title as dinner_title')
                ->select();
            $goods = $goods?$goods:[];
            $strs = [];
            foreach ($goods as $kk=>$vv){
                $strs[] = '['.$vv['cate_title'].']'.$vv['dinner_title'] .' * '.$vv['nums'];
            }
            $lists[$k]['goods'] = $strs?implode(';',$strs):'';
        }

        $columns[] = ["title" => "单位","key" => "company"];
        $columns[] = ["title" => "姓名","key" => "name"];
        $columns[] = ["title" => "手机号","key" => "phone"];
        $columns[] = ["title" => "配送日期","key" => "day"];
        $columns[] = ["title" => "金额","key" => "amount"];
        $columns[] = ["title" => "购买商品","key" => "goods"];
        $columns[] = ["title" => "备注","key" => "remark"];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];

        return $data;
    }

}