/*
groups procedure
####################################################
*/

/*
Get groups which is editable for current user
*/
DROP PROCEDURE getUserEditableGroups;
delimiter $$
	CREATE PROCEDURE getUserEditableGroups(IN userAlias VARCHAR(255))
	BEGIN
		SELECT distinct g1.id as id,g1.name as name,g1.created as created,g1.modified as modified,g1.islock as lockable,g1.approve as approveable
		FROM jmsp.group g1
		INNER JOIN gg
		ON g1.id = gg.idedit
		INNER JOIN jmsp.group g2
		ON g2.id = gg.id
		INNER JOIN ug
		ON ug.groupID = g2.id
		INNER JOIN jmsp.user u
		ON u.id = ug.userID
		WHERE u.user_name = userAlias
		ORDER BY g1.name;
	END$$
delimiter ;

/*
get information from group table
*/
DROP PROCEDURE getGroupDetails;
delimiter $$
	CREATE PROCEDURE getGroupDetails(IN groupId INT)
	BEGIN
		SELECT distinct g.id as id,g.name as name,g.created as created,g.modified as modified,g.islock as lockable,g.approve as approveable
		FROM jmsp.group g
		WHERE g.id = groupId;
	END$$
delimiter ;

/*
get groups which can edit current group
*/
DROP PROCEDURE getGroupCanEditGroups;
delimiter $$
	CREATE PROCEDURE getGroupCanEditGroups(IN groupId INT)
	BEGIN
		SELECT  distinct g.id as id,g.name as name
		FROM jmsp.group g
		INNER JOIN gg
		ON gg.id = g.id
		WHERE gg.idedit = groupId;
	END$$
delimiter ;

DROP PROCEDURE updateGroupData;
delimiter $$
	CREATE PROCEDURE updateGroupData(IN id INT,IN name VARCHAR(255),IN lock1 TINYINT,IN approve TINYINT)
	BEGIN
		UPDATE jmsp.group g SET g.name=name,g.modified = NOW(),g.islock = lock1,g.approve = approve WHERE g.id = id;
	END$$
delimiter ;

DROP PROCEDURE cleanGroupGroups;
delimiter $$
	CREATE PROCEDURE cleanGroupGroups(IN id INT)
	BEGIN
		DELETE FROM gg WHERE idedit = id;
	END$$
delimiter ;

DROP PROCEDURE updateGroupGroups;
delimiter $$
	CREATE PROCEDURE updateGroupGroups(IN id INT,IN editid INT)
	BEGIN
		INSERT INTO gg (gg.id,gg.idedit) VALUE (id,editid);
	END$$
delimiter ;

DROP PROCEDURE addGroup;
delimiter $$

	CREATE PROCEDURE addGroup(IN name1 VARCHAR(255),IN lock1 TINYINT,IN approve TINYINT)

	BEGIN
		DECLARE id INT;
		
		INSERT INTO jmsp.group (name,created,islock,approve) 
		VALUES (name1,NOW(),lock1,approve);
		
		SELECT LAST_INSERT_ID() as id; 
	END$$
delimiter ;

/*
	users procedure
#####################################################
*/

DROP PROCEDURE getUsers;
delimiter $$

	CREATE PROCEDURE getUsers()
	BEGIN
		SELECT u.id as id,u.user_name as user_name,u.first_name as first_name,u.last_name as last_name, g.id as group_id,g.name as group_name,u.contact_number as contact_number,u.address as address
		FROM user u
		LEFT JOIN ug
		ON ug.userID = u.id
		LEFT JOIN jmsp.group g
		ON ug.groupID = g.id
		ORDER BY u.id;
     END$$
delimiter ;

DROP PROCEDURE getUserNames;
delimiter $$
	CREATE PROCEDURE getUserNames(IN userId varchar(255))
	BEGIN
		SELECT distinct u.id AS id,u.user_name AS user_name
		FROM user u
		INNER JOIN ug ON ug.userID = u.id
		INNER JOIN jmsp.group g ON ug.groupID = g.id
		INNER JOIN gg ON gg.idedit = g.id
		INNER JOIN jmsp.group g2 ON gg.id = g2.id
		INNER JOIN ug ug2 ON ug2.groupID = g2.id
		INNER JOIN user u2 ON ug2.userID = u2.id
		WHERE u2.user_name = userId
		ORDER BY u.user_name;
     END$$
