CREATE  PROCEDURE `getAllWhatNewsByContentID`(in siteId text)
BEGIN

-- ALTER TABLE  `cms_news` CHANGE  `id_site_list`  `id_site_list` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

SELECT `id`, `name`,`status`,`date_update`,`date_create`,`edit_time`,`id_account`,
`id_approval`,`id_org`,`text`,`id_icon`,`id_owner`,`id_site_list`,
`public`,`priority`,`range_use`,`range_start`,`range_end`,`link`,
`data`,`id_template`,`id_image`,`date_entry`,`id_file`,`file_target`,`id_dynamic`
FROM `cms_news`
WHERE   ( `range_use` = 0 
		OR ( `range_use` = 1 AND  datediff(CURDATE(),`range_start`) >= 0 AND  datediff(`range_end`,curdate()) >= 0 )
		OR ( `range_use` = 1 AND  `range_start` = "" AND  datediff(`range_end`,curdate()) >= 0 )  
		OR ( `range_use` = 1 AND  datediff(CURDATE(),`range_start`) >= 0 AND  `range_end` = "" )  
		OR ( `range_use` = 1 AND  `range_start` ="" AND  `range_end` = "" )
	)
	AND  `public` = 'public' 
	AND  `id_site_list` like  CONCAT('%',TRIM(siteId),'%')
	ORDER BY `date_entry` DESC,`date_create` DESC;

-- ALTER TABLE  `cms_news` CHANGE  `id_site_list`  `id_site_list` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

END 

DELIMITER;

CREATE  PROCEDURE `getAllDynamicItemByDynamicId`(in dynamicId text,in strcategory text,in statusID int)
BEGIN

SELECT it.*
 from `cms_item` as it
  where
 (     it.`id_dynamic` = dynamicId
	and it.`id_org` != '-'
	and it.`public` = 'public'
	and it.`status` = statusID
	and it.category like CONCAT('%',TRIM(strcategory),'%'))
  or `id` in ( SELECT `id_org`
               FROM `cms_item`
               WHERE `id_org`!='-'
				  and `id_org`!=''
				  and `public` = 'public'
				  and `id_dynamic` = dynamicId
				  and it.category like CONCAT('%',TRIM(strcategory),'%'))
  order by `index` desc  ;

END 
DELIMITER;

CREATE  PROCEDURE `deleteContentPage`(IN strId VARCHAR(32), IN strDbPrefix VARCHAR(20))
BEGIN
 -- ALTER TABLE  `cms_contents` CHANGE  `id`  `id` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- Del permission page

IF (strDbPrefix = 'cms_')
THEN

  SELECT @myLeft := left_id
    , @myRight := right_id
    , @myWidth := right_id - left_id + 1
    , @idOrg   := id_org
  FROM cms_contents
  WHERE id like strId ;

  DELETE FROM cms_contents WHERE left_id  BETWEEN @myLeft AND @myRight ;
  IF(@idOrg != '')
  THEN

    DELETE FROM cms_contents WHERE id = @idOrg ;
	DELETE FROM cms_account_auth WHERE contents_item_id in (strId,@idOrg) ;
  END IF;

  UPDATE cms_contents SET right_id = right_id - @myWidth WHERE right_id > @myRight ;
  UPDATE cms_contents SET left_id = left_id - @myWidth WHERE left_id > @myRight ;

  DELETE FROM cms_contents_auth WHERE content_id in (strId,@idOrg) ;
  
ELSE

  SELECT @myLeft := left_id
    , @myRight := right_id
    , @myWidth := right_id - left_id + 1
    , @idOrg   := id_org
  FROM smp_contents
  WHERE id like strId ;

  DELETE FROM smp_contents WHERE left_id  BETWEEN @myLeft AND @myRight ;
  IF(@idOrg != '')
  THEN
    DELETE FROM smp_contents WHERE id = @idOrg ;
  END IF;

  UPDATE smp_contents SET right_id = right_id - @myWidth WHERE right_id > @myRight ;
  UPDATE smp_contents SET left_id = left_id - @myWidth WHERE left_id > @myRight ;

  DELETE FROM smp_contents_auth WHERE content_id in (strId,@idOrg) ;



END IF ;

-- Del history

DELETE FROM cms_history WHERE head_id = strId ;

 -- ALTER TABLE  `cms_contents` CHANGE  `id`  `id` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
END

DELIMITER;

CREATE  PROCEDURE `deleteContentPageWithOutChidren`(IN strId VARCHAR(32), IN strDbPrefix VARCHAR(20))
BEGIN


