<?php

use Doctrine\ORM\EntityRepository;

class CertificateRequestRepository extends EntityRepository
{
   
    public function addCertificateRequest($body)
    {
        $entityManager = $this->getEntityManager();
        $student_id = addslashes(strip_tags(trim($body['student_id'])));
        $certificate_id = addslashes(strip_tags(trim($body['certificate_id'])));
        $name = addslashes(strip_tags(trim($body['name'])));
        $father_name = addslashes(strip_tags(trim($body['father_name'])));
        $mother_name = addslashes(strip_tags(trim($body['mother_name'])));
        $gender = addslashes(strip_tags(trim($body['gender'])));
        $address = addslashes(strip_tags(trim($body['address'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $roll_no = addslashes(strip_tags(trim($body['roll_no'])));
        $university_roll_no = addslashes(strip_tags(trim($body['university_roll_no'])));
        
        $time_stamp = date('d-m-Y H:i:s');
        if (sizeof($this->checkSlrNo($student_id,$certificate_id)) == 0) {
            $cer = new CertificateRequest();
            $cer->setStudentId($student_id);
            $cer->setCertificateId($certificate_id);
            $cer->setName($name);
            $cer->setFatherName($father_name);
            $cer->setMotherName($mother_name);
            $cer->setGender($gender);
            $cer->setAddress($address);
            $cer->setCourse($course);
            $cer->setSemester($semester);
            $cer->setRollNo($roll_no);
            $cer->setUniversityRollNo($university_roll_no);
            $cer->setTimeStamp($time_stamp);
            $entityManager->persist($cer);
            $entityManager->flush();
            return 'Request send successfully';
        } else {
            return 'Already Send';
        }

    }

    public function getCertificateRequests()
    {
        $dql = "SELECT r.id,r.student_id,r.certificate_id,r.name,r.father_name,r.mother_name,r.gender,r.address,r.course,r.semester,r.roll_no,r.university_roll_no,r.status,s.type_name,s.id as type_id FROM CertificateRequest r,CertificateType s WHERE r.certificate_id=s.id";
        $query = $this->getEntityManager()->createQuery($dql);
        return $query->getResult();
    }

    public function getCertificateRequestsByStudent($student_id)
    {
        $dql = "SELECT r.id,r.student_id,r.certificate_id,r.name,r.father_name,r.mother_name,r.gender,r.address,r.course,r.semester,r.roll_no,r.university_roll_no,r.status,r.time_stamp,r.cancel_reason,s.type_name,s.id as type_id FROM CertificateRequest r,CertificateType s WHERE r.certificate_id=s.id AND r.student_id=:student_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('student_id',$student_id);
        return $query->getResult();
    }

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

    public function getCertificateSingle($id)
    {
        $dql = "SELECT r.id,r.from_no,r.slr_no,r.name,r.roll,r.no,r.course,r.part,r.year,r.major,r.division,r.entry_date,s.type_name,s.id as type_id FROM Certificate r,CertificateType s WHERE r.type=s.id AND r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function updateCertificate($id, $body)
    {
        $entityManager = $this->getEntityManager();

        $id = addslashes(strip_tags(trim($body['id'])));
        $type = addslashes(strip_tags(trim($body['type_id'])));
        $from_no = addslashes(strip_tags(trim($body['from_no'])));
        $slr_no = addslashes(strip_tags(trim($body['slr_no'])));
        $entry_date = addslashes(strip_tags(trim($body['entry_date'])));
        $name = addslashes(strip_tags(trim($body['name'])));
        $roll = addslashes(strip_tags(trim($body['roll'])));
        $no = addslashes(strip_tags(trim($body['no'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $part = addslashes(strip_tags(trim($body['part'])));
        $year = addslashes(strip_tags(trim($body['year'])));
        $major = addslashes(strip_tags(trim($body['major'])));
        $division = addslashes(strip_tags(trim($body['division'])));

        $result = $this->getCertificateForEdit($id);

        if (sizeof($result) == 1) {
            $result[0]->setType($type);
            $result[0]->setFromNo($from_no);
            $result[0]->setSlrNo($slr_no);
            $result[0]->setEntryDate($entry_date);
            $result[0]->setName($name);
            $result[0]->setRoll($roll);
            $result[0]->setNo($no);
            $result[0]->setCourse($course);
            $result[0]->setPart($part);
            $result[0]->setYear($year);
            $result[0]->setMajor($major);
            $result[0]->setDivision($division);
           
            
            $entityManager->persist($result[0]);
            $entityManager->flush();
            return "Updated Successfully.";
        } else {
            return "Not Found.";
        }

    }

    public function checkSlrNo($student_id,$certificate_id)
    {

        $dql = 'SELECT r FROM CertificateRequest r WHERE r.student_id=:student_id AND r.certificate_id=:certificate_id';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('student_id', $student_id)->setParameter('certificate_id',$certificate_id);
        return $query->getResult();
    }

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

}

?>
