CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_after_insert` AFTER INSERT ON `mkt_assist` 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(NEW.assist_ref_table='Employee')THEN
set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.assist_ref_id);
else
set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = NEW.assist_ref_id);
END IF;
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update            = 'mkt_assist',
update_details			= CONCAT('idmkt_open', '.',NEW.idmkt_open),

actions			= 'A',	
table_id		= CONCAT('idmkt_assist', '.',NEW.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',NEW.assist_ref_table),
col3            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)),
col4            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=NEW.idmkt_open))),
col5            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=NEW.idmkt_open))),
col6            = 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=NEW.idmkt_open))),


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')),
col7            	= CONCAT('Rate', '.',NEW.assist_rate),
col8            	= CONCAT('Days', '.',NEW.no_days),
col13                    = CONCAT('Summary', '.',NEW.summary);
END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_after_update` AFTER UPDATE ON `mkt_assist` FOR EACH ROW begin
DECLARE pname varchar(100);

DECLARE oldassist_rate varchar(45);
DECLARE oldsummary varchar(100);


IF OLD.assist_rate IS NULL THEN SET oldassist_rate = ''; ELSE SET oldassist_rate = OLD.assist_rate;	END IF;
IF OLD.summary IS NULL THEN SET oldsummary = ''; ELSE SET oldsummary = OLD.summary;	END IF;

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



IF((NEW.assist_rate!=oldassist_rate)OR(NEW.summary!=oldsummary))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',
actions			= 'O',	
table_id		= CONCAT('idmkt_assist', '.',OLD.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',OLD.assist_ref_table),
col3            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)),
col4            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=OLD.idmkt_open))),
col5            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=OLD.idmkt_open))),
col6            = 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=OLD.idmkt_open))),


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')),
col7            	= CONCAT('Rate', '.',OLD.assist_rate),
col8            	= CONCAT('Days', '.',OLD.no_days),
col13                    = CONCAT('Summary', '.',OLD.summary);
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',
actions			= 'N',	
table_id		= CONCAT('idmkt_assist', '.',NEW.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',NEW.assist_ref_table),
col3            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)),
col4            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=NEW.idmkt_open))),
col5            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=NEW.idmkt_open))),
col6            = 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=NEW.idmkt_open))),


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')),
col7            	= CONCAT('Rate', '.',NEW.assist_rate),
col8            	= CONCAT('Days', '.',NEW.no_days),
col13                    = CONCAT('Summary', '.',NEW.summary);
 
 
 END IF; 

END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_after__delete` AFTER DELETE ON `mkt_assist` 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(OLD.assist_ref_table='Employee')THEN
set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.assist_ref_id);
else
set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = OLD.assist_ref_id);
END IF;  


INSERT INTO log_global 
set
last_update             = now(),
updater                 = vUser,
table_update            = 'mkt_assist',
actions			= 'D',	
table_id		= CONCAT('idmkt_assist', '.',OLD.idmkt_assist),
col1            = pname,
col2            = CONCAT('Ref_table', '.',OLD.assist_ref_table),
col3            = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)),
col4            = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open  where idmkt_open=OLD.idmkt_open))),
col5            = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open  where idmkt_open=OLD.idmkt_open))),
col6            = 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=OLD.idmkt_open))),


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')),
col7            	= CONCAT('Rate', '.',OLD.assist_rate),
col8            	= CONCAT('Days', '.',OLD.no_days),
col13                    = CONCAT('Summary', '.',OLD.summary);


END