一、demo脚本
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`='Expense' LIMIT 1);
SET @trigger_fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @effect_fieldid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname='available_budget' LIMIT 1);
UPDATE ld_related_field SET fromvalue_info="SELECT IFNULL(
ROUND(IFNULL((
SELECT
SUM(b.budget_amount)
FROM ld_expensebudget a
LEFT JOIN ld_expensebudgetdetail b ON b.id=a.expensebudgetid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND a.budget_start_date<=CURDATE()
AND YEAR(a.budget_start_date)=YEAR(CURDATE())
AND b.lin_users_id=?
),0),2)-
ROUND(IFNULL((
SELECT
SUM(c.rmb_total)
FROM ld_expense a
LEFT JOIN ld_expensedetail b ON b.expenseid=a.expenseid
LEFT JOIN ld_expensedetailscf c ON c.expensedetailid=b.expensedetailid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND YEAR(a.createdtime)=YEAR(CURDATE())
AND a.expenseid<>'{Record.ID}'
AND a.smownerid=?
),0),2)
,0) AS tmp_value"
WHERE trigger_fieldid=@trigger_fieldid AND effect_fieldid=@effect_fieldid AND fromvalue_type='sql';UPDATE ld_validatasave SET `status`='active',fieldname="SELECT
ROUND(IFNULL((
SELECT
SUM(b.budget_amount)
FROM ld_expensebudget a
LEFT JOIN ld_expensebudgetdetail b ON b.id=a.expensebudgetid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND a.budget_start_date<=CURDATE()
AND YEAR(a.budget_start_date)=YEAR(CURDATE())
AND b.lin_users_id='{ld_expense.assigned_user_id}'
),0),2)-
ROUND(IFNULL((
SELECT
SUM(c.rmb_total)
FROM ld_expense a
LEFT JOIN ld_expensedetail b ON b.expenseid=a.expenseid
LEFT JOIN ld_expensedetailscf c ON c.expensedetailid=b.expensedetailid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND YEAR(a.createdtime)=YEAR(CURDATE())
AND a.expenseid<>'{Record.ID}'
AND a.smownerid='{ld_expense.assigned_user_id}'
),0),2)
AS tmp_value"
WHERE module='Expense' AND tipzh like '%{line}%' AND relatedtable='ld_tabdetailfield' AND fieldname LIKE '%budget_amount%';