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

class PrepackageModel extends CI_Model { 

    public function __construct(){
		parent::__construct();
		$this->load->database();
	}
	
	function get_rows($where)
    {
		$sql = "select prepackage_tb.*, 
		               package_tb.title, package_tb.type, package_tb.validity, package_tb.image, package_tb.video, package_tb.price, package_tb.discount, package_tb.site_id, package_tb.country_id,
		               site_tb.title as site, 
		               country_tb.title as country, country_tb.symbol as symbol, country_tb.iso_code as iso_code, country_tb.rate as currency_rate,
		               (select COUNT(prepackage_qr_tb.id) from prepackage_qr_tb where isDeleted = 0 and prepackage_id = prepackage_tb.id) as total_count,
		               (select COUNT(prepackage_qr_tb.id) from prepackage_qr_tb WHERE prepackage_qr_tb.isUse = 0 and isDeleted = 0 and prepackage_id = prepackage_tb.id) as used_count
				from prepackage_tb 
				left join package_tb on prepackage_tb.package_id = package_tb.id
				left join site_tb on package_tb.site_id = site_tb.id
				left join country_tb on package_tb.country_id = country_tb.id
				left join prepackage_qr_tb on prepackage_qr_tb.prepackage_id = prepackage_tb.id
				where prepackage_tb.id > 0 and prepackage_tb.isDeleted = 0 group by prepackage_tb.id ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function get_rows_detail($id){
	    $sql = "select * from prepackage_qr_tb where id > 0 and isDeleted = 0 and prepackage_id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->result_array();
	}

	public function insert_row($insert_data)
	{
		$this->db->insert('prepackage_tb', $insert_data);
		
		$sql = "select max(id) as prepackage_id from prepackage_tb";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	public function insert_qr_row($insert_data)
	{
		$this->db->insert('prepackage_qr_tb', $insert_data);
	}
	
	function delete_row($edit_id)
    {		
		$this->db->delete('prepackage_tb', array('id' => $edit_id));
	}
	
	function delete_qr_row($edit_id)
    {		
		$this->db->delete('prepackage_qr_tb', array('id' => $edit_id));
	}
	
	function get_row($id)
    {
		$sql = "select * from prepackage_tb where id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_detail_row($id)
    {
		$sql = "select * from prepackage_qr_tb where id = '".$id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_row_with_token($token)
    {
		$sql = "select * from prepackage_qr_tb where token = '".$token."'";
		$query = $this->db->query($sql);
		return $query->row_array();
	}

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

	function get_purchase_rows($where)
    {
		$sql = "select package_tb.*, 
		               country_tb.symbol as symbol, country_tb.iso_code as currency_code, country_tb.rate as currency_rate,
		               site_tb.package as package 
		        from package_tb
		        left join country_tb on package_tb.country_id = country_tb.id
		        left join site_tb on package_tb.site_id = site_tb.id		        
		       ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}

	function get_prepackage_rows($where)
    {
		$sql = "select prepackage_history_tb.*, 
		               package_tb.title as package, package_tb.type as type, package_tb.validity as validity, package_tb.price as price, 
	                   package_tb.image as image_count, package_tb.video as video_count, 
	                   prepackage_history_tb.image_count as waste_image_count, prepackage_history_tb.video_count as waste_video_count, 
	                   site_tb.title as site, country_tb.symbol as symbol,
	                   prepackage_tb.from_date as from_date, prepackage_tb.to_date as to_date,
	                   prepackage_tb.s_from_date as s_from_date, prepackage_tb.s_to_date as s_to_date,
	                   prepackage_qr_tb.filePath as filePath
				from prepackage_history_tb 
				left join prepackage_tb on prepackage_history_tb.prepackage_id = prepackage_tb.id
				left join prepackage_qr_tb on prepackage_history_tb.token = prepackage_qr_tb.token
				left join package_tb on prepackage_tb.package_id = package_tb.id
				left join site_tb on package_tb.site_id = site_tb.id
				left join country_tb on package_tb.country_id = country_tb.id
				".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}
// 	function get_paid_prepackage_rows($where)
//     {
//         $sql = "select package_tb.title as package, 
//                     package_history_tb.from_date as from_date, 
//                     package_history_tb.to_date as to_date, 
//                     package_history_tb.s_from_date as s_from_date, 
//                     package_history_tb.s_to_date as s_to_date
//                 from package_history_tb 
//                 INNER JOIN package_payments_tb on package_history_tb.user_id = package_payments_tb.guest_id 
//                 INNER JOIN package_tb on package_tb.id = package_history_tb.package_id
//         ". $where;
        
//         $query = $this->db->query($sql);
        
//         return $query->result_array();
//     }

    function get_qrscan_prepackage_rows($where , $token)
    {
    //     $sql = "select DISTINCT pht.*, 
		  //             pt.title as package, 
    //                   prt.from_date as from_date,
    //             	   prt.to_date as to_date,
    //                   prt.s_from_date as s_from_date, 
    //             	   prt.s_to_date as s_to_date
				// from prepackage_tb prt
    //             INNER JOIN package_tb pt ON pt.id = prt.package_id
    //             left JOIN prepackage_history_tb pht ON pht.prepackage_id = prt.id
    //             INNER JOIN prepackage_qr_tb ppt ON ppt.prepackage_id = prt.id
    //     ". $where;
        $sql = "select 
                ppqt.*, 
                pt.title as package, 
                ppt.from_date as from_date, 
                ppt.to_date as to_date, 
                ppt.s_from_date as s_from_date, 
                ppt.s_to_date as s_to_date
                from prepackage_qr_tb ppqt 
                INNER JOIN prepackage_tb ppt ON ppt.id = ppqt.prepackage_id
                INNER JOIN package_tb pt on pt.id = ppt.package_id
                LEFT JOIN prepackage_history_tb ppht on ppht.user_id = '".$token."'
        ". $where;
        
        
        
        // print_r($sql);
        // die();
        
        $query = $this->db->query($sql);

        return $query->result_array();
    }
    
    public function get_latest_token($where) {
        $sql = "select token from prepackage_history_tb " . $where;
        $query = $this->db->query($sql);
        return $query->row_array();
    }
	
	public function insertTransaction($data){	
		$insert = $this->db->insert('package_payments_tb', $data);
		return $insert?true:false;
	}
	
	public function updateTransaction($update_data, $id){
		$this->db->update('package_payments_tb', $update_data, $id);
	}
	
	public function insert_refund_row($refund_data)
	{
		$this->db->insert('refund_package_tb', $refund_data);
	}
	
	function get_refund_rows($where)
    {
        $sql = "select refund_package_tb.id, refund_package_tb.created_at as refund_date, 
                        package_payments_tb.*,
                        package_tb.title as package, package_tb.type,
                        package_history_tb.from_date, package_history_tb.to_date,
                        site_tb.title as site, guest_tb.email as guest
                        from refund_package_tb
                        left join package_history_tb on refund_package_tb.txn_id = package_history_tb.txn_id
                        left join package_payments_tb on refund_package_tb.txn_id = package_payments_tb.txn_id
                        left join package_tb on package_history_tb.package_id = package_tb.id
                        left join site_tb on package_tb.site_id = site_tb.id
                        left join guest_tb on guest_tb.id = package_history_tb.user_id
                ".$where;  
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function get_payment_id($txn_id)
    {
		$sql = "select payment_id from package_payments_tb where txn_id = '".$txn_id."'";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		return $row["payment_id"];
    }
    
    function get_guest_id($where)
    {
		$sql = "select guest_id from package_payments_tb".$where;
		$query = $this->db->query($sql);
		$row = $query->row_array();
		return $row["guest_id"];
    }
    
    function get_package_row($payment_id){
        $sql = "select txn_id, payment_gross from package_payments_tb where payment_id = '".$payment_id."'";
		$query = $this->db->query($sql);
		return $query->row_array();
    }
    
    function get_timezone($package_id)
    {
		$sql = "select country_tb.timezone as timezone from package_tb
		        left join country_tb on package_tb.country_id = country_tb.id
		        where package_tb.id = '".$package_id."'";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		return $row["timezone"];
    }
    
    function get_txn_id()
    {
		$sql = "select txn_id from package_payments_tb order by payment_id desc limit 1";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		return $row["txn_id"];
    }
    
    public function insert_prepackage_history_row($insert_data)
	{
		$insert = $this->db->insert('prepackage_history_tb', $insert_data);
		return $insert?true:false;
	}
}