Untitled

public function getEmployees(Request $req) {

        // ============add function autoremove resigned staff 10/21/2022===========
        $userStatus = [
            'pcu.PCNo' => NULL,
            'DateUpdated' => date('Y/m/d h:i:s'),
        ];

        DB::connection('pcms')
        ->table('PCUsers as pcu')
        ->select('e.EmployeeCode', 'e.EmployeeName', 'pcu.PCNo', 'pcu.DepartmentCode', 'e.RetiredDate')
        ->rightJoin(DB::raw('hrdsql.GroupCompanyInformation.dbo.Employees as e'), 
           'pcu.Employeecode', 'e.EmployeeCode')
        ->where('e.CompanyCode', '=', '003')
        ->whereNotNull('e.RetiredDate')
        ->whereDate('e.RetiredDate', '=', date('Y-m-d'))

        // return $a->toSql();

        ->update($userStatus);

        // =========================================================================

        $empCodeFromPCNo = null;

        if(isset($req->search)) {
            $searchInput = strtoupper($req->search);
            if(str_starts_with($searchInput, 'LAPTOP-') || str_starts_with($searchInput, 'CPU-')) {
                // Search is for a PCNo
                $empCodeFromPCNo = DB::connection('pcms')
                    ->table('PCUsers')
                    ->where('PCNo', $searchInput)
                    ->max('EmployeeCode');
            }
        }


        $data = DB::connection('adminsql1')
        ->table('Employees as emp')
        ->select(
            'emp.*', 
            'CompanyName',
            'DepartmentName',
            'SectionName',
            'TeamName', 
            'pos.PositionName'
        )
        ->leftJoin('Companies as comp', 'comp.CompanyCode', 'emp.CompanyCode')
        ->leftJoin('Positions as pos', function($join) {
            $join->on('emp.CompanyCode', 'pos.CompanyCode')
            ->on('emp.PositionCode', 'pos.PositionCode');
        })
        ->leftJoin('Departments as dept', function($join) {
            $join->on('dept.CompanyCode', 'emp.CompanyCode')
            ->on('dept.DepartmentCode', 'emp.DepartmentCode');
        })
        ->leftJoin('Sections as sect', function($join) {
            $join->on('sect.CompanyCode', 'emp.CompanyCode')
            ->on('sect.SectionCode', 'emp.SectionCode');
        })
        ->leftJoin('Teams as t', function($join) {
            $join->on('t.CompanyCode', 'emp.CompanyCode')
            ->on('t.TeamCode', 'emp.TeamCode');
        })
        // ->leftJoin(DB::raw("SUPPORTSQL1.PCManagementSystem.dbo.PCUsers as pcu"), function($join) {
        //     $join->on('pcu.EmployeeCode', 'emp.EmployeeCode')
        //     ->on('pcu.CompanyCode', 'emp.CompanyCode');
        // })
        

      

        // ->where(function($sub) use($req) {
        //     if(isset($req->search)) {
        //         $sub->where('EmployeeCode', $req->search)
        //         ->orWhere(DB::raw("FirstName + ' ' + LastName"), 'LIKE', '%' . $req->search . '%')
        //         ->orWhere(
        //             'EmployeeCode', 
        //             DB::connection('pcms')
        //             ->table('PCUsers')
        //             ->where('PCNo', $req->search)
        //             ->max('EmployeeCode')
        //         );
        //     }
        //     if(isset($req->selDept)) {
        //         $sub->where('emp.DepartmentCode', $req->selDept);
        //     }
        //     if(isset($req->selSect)) {
        //         $sub->where('emp.SectionCode', $req->selSect);
        //     }
        //     if(isset($req->selTeam)) {
        //         $sub->where('emp.TeamCode', $req->selTeam);
        //     }
        // })
        ->where(function($sub) use($req, $empCodeFromPCNo) {
            if(isset($req->search)) {
                // Normal employee search
                $sub->where('EmployeeCode', $req->search)
                    ->orWhere(DB::raw("FirstName + ' ' + LastName"), 'LIKE', '%' . $req->search . '%');
        
                // Extra condition only if search was LAPTOP-xxx or CPU-xxx
                if($empCodeFromPCNo) {
                    $sub->orWhere('EmployeeCode', $empCodeFromPCNo);
                }
            }
        
            if(isset($req->selDept)) {
                $sub->where('emp.DepartmentCode', $req->selDept);
            }
            if(isset($req->selSect)) {
                $sub->where('emp.SectionCode', $req->selSect);
            }
            if(isset($req->selTeam)) {
                $sub->where('emp.TeamCode', $req->selTeam);
            }
        })
        
        ->where(function($sub) {
            $sub->whereNull('SeparationDate')
            ->orWhere(DB::raw("CAST(LEFT(SeparationDate,11) as DATETIME)"), '>=', date('Y-m-d'));
        })
        ->where(function($sub) use($req) {
            if(isset($req->CompanyCode)) {
                $sub->where('emp.CompanyCode', $req->CompanyCode);
            }
        })
        ->whereNull('emp.DeletedDate')
        ->skip(($req->page - 1) * $req->itemsPerPage)
        ->take($req->itemsPerPage)
        ->get()
        ->map(function($dat) {
            $pcinfo = DB::connection('pcms')
            ->table('PCUsers as pcu')
            ->select(
                'pcu.*', 
                'JobClass',
                DB::raw("
                    (
                        SELECT TOP 1 DateCreated FROM AuditHeaders
                        WHERE EmployeeCode = pcu.EmployeeCode
                        AND CompanyCode = pcu.CompanyCode
                        AND DateDeleted IS NULL
                        ORDER BY DateCreated DESC
                    ) as RecentAuditDate,
                    (
                        SELECT TOP 1 FollowupAuditDate FROM AuditHeaders
                        WHERE EmployeeCode = pcu.EmployeeCode
                        AND CompanyCode = pcu.CompanyCode
                        AND DateDeleted IS NULL
                        ORDER BY DateCreated DESC
                    ) as FollowupAuditDate
                ")
            )
            ->leftJoin('JobClasses as jc', 'jc.JobClassID', 'pcu.JobClassID')
            ->where('pcu.EmployeeCode', $dat->EmployeeCode)
            ->where('pcu.CompanyCode', $dat->CompanyCode)
            ->get();

            if(count($pcinfo)) {
                $dat->JobClass = $pcinfo[0]->JobClass;
                $dat->PCNo = $pcinfo[0]->PCNo;
                $dat->RecentAuditDate = $pcinfo[0]->RecentAuditDate;
                $dat->FollowupAuditDate = $pcinfo[0]->FollowupAuditDate;
                $dat->isTempTIC = $pcinfo[0]->isTempTIC;
            }

            return $dat;
        });

        
        $length = DB::connection('adminsql1')
        ->table('Employees as emp')
        ->leftJoin('Companies as comp', 'comp.CompanyCode', 'emp.CompanyCode')
        ->leftJoin('Departments as dept', function($join) {
            $join->on('dept.CompanyCode', 'emp.CompanyCode')
            ->on('dept.DepartmentCode', 'emp.DepartmentCode');
        })
        ->leftJoin('Sections as sect', function($join) {
            $join->on('sect.CompanyCode', 'emp.CompanyCode')
            ->on('sect.SectionCode', 'emp.SectionCode');
        })
        ->leftJoin('Teams as t', function($join) {
            $join->on('t.CompanyCode', 'emp.CompanyCode')
            ->on('t.TeamCode', 'emp.TeamCode');
        })



        
        // ->leftJoin(DB::raw("
        //     (
        //         SELECT * FROM SUPPORTSQL1.PCManagementSystem.dbo.PCUsers as pc
        //         INNER JOIN JobClasses as jc on jc.JobClassID = pc.JobClassID
        //     )
        
        // as pcu"), function($join) {
        //     $join->on('pcu.EmployeeCode', 'emp.EmployeeCode')
        //     ->on('pcu.CompanyCode', 'emp.CompanyCode');
        // })
        ->where(function($sub) use($req) {
            if(isset($req->search)) {
                $sub->where('EmployeeCode', $req->search)
                ->orWhere(DB::raw("FirstName + ' ' + LastName"), 'LIKE', '%' . $req->search . '%')
                ->orWhere(
                    'EmployeeCode', 
                    DB::connection('pcms')
                    ->table('PCUsers')
                    ->where('PCNo', $req->search)
                    ->max('EmployeeCode')
                );
            }
            if(isset($req->selDept)) {
                $sub->where('emp.DepartmentCode', $req->selDept);
            }
            if(isset($req->selSect)) {
                $sub->where('emp.SectionCode', $req->selSect);
            }
            if(isset($req->selTeam)) {
                $sub->where('emp.TeamCode', $req->selTeam);
            }
        })
        ->where(function($sub) {
            $sub->whereNull('SeparationDate')
            ->orWhere(DB::raw("CAST(LEFT(SeparationDate,11) as DATETIME)"), '>=', date('Y-m-d'));
        })
        ->where(function($sub) use($req) {
            if(isset($req->CompanyCode)) {
                $sub->where('emp.CompanyCode', $req->CompanyCode);
            }
        })
        ->whereNull('emp.DeletedDate')
        ->count();


        $pcNoPerDeptSectTeam = DB::connection('pcms')
        ->table('PCUsers')
        ->where(function($sub) use($req) {
            if(isset($req->CompanyCode)) {
                $sub->where('CompanyCode', $req->CompanyCode);
            }
            if(isset($req->selDept)) {
                $sub->where('DepartmentCode', $req->selDept);
            }
            if(isset($req->selSect)) {
                $sub->where('SectionCode', $req->selSect);
            }
            if(isset($req->selTeam)) {
                $sub->where('TeamCode', $req->selTeam);
            }
        })
        ->whereNull('DateDeleted')
        ->count('PCNo');


        return [
            'tableData' => $data,
            'tableLength' => $length,
            'PCNoPerDeptSectTeam' => $pcNoPerDeptSectTeam,
        ];
    }