manage/app/Services/UserService.php

<?php
/**
 * Created by PhpStorm.
 * User: hanl
 * Date: 2018/6/8
 * Time: 10:13
 */

namespace App\Services;

use App\Models\Apps;
use App\Models\Configs;
use App\Models\Departments;
use App\Models\Users;
use App\Models\Wechats;
use DB;
use Excel;
use App\Common\Api;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Redis;

class UserService
{
    public function __construct($configs = [])
    {

    }

    const USER_UPLOAD_ERROR = 'bsb_user_upload_fail:';

    /**
     * 人员信息上传
     * @param $data
     * @return array
     */
    public static function userUpload($users, $activity_id, $corp_id){
        $data['total']      = count($users);

        //获取主键（phone）重复、号码格式不对的用户的信息集合
        $userError = [];
        $rphones =  collect($users)->groupBy(2)->filter(function ($value, $key) use (&$userError) {
            $value = collect($value)->toArray();
            $preg_str = '/^(1)\d{10}$/';
            if(count($value)>1){
                foreach($value as $k=>$v){
                    $v[3] = "号码重复";
                    $userError[] = $v;
                }
            }else{
                //!empty($value[0][2]) &&
//                if(!empty($value[0][2]) && preg_match($preg_str, $value[0][2]) == 0){
//                    $value[0][3] = "号码格式错误";
//                    $userError[] = $value[0];
//                }
            }

//            return count($value)>1 || preg_match($preg_str, $value[0][2]) == 0;
            return count($value)>1;
        });

        //获取剔除掉重复数据后的正确数据
        $ydata = collect($users)->filter(function ($value, $key) use($rphones, &$userError) {
            if(empty($value[0])){
                $value[3] = "姓名为空";
                $userError[] = $value;
            }
            if(empty($value[2])){
                $value[3] = "电话号码为空";
                $userError[] = $value;
            }
            //剔除条件  名字不为空、电话不为空、电话不重复
            return !empty($value[0])&&!empty($value[2])&&!collect($rphones)->get($value[2]);
        });

        //错误数据并存入redis
        Redis::set(self::USER_UPLOAD_ERROR.$activity_id, json_encode($userError), 'EX', 3600);

        //获取用户信息和部门信息（避免多次连接数据库）
        $departments = Departments::where('activity_id', $activity_id)->where('corp_id', $corp_id)->where('status', 1)->pluck('department_id', 'department_name')->toArray();
        $users = Users::where('activity_id', $activity_id)->where('corp_id', $corp_id)->pluck('department_id', 'guid')->toArray();

        $update_num = 0;
        $user_query = [];
        //正常数据入库
        foreach($ydata as $k=>$v){
            $name               = Api::trimAll($v[0]);
            $department_name    = Api::trimAll($v[1]);
            $phone              = Api::trimAll($v[2]);

            //部门信息处理
            if(empty($department_name)){
                $department_id = '';
            } else {
                $is_department = array_key_exists($department_name, $departments);
                if (!$is_department) {
                    $department_query['department_name']    = $department_name;
                    $department_query['activity_id']        = $activity_id;
                    $department_query['corp_id']            = $corp_id;
                    $department_query['update_time']        = time();
                    $department_query['create_time']        = time();

                    $department_id = Departments::insertGetId($department_query);
                    if(!$department_id){
                        return Api::arr(config('code.sql_error'), trans('msg.sql_error'));
                    }
                    $departments[$department_name] = $department_id;                //部门信息重新赋值
                } else {
                    $department_id = $departments[$department_name];
                }
            }

            //用户信息处理
            $query = [];
            $is_user = array_key_exists($phone, $users);
            if($is_user){           //该guid已经在库里存在
                $update_num += 1;

                //更新用户信息
                $query['department_id'] = $department_id;
                $query['name']          = $name;
                $query['status']        = 1;                //保证导入的用户状态为有效，如果库内的用户以无效，也改为有效
                $query['update_time']   = time();
                $res = Users::where('activity_id', $activity_id)->where('corp_id', $corp_id)->where('guid', $phone)->update($query);
                if(!$res){
                    return Api::arr(config('code.sql_error'), trans('msg.sql_error'));
                }

            } else {                                            //新的用户
                $query['activity_id']   = $activity_id;
                $query['corp_id']       = $corp_id;
                $query['guid']          = $phone;
                $query['name']          = $name;
                $query['department_id'] = $department_id;
                $query['phone']         = $phone;
                $query['update_time']   = time();
                $query['create_time']   = time();
                $user_query[] = $query;
            }
            $users[$phone] = $department_id;                                    //人员信息重新赋值
        }

        //将数据分组运行到mysql，增加效率
        if(!empty($user_query)) {
            $chunks = collect($user_query)->chunk(1000);
            $chunks->toArray();
            foreach ($chunks as $chunk) {
                $values = collect($chunk)->map(function ($item, $key) {
                        return "('".collect($item)->implode("','")."')";
                    });

                DB::select("insert into bs_users(`activity_id`,`corp_id`,`guid`,`name`,`department_id`,`phone`,`update_time`,`create_time`)VALUES".collect($values)->implode(",").";");
            }
        }

        //遍历统计部门人数，为O的部门则软删除
        foreach($departments as $department_id){
            $userCount = Users::where('department_id', $department_id)->where('status', 1)->count();
            if($userCount <= 0 ){
                Departments::where('department_id', $department_id)->update(['status'=>0]);
            }
        }

        //统计导入情况
        $data['uploadSuccess']  = count($ydata);
        $data['uploadFail']     = $data['total'] - $data['uploadSuccess'];
        $data['insertNum']      = count($user_query);
        $data['updateNum']      = $data['uploadSuccess']-$data['insertNum'];

        $userAll = collect($ydata)->groupBy(1);
        foreach($userAll as $key=>$value){
            if(empty($key)){
                $data['departmentCount']['其他'] = count($value);
            } else {
                $data['departmentCount'][$key] = count($value);
            }
        }

        //清除人员redis缓存
        $redisUsersKeys = Redis::keys('bs_users:*');
        $dbUserIds = Users::where('activity_id', $activity_id)->pluck('user_id');
        foreach ($redisUsersKeys as $redisUsersKey) {
            $keys = explode(':', $redisUsersKey);
            if(in_array($keys[1], collect($dbUserIds)->toArray())){
                Redis::del('bs_users:'.$keys[1]);
            }
        }

        //排行榜缓存清理
        Redis::del('bs_top:users:km:'.$activity_id);
        Redis::del('bs_top:users:pk:'.$activity_id);
        Redis::del('bs_top:users:'.$activity_id);

        return Api::arr(config('code.success'), trans('msg.success'), $data);
    }

