mall/modules/client/models/v1/WorkerForm.php

<?php

namespace app\modules\client\models\v1;

use app\constants\OptionSetting;
use app\models\District;
use app\models\Favorite;
use app\models\Goods;
use app\models\GoodsPic;
use app\models\Option;
use app\models\Order;
use app\models\OrderComment;
use app\models\OrderDetail;
use app\models\Worker;
use app\models\WorkerCat;
use app\models\WorkerCatExt;
use app\models\WorkerGoods;
use app\models\WorkerGoodsCat;
use app\models\WorkerGoodsExt;
use app\models\WorkerOrderExt;
use app\models\WorkerPic;
use app\modules\client\models\v1\order\OrderForm;
use yii\base\Model;

class WorkerForm extends Model
{
    public $latitude;
    public $longitude;
    public $sort;
    public $cat_id;
    public $distance;
    public $district_id;
    public $time_type;
    public $store_id;
    public $name;

    public $id;

    public function rules()
    {
        return [
            [['latitude', 'longitude', 'distance', 'name', 'cat_id'], 'string'],
            [['sort', 'district_id', 'time_type', 'store_id', 'id'], 'integer']
        ];
    }


    //获取服务人员列表
    public function workerList()
    {
        try {
            $store_id = $this->store_id;
            $latitude = !empty($this->latitude) ? $this->latitude : '0.00';
            $longitude = !empty($this->longitude) ? $this->longitude : '0.00';
            $cat_id = $this->cat_id;
	        $name = $this->name;
            $sort = $this->sort ?? 0;
            $distance = $this->distance;
            $distance_ = 0;
            $district_id = $this->district_id;
            $time_type = $this->time_type;
            //判断附近
            if (strpos($distance, 'km') !== false) {
                //1-5km
                $km = strpos($distance, 'km');
                $distance_ = substr($distance, 0, $km);
                $distance_ = $distance_ * 1000;
            } else {
                if (strpos($distance, 'm') !== false) {
                    //500m
                    $km = strpos($distance, 'm');
                    $distance_ = substr($distance, 0, $km);
                }
            }

//            //收藏
//            $favorite = "SELECT SUM(worker_id) as favorite_num, worker_id FROM cyy_favorite GROUP BY worker_id";

            $sql = $this->getSql($latitude, $longitude, $store_id);
            //存在分类
            if ($cat_id) {
                $cat_id = explode(',', $cat_id);
                $worker_ = WorkerCatExt::find()->where(['cat_id' => $cat_id, 'is_delete' => 0])->select('worker_id')->column();
                $worker_ = array_merge($worker_, [0]);
                $worker_ = implode(',', $worker_);
                $sql .= " AND (w.id in ({$worker_})) ";
            }
	    
	        //存在名称搜索
            if ($name) {
                $sql .= " AND w.name LIKE '%{$name}%' ";
            }

            //判断距离
            if ($distance_ > 0) {
                $sql .= " AND w_.distance < {$distance_}";
            } else {
                if ($district_id) {
                    $sql .= " AND w.district_id = {$district_id}";
                }
            }

            //判断服务时间
            if ($time_type) {
                switch ($time_type) {
                    case 1:
                        $start_time = 0000;
                        $end_time = 0500;
                        break;
                    case 2:
                        $start_time = 0500;
                        $end_time = 1000;
                        break;
                    case 3:
                        $start_time = 1000;
                        $end_time = 1400;
                        break;
                    case 4:
                        $start_time = 1400;
                        $end_time = 1700;
                        break;
                    case 5:
                        $start_time = 1700;
                        $end_time = 2100;
                        break;
                    case 6:
                        $start_time = 2100;
                        $end_time = 2400;
                        break;
                    default:
                        $start_time = 0000;
                        $end_time = 2400;
                        break;
                }
//                |_________|
//                   |____|
//              |_____|
//                      |______|
//            |__________________|
//
                $sql .= " AND ((w.book_start_time > {$start_time} AND w.book_end_time < {$end_time}) OR
                 (w.book_start_time < {$start_time} AND w.book_end_time > {$start_time} AND w.book_end_time < {$end_time}) OR
                 (w.book_start_time > {$start_time} AND w.book_start_time < {$end_time} AND w.book_end_time > {$end_time}) OR
                 (w.book_start_time < {$start_time} AND w.book_end_time > {$end_time})
                 ) AND (w.book_start_time > 0 OR w.book_end_time > 0)";
            } else {
                $end_time = date("Hi");
                $sql .= " AND (w.book_end_time > {$end_time}) ";
            }





            //距离排序
            switch ($sort) {
                case 1://距离优先
                    $sql .= " ORDER BY distance ASC";
                    break;
                case 2://好评优先
                    $sql .= " ORDER BY w.star DESC";
                    break;
                case 3://销量优先
                    $sql .= " ORDER BY o.order_num DESC";
                    break;
                default:
                    $sql .= " ORDER BY w.id ASC";
                    break;
            }

            $pageNo = get_params('pageNo', get_params('page', 1));
            $pageSize = get_params('pageSize', \Yii::$app->params['pageSize']);
            $pageInitNum = ($pageNo - 1) * $pageSize;
            $count = \Yii::$app->db->createCommand($sql)->query()->count();

            $sql .= " LIMIT {$pageSize} OFFSET {$pageInitNum}";
            $list = \Yii::$app->db->createCommand($sql)->queryAll();
            if (!$list) {
                return [
                    'code' => 0,
                    'msg' => "获取成功",
                    'data' => [
                        'data' => [],
                        'pageNo' => $pageNo,
                        'totalCount' => $count
                    ]
                ];
            }
            foreach ($list as &$item) {
                //距离格式化
                $item['distance'] = $this->distance((float)$item['distance']);
                //收藏列表
                $item['favorite_num'] = Favorite::find()->where(['worker_id' => $item['id'], 'is_delete' => 0])
                    ->select('id')->count() ?? 0;
                $order_ids = WorkerOrderExt::find()->where(['worker_id' => $item['id']])->select('order_id')->column();
                $item['comment_num'] = 0;
                //评价数量
                if (!empty($order_ids)) {
                    $item['comment_num'] = OrderComment::find()->where(['order_id' => $order_ids])->select('id')->count();
                }
                //是否开业
                $item['open_status'] = (int)$item['open_status'];
                $time = str_pad($item['book_start_time'], 4, 0, STR_PAD_LEFT);
                //工作开始时间
                $item['begin_time'] = substr($time, -4, 2) . ':' . substr($time, -2);
                $times = new OrderForm();
                $result = $times->workerTime($item['id']);

                if ($result['code'] === 0) {

                    $arr['time'] = $result['data'];
                    if ($arr['time']) {
                        $times = $result['data'][0]['time'][0]['times'];
                        foreach ($times as $time) {
                            if ($time['status'] === 0) {
                                $item['begin_time'] = date('H:i', $time['time_int']);
                                break;
                            }
                        }
                    }
                }


                //订单数量
                $item['order_num'] = (int)$item['order_num'];
                $certified = [];
                //实名认证
                if ($item['tel']) {
                    array_push($certified, 1);
                }
                $worker_pic = WorkerPic::findOne(['worker_id' => $item['id']]);
                //资质认证
                array_push($certified, 2);
                if ($worker_pic) {
                    array_push($certified, 3);
                }
                //存在图片
                $item['certified'] = implode(',', $certified);
                unset($item['book_start_time']);
            }

            return [
                'code' => 0,
                'msg' => "获取成功",
                'data' => [
                    'data' => $list,
                    'pageNo' => $pageNo,
                    'totalCount' => $count
                ]
            ];


        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }

    //获取服务人员列表筛选项目
    public function workerIndex() {
        $latitude = !empty($this->latitude) ? $this->latitude : '0.00';
        $longitude = !empty($this->longitude) ? $this->longitude : '0.00';
        $store_id = $this->store_id;
        //获取区列表
        $tencent_map_key = Option::get('tencent_map_key', 0, 'saas', '')['value'];
        if (get_store_id() > 0) {
            $tencent_map_key = Option::get(OptionSetting::TENCENT_MAP_KEY, get_store_id(), 'pay', Option::get(OptionSetting::TENCENT_MAP_KEY, get_store_id(), 'store', '')['value'] ?: $tencent_map_key)['value'];
        }
        $place_url = 'https://apis.map.qq.com/ws/geocoder/v1/?location=' . $latitude . ',' . $longitude . '&key=' . $tencent_map_key;
        $json_place = file_get_contents($place_url);
        $place_arr = json_decode($json_place, true);
        $address = $place_arr['result']['address_component'];
        $city_id = District::findOne(['name' => $address['city'], 'level' => 'city'])->id;
        $district = [];
        if ($city_id) {
            $district = District::find()->where(['parent_id' => $city_id, 'level' => 'district'])->select('id, name')->asArray()->all();
        }

        //获取分类
        $work_cate = WorkerCat::find()->where(['store_id' => $store_id, 'is_delete' => 0, 'is_show' => 1])
            ->select('id, name')->orderBy('sort desc')->asArray()->all();


        return [
            'code' => 0,
            'msg' => 'success',
            'data' => [
                'district' => $district,
                'cate' => $work_cate
            ]
        ];

    }

