悟空crm在商机，合同，回款列表中增加来源字段

最新推荐文章于 2024-09-14 16:08:21 发布

cb1101

最新推荐文章于 2024-09-14 16:08:21 发布

阅读量259

点赞数 1

分类专栏：代码文章标签： php postgresql 数据库

本文链接：https://blog.csdn.net/cb1101/article/details/119061619

版权

代码专栏收录该内容

17 篇文章

订阅专栏

博客内容涉及对CRM系统中business、contract和receivables三个模块的查询语句进行优化，主要修改了JOIN、WHERE条件、LIMIT和FIELD部分，以提高数据检索效率。同时，针对'customer_source'字段进行了多次操作，包括插入数据库和在查询中过滤。此外，还调整了商业智能中客户数量统计的where条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

修改文件均在/application/crm/model/目录下

business.php 商机

216行

        $list = db('crm_business')
            ->alias('business')
            ->join('__CRM_CUSTOMER__ customer', 'business.customer_id = customer.customer_id', 'LEFT')
            ->where($map)
            ->where($partMap)
            ->where($authMap)
            ->where($overdueWhere)
            ->limit($request['offset'], $request['length'])
            ->field('business.*,customer.name as customer_name,customer.source as customer_source,customer.crm_nxvckw as customer_sousuoci')
            ->orderRaw($order)
            ->select();

数据库执行

INSERT INTO `crm1110`.`5kcrm_admin_field`(`field_id`, `types`, `types_id`, `field`, `name`, `form_type`, `default_value`, `max_length`, `is_unique`, `is_null`, `input_tips`, `setting`, `order_id`, `operating`, `create_time`, `update_time`, `type`, `relevant`, `is_hidden`, `style_percent`, `form_position`, `precisions`, `max_num_restrict`, `min_num_restrict`, `remark`, `options`, `formAssistId`) VALUES (130, 'crm_business', 0, 'customer_source', '来源', 'select', '', 0, 0, 0, '', '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', 0, 0, 0, 0, 0, NULL, 0, 100, NULL, NULL, NULL, NULL, NULL, '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', NULL);

contract.php 合同

187行

        foreach ($indexField AS $kk => $vv) {
            if ($vv == 'contract.customer_name') unset($indexField[(int)$kk]);
            if ($vv == 'contract.customer_source') unset($indexField[(int)$kk]);
            if ($vv == 'contract.customer_sousuoci') unset($indexField[(int)$kk]);
            if ($vv == 'contract.business_name') unset($indexField[(int)$kk]);
        }

		$list = db('crm_contract')
				->alias('contract')
				->join('__CRM_CUSTOMER__ customer','contract.customer_id = customer.customer_id','LEFT')		
				->join('__CRM_BUSINESS__ business','contract.business_id = business.business_id','LEFT')	
				->join('__CRM_CONTACTS__ contacts','contract.contacts_id = contacts.contacts_id','LEFT')	
				// ->join('__CRM_RECEIVABLES_PLAN__ plan','contract.contract_id = plan.contract_id','LEFT')	
				->join('CrmReceivables receivables','receivables.contract_id = contract.contract_id AND receivables.check_status = 2','LEFT')
                ->where($searchWhere)
                ->where($map)
				->where($partMap)
				->where($authMap)
                ->where($dealtWhere)
        		->limit($request['offset'], $request['length'])
        		->field(array_merge($indexField, [
					'customer.name' => 'customer_name',
					'customer.source' => 'customer_source',
					'customer.crm_nxvckw' => 'customer_sousuoci',
					'business.name' => 'business_name',
					'contacts.name' => 'contacts_name',
					'ifnull(SUM(receivables.money), 0)' => 'done_money',
					'(contract.money - ifnull(SUM(receivables.money), 0))' => 'un_money',
				]))
        		->orderRaw($order)
        		->group('contract.contract_id')
        		->select();

sql

INSERT INTO `crm1110`.`5kcrm_admin_field`(`field_id`, `types`, `types_id`, `field`, `name`, `form_type`, `default_value`, `max_length`, `is_unique`, `is_null`, `input_tips`, `setting`, `order_id`, `operating`, `create_time`, `update_time`, `type`, `relevant`, `is_hidden`, `style_percent`, `form_position`, `precisions`, `max_num_restrict`, `min_num_restrict`, `remark`, `options`, `formAssistId`) VALUES (129, 'crm_contract', 0, 'customer_source', '来源', 'select', '', 0, 0, 0, '', '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', 0, 0, 0, 0, 0, NULL, 0, 100, NULL, NULL, NULL, NULL, NULL, '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', NULL);

receivables.php回款

175行

        $list = db('crm_receivables')
            ->alias('receivables')
            ->join('__CRM_CUSTOMER__ customer', 'receivables.customer_id = customer.customer_id', 'LEFT')
            ->join('__CRM_CONTRACT__ contract', 'receivables.contract_id = contract.contract_id', 'LEFT')
            ->where($searchWhere)
            ->where($map)
            ->where($partMap)
            ->where($authMap)
            ->where($dealtWhere)
            ->limit($request['offset'], $request['length'])
            ->field('receivables.*,customer.name as customer_name,customer.source as customer_source,customer.crm_nxvckw as customer_sousuoci,contract.name as contract_name,contract.num as contract_num,contract.money as contract_money')
            ->orderRaw($order)
            ->select();

sql

INSERT INTO `crm1110`.`5kcrm_admin_field`(`field_id`, `types`, `types_id`, `field`, `name`, `form_type`, `default_value`, `max_length`, `is_unique`, `is_null`, `input_tips`, `setting`, `order_id`, `operating`, `create_time`, `update_time`, `type`, `relevant`, `is_hidden`, `style_percent`, `form_position`, `precisions`, `max_num_restrict`, `min_num_restrict`, `remark`, `options`, `formAssistId`) VALUES (126, 'crm_receivables', 0, 'customer_source', '来源', 'select', '', 0, 0, 0, '', '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', 0, 0, 0, 0, 0, NULL, 0, 100, NULL, NULL, NULL, NULL, NULL, '竞价\r\n竞价2\r\n信息流\r\n优化\r\n官网\r\n直投\r\n客户提供', NULL);

修改商业智能客户数量统计的问题

/application/crm/model/Customer.php 约950行，修改where语句

				WHERE
					(cu.create_time {$where_time})
					AND cu.owner_user_id = {$user_id}
			";
// 				WHERE
// 					(cu.create_time {$where_time} OR obtain_time {$where_time})
// 					AND cu.owner_user_id = {$user_id}
// 					AND ((  (  deal_time > ".$data['deal_time']." ) OR (update_time > ".$data['follow_time']." AND deal_time > ".$data['deal_time']."))OR deal_status = '已成交'  OR is_lock = 1 )
// 			";