<?php

use Doctrine\ORM\EntityRepository;

class AttendanceRepository extends EntityRepository
{

    
  

   


    public function checkAttendance($email, $body, $current_session)
    {
        $course = addslashes(strip_tags(trim($body['course'])));
        $section = addslashes(strip_tags(trim($body['section'])));
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        $period_number = addslashes(strip_tags(trim($body['period_number'])));
        $by_username = $email;
        $date1 = date('d-m-Y');

        $dql = 'SELECT r FROM Attendance r WHERE r.course=:course  AND r.section=:section AND r.dept_code=:dept_code AND r.sub_code=:sub_code AND r.semester=:semester AND r.by_username=:by_username AND r.date1=:date1 AND r.day=:day AND r.session=:current_session AND r.period_number=:period_number';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('course', $course)
            ->setParameter('section', $section)
            ->setParameter('dept_code', $dept_code)
            ->setParameter('semester', $semester)
            ->setParameter('by_username', $email)
            ->setParameter('date1', $date1)
            ->setParameter('current_session', $current_session)
            ->setParameter('day', $day)
            ->setParameter('period_number', $period_number)
            ->setParameter('sub_code', $sub_code);
        return $query->getResult();
    }

    public function checkAttendanceAll($email, $body, $current_session)
    {

        $course = addslashes(strip_tags(trim($body['course'])));
//        $stream = addslashes(strip_tags(trim($body['stream'])));
        $section = addslashes(strip_tags(trim($body['section'])));
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        $period_number = addslashes(strip_tags(trim($body['period_number'])));
        $by_username = $email;

        $dql = 'SELECT r FROM Attendance r WHERE r.course=:course AND r.section=:section AND r.dept_code=:dept_code AND r.sub_code=:sub_code AND r.semester=:semester AND r.by_username=:by_username AND r.session=:current_session AND r.day=:day AND r.period_number=:period_number';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('course', $course)
            
            ->setParameter('section', $section)
            ->setParameter('dept_code', $dept_code)
            ->setParameter('semester', $semester)
            ->setParameter('by_username', $email)
            ->setParameter('current_session', $current_session)
            ->setParameter('day', $day)
            ->setParameter('period_number', $period_number)
            ->setParameter('sub_code', $sub_code);
        return $query->getResult();
    }

