<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

/***************************************************************************
 *	PROJECT: ZokiSoft Smart Catalog
 *
 *	This program is a commercial software and any kind of using
 *	it must agree to ZokiSoft License Agreement.
 *
 *	This notice may not be removed from the code.
 *
 *	Copyright 2007-2008 ZokiSoft
 *	http://www.zokisoft.com/
 ***************************************************************************/

class Catalog_model extends Model {


	function Catalog_model()
	{
		// Call the Model constructor
		parent::Model();
	}


/*
*
*	get user data according given nickname
*
*	@param - user nickname
*
*	@return array
*
*/
	function GetUserArrayByName( $sUserName, $bFullArray = false )
	{

		if ($bFullArray)
		{
			$this->db->select('`zok_user`.*,`zok_user_group`.`GroupName`,`zok_user_group`.`Data` as GroupData');
			$this->db->join('`zok_user_group`','`zok_user`.`UserGroup` = `zok_user_group`.`Id`');
		}
		$rRes = $this->db->getwhere( '`zok_user`', array( '`zok_user`.`Name`' => $sUserName ) );		

		if ( 0 >= $rRes->num_rows() )
			return false;

		$aUser = $rRes->row_array();
		
		if ($bFullArray)
			$aUser['GroupData'] = unserialize($aUser['GroupData']);

		$sUserIconFile = $this->config->item('base_dir') . $this->config->item('user_data_path') . $aUser['UserFolder'] . $aUser['Pic'] . '_icon' . $aUser['Ext'];
		$sUserThumbFile = $this->config->item('base_dir') . $this->config->item('user_data_path') . $aUser['UserFolder'] . $aUser['Pic'] . $aUser['Ext'];

		if( $this->ext_file_exists($sUserIconFile) && $this->ext_file_exists( $sUserThumbFile ) )
		{
			$aUser['iconUrl'] = $this->config->item('base_url') . $this->config->item('user_data_url') . $aUser['UserFolder'] . $aUser['Pic'] . '_icon' . $aUser['Ext'];
			$aUser['thumbUrl'] = $this->config->item('base_url') . $this->config->item('user_data_url') . $aUser['UserFolder'] . $aUser['Pic'] . $aUser['Ext'];
		}
		else
		{
			$aUser['iconUrl'] = $this->config->item('base_url') . $this->config->item('design_icon_url') . $this->config->item('user_no_icon');
			$aUser['thumbUrl'] = $this->config->item('base_url') . $this->config->item('design_icon_url') . $this->config->item('user_no_thumb');
		}

		return $aUser;
	}

/*
*
*	get user data according given id
*
*	@param - user id
*
*	@return array
*
*/
	function GetUserArrayById( $iUserId )
	{
		$iUserId = (int)$iUserId;

		$this->db->select('`zok_user`.*');
		$rRes = $this->db->getwhere( '`zok_user`', array( '`zok_user`.`Id`' => $iUserId ) );
		$aUserInfo = $rRes->row_array();

		if (0==count($aUserInfo))
			return array();

		$sUserIconFile = $this->config->item('base_dir') . $this->config->item('user_data_path') . $aUserInfo['UserFolder'] . $aUserInfo['Pic'] . '_icon' . $aUserInfo['Ext'];
		$sUserThumbFile = $this->config->item('base_dir') . $this->config->item('user_data_path') . $aUserInfo['UserFolder'] . $aUserInfo['Pic'] . $aUserInfo['Ext'];

		if( $this->ext_file_exists($sUserIconFile) && $this->ext_file_exists( $sUserThumbFile ) )
		{
			$aUserInfo['iconUrl'] = $this->config->item('base_url') . $this->config->item('user_data_url') . $aUserInfo['UserFolder'] . $aUserInfo['Pic'] . '_icon' . $aUserInfo['Ext'];
			$aUserInfo['thumbUrl'] = $this->config->item('base_url') . $this->config->item('user_data_url') . $aUserInfo['UserFolder'] . $aUserInfo['Pic'] . $aUserInfo['Ext'];
		}
		else
		{
			$aUserInfo['iconUrl'] = $this->config->item('base_url') . $this->config->item('design_icon_url') . $this->config->item('user_no_icon');
			$aUserInfo['thumbUrl'] = $this->config->item('base_url') . $this->config->item('design_icon_url') . $this->config->item('user_no_thumb');
		}

		return $aUserInfo;
	}

/*
*
*	get owner data according given post id
*
*	@param - post id
*
*	@return array
*
*/
	function GetUserArrayByPostId( $iPostId )
	{

		$this->db->select('`UserId`');
		$this->db->where('`Id` = \'' . $iPostId . '\'');
		$rRes = $this->db->get('`zok_post`');
		$aPost = $rRes->row_array();

		return $this->GetUserArrayById($aPost['UserId']);

	}


/*
*
*	get user stat by user id
*
*	@param - user id
*
*	@return array
*
*/
	function GetUserStatById( $iUserId )
	{

		$aStat = array();

// mail statistic
		$this->db->select('`zok_user_mailbox`.`Status`, COUNT(*) as Num');
		$this->db->where('`zok_user_mailbox`.`UserId` = \'' . $iUserId . '\' AND `zok_user_mailbox`.`RecipientId` = \'' . $iUserId . '\'');
		$this->db->groupby('`zok_user_mailbox`.`Status`');
		$rRes = $this->db->get('`zok_user_mailbox`');

		foreach( $rRes->result_array() as $aItem )
			$aStat['Mail']['Inbox'][$aItem['Status']] = $aItem['Num'];


		$this->db->select('`zok_user_mailbox`.`Status`, COUNT(*) as Num');
		$this->db->where('`zok_user_mailbox`.`UserId` = \'' . $iUserId . '\' AND `zok_user_mailbox`.`SenderId` = \'' . $iUserId . '\'');
		$this->db->groupby('`zok_user_mailbox`.`Status`');
		$rRes = $this->db->get('`zok_user_mailbox`');

		foreach( $rRes->result_array() as $aItem )
			$aStat['Mail']['Outbox'][$aItem['Status']] = $aItem['Num'];

// post statistic
		$this->db->select('`zok_post`.`Status`, COUNT(*) as Num');
		$this->db->where('`zok_post`.`UserId` = \'' . $iUserId . '\'');
		$this->db->groupby('`zok_post`.`Status`');
		$rRes = $this->db->get('`zok_post`');

		foreach( $rRes->result_array() as $aItem )
			$aStat['Post'][$aItem['Status']] = $aItem['Num'];

// comment statistic
		$this->db->select('`zok_comment`.`Status`, COUNT(*) as Num');
		$this->db->where('`zok_comment`.`UserId` = \'' . $iUserId . '\'');
		$this->db->groupby('`zok_comment`.`Status`');
		$rRes = $this->db->get('`zok_comment`');

		foreach( $rRes->result_array() as $aItem )
			$aStat['Comment'][$aItem['Status']] = $aItem['Num'];


		return $aStat;

	}

	
	function GetUserPostNumById( $iUserId )
	{
	
		$this->db->select('COUNT(Id) as Num');
		$this->db->where('`UserId` = \''.$iUserId.'\'');
		$rRes = $this->db->get('`zok_post`');

		$aItem = $rRes->row_array();

		return (!empty($aItem['Num']) ? $aItem['Num'] : 0);
	
	}
	

/*
*
*	Recursively generates hierarchyly sorted array
*
*	@param - ansorted array with fields Id and ParentId
*	@param - auto
*	@param - auto
*	@param - auto
*
*	@return array
*
*/
	function GetHierarchyArray($aItemArray, $sLevelSeparator = '', $iPid=0, $iCurPos=0, $iLevel=0 )
	{

// NOTE: the main idea is that in aItemArray no child can be placed before his parent. Othervise this function will not find it.
// usually, simple sorting by Id helps :-)

		$aResult = array();
// in some cases $iCurPos everytime should be equal to 0 :-)
		$iItemNum = count($aItemArray);
		for ($i = $iCurPos; $i < $iItemNum; $i++)
		{
// don't show items with status 'deleted' and all it childs
			if ( $aItemArray[$i]['ParentId'] == $iPid
				&& $aItemArray[$i]['Status'] != 'deleted' )
			{
				$aItemArray[$i]['Sublevel'] = $iLevel;
//				$aResult[] = $aItemArray[$i];
//				$iIndex = count($aResult)-1;
//				$aResult[$iIndex]['Sublevel'] = $iLevel;
				if ( !empty($sLevelSeparator) )
					$aItemArray[$i]['Title'] = str_repeat( $sLevelSeparator, $iLevel) . @$aItemArray[$i]['Title'];
					
				$aResult[] = $aItemArray[$i];
				
				$aResult = array_merge($aResult, $this -> GetHierarchyArray($aItemArray,  $sLevelSeparator, $aItemArray[$i]['Id'], $i, $iLevel+1));
			}
		}
		return $aResult;
	}



