lachang/app/store/model/ShopsMonthlyBonusSt.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\chef\ChefAreas;
use app\common\model\ShopsMonthlyBonusSt as ShopStModel;

/**
 * 门店销售数据每日统计模型
 * Class ShopsMonthlyBonusSt
 * @package app\common\model
 */
class ShopsMonthlyBonusSt 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(['monthBonusTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'total_bonus')
            ->withSum(['monthBonusBossTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'boss_total_bonus')
            ->withSum(['monthBonusBoss'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'boss_bonus')
            ->withSum(['monthBonusMgTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'mg_total_bonus')
            ->withSum(['monthBonusMg'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'mg_bonus')
            ->withSum(['monthBonusSellerTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'seller_total_bonus')
            ->withSum(['monthBonusSellerl'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'seller_bonus')
            ->paginate(15);
    }

    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(['monthBonusTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'total_bonus')
            ->withSum(['monthBonusBossTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'boss_total_bonus')
            ->withSum(['monthBonusBoss'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'boss_bonus')
            ->withSum(['monthBonusMgTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'mg_total_bonus')
            ->withSum(['monthBonusMg'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'mg_bonus')
            ->withSum(['monthBonusSellerTotal'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'seller_total_bonus')
            ->withSum(['monthBonusSellerl'=>function($query) use ($from,$to){
                $query->where('st_year_month','>=',$from)->where('st_year_month','<=',$to);
            }],'seller_bonus')
            ->select();

        $data['header'] = ['序号', '门店名称', '门店城市', '门店分佣', '店老板总分佣','店老板达成奖励金','店长总分佣','店老板达成奖励金','全部店员总分佣','全部店员达成奖励金'];
        $data['filename'] = $from.'~'.$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['total_sum'] = $arr['month_bonus_total_sum']?:0;
            $new_list['boss_total_sum'] = $arr['month_bonus_boss_total_sum']?:0;
            $new_list['boss_sum'] = $arr['month_bonus_boss_sum']?:0;
            $new_list['mg_total_sum'] = $arr['month_bonus_mg_total_sum']?:0;
            $new_list['mg_sum'] = $arr['month_bonus_mg_sum']?:0;
            $new_list['seller_total_sum'] = $arr['month_bonus_seller_total_sum']?:0;
            $new_list['sellerl_sum'] = $arr['month_bonus_sellerl_sum']?:0;
            $data['data'][] = $new_list;
        }
        return $data;
    }

    /**
     * 门店销售额及佣金-达量奖励金
     * @param $param
     * @return mixed
     * @author: zjwhust
     * @Time: 2022/5/13 9:36
     */
    public function shopBonusStat($param){
        // 检索查询条件
        $filter = $this->getQueryFilter($param);
        // 获取数据列表
        $query = $this->alias('bonus')
            ->field('
                shops.shop_id,
                shops.shop_name,
                shops.city_id,
                areas.name,
                sum(bonus.order_count) as order_count_total,
                sum(bonus.sales_volume) as sales_volume_total,
                sum(bonus.boss_bonus) as boss_bonus_total,
                sum(bonus.mg_bonus) as mg_bonus_total,
                sum(bonus.seller_bonus) as seller_bonus_total,
                sum(bonus.chef_bonus) as chef_bonus_total
            ')
            ->leftJoin('shops', 'shops.shop_id = bonus.shop_id')
            ->leftJoin('areas','shops.city_id=areas.id')
            ->where('shops.is_delete',0)
            ->where($filter)
//            ->order(['bonus.id' => 'desc'])
            ->group('bonus.shop_id')
            ->paginate();
        return $query;
    }

    /**
     * 设置检索查询条件
     * @param array $param
     * @return array
     */
    private function getQueryFilter(array $param): array
    {
        // 默认参数
        $params = $this->setQueryDefaultValue($param, [

        ]);
        // 检索查询条件
        $filter = [];
        // 结算月份起止时间
        if (isset($param['betweenTime'])&&!empty($params['betweenTime'])){
            $times = between_time($param['betweenTime']);
        }else{
            $times['end_time'] = strtotime(date('Y-m-01 00:00:00')) - 1 ;//上月的最后一秒
            $times['start_time'] =strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));
        }
        $from  = date('Ym',$times['start_time']);
        $to  = date('Ym',$times['end_time']);
        $filter[] = ['bonus.st_year_month', '>=', $from];
        $filter[] = ['bonus.st_year_month', '<=', $to ];
        //门店名称
        if(isset($params['shopName'])&&!empty($params['shopName'])){
            $filter[] = ['shops.shop_name','like', "%{$params['shopName']}%"];
        }
        //门店ID
        if(isset($params['shopId'])&&$params['shopId']>0){
            $filter[] = ['shops.shop_id','=',$params['shopId']];
        }
        //门店城市ID
        if(isset($params['cityId'])&&$params['cityId']>0){
            $filter[] = ['shops.city_id','=',$params['cityId']];
        }
        //门店城市名称
        if(isset($params['cityName'])&&!empty($params['cityName'])){
            $cityIds = ChefAreas::where('name','like','%'.$params['cityName'].'%')->where('level',2)->column('id');
            $filter[] = ['shops.city_id','in',$cityIds];
        }
        return $filter;
    }
}