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

class PackageModel extends CI_Model { 

    public function __construct(){
		parent::__construct();
		$this->load->database();
	}
	
	function get_rows($where)
    {
		$sql = "select package_tb.*, 
		               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 
				from package_tb 
				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 package_tb.id > 0 and package_tb.isDeleted = 0 ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}

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

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

	public function update_row($update_data, $id)
	{
		$this->db->update('package_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
				left join guest_tb on guest_tb.site_id = site_tb.id
		       ".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}

	function get_package_rows($where)
    {
		$sql = "select package_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, 
	                   package_history_tb.image_count as waste_image_count, package_history_tb.video_count as waste_video_count, 
	                   site_tb.title as site, country_tb.symbol as symbol, 
	                   package_payments_tb.payment_gross as payment_amount, package_payments_tb.refund_status as refund_status
				from package_history_tb 
				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 country_tb on package_tb.country_id = country_tb.id
				left join package_payments_tb on package_history_tb.txn_id = package_payments_tb.txn_id
				".$where;
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
// 	function get_qrscan_package_rows($where)
//     {
// 	   $sql = "SELECT 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_history_tb pht
//                     INNER JOIN prepackage_tb prt on pht.prepackage_id = prt.id
//                     INNER JOIN package_tb pt on pt.id = prt.package_id
//                     INNER JOIN prepackage_qr_tb pqt on pqt.token = pht.token
//                 " . $where;
       
//         $query = $this->db->query($sql);

//         return $query->result_array();
//     }

    function get_paid_package_rows($where)
    {
        $sql = "SELECT DISTINCT pht.*, 
                pt.title as package,     
                pht.from_date as from_date, 
                pht.to_date as to_date, 
                pht.s_from_date as s_from_date, 
                pht.s_to_date as s_to_date,
                ppt.refund_status as refund_status
            from package_history_tb pht
            INNER JOIN package_tb pt on pt.id = pht.package_id
            INNER JOIN package_payments_tb ppt on ppt.guest_id = pht.user_id and ppt.package_id = pt.id
                " . $where;

        $query = $this->db->query($sql);

        return $query->result_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"];
    }
    
    function get_site_info_row($where)
    {		
		$sql = "select * from package_history_tb".$where;
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	function get_site_row($where)
    {		
		$sql = "select site_tb.title as site_name 
		        from package_tb
		        left join site_tb on package_tb.site_id = site_tb.id
		        ".$where;
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	
	public function insert_history_row($insert_data)
	{
		$this->db->insert('package_history_tb', $insert_data);
		return array('status' => 200,'message' => 'You has bought package successfully.');
	}
}