Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 81 |
CRAP | |
0.00% |
0 / 1434 |
| ExamReportService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 81 |
81510.00 | |
0.00% |
0 / 1434 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getDayWiseAttendanceReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 48 |
|||
| getSubjectListWithExams | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getFailedStudentAfterModeration | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getMarkListApplicationDates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| saveMarkListApplicationDates | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getConsolidatedPublishDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| saveMarkListApplicationAppliedStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getMarkListApplicationAppliedStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getMarkListApplicationAppliedStudentBybatchId | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 27 |
|||
| saveMarkListIssueDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| saveRegularMarklistApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| saveTranscriptCertificate | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 20 |
|||
| saveTranscriptApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getTranscriptApplicationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getApplicationPaymentDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 17 |
|||
| savePaymentDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updatePaymentDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| updateTranscriptApplicationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| updateTranscriptApplicationPaidStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteTranscriptApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteTranscriptCertificates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTranscriptStudentUploadedCertificate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTranscriptApplicationByRequest | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 38 |
|||
| updateStaffInTranscriptApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateApproveStaffInTranscript | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getDistinctBatchByappln | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 33 |
|||
| getAllTranscriptApplicationByRequest | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 41 |
|||
| getTranscriptNotAppliedByRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 28 |
|||
| saveStudentTranscriptApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| saveAuditCourseCategory | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getAuditCourseCategory | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| updateAuditCourseCategory | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteAuditCourseCategory | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| saveAuditCourseCategoryHead | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getAuditCourseCategoryHead | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteAuditCourseCategoryHead | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAuditCourseCategoriesByCourseType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| saveStudentAuditCourseDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 18 |
|||
| getStudentAuditCourseDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getExamRegisteredStudentDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 29 |
|||
| saveMainBookNumber | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getMainBookNumber | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getMainBookNumberDetail | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| saveOmrSheetHallNumber | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 25 |
|||
| getOmrSheetHallNumber | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getDistinctExamRegistrationFromOmrHallNumber | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getExamDateFromOmrHallNumber | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getExamDetailsOfOmrByRequest | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 38 |
|||
| getHallWiseStudentsForOmr | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getExamRegisteredStudentsByBatchAndSubjectId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 30 |
|||
| deleteStudentCertificates | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 33 |
|||
| getStudentCertificatesUploadStatus | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 27 |
|||
| getStudentUploadedCertificates | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 34 |
|||
| verifyStudentUploadedReport | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 17 |
|||
| getCaMarkImpMakeUpTestApplications | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 20 |
|||
| getStudentApplicationAppliedDetails | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 63 |
|||
| saveAppliedDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 23 |
|||
| saveApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getApplicationPaymentDetailsByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| saveApplicationPaymentDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateStudentApplicationDetails | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| verifyStudentApplicationPayment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| deleteCertificateApplication | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| saveCertificateApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentApplicationDetails | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| updateCertificateApplicationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getCertificateApplicationByRequest | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 38 |
|||
| updateStaffInCertificateApplication | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| updateApproveStaffInCertificateApplication | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| deleteApplicationPaymentDetailsByRequest | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getStudentApplicationDetailsList | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| getApplicationPaymentDetailsProps | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 21 |
|||
| savePaymentDetailsProps | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 10 |
|||
| updateCertificateApplicationDetailsProps | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getConsolidatedPublishDates | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| saveStudentLowPassGradeApplication | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getStudentLowPassGradeApplication | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 59 |
|||
| updateStudentLowPassGradeApplication | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 33 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\base\exception\CoreException; | |
| use com\linways\base\util\RequestUtil; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\ExamReportServiceMapper; | |
| use com\linways\core\ams\professional\dto\Subject; | |
| use com\linways\core\ams\professional\request\api\GetAllDepartmentsRequest; | |
| use com\linways\core\ams\professional\request\ExamReportRequest; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| class ExamReportService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = ExamReportServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Prevent any copy of this object | |
| // /Condition 4 - Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self(); | |
| return self::$_instance; | |
| } | |
| /** | |
| * Get consolidated exm reports | |
| * @param ExamReportRequest | $request | |
| * @throws ProfessionalException | |
| * @return studentDetails | |
| */ | |
| public function getDayWiseAttendanceReport(ExamReportRequest $request){ | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT DISTINCT | |
| sa.studentID, | |
| sa.studentName, | |
| sa.rollNo, | |
| sa.studentAccount, | |
| sa.regNo, | |
| ba.batchName, | |
| ba.batchID, | |
| sub.subjectID, | |
| sub.subjectDesc, | |
| sub.subjectName, | |
| sbs.batchID, | |
| sbs.semID, | |
| sbs.staffID, | |
| staff.staffName, | |
| e.examName, | |
| e.examTotalMarks, | |
| sm.examID, sm.marksObtained, sm.percentage, sm.examTypeID | |
| FROM | |
| studentaccount sa | |
| INNER JOIN | |
| batches ba ON ba.batchID = sa.batchID | |
| INNER JOIN | |
| semesters sem ON sem.semID = ba.semID | |
| INNER JOIN | |
| exam e ON sa.batchID = e.batchID and ba.semID = e.semID | |
| INNER JOIN | |
| student_marks sm ON e.examID = sm.examID and sm.studentID = sa.studentID | |
| INNER JOIN | |
| sbs_relation sbs ON sbs.subjectID = e.subjectID | |
| AND sbs.batchID = e.batchID | |
| AND sbs.semID = e.semID | |
| INNER JOIN | |
| subjects sub ON sub.subjectID = e.subjectID | |
| INNER JOIN | |
| staffaccounts staff ON staff.staffID = sbs.staffID | |
| WHERE | |
| sa.batchID IN ($request->batchId) AND ba.semID = $request->semId AND e.examTypeID = $request->examId | |
| ORDER BY sa.rollNo"; | |
| try{ | |
| $studentDetails = $this->executeQueryForList($sql,$this->mapper[ExamReportServiceMapper::GET_CONSOLIDATED_EXAM_REPORT]); | |
| return $studentDetails; | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get exams subject-wise by batchIds | |
| * @param ExamReportRequest | $request | |
| * @throws ProfessionalException | |
| * @return subjectDetails | |
| */ | |
| public function getSubjectListWithExams(ExamReportRequest $request){ | |
| $request = $this->realEscapeObject($request); | |
| $sql ="SELECT DISTINCT(e.examID),e.examName,s.subjectID, s.subjectName,s.subjectDesc,subbatchID,e.examTotalMarks FROM exam e,subjects s WHERE e.semID=$request->semId AND e.batchID IN ($request->batchId) AND e.examTypeID=$request->examId AND e.subjectID=s.subjectID ORDER BY s.subjectID"; | |
| try{ | |
| $subjectDetails = $this->executeQueryForList($sql,$this->mapper[ExamReportServiceMapper::GET_EXAMS_SUBJECT_WISE]); | |
| return $subjectDetails; | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check student failed after moderation | |
| * @param studentTotalMarks | |
| * @throws ProfessionalException | |
| * @return status | |
| */ | |
| public function getFailedStudentAfterModeration($schemeID,$studentMarkModerated) | |
| { | |
| $schemeID = $this->realEscapeString($schemeID); | |
| $studentMarkModerated = $this->realEscapeString($studentMarkModerated); | |
| $sql ="SELECT gradePointID from exam_gradepoints | |
| where schemeID = $schemeID and failStatus = 1 and percentFrom <= $studentMarkModerated and percentTo >= $studentMarkModerated"; | |
| try { | |
| $status = $this->executeQueryForObject($sql); | |
| return $status; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get marklist publish date | |
| * @param $batchId | |
| * @param $startDate | |
| * @param $endDate | |
| */ | |
| public function getMarkListApplicationDates($batchId, $markListType) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $markListType = $this->realEscapeString($markListType); | |
| $sql = "SELECT startDate, endDate FROM ec_initiate_marklist_application WHERE batchID = $batchId and marklist_type = '$markListType'"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * saving marklist publish date | |
| * @param $batchId | |
| * @param $startDate | |
| * @param $endDate | |
| */ | |
| public function saveMarkListApplicationDates($batchId, $startDate, $endDate, $markListType) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $startDate = $this->realEscapeString($startDate); | |
| $endDate = $this->realEscapeString($endDate); | |
| $markListType = $this->realEscapeString($markListType); | |
| $result = $this->getMarkListApplicationDates($batchId, $markListType); | |
| if($result){ | |
| $sql = "UPDATE ec_initiate_marklist_application set startDate = '$startDate', endDate = '$endDate' WHERE batchID = $batchId and marklist_type = '$markListType'"; | |
| } | |
| else{ | |
| $sql = "INSERT INTO ec_initiate_marklist_application(batchID, startDate, endDate, marklist_type) VALUES ($batchId, '$startDate', '$endDate', '$markListType')"; | |
| } | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get consolidated publishDetails | |
| * @param $batchId | |
| */ | |
| public function getConsolidatedPublishDetails($batchId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $sql = "SELECT isPublish FROM consolidated_marklist_publish WHERE batchID = $batchId "; | |
| try { | |
| $result = $this->executeQueryForObject($sql)->isPublish; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * saving marklist application student details | |
| * @param $batchId | |
| * @param $isApproved | |
| * @param $endDate | |
| */ | |
| public function saveMarkListApplicationAppliedStudent($studentId, $isApproved, $markListType) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $isApproved = $this->realEscapeString($isApproved); | |
| $markListType = $this->realEscapeString($markListType); | |
| $date = date('Y-m-d'); | |
| $sql = "INSERT INTO ec_marklist_application_applied_students(studentID, isVerified, applied_date, marklist_type) VALUES ($studentId, '$isApproved', '$date', '$markListType')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get consolidated publishDetails | |
| * @param $studentId | |
| */ | |
| public function getMarkListApplicationAppliedStudent($studentId, $markListType) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $markListType = $this->realEscapeString($markListType); | |
| $sql = "SELECT isVerified FROM ec_marklist_application_applied_students WHERE studentID = $studentId and marklist_type = '$markListType'"; | |
| try { | |
| $result = $this->executeQueryForObject($sql)->isVerified; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get consolidated publishDetails | |
| * @param $batchId | |
| */ | |
| public function getMarkListApplicationAppliedStudentBybatchId($batchId, $startDate, $endDate, $markListType) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $endDate = $this->realEscapeString($endDate); | |
| $startDate = $this->realEscapeString($startDate); | |
| $markListType = $this->realEscapeString($markListType); | |
| if($markListType == "CONSOLIDATED"){ | |
| $condition = " AND empp.isVerified = 1"; | |
| } | |
| if($startDate && $endDate){ | |
| $condition .= " AND empp.applied_date BETWEEN '$startDate' and '$endDate'"; | |
| } | |
| $sql = "SELECT | |
| sa.studentID, sa.regNo, sa.studentName,empp.applied_date, empp.issued, empp.semID, s.semName | |
| FROM | |
| ec_marklist_application_applied_students empp | |
| INNER JOIN | |
| studentaccount sa ON (empp.studentID = sa.studentID) | |
| LEFT JOIN | |
| semesters s ON (empp.semID = s.semID) | |
| WHERE sa.batchID = $batchId and empp.marklist_type = '$markListType' $condition ORDER BY empp.applied_date DESC"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * saving marklist issued details | |
| */ | |
| public function saveMarkListIssueDetails($student, $markListType) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $markListType = $this->realEscapeString($markListType); | |
| if($markListType == "REGULAR"){ | |
| $condition = " AND semID = $student->semID"; | |
| } | |
| $sql = "UPDATE ec_marklist_application_applied_students SET issued = '$student->issued' WHERE studentID = $student->studentID AND marklist_type = '$markListType' $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * saving marklist application student details | |
| * @param $studentId | |
| * @param $isApproved | |
| * @param $endDate | |
| */ | |
| public function saveRegularMarklistApplication($studentId, $semId, $markListType) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $semId = $this->realEscapeString($semId); | |
| $markListType = $this->realEscapeString($markListType); | |
| $date = date('Y-m-d'); | |
| $sql = "INSERT INTO ec_marklist_application_applied_students(studentID, semID, applied_date, marklist_type) VALUES ($studentId, '$semId', '$date', '$markListType')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save transcript application certificated | |
| * @param $request | |
| */ | |
| public function saveTranscriptCertificate($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->examRegId = $request->examRegId ? $request->examRegId : ""; | |
| $examRegField = $examRegIdValue = $propertiesField = $propertiesValue = ""; | |
| $request->properties = $request->properties ? json_encode($request->properties) : ""; | |
| if($request->examRegId){ | |
| $examRegField = " ,examRegId"; | |
| $examRegIdValue = " , $request->examRegId"; | |
| } | |
| if($request->properties){ | |
| $propertiesField = " ,properties"; | |
| $propertiesValue = " , '$request->properties'"; | |
| } | |
| $sql = "INSERT INTO ec_certificate_upload(studentID, resourseId, certificate_type, certificate_order, created_by $examRegField $propertiesField) VALUES ('$request->studentId', '$request->resourseId', '$request->certificate_type', '$request->certificate_order', '$request->updatedBy' $examRegIdValue $propertiesValue)"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save transcript application certificated | |
| * @param $request | |
| */ | |
| public function saveTranscriptApplication($request) | |
| { | |
| $address = addslashes(json_encode($request->address, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $properties = addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $request = $this->realEscapeObject($request); | |
| $date = date('Y-m-d'); | |
| $sql = "INSERT INTO ec_transcript_applied_student_details(studentID, amount, paid, purpose, address, isResultPending, remarks,properties) VALUES ('$request->studentId', '$request->amount', '$request->paid', '$request->purpose','$address', '$request->resultPending', '$request->remarks','$properties')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get transcript application details | |
| * @param $studentId | |
| */ | |
| public function getTranscriptApplicationDetails($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT | |
| amount, paid,purpose,address,remarks,dateOfPay,isResultPending,status,properties | |
| FROM | |
| ec_transcript_applied_student_details | |
| WHERE | |
| studentID = $studentId"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get transcript application payment details | |
| * @param $studentId | |
| */ | |
| public function getApplicationPaymentDetails($studentId, $status, $type) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $status = $this->realEscapeString($status); | |
| $type = $this->realEscapeString($type); | |
| $sql = "SELECT | |
| txnID, amount, transactionDate, payment_gateway_txn_id | |
| FROM | |
| ec_online_payment | |
| WHERE | |
| studentID = $studentId AND status = '$status' AND type = '$type'"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save transcript application payment details | |
| * @param $request | |
| */ | |
| public function savePaymentDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $sql = "INSERT INTO ec_online_payment (studentID,txnID, amount, status, type) VALUES ('$request->studentId', '$request->txnID', '$request->amount', '$request->status', '$request->type')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save transcript application payment details | |
| * @param $request | |
| */ | |
| public function updatePaymentDetails($status, $paymentGatewayTxnId, $txnID) | |
| { | |
| $status = $this->realEscapeString($status); | |
| $paymentGatewayTxnId = $this->realEscapeString($paymentGatewayTxnId); | |
| $txnID = $this->realEscapeString($txnID); | |
| $date = date("Y-m-d H:i:s"); | |
| $sql = "UPDATE ec_online_payment SET status = '$status', transactionDate = '$date', payment_gateway_txn_id = '$paymentGatewayTxnId' WHERE txnID = '$txnID'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save transcript applied student details | |
| * @param $request | |
| */ | |
| public function updateTranscriptApplicationDetails($studentId, $paid, $status, $payment_method) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $paid = $this->realEscapeString($paid); | |
| $payment_method = $this->realEscapeString($payment_method); | |
| $status = $this->realEscapeString($status); | |
| $date = date("Y-m-d"); | |
| $sql = "UPDATE ec_transcript_applied_student_details SET paid = '$paid', payment_method = '$payment_method', dateofPay = '$date', status = '$status' WHERE studentID = '$studentId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save transcript applied student details | |
| * @param $request | |
| */ | |
| public function updateTranscriptApplicationPaidStatus($studentId, $paid, $status, $payment_method) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $paid = $this->realEscapeString($paid); | |
| $sql = "UPDATE ec_transcript_applied_student_details SET paid = '$paid', payment_method = '$payment_method', status = '$status' WHERE studentID = '$studentId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete transcript applied student details | |
| * @param $request | |
| */ | |
| public function deleteTranscriptApplication($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "DELETE FROM ec_transcript_applied_student_details WHERE studentID = '$studentId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete transcript applied student details | |
| * @param $request | |
| */ | |
| public function deleteTranscriptCertificates($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "DELETE FROM ec_certificate_upload WHERE studentID = '$studentId'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get certificates for student to upload | |
| * @param int $studentId | |
| */ | |
| public function getTranscriptStudentUploadedCertificate( $studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql ="SELECT eccu.certificate_type, eccu.certificate_order, lr.path, lr.storage_object,eccu.resourseId from ec_certificate_upload eccu INNER JOIN lin_resource lr ON lr.id=eccu.resourseId WHERE eccu.studentID='$studentId' ORDER BY eccu.certificate_order"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get transcript application details by request | |
| * @param $studentId | |
| */ | |
| public function getTranscriptApplicationByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->courseTypeId){ | |
| $condition .= " AND bt.courseTypeID = $request->courseTypeId"; | |
| } | |
| if($request->batchStartYear){ | |
| $condition .= " AND bt.batchStartYear = $request->batchStartYear"; | |
| } | |
| if($request->batchId){ | |
| $condition .= " AND bt.batchID = $request->batchId"; | |
| } | |
| if($request->staffId){ | |
| $condition .= " AND etasd.staffID = $request->staffId"; | |
| } | |
| $orderBy = " etasd.dateOfPay DESC"; | |
| if($request->orderByAsc){ | |
| $orderBy = " etasd.dateOfPay ASC"; | |
| } | |
| $sql = "SELECT | |
| sa.regNo, sa.studentID, sa.studentName, bt.batchName, bt.batchID, etasd.amount, etasd.paid, etasd.purpose, etasd.address, etasd.remarks, etasd.dateOfPay, etasd.isResultPending,etasd.staffID as staffId, etasd.verified | |
| FROM | |
| ec_transcript_applied_student_details etasd | |
| INNER JOIN | |
| studentaccount sa ON (etasd.studentID = sa.studentID) | |
| INNER JOIN | |
| batches bt ON (bt.batchID = sa.batchID) | |
| WHERE | |
| etasd.paid = 1 $condition | |
| ORDER BY $orderBy"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| $result = CommonUtil::convertObjectToUTF8Format($result); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * update transcript applied student details | |
| * @param $request | |
| */ | |
| public function updateStaffInTranscriptApplication($student, $status) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $sql = "UPDATE ec_transcript_applied_student_details SET staffID = '$student->staffId', status = '$status' WHERE studentID = '$student->studentID'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update transcript applied student details | |
| * @param $request | |
| */ | |
| public function updateApproveStaffInTranscript($student, $flag, $status) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $flag = $this->realEscapeString($flag); | |
| $status = $this->realEscapeString($status); | |
| $sql = "UPDATE ec_transcript_applied_student_details SET verified = '$flag', status = '$status' WHERE studentID = '$student->studentID'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get distinct batches | |
| * @param $courseTypeId | |
| */ | |
| public function getDistinctBatchByappln($courseTypeId, $batchStartYear, $startDate, $endDate, $markListType) | |
| { | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $endDate = $this->realEscapeString($endDate); | |
| $startDate = $this->realEscapeString($startDate); | |
| $batchStartYear = $this->realEscapeString($batchStartYear); | |
| $markListType = $this->realEscapeString($markListType); | |
| if($markListType == "CONSOLIDATED"){ | |
| $condition = " AND empp.isVerified = 1"; | |
| } | |
| if($startDate){ | |
| $condition .= " AND empp.applied_date >= '$startDate'"; | |
| } | |
| if($endDate){ | |
| $condition .= " AND empp.applied_date <= '$endDate'"; | |
| } | |
| $sql = "SELECT | |
| DISTINCT bt.batchID as batchId, | |
| bt.batchName , | |
| bt.batchDesc as description | |
| FROM | |
| ec_marklist_application_applied_students empp | |
| INNER JOIN | |
| studentaccount sa ON (empp.studentID = sa.studentID) | |
| INNER JOIN | |
| batches bt ON (sa.batchID = bt.batchID) | |
| WHERE bt.courseTypeID = $courseTypeId and bt.batchStartYear = $batchStartYear and empp.marklist_type = '$markListType' $condition GROUP By bt.batchID ORDER BY empp.applied_date DESC"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get all transcript application details by request | |
| * @param $studentId | |
| */ | |
| public function getAllTranscriptApplicationByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->courseTypeId){ | |
| $condition .= " AND bt.courseTypeID = $request->courseTypeId"; | |
| } | |
| if($request->batchStartYear){ | |
| $condition .= " AND bt.batchStartYear = $request->batchStartYear"; | |
| } | |
| if($request->batchId){ | |
| $condition .= " AND bt.batchID = $request->batchId"; | |
| } | |
| if($request->staffId){ | |
| $condition .= " AND etasd.staffID = $request->staffId"; | |
| } | |
| if($request->paid){ | |
| $condition .= " AND etasd.paid = $request->paid"; | |
| } | |
| $orderBy = " etasd.dateOfPay DESC"; | |
| if($request->orderByAsc){ | |
| $orderBy = " etasd.dateOfPay ASC"; | |
| } | |
| $sql = "SELECT | |
| sa.regNo, sa.studentID, sa.studentName, bt.batchName, bt.batchID, etasd.amount, etasd.paid, etasd.purpose, etasd.address, etasd.remarks, etasd.dateOfPay, etasd.isResultPending,etasd.staffID as staffId, etasd.verified, etasd.payment_method | |
| FROM | |
| ec_transcript_applied_student_details etasd | |
| INNER JOIN | |
| studentaccount sa ON (etasd.studentID = sa.studentID) | |
| INNER JOIN | |
| batches bt ON (bt.batchID = sa.batchID) | |
| WHERE | |
| 1 = 1 $condition | |
| ORDER BY $orderBy"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| $result = CommonUtil::convertObjectToUTF8Format($result); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get transcript not applied students details by request | |
| * @param $studentId | |
| */ | |
| public function getTranscriptNotAppliedByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->courseTypeId){ | |
| $condition .= " AND b.courseTypeID = $request->courseTypeId"; | |
| } | |
| if($request->batchStartYear){ | |
| $condition .= " AND b.batchStartYear = $request->batchStartYear"; | |
| } | |
| if($request->batchId){ | |
| $condition .= " AND b.batchID = $request->batchId"; | |
| } | |
| $sql = "SELECT | |
| sa.regNo, sa.studentID, sa.studentName, b.batchName, b.batchID | |
| FROM | |
| studentaccount sa | |
| INNER JOIN | |
| batches b ON(b.batchID = sa.batchID ) | |
| WHERE 1 = 1 $condition | |
| And sa.studentID Not IN (SELECT studentID FROM ec_transcript_applied_student_details ectas) | |
| ORDER BY sa.regNo Asc"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save transcript application | |
| * @param $request | |
| */ | |
| public function saveStudentTranscriptApplication($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $date = date('Y-m-d'); | |
| $sql = "INSERT INTO ec_transcript_applied_student_details(studentID, amount, paid, dateofPay, payment_method, status) VALUES ('$request->studentId', '$request->amount', '$request->paid', '$date','$request->paymentMethod', '$request->status')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save audit course category | |
| * @param $categoryName | |
| * @param $courseTypeId | |
| * @param $priority | |
| */ | |
| public function saveAuditCourseCategory($categoryName, $courseTypeId, $priority) | |
| { | |
| $categoryName = $this->realEscapeString($categoryName); | |
| $priority = $this->realEscapeString($priority); | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $staffId = $_SESSION['adminID']; | |
| $sql = "INSERT INTO ec_audit_course_categories (categoryName,courseTypeID,priority,created_by) VALUES ('$categoryName', '$courseTypeId', '$priority','$staffId')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get audit course category | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getAuditCourseCategory($priority = false) | |
| { | |
| if($priority){ | |
| $condition = "ORDER BY priority ASC"; | |
| } | |
| else{ | |
| $condition = "ORDER BY created_date DESC"; | |
| } | |
| $sql = "SELECT eacc.id, eacc.categoryName, eacc.courseTypeID, ct.typeName, eacc.priority FROM ec_audit_course_categories eacc INNER JOIN course_type ct ON (ct.courseTypeID = eacc.courseTypeID) $condition"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update audit course category | |
| * @param $category | |
| */ | |
| public function updateAuditCourseCategory($category) | |
| { | |
| $category = $this->realEscapeObject($category); | |
| $staffId = $_SESSION['adminID']; | |
| $sql = "UPDATE ec_audit_course_categories set priority = '$category->priority', updated_by ='$staffId' WHERE id = $category->id"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete audit course category | |
| * @param $category | |
| */ | |
| public function deleteAuditCourseCategory($category) | |
| { | |
| $category = $this->realEscapeObject($category); | |
| $sql = "DELETE FROM ec_audit_course_categories WHERE id = $category->id"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save audit course category head | |
| * @param $categoryName | |
| * @param $courseTypeId | |
| * @param $priority | |
| */ | |
| public function saveAuditCourseCategoryHead($categoryId,$headName, $isCredit, $priority) | |
| { | |
| $headName = $this->realEscapeString($headName); | |
| $priority = $this->realEscapeString($priority); | |
| $isCredit = $this->realEscapeString($isCredit); | |
| $staffId = $_SESSION['adminID']; | |
| $sql = "INSERT INTO ec_audit_course_sub_categories (categoryID,headName,isCredit,priority,created_by) VALUES ($categoryId, '$headName', '$isCredit', '$priority','$staffId')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get audit course categoryHead | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getAuditCourseCategoryHead($categoryId) | |
| { | |
| $categoryId = $this->realEscapeString($categoryId); | |
| $sql = "SELECT id, headName, isCredit, priority FROM ec_audit_course_sub_categories WHERE categoryID = $categoryId ORDER BY priority ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete audit course category head | |
| * @param $category | |
| */ | |
| public function deleteAuditCourseCategoryHead($category) | |
| { | |
| $category = $this->realEscapeObject($category); | |
| $sql = "DELETE FROM ec_audit_course_sub_categories WHERE id = $category->id"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get audit course category by courseType | |
| * @param $category | |
| */ | |
| public function getAuditCourseCategoriesByCourseType($courseTypeId) | |
| { | |
| $courseTypeId = $this->realEscapeString($courseTypeId); | |
| $sql = "SELECT eacsc.categoryID, eacc.categoryName, eacc.priority as categoryPriority, eacsc.id as headID, eacsc.headName, eacsc.priority as headPriority, eacsc.isCredit FROM ec_audit_course_sub_categories eacsc INNER JOIN ec_audit_course_categories eacc ON (eacsc.categoryID = eacc.id) WHERE eacc.courseTypeID = $courseTypeId"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save student audit certificated | |
| * @param $request | |
| */ | |
| public function saveStudentAuditCourseDetails($request) | |
| { | |
| $applied_details = addslashes(json_encode($request->applied_details, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $request = $this->realEscapeObject($request); | |
| $date = date('Y-m-d'); | |
| $staffId = $_SESSION['adminID']; | |
| $result = $this->getStudentAuditCourseDetails($request->studentId); | |
| if($result){ | |
| $sql = "UPDATE ec_audit_course_applied_students SET applied_details = '$applied_details' WHERE studentID = $request->studentId"; | |
| } | |
| else{ | |
| $sql = "INSERT INTO ec_audit_course_applied_students(studentID, applied_details, applied_date, created_by) VALUES ('$request->studentId', '$applied_details', '$date','$staffId')"; | |
| } | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get student audit certificated | |
| * @return mixed | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentAuditCourseDetails($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT id, applied_details, applied_date FROM ec_audit_course_applied_students WHERE studentID = $studentId "; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get exam registered student details by regId and sujectId | |
| */ | |
| public function getExamRegisteredStudentDetails($isSupply,$examRegId,$subjectId) | |
| { | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $studentDetails = []; | |
| if (!$isSupply) { | |
| $sql = "SELECT DISTINCT sa.studentID as studentId,sa.regNo,sa.studentName,sa.batchID,e.examID, e.semId, b.batchName FROM studentaccount sa | |
| INNER JOIN exam_reg_studentsubject erss ON (sa.studentID = erss.studentID) | |
| INNER JOIN exam e ON (e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND sa.batchID = e.batchID) | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.studentID = sa.studentID AND ersc.examregID ='$examRegId' | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| WHERE erss.examregID = '$examRegId' AND e.subjectID='$subjectId' AND ersc.paid=1 order by b.batchDisplayOrder, sa.regNo ASC"; | |
| } else { | |
| $sql = "SELECT DISTINCT sa.studentID as studentId,sa.regNo,sa.studentName,sa.batchID,es.examID, es.semId, b.batchName | |
| FROM studentaccount sa | |
| INNER JOIN exam_supplementary_student_subjects esss ON (sa.studentID = esss.studentID) | |
| INNER JOIN exam e ON (e.examID = esss.examID AND sa.batchID = e.batchID) | |
| INNER JOIN exam es ON (e.subjectID = es.subjectID AND es.supply_examreg_id = esss.exam_supplementary_id AND es.batchID = e.batchID) | |
| INNER JOIN exam_supplementary_student_details essd ON essd.studentID = sa.studentID AND essd.exam_supplementary_id ='$examRegId' | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| WHERE esss.exam_supplementary_id = '$examRegId' AND es.subjectID='$subjectId' AND essd.paid=1 order by b.batchDisplayOrder, sa.regNo ASC"; | |
| } | |
| try { | |
| $studentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * save main book number | |
| * @param $request | |
| */ | |
| public function saveMainBookNumber($studentList) | |
| { | |
| $studentList = $this->realEscapeObject($studentList); | |
| $date = date('Y-m-d'); | |
| $staffId = $_SESSION['adminID']; | |
| try { | |
| foreach( $studentList as $student){ | |
| $student = (object)$student; | |
| $sql = "INSERT INTO ec_main_book_number(studentID, examID, bookNo, created_by) VALUES ('$student->studentId','$student->examID','$student->bookNo', $staffId)"; | |
| $this->executeQuery($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get main book number | |
| * @param $request | |
| */ | |
| public function getMainBookNumber($studentId, $examId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $examId = $this->realEscapeString($examId); | |
| try { | |
| $sql = "SELECT bookNo from ec_main_book_number WHERE studentID = $studentId AND examID =$examId"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get main book number | |
| * @param $request | |
| */ | |
| public function getMainBookNumberDetail($bookNo) | |
| { | |
| $bookNo = $this->realEscapeString($bookNo); | |
| try { | |
| $sql = "SELECT studentID from ec_main_book_number WHERE bookNo = '$bookNo'"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save omr sheet hall number | |
| * @param $request | |
| */ | |
| public function saveOmrSheetHallNumber($student) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $date = date('Y-m-d'); | |
| $staffId = $_SESSION['adminID']; | |
| $examRegId = "NULL"; | |
| $supplyRegId = "NULL"; | |
| if($student->isSupply){ | |
| $supplyRegId = $student->examRegId; | |
| } | |
| else{ | |
| $examRegId = $student->examRegId; | |
| } | |
| try { | |
| $result = $this->getOmrSheetHallNumber($student); | |
| if($result){ | |
| $sql = "UPDATE ec_omr_sheet_hall_number SET hallNo = '$student->hallNo', updated_by = '$staffId' WHERE studentID = $student->studentId AND examID = $student->examID"; | |
| } | |
| else{ | |
| $sql = "INSERT INTO ec_omr_sheet_hall_number(studentID, examRegID, supplyRegID, examID, hallNo, created_by) VALUES ('$student->studentId', $examRegId, $supplyRegId, '$student->examID','$student->hallNo', $staffId)"; | |
| } | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get omr sheet hall number | |
| * @param $request | |
| */ | |
| public function getOmrSheetHallNumber($student) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| try { | |
| $sql = "SELECT hallNo from ec_omr_sheet_hall_number WHERE studentID = $student->studentId AND examID = $student->examID"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get distinct examregistration from omr sheet hall number | |
| * @param $isSupply | |
| */ | |
| public function getDistinctExamRegistrationFromOmrHallNumber($isSupply) | |
| { | |
| $isSupply = $this->realEscapeString($isSupply); | |
| try { | |
| if ($isSupply) { | |
| $sql = "SELECT DISTINCT es.id, es.supplyDesc as name, es.semID AS semId FROM exam_supplementary es INNER JOIN ec_omr_sheet_hall_number eo ON (es.id = eo.supplyRegID) ORDER BY es.id DESC"; | |
| } | |
| else{ | |
| $sql = "SELECT DISTINCT er.examregID as id, er.examregName as name FROM exam_registration er INNER JOIN ec_omr_sheet_hall_number eo ON (er.examregID = eo.examRegID) ORDER BY er.examregID DESC"; | |
| } | |
| $examRegistration = $this->executeQueryForList($sql); | |
| return $examRegistration; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get exam date from omr sheet hall number | |
| * @param $isSupply | |
| */ | |
| public function getExamDateFromOmrHallNumber( $examRegId, $isSupply) | |
| { | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $examRegId = $this->realEscapeString($examRegId); | |
| try { | |
| if ($isSupply) { | |
| $sql = "SELECT ex.examID, ex.examDate, ex.examStartTime, ex.examEndTime FROM exam ex INNER JOIN ec_omr_sheet_hall_number eo ON (ex.supply_examreg_id = eo.supplyRegID AND eo.examID = ex.examID) WHERE ex.supply_examreg_id = $examRegId ORDER BY ex.examDate DESC"; | |
| } | |
| else{ | |
| $sql = "SELECT ex.examID, ex.examDate, ex.examStartTime, ex.examEndTime FROM exam ex INNER JOIN ec_omr_sheet_hall_number eo ON (ex.examregID = eo.examRegID AND eo.examID = ex.examID) WHERE ex.examregID = $examRegId ORDER BY ex.examDate DESC"; | |
| } | |
| $examDates = $this->executeQueryForList($sql); | |
| return $examDates; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get exam details - omr sheet | |
| * @param $request | |
| */ | |
| public function getExamDetailsOfOmrByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $date = date('Y-m-d'); | |
| $staffId = $_SESSION['adminID']; | |
| $condition = ""; | |
| if($request->isSupply){ | |
| $condition .= " AND ex.supply_examreg_id = $request->examRegId"; | |
| } | |
| else{ | |
| $condition .= " AND ex.examRegID = $request->examRegId"; | |
| } | |
| if($request->examDate){ | |
| $condition .= " AND ex.examDate = '$request->examDate'"; | |
| } | |
| if($request->roomNo){ | |
| $condition .= " AND eo.hallNo = '$request->roomNo'"; | |
| } | |
| try { | |
| $sql = "SELECT ex.examID, ex.examDate, ex.examStartTime, ex.examEndTime, eo.hallNo, s.subjectID, s.subjectName, s.subjectDesc FROM exam ex INNER JOIN ec_omr_sheet_hall_number eo ON (eo.examID = ex.examID) INNER JOIN subjects s ON (s.subjectID = ex.subjectID) WHERE 1 =1 $condition ORDER BY ex.examDate DESC "; | |
| $exams = $this->executeQueryForList($sql); | |
| $examList = []; | |
| foreach($exams as $key => $exam){ | |
| if($request->session == "FN"){ | |
| if(strtotime($exam->examStartTime) >= strtotime("9:00 AM") &&strtotime($exam->examEndTime) <= strtotime("12:30 PM")){ | |
| $examList[] = $exam; | |
| } | |
| } | |
| else if($request->session == "AN"){ | |
| if(strtotime($exam->examStartTime) >= strtotime("1:00 PM") &&strtotime($exam->examEndTime) <= strtotime("4:30 PM")){ | |
| $examList[] = $exam; | |
| } | |
| } | |
| } | |
| return $examList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get hall wise student - omr sheet | |
| * @param $examId | |
| */ | |
| public function getHallWiseStudentsForOmr($examId, $hallNo) | |
| { | |
| $examId = $this->realEscapeString($examId); | |
| $hallNo = $this->realEscapeString($hallNo); | |
| try { | |
| $sql = "SELECT sa.studentID, sa.regNo, sa.rollNo FROM ec_omr_sheet_hall_number eo INNER JOIN studentaccount sa ON (sa.studentID = eo.studentID) WHERE eo.examID = $examId AND eo.hallNo = '$hallNo' ORDER BY sa.regNo ASC"; | |
| $studentsList = $this->executeQueryForList($sql); | |
| return $studentsList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get exam registered student details by regId, batchId and subjectId | |
| */ | |
| public function getExamRegisteredStudentsByBatchAndSubjectId($isSupply,$examRegId, $batchId, $subjectId) | |
| { | |
| $isSupply = $this->realEscapeString($isSupply); | |
| $examRegId = $this->realEscapeString($examRegId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $subjectId = $this->realEscapeString($subjectId); | |
| $studentDetails = []; | |
| if (!$isSupply) { | |
| $sql = "SELECT DISTINCT sa.studentID as studentId,sa.regNo,sa.studentName,sa.batchID,e.examID, e.semId, b.batchName, e.examTotalMarks FROM studentaccount sa | |
| INNER JOIN exam_reg_studentsubject erss ON (sa.studentID = erss.studentID) | |
| INNER JOIN exam e ON (e.examregID = erss.examregID AND e.subjectID = erss.subjectID AND sa.batchID = e.batchID) | |
| INNER JOIN exam_reg_studentchallan ersc ON ersc.studentID = sa.studentID AND ersc.examregID ='$examRegId' | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| WHERE erss.examregID = '$examRegId' AND e.subjectID='$subjectId' AND e.batchID='$batchId' AND ersc.paid=1 order by sa.regNo ASC"; | |
| } else { | |
| $sql = "SELECT DISTINCT sa.studentID as studentId,sa.regNo,sa.studentName,sa.batchID,es.examID, es.semId, b.batchName, es.examTotalMarks | |
| FROM studentaccount sa | |
| INNER JOIN exam_supplementary_student_subjects esss ON (sa.studentID = esss.studentID) | |
| INNER JOIN exam e ON (e.examID = esss.examID AND sa.batchID = e.batchID) | |
| INNER JOIN exam es ON (e.subjectID = es.subjectID AND es.supply_examreg_id = esss.exam_supplementary_id AND es.batchID = e.batchID) | |
| INNER JOIN exam_supplementary_student_details essd ON essd.studentID = sa.studentID AND essd.exam_supplementary_id ='$examRegId' | |
| INNER JOIN batches b ON b.batchID = e.batchID | |
| WHERE esss.exam_supplementary_id = '$examRegId' AND es.subjectID='$subjectId' AND es.batchID = '$batchId' AND essd.paid=1 order by sa.regNo ASC"; | |
| } | |
| try { | |
| $studentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * delete student uploaded certificates | |
| * @param $request | |
| */ | |
| public function deleteStudentCertificates($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $properties = $request->properties; | |
| $condition=""; | |
| if($request->certificateType){ | |
| $condition .= " AND certificate_type = '$request->certificateType' "; | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND examRegId = '$request->examRegId' "; | |
| } | |
| if($properties->examType){ | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"examType\":\"$properties->examType\"}')"; | |
| } | |
| if ($properties->revaluationId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"revaluationId\":\"$properties->revaluationId\"}')"; | |
| } | |
| if ($properties->subjectId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"subjectId\":\"$properties->subjectId\"}')"; | |
| } | |
| if ($properties->applicationId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$properties->applicationId\"}')"; | |
| } | |
| if ($request->semAplnId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"semAplnId\":\"$request->semAplnId\"}')"; | |
| } | |
| $sql = "DELETE FROM ec_certificate_upload WHERE studentID = '$request->studentId' | |
| $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get student uploaded certificates | |
| * @param $request | |
| */ | |
| public function getStudentCertificatesUploadStatus($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->certificateType) { | |
| $condition .= " AND certificate_type = '$request->certificateType' "; | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND examRegId = '$request->examRegId' "; | |
| } | |
| if ($request->examType) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"examType\":\"$request->examType\"}')"; | |
| } | |
| if ($request->revaluationId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"revaluationId\":\"$request->revaluationId\"}')"; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"subjectId\":\"$request->subjectId\"}')"; | |
| } | |
| $sql = "SELECT resourseId,certificate_type,certificate_order FROM ec_certificate_upload WHERE studentID = '$request->studentId' | |
| $condition"; | |
| try { | |
| $uploads = $this->executeQueryForList($sql); | |
| return $uploads; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get certificates of students | |
| * @param int $request | |
| */ | |
| public function getStudentUploadedCertificates($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->certificateType) { | |
| $condition .= " AND eccu.certificate_type = '$request->certificateType' "; | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND eccu.examRegId = '$request->examRegId' "; | |
| } | |
| if ($request->examType) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"examType\":\"$request->examType\"}')"; | |
| } | |
| if ($request->revaluationId) { | |
| $condition .= " AND JSON_CONTAINS(eccu.properties, '{\"revaluationId\":\"$request->revaluationId\"}')"; | |
| } | |
| if ($request->subjectId) { | |
| $condition .= " AND JSON_CONTAINS(eccu.properties, '{\"subjectId\":\"$request->subjectId\"}')"; | |
| } | |
| if ($request->applicationId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| if ($request->semAplnId) { | |
| $condition .= " AND JSON_CONTAINS(properties, '{\"semAplnId\":\"$request->semAplnId\"}')"; | |
| } | |
| $sql = "SELECT eccu.certificate_type, eccu.certificate_order, lr.path, lr.storage_object,eccu.resourseId,eccu.status from ec_certificate_upload eccu INNER JOIN lin_resource lr ON lr.id=eccu.resourseId | |
| WHERE eccu.studentID='$request->studentId' | |
| $condition | |
| ORDER BY eccu.certificate_order"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *verify student uploaded reports | |
| * @param $request | |
| */ | |
| public function verifyStudentUploadedReport($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->certificateType) { | |
| $condition .= " AND certificate_type = '$request->certificateType' "; | |
| } | |
| if ($request->examRegId) { | |
| $condition .= " AND examRegId = '$request->examRegId' "; | |
| } | |
| $sql = "UPDATE ec_certificate_upload set status = '$request->status' WHERE studentID = '$request->studentId' | |
| $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Applications CA Mark imp /make up test | |
| */ | |
| public function getCaMarkImpMakeUpTestApplications($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| try { | |
| $sql = "SELECT id,registrationName as name,properties from student_applications"; | |
| $applications = $this->executeQueryForList($sql); | |
| if ($request->semId) { | |
| foreach($applications as $key => $application){ | |
| $application->semester = explode(",", json_decode($application->properties)->semester); | |
| $application->fromDate = json_decode($application->properties)->fromDate; | |
| $application->toDate = json_decode($application->properties)->toDate; | |
| if(!in_array($request->semId, $application->semester)){ | |
| unset($applications[$key]); | |
| } | |
| } | |
| } | |
| return $applications; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get student applied details | |
| */ | |
| public function getStudentApplicationAppliedDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = $subjectTableJoin = $subjectFields = $applicationTypeFields = ""; | |
| try { | |
| if($request->studentId){ | |
| $conditions .=" AND ecasd.studentID IN ($request->studentId)"; | |
| } | |
| if($request->paid){ | |
| $conditions .= " AND ecasd.paid IN ($request->paid)"; | |
| } | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(ecasd.properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| if($request->type){ | |
| $conditions .= " AND ecasd.type = '$request->type'"; | |
| } | |
| if ($request->paidStatus) { | |
| $conditions .= " AND ecasd.paid IN ($request->paid)"; | |
| } | |
| if ($request->courseTypeId) { | |
| $conditions .= " AND b.courseTypeID IN ($request->courseTypeId)"; | |
| } | |
| if ($request->batchStartYear) { | |
| $conditions .= " AND b.batchStartYear IN ($request->batchStartYear)"; | |
| } | |
| $batchColumns = ""; | |
| $batchTableJoin = ""; | |
| if(!$request->excludeBatchDetails){ | |
| $batchColumns = ",b.batchID,b.batchName"; | |
| $batchTableJoin = "INNER JOIN batches b ON b.batchID = sa.batchID"; | |
| } | |
| //for student applied reports | |
| if($request->subjectId AND $request->typeId){ | |
| if($request->applicationType){ | |
| $conditions .= " AND JSON_CONTAINS(ecasd.properties,'\"$request->subjectId\"', '$.$request->applicationType')"; | |
| }else{ | |
| foreach($request->applicationTypes as $applicationType){ | |
| $subjectConditions []= "JSON_CONTAINS(ecasd.properties,'\"$request->subjectId\"', '$.$applicationType')"; | |
| } | |
| $subjectConditions = implode(" OR ", $subjectConditions); | |
| $conditions .= " AND ($subjectConditions)"; | |
| } | |
| $subjectTableJoin ="INNER JOIN subjects s ON s.subjectID = $request->subjectId "; | |
| $subjectFields = " , s.subjectName,s.subjectDesc,s.syllabusName "; | |
| $applicationTypeFields = " ,IF(JSON_CONTAINS(ecasd.properties,'\"$request->subjectId\"', '$.caMarkImpSubjects'),\"1\",\"0\") AS isCaMark"; | |
| $applicationTypeFields .= " ,IF(JSON_CONTAINS(ecasd.properties,'\"$request->subjectId\"', '$.caMakeUpTestSubjects'),\"1\",\"0\") AS isMakeUp"; | |
| } | |
| //end for applied reports | |
| $sql = "SELECT ecasd.studentID as studentId,ecasd.amount,ecasd.paid,ecasd.properties,sa.regNo,sa.studentName $batchColumns $subjectFields | |
| $applicationTypeFields | |
| FROM ec_certificate_applied_student_details ecasd | |
| INNER JOIN studentaccount sa ON sa.studentID = ecasd.studentID | |
| $batchTableJoin | |
| $subjectTableJoin | |
| WHERE 1 = 1 $conditions"; | |
| if($request->getObject){ | |
| $applications = $this->executeQueryForObject($sql); | |
| }else{ | |
| $applications = $this->executeQueryForList($sql); | |
| } | |
| return $applications; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save student applied details | |
| */ | |
| public function saveAppliedDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = ""; | |
| try { | |
| if ($request->studentId) { | |
| $conditions .= " AND ecasd.studentID IN ($request->studentId)"; | |
| } | |
| if ($request->paid) { | |
| $conditions .= " AND ecasd.paid IN ($request->paid)"; | |
| } | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(ecasd.properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "SELECT ecasd.studentID as studentId,ecasd.amount,ecasd.paid,ecasd.properties | |
| FROM ec_certificate_applied_student_details ecasd | |
| INNER JOIN studentaccount sa ON sa.studentID = ecasd.studentID | |
| WHERE 1 = 1 $conditions"; | |
| $applications = $this->executeQueryForList($sql); | |
| return $applications; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save application | |
| * @param $request | |
| */ | |
| public function saveApplication($request) | |
| { | |
| $applicationProperties = addslashes(json_encode(json_decode($request->applicationProperties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME))); | |
| $request = $this->realEscapeObject($request); | |
| $sql = "INSERT INTO ec_certificate_applied_student_details(studentID, amount, paid,properties,type) VALUES ('$request->studentId', '$request->amount', '$request->paid','$applicationProperties','$request->type')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get application payment details | |
| * @param $request | |
| */ | |
| public function getApplicationPaymentDetailsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = ""; | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "SELECT | |
| txnID, amount, transactionDate, payment_gateway_txn_id | |
| FROM ec_online_payment | |
| WHERE | |
| studentID = $request->studentId AND status = '$request->status' AND type = '$request->type' | |
| $conditions"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save application payment details | |
| * @param $request | |
| */ | |
| public function saveApplicationPaymentDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $request->properties = addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $sql = "INSERT INTO ec_online_payment (studentID,txnID, amount, status, type,properties) VALUES ('$request->studentId', '$request->txnID', '$request->amount', '$request->status', '$request->type','$request->properties')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save applied student details | |
| * @param $request | |
| */ | |
| public function updateStudentApplicationDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $date = date("Y-m-d"); | |
| $conditions = ""; | |
| if ($request->type) { | |
| $conditions .= " AND type = '$request->type'"; | |
| } | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "UPDATE ec_certificate_applied_student_details SET paid = '$request->paid', payment_method = '$request->payment_method', dateofPay = '$date', status = '$request->status' | |
| WHERE studentID = '$request->studentId' $conditions"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *verify student application payment | |
| * @param $request | |
| */ | |
| public function verifyStudentApplicationPayment($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = ""; | |
| if ($request->type) { | |
| $conditions .= " AND type = '$request->type' "; | |
| } | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "UPDATE ec_certificate_applied_student_details set paid = '$request->paid' ,payment_method = '$request->paymentMethod' | |
| WHERE studentID = '$request->studentId' | |
| $conditions"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete certificate applied student details | |
| * @param $request | |
| */ | |
| public function deleteCertificateApplication($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = ""; | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "DELETE FROM ec_certificate_applied_student_details WHERE studentID = '$request->studentId' AND type='$request->type' $conditions"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * save certificate application certificated | |
| * @param $request | |
| */ | |
| public function saveCertificateApplication($request) | |
| { | |
| $address = addslashes(json_encode($request->address, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $applicationProperties = addslashes(json_encode($request->applicationProperties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)); | |
| $request = $this->realEscapeObject($request); | |
| $sql = "INSERT INTO ec_certificate_applied_student_details(studentID, amount, paid, address,remarks,properties,type) VALUES ('$request->studentId', '$request->amount', '$request->paid','$address', '$request->remarks','$applicationProperties','$request->type')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * get certificate application details | |
| * @param $studentId | |
| */ | |
| public function getStudentApplicationDetails($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$request->semAplnId'"; | |
| } | |
| $sql = "SELECT | |
| amount, paid,address,remarks,dateOfPay,properties,status,verified | |
| FROM | |
| ec_certificate_applied_student_details | |
| WHERE | |
| studentID = '$request->studentId' AND type ='$request->type' $condition"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save certificate applied student details | |
| * @param $request | |
| */ | |
| public function updateCertificateApplicationDetails($studentId, $paid, $status, $payment_method, $type) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $paid = $this->realEscapeString($paid); | |
| $payment_method = $this->realEscapeString($payment_method); | |
| $status = $this->realEscapeString($status); | |
| $date = date("Y-m-d"); | |
| $sql = "UPDATE ec_certificate_applied_student_details SET paid = '$paid', payment_method = '$payment_method', dateofPay = '$date', status = '$status' WHERE studentID = '$studentId' AND type ='$type'"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get certificate application details by request | |
| * @param $studentId | |
| */ | |
| public function getCertificateApplicationByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if ($request->courseTypeId) { | |
| $condition .= " AND bt.courseTypeID = $request->courseTypeId"; | |
| } | |
| if ($request->batchStartYear) { | |
| $condition .= " AND bt.batchStartYear = $request->batchStartYear"; | |
| } | |
| if ($request->batchId) { | |
| $condition .= " AND bt.batchID = $request->batchId"; | |
| } | |
| if ($request->staffId) { | |
| $condition .= " AND etasd.staffID = $request->staffId"; | |
| } | |
| if ($request->type) { | |
| $condition .= " AND etasd.type = '$request->type'"; | |
| } | |
| $orderBy = "etasd.dateofPay DESC"; | |
| $sql = "SELECT | |
| sa.regNo, sa.studentID, sa.studentName, bt.batchName, bt.batchID, etasd.amount, etasd.paid, etasd.properties, etasd.address, etasd.remarks, etasd.dateOfPay,etasd.staffID as staffId, etasd.verified,etasd.status,etasd.properties | |
| FROM | |
| ec_certificate_applied_student_details etasd | |
| INNER JOIN | |
| studentaccount sa ON (etasd.studentID = sa.studentID) | |
| INNER JOIN | |
| batches bt ON (bt.batchID = sa.batchID) | |
| WHERE | |
| etasd.paid = 1 $condition | |
| ORDER BY $orderBy"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| $result = CommonUtil::convertObjectToUTF8Format($result); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * update certificate applied student details | |
| * @param $request | |
| */ | |
| public function updateStaffInCertificateApplication($student, $status) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $condition = ""; | |
| if($student->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$student->semAplnId'"; | |
| } | |
| $sql = "UPDATE ec_certificate_applied_student_details SET staffID = '$student->staffId', status = '$status' WHERE studentID = '$student->studentID' $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update certificate applied student details | |
| * @param $request | |
| */ | |
| public function updateApproveStaffInCertificateApplication($student, $flag, $status) | |
| { | |
| $student = $this->realEscapeObject($student); | |
| $flag = $this->realEscapeString($flag); | |
| $status = $this->realEscapeString($status); | |
| $condition =""; | |
| if($student->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$student->semAplnId'"; | |
| } | |
| $sql = "UPDATE ec_certificate_applied_student_details SET verified = '$flag', status = '$status' WHERE studentID = '$student->studentID' $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete application payment details | |
| * @param $request | |
| */ | |
| public function deleteApplicationPaymentDetailsByRequest($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $conditions = ""; | |
| if ($request->applicationId) { | |
| $conditions .= " AND JSON_CONTAINS(properties, '{\"applicationId\":\"$request->applicationId\"}')"; | |
| } | |
| $sql = "DELETE FROM ec_online_payment | |
| WHERE | |
| studentID = $request->studentId AND type = '$request->type' | |
| $conditions"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get certificate application details | |
| * @param $studentId,type | |
| */ | |
| public function getStudentApplicationDetailsList($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$request->semAplnId'"; | |
| } | |
| $sql = "SELECT | |
| amount, paid,address,remarks,dateOfPay,properties,status,verified,created_date | |
| FROM | |
| ec_certificate_applied_student_details | |
| WHERE | |
| studentID = '$request->studentId' AND type ='$request->type' $condition"; | |
| try { | |
| $result = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * get transcript application payment details | |
| * @param $studentId | |
| */ | |
| public function getApplicationPaymentDetailsProps($studentId, $status, $type,$properties) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $status = $this->realEscapeString($status); | |
| $type = $this->realEscapeString($type); | |
| $condition = ""; | |
| if($properties->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$properties->semAplnId'"; | |
| } | |
| $sql = "SELECT | |
| txnID, amount, transactionDate, payment_gateway_txn_id | |
| FROM | |
| ec_online_payment | |
| WHERE | |
| studentID = $studentId AND status = '$status' AND type = '$type' $condition"; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * save transcript application payment details | |
| * @param $request | |
| */ | |
| public function savePaymentDetailsProps($request) | |
| { | |
| $properties = $request->properties ? addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)):""; | |
| $request = $this->realEscapeObject($request); | |
| $sql = "INSERT INTO ec_online_payment (studentID,txnID, amount, status, type,properties) VALUES ('$request->studentId', '$request->txnID', '$request->amount', '$request->status', '$request->type','$properties')"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * save certificate applied student details | |
| * @param $request | |
| */ | |
| public function updateCertificateApplicationDetailsProps($studentId, $paid, $status, $payment_method, $type,$properties) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $paid = $this->realEscapeString($paid); | |
| $payment_method = $this->realEscapeString($payment_method); | |
| $status = $this->realEscapeString($status); | |
| $date = date("Y-m-d"); | |
| $condition =""; | |
| if($properties->semAplnId){ | |
| $condition .=" AND properties->'$.semAplnId' = '$properties->semAplnId'"; | |
| } | |
| $sql = "UPDATE ec_certificate_applied_student_details SET paid = '$paid', payment_method = '$payment_method', dateofPay = '$date', status = '$status' WHERE studentID = '$studentId' AND type ='$type' $condition"; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get consolidated publishDetails | |
| * @param $batchId | |
| */ | |
| public function getConsolidatedPublishDates($batchId) | |
| { | |
| $batchId = $this->realEscapeString($batchId); | |
| $sql = "SELECT isPublish,publishFromDate,publishToDate FROM consolidated_marklist_publish WHERE batchID = $batchId "; | |
| try { | |
| $result = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * @author Sibin | |
| * save student low pass grade applied subjects | |
| */ | |
| public function saveStudentLowPassGradeApplication($request) | |
| { | |
| $properties = $request->properties ? addslashes(json_encode($request->properties, JSON_INVALID_UTF8_IGNORE | JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_ERROR_INVALID_PROPERTY_NAME)) : ""; | |
| $request = $this->realEscapeObject($request); | |
| $identifier = $request->identifier; | |
| $userId = $request->userId; // Assuming userId is available in $request object | |
| $entryType = $request->entryType; | |
| $userType = $request->userType; | |
| $identifierType = $request->identifierType; | |
| $sql = "INSERT INTO subjectWiseSubmissions (identifier, userId, properties, entryType, userType, identifierType, created_by, created_date) VALUES ('$identifier', '$userId', '$properties', '$entryType', '$userType', '$identifierType', '$userId', NOW())"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| /** | |
| * @author Sibin | |
| * get student low pass grade applied subjects | |
| */ | |
| public function getStudentLowPassGradeApplication($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| $orderBy = ""; | |
| $result = []; | |
| if($request->userId){ | |
| $condition .= " AND userId IN($request->userId)"; | |
| } | |
| if($request->identifier){ | |
| $condition .= " AND identifier = '$request->identifier'"; | |
| } | |
| if($request->entryType){ | |
| $condition .= " AND entryType = '$request->entryType'"; | |
| } | |
| if($request->userType){ | |
| $condition .= " AND userType = '$request->userType'"; | |
| } | |
| if($request->identifierType){ | |
| $condition .= " AND identifierType = '$request->identifierType'"; | |
| } | |
| if($request->id){ | |
| $condition .= " AND id = '$request->id'"; | |
| } | |
| if($request->courseTypeId){ | |
| $condition .= " AND b.courseTypeID IN ($request->courseTypeId)"; | |
| } | |
| if($request->batchStartYear){ | |
| $condition .= " AND b.batchStartYear IN ($request->batchStartYear)"; | |
| } | |
| if($request->fromDate && $request->toDate){ | |
| $condition .= " AND created_date BETWEEN '$request->fromDate' AND '$request->toDate' "; | |
| $orderBy = " ORDER BY created_date;"; | |
| } | |
| $sql = "SELECT sws.id,sws.userId,sws.properties,sws.created_date,sa.regNo,sa.studentName | |
| FROM subjectWiseSubmissions sws | |
| INNER JOIN studentaccount sa ON sa.studentID = sws.userId | |
| INNER JOIN batches b ON b.batchID = sa.batchID | |
| WHERE 1=1 $condition $orderBy "; | |
| try { | |
| if($request->getList){ | |
| return $this->executeQueryForList($sql); | |
| } | |
| if($request->getSubjectList){ | |
| $studentList = $this->executeQueryForList($sql); | |
| foreach($studentList as $student){ | |
| foreach(json_decode($student->properties)->subjects as $subject){ | |
| $student->subjects[$subject->subjectId] = $subject; | |
| } | |
| $result[$student->userId] = $student; | |
| } | |
| return $result; | |
| } | |
| else{ | |
| return $this->executeQueryForObject($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| /** | |
| * @author Sibin | |
| * update student low pass grade applied subjects | |
| */ | |
| public function updateStudentLowPassGradeApplication($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $condition = ""; | |
| if($request->userId){ | |
| $condition .= " AND userId IN($request->userId)"; | |
| } | |
| if($request->identifier){ | |
| $condition .= " AND identifier = '$request->identifier'"; | |
| } | |
| if($request->entryType){ | |
| $condition .= " AND entryType = '$request->entryType'"; | |
| } | |
| if($request->userType){ | |
| $condition .= " AND userType = '$request->userType'"; | |
| } | |
| if($request->identifierType){ | |
| $condition .= " AND identifierType = '$request->identifierType'"; | |
| } | |
| if($request->id){ | |
| $condition .= " AND id = '$request->id'"; | |
| } | |
| $request->subjects = base64_decode($request->subjects); | |
| $subjectsJson = json_decode($request->subjects); | |
| $subjectsJson = json_encode($subjectsJson, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_IGNORE); | |
| $sql = "UPDATE subjectWiseSubmissions | |
| SET properties = JSON_SET(properties, '$.subjects', CAST('$subjectsJson' AS JSON)) | |
| WHERE 1=1 $condition"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| } |