如何在单据上增加【相关单据】字段，支持选择不同模块的不同单据，包括自定义单据？

lingdang

问题：如何在单据上增加【相关单据】字段，支持选择不同模块的不同单据，包括自定义表单单据？



说明：仅支持V8.3.8及以上版本，执行脚本后并打下方的补丁

操作方法

1、打开Navicat数据库连接工具，执行附件中的脚本并清除缓存，这里以“费用报销单”模块，添加相关单据字段，可选择“合同订单”和“服务合同”单据为例：

SET @modulename='Expense';
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename LIMIT 1);
SET @trantable='ld_field';
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @columnname='related_info';
SET @fieldname='related_info';
SET @zh='相关单据';
SET @en='Related Bills';
SET @uitype=125;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @displaytype=1;
SET @bigtype='NULL';
SET @datarang=NULL;
SET @maxrowsshow=0;

-- 增加相关区块
SET @maxblockid=(SELECT MAX(blockid) FROM ld_blocks);
INSERT INTO ld_blocks(blockid,tabid,blocklabel,sequence,show_title,visible,create_view,edit_view,detail_view,display_status,iscustom)
SELECT @maxblockid+1,@tabid,CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL'),1,0,0,0,0,0,1,0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_blocks WHERE blocklabel=CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL') AND tabid=@tabid);

SET @blockid=(SELECT blockid FROM ld_blocks WHERE blocklabel=CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL') AND tabid=@tabid LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_blocks',@tabid,@blockid,'相关单据信息' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE tabid=@tabid AND relateid=@blockid AND languagetype='zh' and trantable='ld_blocks');

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_blocks',@tabid,@blockid,'Related Bills Information' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE tabid=@tabid AND relateid=@blockid AND languagetype='en' and trantable='ld_blocks');
UPDATE ld_blocks_seq SET id=(SELECT MAX(blockid) FROM ld_blocks);

SET @relateid=@blockid;
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_field WHERE tabid=@tabid AND block=@relateid),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,'TEXT','200',NULL);

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

-- 添加可选单据:合同订单
SET  @select_module='SalesOrder';
INSERT INTO ld_shareequally(fieldname,module,entitytype,`default`,presence,sequence)
SELECT @columnname,@modulename,@select_module,0,0,1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_shareequally WHERE fieldname=@columnname AND module=@modulename AND entitytype=@select_module);

-- 添加可选单据:服务合同
SET  @select_module='ServiceContracts';
INSERT INTO ld_shareequally(fieldname,module,entitytype,`default`,presence,sequence)
SELECT @columnname,@modulename,@select_module,0,0,1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_shareequally WHERE fieldname=@columnname AND module=@modulename AND entitytype=@select_module);

2、若需要在其他模块上增加选择其他模块单据，如：在客户模块增加选择自定义表单11模块的单据，则将附件中的sql修改成对应模块或添加对应模块的sql，然后执行脚本清除缓存即可，附件中的sql修改位置以红色字体标注




SET @modulename='Accounts';
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename LIMIT 1);
SET @trantable='ld_field';
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @columnname='related_info';
SET @fieldname='related_info';
SET @zh='相关单据';
SET @en='Related Bills';
SET @uitype=125;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @displaytype=1;
SET @bigtype='NULL';
SET @datarang=NULL;
SET @maxrowsshow=0;

-- 增加相关区块
SET @maxblockid=(SELECT MAX(blockid) FROM ld_blocks);
INSERT INTO ld_blocks(blockid,tabid,blocklabel,sequence,show_title,visible,create_view,edit_view,detail_view,display_status,iscustom)
SELECT @maxblockid+1,@tabid,CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL'),1,0,0,0,0,0,1,0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_blocks WHERE blocklabel=CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL') AND tabid=@tabid);

SET @blockid=(SELECT blockid FROM ld_blocks WHERE blocklabel=CONCAT('LBL_',UPPER(@modulename),'_RELATEDBILL') AND tabid=@tabid LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh','ld_blocks',@tabid,@blockid,'相关单据信息' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE tabid=@tabid AND relateid=@blockid AND languagetype='zh' and trantable='ld_blocks');

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en','ld_blocks',@tabid,@blockid,'Related Bills Information' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE tabid=@tabid AND relateid=@blockid AND languagetype='en' and trantable='ld_blocks');
UPDATE ld_blocks_seq SET id=(SELECT MAX(blockid) FROM ld_blocks);

SET @relateid=@blockid;
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_field WHERE tabid=@tabid AND block=@relateid),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,'TEXT','200',NULL);

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

-- 添加可选单据:合同订单
SET  @select_module='SalesOrder';
INSERT INTO ld_shareequally(fieldname,module,entitytype,`default`,presence,sequence)
SELECT @columnname,@modulename,@select_module,0,0,1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_shareequally WHERE fieldname=@columnname AND module=@modulename AND entitytype=@select_module);

-- 添加可选单据:服务合同
SET  @select_module='ServiceContracts';
INSERT INTO ld_shareequally(fieldname,module,entitytype,`default`,presence,sequence)
SELECT @columnname,@modulename,@select_module,0,0,1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_shareequally WHERE fieldname=@columnname AND module=@modulename AND entitytype=@select_module);

-- 添加可选单据:自定义表单11
SET  @select_module='CustomForm11';
INSERT INTO ld_shareequally(fieldname,module,entitytype,`default`,presence,sequence)
SELECT @columnname,@modulename,@select_module,0,0,1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_shareequally WHERE fieldname=@columnname AND module=@modulename AND entitytype=@select_module);