<?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 accountmodel
 *
 * @author BusyMan
 */
class ExtraIncomeSetupModel extends CI_Model {

    //put your code here
    private $_tbl_EAC = 'tbl_extra_account';
    private $_tbl_AC = 'tbl_land_account';
    private $_tbl_CA = 'tbl_calls';
    private $_tbl_CRG = 'tbl_customer_reg';
    private $_tbl_USR = 'tbl_user';
    private $_tbl_ADIMN = 'tbl_admin';

    public function insert_amount($data) {
        $data['date'] = date('Y-m-d', strtotime($data['date']));
        $this->db->insert($this->_tbl_EAC, $data);
        return $this->db->insert_id();
    }

    public function getAllExtraincome() {
        //$query = $this->db->get($this->_tbl_EAC);
        $query=$this->db->select('*')->order_by('id','desc')->get($this->_tbl_EAC);
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function deleteIncomeByid($id) {
        $query = $this->db->delete($this->_tbl_EAC, array('id' => $id));
        return $this->db->affected_rows();
    }

    public function getincomeInfoByid($id) {
        $query = $this->db->get_where($this->_tbl_EAC, array('id' => $id));
        $result = $query->row_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function updateIncomeById($data) {
        $data['date'] = date('Y-m-d', strtotime($data['date']));
        $this->db->update($this->_tbl_EAC, $data, array('id' => $data['id']));
        return $this->db->affected_rows();
    }

    /*public function getExtraincomeBydate($data) {
        if (!empty($data['date'])) {
            $data['date'] = date('Y-m-d', strtotime($data['date']));
        }
        if (empty($data['incomeCategory'])) {
            $query = $this->db->get_where($this->_tbl_EAC, array('date' => $data['date']));
        } elseif (empty($data['date'])) {
            $query = $this->db->get_where($this->_tbl_EAC, array('incomeCategory' => $data['incomeCategory']));
        } else {
            $query = $this->db->get_where($this->_tbl_EAC, array('date' => $data['date'], 'incomeCategory' => $data['incomeCategory']));
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function getincomeBydate($data) { 
        //print_r($data);die();
        if (!empty($data['date'])) {
            $data['date'] = date('Y-m-d', strtotime($data['date']));
        }
        if (empty($data['payment_type']) || $data['payment_type']==null) {
            $query = $this->db->get_where($this->_tbl_AC, array('date' => $data['date']));
         }elseif (empty($data['date'])) {
            $query = $this->db->get_where($this->_tbl_AC, array('payment_type' => $data['payment_type']));
        }else {
            $query = $this->db->get_where($this->_tbl_AC, array('payment_type' => $data['payment_type'],'date'=>$data['date']));
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }*/

    public function getExtraincomeBydaterange($data) { 
        //echo '<pre>';print_r($data);die();
        $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        $data['todate'] = date('Y-m-d', strtotime($data['todate']));
        if (empty($data['customerId']) || $data['customerId'] == "") {
            $query = $this->db->get_where($this->_tbl_EAC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
        } else {
            $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
                            ->where('incomeCategory', $data['incomeCategory'])->get($this->_tbl_EAC);
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }
    public function getincomeBydaterange($data) {
        $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        $data['todate']   = date('Y-m-d', strtotime($data['todate']));
        if (empty($data['payment_type']) || $data['payment_type']==null) {
            $query = $this->db->get_where($this->_tbl_AC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
        }else{
            $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
                            ->where('payment_type', $data['payment_type'])->get($this->_tbl_AC);
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function incomebycusttotalall(){
        $this->db->select('customerId');
        // $this->db->select('amount');
        $query   = $this->db->select_sum('amount')->group_by('customerId')->get($this->_tbl_AC);
        $result  = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }
    public function incomebycusttotal($data){
        $query   = $this->db->select_sum('amount')->where('customerId', $data)->get($this->_tbl_AC);
        $result  = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function getadminname($data){
        $query   = $this->db->select('admin_name')->where('employeeId', $data)->get($this->_tbl_ADIMN);
        $result  = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }
    public function getcustomernames($data){
        $query   = $this->db->select('applicant_name')->where('customerId', $data)->get($this->_tbl_CRG);
        $result  = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    // public function incomebycusttotal($customerId){
    //     $query=$this->db->get_where($this->_tbl_AC,array('customerId'=>$customerId));
    //     $result=$query->row_array();
    //     if(!empty($result)){
    //         echo print_r($result);
    //     }
    // }


    public function getExtraincomeBydaterangeandEmp($data) { 
        //echo '<pre>';print_r($data);die();
        // $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        // $data['todate'] = date('Y-m-d', strtotime($data['todate']));
        // if (empty($data['customerId']) || $data['customerId'] == "") {
        //     $query = $this->db->get_where($this->_tbl_EAC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
        // } else {
            
        // }

        // $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
        $query = $this->db->where('ref_id', $data['employee_id'])->get($this->_tbl_CRG);
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }


    public function getincomeBydaterangeEmp($data) {
        $query = $this->db->where('ref_id', $data['employee_id'])->get($this->_tbl_CRG);
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function getcustomerinfobyid($data)
    {   $this->db->select('tbl_customer_reg.*');
        $this->db->select('tbl_project.project_name');
        $this->db->join('tbl_project','tbl_project.project_id=tbl_customer_reg.projectcategory');    
        $query=$this->db->get_where('tbl_customer_reg', array('customerId' =>$data['customerId']));
        $result=$query->row_array();
        if(!empty($result)){
            return $result;
        }
        
        // $query=$this->db->select('*')->get_where($this->_tbl_CRG,array('customerId'=>$data['customerId']));
        // $result=$query->row_array();
        // if (!empty($result)) {
        //      return $result;
        // }
    }

    // public function getcustomerById($id){
    //     $this->db->select('tbl_customer_reg.*');
    //     $this->db->select('tbl_project.project_name');
    //     $this->db->join('tbl_project','tbl_project.project_id=tbl_customer_reg.projectcategory');    
    //     $query=$this->db->get_where('tbl_customer_reg', array('customerId' =>$id));
    //     $result=$query->row_array();
    //     if(!empty($result)){
    //         return $result;
    //     }
    // }




    public function getExtraincomeBydaterangeCustomer($data) { 
        //echo '<pre>';print_r($data);die();
        $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        $data['todate'] = date('Y-m-d', strtotime($data['todate']));
        
        $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
                        ->where('customerId', $data['customerId'])->get($this->_tbl_AC);
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    // public function getcustomerById($id){
    //     $this->db->select('tbl_customer_reg.*');
    //     $this->db->select('tbl_project.project_name');
    //     $this->db->join('tbl_project','tbl_project.project_id=tbl_customer_reg.projectcategory');    
    //     $query=$this->db->get_where('tbl_customer_reg', array('customerId' =>$id));
    //     $result=$query->row_array();
    //     if(!empty($result)){
    //         return $result;
    //     }
    // }


    // public function getExtraincomeBydaterangeCustomer($data) { 
    //     //echo '<pre>';print_r($data);die();
    //     $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
    //     $data['todate'] = date('Y-m-d', strtotime($data['todate']));
    //     if (empty($data['customerId']) || $data['customerId'] == "") {
    //         $query = $this->db->get_where($this->_tbl_EAC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
    //     } else {
    //         $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
    //                         ->where('customerId', $data['customerId'])->get($this->_tbl_AC);
    //     }
    //     $result = $query->result_array();
    //     if (!empty($result)) {
    //         return $result;
    //     }
    // }

    public function getExtraincomeBydaterangeandCust($data) { 
        //echo '<pre>';print_r($data);die();
        $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        $data['todate'] = date('Y-m-d', strtotime($data['todate']));
        if (empty($data['customerId']) || $data['customerId'] == "") {
            $query = $this->db->get_where($this->_tbl_EAC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
        } else {
            $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
                            ->where('customerId', $data['customerId'])->get($this->_tbl_AC);
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    
    

    public function getincomeBydaterangeCust($data) {
        $data['fromdate'] = date('Y-m-d', strtotime($data['fromdate']));
        $data['todate']   = date('Y-m-d', strtotime($data['todate']));
        if (empty($data['payment_type']) || $data['payment_type']==null) {
            $query = $this->db->get_where($this->_tbl_AC, 'date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"');
        }else{
            $query = $this->db->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')
                            ->where('payment_type', $data['payment_type'])->get($this->_tbl_AC);
        }
        $result = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }

    public function getTotalAmountInDateRange($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')->where('date BETWEEN "' . $data['fromdate'] . '" and "' . $data['todate'] . '"')->get($this->_tbl_AC);
        $result           = $query->result_array();
        if (!empty($result)) {
            return $result;
        }
    }


    // public function getIncomeByYear($year){
        
    //     $query  = $this->db->get_where($this->_tbl_AC, array('year'=>$year));
    //     $result = $query->result_array();
    //     if(!empty($result)){
    //         return $result;
    //     }
    // }

    public function getIncomeByYear($year){

        $query  = $this->db->get_where($this->_tbl_AC, array('year'=>$year));
        $result = $query->result_array();
        if(!empty($result)){
            return $result;
        }
    }

    // public function getcustomerinfobyid($data)
    //     {   
    //     $this->db->select('tbl_customer_reg.*');
    //     $this->db->select('tbl_project.project_name');
    //     $this->db->join('tbl_project','tbl_project.project_id=tbl_customer_reg.projectcategory');    
    //     $query=$this->db->get_where('tbl_customer_reg', array('customerId' =>$data['customerId']));
    //     $result=$query->row_array();
    //     if(!empty($result)){
    //         return $result;
    //     }



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

    public function getTotalExtraIncomeByYear(){
        $query  = $this->db->select_sum('amount')->from($this->_tbl_EAC);
        $query  = $this->db->get();
        $result = $query->row_array();
        if(!empty($result)){
            return $result;
        }
    }


     public function getinvoiceInfoByid($id) 
      {
          // $this->db->select('TU.*,TXA.*');
          // $this->db->from('tbl_extra_account TXA');
          // $this->db->join('tbl_user TU','TXA.customerId=TU.userId');
          // $this->db->where('TXA.id',$id);
          // $query=$this->db->get();
        $query=$this->db->get_where($this->_tbl_EAC,array('id'=>$id));
          $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;}
      } */
}

// $atoms = explode('-',$your_date_variable_in_php);
// $year = $atoms[0];
// $month = $atoms[1];
// $day = $atoms[2];