DELIMITER $$
DROP PROCEDURE IF EXISTS P_C_FD_INSPECTRPT_QRY $$
CREATE
PROCEDURE P_C_FD_INSPECTRPT_QRY(
i_pageno integer -- 页号
,i_pagesize integer -- 每页记录数
,i_op_id char(32) -- 操作员标识
,i_is_inspect_task char(1) -- 是否有检验任务
,i_chktask_name varchar(200) -- 任务名称
,i_file_no varchar(200) -- 文件号
,i_perform_year char(4) -- 执行年份
,i_sample_name varchar(200) -- 样品名称
,i_inspectrpt_no varchar(50) -- 报告编号
,i_inspect_status varchar(50) -- 检验状态
,i_sign_date_start char(8) -- 签发日期起
,i_sign_date_end char(8) -- 签发日期止
,i_audit_status varchar(50) -- 审核状态
,i_beinspected_org_name varchar(100) -- 受检单位名称
,i_inspect_result varchar(50) -- 检验结论
,i_entrust_dept_name varchar(100) -- 委托单位名称
,i_dect_org_id char(32) -- 检测机构标识
,out o_count_num numeric(8,0) -- 总记录数
,out o_outcode integer -- 输出代码
,out o_outmsg varchar(500) -- 输出信息
)
COMMENT '查询新食品检验报告单'
label_pro:
BEGIN
/*======================================================================
* 交 易 名 称 : 查询新食品检验报告单
* 功 能 概 述 ：
* 输 入 表 :
* 输 出 表 :
*
* 文 件 名 称 : P_C_FD_INSPECTRPT_QRY.sql
* 设 计 : 设计时间:
* 开 发 : 开发时间: 2019-01-08 11:07:33
*
* 修 改1：
* 修 改 人 员 ： 修改时间:
* 修 改 标 识 ：
* 修 改 描 述 :
*
* 修 改2:
* 修 改 人 员 : 修改时间:
* 修 改 标 识 :
* 修 改 描 述 :
* 测 试 代 码 : calll P_C_FD_INSPECTRPT_QRY()
======================================================================*/
-- =========================== 声明变量 ===========================
-- 固定变量
declare v_uuid varchar(64); -- 生成日志唯一标识
declare v_trade_log_switch varchar(1); -- 交易日志开关:0-关;1-开
declare v_debug_switch varchar(1); -- 调试开关:0-关;1-开
declare v_debug_id int; -- 调试id
declare v_retcode1 integer; -- 返回代码1
declare v_retmsg1 varchar(2000); -- 返回信息1
-- 分页变量
declare v_pagerow_b integer; -- 分页起始行
declare v_psize integer; -- 页面容量
-- 表变量定义:交易日志【s_pb_tradelog】(tlog)
declare v_tlog_log_id varchar(32); -- 日志标志
declare v_tlog_trade_name varchar(50); -- 交易名称
declare v_tlog_trad_paras varchar(2000); -- 参数
declare v_tlog_begintime varchar(30); -- 开始时间
declare v_tlog_endtime varchar(30); -- 结束时间
declare v_tlog_runtime numeric(20,6); -- 运行时间
declare v_tlog_is_success varchar(1); -- 是否成功
declare v_tlog_errorcode varchar(50); -- 失败代码
declare v_tlog_errormsg varchar(2000); -- 失败信息
declare v_tlog_runscript varchar(2000); -- 执行脚本
DECLARE v_sql varchar(3000);
-- =========================== 声明游标 ===========================
-- =========================== 声明异常 ===========================
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
set o_outcode =-1;
set o_outmsg ='处理失败。';
set v_tlog_endtime = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
set v_tlog_runtime = timestampdiff(second,v_tlog_begintime,v_tlog_endtime);#取时间差
set v_tlog_is_success ='1';
set v_tlog_errormsg = '处理失败';
set v_tlog_errorcode = '';
set v_tlog_runscript = '';
call p_s_sys_tradelog_upd1(
v_tlog_log_id -- 日志标志
, v_tlog_trade_name -- 交易名称
, v_tlog_trad_paras -- 参数
, v_tlog_begintime -- 开始时间
, v_tlog_endtime -- 结束时间
, v_tlog_runtime -- 运行时间
, v_tlog_is_success -- 是否成功
, v_tlog_errorcode -- 失败代码
, v_tlog_errormsg -- 失败信息
, v_tlog_runscript -- 执行脚本
, v_retcode1
, v_retmsg1
);
end ;
-- =========================== 声明临时表==========================
-- 调试临时表
drop table if exists tmp_debug;
create temporary table tmp_debug (
debug_id int
, debug_time varchar(50) -- 调试时间
, debug_info varchar(2000) -- 调试信息
);
-- =========================== 输入检查 ===========================
set o_outcode=0;
set o_outmsg='查询成功';
-- 校验 页号 是否为空
if I_PAGENO is null then
set o_outcode = -1;
set o_outmsg =CONCAT(o_outmsg,'【页号】不能为空');
end if;
-- 校验 每页记录数 是否为空
if I_PAGESIZE is null then
set o_outcode = -1;
set o_outmsg =CONCAT(o_outmsg,'【每页记录数】不能为空');
end if;
-- 校验 操作员标识 是否为空
if I_OP_ID is null or I_OP_ID='' then
set o_outcode = -1;
set o_outmsg =CONCAT(o_outmsg,'【操作员标识】不能为空');
end if;
-- -- 校验 是否有检验任务 是否为空
-- if I_IS_INSPECT_TASK is null or I_IS_INSPECT_TASK='' then
-- set o_outcode = -1;
-- set o_outmsg =CONCAT(o_outmsg,'【是否有检验任务】不能为空');
-- end if;
-- 检查不通过 返回错误信息
if o_outcode <>0 then
leave label_pro;
end if ;
-- =========================== 变量初始化 ===========================
-- 临时变量
set v_trade_log_switch ='0'; -- 交易日志开关:0-关;1-开
set v_uuid = replace(uuid(), '-', ''); -- 生成日志id
-- 日志变量初始化
set v_tlog_log_id = v_uuid;
set v_tlog_trade_name = 'P_C_FD_INSPECTRPT_QRY';
set v_tlog_trad_paras = '';
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_pageno is null then ''else concat('i_pageno','=',i_pageno) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_pagesize is null then ''else concat('i_pagesize','=',i_pagesize) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_op_id is null then ''else concat('i_op_id','=',i_op_id) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_is_inspect_task is null then ''else concat('i_is_inspect_task','=',i_is_inspect_task) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_chktask_name is null then ''else concat('i_chktask_name','=',i_chktask_name) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_file_no is null then ''else concat('i_file_no','=',i_file_no) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_perform_year is null then ''else concat('i_perform_year','=',i_perform_year) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_sample_name is null then ''else concat('i_sample_name','=',i_sample_name) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_inspectrpt_no is null then ''else concat('i_inspectrpt_no','=',i_inspectrpt_no) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_inspect_status is null then ''else concat('i_inspect_status','=',i_inspect_status) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_sign_date_start is null then ''else concat('i_sign_date_start','=',i_sign_date_start) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_sign_date_end is null then ''else concat('i_sign_date_end','=',i_sign_date_end) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_audit_status is null then ''else concat('i_audit_status','=',i_audit_status) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_beinspected_org_name is null then ''else concat('i_beinspected_org_name','=',i_beinspected_org_name) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_inspect_result is null then ''else concat('i_inspect_result','=',i_inspect_result) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_entrust_dept_name is null then ''else concat('i_entrust_dept_name','=',i_entrust_dept_name) end,',');
set v_tlog_trad_paras = concat(v_tlog_trad_paras,case when i_dect_org_id is null then ''else concat('i_dect_org_id','=',i_dect_org_id) end,',');
set v_tlog_begintime = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
set v_tlog_endtime = '';
set v_tlog_runtime = NULL;
set v_tlog_is_success = '0'; -- 0-成功;1-失败
set v_tlog_errorcode = '';
set v_tlog_errormsg = '';
set v_tlog_runscript = '';
set v_retcode1= 0;
set v_retmsg1 = '';
-- =========================== 数据预处理 ===========================
-- 写入日志表信息
IF (v_trade_log_switch='1') THEN
call p_s_sys_tradelog_ins1(
v_tlog_log_id #日志标志
, v_tlog_trade_name #交易名称
, v_tlog_trad_paras #参数
, v_tlog_begintime #开始时间
, v_tlog_endtime #结束时间
, v_tlog_runtime #运行时间
, v_tlog_is_success #是否成功
, v_tlog_errorcode #失败代码
, v_tlog_errormsg #失败信息
, v_tlog_runscript #执行脚本
, v_retcode1
, v_retmsg1
);
END IF;
-- 分页处理
call p_pub_paging(i_pageno,i_pagesize,v_pagerow_b,v_psize);
-- =========================== 业务处理 ===========================
-- START TRANSACTION;
/*
5@@规则说明：只显示“是否有检验任务”为是且“检测机构名称”为当前用户所在的检测机构的报告单数据 IS_ACTIVE = "1"
查询条件：(签发日期 执行年份 检验状态 审核状态 检验结论)精确查询 其他的都是模糊查询
排序说明：按照SIGN_DATE+INSPECTRPT_NO 降序排列
*/
-- todo begin
-- COMMIT;
-- todo end
-- 更新交易日志
IF (v_trade_log_switch='1') THEN
set v_tlog_endtime = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
set v_tlog_runtime = timestampdiff(second,v_tlog_begintime,v_tlog_endtime);#取时间差
set v_tlog_is_success ='0'; -- 0-成功;1-失败
set v_tlog_errormsg = '';
set v_tlog_errorcode = '';
set v_tlog_runscript = '';
call p_s_sys_tradelog_upd1(
v_tlog_log_id -- 日志标志
, v_tlog_trade_name -- 交易名称
, v_tlog_trad_paras -- 参数
, v_tlog_begintime -- 开始时间
, v_tlog_endtime -- 结束时间
, v_tlog_runtime -- 运行时间
, v_tlog_is_success -- 是否成功
, v_tlog_errorcode -- 失败代码
, v_tlog_errormsg -- 失败信息
, v_tlog_runscript -- 执行脚本
, v_retcode1
, v_retmsg1
);
END IF;
-- =========================== 结果集返回 ===========================
/* select
count(1)
into
o_count_num
from C_FD_INSPECTRPT fd04
where 1=1
and fd04.is_active = '1'
and (i_is_inspect_task is null or i_is_inspect_task = '' or fd04.is_inspect_task = i_is_inspect_task ) -- 是否有检验任务
and (i_chktask_name is null or i_chktask_name = '' or fd04.chktask_name LIKE CONCAT('%',i_chktask_name,'%') ) -- 任务名称
and (i_file_no is null or i_file_no = '' or fd04.file_no = i_file_no ) -- 文件号
and (i_perform_year is null or i_perform_year = '' or fd04.perform_year = i_perform_year ) -- 执行年份
and (i_sample_name is null or i_sample_name = '' or fd04.sample_name LIKE CONCAT('%',i_sample_name,'%') ) -- 样品名称
and (i_inspectrpt_no is null or i_inspectrpt_no = '' or fd04.inspectrpt_no LIKE CONCAT('%',i_inspectrpt_no,'%') ) -- 报告编号
and (i_inspect_status is null or i_inspect_status = '' or fd04.inspect_status = i_inspect_status ) -- 检验状态
and (i_sign_date_start is null or i_sign_date_start = '' or fd04.sign_date >= i_sign_date_start ) -- 签发日期起
and (i_sign_date_end is null or i_sign_date_end = '' or fd04.sign_date <= i_sign_date_end ) -- 签发日期止
and (i_audit_status is null or i_audit_status = '' or fd04.audit_status = i_audit_status ) -- 审核状态
and (i_beinspected_org_name is null or i_beinspected_org_name = '' or fd04.beinspected_org_name LIKE CONCAT('%',i_beinspected_org_name,'%') ) -- 受检单位名称
and (i_inspect_result is null or i_inspect_result = '' or fd04.inspect_result = i_inspect_result ) -- 检验结论
and (i_entrust_dept_name is null or i_entrust_dept_name = '' or fd04.entrust_dept_name LIKE CONCAT('%',i_entrust_dept_name,'%') ) -- 委托单位名称
and (i_dect_org_id is null or i_dect_org_id = '' or fd04.DECT_ORG_ID = i_dect_org_id) -- 检测机构标识
; */
set v_sql = 'select
count(1)
into
@rowcount
from C_FD_INSPECTRPT fd04
where fd04.IS_ACTIVE = ''1''
';
-- 是否有检验任务
IF COALESCE(i_is_inspect_task, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.is_inspect_task = ''',i_is_inspect_task,'''' );
end IF ;
-- 任务名称
IF COALESCE(i_chktask_name, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.chktask_name LIKE ''%',i_chktask_name,'%''' );
end IF ;
-- 文件号
IF COALESCE(i_file_no, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.file_no LIKE ''%',i_file_no,'%''' );
end IF ;
-- 执行年份
IF COALESCE(i_perform_year, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.perform_year = ''',i_perform_year,'''' );
end IF ;
-- 样品名称
IF COALESCE(i_sample_name, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.sample_name LIKE ''%',i_sample_name,'%''' );
end IF ;
-- 报告编号
IF COALESCE(i_inspectrpt_no, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.inspectrpt_no LIKE ''%',i_inspectrpt_no,'%''' );
end IF ;
-- 检验状态
IF COALESCE(i_inspect_status, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.inspect_status = ''',i_inspect_status,'''' );
end IF ;
-- 签发日期起
IF COALESCE(i_sign_date_start, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.sign_date >= ''',i_sign_date_start,'''' );
end IF ;
-- 签发日期止
IF COALESCE(i_sign_date_end, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.sign_date <= ''',i_sign_date_end,'''' );
end IF ;
-- 审核状态
IF COALESCE(i_audit_status, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.audit_status = ''',i_audit_status,'''' );
end IF ;
-- 受检单位名称
IF COALESCE(i_beinspected_org_name, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.beinspected_org_name LIKE ''%',i_beinspected_org_name,'%''' );
end IF ;
-- 检验结论
IF COALESCE(i_inspect_result, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.inspect_result = ''',i_inspect_result,'''' );
end IF ;
-- 委托单位名称
IF COALESCE(i_entrust_dept_name, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.entrust_dept_name LIKE ''%',i_entrust_dept_name,'%''' );
end IF ;
-- 检测机构标识
IF COALESCE(i_dect_org_id, '')!='' THEN
set v_sql = CONCAT(v_sql , ' AND fd04.DECT_ORG_ID = ''',i_dect_org_id,'''' );
end IF ;
-- 获取动态SQL语句
set @sql_str = v_sql;
prepare stmt from @sql_str;
execute stmt;
deallocate prepare stmt;
-- 获取记录总数
set o_count_num = @rowcount;
-- 返回结果集rs
select
fd04.inspectrpt_id as inspectrpt_id -- 食品检验报告单标识
,fd04.chktask_id as chktask_id -- 任务标识
,fd04.chktask_name as chktask_name -- 任务名称
,fd04.perform_year as perform_year -- 执行年份
,fd04.file_no as file_no -- 文件号
,fd04.inspectrpt_no as inspectrpt_no -- 报告编号
,fd04.sign_date as sign_date -- 签发日期
,fd04.sample_name as sample_name -- 样品名称
,fd04.beinspected_org_name as beinspected_org_name -- 受检单位名称
,fd04.inspect_status as inspect_status -- 检验状态
,sps.DIC_NAME as inspect_status_name -- 检验状态名称
,fd04.inspect_result as inspect_result -- 检验结论
,sps2.DIC_NAME as inspect_result_name -- 检验结论名称
,fd04.entrust_dept_id as entrust_dept_id -- 委托单位标识
,fd04.entrust_dept_name as entrust_dept_name -- 委托单位名称
,fd04.audit_status as audit_status -- 审核状态
,sps3.DIC_NAME as audit_status_name -- 审核状态名称
,fd04.AUDIT_NAME as audit_name -- 审核人
,fd04.audit_time as audit_time -- 审核时间
,fd04.is_ext_imp as is_ext_imp -- 是否外部导入
,fd04.return_cause as return_cause -- 退回原因
from C_FD_INSPECTRPT fd04
LEFT JOIN s_pb_sysdicinfo sps ON sps.DIC_CODE = fd04.inspect_status AND sps.IS_ACTIVE = '1'
LEFT JOIN s_pb_sysdicinfo sps2 ON sps2.DIC_CODE = fd04.inspect_result AND sps.IS_ACTIVE = '1'
LEFT JOIN s_pb_sysdicinfo sps3 ON sps3.DIC_CODE = fd04.audit_status AND sps.IS_ACTIVE = '1'
INNER JOIN (
SELECT fd04.INSPECTRPT_ID
from C_FD_INSPECTRPT fd04
where fd04.is_active = '1'
and (i_is_inspect_task is null or i_is_inspect_task = '' or fd04.is_inspect_task = i_is_inspect_task ) -- 是否有检验任务
and (i_chktask_name is null or i_chktask_name = '' or fd04.chktask_name LIKE CONCAT('%',i_chktask_name,'%') ) -- 任务名称
and (i_file_no is null or i_file_no = '' or fd04.file_no LIKE CONCAT('%',i_file_no,'%') ) -- 文件号
and (i_perform_year is null or i_perform_year = '' or fd04.perform_year = i_perform_year ) -- 执行年份
and (i_sample_name is null or i_sample_name = '' or fd04.sample_name LIKE CONCAT('%',i_sample_name,'%') ) -- 样品名称
and (i_inspectrpt_no is null or i_inspectrpt_no = '' or fd04.inspectrpt_no LIKE CONCAT('%',i_inspectrpt_no,'%') ) -- 报告编号
and (i_inspect_status is null or i_inspect_status = '' or fd04.inspect_status = i_inspect_status ) -- 检验状态
and (i_sign_date_start is null or i_sign_date_start = '' or fd04.sign_date >= i_sign_date_start ) -- 签发日期起
and (i_sign_date_end is null or i_sign_date_end = '' or fd04.sign_date <= i_sign_date_end ) -- 签发日期止
and (i_audit_status is null or i_audit_status = '' or fd04.audit_status = i_audit_status ) -- 审核状态
and (i_beinspected_org_name is null or i_beinspected_org_name = '' or fd04.beinspected_org_name LIKE CONCAT('%',i_beinspected_org_name,'%') ) -- 受检单位名称
and (i_inspect_result is null or i_inspect_result = '' or fd04.inspect_result = i_inspect_result ) -- 检验结论
and (i_entrust_dept_name is null or i_entrust_dept_name = '' or fd04.entrust_dept_name LIKE CONCAT('%',i_entrust_dept_name,'%') ) -- 委托单位名称
and (i_dect_org_id is null or i_dect_org_id = '' or fd04.DECT_ORG_ID = i_dect_org_id) -- 检测机构标识
ORDER BY fd04.sign_date DESC ,fd04.inspectrpt_no DESC
limit v_pagerow_b, v_psize
)t ON t.INSPECTRPT_ID = fd04.INSPECTRPT_ID
;
END
$$
