-- MySQL Script generated by MySQL Workbench -- 03/08/18 21:25:33 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Table `incidence_category` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_category`; CREATE TABLE IF NOT EXISTS `incidence_category` ( `serial_ica` INT(11) NOT NULL AUTO_INCREMENT, `serial_scy` INT(11) NOT NULL, `incidence_serial_ica` INT(11) NULL DEFAULT NULL, `name_ica` VARCHAR(80) NOT NULL, `attendance_relation_ica` ENUM('NONE', 'FI', 'FJ') NOT NULL DEFAULT 'NONE', `status_ica` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_ica`)) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indices de la tabla `incidence_category` -- ALTER TABLE `incidence_category` ADD KEY `fk_incidence_category_parent_idx` (`incidence_serial_ica`); -- -- Filtros para la tabla `incidence_category` -- ALTER TABLE `incidence_category` ADD CONSTRAINT `fk_incidence_category_parent` FOREIGN KEY (`incidence_serial_ica`) REFERENCES `incidence_category` (`serial_ica`); -- ----------------------------------------------------- -- Table `incidence_setup` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_setup`; CREATE TABLE IF NOT EXISTS `incidence_setup` ( `serial_ise` INT(11) NOT NULL AUTO_INCREMENT, `serial_ica` INT(11) NOT NULL, `frequency_ise` INT(1) NOT NULL DEFAULT 1, `value_ise` DECIMAL(2) NULL, `lower_discipline_level_ise` ENUM('YES', 'NO') NOT NULL DEFAULT 'NO', `by_subject_ise` ENUM('YES', 'NO') NOT NULL DEFAULT 'NO', `status_ise` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_ise`), CONSTRAINT `fk_incidence_setup_incidence_category` FOREIGN KEY (`serial_ica`) REFERENCES `incidence_category` (`serial_ica`) ON DELETE NO ACTION ON UPDATE NO ACTION) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE INDEX `fk_incidence_setup_incidence_category_idx` ON `incidence_setup` (`serial_ica` ASC); -- ----------------------------------------------------- -- Table `incidence_year_level` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_year_level`; CREATE TABLE IF NOT EXISTS `incidence_year_level` ( `serial_iyl` INT(11) NOT NULL AUTO_INCREMENT, `serial_scy` INT(11) NOT NULL, `serial_lvl` INT(11) NOT NULL, `status_iyl` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_iyl`)) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indices de la tabla `incidence_year_level` -- ALTER TABLE `incidence_year_level` ADD KEY `fk_incidence_year_school_year_idx` (`serial_scy`), ADD KEY `fk_incidence_year_level_idx` (`serial_lvl`); -- -- Filtros para la tabla `incidence_year_level` -- ALTER TABLE `incidence_year_level` ADD CONSTRAINT `fk_incidence_year_level` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`), ADD CONSTRAINT `fk_incidence_year_school_year` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`); -- ----------------------------------------------------- -- Table `incidence_student` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_student`; CREATE TABLE IF NOT EXISTS `incidence_student` ( `serial_ist` INT(11) NOT NULL AUTO_INCREMENT, `serial_ica` INT(11) NOT NULL, `serial_usr` INT(11) NOT NULL, `serial_std` INT(11) NOT NULL, `serial_sbc` INT(11) NULL, `serial_osb` INT(11) NULL, `serial_sbp` INT(11) NOT NULL, `serial_scd` INT(11) NULL, `date_ist` DATE NOT NULL, `insert_ist` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `comentary_ist` VARCHAR(80) NULL DEFAULT NULL, `status_ist` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_ist`) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indices de la tabla `incidence_student` -- ALTER TABLE `incidence_student` ADD KEY `fk_incidence_student_incidence_category_idx` (`serial_ica`), ADD KEY `fk_incidence_student_user_idx` (`serial_usr`), ADD KEY `fk_incidence_student_student_idx` (`serial_std`), ADD KEY `fk_incidence_student_subject_course_idx` (`serial_sbc`), ADD KEY `fk_incidence_student_optional_subject_idx` (`serial_osb`), ADD KEY `fk_incidence_student_subperiod_idx` (`serial_sbp`), ADD KEY `fk_incidence_student_schedule_idx` (`serial_scd`); -- -- Filtros para la tabla `incidence_student` -- ALTER TABLE `incidence_student` ADD CONSTRAINT `fk_incidence_student_incidence_category` FOREIGN KEY (`serial_ica`) REFERENCES `incidence_category` (`serial_ica`), ADD CONSTRAINT `fk_incidence_student_user` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`), ADD CONSTRAINT `fk_incidence_student_student` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`), ADD CONSTRAINT `fk_incidence_student_subject_course` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`), ADD CONSTRAINT `fk_incidence_student_optional_subject` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`), ADD CONSTRAINT `fk_incidence_student_subperiod` FOREIGN KEY (`serial_sbp`) REFERENCES `subperiod` (`serial_sbp`), ADD CONSTRAINT `fk_incidence_student_schedule` FOREIGN KEY (`serial_scd`) REFERENCES `schedule` (`serial_scd`); -- ----------------------------------------------------- -- Table `incidence_student_summary` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_student_summary`; CREATE TABLE IF NOT EXISTS `incidence_student_summary` ( `serial_iss` INT(11) NOT NULL AUTO_INCREMENT, `serial_ica` INT(11) NOT NULL, `serial_std` INT(11) NOT NULL, `total_iss` INT(5) NOT NULL DEFAULT 0, `cycle_iss` INT(5) NOT NULL DEFAULT 0, `status_iss` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_iss`) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indices de la tabla `incidence_student_summary` -- ALTER TABLE `incidence_student_summary` ADD KEY `fk_incidence_student_summary_incidence_category_idx` (`serial_ica`), ADD KEY `fk_incidence_student_summary_student_idx` (`serial_std`); -- -- Filtros para la tabla `incidence_student_summary` -- ALTER TABLE `incidence_student_summary` ADD CONSTRAINT `fk_incidence_student_summary_incidence_category` FOREIGN KEY (`serial_ica`) REFERENCES `incidence_category` (`serial_ica`), ADD CONSTRAINT `fk_incidence_student_summary_student` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`); -- ----------------------------------------------------- -- Table `incidence_student_detail` -- ----------------------------------------------------- DROP TABLE IF EXISTS `incidence_student_detail`; CREATE TABLE IF NOT EXISTS `incidence_student_detail` ( `serial_isd` INT(11) NOT NULL AUTO_INCREMENT, `serial_iss` INT(11) NOT NULL, `serial_sbc` INT(11) NULL, `serial_osb` INT(11) NULL, `total_isd` INT(5) NOT NULL DEFAULT 0, `cycle_isd` INT(5) NOT NULL DEFAULT 0, `status_isd` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE', PRIMARY KEY (`serial_isd`) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indices de la tabla `incidence_student_detail` -- ALTER TABLE `incidence_student_detail` ADD KEY `fk_incidence_student_detail_incidence_student_summary_idx` (`serial_iss`), ADD KEY `fk_incidence_student_detail_subject_idx` (`serial_sbc`), ADD KEY `fk_incidence_student_detail_optional_subject_idx` (`serial_osb`); -- -- Filtros para la tabla `incidence_student_detail` -- ALTER TABLE `incidence_student_detail` ADD CONSTRAINT `fk_incidence_student_detail_incidence_student_summary` FOREIGN KEY (`serial_iss`) REFERENCES `incidence_student_summary` (`serial_iss`), ADD CONSTRAINT `fk_incidence_student_detail_subject` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`), ADD CONSTRAINT `fk_incidence_student_detail_optional_subject` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`); ALTER TABLE `incidence_year_level` ADD `max_time_iyl` TIME NULL DEFAULT NULL AFTER `serial_lvl`; ALTER TABLE `incidence_year_level` ADD `serial_set` INT(11) NOT NULL AFTER `serial_scy`; ALTER TABLE incidence_year_level RENAME incidence_year_section_level; ALTER TABLE `incidence_setup` CHANGE `lower_discipline_level_ise` `lower_discipline_level_ise` TINYINT(1) NOT NULL DEFAULT '0', CHANGE `by_subject_ise` `by_subject_ise` TINYINT(1) NOT NULL DEFAULT '0'; ALTER TABLE `incidence_student` ADD `value_ist` DECIMAL(2,0) NULL AFTER `comentary_ist`, ADD `serial_usr_delete_ist` INT(11) NULL AFTER `value_ist`, ADD `comentary_delete_ist` VARCHAR(80) NULL AFTER `serial_usr_delete_ist`; ALTER TABLE `incidence_setup` CHANGE `value_ise` `value_ise` DECIMAL(5,2) NULL DEFAULT NULL; ALTER TABLE `incidence_student` CHANGE `value_ist` `value_ist` DECIMAL(5,2) NULL DEFAULT NULL; ALTER TABLE `incidence_student` ADD `by_subject_ist` TINYINT(1) NOT NULL DEFAULT '0' AFTER `value_ist`; ALTER TABLE `incidence_category` CHANGE `attendance_relation_ica` `attendance_relation_ica` ENUM('NONE','UNEXCUSED_ABSENCE','EXCUSED_ABSENCE','DELAY') BINARY CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NONE'; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- inserto proceso de configuracion en el menú INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('659', '143', 'Configuración de Incidencias de Comportamiento', 'laravel/public/incidenceSetup/index', '4', 'LOCKABLE'); -- allowed by process -- incidence setup INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getindex'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postindex'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getincidencecategory'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getlevelsetup'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postlevelsetup'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@createarrayforincidencelevel'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getincidencelevelsbyschoolyearsection'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postsavecategories'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getfrequencysetup'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@crearopcionesfrecuencia'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@postfrequencysetup'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getfrequenciessetup'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@checksubcategoryincidencestudenttotals'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@validarsesion'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES (659,'incidencesetupcontroller@getcategoriesbyyear'); -- allowed incidence student INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindex'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getschedule'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getemployeebyserialusr'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getscheduledata'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getschedulebyteacher'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudents'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@studentloaddata'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudentsadmin'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@poststudentincidencesrequirementload'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@validateoptionalsubjectincourseschedule'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@validatedatemaxtime'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getstudentincidencesload'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindexguardian'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getstudentsbyschoolyear'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getperiodsbystudentschoolyear'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getincidencesbystudentyearperiod'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getindexadmin'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getsectionlevelspecialtybyyear'); INSERT INTO allowed_pages_by_process (serial_prc,url_dsc) VALUES ((select serial_prc from process where link_prc= 'laravel/public/homeTeacher/index'),'incidencestudentcontroller@getteachersbyyearsectionlevelspecialty'); ALTER TABLE `incidence_category` CHANGE `name_ica` `name_ica` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; ALTER TABLE `incidence_student` CHANGE `comentary_ist` `comentary_ist` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, CHANGE `comentary_delete_ist` `comentary_delete_ist` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL; -- proceso administracion de incidencias INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('660', '143', 'Administración de Incidencias de Comportamiento', 'laravel/public/incidenceStudent/index-admin', '4', 'LOCKABLE'); -- allowed administracion de incidencias insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getindexadmin'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getsectionlevelspecialtybyyear'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getteachersbyyearsectionlevelspecialty'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getschedulebyteacher'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@poststudentsadmin'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@poststudentincidencesrequirementload'); insert into allowed_pages_by_process values(660,'incidencestudentcontroller@getstudentincidencesload'); ALTER TABLE `incidence_student_summary` ADD `serial_sbp` INT(11) NOT NULL AFTER `serial_std`; ALTER TABLE `incidence_student_summary` ADD KEY `fk_incidence_student_summary_subperiod_idx` (`serial_sbp`); ALTER TABLE `incidence_student_summary` ADD CONSTRAINT `fk_incidence_student_summary_subperiod_idx` FOREIGN KEY (`serial_sbp`) REFERENCES `subperiod`(`serial_sbp`);