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

class Tables extends CI_Controller {

	public function __construct()
	{
		parent::__construct();
	}

	public function browse_locale($table,$text_table,$table_id,$locale)
	{
		if(!$this->admin_auth->has_access($table,'browse')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$this->db->save_queries = false;
		$sort          = $this->input->post('sort',TRUE);
		$dir           = $this->input->post('dir',TRUE);
		$limit         = $this->input->post('limit',TRUE);
		$offset        = $this->input->post('start',TRUE);
		$filter        = $this->input->post('filter',TRUE);
		$custom_field  = $this->input->post('custom_field',TRUE);
		$custom_value  = $this->input->post('custom_value',TRUE);
		// $select_fields = $this->input->post('select_fields',TRUE)? $this->input->post('select_fields',TRUE) : '*';
		if($this->input->post('select_fields',TRUE))
		{
			$helper_array1 = explode(',', $this->input->post('select_fields',TRUE));
			$helper_array2 = array();
			for($i=0;$i<sizeof($helper_array1);$i++)
			{
				$helper_array3   = explode('.', $helper_array1[$i]);
				$helper_array2[] = sizeof($helper_array3)==2? '`'.$helper_array3[0].'`.`'.$helper_array3[1].'`' : '`'.$helper_array1[$i].'`';
			}
			$select_fields = implode(",", $helper_array2);
		}
		else
		{
			$select_fields = '*';
		}

		$sql = $this->_sql_query($sort,$dir,$limit,$offset,$filter,$custom_field,$custom_value,$locale);	
		
		$query = $this->db->query('Select '.$select_fields.' From '.$table.' Left Join '.$text_table.' On '.$table.'.'.$table_id.'='.$text_table.'.'.$table_id.$sql['sql'],$sql['vals']);
		if(!$query){
			message_handler(FALSE,'Sql error',$this->security->get_csrf_hash());
		}
		$result['total']   = $query->num_rows();

		$query = $this->db->query('Select '.$select_fields.' From '.$table.' Left Join '.$text_table.' On '.$table.'.'.$table_id.'='.$text_table.'.'.$table_id.$sql['sql'].$sql['extra'],$sql['vals']);
		if(!$query){
			message_handler(FALSE,'Sql error',$this->security->get_csrf_hash());
		}
		$result['data'] = $query->result_array();
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['success'] = TRUE;
		
		echo json_encode($result);
	}
	
	public function create_locale($table,$table_id,$text_table,$locale)
	{
		if(!$this->admin_auth->has_access($table,'insert')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$data = json_decode($this->input->post('data',TRUE));
		$this->db->query($this->db->insert_string($table,$data));
		$id = $this->db->insert_id();
		
		$query = $this->db->query('Select * From locales');
		$locales = $query->result_array();
		foreach($locales as $l)
		{
			$this->db->query('Insert into '.$text_table.' (locale,'.$table_id.') Values (?,?)',array($l['locale_code'],$id));
		}

		$query = $this->db->query('Select * From '.$table.' Left Join '.$text_table.' On '.$table.'.'.$table_id.' = '.$text_table.'.'.$table_id.' Where '.$table.'.'.$table_id.' = ? And locale = ?',array($id,$locale));

		$result['success'] = TRUE;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['data']    = $query->row_array();
		echo json_encode($result);
	}

	public function delete_locale($table,$table_id,$text_table)
	{
		if(!$this->admin_auth->has_access($table,'delete')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$id = json_decode($this->input->post('data',TRUE));
		$this->db->query('Delete from '.$table.' Where '.$table_id.' = ?',array($id));
		$this->db->query('Delete from '.$text_table.' Where '.$table_id.' = ?',array($id));
		
		$result['success'] = true;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['data'] = array();
		echo json_encode($result);
	}
	
	public function browse($table)
	{
		if(!$this->admin_auth->has_access($table,'browse')){
			message_handler(FALSE,'Access Denied','');
		}

		$this->db->save_queries = false;
		$sort          = $this->input->post('sort',TRUE);
		$dir           = $this->input->post('dir',TRUE);
		$limit         = $this->input->post('limit',TRUE);
		$offset        = $this->input->post('start',TRUE);
		$filter        = $this->input->post('filter',TRUE);
		$custom_field  = $this->input->post('custom_field',TRUE);
		$custom_value  = $this->input->post('custom_value',TRUE);
		// $select_fields = $this->input->post('select_fields',TRUE)? $this->input->post('select_fields',TRUE) : '*';
		if($this->input->post('select_fields',TRUE))
		{
			$helper_array1 = explode(',', $this->input->post('select_fields',TRUE));
			$helper_array2 = array();
			for($i=0;$i<sizeof($helper_array1);$i++)
			{
				$helper_array3   = explode('.', $helper_array1[$i]);
				$helper_array2[] = sizeof($helper_array3)==2? '`'.$helper_array3[0].'`.`'.$helper_array3[1].'`' : '`'.$helper_array1[$i].'`';
			}
			$select_fields = implode(",", $helper_array2);
		}
		else
		{
			$select_fields = '*';
		}		

		$sql   = $this->_sql_query($sort,$dir,$limit,$offset,$filter,$custom_field,$custom_value);
		$query = $this->db->query('Select '.$select_fields.' From '.$table.$sql['sql'],$sql['vals']);
		if(!$query){
			message_handler(FALSE,'Sql error',$this->security->get_csrf_hash());
		}
		$result['total']   = $query->num_rows();
		$query = $this->db->query('Select '.$select_fields.' From '.$table.$sql['sql'].$sql['extra'],$sql['vals']);
		if(!$query){
			message_handler(FALSE,'Sql error',$this->security->get_csrf_hash());
		}
		$result['data'] = $query->result_array();
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['success'] = TRUE;

		echo json_encode($result);
	}
	
	public function create($table,$table_id)
	{
		if(!$this->admin_auth->has_access($table,'insert')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$data = json_decode($this->input->post('data',TRUE));
		$this->db->query($this->db->insert_string($table,$data));
		$query = $this->db->query('Select * from '.$table.' Where '.$table_id.' = ?',array($this->db->insert_id()));
		$result['success'] = true;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['data'] = $query->row_array();
		echo json_encode($result);
	}
	
	public function destroy($table,$table_id)
	{
		if(!$this->admin_auth->has_access($table,'delete')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$data = json_decode($this->input->post('data',TRUE));
		$query = $this->db->query('Delete from '.$table.' Where '.$table_id.' = ?',array($data));
		$result['success'] = true;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['data'] = array();
		echo json_encode($result);
	}
	
	public function update($table,$table_id)
	{
		if(!$this->admin_auth->has_access($table,'edit')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$admin_us      = '';
		$admin_pa      = '';
		$params        = array(json_decode($this->input->post("data",FALSE)));
		$do_update     = TRUE;
		$select_fields = $this->input->post("select_fields",TRUE)? $this->input->post("select_fields",TRUE) : '*';

		for($cnt=0;$cnt<count($params);$cnt++)
		{
			$ups = array();
			$upv = array();
			foreach($params[$cnt] as $p => $value )
			{
				if($p!=$table_id)
				{
					switch($p)
					{
						case 'username':
							$admin_us = $this->security->xss_clean($value);
						break;
						case 'password':
							$admin_pa        = $this->security->xss_clean($value);
							$hashed_admin_pa = $this->encryption->encrypt($admin_pa);

							$ups[] = $p." = ?";
							$upv[] = $hashed_admin_pa;
						break;
						default:
							$ups[] = $p." = ?";
							$upv[] = $this->security->xss_clean($value);
						break;
					}
				}
				else
				{
					$id = $this->security->xss_clean($value);
				}
			}
			if($admin_us)
			{
				if($this->admin_auth->username_unique($admin_us,$id))
				{
					$ups[] = " username = ?";
					$upv[] = $admin_us;
				}
				else
				{
					$do_update = FALSE;	
				}
			}
			$upv[] = $id;
			
			if($do_update)
			{
				$this->db->query('Update '.$table.' set '.join(',',$ups).' Where '.$table_id.' = ? ',$upv);
			}
		}

		$query=$this->db->query('Select '.$select_fields.' from '.$table.' where '.$table_id.' = ?',array($id));
		$result['success'] = true;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		$result['data'] = $query->row_array();
		echo json_encode($result);
	}
	
	public function reorder($table,$table_id)
	{
		if(!$this->admin_auth->has_access($table,'edit')){
			message_handler(FALSE,'Access Denied','');
		}
		
		$data = json_decode($this->input->post('data',TRUE));
		$page = $this->input->post('page',TRUE);
		
		$counter = 1;
		foreach($data as $row)
		{			
			foreach($row as $p => $v )
			{
				if($p == $table_id){
					$id = $v;
				}
			}
			
			$this->db->query('Update '.$table.' set sequence = ? Where '.$table_id.' = ?',array(($counter+$page),$id));
			$counter++;
		}
		$result['success'] = true;
		$result['csrf_token'] = $this->security->get_csrf_hash();
		echo json_encode($result);
	}

	public function get_menus($locale)
	{
		if(!$this->admin_auth->has_access('menus','edit')){
			message_handler(FALSE,'Access Denied','');
		}

		$content_type_id = $this->input->post('content_type_id',TRUE);

		$query = $this->db->query('Select ct.content_id, ct.content_text_title From contents c Left Join content_texts ct On c.content_id = ct.content_id Where content_type_id = ? And locale = ?',array($content_type_id,$locale));
		$data  = $query->result_array();
		for($i=0;$i<sizeof($data);$i++)
		{ 
			$data[$i]['content_text_title'] = strip_tags($data[$i]['content_text_title']);
		}
		$result = array(
			'success'     => true
			,'csrf_token' => $this->security->get_csrf_hash()
			,'data'       => $data
		);
		echo json_encode($result);
	}

	public function send_participation_visible_email()
	{
		if(!$this->admin_auth->has_access('participations','edit')){
			message_handler(FALSE,'Access Denied','');
		}
		$locale        = 'gr';
		$participation = $this->db->query('Select * From participations p Left Join users u On p.user_id = u.user_id Where p.is_visible = 1 And id = ?',array($this->input->get('id',TRUE)))->row_array();
		
		if(sizeof($participation)>0)
		{
			send_mail('participation_visible', $this->config->item('email_from'), $participation['user_email'], $participation);

			message_handler(TRUE,'Email Sent successfully!','');
		}
		else
		{
			message_handler(FALSE,'Participation not Found!','');
		}
		echo json_encode($result);
	}

	private function _sql_query($sort,$dir,$limit,$offset,$filter,$custom_field,$custom_value,$locale='')
	{
		$result['sql']    = '';
		$result['extra']  = '';
		$result['vals']   = array();
		$args             = array();
		$vals             = array();

		if($locale)
		{
			$args[] = ' locale = ? ';
			$vals[] = $locale;
		}
		if($sort)
		{
			$helper_array1 = explode(',', $sort);
			$helper_array2 = array();
			for($i=0;$i<sizeof($helper_array1);$i++)
			{
				$helper_array3   = explode('.', $helper_array1[$i]);
				$helper_array2[] = sizeof($helper_array3)==2? '`'.$helper_array3[0].'`.`'.$helper_array3[1].'`' : '`'.$helper_array1[$i].'`';
			}
			$sort_fields = implode(",", $helper_array2);
			if($sort_fields)
			{
				$result['extra'] .= " Order By ".$sort_fields." ".$dir;
			}
		} 
		if($limit)
		{
			$result['extra'] .=" Limit ".intval($limit);	
		} 
		if($offset)
		{
			$result['extra'] .=" Offset ".intval($offset);
		}

		if(strlen($custom_field)>0 && strlen($custom_value)>0)
		{
			$helper_array1 = explode(',', $custom_field);
			$helper_array2 = explode(',', $custom_value);
			foreach ($helper_array1 as $ha1)
			{
				$helper_array3 = explode('.', $ha1);
				$args[]       = sizeof($helper_array3)==2? ' `'.$helper_array3[0].'`.`'.$helper_array3[1].'` = ? ' : ' `'.$ha1.'` = ?';
			}
			foreach ($helper_array2 as $ha2)
			{
				$vals[] = $ha2;
			}
		}

		if($filter)
		{
			$filters = json_decode($filter);
			foreach($filters as $f)
			{
				switch($f->type)
				{
					case 'string':
						switch ($f->field)
						{
							case 'user_id':
								$args_new = array();
								$query    = $this->db->query('Select user_id From users Where user_email LIKE "%'.$f->value.'%"');
								$qr       = $query->result_array();
								foreach($qr as $r)
								{
									$args_new[] = " user_id = ? ";
									$vals[]     = $r['user_id'];
								}
								if(sizeof($args_new)>0)
								{
									$args[] = ' ('.implode(" OR ",$args_new).') ';
								}
								else
								{
									$args[] = ' (user_id = ?) ';
									$vals[] = 0;
								}
								
							break;
							case 'gallery_id':
								$args_new  = array();
								$galleries = array('Cheese','Ketchup');
								for($i=0;$i<sizeof($galleries);$i++)
								{ 
									if(substr($galleries[$i], 0, strlen($f->value)) === $f->value)
									{
										$args_new[] = " gallery_id = ? ";
										$vals[]     = $i+1;
									}
								}
								if(sizeof($args_new)>0)
								{
									$args[] = ' ('.implode(" OR ",$args_new).') ';
								}
								else
								{
									$args[] = ' (gallery_id = ?) ';
									$vals[] = 0;
								}
							break;
							default:
								$args[] = ' '.$f->field.' REGEXP ? ';
								$vals[] = $f->value;
							break;
						}
					break;
					case 'boolean':
						if($f->value)
						{
							$args[] = " ".$f->field." = ? ";
							$vals[] = 1;
						}
						else
						{
							$args[] = " ".$f->field." = ? ";
							$vals[] = 0;
						}
					break;
					case 'date':
						$args[] = " ".$f->field." ".$this->_comp_date($f->comparison)." ? ";
						list($month, $day, $year) = explode('/', $f->value);
						$vals[] = $year.'-'.$month.'-'.$day;
					break;
					case 'numeric':
						$args[] = " ".$f->field." ".$this->_comp($f->comparison)." ? ";
						$vals[] = $f->value;
					break;
				}
			}
		}
		if(count($args))
		{
			$result['sql'] = ' Where '.implode(" AND ",$args);
		}
		$result['vals'] = $vals;
		return $result;
	}
	
	private function _comp($arg)
	{
		switch($arg)
		{
			case "gt":
			return " > ";
			break;
			case "lt":
			return " < ";
			break;
			case "eq":
			return " = ";
			break;
		}
	}
	
	private function _comp_date($arg)
	{
		switch($arg)
		{
			case "gt":
			return " > ";
			break;
			case "lt":
			return " < ";
			break;
			case "eq":
			return " REGEXP ";
			break;
		}
	}	
}
/* End of file admin/tables.php */
/* Location: ./system/application/controllers/admin/tables.php */