delimiter ;

/*Create by Adrian on Dec 05 2012
get user details based on user id
*/
DROP PROCEDURE getUser;
delimiter $$

	CREATE PROCEDURE getUser(IN user_id VARCHAR(255))
	BEGIN
		SELECT u.id as id,u.user_name as user_name,u.first_name as first_name,u.last_name as last_name, g.id as group_id,g.name as group_name,u.contact_number as contact_number,u.address as address 
		FROM user u
		LEFT JOIN ug
		ON ug.userID = u.id
		LEFT JOIN jmsp.group g
		ON ug.groupID = g.id
		WHERE u.user_name = user_id
		ORDER BY u.id;
     END$$
delimiter ;

DROP PROCEDURE getUserById;
delimiter $$

	CREATE PROCEDURE getUserById(IN id INT(255))
	BEGIN
		SELECT u.id as id,u.user_name as user_name,u.first_name as first_name,u.last_name as last_name, g.id as group_id,g.name as group_name,u.contact_number as contact_number,u.address as address 
		FROM user u
		LEFT JOIN ug
		ON ug.userID = u.id
		LEFT JOIN jmsp.group g
		ON ug.groupID = g.id
		WHERE u.id = id
		ORDER BY u.id;
     END$$
delimiter ;

/*Create by Adrian on Dec 07 2012
get groups which are editable for current user id
*/
DROP PROCEDURE getUserEditGroups;
delimiter $$

	CREATE PROCEDURE getUserEditGroups(IN user_id VARCHAR(255))

	BEGIN
		SELECT distinct g2.id as group_id,g2.name as group_name
		FROM jmsp.group g2
		INNER JOIN gg
		ON g2.id = gg.idedit
		INNER JOIN jmsp.group g1
		ON g1.id = gg.id
		INNER JOIN ug
		ON g1.id = ug.groupID
		INNER JOIN jmsp.user u
		ON ug.userID = u.id
		WHERE u.user_name = user_id;
		
	END$$
delimiter ;

/*Create by Adrian on Dec 07 2012
get groups which are editable for current user id
*/
DROP PROCEDURE getUserGroups;
delimiter $$

	CREATE PROCEDURE getUserGroups(IN user_id VARCHAR(255))

	BEGIN
		SELECT distinct g.id as group_id,g.name as group_name,g.isadmin as isadmin
		FROM jmsp.group g
		INNER JOIN ug
		ON g.id = ug.groupID
		INNER JOIN jmsp.user u
		ON ug.userID = u.id
		WHERE u.user_name = user_id;
		
	END$$
delimiter ;

DROP PROCEDURE getUserId;
delimiter $$

	CREATE PROCEDURE getUserId(IN user_id VARCHAR(255))

	BEGIN
		SELECT id
		FROM jmsp.user
		WHERE user_name = user_id;
		
	END$$
delimiter ;

DROP PROCEDURE addUser;
delimiter $$

	CREATE PROCEDURE addUser(IN email VARCHAR(255),IN password VARCHAR(255),IN firstName VARCHAR(255),IN lastName VARCHAR(255),IN dateTime DATETIME,IN contactNumber VARCHAR(255),IN address VARCHAR(255))

	BEGIN
		DECLARE id INT;
		
		INSERT INTO jmsp.user(user_name,password,first_name,last_name,created,contact_number,address) 
		VALUES (email,password,firstName,lastName,NOW(),contactNumber,address);
		
		SELECT LAST_INSERT_ID() as id; 
	END$$
delimiter ;

DROP PROCEDURE addUserGroup;
delimiter $$

	CREATE PROCEDURE addUserGroup(IN userId INT(255),IN groupId INT(255))

	BEGIN
		INSERT INTO ug (userID,groupID)
		VALUES (userId,groupId);
	END$$
delimiter ;

