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 / 7
 CRAP
0.00% covered (danger)
0.00%
0 / 376
ConsolidatedSemReportService
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 7
 756.00
0.00% covered (danger)
0.00%
0 / 376
 __construct
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 3
 __clone
0.00% covered (danger)
0.00%
0 / 1
 2.00
0.00% covered (danger)
0.00%
0 / 1
 getInstance
0.00% covered (danger)
0.00%
0 / 1
 6.00
0.00% covered (danger)
0.00%
0 / 5
 getConsolidatedReport
0.00% covered (danger)
0.00%
0 / 1
 72.00
0.00% covered (danger)
0.00%
0 / 43
 getConsolidatedExamDetails
0.00% covered (danger)
0.00%
0 / 1
 30.00
0.00% covered (danger)
0.00%
0 / 126
 getConsoildatedAssignentDetails
0.00% covered (danger)
0.00%
0 / 1
 30.00
0.00% covered (danger)
0.00%
0 / 116
 getConsolidatedStudentAtendancePercent
0.00% covered (danger)
0.00%
0 / 1
 30.00
0.00% covered (danger)
0.00%
0 / 82
<?php
namespace com\linways\core\ams\professional\service;
use com\linways\core\ams\professional\exception\ProfessionalException;
use com\linways\core\ams\professional\mapper\ConsolidatedSemReportMapper;
use com\linways\core\ams\professional\dto\Subject;
use com\linways\core\ams\professional\service\BatchService;
class ConsolidatedSemReportService 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 = ConsolidatedSemReportMapper::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 sem report
     * @param int $batchId
     * @param int $semId
     * @param number $studentId
     * @return object|array|\com\linways\base\util\$objectList[]
     */
    public function getConsolidatedReport($batchId,$semId, $studentId = 0)
    {
        $consolidatedDetails= [];
          
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $studentId = $this->realEscapeString($studentId);
        
        $consolidatedDetails = $this->getConsolidatedExamDetails($batchId, $semId, $studentId);
        
        
        $i = 0;
        $studentAssignmentMarks = $this->getConsoildatedAssignentDetails($batchId, $semId, $studentId);
        $studentAttendanceDetails = $this->getConsolidatedStudentAtendancePercent($batchId, $semId, $studentId);
        
        foreach ($consolidatedDetails as $studentExamMark)
        {
            if($studentExamMark->studentId == $studentAssignmentMarks[$i]->studentId)
            {
                $j = 0;
                $subjects = $studentExamMark->subjects;
                foreach ($subjects as $key => $subject)
                {
                    $found = false;
                    for($k=0;$k < count($studentAssignmentMarks[$i]->subjects);$k++)
                    {
                        if($subject->id == $studentAssignmentMarks[$i]->subjects[$k]->id)
                        {
                            $subject->assignments = $studentAssignmentMarks[$i]->subjects[$j]->assignments;
                            $found = true;
                        }
                        if($subject->id == $studentAttendanceDetails[$i]->subjects[$j]->id)
                        {
                            $subject->attendancePercent = $studentAttendanceDetails[$i]->subjects[$j]->attendancePercent;
                            $subject->attendanceCount = $studentAttendanceDetails[$i]->subjects[$j]->attendanceCount;
                            $subject->attendance = $studentAttendanceDetails[$i]->subjects[$j]->attendance;
                        }
                        if($found)
                        {
                            break;
                        }
                    }
                    $j++;
                }
                $i++;
            }
        }
        return $consolidatedDetails;
    }
    /**
     * get consolidated student exam details
     * @param int $batchId
     * @param int $semId
     * @param number $studentId
     * @throws ProfessionalException
     * @return object|array|\com\linways\base\util\$objectList[]
     */
    public function getConsolidatedExamDetails($batchId, $semId, $studentId = 0)
    {
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $studentId = $this->realEscapeString($studentId);
        
        $sqlStudent = "";
        if($studentId)
        {
            $sqlStudent = " and sa.studentID = ".$studentId." ";
        }
        
        $sortOrderColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($batchId);
        if(empty($sortOrderColumn)){
            $sortOrderColumn = "rollNo";
        }
        
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId);
        
        if($isCurrentSem)
        {
            $sql = "select sa.studentID,
            sa.regNo,
            sa.rollNo,
            sa.studentName,
            sa.batchID,
            sr.subjectID,
            sub.subjectName,
            sub.subjectDesc,
            exty.typeID,
            exty.typeName,
            ex.examID,
            ex.examName,
            sm.marksObtained AS examMark
            from batches bat
            INNER JOIN studentaccount sa ON bat.batchID = sa.batchID
            INNER JOIN semesters sem ON bat.semID = sem.semID
            inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
            INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID and sr.semID = ".$semId."
            INNER JOIN subjects sub ON sub.subjectID = sr.subjectID and sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
            LEFT JOIN exam ex ON ex.batchID = sa.batchID and ex.subjectID = sub.subjectID and ex.semID = ".$semId."
            LEFT JOIN exam_type exty ON ex.examTypeID = exty.typeID AND exty.canShow = 1 AND isInternal = 1
            LEFT JOIN student_marks sm ON sm.examID = ex.examID AND sm.batchId = ex.batchID AND sm.semID = ex.semID AND sm.studentID = sa.studentID
            WHERE
            sr.batchID = ".$batchId." AND sr.semID = ".$semId." and joinedSem.orderNo <= sem.orderNo ".$sqlStudent."
            GROUP BY sa.studentID , sr.subjectID , typeID , examID order by sa.$sortOrderColumn, studentName, subjectID, typeID";
        }
        else
        {
            $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId);
            
            $sql = "select sa.studentID,
            sa.regNo,
            sa.rollNo,
            sa.studentName,
            sa.batchID,
            sr.subjectID,
            sub.subjectName,
            sub.subjectDesc,
             exty.typeID,
            exty.typeName,
            ex.examID,
            ex.examName,
            sm.marksObtained AS examMark
            from 
            batches bat
            INNER JOIN studentaccount sa ON bat.batchID = sa.batchID
            INNER JOIN semesters sem ON bat.semID = sem.semID
            inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
            INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID
                and sr.semID = ".$semId."
            INNER JOIN subjects sub ON sub.subjectID = sr.subjectID
                and sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
                LEFT JOIN exam ex ON ex.batchID = sa.batchID
                and ex.subjectID = sub.subjectID
                and ex.semID = ".$semId."
            LEFT JOIN exam_type exty ON ex.examTypeID = exty.typeID
                AND exty.canShow = 1
                AND isInternal = 1
            LEFT JOIN student_marks sm ON sm.examID = ex.examID
                AND sm.batchId = ex.batchID
                AND sm.semID = ex.semID
                AND sm.studentID = sa.studentID
                WHERE
                sr.batchID = ".$batchId." AND sr.semID = ".$semId."
                    and joinedSem.orderNo <= sem.orderNo ".$sqlStudent."
            GROUP BY sa.$sortOrderColumn, sa.studentID , sr.subjectID , typeID , examID 
        union 
            select sa.studentID,
                sa.regNo,
                sa.rollNo,
                sa.studentName,
                sa.batchID,
                sr.subjectID,
                sub.subjectName,
                sub.subjectDesc,
                exty.typeID,
                exty.typeName,
                ex.examID,
                ex.examName,
                sm.marksObtained AS examMark            
                from 
                batches bat
                INNER JOIN failed_students fs ON bat.batchID = fs.previousBatch
        INNER JOIN studentaccount sa ON fs.studentID = sa.studentID
        INNER JOIN semesters fsem ON fsem.semID = fs.failedInSemester
        inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
        INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID
            and sr.semID = ".$semId."
        INNER JOIN subjects sub ON sub.subjectID = sr.subjectID
            and sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
            LEFT JOIN exam ex ON ex.batchID = sa.batchID
            and ex.subjectID = sub.subjectID
            and ex.semID = ".$semId."
        LEFT JOIN exam_type exty ON ex.examTypeID = exty.typeID
            AND exty.canShow = 1
            AND isInternal = 1
        LEFT JOIN student_marks sm ON sm.examID = ex.examID
            AND sm.batchId = ex.batchID
            AND sm.semID = ex.semID
            AND sm.studentID = sa.studentID
            WHERE
            sr.batchID = ".$batchId." AND sr.semID = ".$semId."
                and joinedSem.orderNo <= ".$semDetails->orderNo." and fsem.orderNo >= ".$semDetails->orderNo." ".$sqlStudent."
        GROUP BY sa.studentID , sr.subjectID , typeID , examID order by $sortOrderColumn, studentName, subjectID, typeID";
        }
        
        try {
            return $this->executeQueryForList($sql, $this->mapper[ConsolidatedSemReportMapper::GET_CONSOLIDATED_REPORT]);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(),$e->getMessage());
        }
        
    }
    /**
     * Get Consolidated assignment details
     * @param int $batchId
     * @param int $semId
     * @param number $studentId
     * @throws ProfessionalException
     * @return unknown
     */
    public function getConsoildatedAssignentDetails($batchId, $semId, $studentId = 0)
    {
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $studentId = $this->realEscapeString($studentId);
        $sqlStudent = "";
        
        if($studentId)
        {
            $sqlStudent = " and sa.studentID = ".$studentId." ";
        }
        
        $sortOrderColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($batchId);
        if(empty($sortOrderColumn)){
            $sortOrderColumn = "rollNo";
        }
        
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId);
        
        if($isCurrentSem)
        {
            $sql = "select sa.studentID,
                    sa.regNo,
                    sa.rollNo,
                    sa.studentName,
                    sa.batchID,
                    sr.subjectID,
                    sub.subjectName,
                    sub.subjectDesc,
                    bas.assignmentID,
                    bas.assiNu,
                    am.marksObtained AS assignmentMark
                    from
                     batches bat
                    INNER JOIN studentaccount sa ON bat.batchID = sa.batchID
                    INNER JOIN semesters sem ON bat.semID = sem.semID
                    inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
                    INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID
                        and sr.semID = ".$semId."
                    INNER JOIN subjects sub ON sub.subjectID = sr.subjectID
                        and sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
                          LEFT JOIN batch_assignment bas ON bas.subjectID = sub.subjectID
                        AND sa.batchId = bas.batchID
                        and bas.semID = ".$semId."
                    LEFT JOIN assignment_marks am ON bas.assignmentID = am.assignmentID
                        AND sa.studentID = am.studentID
                            
                        WHERE
                        sr.batchID = ".$batchId." AND sr.semID =".$semId."
                            and joinedSem.orderNo <= sem.orderNo ".$sqlStudent." AND bas.assiNu is not null AND bas.assiNu !=0
                    GROUP BY sa.studentID , sr.subjectID , assiNu order by $sortOrderColumn,studentName, sub.subjectID, assiNu;";
        }
        else 
        {
            $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId);
            
            $sql = "select sa.studentID,
                    sa.regNo,
                    sa.rollNo,
                    sa.studentName,
                    sa.batchID,
                    sr.subjectID,
                    sub.subjectName,
                    sub.subjectDesc,
                    bas.assignmentID,
                    bas.assiNu,
                    am.marksObtained AS assignmentMark
                    from
                     batches bat
                    INNER JOIN studentaccount sa ON bat.batchID = sa.batchID
                    INNER JOIN semesters sem ON bat.semID = sem.semID
                    inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
                    INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID
                        and sr.semID = ".$semId."
                    INNER JOIN subjects sub ON sub.subjectID = sr.subjectID
                        and sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
                          LEFT JOIN batch_assignment bas ON bas.subjectID = sub.subjectID
                        AND sa.batchId = bas.batchID
                        and bas.semID = ".$semId."
                    LEFT JOIN assignment_marks am ON bas.assignmentID = am.assignmentID
                        AND sa.studentID = am.studentID
                            
                        WHERE
                        sr.batchID = ".$batchId." AND sr.semID =".$semId."
                            and joinedSem.orderNo <= sem.orderNo ".$sqlStudent."
                    GROUP BY sa.studentID , sr.subjectID , assiNu 
                union 
                    select sa.studentID,
                    sa.regNo,
                    sa.rollNo,
                    sa.studentName,
                    sa.batchID,
                    sr.subjectID,
                    sub.subjectName,
                    sub.subjectDesc,
                    bas.assignmentID,
                    bas.assiNu,
                    am.marksObtained AS assignmentMark
                    from
                     batches bat
                     INNER JOIN failed_students fs ON bat.batchID = fs.previousBatch
                    INNER JOIN studentaccount sa ON fs.studentID = sa.studentID
                    INNER JOIN semesters fsem ON fsem.semID = fs.failedInSemester
                    inner join semesters joinedSem ON joinedSem.semID = sa.joiningSemId
                    INNER JOIN sbs_relation sr ON sr.batchID = bat.batchID
                        and sr.semID = ".$semId."
                    INNER JOIN subjects sub ON sub.subjectID = sr.subjectID
                        and sub.subjectName not in ('TUTOR')
                          LEFT JOIN batch_assignment bas ON bas.subjectID = sub.subjectID
                        AND sa.batchId = bas.batchID
                        and bas.semID = ".$semId."
                    LEFT JOIN assignment_marks am ON bas.assignmentID = am.assignmentID
                        AND sa.studentID = am.studentID
                        WHERE
                        sr.batchID = ".$batchId." AND sr.semID =".$semId."
                            and joinedSem.orderNo <= ".$semDetails->orderNo." and fsem.orderNo >= ".$semDetails->orderNo." ".$sqlStudent."
                    GROUP BY sa.studentID , sr.subjectID , assiNu order by $sortOrderColumn,studentName, subjectID, assiNu";
        }
        
        
        try {
            return $this->executeQueryForList($sql, $this->mapper[ConsolidatedSemReportMapper::GET_CONSOLIDATED_REPORT]);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(),$e->getMessage());
        }
    }
    /**
     * Get consolidated attendance details
     * @param int $batchId
     * @param int $semId
     * @param number $studentId
     * @throws ProfessionalException
     * @return object|array|\com\linways\base\util\$objectList[]
     */
    public function getConsolidatedStudentAtendancePercent($batchId, $semId, $studentId = 0)
    {
        $batchId = $this->realEscapeString($batchId);
        $semId = $this->realEscapeString($semId);
        $studentId = $this->realEscapeString($studentId);
        
        $sqlStudent = "";
        if($studentId)
        {
            $sqlStudent = " and sa.studentID = ".$studentId." ";
        }
        
        $sortOrderColumn = BatchService::getInstance()->getStudentSortByColumnOfABatch($batchId);
        if(empty($sortOrderColumn)){
            $sortOrderColumn = "rollNo";
        }
        
        $isCurrentSem = SemesterService::getInstance()->isCurrentSemester($batchId, $semId);
        $semDetails = SemesterService::getInstance()->getSemDetailsBySemId($semId);
        
        if($isCurrentSem)
        {
            $sql = "SELECT *, (attendanceCount/subjectTotalHour)*100 attendancePercentage, subjectTotalHour AS attendance
            FROM (
                SELECT sa.studentID,sa.rollNo, sa.regNo, sa.studentName, sr.subjectID, sr.batchID, sub.subjectName, sub.subjectDesc, count(IF (atn.isAbsent = 0 OR atn.isAbsent = 2,1,NULL)) as attendanceCount,count(atn.attendanceDate) as subjectTotalHour
                    FROM batches ba 
                INNER JOIN studentaccount sa ON sa.batchID = ba.batchID
                INNER JOIN sbs_relation sr ON  sr.batchID = sa.batchID AND  sr.semID = ".$semId."
                INNER JOIN semesters joinedSem ON sa.joiningSemId = joinedSem.semID
                INNER JOIN subjects sub ON sub.subjectID = sr.subjectID AND sub.subjectName not IN (\"".Subject::TUTOR_SUBJECT."\")
                LEFT JOIN attendance atn ON atn.batchID= sa.batchID AND atn.semID = ".$semId." AND atn.sbsID = sr.sbsID AND atn.studentID = sa.studentID AND atn.isBlocked = 0
                WHERE
                    sr.batchID = ".$batchId." AND sr.semID = ".$semId." AND joinedSem.orderNo <= ".$semDetails->orderNo." ".$sqlStudent."
                GROUP BY sa.studentID , sr.subjectID ) AS attn ORDER BY $sortOrderColumn,studentName, subjectID";
        }
        else
        {
            $sql = "SELECT *, (attendanceCount/subjectTotalHour)*100 attendancePercentage, subjectTotalHour AS attendance FROM (select
                        sa.studentID,
                        sa.regNo,
                        sa.rollNo,
                        sa.studentName,
                        sr.subjectID,
                        sr.batchID,
                        sub.subjectName,
                        sub.subjectDesc,
                        count(IF (atn.isAbsent = 0 OR atn.isAbsent = 2,1,NULL)) as attendanceCount,
                        count(atn.attendanceDate) AS subjectTotalHour
                    FROM batches ba 
                    INNER JOIN studentaccount sa ON sa.batchID = ba.batchID
                    INNER JOIN sbs_relation sr ON  sr.batchID = sa.batchID AND  sr.semID = ".$semId."
                    INNER JOIN semesters joinedSem ON sa.joiningSemId = joinedSem.semID
                    INNER JOIN subjects sub ON sub.subjectID = sr.subjectID AND sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
                    LEFT JOIN attendance atn ON atn.batchID= sa.batchID AND atn.semID = ".$semId." AND atn.sbsID = sr.sbsID AND atn.studentID = sa.studentID AND atn.isBlocked = 0
                    WHERE
                        sr.batchID = ".$batchId." AND sr.semID = ".$semId." AND joinedSem.orderNo <= ".$semDetails->orderNo." ".$sqlStudent."
                    GROUP BY sa.studentID , sr.subjectID ) as attn 
                UNION 
                    SELECT *, (attendanceCount/subjectTotalHour)*100 attendancePercentage, subjectTotalHour AS attendance FROM (SELECT
                        sa.studentID,
                        sa.regNo,
                        sa.rollNo,
                        sa.studentName,
                        sr.subjectID,
                        sr.batchID,
                        sub.subjectName,
                        sub.subjectDesc,
                        count(IF (atn.isAbsent = 0 OR atn.isAbsent = 2,1,NULL)) as attendanceCount,
                        count(atn.attendanceDate) AS subjectTotalHour
                    FROM batches bat
                    INNER JOIN failed_students fs ON bat.batchID = fs.previousBatch
                    INNER JOIN studentaccount sa ON fs.studentID = sa.studentID
                    INNER JOIN semesters fsem ON fsem.semID = fs.failedInSemester
                    INNER JOIN sbs_relation sr ON  sr.batchID = sa.batchID AND  sr.semID = ".$semId."
                    INNER JOIN semesters joinedSem ON sa.joiningSemId = joinedSem.semID
                    INNER JOIN subjects sub ON sub.subjectID = sr.subjectID AND sub.subjectName not in (\"".Subject::TUTOR_SUBJECT."\")
                    LEFT JOIN attendance atn on atn.batchID= sa.batchID AND atn.semID = ".$semId." and atn.sbsID = sr.sbsID AND atn.studentID = sa.studentID AND atn.isBlocked = 0
                    WHERE
                        sr.batchID = ".$batchId." AND sr.semID = ".$semId." AND joinedSem.orderNo <= ".$semDetails->orderNo." AND fsem.orderNo >= ".$semDetails->orderNo." ".$sqlStudent."
                    GROUP BY sa.studentID , sr.subjectID ) as attn  ORDER BY $sortOrderColumn, studentName, subjectID";
        }
        
        try {
            return $this->executeQueryForList($sql, $this->mapper[ConsolidatedSemReportMapper::GET_CONSOLIDATED_REPORT]);
        } catch (\Exception $e) {
            throw new ProfessionalException($e->getCode(),$e->getMessage());
        }
    }
}