拜访签到，不同拜访对象获取不同的拜访地址

lingdang

问题：拜访签到模块，不同拜访对象获取不同的拜访地址？


解决方法：在Navicat数据库中，设置拜访对象对应的拜访地址

操作步骤：

1、打开Navicat数据库工具->选中数据库->查询->新建查询

1

2、复制粘贴以下脚本，点击【运行】

select * from ld_related_field
where trigger_fieldid = (select fieldid from ld_field LEFT JOIN ld_tab on ld_tab.tabid = ld_field.tabid where ld_tab.`name` = 'Singin' and ld_field.columnname = 'record');

3、修改formvalue_info的值，修改方法如下：

2

select CONCAT(ld_account.bill_city,ld_account.bill_street) as address from ld_account -- 拜访对象为客户
where ld_account.deleted = 0 and accountid = ?
UNION
select CONCAT(ld_account.bill_city,ld_account.bill_street) as address from ld_contactdetails -- 拜访对象为联系人
INNER JOIN ld_account on ld_account.accountid = ld_contactdetails.accountid
where ld_account.deleted = 0 and ld_contactdetails.contactid = ?
UNION
select CONCAT(ld_account.bill_city,ld_account.bill_street) as address from ld_contactrecords -- 拜访对象为跟进记录
INNER JOIN ld_account on ld_account.accountid = ld_contactrecords.accountid where ld_account.deleted = 0 and ld_contactrecords.contactrecordID = ?
UNION
select CONCAT(ld_account.bill_city,ld_account.bill_street) as address from ld_potential -- 拜访对象为销售机会
INNER JOIN ld_account on ld_account.accountid = ld_potential.accountid
where ld_account.deleted = 0 and ld_potential.potentialid = ?
UNION
select CONCAT(ld_account.bill_city,ld_account.bill_street) as address from ld_project -- 拜访对象为项目
INNER JOIN ld_account on ld_account.accountid = ld_project.accountid
where ld_account.deleted = 0 and ld_project.projectid = ?


修改红色标注的内容，找到对应模块，设置当前模块字段。修改为该字段的columnname（误差：实际签到地址-拜访地址）；
注意：绿色标注的为帮助大家理解的说明信息，不要复制到formvalue_info的值


“拜访地址”：系统默认取关联客户的地址信息，并且是字段组合 CONCAT(ld_account.bill_city,ld_account.bill_street) as address。
自己可设置一个字段即可，例如项目模块增加自定义字段“拜访地址”。外勤签到时，选择项目时，拜访地址获取项目的“拜访地址”。
则修改项目对应的sql语句，其他sql语句不变。例如：

若为自定义字段，则项目模块sql语句修改为：
select cf_2233 from ld_project -- 拜访对象为项目
INNER JOIN ld_projectcf on ld_projectcf.projectjectid= ld_project.accountid
where ld_project.deleted = 0 and ld_project.projectjectid = ?

说明：
cf_2233根据拜访对象获取字段的columnname更改
INNER JOIN ld_projectcf on ld_projectcf.projectjectid= ld_project.accountid 为固定的；

若为主表预置的字段，则项目模块sql语句修改为：
select cf_2233 from ld_project -- 拜访对象为项目
where ld_project.deleted = 0  and ld_ld_project.projectjectid = ?

说明：cf_2233根据拜访对象获取字段的columnname更改


特别说明：不同拜访对象类型，sql语句中都修改为对应的模块表