lachang/app/store/model/ShopsDailySalesSt.php

<?php
// +----------------------------------------------------------------------
// | 萤火商城系统 [ 致力于通过产品和服务，帮助商家高效化开拓市场 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2021 https://www.yiovo.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
// +----------------------------------------------------------------------
// | Author: 萤火科技 <admin@yiovo.com>
// +----------------------------------------------------------------------
declare (strict_types = 1);

namespace app\store\model;
use app\common\model\ShopsDailySalesSt as ShopStModel;

/**
 * 门店销售数据每日统计模型
 * Class ShopsDailySalesSt
 * @package app\common\model
 */
class ShopsDailySalesSt extends ShopStModel
{
    public function listSt($from,$to,$param=[]){
        $where = [];
        if (isset($param['shop_name']) && !empty($param['shop_name'])){
            $where[] = ['yoshop_shops.shop_name','like','%'.$param['shop_name'].'%'];
        }
        if (isset($param['region_id']) && $param['region_id']>0){
            $where[] = ['yoshop_shops.region_id','=',$param['region_id']];
        }
        if (isset($param['city_id']) && $param['city_id']>0){
            $where[] = ['yoshop_shops.city_id','=',$param['city_id']];
        }
        if (isset($param['province_id']) && $param['province_id']>0){
            $where[] = ['yoshop_shops.province_id','=',$param['province_id']];
        }
        return Shops::alias('yoshop_shops')
            ->leftJoin('areas','yoshop_shops.city_id=areas.id')
            ->field('yoshop_shops.shop_id,yoshop_shops.shop_name,yoshop_shops.city_id,areas.name')
            ->where('yoshop_shops.is_delete',0)
            ->where($where)
            ->withSum(['monthlyBonusTotalMoney'=>function($query) use ($from,$to){//订单总金额（含运费）
                $query->whereBetweenTime('st_time',$from,$to);
                 }],'total_price')
            ->withSum(['monthlyBonusExpr'=>function($query) use ($from,$to){//运费
                $query->whereBetweenTime('st_time',$from,$to);
                }],'express_price')     
            ->withSum(['monthlyBonusGoods'=>function($query) use ($from,$to){//订单商品总金额
            $query->whereBetweenTime('st_time',$from,$to);
             }],'total_goods_price')
            ->withSum(['monthlyBonusPay'=>function($query) use ($from,$to){//实付金额
                $query->whereBetweenTime('st_time',$from,$to);
            }],'total_pay_price')
            ->withSum(['monthlyBonusCoupon'=>function($query) use ($from,$to){//平台优惠券抵扣
                $query->whereBetweenTime('st_time',$from,$to);
            }],'coupon_money')
            ->withSum(['monthlyBonusOrder'=>function($query) use ($from,$to){//订单数
                $query->whereBetweenTime('st_time',$from,$to);
            }],'order_count')
            ->withSum(['monthlyBonusBoss'=>function($query) use ($from,$to){//店老板基本分佣
                $query->whereBetweenTime('st_time',$from,$to);
            }],'boss_comm_money')
            ->withSum(['monthlyBonusMg'=>function($query) use ($from,$to){//店长基本分佣
                $query->whereBetweenTime('st_time',$from,$to);
            }],'mg_comm_money')
            ->withSum(['monthlyBonusSel'=>function($query) use ($from,$to){//全部店员基本分佣
                $query->whereBetweenTime('st_time',$from,$to);
            }],'seller_comm_money')
            ->paginate();
    }


