﻿﻿﻿<?php
// Include the header and database connection
include 'header.php';
include_once 'config/database.php';
error_reporting(E_ALL);          
ini_set('display_errors', 1); 

// Create a new database connection
$database = new Database();
$db = $database->getConnection();

// Check if the form is submitted
if (isset($_POST['submit'])) {

    // Check if a file is uploaded
    if (isset($_FILES['file']) && $_FILES['file']['error'] == 0) {
        $fileName = $_FILES['file']['tmp_name'];
        $fileType = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);

        // Check if the uploaded file is a CSV
        if ($fileType !== 'csv') {
            echo "Please upload a CSV file.";
            exit;
        }

        // Open the CSV file
        if (($file = fopen($fileName, "r")) !== FALSE) {
            // Skip the header row
            fgetcsv($file);

            $successCount = 0;
            $errorCount = 0;
			$errorMessages = [];  // Array to hold error messages

            // Loop through each row
            while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
                // Skip empty rows (where all columns are empty or contain only whitespaces)
                if (empty(array_filter($column, fn($value) => !empty(trim($value))))) {
                    continue;  // Skip this iteration and move to the next row
                }
				
                $empadharnumber = !empty($column[2]) ? htmlspecialchars(strip_tags($column[2])) : null;
				$empname = !empty($column[3]) ? htmlspecialchars(strip_tags($column[3])) : null;
				// Check if the Aadhaar number already exists in acts_emp
                $check_emp_query = "SELECT COUNT(*) FROM acts_emp WHERE empadharnumber = :empadharnumber";
                $check_emp_stmt = $db->prepare($check_emp_query);
                $check_emp_stmt->bindParam(':empadharnumber', $empadharnumber);
                $check_emp_stmt->execute();
                $exists_in_emp = $check_emp_stmt->fetchColumn();

                // If the Aadhaar number exists in acts_emp, skip this record
                if ($exists_in_emp > 0) {
                    $errorCount++;
                    $errorMessages[] = "\n Aadhaar number $empadharnumber already exists for employee $empname. Skipping this row.";
                    continue;  // Skip to the next iteration (row)
                }

                // Process the row
                $empbranchcode = !empty($column[0]) ? htmlspecialchars(strip_tags($column[0])) : null;
                $loname = !empty($column[1]) ? htmlspecialchars(strip_tags($column[1])) : null;
                $empfathername = !empty($column[4]) ? htmlspecialchars(strip_tags($column[4])) : null;
                $empdob = !empty($column[5]) ? htmlspecialchars(strip_tags($column[5])) : null;

                // Normalize and validate the empdob (Date of Birth)
                if ($empdob) {
                    $parts = explode('-', $empdob);
                    if (count($parts) == 3) {
                        $day = str_pad($parts[0], 2, '0', STR_PAD_LEFT); 
                        $month = str_pad($parts[1], 2, '0', STR_PAD_LEFT); 
                        $year = $parts[2];

                        // Rebuild the normalized date string
                        $normalizedDate = $day . '-' . $month . '-' . $year;
                    } else {
                        $normalizedDate = null;
                    }

                    if ($normalizedDate) {
                        $date = DateTime::createFromFormat('d-m-Y', $normalizedDate);
                        if ($date === false) {
                            echo "Failed to parse date with format d-m-Y: $normalizedDate<br>";
                            $empdob = null;
                        } else {
                            $empdob = $date->format('Y-m-d');
                        }
                    } else {
                        $empdob = null;
                    }
                } else {
                    $empdob = null;
                }

                // Remaining fields
                $empqualification = !empty($column[6]) ? htmlspecialchars(strip_tags($column[6])) : null;
                $empemail = !empty($column[7]) ? htmlspecialchars(strip_tags($column[7])) : null;
                $empphone = !empty($column[8]) ? htmlspecialchars(strip_tags($column[8])) : null;
                $empaddress = !empty($column[9]) ? htmlspecialchars(strip_tags($column[9])) : null;
                $dgname = !empty($column[10]) ? htmlspecialchars(strip_tags($column[10])) : null;
                $empbg = !empty($column[11]) ? htmlspecialchars(strip_tags($column[11])) : null;
                $emppic = 'default-picture.png';
                $empstatus = 'active';
                $emppass = !empty($column[14]) ? htmlspecialchars(strip_tags($column[14])) : null;
                $doc_adhar = !empty($column[15]) ? htmlspecialchars(strip_tags($column[15])) : null;
                $doc_polveri = !empty($column[16]) ? htmlspecialchars(strip_tags($column[16])) : null;
                $doc_other = !empty($column[17]) ? htmlspecialchars(strip_tags($column[17])) : null;

                // Check if EPF or ESI No. already exists in acts_emp_bank
                $epfno = !empty($column[23]) ? htmlspecialchars(strip_tags($column[23])) : null;
                $esino = !empty($column[24]) ? htmlspecialchars(strip_tags($column[24])) : null;

                $check_query = "SELECT COUNT(*) FROM acts_emp_bank WHERE epfno = :epfno OR esino = :esino";
                $check_stmt = $db->prepare($check_query);
                $check_stmt->bindParam(':epfno', $epfno);
                $check_stmt->bindParam(':esino', $esino);
                $check_stmt->execute();
                $exists = $check_stmt->fetchColumn();

                // Skip if either EPF or ESI No. exists
                if ($exists > 0) {
                    $errorCount++;
                    $errorMessages[] = "\n EPF/ESI No. $epfno / $esino already exists for employee $empname. Skipping this row.";
                    continue;  // Skip to the next iteration (row)
                }

                // Prepare SQL query for inserting into acts_emp
                $query = "INSERT INTO acts_emp (
                    empbranchcode, loname, empadharnumber, empname, empfathername, empdob, empqualification, empemail, 
                    empphone, empaddress, dgname, empbg, emppic, empstatus, emppass, doc_adhar, doc_polveri, doc_other
                ) VALUES (
                    :empbranchcode, :loname, :empadharnumber, :empname, :empfathername, :empdob, :empqualification, :empemail, 
                    :empphone, :empaddress, :dgname, :empbg, :emppic, :empstatus, :emppass, :doc_adhar, :doc_polveri, :doc_other
                )";

                $stmt = $db->prepare($query);

                // Bind parameters for acts_emp table
                $stmt->bindParam(':empbranchcode', $empbranchcode);
                $stmt->bindParam(':loname', $loname);
                $stmt->bindParam(':empadharnumber', $empadharnumber);
                $stmt->bindParam(':empname', $empname);
                $stmt->bindParam(':empfathername', $empfathername);
                $stmt->bindParam(':empdob', $empdob);
                $stmt->bindParam(':empqualification', $empqualification);
                $stmt->bindParam(':empemail', $empemail);
                $stmt->bindParam(':empphone', $empphone);
                $stmt->bindParam(':empaddress', $empaddress);
                $stmt->bindParam(':dgname', $dgname);
                $stmt->bindParam(':empbg', $empbg);
                $stmt->bindParam(':emppic', $emppic);
                $stmt->bindParam(':empstatus', $empstatus);
                $stmt->bindParam(':emppass', $emppass);
                $stmt->bindParam(':doc_adhar', $doc_adhar);
                $stmt->bindParam(':doc_polveri', $doc_polveri);
                $stmt->bindParam(':doc_other', $doc_other);

                // Execute the query for acts_emp
                if ($stmt->execute()) {
                    $empid = $db->lastInsertId();  // Get the last inserted empid

                    // Insert into acts_emp_bank
                    $bankname = !empty($column[18]) ? htmlspecialchars(strip_tags($column[18])) : null;
                    $bankholdername = !empty($column[19]) ? htmlspecialchars(strip_tags($column[19])) : null;
                    $bankaccountnumber = !empty($column[20]) ? htmlspecialchars(strip_tags($column[20])) : null;
                    $bankbranchname = !empty($column[21]) ? htmlspecialchars(strip_tags($column[21])) : null;
                    $bankifsccode = !empty($column[22]) ? htmlspecialchars(strip_tags($column[22])) : null;

                    $bank_query = "INSERT INTO acts_emp_bank (
                        empid, bankname, bankacholdername, bankaccountno, bankbranchname, bankifsccode, epfno, esino
                    ) VALUES (
                        :empid, :bankname, :bankacholdername, :bankaccountno, :bankbranchname, :bankifsccode, :epfno, :esino
                    )";

                    $bank_stmt = $db->prepare($bank_query);
                    $bank_stmt->bindParam(':empid', $empid);
                    $bank_stmt->bindParam(':bankname', $bankname);
                    $bank_stmt->bindParam(':bankacholdername', $bankholdername);
                    $bank_stmt->bindParam(':bankaccountno', $bankaccountnumber);
                    $bank_stmt->bindParam(':bankbranchname', $bankbranchname);
                    $bank_stmt->bindParam(':bankifsccode', $bankifsccode);
                    $bank_stmt->bindParam(':epfno', $epfno);
                    $bank_stmt->bindParam(':esino', $esino);

                    // Execute the query for acts_emp_bank
                    if ($bank_stmt->execute()) {
                        $successCount++;
                    } else {
                        $errorCount++;
                    }
                } else {
                    $errorCount++;
                }
            }

			// Create the error file and save the error message
			$errorFileName = 'error_report_' . time() . '.txt';
			$errorFilePath = __DIR__ ."/". $errorFileName;
			$errorMsgString = urldecode(implode("\n", $errorMessages));
			$errorContent = "Some of your files were not inserted due to duplicates.\n\nError Details:\n" . $errorMsgString;

			// Write the error content to a file
			file_put_contents($errorFilePath, $errorContent);

			// Redirect with the success/error counts and the download link for the error file
			header("Location: dashboard.employee.view.php?status=error&file=$errorFileName&successCount=$successCount&errorCount=$errorCount");
			exit;

            fclose($file);
        } else {
            echo "Unable to open the CSV file.";
        }
    } else {
        echo "No file uploaded or there was an error.";
    }
}
?>