ALTER TABLE `student_guardian` 
ADD COLUMN `send_mail_payment_stg` ENUM('YES', 'NO') NULL AFTER `send_copy_messages_stg`;

SET SQL_SAFE_UPDATES = 0;
update `student_guardian` SET `send_mail_payment_stg`='YES' WHERE `relationship_stg`='FATHER' OR`relationship_stg`='MOTHER';
SET SQL_SAFE_UPDATES = 1;

-- actualizacion de la vista
USE `einstein`;
CREATE 
     OR REPLACE ALGORITHM = UNDEFINED 
    DEFINER = `root`@`einstein` 
    SQL SECURITY DEFINER
VIEW `Vista_General_Financiero` AS
    SELECT DISTINCT
        `pdt`.`document_number_pdt` AS `Numero_Documento_de_Pago`,
        `pdt`.`num_check_pdt` AS `Numero_Cheque`,
        `fin`.`name_fin` AS `Institucion_Financiera`,
        `phy`.`serial_phy` AS `Codigo_Factura`,
        `phy`.`subtotal_phy` AS `Subtotal_Factura`,
        `phy`.`taxes_phy` AS `Impuesto_Factura`,
        `phy`.`total_phy` AS `Total_Factura`,
        `phy`.`name_paying_phy` AS `Cliente_Factura`,
        `phy`.`phone_paying_phy` AS `Telefono_Factura`,
        `phy`.`email_paying_phy` AS `MailFactura`,
        `phy`.`month_phy` AS `Mes`,
        `grd`.`serial_grd` AS `Codigo_Representante`,
        `grd`.`id_grd` AS `Cedula_Representante`,
        CONCAT(`grd`.`first_name_grd`,
                ' ',
                `grd`.`last_name_grd`) AS `Nombre_Representante`,
        if( stg.send_mail_payment_stg='YES',`grd`.`email_grd`,'') AS `Mail_Representante`,
        if( stg.send_mail_payment_stg='YES',`grd`.`email2_grd`,'') AS `Mail_Representante2`,
        if( stg.send_mail_payment_stg='YES',`grd`.`email3_grd`,'') AS `Mail_Representante3`,
        `gda`.`main_street_gda` AS `Calle_principal_domicilio_Representante`,
        `gda`.`number_gda` AS `Numero_Domicilio_Representante`,
        `gda`.`secondary_street_gda` AS `Calle_Secundaria_Represetnante`,
        `std`.`serial_std` AS `Serial_Estudiante`,
        `std`.`code_std` AS `Codigo_Estudiante`,
        `std`.`type_std` AS `Tipo_Estudiante`,
        `std`.`id_std` AS `Cedula_Estudiante`,
        CONCAT(`std`.`first_name_std`,
                ' ',
                `std`.`last_name_std`) AS `Nombre_Estudiante`,
        IF((`sty`.`status_sty` = 'REGISTERED'),
            'REGISTRADO',
            'NO REGISTRADO') AS `Estado_Estudiante`,
        `sta`.`main_street_sta` AS `Calle_Principal_Domicilio_Estudiante`,
        `sta`.`number_sta` AS `Numero_domicilio_estudiante`,
        `sta`.`secondary_street_sta` AS `Calle_Secundaria_Domicilio_Estudiante`,
        `scy`.`serial_scy` AS `Codigo_Ano`,
        `scy`.`name_scy` AS `Ano`,
        `sct`.`serial_set` AS `Codigo_Seccion`,
        `sct`.`name_set` AS `Seccion`,
        `lvl`.`serial_lvl` AS `Codigo_Nivel`,
        `lvl`.`name_lvl` AS `Nivel`,
        (SELECT 
                `clg`.`name_clg`
            FROM
                ((`student_by_course` `stcP`
                JOIN `course` `crsP` ON ((`stcP`.`serial_crs` = `crsP`.`serial_crs`)))
                JOIN `class_group` `clg` ON ((`crsP`.`serial_clg` = `clg`.`serial_clg`)))
            WHERE
                ((`std`.`serial_std` = `stcP`.`serial_std`)
                    AND (`scy`.`serial_scy` = `crsP`.`serial_scy`)
                    AND (`crsP`.`status_crs` = 'ACTIVE')
                    AND (`stcP`.`status_stc` = 'ACTIVE')
                    AND (`crsP`.`status_crs` = 'ACTIVE')
                    AND (`clg`.`status_clg` = 'ACTIVE'))) AS `Paralelo`,
        (SELECT 
                `crs`.`serial_crs`
            FROM
                (`student_by_course` `stc`
                JOIN `course` `crs` ON ((`stc`.`serial_crs` = `crs`.`serial_crs`)))
            WHERE
                ((`std`.`serial_std` = `stc`.`serial_std`)
                    AND (`scy`.`serial_scy` = `crs`.`serial_scy`)
                    AND (`crs`.`status_crs` = 'ACTIVE')
                    AND (`stc`.`status_stc` = 'ACTIVE')
                    AND (`crs`.`status_crs` = 'ACTIVE'))) AS `Codigo_Curso`,
        `deb`.`serial_deb` AS `CodigoRubroEstudiante`,
        `sva`.`serial_sva` AS `Codigo_Descuento_Estudiante`,
        `sva`.`serial_ydc` AS `Codigo_Descuento_Ano`,
        `dsc`.`name_dsc` AS `Nombre_Descuento`,
        `sva`.`value_ydv` AS `Valor_Descuento`,
        `ypc`.`serial_ypc` AS `Codigo_Rubro_Ano`,
        `pct`.`serial_pct` AS `Codigo_Catalogo_Concepto_Pago`,
        `pct`.`name_pct` AS `Nombre_Concepto_Pago`,
        `phy`.`taxes_phy` AS `Valor_Impuesto`,
        `ypc`.`value_ypc` AS `Valor_Rubro_Configurado_Nivel`
    FROM
        (((((((((((((((((((((`payment_detail` `pdt`
        JOIN `invoice` `phy` ON (((`pdt`.`serial_phy` = `phy`.`serial_phy`)
            AND (`pdt`.`status_pdt` = 'ACTIVE')
            AND (`phy`.`status_phy` = 'PAID'))))
        JOIN `invoice_detail` `psd` ON (((`phy`.`serial_phy` = `psd`.`serial_phy`)
            AND (`psd`.`status_psd` = 'ACTIVE'))))
        JOIN `student_year_debts` `deb` ON (((`psd`.`serial_deb` = `deb`.`serial_deb`)
            AND (`deb`.`status_deb` <> 'INACTIVE'))))
        JOIN `year_level_specialty_payment_concept` `ypc` ON (((`deb`.`serial_ypc` = `ypc`.`serial_ypc`)
            AND (`ypc`.`status_ypc` = 'ACTIVE'))))
        JOIN `payment_concept` `pct` ON (((`ypc`.`serial_pct` = `pct`.`serial_pct`)
            AND (`pct`.`status_pct` = 'ACTIVE'))))
        JOIN `specialty` `spt` ON (((`ypc`.`serial_spt` = `spt`.`serial_spt`)
            AND (`spt`.`status_spt` = 'ACTIVE'))))
        JOIN `level` `lvl` ON (((`ypc`.`serial_lvl` = `lvl`.`serial_lvl`)
            AND (`lvl`.`status_lvl` = 'ACTIVE'))))
        JOIN `school_year` `scy` ON (((`ypc`.`serial_scy` = `scy`.`serial_scy`)
            AND (`scy`.`status_scy` <> 'INACTIVE'))))
        JOIN `section` `sct` ON (((`ypc`.`serial_set` = `sct`.`serial_set`)
            AND (`sct`.`status_set` = 'ACTIVE'))))
        JOIN `payment_method` `pym` ON (((`pdt`.`serial_pym` = `pym`.`serial_pym`)
            AND (`pym`.`status_pym` = 'ACTIVE'))))
        JOIN `payment_mode` `ptm` ON (((`pym`.`serial_ptm` = `ptm`.`serial_ptm`)
            AND (`ptm`.`status_ptm` = 'ACTIVE'))))
        LEFT JOIN `financial_institution` `fin` ON (((`pdt`.`serial_fin` = `fin`.`serial_fin`)
            AND (`fin`.`status_fin` = 'ACTIVE'))))
        JOIN `student` `std` ON (((`deb`.`serial_std` = `std`.`serial_std`)
            AND (`std`.`status_std` = 'ACTIVE'))))
        JOIN `student_guardian` `stg` ON (((`std`.`serial_std` = `stg`.`serial_std`)
            AND (`stg`.`status_stg` = 'ACTIVE')
            AND (`stg`.`legal_guardian_stg` = 'YES'))))
        JOIN `guardian` `grd` ON (((`stg`.`serial_grd` = `grd`.`serial_grd`)
            AND (`grd`.`status_grd` = 'ACTIVE'))))
        JOIN `student_year` `sty` ON (((`std`.`serial_std` = `sty`.`serial_std`)
            AND (`scy`.`serial_scy` = `sty`.`serial_scy`)
            AND (`sty`.`status_sty` <> 'INACTIVE'))))
        LEFT JOIN `student_year_discount_values` `sva` ON (((`psd`.`serial_sva` = `sva`.`serial_sva`)
            AND (`sva`.`status_ydv` = 'ACTIVE'))))
        LEFT JOIN `year_discount` `ydc` ON (((`sva`.`serial_ydc` = `ydc`.`serial_ydc`)
            AND (`ydc`.`status_ydc` = 'ACTIVE'))))
        LEFT JOIN `discount` `dsc` ON (((`ydc`.`serial_dsc` = `dsc`.`serial_dsc`)
            AND (`dsc`.`status_dsc` = 'ACTIVE'))))
        JOIN `student_address` `sta` ON ((`sta`.`serial_sta` = (SELECT 
                `s`.`serial_sta`
            FROM
                `student_address` `s`
            WHERE
                ((`s`.`status_sta` = 'ACTIVE')
                    AND (`std`.`serial_std` = `s`.`serial_std`))
            LIMIT 1))))
        JOIN `guardian_address` `gda` ON ((`gda`.`serial_gda` = (SELECT 
                `g`.`serial_gda`
            FROM
                `guardian_address` `g`
            WHERE
                ((`g`.`status_gda` = 'ACTIVE')
                    AND (`grd`.`serial_grd` = `g`.`serial_grd`)
                    AND (`g`.`address_type_gda` = 'HOME'))
            LIMIT 1))));

			
	-- Trigger para acutualizar el paramertro send_mail_payment_stg dependiendo de si es padre madre u otro
 delimiter //
