ALTER TABLE `travel_attendance`
ADD COLUMN `register_type` ENUM('ONTRACK', 'PPCARNET', 'PPNOCARNET') NULL AFTER `exit_type`,
ADD COLUMN `ontrack_date_register` datetime NULL AFTER `register_type`,
ADD COLUMN `ontrack_file` text NULL AFTER `ontrack_date_register`;

ALTER TABLE `travel_attendance`
ADD COLUMN `serial_usr_register_ontrack` INT NULL AFTER `serial_trs`,
ADD INDEX `fk_tat_usr_ontrack_idx` (`serial_usr_register_ontrack` ASC);

ALTER TABLE `travel_attendance`
ADD CONSTRAINT `fk_tat_usr`
  FOREIGN KEY (`serial_usr_register_ontrack`)
  REFERENCES `user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `travel_attendance`
CHANGE COLUMN `ontrack_file` `ontrack_file` TEXT NULL DEFAULT NULL ;

-- Pantallas ontrack para transporte
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('722', '438', 'Ontrack', '#', '0.99', 'LOCKABLE');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('725', '722', 'Importar recorridos ontrack', 'laravel/public/ontrack', '1', 'LOCKABLE');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('723', '438', 'Autorizar Salida/Entrada sin carnet', 'laravel/public/travelattendancewithoutcarnet/exit', '1.50', 'LOCKABLE');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('724', '722', 'Resumen ontrack', 'laravel/public/ontrackReport', '4.20', 'LOCKABLE');

-- Allowed pages
INSERT INTO allowed_pages_by_process VALUES (725,'ontrackfilemanagecontroller@getindex');
INSERT INTO allowed_pages_by_process VALUES (725,'ontrackfilemanagecontroller@postproccesfile');

INSERT INTO allowed_pages_by_process VALUES (723,'travelattendancewithoutcarnetcontroller@getexit');
INSERT INTO allowed_pages_by_process VALUES (723,'travelattendancewithoutcarnetcontroller@postexit');
INSERT INTO allowed_pages_by_process VALUES (723,'extracurricularactivitycontroller@getsections');


INSERT INTO allowed_pages_by_process VALUES (724,'ontrackreportcontroller@getindex');
INSERT INTO allowed_pages_by_process VALUES (724,'ontrackreportcontroller@getgeneralreport');
INSERT INTO allowed_pages_by_process VALUES (724,'ontrackreportcontroller@getstudentreport');
INSERT INTO allowed_pages_by_process VALUES (724,'ontrackreportcontroller@postsearchstudent');

SET SQL_SAFE_UPDATES = 0;
update travel_attendance set register_type='PPCARNET' where date >='2019-09-01' and door_time ; -- and serial_trs in(select serial_trs from travel_student where serial_trv in(400,475,476,477));
SET SQL_SAFE_UPDATES = 1;
insert into allowed_pages_by_process values (723,'travelattendancewithoutcarnetcontroller@postsearchstudent');
insert into allowed_pages_by_process values (723,'travelattendancewithoutcarnetcontroller@postsavetravelattendance');


-- cambios para facturación

CREATE TABLE `payment_combine_year` (
  `serial_pcy` INT NOT NULL AUTO_INCREMENT,
  `serial_ypc_current_year` INT NULL,
  `serial_ypc_next_year` INT NULL,
  `status_pcy` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_pcy`),
  INDEX `fk_ypc_current_year_idx` (`serial_ypc_current_year` ASC),
  INDEX `fk_ypc_next_year_idx` (`serial_ypc_next_year` ASC),
  CONSTRAINT `fk_ypc_current_year`
    FOREIGN KEY (`serial_ypc_current_year`)
    REFERENCES `year_level_specialty_payment_concept` (`serial_ypc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ypc_next_year`
    FOREIGN KEY (`serial_ypc_next_year`)
    REFERENCES `year_level_specialty_payment_concept` (`serial_ypc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
	ALTER TABLE `year_level_specialty_payment_concept` 
CHANGE COLUMN `type_payment_concept_ypc` `type_payment_concept_ypc` ENUM('TRANSPORT', 'LIFE_INSURANCE', 'UNEMPLOYMENT_INSURANCE', 'ACCCIDENT_INSURANCE', 'SCHOOL_SUPPLIES', 'SUPPLIES', 'REGISTER', 'OTHER') NULL DEFAULT NULL ;

CREATE TABLE `student_register_school_year` (
  `serial_srs` INT NOT NULL AUTO_INCREMENT,
  `serial_ypc` INT NULL,
  `serial_scy` INT NULL,
  `serial_pym` INT NULL,
  `serial_fin` INT NULL,
  `serial_fic` INT NULL,
  `status_srs` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_srs`),
  INDEX `fk_srs_ypc_idx` (`serial_ypc` ASC),
  INDEX `fk_srs_scy_idx` (`serial_scy` ASC),
  INDEX `fk_srs_pym_idx` (`serial_pym` ASC),
  INDEX `fk_srs_fin_idx` (`serial_fin` ASC),
  INDEX `fk_srs_fic_idx` (`serial_fic` ASC),
  CONSTRAINT `fk_srs_ypc`
    FOREIGN KEY (`serial_ypc`)
    REFERENCES `year_level_specialty_payment_concept` (`serial_ypc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_srs_scy`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_srs_pym`
    FOREIGN KEY (`serial_pym`)
    REFERENCES `payment_method` (`serial_pym`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_srs_fin`
    FOREIGN KEY (`serial_fin`)
    REFERENCES `financial_institution` (`serial_fin`)
    ON DELETE NO ACTION);
    
    ALTER TABLE `student_register_school_year` 
ADD COLUMN `created_at` DATETIME NULL AFTER `status_srs`,
ADD COLUMN `updated_at` DATETIME NULL AFTER `created_at`;
ALTER TABLE `student_register_school_year` 
ADD CONSTRAINT `fk_srs_fic`
  FOREIGN KEY (`serial_fic`)
  REFERENCES `financial_institution_card` (`serial_fic`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

	
	ALTER TABLE `student_register_school_year` 
ADD COLUMN `serial_std` INT NULL AFTER `serial_fic`,
ADD INDEX `fk_srs_std_idx` (`serial_std` ASC);
ALTER TABLE `student_register_school_year` 
ADD CONSTRAINT `fk_srs_std`
  FOREIGN KEY (`serial_std`)
  REFERENCES `student` (`serial_std`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('726', '156', 'Registro de forma de pago para matricula', 'laravel/public/register', '1', 'LOCKABLE');
insert into allowed_pages_by_process values(726,'registercontroller@getindex');
insert into allowed_pages_by_process values(726,'registercontroller@getstudents');
insert into allowed_pages_by_process values(726,'registercontroller@getfinancialinstitutions');
insert into allowed_pages_by_process values(726,'registercontroller@postsave');
insert into allowed_pages_by_process values(726,'registercontroller@getLastthreemonths');
insert into allowed_pages_by_process values(726,'sectioncontroller@getsectionsbyschoolyearoptionsall');
insert into allowed_pages_by_process values(726,'coursecontroller@getallbysectionoptionsall');

ALTER TABLE `student_register_school_year` 
ADD COLUMN `student_srs` VARCHAR(500) NULL AFTER `serial_std`;

ALTER TABLE `student_register_school_year` 
ADD COLUMN `owner_srs` VARCHAR(500) NULL AFTER `student_srs`,
ADD COLUMN `id_srs` VARCHAR(45) NULL AFTER `owner_srs`,
ADD COLUMN `credit_card_srs` VARCHAR(100) NULL AFTER `id_srs`;


INSERT INTO `recap_header` (`serial_rch`, `serial_pym`, `serial_ccd`, `serial_fin`, `authorization_code_rch`) VALUES ('1', '1', '1', '11', '499625');
CREATE TABLE `recap_student_register_school_year` (
  `serial_rss` INT NOT NULL AUTO_INCREMENT,
  `serial_rcp` INT NULL,
  `serial_srs` INT NULL,
  `payment_date_rss` DATETIME NULL,
  `number_voucher_rss` INT NULL,
  `number_ticket_rss` VARCHAR(45) NULL,
  `status_recap_rss` ENUM('PENDING', 'INDICTED', 'REJECTED') NULL,
  `status_rss` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_rss`),
  INDEX `fk_rss_rcp_idx` (`serial_rcp` ASC),
  CONSTRAINT `fk_rss_srs`
    FOREIGN KEY (`serial_srs`)
    REFERENCES `student_register_school_year` (`serial_srs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rss_rcp`
    FOREIGN KEY (`serial_rcp`)
    REFERENCES `recap` (`serial_rcp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `recap_student_register_school_year` 
ADD COLUMN `status_proccessed_rss` ENUM('YES', 'NO') NULL AFTER `status_recap_rss`;

ALTER TABLE `payment_detail_total_invoice` 
ADD COLUMN `serial_usr_register` INT NULL AFTER `comment_pdt`,
ADD COLUMN `processed_in_safi_pdt` ENUM('YES', 'NO') NULL AFTER `status_pdt`,
ADD COLUMN `proccessed_date_pdt` DATE NULL AFTER `processed_in_safi_pdt`,
ADD COLUMN `processed_serial_usr` INT NULL AFTER `proccessed_date_pdt`,
ADD COLUMN `proccessed_date_register_pdt` DATETIME NULL AFTER `processed_serial_usr`,
ADD INDEX `fk_pdt_usr_resgister_idx` (`serial_usr_register` ASC),
ADD INDEX `fk_pdt_usr_proccesed_idx` (`processed_serial_usr` ASC);
ALTER TABLE payment_detail_total_invoice 
ADD CONSTRAINT `fk_pdt_usr_resgister`
  FOREIGN KEY (`serial_usr_register`)
  REFERENCES user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_pdt_usr_proccesed`
  FOREIGN KEY (`processed_serial_usr`)
  REFERENCES user` (`serial_usr`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('727', '156', 'Archivo de conciliación para matrícula', 'laravel/public/studentdiners', '1.00', 'LOCKABLE');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@getindex');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@poststudents');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@postcreaterecap');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@poststudentrecapproccesfile');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@getrecapfileexcel');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@getrecapfile');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@geteditrecap');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@geteditrecap');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@gettotoldebtbystudentpaymentconceptgrouppaymentmethod');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@getimportrecapresult');
