crm_project/application/admin/controller/qingdong/statistic/Work.php

<?php

namespace app\admin\controller\qingdong\statistic;

use addons\qingdong\model\Daily;
use addons\qingdong\model\Record;
use addons\qingdong\model\Staff;
use app\admin\model\AuthGroup;
use app\common\controller\Backend;
use fast\Tree;
use think\Exception;

/**
 * 员工工作日志分析
 */
class Work extends Backend
{
    public function _initialize()
    {
        try{
            \think\Db::execute("SET @@sql_mode='';");
        }catch (Exception $e){

        }
        parent::_initialize();

        $childrenGroupIds = $this->auth->getChildrenGroupIds(true);
        $groupList = collection(AuthGroup::where('id', 'in', $childrenGroupIds)->select())->toArray();

        Tree::instance()->init($groupList);
        $groupList = [];
        if ($this->auth->isSuperAdmin()) {
            $groupList = Tree::instance()->getTreeList(Tree::instance()->getTreeArray(0));
        } else {
            $groups = $this->auth->getGroups();
            $groupIds = [];
            foreach ($groups as $m => $n) {
                if (in_array($n['id'], $groupIds) || in_array($n['pid'], $groupIds)) {
                    continue;
                }
                $groupList = array_merge($groupList, Tree::instance()->getTreeList(Tree::instance()->getTreeArray($n['pid'])));
                foreach ($groupList as $index => $item) {
                    $groupIds[] = $item['id'];
                }
            }
        }
        $this->assign('groupdata',$groupList);
    }

