一、demo脚本
-- 业绩划分表 成员修改为 选择用户
UPDATE `ld_tabdetailfield`
SET
`columnname`='lin_users_id',
`allowhide`='yes',
`tablename`='ld_customform30detail',
`fieldlabel`='成员',
`fieldtype`='Reference',
`typeofdata`='V~O',
`edit_permission`='0',
`input_type`='0',
`referencemodule`='Users',
`datarange`='all',
`approve_demand`='none',
`maxrowsshow`='100',
`bigtype`='text',
`savedatabase`='yes'
WHERE (`tabdetailfieldid`='721');CALl AddColumn('ld_customform30detail','lin_users_id','INT','19',0);
-- 根据用户ID自动生成上个月的业绩单
DROP PROCEDURE IF EXISTS `yuejin_addform26_byuserid`;
delimiter ||
CREATE PROCEDURE `yuejin_addform26_byuserid`(
tmp_userid INT(19)
)
BEGIN
SET @tmp_userid=tmp_userid;
SET @start_time=DATE_FORMAT(DATE_SUB(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY),INTERVAL 1 MONTH),'%Y-%m-%d 00:00:00');
SET @end_time=DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY),'%Y-%m-%d 23:59:59');-- 当前用户还没有创建过“月业绩表”的时候创建
IF NOT EXISTS (SELECT 1 FROM ld_customform26 WHERE deleted=0 AND createdtime BETWEEN @start_time AND @end_time AND smownerid=@tmp_userid) THEN
-- 当前用户的业绩数据写入临时表DROP TEMPORARY TABLE IF EXISTS yuejin_yejiinfo;
CREATE TEMPORARY TABLE yuejin_yejiinfo AS
SELECT
a.id,
b.order_id,
b.rate_ht,
b.yeji_ht,
b.rate_sk,
b.yeji_sk
FROM ld_users a
LEFT JOIN (
SELECT
a.id,
a.order_id,
MAX(IF(a.yejitype='order',a.rate,0)) AS rate_ht,
MAX(IF(a.yejitype='order',a.yeji,0)) AS yeji_ht,
MAX(IF(a.yejitype='shoukuan',a.rate,0)) AS rate_sk,
MAX(IF(a.yejitype='shoukuan',a.yeji,0)) AS yeji_sk
FROM (
SELECT
a.id,
b.order_id,
ROUND(b.rate_ht,2) AS rate,
ROUND(b.total*b.rate_ht*0.01,2) AS yeji,
b.chengdan_type,
'order' AS yejitype
FROM ld_users a
INNER JOIN (
SELECT
a.salesorderid AS order_id,
a.ownerid,
IF(a.chengdan_type='独自成单',100,a.rate_ht) AS rate_ht,
a.total,
a.chengdan_type
FROM (
SELECT
a.salesorderid,
a.total,
CASE
WHEN b.cf_8801 LIKE '%,%' THEN c.lin_users_id
WHEN b.cf_8801=a.smownerid OR IFNULL(b.cf_8801,'')+0=0 THEN a.smownerid
ELSE c.lin_users_id
END AS ownerid,
c.rate_ht,
CASE
WHEN b.cf_8801 LIKE '%,%' THEN '团队成单'
WHEN b.cf_8801=a.smownerid OR IFNULL(b.cf_8801,'')+0=0 THEN '独自成单'
ELSE '团队成单'
END AS chengdan_type
FROM ld_salesorder a
LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
LEFT JOIN (
SELECT
a.salesorderid,
b.lin_users_id,
c.sf726 AS rate_ht
FROM ld_customform30 a
LEFT JOIN ld_customform30detail b ON b.id=a.customform30id
LEFT JOIN ld_customform30detailscf c ON c.lineitem_id=b.lineitem_id
WHERE a.deleted=0
GROUP BY a.salesorderid,b.lin_users_id
) c ON c.salesorderid=a.salesorderid
WHERE a.deleted=0
AND a.approvestatus='已批准'
AND a.approvetime BETWEEN @start_time AND @end_time
) a
GROUP BY a.salesorderid,a.ownerid
) b ON b.ownerid=a.id
WHERE a.id=@tmp_userid
UNION ALL
SELECT
a.id,
c.order_id,
ROUND(c.rate_sk,2) AS rate,
ROUND(c.total*c.rate_sk*0.01,2) AS yeji,
c.chengdan_type,
'shoukuan' AS yejitype
FROM ld_users a
INNER JOIN (
SELECT
a.salesorderid AS order_id,
a.ownerid,
IF(a.chengdan_type='独自成单',100,a.rate_sk) AS rate_sk,
SUM(a.amount) AS total,
a.chengdan_type
FROM (
SELECT
a.cashbillid,
b.entitytypeid AS salesorderid,
b.amount,
CASE
WHEN e.cf_8801 LIKE '%,%' THEN c.lin_users_id
WHEN e.cf_8801=d.smownerid OR IFNULL(e.cf_8801,'')+0=0 THEN d.smownerid
ELSE c.lin_users_id
END AS ownerid,
c.rate_sk,
CASE
WHEN e.cf_8801 LIKE '%,%' THEN '团队成单'
WHEN e.cf_8801=d.smownerid OR IFNULL(e.cf_8801,'')+0=0 THEN '独自成单'
ELSE '团队成单'
END AS chengdan_type
FROM ld_cashbill a
LEFT JOIN ld_cashbill2paymentplan b ON b.CashBillID=a.cashbillid
LEFT JOIN (
SELECT
a.salesorderid,
b.lin_users_id,
c.sf727 AS rate_sk
FROM ld_customform30 a
LEFT JOIN ld_customform30detail b ON b.id=a.customform30id
LEFT JOIN ld_customform30detailscf c ON c.lineitem_id=b.lineitem_id
WHERE a.deleted=0
GROUP BY a.salesorderid,b.lin_users_id
) c ON c.salesorderid=b.entitytypeid
LEFT JOIN ld_salesorder d ON d.salesorderid=b.entitytypeid
LEFT JOIN ld_salesordercf e ON e.salesorderid=d.salesorderid
WHERE a.deleted=0
AND b.entitytypeid>0
AND b.entitytype='SalesOrder'
AND a.approvestatus='已批准'
AND a.approvetime BETWEEN @start_time AND @end_time
) a
GROUP BY a.salesorderid,a.ownerid
) c ON c.ownerid=a.id
WHERE a.profileid IN (SELECT profileid FROM ld_profile WHERE profilename LIKE '%销售%')
) a
GROUP BY a.id,a.order_id
) b ON b.id=a.id
WHERE a.id=@tmp_userid;-- 获取自动创建单据的 ID
SET @autoadd_maxcrmid=(SELECT MAX(crmid) FROM ld_crmentity);
INSERT INTO ld_crmentity(crmid,setype)
SELECT @autoadd_maxcrmid+1,'CustomForm26';UPDATE ld_crmentity_seq SET id=(SELECT MAX(crmid) FROM ld_crmentity);
-- 插入主表
INSERT INTO ld_customform26(
customform26id,
customform26_no,smownerid,
smcreatorid,
modifiedby,
createdtime,
modifiedtime
)
SELECT
@autoadd_maxcrmid+1,
(SELECT CONCAT(prefix,cur_id+0) FROM ld_modentity_num WHERE semodule='Customform26' AND active=1),@tmp_userid,
1,
1,
@end_time,
@end_time;-- 插入自定义表
INSERT INTO ld_customform26scf(customform26id) SELECT @autoadd_maxcrmid+1;-- 更新单据流水
UPDATE ld_modentity_num SET cur_id=cur_id+1 WHERE semodule='Customform26' AND active=1;
-- 插入分录明细主表
INSERT INTO ld_customform26detail(
id,
entitytype,
entitytypeid,rate,
amount
)
SELECT
@autoadd_maxcrmid+1,
'SalesOrder',
order_id,
rate_ht,
IFNULL(yeji_ht,0)+IFNULL(yeji_sk,0)
FROM yuejin_yejiinfo
WHERE id=@tmp_userid;-- 插入分录明细自定义表
INSERT INTO ld_customform26detailscf(
lineitem_id,
sf730,
sf731,
sf732
)
SELECT
a.lineitem_id,
b.yeji_ht,
b.rate_sk,
b.yeji_sk
FROM ld_customform26detail a
LEFT JOIN yuejin_yejiinfo b ON b.order_id=a.entitytypeid
WHERE a.id=@autoadd_maxcrmid+1
AND b.id=@tmp_userid;-- 更新分录明细的“合同金额”和“已收金额”
UPDATE ld_customform26detail a
LEFT JOIN ld_salesorder b ON b.salesorderid=a.entitytypeid
SET
a.source_total=b.total,
a.received_amount=b.received_amount
WHERE a.id=@autoadd_maxcrmid+1
AND a.entitytypeid>0
AND b.salesorderid>0;-- 更新单据头的合计、小计
UPDATE ld_customform26 a
LEFT JOIN (
SELECT
id,
SUM(amount) AS total
FROM ld_customform26detail
GROUP BY id
) b ON b.id=a.customform26id
SET
a.total=b.total,
a.subtotal=b.total
WHERE a.customform26id=@autoadd_maxcrmid+1;END IF;
end ||
delimiter;
-- 角色包含“销售”的用户自动生成业绩表
DROP PROCEDURE IF EXISTS `yuejin_autoadd_yeji`;
delimiter ||
CREATE PROCEDURE `yuejin_autoadd_yeji`(
)
BEGINDECLARE tmp_userid INT(19) DEFAULT 0;
DECLARE MYCUSOR CURSOR FOR
SELECT
a.id
FROM ld_users a
LEFT JOIN ld_profile b ON b.profileid=a.profileid
WHERE b.profilename LIKE '%销售%'
AND a.status='Active';DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmp_userid = NULL;
OPEN MYCUSOR;
FETCH MYCUSOR INTO tmp_userid;
WHILE(tmp_userid IS NOT NULL) DO
CALL yuejin_addform26_byuserid(tmp_userid);
FETCH MYCUSOR INTO tmp_userid;
END WHILE;
CLOSE MYCUSOR;END ||
delimiter;
-- 添加事件调用存储过程，每月1号早上8点执行
DROP EVENT IF EXISTS `yuejin_autoadd_yeji_event`;
delimiter ||
CREATE EVENT `yuejin_autoadd_yeji_event` ON SCHEDULE EVERY 1 MONTH STARTS '2024-03-01 08:00:01' ON COMPLETION NOT PRESERVE ENABLE DO
CALL yuejin_autoadd_yeji();
||
delimiter;-- 执行一次
CALL yuejin_autoadd_yeji();