· 4 months ago · Jun 09, 2025, 11:55 PM
1GitHub Copilot: # Code Analysis: upload_masterlist.php
2
3This PHP script handles the upload and processing of an Excel file containing employee data. The file is processed and the employee information is inserted into an `approval_table` database table. Here's how it works:
4
5## Functionality Overview
6
71. The script uses PhpSpreadsheet library to read Excel files
82. It processes each row of the uploaded spreadsheet (skipping the header)
93. For each row, it extracts employee data and inserts it into the approval_table
104. It generates employee IDs and passwords when not provided
115. It validates data and handles date conversions from Excel format
12
13## Key Components
14
15- **Initial Setup**: Error reporting, includes, and response structure
16- **Helper Functions**:
17 - `generatePassword()`: Creates an 8-digit numeric password
18 - `generateCompanyNum()`: Creates a unique employee ID number
19 - `nullIfEmpty()`: Converts empty values to NULL
20- **File Upload Handling**: Validates that a file was uploaded
21- **Data Processing**: Row-by-row extraction of employee data
22- **Data Validation**: Email uniqueness check across multiple tables
23- **Date Conversion**: Handles Excel date formats and string dates
24- **Database Insertion**: Prepares and executes SQL to insert employee records
25
26## Potential Problems
27
281. **Security Issues**:
29 - No sanitization of input data before database insertion (relies solely on prepared statements)
30 - No validation of file type before processing (could allow malicious files)
31 - Simplified password generation that's predictable and insecure
32
332. **Date Handling Issues**:
34 - The `convertDate()` function is defined inside a conditional block, potentially causing scope issues
35 - Falls back to current date for invalid dates without clear warning
36
373. **Error Handling**:
38 - Some errors only logged (not returned to user)
39 - Inconsistent error reporting between console logs and response details
40 - Missing validation for many fields before database insertion
41
424. **Data Integrity**:
43 - No transaction handling for batch processing
44 - No checks for required fields or data type validation
45 - No validation of data format (email format, phone numbers, etc.)
46
475. **Performance Issues**:
48 - No batch processing for large files
49 - Each row requires a separate database query for email checking
50
516. **Code Structure**:
52 - Inconsistent parameter binding (mixing different data types in a single prepared statement)
53 - Long parameter list makes maintenance difficult
54 - Nested try-catch blocks could be simplified
55
567. **Missing Features**:
57 - No progress reporting for large files
58 - Limited feedback on specific row insertion failures
59 - No option to update existing records
60
61This script would benefit from improved validation, better error messaging, more robust security measures, and possibly refactoring into smaller, more focused functions.
62
63captione-bot: can this code be able to add new unique employees from a new masterlist file and add them to the table it should insert into
64
65GitHub Copilot: # Employee Upload Functionality Analysis
66
67Yes, the code is designed to add new unique employees from a masterlist Excel file into the `approval_table`. Here's how it works specifically for this purpose:
68
69## The Uniqueness Check
70
71The script performs a uniqueness check based on employee email addresses:
72
73```php
74// Check if email already exists
75$emailCheckStmt = $conn->prepare("
76 SELECT emp_email FROM employee_table WHERE emp_email = ?
77 UNION
78 SELECT emp_email FROM approval_table WHERE emp_email = ?
79");
80$emailCheckStmt->bind_param("ss", $emp_email, $emp_email);
81$emailCheckStmt->execute();
82$emailCheckStmt->store_result();
83
84if ($emailCheckStmt->num_rows > 0) {
85 $response['details'][] = "Skipping Row $rowIndex: Email $emp_email already exists.";
86 $emailCheckStmt->close();
87 continue;
88}
89```
90
91This check ensures:
921. The email doesn't exist in the `employee_table`
932. The email doesn't exist in the `approval_table`
94
95If an email already exists, that row is skipped, and the system continues processing the next employee.
96
97## ID Generation
98
99For employees without company numbers, the code automatically generates one:
100
101```php
102if (empty($emp_company_num)) {
103 $emp_company_num = generateCompanyNum($conn);
104}
105```
106
107The `generateCompanyNum()` function creates a unique ID in the format `EMP-YYYY-#########` where YYYY is the current year and ######### is a sequential number.
108
109## Data Insertion
110
111If an employee is unique (based on email), their data is inserted into the `approval_table` with this statement:
112
113```php
114$stmt = $conn->prepare("INSERT INTO approval_table (...) VALUES (...)");
115```
116
117## Conclusion
118
119This code can successfully add new unique employees from a masterlist file to the `approval_table`. However, be aware of these limitations:
120
1211. **Uniqueness is only based on email** - If two employees have the same email but different names/details, one will be skipped
1222. **No update functionality** - It can't update existing employee records, only add new ones
1233. **No batch processing** - Each row is processed individually, which could be slow for large files
124
125If you need to update existing employees or check uniqueness based on multiple fields (like employee ID + name), the code would need to be modified.
126
127captione-bot: okay it actually goes through the approval table first. given the information i know how to diagnose my problem right now
128
129GitHub Copilot: ## Approval Table Process Understanding
130
131Yes, you're correct. This script actually processes the uploaded employee data into the `approval_table` first, not directly into the `employee_table`. This is important to understand for diagnosing your issue.
132
133The workflow appears to be:
134
1351. Excel file is uploaded with employee data
1362. Script checks if emails are unique across both tables:
137 ```php
138 SELECT emp_email FROM employee_table WHERE emp_email = ?
139 UNION
140 SELECT emp_email FROM approval_table WHERE emp_email = ?
141 ```
1423. If unique, it inserts the new employee into the `approval_table`
1434. Presumably, there's a separate approval process (not shown in this code) that would later move approved records from `approval_table` to `employee_table`
144
145This two-step process allows for validation/approval before employees are officially added to the system.
146
147Common issues you might be experiencing:
148
1491. **Records stuck in approval**: If employees appear in the approval table but not employee_table, they may need manual approval
150
1512. **Duplicate detection**: If you're trying to re-upload employees and they're being skipped, it's because their emails already exist
152
1533. **Missing data in application**: If your application queries only `employee_table` but new employees are only in `approval_table`, they won't appear
154
1554. **Field mapping issues**: If some fields in your Excel don't match what the code expects, you may get partial or malformed data
156
157To diagnose effectively, check:
158- Are records being inserted into `approval_table` successfully?
159- Is there an approval mechanism that needs to be triggered?
160- Are there any error messages in your logs that provide more detail?
161
162captione-bot: our system actually consist of two applications - the HRIS and the payroll, and the payroll can be independent. since HRIS has the code for the approval, it leaves the independent payroll application's uploaded masterlist employees stuck at the approval table. can you give me any possible solutions for this?
163
164GitHub Copilot: # Solutions for Handling Masterlist Employees in Approval Table
165
166Since your system consists of two separate applications (HRIS and Payroll), with HRIS handling the approval process, you have a bottleneck where uploaded employees in the Payroll system are stuck in the approval table when Payroll operates independently. Here are potential solutions:
167
168## Option 1: Auto-Approve for Payroll Module
169```php
170// Add this at the end of the existing upload code before response echo
171if (isset($_POST['source']) && $_POST['source'] === 'payroll') {
172 // Get all pending records
173 $query = "SELECT * FROM approval_table WHERE emp_status = 'pending' OR emp_status IS NULL";
174 $result = $conn->query($query);
175
176 while ($employee = $result->fetch_assoc()) {
177 // Insert into employee_table
178 $insertStmt = $conn->prepare("INSERT INTO employee_table
179 (emp_fname, emp_mname, emp_lname, /* ...all other fields... */)
180 VALUES (?, ?, ?, /* ...all other values... */)");
181
182 // Bind all parameters from $employee array
183 $insertStmt->bind_param("sss...s",
184 $employee['emp_fname'],
185 $employee['emp_mname'],
186 $employee['emp_lname'],
187 // ... bind all other fields
188 );
189
190 $insertStmt->execute();
191
192 // Update approval status
193 $updateStmt = $conn->prepare("UPDATE approval_table SET emp_status = 'approved' WHERE id = ?");
194 $updateStmt->bind_param("i", $employee['id']);
195 $updateStmt->execute();
196 }
197
198 $response['message'] .= " Employees automatically approved for payroll system.";
199}
200```
201
202## Option 2: Add Direct Employee Import Function for Payroll
203
204Create a separate upload script specifically for the payroll system that bypasses the approval table:
205
206```php
207<?php
208// Similar code to upload_masterlist.php but inserts directly to employee_table
209
210require 'vendor/autoload.php';
211use PhpOffice\PhpSpreadsheet\IOFactory;
212use PhpOffice\PhpSpreadsheet\Shared\Date;
213
214include '../db_connection.php';
215
216// ...existing functions and validations...
217
218// Remove the approval table check from the email uniqueness check
219$emailCheckStmt = $conn->prepare("SELECT emp_email FROM employee_table WHERE emp_email = ?");
220$emailCheckStmt->bind_param("s", $emp_email);
221
222// Skip approval table, insert directly to employee_table
223$stmt = $conn->prepare("INSERT INTO employee_table (/* fields */) VALUES (/* values */)");
224// ...bind parameters and execute...
225```
226
227## Option 3: Scheduled Task to Approve Records
228
229Create a cron job or scheduled task that automatically approves records after a certain time:
230
231```php
232<?php
233include '../db_connection.php';
234
235// Get records older than X hours
236$hours = 24; // Configure as needed
237$query = "SELECT * FROM approval_table WHERE
238 created_at < DATE_SUB(NOW(), INTERVAL $hours HOUR) AND
239 (emp_status = 'pending' OR emp_status IS NULL)";
240
241$result = $conn->query($query);
242$count = 0;
243
244while ($employee = $result->fetch_assoc()) {
245 // Insert into employee_table and update approval status
246 // Similar to Option 1
247 $count++;
248}
249
250echo "Auto-approved $count employee records";
251```
252
253## Option 4: Database View Solution
254
255Create a database view that combines both tables for the payroll system:
256
257```sql
258CREATE VIEW employee_combined_view AS
259SELECT * FROM employee_table
260UNION
261SELECT * FROM approval_table WHERE emp_status = 'pending' OR emp_status IS NULL;
262```
263
264Then modify your payroll application to use this view instead of just the employee_table.
265
266## Option 5: Add Configuration Parameter
267
268Add a configuration setting that determines whether the approval process is required:
269
270```php
271// Add to the beginning of upload_masterlist.php
272$config = include '../config.php';
273$requireApproval = $config['require_employee_approval'] ?? true;
274
275// Then modify the insert logic
276if ($requireApproval) {
277 // Insert into approval_table as currently done
278} else {
279 // Insert directly to employee_table
280}
281```
282
283## Recommendation
284
285Option 1 is likely the most straightforward solution as it allows you to maintain your current code structure but add a parameter to skip approval when needed for the payroll system. You simply need to modify your front-end to pass a 'source' parameter when uploading from the payroll module.
286
287Each solution has different implications for your data integrity and workflow. Choose based on your specific business rules and how strictly you want to enforce the approval process.
288
289captione-bot: would option one have implications when i use the payroll with the hris integrated?
290
291GitHub Copilot: # Implications of Option 1 When Using Integrated HRIS and Payroll
292
293Yes, Option 1 (auto-approval for payroll module) would have several implications when you operate with both systems integrated:
294
295## Potential Issues with Integration
296
2971. **Duplicate Approval Processing**:
298 - When payroll auto-approves employees, and HRIS later processes the same records, you might get conflicting states or duplicate processing attempts
299
3002. **Approval Workflow Bypass**:
301 - Auto-approval circumvents any validation checks, approver assignments, or multi-stage approval workflows in the HRIS system
302 - Any custom business rules enforced during HRIS approval would be skipped
303
3043. **Data Inconsistency**:
305 - Records approved through payroll might have different quality/validation standards than those approved through HRIS
306 - Could result in inconsistent employee data between systems
307
3084. **Audit Trail Problems**:
309 - Auto-approved records wouldn't have proper approval audit trails
310 - Compliance issues if your system requires documented approval processes
311
312## Better Solutions for Integrated Environment
313
314If you run both systems together sometimes and separately other times, consider these alternative approaches:
315
316### 1. System Mode Parameter
317
318```php
319// At the beginning of upload_masterlist.php
320$operatingMode = $_POST['system_mode'] ?? 'integrated'; // Options: 'integrated', 'payroll_only'
321
322// Then near the end of your processing logic
323if ($operatingMode === 'payroll_only') {
324 // Copy from approval_table to employee_table with a 'payroll_auto_approved' flag
325 // This allows HRIS to see which records were auto-approved when integrated
326}
327```
328
329### 2. Configuration-Based Approach
330
331Create a configuration file that defines the environment:
332
333```php
334// In config.php
335return [
336 'environment' => getenv('APP_ENV') ?: 'integrated', // 'integrated', 'hris_only', 'payroll_only'
337 'auto_approve_payroll' => getenv('APP_ENV') === 'payroll_only'
338];
339
340// Then in upload_masterlist.php
341$config = include '../config.php';
342if ($config['auto_approve_payroll']) {
343 // Auto-approve logic
344}
345```
346
347### 3. Database Flag with Manual Review
348
349```php
350// Add a source column to approval_table
351// When inserting:
352$source = isset($_POST['source']) ? $_POST['source'] : 'hris';
353// Include $source in your INSERT statement
354
355// Add a UI element in HRIS that shows payroll-sourced employees for expedited review
356// This maintains approval workflow but highlights records needing quick approval
357```
358
359## Recommendation for Integrated Environment
360
361For an environment that operates in both integrated and standalone modes, I'd recommend:
362
3631. Add a `source` column to your `approval_table` to track where records originated
3642. Create a configuration setting that determines the operating mode
3653. Add an expedited approval process in HRIS for payroll-sourced records
3664. Implement a notification system to alert HRIS admins when payroll adds employees
367
368This approach maintains data integrity and approval workflows while addressing the immediate need to use both systems independently when required.
369
370captione-bot: would a separate upload_masterlist file alleviate those problems?
371
372GitHub Copilot: # Separate Upload File for Payroll - Analysis
373
374Yes, creating a separate `payroll_upload_masterlist.php` file for the payroll system would alleviate most of the integration issues. This approach (similar to Option 2 I mentioned earlier) has several advantages:
375
376## Benefits of Separate Upload Files
377
3781. **Clear Separation of Concerns**:
379 - Each system has its own dedicated upload functionality
380 - No need for complex conditional logic in a single file
381
3822. **Independent Operation**:
383 - Payroll system can run independently without approval bottlenecks
384 - HRIS approval workflow remains intact when used
385
3863. **Avoids Integration Conflicts**:
387 - No risk of duplicate processing or approval conflicts
388 - Each system follows its own defined workflow
389
3904. **Simplified Maintenance**:
391 - Changes to one system's upload process won't affect the other
392 - Easier to debug issues specific to each system
393
394## Implementation Approach
395
396The payroll version would be similar to your current file but with these key differences:
397
398```php
399<?php
400// Similar header, includes, and functions as upload_masterlist.php
401
402try {
403 // File processing and validation code (same as original)
404
405 // Process each row (similar to original)
406 foreach ($worksheet->getRowIterator() as $rowIndex => $row) {
407 // Extract data from row (same as original)
408
409 // Different email check - only check employee_table
410 $emailCheckStmt = $conn->prepare("SELECT emp_email FROM employee_table WHERE emp_email = ?");
411 $emailCheckStmt->bind_param("s", $emp_email);
412 // Rest of validation code
413
414 // Different insertion - direct to employee_table instead of approval_table
415 $stmt = $conn->prepare("INSERT INTO employee_table (
416 emp_fname, emp_mname, emp_lname, /* same fields */
417 ) VALUES (/* same values */)");
418
419 // Same binding and execution code
420 }
421
422 $response['status'] = 'success';
423 $response['message'] = 'Employees added directly to system.';
424} catch (Exception $e) {
425 // Error handling
426}
427
428echo json_encode($response);
429```
430
431## Important Considerations
432
4331. **Code Duplication**:
434 - You'll have duplicate code between files - consider extracting common functions to a shared file
435 - Example: `include 'employee_upload_functions.php';`
436
4372. **Database Consistency**:
438 - Ensure both files generate employee IDs consistently
439 - Your `generateCompanyNum()` function already handles this well
440
4413. **User Interface**:
442 - Provide clear instructions in the UI about which upload option to use
443 - Consider adding system-specific validation rules if needed
444
4454. **Future Maintenance**:
446 - If you change field requirements, you'll need to update both files
447 - Documentation is important to remember which file does what
448
449This approach is clean, straightforward, and maintains the independence of both systems while allowing them to work together when needed. It's likely the best solution given your requirement for independent operation of the payroll system.
450
451captione-bot: okay. ill probably just do the separate file solution. the considerations are acceptable. can you generate the solution as a whole code? I will paste it on a different file.
452
453GitHub Copilot: # Payroll Employee Upload Solution
454
455Here's the complete `payroll_upload_masterlist.php` file for direct employee insertion from the payroll system:
456
457```php
458<?php
459// Enable error reporting for debugging
460error_reporting(E_ALL);
461ini_set('display_errors', 1);
462
463require 'vendor/autoload.php';
464use PhpOffice\PhpSpreadsheet\IOFactory;
465use PhpOffice\PhpSpreadsheet\Shared\Date;
466
467include '../db_connection.php';
468
469$response = [
470 'status' => 'error',
471 'message' => '',
472 'details' => []
473];
474
475function generatePassword($length = 8) {
476 return str_pad(rand(0, 99999999), 8, '0', STR_PAD_LEFT);
477}
478
479function generateCompanyNum($conn) {
480 $year = date('Y');
481 $query = "
482 SELECT MAX(emp_company_num) as max_num FROM (
483 SELECT emp_company_num FROM employee_table WHERE emp_company_num LIKE 'EMP-$year-%'
484 UNION ALL
485 SELECT emp_company_num FROM approval_table WHERE emp_company_num LIKE 'EMP-$year-%'
486 ) AS combined";
487
488 $result = $conn->query($query);
489 if ($result) {
490 $row = $result->fetch_assoc();
491 if ($row['max_num']) {
492 $last_num = (int)substr($row['max_num'], -9);
493 $new_num = str_pad($last_num + 1, 9, '0', STR_PAD_LEFT);
494 } else {
495 $new_num = '000000001';
496 }
497 return "EMP-$year-$new_num";
498 }
499 return null;
500}
501
502function nullIfEmpty($value) {
503 return empty($value) ? null : $value;
504}
505
506try {
507 if (!isset($_FILES['file'])) {
508 throw new Exception("No file uploaded.");
509 }
510
511 $file = $_FILES['file']['tmp_name'];
512 if (!file_exists($file)) {
513 throw new Exception("Error: File not found. Please try uploading again.");
514 }
515
516 try {
517 $spreadsheet = IOFactory::load($file);
518 } catch (Exception $e) {
519 throw new Exception("Error loading spreadsheet: " . $e->getMessage());
520 }
521
522 $worksheet = $spreadsheet->getActiveSheet();
523 $insertCount = 0;
524
525 foreach ($worksheet->getRowIterator() as $rowIndex => $row) {
526 if ($rowIndex === 1) continue; // Skip header row
527
528 $rowData = [];
529 foreach ($row->getCellIterator() as $cell) {
530 $rowData[] = $cell->getValue();
531 }
532
533 if (count($rowData) < 38) {
534 error_log("Row $rowIndex has insufficient data fields.");
535 continue;
536 }
537
538 list($emp_fname, $emp_mname, $emp_lname, $emp_dob, $emp_gender, $emp_nationality,
539 $emp_marital_status, $emp_position, $emp_company_num, $emp_email, $emp_number,
540 $emp_zip, $unique_manager, $unique_department, $sss, $emp_password, $emp_address,
541 $emp_date_hired, $emp_end_date, $emp_type, $emp_salary, $emp_allowance, $emp_status,
542 $pag_ibig, $tin_number, $philhealth, $account_number, $emp_emergency_name,
543 $emp_emergency_number, $emp_emergency_relationship, $payroll_type, $date_regularized,
544 $previous_employer_name, $previous_tin_number, $previous_registered_address,
545 $previous_zip_code, $prev_taxable_income, $prev_tax_withheld, $other_id
546 ) = $rowData;
547
548 // Log raw values
549 error_log("Row $rowIndex - Raw Data: emp_fname=$emp_fname, emp_lname=$emp_lname, emp_dob=$emp_dob, emp_date_hired=$emp_date_hired, emp_end_date=$emp_end_date");
550
551 // Check if email already exists only in employee_table (skip approval_table check)
552 $emailCheckStmt = $conn->prepare("SELECT emp_email FROM employee_table WHERE emp_email = ?");
553 $emailCheckStmt->bind_param("s", $emp_email);
554 $emailCheckStmt->execute();
555 $emailCheckStmt->store_result();
556
557 if ($emailCheckStmt->num_rows > 0) {
558 $response['details'][] = "Skipping Row $rowIndex: Email $emp_email already exists.";
559 $emailCheckStmt->close();
560 continue;
561 }
562 $emailCheckStmt->close();
563
564 // Convert empty values to NULL
565 $emp_mname = nullIfEmpty($emp_mname);
566 $emp_end_date = nullIfEmpty($emp_end_date);
567 $pag_ibig = nullIfEmpty($pag_ibig);
568 $philhealth = nullIfEmpty($philhealth);
569 $tin_number = nullIfEmpty($tin_number);
570 $account_number = nullIfEmpty($account_number);
571 $emp_salary = nullIfEmpty($emp_salary);
572 $emp_allowance = nullIfEmpty($emp_allowance);
573 $emp_status = nullIfEmpty($emp_status);
574 $emp_type = nullIfEmpty($emp_type);
575
576 // Handle date conversions
577 if (!function_exists('convertDate')) {
578 function convertDate($date, $rowIndex, $fieldName) {
579 if (!empty($date)) {
580 if (is_numeric($date)) {
581 return Date::excelToDateTimeObject($date)->format('Y-m-d');
582 } else {
583 $timestamp = strtotime($date);
584 if ($timestamp !== false) {
585 return date("Y-m-d", $timestamp);
586 } else {
587 error_log("Invalid date format at Row $rowIndex: $fieldName=" . var_export($date, true));
588 return date("Y-m-d");
589 }
590 }
591 }
592 return null;
593 }
594 }
595
596 $emp_dob = convertDate($emp_dob, $rowIndex, 'emp_dob');
597 $emp_date_hired = convertDate($emp_date_hired, $rowIndex, 'emp_date_hired') ?? date("Y-m-d");
598 $emp_end_date = convertDate($emp_end_date, $rowIndex, 'emp_end_date');
599 $date_regularized = convertDate($date_regularized, $rowIndex, 'date_regularized');
600
601 if (empty($emp_company_num)) {
602 $emp_company_num = generateCompanyNum($conn);
603 }
604 if (empty($emp_password)) {
605 $emp_password = generatePassword();
606 }
607
608 // Insert directly into employee_table instead of approval_table
609 $stmt = $conn->prepare("INSERT INTO employee_table (
610 emp_fname, emp_mname, emp_lname, emp_dob, emp_gender, emp_nationality, emp_marital_status,
611 emp_position, emp_company_num, emp_email, emp_number, emp_zip, employ_manager, employ_dept, sss,
612 emp_password, emp_address, emp_date_hired, emp_end_date, emp_type, emp_salary, emp_allowance, emp_status,
613 pag_ibig, tin_number, philhealth, account_number, emp_emergency_name, emp_emergency_number, emp_emergency_relationship,
614 payroll_type, date_regularized, previous_employer_name, previous_tin_number, previous_registered_address,
615 previous_zip_code, prev_taxable_income, prev_tax_withheld, other_id
616 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
617
618 if (!$stmt) {
619 throw new Exception("Error preparing statement: " . $conn->error);
620 }
621
622 $stmt->bind_param(
623 "sssssssisssiiissssssddssssssssssssssdds",
624 $emp_fname, $emp_mname, $emp_lname, $emp_dob, $emp_gender, $emp_nationality, $emp_marital_status,
625 $emp_position,
626 $emp_company_num, $emp_email, $emp_number,
627 $emp_zip, $unique_manager, $unique_department,
628 $sss, $emp_password, $emp_address, $emp_date_hired, $emp_end_date, $emp_type,
629 $emp_salary, $emp_allowance,
630 $emp_status,
631 $pag_ibig, $tin_number, $philhealth, $account_number, $emp_emergency_name, $emp_emergency_number, $emp_emergency_relationship, $payroll_type, $date_regularized, $previous_employer_name, $previous_tin_number, $previous_registered_address,
632 $previous_zip_code, $prev_taxable_income, $prev_tax_withheld,
633 $other_id
634 );
635
636 try {
637 $stmt->execute();
638 $insertCount++;
639 $response['details'][] = "Row $rowIndex: Employee $emp_fname $emp_lname added successfully.";
640 } catch (Exception $e) {
641 $response['details'][] = "Error on Row $rowIndex: " . $e->getMessage();
642 }
643
644 $stmt->close();
645 }
646
647 $response['status'] = 'success';
648 $response['message'] = "Upload complete. $insertCount employees added directly to system.";
649} catch (Exception $e) {
650 $response['message'] = $e->getMessage();
651}
652
653echo json_encode($response);
654$conn->close();
655exit;
656```
657
658## Key Differences from Original File
659
6601. **Modified Email Check**: Only checks `employee_table` (skips approval_table)
661 ```php
662 $emailCheckStmt = $conn->prepare("SELECT emp_email FROM employee_table WHERE emp_email = ?");
663 ```
664
6652. **Direct Insertion**: Inserts directly into `employee_table` instead of `approval_table`
666 ```php
667 $stmt = $conn->prepare("INSERT INTO employee_table (...) VALUES (...)");
668 ```
669
6703. **Improved Feedback**: Reports successful insertions and keeps track of count
671 ```php
672 $insertCount++;
673 $response['details'][] = "Row $rowIndex: Employee $emp_fname $emp_lname added successfully.";
674 ```
675
6764. **Better Error Handling**: Wraps execution in try-catch to continue processing after errors
677 ```php
678 try {
679 $stmt->execute();
680 // Success handling
681 } catch (Exception $e) {
682 $response['details'][] = "Error on Row $rowIndex: " . $e->getMessage();
683 }
684 ```
685
686This implementation will directly add employees to your system, bypassing the approval process, while maintaining all the same validation logic and data formatting from your original script.
687
688Similar code found with 2 license types
689
690captione-bot: great. now, the upload_masterlist.php is a button within a main page, the add_employee.php which is also used by both independent and integrated hris + payroll applications. is there a flag we can use to condition what upload_masterlist code it should use?
691
692GitHub Copilot: # Using a Flag to Condition Upload Masterlist Behavior
693
694Yes, you can implement a flag in your upload process to determine which version of the code to use based on the context (independent payroll vs integrated HRIS+payroll). Here are two approaches:
695
696## Option 1: Query Parameter in Upload Button
697
698Modify your "Upload Masterlist" button to include a parameter that identifies the source:
699
700```javascript
701function showUploadModal() {
702 // Get the current system mode
703 const isPayrollOnly = <?php echo $_SESSION['access'] === 'Payroll' && !isset($_SESSION['integrated_mode']) ? 'true' : 'false'; ?>;
704
705 Swal.fire({
706 title: 'Upload Masterlist',
707 text: 'Please select the masterlist file to upload:',
708 input: 'file',
709 inputAttributes: {
710 'accept': '.xlsx, .xls' // Accept Excel files
711 },
712 showCancelButton: true,
713 confirmButtonText: 'Upload',
714 cancelButtonText: 'Cancel',
715 preConfirm: (file) => {
716 if (!file) {
717 Swal.showValidationMessage('Please select a file to upload');
718 }
719 return file;
720 }
721 }).then((result) => {
722 if (result.isConfirmed) {
723 uploadFile(result.value, isPayrollOnly);
724 }
725 });
726}
727
728function uploadFile(file, isPayrollOnly) {
729 const formData = new FormData();
730 formData.append('file', file);
731 formData.append('mode', isPayrollOnly ? 'payroll_only' : 'integrated');
732
733 $.ajax({
734 url: 'upload_masterlist.php',
735 type: 'POST',
736 data: formData,
737 processData: false,
738 contentType: false,
739 success: function(response) {
740 // Your existing success handler
741 },
742 error: function(xhr, status, error) {
743 // Your existing error handler
744 }
745 });
746}
747```
748
749Then in your upload_masterlist.php, check for this parameter:
750
751```php
752// At the beginning of upload_masterlist.php
753$mode = isset($_POST['mode']) ? $_POST['mode'] : 'integrated';
754$isPayrollOnly = ($mode === 'payroll_only');
755
756// Then later in your code, when inserting:
757if ($isPayrollOnly) {
758 // Direct insertion to employee_table for independent payroll
759 $stmt = $conn->prepare("INSERT INTO employee_table (/* fields */) VALUES (/* values */)");
760} else {
761 // Original code path - insert into approval_table for HRIS workflow
762 $stmt = $conn->prepare("INSERT INTO approval_table (/* fields */) VALUES (/* values */)");
763}
764```
765
766## Option 2: Configuration-Based Approach
767
768Create a configuration setting in your application that determines the behavior:
769
770```php
771// In your upload_masterlist.php
772include '../db_connection.php';
773include '../config.php'; // Include your configuration file
774
775// Check the configuration or session to determine mode
776$isPayrollOnly = false;
777
778// Option 1: Check from configuration
779if (isset($config['system_mode']) && $config['system_mode'] === 'payroll_only') {
780 $isPayrollOnly = true;
781}
782
783// Option 2: Check from session
784if (isset($_SESSION['access']) && $_SESSION['access'] === 'Payroll' && !isset($_SESSION['integrated_mode'])) {
785 $isPayrollOnly = true;
786}
787
788// Later in your code:
789if ($isPayrollOnly) {
790 // Insert directly to employee_table
791 $targetTable = "employee_table";
792} else {
793 // Insert to approval_table
794 $targetTable = "approval_table";
795}
796
797// Use dynamic table name in your INSERT statement
798$stmt = $conn->prepare("INSERT INTO $targetTable (/* fields */) VALUES (/* values */)");
799```
800
801## Implementation for Your Specific Case
802
803Since you want to keep the code cleaner and avoid extensive conditional logic, here's how I'd implement it in your specific scenario:
804
805```php
806// At the beginning of upload_masterlist.php
807$isPayrollOnly = false;
808
809// Determine if we're in payroll-only mode
810if (isset($_POST['system_mode']) && $_POST['system_mode'] === 'payroll_only') {
811 $isPayrollOnly = true;
812} else if (isset($_SESSION['access']) && $_SESSION['access'] === 'Payroll' && !isset($_SESSION['integrated_mode'])) {
813 $isPayrollOnly = true;
814}
815
816// Then, when executing the insertion:
817if ($isPayrollOnly) {
818 // Direct insertion to employee_table
819 $stmt = $conn->prepare("INSERT INTO employee_table (
820 emp_fname, emp_mname, emp_lname, /* ...other fields... */
821 ) VALUES (?, ?, ?, /* ...other placeholders... */)");
822
823 // Same binding as before
824 $stmt->bind_param("sssssssisssiiissssssddssssssssssssssdds", /* ...your parameters... */);
825
826 // Execute and provide appropriate feedback
827 if ($stmt->execute()) {
828 $response['details'][] = "Row $rowIndex: Employee $emp_fname $emp_lname added directly to system.";
829 }
830} else {
831 // Original behavior - insert into approval_table
832 $stmt = $conn->prepare("INSERT INTO approval_table (
833 emp_fname, emp_mname, emp_lname, /* ...other fields... */
834 ) VALUES (?, ?, ?, /* ...other placeholders... */)");
835
836 // Same binding as before
837 $stmt->bind_param("sssssssisssiiissssssddssssssssssssssdds", /* ...your parameters... */);
838
839 // Execute and provide appropriate feedback
840 if ($stmt->execute()) {
841 $response['details'][] = "Row $rowIndex: Employee $emp_fname $emp_lname submitted for approval.";
842 }
843}
844```
845
846This approach lets you use the same file but conditionally change the behavior based on the mode, which is cleaner than having separate files with duplicated code.