jili2/application/index/controller/Index.php

<?php
namespace app\index\controller;

use app\common\util\AppMsg;
use app\common\util\ExcelUtil;
use app\hander\HelpHander;
use EasyWeChat\Factory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use think\App;
use think\Controller;
use think\Db;

class Index extends Controller
{
    public function __construct(App $app = null) {
        parent::__construct($app);
//        $this->model = new \app\common\model\Daily();
    }

    public function device()
    {
        set_time_limit(0);
        ini_set("memory_limit", "512M");
        $cates = [24,25,26,27];
        $uids = [3206,3207];
        exit();
        $lists = Db::name('device')->where('org_id',100)->where('cate_id','in',$cates)->select();
        foreach ($lists as $k=>$v){
            Db::name('device_user')->where('device_id',$v['id'])->delete();
            Db::name('device_user')->insert(['device_id'=>$v['id'],'user_id'=>3206]);
            Db::name('device_user')->insert(['device_id'=>$v['id'],'user_id'=>3207]);
        }
        halt($lists);
    }

    public function device2(){
        set_time_limit(0);
        ini_set("memory_limit","512M");

        exit();
        $file = "./1.xls";
        $orgId = 100;
        $cate1 = 22; //灭火器
        $cate2 = 23; // 消火栓
        $form1 = "20,21,22,23";
        $form2 = "24,25,26,27";

        $userId = 3139;

        $data = ExcelUtil::read($file,['title','sn','area','cate_id','content']);
        $i = 0;
        foreach ($data as $k=>$v){
            if($i >= 906){
                $data[$k]['org_id'] = $orgId;
                $data[$k]['enable'] = 1;
                if($v['cate_id'] == "灭火器"){
                    $data[$k]['cate_id'] = $cate1;
                    $data[$k]['device_form'] = $form1;
                }else{
                    $data[$k]['cate_id'] = $cate2;
                    $data[$k]['device_form'] = $form2;
                }

                $deivceId = Db::name('device')->insertGetId($data[$k]);
                if($deivceId){
                    Db::name('device_user')->insert([
                        "device_id" => $deivceId,
                        "user_id" => $userId,
                    ]);
                }
            }

            $i++;
        }
        halt($data);
    }

    public function device3(){
        set_time_limit(0);
        ini_set("memory_limit","2048M");

        $file = "./2.xls";
        $orgId = 100;
        $cate1 = 24; //月度
        $cate2 = 25; //季度
        $cate3 = 26; //年度
        $cate4 = 27; //半年度

        $form1 = "20,21,22,23";
        $form2 = "24,25,26,27";

        $userId = 3139;

        $data = ExcelUtil::read($file,['num','title','sn','spec','content','area','forms','cate']);
//        halt($data);

        $forms = [];

        $i = 0;
        foreach ($data as $k=>$v){
            $forms = array_merge($forms,explode("；",$v['forms']));
            if($i >= 228){
                $device = [
                    'org_id' => $orgId,
                    'title' => $v['title'],
                    'cate_id' => 0,
                    'enable' => 1,
                    'del' => 0,
                    'create_time' => getTime(),
                    'sn' => $v['sn'],
                    'spec' => $v['spec'],
                    'area' => $v['area'],
                    'content' => $v['content']
                ];
                $cateId = 0;
                if($v['cate'] == "1年/次"){
                    $cateId = $cate3;
                    //$data[$k]['device_form'] = $form1;
                }if($v['cate'] == "1季度/次"){
                    $cateId = $cate2;
                    //$data[$k]['device_form'] = $form1;
                }if($v['cate'] == "1月/次"){
                    $cateId = $cate1;
                    //$data[$k]['device_form'] = $form1;
                }else{
                    $cateId = $cate4;
//                    $data[$k]['device_form'] = $form2;
                }
                $device['cate_id'] = $cateId;
                $farr = $this->formatForms(explode("；",$v['forms']));
                $formIds = [];
                foreach ($farr as $kk=>$vv){
                    // 检查是否已存在
                    $forminfo = Db::name("device_form")->where('org_id',$orgId)->where('cate_id',$cateId)->where('title',$vv)->find();
                    if($forminfo){
                        $formIds[] = $forminfo['id'];
                    }else{
                        $fid = Db::name("device_form")->insertGetId([
                            'org_id' => $orgId,
                            'cate_id' => $cateId,
                            'title' => $vv,
                            'sort' => 50,
                            'del' => 0,
                            'enable' => 1,
                            'create_time' => getTime()
                        ]);
                        $formIds[] = $fid;
                    }
                }
                $device['device_form'] = implode(",",$formIds);

                $deivceId = Db::name('device')->insertGetId($device);
                if($deivceId){
                    Db::name('device_user')->insert([
                        "device_id" => $deivceId,
                        "user_id" => $userId,
                    ]);
                }
            }

            $i++;
        }


//        $forms = array_unique($forms);
//        $nforms = [];
//        foreach ($forms as $k=>$v){
//            if(!empty(trim($v))){
//                $nforms[] = $this->format($v);
//            }
//        }
//
//        $farr = [];
//        foreach ($nforms as $k=>$v){
//            $farr[] = [
//                "org_id" => $orgId,
//                "title" => $v,
//                "sort" => 50,
//                "del" => 0,
//                "enable" => 1,
//                "create_time" => getTime(),
//            ];
//        }
//
//        $ids = [20,21,22,23,24,25,26,27];

//        dump($nforms);
        halt($data);
    }

