CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `immi_receipts_after_insert` AFTER INSERT ON `immi_receipts` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
DECLARE myocc_code varchar(45);
DECLARE myskill_level varchar(45);
DECLARE myeducation varchar(45);
DECLARE myexp_years varchar(45);
DECLARE myrec_status varchar(45);
DECLARE mysalary_amt varchar(45);
DECLARE myno_days varchar(45);
DECLARE myimmi_summary varchar(45);
DECLARE myimmi_source varchar(45);
DECLARE myvalid_days varchar(45);


IF(NEW.emp_id='0')THEN
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id=1);
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.emp_id);
END IF;
 
 
set myocc_code=(select look_values from look_values where look_values_id=NEW.occ_code);
set myskill_level=(select look_values from look_values where look_values_id=NEW.skill_level);
set myeducation=(select look_values from look_values where look_values_id=NEW.education);
set myexp_years=(select look_values from look_values where look_values_id=NEW.exp_years);
set myrec_status=(select look_values from look_values where look_values_id=NEW.rec_status);
set mysalary_amt=(select look_values from look_values where look_values_id=NEW.salary_amt);
set myimmi_source=(select look_values from look_values where look_values_id=NEW.immi_source);
set myno_days=NEW.no_days;
set myimmi_summary=NEW.immi_summary;
set myvalid_days=NEW.valid_days;

IF myvalid_days IS NULL THEN SET myvalid_days = ''; ELSE SET myvalid_days = myvalid_days;	END IF;
IF myimmi_source IS NULL THEN SET myimmi_source = ''; ELSE SET myimmi_source = myimmi_source;	END IF;
IF myimmi_summary IS NULL THEN SET myimmi_summary = ''; ELSE SET myimmi_summary = myimmi_summary;	END IF;
IF myno_days IS NULL THEN SET myno_days = ''; ELSE SET myno_days = myno_days;	END IF;
IF myskill_level IS NULL THEN SET myskill_level = ''; ELSE SET myskill_level = myskill_level;	END IF;
IF myeducation IS NULL THEN SET myeducation = ''; ELSE SET myeducation = myeducation;	END IF;
IF myexp_years IS NULL THEN SET myexp_years = ''; ELSE SET myexp_years = myexp_years;	END IF;
IF myrec_status IS NULL THEN SET myrec_status = ''; ELSE SET myrec_status = myrec_status;	END IF;
IF mysalary_amt IS NULL THEN SET mysalary_amt = ''; ELSE SET mysalary_amt = mysalary_amt;	END IF;


INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'immi_receipts',
actions			= 'A',	
table_id		= CONCAT('receipt_id', '.',NEW.receipt_id),
col1			= CONCAT('comp_name', '.',(select companyname from company where comp_id=NEW.comp_id)),

col2			= CONCAT('receipt_no', '.',NEW.receipt_no),
col3			= CONCAT('filed_dt', '.',date_format(NEW.filed_dt,'%m-%d-%y')),

col4			= CONCAT('ad_title', '.',(select look_values from look_values where look_values_id=NEW.ad_title)),

col5			= CONCAT('receipt_type', '.',NEW.receipt_type,'\n','from_dt', '.',date_format(NEW.from_dt,'%m-%d-%y'),'\n','to_dt', '.',date_format(NEW.to_dt,'%m-%d-%y')),
col6			= CONCAT('skill_level', '.',myskill_level,'\n','occ_code',myocc_code),

col7			= CONCAT('exp_years',myexp_years),



col8			= CONCAT('valid_days', '.',myvalid_days,'\n','expire_dt', '.',date_format(NEW.expire_dt,'%m-%d-%y')),

col9			= CONCAT('education', '.',myeducation),

col10			= CONCAT('immi_source', '.',myimmi_source),


