php/app/common/dao/user/UserBillDao.php

<?php
/**
 * @package merchant
 *
 * @author xaboy
 * @day 2020-05-07
 *
 * 
 */

namespace app\common\dao\user;


use app\common\dao\BaseDao;
use app\common\model\BaseModel;
use app\common\model\user\UserBill;

/**
 * Class UserBillDao
 * @package app\common\dao\user
 * @author xaboy
 * @day 2020/6/22
 */
class UserBillDao extends BaseDao
{

    /**
     * @return BaseModel
     * @author xaboy
     * @day 2020-03-30
     */
    protected function getModel(): string
    {
        return UserBill::class;
    }

    /**
     * @param array $where
     * @param $data
     * @return int
     * @throws \think\db\exception\DbException
     * @author xaboy
     * @day 2020/6/22
     */
    public function updateBill(array $where, $data)
    {
        return UserBill::getDB()->where($where)->limit(1)->update($data);
    }

    /**
     * @param $time
     * @return \think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @author xaboy
     * @day 2020/6/22
     */
    public function getTimeoutBrokerageBill($time)
    {
        return UserBill::getDB()->where('create_time', '<=', $time)->where('category', 'brokerage')
            ->whereIn('type', ['order_one', 'order_two'])->with('user')->where('status', 0)->select();
    }

    /**
     * @param $uid
     * @return float
     * @author xaboy
     * @day 2020/6/22
     */
    public function lockBrokerage($uid)
    {
        return UserBill::getDB()->where('category', 'brokerage')
            ->whereIn('type', ['order_one', 'order_two'])->where('uid', $uid)->where('status', 0)->sum('number');
    }

    /**
     * @param $uid
     * @return float
     * @author xaboy
     * @day 2020/6/22
     */
    public function totalBrokerage($uid)
    {
        return UserBill::getDB()->where('category', 'brokerage')
            ->whereIn('type', ['order_one', 'order_two'])->where('uid', $uid)->sum('number');
    }

    /**
     * @param $uid
     * @return float
     * @author xaboy
     * @day 2020/6/22
     */
    public function yesterdayBrokerage($uid)
    {
        return getModelTime(UserBill::getDB()->where('category', 'brokerage')
            ->whereIn('type', ['order_one', 'order_two'])->where('uid', $uid), 'yesterday')->sum('number');
    }

    /**
     * @param array $where
     * @return \think\db\BaseQuery
     * @author xaboy
     * @day 2020/6/22
     */
    public function search(array $where)
    {
        return UserBill::getDB()->when(isset($where['now_money']) && in_array($where['now_money'], [0, 1, 2]), function ($query) use ($where) {
            if ($where['now_money'] == 0)
                $query->where('category', 'now_money')->whereIn('type', ['pay_product', 'recharge']);
            else if ($where['now_money'] == 1)
                $query->where('category', 'now_money')->whereIn('type', 'pay_product');
            else if ($where['now_money'] == 2)
                $query->where('category', 'now_money')->whereIn('type', 'recharge');
        })->when(isset($where['uid']) && $where['uid'] !== '', function ($query) use ($where) {
            $query->where('uid', $where['uid']);
        })->when(isset($where['mer_id']) && $where['mer_id'] !== '', function ($query) use ($where) {
            $query->where('mer_id', $where['mer_id']);
        })->when(isset($where['pm']) && $where['pm'] !== '', function ($query) use ($where) {
            $query->where('pm', $where['pm']);
        })->when(isset($where['category']) && $where['category'] !== '', function ($query) use ($where) {
            $query->where('category', $where['category']);
        })->when(isset($where['status']) && $where['status'] !== '', function ($query) use ($where) {
            $query->where('status', $where['status']);
        })->when(isset($where['is_red_brokerage']) && $where['is_red_brokerage'] !== '', function ($query) use ($where) {
            $query->where('is_red_brokerage', $where['is_red_brokerage']);
        })->when(isset($where['commission_type']) && !empty($where['commission_type']), function ($query) use ($where) {
            $query->whereIn('commission_type', $where['commission_type']);
        })->when(isset($where['not_commission_type']) && !empty($where['not_commission_type']), function ($query) use ($where) {
            $query->whereNotIn('commission_type', $where['not_commission_type']);
        });
    }

    public function searchJoin(array $where)
    {
        return UserBill::getDB()
            ->alias('a')
//            ->join('User b', 'a.uid = b.uid')
            ->Join('merchant_intention c', 'a.mer_id = c.mer_id')
            ->Join('user_city d', 'c.city_id = d.city_id')
            ->where("a.status",1)
//            ->where('a.source', 1)
            ->where("a.type_shop",0)
//            ->where("a.mer_id",'>',0)
            ->field('a.bill_id,a.pm,a.title,a.number,a.balance,a.mark,a.create_time,a.status,a.uid,a.order_sn,a.source,a.mer_id,a.commission_type')
            ->field('c.mer_name')
            ->field('d.city_name')
            ->group("a.order_sn")
            ->when(isset($where['type']) && $where['type'] !== '', function ($query) use ($where) {
                $query->where('a.type', $where['type']);
            })
            ->when(isset($where['user_time']) && $where['user_time'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['user_time'], 'a.create_time');
            })
//            ->when(isset($where['city_id']) && $where['city_id'], function ($query) use ($where) {
//                $query->where('c.city_id', $where['city_id']);
//            })
            ->when(isset($where['keyword']) && $where['keyword'] !== '', function ($query) use ($where) {
                $query->whereLike('c.mer_name', "%{$where['keyword']}%");
            });
    }
    public function searchJoins(array $where)
    {
        return UserBill::getDB()
            ->alias('a')
            ->join('User b', 'a.uid = b.uid')
            ->field('a.bill_id,a.pm,a.title,a.number,a.balance,a.mark,a.create_time,a.status,b.nickname,b.phone,a.uid,a.order_sn,a.source')
            ->when(isset($where['type']) && $where['type'] !== '', function ($query) use ($where) {
                $query->where('a.type', $where['type']);
            })->when(isset($where['date']) && $where['date'] !== '', function ($query) use ($where) {
                getModelTime($query, $where['date'], 'a.create_time');
            })->when(isset($where['keyword']) && $where['keyword'] !== '', function ($query) use ($where) {
                $query->whereLike('a.uid|b.nickname', "%{$where['keyword']}%");
            });
    }

    public function refundBrokerage($order_id, $uid)
    {
        return UserBill::getDB()->where('link_id', $order_id)->where('uid', $uid)
            ->where('category', 'brokerage')->whereIn('type', ['refund_two', 'refund_one'])->sum('number');
    }

    public function type()
    {
        return UserBill::getDB()->group('type')->column('title,type');
    }

}