<?php

use Doctrine\ORM\EntityRepository;

class SubjectMasterRepository extends EntityRepository
{

    public function addSubject($body)
    {
        $entityManager = $this->getEntityManager();
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $sub_type = addslashes(strip_tags(trim($body['sub_type'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $sub_name = addslashes(strip_tags(trim($body['sub_name'])));


        if (sizeof($this->checkSubject($sub_code)) == 0) {
            $sub = new SubjectMaster();
            $sub->setDeptCode($dept_code);
            $sub->setSubCode($sub_code);
            $sub->setCourse($course);
            $sub->setSubType($sub_type);
            $sub->setSubName($sub_name);

            $entityManager->persist($sub);
            $entityManager->flush();
            return "Subject Registered Successfully.";
        } else {
            return "Subject Code already registered.";
        }
    }

    public function getSubject()
    {
        $dql = "SELECT r.id,r.dept_code,r.sub_code,r.sub_name,r.course,r.sub_type From SubjectMaster r";
        $query = $this->getEntityManager()->createQuery($dql);
        $result = $query->getResult();

        $index = 0;
        foreach ($result as $r) {
            $result[$index]['value'] = $r['sub_code'];
            $result[$index]['label'] = $r['sub_name'] . ' - ' . $r['sub_code'];
            $index++;
        }

        return $result;
    }



    public function getSubjectForQuickEntry($sub_code)
    {
        $dql = "SELECT r.id,r.dept_code,r.sub_code,r.sub_name,r.course,r.sub_type From SubjectMaster r WHERE r.sub_code=:sub_code";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('sub_code',$sub_code);
        $result = $query->getResult();

        $index = 0;
        foreach ($result as $r) {
            $result[$index]['value'] = $r['sub_code'];
            $result[$index]['label'] = $r['sub_name'] . ' - ' . $r['sub_code'];
            $index++;
        }

        if(sizeof($result) > 0){
            return $result[0];
        }else{
            return false;
        }

        
    }


    public function getSubjectOfDept($dept_code)
    {
        $dql = "SELECT r.id,r.dept_code,r.sub_code,r.sub_name,r.course,r.sub_type From SubjectMaster r WHERE r.dept_code=:dept_code";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('dept_code', $dept_code);
        $result = $query->getResult();
        $index = 0;
        foreach ($result as $r) {
            $result[$index]['value'] = $r['sub_code'];
            $result[$index]['label'] = $r['sub_name'] . ' - ' . $r['sub_code'];
            $index++;
        }
        return $result;
    }

    public function getSubjectSingle($sub_code)
    {
        $dql = "SELECT r.id,r.dept_code,r.sub_code,r.sub_name,r.course,r.sub_type From SubjectMaster r where r.sub_code=:sub_code ORDER BY r.id DESC";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('sub_code', $sub_code)->setMaxResults(1);
        return $query->getResult();
    }

    public function getSubjectName($sub_code)
    {
        $dql = "SELECT r.sub_name From SubjectMaster r where r.sub_code=:sub_code";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('sub_code', $sub_code);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            return $result[0]['sub_name'];
        } else {
            return null;
        }
    }

    private function getSubjectForEdit($id)
    {
        $dql = "SELECT r from SubjectMaster r where r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function updateSubject($id, $body)
    {
        $entityManager = $this->getEntityManager();
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $sub_name = addslashes(strip_tags(trim($body['sub_name'])));
        $sub_type = addslashes(strip_tags(trim($body['sub_type'])));
        

        $result = $this->getSubjectForEdit($id);
        if (sizeof($result) == 1) {
            $result[0]->setDeptCode($dept_code);
            $result[0]->setSubCode($sub_code);
            $result[0]->setSubName($sub_name);
            $result[0]->setCourse($course);
            $result[0]->setSubType($sub_type);
            
            $entityManager->persist($result[0]);
            $entityManager->flush();
            return "Subject Updated Successfully.";
        } else {
            return "Not Found.";
        }
    }

    public function checkSubject($sub_code)
    {
        $dql = 'SELECT r FROM SubjectMaster r WHERE r.sub_code=:sub_code ';
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('sub_code', $sub_code);
        return $query->getResult();
    }

    public function deleteSubject($id)
    {
        $dql = 'DELETE  FROM SubjectMaster r WHERE r.id=:id';
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        $query->getResult();
        return true;
    }

    public function getAdmissionSubjects($course)
    {
        $em = $this->getEntityManager();
        $sql = "SELECT * from subject_master  WHERE json_contains(course->'$[*].value', json_array(:course)) AND need_in_admission=1 ORDER BY sub_name ASC";
        $stmt = $em->getConnection()->prepare($sql);
        $stmt->bindParam(':course', $course, PDO::PARAM_STR);
        $stmt->execute();
        $r = $stmt->fetchAll();
        $index = 0;
        foreach ($r as $r1) {
            $r[$index]['value'] = $r1['sub_code'];
            $r[$index]['label'] = $r1['sub_name'];
            $r[$index]['dept_name'] = '';

            $temp = $em->getRepository('Department')->getDeptSingle($r1['dept_code']);
            if(is_array($temp) && sizeof($temp) > 0){
            $r[$index]['dept_name'] = $temp[0]['dept_name'];
            }

            $r[$index]['papers'] = $em->getRepository('PaperMaster')->getPaperCodeBySubCode($r1['sub_code']);

            $index++;
        }

        return $r;

    }
}
