<?php

use Doctrine\ORM\EntityRepository;

class CertificateRepository extends EntityRepository
{

    public function addCertificate($body)
    {
        $entityManager = $this->getEntityManager();
        $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'])));
        $time_stamp = date('d-m-Y H:i:s');
        if (sizeof($this->checkSlrNo($slr_no)) == 0) {
            $cer = new Certificate();
            $cer->setType($type);
            $cer->setFromNo($from_no);
            $cer->setSlrNo($slr_no);
            $cer->setEntryDate($entry_date);
            $cer->setName($name);
            $cer->setRoll($roll);
            $cer->setNo($no);
            $cer->setCourse($course);
            $cer->setPart($part);
            $cer->setYear($year);
            $cer->setMajor($major);
            $cer->setDivision($division);
            $cer->setTimeStamp($time_stamp);
            $entityManager->persist($cer);
            $entityManager->flush();
            return 'Sucessfully Inserted';
        } else {
            return 'Already Exists';
        }

    }

    public function getCertificate()
    {
        $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";
        $query = $this->getEntityManager()->createQuery($dql);
        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($slr_no)
    {

        $dql = 'SELECT r FROM Certificate r WHERE r.slr_no=:slr_no';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('slr_no', $slr_no);
        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';
    }

}

?>