    public function formatForms($lists){
        $forms = array_unique($lists);
        $nforms = [];
        foreach ($forms as $k=>$v){
            if(!empty(trim($v))){
                $nforms[] = $this->format($v);
            }
        }
        return $nforms;
    }

    public function format($val){
        $ff = [
            '1.','2.','3.','4.','5.','6.','7.','8.','9.',
            '5、','6、','7，'
        ];
        foreach ($ff as $k=>$v){
            $val = str_ireplace($v,"",$val);
        }
        return trim($val);
    }

    public function tt(){
        $orgId = 28;
        $mode = 3;




//        $res  = send_jpush(['8'],AppMsg::PUSH_WORKER_ORDER_SEND,'',['id'=>1]);
//        echo "<pre/>";
//        print_r($res);
//        die;
    }
    public function index(){
        halt(get_unique_sn());
        $cc = config('database.database');
        halt($cc);
//        $json = [
//            'users' => [9],
//            'msg' => 'ssssssssssssssss',
//            'extra' => [],
//            'type' => AppMsg::PUSH_WORKER_ORDER_SEND
//        ];
//        queue_push(json_encode($json),0);

        $client = new \GuzzleHttp\Client();
        $url = "https://www.baidu.com";
        $response = $client->request('GET', $url);
        $result = $response->getBody();
        halt($result);
    }

    public function setSn(){
        $orders = Db::name('orders')->where('sn','')->limit(300)->field('id,create_time')->select();
        $orders = $orders?$orders:[];
        $nums = 100001;
        foreach ($orders as $k=>$v){
            Db::name('orders')->where('id',$v['id'])->update(['sn' => date('YmdHis').$nums]);
            $nums++;
        }
        dump(count($orders));
    }
    public function setSn2(){
        $orders = Db::name('todo')->where('sn','')->limit(300)->field('id,create_time')->select();
        $orders = $orders?$orders:[];
        $nums = 100001;
        foreach ($orders as $k=>$v){
            Db::name('todo')->where('id',$v['id'])->update(['sn' => date('YmdHis').$nums]);
            $nums++;
        }
        dump(count($orders));
    }

