<?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 incomecategorymodel
 *
 * @author BusyMan
 */
class BalancesetupModel extends CI_Model {
    //put your code here
    private $_tbl_EX = 'tbl_expense';
    private $_tbl_SAEX = 'tbl_salary_expense';
    private $_tbl_AC = 'tbl_land_account';
   private $_tbl_EAC = 'tbl_extra_account';
    public function getTotalExpense()
    {
        $query=$this->db->select_sum('expenseAmount')
        				->get_where($this->_tbl_EX,array('year'=>date('Y')));        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalExpenseBydate($data)
    {
        $data['fromdate']=date('Y-m-d',strtotime($data['fromdate']));
        $data['todate']=date('Y-m-d',strtotime($data['todate']));
        $query=$this->db->select_sum('expenseAmount')->get_where($this->_tbl_EX,'date BETWEEN "'. $data['fromdate']. '" and "'. $data['todate'].'"');      
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalSalaryExpense()
    {
        $query=$this->db->select_sum('salaryAmount')
        				->get_where($this->_tbl_SAEX,array('year'=>date('Y')));        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalSalaryExpenseBydate($data)
    {
        $data['fromdate']=date('Y-m-d',strtotime($data['fromdate']));
         $data['todate']=date('Y-m-d',strtotime($data['todate']));
        $query=$this->db->select_sum('salaryAmount')->get_where($this->_tbl_SAEX,'date BETWEEN "'. $data['fromdate']. '" and "'. $data['todate'].'"');        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalLandIncome()
    {
        $query=$this->db->select_sum('amount')->get_where($this->_tbl_AC,array('year'=>date('Y')));        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalLandIncomeBydate($data)
    {
        $data['fromdate']=date('Y-m-d',strtotime($data['fromdate']));
         $data['todate']=date('Y-m-d',strtotime($data['todate']));
        $query=$this->db->select_sum('amount')->get_where($this->_tbl_AC,'date BETWEEN "'. $data['fromdate']. '" and "'. $data['todate'].'"');        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalExtraIncome(){

        $query=$this->db->select_sum('amount')->get_where($this->_tbl_EAC,array('year'=>date('Y')));        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getTotalExtraIncomeBydate($data){
        $data['fromdate']=date('Y-m-d',strtotime($data['fromdate']));
        $data['todate']=date('Y-m-d',strtotime($data['todate']));
        $query=$this->db->select_sum('amount')->get_where($this->_tbl_EAC,'date BETWEEN "'. $data['fromdate']. '" and "'. $data['todate'].'"');        
        $result=$query->row_array();
        if(!empty($result)){return $result;}
    }

    public function getDueInstallmentByDate($date){
        $sql = "SELECT tbl_customer_reg.customerId, tbl_customer_reg.id, tbl_customer_reg.total_cost, SUM(tbl_land_account.amount)as Total_paid,
                  substring_index(group_concat(tbl_land_account.date order by tbl_land_account.date desc), ',', 1) as Last_pay_date, tbl_customer_reg.mobile
                  FROM tbl_land_account 
                  JOIN tbl_customer_reg
                  ON tbl_customer_reg.customerId = tbl_land_account.customerId
                  Where (tbl_land_account.date Between '2015-01-01' AND '".$date."') 
                  GROUP BY customerId";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

}