-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`data`@`%` PROCEDURE `Add_Group_Details`(P_Group_Name varchar(200),P_Sub_Group_Name varchar(200),

                                                   p_PIN            varchar(40),

                                                   p_FirstName      varchar(200),

                                                   p_LastName       varchar(200),
 
                                                   p_Email           varchar(50),
 
                                                   p_Mobile         varchar(20),
 
                                                   p_BritshDate     Date,
 
                                                   p_Gender         varchar(200))
BEGIN
	DECLARE
		i_sub_groups_ID DECIMAL;
 
DECLARE
	i_groups_ID DECIMAL;
 
DECLARE
	i_Groups_Client_ID DECIMAL;
 
DECLARE
 	i_user_details_ID DECIMAL;
  DECLARE
 	t_ClientID DECIMAL;
  DECLARE
 	t_PIN VARCHAR (200);
  DECLARE
 	t_FirstName VARCHAR (200);
  DECLARE
 	t_LastName VARCHAR (200);
  DECLARE
 	t_Email VARCHAR (200);
  DECLARE
 	t_Mobile VARCHAR (200);
  DECLARE
 	t_BritshDate DATE;
  DECLARE
 	t_Gender VARCHAR (200);

set P_Group_Name=trim(P_Group_Name);
 set P_Sub_Group_Name=trim(P_Sub_Group_Name);
 set p_PIN=trim(p_PIN);
 set p_FirstName=trim(p_FirstName);
 set p_LastName=trim(p_LastName);
 set p_Email=trim(p_Email);
 set p_Mobile=trim(p_Mobile);
 set p_Gender=trim(p_Gender);


if	((p_PIN is null or p_PIN='') and (p_Email is null or p_Email='') and (p_Mobile is null or p_Mobile='') )	
then 
CALL `raise`(1356, 'person number and phone and email is null');
select 'Error';
end if;

if	(P_Group_Name is null or P_Group_Name='' or  P_Sub_Group_Name is null or P_Sub_Group_Name='' ) then
CALL `raise`(1356, 'pgroup Name or Sub group Name is null');
select 'Error';
end if;	


     SET i_groups_ID = (
 	SELECT
 		GROUPS_ID
 	FROM
 		GROUPS
 	WHERE
 		trim(NAME) = P_Group_Name
 	AND ESHELON = FALSE
 	LIMIT 1
 );

   IF (i_groups_ID IS NULL) THEN
 	INSERT INTO GROUPS (
 		NAME,
 		GROUP_TYPE_ID,
 		STATE,
 		DESCRIPTION,
 		REGDATE,
 		CREATOR_ID,
 		CREATE_TIME,
 		MODIFIER_ID,
 		MODIFIED_TIME,
 		HOST_ADDRESS,
 		PAREN_GROUPS_ID,
 		ESHELON,
 		MAIL_COUNT,
 		SMS_COUNT
 	)
 VALUES
 	(
 		P_Group_Name,
 		1,
 		1,
 		'QUARTZ',
 		now(),
 		'system',
 		now(),
 		'QUARTZ',
 		now(),
 		'',
 		NULL,
 		0,
 		0,
 		0
 	);
   SET i_groups_ID = LAST_INSERT_ID();
   END
 IF;
   SET i_sub_groups_ID = (
 	SELECT
 		GROUPS_ID
 	FROM
 		GROUPS
 	WHERE
 		trim(NAME) = P_Sub_Group_Name
 	AND eshelon = TRUE and PAREN_GROUPS_ID=i_groups_ID
 	LIMIT 1
 );
   IF (i_sub_groups_ID IS NULL) THEN
 	INSERT INTO GROUPS (
 		NAME,
 		GROUP_TYPE_ID,
 		STATE,
 		DESCRIPTION,
 		REGDATE,
 		CREATOR_ID,
 		CREATE_TIME,
 		MODIFIER_ID,
 		MODIFIED_TIME,
 		HOST_ADDRESS,
 		PAREN_GROUPS_ID,
 		ESHELON,
 		MAIL_COUNT,
 		SMS_COUNT
 	)
 VALUES
 	(
 		P_Sub_Group_Name,
 		1,
 		1,
 		'QUARTZ',
 		now(),
 		'system',
 		now(),
 		'QUARTZ',
 		now(),
 		'',
 		i_groups_ID,
 		1,
 		0,
 		0
 	);
   SET i_sub_groups_ID = LAST_INSERT_ID();
   END
 IF;
  SELECT
 	c.CLIENT_ID,
 	c.PERSON_NUMBER,
 	c.FIRSTNAME,
 	c.LASTNAME,
 	c.EMAIL,
 	c.PHONE,
 	c.BIRTHDATE,
 	c.GENDER INTO t_ClientID,
 	t_PIN,
 	t_FirstName,
 	t_LastName,
 	t_Email,
 	t_Mobile,
 	t_BritshDate,
 	t_Gender
 FROM
 	CLIENT c
 WHERE
 	(c.PERSON_NUMBER = p_Pin
 		AND TRIM(p_Pin) <> '' and p_Pin is not null and p_Pin<>'null'
 	) OR (
 	c.EMAIL = p_Email
 	AND TRIM(p_Email) <> ''and p_Email is not null and p_Email<>'null'
 ) OR (
 	c.PHONE = p_Mobile
 	AND TRIM(p_Mobile) <> ''and p_Mobile is not null and p_Mobile<>'null'
 )
 LIMIT 1;
   IF (t_ClientID IS NULL) THEN
 	INSERT INTO CLIENT (
 		PERSON_NUMBER,
 		FIRSTNAME,
 		LASTNAME,
 		BIRTHDATE,
 		STATE,
 		REG_DATE,
 		GENDER,
 		CREATOR_ID,
 		CREATE_TIME,
 		MODIFIER_ID,
 		MODIFIED_TIME,
 		HOST_ADDRESS,
 		EMAIL,
 		PHONE
 	)
 VALUES
 	(
 		p_PIN,
 		p_FirstName,
 		p_LastName,
 		p_BritshDate,
 		1,
 		now(),
 		p_Gender,
 		'QUARTZ',
 		now(),
 		'QUARTZ',
 		now(),
 		'Quartz',
 		p_Email,
 		p_Mobile
 	);
   SET t_ClientID = LAST_INSERT_ID();
   ELSEIF (

((p_PIN IS not NULL or p_PIN<>'') and (t_PIN IS NULL or trim(t_PIN)=''))
or ((p_Mobile IS not NULL or p_Mobile<>'') and (t_Mobile IS NULL or trim(t_Mobile)=''))
or ((p_Email IS not NULL or p_Email<>'') and (t_Email IS NULL or trim(t_Email)=''))
or ((p_Gender IS not NULL or p_Gender<>'') and (t_Gender IS NULL or trim(t_Gender)=''))
or ((p_FirstName IS not NULL or p_FirstName<>'') and (t_FirstName IS NULL or trim(t_FirstName)=''))
or ((p_LastName IS not NULL or p_LastName<>'') and (t_LastName IS NULL or trim(t_LastName)=''))
or ((p_BritshDate IS not NULL or p_BritshDate<>'') and (t_BritshDate IS NULL or trim(t_BritshDate)=''))

 ) THEN
 	UPDATE CLIENT
 SET person_number =case when((p_Pin is not null and p_Pin<>'')and (person_number is null or person_number='')) then  p_Pin else person_number end,