insert into allowed_pages_by_process values(727,'studentdinerscontroller@postimportrecapresult');

INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('728', '156', 'Cambiar código de rubros', 'laravel/public/paymentconcept', '1', 'LOCKABLE');
insert into allowed_pages_by_process values (728,'paymentconceptcontroller@getindex');
insert into allowed_pages_by_process values (728,'paymentconceptcontroller@getpaymentconcpets');
insert into allowed_pages_by_process values (728,'paymentconceptcontroller@postsave');
ALTER TABLE`year_level_specialty_payment_concept` 
CHANGE COLUMN `type_payment_concept_ypc` `type_payment_concept_ypc` ENUM('TRANSPORT', 'LIFE_INSURANCE', 'UNEMPLOYMENT_INSURANCE', 'ACCCIDENT_INSURANCE', 'SCHOOL_SUPPLIES', 'SUPPLIES', 'REGISTER', 'FOOD', 'OTHER') NULL DEFAULT NULL ;
ALTER TABLE `authority_email` 
CHANGE COLUMN `type_auth` `type_auth` SET('BURSAR', 'ADMITION', 'ADDTRAVEL', 'ADDFOOD', 'REMOVETRAVEL', 'REMOVEFOOD') NULL DEFAULT NULL ;
insert into authority_email select  null as serial_ath, serial_scy, serial_set, serial_lvl, 'flbenalcazar@rutatec.com' as email_ath, 'Email al que se envía cuando se quita transporte' as description_ath, 'REMOVETRAVEL' as type_auth, 'ACTIVE' AS status_ath from year_level_specialty_payment_concept;
insert into authority_email select  null as serial_ath, serial_scy, serial_set, serial_lvl, 'flbenalcazar@rutatec.com' as email_ath, 'Email al que se envía cuando se quita rubros de alimentación' as description_ath, 'REMOVEFOOD' as type_auth, 'ACTIVE' AS status_ath from year_level_specialty_payment_concept;
insert into authority_email select  null as serial_ath, serial_scy, serial_set, serial_lvl, 'flbenalcazar@rutatec.com' as email_ath, 'Email al que se envía cuando se aumenta rubros de tranasporte' as description_ath, 'ADDTRAVEL' as type_auth, 'ACTIVE' AS status_ath from year_level_specialty_payment_concept;
insert into authority_email select  null as serial_ath, serial_scy, serial_set, serial_lvl, 'flbenalcazar@rutatec.com' as email_ath, 'Email al que se envía cuando se aumenta rubros de alimentación' as description_ath, 'ADDFOOD' as type_auth, 'ACTIVE' AS status_ath from year_level_specialty_payment_concept;

insert into allowed_pages_by_process values(717,'paymentmanagecontroller@getpaymentfamilydetaildebtexcel');
insert into allowed_pages_by_process values(717,'paymentmanagecontroller@getpaymentfamilydetailexcel');
insert into allowed_pages_by_process values(717,'paymentmanagecontroller@postpaymentfamilydetaildebt');
insert into allowed_pages_by_process values(717,'paymentmanagecontroller@postpaymentfamilydetail');
insert into allowed_pages_by_process values(717,'paymentmanagecontroller@');