<?php
/**
 * Created by PhpStorm.
 * User: Mojtaba
 * Date: 9/15/2015
 * Time: 11:19 PM
 * Project: NodCMS
 * Website: http://www.nodcms.com
 */
defined('BASEPATH') OR exit('No direct script access allowed');

class Nodcms_general_model extends CI_Model {

    public function __construct()
    {
        // Call the CI_Model constructor
        parent::__construct();
    }

    // Select from "setting" table
    function getWebsiteInfo()
    {
        $this->db->select('*');
        $this->db->from('setting');
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)?$return[0]:0;
    }

    // Select setting's options in one language from "setting_options_per_lang" table
    function getWebsiteInfoOptions($language_id=null)
    {
        $this->db->select('*');
        $this->db->from('setting_options_per_lang');
        if($language_id!=null)
            $this->db->where('language_id',$language_id);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)?$return[0]:0;
    }

    // Select a list from "menu" table
    

    // Select a list from "languages" table
    function getLanguages()
    {
        $this->db->select('*');
        $this->db->from('languages');
        $this->db->where('public',1);
        $query = $this->db->get();
        return $query->result_array();
    }

    // Select a row with "code" field from "languages" table
    function getLanguageByCode($code)
    {
        $this->db->select('*');
        $this->db->from('languages');
        $this->db->where('public',1);
        $this->db->where('code',$code);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)?$return[0]:0;
    }

    // Select a row from "languages" table (main condition: default = 0)
    function getLanguageDefault()
    {
        $this->db->select('*');
        $this->db->from('languages');
        $this->db->where('public',1);
        $this->db->where('default',1);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)?$return[0]:0;
    }

    
    // Select a row form "users" table with "email_hash" and "active_code"
    function getUserByEmailHashAndActiveCode($email_hash,$active_code)
    {
        $this->db->select('*');
        $this->db->from('users');
        $this->db->where('email_hash',$email_hash);
        $this->db->where('active_code',$active_code);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)!=0?$return[0]:0;
    }

    // Select a row form "users" table with "email" and "password" (for login)
    function getUserByEmailAndPassword($email,$password)
    {
        $this->db->select('*');
        $this->db->from('users');
        $this->db->where('email',$email);
		$this->db->or_where('username',$email);
        $this->db->where('password',$password);
        //$this->db->where('parent_id',0);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)!=0?$return[0]:0;
    }

    

    // Return 1 if a "username" exists in "users" table, else return 0
    function checkUsernameExists($text)
    {
        $this->db->select('*');
        $this->db->from('users');
        $this->db->where('username',$text);
        $this->db->where('active_register',1);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)!=0?1:0;
    }

    // Return 1 if a "email" exists in "users" table, else return 0
    function checkEmailExists($text,$fullname = null,$active_register=true)
    {
        $this->db->select('*');
        $this->db->from('users');
        $this->db->where('fullname',$fullname);
        $this->db->where('email',$text);
        /*if($active_register==true)
            $this->db->where('active_register',1);
        elseif($active_register==false)
            $this->db->where('active_register',0);*/
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)!=0?1:0;
    }

    // Update "users" table with "email" condition
    function updateUserByEmail($data,$email)
    {
        $this->db->where('email',$email);
        $this->db->update('users',$data);
    }

    // Insert into "users" (for register form)
    function insertUser($data)
    {
        $this->db->insert('users',$data);
    }

    // Update "users" table (for keep a user login)
    function updateUserLogin($user_id,$keep_me_time,$user_agent)
    {
        $this->db->where('user_id',$user_id);
        $this->db->set('keep_me_time',$keep_me_time);
        $this->db->set('user_agent',$user_agent);
        $this->db->update('users');
    }

    // Update "users" table (set a new password form return password form)
    function userSetNewPassword($user_id,$new_password)
    {
        $this->db->where('user_id',$user_id);
        $this->db->set('password',$new_password);
        $this->db->set('active_register',1);
        $this->db->set('active',1);
        $this->db->set('status',1);
        $this->db->set('active_code',"");
        $this->db->update('users');
    }

    // Update "users" table (set a new password from profile change password form)
    function userEditPassword($user_id,$new_password)
    {
        $this->db->where('user_id',$user_id);
        $this->db->set('password',$new_password);
        $this->db->update('users');
    }

    /*
     * Calculating visits and visitors!
     * This methods used in nodcms_general_helper.php
     */
    function getDuplicateVisitor($session_id,$request_url)
    {
        $this->db->select('*');
        $this->db->from('visitors');
        $this->db->where('session_id',$session_id);
        $this->db->where('request_url',$request_url);
        $query = $this->db->get();
        return $query->result_array();
    }

    function updateDuplicateVisitor($session_id,$request_url,$data)
    {
        $this->db->where('session_id',$session_id);
        $this->db->where('request_url',$request_url);
        $this->db->update('visitors',$data);
    }

    function insertVisitors($data)
    {
        $this->db->insert('visitors', $data);
        $insert_id = $this->db->insert_id();
        return $insert_id;
    }

    function getMaxDateVisitor()
    {
        $this->db->select('max(created_date)');
        $this->db->from('visitors');
        $this->db->where('created_date < "'.strtotime(date("d.m.Y")).'"');
        $query = $this->db->get();
        $result = $query->result_array();
        return count($result)!=0?$result[0]["max(created_date)"]:0;
    }

    function getMinDateVisitor()
    {
        $this->db->select('min(created_date)');
        $this->db->from('visitors');
        $this->db->where('created_date < "'.strtotime(date("d.m.Y")).'"');
        $query = $this->db->get();
        $result = $query->result_array();
        return count($result)!=0?$result[0]["min(created_date)"]:0;
    }

    function updateStatistic($minDate,$maxDate)
    {
        // Initial data get
        $this->db->select('count(*), sum(count_view)');
        $this->db->from('visitors');
        $this->db->where('created_date >= "'.$minDate.'"');
        $this->db->where('created_date < "'.$maxDate.'"');
        $query = $this->db->get();
        $result = $query->result_array();
        if(count($result)!=0 && $result[0]["count(*)"]){
            // Initial data set
            $data = array(
                "created_date"=>time(),
                "statistic_date"=>$minDate,
                "visits"=>$result[0]["sum(count_view)"],
            );
            // Get visitors count
            $this->db->select('session_id');
            $this->db->from('visitors');
            $this->db->where('created_date >= "'.$minDate.'"');
            $this->db->where('created_date < "'.$maxDate.'"');
            $this->db->group_by('session_id');
            $query = $this->db->get();
            $result = $query->result_array();
            // Set visitors count
            $data["visitors"]=count($result);
            // Get popular URL info
            $this->db->select('request_url, count(request_url) as url_count');
            $this->db->from('visitors');
            $this->db->where('created_date >= "'.$minDate.'"');
            $this->db->where('created_date < "'.$maxDate.'"');
            $this->db->group_by('request_url');
            $this->db->order_by('url_count','DESC');
            $this->db->limit(1);
            $query = $this->db->get();
            $result = $query->result_array();
            // Set popular URL info
            $data["popular_url"]=$result[0]["request_url"];
            $data["popular_url_count"]=$result[0]["url_count"];
            // Get popular Language info
            $this->db->select('language_id, count(language_id) as language_count');
            $this->db->from('visitors');
            $this->db->where('created_date >= "'.$minDate.'"');
            $this->db->where('created_date < "'.$maxDate.'"');
            $this->db->group_by('language_id');
            $this->db->order_by('language_count','DESC');
            $this->db->limit(1);
            $query = $this->db->get();
            $result = $query->result_array();
            // Set popular Language info
            $data["popular_lang"]=$result[0]["language_id"];
            $data["popular_lang_count"]=$result[0]["language_count"];
            // Get popular Language visits percent
            $this->db->select('count(*)');
            $this->db->from('visitors');
            $this->db->where('created_date >= "'.$minDate.'"');
            $this->db->where('created_date < "'.$maxDate.'"');
            $query = $this->db->get();
            $result = $query->result_array();
            // Set popular Language visits percent
            $data["popular_lang_percent"]=round((100*$data["popular_lang_count"])/$result[0]["count(*)"]);
            // Insert in statistic's table
            $this->db->insert('statistic',$data);
            // Delete unused records from visitors table
            $this->db->delete('visitors','created_date >= "'.$minDate.'" AND created_date < "'.$maxDate.'"');
        }else{
            // Set data for statistic table if doesn't exists eny data in visitors table in the period time
            $data = array(
                "created_date"=>time(),
                "statistic_date"=>$minDate,
                "visitors"=>0,
                "visits"=>0,
                "popular_url"=>"",
                "popular_url_count"=>0,
                "popular_lang"=>0,
                "popular_lang_count"=>0,
                "popular_lang_percent"=>0,
            );
            // Insert in statistic's table
            $this->db->insert('statistic',$data);
        }
    }

    function QueryArray($table,$where = array(),$order_by = null,$sort = null){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        if($order_by!=null) $this->db->order_by($order_by,$sort);
        $query = $this->db->get();
        return $query->result_array();
    }
    
	function QueryArrayGroup($table,$where = array(),$group_by = null){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        if($group_by!=null) $this->db->group_by($group_by);
        $query = $this->db->get();
        return $query->result_array();
    }

    function QueryArrayIn($table,$where = array(),$filed,$in_array){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        $this->db->where_in($filed, $in_array );
        $query = $this->db->get();
        return $query->result_array();
    }
    
	function QueryArrayInPayment($table,$where = array(),$filed1 = null,$in_array1 = null,$filed2 = null,$in_array2 = null){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        if($in_array1!='') { $this->db->where_in($filed1, $in_array1 ); }
        if($in_array2!='') { $this->db->or_where_in($filed2, $in_array2 ); }
        $query = $this->db->get();
        //echo $this->db->last_query();
        return $query->result_array();
    }

	function PaymentResult($where = array()){
        $this->db->select('*,SUM(no_of_members) as total_members,SUM(actual_amount) as a_amount,SUM(vat_amount) as v_amount,SUM(payment_amount) AS p_amount',FALSE);
        $this->db->from('payment');
        $this->db->where($where);
        $this->db->join('package', 'package.id = payment.package_id', 'left');
        $this->db->group_by("payment.package_id");
        $query = $this->db->get();
        //echo $this->db->last_query();die();
        return $query->result_array();
    }
    
    function QueryLimit($table,$where = array(),$limit=null,$order_by = null,$sort = null){
        $this->db->select('*');
        $this->db->from($table);
        if($where!=''){
        	$this->db->where($where);
    	}
	    if($limit!=''){
	       $this->db->limit($limit);
	    }
	    if($order_by!=null) $this->db->order_by($order_by,$sort);
        $query = $this->db->get();
        //echo $this->db->last_query();
        return $query->result_array();
    }
    
    function TotalPayment($search_by_year){
    	$this->db->select('MONTH(payment_date) AS month,SUM(payment_amount) AS total');
            $this->db->from('payment');
            $this->db->where('YEAR(payment_date)',$search_by_year);
            $this->db->group_by('YEAR(payment_date)');
            $this->db->group_by('MONTH(payment_date)');
            $query = $this->db->get();
           // echo $this->db->last_query();die();
            return $query->result_array();
    }
    
	function TotalExpense($search_by_year){
    	$this->db->select('MONTH(expense_date) AS month,SUM(amount) AS total');
            $this->db->from('expense');
            $this->db->where('YEAR(expense_date)',$search_by_year);
            $this->db->group_by('YEAR(expense_date)');
            $this->db->group_by('MONTH(expense_date)');
            $query = $this->db->get();
            return $query->result_array();
    }
    
    function GetExpireProof(){
    	//$query = "SELECT * FROM  gym_employee_proof WHERE  (`expire_date` >= NOW() - INTERVAL 1 DAY AND `expire_date` < NOW() + INTERVAL 7 DAY ) OR `expire_date` > NOW() - INTERVAL 50 YEAR ORDER BY `expire_date` ASC;";
		$query = "SELECT * FROM  gym_employee_proof WHERE  `expire_date` <= NOW() + INTERVAL 7 DAY AND `expire_date` <> '0000-00-00' ORDER BY `expire_date` ASC;";
    	$result = $this->db->query($query);
        return $result->result_array();
    }
    
	function GetExpirePayment(){
    	/*$query = "SELECT * FROM  gym_payment 
    				WHERE `remember` = 1 
    				AND `expire_date` <= NOW() + INTERVAL 7 DAY AND `expire_date` <> '0000-00-00'
    				ORDER BY `expire_date` ASC ;";*/
		
		/*$query = "SELECT * FROM  gym_payment 
    				WHERE `remember` = 1 
					AND `is_add_on` <> 1 
    				AND `expire_date` <= NOW() + INTERVAL 7 DAY AND `expire_date` <> '0000-00-00'
    				GROUP BY `member_id` ORDER BY `gym_payment`.`expire_date` ASC ";*/
		
		$query = "SELECT * FROM  gym_payment 
    				WHERE `remember` = 1 
					AND `is_add_on` <> 1 
    				AND (`expire_date` >= NOW() - INTERVAL 10 DAY AND `expire_date` <= NOW() + INTERVAL 10 DAY) AND `expire_date` <> '0000-00-00'
    				GROUP BY `member_id` ORDER BY `gym_payment`.`expire_date` ASC ";
		
    	$result = $this->db->query($query);
        return $result->result_array();
    }
	
	function GetExpireAddPayment(){
    	/*$query = "SELECT * FROM  gym_payment 
    				WHERE `remember` = 1 
    				AND `expire_date` <= NOW() + INTERVAL 7 DAY AND `expire_date` <> '0000-00-00'
    				ORDER BY `expire_date` ASC ;";*/
		 $query = "SELECT * FROM  gym_payment 
    				WHERE `remember` = 1 
					AND `is_add_on` = 1 
    				AND `expire_date` <= NOW() + INTERVAL 7 DAY AND `expire_date` <> '0000-00-00'
    				GROUP BY `member_id` ORDER BY  `gym_payment`.`expire_date` ASC ";
		
    	$result = $this->db->query($query);
        return $result->result_array();
    }
    
	function GetExpirePaymentReport($where,$filter_status = ''){
    	//$query = "SELECT * FROM  gym_payment WHERE  DATE_ADD(expire_date, INTERVAL YEAR(CURDATE())-YEAR(expire_date) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(expire_date),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);";
    	$this->db->select('*');
    	//$this->db->select_max('id' , 'id');
    	$this->db->from('payment');
    	$this->db->where("remember",'1');
		//$this->db->where("is_add_on",'0');
    	//$this->db->where("((`expire_date` >= NOW() - INTERVAL 1 DAY AND `expire_date` < NOW() + INTERVAL 7 DAY) OR `expire_date` > NOW() - INTERVAL 50 YEAR)");
    	if($filter_status=='active') $this->db->where("`expire_date` >= NOW()");
    	if($filter_status=='expired') $this->db->where("`expire_date` <= NOW()");
		$this->db->where("`expire_date` <> '0000-00-00'");
    	$this->db->where($where);
    	$this->db->where("id IN (
					    SELECT MAX(id)
					    FROM `gym_payment`
					    GROUP BY member_id
					)");
    	$this->db->order_by('id', 'DESC');
		//$this->db->group_by('member_id');
    	//$this->db->order_by('expire_date', 'ASC');
    	
    	$result =$this->db->get();
    	//echo $this->db->last_query();die();
        return $result->result_array();
    }
    
	function GetPendingPayment($where){
    	$this->db->select('*,SUM(payment_amount) as total');
		$this->db->from('payment');
		$this->db->where($where); 
		$query = $this->db->get();
		//echo $this->db->last_query();
		return  $query->row();
    }
    
    function FilterMember($search_key){
    	$this->db->select('*');
		$this->db->from('members');
		$this->db->like('member_name', $search_key);
		return $this->db->get()->result_array();
    }

    function UserList($table,$where,$like=array(),$and = null,$limit,$letter){
        $where_condition = '';
        if(!empty($like))
        {
            foreach ($like as $key => $value){
                $where_condition[] = $key." LIKE '%".$value."%'";
            }

            $where_condition = ' AND (' .implode(" OR ",$where_condition).')';
        }else{
            $where_condition = '';
        }
        if($letter){
            $where_letter = ' AND fullname LIKE "'.$letter.'%"';
        }else{
            $where_letter = '';
        }
        if($and != null) {
            $where_status = ' AND status = ' . $and;
        }else{
            $where_status = ' AND status = 1';
        }
        if($and=='all'){
            $where_status = '';
        }
        $query = "SELECT * FROM ".$this->db->dbprefix.$table." WHERE `group_id` = '".$where."' ".$where_condition." ".$where_status." ".$where_letter." ORDER BY fullname ASC ".$limit;
        $result = $this->db->query($query);
        return $result->result_array();
    }

    function UserCount($table,$where,$like=array(),$and = null,$letter){
        $where_condition = '';
        if(!empty($like))
        {
            foreach ($like as $key => $value){
                $where_condition[] = $key." LIKE '%".$value."%'";
            }

            $where_condition = ' AND (' .implode(" OR ",$where_condition).')';
        }else{
            $where_condition = '';
        }
        if($letter){
            $where_letter = ' AND fullname LIKE "'.$letter.'%"';
        }else{
            $where_letter = '';
        }
        if($and != null) {
            $where_status = ' AND status = ' . $and;
        }else{
            $where_status = ' AND status = 1';
        }
        if($and=='all'){
            $where_status = '';
        }
        $query = "SELECT * FROM ".$this->db->dbprefix.$table." WHERE `group_id` = '".$where."' ".$where_condition." ".$where_status." ".$where_letter." ORDER BY fullname ASC ";
        $result = $this->db->query($query);
        $return = $result->result_array();
        return count($return);
    }

    function UpdateRow($table,$data,$where = array())
    {
        $this->db->where($where);
        $return = $this->db->update($table,$data);
        return $return;
    }

    function Query($table,$where){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        $query = $this->db->get();
        return $query->result_array();
    }

    function QuerySingleRow($table,$where){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        $query = $this->db->get();
        /*echo $this->db->last_query();*/
        return $query->row();
    }

    

    function RowCount($table,$where = array(),$like = array()){
        $where_condition = '';
        if(!empty($like))
        {
            foreach ($like as $key => $value){
                $where_condition[] = $key." LIKE '%".$value."%'";
            }

            $where_condition = ' (' .implode(" OR ",$where_condition).')';
        }
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        if($where_condition!='')$this->db->where($where_condition);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return);
    }

    function InsertRow($tableName, $valuesArray) {
        $return = $this->db->insert($tableName,$valuesArray);
        return $return;
    }

    function DeleteRow($tableName, $valuesArray){
        $return = $this->db->delete($tableName, $valuesArray);
        return $return;
    }
	function InsertedID() {
        return $this->db->insert_id();
    }
    
	function QuerySingleRowLast($table,$where,$order_by = null,$sort = null,$like = null){
		$this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        if($like!=null) $this->db->like('payment_number', $like, '');
        if($order_by!=null) $this->db->order_by($order_by,$sort);
        //echo $this->db->last_query();
        $query = $this->db->get();
        return $query->row();
    }

    function Update($tableName,$data, $where = array()){
        $this->db->where($where);
        $return = $this->db->update($tableName,$data);
        /*echo $this->db->last_query();*/
        return $return;
    }

    function Count($table,$where = array(),$where_or = array())
    {
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        $this->db->or_where($where_or);
        $query = $this->db->get();
        $return = $query->result_array();
        //echo $this->db->last_query();die();
        return count($return)!=0?1:0;
    }
    
	function CountSingleValue($table,$where = array(),$where_or = array())
    {
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($where);
        $this->db->or_where($where_or);
        $query = $this->db->get();
        return $query->row();
    }

	function GetMembers($member_number = array(),$member_name = array(),$member_email = array(),$contact_number = array())
    {
        $this->db->select("*");
        $this->db->from('members');
        $this->db->where('booking_id > 0');
        $this->db->group_start();
        $this->db->like($member_number);
        $this->db->or_like($member_name);
        $this->db->or_like($member_email);
        $this->db->or_like($contact_number);
        $this->db->group_end();
        $query = $this->db->get();
        //echo $this->db->last_query();die();
        $member_data = $query->result_array();
        $booking_data = array();
        if($member_data){
        	foreach ($member_data as $member){
        		$this->db->select("*");
		        $this->db->from('booking');
		        $this->db->where('member_id = '.$member['id']);
		        $query_booking = $this->db->get();
		        if($query_booking){
		        	$booking_data[] = $query_booking->result_array();
		        }
        	}
        	
        	return call_user_func_array('array_merge', $booking_data);
        }else{
        	return $booking_data;
        }
        
    }
    
	function GetMembersBooking($member_number = array(),$member_name = array(),$member_email = array(),$contact_number = array())
    {
        $this->db->select("*");
        $this->db->from('members');
        $this->db->like($member_number);
        $this->db->or_like($member_name);
        $this->db->or_like($member_email);
        $this->db->or_like($contact_number);
        $query = $this->db->get();
        //echo $this->db->last_query();
        return $query->result_array();
    }
    
    /*language*/
	function get_all_language()
    {
        $this->db->select("*");
        $this->db->from('languages');
        $this->db->order_by('sort_order','ASC');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_language_detail($id)
    {
        $this->db->select('*');
        $this->db->from('languages');
        $this->db->where('language_id', $id);
        $query = $this->db->get();
        $return = $query->result_array();
        return count($return)!=0?$return[0]:null;
    }
    function language_manipulate($data,$id=null)
    {
        $this->db->trans_start();

        if (!isset($data['rtl'])) {
            $data['rtl']=0;
        }
        if (!isset($data['public'])) {
            $data['public']=0;
        }
        if (!isset($data['default'])) {
            $data['default']=0;
        }

        if ($this->session->userdata('group') != 1 && isset($data['default'])) {
            unset($data['default']);
        }
        if ($this->session->userdata('group') != 1 && isset($data['public'])) {
            unset($data['public']);
        }

        if(isset($data['default']) && $data['default']==1)
        {
            $this->db->set('default',0);
            $this->db->update('languages');
        }

        if($id!=null) // update
        {
            $this->db->where('language_id',$id);
            $this->db->update('languages',$data);
        }
        else	//add
        {
            $data['created_date']=time();
            $this->db->insert('languages',$data);
        }
        $this->db->trans_complete();
        // end TRANSACTION
        if ($this->db->trans_status() == FALSE)
        {
            return 0;
        }
        else
        {
            return 1;
        }
    }
    
	

}