    //获取服务人员详情
    public function workerInfo() {
        try {
            $id = $this->id;
            $latitude = !empty($this->latitude) ? $this->latitude : '0.00';
            $longitude = !empty($this->longitude) ? $this->longitude : '0.00';
            if (!$id) {
                return [
                    'code' => 0,
                    'msg' => '获取成功',
                    'data' => [
                        'info' => []
                    ]
                ];
            }
            $sql = "SELECT `id`, `name`, `logo`, `desc`, `visited`, `tel`, acos(cos({$latitude}*pi()/180 )*cos(lat*pi()/180)*cos({$longitude}*pi()/180 -lng*pi()/180)+sin({$latitude}*pi()/180 )*sin(lat*pi()/180))*6370996.81  as 
                distance FROM cyy_worker WHERE id = {$id}";
            $info = \Yii::$app->db->createCommand($sql)->queryOne();
            if (!$info) {
                return [
                    'code' => 0,
                    'msg' => '获取成功',
                    'data' => [
                        'info' => []
                    ]
                ];
            }
            $order_ext = WorkerOrderExt::find()->where(['worker_id' => $info['id']])->select('order_id')->asArray()->all();
            $info['distance'] = $this->distance($info['distance']);
            $info['order_num'] = count($order_ext);
            $info['favorite_num'] = Favorite::find()->where(['worker_id' => $info['id']])->select('order_id')
                    ->count() ?? 0;
            $info['is_favorite'] = (int)Favorite::find()->where(['worker_id' => $info['id'], 'user_id' => get_user_id()])->one();
            $order_id = array_column($order_ext, 'order_id');

            //好评比例
            $order_comment = OrderComment::find()->where(['order_id' => $order_id])->select('score')->column();
            $info['proportion'] = 100;

            if (!empty($order_comment)) {
                $max = array_sum(array_filter($order_comment, function($item) {
                    if ($item > 2) {
                        return $item;
                    }
                }));
                $total = array_sum($order_comment);
                if ($total > 0) {
                    $info['proportion'] = sprintf('%.2f', (($max / $total) * 100));
                }
            }
            $info['pic_url'] = WorkerPic::find()->where(['worker_id' => $id, 'is_delete' => 0])->asArray()->all();
            foreach ($info['pic_url'] as &$value) {
                if (strpos($value['pic_url'], 'https:') === false && strpos($value['pic_url'], 'http:') === false) {
                    $value['pic_url'] = 'https:' . $value['pic_url'];
                }
            }

            $info['goods_list'] = WorkerGoods::find()->alias('wg')->where(['wg.worker_id' => $info['id'],
                'wg.status' => 1, 'g.product_type' => Goods::GOODS_TYPE_WORKER, 'g.status' => 1, 'g.is_delete' => 0])
                ->leftJoin(['g' => Goods::tableName()], 'wg.goods_id = g.id')
                ->leftJoin(['wge' => WorkerGoodsExt::tableName()], 'wge.goods_id = g.id')
                ->select('g.id, g.name, g.price, g.original_price, g.cover_pic, wge.desc')->asArray()->all();
            foreach ($info['goods_list'] as &$item) {
                $item['price'] = sprintf("%.2f", $item['price']);
                $item['original_price'] = sprintf("%.2f", $item['original_price']);
                if ($item['desc']) {
                    $item['desc'] = explode(',', $item['desc']);
                } else {
                    $item['desc'] = null;
                }
            }

            $certified = [];
            //实名认证
            if ($info['tel']) {
                array_push($certified, 1);
            }
            $worker_pic = WorkerPic::findOne(['worker_id' => $info['id']]);
            //资质认证
            array_push($certified, 2);
            if ($worker_pic) {
                array_push($certified, 3);
            }
            //存在图片
            $info['certified'] = implode(',', $certified);

            //增加浏览量
            $worker = Worker::findOne($info['id']);
            $worker->visited += 1;
            $worker->save();
            return [
                'code' => 0,
                'msg' => '获取成功',
                'data' => [
                    'info' => $info
                ]
            ];

        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }


    //获取商品详情
    public function workerGoodsInfo() {
        try {
            $id = $this->id;
            $store_id = $this->store_id;
            $latitude = !empty($this->latitude) ? $this->latitude : '0.00';
            $longitude = !empty($this->longitude) ? $this->longitude : '0.00';
            $info = Goods::find()->alias('g')->where(['g.product_type' => Goods::GOODS_TYPE_WORKER, 'g.status' => 1, 'g.is_delete' => 0, 'g.id' => $id])
                ->leftJoin(['wge' => WorkerGoodsExt::tableName()], 'g.id = wge.goods_id')->select('g.id, g.name, g.price, g.service, wge.desc, g.detail, wge.warn, g.cover_pic, wge.bind_worker, g.virtual_sales')->asArray()->one();
//            $info['order_num'] = OrderDetail::find()->alias('od')->where(['od.goods_id' => $info['id']])
//                ->leftJoin(['o' => Order::tableName()], 'od.order_id = o.id')
//                ->select('od.id')->groupBy('o.user_id')->count() ?? 0;
            $info['bind_worker'] = (int)$info['bind_worker'];
            $info['order_num'] = OrderDetail::find()->alias('od')->where(['od.goods_id' => $info['id'], 'o.is_pay' => 1])
                ->leftJoin(['o' => Order::tableName()], 'o.id = od.order_id')->select('o.user_id')->groupBy('o.user_id')->count() ?? 0;

            $info['order_num'] += $info['virtual_sales'];
            $info['goods_pic'] = GoodsPic::find()->where(['is_delete' => 0, 'goods_id' => $info['id']])->select('pic_url')->asArray()->all() ?: [];
            $worker_id = WorkerGoods::find()->where(['goods_id' => $id, 'status' => 1])->select('worker_id')->column();
            $worker_id = array_merge($worker_id, [0]);
            $worker_id = implode(',', $worker_id);
            $sql = $this->getSql($latitude, $longitude, $store_id);

//            $info['goods_pic'] = array_merge($info['goods_pic'], [['pic_url' => $info['cover_pic']]]);

            $time_ = date("Hi");
            $sql .= " AND w.id in ({$worker_id}) AND w.open_status = 1 AND (w.book_end_time > {$time_}) ORDER BY distance ASC";
            $info['worker_list'] = \Yii::$app->db->createCommand($sql)->queryAll();
            if ($info['worker_list']) {
                foreach ($info['worker_list'] as &$item) {
                    $item['distance'] = $this->distance($item['distance']);
                    $item['open_status'] = (int)$item['open_status'];
                    $item['favorite_num'] = Favorite::find()->where(['worker_id' => $item['id'], 'is_delete' => 0])
                        ->select('id')->count();
                    $order_ids = WorkerOrderExt::find()->where(['worker_id' => $item['id']])->select('order_id')->column();
                    $item['comment_num'] = 0;
                    //评价数量
                    if (!empty($order_ids)) {
                        $item['comment_num'] = OrderComment::find()->where(['order_id' => $order_ids])->select('id')->count();
                    }
                    //工作开始时间
                    $time = str_pad($item['book_start_time'], 4, 0, STR_PAD_LEFT);
                    $item['begin_time'] = substr($time, -4, 2) . ':' . substr($time, -2);
                    $times = new OrderForm();
                    $result = $times->workerTime($item['id']);
                    if ($result['code'] === 0) {
                        $arr['time'] = $result['data'];
                        if ($arr['time']) {
                            $times = $result['data'][0]['time'][0]['times'];
                            foreach ($times as $time) {
                                if ($time['status'] === 0) {
                                    $item['begin_time'] = date('H:i', $time['time_int']);
                                    break;
                                }
                            }
                        }
                    }
                    //订单数量
                    $item['order_num'] = (int)$item['order_num'];
                    $certified = [];
                    //实名认证
                    if ($item['tel']) {
                        array_push($certified, 1);
                    }
                    $worker_pic = WorkerPic::findOne(['worker_id' => $item['id']]);
                    //资质认证
                    array_push($certified, 2);
                    if ($worker_pic) {
                        array_push($certified, 3);
                    }
                    //存在图片
                    $item['certified'] = implode(',', $certified);
                    unset($item['book_start_time']);
                }
            }


            if ($info['desc']) {
                $info['desc'] = explode(',', $info['desc']);
            } else {
                $info['desc'] = null;
            }

            if ($info['service']) {
                $info['service'] = explode(',', $info['service']);
            } else {
                $info['service'] = null;
            }

            return [
                'code' => 0,
                'msg' => '获取成功',
                'data' => [
                    'info' => $info
                ]
            ];

        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }

    //获取商品列表
    public function workerGoodsCateList() {
        try {
            $store_id = $this->store_id;
            $data = WorkerGoodsCat::find()->where(['store_id' => $store_id, 'is_delete' => 0, 'is_show' => 1])
                ->select('id, name')
                ->orderBy('sort desc')->asArray()->all();

            return [
                'code' => 0,
                'msg' => 'success',
                'data' => [
                    'list' => $data
                ]
            ];

        }  catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }

    //获取服务类目列表
    public function workerCateList() {
        try {
            $store_id = $this->store_id;
            $data = WorkerCat::find()->where(['store_id' => $store_id, 'is_delete' => 0, 'is_show' => 1])
                ->select('id, name')
                ->orderBy('sort desc')->asArray()->all();

            return [
                'code' => 0,
                'msg' => 'success',
                'data' => [
                    'list' => $data
                ]
            ];

        }  catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }

    //获取商品列表
    public function workerGoodsList() {
        try {
            $cat_id = $this->cat_id;
            $store_id = $this->store_id;
            $query = WorkerGoodsExt::find()->alias('wge')->leftJoin(['g' => Goods::tableName()], 'wge.goods_id = g.id')
                ->where(['wge.cat_id' => $cat_id, 'wge.store_id' => $store_id, 'g.is_delete' => 0, 'g.status' => 1])
            ->select('g.id, g.name,g.cover_pic, g.price, g.original_price, wge.desc, g.virtual_sales')->orderBy('g.sort desc');

            $list = pagination_make($query);
            foreach ($list['list'] as &$item) {
//                $item['order_num'] = OrderDetail::find()->alias('od')->leftJoin(['o' => Order::tableName()], 'o.id = od.order_id')
//                    ->where(['od.goods_id' => $item['id'], 'o.is_pay' => 1])->groupBy('o.user_id')->select('od.id')->count() ?? 0;
                $item['order_num'] = OrderDetail::find()->alias('od')->where(['od.goods_id' => $item['id'], 'o.is_pay' => 1])
                        ->leftJoin(['o' => Order::tableName()], 'o.id = od.order_id')->select('od.num')->sum('od.num') ?? 0;
                $item['order_num'] += $item['virtual_sales'];
                if ($item['desc']) {
                    $item['desc'] = explode(',', $item['desc']);
                } else {
                    $item['desc'] = null;
                }

            }
            return [
                'code' => 0,
                'msg' => 'success',
                'data' => [
                    'list' => $list['list'],
                    'pageNo' => $list['pageNo'],
                    'totalCount' => $list['totalCount']
                ]
            ];

        }  catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }




    public function distance($distance)
    {
        if ($distance == -1) {
            return -1;
        }
        if ($distance > 1000) {
            $distance = round($distance / 1000, 2) . 'km';
        } else {
            $distance = round($distance, 2);
            $distance .= 'm';
        }
        return $distance;
    }

    public function getSql($latitude, $longitude, $store_id) {
        //服务订单数量 （用于排序）
        $order = "SELECT count(order_id) as order_num, worker_id FROM cyy_worker_order_ext GROUP BY worker_id";


        //中间嵌套相同的cyy_worker用于判断距离
        $sql = "SELECT w.id, w.name, w.logo, w.lat, w.lng, w.open_status, w.star, w_.distance, o.order_num, w.tel, w.book_start_time
                FROM cyy_worker w 
                LEFT JOIN (select id, acos(cos({$latitude}*pi()/180 )*cos(lat*pi()/180)*cos({$longitude}*pi()/180 -lng*pi()/180)+sin({$latitude}*pi()/180 )*sin(lat*pi()/180))*6370996.81  as 
                distance from cyy_worker) as w_ ON w_.id = w.id 
                LEFT JOIN ( {$order} ) AS o ON o.worker_id = w.id 
                WHERE w.store_id = {$store_id} AND w.status = " . Worker::STATUS_VALID;//LEFT JOIN ( {$favorite} ) as f ON f.worker_id = w.id

        return $sql;
    }
}