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

class GalleryModel extends CI_Model { 

    public function __construct(){
		parent::__construct();
		$this->load->database();
	}
	
	function get_rows($where)
    {
		$sql = "select * from gallery_tb".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}

	public function insert_row($insert_data)
	{
		$this->db->insert('gallery_tb', $insert_data);
	}

	function delete_row($edit_id)
    {		
		$this->db->delete('gallery_tb', array('id' => $edit_id));
	}
	
	function get_row($id)
    {
		$sql = "select * from gallery_tb where id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_qr_row($id)
    {
        $sql   = "select * from gallery_tb where user_id = '" . $id . "'";
        $query = $this->db->query($sql);

        return $query->result_array();
    }
	
	function get_detail_row($where)
    {
		$sql = "select gallery_tb.*, 
		               site_tb.watermark_file as watermarkfile, 
		               package_history_tb.id as package_history_id,
		               prepackage_history_tb.id as prepackage_history_id
		        from gallery_tb 
		        left join site_tb on gallery_tb.site_id = site_tb.id
		        left join package_tb on package_tb.site_id = gallery_tb.site_id
		        left join package_history_tb on package_tb.id = package_history_tb.package_id
		        left join prepackage_tb on prepackage_tb.package_id = package_tb.id
		        left join prepackage_history_tb on prepackage_tb.id = prepackage_history_tb.prepackage_id
		       ".$where;
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_video_detail_row($id)
    {
		$sql = "select background_tb.*, 
		               gallery_tb.bg_comp_url as bg_comp_url, gallery_tb.orig_comp_detail_url as orig_comp_detail_url,
		               site_tb.watermark_file as watermarkfile, 
		               location_tb.width as width, location_tb.height as height, location_tb.rotate as rotate, location_tb.rate as rate, 
		               package_history_tb.id as package_history_id,
		               prepackage_history_tb.id as prepackage_history_id
		        from background_tb 
		        left join site_tb on background_tb.site_id = site_tb.id
		        left join gallery_tb on gallery_tb.site_id = site_tb.id
		        left join location_tb on background_tb.location_id = location_tb.id
		        left join package_tb on package_tb.site_id = gallery_tb.site_id
		        left join package_history_tb on package_tb.id = package_history_tb.package_id
		        left join prepackage_tb on prepackage_tb.package_id = package_tb.id
		        left join prepackage_history_tb on prepackage_tb.id = prepackage_history_tb.prepackage_id
		        where background_tb.id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}

	public function update_row($update_data, $id)
	{
		$this->db->update('gallery_tb', $update_data, $id);
	}	

	function get_count($where)
    {
		$sql = "select count(id) as cnt from gallery_tb".$where;
		$query = $this->db->query($sql);
		$row = $query->row_array();
		if(isset($row["cnt"]))
			return $row["cnt"];
		else
			return 0;
	}
	
	public function update_album_row($update_data, $id)
	{
		$this->db->update('album_tb', $update_data, $id);
	}
	
	function get_album_rows($where)
    {
// 		$sql = "select 
// 		        album_tb.*, 
// 	            gallery_tb.user_id as user_id, 
// 	            gallery_tb.location_id, 
// 	            background_tb.id as bid, 
// 	            pht.id package_history_id
// 		        from album_tb
// 		        left join gallery_tb on album_tb.gallery_id = gallery_tb.id
// 		        left join background_tb on background_tb.location_id = gallery_tb.location_id
// 		        left join package_history_tb pht on pht.user_id = gallery_tb.user_id
// 		        ".$where;
		        
		        $sql = "select album_tb.*, album_tb.user_id as user_id
		        from album_tb
		        ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function get_kiosk_rows($where)
    {
		$sql = "select gallery_tb.*, gallery_tb.user_id as user_id, gallery_tb.location_id, 0 as bid, 0 package_history_id 
		from gallery_tb 
		".$where;

		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function get_qr_album_row($id)
    {
        $sql   = "select * from album_tb where user_id = '" . $id . "'";
        $query = $this->db->query($sql);

        return $query->result_array();
    }
	
	function get_paid_user_count($where,$token)
    {
          $sql = "select 
            (SELECT count(gt.id) 
            from package_payments_tb pqr
            Inner join guest_tb gt ON gt.id = pqr.guest_id ".$where." and pqr.refund_status != 3)
            +
            (SELECT count(pqt.id) 
            from prepackage_qr_tb pqt 
            INNER join prepackage_tb ppt on ppt.id = pqt.prepackage_id 
            INNER join package_tb pt on pt.id = ppt.package_id 
            where pqt.token = '". $token."' and pt.price > 0)
            +
            (SELECT count(gt.id)
            from guest_tb gt
            INNER join prepackage_history_tb pht on pht.user_id = gt.id ". $where." and pht.price > 0) as paidUserCount";
      
        // print_r($sql);
        // die();
        // $sql = "SELECT count(gt.id) as paidUserCount
        //         from prepackage_qr_tb pqr
        //         Inner join prepackage_tb prt ON pqr.prepackage_id = prt.id
        //         Inner join package_tb pt ON prt.package_id = pt.id
        //         Inner join guest_tb gt ON gt.qr_token LIKE CONCAT('%', pqr.token , '%')
		      //  ".$where;
		        //Inner join guest_tb gt ON gt.qr_token = pqr.token
		        
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    
    function get_paid_user_data($where)
    {
          $sql = "select 
            (SELECT count(gt.id) 
            from package_payments_tb pqr
            Inner join guest_tb gt ON gt.id = pqr.guest_id ".$where.")
            +
            (SELECT count(gt.id)
            from guest_tb gt
            INNER join prepackage_history_tb pht on pht.user_id = gt.id ". $where." and pht.price > 0)";
		        
        //print_r($sql);		        
	    //die();    
        $query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function get_album_row($id)
    {
		$sql = "select * from album_tb where id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_order_row($id)
    {
		$sql = "select comp_attachment from background_tb where location_id = '".$id."' and orderID = 2";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_album_count($where)
    {
		$sql = "select count(album_tb.id) as cnt
		        from album_tb
		        ".$where;
		$query = $this->db->query($sql);
		$row = $query->row_array();
		if(isset($row["cnt"]))
			return $row["cnt"];
		else
			return 0;
	}
	
	function get_cart_album_rows($where)
    {
        $sql = "select process_comp_file_url from album_tb".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}

	function get_background_rows($where)
    {
		$sql = "select background_tb.*, 
		               location_tb.id as location_id, location_tb.category_id as category_id, location_tb.title as title, 
		               location_tb.width as width, location_tb.height as height, location_tb.rate as rate, location_tb.rotate as rotate
				from background_tb
				left join location_tb on background_tb.location_id = location_tb.id
				left join site_tb on background_tb.site_id = site_tb.id
			   ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	public function insertTransaction($data){	
		$insert = $this->db->insert('payg_payments_tb', $data);
		return $insert?true:false;
	}
}