DELIMITER $$
USE `ld_wpfmgl_sys`$$
DROP PROCEDURE IF EXISTS `code_query`$$
CREATE DEFINER=`root`@`%` PROCEDURE `code_query`(IN `$opfrom` INT, IN `$username` VARCHAR(50), IN `$password` VARCHAR(50), IN `$pagesize` INT, IN `$pageindex` INT, IN `$enterpriseID` INT, IN `$productSeriesID` INT, IN `$productID` INT, IN `$productStandard` VARCHAR(50), IN `$taskID` INT, IN `$code` VARCHAR(50))
COMMENT '3.35 编码查询 web'
BEGIN
/*********************参数定义区域(begin)*************************/
/*定义接收外部参数变量*/
DECLARE $_opfrom INT; # 命令请求来源
DECLARE $_username VARCHAR(50); # 用户名
DECLARE $_password VARCHAR(50); # 密码
DECLARE $_pagesize INT DEFAULT 0; # 页码数
DECLARE $_pageindex INT DEFAULT 0; # 当前页数
DECLARE $_enterpriseID INT DEFAULT 0; # 企业ID
DECLARE $_productSeriesID INT DEFAULT 0; # 产品系列ID
DECLARE $_productID INT DEFAULT 0; # 产品ID
DECLARE $_productStandard VARCHAR(50) ; # 产品规格
DECLARE $_taskID INT ; # 任务名称
DECLARE $_code VARCHAR(50) ; # 码值
/*内部使用变量*/
DECLARE $totalPages INT DEFAULT 0; # 返回用户总数
DECLARE $sqltext VARCHAR(2000); # 拼接sql串
DECLARE $f_code_id INT DEFAULT 0 ; # 父码ID
DECLARE $biaoshi INT DEFAULT 0; # 标识记号
DECLARE $t_id INT DEFAULT 0; # 任务id
/*错误代码及描述
*/
DECLARE $_error INT DEFAULT 0;
#declare continue handler for sqlexception set $_error=1;
/*接收外部参数*/
SET $_opfrom =IFNULL($opfrom,0);
SET $_username=IFNULL($username,'');
SET $_password=IFNULL($PASSWORD,'');
SET $_pagesize=IFNULL($pagesize,0);
SET $_pageindex=IFNULL($pageindex,0);
SET $_enterpriseID = IFNULL($enterpriseID ,0);
SET $_productSeriesID = IFNULL($productSeriesID,0);
SET $_productID = IFNULL($productID ,0);
SET $_productStandard = IFNULL($productStandard,'');
SET $_taskID = IFNULL($taskID ,0);
SET $_code = IFNULL($CODE ,'');
/*********************参数定义区域(end)***************************/
/*********************业务逻辑处理区域(begin)*********************/
#事务开始
SET $sqltext = 'select s.id as taskNO,bu.c_name as enterpriseName,ps.serie_name as proSeries,p.product_name as proName, p.product_spec as proStandard,pr.name as packRatio_name,pr.ratio as packRatio, s.batchNO as batchNO,s.subBatchNO as subBatchNO,s.workshop as workshop,s.proline as proline,s.team as team,s.proBeginDate,s.proEndDate,s.product_date as produceDate,s.expired_date as exprieDate, s.code_count as codeCount,(SELECT COUNT(1) FROM ld_bus_code_produced cp where cp.task_id = s.id ) as codeUsed,s.createBy as opName, s.state as status';
SET $sqltext = CONCAT($sqltext,' from ld_bus_task s join ld_bus_business bu on s.business_id=bu.id
join ld_bus_product_series ps on s.product_series_id=ps.id
join ld_bus_product p on s.product_id=p.id
join ld_bus_pack_ratio pr on s.ratio_id=pr.id where s.isvalid=1 ');
#1判断任务ID是否为空
IF($_taskID !=0)THEN
SET $sqltext=CONCAT($sqltext,' and s.id =',$_taskID);
#2判断码值是否为空
ELSEIF($_code!='')THEN
#获取码值所属任务
SELECT task_id FROM ld_bus_code_produced WHERE CODE =$_code
INTO $t_id;
SET $sqltext=CONCAT($sqltext,' and s.id =',$t_id);
#set $sqltext=concat($sqltext,' and cp.code= \'',$_code,'\'');
#3判断产品规格是否为空
ELSEIF($_productStandard!='')THEN
SET $sqltext=CONCAT($sqltext,' and p.product_spec= \'',$_productStandard,'\'');
ELSE
SET $biaoshi = 1;
END IF;#1
#任务号，编码，产品规格都为空时才判断企业信息
IF($biaoshi=1)THEN
#判断企业是否为空
IF($_enterpriseID !=0)THEN
SET $sqltext=CONCAT($sqltext,' and s.business_id = ',$_enterpriseID);
END IF;
#判断产品系列是否为空
IF($_productSeriesID!=0)THEN
SET $sqltext=CONCAT($sqltext,' and s.product_series_id= ',$_productSeriesID);
END IF;
#判断产品是否为空
IF($_productID!=0)THEN
SET $sqltext=CONCAT($sqltext,' and s.product_id= ',$_productID);
END IF;
END IF;
#set $sqltext = concat($sqltext,' where cp.isValid = 1 group by cp.task_id ');
# 输出拼接sql语句返回的总数
SET @querycount = CONCAT('select count(1) as totalPages from (',$sqltext,')a');
PREPARE querycount FROM @querycount;
EXECUTE querycount;
# 拼接分页语句
SET $sqltext = CONCAT($sqltext,' order by s.createDate desc limit ',$_pageindex,',',$_pagesize);
#输出拼接的sql语句
SET @querysql = $sqltext;
PREPARE stmt FROM @querysql;
EXECUTE stmt;
/*********************业务逻辑处理区域(end)***********************/
/*********************事务报错处理区域(begin)*********************/
#2 报错处理
IF($_error=1) THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
/*********************事务报错处理区域(end)***********************/
/*********************操作日志记录区域(begin)*********************/
INSERT INTO ld_sys_use_log(username,optname,optfrom,optdesc,errorCode)
VALUES($_username,'code_query',$_opfrom,'3.35 编码查询 web',$_error);
/*********************操作日志记录区域(end)***********************/
/*********************返回区域(begin)*****************************/
SELECT $_error AS errorCode;
/*********************返回区域(end)*******************************/
END$$
DELIMITER ;