ball/app/common/service/ExcelExportService.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\common\service;


use app\adminapi\logic\financial\MasterSettlementDetailsLogic;
use app\common\enum\FileEnum;
use app\common\model\export\Export;
use app\common\model\file\File;
use app\common\model\financial\FinancialPaymentRecords;
use app\common\model\goods_category\GoodsCategory;
use app\common\service\storage\Driver as StorageDriver;
use excel\ExcelWriter;
use Exception;
use think\facade\Db;
use think\facade\Log;


class ExcelExportService
{

    private $sheet;
    public $download_type = [
        '1' =>  'EngineerBillDownload',
        '2' =>  'MasterWorkerServiceOrderDownload',
    ];
    public function __construct()
    {
        $this->sheet = new ExcelWriter();
    }
    public function download($id)
    {
        try{
            $infoExport = Export::findOrEmpty($id);
            if(!$infoExport->isEmpty()){
                $download_fun = $infoExport['download_fun'];
                if(!method_exists($this,$download_fun)){
                    throw new Exception('下载不存在-1001');
                }
                $this->$download_fun($infoExport);
            }
        }catch (\Exception $e){
            Log::info("download-error:{$id}:".$e->getMessage());
            throw new Exception($e->getMessage());
        }
    }

    /**
     * @notes 工程师 -上月最后结算余额 导出
     */
    public function EngineerBillDownload_Bak($infoExport)
    {
        try{
            $filename = $infoExport['name'];
            $params = $infoExport['params']?:[];
            if($params){ }
            $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'));
            $lists =  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')
                //->where('d.left_amount', '>', 0)
                ->select()->toArray();
            $filename = ($filename?:'').('-'.date('YmdHis').'-'.$infoExport->id);
            $this->sheet->generateExcelFile([
                '工程师ID', '工程师编号', '工程师姓名', '工程师银行卡号', '工程师开户行及支行信息','上月最后结算余额'
            ], $lists,$filename, ['id','worker_number','real_name','bank_account','bank_name','left_amount']);
            $infoExport->file_url = $this->sheet->fileUrl();
            $infoExport->generate_status = 1;
            $infoExport->updatetime = time();
            $infoExport->save();
            return $infoExport->file_url;
        }catch (\Exception $e){
            throw new Exception($e->getMessage());
        }
    }

    /**
     * @notes 工程师 -截止某日最后结算余额 导出
     */
    public function EngineerBillDownload($infoExport)
    {
        Db::startTrans();
        try{
            $filename = $infoExport['name'];
            $params = $infoExport['params']?:[];
            if($params){ }
            // 导出逻辑
            /*添加财务打款记录表: 生成批次编号、状态待上传 已取消  打款状态： 未打款、部分打款、全部打款
            生成数据表格：工程师信息、银行卡信息、批次编号信息、应发金额、导出时账号余额、导入时账号余额、扣款金额、扣款说明、实发金额、打款状态(已打、下次打款)、打款时间、备注、是否已导入
            (账号当前余额 !=0) < (应发金额 !=0)  = 下次打款
            更新表 la_master_settlement_details 批次编号、状态-待算
            */

            $batch_number = 'bn' . date('YmdHis') . rand(1000, 9999);

            // 添加财务打款记录表 -- 批次编号、状态待上传
            $params['infoExport_id'] = $infoExport->id;
            FinancialPaymentRecords::create([
                'export_conditions' => $params,
                'batch_number' => $batch_number,
                'upload_status' => 1,
                'payment_status' => 1,
            ]);
            //生成数据表格 & 更新表 -- 批次编号、状态-待算
            $lists = MasterSettlementDetailsLogic::upBatchStatus($params,$batch_number);
            $filename = ($filename?($filename.'-批号'.$batch_number):'').('-'.date('YmdHis').'-'.$infoExport->id);
            $this->sheet->generateExcelFile([
                '工程师ID', '工程师编号', '工程师姓名', '工程师银行卡号', '所属银行', '工程师开户行及支行信息','导出时账号余额',
                '批次编号',
                '应发金额', '扣款金额', '扣款说明', '实发金额', '打款时间','备注'
            ], $lists,$filename, [
                'master_worker_id','worker_number','engineer_name','bank_account','bank_name','opening_branch','original_balance',
                'batch_number',
                'total_settlement_amount','','','','',''
            ]);
            $infoExport->name = $filename;
            $infoExport->file_url = $this->sheet->fileUrl();
            $infoExport->generate_status = 1;
            $infoExport->updatetime = time();
            $infoExport->save();

            Db::commit();
            return $infoExport->file_url;
        } catch (\Exception $e) {
            Db::rollback();
            throw new Exception($e->getMessage());
        }
    }

