-- Table structure for table `personnel_leaveday_types`
--

CREATE TABLE `personnel_leaveday_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `number` float DEFAULT NULL COMMENT 'Số ngày nghỉ',
  `year` float DEFAULT NULL COMMENT 'Theo năm',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_leaves`
--

CREATE TABLE `personnel_leaves` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `reason` varchar(5) NOT NULL,
  `personnel_id` bigint(20) NOT NULL,
  `date_start` datetime DEFAULT NULL,
  `date_end` datetime DEFAULT NULL,
  `approver_id` bigint(20) DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0',
  `desc` text,
  `date_approved` datetime DEFAULT NULL,
  `comment_approve` varchar(500) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `action_history` text,
  `reason_percent` float DEFAULT NULL,
  `reason_day_all` float DEFAULT NULL,
  `reason_day_used` float DEFAULT NULL,
  `reason_day_had` float DEFAULT NULL,
  `day_number` float DEFAULT NULL,
  `reason_day_end` float DEFAULT NULL,
  `step` tinyint(4) DEFAULT '1',
  `current_step` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `personnel_id` (`personnel_id`,`date_created`),
  KEY `personnel_id_2` (`personnel_id`,`date_created`),
  KEY `personnel_id_3` (`personnel_id`,`date_created`),
  KEY `personnel_id_4` (`personnel_id`,`date_created`),
  KEY `personnel_id_5` (`personnel_id`,`date_created`),
  KEY `personnel_id_6` (`personnel_id`,`date_created`)
) ENGINE=InnoDB AUTO_INCREMENT=8476 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_add_more_setting`
--

CREATE TABLE `personnel_lunch_add_more_setting` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `time` time DEFAULT NULL,
  `date_created` date DEFAULT NULL,
  `date_updated` date DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_item_groups`
--

CREATE TABLE `personnel_lunch_item_groups` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_items`
--

CREATE TABLE `personnel_lunch_items` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19443 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_levels`
--

CREATE TABLE `personnel_lunch_levels` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `sign` varchar(10) DEFAULT NULL,
  `max_day` int(11) DEFAULT NULL,
  `has_salary` enum('0','1') DEFAULT '0',
  `has_premium` enum('0','1') DEFAULT '1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_menus`
--

CREATE TABLE `personnel_lunch_menus` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `items` varchar(300) DEFAULT NULL,
  `for_date` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5337 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_order_menus`
--

CREATE TABLE `personnel_lunch_order_menus` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `menu_ids` varchar(200) DEFAULT NULL,
  `position_ids` varchar(200) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `is_active` enum('0','1') DEFAULT '1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1154 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunch_rows`
--

CREATE TABLE `personnel_lunch_rows` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `sign` varchar(10) DEFAULT NULL,
  `max_day` int(11) DEFAULT NULL,
  `has_salary` enum('0','1') DEFAULT '0',
  `has_premium` enum('0','1') DEFAULT '1',
  `position_id` bigint(20) DEFAULT NULL,
  `is_active` enum('0','1') DEFAULT '1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_lunchs`
--

CREATE TABLE `personnel_lunchs` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT '1',
  `personnel_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) NOT NULL,
  `row_id` int(11) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` date DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` date DEFAULT NULL,
  `menu_id` bigint(20) DEFAULT NULL,
  `type` enum('NORMAL','PLUS') DEFAULT 'NORMAL',
  `date_add_more` datetime DEFAULT NULL,
  `add_more_history` int(11) DEFAULT NULL,
  `is_add_more` enum('0','1') DEFAULT '0',
  `users_addmore` text,
  `order_date` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `personnel_id` (`personnel_id`,`date_created`)
) ENGINE=InnoDB AUTO_INCREMENT=118327 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_marriage_status`
--

CREATE TABLE `personnel_marriage_status` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_math_symbols`
--

CREATE TABLE `personnel_math_symbols` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_meeting_cc_users`
--

