jya-oa-api/application/common/model/AttendanceRecordResult.php

<?php
namespace app\common\model;

use app\hander\HelpHander;
use think\Db;
use think\Exception;
use think\Model;

class AttendanceRecordResult extends Model
{
    protected $connection = 'db_config_wy';

    public function situationByMonth($month,$staffId,$orgId){
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-d', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );

        $lists = Db::name('attendance_user_class')
            ->where('user_id',$staffId)
            ->where('org_id',$orgId)
            ->where('day','>=',$start_time)
            ->where('day','<=',$end_time)
            ->select();

        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $record = Db::name('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);
            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]['content'] = $dates;

            $lists[$k]['lack'] = $lack;
            $lists[$k]['early'] = $early;
            $lists[$k]['late'] = $late;

            //获取今日请假记录
            $leave = Db::name('attendance_leave')
                ->where('status',1)
                ->where('user_id',$v['user_id'])
                ->where('start_time','<=',$v['day'].' 23:59:59')
                ->where('end_time','>=',$v['day'].' 00:00:00')
                ->select();
            $leave = $leave?$leave:[];
            foreach ($leave as $key=>$val){
                $leave['leave_type'] = Db::name('attendance_leave_type')->where('id',$val['id'])->value('name');
            }
            $lists[$k]['leave'] = $leave?$leave:[];
        }

        return $lists;
    }

    public function situationByMonth_old($month,$staffId,$orgId){
        $map[] = ['ar.user_id','=',$staffId];
        $map[] = ['ar.org_id','=',$orgId];
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-1', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );
        $map[] = ['day','>=',$start_time];
        $map[] = ['day','<=',$end_time];

        $lists = Db::name('attendance_record_result')
            ->alias('ar')
            ->join('user_info ui','ui.user_id = ar.user_id')
            ->join('attendance_class ac','ac.id = ar.class_id')
            ->where($map)
            ->field('ar.*,ui.name as user_name,ac.stime as start_time,ac.etime as end_time,ar.work as isWork')
            ->order('ar.id desc')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $record = Db::name('attendance_record')
                ->where('user_id',$staffId)
                ->where('create_time','>=',$v['day'].' 00:00:00')
                ->where('create_time','<=',$v['day'].' 23:59:59')
                ->field('type as record_type,create_time as record_time')
                ->select();
            $lists[$k]['recordDetail'] = $record?$record:[];
            $leave = Db::name('attendance_leave')
                ->where('user_id',$staffId)
                ->where('start_time','<=',$v['day'].' 23:59:59')
                ->where('end_time','>=',$v['day'].' 00:00:00')
                ->field('days as leave_days,leave_type,start_time as leave_start_time,end_time as leave_end_time')
                ->select();
            $lists[$k]['leaveDetail'] = $leave?$leave:[];
        }
        return $lists;
    }

    public function attendanceStatistics($month,$page,$size,$orgId,$userName){
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-d', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );
        if($userName !=''){
            $userIds = Db::name('user_info')->where('name','like','%'.$userName.'%')->column('user_id');
            $map[] = ['u.id','in',$userIds];
        }
        $map[] = ['u.del','=',0];
        $lists = Db::name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
           ->where($map)
            ->where('a.org_id',$orgId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->order('a.user_id')
            ->page($page,$size)
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $duration = 0;
            $uclist = Db::name('attendance_user_class')
                ->where('org_id',$orgId)
                ->where('day','>=',$start_time)
                ->where('day','<=',$end_time)
                ->where('user_id',$v['user_id'])
                ->field('content')
                ->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);

            $lists[$k]['user_name'] = Db::name('user_info')->where('user_id',$v['user_id'])->value('name');
            $work = Db::name('attendance_user_class')
                ->alias('auc')
                ->join('attendance_record ar','ar.user_class_id = auc.id','left')
                ->where('auc.org_id',$orgId)
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('auc.user_id',$v['user_id'])
                ->where('ar.user_id',$v['user_id'])
                ->group('auc.day')
                ->count();
//            $work = Db::name('attendance_record')
//                ->alias('ar')
//                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
//                ->where('auc.org_id',$orgId)
//                ->where('auc.day','>=',$start_time)
//                ->where('auc.day','<=',$end_time)
//                ->where('ar.user_id',$v['user_id'])
//                ->group('auc.day')
//                ->distinct(true)
//                ->count();
            $lists[$k]['work'] = $work; // 出勤天数

            $bk = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('ar.type',2)
                ->where('ar.user_id',$v['user_id'])
                ->count();
            $lists[$k]['bk'] = $bk; // 补卡次数

            $late = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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'])
                ->count();
            $lists[$k]['late'] = $late; // 迟到次数

            $lated = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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/60):0; // 迟到时长

            $early = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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'])
                ->count();
            $lists[$k]['early'] = $early; // 早退次数

            $earlyd = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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/60):0; // 早退时长

            // 请假天数
            $leave = Db::name('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; // 请假天数

            // 获取用户岗位
            $jlist = Db::name('user_job')
                ->alias('uj')
                ->join('job j','j.id = uj.job_id')
                ->join('dep d','d.id = j.dep_id')
                ->where('uj.user_id',$v['user_id'])
                ->field("d.name as dep,j.name as job,d.parent_id")
                ->select();
            $jlist = $jlist?$jlist:[];
            $deps = $jobs = [];
            $post = [];
            foreach ($jlist as $kk=>$vv){
                $subName = model('Dep')->getDepParent($vv['parent_id']);
                // $depname = $vv['dep'].'/'.$vv['job'];
                $depname = $vv['dep'];
                $jobs[] = $subName?$subName.'/'.$depname:$depname;
                $deps[] = $vv['dep'];
//                $jobs[] = $vv['job'];
                $post[] = $vv['job'];
            }
            $lists[$k]['dep'] = $deps?implode(',',$deps):'';
            $lists[$k]['job_name'] = $jobs?implode(',',$jobs):'';
//            $lists[$k]['post_name'] = $jobs?implode(',',$post):'';

        }

