CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_insert` AFTER INSERT ON `mkt_assist_status` FOR EACH ROW begin

DECLARE vUser varchar(50);
DECLARE pname varchar(100);
IF(NEW.updated_by='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updated_by);
END IF;




IF((select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)='Employee')THEN
set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = NEW.idmkt_assist));
else
set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = NEW.idmkt_assist));
END IF;
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update            = 'mkt_assist_status',
actions			= 'A',	
table_id		= CONCAT('idmkt_assist_status', '.',NEW.idmkt_assist_status, '\n','idmkt_assist', '.',NEW.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)),
col4            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)),
col5            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),
col7            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),
col8            = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state  on  look_city.state_id=look_state.state_id where city_id= (select location from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),


col14              = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = NEW.idmkt_assist)),
col15            	= CONCAT('Days', '.',NEW.no_days),

col10              = CONCAT('Start_date', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')),
col11              = CONCAT('End_date', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')),


col13                    = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = NEW.idmkt_assist));
END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_update` AFTER UPDATE ON `mkt_assist_status` FOR EACH ROW begin


DECLARE pname varchar(100);

DECLARE oldmkt_status int(5);
DECLARE oldstat_st_dt date;
DECLARE oldstat_e_dt date;
DECLARE oldno_days int(5);

IF OLD.assist_status IS NULL THEN SET oldmkt_status = ''; ELSE SET oldmkt_status = OLD.assist_status;	END IF;
IF OLD.stat_st_dt IS NULL THEN SET oldstat_st_dt = ''; ELSE SET oldstat_st_dt = OLD.stat_st_dt;	END IF;
IF OLD.stat_e_dt IS NULL THEN SET oldstat_e_dt = ''; ELSE SET oldstat_e_dt = OLD.stat_e_dt;	END IF;
IF OLD.no_days IS NULL THEN SET oldno_days = ''; ELSE SET oldno_days = OLD.no_days;	END IF;




 IF((select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)='Employee')THEN
set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist));
else
set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist));
END IF;

 IF((NEW.assist_status!=oldmkt_status)OR(NEW.stat_st_dt!=oldstat_st_dt)OR(NEW.stat_e_dt!=oldstat_e_dt)OR(NEW.no_days!=oldno_days))THEN
INSERT INTO log_global 
set
last_update             = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (OLD.updated_by = 0) then 1 else OLD.updated_by end)),
table_update            = 'mkt_assist_status',
actions			= 'O',	
table_id		= CONCAT('idmkt_assist_status', '.',OLD.idmkt_assist_status, '\n','idmkt_assist', '.',OLD.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)),
col4            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)),
col5            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),
col7            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),
col8            = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state  on  look_city.state_id=look_state.state_id where city_id= (select location from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),

col10                    = CONCAT('Start_date', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')),
col11                   = CONCAT('End_date', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')),
col14            	= CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = OLD.idmkt_assist)),
col15            	= CONCAT('Days', '.',OLD.no_days),



col13                    = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = OLD.idmkt_assist));






INSERT INTO log_global 
set
last_update             = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.updated_by = 0) then 1 else NEW.updated_by end)),
table_update            = 'mkt_assist_status',
actions			= 'N',	
table_id		= CONCAT('idmkt_assist_status', '.',NEW.idmkt_assist_status, '\n','idmkt_assist', '.',NEW.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)),
col4            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)),
col5            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),
col7            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),
col8            = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state  on  look_city.state_id=look_state.state_id where city_id= (select location from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))),

col10                    = CONCAT('Start_date', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')),
col11                   = CONCAT('End_date', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')),
col14            	= CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = NEW.idmkt_assist)),
col15            	= CONCAT('Days', '.',NEW.no_days),



col13                    = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = NEW.idmkt_assist));

 END IF; 
END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_delete` AFTER DELETE ON `mkt_assist_status` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
DECLARE pname varchar(100);
IF(OLD.updated_by='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updated_by);
END IF;




IF((select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)='Employee')THEN
set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist));
else
set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist));
END IF;




INSERT INTO log_global 
set
last_update             = now(),
updater                 = vUser,
table_update            = 'mkt_assist_status',
actions			= 'D',	
table_id		= CONCAT('idmkt_assist_status', '.',OLD.idmkt_assist_status, '\n','idmkt_assist', '.',OLD.idmkt_assist),
col1                    = pname,
col2                    = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)),
col4                    = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)),
col5                    = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),
col7                    = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),
col8                    = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state  on  look_city.state_id=look_state.state_id where city_id= (select location from mkt_open  where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))),
col10                    = CONCAT('Start_date', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')),
col11                    = CONCAT('End_date', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')),
col14            	 = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = OLD.idmkt_assist)),
col15            	 = CONCAT('Days', '.',OLD.no_days),
col13                    = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = OLD.idmkt_assist));


END