<?php

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

class M_user extends CI_Model {

    function getUserData($id) {
        $this->db->select('username,email,total_balance,user_image.image');
        $this->db->join('user_image', 'user_image.user_id=bills_user.id and user_image.profile_pic=1', 'left');
        return $this->db->get_where('bills_user', array('bills_user.id' => $id))->row_array();
    }

    function userPoints($id) {
        $this->db->select('total_balance,total_points');
        return $this->db->get_where('bills_user', array('id' => $id))->row_array();
    }

    function userPointsHistory($id) {
        $this->db->select('points,balance,update_on as date');
        return $this->db->get_where('pts_student_history', array('stud_id' => $id))->result_array();
    }

    function userAwards($id) {
        $this->db->select('pts_award.award_name,pts_award_history.award_history_point,pts_award_history.memo as notes,pts_award_history.award_status,date(pts_award_history.h_update_on) as date');
        $this->db->join('pts_award', 'pts_award.id = pts_award_history.award_id');
        return $this->db->get_where('pts_award_history', array('user_id' => $id))->result_array();
    }

    function get_awards_list() {
        $this->db->select('id,award_name');
        return $this->db->get_where('pts_award', array('award_status' => 1))->result();
    }

    function getAwardById($award_id) {
        return $this->db->where('id', $award_id)->get('pts_award')->row();
    }

    function userSubscriptions($id) {
        $this->db->select('bills_producer.name,bills_orders.date_now as date,bills_orders.id_orders as num,bills_orders.start_date,'
                . 'bills_orders.expiry_date,bills_orders.active');
        $this->db->join('bills_producer', 'bills_producer.id=bills_orders.id_producer and bills_producer.type=2');
        return $this->db->get_where('bills_orders', array('bills_orders.id_user' => $id))->result_array();
    }

    function userPersonalInfo($id) {
        $this->db->select('bills_user.name,bills_user.email,bills_user.phone,bills_user.phone_father,user_image.image');
        $this->db->join('user_image', 'user_image.user_id=bills_user.id and user_image.profile_pic = 1', 'left');
        $user = $this->db->get_where('bills_user', array('id' => $id))->row();
        if ($user) {
            $user->addresses = new stdClass();
            $this->db->select('region,blocks,street,jadah,building_num,floor,door_no,address_link');
            $user->addresses = $this->db->get_where('addresses', array('user_id' => $id))->result();
            if ($user->image) {
                $user->image = base_url('public/upload/userimage/' . $user->image);
            }
            return $user;
        }
        return FALSE;
    }

