-- http://www.phpmyadmin.net
--




--
-- Database: `jmsp`
--

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `addGroup`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `addImageDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `addUser`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `addUser1`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `addUser1`()
BEGIN
		
		SELECT * from cms.user; 
	END$$

DROP PROCEDURE IF EXISTS `addUserGroup`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `addUserGroup`(IN userId INT(255),IN groupId INT(255))
BEGIN
		INSERT INTO ug (userID,groupID)
		VALUES (userId,groupId);
	END$$

DROP PROCEDURE IF EXISTS `addYoutube`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `addYoutube`(IN url1 VARCHAR(255),IN thumbnail1 mediumblob,IN name1 VARCHAR(50),IN size1 INT,IN type1 VARCHAR(30),IN description1 TEXT)
BEGIN
		INSERT INTO youtube (url,thumbnail,name,size,type,description) VALUES (url1,thumbnail1,name1,size1,type1,description1);
		
		SELECT LAST_INSERT_ID() as id;
	END$$

DROP PROCEDURE IF EXISTS `cleanGroupGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `cleanGroupGroups`(IN id INT)
BEGIN
		DELETE FROM gg WHERE idedit = id;
	END$$

DROP PROCEDURE IF EXISTS `cleanPageGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `cleanPageGroups`(IN id INT(255))
BEGIN
		delete from gp WHERE pageID = id;
	END$$

DROP PROCEDURE IF EXISTS `cleanPageImage`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `cleanPageImage`(IN pageId INT(255))
BEGIN
		DELETE FROM pageimage WHERE pageimage.pageId = pageId;
	END$$

DROP PROCEDURE IF EXISTS `cleanPageYoutube`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `cleanPageYoutube`(IN id INT)
BEGIN
		DELETE FROM pageyoutube WHERE pageId = id;
	END$$

DROP PROCEDURE IF EXISTS `clearUserGroup`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `clearUserGroup`(IN user_id INT(255))
BEGIN
		DELETE FROM ug WHERE userID= user_id;
	END$$

DROP PROCEDURE IF EXISTS `createPage`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `deleteUser`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `deleteUser`(IN userId VARCHAR(255))
BEGIN
		DELETE FROM cms.user WHERE id = userId;
	END$$

DROP PROCEDURE IF EXISTS `getAllPages`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getGroupCanEditGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getGroupDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getImageDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getImageDetails`(IN imageId INT(255))
BEGIN
		SELECT * FROM image WHERE id = imageId;
	END$$

DROP PROCEDURE IF EXISTS `getImageList`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getImageList`()
BEGIN
		SELECT * FROM image;
	END$$

DROP PROCEDURE IF EXISTS `getImageListWithoutData`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getImageListWithoutData`()
BEGIN
		SELECT id,created,name,description,image.size as size FROM image;
	END$$

DROP PROCEDURE IF EXISTS `getNavPages`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getPageDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getPageDetails`(IN pID VARCHAR(255),IN sectionAlias VARCHAR(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
		INNER JOIN jmsp.section s
		ON page.section = s.id
		WHERE page.alias = pID AND s.alias = sectionAlias;
	END$$

DROP PROCEDURE IF EXISTS `getPageDetailsByID`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getPageGroup`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getPageGroup`(IN pAlias VARCHAR(255),IN sectionAlias VARCHAR(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
		INNER JOIN jmsp.section s
		ON s.id = p.section
		WHERE p.id = getRootPageGroupID(pAlias,sectionAlias) AND s.alias = sectionAlias;
	END$$

DROP PROCEDURE IF EXISTS `getPageImage`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getPageRealGroupsByID`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getPageSections`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getPageSections`()
BEGIN
		SELECT *
		FROM jmsp.section
		ORDER BY alias;
	END$$

DROP PROCEDURE IF EXISTS `getPageYoutube`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getPageYoutube`(IN id INT)
BEGIN
		SELECT youtube.id as id,url,name,youtube.size as size,youtube.type as type,created,description
		FROM youtube
		INNER JOIN pageyoutube
		ON youtube.id = pageyoutube.youtubeId
		WHERE pageyoutube.pageId = id;
	END$$

DROP PROCEDURE IF EXISTS `getPublicNavPages`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getPublicNavPages`(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 cms.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 id FROM cms.group WHERE ispublic = 1)
		ORDER BY page.sort;
	END$$

