131 lines
4.4 KiB
Plaintext
131 lines
4.4 KiB
Plaintext
DELIMITER //
|
|
|
|
DROP FUNCTION IF EXISTS orm_page_where_v2;
|
|
CREATE FUNCTION orm_page_where_v2(
|
|
tablename VARCHAR(200), -- 表名
|
|
pk VARCHAR(100), -- 主键(用于更新删除)
|
|
where_sql TEXT, -- 原始 WHERE 条件
|
|
userid VARCHAR(100) -- 用户ID
|
|
)
|
|
RETURNS TEXT
|
|
DETERMINISTIC
|
|
/*
|
|
Function : orm_api_v2 [分頁查詢] where 條件判斷的核心程式, 智慧的組出相應的 sql, 被 orm_api_page_v2 呼叫使用
|
|
Description:
|
|
Build Date : 2024-06-20
|
|
Modify History:
|
|
1 Michael 2023/12/16 原始版本
|
|
2 [优化版] 2024-06-20 MariaDB优化版本
|
|
*/
|
|
BEGIN
|
|
DECLARE wheresql TEXT;
|
|
DECLARE limit_s_date VARCHAR(100);
|
|
DECLARE limit_e_date VARCHAR(100);
|
|
DECLARE limit_field VARCHAR(100);
|
|
DECLARE limit_field_d VARCHAR(100);
|
|
DECLARE limit_filter_master TEXT;
|
|
DECLARE limit_table_d VARCHAR(1000);
|
|
DECLARE limit_filter_detail TEXT;
|
|
DECLARE limit_filter_date VARCHAR(200);
|
|
DECLARE maxcount INT DEFAULT 0;
|
|
DECLARE new_billno VARCHAR(100);
|
|
DECLARE comma_pos INT;
|
|
DECLARE emp1, emp2, emp3 VARCHAR(200);
|
|
|
|
-- 初始化 WHERE 子句
|
|
SET wheresql = IF(where_sql = '', ' WHERE 1=1 ', where_sql);
|
|
|
|
-- 获取高级查询限制配置
|
|
SELECT
|
|
limit_field, limit_table_d, limit_field_d, limit_s_date, limit_e_date
|
|
INTO
|
|
limit_field, limit_table_d, limit_field_d, limit_s_date, limit_e_date
|
|
FROM sys_menutable
|
|
WHERE limit_table = tablename
|
|
AND islimitdata = 'Y'
|
|
LIMIT 1;
|
|
|
|
-- 如果存在限制字段且用户已登录
|
|
IF limit_field IS NOT NULL AND userid <> '' THEN
|
|
-- 检查是否有全局权限 (*)
|
|
SELECT COUNT(*) INTO maxcount
|
|
FROM basperson_share
|
|
WHERE personid = userid
|
|
AND share_personid = '*';
|
|
|
|
-- 如果没有全局权限，则添加个人限制条件
|
|
IF maxcount = 0 THEN
|
|
-- 初始化过滤条件
|
|
SET limit_filter_master = '';
|
|
SET limit_filter_detail = '';
|
|
SET limit_filter_date = '';
|
|
|
|
-- 添加日期范围限制
|
|
IF limit_s_date <> '' AND limit_e_date <> '' THEN
|
|
SET limit_filter_date = CONCAT(
|
|
' AND ', limit_s_date, ' <= CURDATE()',
|
|
' AND ', limit_e_date, ' >= CURDATE()'
|
|
);
|
|
END IF;
|
|
|
|
-- 添加明细表限制
|
|
IF limit_field_d <> '' THEN
|
|
SET limit_filter_detail = CONCAT(
|
|
' OR ', pk, ' IN (',
|
|
'SELECT ', pk, ' FROM ', limit_table_d,
|
|
' WHERE ', limit_field_d, ' = ', QUOTE(userid),
|
|
limit_filter_date, ')'
|
|
);
|
|
END IF;
|
|
|
|
-- 处理多字段限制
|
|
SET comma_pos = LOCATE(',', limit_field);
|
|
IF comma_pos > 0 THEN
|
|
-- 拆分字段列表
|
|
SET emp1 = SUBSTRING_INDEX(limit_field, ',', 1);
|
|
SET emp2 = SUBSTRING_INDEX(SUBSTRING_INDEX(limit_field, ',', 2), ',', -1);
|
|
SET emp3 = IF(LOCATE(',', limit_field, LENGTH(emp1) + LENGTH(emp2) + 3) > 0,
|
|
SUBSTRING_INDEX(limit_field, ',', -1),
|
|
NULL);
|
|
|
|
-- 构建多字段限制条件
|
|
SET wheresql = CONCAT(wheresql, ' AND (');
|
|
SET wheresql = CONCAT(wheresql, emp1, ' IN (SELECT share_personid FROM basperson_share WHERE personid = ', QUOTE(userid), ')');
|
|
|
|
IF emp2 <> '' THEN
|
|
SET wheresql = CONCAT(wheresql, ' OR ', emp2, ' IN (SELECT share_personid FROM basperson_share WHERE personid = ', QUOTE(userid), ')');
|
|
END IF;
|
|
|
|
IF emp3 IS NOT NULL THEN
|
|
SET wheresql = CONCAT(wheresql, ' OR ', emp3, ' IN (SELECT share_personid FROM basperson_share WHERE personid = ', QUOTE(userid), ')');
|
|
END IF;
|
|
|
|
SET wheresql = CONCAT(wheresql, limit_filter_date, ')', limit_filter_detail);
|
|
ELSE
|
|
-- 单字段限制条件
|
|
SET limit_filter_master = CONCAT(
|
|
' AND ', limit_field, ' IN (',
|
|
'SELECT share_personid FROM basperson_share WHERE personid = ', QUOTE(userid), ')',
|
|
limit_filter_date
|
|
);
|
|
SET wheresql = CONCAT(wheresql, limit_filter_master, limit_filter_detail);
|
|
END IF;
|
|
END IF;
|
|
END IF;
|
|
|
|
/* 新增记录后只显示刚添加的数据 */
|
|
SELECT billno INTO new_billno
|
|
FROM sys_new_billno
|
|
WHERE tablename = tablename
|
|
AND login_user = userid
|
|
AND create_time >= NOW() - INTERVAL 30 SECOND
|
|
LIMIT 1;
|
|
|
|
IF new_billno IS NOT NULL THEN
|
|
SET wheresql = CONCAT(wheresql, ' AND billno = ', QUOTE(new_billno));
|
|
END IF;
|
|
|
|
RETURN wheresql;
|
|
END //
|
|
|
|
DELIMITER ; |