| 作者 |
|
fwj3861 [个人空间] QQ名 越南-叶子(121305301)
    注册  2010-09-09 发贴数 633 精华贴 10 原创贴 20 来自 越南 状态 正常
级别 贵宾 |
|
|
由于经常需要提取几万条以上的单据明细报表,例如客户订单明细,按公司要求添加了快捷查询菜单,只要输入或修改起始日期和结束日期,就可以提取日期范围内的单据明细,测试提取32883条明细数据用时47秒,初始脚本提交给AI分析,根据AI给出的建议,取消循环进行字典转换,提取效率提升了好几倍 下面是最终版本的脚本 int test() { return 1; } int main() { // ========================================== // 1. 变量声明与初始化 // ========================================== int count, i, counts, j; string sql, v_djlsh, uid, uname, m_dqrq, m_dqsk, m_kssk, m_jssk, m_bz, m_ksrq, m_jsrq, m_ksrqs, m_jsrqs, m_khbh, m_khbhs, m_khbhss, v_has_power, sql_where, m_tqfm; num m_dqsks, m_jssks, m_tqsj, m_tqfz, m_tqms,m_tqmss; uid = get_user_id(); //获取登陆账号,报表标题调用 uname = get_user_name(); //获取登陆姓名,报表标题调用 m_dqrq = fmt_date(get_serv_date(), 'yyyy-mm-dd'); //获取当前日期,报表标题调用 m_dqsk = fmt_time(get_serv_time(), 'hh分mi秒'); //获取当前时刻,报表标题调用 m_kssk = fmt_time(get_serv_time(), 'hh:mi:ss'); //获取执行开始时刻,弹出内容调用 m_dqsks = time_to_second(get_serv_time()); //执行开始时刻转换成秒数 m_ksrq = inp_date("开始日期:", get_serv_date()); //选择开始日期 m_jsrq = inp_date("结束日期:", get_serv_date()); //选择结束日期 m_ksrqs = fmt_date(m_ksrq, 'yyyy-mm-dd'); //格式化开始日期,弹出内容调用 m_jsrqs = fmt_date(m_jsrq, 'yyyy-mm-dd'); //格式化结束日期,弹出内容调用 // ========================================== // 2. 核心权限控制逻辑 // ========================================== db_run("select 1 from mup_user_bo where bo_id='eba_service' and user_id='" + uid + "'"); if(db_row_count() > 0) { v_has_power = 1; } else { v_has_power = 0; } // ========================================== // 3. 审计提示字符串剪裁(防止大用户量下弹窗溢出崩溃) // ========================================== m_khbhss = ""; if(v_has_power == 1) { db_run("select eba_id from eba where service_id in (select val from mup_user_bo where bo_id='eba_service' and user_id='" + uid + "')"); count = db_row_count(); if(count > 30) { m_khbhss = "当前用户管辖的【" + count + "】个客户"; } else { for(i = 0; i < count; i++) { db_go_row(i); if(m_khbhss == "") { m_khbhss = db_res(0); } else { m_khbhss = m_khbhss + "," + db_res(0); } } } } // ========================================== // 4. 构建高强度多表联合报表查询 SQL // ========================================== sql = "select d.voucher_no AS 订单单据编号,STUFF(STUFF(CAST(d.voucher_date AS VARCHAR(10)), 7, 0, '-'), 5, 0, '-') AS 单据日期,g.user_name as 制单人,i.name as 单据状态,h.user_name as 审核人," sql+= " e.eba_id AS 客户编号, j.name as 业务员,a.vr_item_ext_2 AS 内部订号, T.djlx AS 订单类型, T.gdh AS 工单号, T.yggdh AS 预告工单号, a.vr_item_ext_1 AS 下单日期,"; sql+= " STUFF(STUFF(CAST(a.item_date_lmt AS VARCHAR(10)), 7, 0, '-'), 5, 0, '-') AS 交付日期, a.vr_item_ext_3 AS 客户采购单号,a.vr_item_ext_4 AS 型体, a.vr_item_ext_5 AS 项次, "; sql+= " T.khzl AS 客户指令, T.qhdh AS 企划单号, T.qydh AS 企业单号, T.khwldm AS 客户物料代码, a.res_id AS 内部料号, a.vr_item_ext_6 AS 品名描述, a.sub_attr_val_1 AS 规格, T.color AS 通用色号,"; sql+= " T.nbsh AS 内部色号, T.sfdqry AS 是否打确认样, T.sffhx AS 是否防虹吸, T.dgjp AS 订购胶片, T.zdys AS 织带印刷, a.inp_num AS 订单数量,ISNULL(Ship.total_ship_num, 0) AS 已出货数量, "; sql+= " f.name AS 订单单位, (a.inp_num - ISNULL(Ship.total_ship_num, 0)) AS 待出货数量, a.note_info AS 备注, T.khddbz AS 客户订单备注 "; sql+= "FROM ebs_vr_item a "; // 关联出货情况 sql+= "LEFT JOIN ( "; sql+= " SELECT b.ref_voucher_id, b.ref_item_id, SUM(b.inp_num) AS total_ship_num "; sql+= " FROM ebs_vr_item b "; sql+= " INNER JOIN ebs_v c ON c.voucher_id = b.voucher_id "; sql+= " WHERE c.voucher_type = 'BB' AND b.is_main = 'N' AND b.inp_num > 0 ";sql+= " GROUP BY b.ref_voucher_id, b.ref_item_id "; sql+= ") AS Ship ON a.voucher_id = Ship.ref_voucher_id AND a.item_id = Ship.ref_item_id "; // PIVOT 行转列动态展平 sql+= "LEFT JOIN ( "; sql+= " SELECT attr_id, CONVERT(NVARCHAR(500), attr_val) AS attr_val, item_id, voucher_id FROM ebs_vr_item_attr "; sql+= ") AS S PIVOT ( "; sql+= " MAX(s.attr_val) FOR S.attr_id IN ([khwldm],[color],[nbsh],[sfdqry],[dgjp],[zdys],[sffhx],[pinpai],[djlx],[changdu],[yggdh],[ddbz],[khddbz],[gdh],[jijie],[qhdh],[qydh],[khzl]) "; sql+= ") AS T ON a.voucher_id = T.voucher_id AND a.item_id = T.item_id "; sql+= "LEFT JOIN ebs_v d ON d.voucher_id = a.voucher_id "; sql+= "LEFT JOIN ebs_vr e ON e.voucher_id = a.voucher_id "; // 字典编码转换名称 sql+= "LEFT JOIN (SELECT code,name FROM app_dict_def where dict_id='res_unit_type') AS f on f.code=a.inp_unit_type_id "; //订单单位 sql+= "LEFT JOIN (SELECT user_id,user_name FROM mup_user) AS g on g.user_id=d.create_user_id "; //制单人 sql+= "LEFT JOIN (SELECT user_id,user_name FROM mup_user) AS h on h.user_id=d.check_user_id "; //审核人 sql+= "LEFT JOIN (SELECT code,name FROM app_dict_def where dict_id='voucher_state') AS i on i.code=d.state "; //单据状态 sql+= "LEFT JOIN (SELECT emp_id,name FROM emp) AS j on j.emp_id=d.emp_id "; //业务员 // 基础过滤拦截 sql_where = "WHERE a.inp_num > 0 AND d.voucher_type = 'BA' AND d.voucher_date >= '" + m_ksrq + "' AND d.voucher_date <= '" + m_jsrq + "' "; // 注入高效权限隔离条件 if(v_has_power == 1) { sql_where += "AND e.eba_id IN (SELECT eba_id FROM eba WHERE service_id IN (SELECT val FROM mup_user_bo WHERE bo_id='eba_service' AND user_id='" + uid + "')) "; } sql_where += "ORDER BY d.voucher_no, a.item_id;"; // 执行报表拉取 db_run(sql + sql_where); counts = db_row_count(); if(counts == 0) { msg("【" + m_ksrqs + "】-【" + m_jsrqs + "】期间没有订单明细数据"); return 1; } // ========================================== // 6. 弹出报表及效率审计计算 // ========================================== if(m_khbhss == "" && counts > 0) { db_show_m("操作员【" + uid + " " + uname + "】于【" + m_dqrq + " " + m_dqsk + "】查询所有客户【" +m_ksrqs + "】-【"+ m_jsrqs + "】的订单明细"); } if(m_khbhss != "" && counts > 0) { db_show_m("操作员【" + uid + " " + uname + "】于【" + m_dqrq + " " + m_dqsk + "】查询客户编号【" + m_khbhss + "】【" + m_ksrqs + "】-【" + m_jsrqs + "】的订单明细"); } m_jssks = time_to_second(get_serv_time()); //执行结束时刻转换成秒数,计算执行时间调用 m_jssk = fmt_time(get_serv_time(), 'hh:mi:ss'); //格式化执行结束时刻,弹出内容调用 m_tqsj = m_jssks - m_dqsks; //计算执行时间,秒数 m_tqfz = to_int(m_tqsj / 60); //执行秒数转换成分数整数 m_tqmss = (m_tqsj / 60 - to_int(m_tqsj / 60)) * 60; //计算执行分钟数剩余的秒数,含小数点 m_tqms = to_int((m_tqsj / 60 - to_int(m_tqsj / 60)) * 60); //取执行分钟数剩余的秒数整数 if(m_tqmss > m_tqms) //当执行分钟数剩余的秒数有小数点时,秒数+1 { m_tqms = m_tqms + 1; } if(m_tqfz == 0) { m_tqfm = m_tqsj + '秒'; } if(m_tqfz > 0) { m_tqfm = m_tqfz + '分' + m_tqms + '秒';} msg('本次共提取【' + counts + '】条记录,从【' + m_kssk + '】开始执行,到【' + m_jssk + '】结束,用时【' + m_tqfm + '】'); return 1; } AI分析如下 这段脚本是一段非常典型的 ERP 报表自定义脚本(常见于用友、金蝶或其他低代码 ERP 的二次开发脚本中)。它主要实现了带权限隔离的订单明细报表查询,并附带了详细的执行效率审计(耗时计算)与日志提示功能。以下是对该脚本的深度解析与逻辑梳理:核心功能拆解 ┌────────────────────────────────────────────────────────┐ │ 1. 变量初始化 & 用户输入 (获取当前时间、操作员信息、起止日期) │ └───────────────────────────┬────────────────────────────┘ │ ┌───────────────────────────▼────────────────────────────┐ │ 2. 权限判定 (检查当前用户在 mup_user_bo 中是否有管辖权限) │ └───────────────────────────┬───────────────────────────-─┘ │ ┌───────────────────────────▼────────────────────────────┐ │ 3. 审计提示剪裁 (少于30个客户罗列编码,超过30个只显示总数) │ └───────────────────────────┬──────────────────────────-──┘ │ ┌───────────────────────────▼────────────────────────────┐ │ 4. 动态 SQL 构建 (行转列 PIVOT、多表关联、出货情况计算) │ └───────────────────────────┬────────────────────────────-┘ │ ┌───────────────────────────▼────────────────────────────┐ │ 5. 数据执行 & 时间审计 (计算精确到秒的耗时并弹出完成提示) │ └───────────────────────────────────────────────────────-─┘ 关键技术点与亮点 1. 高效的权限隔离与防崩溃设计 防弹窗溢出崩溃:在第 3 部分中,脚本会查询当前用户管辖的客户数量。如果客户数 > 30,为了防止字符串拼接过长导致前端 db_show_m 弹窗文本溢出崩溃,脚本会自动将其转换为概括性文字:"当前用户管辖的【X】个客户"。 动态权限注入:在 SQL 的 WHERE 子句中,如果 v_has_power == 1,则通过子查询强制过滤 e.eba_id,实现了精准的数据行级权限隔离。 2. 高强度的复杂 SQL 架构 时间格式规整:使用 STUFF(STUFF(CAST(...))) 嵌套函数,将数据库中可能存储为整型或连续字符串的日期(如 20231024)动态格式化为标准形如 2023-10-24 的显示格式。 动态展平(PIVOT 行转列):由于客户物料、颜色、通用色号等属性在 ebs_vr_item_attr 表中是以“键-值”行数据的形式存储的,脚本使用 SQL 的 PIVOT 函数将多行属性动态转换为了列,大幅提升了报表的可读性。 出货结存计算:通过 LEFTJOIN 一个聚合子查询 Ship,实时计算已出货数量,并在主查询中通过 (a.inp_num - ISNULL(Ship.total_ship_num, 0)) 动态算出待出货数量。 3. 严谨的效率审计算法 在脚本尾部,利用开始与结束的秒数差值计算耗时。 为了解决浮点数精度丢失问题,脚本专门设计了向上取整的逻辑: pascalif(m_tqmss > m_tqms) { m_tqms = m_tqms + 1 ; } 请谨慎使用此类代码。 最终将耗时智能格式化为 "X秒" 或 "X分X秒" 呈现给用户。 潜在的隐患与优化建议 虽然脚本逻辑完整,但在大规模生产环境中仍有几个细节需要注意: SQL 注入风险:SQL 拼接中直接使用了 + m_ksrq + 和 + m_jsrq +。如果这两个日期输入框允许操作员手动输入任意文本,将存在 SQL 注入风险。建议确保 inp_date 控件在前端有严格的日期格式校验。 PIVOT 性能瓶颈: ebs_vr_item_attr 在进行 PIVOT 转换时,如果数据量极其庞大(百万级以上),可能会导致报表查询变慢。建议确保该表的 item_id 和 attr_id 上建有复合索引。 多表联合查询的索引: 确保 ebs_vr_item 表的 voucher_id、ebs_v 表的 voucher_date 与 voucher_type 建立了索引,否则在日期跨度较长时容易引发全表扫描。 ================================================================================== 如果需要提取待出货明细,只需要更新这2个判断条件(订单数量大于已出货合计数量),并修改标题日期内容 if(v_has_power == 1) { sql_where += "AND e.eba_id IN (SELECT eba_id FROM eba WHERE service_id IN (SELECT val FROM mup_user_bo WHERE bo_id='eba_service' AND user_id='" + uid + "')) "; sql_where += " AND a.inp_num > ISNULL(Ship.total_ship_num, 0) "; //只显示订单数量大于已发货合计数量 } sql_where += " AND a.inp_num > ISNULL(Ship.total_ship_num, 0) "; //只显示订单数量大于已发货合计数量 sql_where += "ORDER BY d.voucher_no, a.item_id"; |
|
 |
于 2026-07-27 16:31 被 fwj3861 修改

官方认证第三方服务团队 (越南-叶子) QQ:121305301 TEL:+84869350626
承接越南北部-河内周边省份-OIT部署和维护服务(24小时上门或远程服务)
服务介绍:http://www.onlyit.cn/onlyit_service.html
|
|
fwj3861 [个人空间] QQ名 越南-叶子(121305301)
    注册  2010-09-09 发贴数 633 精华贴 10 原创贴 20 来自 越南 状态 正常
级别 贵宾 |
|
|
查询指定日期范围内的待出货的订单明细脚本: int test() { return 1; } int main() { // ========================================== // 1. 变量声明与初始化 // ========================================== int count, i, counts, j; string sql, v_djlsh, uid, uname, m_dqrq, m_dqsk, m_kssk, m_jssk, m_bz, m_ksrq, m_jsrq, m_ksrqs, m_jsrqs, m_khbh, m_khbhs, m_khbhss, v_has_power, sql_where, m_tqfm; num m_dqsks, m_jssks, m_tqsj, m_tqfz, m_tqms,m_tqmss; uid = get_user_id(); //获取登陆账号,报表标题调用 uname = get_user_name(); //获取登陆姓名,报表标题调用 m_dqrq = fmt_date(get_serv_date(), 'yyyy-mm-dd'); //获取当前日期,报表标题调用 m_dqsk = fmt_time(get_serv_time(), 'hh分mi秒'); //获取当前时刻,报表标题调用 m_kssk = fmt_time(get_serv_time(), 'hh:mi:ss'); //获取执行开始时刻,弹出内容调用 m_dqsks = time_to_second(get_serv_time()); //执行开始时刻转换成秒数 m_ksrq = inp_date("开始日期:", get_serv_date()); //选择开始日期 m_jsrq = inp_date("结束日期:", get_serv_date()); //选择结束日期 m_ksrqs = fmt_date(m_ksrq, 'yyyy-mm-dd'); //格式化开始日期,弹出内容调用 m_jsrqs = fmt_date(m_jsrq, 'yyyy-mm-dd'); //格式化结束日期,弹出内容调用 // ========================================== // 2. 核心权限控制逻辑 // ========================================== db_run("select 1 from mup_user_bo where bo_id='eba_service' and user_id='" + uid + "'"); if(db_row_count() > 0) { v_has_power = 1; } else { v_has_power = 0; } // ========================================== // 3. 审计提示字符串剪裁(防止大用户量下弹窗溢出崩溃) // ========================================== m_khbhss = ""; if(v_has_power == 1) { db_run("select eba_id from eba where service_id in (select val from mup_user_bo where bo_id='eba_service' and user_id='" + uid + "')"); count = db_row_count(); if(count > 30) { m_khbhss = "当前用户管辖的【" + count + "】个客户"; } else { for(i = 0; i < count; i++) { db_go_row(i); if(m_khbhss == "") { m_khbhss = db_res(0); } else { m_khbhss = m_khbhss + "," + db_res(0); } } } } // ========================================== // 4. 构建高强度多表联合报表查询 SQL // ========================================== sql = "select d.voucher_no AS 订单单据编号,STUFF(STUFF(CAST(d.voucher_date AS VARCHAR(10)), 7, 0, '-'), 5, 0, '-') AS 单据日期,g.user_name as 制单人,i.name as 单据状态,h.user_name as 审核人," sql+= " e.eba_id AS 客户编号, j.name as 业务员,a.vr_item_ext_2 AS 内部订号, T.djlx AS 订单类型, T.gdh AS 工单号, T.yggdh AS 预告工单号, a.vr_item_ext_1 AS 下单日期,"; sql+= " STUFF(STUFF(CAST(a.item_date_lmt AS VARCHAR(10)), 7, 0, '-'), 5, 0, '-') AS 交付日期, a.vr_item_ext_3 AS 客户采购单号,a.vr_item_ext_4 AS 型体, a.vr_item_ext_5 AS 项次, "; sql+= " T.khzl AS 客户指令, T.qhdh AS 企划单号, T.qydh AS 企业单号, T.khwldm AS 客户物料代码, a.res_id AS 内部料号, a.vr_item_ext_6 AS 品名描述, a.sub_attr_val_1 AS 规格, T.color AS 通用色号,"; sql+= " T.nbsh AS 内部色号, T.sfdqry AS 是否打确认样, T.sffhx AS 是否防虹吸, T.dgjp AS 订购胶片, T.zdys AS 织带印刷, a.inp_num AS 订单数量,ISNULL(Ship.total_ship_num, 0) AS 已出货数量, "; sql+= " f.name AS 订单单位, (a.inp_num - ISNULL(Ship.total_ship_num, 0)) AS 待出货数量, a.note_info AS 备注, T.khddbz AS 客户订单备注 "; sql+= "FROM ebs_vr_item a "; // 关联出货情况 sql+= "LEFT JOIN ( "; sql+= " SELECT b.ref_voucher_id, b.ref_item_id, SUM(b.inp_num) AS total_ship_num "; sql+= " FROM ebs_vr_item b "; sql+= " INNER JOIN ebs_v c ON c.voucher_id = b.voucher_id "; sql+= " WHERE c.voucher_type = 'BB' AND b.is_main = 'N' AND b.inp_num > 0 "; sql+= " GROUP BY b.ref_voucher_id, b.ref_item_id "; sql+= ") AS Ship ON a.voucher_id = Ship.ref_voucher_id AND a.item_id = Ship.ref_item_id "; // PIVOT 行转列动态展平 sql+= "LEFT JOIN ( "; sql+= " SELECT attr_id, CONVERT(NVARCHAR(500), attr_val) AS attr_val, item_id, voucher_id FROM ebs_vr_item_attr "; sql+= ") ASS PIVOT ( "; sql+= " MAX(s.attr_val) FOR S.attr_id IN ([khwldm],[color],[nbsh],[sfdqry],[dgjp],[zdys],[sffhx],[pinpai],[djlx],[changdu],[yggdh],[ddbz],[khddbz],[gdh],[jijie],[qhdh],[qydh],[khzl]) "; sql+= ") AS T ON a.voucher_id = T.voucher_id AND a.item_id = T.item_id "; sql+= "LEFT JOIN ebs_v d ON d.voucher_id = a.voucher_id "; sql+= "LEFT JOIN ebs_vr e ON e.voucher_id = a.voucher_id "; // 字典编码转换名称 sql+= "LEFT JOIN (SELECT code,name FROM app_dict_def where dict_id='res_unit_type') AS f on f.code=a.inp_unit_type_id "; //订单单位 sql+= "LEFT JOIN (SELECT user_id,user_name FROM mup_user) AS g on g.user_id=d.create_user_id "; //制单人 sql+= "LEFT JOIN (SELECT user_id,user_name FROM mup_user) AS h on h.user_id=d.check_user_id "; //审核人 sql+= "LEFT JOIN (SELECT code,name FROM app_dict_def where dict_id='voucher_state') AS i on i.code=d.state "; //单据状态 sql+= "LEFT JOIN (SELECT emp_id,name FROM emp) AS j on j.emp_id=d.emp_id "; //业务员 // 基础过滤拦截 sql_where = "WHERE a.inp_num > 0 AND d.voucher_type = 'BA' AND d.voucher_date >= '" + m_ksrq + "' AND d.voucher_date <= '" + m_jsrq + "' "; // 注入高效权限隔离条件 if(v_has_power == 1) { sql_where += "AND e.eba_id IN (SELECT eba_id FROM eba WHERE service_id IN (SELECT val FROM mup_user_bo WHERE bo_id='eba_service' AND user_id='" + uid + "')) "; sql_where += " AND a.inp_num > ISNULL(Ship.total_ship_num, 0) "; //只显示订单数量大于已发货合计数量 } sql_where += " AND a.inp_num > ISNULL(Ship.total_ship_num, 0) ORDER BY d.voucher_no, a.item_id;"; //只显示订单数量大于已发货合计数量 // 执行报表拉取 db_run(sql + sql_where); counts = db_row_count(); if(counts == 0) { msg("【" + m_ksrqs + "】-【" + m_jsrqs + "】期间没有待出货的订单明细数据"); return 1; } // ========================================== // 6. 弹出报表及效率审计计算 // ========================================== if(m_khbhss == "" && counts > 0) { db_show_m("操作员【" + uid + " " + uname + "】于【" + m_dqrq + " " + m_dqsk + "】查询所有客户【" +m_ksrqs + "】-【"+ m_jsrqs + "】待出货的订单明细"); } if(m_khbhss != "" && counts > 0) { db_show_m("操作员【" + uid + " " + uname + "】于【" + m_dqrq + " " + m_dqsk + "】查询客户编号【" + m_khbhss + "】【" + m_ksrqs + "】-【" + m_jsrqs + "】待出货的订单明细"); } m_jssks = time_to_second(get_serv_time()); //执行结束时刻转换成秒数,计算执行时间调用 m_jssk = fmt_time(get_serv_time(), 'hh:mi:ss'); //格式化执行结束时刻,弹出内容调用 m_tqsj = m_jssks - m_dqsks; //计算执行时间,秒数 m_tqfz = to_int(m_tqsj / 60); //执行秒数转换成分数整数 m_tqmss = (m_tqsj / 60 - to_int(m_tqsj / 60)) * 60; //计算执行分钟数剩余的秒数,含小数点 m_tqms = to_int((m_tqsj / 60 - to_int(m_tqsj / 60)) * 60); //取执行分钟数剩余的秒数整数 if(m_tqmss > m_tqms) //当执行分钟数剩余的秒数有小数点时,秒数+1 { m_tqms = m_tqms + 1; } if(m_tqfz == 0) { m_tqfm = m_tqsj + '秒'; } if(m_tqfz > 0) { m_tqfm = m_tqfz + '分' + m_tqms + '秒';} msg('本次共提取【' + counts + '】条记录,从【' + m_kssk + '】开始执行,到【' + m_jssk + '】结束,用时【' + m_tqfm + '】'); return 1; } |
|
 |

官方认证第三方服务团队 (越南-叶子) QQ:121305301 TEL:+84869350626
承接越南北部-河内周边省份-OIT部署和维护服务(24小时上门或远程服务)
服务介绍:http://www.onlyit.cn/onlyit_service.html
|
|
qf2016 [个人空间] QQ名 meng 113779009
    注册  2016-01-17 发贴数 3010 精华数 0 原创贴 0 来自 状态 正常
级别 版主 |
|
|