DROP PROCEDURE IF EXISTS `getPublicRootPages`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getRootSubPages`$$
CREATE DEFINER=`root`@`127.0.0.1` 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
		ORDER BY p.sort;
	END$$

DROP PROCEDURE IF EXISTS `getSubPageList`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getSubPageList`(IN pageAlias VARCHAR(255),IN sectionAlias VARCHAR(255),IN userid VARCHAR(255))
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
				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) > 0
				ORDER BY p.sort;
		END IF;
		
	END$$

DROP PROCEDURE IF EXISTS `getSubPages`$$
CREATE DEFINER=`root`@`127.0.0.1` 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) > 0
		ORDER BY p2.sort;
	END$$

DROP PROCEDURE IF EXISTS `getUser`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUserById`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUserEditableGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUserEditGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUserGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUserId`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getUserId`(IN user_id VARCHAR(255))
BEGIN
		SELECT id
		FROM jmsp.user
		WHERE user_name = user_id;
		
	END$$

DROP PROCEDURE IF EXISTS `getUserNames`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getUsers`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `getYoutubeDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getYoutubeDetails`(IN id INT)
BEGIN
		SELECT id,url,name,youtube.size as size,youtube.type as type,created,description
		FROM youtube
		WHERE youtube.id = id;
	END$$

DROP PROCEDURE IF EXISTS `getYoutubeFullDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getYoutubeFullDetails`(IN id INT)
BEGIN
		SELECT id,thumbnail,url,name,youtube.size as size,youtube.type as type,created,description
		FROM youtube
		WHERE youtube.id = id;
	END$$

DROP PROCEDURE IF EXISTS `getYoutubeList`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getYoutubeList`()
BEGIN
		SELECT * FROM youtube;
	END$$

DROP PROCEDURE IF EXISTS `getYoutubeListWithoutData`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `getYoutubeListWithoutData`()
BEGIN
		SELECT id,url,name,youtube.size as size,youtube.type as type,created,description 
		FROM youtube;
	END$$

DROP PROCEDURE IF EXISTS `savePageContent`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `saveRootOrders`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `saveSubOrders`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `setUser`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `setUserPwd`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `setUserPwd`(IN userId INT(255),IN password VARCHAR(255))
BEGIN
		UPDATE jmsp.user u SET u.password = password WHERE id = userId;
	END$$

DROP PROCEDURE IF EXISTS `updateGroupData`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `updateGroupGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `updateGroupGroups`(IN id INT,IN editid INT)
BEGIN
		INSERT INTO gg (gg.id,gg.idedit) VALUE (id,editid);
	END$$

DROP PROCEDURE IF EXISTS `updateImageData`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `updateImageDetails`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `updatePageData`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `updatePageGroups`$$
CREATE DEFINER=`root`@`127.0.0.1` 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$$

DROP PROCEDURE IF EXISTS `updatePageImage`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `updatePageImage`(IN pageId1 INT(255),IN imageId1 INT(255))
BEGIN
		INSERT INTO pageimage(pageId,imageId) VALUES (pageId1,imageId1);
	END$$

DROP PROCEDURE IF EXISTS `updatePageYoutube`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `updatePageYoutube`(IN id INT,IN youtube INT)
BEGIN
		INSERT INTO pageyoutube(pageId,youtubeId) VALUES (id,youtube);
	END$$

DROP PROCEDURE IF EXISTS `updateYoutube`$$
CREATE DEFINER=`root`@`127.0.0.1` 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)
BEGIN
		UPDATE youtube SET youtube.url=url,youtube.thumbnail=thumbnail1,youtube.name=name1,youtube.size=size1,youtube.type=type1,youtube.description=description1 WHERE youtube.id = id;
	END$$

--
-- Functions
--
DROP FUNCTION IF EXISTS `checkPagePermission`$$
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `checkPagePermission`(pageAlias VARCHAR(255),sectionAlias VARCHAR(255),userid VARCHAR(255)) RETURNS int(11)
    DETERMINISTIC
BEGIN
		declare resultCount INT;
/* 		declare rootPageID INT;
		SELECT getRootPageGroupID(pageAlias,sectionID) INTO rooPageID; */
		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);
		
		RETURN resultCount;
	END$$

DROP FUNCTION IF EXISTS `getRootPageGroupID`$$
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `getRootPageGroupID`(pageAlias VARCHAR(255),sectionAlias VARCHAR(255)) 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;
		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$$