DROP PROCEDURE clearUserGroup;
delimiter $$

	CREATE PROCEDURE clearUserGroup(IN user_id INT(255))

	BEGIN
		DELETE FROM ug WHERE userID= user_id;
	END$$
delimiter ;

DROP PROCEDURE setUser;
delimiter $$

	CREATE PROCEDURE setUser(IN userId INT(255),IN userName VARCHAR(255),IN firstName VARCHAR(255),IN lastName VARCHAR(255),IN dateTime DATETIME,IN contactNumber VARCHAR(255),IN address VARCHAR(255))

	BEGIN
		UPDATE jmsp.user SET user_name = userName,first_name = firstName,last_name = lastName, modified = NOW(),contact_number = contactNumber,address = address WHERE id = userId;
	END$$
delimiter ;

DROP PROCEDURE setUserPwd;
delimiter $$


	END$$
delimiter ;

/*
	pages procedure
############################################################################
*/

/*
not update to live and mf project yet
have done update to live server and mf project
*/
DROP PROCEDURE getPageGroup;
delimiter $$

	CREATE PROCEDURE getPageGroup(IN pAlias VARCHAR(255),IN sectionAlias VARCHAR(255),IN parentId INT)
	BEGIN		  
		SELECT gp.groupID as group_id,g.name as group_name 
		FROM gp
		INNER JOIN jmsp.group g 
		ON g.id = gp.groupID
		INNER JOIN jmsp.page p
		ON gp.pageID = p.id
		INNER JOIN jmsp.section s
		ON s.id = p.section
		WHERE p.id = getRootPageGroupID(pAlias,sectionAlias,parentId) AND s.alias = sectionAlias;
	END$$
delimiter ;

DROP PROCEDURE getPageRealGroupsByID;
delimiter $$

	CREATE PROCEDURE getPageRealGroupsByID(IN pID INT(255))
	BEGIN		  
		SELECT gp.groupID as group_id,g.name as group_name 
		FROM gp
		INNER JOIN jmsp.group g 
		ON g.id = gp.groupID
		INNER JOIN jmsp.page p
		ON gp.pageID = p.id
		WHERE p.id = pID;
	END$$
delimiter ;

/*
@@ not update to mf project and live server
*/
DROP PROCEDURE getPageDetails;
delimiter $$
	CREATE PROCEDURE getPageDetails(IN pID VARCHAR(255),IN sectionAlias VARCHAR(255),IN parentId INT)

	BEGIN
		SELECT page.id as id,page.alias as alias,page.name as name,parent,sort,status,content,custom,s.alias as section,created,modified,layer,inherit,accessto,accessfrom,navigation
		FROM page
		INNER JOIN jmsp.section s
		ON page.section = s.id
		WHERE page.alias = pID AND s.alias = sectionAlias AND page.parent = parentId;
	END$$
delimiter ;

DROP PROCEDURE getPageDetailsByID;
delimiter $$
	CREATE PROCEDURE getPageDetailsByID(IN pID INT(255))

	BEGIN
		SELECT page.id as id,page.alias as alias,page.name as name,parent,sort,status,content,custom,s.alias as section,created,modified,layer,inherit,accessto,accessfrom,navigation
		FROM page
		LEFT JOIN jmsp.section s
		ON s.id = page.section
		WHERE page.id = pID;
	END$$
delimiter ;

DROP PROCEDURE getPageSections;
delimiter $$

	CREATE PROCEDURE getPageSections()

	BEGIN
		SELECT *
		FROM jmsp.section
		ORDER BY alias;
	END$$
delimiter ;

DROP PROCEDURE getNavPagesWithinParent;
delimiter $$

	CREATE PROCEDURE getNavPagesWithinParent(IN parentId INT)
	BEGIN
		SELECT distinct page.id as id,page.alias as alias,page.name as name,page.parent as parent,page.sort as sort,page.status as status, page.navigation as navigation,page.inherit as inherit,page.content as content,page.custom as custom,s.alias as section,page.created as created, page.modified as modified,page.layer as layer,page.accessfrom as accessfrom,page.accessto as accessto
		FROM page
		INNER JOIN jmsp.section s
		ON s.id = page.section
		WHERE page.navigation = 1 AND page.parent = parentId AND page.status > 0 AND (NOW() BETWEEN accessfrom AND page.accessto)
		ORDER BY page.sort;
	END$$
