sycheckserver/application/index/controller/Test.php

<?php
/**
 * Created by PhpStorm.
 * User: sicilon_IT
 * Date: 2020/1/15
 * Time: 13:56
 */
namespace app\index\controller;
use app\index\model\daycheckinfo;
use app\index\logic\workermanger;
use app\index\logic\checkmanger;
use app\index\logic\prjmanger;
use app\index\model\ginfomodel;
use app\index\model\wheadmodel;
use app\index\model\gworkermodel;
use app\index\logic\contactlogic;
use app\index\logic\recuitlogic;
use app\index\logic\workercheck;
use app\index\logic\paylogic;
use think\Controller;
use think\File;

class Test extends Controller{
    public function testtime(){
       echo  date('Y-m-d',strtotime('-1 day'));
    }

    public function testgetdays(){
        echo  date('t', strtotime("2020-01-1"));
    }

    public function teststrvla(){
        echo $fieldname=strval(1);
        echo $fieldname=strval(11);
        for ($i=0;$i<31;$i++){
            $k=strval($i);
            if (strlen($k)<2){
                $k='0'.$k;

            }
            echo $k;
            echo '<br>';
        }
    }

    public function testexeclclass(){
        vendor('PHPExcel.PHPExcel');
        //$fcell=new \PHPExcel_Cell();
        for ($j=1;$j<=31;$j++){
            $fcolumnstr=\PHPExcel_Cell::stringFromColumnIndex($j+4);
            echo $fcolumnstr;
        }

    }

    public function testsumbymonth(){
        $wid=101;
        $table=new daycheckinfo();
        $rlist=$table->totalcheckbymonthwithwid($wid);
        var_dump($rlist);
    }



    public function testworkover(){
        $l_check=new checkmanger();
        $rlist=$l_check->getworkovertime(1,10);

        return json_encode($rlist,320);
    }

    public function t_prj_getglist(){
        echo 'test start ok!';
        echo '<br>';
        $g_talbe=new ginfomodel();
        $gheadinfo=new wheadmodel();
        $work_table=new gworkermodel();
        //获取所有班组
        $res=$g_talbe->getglistbypid(1);

        echo 'res for start';
        foreach ($res as &$gitem) {
            //取班组长名称
            $gid=$gitem['id'];
            echo '[gid:';
            echo $gid;
            echo "],";

            $gheader=$gheadinfo->getwidbygid($gid);


            if ($gheader){
                $wid=$gheader['gwid'];

                $winfo=$work_table->getinfobyid($wid);
                echo $winfo['wname'];
                echo '<br>';
                $gitem['wname']=$winfo['wname'];
            }else{
                $gitem['wname']='';
            }
        }
        return json_encode($res,320);

    }

    public function testjson11(){
        $date='2020-03-11';
        $daystr=substr($date,-2);

       // $v["11"]=20;
        $v[$daystr]=20;
        echo json_encode($v);
        print_r($v);
    }

    public function testdaycount(){
        $dc=20;
        $strdc=strval($dc+2);
        echo $strdc;
        $strparam='-'.$strdc.' day';
        echo 'strparm='.$strparam;
        echo '<bc>';
        $datestart=date('Y-m-d',strtotime($strparam));
        echo 'datsstart='.$datestart;
    }

    public function testnewsumgroupworkerinfo(){
        $arr['cid']=1;
        $arr['yearinfo']='2020';
        $arr['monthinfo']='03';
        $l_c=new contactlogic();
        $list=$l_c->getworkersumbycid($arr);

//        foreach($list as $k){
//            $k_list=array_keys($k['detail']);
//           // var_dump($k_list);
//            foreach($k_list as $kv){
//                echo $kv;
//            }
//
////            foreach($k['detail'] as $v){
////                var_dump($v);
////            };
////            $alen=count($k['detail']);
////            for ($i=0;$i<$alen;$i++){
////                var_dump($k['detail'][$i]);
////            }
//        }

        return json_encode($list,320);
    }

