<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Issue_modal
 *
 * @author user
 */
class Issue_modal extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function exit_issue($recno) {
        $this->db->select();
        $this->db->from('issue');
        $this->db->where('recno', $recno);
        $query = $this->db->get();
        $issue = $query->first_row('array');
        return $issue;
    }

    public function add_issue($data) {
        $this->db->insert('issue', $data);
    }

    public function add_item_issue($data) {
        $this->db->insert('item_issue', $data);
    }

    public function edit_issue($recno, $data) {
        $this->db->where('recno', $recno);
        $this->db->update('issue', $data);
    }

    public function delete_issue($data) {
        $this->db->delete('issue', $data);
    }

    public function delete_item_issue($data) {
        $this->db->delete('item_issue', $data);
    }

    public function get_max_receipt_no() {
        $this->db->select_max('recno');
        $query = $this->db->get('issue');
        return $query->result();
    }

    public function search_issue_with_limit($limit, $recno) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('issue.recno', $recno);
        $this->db->order_by("issue.recno", "desc");
        $this->db->limit($limit);
        $query = $this->db->get();
        return $query->result();
    }

    public function search_issue_with_limit_count($recno) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('issue.recno', $recno);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function search_issue_item_with_limit($recno) {
        $this->db->select();
        $this->db->from('item_issue');
        $this->db->where('recno', $recno);
        $query = $this->db->get();
        return $query->result();
    }

    public function search_issue_without_limit($recno) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('recno', $recno);
        $this->db->order_by("issue.recno", "desc");
        $query = $this->db->get();
        return $query->result();
    }

    public function get_my_issue_list($limit) {
        $this->db->select();
        $this->db->from('issue');
        $this->db->limit($limit);
        $query = $this->db->get();
        return $query->result();
    }

    public function get_my_issue_list_with_limit($limit, $username) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->limit($limit);
        $this->db->order_by("issue.recno", "desc");
        $this->db->where('issue.username', $username);
        $query = $this->db->get();
        return $query->result();
    }

    public function get_my_issue_list_with_limit_count($username) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('issue.username', $username);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function get_my_issue_list_without_limit($username) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('issue.username', $username);
        $this->db->order_by("issue.recno", "desc");
        $query = $this->db->get();
        return $query->result();
    }

    public function get_issue_by_id_list($recno) {
        $this->db->select('issue.username,issue.recno,issue.season,issue.farmerno,issue.name,issue.centre,issue.date,issue.total,centre.area');
        $this->db->from('issue');
        $this->db->join('centre', 'centre.name = issue.centre');
        $this->db->where('recno', $recno);
        $query = $this->db->get();
        return $query->result();
    }
    
    public function search_issue_by_farmer_date($farmerno, $fromdate, $todate) {
        $this->db->select('issue.recno, issue.farmerno, issue.date, item_issue.item, item_issue.desc,item_issue.qty, item_issue.rate, item_issue.val');
        $this->db->from('item_issue');
        $this->db->join('issue', 'item_issue.recno = issue.recno');
        $this->db->where('issue.farmerno', $farmerno);
        $this->db->where('issue.date >=', $fromdate);
        $this->db->where('issue.date <=', $todate);
        $this->db->order_by("issue.recno", "desc");
        $query = $this->db->get();
        return $query->result();
    }
    
    public function search_issue_by_farmer_date_total($farmerno, $fromdate, $todate) {
        $this->db->select_sum('issue.total');
        $this->db->where('issue.farmerno', $farmerno);
        $this->db->where('issue.date >=', $fromdate);
        $this->db->where('issue.date <=', $todate);
        $query = $this->db->get('issue');

        return $query->result();
    }

    public function search_issue_by_center_date($center, $fromdate, $todate) {
        $this->db->select('issue.recno, issue.farmerno, issue.date, item_issue.item, item_issue.desc,item_issue.qty, item_issue.rate, item_issue.val');
        $this->db->from('item_issue');
        $this->db->join('issue', 'item_issue.recno = issue.recno');
        $this->db->where('issue.centre', $center);
        $this->db->where('issue.date >=', $fromdate);
        $this->db->where('issue.date <=', $todate);
        $this->db->order_by("issue.recno", "desc");
        $query = $this->db->get();
        return $query->result();
    }
    
    public function search_issue_by_center_date_total($center, $fromdate, $todate) {
        $this->db->select_sum('issue.total');
        $this->db->where('issue.date >=', $fromdate);
        $this->db->where('issue.date <=', $todate);
       $this->db->where('issue.centre', $center);
        $query = $this->db->get('issue');

        return $query->result();
    }

    public function get_payemnt_result($farmerno, $date) {
        $this->db->select();
        $this->db->from('payment');
        $this->db->where('farmerno', $farmerno);
        $this->db->where("DATE_FORMAT(start,'%Y/%m/%d') <= ", $date);
        $this->db->where("DATE_FORMAT(end,'%Y/%m/%d') >= ", $date);
        $query = $this->db->get();
        return $query->result();
    }

    public function get_area($center) {
        $this->db->select('area');
        $this->db->from('centre');
        $this->db->where('name', $center);
        $query = $this->db->get();
        $center = $query->first_row('array');
        return $center['area'];
    }
    
    public function get_user_type($username) {
        $this->db->select('type');
        $this->db->from('z_mobile_user');
        $this->db->where('username', $username);
        $query = $this->db->get();
        $center = $query->first_row('array');
        return $center['type'];
    }
    
     public function get_my_supervisor($username,$supervisor_username) {
        $this->db->select();
        $this->db->from('z_supervisor_executive');
        $this->db->where('executive', $username);
        $this->db->where('supervisor', $supervisor_username);
        $query = $this->db->get();
        return $query->result();
    }

}
