guoyao-jiankang/application/admin/controller/EnergyElectricityRecord2.php

<?php
namespace app\admin\controller;

use app\common\model\User;
use think\App;
use think\Db;

class EnergyElectricityRecord2 extends Auth
{
    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->model = new \app\common\model\EnergyElectricityRecord();
    }
    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','sort','trim');      //排序列
            $sort = input('sord','asc','trim');        //排序方式
            $order = $sortRow.' '.$sort.' ,id desc';

            $sn = input('sn','','trim');
            if($sn){
                $map[] = ['sn','=',$sn];
            }
            $ids = Db::name('energy_electricity')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('parent_id',0)
                ->column('number');
            if (!empty($ids)) {
                $map[] = ['sn','in',$ids];
            }
            $map[] = ['org_id','=',$this->orgId];
            $map[] = ['del','=',0];
            $map= empty($map) ? true: $map;
            //数据查询

            $lists = Db::name('energy_electricity_record')
                ->where($map)->limit($start,$length)
                ->order($order)->select();
            foreach($lists as $k=>$v){
                $lists[$k]['user_id'] = Db::name('user')->where('id',$v['user_id'])->value('real_name');
            }

            //数据返回
            $totalCount = db('energy_electricity_record')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $this->assign('meta_title','电表抄表记录');
            return $this->fetch();
        }
    }

    /**
     * 新增/编辑
     */
    public function add($id=0){
        $model = $this->model;
        $userId = is_login();
        if(request()->isPost()){
            $res = $model->updates($userId,0);
            if($res){
                $this->success('操作成功',url('index'));
            }else{
                $this->error($model->getError());
            }
        }else{
            if($id){
                $info = db('energy_electricity_record')->where('id',$id)->find();
                $this->assign('info',$info);
            }
            return $this->fetch();
        }
    }

    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        $res = Db::name('energy_electricity_record')->where('id',$id)->update(['del'=>1]);
        if($res){
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }
    }

    public function import(){
        return $this->fetch();
    }
    /**
     * 下载点模板
     */
    public function downloadtem(){
        set_time_limit(0);
        ini_set("memory_limit","512M");
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        $types = model('Address')->getTypes();
        ksort($types);
        $fileName = '电表抄表记录模板.xlsx';
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G');
        foreach($arr as $k=>$v){
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(true);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);

        }
        $sheet->setCellValueByColumnAndRow(0,1,'日期');
        $sheet->setCellValueByColumnAndRow(1,1,'表号');
        $sheet->setCellValueByColumnAndRow(2,1,'倍率');
        $sheet->setCellValueByColumnAndRow(3,1,'抄表读数');
        $sheet->setCellValueByColumnAndRow(4,1,'最低温度 (℃))');
        $sheet->setCellValueByColumnAndRow(5,1,'最高温度 (℃))');
        $sheet->setCellValueByColumnAndRow(6,1,'床位数量');

        $excel->getActiveSheet()->setCellValue('A2', '2024/09/23');
        $excel->getActiveSheet()->setCellValue('B2', 'D1101');
        $excel->getActiveSheet()->setCellValue('C2', '10');
        $excel->getActiveSheet()->setCellValue('D2', '100');
        $excel->getActiveSheet()->setCellValue('E2', '10');
        $excel->getActiveSheet()->setCellValue('F2', '22');
        $excel->getActiveSheet()->setCellValue('G2', '120');

        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output'); //文件通过浏览器下载

    }


    /**
     * 导入
     */
    public function importexcel(){
        set_time_limit(0);
        ini_set("memory_limit", -1);
        ob_flush();//清空缓存
        flush();//刷新缓存
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        $orgId = $this->orgId;
        if(request()->file()) {
            $file = request()->file('file');
            //获取文件后缀
            $e = explode('.',$_FILES['file']['name']);
            $ext = $e[count($e)-1];
            if($ext == 'xls'){
                \PHPExcel_IOFactory::createReader( 'Excel5');
            }else{
                \PHPExcel_IOFactory::createReader('Excel2007');
            }
            $newArr=['xls','xlsx'];
            if(!in_array($ext,$newArr)){
                exit('文件格式不正确');
            }
            // 移动到框架应用根目录/uploads/ 目录下
            $info = $file->validate([ 'size'=>config('app.max_upload_file_size') ])
                ->move(env('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads'. DIRECTORY_SEPARATOR . 'files');
            if(!$info){
                exit('文件上传失败');
            }
            $img = './uploads/files/' . $info->getSaveName();
            $filePath = str_replace('\\', '/', $img);
            $newPath = \PHPExcel_IOFactory::load($filePath);

            $excelArray = $newPath->getsheet(0)->toArray();   //转换为数组格式

            array_shift($excelArray);  //删除第一个数组(标题);
            if(empty($excelArray)){
                exit('文件内容为空');
            }
            $ids = Db::name('energy_electricity')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('parent_id',0)
                ->column('number');
            $flag = 0;
            foreach ($excelArray as $k => $v) {
                if (!$v[0] || !$v[1] || !$v[2] || !$v[3]) {
                    $flag = 1;
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第" . ($k + 1) . "行，有数据为空，请修改后重新导入</font><br />";
                    continue;
                }
                if (!in_array($v[1], $ids)) {
                    $flag = 1;
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第" . ($k + 1) . "行，表号不存在或非总表表号，请修改后重新导入</font><br />";
                    continue;
                }
            }
            if ($flag > 0) {
                exit();
            }
            $data = [];
            foreach ($excelArray as $k => $v) {
                $degree = trim($v[3]);
                $change = Db::name('energy_change_log')
                    ->where('org_id',$this->orgId)
                    ->where('sn',$v[1])
                    ->order('id desc')
                    ->value('degree');
                if ($change){
                    $degree += $change;
                }
                $data[] = [
                    'org_id'=>$this->orgId,
                    'read_time'=>date('Y-m-d',strtotime($v[0])),
                    'sn'=>$v[1],
                    'rate'=>$v[2],
                    'degree'=>$degree,
                    'min_temperature'=>$v[4],
                    'max_temperature'=>$v[5],
                    'user_id'=>is_login(),
                    'read_yyyy'=>date('Y',strtotime($v[0])),
                    'read_yyyymm'=>date('Ym',strtotime($v[0])),
                    'read_yyyymmdd'=>date('Ymd',strtotime($v[0])),
                    'create_time'=>getTime(),
                ];
            }
            $ret=Db::name('energy_electricity_record')->insertAll($data);

            if(!$ret){
                echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>批量导入失败</font><br />";
            }else{
                echo "<font color=\"green\" style='margin-left:20px;font-size: 17px'>批量导入成功</font><br />";
            }

        }else{
            exit('请上传文件');
        }

    }

    public function multiDel() {
        $ids = input('ids', [], 'trim');
        if (empty($ids)) {
            $this->error('未选择记录');
        }
        $res = Db::name('energy_electricity_record')->whereIn('id', $ids)->update(['del'=>1]);
        if (!$res) $this->error('批量删除失败');
        $this->success('批量删除传成功');
    }
}