如何在单据上增加部门字段并根据选用户字段带出？

操作方法

1、打开Navicat数据库连接工具，执行以下脚本并清除缓存，这里以“合同订单”模块，选择“单选用户”字段带出该用户对应的“所属部门”字段为例：
/*
实现：增加部门字段根据选用户字段带出_20210115.sql
例如：合同订单上已增加有单选用户的字段，现需要增加部门字段显示用户对应的部门
备注：脚本中的变量信息需要根据需要修改，执行脚本之后需要清除缓存
*/

-- 根据单据上的选择成员字段设置字段变量信息
SET @modulename='SalesOrder';
SET @userfield_columnname='cf_3349';                        -- 选择用户字段在 ld_field 表中对应的 columnname
SET @department_columnname='department_name';        -- 定义部门字段 columnname
SET @userfield_lable_zh='所属部门';                                -- 定义部门字段中文标签
SET @userfield_lable_en='Department Name';                -- 定义部门字段英文标签



-- 以下为增加部门字段，以及选成员自动带出部门功能，该部分脚本无需修改
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename);
SET @relateid=(SELECT block FROM ld_field WHERE tabid=@tabid AND columnname=@userfield_columnname LIMIT 1);
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_field WHERE tabid=@tabid AND block=@relateid),0);
SET @trantable='ld_field';
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname=@userfield_columnname LIMIT 1);
SET @columnname=@department_columnname;
SET @fieldname=@department_columnname;
SET @zh=@userfield_lable_zh;
SET @en=@userfield_lable_en;
SET @uitype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @displaytype=1;
SET @bigtype='text';
SET @datarang=NULL;
SET @maxrowsshow=0;

SET @maxid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly,approve_demand,is_checkimportfield,bigtype,datarang,maxrowsshow)
SELECT @tabid,@maxid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,1,2,NULL,100,@maxseq+1,@relateid,@displaytype,@typeofdata,@quickcreate,NULL,NULL,1,NULL,0,2,0,@bigtype,@datarang,@maxrowsshow from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);

UPDATE ld_field_seq SET id=(SELECT max(fieldid) FROM ld_field);

SET @relateid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh',@trantable,@tabid,@relateid,@zh FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en',@trantable,@tabid,@relateid,@en FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);

CALl AddColumn(@tablename,@columnname,'VARCHAR','255','');

CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);

UPDATE ld_field SET
        allowedit='no',
        defaultvalue=(
                SELECT
                        GROUP_CONCAT(DISTINCT c.groupname ORDER BY c.groupid) AS groupname
                FROM ld_users a
                LEFT JOIN ld_users2group b ON b.userid = a.id
                INNER JOIN ld_groups c ON c.groupid = b.groupid
                WHERE a.id=1
                GROUP BY a.id
        )
WHERE tabid=@tabid AND columnname=@department_columnname;

UPDATE ld_field SET defaultvalue=1 WHERE tabid=@tabid AND columnname=@userfield_columnname;

SET @trigger_fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@userfield_columnname);
SET @effect_fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@department_columnname);
INSERT INTO ld_related_field(fieldtype,trigger_fieldid,effect_fieldid,fromvalue_type,fromvalue_info)
SELECT 'main',@trigger_fieldid,@effect_fieldid,'sql','SELECT
        GROUP_CONCAT(DISTINCT c.groupname ORDER BY c.groupid) AS groupname
FROM ld_users a
LEFT JOIN ld_users2group b ON b.userid = a.id
INNER JOIN ld_groups c ON c.groupid = b.groupid
WHERE a.id=?
GROUP BY a.id' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_related_field WHERE trigger_fieldid=@trigger_fieldid AND effect_fieldid=@effect_fieldid)
        AND @trigger_fieldid>0 AND @effect_fieldid>0;增加部门字段根据选用户字段带出_20210115.sql