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

    private $conn;
    private $notice_board = "notice_board";
    private $notice_board_assignment = "notice_board_assignment";
    //
    public $notice_board_id;
    public $notice_subject;
    public $notice_desc;
    public $start_date;
    public $end_date;
    public $created_ts;
    public $last_updated_ts;
    //
    public $user_id;
    public $group_id;
    //
    public $date;

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

 public function getNotices($from_record_num, $records_per_page) {
        try {
            $query = "SELECT  nb.notice_board_id,nb.notice_subject, nb.notice_desc, nb.end_date,nb.start_date,nb.created_ts,MIN(nbg.group_id) as group_id,
                    (Select nbg1.view_ts From notice_board_log as nbg1 Where nbg.notice_board_id=nbg1.notice_board_id AND nbg1.user_id=:user_id5 order by nbg1.view_ts desc limit 1) as view_ts
                    FROM notice_board_assignment as nbg 
                    JOIN notice_board as nb on nbg.notice_board_id=nb.notice_board_id 
                    Left join user_group_allocation as uga on  nbg.group_id=uga.group_id "
                    . "WHERE nb.end_date>=:date AND nb.start_date<=:date1  
                    and (IF(nbg.group_id='0',nbg.user_id=:user_id,nbg.group_id=uga.group_id AND uga.user_id=:user_id1)
                    OR (IF(nb.department_id != '',(nbg.group_id='0' and nbg.user_id='0' AND nb.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id3) ),(nbg.group_id='0' and nbg.user_id='0' AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))
                    GROUP BY notice_board_id 
                    ORDER BY nb.start_date 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(":user_id5", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":date", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            $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 getNotices_count() {
        try {
            $query = "SELECT COUNT(nb.notice_board_id)
            FROM notice_board_assignment as nbg 
            JOIN notice_board as nb on nbg.notice_board_id=nb.notice_board_id 
            Left join user_group_allocation as uga on  nbg.group_id=uga.group_id "
            . "WHERE nb.end_date>=:date AND nb.start_date<=:date1  
            and (IF(nbg.group_id='0',nbg.user_id=:user_id,nbg.group_id=uga.group_id AND uga.user_id=:user_id1)
            OR (IF(nb.department_id != '',(nbg.group_id='0' and nbg.user_id='0' AND nb.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id3) ),(nbg.group_id='0' and nbg.user_id='0' AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))
            GROUP BY nb.notice_board_id ";
           
            // 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(":date", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function insert_view_ts() {
        try {
            $query = "INSERT INTO
                notice_board_log
            SET
                notice_board_id = :notice_board_id ,
                user_id = :user_id,
                view_ts = :view_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":notice_board_id", $this->notice_board_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":view_ts", $this->datetime);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //for home page
    public function getNotices1() {
        try {
            $query = "SELECT  nb.notice_board_id,nb.notice_subject, nb.notice_desc, nb.end_date,nb.start_date,nb.created_ts,MIN(nbg.group_id) as group_id,
            (Select nbg1.view_ts From notice_board_log as nbg1 Where nbg.notice_board_id=nbg1.notice_board_id order by nbg1.view_ts desc limit 1) as view_ts
            FROM notice_board_assignment as nbg 
            JOIN notice_board as nb on nbg.notice_board_id=nb.notice_board_id 
            Left join user_group_allocation as uga on  nbg.group_id=uga.group_id "
            . "WHERE nb.end_date>=:date AND nb.start_date<=:date1  
            and (IF(nbg.group_id='0',nbg.user_id=:user_id,nbg.group_id=uga.group_id AND uga.user_id=:user_id1)
            OR (IF(nb.department_id != '',(nbg.group_id='0' and nbg.user_id='0' AND nb.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id3) ),(nbg.group_id='0' and nbg.user_id='0' AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))
            GROUP BY notice_board_id 
            ORDER BY nb.start_date DESC LIMIT
                3";
            // 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(":date", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);        
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

   

    public function getNotices_unreadcount() {
        try {
            $query = "select count(*) from (SELECT nb.notice_board_id,nb.notice_subject, nb.notice_desc, nb.end_date,nb.start_date,nb.created_ts,MIN(nbg.group_id) as group_id,
            (Select nbg1.view_ts From notice_board_log as nbg1 Where nbg.notice_board_id=nbg1.notice_board_id order by nbg1.view_ts desc limit 1) as view_ts
            FROM notice_board_assignment as nbg 
            JOIN notice_board as nb on nbg.notice_board_id=nb.notice_board_id 
            Left join user_group_allocation as uga on  nbg.group_id=uga.group_id "
            . "WHERE nb.end_date>=:date AND nb.start_date<=:date1  
            and (IF(nbg.group_id='0',nbg.user_id=:user_id,nbg.group_id=uga.group_id AND uga.user_id=:user_id1)
            OR (IF(nb.department_id != '',(nbg.group_id='0' and nbg.user_id='0' AND nb.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id3) ),(nbg.group_id='0' and nbg.user_id='0' AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))
            GROUP BY notice_board_id 
            ORDER BY nb.start_date) as count where view_ts is null";
            // 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(":date", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            // 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");
        }
    }

  
}