    //考勤按合同汇总信息导出execl
    public function workersumtoexecl(){
        //获取数据
   //     $queryinfo=request() -> param();
        $queryinfo['cid']=6;
        $queryinfo['yearinfo']='2020';
        $queryinfo['monthinfo']='03';
        $yearinfo=$queryinfo['yearinfo'];
        $monthinfo=$queryinfo['monthinfo'];
        $l_contact=new contactlogic();
        //获取当用天数
        $daysofm=date('t', strtotime( $yearinfo.'-'.$monthinfo.'-1'));
        $gsumlist=$l_contact->getworkersumbycid($queryinfo);

        //加载类库
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        //4.激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '班组/工人')
            ->setCellValue('B1', '当月总记分');
        for ($j=1; $j<=$daysofm;$j++){
            $columnname=strval($j);
            if (strlen($columnname)<2){
                $columnname='0'.$columnname;
            }
            $fcolumnchar=\PHPExcel_Cell::stringFromColumnIndex($j+2);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($fcolumnchar.'1',$columnname);
        }
        //设置A列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(15);
        //6.循环刚取出来的数组，将数据逐一添加到excel表格。
        $rowindex=0;
        for($i=0;$i<count($gsumlist);$i++){
            //班组汇总信息
            $objPHPExcel->getActiveSheet()->setCellValue('A'.($rowindex+2),$gsumlist[$i]['gname']);//ID
            $objPHPExcel->getActiveSheet()->setCellValue('B'.($rowindex+2),$gsumlist[$i]['summonth']);//名称

           // $a_keys=array_keys($gsumlist[$i]);
          //  var_dump($a_keys);
            for ($j=1; $j<=$daysofm;$j++){
                //  for ($j=13; $j<=16;$j++){//测试临时
                $daystr=strval($j);
                if (strlen($daystr)<2){
                    $daystr='0'.$daystr;
                }
                $fname=$yearinfo.'-'.$monthinfo.'-'.$daystr;
                //$eecll=new \PHPExcel_Cell()
                $fcolumnstr=\PHPExcel_Cell::stringFromColumnIndex($j+2);

                echo $fname;
                //echo true;
               // echo '<br>';
                echo (array_key_exists($fname,$gsumlist[$i]));
                echo '<br>';
//                echo (in_array($fname,$a_keys));
//                echo '<br>';
                $objPHPExcel->getActiveSheet()->setCellValue($fcolumnstr.($rowindex+2),(array_key_exists($fname,$gsumlist[$i])?$gsumlist[$i][$fname]:'-'));//说明
                //  $objPHPExcel->getActiveSheet()->setCellValue($fcolumnstr.($rowindex+2),$gsumlist[$i][$fname]);//说明
            }
            $rowindex++;//行控制
            //员工数据
            for($workerindex=0;$workerindex<count($gsumlist[$i]['detail']);$workerindex++){
                $objPHPExcel->getActiveSheet()->setCellValue('A'.($rowindex+2),$gsumlist[$i]['detail'][$workerindex]['gname']);//
                $objPHPExcel->getActiveSheet()->setCellValue('B'.($rowindex+2),$gsumlist[$i]['detail'][$workerindex]['summonth']);//
                for ($j=1; $j<=$daysofm;$j++){
                    $daystr=strval($j);
                    if (strlen($daystr)<2){
                        $daystr='0'.$daystr;
                    }
                    $fname=$yearinfo.'-'.$monthinfo.'-'.$daystr;
                    //$eecll=new \PHPExcel_Cell()
                    $fcolumnstr=\PHPExcel_Cell::stringFromColumnIndex($j+2);
                    $objPHPExcel->getActiveSheet()->setCellValue($fcolumnstr.($rowindex+2),(array_key_exists($fname,$gsumlist[$i]['detail'][$workerindex])?$gsumlist[$i]['detail'][$workerindex][$fname]:'-'));//说明
                }
                $rowindex++;//行控制
            }
        }
        //7.设置保存的Excel表格名称
        $filename = '考勤汇总'.date('ymd',time()).'.xls';
        //8.设置当前激活的sheet表格名称；
        $objPHPExcel->getActiveSheet()->setTitle('考勤汇总');
        //9.设置浏览器窗口下载表格
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$filename.'"');
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        $objWriter->save('php://output');
        //原代码没有返回
        $res_r['code']=200;
        $res_r['resultData']=$filename;
        return json_encode($res_r,320);
    }

    public function testouttoexecl(){
        //获取传入参数
        set_time_limit(0);
        $queryinfo['monthinfo']='03';
        $queryinfo['yearinfo']='2020';
        $queryinfo['pageNum']=1;
        $queryinfo['pageSize']=0;
        $month=$queryinfo['monthinfo'];
        $year=$queryinfo['yearinfo'];
        //获取数据
        $l_check=new checkmanger();
        $r_list=$l_check->getsumcheckinfo($queryinfo);
        //获取当用天数
        $daysofm=date('t', strtotime($year.'-'.$month.'-1'));

        //加载类库
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        //4.激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '姓名')
            ->setCellValue('C1', '记工天数')
            ->setCellValue('D1','当月计分')
            ->setCellValue('E1','身份证号');
        for ($j=1; $j<=$daysofm;$j++){
            $columnname=strval($j);
            if (strlen($columnname)<2){
                $columnname='0'.$columnname;
            }
            $fcolumnchar=\PHPExcel_Cell::stringFromColumnIndex($j+4);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($fcolumnchar.'1',$columnname);
        }

        //设置A列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(18);
        //6.循环刚取出来的数组，将数据逐一添加到excel表格。
        for($i=0;$i<count($r_list);$i++){
            $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$r_list[$i]['wid']);//ID
            $objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$r_list[$i]['wname']);//名称
            $objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$r_list[$i]['daycount']);//说明
            $objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$r_list[$i]['monthsum']);//说明
            $objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$r_list[$i]['sfz']);//说明
            for ($j=1; $j<=$daysofm;$j++){
                $fname=strval($j);
                if (strlen($fname)<2){
                    $fname='0'.$fname;
                }
                //$eecll=new \PHPExcel_Cell()
                $fcolumnstr=\PHPExcel_Cell::stringFromColumnIndex($j+4);
                $objPHPExcel->getActiveSheet()->setCellValue($fcolumnstr.($i+2),(array_key_exists($fname,$r_list[$i])?$r_list[$i][$fname]:'-'));//说明
            }
        }
        //7.设置保存的Excel表格名称
        $filename = $year.'年'.$month.'月考勤统计表'.date('ymd',time()).'.xls';
        //8.设置当前激活的sheet表格名称；
        $objPHPExcel->getActiveSheet()->setTitle('考勤信息'.$year.'年'.$month.'月');
        //9.设置浏览器窗口下载表格
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$filename.'"');
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        $objWriter->save('php://output');
        //原代码没有返回
        $res_r['code']=200;
        $res_r['resultData']=$filename;
        return json_encode($res_r,320);
    }

    public function testheadoprlist(){
       // $queryinfo=request() -> param();
        $queryinfo['hid']=102;

        $l_opr=new checkmanger();
        $rlist=$l_opr->getoprinfobyhid($queryinfo);
        if (empty($rlist)){
            $res_r['code']=0;
            $res_r['msg']='无考勤数据';
        }else{
            $res_r['code']=200;
            $res_r['resultData']=$rlist;
        }
        return json_encode($res_r,320);

    }

    public function testcountaward(){
        $queryinfo['uid']=101;
        $l_r=new recuitlogic();
        $rec=$l_r->getawardbyrid($queryinfo);
        return json_encode($rec,320);
    }

    public function testexcelinput(){
        vendor('PHPExcel.PHPExcel');
        //$phpExcel = new \PHPExcel();
        $objRender = \PHPExcel_IOFactory::createReader('excel2007');
        $filename = '.'.'/'.'Uploads'.'/'.'test1.xlsx';
        $filename2 = '.'.'/'.'Uploads'.'/'.'test2.xlsx';
        $ExcelObj = $objRender->load($filename);
        $ExcelObj->setActiveSheetIndex(0);

        $ExcelObj->getActiveSheet()->setCellValue('C6',4);
        $ExcelObj->getActiveSheet()->setCellValue('D6',5);
        $ExcelObj->getActiveSheet()->setCellValue('E6',20);
        $flag = $ExcelObj->getActiveSheet()->getCell("B1")->getValue();
        $objWriter = \PHPExcel_IOFactory::createWriter($ExcelObj, 'excel2007');
        $objWriter->save($filename2);
        echo $flag;

    }

    public function testupcheck(){
        $queryinfo['pid']=14;
        $queryinfo['checkdate']='2020-05-04';
        $queryinfo['checkshift']=1;
        $logicworker=new workercheck();
        $wlist=$logicworker->getunchecklist($queryinfo);
        var_dump($wlist);
    }

    public function testnewchout(){
        $arr['cid']=4;
        $arr['yearinfo']='2020';
        $arr['monthinfo']='08';
        $logic=new contactlogic();
        $llist=$logic->getworkersumbycid($arr);
        var_dump($llist);

    }


    public function testexcelinputdb(){
        vendor('PHPExcel.PHPExcel');
        //$phpExcel = new \PHPExcel();
        $objRender = \PHPExcel_IOFactory::createReader('excel2007');
        $filename = '.'.'/'.'Uploads'.'/'.'payrecord.xlsx';
        $ExcelObj = $objRender->load($filename);
        $ExcelObj->setActiveSheetIndex(0);

        $l_p=new paylogic();
        $plist=array();

        for($i=2;$i<10;$i++){
            $paydetail=array();
            $paydetail['paymoney']=$ExcelObj->getActiveSheet()->getCell("H".$i)->getValue();
            $paydetail['sfzid']=$ExcelObj->getActiveSheet()->getCell("F".$i)->getValue();
            $paydetail['wname']=$ExcelObj->getActiveSheet()->getCell("G".$i)->getValue();
            $paydetail['payclass']=$ExcelObj->getActiveSheet()->getCell("E".$i)->getValue();
            $daynumber=$ExcelObj->getActiveSheet()->getCell("D".$i)->getValue();
            $paydetail['paydate']=gmdate('Y-m-d',($daynumber-25569)*1440*60);
            $paydetail['indate']=date('Y-m-d');
            $l_p->savepaydetail($paydetail);
            array_push($plist,$paydetail);
        }
        echo count($plist);
        var_dump($plist);
    }

    public function testdatastrtrans(){
        $i=44302-25569;
        $j=$i*1440*60;


        echo $j."</br>";
        echo gmdate('Y-m-d',$j);
        //date_format($j,"YYYY-mm-dd");
        //var_dump(strtotime('1900-01-01','+'.$i.' days'));

    }
}