    /**
     * 工作报告
     */
    public function report(){
        $row=input('row/a');
        $type=$row['type']??'日报';
        $year=$row['year']??date('Y');
        $month=$row['month']??date('m');
        $group_id=$row['group_id']??0;
        $staff_id=$row['staff_id']??0;
        $where=['type'=>$type];
        $ids=[];
        if ($group_id) {//角色组
            $ids = Staff::getGroupStaffIds($group_id);
        }
        if ($staff_id) {
            $ids = $row['staff_id'];
        }

        if($type == '日报'){
            $where['createtime'] = ['between', [strtotime("{$year}-{$month}-01"),
                strtotime("{$year}-".($month+1).'-01') - 1]];
        }elseif($type == '年报'){

        }else{
            $where['createtime'] = ['between', [strtotime($year . '-01-01'),
                strtotime(($year + 1) . '-01-01') - 1]];
        }
        $dates=[];
        $lastDay = date('d', strtotime("{$year}-{$month} +1 month -1 day"));

        if($type == '日报'){
            for($i=1;$i<=$lastDay;$i++){
                $dates[]=['date'=>$i,'between'=>"{$year}-{$month}-{$i}",
                    'search'=>"{$year}-{$month}-{$i} 00:00:00 - {$year}-{$month}-{$i} 23:59:59"];
            }
        }else if($type == '月报'){
            for($i=1;$i<=12;$i++){
                $e=date('d', strtotime("{$year}-{$i} +1 month -1 day"));
                $dates[]=['date'=>$i,'between'=>[$year.'-'.$i,$year.'-'.($i+1)],
                    'search'=>"{$year}-{$i}-01 00:00:00 - {$year}-{$i}-{$e} 23:59:59"];
            }
        }else if($type == '季报'){
            for($i=1;$i<=4;$i++){
                $r = $i * 3;
                $s=($i-1)*3+1;
                $e=date('d', strtotime("{$year}-{$r} +1 month -1 day"));
                $dates[]=['date'=>$i,
                    'search'=>"{$year}-{$s}-01 00:00:00 - {$year}-{$r}-{$e} 23:59:59"];
            }
        }else if($type == '年报'){
            $y=date('Y');
            for ($i = 2022; $i <= $y; $i++) {
                $dates[] = ['date' => $i,
                    'search' => "{$i}-01-01 00:00:00 - {$i}-12-31 23:59:59"];
            }
        }else if($type == '周报'){
            $week = date('W',strtotime($year.'-12-31'));
            $s=7-date('w',strtotime($year.'-01-01'));
            for ($i = 1; $i <= $week; $i++) {
                $s1 = ($i - 2) * 7 + $s;
                $s1 = $s1 < 0 ? 0 : $s1;
                $s2 = ($i - 1) * 7 + $s;
                $sw=date('Y-m-d', strtotime($year . '-01-01 '.'+' . $s1 . ' day'));
                $ew=date('Y-m-d', strtotime($year . '-01-01 '.'+' . $s2 . ' day')) ;
                $dates[] = ['date' => $i, 'between' => [$sw,$ew],
                    'search' =>  $sw . " 00:00:00 - " .
                        $ew. " 23:59:59"];
            }
        }
        $daily = Daily::where($where)->field('createtime,create_staff_id')->select();
        $whereT=[];
        if($group_id || $staff_id){
            $whereT=['id'=>['in',$ids]];
        }
        $staff = Staff::where($whereT)->field('id,name,img')->select();
        $staff=collection($staff)->toArray();
        $result=[];
        foreach ($daily as $d) {
            if($type == '日报'){
                $time = date('Y-m-d', strtotime($d['createtime']));
            }elseif($type == '月报'){
                $time = date('Y-m-d', strtotime($d['createtime']));
            }elseif($type == '周报'){
                $time = date('Y-m-d', strtotime($d['createtime']));
            }elseif($type == '季报'){
                $time = date('Y-m', strtotime($d['createtime']));
            }elseif($type == '年报'){
                $time = date('Y', strtotime($d['createtime']));
            }

            $result[$time][] = $d['create_staff_id'];
        }
        $list=[];
        $startYear=strtotime($year.'-01-01');
        $endYear=strtotime($year.'-12-31');
        $startMonth=strtotime($year."-{$month}-01");
        $endMonth=strtotime($year."-{$month}-".$lastDay);
        $allDay=($endYear-$startYear)/86400+1;
        foreach ($staff as $s){
            $row=$s;


            if ($type == '日报') {
                $count = Daily::where([
                    'type'=>$type,
                    'create_staff_id'=>$s['id'],
                    'createtime'=>['between',[$startYear,$endYear]]
                ])->field("FROM_UNIXTIME(createtime,'%Y-%m-%d') as ctime")->group('ctime')->count();
                $row['year']=$count;
                $row['year_ratio']= sprintf("%.2f", $count/$allDay * 100);
                $count = Daily::where([
                    'type' => $type,
                    'create_staff_id' => $s['id'],
                    'createtime' => ['between', [$startMonth, $endMonth]]
                ])->field("FROM_UNIXTIME(createtime,'%Y-%m-%d') as ctime")->group('ctime')->count();
                $row['month'] = $count;
                $row['month_ratio'] = sprintf("%.2f", $count / $allDay * 100);
            }elseif($type =='周报'){
                $count = Daily::where([
                    'type'=>$type,
                    'create_staff_id'=>$s['id'],
                    'createtime'=>['between',[$startYear,$endYear]]
                ])->field("FROM_UNIXTIME(createtime,'%Y-%m-%d') as ctime")->group('ctime')->count();
                $row['year']=$count;
                $row['year_ratio']= sprintf("%.2f", $count/12 * 100);
            }elseif($type =='月报'){
                $count = Daily::where([
                    'type'=>$type,
                    'create_staff_id'=>$s['id'],
                    'createtime'=>['between',[$startYear,$endYear]]
                ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime")->group('ctime')->count();
                $row['year']=$count;
                $row['year_ratio']= sprintf("%.2f", $count/12 * 100);
            }elseif($type =='季报'){
                $count = Daily::where([
                    'type'=>$type,
                    'create_staff_id'=>$s['id'],
                    'createtime'=>['between',[$startYear,$endYear]]
                ])->field("quarter(FROM_UNIXTIME(createtime,'%Y-%m')) as ctime")->group('ctime')->count();
                $row['year']=$count;
                $row['year_ratio']= sprintf("%.2f", $count/4 * 100);
            }else if($type =='年报'){

            }
            foreach ($dates as $d) {
                $row[$d['date']] = 0;
                if ($type == '日报') {
                    $between = date('Y-m-d', strtotime($d['between']));
                    if (strtotime($d['between']) > time()) {
                        $row[$d['date']] = 9;
                    }
                    if (isset($result[$between]) && in_array($s['id'], $result[$between])) {//已提交日报
                        $row[$d['date']] = 1;
                    }
                }else if ($type == '月报') {
                    $between = date('Y-m',strtotime($year.'-'.$d['date']));
                    if (strtotime($between) > time()) {
                        $row[$d['date']] = 9;
                    }
                    if (isset($result[$between]) && in_array($s['id'], $result[$between])) {//已提交日报
                        $row[$d['date']] = 1;
                    }
                }else if ($type == '季报') {
                    $between1 = date('Y-m',strtotime($year.'-'.(($d['date']-1)*3+1)));//第一月
                    $between2 = date('Y-m',strtotime($year.'-'.(($d['date']-1)*3+2)));//第二月
                    $between3 = date('Y-m',strtotime($year.'-'.$d['date']*3));//第三月

                    if (strtotime($between3) > time()) {
                        $row[$d['date']] = 9;
                    }
                    if (isset($result[$between1]) && in_array($s['id'], $result[$between1])) {//
                        $row[$d['date']] = 1;
                    }
                    if (isset($result[$between2]) && in_array($s['id'], $result[$between2])) {//
                        $row[$d['date']] = 1;
                    }
                    if (isset($result[$between3]) && in_array($s['id'], $result[$between3])) {//
                        $row[$d['date']] = 1;
                    }
                }else if ($type == '年报') {
                    $between=$d['date'];
                    if (strtotime($d['date'].'-12-31') > time()) {
                        $row[$d['date']] = 9;
                    }
                    if (isset($result[$between]) && in_array($s['id'], $result[$between])) {//已提交日报
                        $row[$d['date']] = 1;
                    }
                }else if ($type == '周报') {
                    $between=$d['between'];
                    if (strtotime($between[1]) > time()) {
                        $row[$d['date']] = 9;
                    }
                    for ($i=$between[0];strtotime($i)<= strtotime($between[1]);$i=date('Y-m-d',strtotime($i.'+1 day'))){
                        if (isset($result[$i]) && in_array($s['id'], $result[$i])) {//已提交日报
                            $row[$d['date']] = 1;
                        }
                    }
                }
            }
            $list[]=$row;
        }


        $years=[];
        $end=date('Y')+1;
        for ($i=2022;$i<=$end;$i++){
            $years[]=$i;
        }
        $this->view->assign([
            'type' => $type,
            'year' => $year,
            'years' => $years,
            'dates' => $dates,
            'month' => $month,
            'staff_id' => $staff_id,
            'group_id' => $group_id,
            'list' => $list,
        ]);
        return $this->view->fetch();
    }


    /**
     * 沟通日志
     */
    public function record(){
        $row=input('row/a');
        $year=$row['year']??date('Y');
        $month=$row['month']??date('m');
        $group_id=$row['group_id']??0;
        $staff_id=$row['staff_id']??0;
        $where=[];
        $ids=[];
        if ($group_id) {//角色组
            $ids = Staff::getGroupStaffIds($group_id);
        }
        if ($staff_id) {
            $ids = $row['staff_id'];
        }

       $where['createtime'] = ['between', [strtotime("{$year}-{$month}-01"),
                strtotime("{$year}-".($month+1).'-01') - 1]];

        $dates=[];
        $lastDay = date('d', strtotime("{$year}-{$month} +1 month -1 day"));

        for ($i = 1; $i < $lastDay; $i++) {
            $dates[] = ['date' => $i, 'between' => "{$year}-{$month}-{$i}",
                'search' => "{$year}-{$month}-{$i} 00:00:00 - {$year}-{$month}-{$i} 23:59:59"];
        }
        $daily = Record::where($where)
            ->field("FROM_UNIXTIME(createtime,'%Y-%m-%d') as ctime,create_staff_id,count(*) as num")
            ->group('ctime,create_staff_id')->select();
        $daily=collection($daily)->toArray();
        $whereT=[];
        if($group_id || $staff_id){
            $whereT=['id'=>['in',$ids]];
        }
        $staff = Staff::where($whereT)->field('id,name,img')->select();
        $staff=collection($staff)->toArray();
        $result=[];
        foreach ($daily as $d) {
            $result[$d['ctime']][$d['create_staff_id']]=$d['num'];
        }
        $list=[];
        $startYear=strtotime($year.'-01-01');
        $endYear=strtotime($year.'-12-31');
        $startMonth=strtotime($year."-{$month}-01");
        $endMonth=strtotime($year."-{$month}-".$lastDay);
        $allDay=($endYear-$startYear)/86400+1;
        foreach ($staff as $s) {
            $row = $s;

            $count = Record::where([
                'create_staff_id' => $s['id'],
                'createtime' => ['between', [$startYear, $endYear]]
            ])->count();
            $row['year'] = $count;
            $row['year_ratio'] = sprintf("%.2f", $count / $allDay * 100);
            $count = Record::where([
                'create_staff_id' => $s['id'],
                'createtime' => ['between', [$startMonth, $endMonth]]
            ])->count();
            $row['month'] = $count;
            $row['month_ratio'] = sprintf("%.2f", $count / $allDay * 100);

            foreach ($dates as $d) {
                $row[$d['date']] = 0;
                $between = date('Y-m-d', strtotime($d['between']));
                if (strtotime($d['between']) > time()) {
                    $row[$d['date']] = -1;
                }
                if (isset($result[$between]) && isset($result[$between][$s['id']])) {//已提交日报
                    $row[$d['date']] = $result[$between][$s['id']];
                }

            }
            $list[] = $row;
        }


        $years=[];
        $end=date('Y')+1;
        for ($i=2022;$i<=$end;$i++){
            $years[]=$i;
        }
        $this->view->assign([
            'year' => $year,
            'years' => $years,
            'dates' => $dates,
            'month' => $month,
            'staff_id' => $staff_id,
            'group_id' => $group_id,
            'list' => $list,
        ]);
        return $this->view->fetch();
    }
    /**
     * 沟通日志列表
     */
    public function record_list(){
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $whereT=[];
            $staff_id=input('staff_id');
            if($staff_id){
                $whereT['create_staff_id']=$staff_id;
            }
            $list   = Record::where($where)->where($whereT)->with(['staff','customer'])->order($sort, $order)
                ->paginate($limit);
            foreach($list as $k=>$v){
                if(!isset($v['customer']) || !$v['customer']){
                    $v['customer'] = array('id'=>'','name'=>'');
                }
            }
            $row    = $list->items();
            $result = array("total" => $list->total(), "rows" => $row);

            return json($result);
        }

        return $this->view->fetch();
    }
}