	function GetHierarchyArray2( $aItems, $iParentId=0, $iLevel=0 )
	{

		$aResult = array();

		foreach( $aItems as $aItem )
		{
			if ( $aItem['ParentId'] == $iParentId
				&& $aItem['Status'] != 'deleted' )
			{
				$aItem['Sublevel'] = $iLevel;
				$aResult[] = $aItem;
				$aResult = array_merge($aResult, $this->GetHierarchyArray2($aItems, $aItem['Id'], ($iLevel+1)));
			}
		}

		return $aResult;
	}

/*
*
*	Generates BaseName by given name
*
*	@param - name
*	@param - db table name
*	@param - id
*	@return string
*
*/
	function GetUniqBaseName($sName, $sDbTableName, $iId='')
	{

// generate unique basename for category

//		$search = array('/[^\w\-]+/');
		// allow only latin letters
		$search = array('/[^A-Za-z0-9\-]+/');
		$replace = array('-');
		$sBaseName = preg_replace( $search, $replace, strtolower(substr($sName, 0, 42)));
		$this->db->select('`BaseName`');
		$this->db->where('`BaseName`=\'' . $sBaseName . '\'');
		if ( !empty($iId) )
			$this->db->where('`Id`!=\'' . $iId . '\'');
		$rRes = $this->db->get($sDbTableName);

		if ( 0 < $rRes->num_rows() )
		{
			$this->db->select('`BaseName`');
			$this->db->where('`BaseName` LIKE \'' . $sBaseName . '%\'');
			$rRes = $this->db->get($sDbTableName);

			foreach ( $rRes->result_array() as $aValue )
				$aVariant[] = $aValue['BaseName'];

			$iIndex = 0;
			do {
					$iIndex++;
					$sProbe = $sBaseName . '-' . $iIndex;

					if ( !in_array($sProbe, $aVariant) )
					{
						$sBaseName = $sProbe;
						break;
					}

			} while (1);

		}

		return $sBaseName;

	}


/*
*
*	Generates BreadCramb for category by given Id
*
*	@param - category id
*
*	@return string
*
*/
   function GetNumericBreadCramb($iCategoryId)
	{

		$sTableName = 'zok_category';
		$sBreadCramb = '';
		$aBreadCramb = array();

		$this->load->database();

		$this->db->select('ParentId');
		$rRes = $this->db->getwhere( $sTableName, array( 'Id' => $iCategoryId ) );

// get breadcramb from parent

		if ( 0 == $rRes->num_rows() )
			return false;

		$aParentId = $rRes->row_array();

		$this->db->select('`BreadCramb`');
		$this->db->where('`Id`=\''.$aParentId['ParentId'].'\'');
		$rRes = $this->db->get($sTableName);
		if ( 0 < $rRes->num_rows() )
		{
			$aCategory = $rRes->row_array();
			$sBreadCramb = $aCategory['BreadCramb'] . $iCategoryId . '/';
		}
		else
			$sBreadCramb = '0/' . $iCategoryId . '/';


// culculate bread cramb
/*
		if ( 0 == $rRes->num_rows() )
			return ('0/'.$iCategoryId.'/');

		$aParentId = $rRes->row_array();
		$aBreadCramb[] = $aParentId['ParentId'];

		while( 0 != $aParentId['ParentId'] )
		{
			$this->db->select('ParentId');
			$rRes = $this->db->getwhere( $sTableName, array( 'Id' => $aParentId['ParentId'] ) );
			$aParentId = $rRes->row_array();
			$aBreadCramb[] = $aParentId['ParentId'];
		}

		$sBreadCramb = implode('/',array_reverse($aBreadCramb)) . '/';

		$sBreadCramb .= $iCategoryId . '/';
*/
		return $sBreadCramb;

	}



/*
*
*	Generates UserFriendly BreadCramb for category by given Numeric BreadCramb
*
*	@param - numeric breadcramb
*
*	@return array
*
*/
	function GetNamedBreadCramb($sBreadCramb, $sLang)
	{

		$sTableName = 'zok_category';

		if ( !$sBreadCramb )
			return array();

		$sBreadCramb = str_replace('/', ',', $sBreadCramb );
		$sBreadCramb=substr( $sBreadCramb, 0, -1 );

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		$this->db->select('`zok_category`.`Id`,`zok_category`.`BaseName`,`zok_category_lang`.`Title`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$this->db->where( '`zok_category`.`Id` IN ('.$sBreadCramb.')');
		$this->db->orderby( 'CategoryId', 'asc' );
		$rRes = $this->db->get($sTableName);

		$aBreadCramb = array();

		foreach ( $rRes->result_array() as $aCategory )
		{
			if (empty($aCategory['Title']))
				$aCategory['Title'] = $aCategory['BaseName'];
			$aBreadCramb[$aCategory['Id']] = array( 'Title' => $aCategory['Title'], 'BaseName' => $aCategory['BaseName'] );
		}

		return $aBreadCramb;

	}

/*
*	Get localized category by id and specified lang
*/
	function getLocCategory($iCatId,$sLang='')
	{

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$this->db->where('`zok_category`.`Id`=\''.$iCatId.'\'');
		$rRes = $this->db->get('`zok_category`');

		$aCat = $rRes->row_array();

		if (empty($aCat['Title']) && @$aCat['BaseName'])
			$aCat['Title']=$aCat['BaseName'];

		return $aCat;
	}

    /**
	 *	@desc		-	get categories list with given parent id
	 * 					get subcategories list for each category if so
	 * 					get icons for high level categories
	 *	@access		-	public
	 * 	@param		-	integer 	parent id
	 *
	 * 	@return		-	array
	 *
	 */
	function getCategoriesList($iParentId=0, $sLang='', $sWString='')
	{

		$iParentId = (int)$iParentId;

		// select categories
		if (empty($sWString))
			$sWhereStatus = $this->config->item('premoderation') ? '`zok_category`.`Status`=\'active\'' : '`zok_category`.`Status`!=\'deleted\'';
		else
			$sWhereStatus = $sWString;

		$aCategoryList = $this->getSubCategoryList($iParentId,$sWhereStatus,$sLang);

		// select subcategories
		if (0<count($aCategoryList))
		{
			$sId = '';
			foreach ( $aCategoryList as $aCategory )
				$sId .= $aCategory['Id'] . ',';

			$sId=substr($sId,0,-1);

			if (!empty($sLang))
				$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
			else
				$sLangCond = '';

			$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
			$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
			$this->db->where('`zok_category`.`ParentId` IN ('.$sId.') AND '.$sWhereStatus);
			$this->db->orderby('`zok_category`.`ParentId`', 'asc');
			$rRes = $this->db->get('`zok_category`');
			$aSubCategoryList = array();

			foreach ( $rRes->result_array() as $aCategory )
			{
				if (empty($aCategory['Title']) && @$aCategory['BaseName'])
					$aCategory['Title'] = $aCategory['BaseName'];

				$aCategoryList[$aCategory['ParentId']]['subcategories'][] = $aCategory;
			}
		}

		return $aCategoryList;

/*
		$sSelect = '
			`Id`,
			`Title`,
			`ParentId`,
			`BaseName`,
			`Status`,
			`Pic`,
			`Ext`
		';
		$sWhereStatus = $this->config->item('premoderation') ? '`Status` = \'active\'' : '`Status` != \'deleted\'';

		$this->db->select($sSelect);
		$this->db->order_by('`ParentId`', 'asc');
		$this->db->order_by('`CategoryOrder`', 'asc');
		$this->db->where($sWhereStatus);
		$oRes = $this->db->get('`zok_category`');
		$aCategories = $this->GetHierarchyArray($oRes->result_array(), '', $iParentId);

		$aResult = array();

		foreach( $aCategories as $aCategory )
		{
			$aResult[$aCategory['Id']] = $aCategory;
			$aResult[$aCategory['Id']]['subcategories'] = array();
		}

		foreach( $aResult as $iKey => $aCategory )
		{
			$aResult[$iKey]['IconUrl'] = '';
			if( $aCategory['Sublevel'] == 0 )
			{
				$sIconFile = $this->config->item('base_dir') . $this->config->item('category_pic_path'). $aCategory['Id'] . '/' . $aCategory['Pic'] . $aCategory['Ext'];
				if( $this->ext_file_exists($sIconFile) )
				{
					$aResult[$iKey]['IconUrl'] = $this->config->item('base_url') . $this->config->item('category_pic_url') . $aCategory['Id'] . '/' . $aCategory['Pic'] . $aCategory['Ext'];
				}
			}
			elseif( $aCategory['Sublevel'] == 1 )
			{
				$aResult[$aCategory['ParentId']]['subcategories'][] = $aCategory;
				unset($aResult[$iKey]);
			}
			else
			{
				unset($aResult[$iKey]);
			}
		}
		//print_r($aResult);

		return $aResult;
*/

	}

	/**
	 *	get list of subcategories with icons
	 *
	 *	@param	- integer	id of category
	 *	@param	- string	additional condition
	 *	@param	- string	category table name
	 *
	 * 	@return	- array
	 *
	 */
	function getSubCategoryList($iParentCategory=0, $aAddCond='', $sLang='')
	{

		$sTableName='`zok_category`';

		if ( !empty($aAddCond) )
			$sCondition = ' AND ' . $aAddCond;
		else
			$sCondition = '1' == $this->config->item('premoderation') ? ' AND `Status` = \'active\' ' : ' AND `Status` != \'deleted\' ';

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$this->db->where('`zok_category`.`ParentId` = \''.$iParentCategory.'\'' . $sCondition);
		$this->db->orderby('`CategoryOrder`', 'asc');
		$rRes = $this->db->get('`zok_category`');
		$aCatList = $rRes->result_array();

		$aResList = array();
		foreach($aCatList as $iKey => $aCateg)
		{

			if (empty($aCateg['Title']) && @$aCateg['BaseName'])
				$aCateg['Title'] = $aCateg['BaseName'];

			$aResList[$aCateg['Id']] = $aCateg;

			if( $this->ext_file_exists($this->config->item('base_dir').$this->config->item('category_pic_path').$aCateg['Id'].'/'.$aCateg['Pic'].$aCateg['Ext']) )
				$aResList[$aCateg['Id']]['IconUrl'] = base_url() . $this->config->item('category_pic_url') . $aCateg['Id'] . '/' . $aCateg['Pic'] . $aCateg['Ext'];
			else
				$aResList[$aCateg['Id']]['IconUrl'] = '';
		}

		return $aResList;
	}


	function GetCategoryTree($sLang='', $divider = '::')
	{
		$sTableName='`zok_category`';

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$rRes = $this->db->orderby('`zok_category`.`Id`');
		$rRes = $this->db->get('`zok_category`');

		$aCatList = $rRes->result_array();

		foreach ( $aCatList as $key => $aItem )
		{
			if (empty($aItem['Title']))
				$aCatList[$key]['Title'] = $aCatList[$key]['BaseName'];
		}


		$aSortedCatList = $this->GetHierarchyArray($aCatList, $divider);
		
		// add root category
		//array_unshift($aSortedCatList,array('Id'=>0,'Title'=>'-- add as primary --'));
		
		return $aSortedCatList;
		
	}


	function GetCategoryTreeForCategory($sLang='', $divider = '::')
	{
		$sTableName='`zok_category`';

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		// get allowed categories
		$aGrValue = Catalog_Controller::_grGetValue('categ_to_categ');

		if ( !is_array($aGrValue) || 0 == count($aGrValue) )
			return array();

		foreach ( $aGrValue as $value )
			if ( 0 == $value )
				$this->db->orwhere('`zok_category`.`BreadCramb` LIKE "'.(int)$value.'/%"');		
			else
				$this->db->orwhere('`zok_category`.`BreadCramb` LIKE "%/'.(int)$value.'/%"');		
		
		$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$rRes = $this->db->orderby('`zok_category`.`Id`');
		$rRes = $this->db->get('`zok_category`');

		$aCatList = $rRes->result_array();

		foreach ( $aCatList as $key => $aItem )
		{
			if (empty($aItem['Title']))
				$aCatList[$key]['Title'] = $aCatList[$key]['BaseName'];
		}

		// gethierarhyarray function need one root(common) category to build hierarhy list
		// here can be categories from different parents, withoun common root, so we build array
		// using all categories and then pick up requested
		$this->db->select('`zok_category`.*');
		$rRes = $this->db->orderby('`zok_category`.`Id`');
		$rRes = $this->db->get('`zok_category`');		
		
		$aAllCatList = $rRes->result_array();		

		$aSortedAllCatList = $this->GetHierarchyArray($aAllCatList, $divider);

		$aSortedCatList = array();
		foreach ($aSortedAllCatList as $key => $aItem)
			foreach ( $aCatList as $key2 => $aCat )
				if ( $aItem['Id'] == $aCat['Id'] )
				{
					$aCat['Title'] = $aItem['Title'] . $aCat['Title'];
					$aSortedCatList[] = $aCat;
				}

		// add root category
		if ( in_array('0',$aGrValue) )
			array_unshift($aSortedCatList,array('Id'=>0,'Title'=>'-- add as primary --'));
		
		return $aSortedCatList;
		
	}


	function GetCategoryTreeForPost($sLang='', $divider = '::')
	{
	
		$sTableName='`zok_category`';

		if (!empty($sLang))
			$sLangCond = ' AND `zok_category_lang`.`Lang`=\''.$sLang.'\'';
		else
			$sLangCond = '';

		// get allowed categories
		$aGrValue = Catalog_Controller::_grGetValue('categ_to_post');

		if ( !is_array($aGrValue) || 0 == count($aGrValue) )
			return array();

		foreach ( $aGrValue as $value )
			if ( 0 == $value )
				$this->db->orwhere('`zok_category`.`BreadCramb` LIKE "'.(int)$value.'/%"');		
			else
				$this->db->orwhere('`zok_category`.`BreadCramb` LIKE "%/'.(int)$value.'/%"');		
		
		$this->db->select('`zok_category`.*,`zok_category_lang`.`Title`,`zok_category_lang`.`Description`,`zok_category_lang`.`Keywords`');
		$this->db->join('`zok_category_lang`',' `zok_category`.`Id`=`zok_category_lang`.`CategoryId`'.$sLangCond,'left');
		$rRes = $this->db->orderby('`zok_category`.`Id`');
		$rRes = $this->db->get('`zok_category`');

		$aCatList = $rRes->result_array();

		foreach ( $aCatList as $key => $aItem )
		{
			if (empty($aItem['Title']))
				$aCatList[$key]['Title'] = $aCatList[$key]['BaseName'];
		}

		// gethierarhyarray function need one root(common) category to build hierarhy list
		// here can be categories from different parents, withoun common root, so we build array
		// using all categories and then pick up requested
		$this->db->select('`zok_category`.*');
		$rRes = $this->db->orderby('`zok_category`.`Id`');
		$rRes = $this->db->get('`zok_category`');		
		
		$aAllCatList = $rRes->result_array();		

		$aSortedAllCatList = $this->GetHierarchyArray($aAllCatList, $divider);

		$aSortedCatList = array();
		foreach ($aSortedAllCatList as $key => $aItem)
			foreach ( $aCatList as $key2 => $aCat )
				if ( $aItem['Id'] == $aCat['Id'] )
				{
					$aCat['Title'] = $aItem['Title'] . $aCat['Title'];
					$aSortedCatList[] = $aCat;
				}

		// add root category
		/*
		if ( in_array('0',$aGrValue) )
			array_unshift($aSortedCatList,array('Id'=>0,'Title'=>'-- add as primary --'));
		*/
		
		return $aSortedCatList;
		
	}
	


/*
*
*	Get post list
*
*	@param - posts type to get
*	@param - limit
*	@param - offset
*	@param - category id( or basename) that will be considered as root.
*	@param - enable/disable browse mode
*	@param - adds SQL_CALC_FOUND_ROWS to select for have ability count number of records
*
*	@return string
*
*/
	function GetPostList( $sType='', $iLimit = '', $iOffset= '', $iParentCategory = '0', $bBrowseMode = TRUE, $bCount = FALSE, $sLang='')
	{

		if ( empty($sLang) )
			$sLang = $this->aSystemVar['cur_lang'];

// select root category basename
		if ( !empty($iParentCategory) )
		{

			$this->db->select('`zok_category`.`BreadCramb`, `zok_category`.`Id`');

			if ( is_numeric($iParentCategory) )
				$this->db->where('`zok_category`.`Id` = \'' . $iParentCategory . '\'');
			else
				$this->db->where('`zok_category`.`BaseName` = \'' . $iParentCategory . '\'');

			$rRes = $this->db->get('`zok_category`');

			$aRootCategory = $rRes->row_array();

		}


		$sDate = date("Y-m-d");

		$this->db->from('`zok_post`');
		$this->db->join('`zok_post_pic`', '`zok_post`.`PrimaryPicId` = `zok_post_pic`.`Id`', 'left');
		// Note: this is simple join, so, if there are no post translation exist - post will not be selected !!
		$this->db->join('`zok_post_lang`', '`zok_post`.`Id` = `zok_post_lang`.`PostId` AND `zok_post_lang`.`Lang`=\''.$sLang.'\'');
		$this->db->join('`zok_post_stat`', '`zok_post`.`Id` = `zok_post_stat`.`PostId`', 'left');

// construct conditions for sql depending on sType value:
		switch( $sType )
		{
			case 'news':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`', false);
				$this->db->where('`zok_post`.`SubType` = \'news\'');
				$this->db->orderby('`zok_post`.`DateEdit` DESC');
			break;
			case 'featured':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc, `zok_category_lang`.`Title` as CategoryTitle, `zok_category`.`Id` as CategoryId, `zok_category`.`BaseName` as CategoryBaseName, `zok_user`.`Name` as UserName, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`', false);
				$this->db->join('`zok_user`', '`zok_post`.`UserId` = `zok_user`.`Id`');
				$this->db->join('`zok_category`', '`zok_post`.`CategoryId` = `zok_category`.`Id`');
				$this->db->join('`zok_category_lang`', '`zok_post`.`CategoryId` = `zok_category_lang`.`CategoryId` AND `zok_category_lang`.`Lang`=\''.$sLang.'\'','left');
				$this->db->where('`zok_post`.`SubType` = \'featured\'');
				$this->db->orderby('RAND()');
			break;
			case 'recent':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc,`zok_category_lang`.`Title` as CategoryTitle, `zok_category`.`Id` as CategoryId, `zok_category`.`BaseName` as CategoryBaseName, `zok_user`.`Name` as UserName, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`', false);
				$this->db->join('`zok_user`', '`zok_post`.`UserId` = `zok_user`.`Id`');
				$this->db->join('`zok_category`', '`zok_post`.`CategoryId` = `zok_category`.`Id`');
				$this->db->join('`zok_category_lang`', '`zok_post`.`CategoryId` = `zok_category_lang`.`CategoryId` AND `zok_category_lang`.`Lang`=\''.$sLang.'\'','left');
				$this->db->where('`zok_post`.`SubType` != \'featured\' AND `zok_post`.`SubType` != \'news\'');
				$this->db->orderby('`zok_post`.`DateEdit` DESC');
			break;
			case 'top':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc,`zok_category_lang`.`Title` as CategoryTitle, `zok_category`.`Id` as CategoryId, `zok_category`.`BaseName` as CategoryBaseName, `zok_user`.`Name` as UserName, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`, ((`zok_post_stat`.`View`+100*`zok_post_stat`.`Rate`)-(DATEDIFF(NOW(),DateEdit)*10)) as Rank', false);
				$this->db->join('`zok_user`', '`zok_post`.`UserId` = `zok_user`.`Id`');
				$this->db->join('`zok_category`', '`zok_post`.`CategoryId` = `zok_category`.`Id`');
				$this->db->join('`zok_category_lang`', '`zok_post`.`CategoryId` = `zok_category_lang`.`CategoryId` AND `zok_category_lang`.`Lang`=\''.$sLang.'\'','left');
				$this->db->where('`zok_post`.`SubType` != \'featured\' AND `zok_post`.`SubType` != \'news\'');
				$this->db->orderby('Rank DESC');
			break;
			case 'random':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc,`zok_category_lang`.`Title` as CategoryTitle, `zok_category`.`Id` as CategoryId, `zok_category`.`BaseName` as CategoryBaseName, `zok_user`.`Name` as UserName, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`', false);
				$this->db->join('`zok_user`', '`zok_post`.`UserId` = `zok_user`.`Id`');
				$this->db->join('`zok_category`', '`zok_post`.`CategoryId` = `zok_category`.`Id`');
				$this->db->join('`zok_category_lang`', '`zok_post`.`CategoryId` = `zok_category_lang`.`CategoryId` AND `zok_category_lang`.`Lang`=\''.$sLang.'\'','left');
				$this->db->where('`zok_post`.`SubType` != \'featured\' AND `zok_post`.`SubType` != \'news\'');
				$this->db->orderby('RAND()');
			break;
			case 'normal':
				$this->db->select(($bCount ? 'SQL_CALC_FOUND_ROWS ' : '') . '`zok_post`.*, `zok_post_lang`.`Title`, `zok_post_lang`.`Body`, `zok_post_lang`.`Keywords`, SUBSTR(`zok_post_lang`.`Body`, 1, 500) as Snipet, `zok_post_pic`.`Pic`, `zok_post_pic`.`Ext`, `zok_post_pic`.`Description` AS picDesc, `zok_category_lang`.`Title` as CategoryTitle, `zok_category`.`Id` as CategoryId, `zok_category`.`BaseName` as CategoryBaseName, `zok_user`.`Name` as UserName, `zok_post_stat`.`View`, `zok_post_stat`.`LastView`, `zok_post_stat`.`Rate`, `zok_post_stat`.`LastRate`', false);
				$this->db->join('`zok_user`', '`zok_post`.`UserId` = `zok_user`.`Id`');
				$this->db->join('`zok_category`', '`zok_post`.`CategoryId` = `zok_category`.`Id`');
				$this->db->join('`zok_category_lang`', '`zok_post`.`CategoryId` = `zok_category_lang`.`CategoryId` AND `zok_category_lang`.`Lang`=\''.$sLang.'\'','left');
// this order allows 'featured'posts to be before 'regular' ones
				$this->db->orderby('`zok_post`.`SubType` DESC');
				$this->db->orderby('`zok_post`.`Id` DESC');
			break;
			default:
			break;
		}

// date condition
		$this->db->where('`zok_post`.`DateStart` <= \''.$sDate.'\' AND (`zok_post`.`DateEnd` >= \''.$sDate.'\' OR `zok_post`.`DateEnd` = \'0000-00-00\' )');

// pre od post moderation
		if ( '1' == $this->config->item('premoderation') )
			$this->db->where('`zok_post`.`Status` = \'active\'');
		else
			$this->db->where('`zok_post`.`Status` != \'deleted\'');

// localization
/*
		if ( !empty($this->aSystemVar['localization']) )
			$this->db->where('( `zok_post`.`Country` = \'' . $this->aSystemVar['localization'] . '\' OR `zok_post`.`Country` = \'all\')');
*/
// set root category
		if ( !empty($aRootCategory['BreadCramb']) )
			if ( true == $bBrowseMode )
			{
				$this->db->where('`zok_category`.`BreadCramb` LIKE \'' . $aRootCategory['BreadCramb'] . '%\'' );
				$this->db->orderby('`zok_category`.`BreadCramb` ASC');
			}
			else
				$this->db->where('`CategoryId` = \'' . $aRootCategory['Id'] . '\'');

// set limit and offset
		if ( !empty($iLimit) )
			if ( !empty($iOffset) )
				$this->db->limit((int)$iLimit, (int)$iOffset);
			else
				$this->db->limit((int)$iLimit);


		$rRes = $this->db->get();

		return $rRes->result_array();

	}



/*
*
*	Get User list
*
*	@param - sort type
*	@param - limit
*
*	@return string
*
*/
	function GetUserList( $sSort = FALSE, $iLimit = FALSE )
	{


		$this->db->select('`zok_user`.`Name`, `zok_user`.`UserFolder`, `zok_user`.`Pic`, `zok_user`.`Ext`');
		$sCondition = '1' == $this->config->item('premoderation') ? '`zok_user`.`Status` = \'active\'' : '`zok_user`.`Status` != \'deleted\' AND `zok_user`.`Status` != \'unconfirmed\'';
		$this->db->where($sCondition);

// sort array by ..
		if ( $sSort )
		{
			switch ( $sSort )
			{
				case 'name':
					$this->db->orderby('`zok_user`.`Name`', 'ASC');
					break;
				case 'post':
					$this->db->select('COUNT(`zok_post`.`Id`) AS PostNum');
					$this->db->join('`zok_post`', '`zok_user`.`Id` = `zok_post`.`UserId`', 'left');
					$this->db->groupby('`zok_user`.`Name`');
					$this->db->orderby('PostNum', 'DESC');
					break;
				default:
					break;

			}
		}

		if ( $iLimit )
			$this->db->limit((int)$iLimit);

		$rRes = $this->db->get('zok_user');

		$aUserData = array();
		$sUserDataUrl = $this->config->item('user_data_url');

		foreach ( $rRes->result_array() as $aUser )
		{
			if ( !empty($aUser['Pic']) )
				$aUser['PicUrl'] = base_url() . $sUserDataUrl . $aUser['UserFolder'] . $aUser['Pic'] . '_icon' . $aUser['Ext'];
			else
				$aUser['PicUrl'] = base_url() . $this->config->item('design_pic_url') . $this->config->item('user_no_icon');

			$aUserData[] = $aUser;
		}

		return $rRes->result_array();


	}


	function GetCustomFields($iCategoryId=0,$iPostId='',$sLang='')
	{

		if ( !empty($iCategoryId) )
		{
			// get id of all categories up to requested one
			$this->db->where('`Id` = \''.(int)$iCategoryId.'\'');
			$rRes = $this->db->get('`zok_category`');
			$aCCategory = $rRes->row_array();
				
			$sId = str_replace('/',',',$aCCategory['BreadCramb']);
			$sId = substr($sId,0,-1);
			
			$this->db->where('`zok_post_cfield`.`CategoryId` IN ('.$sId.')');
		}
		else
			$this->db->where('`zok_post_cfield`.`CategoryId`=0');
			


		if ( empty($sLang) )
			$sLang = $this->aSystemVar['cur_lang'];

		$this->db->select('`zok_post_cfield`.*,`zok_post_cdata`.`Title`,`zok_post_cdata`.`Values`'.(!empty($iPostId)?',`zok_post_clang`.`Value`':',\'\' as Value'));
		$this->db->join('`zok_post_cdata`','`zok_post_cdata`.`FieldId`=`zok_post_cfield`.`Id` AND `zok_post_cdata`.`Lang`=\''.$sLang.'\'');
		if ( !empty($iPostId) )
			$this->db->join('`zok_post_clang`','`zok_post_clang`.`FieldId`=`zok_post_cfield`.`Id` AND `zok_post_clang`.`Lang`=\''.$sLang.'\' AND `zok_post_clang`.`PostId`=\''.$iPostId.'\'','left');
		$this->db->where('`zok_post_cfield`.`Display`=1');
		if ( !$this->bAdmin )
			$this->db->where('`zok_post_cfield`.`Mode`!=\'adm\'');
		$this->db->orderby('`FieldOrder`');
		$rRes = $this->db->get('`zok_post_cfield`');

		return $rRes->result_array();;

	}


/*
*
*	Generates random text
*
*	@param - length of text
*
*	@return string
*
*/
	function GetRandomText( $iLength = '4' )
	{


		$aChar = array('A','B','C','D','E','F','G','H','J','K','M','N','P','R','S','T','U','V','W','X','Z','2','3','4','5','6','7','8','9');
		$iNumChar = count($aChar);

		$sText = '';
		for ($i = 0; $i < $iLength; $i++)
			$sText .= $aChar[rand(0, $iNumChar - 1)];

		return $sText;

	}


/*
*
*	get array with common variables for email templates
*
*	@return - array
*
*/
	function GetMailVar()
	{

		$aCommonVar = array(
							'site_title'	=> $this->config->item('site_title'),
							'site_url'		=> base_url()
							);

		return $aCommonVar;

	}

/*
*
*	Send mail
*
*	@param - template name or id
*	@param - sender email ( if empty - will be used site email from config file )
*	@param - recipient email
*	@param - array with specific template variables
*
*/
	function SendTMail( $sTemplate, $sSender = '', $sRecipient, $aAddVar = array(), $sTableName = '`zok_email_template`', $aCustomTemplate = array() )
	{

		$this->db->select('*');
		$this->db->where( $sTableName . '.`Name` = \'' . $sTemplate . '\' OR ' . $sTableName . '.`Id` = \'' . $sTemplate . '\'');
		$rRes = $this->db->get( $sTableName );

		if ( 0 == $rRes->num_rows() )
			return false;

		$aTemplate = $rRes->row_array();

		if ( !empty($aCustomTemplate['Body']) )
			$aTemplate['Body'] = $aCustomTemplate['Body'];

		preg_match_all( '/__(.+?)__/', $aTemplate['Body'], $aVar );

		$aCommonVar = $this->GetMailVar();
		$aSearch = array();
		$aReplace = array();
		foreach ( $aVar[1] as $sKey )
		{

			$aSearch[] = '/__' . $sKey . '__/';

			if ( !empty($aCommonVar[$sKey]) )
				$aReplace[] = $aCommonVar[$sKey];
			else if ( !empty($aAddVar[$sKey]) )
				$aReplace[] = $aAddVar[$sKey];
			else
				$aReplace[] = '';
		}

		$aTemplate['Body'] = preg_replace( $aSearch, $aReplace, $aTemplate['Body']);

		$this->load->library('email');

		$this->email->initialize(array('charset'=>$this->config->item('catalog_charset')));

		if ( empty($sSender) )
			$this->email->from( $this->config->item('mail_reply'), $this->config->item('site_title') );
		else
			$this->email->from( $sSender );

		$this->email->to( $sRecipient );
		$this->email->subject( $aTemplate['Subject'] );
		$this->email->message( $aTemplate['Body'] );

		return $this->email->send();

	}

/*
*
*	extended check if file exists
*
*	@param - file name with full path to it
*
*	@return bool
*
*/
	function ext_file_exists( $sFile )
	{
		if( file_exists($sFile) && is_file($sFile) )
		{
			clearstatcache();
			return true;
		}
		else
		{
			return false;
		}
	}


/*
*
*	extended check if directory exists
*
*	@param - directory name with full path to it.
*
*	@return bool
*
*/
	function ext_dir_exists( $sDir )
	{
		if( is_dir($sDir) )
		{
			clearstatcache();
			return true;
		}
		else
		{
			return false;
		}
	}

/*
*
*	create specified path
*
*	@param - path
*	@param - base dir
*
*/
	function createFolder($sPath, $sBase='', $bSafe = false )
	{

// strip slashes from the end
		$sPath = rtrim($sPath," \\/");
		$sBase = rtrim($sBase," \\/");

		$aPath = explode('/',$sPath);
		$sPath = $sBase;

		foreach ( $aPath as $folder )
		{
			$sPath .= '/' . $folder;
//			if ( !file_exists($sPath) )
			if ( !is_dir($sPath) )
			{
				mkdir($sPath);
				chmod($sPath, 0777);

				if($bSafe)
				{
					$sFileText = 'Options -Indexes' . "\n";
					$sFileText .= '<FilesMatch "^.*\.php|.*\.html$">' . "\n";
					$sFileText .= 'ForceType application/x-httpd-php-source' . "\n";
					$sFileText .= '</FilesMatch>' . "\n";

					$sIndex = '<html>
						<head>
						<title>403 Forbidden</title>
						</head>
						<body bgcolor="#ffffff">
						<p>Directory access is forbidden.<p>
						</body>
						</html>';

					$this->load->helper('file');
					write_file( $sPath . '/.htaccess', $sFileText, 'w+');
					write_file( $sPath . '/index.html', $sIndex, 'w+' );
				}

			}
		}

		return true;
	}

}

/* End of file Catalog_model.php */
/* Location: ./system/application/libraries/Catalog_model.php */