/*
**************************************************************************************
                UTS MBD 17FX

FIRLI AFRISA (17312296)
KHOLID AINI   (17312261)

                 INFORMATIKA 17 FX
**************************************************************************************

*/


/*TRIGGER NO HP DAN NAMA
*/
DELIMITER $$

USE `securitypractice`$$

DROP TRIGGER /*!50032 IF EXISTS */ `b_insert-nohp&&ceknama`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `b_insert-nohp&&ceknama` BEFORE INSERT ON `employee`
    FOR EACH ROW BEGIN

/*kondisi nomor hp <10 atau >12 st*/
    IF(LENGTH(new.epe_noHape) <10 OR LENGTH(new.epe_noHape) > 12) THEN
    SET NEW.epe_noHape=NULL;
    END IF;

/*kondisi awalan st*/
    IF(SUBSTRING(new.epe_name,1,2) = "st") THEN
    INSERT INTO audit_employee
    SET audit_employee.epe_oldName = CONCAT(SUBSTRING(new.epe_name,1,2),SUBSTRING(new.epe_name,3)),
    audit_employee.epe_newName = CONCAT("Siti",SUBSTRING(new.epe_name,3)),
    new.epe_name =CONCAT("Siti",SUBSTRING(new.epe_name,3)),
    audit_employee.epe_nik  = new.epe_nik,
    audit_employee.aee_action ="Updates",
    audit_employee.aee_chDate = NOW();
    END IF;


/*kondisi awalan a or a.*/

    IF(SUBSTRING(new.epe_name,1,2) = "a " OR SUBSTRING(new.epe_name,1,2) = "a." ) THEN
    INSERT INTO audit_employee
    SET audit_employee.epe_oldName = CONCAT(SUBSTRING(new.epe_name,1,2),SUBSTRING(new.epe_name,3)),
    audit_employee.epe_newName = CONCAT("Abdul", SUBSTRING(new.epe_name,3)),
    new.epe_name =CONCAT("Abdul", SUBSTRING(new.epe_name,3)),
    audit_employee.epe_nik  = new.epe_nik,
    audit_employee.aee_action ="Updates",
    audit_employee.aee_chDate = NOW();
    END IF;

/*===========================kondisi awalan m atau m. dan muh atau muh.===============================*/
    IF(SUBSTRING(new.epe_name,1,2) = "m " OR SUBSTRING(new.epe_name,1,2) = "m." ) THEN
    INSERT INTO audit_employee
    SET audit_employee.epe_oldName = CONCAT(SUBSTRING(new.epe_name,1,2),SUBSTRING(new.epe_name,3)),
    audit_employee.epe_newName = CONCAT("Muhammad", SUBSTRING(new.epe_name,3)),
    new.epe_name =CONCAT("Muhammad", SUBSTRING(new.epe_name,3)),
    audit_employee.epe_nik  = new.epe_nik,
    audit_employee.aee_action ="Updates",
    audit_employee.aee_chDate = NOW();
    END IF;

    IF(SUBSTRING(new.epe_name,1,4) = "muh " OR SUBSTRING(new.epe_name,1,4) = "muh." ) THEN
    INSERT INTO audit_employee
    SET audit_employee.epe_oldName = CONCAT(SUBSTRING(new.epe_name,1,4),SUBSTRING(new.epe_name,5)),
    audit_employee.epe_newName = CONCAT("Muhammad", SUBSTRING(new.epe_name,5)),
    new.epe_name =CONCAT("Muhammad", SUBSTRING(new.epe_name,5)),
    audit_employee.epe_nik  = new.epe_nik,
    audit_employee.aee_action ="Updates",
    audit_employee.aee_chDate = NOW();
    END IF;

        /*
    IF(SUBSTRING(new.epe_name,1,4) = "abd " OR SUBSTRING(new.epe_name,1,4) = "abd." ) THEN
    SET new.epe_name = CONCAT("Abdul",SUBSTRING(new.epe_name,5));
    END IF;*/
    END;
$$

DELIMITER ;

/*INPUT KE TABEL EMPLOYEE*/

INSERT INTO `securitypractice`.`employee` (
  `epe_nik`,
  `epe_name`,
  `epe_tglLahir`,
  `epe_tglGabung`,
  `epe_noHape`,
  `usr_id`
)
VALUES
  (
    '324568',
    'Hadi Gusnanto',
    '1989-02-03',
    '2012-12-15',
    '0821382784',
    '25638'
  ),
  (
    '324569',
    'M. Solihin',
    '1993-07-24',
    '2011-05-13',
    '085827186378',
    '45372'
  );

/*MEMBERIKAN AKSES INACTIVE PADA BUDI SETIAWAN*/
UPDATE usertab SET `usr_status`="INACTIVE",`usr_statusDate`="2018-02-08" WHERE `usr_name`="Budi Setiawan"