    public function sqldic(){
        $db = config('database.database');
        $sql = <<<sql
    select 
        TABLE_NAME as name,
        COLUMN_NAME as col,
        COLUMN_TYPE as type,
        COLLATION_NAME as collation,
        COLUMN_COMMENT as comment 
    from information_schema.columns
    where TABLE_SCHEMA='{$db}'
sql;
        $sql2 = <<<sql
    select 
        ENGINE as engine,
        TABLE_COLLATION as collation,
        TABLE_COMMENT as comment,
        TABLE_TYPE as type
    from information_schema.tables
    where TABLE_SCHEMA='{$db}' and TABLE_NAME=
sql;
        $lists = Db::query($sql);
        if(!$lists){
            halt('无数据');
        }
        $dic = [];
        $tables = [];
        foreach ($lists as $k=>$v){
            if(!in_array($v['name'],$tables)){

                $mdata = Db::query($sql2."'{$v['name']}'");
                if(!$mdata){
                    halt('查询表错误');
                }
                $table = $mdata[0];
                if($table['type'] != 'VIEW'){
                    $tables[] = $v['name'];
                    $cols = [];
                    foreach ($lists as $kk=>$vv){
                        if($vv['name'] == $v['name']){
                            $cols[] = [
                                'name' => $vv['col'],
                                'type' => $vv['type'],
                                'comment' => $vv['comment']
                            ];
                        }
                    }
                    $dic[] = [
                        'name' => $v['name'],
                        'engine' => $table['engine'],
                        'type' => $table['type'],
                        'collation' => $table['collation'],
                        'comment' => $table['comment'],
                        'cols' => $cols
                    ];
                }
            }
        }
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('数据库字典');

        $titles = ['表名','引擎','排序规则','注释'];
        foreach ($titles as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
        }
        
        $arr = array('A','B','C','D');
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        foreach($arr as $k=>$v){
            $worksheet->getStyle($v.'1')->applyFromArray($styleArray)->getFont()->setSize(12);
            $worksheet->getStyle($v)->applyFromArray($styleArray2)->getAlignment()->setWrapText(true);//换行
            $worksheet->getColumnDimension($v)->setWidth(25);
        }
        $lists = $dic;
        foreach ($lists as $k=>$v){
            $worksheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $worksheet->setCellValueByColumnAndRow(2, $k + 2, $v['engine']);
            $worksheet->setCellValueByColumnAndRow(3, $k + 2, $v['collation']);
            $worksheet->setCellValueByColumnAndRow(4, $k + 2, $v['comment']);

            $cols = $v['cols'];
            $sheet = $spreadsheet->createSheet($k+10)->setTitle($v['name']);
            $worksheet->getCellByColumnAndRow(1, $k + 2)->getHyperlink()->setUrl("sheet://{$v['name']}!A1");
            foreach ($cols as $kk=>$vv){
                $this->writeSheet($sheet,$cols,$k + 2);
            }
        }

        $worksheet->setCellValueByColumnAndRow(1, $k + 3, '共'.count($lists).'张表');

        $fileName = '数据库字典.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }

    private function writeSheet($sheet,$data,$row){
        $arr = array('A','B','C','D');
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        foreach($arr as $k=>$v){
            $sheet->getStyle($v.'2')->applyFromArray($styleArray)->getFont()->setSize(12);
            $sheet->getStyle($v)->applyFromArray($styleArray2)->getAlignment()->setWrapText(true);//换行
            $sheet->getColumnDimension($v)->setWidth(20);
        }
        $sheet->setCellValueByColumnAndRow(1, 1, '返回目录页');
        $sheet->getCellByColumnAndRow(1, 1)->getHyperlink()->setUrl("sheet://数据库字典!A".$row);

        $titles = ['字段名','类型','注释'];
        foreach ($titles as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key+1, 2, $value);
        }
        foreach ($data as $k=>$v){
            $sheet->setCellValueByColumnAndRow(1, $k + 3, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 3, $v['type']);
            $sheet->setCellValueByColumnAndRow(3, $k + 3, $v['comment']);
        }
    }

    public function host(){
        exit();
        $host1 = "http://lf.jya-tech.com";
        $host2 = "http://wy.gzbjwy.net";
        $host = "http://logistics.hetaigl.com";
//        $lists = Db::name('screen_module')->select();
//        foreach ($lists as $k=>$v){
//            if($v['img']){
//                $img = str_ireplace($host1,$host,$v['img']);
//                $img = str_ireplace($host2,$host,$img);
//                Db::name('screen_module')->where('id',$v['id'])->setField('img',$img);
//            }
//        }

        $lists = Db::name('screen')->select();
        foreach ($lists as $k=>$v){
            if($v['img']){
                $img = str_ireplace($host1,$host,$v['img']);
                $img = str_ireplace($host2,$host,$img);
                Db::name('screen')->where('id',$v['id'])->setField('img',$img);
            }
        }

        $lists = Db::name('screen_template')->select();
        foreach ($lists as $k=>$v){
            if($v['img']){
                $img = str_ireplace($host1,$host,$v['img']);
                $img = str_ireplace($host2,$host,$img);
                Db::name('screen_template')->where('id',$v['id'])->setField('img',$img);
            }
        }

//        $lists = Db::name('app_icon')->select();
//        foreach ($lists as $k=>$v){
//            if($v['path']){
//                $img = str_ireplace($host1,$host,$v['path']);
//                $img = str_ireplace($host2,$host,$img);
//                Db::name('app_icon')->where('id',$v['id'])->setField('path',$img);
//            }
//            if($v['url']){
//                $img = str_ireplace($host1,$host,$v['url']);
//                $img = str_ireplace($host2,$host,$img);
//                Db::name('app_icon')->where('id',$v['id'])->setField('url',$img);
//            }
//        }
    }

