<?php

/***************************************************************************
*                            Dolphin Smart Community Builder
*                              -------------------
*     begin                : Mon Mar 23 2006
*     copyright            : (C) 2007 BoonEx Group
*     website              : http://www.boonex.com
* This file is part of Dolphin - Smart Community Builder
*
* Dolphin is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the
* Free Software Foundation; either version 2 of the
* License, or  any later version.
*
* Dolphin is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License for more details.
* You should have received a copy of the GNU General Public License along with Dolphin,
* see license.txt file; if not, write to marketing@boonex.com
***************************************************************************/

require_once( BX_DIRECTORY_PATH_CLASSES . 'BxDolDb.php' );

class BxBlogsDb extends BxDolDb {	
	var $_oConfig;

	/*
	* Constructor.
	*/
	function BxBlogsDb(&$oConfig) {
		parent::BxDolDb();

		$this->_oConfig = $oConfig;
	}

	function getMembershipActions() {
	    $sSql = "SELECT `ID` AS `id`, `Name` AS `name` FROM `sys_acl_actions` WHERE `Name`='use blog' OR `Name`='view blog'";
	    return $this->getAll($sSql);
	}

	function getPostCaptionByID($iPostID) {
		$sSQL = "
			SELECT `PostCaption`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostID`='{$iPostID}'
		";
		return $this->getOne($sSQL);
	}

	function getPostCaptionAndUriByID($iPostID) {
		$sSQL = "
			SELECT `PostCaption`, `PostUri`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostID`='{$iPostID}'
		";
		return $this->getRow($sSQL);
	}

	function getPostCaptionByUri($sPostUri) {
		$sSQL = "
			SELECT `PostCaption`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostUri`='{$sPostUri}'
		";
		return $this->getOne($sSQL);
	}

	function getAllBlogsCnt($sStatusFilter) {
		$sBlogsSQL = "
			SELECT COUNT(DISTINCT(`{$this->_oConfig->sSQLBlogsTable}`.`ID`)) AS 'Cnt'
			FROM `{$this->_oConfig->sSQLBlogsTable}` 
			INNER JOIN `{$this->_oConfig->sSQLPostsTable}` ON `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID`
			WHERE {$sStatusFilter}
		";
		return $this->getOne($sBlogsSQL);
	}

	function getTopBlogs($sStatusFilter, $sqlLimit) {
		$sBlogsSQL = "
			SELECT `{$this->_oConfig->sSQLBlogsTable}`.`ID`, `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID`, `{$this->_oConfig->sSQLBlogsTable}`.`Description`, COUNT(`{$this->_oConfig->sSQLPostsTable}`.`PostID`) AS 'PostCount'
			FROM `{$this->_oConfig->sSQLBlogsTable}` 
			INNER JOIN `{$this->_oConfig->sSQLPostsTable}` ON `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID`
			WHERE {$sStatusFilter}
			GROUP BY `{$this->_oConfig->sSQLBlogsTable}`.`ID` 
			ORDER BY `PostCount` DESC
			{$sqlLimit}
		";
		$vBlogsRes = db_res($sBlogsSQL);
		return $vBlogsRes;
	}

	function getLastBlogs($sStatusFilter, $sqlLimit) {
		$sBlogsSQL = "
			SELECT `{$this->_oConfig->sSQLBlogsTable}`.`ID`, `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID`, `{$this->_oConfig->sSQLBlogsTable}`.`Description`, COUNT(`{$this->_oConfig->sSQLPostsTable}`.`PostID`) AS 'PostCount'
			FROM `{$this->_oConfig->sSQLBlogsTable}` 
			INNER JOIN `{$this->_oConfig->sSQLPostsTable}` ON `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID` 
			WHERE {$sStatusFilter}
			GROUP BY `{$this->_oConfig->sSQLBlogsTable}`.`ID`
			ORDER BY `PostDate` DESC
			{$sqlLimit}
		";

		$vBlogsRes = db_res( $sBlogsSQL );
		return $vBlogsRes;
	}

	function getTagsInfo($iMemberID, $sStatusFilter, $sCategoryName) {
		$sCategJoin = $sCategFilter = '';
		if ($sCategoryName != '') {
			$sCategJoin = "
				LEFT JOIN `{$this->_oConfig->sSQLCategoriesTable}` ON `{$this->_oConfig->sSQLCategoriesTable}`.`ID` = `{$this->_oConfig->sSQLPostsTable}`.`PostID`
			";
			$sCategFilter = "
				AND `{$this->_oConfig->sSQLCategoriesTable}`.`Category` = '{$sCategoryName}' AND {$this->_oConfig->sSQLCategoriesTable}`.`Type`='bx_blogs'
			";
		}