DROP FUNCTION IF EXISTS `hasChildren`$$
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `hasChildren`(pageID INT(255)) RETURNS int(11)
    DETERMINISTIC
BEGIN
		declare hasChildren INT;
		SELECT COUNT(*) INTO hasChildren
		FROM page
		WHERE parent=pageID;
		RETURN hasChildren;
	END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `gg`
--

CREATE TABLE IF NOT EXISTS `gg` (
  `id` bigint(255) NOT NULL,
  `idedit` bigint(255) NOT NULL,
  PRIMARY KEY (`id`,`idedit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `gg`
--

INSERT INTO `gg` (`id`, `idedit`) VALUES
(1, 0),
(1, 1),
(1, 2),
(1, 3),
(1, 9),
(1, 10);

-- --------------------------------------------------------

--
-- Table structure for table `gp`
--

CREATE TABLE IF NOT EXISTS `gp` (
  `groupID` varchar(255) NOT NULL,
  `pageID` varchar(255) NOT NULL,
  PRIMARY KEY (`groupID`,`pageID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `gp`
--

INSERT INTO `gp` (`groupID`, `pageID`) VALUES
('1', '1'),
('1', '2'),
('1', '30'),
('1', '31'),
('1', '32'),
('1', '33'),
('1', '34'),
('1', '36'),
('1', '37'),
('1', '39'),
('1', '4'),
('1', '41'),
('1', '42'),
('1', '43'),
('1', '49'),
('1', '5'),
('1', '50'),
('1', '51'),
('1', '52'),
('1', '53'),
('1', '55'),
('1', '56'),
('1', '57'),
('1', '58'),
('1', '59'),
('1', '6'),
('1', '60'),
('1', '61'),
('1', '62'),
('1', '63'),
('1', '69'),
('1', '70'),
('1', '71'),
('1', '72'),
('1', '73'),
('1', '8'),
('1', '9'),
('2', '31'),
('2', '72'),
('3', '3'),
('3', '31'),
('3', '32'),
('3', '33'),
('3', '36'),
('3', '37'),
('3', '39'),
('3', '41'),
('3', '43'),
('3', '5'),
('3', '59'),
('3', '6'),
('3', '60'),
('3', '8');

-- --------------------------------------------------------

--
-- Table structure for table `group`
--

CREATE TABLE IF NOT EXISTS `group` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `islock` int(11) NOT NULL,
  `approve` int(11) NOT NULL,
  `ispublic` tinyint(4) NOT NULL,
  `isadmin` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `group`
--

INSERT INTO `group` (`id`, `name`, `created`, `modified`, `islock`, `approve`, `ispublic`, `isadmin`) VALUES
(1, 'Admin', '0000-00-00 00:00:00', '2013-02-07 11:15:10', 0, 0, 0, 1),
(2, 'Member', '0000-00-00 00:00:00', '2013-01-31 16:39:45', 1, 1, 0, 0),
(3, 'Unregistered', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 0, 0, 1, 0),
(4, 'mres7000', '2013-01-31 17:34:51', '0000-00-00 00:00:00', 0, 0, 0, 0),
(5, 'mres7001', '2013-01-31 17:37:01', '0000-00-00 00:00:00', 1, 1, 0, 0),
(6, 'hhh', '2013-01-31 17:39:42', '0000-00-00 00:00:00', 0, 0, 0, 0),
(8, 'hhj', '2013-01-31 17:40:12', '0000-00-00 00:00:00', 0, 0, 0, 0),
(9, 'mres2300', '2013-01-31 17:41:20', '2013-01-31 17:44:07', 1, 1, 0, 0),
(10, 'mres7500', '2013-01-31 17:41:51', '0000-00-00 00:00:00', 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `image`
--

CREATE TABLE IF NOT EXISTS `image` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `thumbnail` mediumblob NOT NULL,
  `big` longblob NOT NULL,
  `created` datetime NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `size` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `image`
--

INSERT INTO `image` (`id`, `thumbnail`, `big`, `created`, `name`, `description`, `size`, `alias`, `type`, `title`) VALUES
(1, 0x[encoded data, 7266 characters], 0x[encoded data, 399108 characters], '2013-02-06 15:53:21', '37312f6d.jpg', 'hello world1awsefrawefr', '199554', '37312f6d.jpg', 'image/jpeg', '');
