39388 - No sql file 35784 - Yes, 35784-mixed-transactions-missing-in-eod-list-UAT - (Menu Query changed) 39487 - No sql file 39563 - No sql file 39574 - No sql file 39599 - No sql file 39623 - No sql file 39649 - No sql file US779 - Yes, US779-dwell-time-report-with-inifiniti - (Menu Query changed) 39598 - No sql file US790 - No sql file 38024 - No sql file 38963 - No sql file US731 - Yes, US731-admin-alert-messages-reports-and-tariff-changes - (Menu Query changed) US782 - Yes, US782-Vehicle-Frequency-Report - (Menu Query changed) US724 - No sql file 39734 - No sql file /*---------------------1) 35784 - 35784-mixed-transactions-missing-in-eod-list-UAT menu query-------------*/ INSERT INTO `sps_menu` (`menu_item`, `menu_url`, `menu_class`, `parent_menu_id`, `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_by`) VALUES ('mixed_transaction', 'admin/reports/mixed_transaction', 'menu', '46', '', '11.28', '1', '1', '1', '1', CURRENT_TIMESTAMP, '0'); INSERT INTO `sps_menu_action_mapping` (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`) VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "mixed_transaction"), '1', '0', '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_menu_action_mapping` (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`) VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "mixed_transaction"), '6', '0', '0', '0', '0', CURRENT_TIMESTAMP); /*sps_access_rights table query*/ INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "mixed_transaction"), '1', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "mixed_transaction"), '6', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); /*---------------------------------------2) US779 - US779-dwell-time-report-with-inifiniti menu query----------------------*/ /*--Please check the sps_menu table and check the order column--*/ INSERT INTO `sps_menu` (`id`, `menu_item`, `menu_url`, `menu_class`, `parent_menu_id`, `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (NULL, 'dwell_time_report', 'admin/dwell_time_report', 'menu', '46', '', '11.31', '1', '1', '1', '1', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL, ''); /*--sps_menu_mapping---- please check the menu_id column of sps_menu_mapping and id column of sps_menu. It should be the same.*/ INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "dwell_time_report"), '1', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "dwell_time_report"), '6', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); /*sps_access_rights table query*/ INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "dwell_time_report"), '1', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "dwell_time_report"), '6', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); /*---------------------------------3) US731 - US731-admin-alert-messages-reports-and-tariff-changes menu query--------------------------------*/ INSERT INTO `sps_menu` (`menu_item`, `menu_url`, `menu_class`, `parent_menu_id`, `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_by`) VALUES ('alert_messages_configuration', 'admin/alert_msg_configuration', 'menu', '11', '1,2', '2.11', '1', '0', '0', '0', 'CURRENT_TIMESTAMP', '0'); INSERT INTO `sps_menu_action_mapping` (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `deleted_at`) VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '1', '0', '0', '0', '0', 'CURRENT_TIMESTAMP', NULL); INSERT INTO `sps_menu_action_mapping` (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `deleted_at`) VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '3', '0', '0', '0', '0', 'CURRENT_TIMESTAMP', NULL); CREATE TABLE `sps_tariff_list` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tariff_name` varchar(150) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `sps_reports_list` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sps_tariff_list` (`tariff_name`) VALUES('casual_tariff'),('overnight_tariff'),('additional_tariff'); INSERT INTO `sps_reports_list` (report_name) SELECT `menu_item` FROM `sps_menu` WHERE `parent_menu_id` = (SELECT `id` FROM `sps_menu` WHERE `menu_item` = 'reports') AND `menu_item` NOT IN ('configure_statistic_list', 'overnight_vehicle','tariff_report') AND `status` = '1'; CREATE TABLE `sps_alert_msg_config` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `reference_id` INT(11) NOT NULL, `on_email` ENUM('0','1') NOT NULL DEFAULT '0', `on_sms` ENUM('0','1') NOT NULL DEFAULT '0', `screen_type` VARCHAR(50) NOT NULL, `created_by` VARCHAR(50) DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `reference_id` (`reference_id`), KEY `screen_type` (`screen_type`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE `sps_alert_msg_config_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `config_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `config_id` (`config_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `sps_email_log` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `to` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `from` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `cc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `bcc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `body` longtext COLLATE utf8_unicode_ci NOT NULL, `email_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `attachment_path` longtext COLLATE utf8_unicode_ci, `remark` longtext COLLATE utf8_unicode_ci, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*sps_access_rights table query*/ INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '1', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '3', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); /*-----------------------------------4) US782 - US782-Vehicle-Frequency-Report menu query-----------------------*/ /*sps_menu table query*/ INSERT INTO `sps_menu` (`id`, `menu_item`, `menu_url`, `menu_class`, `parent_menu_id`, `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (NULL, 'frequency_report', 'admin/frequency_report', 'menu', '46', '', '11.32', '1', '1', '1', '1', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL, ''); /*sps_menu mapping table query*/ INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "frequency_report"), '1', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "frequency_report"), '6', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); /*If sps_reports_list table exist in database then run following query for alert configuration*/ INSERT INTO `sps_reports_list` (`id`, `report_name`) VALUES (NULL, 'frequency_report'); /*sps_access_rights table query*/ INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "frequency_report"), '1', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "frequency_report"), '6', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); /*----Query For Export function of Loyalty Discount and Max Charge Discount for Report Section is added below - Same is added in US775 sql file*/ INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "loyalty_discount"), '6', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "loyalty_discount"), '6', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP); INSERT INTO `sps_menu_action_mapping` (`id`, `menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "max_charge_discount"), '6', '0', '0', '0', '0', CURRENT_TIMESTAMP, '0000-00-00 00:00:00.000000', NULL); INSERT INTO `sps_access_rights` (`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`) VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "max_charge_discount"), '6', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP);