--
-- Host: localhost    Database: src_compliance
-- ------------------------------------------------------


--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `category_id` bigint NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`category_id`),
  KEY `category_fk1` (`created_by`),
  CONSTRAINT `category_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` VALUES (1,'State Corporation',NULL,'2021-04-28 18:12:12','2021-04-28 15:12:12',1,4),(2,'County Governments','County Governments including the Executives and Assemblies','2021-04-28 18:12:40','2021-05-01 12:03:15',1,4),(3,'Ministries & State Departments',NULL,'2021-04-28 18:13:00','2021-04-28 15:13:00',1,4),(4,'Public Universities & Research & TVETs',NULL,'2021-04-28 18:13:25','2021-04-28 15:15:20',1,4),(5,'CCIOs',NULL,'2021-04-28 18:14:06','2021-04-28 15:14:06',1,4),(6,'Disciplined Services',NULL,'2021-04-28 18:14:29','2021-04-28 15:14:29',1,4);

--
-- Table structure for table `constituency`
--

CREATE TABLE `constituency` (
  `constituency_id` bigint NOT NULL AUTO_INCREMENT,
  `county_id` bigint NOT NULL,
  `constituency_name` varchar(30) NOT NULL,
  `constituency_code` varchar(30) NOT NULL,
  `status` smallint DEFAULT '1',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`constituency_id`),
  KEY `constituency_fk1` (`county_id`),
  KEY `constituency_fk2` (`created_by`),
  CONSTRAINT `constituency_fk1` FOREIGN KEY (`county_id`) REFERENCES `county` (`county_id`),
  CONSTRAINT `constituency_fk2` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `constituency`
--

INSERT INTO `constituency` VALUES (1,5,'Maragua','023002',1,'2021-04-28 20:24:35','2021-04-28 17:24:35',4),(2,5,'Kiharu','023004',1,'2021-04-28 20:25:34','2021-04-28 17:28:16',4);

--
-- Table structure for table `control_figure`
--

CREATE TABLE `control_figure` (
  `control_figure_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_id` bigint NOT NULL,
  `sub_questionnaire_id` bigint DEFAULT NULL,
  `control_figure_name` varchar(50) NOT NULL,
  `value` int NOT NULL,
  `questionnaire_table_id` bigint DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`control_figure_id`),
  KEY `control_figure_fk1` (`created_by`),
  KEY `control_figure_fk2` (`questionnaire_id`),
  KEY `control_figure_fk3` (`sub_questionnaire_id`),
  KEY `control_figure_fk4` (`questionnaire_table_id`),
  CONSTRAINT `control_figure_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `control_figure_fk2` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaire` (`questionnaire_id`),
  CONSTRAINT `control_figure_fk3` FOREIGN KEY (`sub_questionnaire_id`) REFERENCES `sub_questionnaire` (`sub_questionnaire_id`),
  CONSTRAINT `control_figure_fk4` FOREIGN KEY (`questionnaire_table_id`) REFERENCES `questionnaire_table` (`questionnaire_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `control_figure`
--


--
-- Table structure for table `county`
--

CREATE TABLE `county` (
  `county_id` bigint NOT NULL AUTO_INCREMENT,
  `county_name` varchar(30) NOT NULL,
  `county_code` varchar(30) NOT NULL,
  `status` smallint DEFAULT '1',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`county_id`),
  KEY `county_fk1` (`created_by`),
  CONSTRAINT `county_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `county`
--

INSERT INTO `county` VALUES (1,'Nairobi','047',1,'2021-04-28 14:08:25','2021-04-28 13:42:36',4),(4,'Mombasa','001',1,'2021-04-28 16:53:45','2021-04-28 13:53:45',4),(5,'Murang\'a','021',1,'2021-04-28 16:53:57','2021-04-28 13:53:57',4);

--
-- Table structure for table `county_assembly`
--

CREATE TABLE `county_assembly` (
  `county_assembly_id` bigint NOT NULL AUTO_INCREMENT,
  `county_id` bigint NOT NULL,
  `county_assembly_name` varchar(50) NOT NULL,
  `county_assembly_code` varchar(30) NOT NULL,
  `status` smallint DEFAULT '1',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`county_assembly_id`),
  KEY `county_assembly_fk1` (`county_id`),
  KEY `county_assembly_fk2` (`created_by`),
  CONSTRAINT `county_assembly_fk1` FOREIGN KEY (`county_id`) REFERENCES `county` (`county_id`),
  CONSTRAINT `county_assembly_fk2` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `county_assembly`
--

INSERT INTO `county_assembly` VALUES (2,5,'Murang\'a County Assembly','021001',1,'2021-04-28 21:18:10','2021-04-28 18:23:12',4);

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `financial_year`
--

CREATE TABLE `financial_year` (
  `financial_year_id` bigint NOT NULL AUTO_INCREMENT,
  `financial_year_name` varchar(20) NOT NULL,
  `status` smallint DEFAULT '1',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`financial_year_id`),
  KEY `financial_year_fk1` (`created_by`),
  CONSTRAINT `financial_year_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `financial_year`
--

INSERT INTO `financial_year` VALUES (1,'2017/2018',1,'2021-04-28 22:38:19','2021-04-28 19:38:19',4),(2,'2018/2019',1,'2021-04-28 22:38:29','2021-04-28 19:38:29',4),(3,'2019/2020',1,'2021-04-28 22:38:37','2021-04-28 19:38:37',4),(4,'2020/2021',1,'2021-04-28 22:38:44','2021-04-28 19:38:44',4);

--
-- Table structure for table `institution`
--

CREATE TABLE `institution` (
  `institution_id` bigint NOT NULL AUTO_INCREMENT,
  `sub_category_id` bigint NOT NULL,
  `institution_name` varchar(50) NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`institution_id`),
  KEY `institution_fk1` (`created_by`),
  KEY `institution_fk2` (`sub_category_id`),
  CONSTRAINT `institution_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `institution_fk2` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_category` (`sub_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `institution`
--

INSERT INTO `institution` VALUES (1,8,'Bumbe Institute of Technology','Technical College','2021-04-28 22:11:35','2021-04-28 19:14:57',1,4),(4,8,'Sigalagala Technical Training Institute','Technical Training Institute','2021-04-28 22:16:39','2021-04-28 19:16:39',1,4),(5,1,'Rift Valley Water Services Board','Rift Valley Water Services Board','2021-05-02 15:20:59','2021-05-02 12:20:59',1,4),(6,2,'School Equipment Production Unit','School Equipment Production Unit','2021-05-02 15:23:57','2021-05-02 12:23:57',1,4),(7,2,'Simlaw Seeds Company Limited','Simlaw Seeds Company Limited','2021-05-02 15:24:23','2021-05-02 12:24:23',1,4),(8,2,'South Nyanza Sugar Company','South Nyanza Sugar Company','2021-05-02 15:24:45','2021-05-02 12:24:45',1,4),(9,4,'Baringo County Government','Baringo County Government','2021-05-02 15:26:19','2021-05-02 12:26:19',1,4),(10,4,'Bomet County Government','Bomet County Government','2021-05-02 15:26:40','2021-05-02 12:26:40',1,4),(11,4,'Bungoma County Government','Bungoma County Government','2021-05-02 15:27:00','2021-05-02 12:27:00',1,4);

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

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `migrations` VALUES (1,'2014_10_12_000000_create_users_table',1),(2,'2014_10_12_100000_create_password_resets_table',1),(3,'2019_08_19_000000_create_failed_jobs_table',1);

--
-- Table structure for table `questionnaire`
--

CREATE TABLE `questionnaire` (
  `questionnaire_id` bigint NOT NULL AUTO_INCREMENT,
  `category_id` bigint DEFAULT NULL,
  `sub_category_id` bigint DEFAULT NULL,
  `questionnaire_part_id` bigint DEFAULT NULL,
  `questionnaire_section_id` bigint DEFAULT NULL,
  `question` text NOT NULL,
  `question_no` int NOT NULL,
  `feedback_area` tinyint(1) DEFAULT '0',
  `needs_upload` tinyint DEFAULT '0',
  `checkbox_options` varchar(200) DEFAULT NULL,
  `dropdown_options` varchar(200) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_id`),
  KEY `questionnaire_fk1` (`created_by`),
  KEY `questionnaire_fk2` (`category_id`),
  KEY `questionnaire_fk3` (`sub_category_id`),
  KEY `questionnaire_fk4` (`questionnaire_part_id`),
  KEY `questionnaire_fk5` (`questionnaire_section_id`),
  CONSTRAINT `questionnaire_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `questionnaire_fk2` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`),
  CONSTRAINT `questionnaire_fk3` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_category` (`sub_category_id`),
  CONSTRAINT `questionnaire_fk4` FOREIGN KEY (`questionnaire_part_id`) REFERENCES `questionnaire_part` (`questionnaire_part_id`),
  CONSTRAINT `questionnaire_fk5` FOREIGN KEY (`questionnaire_section_id`) REFERENCES `questionnaire_section` (`questionnaire_section_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire`
--

INSERT INTO `questionnaire` VALUES (1,NULL,NULL,2,1,'<p>Does your institution have a document outlining HR Policies and Procedures on recruitment, promotion, performance management, and training?&nbsp;</p>',1,0,0,'Yes,No',NULL,'2021-04-29 21:38:07','2021-04-29 18:38:07',1,4),(2,NULL,NULL,2,1,'<p>Does your institution have Scheme(s) of Service and/or Career Progression Guidelines?</p>',2,0,0,'Yes,No',NULL,'2021-04-30 12:00:43','2021-04-30 09:00:43',1,4),(3,NULL,NULL,2,1,'<p>Does your institution have a Performance Management System?</p>',3,0,0,'Yes,No',NULL,'2021-04-30 12:41:19','2021-04-30 09:41:19',1,4),(4,NULL,NULL,2,1,'<p>Does your institution have an automated Human Resource Management Information System?</p>',4,0,0,'Yes,No',NULL,'2021-04-30 13:01:05','2021-04-30 10:01:05',1,4),(6,NULL,NULL,2,3,'<p>Does your institution have an approved staff establishment?</p>',5,0,0,'Yes,No',NULL,'2021-04-30 17:08:14','2021-04-30 14:08:14',1,4),(7,NULL,NULL,2,3,'<p>How many employees in your institution are serving (or had served) in different terms of employment?</p>',6,0,0,NULL,NULL,'2021-04-30 19:34:03','2021-04-30 16:34:03',1,4),(9,NULL,NULL,2,3,'<p>How many employees are there in your institution by mode of recruitment, and what is the total wage bill for each category?&nbsp;</p>',7,0,0,NULL,NULL,'2021-04-30 19:50:03','2021-04-30 16:50:03',1,4),(10,NULL,NULL,2,3,'<p>How many employees are there in your institution by mode of recruitment, and what is the total wage bill for each category?</p>',8,0,0,NULL,NULL,'2021-04-30 20:08:03','2021-04-30 17:08:03',1,4),(11,NULL,NULL,2,3,'<p>How many State Officers do you have and what is the cost of their remuneration and benefits?</p>',9,0,0,NULL,NULL,'2021-04-30 20:16:37','2021-04-30 17:16:37',1,4),(12,NULL,NULL,2,3,'<p>How many Advisors do you have and what is the cost of their remuneration and benefits?&nbsp;</p>',10,0,0,NULL,NULL,'2021-04-30 20:26:18','2021-04-30 17:26:18',1,4),(13,NULL,NULL,2,3,'<p>How many Board Members/Commissioners do you have and what is the cost of their remuneration and benefits? (Including Municipal and City Boards)</p>',11,0,0,NULL,NULL,'2021-04-30 20:29:44','2021-04-30 17:29:44',1,4),(14,NULL,NULL,2,3,'<p>How much is your total wage bill (i.e. salaries, wages) and pension/gratuity contribution/payments for the period 2012/13 to 2019/20 FYs?</p>',12,0,0,NULL,NULL,'2021-04-30 20:34:33','2021-04-30 17:34:33',1,4),(15,NULL,NULL,2,3,'<p>How much of the total compensation was paid as:</p>',13,0,0,NULL,NULL,'2021-04-30 20:48:36','2021-04-30 17:48:36',1,4),(16,NULL,NULL,2,3,'<p>What other allowances are paid in your institution to both State and Public Officers in the format below?</p>',14,0,0,NULL,NULL,'2021-04-30 21:09:01','2021-04-30 18:09:01',1,4),(17,NULL,NULL,2,3,'<p>Has the county formed any County Corporations? If Yes, kindly provide the salary structure for staff for each Corporation.</p>',15,1,0,NULL,NULL,'2021-04-30 21:10:29','2021-04-30 18:10:29',1,4);

--
-- Table structure for table `questionnaire_answer`
--

CREATE TABLE `questionnaire_answer` (
  `questionnaire_answer_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_id` bigint NOT NULL,
  `sub_questionnaire_id` bigint DEFAULT NULL,
  `institution_id` bigint NOT NULL,
  `answered_by` bigint NOT NULL,
  `in_progress` tinyint(1) DEFAULT NULL,
  `submitted_for_review` tinyint(1) DEFAULT '0',
  `returned_for_review` tinyint(1) DEFAULT '0',
  `review_comments` text,
  `approved_by` bigint NOT NULL,
  `questionnaire_year` varchar(5) NOT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_answer_id`),
  KEY `questionnaire_answer_fk1` (`questionnaire_id`),
  KEY `questionnaire_answer_fk2` (`answered_by`),
  KEY `questionnaire_answer_fk3` (`approved_by`),
  KEY `questionnaire_answer_fk4` (`created_by`),
  CONSTRAINT `questionnaire_answer_fk1` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaire` (`questionnaire_id`),
  CONSTRAINT `questionnaire_answer_fk2` FOREIGN KEY (`answered_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `questionnaire_answer_fk3` FOREIGN KEY (`approved_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `questionnaire_answer_fk4` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_answer`
--


--
-- Table structure for table `questionnaire_answer_detail`
--

CREATE TABLE `questionnaire_answer_detail` (
  `questionnaire_answer_detail_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_answer_id` bigint NOT NULL,
  `answer_checkbox_options` varchar(100) DEFAULT NULL,
  `answer_dropdown_option` varchar(50) DEFAULT NULL,
  `answer_text` text,
  `answer_upload` varchar(200) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_answer_detail_id`),
  KEY `questionnaire_answer_detail_fk1` (`questionnaire_answer_id`),
  KEY `questionnaire_answer_detail_fk4` (`created_by`),
  CONSTRAINT `questionnaire_answer_detail_fk1` FOREIGN KEY (`questionnaire_answer_id`) REFERENCES `questionnaire_answer` (`questionnaire_answer_id`),
  CONSTRAINT `questionnaire_answer_detail_fk4` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_answer_detail`
--


--
-- Table structure for table `questionnaire_part`
--

CREATE TABLE `questionnaire_part` (
  `questionnaire_part_id` bigint NOT NULL AUTO_INCREMENT,
  `part_name` varchar(200) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_part_id`),
  KEY `questionnaire_part_fk1` (`created_by`),
  CONSTRAINT `questionnaire_part_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_part`
--

INSERT INTO `questionnaire_part` VALUES (1,'PART 1: GENERAL INFORMATION',NULL,'2021-04-29 03:23:36','2021-04-29 01:03:24',4),(2,'PART 2: TECHNICAL QUESTIONS',NULL,'2021-04-29 03:23:45','2021-04-29 01:04:04',4),(3,'PART 3: DISCUSSION',NULL,'2021-04-29 03:23:52','2021-04-29 01:04:36',4);

--
-- Table structure for table `questionnaire_section`
--

CREATE TABLE `questionnaire_section` (
  `questionnaire_section_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_part_id` bigint NOT NULL,
  `section_name` varchar(200) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_section_id`),
  KEY `questionnaire_section_fk1` (`created_by`),
  KEY `questionnaire_section_fk2` (`questionnaire_part_id`),
  CONSTRAINT `questionnaire_section_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `questionnaire_section_fk2` FOREIGN KEY (`questionnaire_part_id`) REFERENCES `questionnaire_part` (`questionnaire_part_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_section`
--

INSERT INTO `questionnaire_section` VALUES (1,2,'SECTION A: HUMAN RESOURCES POLICIES AND PRACTICES',0,'2021-04-29 04:06:07','2021-04-29 01:09:25',4),(3,2,'SECTION B: EMPLOYEES NUMBERS, SALARIES, WAGES AND ALLOWANCES',0,'2021-04-29 04:10:28','2021-04-29 01:10:28',4),(4,2,'SECTION C: PAYROLL MANAGEMENT',0,'2021-04-29 04:11:20','2021-04-29 01:11:20',4);

--
-- Table structure for table `questionnaire_table`
--

CREATE TABLE `questionnaire_table` (
  `questionnaire_table_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_id` bigint DEFAULT NULL,
  `sub_questionnaire_id` bigint DEFAULT NULL,
  `table_no` int NOT NULL,
  `table_title` varchar(200) NOT NULL,
  `table_html` text NOT NULL,
  `table_js` text,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_table_id`),
  KEY `questionnaire_table_fk1` (`created_by`),
  CONSTRAINT `questionnaire_table_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_table`
--

INSERT INTO `questionnaire_table` VALUES (1,6,NULL,1,'Table 1: Approved Staff Establishment','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"staff_establishment\">\r\n		<thead>\r\n			<tr>\r\n				<th>Designation</th>\r\n				<th>Internal Grade</th>\r\n				<th>Civil Service Equivalent</th>\r\n				<th>Number (As At....) \r\n				<span style=\"float:right\">\r\n				<button id=\"but_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n				</span>\r\n				</th>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n    $(function() {\r\n\r\n        $(\'#staff_establishment\').SetEditable({\r\n            $addButton: $(\'#but_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'staff_establishment\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 17:08:14','2021-04-30 14:41:06',1,4),(2,7,NULL,2,'Table 2: Number of employees','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"employees_table\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td colspan=\"8\">Number of Public officers</td>\r\n			</tr>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td>Permanent</td>\r\n				<td>Contractual</td>\r\n				<td>Casual</td>\r\n				<td>Temporary</td>\r\n				<td>Interns</td>\r\n				<td>Secondments in</td>\r\n				<td>Secondments out</td>\r\n				<td>Total\r\n				<span style=\"float:right\">\r\n				<button id=\"emp_row_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n				</span>\r\n				</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"financial_year\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n                <td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n\r\n	$(document).ready(function() { \r\n\r\n        $.ajax({\r\n            type: \"POST\",\r\n            data: {\"_token\": $(\'meta[name=\"csrf-token\"]\').attr(\'content\')},\r\n            url: \"/questionnaire/years\",\r\n            success: function(data){\r\n                $(\".finad\").html(data);\r\n            }\r\n        });\r\n     });\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#employees_table\').SetEditable({\r\n            $addButton: $(\'#emp_row_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'employees_table\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 19:34:03','2021-04-30 16:38:06',1,4),(3,9,NULL,3,'Table 3: Category of Employees (Public Officers including Board Members and Advisors)','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"employees_cat_table\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td colspan=\"2\">Staff Recruited by CPSB</td>\r\n				<td colspan=\"2\">Staff Recruited by CASB</td>\r\n				<td colspan=\"2\">Staff Devolved from National Government</td>\r\n				<td colspan=\"2\">Staff Inherited from Defunct Local Authorities</td>\r\n				<td colspan=\"2\">\r\n					Others (casuals and other temporary employees)\r\n					<span style=\"float:right\">\r\n					<button id=\"emp_row_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"cat_financial_year\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n                <td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#employees_cat_table\').SetEditable({\r\n            $addButton: $(\'#emp_row_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'employees_cat_table\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 19:50:03','2021-04-30 16:57:49',1,4),(4,10,NULL,4,'Table 4: Category of Employees (Public Officers including Board Members and Advisors)','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"employees_cat_tb\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td colspan=\"2\">Permanent and Pensionable</td>\r\n				<td colspan=\"2\">Contract</td>\r\n				<td colspan=\"2\">\r\n					Others(casuals and other temporary employees)\r\n					<span style=\"float:right\">\r\n					<button id=\"emp_row_add_cat\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n				<td>Total Number</td>\r\n				<td>Total Wage Bill</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"cat_financial_year\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#employees_cat_tb\').SetEditable({\r\n            $addButton: $(\'#emp_row_add_cat\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'employees_cat_tb\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:08:03','2021-04-30 17:17:42',1,4),(5,11,NULL,5,'Table 5: Total Wage Bill Expenditure for State Officers','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"wage_bill_tb\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td rowspan=\"2\">State officers (no)</td>\r\n				<td colspan=\"2\">\r\n					Total Wage Bill Expenditure (Kshs)\r\n					<span style=\"float:right\">\r\n					<button id=\"wage_bill_row_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>Remuneration</td>\r\n				<td>Benefits</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"wage_bill_financial_year\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#wage_bill_tb\').SetEditable({\r\n            $addButton: $(\'#wage_bill_row_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'wage_bill_tb\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:16:37','2021-04-30 17:16:37',1,4),(6,12,NULL,6,'Table 6: Total Wage Bill Expenditures for Advisors','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"wage_bill_ad\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td rowspan=\"2\">Advisors (No)</td>\r\n				<td colspan=\"2\">\r\n					Total Wage Bill Expenditure (Kshs)\r\n					<span style=\"float:right\">\r\n					<button id=\"wage_bill_row_add_ad\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>Remuneration</td>\r\n				<td>Benefits</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"wage_bill_financial_year_ad\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#wage_bill_ad\').SetEditable({\r\n            $addButton: $(\'#wage_bill_row_add_ad\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'wage_bill_ad\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:26:19','2021-04-30 17:26:19',1,4),(7,13,NULL,7,'Table 7: Total Wage Bill Expenditures for Board Members','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"wage_bill_board\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">FY</td>\r\n				<td rowspan=\"2\">Board Members/Commisioners (No)</td>\r\n				<td colspan=\"2\">\r\n					Total Wage Bill Expenditure (Kshs)\r\n					<span style=\"float:right\">\r\n					<button id=\"wage_bill_row_add_board\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>Remuneration</td>\r\n				<td>Benefits</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"wage_bill_financial_year_board\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#wage_bill_board\').SetEditable({\r\n            $addButton: $(\'#wage_bill_row_add_board\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'wage_bill_board\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:29:44','2021-04-30 17:29:44',1,4),(8,14,NULL,8,'Table 8: Total Wage Bill Expenditure including pension for the institution','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"wage_bill_total\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td>FY</td>\r\n				<td>Wage Bill Expenditure</td>\r\n				<td>Pension</td>\r\n				<td>\r\n					Total Expenditure\r\n					<span style=\"float:right\">\r\n					<button id=\"wage_bill_row_add_total\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>\r\n                    <select name=\"wage_bill_financial_year_total\" class=\"form-control finad\">\r\n                    </select>\r\n                </td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#wage_bill_total\').SetEditable({\r\n            $addButton: $(\'#wage_bill_row_add_total\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'wage_bill_total\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:34:33','2021-04-30 17:34:33',1,4),(9,15,NULL,9,'Table 9: Total Compensation','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"compensation_total\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td rowspan=\"2\">Category</td>\r\n				<td colspan=\"8\">\r\n					Period\r\n					<span style=\"float:right\">\r\n					<button id=\"compensation_row_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n			<tr>\r\n				<td>2012/2013</td>\r\n				<td>2013/2014</td>\r\n				<td>2014/2015</td>\r\n				<td>2015/2016</td>\r\n				<td>2016/2017</td>\r\n				<td>2017/2018</td>\r\n				<td>2018/2019</td>\r\n				<td>2019 Dec</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td>Basic Salary</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Other Allowances e.g. DSA, Sitting allowances</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Wages to Casuals</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Pension</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Medical Cover</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Group Life Cover</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Group Personal Accident</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Gratuity</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Bonus</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Arrears</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Top-up Salary</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n			<tr class=\"bg-white\">\r\n				<td>Others (specify)</td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#compensation_total\').SetEditable({\r\n            $addButton: $(\'#compensation_row_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'compensation_total\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 20:48:36','2021-04-30 17:48:36',1,4),(10,16,NULL,10,'Table 10: Allowances Payable','<!-- Editable Table -->\r\n<div class=\"container col-md-12\">\r\n	<table class=\"table table-responsive-md table-sm table-bordered\" id=\"allowances_tb\">\r\n		<thead>\r\n			<tr style=\"font-weight: 550;\">\r\n				<td>Name of Allowance</td>\r\n				<td>Source of Circular (e.g. SRC, PSC, etc)</td>\r\n				<td>Eligibile Staff by Grade</td>\r\n				<td>Rate of Payment/ amount(KShs)</td>\r\n				<td>Mode of payment (e.g. payroll, cash or voucher)</td>\r\n				<td>\r\n					Frequency of Payment (e.g. Monthly, annually, etc)\r\n					<span style=\"float:right\">\r\n					<button id=\"allowances_row_add\" class=\"btn btn-danger\" style=\"padding: 2px; font-size: 11px;\">Add New Row</button>\r\n					</span>\r\n				</td>\r\n			</tr>\r\n		</thead>\r\n		<tbody>\r\n			<tr class=\"bg-white\">\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n				<td></td>\r\n			</tr>\r\n		</tbody>\r\n	</table>\r\n\r\n</div>\r\n<!-- End -->','<script>\r\n	 \r\n    $(function() {\r\n\r\n        $(\'#allowances_tb\').SetEditable({\r\n            $addButton: $(\'#allowances_row_add\')\r\n        });\r\n\r\n        $(\'#submit_data\').on(\'click\', function() {\r\n            var td = TableToCSV(\'allowances_tb\', \',\');\r\n            console.log(td);\r\n            var ar_lines = td.split(\"\\n\");\r\n            var each_data_value = [];\r\n            for (i = 0; i < ar_lines.length; i++) {\r\n                each_data_value[i] = ar_lines[i].split(\",\");\r\n            }\r\n\r\n            for (i = 0; i < each_data_value.length; i++) {\r\n                if (each_data_value[i] > 1) {\r\n                    console.log(each_data_value[i][2]);\r\n                    console.log(each_data_value[i].length);\r\n                }\r\n\r\n            }\r\n\r\n        });\r\n    });\r\n</script>','2021-04-30 21:09:01','2021-04-30 18:09:01',1,4);

--
-- Table structure for table `questionnaire_table_answer`
--

CREATE TABLE `questionnaire_table_answer` (
  `questionnaire_table_answer_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_answer_id` bigint NOT NULL,
  `questionnaire_table_id` bigint NOT NULL,
  `table_answerhtml` text NOT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`questionnaire_table_answer_id`),
  KEY `questionnaire_table_fk1` (`created_by`),
  KEY `questionnaire_table_answer_fk2` (`questionnaire_answer_id`),
  KEY `questionnaire_table_answer_fk3` (`questionnaire_table_id`),
  CONSTRAINT `questionnaire_table_answer_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `questionnaire_table_answer_fk2` FOREIGN KEY (`questionnaire_answer_id`) REFERENCES `questionnaire_answer` (`questionnaire_answer_id`),
  CONSTRAINT `questionnaire_table_answer_fk3` FOREIGN KEY (`questionnaire_table_id`) REFERENCES `questionnaire_table` (`questionnaire_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `questionnaire_table_answer`
--


--
-- Table structure for table `src_circular`
--

CREATE TABLE `src_circular` (
  `src_circular_id` bigint NOT NULL AUTO_INCREMENT,
  `subject` varchar(300) NOT NULL,
  `ref_no` varchar(50) DEFAULT NULL,
  `date_issued` datetime DEFAULT NULL,
  `status` smallint DEFAULT '1',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`src_circular_id`),
  KEY `src_circular_fk1` (`created_by`),
  CONSTRAINT `src_circular_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `src_circular`
--

INSERT INTO `src_circular` VALUES (1,'<a target=\"_blank\" href=\"https://src.go.ke/wp-content/uploads/2020/08/Salary-Survey-for-the-2021-2025-Remuneration-Review-Cycle-for-Public-Sector.pdf\">Salary survey for 2021-2025</a>','SRC/ADM/CIR/1/13 Vol. IV (39)','2020-08-14 00:00:00',1,'2021-04-30 23:57:23','2021-04-30 21:07:12',4),(2,'<a target=\"_blank\" href=\"https://src.go.ke/wp-content/uploads/2020/08/Housing-Benefits-for-Governors-Deputy-Governors-and-County-Assembly-Speakers.pdf\">Housing benefits for Governors</a>','SRC/ADM/CIR/1/13 Vol. IV (39)','2020-08-14 00:00:00',1,'2021-05-01 00:05:32','2021-04-30 21:11:00',4);

--
-- Table structure for table `sub_category`
--

CREATE TABLE `sub_category` (
  `sub_category_id` bigint NOT NULL AUTO_INCREMENT,
  `category_id` bigint NOT NULL,
  `sub_category_name` varchar(50) NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`sub_category_id`),
  KEY `sub_category_fk1` (`created_by`),
  KEY `sub_category_fk2` (`category_id`),
  CONSTRAINT `sub_category_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `sub_category_fk2` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sub_category`
--

INSERT INTO `sub_category` VALUES (1,1,'Service and Regulatory',NULL,'2021-04-28 19:27:51','2021-04-28 16:27:51',1,4),(2,1,'Commercial and Strategic','Commercial and Strategic Sub Category','2021-04-28 19:44:11','2021-04-28 16:54:14',1,4),(3,1,'Service and Regulatory',NULL,'2021-04-28 19:44:37','2021-04-28 16:44:37',1,4),(4,2,'County Governments - Executive',NULL,'2021-04-28 19:45:32','2021-04-28 16:45:32',1,4),(5,2,'County Governments - Legislature',NULL,'2021-04-28 19:46:04','2021-04-28 16:46:04',1,4),(6,2,'County Governments - Public Service Board',NULL,'2021-04-28 19:46:11','2021-04-28 16:46:11',1,4),(8,4,'TVET Institution','Technical Vocational','2021-04-28 22:03:35','2021-04-28 19:03:35',1,4);

--
-- Table structure for table `sub_questionnaire`
--

CREATE TABLE `sub_questionnaire` (
  `sub_questionnaire_id` bigint NOT NULL AUTO_INCREMENT,
  `questionnaire_id` bigint DEFAULT NULL,
  `parent_sub_questionnaire_id` bigint DEFAULT NULL,
  `sub_question` text NOT NULL,
  `feedback_area` tinyint(1) DEFAULT NULL,
  `sub_question_no` int NOT NULL,
  `needs_upload` tinyint DEFAULT '0',
  `checkbox_options` varchar(200) DEFAULT NULL,
  `dropdown_options` varchar(200) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` bigint NOT NULL,
  PRIMARY KEY (`sub_questionnaire_id`),
  KEY `sub_questionnaire_fk1` (`created_by`),
  KEY `sub_questionnaire_fk2` (`questionnaire_id`),
  KEY `sub_questionnaire_id_fk3` (`parent_sub_questionnaire_id`),
  CONSTRAINT `sub_questionnaire_fk1` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`),
  CONSTRAINT `sub_questionnaire_fk2` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaire` (`questionnaire_id`),
  CONSTRAINT `sub_questionnaire_id_fk3` FOREIGN KEY (`parent_sub_questionnaire_id`) REFERENCES `sub_questionnaire` (`sub_questionnaire_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sub_questionnaire`
--

INSERT INTO `sub_questionnaire` VALUES (1,1,NULL,'<p>If Yes, please specify the documents and provide copies of the documents.</p>',NULL,1,1,NULL,NULL,'2021-04-30 01:05:09','2021-04-29 22:05:09',1,4),(2,1,NULL,'<p>If No, what guides your HR practices?</p>',1,2,NULL,NULL,NULL,'2021-04-30 01:07:50','2021-04-29 22:07:50',1,4),(3,2,NULL,'<p>If Yes, please provide a copy.&nbsp;</p>',0,1,1,NULL,NULL,'2021-04-30 12:01:30','2021-04-30 09:01:30',1,4),(4,2,NULL,'<p>If No, how is career progression managed in your institution?&nbsp;</p>',1,2,NULL,NULL,NULL,'2021-04-30 12:02:10','2021-04-30 09:02:10',1,4),(5,3,NULL,'<p>If Yes,&nbsp;</p>',0,1,0,NULL,NULL,'2021-04-30 12:42:27','2021-04-30 09:42:27',1,4),(6,NULL,5,'<p>Specify the system used to manage performance in your institution.</p>',1,1,0,NULL,NULL,'2021-04-30 12:46:49','2021-04-30 09:53:30',1,4),(7,NULL,5,'<p>How is the system applied in managing performance in your institution?&nbsp;</p>',1,2,0,NULL,NULL,'2021-04-30 12:48:04','2021-04-30 09:53:30',1,4),(8,NULL,5,'<p>What type of rewards are administered?&nbsp;</p>',1,3,0,NULL,NULL,'2021-04-30 12:48:42','2021-04-30 09:53:30',1,4),(9,NULL,5,'<p>What type of Sanctions are used for none performers?</p>',1,4,0,NULL,NULL,'2021-04-30 12:49:20','2021-04-30 09:53:30',1,4),(10,3,NULL,'<p>If No,</p>',0,2,0,NULL,NULL,'2021-04-30 12:56:10','2021-04-30 09:56:10',1,4),(11,NULL,10,'<p>How do you manage performance?</p>',1,1,0,NULL,NULL,'2021-04-30 12:56:53','2021-04-30 09:58:24',1,4),(12,NULL,10,'<p>Are you in the process of developing a Performance Management System?</p>',1,2,0,NULL,NULL,'2021-04-30 12:57:24','2021-04-30 09:58:24',1,4),(13,4,NULL,'<p>If Yes, please specify. (Does it include personal file management, performance appraisal, attendance monitoring, etc)?</p>',1,1,0,NULL,NULL,'2021-04-30 13:02:39','2021-04-30 10:02:39',1,4),(14,4,NULL,'<p>If No, state what is in use?</p>',1,2,0,NULL,NULL,'2021-04-30 13:03:08','2021-04-30 10:03:08',1,4),(15,6,NULL,'<p>Is the establishment adhered to during recruitments?</p>',1,1,0,NULL,NULL,'2021-04-30 17:43:59','2021-04-30 14:43:59',1,4),(16,6,NULL,'<p>If No, how does the county control recruitment?</p>',1,2,0,NULL,NULL,'2021-04-30 17:44:54','2021-04-30 14:44:54',1,4);

--
-- Table structure for table `user_role`
--

CREATE TABLE `user_role` (
  `user_role_id` bigint NOT NULL AUTO_INCREMENT,
  `role` varchar(50) NOT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint DEFAULT '1',
  `created_by` int DEFAULT NULL,
  PRIMARY KEY (`user_role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_role`
--

INSERT INTO `user_role` VALUES (1,'SRC Employee','2021-04-28 13:26:41','2021-04-28 10:26:41',1,1),(2,'Government Institution Clerk','2021-04-28 13:26:58','2021-04-28 10:26:58',1,1),(3,'Government Institution Management','2021-04-28 13:27:04','2021-04-28 10:27:04',1,1),(8,'SRC Management','2021-05-02 03:24:14','2021-05-02 00:24:14',1,4);


