<?php

class Magreement extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }
	
	function selectQuery($query) {
		return $this->db->query($query);
	}
	
	function get_agreement($condn = null)
	{
		$this->db->select('agreement.*, licencee_masters.*, property_master.*');
		$this->db->from('agreement');
		$this->db->join('licencee_masters', 'agreement.licencee_id = licencee_masters.licencee_id', 'LEFT');
		$this->db->join('property_master', 'agreement.property_id = property_master.property_id', 'LEFT');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('agreement.agreement_id','ASC');
        $query=$this->db->get();
		//echo $this->db->last_query();die;
        return $query;
	}
	
	function get_agreement_schedule($condn = null)
	{
		$this->db->select('agreement_schedule.*');
		$this->db->from('agreement_schedule');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('agreement_schedule.agreement_id','ASC');
        $query=$this->db->get();
        return $query;
	}
	
	public function get_agreement_schedule_list($agreement_id, $sql1 = null) {
        $sql = "SELECT a.*, b.* FROM property_agreement_detail a INNER JOIN attribute_master b ON a.attribute_id = b.attribute_id WHERE a.agreement_id = $agreement_id";
		$sql = !is_null($sql1) ? $sql . " AND " . $sql1 : $sql;
        $result = $this->db->query($sql);

        return $result;
    }
	
	function get_property_attribute($condn = null)
	{
		$this->db->select('property_attribute_detail.*, attribute_master.*');
		$this->db->from('property_attribute_detail');
		$this->db->join('attribute_master', 'property_attribute_detail.attribute_id = attribute_master.attribute_id', 'LEFT');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('property_id','ASC');
        $query=$this->db->get();
        return $query;
	}
	
	function get_agreement_demand($condn = null)
	{
		$this->db->select('agreement.*, agreement_demand.*, licencee_masters.*, property_master.*');
		$this->db->from('agreement_demand');
		$this->db->join('agreement', 'agreement_demand.agreement_id = agreement.agreement_id', 'INNER');
		$this->db->join('licencee_masters', 'agreement.licencee_id = licencee_masters.licencee_id', 'LEFT');
		$this->db->join('property_master', 'agreement.property_id = property_master.property_id', 'LEFT');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('demand_id ASC, schedule_from_date ASC');
        $query=$this->db->get();
        return $query;
	}
	
	function get_agreement_demand_discount($condn = null)
	{
		$this->db->select('agreement_discount_waiver.*');
		$this->db->from('agreement_discount_waiver');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('waiver_id','DESC');
        $query=$this->db->get();
        return $query;
	}
	
	function add_demand_payment_info($data_record) {
		$this->db->insert('agreement_demand_payment', $data_record);
		$insert_id = $this->db->insert_id();
		return $insert_id;
	}
	
	function update_demand_payment_info($data_record, $condn) {
		return $this->db->update('agreement_demand_payment', $data_record, $condn);
	}
	
	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;
	}
	
	function get_demand_payment_info_cron($condn = null)
	{
		$this->db->select('agreement_demand_payment.*, agreement_demand.notice_number');
		$this->db->from('agreement_demand_payment');
		$this->db->join('agreement_demand', 'agreement_demand_payment.demand_id = agreement_demand.demand_id', 'INNER');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('demand_payment_id','DESC');
        $query=$this->db->get();
		//echo $this->db->last_query();die;
        return $query;
	}
	
	function update_agreement_demand($data_record, $condn) {
		return $this->db->update('agreement_demand', $data_record, $condn);
	}
	
	function get_customer_det($condn = null) {
		$this->db->select('customer_master.*');
		$this->db->from('customer_master');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('customer_id','ASC');
        $query=$this->db->get();
        return $query;
	}
	
	function get_licencee_det($condn = null) {
		$this->db->select('licencee_masters.*');
		$this->db->from('licencee_masters');
		foreach ($condn as $key => $value)
			$this->db->where($key, $value);
		$this->db->order_by('licencee_id','ASC');
        $query=$this->db->get();
        return $query;
	}
	
	public function get_demand_waiver($demand_notice_number) {

        $sql = "SELECT SUM(amount) total_waiver FROM agreement_discount_waiver WHERE demand_notice_number = '".$demand_notice_number."' AND is_active = 1 AND status_flag = 9 ";
        $query = $this->db->query($sql);
		$row = $query->row_array();
        return $row;

    }
	
	public function property_type_attribute($property_id) {

        $result = array();

        $sql = "SELECT a.*, b.* FROM property_attribute_detail a INNER JOIN attribute_master b ON a.attribute_id = b.attribute_id WHERE a.property_id = ".$property_id."";
        $query = $this->db->query($sql);
		$rows = $query->result_array();	       

        $i = 0;
        foreach($rows as $row){
            $sql1 = "SELECT * FROM attribute_master_static WHERE attribute_id = ".$row['attribute_id']."";
            $query1 = $this->db->query($sql1);
		    $rows1 = $query1->result_array();

            $result[$i] = $row;
            $result[$i]['attribute_static'] = $rows1;

            $i++;
        }

        //echo "<pre>"; print_r($result); die;

        if($result){
            return $result;
        } else {
            return false;
        }

    }
	
	
	public function get_agreement_demand_payment($condn = array()) {

        //$sql = "SELECT a.*, b.*, c.*, d.*, e.unit_name, e.unit_level FROM agreement_demand_payment a LEFT JOIN agreement_demand b ON a.demand_id = b.demand_id LEFT JOIN agreement c ON b.agreement_id = c.agreement_id LEFT JOIN licencee_masters d ON c.licencee_id = d.licencee_id LEFT JOIN property_unit_master e ON c.property_unit_master_id = e.id ";
		$this->db->select('a.*, b.*, c.*, d.*, e.unit_name, e.unit_level');
		$this->db->from('agreement_demand_payment a');
		$this->db->join('agreement_demand b', 'a.demand_id = b.demand_id', 'LEFT');
		$this->db->join('agreement c', 'b.agreement_id = c.agreement_id', 'LEFT');
		$this->db->join('licencee_masters d', 'c.licencee_id = d.licencee_id', 'LEFT');
		$this->db->join('property_unit_master e', 'c.property_unit_master_id = e.id', 'LEFT');
		if(!empty($condn)){
            $this->db->where($condn);
        }
        $query = $this->db->get();
		//echo $this->db->last_query(); die;
		$row = $query->row_array();

        //echo "<pre>"; print_r($row); die;

        return $row;

    }
	
	function add_cron_job_activity_log($data_record) {
		$this->db->insert('activity_log', $data_record);
		$insert_id = $this->db->insert_id();
		return $insert_id;
	}
	
	function update_demand_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);
		}
	}
	
	public function __get_agreement_demand($demand_id) {

        $sql = "SELECT b.*, c.*, d.*, c.property_unit_master_id AS property_unit_master_id_agreement, e.unit_name, e.unit_level,(SELECT attribute_value FROM property_agreement_detail WHERE property_agreement_detail.agreement_id = c.agreement_id AND attribute_id = 10) AS attribute_value,(SELECT attribute_value FROM property_agreement_detail WHERE property_agreement_detail.agreement_id = c.agreement_id AND attribute_id = 7) AS agreement_start_date,(SELECT attribute_value FROM property_agreement_detail WHERE property_agreement_detail.agreement_id = c.agreement_id AND attribute_id = 8) AS agreement_duration,(SELECT attribute_value FROM property_agreement_detail WHERE property_agreement_detail.agreement_id = c.agreement_id AND attribute_id = 9) AS agreement_end_date, f.property_name, f.address_line_1, f.pincode, p.property_type_name FROM agreement_demand b LEFT JOIN agreement c ON b.agreement_id = c.agreement_id LEFT JOIN licencee_masters d ON c.licencee_id = d.licencee_id LEFT JOIN property_unit_master e ON c.property_unit_master_id = e.id INNER JOIN property_master f ON c.property_id = f.property_id INNER JOIN property_types p ON f.property_type_id = p.id WHERE b.demand_id = ".$demand_id." ";
        $query = $this->db->query($sql);
		$row = $query->row_array();

        //echo "<pre>"; print_r($row); die;

        return $row;

    }
	
	public function get_property_unit_detail($property_unit_master_id) {

        $sql = "SELECT * FROM property_unit_list_view WHERE id = ".$property_unit_master_id." ";
        $query = $this->db->query($sql);
		$row = $query->row_array();

        //echo $sql; die;

        return $row;

    }
	
	public function get_agreement_waiver($agreement_id){
	
		$sql = "SELECT * FROM agreement_discount_waiver WHERE agreement_id = ".$agreement_id."";
        $query = $this->db->query($sql);
		$rows = $query->result_array();
		
		return $rows;
	}
	
	
}