delimiter ;

DROP PROCEDURE getNavPages;
delimiter $$

	CREATE PROCEDURE getNavPages(IN sectionAlias VARCHAR(255),IN userid VARCHAR(255))

	BEGIN
		SELECT distinct page.id as id,page.alias as alias,page.name as name,page.parent as parent,page.sort as sort,page.status as status, page.navigation as navigation,page.inherit as inherit,page.content as content,page.custom as custom,s.alias as section,page.created as created, page.modified as modified,page.layer as layer,page.accessfrom as accessfrom,page.accessto as accessto
		FROM page
		INNER JOIN gp
		ON page.id = gp.pageID
		INNER JOIN jmsp.section s
		ON s.id = page.section
		WHERE page.navigation = 1 AND page.status > 0 AND (NOW() BETWEEN accessfrom AND page.accessto) AND s.alias=sectionAlias AND gp.groupID 
		IN (SELECT groupID FROM ug INNER JOIN jmsp.user u ON ug.userID = u.id WHERE u.user_name = userid)
		ORDER BY page.sort;
	END$$
delimiter ;

/*
not update to live server and mf project yet
have done update
*/
DROP PROCEDURE getRootSubPages;
delimiter $$

	CREATE PROCEDURE getRootSubPages(IN sectionID INT(255),IN userid VARCHAR(255))
	
	BEGIN
	
		SELECT p.id as id,p.alias as alias,p.name as name,p.parent as parent,p.sort as sort,p.status as status,p.navigation as navigation,p.inherit as inherit,p.content as content,p.custom as custom,s.alias as section,p.created as created, p.modified as modified,p.layer as layer,hasChildren(p.id) as children,p.accessfrom as accessfrom,p.accessto as accessto
		FROM page p
		INNER JOIN jmsp.section s
		ON p.section = s.id
		WHERE parent = 0 
		AND p.section = sectionID
		AND checkPagePermission(p.alias,s.alias,userid,0) > 0
		ORDER BY p.sort;
	END$$
delimiter ;

/*
not update to live server and mf project yet
have done update
*/
DROP PROCEDURE getSubPages;
delimiter $$

	CREATE PROCEDURE getSubPages(IN pageID INT(255),IN userid VARCHAR(255))
	
	BEGIN
	
		SELECT p2.id as id,p2.alias as alias,p2.name as name,p2.parent as parent,p2.sort as sort,p2.status as status,p2.navigation as navigation,p2.inherit as inherit,p2.content as content,p2.custom as custom,p2.alias as section,p2.created as created, p2.modified as modified,p2.layer as layer,hasChildren(p2.id) as children,p2.accessto as accessto,p2.accessfrom as accessfrom
		FROM page p1
		INNER JOIN page p2
		ON p1.id = p2.parent
		INNER JOIN jmsp.section s
		ON p2.section = s.id
		WHERE p1.id = pageID
		AND checkPagePermission(p2.alias,s.alias,userid,p2.parent) > 0
		ORDER BY p2.sort;
	END$$
