php/app/common/dao/movie/order/MovieOrderDao.php

<?php
/**
 * @package merchant
 *
 * @author xaboy
 * @day 2020/6/1
 *
 *
 */

namespace app\common\dao\movie\order;


use app\common\dao\BaseDao;
use app\common\model\movie\order\MovieOrder;
use app\common\model\user\User;
use think\facade\Db;

/**
 * Class MovieOrderDao
 * @package app\common\dao\movie\order
 * @author xaboy
 * @day 2020/6/8
 */
class MovieOrderDao extends BaseDao
{

    /**
     * @return string
     * @author xaboy
     * @day 2020/6/8
     */
    protected function getModel(): string
    {
        return MovieOrder::class;
    }

    /**
     * @param array $where
     * @param int $sysDel
     * @return \think\db\BaseQuery
     * @author xaboyCRMEB
     * @day 2020/6/16
     */
    public function search(array $where, $sysDel = 0)
    {
        return MovieOrder::getDB()
            ->when(($sysDel !== null), function ($query) use ($sysDel) {
                $query->where('is_system_del', $sysDel);
            })
            ->when(isset($where['pay_type']) && $where['pay_type'] != -1 && $where['pay_type'] >= 0, function ($query) use ($where) {
                $query->where('pay_type', $where['pay_type']);
            })
            ->when(isset($where['order_type']) && $where['order_type'] && $where['order_type'] >= 0, function ($query) use ($where) {
                $query->where('order_type', $where['order_type']);
            })
            ->when(isset($where['distribution_mode']) && $where['distribution_mode'] && $where['distribution_mode'] >= 0, function ($query) use ($where) {
                $query->where('distribution_mode', $where['distribution_mode']);
            })
            ->when(isset($where['status']) && $where['status'] !== '', function ($query) use ($where) {
                if ($where['status'] == -2)
                    $query->where('paid', 1);
                else
                    $query->where('status', $where['status']);
            })
            ->when(isset($where['uid']) && $where['uid'] !== '', function ($query) use ($where) {
                $query->where('uid', $where['uid']);
            })
            ->when(isset($where['take_order']), function ($query) use ($where) {
                $query->where('order_type', 1)->where('status', '>=', 2);
            })
            ->when(isset($where['mer_id']) && $where['mer_id'] !== '', function ($query) use ($where) {
                $query->where('mer_id', $where['mer_id']);
            })
            ->when(isset($where['date']) && $where['date'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['date']);
            })
            ->when(isset($where['verify_date']) && $where['verify_date'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['verify_date']);
            })
            ->when(isset($where['order_sn']) && $where['order_sn'] !== '', function ($query) use ($where) {
                $query->where('order_sn', 'like', '%' . $where['order_sn'] . '%');
            })
            ->when(isset($where['paid']) && $where['paid'] !== '', function ($query) use ($where) {
                $query->where('paid', $where['paid']);
            })
            ->when(isset($where['is_del']) && $where['is_del'] !== '', function ($query) use ($where) {
                $query->where('is_del', $where['is_del']);
            })
            ->when(isset($where['service_id']) && $where['service_id'] !== '', function ($query) use ($where) {
                $query->where('service_id', $where['service_id']);
            })
            ->when(isset($where['username']) && $where['username'] !== '', function ($query) use ($where) {
                $uid = User::where('nickname', 'like', "%{$where['username']}%")
                    ->whereOr('phone', 'like', "%{$where['username']}%")
                    ->column('uid');
                $query->where('uid', 'in', $uid);
            })
            ->when(isset($where['gzc']) && $where['gzc'] !== '', function ($query) use ($where) {
                $query->where('gzc', $where['gzc']);
            })
            ->when(isset($where['keywords']) && $where['keywords'] !== '', function ($query) use ($where) {
                $query->where(function ($query) use ($where) {
                    $query->where('real_name', 'like', "%" . $where['keywords'] . "%")
                        ->whereOr('user_phone', 'like', "%{$where['keywords']}%")
                        ->whereOr('order_sn', 'like', '%' . $where['keywords'] . '%')
                        ->whereOr('verify_code', 'like', '%' . $where['keywords'] . '%');
                });
            })
            ->when(isset($where['reconciliation_type']) && $where['reconciliation_type'] !== '', function ($query) use ($where) {
                $query->when($where['reconciliation_type'], function ($query) use ($where) {
                    $query->where('reconciliation_id', '<>', 0);
                }, function ($query) use ($where) {
                    $query->where('reconciliation_id', 0);
                });
            })->order('create_time DESC');
    }

    /**
     * @param array $where
     * @param int $sysDel
     * @return \think\db\BaseQuery
     * @author xaboyCRMEB
     * @day 2020/6/16
     */
    public function merSearch(array $where, $sysDel = 0)
    {

        return MovieOrder::getDB()
            ->alias('so')
            ->leftJoin('user_bill ub', 'ub.order_sn=so.order_sn')
            ->when(($sysDel !== null), function ($query) use ($sysDel) {
                $query->where('so.is_system_del', $sysDel);
            })
            ->when(isset($where['pay_type']) && $where['pay_type'] != -1 && $where['pay_type'] >= 0, function ($query) use ($where) {
                $query->where('so.pay_type', $where['pay_type']);
            })
            ->when(isset($where['order_type']) && $where['order_type'] && $where['order_type'] >= 0, function ($query) use ($where) {
                $query->where('so.order_type', $where['order_type']);
            })
            ->when(isset($where['distribution_mode']) && $where['distribution_mode'] && $where['distribution_mode'] >= 0, function ($query) use ($where) {
                $query->where('so.distribution_mode', $where['distribution_mode']);
            })
            ->when(isset($where['status']) && $where['status'] !== '', function ($query) use ($where) {
                if ($where['status'] == -2)
                    $query->where('so.paid', 1);
                else
                    $query->where('so.status', $where['status']);
            })
            ->when(isset($where['uid']) && $where['uid'] !== '', function ($query) use ($where) {
                $query->where('so.uid', $where['uid']);
            })
            ->when(isset($where['take_order']), function ($query) use ($where) {
                $query->where('so.order_type', 1)->where('status', '>=', 2);
            })
            ->when(isset($where['mer_id']) && $where['mer_id'] !== '', function ($query) use ($where) {
                $query->where('so.mer_id', $where['mer_id']);
            })
            ->when(isset($where['date']) && $where['date'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['date']);
            })
            ->when(isset($where['verify_date']) && $where['verify_date'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['verify_date']);
            })
            ->when(isset($where['order_sn']) && $where['order_sn'] !== '', function ($query) use ($where) {
                $query->where('so.order_sn', 'like', '%' . $where['order_sn'] . '%');
            })
            ->when(isset($where['paid']) && $where['paid'] !== '', function ($query) use ($where) {
                $query->where('so.paid', $where['paid']);
            })
            ->when(isset($where['is_del']) && $where['is_del'] !== '', function ($query) use ($where) {
                $query->where('so.is_del', $where['is_del']);
            })
            ->when(isset($where['service_id']) && $where['service_id'] !== '', function ($query) use ($where) {
                $query->where('so.service_id', $where['service_id']);
            })
            ->when(isset($where['username']) && $where['username'] !== '', function ($query) use ($where) {
                $uid = User::where('so.nickname', 'like', "%{$where['username']}%")
                    ->whereOr('so.phone', 'like', "%{$where['username']}%")
                    ->column('so.uid');
                $query->where('so.uid', 'in', $uid);
            })
            ->when(isset($where['gzc']) && $where['gzc'] !== '', function ($query) use ($where) {
                $query->where('so.gzc', $where['gzc']);
            })
            ->when(isset($where['keywords']) && $where['keywords'] !== '', function ($query) use ($where) {
                $query->where(function ($query) use ($where) {
                    $query->where('so.real_name', 'like', "%" . $where['keywords'] . "%")
                        ->whereOr('so.user_phone', 'like', "%{$where['keywords']}%")
                        ->whereOr('so.order_sn', 'like', '%' . $where['keywords'] . '%')
                        ->whereOr('so.verify_code', 'like', '%' . $where['keywords'] . '%');
                });
            })
            ->when(isset($where['reconciliation_type']) && $where['reconciliation_type'] !== '', function ($query) use ($where) {
                $query->when($where['reconciliation_type'], function ($query) use ($where) {
                    $query->where('so.reconciliation_id', '<>', 0);
                }, function ($query) use ($where) {
                    $query->where('so.reconciliation_id', 0);
                });
            })->order('so.create_time DESC');
    }

    /**
     * @param $id
     * @param $uid
     * @return array|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @author xaboy
     * @day 2020/6/11
     */
    public function userOrder($id, $uid)
    {
        return MovieOrder::getDB()->where('order_id', $id)->where('uid', $uid)->where('is_del', 0)->where('paid', 1)->where('is_system_del', 0)->find();
    }

    /**
     * @param array $where
     * @param $ids
     * @return \think\db\BaseQuery
     * @author xaboy
     * @day 2020/6/26
     */
    public function usersOrderQuery(array $where, $ids)
    {
        return MovieOrder::getDB()->whereIn('uid', $ids)->when(isset($where['date']) && $where['date'] !== '', function ($query) use ($where) {
            getModelTime($query, $where['date'], 'pay_time');
        })->when(isset($where['keyword']) && $where['keyword'] !== '', function ($query) use ($where) {
            $query->where('order_id|order_sn', $where['keyword']);
        })->where('paid', 1)->order('pay_time DESC');
    }

    /**
     * @param $field
     * @param $value
     * @param int|null $except
     * @return bool
     * @author xaboy
     * @day 2020/6/11
     */
    public function fieldExists($field, $value, ?int $except = null): bool
    {
        return ($this->getModel()::getDB())->when($except, function ($query) use ($field, $except) {
                $query->where($field, '<>', $except);
            })->where($field, $value)->count() > 0;
    }

    public function orderUserNum($date, $paid = null, $merId = null)
    {
        return MovieOrder::getDB()->when($paid, function ($query, $paid) {
            $query->where('paid', $paid);
        })->when($merId, function ($query, $merId) {
            $query->where('mer_id', $merId);
        })->when($date, function ($query, $date) {
            getModelTime($query, $date, 'pay_time');
        })->group('uid')->count();
    }

    public function orderUserGroup($date, $paid = null, $merId = null)
    {
        return MovieOrder::getDB()->when($paid, function ($query, $paid) {
            $query->where('paid', $paid);
        })->when($merId, function ($query, $merId) {
            $query->where('mer_id', $merId);
        })->when($date, function ($query, $date) {
            getModelTime($query, $date, 'pay_time');
        })->group('uid')->field(Db::raw('uid,sum(pay_price) as pay_price'))->select();
    }

    public function orderPrice($date, $paid = null, $merId = null)
    {
        return MovieOrder::getDB()->when($paid, function ($query, $paid) {
            $query->where('paid', $paid);
        })->when($merId, function ($query, $merId) {
            $query->where('mer_id', $merId);
        })->when($date, function ($query, $date) {
            getModelTime($query, $date, 'pay_time');
        })->sum('pay_price');
    }

    public function orderGroupNum($date, $merId = null)
    {
        return MovieOrder::getDB()->field(Db::raw('sum(pay_price) as pay_price,count(*) as total,count(distinct uid) as user,from_unixtime(unix_timestamp(pay_time),\'%m-%d\') as `day`'))
            ->where('paid', 1)->when($date, function ($query, $date) {
                getModelTime($query, $date, 'pay_time');
            })->when($merId, function ($query, $merId) {
                $query->where('mer_id', $merId);
            })->order('day ASC')->group('day')->select();
    }

    public function orderGroupNumPage($where, $page, $limit, $merId = null)
    {
        return MovieOrder::getDB()->when(isset($where['dateRange']), function ($query) use ($where) {
            getModelTime($query, date('Y/m/d H:i:s', $where['dateRange']['start']) . '-' . date('Y/m/d H:i:s', $where['dateRange']['stop']), 'pay_time');
        })->field(Db::raw('sum(pay_price) as pay_price,count(*) as total,count(distinct uid) as user,from_unixtime(unix_timestamp(pay_time),\'%m-%d\') as `day`'))
            ->where('paid', 1)->when($merId, function ($query, $merId) {
                $query->where('mer_id', $merId);
            })->order('day DESC')->page($page, $limit)->group('day')->select();
    }
}