|
Server IP : 172.26.0.195 / Your IP : 18.191.107.181 Web Server : Apache System : Linux 43-205-77-33.cprapid.com 3.10.0-1160.119.1.el7.tuxcare.els2.x86_64 #1 SMP Mon Jul 15 12:09:18 UTC 2024 x86_64 User : jnclnmuac ( 1026) PHP Version : 8.0.30 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON Directory (0755) : /home/jnclnmuac/public_html/web/../web/../jobs/../cas/application/models/admin/ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
<?php
/**
* Model For Handling All DB Operations Related To User
*
* @author Softpro India Pvt. Ltd.
*/
defined('BASEPATH') OR exit('No direct script access allowed');
class StaffManagement extends CI_Model {
function createNewEmployeeLoginDetail($newLoginDetailInfo) {
$this->db->insert('tbl_logindetails', $newLoginDetailInfo);
return $this->db->insert_id();
}
function createNewEmployeeProfileDetail($newProfileDetailInfo) {
$this->db->insert('tbl_profile', $newProfileDetailInfo);
return $this->db->insert_id();
}
function createNewEmployeeStaffMemberDetail($newStaffMemberInfo) {
$this->db->insert('tbl_staff_members', $newStaffMemberInfo);
return $this->db->insert_id();
}
function createNewEmployeeEducationalQualificationDetail($newStaffEduQaliInfo) {
$this->db->insert('staff_edu_quali_info', $newStaffEduQaliInfo);
return $this->db->insert_id();
}
function getAllEmployees() {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,TIPIPAN.tipi_number PANNumber,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_id_proof_info TIPIPAN', 'TSM.smember_id = TIPIPAN.tipi_for');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where("TIPIPAN.tipsm_id = (SELECT tipsm_id FROM tbl_id_proof_sub_master WHERE tipm_id = (SELECT tipm_id FROM tbl_id_proof_master WHERE tipm_code = '1') and (tipsm_for = 'E'))");
$this->db->order_by("TLD.tld_created_on", "desc");
return $this->db->get();
}
function getAllWorkingEmployees() {
$this->db->select("*");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_designation_master TDESIGM', 'TPRFL.desig_id = TDESIGM.desig_id');
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->order_by("TLD.tld_created_on", "desc");
return $this->db->get();
}
function getAllDeletedEmployees() {
$this->db->select("*");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->where("TLD.tld_delete_status", "T");
$this->db->order_by("TLD.tld_created_on", "desc");
return $this->db->get();
}
function getAllDistinctStudentEntryOperators() {
$this->db->select("*");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->where("TSM.smember_id IN (select DISTINCT(tspi_added_by) from tbl_student_personal_info UNION SELECT DISTINCT(tspi_updated_by) from tbl_student_personal_info)");
$this->db->order_by("TLD.tld_created_on", "desc");
return $this->db->get();
}
function getAllDistinctFeeEntryOperators() {
$this->db->select("*");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->where("TSM.smember_id IN (select DISTINCT(frm_generated_by) from fee_receipt_mst UNION SELECT DISTINCT(frm_updated_by) from fee_receipt_mst)");
$this->db->order_by("TLD.tld_created_on", "desc");
return $this->db->get();
}
function getEmployeeInfoBy($smember_id) {
$this->db->select("*,TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,TPRFL.tprfl_appointment_type,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_id,TSDM.sub_dept_name,TDM.dept_id,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,TIPIPAN.tipi_number PANNumber,"
. "TDDI.tddi_id,TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('staff_edu_quali_info SEQI', 'TSM.smember_id = SEQI.smember_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TPRFL.tprfl_comm_state = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TPRFL.tprfl_perma_state = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_id_proof_info TIPIPAN', 'TSM.smember_id = TIPIPAN.tipi_for');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where("TIPIPAN.tipsm_id = (SELECT tipsm_id FROM tbl_id_proof_sub_master WHERE tipm_id = (SELECT tipm_id FROM tbl_id_proof_master WHERE tipm_code = '1') and (tipsm_for = 'E'))");
$this->db->where('TSM.smember_id', $smember_id);
return $this->db->get();
}
function getProfileInfoBy($profile_id) {
$this->db->select("TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,"
. "TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name");
$this->db->from('tbl_profile TPRFL');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->where("TPRFL.tprfl_id", $profile_id);
return $this->db->get();
}
function getProfileInfoByEmail($tprfl_email) {
$this->db->select("TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,"
. "TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name");
$this->db->from('tbl_profile TPRFL');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->where("TPRFL.tprfl_email", $tprfl_email);
return $this->db->get();
}
function getProfileInfoByMobile($tprfl_mobile_no) {
$this->db->select("TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,"
. "TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name");
$this->db->from('tbl_profile TPRFL');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->where("TPRFL.tprfl_mobile_no", $tprfl_mobile_no);
return $this->db->get();
}
function getLoginDetailInfoBySigninId($tld_signinid) {
$this->db->select("TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,"
. "TLD.tld_is_first_login,TSM.smember_id");
$this->db->from('tbl_logindetails TLD');
$this->db->join('tbl_staff_members TSM', 'TSM.tld_id = TLD.tld_id');
$this->db->where("TLD.tld_signinid", $tld_signinid);
return $this->db->get();
}
function getEmployeeInfoByCodeId($smember_code) {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code");
$this->db->from('tbl_staff_members TSM');
$this->db->where("TSM.smember_code", $smember_code);
return $this->db->get();
}
function isEmployeeEmailSafeUpdate($tprfl_id, $emp_email) {
$this->db->select("*");
$this->db->from('tbl_profile');
$this->db->where('tprfl_email', $emp_email);
$this->db->where('tprfl_id != ' . $tprfl_id);
$result = $this->db->get()->result();
if (sizeof($result)) {
return FALSE;
} else {
return TRUE;
}
}
function isEmployeeMobileSafeUpdate($tprfl_id, $emp_mobile) {
$this->db->select("*");
$this->db->from('tbl_profile');
$this->db->where('tprfl_mobile_no', $emp_mobile);
$this->db->where('tprfl_id != ' . $tprfl_id);
$result = $this->db->get()->result();
if (sizeof($result)) {
return FALSE;
} else {
return TRUE;
}
}
function isEmployeeSigninIdSafeUpdate($tld_id, $signin_id) {
$this->db->select("*");
$this->db->from('tbl_logindetails');
$this->db->where('tld_signinid', $signin_id);
$this->db->where('tld_id != ' . $tld_id);
$result = $this->db->get()->result();
if (sizeof($result)) {
return FALSE;
} else {
return TRUE;
}
}
function isEmployeeCodeIdSafeUpdate($smember_id, $emp_code_id) {
$this->db->select("*");
$this->db->from('tbl_staff_members');
$this->db->where('smember_code', $emp_code_id);
$this->db->where('smember_id != ' . $smember_id);
$result = $this->db->get()->result();
if (sizeof($result)) {
return FALSE;
} else {
return TRUE;
}
}
function updateLoginDetailsInfo($loginDetailUpdateInfo) {
$this->db->where('tld_id', $loginDetailUpdateInfo['tld_id']);
return $this->db->update('tbl_logindetails', $loginDetailUpdateInfo);
}
function updateProfileInfo($profileDetailUpdateInfo) {
$this->db->where('tprfl_id', $profileDetailUpdateInfo['tprfl_id']);
return $this->db->update('tbl_profile', $profileDetailUpdateInfo);
}
function updateEducationalQualificationInfo($educationalQualificationUpdateInfo) {
$this->db->where('seqi_id', $educationalQualificationUpdateInfo['seqi_id']);
return $this->db->update('staff_edu_quali_info', $educationalQualificationUpdateInfo);
}
function updateStaffMemberDetail($staffMemberDetailUpdateInfo) {
$this->db->where('smember_id', $staffMemberDetailUpdateInfo['smember_id']);
return $this->db->update('tbl_staff_members', $staffMemberDetailUpdateInfo);
}
function getActiveEntryOperators($branch_id) {
$sql = "SELECT DISTINCT(TSPI.tspi_added_by)USER_ID, CONCAT(TPA.tprfl_firstname,' ',TPA.tprfl_lastname)USER_NAME "
. "FROM tbl_student_personal_info TSPI, tbl_staff_members TSMA, tbl_profile TPA, tbl_logindetails TLDA "
. "WHERE TSPI.tspi_added_by = TSMA.smember_id AND TSMA.tprfl_id = TPA.tprfl_id AND TSMA.tld_id = TLDA.tld_id "
. "AND TLDA.branch_id = " . $branch_id . " GROUP BY TSPI.tspi_added_by";
return $this->db->query($sql);
}
/* Functions For Reporting */
function getStaffMiscellaneousReport($codeId = '', $firstName = '', $lastName = '', $gender = '', $department = '', $subDepartment = '', $desinationCategory = '', $designation = '', $ugcNETStatus = '', $phdStatus = '', $appointmentType = '', $startDate = '', $endDate = '', $rangeAppliedWith) {
$this->db->select("*,TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,TPRFL.tprfl_appointment_type,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,TIPIPAN.tipi_number PANNumber,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('staff_edu_quali_info SEQI', 'TSM.smember_id = SEQI.smember_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_id_proof_info TIPIPAN', 'TSM.smember_id = TIPIPAN.tipi_for');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where("TIPIPAN.tipsm_id = (SELECT tipsm_id FROM tbl_id_proof_sub_master WHERE tipm_id = (SELECT tipm_id FROM tbl_id_proof_master WHERE tipm_code = '1') and (tipsm_for = 'E'))");
$this->db->where("TSM.smember_code LIKE '%" . $codeId . "%'");
$this->db->where("TPRFL.tprfl_firstname LIKE '%" . $firstName . "%'");
$this->db->where("TPRFL.tprfl_lastname LIKE '%" . $lastName . "%'");
$this->db->where("TPRFL.tprfl_gender LIKE '%" . $gender . "%'");
$this->db->where("TPRFL.tprfl_appointment_type LIKE '%" . $appointmentType . "%'");
$this->db->where("SEQI.seqi_net_status LIKE '%" . $ugcNETStatus . "%'");
$this->db->where("SEQI.seqi_phd_status LIKE '%" . $phdStatus . "%'");
$this->db->where("TLD.tld_delete_status", 'F');
/* Department Wise Filter */
if ($department != '') {
$this->db->where("TDM.dept_id", $department);
}
/* Department Wise Filter */
/* Sub-Department Wise Filter */
if ($subDepartment != '') {
$this->db->where("TLD.sub_dept_id", $subDepartment);
}
/* Sub-Department Wise Filter */
/* Designation Category Wise Filter */
if ($desinationCategory != '') {
$this->db->where("DCM.dcm_id", $desinationCategory);
}
/* Designation Category Wise Filter */
/* Designation Wise Filter */
if ($designation != '') {
$this->db->where("TPRFL.desig_id", $designation);
}
/* Designation Wise Filter */
/* Dates Combination */
if ($startDate != '' && $endDate != '' && $rangeAppliedWith != '') {
if ($rangeAppliedWith == "JNG") {
$this->db->where("TPRFL.tprfl_doj >= '" . $startDate . "' && TPRFL.tprfl_doj <= '" . $endDate . "'");
} else {
$this->db->where("TPRFL.tprfl_dor >= '" . $startDate . "' && TPRFL.tprfl_dor <= '" . $endDate . "'");
}
} else if ($startDate == '' && $endDate != '' && $rangeAppliedWith != '') {
if ($rangeAppliedWith == "JNG") {
$this->db->where("TPRFL.tprfl_doj <= '" . $endDate . "'");
} else {
$this->db->where("TPRFL.tprfl_dor <= '" . $endDate . "'");
}
} else if ($startDate != '' && $endDate == '' && $rangeAppliedWith != '') {
if ($rangeAppliedWith == "JNG") {
$this->db->where("TPRFL.tprfl_doj >= '" . $startDate . "'");
} else {
$this->db->where("TPRFL.tprfl_dor >= '" . $startDate . "'");
}
}
/* Dates Combination */
$this->db->order_by("TPRFL.tprfl_created_on", "desc");
return $this->db->get();
}
/* Functions For Reporting */
/* Funtions for supervisors-subordinates modules */
function createNewSupervisorAssignment($newSupervisorAssignmentInfo) {
$this->db->insert('tbl_supervisors', $newSupervisorAssignmentInfo);
return $this->db->insert_id();
}
function getAllSupervisorsAndSubordinates() {
$this->db->select("TSUP.sup_id,TSUP.sup_smember_id,TSUP.sub_smember_id,TSUP.sup_assigned_by,TSUP.sup_assigned_on,TSUP.sup_updated_by,TSUP.sup_updated_on,"
. "CONCAT(TPRFLSUP.tprfl_firstname,' ',TPRFLSUP.tprfl_lastname) supervisorName,"
. "CONCAT(TPRFLSUB.tprfl_firstname,' ',TPRFLSUB.tprfl_lastname) subordinateName,"
. "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
. "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
$this->db->from('tbl_supervisors TSUP');
$this->db->join('tbl_staff_members TSMSUP', 'TSUP.sup_smember_id = TSMSUP.smember_id');
$this->db->join('tbl_profile TPRFLSUP', 'TSMSUP.tprfl_id = TPRFLSUP.tprfl_id');
$this->db->join('tbl_staff_members TSMSUB', 'TSUP.sub_smember_id = TSMSUB.smember_id');
$this->db->join('tbl_profile TPRFLSUB', 'TSMSUB.tprfl_id = TPRFLSUB.tprfl_id');
$this->db->join('tbl_staff_members TSMA', 'TSUP.sup_assigned_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_staff_members TSMU', 'TSUP.sup_updated_by = TSMU.smember_id');
$this->db->join('tbl_profile TPRFLU', 'TSMU.tprfl_id = TPRFLU.tprfl_id');
$this->db->order_by("TSUP.sup_assigned_on", "desc");
return $this->db->get();
}
function getAllSupervisorEmployees() {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TLD.tld_delete_status,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where('TLD.tld_is_head', 'T');
$this->db->where('TLD.tld_working_status', 'A');
$this->db->order_by("TPRFL.tprfl_doj", "desc");
return $this->db->get();
}
function getAllWorkingEmployeesUnderGivenDesignation($desig_id) {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where('TPRFL.desig_id', $desig_id);
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->order_by("TPRFL.tprfl_doj", "desc");
return $this->db->get();
}
function getAllWorkingEmployeesUnderGivenRole($role_id) {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where('TRM.role_id', $role_id);
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->order_by("TPRFL.tprfl_doj", "desc");
return $this->db->get();
}
function getAllWorkingEmployeesUnderGivenSubDepartment($sub_dept_id) {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where('TLD.sub_dept_id', $sub_dept_id);
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->order_by("TPRFL.tprfl_doj", "desc");
return $this->db->get();
}
function getAllWorkingEmployeesUnderGivenSubDepartmentAndDesignationType($sub_dept_id, $desig_type) {
$this->db->select("TSM.smember_id,TSM.role_id,TSM.tprfl_id,TSM.tld_id,TSM.smember_code,TPRFL.tprfl_id,TPRFL.tprfl_firstname,TPRFL.tprfl_lastname,TPRFL.tprfl_gender,TPRFL.tprfl_comm_state,TPRFL.tprfl_comm_city,"
. "TPRFL.tprfl_comm_full_address,TPRFL.tprfl_perma_state,TPRFL.tprfl_perma_city,TPRFL.tprfl_perma_full_address,TPRFL.tprfl_mobile_no,"
. "TPRFL.tprfl_tel_no,TPRFL.tprfl_email,TPRFL.tprfl_dob,TPRFL.desig_id,TPRFL.tprfl_created_on,TPRFL.tprfl_updated_on,TPRFL.tprfl_doj,TPRFL.tprfl_dor,"
. "TLD.tld_id,TLD.tld_signinid,TLD.branch_id,TLD.tld_is_head,TLD.tld_working_status,TLD.tld_web_status,"
. "TLD.tld_login_status,TLD.tld_created_by,TLD.tld_created_on,TLD.tld_updated_by,TLD.tld_updated_on,TLD.tld_delete_status,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "TLD.tld_is_first_login,TDEM.desig_name designatioName,TSDM.sub_dept_name,TDM.dept_name,TRM.role_name,TSBM.branch_name,"
. "TCMC.city_name commCityName, TSTMC.state_name commStateName,TCMR.city_name residCityName, TSTMR.state_name residStateName,"
. "TDDI.tddi_doc_file_path staff_photograph,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
$this->db->from('tbl_staff_members TSM');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_role_master TRM', 'TSM.role_id = TRM.role_id');
$this->db->join('tbl_designation_master TDEM', 'TPRFL.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_sms_branch_master TSBM', 'TLD.branch_id = TSBM.branch_id');
$this->db->join('tbl_city_master TCMC', 'TPRFL.tprfl_comm_city = TCMC.city_id');
$this->db->join('tbl_states_master TSTMC', 'TCMC.state_id = TSTMC.state_id');
$this->db->join('tbl_city_master TCMR', 'TPRFL.tprfl_perma_city = TCMR.city_id');
$this->db->join('tbl_states_master TSTMR', 'TCMR.state_id = TSTMR.state_id');
$this->db->join('tbl_document_document_info TDDI', 'TSM.smember_id = TDDI.tddi_mapping_id');
$this->db->join('tbl_document_master TDOCM', 'TDDI.tddi_doc_id = TDOCM.doc_id');
$this->db->join('tbl_staff_members TSMA', 'TLD.tld_created_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->where('TDOCM.doc_flag', 'PP');
$this->db->where('TLD.sub_dept_id', $sub_dept_id);
$this->db->where('DCM.dcm_id', $desig_type);
$this->db->where("TLD.tld_working_status != 'L'");
$this->db->where("TLD.tld_delete_status", 'F');
$this->db->order_by("TPRFL.tprfl_doj", "desc");
return $this->db->get();
}
public function getSupervisorInfoBySubordinate($subordinate_smember_id) {
$this->db->select("TSUP.sup_id,TSUP.sup_smember_id,TSUP.sub_smember_id,TSUP.sup_assigned_by,TSUP.sup_assigned_on,TSUP.sup_updated_by,TSUP.sup_updated_on,"
. "CONCAT(TPRFLSUP.tprfl_firstname,' ',TPRFLSUP.tprfl_lastname) supervisorName,"
. "CONCAT(TPRFLSUB.tprfl_firstname,' ',TPRFLSUB.tprfl_lastname) subordinateName,"
. "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
. "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
$this->db->from('tbl_supervisors TSUP');
$this->db->join('tbl_staff_members TSMSUP', 'TSUP.sup_smember_id = TSMSUP.smember_id');
$this->db->join('tbl_profile TPRFLSUP', 'TSMSUP.tprfl_id = TPRFLSUP.tprfl_id');
$this->db->join('tbl_staff_members TSMSUB', 'TSUP.sub_smember_id = TSMSUB.smember_id');
$this->db->join('tbl_profile TPRFLSUB', 'TSMSUB.tprfl_id = TPRFLSUB.tprfl_id');
$this->db->join('tbl_staff_members TSMA', 'TSUP.sup_assigned_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_staff_members TSMU', 'TSUP.sup_updated_by = TSMU.smember_id');
$this->db->join('tbl_profile TPRFLU', 'TSMU.tprfl_id = TPRFLU.tprfl_id');
$this->db->where('TSUP.sub_smember_id', $subordinate_smember_id);
return $this->db->get();
}
function updateSupervisorAssignmentInfo($supervisorAssignmentUpdateInfo) {
$this->db->where('sup_id', $supervisorAssignmentUpdateInfo['sup_id']);
return $this->db->update('tbl_supervisors', $supervisorAssignmentUpdateInfo);
}
function deleteAssignmentMappingRecord($sup_id) {
$this->db->where("sup_id", $sup_id);
$this->db->delete("tbl_supervisors");
}
/* Funtions for supervisors-subordinates modules */
/* Functions for employee promotion module */
function createNewPromotion($newPromotionInfo) {
$this->db->insert('staff_promotion_mst', $newPromotionInfo);
return $this->db->insert_id();
}
function getAllEmployeesPromotions() {
$this->db->select("TLD.tld_delete_status,TLD.tld_working_status,SPM.spm_id,SPM.smember_id,SPM.desig_id,SPM.spm_doj,SPM.spm_dor,SPM.spm_added_by,SPM.spm_added_on,"
. "SPM.spm_updated_by,SPM.spm_updated_on,TDEM.desig_name designationName,TSDM.sub_dept_name,TDM.dept_name,"
. "CONCAT(TPRFL.tprfl_firstname,' ',TPRFL.tprfl_lastname) empName,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
. "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
$this->db->from('staff_promotion_mst SPM');
$this->db->join('tbl_staff_members TSM', 'SPM.smember_id = TSM.smember_id');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_designation_master TDEM', 'SPM.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_staff_members TSMA', 'SPM.spm_added_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_staff_members TSMU', 'SPM.spm_updated_by = TSMU.smember_id');
$this->db->join('tbl_profile TPRFLU', 'TSMU.tprfl_id = TPRFLU.tprfl_id');
$this->db->order_by("SPM.spm_added_on", "desc");
return $this->db->get();
}
function getEmployeeAllPromotions($smember_id) {
$this->db->select("SPM.spm_id,SPM.smember_id,SPM.desig_id,SPM.spm_doj,SPM.spm_dor,SPM.spm_added_by,SPM.spm_added_on,"
. "SPM.spm_updated_by,SPM.spm_updated_on,TDEM.desig_name designationName,TSDM.sub_dept_name,TDM.dept_name,"
. "CONCAT(TPRFL.tprfl_firstname,' ',TPRFL.tprfl_lastname) empName,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,TLD.tld_delete_status,TLD.tld_working_status,"
. "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
. "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
$this->db->from('staff_promotion_mst SPM');
$this->db->join('tbl_staff_members TSM', 'SPM.smember_id = TSM.smember_id');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_designation_master TDEM', 'SPM.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_staff_members TSMA', 'SPM.spm_added_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_staff_members TSMU', 'SPM.spm_updated_by = TSMU.smember_id');
$this->db->join('tbl_profile TPRFLU', 'TSMU.tprfl_id = TPRFLU.tprfl_id');
$this->db->where('SPM.smember_id', $smember_id);
$this->db->order_by("SPM.spm_added_on", "desc");
return $this->db->get();
}
function getEmployeePromotionInfoBy($spm_id) {
$this->db->select("SPM.spm_id,SPM.smember_id,SPM.desig_id,SPM.spm_doj,SPM.spm_dor,SPM.spm_added_by,SPM.spm_added_on,"
. "SPM.spm_updated_by,SPM.spm_updated_on,TDEM.desig_name designationName,TSDM.sub_dept_id,"
. "TSDM.sub_dept_name,TDM.dept_name,TDM.dept_id,DCM.dcm_id,DCM.dcm_name,DCM.dcm_short_name,"
. "CONCAT(TPRFL.tprfl_firstname,' ',TPRFL.tprfl_lastname) empName,"
. "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
. "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
$this->db->from('staff_promotion_mst SPM');
$this->db->join('tbl_staff_members TSM', 'SPM.smember_id = TSM.smember_id');
$this->db->join('tbl_profile TPRFL', 'TSM.tprfl_id = TPRFL.tprfl_id');
$this->db->join('tbl_logindetails TLD', 'TSM.tld_id = TLD.tld_id');
$this->db->join('tbl_designation_master TDEM', 'SPM.desig_id = TDEM.desig_id');
$this->db->join('designation_category_mst DCM', 'TDEM.dcm_id = DCM.dcm_id');
$this->db->join('tbl_sub_departments_master TSDM', 'TLD.sub_dept_id = TSDM.sub_dept_id');
$this->db->join('tbl_department_master TDM', 'TSDM.dept_id = TDM.dept_id');
$this->db->join('tbl_staff_members TSMA', 'SPM.spm_added_by = TSMA.smember_id');
$this->db->join('tbl_profile TPRFLA', 'TSMA.tprfl_id = TPRFLA.tprfl_id');
$this->db->join('tbl_staff_members TSMU', 'SPM.spm_updated_by = TSMU.smember_id');
$this->db->join('tbl_profile TPRFLU', 'TSMU.tprfl_id = TPRFLU.tprfl_id');
$this->db->where('SPM.spm_id', $spm_id);
return $this->db->get();
}
public function getLastPromotionOfEmployeeBy($smemberId) {
$this->db->select("*");
$this->db->from('staff_promotion_mst SPM');
$this->db->where("SPM.spm_id = (select max(spm_id) from staff_promotion_mst where smember_id = " . $smemberId . ")");
return $this->db->get();
}
public function getEmployeePromotionInfoByDesignation($smemberId, $desig_id) {
$this->db->select("*");
$this->db->from('staff_promotion_mst SPM');
$this->db->where("SPM.spm_id", $smemberId);
$this->db->where("SPM.desig_id", $desig_id);
return $this->db->get();
}
function updateEmployeePromotionInfo($employeePromotionUpdateInfo) {
$this->db->where('spm_id', $employeePromotionUpdateInfo['spm_id']);
return $this->db->update('staff_promotion_mst', $employeePromotionUpdateInfo);
}
/* Functions for Employee Promotion Module */
}