File: /var/www/html/dev-hrms/app/Http/Controllers/AbstractReportController copy.php
<?php
namespace App\Http\Controllers;
use App\Models\EmployeeProfile;
use App\Models\EmployeeAddress;
use App\Models\Course;
use App\Models\Subjects;
use App\Models\FamilyMember;
use App\Models\EmployeeTrainingDetail;
use App\Models\EmployeeDisciplinaryAction;
use App\Models\ForeignVisit;
use App\Models\DivisionGrade;
use App\Models\EmployeeQualifications;
use App\Models\EmployeeNameChange;
use App\Models\AssetsLiability;
use App\Models\ConfidentialReport;
use App\Models\DeputationDetail;
use App\Models\EmployeePromotion;
use App\Models\EmployeePensionScheme;
use App\Models\EmployeeCategoryDetail;
use App\Models\EmployeeGeneralDetail;
use App\Models\EmployeePoliceVerification;
use App\Models\EmployeeMedicalCertificate;
use App\Models\EmployeeEligibilityExam;
use App\Models\EmployeePermanency;
use App\Models\EmployeeNomineeDetail;
use App\Models\EmployeePayMatrix;
use App\Models\PensionScheme;
use App\Models\Group;
use App\Models\Appointment;
use App\Models\SchemeWisePost;
use App\Models\ActualWorkPlace;
use App\Models\Cadre;
use Illuminate\Http\Request;
use App\Library\UserLib;
use Illuminate\Support\Facades\DB;
class AbstractReportController extends Controller
{
protected $userLib;
protected $UserSessionId;
public function __construct(UserLib $userLib)
{
$this->userLib = $userLib;
}
### Office Wise Report view page
public function officeWiseReport(Request $request)
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$OfficeWiseReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_department.DepartmentName as DepartmentName', 'tbl_ddo.DDOName as DDOName', 'tbl_controlling_officer.ControllingOfficerName as ControllingOfficer', 'tbl_designation.DesignationName as DesignationName', 'tbl_actual_work_place.ActualWorkPlaceName as ActualWorkPlace', 'tbl_group.GroupName as Group', 'tbl_cadre.CadreName as Cadre', 'tbl_sanction_post.SanctionPostName as SanctionPost', 'tbl_faculty.FacultyName as Faculty', 'tbl_scheme_name.NameOfScheme as NameOfScheme')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_department', 'tbl_employees_promotion_transfer.DepartmentId', '=', 'tbl_department.DepartmentId')
->leftJoin('tbl_ddo', 'tbl_employees_promotion_transfer.DDOId', '=', 'tbl_ddo.DDOId')
->leftJoin('tbl_controlling_officer', 'tbl_employees_promotion_transfer.ControllingOfficerId', '=', 'tbl_controlling_officer.ControllingOfficerId')
->leftJoin('tbl_designation', 'tbl_employees_promotion_transfer.DesignationId', '=', 'tbl_designation.DesignationId')
//->leftJoin('tbl_appointment', 'tbl_employees_promotion_transfer.AppointmentBy', '=', 'tbl_appointment.AppointmentId')
->leftJoin('tbl_actual_work_place', 'tbl_employees_promotion_transfer.ActualWorkPlaceId', '=', 'tbl_actual_work_place.ActualWorkPlaceId')
->leftJoin('tbl_group', 'tbl_employees_promotion_transfer.GroupId', '=', 'tbl_group.GroupId')
->leftJoin('tbl_cadre', 'tbl_employees_promotion_transfer.CadreId', '=', 'tbl_cadre.CadreId')
->leftJoin('tbl_sanction_post', 'tbl_employees_promotion_transfer.SanctionPostId', '=', 'tbl_sanction_post.SanctionPostId')
->leftJoin('tbl_faculty', 'tbl_employees_promotion_transfer.FacultyId', '=', 'tbl_faculty.FacultyId')
->leftJoin('tbl_scheme_name', 'tbl_employees_promotion_transfer.SchemeNameId', '=', 'tbl_scheme_name.SchemeNameId')
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC')
->get();
// print_r(json_encode($OfficeWiseReport));
// exit();
return view('admin.Reports.office-wise-report');
}
### Get Office Wise Report
public function getOfficeWiseReport(Request $request)
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$OfficeWiseReport = $request->input('OfficeWiseReport');
$DDOId = $request->input('DDO');
$WorkPlaceId = $request->input('WorkPlace');
$GetOfficeWiseReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_department.DepartmentName as DepartmentName', 'tbl_ddo.DDOName as DDOName', 'tbl_controlling_officer.ControllingOfficerName as ControllingOfficer', 'tbl_designation.DesignationName as DesignationName', 'tbl_actual_work_place.ActualWorkPlaceName as ActualWorkPlace', 'tbl_group.GroupName as Group', 'tbl_cadre.CadreName as Cadre', 'tbl_sanction_post.SanctionPostName as SanctionPost', 'tbl_faculty.FacultyName as Faculty', 'tbl_scheme_name.NameOfScheme as NameOfScheme')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_department', 'tbl_employees_promotion_transfer.DepartmentId', '=', 'tbl_department.DepartmentId')
->leftJoin('tbl_ddo', 'tbl_employees_promotion_transfer.DDOId', '=', 'tbl_ddo.DDOId')
->leftJoin('tbl_controlling_officer', 'tbl_employees_promotion_transfer.ControllingOfficerId', '=', 'tbl_controlling_officer.ControllingOfficerId')
->leftJoin('tbl_designation', 'tbl_employees_promotion_transfer.DesignationId', '=', 'tbl_designation.DesignationId')
//->leftJoin('tbl_appointment', 'tbl_employees_promotion_transfer.AppointmentBy', '=', 'tbl_appointment.AppointmentId')
->leftJoin('tbl_actual_work_place', 'tbl_employees_promotion_transfer.ActualWorkPlaceId', '=', 'tbl_actual_work_place.ActualWorkPlaceId')
->leftJoin('tbl_group', 'tbl_employees_promotion_transfer.GroupId', '=', 'tbl_group.GroupId')
->leftJoin('tbl_cadre', 'tbl_employees_promotion_transfer.CadreId', '=', 'tbl_cadre.CadreId')
->leftJoin('tbl_sanction_post', 'tbl_employees_promotion_transfer.SanctionPostId', '=', 'tbl_sanction_post.SanctionPostId')
->leftJoin('tbl_faculty', 'tbl_employees_promotion_transfer.FacultyId', '=', 'tbl_faculty.FacultyId')
->leftJoin('tbl_scheme_name', 'tbl_employees_promotion_transfer.SchemeNameId', '=', 'tbl_scheme_name.SchemeNameId')
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC');
//->get();
if ($OfficeWiseReport == 'DDOWise' && $DDOId) {
$GetOfficeWiseReport = $GetOfficeWiseReport->where("tbl_employees_promotion_transfer.DDOId", $DDOId)->get();
} elseif ($OfficeWiseReport == 'OfficeWise' && $WorkPlaceId) {
$GetOfficeWiseReport = $GetOfficeWiseReport->where("tbl_employees_promotion_transfer.ActualWorkPlaceId", $WorkPlaceId)->get();
} else {
$GetOfficeWiseReport = $GetOfficeWiseReport->get();
}
$OfficeWiseReportOutput = '';
if ($GetOfficeWiseReport->count() > 0) {
$s = 1;
foreach ($GetOfficeWiseReport as $key => $OfficeReport) {
$EmployeeId = $OfficeReport->EmployeeId;
/*** Get Employee Category Detail ***/
$GetCategoryDetail = EmployeeCategoryDetail::select('tbl_employee_category_detail.*', 'tbl_religion.ReligionName as Religion', 'tbl_caste.CasteName as Caste', 'tbl_basic_category.BasicCategoryName as BasicCategory', 'tbl_appointment_category.AppointmentCategoryName as AppointmentCategory', 'tbl_parallel_reservation.ParallelReservationName as ParallelReservation', 'tbl_admin.FirstName as Updated_by', 'tbl_admin.FirstName as Created_by')
->leftJoin('tbl_admin', 'tbl_employee_category_detail.Updated_by', '=', 'tbl_admin.UserId')
->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.BasicCategoryId', '=', 'tbl_basic_category.BasicCategoryId')
->leftJoin('tbl_religion', 'tbl_employee_category_detail.ReligionId', '=', 'tbl_religion.ReligionId')
->leftJoin('tbl_caste', 'tbl_employee_category_detail.CasteId', '=', 'tbl_caste.CasteId')
->leftJoin('tbl_appointment_category', 'tbl_employee_category_detail.AppointmentCategoryId', '=', 'tbl_appointment_category.AppointmentCategoryId')
->leftJoin('tbl_parallel_reservation', 'tbl_employee_category_detail.ParallelReservationId', '=', 'tbl_parallel_reservation.ParallelReservationId')
->where('tbl_employee_category_detail.EmployeeId', $EmployeeId)
->get()
->first();
if ($GetCategoryDetail) {
$Category = $GetCategoryDetail->BasicCategory;
} else {
$Category = 'N/A';
}
$DateOfBirth = date('d-M-Y', strtotime($OfficeReport->DateOfBirth));
$PromotionTransferDate = date('d-M-Y', strtotime($OfficeReport->PromotionTransferDate));
$OfficeWiseReportOutput .= '<tr>' .
'<td>' . $s . '</td>' .
'<td>' . $OfficeReport->DDOName . '</td>' .
'<td>' . $OfficeReport->NameOfScheme . '</a></td>' .
'<td> <a href="view-employee/' . $OfficeReport->EmployeeId . '" target="blank">' . $OfficeReport->EmployeeName . '</td>' .
'<td>' . $OfficeReport->DesignationName . '</td>' .
'<td>' . $OfficeReport->DepartmentName . '</td>' .
'<td>' . $OfficeReport->ActualWorkPlace . '</td>' .
'<td>' . $DateOfBirth . '</td>' .
'<td>' . $PromotionTransferDate . '</td>' .
'<td>' . $Category . '</td>' .
'</tr>';
$s++;
}
}
return response()->json([
'status' => 200,
'OfficeWiseReportOutput' => $OfficeWiseReportOutput
]);
// print_r(json_encode($OfficeWiseReport));
// exit();
//return view('admin.Reports.office-wise-report', ['OfficeWiseReport' => $OfficeWiseReport]);
}
### Employee Establishment Report
public function empEstablishmentReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
return view('admin.Reports.employee-establishment-report');
}
### Nominee Detail Report
public function nomineeDetailReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
// $EmployeeList = EmployeeProfile::select('tbl_employees.*', 'tbl_name_title.NameTitle as Title', 'tbl_department.DepartmentName as DepartmentName', 'tbl_ddo.DDOName as DDOName', 'tbl_controlling_officer.ControllingOfficerName as ControllingOfficer', 'tbl_designation.DesignationName as DesignationName', 'tbl_appointment.AppointmentByName as AppointmentByName', 'tbl_actual_work_place.ActualWorkPlaceName as ActualWorkPlace', 'tbl_group.GroupName as Group', 'tbl_cadre.CadreName as Cadre', 'tbl_sanction_post.SanctionPostName as SanctionPost', 'tbl_faculty.FacultyName as Faculty', 'tbl_scheme_name.NameOfScheme as NameOfScheme')
// ->where('tbl_employees.IsDeleted', '=', '0')
// ->leftJoin('tbl_name_title', 'tbl_employees.NameTitleId', '=', 'tbl_name_title.NameTitleId')
// ->leftJoin('tbl_department', 'tbl_employees.DepartmentId', '=', 'tbl_department.DepartmentId')
// ->leftJoin('tbl_ddo', 'tbl_employees.DDOId', '=', 'tbl_ddo.DDOId')
// ->leftJoin('tbl_controlling_officer', 'tbl_employees.ControllingOfficerId', '=', 'tbl_controlling_officer.ControllingOfficerId')
// ->leftJoin('tbl_designation', 'tbl_employees.DesignationId', '=', 'tbl_designation.DesignationId')
// ->leftJoin('tbl_appointment', 'tbl_employees.AppointmentBy', '=', 'tbl_appointment.AppointmentId')
// ->leftJoin('tbl_actual_work_place', 'tbl_employees.ActualWorkPlaceId', '=', 'tbl_actual_work_place.ActualWorkPlaceId')
// ->leftJoin('tbl_group', 'tbl_employees.Group', '=', 'tbl_group.GroupId')
// ->leftJoin('tbl_cadre', 'tbl_employees.CadreId', '=', 'tbl_cadre.CadreId')
// ->leftJoin('tbl_sanction_post', 'tbl_employees.SanctionPostId', '=', 'tbl_sanction_post.SanctionPostId')
// ->leftJoin('tbl_faculty', 'tbl_employees.FacultyId', '=', 'tbl_faculty.FacultyId')
// ->leftJoin('tbl_scheme_name', 'tbl_employees.SchemeNameId', '=', 'tbl_scheme_name.SchemeNameId')
// ->orderBy('tbl_actual_work_place.ActualWorkPlaceName', 'ASC')
// ->get();
$EmployeeNomineeList = EmployeeNomineeDetail::select('tbl_employees_nominee_details.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employee_relation.EmployeeRelationName as EmployeeRelation', 'tbl_pension_scheme.PensionSchemeName as NomineeType')
->leftJoin('tbl_employees', 'tbl_employees_nominee_details.EmployeeId', 'tbl_employees.EmployeeId')
->leftJoin('tbl_employee_relation', 'tbl_employees_nominee_details.EmployeeRelationId', 'tbl_employee_relation.EmployeeRelationId')
->leftJoin('tbl_pension_scheme', 'tbl_employees_nominee_details.NomineeType', 'tbl_pension_scheme.PensionSchemeId')
//->where('tbl_employees_nominee_details.Status', '1')
->where('tbl_employees_nominee_details.IsDeleted', '0')
->orderBy('tbl_employees_nominee_details.EmployeeCode', 'ASC')
->get();
return view('admin.Reports.nominee-detail-report', ['EmployeeNomineeList' => $EmployeeNomineeList]);
}
### Employee Eligibility Exam List
public function empEligibilityExamList()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$EmpEligibilityExamList = EmployeeEligibilityExam::select('tbl_employee_eligibility_exam.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_admin.FirstName as Updated_by', 'tbl_admin.FirstName as Created_by')
->leftJoin('tbl_employees', 'tbl_employee_eligibility_exam.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_admin', 'tbl_employee_eligibility_exam.Updated_by', '=', 'tbl_admin.UserId')
->where('tbl_employee_eligibility_exam.Status', '1')
->where('tbl_employee_eligibility_exam.IsDeleted', '0')
->get();
return view('admin.Reports.employee-eligibility-exam', ['EmpEligibilityExamList' => $EmpEligibilityExamList]);
}
### Emp Modified Detail
public function empModifiedDetail()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
return view('admin.Reports.employee-modified-detail');
}
### Category Wise Nomination // Abstract 1 (A) //
public function categoryWiseNomination()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
// $CategoryWiseNomination = EmployeePromotion::select('tbl_employees_promotion_transfer.*','tbl_appointment.AppointmentByName','tbl_controlling_officer.ControllingOfficerName','tbl_scheme_name.NameOfScheme','tbl_designation.DesignationName','tbl_employees.EmployeeName','tbl_employees.DateOfBirth','tbl_employees.JoiningDate','tbl_actual_work_place.ActualWorkPlaceName','tbl_department.DepartmentName','tbl_department.Description','tbl_employee_category_detail.AppointmentCategoryId')
// ->leftJoin('tbl_controlling_officer', 'tbl_controlling_officer.ControllingOfficerId', '=', 'tbl_employees_promotion_transfer.ControllingOfficerId')
// ->leftJoin('tbl_appointment', 'tbl_appointment.AppointmentId', '=', 'tbl_employees_promotion_transfer.AppointmentStatus')
// ->leftJoin('tbl_scheme_name', 'tbl_scheme_name.SchemeNameId', '=', 'tbl_employees_promotion_transfer.SchemeNameId')
// ->leftJoin('tbl_employee_category_detail', 'tbl_employee_category_detail.EmployeeId', '=', 'tbl_employees_promotion_transfer.EmployeeId')
// ->leftJoin('tbl_employees', 'tbl_employees.EmployeeId', '=', 'tbl_employees_promotion_transfer.EmployeeId')
// ->leftJoin('tbl_designation', 'tbl_designation.DesignationId', '=', 'tbl_employees_promotion_transfer.DesignationId')
// ->leftJoin('tbl_department', 'tbl_department.DepartmentId', '=', 'tbl_employees_promotion_transfer.DepartmentId')
// ->leftJoin('tbl_actual_work_place', 'tbl_actual_work_place.ActualWorkPlaceId', '=', 'tbl_employees_promotion_transfer.ActualWorkPlaceId')
// ->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'bTM9MUJXKKhGHezyrMP85rUv0tLHV8crYg0Ob')
// ->get();
// if ($CategoryWiseNomination->count()) {
// foreach ($CategoryWiseNomination as $CatValue) {
// $AppointmentCategoryId = $CatValue->AppointmentCategoryId;
// $Categories = AppointmentCategory::select('*')
// ->where('Status','1')
// ->where('AppointmentCategoryId',$AppointmentCategoryId)
// ->get();
// $CategoryDetail = $Categories->first();
// $AppointmentCategoryName = isset($CategoryDetail->AppointmentCategoryName) ? $CategoryDetail->AppointmentCategoryName : '';
// $CatValue->AppointmentCategoryName = $AppointmentCategoryName;
// }
// }
$Cadre = Cadre::select('*')
->where('Status', '1')
->where('IsDeleted', '0')
->get();
$groupData = array();
if ($Cadre->count()) {
foreach ($Cadre as $CValue) {
$CadreId = $CValue->CadreId;
$CadreName = $CValue->CadreName;
$TotalNominationCadreWisePost = '';
$TotalSanctionedPost = '';
$getSanctionedPostCadreWise = SchemeWisePost::select((DB::raw('SUM(tbl_scheme_wise_post.SanctionedPost) as TotalSanctionPost')))
->where('tbl_scheme_wise_post.CadreId', $CadreId)
->where('tbl_scheme_wise_post.Status', '1')
->where('tbl_scheme_wise_post.IsDeleted', '0')
->get();
if ($getSanctionedPostCadreWise->count()) {
foreach ($getSanctionedPostCadreWise as $SanctionedPost) {
$TotalSanctionedPost = $SanctionedPost->TotalSanctionPost;
}
}
$CadreWiseNominationReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalNominationCadreWisePost')), 'tbl_department.DepartmentName')
->leftJoin('tbl_department', 'tbl_department.DepartmentId', '=', 'tbl_employees_promotion_transfer.DepartmentId')
->where('tbl_employees_promotion_transfer.Status', '1')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'bTM9MUJXKKhGHezyrMP85rUv0tLHV8crYg0Ob')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
$EmployeeId = '';
if ($CadreWiseNominationReport->count()) {
foreach ($CadreWiseNominationReport as $NominationReport) {
$TotalNominationCadreWisePost = $NominationReport->TotalNominationCadreWisePost;
$NominationCadreWiseDepartment = $NominationReport->DepartmentName;
$EmployeeId = $NominationReport->EmployeeId;
}
}
$BasicCategory = EmployeeCategoryDetail::select('*')
->where('EmployeeId', $EmployeeId)
->where('Status', '=', '1')
->where('IsDeleted', '=', '0')
->get();
// print_r($BasicCategory);
$BasicCategoryName = "";
$TotalCategoryPost = "";
if ($BasicCategory->count()) {
foreach ($BasicCategory as $BValue) {
$BasicCategoryId = $BValue->BasicCategoryId;
$TotalBasicCategoryPost = EmployeeCategoryDetail::select((DB::raw('COUNT(tbl_employee_category_detail.BasicCategoryId) as TotalCategory')), 'tbl_basic_category.BasicCategoryName')
->leftJoin('tbl_basic_category', 'tbl_basic_category.BasicCategoryId', '=', 'tbl_employee_category_detail.BasicCategoryId')
->where('tbl_employee_category_detail.Status', '=', '1')
->where('tbl_employee_category_detail.IsDeleted', '=', '0')
->where('tbl_employee_category_detail.EmployeeId', $EmployeeId)
->where('tbl_employee_category_detail.BasicCategoryId', $BasicCategoryId)
->get();
$CategoryDetail = $TotalBasicCategoryPost->first();
$BasicCategoryName = isset($CategoryDetail->BasicCategoryName) ? $CategoryDetail->BasicCategoryName : '';
$BValue->BasicCategoryName = $BasicCategoryName;
$CategoryDetail = $TotalBasicCategoryPost->first();
$TotalCategoryPost = isset($CategoryDetail->TotalCategory) ? $CategoryDetail->TotalCategory : '';
$BValue->TotalCategory = $TotalCategoryPost;
}
}
$groupData[] = array('CadreName' => $CadreName,'TotalSanctionedPost' => $TotalSanctionedPost, 'TotalNominationCadreWisePost' => $TotalNominationCadreWisePost, 'NominationCadreWiseDepartment' => $NominationCadreWiseDepartment, 'BasicCategoryName' => $BasicCategoryName, 'TotalCategoryPost' => $TotalCategoryPost);
}
}
// echo json_encode($groupData);die;
return view('admin.Reports.category-wise-nomination', ['groupData' => $groupData]);
}
### Category Wise Promotion // Abstract 1 (B) //
public function categoryWisePromotion()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
// $CategoryWisePromotion = EmployeePromotion::select('tbl_employees_promotion_transfer.*','tbl_appointment.AppointmentByName','tbl_controlling_officer.ControllingOfficerName','tbl_scheme_name.NameOfScheme','tbl_designation.DesignationName','tbl_employees.EmployeeName','tbl_employees.DateOfBirth','tbl_employees.JoiningDate','tbl_actual_work_place.ActualWorkPlaceName','tbl_department.DepartmentName','tbl_department.Description','tbl_employee_category_detail.AppointmentCategoryId')
// ->leftJoin('tbl_controlling_officer', 'tbl_controlling_officer.ControllingOfficerId', '=', 'tbl_employees_promotion_transfer.ControllingOfficerId')
// ->leftJoin('tbl_appointment', 'tbl_appointment.AppointmentId', '=', 'tbl_employees_promotion_transfer.AppointmentStatus')
// ->leftJoin('tbl_scheme_name', 'tbl_scheme_name.SchemeNameId', '=', 'tbl_employees_promotion_transfer.SchemeNameId')
// ->leftJoin('tbl_employee_category_detail', 'tbl_employee_category_detail.EmployeeId', '=', 'tbl_employees_promotion_transfer.EmployeeId')
// ->leftJoin('tbl_employees', 'tbl_employees.EmployeeId', '=', 'tbl_employees_promotion_transfer.EmployeeId')
// ->leftJoin('tbl_designation', 'tbl_designation.DesignationId', '=', 'tbl_employees_promotion_transfer.DesignationId')
// ->leftJoin('tbl_department', 'tbl_department.DepartmentId', '=', 'tbl_employees_promotion_transfer.DepartmentId')
// ->leftJoin('tbl_actual_work_place', 'tbl_actual_work_place.ActualWorkPlaceId', '=', 'tbl_employees_promotion_transfer.ActualWorkPlaceId')
// ->leftJoin('tbl_appointment_category', 'tbl_appointment_category.AppointmentCategoryId', '=', 'tbl_employees_promotion_transfer.AppointmentCategoryId')
// ->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
// ->get();
// if ($CategoryWisePromotion->count()) {
// foreach ($CategoryWisePromotion as $CatValue) {
// $AppointmentCategoryId = $CatValue->AppointmentCategoryId;
// $Categories = AppointmentCategory::select('*')
// ->where('Status','1')
// ->where('AppointmentCategoryId',$AppointmentCategoryId)
// ->get();
// $CategoryDetail = $Categories->first();
// $AppointmentCategoryName = isset($CategoryDetail->AppointmentCategoryName) ? $CategoryDetail->AppointmentCategoryName : '';
// $CatValue->AppointmentCategoryName = $AppointmentCategoryName;
// }
// }
$Cadre = Cadre::select('*')
->where('Status', '1')
->where('IsDeleted', '0')
->get();
$groupData = array();
if ($Cadre->count()) {
foreach ($Cadre as $CValue) {
$CadreId = $CValue->CadreId;
$CadreName = $CValue->CadreName;
$TotalPromotionCadreWisePost = '';
$TotalSanctionedPost = '';
$getSanctionedPostCadreWise = SchemeWisePost::select((DB::raw('SUM(tbl_scheme_wise_post.SanctionedPost) as TotalSanctionPost')))
->where('tbl_scheme_wise_post.CadreId', $CadreId)
->where('tbl_scheme_wise_post.Status', '1')
->where('tbl_scheme_wise_post.IsDeleted', '0')
->get();
if ($getSanctionedPostCadreWise->count()) {
foreach ($getSanctionedPostCadreWise as $SanctionedPost) {
$TotalSanctionedPost = $SanctionedPost->TotalSanctionPost;
}
}
$CadreWisePromotionReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalPromotionCadreWisePost')), 'tbl_department.DepartmentName')
->leftJoin('tbl_department', 'tbl_department.DepartmentId', '=', 'tbl_employees_promotion_transfer.DepartmentId')
->where('tbl_employees_promotion_transfer.Status', '1')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'bTM9MUJXKKhGHezyrMP85rUv0tLHV8crYg0Ob')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
if ($CadreWisePromotionReport->count()) {
foreach ($CadreWisePromotionReport as $PromotionReport) {
$TotalPromotionCadreWisePost = $PromotionReport->TotalPromotionCadreWisePost;
$PromotionCadreWiseDepartment = $PromotionReport->DepartmentName;
$EmployeeId = $PromotionReport->EmployeeId;
}
}
$BasicCategory = EmployeeCategoryDetail::select('*')
->where('EmployeeId', $EmployeeId)
->where('Status', '=', '1')
->where('IsDeleted', '=', '0')
->get();
$BasicCategoryName = "";
$TotalCategoryPost = "";
if ($BasicCategory->count()) {
foreach ($BasicCategory as $BValue) {
$BasicCategoryId = $BValue->BasicCategoryId;
$TotalBasicCategoryPost = EmployeeCategoryDetail::select((DB::raw('COUNT(tbl_employee_category_detail.BasicCategoryId) as TotalCategory')), 'tbl_basic_category.BasicCategoryName')
->leftJoin('tbl_basic_category', 'tbl_basic_category.BasicCategoryId', '=', 'tbl_employee_category_detail.BasicCategoryId')
->where('tbl_employee_category_detail.Status', '=', '1')
->where('tbl_employee_category_detail.EmployeeId', $EmployeeId)
->where('tbl_employee_category_detail.BasicCategoryId', $BasicCategoryId)
->get();
$CategoryDetail = $TotalBasicCategoryPost->first();
$BasicCategoryName = isset($CategoryDetail->BasicCategoryName) ? $CategoryDetail->BasicCategoryName : '';
$BValue->BasicCategoryName = $BasicCategoryName;
$CategoryDetail = $TotalBasicCategoryPost->first();
$TotalCategoryPost = isset($CategoryDetail->TotalCategory) ? $CategoryDetail->TotalCategory : '';
$BValue->TotalCategory = $TotalCategoryPost;
}
}
$groupData[] = array('CadreName' => $CadreName,'TotalSanctionedPost' => $TotalSanctionedPost, 'TotalPromotionCadreWisePost' => $TotalPromotionCadreWisePost, 'PromotionCadreWiseDepartment' => $PromotionCadreWiseDepartment, 'BasicCategoryName' => $BasicCategoryName, 'TotalCategoryPost' => $TotalCategoryPost);
}
}
return view('admin.Reports.category-wise-promotion', ['groupData' => $groupData]);
}
### Cadre Wise Report // Abstract 2 //
public function cadreWiseReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$Cadre = Cadre::select('*')
->where('Status', '1')
->where('IsDeleted', '0')
->get();
$groupData = array();
if ($Cadre->count()) {
foreach ($Cadre as $CValue) {
$CadreId = $CValue->CadreId;
$CadreName = $CValue->CadreName;
$TotalSanctionedPost = '';
$TotalNominationCadreWisePost = '';
$TotalPromotionCadreWisePost = '';
$getSanctionedPostCadreWise = SchemeWisePost::select((DB::raw('SUM(tbl_scheme_wise_post.SanctionedPost) as TotalSanctionPost')))
->where('tbl_scheme_wise_post.CadreId', $CadreId)
->where('tbl_scheme_wise_post.Status', '1')
->where('tbl_scheme_wise_post.IsDeleted', '0')
->get();
if ($getSanctionedPostCadreWise->count()) {
foreach ($getSanctionedPostCadreWise as $SanctionedPost) {
$TotalSanctionedPost = $SanctionedPost->TotalSanctionPost;
}
}
$CadreWiseNominationReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalNominationCadreWisePost')))
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'bTM9MUJXKKhGHezyrMP85rUv0tLHV8crYg0Ob')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
if ($CadreWiseNominationReport->count()) {
foreach ($CadreWiseNominationReport as $NominationReport) {
$TotalNominationCadreWisePost = $NominationReport->TotalNominationCadreWisePost;
}
}
$CadreWisePromotionReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalPromotionCadreWisePost')))
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
if ($CadreWisePromotionReport->count()) {
foreach ($CadreWisePromotionReport as $PromotionReport) {
$TotalPromotionCadreWisePost = $PromotionReport->TotalPromotionCadreWisePost;
}
}
$groupData[] = array('CadreName' => $CadreName, 'TotalSanctionedPost' => $TotalSanctionedPost, 'TotalNominationCadreWisePost' => $TotalNominationCadreWisePost, 'TotalPromotionCadreWisePost' => $TotalPromotionCadreWisePost);
}
}
// echo json_encode($groupData);exit;
return view('admin.Reports.cadre-wise-report', ['groupData' => $groupData]);
}
public function getCadreWiseReport($CadreId = '')
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$Cadre = Cadre::select('*')
->where('CadreId', $CadreId)
->where('Status', '1')
->where('IsDeleted', '0')
->get()->first();
$CadreName = isset($Cadre->CadreName) ? $Cadre->CadreName : '';
$Cadre = Cadre::select('*')
->where('CadreId', $CadreId)
->where('Status', '1')
->where('IsDeleted', '0')
->get()->first();
$CadreName = isset($Cadre->CadreName) ? $Cadre->CadreName : '';
$TotalSanctionPost = 0;
$NameOfScheme = '';
$NumberOfPost = 0;
$DesignationName = '';
$getSanctionedPostCadreWise = SchemeWisePost::select('tbl_scheme_wise_post.*', 'tbl_scheme_name.NameOfScheme as NameOfScheme', 'tbl_designation.DesignationId as DesignationId', 'tbl_designation.DesignationName as DesignationName')
->leftJoin('tbl_designation', 'tbl_designation.DesignationId', '=', 'tbl_scheme_wise_post.DesignationId')
->leftJoin('tbl_scheme_name', 'tbl_scheme_name.SchemeNameId', '=', 'tbl_scheme_wise_post.SchemeNameId')
->where('tbl_scheme_wise_post.CadreId', $CadreId)
->where('tbl_scheme_wise_post.Status', '1')
->where('tbl_scheme_wise_post.IsDeleted', '0')
->get();
$SchemeNameId = '';
$DesignationId = '';
$output = "";
$total = "";
$TotalNominationfilledPostEmp = 0;
$TotalPromotionfilledPostEmp = 0;
$TotalVacantPost = 0;
if ($getSanctionedPostCadreWise->count()) {
foreach ($getSanctionedPostCadreWise as $SanctionedPost) {
$NameOfScheme = $SanctionedPost->NameOfScheme;
$NumberOfPost = $SanctionedPost->SanctionedPost;
$DesignationName = $SanctionedPost->DesignationName;
$SchemeNameId = $SanctionedPost->SchemeNameId;
$DesignationId = $SanctionedPost->DesignationId;
$TotalSanctionPost = $TotalSanctionPost + $SanctionedPost->SanctionedPost;
$CadreWiseNominationReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.SchemeNameId', $SchemeNameId)
->where('tbl_employees_promotion_transfer.DesignationId', $DesignationId)
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
$NominationfilledPost = 0;
$NominationfilledPostEmp = $CadreWiseNominationReport->count();
if ($NominationfilledPostEmp > 0) {
$NominationfilledPost = $NominationfilledPostEmp;
$TotalNominationfilledPostEmp = $TotalNominationfilledPostEmp + $NominationfilledPostEmp;
}
$CadreWisePromotionReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.SchemeNameId', $SchemeNameId)
->where('tbl_employees_promotion_transfer.DesignationId', $DesignationId)
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
$PromotionfilledPost = 0;
$VacantPost = 0;
$PromotionfilledPostEmp = $CadreWisePromotionReport->count();
if ($PromotionfilledPostEmp > 0) {
$PromotionfilledPost = $PromotionfilledPostEmp;
$TotalVacantPost = $TotalVacantPost + $PromotionfilledPostEmp;
$TotalPromotionfilledPostEmp = $TotalPromotionfilledPostEmp + $PromotionfilledPostEmp;
}
if ($NumberOfPost > ($NominationfilledPost + $PromotionfilledPostEmp)) {
$VacantPost = $NumberOfPost - ($NominationfilledPost + $PromotionfilledPostEmp);
}
$output .= '<tr>' .
'<td>' . $NameOfScheme . '</td>' .
'<td>' . $DesignationName . '</td>' .
'<td>' . $NumberOfPost . '</td>' .
'<td>' . $NominationfilledPost . '</td>' .
'<td>' . $PromotionfilledPost . '</td>' .
'<td>' . $VacantPost . '</td>' .
'</tr>';
}
if ($TotalSanctionPost > ($TotalNominationfilledPostEmp + $TotalPromotionfilledPostEmp)) {
$getTotalSanctionPost = $TotalSanctionPost - ($TotalNominationfilledPostEmp + $TotalPromotionfilledPostEmp);
} else {
$getTotalSanctionPost = 0;
}
$total .= '<tr>' .
'<th rowspan="4">Total</th>' .
'<th></th>' .
'<th>' . $TotalSanctionPost . '</th>' .
'<th>' . $TotalNominationfilledPostEmp . '</th>' .
'<th>' . $TotalPromotionfilledPostEmp . '</th>' .
'<th>' . $getTotalSanctionPost . '</th>' .
'</tr>';
return response()->json([
'status' => 200,
'CadreName' => $CadreName,
'output' => $output,
'total' => $total,
]);
}
$CadreName = isset($Cadre->CadreName) ? $Cadre->CadreName : '';
$CadreWiseNominationReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalNominationCadreWisePost')))
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'bTM9MUJXKKhGHezyrMP85rUv0tLHV8crYg0Ob')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
if ($CadreWiseNominationReport->count()) {
foreach ($CadreWiseNominationReport as $NominationReport) {
$TotalNominationCadreWisePost = $NominationReport->TotalNominationCadreWisePost;
}
}
$CadreWisePromotionReport = EmployeePromotion::select('tbl_employees_promotion_transfer.*', (DB::raw('COUNT(tbl_employees_promotion_transfer.CadreId) as TotalPromotionCadreWisePost')))
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.CadreId', '=', $CadreId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->orderBy('tbl_employees_promotion_transfer.id', 'desc')
->get();
$groupData = array();
if ($CadreWisePromotionReport->count()) {
foreach ($CadreWisePromotionReport as $PromotionReport) {
$TotalPromotionCadreWisePost = $PromotionReport->TotalPromotionCadreWisePost;
}
}
$groupData[] = array('CadreName' => $CadreName, 'TotalNominationCadreWisePost' => $TotalNominationCadreWisePost, 'TotalPromotionCadreWisePost' => $TotalPromotionCadreWisePost);
$output = "";
$total = "";
$TotalNominationCadreFilled = 0;
$TotalPromotionCadreFilled = 0;
if ($groupData) {
foreach ($groupData as $report) {
$TotalNominationCadreWisePost = $report["TotalNominationCadreWisePost"];
$TotalPromotionCadreWisePost = $report["TotalPromotionCadreWisePost"];
$NominationFilled = isset($report["TotalNominationCadreWisePost"]) ? $report["TotalNominationCadreWisePost"] : '';
$TotalNominationCadreFilled = $TotalNominationCadreFilled + $NominationFilled;
$PromotionFilled = isset($report["TotalPromotionCadreWisePost"]) ? $report["TotalPromotionCadreWisePost"] : '';
$TotalPromotionCadreFilled = $TotalPromotionCadreFilled + $PromotionFilled;
$output .= '<tr>' .
'<td>' . $CadreName . '</td>' .
'<td></td>' .
'<td>' . $TotalNominationCadreWisePost . '</td>' .
'<td></td>' .
'<td></td>' .
'<td>' . $TotalPromotionCadreWisePost . '</td>' .
'<td></td>' .
'</tr>';
}
$total .= '<tr>' .
'<td rowspan="6">Total</td>' .
'<td></td>' .
'<td>' . $TotalNominationCadreFilled . '</td>' .
'<td></td>' .
'<td></td>' .
'<td>' . $TotalPromotionCadreFilled . '</td>' .
'<td></td>' .
'</tr>';
}
return response()->json([
'status' => 200,
'output' => $output,
'total' => $total,
]);
}
### Group Wise Report // Abstract 3 //
public function groupWiseReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$Groups = Group::select('*')
->where('Status', '=', '1')
->get();
$groupData = array();
if ($Groups->count()) {
foreach ($Groups as $GValue) {
$GroupId = $GValue->GroupId;
$GroupName = $GValue->GroupName;
$TotalGroupSanctionPost = '';
$TotalGroupFilledPost = '';
$TotalSanctionPostGroupWise = SchemeWisePost::select((DB::raw('SUM(tbl_scheme_wise_post.SanctionedPost) as TotalGroupSanctionPost')))
->where('tbl_scheme_wise_post.Status', '=', '1')
->where('tbl_scheme_wise_post.GroupId', $GroupId)
->get();
if ($TotalSanctionPostGroupWise->count()) {
foreach ($TotalSanctionPostGroupWise as $PValue) {
$TotalGroupSanctionPost = $PValue->TotalGroupSanctionPost;
}
}
$TotalFilledPostGroupWise = EmployeePromotion::select((DB::raw('COUNT(tbl_employees_promotion_transfer.GroupId) as TotalGroupFilledPost')))
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.GroupId', $GroupId)
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->get();
if ($TotalFilledPostGroupWise->count()) {
foreach ($TotalFilledPostGroupWise as $PValue) {
$TotalGroupFilledPost = $PValue->TotalGroupFilledPost;
}
}
$groupData[] = array('GroupName' => $GroupName, 'TotalGroupSanctionPost' => $TotalGroupSanctionPost, 'TotalGroupFilledPost' => $TotalGroupFilledPost);
}
}
return view('admin.Reports.group-wise-report', ['groupData' => $groupData]);
}
### Group Wise Nomination Promotion // Abstract 4 //
public function groupWiseNominationPromotion()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$Group = Group::select('*')
->where('Status', '=', '1')
->get();
$groupData = array();
if ($Group->count()) {
foreach ($Group as $key => $GValue) {
//Distributer
$GroupId = $GValue->GroupId;
$GroupName = $GValue->GroupName;
$TotalSanctionPostGroup = '';
$AppointmentNomination = '';
$TotalGroupWiseNomination = '';
$AppointmentPromotion = '';
$TotalGroupWisePromotion = '';
$GroupWiseNomination = EmployeePromotion::select((DB::raw('COUNT(tbl_employees_promotion_transfer.AppointmentStatus) as TotalSanctionPost')), 'tbl_appointment.AppointmentByName')
->leftJoin('tbl_appointment', 'tbl_appointment.AppointmentId', '=', 'tbl_employees_promotion_transfer.AppointmentStatus')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.GroupId', '=', $GroupId)
->get();
if ($GroupWiseNomination->count()) {
foreach ($GroupWiseNomination as $key => $NValue) {
$TotalGroupWiseNomination = $NValue->TotalSanctionPost;
$AppointmentNomination = $NValue->AppointmentByName;
}
}
$TotalSanctionPostGroup = SchemeWisePost::select((DB::raw('SUM(tbl_scheme_wise_post.SanctionedPost) as TotalSanctionPostGroup')))
->where('tbl_scheme_wise_post.Status', '=', '1')
->where('tbl_scheme_wise_post.IsDeleted', '=', '0')
->where('tbl_scheme_wise_post.GroupId', $GroupId)
->get();
if ($TotalSanctionPostGroup->count()) {
foreach ($TotalSanctionPostGroup as $key => $SValue) {
$TotalSanctionPostGroup = $SValue->TotalSanctionPostGroup;
}
}
$GroupWisePromotion = EmployeePromotion::select((DB::raw('COUNT(tbl_employees_promotion_transfer.AppointmentStatus) as TotalSanctionPost')), 'tbl_appointment.AppointmentByName')
->leftJoin('tbl_appointment', 'tbl_appointment.AppointmentId', '=', 'tbl_employees_promotion_transfer.AppointmentStatus')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->where('tbl_employees_promotion_transfer.AppointmentStatus', '=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('tbl_employees_promotion_transfer.GroupId', '=', $GroupId)
->get();
if ($GroupWisePromotion->count()) {
foreach ($GroupWisePromotion as $key => $PValue) {
$AppointmentPromotion = $PValue->AppointmentByName;
$TotalGroupWisePromotion = $PValue->TotalSanctionPost;
}
}
$groupData[] = array('GroupName' => $GroupName, 'TotalSanctionPostGroup' => $TotalSanctionPostGroup, 'AppointmentNomination' => $AppointmentNomination, 'TotalGroupWiseNomination' => $TotalGroupWiseNomination, 'AppointmentPromotion' => $AppointmentPromotion, 'TotalGroupWisePromotion' => $TotalGroupWisePromotion);
}
}
// echo json_encode($groupData);exit;
return view('admin.Reports.group-wise-nomination-promotion', ['groupData' => $groupData]);
}
### Scheme Wise Report // Abstract 5 //
public function schemeWiseReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
return view('admin.Reports.scheme-wise-report');
}
public function getSchemeWiseReport($SchemeId = '')
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
if ($SchemeId) {
$TotalSanctionPostSchemeWise = SchemeWisePost::select('tbl_designation.DesignationId', 'tbl_designation.DesignationName', 'tbl_scheme_wise_post.SanctionedPost as NumberOfPost')
->leftJoin('tbl_designation', 'tbl_designation.DesignationId', '=', 'tbl_scheme_wise_post.DesignationId')
->where('tbl_scheme_wise_post.SchemeNameId', $SchemeId)
->get();
$output = "";
$total = "";
$NameOfScheme = '';
$totalFilledPost = 0;
$totalPost = 0;
if ($TotalSanctionPostSchemeWise->count()) {
foreach ($TotalSanctionPostSchemeWise as $SanctionPost) {
$NumberOfPost = $SanctionPost->NumberOfPost;
$DesignationId = $SanctionPost->DesignationId;
$EmployeeDetails = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeName', 'tbl_scheme_name.NameOfScheme')
->leftJoin('tbl_employees', 'tbl_employees.EmployeeId', '=', 'tbl_employees_promotion_transfer.EmployeeId')
->leftJoin('tbl_scheme_name', 'tbl_scheme_name.SchemeNameId', '=', 'tbl_employees_promotion_transfer.SchemeNameId')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', '=', 1)
->where('tbl_employees_promotion_transfer.DesignationId', $DesignationId)
->where('tbl_employees_promotion_transfer.SchemeNameId', $SchemeId)
->groupBy('tbl_employees_promotion_transfer.EmployeeId')
->get();
$filledPost = 0;
$allEmployeeName = '';
$allActualWorkPlaceName = '';
$filledPostEmp = $EmployeeDetails->count();
if ($filledPostEmp > 0) {
$FilledPost = $filledPostEmp;
foreach ($EmployeeDetails as $EmployeeValue) {
$NameOfScheme = $EmployeeValue->NameOfScheme;
$EmployeeName = $EmployeeValue->EmployeeName;
$ActualWorkPlaceId = $EmployeeValue->ActualWorkPlaceId;
$ActualWorkPlaceDetails = ActualWorkPlace::select('*')
->where('IsDeleted', '=', '0')
->where('Status', '=', '1')
->where('ActualWorkPlaceId', $ActualWorkPlaceId)
->get();
$ActualWorkPlaceName = '';
if ($ActualWorkPlaceDetails->count() > 0) {
$ActualWorkPlaceData = $ActualWorkPlaceDetails->first();
$ActualWorkPlaceName = $ActualWorkPlaceData->ActualWorkPlaceName;
}
$allEmployeeName .= '<span class="cmrow">' . $EmployeeName . '</span>';
$allActualWorkPlaceName .= '<span class="cmrow">' . $ActualWorkPlaceName . '</span>';
}
}
$VPost = $SanctionPost->NumberOfPost - $filledPostEmp;
$totalPost = $totalPost + $SanctionPost->NumberOfPost;
$output .= '<tr>' .
'<td>' . $SanctionPost->DesignationName . '</td>' .
'<td>' . $SanctionPost->NumberOfPost . '</td>' .
'<td>' . $filledPostEmp . '</td>' .
'<td>' . $VPost . '</td>' .
'<td>' . $allEmployeeName . '</td>' .
'<td>' . $allActualWorkPlaceName . '</td>' .
'</tr>';
$totalFilledPost = $totalFilledPost + $filledPostEmp;
}
$VacantPost = $totalPost - $totalFilledPost;
$total .= '<tr>' .
'<th> Total </th>' .
'<th>' . $totalPost . '</th>' .
'<th>' . $totalFilledPost . '</th>' .
'<th>' . $VacantPost . '</th>' .
'<td> </td>' .
'<td> </td>' .
'</tr>';
}
return response()->json([
'status' => 200,
'NameOfScheme' => $NameOfScheme,
'TotalSanctionPost' => $totalPost,
'output' => $output,
'total' => $total,
]);
} else {
$NameOfScheme = 'Name Of Scheme not found';
$TotalSanctionPost = 0;
$FilledPost = 0;
$VacantPost = 0;
return response()->json([
'status' => 400,
'NameOfScheme' => $NameOfScheme,
'TotalSanctionPost' => $TotalSanctionPost,
'output' => $output,
'total' => $total,
]);
}
}
### Parallel Reservation Report // Abstract 6 //
public function parallelReservationReport()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$Groups = Group::select('*')
->where('Status', '=', '1')
->get();
$Appointments = Appointment::select('*')
->where('Status', '=', '1')
->get();
$groupData = array();
if ($Groups->count()) {
foreach ($Groups as $GValue) {
$GroupId = $GValue->GroupId;
$GroupName = $GValue->GroupName;
if ($Appointments->count()) {
foreach ($Appointments as $AValue) {
$AppointmentId = $AValue->AppointmentId;
$AppointmentByName = $AValue->AppointmentByName;
$ParallelNominationGroupWiseReport = EmployeePromotion::select('*')
->where('Status', '=', '1')
->where('AppointmentStatus', $AppointmentId)
// ->where('tbl_employees_promotion_transfer.AppointmentStatus', '!=', 'W8whXFgWakAvkHU6OTgvgXBVPZgmQNveI48YY')
->where('GroupId', $GroupId)
->get();
$employeeData = array();
if ($ParallelNominationGroupWiseReport->count()) {
foreach ($ParallelNominationGroupWiseReport as $EValue) {
$EmployeeId = $EValue->EmployeeId;
$GetParallelReservation = EmployeeCategoryDetail::select('*')
->where('Status', '=', '1')
->where('EmployeeId', $EmployeeId)
->get();
$parallelData = array();
if ($GetParallelReservation->count()) {
foreach ($GetParallelReservation as $PValue) {
$ParallelReservationId = $PValue->ParallelReservationId;
// echo $ParallelReservationId."<br>";
$ParallelReservation = EmployeeCategoryDetail::select((DB::raw('COUNT(tbl_employee_category_detail.ParallelReservationId) as TotalParallelPost')), 'tbl_parallel_reservation.ParallelReservationName')
->leftJoin('tbl_parallel_reservation', 'tbl_parallel_reservation.ParallelReservationId', '=', 'tbl_employee_category_detail.ParallelReservationId')
->where('tbl_employee_category_detail.Status', '=', '1')
->where('tbl_employee_category_detail.ParallelReservationId', $ParallelReservationId)
->get();
$ParallelDetail = $ParallelReservation->first();
$TotalParallelPost = isset($ParallelDetail->TotalParallelPost) ? $ParallelDetail->TotalParallelPost : '';
$PValue->TotalParallelPost = $TotalParallelPost;
$ParallelDetail = $ParallelReservation->first();
$ParallelReservationName = isset($ParallelDetail->ParallelReservationName) ? $ParallelDetail->ParallelReservationName : '';
$PValue->ParallelReservationName = $ParallelReservationName;
$parallelData[] = array('ParallelReservationId' => $ParallelReservationId, 'TotalParallelPost' => $TotalParallelPost, 'ParallelReservationName' => $ParallelReservationName);
// echo $ParallelReservationName ."<br>";
}
}
$employeeData[] = array('EmployeeId' => $EmployeeId, 'ParallelReservation' => $parallelData);
}
}
$groupData[] = array('AppointmentId' => $AppointmentId, 'AppointmentByName' => $AppointmentByName, 'GroupId' => $GroupId, 'GroupName' => $GroupName, 'EmployeeDetails' => $employeeData);
}
}
}
// die;
}
return view('admin.Reports.parallel-reservation-report', ['groupData' => $groupData]);
}
### Seniority List // Abstract 7 //
public function reportSeniorityList()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$EmpSeniorityList = EmployeePromotion::select('tbl_employees_promotion_transfer.*', DB::raw('MAX(tbl_employees_promotion_transfer.id)'), 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_employees.RetirementDate as RetirementDate', 'tbl_transfer_status.TransferStatusName as AppointmentStatus', 'tbl_employee_category_detail.BasicCategoryId as BasicCategoryId', 'tbl_basic_category.BasicCategoryName as BasicCategoryName', 'tbl_employee_category_detail.AppointmentCategoryId as AppointmentCategoryId', 'tbl_appointment_category.AppointmentCategoryName as AppointmentCategoryName', 'tbl_designation.DesignationName as DesignationName')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->whereIn('tbl_transfer_status.TransferStatusName', array('Promotion', 'Nomination'))
->leftJoin('tbl_designation', 'tbl_employees_promotion_transfer.DesignationId', '=', 'tbl_designation.DesignationId')
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_employee_category_detail', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employee_category_detail.EmployeeId')
->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.BasicCategoryId', '=', 'tbl_basic_category.BasicCategoryId')
->leftJoin('tbl_appointment_category', 'tbl_employee_category_detail.AppointmentCategoryId', '=', 'tbl_appointment_category.AppointmentCategoryId')
->leftJoin('tbl_transfer_status', 'tbl_employees_promotion_transfer.AppointmentStatus', '=', 'tbl_transfer_status.TransferStatusId')
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC')
->groupBy('tbl_employees_promotion_transfer.EmployeeId')
->get();
// Get Joining Date of Previous Post / Designation
// $EmployeeId = array();
// foreach ($EmpSeniorityList as $key => $SeniorityList) {
// $EmployeeId[$key]['EmployeeId'] = $SeniorityList->EmployeeId;
// }
// print_r(json_encode($EmployeeId));
// exit;
// Get Joining Date of Previous Post / Designation
if ($EmpSeniorityList->count() > 0) {
foreach ($EmpSeniorityList as $key => $SeniorityList) {
$EmployeeId = $SeniorityList->EmployeeId;
// echo $EmployeeId. "<br/>";
// exit;
// Get total Count Promotion/Nomination/Transfer of the Employee
$EmpTotalRecords = EmployeePromotion::where('EmployeeId', $EmployeeId)
->where('IsDeleted', '=', '0')
->where('Status', '=', '1')
->count();
$GetPreviousJoiningDate = EmployeePromotion::select('tbl_employees_promotion_transfer.PromotionTransferDate', 'tbl_employees_promotion_transfer.AppointmentStatus as AppointmentStatus', 'tbl_transfer_status.TransferStatusName as TransferStatusName')
->leftJoin('tbl_transfer_status', 'tbl_employees_promotion_transfer.AppointmentStatus', '=', 'tbl_transfer_status.TransferStatusId')
->where('tbl_employees_promotion_transfer.EmployeeId', '=', $EmployeeId)
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
//->whereIn('tbl_transfer_status.TransferStatusName', array('Promotion', 'Nomination'))
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC')
//->groupBy('EmployeeId')
->skip(1)
//->offset(1)
->take(1)
//->get()
->first();
$dataPro = json_decode(json_encode($GetPreviousJoiningDate), true);
$PromotionTransferDate = isset($dataPro['PromotionTransferDate']) ? $dataPro['PromotionTransferDate'] : '';
$SeniorityList->PreviousJoiningDate = $PromotionTransferDate;
//echo(json_encode($GetPreviousJoiningDate));
//exit;
}
}
// echo json_encode($EmpSeniorityList,JSON_PRETTY_PRINT);
return view('admin.Reports.employee-seniority-list', ['EmpSeniorityList' => $EmpSeniorityList]);
}
### Get Seniority List // Abstract 7 //
public function getSeniorityList(Request $request)
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$DesignationId = $request->input('DesignationId');
if ($DesignationId) {
$DesignationId = $request->input('DesignationId');
} else {
$DesignationId = '';
}
$EmpSeniorityList = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_employees.RetirementDate as RetirementDate', 'tbl_transfer_status.TransferStatusName as AppointmentStatus', 'tbl_employee_category_detail.BasicCategoryId as BasicCategoryId', 'tbl_basic_category.BasicCategoryName as BasicCategoryName', 'tbl_employee_category_detail.AppointmentCategoryId as AppointmentCategoryId', 'tbl_appointment_category.AppointmentCategoryName as AppointmentCategoryName', 'tbl_designation.DesignationName as DesignationName')
->where('tbl_employees_promotion_transfer.DesignationId', '=', $DesignationId)
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
//->whereIn('tbl_transfer_status.TransferStatusName', array('Promotion', 'Nomination'))
->leftJoin('tbl_designation', 'tbl_employees_promotion_transfer.DesignationId', '=', 'tbl_designation.DesignationId')
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_employee_category_detail', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employee_category_detail.EmployeeId')
->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.BasicCategoryId', '=', 'tbl_basic_category.BasicCategoryId')
->leftJoin('tbl_appointment_category', 'tbl_employee_category_detail.AppointmentCategoryId', '=', 'tbl_appointment_category.AppointmentCategoryId')
->leftJoin('tbl_transfer_status', 'tbl_employees_promotion_transfer.AppointmentStatus', '=', 'tbl_transfer_status.TransferStatusId')
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'ASC')
//->groupBy('tbl_employees_promotion_transfer.EmployeeId')
->get();
// print_r(json_encode($EmpSeniorityList));
// exit;
$htmlTableBody = '';
// Get Joining Date of Previous Post / Designation
if ($EmpSeniorityList->count() > 0) {
$sn = 1;
foreach ($EmpSeniorityList as $key => $SeniorityList) {
$EmployeeId = $SeniorityList->EmployeeId;
// echo $EmployeeId. "<br/>";
// exit;
// Get total Count Promotion/Nomination/Transfer of the Employee
$EmpTotalRecords = EmployeePromotion::where('EmployeeId', $EmployeeId)
->where('IsDeleted', '=', '0')
->where('Status', '=', '1')
->count();
$GetPreviousJoiningDate = EmployeePromotion::select('tbl_employees_promotion_transfer.PromotionTransferDate', 'tbl_employees_promotion_transfer.AppointmentStatus as AppointmentStatus', 'tbl_transfer_status.TransferStatusName as TransferStatusName')
->leftJoin('tbl_transfer_status', 'tbl_employees_promotion_transfer.AppointmentStatus', '=', 'tbl_transfer_status.TransferStatusId')
->where('tbl_employees_promotion_transfer.EmployeeId', '=', $EmployeeId)
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.Status', '=', '1')
//->whereIn('tbl_transfer_status.TransferStatusName', array('Promotion', 'Nomination'))
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC')
//->groupBy('EmployeeId')
->skip(1)
//->offset(1)
->take(1)
//->get()
->first();
$dataPro = json_decode(json_encode($GetPreviousJoiningDate), true);
$PromotionTransferDate = isset($dataPro['PromotionTransferDate']) ? $dataPro['PromotionTransferDate'] : '';
$SeniorityList->PreviousJoiningDate = $PromotionTransferDate;
// echo(json_encode($GetPreviousJoiningDate));
// exit;
$EmployeeName = $SeniorityList->EmployeeName;
$DateOfBirth = date('d-M-y', strtotime($SeniorityList->DateOfBirth));
$Qualification = '--';
$PrevDesignation = $SeniorityList->PrevDesignation;
if ($PrevDesignation) {
$PrevDesignation = $PrevDesignation;
} else {
$PrevDesignation = '--';
}
$PreviousJoiningDate = date('d-M-y', strtotime($SeniorityList->PreviousJoiningDate));
if ($PrevDesignation && $PreviousJoiningDate) {
$PreviousJoiningDate = date('d-M-y', strtotime($SeniorityList->PreviousJoiningDate));
} else {
$PreviousJoiningDate = '--';
}
$DesignationName = $SeniorityList->DesignationName;
$PromotionTransferDate = date('d-M-y', strtotime($SeniorityList->PromotionTransferDate));
$AppointmentStatus = $SeniorityList->AppointmentStatus;
$BasicCategoryName = '--';
if ($SeniorityList->BasicCategoryName) {
$BasicCategoryName = $SeniorityList->BasicCategoryName;
}
$AppointmentCategoryName = '--';
if ($SeniorityList->AppointmentCategoryName) {
$AppointmentCategoryName = $SeniorityList->AppointmentCategoryName;
}
$RetirementDate = date('d-M-y', strtotime($SeniorityList->RetirementDate));
$htmlTableBody .= '<tr>
<td>' . $sn . '</td>
<td><a href="' . url("view-employee/$EmployeeId") . '" target="_blank" >' . $EmployeeName . '</a></td>
<td>' . $DateOfBirth . '</td>
<td>' . $Qualification . '</td>
<td>' . $PrevDesignation . '</td>
<td>' . $PromotionTransferDate . '</td>
<td>' . $AppointmentStatus . '</td>
<td>' . $BasicCategoryName . '</td>
<td>' . $AppointmentCategoryName . '</td>
<td>' . $RetirementDate . '</td>
<td></td>
</tr>';
$sn++;
}
}
return response()->json([
'status' => 200,
'output' => $htmlTableBody,
]);
// echo json_encode($EmpSeniorityList,JSON_PRETTY_PRINT);
//return view('admin.Reports.employee-seniority-list', ['EmpSeniorityList' => $EmpSeniorityList]);
}
### Roster // Abstract 8 //
public function reportRoster()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$EmpRoasterList = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_employees.RetirementDate as RetirementDate', 'tbl_transfer_status.TransferStatusName as AppointmentStatus', 'tbl_employee_category_detail.*', 'tbl_caste.CasteName as CasteName', 'tbl_appointment_category.AppointmentCategoryName as AppointmentCategoryName', 'tbl_designation.DesignationName as DesignationName')
->where('tbl_employees_promotion_transfer.IsDeleted', '=', '0')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
//->whereIn('tbl_transfer_status.TransferStatusName', array('Promotion', 'Nomination'))
->leftJoin('tbl_designation', 'tbl_employees_promotion_transfer.DesignationId', '=', 'tbl_designation.DesignationId')
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->leftJoin('tbl_employee_category_detail', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employee_category_detail.EmployeeId')
->leftJoin('tbl_caste', 'tbl_employee_category_detail.CasteId', '=', 'tbl_caste.CasteId')
->leftJoin('tbl_appointment_category', 'tbl_employee_category_detail.AppointmentCategoryId', '=', 'tbl_appointment_category.AppointmentCategoryId')
->leftJoin('tbl_transfer_status', 'tbl_employees_promotion_transfer.AppointmentStatus', '=', 'tbl_transfer_status.TransferStatusId')
->orderBy('tbl_employees_promotion_transfer.PromotionTransferDate', 'DESC')
//->groupBy('tbl_employees_promotion_transfer.EmployeeId')
->get();
return view('admin.Reports.employee-roster', ['EmpRoasterList' => $EmpRoasterList]);
}
### Employee details for Retirement & Others Purpose // Abstract 9 //
public function employeeDetailRetirement()
{
$this->UserSessionId = session('UserId');
if (empty($this->UserSessionId)) {
return redirect('/');
}
$GetEmpRetirementDetail = EmployeePromotion::select('tbl_employees_promotion_transfer.*', 'tbl_employees.EmployeeCode as EmployeeCode', 'tbl_employees.EmployeeName as EmployeeName', 'tbl_employees.JoiningDate as JoiningDate', 'tbl_employees.DateOfBirth as DateOfBirth', 'tbl_employees.RetirementDate as RetirementDate', 'tbl_actual_work_place.ActualWorkPlaceName as ActualWorkPlace', 'tbl_scheme_name.NameOfScheme as NameOfScheme')
// ->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.EmployeeId', '=', 'tbl_basic_category.EmployeeId')
//->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.EmployeeId', '=', 'tbl_basic_category.EmployeeId')
->leftJoin('tbl_actual_work_place', 'tbl_employees_promotion_transfer.ActualWorkPlaceId', '=', 'tbl_actual_work_place.ActualWorkPlaceId')
->leftJoin('tbl_scheme_name', 'tbl_employees_promotion_transfer.SchemeNameId', '=', 'tbl_scheme_name.SchemeNameId')
->leftJoin('tbl_employees', 'tbl_employees_promotion_transfer.EmployeeId', '=', 'tbl_employees.EmployeeId')
->where('tbl_employees_promotion_transfer.IsCurrentPosition', 1)
->where('tbl_employees_promotion_transfer.Status', '1')
->where('tbl_employees_promotion_transfer.IsDeleted', '0')
->orderBy('tbl_employees.RetirementDate', 'ASC')
//->groupBy('tbl_employees_promotion_transfer.EmployeeId')
->get();
if ($GetEmpRetirementDetail->count() > 0) {
foreach ($GetEmpRetirementDetail as $key => $EmpRetirement) {
$EmployeeId = $EmpRetirement->EmployeeId;
/*** Get Employee Category Detail ***/
$GetCategoryDetail = EmployeeCategoryDetail::select('tbl_employee_category_detail.*', 'tbl_religion.ReligionName as Religion', 'tbl_caste.CasteName as Caste', 'tbl_basic_category.BasicCategoryName as BasicCategory', 'tbl_appointment_category.AppointmentCategoryName as AppointmentCategory', 'tbl_parallel_reservation.ParallelReservationName as ParallelReservation', 'tbl_admin.FirstName as Updated_by', 'tbl_admin.FirstName as Created_by')
->leftJoin('tbl_admin', 'tbl_employee_category_detail.Updated_by', '=', 'tbl_admin.UserId')
->leftJoin('tbl_basic_category', 'tbl_employee_category_detail.BasicCategoryId', '=', 'tbl_basic_category.BasicCategoryId')
->leftJoin('tbl_religion', 'tbl_employee_category_detail.ReligionId', '=', 'tbl_religion.ReligionId')
->leftJoin('tbl_caste', 'tbl_employee_category_detail.CasteId', '=', 'tbl_caste.CasteId')
->leftJoin('tbl_appointment_category', 'tbl_employee_category_detail.AppointmentCategoryId', '=', 'tbl_appointment_category.AppointmentCategoryId')
->leftJoin('tbl_parallel_reservation', 'tbl_employee_category_detail.ParallelReservationId', '=', 'tbl_parallel_reservation.ParallelReservationId')
->where('tbl_employee_category_detail.EmployeeId', $EmployeeId)
->get()
->first();
if ($GetCategoryDetail) {
$CategoryName = $GetCategoryDetail->BasicCategory;
$AppointmentCategory = $GetCategoryDetail->AppointmentCategory;
} else {
$CategoryName = 'N/A';
$AppointmentCategory = 'N/A';
}
$EmpRetirement->CategoryName = $CategoryName;
$EmpRetirement->AppointmentCategory = $AppointmentCategory;
}
}
// print_r(json_encode($GetEmpRetirementDetail));
// exit;
return view('admin.Reports.employee-detail-for-retirement', ['GetEmpRetirementDetail' => $GetEmpRetirementDetail]);
}
}