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

class TestModel extends CI_Model {

	public function popular_merchants(){
		//include total reviews, sum of reviews, mp_id, unit, building, street, city name, region name, merchant name, merchant image, date and time of application approval, sub category
		$sql = "(SELECT 
					SUM(gr.rev_rate) AS sum_rev, COUNT(gr.rev_rate) AS total_rev, gr.mp_id AS ref_id,mp.mp_unit AS unit_add,mp.mp_building AS building_add,mp.mp_street AS street_add,ac.city_name AS merchant_city_name,ar.region_name AS merchant_region_name,ma.ma_merchant_name AS merchant_name,ma.ma_application_datetime AS datetime_approve, cs.cs_description AS merchant_sub_cat, gml.logo_name AS merchant_image
				FROM 
					gl_review gr
				INNER JOIN gl_merchant_profile mp ON gr.mp_id = mp.mp_id
				INNER JOIN gl_merchant_application ma ON mp.ma_id = ma.ma_id
				INNER JOIN gl_address_city ac ON mp.city_id = ac.city_id
				INNER JOIN gl_address_region ar ON mp.region_id = ar.region_id
                INNER JOIN gl_category_sub cs ON ma.cs_id = cs.cs_id
                INNER JOIN gl_merchant_logo gml ON gml.mp_id = mp.mp_id
                AND ma.pub_id= 1
				GROUP BY ref_id
				ORDER BY sum_rev DESC, datetime_approve ASC
				LIMIT 12)";
		$q = $this->db->query($sql);
		return ($q->num_rows() > 0) ? $q->result() : false;
	}

	public function insert_lang($lang_name,$lang_flag){
		$this->db->set('lang_name', $lang_name);
		$this->db->set('lang_flag', $lang_flag);
		$this->db->insert('language');
		return ($this->db->affected_rows() > 0) ? true : false;
	}

	public function check_lang($lang_name){
		$this->db->where('lang_name', $lang_name);
		$q = $this->db->get('language');
		return ($q->num_rows() > 0) ? true : false;
	}

	public function list_lang(){
		$q = $this->db->get('language');
		return ($q->num_rows() > 0) ? $q->result_array() : false;
	}

	public function delete_lang($lang_id){
		$this->db->where('lang_id', $lang_id);
		$this->db->delete('language'); 
		return ($this->db->affected_rows() > 0) ? true : false;
	}

	public function bookmarks($ui_id){

		$sql = "SELECT 
				CONCAT_WS('-', REPLACE(`ma`.`ma_merchant_name`, ' ', '-'), REPLACE(`ac`.`city_name`, ' ', '-')) AS `merchant_linkname`,
					`mp`.`mp_id` AS `merchant_prof_id`, `ma`.`ma_id` AS `merchant_app_id`, `ma`.`ma_merchant_name` AS `merchant_name`, `ma`.`cm_id` AS `merchant_main_cat_id`, 
					`cm`.`cm_category` AS `merchant_main_cat_text`, `ma`.`cs_id` AS `merchant_sub_cat_id`, `cs`.`cs_description` AS `merchant_sub_cat_text`, 
					`ma`.`ma_email` AS `merchant_email`, `ma`.`ma_contact_number` AS `merchant_contact_number`, `ma`.`ma_status` AS `merchant_status`,
					`bk`.book_id AS `book_id`

				FROM `gl_bookmark` `bk`
				LEFT JOIN `gl_merchant_profile` `mp` ON `mp`.`mp_id` = `bk`.`mp_id`
				LEFT JOIN `gl_merchant_application` `ma` ON `ma`.`ma_id` = `mp`.`ma_id`
				LEFT JOIN `gl_address_city` `ac` ON `ac`.`city_id` = `mp`.`city_id`
				LEFT JOIN `gl_category_main` `cm` ON `cm`.`cm_id` = `ma`.`cm_id`
				LEFT JOIN `gl_category_sub` `cs` ON `cs`.`cs_id` = `ma`.`cs_id`
				LEFT JOIN `gl_address_region` `ar` ON `ar`.`region_id` = `mp`.`region_id`

				WHERE `bk`.`ui_id` = '".$ui_id."' ";

		$q = $this->db->query($sql);
		return ($q->num_rows() > 0) ? $q->result() : false;
	}

  	public function check_bookmark($ui_id , $mp_id){
		
		 $sql = "SELECT * FROM `gl_bookmark`
		WHERE `ui_id` = '".$ui_id."' AND  `mp_id` = '".$mp_id."'";
		$q = $this->db->query($sql);
		return ($q->num_rows() > 0) ? true : false;
	}	

	public function add_bookmark($data) {
		return ($this->db->insert('gl_bookmark',$data))?$this->db->insert_id():false;
	}

	public function delete_bookmark($ui_id , $book_id){
		$sql = "DELETE  FROM `gl_bookmark`
				WHERE `ui_id` = '".$ui_id."' AND  `book_id` = '".$book_id."'";
		$q = $this->db->query($sql);
		return ($this->db->affected_rows() > 0) ? true : false;
	}


}