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

class Magreement extends CI_Model {

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

    }

    public function getRecords($where = array()){
        $this->db->select('a.allotment_id, a.industrial_park_id, a.property_type, a.plot_module_id, a.lessee_id, a.agreement_type, a.application_date, a.application_money, a.app_money_payment_mode, a.allotment_date, a.allotment_money, a.cgst_percent, a.sgst_percent, a.gst_percent, a.cgst_amt, a.sgst_amt, a.gst_amt, a.payable_amount, a.txnid, a.order_id, a.payment_status, a.created_at, a.created_by, a.updated_at, a.updated_by, b.park_name, IF(a.property_type = "Plot",c.plot_ref_no,d.module_ref_no) AS ref_no, IF(a.property_type = "Plot",c.total_area,d.total_area) AS total_area, IF(a.property_type = "Plot",c.uom_id,d.uom_id) AS uom_id, e.name, ag.agreement_number, ag.is_active AS agreement_status');
        $this->db->from('allotment a');
		$this->db->join('industrial_park_master b', 'a.industrial_park_id = b.industrial_park_id', 'LEFT');
		$this->db->join('plot_header c', 'a.plot_module_id = c.plot_header_id', 'LEFT');
		$this->db->join('module_master d', 'a.plot_module_id = d.module_id', 'LEFT');
		$this->db->join('register_lease_master e', 'a.lessee_id = e.register_lessee_id', 'LEFT');
		$this->db->join('agreement ag', 'a.allotment_id = ag.allotment_id', 'LEFT');
        if(!empty($where)){
            $this->db->where($where);
        }
		$this->db->order_by('DATE(a.created_at)','DESC');
        $query=$this->db->get();
		//echo nl2br($this->db->last_query());die;
        return $query->result_array();
    }
	
	public function getDetails($where = array()){
        $this->db->select('a.allotment_id, a.industrial_park_id, a.property_type, a.plot_module_id, a.lessee_id, a.agreement_type, a.application_date, a.application_money, a.app_money_payment_mode, a.allotment_date, a.allotment_money, a.cgst_percent, a.sgst_percent, a.gst_percent, a.cgst_amt, a.sgst_amt, a.gst_amt, a.payable_amount, a.payment_status, a.created_at, a.created_by, a.updated_at, a.updated_by, b.park_name, IF(a.property_type = "Plot",c.plot_ref_no,d.module_ref_no) AS ref_no, IF(a.property_type = "Plot",c.total_area,d.total_area) AS total_area, IF(a.property_type = "Plot",c.uom_id,d.uom_id) AS uom_id, e.register_lessee_type, e.name, e.address, e.gst_no, e.primary_contact_person, e.primary_designation, e.primary_email, e.primary_contact_no1, e.primary_contact_no2, e.secondary_contact_person, e.secondary_designation, e.secondary_email, e.secondary_contact_no1, e.secondary_contact_no2, h.payment_date, h.order_id, h.payment_mode, h.amount AS paid_amount, h.status, ag.agreement_id, ag.type');
        $this->db->from('allotment a');
		$this->db->join('industrial_park_master b', 'a.industrial_park_id = b.industrial_park_id', 'LEFT');
		$this->db->join('plot_header c', 'a.plot_module_id = c.plot_header_id', 'LEFT');
		$this->db->join('module_master d', 'a.plot_module_id = d.module_id', 'LEFT');
		$this->db->join('register_lease_master e', 'a.lessee_id = e.register_lessee_id', 'LEFT');
		$this->db->join('allotment_payment_info h', 'a.allotment_id = h.allotment_id', 'LEFT');
		$this->db->join('agreement ag', 'a.allotment_id = ag.allotment_id', 'LEFT');
        if(!empty($where)){
            $this->db->where($where);
        }
        $query=$this->db->get();
		//echo $this->db->last_query();die;
        return $query->row_array();
    }
	
	public function getDemandsFromYearRange($agreement_id = 0, $yearFrom = 0, $yearTo = 0)
	{
		$sql = "SELECT a.demand_id, a.schedule_id, a.agreement_id, a.lessee_id, a.notice_number, a.notice_date, a.schedule_from_date, a.schedule_to_date, a.schedule_amount, a.schedule_due_date, a.balance_bf, a.penalty_amount, a.cgst_rate, a.cgst_amount, a.sgst_rate, a.sgst_amount, a.gst_rate, a.gst_amount, a.net_payable_amount, a.is_publish, a.is_active, a.created_at, a.published_by, a.updated_at, a.remarks, a.demand_type, a.payment_status, a.order_id, a.txnid 
				FROM agreement_demand a
				WHERE a.agreement_id = '$agreement_id' AND YEAR(schedule_from_date) BETWEEN '$yearFrom' AND '$yearTo' ";
				
		$query = $this->db->query($sql);
		//echo $this->db->last_query(); die;
        return $query->result_array();
	}
	
	public function agreement_file_submit($data){        
        $this->db->insert('agreement_files', $data);
        return $this->db->insert_id();
    }
	
	public function get_allotment_payment_info($condn = null)
	{
		$this->db->select('allotment_payment_info.*, allotment.*');
		$this->db->from('allotment_payment_info');
		$this->db->join('allotment', 'allotment_payment_info.allotment_id = allotment.allotment_id', 'LEFT');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('payment_id','DESC');
        $query=$this->db->get();
        return $query;
	}
	
	public function update_allotment_payment_to_failed($order_id) {
		$sql = "INSERT INTO allotment_payment_info_failed SELECT * FROM allotment_payment_info WHERE order_id = '".$order_id."' ";
		$rs = $this->db->query($sql);
		if($rs){
			$sql_del = "DELETE FROM allotment_payment_info WHERE order_id = '".$order_id."' ";
			$rs_del = $this->db->query($sql_del);
		}
	}
	
	public function get_demand_payment_info($condn = null)
	{
		$this->db->select('agreement_demand_payment.*, agreement_demand.*');
		$this->db->from('agreement_demand_payment');
		$this->db->join('agreement_demand', 'agreement_demand_payment.demand_id = agreement_demand.demand_id', 'LEFT');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('demand_payment_id','DESC');
        $query=$this->db->get();
        return $query;
	}
	
	public function update_demand_payment_to_failed($order_id) {
		$sql = "INSERT INTO agreement_demand_payment_failed SELECT * FROM agreement_demand_payment WHERE order_id = '".$order_id."' ";
		$rs = $this->db->query($sql);
		if($rs){
			$sql_del = "DELETE FROM agreement_demand_payment WHERE order_id = '".$order_id."' ";
			$rs_del = $this->db->query($sql_del);
		}
	}

}