FIRSTNAME =case when((p_FirstName is not null and p_FirstName<>'')and (FIRSTNAME is null or FIRSTNAME='')) then  p_FirstName else FIRSTNAME end,
LASTNAME =case when((LASTNAME is not null and LASTNAME<>'')and (p_LastName is null or p_LastName='')) then  p_LastName else LASTNAME end,
GENDER =case when((p_Gender is not null and p_Gender<>'')and (GENDER is null or GENDER='')) then  p_Gender else GENDER end,
EMAIL =case when((p_Email is not null and p_Email<>'')and (EMAIL is null or EMAIL='')) then  p_Email else EMAIL end,
PHONE =case when((p_Mobile is not null and p_Mobile<>'')and (PHONE is null or PHONE='')) then  p_Mobile else PHONE end,
BIRTHDATE =case when((p_BritshDate is not null and p_BritshDate<>'')and (BIRTHDATE is null or BIRTHDATE='')) then  p_BritshDate else BIRTHDATE end,
MODIFIER_ID = 'QUARTZ',
MODIFIED_TIME = now()

 WHERE
 	CLIENT_ID = t_ClientID;
   END IF;

   SET i_Groups_Client_ID = (
 	SELECT
 		GROUPS_CLIENTS_ID
 	FROM
 		GROUPS_CLIENTS
 	WHERE
 		CLIENT_ID = t_ClientID
 	AND GROUPS_ID = i_sub_groups_ID
 	LIMIT 1
 );



   IF (i_Groups_Client_ID IS NULL) THEN
 	INSERT INTO GROUPS_CLIENTS (
 		CLIENT_ID,
 		GROUPS_ID,
 		STATE,
 		REG_DATE,
 		CREATOR_ID,
 		CREATE_TIME,
 		MODIFIER_ID,
 		MODIFIED_TIME,
 		HOST_ADDRESS
 	)
 VALUES
 	(
 		t_ClientID,
 		i_sub_groups_ID,
 		1,
 		now(),
 		'Quartz',
 		now(),
 		'Quartz',
 		now(),
 		'LocalHost'
 	);
   END
 IF;
   END