
--
-- Table structure for table `translator_langs`
--

CREATE TABLE `translator_langs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_trans_id` int(11) NOT NULL,
  `user_role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lang_trans_id` (`lang_trans_id`),
  KEY `user_role_id` (`user_role_id`),
  CONSTRAINT `FK_lang_trans` FOREIGN KEY (`lang_trans_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_role_trans` FOREIGN KEY (`user_role_id`) REFERENCES `user_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `translator_langs`
--

INSERT INTO `translator_langs` VALUES (1,8,33);

--
-- Table structure for table `user_lang`
--

CREATE TABLE `user_lang` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `lang_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `lang_id` (`lang_id`),
  CONSTRAINT `FK_lang_lang` FOREIGN KEY (`lang_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_lang` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `user_lang`
--

INSERT INTO `user_lang` VALUES (10,11,76),(13,14,51),(20,14,54),(22,21,43),(23,22,43),(26,25,43),(29,28,14),(32,31,76),(33,32,76),(34,33,14),(35,34,17),(40,37,76),(42,39,12),(45,40,20),(46,40,19),(47,41,9),(48,42,53),(49,43,66),(50,44,25),(51,45,70),(52,46,37),(53,47,61),(54,48,50),(55,49,13),(56,50,56),(57,51,34),(58,52,35),(59,53,59),(60,54,28),(61,55,39),(62,56,58),(63,57,74),(64,58,2),(65,59,40),(66,60,11),(67,61,43),(69,62,76),(74,63,43),(75,64,76),(77,66,76),(78,67,43),(79,68,2),(80,69,43),(83,56,14),(85,45,17),(86,71,43),(87,72,51),(88,73,51),(89,74,51),(90,75,43),(91,76,51),(92,77,14),(94,79,43),(95,80,58),(96,81,25),(97,82,17),(98,83,76),(99,84,28),(100,85,12),(101,86,12),(102,87,51),(103,88,40),(104,89,70),(105,90,17),(107,92,12),(108,93,40),(109,94,40),(110,95,40),(111,96,76),(112,97,76),(113,98,40),(114,99,40),(116,101,40),(118,103,53),(119,104,70),(120,105,76),(121,106,40),(125,110,40),(127,112,76),(128,113,12),(129,114,14),(130,115,70),(131,116,28),(132,117,20),(133,118,53),(134,119,76),(138,123,19),(139,124,78),(142,127,78),(143,128,78),(148,133,78),(153,138,77),(154,139,64),(155,140,1),(156,141,2),(157,142,2),(158,143,19),(159,144,20),(160,145,20),(161,146,20),(163,150,20),(164,151,20),(165,152,17),(166,153,20),(168,155,20),(169,156,20),(173,162,20),(174,165,22),(176,167,20),(177,168,6),(180,170,19),(183,173,17),(184,174,14),(185,175,1),(188,178,20),(192,185,8),(193,187,8),(194,188,41),(195,189,5),(196,190,5),(197,191,8),(198,56,64),(199,56,64),(200,56,8),(201,175,36),(202,175,53),(203,175,49),(204,194,15),(205,195,20),(206,196,64),(207,197,14),(208,199,3),(209,200,19),(210,201,17),(211,203,20),(212,204,14),(213,205,14),(214,206,20),(215,207,19),(216,208,19);

--
-- Table structure for table `user_lists`
--

CREATE TABLE `user_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `list_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `list_user_unique` (`list_id`,`user_id`),
  KEY `list_id` (`list_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `FK_list` FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_list` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `user_lists`
--


--
-- Table structure for table `user_roles`
--

CREATE TABLE `user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `FK_roles_user` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_roles` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `user_roles`
--

INSERT INTO `user_roles` VALUES (3,150,1),(4,151,1),(5,151,2),(6,152,1),(7,153,1),(9,155,1),(10,155,2),(14,162,1),(16,167,1),(17,167,2),(18,168,1),(23,173,1),(26,178,1),(30,185,2),(31,187,1),(32,188,2),(33,189,4),(34,190,1),(35,191,1),(36,194,1),(37,195,1),(38,196,1),(39,197,1),(40,199,2),(41,200,1),(42,201,1),(43,203,1),(44,204,1),(45,205,1),(46,206,1),(47,207,1),(48,208,2);

--
-- Dumping routines for database 'yii2advanced'
--

--
-- Final view structure for view `all_authors_view`
--

/*!50001 DROP VIEW IF EXISTS `all_authors_view`*/;

--
-- Final view structure for view `author_score`
--

/*!50001 DROP VIEW IF EXISTS `author_score`*/;

--
-- Final view structure for view `authors_view`
--

/*!50001 DROP VIEW IF EXISTS `authors_view`*/;

--
-- Final view structure for view `clients_view`
--

/*!50001 DROP VIEW IF EXISTS `clients_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `clients_view` AS select `t1`.`id` AS `id`,`t1`.`name` AS `company_name`,`t1`.`industry_id` AS `industry_id`,`t1`.`country_id` AS `country_id`,`t1`.`city` AS `city`,`t1`.`situation` AS `situation`,concat(`t2`.`first_name`,' ',`t2`.`last_name`) AS `contact_name`,`t2`.`gender` AS `gender`,`t2`.`position` AS `position`,`t1`.`payment_method` AS `payment_method`,`t1`.`discount` AS `discount`,`t1`.`due_days` AS `due_days`,`t1`.`potential_id` AS `potential_id`,`t1`.`client_type_id` AS `client_type_id`,`t1`.`lead_source` AS `lead_source`,`t1`.`notes` AS `notes` from (`clients` `t1` join `client_contacts` `t2` on((`t2`.`client_id` = `t1`.`id`))) */;

--
-- Final view structure for view `editors_tests`
--

/*!50001 DROP VIEW IF EXISTS `editors_tests`*/;

--
-- Final view structure for view `editors_view`
--

/*!50001 DROP VIEW IF EXISTS `editors_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `editors_view` AS select `editors_tests`.`id` AS `id`,`editors_tests`.`user_id` AS `user_id`,`editors_tests`.`first_last_name` AS `first_last_name`,`editors_tests`.`email` AS `email`,`editors_tests`.`user_status` AS `user_status`,count(`editors_tests`.`test_status`) AS `cnt_tests`,avg(`editors_tests`.`avg_test`) AS `avg_results` from `editors_tests` group by `editors_tests`.`id`,`editors_tests`.`user_id`,`editors_tests`.`first_last_name`,`editors_tests`.`email`,`editors_tests`.`user_status` */;

--
-- Final view structure for view `historical_paid_payment_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `historical_paid_payment_bill_view`*/;

--
-- Final view structure for view `historical_paid_payments_view`
--

/*!50001 DROP VIEW IF EXISTS `historical_paid_payments_view`*/;

--
-- Final view structure for view `historical_payment_admin_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `historical_payment_admin_bill_view`*/;

--
-- Final view structure for view `historical_payment_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `historical_payment_bill_view`*/;

--
-- Final view structure for view `historical_payments_view`
--

/*!50001 DROP VIEW IF EXISTS `historical_payments_view`*/;

--
-- Final view structure for view `max_test_id`
--

/*!50001 DROP VIEW IF EXISTS `max_test_id`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `max_test_id` AS select `tests`.`author_id` AS `author_id`,max(`tests`.`id`) AS `test_id` from `tests` where (`tests`.`status` = 1) group by `tests`.`author_id` */;

--
-- Final view structure for view `paid_payment_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `paid_payment_bill_view`*/;

--
-- Final view structure for view `paid_payments_view`
--

/*!50001 DROP VIEW IF EXISTS `paid_payments_view`*/;

--
-- Final view structure for view `payment_admin_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `payment_admin_bill_view`*/;

--
-- Final view structure for view `payment_bill_view`
--

/*!50001 DROP VIEW IF EXISTS `payment_bill_view`*/;

--
-- Final view structure for view `payments_view`
--

/*!50001 DROP VIEW IF EXISTS `payments_view`*/;

--
-- Final view structure for view `scoring_rate_view`
--

/*!50001 DROP VIEW IF EXISTS `scoring_rate_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`developer`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `scoring_rate_view` AS select `scoring_amount`.`user_id` AS `user_id`,`scoring_amount`.`participant_type` AS `participant_type`,`scoring_amount`.`scoring_type` AS `scoring_type`,(`scoring_amount`.`total_scoring` / `scoring_count`.`count_scoring`) AS `rate` from (((select `yii2advanced`.`scoring`.`user_id` AS `user_id`,`yii2advanced`.`scoring`.`participant_type` AS `participant_type`,`yii2advanced`.`scoring`.`scoring_type` AS `scoring_type`,sum(`yii2advanced`.`scoring`.`scoring`) AS `total_scoring` from `yii2advanced`.`scoring` group by `yii2advanced`.`scoring`.`user_id`,`yii2advanced`.`scoring`.`participant_type`,`yii2advanced`.`scoring`.`scoring_type`)) `scoring_amount` join (select `yii2advanced`.`scoring`.`user_id` AS `user_id`,`yii2advanced`.`scoring`.`participant_type` AS `participant_type`,`yii2advanced`.`scoring`.`scoring_type` AS `scoring_type`,count(`yii2advanced`.`scoring`.`scoring`) AS `count_scoring` from `yii2advanced`.`scoring` where (`yii2advanced`.`scoring`.`scoring` > 0) group by `yii2advanced`.`scoring`.`user_id`,`yii2advanced`.`scoring`.`participant_type`,`yii2advanced`.`scoring`.`scoring_type`) `scoring_count` on(((`scoring_amount`.`user_id` = `scoring_count`.`user_id`) and (`scoring_amount`.`participant_type` = `scoring_count`.`participant_type`) and (`scoring_amount`.`scoring_type` = `scoring_count`.`scoring_type`)))) */;

--
-- Final view structure for view `tests_view`
--

/*!50001 DROP VIEW IF EXISTS `tests_view`*/;