//        $total = Db::name('attendance_user_class')
//            ->where('org_id',$orgId)
//            ->where('day','>=',$start_time)
//            ->where('day','<=',$end_time)
//            ->group('user_id')
//            ->distinct(true)
//            ->order('user_id')
//            ->count();
        $total = Db::name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
            ->where('u.del',0)
            ->where('a.org_id',$orgId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->count();
        $data = [
            'total' => $total,
            'list' => $lists
        ];
        return $data;
    }

    public function wyattendanceStatistics($month,$page,$size,$userId = [],$userName){
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-d', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );
        if($userName !=''){
            $map[] = ['u.real_name','like','%'.$userName.'%'];
        }
        $map[] = ['u.del','=',0];
        $lists = Db::connect($this->connection)
            ->name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
            ->where($map)
            ->where('a.user_id','in',$userId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->order('a.user_id')
            ->page($page,$size)
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $duration = 0;
            $uclist = Db::connect($this->connection)
            ->name('attendance_user_class')
                ->where('day','>=',$start_time)
                ->where('day','<=',$end_time)
                ->where('user_id',$v['user_id'])
                ->field('content')
                ->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);

            $uid = Db::name('user_wyorg')
                ->where('wyuser_id',$v['user_id'])
                ->find();

            $lists[$k]['user_name'] = Db::name('user_info')
                ->where('user_id',$uid['user_id'])->value('name');
            $work = Db::connect($this->connection)
                ->name('attendance_user_class')
                ->alias('auc')
                ->join('attendance_record ar','ar.user_class_id = auc.id','left')
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('auc.user_id',$v['user_id'])
                ->where('ar.user_id',$v['user_id'])
                ->group('auc.day')
                ->count();
//            $work = Db::name('attendance_record')
//                ->alias('ar')
//                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
//                ->where('auc.org_id',$orgId)
//                ->where('auc.day','>=',$start_time)
//                ->where('auc.day','<=',$end_time)
//                ->where('ar.user_id',$v['user_id'])
//                ->group('auc.day')
//                ->distinct(true)
//                ->count();
            $lists[$k]['work'] = $work; // 出勤天数

            $bk = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('ar.type',2)
                ->where('ar.user_id',$v['user_id'])
                ->count();
            $lists[$k]['bk'] = $bk; // 补卡次数

            $late = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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'])
                ->count();
            $lists[$k]['late'] = $late; // 迟到次数

            $lated = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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/60):0; // 迟到时长

            $early = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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'])
                ->count();
            $lists[$k]['early'] = $early; // 早退次数

            $earlyd = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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/60):0; // 早退时长

            // 请假天数
            $leave = Db::connect($this->connection)
                ->name('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; // 请假天数
            $dep = Db::connect($this->connection)
                ->name('dep')
                ->alias('o')
                ->field('o.title')
                ->join('user_dep uo','uo.dep_id=o.id')
                ->where('uo.user_id',$v['user_id'])
//                ->where('uo.ORG_TYPE',1)
                ->find();
            $lists[$k]['dep'] = $dep?$dep['title']:'';
        }

