<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\console\command\make\Controller;
use think\Db;
use think\Request;

class Tyexcel extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    protected $rid;
    protected $startime;
    protected $startime2;
    protected $endtime;
    protected $resvinfo;

    //构造函数
    public function _initialize()
    {
        parent::_initialize();
        $this->rid = $this->request->param('rid', 23);
        $this->startime = $this->request->param('startime', date("Y-m-d", strtotime("-3 month")));
        $this->startime2 = $this->request->param('startime', date("Y-m-d", strtotime("-3 day")));
        $this->endtime = $this->request->param('endtime', date('Y-m-d', time()));

        //水库信息
        $this->resvinfo = Db::name('reservoir_list')
            ->where(['id' => $this->rid])
            ->find();
    }

    /**
     * 降雨量列表
     *
     */
    public function resvRainList()
    {
        if ($this->request->isPost()) {
            $rid = $this->request->post('rid', 23);
            $resvinfo = Db::name('reservoir_list')
                ->where(['id' => $rid])
                ->find();
            //时间段处理
            $stime = strtotime($this->startime2);
            $etime = strtotime($this->endtime) + 24 * 3600 - 1;

            $where2["createtime"] = ["between", [$stime, $etime]];
            $result = Db::name('reservoir_rain_rainfall')
                ->where(['reservoir_id' => $rid])
                ->where($where2)
                ->field('id,number,total_rainfall,createtime')
                ->order('id', 'desc')
                ->limit(100)
                ->select();
            foreach ($result as $k => $v) {
                $result[$k]['name'] = $resvinfo['name'];
                $result[$k]['createtime'] = date('Y-m-d H:i:s', $v['createtime']);
            }

            $this->success('获取成功', $result);
        }
    }

    /**
     * 导出表
     * 降雨量
     */
    public function resvRainExcel()
    {
        $rid = $this->rid;
        $resv = $this->resvinfo;
        //时间段处理

        $stime = strtotime($this->startime);
        $etime = strtotime($this->endtime) + 24 * 3600 - 1;


        $where2["createtime"] = ["between", [$stime, $etime]];
        $result = Db::name('reservoir_rain_rainfall')
            ->where(['reservoir_id' => $rid])
            ->where($where2)
            //->where('createtime between ' . $stime . ' and ' . $etime)
            ->field('number,total_rainfall,createtime')
            ->order('id', 'desc')
            ->group("createtime")
            ->select();
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E");
        $arrHeader = array('序号', '水库', '设备编号', '日降雨量', "采集时间");

        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($result as $k => $v) {
            $j = $k + 1;
            $k += 2;
            $objActSheet->setCellValue('A' . $k, $j);
            $objActSheet->setCellValue('B' . $k, $resv['name']);
            $objActSheet->setCellValue('C' . $k, $v['number']);
            $objActSheet->setCellValue('D' . $k, $v['total_rainfall']);
            $objActSheet->setCellValue('E' . $k, date('Y-m-d H:i:s', $v['createtime']));
            $objActSheet->getColumnDimension('B')->setWidth(20);
            $objActSheet->getColumnDimension('C')->setWidth(20);
            $objActSheet->getColumnDimension('D')->setWidth(20);
        }
        $outfile = $resv["name"] . '降雨量_' . date('YmdHis', time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /**
     * 水位列表
     *
     */
    public function resvWaterList()
    {
        if ($this->request->isPost()) {
            $rid = $this->request->post('rid', 23);
            $resvinfo = Db::name('reservoir_list')
                ->where(['id' => $rid])
                ->find();
            //时间段处理
            $stime = strtotime($this->startime2);
            $etime = strtotime($this->endtime) + 24 * 3600 - 1;

            $where2["createtime"] = ["between", [$stime, $etime]];
            $result = Db::name('reservoir_rain_water_level')
                ->where(['reservoir_id' => $rid])
                ->where($where2)
                ->field('id,number,water_level,createtime')
                ->order('id', 'desc')
                ->select();
            foreach ($result as $k => $v) {
                $result[$k]['name'] = $resvinfo['name'];
                $result[$k]['createtime'] = date('Y-m-d H:i:s', $v['createtime']);
            }

            $this->success('获取成功', $result);
        }
    }

    /**
     * 水库水位管理
     *导出表
     */
    public function resvWaterLevel()
    {
        ini_set('memory_limit', '256M');
        $rid = $this->rid;
        $resv = $this->resvinfo;
        //时间段处理

        $stime = strtotime($this->startime);
        $etime = strtotime($this->endtime) + 24 * 3600 - 1;

        $where2["createtime"] = ["between", [$stime, $etime]];

        $rlevel = Db::name('reservoir_rain_water_level')
            ->where(['reservoir_id' => $rid])
            ->where($where2)
            ->field('id,number,water_level,createtime')
            ->order('id', 'desc')
            ->group("createtime")
            ->select();

        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E");
        $arrHeader = array('序号', '水库', '设备编号', '日水位', "采集时间");

        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($rlevel as $k => $v) {
            $j = $k + 1;
            $k += 2;

            $objActSheet->setCellValue('A' . $k, $j);
            $objActSheet->setCellValue('B' . $k, $resv['name']);
            $objActSheet->setCellValue('C' . $k, $v['number']);
            $objActSheet->setCellValue('D' . $k, $v['water_level']);
            $objActSheet->setCellValue('E' . $k, date('Y-m-d H:i:s', $v['createtime']));
            $objActSheet->getColumnDimension('B')->setWidth(20);
            $objActSheet->getColumnDimension('C')->setWidth(20);
            $objActSheet->getColumnDimension('D')->setWidth(20);

        }
        $outfile = $resv["name"] . '水位统计_' . date('YmdHis', time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /**
     *渗压列表
     *
     */
    public function isotList()
    {
        if ($this->request->isPost()) {
            $rid = $this->request->post('rid', 23);
            $resvinfo = Db::name('reservoir_list')
                ->where(['id' => $rid])
                ->find();
            //时间段处理
            $stime = strtotime($this->startime2);
            $etime = strtotime($this->endtime) + 24 * 3600 - 1;

            $where2["createtime"] = ["between", [$stime, $etime]];
            $result = Db::name('reservoir_dam_isotonic')
                ->where(['reservoir_id' => $rid])
                ->where($where2)
                ->field('id,equipment_id,value,createtime')
                ->order('id', 'desc')
                ->select();
            foreach ($result as $k => $v) {
                $result[$k]['name'] = $resvinfo['name'];
                $result[$k]['createtime'] = date('Y-m-d H:i:s', $v['createtime']);
            }

            $this->success('获取成功', $result);
        }
    }

    /**
     *渗压记录
     * 导出表
     *
     */
    public function isotListExcel()
    {
        $rid = $this->rid;
        $resv = $this->resvinfo;
        //时间段处理
        $stime = strtotime($this->startime);
        $etime = strtotime($this->endtime) + 24 * 3600 - 1;

        $where2["createtime"] = ["between", [$stime, $etime]];
        $result = Db::name('reservoir_dam_isotonic')
            ->where(['reservoir_id' => $rid])
            ->where($where2)
            ->field('id,equipment_id,value,createtime')
            ->order('id', 'desc')
            ->group("createtime")
            ->select();
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E");
        $arrHeader = array('序号', '水库', '设备编号', '渗压值', "采集时间");

        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($result as $k => $v) {
            $j = $k + 1;
            $k += 2;

            $objActSheet->setCellValue('A' . $k, $j);
            $objActSheet->setCellValue('B' . $k, $resv['name']);
            $objActSheet->setCellValue('C' . $k, $v['equipment_id']);
            $objActSheet->setCellValue('D' . $k, $v['value']);
            $objActSheet->setCellValue('E' . $k, date('Y-m-d H:i:s', $v['createtime']));
            $objActSheet->getColumnDimension('B')->setWidth(20);
            $objActSheet->getColumnDimension('C')->setWidth(20);
            $objActSheet->getColumnDimension('D')->setWidth(20);
        }
        $outfile = $resv["name"] . '渗压记录_' . date('YmdHis', time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /***
     *地表位移记录列表
     */
    public function damDisplace()
    {
        if ($this->request->isPost()) {
            $rid = $this->request->post('rid', 23);
            $resvinfo = Db::name('reservoir_list')
                ->where(['id' => $rid])
                ->find();
            //时间段处理
            $stime = strtotime($this->startime2);
            $etime = strtotime($this->endtime) + 24 * 3600 - 1;

            $where2["createtime"] = ["between", [$stime, $etime]];
            $result = Db::name('reservoir_dam_displacement')
                ->where(['reservoir_id' => $rid])
                ->where($where2)
                ->field('id,number,vertical,horizontal,horizontalY,createtime')
                ->order('id', 'desc')
                ->select();
            foreach ($result as $k => $v) {
                $result[$k]['name'] = $resvinfo['name'];
                $result[$k]['createtime'] = date('Y-m-d H:i:s', $v['createtime']);
            }

            $this->success('获取成功', $result);
        }
    }

    /***
     *地表位移记录
     * 导出表
     */
    public function damDisplaceExcel()
    {
        $rid = $this->rid;
        $resv = $this->resvinfo;
        //时间段处理
        $stime = strtotime($this->startime);
        $etime = strtotime($this->endtime) + 24 * 3600 - 1;

        $where2["createtime"] = ["between", [$stime, $etime]];
        $result = Db::name('reservoir_dam_displacement')
            ->where(['reservoir_id' => $rid])
            ->where($where2)
            ->field('id,number,vertical,horizontal,horizontalY,createtime')
            ->order('id', 'desc')
            ->group("createtime")
            ->select();
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E,F,G");
        $arrHeader = array('序号', '水库', '设备编号', 'Z垂直位移', 'X水平位移', 'Y水平位移', "采集时间");

        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($result as $k => $v) {
            $j = $k + 1;
            $k += 2;

            $objActSheet->setCellValue('A' . $k, $j);
            $objActSheet->setCellValue('B' . $k, $resv['name']);
            $objActSheet->setCellValue('C' . $k, $v['number']);
            $objActSheet->setCellValue('D' . $k, $v['vertical']);
            $objActSheet->setCellValue('E' . $k, $v['horizontal']);
            $objActSheet->setCellValue('F' . $k, $v['horizontalY']);
            $objActSheet->setCellValue('G' . $k, date('Y-m-d H:i:s', $v['createtime']));
            $objActSheet->getColumnDimension('B')->setWidth(20);
            $objActSheet->getColumnDimension('C')->setWidth(20);
            $objActSheet->getColumnDimension('D')->setWidth(20);
        }
        $outfile = $resv["name"] . '位移记录_' . date('YmdHis', time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

}