ball/app/adminapi/lists/master_worker/EngineerBillLists.php

<?php
// +----------------------------------------------------------------------
// | likeadmin快速开发前后端分离管理后台（PHP版）
// +----------------------------------------------------------------------
// | 欢迎阅读学习系统程序代码，建议反馈是我们前进的动力
// | 开源版本可自由商用，可去除界面版权logo
// | gitee下载：https://gitee.com/likeshop_gitee/likeadmin
// | github下载：https://github.com/likeshop-github/likeadmin
// | 访问官网：https://www.likeadmin.cn
// | likeadmin团队 版权所有 拥有最终解释权
// +----------------------------------------------------------------------
// | author: likeadminTeam
// +----------------------------------------------------------------------

namespace app\adminapi\lists\master_worker;


use app\adminapi\lists\BaseAdminDataLists;
use app\common\model\master_worker\EngineerSettlement;
use app\common\lists\ListsSearchInterface;
use app\common\model\master_worker\MasterWorker;
use app\common\model\master_worker\MasterWorkerAccountLog;
use think\facade\Db;


/**
 * EngineerSettlement列表
 * Class EngineerSettlementLists
 * @package app\adminapi\lists
 */
class EngineerBillLists extends BaseAdminDataLists implements ListsSearchInterface
{


    /**
     * @notes 设置搜索条件
     * @return \string[][]
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function setSearch(): array
    {
        return [];
    }


    /**
     * @notes 获取列表
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function lists(): array
    {
        $deadline_time = strtotime($this->params['deadline_time'])+86400;
        $settlement_type = $this->params['settlement_type'];
        return Db::name('master_worker')->alias('a')->field([
            'a.id','a.real_name','a.nickname','a.worker_number','a.user_money','e.account_holder','e.bank_name','e.opening_branch','e.account as bank_account',
            Db::raw("sum(b.amount_change) as left_amount")
        ])
            ->leftJoin('master_settlement_details b', 'a.id = b.master_worker_id AND b.status = 1')
            ->leftJoin('bank_account e', 'a.id = e.worker_id')
            ->where('a.audit_state', '=', 1)
            ->where('a.user_money', '>', 0)
            ->where('b.status', '=', 1)
            //->where('a.settlement_type', 'in', $settlement_type)
            ->where('a.settlement_type', '=', $settlement_type)
            ->where('b.change_time', '<', $deadline_time)
            ->group('a.id')
            ->order('a.id desc')
            ->having('left_amount > 0')
            ->limit($this->limitOffset, $this->limitLength)
            ->select()->toArray();

        /*return Db::name('master_worker')->alias('a')->field([
                'a.id','a.real_name','a.nickname','a.worker_number','e.account_holder','e.bank_name','e.opening_branch','e.account as bank_account',
                Db::raw("IFNULL(c.maxid,0) as maxid"),Db::raw("IFNULL(d.left_amount,0) as left_amount")
            ])
            ->leftJoin([Db::name('master_worker_account_log')
                ->where('create_time','between',[strtotime($firstDay),strtotime($lastDay)])
                ->field('worker_id as master_worker_id, max(id) as maxid')
                ->group('worker_id')
                ->buildSql() => 'c'], 'a.id = c.master_worker_id')
            ->leftJoin('master_worker_account_log d', 'c.maxid = d.id')
            ->leftJoin('bank_account e', 'a.id = e.worker_id')
            ->order('a.id desc')
            ->limit($this->limitOffset, $this->limitLength)
            //->where('d.left_amount', '>', 0)
            ->select()->toArray();*/
    }


    /**
     * @notes 获取数量
     * @return int
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function count(): int
    {
        $deadline_time = strtotime($this->params['deadline_time'])+86400;
        $settlement_type = $this->params['settlement_type'];
        return Db::name('master_worker')->alias('a')->field([
                'a.id','a.real_name','a.nickname','a.worker_number','a.user_money','e.account_holder','e.bank_name','e.opening_branch','e.account as bank_account',
                Db::raw("sum(amount_change) as left_amount")
            ])
            ->leftJoin('master_settlement_details b', 'a.id = b.master_worker_id AND b.status = 1')
            ->leftJoin('bank_account e', 'a.id = e.worker_id')
            ->where('a.audit_state', '=', 1)
            ->where('a.user_money', '>', 0)
            ->where('b.status', '=', 1)
            //->where('a.settlement_type', 'in', $settlement_type)
            ->where('a.settlement_type', '=', $settlement_type)
            ->where('b.change_time', '<', $deadline_time)
            ->group('a.id')
            ->count();

        /*$firstDay = date('Y-m-01 00:00:00', strtotime('first day of last month'));
        $lastDay = date('Y-m-t 23:59:59', strtotime('last day of last month'));
        return Db::name('master_worker')->alias('a')->field([
            'a.id','a.real_name','a.nickname','a.worker_number',Db::raw("IFNULL(c.maxid,0) as maxid"),Db::raw("IFNULL(d.left_amount,0) as left_amount")
        ])
            ->leftJoin([Db::name('master_worker_account_log')
                ->where('create_time','between',[strtotime($firstDay),strtotime($lastDay)])
                ->field('worker_id as master_worker_id, max(id) as maxid')
                ->group('worker_id')
                ->buildSql() => 'c'], 'a.id = c.master_worker_id')
            ->leftJoin('master_worker_account_log d', 'c.maxid = d.id')
            //->where('d.left_amount', '>', 0)
            ->count();*/
    }

    public function setExcelComplexFields(): array
    {
        $zh_cn_fields = [
            '工程师ID','工程师编号', '工程师姓名','工程师银行卡号','工程师开户行','支行信息','工程师上月最后结算余额'
        ];
        $data_fields = ['id','worker_number','real_name','bank_account','bank_name','opening_branch','left_amount'];
        return [
            'zh_cn_fields' => $zh_cn_fields,
            'data_fields' => $data_fields
        ];
    }
}