<?php

use Doctrine\ORM\EntityRepository;

class AdmissionFormRepository extends EntityRepository
{

    public function addStudent($body, $step)
    {

        // comment to activate 
        // used for reamission only

        $entityManager = $this->getEntityManager();
        $course = addslashes(strip_tags(trim($body['course'])));
        $email = addslashes(strip_tags(trim($body['email'])));

        if (sizeof($this->checkStudent($email,$course)) == 0 && $step == 1) {

            $form_id = $this->getFormCount();
           

            $admission_type = 1;  //0 for new admission , 1 for re admission 
            

            $flag = $entityManager->getRepository('AllowAdmission')->isAdmissionAllowed($course,$admission_type);
            if ($flag == false) {
                $s['status'] = false;
                $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                return $s;
           }

           $r = $entityManager->getRepository('SessionMaster')->getActiveSession();
           if (sizeof($r) > 0) {
               $session_id = $r[0]->getId();
           } else {
               $session_id = 0;
           }

          
           
            $primary_email = addslashes(strip_tags(trim($body['primary_email'])));
            $subjects_choosen = $body['subjects_choosen'];
           
            $course = addslashes(strip_tags(trim($body['course'])));
            $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'])));
            $mother_phone = addslashes(strip_tags(trim($body['mother_phone'])));
            $father_phone = addslashes(strip_tags(trim($body['father_phone'])));
            $father_email = addslashes(strip_tags(trim($body['father_email'])));

            $father_qualification = addslashes(strip_tags(trim($body['father_qualification'])));
            $mother_qualification = addslashes(strip_tags(trim($body['mother_qualification'])));
            $father_profession = addslashes(strip_tags(trim($body['father_profession'])));
            $mother_profession = addslashes(strip_tags(trim($body['mother_profession'])));
           
            
            $phone = addslashes(strip_tags(trim($body['phone'])));

            $gender = addslashes(strip_tags(trim($body['gender'])));
            $is_pwd = addslashes(strip_tags(trim($body['is_pwd'])));
            $is_ews = addslashes(strip_tags(trim($body['is_ews'])));

            $caste = addslashes(strip_tags(trim($body['caste'])));
            $date_of_birth = addslashes(strip_tags(trim($body['date_of_birth'])));
            $p_occupation = $body['p_occupation'];
            $quota = addslashes(strip_tags(trim($body['quota'])));
            $state = addslashes(strip_tags(trim($body['state'])));
            $religion = addslashes(strip_tags(trim($body['religion'])));
            $nationality = addslashes(strip_tags(trim($body['nationality'])));
            $blood_group = addslashes(strip_tags(trim($body['blood_group'])));
            
           
            $maratial_status = addslashes(strip_tags(trim($body['maratial_status'])));
            $has_hostel = addslashes(strip_tags(trim($body['has_hostel'])));
            
            $annual_income = addslashes(strip_tags(trim($body['annual_income'])));


            $present_address = $body['present_address'];
            $permanent_address = $body['permanent_address'];
            $local_address = $body['local_address'];
            $activities = $body['activities'];
            $local_g = addslashes(strip_tags(trim($body['local_g'])));
            $local_g_phone = addslashes(strip_tags(trim($body['local_g_phone'])));
            $extra_curr = addslashes(strip_tags(trim($body['extra_curr'])));

            $account_holder = addslashes(strip_tags(trim($body['account_holder'])));
            $account_no = addslashes(strip_tags(trim($body['account_no'])));
            $account_bank = addslashes(strip_tags(trim($body['account_bank'])));
            $account_branch = addslashes(strip_tags(trim($body['account_branch'])));
            $account_ifsc = addslashes(strip_tags(trim($body['account_ifsc'])));
            $account_micr = addslashes(strip_tags(trim($body['account_micr'])));
            $semester = addslashes(strip_tags(trim($body['semester'])));

            $obj = new AdmissionForm();
            $obj->setFormId($form_id);
            $obj->setSessionId($session_id);
            $obj->setCourse($course);
            $obj->setName($name);
            $obj->setFatherName($father_name);
            $obj->setFatherPhone($father_phone);
            $obj->setFatherEmail($father_email);
            $obj->setMotherName($mother_name);
            $obj->setMotherPhone($mother_phone);
            $obj->setFatherQualification($father_qualification);
            $obj->setMotherQualification($mother_qualification);
            $obj->setFatherProfession($father_profession);
            $obj->setMotherProfession($mother_profession);
            $obj->setIsPwd($is_pwd);
            $obj->setIsEws($is_ews);
            $obj->setGender($gender);
            $obj->setCaste($caste);
            $obj->setPhone($phone);
            $obj->setEmail($email);
            $obj->setPrimaryEmail($primary_email);
            $obj->setDateOfBirth($date_of_birth);
            $obj->setPOccupation($p_occupation);
            $obj->setQuota($quota);
            $obj->setState($state);
            $obj->setReligion($religion);
            $obj->setNationality($nationality);
            $obj->setBloodGroup($blood_group);
            $obj->setPresentAddress($present_address);
            $obj->setPermanentAddress($permanent_address);
            $obj->setLocalAddress($local_address);
            $obj->setLocalG($local_g);
            $obj->setLocalGPhone($local_g_phone);
            $obj->setExtraCurr($extra_curr);
            $obj->setAdmissionType($admission_type);
            $obj->setMaratialStatus($maratial_status);
            $obj->setHasHostel($has_hostel);
            $obj->setAnnualIncome($annual_income);
            $obj->setAccountHolder($account_holder);
            $obj->setAccountNo($account_no);
            $obj->setAccountBank($account_bank);
            $obj->setAccountBranch($account_branch);
            $obj->setAccountIfsc($account_ifsc);
            $obj->setAccountMicr($account_micr);
            $obj->setActivities($activities);
            $obj->setSemester($semester);
            $obj->setIsDel(0);
            $obj->setActiveStep(1);
            if($course == 'BA' || $course == 'BSC' || $course == 'HSA' || $course == 'HSS' || $course == 'HSC')
            $obj->setSubjectsChoosen($subjects_choosen);
            $entityManager->persist($obj);
            $entityManager->flush();
            if (null != $obj->getId()) {
                $s['status'] = true;
                $s['message'] = "Successfully Submitted. Proceed to next step";
                return $s;
            } else {
                $s['status'] = false;
                $s['message'] = "Something Went Wrong. Please try again";
                return $s;
            }
        }  else {
            $s['status'] = false;
            $s['message'] = "Email already exists.";
            return $s;
        }
    }

    public function updateStudent($id, $step, $body)
    {

        // comment to activate 
       //used for readmission only

        $entityManager = $this->getEntityManager();

        if (sizeof($this->getStudentForEdit($id)) == 1 && $step == 1) {

            $r = $this->getStudentForEdit($id);
            $obj = $r[0];

            $course = addslashes(strip_tags(trim($body['course'])));

            $admission_type = 1;  //0 for new admission , 1 for re admission 
           

            $flag = $entityManager->getRepository('AllowAdmission')->isAdmissionAllowed($course,$admission_type);
            if ($flag == false) {
                $s['status'] = false;
                $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                return $s;
           }


            $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'])));
            $mother_phone = addslashes(strip_tags(trim($body['mother_phone'])));
            $father_phone = addslashes(strip_tags(trim($body['father_phone'])));
            $father_email = addslashes(strip_tags(trim($body['father_email'])));

            $father_qualification = addslashes(strip_tags(trim($body['father_qualification'])));
            $mother_qualification = addslashes(strip_tags(trim($body['mother_qualification'])));
            $father_profession = addslashes(strip_tags(trim($body['father_profession'])));
            $mother_profession = addslashes(strip_tags(trim($body['mother_profession'])));
           
            
            $phone = addslashes(strip_tags(trim($body['phone'])));

            $gender = addslashes(strip_tags(trim($body['gender'])));
            $is_pwd = addslashes(strip_tags(trim($body['is_pwd'])));
            $is_ews = addslashes(strip_tags(trim($body['is_ews'])));

            $caste = addslashes(strip_tags(trim($body['caste'])));
            $date_of_birth = addslashes(strip_tags(trim($body['date_of_birth'])));
            $p_occupation = $body['p_occupation'];
            $quota = addslashes(strip_tags(trim($body['quota'])));
            $state = addslashes(strip_tags(trim($body['state'])));
            $religion = addslashes(strip_tags(trim($body['religion'])));
            $nationality = addslashes(strip_tags(trim($body['nationality'])));
            $blood_group = addslashes(strip_tags(trim($body['blood_group'])));
            
           
            $maratial_status = addslashes(strip_tags(trim($body['maratial_status'])));
            $has_hostel = addslashes(strip_tags(trim($body['has_hostel'])));
            
            $annual_income = addslashes(strip_tags(trim($body['annual_income'])));


            $present_address = $body['present_address'];
            $permanent_address = $body['permanent_address'];
            $local_address = $body['local_address'];
            $activities = $body['activities'];
            $local_g = addslashes(strip_tags(trim($body['local_g'])));
            $local_g_phone = addslashes(strip_tags(trim($body['local_g_phone'])));
            $extra_curr = addslashes(strip_tags(trim($body['extra_curr'])));

            $account_holder = addslashes(strip_tags(trim($body['account_holder'])));
            $account_no = addslashes(strip_tags(trim($body['account_no'])));
            $account_bank = addslashes(strip_tags(trim($body['account_bank'])));
            $account_branch = addslashes(strip_tags(trim($body['account_branch'])));
            $account_ifsc = addslashes(strip_tags(trim($body['account_ifsc'])));
            $account_micr = addslashes(strip_tags(trim($body['account_micr'])));
            $semester = addslashes(strip_tags(trim($body['semester'])));

           
            $primary_email = addslashes(strip_tags(trim($body['primary_email'])));
            $obj->setPrimaryEmail($primary_email);
             
            
           
            $obj->setName($name);
            $obj->setFatherName($father_name);
            $obj->setFatherPhone($father_phone);
            $obj->setFatherEmail($father_email);
            $obj->setMotherName($mother_name);
            $obj->setMotherPhone($mother_phone);
            $obj->setFatherQualification($father_qualification);
            $obj->setMotherQualification($mother_qualification);
            $obj->setFatherProfession($father_profession);
            $obj->setMotherProfession($mother_profession);
            $obj->setIsPwd($is_pwd);
            $obj->setIsEws($is_ews);
            $obj->setGender($gender);
            $obj->setCaste($caste);
            $obj->setPhone($phone);
          
            $obj->setDateOfBirth($date_of_birth);
            $obj->setPOccupation($p_occupation);
            $obj->setQuota($quota);
            $obj->setState($state);
            $obj->setReligion($religion);
            $obj->setNationality($nationality);
            $obj->setBloodGroup($blood_group);
            $obj->setPresentAddress($present_address);
            $obj->setPermanentAddress($permanent_address);
            $obj->setLocalAddress($local_address);
            $obj->setLocalG($local_g);
            $obj->setLocalGPhone($local_g_phone);
            $obj->setExtraCurr($extra_curr);
            $obj->setAdmissionType($admission_type);
            $obj->setMaratialStatus($maratial_status);
            $obj->setHasHostel($has_hostel);
            $obj->setAnnualIncome($annual_income);
            $obj->setAccountHolder($account_holder);
            $obj->setAccountNo($account_no);
            $obj->setAccountBank($account_bank);
            $obj->setAccountBranch($account_branch);
            $obj->setAccountIfsc($account_ifsc);
            $obj->setAccountMicr($account_micr);
            $obj->setActivities($activities);
            $obj->setIsDel(0);
            $obj->setActiveStep(1);
            $entityManager->persist($obj);
            $entityManager->flush();
           
            if (null != $obj->getId()) {
                $s['status'] = true;
                $s['message'] = "Successfully Submitted. Proceed to next step";
                return $s;
            } else {
                $s['status'] = false;
                $s['message'] = "Something Went Wrong. Please try again";
                return $s;
            }
        } else if (sizeof($this->getStudentForEdit($id)) == 1 && $step == 2) {
            $r = $this->getStudentForEdit($id);

            $course = $r[0]->getCourse();
            $admission_type = $r[0]->getAdmissionType();

            $flag = $entityManager->getRepository('AllowAdmission')->isAdmissionAllowed($course,$admission_type);
            if ($flag == false) {
                $s['status'] = false;
                $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                return $s;
           }


           if($course == 'BA' || $course == 'BSC' || $course == 'BCOM' || $course == 'BCA' || $course == 'BBA' || $course == 'HSA' || $course == 'HSS' || $course == 'HSC' || $course == 'BSCCS'){

            $unique_id = addslashes(strip_tags(trim($body['unique_id'])));
            $course_type = addslashes(strip_tags(trim($body['course_type'])));
           
            $subjects_choosen = $body['subjects_choosen'];
           


            $r[0]->setCourseType($course_type);
           
            $r[0]->setUniqueId($unique_id);
            $r[0]->setSubjectsChoosen($subjects_choosen);
           
           
           
        }
          
        if($course == 'PG'){

           
            $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
            $subjects_choosen = $body['subjects_choosen'];
          


            $r[0]->setDeptCode($dept_code);
         
            $r[0]->setSubjectsChoosen($subjects_choosen);
           
           
           
        }

            if ($r[0]->getActiveStep() <= $step) {
                $r[0]->setActiveStep(2);
            }

            

            $entityManager->persist($r[0]);
            $entityManager->flush();

            if (null != $r[0]->getId()) {
                $s['status'] = true;
                $s['message'] = "Successfully Submitted. Proceed to next step";
                return $s;
            } else {
                $s['status'] = false;
                $s['message'] = "Something Went Wrong. Please try again";
                return $s;
            }
        } else if (sizeof($this->getStudentForEdit($id)) == 1 && $step == 3) {
            $entityManager = $this->getEntityManager();
            $r = $this->getStudentForEdit($id);

            $course = $r[0]->getCourse();
            $admission_type = $r[0]->getAdmissionType();

            $flag = $entityManager->getRepository('AllowAdmission')->isAdmissionAllowed($course,$admission_type);
            if ($flag == false) {
                $s['status'] = false;
                $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                return $s;
           }

            $documents_required = $body['documents_required'];
            $documents_uploaded = $body['documents_uploaded'];

            $r[0]->setDocumentsRequired($documents_required);
            $r[0]->setDocumentsUploaded($documents_uploaded);

            if ($r[0]->getActiveStep() <= $step) {
                $r[0]->setActiveStep(3);
            }

            $entityManager->persist($r[0]);
            $entityManager->flush();
            if (null != $r[0]->getId()) {

                $s['status'] = true;
                $s['message'] = "Successfully Submitted. Proceed to step 3";
                return $s;
            } else {
                $s['status'] = false;
                $s['message'] = "Something Went Wrong. Please try again";
                return $s;
            }
        } else if (sizeof($this->getStudentForEdit($id)) == 1 && $step == 4) {

            $entityManager = $this->getEntityManager();
            $transaction = addslashes(strip_tags(trim($body['transaction'])));
            $r = $this->getStudentForEdit($id);



            //if ($r[0]->getCourse() != 'PG') {
                // $s['status'] = false;
                // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                // return $s;
            //}


            $r[0]->setPaymentStatus(1);
            $r[0]->setIsSubmit(1);
            $r[0]->setTransaction($transaction);
            if ($r[0]->getActiveStep() <= $step) {
                $r[0]->setActiveStep(4);
            }

            $entityManager->persist($r[0]);
            $entityManager->flush();
            if (null != $r[0]->getId()) {

                $s['status'] = true;
                $s['message'] = "Successfully Submitted.";
                return $s;
            } else {
                $s['status'] = false;
                $s['message'] = "Something went wrong";
                return $s;
            }
        } else {
            $s['status'] = false;
            $s['message'] = "Failed";
            return $s;
        }
    }