delimiter ;
/*
not update to live server and mf project yet
done update
*/
DROP PROCEDURE getSubPageList;
delimiter $$

	CREATE PROCEDURE getSubPageList(IN pageAlias VARCHAR(255),IN sectionAlias VARCHAR(255),IN userid VARCHAR(255),IN parentId INT)

	BEGIN
	
		declare rootPageID INT;
	
		IF(pageAlias = '') 
			THEN
				SELECT p.id as id,p.alias as alias,p.name as name,p.parent as parent,p.sort as sort,p.status as status,p.navigation as navigation,p.inherit as inherit,p.content as content,p.custom as custom,s.alias as section,p.created as created, p.modified as modified,p.layer as layer,p.accessto as accessto,p.accessfrom as accessfrom
				FROM page p
				INNER JOIN jmsp.section s
				ON p.section = s.id
				WHERE parent = 0 
				AND s.alias = sectionAlias
				AND checkPagePermission(p.alias,sectionAlias,userid,0) > 0
				ORDER BY p.sort;
		ELSE
				SELECT p2.id as id,p2.alias as alias,p2.name as name,p2.parent as parent,p2.sort as sort,p2.status as status,p2.navigation as navigation,p2.inherit as inherit,p2.content as content,p2.custom as custom,p2.alias as section,p2.created as created, p2.modified as modified,p2.layer as layer,p2.accessto as accessto,p2.accessfrom as accessfrom
				FROM page p1
				INNER JOIN page p2
				ON p1.id = p2.parent
				INNER JOIN jmsp.section s
				ON p2.section = s.id
				WHERE p1.alias = pageAlias 
				AND s.alias = sectionAlias
				AND checkPagePermission(p2.alias,sectionAlias,userid,parentId) > 0
				ORDER BY p.sort;
		END IF;
		
	END$$
delimiter ;

DROP PROCEDURE createPage;
delimiter $$
	CREATE PROCEDURE createPage(IN pageName VARCHAR(255),IN pageAlias VARCHAR(255))

	BEGIN
	
		declare pageId INT;
		declare userId INT;
	
		INSERT INTO page (alias,name,created) VALUES(pageAlias,pageName,NOW());
		
		SELECT LAST_INSERT_ID() INTO pageId;
		
		INSERT INTO gp (groupID,pageID) 
		VALUES(1,pageId);
		
		select pageId as id;
		
	END$$
delimiter ;

DROP PROCEDURE updatePageData;
delimiter $$
	CREATE PROCEDURE updatePageData(IN id INT(255),IN name VARCHAR(255),IN alias VARCHAR(255),IN accesstype TINYINT,IN accessfrom VARCHAR(255),IN accessto VARCHAR(255),IN status TINYINT,IN navigation TINYINT,IN custom VARCHAR(255))
	
	BEGIN
		UPDATE page SET page.name = name,page.alias = alias,page.inherit = accesstype,page.accessfrom = accessfrom,page.accessto = accessto,page.status = status,page.navigation = navigation,page.custom = custom WHERE page.id = id;
	END$$
delimiter ;

DROP PROCEDURE savePageContent;
delimiter $$
	CREATE PROCEDURE savePageContent(IN id INT(255),IN content text)
	
	BEGIN
		UPDATE page SET page.content = content,modified = now() WHERE page.id = id;
		SELECT ROW_COUNT();
	END$$
delimiter ;

DROP PROCEDURE updatePageGroups;
delimiter $$
	CREATE PROCEDURE updatePageGroups(IN id INT(255),IN groupId INT(255))
	BEGIN
		declare flag INT;
		SELECT count(*) INTO flag FROM gp WHERE gp.groupID = groupId AND pageID = id;
		if(flag = 0)
		THEN
		INSERT INTO gp (groupID,pageID) VALUES (groupId,id);
		END if;
	END$$
delimiter ;

DROP PROCEDURE cleanPageGroups;
delimiter $$
	CREATE PROCEDURE cleanPageGroups(IN id INT(255))
	BEGIN
		delete from gp WHERE pageID = id;
	END$$
delimiter ;

DROP PROCEDURE saveRootOrders;
delimiter $$
	CREATE PROCEDURE saveRootOrders(IN pageId INT(255),IN sectionId INT(255),IN orderNum INT(255))
	
	BEGIN
		UPDATE page SET page.section = sectionId,page.sort= orderNum,page.parent=0,modified=NOW() WHERE id = pageId;
		
		SELECT ROW_COUNT();
	END$$
delimiter ;

DROP PROCEDURE saveSubOrders;
delimiter $$
	CREATE PROCEDURE saveSubOrders(IN pageId INT(255),IN parentId INT(255),IN orderNum INT(255))
	BEGIN
		declare sectionID INT;
		
		select page.section INTO sectionID FROM page WHERE page.id=parentId;
		
		UPDATE page SET page.parent = parentId,page.sort= orderNum,page.section=sectionID,modified=NOW() WHERE id = pageId;
		
		SELECT ROW_COUNT();
	END$$
