Code Coverage
 
Classes and Traits
Functions and Methods
Lines
Total
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 8
 CRAP
0.00% covered (danger)
0.00%
0 / 125
ImporterService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 8
 600.00
0.00% covered (danger)
0.00%
0 / 125
 __construct
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 2
 __clone
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 2
 getInstance
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 5
 importStudentExamRegistrationDataFromExcel
0.00% covered (danger)
0.00%
0 / 1
 20.00
0.00% covered (danger)
0.00%
0 / 20
 excludeStudentsNotAttendingExams
0.00% covered (danger)
0.00%
0 / 1
 42.00
0.00% covered (danger)
0.00%
0 / 42
 changeProcessedDataStatus
0.00% covered (danger)
0.00%
0 / 1
 20.00
0.00% covered (danger)
0.00%
0 / 18
 getAllCreatedExams
0.00% covered (danger)
0.00%
0 / 1
 20.00
0.00% covered (danger)
0.00%
0 / 27
 deleteAllExpiredImportedRegistrationData
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 9
<?php
/**
 * User: jithinvijayan
 * Date: 13/11/19
 * Time: 4:47 PM
 */
namespace com\linways\core\ams\professional\service;
use com\linways\core\ams\professional\exception\ProfessionalException;
use com\linways\core\ams\professional\request\DeleteExcludedStudentRequest;
use com\linways\core\ams\professional\request\ExcludeStudentsNotAttendingExamsRequest;
use com\linways\core\ams\professional\request\ImportExamRegisteredStudentRequest;
class ImporterService extends BaseService
{
    /**
     * Presence of a static member variable
     *
     * @var null
     */
    private static $_instance = null;
    /**
     * Mapper variable
     * @var array
     */
    private $mapper = [];
    /**
     * Initialise mapper, logger, hooks here
     *
     * ReportGenderService constructor.
     */
    private function __construct()
    {
    }
    /**
     * Prevent any object or instance of that class to be cloned
     */
    private function __clone()
    {
    }
    /**
     * Have a single globally accessible static method
     *
     * @return ImporterService|null
     */
    public static function getInstance()
    {
        if (!is_object(self::$_instance))
            self::$_instance = new self ();
        return self::$_instance;
    }
    /**
     * @param ImportExamRegisteredStudentRequest[] $requests
     * @throws ProfessionalException
     * @author jithinvijayan
     */
    public function importStudentExamRegistrationDataFromExcel($requests)
    {
        $requests = $this->realEscapeArray($requests);
        $sql = "INSERT INTO importer_exam_registered_students (register_number, admission_number, subject_code, department_id, 
                batch_id, semester_id, exam_type_id, show_hidden_exams, created_by, created_date, updated_by, updated_date) 
                VALUES ";
        foreach ($requests as $request) {
            if (empty($request->examTypeId)) {
                $request->examTypeId = "NULL";
            }
            $sql .= " ('$request->registerNumber','$request->admissionNumber','$request->subjectCode',$request->departmentId,
                        $request->batchId,$request->semesterId,$request->examTypeId,$request->showHiddenExams,
                        $request->createdBy,UTC_TIMESTAMP(),$request->updatedBy,UTC_TIMESTAMP()),";
        }
        $sql = rtrim($sql, ',');
        try {
            $this->executeQuery($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
    /**
     * @param ExcludeStudentsNotAttendingExamsRequest $request
     * @return void
     * @throws ProfessionalException
     * @author jithinvijayan
     */
    public function excludeStudentsNotAttendingExams(ExcludeStudentsNotAttendingExamsRequest $request)
    {
        $request = $this->realEscapeObject($request);
        $conditions = "";
        $joinCondition = "";
        if (!empty($request->examTypeId)) {
            $conditions .= " AND iers.exam_type_id  =$request->examTypeId ";
            $joinCondition .= " AND e.examTypeID = iers.exam_type_id ";
        }
        if (empty($request->showHiddenExams)) {
            $conditions .= " AND iers.show_hidden_exams = 0 ";
            $joinCondition .= " AND e.canShow=1 ";
        }
        $sql = "INSERT INTO exam_excluded_students (studentID, examID, batchID)  
                SELECT DISTINCT  sa.studentID,e.examID, iers.batch_id
                FROM importer_exam_registered_students iers
                INNER JOIN subjects s ON s.subjectName = iers.subject_code
                INNER JOIN exam e ON e.subjectID = s.subjectID
                    AND e.batchID = iers.batch_id
                    AND e.semID = iers.semester_id
                    $joinCondition
                INNER JOIN studentaccount sa ON sa.batchID = e.batchID 
                LEFT JOIN importer_exam_registered_students iers1 ON iers1.register_number = sa.regNo
                AND iers.batch_id  =iers1.batch_id AND iers1.subject_code = iers.subject_code 
                AND iers.semester_id = iers1.semester_id AND iers1.is_active=1
                WHERE iers.department_id =$request->departmentId AND iers.batch_id = $request->batchId 
                AND iers.semester_id=$request->semesterId AND iers.is_active=1 AND iers1.id IS NULL $conditions";
        try {
            /**
             * Fetching all created exam ids
             */
            $exams = $this->getAllCreatedExams($request);
            /**
             * Deleting if students already excluded from this subject
             */
            foreach ($exams as $exam) {
                $deleteRequest = new DeleteExcludedStudentRequest();
                $deleteRequest->examId = $exam->examId;
                $deleteRequest->batchId = $request->batchId;
                ExamService::getInstance()->deleteExcludedStudentsFromExam($deleteRequest);
            }
            if (count($exams) > 0) {
                $this->executeQuery($sql);
                /**
                 * Changing processed data status
                 */
                $this->changeProcessedDataStatus($request);
            }
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
    /**
     * @param $request
     * @throws ProfessionalException
     * @author jithinvijayan
     */
    private function changeProcessedDataStatus($request)
    {
        $request = $this->realEscapeObject($request);
        $conditions = "";
        if (!empty($request->examTypeId)) {
            $conditions .= " AND exam_type_id  =$request->examTypeId ";
        }
        if (empty($request->showHiddenExams)) {
            $conditions .= " AND show_hidden_exams = 0";
        }
        $sql = "UPDATE importer_exam_registered_students SET is_active =0
                 WHERE department_id =$request->departmentId AND batch_id = $request->batchId 
                AND semester_id=$request->semesterId AND is_active=1 $conditions";
        try {
            $this->executeQuery($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
    /**
     * @param $request
     * @return Object|array
     * @throws ProfessionalException
     * @author jithinvijayan
     */
    private function getAllCreatedExams($request)
    {
        $request = $this->realEscapeObject($request);
        $conditions = "";
        $joinCondition = "";
        if (!empty($request->examTypeId)) {
            $conditions .= " AND iers.exam_type_id  =$request->examTypeId ";
            $joinCondition .= " AND e.examTypeID = iers.exam_type_id ";
        }
        if (empty($request->showHiddenExams)) {
            $conditions .= " AND iers.show_hidden_exams =0";
            $joinCondition .= " AND e.canShow=1 ";
        }
        $sql = "SELECT DISTINCT e.examID as examId
                FROM importer_exam_registered_students iers
                INNER JOIN subjects s ON s.subjectName = iers.subject_code
                INNER JOIN exam e ON e.subjectID = s.subjectID
                    AND e.batchID = iers.batch_id
                    AND e.semID = iers.semester_id
                    $joinCondition
                WHERE iers.department_id =$request->departmentId AND iers.batch_id = $request->batchId 
                AND iers.semester_id=$request->semesterId AND iers.is_active=1  $conditions";
        try {
            return $this->executeQueryForList($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
    /**
     * Deleting expired imported registration from temp table. default expiration time is 60 days
     *
     * @throws ProfessionalException
     * @author jithinvijayan
     */
    public function deleteAllExpiredImportedRegistrationData()
    {
        $sql = "DELETE FROM importer_exam_registered_students 
                WHERE UNIX_TIMESTAMP(created_date) < UNIX_TIMESTAMP(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 60 DAY))";
        try {
            $this->executeQuery($sql);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(), $e->getMessage());
        }
    }
}