    public function setFinalMajor($body)
    {

        $em = $this->getEntityManager();

        $form_id = addslashes(strip_tags(trim($body['form_id'])));
        $subject_code = addslashes(strip_tags(trim($body['final_major'])));
        $final_generic = addslashes(strip_tags(trim($body['final_generic'])));

        // get subject object by subject code

        $subObj = $em->getRepository('SubjectMaster')->getSingleSubjectBySubjectCode($subject_code);
        $subObj1 = $em->getRepository('SubjectMaster')->getSingleSubjectBySubjectCode($final_generic);


        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0 && $subObj != false) {
            $r[0]->setFinalMajor($subObj);
            $r[0]->setFinalGeneric($subObj1);

            $em->persist($r[0]);
            $em->flush();

            $b = [];
            $b['status'] = true;
            $b['status_text'] = 'Final Major Added';
            $b['roll_no'] = '';
            return $b;
        } else {
            $b = [];
            $b['status'] = false;
            $b['status_text'] = 'Final Major Added';
            $b['roll_no'] = '';
            return $b;
        }
    }

    public function addFinalMajor($body)
    {
        $entityManager = $this->getEntityManager();
        $b = array();

        $course = addslashes(strip_tags(trim($body['course'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $final_major = $body['final_major'];

        $r = $this->checkStudent($email,$course);

        if (sizeof($r) > 0) {
            $r[0]->setFinalMajor($final_major);
            $entityManager->persist($r[0]);
            $entityManager->flush();

            $b['status'] = true;
            $b['message'] = "Successfully Updated";
            return $b;
        } else {
            $b['status'] = false;
            $b['message'] = "Not found";
            return $b;
        }
    }

    private function getFormCount()
    {
        $dql = "SELECT max(r.id) as id FROM AdmissionForm r";
        $query = $this->getEntityManager()->createQuery($dql);
        $r = $query->getResult();
        if ($r[0]['id'] == 0 || $r[0]['id'] == null) {
            return 1001;
        } else {
            return 1001 + (float) $r[0]['id'];
        }
    }

    public function getAdmissionCount()
    {

        $entityManager = $this->getEntityManager();
        $r1 = $entityManager->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r1) > 0) {
            $session_id = $r1[0]->getId();
        } else {
            $session_id = 0;
        }

        $a = array();

        //Register count
        $dql = "SELECT count(r.id) as value FROM AdmissionForm r WHERE r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $r = $query->getResult();
        $r[0]['title'] = 'Total Registered';
        $a = array_merge($a, $r);

        //Completed count
        $dql = "SELECT count(r.id) as value FROM AdmissionForm r WHERE r.is_complete=1 AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $r = $query->getResult();
        $r[0]['title'] = 'Completed';
        $a = array_merge($a, $r);

        //admission count
        $dql = "SELECT count(r.id) as value,r.course as title FROM AdmissionForm r WHERE r.session_id=:session_id AND r.is_complete=1 GROUP BY r.course";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $r = $query->getResult();
        $a = array_merge($a, $r);

        //max form id
        $dql = "SELECT MAX(r.form_id) as value FROM AdmissionForm r WHERE r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $r = $query->getResult();
        $index = 0;
        foreach ($r as $r1) {
            $r[$index]['title'] = 'Max Form No';
            $index++;
        }
        $a = array_merge($a, $r);

        //area count
        $dql = "SELECT count(r.id) as value,r.caste as title FROM AdmissionForm r WHERE r.session_id=:session_id AND r.is_complete=1 GROUP BY r.caste";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $r = $query->getResult();
        $a = array_merge($a, $r);



        return $a;
    }

    public function getAdmissionCountRe()
    {
        $a = array();


        $session = $this->getEntityManager()->getRepository('SessionMaster')->getActiveSessionSingle();

        //Completed count
        $dql = "SELECT count(r.id) as value FROM AdmissionForm r WHERE r.semester!=1 AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session);
        $r = $query->getResult();
        $r[0]['title'] = 'Total Registration';
        $a = array_merge($a, $r);


        //Completed count
        $dql = "SELECT count(r.id) as value FROM AdmissionForm r WHERE r.is_submit=1 AND r.semester!=1 AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session);
        $r = $query->getResult();
        $r[0]['title'] = 'Completed';
        $a = array_merge($a, $r);


        //admission count
        $dql = "SELECT count(r.id) as value,r.course as title FROM AdmissionForm r WHERE r.is_submit=1 AND r.semester!=1 AND r.session_id=:session_id GROUP BY r.course";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session);
        $r = $query->getResult();
        $a = array_merge($a, $r);

        //max form id
        $dql = "SELECT MAX(r.form_id) as value FROM AdmissionForm r WHERE r.semester!=1 AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session);
        $r = $query->getResult();
        $index = 0;
        foreach ($r as $r1) {
            $r[$index]['title'] = 'Max Form No';
            $index++;
        }
        $a = array_merge($a, $r);

        //area count
        $dql = "SELECT count(r.id) as value,r.caste as title FROM AdmissionForm r WHERE r.is_submit=1 AND r.semester!=1 AND r.session_id=:session_id GROUP BY r.caste";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session);
        $r = $query->getResult();
        $a = array_merge($a, $r);



        return $a;
    }

    public function getShortList()
    {
    }

    public function makeShortList($caste, $stream)
    {
        $a = array();
        $em = $this->getEntityManager();
        if ($caste == '') {
            $sql = "SELECT id,form_id,marks_obtained,phone FROM admission_form r WHERE stream=:stream AND is_del =0 AND payment_status=1 ORDER BY marks_obtained DESC,json_contains(last_marks->'$[*].sub_code', json_array('ENG'))";

            $stmt = $em->getConnection()->prepare($sql);
            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
            $stmt->execute();
            $r = $stmt->fetchAll();
            if (!empty($r)) {
                array_push($a, $r[0]);
            }
        } else {
            $sql = "SELECT id,form_id,marks_obtained,phone FROM admission_form r WHERE stream=:stream AND is_del =0 AND payment_status=1 ORDER BY marks_obtained DESC,json_contains(last_marks->'$[*].sub_code', json_array('ENG'))";

            $stmt = $em->getConnection()->prepare($sql);
            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
            $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
            $stmt->execute();
            $r = $stmt->fetchAll();
            if (!empty($r)) {
                array_push($a, $r[0]);
            }
        }
    }

    public function getStudent()
    {
        $dql = "SELECT r.id,r.form_id,r.class_id,r.class_name,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.subjects_combo,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.dp,r.signature,r.is_del,r.is_submit,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.is_del =0";
        $query = $this->getEntityManager()->createQuery($dql);
        $result = $query->getResult();
        $a = array();
        for ($i = 0; $i < sizeof($result); $i++) {
            require_once __DIR__ . '/../utility.php';
            $a[$i]['data'] = $result[$i];
            $file = new FileUpload();
            $a[$i]['file'] = [$file->getFile($result[$i]['dp'], $result[$i]['signature'])];
        }

        return $a;
    }

    public function getActiveStudent()
    {
        $dql = "SELECT r.id,r.form_id,r.class_id,r.class_name,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.subjects_combo,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.dp,r.signature,r.is_del,r.is_submit,r.payment_status,r.transaction FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.payment_status=1";
        $query = $this->getEntityManager()->createQuery($dql);
        $result = $query->getResult();
        $a = array();
        for ($i = 0; $i < sizeof($result); $i++) {
            require_once __DIR__ . '/../utility.php';
            $a[$i]['data'] = $result[$i];
            $file = new FileUpload();
            $a[$i]['file'] = [$file->getFile($result[$i]['dp'], $result[$i]['signature'])];
        }

        return $a;
    }

    public function getPaidStudent()
    {
        $dql = "SELECT r.id,r.form_id,r.class_id,r.class_name,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.subjects_combo,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.dp,r.signature,r.is_del,r.is_submit,r.payment_status,r.transaction FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.payment_status=1";
        $query = $this->getEntityManager()->createQuery($dql);
        $result = $query->getResult();
        $a = array();
        for ($i = 0; $i < sizeof($result); $i++) {
            require_once __DIR__ . '/../utility.php';
            $a[$i]['data'] = $result[$i];
            $file = new FileUpload();
            $a[$i]['file'] = [$file->getFile($result[$i]['dp'], $result[$i]['signature'])];
        }

        return $a;
    }

    public function gotToStep($email,$course, $step)
    {
        $em = $this->getEntityManager();


        // comment to activate 
        // $s['status'] = false;
        // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
        // return $s;

        $r = $this->checkStudent($email,$course);

        if (sizeof($r) > 0) {


            //if ($r[0]->getCourse() != 'PG') {
                // $s['status'] = false;
                // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                // return $s;
            //}

            $r[0]->setActiveStep($step);
            $em->persist($r[0]);
            $em->flush();
            return $step;
        } else {
            return false;
        }
    }


    public function completeThisApplication($email,$course)
    {
        $em = $this->getEntityManager();

        // comment to activate 
        // $s['status'] = false;
        // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
        // return $s;

        $r = $this->checkStudent($email,$course);

        if (sizeof($r) > 0) {

            //if ($r[0]->getCourse() != 'PG') {
                // $s['status'] = false;
                // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                // return $s;
            //}

            
            $r[0]->setActiveStep(5);
            $r[0]->setIsComplete(1);
            $em->persist($r[0]);
            $em->flush();
            return true;
        } else {
            return false;
        }
    }


    public function paymentDoing($body)
    {
        $em = $this->getEntityManager();

        $course = addslashes(strip_tags(trim($body['course'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        // comment to activate 
        // $s['status'] = false;
        // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
        // return $s;

        $r = $this->checkStudent($email,$course);

        if (sizeof($r) > 0) {

            //if ($r[0]->getCourse() != 'PG') {
                // $s['status'] = false;
                // $s['message'] = "The last date of Submission of online application is over. Sorry !!";
                // return $s;
            //}

            
            $r[0]->setActiveStep(5);
            $r[0]->setIsComplete(1);
            $em->persist($r[0]);
            $em->flush();
            return true;
        } else {
            return false;
        }
    }

    public function getActiveStudentSingle($email,$course)
    {
        // add session id too
        $entityManager = $this->getEntityManager();

        $session_id = $entityManager->getRepository('SessionMaster')->getActiveSessionSingle();

        $dql = "SELECT r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.primary_email,s.session_name FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.email=:email AND r.course=:course AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('session_id', $session_id)
            ->setParameter('email', $email)
            ->setParameter('course', $course);
        $result = $query->getResult();
        return $result;
    }

    public function getPaymentDetails($email)
    {
        $dql = "SELECT r.id,r.form_id,r.name,r.email,r.phone,r.payment_status,r.admission_type FROM AdmissionForm r WHERE r.email=:email";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('email', $email);
        $result = $query->getResult();
        return $result;
    }

    public function updatePaymentStatus($form_id, $data)
    {
        $em = $this->getEntityManager();
        $r = $this->getActiveStudentSingleByFormId1($form_id);
        if (sizeof($r) > 0) {
            $r[0]->setPaymentStatus(1);
            $r[0]->setTransaction($data);
            $em->persist($r[0]);
            $em->flush();
            return true;
        }
    }

    public function hasPaymentDone($email,$course)
    {
        $em = $this->getEntityManager();
        $r = $this->checkStudent($email,$course);
        if (sizeof($r) > 0) {
            if ($r[0]->getPaymentStatus() == '1') {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function getPaymentResponse($email,$course)
    {
        $em = $this->getEntityManager();
        if ($this->hasPaymentDone($email,$course)) {

            $dql = "SELECT r.id,r.form_id,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.course,r.stream,r.date_of_admission,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.email=:email";
            $query = $this->getEntityManager()->createQuery($dql)->setParameter('email', $email);
            $result = $query->getResult();
            $index = 0;
            foreach ($result as $r) {
                $result[$index]['transaction'] = $this->goodTransaction($r['transaction']);
                $index++;
            }

            return $result;
        }
    }

    private function goodTransaction($transaction)
    {
        $a = array();
        if ($transaction !== null) {
            $m = explode('|', $transaction);
            $length = sizeof($m);
            if ($length > 0) {
                $a['status'] = 'SUCCESS';
                $a['form_id'] = $m[1];
                $a['Ref_no'] = $m[2];
                $a['BANK_Ref_no'] = $m[3];
                $a['amount'] = $m[4];
                $a['date'] = $m[13];

                return $a;
            }
        }
    }

    public function getActiveStudentSingleByFormId($form_id)
    {

        $entityManager = $this->getEntityManager();

        $session_id = $entityManager->getRepository('SessionMaster')->getActiveSessionSingle();
        


        $dql = "SELECT r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.final_major,r.final_generic,s.session_name FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.form_id=:form_id AND r.session_id=:session_id";
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('session_id', $session_id)
            ->setParameter('form_id', $form_id);
        $result = $query->getResult();
        return $result;
    }

    public function getActiveStudentSingleByFormId1($form_id)
    {
        $dql = "SELECT r FROM AdmissionForm r WHERE r.form_id=:form_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('form_id', $form_id);
        $result = $query->getResult();
        return $result;
    }

    public function getFilesOfActiveStudentSingleByFormId($form_id)
    {
        $em = $this->getEntityManager();
        $dql = "SELECT r.name,r.email,r.phone,r.course, r.stream, r.course_type,r.dp,r.signature,r.certificate,r.gap_certificate,r.marksheet,r.income,r.bpl,r.tree,r.is_free_admission,r.age_proof,r.caste_certificate,r.bank_details,r.other_certificate,r.domicile_certificate,r.pwd_certificate FROM AdmissionForm r WHERE r.form_id=:form_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('form_id', $form_id);
        $result = $query->getResult();

        // resolve all file to url
        $index = 0;
        foreach ($result as $r) {
            $result[$index]['dp'] = $em->getRepository('MasterFile')->getUrls($r['dp']);
            $result[$index]['signature'] = $em->getRepository('MasterFile')->getUrls($r['signature']);
            $result[$index]['certificate'] = $em->getRepository('MasterFile')->getUrls($r['certificate']);
            $result[$index]['marksheet'] = $em->getRepository('MasterFile')->getUrls($r['marksheet']);
            $result[$index]['income'] = $em->getRepository('MasterFile')->getUrls($r['income']);
            $result[$index]['bpl'] = $em->getRepository('MasterFile')->getUrls($r['bpl']);
            $result[$index]['tree'] = $em->getRepository('MasterFile')->getUrls($r['tree']);
            $result[$index]['age_proof'] = $em->getRepository('MasterFile')->getUrls($r['age_proof']);
            $result[$index]['caste_certificate'] = $em->getRepository('MasterFile')->getUrls($r['caste_certificate']);
            $result[$index]['bank_details'] = $em->getRepository('MasterFile')->getUrls($r['bank_details']);
            $result[$index]['other_certificate'] = $em->getRepository('MasterFile')->getUrls($r['other_certificate']);
            $result[$index]['gap_certificate'] = $em->getRepository('MasterFile')->getUrls($r['gap_certificate']);
            $result[$index]['domicile_certificate'] = $em->getRepository('MasterFile')->getUrls($r['domicile_certificate']);
            $result[$index]['pwd_certificate'] = $em->getRepository('MasterFile')->getUrls($r['pwd_certificate']);
            $index++;
        }
        return $result;
    }

    public function getFilesOfActiveStudentSingleByFormIdNew($form_id)
    {
        $dql = "SELECT r.name,r.email,r.phone,r.course, r.stream, r.course_type,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.active_step,r.age_proof,r.bank_details,r.gap_certificate,r.caste_certificate,r.domicile_certificate,r.pwd_certificate,r.dp,r.signature,r.certificate,r.marksheet,r.income,r.bpl,r.tree,r.is_free_admission FROM AdmissionForm r WHERE r.form_id=:form_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('form_id', $form_id);
        $result = $query->getResult();
        return $result;
    }

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

    public function getStudentByEmail($email)
    {
        $dql = "SELECT r.id,r.form_id,r.class_id,r.class_name,r.is_free_admission,r.subject_comb,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.subjects_combo,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.dp,r.signature,r.is_del,r.is_submit,r.payment_status,r.transaction FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.email=:email";
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('email', $email);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            return $result[0];
        } else {
            return false;
        }
    }

    public function getStudentObjectByEmail($email)
    {
        $dql = "SELECT r FROM AdmissionForm r WHERE r.email=:email";
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('email', $email);
        $result = $query->getResult();
        return $result;
    }

    public function getStudentPhoneNumber($email)
    {
        $dql = "SELECT r FROM AdmissionForm r WHERE r.email=:email";
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('email', $email);
        $result = $query->getResult();
        return $result[0]->getPhone();
    }

    public function getStudentSingle($id)
    {
        $dql = "SELECT r.id,r.form_id,r.class_id,r.class_name,r.is_free_admission,r.subject_comb,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.subjects_combo,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.dp,r.signature,r.is_del,r.is_submit,r.payment_status,r.transaction FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND s.is_active=1 AND r.is_del =0 AND r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        $result = $query->getResult();
        $a = array();

        require_once __DIR__ . '/../utility.php';
        $a[0]['data'] = $result[0];
        $file = new FileUpload();
        $a[0]['file'] = [$file->getFile($result[0]['dp'], $result[0]['signature'])];

        return $a;
    }

    public function deleteStudent($id)
    {
        $r = $this->getStudentForEdit($id);
        $this->getEntityManager()->getRepository('AdmissionTemp')->deleteGeneralUser($r[0]->getEmail());
        $dql = "DELETE FROM AdmissionForm r WHERE r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        $query->getResult();
        return "Succesfully deleted";
    }

    public function checkStudent($email,$course)
    {

        // add session id too
        $entityManager = $this->getEntityManager();

        $r = $entityManager->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r) > 0) {
            $session_id = $r[0]->getId();
        } else {
            $session_id = 0;
        }

        $dql = 'SELECT r FROM AdmissionForm r WHERE r.email=:email AND r.course=:course AND r.session_id=:session_id';
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('email', $email)
            ->setParameter('course', $course)
            ->setParameter('session_id', $session_id);
        return $query->getResult();
    }

    public function getIdForEdit($email)
    {
        $dql = 'SELECT r FROM AdmissionForm r WHERE r.email=:email';
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('email', $email);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            return $result[0]->getId();
        } else {
            return false;
        }
    }

    public function getReportsInitial()
    {
        $data = array();



        $dql = "SELECT r.course from AdmissionForm r group by r.course";
        $query = $this->getEntityManager()->createQuery($dql);
        $course = $query->getScalarResult();
        $data['course'] = array_column($course, 'course');

        // $dql = "SELECT r.stream from AdmissionForm r  group by r.stream";
        // $query = $this->getEntityManager()->createQuery($dql);
        // $stream = $query->getScalarResult();
        // $data['stream'] = array_column($stream, 'stream');

        $dql = "SELECT r.caste from AdmissionForm r group by r.caste";
        $query = $this->getEntityManager()->createQuery($dql);
        $caste = $query->getScalarResult();
        $data['caste'] = array_column($caste, 'caste');

        $dql = "SELECT r.gender from AdmissionForm r group by r.gender";
        $query = $this->getEntityManager()->createQuery($dql);
        $gender = $query->getScalarResult();
        $data['gender'] = array_column($gender, 'gender');

        $dql = "SELECT r.state from AdmissionForm r  group by r.state";
        $query = $this->getEntityManager()->createQuery($dql);
        $state = $query->getScalarResult();
        $data['state'] = array_column($state, 'state');

        return $data;
    }

    public function getMeritList($body)
    {
        $em = $this->getEntityManager();

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $course_type = addslashes(strip_tags(trim($body['course_type'])));
        $caste = addslashes(strip_tags(trim($body['caste'])));

        // add session id too
        $entityManager = $this->getEntityManager();

        $r = $entityManager->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r) > 0) {
            $session_id = $r[0]->getId();
        } else {
            $session_id = 0;
        }

        if ($course == 'TDC') {
            if ($caste == 'GENERAL' || $caste == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.course_type=:course_type AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course_type', $course_type);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.course_type=:course_type AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('caste', $caste)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course_type', $course_type);
            }
        } else if ($course == 'HS' || $course == 'PG') {
            if ($caste == 'GENERAL' || $caste == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('caste', $caste)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            }
        } else if ($course == 'BSC-BIOTECHNOLOGY') {
            if ($caste == 'GENERAL' || $caste == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course)
                    ->setParameter('caste', $caste);
            }
        } else if ($course == 'BCA') {
            if ($caste == 'GENERAL' || $caste == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.last_marks FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course)
                    ->setParameter('caste', $caste);
            }
        }

        $result = $query->getResult();

        $index = 0;
        foreach ($result as $r) {
            $a = [];
            if (sizeof($r['compulsory_1']) !== 0) {
                array_push($a, $r['compulsory_1']);
            }

            if (sizeof($r['compulsory_2']) !== 0) {
                array_push($a, $r['compulsory_2']);
            }

            if (sizeof($r['compulsory_3']) !== 0) {
                array_push($a, $r['compulsory_3']);
            }

            if (sizeof($r['major_1']) !== 0) {
                array_push($a, $r['major_1']);
            }

            if (sizeof($r['major_2']) !== 0) {
                array_push($a, $r['major_2']);
            }

            if (sizeof($r['mil']) !== 0) {
                array_push($a, $r['mil']);
            }

            if (sizeof($r['generic_1']) !== 0) {
                array_push($a, $r['generic_1']);
            }

            if (sizeof($r['generic_2']) !== 0) {
                array_push($a, $r['generic_2']);
            }

            if (sizeof($r['generic_3']) !== 0) {
                array_push($a, $r['generic_3']);
            }

            if (sizeof($r['optional_subjects']) !== 0) {
                $a = array_merge($a, $r['optional_subjects']);
            }

            $result[$index]['subjects'] = $a;

            $index++;
        }

        if ($course === 'TDC') {
            $index = 0;
            foreach ($result as $r) {
                $e = $r['best_subjects'];
                $t = 300;
                $o = 0;
                $p = 0;

                foreach ($e as $e1) {
                    if (array_key_exists('marks_secured', $e1)) {
                        $o = $o + (float) $e1['marks_secured'];
                    }
                }

                $p = ($o / $t) * 100;
                $p = number_format((float) $p, 2, '.', '');

                $result[$index]['marks_obtained'] = $o;
                $result[$index]['percentage'] = $p;

                $index++;
            }

            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });
        }

        return $result;
    }

    public function getAdmissionList($body)
    {

        ini_set('memory_limit', '128M'); // this is very wrong - solve it

        $em = $this->getEntityManager();

        // add session id too


        $r = $em->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r) > 0) {
            $session_id = $r[0]->getId();
        } else {
            $session_id = 0;
        }

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $course_type = addslashes(strip_tags(trim($body['course_type'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        if ($semester == '') {
            $semester = 1;
        }

        if ($course == 'TDC') {
            if ($semester == 1) {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.stream,r.semester,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.course_type=:course_type AND r.is_complete='1' AND r.session_id=:session_id AND (r.semester IS NULL OR r.semester ='1') ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course_type', $course_type);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.course_type=:course_type AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('semester', $semester)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course_type', $course_type);
            }
        } else if ($course == 'HS') {
            if ($semester == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.semester,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND (r.semester IS NULL OR r.semester ='1') ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.semester,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('semester', $semester)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            }
        } else if ($course == 'PG') {
            if ($semester == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.semester,r.course_type,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND (r.semester IS NULL OR r.semester ='1') ORDER BY r.ranking_score DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.ranking_score DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('semester', $semester)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('stream', $stream);
            }
        } else if ($course == 'BSC-BIOTECHNOLOGY') {
            if ($semester == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id AND (r.semester IS NULL OR r.semester ='1') ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('semester', $semester);
            }
        } else if ($course == 'BCA') {
            if ($semester == '') {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id AND (r.semester IS NULL OR r.semester ='1') ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('course', $course);
            } else {
                $dql = "SELECT r.id, r.form_id,r.name,r.phone,r.email,r.father_name,r.permanent_address,r.course,r.course_type,r.semester,r.stream,r.caste,r.marks_obtained,r.percentage,r.email,r.phone,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.major_1,r.major_2,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.best_subjects,r.last_marks,r.roll_no,r.university_roll_no,r.ranking_score,r.ranking_serial,r.payment_status,r.transaction FROM AdmissionForm r WHERE r.course=:course AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('semester', $semester);
            }
        }

        $result = $query->getResult();

        $index = 0;
        foreach ($result as $r) {
            $a = [];
            if (sizeof($r['compulsory_1']) !== 0) {
                array_push($a, $r['compulsory_1']);
            }

            if (sizeof($r['compulsory_2']) !== 0) {
                array_push($a, $r['compulsory_2']);
            }

            if (sizeof($r['compulsory_3']) !== 0) {
                array_push($a, $r['compulsory_3']);
            }

            if (sizeof($r['major_1']) !== 0) {
                array_push($a, $r['major_1']);
            }

            if (sizeof($r['major_2']) !== 0) {
                array_push($a, $r['major_2']);
            }

            if (sizeof($r['mil']) !== 0) {
                array_push($a, $r['mil']);
            }

            if (sizeof($r['generic_1']) !== 0) {
                array_push($a, $r['generic_1']);
            }

            if (sizeof($r['generic_2']) !== 0) {
                array_push($a, $r['generic_2']);
            }

            if (sizeof($r['generic_3']) !== 0) {
                array_push($a, $r['generic_3']);
            }

            if (sizeof($r['optional_subjects']) !== 0) {
                $a = array_merge($a, $r['optional_subjects']);
            }

            $result[$index]['subjects'] = $a;

            $result[$index]['transaction'] = $this->goodTransaction($r['transaction']);

            $index++;
        }

        if ($course === 'TDC') {
            $index = 0;
            foreach ($result as $r) {
                $e = $r['best_subjects'];
                $t = 300;
                $o = 0;
                $p = 0;

                foreach ($e as $e1) {
                    $o = $o + (float) $e1['marks_obtained'];
                }

                $p = ($o / $t) * 100;
                $p = number_format((float) $p, 2, '.', '');

                $result[$index]['marks_obtained'] = $o;
                $result[$index]['percentage'] = $p;

                $index++;
            }

            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });
        }

        return $result;
    }

    public function getMeritListMajor($body)
    {
        $em = $this->getEntityManager();

        $session_id = $em->getRepository('SessionMaster')->getActiveSessionSingle();
       
        $course = addslashes(strip_tags(trim($body['course'])));
        $preference = addslashes(strip_tags(trim($body['preference'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $caste = addslashes(strip_tags(trim($body['caste'])));
        $is_verified = addslashes(strip_tags(trim($body['is_verified'])));
        $semester = 1;

        if ($preference === '1') {
            $type = 'major_1';
        }
        else if ($preference === '2') {
            $type = 'major_2';
        }
        else if ($preference === '3') {
            $type = 'major_3';
        }

        if ($course == 'BA' || $course == 'BSC') {
            if ($caste == 'GENERAL' || $caste == '') {

                    if ($is_verified == 0) {
                        //SELECT JSON_EXTRACT(subjects_choosen, JSON_UNQUOTE(REPLACE(JSON_SEARCH(subjects_choosen,'one', 'major_1'),'.type','.subject'))), subjects_choosen->"$[*].subject" FROM `admission_form`
                        $qb = $em->createQueryBuilder();
                        $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                         ->from('AdmissionForm','r')
                         ->andWhere('r.is_complete=1')
                         ->andWhere('r.course=:course')
                         ->andWhere('r.semester=:semester')
                         ->andWhere('r.session_id=:session_id')
                         ->andWhere("JSON_CONTAINS(JSON_EXTRACT(r.subjects_choosen, '$[*].type'), :type) = 1")
                         ->setParameters(array(
                         "course"=>$course,
                         "semester"=>$semester,
                         "session_id"=>$session_id,
                         "type"=>json_encode([$type])))
                         ->orderBy("r.percentage","DESC")->getQuery();
                         
                         $rr = $query->execute();
                         $result = [];

                         if(sizeof($rr) > 0){
                            foreach($rr as $r){
                                $flag = false;
                                $subjects = $r['subjects_choosen'];
                                if(sizeof($subjects) > 0){
                                    foreach($subjects as $sub){
                                        if($sub['type'] == $type && $sub['subject']['sub_code'] == $sub_code){
                                            $flag = true;
                                        }
                                    }
                                }

                                if($flag){
                                    array_push($result,$r);
                                }
                            }
                         }
                    } else {
                        $qb = $em->createQueryBuilder();
                        $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                         ->from('AdmissionForm','r')
                         ->andWhere('r.is_complete=1')
                         ->andWhere('r.is_verified=1')
                         ->andWhere('r.course=:course')
                         ->andWhere('r.semester=:semester')
                         ->andWhere('r.session_id=:session_id')
                         ->andWhere("JSON_CONTAINS(JSON_EXTRACT(r.subjects_choosen, '$[*].type'), :type) = 1")
                         ->setParameters(array(
                         "course"=>$course,
                         "semester"=>$semester,
                         "session_id"=>$session_id,
                         "type"=>json_encode([$type])))
                         ->orderBy("r.percentage","DESC")->getQuery();
                         
                         $rr = $query->execute();
                         $result = [];

                         if(sizeof($rr) > 0){
                            foreach($rr as $r){
                                $flag = false;
                                $subjects = $r['subjects_choosen'];
                                if(sizeof($subjects) > 0){
                                    foreach($subjects as $sub){
                                        if($sub['type'] == $type && $sub['subject']['sub_code'] == $sub_code){
                                            $flag = true;
                                        }
                                    }
                                }

                                if($flag){
                                    array_push($result,$r);
                                }
                            }
                         }
                    }
                
            } else {
               

                if ($is_verified == 0) {
                    //SELECT JSON_EXTRACT(subjects_choosen, JSON_UNQUOTE(REPLACE(JSON_SEARCH(subjects_choosen,'one', 'major_1'),'.type','.subject'))), subjects_choosen->"$[*].subject" FROM `admission_form`
                    $qb = $em->createQueryBuilder();
                    $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                     ->from('AdmissionForm','r')
                     ->andWhere('r.is_complete=1')
                     ->andWhere('r.course=:course')
                     ->andWhere('r.semester=:semester')
                     ->andWhere('r.session_id=:session_id')
                     ->andWhere('r.caste=:caste')
                     ->andWhere("JSON_CONTAINS(JSON_EXTRACT(r.subjects_choosen, '$[*].type'), :type) = 1")
                     ->setParameters(array(
                     "course"=>$course,
                     "semester"=>$semester,
                     "session_id"=>$session_id,
                     "caste"=>$caste,
                     "type"=>json_encode([$type])))
                     ->orderBy("r.percentage","DESC")->getQuery();
                     
                     $rr = $query->execute();
                     $result = [];

                     if(sizeof($rr) > 0){
                        foreach($rr as $r){
                            $flag = false;
                            $subjects = $r['subjects_choosen'];
                            if(sizeof($subjects) > 0){
                                foreach($subjects as $sub){
                                    if($sub['type'] == $type && $sub['subject']['sub_code'] == $sub_code){
                                        $flag = true;
                                    }
                                }
                            }

                            if($flag){
                                array_push($result,$r);
                            }
                        }
                     }
                } else {
                    $qb = $em->createQueryBuilder();
                    $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                     ->from('AdmissionForm','r')
                     ->andWhere('r.is_complete=1')
                     ->andWhere('r.is_verified=1')
                     ->andWhere('r.course=:course')
                     ->andWhere('r.semester=:semester')
                     ->andWhere('r.session_id=:session_id')
                     ->andWhere('r.caste=:caste')
                     ->andWhere("JSON_CONTAINS(JSON_EXTRACT(r.subjects_choosen, '$[*].type'), :type) = 1")
                     ->setParameters(array(
                     "course"=>$course,
                     "semester"=>$semester,
                     "session_id"=>$session_id,
                     "caste"=>$caste,
                     "type"=>json_encode([$type])))
                     ->orderBy("r.percentage","DESC")->getQuery();
                     
                     $rr = $query->execute();
                     $result = [];

                     if(sizeof($rr) > 0){
                        foreach($rr as $r){
                            $flag = false;
                            $subjects = $r['subjects_choosen'];
                            if(sizeof($subjects) > 0){
                                foreach($subjects as $sub){
                                    if($sub['type'] == $type && $sub['subject']['sub_code'] == $sub_code){
                                        $flag = true;
                                    }
                                }
                            }

                            if($flag){
                                array_push($result,$r);
                            }
                        }
                     }
                }


            }


            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });

            return $result;
        } 

        else {
            if ($caste == 'GENERAL' || $caste == '') {

                    if ($is_verified == 0) {
                        $qb = $em->createQueryBuilder();
                        $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                         ->from('AdmissionForm','r')
                         ->andWhere('r.is_complete=1')
                         ->andWhere('r.course=:course')
                         ->andWhere('r.semester=:semester')
                         ->andWhere('r.session_id=:session_id')
                         ->setParameters(array(
                         "course"=>$course,
                         "semester"=>$semester,
                         "session_id"=>$session_id
                        ))
                         ->orderBy("r.percentage","DESC")->getQuery();
                         
                         $result = $query->execute();
                       

                         
                    } else {
                        $qb = $em->createQueryBuilder();
                        $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                         ->from('AdmissionForm','r')
                         ->andWhere('r.is_complete=1')
                         ->andWhere('r.is_verified=1')
                         ->andWhere('r.course=:course')
                         ->andWhere('r.semester=:semester')
                         ->andWhere('r.session_id=:session_id')
                         ->setParameters(array(
                         "course"=>$course,
                         "semester"=>$semester,
                         "session_id"=>$session_id
                        ))
                         ->orderBy("r.percentage","DESC")->getQuery();
                         
                         $result = $query->execute();
                    
                    }
                
            } else {
               

                if ($is_verified == 0) {
                    
                    $qb = $em->createQueryBuilder();
                    $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                     ->from('AdmissionForm','r')
                     ->andWhere('r.is_complete=1')
                     ->andWhere('r.course=:course')
                     ->andWhere('r.semester=:semester')
                     ->andWhere('r.session_id=:session_id')
                     ->andWhere('r.caste=:caste')
                     ->setParameters(array(
                     "course"=>$course,
                     "semester"=>$semester,
                     "session_id"=>$session_id,
                     "caste"=>$caste
                     ))
                     ->orderBy("r.percentage","DESC")->getQuery();
                     
                     $result = $query->execute();

                    
                } else {
                    $qb = $em->createQueryBuilder();
                    $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded,r.subjects_choosen")
                     ->from('AdmissionForm','r')
                     ->andWhere('r.is_complete=1')
                     ->andWhere('r.is_verified=1')
                     ->andWhere('r.course=:course')
                     ->andWhere('r.semester=:semester')
                     ->andWhere('r.session_id=:session_id')
                     ->andWhere('r.caste=:caste')
                     ->setParameters(array(
                     "course"=>$course,
                     "semester"=>$semester,
                     "session_id"=>$session_id,
                     "caste"=>$caste
                     ))
                     ->orderBy("r.percentage","DESC")->getQuery();
                     
                     $result = $query->execute();

                    
                }


            }


            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });

            return $result;
        } 
    }

    public function admissionDone($form_id, $student_id)
    {
        $em = $this->getEntityManager();

        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0) {
            $r[0]->setAdmissionDone(1);
            $r[0]->setStudentId($student_id);
            $em->persist($r[0]);
            $em->flush();
            return true;
        } else {
            return false;
        }
    }

    public function paymentDone($form_id)
    {
        $em = $this->getEntityManager();

        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0) {
            $r[0]->setAdmissionPaymentDone(1);
            $em->persist($r[0]);
            $em->flush();
            return true;
        } else {
            return false;
        }
    }

    public function getMeritListShort($body)
    {
        $em = $this->getEntityManager();

        $r = $em->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r) > 0) {
            $session_id = $r[0]->getId();
        } else {
            $session_id = 0;
        }

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $course_type = addslashes(strip_tags(trim($body['course_type'])));
        $caste = addslashes(strip_tags(trim($body['caste'])));
        $is_verified = addslashes(strip_tags(trim($body['is_verified'])));

        if ($course == 'TDC') {

            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_verified == 0) {

                    if ($course_type == '') {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            //->setParameter('course_type', $course_type)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('course_type', $course_type)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('stream', $stream);
                    }
                } else {

                    if ($course_type == '') {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            //->setParameter('course_type', $course_type)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('course_type', $course_type)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('stream', $stream);
                    }
                }
            } else {
                if ($is_verified == 0) {
                    if ($course_type == '') {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            //->setParameter('course_type', $course_type)
                            ->setParameter('stream', $stream)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('caste', $caste);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('course_type', $course_type)
                            ->setParameter('stream', $stream)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('caste', $caste);
                    }
                } else {

                    if ($course_type == '') {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            //->setParameter('course_type', $course_type)
                            ->setParameter('stream', $stream)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('caste', $caste);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('course_type', $course_type)
                            ->setParameter('stream', $stream)
                            ->setParameter('session_id', $session_id)
                            ->setParameter('caste', $caste);
                    }
                }
            }

            $result = $query->getResult();


            // if ($caste == 'GENERAL' || $caste == '') {
            //     if ($is_verified == 0) {

            //         if ($course_type == '') {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 //->setParameter('course_type', $course_type)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('stream', $stream);
            //         } else {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 ->setParameter('course_type', $course_type)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('stream', $stream);
            //         }
            //     } else {

            //         if ($course_type == '') {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 //->setParameter('course_type', $course_type)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('stream', $stream);
            //         } else {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 ->setParameter('course_type', $course_type)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('stream', $stream);
            //         }
            //     }
            // } else {
            //     if ($is_verified == 0) {
            //         if ($course_type == '') {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 //->setParameter('course_type', $course_type)
            //                 ->setParameter('stream', $stream)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('caste', $caste);
            //         } else {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 ->setParameter('course_type', $course_type)
            //                 ->setParameter('stream', $stream)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('caste', $caste);
            //         }
            //     } else {

            //         if ($course_type == '') {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 //->setParameter('course_type', $course_type)
            //                 ->setParameter('stream', $stream)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('caste', $caste);
            //         } else {
            //             $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.best_subjects,r.is_verified,r.verified_by,r.verified_on FROM AdmissionForm r WHERE r.course=:course AND r.course_type=:course_type AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1";
            //             $query = $em->createQuery($dql)
            //                 ->setParameter('course', $course)
            //                 ->setParameter('course_type', $course_type)
            //                 ->setParameter('stream', $stream)
            //                 ->setParameter('session_id', $session_id)
            //                 ->setParameter('caste', $caste);
            //         }
            //     }
            // }

            // $result = $query->getResult();

            $index = 0;
            foreach ($result as $r) {
                $e = $r['best_subjects'];
                $t = 300;
                $o = 0;
                $p = 0;

                foreach ($e as $e1) {
                    if (array_key_exists('marks_secured', $e1)) {
                        $o = $o + (float) $e1['marks_secured'];
                    }
                }

                $p = ($o / $t) * 100;
                $p = number_format((float) $p, 2, '.', '');

                $result[$index]['marks_obtained'] = $o;
                $result[$index]['percentage'] = $p;

                $index++;
            }

            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });

            return $result;
        } else if ($course == 'HS') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream);
                }
            } else {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('caste', $caste)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('caste', $caste)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream);
                }
            }

            $result = $query->getResult();

            return $result;
        } else if ($course == 'BSC-BIOTECHNOLOGY') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('course', $course);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('course', $course);
                }
            } else {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('caste', $caste);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('caste', $caste);
                }
            }

            $result = $query->getResult();

            return $result;
        } else if ($course == 'BCA') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('course', $course);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('course', $course);
                }
            } else {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone, r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('caste', $caste);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.percentage,r.email,r.phone, r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('caste', $caste);
                }
            }

            $result = $query->getResult();

            return $result;
        } else if ($course == 'PG') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.ranking_score as percentage,r.university_roll_no,r.ranking_serial,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.ranking_score DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream)
                        ->setParameter('course', $course);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.ranking_score as percentage,r.university_roll_no,r.ranking_serial,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.ranking_score DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream)
                        ->setParameter('course', $course);
                }
            } else {
                if ($is_verified == 0) {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.ranking_score as percentage,r.university_roll_no,r.ranking_serial,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.ranking_score DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream)
                        ->setParameter('course', $course)
                        ->setParameter('caste', $caste);
                } else {
                    $dql = "SELECT r.id, r.form_id,r.name,r.course,r.course_type,r.stream,r.caste,r.is_pwd,r.state,r.marks_obtained,r.ranking_score as percentage,r.university_roll_no,r.ranking_serial,r.email,r.phone,r.is_verified,r.verified_by,r.verified_on,r.exam_passed FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.session_id=:session_id AND r.is_verified=1 ORDER BY r.ranking_score DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('stream', $stream)
                        ->setParameter('course', $course)
                        ->setParameter('caste', $caste);
                }
            }

            $result = $query->getResult();

            return $result;
        }
    }

    public function setNewAdminData($body)
    {

        $em = $this->getEntityManager();

        $form_id = addslashes(strip_tags(trim($body['form_id'])));
        $total_marks = addslashes(strip_tags(trim($body['total_marks'])));
        $marks_obtained = addslashes(strip_tags(trim($body['marks_obtained'])));
        $percentage = addslashes(strip_tags(trim($body['percentage'])));

        $last_marks = $body['last_marks'];
        $best_subjects = $body['best_subjects'];

        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0) {
            $r[0]->setTotalMarks($total_marks);
            $r[0]->setMarksObtained($marks_obtained);
            $r[0]->setPercentage($percentage);
            $r[0]->setLastMarks($last_marks);
            $r[0]->setBestSubjects($best_subjects);

            $em->persist($r[0]);
            $em->flush();

            return 'Successfully Updated';
        }
    }

    public function getOnlineFeeReport($body)
    {
        $em = $this->getEntityManager();

        $starting_date = addslashes(strip_tags(trim($body['starting_date'])));
        $ending_date = addslashes(strip_tags(trim($body['ending_date'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));

        if ($course != '') {
            if ($stream != '' && $semester == '1') {
                $dql = "SELECT r.id,r.form_id,r.name,r.father_name,r.payment_status,r.transaction,r.date_of_admission,r.course,r.stream,r.semester FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.payment_status='1' AND r.transaction IS NOT NULL AND r.date_of_admission >= :starting_date AND r.date_of_admission <= :ending_date AND (r.semester = '1' OR r.semester IS NULL)";
                $query = $this->getEntityManager()->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('starting_date', $starting_date)
                    ->setParameter('ending_date', $ending_date);
                //->setParameter('semester', $semester);
                $result = $query->getResult();
            } else if ($stream != '' && $semester != '1') {
                $dql = "SELECT r.id,r.form_id,r.name,r.father_name,r.payment_status,r.transaction,r.date_of_admission,r.course,r.stream,r.semester FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.payment_status='1' AND r.transaction IS NOT NULL AND r.date_of_admission >= :starting_date AND r.date_of_admission <= :ending_date AND r.semester=:semester";
                $query = $this->getEntityManager()->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('stream', $stream)
                    ->setParameter('starting_date', $starting_date)
                    ->setParameter('ending_date', $ending_date)
                    ->setParameter('semester', $semester);
                $result = $query->getResult();
            }
        } else {
            $dql = "SELECT r.id,r.form_id,r.name,r.father_name,r.payment_status,r.transaction,r.date_of_admission,r.course,r.stream,r.semester FROM AdmissionForm r WHERE r.payment_status='1' AND r.transaction IS NOT NULL AND r.date_of_admission >= :starting_date AND r.date_of_admission <= :ending_date";
            $query = $this->getEntityManager()->createQuery($dql)
                ->setParameter('starting_date', $starting_date)
                ->setParameter('ending_date', $ending_date);
            $result = $query->getResult();
        }

        $index = 0;
        foreach ($result as $r) {
            $transaction = $this->goodTransaction($r['transaction']);

            /*
            $a['status'] = 'SUCCESS';
            $a['form_id'] = $m[1];
            $a['Ref_no'] = $m[2];
            $a['BANK_Ref_no'] = $m[3];
            $a['amount'] = $m[4];
            $a['date'] = $m[13];
             */
            $result[$index]['receipt_no'] = $transaction['Ref_no'];
            $result[$index]['BANK_Ref_no'] = $transaction['Ref_no'];
            $result[$index]['amount'] = $transaction['amount'];
            $result[$index]['date'] = $r['date_of_admission']->format('d-m-Y');
            $index++;
        }

        return $result;
    }
    public function getDpFromStudentId($id)
    {
        $dql = "SELECT r.dp FROM AdmissionForm r WHERE r.student_id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            return $result[0]['dp'];
        } else {
            return [];
        }
    }

    public function getFatherDetails($email)
    {
        $dql = "SELECT r.father_name,r.gender,r.caste,r.religion,r.permanent_address FROM AdmissionForm r WHERE r.email=:email";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('email', $email);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            $b['father_name'] = $result[0]['father_name'];
            $b['gender'] = $result[0]['gender'];
            $b['caste'] = $result[0]['caste'];
            $b['religion'] = $result[0]['religion'];
            $b['permanent_address'] = $result[0]['permanent_address'];
            return $b;
        } else {
            $b['father_name'] = '';
            $b['gender'] = '';
            $b['caste'] = '';
            $b['religion'] = '';
            $b['permanent_address'] = '';
            return $b;
        }
    }

    public function getListForVerification($body)
    {
        $em = $this->getEntityManager();

        $course = addslashes(strip_tags(trim($body['course'])));
        $dept_code = addslashes(strip_tags(trim($body['dept_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));

        $subject = '';
        if (array_key_exists('subject', $body)) {
            $subject = addslashes(strip_tags(trim($body['subject'])));
        }

        $session_id = $em->getRepository('SessionMaster')->getActiveSessionSingle();
       


        if ($course != 'PG') {

                if ($subject == '') {
                    $dql = "SELECT r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded FROM AdmissionForm r WHERE r.course=:course AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                    $query = $em->createQuery($dql)
                        ->setParameter('course', $course)
                        ->setParameter('session_id', $session_id)
                        ->setParameter('semester', $semester);
                    $result = $query->getResult();
                } 
                else {
                   $qb = $em->createQueryBuilder();
                   $query = $qb->select("r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded")
                    ->from('AdmissionForm','r')
                    ->andWhere('r.is_complete=1')
                    ->andWhere('r.course=:course')
                    ->andWhere('r.semester=:semester')
                    ->andWhere('r.session_id=:session_id')
                    ->andWhere("JSON_CONTAINS(JSON_EXTRACT(r.subjects_choosen, '$[*].subject.value'), :subject) = 1")
                    ->setParameters(array("course"=>$course,"semester"=>$semester,"session_id"=>$session_id,"subject"=>json_encode([$subject])))
                    ->orderBy("r.percentage","DESC")->getQuery();
                    
                    $result = $query->execute();
                } 
            
        } else if ($course == 'PG') {

            if($dept_code == ''){
            $dql = "SELECT r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded FROM AdmissionForm r WHERE r.course=:course AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
            $query = $em->createQuery($dql)
                ->setParameter('course', $course)
                ->setParameter('session_id', $session_id)
                ->setParameter('semester', $semester);
            $result = $query->getResult();
            }

            else if($dept_code != ''){
                $dql = "SELECT r.id,r.form_id,r.ranking_score,r.ranking_serial,r.is_complete,r.father_email,r.mother_phone,r.local_g_phone,r.roll_no,r.maratial_status,r.has_hostel,r.annual_income,r.mother_toungue,r.languages_known,r.university_roll_no,r.is_free_admission,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_phone,r.account_no,r.account_holder,r.account_branch,r.account_bank,r.account_ifsc,r.account_micr,r.father_name,r.mother_name,r.gender,r.caste,r.date_of_birth,r.phone,r.p_occupation,r.quota,r.state,r.religion,r.nationality,r.blood_group,r.exam_passed,r.best_subjects,r.last_marks,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.is_submit,r.payment_status,r.transaction,r.active_step,r.is_pwd,r.is_complete,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.is_admitted,r.request_id,r.request_type,r.father_qualification,r.father_profession,r.father_email,r.father_phone,r.mother_qualification,r.mother_profession,r.mother_phone,r.local_address,r.activities,r.subjects_choosen,r.has_gap,r.gap_admission,r.gap_course,r.gap_institute,r.gap_roll_no,r.gap_reason,r.unique_id,r.is_ews,r.documents_required,r.documents_uploaded FROM AdmissionForm r WHERE r.course=:course AND r.dept_code=:dept_code AND r.semester=:semester AND r.is_complete='1' AND r.session_id=:session_id ORDER BY r.percentage DESC";
                $query = $em->createQuery($dql)
                    ->setParameter('course', $course)
                    ->setParameter('session_id', $session_id)
                    ->setParameter('dept_code', $dept_code)
                    ->setParameter('semester', $semester);
    
                $result = $query->getResult();
                }

        } 


        $total = 0;
        $total_verified = 0;
        $total_defective = 0;
        $total_rejected = 0;


        $index = 0;
        foreach ($result as $r) {
            $total = $total + 1;
            if ($r['is_verified'] == 1) {
                $total_verified = $total_verified + 1;
            }

            if ($r['is_verified'] == 2) {
                $total_rejected = $total_rejected + 1;
            }

            if ($r['is_verified'] == 3) {
                $total_defective = $total_defective + 1;
            }
            $a = [];
            if (is_array($r['compulsory_1']) && sizeof($r['compulsory_1']) !== 0) {
                array_push($a, $r['compulsory_1']);
            }

            if (is_array($r['compulsory_2']) && sizeof($r['compulsory_2']) !== 0) {
                array_push($a, $r['compulsory_2']);
            }

            if (is_array($r['compulsory_3']) && sizeof($r['compulsory_3']) !== 0) {
                array_push($a, $r['compulsory_3']);
            }

            if (is_array($r['major_1']) && sizeof($r['major_1']) !== 0) {
                array_push($a, $r['major_1']);
            }

            if (is_array($r['major_2']) && sizeof($r['major_2']) !== 0) {
                array_push($a, $r['major_2']);
            }

            if (is_array($r['mil']) && sizeof($r['mil']) !== 0) {
                array_push($a, $r['mil']);
            }

            if (is_array($r['generic_1']) && sizeof($r['generic_1']) !== 0) {
                array_push($a, $r['generic_1']);
            }

            if (is_array($r['generic_2']) && sizeof($r['generic_2']) !== 0) {
                array_push($a, $r['generic_2']);
            }

            if (is_array($r['generic_3']) && sizeof($r['generic_3']) !== 0) {
                array_push($a, $r['generic_3']);
            }

            if (is_array($r['optional_subjects']) && sizeof($r['optional_subjects']) !== 0) {
                $a = array_merge($a, $r['optional_subjects']);
            }

            $result[$index]['subjects'] = $a;

            $index++;
        }

        $t = [];
        $t['total'] = $total;
        $t['total_verified'] = $total_verified;
        $t['total_defective'] = $total_defective;
        $t['total_rejected'] = $total_rejected;


        $b = [];
        $b['result'] = $result;
        $b['stats'] = $t;



        return $b;
    }

    public function updateVerificationStatus($body, $teacher_id)
    {
        $em = $this->getEntityManager();


        $form_id = addslashes(strip_tags(trim($body['form_id'])));
        $form_status = addslashes(strip_tags(trim($body['form_status'])));
        $active_step = addslashes(strip_tags(trim($body['active_step'])));

        $rejection_reason = '';
        if (array_key_exists('rejection_reason', $body)) {
            $rejection_reason = addslashes(strip_tags(trim($body['rejection_reason'])));
        }

        $final_major = addslashes(strip_tags(trim($body['final_major'])));
        $final_generic = addslashes(strip_tags(trim($body['final_generic'])));

        // get subject object by subject code

        $subObj = $em->getRepository('SubjectMaster')->getSingleSubjectBySubjectCode($final_major);
        $subObj1 = $em->getRepository('SubjectMaster')->getSingleSubjectBySubjectCode($final_generic);

        $r = $this->getActiveStudentSingleByFormId1($form_id);
        $flag = true;
        $d = date('d-m-Y h:i:s a');
        if (sizeof($r) > 0) {
            $r[0]->setIsVerified($form_status);
            if ($form_status != 1) {
                $r[0]->setActiveStep($active_step);
            }

            // verified by & on
            $r[0]->setVerifiedOn($d);
            $r[0]->setVerifiedBy($teacher_id);


            if ($form_status == 3 || $form_status == 2) {
                $r[0]->setRejectionReason($rejection_reason);
            }

            $r[0]->setFinalMajor($subObj);
            $r[0]->setFinalGeneric($subObj1);


            $em->persist($r[0]);
            $em->flush();
        }
        return $flag;
    }

    public function updateVerificationStatusWithFee($form_id,$is_free_admission,$fee_group_id, $payment_mode, $transaction_id)
    {
        $em = $this->getEntityManager();
        $r = $this->getActiveStudentSingleByFormId1($form_id);
        $flag = true;
        $receipt_no  = '';
        $d = date('d-m-Y h:i:s a');
        if (sizeof($r) > 0) {
            
           
            // verified by & on
            
            $r[0]->setIsFreeAdmission($is_free_admission);
           
            $b1 = $em->getRepository('PaymentsMaster')->getPaymentsForAdmissionById($fee_group_id);
            if($b1 == false)
            return array('status' => false, 'receipt_no' => '');

                $b = [];
                $b['form_id'] = $form_id;
                $b['fees_group_id'] = $fee_group_id;
                $b['name'] = $r[0]->getName();
                $b['email'] = $r[0]->getEmail();
                $b['course'] = $r[0]->getCourse();
                $b['course_type'] = $r[0]->getCourseType();
                $b['email'] = $r[0]->getEmail();
                $b['amount_paid'] = $b1['payment_amount'];
                $b['fees_table'] = $b1['payment_table'];
                $b['transaction_id'] = $transaction_id;
                $b['type'] = 0; // new admission
                $b['mop'] = $payment_mode;
                    
                
                $r[0]->setAdmissionAmountPaid($b['amount_paid']);

                    if($payment_mode == 'ONLINE'){
                         $em->getRepository('AdmissionTempReceipt')->addAdmissionReceiptNew($b);
                    }

            
                if($payment_mode == 'OFFLINE'){
                    $b2 = $em->getRepository('AdmissionReceipt')->addAdmissionReceiptNew($b);
                    $flag = $b2['status'];
                    $receipt_no = $b2['new_receipt_no'];

                    if ($flag) {
                    $r[0]->setIsAdmitted(1);
                    $r[0]->setPaymentStatus(1);
                    $r[0]->setAdmissionPaymentDone(1);
                    $r[0]->setAdmissionDone(1);
                    $r[0]->setIsAdmitted(1);
                    $r[0]->setDateOfAdmission(date('d-m-Y'));

                    if($r[0]->getAdmissionType() == 0){
                        
                        $real_roll = $this->getRealRollNumber($r[0]->getCourse(), $r[0]->getStream(),$r[0]->getSessionId());
                        $r[0]->setRollNo($real_roll);    
                    }
                    
                }
               else return array('status' => $flag, 'receipt_no' => $receipt_no);

                }
                $em->persist($r[0]);
                $em->flush();
                return array('status' => true, 'receipt_no' => $receipt_no);
            
        }
        return array('status' => $flag, 'receipt_no' => $receipt_no);
    }

    

    public function updateBasicDetailsOnVerification($body)
    {
        $em = $this->getEntityManager();
   

            $account_holder = addslashes(strip_tags(trim($body['account_holder'])));
            $account_bank = addslashes(strip_tags(trim($body['account_bank'])));
            $account_no = addslashes(strip_tags(trim($body['account_no'])));
            $account_branch = addslashes(strip_tags(trim($body['account_branch'])));
            $account_ifsc = addslashes(strip_tags(trim($body['account_ifsc'])));
            $account_micr = addslashes(strip_tags(trim($body['account_micr'])));
            $state = addslashes(strip_tags(trim($body['state'])));
            $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'])));
            $mother_phone = addslashes(strip_tags(trim($body['mother_phone'])));
            $father_phone = addslashes(strip_tags(trim($body['father_phone'])));
            $father_profession = addslashes(strip_tags(trim($body['father_profession'])));
            $mother_profession = addslashes(strip_tags(trim($body['mother_profession'])));
           
            
            $phone = addslashes(strip_tags(trim($body['phone'])));
            $religion = addslashes(strip_tags(trim($body['religion'])));
            $nationality = addslashes(strip_tags(trim($body['nationality'])));
            $annual_income = addslashes(strip_tags(trim($body['annual_income'])));

            $gender = addslashes(strip_tags(trim($body['gender'])));
            $is_pwd = addslashes(strip_tags(trim($body['is_pwd'])));
            $is_ews = addslashes(strip_tags(trim($body['is_ews'])));

            $caste = addslashes(strip_tags(trim($body['caste'])));
            $date_of_birth = addslashes(strip_tags(trim($body['date_of_birth'])));
            $form_id = addslashes(strip_tags(trim($body['form_id'])));

        $r = $this->getActiveStudentSingleByFormId1($form_id);


        if (sizeof($r) > 0) {
            $r[0]->setAccountHolder($account_holder);
            $r[0]->setAccountBank($account_bank);
            $r[0]->setAccountNo($account_no);
            $r[0]->setAccountBranch($account_branch);
            $r[0]->setAccountIfsc($account_ifsc);
            $r[0]->setAccountMicr($account_micr);
            $r[0]->setName($name);
            $r[0]->setFatherName($father_name);
            $r[0]->setMotherName($mother_name);
            $r[0]->setFatherPhone($father_phone);
            $r[0]->setMotherPhone($mother_phone);
            $r[0]->setFatherProfession($father_profession);
            $r[0]->setMotherProfession($mother_profession);
            $r[0]->setPhone($phone);
            $r[0]->setReligion($religion);
            $r[0]->setNationality($nationality);
            $r[0]->setAnnualIncome($annual_income);
            $r[0]->setGender($gender);
            $r[0]->setCaste($caste);
            $r[0]->setState($state);
            $r[0]->setIsPwd($is_pwd);
            $r[0]->setIsEws($is_ews);
            $r[0]->setDateOfBirth($date_of_birth);

            $em->persist($r[0]);
            $em->flush();
            return true;
        }
    }


    public function updateSubjectDetailssOnVerification($form_id, $course, $stream, $compulsory_1)
    {
        $em = $this->getEntityManager();
        $r = $this->getActiveStudentSingleByFormId1($form_id);


        if (sizeof($r) > 0) {
            $r[0]->setCourse($course);
            
            $r[0]->setCompulsory1($compulsory_1);

            $em->persist($r[0]);
            $em->flush();
            return true;
        }
    }

    public function getListForSendingRequests($body)
    {
        $em = $this->getEntityManager();
        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $course_type = addslashes(strip_tags(trim($body['course_type'])));
        $preference = addslashes(strip_tags(trim($body['preference'])));
        $sub_code = addslashes(strip_tags(trim($body['subject'])));
        $caste = addslashes(strip_tags(trim($body['caste'])));
        $is_verified = addslashes(strip_tags(trim($body['is_verified'])));
        $is_pwd = '';
        if (array_key_exists('is_pwd', $body)) {
            $is_pwd = addslashes(strip_tags(trim($body['is_pwd'])));
        }

        if ($course == 'TDC') {


            if ($course_type == 'HONOURS') {
                if ($caste == 'GENERAL' || $caste == '') {

                    if ($stream != 'COMMERCE') {

                        if ($preference == '1') {

                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_1->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_1->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        } else if ($preference == '2') {
                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_2->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' ORDER BY percentage DESC";

                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_2->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";

                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        } else if ($preference == '') {
                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE  course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE  course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        }
                    } else {
                        if ($is_verified == 0) {
                            $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE  course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' ORDER BY percentage DESC";
                            $stmt = $em->getConnection()->prepare($sql);
                            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                            $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                            $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                            //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                            $stmt->execute();
                        } else {
                            $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE  course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                            $stmt = $em->getConnection()->prepare($sql);
                            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                            $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                            //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                            $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);

                            $stmt->execute();
                        }
                    }
                } else {

                    if ($stream != 'COMMERCE') {



                        if ($preference == '1') {

                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_1->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_1->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        } else if ($preference == '2') {
                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_2->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE major_2->>'$.value'=:sub_code AND course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        } else if ($preference == '') {
                            if ($is_verified == 0) {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                                $stmt->execute();
                            } else {
                                $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                                $stmt = $em->getConnection()->prepare($sql);
                                $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                                $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                                //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                                $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                                $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);

                                $stmt->execute();
                            }
                        }
                    } else {
                        if ($is_verified == 0) {
                            $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                            $stmt = $em->getConnection()->prepare($sql);
                            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                            $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                            //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                            $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                            $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                            $stmt->execute();
                        } else {
                            $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                            $stmt = $em->getConnection()->prepare($sql);
                            $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                            $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                            //$stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
                            $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                            $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);

                            $stmt->execute();
                        }
                    }
                }
            } else if ($course_type == 'REGULAR') {
                if ($caste == 'GENERAL' || $caste == '') {



                    if ($is_verified == 0) {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);
                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);

                        $stmt->execute();
                    } else {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);

                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                        $stmt->execute();
                    }
                } else {


                    if ($is_verified == 0) {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);

                        $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                        $stmt->execute();
                    } else {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);

                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                        $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                        $stmt->execute();
                    }

                    if ($is_verified == 0) {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);

                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                        $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                        $stmt->execute();
                    } else {
                        $sql = "SELECT id, form_id,name,course,course_type,stream,caste,is_pwd,marks_obtained,percentage,email,phone,last_marks,best_subjects,major_1,major_2,generic_1,generic_2,father_name,is_verified,verified_by,verified_on, rejection_reason,is_pwd,is_verified,verified_by,verified_on,rejection_reason from admission_form  WHERE course=:course AND stream=:stream AND course_type=:course_type AND caste=:caste AND is_complete='1' AND is_verified=1 ORDER BY percentage DESC";
                        $stmt = $em->getConnection()->prepare($sql);
                        $stmt->bindParam(':stream', $stream, PDO::PARAM_STR);
                        $stmt->bindParam(':course', $course, PDO::PARAM_STR);

                        $stmt->bindParam(':course_type', $course_type, PDO::PARAM_STR);
                        $stmt->bindParam(':caste', $caste, PDO::PARAM_STR);
                        $stmt->execute();
                    }
                }
            }




            $result = $stmt->fetchAll();


            $index = 0;
            foreach ($result as $r) {
                $e = json_decode($r['best_subjects'], true);
                $t = 300;
                $o = 0;
                $p = 0;

                if (is_array($e) && sizeof($e) > 0) {
                    foreach ($e as $e1) {
                        if (array_key_exists('marks_secured', $e1)) {
                            $o = $o + (float) $e1['marks_secured'];
                        }
                        // if (array_key_exists('total_marks', $e1))
                        //     $t = $t + (float) $e1['total_marks'];
                    }
                }

                if ($t != 0) {
                    $p = ($o / $t) * 100;
                } else {
                    $p = 0;
                }

                $p = number_format((float) $p, 2, '.', '');

                $result[$index]['marks_obtained'] = $o;
                $result[$index]['percentage'] = $p;


                $result[$index]['major_1'] = json_decode($r['major_1'], true);
                $result[$index]['major_2'] = json_decode($r['major_2'], true);

                $result[$index]['generic_1'] = json_decode($r['generic_1'], true);
                $result[$index]['generic_2'] = json_decode($r['generic_2'], true);

                $index++;
            }

            usort($result, function ($a, $b) { //Sort the array using a user defined function
                return $a['percentage'] > $b['percentage'] ? -1 : 1; //Compare the scores
            });


            // now get admission request if any for this form
            $index11 = 0;
            foreach ($result as $r) {
                $result[$index11]['admission_request'] = $em->getRepository('AdmissionRequest')->getAdmissionRequestByFormID($r['form_id']);
                $index11++;
            }

            return $result;
        } else if ($course == 'HS' || $course == 'BCA' || $course == 'BSC-BIOTECHNOLOGY') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_pwd == '') {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                } else {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_pwd=:is_pwd AND r.is_complete='1' ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_pwd=:is_pwd AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                }
            } else {

                if ($is_pwd == '') {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_verified=:is_verified AND r.is_complete='1' ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                } else {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.is_pwd=:is_pwd  ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd  FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_pwd=:is_pwd AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                }
            }

            $result = $query->getResult();

            // now get admission request if any for this form
            $index11 = 0;
            foreach ($result as $r) {
                $result[$index11]['admission_request'] = $em->getRepository('AdmissionRequest')->getAdmissionRequestByFormID($r['form_id']);
                $index11++;
            }

            return $result;
        } else if ($course == 'PG') {
            if ($caste == 'GENERAL' || $caste == '') {
                if ($is_pwd == '') {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage, r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                } else {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_pwd=:is_pwd AND r.is_complete='1' ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.father_name,r.stream,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_pwd=:is_pwd AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                }
            } else {

                if ($is_pwd == '') {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_verified=:is_verified AND r.is_complete='1' ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                } else {
                    if ($is_verified == 0) {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_complete='1' AND r.is_pwd=:is_pwd  ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('stream', $stream);
                    } else {
                        $dql = "SELECT r.id, r.form_id,r.is_verified,r.verified_by,r.verified_on,r.rejection_reason,r.name,r.course,r.course_type,r.stream,r.father_name,r.caste,r.is_pwd,r.marks_obtained,r.percentage,r.email,r.phone,r.is_pwd,r.last_exam_percentage as percentage,r.compulsory_1 as major_1,r.last_exam_obtained as marks_obtained FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.caste=:caste AND r.is_pwd=:is_pwd AND r.is_complete='1' AND r.is_verified=:is_verified ORDER BY r.last_exam_percentage DESC";
                        $query = $em->createQuery($dql)
                            ->setParameter('is_pwd', $is_pwd)
                            ->setParameter('course', $course)
                            ->setParameter('caste', $caste)
                            ->setParameter('is_verified', $is_verified)
                            ->setParameter('stream', $stream);
                    }
                }
            }

            $result = $query->getResult();

            // now get admission request if any for this form
            $index11 = 0;
            foreach ($result as $r) {
                $result[$index11]['admission_request'] = $em->getRepository('AdmissionRequest')->getAdmissionRequestByFormID($r['form_id']);
                $index11++;
            }

            return $result;
        }
    }

    public function fecthAdmittedListThisSession($course, $stream)
    {
        $entityManager = $this->getEntityManager();

        $r = $entityManager->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r) > 0) {
            $session_id = $r[0]->getId();

            $dql = "SELECT r.id,r.form_id,r.roll_no,r.date_of_admission as admission_date,r.class_id,r.class_name,r.is_free_admission,r.subject_comb,r.total_marks,r.marks_obtained,r.percentage,r.office_use,r.name,r.email,r.father_name,r.father_phone,r.father_email,r.mother_name,r.mother_phone,r.maratial_status,r.mother_toungue,r.languages_known,r.local_g_phone,r.annual_income,r.gender,r.caste,r.date_of_birth,r.phone,r.quota,r.state,r.religion,r.nationality,r.exam_passed,r.best_subjects,r.last_marks,r.final_major,r.final_generic,r.major_1,r.major_2,r.compulsory_1,r.compulsory_2,r.compulsory_3,r.mil,r.generic_1,r.generic_2,r.generic_3,r.optional_subjects,r.course_type,r.course,r.stream,r.dept_code,r.semester,r.session_id,r.date_of_admission,r.present_address,r.permanent_address,r.local_g,r.extra_curr,r.account_holder,r.account_bank,r.account_branch,r.account_no,r.account_ifsc,r.account_micr,r.father_phone,r.mother_phone,r.is_pwd,r.has_hostel,r.dp,r.signature,r.marksheet,r.age_proof,r.bank_details,r.gap_certificate,r.caste_certificate,r.certificate,r.is_del,r.is_submit,r.payment_status,r.transaction,r.active_step,r.domicile_certificate,r.pwd_certificate,r.is_verified,r.verified_on,r.verified_by,r.rejection_reason,r.signature,r.g_signature,r.unique_ack,r.unique_id,r.p_occupation,r.other_college FROM AdmissionForm r,SessionMaster s WHERE r.session_id=s.id AND r.is_admitted=1 AND r.course=:course AND r.stream=:stream ORDER BY r.roll_no";
            $query = $this->getEntityManager()->createQuery($dql)
                ->setParameter('stream', $stream)
                ->setParameter('course', $course);
            $result = $query->getResult();
            $index = 0;
            if (is_array($result) && sizeof($result) > 0) {
                foreach ($result as $r) {
                    $result[$index]['good_date_of_birth'] = date('d-m-Y', strtotime($r['date_of_birth']));
                    $result[$index]['good_admission_date'] = date('d-m-Y', strtotime($r['admission_date']));

                    $a = [];
                    if (is_array($r['compulsory_1']) && sizeof($r['compulsory_1']) !== 0) {
                        array_push($a, $r['compulsory_1']);
                    }

                    if (is_array($r['compulsory_2']) && sizeof($r['compulsory_2']) !== 0) {
                        array_push($a, $r['compulsory_2']);
                    }

                    if (is_array($r['compulsory_3']) && sizeof($r['compulsory_3']) !== 0) {
                        array_push($a, $r['compulsory_3']);
                    }

                    if (is_array($r['final_major']) && sizeof($r['final_major']) !== 0) {
                        array_push($a, $r['final_major']);
                    }

                    // if (is_array($r['major_1']) && sizeof($r['major_1']) !== 0) {
                    //     array_push($a, $r['major_1']);
                    // }

                    // if (is_array($r['major_2']) && sizeof($r['major_2']) !== 0) {
                    //     array_push($a, $r['major_2']);
                    // }

                    if (is_array($r['mil']) && sizeof($r['mil']) !== 0) {
                        array_push($a, $r['mil']);
                    }

                    if ($r['course'] == 'TDC' && ($r['stream'] == 'ARTS' || $r['stream'] == 'SCIENCE') && $r['course_type'] == 'HONOURS') {
                        if (is_array($r['final_generic']) && sizeof($r['final_generic']) !== 0) {
                            array_push($a, $r['final_generic']);
                        }
                    } else {
                        if (is_array($r['generic_1']) && sizeof($r['generic_1']) !== 0) {
                            array_push($a, $r['generic_1']);
                        }

                        if (is_array($r['generic_2']) && sizeof($r['generic_2']) !== 0) {
                            array_push($a, $r['generic_2']);
                        }

                        if (is_array($r['generic_3']) && sizeof($r['generic_3']) !== 0) {
                            array_push($a, $r['generic_3']);
                        }
                    }



                    if (is_array($r['optional_subjects']) && sizeof($r['optional_subjects']) !== 0) {
                        $a = array_merge($a, $r['optional_subjects']);
                    }

                    $result[$index]['subjects'] = $a;

                    $index++;
                }
            }
            return $result;
        } else {
            $session_id = 0;
            return false;
        }
    }

    public function getPhoneNumberByFormId($form_id)
    {
        $dql = "SELECT r.phone FROM AdmissionForm r WHERE r.form_id=:form_id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('form_id', $form_id);
        $result = $query->getResult();
        if (sizeof($result) > 0) {
            return $result[0]['phone'];
        } else {
            return false;
        }
    }

    public function setHostelStatus($form_id, $hostel, $unique_id, $other_college, $unique_ack, $signature, $g_signature, $fees_receipt)
    {
        $em = $this->getEntityManager();
        $r = $this->getActiveStudentSingleByFormId1($form_id);
        if (sizeof($r) > 0) {
            $r[0]->setHasHostel($hostel);
            $r[0]->setOtherCollege($other_college);
            $r[0]->setUniqueAck($unique_ack);
            $r[0]->setSignature($signature);
            $r[0]->setGSignature($g_signature);
            $r[0]->setFeesReceipt($fees_receipt);

            if ($unique_id != '') {
                $r[0]->setUniqueId($unique_id);
            }

            $em->persist($r[0]);
            $em->flush();
            return true;
        } else {
            return false;
        }
    }



    public function setAdmissionStatusOfStudent($body)
    {

        $em = $this->getEntityManager();

        $form_id = addslashes(strip_tags(trim($body['form_id'])));
        $request_id = addslashes(strip_tags(trim($body['request_id'])));
        $request_type = addslashes(strip_tags(trim($body['request_type'])));
        $admission_date = addslashes(strip_tags(trim($body['admission_date'])));

        $r1 = $em->getRepository('SessionMaster')->getActiveSession();
        if (sizeof($r1) > 0) {
            $session_id = $r1[0]->getId();
        } else {
            $session_id = 0;
        }

        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0) {
            $roll_no = $this->getRealRollNumber($r[0]->getCourse(), $r[0]->getStream(), $session_id);

            $r[0]->setRequestId($request_id);
            $r[0]->setRequestType($request_type);
            $r[0]->setDateOfAdmission($admission_date);
            $r[0]->setRollNo($roll_no);
            $r[0]->setIsAdmitted(1);


            $em->persist($r[0]);
            $em->flush();

            $b = [];
            $b['status'] = true;
            $b['status_text'] = 'Student Admitted';
            $b['roll_no'] = $roll_no;
            return $b;
        } else {
            $b = [];
            $b['status'] = false;
            $b['status_text'] = 'Student Admission Failed';
            $b['roll_no'] = '';
            return $b;
        }
    }

    private function getRealRollNumber($course, $stream, $session_id)
    {

        $em = $this->getEntityManager();

        $roll_serial = 1;

        // count no of already admitted student for this session

        $dql = "SELECT COUNT(r)+1 as c FROM AdmissionForm r WHERE r.course=:course AND r.stream=:stream AND r.is_admitted=1 AND r.session_id=:session_id";
        $query = $em->createQuery($dql)
            ->setParameter('session_id', $session_id)
            ->setParameter('course', $course)
            ->setParameter('stream', $stream);
        $result = $query->getResult();
        if ($result != null) {
            if (sizeof($result) > 0) {
                if ($result[0]['c'] == null) {
                    $roll_serial = 1;
                } else {
                    $roll_serial = $result[0]['c'];
                }
            } else {
                $roll_serial = 1;
            }
        } else {
            $roll_serial = 1;
        }


        // edit modiify roll nos
        if ($course == 'TDC' && $stream == 'SCIENCE') {
            $roll_serial = $roll_serial + 500;
        }

        if ($course == 'TDC' && $stream == 'COMMERCE') {
            $roll_serial = $roll_serial + 800;
        }

        if ($course == 'BCA') {
            $roll_serial = $roll_serial + 1000;
        }

        if ($course == 'BSC-BIOTECHNOLOGY') {
            $roll_serial = $roll_serial + 1100;
        }


        $c = '';
        if ($course === 'HS') {
            $c .= 'HS';

            if ($stream === 'ARTS') {
                $c .= 'A/';
            }

            if ($stream === 'SCIENCE') {
                $c .= 'S/';
            }

            if ($stream === 'COMMERCE') {
                $c .= 'C/';
            }

            $d = date('Y');
            $d1 = substr($d, -2);

            $c .= $d1 . '/';

            $r = sprintf('%03d', $roll_serial);

            $c .= $r;

            return $c;
        }

        if ($course == 'TDC') {
            if ($stream == 'ARTS') {
                $c .= 'BA/';
            }

            if ($stream == 'SCIENCE') {
                $c .= 'BSC/';
            }

            if ($stream == 'COMMERCE') {
                $c .= 'BCOM/';
            }

            $d = date('Y');
            $d1 = substr($d, -2);

            $c .= $d1 . '/';

            $r = sprintf('%03d', $roll_serial);

            $c .= $r;

            return $c;
        }


        if ($course == 'BCA') {

            $c .= 'BCA/';


            $d = date('Y');
            $d1 = substr($d, -2);

            $c .= $d1 . '/';

            $r = sprintf('%03d', $roll_serial);

            $c .= $r;

            return $c;
        }

        if ($course == 'BSC-BIOTECHNOLOGY') {

            $c .= 'BIOT/';


            $d = date('Y');
            $d1 = substr($d, -2);

            $c .= $d1 . '/';

            $r = sprintf('%03d', $roll_serial);

            $c .= $r;

            return $c;
        }
    }

    public function submitUniqueId($email, $unique_id)
    {
        $em = $this->getEntityManager();
        $r = $this->getStudentObjectByEmail($email);
        if (sizeof($r) > 0) {
            $r[0]->setUniqueId($unique_id);
            $em->persist($r[0]);
            $em->flush();
            return $unique_id;
        } else {
            return false;
        }
    }


    public function fecthAdmittedListThisSessionForImport($course, $stream, $session_id)
    {
        $entityManager = $this->getEntityManager();

        if ($course == 'TDC' || $course == 'HS') {
            $dql = "SELECT r FROM AdmissionForm r WHERE r.is_admitted=1 AND r.course=:course AND r.stream=:stream AND r.session_id=:session_id AND r.form_id IN (7951,7770,7456,7428,8061,7827,9317) ORDER BY r.roll_no";
            $query = $entityManager->createQuery($dql)
                ->setParameter('stream', $stream)
                ->setParameter('session_id', $session_id)
                ->setParameter('course', $course);
            $result = $query->getResult();
            return $result;
        } else if ($course == 'BCA' || $course == 'BSC-BIOTECHNOLOGY') {
            $dql = "SELECT r FROM AdmissionForm r WHERE r.is_admitted=1 AND r.course=:course AND r.session_id=:session_id ORDER BY r.roll_no";
            $query = $entityManager->createQuery($dql)
                ->setParameter('session_id', $session_id)
                ->setParameter('course', $course)->setMaxResults(2);
            $result = $query->getResult();
            return $result;
        }
    }


    public function cancelThisAdmission($email, $body)
    {

        $em = $this->getEntityManager();

        $form_id = addslashes(strip_tags(trim($body['form_id'])));


        $r = $this->getActiveStudentSingleByFormId1($form_id);

        if (sizeof($r) > 0) {
            $r[0]->setIsAdmitted(2);


            $em->persist($r[0]);
            $em->flush();


            $em->getRepository('AdmissionRequest')->cancelAdmissionRequest($body);

            $b = [];
            $b['status'] = true;
            $b['status_text'] = 'Admission Cancelled';
            $b['roll_no'] = '';
            return $b;
        } else {
            $b = [];
            $b['status'] = false;
            $b['status_text'] = 'Admission Cancelled';
            $b['roll_no'] = '';
            return $b;
        }
    }


    public function getStudentForPerformance($email,$name,$semester)
    {
        $entityManager = $this->getEntityManager();

        $dql = "SELECT r.id,r.university_roll_no,r.last_exam_passed,r.last_exam_total,r.last_exam_obtained,r.last_exam_percentage,r.last_exam_year,r.last_exam_university,r.last_exam_college,r.is_free_admission,r.subject_comb,r.total_marks,r.marks_obtained,r.percentage,r.exam_passed,r.best_subjects,r.last_marks,r.semester FROM AdmissionForm r WHERE r.is_del =0 AND r.email=:email AND r.name LIKE :name";
        $query = $this->getEntityManager()->createQuery($dql)
        ->setParameter('name', '%' . $name . '%')
            // ->setParameter('semester', $semester)
            ->setParameter('email', $email);
        $result = $query->getResult();
        return $result;
    }
   
}