CREATE TRIGGER trg_student_guardian BEFORE insert ON student_guardian
     FOR EACH ROW
    BEGIN
     IF NEW.relationship_stg = 'FATHER' THEN
       SET NEW.send_mail_payment_stg = 'YES';
 ELSEIF NEW.relationship_stg= 'MOTHER'THEN
     SET NEW.send_mail_payment_stg = 'YES'; 
     ELSEIF NEW.relationship_stg= 'OTHER'THEN
     SET NEW.send_mail_payment_stg = 'NO';
  END IF;
END;//
 delimiter ;
 
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `status_prc`) VALUES ('669', '156', 'Bandera mails representantes', 'laravel/public/mailpayments', 'LOCKABLE');
insert into allowed_pages_by_process values('669','mailpaymentsguardiancontroller@getindex');
insert into allowed_pages_by_process values('669','mailpaymentsguardiancontroller@poststudents');
insert into allowed_pages_by_process values('669', 'mailpaymentsguardiancontroller@postsave')
 
 USE `einstein`;
CREATE 
     OR REPLACE ALGORITHM = UNDEFINED 
    DEFINER = `root`@`einstein` 
    SQL SECURITY DEFINER
VIEW `Vista_General_Financiero` AS
    SELECT DISTINCT
        `pdt`.`document_number_pdt` AS `Numero_Documento_de_Pago`,
        `pdt`.`num_check_pdt` AS `Numero_Cheque`,
        `fin`.`name_fin` AS `Institucion_Financiera`,
        `phy`.`serial_phy` AS `Codigo_Factura`,
        `phy`.`subtotal_phy` AS `Subtotal_Factura`,
        `phy`.`taxes_phy` AS `Impuesto_Factura`,
        `phy`.`total_phy` AS `Total_Factura`,
        `phy`.`name_paying_phy` AS `Cliente_Factura`,
        `phy`.`phone_paying_phy` AS `Telefono_Factura`,
        `phy`.`email_paying_phy` AS `MailFactura`,
        `phy`.`month_phy` AS `Mes`,
        `grd`.`serial_grd` AS `Codigo_Representante`,
        `grd`.`id_grd` AS `Cedula_Representante`,
        CONCAT(`grd`.`first_name_grd`,
                ' ',
                `grd`.`last_name_grd`) AS `Nombre_Representante`,
         if( stg.send_mail_payment_stg='YES',`grd`.`email_grd`,'') AS `Mail_Representante`,
        if( stg.send_mail_payment_stg='YES',`grd`.`email2_grd`,'') AS `Mail_Representante2`,
        if( stg.send_mail_payment_stg='YES',`grd`.`email3_grd`,'') AS `Mail_Representante3`,
        `gda`.`main_street_gda` AS `Calle_principal_domicilio_Representante`,
        `gda`.`number_gda` AS `Numero_Domicilio_Representante`,
        `gda`.`secondary_street_gda` AS `Calle_Secundaria_Represetnante`,
        `std`.`serial_std` AS `Serial_Estudiante`,
        `std`.`code_std` AS `Codigo_Estudiante`,
        `std`.`type_std` AS `Tipo_Estudiante`,
        `std`.`id_std` AS `Cedula_Estudiante`,
        CONCAT(`std`.`first_name_std`,
                ' ',
                `std`.`last_name_std`) AS `Nombre_Estudiante`,
        IF((`sty`.`status_sty` = 'REGISTERED'),
            'REGISTRADO',
            'NO REGISTRADO') AS `Estado_Estudiante`,
        `sta`.`main_street_sta` AS `Calle_Principal_Domicilio_Estudiante`,
        `sta`.`number_sta` AS `Numero_domicilio_estudiante`,
        `sta`.`secondary_street_sta` AS `Calle_Secundaria_Domicilio_Estudiante`,
        `scy`.`serial_scy` AS `Codigo_Ano`,
        `scy`.`name_scy` AS `Ano`,
        `sct`.`serial_set` AS `Codigo_Seccion`,
        `sct`.`name_set` AS `Seccion`,
        `lvl`.`serial_lvl` AS `Codigo_Nivel`,
        `lvl`.`name_lvl` AS `Nivel`,
        (SELECT 
                `clg`.`name_clg`
            FROM
                ((`student_by_course` `stcP`
                JOIN `course` `crsP` ON ((`stcP`.`serial_crs` = `crsP`.`serial_crs`)))
                JOIN `class_group` `clg` ON ((`crsP`.`serial_clg` = `clg`.`serial_clg`)))
            WHERE
                ((`std`.`serial_std` = `stcP`.`serial_std`)
                    AND (`scy`.`serial_scy` = `crsP`.`serial_scy`)
                    AND (`crsP`.`status_crs` = 'ACTIVE')
                    AND (`stcP`.`status_stc` = 'ACTIVE')
                    AND (`crsP`.`status_crs` = 'ACTIVE')
                    AND (`clg`.`status_clg` = 'ACTIVE'))) AS `Paralelo`,
        (SELECT 
                `crs`.`serial_crs`
            FROM
                (`student_by_course` `stc`
                JOIN `course` `crs` ON ((`stc`.`serial_crs` = `crs`.`serial_crs`)))
            WHERE
                ((`std`.`serial_std` = `stc`.`serial_std`)
                    AND (`scy`.`serial_scy` = `crs`.`serial_scy`)
                    AND (`crs`.`status_crs` = 'ACTIVE')
                    AND (`stc`.`status_stc` = 'ACTIVE')
                    AND (`crs`.`status_crs` = 'ACTIVE'))) AS `Codigo_Curso`,
        `deb`.`serial_deb` AS `CodigoRubroEstudiante`,
        `sva`.`serial_sva` AS `Codigo_Descuento_Estudiante`,
        `sva`.`serial_ydc` AS `Codigo_Descuento_Ano`,
        `dsc`.`name_dsc` AS `Nombre_Descuento`,
        `sva`.`value_ydv` AS `Valor_Descuento`,
        `ypc`.`serial_ypc` AS `Codigo_Rubro_Ano`,
        `pct`.`serial_pct` AS `Codigo_Catalogo_Concepto_Pago`,
        `pct`.`name_pct` AS `Nombre_Concepto_Pago`,
        `phy`.`taxes_phy` AS `Valor_Impuesto`,
        `ypc`.`value_ypc` AS `Valor_Rubro_Configurado_Nivel`
    FROM
        (((((((((((((((((((((`payment_detail` `pdt`
        JOIN `invoice` `phy` ON (((`pdt`.`serial_phy` = `phy`.`serial_phy`)
            AND (`pdt`.`status_pdt` = 'ACTIVE')
            AND (`phy`.`status_phy` = 'PAID'))))
        JOIN `invoice_detail` `psd` ON (((`phy`.`serial_phy` = `psd`.`serial_phy`)
            AND (`psd`.`status_psd` = 'ACTIVE'))))
        JOIN `student_year_debts` `deb` ON (((`psd`.`serial_deb` = `deb`.`serial_deb`)
            AND (`deb`.`status_deb` <> 'INACTIVE'))))
        JOIN `year_level_specialty_payment_concept` `ypc` ON (((`deb`.`serial_ypc` = `ypc`.`serial_ypc`)
            AND (`ypc`.`status_ypc` = 'ACTIVE'))))
        JOIN `payment_concept` `pct` ON (((`ypc`.`serial_pct` = `pct`.`serial_pct`)
            AND (`pct`.`status_pct` = 'ACTIVE'))))
        JOIN `specialty` `spt` ON (((`ypc`.`serial_spt` = `spt`.`serial_spt`)
            AND (`spt`.`status_spt` = 'ACTIVE'))))
        JOIN `level` `lvl` ON (((`ypc`.`serial_lvl` = `lvl`.`serial_lvl`)
            AND (`lvl`.`status_lvl` = 'ACTIVE'))))
        JOIN `school_year` `scy` ON (((`ypc`.`serial_scy` = `scy`.`serial_scy`)
            AND (`scy`.`status_scy` <> 'INACTIVE'))))
        JOIN `section` `sct` ON (((`ypc`.`serial_set` = `sct`.`serial_set`)
            AND (`sct`.`status_set` = 'ACTIVE'))))
        JOIN `payment_method` `pym` ON (((`pdt`.`serial_pym` = `pym`.`serial_pym`)
            AND (`pym`.`status_pym` = 'ACTIVE'))))
        JOIN `payment_mode` `ptm` ON (((`pym`.`serial_ptm` = `ptm`.`serial_ptm`)
            AND (`ptm`.`status_ptm` = 'ACTIVE'))))
        LEFT JOIN `financial_institution` `fin` ON (((`pdt`.`serial_fin` = `fin`.`serial_fin`)
            AND (`fin`.`status_fin` = 'ACTIVE'))))
        JOIN `student` `std` ON (((`deb`.`serial_std` = `std`.`serial_std`)
            AND (`std`.`status_std` = 'ACTIVE'))))
        JOIN `student_guardian` `stg` ON (((`std`.`serial_std` = `stg`.`serial_std`)
            AND (`stg`.`status_stg` = 'ACTIVE')
            AND (`stg`.`legal_guardian_stg` = 'YES'))))
        JOIN `guardian` `grd` ON (((`stg`.`serial_grd` = `grd`.`serial_grd`)
            AND (`grd`.`status_grd` = 'ACTIVE'))))
        JOIN `student_year` `sty` ON (((`std`.`serial_std` = `sty`.`serial_std`)
            AND (`scy`.`serial_scy` = `sty`.`serial_scy`)
            AND (`sty`.`status_sty` <> 'INACTIVE'))))
        LEFT JOIN `student_year_discount_values` `sva` ON (((`psd`.`serial_sva` = `sva`.`serial_sva`)
            AND (`sva`.`status_ydv` = 'ACTIVE'))))
        LEFT JOIN `year_discount` `ydc` ON (((`sva`.`serial_ydc` = `ydc`.`serial_ydc`)
            AND (`ydc`.`status_ydc` = 'ACTIVE'))))
        LEFT JOIN `discount` `dsc` ON (((`ydc`.`serial_dsc` = `dsc`.`serial_dsc`)
            AND (`dsc`.`status_dsc` = 'ACTIVE'))))
        JOIN `student_address` `sta` ON ((`sta`.`serial_sta` = (SELECT 
                `s`.`serial_sta`
            FROM
                `student_address` `s`
            WHERE
                ((`s`.`status_sta` = 'ACTIVE')
                    AND (`std`.`serial_std` = `s`.`serial_std`))
            LIMIT 1))))
        JOIN `guardian_address` `gda` ON ((`gda`.`serial_gda` = (SELECT 
                `g`.`serial_gda`
            FROM
                `guardian_address` `g`
            WHERE
                ((`g`.`status_gda` = 'ACTIVE')
                    AND (`grd`.`serial_grd` = `g`.`serial_grd`)
                    AND (`g`.`address_type_gda` = 'HOME'))
            LIMIT 1))));