-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `collaborative-safety`
--

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `login_logout_report`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `login_logout_report` (IN `searchtext` TEXT, IN `selectiondate1` DATETIME, IN `selectiondate2` DATETIME)  BEGIN

                DECLARE _sql LONGTEXT;

                 DROP TABLE IF EXISTS TEMPCUMM;
                 CREATE temporary TABLE TEMPCUMM
                 (
                        id INT(10),
                    user_id INT(10),
                    activity varchar(15),
                    ip_address varchar(50),
                    previous_at datetime,
                    created_at datetime,
                    previous_activity varchar(15),
                    current_activity varchar(15)
                 );

                SET _sql = NULL;
                 SET _sql = ' IF(m.activity = "login",m.created_at,0) as login, IF(m.activity = "logout",m.created_at,0) as logout, IF(m.activity = "logout", m.previous_at,0 ) as login1 ';



                SET @DynamicPivotQuery = CONCAT('SELECT m.id,u.id as user_id , concat(u.first_name," ",u.last_name) as user,', _sql, ',m.ip_address,m.previous_activity,m.current_activity
                        FROM users as u
                      LEFT JOIN  TEMPCUMM as m ON m.user_id = u.id
                      WHERE m.activity = "login" OR m.activity = "logout"
                      group by u.id,m.created_at,m.ip_address
                      order BY m.created_at desc
                ');
                  PREPARE stmt FROM @DynamicPivotQuery;
                  EXECUTE stmt;
                  DEALLOCATE PREPARE stmt;
                  DROP  TABLE TEMPCUMM;
                END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `general_configs`
--

CREATE TABLE IF NOT EXISTS `general_configs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `tooltipval` mediumtext CHARACTER SET latin1,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `general_configs`
--

INSERT INTO `general_configs` (`id`, `name`, `tooltipval`, `status`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(6, 'Review', 'Review', 1, '2019-10-03 19:47:07', '2019-10-09 17:49:54', 1, 1, NULL),
(7, 'Review dataQ', 'Review data', 1, '2019-10-08 23:43:09', '2019-10-09 17:50:11', 1, 1, NULL),
(8, 'Review tool 3s', 'Review tool 3 tool tips', 1, '2019-10-09 22:25:38', '2019-10-09 22:26:08', 1, 1, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '2019_08_20_114555_create_categories_table', 1),
(2, '2019_08_26_134653_alterchanges', 1),
(3, '2019_08_27_112504_create_teams_table', 1),
(4, '2019_08_28_050946_create_faqs_table', 1),
(5, '2019_09_06_045906_alter_contact_us', 1),
(6, '2019_09_20_062434_create_events_table', 1),
(7, '2019_09_23_141300_login_logout_report', 1),
(8, '2019_09_25_055544_add_image_profile_to_users', 1);

-- --------------------------------------------------------

--
-- Table structure for table `organizations`
--

CREATE TABLE IF NOT EXISTS `organizations` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `email` varchar(255) CHARACTER SET latin1 NOT NULL,
  `phone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `organizations`
--

INSERT INTO `organizations` (`id`, `name`, `email`, `phone`, `status`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(1, 'Organization111111', 'nayan.dav1e@devdigital.com', '1234567890', 0, '2019-10-03 18:22:20', '2019-10-09 18:27:15', 1, 1, NULL),
(2, 'Organization 21', 'testwwew@test.com', '23121231231', 1, '2019-10-03 18:22:46', '2019-10-03 19:25:29', 1, 1, NULL),
(3, 'Organization 311', 'test@test.com', '42442342', 0, '2019-10-03 19:19:09', '2019-10-08 23:54:19', 1, 1, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `organization_users`
--

CREATE TABLE IF NOT EXISTS `organization_users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `organization_id` int(10) NOT NULL,
  `title` varchar(255) CHARACTER SET latin1 NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `organization_users`
--

INSERT INTO `organization_users` (`id`, `user_id`, `organization_id`, `title`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(3, 6, 2, 'Php developer', '2019-10-09 18:30:00', NULL, NULL, NULL, NULL),
(6, 11, 2, 'test', NULL, NULL, NULL, NULL, NULL),
(7, 8, 2, 'php developer', NULL, NULL, NULL, NULL, NULL),
(10, 14, 2, 'firstname', NULL, NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `reviews`
--

CREATE TABLE IF NOT EXISTS `reviews` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `reviews`
--

INSERT INTO `reviews` (`id`, `user_id`, `title`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(1, 6, 'My First review is there', '2019-10-09 19:30:00', NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `site_configs`
--

CREATE TABLE IF NOT EXISTS `site_configs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `val` mediumtext COLLATE utf8mb4_unicode_ci,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `site_configs`
--

INSERT INTO `site_configs` (`id`, `name`, `val`, `status`, `type`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(1, 'app_name', 'Collaborative Safety', 1, 'string', '2019-07-10 03:16:47', '2019-10-02 00:44:03', 1, 1, NULL),
(2, 'image', 'GH78FUUXuSP2X8YlJ1k0401qyOU9J1WD9w3zHWur.png', 1, 'string', '2019-07-10 03:16:47', '2019-07-10 03:31:07', 1, 1, NULL),
(3, 'records_per_page', '100', 1, 'int', '2019-07-10 03:16:47', '2019-07-15 05:48:45', 1, 1, NULL),
(4, 'footer_title_for_admin', '© Copyright 2019', 1, 'string', '2019-07-10 03:16:47', '2019-07-11 06:53:33', 1, 1, NULL),
(5, 'site_title', 'Collaborative Safety', 1, 'string', '2019-07-10 03:16:47', '2019-10-02 00:44:03', 1, 1, NULL),
(6, 'tagline', '', 1, 'string', '2019-07-10 03:16:47', '2019-10-02 00:44:03', 1, 1, NULL),
(7, 'copyright_text', '© Copyright 2019', 1, 'string', '2019-07-10 03:16:47', '2019-07-11 06:53:33', 1, 1, NULL),
(8, 'phone', '123-456-789', 1, 'string', '2019-07-10 03:16:47', '2019-08-06 00:13:21', 1, 1, NULL),
(9, 'from_name', 'Collaborative Safety', 1, 'string', '2019-07-10 03:16:48', '2019-10-07 03:06:45', 1, 1, NULL),
(10, 'from_email', 'contact@collaborativesafety.com', 1, 'email', '2019-07-10 03:16:48', '2019-10-02 00:44:04', 1, 1, NULL),
(11, 'admin_email', 'contact@collaborativesafety.com', 1, 'email', '2019-07-10 03:16:48', '2019-10-02 00:44:05', 1, 1, NULL),
(12, 'robots_meta_tag', '<meta name=\"robots\" content=\"noindex, nofollow\" />', 1, 'string', '2019-07-10 03:16:48', '2019-07-10 03:16:48', 1, 1, NULL),
(13, 'google_analytics_code', NULL, 1, 'string', '2019-07-10 03:16:48', '2019-07-10 03:16:48', 1, 1, NULL),
(14, 'advantage_title', 'Why The laravelmvc Systems?', 1, 'string', '2019-07-11 06:36:58', '2019-07-11 06:37:13', 1, 1, NULL),
(15, 'advantage_sub_title', 'Our advantages', 1, 'string', '2019-07-11 06:36:58', '2019-07-11 06:37:13', 1, 1, NULL),
(16, 'our_team_title', 'Why The laravelmvc Team?', 1, 'string', '2019-07-11 06:41:41', '2019-07-11 06:42:05', 1, 1, NULL),
(17, 'our_team_sub_title', 'Who are we', 1, 'string', '2019-07-11 06:41:41', '2019-07-11 06:42:05', 1, 1, NULL),
(18, 'disadvantage_title', 'The Not So Pure Alternatives', 1, 'string', '2019-07-11 06:45:09', '2019-07-11 06:45:09', 1, 1, NULL),
(19, 'disadvantage_sub_title', 'Disadvantages of others', 1, 'string', '2019-07-11 06:45:09', '2019-07-11 06:45:09', 1, 1, NULL),
(20, 'contact_title', 'Leave a request \r\nand our manager will contact you', 1, 'string', '2019-07-11 06:50:03', '2019-07-15 02:51:44', 1, 1, NULL),
(21, 'contact_sub_title', 'Any questions?', 1, 'string', '2019-07-11 06:50:03', '2019-07-15 04:04:33', 1, 1, NULL),
(22, 'email', 'monali.rana@devdigital.com', 1, 'string', '2019-07-11 06:51:58', '2019-08-05 05:52:44', 1, 1, NULL),
(23, 'disadvantage_left_section_title', 'Bottles & Jugs', 1, 'string', '2019-07-11 06:59:59', '2019-07-11 06:59:59', 1, 1, NULL),
(24, 'disadvantage_right_section_title', 'Water Fountains, Bottle Fillers, Non RO Systems', 1, 'string', '2019-07-11 06:59:59', '2019-07-11 06:59:59', 1, 1, NULL),
(25, 'contact_thank_you_message', 'Thank you for contacting us. We will respond to you as soon as possible.', 1, 'string', '2019-07-14 22:57:46', '2019-07-15 04:04:13', 1, 1, NULL),
(26, 'freetrial_title', 'Leave a request to schedule a free trial', 1, 'string', '2019-07-15 02:51:06', '2019-07-15 02:51:06', 1, 1, NULL),
(27, 'freetrial_thank_you_message_title', 'Great water is on the way!', 1, 'string', '2019-07-15 02:51:06', '2019-07-15 02:51:06', 1, 1, NULL),
(28, 'freetrial_thank_you_message_description', 'Your H2O Consultant will\r\ncontact you shortly', 1, 'string', '2019-07-15 02:51:06', '2019-07-15 03:16:03', 1, 1, NULL),
(29, 'referral_title', 'Share Your Love for\r\nlaravelmvc and Earn Cash', 1, 'string', '2019-07-15 02:51:21', '2019-07-15 03:19:10', 1, 1, NULL),
(30, 'referral_description', 'If you know of anyone who is using the 5-gallon jugs, or anyone who would enjoy our solution, please fill out the information below', 1, 'string', '2019-07-15 02:51:21', '2019-07-15 02:51:21', 1, 1, NULL),
(31, 'referral_thank_you_message', 'Thank you for referring the Contact. Our H2O Consultant will contact you shortly.', 1, 'string', '2019-07-15 02:51:21', '2019-07-17 00:00:20', 1, 1, NULL),
(32, 'logo', NULL, 1, 'file', '2019-08-06 01:09:10', '2019-08-06 01:09:10', 1, 1, NULL),
(33, 'blog_title', 'The Not So Pure Alternatives', 1, 'string', '2019-08-06 06:59:36', '2019-08-06 06:59:36', 2, 2, NULL),
(34, 'blog_sub_title', 'Sub Blog', 1, 'string', '2019-08-06 06:59:36', '2019-08-06 06:59:36', 2, 2, NULL),
(35, 'meta_description_for_front', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(36, 'company_name', 'Collaborative Safet', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(37, 'company_address_line1', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(38, 'company_address_line2', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(39, 'company_city', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(40, 'company_state', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(41, 'company_country', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(42, 'company_zipcode', '', 1, 'string', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(43, 'company_email', '', 1, 'email', '2019-10-02 00:44:04', '2019-10-02 00:44:04', 1, 1, NULL),
(44, 'password_strength', 'Easy', 1, 'string', '2019-10-02 00:44:05', '2019-10-02 00:44:05', 1, 1, NULL),
(45, 'login_attempt', '10', 1, 'int', '2019-10-02 00:44:05', '2019-10-02 00:44:05', 1, 1, NULL),
(46, 'minimum_password_length', '8', 1, 'int', '2019-10-02 00:44:05', '2019-10-02 00:44:05', 1, 1, NULL),
(47, 'user_block_time', '1', 1, 'int', '2019-10-02 00:44:05', '2019-10-02 00:44:05', 1, 1, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_types`
--

CREATE TABLE IF NOT EXISTS `user_types` (
  `id` tinyint(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  `updated_by` int(10) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_types`
--

INSERT INTO `user_types` (`id`, `title`, `status`, `created_at`, `updated_at`, `created_by`, `updated_by`, `deleted_at`) VALUES
(1, 'Super Admin', 1, '2019-07-02 18:30:00', '2019-07-02 18:30:00', 1, 1, NULL),
(2, 'Admin', 1, '2019-07-02 18:30:00', '2019-07-02 18:30:00', 1, 1, NULL),
(3, 'organization user', 1, '2019-07-02 18:30:00', '2019-07-02 18:30:00', 1, 1, NULL);
COMMIT;

