CREATE DEFINER=`usmadmin`@`%` PROCEDURE `Pro_syn_c_dprecord`(OUT out_status CHAR(5))
COMMENT 'Violation records are synchronized from DSS to USM'
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE last_syn_datetime datetime;
DECLARE next_syn_datetime datetime;
DECLARE usm_reu_id VARCHAR(40) DEFAULT '0' ;-- 大保卫的的违章ID
DECLARE vid INT DEFAULT 0 ;-- 违章ID
DECLARE vcar_num varchar(12);-- 车牌号
DECLARE vcar_speed int;-- 车速
DECLARE vcar_type int ;-- 车辆类型
DECLARE usm_car_type varchar(2) DEFAULT 'F' ;-- 大保卫中车辆类型 H货车 K客车 T特种车辆 F非机动车 Q其他机动车
DECLARE vcar_color int ;-- 车辆颜色
DECLARE vcap_date datetime;-- 违章时间
DECLARE vrec_type int ;-- 违章类型
DECLARE vdev_name varchar(50) ;-- 抓拍地点
DECLARE usm_typeVio VARCHAR(2) DEFAULT '32' ;-- 大保卫违章类型 32:闯红灯 10:超速
DECLARE vcombined_pic_url varchar(255); -- 总违章图片url
DECLARE vcar_img_url varchar(255);-- 违章图片1
DECLARE vcar_img1_url varchar(255);-- 违章图片2
DECLARE dprecord_data CURSOR
FOR
SELECT ID, CAR_NUM, CAR_SPEED, CAR_TYPE, CAR_COLOR, CAP_DATE, REC_TYPE, DEV_NAME, COMBINED_PIC_URL, CAR_IMG_URL, CAR_IMG1_URL
FROM c_dprecord_link_dh
WHERE CAP_DATE > last_syn_datetime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 没有下一条数据 修改标记为1
BEGIN
SELECT t.updateTime INTO last_syn_datetime FROM syn_datetime t WHERE t.systemName= 'DH';
SET next_syn_datetime= SYSDATE();
END;
OPEN dprecord_data;
FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;
WHILE (done<>1) DO
SET usm_reu_id = REPLACE(UUID(),"-","");
IF vcar_type=1 THEN
SET usm_car_type = 'K';
END IF;
IF vcar_type=2 THEN
SET usm_car_type = 'H';
END IF;
IF vrec_type=1 THEN
SET usm_typeVio = '10';
ELSE
SET usm_typeVio = '32';
END IF;
BEGIN
INSERT into vehicle_regulation
(
id,
recordSource,
carType,
carNumber,
datetimeVio,
siteVio,
typeVio,
processState,
recieveDatetime,
isNeedfine,
isInFactry,
sourceId,
intIsActive,
intIsDelete,
createUserId,
createDateTime,
updateUserId,
updateDateTime,
companyId
)
VALUES
(
usm_reu_id,
'1',
usm_car_type,
vcar_num,
vcap_date,
vdev_name,
usm_typeVio,
'1',
SYSDATE(),
'Y',
'1',
vid,
'1',
'0',
'000000',
SYSDATE(),
'000000',
SYSDATE(),
'1'
);
END;
BEGIN
INSERT INTO image_regulation
(
imageUrl,
regulationid,
iscombined,
intIsActive,
intIsDelete,
createUserId,
createDateTime,
updateUserId,
updateDateTime,
companyId
)
VALUES
(
CONCAT('http://172.18.**.***:8081',substring(vcombined_pic_url, 5)),
usm_reu_id,
'1',
'1',
'0',
'000000',
SYSDATE(),
'000000',
SYSDATE(),
'1'
);
END;
IF(vcar_img_url<>'0') THEN
BEGIN
INSERT INTO image_regulation
(
imageUrl,
regulationid,
iscombined,
intIsActive,
intIsDelete,
createUserId,
createDateTime,
updateUserId,
updateDateTime,
companyId
)
VALUES
(
CONCAT('http://172.18.**.***:8081',substring(vcar_img_url, 5)),
usm_reu_id,
'0',
'1',
'0',
'000000',
SYSDATE(),
'000000',
SYSDATE(),
'1'
);
END;
END IF;
IF(vcar_img1_url<>'0') THEN
BEGIN
INSERT INTO image_regulation
(
imageUrl,
regulationid,
iscombined,
intIsActive,
intIsDelete,
createUserId,
createDateTime,
updateUserId,
updateDateTime,
companyId
)
VALUES
(
CONCAT('http://172.18.**.***:8081',substring(vcar_img1_url, 5)),
usm_reu_id,
'0',
'1',
'0',
'000000',
SYSDATE(),
'000000',
SYSDATE(),
'1'
);
END;
END IF;
BEGIN
UPDATE syn_datetime SET updateTime = next_syn_datetime WHERE systemName='DH';
END;
FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;
END WHILE;
CLOSE dprecord_data;
set out_status='OK';
END