<?php
/**
 * This file is to manage venue categories
 *
 * PHP version 5.0
 *
 * @category	Anova
 * @package		QuestionCategoryFormHandler
 * @author 		karthiselvam_75ag08
 * @copyright	Copyright (c) 2009 {@link http://www.agriya.com Agriya Infoway}
 * @license		http://www.agriya.com Agriya Infoway Licence
 * @version		SVN: $Id:
 * @since 		2008-12-23
 */
/**
 * To include config file
 */
require_once('../../common/configs/config.inc.php');
$CFG['lang']['include_files'][] = 'languages/%s/'.$CFG['admin']['index']['anova_module'].'/admin/questionCategory.php';

$CFG['html']['header'] = 'admin/html_header.php';
$CFG['html']['footer'] = 'admin/html_footer.php';
$CFG['mods']['include_files'][] = 'common/classes/class_ListRecordsHandler.lib.php';
$CFG['mods']['include_files'][] = 'common/classes/class_AnovaHandler.lib.php';

//compulsory
$CFG['site']['is_module_page'] = $CFG['admin']['index']['anova_module'];
$CFG['mods']['is_include_only']['html_header'] = false;
$CFG['html']['is_use_header'] = false;

/**
 * To include application top file
 */
require($CFG['site']['project_path'].'common/application_top.inc.php');