    public function fetchReports($data, $body, $current_session)
    {
        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $section = addslashes(strip_tags(trim($body['section'])));
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));

        //fetch all data
        $dql = 'SELECT r FROM Attendance r WHERE r.course=:course AND r.stream=:stream AND r.section=:section AND r.dept_code=:dept_code AND r.sub_code=:sub_code AND r.semester=:semester AND r.session=:current_session';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('course', $course)
            ->setParameter('stream', $stream)
            ->setParameter('section', $section)
            ->setParameter('dept_code', $dept_code)
            ->setParameter('semester', $semester)
            ->setParameter('current_session', $current_session)
            ->setParameter('sub_code', $sub_code);
        $result = $query->getResult();

        $total_class = sizeof($result); //total class

        if ($total_class > 0) {
            $b = array();
            foreach ($data as $d) {
                $c = array();
                $c['email'] = $d['email'];
                $c['roll_no'] = $d['roll_no'];
                $c['name'] = $d['name'];
                $c['total_class'] = $total_class;
                $c['attended_class'] = $this->attendedClassNo($body, $d['email'], true, $current_session);
                $c['not_attended_class'] = $this->attendedClassNo($body, $d['email'], false, $current_session);

                $per = ($this->attendedClassNo($body, $d['email'], true, $current_session) / $total_class) * 100;

                $c['percentage'] = (int) $per;
                array_push($b, $c);
            }

            return $b;

        } else {
            return false;
        }

    }

    public function fetchReportByRoll($data, $body, $current_session)
    {

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $dd_code = addslashes(strip_tags(trim($body['dept_code'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $name = addslashes(strip_tags(trim($body['name'])));
        $roll_no = addslashes(strip_tags(trim($body['roll_no'])));
        $subjects = $body['subjects'];

        $tot = [];
        $total_count = 0;
        $total_attended = 0;
        $t_class = 0;

        foreach ($subjects as $s) {

            $sub_code = $s['value'];
            $dept_code = $s['dept_code'];

            $dql = 'SELECT r FROM Attendance r WHERE r.course=:course AND  r.sub_code=:sub_code AND r.semester=:semester AND r.session=:current_session';
            $query = $this->getEntityManager()
                ->createQuery($dql)
                ->setParameter('course', $course)
            //->setParameter('stream', $stream)
            //->setParameter('dept_code', $dept_code)
                ->setParameter('sub_code', $sub_code)
                ->setParameter('current_session', $current_session)
                ->setParameter('semester', $semester);
            $result = $query->getResult();
            $total_count = $total_count + sizeof($result);

            if (sizeof($result) > 0) {

                foreach ($result as $r) {
                    $att = $r->getAttendance();
                    foreach ($att as $a) {
                        if ($a['email'] == $email) {
                            $t_class++;
                        }
                        if ($a['email'] == $email && $a['status'] == true) {
                            $total_attended++;
                        }
                    }

                }

            }

        }

        $b = array();
        $b['email'] = $email;
        $b['roll_no'] = $roll_no;
        $b['name'] = $name;
        $b['course'] = $course;
        $b['stream'] = $stream;
        $b['semester'] = $semester;
        $b['dept_code'] = $dd_code;
        $b['total_class'] = $t_class;
        $b['attended_class'] = $total_attended;
        $b['not_attended_class'] = $t_class - $total_attended;
        if ($t_class > 0) {
            $per = ($total_attended / $t_class) * 100;

            $b['percentage'] = (int) $per;
        } else {
            $b['percentage'] = 0;
        }

        return $b;

    }

    public function fetchReportByDateSingle($start_date, $end_date, $body, $sub_code)
    {
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $id = addslashes(strip_tags(trim($body['id'])));
        //$stream = addslashes(strip_tags(trim($body['stream'])));

        $sec = $this->getEntityManager()->getRepository('Section')->getSemSection($id, $sub_code);

        if ($sec != null) {
            $sql = "SELECT attendance from attendance WHERE sub_code=:sub_code AND section=:section AND semester=:semester AND STR_TO_DATE(date1,'%d-%m-%Y')>=STR_TO_DATE(:start_date, '%Y-%m-%d') AND STR_TO_DATE(date1,'%d-%m-%Y')<=STR_TO_DATE(:end_date, '%Y-%m-%d')";
            $em = $this->getEntityManager();
            $stmt = $em->getConnection()->prepare($sql);
            $stmt->bindParam(':section', $sec, PDO::PARAM_STR);
            $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
            $stmt->bindParam(':semester', $semester, PDO::PARAM_INT);
            $stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
            $stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
            //$stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
            $stmt->execute();
            $r = $stmt->fetchAll();

        } else {
            $sql = "SELECT attendance from attendance WHERE sub_code=:sub_code AND semester=:semester AND STR_TO_DATE(date1,'%d-%m-%Y')>=STR_TO_DATE(:start_date, '%Y-%m-%d') AND STR_TO_DATE(date1,'%d-%m-%Y')<=STR_TO_DATE(:end_date, '%Y-%m-%d')";
            $em = $this->getEntityManager();
            $stmt = $em->getConnection()->prepare($sql);
            $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
            $stmt->bindParam(':semester', $semester, PDO::PARAM_INT);
            $stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
            $stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
            //$stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
            $stmt->execute();
            $r = $stmt->fetchAll();

        }

        $total_count = 0;
        $total_attended = 0;
        $t_class = 0;

        $total_count = $total_count + sizeof($r);
        foreach ($r as $r1) {
            $attn = json_decode($r1['attendance'], true);
                foreach ($attn as $at) {
                    if ($at['id'] == $id) {
                        
                        $t_class++;

                        if ($at['status'] == true) {
                            $total_attended++;
                        }

                    }
                }
            
        }

        $b = array();

        $b['total_class'] = $t_class;
        $b['attended_class'] = $total_attended;
        $b['not_attended_class'] = $t_class - $total_attended;
        if ($t_class > 0) {
            $per = ($total_attended / $t_class) * 100;

            $b['percentage'] = (int) $per;
        } else {
            $b['percentage'] = 0;
        }

        return $b;

    }

    public function fetchReportByDate($start_date, $end_date, $body)
    {

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $dd_code = addslashes(strip_tags(trim($body['dept_code'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $name = addslashes(strip_tags(trim($body['name'])));
        $roll_no = addslashes(strip_tags(trim($body['roll_no'])));
        $subjects = $body['subjects'];

        $tot = [];
        $total_count = 0;
        $total_attended = 0;
        $t_class = 0;

        if ($body['sub_code'] != '') {
            $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
            //$section = addslashes(strip_tags(trim($body['section'])));

            $sql = "SELECT * from attendance WHERE json_contains(attendance->'$[*].email', json_array(:email)) AND course=:course AND stream=:stream AND dept_code=:dept_code AND sub_code=:sub_code AND semester=:semester AND r.section=:section AND r.date1>=:start_date AND r.date1<=:end_date";
            $em = $this->getEntityManager();
            $stmt = $em->getConnection()->prepare($sql);
            $stmt->bindParam(':semester', $semester, PDO::PARAM_INT);
            $stmt->bindParam(':email', $email, PDO::PARAM_STR);
            //$stmt->bindParam(':status', $status, PDO::PARAM_BOOL);
            $stmt->bindParam(':course', $course, PDO::PARAM_STR);
            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
            $stmt->bindParam(':section', $section, PDO::PARAM_STR);
            $stmt->bindParam(':dept_code', $dd_code, PDO::PARAM_STR);
            $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
            $stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
            $stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);

            $stmt->execute();
            $r = $stmt->fetchAll();
            $total_count = $total_count + sizeof($r);
            foreach ($r as $r1) {
                $attn = json_decode($r1['attendance'], true);

                foreach ($attn as $at) {
                    if ($at['email'] == $email) {
                        $t_class++;
                        if ($at['status'] == true) {
                            $total_attended++;
                        }

                    }
                }
            }
            //return $count;
        } else {

            foreach ($subjects as $s) {

                $sub_code = $s['value'];
                $dept_code = $s['dept_code'];

                $dql = 'SELECT r FROM Attendance r WHERE r.course=:course AND  r.sub_code=:sub_code AND r.semester=:semester AND r.date1>=:start_date AND r.date1<=:end_date';
                $query = $this->getEntityManager()
                    ->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('sub_code', $sub_code)
                    ->setParameter('semester', $semester)
                    ->setParameter('start_date', $start_date)
                    ->setParameter('end_date', $end_date);
                $result = $query->getResult();
                $total_count = $total_count + sizeof($result);

                if (sizeof($result) > 0) {

                    foreach ($result as $r) {
                        $att = $r->getAttendance();
                        foreach ($att as $a) {
                            if ($a['email'] == $email) {
                                $t_class++;

                            }
                            if ($a['email'] == $email && $a['status'] == true) {
                                $total_attended++;
                            }
                        }

                    }

                }

            }

        }

        $b = array();

        $b['total_class'] = $t_class;
        $b['attended_class'] = $total_attended;
        $b['not_attended_class'] = $t_class - $total_attended;
        if ($t_class > 0) {
            $per = ($total_attended / $t_class) * 100;

            $b['percentage'] = (int) $per;
        } else {
            $b['percentage'] = 0;
        }

        return $b;

    }

    public function fetchReportByRollSubjectWise($email, $body, $current_session)
    {

        $entityManager = $this->getEntityManager();

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $dd_code = addslashes(strip_tags(trim($body['dept_code'])));
        $name = addslashes(strip_tags(trim($body['name'])));
        $roll_no = addslashes(strip_tags(trim($body['roll_no'])));
        $subjects = $body['subjects'];

        $tot = [];
        $total_count = 0;
        $total_attended = 0;
        $t_class = 0;

        $b = array();

        $subb = array();

        foreach ($subjects as $s) {

            $new_data = [];

            $total_count = 0;
            $total_attended = 0;
            $t_class = 0;

            $sub_code = $s['value'];
            $dept_code = $s['dept_code'];

            $dql = 'SELECT r FROM Attendance r WHERE r.course=:course AND  r.sub_code=:sub_code AND r.semester=:semester AND r.session=:current_session';
            $query = $this->getEntityManager()
                ->createQuery($dql)
                ->setParameter('course', $course)
            //->setParameter('stream', $stream)
            //->setParameter('dept_code', $dept_code)
                ->setParameter('sub_code', $sub_code)
                ->setParameter('current_session', $current_session)
                ->setParameter('semester', $semester);
            $result = $query->getResult();
            $total_count = $total_count + sizeof($result);

            if (sizeof($result) > 0) {

                foreach ($result as $r) {
                    $att = $r->getAttendance();
                    foreach ($att as $a) {
                        if ($a['email'] == $email) {
                            $t_class++;
                        }
                        if ($a['email'] == $email && $a['status'] == true) {
                            $total_attended++;
                        }
                    }

                }

            }

            //for every subject push the values
            $new_data['sub_code'] = $sub_code;
            $new_data['sub_name'] = $entityManager->getRepository('SubjectMaster')->getSubjectName($sub_code);
            $new_data['dept_code'] = $dept_code;
            $new_data['total_count'] = $t_class;
            $new_data['total_attended'] = $total_attended;
            $new_data['total_absent'] = (int) $t_class - (int) $total_attended;
            if ($t_class != 0) {
                $new_data['percentage'] = (int) (($total_attended / $t_class) * 100);
            } else {
                $new_data['percentage'] = 0;
            }

            array_push($subb, $new_data);

        }

        $b['email'] = $email;
        $b['roll_no'] = $roll_no;
        $b['name'] = $name;
        $b['course'] = $course;
        $b['stream'] = $stream;
        $b['semester'] = $semester;
        $b['dept_code'] = $dd_code;
        $b['table_data'] = $subb;

        return $b;

    }

    
 

    


    public function fetchReportByDateSingleByDate($current_session, $body, $sub_code)
    {
        $semester = addslashes(strip_tags(trim($body['semester'])));

        $id = addslashes(strip_tags(trim($body['id'])));
        $start_date = addslashes(strip_tags(trim($body['start_date'])));
        $end_date = addslashes(strip_tags(trim($body['end_date'])));

        // json_contains(attendance->'$[*].email', json_array(:email))
        $sql = "SELECT attendance from attendance WHERE sub_code=:sub_code AND semester=:semester AND STR_TO_DATE(date1,'%d-%m-%Y') >= STR_TO_DATE(:start_date,'%Y-%m-%d') AND STR_TO_DATE(date1,'%d-%m-%Y') <= STR_TO_DATE(:end_date,'%Y-%m-%d')";
        $em = $this->getEntityManager();
        $stmt = $em->getConnection()->prepare($sql);
        //$stmt->bindParam(':email', $email, PDO::PARAM_STR);
        $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
        $stmt->bindParam(':semester', $semester, PDO::PARAM_INT);
        $stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
        $stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
        $stmt->execute();
        $r = $stmt->fetchAll();

        $total_count = 0;
        $total_attended = 0;
        $t_class = 0;

        $total_count = $total_count + sizeof($r);
        foreach ($r as $r1) {
            $attn = json_decode($r1['attendance'], true);
            
            foreach ($attn as $at) {
                if ((int)$at['id'] == (int)$id) {
                    $t_class++;     
                }
                if ((int)$at['id'] == (int)$id && $at['status'] == true) {
                    $total_attended++;
                }
            }
        }

        $b = array();

        $b['total_class'] = $t_class;
        $b['attended_class'] = $total_attended;
        $b['not_attended_class'] = $t_class - $total_attended;
        if ($t_class > 0) {
            $per = ($total_attended / $t_class) * 100;

            $b['percentage'] = (int) $per;
        } else {
            $b['percentage'] = 0;
        }

        return $b;

    }

    public function getEmployeeBySubject($id,$subjects,$current_session){

        $teacher_arr = [];

        foreach ($subjects as $subject) {
            $sql = "SELECT DISTINCT(r.by_username) as by_username FROM attendance r WHERE r.sub_code=:sub_code AND json_contains(r.attendance->'$[*].id',json_array(:id)) AND r.session=:session";
            $stmt=$this->getEntityManager()->getConnection()->prepare($sql);
            $stmt->bindParam(':sub_code',$subject['value'],PDO::PARAM_STR);
            $stmt->bindParam(':id',$id,PDO::PARAM_STR);
            $stmt->bindParam(':session',$current_session,PDO::PARAM_STR);
            $stmt->execute();
            $result = $stmt->fetchAll();
            foreach ($result as $r) {
                array_push($teacher_arr,$r['by_username']);
            }
        }

        $teachers = [];

        $index=0;
        foreach ($teacher_arr as $ta) {

            $tt = $this->getEntityManager()->getRepository('Employee')->getTeacherByEmail($ta);
            $teachers[$index]['email'] = $ta;
            $teachers[$index]['name'] = $tt['name'];
            $teachers[$index]['id'] = $tt['id'];
            $teachers[$index]['dept_code'] = $tt['dept_code'];
            $teachers[$index]['dept_name'] = $tt['dept_name'];
            $teachers[$index]['dp'] = $tt['dp'];

            $index++;
        }

        return $teachers;

        // get teacher names now

    }

   
  
}
