CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_after_insert` AFTER INSERT ON `emp` FOR EACH ROW BEGIN

DECLARE vUser varchar(50);
DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE emp_photoname varchar(95);

IF(NEW.updater IS NULL)THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updater);
END IF;
set url=CONCAT('<a href="','',(select table_summary from look_table where table_name="file_url"));
set furl=(select table_summary from look_table where table_name="file_url");
set emp_photoname=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage));

 
INSERT INTO  log_global
SET
last_update    =   now(),
updater        =   vUser,
table_update   =   'emp',
actions        =   'A',
table_id       =   CONCAT('emp_id', '.',NEW.emp_id), 
col1           =   CONCAT('firstname.',NEW.firstname), 
col2           =   CONCAT('lastname.',NEW.lastname),
col4           =   CONCAT('nickname.',NEW.nickname), 
col5           =   CONCAT('ssn.',NEW.ssn),
col7           =   CONCAT('dob.',date_format(NEW.dob,'%m-%d-%y')),
col8           =   CONCAT('gender','.',(select look_values from look_values where look_values_id=NEW.gender)),
col10          =   CONCAT('taxid.',NEW.taxid),
col13          =   CONCAT('emp_photo', '.',CONCAT(url,'',(select concat(file_name,'">',emp_photoname,'</a>') from storages where idstorage=NEW.idstorage)));

END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_after_update` AFTER UPDATE ON `emp` FOR EACH ROW begin
 
DECLARE oldfile_name varchar(100);
DECLARE newfile_name varchar(100);
DECLARE oldssn varchar(20);
DECLARE oldnick varchar(5);
DECLARE olddob date;
DECLARE oldgender int(5);
DECLARE oldtaxid varchar(45);
DECLARE oldsto_limit bigint(45);
DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE logoname varchar(95);
DECLARE oldlogoname varchar(95);
DECLARE cmp_oldfile_name varchar(100);
DECLARE cmp_newfile_name varchar(100);

set url=CONCAT('<a href="','',(select table_summary from look_table where table_name="file_url"));
set furl=(select table_summary from look_table where table_name="file_url");
set logoname=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage));
set oldlogoname=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));

IF OLD.ssn IS NULL THEN SET oldssn = ''; ELSE SET oldssn = OLD.ssn;	END IF;
IF OLD.nickname IS NULL THEN SET oldnick = ''; ELSE SET oldnick = OLD.nickname;	END IF;
IF OLD.dob IS NULL THEN SET olddob = ''; ELSE SET olddob = OLD.dob;	END IF;
IF OLD.gender IS NULL THEN SET oldgender = ''; ELSE SET oldgender = OLD.gender;	END IF;
IF OLD.taxid IS NULL THEN SET oldtaxid = ''; ELSE SET oldtaxid = OLD.taxid;	END IF;
IF OLD.sto_limit IS NULL THEN SET oldsto_limit = ''; ELSE SET oldsto_limit = OLD.sto_limit;	END IF;


IF OLD.idstorage IS NOT NULL THEN
set oldfile_name=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));
set cmp_oldfile_name=(select file_name from storages where idstorage=OLD.idstorage);

END IF;

IF OLD.idstorage IS NULL THEN
set oldfile_name="";
set cmp_oldfile_name="";

END IF;

IF NEW.idstorage IS NOT NULL THEN
set newfile_name=concat(url,'',(select concat(file_name,'">',logoname,'</a>') from storages where idstorage=NEW.idstorage));
set cmp_newfile_name=(select file_name from storages where idstorage=NEW.idstorage);

END IF;

IF NEW.idstorage IS NULL THEN
set newfile_name="";
set cmp_newfile_name="";
END IF;

			
IF((NEW.firstname!=OLD.firstname)OR(NEW.lastname!=OLD.lastname)OR(NEW.nickname!=oldnick)OR(NEW.ssn!=oldssn)OR(NEW.dob!=olddob)OR(NEW.gender!=oldgender)OR(cmp_oldfile_name!=cmp_newfile_name)OR(NEW.taxid!=oldtaxid)OR(NEW.sto_limit!=oldsto_limit))THEN

INSERT INTO log_global 
set
last_update 	= now(),
updater         = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (OLD.updater IS NULL) then 'Admin' else OLD.updater end)),
table_update    = 'emp',
actions		= 'O',	
table_id	= CONCAT('emp_id', '.',OLD.emp_id),
col1            = CONCAT('firstname.',OLD.firstname),
col2            = CONCAT('lastname.',OLD.lastname),
col4            = CONCAT('nickname.',OLD.nickname),
col5            = CONCAT('ssn.',OLD.ssn),
col7            = CONCAT('dob.',date_format(OLD.dob,'%m-%d-%y')),
col8            = CONCAT('gender','.',(select looK_values from look_values where look_values_id=OLD.gender)),
col10           = CONCAT('taxid.',OLD.taxid),
col13           = CONCAT('emp_photo', '.',oldfile_name);



INSERT INTO log_global 
set
last_update 	       = now(),
updater                = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.updater IS NULL) then 'Admin' else NEW.updater end)),
table_update           = 'emp',
actions		       = 'N',	
table_id	       = CONCAT('emp_id', '.',NEW.emp_id),
col1                   = CONCAT('firstname.',NEW.firstname),
col2                   = CONCAT('lastname.',NEW.lastname),
col4                   = CONCAT('nickname.',NEW.nickname),
col5                   = CONCAT('ssn.',NEW.ssn),
col7                   = CONCAT('dob.',date_format(NEW.dob,'%m-%d-%y')),
col8                   = CONCAT('gender','.',(select looK_values from look_values where look_values_id=NEW.gender)),
col10                  = CONCAT('taxid.',NEW.taxid),
col13                  = CONCAT('emp_photo', '.',newfile_name);
  
END IF; 

END








CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_after_delete` AFTER DELETE ON `emp` FOR EACH ROW BEGIN
 
DECLARE vUser varchar(50);
DECLARE furl varchar(95);
DECLARE oldlogoname varchar(95);

IF(OLD.updater IS NULL)THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updater);
END IF; 

set furl=(select table_summary from look_table where table_name="file_url");
set oldlogoname=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));
 
INSERT INTO  log_global
SET
last_update    =   now(),
updater        =   vUser,
table_update   =   'emp',
actions        =   'D',
table_id       =   CONCAT('emp_id', '.',OLD.emp_id), 
col1           =   CONCAT('firstname.',OLD.firstname), 
col2           =   CONCAT('lastname.',OLD.lastname),
col4           =   CONCAT('nickname.',OLD.nickname),  
col5           =   CONCAT('ssn.',OLD.ssn),
col7           =   CONCAT('dob.',date_format(OLD.dob,'%m-%d-%y')),
col8           =   CONCAT('gender','.',(select looK_values from look_values where look_values_id=OLD.gender)),
col10          =   CONCAT('taxid.',OLD.taxid),
col13          =   CONCAT('emp_photo', '.',oldlogoname);

END