delimiter ;

DROP PROCEDURE getPublicRootPages;
delimiter $$
	CREATE PROCEDURE getPublicRootPages(IN sectionAlias VARCHAR(255))
	BEGIN
		
		SELECT distinct page.id as id,page.alias as alias,page.name as name,page.parent as parent,page.sort as sort,page.status as status, page.navigation as navigation,page.inherit as inherit,page.content as content,page.custom as custom,s.alias as section,page.created as created, page.modified as modified,page.layer as layer,page.accessfrom as accessfrom,page.accessto as accessto
		FROM page
		INNER JOIN gp
		ON page.id = gp.pageID
		INNER JOIN jmsp.section s
		ON s.id = page.section
		WHERE page.status > 0 AND (NOW() BETWEEN accessfrom AND page.accessto) AND s.alias=sectionAlias AND gp.groupID 
		IN (SELECT id FROM jmsp.group WHERE ispublic = 1)
		ORDER BY page.sort;
	END$$
delimiter ;

DROP PROCEDURE getAllPages;
delimiter $$
	CREATE PROCEDURE getAllPages(IN sectionAlias VARCHAR(255))
	BEGIN
		
		SELECT distinct page.id as id,page.alias as alias,page.name as name,page.parent as parent,page.sort as sort,page.status as status, page.navigation as navigation,page.inherit as inherit,page.content as content,page.custom as custom,s.alias as section,page.created as created, page.modified as modified,page.layer as layer,page.accessfrom as accessfrom,page.accessto as accessto
		FROM page
		INNER JOIN gp
		ON page.id = gp.pageID
		INNER JOIN jmsp.section s
		ON s.id = page.section
		WHERE page.status > 0 AND (NOW() BETWEEN accessfrom AND page.accessto) AND s.alias=sectionAlias AND gp.groupID 
		IN (SELECT id FROM jmsp.group WHERE ispublic = 1)
		ORDER BY page.parent,page.sort;
	END$$
delimiter ;

/*
	Image
####################################################
*/
DROP PROCEDURE getImageList;
delimiter $$
	CREATE PROCEDURE getImageList()
	BEGIN
		SELECT * FROM image;
	END$$
delimiter ;

DROP PROCEDURE getImageListWithoutData;
delimiter $$
	CREATE PROCEDURE getImageListWithoutData()
	BEGIN
		SELECT id,created,name,description,image.size as size FROM image;
	END$$
delimiter ;

DROP PROCEDURE getImageDetails;
delimiter $$
	CREATE PROCEDURE getImageDetails(IN imageId INT(255))
	BEGIN
		SELECT * FROM image WHERE id = imageId;
	END$$
delimiter ;

DROP PROCEDURE updateImageData;
delimiter $$
	CREATE PROCEDURE updateImageData(IN id INT, IN thumbnail MEDIUMBLOB,IN content LONGBLOB,IN size1 INT)
	BEGIN
		UPDATE image SET image.thumbnail = thumbnail,image.big = content,image.size = size1 WHERE image.id = id;
	END$$
delimiter ;

DROP PROCEDURE updateImageDetails;
delimiter $$
	CREATE PROCEDURE updateImageDetails(IN id INT, IN name VARCHAR(255),IN title VARCHAR(255),IN alt VARCHAR(255),IN description TEXT)
	BEGIN
		UPDATE image SET image.name = name,image.title = title,image.alias = alt,image.description = description WHERE image.id = id;
	END$$
delimiter ;

DROP PROCEDURE addImageDetails;
delimiter $$
	CREATE PROCEDURE addImageDetails(IN name VARCHAR(255),IN thumbnail MEDIUMBLOB,IN content LONGBLOB,IN size1 INT)
	BEGIN
		INSERT INTO image(name,thumbnail,big,size,created) values(name,thumbnail,content,size1,NOW());
		
		SELECT LAST_INSERT_ID() as id;
	END$$
delimiter ;