col11			= CONCAT('rec_status', '.',myrec_status,'\n','salary_amt','.',mysalary_amt),
col12			= CONCAT('stat_st_dt', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')),
col13			= CONCAT('stat_e_dt', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')),

col14			= CONCAT('no_days', '.',myno_days,'\n','immi_summary', '.',myimmi_summary),

col15			= CONCAT('idlook_stat_order', '.',NEW.idlook_stat_order);


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `immi_receipts_after_update` AFTER UPDATE ON `immi_receipts` FOR EACH ROW begin

DECLARE myocc_code varchar(45);
DECLARE myskill_level varchar(45);
DECLARE myeducation varchar(45);
DECLARE myexp_years varchar(45);
DECLARE myrec_status varchar(45);
DECLARE mysalary_amt varchar(45);
DECLARE myno_days varchar(45);
DECLARE myimmi_summary varchar(45);
DECLARE myimmi_source varchar(45);
DECLARE myvalid_days varchar(45);
DECLARE oldmyocc_code varchar(45);
DECLARE oldmyskill_level varchar(45);
DECLARE oldmyeducation varchar(45);
DECLARE oldmyexp_years varchar(45);
DECLARE oldmyrec_status varchar(45);
DECLARE oldmysalary_amt varchar(45);
DECLARE oldmyno_days varchar(45);
DECLARE oldmyimmi_summary varchar(45);
DECLARE oldmyimmi_source varchar(45);
DECLARE oldmyvalid_days varchar(45);

 
 
 
set myocc_code=(select look_values from look_values where look_values_id=NEW.occ_code);
set myskill_level=(select look_values from look_values where look_values_id=NEW.skill_level);
set myeducation=(select look_values from look_values where look_values_id=NEW.education);
set myexp_years=(select look_values from look_values where look_values_id=NEW.exp_years);
set myrec_status=(select look_values from look_values where look_values_id=NEW.rec_status);
set mysalary_amt=(select look_values from look_values where look_values_id=NEW.salary_amt);
set myimmi_source=(select look_values from look_values where look_values_id=NEW.immi_source);
set myno_days=NEW.no_days;
set myimmi_summary=NEW.immi_summary;
set myvalid_days=NEW.valid_days;
set oldmyocc_code=(select look_values from look_values where look_values_id=OLD.occ_code);
set oldmyskill_level=(select look_values from look_values where look_values_id=OLD.skill_level);
set oldmyeducation=(select look_values from look_values where look_values_id=OLD.education);
set oldmyexp_years=(select look_values from look_values where look_values_id=OLD.exp_years);
set oldmyrec_status=(select look_values from look_values where look_values_id=OLD.rec_status);
set oldmysalary_amt=(select look_values from look_values where look_values_id=OLD.salary_amt);
set oldmyimmi_source=(select look_values from look_values where look_values_id=OLD.immi_source);
set oldmyno_days=OLD.no_days;
set oldmyimmi_summary=OLD.immi_summary;
set oldmyvalid_days=OLD.valid_days;

IF myvalid_days IS NULL THEN SET myvalid_days = ''; ELSE SET myvalid_days = myvalid_days;	END IF;
IF myimmi_source IS NULL THEN SET myimmi_source = ''; ELSE SET myimmi_source = myimmi_source;	END IF;
IF myimmi_summary IS NULL THEN SET myimmi_summary = ''; ELSE SET myimmi_summary = myimmi_summary;	END IF;
IF myno_days IS NULL THEN SET myno_days = ''; ELSE SET myno_days = myno_days;	END IF;
IF myskill_level IS NULL THEN SET myskill_level = ''; ELSE SET myskill_level = myskill_level;	END IF;
IF myeducation IS NULL THEN SET myeducation = ''; ELSE SET myeducation = myeducation;	END IF;
IF myexp_years IS NULL THEN SET myexp_years = ''; ELSE SET myexp_years = myexp_years;	END IF;
IF myrec_status IS NULL THEN SET myrec_status = ''; ELSE SET myrec_status = myrec_status;	END IF;
IF mysalary_amt IS NULL THEN SET mysalary_amt = ''; ELSE SET mysalary_amt = mysalary_amt;	END IF; 
IF oldmyvalid_days IS NULL THEN SET oldmyvalid_days = ''; ELSE SET oldmyvalid_days = oldmyvalid_days;	END IF;
IF oldmyimmi_source IS NULL THEN SET oldmyimmi_source = ''; ELSE SET oldmyimmi_source = oldmyimmi_source;	END IF;
IF oldmyimmi_summary IS NULL THEN SET oldmyimmi_summary = ''; ELSE SET oldmyimmi_summary = oldmyimmi_summary;	END IF;
IF oldmyno_days IS NULL THEN SET oldmyno_days = ''; ELSE SET oldmyno_days = oldmyno_days;	END IF;
IF oldmyskill_level IS NULL THEN SET oldmyskill_level = ''; ELSE SET oldmyskill_level = oldmyskill_level;	END IF;
IF oldmyeducation IS NULL THEN SET oldmyeducation = ''; ELSE SET oldmyeducation = oldmyeducation;	END IF;
IF oldmyexp_years IS NULL THEN SET oldmyexp_years = ''; ELSE SET oldmyexp_years = oldmyexp_years;	END IF;
IF oldmyrec_status IS NULL THEN SET oldmyrec_status = ''; ELSE SET oldmyrec_status = oldmyrec_status;	END IF;
IF oldmysalary_amt IS NULL THEN SET oldmysalary_amt = ''; ELSE SET oldmysalary_amt = oldmysalary_amt;	END IF;  
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (OLD.emp_id = 0) then 1 else OLD.emp_id end)),
table_update     	= 'immi_receipts',
actions			= 'O',	
table_id		= CONCAT('receipt_id', '.',OLD.receipt_id),
col1			= CONCAT('comp_name', '.',(select companyname from company where comp_id=OLD.comp_id)),
col2			= CONCAT('receipt_no', '.',OLD.receipt_no),
col3			= CONCAT('filed_dt', '.',date_format(OLD.filed_dt,'%m-%d-%y')),
col4			= CONCAT('ad_title', '.',(select look_values from look_values where look_values_id=OLD.ad_title)),
col5			= CONCAT('receipt_type', '.',OLD.receipt_type,'\n','from_dt', '.',date_format(OLD.from_dt,'%m-%d-%y'),'\n','to_dt', '.',date_format(OLD.to_dt,'%m-%d-%y')),
col6			= CONCAT('skill_level', '.',oldmyskill_level,'\n','occ_code',oldmyocc_code),
col7			= CONCAT('exp_years',oldmyexp_years),
col8			= CONCAT('valid_days', '.',oldmyvalid_days,'\n','expire_dt', '.',date_format(OLD.expire_dt,'%m-%d-%y')),
col9			= CONCAT('education', '.',oldmyeducation),
col10			= CONCAT('immi_source', '.',oldmyimmi_source),
col11			= CONCAT('rec_status', '.',oldmyrec_status,'\n','salary_amt','.',oldmysalary_amt),
col12			= CONCAT('stat_st_dt', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')),
col13			= CONCAT('stat_e_dt', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')),
col14			= CONCAT('no_days', '.',oldmyno_days,'\n','immi_summary', '.',oldmyimmi_summary),
col15			= CONCAT('idlook_stat_order', '.',OLD.idlook_stat_order);


 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.emp_id = 0) then 1 else NEW.emp_id end)),