CREATE TABLE `personnel_meeting_cc_users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Post_Id_Index` (`post_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8387 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_meeting_types`
--

CREATE TABLE `personnel_meeting_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `sign` varchar(10) DEFAULT NULL,
  `has_salary` enum('0','1') DEFAULT '0',
  `max_time` tinyint(4) NOT NULL DEFAULT '8',
  `max_day` tinyint(4) NOT NULL DEFAULT '7',
  `status_desc` enum('0','1') DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_meetings`
--

CREATE TABLE `personnel_meetings` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `reason` varchar(5) DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `time_start` time DEFAULT NULL,
  `time_end` time DEFAULT NULL,
  `minutes` float DEFAULT NULL,
  `approver_id` bigint(20) DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `desc` text,
  `department_head_id_old` bigint(20) DEFAULT NULL,
  `department_head_id_new` bigint(20) DEFAULT NULL,
  `status_decide` bigint(20) DEFAULT NULL,
  `status_approved_new` bigint(20) DEFAULT NULL,
  `status_approved_old` bigint(20) DEFAULT NULL,
  `date_approved` datetime DEFAULT NULL,
  `comment_approve` varchar(300) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `type` varchar(100) NOT NULL DEFAULT 'Meeting',
  `action_history` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17648 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_multi_departments`
--

CREATE TABLE `personnel_multi_departments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) NOT NULL,
  `department_id` bigint(20) NOT NULL,
  `position_title_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Personnel_Id_Index` (`personnel_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=494 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_nations`
--

CREATE TABLE `personnel_nations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_nomination_comments`
--

CREATE TABLE `personnel_nomination_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `post_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `content` text,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_nominations`
--

CREATE TABLE `personnel_nominations` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `type_part` enum('ORTHER','SUPPORT','BUSINESS') DEFAULT NULL,
  `sale_money` decimal(10,0) DEFAULT NULL,
  `number_work` bigint(20) DEFAULT NULL,
  `is_sign` tinyint(4) DEFAULT '0',
  `desc_sign` varchar(500) DEFAULT NULL,
  `is_arise` tinyint(4) DEFAULT '0',
  `desc_arise` varchar(500) DEFAULT NULL,
  `personnel_number` bigint(20) DEFAULT NULL,
  `user_id_new` text,
  `review` varchar(500) DEFAULT NULL,
  `user_id_discussion` text,
  `user_id_approve` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `history` text,
  `position_title_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_overtime_application_details`
--

CREATE TABLE `personnel_overtime_application_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `hours` float DEFAULT NULL,
  `time_add` float DEFAULT NULL COMMENT 'Giờ',
  `date` date DEFAULT NULL,
  `application_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_overtime_application_percent`
--

CREATE TABLE `personnel_overtime_application_percent` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `time_start` time DEFAULT NULL,
  `time_end` time DEFAULT NULL,
  `hour` float DEFAULT NULL,
  `percent` float DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_overtime_application_persons`
--

CREATE TABLE `personnel_overtime_application_persons` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `personnel_id` varchar(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_overtime_applications`
--

CREATE TABLE `personnel_overtime_applications` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `approver_id` bigint(20) DEFAULT NULL,
  `personnel_id` varchar(20) DEFAULT NULL,
  `status` enum('1','2','0') DEFAULT '0',
  `desc` text,
  `approved_by_id` bigint(20) DEFAULT NULL,
  `date_approved` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `action_history` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_point_details`
--

CREATE TABLE `personnel_point_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_point_id` bigint(20) NOT NULL,
  `personnel_id` bigint(20) NOT NULL,
  `status` enum('0','1') NOT NULL DEFAULT '0',
  `point_type_id` int(11) NOT NULL,
  `reason` varchar(500) NOT NULL,
  `point` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_point_types`
--

CREATE TABLE `personnel_point_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_points`
--

CREATE TABLE `personnel_points` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `approver_id` bigint(20) NOT NULL,
  `date_approved` datetime DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_premium_codes`
--

CREATE TABLE `personnel_premium_codes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_premium_histories`
--

CREATE TABLE `personnel_premium_histories` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_premium_modes`
--

CREATE TABLE `personnel_premium_modes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_premium_places`
--

CREATE TABLE `personnel_premium_places` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_private_attendance`
--

CREATE TABLE `personnel_private_attendance` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `confirm_id` bigint(20) DEFAULT NULL,
  `approver_id` bigint(20) DEFAULT NULL,
  `status` enum('0','1','2','3') DEFAULT '0' COMMENT '0: chờ duyệt\n1: đã confirm\n2: đã duyệt\n3: không duyệt, xác nhận',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `desc` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3166 DEFAULT CHARSET=utf8 COMMENT='Lưu đơn sửa công';

--
-- Table structure for table `personnel_private_attendance_details`
--

CREATE TABLE `personnel_private_attendance_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `personnel_code` varchar(45) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `time_start` time DEFAULT NULL,
  `time_end` time DEFAULT NULL,
  `time_start_origin` time DEFAULT NULL,
  `time_end_origin` time DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6135 DEFAULT CHARSET=utf8 COMMENT='lưu chi tiết ngày sửa công trong đơn';

--
-- Table structure for table `personnel_private_attendance_manager_actions`
--

CREATE TABLE `personnel_private_attendance_manager_actions` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `type` enum('CONFIRM','APPROVE') DEFAULT NULL,
  `user_created` bigint(20) DEFAULT NULL,
  `status` enum('1','2') DEFAULT NULL COMMENT '1: xác nhận\n2: duyệt\n3: không xác nhận, duyệt',
  `date_created` datetime DEFAULT NULL,
  `desc` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7047 DEFAULT CHARSET=utf8 COMMENT='lưu hành động xác nhận, duyệt của quản lý tt và quản lý trên 1 cấp';

--
-- Table structure for table `personnel_private_histories`
--

CREATE TABLE `personnel_private_histories` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `module` varchar(100) DEFAULT NULL COMMENT 'WORK,CALENDAR,...',
  `object` varchar(30) DEFAULT NULL COMMENT 'Project,...',
  `object_id` bigint(20) DEFAULT NULL,
  `object1` varchar(20) DEFAULT NULL COMMENT 'Task,...',
  `object1_id` bigint(20) DEFAULT NULL,
  `object2` varchar(20) DEFAULT NULL COMMENT 'Sub_task,...',
  `object2_id` bigint(20) DEFAULT NULL,
  `action` text,
  `msg` longtext,
  `date` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `note` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=709 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_private_settings`
--

CREATE TABLE `personnel_private_settings` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `type` enum('leave','meeting','transfer','config','leaveapplication','charge','see','nomination') NOT NULL DEFAULT 'leave',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_private_templates`
--

CREATE TABLE `personnel_private_templates` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(200) DEFAULT NULL,
  `type` varchar(200) DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `module` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_profile_labels`
--

CREATE TABLE `personnel_profile_labels` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) NOT NULL,
  `label_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_reads`
--

CREATE TABLE `personnel_reads` (
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `controller` varchar(20) NOT NULL,
  `first_date` datetime DEFAULT NULL,
  `last_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_approver`
--

CREATE TABLE `personnel_recruiment_approver` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `charges` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `approves` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `adds` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_comment_files`
--

CREATE TABLE `personnel_recruiment_comment_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) NOT NULL,
  `name` varchar(500) DEFAULT NULL,
  `filename` varchar(500) DEFAULT NULL,
  `type` varchar(300) DEFAULT NULL,
  `size` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_comment_users`
--

CREATE TABLE `personnel_recruiment_comment_users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `recruiment_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `is_guest` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_comments`
--

CREATE TABLE `personnel_recruiment_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `content` text,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_experiences`
--

CREATE TABLE `personnel_recruiment_experiences` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_files`
--

CREATE TABLE `personnel_recruiment_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) NOT NULL,
  `name` varchar(500) DEFAULT NULL,
  `filename` varchar(500) DEFAULT NULL,
  `type` varchar(300) DEFAULT NULL,
  `size` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_reads`
--

CREATE TABLE `personnel_recruiment_reads` (
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `mode` varchar(20) NOT NULL,
  `first_date` datetime DEFAULT NULL,
  `last_date` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`,`post_id`,`mode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_salarys`
--

CREATE TABLE `personnel_recruiment_salarys` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_settings`
--

CREATE TABLE `personnel_recruiment_settings` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `action_id` int(11) DEFAULT NULL,
  `type_action` enum('APPROVER_01','APPROVER_02','CHARGE','ADD','ADD_RECRUIMENT') NOT NULL DEFAULT 'APPROVER_01',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_types`
--

CREATE TABLE `personnel_recruiment_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_users`
--

CREATE TABLE `personnel_recruiment_users` (
  `post_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `is_guest` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`post_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiment_workplaces`
--

CREATE TABLE `personnel_recruiment_workplaces` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_recruiments`
--

CREATE TABLE `personnel_recruiments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `title` varchar(300) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `position_id` int(11) NOT NULL,
  `type_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `salary` varchar(100) DEFAULT NULL,
  `experience` tinyint(4) DEFAULT NULL,
  `degree_id` int(11) DEFAULT NULL,
  `place` int(100) DEFAULT NULL,
  `desc` varchar(600) DEFAULT NULL,
  `reasons` text,
  `department_head_id_old` bigint(20) DEFAULT NULL,
  `department_head_id_new` bigint(20) DEFAULT NULL,
  `status_decide` bigint(20) DEFAULT NULL,
  `status_approved_new` bigint(20) DEFAULT NULL,
  `status_approved_old` bigint(20) DEFAULT NULL,
  `desc_info` varchar(600) DEFAULT NULL,
  `why_recruitment` varchar(600) DEFAULT NULL,
  `rules` varchar(600) DEFAULT NULL,
  `contract_req` varchar(600) DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  `contact_person` varchar(150) NOT NULL,
  `contact_person_id` bigint(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `approver_id` bigint(20) DEFAULT NULL,
  `date_approved` datetime DEFAULT NULL,
  `status` enum('0','1','2') NOT NULL DEFAULT '0',
  `comment` text,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `type` varchar(100) NOT NULL DEFAULT 'Recruiment',
  `filter_ids` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_religions`
--

CREATE TABLE `personnel_religions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_request_mail`
--

CREATE TABLE `personnel_request_mail` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `is_add` enum('0','1') DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=779 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salaries`
--

CREATE TABLE `personnel_salaries` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) DEFAULT NULL,
  `personnel_id` bigint(11) NOT NULL,
  `month` tinyint(4) NOT NULL,
  `year` year(4) NOT NULL,
  `salary_money` decimal(20,5) DEFAULT NULL,
  `workday` float DEFAULT NULL,
  `workday_month` tinyint(4) DEFAULT NULL,
  `salary_real` decimal(20,5) DEFAULT NULL,
  `salary_premium` decimal(20,5) DEFAULT NULL,
  `trade_union_fees` decimal(20,5) DEFAULT NULL,
  `salary_allowances` decimal(20,5) DEFAULT NULL,
  `salary_all` decimal(20,5) DEFAULT NULL,
  `insurance_social_percent` float DEFAULT NULL,
  `insurance_social_money` decimal(20,5) DEFAULT NULL,
  `insurance_social_percent_company` float DEFAULT NULL,
  `insurance_social_money_company` decimal(20,5) DEFAULT NULL,
  `insurance_social_percent_person` float DEFAULT NULL,
  `insurance_social_money_person` decimal(20,5) DEFAULT NULL,
  `insurance_health_percent` float DEFAULT NULL,
  `insurance_health_money` decimal(20,5) DEFAULT NULL,
  `insurance_health_percent_company` float DEFAULT NULL,
  `insurance_health_money_company` decimal(20,5) DEFAULT NULL,
  `insurance_health_percent_person` float DEFAULT NULL,
  `insurance_health_money_person` decimal(20,5) DEFAULT NULL,
  `insurance_unemployment_percent` float DEFAULT NULL,
  `insurance_unemployment_money` decimal(20,5) DEFAULT NULL,
  `insurance_unemployment_percent_company` float DEFAULT NULL,
  `insurance_unemployment_money_company` decimal(10,0) DEFAULT NULL,
  `insurance_unemployment_percent_person` float DEFAULT NULL,
  `insurance_unemployment_money_person` decimal(10,0) DEFAULT NULL,
  `advances_employees` decimal(20,5) DEFAULT NULL,
  `advances_employees_sub_wage` decimal(20,5) DEFAULT NULL,
  `wage_reduct_total` decimal(20,5) DEFAULT NULL,
  `money_person_reduction` decimal(20,5) DEFAULT NULL,
  `money_depend_reduction` decimal(20,5) DEFAULT NULL,
  `money_number_reduction` decimal(20,5) DEFAULT NULL,
  `money_reduction_all` decimal(20,5) DEFAULT NULL,
  `income_tax` decimal(20,5) DEFAULT NULL,
  `income_tax_person` decimal(20,5) DEFAULT NULL,
  `money_all` decimal(20,5) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `f52` int(30) DEFAULT NULL,
  `f53` decimal(20,5) DEFAULT NULL,
  `f54` decimal(20,5) DEFAULT NULL,
  `f57` decimal(20,5) DEFAULT NULL,
  `f58` decimal(20,5) DEFAULT NULL,
  `f59` int(30) DEFAULT NULL,
  `f61` int(30) DEFAULT NULL,
  `insurance_f2_percent` float DEFAULT NULL,
  `insurance_f2_money` decimal(20,3) DEFAULT NULL,
  `insurance_f2_percent_person` float DEFAULT '10',
  `insurance_f2_money_person` decimal(20,3) DEFAULT NULL,
  `insurance_f2_percent_company` float DEFAULT '10',
  `insurance_f2_money_company` decimal(20,3) DEFAULT NULL,
  `insurance_f3_percent` float DEFAULT NULL,
  `insurance_f3_money` decimal(20,3) DEFAULT NULL,
  `insurance_f3_percent_person` float DEFAULT '10',
  `insurance_f3_money_person` decimal(20,3) DEFAULT NULL,
  `insurance_f3_percent_company` float DEFAULT '10',
  `insurance_f3_money_company` decimal(20,3) DEFAULT NULL,
  `insurance_f4_percent` float DEFAULT NULL,
  `insurance_f4_money` decimal(20,3) DEFAULT NULL,
  `insurance_f4_percent_person` float DEFAULT '10',
  `insurance_f4_money_person` decimal(20,3) DEFAULT NULL,
  `insurance_f4_percent_company` float DEFAULT '10',
  `insurance_f4_money_company` decimal(20,3) DEFAULT NULL,
  `insurance_f7_percent` float DEFAULT NULL,
  `insurance_f7_money` decimal(20,3) DEFAULT NULL,
  `insurance_f7_percent_person` float DEFAULT '0',
  `insurance_f7_money_person` decimal(20,3) DEFAULT NULL,
  `insurance_f7_percent_company` float DEFAULT '2',
  `insurance_f7_money_company` decimal(20,3) DEFAULT NULL,
  `f1` decimal(20,5) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `personnel_id` (`personnel_id`,`month`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_bills`
--

CREATE TABLE `personnel_salary_bills` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `list_id` bigint(20) DEFAULT NULL,
  `income` decimal(8,0) NOT NULL,
  `advance` decimal(8,0) NOT NULL,
  `status` tinyint(1) DEFAULT '0',
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_bonus`
--

CREATE TABLE `personnel_salary_bonus` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `list_id` bigint(20) DEFAULT NULL,
  `bill_id` bigint(20) DEFAULT NULL,
  `bonus_value` decimal(8,0) NOT NULL,
  `bonus_desc` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_data_types`
--

CREATE TABLE `personnel_salary_data_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `money_private` decimal(20,5) DEFAULT NULL COMMENT 'Mức giảm trừ chi phí cá nhân người nộp thuế',
  `money_person` decimal(20,5) DEFAULT NULL COMMENT 'Mức giảm trừ gia cảnh đối với  1  người phụ thuộc',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_deductions`
--

CREATE TABLE `personnel_salary_deductions` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `list_id` bigint(20) DEFAULT NULL,
  `bill_id` bigint(20) DEFAULT NULL,
  `deduction_value` decimal(8,0) NOT NULL,
  `deduction_desc` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_fields`
--

CREATE TABLE `personnel_salary_fields` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `type_salary` enum('NONE','ASC','DESC') DEFAULT 'NONE',
  `type_tax` enum('NO','YES') DEFAULT 'NO',
  `ord` int(11) DEFAULT '0',
  `type` varchar(10) DEFAULT NULL,
  `title` varchar(250) NOT NULL COMMENT 'tên cột hiển thị trên danh sách',
  `parent_id` int(11) DEFAULT NULL,
  `is_time` enum('0','1') DEFAULT '0',
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `display` tinyint(4) DEFAULT '0',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_histories`
--

CREATE TABLE `personnel_salary_histories` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `method` int(11) DEFAULT NULL,
  `real` decimal(10,0) DEFAULT NULL,
  `recruiment` decimal(10,0) DEFAULT NULL,
  `allowances` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_item_types`
--

CREATE TABLE `personnel_salary_item_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `percent_company` float DEFAULT NULL,
  `percent_private` float DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_lists`
--

CREATE TABLE `personnel_salary_lists` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `month` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_methods`
--

CREATE TABLE `personnel_salary_methods` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_params`
--

CREATE TABLE `personnel_salary_params` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  `countb` int(11) DEFAULT NULL,
  `subsalary` float DEFAULT NULL,
  `stone` int(11) NOT NULL DEFAULT '2' COMMENT 'Khoáº£ng cÃ¡ch nÄƒm tÄƒng lÆ°Æ¡ng',
  `created_by_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_tax_phases`
--

CREATE TABLE `personnel_salary_tax_phases` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `tax_id` bigint(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `money_start` decimal(20,5) DEFAULT NULL,
  `money_end` decimal(20,5) DEFAULT NULL,
  `percent` float DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_tax_types`
--

CREATE TABLE `personnel_salary_tax_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_salary_workdays`
--

CREATE TABLE `personnel_salary_workdays` (
  `personnel_id` bigint(11) NOT NULL,
  `date` date NOT NULL,
  `amount` float DEFAULT NULL,
  PRIMARY KEY (`personnel_id`,`date`),
  KEY `personnel_id` (`personnel_id`,`date`,`amount`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_sales`
--

CREATE TABLE `personnel_sales` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(20,5) DEFAULT '0.00000',
  `percent` float DEFAULT '0',
  `desc` text,
  `ref_id` int(11) DEFAULT NULL COMMENT 'ID tham chiếu',
  `type` varchar(100) DEFAULT NULL COMMENT 'ĐT tham chiếu (Tên bảng)',
  `date` date DEFAULT NULL,
  `status` enum('0','1') DEFAULT '0',
  `type_depart` enum('BUSSINESS','CARE','OTHER') DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` int(11) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `is_new` tinyint(4) NOT NULL DEFAULT '0',
  `is_resign` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_setting_views`
--

CREATE TABLE `personnel_setting_views` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `action_id` int(11) DEFAULT NULL,
  `type_action` enum('PERSONNEL','CONTRACT','INSURANCE','LUNCH','RECRUIMENT','ATTENDANCE') NOT NULL DEFAULT 'PERSONNEL',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_settings`
--

CREATE TABLE `personnel_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `basic_salary` bigint(11) unsigned NOT NULL DEFAULT '0',
  `contract_count` bigint(20) DEFAULT '1',
  `day1` tinyint(4) NOT NULL DEFAULT '3',
  `day2` tinyint(4) NOT NULL DEFAULT '3',
  `day3` tinyint(4) NOT NULL DEFAULT '3',
  `day4` tinyint(4) NOT NULL DEFAULT '3',
  `day5` tinyint(4) NOT NULL DEFAULT '3',
  `day6` tinyint(4) NOT NULL DEFAULT '1',
  `day7` tinyint(4) NOT NULL DEFAULT '0',
  `work_time_start` time NOT NULL DEFAULT '07:00:00',
  `work_time_end_morning` time NOT NULL DEFAULT '12:00:00',
  `work_time_start_afternoon` time NOT NULL DEFAULT '13:00:00',
  `work_time_end` time NOT NULL DEFAULT '17:30:00',
  `part` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_specializations`
--

CREATE TABLE `personnel_specializations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=383 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_temp_date_export`
--

CREATE TABLE `personnel_temp_date_export` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `personnel_id` int(11) DEFAULT NULL,
  `date_trial` date DEFAULT NULL,
  `date_trial_contract` date DEFAULT NULL,
  `date_contract` date DEFAULT NULL,
  `current_contract_type` int(11) DEFAULT NULL,
  `time_work` float DEFAULT NULL,
  `time_business` float DEFAULT NULL,
  `total_leave_day` float DEFAULT NULL,
  `leave_day_used` float DEFAULT NULL,
  `leave_day_no_money` float DEFAULT NULL,
  `leave_day_debt` float DEFAULT NULL,
  `total_day_late` float DEFAULT NULL,
  `total_time_late` float DEFAULT NULL,
  `day_late_details` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=435 DEFAULT CHARSET=utf8 COMMENT='Bảng lưu tạm thông tin nhân viên của 1 năm xuất ra phục vụ cho kế toán thống kê lương thưởng cuối năm';

--
-- Table structure for table `personnel_template_exports`
--

CREATE TABLE `personnel_template_exports` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `type` varchar(300) NOT NULL,
  `size` varchar(100) NOT NULL,
  `name` varchar(200) NOT NULL,
  `filename` varchar(300) NOT NULL,
  `module` varchar(100) NOT NULL,
  `contract_type_ids` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_train_types`
--

CREATE TABLE `personnel_train_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_training_types`
--

CREATE TABLE `personnel_training_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_training_users`
--

CREATE TABLE `personnel_training_users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `training_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `is_guest` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_trainings`
--

CREATE TABLE `personnel_trainings` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_draft` enum('yes','no') NOT NULL DEFAULT 'yes',
  `code` varchar(100) NOT NULL,
  `title` varchar(300) NOT NULL,
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `train_type` bigint(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `numbers` int(11) DEFAULT NULL,
  `desc` text,
  `status` enum('W','A','C','O','P') NOT NULL DEFAULT 'W',
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_transfer_historys`
--

CREATE TABLE `personnel_transfer_historys` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `date_job_from` date DEFAULT NULL,
  `department` varchar(300) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `position_id` bigint(20) DEFAULT NULL,
  `job_id` bigint(20) DEFAULT NULL,
  `date_job_to` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5998 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_transfer_persons`
--

CREATE TABLE `personnel_transfer_persons` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) DEFAULT NULL,
  `department_id_old` bigint(20) DEFAULT NULL,
  `position_id_old` bigint(20) DEFAULT NULL,
  `job_id_old` bigint(20) DEFAULT NULL,
  `department_id_new` bigint(20) DEFAULT NULL,
  `position_id_new` bigint(20) DEFAULT NULL,
  `job_id_new` bigint(20) DEFAULT NULL,
  `transfer_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_transfer_settings`
--

CREATE TABLE `personnel_transfer_settings` (
  `job_default_id` bigint(20) DEFAULT NULL,
  `add_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_transfers`
--

CREATE TABLE `personnel_transfers` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `date_transfer` date DEFAULT NULL,
  `desc` text,
  `date_approved` date DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `action_history` text,
  `department_head_id_old` bigint(20) DEFAULT NULL,
  `status_head_old` enum('2','1','0') DEFAULT NULL COMMENT 'trang thai phu trach cu : 0: chờ duyet, 1 : da duyet, 2 : khong duyet',
  `date_head_old` datetime DEFAULT NULL,
  `comment_head_old` varchar(500) DEFAULT NULL,
  `department_head_id_new` bigint(20) DEFAULT NULL,
  `status_head_new` enum('2','1','0') DEFAULT NULL,
  `date_head_new` datetime DEFAULT NULL,
  `comment_head_new` varchar(500) DEFAULT NULL,
  `user_id_decide` bigint(20) DEFAULT NULL,
  `comment_decide` varchar(500) DEFAULT NULL,
  `date_decide` datetime DEFAULT NULL,
  `status_decide` enum('2','1','0','-1') DEFAULT NULL COMMENT '-1 : chưa tạo, 0 : chờ tạo. 1 : đã tạo, 2: hủy tạo',
  `personnel_ids` text,
  `user_ids` text,
  `user_id_review` bigint(20) DEFAULT NULL,
  `comment_review` varchar(500) DEFAULT NULL,
  `date_review` datetime DEFAULT NULL,
  `status_review` enum('2','1','0','-1') DEFAULT '-1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_union_positions`
--

CREATE TABLE `personnel_union_positions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_wage_fields`
--

CREATE TABLE `personnel_wage_fields` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ord` int(11) DEFAULT NULL,
  `type` varchar(10) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `display` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_wages`
--

CREATE TABLE `personnel_wages` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(11) NOT NULL,
  `month` tinyint(4) NOT NULL,
  `year` year(4) NOT NULL,
  `f0` varchar(300) DEFAULT NULL,
  `f32` int(30) DEFAULT NULL,
  `f34` int(30) DEFAULT NULL,
  `f37` varchar(300) DEFAULT NULL,
  `f38` int(30) DEFAULT NULL,
  `f39` int(30) DEFAULT NULL,
  `f46` int(30) DEFAULT NULL,
  `f48` int(30) DEFAULT NULL,
  `f55` int(30) DEFAULT NULL,
  `f56` int(30) DEFAULT NULL,
  `f57` int(30) DEFAULT NULL,
  `f58` int(30) DEFAULT NULL,
  `f62` int(30) DEFAULT NULL,
  `f63` int(30) DEFAULT NULL,
  `f64` int(30) DEFAULT NULL,
  `f66` int(30) DEFAULT NULL,
  `f67` int(30) DEFAULT NULL,
  `f68` int(30) DEFAULT NULL,
  `f70` int(30) DEFAULT NULL,
  `f69` int(30) DEFAULT NULL,
  `f71` int(30) DEFAULT NULL,
  `f73` int(30) DEFAULT NULL,
  `f74` int(30) DEFAULT NULL,
  `f75` int(30) DEFAULT NULL,
  `f76` int(30) DEFAULT NULL,
  `f77` int(30) DEFAULT NULL,
  `f78` int(30) DEFAULT NULL,
  `f79` int(30) DEFAULT NULL,
  `f80` int(30) DEFAULT NULL,
  `f81` int(30) DEFAULT NULL,
  `f82` int(30) DEFAULT NULL,
  `f83` int(30) DEFAULT NULL,
  `f85` int(30) DEFAULT NULL,
  `f86` int(30) DEFAULT NULL,
  `f87` int(30) DEFAULT NULL,
  `f89` int(30) DEFAULT NULL,
  `f90` int(30) DEFAULT NULL,
  `f91` int(30) DEFAULT NULL,
  `f92` int(30) DEFAULT NULL,
  `f93` int(30) DEFAULT NULL,
  `f95` int(30) DEFAULT NULL,
  `f96` int(30) DEFAULT NULL,
  `f97` int(30) DEFAULT NULL,
  `f98` int(30) DEFAULT NULL,
  `f99` int(30) DEFAULT NULL,
  `f100` int(30) DEFAULT NULL,
  `f101` int(30) DEFAULT NULL,
  `f102` int(30) DEFAULT NULL,
  `f103` int(30) DEFAULT NULL,
  `f104` int(30) DEFAULT NULL,
  `f105` int(30) DEFAULT NULL,
  `f106` int(30) DEFAULT NULL,
  `f107` int(30) DEFAULT NULL,
  `f108` int(30) DEFAULT NULL,
  `f109` int(30) DEFAULT NULL,
  `f110` varchar(300) DEFAULT NULL,
  `f111` varchar(300) DEFAULT NULL,
  `f112` int(30) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `personnel_id` (`personnel_id`,`month`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_work_places`
--

CREATE TABLE `personnel_work_places` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `code` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_work_types`
--

CREATE TABLE `personnel_work_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnel_worktimes`
--

CREATE TABLE `personnel_worktimes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `work_time_start` time DEFAULT NULL,
  `work_time_end_morning` time DEFAULT NULL,
  `work_time_start_afternoon` time DEFAULT NULL,
  `work_time_end` time DEFAULT NULL,
  `days` varchar(20) DEFAULT NULL,
  `departments` varchar(200) DEFAULT NULL,
  `apply_from` date DEFAULT NULL,
  `apply_to` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Table structure for table `personnels`
--

CREATE TABLE `personnels` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `place_of_birth` int(11) DEFAULT NULL,
  `gender` enum('0','1','2') NOT NULL DEFAULT '2',
  `marital_status` int(11) DEFAULT NULL,
  `private_code` varchar(20) DEFAULT NULL,
  `private_code_date` date DEFAULT NULL,
  `private_code_place` int(100) DEFAULT NULL,
  `photo` text,
  `phone` varchar(50) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(300) DEFAULT NULL,
  `yahoo` varchar(30) DEFAULT NULL,
  `skype` varchar(30) DEFAULT NULL,
  `facebook` varchar(200) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `origin_id` int(11) DEFAULT NULL,
  `people` int(11) DEFAULT NULL,
  `religious` int(11) DEFAULT NULL,
  `nationality` int(11) DEFAULT NULL,
  `home_address` varchar(500) DEFAULT NULL,
  `home_address_state` int(11) DEFAULT NULL,
  `current_address` varchar(500) DEFAULT NULL,
  `current_address_state` int(11) DEFAULT NULL,
  `fast_contact_name` varchar(50) DEFAULT NULL,
  `fast_contact_relation` int(11) DEFAULT NULL,
  `fast_contact_phone` varchar(20) DEFAULT NULL,
  `fast_contact_address` varchar(200) DEFAULT NULL,
  `is_party` tinyint(1) DEFAULT '0',
  `party_date_join` date DEFAULT NULL,
  `party_place` varchar(50) DEFAULT NULL,
  `party_position` int(11) DEFAULT NULL,
  `is_union` tinyint(1) DEFAULT NULL,
  `union_date_join` date DEFAULT NULL,
  `union_place` varchar(300) DEFAULT NULL,
  `union_position` int(11) DEFAULT NULL,
  `is_army` tinyint(1) DEFAULT '0',
  `army_date_join` date DEFAULT NULL,
  `army_unit` varchar(50) DEFAULT NULL,
  `army_position` int(11) DEFAULT NULL,
  `army_level` varchar(50) DEFAULT NULL,
  `army_date_out` varchar(50) DEFAULT NULL,
  `army_reason` varchar(300) DEFAULT NULL,
  `heath_blood` varchar(4) DEFAULT NULL,
  `heath_height` varchar(5) DEFAULT NULL,
  `heath_weight` varchar(5) DEFAULT NULL,
  `heath_status` varchar(50) DEFAULT NULL,
  `heath_disability` varchar(50) DEFAULT NULL,
  `heath_family_army` tinyint(1) DEFAULT '0',
  `heath_wounded` tinyint(1) DEFAULT '0',
  `heath_range` varchar(5) DEFAULT NULL,
  `heath_percent` int(11) DEFAULT NULL,
  `self_target` varchar(600) DEFAULT NULL,
  `self_hobby` varchar(600) DEFAULT NULL,
  `self_strong` varchar(600) DEFAULT NULL,
  `self_weak` varchar(600) DEFAULT NULL,
  `self_experience` varchar(600) DEFAULT NULL,
  `premium_status` enum('0','1') NOT NULL DEFAULT '0',
  `premium_pay_status` enum('0','1') DEFAULT '0',
  `premium_key_status` enum('0','1') DEFAULT '0',
  `premium_mode` int(11) DEFAULT NULL,
  `premium_number` varchar(50) DEFAULT NULL,
  `premium_start_date` date DEFAULT NULL,
  `premium_percent` varchar(5) DEFAULT NULL,
  `premium_code` varchar(20) DEFAULT NULL,
  `premium_card` varchar(20) DEFAULT NULL,
  `premium_experie` date DEFAULT NULL,
  `premium_place` varchar(150) DEFAULT NULL,
  `train_level` int(5) DEFAULT NULL,
  `train_place` int(150) DEFAULT NULL,
  `train_discripline` int(150) DEFAULT NULL,
  `train_degree` varchar(150) DEFAULT NULL,
  `train_year` varchar(150) DEFAULT NULL,
  `train_faculty` varchar(150) DEFAULT NULL,
  `train_degrees` text,
  `position_id` int(11) DEFAULT NULL,
  `job_title` int(11) DEFAULT NULL,
  `job_date_try` date DEFAULT NULL,
  `job_contract` int(11) DEFAULT NULL,
  `job_time_try` tinyint(2) DEFAULT NULL,
  `work_place` int(20) DEFAULT NULL,
  `job_manager` bigint(20) DEFAULT NULL,
  `job_bank_account` varchar(150) DEFAULT NULL,
  `job_tax` varchar(200) DEFAULT NULL,
  `job_status` varchar(80) DEFAULT NULL,
  `job_date_out` date DEFAULT NULL,
  `job_out_reason` int(11) DEFAULT NULL,
  `job_out_approver` bigint(20) DEFAULT NULL,
  `job_date_join` date DEFAULT NULL,
  `job_reldate_join` date DEFAULT NULL,
  `date_promote` date DEFAULT NULL,
  `salary_method` int(11) DEFAULT NULL,
  `salary_real` decimal(10,0) DEFAULT NULL,
  `salary_premium_id` int(11) DEFAULT NULL,
  `salary_premium` decimal(10,0) DEFAULT NULL,
  `salary_allowances` decimal(10,0) DEFAULT NULL,
  `salary_premium_percent` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `status_recruiment` enum('0','1') NOT NULL DEFAULT '0',
  `status_contract` enum('0','1') NOT NULL DEFAULT '0',
  `status_card` enum('0','1') NOT NULL DEFAULT '0',
  `status_account` enum('0','1') NOT NULL DEFAULT '0',
  `status_email` enum('0','1') NOT NULL DEFAULT '0',
  `status_document` enum('0','1') NOT NULL DEFAULT '0',
  `status_profile` enum('0','1') DEFAULT '0',
  `status_heath_certificate` enum('0','1') DEFAULT '0',
  `status_identity_card` enum('0','1') DEFAULT '0',
  `status_photo` enum('0','1') DEFAULT '0',
  `log_department` int(11) DEFAULT NULL,
  `log_department_date` date DEFAULT NULL,
  `log_job_title` int(11) DEFAULT NULL,
  `log_job_title_date` date DEFAULT NULL,
  `log_salary_real` decimal(20,3) DEFAULT NULL,
  `log_salary_real_date` date DEFAULT NULL,
  `premium_inventoris` enum('0','1') NOT NULL DEFAULT '0',
  `full_attendance` tinyint(4) NOT NULL DEFAULT '0',
  `no_fingerprint` tinyint(4) NOT NULL DEFAULT '0',
  `insurance_f2_percent` float DEFAULT '30',
  `insurance_f2_person` float DEFAULT '10',
  `insurance_f2_company` float DEFAULT '10',
  `insurance_f3_percent` float DEFAULT '20',
  `insurance_f3_person` float DEFAULT '10',
  `insurance_f3_company` float DEFAULT '10',
  `insurance_f4_percent` float DEFAULT '15',
  `insurance_f4_person` float DEFAULT '10',
  `insurance_f4_company` float DEFAULT '10',
  `insurance_f7_percent` float DEFAULT '2',
  `insurance_f7_person` float DEFAULT '0',
  `insurance_f7_company` float DEFAULT '2',
  `status_birth_certificate` enum('0','1') DEFAULT '0',
  `status_household` enum('0','1') DEFAULT '0',
  `status_submit_insurance` enum('0','1') DEFAULT NULL,
  `premium_experie_date` date DEFAULT NULL,
  `department_ids` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_code` (`code`),
  KEY `Department_Ids_Index` (`department_ids`) USING BTREE,
  KEY `JobStatusIndex` (`job_status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2630 DEFAULT CHARSET=utf8;

--
-- Table structure for table `position_titles`
--

CREATE TABLE `position_titles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;

--
-- Table structure for table `positions`
--

CREATE TABLE `positions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_approver_histories`
--

CREATE TABLE `problem_approver_histories` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `problem_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `ord` tinyint(4) DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_approver_ideas`
--

CREATE TABLE `problem_approver_ideas` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_approvers`
--

CREATE TABLE `problem_approvers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `type` enum('approve','ceo') NOT NULL DEFAULT 'approve',
  `date_created` datetime DEFAULT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_apprrover_users`
--

CREATE TABLE `problem_apprrover_users` (
  `ID` bigint(22) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(22) DEFAULT NULL,
  `user_id` bigint(22) DEFAULT NULL,
  `mark_id` int(11) DEFAULT NULL,
  `status` enum('1','2') DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_comments`
--

CREATE TABLE `problem_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `content` text,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_departments`
--

CREATE TABLE `problem_departments` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_files`
--

CREATE TABLE `problem_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(200) DEFAULT NULL,
  `type` varchar(200) DEFAULT NULL,
  `post_id` bigint(20) NOT NULL,
  `module` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_histories`
--

CREATE TABLE `problem_histories` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `problem_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `ord` tinyint(4) DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_marks`
--

CREATE TABLE `problem_marks` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` text,
  `mark` varchar(200) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_origins`
--

CREATE TABLE `problem_origins` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `origin_id` int(11) DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_persons`
--

CREATE TABLE `problem_persons` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_product_storages`
--

CREATE TABLE `problem_product_storages` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_products`
--

CREATE TABLE `problem_products` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  `ord` tinyint(4) DEFAULT '0',
  `parent_id` int(11) DEFAULT '0',
  `company_id` bigint(20) DEFAULT NULL,
  `is_new` enum('0','1') DEFAULT '0',
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  `type_service` enum('RESIGN','NONE','NEW') DEFAULT NULL,
  `is_report` tinyint(4) DEFAULT NULL,
  `money` decimal(20,5) DEFAULT NULL,
  `alias` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_step_users`
--

CREATE TABLE `problem_step_users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `step_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_steps`
--

CREATE TABLE `problem_steps` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL,
  `ord` tinyint(4) NOT NULL,
  `number` int(11) DEFAULT NULL,
  `date_started` datetime DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_times`
--

CREATE TABLE `problem_times` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` int(11) NOT NULL COMMENT 'Thời gian(Phút)',
  `date_created` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_type_origins`
--

CREATE TABLE `problem_type_origins` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_type_storages`
--

CREATE TABLE `problem_type_storages` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problem_types`
--

CREATE TABLE `problem_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(40) DEFAULT NULL,
  `ord` tinyint(4) DEFAULT '0',
  `parent_id` int(11) DEFAULT '0',
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

--
-- Table structure for table `problems`
--

CREATE TABLE `problems` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `type_origin` int(11) DEFAULT NULL,
  `priority` tinyint(4) DEFAULT '0',
  `code` varchar(150) DEFAULT NULL,
  `status` enum('0','1','2','3') DEFAULT '0',
  `desc` text,
  `is_type` enum('problem','idea') DEFAULT 'problem',
  `is_idea` enum('0','1','2') DEFAULT '0',
  `is_sms` enum('0','1') DEFAULT '0',
  `time_planned` time DEFAULT NULL,
  `date_planned` date DEFAULT NULL,
  `deadline_planned` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  `step` tinyint(4) DEFAULT '0',
  `current_step` tinyint(4) DEFAULT '0',
  `datetime_checked` datetime DEFAULT NULL,
  `date_ceo_started` datetime DEFAULT NULL,
  `deadline` datetime DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `date_updated` datetime DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `task_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(11) DEFAULT NULL,
  `users` varchar(500) DEFAULT NULL,
  `approver` varchar(500) DEFAULT NULL,
  `charge_id` int(11) DEFAULT NULL,
  `charge_status` enum('-1','0','1','2','3','4') NOT NULL DEFAULT '0' COMMENT '-1: không duyệt , 0 : chờ duyệt , 1: đang duyệt , 2: chờ chốt , 3: không chốt , 4 : đã chốt',
  `charge_desc` text,
  `charge_date` datetime DEFAULT NULL,
  `approver_idea_id` int(11) DEFAULT NULL,
  `approver_idea_status` enum('0','1','2') DEFAULT '0',
  `approver_idea_date` datetime DEFAULT NULL,
  `approver_idea_desc` text,
  `rollback_id` bigint(20) DEFAULT NULL,
  `rollback_status` enum('0','1') DEFAULT '0',
  `rollback_date` datetime DEFAULT NULL,
  `rollback_desc` text,
  `rollback_ord` tinyint(4) DEFAULT NULL COMMENT '0:person is ceo',
  `mark_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

--
-- Table structure for table `projects`
--

CREATE TABLE `projects` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `is_draft` enum('yes','no') NOT NULL,
  `status` enum('active','inactive','complete','cancel') NOT NULL DEFAULT 'active',
  `code` varchar(300) DEFAULT NULL,
  `icon` varchar(2) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `parent_id` bigint(20) NOT NULL DEFAULT '0',
  `desc` text,
  `rate` int(11) NOT NULL,
  `dateof` decimal(10,1) unsigned DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `date_end_forecast` date DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `created_by_id` (`created_by_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `projects_files`
--

CREATE TABLE `projects_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(300) DEFAULT NULL,
  `type` varchar(300) DEFAULT NULL,
  `project_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `projects_reads`
--

CREATE TABLE `projects_reads` (
  `project_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `date_read` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `projects_types`
--

CREATE TABLE `projects_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

--
-- Table structure for table `projects_users`
--

CREATE TABLE `projects_users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `is_guest` enum('yes','no') NOT NULL DEFAULT 'no',
  `is_supervisor` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rs_sysusers`
--

CREATE TABLE `rs_sysusers` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(50) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `UserType` int(11) NOT NULL,
  `Address` varchar(250) DEFAULT NULL,
  `AmountCharge` decimal(15,2) NOT NULL,
  `Amount` decimal(15,2) NOT NULL,
  `PhoneNumber` varchar(50) DEFAULT NULL,
  `Website` varchar(255) DEFAULT NULL,
  `FullName` varchar(150) DEFAULT NULL,
  `ResellerLevelId` int(11) DEFAULT NULL,
  `SaleId` int(11) DEFAULT NULL,
  `SaleGroupId` int(11) DEFAULT NULL,
  `CreatedOn` datetime DEFAULT NULL,
  `ModifiedOn` datetime DEFAULT NULL,
  `LastLogin` datetime DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `Password` varchar(100) NOT NULL,
  `PasswordSalt` varchar(100) NOT NULL,
  `CheckSum` varchar(50) DEFAULT NULL,
  `AccountNumber` varchar(50) DEFAULT NULL,
  `AccountHolder` varchar(255) DEFAULT NULL,
  `Bank` varchar(255) DEFAULT NULL,
  `Branch` varchar(255) DEFAULT NULL,
  `BaoKimEmail` varchar(255) DEFAULT NULL,
  `NganLuongEmail` varchar(255) DEFAULT NULL,
  `ReceivedContact` bit(1) DEFAULT b'0',
  `WebMaster` bit(1) DEFAULT b'0',
  `Banner` bit(1) DEFAULT b'0',
  `Yahoo` varchar(50) DEFAULT NULL,
  `Skype` varchar(50) DEFAULT NULL,
  `Gtalk` varchar(50) DEFAULT NULL,
  `Description` varchar(500) DEFAULT NULL,
  `LeaderDepartment` varchar(20) DEFAULT NULL,
  `BrandName` varchar(100) DEFAULT NULL,
  `Region` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `UserName_Email_Index` (`Username`,`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `salaries`
--

CREATE TABLE `salaries` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `personnel_id` bigint(20) NOT NULL,
  `year` int(4) NOT NULL,
  `m1` decimal(10,0) DEFAULT NULL,
  `m2` decimal(10,0) DEFAULT NULL,
  `m3` decimal(10,0) DEFAULT NULL,
  `m4` decimal(10,0) DEFAULT NULL,
  `m5` decimal(10,0) DEFAULT NULL,
  `m6` decimal(10,0) DEFAULT NULL,
  `m7` decimal(10,0) DEFAULT NULL,
  `m8` decimal(10,0) DEFAULT NULL,
  `m9` decimal(10,0) DEFAULT NULL,
  `m10` decimal(10,0) DEFAULT NULL,
  `m11` decimal(10,0) DEFAULT NULL,
  `m12` decimal(10,0) DEFAULT NULL,
  `d1` varchar(300) DEFAULT NULL,
  `d2` varchar(300) DEFAULT NULL,
  `d3` varchar(300) DEFAULT NULL,
  `d4` varchar(300) DEFAULT NULL,
  `d5` varchar(300) DEFAULT NULL,
  `d6` varchar(300) DEFAULT NULL,
  `d7` varchar(300) DEFAULT NULL,
  `d8` varchar(300) DEFAULT NULL,
  `d9` varchar(300) DEFAULT NULL,
  `d10` varchar(300) DEFAULT NULL,
  `d11` varchar(300) DEFAULT NULL,
  `d12` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `salary_email_groups`
--

CREATE TABLE `salary_email_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `salary_month` varchar(10) NOT NULL,
  `date_created` datetime NOT NULL,
  `created_by_id` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `month` (`salary_month`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

--
-- Table structure for table `salary_emails`
--

CREATE TABLE `salary_emails` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(45) NOT NULL,
  `data` varchar(1000) NOT NULL,
  `status` int(11) NOT NULL COMMENT '0: chua gui email\n1: da gui email\n2: gui that bai',
  `group_id` int(11) NOT NULL,
  `error` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique email` (`group_id`,`email`),
  KEY `group` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8445 DEFAULT CHARSET=utf8;

--
-- Table structure for table `sapo_mapping_datas`
--

CREATE TABLE `sapo_mapping_datas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sapo_item_id` int(11) NOT NULL,
  `trial_service_id` int(11) DEFAULT NULL,
  `lead_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sapo_item_id` (`sapo_item_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7642 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='mapping data';

--
-- Table structure for table `sapo_mapping_errors`
--

CREATE TABLE `sapo_mapping_errors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sapo_item_id` int(11) NOT NULL,
  `error_message` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sapo_item_id` (`sapo_item_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2551 DEFAULT CHARSET=utf8 COMMENT='mapping_errors';

--
-- Table structure for table `setting_emails`
--

CREATE TABLE `setting_emails` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `host` varchar(250) DEFAULT NULL,
  `port` varchar(10) DEFAULT NULL,
  `secure` varchar(5) DEFAULT 'ssl',
  `email` varchar(250) DEFAULT NULL,
  `password` varchar(250) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `is_default` enum('0','1') DEFAULT '0',
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `name` varchar(100) NOT NULL DEFAULT '',
  `value` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `share_cycles`
--

CREATE TABLE `share_cycles` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(45) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `var_int` int(11) DEFAULT NULL,
  `var_char` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8;

--
-- Table structure for table `sms`
--

CREATE TABLE `sms` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `content` text,
  `user_id` bigint(11) NOT NULL,
  `address` varchar(30) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_send` datetime DEFAULT NULL,
  `status` varchar(6) DEFAULT NULL,
  `result_send` varchar(50) DEFAULT NULL,
  `mode` varchar(30) DEFAULT NULL,
  `post_id` bigint(20) DEFAULT NULL,
  `url` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `sms_messages`
--

CREATE TABLE `sms_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(16) NOT NULL,
  `message` varchar(512) NOT NULL,
  `status` varchar(8) NOT NULL DEFAULT 'PENDING',
  `error` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1043 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_albums`
--

CREATE TABLE `social_albums` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `desc` text,
  `date_created` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_near` datetime DEFAULT NULL,
  `wall_type` enum('GROUP','USER') DEFAULT 'USER',
  `wall_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_banner_posts`
--

CREATE TABLE `social_banner_posts` (
  `ID` bigint(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `content` text,
  `type` varchar(25) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `created_by` bigint(11) DEFAULT NULL,
  `updated_by` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_cache_files`
--

CREATE TABLE `social_cache_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(200) DEFAULT NULL,
  `type` varchar(300) DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `date_created` datetime NOT NULL,
  `module` varchar(50) NOT NULL,
  `path` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=902 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_comments`
--

CREATE TABLE `social_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `content` text,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `type` enum('STATUS','IMAGE') NOT NULL DEFAULT 'STATUS',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11289 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_configs`
--

CREATE TABLE `social_configs` (
  `name` varchar(150) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_couple_details`
--

CREATE TABLE `social_couple_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `status` bigint(20) DEFAULT NULL,
  `date_status` datetime DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `partner_id` bigint(20) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_couples`
--

CREATE TABLE `social_couples` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_files`
--

CREATE TABLE `social_files` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(200) DEFAULT NULL,
  `type` varchar(300) DEFAULT NULL,
  `post_id` bigint(20) DEFAULT NULL,
  `mode` varchar(20) DEFAULT NULL,
  `tab_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_friends`
--

CREATE TABLE `social_friends` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `friend_id` bigint(20) DEFAULT NULL,
  `date_request` datetime DEFAULT NULL,
  `date_active` datetime DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `score` float DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `user_id` (`user_id`,`friend_id`)
) ENGINE=InnoDB AUTO_INCREMENT=73341 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_group_members`
--

CREATE TABLE `social_group_members` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `is_admin` tinyint(4) DEFAULT '0',
  `group_id` bigint(20) DEFAULT NULL,
  `date_request` datetime DEFAULT NULL,
  `request_by_id` bigint(20) DEFAULT NULL,
  `date_join` datetime DEFAULT NULL,
  `date_quit` datetime DEFAULT NULL,
  `quit_by_id` bigint(20) DEFAULT NULL,
  `accept_by_id` bigint(20) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `is_rule_diploma_notice` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2424 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_groups`
--

CREATE TABLE `social_groups` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `department_id` bigint(20) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `desc` text,
  `status` tinyint(4) DEFAULT '0',
  `is_private` tinyint(4) DEFAULT '1',
  `is_company` tinyint(4) DEFAULT '0',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `active_by_id` bigint(20) DEFAULT NULL,
  `date_active` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_images`
--

CREATE TABLE `social_images` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `filename` varchar(300) DEFAULT NULL,
  `size` varchar(200) DEFAULT NULL,
  `img_type` enum('TIMELINE','ALBUM','COVER','AVATAR','POSTAVATAR') DEFAULT 'TIMELINE',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `post_id` bigint(20) DEFAULT NULL,
  `album_id` bigint(20) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  `wall_type` enum('GROUP','USER') DEFAULT 'USER',
  `is_current` enum('0','1') DEFAULT '0',
  `wall_id` bigint(20) DEFAULT NULL,
  `is_post_ava` enum('0','1') DEFAULT '0' COMMENT '0 : Không phải ảnh đại diện của bài viết\n1 : Là ảnh đại diện của bài viết',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1999 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_likes`
--

CREATE TABLE `social_likes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `type` enum('IMAGE','STATUS','COMMENT') DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20827 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_notes`
--

CREATE TABLE `social_notes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_draft` enum('PUBLISH','DRAFT') DEFAULT NULL COMMENT '''0'': ''is draft'', ''1'': ''publish''',
  `title` varchar(500) DEFAULT NULL,
  `content` text,
  `post_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_notice_diploma`
--

CREATE TABLE `social_notice_diploma` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(400) DEFAULT NULL,
  `content` text,
  `group_id` bigint(20) DEFAULT NULL,
  `post_id` bigint(20) DEFAULT NULL,
  `type` enum('NOTICE','DIPLOMA','CHAMPION','SUCCESSWAY') DEFAULT NULL,
  `tab_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=371 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_posts`
--

CREATE TABLE `social_posts` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `content` text,
  `type` enum('STATUS','NOTE','NOTICE','DIPLOMA','TIMELINE','ALBUM','COVER','AVATAR','PROBLEM','THANK','CHAMPION','SUCCESSWAY','DKTNEWS') DEFAULT 'STATUS',
  `real_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `is_birthday` tinyint(4) DEFAULT NULL,
  `is_wish_new` tinyint(4) NOT NULL DEFAULT '0',
  `is_preview` tinyint(4) DEFAULT '0',
  `preview_link_image` tinytext,
  `preview_desc` varchar(500) DEFAULT NULL,
  `preview_title` varchar(500) DEFAULT NULL,
  `preview_link` varchar(500) DEFAULT NULL,
  `preview_domain` varchar(300) DEFAULT NULL,
  `wall_type` enum('GROUP','USER') DEFAULT 'USER',
  `wall_id` bigint(20) DEFAULT NULL,
  `thank_ids` text,
  `tab_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=12556 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_sub_comments`
--

CREATE TABLE `social_sub_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) DEFAULT NULL,
  `content` text,
  `date_created` date DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_tab_groups`
--

CREATE TABLE `social_tab_groups` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `tab_id` bigint(20) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  `group_name` varchar(500) DEFAULT NULL,
  `order_number` int(11) DEFAULT NULL,
  `limited_post_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_tab_settings`
--

CREATE TABLE `social_tab_settings` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `status` enum('0','1') DEFAULT NULL COMMENT '0 : Ẩn\n1 : Hiện',
  `icon` varchar(500) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_walls`
--

CREATE TABLE `social_walls` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `wall_id` bigint(20) DEFAULT NULL,
  `type` enum('GROUP','USER') DEFAULT 'USER',
  PRIMARY KEY (`ID`),
  KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `social_walls_bk`
--

CREATE TABLE `social_walls_bk` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `wall_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `statistics`
--

CREATE TABLE `statistics` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `namespace` int(11) NOT NULL,
  `value1` decimal(10,0) NOT NULL DEFAULT '0',
  `value2` decimal(10,0) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `survey_answers`
--

CREATE TABLE `survey_answers` (
  `survey_answer_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `survey_question_id` bigint(20) DEFAULT NULL,
  `survey_id` bigint(20) DEFAULT NULL,
  `answers` varchar(500) DEFAULT NULL,
  `count` int(11) DEFAULT '0',
  PRIMARY KEY (`survey_answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `survey_questions`
--

CREATE TABLE `survey_questions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` bigint(20) DEFAULT NULL,
  `content` varchar(500) DEFAULT NULL,
  `type` enum('0','1','2') DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `survey_user_answers`
--

CREATE TABLE `survey_user_answers` (
  `survey_question_id` bigint(20) DEFAULT NULL,
  `survey_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `result` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `survey_users`
--

CREATE TABLE `survey_users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `surveys`
--

CREATE TABLE `surveys` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  `date_start` datetime DEFAULT NULL,
  `date_end` datetime DEFAULT NULL,
  `num_answer` int(11) DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `table_relations`
--

CREATE TABLE `table_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_table` varchar(32) NOT NULL,
  `source_id` int(11) NOT NULL,
  `destination_table` varchar(32) NOT NULL,
  `destination_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `desc` text NOT NULL,
  `parent_id` bigint(20) NOT NULL,
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `priority` tinyint(4) NOT NULL DEFAULT '1',
  `dateof` decimal(10,1) unsigned DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `date_active` datetime DEFAULT NULL,
  `rate` int(6) NOT NULL DEFAULT '0',
  `status` tinyint(4) DEFAULT '0',
  `leader_id` bigint(20) DEFAULT NULL,
  `perform_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) NOT NULL,
  `updated_by_id` bigint(20) NOT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `approver_id` bigint(20) DEFAULT NULL,
  `process_id` bigint(20) DEFAULT NULL,
  `last_action` int(11) DEFAULT '0',
  `last_step` int(11) DEFAULT NULL,
  `current_step` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `created_by_id` (`created_by_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `template_exports`
--

CREATE TABLE `template_exports` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) NOT NULL,
  `type` varchar(300) NOT NULL,
  `size` varchar(100) NOT NULL,
  `name` varchar(200) NOT NULL,
  `filename` varchar(300) NOT NULL,
  `module` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `template_mails`
--

CREATE TABLE `template_mails` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `module` varchar(200) DEFAULT NULL,
  `sub_module` varchar(255) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `content` text,
  `update_by_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `type` enum('AUTO','TEMPLATE') DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `module` (`module`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8;

--
-- Table structure for table `thanks`
--

CREATE TABLE `thanks` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `from_user_id` bigint(20) DEFAULT NULL,
  `to_user_id` bigint(20) DEFAULT NULL,
  `content` text,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11584 DEFAULT CHARSET=utf8;

--
-- Table structure for table `tool_websites`
--

CREATE TABLE `tool_websites` (
  `website` varchar(255) NOT NULL,
  `package_name` varchar(45) NOT NULL,
  `started_date` date NOT NULL,
  `expired_date` date DEFAULT NULL,
  `source` enum('SKY','ORION') NOT NULL DEFAULT 'SKY',
  `sync_id` int(11) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `contract_id` bigint(20) DEFAULT NULL,
  `contract_code` varchar(255) DEFAULT NULL,
  `is_virtual` enum('0','1') NOT NULL DEFAULT '0',
  `potential_id` bigint(20) DEFAULT NULL,
  `potential_code` varchar(20) DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `customer_code` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`website`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tracking_statuses`
--

CREATE TABLE `tracking_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference_type` varchar(16) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `status` bit(1) NOT NULL DEFAULT b'0',
  `created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `query` (`reference_type`,`reference_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4145 DEFAULT CHARSET=utf8;

--
-- Table structure for table `trade_union_fees`
--

CREATE TABLE `trade_union_fees` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `money` decimal(20,5) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `urlcodemap`
--

CREATE TABLE `urlcodemap` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `encoded` varchar(128) NOT NULL,
  `decoded` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `urlcodemapUIdx1` (`encoded`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `user_tokens`
--

CREATE TABLE `user_tokens` (
  `user_id` bigint(20) NOT NULL,
  `office_token` varchar(500) DEFAULT NULL,
  `app_token` varchar(500) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `device` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_deleted` enum('yes','no') DEFAULT 'no',
  `is_admin` enum('yes','no') DEFAULT 'no',
  `group_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` text,
  `reset_password` text,
  `ip` varchar(100) DEFAULT NULL,
  `inherit_roles` enum('yes','no') DEFAULT 'yes',
  `roles` text,
  `max_size_upload` bigint(20) unsigned DEFAULT '0',
  `memory_upload` bigint(20) unsigned DEFAULT '0',
  `email` varchar(100) DEFAULT NULL,
  `gender` tinyint(3) DEFAULT '0',
  `photo` text,
  `date_created` datetime DEFAULT NULL,
  `date_login` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `date_deleted` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `style` varchar(50) DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `fullname` varchar(150) DEFAULT NULL,
  `fav` varchar(500) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `profiles` varchar(500) DEFAULT NULL,
  `university` varchar(300) DEFAULT NULL,
  `sport` varchar(300) DEFAULT NULL,
  `film` varchar(300) DEFAULT NULL,
  `music` varchar(300) DEFAULT NULL,
  `yahoo` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `city` varchar(300) DEFAULT NULL,
  `web` varchar(300) DEFAULT NULL,
  `date_active` datetime DEFAULT NULL,
  `date_notice` datetime DEFAULT NULL,
  `settings` text,
  `row_id` int(11) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `mobile_token` text,
  `total_thank` int(11) NOT NULL DEFAULT '0',
  `total_action_thank` int(11) NOT NULL DEFAULT '0',
  `date_thank` datetime DEFAULT NULL,
  `date_action_thank` datetime DEFAULT NULL,
  `token_get_pass` varchar(500) DEFAULT NULL,
  `token_time_expired` datetime DEFAULT NULL,
  `reset_expired_time` int(11) DEFAULT NULL,
  `time_cron_crm_warningwrite` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `is_deleted` (`is_deleted`) USING BTREE,
  KEY `DepartmentIdIndex` (`department_id`) USING BTREE,
  KEY `FullnameIndex` (`fullname`(50)) USING BTREE,
  KEY `CrmContactQueryIndex` (`fullname`(50),`department_id`) USING BTREE,
  KEY `PersonnelIdIndex` (`personnel_id`) USING BTREE,
  KEY `UserNameIndex` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8337 DEFAULT CHARSET=utf8;

--
-- Table structure for table `users_bk_20160530`
--

CREATE TABLE `users_bk_20160530` (
  `ID` bigint(20) NOT NULL DEFAULT '0',
  `is_deleted` enum('yes','no') DEFAULT 'no',
  `is_admin` enum('yes','no') DEFAULT 'no',
  `group_id` bigint(20) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` text,
  `reset_password` text,
  `ip` varchar(100) DEFAULT NULL,
  `inherit_roles` enum('yes','no') DEFAULT 'yes',
  `roles` text,
  `max_size_upload` bigint(20) unsigned DEFAULT '0',
  `memory_upload` bigint(20) unsigned DEFAULT '0',
  `email` varchar(100) DEFAULT NULL,
  `gender` tinyint(3) DEFAULT '0',
  `photo` text,
  `date_created` datetime DEFAULT NULL,
  `date_login` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `date_deleted` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `style` varchar(50) DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `fullname` varchar(150) DEFAULT NULL,
  `fav` varchar(500) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `profiles` varchar(500) DEFAULT NULL,
  `university` varchar(300) DEFAULT NULL,
  `sport` varchar(300) DEFAULT NULL,
  `film` varchar(300) DEFAULT NULL,
  `music` varchar(300) DEFAULT NULL,
  `yahoo` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `city` varchar(300) DEFAULT NULL,
  `web` varchar(300) DEFAULT NULL,
  `date_active` datetime DEFAULT NULL,
  `date_notice` datetime DEFAULT NULL,
  `settings` text,
  `row_id` int(11) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `mobile_token` text,
  `total_thank` int(11) NOT NULL DEFAULT '0',
  `total_action_thank` int(11) NOT NULL DEFAULT '0',
  `date_thank` datetime DEFAULT NULL,
  `date_action_thank` datetime DEFAULT NULL,
  `token_get_pass` varchar(500) DEFAULT NULL,
  `token_time_expired` datetime DEFAULT NULL,
  `reset_expired_time` int(11) DEFAULT NULL,
  `time_cron_crm_warningwrite` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `v_contracts_had_receipts`
--

/*!50001 DROP VIEW IF EXISTS `v_contracts_had_receipts`*/;
/*!50001 CREATE TABLE `v_contracts_had_receipts` (
  `id` tinyint NOT NULL,
  `code` tinyint NOT NULL,
  `created` tinyint NOT NULL,
  `package` tinyint NOT NULL,
  `opportunity_id` tinyint NOT NULL,
  `website` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `v_field_log_sources`
--

/*!50001 DROP VIEW IF EXISTS `v_field_log_sources`*/;
/*!50001 CREATE TABLE `v_field_log_sources` (
  `lead_id` tinyint NOT NULL,
  `source_type` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `v_sources_details`
--

/*!50001 DROP VIEW IF EXISTS `v_sources_details`*/;
/*!50001 CREATE TABLE `v_sources_details` (
  `source_ID` tinyint NOT NULL,
  `code` tinyint NOT NULL,
  `title` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `v_trial_service_sources`
--

/*!50001 DROP VIEW IF EXISTS `v_trial_service_sources`*/;
/*!50001 CREATE TABLE `v_trial_service_sources` (
  `lead_id` tinyint NOT NULL,
  `website` tinyint NOT NULL,
  `created` tinyint NOT NULL,
  `source_type` tinyint NOT NULL,
  `source_reference` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `v_user_info`
--

/*!50001 DROP VIEW IF EXISTS `v_user_info`*/;
/*!50001 CREATE TABLE `v_user_info` (
  `ID` tinyint NOT NULL,
  `personnel_code` tinyint NOT NULL,
  `username` tinyint NOT NULL,
  `department_id` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `department` tinyint NOT NULL,
  `parent_id` tinyint NOT NULL,
  `lineage` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `work_colors`
--

CREATE TABLE `work_colors` (
  `position_id` bigint(20) NOT NULL,
  `color` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`position_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_day_details`
--

CREATE TABLE `work_day_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `day_id` bigint(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `manager_id` bigint(20) DEFAULT NULL,
  `why` varchar(500) DEFAULT NULL,
  `where` varchar(500) DEFAULT NULL,
  `how` varchar(500) DEFAULT NULL,
  `hour_start` time DEFAULT NULL,
  `hour_end` time DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_days`
--

CREATE TABLE `work_days` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_issues`
--

CREATE TABLE `work_issues` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` enum('FEATURE','TASK','ISSUE') NOT NULL DEFAULT 'FEATURE',
  `title` varchar(300) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `project_title` varchar(300) DEFAULT NULL,
  `project_status` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `project_owner_ids` text,
  `project_assign_ids` text,
  `project_user_ids` text,
  `num_comment` int(10) unsigned NOT NULL DEFAULT '0',
  `priority` enum('0','1','2','3') NOT NULL DEFAULT '0',
  `desc` text,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_kpi_items`
--

CREATE TABLE `work_kpi_items` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `kpi_id` int(11) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `kpi_type_ids` varchar(100) DEFAULT NULL,
  `user_ids` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_kpi_results`
--

CREATE TABLE `work_kpi_results` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `kpi_id` int(11) DEFAULT NULL,
  `kpi_type_id` int(11) DEFAULT NULL,
  `value` decimal(20,2) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_kpi_types`
--

CREATE TABLE `work_kpi_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `desc` text,
  `type_unit` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_kpi_units`
--

CREATE TABLE `work_kpi_units` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_kpis`
--

CREATE TABLE `work_kpis` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `type` enum('DAY','MONTH','WEEK') DEFAULT 'DAY',
  `day_condition` time DEFAULT NULL,
  `week_condition_date` enum('1','2','3','4','5','6') DEFAULT NULL,
  `week_condition_time` time DEFAULT NULL,
  `desc` text,
  `department_ids` varchar(300) DEFAULT NULL,
  `user_ids` varchar(300) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `updated_by_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_pages`
--

CREATE TABLE `work_pages` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `project_title` varchar(300) DEFAULT NULL,
  `project_status` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `project_owner_ids` text,
  `project_assign_ids` text,
  `project_user_ids` text,
  `num_comment` int(10) unsigned NOT NULL DEFAULT '0',
  `desc` text,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_project_templates`
--

CREATE TABLE `work_project_templates` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `data` text,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_project_types`
--

CREATE TABLE `work_project_types` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `data` text,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_projects`
--

CREATE TABLE `work_projects` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `project_type` enum('EXTERNAL','INTERNAL') DEFAULT 'INTERNAL',
  `contract_id` bigint(20) DEFAULT NULL,
  `owner_ids` text,
  `assign_ids` text,
  `member_ids` text,
  `user_ids` text,
  `status_old` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `status` enum('DOING','PENDING','COMPLETED') DEFAULT 'DOING',
  `status_new` enum('DOING','PENDING','COMPLETED') DEFAULT 'DOING',
  `desc` text,
  `num_task` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `num_issue` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `num_member` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `num_comment` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `num_file` bigint(20) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `problem_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `config_percent` enum('AUTO','MANUAL') DEFAULT 'AUTO',
  `is_contract` enum('0','1') DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_report_days`
--

CREATE TABLE `work_report_days` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `content` text,
  `desc_1` text,
  `desc_2` text,
  `desc_3` text,
  `desc_4` text,
  `desc_5` text,
  `desc_6` text,
  `user_id` bigint(20) DEFAULT NULL,
  `personnel_id` bigint(20) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_report_weeks`
--

CREATE TABLE `work_report_weeks` (
  `year` year(4) NOT NULL,
  `week` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`year`,`week`,`user_id`),
  UNIQUE KEY `date` (`week`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_setting_priorities`
--

CREATE TABLE `work_setting_priorities` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_setting_times`
--

CREATE TABLE `work_setting_times` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(300) DEFAULT NULL,
  `time` float DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_setting_type_reports`
--

CREATE TABLE `work_setting_type_reports` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title_type` varchar(500) DEFAULT NULL,
  `title` enum('1','0') DEFAULT '1',
  `manager_id` enum('1','0') DEFAULT '1',
  `hour_start` enum('1','0') DEFAULT '1',
  `hour_end` enum('1','0') DEFAULT '1',
  `why` enum('1','0') DEFAULT '0',
  `how` enum('1','0') DEFAULT '0',
  `where` enum('1','0') DEFAULT '0',
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_sub_task_template_details`
--

CREATE TABLE `work_sub_task_template_details` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `type_id` bigint(20) DEFAULT NULL,
  `title` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `assign_id` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `template_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=199 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `work_sub_task_templates`
--

CREATE TABLE `work_sub_task_templates` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `responsibility_ids` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_sub_task_types`
--

CREATE TABLE `work_sub_task_types` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `required_files` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_task_assigns`
--

CREATE TABLE `work_task_assigns` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `work_task_id` bigint(20) NOT NULL DEFAULT '0',
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `date_finish` datetime DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `step` tinyint(4) DEFAULT NULL,
  `desc` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_task_comments`
--

CREATE TABLE `work_task_comments` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `date_stick` datetime DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `content` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_task_read`
--

CREATE TABLE `work_task_read` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) DEFAULT NULL,
  `user_read_id` bigint(20) DEFAULT NULL,
  `first_read_date` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=53438 DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_task_relations`
--

CREATE TABLE `work_task_relations` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `task_id` bigint(20) DEFAULT '0',
  `start_task_id` bigint(20) DEFAULT '0',
  `start_status` enum('DOING','COMPLETED') DEFAULT NULL,
  `end_status` enum('DOING','COMPLETED') DEFAULT NULL,
  `start_plan_old` datetime DEFAULT NULL,
  `end_plan_old` datetime DEFAULT NULL,
  `start_old` datetime DEFAULT NULL,
  `end_old` datetime DEFAULT NULL,
  `duration_plan` int(11) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `work_tasks`
--

CREATE TABLE `work_tasks` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` enum('TASK','LIST','MILESTONE','SUBTASK') NOT NULL DEFAULT 'TASK',
  `title` varchar(300) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `project_title` varchar(300) DEFAULT NULL,
  `project_status_old` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `project_owner_ids` text,
  `project_user_ids` text,
  `project_assign_ids` text,
  `list_id` bigint(20) DEFAULT NULL,
  `milestone_id` bigint(20) DEFAULT NULL,
  `issue_id` bigint(20) DEFAULT NULL,
  `list_title` varchar(300) DEFAULT NULL,
  `milestone_title` varchar(300) DEFAULT NULL,
  `issue_title` varchar(300) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `start_plan` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `end_plan` datetime DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `duration_plan` int(11) DEFAULT NULL,
  `priority` enum('0','1','2','3') DEFAULT '0',
  `status_old` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `status` enum('DOING','PENDING','COMPLETED') DEFAULT 'DOING',
  `project_status` enum('DOING','PENDING','COMPLETED') DEFAULT 'DOING',
  `last_status_old` enum('OPEN','CLOSED') DEFAULT 'OPEN',
  `last_status` enum('DOING','PENDING','COMPLETED') DEFAULT 'DOING',
  `percent` tinyint(3) unsigned DEFAULT '0',
  `last_percent` decimal(10,2) DEFAULT NULL,
  `desc` text,
  `assign_ids` text,
  `num_comment` tinyint(4) DEFAULT '0',
  `type_assign` enum('PARALLEL','TURN') DEFAULT 'TURN',
  `type_created` enum('0','1') DEFAULT '0' COMMENT '0: Created in view. 1: Created in add',
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `problem_id` bigint(20) DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `updated_by_id` bigint(20) DEFAULT NULL,
  `contract_id` bigint(20) DEFAULT NULL,
  `perform_id` bigint(20) DEFAULT NULL,
  `template_id` bigint(20) DEFAULT NULL,
  `sub_task_type_id` bigint(20) DEFAULT NULL,
  `template_none_assign` enum('0','1') DEFAULT NULL,
  `original_start_plan` datetime DEFAULT NULL,
  `original_end_plan` datetime DEFAULT NULL,
  `is_read` enum('yes','no') DEFAULT NULL COMMENT 'yes : đã đọc\nno: chưa đọc',
  `read_by_id` bigint(20) DEFAULT NULL,
  `last_action_date` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42491 DEFAULT CHARSET=utf8;

--
-- Dumping routines for database 'dms'
--
/*!50003 DROP FUNCTION IF EXISTS `calculate_duration_of_sale_support` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `calculate_duration_of_sale_support`(sale_support_id int, total_site_sent int, date_start_from date, date_start_to date) RETURNS float
    DETERMINISTIC
BEGIN
  DECLARE _time_perform float;
  SET _time_perform = (SELECT
      SUM(DATEDIFF(cc.site_date_approver, p.`date_created`))
    FROM `crm_contracts` AS c
      INNER JOIN (SELECT
          contract_id,
          id,
          date_created
        FROM `crm_performs`
        WHERE `origin` = 'SALE'
        GROUP BY contract_id) AS p
        ON c.ID = p.`contract_id`
      INNER JOIN (SELECT
          ID,
          IF(site_status_1 = '1', site_date_approver_1, site_date_approver_2) AS site_date_approver
        FROM `crm_contracts`
        WHERE `site_status` = '1'
        AND `sale_support_id` = sale_support_id) AS cc
        ON cc.ID = c.ID
     INNER JOIN (SELECT
    pr.contract_id
  FROM perform_reports pr) AS pr
    ON c.ID = pr.contract_id
    WHERE c.`sale_support_id` = sale_support_id
    AND c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
    AND (c.date_start BETWEEN date_start_from AND date_start_to));
  RETURN (_time_perform / total_site_sent);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `count_perform_review_by_sale` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `count_perform_review_by_sale`(sale_id INT,  date_start_from DATE, date_start_to DATE) RETURNS int(11)
    DETERMINISTIC
BEGIN
	DECLARE _count INT;
	SET _count = (
		SELECT COUNT(pr.id) AS `count`
		FROM `perform_reviews` pr INNER JOIN `crm_contracts` cc ON pr.contract_id = cc.ID
		WHERE cc.sale_id = sale_id AND cc.date_start BETWEEN date_start_from AND date_start_to
	);
	RETURN _count;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `DEPARTMENT_TREE_NAME` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `DEPARTMENT_TREE_NAME`(department_id INT) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE lineage VARCHAR(100);
  DECLARE title NVARCHAR(255);

  SET lineage = (SELECT CONCAT(`d`.`lineage`, `d`.`ID`) AS `lineage` FROM `departments` `d` WHERE `d`.`ID` = department_id);
  SET lineage = REPLACE(lineage, SUBSTRING_INDEX(lineage, '/', 2), '');
  SET lineage = REPLACE(SUBSTRING(lineage, 2), '/', ',');

  SET title = (
    SELECT GROUP_CONCAT(d.title SEPARATOR ' � ') AS `title` FROM `departments` `d` WHERE FIND_IN_SET(`d`.`ID`, lineage)
  );

  RETURN TRIM(title);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_find_child_departments` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_find_child_departments`(d_ID int) RETURNS int(11)
    DETERMINISTIC
BEGIN
  DECLARE _parent_id int;
  SET _parent_id = -1;
 
    IF NOT EXISTS (SELECT
          *
        FROM departments d
        WHERE d.parent_id = d_ID) THEN
      SET _parent_id = (SELECT
          d.parent_id
        FROM departments d
        WHERE d.ID = d_ID);
    END IF;
  RETURN _parent_id;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_first_receipt_from_contract` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_first_receipt_from_contract`(p_contract_id int) RETURNS datetime
    DETERMINISTIC
BEGIN
   DECLARE v_date   DATETIME;
       select fi.date_income into v_date
       from
       finance_internals fi
       where 
       fi.contract_id=p_contract_id
       and fi.date_income is not null 
       and fi.rollback_status='0'
       and fi.status='1' 
       order by date_income asc
       limit 1;
  return v_date;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_fullname_by_userIds` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_fullname_by_userIds`(user_ids varchar(50)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE return_str nvarchar(255);
  DECLARE user_id varchar(25);
  DECLARE number_of_char int;
SET number_of_char = LENGTH(user_ids) - LENGTH(REPLACE(user_ids, ',', ''));

  WHILE number_of_char >= 0 DO

    IF (number_of_char > 0) THEN
      SET user_id = (SELECT
          SUBSTRING_INDEX(user_ids, ',', 1));
SET user_ids = REPLACE(user_ids, CONCAT(user_id, ','), '');
    ELSE
SET user_id = user_ids;
    END IF;

    IF (return_str IS NULL) THEN

      SET return_str = (SELECT
          vui.name
        FROM v_user_info vui
        WHERE ID = user_id);
    ELSE
SET return_str = CONCAT(return_str, ' , ', (SELECT
    vui.name
  FROM v_user_info vui
  WHERE ID = user_id));
    END IF;

SET number_of_char = number_of_char - 1;
  END WHILE;

  RETURN return_str;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_full_dep` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_full_dep`(GivenID INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024) CHARSET utf8;
    DECLARE cm CHAR(1);
    DECLARE ch INT;
    Declare v_title varchar(250) CHARSET utf8;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM departments WHERE id = ch) A;
        IF ch > 0 THEN
        select title into v_title from departments where id=ch;
            SET rv = CONCAT(rv,cm,v_title);
            SET cm = '<';
        END IF;
    END WHILE;
    RETURN rv;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_lead_source` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_lead_source`(p_lead_id double) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
DECLARE v_lead_source  varchar(255)   ;
    SELECT 
          substring_index(group_concat(ctsm.meta_value SEPARATOR ','),
                          ',',
                          1) into v_lead_source
          
          
     FROM ((dms.crm_trial_services cts
            JOIN dms.crm_trial_service_meta ctsm)
           JOIN dms.crm_leads cl)
    WHERE (    (cts.id = ctsm.trial_service_id)
           AND (ctsm.meta_key = 'source_reference')
           AND (cl.id = cts.lead_id)
           AND cl.id =p_lead_id)
   GROUP BY cts.lead_id;
   RETURN v_lead_source;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_number_of_weekdays` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_number_of_weekdays`(p_date_from DATE, p_date_to DATE) RETURNS int(11)
    DETERMINISTIC
BEGIN   
RETURN  5 * (DATEDIFF(p_date_to, p_date_from) DIV 7)
    + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(p_date_from)
    + WEEKDAY(p_date_to) + 1, 1);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_potential_from_contract` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_potential_from_contract`(p_contract_id double) RETURNS double
    DETERMINISTIC
BEGIN
   DECLARE v_potential_id   double;
     SELECT cp1.id into v_potential_id
   
     FROM 
          crm_contracts ct1,
          crm_customers cu1,
          crm_contract_customers cc1,
          crm_potentials cp1
         
    WHERE     
          ct1.id = cc1.contract_id
          AND cc1.customer_id_old = cu1.id
          AND cu1.potential_id = cp1.ID;
   RETURN v_potential_id;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_receipt_amount_from_contract` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_receipt_amount_from_contract`(p_contract_id int) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_amount   int;
       select sum(fi.total_money) into v_amount
       from
       finance_internals fi
       where 
       fi.contract_id=p_contract_id
       and fi.date_income is not null 
       and fi.rollback_status='0'
       and fi.status='1' 
       group by fi.contract_id;
       
  
  return v_amount;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_reference_name` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `fc_get_reference_name`(p_source_type INT, p_source_reference VARCHAR(50), p_service_name VARCHAR(20)) RETURNS varchar(30) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE result VARCHAR(30);
  SET result = IF(p_service_name = 'Bizweb', 'Khác', 'Từ hệ thống');

  IF (p_source_type = 2) THEN
    IF
      INSTR(p_source_reference, 'bizweb') = 1
      OR INSTR(p_source_reference, 'sky') = 1
      OR INSTR(p_source_reference, 'bizwebhome') = 1
      OR INSTR(p_source_reference, 'banggia') = 1
      OR INSTR(p_source_reference, 'blog') = 1
      OR INSTR(p_source_reference, 'theme') = 1
      OR INSTR(p_source_reference, 'app') = 1
      OR INSTR(p_source_reference, 'kenhwebsite') = 1
      OR INSTR(p_source_reference, 'kenhsendo') = 1
      OR INSTR(p_source_reference, 'kenhzalo') = 1
      OR INSTR(p_source_reference, 'fpage') = 1
      OR INSTR(p_source_reference, 'kdonline') = 1
      OR INSTR(p_source_reference, 'kdquanao') = 1
      OR INSTR(p_source_reference, 'kdmypham') = 1
      OR INSTR(p_source_reference, 'kdnho') = 1
      OR INSTR(p_source_reference, 'kdgiaydep') = 1
      OR INSTR(p_source_reference, 'khoinghiepkd') = 1
      OR INSTR(p_source_reference, 'bhonline') = 1
      OR INSTR(p_source_reference, 'bhtructuyen') = 1
      OR INSTR(p_source_reference, 'bhquamang') = 1
      OR INSTR(p_source_reference, 'banquanao') = 1
      OR INSTR(p_source_reference, 'bantuixach') = 1
      OR INSTR(p_source_reference, 'banmypham') = 1
      OR INSTR(p_source_reference, 'thietkeweb') = 1
      OR INSTR(p_source_reference, 'laptrangweb') = 1
      OR INSTR(p_source_reference, 'taoweb') = 1
      OR INSTR(p_source_reference, 'ctythietkeweb') = 1
      OR INSTR(p_source_reference, 'webbanhang') = 1
      OR INSTR(p_source_reference, 'webtrongoi') = 1
      OR INSTR(p_source_reference, 'bizweblagi') = 1
      OR INSTR(p_source_reference, 'webchuanseo') = 1
      OR INSTR(p_source_reference, 'webuytin') = 1
      OR INSTR(p_source_reference, 'web_tmdt') = 1
      OR INSTR(p_source_reference, 'tenmien') = 1
      OR INSTR(p_source_reference, 'email_') = 1
      OR INSTR(p_source_reference, 'home') = 1
      OR p_source_reference= ''
      OR p_source_reference IS NULL THEN

      SET result = IF(p_service_name = 'Bizweb', p_service_name, 'Từ hệ thống');

    END IF;

    IF INSTR(p_source_reference, 'sieuweb') = 1 THEN
      SET result = 'Sieuweb';
    END IF;
    IF
      INSTR(p_source_reference, 'blogbizweb_') = 1
      OR INSTR(p_source_reference, 'blogsapo_') = 1
      OR INSTR(p_source_reference, 'blogforum') = 1
      OR INSTR(p_source_reference, 'mkt_') = 1
      OR INSTR(p_source_reference, 'van_') = 1
      OR INSTR(p_source_reference, 'doanhnhan_') = 1 THEN
      SET result = 'Blog Forum';
    END IF;
    IF INSTR(p_source_reference, 'QC_gdn') = 1
      OR INSTR(p_source_reference, 'GA') = 1 THEN
      SET result = 'GDN';
    END IF;
    IF INSTR(p_source_reference, 'QC_re') = 1
      OR INSTR(p_source_reference, 'RE') = 1 THEN
      SET result = 'Remarketing';
    END IF;
    IF INSTR(p_source_reference, 'QC_fb') = 1
      OR INSTR(p_source_reference, 'fb') = 1 THEN
      SET result = 'Facebook';
    END IF;
    IF INSTR(p_source_reference, 'QC_search_kenhwebsite') = 1
      OR INSTR(p_source_reference, 'search4') = 1
      OR INSTR(p_source_reference, 'search5') = 1
      OR INSTR(p_source_reference, 'search6') = 1 THEN
      SET result = 'Search nhu cầu';
    END IF;
    IF INSTR(p_source_reference, 'QC_search_bizweb') = 1
      OR INSTR(p_source_reference, 'search1') = 1
      OR INSTR(p_source_reference, 'search2') = 1
      OR INSTR(p_source_reference, 'search3') = 1 THEN
      SET result = 'Search thương hiệu';
    END IF;
    IF INSTR(p_source_reference, 'QC_search_lp') = 1
      OR INSTR(p_source_reference, 'search_') = 1 THEN
      SET result = 'Search Landing Page';
    END IF;
    IF INSTR(p_source_reference, '?kd=') = 1 THEN
      SET result = 'Link kinh doanh';
    END IF;
  END IF;
  IF (p_source_type = 1) THEN
    SET result = 'Tự liên hệ';
  END IF;
  IF (p_source_type = 3) THEN
    SET result = 'Kinh doanh tự nhập';
  END IF;
  IF (p_source_type = 4) THEN
    SET result = 'Telesale nhập';
  END IF;
  IF (p_source_type = 5) THEN
    SET result = 'Từ web master, TK liên kết, CTV';
  END IF;
  IF (p_source_type = 6) THEN
    SET result = 'Tự nhập';
  END IF;
  IF (p_source_type = 7) THEN
    SET result = 'CSKH';
  END IF;
  IF (p_source_type = 8) THEN
    SET result = 'Affiliate đối tác';
  END IF;

  RETURN result;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_total_months` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_total_months`(date_a DATETIME, date_b DATETIME) RETURNS int(11)
    DETERMINISTIC
BEGIN
  DECLARE _date_a, _date_b DATETIME;
  DECLARE total_months INT;
  SET _date_a = DATE_FORMAT(date_a, '%Y-%m-01 00:00:00');
  SET _date_b = DATE_FORMAT(date_b, '%Y-%m-01 00:00:00');
  SET total_months = (SELECT TIMESTAMPDIFF(MONTH, _date_a, _date_b));
  RETURN total_months;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_get_total_number_of_weekdays` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_get_total_number_of_weekdays`(p_date_from DATE, p_date_to DATE) RETURNS int(11)
    DETERMINISTIC
BEGIN
DECLARE _total_day,_total_holiday int;
  SET _total_day = (SELECT
      fc_get_number_of_weekdays(p_date_from, p_date_to));
    IF EXISTS (SELECT
        h.id
      FROM holidays h
      WHERE (h.`from` BETWEEN p_date_from AND p_date_to)
      OR (h.`to` BETWEEN p_date_from AND p_date_to)) THEN

    SET _total_holiday = (SELECT
        SUM(total_holiday.total) AS total_holiday
      FROM (SELECT
          h.`from`,
          h.`to`,
        fc_get_number_of_weekdays(IF(p_date_from <= h.`from`,h.`from`,p_date_from),IF(p_date_to <= h.`to`, p_date_to,h.`to`)) `total`
        FROM holidays h
        WHERE (h.`from` BETWEEN p_date_from AND p_date_to)
        OR (h.`to` BETWEEN p_date_from AND p_date_to)) AS total_holiday);

SET _total_day = _total_day - _total_holiday;

  END IF;
RETURN _total_day;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_remove_sign` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_remove_sign`(p_string nvarchar(4000) ) RETURNS varchar(4000) CHARSET utf8
    DETERMINISTIC
BEGIN
    declare l_str nvarchar(4000)   ;
    SET l_str = LTRIM(p_string); 
    SET l_str = LOWER(RTRIM(l_str)); 
    SET l_str = REPLACE(l_str, 'á', 'a'); 
    SET l_str = REPLACE(l_str, 'à', 'a'); 
    SET l_str = REPLACE(l_str, 'ả', 'a'); 
    SET l_str = REPLACE(l_str, 'ã', 'a'); 
    SET l_str = REPLACE(l_str, 'ạ', 'a'); 
    SET l_str = REPLACE(l_str, 'â', 'a'); 
    SET l_str = REPLACE(l_str, 'ấ', 'a'); 
    SET l_str = REPLACE(l_str, 'ầ', 'a'); 
    SET l_str = REPLACE(l_str, 'ẩ', 'a'); 
    SET l_str = REPLACE(l_str, 'ẫ', 'a'); 
    SET l_str = REPLACE(l_str, 'ậ', 'a'); 
    SET l_str = REPLACE(l_str, 'ă', 'a'); 
    SET l_str = REPLACE(l_str, 'ắ', 'a'); 
    SET l_str = REPLACE(l_str, 'ằ', 'a'); 
    SET l_str = REPLACE(l_str, 'ẳ', 'a'); 
    SET l_str = REPLACE(l_str, 'ẵ', 'a'); 
    SET l_str = REPLACE(l_str, 'ặ', 'a'); 
    SET l_str = REPLACE(l_str, 'é', 'e'); 
    SET l_str = REPLACE(l_str, 'è', 'e'); 
    SET l_str = REPLACE(l_str, 'ẻ', 'e'); 
    SET l_str = REPLACE(l_str, 'ẽ', 'e'); 
    SET l_str = REPLACE(l_str, 'ẹ', 'e'); 
    SET l_str = REPLACE(l_str, 'ê', 'e'); 
    SET l_str = REPLACE(l_str, 'ế', 'e'); 
    SET l_str = REPLACE(l_str, 'ề', 'e'); 
    SET l_str = REPLACE(l_str, 'ể', 'e'); 
    SET l_str = REPLACE(l_str, 'ễ', 'e'); 
    SET l_str = REPLACE(l_str, 'ệ', 'e'); 
    SET l_str = REPLACE(l_str, 'í', 'i'); 
    SET l_str = REPLACE(l_str, 'ì', 'i'); 
    SET l_str = REPLACE(l_str, 'ỉ', 'i'); 
    SET l_str = REPLACE(l_str, 'ĩ', 'i'); 
    SET l_str = REPLACE(l_str, 'ị', 'i'); 
    SET l_str = REPLACE(l_str, 'ó', 'o'); 
    SET l_str = REPLACE(l_str, 'ò', 'o'); 
    SET l_str = REPLACE(l_str, 'ỏ', 'o'); 
    SET l_str = REPLACE(l_str, 'õ', 'o'); 
    SET l_str = REPLACE(l_str, 'ọ', 'o'); 
    SET l_str = REPLACE(l_str, 'ô', 'o'); 
    SET l_str = REPLACE(l_str, 'ố', 'o'); 
    SET l_str = REPLACE(l_str, 'ồ', 'o'); 
    SET l_str = REPLACE(l_str, 'ổ', 'o'); 
    SET l_str = REPLACE(l_str, 'ỗ', 'o'); 
    SET l_str = REPLACE(l_str, 'ộ','o'); 
    SET l_str = REPLACE(l_str, 'ơ', 'o'); 
    SET l_str = REPLACE(l_str, 'ớ', 'o'); 
    SET l_str = REPLACE(l_str, 'ờ', 'o'); 
    SET l_str = REPLACE(l_str, 'ở', 'o'); 
    SET l_str = REPLACE(l_str, 'ỡ', 'o'); 
    SET l_str = REPLACE(l_str, 'ợ', 'o'); 
    SET l_str = REPLACE(l_str, 'ú', 'u'); 
    SET l_str = REPLACE(l_str, 'ù', 'u'); 
    SET l_str = REPLACE(l_str, 'ủ', 'u'); 
    SET l_str = REPLACE(l_str, 'ũ', 'u'); 
    SET l_str = REPLACE(l_str, 'ụ', 'u'); 
    SET l_str = REPLACE(l_str, 'ư', 'u'); 
    SET l_str = REPLACE(l_str, 'ứ', 'u'); 
    SET l_str = REPLACE(l_str, 'ừ', 'u'); 
    SET l_str = REPLACE(l_str, 'ử', 'u'); 
    SET l_str = REPLACE(l_str, 'ữ', 'u'); 
    SET l_str = REPLACE(l_str, 'ự', 'u'); 
    SET l_str = REPLACE(l_str, 'ý', 'y'); 
    SET l_str = REPLACE(l_str, 'ỳ', 'y'); 
    SET l_str = REPLACE(l_str, 'ỷ', 'y'); 
    SET l_str = REPLACE(l_str, 'ỹ', 'y'); 
    SET l_str = REPLACE(l_str, 'ỵ', 'y'); 
    SET l_str = REPLACE(l_str, 'đ', 'd');  
  RETURN l_str;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_total_contract_from_contact` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_total_contract_from_contact`(p_contact_id int) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_count_contract   int;
   DECLARE v_count_potential   int;

   if fc_total_potential_from_contact(p_contact_id)=0 then return 0;
   else 
    Begin
    DROP TEMPORARY TABLE IF EXISTS temp_contact_potential;

       
       CREATE TEMPORARY TABLE temp_contact_potential
        SELECT cp.id
                       FROM crm_potentials cp
                      WHERE     1 = 1                   
                            AND (cp.condition_type = 'WARNING' or cp.condition_type ='NORMAL' or cp.condition_type ='FREE')
                            AND cp.contact_id=p_contact_id   ;
                            










    












       SELECT COUNT(distinct t.ID) INTO v_count_contract
        FROM crm_contracts t
        INNER JOIN crm_contract_customers ccc ON  t.ID = ccc.contract_id
        INNER JOIN crm_customers cc ON  cc.ID = ccc.customer_id_old
        INNER JOIN crm_potentials cp ON cp.customer_id = cc.ID
        INNER JOIN temp_contact_potential tp ON  tp.id = cp.id
        WHERE t.condition_type <> 'CANCEL'
        AND (t.status = 'APPROVER' OR t.status = 'SAVE');

   DROP TEMPORARY TABLE IF EXISTS temp_contact_potential;


   RETURN v_count_contract;
   end;
   end if;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_total_contract_from_lead` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_total_contract_from_lead`(p_lead_id int) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_count_contract   int;
   DECLARE v_count_opp   int;

   if fc_total_potential_from_lead(p_lead_id)=0 then return 0;
   else 
       SELECT COUNT(t.ID) INTO v_count_contract
        FROM crm_contracts t
        WHERE t.condition_type <> 'CANCEL'
        AND (t.status = 'APPROVER' OR t.status = 'SAVE')
        AND t.potential_id=p_lead_id;


   RETURN v_count_contract;
   end if;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_total_contract_from_potential` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_total_contract_from_potential`(p_potential_string TEXT) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_count_contract   int;
    if p_potential_string is null then return 0;
    end if;
      
       SELECT COUNT(distinct t.ID) INTO v_count_contract
        FROM crm_contracts t
        INNER JOIN crm_contract_customers ccc ON ccc.contract_id = t.ID 
        INNER JOIN crm_customers cc ON ccc.customer_id_old = cc.ID AND FIND_IN_SET(cc.potential_id,p_potential_string)>0
        
        
        WHERE t.condition_type <> 'CANCEL'
        AND (t.status = 'APPROVER' OR t.status = 'SAVE');
   RETURN v_count_contract;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_total_potential_from_contact` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_total_potential_from_contact`(p_contact double) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_count_potential   int;

   SELECT count(cp.ID)
     INTO v_count_potential
     FROM crm_potentials cp
    WHERE (cp.condition_type = 'WARNING' OR cp.condition_type = 'NORMAL' or cp.condition_type ='FREE')
    and cp.contact_id = p_contact;








   
   
   RETURN v_count_potential;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `fc_total_potential_from_lead` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `fc_total_potential_from_lead`(p_lead_id double) RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE v_count_opp   int;

   SELECT count(cl.ID)
     INTO v_count_opp
     FROM crm_leads cl
    WHERE (cl.type='OPPORTUNITY')
    and cl.status<>'DEAD'
    and cl.id = p_lead_id;
     RETURN v_count_opp;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `func_get_date_income` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `func_get_date_income`(contract_id int, internal_id int, user_id int) RETURNS date
    DETERMINISTIC
BEGIN
  DECLARE date_income date;
  IF EXISTS (SELECT
        a.id
      FROM crm_income_events a
      WHERE a.contract_id = contract_id
      AND a.internal_id = internal_id
      AND a.user_id = a.user_id) THEN
    SET date_income = (SELECT
        a.date_income
      FROM crm_income_events a
      WHERE a.contract_id = contract_id
      AND a.internal_id = internal_id
      AND a.user_id = a.user_id
      LIMIT 1);
  ELSE
    SET date_income = (SELECT
        fi.date_income
      FROM finance_internals fi
      WHERE fi.ID = internal_id);
  END IF;
  RETURN date_income;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `TOTAL_WEEKDAYS` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `TOTAL_WEEKDAYS`(date1 date, date2 date) RETURNS int(11)
    DETERMINISTIC
BEGIN

RETURN ABS(DATEDIFF(date2, date1)) + 1
       - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                      ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
       - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
       - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `URLDECODER` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` FUNCTION `URLDECODER`(str VARCHAR(4096) CHARSET utf8) RETURNS varchar(4096) CHARSET utf8
    DETERMINISTIC
BEGIN
               DECLARE X  INT;               
               DECLARE chr VARCHAR(256);
               DECLARE chrto VARCHAR(256);
               DECLARE result VARCHAR(4096);
               SET X = 1;
               WHILE X  <= (SELECT MAX(id) FROM urlcodemap) DO
                   SET chr = (SELECT `encoded` FROM urlcodemap WHERE id = X);
                   SET chrto = (SELECT `decoded` FROM urlcodemap WHERE id = X);                
                           SET str = REPLACE(str,chr,chrto);
                           SET  X = X + 1;                           
               END WHILE;
               RETURN str;
       END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `APPEND_PRIVATE_CODE_CUSTOMER` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `APPEND_PRIVATE_CODE_CUSTOMER`(IN contract_id INT, IN private_code VARCHAR(255))
BEGIN
  DECLARE customer_id INT;
  SET customer_id = (SELECT `a`.`customer_id_old` FROM `crm_contract_customers` `a` WHERE `a`.`contract_id` = contract_id);
  UPDATE `crm_contract_customers` `a` SET `a`.`private_code` = private_code WHERE `a`.`contract_id` = contract_id;
  UPDATE `crm_customers` `cc` SET `cc`.`private_code` = private_code WHERE `cc`.`ID` = customer_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `APPEND_VALUE_ADDED_PACKAGE` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `APPEND_VALUE_ADDED_PACKAGE`(
  IN title VARCHAR(255) CHARACTER SET utf8,
  IN unit VARCHAR(100) CHARACTER SET utf8,
  IN price DECIMAL
)
BEGIN
  DECLARE _valude_added_id INT;

  IF (unit = '' OR unit = NULL) THEN
    SET unit = 'default:months';
  END IF;

  IF NOT EXISTS (SELECT `a`.`ID` FROM `crm_value_added_services` `a` WHERE `a`.`title` = title) THEN
    INSERT INTO `crm_value_added_services` (
      `title`, `unit`, `has_vat`, `is_activated`, `created_by_id`, `date_created`, `updated_by_id`, `date_updated`
    )
    VALUES (
      title, unit, 0, 1, 1, NOW(), 1, NOW()
    );
    SET _valude_added_id = (SELECT LAST_INSERT_ID());

    INSERT INTO `crm_value_added_packages` (
      `package_id`, `value_added_id`, `price`, `dynamic_price`, `created_by_id`, `date_created`, `updated_by_id`, `date_updated`
    )
	    SELECT `a`.`ID`, _valude_added_id, price, 1, 1, NOW(), 1, NOW()
	    FROM `crm_product_service_packages` `a` 
	    WHERE `a`.`service_id` = 1
	    ORDER BY `a`.`ID` ASC;
  
    SELECT a.* FROM crm_value_added_packages a 
    WHERE a.value_added_id = _valude_added_id
    ORDER BY a.ID DESC;
  ELSE
    SELECT `a`.`ID`,'Da ton tai ten!' FROM `crm_value_added_services` `a` WHERE `a`.`title` = title;
  END IF;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CHANGE_CONTRACT_CUSTOMER` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `CHANGE_CONTRACT_CUSTOMER`(IN contract_id INT, IN customer_id INT)
BEGIN

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `convert_incomes_step2` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `convert_incomes_step2`()
BEGIN

  DECLARE _user_id INT;
  DECLARE _contract_id INT;
  DECLARE _internal_id INT;
  DECLARE _created DATETIME;
  DECLARE _created_by INT;
  DECLARE _modified DATETIME;
  DECLARE _modified_by INT;
  DECLARE _event_name VARCHAR(32);
  DECLARE _income_percent DECIMAL;
  DECLARE _commision_percent DECIMAL;

  DECLARE completed INT DEFAULT FALSE;
  DECLARE cursor_income CURSOR FOR
  SELECT `s`.`user_id`, `f`.`contract_id`, `s`.`post_id` AS `internal_id`, `s`.`date_created` AS `created`, 
  `s`.`created_by_id` AS `created_by`, `s`.`date_updated` AS `modified`, `s`.`updated_by_id` AS `modified_by`, 
  IF (`s`.`group` = 'SALE_SUPPORT', 'ServiceApproved', 'InternalApproved') AS `event_name`, 
  `s`.`percent` AS `income_percent`, `s`.`commision` AS `commision_percent`
  FROM `crm_income_sets` AS `s`
  INNER JOIN `finance_internals` AS `f` ON `s`.`TYPE` = 'INTERNAL' AND `s`.`post_id` = `f`.`id`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET completed = TRUE;
  OPEN cursor_income;
read_loop:
  LOOP
    FETCH cursor_income INTO _user_id, _contract_id, _internal_id, _created, _created_by, _modified, _modified_by, _event_name, _income_percent, _commision_percent;
    IF completed THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO `crm_income_ratio` (`user_id`, `contract_id`, `internal_id`, `created`, `created_by`, `modified`, `modified_by`)
    SELECT _user_id, _contract_id, _internal_id, _created, _created_by, _modified, _modified_by;
    INSERT INTO `crm_income_event_generators` (`ratio_id`, `event_name`, `income_percent`, `commision_percent`, `approved`)
    SELECT LAST_INSERT_ID(), _event_name, _income_percent, _commision_percent, IF(_event_name = 'InternalApproved', '1', '0');
  END LOOP;
  CLOSE cursor_income;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `explode_table` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `explode_table`(bound VARCHAR(255))
BEGIN
    DECLARE ID INT DEFAULT 0;
    DECLARE websites TEXT;
    DECLARE occurance INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value TEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT ID, f_1 as websites
                                         FROM crm_potentials
                                         WHERE f_1 LIKE '%,%'
                                         AND `condition_type` <> 'DELETED';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2;
    CREATE TEMPORARY TABLE table2(
    `type` varchar(100) DEFAULT NULL,
    `title` varchar(200),
	`post_id` bigint(20) DEFAULT NULL,
	`object` enum('CUSTOMER','POTENTIAL','CUSTOMER_CONTRACT','CONTACT','LOCATION','CONTRACT') DEFAULT NULL,
	`contract_id` bigint(20) DEFAULT NULL,
	`hidden` bit(1) DEFAULT b'0'
    ) ENGINE=Memory;

    OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO id, websites;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurance = (SELECT LENGTH(websites)
                                 - LENGTH(REPLACE(websites, bound, ''))
                                 +1);
        SET i=1;
        WHILE i <= occurance DO
          SET splitted_value =
          (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(websites, bound, i),
          LENGTH(SUBSTRING_INDEX(websites, bound, i - 1)) + 1), ',', ''));
          INSERT INTO table2 VALUES ('WEBSITES',splitted_value, ID, 'POTENTIAL', NULL, '0');
          SET i = i + 1;
        END WHILE;
      END LOOP;      
      INSERT INTO crm_object_properties (type, title, post_id, object,contract_id, hidden) SELECT * FROM table2;
    CLOSE cur1;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `free_contacts_and_potentials` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `free_contacts_and_potentials`()
BEGIN
SET @today = CURDATE();
SET @tomorow = CURDATE() + INTERVAL 1 DAY;


UPDATE `crm_contacts`,
    `crm_potentials` 
SET 
    `crm_contacts`.`free_time` = DATE_ADD(`crm_contacts`.`date_action_updated`,
        INTERVAL 1 MONTH)
WHERE
    `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
        AND `crm_contacts`.`status_id` <> 1
        AND `crm_contacts`.`date_action_updated` > `crm_contacts`.`date_created`
        AND `crm_contacts`.`date_action_updated` > `crm_potentials`.`date_action_updated`
        AND `crm_contacts`.`date_action_updated` >= '2016-01-01'
        AND `crm_contacts`.`condition_type` IN ('NORMAL' , 'WARNING')
        AND `crm_contacts`.`free_time` < @tomorow 
        AND `crm_contacts`.`date_appointment` IS NULL;


UPDATE `crm_contacts`,
    `crm_potentials` 
SET 
    `crm_contacts`.`free_time` = DATE_ADD(`crm_contacts`.`date_appointment`,
        INTERVAL 1 DAY)
WHERE
    `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
        AND `crm_contacts`.`status_id` <> 1
        AND `crm_contacts`.`date_action_updated` > `crm_contacts`.`date_created`
        AND `crm_contacts`.`date_action_updated` > `crm_potentials`.`date_action_updated`
        AND `crm_contacts`.`date_appointment` >= '2016-01-01'
        AND `crm_contacts`.`condition_type` IN ('NORMAL' , 'WARNING')
        AND `crm_contacts`.`free_time` < @tomorow 
        AND `crm_contacts`.`date_action_updated` < `crm_contacts`.`date_appointment`
        AND `crm_contacts`.`free_time` < DATE_ADD(`crm_contacts`.`date_appointment`,
        INTERVAL 1 DAY);


UPDATE `crm_contacts`,
    `crm_potentials` 
SET 
    `crm_contacts`.`free_time` = DATE_ADD(`crm_contacts`.`date_action_updated`,
        INTERVAL 4 DAY)
WHERE
    `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
        AND `crm_contacts`.`status_id` <> 1
        AND `crm_contacts`.`date_action_updated` > `crm_contacts`.`date_created`
        AND `crm_contacts`.`date_action_updated` > `crm_potentials`.`date_action_updated`
        AND `crm_contacts`.`date_appointment` >= '2016-01-01'
        AND `crm_contacts`.`condition_type` IN ('NORMAL' , 'WARNING')
        AND `crm_contacts`.`free_time` < @tomorow 
        AND `crm_contacts`.`date_action_updated` >= `crm_contacts`.`date_appointment`
        AND `crm_contacts`.`free_time` < DATE_ADD(`crm_contacts`.`date_action_updated`,
        INTERVAL 4 DAY);
        

UPDATE `crm_contacts`,
    `crm_potentials` 
SET 
    `crm_potentials`.`free_time` = `crm_contacts`.`free_time`
WHERE
    `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
        AND `crm_contacts`.`free_time` > `crm_potentials`.`free_time`
        AND `crm_contacts`.`condition_type` IN ('NORMAL' , 'WARNING')
        AND `crm_potentials`.`condition_type` IN ('NORMAL' , 'WARNING');
        

UPDATE `crm_potentials`, `crm_contacts`
SET `crm_potentials`.`condition_type` = 'FREE',
`crm_contacts`.`condition_type` = 'FREE'
WHERE `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
AND `crm_potentials`.`condition_type` IN ('NORMAL', 'WARNING')
AND `crm_potentials`.`convert_customer` <> '1'
AND (`crm_potentials`.`free_time` IS NULL OR `crm_potentials`.`free_time` < @today)
AND (`crm_potentials`.`sale_id` IS NOT NULL OR `crm_potentials`.`telesale_id` IS NOT NULL)
AND `crm_potentials`.`origin_id` <> 8;

UPDATE `crm_potentials`, `crm_contacts`
SET `crm_contacts`.`condition_type` = 'FREE'
WHERE `crm_potentials`.`contact_id` = `crm_contacts`.`ID`
AND `crm_contacts`.`condition_type` IN ('NORMAL', 'WARNING')
AND `crm_potentials`.`condition_type` NOT IN ('NORMAL', 'WARNING')
AND (`crm_contacts`.`free_time` IS NULL OR `crm_contacts`.`free_time` < @today)
AND (`crm_contacts`.`sale_id` IS NOT NULL OR `crm_contacts`.`telesale_id` IS NOT NULL)
AND `crm_contacts`.`origin_id` <> 8;

UPDATE `crm_contacts`
SET `crm_contacts`.`condition_type` = 'FREE'
WHERE `crm_contacts`.`condition_type` IN ('NORMAL', 'WARNING')
AND `has_normal_potentials` <> '1'
AND (`crm_contacts`.`free_time` IS NULL OR `crm_contacts`.`free_time` < @today)
AND (`crm_contacts`.`sale_id` IS NOT NULL OR `crm_contacts`.`telesale_id` IS NOT NULL)
AND `crm_contacts`.`origin_id` <> 8;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `report_checkpoint` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `report_checkpoint`(IN evaluation_id INT,IN ep1_type_id INT,IN ep2_type_id INT,IN ep3_type_id INT)
BEGIN
SET @evaluation_id = evaluation_id;
SET @ep1_type_id = ep1_type_id;
SET @ep2_type_id = ep2_type_id;
SET @ep3_type_id = ep3_type_id;
SET @ep1_type_ids = (SELECT GROUP_CONCAT(`id`) FROM `personnel_evaluation_types` WHERE `parent_id` = @ep1_type_id);
SET @ep3_type_ids = (SELECT GROUP_CONCAT(`id`) FROM `personnel_evaluation_types` WHERE `parent_id` = @ep3_type_id);
SELECT (@cnt := @cnt + 1) AS rowNumber, `p`.`CODE`, `p`.`NAME`, `d`.`title` AS `department`, `pt`.`title` AS `position`, `jt`.`title` AS `job_title`, 
IF (`eu`.`STATUS` = 1, 'Đã đánh giá', 'Chưa đánh giá') AS `eu_status`, IF (`eu`.`STATUS` = 1, `eu`.`total_point`, NULL) AS `eu_total_point`, 
`uep1`.`eu_total_point_part_1`, `uep2`.`eu_point_part_1_bonus`, `uep3`.`eu_total_point_part_2`,
IF (`em`.`action_user_id` > 0, IF (`em`.`STATUS` = 1, 'Đã đánh giá', 'Chưa đánh giá'), NULL) AS `em_status`, IF (`em`.`STATUS` = 1, `em`.`total_point`, NULL) AS `em_total_point`, 
`mep1`.`em_total_point_part_1`, `mep2`.`em_point_part_1_bonus`, `mep3`.`em_total_point_part_2`, 
IF (`eo`.`action_user_id` > 0, IF(`eo`.`STATUS` = 1, 'Đã đánh giá', 'Chưa đánh giá'), NULL) AS `eo_status`, IF (`eo`.`STATUS` = 1, `eo`.`total_point`, NULL) AS `eo_total_point`,
`oep1`.`eo_total_point_part_1`, `oep2`.`eo_point_part_1_bonus`, `oep3`.`eo_total_point_part_2`
FROM `personnel_evaluation_marks` `eu` 
CROSS JOIN (SELECT @cnt := 0) AS dummy
  INNER JOIN `users` `u` ON `eu`.`user_id` = `u`.`id`
  INNER JOIN `personnels` `p` ON `u`.`personnel_id` = `p`.`id` 
  LEFT JOIN `departments` `d` ON `u`.`department_id` = `d`.`id`
  LEFT JOIN `positions` `pt` ON `p`.`position_id` = `pt`.`id` 
  LEFT JOIN `position_titles` `jt` ON `p`.`job_title` = `jt`.`id`
  LEFT JOIN 
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `eu_total_point_part_1` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep1_type_ids) 
 GROUP BY `mark_id`) 
 AS `uep1` ON `uep1`.`mark_id` = `eu`.`id` AND `eu`.`STATUS` = 1 
  LEFT JOIN 
 (SELECT `mark_id`, `percent` * `mark` / 10 AS `eu_point_part_1_bonus`
 FROM `personnel_evaluation_mark_properties` 
 WHERE `type_id` = @ep2_type_id)
 AS `uep2` ON `uep2`.`mark_id` = `eu`.`id` AND `eu`.`STATUS` = 1 
  LEFT JOIN
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `eu_total_point_part_2` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep3_type_ids) 
 GROUP BY `mark_id`)
 AS `uep3` ON `uep3`.`mark_id` = `eu`.`id` AND `eu`.`STATUS` = 1 
  LEFT JOIN 
   `personnel_evaluation_marks` `em` 
   ON `em`.`evaluation_id` = @evaluation_id  
     AND `em`.`user_id` = `eu`.`user_id` 
     AND `em`.`ACTION` = 'MANAGER' 
     AND FIND_IN_SET(`em`.`action_user_id`, `eu`.`manager_ids`)
  LEFT JOIN 
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `em_total_point_part_1` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep1_type_ids) 
 GROUP BY `mark_id`) 
 AS `mep1` ON `mep1`.`mark_id` = `em`.`id` AND `em`.`STATUS` = 1 
  LEFT JOIN 
 (SELECT `mark_id`, `percent` * `mark` / 10 AS `em_point_part_1_bonus`
 FROM `personnel_evaluation_mark_properties` 
 WHERE `type_id` = @ep2_type_id)
 AS `mep2` ON `mep2`.`mark_id` = `em`.`id` AND `em`.`STATUS` = 1 
  LEFT JOIN
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `em_total_point_part_2` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep3_type_ids) 
 GROUP BY `mark_id`)
 AS `mep3` ON `mep3`.`mark_id` = `em`.`id` AND `em`.`STATUS` = 1 
  LEFT JOIN 
   `personnel_evaluation_marks` `eo` 
   ON `eo`.`evaluation_id` = @evaluation_id  
     AND `eo`.`user_id` = `eu`.`user_id` 
     AND `eo`.`ACTION` = 'OWNER' 
     AND FIND_IN_SET(`eo`.`action_user_id`, `eu`.`owner_ids`)
  LEFT JOIN 
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `eo_total_point_part_1` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep1_type_ids) 
 GROUP BY `mark_id`) 
 AS `oep1` ON `oep1`.`mark_id` = `eo`.`id` AND `eo`.`STATUS` = 1 
  LEFT JOIN 
 (SELECT `mark_id`, `percent` * `mark` / 10 AS `eo_point_part_1_bonus`
 FROM `personnel_evaluation_mark_properties` 
 WHERE `type_id` = @ep2_type_id)
 AS `oep2` ON `oep2`.`mark_id` = `eo`.`id` AND `eo`.`STATUS` = 1 
  LEFT JOIN
 (SELECT `mark_id`, SUM(`percent` * `mark`) / 10 AS `eo_total_point_part_2` 
 FROM `personnel_evaluation_mark_properties` 
 WHERE FIND_IN_SET(`type_id`, @ep3_type_ids) 
 GROUP BY `mark_id`)
 AS `oep3` ON `oep3`.`mark_id` = `eo`.`id` AND `eo`.`STATUS` = 1 
WHERE `eu`.`evaluation_id` = @evaluation_id
  AND `eu`.`ACTION` = 'USER';
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `report_deployment_requires` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `report_deployment_requires`(IN website nvarchar(100), IN sale_name nvarchar(100), IN department_id int, IN sale_id int, IN is_parent int, IN require_status int, IN confirm_status int, IN date_start_from date, IN date_start_to date, IN feedback_type int)
BEGIN
  SELECT

    1 AS rank,
    cc.code,
    cc.ID,
    cc.date_start,
    CASE cc.type WHEN 'NEW' THEN 'Ki moi' WHEN 'RESIGN' THEN 'Gia han' WHEN 'UPGRAdE' THEN 'Nang cap' END AS `type`,
    CASE r.require_status WHEN -1 THEN IF(fc_get_total_number_of_weekdays(cc.date_start, CURDATE()) <= 18, 'Chua trien khai', 'Trien khai cham') WHEN 0 THEN IF(fc_get_total_number_of_weekdays(r.require_date, CURDATE()) <= 15, 'Dang trien khai', 'Trien khai cham') WHEN 1 THEN IF(fc_get_total_number_of_weekdays(r.require_date, r.confirm_date) <= 15, 'Trien khai dung han', 'Trien khai cham') WHEN 3 THEN 'Trien khai sau' ELSE '-' END require_status,
    CASE r.confirm_status WHEN '-1' THEN 'Chua ban giao' WHEN '0' THEN 'Cho xu ly' WHEN '1' THEN 'Da ban giao' WHEN '2' THEN 'Khong xac nhan' ELSE '-' END AS confirm_status,
    IFNULL(cc.website, '-') AS website,
    IFNULL(DATE_FORMAT(DATE(r.require_date), '%d/%m/%Y'), '-') AS require_date,
    IFNULL(DATE_FORMAT(DATE(r.confirm_date), '%d/%m/%Y'), '-') AS confirm_date,
    CASE r.service_rating WHEN '-1' THEN '-' WHEN r.require_date IS NULL THEN '-' ELSE r.service_rating END AS service_rating,
    IFNULL(r.comment, '-') AS `comment`,
    IFNULL(cc.sale_name, '-') AS sale_name,
    IFNULL(cc.sale_support_name, '-') AS sale_suport_name,
    IF(pc1.feedback_by IS NULL, '-', IF(pc1.feedback_by = 'Admin DMS', 'Tự động', 'Khác hàng xác nhận')) feedback_type
  FROM `perform_reports` r

    JOIN (SELECT
        c.ID,
        c.code,
        c.type,
        c.website,
        c.date_start,
        u_sale.name AS sale_name,
        u_sale_support.name AS sale_support_name,
        u_sale.department_id,
        u_sale.parent_id,
        u_sale.ID AS sale_id,
        u_sale.lineage
      FROM `crm_contracts` AS c
        JOIN v_user_info AS u_sale
          ON c.sale_id = u_sale.ID
        JOIN v_user_info AS u_sale_support
          ON c.sale_support_id = u_sale_support.ID
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND c.service_origin_id = 1) AS cc
      ON r.contract_id = cc.ID
    LEFT JOIN (SELECT
        pc.contract_id,
        pc.feedback_by
      FROM perform_confirms pc
      WHERE pc.status = 1
      GROUP BY pc.contract_id
      HAVING MAX(pc.feedback)) pc1
      ON r.contract_id = pc1.contract_id
  WHERE (cc.department_id = IFNULL(department_id, cc.department_id)
  OR CASE WHEN is_parent <> -1 THEN cc.lineage LIKE CONCAT('%', department_id, '%') END)
  AND cc.sale_id = IF(sale_id <> -1, sale_id, cc.sale_id)
  AND cc.website LIKE CONCAT('%', IFNULL(website, ''), '%')
  AND cc.sale_name LIKE CONCAT('%', IFNULL(sale_name, ''), '%')
  AND CASE require_status WHEN -1 THEN r.require_status = -1
          AND fc_get_total_number_of_weekdays(cc.date_start, CURDATE()) <= 18 WHEN 0 THEN r.require_status = 0
          AND fc_get_total_number_of_weekdays(r.require_date, CURDATE()) <= 15 WHEN 1 THEN r.require_status = 1
          AND fc_get_total_number_of_weekdays(r.require_date, r.confirm_date) <= 15 WHEN 2 THEN (r.require_status = 1
          AND fc_get_total_number_of_weekdays(r.require_date, r.confirm_date) > 15)
          OR (r.require_status = 0
          AND fc_get_total_number_of_weekdays(r.require_date, CURDATE()) > 15)
          OR (r.require_status = -1
          AND fc_get_total_number_of_weekdays(cc.date_start, CURDATE()) > 18) WHEN 3 THEN r.require_status = 3 ELSE 1 = 1 END
  AND r.confirm_status = IFNULL(confirm_status, r.confirm_status)
  AND CASE feedback_type WHEN 0 THEN pc1.feedback_by = 'Admin DMS' WHEN 1 THEN pc1.feedback_by <> 'Admin DMS' ELSE 1 = 1 END
  AND (cc.date_start BETWEEN date_start_from AND date_start_to)
  ORDER BY cc.date_start, r.require_date
  ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `report_deployment_requires2` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `report_deployment_requires2`(IN website NVARCHAR(100), IN sale_name NVARCHAR(100), IN department_id INT, IN sale_id INT, IN is_parent INT, IN require_status INT, IN confirm_status INT, IN date_start_from DATE, IN date_start_to DATE)
BEGIN
  SELECT

    1 AS rank,
    c.code,
    c.ID,
    c.date_start,
    r.total_days_deploying,
    r.total_days_deploying_limit,
    CASE c.type WHEN 'NEW' THEN 'Ki moi' WHEN 'RESIGN' THEN 'Gia han' WHEN 'UPGRAdE' THEN 'Nang cap' END AS `type`,
    TOTAL_WEEKDAYS(IFNULL (r.require_date, c.date_start), IFNULL(r.confirm_date, CURDATE())) AS `nod`,
    IF (r.require_date IS NULL, 18, 15),
    IF (TOTAL_WEEKDAYS(IFNULL (r.require_date, c.date_start), IFNULL(r.confirm_date, CURDATE())) > IF (r.require_date IS NULL, 18, 15), 'Trien khai cham', 'trang thai'),
    CASE WHEN (r.require_status = '-1' AND
        c.total_days <= c.total_days_limit) THEN 'Chua trien khai' WHEN (r.require_status = '0' AND
        r.total_days_deploying <= r.total_days_deploying_limit) THEN 'Dang trien khai' WHEN r.require_status = '1' THEN 'Trien khai dung han' WHEN r.require_status = '2' THEN 'Trien khai cham' WHEN (r.require_status = '-1' AND
        c.total_days > c.total_days_limit) THEN 'Trien khai cham' WHEN (r.require_status = '0' AND
        r.total_days_deploying > r.total_days_deploying_limit) THEN 'Trien khai cham' WHEN r.require_status = '3' THEN 'Trien khai sau' ELSE '-' END AS require_status,
    CASE r.confirm_status WHEN '-1' THEN 'Chua ban giao' WHEN '0' THEN 'Cho xu ly' WHEN '1' THEN 'Da ban giao' WHEN '2' THEN 'Khong xac nhan' ELSE '-' END AS confirm_status,
    IFNULL(c.website, '-') AS website,
    IFNULL(DATE_FORMAT(DATE(r.require_date), '%d/%m/%Y'), '-') AS require_date,
    IFNULL(DATE_FORMAT(DATE(r.confirm_date), '%d/%m/%Y'), '-') AS confirm_date,
    CASE r.service_rating WHEN '-1' THEN '-' WHEN r.require_date IS NULL THEN '-' ELSE r.service_rating END AS service_rating,
    IFNULL(r.comment, '-') AS `comment`,
    IFNULL(c.sale_name, '-') AS sale_name,
    IFNULL(c.sale_support_name, '-') AS sale_suport_name
  FROM (SELECT
      r.*,
      DATEDIFF(CURDATE(), r.require_date) total_days_deploying,
      calculate_number_day_limit_implement(r.require_date, 15) total_days_deploying_limit
    FROM `perform_reports` AS r) r

    JOIN (SELECT
        c.ID,
        c.code,
        c.type,
        c.website,
        c.date_start,
        DATEDIFF(CURDATE(), c.date_start) total_days,
        calculate_number_day_limit_implement(c.date_start, 18) total_days_limit,
        u_sale.name AS sale_name,
        u_sale_support.name AS sale_support_name,
        u_sale.department_id,
        u_sale.parent_id,
        u_sale.ID AS sale_id,
        u_sale.lineage
      FROM `crm_contracts` AS c
        JOIN v_user_info AS u_sale
          ON c.sale_id = u_sale.ID
        JOIN v_user_info AS u_sale_support
          ON c.sale_support_id = u_sale_support.ID
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND c.service_origin_id = 1) AS c
      ON r.contract_id = c.ID
  WHERE (c.department_id = IFNULL(department_id, c.department_id)
  OR CASE WHEN is_parent <> -1 THEN c.lineage LIKE CONCAT('%', department_id, '%') END)
  AND c.sale_id = IF(sale_id <> -1, sale_id, c.sale_id)
  AND c.website LIKE CONCAT('%', IFNULL(website, ''), '%')
  AND c.sale_name LIKE CONCAT('%', IFNULL(sale_name, ''), '%')
  AND CASE WHEN require_status IS NULL THEN r.require_status = r.require_status
          OR r.require_status IS NULL WHEN require_status = 2 THEN r.require_status = '2'
          OR (r.require_status = '0'
          AND r.total_days_deploying > r.total_days_deploying_limit)
          OR (r.require_status = '-1'
          AND c.total_days > c.total_days_limit) WHEN require_status = 0 THEN (r.require_status = '0'
          AND r.total_days_deploying <= r.total_days_deploying_limit) WHEN require_status = -1 THEN (r.require_status = '-1'
          AND c.total_days <= c.total_days_limit) ELSE r.require_status = require_status END
  AND CASE WHEN confirm_status IS NULL THEN r.confirm_status = r.confirm_status
          OR r.confirm_status IS NULL ELSE r.confirm_status = confirm_status END
  AND (c.date_start BETWEEN date_start_from AND date_start_to)
  ORDER BY c.date_start, r.require_date;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `report_deploy_for_sales` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `report_deploy_for_sales`(IN date_start_from date, IN date_start_to date, IN sale_name nvarchar(50), IN department_id int, IN sale_rating int, IN time_peform int, IN is_parent int, IN sale_id_in int)
    DETERMINISTIC
BEGIN
  SET @sale_rating = sale_rating;
  SET @time_peform = time_peform;
  SET @sale_id = sale_id_in;

  SELECT
    1 rank,
    v_user_info.department,
    v_user_info.name,
    ct_sale_id.total_contract,
    ct_sale_id.sale_id user_id,
    IFNULL(not_yet_deploy_befor.count, 0) AS `not_yet_deploy_befor`,
    IFNULL(not_yet_deploy.count, 0) AS `not_yet_deploy`,
    IFNULL(deploying.count, 0) AS `deploying`,
    IFNULL(done.count, 0)  AS `done`,
    IFNULL(slow_deploy.count, 0) AS `slow_deploy`,
    CONVERT(IFNULL(sale_rating.sale_rating, '0.0/0'), char(50)) AS sale_rating,
    IFNULL(service_rating.time_perform, 0) AS time_perform,
    sale_rating.point_rating
  FROM (SELECT
      sale_id,
      SUM(`count`) AS total_contract
    FROM (SELECT
        c.sale_id,
        r.require_status,
        COUNT(c.ID) AS count
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id,
               r.require_status) AS ct
    GROUP BY sale_id) AS ct_sale_id

    LEFT JOIN (SELECT
        c.sale_id,
        COUNT(c.ID) AS COUNT
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND c.date_start < date_start_from
      AND r.require_status IN (-1, 0, 3)
      GROUP BY c.sale_id) AS not_yet_deploy_befor
      ON ct_sale_id.sale_id = not_yet_deploy_befor.sale_id

    LEFT JOIN (SELECT
        c.sale_id,
        COUNT(c.ID) AS count
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND ((r.require_status = -1
      AND fc_get_total_number_of_weekdays(c.date_start, CURDATE()) <= 18)
      OR r.require_status = 3)
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id) AS not_yet_deploy
      ON ct_sale_id.sale_id = not_yet_deploy.sale_id


    LEFT JOIN (SELECT
        c.sale_id,
        COUNT(c.ID) AS count
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND r.require_status = 0
      AND fc_get_total_number_of_weekdays(r.require_date, CURDATE()) <= 15
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id) AS deploying
      ON ct_sale_id.sale_id = deploying.sale_id


    LEFT JOIN (SELECT
        c.sale_id,
        COUNT(c.ID) AS count
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND r.require_status = 1
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id) AS done
      ON ct_sale_id.sale_id = done.sale_id

    LEFT JOIN (SELECT
        c.sale_id,
        COUNT(c.ID) AS count
      FROM crm_contracts c
        INNER JOIN perform_reports r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND ((r.require_status = 1
      AND fc_get_total_number_of_weekdays(r.require_date, r.confirm_date) > 15)
      OR (r.require_status = 0
      AND fc_get_total_number_of_weekdays(r.require_date, CURDATE()) > 15)
      OR (r.require_status = -1
      AND fc_get_total_number_of_weekdays(c.date_start, CURDATE()) > 18))
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id) AS slow_deploy
      ON ct_sale_id.sale_id = slow_deploy.sale_id


    LEFT JOIN (SELECT
        c.sale_id,
        CONCAT(IFNULL(ROUND(ROUND(SUM(r.`support_rating`), 1) / count_perform_review_by_sale(c.`sale_id`, date_start_from, date_start_to), 1), 0.0), '/', count_perform_review_by_sale(c.`sale_id`, date_start_from, date_start_to)) AS sale_rating,
        IFNULL(ROUND(ROUND(SUM(r.`support_rating`), 1) / count_perform_review_by_sale(c.`sale_id`, date_start_from, date_start_to), 1), 0.0) AS point_rating
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND (r.`confirm_status` = 1
      AND (r.`comment` IS NOT NULL
      OR r.`service_rating` <> -1
      OR r.`support_rating` <> -1))
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.sale_id) AS sale_rating
      ON ct_sale_id.sale_id = sale_rating.sale_id


    LEFT JOIN (SELECT
        c.sale_id,
        ROUND(SUM(DATEDIFF(r.`confirm_date`, r.`require_date`)) / COUNT(c.`ID`), 1) AS time_perform
      FROM crm_contracts AS c
        INNER JOIN perform_reports AS r
          ON c.ID = r.contract_id
      WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
      AND r.`confirm_status` = 1
      AND c.date_start BETWEEN date_start_from AND date_start_to
      GROUP BY c.`sale_id`) AS service_rating
      ON ct_sale_id.sale_id = service_rating.sale_id

    LEFT JOIN v_user_info
      ON ct_sale_id.sale_id = v_user_info.ID

  WHERE v_user_info.name LIKE CONCAT('%', IFNULL(sale_name, ''), '%')
  AND (v_user_info.department_id = IFNULL(department_id, v_user_info.department_id)
  OR CASE WHEN is_parent <> -1 THEN v_user_info.lineage LIKE CONCAT('%', department_id, '%') END)
  AND (@sale_id = -1
  OR ct_sale_id.sale_id = @sale_id)
  AND CASE WHEN @sale_rating = 1 THEN sale_rating.point_rating < 3
          OR sale_rating.point_rating IS NULL WHEN @sale_rating = 2 THEN sale_rating.point_rating BETWEEN 3 AND 4 WHEN @sale_rating = 3 THEN sale_rating.point_rating BETWEEN 4 AND 5 ELSE sale_rating.point_rating = sale_rating.point_rating
        OR sale_rating.point_rating IS NULL END
  AND CASE WHEN @time_peform = 1 THEN service_rating.time_perform < 5
          OR service_rating.time_perform IS NULL WHEN @time_peform = 2 THEN service_rating.time_perform BETWEEN 5 AND 10 WHEN @time_peform = 3 THEN service_rating.time_perform > 10 ELSE service_rating.time_perform = service_rating.time_perform
        OR service_rating.time_perform IS NULL END
  ORDER BY CONCAT(v_user_info.`lineage`, v_user_info.`department_id`);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `report_perform_of_sale_support` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `report_perform_of_sale_support`(IN date_start_from date, IN date_start_to date, IN sale_name nvarchar(50), IN department_id int, IN sale_id_in int, IN is_parent int, IN time_peform int)
BEGIN
  SET @time_peform = time_peform;
  SELECT
    1 rank,
    report.department,
    report.department_id,
    report.lineage,
    report.name,
    report.sale_support_id,
    report.total,
    report.st1,
    report.st2,
    report.st3,
    report.st4,
    report.time_perform
  FROM (SELECT

      v_user_info.department,
      v_user_info.name,
      v_user_info.department_id,
      v_user_info.lineage,
      total.sale_support_id,
      IFNULL(total.total_contract, 0) AS total,
      IFNULL(tmp1.`count`, 0) AS st1,
      IFNULL(tmp2.`count`, 0) AS st2,
      IFNULL(tmp3.`count`, 0) AS st3,
      IFNULL(tmp4.`count`, 0) AS st4,
      ROUND(IFNULL(calculate_duration_of_sale_support(`v_user_info`.`ID`, IFNULL(tmp2.`count`, 0) + IFNULL(tmp3.`count`, 0) + IFNULL(tmp4.`count`, 0), date_start_from, date_start_to), 0), 1) AS time_perform

    FROM (SELECT
        sale_support_id,
        SUM(`count`) AS total_contract
      FROM (SELECT
          c.`sale_support_id`,
          c.`site_status`,
          COUNT(c.`ID`) AS `count`
        FROM `crm_contracts` AS c
          INNER JOIN (SELECT
              contract_id
            FROM `crm_performs`
            GROUP BY contract_id) AS p
            ON c.`ID` = p.`contract_id`
          INNER JOIN (SELECT
              pr.contract_id
            FROM perform_reports pr) AS pr
            ON c.ID = pr.contract_id
        WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
        AND c.`sale_support_id` IS NOT NULL
        AND (c.date_start BETWEEN date_start_from AND date_start_to)
        GROUP BY c.`sale_support_id`,
                 c.`site_status`) AS total
      GROUP BY `sale_support_id`) AS total

      LEFT JOIN (SELECT
          *
        FROM (SELECT
            c.`sale_support_id`,
            c.`site_status`,
            COUNT(c.`ID`) AS `count`
          FROM `crm_contracts` AS c
            INNER JOIN (SELECT
                contract_id
              FROM `crm_performs`
              GROUP BY contract_id) AS p
              ON c.`ID` = p.`contract_id`
            INNER JOIN (SELECT
                pr.contract_id
              FROM perform_reports pr) AS pr
              ON c.ID = pr.contract_id
          WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
          AND c.`sale_support_id` IS NOT NULL
          AND (c.date_start BETWEEN date_start_from AND date_start_to)
          GROUP BY c.`sale_support_id`,
                   c.`site_status`) AS tmp1
        WHERE tmp1.site_status = '-1') AS tmp1
        ON total.sale_support_id = tmp1.sale_support_id
      LEFT JOIN (SELECT
          *
        FROM (SELECT
            c.`sale_support_id`,
            c.`site_status`,
            COUNT(c.`ID`) AS `count`
          FROM `crm_contracts` AS c
            INNER JOIN (SELECT
                contract_id
              FROM `crm_performs`
              GROUP BY contract_id) AS p
              ON c.`ID` = p.`contract_id`
            INNER JOIN (SELECT
                pr.contract_id
              FROM perform_reports pr) AS pr
              ON c.ID = pr.contract_id
          WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
          AND c.`sale_support_id` IS NOT NULL
          AND (c.date_start BETWEEN date_start_from AND date_start_to)
          GROUP BY c.`sale_support_id`,
                   c.`site_status`) AS tmp1
        WHERE tmp1.site_status = '0') AS tmp2
        ON total.sale_support_id = tmp2.sale_support_id
      LEFT JOIN (SELECT
          *
        FROM (SELECT
            c.`sale_support_id`,
            c.`site_status`,
            COUNT(c.`ID`) AS `count`
          FROM `crm_contracts` AS c
            INNER JOIN (SELECT
                contract_id
              FROM `crm_performs`
              GROUP BY contract_id) AS p
              ON c.`ID` = p.`contract_id`
            INNER JOIN (SELECT
                pr.contract_id
              FROM perform_reports pr) AS pr
              ON c.ID = pr.contract_id
          WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
          AND c.`sale_support_id` IS NOT NULL
          AND (c.date_start BETWEEN date_start_from AND date_start_to)
          GROUP BY c.`sale_support_id`,
                   c.`site_status`) AS tmp1
        WHERE tmp1.site_status = '1') AS tmp3
        ON total.sale_support_id = tmp3.sale_support_id
      LEFT JOIN (SELECT
          *
        FROM (SELECT
            c.`sale_support_id`,
            c.`site_status`,
            COUNT(c.`ID`) AS `count`
          FROM `crm_contracts` AS c
            INNER JOIN (SELECT
                contract_id
              FROM `crm_performs`
              GROUP BY contract_id) AS p
              ON c.`ID` = p.`contract_id`
            INNER JOIN (SELECT
                pr.contract_id
              FROM perform_reports pr) AS pr
              ON c.ID = pr.contract_id
          WHERE c.condition_type NOT IN ('LIQUIDATION', 'CANCEL')
          AND c.`sale_support_id` IS NOT NULL
          AND (c.date_start BETWEEN date_start_from AND date_start_to)
          GROUP BY c.`sale_support_id`,
                   c.`site_status`) AS tmp1
        WHERE tmp1.site_status = '2') AS tmp4
        ON total.sale_support_id = tmp4.sale_support_id
      JOIN v_user_info
        ON total.sale_support_id = v_user_info.ID) AS report
  WHERE report.name LIKE CONCAT(IFNULL(sale_name, ""), "%")
  AND (report.department_id = IFNULL(department_id, report.department_id)
  OR CASE WHEN is_parent <> -1 THEN report.lineage LIKE CONCAT('%', department_id, '%') END)
  AND (sale_id_in = -1
  OR report.sale_support_id = sale_id_in)
  AND CASE WHEN @time_peform = 1 THEN report.time_perform < 5 WHEN @time_peform = 2 THEN report.time_perform BETWEEN 5 AND 10 WHEN @time_peform = 3 THEN report.time_perform > 10 ELSE report.time_perform = report.time_perform END
  ORDER BY CONCAT(report.`lineage`, report.`department_id`);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rp_mkt_metrics_contracts` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `rp_mkt_metrics_contracts`(IN P_first_source varchar(50), IN p_service_code varchar(20), IN p_service_package_name text, IN p_date_from date, IN p_date_to date)
BEGIN

  SELECT
    1 rank,
    com.ID,
    com.contract_id,
    com.website,
    com.type_contract,
    com.first_source_type,
    com.last_source_type,
    com.cl_created,
    com.fi_date_approver
  FROM (SELECT

      +1 rank,
      cl1.ID,
      cc.ID contract_id,
      cc.website,
      CONCAT(service.title_service, ' / ', service.title_Service_package) type_contract,
      fc_get_reference_name(IFNULL(cl1.first_source_type, IFNULL(source_type, cl1.last_source_type)), cl1.source_reference) first_source_type,
      fc_get_reference_name(cl1.last_source_type, cl1.source_reference) last_source_type,
      cl1.created cl_created,
      f_internals.date_approver fi_date_approver,
      service.code_service,
      service.title_Service_package

    FROM crm_contracts cc
      INNER JOIN (SELECT
          fi.contract_id,
          fi.date_approver
        FROM finance_internals fi
        WHERE fi.status = '1'
        AND fi.rollback_status = '0'
        GROUP BY fi.contract_id
        HAVING MIN(fi.date_approver)) f_internals
        ON cc.ID = f_internals.contract_id


      INNER JOIN (SELECT
          ccs.contract_id,
          ccs.code code_service,
          ccs.title title_service,
          ccsp.title title_Service_package
        FROM crm_contract_services ccs
          INNER JOIN crm_contract_service_packages ccsp
            ON ccs.ID = ccsp.service_id) service
        ON cc.ID = service.contract_id



      INNER JOIN (SELECT
          cl.ID,
          cl.created,
          cl.status cl_status,
          cl.source_type last_source_type,
          first_source.value first_source_type,
          cl.website cl_website,
          trial.website,
          trial.source_reference,
          trial.source_type
        FROM crm_leads cl
          LEFT JOIN (SELECT
              fl.row_id,
              fl.value
            FROM field_logs fl
            WHERE fl.`table` = 'crm_leads'
            AND fl.`column` = 'source_type'
            GROUP BY fl.row_id
            HAVING MIN(fl.created)) first_source
            ON first_source.row_id = cl.ID
          LEFT JOIN (SELECT
              *
            FROM v_trial_service_sources) trial
            ON cl.ID = trial.lead_id
        WHERE cl.status = 'WON') cl1
        ON cc.potential_id = cl1.id
    WHERE
     IF(cl1.source_type = 2
        AND cl1.website is NOT NULL, cc.website = cl1.website, 1 = 1)
    ) com

  WHERE
    com.code_service = CASE WHEN p_service_code <> 'ALL' THEN p_service_code ELSE com.code_service END
  AND com.title_Service_package = CASE WHEN p_service_package_name <> 'ALL' THEN p_service_package_name ELSE com.title_Service_package END
   
  and DATE(com.fi_date_approver) BETWEEN p_date_from AND p_date_to
    HAVING com.first_source_type LIKE IF(P_first_source = 'ALL', com.first_source_type, P_first_source)
  ORDER BY com.fi_date_approver
  ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rp_mkt_rate_convert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `rp_mkt_rate_convert`(IN p_date_created DATE, IN p_service_id INT, IN p_source_ref VARCHAR(50))
BEGIN
	SET @service_id = p_service_id;
	SET @reference = p_source_ref;
	SET @created_from = DATE_FORMAT(p_date_created, '%Y-%m-01 00:00:00');
	SET @created_to = DATE_FORMAT(LAST_DAY(p_date_created), '%Y-%m-%d 23:59:59');
	SET @service_name = (SELECT
			cps.title
		FROM crm_product_services cps
		WHERE cps.ID = @service_id);

	SELECT
		1 AS `rank`,
		a.`reference`,
		fc_remove_sign(a.`reference`) code_ref,
		COUNT(a.`lead_id`) AS `leads`,
		COUNT(IF(a.`type` = 'OPPORTUNITY', a.`lead_id`, NULL)) AS `opps`,
		COUNT(IF(a.`contract_id` IS NOT NULL AND a.`total_months` = 0, a.`lead_id`, NULL)) AS `x`,
		COUNT(IF(a.`contract_id` IS NOT NULL AND a.`total_months` = 1, a.`lead_id`, NULL)) AS `x1`,
		COUNT(IF(a.`contract_id` IS NOT NULL AND a.`total_months` = 2, a.`lead_id`, NULL)) AS `x2`,
		COUNT(IF(a.`contract_id` IS NOT NULL AND a.`total_months` = 3, a.`lead_id`, NULL)) AS `x3`,
		COUNT(IF(a.`contract_id` IS NOT NULL AND a.`total_months` = 4, a.`lead_id`, NULL)) AS `x4`
	FROM (SELECT
			`fc_get_reference_name`(IFNULL(fl.`source_type`, vs.`source_type`), vs.`source_reference`, @service_name) AS `reference`,
			vs.`lead_id`,
			l.`type`,
			c.`ID` AS `contract_id`,
			IF(c.`ID` IS NOT NULL, `fc_get_total_months`(l.`created`, c.`date_created`), NULL) AS `total_months`
		FROM `v_trial_service_sources` vs
			LEFT JOIN (SELECT
					fl.row_id `lead_id`,
					fl.value `source_type`
				FROM field_logs fl
					INNER JOIN (SELECT
							fl1.row_id,
							MIN(fl1.created) created
						FROM field_logs fl1
						WHERE fl1.`table` = 'crm_leads'
						AND fl1.`column` = 'source_type'
						GROUP BY fl1.row_id) fl_min
						ON fl.row_id = fl_min.row_id
						AND fl.created = fl_min.created) fl
				ON vs.`lead_id` = fl.`lead_id`
			INNER JOIN `crm_leads` l
				ON l.`ID` = vs.`lead_id`
			LEFT JOIN `crm_contracts` c
				ON l.`ID` = c.`potential_id`
				AND c.`total_price_internal` > 0
				AND c.`website` = vs.`website`
		WHERE vs.`lead_id` IS NOT NULL
		AND (fl.`source_type` = 2
		OR (fl.`source_type` IS NULL
		AND vs.`source_type` = 2))
		AND l.`service_id` = IF(@service_id IS NULL
		OR @service_id = -1, l.`service_id`, @service_id)
		AND l.`created` BETWEEN @created_from AND @created_to
		AND vs.`created` BETWEEN @created_from AND @created_to

		UNION

		SELECT
			`fc_get_reference_name`(l.`source_type`, NULL, @service_name) AS `reference`,
			l.`ID` AS `lead_id`,
			l.`type`,
			c.`ID` AS `contract_id`,
			IF(c.`ID` IS NOT NULL, `fc_get_total_months`(l.`created`, c.`date_created`), NULL) AS `total_months`
		FROM `crm_leads` l
			LEFT JOIN `crm_contracts` c
				ON l.`ID` = c.`potential_id`
				AND c.`total_price_internal` > 0
		WHERE l.`source_type` != 2
		AND l.`service_id` = IF(@service_id IS NULL
		OR @service_id = -1, l.`service_id`, @service_id)
		AND l.`created` BETWEEN @created_from AND @created_to) a
	GROUP BY a.`reference`
	HAVING a.`reference` = IFNULL(@reference, a.`reference`);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rp_mkt_rate_convert_detail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `rp_mkt_rate_convert_detail`(IN p_lead_created DATE, IN p_months INT, IN p_reference VARCHAR(100), IN p_service_id INT)
BEGIN
  SET @reference = p_reference;
  SET @lead_created_from = DATE_FORMAT(p_lead_created, '%Y-%m-01 00:00:00');
  SET @lead_created_to = DATE_FORMAT(LAST_DAY(p_lead_created), '%Y-%m-%d 23:59:59');
  SET @contract_created = DATE_ADD(p_lead_created, INTERVAL p_months MONTH);
  SET @contract_created_from = DATE_FORMAT(@contract_created, '%Y-%m-01 00:00:00');
  SET @contract_created_to = DATE_FORMAT(LAST_DAY(@contract_created), '%Y-%m-%d 23:59:59');	
  SET @service_id = p_service_id;
  SET @service_name = (SELECT cps.title FROM crm_product_services cps WHERE cps.ID = @service_id);

  SELECT 1 AS `rank`, a.*
  FROM (
    SELECT
      c.website,
      vs.`source_reference`,
      vs.`created` AS `lead_created`,
      IF(c.`ID` IS NOT NULL, c.`created`, NULL) AS `contract_created`,
      IF(c.`ID` IS NOT NULL, c.`CODE`, NULL) AS `CODE`,
      IF(c.`ID` IS NOT NULL, c.`package`, NULL) AS `package`,
      `fc_get_reference_name`(IFNULL(fl.`source_type`, vs.`source_type`), vs.`source_reference`, @service_name) AS `reference`
    FROM 
      `v_trial_service_sources` vs
      LEFT JOIN (SELECT
  fl.row_id `lead_id` ,
  fl.value `source_type`
FROM field_logs fl
  INNER JOIN (SELECT
      fl1.row_id,
      MIN(fl1.created) created
    FROM field_logs fl1
    WHERE fl1.`TABLE` = 'crm_leads'
    AND fl1.`COLUMN` = 'source_type'
    GROUP BY fl1.row_id) fl_min
    ON fl.row_id = fl_min.row_id
    AND fl.created = fl_min.created) fl ON vs.`lead_id` = fl.`lead_id`
      INNER JOIN `crm_leads` l ON l.`ID` = vs.`lead_id`
      LEFT JOIN `v_contracts_had_receipts` c ON c.`opportunity_id` = l.`id` AND c.`website` = vs.`website`
    WHERE 
      (vs.`lead_id` IS NOT NULL)
      AND l.service_id = IF(@service_id IS NULL OR @service_id = -1, l.service_id,@service_id)
      AND (fl.`source_type` = 2 OR (fl.`source_type` IS NULL AND vs.`source_type` = 2))
      AND (l.`created` BETWEEN @lead_created_from AND @lead_created_to OR vs.`created` BETWEEN @lead_created_from AND @lead_created_to)
      AND c.`created` BETWEEN @contract_created_from AND @contract_created_to

    UNION

    SELECT 
    	c.website,
    	NULL AS `source_reference`,
    	l.`created` AS `lead_created`,
    	IF(c.`ID` IS NOT NULL, c.`created`, NULL) AS `contract_created`,
      IF(c.`ID` IS NOT NULL, c.`CODE`, NULL) AS `CODE`,
      IF(c.`ID` IS NOT NULL, c.`package`, NULL) AS `package`,
    	`fc_get_reference_name` (l.`source_type`, NULL, @service_name) AS `reference` 
    FROM
    	`crm_leads` l 
    	LEFT JOIN `v_contracts_had_receipts` c ON c.`opportunity_id` = l.`id`
    WHERE 
    	l.`source_type` != 2 AND l.service_id = IF(@service_id IS NULL OR @service_id = -1, l.service_id,@service_id)
      AND l.`created` BETWEEN @lead_created_from AND @lead_created_to
      AND c.`created` BETWEEN @contract_created_from AND @contract_created_to
  ) a
  WHERE a.`reference` = IFNULL(@reference, a.`reference`);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rp_work_progress` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `rp_work_progress`(IN _task_name VARCHAR(255), IN _subtask_type INT, IN _subtask_status VARCHAR(50), IN _created_name VARCHAR(255), IN _assign_name VARCHAR(255), IN _date_created_from DATE, IN _date_created_to DATE, IN _date_start_plan_from DATE, IN _date_start_plan_to DATE, IN _date_end_plan_from DATE, IN _date_end_plan_to DATE, IN _date_start_from DATE, IN _date_start_to DATE, IN _date_end_from DATE, IN _date_end_to DATE, IN _slow_deadline INT)
    DETERMINISTIC
BEGIN

  DECLARE _assign_id int;

  
  SET _assign_id = (SELECT
      ID
    FROM v_user_info
    WHERE username =_assign_name);

  SELECT
    1 row,
    wt.ID,
    wt.title sub_task_name,
    wt.parent_id task_id,
    wt1.title task_name,
    vui.name created_name,
    fc_get_fullname_by_userIds(wt.assign_ids) assign_name,
    wt.start_plan,
    wt.end_plan,
    IFNULL(wt.start, '-') wt_start,
    IFNULL(wt.end, '-') wt_end,
    IFNULL(DATEDIFF(wt.end, wt.start) + 1, 0) days,
    CASE wt.status WHEN 'DOING' THEN 'Dang xu ly' WHEN 'PENDING' THEN 'Cho xu ly' WHEN 'COMPLETED' THEN 'Hoan thanh' ELSE '-' END wt_status,
    IF(DATEDIFF(wt.end, wt.end_plan) <= 0 OR wt.end IS NULL OR wt.end_plan IS NULL, '-', DATEDIFF(wt.end, wt.end_plan)) slow_deadline
  FROM work_tasks wt
    LEFT JOIN work_tasks wt1
      ON wt.parent_id = wt1.ID
    INNER JOIN v_user_info vui
      ON wt.created_by_id = vui.ID
    INNER JOIN (SELECT
        MAX(wt2.date_created) max_date_created,
        MIN(wt2.date_created) min_date_created,
        MAX(wt2.start_plan) max_start_plan,
        MIN(wt2.start_plan) min_start_plan,
        MAX(wt2.end_plan) max_end_plan,
        MIN(wt2.end_plan) min_end_plan,
        MAX(wt2.start) max_start,
        MIN(wt2.start) min_start,
        MAX(wt2.end) max_end,
        MIN(wt2.end) min_end
      FROM work_tasks wt2) mm
  WHERE wt.type = 'SUBTASK'
  AND wt1.title LIKE CONCAT('%', IFNULL(_task_name, ''), '%')
  AND wt.sub_task_type_id = IFNULL(_subtask_type, wt.sub_task_type_id)
  AND wt.status = IFNULL(_subtask_status, wt.status)
  AND vui.username =IFNULL(_created_name,vui.username)
  AND IF(_slow_deadline IS NULL,1=1, DATEDIFF(wt.end, wt.end_plan) > _slow_deadline)
  AND IF(_assign_id IS NULL,1=1,FIND_IN_SET(_assign_id,wt.assign_ids))
  AND wt.date_created BETWEEN COALESCE(_date_created_from, mm.min_date_created) AND COALESCE(_date_created_to, mm.max_date_created)
  AND wt.start_plan BETWEEN COALESCE(_date_start_plan_from, mm.min_start_plan) AND COALESCE(_date_start_plan_to, mm.max_start_plan)
  AND wt.end_plan BETWEEN COALESCE(_date_end_plan_from, mm.min_end_plan) AND COALESCE(_date_end_plan_to, mm.max_end_plan)

















  ORDER BY wt.last_action_date DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_cursor_demo` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `sp_cursor_demo`()
BEGIN
  DECLARE package_id INT;
  DECLARE done BIT DEFAULT FALSE;
  DECLARE cursor_package CURSOR FOR 
    SELECT `a`.`ID` FROM `crm_product_service_packages` `a`;
  DECLARE CONTINUE HANDLER FOR 
    NOT FOUND SET done = TRUE;

  OPEN cursor_package;

  read_loop: 
    LOOP
      FETCH cursor_package INTO package_id;
      IF done THEN
        LEAVE read_loop;
      END IF;

      INSERT INTO crm_value_added_packages (
        `package_id`, `value_added_id`, `price`, `dynamic_price`, `created_by_id`, `date_created`, `updated_by_id`, `date_updated`
      )
      VALUES (
        package_id, 149, 0, 0, 1, NOW(), 1, NOW()
      );

    END LOOP;

  CLOSE cursor_package;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test_rep` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_rep`()
BEGIN
    DECLARE counter INT DEFAULT 0;

    WHILE (counter < 10000) DO
        INSERT INTO test_rep (id, note) VALUES (counter, 'Test........ ');

        SET counter := counter +1;
    END WHILE;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `updateIncome4SaleSupport` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `updateIncome4SaleSupport`(
	IN _contract_code varchar(255)
)
BEGIN
	declare _sale_id int;
	declare _user_id int;
	declare _personnel_id int;
	declare _feedback datetime;
	DECLARE _done INT DEFAULT FALSE;
	DECLARE _internal_id INT;
	DECLARE _cursor CURSOR FOR 
		select fi.`ID` 
		from `finance_internals` fi inner join `crm_contracts` c on c.`ID` = fi.`contract_id`
		WHERE c.`code` = _contract_code;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
	
	set _sale_id = (select c.`sale_id` from `crm_contracts` c where c.`code` = _contract_code);
	SET _user_id = (SELECT c.`sale_support_id` FROM `crm_contracts` c WHERE c.`code` = _contract_code);
	SET _personnel_id = (SELECT u.`personnel_id` FROM `users` u WHERE u.`ID` = _user_id);		
	set _feedback = (
		select pc.`feedback` from `perform_confirms` pc INNER JOIN `crm_contracts` c on c.`ID` = pc.`contract_id`
		where c.`code` = _contract_code AND pc.`status` = 1
	);
	
	OPEN _cursor;
	_loop: LOOP
		fetch _cursor INTO _internal_id;
		IF _done THEN
		  LEAVE _loop;
		END IF;
		
		if exists (
			select a.`ID` from `crm_income_get_services` a 
			where a.`type` = 'INTERNAL' AND a.`post_id` = _internal_id AND a.`user_id` = _user_id
		) THEN
			update `crm_income_get_services` 
			set `date` = _feedback
			where `type` = 'INTERNAL' AND `post_id` = _internal_id AND `user_id` = _user_id;
		else
			insert into `crm_income_get_services` (
				`user_id`, `personnel_id`, `package_id`, `service_id`, `date`,
				`date_approver`, `date_since`, `money`, `percent`, `post_id`, `commision`,
				`money_commision`, `type`, `company_id`, `set_id`, `target_person_id`, `target_money`,
				`target_date_from`, `target_date_to`, `contract_id`, `contract_type`, `contract_type_upgrade`
			)
				SELECT 
					_user_id, _personnel_id, a.`package_id`, a.`service_id`, _feedback,
					a.`date_approver`, a.`date_since`, SUM(a.`money`) AS `money`, 100 as `percent`, _internal_id, 0 as `commision`,
					0 as `money_commision`, 'INTERNAL' as `type`, a.`company_id`, a.`set_id`, a.`target_person_id`, a.`target_money`,
					a.`target_date_from`, a.`target_date_to`, a.`contract_id`, a.`contract_type`, a.`contract_type_upgrade`
				FROM `crm_income_get_services` a
				WHERE a.`user_id` = _sale_id AND a.`type` = 'INTERNAL' AND a.`post_id` = _internal_id;
		end if;
	END LOOP;
	CLOSE _cursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_recursiveUpdateDepartmentTree` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `_recursiveUpdateDepartmentTree`()
BEGIN
  UPDATE departments d SET d.lineage = '';
  UPDATE departments d SET d.lineage = '/' WHERE d.parent_id = 0;

  while_loop_label:
  WHILE EXISTS (SELECT d.ID FROM departments d WHERE d.lineage = '') DO
    CALL _updateDepartmentTree();
  END WHILE while_loop_label;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_updateDepartmentTree` */;
DELIMITER ;;
CREATE DEFINER=`tientq`@`%` PROCEDURE `_updateDepartmentTree`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE parent_lineage VARCHAR(255);
  DECLARE department_id, parent_id INT;

  DECLARE d_cursor CURSOR FOR SELECT `d`.`ID`, `d`.`parent_id` FROM `departments` `d` WHERE `d`.`lineage` = '';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN d_cursor;

  cursor_loop_label:
  LOOP
    FETCH d_cursor INTO department_id, parent_id;

    IF done = 1 THEN
      LEAVE cursor_loop_label;
    END IF;

    SET parent_lineage = (
      SELECT `d`.`lineage` FROM `departments` `d` WHERE `d`.`ID` = parent_id
    );

    IF parent_lineage != '' THEN
      UPDATE `departments` `d`
      SET `d`.`lineage` = CONCAT(parent_lineage, CONCAT(parent_id, '/'))
      WHERE `d`.`ID` = department_id;
    END IF;

  END LOOP cursor_loop_label;

  CLOSE d_cursor;
  
END ;;
DELIMITER ;

--
-- Final view structure for view `v_contracts_had_receipts`
--

/*!50001 DROP TABLE IF EXISTS `v_contracts_had_receipts`*/;
/*!50001 DROP VIEW IF EXISTS `v_contracts_had_receipts`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`tientq`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_contracts_had_receipts` AS select `c`.`ID` AS `id`,`c`.`code` AS `code`,`c`.`date_created` AS `created`,`csp`.`title` AS `package`,`c`.`potential_id` AS `opportunity_id`,`c`.`website` AS `website` from (`crm_contracts` `c` join `crm_contract_service_packages` `csp` on((`c`.`ID` = `csp`.`contract_id`))) where (`c`.`total_price_internal` > 0) */;

--
-- Final view structure for view `v_field_log_sources`
--

/*!50001 DROP TABLE IF EXISTS `v_field_log_sources`*/;
/*!50001 DROP VIEW IF EXISTS `v_field_log_sources`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`tientq`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_field_log_sources` AS select `fl`.`row_id` AS `lead_id`,`fl`.`value` AS `source_type` from `field_logs` `fl` where ((`fl`.`table` = 'crm_leads') and (`fl`.`column` = 'source_type')) group by `fl`.`row_id` having min(`fl`.`created`) */;

--
-- Final view structure for view `v_sources_details`
--

/*!50001 DROP TABLE IF EXISTS `v_sources_details`*/;
/*!50001 DROP VIEW IF EXISTS `v_sources_details`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`tientq`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_sources_details` AS select '1' AS `source_ID`,'Bizweb' AS `code`,'Bizweb' AS `title` union select '1' AS `source_ID`,'Sieuweb' AS `code`,'Sieuweb' AS `title` union select '1' AS `source_ID`,'Blogforum' AS `code`,'Blogforum' AS `title` union select '1' AS `source_ID`,'CSKH' AS `code`,'CSKH' AS `title` union select '1' AS `source_ID`,'Affiliate doi tac' AS `code`,'Affiliate đối tác' AS `title` union select '2' AS `source_ID`,'Kinh doanh tu nhap' AS `code`,'Kinh doanh tự nhập' AS `title` union select '2' AS `source_ID`,'Telesale nhap' AS `code`,'Telesale nhập' AS `title` union select '2' AS `source_ID`,'Link kinh doanh' AS `code`,'Link kinh doanh' AS `title` union select '3' AS `source_ID`,'GDN' AS `code`,'GDN' AS `title` union select '3' AS `source_ID`,'Remarketing' AS `code`,'Remarketing' AS `title` union select '3' AS `source_ID`,'Facebook' AS `code`,'Facebook' AS `title` union select '3' AS `source_ID`,'Search thuong hieu' AS `code`,'Search thương hiệu' AS `title` union select '3' AS `source_ID`,'Search nhu cau' AS `code`,'Search nhu cầu' AS `title` union select '3' AS `source_ID`,'Search landing page' AS `code`,'Search landing page' AS `title` union select '0' AS `source_ID`,NULL AS `code`,'ALL' AS `title` */;

--
-- Final view structure for view `v_trial_service_sources`
--

/*!50001 DROP TABLE IF EXISTS `v_trial_service_sources`*/;
/*!50001 DROP VIEW IF EXISTS `v_trial_service_sources`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`tientq`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_trial_service_sources` AS select `ts`.`lead_id` AS `lead_id`,`ts`.`website` AS `website`,`ts`.`created` AS `created`,`tsm1`.`meta_value` AS `source_type`,`tsm2`.`meta_value` AS `source_reference` from ((`crm_trial_services` `ts` join `crm_trial_service_meta` `tsm1` on(((`ts`.`id` = `tsm1`.`trial_service_id`) and (`tsm1`.`meta_key` = 'source_type')))) left join `crm_trial_service_meta` `tsm2` on(((`ts`.`id` = `tsm2`.`trial_service_id`) and (`tsm2`.`meta_key` = 'source_reference')))) where ((`tsm1`.`meta_value` <> '') and (`ts`.`lead_id` is not null) and (`ts`.`status` = 'CONVERTED')) */;

--
-- Final view structure for view `v_user_info`
--

/*!50001 DROP TABLE IF EXISTS `v_user_info`*/;
/*!50001 DROP VIEW IF EXISTS `v_user_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`tientq`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_user_info` AS select `u`.`ID` AS `ID`,`p`.`code` AS `personnel_code`,`u`.`username` AS `username`,`u`.`department_id` AS `department_id`,`p`.`name` AS `name`,`d`.`title` AS `department`,`d`.`parent_id` AS `parent_id`,`d`.`lineage` AS `lineage` from ((`users` `u` join `departments` `d` on((`u`.`department_id` = `d`.`ID`))) join `personnels` `p` on((`u`.`personnel_id` = `p`.`ID`))) */;


