<?php

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

class Dashboard extends MX_Controller {
    


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

        $logged_in = $this->session->userdata('logged_in');
        if (!$logged_in) {
            redirect('access/login', 'refresh');
        }

        $language = $this->session->userdata('lang');
        $this->lang->load('dashboard', $language);
        $this->load->library('envatoapi');
    }

    public function index() {
        $month = date('F');
        $year = date('Y');
        $data['user'] = $this->countAllRows('users');
        $data['committee'] = $this->countAllRows('committee');
        $data['pastor'] = $this->countAllRows('pastor');
        $data['clan'] = $this->countAllRows('clan');
        $data['chorus'] = $this->countAllRows('chorus');
        $data['staff'] = $this->countAllRows('staff');
        $data['member'] = $this->countAllRows('member');
        $data['student'] = $this->countAllRows('sundayschool');
        $data['mFundsCollect'] = $this->getMonthFundsData("Collect", $month, $year);
        $data['mFundsSpend'] = $this->getMonthFundsData("Spend", $month, $year);
        $data['fundsCollect'] = $this->getTotalFundsData("Collect");
        $data['fundsSpend'] = $this->getTotalFundsData("Spend");
        $data['totalAssets'] = $this->getTotalAssetsData();
        $data['totalDonation'] = $this->getTotalDonatinData();
        $data['mDonation'] = $this->getMonthDonationData($month, $year);
        $data['mAssets'] = $this->getMonthAssetsData($month, $year);
        $data['browse_collect_year'] = $this->get_sum_browse_collect_year($year);
        $data['browse_spend_year'] = $this->get_sum_browse_spend_year($year);
        $data['browse_collect_jan'] = $this->get_sum_collect_jan($year);
        $data['browse_spend_jan'] = $this->get_sum_spend_jan($year);
        $data['browse_collect_feb'] = $this->get_sum_collect_feb($year);
        $data['browse_spend_feb'] = $this->get_sum_spend_feb($year);
        $data['browse_collect_feb'] = $this->get_sum_collect_feb($year);
        $data['browse_spend_feb'] = $this->get_sum_spend_feb($year);
        $data['browse_collect_mar'] = $this->get_sum_collect_mar($year);
        $data['browse_spend_mar'] = $this->get_sum_spend_mar($year);
        $data['browse_collect_apr'] = $this->get_sum_collect_apr($year);
        $data['browse_spend_apr'] = $this->get_sum_spend_apr($year);
        $data['browse_collect_may'] = $this->get_sum_collect_may($year);
        $data['browse_spend_may'] = $this->get_sum_spend_may($year);
        $data['browse_collect_jun'] = $this->get_sum_collect_jun($year);
        $data['browse_spend_jun'] = $this->get_sum_spend_jun($year);
        $data['browse_collect_jul'] = $this->get_sum_collect_jul($year);
        $data['browse_spend_jul'] = $this->get_sum_spend_jul($year);
        $data['browse_collect_aug'] = $this->get_sum_collect_aug($year);
        $data['browse_spend_aug'] = $this->get_sum_spend_aug($year);
        $data['browse_collect_sep'] = $this->get_sum_collect_sep($year);
        $data['browse_spend_sep'] = $this->get_sum_spend_sep($year);
        $data['browse_collect_oct'] = $this->get_sum_collect_oct($year);
        $data['browse_spend_oct'] = $this->get_sum_spend_oct($year);
        $data['browse_collect_nov'] = $this->get_sum_collect_nov($year);
        $data['browse_spend_nov'] = $this->get_sum_spend_nov($year);
        $data['browse_collect_dec'] = $this->get_sum_collect_dec($year);
        $data['browse_spend_dec'] = $this->get_sum_spend_dec($year);
        $data['siteinfo'] = $this->getBasicInfo();
        $data['purchase'] = $this->evnatoVerify();
        $this->load->view('Dashboard/header', $data);
        $this->load->view('Dashboard/dashboard', $data);
        $this->load->view('Dashboard/footer', $data);
    }
    
    
    public function getBasicInfo(){
        $query = $this->db->get('websitebasic');
        return $query->result();
    }
    

    public function countAllRows($table) {
        return $this->db->count_all_results($table);
    }

    public function countFieldRows($table, $field, $gender) {
        $this->db->where($field, $gender);
        return $this->db->count_all_results($table);
    }

    //Getting Sum Of Collected Data
    public function getMonthFundsData($type, $month, $year) {
        $this->db->select_sum('fundsamount');
        $this->db->where('fundstype', $type);
        $this->db->where('fundsmonth', $month);
        $this->db->where('fundsyear', $year);
        $query = $this->db->get('funds');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->fundsamount;
            }
            return $row->fundsamount;
        }
    }
    
    
    //Getting Sum Of Collected Data
    public function getTotalFundsData($type) {
        $this->db->select_sum('fundsamount');
        $this->db->where('fundstype', $type);
        $query = $this->db->get('funds');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->fundsamount;
            }
            return $row->fundsamount;
        }
    }
    
    //Getting Sum Of Collected Data
    public function getTotalDonatinData() {
        $this->db->select_sum('donationamount');
        $query = $this->db->get('donation');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->donationamount;
            }
            return $row->donationamount;
        }
    }
    
    
    //Getting Sum Of Collected Data
    public function getTotalAssetsData() {
        $this->db->select_sum('assetsamount');
        $query = $this->db->get('assets');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->assetsamount;
            }
            return $row->assetsamount;
        }
    }
    
    //Getting Sum Of Collected Data
    public function getMonthDonationData($month, $year) {
        $this->db->select_sum('donationamount');
        $this->db->where('donationmonth', $month);
        $this->db->where('donationyear', $year);
        $query = $this->db->get('donation');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->donationamount;
            }
            return $row->donationamount;
        }
    }
    
    //Getting Sum Of Collected Data
    public function getMonthAssetsData($month, $year) {
        $this->db->select_sum('assetsamount');
        $this->db->where('assetsmonth', $month);
        $this->db->where('assetsyear', $year);
        $query = $this->db->get('assets');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $row->assetsamount;
            }
            return $row->assetsamount;
        }
    }
    
    
    /////////////////////////////////////////////////
	//////////////////// Sum ////////////////////////
	/////////////////////////////////////////////////
	
	//Getting Sum Of Collected Data
	public function get_sum_collect_data(){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Data
	public function get_sum_spend_data(){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Current Balance Data
	public function get_sum_current_balance(){
		$this->db->order_by("fundsid", "desc");
		$this->db->limit(1);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsbalance;
			}
			return $row->fundsbalance;
		}
	}
	
	
	//Getting Sum Of Collect Of Jan
	public function get_sum_collect_jan($year){	
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'January');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of Jan
	public function get_sum_spend_jan($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'January');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	//Getting Sum Of Collect Of February
	public function get_sum_collect_feb($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'February');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of Jan
	public function get_sum_spend_feb($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'February');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of March
	public function get_sum_collect_mar($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'March');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of Jan
	public function get_sum_spend_mar($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'March');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of April
	public function get_sum_collect_apr($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'April');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of Jan
	public function get_sum_spend_apr($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'April');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of May
	public function get_sum_collect_may($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'May');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of Jan
	public function get_sum_spend_may($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'May');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of June
	public function get_sum_collect_jun($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'June');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of June
	public function get_sum_spend_jun($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'June');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of July
	public function get_sum_collect_jul($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'July');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of July
	public function get_sum_spend_jul($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'July');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of August
	public function get_sum_collect_aug($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'August');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of August
	public function get_sum_spend_aug($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'August');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	//Getting Sum Of Collect Of September
	public function get_sum_collect_sep($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'September');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of September
	public function get_sum_spend_sep($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'September');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	//Getting Sum Of Collect Of October
	public function get_sum_collect_oct($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'October');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of August
	public function get_sum_spend_oct($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'October');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of November
	public function get_sum_collect_nov($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'November');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of November
	public function get_sum_spend_nov($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'November');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Collect Of December
	public function get_sum_collect_dec($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', 'December');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Spend Of December
	public function get_sum_spend_dec($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', 'December');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	
	//Getting Sum Of Donation
	public function get_sum_donation(){
	
		$query = $this->db->select_sum('donationamount');
		$query = $this->db->get('donation');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->donationamount;
			}
			return $row->donationamount;
		}
	} 
	
	
	//Getting Sum Of Donation
	public function get_sum_assets(){
	
		$query = $this->db->select_sum('assetsamount');
		$query = $this->db->get('assets');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->assetsamount;
			}
			return $row->assetsamount;
		}
	}
	
	
	
	/////////////////////////////////////////////////
	//////////////////// Browse ///////////////////
	/////////////////////////////////////////////////
	
	//Getting Browse Sum Of Collected Data by Monthly
	public function get_sum_browse_collect($month, $year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsmonth', $month);
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	//Getting Browse Sum Of Spend Data by Monthly
	public function get_sum_browse_spend($month, $year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsmonth', $month);
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}			
			return $row->fundsamount;
		}
	}
	
	//Getting Browse Sum Of Balance Data by Monthly
	public function get_sum_browse_balance($month, $year){
		
		$this->db->order_by("fundsid", "desc");
		$this->db->limit(1);
		$query = $this->db->get_where('funds', array('fundsmonth' => $month, 'fundsyear' => $year));
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsbalance;
			}
			return $row->fundsbalance;
		}
	}
	
	
	//Getting Browse Sum Of Collected Data by Year
	public function get_sum_browse_collect_year($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Collect');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}
			return $row->fundsamount;
		}
	}
	
	//Getting Browse Sum Of Spend Data by Monthly
	public function get_sum_browse_spend_year($year){
	
		$query = $this->db->select_sum('fundsamount');
		$query = $this->db->where('fundstype', 'Spend');
		$query = $this->db->where('fundsyear', $year);
		$query = $this->db->get('funds');
		
		if($query->num_rows() > 0){
			foreach($query->result() as $row){
				$row->fundsamount;
			}			
			return $row->fundsamount;
		}
	}
        
        

    public function switchLang($language = "") {
        $language = ($language != "") ? $language : "english";
        $this->session->set_userdata('lang', $language);
        //redirect('dashboard/dashboard', 'refresh');
        redirect($_SERVER['HTTP_REFERER']);
    }
    
    
    public function evnatoVerify(){
        $purchaseCode = $this->getBasicInfo()[0]->verify;
        $o = $this->envatoapi->verifyPurchase($purchaseCode);
        if ( is_object($o) ) {
            return true;
        }else {
            return false;
        }
    }
    
    public function verifyPurchase() {
        $data['verify'] = $this->input->post('purchasecode');
        $this->db->where('basicid', 1);
        $this->db->update('websitebasic', $data);
        redirect('dashboard', 'refresh');
    }

}