table_update     	= 'immi_receipts',
actions			= 'N',	
table_id		= CONCAT('receipt_id', '.',NEW.receipt_id),
col1			= CONCAT('comp_name', '.',(select companyname from company where comp_id=NEW.comp_id)),
col2			= CONCAT('receipt_no', '.',NEW.receipt_no),
col3			= CONCAT('filed_dt', '.',date_format(NEW.filed_dt,'%m-%d-%y')),
col4			= CONCAT('ad_title', '.',(select look_values from look_values where look_values_id=NEW.ad_title)),
col5			= CONCAT('receipt_type', '.',NEW.receipt_type,'\n','from_dt', '.',date_format(NEW.from_dt,'%m-%d-%y'),'\n','to_dt', '.',date_format(NEW.to_dt,'%m-%d-%y')),
col6			= CONCAT('skill_level', '.',myskill_level,'\n','occ_code',myocc_code),
col7			= CONCAT('exp_years',myexp_years),
col8			= CONCAT('valid_days', '.',myvalid_days,'\n','expire_dt', '.',date_format(NEW.expire_dt,'%m-%d-%y')),
col9			= CONCAT('education', '.',myeducation),
col10			= CONCAT('immi_source', '.',myimmi_source),
col11			= CONCAT('rec_status', '.',myrec_status,'\n','salary_amt','.',mysalary_amt),
col12			= CONCAT('stat_st_dt', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')),
col13			= CONCAT('stat_e_dt', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')),
col14			= CONCAT('no_days', '.',myno_days,'\n','immi_summary', '.',myimmi_summary),
col15			= CONCAT('idlook_stat_order', '.',NEW.idlook_stat_order);


END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `immi_receipts_after_delete` AFTER DELETE ON `immi_receipts` FOR EACH ROW begin
 
DECLARE oldmyocc_code varchar(45);
DECLARE oldmyskill_level varchar(45);
DECLARE oldmyeducation varchar(45);
DECLARE oldmyexp_years varchar(45);
DECLARE oldmyrec_status varchar(45);
DECLARE oldmysalary_amt varchar(45);
DECLARE oldmyno_days varchar(45);
DECLARE oldmyimmi_summary varchar(45);
DECLARE oldmyimmi_source varchar(45);
DECLARE oldmyvalid_days varchar(45);

set oldmyocc_code=(select look_values from look_values where look_values_id=OLD.occ_code);
set oldmyskill_level=(select look_values from look_values where look_values_id=OLD.skill_level);
set oldmyeducation=(select look_values from look_values where look_values_id=OLD.education);
set oldmyexp_years=(select look_values from look_values where look_values_id=OLD.exp_years);
set oldmyrec_status=(select look_values from look_values where look_values_id=OLD.rec_status);
set oldmysalary_amt=(select look_values from look_values where look_values_id=OLD.salary_amt);
set oldmyimmi_source=(select look_values from look_values where look_values_id=OLD.immi_source);
set oldmyno_days=OLD.no_days;
set oldmyimmi_summary=OLD.immi_summary;
set oldmyvalid_days=OLD.valid_days;
 
IF oldmyvalid_days IS NULL THEN SET oldmyvalid_days = ''; ELSE SET oldmyvalid_days = oldmyvalid_days; END IF;
IF oldmyimmi_source IS NULL THEN SET oldmyimmi_source = ''; ELSE SET oldmyimmi_source = oldmyimmi_source; END IF;
IF oldmyimmi_summary IS NULL THEN SET oldmyimmi_summary = ''; ELSE SET oldmyimmi_summary = oldmyimmi_summary; END IF;
IF oldmyno_days IS NULL THEN SET oldmyno_days = ''; ELSE SET oldmyno_days = oldmyno_days; END IF;
IF oldmyskill_level IS NULL THEN SET oldmyskill_level = ''; ELSE SET oldmyskill_level = oldmyskill_level; END IF;
IF oldmyeducation IS NULL THEN SET oldmyeducation = ''; ELSE SET oldmyeducation = oldmyeducation; END IF;
IF oldmyexp_years IS NULL THEN SET oldmyexp_years = ''; ELSE SET oldmyexp_years = oldmyexp_years; END IF;
IF oldmyrec_status IS NULL THEN SET oldmyrec_status = ''; ELSE SET oldmyrec_status = oldmyrec_status; END IF;
IF oldmysalary_amt IS NULL THEN SET oldmysalary_amt = ''; ELSE SET oldmysalary_amt = oldmysalary_amt; END IF;  
 
INSERT INTO log_global 
set
last_update          = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (OLD.emp_id = 0) then 1 else OLD.emp_id end)),
table_update      = 'immi_receipts',
actions   = 'D', 
table_id  = CONCAT('receipt_id', '.',OLD.receipt_id),
col1   = CONCAT('comp_name', '.',(select companyname from company where comp_id=OLD.comp_id)),
col2   = CONCAT('receipt_no', '.',OLD.receipt_no),
col3   = CONCAT('filed_dt', '.',date_format(OLD.filed_dt,'%m-%d-%y')),
col4   = CONCAT('ad_title', '.',(select look_values from look_values where look_values_id=OLD.ad_title)),
col5   = CONCAT('receipt_type', '.',OLD.receipt_type,'\n','from_dt', '.',date_format(OLD.from_dt,'%m-%d-%y'),'\n','to_dt', '.',date_format(OLD.to_dt,'%m-%d-%y')),
col6   = CONCAT('skill_level', '.',oldmyskill_level,'\n','occ_code',oldmyocc_code),
col7   = CONCAT('exp_years',oldmyexp_years),
col8   = CONCAT('valid_days', '.',oldmyvalid_days,'\n','expire_dt', '.',date_format(OLD.expire_dt,'%m-%d-%y')),
col9   = CONCAT('education', '.',oldmyeducation),
col10   = CONCAT('immi_source', '.',oldmyimmi_source),
col11   = CONCAT('rec_status', '.',oldmyrec_status,'\n','salary_amt','.',oldmysalary_amt),
col12   = CONCAT('stat_st_dt', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')),
col13   = CONCAT('stat_e_dt', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')),
col14   = CONCAT('no_days', '.',oldmyno_days,'\n','immi_summary', '.',oldmyimmi_summary),
col15   = CONCAT('idlook_stat_order', '.',OLD.idlook_stat_order);

END