    public function listsExport($from,$to,$param=[]){
        $where = [];
        if (isset($param['shop_name']) && !empty($param['shop_name'])){
            $where[] = ['yoshop_shops.shop_name','like','%'.$param['shop_name'].'%'];
        }
        if (isset($param['region_id']) && $param['region_id']>0){
            $where[] = ['yoshop_shops.region_id','=',$param['region_id']];
        }
        if (isset($param['city_id']) && $param['city_id']>0){
            $where[] = ['yoshop_shops.city_id','=',$param['city_id']];
        }
        if (isset($param['province_id']) && $param['province_id']>0){
            $where[] = ['yoshop_shops.province_id','=',$param['province_id']];
        }
        $list = Shops::alias('yoshop_shops')
            ->leftJoin('areas','yoshop_shops.city_id=areas.id')
            ->field('yoshop_shops.shop_id,yoshop_shops.shop_name,yoshop_shops.city_id,areas.name')
            ->where('yoshop_shops.is_delete',0)
            ->where($where)
            ->withSum(['monthlyBonusTotalMoney'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'total_price')
            ->withSum(['monthlyBonusExpr'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'express_price')
            ->withSum(['monthlyBonusGoods'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'total_goods_price')
            ->withSum(['monthlyBonusPay'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'total_pay_price')
            ->withSum(['monthlyBonusCoupon'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'coupon_money')
            ->withSum(['monthlyBonusOrder'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'order_count')
            ->withSum(['monthlyBonusBoss'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'boss_comm_money')
            ->withSum(['monthlyBonusMg'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'mg_comm_money')
            ->withSum(['monthlyBonusSel'=>function($query) use ($from,$to){
                $query->whereBetweenTime('st_time',$from,$to);
            }],'seller_comm_money')
            ->select();

        $data['header'] = ['序号', '门店名称', '门店城市','应付总金额(含运费)', '应付商品总金额', '实付金额','运费','平台优惠券抵扣','订单数','店老板基本分佣','店长基本分佣','全部店员基本分佣'];
        $data['filename'] = date('Y-m-d',$from).'~'.date('Y-m-d',$to).'门店购买统计';
        $data['data'] = [];

        foreach ($list as $key=>$arr){
            $new_list['key'] = $key+1;
            $new_list['shop_name'] = $arr['shop_name'];
            $new_list['name'] = $arr['name'];
            $new_list['monthly_bonus_total_money_sum'] = $arr['monthly_bonus_total_money_sum']?:0;

            $new_list['goods_sum'] = $arr['monthly_bonus_goods_sum']?:0;
            $new_list['pay_sum'] = $arr['monthly_bonus_pay_sum']?:0;
            $new_list['monthly_bonus_expr_sum'] = $arr['monthly_bonus_expr_sum']?:0;

            $new_list['coupon_sum'] = $arr['monthly_bonus_coupon_sum']?:0;
            $new_list['order_sum'] = $arr['monthly_bonus_order_sum']?:0;
            $new_list['boss_sum'] = $arr['monthly_bonus_boss_sum']?:0;
            $new_list['mg_sum'] = $arr['monthly_bonus_mg_sum']?:0;
            $new_list['sel_sum'] = $arr['monthly_bonus_sel_sum']?:0;
            $data['data'][] = $new_list;
        }
        return $data;
    }


    /**
     * 门店业绩销售额排名
     * @param $from
     * @param $to
     * @param $page
     * @return mixed
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function listStRank($from,$to,$page){
        $pageP = $page - 1;
        $shopM = new Shops();
        $i = 1;
        return self::whereBetweenTime('st_time',$from,$to)
            ->field('sum(total_pay_price) as sale_volume,sum(order_count) as order_count,shop_id')
            ->group('shop_id')->order('sale_volume desc')->paginate(15)
            ->each(function(&$item) use ($shopM,$pageP,&$i){
                $shop = Shops::where('shop_id',$item['shop_id'])
                        ->field('shop_id,shop_name,province_id,city_id,region_id')->find();
                $item['shop_name'] = $shop['shop_name'];
                $item['region'] = $shopM->getRegion($shop);
                $item['sort'] = $pageP*15 + $i;
                $i ++ ;
            });
    }

    /**
     * 门店业绩销售额排名
     * @param $from
     * @param $to
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function listsRankExport($from,$to){
        $shopM = new Shops();
        $list = self::whereBetweenTime('st_time',$from,$to)
            ->field('sum(total_pay_price) as sale_volume,sum(order_count) as order_count,shop_id')
            ->group('shop_id')->order('sale_volume desc')->select()
          ->each(function($item) use ($shopM){
                $shop = Shops::where('shop_id',$item['shop_id'])
                    ->field('shop_id,shop_name,province_id,city_id,region_id')->find();
                $item['shop_name'] = $shop['shop_name'];
                $item['region'] = $shopM->getRegion($shop);
            });
        $data['header'] = ['排名', '门店名称', '门店地区', '订单数', '销售额'];
        $data['filename'] = date('Y-m-d',$from).'~'.date('Y-m-d',$to).'门店业绩排名';
        $data['data'] = [];

        foreach ($list as $key=>$arr){
            $new_list['key'] = $key+1;
            $new_list['shop_name'] = $arr['shop_name'];
            $new_list['region'] = $arr['region'];
            $new_list['order_count'] = $arr['order_count'];
            $new_list['sale_volume'] = $arr['sale_volume'];
            $data['data'][] = $new_list;
        }
        return $data;
    }


}