    /**
     * @notes 工程师 -工单统计 导出
     */
    public function MasterWorkerServiceOrderDownload($infoExport)
    {
        try{
            $filename = $infoExport['name'];
            $params = $infoExport['params']?:[];
            $where = [];
            $sqlJoin = '';
            if($params){
                if (isset($params['real_name']) && !empty($params['real_name'])) {
                    $where[] = ['a.real_name','like' ,"%".$params['real_name']."%"];
                }
                if (isset($params['worker_number']) && !empty($params['worker_number'])) {
                    $where[] = ['a.worker_number','like' ,"%".$params['worker_number']."%"];
                }
                if (isset($params['recruiting_behalf']) && !empty($params['recruiting_behalf'])) {
                    $where[] = ['a.recruiting_behalf','like' ,"%".$params['recruiting_behalf']."%"];
                }
                if (isset($params['is_recruiting_behalf']) && !empty($params['is_recruiting_behalf'])) {
                    if ($params['is_recruiting_behalf'] == 1)
                        $where[] = ['a.recruiting_behalf','<>' ,""];
                    else
                        $where[] = ['a.recruiting_behalf','=' ,""];
                }
                if (isset($params['mobile']) && !empty($params['mobile'])) {
                    $where[] = ['a.mobile','=' ,$params['mobile']];
                }
                if (isset($params['cooperation']) && !empty($params['cooperation'])) {
                    $where[] = ['a.cooperation','=' ,$params['cooperation']];
                }
                if (!empty($params['start_time'])) {
                    $sqlJoin .= '    AND  b.update_time >= '.strtotime($params['start_time']);
                }
                if (!empty($params['end_time'])) {
                    $sqlJoin .= '    AND  b.update_time <= '.strtotime($params['end_time'])+86400;
                }
            }
            $lists =   Db::name('master_worker')->alias('a')->field([
                'a.id','a.real_name','a.nickname','a.worker_number','a.recruiting_behalf','a.mobile','a.cooperation','a.category_ids',
                Db::raw("COUNT(b.id) AS all_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN 1 ELSE 0 END) AS success_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 4 OR b.service_status = 5 THEN 1 ELSE 0 END) AS fail_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN b.work_total ELSE 0 END) work_total"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN b.worker_price ELSE 0 END) worker_price"),
            ])
                ->leftJoin('service_work b', 'a.id = b.master_worker_id'.$sqlJoin)
                ->where($where)
                ->group('a.id')
                ->select()->toArray();
            $categoryData = GoodsCategory::select()->toArray();
            foreach ($lists as &$item) {
                $item['category_name'] = $item['category_ids']?implode('、',array_column(get_parent_info($categoryData,explode(',',$item['category_ids'])),'name')):'';
            }
            $filename = ($filename?:'').('-'.date('YmdHis').'-'.$infoExport->id);
            $this->sheet->generateExcelFile([
                '服务类别', '工程师编号', '工程师姓名', '工程师手机', '代招人姓名', '工程师接单数', '工程师成功单','工程师失败单','工程师成交金额','工程师提成金额'
            ], $lists,$filename, ['category_name','worker_number','real_name','mobile','recruiting_behalf','all_count','success_count','fail_count','work_total','worker_price']);
            $infoExport->file_url = $this->sheet->fileUrl();
            $infoExport->generate_status = 1;
            $infoExport->updatetime = time();
            $infoExport->save();
            return $infoExport->file_url;
        }catch (\Exception $e){
            throw new Exception($e->getMessage());
        }
    }



}