    private function getSize($text,$size,$weight,$height,$i=1,$angle=0){
        //获取文字信息
        $font = env('root_path').'public/static/hylht.ttf';
//        $size = 100;
        $info = imagettfbbox($size, $angle, $font, $text);
        $minx = min($info[0], $info[2], $info[4], $info[6]);
        $maxx = max($info[0], $info[2], $info[4], $info[6]);
        $miny = min($info[1], $info[3], $info[5], $info[7]);
        $maxy = max($info[1], $info[3], $info[5], $info[7]);
        /* 计算文字初始坐标和尺寸 */
        $x = $minx;
        $y = abs($miny);
        $w = $maxx - $minx;
        $h = $maxy - $miny;

        $xx = ($weight - $w) / 2;
        $yy = $height - ($h+10)*$i;

        return [$xx,$yy];
    }

    public function excel2(){
        set_time_limit(0);
        ini_set("memory_limit","2048M");
        exit();
        $page = 9;
        $size = 10000;
        $lists = Db::name('oa_lis')->page($page,$size)->order('start asc')->select();
        if(!$lists){
            halt('无数据');
        }
        foreach ($lists as $k=>$v){
            $btime = date('Y-m-d',strtotime($v['start'])).' 03:00:00';
            if($btime > $v['start']){ // 算昨天的
                $v['day'] = date('Ymd',strtotime($btime) - 24*60*60);
            }
            $ret = Db::name('oa')->where('name',$v['name'])->where('day',$v['day'])->find();
            if($ret){
                if(!$ret['end'] || $ret['end'] < $v['start']){
                    Db::name('oa')->where('id',$ret['id'])->update([
                        'end' => $v['start']
                    ]);
                }
            }else{
                if($btime <= $v['start']){
                    Db::name('oa')->insert([
                        'name' => $v['name'],
                        'start' => $v['start'],
                        'day' => $v['day'],
                    ]);
                }
            }
        }


        halt('结束2');
    }