DROP PROCEDURE getPageImage;
delimiter $$
	CREATE PROCEDURE getPageImage(IN pageId INT(255))
	BEGIN
		SELECT id,created,name,description,image.size as size,alias,image.type as type,title
		FROM image
		INNER JOIN pageimage
		ON image.id = pageimage.imageId
		WHERE pageimage.pageId = pageId;
	END$$
delimiter ;

DROP PROCEDURE cleanPageImage;
delimiter $$
	CREATE PROCEDURE cleanPageImage(IN pageId INT(255))
	BEGIN
		DELETE FROM pageimage WHERE pageimage.pageId = pageId;
	END$$
delimiter ;

DROP PROCEDURE updatePageImage;
delimiter $$
	CREATE PROCEDURE updatePageImage(IN pageId1 INT(255),IN imageId1 INT(255))
	BEGIN
		INSERT INTO pageimage(pageId,imageId) VALUES (pageId1,imageId1);
	END$$
delimiter ;

/*
	Youtube
####################################################
*/
DROP PROCEDURE getYoutubeList;
delimiter $$
	CREATE PROCEDURE getYoutubeList()
	BEGIN
		SELECT * FROM youtube;
	END$$
delimiter ;

/*done*/
DROP PROCEDURE getYoutubeListWithoutData;
delimiter $$
	CREATE PROCEDURE getYoutubeListWithoutData()
	BEGIN
		SELECT id,url,name,youtube.size as size,youtube.type as type,created,description,title 
		FROM youtube;
	END$$
delimiter ;

/*done*/
DROP PROCEDURE addYoutube;
delimiter $$
	CREATE PROCEDURE addYoutube(IN url1 VARCHAR(255),IN thumbnail1 mediumblob,IN name1 VARCHAR(50),IN size1 INT,IN type1 VARCHAR(30),IN description1 TEXT,IN title VARCHAR(255))
	BEGIN
		INSERT INTO youtube (url,thumbnail,name,size,type,description,title) VALUES (url1,thumbnail1,name1,size1,type1,description1,title);
		
		SELECT LAST_INSERT_ID() as id;
	END$$
delimiter ;

/* done*/
DROP PROCEDURE getYoutubeDetails;
delimiter $$
	CREATE PROCEDURE getYoutubeDetails(IN id INT)
	BEGIN
		SELECT id,url,name,youtube.size as size,youtube.type as type,created,description,title
		FROM youtube
		WHERE youtube.id = id;
	END$$
delimiter ;

/* done*/
DROP PROCEDURE getYoutubeFullDetails;
delimiter $$
	CREATE PROCEDURE getYoutubeFullDetails(IN id INT)
	BEGIN
		SELECT id,thumbnail,url,name,youtube.size as size,youtube.type as type,created,description,title
		FROM youtube
		WHERE youtube.id = id;
	END$$
delimiter ;

/*done*/
DROP PROCEDURE updateYoutube;
delimiter $$
	CREATE PROCEDURE updateYoutube(IN id INT,IN url VARCHAR(255),IN thumbnail1 mediumblob,IN name1 VARCHAR(50),IN size1 INT,IN type1 VARCHAR(30),IN description1 TEXT,IN title VARCHAR(255))
	BEGIN
		UPDATE youtube SET youtube.url=url,youtube.thumbnail=thumbnail1,youtube.name=name1,youtube.size=size1,youtube.type=type1,youtube.description=description1,youtube.title =title WHERE youtube.id = id;
	END$$
delimiter ;

/*done */
DROP PROCEDURE getPageYoutube;
delimiter $$
	CREATE PROCEDURE getPageYoutube(IN id INT)
	BEGIN
		SELECT youtube.id as id,url,name,youtube.size as size,youtube.type as type,created,description,youtube.title as title
		FROM youtube
		INNER JOIN pageyoutube
		ON youtube.id = pageyoutube.youtubeId
		WHERE pageyoutube.pageId = id;
	END$$
delimiter ;

DROP PROCEDURE cleanPageYoutube;
delimiter $$
	CREATE PROCEDURE cleanPageYoutube(IN id INT)
	BEGIN
		DELETE FROM pageyoutube WHERE pageId = id;
	END$$
delimiter ;

