一、DEMO脚本
-- ----------------------------
-- Procedure structure for hetao_autoaddform15_byform19
-- ----------------------------
DROP PROCEDURE IF EXISTS `hetao_autoaddform15_byform19`;
DELIMITER ;;
CREATE PROCEDURE `hetao_autoaddform15_byform19`(
tmp_form19_billid INT(19),
tmp_form19_ownerid INT(19)
)
BEGIN-- 获取自动创建单据的 ID
SET @hetao_maxcrmid=(SELECT MAX(crmid) FROM ld_crmentity);
INSERT INTO ld_crmentity(crmid,setype)
SELECT @hetao_maxcrmid+1,'CustomForm15';UPDATE ld_crmentity_seq SET id=(SELECT MAX(crmid) FROM ld_crmentity);
-- 查询审批流程和审批人信息
SET @hetao_approveid=(SELECT id FROM ld_approve WHERE tabid=157 AND `status`=0 ORDER BY IFNULL(modifiedtime,0) DESC LIMIT 1);
SET @hetao_approvestepid=(SELECT id FROM ld_approvestep WHERE approveid=@hetao_approveid ORDER BY sequence LIMIT 1);
SET @hetao_nextapproveby=NULL;
-- 插入主表
INSERT INTO ld_customform15(
customform15id,
customform15_no,
accountid,
contactid,source_list,
entitytype,smownerid,
smcreatorid,
modifiedby,
createdtime,
modifiedtime,currency_id,
currency_symbol,
conversion_rate,
total,
subtotal,approvestatus,
currentapprover,
currentapprovestepid,
approvesubmitter,
approvesubmittedtime
)
SELECT
@hetao_maxcrmid+1,
(SELECT CONCAT(prefix,DATE_FORMAT(CURDATE(),'%Y%m%d'),LPAD(cur_id+0,3,'0')) FROM ld_modentity_num WHERE semodule='CustomForm15' AND active=1),
a.accountid,
a.contactid,a.customform19id,
'CustomForm19',a.smownerid,
a.smownerid,
a.smownerid,
NOW(),
NOW(),a.currency_id,
a.currency_symbol,
a.conversion_rate,
a.total,
a.subtotal,'未提交',
@hetao_nextapproveby,
NULL,
a.smownerid,
NOW()
FROM ld_customform19 a
WHERE a.customform19id=tmp_form19_billid LIMIT 1;SET @lingliao_type=(
SELECT
form17scf.cf_4676
FROM ld_customform17scf form17scf
LEFT JOIN ld_customform19 form19 ON form19.source_list=form17scf.customform17id
WHERE form19.customform19id=tmp_form19_billid
);INSERT INTO ld_customform15scf(
cf_4605, -- 站点
cf_4607, -- 客户编号
cf_4517, -- 派单类型
cf_4550, -- 客户地址
cf_4555, -- 派工单状态
cf_4677, -- 领料类型cf_4611, -- 联系人电话
cf_4553, -- 详细内容customform15id
)
SELECT
b.cf_4656, -- 站点
c.account_no, -- 客户编号
'安装', -- 派单类型
b.cf_4651, -- 客户地址
'未派工', -- 派工单状态
@lingliao_type,b.cf_4649, -- 联系人电话
b.cf_4533, -- 备注@hetao_maxcrmid+1
FROM ld_customform19 a
LEFT JOIN ld_customform19scf b ON b.customform19id=a.customform19id
LEFT JOIN ld_account c ON c.accountid=a.accountid
WHERE a.customform19id=tmp_form19_billid;UPDATE ld_modentity_num SET cur_id=LPAD(cur_id+1,5,'0') WHERE semodule='CustomForm15' AND active=1;
-- 插入产品明细
INSERT INTO ld_customform15detail(
id,
productid,
sequence_no,quantity,
listprice,
taxprice,
tax3,
tax_amount,
comment,
baoyang_zhouqi,
parent_lineitem_id,
entitytype,
entitytypeid
)
SELECT
@hetao_maxcrmid+1,
a.productid,
a.sequence_no,a.quantity,
a.listprice,
a.taxprice,
a.tax3,
a.tax_amount,
comment,
baoyang_zhouqi,
a.lineitem_id,
'CustomForm19',
a.id
FROM ld_customform19detail a
LEFT JOIN ld_products p ON p.productid=a.productid
WHERE a.id=tmp_form19_billid;INSERT INTO ld_customform15detailscf(
sf5623, -- 维保到期日
sf5411, -- 费用
sf5199, -- 成本总价
sf3927, -- 毛利lineitem_id
)
SELECT
b.sf5835, -- 安装日期
b.sf5411, -- sf5411
b.sf5199, -- 成本总价
b.sf3927, -- 毛利a.lineitem_id
FROM ld_customform15detail a
LEFT JOIN ld_customform19detailscf b ON b.lineitem_id=a.parent_lineitem_id
WHERE a.id=@hetao_maxcrmid+1;
-- 更新派工单：客户简称、送货类型、领料类型
UPDATE ld_customform15 a
LEFT JOIN ld_customform15scf b ON b.customform15id=a.customform15id
LEFT JOIN ld_accountscf c ON c.accountid=a.accountid
SET
b.cf_5316=c.cf_4579
WHERE a.customform15id=@hetao_maxcrmid+1;-- 自动创建派工类型为维保或安装的派工单时，表头的“维保日期”能显示出分录的维保到期日的最小值。
UPDATE ld_customform15scf a
LEFT JOIN (
SELECT
a.id,
MIN(b.sf5623) AS sf5623
FROM ld_customform15detail a
LEFT JOIN ld_customform15detailscf b ON b.lineitem_id=a.lineitem_id
WHERE a.id=@hetao_maxcrmid+1
AND b.sf5623>'1600-01-01'
GROUP BY a.id
) b ON b.id=a.customform15id
SET a.cf_5338=b.sf5623
WHERE a.cf_4517 IN ('维保','安装')
AND a.customform15id=@hetao_maxcrmid+1;-- 自动审批通过
INSERT INTO ld_approvelog(
id,
crmid,
description,
approveid,
stepid,approved_by,
approveresult,
createdtime,
nextstepid,
nextstepapproveby
)
SELECT
IFNULL((SELECT MAX(id) FROM ld_approvelog),0)+1,
@hetao_maxcrmid+1,
'销售合同审批通过自动生成并提交审批',
@hetao_approveid,
-1,tmp_form19_ownerid,
1,
NOW(),
@hetao_approvestepid,
@hetao_nextapproveby;INSERT INTO ld_approvelog(
id,
crmid,
description,
approveid,
stepid,approved_by,
approveresult,
createdtime,
nextstepid,
nextstepapproveby
)
SELECT
IFNULL((SELECT MAX(id) FROM ld_approvelog),0)+1,
@hetao_maxcrmid+1,
'审批人为空自动通过',
@hetao_approveid,
@hetao_approvestepid,tmp_form19_ownerid,
2,
NOW(),
NULL,
NULL;UPDATE ld_approvelog_seq SET id=(SELECT MAX(id) FROM ld_approvelog);
-- 审批状态改为“已批准”
UPDATE ld_customform15 SET approvestatus='已批准' WHERE customform15id=@hetao_maxcrmid+1;
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `hetao_resetinfo_after_form19_update`;
DELIMITER ;;
CREATE TRIGGER `hetao_resetinfo_after_form19_update` AFTER UPDATE ON `ld_customform19` FOR EACH ROW BEGINIF @disable_triggers IS NULL THEN
IF NEW.approvestatus='已批准' AND OLD.approvestatus<>'已批准' THEN
CALL hetao_autoaddform15_byform19(NEW.customform19id,NEW.smownerid);
END IF;
END IF;END
;;
DELIMITER ;