    public function excel3(){
        $names = [
            '冯主任','张棉旺','吴晓刚','王先生','肖先生','王君','杨林源','王杰','董春艳','吴新刚','胡添滢','荣海燕',
            '丁文佳','王国玲','杨和融','赵雷','杨劲','石少亮','许倩鸿','杨璐','王玉玲','秦健','汪涵','郑琳','蔡涛',
            '贾永利','陈婧','周钊伟','雷春光','孙晨','谢正辉','吕雯','张宁','杨耀强','李本新','李月辉','陈晨','林进军',
            '寇建娣','王晓博','齐学东','隗永芝','牛全起','李鑫','闫勇哲','郭风军','张光利','文辉','王晨','王敏','范泉',
            '刘学政','王昱','马跃征','董杰','刘卉丛','梁艳','董军红','刘晓伟','赵雪','赵南','伍力','王宇','张京环',
            '李永尖','陈鹏','贾自强','杨云','黄志军','张天舒','张云','王新先','沈涛','吴德安','王帅','龙朝辉','王瑞',
            '刘爱枝','耿波','薛晓辉','徐新建','魏京菊','周云隆','孙立民','刁文矛','王鹏飞','冯峰-华馨泰','刘建宁-华馨泰'
        ];


//        $days = [1,2,3,4,5,6,7,10,16,17,23,24,30,31];
//        $mdays = 31;
//        $m = '10';
//        $month = '202110';

        $days = [6,7,13,14,20,21,27,28];
        $mdays = 30;
        $m = '11';
        $month = '202111';

//        $days = [4,5,11,12,18,19,25,26];
//        $mdays = 31;
//        $m = '12';
//        $month = '202112';


        $char = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $lins = [];
        for( $i=0;$i<$mdays*3 + 4;$i++ ){
            $r1 = $i%26;
            $r2 = floor($i/26);
            if($r2 == 0){
                $lins[] = $char[$r1];
            }else if($r2 == 1){
                $lins[] = 'A'.$char[$r1];
            }else if($r2 == 2){
                $lins[] = 'B'.$char[$r1];
            }else if($r2 == 3){
                $lins[] = 'C'.$char[$r1];
            }
        }

        $lists = [];
        foreach ($names as $k=>$v){
            $d = [
                'name' => $v,
                'tday' => $mdays - count($days),
            ];
            $thour = 0;
            for( $i=1;$i<=$mdays;$i++ ){
                if($i < 10){
                    $day = $month.'0'.$i;
                }else{
                    $day = $month.$i;
                }
                $d['s'.$i] = '';
                $d['e'.$i] = '';
                $d['h'.$i] = 0;
                $info = Db::name('oa')->where('name',$v)->where('day',$day)->find();
                if($info){
                    $d['s'.$i] = date('H:i:s',strtotime($info['start']));
                    $d['e'.$i] = $info['end']?date('H:i:s',strtotime($info['end'])):'';
                    $hour = 0;
                    if($info['end']){
                        $hour = round((strtotime($info['end']) - strtotime($info['start']))/3600,2);
                    }
                    $d['h'.$i] = $hour;
                    $thour = $thour + $hour;
                }
            }
            $d['thour'] = $thour;
            $d['avghour'] = round($thour/$d['tday'],2);
            $lists[] = $d;
        }
//        halt($lists);

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('考勤'.$m.'月');
        foreach ($lins as $k=>$v){
            $spreadsheet->setActiveSheetIndex(0)->getStyle($v)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $spreadsheet->setActiveSheetIndex(0)->getColumnDimension($v)->setAutoSize(true);
        }

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, '姓名');
        $spreadsheet->getActiveSheet()->mergeCells('A1:A2');
        $j = 1;
        for( $i=1;$i<=$mdays*3;$i=$i+3 ){
            $spreadsheet->getActiveSheet()->mergeCells($lins[$i].'1:'.$lins[$i+2].'1');
            $worksheet->setCellValueByColumnAndRow($i+1, 1, $j.'日');
            $worksheet->setCellValueByColumnAndRow($i+2, 1, '');
            $worksheet->setCellValueByColumnAndRow($i+3, 1, '');
            $j++;
        }
        $worksheet->setCellValueByColumnAndRow($mdays*3+2, 1, '应出勤天数');
        $spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+1].'1:'.$lins[$mdays*3+1].'2');
        $worksheet->setCellValueByColumnAndRow($mdays*3+3, 1, '总时长(h)');
        $spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+2].'1:'.$lins[$mdays*3+2].'2');
        $worksheet->setCellValueByColumnAndRow($mdays*3+4, 1, '平均时长(h)');
        $spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+3].'1:'.$lins[$mdays*3+3].'2');

        $worksheet->setCellValueByColumnAndRow(1, 2, '');
        for( $i=1;$i<=$mdays*3;$i=$i+3 ){
            $worksheet->setCellValueByColumnAndRow($i+1, 2, '上班');
            $worksheet->setCellValueByColumnAndRow($i+2, 2, '下班');
            $worksheet->setCellValueByColumnAndRow($i+3, 2, '时长(h)');
        }
        foreach ($lists as $k=>$v){
            $n = $v['name'];
            if($n == '冯主任'){
                $n = '冯磊';
            }else if($n == '王先生'){
                $n = '王汝江';
            }else if($n == '肖先生'){
                $n = '肖洪钦';
            }else if($n == '冯峰-华馨泰'){
                $n = '冯峰';
            }else if($n == '刘建宁-华馨泰'){
                $n = '刘建宁';
            }
            $worksheet->setCellValueByColumnAndRow(1, $k+3, $n);
            $j = 1;
            for( $i=1;$i<=$mdays*3;$i=$i+3 ){
                $worksheet->setCellValueByColumnAndRow($i+1, $k+3, $v['s'.$j]);
                $worksheet->setCellValueByColumnAndRow($i+2, $k+3, $v['e'.$j]);
                $worksheet->setCellValueByColumnAndRow($i+3, $k+3, $v['h'.$j]);
                $j++;
            }
            $worksheet->setCellValueByColumnAndRow($mdays*3+2, $k+3, $v['tday']);
            $worksheet->setCellValueByColumnAndRow($mdays*3+3, $k+3, $v['thour']);
            $worksheet->setCellValueByColumnAndRow($mdays*3+4, $k+3, $v['avghour']);
        }

        $filename = '考勤'.$m.'月.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }


    public function ll(){
        $list = Db::name('mate_goods')
            ->alias('a')
            ->join('mate_cate b','a.cate_id=b.id')
            ->where('a.org_id',27)
            ->field('a.*,b.title as cateName')
            ->select();

        $succ = 0;
       foreach ($list as $k=>$v){
           if($v['cateName']=='电工类'){
               $a = 9;
           }elseif ($v['cateName']=='木工类'){
               $a = 93;
           }elseif ($v['cateName']=='水工类'){
               $a = 128;
           }
           $r = Db::name('mate_goods')
               ->where('id',$v['id'])
               ->update([
                   'update_time'=>getTime(),
                   'cate_id'=>$a
               ]);
           if($r){
               $succ++;
           }
       }
       echo $succ;
    }

}