guochanhua/zskk_new_manage/application/admin/controller/questionnaire/Water.php

<?php

namespace app\admin\controller\questionnaire;

use app\common\controller\Backend;
use think\Db;
use function fast\e;

/**
 * 调查问卷流水管理
 *
 * @icon fa fa-circle-o
 */
class Water extends Backend
{
    
    /**
     * Water模型对象
     * @var \app\admin\model\questionnaire\Water
     */
    protected $model = null;
    protected $noNeedLogin = ['makeQWater'];
    protected $noNeedRight = ['makeQWater'];

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\questionnaire\Water;

    }
    
    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $list = collection($list)->toArray();
            $questionnaire = Db::table('questionnaire')->column('id,name');
            foreach ($list as $k=>$v)
            {
                $list[$k]['questionnaire'] = $questionnaire[$v['questionnaire_id']] ?? '';
            }
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    public function makeQWater()
    {
        $now = new \DateTime(); // 获取当前时间
        $now->modify('first day of this month'); // 设置为当前月的第一天
        $lastMonth = clone $now; // 克隆当前时间对象
        $lastMonth->sub(new \DateInterval('P1M')); // 减去一个月
        $first =  $lastMonth->format('Y-m-d 00:00:00'); // 输出上个月的第一天
        $lastMonth->modify('last day of this month'); // 设置为上个月最后一天
        $last = $lastMonth->format('Y-m-d 23:59:59'); // 输出上个月最后一天
        $questionnaire = Db::table('questionnaire')->column('id,name');
        $water = Db::table('questionnaire_water')
            ->alias('w')
            ->join(['user_wechat'=>'u'],'u.wx_openid=w.openid')
            ->where("w.createdAt between '$first' and '$last'")
            ->field('w.questionnaire_id,w.createdAt,w.money,u.wx_nickname')
            ->order('createdAt')
            ->select();
        if(!empty($water))
        {
            foreach ($water as $k=>$v)
            {
                $water[$k]['questionnaire_name'] = $questionnaire[$v['questionnaire_id']] ?? '';
            }
            $url = $this->makeExcel($water);
            $this->sendInstitutionNotice($url,$first);
        }
        return 'success';
    }

    public function makeExcel($data)
    {
        import('phpexcel.PHPExcel', EXTEND_PATH);
        $obj = new \PHPExcel();

// 设置 Excel 文件的信息描述信息
        $obj->getProperties()->setCreator(''); // 设置创建者
        $obj->getProperties()->setLastModifiedBy(''); // 设置修改者
        $obj->getProperties()->setTitle(''); // 设置标题
        $obj->getProperties()->setSubject(''); // 设置主题
        $obj->getProperties()->setDescription(''); // 设置描述
        $obj->getProperties()->setKeywords(''); // 设置关键词
        $obj->getProperties()->setCategory(''); // 设置类型

// 设置当前 sheet
        $obj->setActiveSheetIndex(0);

// 设置当前 sheet 的名称
        $obj->getActiveSheet()->setTitle('问卷流水');

// 列标
        $list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'];
// 填充第一行数据
        $obj->getActiveSheet()
            ->setCellValue($list[0] . '1', '问卷')
            ->setCellValue($list[1] . '1', '价格（分）')
            ->setCellValue($list[2] . '1', '支付时间')
            ->setCellValue($list[3] . '1', '微信昵称');

// 填充第 n 行数据
        $length = count($data);
        for ($i = 0; $i < $length; $i++) {
            $obj->getActiveSheet()->setCellValue($list[0] . ($i + 2), $data[$i]['questionnaire_name'], \PHPExcel_Cell_DataType::TYPE_STRING); // 设置为文本格式
            $obj->getActiveSheet()->setCellValue($list[1] . ($i + 2), $data[$i]['money']);
            $obj->getActiveSheet()->setCellValue($list[2] . ($i + 2), $data[$i]['createdAt']);
            $obj->getActiveSheet()->setCellValue($list[3] . ($i + 2), $data[$i]['wx_nickname']);
        }

// 设置加粗和左对齐
        foreach ($list as $col) {
            // 设置第一行加粗
            $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
            // 设置第 1-n 行，左对齐
            for ($i = 1; $i <= $length + 1; $i++) {
                $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            }
        }

// 设置列宽
        $obj->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $obj->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $obj->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $obj->getActiveSheet()->getColumnDimension('D')->setWidth(40);

// 检查目标目录
        $targetDir = "excel"; // 替换为你的目标目录
        if (!is_dir($targetDir)) {
            mkdir($targetDir, 0777, true); // 如果目录不存在，则创建目录
        }
        if (!is_writable($targetDir)) {
            chmod($targetDir, 0777); // 确保目录可写
        }

// 生成文件名
        $name = time() . '.xlsx';
        $filePath = $targetDir . '/' . $name;

// 创建 Excel2007 写入器
        $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');

// 保存到指定路径
        $objWriter->save($filePath);
        return $filePath;
    }

    public function sendInstitutionNotice($sendUrl,$date)
    {
        $base_url = "https://oapi.dingtalk.com/robot/send?access_token=f40711574780111ea157cdcbc6f81e267a7a6869ab257a1ff67beebcebc305a9";
        $time = time()* 1000;
        $secret = 'SEC39d1d634cda3a428884a98395f40ffe8e7ef43b9546eee23be552010dab78236';
        $m = $time."\n".$secret;
        $s = hash_hmac('sha256', $m, $secret, true);
        $a = base64_encode($s);
        $b = urlencode($a);
        $url = $base_url . "&timestamp=$time&sign=$b";
        $month = date('Ym',strtotime($date));
        $data = [
            'msgtype' => 'link',
            "link"=>[
                "text"=> $month."月的问卷流水",
                "title"=> $month."月的问卷流水",
                "messageUrl"=> "https://rismanage3.pacsonline.cn/".$sendUrl
            ],
//            'at'=>['atMobiles'=>['15210490031']]
        ];
        $data_string = json_encode($data);
        $res = $this->request_by_curl($url, $data_string);
        return $res;
    }

    function request_by_curl($remote_server, $post_string,$header = array ('Content-Type: application/json;charset=utf-8')) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $remote_server);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
        curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post_string);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        // 线下环境不用开启curl证书验证, 未调通情况可尝试添加该代码
        // curl_setopt ($ch, CURLOPT_SSL_VERIFYHOST, 0);
        // curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, 0);
        $data = curl_exec($ch);
        curl_close($ch);
        return $data;
    }
}