DROP PROCEDURE updatePageYoutube;
delimiter $$
	CREATE PROCEDURE updatePageYoutube(IN id INT,IN youtube INT)
	BEGIN
		INSERT INTO pageyoutube(pageId,youtubeId) VALUES (id,youtube);
	END$$
delimiter ;

/*
add by adrian on FEb 20 11:06am
not update to system yet
done update ..
*/
DROP PROCEDURE deleteYoutube;
delimiter $$
	CREATE PROCEDURE deleteYoutube(IN id INT)
	BEGIN
		DELETE FROM youtube WHERE youtube.id = id;
		DELETE FROM pageyoutube WHERE youtubeId = id;
	END$$
delimiter ;

/*
	Function
####################################################
*/
DROP FUNCTION hasChildren;
delimiter $$

	CREATE FUNCTION hasChildren(pageID INT(255)) RETURNS INT
	DETERMINISTIC
	BEGIN
		declare hasChildren INT;
		SELECT COUNT(*) INTO hasChildren
		FROM page
		WHERE parent=pageID;
		RETURN hasChildren;
	END$$
delimiter ;

/*
 not update to live server and mf project yet
 done update
*/
DROP FUNCTION checkPagePermission;
delimiter $$

	CREATE FUNCTION checkPagePermission(pageAlias VARCHAR(255),sectionAlias VARCHAR(255),userid VARCHAR(255),parentId INT) RETURNS INT
	DETERMINISTIC
	BEGIN
		declare resultCount INT;
		declare ispublic INT;
		declare pageId INT;
/* 		declare rootPageID INT;
		SELECT getRootPageGroupID(pageAlias,sectionID) INTO rooPageID; */
		
		SELECT getRootPageGroupID(pageAlias,sectionAlias,parentId) INTO pageId;
		
		SELECT IsPagePublic(pageId) INTO ispublic;
		
		if(ispublic = 0) THEN
			SELECT COUNT(*) INTO resultCount
			FROM page
			INNER JOIN gp
			ON page.id = gp.pageID
			INNER JOIN ug
			ON ug.groupID = gp.groupID
			INNER JOIN jmsp.user u
			ON ug.userID = u.id
			WHERE u.user_name = userid AND page.id = getRootPageGroupID(pageAlias,sectionAlias,parentId);
			
			RETURN resultCount;
		ELSE
			RETURN 1;
		END IF;	
	END$$
delimiter ;

DROP FUNCTION IsPagePublic;
delimiter $$
	CREATE FUNCTION IsPagePublic(pageId INT) RETURNS INT
	DETERMINISTIC
	BEGIN
		declare ispublic INT;
		SELECT COUNT(*) into ispublic
		FROM page
		INNER JOIN gp
		ON page.id = gp.pageID
		INNER JOIN jmsp.group g
		ON gp.groupID = g.id
		WHERE g.ispublic = 1 AND page.id = pageId;
		
		RETURN ispublic;
	END$$
delimiter ;
/*
@@ not update to cms and live server yet
*/
DROP FUNCTION getRootPageGroupID;
delimiter $$

	CREATE FUNCTION getRootPageGroupID(pageAlias VARCHAR(255),sectionAlias VARCHAR(255),parentId INT) RETURNS BIGINT(255)
	DETERMINISTIC
	BEGIN
		declare pageInherit INT;
		declare pageParent INT;
		declare pageId INT;
		declare resultCount INT DEFAULT 0;
		select inherit,parent,page.id,count(*) INTO pageInherit,pageParent,pageId,resultCount FROM page INNER JOIN jmsp.section s ON page.section = s.id WHERE page.alias = pageAlias AND s.alias = sectionAlias AND page.parent = parentId;
		IF(resultCount != 0) THEN
		label1: LOOP
			IF (pageParent = 0 || (pageInherit = 0 && pageParent != 0)) THEN RETURN pageId;
			ELSE
				SELECT inherit,parent,id INTO pageInherit,pageParent,pageId FROM page WHERE page.id = pageParent;
				ITERATE label1;
			END IF;
			LEAVE label1;
		END LOOP label1;
		ELSE
			RETURN 0;
		END IF;
	END$$
delimiter ;