		$sPostsSQL = "
			SELECT
				`Tags`,`OwnerID`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			{$sCategJoin}
			WHERE
				`{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = {$iMemberID}
				{$sCategFilter}
				{$sStatusFilter}
		";
		$vTags = db_res($sPostsSQL);
		return $vTags;
	}

	function getPostsInCategory($sStatusFilter, $sCategoryName, $iOwnerID) {
		$sCategJoin = $sCategFilter = '';
		if ($sCategoryName != '') {
			$sCategJoin = "
				LEFT JOIN `{$this->_oConfig->sSQLCategoriesTable}` ON `{$this->_oConfig->sSQLCategoriesTable}`.`ID` = `{$this->_oConfig->sSQLPostsTable}`.`PostID`
			";
			$sCategFilter = "
				AND `{$this->_oConfig->sSQLCategoriesTable}`.`Category` = '{$sCategoryName}' AND `{$this->_oConfig->sSQLCategoriesTable}`.`Type`='bx_blogs'
			";
		}
		$sPostsSQL = "
			SELECT
				`PostID`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			{$sCategJoin}
			WHERE
				{$sStatusFilter}
				{$sCategFilter}
				AND `{$this->_oConfig->sSQLPostsTable}`.`OwnerID`='{$iOwnerID}'
			ORDER BY `PostDate` ASC
		";
		$vPostsInCat = db_res($sPostsSQL);

		$aPosts = array();
		while ($aPost = mysql_fetch_assoc($vPostsInCat)) {
			$aPosts[] = (int)$aPost['PostID'];
		}
		return $aPosts;
	}

	function getPostsCntInCategory($sCategoryName, $sStatusFilter, $iOwnerID) {
		$sCategJoin = "
			LEFT JOIN `{$this->_oConfig->sSQLCategoriesTable}` ON `{$this->_oConfig->sSQLCategoriesTable}`.`ID` = `{$this->_oConfig->sSQLPostsTable}`.`PostID`
		";
		$sCategFilter = "
			AND `{$this->_oConfig->sSQLCategoriesTable}`.`Category` = '{$sCategoryName}' AND `{$this->_oConfig->sSQLCategoriesTable}`.`Type`='bx_blogs'
		";

		$sCountPostCatSQL = "
			SELECT COUNT(*)
			FROM `{$this->_oConfig->sSQLPostsTable}`
			{$sCategJoin}
			WHERE 1
			{$sCategFilter}
			AND `{$this->_oConfig->sSQLPostsTable}`.`OwnerID`='{$iOwnerID}'
			{$sStatusFilter}
		";

		$iCountCatPost = (int)$this->getOne($sCountPostCatSQL);
		return $iCountCatPost;
	}

	function getFeaturedPosts($iMemberID) {
		$sFeaturedSQL = "
			SELECT `{$this->_oConfig->sSQLPostsTable}`.*
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = {$iMemberID} AND `{$this->_oConfig->sSQLPostsTable}`.`Featured`='1'
			ORDER BY `PostDate` DESC
		";
		$vFeaturedPosts = db_res($sFeaturedSQL);
		return $vFeaturedPosts;
	}

	function setPostStatus($iPostID, $sStatus = 'disapproval') {
		$sUpdateSQL = "
			UPDATE `{$this->_oConfig->sSQLPostsTable}`
			SET `PostStatus`='{$sStatus}'
			WHERE `PostID`='{$iPostID}'
			LIMIT 1";
		$this->query($sUpdateSQL);
	}

	function getBlogInfo($iMemberID) {
		$sBlogsSQL = "
			SELECT * FROM `{$this->_oConfig->sSQLBlogsTable}` 
			WHERE `{$this->_oConfig->sSQLBlogsTable}`.`OwnerID` = '{$iMemberID}'
			LIMIT 1
		";

		return $this->getRow($sBlogsSQL);
	}

	function getPostOwnerByID($iPostID) {
		$sCheckPostSQL = "
			SELECT `{$this->_oConfig->sSQLPostsTable}`.`OwnerID`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostID`='{$iPostID}'
		";
		$iOwnerID = $this->getOne($sCheckPostSQL);
		return $iOwnerID;
	}

	function getOwnerByBlogID($iBlogID) {
		$sCheckSQL = "
			SELECT `OwnerID`
			FROM `{$this->_oConfig->sSQLBlogsTable}`
			WHERE `ID`='{$iBlogID}'
		";
		$iOwnerID = $this->getOne($sCheckSQL);
		return $iOwnerID;
	}

	function getPostPhotoByID($iPostID) {
		$sPhotosSQL = "SELECT `PostPhoto` FROM `{$this->_oConfig->sSQLPostsTable}` WHERE `PostID` = '{$iPostID}' LIMIT 1";
		$sFileName = $this->getOne($sPhotosSQL);
		return $sFileName;
	}

	function performUpdatePostWithPhoto($iPostID, $sPhotoFilename = '') {
		$sUpdateSQL = "
			UPDATE `{$this->_oConfig->sSQLPostsTable}` SET
			`PostPhoto`='{$sPhotoFilename}'
			WHERE `PostID`='{$iPostID}'
		";

		$vSqlRes = db_res($sUpdateSQL);
		return $vSqlRes;
	}

	function deletePost($iPostID) {
		$sDelSQL = "DELETE FROM `{$this->_oConfig->sSQLPostsTable}` WHERE `{$this->_oConfig->sSQLPostsTable}`.`PostID` = '{$iPostID}' LIMIT 1";
		$vSqlRes = db_res($sDelSQL);
		return $vSqlRes;
	}

	function getPostIDByUri($sPostUri) {
		$sPostIdSQL = "SELECT `PostID` FROM `{$this->_oConfig->sSQLPostsTable}` WHERE `PostUri`='{$sPostUri}'";
		$iPostID = (int)$this->getOne($sPostIdSQL);
		return $iPostID;
	}
	function getPostUriByID($iPostID) {
		$sPostUriSQL = "SELECT `PostUri` FROM `{$this->_oConfig->sSQLPostsTable}` WHERE `PostID`='{$iPostID}'";
		$sPostUri = $this->getOne($sPostUriSQL);
		return $sPostUri;
	}

	function getPostInfo($iPostID) {
		$sAllBlogPostInfoSQL = "
			SELECT `{$this->_oConfig->sSQLPostsTable}`. * , `{$this->_oConfig->sSQLPostsTable}`.`PostCaption`
			FROM `{$this->_oConfig->sSQLPostsTable}` 
			WHERE `{$this->_oConfig->sSQLPostsTable}`.`PostID` = '{$iPostID}'
			LIMIT 1
		";
        // , `{$this->_oConfig->sSQLCategoriesTable}`.`Category` AS 'CategoryName'
		/*LEFT JOIN `{$this->_oConfig->sSQLCategoriesTable}` ON `{$this->_oConfig->sSQLCategoriesTable}`.`ID`=`{$this->_oConfig->sSQLPostsTable}`.`PostID` AND {$this->_oConfig->sSQLCategoriesTable}`.`Type`='bx_blogs'*/

		$aAllBlogPostInfo = $this->getRow($sAllBlogPostInfoSQL);
		return $aAllBlogPostInfo;
	}

	function getJustPostInfo($iPostID) {
		$sBlogPostsSQL = "SELECT * FROM `{$this->_oConfig->sSQLPostsTable}` WHERE `PostID` = '{$iPostID}' LIMIT 1";
		$aBlogPost = $this->getRow($sBlogPostsSQL);
		return $aBlogPost;
	}

	function getFeaturedStatus($iPostID) {
		$sCheckSQL = "
			SELECT `Featured`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostID`='{$iPostID}'
		";
		$iFeatured = $this->getOne($sCheckSQL);
		return $iFeatured;
	}

	function getActiveStatus($iPostID) {
		$sCheckSQL = "
			SELECT `PostStatus`
			FROM `{$this->_oConfig->sSQLPostsTable}`
			WHERE `PostID`='{$iPostID}'
		";
		$sStatus = $this->getOne($sCheckSQL);
		return $sStatus;
	}

	function performUpdateFeatureStatus($aParams) {
		$iPostID = $aParams['postID'];
		$sStatus = $aParams['status'];

		$sUpdateSQL = "
			UPDATE `{$this->_oConfig->sSQLPostsTable}`
			SET
				`Featured`='{$sStatus}'
			WHERE
				`PostID`='{$iPostID}'
		";
		$this->query($sUpdateSQL);
	}

	function performUpdateBlog($aParams) {
		$iBlogID = $aParams['blogID'];
		$sDesc = $aParams['description'];

		$sUpdateSQL = "
			UPDATE `{$this->_oConfig->sSQLBlogsTable}`
			SET
				`Description` = '{$sDesc}'
			WHERE
				`{$this->_oConfig->sSQLBlogsTable}`.`ID` = '{$iBlogID}'
			LIMIT 1
		";
		$this->query($sUpdateSQL);
	}

	function deleteBlog($iBlogID) {
		$sDelSQL = "DELETE FROM `{$this->_oConfig->sSQLBlogsTable}` WHERE `ID` = '{$iBlogID}'";
		$this->query($sDelSQL);
	}

	function getMemberIDByNickname($sNickName) {
		$sCheckSQL = "SELECT `ID` FROM `Profiles` WHERE `NickName`='{$sNickName}'";
		$iMemberID = (int)$this->getOne($sCheckSQL);
		return $iMemberID;
	}

	function getMemberPostsRSS($iPID) {
		$sUnitsSQL = "
				SELECT DISTINCT `{$this->_oConfig->sSQLPostsTable}`.`PostID` AS 'UnitID',
					`{$this->_oConfig->sSQLPostsTable}`.`OwnerID`,
					`{$this->_oConfig->sSQLPostsTable}`.`PostCaption` AS 'UnitTitle',
					`{$this->_oConfig->sSQLPostsTable}`.`PostUri` AS 'UnitUri',
					`{$this->_oConfig->sSQLPostsTable}`.`PostText` AS 'UnitDesc',
					`PostDate` AS 'UnitDateTimeUTS',
					`{$this->_oConfig->sSQLPostsTable}`.`PostPhoto` AS 'UnitIcon'
				FROM `{$this->_oConfig->sSQLPostsTable}`
				WHERE `{$this->_oConfig->sSQLPostsTable}`.`PostStatus` = 'approval'
				AND `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = '{$iPID}'
				ORDER BY `{$this->_oConfig->sSQLPostsTable}`.`PostDate` DESC
				LIMIT 10
		";
		$aRssUnits = $this->getAll($sUnitsSQL);
		return $aRssUnits;
	}

    function getBlogPostsByMonth($iYear, $iMonth, $iNextYear, $iNextMonth, $sStatus = 'approval') {
    	$sStatus = $this -> escape($sStatus);
    	$sExtra = $sStatus
    		? " AND `{$this->_oConfig->sSQLPostsTable}`.`PostStatus` = '{$sStatus}'"
    		: '';

        return $this->getAll ("
			SELECT `{$this->_oConfig->sSQLPostsTable}`.*, DAYOFMONTH(FROM_UNIXTIME(`{$this->_oConfig->sSQLPostsTable}`.`PostDate`)) AS `Day`
            FROM `{$this->_oConfig->sSQLPostsTable}`
            WHERE
				`{$this->_oConfig->sSQLPostsTable}`.`PostDate` >= UNIX_TIMESTAMP('{$iYear}-{$iMonth}-1')
				AND `{$this->_oConfig->sSQLPostsTable}`.`PostDate` < UNIX_TIMESTAMP('{$iNextYear}-{$iNextMonth}-1')
				$sExtra
		");
    }

	function getMemberPostsCnt($iPID) {
		$sUnitsSQL = "
				SELECT COUNT(`{$this->_oConfig->sSQLPostsTable}`.`PostID`)
				FROM `{$this->_oConfig->sSQLPostsTable}`
				WHERE `{$this->_oConfig->sSQLPostsTable}`.`PostStatus` = 'approval'
				AND `{$this->_oConfig->sSQLPostsTable}`.`OwnerID` = '{$iPID}'
		";
		return (int)$this->getOne($sUnitsSQL);
	}

    function getSettingsCategory() {
        return (int)$this->getOne("SELECT `ID` FROM `sys_options_cats` WHERE `name` = 'Blogs' LIMIT 1");
    }
}

?>