    function userTsme3Counts($id, $type, $date, $rating) {
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 0 THEN quran_tsme3.pages_count ELSE 0 END) AS hfz');
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 1 THEN quran_tsme3.pages_count ELSE 0 END) AS mrg3a');
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 2 THEN quran_tsme3.pages_count ELSE 0 END) AS srd');
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 3 THEN quran_tsme3.pages_count ELSE 0 END) AS tlawa');
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 4 THEN quran_tsme3.pages_count ELSE 0 END) AS tlken');
        $this->db->select('SUM(CASE WHEN quran_tsme3.type = 5 THEN quran_tsme3.pages_count ELSE 0 END) AS test');
        if ($type != -1) {
            $this->db->where('quran_tsme3.type', $type);
        }
        if ($rating) {
            $this->db->where('quran_tsme3.evaluation', $rating);
        }
        $this->db->join('quraan_session_students q_s', 'q_s.session_id = quran_tsme3.product_id and q_s.student_id = quran_tsme3.student');
        if ($date) {
            $this->db->where('date(q_s.created_at)', $date);
        }
        return $this->db->get_where('quran_tsme3', array('quran_tsme3.student' => $id))->row();
    }

    function userQuraanSessions($id, $type, $date, $rating) {
        $this->db->select('quraan_sessions.id,quraan_sessions.title as session_title,q_s.created_at as join_date');
        $this->db->where('q_s.student_id', $id);
        if ($date) {
            $this->db->where('date(q_s.created_at)', $date);
        }
        $this->db->join('quraan_session_students q_s', 'q_s.session_id = quraan_sessions.id');
        $sessions = $this->db->get('quraan_sessions')->result();
        foreach ($sessions as $session) {
            //teachers
            $session->teachers = new stdClass();
            $this->db->select('teachers.name');
            $this->db->join('teachers', 'teachers.id = q_t.teacher_id');
            $session->teachers = $this->db->get_where('quraan_session_teachers q_t', array('q_t.session_id' => $session->id))->result();

            //tsme3 page count
            $session->tsme3_pages_count = new stdClass();
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 0 THEN quran_tsme3.pages_count ELSE 0 END) AS hfz');
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 1 THEN quran_tsme3.pages_count ELSE 0 END) AS mrg3a');
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 2 THEN quran_tsme3.pages_count ELSE 0 END) AS srd');
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 3 THEN quran_tsme3.pages_count ELSE 0 END) AS tlawa');
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 4 THEN quran_tsme3.pages_count ELSE 0 END) AS tlken');
            $this->db->select('SUM(CASE WHEN quran_tsme3.type = 5 THEN quran_tsme3.pages_count ELSE 0 END) AS test');
            $this->db->select('avg(evaluation) as avg_score');
            if ($type != -1) {
                $this->db->where('quran_tsme3.type', $type);
            }
            if ($rating) {
                $this->db->where('quran_tsme3.evaluation', $rating);
            }
            if ($date) {
                $this->db->where('date(q_s.created_at)', $date);
            }
            $this->db->join('quraan_session_students q_s', 'q_s.session_id = quran_tsme3.product_id and q_s.student_id = quran_tsme3.student');
            $session->tsme3_pages_count = $this->db->get_where('quran_tsme3', array('quran_tsme3.product_id' => $session->id, 'quran_tsme3.student' => $id))->row();
        }
        return $sessions;
    }

    function userSessionDetails($user_id, $session_id) {
        $this->db->select('quraan_sessions.id,quraan_sessions.title as session_title,q_s.created_at as join_date');
        $this->db->join('quraan_session_students q_s', 'q_s.session_id = quraan_sessions.id and q_s.student_id =' . $user_id);
        $session = $this->db->get_where('quraan_sessions', array('quraan_sessions.id' => $session_id))->row();
        if ($session) {
            $session->teachers = new stdClass();
            $this->db->select('teachers.name');
            $this->db->join('teachers', 'teachers.id = q_t.teacher_id');
            $session->teachers = $this->db->get_where('quraan_session_teachers q_t', array('q_t.session_id' => $session->id))->result();

            $session->tsme3 = new stdClass();
            $this->db->select('quran_tsme3.type,from.arabic as from_surah,to.arabic as to_surah,from_ayah,to_ayah,evaluation,lines_count,notes');
            $this->db->join('quran_surah from', 'from.id = quran_tsme3.from_surah');
            $this->db->join('quran_surah to', 'to.id = quran_tsme3.to_surah');
            $session->tsme3 = $this->db->get_where('quran_tsme3', array('quran_tsme3.product_id' => $session_id, 'quran_tsme3.student' => $user_id))->result();
        }
        return $session;
    }

    function update_user_profile($data, $user_id) {
        $this->db->trans_start();
        $this->db->update('bills_user', $data, array('id' => $user_id));
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return FALSE;
        }
        return TRUE;
    }

    function get_data($type) {
        $this->db->select('id,arabic,english');
        return $this->db->get_where('datas', array('lable' => $type))->result();
    }

    function get_data_region($region_id) {
        $row = $this->db->get_where('datas', array('id' => $region_id))->row();
        if ($row) {
            return $row->arabic;
        }
        return FALSE;
    }

    function add_user_address($data) {
        $this->db->insert('addresses', $data);
        if ($this->db->affected_rows()) {
            return TRUE;
        }
        return FALSE;
    }

    function update_user_address($data, $address_id) {
        $this->db->trans_start();
        $this->db->update('addresses', $data, array('id' => $address_id));
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return FALSE;
        }
        return TRUE;
    }

    function checkIfThisUserAddress($address_id, $user_id) {
        $row = $this->db->get_where('addresses', array('id' => $address_id, 'user_id' => $user_id))->row();
        if ($row) {
            return TRUE;
        }
        return FALSE;
    }

    function get_invoices($user_id) {
        $this->db->select('bills_orders.id_orders as inv_num,bills_orders.name as customer_name,sum(bills_orders.discount_remainder) as total_amount');
        $this->db->select('addresses.region,addresses.blocks,addresses.street,addresses.jadah,addresses.building_num,addresses.floor,addresses.door_no');
        $this->db->group_by('bills_orders.id_orders,bills_orders.name');
        $this->db->join('orders', 'orders.id = bills_orders.id_orders');
        $this->db->join('addresses', 'orders.address_id = addresses.id', 'left');
        return $this->db->get_where('bills_orders', array('id_user' => $user_id))->row();
    }

    function checkIfThisUserInvoice($inv_num, $user_id) {
        $row = $this->db->get_where('bills_orders', array('id_user' => $user_id, 'id_orders' => $inv_num))->row();
        if ($row) {
            return TRUE;
        }
        return FALSE;
    }

    function get_invoice($inv_num) {
        $this->db->select('bills_orders.id_orders as inv_num,bills_orders.name as customer_name,sum(bills_orders.discount_remainder) as total_amount,date(orders.timestamp) as date,avg(bills_orders.paid) as paid');
        $this->db->select('bills_user.phone,bills_user.email');
        $this->db->select('addresses.region,addresses.blocks,addresses.street,addresses.jadah,addresses.building_num,addresses.floor,addresses.door_no');
        $this->db->group_by('bills_orders.id_orders,bills_orders.name');
        $this->db->join('bills_user', 'bills_user.id = bills_orders.id_user');
        $this->db->join('orders', 'orders.id = bills_orders.id_orders');
        $this->db->join('addresses', 'orders.address_id = addresses.id', 'left');
        $inv = $this->db->get_where('bills_orders', array('bills_orders.id_orders' => $inv_num))->row();
        if ($inv->paid == 1) {
            $inv->paid = 'paid';
        } else {
            $inv->paid = 'Not paid';
        }

        // $inv->products = new stdClass();
        $this->db->select('name_order as product,item_price,quantity,discount,discount_remainder as amount_after_discount,start_date,expiry_date');
        $inv->products = $this->db->get_where('bills_orders', array('id_orders' => $inv_num))->result();

        $this->db->select('inv_footer as terms_conditions, inv_footer_ar as terms_conditions_ar');
        $inv->terms = $this->db->get('setting')->row();

        return $inv;
    }

    function getMembershipProducts() {
        $this->db->select('id,name');
        return $this->db->get_where('bills_producer', array('active' => 1, 'type' => 2))->result();
    }

    function getStatisAttendance($user_id, $product, $date, $present) {

        if ($product) {
            $this->db->where('product_id', $product);
        }
        if ($date) {
            $this->db->where('date', $date);
        }
        if ($present) {
            $type = '';
            switch ($present) {
                case 'present':
                    $type = 1;
                    break;
                case 'absent':
                    $type = 0;
                    break;
                case 'late':
                    $type = 2;
                    break;
                default:
                    break;
            }
            if ($type != '') {
                $this->db->where('present', $type);
            }
        }
        $this->db->select("SUM(CASE WHEN present = '0' THEN 1 ELSE 0 END) AS absent");
        $this->db->select("SUM(CASE WHEN present = '1' THEN 1 ELSE 0 END) AS present");
        $this->db->select("SUM(CASE WHEN present = '2' THEN 1 ELSE 0 END) AS late");
        return $this->db->get_where('gyab', array('user_id' => $user_id))->row();
    }

    function getAttendance($user_id, $product, $date, $present) {
        if ($product) {
            $this->db->where('product_id', $product);
        }
        if ($date) {
            $this->db->where('date', $date);
        }
        if ($present) {
            $type = '';
            switch ($present) {
                case 'present':
                    $type = 1;
                    break;
                case 'absent':
                    $type = 0;
                    break;
                case 'late':
                    $type = 2;
                    break;
                default:
                    break;
            }
            if ($type != '') {
                $this->db->where('present', $type);
            }
        }
        $this->db->select("IF(gyab.present = 0, 'absent', IF(gyab.present = 1, 'present','late')  ) AS type");
        $this->db->select('gyab.date,bills_producer.name as product_name');
        $this->db->join('bills_producer', 'bills_producer.id=gyab.product_id');
        return $this->db->get_where('gyab', array('user_id' => $user_id))->result();
    }

    function get_msgs($user_id) {
        $this->db->select('msg,bills_producer.name as product_name');
        $this->db->where_in('students', $user_id);
        $this->db->where(array('announcement.start_date <=' => date('Y-m-d'), 'announcement.end_date >= ' => date('Y-m-d')));
        $this->db->join('bills_producer', 'bills_producer.id=announcement.product_id');
        return $this->db->get('announcement')->result();
    }
    
    function get_announc() {
        $this->db->select('msg,bills_producer.name as product_name');
        $this->db->where('students', '');
        $this->db->where(array('announcement.start_date <=' => date('Y-m-d'), 'announcement.end_date >= ' => date('Y-m-d')));
        $this->db->join('bills_producer', 'bills_producer.id=announcement.product_id');
        return $this->db->get('announcement')->result();
    }

}
