Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 55 |
CRAP | |
0.00% |
0 / 1058 |
| TransportService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 55 |
24180.00 | |
0.00% |
0 / 1058 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getBuswiseStudentDetails | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 115 |
|||
| getBuswiseStaffDetails | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 96 |
|||
| getBusRouteDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getBusRoutes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getBoardingPoints | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getAllBus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| searchTransportationAdmitedStudents | |
0.00% |
0 / 1 |
156.00 | |
0.00% |
0 / 58 |
|||
| getStudentTransportationDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getStudentOneWayFare | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateTransportationStudents | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getBusRoutBoardingPointRelation | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStaffOneWayFare | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| createStaffTransportRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getStaffTransportRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getStaffTransportRequestById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteStaffTransportRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getAllTransportationRequestedStaffs | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 23 |
|||
| getStaffTransportRequestByRequestId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| createStaffTransportHistory | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteTransportationStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteTransportationStaffById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createTransportationStaff | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| updateTransportationStaffRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTransportionAdmittedStaffs | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getTransportAdmittedStaffByStaffId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 44 |
|||
| getTransportAdmittedStaffByTransportationsStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 40 |
|||
| updateTransportationStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| updateTransportationStaffById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTransportAdmittedStaffByStaffIds | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 47 |
|||
| getTransportAdmittedStaffByTransportationStaffIds | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| createTransportStaffApprovalGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getAllTransportStaffApprovalGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getTransportStaffApprovalGroupById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateTransportStaffApprovalGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| deleteTransportStaffApprovalGroupById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| createTransportationStaffUserGroup | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 24 |
|||
| getAllTransportationStaffUserGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| deleteTransportationStaffUserGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getTransportationStaffUserGroupByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getNextLvelPriorities | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getNextLevelStaffDetailsUsingPriority | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 22 |
|||
| getCurrentLevelStaffDetailsUsingPriority | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| updateStaffTransportLevelPriority | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| createStaffTransportApproval | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getBoardingPointDetailsByBusRouteId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getBoardingPointsByBoardingPointRequest | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 47 |
|||
| deleteTransportAdmittedStudents | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 31 |
|||
| deleteTransportationStudentRequest | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getBusTrips | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getStudentTransportRequestDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 44 |
|||
| getCurrentLevelApproversForStudentTransport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getNextLevelApproversForStudentTransport | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\dto\TransportationStaff; | |
| use com\linways\core\ams\professional\dto\TransportationStudent; | |
| use com\linways\core\ams\professional\dto\TransportStaffApproval; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\request\TransportBuswiseRequest; | |
| use com\linways\core\ams\professional\response\TransportBuswiseStaffResponse; | |
| use com\linways\core\ams\professional\request\GetBusBoardingPointSearchRequest; | |
| use com\linways\core\ams\professional\response\TransportBuswiseStudentResponse; | |
| use com\linways\core\ams\professional\response\GetBusBoardingPointSearchResponse; | |
| use com\linways\core\ams\professional\service\StaffService; | |
| class TransportService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| {} | |
| // 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 Bus wise student detailed report | |
| * @param TransportBuswiseRequest $transportBuswiseRequest | |
| * @return \com\linways\core\ams\professional\response\TransportBuswiseStudentResponse | |
| */ | |
| public function getBuswiseStudentDetails($transportBuswiseRequest) | |
| { | |
| $transportBuswiseRequest = $this->realEscapeObject($transportBuswiseRequest); | |
| $transportBuswiseStudentResponse = new TransportBuswiseStudentResponse(); | |
| $busrouteCond = ""; | |
| $deptCond = ""; | |
| $batchCond = ""; | |
| if ($transportBuswiseRequest->busrouteID != "0") | |
| { | |
| $busrouteCond = " and t5.busrouteID=\"$transportBuswiseRequest->busrouteID\""; | |
| } | |
| if($transportBuswiseRequest->deptID != "0") | |
| { | |
| $deptCond = "AND t3.deptID = $transportBuswiseRequest->deptID"; | |
| } | |
| if($transportBuswiseRequest->batchID != "0") | |
| { | |
| $batchCond = "AND t2.batchID = $transportBuswiseRequest->batchID"; | |
| } | |
| $sortCond = ""; | |
| switch ($transportBuswiseRequest->sortBy) | |
| { | |
| case 'dept' : | |
| $sortCond = 'ORDER BY t3.deptName '; | |
| break; | |
| case 'batch' : | |
| $sortCond = 'ORDER BY t2.batchName'; | |
| break; | |
| case 'bus' : | |
| $sortCond = 'ORDER BY t5.destinationName'; | |
| break; | |
| case 'bdng' : | |
| $sortCond = 'ORDER BY t7.bpName'; | |
| break; | |
| case 'kms' : | |
| $sortCond = 'ORDER BY t7.bp_kms'; | |
| break; | |
| } | |
| $sql = "SELECT | |
| t1.studentName, | |
| t2.batchName, | |
| t3.deptName, | |
| t1.batchID, | |
| t8.busName, | |
| t5.destinationName, | |
| t7.bpName, | |
| t4.studentTrips, | |
| t6.boardingTime, | |
| t6.dropTime, | |
| t4.studentFare, | |
| t7.bp_kms, | |
| t1.studentID, | |
| t4.valFrom, | |
| t4.valTo, | |
| t1.studentPhone, | |
| t1.parentPhone, | |
| t9.semName, | |
| t8.noofSeats, | |
| t1.myImage, t2.batchStartYear, t2.batchEndYear | |
| FROM | |
| studentaccount t1, | |
| batches t2, | |
| department t3, | |
| transportation_students t4, | |
| transportation_busroute t5, | |
| transportation_brbp_relation t6, | |
| transportation_boardingpoint t7, | |
| transportation_bus t8, | |
| semesters t9 | |
| where | |
| (t1.batchID = t2.batchID AND | |
| t1.deptID = t3.deptID AND | |
| t1.studentID = t4.studentID AND | |
| t5.busrouteID = t4.busrouteID AND | |
| t4.brbpID = t6.brbpID AND | |
| t6.bpID = t7.bpID AND | |
| t5.busID = t8.busID AND t2.semID = t9.semID) AND t4.valTo >= \"" . $transportBuswiseRequest->fromdate . "\" and t4.valFrom <= \"" . $transportBuswiseRequest->todate . "\" | |
| $busrouteCond $deptCond $batchCond $sortCond $transportBuswiseRequest->sortOrder ,t1.rollNo ASC limit $transportBuswiseRequest->startIndex , $transportBuswiseRequest->endIndex"; | |
| try | |
| { | |
| $result = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $transportBuswiseStudentResponse->buswisestudentList = $result; | |
| $sqlCount = "SELECT | |
| Count(t1.studentName) as totalRecords | |
| FROM | |
| studentaccount t1, | |
| batches t2, | |
| department t3, | |
| transportation_students t4, | |
| transportation_busroute t5, | |
| transportation_brbp_relation t6, | |
| transportation_boardingpoint t7, | |
| transportation_bus t8, | |
| semesters t9 | |
| where | |
| (t1.batchID = t2.batchID AND | |
| t1.deptID = t3.deptID AND | |
| t1.studentID = t4.studentID AND | |
| t5.busrouteID = t4.busrouteID AND | |
| t4.brbpID = t6.brbpID AND | |
| t6.bpID = t7.bpID AND | |
| t5.busID = t8.busID AND t2.semID = t9.semID) AND t4.valTo >= \"" . $transportBuswiseRequest->fromdate . "\" and t4.valFrom <= \"" . $transportBuswiseRequest->todate . "\" | |
| $busrouteCond $deptCond $batchCond"; | |
| try | |
| { | |
| $transportBuswiseStudentResponse->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $transportBuswiseStudentResponse; | |
| } | |
| /** | |
| * Get Bus wise staff detailed report | |
| * @param TransportBuswiseRequest $transportBuswiseRequest | |
| * @return \com\linways\core\ams\professional\response\TransportBuswiseStaffResponse | |
| */ | |
| public function getBuswiseStaffDetails($transportBuswiseRequest) | |
| { | |
| $transportBuswiseRequest = $this->realEscapeObject($transportBuswiseRequest); | |
| $transportBuswiseStaffResponse = new TransportBuswiseStaffResponse(); | |
| $busrouteCond = ""; | |
| $deptCond = ""; | |
| $batchCond = ""; | |
| if ($transportBuswiseRequest->busrouteID != "0") | |
| { | |
| $busrouteCond = " and t5.busrouteID=\"$transportBuswiseRequest->busrouteID\""; | |
| } | |
| if($transportBuswiseRequest->deptID != "0") | |
| { | |
| $deptCond = "AND t3.deptID = $transportBuswiseRequest->deptID"; | |
| } | |
| $sortCond = ""; | |
| switch ($transportBuswiseRequest->sortBy) | |
| { | |
| case 'dept' : | |
| $sortCond = 'ORDER BY t3.deptName '; | |
| break; | |
| case 'bus' : | |
| $sortCond = 'ORDER BY t5.destinationName'; | |
| break; | |
| case 'bdng' : | |
| $sortCond = 'ORDER BY t7.bpName'; | |
| break; | |
| case 'kms' : | |
| $sortCond = 'ORDER BY t7.bp_kms'; | |
| break; | |
| } | |
| $sql = "SELECT distinct | |
| t1.staffName, | |
| t3.deptName, | |
| t8.busName, | |
| t5.destinationName, | |
| t7.bpName, | |
| t4.staffTrips, | |
| t6.boardingTime, | |
| t6.dropTime, | |
| t4.staffFare, | |
| t7.bp_kms, | |
| t1.staffID, | |
| t4.valFrom, | |
| t4.valTo, | |
| t1.staffPhone, | |
| t8.noofSeats, | |
| t1.myImage | |
| FROM | |
| staffaccounts t1, | |
| department t3, | |
| transportation_staffs t4, | |
| transportation_busroute t5, | |
| transportation_brbp_relation t6, | |
| transportation_boardingpoint t7, | |
| transportation_bus t8 | |
| where | |
| (t1.deptID = t3.deptID AND | |
| t1.staffID = t4.staffID AND | |
| t4.brbpID = t6.brbpID AND | |
| t6.busrouteID = t5.busrouteID AND | |
| t6.bpID = t7.bpID AND | |
| t5.busID = t8.busID) AND t4.valTo >= \"" . $transportBuswiseRequest->fromdate . "\" and t4.valFrom <= \"" . $transportBuswiseRequest->todate . "\" | |
| $busrouteCond $deptCond $batchCond $sortCond $transportBuswiseRequest->sortOrder "; | |
| $sqlCount = "SELECT count(t4.staffID) as totalRecords | |
| FROM | |
| staffaccounts t1, | |
| department t3, | |
| transportation_staffs t4, | |
| transportation_busroute t5, | |
| transportation_brbp_relation t6, | |
| transportation_boardingpoint t7, | |
| transportation_bus t8 | |
| where | |
| (t1.deptID = t3.deptID AND | |
| t1.staffID = t4.staffID AND | |
| t4.brbpID = t6.brbpID AND | |
| t6.busrouteID = t5.busrouteID AND | |
| t6.bpID = t7.bpID AND | |
| t5.busID = t8.busID) AND t4.valTo >= \"" . $transportBuswiseRequest->fromdate . "\" and t4.valFrom <= \"" . $transportBuswiseRequest->todate . "\" | |
| $busrouteCond $deptCond $batchCond"; | |
| try | |
| { | |
| $transportBuswiseStaffResponse->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| if($transportBuswiseRequest->isExport) | |
| { | |
| $transportBuswiseRequest->endIndex=$transportBuswiseStaffResponse->totalRecords; | |
| } | |
| $sql .=" limit $transportBuswiseRequest->startIndex , $transportBuswiseRequest->endIndex"; | |
| $result = $this->executeQueryForList($sql); | |
| $transportBuswiseStaffResponse->buswiseStaffList = $result; | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $transportBuswiseStaffResponse; | |
| } | |
| /** | |
| * get bus route details using brbpid and bus route id | |
| * @param int $busrouteID | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBusRouteDetailsById($busrouteID) | |
| { | |
| $busrouteID = $this->realEscapeString($busrouteID); | |
| $details=NULL; | |
| $sql = "SELECT t1.busrouteID, t1.destinationName, t2.busName from transportation_busroute t1, transportation_bus t2 where t1.busID=t2.busID AND t1.busrouteID=$busrouteID"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get all bus routes | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBusRoutes() | |
| { | |
| $busRoutes=[]; | |
| $sql = "SELECT br.busrouteID as id, br.destinationName as destination, tb.busName as name from transportation_busroute br inner join transportation_bus tb where br.busID=tb.busID"; | |
| try | |
| { | |
| $busRoutes = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $busRoutes; | |
| } | |
| /** | |
| * get all boarding points | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBoardingPoints() | |
| { | |
| $boardingPoints=[]; | |
| $sql =" SELECT bpID AS id, bpName AS name FROM transportation_boardingpoint"; | |
| try | |
| { | |
| $boardingPoints = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $boardingPoints; | |
| } | |
| /** | |
| * get all buses | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllBus() | |
| { | |
| $busDetails=[]; | |
| $sql ="SELECT busID AS id, busName as name, noofSeats AS seats FROM transportation_bus"; | |
| try | |
| { | |
| $busDetails = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $busDetails; | |
| } | |
| /** | |
| * To search student from transportation admitted list | |
| * @param string $studentName | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function searchTransportationAdmitedStudents($searchRequest, $studentName) | |
| { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $studentName = $this->realEscapeString($studentName); | |
| $sql = "SELECT sa.studentID, | |
| sa.studentName, | |
| sa.studentPhone, | |
| sa.studentEmail, | |
| sa.rollNo, | |
| ts.valFrom, | |
| ts.valTo, | |
| sa.hosteller, | |
| bth.batchName, | |
| sem.semName, | |
| tbp.bpName | |
| FROM studentaccount sa | |
| INNER JOIN transportation_students ts ON sa.studentID = ts.studentID | |
| INNER JOIN transportation_brbp_relation tbr ON tbr.brbpID = ts.brbpID | |
| INNER JOIN transportation_busroute tb ON tb.busrouteID= ts.busrouteID | |
| LEFT JOIN transportation_boardingpoint tbp ON tbp.bpID=tbr.bpID | |
| LEFT JOIN batches bth ON bth.batchID=sa.batchID | |
| LEFT JOIN semesters sem ON sem.semID=bth.semID | |
| WHERE sa.studentName LIKE '%$studentName%'"; | |
| if($searchRequest->batchId) | |
| { | |
| $sql.= " AND sa.batchID = $searchRequest->batchId"; | |
| } | |
| if($searchRequest->busId) | |
| { | |
| $sql.= " AND tb.busID = $searchRequest->busId"; | |
| } | |
| if($searchRequest->busrouteId) | |
| { | |
| $sql.= " AND ts.busrouteID = $searchRequest->busrouteId"; | |
| } | |
| if($searchRequest->bpId) | |
| { | |
| $sql.= " AND tbr.bpID = $searchRequest->bpId"; | |
| } | |
| if($searchRequest->fromDate && $searchRequest->toDate) | |
| { | |
| $sql.= " AND (ts.valFrom >= '$searchRequest->fromDate' AND ts.valTo <= '$searchRequest->toDate')"; | |
| } | |
| elseif ($searchRequest->fromDate && !$searchRequest->toDate) | |
| { | |
| $sql.= " AND ts.valFrom >= '$searchRequest->fromDate'"; | |
| } | |
| elseif (!$searchRequest->fromDate && $searchRequest->toDate) | |
| { | |
| $sql.= " AND ts.valTo <= '$searchRequest->toDate'"; | |
| } | |
| $sql.= " ORDER BY ts.valFrom DESC ,sa.studentName ASC , admit_time DESC"; | |
| try { | |
| $studentDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentDetails; | |
| } | |
| /** | |
| * get student transportation details | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentTransportationDetails($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql= "SELECT studentName, studentAccount, studentAddress, studentPhone,rollNo, sa.myImage, dept.deptName, bth.batchName, sem.semName, studentEmail, hosteller, valFrom, valTo, tb.busID, busName, br.busrouteID, destinationName, bp.bpID, bp.bp_kms, bp.bpName, noOfTrips, tbr.brbpID, ts.adminRemarks, tbr.boardingTime, tbr.dropTime, ts.studentFare, ts.studentTrips from studentaccount sa inner join department dept ON dept.deptID=sa.deptID inner join batches bth ON bth.batchID=sa.batchID inner join semesters sem ON sem.semID=bth.semID inner join transportation_students ts on sa.studentID = ts.studentID inner join transportation_busroute br on ts.busrouteID = br.busrouteID INNER JOIN transportation_triptype ttt ON ttt.triptypeID = br.triptypeID inner join transportation_brbp_relation tbr on tbr.brbpID = ts.brbpID inner join transportation_boardingpoint bp on bp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = br.busID where ts.studentID= $studentId"; | |
| try { | |
| $studentBusDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentBusDetails; | |
| } | |
| /** | |
| * get student one way fare | |
| * @param int $studentId | |
| * @param int $brbpId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentOneWayFare($studentId, $brbpId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $brbpId = $this->realEscapeString($brbpId); | |
| $sql = "select onewayFare from transportation_fareCharge fc inner join batches bat on bat.batchStartYear = fc.batchStartYear inner join studentaccount sa on sa.batchID = bat.batchID where studentID = $studentId and brbpID = $brbpId"; | |
| try { | |
| $oneWayFare = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $oneWayFare; | |
| } | |
| /** | |
| * Update tansportation student | |
| * @param TransportationStudent $transportationStudent | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateTransportationStudents($transportationStudent) | |
| { | |
| $transportationStudent = $this->realEscapeObject($transportationStudent); | |
| $sql = "update transportation_students set brbpID = $transportationStudent->brbpID , studentTrips = $transportationStudent->noOfTrips, valFrom = '$transportationStudent->valFrom', valTo = '$transportationStudent->valTo', studentFare = $transportationStudent->busfee, adminRemarks = '$transportationStudent->remarks', busrouteID = $transportationStudent->busrouteID where studentID = $transportationStudent->studentId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get brbp relation | |
| * @param int $busRouteId | |
| * @param int $bpId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function getBusRoutBoardingPointRelation($busRouteId, $bpId) | |
| { | |
| $busRouteId = $this->realEscapeString($busRouteId); | |
| $bpId = $this->realEscapeString($bpId); | |
| $sql = "select brbpID from transportation_brbp_relation where busrouteID = $busRouteId and bpID = $bpId"; | |
| try { | |
| $brbpId = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $brbpId; | |
| } | |
| /** | |
| * get staff one way fare | |
| * @param int $batchStartYear | |
| * @param int $brbpId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStaffOneWayFare($batchStartYear, $brbpId) | |
| { | |
| $batchStartYear = $this->realEscapeString($batchStartYear); | |
| $brbpId = $this->realEscapeString($brbpId); | |
| $sql = "SELECT onewayFare FROM transportation_fareCharge WHERE batchStartYear = $batchStartYear AND brbpID = $brbpId"; | |
| try { | |
| $onWayFare = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $onWayFare; | |
| } | |
| /** | |
| * create staff transportation request | |
| * @param TransportationStaff $transportStaff | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createStaffTransportRequest($transportStaff) | |
| { | |
| $transportStaff = $this->realEscapeObject($transportStaff); | |
| $sql = "INSERT INTO transportation_staff_request (staffId, busRouteId, brbpId, noofTrips, valFrom, valTo, busfee, staffRemarks, levelPriority) VALUE ($transportStaff->staffId, $transportStaff->busrouteId, $transportStaff->brbpId, $transportStaff->noOfTrips, '$transportStaff->valFrom', '$transportStaff->valTo', $transportStaff->busfee, '$transportStaff->remarks', '$transportStaff->levelPriority')"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| //call hook for sending notification to approvers | |
| } | |
| /** | |
| * get staff transport request | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStaffTransportRequest($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "select sa.staffID, staffName, staffAccount, staffAddress, staffPhone, staffEmail, id, tsr.busrouteId, tsr.brbpId, valFrom, valTo, destinationName, bpName, busName, staffRemarks, tb.busID, noofTrips, sanctioned from staffaccounts sa inner join transportation_staff_request tsr on sa.staffID = tsr.staffId inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID where tsr.staffId = $staffId"; | |
| try { | |
| $staffRequestDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffRequestDetails; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $id | |
| * @return void | |
| */ | |
| public function getStaffTransportRequestById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "select sa.staffID, staffName, staffAccount, staffAddress, staffPhone, staffEmail, id, tsr.busrouteId, tsr.brbpId, valFrom, valTo, destinationName, bpName, busName, staffRemarks, tb.busID, noofTrips, sanctioned, tsr.levelPriority from staffaccounts sa inner join transportation_staff_request tsr on sa.staffID = tsr.staffId inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID where tsr.id = $id"; | |
| try { | |
| $staffRequestDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffRequestDetails; | |
| } | |
| /** | |
| * delete staff transport request | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteStaffTransportRequest($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "DELETE FROM transportation_staff_request WHERE id = $id"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *get all staff transport request | |
| * @param TransportationStaff $transportStaff | |
| * @throws ProfessionalException | |
| * @return \com\linways\core\ams\professional\response\TransportBuswiseStaffResponse | |
| */ | |
| public function getAllTransportationRequestedStaffs($transportStaff) | |
| { | |
| $transportStaffResponse = new TransportBuswiseStaffResponse(); | |
| $transportStaff = $this->realEscapeObject($transportStaff); | |
| $sql_staff = ""; | |
| $sql_level = ""; | |
| if($transportStaff->staffName) | |
| { | |
| $sql_staff = " AND staffName like '%$transportStaff->staffName%'"; | |
| } | |
| if($transportStaff->levelPriority != null) | |
| { | |
| $sql_level = " AND levelPriority = $transportStaff->levelPriority"; | |
| } | |
| $sql = "select sa.staffID, staffName, staffEmail, staffPhone, sa.deptID, deptName, id, tsr.busrouteId, tsr.brbpId, valFrom, valTo, destinationName, bpName, busName from transportation_staff_request tsr inner join staffaccounts sa on sa.staffID = tsr.staffId inner join department dep on sa.deptID = dep.deptID inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID where sanctioned = 0 $sql_staff $sql_level order by valFrom limit $transportStaff->startIndex, $transportStaff->endIndex"; | |
| $sql_count = "SELECT count(staffId) AS count FROM transportation_staff_request where sanctioned = 0 $sql_level"; | |
| try { | |
| $transportStaffResponse->buswiseStaffList = $this->executeQueryForList($sql); | |
| $transportStaffResponse->totalRecords = $this->executeQueryForObject($sql_count)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $transportStaffResponse; | |
| } | |
| /** | |
| * get staff transport request by request id | |
| * @param int $reqId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStaffTransportRequestByRequestId($reqId) | |
| { | |
| $reqId = $this->realEscapeString($reqId); | |
| $sql = "select sa.staffID, staffName, staffAccount, staffAddress, staffPhone, staffEmail, dep.deptID, deptName, id, tsr.busrouteId, tsr.brbpId, valFrom, valTo, destinationName, bpName, busName, staffRemarks, tb.busID, noofTrips, tbp.bpID, busfee from staffaccounts sa inner join department dep on dep.deptID = sa.deptID inner join transportation_staff_request tsr on sa.staffID = tsr.staffId inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID where id = $reqId"; | |
| try { | |
| $staffRequestDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffRequestDetails; | |
| } | |
| /** | |
| * create staff transport history | |
| * @param TransportationStaff $transportStaff | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function createStaffTransportHistory($transportStaff) | |
| { | |
| $transportStaff = $this->realEscapeObject($transportStaff); | |
| $sql = "INSERT INTO transportation_staffs_history (brbpId, staffTrips, valFrom, valTo, staffFare) VALUE($transportStaff->brbpId, $transportStaff->staffId, '$transportStaff->valFrom', '$transportStaff->valTo', $transportStaff->busfee)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete transport admitted staff | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function deleteTransportationStaff($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "DELETE FROM transportation_staffs WHERE staffId = $staffId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete transport admitted staff | |
| * @param int $transportationId | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function deleteTransportationStaffById($Id) | |
| { | |
| $staffId =(int) $this->realEscapeString($Id); | |
| $sql = "DELETE FROM transportation_staffs WHERE id = $Id"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create transport admitted staff | |
| * @param TransportationStaff $transportationStaff | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function createTransportationStaff($transportationStaff) | |
| { | |
| $transportationStaff = $this->realEscapeObject($transportationStaff); | |
| $sql = "INSERT INTO transportation_staffs (staffID, brbpID, staffTrips, valFrom, valTo, staffFare) VALUE ($transportationStaff->staffId, $transportationStaff->brbpId, $transportationStaff->noOfTrips, '$transportationStaff->valFrom', '$transportationStaff->valTo', $transportationStaff->busfee)"; | |
| try { | |
| $this->createStaffTransportHistory($transportationStaff); | |
| if(!CommonService::getInstance()->getSettings(SettingsConstents::TRANSPORT, SettingsConstents::BYPASS_MULTIPLE_TRANSPORT_REQUEST_VALIDATION_ON_THE_SAME_PERIOD)){ | |
| $this->deleteTransportationStaff($transportationStaff->staffId); | |
| } | |
| $this->executeQueryForObject($sql); | |
| $this->updateTransportationStaffRequest($transportationStaff->id); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * update transport admitted staff | |
| * @param int $reqId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateTransportationStaffRequest($reqId) | |
| { | |
| $reqId = $this->realEscapeString($reqId); | |
| $sql = "UPDATE transportation_staff_request SET sanctioned = 1 WHERE id= $reqId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get transport admitted staff | |
| * @param TransportBuswiseRequest $busWiseStaffReq | |
| * @throws ProfessionalException | |
| * @return \com\linways\core\ams\professional\response\TransportBuswiseStaffResponse | |
| */ | |
| public function getTransportionAdmittedStaffs($busWiseStaffReq) | |
| { | |
| $busWiseStaffReq = $this->realEscapeObject($busWiseStaffReq); | |
| $busWiseStaffList = new TransportBuswiseStaffResponse(); | |
| if($busWiseStaffReq->staffName) | |
| { | |
| $sql_staff = "WHERE staffName like '%$busWiseStaffReq->staffName%'"; | |
| } | |
| $sql = "select sa.staffID, staffName, staffAccount, staffAddress, staffPhone, staffEmail, dep.deptID, deptName, tbr.busrouteId, tsr.brbpID, valFrom, valTo, destinationName, bpName, busName, tb.busID, tbp.bpID, staffFare, staffTrips, tsr.id as tsrId from staffaccounts sa inner join department dep on dep.deptID = sa.deptID inner join transportation_staffs tsr on sa.staffID = tsr.staffId inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID $sql_staff limit $busWiseStaffReq->startIndex, $busWiseStaffReq->endIndex"; | |
| $sql_count = "select count(staffID) as count from transportation_staffs"; | |
| try { | |
| $busWiseStaffList->buswiseStaffList = $this->executeQueryForList($sql); | |
| $busWiseStaffList->totalRecords = $this->executeQueryForObject($sql_count)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $busWiseStaffList; | |
| } | |
| /** | |
| * get transport admitted staff by staffId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getTransportAdmittedStaffByStaffId($staffId, $brbpID = NULL) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $brbpID = $this->realEscapeString($brbpID); | |
| $sql = "SELECT sa.staffID, | |
| staffName, | |
| staffAccount, | |
| staffAddress, | |
| staffPhone, | |
| staffEmail, | |
| myImage, | |
| dep.deptID, | |
| deptName, | |
| tbr.busrouteId, | |
| tsr.brbpID, | |
| valFrom, | |
| valTo, | |
| destinationName, | |
| bpName, | |
| busName, | |
| tb.busID, | |
| tbp.bpID, | |
| staffFare, | |
| staffTrips | |
| from staffaccounts sa | |
| inner join department dep on dep.deptID = sa.deptID | |
| inner join transportation_staffs tsr on sa.staffID = tsr.staffId | |
| inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID | |
| inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID | |
| inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID | |
| inner join transportation_bus tb on tb.busID = tbrt.busID | |
| where sa.staffID = $staffId"; | |
| if($brbpID){ | |
| $sql .= " AND tsr.brbpID = $brbpID"; | |
| } | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sql); | |
| $profilePicture = StaffService::getInstance()->getStaffProfilePic($staffId); | |
| $imagePath = $profilePicture->docpath; | |
| $staffDetails->myImage = $imagePath; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $tsrId | |
| * @return void | |
| */ | |
| public function getTransportAdmittedStaffByTransportationsStaffId($tsrId) | |
| { | |
| $tsrId = $this->realEscapeString($tsrId); | |
| $sql = "SELECT sa.staffID, | |
| staffName, | |
| staffAccount, | |
| staffAddress, | |
| staffPhone, | |
| staffEmail, | |
| myImage, | |
| dep.deptID, | |
| deptName, | |
| tbr.busrouteId, | |
| tsr.brbpID, | |
| valFrom, | |
| valTo, | |
| destinationName, | |
| bpName, | |
| busName, | |
| tb.busID, | |
| tbp.bpID, | |
| staffFare, | |
| staffTrips, | |
| tsr.id as tsrId | |
| from staffaccounts sa | |
| inner join department dep on dep.deptID = sa.deptID | |
| inner join transportation_staffs tsr on sa.staffID = tsr.staffId | |
| inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID | |
| inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID | |
| inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID | |
| inner join transportation_bus tb on tb.busID = tbrt.busID where tsr.id = $tsrId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sql); | |
| $profilePicture = StaffService::getInstance()->getStaffProfilePic($staffDetails->staffID); | |
| $imagePath = $profilePicture->docpath; | |
| $staffDetails->myImage = $imagePath; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * update transport admitted staff | |
| * @param TransportationStaff $transportationStaff | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function updateTransportationStaff($transportationStaff) | |
| { | |
| $transportationStaff = $this->realEscapeObject($transportationStaff); | |
| $sql = "UPDATE transportation_staffs SET brbpID = $transportationStaff->brbpId, staffTrips = $transportationStaff->noOfTrips, valFrom = '$transportationStaff->valFrom', valTo = '$transportationStaff->valTo', staffFare = $transportationStaff->busfee WHERE staffID = $transportationStaff->staffId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $transportationStaff | |
| * @return void | |
| */ | |
| public function updateTransportationStaffById($transportationStaff) | |
| { | |
| $transportationStaff = $this->realEscapeObject($transportationStaff); | |
| $sql = "UPDATE transportation_staffs SET brbpID = $transportationStaff->brbpId, staffTrips = $transportationStaff->noOfTrips, valFrom = '$transportationStaff->valFrom', valTo = '$transportationStaff->valTo', staffFare = $transportationStaff->busfee WHERE id = $transportationStaff->tsrId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get transport admitted staff by staff IDs | |
| * @param array $staffIds | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getTransportAdmittedStaffByStaffIds($staffIds) | |
| { | |
| $staffIds = implode(', ', $staffIds); | |
| $staffIds = $this->realEscapeString($staffIds); | |
| $result = []; | |
| $sql = "SELECT sa.staffID, | |
| staffName, | |
| staffAccount, | |
| staffAddress, | |
| staffPhone, | |
| staffEmail, | |
| myImage, | |
| dep.deptID, | |
| deptName, | |
| tbr.busrouteId, | |
| tsr.brbpID, | |
| valFrom, | |
| valTo, | |
| destinationName, | |
| bpName, | |
| busName, | |
| tb.busID, | |
| tbp.bpID, | |
| staffFare, | |
| staffTrips | |
| from staffaccounts sa | |
| inner join department dep on dep.deptID = sa.deptID | |
| inner join transportation_staffs tsr on sa.staffID = tsr.staffId | |
| inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID | |
| inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID | |
| inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID | |
| inner join transportation_bus tb on tb.busID = tbrt.busID where sa.staffID in ($staffIds)"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| foreach ($staffDetails as $staffDetail) { | |
| if($staffDetail->staffID) | |
| { | |
| $profilePicture = StaffService::getInstance()->getStaffProfilePic($staffDetail->staffID); | |
| $imagePath = $profilePicture->docpath; | |
| $staffDetail->myImage = $imagePath; | |
| $result[] = $staffDetail; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $tsrIds | |
| * @return void | |
| */ | |
| public function getTransportAdmittedStaffByTransportationStaffIds($tsrIds) | |
| { | |
| $tsrIds = implode(', ', $tsrIds); | |
| $tsrIds = $this->realEscapeString($tsrIds); | |
| $result = []; | |
| $sql = "select sa.staffID, staffName, staffAccount, staffAddress, staffPhone, staffEmail, myImage,dep.deptID, deptName, tbr.busrouteId, tsr.brbpID, valFrom, valTo, destinationName, bpName, busName, tb.busID, tbp.bpID, staffFare, staffTrips from staffaccounts sa inner join department dep on dep.deptID = sa.deptID inner join transportation_staffs tsr on sa.staffID = tsr.staffId inner join transportation_brbp_relation tbr on tsr.brbpID = tbr.brbpID inner join transportation_busroute tbrt on tbr.busrouteID = tbrt.busrouteID inner join transportation_boardingpoint tbp on tbp.bpID = tbr.bpID inner join transportation_bus tb on tb.busID = tbrt.busID where tsr.id in ($tsrIds)"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| foreach ($staffDetails as $staffDetail) { | |
| if($staffDetail->staffID) | |
| { | |
| $profilePicture = StaffService::getInstance()->getStaffProfilePic($staffDetail->staffID); | |
| $imagePath = $profilePicture->docpath; | |
| $staffDetail->myImage = $imagePath; | |
| $result[] = $staffDetail; | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Create Transport Staff Approval Group | |
| * @param string $levelName | |
| * @param int $levelPriority | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function createTransportStaffApprovalGroup($levelName, $levelPriority) | |
| { | |
| $levelName = $this->realEscapeString($levelName); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $sql = "INSERT INTO transport_staff_approval_group (levelName, levelPriority) VALUE ('$levelName', $levelPriority)"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Transport Staff Approval Group | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllTransportStaffApprovalGroup() | |
| { | |
| $sql = "SELECT id, levelName, levelPriority FROM transport_staff_approval_group"; | |
| try { | |
| $approvalGrpDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $approvalGrpDetails; | |
| } | |
| /** | |
| * Get Transport Staff Approval Group by id | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getTransportStaffApprovalGroupById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "SELECT levelName, levelPriority FROM transport_staff_approval_group WHERE id = $id"; | |
| try { | |
| $approvalGrpDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $approvalGrpDetails; | |
| } | |
| /** | |
| * Upadte Transport Staff Approval Group | |
| * @param int $id | |
| * @param string $levelName | |
| * @param int $levelPriority | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateTransportStaffApprovalGroup($id, $levelName, $levelPriority) | |
| { | |
| $levelName = $this->realEscapeString($levelName); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $id = $this->realEscapeString($id); | |
| $sql = "UPDATE transport_staff_approval_group SET levelName = '$levelName', levelPriority=$levelPriority WHERE id = $id"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete Transport approval group | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteTransportStaffApprovalGroupById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "DELETE FROM transport_staff_approval_group WHERE id = $id"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create Transportation Staff User Group | |
| * @param int $staffIds | |
| * @param int $deptId | |
| * @param int $grpId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function createTransportationStaffUserGroup($staffIds, $deptId, $grpId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $deptId = $this->realEscapeString($deptId); | |
| $grpId = $this->realEscapeString($grpId); | |
| $sql_values = ""; | |
| if(is_array($staffIds)) | |
| { | |
| foreach ($staffIds as $staffId) | |
| { | |
| $sql_values.= " ($staffId, $deptId, $grpId), "; | |
| } | |
| $sql_values = rtrim($sql_values, ", "); | |
| } | |
| else | |
| { | |
| $sql_values = " ($staffId, $deptId, $grpId)"; | |
| } | |
| $sql = "INSERT INTO transport_staff_user_group (staffId, deptId, staffApprovalGroupId) VALUES $sql_values"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * et Transportation Staff User Group | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAllTransportationStaffUserGroup() | |
| { | |
| $sql = "select group_concat(ug.staffId) as ids, group_concat(staffName SEPARATOR ', ') as staffNames, ug.deptId, dep.deptName, staffApprovalGroupId, levelName from transport_staff_user_group ug inner join transport_staff_approval_group ag on ug.staffApprovalGroupId= ag.id left join department dep on dep.deptID = ug.deptId inner join staffaccounts sa on sa.staffID = ug.staffId group by deptId, staffApprovalGroupId;"; | |
| try { | |
| $userGrpDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $userGrpDetails; | |
| } | |
| /** | |
| * Delete Transportation Staff User Group | |
| * @param int $grpId | |
| * @param int $deptId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteTransportationStaffUserGroup($grpId, $deptId) | |
| { | |
| $grpId = $this->realEscapeString($grpId); | |
| $deptId = $this->realEscapeString($deptId); | |
| $sql = "DELETE FROM transport_staff_user_group WHERE staffApprovalGroupId = $grpId and deptId = $deptId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get Transportation Staff User Group by staffId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getTransportationStaffUserGroupByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "select ug.staffId, levelPriority, ug.deptId, staffApprovalGroupId, levelName from transport_staff_user_group ug inner join transport_staff_approval_group ag on ug.staffApprovalGroupId= ag.id where find_in_set($staffId,ug.staffId)"; | |
| try { | |
| $userGrpDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $userGrpDetails; | |
| } | |
| /** | |
| * get next levelpriority | |
| * @param int $deptId | |
| * @param int $levelPriority | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getNextLvelPriorities($deptId, $levelPriority) | |
| { | |
| $deptId = $this->realEscapeString($deptId); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $sql = "select staffId, deptId, staffApprovalGroupId, levelName, min(levelPriority) as nextPriority from transport_staff_user_group ug inner join transport_staff_approval_group ag on ag.id = ug.staffApprovalGroupId where (deptId = $deptId or deptId = 0) and levelPriority > $levelPriority order by levelPriority"; | |
| try { | |
| $levelDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $levelDetails; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $deptId | |
| * @param [type] $levelPriority | |
| * @return void | |
| */ | |
| public function getNextLevelStaffDetailsUsingPriority($deptId, $levelPriority) | |
| { | |
| $deptId = $this->realEscapeString($deptId); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $levelDetails = []; | |
| $sql = "select min(levelPriority) as nextPriority from transport_staff_user_group ug inner join transport_staff_approval_group ag on ag.id = ug.staffApprovalGroupId where (deptId = $deptId or deptId = 0) and levelPriority > $levelPriority"; | |
| try { | |
| $nextLevelPriority = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if(empty($nextLevelPriority) || empty($nextLevelPriority->nextPriority)){ | |
| return null; | |
| } | |
| $nextLevelPriority = $nextLevelPriority->nextPriority; | |
| $sql = "select staffId, deptId, staffApprovalGroupId, levelName, $nextLevelPriority as nextPriority from transport_staff_user_group ug inner join transport_staff_approval_group ag on ag.id = ug.staffApprovalGroupId where (deptId = $deptId or deptId = 0) and levelPriority = $nextLevelPriority order by levelPriority"; | |
| try { | |
| $levelDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $levelDetails; | |
| } | |
| public function getCurrentLevelStaffDetailsUsingPriority($deptId, $levelPriority) | |
| { | |
| $deptId = $this->realEscapeString($deptId); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $levelDetails = []; | |
| if(empty($levelPriority)){ | |
| return null; | |
| } | |
| $sql = "select staffId, deptId, staffApprovalGroupId, levelName, $levelPriority as nextPriority from transport_staff_user_group ug inner join transport_staff_approval_group ag on ag.id = ug.staffApprovalGroupId where (deptId = $deptId or deptId = 0) and levelPriority = $levelPriority order by levelPriority"; | |
| try { | |
| $levelDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $levelDetails; | |
| } | |
| /** | |
| * update staff level priority | |
| * @param int $levelPriority | |
| * @param int $reqId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateStaffTransportLevelPriority($levelPriority, $reqId) | |
| { | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $reqId = $this->realEscapeString($reqId); | |
| $sql = "UPDATE transportation_staff_request SET levelPriority = $levelPriority WHERE id=$reqId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * create staff approval | |
| * @param TransportStaffApproval $staffTransportApproval | |
| * @throws ProfessionalException | |
| * @return unknown | |
| */ | |
| public function createStaffTransportApproval($staffTransportApproval) | |
| { | |
| $staffTransportApproval = $this->realEscapeObject($staffTransportApproval); | |
| $sql = "INSERT INTO transport_staff_approval_table (staffRequestId, levelPriority, comments, commentedBy, commentedDate) VALUE ($staffTransportApproval->staffReqId, $staffTransportApproval->LevelPriority, '$staffTransportApproval->comments', $staffTransportApproval->createdBy, UTC_TIMESTAMP())"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get boarding points by route id | |
| * @param int $busRouteId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBoardingPointDetailsByBusRouteId($busRouteId) | |
| { | |
| $busRouteId = $this->realEscapeString($busRouteId); | |
| $sql = "select brbpID, busrouteID, tbr.bpID, boardingTime, dropTime, bpName, bp_kms from transportation_brbp_relation tbr inner join transportation_boardingpoint tb on tbr.bpID = tb.bpID where busrouteID = $busRouteId"; | |
| try { | |
| $boardingPointDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $boardingPointDetails; | |
| } | |
| /** | |
| * search boarding point | |
| * @param GetBusBoardingPointSearchRequest $getBusBoardingPointSearchRequest | |
| * @throws ProfessionalException | |
| * @return \com\linways\core\ams\professional\response\GetBusBoardingPointSearchResponse | |
| */ | |
| public function getBoardingPointsByBoardingPointRequest($getBusBoardingPointSearchRequest) | |
| { | |
| $getBusBoardingPointSearchResponse = new GetBusBoardingPointSearchResponse(); | |
| $getBusBoardingPointSearchRequest = $this->realEscapeObject($getBusBoardingPointSearchRequest); | |
| $sql = "SELECT | |
| bus.busID, bp.bpID, br.busrouteID, brbp.brbpID, bp.bpName, br.destinationName, bus.busName | |
| FROM | |
| transportation_brbp_relation brbp | |
| INNER JOIN | |
| transportation_busroute br ON brbp.busrouteID = br.busrouteID | |
| INNER JOIN | |
| transportation_boardingpoint bp ON brbp.bpID = bp.bpID | |
| INNER JOIN | |
| transportation_bus bus on bus.busID = br.busID where brbp.brbpID is not null "; | |
| $sql_count = "SELECT | |
| count(brbp.brbpID) as count | |
| FROM | |
| transportation_brbp_relation brbp | |
| INNER JOIN | |
| transportation_busroute br ON brbp.busrouteID = br.busrouteID | |
| INNER JOIN | |
| transportation_boardingpoint bp ON brbp.bpID = bp.bpID | |
| INNER JOIN | |
| transportation_bus bus on bus.busID = br.busID where brbp.brbpID is not null "; | |
| $condition = ""; | |
| if(!empty($getBusBoardingPointSearchRequest->busRouteId)) | |
| { | |
| $condition.= " and br.busRouteID = '$getBusBoardingPointSearchRequest->busRouteId'"; | |
| } | |
| if(!empty($getBusBoardingPointSearchRequest->busId)) | |
| { | |
| $condition.= " and bus.busID = '$getBusBoardingPointSearchRequest->busId'"; | |
| } | |
| if(!empty($getBusBoardingPointSearchRequest->boardingPointId)) | |
| { | |
| $condition.= " and bp.bpID = '$getBusBoardingPointSearchRequest->boardingPointId'"; | |
| } | |
| $sql.=$condition; | |
| $sql_count.=$condition; | |
| $sql.=" limit $getBusBoardingPointSearchRequest->startIndex, $getBusBoardingPointSearchRequest->endIndex"; | |
| try { | |
| $getBusBoardingPointSearchResponse->boardingPointList = $this->executeQueryForList($sql); | |
| $getBusBoardingPointSearchResponse->totalRecords = $this->executeQueryForObject($sql_count)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $getBusBoardingPointSearchResponse; | |
| } | |
| /** | |
| * Delete transport admitted student detail - update | |
| * hosteller and transport flag | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteTransportAdmittedStudents($studentId, $valFrom, $valTo) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "DELETE FROM transportation_students WHERE studentID = $studentId"; | |
| $sql_updateReq = "UPDATE transport_student_request SET sanctioned=2 WHERE studentaccount_id = $studentId and valFrom= $valFrom and valTo = $valTo"; | |
| try { | |
| $this->executeQuery($sql); | |
| $this->executeQuery($sql_updateReq); | |
| $sql_hostelReq = "SELECT sanctioned FROM hostel_student_request WHERE studentaccount_id = $studentId"; | |
| //check if admitted to hostel | |
| $appliedHostel = $this->executeQueryForObject($sql_hostelReq); | |
| if(!empty($appliedHostel)) | |
| { | |
| $sql_hosteladmitted = "SELECT sanctioned FROM hostel_student_request WHERE studentaccount_id = $studentId and sanctioned= 1"; | |
| $admitted = $this->executeQueryForObject($sql_hosteladmitted)->sanctioned; | |
| if(!$admitted) | |
| { | |
| $sql_update = "UPDATE studentaccount SET transport = 0, hosteller = 1 WHERE studentID = $studentId"; | |
| } | |
| else | |
| { | |
| $sql_update = "UPDATE studentaccount SET transport = 2, hosteller = 1 WHERE studentID = $studentId"; | |
| } | |
| } | |
| else | |
| { | |
| $sql_update = "UPDATE studentaccount SET transport = 0, hosteller = 0 WHERE studentID = $studentId"; | |
| } | |
| return $this->executeQuery($sql_update); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete transport student request | |
| * @param int $studentId | |
| * @param int $valFrom | |
| * @param int $valTo | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function deleteTransportationStudentRequest($studentId, $valFrom, $valTo) | |
| { | |
| $sql = "DELETE FROM transport_student_request WHERE studentaccount_id = $studentId and valFrom= $valFrom and valTo = $valTo"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getBusTrips( $busRouteId ) { | |
| $busRouteId = $this->realEscapeString($busRouteId); | |
| $noOfTrips = null; | |
| $sql = "SELECT triptype.noOfTrips FROM transportation_triptype triptype INNER JOIN transportation_busroute busroute ON (triptype.triptypeID = busroute.triptypeID) WHERE busroute.busrouteID = ".$busRouteId ; | |
| try { | |
| $noOfTrips = $this->executeQueryForObject($sql)->noOfTrips; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $noOfTrips; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $id | |
| * @return void | |
| */ | |
| public function getStudentTransportRequestDetailsById($id){ | |
| $id = $this->realEscapeString($id); | |
| $requestDetails = NULL; | |
| $sql = "SELECT | |
| tsr.id AS transportRequestId, | |
| sa.studentID AS studentId, | |
| sa.studentName, | |
| dep.deptID as deptId, | |
| dep.deptName, | |
| dep.departmentDesc, | |
| bat.batchID as batchId, | |
| bat.batchName, | |
| tbrt.destinationName, | |
| tb.busName, | |
| tsr.studentRemarks, | |
| tsr.noofTrip, | |
| tsr.valFrom, | |
| tsr.valTo, | |
| tsr.levelPriority | |
| FROM | |
| studentaccount sa | |
| INNER JOIN | |
| department dep ON dep.deptID = sa.deptID | |
| INNER JOIN | |
| batches bat ON bat.deptID = dep.deptID | |
| AND sa.batchID = bat.batchID | |
| INNER JOIN | |
| transport_student_request tsr ON sa.studentID = tsr.studentaccount_id | |
| INNER JOIN | |
| transportation_brbp_relation tbr ON tsr.brbpID = tbr.brbpID | |
| INNER JOIN | |
| transportation_busroute tbrt ON tbr.busrouteID = tbrt.busrouteID | |
| INNER JOIN | |
| transportation_boardingpoint tbp ON tbp.bpID = tbr.bpID | |
| INNER JOIN | |
| transportation_bus tb ON tb.busID = tbrt.busID | |
| WHERE | |
| tsr.id = $id"; | |
| try { | |
| $requestDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $requestDetails; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $deptId | |
| * @param [type] $batchId | |
| * @param [type] $levelPriority | |
| * @return void | |
| */ | |
| public function getCurrentLevelApproversForStudentTransport($deptId, $batchId, $levelPriority){ | |
| $deptId = $this->realEscapeString($deptId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $staffDetails = []; | |
| $sql = "SELECT | |
| ug.staffaccount_id AS staffId, | |
| tg.levelPriority, | |
| tg.levelName | |
| FROM | |
| transport_approval_group tg | |
| INNER JOIN | |
| transport_user_group ug ON tg.id = ug.transport_approval_group_id | |
| WHERE | |
| (ug.department_id = '$deptId' | |
| OR ug.department_id = 0) | |
| AND (ug.batches_id = '$batchId' OR ug.batches_id = 0) | |
| AND tg.levelPriority = '$levelPriority'"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * Undocumented function | |
| * | |
| * @param [type] $deptId | |
| * @param [type] $batchId | |
| * @param [type] $levelPriority | |
| * @return void | |
| */ | |
| public function getNextLevelApproversForStudentTransport($deptId, $batchId, $levelPriority){ | |
| $deptId = $this->realEscapeString($deptId); | |
| $batchId = $this->realEscapeString($batchId); | |
| $levelPriority = $this->realEscapeString($levelPriority); | |
| $staffDetails = []; | |
| $sql = "SELECT | |
| min(tg.levelPriority) as levelPriority | |
| FROM | |
| transport_approval_group tg | |
| INNER JOIN | |
| transport_user_group ug ON tg.id = ug.transport_approval_group_id | |
| WHERE | |
| (ug.department_id = '$deptId' | |
| OR ug.department_id = 0) | |
| AND (ug.batches_id = '$batchId' OR ug.batches_id = 0) | |
| AND tg.levelPriority > '$levelPriority' ORDER BY tg.levelPriority"; | |
| try { | |
| $nextLevelPriority = $this->executeQueryForObject($sql)->levelPriority; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if(empty($nextLevelPriority)){ | |
| return null; | |
| } | |
| $staffDetails = $this->getCurrentLevelApproversForStudentTransport($deptId, $batchId, $nextLevelPriority); | |
| return $staffDetails; | |
| } | |
| } | |
| ?> |