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\equity\UserEquityLogic;
use app\adminapi\logic\financial\MasterSettlementDetailsLogic;
use app\common\enum\FileEnum;
use app\common\model\equity\UserEquity;
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\model\user\User;
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',
        '3' =>  'UserEquityDownload',
    ];
    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($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(b.work_total) work_total"),
                Db::raw("SUM(b.worker_price) 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());
        }
    }

    // 权益卡编码导出
    public function UserEquityDownload($infoExport){
        try{
            $filename = $infoExport['name'];
            $params = $infoExport['params']?:[];
            $where = [];
            if($params){
                if (isset($params['select_ids']) && !empty($params['select_ids'])) {
                    $where[] = ['id','in' ,$params['select_ids']];
                }
                if (isset($params['equity_id']) && !empty($params['equity_id'])) {
                    $where[] = ['equity_id','=' ,$params['equity_id']];
                }
                if (isset($params['mobile']) && !empty($params['mobile'])) {
                    $userId = User::where('mobile',$params['mobile'])->value('id')??-1;
                    $where[] = ['user_id','=' ,$userId];
                }
                if (isset($params['is_binding']) && !empty($params['is_binding'])) {
                    if((int)$params['is_binding'] === 1){
                        $where[] = ['user_id','>',0];
                    }else{
                        $where[] = ['user_id','=',0];
                    }
                }
            }
            $lists =  UserEquity::with(['equityConfig', 'goods','user'])
                ->where($where)
                ->field(['id', 'user_id', 'equity_id', 'goods_id', 'number', "IF(end_time=0,'',FROM_UNIXTIME(end_time, '%Y-%m-%d %H:%i:%s')) AS end_time",'code','IF(user_id=0,2,1) as is_binding'])
                ->select()
                ->toArray();
            $filename = ($filename?:'').('-'.date('YmdHis').'-'.$infoExport->id);
            $this->sheet->generateExcelFile([
                '用户','编码', '权益卡名称','商品','预售价','权益总次数','剩余次数','权益截止时间'
            ], $lists,$filename, ['user.nickname','code','equityConfig.equity_name','goods.goods_name','equityConfig.price','equityConfig.number','number','end_time']);
            $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());
        }
    }

}