<?
php
namespace App\Livewire\User;
use App\Enums\UserRole;
use App\Exports\ArrayWithMappingExport;
use App\Helpers\AdvancedImporterHelper;
use App\Helpers\FormattedCodeHelper;
use App\Livewire\Forms\UserForm;
use App\Livewire\JengeBaseClass;
use App\Models\User;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Illuminate\Validation\Rule;
use Livewire\Attributes\On;
use Livewire\Attributes\Title;
use Livewire\WithFileUploads;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use Maatwebsite\Excel\Events\AfterSheet;
use Throwable;
use Flux\Flux;
#[Title('SGA | C-HRMS | Users')]
class UserTabular extends JengeBaseClass
{
use WithFileUploads;
private const SORTABLE_COLUMNS = ['code', 'created_at'];
private const SEARCHABLE_COLUMNS = ['code', 'first_name', 'last_name',
'username', 'email', 'phone_number'];
private const CACHE_TTL = 600;
public UserForm $form;
public ?string $selectedId = null;
public string $sortBy = 'created_at';
public string $sortDirection = 'desc';
public ?string $search = null;
public bool $dryRun = false;
public bool $previewMode = false;
public int $chunkSize = 500;
public ?array $result = null;
public ?array $users = [];
public ?array $selectedUsers = [];
public ?array $invalidResult = null;
public ?array $validResult = null; // 10 minutes in seconds
protected $rules = [
'file' => ['required', 'file', 'mimes:csv,xlsx,xls', 'max:10240'],
];
public $file;
public function mount(): void
{
$this->getUsers();
$this->form->code = FormattedCodeHelper::getNextFormattedCode(User::class,
'SGA', 5);
}
// New method to search within the cached users array
#[On('reload-page')]
public function getUsers(): void
{
$cacheKey = 'users_' . md5($this->sortBy . $this->sortDirection);
$this->users = User::query()
->orderBy($this->sortBy, $this->sortDirection)
->lazy()
->toArray();
// Apply search filter if search term exists
if ($this->search) {
$this->searchUsers();
}
public function selectRow($id): void
{
// dd($id);
$this->selectedId = $id;
$this->selectedUsers[]=$id;
$this->form->setData(User::find($id));
}
public function deleteUser($id): void
{
$user = User::find($id);
$user->delete();
Flux::toast('User '.$user->first_name.' '. $user->last_name.' was deleted
successfully', 'User deleted success',5000,'success','top-right');
$this->getUsers();
public function resetPassword($id): void
{
Flux::modal('UserLoadingPage')->show();
$password = Str::random(12);
while (!preg_match('/^(?=.*[a-z])(?=.*[A-Z])(?=.*\d).{6,}$/', $password)) {
$password = Str::random(12);
}
$user = User::findOrFail($id);
$user->password = Hash::make($password);
$user->save();
$this->dispatch('[user]Notification', ['message' => "Password for user
$user->first_name".$user->last_name ." has been reset to <code>{$password}</code>",
'type' => 'info']);
Flux::toast('Password for user '.$user->first_name.' '. $user->last_name.'
has been reset successfully to " '.$password.' "', 'Password reset
success',15000,'success','top-right');
Flux::modal('UserLoadingPage')->close();
/**
* @throws Throwable
*/
public function newUser(): void
{
$this->selectedId = null;
$this->form->reset();
$this->form->code = FormattedCodeHelper::getNextFormattedCode(User::class,
'SGA', 5);
/**
* @throws Throwable
*/
public function resetForm(): void
{
$this->newUser();
}
/**
* @throws Throwable
*/
public function updateUser(): void
{
if (!$this->selectedId) {
Log::info('Cannot save user while no user is selected');
Flux::toast('Cannot update user while no user is selected. Please
select a user from the list to update.', 'Error', 10000,variant:
'danger',position:"top right");
$this->dispatch('userNotification', ['message' => 'No user selected to
save.', 'type' => 'error']);
sleep(1);
Flux::modal('loadingPage')->close();
return;
}
$result = $this->form->updateData();
if ($result['success']) {
$this->newUser();
$this->dispatch('userNotification', ['message' => $result['message'],
'type' => 'success']);
Flux::toast($result['message'], 'Success', 10000,variant:
'success',position:"top right");
$this->getUsers();
} else {
$this->dispatch('userNotification', ['message' => $result['message'],
'type' => 'error']);
}
}
/**
* @throws Throwable
*/
public function saveAs(): void
{
if ($this->selectedId) {
Log::info("Cannot create new user while user is selected: ID {$this-
>selectedId}");
$this->dispatch('userNotification', ['message' => 'Cannot create new
user while a user is selected.', 'type' => 'error']);
return;
}
$result = $this->form->storeData();
if ($result['success']) {
$this->getUsers();
$this->selectedId = $result['user']->id; // Set new user as selected
$this->form->setData($result['user']);
$this->search = null;
Flux::toast($result['message'], 'Success', 10000,variant:
'success',position:"top right");
$this->newUser();
} else {
sleep(1);
Flux::modal('loadingPage')->close();
$this->dispatch('userNotification', ['message' => $result['message'],
'type' => 'error']);
return;
}
}
public function unverifyingEmail($id): void
{
$user = User::findOrFail($id);
$user->email_verified_at = null;
$user->save();
$this->dispatch('[user]Notification', ['message' => "Email verification for
user {$user->first_name} {$user->last_name} has been revoked.", 'type' => 'info']);
Flux::toast('Email verification for user '.$user->first_name.' '. $user-
>last_name.' has been revoked successfully', 'Email verification revoked
success',15000,'success','top-right');
public function verifyingEmail($id): void
{
$user = User::findOrFail($id);
$user->email_verified_at = now();
$user->save();
$this->dispatch('[user]Notification', ['message' => "Email verification for
user {$user->first_name} {$user->last_name} has been granted.", 'type' => 'info']);
Flux::toast('Email verification for user '.$user->first_name.' '. $user-
>last_name.' has been granted successfully', 'Email verification granted
success',15000,'success','top-right');
public function unverifyingPassword($id): void
{
$user = User::findOrFail($id);
$user->password_changed_at = null;
$user->save();
$this->dispatch('[user]Notification', ['message' => "Password verification
for user {$user->first_name} {$user->last_name} has been revoked.", 'type' =>
'info']);
Flux::toast('Password verification for user '.$user->first_name.' '. $user-
>last_name.' has been revoked successfully', 'Password verification revoked
success',15000,'success','top-right');
public function verifyingPassword($id): void
{
$user = User::findOrFail($id);
$user->password_changed_at = now();
$user->save();
$this->dispatch('[user]Notification', ['message' => "Password verification
for user {$user->first_name} {$user->last_name} has been granted.", 'type' =>
'info']);
Flux::toast('Password verification for user '.$user->first_name.' '. $user-
>last_name.' has been granted successfully', 'Password verification granted
success',15000,'success','top-right');
public function unverifyingPhone($id): void
{
$user = User::findOrFail($id);
$user->phone_verified_at = null;
$user->save();
$this->dispatch('[user]Notification', ['message' => "Phone verification for
user {$user->first_name} {$user->last_name} has been revoked.", 'type' => 'info']);
Flux::toast('Phone verification for user '.$user->first_name.' '. $user-
>last_name.' has been revoked successfully', 'Phone verification revoked
success',15000,'success','top-right');
public function verifyingPhone($id): void
{
$user = User::findOrFail($id);
$user->phone_verified_at = now();
$user->save();
$this->dispatch('[user]Notification', ['message' => "Phone verification for
user {$user->first_name} {$user->last_name} has been granted.", 'type' => 'info']);
Flux::toast('Phone verification for user '.$user->first_name.' '. $user-
>last_name.' has been granted successfully', 'Phone verification granted
success',15000,'success','top-right');
public function searchUsers(): void
{
if (!$this->search) {
// If no search term, reset to full cached users
$this->getUsers();
return;
}
$searchTerm = strtolower($this->search);
$this->users = array_filter($this->users, function ($user) use
($searchTerm) {
foreach (self::SEARCHABLE_COLUMNS as $column) {
if (isset($user[$column]) &&
str_contains(strtolower($user[$column]), $searchTerm)) {
return true;
}
}
return false;
});
}
public function dispatchSelectUser($id): void
{
$this->selectedId = $id;
$this->dispatch('user-selected', ['id' => $id]);
}
// React to search input changes
#[On('search-updated')]
public function updatedSearch($value): void
{
$this->search = $value;
$this->searchUsers();
}
public function render(): object
{
return view('[Link]-tabular', [
'roles' => collect(UserRole::detailedList())
->mapWithKeys(function ($item) {
return [
$item['key'] instanceof UserRole ? $item['key']->value :
$item['key'] => $item['label']
];
})
->toArray(),
]);
}
/**
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function exportUsers(): BinaryFileResponse
{
$exportData = collect($this->users)->map(function ($user) {
return [
'code' => (string) $user['code'],
'first_name' => (string) $user['first_name'],
'middle_name' => (string) $user['middle_name'],
'last_name' => (string) $user['last_name'],
'username' => (string) $user['username'],
'email' => (string) $user['email'],
'phone_number'=> (string) $user['phone_number'], // 🔑 Cast here
'role' => (string) $user['role'],
];
})->toArray();
$headings = ['Code', 'First Name', 'Middle Name', 'Last Name', 'Username',
'Email', 'Phone Number', 'Role'];
$mappedHeadings = ['code', 'first_name', 'middle_name', 'last_name',
'username', 'email', 'phone_number', 'role'];
$styles = [
1 => [
'font' => ['bold' => true, 'size' => 13],
'alignment' => ['horizontal' => 'center'],
'fill' => [
'fillType' => Fill::FILL_SOLID,
'startColor' => ['rgb' => 'D9E1F2'],
],
],
];
$columnFormats = [
'A' => NumberFormat::FORMAT_TEXT,
'B' => NumberFormat::FORMAT_TEXT,
'C' => NumberFormat::FORMAT_TEXT,
'D' => NumberFormat::FORMAT_TEXT,
'E' => NumberFormat::FORMAT_TEXT,
'F' => NumberFormat::FORMAT_TEXT,
'G' => NumberFormat::FORMAT_TEXT,
'H' => NumberFormat::FORMAT_TEXT,
];
$dropdownOptions = ['H' => ['Admin', 'Manager', 'Staff', 'HR']];
$events = [
AfterSheet::class => function (AfterSheet $event) use
($dropdownOptions) {
$sheet = $event->sheet->getDelegate();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Freeze header
$sheet->freezePane('A2');
// Auto-size all columns
foreach (range('A', $highestColumn) as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// Apply thin border to all used cells
$sheet->getStyle("A1:{$highestColumn}{$highestRow}")
->getBorders()
->getAllBorders()
->setBorderStyle(Border::BORDER_THIN);
}
];
return Excel::download(
new ArrayWithMappingExport(
$exportData,
$headings,
$mappedHeadings,
$styles,
$columnFormats,
$events
),
'users_' . now()->format('Ymd_His') . '.xlsx'
);
}
/**
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function exportUserTemplate(): BinaryFileResponse
{
$headings = ['Code', 'First Name', 'Middle Name', 'Last Name', 'Username',
'Email', 'Phone Number', 'Role'];
$mappedHeadings = ['code', 'first_name', 'middle_name', 'last_name',
'username', 'email', 'phone_number', 'role'];
$styles = [
1 => [
'font' => ['bold' => true, 'size' => 13],
'alignment' => ['horizontal' => 'center'],
'fill' => [
'fillType' => Fill::FILL_SOLID,
'startColor' => ['rgb' => 'D9E1F2'],
],
],
];
$columnFormats = array_fill_keys(range('A', 'H'),
NumberFormat::FORMAT_TEXT);
$roles = UserRole::values(); // returns: ['Super Admin', 'Company
Admin', ...]
$dropdownColumn = 'H';
$dropdownRangeName = 'RoleOptions';
$events = [
AfterSheet::class => function (AfterSheet $event) use ($roles,
$dropdownColumn, $dropdownRangeName) {
$mainSheet = $event->sheet->getDelegate();
$spreadsheet = $mainSheet->getParent();
// Create hidden sheet with dropdown options
$hiddenSheet = new Worksheet($spreadsheet, 'hidden');
$spreadsheet->addSheet($hiddenSheet);
$spreadsheet->setActiveSheetIndexByName('hidden');
foreach ($roles as $index => $role) {
$hiddenSheet->setCellValue("A" . ($index + 1), $role);
}
// Define named range for dropdown (A1:A{count})
$cellRange = "'hidden'!\$A\$1:\$A\$" . count($roles);
$spreadsheet->addNamedRange(
new NamedRange(
$dropdownRangeName,
$hiddenSheet,
$cellRange
)
);
// Hide the sheet
$hiddenSheet->setSheetState(Worksheet::SHEETSTATE_HIDDEN);
// Switch back to main sheet
$spreadsheet->setActiveSheetIndexByName($mainSheet->getTitle());
// Format main sheet
$mainSheet->freezePane('A2');
$highestRow = 50;
foreach (range('A', 'H') as $col) {
$mainSheet->getColumnDimension($col)->setAutoSize(true);
}
$mainSheet->getStyle("A1:H$highestRow")
->getBorders()
->getAllBorders()
->setBorderStyle(Border::BORDER_THIN);
// Apply dropdown validation to column H
for ($row = 2; $row <= $highestRow+3000; $row++) {
$validation = $mainSheet->getCell("{$dropdownColumn}{$row}")-
>getDataValidation();
$validation->setType(DataValidation::TYPE_LIST);
$validation->setErrorStyle(DataValidation::STYLE_STOP);
$validation->setAllowBlank(true);
$validation->setShowDropDown(true);
$validation->setFormula1("={$dropdownRangeName}");
}
},
];
return Excel::download(
new ArrayWithMappingExport(
[], // no data
$headings,
$mappedHeadings,
$styles,
$columnFormats,
$events
),
'user_template_' . now()->format('Ymd_His') . '.xlsx'
);
}
public function getImportModal(): void
{
Flux::modal('importUsers')->show();
}
protected function userConfig(): array
{
$emailRegex = '/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/';
$phoneRegex = '/^\+?[1-9]\d{1,14}$/';
$RwPhoneRegex = '/^(\+2507[2-38-9]\d{7}|07[2-38-9]\d{7})$/'; // Rwanda-
specific phone number
$CombinedPhoneRegex = '/^(?:07[2-9]\d{7}|\+2507[2-9]\d{7}|\+\d{7,15})$/';
$CombinedMaskedPhoneRegex = '/^(\+?\d[\d\s\-\(\)]{6,20}|\s*07[2-9][\d\s\-\
(\)]{7,15})$/';
return [
'rules' => [
'code' => ['nullable', 'string', 'max:255', Rule::unique('users',
'code')],
'first_name' => ['required', 'string', 'max:255'],
'middle_name' => ['nullable', 'string', 'max:255'],
'last_name' => ['nullable', 'string', 'max:255'],
'username' => ['required', 'string', 'max:255'],
'email' => ['required', 'string', 'max:255', 'regex:' .
$emailRegex, Rule::unique('users', 'email')],
'phone_number' => ['nullable', 'max:255', 'regex:' . $RwPhoneRegex,
Rule::unique('users', 'phone_number')],
'phone_verified_at' => ['nullable', 'date'],
'email_verified_at' => ['nullable', 'date'],
'password_changed_at' => ['nullable', 'date'],
'role' => ['required','in:' . implode(',', UserRole::getValues())],
'password' => ['nullable'],
],
'fieldMap' => [
'Code' => 'code',
'First Name' => 'first_name',
'Middle Name' => 'middle_name',
'Last Name' => 'last_name',
'Username' => 'username',
'Email' => 'email',
'Phone Number' => 'phone_number',
'Role' => 'role',
],
'fixedValues' => [
'phone_verified_at' => null,
'email_verified_at' => null,
'password_changed_at' => null,
'password' => 'ChangeMe@123',
],
'lookupFields' => [],
'customMessages' => [
'[Link]' => 'The user code is already in use. Please use a
different code.',
'[Link]' => 'The user code must be a valid string.',
'[Link]' => 'The user code cannot exceed 255 characters.',
'first_name.required' => 'The first name is required.',
'first_name.string' => 'The first name must be a valid string.',
'first_name.max' => 'The first name cannot exceed 255 characters.',
'middle_name.string' => 'The middle name must be a valid string.',
'middle_name.max' => 'The middle name cannot exceed 255
characters.',
'last_name.string' => 'The last name must be a valid string.',
'last_name.max' => 'The last name cannot exceed 255 characters.',
'[Link]' => 'The username is required.',
'[Link]' => 'The username must be a valid string.',
'[Link]' => 'The username cannot exceed 255 characters.',
'[Link]' => 'The email address is required.',
'[Link]' => 'The email address must be a valid string.',
'[Link]' => 'The email address cannot exceed 255 characters.',
'[Link]' => 'Please enter a valid email address (e.g.,
user@[Link]).',
'[Link]' => 'This email address is already registered.',
'phone_number.regex' => 'Please enter a valid Rwanda phone number
(e.g., +2507xxxxxxxx or 07xxxxxxxx).',
'phone_number.string' => 'The phone number must be a valid
string.',
'phone_number.max' => 'The phone number cannot exceed 255
characters.',
'phone_number.unique' => 'This phone number is already
registered.',
'phone_verified_at.date' => 'The phone verification date must be a
valid date.',
'email_verified_at.date' => 'The email verification date must be a
valid date.',
'password_changed_at.date' => 'The password change date must be a
valid date.',
'[Link]' => 'Please select a user role.',
'[Link]' => 'The password must be a valid string.',
'[Link]' => 'The password cannot exceed 16 characters.',
'[Link]' => 'The password and confirmation do not
match.',
'[Link]' => 'The password must be at least 6 characters
long and include at least one lowercase letter, one uppercase letter, and one
number.',
'password_confirmation.string' => 'The password confirmation must
be a valid string.',
'password_confirmation.max' => 'The password confirmation cannot
exceed 16 characters.',
],
'targetModel'=>User::class
];
}
/**
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function exportInvalidUser(): BinaryFileResponse
{
$headings = [
'Code', 'First Name', 'Middle Name', 'Last Name',
'Username', 'Email', 'Phone Number', 'Role', 'Errors'
];
$mappedHeadings = [
'code', 'first_name', 'middle_name', 'last_name',
'username', 'email', 'phone_number', 'role', 'errors'
];
$styles = [
1 => [
'font' => ['bold' => true, 'size' => 13],
'alignment' => ['horizontal' => 'center'],
'fill' => [
'fillType' => Fill::FILL_SOLID,
'startColor' => ['rgb' => 'FFF2CC'],
],
],
];
$columnFormats = [
'G' => NumberFormat::FORMAT_TEXT, // Phone
'I' => NumberFormat::FORMAT_TEXT, // Errors (column I)
];
$events = [
AfterSheet::class => function (AfterSheet $event) {
$sheet = $event->sheet->getDelegate();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$sheet->freezePane('A2');
foreach (range('A', $highestColumn) as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
$sheet->getStyle("A1:{$highestColumn}{$highestRow}")-
>applyFromArray([
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => '000000'],
],
],
]);
// Wrap text for column I (errors)
$sheet->getStyle("I2:I{$highestRow}")
->getAlignment()->setWrapText(true);
},
];
$invalidUsers = collect($this->result['invalid'])->map(function ($user) {
$errors=$user['errors']??[];
$errorString = collect($errors)->map(function ($msgs, $field) {
return "$field: " . implode(', ', $msgs);
})->implode("\n");
return [
'code' => (string) $user['code'],
'first_name' => (string) $user['first_name'],
'middle_name' => (string) $user['middle_name'],
'last_name' => (string) $user['last_name'],
'username' => (string) $user['username'],
'email' => (string) $user['email'],
'phone_number' => (string) $user['phone_number'],
'role' => (string) $user['role'],
'errors' => $errorString,
];
})->toArray();
return Excel::download(
new ArrayWithMappingExport($invalidUsers, $headings, $mappedHeadings,
$styles, $columnFormats, $events),
'invalid_users_import_' . now()->format('Ymd_His') . '.xlsx'
);
}
/**
* @throws \Exception
*/
public function importToArray(): void
{
$config = $this->userConfig();
try {
$filePath = $this->file->store('uploads', 'public');
$this->result = AdvancedImporterHelper::importToArray(
storage_path('app/public/' . $filePath),
$config['rules'],
$config['fieldMap'],
$config['fixedValues'],
$config['lookupFields'],
$config['customMessages'],
$this->chunkSize,
null,
fn($processed, $total) => $this->throttledDispatch($processed,
$total)
);
// dd($this->result['invalid']);
} catch (Exception $e) {
dd($e);
}
}
protected function throttledDispatch(int $processed, int $total): void
{
static $lastDispatch = 0;
if ($processed - $lastDispatch >= 10 || $processed === $total) {
$lastDispatch = $processed;
$this->dispatch('import-progress', [
'processed' => $processed,
'total' => $total,
'percentage' => round(($processed / $total) * 100, 2),
]);
}
}
/**
* @throws Throwable
*/
public function saveValidData(): void
{
$this->invalidResult = []; // To store failed records
$this->validResult = $this->result['valid'];
$successCount = 0;
$totalRecords = count($this->validResult);
collect($this->validResult)->chunk($this->chunkSize)->each(function
($chunk) use (&$successCount) {
DB::transaction(function () use ($chunk, &$successCount) {
foreach ($chunk as $data) {
try {
User::create($data); // Triggers creating, created, saving,
saved events
$successCount++;
} catch (QueryException $e) {
$this->invalidResult[] = array_merge($data,
['error_message' => $e->getMessage()]);
Log::error("User creation failed", [
'error' => $e->getMessage(),
'trace' => $e->getTraceAsString(),
]);
}
}
});
});
$this->getUsers();
}