操作方法
-- 存储过程
DROP PROCEDURE IF EXISTS `ldcrm_add_selectbill_bymodule`;
delimiter ||
CREATE PROCEDURE `ldcrm_add_selectbill_bymodule`(
tmp_curmodule VARCHAR(100),
tmp_selmodule VARCHAR(100)
)
BEGIN
SET @cur_tabid=(SELECT tabid FROM ld_tab WHERE `name`=tmp_curmodule LIMIT 1);
SET @sel_tabid=(SELECT tabid FROM ld_tab WHERE `name`=tmp_selmodule LIMIT 1);
SET @maxseq=(SELECT MAX(sequence) FROM ld_option_module WHERE tabid=@cur_tabid);
SET @table_sequence=(SELECT tabsequence FROM ld_tab WHERE `name`=tmp_curmodule LIMIT 1);
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ld_option_module' AND COLUMN_NAME='maxrowsshow') THEN
SET @insert_sql=CONCAT("INSERT INTO ld_option_module(tabid,option_tabid,presence,`default`,approve_demand,sequence,table_sequence,datarang,maxrowsshow)
SELECT ",@cur_tabid,",",@sel_tabid,",",0,",",0,",",0,",",@maxseq+1,",",@table_sequence,",'all',5
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ld_option_module WHERE tabid=",@cur_tabid," AND option_tabid=",@sel_tabid,")");
ELSE
SET @insert_sql=CONCAT("INSERT INTO ld_option_module(tabid,option_tabid,presence,`default`,approve_demand,sequence,table_sequence)
SELECT ",@cur_tabid,",",@sel_tabid,",",0,",",0,",",0,",",@maxseq+1,",",@table_sequence,"
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ld_option_module WHERE tabid=",@cur_tabid," AND option_tabid=",@sel_tabid,")");
END IF;
PREPARE mystmt FROM @insert_sql;
EXECUTE mystmt;
DEALLOCATE PREPARE mystmt;
end ||
delimiter;
-- 存储过程调用方法：
-- 第1个参数“当前模块”，第2个参数“要增加的源单类型选项对应模块”（脚本执行后需要清除缓存），
例如：费用报销 源单类型 增加选择 自定义表单01
CALL ldcrm_add_selectbill_bymodule('Expense','CustomForm01');
例如：自定义表单21到25 源单类型 增加选择 客户
CALL ldcrm_add_selectbill_bymodule('CustomForm21','Accounts');
业务场景：一次出差或者接待，针对多家客户进行。