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\common\enum\FileEnum;
use app\common\model\export\Export;
use app\common\model\file\File;
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($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 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['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',
                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();
            $filename = ($filename?:'').('-'.date('YmdHis').'-'.$infoExport->id);
            $this->sheet->generateExcelFile([
                '工程师ID', '工程师编号', '工程师姓名', '工程师手机', '代招人姓名', '工程师成功单','工程师失败单','工程师成交金额','工程师提成金额'
            ], $lists,$filename, ['id','worker_number','real_name','mobile','recruiting_behalf','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());
        }
    }



}