CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_insert`(IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT)
    NO SQL
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
 ROLLBACK;
END;
	drop temporary table if exists temp_cursor_table;
    set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  prepare pst from @temp;
  execute pst;
  drop prepare pst;
INSERT INTO `contact_meeting`(`contact_id`, `contact_meeting_name`, `contact_meeting_location`, `contact_meeting_start_date`, `contact_meeting_end_date`, `contact_meeting_description`, `contact_meeting_date`, `contact_meeting_user`) VALUES (l_id,name,location,start_date,end_date,description,date,user);
  SET @meet_id = LAST_INSERT_ID();
INSERT INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
  SELECT @meet_id,user_id from temp_cursor_table;
  drop temporary table if exists temp_cursor_table;
  set sukses=1;
COMMIT;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_update`(IN `meet_id` INT, IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT)
    NO SQL
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
 ROLLBACK;
END;
	drop temporary table if exists temp_cursor_table;
    set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  prepare pst from @temp;
  execute pst;
  drop prepare pst;
UPDATE `contact_meeting` SET
`contact_meeting_name`=name,
`contact_meeting_location`=location,
`contact_meeting_start_date`=start_date,
`contact_meeting_end_date`=end_date,
`contact_meeting_description`=description,
`contact_meeting_date`=date,
`contact_meeting_user`=user,
`contact_id`=l_id
WHERE `contact_meeting_id`=meet_id;
delete from contact_meeting_users where contact_meeting_id=meet_id;
REPLACE INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
  SELECT meet_id,user_id from temp_cursor_table;
  drop temporary table if exists temp_cursor_table;
set sukses=1;
COMMIT;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_update`(IN `l_id` INT, IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `comp_id` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `user_list` VARCHAR(300), OUT `sukses` INT)
    NO SQL
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
        SET sukses=0;
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
        SET sukses=0;
 ROLLBACK;
END;
	drop temporary table if exists temp_cursor_table;
    set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  prepare pst from @temp;
  execute pst;
  drop prepare pst;
  UPDATE `crm_peoples` a JOIN crm_contacts b ON a.people_id=b.people_id
SET
a.people_salutation=salutation,
a.people_first_name=first_name,
a.people_last_name=last_name,
a.people_job_title=job_title,
a.people_company=company,
a.people_department=department,
a.people_email=email,
a.people_office_phone=office_phone,
a.people_mobile_phone=mobile_phone,
a.people_industry=industry,
a.people_address1=address1,
a.people_address2=address2,
a.people_city=city,
a.people_state=state,
a.people_zip_code=zip_code,
a.people_country=country,
a.people_source=sources,
a.people_photo=photo,
b.contact_right_permission=right_permission,
b.contact_groups=groups,
b.company_id=comp_id
WHERE b.contact_id = l_id;
delete from crm_users_contacts where contact_id = l_id;
REPLACE INTO `crm_users_contacts`(`contact_id`,`user_id`)
  SELECT l_id,u_id from temp_cursor_table;
  drop temporary table if exists temp_cursor_table;
    SET sukses = 1;
COMMIT;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_insert`(IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `comp_id` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `user_list` VARCHAR(300), OUT `sukses` INT, OUT `contact_id` INT)
    NO SQL
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
        SET sukses=0;
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
        SET sukses=0;
 ROLLBACK;
END;
	drop temporary table if exists temp_cursor_table;
    set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  prepare pst from @temp;
  execute pst;
  drop prepare pst;

  INSERT INTO `crm_peoples`(`people_salutation`, `people_first_name`, `people_last_name`, `people_job_title`, `people_company`, `people_department`, `people_email`, `people_office_phone`, `people_mobile_phone`, `people_industry`, `people_address1`, `people_address2`, `people_city`, `people_state`, `people_zip_code`, `people_country`, `people_source`, `people_photo`, `people_status`) VALUES (salutation, first_name, last_name, job_title, company, department, email, office_phone, mobile_phone, industry, address1, address2, city, state, zip_code, country, sources, photo, '2');
  SET @p_id = LAST_INSERT_ID();
INSERT INTO `crm_contacts`(`people_id`, `contact_right_permission`, `contact_groups`, `company_id`) VALUES (@p_id, right_permission, groups, comp_id);
SET @l_id = LAST_INSERT_ID();
INSERT INTO `crm_users_contacts`(`contact_id`,`user_id`)
  SELECT @l_id,u_id from temp_cursor_table;
    	SET contact_id = @l_id;
  drop temporary table if exists temp_cursor_table;
    SET sukses = 1;
COMMIT;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_delete`(IN `meet_id` INT, OUT `sukses` INT)
    NO SQL
BEGIN


DECLARE exit handler for sqlexception
  BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
    SET sukses=0;
 ROLLBACK;
END;

START TRANSACTION;
	DELETE FROM contact_meeting_users WHERE contact_meeting_id = meet_id;
    DELETE FROM contact_meeting WHERE contact_meeting_id = meet_id;
    SET sukses = 1;
COMMIT;
END