Server : Apache
System : Linux profile 3.10.0-1160.88.1.el7.x86_64 #1 SMP Tue Mar 7 15:41:52 UTC 2023 x86_64
User : apache ( 48)
PHP Version : 8.0.28
Disable Function : NONE
Directory : /var/www/html/mmishra/erp-19-12-2019/models/
|
Server : Apache System : Linux profile 3.10.0-1160.88.1.el7.x86_64 #1 SMP Tue Mar 7 15:41:52 UTC 2023 x86_64 User : apache ( 48) PHP Version : 8.0.28 Disable Function : NONE Directory : /var/www/html/mmishra/erp-19-12-2019/models/ |
<?php
class aaaModel extends erpModel {
public function __construct() {
parent::__construct();
$this->db = 'erp' .$_SESSION['instituteID']. '_aaa';
$this->pdo->query('use ' .$this->db);
}
//***********************************************************************
//* aaa alert functions *
//***********************************************************************
function listDefaulters($programID, $subjectID=0) {
switch ($subjectID) {
case 1: //document overdue
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,x.docheadCode,d.docheadName,x.remark
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN aaa_docheads AS d ON x.docheadCode=d.docheadCode AND x.docheadCode NOT IN (1, 2) LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' AND x.docheadCode NOT IN (1,2) ORDER BY e.enrolmentNo");
break;
case 2: // fees overdue
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,docheadCode,CASE docheadCode WHEN '1' THEN 'Fees' ELSE 'Mess' END AS docheadName, x.remark
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' AND x.docheadCode='1' ORDER BY e.enrolmentNo");
break;
case 3: // messcharge overdue
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,docheadCode,CASE docheadCode WHEN '1' THEN 'Fees' ELSE 'Mess' END AS docheadName, x.remark
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' AND x.docheadCode='2' ORDER BY e.enrolmentNo");
break;
case 4: // name correction for degree
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,d.docheadName
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN aaa_docheads AS d on x.docheadCode=d.docheadCode LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' ORDER BY e.enrolmentNo");
break;
case 5: // photo submission for degree
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,d.docheadName
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN aaa_docheads AS d on x.docheadCode=d.docheadCode LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' ORDER BY e.enrolmentNo");
break;
case 6: // arrival plan submission for convocation
$this->dbStmt = $this->pdo->query("SELECT enrolmentNo,nameEnglish,nameHindi,e.category,e.phoneNo,e.email,d.docheadName
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN aaa_docheads AS d on x.docheadCode=d.docheadCode LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' ORDER BY e.enrolmentNo");
break;
default:
$this->dbStmt = $this->pdo->query("SELECT *
FROM " .$this->db. ".aaa_enrolmentsv
WHERE batchYear=" .$_SESSION['batchYear']. " AND programID=$programID AND estatus='Enrolled' ORDER BY enrolmentNo");
break;
}
return $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
//***********************************************************************
//* aaa candidate functions *
//***********************************************************************
/*
function saveCandidate($programID,$roundNo,$degreeCode,$rollNo,$nameEnglish,$category,$pwdStatus,$gender,$dateBirth,$stateEligibility,$nationality,$programName,$allotedCategory,$allotedRank,$choiceNo,$quota,$roundName,$willingness,$status,$rcName,$alumni,$remark) {
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE candidateID=" .$this->tupleID)->rowCount()) {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET programID=$programID,batchYear=" .$_SESSION['batchYear']. ",roundNo=$roundNo,degreeCode='$degreeCode',rollNo='$rollNo',nameEnglish='$nameEnglish',category='$category',pwdStatus='$pwdStatus',gender='$gender',dateBirth='$dateBirth',stateEligibility='$stateEligibility',nationality='$nationality',programName='$programName',allotedCategory='$allotedCategory',allotedRank=$allotedRank,choiceNo=$choiceNo,quota='$quota',roundName='$roundName',willingness='$willingness',status='$status',rcName='$rcName',alumni='$alumni',remark='$remark'
WHERE candidateID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
} else {
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(instituteID,programID,batchYear,roundNo,degreeCode,rollNo,nameEnglish,category,pwdStatus,gender,dateBirth,stateEligibility,nationality,programName,allotedCategory,allotedRank,choiceNo,quota,roundName,willingness,status,rcName,alumni,remark)
VALUES($programID," .$_SESSION['batchYear']. ",$roundNo,'$degreeCode','$rollNo','$nameEnglish','$category','$pwdStatus','$gender','$dateBirth','$stateEligibility','$nationality','$programName','$allotedCategory',$allotedRank,$choiceNo,'$quota','$roundName','$willingness','$status','$rcName','$alumni','$remark')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $this->pdo->lastInsertId();
}
}
function deleteCandidate() {
$this->dbStmt = $this->pdo->query("SELECT * FROM " .$this->db. ".aaa_applicants WHERE rollNo=(SELECT rollNo FROM " .$this->tableID. " WHERE candidateID=" .$this->tupleID.")");
$row = $this->dbStmt->fetch(PDO::FETCH_ASSOC);
$studentID = $row['studentID'];
$status = $row['status'];
if ($studentID && $status == 'Enrolled') {
return 'Enrolled';
} else {
$this->sqlStmt = "DELETE FROM " .$this->db. ".aaa_students WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
$this->sqlStmt = "DELETE FROM " .$this->tableID. " WHERE candidateID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
$this->sqlStmt = "DELETE FROM erp_core.erp_users WHERE loginID=(SELECT rollNo FROM " .$this->tableID. " WHERE candidateID=" .$this->tupleID.")";
$this->pdo->exec($this->sqlStmt);
}
}
function setOfferedProgram($programName,$programID,$roundNo) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_candidates SET programID=$programID
WHERE batchYear=" .$_SESSION['batchYear']. " AND programName='$programName' AND roundNo=$roundNo";
$this->pdo->exec($this->sqlStmt);
}
function setOfferedCategory($aCategory,$oCategory,$roundNo) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_candidates SET offerCategory='$oCategory'
WHERE batchYear=" .$_SESSION['batchYear']. " AND allotedCategory='$aCategory' AND roundNo=$roundNo";
$this->pdo->exec($this->sqlStmt);
}
*/
//***********************************************************************
//* aaa student, parent, education functions *
//***********************************************************************
function saveStudent0($studentID,$nameEnglish,$dateBirth,$gender,$pwdStatus,$category,$nationality,$homeState,$remark) {
// check for student records, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->db. ".aaa_students WHERE studentID=$studentID")->rowCount()) {
// this is from the imported list from CCMT
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_students SET nameEnglish='$nameEnglish',dateBirth='$dateBirth',gender='$gender',pwdStatus='$pwdStatus',category='$category',nationality='$nationality',homeState=$homeState,remark='$remark'
WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
}
return $studentID;
}
function saveStudent($studentID,$nameEnglish,$nameHindi,$dateBirth,$gender,$bloodGroup,$pwdStatus,$maritalStatus,$category,$subCategory,$religion,$nationality,$aadhaarNo,$hhIncome,$phoneNo,$email,$homeAddress,$homePIN,$homeState,$homeDistrict,$area,$policeStation,$commAddress,$commPIN,$remark,$status) {
// check for student records, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->db. ".aaa_students WHERE studentID=$studentID")->rowCount()) {
// this is from the imported list from CCMT
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_students SET nameEnglish='$nameEnglish',nameHindi='$nameHindi',dateBirth='$dateBirth',gender='$gender',
bloodGroup='$bloodGroup',pwdStatus='$pwdStatus',subCategory='$subCategory',category='$category',religion='$religion',maritalStatus='$maritalStatus',nationality='$nationality',
aadhaarNo='$aadhaarNo',hhIncome=$hhIncome,homeAddress='$homeAddress',homePIN=$homePIN,homeState=$homeState,homeDistrict=$homeDistrict,commAddress='$commAddress',
commPIN=$commPIN,area=$area,policeStation='$policeStation',phoneNo='$phoneNo',email='$email',remark='$remark',status='$status'
WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
} else {
// this is direct enrollment at AAA counter
$this->sqlStmt = "INSERT INTO " .$this->db. ".aaa_students(nameEnglish,nameHindi,dateBirth,gender,bloodGroup,pwdStatus,subCategory,category,religion,maritalStatus,nationality,aadhaarNo,hhIncome,
homeAddress,homePIN,homeState,homeDistrict,commAddress,commPIN,area,policeStation,phoneNo,email,remark,status)
VALUES('$nameEnglish','$nameHindi','$dateBirth','$gender','$bloodGroup','$pwdStatus','$subCategory','$category','$religion','$maritalStatus','$nationality','$aadhaarNo',$hhIncome,
'$homeAddress',$homePIN,$homeState,$homeDistrict,'$commAddress',$commPIN,$area,'$policeStation','$phoneNo','$email','$remark','$status')";
$this->pdo->exec($this->sqlStmt);
$studentID = $this->pdo->lastInsertId();
}
return $studentID;
}
function saveParent($parentID,$studentID,$fatherName,$fatherProfession,$fatherPhone,$fatherEmail,$fatherAddress,$motherName,$motherProfession,$motherPhone,$motherEmail,$motherAddress,$guardianName,$guardianProfession,$relationWith,$guardianPhone,$guardianEmail,$guardianAddress) {
// check for student records, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->db. ".aaa_parents WHERE parentID=$parentID")->rowCount()) {
// this is from the imported list from CCMT
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_parents SET studentID=$studentID,fatherName='$fatherName',fatherProfession='$fatherProfession',fatherPhone='$fatherPhone',fatherEmail='$fatherEmail',fatherAddress='$fatherAddress',
motherName='$motherName',motherProfession='$motherProfession',motherPhone='$motherPhone',motherEmail='$motherEmail',motherAddress='$motherAddress',
guardianName='$guardianName',guardianProfession='$guardianProfession',relationWith='$relationWith',guardianPhone='$guardianPhone',guardianEmail='$guardianEmail',guardianAddress='$guardianAddress'
WHERE parentID=$parentID";
$this->pdo->exec($this->sqlStmt);
} else {
// this is direct enrolment at AAA counter
$this->sqlStmt = "INSERT INTO " .$this->db. ".aaa_parents(studentID,fatherName,fatherProfession,fatherPhone,fatherEmail,fatherAddress,motherName,motherProfession,motherPhone,motherEmail,motherAddress,
guardianName,guardianProfession,relationWith,guardianPhone,guardianEmail,guardianAddress)
VALUES($studentID,'$fatherName','$fatherProfession','$fatherPhone','$fatherEmail','$fatherAddress','$motherName','$motherProfession','$motherPhone','$motherEmail','$motherAddress',
'$guardianName','$guardianProfession','$relationWith','$guardianPhone','$guardianEmail','$guardianAddress')";
$this->pdo->exec($this->sqlStmt);
$parentID = $this->pdo->lastInsertId();
}
//echo $this->sqlStmt;
return $parentID;
}
function saveEducation($educationID,$studentID,$xGrade,$xYear,$xBoard,$xiiGrade,$xiiYear,$xiiBoard,$ugGrade,$ugYear,$ugInstitute,$pgGrade,$pgYear,$pgInstitute,$lastScore,$categoryRank) {
// check for student records, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->db. ".aaa_educations WHERE educationID=$educationID")->rowCount()) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_educations SET studentID=$studentID,xGrade='$xGrade',xYear=$xYear,xBoard='$xBoard',xiiGrade='$xiiGrade',xiiYear=$xiiYear,xiiBoard='$xiiBoard',ugGrade='$ugGrade',ugYear=$ugYear,ugInstitute='$ugInstitute',pgGrade='$pgGrade',pgYear=$pgYear,pgInstitute='$pgInstitute',lastScore=$lastScore,categoryRank=$categoryRank
WHERE educationID=$educationID";
$this->pdo->exec($this->sqlStmt);
} else {
// this is direct enrolment at AAA counter
$this->sqlStmt = "INSERT INTO " .$this->db. ".aaa_educations(studentID,xGrade,xYear,xBoard,xiiGrade,xiiYear,xiiBoard,ugGrade,ugYear,ugInstitute,pgGrade,pgYear,pgInstitute,lastScore,categoryRank)
VALUES($studentID,'$xGrade',$xYear,'$xBoard','$xiiGrade',$xiiYear,'$xiiBoard','$ugGrade',$ugYear,'$ugInstitute','$pgGrade',$pgYear,'$pgInstitute',$lastScore,$categoryRank)";
$this->pdo->exec($this->sqlStmt);
$educationID = $this->pdo->lastInsertId();
}
//echo $this->sqlStmt;
return $educationID;
}
function saveAccount($accountID,$studentID,$accountNo,$holderName,$bankName,$ifscCode) {
// check for student records, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->db. ".aaa_accounts WHERE accountID=$accountID")->rowCount()) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_accounts SET studentID=$studentID,accountNo='$accountNo',holderName='$holderName',bankName='$bankName',ifscCode='$ifscCode'
WHERE accountID=$accountID";
$this->pdo->exec($this->sqlStmt);
} else {
// this is direct enrolment at AAA counter
$this->sqlStmt = "INSERT INTO " .$this->db. ".aaa_accounts(studentID,accountNo,holderName,bankName,ifscCode)
VALUES($studentID,'$accountNo','$holderName','$bankName','$ifscCode')";
$this->pdo->exec($this->sqlStmt);
$educationID = $this->pdo->lastInsertId();
}
//echo $this->sqlStmt;
return $accountID;
}
//***********************************************************************
//* aaa applicant functions *
//***********************************************************************
function saveApplicant($rollNo,$studentID,$programID,$offerCategory,$offerRound,$remark) {
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE applicantID=" .$this->tupleID)->rowCount()) {
// interested applicants thru self registration
if ($offerRound) {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET rollNo='$rollNo',studentID=$studentID,programID=$programID,batchYear=" .$_SESSION['batchYear']. ",offerCategory='$offerCategory',offerRound=$offerRound,remark='$remark'
WHERE applicantID=" .$this->tupleID;
} else {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET dateResponse=now(),status='Interested'
WHERE applicantID=" .$this->tupleID;
}
$this->pdo->exec($this->sqlStmt);
} else {
// offered applicant thru manageOffer or edit candidature
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(rollNo,studentID,programID,batchYear,offerCategory,offerRound,remark,status)
VALUES('$rollNo',$studentID,$programID," .$_SESSION['batchYear']. ",'$offerCategory',$offerRound,'$remark','Offered')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $this->pdo->lastInsertId();
}
$data = $this->getStudent($studentID);
$userName = $data['nameEnglish']; // for password
$dob = $data['dateBirth']; // for password
$groupID = 6; // groupID of applicants from erp_groups
// insert login credentials
$this->sqlStmt = "INSERT INTO erp_core.erp_users(loginID,userName,groupID,authMode,secretCode)
values('$rollNo','$userName',$groupID,'MDB',password('$dob'))";
if ($this->pdo->exec($this->sqlStmt)) {
$userID = $this->pdo->lastInsertId();
// increment member count in group
$this->sqlStmt = "UPDATE erp_core.erp_groups SET memberCount=memberCount+1 WHERE groupID=$groupID";
$this->pdo->exec($this->sqlStmt);
// grant access privilege on aaaApplicant view
$intfaceID = $this->getIntfaceID('applicant');
$this->sqlStmt = "INSERT INTO erp_core.erp_uacls(userID,intfaceID,aclRead,aclInsert,aclUpdate,aclDelete)
values($userID,$intfaceID,1,0,1,0)";
$this->pdo->exec($this->sqlStmt);
$aclID = $this->pdo->lastInsertId();
} else {
$this->sqlStmt = "UPDATE erp_core.erp_users SET secretCode=password('$dob') WHERE loginID='$rollNo'";
$this->pdo->exec($this->sqlStmt);
}
//echo $this->sqlStmt;
}
//***********************************************************************
//* aaa upload functions *
//***********************************************************************
function saveUpload($studentID,$docheadCode,$fileName) {
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE studentID=$studentID AND docheadCode='$docheadCode'")->rowCount()) {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET fileName='$fileName' WHERE studentID=$studentID AND docheadCode='$docheadCode'";
$this->pdo->exec($this->sqlStmt);
} else{
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(studentID,docheadCode,fileName) VALUES($studentID,'$docheadCode','$fileName')";
$this->pdo->exec($this->sqlStmt);
// not needed as it is called on studentID not on uploadID...$this->tupleID = $this->pdo->lastInsertId();
}
//echo $this->sqlStmt;
}
//***********************************************************************
//* aaa exemption functions *
//***********************************************************************
function saveExemption($studentID,$docheadCode,$dateRemind,$remark) {
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE studentID=$studentID AND docheadCode='$docheadCode'")->rowCount() > 0) {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET dateRemind='$dateRemind',remark='$remark'
WHERE studentID=$studentID AND docheadCode='$docheadCode'";
$this->pdo->exec($this->sqlStmt);
} else {
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(studentID,docheadCode,dateRemind,remark)
VALUES($studentID,'$docheadCode','$dateRemind','$remark')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $studentID; // $this->pdo->lastInsertId();
}
}
function settleExemption($studentID,$docheadCode) {
if ($studentID && $docheadCode) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_exemptions
SET dateClear=curdate() WHERE studentID=$studentID AND docheadCode='$docheadCode'";
$this->pdo->exec($this->sqlStmt);
}
}
function exemptionSummary($programID) {
if ($programID) {
$this->dbStmt = $this->pdo->query("SELECT DISTINCT a.enrolmentNo,CONCAT(nameEnglish,' (',s.nameHindi,')') AS studentName,s.gender,s.category,s.phoneNo,s.email,h.docheadName
FROM " .$this->db. ".aaa_exemptions AS e INNER JOIN aaa_docheads AS h on e.docheadCode=h.docheadCode INNER JOIN " .$this->db. ".aaa_uploads AS u ON e.studentID=u.studentID AND u.docheadID=996 INNER JOIN " .$this->db. ".aaa_students AS s ON e.studentID=s.studentID INNER JOIN aaa_enrolments AS a ON s.studentID=a.studentID INNER JOIN " .$this->db. ".aaa_programs AS p ON a.programID=p.programID
WHERE a.batchYear=" .$_SESSION['batchYear']. " AND a.programID=$programID AND a.status='Enrolled' ORDER BY a.enrolmentNo");
$rows = $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
return $rows;
}
function selectDeficit($programID) {
if ($programID) {
$this->dbStmt = $this->pdo->query("SELECT DISTINCT x.studentID,e.enrolmentNo,e.nameEnglish
FROM " .$this->db. ".aaa_exemptions AS x LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON x.studentID=e.studentID
WHERE e.batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND e.estatus='Enrolled' ORDER BY e.enrolmentNo");
$rows = $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
return $rows;
}
//***********************************************************************
//* aaa enrolment functions *
//***********************************************************************
function saveEnrolment($enrolmentNo,$studentID,$programID,$enrolCategory,$hbookNo,$abookNo,$remark) {
// check for enrolment record, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE enrolmentID=" .$this->tupleID)->rowCount()) {
// update on enrolment table
$this->sqlStmt = "UPDATE " .$this->tableID. " SET studentID=$studentID,programID=$programID,batchYear=" .$_SESSION['batchYear']. ",enrolCategory='$enrolCategory',hbookNo=$hbookNo,abookNo='$abookNo',remark='$remark',status='Enrolled'
WHERE enrolmentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
} else { // rollNo=enrolmentNo by defaul that may change in second semester on merit basis
$dateEnrolment = date('Y-m-d');
$loginID = $_SESSION['loginID'];
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(enrolmentNo,studentID,programID,batchYear,enrolCategory,hbookNo,abookNo,remark,loginID,status)
VALUES('$enrolmentNo',$studentID,$programID," .$_SESSION['batchYear']. ",'$enrolCategory',$hbookNo,'$abookNo','$remark','$loginID','Enrolled')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $this->pdo->lastInsertId();
//echo $this->sqlStmt;
// increment rollno by 1
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_programs SET lastRollNo=lastRollNo+1 WHERE programID=$programID";
$this->pdo->exec($this->sqlStmt);
// update applicant status to enrolled
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_applicants SET status='Enrolled' WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
}
//echo $this->sqlStmt;
}
//***********************************************************************
//* aaa mcaip functions *
//***********************************************************************
function saveInsurance($spouseName,$spouseAge,$spousePhone,$spouseEmail,$spouseAddress,$spousePIN,$child1Name,$child1Age,$child1Gender,$child1Phone,$child1Email,$child1Address,$child1PIN,$child2Name,$child2Age,$child2Gender,$child2Phone,$child2Email,$child2Address,$child2PIN,$knownDiseases) {
// check for insurance record, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE enrolmentID=" .$this->tupleID)->rowCount()) {
// update on enrolment table
$this->sqlStmt = "UPDATE " .$this->tableID. " SET spouseName='$spouseName',spouseAge=$spouseAge,spousePhone='$spousePhone',spouseEmail='$spouseEmail',spouseAddress='$spouseAddress',spousePIN=$spousePIN,child1Name='$child1Name',child1Age=$child1Age,child1Gender='$child1Gender',child1Phone='$child1Phone',child1Email='$child1Email',child1Address='$child1Address',child1PIN=$child1PIN,child2Name='$child2Name',child2Age=$child2Age,child2Gender='$child2Gender',child2Phone='$child2Phone',child2Email='$child2Email',child2Address='$child2Address',child2PIN=$child2PIN,knownDiseases='$knownDiseases'
WHERE enrolmentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
} else {
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(enrolmentID,spouseName,spouseAge,spousePhone,spouseEmail,spouseAddress,spousePIN,child1Name,child1Age,child1Gender,child1Phone,child1Email,child1Address,child1PIN,child2Name,child2Age,child2Gender,child2Phone,child2Email,child2Address,child2PIN,knownDiseases)
VALUES(" .$this->tupleID. ",'$spouseName',$spouseAge,'$spousePhone','$spouseEmail','$spouseAddress',$spousePIN,'$child1Name',$child1Age,'$child1Gender','$child1Phone','$child1Email','$child1Address',$child1PIN,'$child2Name',$child2Age,'$child2Gender','$child2Phone','$child2Email','$child2Address',$child2PIN,'$knownDiseases')";
$this->pdo->exec($this->sqlStmt);
//$this->tupleID = $this->pdo->lastInsertId();
}
}
function getInsurance($enrolmentID) {
if ($enrolmentID) {
$this->dbStmt = $this->pdo->query("SELECT e.*,m.* FROM " .$this->db. ".aaa_enrolmentsv AS e LEFT JOIN " .$this->db. ".aaa_mcaips AS m ON e.enrolmentID=m.enrolmentID WHERE e.enrolmentID=$enrolmentID");
return $this->dbStmt->fetch(PDO::FETCH_ASSOC);
}
}
function listInsurances() {
$this->sqlStmt = "SELECT e.enrolmentNo,e.nameEnglish, m.*
FROM " .$this->db. ".aaa_mcaips AS m LEFT JOIN aaa_enrolmentsv AS e ON m.enrolmentID=e.enrolmentID ORDER BY mcaipID";
$this->dbStmt = $this->pdo->query($this->sqlStmt);
return $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
//***********************************************************************
//* aaa sliding functions *
//***********************************************************************
function saveSliding($enrolmentNo,$studentID,$programID,$enrolCategory,$hbookNo,$abookNo,$hostelID,$roomID,$remark) {
// nothing to do with student record
// just allot new enrollment no. under new program
// tag old enrollment no. with status as slided
// check for enrolment record, if exists, update otherwise insert
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE enrolmentID=" .$this->tupleID)->rowCount()) {
// update enrolment table
$programCode = $this->getProgramCodeByProgramID($programID);
$this->sqlStmt = "UPDATE " .$this->tableID. " SET remark=CONCAT(remark,' [slided to $programCode $enrolmentNo]'),status='Slided'
WHERE enrolmentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
// new enrolment
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(enrolmentNo,studentID,programID,batchYear,enrolCategory,hbookNo,abookNo,loginID,remark)
VALUES('$enrolmentNo',$studentID,$programID," .$_SESSION['batchYear']. ",'$enrolCategory',$hbookNo,'$abookNo','" .$_SESSION['loginID']. "','$remark')";
$this->pdo->exec($this->sqlStmt);
$enrolmentID = $this->pdo->lastInsertId();
//increment last rollno if successful
if ($enrolmentID) {
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_programs SET lastRollNo=lastRollNo+1 WHERE programID=$programID";
$this->pdo->exec($this->sqlStmt);
}
}
}
//***********************************************************************
//* aaa withdrawal functions *
//***********************************************************************
function saveWithdrawal($studentID,$dateWithdraw,$withdrawReason,$accountNo,$holderName,$bankName,$ifscCode,$remark) {
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE withdrawID=" .$this->tupleID)->rowCount()) {
$this->sqlStmt = "UPDATE " .$this->tableID. " SET studentID=$studentID,dateWithdraw='$dateWithdraw',withdrawReason='$withdrawReason',accountNo='$accountNo',holderName='$holderName',bankName='$bankName',ifscCode='$ifscCode',remark='$remark',loginID='" .$_SESSION['loginID']. "'
WHERE withdrawID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
} else {
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(studentID,dateWithdraw,withdrawReason,accountNo,holderName,bankName,ifscCode,remark,loginID)
VALUES($studentID,'$dateWithdraw','$withdrawReason','$accountNo','$holderName','$bankName','$ifscCode','$remark','" .$_SESSION['loginID']. "')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $this->pdo->lastInsertId();
// update enrolment record
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_enrolments SET status='Withdrawn',remark='$remark' WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
// update student record
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_students SET status='Withdrawn',remark='$remark' WHERE studentID=$studentID";
$this->pdo->exec($this->sqlStmt);
}
}
function deleteWithdrawal($studentID,$remark) {
$this->sqlStmt = "DELETE FROM " .$this->tableID. " WHERE studentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
// restore original status in enrollment table
$enrolmentID = $this->getEnrolmentIDByStudentID($this->tupleID);
$this->sqlStmt = "UPDATE " .$this->db. ".aaa_enrolments SET status='Enrolled',remark=CONCAT(remark, ' $remark', '\ndated ', curdate()) WHERE enrolmentID=$enrolmentID";
$this->pdo->exec($this->sqlStmt);
}
function listWithdrawals($programID) {
$this->sqlStmt = "SELECT CONCAT('<img src=\'uploads/photos/', u.fileName, '\' width=\'30\' height=\'40\'>') AS photo,enrolmentNo,
w.dateWithdraw,w.withdrawReason,e.accountNo,e.holderName,e.bankName,e.ifscCode,w.remark,dateEnrolment,
p.programCode,batchYear,enrolCategory,nameEnglish,e.nameHindi,gender,dateBirth,bloodGroup,
pwdStatus,category,subCategory,religion,maritalStatus,nationality,aadhaarNo,homeAddress,homePIN,s.stateName,d.districtName,
commAddress,commPIN,CASE WHEN e.area = 1 THEN 'Urban' ELSE 'Rural' END AS nativeArea,policeStation,e.phoneNo,e.email,
xGrade,xYear,xBoard,xiiGrade,xiiYear,xiiBoard,ugGrade,ugYear,ugInstitute,pgGrade,pgYear,pgInstitute,lastScore,
fatherName,fatherProfession,fatherPhone,fatherEmail,fatherAddress,motherName,motherProfession,motherPhone,motherEmail,
motherAddress,guardianName, guardianProfession,relationWith,guardianPhone,guardianEmail,guardianAddress,e.enrolmentID,withdrawID
FROM " .$this->db. ".aaa_withdrawals AS w LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON w.studentID=e.studentID INNER JOIN " .$this->db. ".aaa_uploads AS u ON e.studentID=u.studentID AND u.docheadID=996 INNER JOIN " .$this->db. ".aaa_programs AS p ON e.programID=p.programID INNER JOIN erp_core.erp_states AS s ON e.homeState=s.stateID INNER JOIN erp_core.erp_districts AS d ON e.homeDistrict=d.districtID
WHERE e.batchYear=" .$_SESSION['batchYear']. ($programID ? " AND e.programID=$programID" : ''). " ORDER BY e.enrolmentNo";
$this->dbStmt = $this->pdo->query($this->sqlStmt);
$rows = $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
return $rows;
}
function selectWithdrawal($programID) {
if ($programID) {
$this->sqlStmt = "SELECT w.*,programID,enrolmentNo,nameEnglish
FROM " .$this->db. ".aaa_withdrawals AS w LEFT JOIN " .$this->db. ".aaa_enrolmentsv AS e ON w.studentID=e.studentID
WHERE batchYear=" .$_SESSION['batchYear']. " AND programID=$programID ORDER BY enrolmentNo";
$this->dbStmt = $this->pdo->query($this->sqlStmt);
return $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
}
//***********************************************************************
//* aaa branch change functions *
//***********************************************************************
function saveBranchChange($enrolmentNo,$programID,$remark) {
$loginID = $_SESSION['loginID'];
// check for enrolment record, if exists, update
if ($this->pdo->query("SELECT * FROM " .$this->tableID. " WHERE enrolmentID=" .$this->tupleID)->rowCount()) {
// update on enrolment table
$this->sqlStmt = "UPDATE " .$this->tableID. " SET remark='Changed to $enrolmentNo',status='Changed'
WHERE enrolmentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
// create new record
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(enrolmentNo,programID,remark,status,loginID,studentID,batchYear,enrolCategory)
SELECT '$enrolmentNo',$programID,'$remark','Enrolled','$loginID',studentID,batchYear,enrolCategory FROM " .$this->tableID. " WHERE enrolmentID=" .$this->tupleID;
$this->pdo->exec($this->sqlStmt);
//$this->tupleID = $this->pdo->lastInsertId();
}
}
function listBranchChanges($programID=0) {
if ($programID) {
$this->sqlStmt = "SELECT CONCAT('<img src=\'uploads/photos/', u.fileName, '\' width=\'30\' height=\'40\'>') AS photo,enrolmentNo,dateEnrolment,
p.programCode,batchYear,enrolCategory,nameEnglish,e.nameHindi,gender,dateBirth,
bloodGroup,pwdStatus,category,subCategory,religion,maritalStatus,nationality,aadhaarNo,homeAddress,homePIN,s.stateName,
d.districtName,commAddress,commPIN,CASE WHEN e.area = 1 THEN 'Urban' ELSE 'Rural' END AS nativeArea,policeStation,e.phoneNo,e.email,xGrade,xYear,xBoard,xiiGrade,xiiYear,xiiBoard,
ugGrade,ugYear,ugInstitute,pgGrade,pgYear,pgInstitute,lastScore,fatherName,fatherProfession,fatherPhone,fatherEmail,
fatherAddress,motherName,motherProfession,motherPhone,motherEmail,motherAddress,guardianName,
guardianProfession,relationWith,guardianPhone,guardianEmail,guardianAddress
FROM " .$this->db. ".aaa_enrolmentsv AS e LEFT JOIN " .$this->db. ".aaa_uploads AS u ON e.studentID=u.studentID AND u.docheadID=996 INNER JOIN " .$this->db. ".aaa_programs AS p ON e.programID=p.programID INNER JOIN erp_core.erp_states AS s ON e.homeState=s.stateID INNER JOIN erp_core.erp_districts AS d ON e.homeDistrict=d.districtID
WHERE e.batchYear=" .$_SESSION['batchYear']. " AND e.programID=$programID AND estatus='Changed' ORDER BY e.enrolmentNo";
$this->dbStmt = $this->pdo->query($this->sqlStmt);
$rows = $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
return $rows;
}
//***********************************************************************
//* aaa miscellaneous functions *
//***********************************************************************
function saveOTP($loginID,$groupID=6,$authMode='MDB',$otpSecret=0,$aclRead,$aclInsert=1,$aclUpdate=1,$aclDelete=0) {
if ($this->pdo->query("SELECT * FROM erp_core.erp_users WHERE loginID='$loginID'")->rowCount()) {
$this->sqlStmt = "UPDATE erp_core.erp_users SET loginID='$loginID',groupID=$groupID,authMode='$authMode',otpSecret=$otpSecret,aclRead=$aclRead,aclInsert=$aclInsert,aclUpdate=$aclUpdate,aclDelete=$aclDelete
WHERE loginID='$loginID'";
} else {
$this->sqlStmt = "INSERT INTO erp_core.erp_users(loginID,groupID,authMode,otpSecret,aclRead,aclInsert,aclUpdate,aclDelete)
VALUES('$loginID',$groupID,'$authMode',$otpSecret,$aclRead,$aclInsert,$aclUpdate,$aclDelete)";
}
$this->pdo->exec($this->sqlStmt);
}
function dumpCSV($SN,$RollNo,$Name,$Category,$PwD,$Gender,$DOB,$StateEligibility,$Nationality,$ProgramAllocated,$AllocatedCat,$AllocatedRank,$ChoiceNo,$Quota,$Round,$Status,$RCName,$RemarksbyRC) {
if ($this->pdo->query("SELECT * FROM aaa_candidates WHERE RollNo='" .$this->tupleID. "'")->rowCount()) {
$this->sqlStmt = "UPDATE aaa_cadidates SET RollNo='$RollNo',Name='$Name',Category='$Category',Gender='$Gender',DOB='$DOB',StateEligibility=$StateEligibility',
Nationality='$Nationality',ProgramAllocated='$ProgramAllocated',AllocatedCat='$AllocatedCat',AllocatedRank='$AllocatedRank',ChoiceNo='$ChoiceNo',Quota='$Quota',
$Round,$Status,$RCName,$RemarksbyRCphoneNo='$phoneNo',email='$email',homeAddress='$homeAddress',homeDistrict=$homeDistrict,homePIN=$homePIN,commAddress='$commAddress',
WHERE RollNo='$RollNo'";
$this->pdo->exec($this->sqlStmt);
} else{
$this->sqlStmt = "INSERT INTO " .$this->tableID. "(rollNo,nameEnglish,nameHindi,gender,dateBirth,phoneNo,email,homeAddress,homeDistrict,homeState,homePIN,commAddress,
subCategory,category,religion,nationality,pwdStatus,bloodGroup,aadhaarNo,batchYear,programID,status,
fatherAddress,fatherAddress,fatherAddress,fatherAddress,fatherAddress,motherName,motherProfession,motherPhone,motherEmail,motherAddress,guardianName,guardianProfession,guardianPhone,guardianEmail,guardianAddress)
VALUES('$rollNo','$nameEnglish','$nameHindi','$gender','$dateBirth','$phoneNo','$email','$homeAddress',$homeDistrict,$homeState,$homePIN,'$commAddress',
'$subCategory','$category','$religion','$nationality','$pwdStatus','$bloodGroup','$aadhaarNo'," .$_SESSION['batchYear']. ",$programID,'Offered',
'$fatherName','$fatherProfession','$fatherPhone','$fatherEmail','$fatherAddress','$motherName','$motherProfession','$motherPhone','$motherEmail','$motherAddress','$guardianName','$guardianProfession','$guardianPhone','$guardianEmail','$guardianAddress')";
$this->pdo->exec($this->sqlStmt);
$this->tupleID = $this->pdo->lastInsertId();
}
}
//***********************************************************************
//* aaa travelplan functions *
//***********************************************************************
function getTravelplan($enrolmentNo) {
if ($enrolmentNo) {
$this->dbStmt = $this->pdo->query("SELECT g.*,p.programCode,t.planID,t.dateArrival,t.dateDeparture,t.campusStay,t.companion,t.remark
FROM " .$this->db. ".aaa_graduands AS g LEFT JOIN " .$this->db. ".aaa_travelplans AS t ON g.enrolmentNo=t.enrolmentNo LEFT JOIN " .$this->db. ".aaa_programs AS p ON g.programID=p.programID
WHERE batchYear=" .$_SESSION['batchYear']. " AND g.enrolmentNo='$enrolmentNo'");
return $this->dbStmt->fetch(PDO::FETCH_ASSOC);
}
}
function listTravelplans($programID) {
if ($programID) {
$this->dbStmt = $this->pdo->query("SELECT CONCAT('<img src=\'uploads/photos/', lcase(t.enrolmentNo), '.jpg\' width=\'30\' height=\'40\'>') AS photo,
t.enrolmentNo,CONCAT(nameEnglish,' (',nameHindi,')') AS name,contactNo,email,dateArrival,dateDeparture,campusStay,companion,t.remark
FROM " .$this->db. ".aaa_travelplans AS t LEFT JOIN " .$this->db. ".aaa_graduands AS g ON t.enrolmentNo=g.enrolmentNo
WHERE batchYear=" .$_SESSION['batchYear']. " AND programID=$programID ORDER BY t.enrolmentNo");
return $this->dbStmt->fetchAll(PDO::FETCH_ASSOC);
}
}
}
?>