<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

/*
 * 	@author : themetic.net
 * 	date	: 21 April, 2015
 * 	Inventory & Invoice Management System
 * 	http://themetic.net
 *  version: 1.0
 */

class User_Model extends MY_Model
{

    public $_table_name;
    public $_order_by;
    public $_primary_key;

    public function select_user_roll_by_id($user_id)
    {
        $this->db->select('tbl_user_role.*', false);
        $this->db->select('tbl_menu.*', false);
        $this->db->from('tbl_user_role');
        $this->db->join('tbl_menu', 'tbl_user_role.menu_id = tbl_menu.menu_id', 'left');
        $this->db->where('tbl_user_role.user_id', $user_id);
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

    public function get_new_user()
    {
        $post = new stdClass();
        $post->user_name = '';
        $post->name = '';
        $post->email = '';
        $post->flag = 3;
        $post->employee_login_id = '';

        return $post;
    }

    public function get_user($filterBy = null)
    {
        $users = array();
        $all_users = array_reverse($this->get_permission('tbl_users'));
        if (empty($filterBy)) {
            return $all_users;
        } else {
            foreach ($all_users as $v_users) {
                if ($filterBy == 'admin' && $v_users->role_id == 1) {
                    array_push($users, $v_users);
                }
                if ($filterBy == 'client' && $v_users->role_id == 2) {
                    array_push($users, $v_users);
                }
                if ($filterBy == 'staff' && $v_users->role_id == 3) {
                    array_push($users, $v_users);
                }
                if ($filterBy == 'active' && $v_users->activated == 1) {
                    array_push($users, $v_users);
                }
                if ($filterBy == 'deactive' && $v_users->activated == 0) {
                    array_push($users, $v_users);
                }
                if ($filterBy == 'banned' && $v_users->banned == 1) {
                    array_push($users, $v_users);
                }
            }
        }
        return $users;
    }

    public function get_shift_by_user_id($user_id,$month=null,$project_id=null)
    {   
        if(empty($month)){
            $month = date('Y-m');
        }

        $date = new DateTime($month . '-01');
        $start_date = $date->modify('first day of this month')->format('Y-m-d');
        $end_date = $date->modify('last day of this month')->format('Y-m-d');
        

        if(!empty($project_id)){
            $this->db->select('tbl_shift_mapping.*, tbl_account_details.fullname, tbl_shift.shift_name, tbl_shift.start_time, tbl_shift.end_time, tbl_shift.project_id');
        }else{
            $this->db->select('tbl_shift_mapping.*, tbl_shift.shift_name, tbl_shift.start_time, tbl_shift.end_time');
        }
        
        $this->db->from('tbl_shift_mapping');
        $this->db->join('tbl_shift', 'tbl_shift_mapping.shift_id = tbl_shift.shift_id', 'left');

        if(!empty($project_id)){
            $this->db->join('tbl_account_details', 'tbl_shift_mapping.s_user_id = tbl_account_details.user_id', 'left');
            $this->db->where('tbl_shift.project_id', $project_id);

        }else{
            $this->db->where('tbl_shift_mapping.s_user_id', $user_id);
        }
        $this->db->where('tbl_shift_mapping.start_date >=', $start_date);
        $this->db->where('tbl_shift_mapping.end_date <=', $end_date);
        
        $this->db->order_by('tbl_shift_mapping.shift_mapping_id', 'desc');
        return $this->db->get()->result();
    }

    public function get_expense_info($id) {

        // get all expense from tbl_expense_items and tbl_transactions
        // where tbl_transactions.project_id != 0 and tbl_expense_items.user_id = $id 
        // and tbl_expense_items.module = 'expense' and sum of tbl_expense_items.total_amount

        $this->db->query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
        // $this->db->select('tbl_expense_items.*, tbl_transactions.name,tbl_transactions.project_id,tbl_transactions.amount as expense_amount,sum(tbl_expense_items.total_amount) as total_amount, tbl_project.project_name');        

        // $this->db->select('tbl_expense_items.*, tbl_transactions.name,tbl_transactions.project_id,tbl_transactions.amount as expense_amount,sum(tbl_expense_items.other) as total_amount, tbl_project.project_name');

        // select all columns from tbl_expense_items and tbl_transactions.name,tbl_transactions.project_id,tbl_transactions.amount as expense_amount,
        // sum with COALESCE two feild one is tbl_expense_items.total_amount and other is tbl_expense_items.other

        if (!$this->db->table_exists('tbl_expense_items')) {
            $this->db->select("
                tbl_transactions.transactions_id as module_id,
                'expense' as module,
                tbl_transactions.name,
                tbl_transactions.project_id,
                tbl_transactions.amount as expense_amount,
                COALESCE(tbl_transactions.amount, 0) as expense_total_amount,
                COALESCE(tbl_transactions.amount, 0) as total_amount,
                tbl_project.project_name
            ", false);
            $this->db->from('tbl_transactions');
            $this->db->join('tbl_project', 'tbl_transactions.project_id = tbl_project.project_id', 'left');
            $this->db->where('tbl_transactions.project_id !=', 0);
            $this->db->where('tbl_transactions.type', 'Expense');
            if ($this->db->field_exists('added_by', 'tbl_transactions')) {
                $this->db->group_start();
                $this->db->where('tbl_transactions.added_by', $id);
                $this->db->or_where('tbl_transactions.paid_by', $id);
                $this->db->group_end();
            } else {
                $this->db->where('tbl_transactions.paid_by', $id);
            }
            $this->db->order_by('tbl_transactions.transactions_id', 'desc');
            return $this->db->get()->result();
        }

        $this->db->select('tbl_expense_items.*, tbl_transactions.name,tbl_transactions.project_id,tbl_transactions.amount as expense_amount,COALESCE(sum(tbl_expense_items.total_amount),0) as expense_total_amount,COALESCE(sum(tbl_expense_items.other),0) as total_amount, tbl_project.project_name');
        
        $this->db->from('tbl_expense_items');
        $this->db->join('tbl_transactions', 'tbl_expense_items.module_id = tbl_transactions.transactions_id');
        $this->db->join('tbl_project', 'tbl_transactions.project_id = tbl_project.project_id');
        $this->db->where('tbl_expense_items.user_id', $id);        
        // $this->db->group_by('tbl_transactions.project_id');
        $this->db->group_by('tbl_expense_items.module_id');
        $this->db->order_by('tbl_expense_items.expense_items_id', 'desc');
        $result = $this->db->get()->result();
        return $result;

        // commision
        // vip_table_income
        // entred_income
        // fond_out
        // stock_event_sales


        // not include in 
        // vip_table
        // list_stock
    }


}
