<?php
/*
File Name: commonpages_model.php. Model File
Description: The file contains common pages related functions.
*/

require_once './lib/simplemvcwf/model_base.php';
require_once './utility/guid_utility.php';

// Encapsulates all common pages database table access. This method of encapsulation uses the singleton design pattern.
class SubsectionsModel extends ModelBase {
    private static $subsectionsModel = NULL;
    
    private function __construct() {
    }
    
    // The create function is necessary to ensure there is only one instance of common pages table being used at any one time (eg. singleton)
    static function create() {
        if (NULL == self::$subsectionsModel) {
           self::$subsectionsModel = new SubsectionsModel();
        }
        return self::$subsectionsModel;
    }
    
    // Retrieves a common page row by its id    
    public function selectPagesByPageId($pageId) {
		
		global $MyPdodb;		        
		$pageId = (int) $pageId;
		$sql = "SELECT * FROM hi_subsections WHERE id = :id  LIMIT 1";        		
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->bindParam(':id', $pageId, PDO::PARAM_INT);
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);			
			return $results[0];		
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}

    }
	
	 // Retrieves a common page row by its pagename    
    public function selectPagesByPageName($pageName) {
		
		global $MyPdodb;		        		
		$sql = "SELECT * FROM hi_subsections WHERE page_alias = :page_alias LIMIT 1";        		
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->bindParam(':page_alias', $pageName, PDO::PARAM_STR);
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);			
			return $results[0];				
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
    }
	
	 // Retrieves a common page row by its pagename    
    public function selectPagesByURL($pageUrl) {
		
		global $MyPdodb;		        		
		$sql = "SELECT * FROM hi_subsections WHERE pagelink = :pagelink LIMIT 1";        		
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->bindParam(':pagelink', $pageUrl, PDO::PARAM_STR);
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);			
			return $results[0];				
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
    }
    
    // Retrieves all pages
    public function selectPages($start=0,$limit,$section_id) {
		
		global $MyPdodb;
		$MyPdodb->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPDOStatement'));		
		$sql = "SELECT * FROM hi_subsections where secid=?";  
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->execute(array($section_id));
			$results = $stm->fetchAll(PDO::FETCH_CLASS);
			$i=0;
			foreach($results as $result){
				//print_r($result);
				
				$sql = "select count(id) as cnt from hi_pages where subsecid='".$result->id."'";
				try{
					$MyPdodb->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
	   				$res = $MyPdodb->queryFirst($sql);
					$totalpages = $res->cnt;
					//array_push($arrres,$totalsubsec);
					$results[$i]->pages_count = $totalpages;
				}catch(PDOException $e){
					Logger::newMessage($e);
					logger::customErrorMsg();
				}
				$i++;
			}
			return $results ;		
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
		
    }
	
	public function selectSubsections() {
		
		global $MyPdodb;
		$MyPdodb->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPDOStatement'));		
		$sql = "SELECT * FROM hi_subsections where status=1";  
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);
			return $results ;		
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
		
    }
	
	// Retrieves total count of common pages
    public function selectCount($secid) {
       global $MyPdodb;
       $sql = "SELECT count(id) as cnt FROM hi_subsections where secid=$secid";        	       	  
	   $MyPdodb->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
	   return $MyPdodb->queryFirst($sql);	   	  
    }
	public function getSubSectionName($subsecid) {
       global $MyPdodb;
       $sql = "SELECT name FROM hi_subsections where id = $subsecid";        	       	  
	   $MyPdodb->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
	   return $MyPdodb->queryFirst($sql);	   	  
    }
	public function selectSubsectionName($section_id) {
       global $MyPdodb;
       $sql = "SELECT name FROM hi_subsections where id=:section_id";        	       	  
	   $stm  = $MyPdodb->prepare($sql);      		
		$stm->bindParam(':section_id', $section_id, PDO::PARAM_INT);
		$stm->execute();       
	  	$results = $stm->fetchAll(PDO::FETCH_CLASS);	
		$section_name = $results[0];		
		return $section_name->name;  
    }
	// Change disaply Status
    public function changeStatus($postvars) {
        global $MyPdodb;
		   $recId = $postvars["subsection_id"];
		   $recStatus = $postvars["recStatus"];	   
		   if($recStatus == 1) { $recStatus=0; } else { $recStatus = 1; }	
			$sql = "UPDATE hi_subsections set status = :status where id = :id ";
			try {
						$stm  = $MyPdodb->prepare($sql);      		
						$stm->bindParam(':status', $recStatus, PDO::PARAM_INT);		
						$stm->bindParam(':id', $recId, PDO::PARAM_INT);
						$stm->execute();       
						$msg=CommonModel::create()->getSettings('SECTION_STATUS_MSG');					
				}catch(PDOException $e){
						Logger::newMessage($e);
						logger::customErrorMsg();
				}
			return $msg;
		
    }
    
	// Remove a common page.
	public function delete($recId) {
       global $MyPdodb;		        
	    $sql = "Delete from hi_subsections where  id = :id ";
		try{
			$stm  = $MyPdodb->prepare($sql);      			
			$stm->bindParam(':id', $recId, PDO::PARAM_INT);
			$stm->execute();       		
			$msg=CommonModel::create()->getSettings('SECTION_DELETE_MSG');
			$sql = "Delete from hi_pages where  subsecid = :id ";
			try{
				$stm  = $MyPdodb->prepare($sql);      			
				$stm->bindParam(':id', $recId, PDO::PARAM_INT);
				$stm->execute();       		
				$msg=CommonModel::create()->getSettings('SECTION_DELETE_MSG');
			}catch(PDOException $e){
				Logger::newMessage($e);
				logger::customErrorMsg();
			}
			return $msg;			
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
    }
	
	
	
	// Update a common page row into the table and returns confirmation message
    public function editRecord($postvars) {
       global $MyPdodb;
	    $name =$postvars['name'];
		$pagelink =$postvars['pagelink'];
		$description =$postvars['description'];
		$pagetitle_seo =$postvars['pagetitle_seo'];
		$keywords =$postvars['keywords'];
		$description_seo =$postvars['description_seo'];
		$order =$postvars['ord'];
		$status =$postvars['status'];
		$tdate = date("Y-m-d");	   
	   	$udate = date("Y-m-d");
		
	   $id = $postvars['subsection_id'];
	   $secid = $postvars['section_id'];
	   $alias = $postvars['name'];
		$alias = preg_replace("/(\s){2,}/",'$1',$alias);					
		$alias = str_replace('-', ' ', $alias);			
		$alias = preg_replace(array('/\s+/','/[^A-Za-z0-9\-]/'), array('-',''), $alias);			
		$page_alias = str_replace("--","-",trim(strtolower($alias)));
						$sql = "select count(id) as cnt_sec from hi_sections where page_alias=:page_alias";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_sec = $results[0]->cnt_sec;
						
						$sql = "select count(id) as cnt_subsec from hi_subsections where page_alias=:page_alias and id!=:id";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->bindParam(':id', $id, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_subsec = $results[0]->cnt_subsec;
					   
						$sql = "select count(id) as cnt_page from hi_pages where page_alias=:page_alias";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_page = $results[0]->cnt_page;
						
						if($cnt_sec > 0 || $cnt_subsec > 0 || $cnt_page > 0){
						
							$msg = "Page Alias already exists, Please try different Title";						   
							return $msg;
						}else{	
							$sql = "Select ord from hi_subsections where secid=:secid and id=:id";
									try{
										$stm  = $MyPdodb->prepare($sql);      	
										$stm->bindParam(':secid', $secid, PDO::PARAM_INT);	
										$stm->bindParam(':id', $id, PDO::PARAM_INT);
										$stm->execute();
										$results = $stm->fetchAll(PDO::FETCH_CLASS);
										$old_disp_ord = $results[0]->ord;
										if($order > $old_disp_ord)								
												{
													$sql = "Update hi_subsections SET ord =ord - 1 where secid=$secid and ord >'$old_disp_ord' and ord <= '$order'";
													//$sql = "Update tbl_section SET ord =$old_disp_ord where  ord = '$ord'";
													$stm  = $MyPdodb->prepare($sql);      		
													$stm->execute();  
												}
											else if($order < $old_disp_ord)								
												{
													$sql = "Update hi_subsections SET ord =ord + 1 where secid=$secid and ord < '$old_disp_ord' and ord >= '$order'";
													//$sql = "Update tbl_section SET ord =$old_disp_ord where  ord = '$ord'";
													$stm  = $MyPdodb->prepare($sql);      		
													$stm->execute();  
												}	
														
									}catch(PDOException $e){
										Logger::newMessage($e);
										logger::customErrorMsg();
									}
							
							$sql = "UPDATE hi_subsections SET ";
							$sql .= " name = :name, ";
							$sql .= " pagelink = :pagelink, ";
							$sql .= " page_alias = :page_alias, ";
							$sql .= " description = :description, ";
							$sql .= " pagetitle_seo = :pagetitle_seo, ";
							$sql .= " keywords = :keywords, ";
							$sql .= " description_seo = :description_seo, ";
							$sql .= " ord = :order, ";
							$sql .= " status = :status, ";
							
							
							$sql .= " udate = :udate ";
							$sql .= " Where id= :id";
							
							try{
								$stm  = $MyPdodb->prepare($sql);				
								$stm->bindParam(":name", $name, PDO::PARAM_STR);
								$stm->bindParam(":pagelink", $pagelink, PDO::PARAM_STR);
								$stm->bindParam(":page_alias", $page_alias, PDO::PARAM_STR);
								$stm->bindParam(":description", $description, PDO::PARAM_STR);
								$stm->bindParam(":pagetitle_seo", $pagetitle_seo, PDO::PARAM_STR);
								$stm->bindParam(":keywords", $keywords, PDO::PARAM_STR);
								$stm->bindParam(":description_seo", $description_seo, PDO::PARAM_STR);
								$stm->bindParam(":order", $order, PDO::PARAM_STR);
								$stm->bindParam(":status", $status, PDO::PARAM_STR);
								
								
								$stm->bindParam(":udate", $udate, PDO::PARAM_STR);
								$stm->bindParam(":id", $id, PDO::PARAM_INT);		
								if($stm->execute()){
									
											
									$msg =CommonModel::create()->getSettings('SECTION_EDIT_MSG');
								}else{
									$msg =CommonModel::create()->getSettings('SECTION_EDIT_ERROR_MSG');
								}			
								
								return $msg;
							}catch(PDOException $e){
								Logger::newMessage($e);
								logger::customErrorMsg();
							}
						}
		
				
		
    }
	public function insertRecord($postvars){
		global $MyPdodb;
		$name =$postvars['name'];
		$pagelink =$postvars['pagelink'];
		$description =$postvars['description'];
		$pagetitle_seo =$postvars['pagetitle_seo'];
		$keywords =$postvars['keywords'];
		$description_seo =$postvars['description_seo'];
		$order =$postvars['ord'];
		$status =$postvars['status'];
		$tdate = date("Y-m-d");	   
	   	$udate = date("Y-m-d");
		$section_id = $postvars['section_id'];
		
		if($opt_out_marketing==''){
			$opt_out_marketing=0;
		}
		$alias = $postvars['name'];
		$alias = preg_replace("/(\s){2,}/",'$1',$alias);					
		$alias = str_replace('-', ' ', $alias);			
		$alias = preg_replace(array('/\s+/','/[^A-Za-z0-9\-]/'), array('-',''), $alias);			
		$page_alias = str_replace("--","-",trim(strtolower($alias)));
						$sql = "select count(id) as cnt_sec from hi_sections where page_alias=:page_alias";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_sec = $results[0]->cnt_sec;
						
						$sql = "select count(id) as cnt_subsec from hi_subsections where page_alias=:page_alias";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_subsec = $results[0]->cnt_subsec;
					   
						$sql = "select count(id) as cnt_page from hi_pages where page_alias=:page_alias";
						$stm  = $MyPdodb->prepare($sql);	
						$stm->bindParam(':page_alias', $page_alias, PDO::PARAM_STR);
						$stm->execute(); 
						$results = $stm->fetchAll(PDO::FETCH_CLASS);
						$cnt_page = $results[0]->cnt_page;
						
						if($cnt_sec > 0 || $cnt_subsec > 0 || $cnt_page > 0){
						
							$msg = "Page Alias already exists, Please try different Title";						   
							return $msg;
						}else{	
							$sql = "insert into hi_subsections (name, secid, pagelink, page_alias, description, pagetitle_seo, keywords, description_seo, udate, tdate, ord, status) values (?,?,?,?,?,?,?,?,?,?,?,?)";
							try{
								$stm  = $MyPdodb->prepare($sql);
										
								$stm->bindParam(1, $name, PDO::PARAM_STR);
								$stm->bindParam(2, $section_id, PDO::PARAM_INT);
								$stm->bindParam(3, $pagelink, PDO::PARAM_STR);
								$stm->bindParam(4, $page_alias, PDO::PARAM_STR);
								$stm->bindParam(5, $description, PDO::PARAM_STR);
								$stm->bindParam(6, $pagetitle_seo, PDO::PARAM_STR);
								$stm->bindParam(7, $keywords, PDO::PARAM_STR);
								$stm->bindParam(8, $description_seo, PDO::PARAM_STR);
								$stm->bindParam(9, $udate, PDO::PARAM_STR);
								$stm->bindParam(10, $tdate, PDO::PARAM_STR);
								$stm->bindParam(11, $order, PDO::PARAM_INT);
								$stm->bindParam(12, $status, PDO::PARAM_INT);
								
									if($stm->execute()){
										$msg =CommonModel::create()->getSettings('SECTION_INSERT_MSG');
									}else{
										$msg =CommonModel::create()->getSettings('SECTION_INSERT_ERROR_MSG');
									}	
									return $msg;
							}catch(PDOException $e){
										Logger::newMessage($e);
										logger::customErrorMsg();
							}
						}
		
		
	}
	public function checkUserEmail($emailAddress,$recId=0){
		global $MyPdodb;	
		 	
		 if(!empty($recId)){		
			 $idSql=" and user_id != :id";
		 }else{
			 $idSql="";
		 }
		 $sql = "SELECT count(user_id) as cnt FROM hi_subsections where email_address= :email ".$idSql;   
		 try{
				 $stm  = $MyPdodb->prepare($sql);      		  
				 $stm->bindParam(':email', $emailAddress, PDO::PARAM_STR);
				 if(!empty($recId)){
					 $stm->bindParam(':id', $recId, PDO::PARAM_STR);
				 }
				$stm->execute();
				$results = $stm->fetchAll(PDO::FETCH_CLASS);		
				$totalCounts = $results[0];		
				$totalCounts->cnt;
				 if($totalCounts->cnt > 0){
					 return false;
				 }else{
				 	return true;
				 }
			}catch(PDOException $e){
					Logger::newMessage($e);
					logger::customErrorMsg();
			}
	}	
	public function checkSubsection($name,$secid,$recId=0){
		global $MyPdodb;	
		 	
		 if(!empty($recId)){		
			 $idSql=" and id != :id";
		 }else{
			 $idSql="";
		 }
		 $sql = "SELECT count(id) as cnt FROM hi_subsections where name= :name and secid=:secid".$idSql;   
		 try{
				 $stm  = $MyPdodb->prepare($sql);      		  
				 $stm->bindParam(':name', $name, PDO::PARAM_STR);
				 $stm->bindParam(':secid', $secid, PDO::PARAM_INT);
				 if(!empty($recId)){
				
					 $stm->bindParam(':id', $recId, PDO::PARAM_STR);
				 }
				$stm->execute();
				$results = $stm->fetchAll(PDO::FETCH_CLASS);
				$totalCounts = $results[0];		
				$totalCounts->cnt;
				 if($totalCounts->cnt > 0){
					 return false;
				 }else{
				 		
					 return true;
				 }
						
			}catch(PDOException $e){
					Logger::newMessage($e);
					logger::customErrorMsg();
			}
	}		
	public function getMenuSubSections($secid) {
		
		global $MyPdodb;		        		
		$sql = "SELECT * FROM hi_subsections WHERE status=1 and secid=$secid order by ord ";        		
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);			
			return $results;				
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
    }
	public function getMenuSubSections_count($secid) {
		
		global $MyPdodb;		        		
		$sql = "SELECT  count(*) as cnt FROM hi_subsections WHERE status=1 and secid=$secid order by ord ";        		
		try{
			$stm  = $MyPdodb->prepare($sql);      		
			$stm->execute();
			$results = $stm->fetchAll(PDO::FETCH_CLASS);			
			return $results[0];				
	    }catch(PDOException $e){
			Logger::newMessage($e);
			logger::customErrorMsg();
		}
		
    }
}
?>