//        $total = Db::connect($this->connection)
//            ->name('attendance_user_class')
//            ->where('day','>=',$start_time)
//            ->where('day','<=',$end_time)
//            ->where('user_id','in',$userId)
//            ->group('user_id')
//            ->distinct(true)
//            ->order('user_id')
//            ->count();
        $total = Db::connect($this->connection)
            ->name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
            ->where('u.del',0)
            ->where('a.user_id','in',$userId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->count();
        $data = [
            'total' => $total,
            'list' => $lists
        ];
        return $data;
    }

    public function wyattendanceExport($month,$userId = [],$userName){
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-d', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );
        if($userName !=''){
            $map[] = ['u.real_name','like','%'.$userName.'%'];
        }
        $map[] = ['u.del','=',0];
        $lists =Db::connect($this->connection)
            ->name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
            ->where($map)
            ->where('a.user_id','in',$userId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->order('a.user_id')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $duration = 0;
            $uclist = Db::connect($this->connection)
                ->name('attendance_user_class')
                ->where('day','>=',$start_time)
                ->where('day','<=',$end_time)
                ->where('user_id',$v['user_id'])
                ->field('content')
                ->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);

            $lists[$k]['user_name'] = Db::connect($this->connection)
                ->name('user')->where('id',$v['user_id'])->value('real_name');
            $work = Db::connect($this->connection)
                ->name('attendance_user_class')
                ->alias('auc')
                ->join('attendance_record ar','ar.user_class_id = auc.id','left')
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('auc.user_id',$v['user_id'])
                ->where('ar.user_id',$v['user_id'])
                ->group('auc.day')
                ->count();
//            $work = Db::name('attendance_record')
//                ->alias('ar')
//                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
//                ->where('auc.org_id',$orgId)
//                ->where('auc.day','>=',$start_time)
//                ->where('auc.day','<=',$end_time)
//                ->where('ar.user_id',$v['user_id'])
//                ->group('auc.day')
//                ->distinct(true)
//                ->count();
            $lists[$k]['work'] = $work; // 出勤天数

            $bk = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('ar.type',2)
                ->where('ar.user_id',$v['user_id'])
                ->count();
            $lists[$k]['bk'] = $bk; // 补卡次数

            $late = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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'])
                ->count();
            $lists[$k]['late'] = $late; // 迟到次数

            $lated = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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/60):0; // 迟到时长

            $early = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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'])
                ->count();
            $lists[$k]['early'] = $early; // 早退次数

            $earlyd = Db::connect($this->connection)
                ->name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.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/60):0; // 早退时长

            // 请假天数
            $leave = Db::connect($this->connection)
                ->name('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; // 请假天数

            $dep = Db::connect($this->connection)
                ->name('dep')
                ->alias('o')
                ->field('o.title')
                ->join('user_dep uo','uo.dep_id=o.id')
                ->where('uo.user_id',$v['user_id'])
//                ->where('uo.ORG_TYPE',1)
                ->find();
            $lists[$k]['dep'] = $dep?$dep['title']:'';
        }

        $columns = [
            ["title" => "姓名","key" => "userName"],
            ["title" => "部门","key" => "dep"],
            ["title" => "应出勤天数","key" => "days"],
            ["title" => "出勤天数","key" => "work"],
            ["title" => "工作时长","key" => "duration"],
            ["title" => "迟到次数","key" => "late"],
            ["title" => "迟到时长","key" => "lateDuration"],
            ["title" => "早退次数","key" => "early"],
            ["title" => "早退时长","key" => "earlyDuration"],
            ["title" => "补卡次数","key" => "bk"],
            ["title" => "请假","key" => "leave"],
        ];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];
        return $data;
    }

    public function attendanceExport($month,$orgId,$userName){
        $date = $month.'-01';
        $timestamp = strtotime( $date );
        $start_time = date( 'Y-m-d', $timestamp );
        $mdays = date( 't', $timestamp );
        $end_time = date( 'Y-m-' . $mdays, $timestamp );
        if($userName !=''){
            $userIds = Db::name('user_info')->where('name','like','%'.$userName.'%')->column('user_id');
            $map[] = ['u.id','in',$userIds];
        }
        $map[] = ['u.del','=',0];
        $lists = Db::name('attendance_user_class')
            ->alias('a')
            ->join('user u','u.id = a.user_id')
            ->where($map)
            ->where('a.org_id',$orgId)
            ->where('a.day','>=',$start_time)
            ->where('a.day','<=',$end_time)
            ->field('a.user_id,count(*) as days')
            ->group('a.user_id')
            ->distinct(true)
            ->order('a.user_id')
            ->select();
        $lists = $lists?$lists:[];
        foreach ($lists as $k=>$v){
            $duration = 0;
            $uclist = Db::name('attendance_user_class')
                ->where('org_id',$orgId)
                ->where('day','>=',$start_time)
                ->where('day','<=',$end_time)
                ->where('user_id',$v['user_id'])
                ->field('content')
                ->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);

            $lists[$k]['user_name'] = Db::name('user_info')->where('user_id',$v['user_id'])->value('name');
            $work = Db::name('attendance_user_class')
                ->alias('auc')
                ->join('attendance_record ar','ar.user_class_id = auc.id','left')
                ->where('auc.org_id',$orgId)
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('auc.user_id',$v['user_id'])
                ->where('ar.user_id',$v['user_id'])
                ->group('auc.day')
                ->count();
//            $work = Db::name('attendance_record')
//                ->alias('ar')
//                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
//                ->where('auc.org_id',$orgId)
//                ->where('auc.day','>=',$start_time)
//                ->where('auc.day','<=',$end_time)
//                ->where('ar.user_id',$v['user_id'])
//                ->group('auc.day')
//                ->distinct(true)
//                ->count();
            $lists[$k]['work'] = $work; // 出勤天数

            $bk = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->where('auc.day','>=',$start_time)
                ->where('auc.day','<=',$end_time)
                ->where('ar.type',2)
                ->where('ar.user_id',$v['user_id'])
                ->count();
            $lists[$k]['bk'] = $bk; // 补卡次数

            $late = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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'])
                ->count();
            $lists[$k]['late'] = $late; // 迟到次数

            $lated = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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/60):0; // 迟到时长

            $early = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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'])
                ->count();
            $lists[$k]['early'] = $early; // 早退次数

            $earlyd = Db::name('attendance_record')
                ->alias('ar')
                ->join('attendance_user_class auc','ar.user_class_id = auc.id')
                ->where('auc.org_id',$orgId)
                ->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/60):0; // 早退时长

            // 请假天数
            $leave = Db::name('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; // 请假天数

            // 获取用户岗位
            $jlist = Db::name('user_job')
                ->alias('uj')
                ->join('job j','j.id = uj.job_id')
                ->join('dep d','d.id = j.dep_id')
                ->where('uj.user_id',$v['user_id'])
                ->field("d.name as dep,j.name as job,d.parent_id")
                ->select();
            $jlist = $jlist?$jlist:[];
            $deps = $jobs = [];
            $post = [];
            foreach ($jlist as $kk=>$vv){
                $subName = model('Dep')->getDepParent($vv['parent_id']);
                // $depname = $vv['dep'].'/'.$vv['job'];
                $depname = $vv['dep'];
                $jobs[] = $subName?$subName.'/'.$depname:$depname;
                $deps[] = $vv['dep'];
//                $jobs[] = $vv['job'];
                $post[] = $vv['job'];
            }
            $lists[$k]['dep'] = $deps?implode(',',$deps):'';
            $lists[$k]['job_name'] = $jobs?implode(',',$jobs):'';
//            $lists[$k]['post_name'] = $jobs?implode(',',$post):'';
        }

        $columns = [
            ["title" => "姓名","key" => "userName"],
            ["title" => "岗位","key" => "jobName"],
            ["title" => "部门","key" => "dep"],
            ["title" => "应出勤天数","key" => "days"],
            ["title" => "出勤天数","key" => "work"],
            ["title" => "工作时长","key" => "duration"],
            ["title" => "迟到次数","key" => "late"],
            ["title" => "迟到时长","key" => "lateDuration"],
            ["title" => "早退次数","key" => "early"],
            ["title" => "早退时长","key" => "earlyDuration"],
            ["title" => "补卡次数","key" => "bk"],
            ["title" => "请假","key" => "leave"],
        ];

        $data = [
            'columns' => $columns,
            'list' => $lists
        ];
        return $data;
    }



}