php-jiesuan/app/command/ExecReport.php

<?php
declare (strict_types = 1);

namespace app\command;


use PHPUnit\Exception;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\facade\Db;
use think\facade\Cache;


class ExecReport extends Command
{
    public $redis=[];
    protected function configure()
    {
        ini_set("memory_limit","512M");
        // 指令配置

        $this->setName('ExecReport')
            ->setDescription('the hello command');
    }

    protected function execute(Input $input, Output $output)
    {
        $this->redis=Cache::store("redis");
        $iscgd =  $this->redis->get("exreport");
        if($iscgd==0){
            $this->redis->set("exreport",1);
        }else{
            return;
        }
        try {
            if ($this->redis->handler()->llen('execreport') > 0) {
                $data = $this->redis->handler()->rpop('execreport');
                $user_info = json_decode($data, true);
                $rel = $this->checkJob($user_info);
                if ($rel) {
                    $this->doData($user_info);
                }
            }
            $this->redis->set("exreport", 0);
            $output->writeln('queuecus');
        }catch (\Exception $e){
            $this->redis->set("exreport", 0);
            $output->writeln('queuecus');
        }
    }
    protected function checkJob($data){
        if($data['id']!=''){
            $info = Db::name("exec")->where(["id"=>$data['id']])->find();
            if($info['status']!=1){
                return false;
            }
        }else{
            return false;
        }
        return  true;
    }
    protected function doData($data){
        $diff = $this->monthDiff($data['start'],$data['end']);
        $zipfile=[];
        $dir = "/storage/report/".date("YmdHis")."/";
      foreach ($diff as $value){
        $start = $value." 00:00:00";
        $end = date("Y-m-t",strtotime($value))." 23:59:59";
          $sql =str_replace(["%s","%t"],[$start,$end],$data['exec_sql']);
          $list = Db::query($sql);
          if(empty($list)){
              continue;
          }
          $header = array_keys($list[0]);
          array_walk($list, function (&$v) {
              $v = array_values($v);
          });
          $zipfile []=$this->execfile($value.'月份'.$data['name'],$header,$list,$dir);

      }
        if(empty($zipfile)){
            $info=[];
            $info['status']=2;
            $info['remark']='文件生成失败';
            $info['updatetime']=date("Y-m-d H:i:s");
            Db::name("exec")->where(["id"=>$data['id']])->save($info);
            return ;
        }
        $zip=$this->addzipfile($zipfile,$data['name']);
        if(is_file(root_path() . 'public'.$zip)){
            $info=[];
            $info['status']=2;
            $info['down_url']=$zip;
            $info['expiretime']=date("Y-m-d H:i:s",time()+7*24*3600);
          //  $info['updatetime']=date("Y-m-d H:i:s");
            Db::name("exec")->where(["id"=>$data['id']])->save($info);
            return ;
        }else{
            $info=[];
            $info['status']=2;
            $info['remark']='文件生成失败';
            $info['updatetime']=date("Y-m-d H:i:s");
            Db::name("exec")->where(["id"=>$data['id']])->save($info);
            return ;
        }

    }

    protected function execfile($fileName,$headArr,$data,$root_dir){
        if(is_file(root_path() . 'public'.$root_dir.$fileName)){
            unlink(root_path() . 'public'.$root_dir.$fileName);
        }
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties();
        $keyA = 0; // 设置表头
        foreach ($headArr as $v) {
            $colum = \PHPExcel_Cell::stringFromColumnIndex($keyA);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $keyA += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入
            $span = 0;
            foreach ($rows as $keyName => $value) { // 列写入
                //判断数据是否有数组，如果有数组，转换成字符串

                if(is_array($value)){
                    $value = implode("、", $value);
                }
                $objActSheet->setCellValue(\PHPExcel_Cell::stringFromColumnIndex($span) . $column, $value);
                $span++;
            }
            $column++;
        }
        $file = $fileName. ".xls";
        $dir =root_path() . 'public'.$root_dir;
        if(!is_dir($dir)){
            mkdir($dir,0777,true);
        }
        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($dir . $file); // 文件通过浏览器下载
        return $dir.$file;
    }

    public function monthDiff($start,$end){
        $start = date("Y-m",strtotime($start))."-01";
        $end = date("Y-m",strtotime($end))."-01";
        $data=[];
        while (strtotime($start)<=strtotime($end)){
            $data[]=$start;
            $start = date('Y-m-d', strtotime("$start +1 month"));
        }
        return $data;
    }

    public function addzipfile($filelist,$zipname){
        $datetime = date("YmdHis");
        $zip_dir="/storage/zip/".$datetime."/".$zipname.".zip";
        $file_dir =root_path()."public/storage/zip/".$datetime."/".$zipname.".zip";
        $saveDir = root_path()."public/storage/zip/".$datetime."/";
        if(!is_dir( $saveDir)){
            mkdir($saveDir,0777,true);
        }
        # 5.1 文件打包，提示：使用本类，linux需开启zlib，windows需取消php_zip.dll前的注释
        $zip = new \ZipArchive ();
        # 5.2 文件不存在则生成一个新的文件 用CREATE打开文件会追加内容至zip
        if ($zip->open($file_dir, \ZipArchive::OVERWRITE) !== true && $zip->open($file_dir, \ZipArchive::CREATE) !==
            true) echo '无法打开文件或者文件创建失败';
        # 5.3 批量写入压缩包
        foreach ($filelist as $value){
            $fileName=basename($value);
            $filedir=pathinfo($value);
            $zip->addEmptyDir($fileName);
            // @$zip->addFile($v['file_path'], 'resume'.DIRECTORY_SEPARATOR.basename($headername));
            @$zip->addFile($value,$fileName.DIRECTORY_SEPARATOR.basename($value));
            # 5.4 关闭压缩包写入
            $dir =  $filedir['dirname'];
            //@unlink($value);
            // @$this->deldir($filedir['dirname']);
        }
        $zip->close();
        @$this->deldir($dir);
     return $zip_dir;
    }
  public  function deldir($path){
        //如果是目录则继续
        if(is_dir($path)){
            //扫描一个文件夹内的所有文件夹和文件并返回数组
            $p = scandir($path);
            //如果 $p 中有两个以上的元素则说明当前 $path 不为空
            if(count($p)>2){
                foreach($p as $val){
                    //排除目录中的.和..
                    if($val !="." && $val !=".."){
                        //如果是目录则递归子目录，继续操作
                        if(is_dir($path.$val)){
                            //子目录中操作删除文件夹和文件
                            deldir($path.$val.'/');
                        }else{
                            //如果是文件直接删除
                            unlink($path.'/'.$val);
                        }
                    }
                }
            }
        }
        //删除目录
        return rmdir($path);
    }
}