
use `clbs`;

#新增OBD-HJ-2021协议
INSERT IGNORE INTO `clbs`.`zw_c_dictionary`(`id`, `pid`, `code`, `value`, `type`, `description`, `sort`) VALUES ('de611b69-639c-4ae8-a15e-27b722217e15', NULL, '100', 'OBD-HJ-2021', '808', NULL, 40);


ALTER TABLE `clbs`.`zw_m_adas_jing_alarm_param_setting` add column alarm_speed_threshold  int DEFAULT NULL COMMENT '报警判断速度阈值(km/h)';

ALTER TABLE `clbs`.`zw_m_adas_jing_alarm_param_setting` modify column speed_limit int DEFAULT NULL COMMENT '报警抑制速度阈值(km/h)';

ALTER TABLE `clbs`.`zw_m_adas_jing_alarm_param_setting` modify column duration_threshold int DEFAULT NULL COMMENT '报警触发时间间隔阈值(s)';

DELETE FROM `clbs`.`zw_m_adas_jing_alarm_param_setting` WHERE `vehicle_id` = 'default';

#京标(参数默认设置)
INSERT IGNORE INTO `clbs`.`zw_m_adas_jing_alarm_param_setting`(`id`, `risk_function_id`, `parameter_id`, `vehicle_id`, `alarm_level`, `alarm_volume`, `speech`, `alarm_video_duration`, `video_resolution`, `photograph_number`, `camera_resolution`, `photograph_time`, `speed_limit`, `alarm_speed_threshold`, `duration_threshold`, `protocol_type`, `param_type`, `flag`) VALUES
('0be2f2ac-394f-4c7c-8cd6-5cd95092a683', 246505, '0xF517', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('203ea1b2-241d-4739-8d29-8553e07041cb', 246404, '0xF524', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 52, 1),
('21b3a95c-80ea-4c32-bd44-50645f1a342e', 246502, '0xF512', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('5245aab5-5899-4861-b47c-5e9ca4df6d0b', 246506, '0xF516', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('56bd3c9f-3839-4ccf-8e68-a3ca71fc46df', 246503, '0xF514', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('645a09ea-d694-42c4-937d-117c64b7c8b0', 246501, '0xF511', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('8626f8f3-bcf6-43f2-ac1f-1904b41fa1f9', 246518, '0xF515', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('8794cf3c-c44b-46e2-bfbc-c59b17a1cd1c', 246402, '0xF523', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 52, 1),
('af70e9bf-8c22-4685-bb46-66ecd1a3b4c0', 246403, '0xF522', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 52, 1),
('bf6af10d-f5a1-4a88-bd26-5672b3d19eca', 246504, '0xF513', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 51, 1),
('e7465600-2f84-4ef7-b06b-eb4a73ed2ea4', 246405, '0xF525', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 52, 1),
('fece05dd-9e52-4de3-bdbc-2421c1b3076d', 246401, '0xF521', 'default', '1', '7', 1, 6, '0x01', 4, '0x01', 2, 50, 40, 0, 24, 52, 1);

#删除新京标移除的参数项
DELETE FROM `clbs`.`zw_m_adas_jing_alarm_param_setting` WHERE risk_function_id NOT IN (246505, 246404, 246502, 246506, 246503, 246501, 246518, 246402, 246403, 246504, 246405, 246401);

#修改京标车的主动安全参数设置,新增的报警判断速度阈值填充默认值40
UPDATE `clbs`.`zw_m_adas_jing_alarm_param_setting` SET alarm_speed_threshold = 40 WHERE alarm_speed_threshold IS NULL;

#OBD里程油耗报表菜单
UPDATE `clbs`.`zw_c_resource` SET perm_value = 'report-management' WHERE id = 'f9c65d06-f40e-11e6-bc64-92361f006689';

UPDATE `clbs`.`zw_c_resource` SET perm_value = 'sensor' WHERE id = '48c5b1ee-5f60-5953-cb6d-c5b28ad78a31';

INSERT IGNORE INTO `clbs`.`zw_c_resource` (`id`, `icon_cls`, `resource_name`, `type`, `code`, `permission`, `perm_value`, `description`, `parent_id`, `sort_order`, `domain`, `code_num`, `editable`, `enabled`, `flag`, `create_data_time`, `create_data_username`, `update_data_time`, `update_data_username`)
VALUES ('e0d60dd8-fe6e-4da5-a13a-abe414cdd9bd', NULL, 'T-BOX/OBD里程油耗', 0, NULL, 'report_sensor_obd', 'obd-mileage', NULL, '48c5b1ee-5f60-5953-cb6d-c5b28ad78a31', 110413, 2, NULL, 1, 1, 1, NULL, NULL, NULL, NULL);

INSERT IGNORE INTO `clbs`.`zw_c_role_resource` (`id`, `resource_id`, `role_id`, `create_data_time`, `create_data_username`, `update_data_time`, `update_data_username`, `flag`, `editable`)
VALUES ('00573ef5-c060-4d03-b762-04641432ebb7', 'e0d60dd8-fe6e-4da5-a13a-abe414cdd9bd', 'cn=ROLE_ADMIN,ou=Groups', '2023-03-13 09:34:05', 'admin', NULL, NULL, 1, 1);  

#京标报警名称修改
#京标-分神驾驶报警修改为长时间不目视前方报警
UPDATE `clbs`.`zw_m_risk_event` SET `risk_event` = '长时间不目视前方报警', `description` = '长时间不目视前方报警说明', `event_common_name` = '长时间不目视前方', `event_common_field` = 'abnormalPosture' WHERE id = '078e7900-abb4-11ea-bdcd-000c29920fdc';

#京标-驾驶人异常报警修改为驾驶员不在驾驶位置报警
UPDATE `clbs`.`zw_m_risk_event` SET `risk_event` = '驾驶员不在驾驶位置报警', `risk_type` = '违规异常', `description` = '驾驶员不在驾驶位置报警危险说明', `event_common_name` = '驾驶员不在驾驶位置', `event_common_field` = 'noDriverDetected' WHERE id = '07996653-abb4-11ea-bdcd-000c29920fdc';

#京标-前车碰撞报警修改为前向碰撞报警
UPDATE `clbs`.`zw_m_risk_event` SET `risk_event` = '前向碰撞报警', `description` = '前向碰撞报警危险说明', `event_common_name` = '前向碰撞', `event_common_field` = 'vehicleCrash' WHERE id = '0774a3ce-abb4-11ea-bdcd-000c29920fdc';

#删除京标限速报警、限高报警、限宽报警、限重报警
DELETE FROM `clbs`.`zw_m_risk_event` WHERE id IN ('0780ae8b-abb4-11ea-bdcd-000c29920fdc',
                                                  '07837f78-abb4-11ea-bdcd-000c29920fdc',
                                                  '078684fa-abb4-11ea-bdcd-000c29920fdc',
                                                  '078952fc-abb4-11ea-bdcd-000c29920fdc');

#两客一危 监控对象气泡增加对象状态列
INSERT INTO `clbs`.`zw_c_custom_column` (`id`, `title`, `name`, `mark`, `status`, `sort`, `column_module`, `init_value`) VALUES
('f6bc9a8b-2254-4b76-8aa2-b29e0ea708e0', '对象状态', 'objectState', 'TGAC_AlERT_WINDOW_REALTIME_DATA_LIST', 0, 5, 'TWO_GUEST_A_CRISIS_REALTIME_MONITORING', NULL);

#更改菜单名称
UPDATE `clbs`.`zw_c_resource` SET resource_name = '808转发管理' WHERE id = 'f9c6671a-f40e-11e6-bc64-92361f002671';
UPDATE `clbs`.`zw_c_resource` SET resource_name = '809转发管理' WHERE id = 'f9c6629c-f40e-11e6-bc64-92361f003671';

 #企业加油记录表
CREATE TABLE IF NOT EXISTS `clbs`.`zw_m_refuel_record` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '车辆加油记录表',
      `monitor_id` varchar(40) NOT NULL COMMENT '监控对象id',
      `refueling_time` datetime NOT NULL COMMENT '加油时间',
      `refueling_capacity` double DEFAULT NULL COMMENT '加油量,单位:L',
      `vehicle_dashboard_total_mile` double DEFAULT NULL COMMENT '车辆仪表盘总里程',
      `invoice_picture` varchar(100) DEFAULT NULL COMMENT '单据图片',
      `flag` smallint(6) NOT NULL DEFAULT '1' COMMENT '逻辑删除标识,0已删除、1正常',
      `create_data_time` datetime DEFAULT NULL COMMENT '创建时间',
      `create_data_username` varchar(45) DEFAULT NULL COMMENT '创建人',
      `update_data_time` datetime DEFAULT NULL COMMENT '修改时间',
      `update_data_username` varchar(45) DEFAULT NULL COMMENT '修改人',
      PRIMARY KEY (`id`),
      INDEX `query_by_id` (`monitor_id`, `refueling_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#能耗基准表
CREATE TABLE IF NOT EXISTS `clbs`.`zw_m_energy_consume_criterion` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '车辆能耗基准表',
    `monitor_id` varchar(40) NOT NULL COMMENT '监控对象id',
    `oil_type` smallint DEFAULT NULL COMMENT '油品 0:柴油 1:汽油',
    `install_date` DATE DEFAULT NULL COMMENT '节油产品安装日期',
    `invoice_picture` varchar(100) DEFAULT NULL COMMENT '单据图片',
    `day_item` mediumtext DEFAULT NULL COMMENT '日基准数据项 json格式',
    `total_item_id` bigint DEFAULT NULL COMMENT '总基准数据项',
    `down_threshold` smallint DEFAULT NULL COMMENT '怠（慢）速度阈值',
    `create_data_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_data_username` varchar(45) DEFAULT NULL COMMENT '创建人',
    `update_data_time` datetime DEFAULT NULL COMMENT '修改时间',
    `update_data_username` varchar(45) DEFAULT NULL COMMENT '修改人',
    PRIMARY KEY (`id`),
    UNIQUE INDEX MONITOR_INDEX(`monitor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#能耗总基准
#   车id、天数、总里程(KM)、行驶里程(KM)、怠(慢)速里程(KM)、发动机工作时长、行驶时长、怠(慢)速时长、综合平均车速、行驶平均车速、总用油量、行驶用油量、怠（慢）速用油量、
#   综合百公里油耗、行驶百公里油耗
#   怠（慢）速油耗、怠（慢）速度阈值、标准煤、百公里标准煤、百公里CO2排放量、百公里SO2排放量、百公里NOX排放量、污染空气/百公里、毁坏树/百公里、毁坏绿化面积/百公里
CREATE TABLE IF NOT EXISTS `clbs`.`zw_m_energy_consume_criterion_total` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '能耗基准总表',
    `day` int DEFAULT NULL COMMENT '基准天数',
    `total_mile` double DEFAULT NULL COMMENT '总里程(KM),保留一位小数',
    `travel_mile` double DEFAULT NULL COMMENT '行驶里程(KM),保留一位小数',
    `down_mile` double DEFAULT NULL COMMENT '怠(慢)速里程(KM),保留一位小数',
    `engine_work_duration` bigint DEFAULT NULL COMMENT '发动机工作时长(秒)',
    `travel_duration` bigint DEFAULT NULL COMMENT '行驶时长(秒)',
    `down_duration` bigint DEFAULT NULL COMMENT '怠(慢)速时长(秒)',
    `synthesize_avg_speed` double DEFAULT NULL COMMENT '综合平均车速(KM/H),保留一位小数',
    `travel_avg_speed` double DEFAULT NULL COMMENT '行驶平均车速(KM/H),保留一位小数',
    `total_oil_use_num` double DEFAULT NULL COMMENT '总用油量(L),保留一位小数',
    `travel_oil_use_num` double DEFAULT NULL COMMENT '行驶用油量(L),保留一位小数',
    `down_oil_use_num` double DEFAULT NULL COMMENT '怠（慢）速用油量(L),保留一位小数',
    `avg100Km_oil_consumption` double DEFAULT NULL COMMENT '综合百公里油耗(L/100KM),保留两位小数',
    `travel_avg100Km_oil_consumption` double DEFAULT NULL COMMENT '行驶百公里油耗(L/100KM),保留两位小数',
    `down_oil_consumption` double DEFAULT NULL COMMENT '怠（慢）速油耗(L/100KM),保留两位小数',
    `standard_coal` double DEFAULT NULL COMMENT '标准煤(Kg)',
    `avg100Km_standard_coal` double DEFAULT NULL COMMENT '百公里标准煤(Kg),保留三位小数',
    `avg100Km_CO2` double DEFAULT NULL COMMENT '百公里CO2排放量(Kg),保留三位小数',
    `avg100Km_SO2` double DEFAULT NULL COMMENT '百公里SO2排放量(g),保留三位小数',
    `avg100Km_NOX` double DEFAULT NULL COMMENT '百公里NOX排放量(g),保留三位小数',
    `avg100Km_polluted_air` double DEFAULT NULL COMMENT '污染空气(立方米)/百公里,保留三位小数',
    `avg100Km_destroy_trees` double DEFAULT NULL COMMENT '毁坏树(棵)/百公里,保留三位小数',
    `avg100Km_destroy_green_area` double DEFAULT NULL COMMENT '毁坏绿化面积(平方米)/百公里,保留三位小数',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#京标添加基础报警转发关系
insert into `zw_m_808_809_mapping`(`id`,`809_setting_id`,`808_pos`,`809_pos`,`time`,`protocol_type`,`flag`,`create_data_time`,`create_data_username`,`update_data_time`,`update_data_username`) values 
('fc8fe8a6-731a-1000-93b0-000c29920fdc','1012-default','1','0x0001',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1001-93b0-000c29920fdc','1012-default','2','0x0002',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1002-93b0-000c29920fdc','1012-default','0','0x0003',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1003-93b0-000c29920fdc','1012-default','20','0x0008',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1004-93b0-000c29920fdc','1012-default','26','0x0009',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1005-93b0-000c29920fdc','1012-default','23','0x000B',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1006-93b0-000c29920fdc','1012-default','28','0x000C',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1007-93b0-000c29920fdc','1012-default','18','0x000D',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1008-93b0-000c29920fdc','1012-default','15','0x0010',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1009-93b0-000c29920fdc','1012-default','16','0x0013',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL);

#删除京标809
DELETE from `clbs`.`zw_c_dictionary` where id = '3b3b6e9d-1d7c-4f95-a9fb-9d5d30f04732';
insert into `clbs`.`zw_c_dictionary`(`id`,`pid`,`code`,`value`,`type`,`description`,`sort`) values ('3b3b6e9d-1d7c-4f95-a9fb-9d5d30f04732',NULL,'1012','京标-809-2019-主动安全','809',NULL,16);



-- 京标添加基础报警转发关系
insert into `zw_m_808_809_mapping`(`id`,`809_setting_id`,`808_pos`,`809_pos`,`time`,`protocol_type`,`flag`,`create_data_time`,`create_data_username`,`update_data_time`,`update_data_username`) values 
('fc8fe8a6-731a-1000-93b0-000c29920fdc','1012-default','1','0x0001',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1001-93b0-000c29920fdc','1012-default','2','0x0002',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1002-93b0-000c29920fdc','1012-default','0','0x0003',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1003-93b0-000c29920fdc','1012-default','20','0x0008',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1004-93b0-000c29920fdc','1012-default','26','0x0009',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1005-93b0-000c29920fdc','1012-default','23','0x000B',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1006-93b0-000c29920fdc','1012-default','28','0x000C',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1007-93b0-000c29920fdc','1012-default','18','0x000D',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1008-93b0-000c29920fdc','1012-default','15','0x0010',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL),
('fc8fe8a6-731a-1009-93b0-000c29920fdc','1012-default','16','0x0013',0,1012,1,'2023-06-25 10:00:00','hujun',NULL,NULL);
