<?php
namespace app\admin\service\consultant;
use app\model\Consultant;
use app\model\MarketCustomer;
use app\model\MarketCustomerFollow;
use app\model\MarketCustomerLogs;
use app\model\SysDept;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use support\Db;
use support\exception\BusinessException;
use support\Request;
use Tinywan\Jwt\JwtToken;
class CustomService
{
const DIFF_TIME = (60 * 60 * 24 * 30); //调入团队公池 x4 = 调入集团公池
/**
* Notes: 返回选项配置信息
* User: yb
* Date: 2024/8/5
* Time: 16:05
* @return array
*/
public static function config()
{
return MarketCustomer::config();
}
public static function commonSearch($params)
{
$where = [];
$currentTime = time();
$diffNums = self::DIFF_TIME;
$deptIds = TeamService::getIdsByUser();
$belongStatus = $params['belong_status'] ?? 1;
if (false === $deptIds) {
//无权限
$where[] = ['dept_id', '=', 0];
} else if (is_array($deptIds)) {
//指定团队下的权限
if (!empty($params['dept_id'])) {
$deptId = end($params['dept_id']);
$deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
}
$where[] = [function($query) use ($deptIds) {
$query->whereIn('dept_id', $deptIds);
}];
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("check_status IN (-1, 1)")
->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)")
->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}];
} else {
if (!empty($params['dept_id'])) {
$deptId = end($params['dept_id']);
$deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
$where[] = [function($query) use ($deptIds) {
$query->whereIn('dept_id', $deptIds);
}];
}
}
if (!empty($params['name'])) {
$keywords = $params['name'];
$where[] = [function($query) use ($keywords) {
$query->orWhere('name', 'like', "%{$keywords}%")->orWhere('mobile', 'like', "%{$keywords}%");
}];
}
if (!empty($params['mobile'])) {
$mobile = $params['mobile'];
$where[] = [function($query) use ($mobile) {
$query->where('mobile', 'like', "%{$mobile}%");
}];
}
if (!empty($params['consultant_name'])) {
$consultantIds = Consultant::orWhere('name', 'like', "%{$params['consultant_name']}%")
->orWhere('mobile', 'like', "%{$params['consultant_name']}%")
->pluck('id')->toArray();
$where[] = [function($query) use ($consultantIds) {
$query->whereIn('consultant_id', $consultantIds);
}];
}
if (!empty($params['report_name'])) {
$consultantIds = Consultant::orWhere('name', 'like', "%{$params['report_name']}%")
->orWhere('mobile', 'like', "%{$params['report_name']}%")
->pluck('id')->toArray();
$where[] = [function($query) use ($consultantIds) {
$query->whereIn('report_consultant_id', $consultantIds);
}];
}
if (!empty($params['type'])) {
$where[] = ['type', '=', $params['type']];
}
if (!empty($params['visit_time'])) {
$datetime = $params['visit_time'];
$datetime[0] = strtotime($datetime[0].' 00:00:00');
$datetime[1] = strtotime($datetime[1].' 23:59:59');
$where[] = [function($query) use ($datetime) {
$query->whereBetween('visit_time', $datetime);
}];
}
if (!empty($params['visit_date'])) {
$visitDate = $params['visit_date'];
$visitDate[0] = strtotime($visitDate[0].' 00:00:00');
$visitDate[1] = strtotime($visitDate[1].' 23:59:59');
$where[] = [function($query) use ($visitDate) {
$query->whereBetween('visit_time', $visitDate);
}];
}
if (!empty($params['created_date'])) {
$createdDate = $params['created_date'];
$createdDate[0] = strtotime($createdDate[0].' 00:00:00');
$createdDate[1] = strtotime($createdDate[1].' 23:59:59');
$where[] = [function($query) use ($createdDate) {
$query->whereBetween('created_at', $createdDate);
}];
}
if (!empty($params['check_date'])) {
$checkDate = $params['check_date'];
$checkDate[0] = strtotime($checkDate[0].' 00:00:00');
$checkDate[1] = strtotime($checkDate[1].' 23:59:59');
$where[] = [function($query) use ($checkDate) {
$query->whereBetween('check_time', $checkDate)->where('current_status', '>', 1);
}];
}
if (!empty($params['current_status'])) {
$currentStatus = $params['current_status'];
if ($currentStatus == -2) {
//检索公池状态
$where[] = [function($query) use ($diffNums, $currentTime){
$query->whereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)");
}];
} else {
$where[] = ['current_status', '=', $currentStatus];
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("check_status IN (-1, 1)")
->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)");
}];
}
}
if (!empty($params['consultant_id'])) {
$consultantId = $params['consultant_id'];
$where[] = [function($query) use ($consultantId) {
$query->orWhere('consultant_id', $consultantId)->orWhere('report_consultant_id', $consultantId);
}];
}
if (!empty($params['custom'])) {
$keywords = $params['custom'];
$where[] = [function($query) use ($keywords) {
$query->orWhere('name', 'like', "%{$keywords}%")->orWhere('mobile', 'like', "%{$keywords}%");
}];
}
if (!empty($params['check_status'])) {
$where[] = ['check_status', '=', $params['check_status']];
}
if (!empty($params['stat_report_status'])) {
//已报备 1
$statReportStatus = $params['stat_report_status'];
if ($statReportStatus == 1) {
$where[] = [function($query) use ($diffNums, $currentTime) {
$query->whereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0)")->where('current_status', '=', 1)->where('check_status', '=', 2);
}];
}
//已锁定 2
if ($statReportStatus == 2) {
$where[] = [function($query) {
$query->whereIn('current_status', [2,3,4])->where('check_status', '=', 2);
}];
}
//已失效 -1
if ($statReportStatus == -1) {
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhere('current_status', '=', -1)->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND current_status = 1 AND check_status = 2)")->orWhere('check_status', '=', -1);
}];
}
}
if (isset($params['require_area'])) {
if (is_numeric($params['require_area'])) {
$where[] = ['require_area', '=', ($params['require_area'] + 1)];
}
}
if (isset($params['requirement'])) {
if (is_numeric($params['requirement'])) {
$where[] = ['requirement', '=', ($params['requirement'] + 1)];
}
}
if (isset($params['age_range'])) {
if (is_numeric($params['age_range'])) {
$where[] = ['age_range', '=', ($params['age_range'] + 1)];
}
}
if (isset($params['visit_type'])) {
if (is_numeric($params['visit_type'])) {
$where[] = ['visit_type', '=', $params['visit_type']];
}
}
if (isset($params['region'])) {
if (is_numeric($params['region'])) {
$where[] = ['region', '=', ($params['region'] + 1)];
}
}
if (isset($params['purpose'])) {
if (is_numeric($params['purpose'])) {
$where[] = ['purpose', '=', ($params['purpose'] + 1)];
}
}
if (isset($params['level'])) {
if (is_numeric($params['level'])) {
$where[] = ['level', '=', ($params['level'] + 1)];
}
}
if (!empty($params['belong_status'])) {
$belongStatus = $params['belong_status'];
if ($belongStatus == 1) {
//未流入公池
$where[] = ['belong_status', '=', 1];
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("check_status IN (-1, 1)")
->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)");
}];
} else {
if (true === $deptIds) {
if (!empty($params['pub_status'])) {
if ($params['pub_status'] == 2) {
//仅检索团队公池
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}];
} else {
//仅检索集团公池
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("((visit_time + ({$diffNums} * 4)) - {$currentTime} <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}];
}
} else {
//超管权限
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}];
}
} else if (is_array($deptIds)) {
//团队权限
$where[] = [function($query) use ($diffNums, $currentTime){
$query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}];
} else {
//无权限
}
}
}
return $where;
}
/**
* Notes: 列表
* User: yb
* Date: 2024/8/6
* Time: 15:07
* @param Request $request
*/
public static function index(Request $request)
{
$format = $request->get('format', 'normal');
$limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
$limit = $limit <= 0 ? 10 : $limit;
$params = $request->get();
$page = (int)$request->get('page');
$page = $page > 0 ? $page : 1;
$currentTime = time();
$diffNums = self::DIFF_TIME;
$where = self::commonSearch($params);
$paginator = MarketCustomer::where($where)->orderBy('visit_time', 'desc')->paginate($limit, '*', 'page', $page);
$total = $paginator->total();
$items = $paginator->items();
if (!empty($items)) {
//查询顾问信息
$consultantKeys = [];
$reportConsultantIds = $paginator->pluck('report_consultant_id')->toArray();
$consultantIds = $paginator->pluck('consultant_id')->toArray();
if (!empty($consultantIds) || !empty($reportConsultantIds)) {
if (!empty($consultantIds)) {
//去重
$consultantIds = array_unique($consultantIds);
//排序
$consultantIds = array_values($consultantIds);
}
if (!empty($reportConsultantIds)) {
//去重
$reportConsultantIds = array_unique($reportConsultantIds);
//排序
$reportConsultantIds = array_values($reportConsultantIds);
}
$consultantIds = array_merge($consultantIds, $reportConsultantIds);
$consultantList = Consultant::whereIn('id', $consultantIds)->select(['id', 'name', 'mobile'])->get();
if (!$consultantList->isEmpty()) {
foreach ($consultantList->toArray() as $consultantItem) {
$consultantKeys[$consultantItem['id']] = $consultantItem;
}
}
}
$teamKeys = TeamService::getItemKeys();
foreach ($items as &$item) {
$visitTime = $item->visit_time;
$visitTimeStamp = strtotime($visitTime);
$diff = ($visitTimeStamp + $diffNums) - $currentTime;
if ($diff > 0) {
$item->belong_status = 1;
} else {
if (abs($diff) - (4*$diffNums) > 0) {
$item->belong_status = 3;
} else {
$item->belong_status = 2;
}
}
$item->diff_nums = ($diff > 0) ? $diff : 0;
$item->consultant_name = $consultantKeys[$item->consultant_id]['name'] ?? '';
$item->consultant_mobile = $consultantKeys[$item->consultant_id]['mobile'] ?? '';
$item->report_consultant_name = $consultantKeys[$item->report_consultant_id]['name'] ?? '';
$item->report_consultant_mobile = $consultantKeys[$item->report_consultant_id]['mobile'] ?? '';
$item->team_name = TeamService::getTeamName($teamKeys, $item->dept_id);
$item->mask_mobile = self::handlePhone($item->mobile);
}
}
$data = [
'total' => $total,
'rows' => $items
];
return json_success('success', $data);
}
/**
* Notes: 添加客户
* User: yb
* Date: 2024/8/6
* Time: 11:20
*/
public static function add($params)
{
$params = MarketCustomer::handleNumParams($params);
$mobile = $params['mobile'];
//查询客户手机号是否已经存在
if (MarketCustomer::checkCustomExists($mobile)) {
return json_fail('客户已经登记过了');
}
//查询顾问信息
$reportConsultantId = $params['report_consultant_id'];
$consultantInfo = Consultant::firstWhere(['id' => $reportConsultantId]);
if (empty($consultantInfo)) {
return json_fail('报备顾问信息不存在');
}
$deptId = $consultantInfo->dept_id;
$insertData = [
'name' => $params['name'],
'mobile' => $mobile,
'consultant_id' => 0,
'create_consultant_id' => $reportConsultantId,
'report_consultant_id' => $reportConsultantId,
'dept_id' => $deptId,
'gender' => $params['gender'] ?? null,
'visit_type' => $params['visit_type'] ?? null,
'require_area' => $params['require_area'] ?? null,
'area' => $params['area'] ?? null,
'requirement' => $params['requirement'] ?? null,
'age_range' => $params['age_range'] ?? null,
'focus' => $params['focus'] ?? null,
'region' => $params['region'] ?? null,
'purpose' => $params['purpose'] ?? null,
'level' => $params['level'] ?? null,
'type' => $params['type'] ?? null,
'check_status' => 1,
'visit_time' => $params['visit_time'] ?? null,
'note' => $params['note'] ?? '',
'belong_status' => 1,
'current_status' => $params['current_status'] ?? null,
'created_at' => time()
];
Db::beginTransaction();
try {
//查询会员表中是否存在该客户
// $memberId = Member::where('member_mobile', $insertData['mobile'])->value('member_id');
// if (empty($memberId)) {
// $result = MemberService::add([
// 'account_name'=> $insertData['name'],
// 'member_category' => '售房客户',
// 'mobile' => $insertData['mobile'],
// 'source' => 'HOUSE']);
// $code = $result->getStatusCode();
// if ($code == 200) {
// $content = $result->rawBody();
// if (is_json($content)) {
// $content = json_decode($content, 1);
// if ($content['code'] == 200) {
// $memberId = $content['data']['member_id'];
// } else {
// throw new BusinessException('新增会员失败');
// }
// } else {
// throw new BusinessException('新增会员失败');
// }
// } else {
// throw new BusinessException('新增会员失败');
// }
// }
// $insertData['member_id'] = $memberId;
$customId = MarketCustomer::insertGetId($insertData);
Db::commit();
}catch (BusinessException|\Exception $e){
Db::rollBack();
return json_fail($e->getMessage());
}
if ($customId) {
return json_success('添加成功');
} else {
return json_fail('添加失败');
}
}
/**
* Notes: 编辑客户
* User: yb
* Date: 2024/8/6
* Time: 16:53
* @param $params
*/
public static function update($params)
{
$params = MarketCustomer::handleNumParams($params);
$mobile = $params['mobile'];
//查询客户手机号是否已经存在
if (MarketCustomer::checkCustomExists($mobile, $params['id'])) {
return json_fail('客户已经登记过了');
}
$updateData = [
'name' => $params['name'],
'mobile' => $params['mobile'],
'gender' => $params['gender'] ?? null,
'visit_type' => $params['visit_type'] ?? null,
'require_area' => $params['require_area'] ?? null,
'area' => $params['area'] ?? null,
'requirement' => $params['requirement'] ?? null,
'age_range' => $params['age_range'] ?? null,
'focus' => $params['focus'] ?? null,
'region' => $params['region'] ?? null,
'purpose' => $params['purpose'] ?? null,
'level' => $params['level'] ?? null,
'type' => $params['type'] ?? null,
'note' => $params['note'] ?? '',
'current_status' => $params['current_status'] ?? null,
'updated_at' => time()
];
if (!empty($params['update_visit_time'])) {
if ($params['update_visit_time'] == 1) {
$updateData['visit_time'] = $params['visit_time'] ?? null;
}
}
$result = MarketCustomer::where('id', $params['id'])->update($updateData);
if ($result) {
return json_success('编辑成功');
} else {
return json_fail('编辑失败');
}
}
/**
* Notes: 删除客户
* User: yb
* Date: 2024/8/2
* Time: 13:33
* @param $ids
* @return \support\Response
*/
public static function delete($ids)
{
if (!$ids) {
return json_fail("数据错误");
}
if (!is_array($ids)) {
$ids = [$ids];
}
Db::beginTransaction();
try {
if (is_array($ids)) {
MarketCustomer::whereIn('id', $ids)->delete();
MarketCustomerFollow::whereIn('market_customer_id', $ids)->delete();//删除跟进记录
MarketCustomerLogs::whereIn('market_customer_id', $ids)->delete();//删除移交记录
} else {
MarketCustomer::where('id', $ids)->delete();
MarketCustomerFollow::where('market_customer_id', $ids)->delete();//删除跟进记录
MarketCustomerLogs::where('market_customer_id', $ids)->delete();//删除移交记录
}
Db::commit();
} catch (\Exception $e) {
Db::rollBack();
return json_fail('删除失败');
}
return json_success('删除成功');
}
/**
* Notes: 跟进记录
* User: yb
* Date: 2024/8/7
* Time: 9:00
* @param Request $request
*/
public static function follow(Request $request)
{
$format = $request->get('format', 'normal');
$limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
$limit = $limit <= 0 ? 10 : $limit;
$params = $request->get();
$page = (int)$request->get('page');
$page = $page > 0 ? $page : 1;
$where = [];
$whereCustom = [];
$whereConsultant = [];
if (!empty($params['market_customer_id'])) {
$marketCustomerIds = $params['market_customer_id'];
$where[] = [function($query) use ($marketCustomerIds) {
$query->whereIn('market_customer_id', $marketCustomerIds);
}];
}
if (!empty($params['consultant_id'])) {
$consultantId = $params['consultant_id'];
$where[] = [function($query) use ($consultantId) {
$query->whereIn('consultant_id', $consultantId);
}];
}
if (!empty($params['follow_way'])) {
$where[] = ['follow_way', '=', $params['follow_way']];
}
if (!empty($params['custom'])) {
$custom = $params['custom'];
$whereCustom[] = [function($query) use ($custom) {
$query->orWhere('name', 'like', "%{$custom}%")->orWhere('mobile', 'like', "%{$custom}%");
}];
}
if (!empty($params['consultant'])) {
$consultant = $params['consultant'];
$whereConsultant[] = [function($query) use ($consultant) {
$query->orWhere('name', 'like', "%{$consultant}%")->orWhere('mobile', 'like', "%{$consultant}%");
}];
}
if (!empty($params['follow_time'])) {
$datetime = $params['follow_time'];
$datetime[0] = strtotime($datetime[0].' 00:00:00');
$datetime[1] = strtotime($datetime[1].' 23:59:59');
$where[] = [function($query) use ($datetime) {
$query->whereBetween('follow_time', $datetime);
}];
}
$paginator = MarketCustomerFollow::with(['custom', 'consultant'])->whereHas('custom', function($query) use ($whereCustom){
$query->where($whereCustom);
})->whereHas('consultant', function($query) use ($whereConsultant) {
$query->where($whereConsultant);
})->where($where)->orderBy('follow_time', 'desc')->paginate($limit, '*', 'page', $page);
$total = $paginator->total();
$items = $paginator->items();
foreach ($items as &$item) {
if (!empty($item->custom)) {
$item->custom->mask_mobile = self::handlePhone($item->custom->mobile);
}
}
$data = [
'total' => $total,
'rows' => $items
];
return json_success('success', $data);
}
/**
* Notes: 删除跟进记录
* User: yb
* Date: 2024/8/7
* Time: 11:25
* @param $ids
*/
public static function deleteFollow($ids)
{
if (!$ids) {
return json_fail("数据错误");
}
if (!is_array($ids)) {
$ids = [$ids];
}
try {
if (is_array($ids)) {
MarketCustomerFollow::whereIn('id', $ids)->delete();
} else {
MarketCustomerFollow::where('id', $ids)->delete();
}
} catch (\Exception $e) {
return json_fail('删除失败');
}
return json_success('删除成功');
}
/**
* Notes: 转移客户
* User: yb
* Date: 2024/8/7
* Time: 14:27
* @param $params
*/
public static function moveCustom($params)
{
$userId = JwtToken::getCurrentId();
$moveType = $params['move_type'] ?? 0;
if ($moveType == 1) {
$currentDeptId = $params['dept_id'];
$currentConsultantId = 0;
} else {
$currentConsultantId = $params['consultant_id'] ?? 0; //当前顾问
$consultantInfo = Consultant::firstWhere(['id' => $currentConsultantId]);
if (empty($consultantInfo)) {
return json_fail('顾问不存在');
}
$currentDeptId = $consultantInfo->dept_id; //当前部门
}
$consultantIds = $params['move_consultant_id'] ?? [];
$customIds = $params['move_market_customer_id'] ?? [];
$note = $params['note'] ?? '';
$logsInertData = [];
$now = time();
$whereCustom = [];
if (!empty($customIds)) {
$whereCustom[] = [function($query) use ($customIds) {
$query->whereIn('id', $customIds);
}];
}
if (!empty($consultantIds)) {
$whereCustom[] = [function($query) use ($consultantIds) {
$query->whereIn('consultant_id', $consultantIds);
}];
}
$customList = MarketCustomer::where($whereCustom)->select(['id','consultant_id','dept_id','visit_time','check_status','current_status'])->get();
if (!$customList->isEmpty()) {
foreach ($customList as$item) {
$logsInertData[] = [
'market_customer_id' => $item->id,
'consultant_id' => $currentConsultantId,
'dept_id' => $currentDeptId,
'before_consultant_id' => $item->consultant_id,
'before_dept_id' => $item->dept_id,
'note' => $note,
'change_user_id' => $userId,
'created_at' => $now
];
}
}
if ($customList->isEmpty()) {
return json_fail('移交的客户信息不存在');
}
if (empty($logsInertData)) {
return json_fail('移交记录不能为空');
}
$diffTime = self::DIFF_TIME;
Db::beginTransaction();
$result = false;
try {
foreach ($customList as $item) {
if ($currentConsultantId != 0) {
//移交至指定顾问
//判断一下是否到了到访的过期时间，如果过期了就更新到访时间，没过期时间就不变
if ($item->check_status == 2 && $item->current_status == 2) {
$visitTime = $item->visit_time;
$diff = strtotime($visitTime) + $diffTime - time();
if ($diff <= 0) {
$item->visit_time = time();
}
}
} else {
//移交至其他团队
$item->visit_time = time() - self::DIFF_TIME;
}
$item->consultant_id = $currentConsultantId;
$item->dept_id = $currentDeptId;
$item->save();
}
$result = MarketCustomerLogs::insert($logsInertData);
Db::commit();
}catch (BusinessException|\Exception $e) {
Db::rollBack();
return json_fail($e->getMessage());
}
if ($result) {
return json_success('移交成功');
} else {
return json_fail('移交失败');
}
}
/**
* Notes: 转移记录
* User: yb
* Date: 2024/8/7
* Time: 15:33
* @param Request $request
*/
public static function moveLogs(Request $request)
{
$format = $request->get('format', 'normal');
$limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
$limit = $limit <= 0 ? 10 : $limit;
$params = $request->get();
$page = (int)$request->get('page');
$page = $page > 0 ? $page : 1;
$platform = 0;
$where = [];
$whereCustom = [];
$whereCurrent = [];
$whereBefore = [];
$whereOpBack = [];
$whereOpFront = [];
if (!empty($params['market_customer_id'])) {
$where[] = ['market_customer_id', '=', $params['market_customer_id']];
}
if (!empty($params['consultant_id'])) {
$consultantIds = $params['consultant_id'];
$where[] = [function($query) use ($consultantIds) {
$query->orWhereIn('consultant_id', $consultantIds)->orWhereIn('before_consultant_id', $consultantIds);
}];
}
if (!empty($params['custom'])) {
//客户信息
$custom = $params['custom'];
$whereCustom[] = ['name', 'like', "%{$custom}%"];
}
if (!empty($params['current_consultant'])) {
//当前员工信息
$current = $params['current_consultant'];
$whereCurrent[] = ['name', 'like', "%{$current}%"];
}
if (!empty($params['before_consultant'])) {
//转移前员工信息
$before = $params['before_consultant'];
$whereBefore[] = ['name', 'like', "%{$before}%"];
}
if (!empty($params['platform'])) {
$platform = $params['platform'];
if ($platform == 1) {
//后台
$where[] = ['change_user_id', '<>', null];
$where[] = ['change_user_id', '=', null];
} else {
//小程序
$where[] = ['change_user_id', '=', null];
$where[] = ['change_user_id', '<>', null];
}
}
if (!empty($params['op_name'])) {
$opName = $params['op_name'];
if (!empty($platform)) {
if ($platform == 1) {
$whereOpBack[] = ['user_name', 'like', "%{$opName}%"];
} else {
$whereOpFront[] = ['name', 'like', "%{$opName}%"];
}
} else {
$whereOpBack[] = [function($query) use ($opName) {
$query->orWhere('user_name', 'like', "%{$opName}%");
}];
$whereOpFront[] = [function($query) use ($opName) {
$query->orWhere('name', 'like', "%{$opName}%");
}];
}
}
if (!empty($params['created_at'])) {
$createdDate = $params['created_at'];
$createdDate[0] = strtotime($createdDate[0].' 00:00:00');
$createdDate[1] = strtotime($createdDate[1].' 23:59:59');
$where[] = [function($query) use ($createdDate) {
$query->whereBetween('created_at', $createdDate);
}];
}
$paginator = MarketCustomerLogs::with(['custom' => function($query) {
$query->select('id', 'name', 'mobile');
}, 'beforeMan' => function($query) {
$query->select('id', 'name', 'mobile');
}, 'currentMan' => function($query) {
$query->select('id', 'name', 'mobile');
}, 'beforeDept' => function($query) {
$query->select('dept_id', 'dept_name');
}, 'currentDept' => function($query) {
$query->select('dept_id', 'dept_name');
}])
// ->whereHas('custom', function ($query) use ($whereCustom) {
// $query->where($whereCustom);
// })
// ->whereHas('currentMan', function ($query) use ($whereCurrent) {
// $query->where($whereCurrent);
// })
// ->whereHas('beforeMan', function ($query) use ($whereBefore) {
// $query->where($whereBefore);
// })
->where($where)
->orderBy('created_at', 'desc')
->paginate($limit, '*', 'page', $page);
$total = $paginator->total();
$items = $paginator->items();
foreach ($items as &$item) {
if (!empty($item->custom)) {
$item->custom->mask_mobile = self::handlePhone($item->custom->mobile);
}
}
$data = [
'total' => $total,
'rows' => $items
];
return json_success('success', $data);
}
/**
* Notes: 审核客户
* User: yb
* Date: 2024/8/16
* Time: 11:21
* @param $params
*/
public static function checkCustom($params)
{
$adminId = JwtToken::getCurrentId();
//查询客户是否已经存在
$customId = $params['id'];
$checkStatus = $params['check_status'];
if (!in_array($checkStatus, [-1,2])) {
return json_fail('状态值非法');
}
$info = MarketCustomer::firstWhere(['id' => $customId]);
if (empty($info)) {
return json_fail('客户不存在');
}
$status = $info->check_status;
if ($status != 1) {
return json_fail('客户不是待转到访状态');
}
$mobile = $info->mobile;
if ($checkStatus == 2) {
if (MarketCustomer::checkCustomExists($mobile)) {
return json_fail('客户已经存在');
}
}
//查询顾问的团队信息
$deptId = Consultant::where('id', $params['consultant_id'])->value('dept_id');
$result = false;
Db::beginTransaction();
try {
$info->check_status = $checkStatus;
$info->current_status = $checkStatus;
if ($checkStatus == -1) {
//拒绝录入拒绝理由
$info->check_note = $params['note'] ?? '无拒绝理由';
}
if ($checkStatus == 2) {
//更新到访保护期时间
$info->visit_time = time();
}
$info->dept_id = $deptId ?? 0;
$info->consultant_id = $params['consultant_id'];
$info->check_time = time();
$info->check_admin_id = $adminId;
$result = $info->save();
if ($checkStatus == 2) {
//将其他待审的相同手机号的改拒绝
$where = [
['mobile' , '=', $mobile],
['check_status', '=', 1],
['id', '<>', $customId]
];
MarketCustomer::where($where)->update(['check_time' => time(),'check_status' => -1, 'current_status' => -1,'check_note' => '客户已经被其他登记']);
}
Db::commit();
}catch (BusinessException|\Exception $e){
Db::rollBack();
return json_fail($e->getMessage());
}
if ($result) {
return json_success('操作成功');
} else {
return json_fail('操作失败');
}
}
/**
* Notes: 数据统计分析
* User: yb
* Date: 2024/8/17
* Time: 11:27
* @param $params
*/
public static function statisticsIndex(Request $request)
{
$params = $request->get();
$where = self::commonSearch($params);
//客户总数
$total = MarketCustomer::where($where)->count();
//公池客户
$pubTotal = MarketCustomer::where($where)->where([[function($query){
$diffNums = self::DIFF_TIME;
$query->orWhereRaw("((visit_time + {$diffNums}) - UNIX_TIMESTAMP() <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
}]])->count();
//已报备
$checkTotal = MarketCustomer::where($where)->where('check_status', 1)->count();
//已到访
$visitTotal = MarketCustomer::where($where)->where(
[[function($query) {
$diffNums = self::DIFF_TIME;
$query->whereRaw("((visit_time + {$diffNums}) - UNIX_TIMESTAMP() > 0) AND current_status = 2 AND check_status = 2")->where('belong_status', 1);
}]]
)->count();
//已来电
$mobileTotal = MarketCustomer::where($where)->where([[function($query) {
$query->where('check_status', '=', 2)->where('current_status', '=', 1);
}]])->count();
//已到访
// $visitTotal = MarketCustomer::where($where)->where([[function($query) {
// $query->where('check_status', '=', 2)->where('current_status', '=', 2);
// }]])->count();
//已缴费
$payTotal = MarketCustomer::where($where)->where([[function($query) {
$query->where('check_status', '=', 2)->where('current_status', '=', 3)->where('belong_status', 1);
}]])->count();
//已成交
$dealTotal = MarketCustomer::where($where)->where([[function($query) {
$query->where('check_status', '=', 2)->where('current_status', '=', 4)->where('belong_status', 1);
}]])->count();
//已缴费 + 已成交 / 客户总数
//报备失败
$failTotal = MarketCustomer::where($where)->where('current_status', -1)->count();
$rant = 0;
if ($total > 0) {
$rant = ($payTotal + $dealTotal) / $total;
}
$rant = sprintf('%.2f', $rant);
$data = [
'total' => $total,
'pub_total' => $pubTotal,
'check_total' => $checkTotal,
'report_total' => 0,
'mobile_total' => $mobileTotal,
'visit_total' => $visitTotal,
'pay_total' => $payTotal,
'deal_total' => $dealTotal,
'fail_total' => $failTotal,
'rant' => $rant,
];
return json_success('请求成功', $data);
}
/**
* Notes: 前12个月统计数据
* User: yb
* Date: 2024/8/21
* Time: 15:56
*/
public static function statisticsMonth()
{
$months = month_12();
sort($months);
$startDate = $months[0];
$startDateTime = strtotime($startDate.'/01 00:00:00');
$endDateTime = time();
$where = [
['created_at', '>=', $startDateTime],
['created_at', '<=', $endDateTime]
];
$currentTime = time();
$diffNums = self::DIFF_TIME;
$deptIds = TeamService::getIdsByUser();
if (false === $deptIds) {
//无权限
$where[] = ['dept_id', '=', 0];
} else if (is_array($deptIds)) {
//指定团队下的权限
if (!empty($params['dept_id'])) {
$deptId = end($params['dept_id']);
$deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
}
$where[] = [function($query) use ($deptIds) {
$query->whereIn('dept_id', $deptIds);
}];
} else {
if (!empty($params['dept_id'])) {
$deptId = end($params['dept_id']);
$deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
$where[] = [function($query) use ($deptIds) {
$query->whereIn('dept_id', $deptIds);
}];
}
}
$selectRaw = Db::raw("DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y/%m') as date,count(*) as nums");
$groupRaw = Db::raw("DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y/%m')");
$totalData = MarketCustomer::where($where)->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
$payData = MarketCustomer::where($where)->where([['current_status', '=', 3], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
$visitData = MarketCustomer::where($where)->where([['current_status', '=', 2], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
$dealData = MarketCustomer::where($where)->where([['current_status', '=', 4], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
$returnData = [
'total' => $totalData->pluck('nums','date'),
'pay' => $payData->pluck('nums','date'),
'visit' => $visitData->pluck('nums','date'),
'deal' => $dealData->pluck('nums','date'),
'date' => $months
];
return json_success('请求成功', $returnData);
}
/**
* Notes: 导出数据
* User: yb
* Date: 2024/8/17
* Time: 13:01
*/
public static function exportData(Request $request)
{
$currentTime = time();
$diffNums = self::DIFF_TIME;
$params = $request->get();
$where = self::commonSearch($params);
if (!empty($params['custom_ids'])) {
$customIds = $params['custom_ids'];
$where[] = [function($query) use ($customIds){
$query->whereIn('id', $customIds);
}];
}
$list = MarketCustomer::where($where)->orderBy('visit_time', 'desc')->get();
if (!$list->isEmpty()) {
$consultantKeys = [];
//查询顾问信息
$consultantList = Consultant::select(['id', 'name', 'mobile'])->get();
if (!$consultantList->isEmpty()) {
foreach ($consultantList->toArray() as $consultantItem) {
$consultantKeys[$consultantItem['id']] = $consultantItem;
}
}
$teamKeys = TeamService::getItemKeys();
$returnData = [];
$genderData = ['', '男', '女'];
$typeData = ['', '来电', '来访'];
$currentStatusData = [-1 => '报备失败', 1 => '已报备', 2 => '已到访', 3 => '已缴费', 4 => '已成交'];
$textConfig = self::config();
//合并数组并去重
$type1 = $textConfig['visit_type']['type1'] ?? [];
$type2 = $textConfig['visit_type']['type2'] ?? [];
$textTypeKeys = [];
foreach ($type1 as $k1 => $v1) {
$textTypeKeys[$k1] = $v1;
}
foreach ($type2 as $k2 => $v2) {
$textTypeKeys[$k2] = $v2;
}
foreach ($list as &$item) {
$visitTime = $item->visit_time;
$visitTimeStamp = strtotime($visitTime);
$diff = ($visitTimeStamp + $diffNums) - $currentTime;
$currentStatus = $item->current_status;//当前状态
$checkStatus = $item->check_status;//审核状态
if ($diff <= 0 && $checkStatus == 2 && $currentStatus > 1) {
$currentStatusText = '公池';
} else {
$currentStatusText = $currentStatusData[$item->current_status] ?? '';
}
$ageRangeText = $item->age_range > 0 ? $textConfig['age_range'][$item->age_range - 1] ?? '' : '';
$levelText = $item->level > 0 ? $textConfig['level'][$item->level - 1] ?? '' : '';
$regionText = $item->region > 0 ? $textConfig['region'][$item->region - 1] ?? '' : '';
$visitTypeText = $textTypeKeys[$item->visit_type] ?? '';
$returnData[] = [
'name' => $item->name, //客户姓名
'mobile' => $item->mobile, //客户电话
'mask_mobile' => self::handlePhone($item->mobile),
'gender' => $genderData[$item->gender] ?? '', //客户性别
"report_consultant_name" => $consultantKeys[$item->report_consultant_id]['name'] ?? '', //报备人
'consultant_name' => $consultantKeys[$item->consultant_id]['name'] ?? '', //健康顾问
'team_name' => TeamService::getTeamName($teamKeys, $item->dept_id), //所属团队
'current_status' => $currentStatusText, //当前状态
'visit_time' => $item->visit_time, //最后一次跟进时间
'check_time' => $item->current_status > 1 ? $item->check_time : '', //首次到访时间
"age_range" => $ageRangeText, //年龄区间
"level" => $levelText, //客户等级
"region" => $regionText, //所在区域
"visit_type" => $visitTypeText, //来源
'created_at' => date('Y-m-d H:i:s', strtotime($item->created_at)) //报备时间
];
}
return json_success('请求成功', $returnData);
}
}
/**
* Notes: 对手机号加密处理
* User: yb
* Date: 2024/8/8
* Time: 15:41
* @param $val
* @return string|string[]
*/
public static function handlePhone($val)
{
return substr_replace($val, '****', 3, 4);
}
/**
* Notes: 指定顾问
* User: yb
* Date: 2024/9/5
* Time: 12:52
* @param $params
*/
public static function appoint($params)
{
if (empty($params['id'])) {
return json_fail('请选择客户');
}
if (empty($params['report_consultant_id'])) {
return json_fail('请选择指定顾问');
}
$userId = JwtToken::getCurrentId();
$relationUserId = $userId;
$customId = $params['id'];
$reportConsultantId = $params['report_consultant_id'];
$customInfo = MarketCustomer::firstWhere(['id' => $customId]);
if (empty($customInfo)) {
return json_fail('客户信息不存在');
}
$consultantInfo = Consultant::firstWhere(['id' => $reportConsultantId]);
if (empty($consultantInfo)) {
return json_fail('顾问信息不存在');
}
$currentDeptId = $consultantInfo->dept_id; //当前部门
$now = time();
$logData = [
'market_customer_id' => $customId,
'consultant_id' => $reportConsultantId,
'dept_id' => $currentDeptId,
'before_consultant_id' => $customInfo->report_consultant_id,
'before_dept_id' => $customInfo->dept_id,
'note' => $params['note'] ?? '判客指定报备顾问',
'change_user_id' => $relationUserId,
'change_consultant_id' => 0,
'created_at' => $now
];
Db::beginTransaction();
$result = false;
try {
$customInfo->report_consultant_id = $reportConsultantId;
$customInfo->dept_id = $currentDeptId;
$result = $customInfo->save();
MarketCustomerLogs::insert($logData);
Db::commit();
}catch (BusinessException|\Exception $e) {
Db::rollBack();
return json_fail($e->getMessage());
}
if ($result) {
return json_success('指定成功');
} else {
return json_fail('指定失败');
}
}
/**
* Notes: 导入客户
* User: yb
* Date: 2024/9/10
* Time: 10:30
* @param $params
*/
public static function import($params)
{
if (empty($params['path'])) {
return json_fail('文件地址不能为空');
}
$path = 'public'.$params['path'];
if (is_file($path)) {
$teamKeys = [];
//查询顾问信息
$teamData = Db::table('consultant')
->leftJoin('sys_dept', 'consultant.dept_id', '=', 'sys_dept.dept_id')
->select(['consultant.id', 'consultant.name', 'sys_dept.dept_name', 'sys_dept.dept_id'])
->whereNull('consultant.deleted_at')
->get();
if (!($teamData->isEmpty())) {
foreach ($teamData as $item) {
$key = $item->name.'-'.$item->dept_name;
$teamKeys[$key] = [
'consultant_id' => $item->id,
'dept_id' => $item->dept_id,
'name' => $item->name,
'dept_name' => $item->dept_name
];
}
}
$data = self::readExcel($path);
// $fields = ['到访时间', '客户姓名', '客户手机号', 'A类', 'B类', 'C类', 'D类', '健康顾问', '报备人', '团队'];
if (empty($data)) {
return json_fail('无可导入内容');
} else {
$sucNums = 0;
$errNums = 0;
$validate = '/^1\d{10}$/';
$errorData = [];
Db::beginTransaction();
try {
foreach ($data as $key => $val) {
if ($val[3] == 1) {$level = 1;} else if ($val[4] == 1) {$level = 2;} else if ($val[5] == 1) {$level = 3;} else if ($val[6] == 1) {$level = 4;}
$line = '姓名：'.$val[1].' 第'.($key + 2).'行：';
$mobile = $val[2] ?? '';
$mobile = trim($mobile);
$error = [];
$msg = '';
if (empty($val[7]) && empty($val[8])) {
$msg = $line.'报备人和顾问必须存在其中之一';
$val[10] = '报备人和顾问必须存在其中之一';
// throw new \Exception($line.'报备人和顾问必须存在其中之一');
}
if (empty($val[9])) {
$msg = $line.'无团队信息';
$val[10] = '无团队信息';
// throw new \Exception($line.'无团队信息');
}
if ($val[0] == '1970-01-01 08:00:00') {
$msg = $line.'日期格式错误';
$val[10] = '日期格式错误';
// throw new \Exception($line.'日期格式错误');
}
if (empty($mobile)) {
$msg = $line.'手机号不能为空';
$val[10] = '手机号不能为空';
// throw new \Exception($line.'手机号不能为空');
}
if (!preg_match($validate, $mobile)) {
$msg = $line.'手机号格式非法';
$val[10] = '手机号格式非法';
// throw new \Exception($line.'手机号格式非法');
}
$consultantName = $val[7];
$reportName = $val[8];
$teamName = $val[9];
$consultantKey = $consultantName.'-'.$teamName;
$consultantId = $teamKeys[$consultantKey]['consultant_id'] ?? 0;
$reportKey = $reportName.'-'.$teamName;
$reportConsultantId = $teamKeys[$reportKey]['consultant_id'] ?? 0;
if ($consultantId <= 0 && $reportConsultantId <= 0) {
$msg = $line.'健康顾问或报备人都不存在';
$val[10] = '健康顾问或报备人都不存在';
// throw new \Exception($line.'健康顾问或报备人都不存在');
}
if ($consultantId > 0) {
$deptId = $teamKeys[$consultantKey]['dept_id'] ?? 0;
}
if ($reportConsultantId > 0) {
$deptId = $teamKeys[$reportKey]['dept_id'] ?? 0;
} else {
if ($consultantId > 0) {
$reportConsultantId = $consultantId;
}
}
if (empty($deptId)) {
$msg = $line.'健康顾问或报备人都不存在';
$val[10] = '健康顾问或报备人都不存在';
// throw new \Exception($line.'团队不存在');
}
if (MarketCustomer::checkCustomExists($val[2])) {
$msg = $line.'客户已转到访，无法二次录入';
$val[10] = '客户已转到访，无法二次录入';
// throw new \Exception($line.'客户已转到访，无法二次录入');
}
$result = false;
if (empty($msg)) {
//查看手机号是否存在
$insertData = [
'name' => $val[1],
'mobile' => $mobile,
'level' => $level ?? null,
'consultant_id' => $consultantId,
'report_consultant_id' => $reportConsultantId,
'create_consultant_id' => $reportConsultantId,
'dept_id' => $deptId ?? 0,
'belong_status' => 1,
'current_status' => 1,
'check_status' => 1,
'check_note' => '',
'type' => 1,
'visit_time' => strtotime($val[0]),
'created_at' => strtotime($val[0])
];
$result = Db::table('market_customer')->insert($insertData);
} else {
$val[11] = $line;
$errorData[] = $val;
}
if ($result) {
$sucNums++;
} else {
$errNums++;
}
}
Db::commit();
}catch (\Exception $e) {
Db::rollBack();
return json_fail($e->getMessage());
}
}
} else {
return json_fail('文件不存在');
}
$total = count($data);
return json_success("全部数据：{$total}，成功：{$sucNums}，失败：{$errNums}", ['data' => $errorData]);
}
/**
* Notes: 读取excel文件
* User: yb
* Date: 2024/9/10
* Time: 10:41
* @param $filePath
*/
public static function readExcel($filePath)
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
return json_fail('未知的导入文件类型');
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} else if ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
try {
if (!$PHPExcel = $reader->load($filePath)) {
return json_fail('未知的导入文件类型');
}
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = 10;
$insertData = [];
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getFormattedValue();
if ($currentColumn == 1) {
$val = date('Y-m-d H:i:s', strtotime($val)); //时间
}
$values[] = is_null($val) ? '' : $val;
}
$insertData[] = $values;
}
}catch (\Exception $e) {
return json_fail($e->getMessage());
}
return $insertData;
}
}