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\enum\user\UserBillEnum;
use app\common\model\BaseModel;
use app\common\model\user\UserBill;
use app\entity\CommonEntity;
use app\entity\data\user\UserBillEntity;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;

/**
 * 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');
    }

    /**
     * @param array $billIdList
     * @return UserBillEntity[]
     */
    public function getUserBillEntityListByBillIdList(array $billIdList): array
    {
        $entityList = [];
        try {
            $dataList = UserBill::getDB()
                ->whereIn('bill_id', $billIdList)
                ->select()
                ->toArray();
            if (!empty($dataList)) {
                $entityList = array_map(function ($data) {
                    return UserBillEntity::newInstance($data);
                }, $dataList);
            }
        } catch (DataNotFoundException|ModelNotFoundException|DbException $e) {

        }
        return $entityList;
    }

    /**
     * @param string $typeShop
     * @param array $orderIdList
     * @return UserBillEntity[]
     */
    public function getUserBillEntityListByOrderIdListAndPending(string $typeShop, array $orderIdList): array
    {
        $entityList = [];
        try {
            $dataList = UserBill::getDB()
                ->where('type_shop', '=', $typeShop)
                ->whereIn('link_id', $orderIdList)
                ->where('status', '=', UserBillEnum::STATUS['PENDING']['code'])
                ->select()
                ->toArray();
            if (!empty($dataList)) {
                $entityList = array_map(function ($data) {
                    return UserBillEntity::newInstance($data);
                }, $dataList);
            }
        } catch (DataNotFoundException|ModelNotFoundException|DbException $e) {

        }
        return $entityList;
    }

    /**
     * @param string $typeShop
     * @param string $orderSn
     * @return UserBillEntity
     */
    public function getUserBillEntityByTypeShopAndOrderSn(string $typeShop, string $orderSn): UserBillEntity
    {
        $entity = UserBillEntity::newInstance();
        try {
            $dataRes = UserBill::getDB()
                ->where('type_shop', '=', $typeShop)
                ->where('order_sn', '=', $orderSn)
                ->find()
                ->toArray();
            if (!empty($dataRes)) {
                $entity = UserBillEntity::newInstance($dataRes);
            }
        } catch (DataNotFoundException|ModelNotFoundException|DbException $e) {

        }
        return $entity;
    }

    /**
     * 批量更新
     * @param $dataList
     * @return array
     */
    public function saveAll($dataList): array
    {
        try {
            return $this->getModel()::getInstance()->allowField(array_keys(reset($dataList)))->saveAll($dataList)->toArray();
        } catch (\Exception $e) {
            return [];
        }
    }
    /**
     * 根据order_id与uid查找养老金值（link_id，status=1, pm=1）
     */
    public function getYanglaoFromUidOrderId($orderId, $uid)
    {
        try {
            return $this::getModel()::getDB()
                ->where('link_id', '=', $orderId)
                ->where('status', '=', 1)
                ->where('pm', '=', 1)
                ->where('uid', '=', $uid)
                ->value('number');
        } catch (\Exception $e) {
            return [];
        }
    }
    /**
     * 根据order_id与uid查找佣金（commission_type=5）
     */
    public function getYljFromCommissionTypeUidOrderId($orderId, $uid)
    {
        try {
            return $this::getModel()::getDB()
                ->where('link_id', '=', $orderId)
                ->where('commission_type', '=', 5)
                ->where('uid', '=', $uid)
                ->value('number');
        } catch (\Exception $e) {
            return [];
        }
    }
    /**
     * 根据order_id与uid查找佣金（commission_type=3）
     */
    public function getYongjinFromCommissionTypeUidOrderId($orderId, $uid)
    {
        try {
            return $this::getModel()::getDB()
                ->where('link_id', '=', $orderId)
                ->where('commission_type', '=', 3)
                ->where('uid', '=', $uid)
                ->value('number');
        } catch (\Exception $e) {
            return [];
        }
    }
    /**
     * 根据order_id查找第二推荐佣金uid（commission_type=3，排除当前用户和一级推荐人）
     */
    public function getYongjinTwoUidFromOrderId($orderId, $uid, $spreadUid)
    {
        try {
            $value = $this::getModel()::getDB()
                ->where('link_id', '=', $orderId)
                ->where('commission_type', '=', 3)
                ->whereNotIn('uid', [$uid, $spreadUid])
                ->value('uid');
            return $value ?: 0;
        } catch (\Exception $e) {
            return 0;
        }
    }

    /**
     * @param $params
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     * @author 史晨
     * @date 2026/2/11 14:45
     * 获取数据列表
     */
    public function getListData($params)
    {
        $query = $this::getModel()::getDB();
        if (isset($params['start']) && $params['start'] != '') {
            if (!is_numeric($params['start'])) {
                $params['start'] = strtotime($params['start']);
            }
            $query->where('take_time', '>=', $params['start']);
        }
        if (isset($params['end']) && $params['end'] != '') {
            if (!is_numeric($params['end'])) {
                $params['end'] = strtotime($params['end']);
            }
            $query->where('take_time', '<=', $params['end']);
        }

        if (isset($params['status']) && $params['status'] != '') {
            $query->where('status', '=', $params['status']);
        }

        if (isset($params['merId']) && $params['merId'] != '') {
            $query->whereIn('mer_id', $params['merId']);
        }

        if (isset($params['commissionType']) && $params['commissionType'] != '') {
            $query->whereIn('commission_type', $params['commissionType']);
        }

        return $query->select()->toArray();
    }
}