/*
#############################################
productions
*/
/*
done the update
*/
DROP PROCEDURE getProductions;
delimiter $$

	CREATE PROCEDURE getProductions(IN pageID INT(255),IN userid VARCHAR(255),IN imgFolderAlias VARCHAR(20),IN smallAlias VARCHAR(20),IN medAlias VARCHAR(20),IN bigAlias VARCHAR(20))
	
	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,
			getProductionImageId(p2.id,userid,imgFolderAlias,smallAlias) as small,
			getImageAlias(small) as smallImageAlias,
			getProductionImageId(p2.id,userid,imgFolderAlias,medAlias) as med,
			getImageAlias(med) as medImageAlias,
			getProductionImageId(p2.id,userid,imgFolderAlias,bigAlias) as big,
			getImageAlias(big) as bigImageAlias
		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 ;

DROP PROCEDURE getImageByPageAlias;
delimiter $$
	CREATE PROCEDURE getImageByPageAlias(IN parentId INT,IN userId VARCHAR(255),IN alias1 VARCHAR(50))
	BEGIN
		SELECT i.id as id,i.created as created,i.name as name,description,size,i.alias as alias,i.type as type,i.title as title
		FROM page p
		INNER JOIN jmsp.section s
		ON p.section = s.id
		INNER JOIN pageimage pi
		ON p.id = pi.pageId
		INNER JOIN image i
		ON i.id = pi.imageId
		WHERE checkPagePermission(p.alias,s.alias,userid,p.parent) > 0
		AND p.parent = getChildrenIDByAlias(alias1,parentId,userId)
		ORDER BY p.sort;
	END$$
delimiter ;

DROP PROCEDURE getYoutubeByPageAlias;
delimiter $$
	CREATE PROCEDURE getYoutubeByPageAlias(IN parentId INT,IN userId VARCHAR(255),IN alias1 VARCHAR(50))
	BEGIN
		SELECT y.id as id,y.url as url, y.thumbnail as thumbnail, y.name as name,y.size as size ,y.type as type,y.created as created,y.description as description,y.title as title
		FROM page p
		INNER JOIN jmsp.section s
		ON p.section = s.id
		INNER JOIN pageyoutube py
		ON p.id = py.pageId
		INNER JOIN youtube y
		ON y.id = py.youtubeId
		WHERE checkPagePermission(p.alias,s.alias,userid,p.parent) > 0
		AND p.parent = getChildrenIDByAlias(alias1,parentId,userId)
		ORDER BY p.sort;
	END$$
delimiter ;


/*
done the update
*/
DROP FUNCTION getProductionImageId;
delimiter $$
	CREATE FUNCTION getProductionImageId(parentId INT,userid VARCHAR(255),imgFolderAlias VARCHAR(20),imagePageAlias VARCHAR(20)) RETURNS INT
	DETERMINISTIC
	BEGIN
		declare childrenId INT;
		declare pImageId INT;

		SELECT getChildrenIDByAlias(imgFolderAlias,parentId,userid) INTO childrenId;

		SELECT imageId INTO pImageId 
		FROM pageimage WHERE pageId =  getChildrenIDByAlias(imagePageAlias,childrenId,userid);

		RETURN pImageId;
	END$$
delimiter ;

/*
done the update
*/
DROP FUNCTION getImageAlias;
delimiter $$
	CREATE FUNCTION getImageAlias(id INT) RETURNS VARCHAR(255)
	DETERMINISTIC
	BEGIN
		declare imageAlias VARCHAR(255);

		SELECT alias into imageAlias FROM image WHERE image.id = id;

		RETURN imageAlias;
	END$$
delimiter ;

/*
done the update
*/
DROP FUNCTION getChildrenIDByAlias;
delimiter $$
	CREATE FUNCTION getChildrenIDByAlias(pageAlias VARCHAR(255),parentId INT,userid VARCHAR(255)) RETURNS INT
	DETERMINISTIC
	BEGIN
		declare childrenId INT;
		SELECT page.id INTO childrenId
		FROM page 
		INNER JOIN jmsp.section s
		ON page.section = s.id 
		WHERE page.parent = parentId 
		AND page.alias = pageAlias 
		AND checkPagePermission(page.alias,s.alias,userid,page.parent) > 0;

		RETURN childrenId;
	END$$
delimiter ;