-- phpMyAdmin SQL Dump -- version 4.9.0.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Oct 21, 2021 at 11:13 AM -- Server version: 10.4.6-MariaDB -- PHP Version: 7.2.22 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `affiliates-app` -- DELIMITER $$ -- -- Functions -- CREATE DEFINER=`leads_crm`@`%` FUNCTION `calculateOrderRevenue` (`startDate` DATETIME, `endDate` DATETIME, `orderId` INT) RETURNS VARCHAR(50) CHARSET latin1 BEGIN DECLARE price DOUBLE; DECLARE cost DOUBLE; SELECT SUM(ftdPrice(ftd_date, orderId, order_lead_id)), SUM(ftdCost(ftd_date, orderId, order_lead_id)) INTO price,cost FROM order_ftds WHERE `order_id`=orderId AND ftd_date BETWEEN startDate AND endDate LIMIT 1; IF price IS NULL THEN SET price = 0; END IF; IF cost IS NULL THEN SET cost = 0; END IF; RETURN CONCAT_WS('|', price, cost); END$$ CREATE DEFINER=`leads_crm`@`%` FUNCTION `ftdCost` (`ftdDate` DATETIME, `orderId` INT, `orderLeadId` INT) RETURNS DOUBLE UNSIGNED BEGIN DECLARE cost DOUBLE; DECLARE sourceId INT; DECLARE countryId INT; SELECT source_id, country_id INTO sourceId, countryId FROM order_leads WHERE id = orderLeadId LIMIT 1; SELECT cpa_cost INTO cost FROM order_prices WHERE order_id = orderId AND source_id = sourceId AND country_id = countryId AND ftdDate BETWEEN start_date AND IF(end_date IS NOT NULL,end_date,'3000-01-01 23:59:59') LIMIT 1; RETURN cost; END$$ CREATE DEFINER=`leads_crm`@`%` FUNCTION `ftdPrice` (`ftdDate` DATETIME, `orderId` INT, `orderLeadId` INT) RETURNS DOUBLE UNSIGNED BEGIN DECLARE price DOUBLE; DECLARE sourceId INT; DECLARE countryId INT; SELECT source_id, country_id INTO sourceId, countryId FROM order_leads WHERE id = orderLeadId LIMIT 1; SELECT cpa_price INTO price FROM order_prices WHERE order_id = orderId AND source_id = sourceId AND country_id = countryId AND ftdDate BETWEEN start_date AND IF(end_date IS NOT NULL,end_date,'3000-01-01 23:59:59') LIMIT 1; RETURN price; END$$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `languages` -- CREATE TABLE `languages` ( `id` int(10) UNSIGNED NOT NULL, `user_id` int(11) NOT NULL, `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `default` tinyint(4) NOT NULL, `order` tinyint(4) NOT NULL, `status` tinyint(4) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `languages` -- INSERT INTO `languages` (`id`, `user_id`, `name`, `code`, `default`, `order`, `status`, `created_at`, `updated_at`) VALUES (1, 1, 'English', 'en', 1, 1, 1, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `roles` -- CREATE TABLE `roles` ( `id` int(10) UNSIGNED NOT NULL, `user_id` int(11) NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `permissions` text COLLATE utf8_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `roles` -- INSERT INTO `roles` (`id`, `user_id`, `name`, `description`, `permissions`, `created_at`, `updated_at`) VALUES (1, 1, 'Admin', '', 'a:65:{i:0;s:10:\"leads.list\";i:1;s:12:\"leads.create\";i:2;s:12:\"leads.update\";i:3;s:12:\"leads.delete\";i:4;s:12:\"leads.import\";i:5;s:12:\"leads.export\";i:6;s:18:\"leads.assign-agent\";i:7;s:20:\"leads.alocate-source\";i:8;s:19:\"leads.alocate-order\";i:9;s:21:\"leads.dealocate-order\";i:10;s:10:\"leads.send\";i:11;s:12:\"history.list\";i:12;s:12:\"history.view\";i:13;s:14:\"history.delete\";i:14;s:21:\"history.change-status\";i:15;s:10:\"types.list\";i:16;s:12:\"types.create\";i:17;s:12:\"types.update\";i:18;s:12:\"types.delete\";i:19;s:18:\"lead-statuses.list\";i:20;s:20:\"lead-statuses.create\";i:21;s:20:\"lead-statuses.update\";i:22;s:20:\"lead-statuses.delete\";i:23;s:14:\"customers.list\";i:24;s:16:\"customers.create\";i:25;s:16:\"customers.update\";i:26;s:16:\"customers.delete\";i:27;s:22:\"customers.save-contact\";i:28;s:23:\"customers.contact-order\";i:29;s:24:\"customers.delete-contact\";i:30;s:31:\"customers.contact-change-status\";i:31;s:24:\"customers.update-contact\";i:32;s:17:\"source-types.list\";i:33;s:19:\"source-types.create\";i:34;s:19:\"source-types.update\";i:35;s:19:\"source-types.delete\";i:36;s:12:\"imports.list\";i:37;s:14:\"imports.create\";i:38;s:14:\"imports.delete\";i:39;s:12:\"exports.list\";i:40;s:14:\"exports.create\";i:41;s:14:\"exports.delete\";i:42;s:21:\"import-templates.list\";i:43;s:23:\"import-templates.create\";i:44;s:23:\"import-templates.delete\";i:45;s:22:\"service-providers.list\";i:46;s:24:\"service-providers.create\";i:47;s:24:\"service-providers.update\";i:48;s:24:\"service-providers.delete\";i:49;s:12:\"reports.list\";i:50;s:10:\"users.list\";i:51;s:12:\"users.create\";i:52;s:12:\"users.update\";i:53;s:12:\"users.delete\";i:54;s:10:\"roles.list\";i:55;s:12:\"roles.create\";i:56;s:12:\"roles.update\";i:57;s:12:\"roles.delete\";i:58;s:14:\"api-users.list\";i:59;s:16:\"api-users.create\";i:60;s:16:\"api-users.update\";i:61;s:16:\"api-users.delete\";i:62;s:20:\"files-manager.access\";i:63;s:13:\"settings.view\";i:64;s:15:\"settings.update\";}', '2017-10-28 21:00:00', '2018-03-13 15:41:56'), (2, 5, 'Test', '', 'a:2:{i:0;s:10:\"leads.list\";i:1;s:12:\"history.list\";}', '2017-11-07 20:55:34', '2017-11-07 20:55:40'), (3, 7, 'affiliate', '', 'N;', '2019-07-31 09:49:43', '2019-07-31 09:49:55'); -- -------------------------------------------------------- -- -- Table structure for table `role_user` -- CREATE TABLE `role_user` ( `user_id` int(10) UNSIGNED NOT NULL, `role_id` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `role_user` -- INSERT INTO `role_user` (`user_id`, `role_id`) VALUES (1, 1), (2, 1), (4, 1), (7, 1), (10, 1), (11, 1); -- -------------------------------------------------------- -- -- Table structure for table `settings` -- CREATE TABLE `settings` ( `id` int(10) UNSIGNED NOT NULL, `key` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `value` text COLLATE utf8_unicode_ci NOT NULL, `type` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `order` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `settings` -- INSERT INTO `settings` (`id`, `key`, `value`, `type`, `order`) VALUES (1, 'debug', 'true', 'radio', 1), (2, 'send-leads-job-finish-mail-subject', 'Send lead finish', 'input', 2), (3, 'send-leads-job-finish-from-email', 'no-reply@traderleads.com', 'input', 4), (4, 'send-leads-job-finish-from-name', 'Admin', 'textarea', 5), (9, 'send-leads-job-finish-mail-body', 'Hello {name},\n\nSending leads job is done!\nOrder code: {orderCode}', 'textarea', 6), (10, 'default_import_email', '', 'input', 7), (11, 'default_import_email_subject', '', 'input', 8), (12, 'imports-job-finish-mail-subject', 'Imports finish', 'input', 0), (13, 'imports-job-finish-from-email', 'no-reply@traderleads.com', 'input', 0), (14, 'imports-job-finish-from-name', 'Admin', 'textarea', 0), (15, 'imports-job-finish-mail-body', 'Hello {name},\nImports job is done!', 'textarea', 0), (16, 'export-leads-mail-subject', 'export subject', 'text', 0), (17, 'export-leads-from-address', 'demo@demo.com', 'text', 0), (18, 'export-leads-from-name', 'demo', 'text', 0), (19, 'export-leads-job-finish-mail-body', '', 'textarea', 0), (20, 'check-ftd-manual-running', '0', 'text', 0), (21, 'check-status-manual-running', '0', 'text', 0); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(10) UNSIGNED NOT NULL, `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `api_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `api_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `permissions` text COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `password_updated` datetime DEFAULT NULL, `status` tinyint(4) NOT NULL, `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `api_username`, `api_password`, `permissions`, `password`, `password_updated`, `status`, `remember_token`, `created_at`, `updated_at`) VALUES (1, 'Radu', 'Patron', 'radup@olahmedia.com', '', '', 'N;', '$2y$10$Osd0EmEcARKpLJkih64DRua9JXdFbJtRRzD9orS4HCyhlCoLQuH1O', '2021-01-11 12:43:36', 1, '7tKHBoYZTQDxJjAz1zW2f80uWgKCG50iRrYgyRrjpOaXC5zEu06lMWBZREvo', '2017-10-28 21:00:00', '2021-02-09 11:13:37'), (2, 'Nimrod', 'Cohen', 'nimrod@trader-leads.com', '', '', 'N;', '$2y$10$5TuqKUcNorro/OdkeBCDPeuqb1kTPRKn3cm7Bz.ku/tdpVXo/2wJq', '2021-02-14 07:44:35', 1, 'sDsR8o3wanxlUZ5ZNh4p30zk79m4GFACEzik1Lh9eA0Kdouyj2UAITi9FUp6', '2017-10-28 21:00:00', '2021-04-15 04:35:18'), (4, 'Nimrod ', 'Pinto', 'Nimrodp@players-leads.com', '', '', 'N;', '$2y$10$u144GUc4xJR7IL7n5j7HAekp735lRWcMOu09gMzXklKfvwGK1Livm', '2021-01-07 10:03:27', 1, NULL, '2017-10-29 14:59:30', '2021-01-07 08:03:27'), (7, 'Constantin Silviu', 'Nuta', 'silviun@olahmedia.com', 'charmelle', '123456', 'N;', '$2y$10$GuDopY7WtVHhOB7kJMhZTOHfoqn2RHaKhlj9rwPAFMI.mPcpsSVrO', '2021-02-02 16:14:10', 1, 'HH18TS1hQSv5VfthBV7nDCRciljDP4tXWmyXZTAiVhU68OwhobablIaZlNEG', '2018-10-16 09:47:11', '2021-02-02 14:14:10'), (10, 'Sarit', 'Cohen', 'sarit81co@gmail.com', '', '', 'N;', '$2y$10$VBOn0bQKB3wAZOHZQNttOen8laEaoCQA3mH/eb33HTKCpA86CE2EK', '2020-12-23 17:56:32', 1, NULL, '2020-07-28 05:40:24', '2020-12-23 15:56:32'), (11, 'Finance', 'Finance', 'Finance@trader-leads.com', '', '', 'N;', '$2y$10$t0R9Q3OI4eChz1c/HLQHKetXm2oQQT/z7azDk0NFRLX0feTrluId2', NULL, 1, NULL, '2021-03-16 16:16:16', '2021-03-16 16:16:52'); -- -- Indexes for dumped tables -- -- -- Indexes for table `languages` -- ALTER TABLE `languages` ADD PRIMARY KEY (`id`), ADD KEY `languages_user_id_default_order_status_index` (`user_id`,`default`,`order`,`status`); -- -- Indexes for table `roles` -- ALTER TABLE `roles` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `roles_name_unique` (`name`), ADD KEY `roles_user_id_index` (`user_id`); -- -- Indexes for table `role_user` -- ALTER TABLE `role_user` ADD PRIMARY KEY (`user_id`,`role_id`), ADD KEY `role_user_role_id_foreign` (`role_id`); -- -- Indexes for table `settings` -- ALTER TABLE `settings` ADD PRIMARY KEY (`id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `users_email_unique` (`email`), ADD KEY `id` (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `languages` -- ALTER TABLE `languages` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `roles` -- ALTER TABLE `roles` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `settings` -- ALTER TABLE `settings` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;