    /**
     * 删除人员（软删除）
     * 2018-06-20 18:00:00
     * @param $params
     * @return array
     */
    public static function delUser($params){
        $userInfo = Users::where('activity_id', $params['activity_id'])->where('corp_id', $params['corp_id'])->where('user_id', $params['user_id'])->first();
        if(!$userInfo){
            return Api::arr(config('code.user_not_exist'), trans('msg.user_not_exist'));
        }

        if(!empty($userInfo['open_id'])) {
            Wechats::where('open_id',$userInfo['open_id']) -> delete();
        }

        $res = Users::where('activity_id', $params['activity_id'])->where('corp_id', $params['corp_id'])->where('user_id', $params['user_id'])->update(['status'=>0]);

        if($res){
            $user_num = Users::where('activity_id', $params['activity_id'])->where('status', 1)->count();
            if($user_num <= 0){
                Configs::where('activity_id', $params['activity_id'])->update(['is_user_upload'=>0]);
            }
            //清除人员缓存
            Redis::del('bs_users:'.$params['user_id']);
            Redis::zrem('bs_top:users:km:'.$params['activity_id'], $params['user_id']);
            Redis::zrem('bs_top:users:pk:'.$params['activity_id'], $params['user_id']);
            Redis::hdel('bs_top:users:'.$params['activity_id'], $params['user_id']);
            return Api::arr(config('code.success'), trans('msg.success'));
        } else {
            return Api::arr(config('code.sql_error'), trans('msg.sql_error'));
        }
    }

    /**
     * 下载上传失败的人员数据
     * @param $data
     */
    public static function downloadUserExcel($data){
        $cellData = [
            ['姓名', '部门', '手机号码', '错误原因'],
            ['基本信息（红色为必填）'],
            ['填写规则'],
        ];

        foreach($cellData as $k=>$v){
            array_unshift($data, $v);
        }

        Excel::create('人员导入错误数据'.date('mdHi'),function($excel) use ($data){
            $excel->sheet('人员信息', function($sheet) use ($data){

                $tot = count($data) ;
                $sheet->setWidth(array(
                    'A'     =>  25,
                    'B'     =>  25,
                    'C'     =>  25,
                    'D'     =>  25,
                ))->rows($data)->setFontSize(12);

                //设置第一行的表格格式
                $sheet->setStyle([
                    'font' => [
                        'size' => 12,
                        'bold' => false,
                    ]
                ])
                    ->mergeCells('A1:D1')       //合并单元格
                    ->mergeCells('A2:D2')
                    ->setHeight(1, 100)         //设置行高
                    ->setHeight(2, 30)
                    ->setHeight(3, 30)
                    ->cells('A1:D1', function($cells){          //设置单元格格式
                        $cells->setValignment('center');         //文本对齐方式
                        $cells->setFontWeight('bold');           //粗体
                    })
                    ->cells('A2:D2', function($cells){
                        $cells->setValignment('center');
                        $cells->setFontWeight('bold');
                        $cells->setFontSize(10);
                        $cells->setAlignment('center');
                    })
                    ->cells('A3:D3', function($cells){
                        $cells->setValignment('center');
                        $cells->setBackground('#92D050');
                        $cells->setFontWeight('bold');
                        $cells->setFontColor('#DD0806');
                        $cells->setAlignment('center');
                    })
                    ->cells('A2:D'.$tot, function($cells) {
                        $cells->setAlignment('center');
                    })
                    ->cells('B3', function($cells) {
                        $cells->setFontColor('#000000');
                    })
                    ->cells('D3', function($cells) {
                        $cells->setFontColor('#000000');
                    })
                    ->cells('D4:D'.$tot, function($cells) {
                        $cells->setFontColor('#ff0000');
                        $cells->setFontWeight('bold');
                    });
            });
        })->export('xls');
    }

