enterpriseId = $enterpriseId; $this->userCenterId = $userCenterId; $this->objDCustomerCommunication = new DCustomerCommunication('default'); $this->objDCustomerCommunication->setTable('qianniao_customer_communication_'.$enterpriseId); } /** * 客户沟通添加 * @param $params * @return ResultWrapper */ public function addCustomerCommunication($params) { $params['createTime'] = time(); $params['updateTime'] = time(); $dbResult = $this->objDCustomerCommunication->insert($params); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); }else{ return ResultWrapper::success($dbResult); } } /** * 客户沟通删除 * @param $id * @return ResultWrapper */ public function delCustomerCommunication($id) { $update = [ 'deleteStatus' => StatusCode::$delete, 'updateTime' => time(), ]; $dbResult = $this->objDCustomerCommunication->update($update, $id); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); }else{ return ResultWrapper::success($dbResult); } } /** * 客户沟通修改 * @param $params * @param $id * @return ResultWrapper */ public function updateCustomerCommunication($params, $id) { $params['updateTime'] = time(); $dbResult = $this->objDCustomerCommunication->update($params,['id' => $id]); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); }else{ return ResultWrapper::success($dbResult); } } /** * 客户沟通列表 * @return ResultWrapper */ public function getAllCustomerCommunication($params) { $limit = $params['limit']; unset($params['limit']); $offset = $params['offset']; unset($params['offset']); $whereSql = ' deleteStatus = '.StatusCode::$standard; if(isset($params['customerId']) && !empty($params['customerId'])){ $whereSql .= ' and customerId = '.$params['customerId']; } // if(isset($params['staffId']) && !empty($params['staffId'])){ // $whereSql .= ' and staffId = '.$params['staffId']; // } if(isset($params['start']) && !empty($params['start']) && isset($params['end']) && !empty($params['end'])){ $whereSql .= ' and `time` between '.$params['start'].' and '.$params['end']; } //数据域查询 $objMBaseModel = new MBaseModel($this->enterpriseId,$this->userCenterId); $whereSql = $objMBaseModel->getSalesManQueryParams($whereSql, 'staffId'); $dbResult = $this->objDCustomerCommunication->select($whereSql, '*', 'createTime desc', $limit, $offset); if ($dbResult === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $modelResult = self::format($dbResult); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode()); } $data = $modelResult->getData(); $sql = 'select count(*) as count from '.$this->objDCustomerCommunication->get_Table().' where '.$whereSql; $dbResult = $this->objDCustomerCommunication->query($sql); if ($dbResult === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $return = [ 'data' => $data, 'total' => $dbResult[0]['count']?$dbResult[0]['count']:0, ]; if($return === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); }else{ return ResultWrapper::success($return); } } /** * 客户沟通详情 * @param $params * @return ResultWrapper */ public function getCustomerCommunication($params) { $params['deleteStatus'] = StatusCode::$standard; $dbResult = $this->objDCustomerCommunication->get($params); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $modelResult = self::format($dbResult); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode()); } $data = $modelResult->getData(); return ResultWrapper::success($data); } /** * 格式化 * @param $params */ public function format($params) { if (empty($params)) { return ResultWrapper::success($params); } if (!isset($params['id'])) { $data = $params; } else { $data = [$params]; } $customerData = []; $customerIds = array_column($data, 'customerId'); $staffData = []; $staffIds = array_column($data, 'staffId'); if(!empty($customerIds)){ $objMCustomer = new MCustomer($this->enterpriseId,false); $modelResult = $objMCustomer->getCustomerData(['id' => $customerIds], '*', false); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode()); } $customerResult = $modelResult->getData(); foreach($customerResult as $value){ $customerData[$value['id']] = $value; $customerData[$value['managerMobile']] = $value; } unset($modelResult); } if(!empty($staffIds)){ $objMStaff = new MStaff($this->enterpriseId,false); $modelResult = $objMStaff->getAllStaffData(['id' => $staffIds]); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(), $modelResult->getErrorCode()); } $staffResult = $modelResult->getData(); foreach($staffResult as $value){ $staffData[$value['id']] = $value; } } foreach ($data as &$value) { $value['customerName'] = isset($customerData[$value['customerId']]) ? $customerData[$value['customerId']]['name'] : ''; $value['staffName'] = isset($staffData[$value['staffId']]) ? $staffData[$value['staffId']]['staffName'] : ''; if(isset($value['managerMobile'])){ $value['managerMobile'] = isset($customerData[$value['managerMobile']]) && !empty($customerData[$value['managerMobile']]) ? $customerData[$value['managerMobile']]['managerMobile'] : ''; } } unset($value); if (!isset($params['id'])) { $return = $data; } else { $return = array_shift($data); } return ResultWrapper::success($return); } /** * 客户拜访报表 * @return ResultWrapper */ public function getCustomerCallOnReportForm($selectParams) { $limit = $selectParams['limit']; unset($selectParams['limit']); $offset = $selectParams['offset']; unset($selectParams['offset']); //筛选部门 $whereSql = 'c.deleteStatus ='.StatusCode::$standard ; if(!empty($selectParams['departmentId']) ){ $whereSql .= ' and s.departmentId in(' .implode (',',$selectParams['departmentId']).')'; } //筛选时间 if(isset($selectParams['start']) && !empty($selectParams['start']) && isset($selectParams['end']) && !empty($selectParams['end'])){ $whereSql .= ' and `time` between '.$selectParams['start'].' and '.$selectParams['end']; } //数据域查询 $objMBaseModel = new MBaseModel($this->enterpriseId,$this->userCenterId); $whereSql = $objMBaseModel->getSalesManQueryParams($whereSql, ' staffId '); //查询员工 姓名,部门,客户数,跟进次数 $sql = 'select c.staffId,s.staffName,s.departmentId,count(DISTINCT c.customerid) as customerNum,count(c.id) as num from qianniao_customer_communication_'.$this->enterpriseId.' as c left join qianniao_staff_'.$this->enterpriseId.' s on c.staffId = s.id where '. $whereSql.' GROUP BY c.staffId limit '.$offset.','.$limit; $dbResultStaff = $this->objDCustomerCommunication->query($sql); if ($dbResultStaff === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } //统计条数 $sql = 'SELECT count(*) as total FROM (select c.staffId from qianniao_customer_communication_'.$this->enterpriseId.' as c left join qianniao_staff_'.$this->enterpriseId.' s on c.staffId = s.id where '.$whereSql.' GROUP BY c.staffId ) as a'; $dbResult = $this->objDCustomerCommunication->query($sql); if ($dbResult === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } //查询单个客户的订单金额和订单数量 $sql = 'select customerId,count(id) as number ,sum(payAmount) as amount from qianniao_order_'.$this->enterpriseId.'_1 where customerId in(select c.customerId from qianniao_customer_communication_'.$this->enterpriseId.' as c left join qianniao_staff_'.$this->enterpriseId.' s on c.staffId = s.id group by c.customerId) group by customerId'; $dbResultOrder = $this->objDCustomerCommunication->query($sql); if ($dbResultOrder === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } //用客户id映射客户数据 $customerNum = []; foreach ($dbResultOrder as $key =>$value){ $customerNum [$value['customerId']] = $value ; } $departmentIds = []; //把查寻记录表中的员工循环出来 foreach ($dbResultStaff as &$v){ $departmentIds[] = $v['departmentId']; //根据员工id查询客户id; $sql = 'select customerId from qianniao_customer_communication_'.$this->enterpriseId.' where staffId = '.$v['staffId'].' group by customerId'; $dbResultCustomer = $this->objDCustomerCommunication->query($sql); if ($dbResultCustomer === false) { return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $v['customerOrderNum'] = 0; $v['customerOrderAmount'] = 0.00; //循环累加客户数据 foreach ($dbResultCustomer as $value){ if( isset($customerNum[$value['customerId']]) ){ $v['customerOrderNum'] += $customerNum[$value['customerId']]['number']; $v['customerOrderAmount'] += $customerNum[$value['customerId']]['amount']; } } } $objDepartment =new MDepartment($this->enterpriseId); $modelResult = $objDepartment->getNameByIds($departmentIds); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(),$modelResult->getErrorCode()); } $departmentData = $modelResult->getData(); $departmentName = []; foreach ($departmentData as $value){ $departmentName[$value['id']] = $value['departmentName']; } foreach ($dbResultStaff as &$value){ $value['departmentName'] = $departmentName[$value['departmentId']]; } $return = [ 'data' => $dbResultStaff, 'total' => $dbResult[0]['total'] ]; return ResultWrapper::success($return); } /** * 客户拜访记录详情 * @return ResultWrapper */ public function getCustomerVisitInfo($selectParams) { $limit = $selectParams['limit']; unset($selectParams['limit']); $offset = $selectParams['offset']; unset($selectParams['offset']); $selectParams['deleteStatus'] = StatusCode::$standard; $sql = 'select a.customerId,b.`name`,a.time,a.location,a.content,a.picture from qianniao_customer_communication_'.$this->enterpriseId.' as a LEFT JOIN qianniao_customer_'.$this->enterpriseId.' as b on a.customerId = b.id where staffId ='.$selectParams['staffId'].' and a.deleteStatus ='.$selectParams['deleteStatus']. ' limit '.$offset .',' .$limit; $dbResult = $this->objDCustomerCommunication->query($sql); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $total = $this->objDCustomerCommunication->count($selectParams); $return = [ 'data' => $dbResult, 'total' => ($total)?intval($total):0, ]; return ResultWrapper::success($return); } /** * 未拜访客户统计列表 * @return ResultWrapper */ public function getCustomerNoVisit($selectParams) { $limit = $selectParams['limit']; unset($selectParams['limit']); $offset = $selectParams['offset']; unset($selectParams['offset']); $selectParams['deleteStatus'] = StatusCode::$standard; $whereSql = ' where deleteStatus='.$selectParams['deleteStatus']; if( !empty($selectParams['staffId']) ){ $whereSql .= ' and staffId ='.$selectParams['staffId']; } $where =''; if( !empty($selectParams['province']) ){ $where .= ' and c.provinceCode ='.$selectParams['province']; } if( !empty($selectParams['city']) ){ $where .= ' and c.cityCode ='.$selectParams['city']; } if( !empty($selectParams['district']) ){ $where .= ' and c.districtCode ='.$selectParams['district']; } //`name` LIKE '%测试%' and salesManName LIKE '%根据%' if( !empty($selectParams['customerName']) ){ $where .= ' and c.`name` LIKE "%' .$selectParams['customerName']. '%"'; } //根据天数查询所对应的未拜访记录 $daysSql = 'select b.customerId,b.intervalDay,c.* from (select *,TIMESTAMPDIFF(day,FROM_UNIXTIME(createTime,"%Y%m%d"),CURRENT_DATE()) as intervalDay from (select customerId,createTime from qianniao_customer_communication_'.$this->enterpriseId.$whereSql.' order by createTime desc limit 99999)a GROUP BY a.customerId)b LEFT JOIN qianniao_customer_'.$this->enterpriseId.' c on b.customerId = c.id WHERE b.intervalDay < '.$selectParams['days'].$where.' limit '.$offset.' , '.$limit; $dayDbResult = $this->objDCustomerCommunication->query($daysSql); if($dayDbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } if(empty($dayDbResult)){ $return = [ 'data' => [], 'total' => 0 ]; return ResultWrapper::success($return); } //拿到范围内的客户 $customer = []; foreach ($dayDbResult as $value){ $customer[] = $value['customerId']; } $customerIds = implode(',',$customer); //查询每个客户本年拜访次数 $dbYearNum = $this->getCustomerYearVisitNumber($customerIds); if(!$dbYearNum->isSuccess()){ return ResultWrapper::fail($dbYearNum->getData(),$dbYearNum->getErrorCode()); } $yearNumDbResult = $dbYearNum->getData(); $yearNum = []; foreach ($yearNumDbResult as $v){ $yearNum[$v['customerId']]= $v['yearNum'];//客户本年拜访次数 } //根据查出天数范围内的客户查出所对应的日期并归组 $result = $this->getCustomerDate($customerIds); if(!$result->isSuccess()){ return ResultWrapper::fail($result->getData(),$result->getErrorCode()); } $dbResult = $result->getData(); //查询客户数据以及注册时间 $objMCustomer = new MCustomer($this->enterpriseId,false); $modelResult = $objMCustomer->getCustomerData(['id'=> $customer],'*',false); if(!$modelResult->isSuccess()){ return ResultWrapper::fail($modelResult->getData(),$modelResult->getErrorCode()); } $customerResult = $modelResult->getData(); //处理客户地址 $objMSysAreaChina = new MSysAreaChina(); //处理客户类型 $objMCustomerSource = new MCustomerSource($this->enterpriseId); $customerSourceIds = array_column($customerResult,'type');//客户类型 //查询客户类型 if ($customerSourceIds) { $customerSourceData = $objMCustomerSource->getCustomerSourceNameByIds($customerSourceIds); if ($customerSourceData->isSuccess()) { $customerSourceArray = $customerSourceData->getData(); foreach ($customerSourceArray as $customerSource){ $customerSourceArr[$customerSource['id']] = $customerSource['name']; } } } $customerData = []; foreach ($customerResult as $value){ $customerData[$value['id']]['customerDays'] = intval((time() - $value['createTime']) / 86400);//注册天数 $customerData[$value['id']]['name'] = $value['name'];//客户名 $customerData[$value['id']]['code'] = $value['code'];//客户编码 $customerData[$value['id']]['provinceCode'] = $value['provinceCode'];//地区 $customerData[$value['id']]['cityCode'] = $value['cityCode'];//地区 $customerData[$value['id']]['districtCode'] = $value['districtCode'];//地区 $customerData[$value['id']]['address'] = $value['address'];//地区 $customerData[$value['id']]['type'] = $value['type'];//客户类型 // $customerSourceIds[] = $value['type']; $customerData[$value['id']]['salesManName'] = $value['salesManName'];//业务员 } //按照查出的年 本月 上月进行分组 $data = []; $year = strtotime(date('Y-1-1')); $thisMonth = mktime(0,0,0,date('m'),1,date('Y')); $lastMonth = strtotime(date('Y-m-01 00:00:00',strtotime('-1 month'))); foreach ( $dbResult as $value ){ //年 $time = strtotime($value['time']); if( $time>$year ){ if( isset($data[$value['customerId']]['year']) ){ $data[$value['customerId']]['year']['orderNum'] += $value['orderNum']; $data[$value['customerId']]['year']['orderMoney'] += $value['orderMoney']; } else { $data[$value['customerId']]['year']['orderNum'] = $value['orderNum']; $data[$value['customerId']]['year']['orderMoney'] = $value['orderMoney']; } } //月 if( $time>$thisMonth ){ if( isset($data[$value['customerId']]['thisMonth']) ){ $data[$value['customerId']]['thisMonth']['orderNum'] += $value['orderNum']; $data[$value['customerId']]['thisMonth']['orderMoney'] += $value['orderMoney']; } else { $data[$value['customerId']]['thisMonth']['orderNum'] = $value['orderNum']; $data[$value['customerId']]['thisMonth']['orderMoney'] = $value['orderMoney']; } } //上月 if( $time>$lastMonth && $time < $thisMonth ){ if( isset($data[$value['customerId']]['lastMonth']) ){ $data[$value['customerId']]['lastMonth']['orderNum'] += $value['orderNum']; $data[$value['customerId']]['lastMonth']['orderMoney'] += $value['orderMoney']; } else { $data[$value['customerId']]['lastMonth']['orderNum'] = $value['orderNum']; $data[$value['customerId']]['lastMonth']['orderMoney'] = $value['orderMoney']; } } } foreach($dayDbResult as &$value) { //年 if ( isset($data[$value['customerId']]['year']) ) { $value['year']['orderNum'] = $data[$value['customerId']]['year']['orderNum']; $value['year']['orderMoney'] = $data[$value['customerId']]['year']['orderMoney']; } else { $value['year']['orderNum'] = 0; $value['year']['orderMoney'] = 0; } //月 if ( isset($data[$value['customerId']]['thisMonth']) ) { $value['thisMonth']['orderNum'] = $data[$value['customerId']]['thisMonth']['orderNum']; $value['thisMonth']['orderMoney'] = $data[$value['customerId']]['thisMonth']['orderMoney']; } else { $value['thisMonth']['orderNum'] = 0; $value['thisMonth']['orderMoney'] = 0; } //上月 if ( isset($data[$value['customerId']]['lastMonth']) ) { $value['lastMonth']['orderNum'] = $data[$value['customerId']]['lastMonth']['orderNum']; $value['lastMonth']['orderMoney'] = $data[$value['customerId']]['lastMonth']['orderMoney']; } else { $value['lastMonth']['orderNum'] = 0; $value['lastMonth']['orderMoney'] = 0; } $value['customerDays'] = isset($customerData[$value['customerId']]) ? $customerData[$value['customerId']]['customerDays'] : ''; $value['customerName'] = isset($customerData[$value['customerId']]) ? $customerData[$value['customerId']]['name'] : ''; $value['customerCode'] = isset($customerData[$value['customerId']]) ? $customerData[$value['customerId']]['code'] : ''; $value['customerSalesManName'] = isset($customerData[$value['customerId']]) ? $customerData[$value['customerId']]['salesManName'] : ''; //处理客户地址 if (isset($customerData[$value['customerId']]['provinceCode'] ) && isset($customerData[$value['customerId']]['cityCode']) && isset($customerData[$value['customerId']]['districtCode']) ) { $areaName = $objMSysAreaChina->getNameByCode([ $customerData[$value['customerId']]['provinceCode'], $customerData[$value['customerId']]['cityCode'], $customerData[$value['customerId']]['districtCode'] ]); $value['area']['provinceName'] = isset($areaName[ $customerData[$value['customerId']]['provinceCode']]) ? $areaName[ $customerData[$value['customerId']]['provinceCode']] : ''; $value['area']['cityName'] = isset($areaName[ $customerData[$value['customerId']]['cityCode']]) ? $areaName[ $customerData[$value['customerId']]['cityCode']] : ''; $value['area']['districtName'] = isset($areaName[ $customerData[$value['customerId']]['districtCode']]) ? $areaName[ $customerData[$value['customerId']]['districtCode']] : ''; $value['area']['address'] = isset($customerData[$value['customerId']]['address']) ? $customerData[$value['customerId']]['address'] :''; } if(isset($customerData[$value['customerId']])){ $value['typeName'] = isset($customerSourceArr[$customerData[$value['customerId']]['type']]) ? $customerSourceArr[$customerData[$value['customerId']]['type']] :''; } else { $value['typeName'] = ''; } if(isset($yearNum[$value['customerId']])){ $value['yearNum'] = isset( $yearNum[$value['customerId']]) ? $yearNum[$value['customerId']] : 0; } else { $value['yearNum'] = 0; } } $totalSql = 'select count(*) as total from (select *,TIMESTAMPDIFF(day,FROM_UNIXTIME(createTime,"%Y%m%d"),CURRENT_DATE()) as intervalDay from (select customerId,createTime from qianniao_customer_communication_'.$this->enterpriseId.$whereSql.' order by createTime desc limit 99999)a GROUP BY a.customerId)b LEFT JOIN qianniao_customer_'.$this->enterpriseId.' c on b.customerId = c.id WHERE b.intervalDay < '.$selectParams['days'].$where; $total = $this->objDCustomerCommunication->query($totalSql); if($total === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } $return = [ 'data' => $dayDbResult, 'total' => $total[0]['total'] ? intval($total[0]['total']):0, ]; return ResultWrapper::success($return); } /** * 获取客户本年拜访次数统计 * @return ResultWrapper * @params string */ public function getCustomerYearVisitNumber($customerIds) { if(empty($customerIds)){ return ResultWrapper::success([]); } $year = strtotime(date('Y-1-1')); //本年开始时间 //根据客户id查询客户本年拜访次数 $yearNumSql = 'SELECT customerId,count(customerId) as yearNum from qianniao_customer_communication_'.$this->enterpriseId.' WHERE customerId in('.$customerIds.') and time >= '.$year.' GROUP BY customerId'; $yearNumDbResult = $this->objDCustomerCommunication->query($yearNumSql); if($yearNumDbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } return ResultWrapper::success($yearNumDbResult); } /** * 获取客户距离上次拜访多少天 * @return ResultWrapper * @params string */ public function getCustomerlastTimeDay($customerIds) { if(empty($customerIds)){ return ResultWrapper::success([]); } //根据客户id查询客户距离上次拜访多少天 $daySql = 'SELECT DISTINCT customerId,`time` from qianniao_customer_communication_'.$this->enterpriseId.' WHERE customerId in('.$customerIds.') order by `time` desc'; $dayNumDbResult = $this->objDCustomerCommunication->query($daySql); foreach ($dayNumDbResult as $key =>$value){ $dayNumDbResult[$key]['time'] = intval((time() - $value['time']) / 86400); } if($dayNumDbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } return ResultWrapper::success($dayNumDbResult); } /** * 根据客户id查出客户所对应的日期 * @return ResultWrapper * @params string */ public function getCustomerDate($customerIds) { if(empty($customerIds)){ return ResultWrapper::success([]); } $sql = 'select customerId,count(id) as orderNum,sum(payAmount) as orderMoney,FROM_UNIXTIME(createTime,"%Y-%m") as time,createTime from qianniao_order_'.$this->enterpriseId.'_1 where customerId in('.$customerIds.') GROUP BY customerId,time'; $dbResult = $this->objDCustomerCommunication->query($sql); if($dbResult === false){ return ResultWrapper::fail($this->objDCustomerCommunication->error(), ErrorCode::$dberror); } return ResultWrapper::success($dbResult); } }