<?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 refmaterial
 *
 * @author Lenovo
 */
class Referencematerial {

    //put your code here
    private $conn;
    private $reference_material = "reference_material";
    private $reference_material_assignment = "reference_material_assignment";
    //
    public $reference_material_id;
    public $reference_title;
    public $reference_desc;
    public $reference_filename;
    public $status;
    public $created_ts;
    public $last_updated_ts;
    //
    public $user_id;
    public $group_id;
    //
    public $date;

    // constructor
    public function __construct() {
        $this->conn = DBConnection();
    }

    public function getRefMaterials($from_record_num, $records_per_page) {
        try {
            $query = "SELECT rm.reference_material_id,MIN(rma.group_id) as group_id,rm.reference_title,rm.reference_desc,rm.reference_filename,rm.last_updated_ts "
                    . "FROM reference_material_assignment as rma "
                    . "JOIN reference_material as rm on rma.reference_material_id=rm.reference_material_id "
                    . "Left join user_group_allocation as uga on rma.group_id=uga.group_id "
                    . "WHERE rm.status='Active' "
                    . "AND (IF(rma.group_id='0',rma.user_id=:user_id,rma.group_id=uga.group_id AND uga.user_id=:user_id1) "
                    . " or (IF(rm.department_id !='',(rma.group_id='0' and rma.user_id='0' AND rm.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id3)),(rma.group_id='0' and rma.user_id='0' AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))"
                    . "GROUP BY rm.reference_material_id "
                    . "ORDER BY rm.reference_material_id DESC LIMIT
                :start, :end";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id3", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id4", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    public function getRefMaterials_count() {
        try {
            $query = "SELECT  COUNT(rm.reference_material_id)"
                    . "FROM reference_material_assignment as rma "
                    . "JOIN reference_material as rm on rma.reference_material_id=rm.reference_material_id "
                    . "Left join user_group_allocation as uga on rma.group_id=uga.group_id "
                    . "WHERE rm.status='Active' "
                    . "AND (IF(rma.group_id='0',rma.user_id=:user_id,rma.group_id=uga.group_id AND uga.user_id=:user_id1) "
                    . " or (IF(rm.department_id !='',(rma.group_id='0' and rma.user_id='0' AND rm.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id3)),(rma.group_id='0' and rma.user_id='0' AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id3", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id4", $this->user_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

}