class QuestionCategoryFormHandler extends AnovaHandler
	{
		public $cateogry_arr = array();
		/**
		 * BrowseUsersFormHandler::buildConditionQuery()
		 *
		 * @return
		 **/
		public function buildConditionQuery()
			{
				$this->sql_condition = 'parent_id=0';
			}

		/**
		 * BrowseUsersFormHandler::ConditionQuery()
		 *
		 * @return
		 **/
		public function ConditionQuery()
			{
				$this->sql_condition = 'parent_id=\''.addslashes($this->fields_arr['cat_id']).'\'';
			}

		/**
		 * searchMembersFormHandler::buildSortQuery()
		 *
		 * @return
		 **/
		public function buildSortQuery()
			{
				$this->sql_sort = $this->fields_arr['orderby_field'].' '.$this->fields_arr['orderby'];
			}

		/**
		 * showCategory()
		 *
		 * @return
		 **/
		public function showCategory()
			{
				$this->showCategory_arr	=	array();
				if (!$this->isResultsFound())
					{
						$this->showCategory_arr['RecordCount']	=	0;
					}
					$this->showCategory_arr['RecordCount']	=	1;
				$found = false;
				$this->showCategory_arr['row']	=	array();
				$inc=0;
				while($row = $this->fetchResultRecord())
					{
						$this->showCategory_arr['row'][$inc]['record']	=	$row;
						$this->showCategory_arr['row'][$inc]['questionCategory_mode_url']	=	'questionCategory.php?mode=viewsubcategory&amp;cat_id='.$row['cat_id'];
						$this->showCategory_arr['row'][$inc]['questionCategory_url']	=	'questionCategory.php?cat_id='.$row['cat_id'].'&amp;start='.$this->fields_arr['start'].'&amp;mode=edit';
						$inc++;
					}
				$this->showCategory_arr['populateFilterList']	=	$this->populateFilterList($this->action_arr, $this->fields_arr['action']);
				return $this->showCategory_arr;
			}

		/**
		 *
		 * @access public
		 * @return void
		 **/
		public function showCategoryName($cat_id)
		    {
				$sql = 'SELECT cat_name, seo_title, cat_id FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE cat_id='.$this->dbObj->Param('cat_id');
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($cat_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
				if ($rs->PO_RecordCount())
					{
						$row = $rs->FetchRow();
						echo $row['cat_name'];
					}
		    }

		/**
		 * showCategory()
		 *
		 * @return
		 **/
		public function showSubCategory()
			{
				$this->showSubCategory_arr	=	array();
				$this->showSubCategory_arr['RecordCount']	=	1;
				if (!$this->isResultsFound())
					{
						$this->showSubCategory_arr['RecordCount']	=	0;
					}
				$this->showSubCategory_arr['row']	=	array();
				$found = false;
				$inc=0;
				while($row = $this->fetchResultRecord())
					{
						$this->showSubCategory_arr['row'][$inc]['record']	=	$row;
						$this->showSubCategory_arr['row'][$inc]['questionCategory_url']	=	'questionCategory.php?sub_cat_id='.$row['cat_id'].'&amp;start='.$this->fields_arr['start'].'&amp;mode=editsubcategory';
						$inc++;
					}
					$this->showSubCategory_arr['getMultiCheckBoxValue']	=	'if(getMultiCheckBoxValue(\'formViewCategory\', \'checkall\', \''.$this->LANG['category_err_tip_select_subcategory'].'\')){getAction()}';
					$this->showSubCategory_arr['populateFilterList']	=	$this->populateFilterList($this->action_arr, $this->fields_arr['action']);
				return $this->showSubCategory_arr;
			}

		/**
		 * To populate filter list
		 *
		 * @param	    string $highlight Selected filter
		 * @return 		void
		 * @access		public
		*/
		public function populateFilterList($array_to_expand, $highlight)
			{
				$this->populateFilterList_arr	=	array();
				$inc=0;
				foreach ($array_to_expand as $key=>$value)
					{
						$this->populateFilterList_arr[$inc]['key']	=	$key;
						$this->populateFilterList_arr[$inc]['name']	=	$value;
						if($key == $highlight)
							{
								$this->populateFilterList_arr[$inc]['selected_chk']	=	'selected="selected"';
							}
						else
							{
								$this->populateFilterList_arr[$inc]['selected_chk']	=	'';
							}
						$inc++;
					}
				return 	$this->populateFilterList_arr;
			}

		public function chkCategoryExists($err_tip='')
			{
				$sql = 'SELECT COUNT(cat_id) AS count FROM '.$this->CFG['db']['tbl']['questions_category'].' WHERE'.
						' cat_name='.$this->dbObj->Param($this->fields_arr['category']);
				$fields_value_array[] = $this->fields_arr['category'];
				if ($this->fields_arr['cat_id'])
					{
						$sql .= ' AND cat_id!='.$this->dbObj->Param($this->fields_arr['cat_id']);
						$sql .= ' AND parent_id = 0';
						$fields_value_array[] = $this->fields_arr['cat_id'];
					}

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, $fields_value_array);
				if (!$rs)
						trigger_db_error($this->dbObj);

				$row = $rs->FetchRow();
				if ($row['count'])
					{
						$this->fields_err_tip_arr['category'] = $err_tip;
						return false;
					}
				return true;
			}

		public function chkSubCategoryExists($err_tip='')
			{
				$sql = 'SELECT COUNT(cat_id) AS count FROM '.$this->CFG['db']['tbl']['questions_category'].' WHERE'.
						' cat_name='.$this->dbObj->Param($this->fields_arr['category']).
						' AND parent_id='.$this->dbObj->Param($this->fields_arr['parent_id']);
				$fields_value_array[] = $this->fields_arr['category'];
				$fields_value_array[] = $this->fields_arr['parent_id'];
				if ($this->fields_arr['sub_cat_id'])
					{
						$sql .= ' AND cat_id!='.$this->dbObj->Param($this->fields_arr['sub_cat_id']);
						$fields_value_array[] = $this->fields_arr['sub_cat_id'];
					}

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, $fields_value_array);
				if (!$rs)
						trigger_db_error($this->dbObj);

				$row = $rs->FetchRow();
				if ($row['count'])
					{
						$this->fields_err_tip_arr['category'] = $err_tip;
						return false;
					}
				return true;
			}

		public function isValidCategoryId($cat_id, $err_tip='')
			{
				$sql = 'SELECT cat_name, seo_title, parent_id, status FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE cat_id=\''.addslashes($this->fields_arr[$cat_id]).'\'';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
						trigger_db_error($this->dbObj);

				$row = array();
				if (!$rs->PO_RecordCount())
					{
						$this->setCommonErrorMsg($err_tip);
						return false;
					}
				$this->link_details_arr = $rs->FetchRow();
			}

		/**
		 *
		 * @access public
		 * @return void
		 **/
		public function countSubCategory($cat_id)
			{
				$sql = 'SELECT COUNT(cat_id) as question_count FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE parent_id =\''.$cat_id.'\'';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
						trigger_db_error($this->dbObj);

				$row = $rs->FetchRow();
				return $row['question_count'];
			}

		/**
		 *
		 * @access public
		 * @return void
		 **/
		public function countQuestion($cat_id, $parent_id=0)
			{
				if ($parent_id==0)
					$cat_id = 'SELECT cat_id FROM '.$this->CFG['db']['tbl']['questions_category'].' WHERE parent_id=\''.addslashes($cat_id).'\'';
				else
					$cat_id = addslashes($cat_id);

				$sql = 'SELECT COUNT(ques_id) as question_count FROM '.$this->CFG['db']['tbl']['questions'].' '.
						'WHERE cat_id IN ('.$cat_id.')';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
						trigger_db_error($this->dbObj);

				$row = $rs->FetchRow();
				return $row['question_count'];
			}

		/**
		 * updateUsersTable()
		 *
		 * @return
		 **/
		public function insertCategory()
			{
				$sql = 'INSERT INTO '.$this->CFG['db']['tbl']['questions_category'].
						' SET cat_name='.$this->dbObj->Param('category').
						', seo_title='.$this->dbObj->Param('seo_title').
						', parent_id='.$this->dbObj->Param('parent_id').
						', status='.$this->dbObj->Param('status').
						', date_added=NOW()';

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($this->fields_arr['category'],
														 $this->fields_arr['seo_title'],
														 $this->fields_arr['parent_id'],
														 $this->fields_arr['status']));
				if (!$rs)
						trigger_db_error($this->dbObj);
				return true;
			}

		public function updateCategory($cat_field)
			{
				$sql = 'UPDATE '.$this->CFG['db']['tbl']['questions_category'].
						' SET cat_name='.$this->dbObj->Param('category').
						', seo_title='.$this->dbObj->Param('seo_title').
						', parent_id='.$this->dbObj->Param('parent_id').
						', status='.$this->dbObj->Param('status').
						' WHERE cat_id = '.$this->dbObj->Param($cat_field);

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($this->fields_arr['category'],
														 $this->fields_arr['seo_title'],
														 $this->fields_arr['parent_id'],
														 $this->fields_arr['status'],
														 $this->fields_arr[$cat_field]));
				if (!$rs)
						trigger_db_error($this->dbObj);
				return true;
			}

		public function updateAction($fieldToUpdate, $fieldValue)
			{
				$cat_ids = $this->fields_arr['cat_ids'];
				$sql = 'UPDATE '.$this->CFG['db']['tbl']['questions_category'].' SET '.$fieldToUpdate.'=\''.$fieldValue.'\' '.
						' WHERE cat_id IN ('.$cat_ids.')';

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
						trigger_db_error($this->dbObj);
				return true;
			}
		/**
		 *
		 * @access public
		 * @return void
		 **/
		public function populateCategories($parent_id)
		    {
				$sql = 'SELECT cat_name, cat_id FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE parent_id=0 AND status=\'1\' ORDER BY cat_name';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
					    trigger_db_error($this->dbObj);
				$this->populateCategories_arr	=	array();
				if ($rs->PO_RecordCount())
					{
						$inc=0;
						while($row = $rs->FetchRow())
						{
							$this->populateCategories_arr[$inc]['record']	=	$row;
							if($row['cat_id']==$parent_id)
								{
									$this->populateCategories_arr[$inc]['selected_chk']	=	'selected';
								}
							else
								{
									$this->populateCategories_arr[$inc]['selected_chk']	=	'';
								}
							$inc++;
						} // while
					}
				return $this->populateCategories_arr;
		    }

		/**
		 *
		 * @return 		void
		 * @access 		public
		 */
		public function updateHasChild()
			{
				$cat_id = $this->fields_arr['cat_id'];
				$sql = 'SELECT 1 FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE parent_id='.$this->dbObj->Param($cat_id).' AND status=\'1\'';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($cat_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
				$has_child = '0';
				if ($rs->PO_RecordCount())
					$has_child = '1';

				$sql = 'UPDATE '.$this->CFG['db']['tbl']['questions_category'].
						' SET has_child='.$this->dbObj->Param($has_child).
						' WHERE cat_id='.$this->dbObj->Param($cat_id);
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($has_child, $cat_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}

		public function deleteCategory()
			{
				$cat_ids = $this->fields_arr['cat_ids'];

				$sql = 'SELECT cat_id, parent_id, has_child FROM '.$this->CFG['db']['tbl']['questions_category'].
						' WHERE cat_id IN ('.$cat_ids.')';

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
						trigger_db_error($this->dbObj);
				if ($rs->PO_RecordCount())
					{
						while($row = $rs->FetchRow())
						{
							$this->category_arr[] = $row['cat_id'];
							if($row['parent_id'])
								{
									$this->updateParentCategory($row['parent_id'], $row['cat_id']);
								}
							if($row['has_child'])
								{
									$this->updateChildCategory($row['cat_id']);
								}
						}
					}
					foreach($this->category_arr as $pcat_id)
						{
							$sql = 'SELECT ques_id FROM '.$this->CFG['db']['tbl']['questions'].
									' WHERE (cat_id='.$this->dbObj->Param($pcat_id).' OR pcat_id='.$this->dbObj->Param($pcat_id).')';
							$stmt = $this->dbObj->Prepare($sql);
							$rs = $this->dbObj->Execute($stmt, array($pcat_id, $pcat_id));
							if (!$rs)
							        trigger_db_error($this->dbObj);
							$ques_arr = array();
							if ($rs->PO_RecordCount())
								{
									while($row = $rs->FetchRow())
										{
											$ques_arr[] = $row['ques_id'];
										}
									$ques_ids = implode(",", $ques_arr);
									$this->deleteQuestions($this->CFG['db']['tbl']['questions'], $this->CFG['db']['tbl']['answers'], $this->CFG['db']['tbl']['users_ans_log'], $ques_ids);
								}
						}

					$delete_catids = implode(",", $this->category_arr);
					if($delete_catids != ''){
					$delsql = 'DELETE FROM '.$this->CFG['db']['tbl']['questions_category'].
							' WHERE cat_id IN ('.$delete_catids.')';

					$delstmt = $this->dbObj->Prepare($delsql);
					$del_rs = $this->dbObj->Execute($delstmt);
					if (!$del_rs)
							trigger_db_error($this->dbObj);
					}

			}
		public function updateParentCategory($parent_id, $cat_id)
			{
				$parent_sql = 'SELECT 1 FROM '.$this->CFG['db']['tbl']['questions_category'].
								' WHERE cat_id!='.$this->dbObj->Param($cat_id).' AND parent_id='.$this->dbObj->Param($parent_id);
				$parent_stmt = $this->dbObj->Prepare($parent_sql);

				$parent_rs = $this->dbObj->Execute($parent_stmt, array($cat_id, $parent_id));
				if (!$parent_rs)
    				trigger_db_error($this->dbObj);

    			if(!$parent_rs->PO_RecordCount())
    				{
						$upd_parent = 'UPDATE '.$this->CFG['db']['tbl']['questions_category'].
										' SET has_child=0 WHERE cat_id='.$this->dbObj->Param($parent_id);
						$upd_stmt = $this->dbObj->Prepare($upd_parent);

						$upd_rs = $this->dbObj->Execute($upd_stmt, array($parent_id));
						if (!$upd_rs)
    						trigger_db_error($this->dbObj);
					}
			}
		public function updateChildCategory($cat_id)
			{
				$parent_sql = 'SELECT cat_id FROM '.$this->CFG['db']['tbl']['questions_category'].
								' WHERE parent_id='.$this->dbObj->Param($cat_id);
				$parent_stmt = $this->dbObj->Prepare($parent_sql);

				$parent_rs = $this->dbObj->Execute($parent_stmt, array($cat_id));
				if (!$parent_rs)
					trigger_db_error($this->dbObj);

				if($parent_rs->PO_RecordCount())
					{
						while($record = $parent_rs->FetchRow())
							{
								$this->category_arr[] = $record['cat_id'];
								$this->updateChildCategory($record['cat_id']);
							}
					}
			}
		/**
		 * QuestionCategoryFormHandler::deleteQuestions()
		 *
		 * @param mixed $table_name
		 * @param mixed $answers_table
		 * @param mixed $users_ans_log_table
		 * @return
		 */
		public function deleteQuestions($table_name, $answers_table, $users_ans_log_table, $ques_ids)
		    {
				if(!$ques_ids)
					return ;

				$id = $ques_ids;

				$sql = 'SELECT user_id, video_id, status, best_ans_id, audio_id, pcat_id, cat_id, tags FROM '.$table_name.' WHERE ques_id IN ('.$id.')';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
				        trigger_db_error($this->dbObj);
				if ($rs->PO_RecordCount())
					{
						while($row = $rs->FetchRow()){

							if($row['status'] != 'ToActivate')
								{

									if($row['status'] != 'Blocked')
										{
											$sql = 'UPDATE '.$users_ans_log_table.' SET total_ques=total_ques-1';
												if($this->CFG['admin']['ask_answers']['allowed'])
												$sql .= ', total_points=total_points-'.$this->CFG['admin']['ask_answers']['points'];
											$sql .= ' WHERE user_id='.$this->dbObj->Param('uid');

											$stmt = $this->dbObj->Prepare($sql);
											$rs_user_log = $this->dbObj->Execute($stmt, array($row['user_id']));
												if (!$rs_user_log)
									        			trigger_db_error($this->dbObj);
									       }

									$this->updateCategoryQuestionCount($row['pcat_id']);
									if ($row['cat_id'])
										$this->updateCategoryQuestionCount($row['cat_id']);

									//Decrement tag_count
									$this->updateDeletedQuestionsTagCount($row['tags']);
								}

							//Change the video and audio status
							$this->updateQuestionVideoStatus($row['video_id']);
							$this->updateQuestionAudioStatus($row['audio_id']);
							if ($row['best_ans_id'])
								$this->decreaseBestAnswerCount($row['best_ans_id']);
							//to delete attachments
							if ($this->CFG['admin']['attachments_questions']['allowed'])
								$this->commonDeleteAttachments('Question', $ques_ids);
							$this->deleteQuestionsComment($ques_ids);
						} // while
					}

				$sql = 'DELETE FROM '.$table_name.' WHERE ques_id IN ('.$id.')';

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
				        trigger_db_error($this->dbObj);

				$sql = 'SELECT user_id, video_id, ans_id, audio_id FROM '.$answers_table.' WHERE ques_id IN ('.$id.')';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
				        trigger_db_error($this->dbObj);
				if ($rs->PO_RecordCount())
					{
						while($row = $rs->FetchRow()){
							$sql = 'UPDATE '.$users_ans_log_table.' SET total_ans=total_ans-1'.
									' WHERE user_id='.$this->dbObj->Param('uid');

							$stmt = $this->dbObj->Prepare($sql);
							$rs_user_log = $this->dbObj->Execute($stmt, array($row['user_id']));
							if (!$rs_user_log)
							        trigger_db_error($this->dbObj);

							$this->updateAnswerVideoStatus($row['video_id']);
							$this->updateAnswerAudioStatus($row['audio_id']);
							//to delete attachments
							if ($this->CFG['admin']['attachments_answers']['allowed'])
								$this->commonDeleteAttachments('Answer', $row['ans_id']);

						} // while
					}

				$sql = 'DELETE FROM '.$answers_table.' WHERE ques_id IN ('.$id.')';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
				        trigger_db_error($this->dbObj);
		    }
		/**
		 * QuestionCategoryFormHandler::updateCategoryQuestionCount()
		 *
		 * To update questions count
		 *
		 * @param mixed $cat_id
		 * @return
		 */
		public function updateCategoryQuestionCount($cat_id)
			{
				$sql = 'UPDATE '.$this->CFG['db']['tbl']['questions_category'].
						' SET total_questions=total_questions-1'.
						' WHERE cat_id='.$this->dbObj->Param($cat_id).
						' AND total_questions>0';
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($cat_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
		/**
		 * QuestionCategoryFormHandler::updateDeletedQuestionsTagCount()
		 *
		 * @param mixed $tags
		 * @return
		 */
		public function updateDeletedQuestionsTagCount($tags)
			{
				$tags_arr = explode(' ', $tags);
				foreach($tags_arr as $tagname)
					{
						$sql = 'UPDATE '.$this->CFG['db']['tbl']['tags'].
							' SET total_count=total_count-1'.
							' WHERE tag_name='.$this->dbObj->Param($tagname);
						$stmt = $this->dbObj->Prepare($sql);
						$rs = $this->dbObj->Execute($stmt, array($tagname));
						if (!$rs)
					    		trigger_db_error($this->dbObj);
					}
			}
		/**
		 * QuestionCategoryFormHandler::updateQuestionVideoStatus()
		 *
		 * @param mixed $video_id
		 * @return
		 */
		public function updateQuestionVideoStatus($video_id)
			{
				if (!$video_id) return ;

				$sql = 'UPDATE '.$this->CFG['db']['tbl']['ans_video'].
						' SET video_status=\'Deleted\''.
						' WHERE video_id='.$this->dbObj->Param($video_id);
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($video_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
		/**
		 * QuestionCategoryFormHandler::updateQuestionAudioStatus()
		 *
		 * @param mixed $audio_id
		 * @return
		 */
		public function updateQuestionAudioStatus($audio_id)
			{
				if (!$audio_id) return ;

				$sql = 'UPDATE '.$this->CFG['db']['tbl']['ans_audio'].
						' SET audio_status=\'Deleted\''.
						' WHERE audio_id='.$this->dbObj->Param($audio_id);
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($audio_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
		/**
		 * QuestionCategoryFormHandler::decreaseBestAnswerCount()
		 *
		 * @param mixed $bid
		 * @return
		 */
		public function decreaseBestAnswerCount($bid)
			{
				if ($bid and $this->CFG['admin']['best_answers']['allowed'])
					{
						$best_sql = 'SELECT user_id FROM '.$this->CFG['db']['tbl']['answers'].' WHERE ans_id='.$bid;
						$best_stmt = $this->dbObj->Prepare($best_sql);
						$best_rs = $this->dbObj->Execute($best_stmt);
						if (!$best_rs)
						        trigger_db_error($this->dbObj);

						if ($best_rs->PO_RecordCount()){
								$best_row = $best_rs->FetchRow();
								$best_ans_userid = $best_row['user_id'];
							}

						$sql = 'UPDATE '.$this->CFG['db']['tbl']['users_ans_log'].
								' SET total_best_ans = total_best_ans - 1'.
								' WHERE user_id='.$this->dbObj->Param($best_ans_userid);
						$stmt = $this->dbObj->Prepare($sql);
						$rs = $this->dbObj->Execute($stmt, array($best_ans_userid));
						if (!$rs)
							    trigger_db_error($this->dbObj);
					}
			}
		public function deleteQuestionsComment($id)
			{
				$sql = 'DELETE FROM '.$this->CFG['db']['tbl']['question_comment'].
						' WHERE ques_id IN ('.$id.')';

				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt);
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
		/**
		 * QuestionCategoryFormHandler::updateAnswerVideoStatus()
		 *
		 * @param mixed $video_id
		 * @return
		 */
		public function updateAnswerVideoStatus($video_id)
			{
				if (!$video_id) return ;

				$sql = 'UPDATE '.$this->CFG['db']['tbl']['ans_video'].
						' SET video_status=\'Deleted\''.
						' WHERE video_id='.$this->dbObj->Param($video_id);
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($video_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
		/**
		 * AnswersFormHandler::updateAnswerAudioStatus()
		 *
		 * @param mixed $audio_id
		 * @return
		 */
		public function updateAnswerAudioStatus($audio_id)
			{
				if (!$audio_id) return ;

				$sql = 'UPDATE '.$this->CFG['db']['tbl']['ans_audio'].
						' SET audio_status=\'Deleted\''.
						' WHERE audio_id='.$this->dbObj->Param($audio_id);
				$stmt = $this->dbObj->Prepare($sql);
				$rs = $this->dbObj->Execute($stmt, array($audio_id));
				if (!$rs)
					    trigger_db_error($this->dbObj);
			}
	}
//<<<<<---------------class QuestionCategoryFormHandler------///
//--------------------Code begins-------------->>>>>//
$categoryfrm = new QuestionCategoryFormHandler();
$categoryfrm->left_navigation_div = 'anovaAnswers';
$categoryfrm->setPageBlockNames(array('form_add_category', 'form_view_category', 'form_view_sub_category', 'form_add_sub_category'));
// Set the form fields
$categoryfrm->setFormField('user_id', '');
$categoryfrm->setFormField('category', '');
$categoryfrm->setFormField('seo_title', '');
$categoryfrm->setFormField('parent_id', 0);
$categoryfrm->setFormField('status', 1);
$categoryfrm->setFormField('cat_id', '');
$categoryfrm->setFormField('sub_cat_id', '');
$categoryfrm->setFormField('cat_ids', array());
$categoryfrm->setFormField('sub_cat_ids', array());
$categoryfrm->setFormField('action', '');
$categoryfrm->setFormField('mode', '');
$categoryfrm->setFormField('msg', '');
$categoryfrm->setFormField('orderby_field', 'qc.cat_name');
$categoryfrm->setFormField('orderby', 'ASC');
$categoryfrm->action_arr = array('' => $LANG['common_select_action'],
					'Active' => $LANG['common_activate'],
					'Inactive' => $LANG['common_inactivate'],
					'Delete' => $LANG['common_delete'],
					);

// Default page block
$categoryfrm->setAllPageBlocksHide();
/*********** Page Navigation Start *********/
$categoryfrm->setFormField('start', '0');
$categoryfrm->setFormField('numpg', $CFG['data_tbl']['numpg']);
$categoryfrm->setMinRecordSelectLimit(2);
$categoryfrm->setMaxRecordSelectLimit($CFG['data_tbl']['max_record_select_limit']);
$categoryfrm->setNumPerPageListArr($CFG['data_tbl']['numperpage_list_arr']);

$categoryfrm->setTableNames(array($CFG['db']['tbl']['questions_category'].' AS qc'));
$categoryfrm->setReturnColumns(array('qc.cat_id', 'qc.cat_name', 'qc.seo_title', 'qc.total_questions', 'qc.total_debates', 'CASE WHEN qc.status=1 THEN \''.$LANG['common_display_active'].'\' ELSE \''.$LANG['common_display_inactive'].'\' END AS status', 'qc.date_added'));
/************ page Navigation stop *************/
$categoryfrm->sanitizeFormInputs($_REQUEST);
$categoryfrm->setPageBlockShow('form_view_category');
if ($categoryfrm->isFormPOSTed($_POST, 'add_category'))
	{
		// Validations
		$categoryfrm->chkIsNotEmpty('status', $LANG['err_tip_compulsory']);
		$categoryfrm->chkIsNotEmpty('category', $LANG['err_tip_compulsory'])and
			$categoryfrm->chkCategoryExists($LANG['category_err_tip_category_exists']);
		if ($categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setFormField('seo_title', $categoryfrm->getSeoTitleForQuestionCategory($categoryfrm->getFormField('category'), $categoryfrm->getFormField('cat_id')));
				if ($categoryfrm->getFormField('cat_id'))
					{
						$categoryfrm->updateCategory('cat_id');
						Redirect2URL($CFG['site']['relative_url'].'questionCategory.php?msg=updated');
					}
				else
					{
						$categoryfrm->insertCategory();
						Redirect2URL($CFG['site']['relative_url'].'questionCategory.php?msg=added');
					}
			}
		else
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('form_add_category');
				$categoryfrm->setCommonErrorMsg($categoryfrm->getCommonErrorMsg().$LANG['common_msg_error_sorry']);
				$categoryfrm->setPageBlockShow('block_msg_form_error');
			}
	}
elseif ($categoryfrm->isFormPOSTed($_POST, 'add_sub_category'))
	{
		// Validations
		$categoryfrm->chkIsNotEmpty('parent_id', $LANG['err_tip_compulsory']);
		$categoryfrm->chkIsNotEmpty('status', $LANG['err_tip_compulsory']);
		$categoryfrm->chkIsNotEmpty('category', $LANG['err_tip_compulsory'])and
			$categoryfrm->chkSubCategoryExists($LANG['category_err_tip_sub_category_exists']);
		if ($categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setFormField('seo_title', $categoryfrm->getSeoTitleForQuestionCategory($categoryfrm->getFormField('category'), $categoryfrm->getFormField('cat_id')));
				if ($categoryfrm->getFormField('sub_cat_id'))
					{
						$categoryfrm->updateCategory('sub_cat_id');
						$categoryfrm->setFormField('cat_id', $categoryfrm->getFormField('parent_id'));
						$categoryfrm->updateHasChild();
						Redirect2URL($CFG['site']['relative_url'].'questionCategory.php?mode=viewsubcategory&cat_id='.$categoryfrm->getFormField('parent_id').'&msg=updatedsub');
					}
				else
					{
						$categoryfrm->insertCategory();
						$categoryfrm->setFormField('cat_id', $categoryfrm->getFormField('parent_id'));
						$categoryfrm->updateHasChild();
						Redirect2URL($CFG['site']['relative_url'].'questionCategory.php?mode=viewsubcategory&cat_id='.$categoryfrm->getFormField('parent_id').'&msg=addedsub');
					}
			}
		else
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setCommonErrorMsg($LANG['common_msg_error_sorry']);
				$categoryfrm->setPageBlockShow('block_msg_form_error');
				$categoryfrm->setPageBlockShow('form_add_sub_category');
			}
	}
elseif ($categoryfrm->isFormPOSTed($_POST, 'confirm_action'))
	{
		// Validations
		$categoryfrm->chkIsNotEmpty('cat_ids', $LANG['err_tip_compulsory'])or
			$categoryfrm->setCommonErrorMsg($LANG['category_err_tip_select_category']);
		$categoryfrm->chkIsNotEmpty('action', $LANG['err_tip_compulsory'])or
			$categoryfrm->setCommonErrorMsg($LANG['err_tip_select_action']);
		if ($categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('block_msg_form_success');
				switch($categoryfrm->getFormField('action')){
					case 'Active':
						$categoryfrm->updateAction('status', '1');
						$categoryfrm->setCommonSuccessMsg($LANG['category_success_activate']);
						break;
					case 'Inactive':
						$categoryfrm->updateAction('status', '0');
						$categoryfrm->setCommonSuccessMsg($LANG['category_success_inactivate']);
						break;
					case 'Delete':
						$categoryfrm->deleteCategory();
						$categoryfrm->setCommonSuccessMsg($LANG['category_success_deleted']);
						break;
				} // switch
				if ($categoryfrm->getFormField('cat_id'))
					{
						$categoryfrm->setPageBlockShow('form_view_sub_category');
						$categoryfrm->updateHasChild();
					}
				else
					{
						$categoryfrm->setPageBlockShow('form_view_category');
					}
			}
		else
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('block_msg_form_error');
			}
	}
elseif ($categoryfrm->isFormPOSTed($_POST, 'cancel'))
	{
		$mode = $categoryfrm->getFormField('mode');
		$catId = $categoryfrm->getFormField('parent_id');
		if ($catId AND strcmp($mode, 'editsubcategory')==0)
			Redirect2URL($CFG['site']['relative_url'].'questionCategory.php?mode=viewsubcategory&cat_id='.$catId);

		Redirect2URL($CFG['site']['relative_url'].'questionCategory.php');
	}
elseif ($categoryfrm->isFormGETed($_GET, 'cat_id'))
	{
		$categoryfrm->chkIsNotEmpty('cat_id', $LANG['err_tip_compulsory'])and
			$categoryfrm->isValidCategoryId('cat_id', $LANG['category_err_tip_invalid_id']);
		if ($categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setFormField('category', $categoryfrm->link_details_arr['cat_name']);
				$categoryfrm->setFormField('status', $categoryfrm->link_details_arr['status']);
			}
		else
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('block_msg_form_error');
			}
	}
elseif ($categoryfrm->isFormGETed($_GET, 'sub_cat_id'))
	{
		$categoryfrm->chkIsNotEmpty('sub_cat_id', $LANG['err_tip_compulsory'])and
			$categoryfrm->isValidCategoryId('sub_cat_id', $LANG['category_err_tip_invalid_id']);
		if ($categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setFormField('category', $categoryfrm->link_details_arr['cat_name']);
				$categoryfrm->setFormField('parent_id', $categoryfrm->link_details_arr['parent_id']);
				$categoryfrm->setFormField('status', $categoryfrm->link_details_arr['status']);
			}
		else
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('block_msg_form_error');
			}
	}
if ($categoryfrm->isPageGETed($_GET, 'mode') && $categoryfrm->isValidFormInputs())
    {
		$mode = $categoryfrm->getFormField('mode');
		$catId = $categoryfrm->getFormField('cat_id');
		$subCatId = $categoryfrm->getFormField('sub_cat_id');
		if ($catId AND strcmp($mode, 'edit')==0)
		    {
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('form_add_category');
		    }
		elseif ($subCatId AND strcmp($mode, 'editsubcategory')==0)
		    {
		        $categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('form_add_sub_category');
		    }
		elseif ($catId AND strcmp($mode, 'viewsubcategory')==0 AND $categoryfrm->isValidFormInputs())
			{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('form_view_sub_category');
			}
		else if (strcmp($mode, 'add')==0)
         	{
				$categoryfrm->setAllPageBlocksHide();
				$categoryfrm->setPageBlockShow('form_add_category');
	        }
		else if (strcmp($mode, 'addsubcategory')==0)
         	{
				$categoryfrm->setAllPageBlocksHide();
	            $categoryfrm->setPageBlockShow('form_add_sub_category');
	        }
    }
if ($categoryfrm->isPageGETed($_GET, 'msg'))
	{
		$categoryfrm->setPageBlockShow('block_msg_form_success');
		switch($categoryfrm->getFormField('msg'))
			{
				case 'added':
					$categoryfrm->setCommonSuccessMsg($LANG['category_success_message']);
					break;
				case 'addedsub':
					$categoryfrm->setCommonSuccessMsg($LANG['subcategory_success_message']);
					break;
				case 'updated':
					$categoryfrm->setCommonSuccessMsg($LANG['category_success_update_message']);
					break;
				case 'updatedsub':
					$categoryfrm->setCommonSuccessMsg($LANG['subcategory_success_update_message']);
					break;
			} // switch
	}
$categoryfrm->questionCategoryCommon['title']	=	$title = $LANG['category_title'];
if ($categoryfrm->isShowPageBlock('form_add_category'))
	{
		$categoryfrm->questionCategoryCommon['title']	=	$title = $LANG['category_add'];
		$catId = $categoryfrm->getFormField('cat_id');
		if ($catId)
		    {
		       $categoryfrm->questionCategoryCommon['title']	=	 $title = $LANG['category_subtitle_edit_category'];
		    }
	}
if ($categoryfrm->isShowPageBlock('form_add_sub_category'))
	{
		$categoryfrm->questionCategoryCommon['title']	=	$title = $LANG['category_add_sub'];
		$subCatId = $categoryfrm->getFormField('sub_cat_id');
		if ($subCatId)
		    {
		        $categoryfrm->questionCategoryCommon['title']	=	$title = $LANG['category_subtitle_edit_sub_category'];
		    }
		$categoryfrm->form_add_sub_category['populateCategories']	=	$categoryfrm->populateCategories($categoryfrm->getFormField('parent_id'));
	}
//<<<<--------------------Code Ends----------------------//
//--------------------Page block templates begins-------------------->>>>>//
if ($categoryfrm->isShowPageBlock('form_add_category'))
	{
		$catId = $categoryfrm->getFormField('cat_id');
		$categoryfrm->form_add_category['hidden_array']	=	array('start');
	}
if ($categoryfrm->isShowPageBlock('form_add_sub_category'))
	{
		$subCatId = $categoryfrm->getFormField('sub_cat_id');
		$categoryfrm->form_add_sub_category['hidden_array']	=	array('start');
	}
if ($categoryfrm->isShowPageBlock('form_view_category'))
	{
		/****** navigtion continue*********/
		$categoryfrm->buildSelectQuery();
		$categoryfrm->buildConditionQuery();
		$categoryfrm->buildSortQuery();
		$categoryfrm->buildQuery();
		$categoryfrm->executeQuery();
		$smartyObj->assign('smarty_paging_list', $categoryfrm->populatePageLinksGET($categoryfrm->getFormField('start')));
		$categoryfrm->form_view_category['showCategory']	=	$categoryfrm->showCategory();
		$categoryfrm->form_view_category['hidden_array']	=	array('start', 'orderby_field', 'orderby');
	}
if ($categoryfrm->isShowPageBlock('form_view_sub_category'))
	{
		/****** navigtion continue*********/
		$categoryfrm->buildSelectQuery();
		$categoryfrm->ConditionQuery();
		$categoryfrm->buildSortQuery();
		$categoryfrm->buildQuery();
		$categoryfrm->executeQuery();
		$categoryfrm->form_view_sub_category['showSubCategory']	=	$categoryfrm->showSubCategory();
		$categoryfrm->form_view_sub_category['hidden_array']	=	array('mode', 'cat_id', 'start');
		$smartyObj->assign('smarty_paging_list', $categoryfrm->populatePageLinksGET($categoryfrm->getFormField('start'), array('mode', 'cat_id')));
		$LANG['category_activage_msg'] = $LANG['category_activage_submsg'];
		$LANG['category_inactivage_msg'] = $LANG['category_inactivage_submsg'];
	}
//include the header file
$categoryfrm->includeHeader();
//include the content of the page
setTemplateFolder('admin/', $CFG['admin']['index']['anova_module']);
$smartyObj->display('questionCategory.tpl');
//includ the footer of the page
?>
<script type="text/javascript" language="javascript">
	var block_arr= new Array('selMsgConfirm');
	var replace_url = '<?php echo $CFG['site']['url'];?>login.php';
	var please_select_action = '<?php echo $LANG['err_tip_select_action'];?>';
	var confirm_message = '';
	function getAction()
		{
			var act_value = document.formViewCategory.action.value;
			if(act_value)
				{
					switch (act_value)
						{
							case 'Active':
								confirm_message = '<?php echo $LANG['category_activage_msg'];?>';
								break;
							case 'Inactive':
								confirm_message = '<?php echo $LANG['category_inactivage_msg'];?>';
								break;
							case 'Delete':
								confirm_message = '<?php echo $LANG['category_delete_msg'];?>';
								break;
						}
					$('confirmMessage').innerHTML = confirm_message;
					document.formConfirm.action.value = act_value;
					Confirmation('selMsgConfirm', 'formConfirm', Array('cat_ids'), Array(multiCheckValue), Array('value'), 'formViewCategory');
				}
				else
					alert_manual(please_select_action, 'dAltMlti');
		}
</script>
<?php
//<<<<<<--------------------Page block templates Ends--------------------//
$categoryfrm->includeFooter();
?>