SELECT @myLeft := left_id
, @myRight := right_id
, @myWidth := right_id - left_id + 1
, @idOrg   := id_org
FROM cms_contents
WHERE id = strId ;

-- DELETE FROM cms_contents WHERE left_id = @myLeft;

UPDATE cms_contents SET right_id = right_id - 1, left_id = left_id - 1 WHERE left_id BETWEEN @myLeft AND @myRight ;
UPDATE cms_contents SET right_id = right_id - 2 WHERE right_id > @myRight ;
UPDATE cms_contents SET left_id = left_id - 2 WHERE left_id > @myRight ;

END

DELIMITER;

CREATE PROCEDURE `updateDataContentPage`(IN strId VARCHAR(32), IN strDbPrefix VARCHAR(20))
BEGIN

IF (strDbPrefix = 'cms_')
THEN

SELECT @myLeft := left_id
, @myRight := right_id
, @myWidth := right_id - left_id + 1
, @idOrg   := id_org
FROM cms_contents
WHERE id = strId ;

-- DELETE FROM cms_contents WHERE left_id = @myLeft;

UPDATE cms_contents SET right_id = right_id - 1, left_id = left_id - 1 WHERE left_id BETWEEN @myLeft AND @myRight ;
UPDATE cms_contents SET right_id = right_id - 2 WHERE right_id > @myRight ;
UPDATE cms_contents SET left_id = left_id - 2 WHERE left_id > @myRight ;

ELSE

SELECT @myLeft := left_id
, @myRight := right_id
, @myWidth := right_id - left_id + 1
, @idOrg   := id_org
FROM smp_contents
WHERE id = strId ;

-- DELETE FROM cms_contents WHERE left_id = @myLeft;

UPDATE smp_contents SET right_id = right_id - 1, left_id = left_id - 1 WHERE left_id BETWEEN @myLeft AND @myRight ;
UPDATE smp_contents SET right_id = right_id - 2 WHERE right_id > @myRight ;
UPDATE smp_contents SET left_id = left_id - 2 WHERE left_id > @myRight ;


END IF ;

END

DELIMITER;

CREATE  PROCEDURE `updateCategory`(IN strId VARCHAR(32), IN strDbPrefix VARCHAR(20))
BEGIN

if(strDbPrefix = 'cms_')
THEN

SELECT @myLeft := left_id
, @myRight := right_id
, @myWidth := right_id - left_id + 1

FROM cms_categories
WHERE id = strId ;

UPDATE cms_categories SET right_id = right_id - 1, left_id = left_id - 1 WHERE left_id BETWEEN @myLeft AND @myRight ;
UPDATE cms_categories SET right_id = right_id - 2 WHERE right_id > @myRight ;
UPDATE cms_categories SET left_id = left_id - 2 WHERE left_id > @myRight ;


ELSE

SELECT @myLeft := left_id
, @myRight := right_id
, @myWidth := right_id - left_id + 1

FROM smp_categories
WHERE id = strId ;


UPDATE smp_categories SET right_id = right_id - 1, left_id = left_id - 1 WHERE left_id BETWEEN @myLeft AND @myRight ;
UPDATE smp_categories SET right_id = right_id - 2 WHERE right_id > @myRight ;
UPDATE smp_categories SET left_id = left_id - 2 WHERE left_id > @myRight ;

END IF ;

END 

DELIMITER;

CREATE  PROCEDURE `deleteCategory`(IN strId VARCHAR(32), IN strDbPrefix VARCHAR(20))
BEGIN


-- Del permission page

IF (strDbPrefix = 'cms_')
THEN
  SELECT @myLeft := left_id
    , @myRight := right_id
    , @myWidth := right_id - left_id + 1
FROM cms_categories
WHERE id like strId ;

DELETE FROM cms_categories WHERE left_id  BETWEEN @myLeft AND @myRight ;


  UPDATE cms_categories SET right_id = right_id - @myWidth WHERE right_id > @myRight ;
  UPDATE cms_categories SET left_id  = left_id  - @myWidth WHERE left_id >  @myRight ;


ELSE

SELECT @myLeft := left_id
    , @myRight := right_id
    , @myWidth := right_id - left_id + 1
FROM cms_categories
WHERE id like strId ;

DELETE FROM smp_categories WHERE left_id  BETWEEN @myLeft AND @myRight ;


UPDATE smp_categories SET right_id = right_id - @myWidth WHERE right_id > @myRight ;
UPDATE smp_categories SET left_id  = left_id  - @myWidth WHERE left_id >  @myRight ;


END IF;

END 