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 ;