# set locations.id to vehicles.location_id DELETE FROM vehicles WHERE reference_location_id NOT IN (SELECT reference_location_id FROM locations); DELETE FROM vehicles WHERE id NOT IN (SELECT vehicle_id FROM vehicle_violation); ALTER TABLE vehicle_violation ADD INDEX vehicle_violation_vehicle_id(vehicle_id); UPDATE vehicles v, locations l SET v.location_id=l.id, v.location_id_name=l.name WHERE l.reference_location_id=v.reference_location_id; UPDATE vehicles set vehicle_missed_locations=null where action_id!=2; UPDATE vehicles set action_remarks=null where action_remarks="undefined"; # validate all location is set SELECT location_id_name, reference_location_id FROM vehicles WHERE location_id_name IS NULL or location_id IS NULL; DROP TABLE IF EXISTS vehicle_violation_flat; Create TABLE vehicle_violation_flat ( SELECT vehicle_id, MAX(CASE WHEN violation_type_id = 1 THEN 1 ELSE 0 END) AS violation_criminal, MAX(CASE WHEN violation_type_id = 2 THEN 1 ELSE 0 END) AS violation_excise, MAX(CASE WHEN violation_type_id = 3 THEN 1 ELSE 0 END) AS violation_num_plate_mismatched, MAX(CASE WHEN violation_type_id = 4 THEN 1 ELSE 0 END) AS violation_stolen, MAX(CASE WHEN violation_type_id = 5 THEN 1 ELSE 0 END) AS violation_made_mismatched, MAX(CASE WHEN violation_type_id = 6 THEN 1 ELSE 0 END) AS violation_color_mismatched, MAX(CASE WHEN violation_type_id = 7 THEN 1 ELSE 0 END) AS violation_seat_belt, MAX(CASE WHEN violation_type_id = 8 THEN 1 ELSE 0 END) AS violation_mobile_use, MAX(CASE WHEN violation_type_id = 9 THEN 1 ELSE 0 END) AS government_vehicle, MAX(CASE WHEN violation_type_id = 10 THEN 1 ELSE 0 END) AS applied_for_registration, MAX(CASE WHEN violation_type_id = 11 THEN 1 ELSE 0 END) AS violation_vehicle_type_mismatched, MAX(CASE WHEN violation_type_id = 12 THEN 1 ELSE 0 END) AS violation_snatched, MAX(CASE WHEN violation_type_id = 13 THEN 1 ELSE 0 END) AS violation_burned, MAX(CASE WHEN violation_type_id = 14 THEN 1 ELSE 0 END) AS violation_wanted, MAX(CASE WHEN violation_type_id = 15 THEN 1 ELSE 0 END) AS violation_not_registered_afr, MAX(CASE WHEN violation_type_id = 16 THEN 1 ELSE 0 END) AS violation_not_registered_apl, MAX(CASE WHEN violation_type_id = 17 THEN 1 ELSE 0 END) AS violation_unknown, count(1) as viloation_count FROM vehicle_violation GROUP BY vehicle_id ); UPDATE vehicles SET action_id=NULL, action_user_id=NULL, action_dt=NULL WHERE action_id=0; TRUNCATE rpt_location_hourly_summeries; INSERT IGNORE INTO rpt_location_hourly_summeries ( `date`, `year`, `month`, `day`, `hour`, `location_id`, `location_id_name`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT DATE(entry_datetime) AS entry_date_part, year(entry_datetime) AS year, MONTH(entry_datetime) AS month, DAY(entry_datetime) AS day, HOUR(entry_datetime) AS hour, location_id, location_id_name, count(vehicles.id) AS vehicle_count, SUM(IF((action_id IS NULL), 0, 1)) AS vehicle_action_count, SUM(IF((vehicle_missed_locations IS NULL), 0, 1)) AS vehicle_missed_count, SUM(IF((ack_user_id IS NULL), 0, 1)) AS vehicle_ack_count, SUM(vehicle_violation_flat.violation_criminal) as violation_criminal, SUM(vehicle_violation_flat.violation_excise) as violation_excise, SUM(vehicle_violation_flat.violation_num_plate_mismatched) as violation_num_plate_mismatched, SUM(vehicle_violation_flat.violation_color_mismatched) as violation_color_mismatched, SUM(vehicle_violation_flat.violation_stolen) as violation_stolen, SUM(vehicle_violation_flat.violation_made_mismatched) as violation_made_mismatched, SUM(vehicle_violation_flat.violation_seat_belt) as violation_seat_belt, SUM(vehicle_violation_flat.violation_mobile_use) as violation_mobile_use, SUM(vehicle_violation_flat.violation_vehicle_type_mismatched) as violation_vehicle_type_mismatched, SUM(vehicle_violation_flat.violation_snatched) as violation_snatched, SUM(vehicle_violation_flat.violation_burned) as violation_burned, SUM(vehicle_violation_flat.violation_wanted) as violation_wanted, sum(vehicle_violation_flat.violation_criminal) + sum(vehicle_violation_flat.violation_color_mismatched) + sum(vehicle_violation_flat.violation_stolen) + sum(vehicle_violation_flat.violation_made_mismatched) + sum(vehicle_violation_flat.violation_vehicle_type_mismatched) + sum(vehicle_violation_flat.violation_snatched) + sum(vehicle_violation_flat.violation_burned) + sum(vehicle_violation_flat.violation_wanted) + sum(violation_not_registered_afr) + sum(violation_not_registered_apl) + sum(violation_unknown) as violation_critical, sum(vehicle_violation_flat.violation_seat_belt)+sum(vehicle_violation_flat.violation_mobile_use) as violation_minor, sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM vehicles INNER JOIN vehicle_violation_flat ON vehicles.id=vehicle_violation_flat.vehicle_id GROUP BY entry_date_part, year, month, day, hour, vehicles.location_id, vehicles.location_id_name; TRUNCATE rpt_location_daily_summeries; INSERT IGNORE INTO rpt_location_daily_summeries( `date`, `year`, `month`, `day`, `location_id`, `location_id_name`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `date`, `year`, `month`, `day`, `location_id`, `location_id_name`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_location_hourly_summeries GROUP BY `date`, `year`, `month`, `day`, `location_id`, `location_id_name`; TRUNCATE rpt_location_monthly_summeries; INSERT IGNORE INTO rpt_location_monthly_summeries( `year`, `month`, `location_id`, `location_id_name`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `year`, `month`, `location_id`, `location_id_name`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), , sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_location_daily_summeries GROUP BY `year`, `month`, `location_id`, `location_id_name`; TRUNCATE rpt_location_yearly_summeries; INSERT IGNORE INTO rpt_location_yearly_summeries( `year`, `location_id`, `location_id_name`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `year`, `location_id`, `location_id_name`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_location_monthly_summeries GROUP BY `year`, `location_id`, `location_id_name`; TRUNCATE rpt_all_location_hourly_summeries; INSERT IGNORE INTO rpt_all_location_hourly_summeries( `date`, `year`, `month`, `day`, `hour`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `date`, `year`, `month`, `day`, `hour`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_location_hourly_summeries GROUP BY `date`, `year`, `month`, `day`, `hour`; TRUNCATE rpt_all_location_daily_summeries; INSERT IGNORE INTO rpt_all_location_daily_summeries( `date`, `year`, `month`, `day`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `date`, `year`, `month`, `day`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_all_location_hourly_summeries GROUP BY `date`, `year`, `month`, `day`; TRUNCATE rpt_all_location_monthly_summeries; INSERT IGNORE INTO rpt_all_location_monthly_summeries( `year`, `month`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `year`, `month`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_all_location_daily_summeries GROUP BY `year`, `month`; TRUNCATE rpt_all_location_yearly_summeries; INSERT IGNORE INTO rpt_all_location_yearly_summeries( `year`, `vehicle_count`, `vehicle_action_count`, `vehicle_missed_count`, `vehicle_ack_count`, `violation_criminal`, `violation_excise`, `violation_num_plate_mismatched`, `violation_color_mismatched`, `violation_stolen`, `violation_made_mismatched`, `violation_seat_belt`, `violation_mobile_use`, `violation_vehicle_type_mismatched`, `violation_snatched`, `violation_burned`, `violation_wanted`, `violation_critical`, `violation_minor`, `violation_not_registered_afr`, `violation_not_registered_apl`, `violation_unknown` ) SELECT `year`, SUM(vehicle_count), SUM(vehicle_action_count), SUM(vehicle_missed_count), SUM(vehicle_ack_count), SUM(violation_criminal), SUM(violation_excise), SUM(violation_num_plate_mismatched), SUM(violation_color_mismatched), SUM(violation_stolen), SUM(violation_made_mismatched), SUM(violation_seat_belt), SUM(violation_mobile_use), SUM(violation_vehicle_type_mismatched), SUM(violation_snatched), SUM(violation_burned), SUM(violation_wanted), SUM(violation_critical), SUM(violation_minor), sum(violation_not_registered_afr), sum(violation_not_registered_apl), sum(violation_unknown) FROM rpt_all_location_daily_summeries GROUP BY `year`; update users u, vehicles v SET v.action_user_id_name=u.full_name WHERE u.id=v.action_user_id;