CREATE DEFINER=`root`@`%` PROCEDURE `P_codestream_count_main`(IN `P_LINKID` varchar(32))
COMMENT '国标码流分析统计主函数'
BEGIN
#Routine body goes here...
BEGIN
#声明接受的数据变量
DECLARE P_CIVILCODE VARCHAR(255);
#遍历数据结束标志
DECLARE done TINYINT DEFAULT 0;
#游标数据
DECLARE civilcodes CURSOR FOR SELECT distinct civilcode FROM ao_quality_count where taskno = P_LINKID;
#将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#打开游标
OPEN civilcodes;
#开始循环
WHILE done<>1 DO
#提取游标里的数据
FETCH civilcodes INTO P_CIVILCODE;
IF done<>1 THEN
CALL P_codestream_count_drs(P_LINKID,P_CIVILCODE);
CALL P_codestream_count_hds(P_LINKID,P_CIVILCODE);
END IF;
END WHILE;
#关闭游标
CLOSE civilcodes;
END;
END
CREATE DEFINER=`root`@`%` PROCEDURE `P_codestream_count_drs`(IN `P_LINKID` varchar(64),IN `P_CIVILCODE` varchar(32))
COMMENT '国标码流分析统计'
BEGIN
#DECLARE p_linkid VARCHAR(64) DEFAULT 0;#关联任务编码
#DECLARE p_civilcode VARCHAR(32) DEFAULT 0;#区域编码
DECLARE p_civilname VARCHAR(255) DEFAULT 0;#区域名称
DECLARE p_ps INT(11) DEFAULT 0;#ps封装数
DECLARE p_es INT(11) DEFAULT 0;#es封装数
DECLARE p_ungbpacking INT(11) DEFAULT 0;#非国标封装数
DECLARE p_h264 INT(11) DEFAULT 0;#h264编码数
DECLARE p_h265 INT(11) DEFAULT 0;#h265编码数
DECLARE p_svac INT(11) DEFAULT 0;#svac编码数
DECLARE p_mpeg4 INT(11) DEFAULT 0;#mp4编码数
DECLARE p_ungbencoding INT(11) DEFAULT 0;#非标编码数
DECLARE p_total INT(11) DEFAULT 0;#总数
DECLARE p_normal INT(11) DEFAULT 0;#合格数
DECLARE p_normalrate DECIMAL(10,2) DEFAULT 0.00;#合格率
#区域名称
BEGIN
SELECT areaName INTO p_civilname FROM ao_area WHERE areaId = P_CIVILCODE;
END;
#码流封装 ps
BEGIN
SELECT
COUNT(1) INTO p_ps
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND payload = 96;
END;
#码流封装 es
BEGIN
SELECT
COUNT(1) INTO p_es
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND payload IN (97,98,99,108);
END;
#码流封装 非国标封装数
BEGIN
SELECT
COUNT(1) INTO p_ungbpacking
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND payload NOT IN (96,97,98,99,108);
END;
#视频编码 h264编码数
BEGIN
SELECT
COUNT(1) INTO p_h264
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND videoformat = 0;
END;
#视频编码 h265编码数
BEGIN
SELECT
COUNT(1) INTO p_h265
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND videoformat = 1;
END;
#视频编码 svac编码数
BEGIN
SELECT
COUNT(1) INTO p_svac
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND videoformat = 3;
END;
#视频编码 mp4编码数
BEGIN
SELECT
COUNT(1) INTO p_mpeg4
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND videoformat = 2;
END;
#视频编码 非标编码数
BEGIN
SELECT
COUNT(1) INTO p_ungbencoding
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND videoformat NOT IN (0,1,2,3);
END;
#总数,合格数,合格率
BEGIN
SELECT
predicttotal,qualifiednum,qualifiedrate INTO p_total,p_normal,p_normalrate
FROM
ao_quality_count
WHERE
taskno = P_LINKID AND civilcode = P_CIVILCODE AND tasktype = 103;
END;
#删除,插入
BEGIN
DELETE FROM ao_gb_stream_statis WHERE linkid = P_LINKID AND civilcode = P_CIVILCODE;
INSERT INTO ao_gb_stream_statis
(
linkid,civilcode,civilname,ps,es,ungbpacking,h264,h265,svac,mpeg4,ungbencoding,total,normal,normalrate,ctime
)
VALUES
(
P_LINKID,P_CIVILCODE,p_civilname,p_ps,p_es,p_ungbpacking,p_h264,p_h265,p_svac,p_mpeg4,p_ungbencoding,p_total,p_normal,p_normalrate,now()
);
END;
END
CREATE DEFINER=`root`@`%` PROCEDURE `P_codestream_count_hds`(IN `P_LINKID` varchar(64),IN `P_CIVILCODE` varchar(32))
COMMENT '数据高清分析统计'
BEGIN
#Routine body goes here...
DECLARE p_civilname VARCHAR(255) DEFAULT 0;#区域名称
DECLARE p_hd INT(11) DEFAULT 0;#高清数
DECLARE p_sd INT(11) DEFAULT 0;#标清数
DECLARE p_sdbelow INT(11) DEFAULT 0;#低于标清
DECLARE p_unknown INT(11) DEFAULT 0;#无法识别
DECLARE p_total INT(11) DEFAULT 0;#总数
DECLARE p_hdrate DECIMAL(10,2) DEFAULT 0.00;#高清占比
#区域名称
BEGIN
SELECT areaName INTO p_civilname FROM ao_area WHERE areaId = P_CIVILCODE;
END;
#高清数
BEGIN
SELECT
COUNT(1) INTO p_hd
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND high > 96;
END;
#标清数
BEGIN
SELECT
COUNT(1) INTO p_sd
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND high = 96;
END;
#低于标清
BEGIN
SELECT
COUNT(1) INTO p_sdbelow
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND high < 96 AND high != 0;
END;
#无法识别
BEGIN
SELECT
COUNT(1) INTO p_unknown
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%') AND (high = 0 OR high = '');
END;
#总数
BEGIN
SELECT
COUNT(1) INTO p_total
FROM
ao_diag_result_stardand
WHERE
linkid = P_LINKID AND deviceid LIKE CONCAT(P_CIVILCODE,'%');
END;
#高清占比
SET p_hdrate = IFNULL(TRUNCATE(p_hd / p_total * 100, 2),0);
#删除,插入
BEGIN
DELETE FROM ao_hd_diagnosis_statis WHERE linkid = P_LINKID AND civilcode = P_CIVILCODE;
INSERT INTO ao_hd_diagnosis_statis
(
linkid,civilcode,civilname,hd,sd,sdbelow,unknown,total,hdrate,ctime
)
VALUES
(
P_LINKID,P_CIVILCODE,p_civilname,p_hd,p_sd,p_sdbelow,p_unknown,p_total,p_hdrate,now()
);
END;
END
