Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 62 |
CRAP | |
0.00% |
0 / 1047 |
| LibraryService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 62 |
26732.00 | |
0.00% |
0 / 1047 |
| __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 |
|||
| addLibraryDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| updateLibraryDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| deleteLibraryDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getLibraryDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getLibraryDivisionById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getLibraryDivisionNotSetDepartments | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 23 |
|||
| isDivisionAssignedToBooks | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 17 |
|||
| getBookRenewalDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getLibraryStaffDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getLibraryStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| UpdateIssueLibraryBook | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| setDefaultAttachment | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getAttachments | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStudentLibraryBooksIssuedDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 23 |
|||
| blockOrUnblockStudentFromLibrary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getAllStudentsBlockedFromLibrary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| addLibraryStaffDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| removeLibraryStaffDivisionByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| removeLibraryStaffDivisionByDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getLibraryStaffDivisionByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| setPrimaryLibraryStaffDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| unsetPrimaryLibraryStaffDivision | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getBookDetailsByStudentId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getBookDetailsByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getLibraryStatisticsReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 38 |
|||
| addLibraryAttendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateLibraryAttendanceOutTime | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| updateMissedLibraryAttendanceOutTime | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 22 |
|||
| getStudentLibraryAttendanceIn | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStudentLibraryAttendanceLastIn | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getStudentLibraryAttendanceReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| addLibraryReportSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| updateLibraryReportSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getLibraryReportSettingsByName | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getLibraryReportSettings | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStaffLibraryBooksIssuedDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 22 |
|||
| updateDocumentViewCount | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| blockOrUnblockStaffFromLibrary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getViewCountOfDocumentById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getAllStaffBlockedFromLibrary | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getLastGeneratedPeriodicalAccNoByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getLibraryDepartments | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getLibrarySubjects | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getLibraryLanguages | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| getLibraryStatisticsReportRackWise | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 38 |
|||
| getLibraryStatisticsReportRowWise | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 38 |
|||
| searchLibraryBook | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 84 |
|||
| searchLibraryBookOPAC | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 66 |
|||
| getBookDetailsById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 52 |
|||
| getIssuedBookStudentDetailsByBookId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getIssuedBookStaffDetailsByBookId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteBookById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| addUpdateEJournalViewCount | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| checkIfUserViewCountRecordExists | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| searchPeriodicals | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 26 |
|||
| calculatingStudentReturnDate | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| calculateStudentTotalFine | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| getFine | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 21 |
|||
| searchEjournals | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 22 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\dto\LibraryDivision; | |
| use com\linways\core\ams\professional\dto\AccessionPrefix; | |
| use com\linways\core\ams\professional\response\GetStudentsBlockedFromLibraryResponse; | |
| use com\linways\core\ams\professional\request\GetStudentsBlockedFromLibraryRequest; | |
| use com\linways\core\ams\professional\dto\LibraryStaffDivision; | |
| use com\linways\core\ams\professional\dto\LibraryAttendanceRegister; | |
| use com\linways\core\ams\professional\dto\LibraryReportSettings; | |
| use com\linways\core\ams\professional\request\GetStaffBlockedFromLibraryRequest; | |
| use com\linways\core\ams\professional\response\GetStaffBlockedFromLibraryResponse; | |
| use com\linways\core\ams\professional\dto\LibraryDocumentReview; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| use com\linways\core\ams\professional\request\LibraryEjournalSearchRequest; | |
| class LibraryService 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; | |
| } | |
| /** | |
| * add library division | |
| * @param LibraryDivision $libraryDivision | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function addLibraryDivision($libraryDivision) | |
| { | |
| $sql="INSERT INTO libraryDivision (name, code, deptIds, createdBy, updatedBy, createdDate, updatedDate) VALUES ('$libraryDivision->name', '$libraryDivision->code', '$libraryDivision->deptIds', $libraryDivision->createdBy, $libraryDivision->updatedBy, utc_timestamp(), utc_timestamp())"; | |
| try | |
| { | |
| $divisionId=$this->executeQueryForObject($sql,true); | |
| $accessionPrefix = new AccessionPrefix(); | |
| $accessionPrefix->divisionId=$divisionId; | |
| $accessionPrefix->prefix=$libraryDivision->prefix; | |
| $accessionPrefix->createdBy=$libraryDivision->createdBy; | |
| $accessionPrefix->updatedBy=$libraryDivision->updatedBy; | |
| BookService::getInstance()->addAccessionNumberPrefix($accessionPrefix); | |
| return true; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update library division | |
| * @param LibraryDivision $libraryDivision | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function updateLibraryDivision($libraryDivision) | |
| { | |
| $sql="UPDATE libraryDivision SET name='$libraryDivision->name', code='$libraryDivision->code', deptIds='$libraryDivision->deptIds', updatedBy=$libraryDivision->updatedBy, updatedDate=utc_timestamp() WHERE id=$libraryDivision->id"; | |
| try | |
| { | |
| $this->executeQueryForObject($sql,true); | |
| BookService::getInstance()->updateAccessionNumberPrefix($libraryDivision->prefix, $libraryDivision->id); | |
| return true; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * deleteLibraryDivision | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return boolean | |
| */ | |
| public function deleteLibraryDivision($id) | |
| { | |
| $sql="DELETE FROM libraryDivision WHERE id=$id"; | |
| try | |
| { | |
| $this->executeQueryForObject($sql); | |
| BookService::getInstance()->deleteAccessionNumberPrefix($id); | |
| return true; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get all library division | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryDivision() | |
| { | |
| $libraryDivision=[]; | |
| $sql="SELECT ld.id, ld.name, ld.code, ld.deptIds, ap.prefix, ap.id as prefixId FROM libraryDivision ld INNER JOIN accessionPrefix ap ON ap.divisionId=ld.id"; | |
| try | |
| { | |
| $libraryDivision = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $libraryDivision; | |
| } | |
| /** | |
| * get all library division by id | |
| * @var int $id | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryDivisionById($id) | |
| { | |
| $libraryDivision=null; | |
| $sql="SELECT ld.id, ld.name, ld.code, ld.deptIds, ap.prefix, ap.id as prefixId FROM libraryDivision ld INNER JOIN accessionPrefix ap ON ap.divisionId=ld.id WHERE ld.id=$id"; | |
| try | |
| { | |
| $libraryDivision = $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $libraryDivision; | |
| } | |
| /** | |
| * get library division not set departments | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryDivisionNotSetDepartments() | |
| { | |
| $deptList=[]; | |
| $sql="SELECT group_concat(deptIds) as deptIds FROM libraryDivision"; | |
| $deptIds=$this->executeQueryForObject($sql)->deptIds; | |
| if(!empty($deptIds)) | |
| { | |
| $deptId =explode(',',$deptIds); | |
| $sql="SELECT deptID, deptName, departmentDesc FROM department WHERE deptID NOT IN(".implode(',',$deptId).") AND deptShow=1"; | |
| } | |
| else | |
| { | |
| $sql="SELECT deptID, deptName, departmentDesc FROM department WHERE deptShow=1"; | |
| } | |
| try | |
| { | |
| $deptList = $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $deptList; | |
| } | |
| /** | |
| * check library division is assigned to book or not | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return bool | |
| */ | |
| public function isDivisionAssignedToBooks($id) | |
| { | |
| $isBooksFlag=false; | |
| $sql="SELECT if(count(lb.bookID),1,0) as isBooks FROM library_bookinfo lb INNER JOIN accessionPrefix ap ON ap.id=lb.prefixId INNER JOIN libraryDivision ld ON ld.id=ap.divisionId WHERE ld.id=$id"; | |
| try | |
| { | |
| $isBooks = $this->executeQueryForObject($sql)->isBooks; | |
| if($isBooks=='1') | |
| { | |
| $isBooksFlag=true; | |
| } | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $isBooksFlag; | |
| } | |
| /** | |
| * Get book renewal details | |
| * @param string $fromDate | |
| * @param string $toDate | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBookRenewalDetails($fromDate, $toDate) | |
| { | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql= "SELECT lst.studentID, lst.bookAccNo, lst.loanDate, lsb.lendDuration, sa.studentAccount, lsb.ruleheadID, (DATEDIFF(CURDATE(), lst.loanDate)) as dateDiff, sa.deptID, sa.batchID, bat.batchName, dep.deptName, sa.studentName, lst.returnDate, lst.fine, lst.transactionStatus, ap.prefix, lst.renewalCount, (select staffName from library_staff_account lsa where lsa.staffID= lst.issuedStaffID) as issuedBy, (select staffName from library_staff_account lsa where lsa.staffID= lst.returnedStaffID) as returnedBy, transactionDate,lst.created_date,lst.fine from library_studenttransaction lst inner join studentaccount sa on lst.studentID=sa.studentID inner join library_studentbenefits lsb on lsb.deptID = sa.deptID and lsb.batchID = sa.batchID inner join batches bat on bat.batchID = sa.batchID inner join department dep on dep.deptID = sa.deptID inner join library_bookinfo lbi on lbi.bookAccNo=lst.bookAccNo AND lbi.prefixId=lst.prefixId inner join accessionPrefix ap on lbi.prefixId=ap.id WHERE lst.renewalCount > 0 and lst.loanDate BETWEEN '$fromDate' AND '$toDate' union SELECT lst.studentID, lst.bookAccNo, lst.loanDate, lsb.lendDuration, sa.studentAccount, lsb.ruleheadID, (DATEDIFF(CURDATE(), lst.loanDate)) as dateDiff, sa.deptID, sa.batchID, bat.batchName, dep.deptName, sa.studentName, lst.returnDate, lst.fine, lst.transactionStatus, ap.prefix, lst.renewalCount, (select staffName from library_staff_account lsa where lsa.staffID= lst.issuedStaffID) as issuedBy, (select staffName from library_staff_account lsa where lsa.staffID= lst.returnedStaffID) as returnedBy, transactionDate,lst.created_date,lst.fine from library_studenttransaction lst inner join studentaccount sa on lst.studentID=sa.studentID inner join failed_students fs on fs.studentID=sa.studentID inner join library_studentbenefits lsb on lsb.deptID = sa.deptID and lsb.batchID = fs.previousBatch inner join batches bat on bat.batchID = fs.previousBatch inner join department dep on dep.deptID = sa.deptID inner join library_bookinfo lbi on lbi.bookAccNo=lst.bookAccNo AND lbi.prefixId=lst.prefixId inner join accessionPrefix ap on lbi.prefixId=ap.id WHERE lst.renewalCount > 0 and lst.loanDate BETWEEN '$fromDate' AND '$toDate' ORDER BY transactionDate"; | |
| try { | |
| $renewalDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $renewalDetails; | |
| } | |
| /** | |
| * Get library staff details by staffID | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| */ | |
| public function getLibraryStaffDetails($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "select staffAccount, staffTypeID, staffName, staffEmail, staffGender, staffBirthday, staffAddress, staffPhone from library_staff_account where staffID=$staffId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * Get library staff details | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| */ | |
| public function getLibraryStaff() | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "SELECT staffId, staffAccount, staffTypeID, staffName, staffEmail, staffGender, staffBirthday, staffAddress, staffPhone FROM library_staff_account"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $staffDetails; | |
| } | |
| /** | |
| * Update library book issue | |
| * @param int $transactionId | |
| * @param int $staffId | |
| * @param int $userType | |
| * @throws ProfessionalException | |
| */ | |
| public function UpdateIssueLibraryBook($transactionId, $staffId, $userType) | |
| { | |
| $transactionId = $this->realEscapeString($transactionId); | |
| $userType = $this->realEscapeString($userType); | |
| $staffId = $this->realEscapeString($staffId); | |
| if($userType == 1) | |
| $sql = "UPDATE library_studenttransaction SET issuedStaffId = $staffId WHERE transactionID = $transactionId"; | |
| else | |
| $sql = "UPDATE library_stafftransaction SET issuedStaffId = $staffId WHERE transactionID = $transactionId"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * set default attachment | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function setDefaultAttachment($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| try | |
| { | |
| $sql ="UPDATE library_attachments SET isDefault=0"; | |
| $this->executeQueryForObject($sql); | |
| $sql_set = "UPDATE library_attachments SET isDefault=1 WHERE attachmentID=$id"; | |
| return $this->executeQueryForObject($sql_set); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get all library attachments | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getAttachments() | |
| { | |
| try | |
| { | |
| $sql ="SELECT * FROM library_attachments"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get books issued details of student | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentLibraryBooksIssuedDetails($studentId) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT | |
| lst.studentID, lst.bookAccNo, lst.loanDate, lsb.lendDuration, lbi.bookTitle, lsb.ruleheadID, (DATEDIFF(CURDATE(), lst.loanDate)) as noOfDays, sta.deptID, fs.previousBatch, ap.prefix | |
| FROM | |
| library_studenttransaction lst inner join | |
| studentaccount sta on lst.studentID = sta.studentID inner join | |
| library_bookinfo lbi on lbi.bookAccNo = lst.bookAccNo and lbi.prefixId = lst.prefixId left join | |
| failed_students fs on fs.studentID = sta.studentID left join | |
| library_studentbenefits lsb on lsb.deptID = sta.deptID and (lsb.batchID = sta.batchID or lsb.batchID = fs.previousBatch ) inner join | |
| accessionPrefix ap on ap.id = lbi.prefixId | |
| WHERE | |
| lst.studentID = $studentId AND | |
| lst.transactionStatus = '1';"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * block or unblock student from library | |
| * @param int $studentId | |
| * @param int $block | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function blockOrUnblockStudentFromLibrary($studentId, $block, $reason) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $block = $this->realEscapeString($block); | |
| $reason = $this->realEscapeString($reason); | |
| $sql = "UPDATE studentaccount SET isBlockedFromLibrary = '$block', libraryBlockReason = '$reason' WHERE studentID = $studentId"; | |
| try | |
| { | |
| return $this->executeQuery($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all students blocked from library | |
| * @param GetStudentsBlockedFromLibraryRequest $getStudentsBlockedRequest | |
| * @throws ProfessionalException | |
| * @return \com\linways\core\ams\professional\response\GetStudentsBlockedFromLibraryResponse | |
| */ | |
| public function getAllStudentsBlockedFromLibrary($getStudentsBlockedRequest) | |
| { | |
| $getStudentsBlockedRequest = $this->realEscapeObject($getStudentsBlockedRequest); | |
| $getStudentsBlockedResponse = new GetStudentsBlockedFromLibraryResponse(); | |
| $sql = "SELECT studentID, studentAccount, studentName, admissionNo, isBlockedFromLibrary, libraryBlockReason, sa.batchID, batchName, semID, sa.deptID, dep.deptName, dep.departmentDesc from studentaccount sa inner join batches bat on sa.batchID = bat.batchID inner join department dep on dep.deptID = sa.deptID where isBlockedFromLibrary = 1 "; | |
| $sql.= " limit $getStudentsBlockedRequest->startIndex, $getStudentsBlockedRequest->endIndex"; | |
| $sql_count = "SELECT count(studentID) as count from studentaccount sa inner join batches bat on sa.batchID = bat.batchID inner join department dep on dep.deptID = sa.deptID where isBlockedFromLibrary = 1"; | |
| try { | |
| $getStudentsBlockedResponse->studentList = $this->executeQueryForList($sql); | |
| $getStudentsBlockedResponse->totalRecords = $this->executeQueryForObject($sql_count)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $getStudentsBlockedResponse; | |
| } | |
| /** | |
| * assign library division to staff | |
| * @param LibraryStaffDivision $libraryStaffDivision | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function addLibraryStaffDivision($libraryStaffDivision) | |
| { | |
| $libraryStaffDivision = $this->realEscapeObject($libraryStaffDivision); | |
| $sql="INSERT INTO library_staff_division (staffId, divisionId, isPrimary, createdBy, createdDate, updatedBy, updatedDate) VALUES ($libraryStaffDivision->staffId, $libraryStaffDivision->divisionId, $libraryStaffDivision->isPrimary, $libraryStaffDivision->createdBy, utc_timestamp(), $libraryStaffDivision->updatedBy, utc_timestamp())"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function removeLibraryStaffDivisionByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| try { | |
| $sql = "DELETE FROM library_staff_division WHERE staffId=$staffId"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * remove staff assigned division by division id | |
| * @param int $staffId | |
| * @param int $divisionId | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function removeLibraryStaffDivisionByDivision($staffId, $divisionId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $divisionId = $this->realEscapeString($divisionId); | |
| try { | |
| $sql = "DELETE FROM library_staff_division WHERE staffId=$staffId AND divisionId=$divisionId"; | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get staff assigned library division by staffId | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryStaffDivisionByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql ="SELECT lsd.id, lsd.staffId, lsd.divisionId, lsd.isPrimary, ld.name AS divisionName, ld.deptIds, ap.id AS prefixId, ap.prefix FROM library_staff_division lsd INNER JOIN library_staff_account lsa ON lsa.staffId=lsd.staffId INNER JOIN libraryDivision ld ON ld.id=lsd.divisionId INNER JOIN accessionPrefix ap ON ap.divisionId=ld.id WHERE lsd.staffId=$staffId order by lsd.isPrimary desc, lsd.id"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * set primary division for staff | |
| * @param LibraryStaffDivision $libraryStaffDivision | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function setPrimaryLibraryStaffDivision($libraryStaffDivision) | |
| { | |
| $libraryStaffDivision = $this->realEscapeObject($libraryStaffDivision); | |
| $sql="UPDATE library_staff_division SET isPrimary=1 WHERE staffId=$libraryStaffDivision->staffId AND divisionId=$libraryStaffDivision->divisionId"; | |
| try { | |
| $this->unsetPrimaryLibraryStaffDivision($libraryStaffDivision->staffId); | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * unset primary division for staff | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function unsetPrimaryLibraryStaffDivision($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql="UPDATE library_staff_division SET isPrimary=0 WHERE staffId=$staffId"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get details of book using studentID | |
| * @param int $studentId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBookDetailsByStudentId($studentId) { | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT t1.bookAccNo,t4.bookTitle,t4.bookEdition,t5.authorName, t1.loanDate, t3.lendDuration, t3.ruleheadID, (DATEDIFF(CURDATE(),t1.loanDate)), t1.returnDate, t1.fine, t1.transactionStatus, t6.prefix,t1.renewalCount from library_studenttransaction t1, studentaccount t2, library_studentbenefits t3, library_bookinfo t4, library_author t5, accessionPrefix t6 WHERE t1.studentID=t2.studentID AND t3.deptID = t2.deptID AND t3.batchID = t2.batchID AND t1.bookAccNo=t4.bookAccNo AND t1.prefixId=t4.prefixId AND t5.authorID=t4.authorID AND t4.prefixId=t6.id AND t1.studentID=$studentId ORDER BY t1.loanDate"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get details of book using staffID | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getBookDetailsByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "SELECT t1.bookAccNo,t4.bookTitle,t4.bookEdition,t5.authorName, t1.loanDate, | |
| t3.lendDuration, t3.ruleheadID, (DATEDIFF(CURDATE(), t1.loanDate)), t1.returnDate, t1.fine, | |
| t1.transactionStatus, t7.prefix,t1.renewalCount from library_stafftransaction t1, staffaccounts t2, library_staffbenefits t3, | |
| library_bookinfo t4, library_author t5, accessionPrefix t7 WHERE t1.staffID=t2.staffID AND t1.bookAccNo=t4.bookAccNo AND t1.prefixId=t4.prefixId AND t4.prefixId=t7.id AND t5.authorID=t4.authorID AND t3.deptID = t2.deptID AND t1.staffID=$staffId ORDER BY t1.loanDate"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get libraryStatiscs report | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryStatisticsReport() | |
| { | |
| $sql = "SELECT | |
| * | |
| FROM | |
| (SELECT | |
| ld.deptName, | |
| lb.deptID, | |
| COUNT(lb.bookID) AS noOfBooks, | |
| SUM(lb.bookPrice * lc.currencyConversion) AS amount | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN library_department ld ON (lb.deptID = ld.deptID) | |
| INNER JOIN library_currency lc ON (lb.currencyID = lc.currencyID) | |
| INNER JOIN accessionPrefix ap ON (lb.prefixId = ap.id) | |
| INNER JOIN libraryDivision ldiv ON (ldiv.id = ap.divisionId) | |
| WHERE | |
| FIND_IN_SET(ldiv.id, '".implode(',', $_SESSION['divisionIds'])."') | |
| GROUP BY lb.deptID) books_in_dept | |
| INNER JOIN | |
| (SELECT | |
| COUNT(*) AS noOfTitles, deptID | |
| FROM | |
| (SELECT | |
| COUNT(bookID), deptID | |
| FROM | |
| library_bookinfo lbi INNER JOIN accessionPrefix apr ON (lbi.prefixId = apr.id) | |
| INNER JOIN libraryDivision lidiv ON (lidiv.id = apr.divisionId) WHERE FIND_IN_SET(lidiv.id, '".implode(',', $_SESSION['divisionIds'])."') | |
| GROUP BY lbi.deptID , lbi.bookTitle , lbi.authorID , lbi.bookVolume , lbi.bookEdition) t1 | |
| GROUP BY deptID ) titles_in_dept ON (books_in_dept.deptID = titles_in_dept.deptID) | |
| ORDER BY deptName ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * add library attendance | |
| * @param LibraryAttendanceRegister $libraryAttendanceReg | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function addLibraryAttendance($libraryAttendanceReg) | |
| { | |
| $sql ="INSERT INTO library_attendance_register(studentId, inTime, createdBy, createdDate, updatedBy, updatedDate) VALUES ($libraryAttendanceReg->studentId, '".date('Y-m-d H:i:s', strtotime($libraryAttendanceReg->inTime))."', $libraryAttendanceReg->createdBy, utc_timestamp(), $libraryAttendanceReg->updatedBy, utc_timestamp())"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update library attendance | |
| * @param LibraryAttendanceRegister $libraryAttendanceReg | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function updateLibraryAttendanceOutTime($libraryAttendanceReg) | |
| { | |
| $sql = "UPDATE library_attendance_register SET outTime='".date('Y-m-d H:i:s', strtotime($libraryAttendanceReg->outTime))."', updatedBy=$libraryAttendanceReg->updatedBy, updatedDate=utc_timestamp() WHERE id=$libraryAttendanceReg->id"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function updateMissedLibraryAttendanceOutTime() | |
| { | |
| $sql="select * from library_attendance_register where outTime is null"; | |
| try | |
| { | |
| $closingTime = CommonService::getInstance()->getSettings(SettingsConstents::LIBRARY_SETTINGS, SettingsConstents::LIBRARY_CLOSING_TIME); | |
| $attendanceList = $this->executeQueryForList($sql); | |
| if(!empty($attendanceList)) | |
| { | |
| foreach ($attendanceList as $attendance) | |
| { | |
| $outTime = date('Y-m-d', strtotime($attendance->inTime)); | |
| $outTime .=" ".$closingTime.":00"; | |
| $sqlUpdate ="UPDATE library_attendance_register SET outTime='".date('Y-m-d H:i:s', strtotime($outTime))."', isManual=0, updatedBy=1, updatedDate=utc_timestamp() WHERE id=$attendance->id"; | |
| $this->executeQuery($sqlUpdate); | |
| } | |
| } | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getStudentLibraryAttendanceIn($studentId) | |
| { | |
| $sql = "SELECT * FROM library_attendance_register WHERE studentId=$studentId AND outTime is null"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getStudentLibraryAttendanceLastIn($studentId) | |
| { | |
| $sql = "SELECT * FROM library_attendance_register WHERE studentId=$studentId AND outTime is not null ORDER BY inTime DESC LIMIT 1"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get library attendance report between dates | |
| * @param unknown $fromDate | |
| * @param unknown $toDate | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStudentLibraryAttendanceReport($fromDate, $toDate) | |
| { | |
| $sql = "select lar.studentId, sa.studentName, bt.batchName, dep.deptName, lar.inTime, lar.outTime from library_attendance_register lar INNER JOIN studentaccount sa ON sa.studentID=lar.studentId INNER JOIN batches bt ON bt.batchID=sa.batchID INNER JOIN department dep ON dep.deptID=bt.deptID WHERE DATE_FORMAT(lar.inTime, '%Y-%m-%d') BETWEEN '".date('Y-m-d', strtotime($fromDate))."' AND '".date('Y-m-d', strtotime($toDate))."' OR DATE_FORMAT(lar.outTime, '%Y-%m-%d') BETWEEN '".date('Y-m-d', strtotime($fromDate))."' AND '".date('Y-m-d', strtotime($toDate))."' order by lar.inTime"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * add library report settings | |
| * @param LibraryReportSettings $reportSettings | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function addLibraryReportSettings($reportSettings) | |
| { | |
| $reportSettings = $this->realEscapeObject($reportSettings); | |
| $sql="INSERT INTO libraryReportSettings (reportName, columnConfig, createdBy, createdDate, updatedBy, updatedDate) VALUES ('$reportSettings->reportName', '$reportSettings->columnConfig', $reportSettings->createdBy, utc_timestamp(), $reportSettings->updatedBy, utc_timestamp()); | |
| "; | |
| try | |
| { | |
| return $this->executeQuery($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update library report settings | |
| * @param LibraryReportSettings $reportSettings | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function updateLibraryReportSettings($reportSettings) | |
| { | |
| $sql="UPDATE libraryReportSettings SET columnConfig='$reportSettings->columnConfig', updatedBy=$reportSettings->updatedBy, updatedDate=utc_timestamp() WHERE reportName='$reportSettings->reportName'"; | |
| try | |
| { | |
| return $this->executeQuery($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get library report settings by name | |
| * @param string $reportName | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryReportSettingsByName($reportName) | |
| { | |
| $reportName = $this->realEscapeString($reportName); | |
| $sql="SELECT columnConfig FROM libraryReportSettings WHERE reportName='$reportName'"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql)->columnConfig; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get library report settings by name | |
| * @throws ProfessionalException | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getLibraryReportSettings() | |
| { | |
| $reportName = $this->realEscapeString($reportName); | |
| $sql="SELECT reportName, columnConfig FROM libraryReportSettings"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @author Sanoop | |
| * Get books issued details of staff | |
| * @param int $staffId | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| */ | |
| public function getStaffLibraryBooksIssuedDetails($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "SELECT | |
| lst.staffID, lst.bookAccNo, lst.loanDate, lsb.lendDuration, lbi.bookTitle, lsb.ruleheadID, (DATEDIFF(CURDATE(), lst.loanDate)) as noOfDays, sta.deptID, ap.prefix | |
| FROM | |
| library_stafftransaction lst inner join | |
| staffaccounts sta on lst.staffID = sta.staffID inner join | |
| library_bookinfo lbi on lbi.bookAccNo = lst.bookAccNo and lbi.prefixId = lst.prefixId left join | |
| library_staffbenefits lsb on lsb.deptID = sta.deptID inner join | |
| accessionPrefix ap on ap.id = lbi.prefixId | |
| WHERE | |
| lst.staffID = $staffId AND | |
| lst.transactionStatus = '1'"; | |
| try | |
| { | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * update document view count | |
| * @param LibraryDocumentReview $libraryDocumentReview | |
| * @throws ProfessionalException | |
| */ | |
| public function updateDocumentViewCount($libraryDocumentReview) | |
| { | |
| $libraryDocumentReview = $this->realEscapeObject($libraryDocumentReview); | |
| $sql="SELECT id FROM library_document_review WHERE userType='$libraryDocumentReview->userType' AND userId=$libraryDocumentReview->userId AND ebookId=$libraryDocumentReview->ebookId"; | |
| try | |
| { | |
| $id = $this->executeQueryForObject($sql)->id; | |
| if($id) | |
| { | |
| $sql_change = "UPDATE library_document_review SET lastViewDate=utc_timestamp() WHERE id=$id"; | |
| } | |
| else | |
| { | |
| $sql_change = "INSERT INTO library_document_review (userId, userType, ebookId, lastViewDate) VALUES ($libraryDocumentReview->userId, '$libraryDocumentReview->userType', $libraryDocumentReview->ebookId, utc_timestamp()) | |
| "; | |
| } | |
| $this->executeQuery($sql_change); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @author Sanoop | |
| * block or unblock staff from library | |
| * @param int $staffId | |
| * @param int $block | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| public function blockOrUnblockStaffFromLibrary($staffId, $block, $reason) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $block = $this->realEscapeString($block); | |
| $reason = $this->realEscapeString($reason); | |
| $sql = "UPDATE staffaccounts SET isBlockedFromLibrary = '$block', libraryBlockReason = '$reason' WHERE staffID = $staffId"; | |
| try | |
| { | |
| return $this->executeQuery($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * get viewers count by ebook id | |
| * @param int $id | |
| * @throws ProfessionalException | |
| * @return string | |
| */ | |
| public function getViewCountOfDocumentById($id) | |
| { | |
| $sql="SELECT count(id) as views FROM library_document_review WHERE ebookId=$id"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql)->views; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all staff blocked from library | |
| * @param GetStaffBlockedFromLibraryRequest $getStaffBlockedRequest | |
| * @throws ProfessionalException | |
| * @return \com\linways\core\ams\professional\response\GetStaffBlockedFromLibraryResponse | |
| */ | |
| public function getAllStaffBlockedFromLibrary($getStaffBlockedRequest) | |
| { | |
| $getStaffBlockedRequest = $this->realEscapeObject($getStaffBlockedRequest); | |
| $getStaffBlockedResponse = new GetStaffBlockedFromLibraryResponse(); | |
| $sql = "SELECT staffID, staffAccount, staffName, isBlockedFromLibrary, libraryBlockReason, sa.deptID, dep.deptName, dep.departmentDesc from staffaccounts sa inner join department dep on dep.deptID = sa.deptID where isBlockedFromLibrary=1 "; | |
| $sql.= " limit $getStaffBlockedRequest->startIndex, $getStaffBlockedRequest->endIndex"; | |
| $sql_count = "SELECT count(staffID) as count from staffaccounts sa inner join department dep on dep.deptID = sa.deptID where isBlockedFromLibrary = 1"; | |
| try { | |
| $getStaffBlockedResponse->staffList = $this->executeQueryForList($sql); | |
| $getStaffBlockedResponse->totalRecords = $this->executeQueryForObject($sql_count)->count; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $getStaffBlockedResponse; | |
| } | |
| public function getLastGeneratedPeriodicalAccNoByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $sql = "SELECT ap.id , ap.prefix, if(lp.periodicalAccNo,lp.periodicalAccNo,0) as periodicalAccNo, lp.periodicalTitle, ld.name as divisionName FROM library_periodicalinfo lp RIGHT JOIN accessionPrefix ap ON ap.id=lp.prefixId RIGHT JOIN libraryDivision ld ON ld.id=ap.divisionId RIGHT JOIN library_staff_division lsd ON lsd.divisionId=ap.divisionId AND lsd.divisionId=ld.id WHERE lsd.staffId=$staffId ORDER BY lp.periodicalID DESC LIMIT 1"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all Library departments | |
| * @return Array departments | |
| * @author Vishnu M | |
| */ | |
| public function getLibraryDepartments() { | |
| $sql = "SELECT deptID, deptName, departmentDesc FROM library_department"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all Library subjects | |
| * @return Array subjects | |
| * @author Vishnu M | |
| */ | |
| public function getLibrarySubjects() { | |
| $sql = "SELECT subjectID, subjectName FROM library_subject"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all Library language | |
| * @return Array language | |
| * @author Vishnu M | |
| */ | |
| public function getLibraryLanguages() { | |
| $sql = "SELECT langID, langName FROM library_language"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get libraryStatiscs report Rack-wise | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @author Vishnu M | |
| */ | |
| public function getLibraryStatisticsReportRackWise() { | |
| // $sql = "SELECT ld.deptName, lr.rackName, COUNT(lb.bookID) AS bookCount, SUM(lb.bookPrice * lc.currencyConversion) AS amount FROM library_bookinfo lb INNER JOIN library_rackentry lr ON (lb.rackID = lr.rackID) INNER JOIN library_department ld ON (lb.deptID = ld.deptID) INNER JOIN library_currency lc ON (lb.currencyID = lc.currencyID) INNER JOIN accessionPrefix ap ON (lb.prefixId = ap.id) INNER JOIN libraryDivision ldiv ON (ldiv.id = ap.divisionId) WHERE FIND_IN_SET(ldiv.id, '".implode(',', $_SESSION['divisionIds'])."') GROUP BY lb.deptID , lb.rackID ORDER BY ld.deptName ASC , lr.rackName ASC"; | |
| $sql = "SELECT | |
| * | |
| FROM | |
| (SELECT | |
| ld.deptName, | |
| lr.rackName, | |
| lb.deptID, | |
| lb.rackID, | |
| COUNT(lb.bookID) AS bookCount, | |
| SUM(lb.bookPrice * lc.currencyConversion) AS amount | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN library_rackentry lr ON (lb.rackID = lr.rackID) | |
| INNER JOIN library_department ld ON (lb.deptID = ld.deptID) | |
| INNER JOIN library_currency lc ON (lb.currencyID = lc.currencyID) | |
| INNER JOIN accessionPrefix ap ON (lb.prefixId = ap.id) | |
| INNER JOIN libraryDivision ldiv ON (ldiv.id = ap.divisionId) | |
| WHERE | |
| FIND_IN_SET(ldiv.id, '".implode(',', $_SESSION['divisionIds'])."') | |
| GROUP BY lb.deptID , lb.rackID) books_in_dept | |
| INNER JOIN | |
| (SELECT | |
| COUNT(*) AS bookTitleCount, deptID, rackID | |
| FROM | |
| (SELECT | |
| COUNT(bookID), deptID, rackID | |
| FROM | |
| library_bookinfo | |
| GROUP BY deptID , rackID, bookTitle , authorID , bookVolume , bookEdition) t1 | |
| GROUP BY deptID , rackID) titles_in_dept ON (books_in_dept.deptID = titles_in_dept.deptID | |
| AND books_in_dept.rackID = titles_in_dept.rackID) | |
| ORDER BY deptName ASC , rackName ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get libraryStatiscs report Row-wise | |
| * @throws ProfessionalException | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @author Vishnu M | |
| */ | |
| public function getLibraryStatisticsReportRowWise() { | |
| $sql = "SELECT | |
| * | |
| FROM | |
| (SELECT | |
| ld.deptName, | |
| lr.drawName, | |
| lb.deptID, | |
| lb.drawID, | |
| COUNT(lb.bookID) AS bookCount, | |
| SUM(lb.bookPrice * lc.currencyConversion) AS amount | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN library_drawentry lr ON (lb.drawID = lr.drawID) | |
| INNER JOIN library_department ld ON (lb.deptID = ld.deptID) | |
| INNER JOIN library_currency lc ON (lb.currencyID = lc.currencyID) | |
| INNER JOIN accessionPrefix ap ON (lb.prefixId = ap.id) | |
| INNER JOIN libraryDivision ldiv ON (ldiv.id = ap.divisionId) | |
| WHERE | |
| FIND_IN_SET(ldiv.id, '".implode(',', $_SESSION['divisionIds'])."') | |
| GROUP BY lb.deptID , lb.drawID) books_in_dept | |
| INNER JOIN | |
| (SELECT | |
| COUNT(*) AS bookTitleCount, deptID, drawID | |
| FROM | |
| (SELECT | |
| COUNT(bookID), deptID, drawID | |
| FROM | |
| library_bookinfo | |
| GROUP BY deptID , drawID, bookTitle , authorID , bookVolume , bookEdition) t1 | |
| GROUP BY deptID , drawID) titles_in_dept ON (books_in_dept.deptID = titles_in_dept.deptID | |
| AND books_in_dept.drawID = titles_in_dept.drawID) | |
| ORDER BY deptName ASC , drawName ASC"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Search Library Books | |
| * @param LibraryBookSearchRequest $searchRequest | |
| * @return Array $libraryBooks | |
| */ | |
| public function searchLibraryBook($searchRequest, $libStaffId=null) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $libraryBooks = []; | |
| $sqlCount = "SELECT COUNT(lb.bookID) AS totalRecords "; | |
| $sqlSearch = "SELECT | |
| lb.bookID, | |
| lb.bookTitle, | |
| la.authorName, | |
| lb.bookEdition, | |
| lb.bookVolume, | |
| lb.bookMaterialType, | |
| lb.bookAccNo, | |
| ap.prefix, | |
| ld.drawName, | |
| lr.rackName, | |
| (lb.bookPrice * lc.currencyConversion) AS price "; | |
| $fromWhere = " | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN | |
| library_author la ON (lb.authorID = la.authorID) | |
| INNER JOIN | |
| library_currency lc ON (lc.currencyID = lb.currencyID) | |
| INNER JOIN | |
| library_drawentry ld ON ld.drawID=lb.drawID | |
| INNER JOIN | |
| library_rackentry lr ON lr.rackID=lb.rackID | |
| INNER JOIN | |
| accessionPrefix ap ON ap.id=lb.prefixId | |
| INNER JOIN | |
| libraryDivision ldiv ON ldiv.id=ap.divisionId | |
| WHERE 1 = 1 "; | |
| if ( !empty ( $searchRequest ) ) { | |
| if ( !empty ( $searchRequest->bookTitle ) ) { | |
| $searchCondition .= " AND lb.bookTitle LIKE '%".$searchRequest->bookTitle."%' "; | |
| } | |
| if ( !empty ( $searchRequest->deptId ) ) { | |
| $searchCondition .= " AND lb.deptID IN (".implode(",", $searchRequest->deptId).") "; | |
| } | |
| if ( !empty ( $searchRequest->subjectId ) ) { | |
| $searchCondition .= " AND lb.subjectID IN (".implode(",", $searchRequest->subjectId).") "; | |
| } | |
| if ( !empty ( $searchRequest->langId ) ) { | |
| $searchCondition .= " AND lb.langID = ".$searchRequest->langId." "; | |
| } | |
| if ( !empty ( $searchRequest->bookYearOfPub ) ) { | |
| $searchCondition .= " AND lb.bookYearOfPub = ".$searchRequest->bookYearOfPub." "; | |
| } | |
| if ( !empty ( $searchRequest->bookAccNo ) ) { | |
| $searchCondition .= " AND lb.bookAccNo = ".$searchRequest->bookAccNo." "; | |
| } | |
| if ( !empty ( $searchRequest->bookCallNo ) ) { | |
| $searchCondition .= " AND lb.bookCallNo LIKE '%".$searchRequest->bookCallNo."%' "; | |
| } | |
| if ( !empty ( $searchRequest->authorName ) ) { | |
| $searchCondition .= " AND la.authorName LIKE '%".$searchRequest->authorName."%' "; | |
| } | |
| if ( !empty ( $searchRequest->keyword ) ) { | |
| $searchCondition .= " AND lb.".$searchRequest->keyword." LIKE '%".$searchRequest->keywordValue."%'"; | |
| } | |
| if(!empty($libStaffId)) | |
| { | |
| $libraryDivision = $this->getLibraryStaffDivisionByStaffId($libStaffId); | |
| if(!empty($libraryDivision)) | |
| { | |
| $divisionIds= array_map(function($obj) { return $obj->divisionId; },$libraryDivision); | |
| $searchCondition .= " AND FIND_IN_SET(ldiv.id, '".implode(',', $_SESSION['divisionIds'])."')"; | |
| } | |
| } | |
| if ( !empty ( $searchRequest->sortBy ) ) { | |
| $searchCondition .= " ORDER BY $searchRequest->sortBy $searchRequest->sortOrder "; | |
| } | |
| $pagination = " LIMIT $searchRequest->startIndex, $searchRequest->offset"; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,"Library Book Search Request is Empty!"); | |
| } | |
| try { | |
| $sqlSearch .= $fromWhere.$searchCondition.$pagination; | |
| $sqlCount .= $fromWhere.$searchCondition; | |
| $libraryBooks["totalRecords"] = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $libraryBooks["books"] = $this->executeQueryForList($sqlSearch); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $libraryBooks; | |
| } | |
| public function searchLibraryBookOPAC($searchRequest) { | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| $libraryBooks = []; | |
| $sqlCount = "SELECT COUNT(lb.bookID) AS totalRecords "; | |
| // $sqlSearch = "SELECT | |
| // group_concat(lb.bookID) as bookID, | |
| // lb.bookTitle, | |
| // la.authorName, | |
| // lb.bookEdition, | |
| // lb.bookVolume, | |
| // COUNT(lb.bookID) as totalCopies, | |
| // COUNT(CASE WHEN lb.bookStatus = 1 THEN 1 END) AS availableCopies, | |
| // (lb.bookPrice * lc.currencyConversion) AS price "; | |
| $sqlSearch = "SELECT | |
| lb.bookID, | |
| lb.bookAccNo, | |
| lb.bookTitle, | |
| la.authorName, | |
| lb.bookEdition, | |
| lb.bookVolume, | |
| ap.prefix "; | |
| $fromWhere = " | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN | |
| library_author la ON (lb.authorID = la.authorID) | |
| INNER JOIN | |
| library_currency lc ON (lc.currencyID = lb.currencyID) | |
| INNER JOIN | |
| accessionPrefix ap ON (ap.id = lb.prefixId) | |
| WHERE 1 = 1 "; | |
| // $authorGroupBy = " GROUP BY lb.bookTitle, lb.authorID, lb.bookVolume, lb.bookEdition"; | |
| if ( !empty ( $searchRequest ) ) { | |
| if ( !empty ( $searchRequest->bookTitle ) ) { | |
| $searchCondition .= " AND lb.bookTitle LIKE '%".$searchRequest->bookTitle."%' "; | |
| } | |
| if ( !empty ( $searchRequest->deptId ) ) { | |
| $searchCondition .= " AND lb.deptID IN (".implode(",", $searchRequest->deptId).") "; | |
| } | |
| if ( !empty ( $searchRequest->subjectId ) ) { | |
| $searchCondition .= " AND lb.subjectID IN (".implode(",", $searchRequest->subjectId).") "; | |
| } | |
| if ( !empty ( $searchRequest->langId ) ) { | |
| $searchCondition .= " AND lb.langID = '".$searchRequest->langId."' "; | |
| } | |
| if ( !empty ( $searchRequest->bookYearOfPub ) ) { | |
| $searchCondition .= " AND lb.bookYearOfPub = '".$searchRequest->bookYearOfPub."' "; | |
| } | |
| if ( !empty ( $searchRequest->bookAccNo ) ) { | |
| $searchCondition .= " AND CONCAT(ap.prefix, '-', lb.bookAccNo) LIKE '%".$searchRequest->bookAccNo."%' "; | |
| } | |
| if ( !empty ( $searchRequest->bookCallNo ) ) { | |
| $searchCondition .= " AND lb.bookCallNo LIKE '%".$searchRequest->bookCallNo."%' "; | |
| } | |
| if ( !empty ( $searchRequest->authorName ) ) { | |
| $searchCondition .= " AND la.authorName LIKE '%".$searchRequest->authorName."%' "; | |
| } | |
| if ( !empty ( $searchRequest->keyword ) ) { | |
| $searchCondition .= " AND lb.".$searchRequest->keyword." LIKE '%".$searchRequest->keywordValue."%'"; | |
| } | |
| if ( !empty ( $searchRequest->sortBy ) ) { | |
| $searchCondition .= " ORDER BY $searchRequest->sortBy $searchRequest->sortOrder "; | |
| } | |
| $pagination = " LIMIT $searchRequest->startIndex, $searchRequest->offset"; | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST,"Library Book Search Request is Empty!"); | |
| } | |
| try { | |
| $sqlSearch .= $fromWhere.$searchCondition.$authorGroupBy.$pagination; | |
| $sqlCount .= $fromWhere.$searchCondition.$authorGroupBy; | |
| // $sqlCount = "SELECT COUNT(*) AS totalRecords FROM (".$sqlCount.") t1"; | |
| $libraryBooks["totalRecords"] = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $libraryBooks["books"] = $this->executeQueryForList($sqlSearch); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $libraryBooks; | |
| } | |
| public function getBookDetailsById ( $bookId ) { | |
| $bookId = $this->realEscapeString($bookId); | |
| $bookDetails = null; | |
| $sql = "SELECT | |
| lb.bookID, | |
| lb.bookAccNo, | |
| lb.bookTitle, | |
| la.authorName, | |
| lb.bookEdition, | |
| lb.bookVolume, | |
| lb.bookMaterialType, | |
| lb.bookClass, | |
| lb.bookCallNo, | |
| lb.bookISBN, | |
| lb.bookPages, | |
| lb.bookRefonly, | |
| lb.bookYearofPub, | |
| lb.bookStatus, | |
| ldr.drawName, | |
| lra.rackName, | |
| lp.publisherName, | |
| ld.deptName, | |
| ls.subjectName, | |
| ap.prefix, | |
| COUNT(lb.bookID) as totalCopies, | |
| COUNT(CASE WHEN lb.bookStatus = 1 THEN 1 END) AS availableCopies, | |
| (lb.bookPrice * lc.currencyConversion) AS price | |
| FROM | |
| library_bookinfo lb | |
| INNER JOIN | |
| library_author la ON (lb.authorID = la.authorID) | |
| INNER JOIN | |
| library_currency lc ON (lc.currencyID = lb.currencyID) | |
| INNER JOIN | |
| library_drawentry ldr ON (lb.drawID = ldr.drawID) | |
| INNER JOIN | |
| library_rackentry lra ON (lb.rackID = lra.rackID) | |
| INNER JOIN | |
| library_publisher lp ON (lb.publisherID = lp.publisherID) | |
| INNER JOIN | |
| library_department ld ON (lb.deptID = ld.deptID) | |
| INNER JOIN | |
| library_subject ls ON (lb.subjectID = ls.subjectID) | |
| INNER JOIN | |
| accessionPrefix ap ON (ap.id = lb.prefixId) | |
| WHERE bookID IN (".$bookId.")"; | |
| try { | |
| $bookDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $bookDetails; | |
| } | |
| /** | |
| * Get student book issued ( which is not yet returned ) details by bookId | |
| * @param Integer $bookId | |
| * @return Object $issuedDetails | |
| * @author Vishnu M | |
| */ | |
| public function getIssuedBookStudentDetailsByBookId ( $bookId ) { | |
| $bookId = $this->realEscapeString($bookId); | |
| $issuedDetails = null; | |
| $sql = "SELECT sa.studentID, sa.admissionNo, sa.regNo, sa.studentName, lst.transactionDate, lst.transactionStatus, lst.loanDate, lst.returnDate, lst.renewalCount, lst.fine, lst.issuedStaffId FROM studentaccount sa INNER JOIN library_studenttransaction lst ON (sa.studentID = lst.studentID) INNER JOIN library_bookinfo lb ON (lb.bookAccNo = lst.bookAccNo AND lb.prefixId = lst.prefixId) WHERE lst.transactionStatus = 1 AND lb.bookID = '".$bookId."'"; | |
| try { | |
| $issuedDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $issuedDetails; | |
| } | |
| /** | |
| * Get staff book issued ( which is not yet returned ) details by bookId | |
| * @param Integer $bookId | |
| * @return Object $issuedDetails | |
| * @author Vishnu M | |
| */ | |
| public function getIssuedBookStaffDetailsByBookId ( $bookId ) { | |
| $bookId = $this->realEscapeString($bookId); | |
| $issuedDetails = null; | |
| $sql = "SELECT sa.staffID, sa.staffCode, sa.staffName, lst.transactionDate, lst.transactionStatus, lst.loanDate, lst.returnDate, lst.renewalCount, lst.fine, lst.issuedStaffId FROM staffaccounts sa INNER JOIN library_stafftransaction lst ON (sa.staffID = lst.staffID) INNER JOIN library_bookinfo lb ON (lb.bookAccNo = lst.bookAccNo AND lb.prefixId = lst.prefixId) WHERE lst.transactionStatus = 1 AND lb.bookID = '".$bookId."'"; | |
| try { | |
| $issuedDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $issuedDetails; | |
| } | |
| /** | |
| * delete book by book id | |
| * @param int $bookId | |
| * @throws ProfessionalException | |
| * @return \com\linways\base\dto\MySqlResult | |
| */ | |
| function deleteBookById($bookId) | |
| { | |
| $bookId = $this->realEscapeString($bookId); | |
| $sql ="DELETE FROM library_bookinfo WHERE bookID='$bookId'"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Add or Update Ejournal View Count | |
| * | |
| * @param int $ejournalId | |
| * @param int $userId | |
| * @param string $userType | |
| * @return void | |
| */ | |
| public function addUpdateEJournalViewCount($ejournalId, $userId, $userType) | |
| { | |
| $ejournalId = $this->realEscapeString($ejournalId); | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| $viewCountRecordIdIfExists = $this->checkIfUserViewCountRecordExists($ejournalId, $userId, $userType)->id; | |
| if($viewCountRecordIdIfExists) | |
| { | |
| $sql = "UPDATE library_ejournal_review SET viewCount = viewCount + 1, lastViewDate = utc_timestamp() WHERE id = $viewCountRecordIdIfExists"; | |
| } | |
| else | |
| { | |
| $sql = "INSERT INTO library_ejournal_review (userId, userType, ejournalId, lastViewDate, viewCount) VALUES ($userId, \"$userType\", $ejournalId, utc_timestamp(), 1)"; | |
| } | |
| try | |
| { | |
| return $this->executeQuery($sql); | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Check if user view count Record exists in DB | |
| * | |
| * @param int $ejournalId | |
| * @param int $userId | |
| * @param string $userType | |
| * @return int $id | |
| */ | |
| private function checkIfUserViewCountRecordExists($ejournalId, $userId, $userType) | |
| { | |
| $sql = "SELECT id FROM library_ejournal_review WHERE userId = $userId AND userType = \"$userType\" AND ejournalId = $ejournalId"; | |
| try | |
| { | |
| return $this->executeQueryForObject($sql); | |
| } | |
| catch(\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Search Periodicals | |
| * @param LibraryPeriodicalSearchRequest $searchRequest | |
| * @return $periodicalList | |
| */ | |
| public function searchPeriodicals($searchRequest) | |
| { | |
| $periodicalList = []; | |
| $cond = ""; | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| if($searchRequest->deptId) | |
| { | |
| $cond .= "AND FIND_IN_SET($searchRequest->deptId, ld.deptIds)"; | |
| } | |
| if($searchRequest->periodicalTitle) | |
| { | |
| $cond .= " AND lp.periodicalTitle LIKE '%".$searchRequest->periodicalTitle."%' "; | |
| } | |
| $sql = "SELECT lp.periodicalID, lp.periodicalTitle, lp.periodicalAccNo, ls.subjectName, lf.frequency, lf.noofdays, ap.prefix FROM library_periodicalinfo lp INNER JOIN library_subject ls ON lp.subjectID = ls.subjectID INNER JOIN library_frequency lf ON lp.frequencyID=lf.frequencyID INNER JOIN accessionPrefix ap ON lp.prefixId=ap.id INNER JOIN libraryDivision ld ON ld.id=ap.divisionId WHERE lp.periodicalID is not null $cond order by lp.periodicalAccNo ASC"; | |
| $sqlCount = "SELECT COUNT(lp.periodicalID) as totalRecords FROM library_periodicalinfo lp INNER JOIN library_subject ls ON lp.subjectID = ls.subjectID INNER JOIN library_frequency lf ON lp.frequencyID=lf.frequencyID INNER JOIN accessionPrefix ap ON lp.prefixId=ap.id INNER JOIN libraryDivision ld ON ld.id=ap.divisionId WHERE lp.periodicalID is not null $cond order by lp.periodicalAccNo ASC"; | |
| $pagination = " LIMIT $searchRequest->startIndex, $searchRequest->offset"; | |
| try | |
| { | |
| $periodicalList['periodicals'] = $this->executeQueryForList($sql.$pagination); | |
| $periodicalList['totalRecords'] = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $periodicalList; | |
| } | |
| public function calculatingStudentReturnDate($studentId, $lastDate) | |
| { | |
| $batchDetails = BatchService::getInstance()->getBatchDetailsByStudentId($studentId); | |
| $sql = "select holiday from library_studentcalendar where deptID = " . $batchDetails->deptID . " AND batchID = " . $batchDetails->batchID . " AND holiday >='$lastDate' ORDER BY holiday"; | |
| $dateList = $this->executeQueryForList($sql); | |
| $b = 0; | |
| foreach ($dateList as $date) | |
| { | |
| if ($date->holiday == $lastDate) { | |
| $lastDate = CommonUtil::nextNDay( $lastDate, 1); | |
| $b ++; | |
| } else if ($b != 0) { | |
| break; | |
| } | |
| } | |
| $returnDateDetails= new \stdClass(); | |
| $returnDateDetails->date = $lastDate; | |
| $returnDateDetails->countDay = $b; | |
| return $returnDateDetails; | |
| } | |
| public function calculateStudentTotalFine($studentId) | |
| { | |
| $totalFineAmt=0; | |
| $sql ="SELECT t1.studentID, t1.bookAccNo, t1.loanDate, t3.lendDuration, t2.studentAccount, | |
| t3.ruleheadID, (DATEDIFF(CURDATE(), t1.loanDate)) as dateDiff, t2.deptID, t2.batchID, t4.batchName, | |
| t5.deptName, t2.studentName, t8.prefix, t1.fine from library_studenttransaction t1, studentaccount t2, | |
| library_studentbenefits t3, batches t4, department t5, semesters t6, library_bookinfo t7, accessionPrefix t8, libraryDivision t9 WHERE t1.transactionStatus=true | |
| AND t1.studentID=t2.studentID AND t3.deptID = t2.deptID AND t3.batchID = t2.batchID | |
| AND t4.batchID = t2.batchID AND t5.deptID = t2.deptID AND t4.semID = t6.semID AND t1.bookAccNo=t7.bookAccNo AND t1.prefixId=t7.prefixId AND t7.prefixId=t8.id AND t9.id=t8.divisionId AND t1.studentID=$studentId"; | |
| $studentBookList =$this->executeQueryForList($sql); | |
| foreach ($studentBookList as $book) | |
| { | |
| $lastdate= CommonUtil::nextNDay($book->loanDate, $book->lendDuration); | |
| //Checking whether the return date is a holiday or not | |
| $findHoliday = $this->calculatingStudentReturnDate($studentId, $lastdate); | |
| //For calculating fine if any | |
| $fineAdj = $book->lendDuration + $findHoliday->countDay; | |
| if($book->dateDiff > $fineAdj) | |
| { | |
| $date_diff = $book->dateDiff - $fineAdj; | |
| //$fineAmt = $row[5] * $date_diff; | |
| $totalFineAmt += $this->getFine($book->ruleheadID, $date_diff); | |
| } | |
| } | |
| return $totalFineAmt; | |
| } | |
| public function getFine($ruleheadID, $fineDays) { | |
| $userFine = 0; | |
| $sql = "SELECT t2.fineAmount, t2.fineInterval FROM library_fine_ruleheads t1, library_fine_rulesets t2 WHERE t1.ruleheadID=t2.ruleheadID AND t1.ruleheadID=\"$ruleheadID\" ORDER BY rulesetID ASC"; | |
| $fineDetails = $this->executeQueryForObject($sql); | |
| $fineAmount = $fineDetails->fineAmount; | |
| $fineInterval = $fineDetails->fineInterval; | |
| if ($fineInterval > 0 && $fineDays > $fineInterval) { | |
| $userFine = $userFine + ($fineInterval * $fineAmount); | |
| $fineDays = $fineDays - $fineInterval; | |
| } elseif ($fineInterval > 0 && $fineDays <= $fineInterval) { | |
| $userFine = $userFine + ($fineDays * $fineAmount); | |
| $fineDays = 0; | |
| } else { | |
| $userFine = $userFine + ($fineDays * $fineAmount); | |
| $fineDays = 0; | |
| } | |
| if ($fineDays != 0) // if the libadmin not left the last fine interval field, system will automatially assume it as blank and calculate the fine. | |
| { | |
| $userFine = $userFine + ($fineDays * $fineAmount); | |
| } | |
| return $userFine; | |
| } | |
| /** | |
| * Search ejournal | |
| * @param LibraryEjournalSearchRequest $searchRequest | |
| * @return $ejournalList | |
| */ | |
| public function searchEjournals($searchRequest) | |
| { | |
| $ejournalList = []; | |
| $cond = ""; | |
| $searchRequest = $this->realEscapeObject($searchRequest); | |
| if($searchRequest->journalTitle) | |
| { | |
| $cond .= "WHERE ejournelTitle LIKE '%".$searchRequest->journalTitle."%' "; | |
| } | |
| $sql = "SELECT id,ejournelTitle, ejournelLink,ejournelUsername,ejournelPassword FROM addEjournal $cond"; | |
| $sqlCount = "SELECT count(id) as totalRecords FROM addEjournal $cond"; | |
| $pagination = " LIMIT $searchRequest->startIndex, $searchRequest->offset"; | |
| try | |
| { | |
| $ejournalList['ejournals'] = $this->executeQueryForList($sql.$pagination); | |
| $ejournalList['totalRecords'] = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| } | |
| catch (\Exception $e) | |
| { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $ejournalList; | |
| } | |
| } | |
| ?> |