    /**
     * 新增单个人员
     * @param $params
     * @return array
     */
    public static function addUser($params){
        $activity_info = ActivityService::getActivityInfo($params['activity_id']);
        if(!$activity_info){
            return Api::arr(config('code.activity_not_exist'), trans('msg.activity_not_exist'));
        }

        $query = [];
        if(isset($params['department_name']) && !empty($params['department_name'])) {
            $department_info = Departments::where('department_name', $params['department_name'])->where('activity_id', $params['activity_id'])->first();
            if ($department_info) {
                $query['department_id'] = $department_info['department_id'];
            } else {
                $department_query['department_name'] = $params['department_name'];
                $department_query['activity_id'] = $params['activity_id'];
                $department_query['corp_id'] = $params['corp_id'];
                $department_query['create_time'] = time();
                $department_query['update_time'] = time();
                $query['department_id'] = Departments::insertGetId($department_query);
            }
        } else {
            $query['department_id'] = 0;
        }

        $query['activity_id']   = $params['activity_id'];
        $query['corp_id']       = $params['corp_id'];
        $query['name']          = $params['name'];
        $query['guid']          = Api::trimAll($params['phone']);
        $query['phone']         = Api::trimAll($params['phone']);
        $query['update_time']   = time();
        $query['status']        = 1;
        $query['open_id']       = null;
        $query['nickname']      = null;

        $user_info = Users::where('guid', Api::trimAll($params['phone']))->where('activity_id', $params['activity_id'])->orderByDesc('status')->first();
        if($user_info){
            $res = Users::where('user_id', $user_info['user_id'])->update($query);

            //排行榜缓存清理
            Redis::del('bs_top:users:km:'.$params['activity_id']);
            Redis::del('bs_top:users:pk:'.$params['activity_id']);
            Redis::del('bs_top:users:'.$params['activity_id']);
        } else {
            $query['create_time']   = time();
            $res = Users::insert($query);
        }
        if($res){
            Configs::where('activity_id', $params['activity_id'])->update(['is_user_upload'=>1]);
            return Api::arr(config('code.success'), trans('msg.success'));
        } else {
            return Api::arr(config('code.fail'), trans('msg.sql_error'));
        }
    }

    /**
     * 修改人员信息
     * @param $params
     * @return array
     */
    public static function ModifyUser($params){
        $query = [];
        $query['name'] = $params['name'];
        $query['guid'] = $params['phone'];
        $query['phone'] = $params['phone'];
        $query['update_time'] = time();
        $isRepeatUser = Users::where('user_id', '!=', $params['user_id'])->where('activity_id', $params['activity_id'])->where('guid', $params['phone'])->first();
        if($isRepeatUser){
            return Api::arr(config('code.fail'), '号码：'.$params['phone'].'已存在，请重新修改！');
        }

        //部门信息处理
        if(isset($params['department_name']) && !empty($params['department_name'])) {
            $department_info = Departments::where('department_name', $params['department_name'])->where('activity_id', $params['activity_id'])->first();
            if ($department_info) {
                $query['department_id'] = $department_info['department_id'];
            } else {
                $department_query['department_name'] = $params['department_name'];
                $department_query['activity_id'] = $params['activity_id'];
                $department_query['corp_id'] = $params['corp_id'];
                $department_query['create_time'] = time();
                $department_query['update_time'] = time();
                $query['department_id'] = Departments::insertGetId($department_query);
            }
        } else {
            $query['department_id'] = 0;
        }

//        return Api::arr(config('code.success'), trans('msg.success'), $query);

        $res = Users::where('user_id', $params['user_id'])->update($query);
        if($res){
            return Api::arr(config('code.success'), trans('msg.success'));
        } else {
            return Api::arr(config('code.fail'), trans('msg.sql_error'));
        }
    }
}
?>