jili2/application/admin/controller/AttendanceRecord.php

<?php
namespace app\admin\controller;

use think\Db;


class AttendanceRecord extends Auth
{

    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $star = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('title','','trim');
            if($title){
                $map[] = ['u.real_name','like','%'.$title.'%'];
            }
            $start = input('start','','trim');
            if($start){
                $map[] = ['ar.create_time',',>=',$start.' 00:00:00'];
            }
            $end= input('end','','trim');
            if($end){
                $map[] = ['ar.create_time',',<=',$end.' 23:59:59'];
            }
            $map[] = ['u.del','=',0];
            $map[] = ['ar.org_id','=',cur_org_id()];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db('attendance_record')
                ->alias('ar')
                ->field('u.real_name,ar.*')
                ->leftJoin('user u','u.id=ar.user_id')
                ->limit($star,$length)
                ->where($map)
                ->order(['ar.id'=>'desc'])
                ->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['day'] = Db::name('attendance_user_class')->where('id',$v['user_class_id'])->value('day');

                $lists[$k]['group_name'] = Db::name('attendance_group')
                    ->alias('ag')
                    ->join('attendance_user_class auc', 'auc.group_id = ag.id')
                    ->where('auc.id',$v['user_class_id'])
                    ->value('ag.name');

            }

            //数据返回
            $totalCount = db('attendance_record')
                ->alias('ar')
                ->leftJoin('user u','u.id=ar.user_id')
                ->where($map)
                ->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            return $this->fetch();
        }
    }

    public function check(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('title','','trim');
            if($title){
                $map[] = ['u.real_name','like','%'.$title.'%'];
            }

            $month = input('month','','trim');
            if($month){
                $map[] = ['auc.day','>=',$month.'-01'];
                $map[] = ['auc.day','<=',$month.'-31'];
            }else{
                $smonth= date('Y-m').'-01';
                $emonth = date('Y-m').'-31';
                $map[] = ['auc.day','>=',$smonth];
                $map[] = ['auc.day','<=',$emonth];
            }

            $map[] = ['u.del','=',0];
            $map[] = ['auc.org_id','=',cur_org_id()];
            $map= empty($map) ? true: $map;

            //数据查询
            $lists = db('attendance_user_class')
                ->alias('auc')
                ->field('u.real_name,auc.*')
                ->join('user u','u.id=auc.user_id')
                ->limit($start,$length)
                ->where($map)
                ->order(['auc.id'=>'desc'])
                ->select();

            foreach ($lists as $k=>$v){
                $record = db('attendance_record')
                    ->where('user_class_id',$v['id'])
                    ->order('create_time asc')
                    ->select();

                $record = $record?$record:[];

                $lack = 0;
                $early = 0;
                $late = 0;
                foreach ($record as $kk=>$vv){
                    if($vv['effective'] == 1&&$vv['status'] == 1){
                        $late++;
                    }else if($vv['effective'] == 1&&$vv['status'] == 2){
                        $early++;
                    }
                }
                $dates = json_decode($v['content'],true);
                $lists[$k]['content'] = $dates;
                foreach ($dates as $kk=>$vv){
                    $snr = $enr = [];
                    if($record){
                        $f1 = 0;
                        $f2 = 0;
                        foreach ($record as $kkk=>$vvv){
                            if($vvv['kq_time'] == $vv['stime']){
                                $snr[] = $vvv;
                                $f1 = 1;
                            }
                            if($vvv['kq_time'] == $vv['etime']){
                                $enr[] = $vvv;
                                $f2 = 1;
                            }
                        }
                        if(!$f1){
                            $lack++;
                        }
                        if(!$f2){
                            $lack++;
                        }
                    }else{
                        $lack += 2;
                    }
                    $dates[$kk]['slist'] = $snr;
                    $dates[$kk]['elist'] = $enr;
                }

                $lists[$k]['lack'] = $lack;     //缺卡次数
                $lists[$k]['early'] = $early;      //早退
                $lists[$k]['late'] = $late;       //迟到
            }

            //数据返回
            $totalCount = db('attendance_user_class')
                ->alias('auc')
                ->field('u.real_name,auc.*')
                ->join('user u','u.id=auc.user_id')
                ->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('month',date('Y-m'));
            return $this->fetch();
        }
    }

    public function checkDetails(){
        $id = input('id',0);
        if($id < 1){
            $this->error('参数错误');
        }

        $info = db('attendance_user_class')->where('id',$id)->find();

        $record = db('attendance_record')->where('user_class_id',$id)->order('create_time asc')->select();
        $record = $record?$record:[];

        $dates = json_decode($info['content'],true);
        foreach ($dates as $kk=>$vv){
            $snr = $enr = [];
            if($record){
                foreach ($record as $kkk=>$vvv){
                    if($vvv['kq_time'] == $vv['stime']){
                        $snr[] = $vvv;
                    }
                    if($vvv['kq_time'] == $vv['etime']){
                        $enr[] = $vvv;
                    }
                }
            }
            $dates[$kk]['slist'] = $snr;
            $dates[$kk]['elist'] = $enr;
        }

        $info['content'] = $dates;

        //获取今日请假记录
        $leave = db('attendance_leave')
            ->where('status',1)
            ->where('user_id',$info['user_id'])
            ->where('start_time','<=',$info['day'].' 23:59:59')
            ->where('end_time','>=',$info['day'].' 00:00:00')
            ->select();
        foreach ($leave as $k=>$v){
            if($v['leave_type'] == 1){
                $leave[$k]['leavetype_name'] = '事假';
            }
            if($v['leave_type'] == 2){
                $leave[$k]['leavetype_name'] = '病假';
            }
            if($v['leave_type'] == 3){
                $leave[$k]['leavetype_name'] = '年假';
            }
            if($v['leave_type'] == 4){
                $leave[$k]['leavetype_name'] = '婚假';
            }
            if($v['leave_type'] == 5){
                $leave[$k]['leavetype_name'] = '婚假';
            }
            if($v['leave_type'] == 6){
                $leave[$k]['leavetype_name'] = '丧假';
            }
        }

        $info['leave'] = $leave;
        $this->assign('info',$info);
        return $this->fetch();

    }

    // 改变打卡状态
    public function changeAttendance(){

        $data = [
            'type' => input('type'),
            'time' => input('time'),
            'min' => input('min'),
            'id' => input('id'),
            'cate' => input('cate'),
            'status' => input('status')
        ];

        if($data['type'] == 2 && $data['min'] <= 0){
            $this->error('分钟数不能为0');
        }
        if(!$data['time']){
            $this->error('参数错误1');
        }
        $info = db('attendance_user_class')->where('id',$data['id'])->find();

        if(!$info){
            $this->error('参数错误2');
        }

        if($data['cate'] == 1){ // 上班（正常上班/下班的记录是不可修改的）
            $ret = db('attendance_record')
                ->where('user_id',$info['user_id'])
                ->where('create_time',$data['time'])
                ->where('cate',1)
                ->where('effective',1)
                ->where('status',0)
                ->find();;

            if($ret){
                $this->error('上班打卡为正常状态，不能修改');
            }
        }else if($data['cate'] == 2){// 下班
            $ret = db('attendance_record')
                ->where('user_id',$info['user_id'])
                ->where('user_class_id',$data['id'])
                ->where('create_time',$data['time'])
                ->where('cate',2)
                ->where('effective',1)
                ->where('status',0)
                ->find();
            if($ret){
                $this->error('下班打卡为正常状态，不能修改');
            }
        }
        if($data['type'] == 1){
            $data['min'] = 0;
        }

        $signdate = $data['time'];
        $status = 0;
        $result = '管理员修改为:正常';
        if($data['cate'] == 1 && $data['type'] == 2){ //上班
            $signdate = date('Y-m-d H:i:s',strtotime($data['time']) + $data['min']*60);
            $status = 1;
            $result = '管理员修改为:迟到';
        }else if($data['cate'] == 2 && $data['type'] == 2){
            $signdate = date('Y-m-d H:i:s',strtotime($data['time']) - $data['min']*60);
            $status = 2;
            $result = '管理员修改为:早退';
        }

        Db::startTrans();
        try{
                db('attendance_record')
                ->where('user_class_id',$data['id'])
                ->where('user_id',$info['user_id'])
                ->where('kq_time',$data['time'])
                ->update(['effective' => 0,'result'=>"打卡无效:此记录已被更新"]);

            $rdata = [
                'kq_time' => $data['time'],
                'user_id' => $info['user_id'],
                'org_id' => $info['org_id'],
                'type' => 4,
                'from' => '管理员修改',
                'result' => $result,
                'create_time' => $signdate,
                'user_class_id' => $data['id'],
                'effective' => 1,
                'status' => $status,
                'cate' => $data['cate'],
                'duration' => $data['min']*60
            ];
            $add = db('attendance_record')->insert($rdata);

            if(!$add){
                exception('操作失败');
            }

            $dates = json_decode($info['content'],true);
            foreach ($dates as $k=>$v){
                if($rdata['cate'] == 1 && $v['stime'] == $rdata['kq_time']){
                    $dates[$k]['ssign'] = $rdata['create_time'];
                }else if($rdata['cate'] == 2 && $v['etime'] == $rdata['kq_time']){
                    $dates[$k]['esign'] = $rdata['create_time'];
                }
            }
            $save = db('attendance_user_class')->where('id',$info['id'])->update(['content' => json_encode($dates)]);

            if(!$save){
                exception('操作失败');
            }
            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            $this->error('操作失败');
        }
        $this->success('操作成功');
    }

    public function results(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $month = input('month','','trim');

            if($month){
                $map[] = ['auc.day','>=',$month.'-01'];
                $map[] = ['auc.day','<=',$month.'-31'];
            }else{
                $smonth= date('Y-m').'-01';
                $emonth = date('Y-m').'-31';
                $map[] = ['auc.day','>=',$smonth];
                $map[] = ['auc.day','<=',$emonth];
            }
            $start_time = $month.'-01';
            $end_time = $month.'-31';

            $map[] = ['u.del','=',0];
            $map[] = ['u.enable','=',1];
            $map[] = ['auc.org_id','=',cur_org_id()];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db('attendance_user_class')
                ->alias('auc')
                ->field('auc.*,count(*) as days,u.real_name')
                ->join('user u','u.id=auc.user_id')
                ->limit($start,$length)
                ->where($map)
                ->order('auc.user_id asc')
                ->group('auc.user_id')
                ->select();

            foreach ($lists as $k=>$v){
                $duration = 0;
                $uclist = db('attendance_user_class')
                    ->field('content')
                    ->where('org_id',cur_org_id())
                    ->where('day','>=',$start_time)
                    ->where('day','<=',$end_time)
                    ->where('user_id',$v['user_id'])
                    ->select();
                foreach ($uclist as $key=>$val){
                    $dates = json_decode($val['content'],true);
                    foreach ($dates as $kk=>$vv){
                        if(!empty($vv['ssgin']) && !empty($vv['esign'])){
                            $duration += strtotime($vv['esign']) - strtotime($vv['ssgin']);
                        }
                    }
                }
                $lists[$k]['duration'] = round($duration/60);

                $work = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->field('auc.day')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.user_id',$v['user_id'])
                    ->group('auc.day')
                    ->select();
                $lists[$k]['work'] = $work ? count($work): 0; // 出勤天数

                $bk = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.type',2)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['bk'] = $bk ? count($bk) : 0; // 补卡次数

                $late = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',1)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['late'] = $late ? count($late) :0; // 迟到次数

                $lated = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',1)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->sum('ar.duration');

                $lists[$k]['late_duration'] = $lated?round($lated['duration']/60):0; // 迟到时长

                $early = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',2)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['early'] = $early ? count($early) :0; // 早退次数

                $earlyd =  db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',2)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->sum('ar.duration');

                $lists[$k]['early_duration'] = $earlyd?round($earlyd['duration']/60):0; // 早退时长

                // 请假天数
                $leave = db('attendance_leave')
                    ->where('user_id',$v['user_id'])
                    ->where('status',1)
                    ->where('start_time','<=',$end_time.' 23:59:59')
                    ->where('end_time','>=',$start_time.' 00:00:00')
                    ->sum('cur_days');

                $lists[$k]['leave'] = $leave ? round($leave['cur_days'],1): 0; // 请假天数
            }

            //数据返回
            $totalCount = db('attendance_user_class')
                ->alias('auc')
                ->field('auc.*,count(*) as days,u.real_name')
                ->join('user u','u.id=auc.user_id')
                ->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('month',date('Y-m'));
            return $this->fetch();
        }
    }

    //excel导出
    public function export() {
        $month = input('month','','trim');
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        if (request()->isGet()) {
            $start_time = $month.'-01';
            $end_time = $month.'-31';
            $map[] = ['u.del','=',0];
            $map[] = ['u.enable','=',1];
            $map[] = ['auc.org_id','=',cur_org_id()];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db('attendance_user_class')
                ->alias('auc')
                ->field('auc.*,count(*) as days,u.real_name')
                ->join('user u','u.id=auc.user_id')
                ->where($map)
                ->order('auc.user_id asc')
                ->group('auc.user_id')
                ->select();

            foreach ($lists as $k=>$v){
                $duration = 0;
                $uclist = db('attendance_user_class')
                    ->field('content')
                    ->where('org_id',cur_org_id())
                    ->where('day','>=',$start_time)
                    ->where('day','<=',$end_time)
                    ->where('user_id',$v['user_id'])
                    ->select();
                foreach ($uclist as $key=>$val){
                    $dates = json_decode($val['content'],true);
                    foreach ($dates as $kk=>$vv){
                        if(!empty($vv['ssgin']) && !empty($vv['esign'])){
                            $duration += strtotime($vv['esign']) - strtotime($vv['ssgin']);
                        }
                    }
                }
                $lists[$k]['duration'] = round($duration/60);

                $work = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->field('auc.day')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.user_id',$v['user_id'])
                    ->group('auc.day')
                    ->select();
                $lists[$k]['work'] = $work ? count($work): 0; // 出勤天数

                $bk = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.type',2)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['bk'] = $bk ? count($bk) : 0; // 补卡次数

                $late = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',1)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['late'] = $late ? count($late) :0; // 迟到次数

                $lated = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',1)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->sum('ar.duration');

                $lists[$k]['late_duration'] = $lated?round($lated['duration']/60):0; // 迟到时长

                $early = db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',2)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->select();

                $lists[$k]['early'] = $early ? count($early) :0; // 早退次数

                $earlyd =  db('attendance_record')
                    ->alias('ar')
                    ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                    ->where('auc.org_id',cur_org_id())
                    ->where('auc.day','>=',$start_time)
                    ->where('auc.day','<=',$end_time)
                    ->where('ar.status',2)
                    ->where('ar.effective',1)
                    ->where('ar.user_id',$v['user_id'])
                    ->sum('ar.duration');

                $lists[$k]['early_duration'] = $earlyd?round($earlyd['duration']/60):0; // 早退时长

                // 请假天数
                $leave = db('attendance_leave')
                    ->where('user_id',$v['user_id'])
                    ->where('status',1)
                    ->where('start_time','<=',$end_time.' 23:59:59')
                    ->where('end_time','>=',$start_time.' 00:00:00')
                    ->sum('cur_days');

                $lists[$k]['leave'] = $leave ? round($leave['cur_days'],1): 0; // 请假天数
            }
            //实例化PHPExcel类
            $objPHPExcel =new \PHPExcel();
            //激活当前的sheet表
            $objPHPExcel->setActiveSheetIndex(0);
            //设置表格头（即excel表格的第一行）
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '姓名')
                ->setCellValue('B1', '应出勤天数')
                ->setCellValue('C1', '出勤天数')
                ->setCellValue('D1', '工作时长')
                ->setCellValue('E1', '迟到次数')
                ->setCellValue('F1', '迟到时长')
                ->setCellValue('G1', '早退次数')
                ->setCellValue('H1', '早退时长')
                ->setCellValue('I1', '补卡次数')
                ->setCellValue('J1', '请假');
            // 设置表格头水平居中
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置列水平居中
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置单元格宽度
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(30);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(30);
            //循环刚取出来的数组，将数据逐一添加到excel表格。
            for ($i = 0; $i < count($lists); $i++) {
                $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['real_name']);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['days']);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['work']);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['duration']);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['late']);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['late_duration']);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $lists[$i]['early']);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $lists[$i]['early_duration']);
                $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 2), $lists[$i]['bk']);
                $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 2), $lists[$i]['leave']);

            }
            //设置保存的Excel表格名称
            $filename = '考勤报表_' . date('YmdHis', time()) . '.xls';
            //设置当前激活的sheet表格名称
            $objPHPExcel->getActiveSheet()->setTitle('考勤报表');
            //设置浏览器窗口下载表格
            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="' . $filename);
            //生成excel文件
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            //下载文件在浏览器窗口
            return $objWriter->save('php://output');
        }

    }



}