Root Explorer PHP

Editing: add-companies.php

<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);

session_start();

if (!isset($_SESSION['admin_logged_in']) || $_SESSION['admin_logged_in'] !== true) {
    header("Location: index.php");
    exit;
}

require_once __DIR__ . "/db-connection.php";

$requiredColumns = [
    'category' => 'Category',
    'company_na' => 'COMPANY_NA',
    'name' => 'NAME',
    'region' => 'REGION',
    'address_1' => 'Address 1',
    'address_2' => 'Address 2',
    'state' => 'State',
    'phone' => 'PHONE',
    'e_mail' => 'E-MAIL',
];

$inserted = [];
$skipped = [];
$errors = [];
$credentialExportRows = [];

function normalizeHeader($value)
{
    $value = strtolower(trim((string) $value));
    $value = str_replace(['-', ' '], '_', $value);
    return preg_replace('/[^a-z0-9_]/', '', $value);
}

function firstEmail($value)
{
    $emails = preg_split('/[,;]+/', (string) $value);
    return strtolower(trim($emails[0]));
}

function generatePassword($length = 12)
{
    $lowercase = 'abcdefghjkmnpqrstuvwxyz';
    $uppercase = 'ABCDEFGHJKMNPQRSTUVWXYZ';
    $numbers = '23456789';
    $symbols = '@#$%';
    $allCharacters = $lowercase . $uppercase . $numbers . $symbols;

$password = [
        $lowercase[random_int(0, strlen($lowercase) - 1)],
        $uppercase[random_int(0, strlen($uppercase) - 1)],
        $numbers[random_int(0, strlen($numbers) - 1)],
        $symbols[random_int(0, strlen($symbols) - 1)],
    ];

for ($i = count($password); $i < $length; $i++) {
        $password[] = $allCharacters[random_int(0, strlen($allCharacters) - 1)];
    }

shuffle($password);

return implode('', $password);
}

function cellReferenceToIndex($reference)
{
    preg_match('/[A-Z]+/', $reference, $matches);
    $letters = $matches[0];
    $index = 0;

for ($i = 0; $i < strlen($letters); $i++) {
        $index = ($index * 26) + (ord($letters[$i]) - 64);
    }

return $index - 1;
}

function readXlsxRows($filePath)
{
    if (!class_exists('ZipArchive')) {
        throw new Exception('XLSX upload requires the PHP ZipArchive extension.');
    }

$zip = new ZipArchive();

if ($zip->open($filePath) !== true) {
        throw new Exception('Unable to open XLSX file.');
    }

$sharedStrings = [];
    $sharedXml = $zip->getFromName('xl/sharedStrings.xml');

if ($sharedXml !== false) {
        $shared = simplexml_load_string($sharedXml);

foreach ($shared->si as $item) {
            $text = '';

if (isset($item->t)) {
                $text = (string) $item->t;
            } elseif (isset($item->r)) {
                foreach ($item->r as $run) {
                    $text .= (string) $run->t;
                }
            }

$sharedStrings[] = $text;
        }
    }

$sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    $zip->close();

if ($sheetXml === false) {
        throw new Exception('Unable to read the first worksheet.');
    }

$sheet = simplexml_load_string($sheetXml);
    $rows = [];

foreach ($sheet->sheetData->row as $row) {
        $rowData = [];

foreach ($row->c as $cell) {
            $attributes = $cell->attributes();
            $cellIndex = cellReferenceToIndex((string) $attributes['r']);
            $type = isset($attributes['t']) ? (string) $attributes['t'] : '';
            $value = isset($cell->v) ? (string) $cell->v : '';

if ($type === 's' && $value !== '') {
                $value = $sharedStrings[(int) $value] ?? '';
            } elseif ($type === 'inlineStr' && isset($cell->is->t)) {
                $value = (string) $cell->is->t;
            }

$rowData[$cellIndex] = trim($value);
        }

if (!empty(array_filter($rowData, 'strlen'))) {
            ksort($rowData);
            $rows[] = $rowData;
        }
    }

return $rows;
}

function readCsvRows($filePath)
{
    $handle = fopen($filePath, 'r');

if (!$handle) {
        throw new Exception('Unable to open CSV file.');
    }

$rows = [];

while (($row = fgetcsv($handle)) !== false) {
        if (!empty(array_filter($row, 'strlen'))) {
            $rows[] = array_map('trim', $row);
        }
    }

fclose($handle);
    return $rows;
}

function readUploadedRows($filePath, $fileName)
{
    $extension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

if ($extension === 'xlsx') {
        return readXlsxRows($filePath);
    }

if ($extension === 'csv') {
        return readCsvRows($filePath);
    }

throw new Exception('Only .xlsx and .csv files are supported.');
}

function html($value)
{
    return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}

function jsonForHtml($value)
{
    return json_encode($value, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT);
}

function findMailerGroupId($mailerDb, $category)
{
    $stmt = mysqli_prepare($mailerDb, "SELECT id FROM `groups` WHERE LOWER(TRIM(name)) = LOWER(TRIM(?)) LIMIT 1");

if (!$stmt) {
        throw new Exception('Unable to prepare group lookup: ' . mysqli_error($mailerDb));
    }

mysqli_stmt_bind_param($stmt, 's', $category);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $groupId);

if (!mysqli_stmt_fetch($stmt)) {
        mysqli_stmt_close($stmt);
        return null;
    }

mysqli_stmt_close($stmt);
    return (int) $groupId;
}

function createMailerUser($mailerDb, $email, $groupId)
{
    $checkStmt = mysqli_prepare($mailerDb, "SELECT id FROM users WHERE email = ? LIMIT 1");

if (!$checkStmt) {
        throw new Exception('Unable to prepare mailer user lookup: ' . mysqli_error($mailerDb));
    }

mysqli_stmt_bind_param($checkStmt, 's', $email);
    mysqli_stmt_execute($checkStmt);
    mysqli_stmt_bind_result($checkStmt, $userId);

if (mysqli_stmt_fetch($checkStmt)) {
        mysqli_stmt_close($checkStmt);
    } else {
        mysqli_stmt_close($checkStmt);

$insertUserStmt = mysqli_prepare($mailerDb, "INSERT INTO users (email) VALUES (?)");

if (!$insertUserStmt) {
            throw new Exception('Unable to prepare mailer user insert: ' . mysqli_error($mailerDb));
        }

mysqli_stmt_bind_param($insertUserStmt, 's', $email);

if (!mysqli_stmt_execute($insertUserStmt)) {
            $error = mysqli_stmt_error($insertUserStmt);
            mysqli_stmt_close($insertUserStmt);
            throw new Exception('Unable to create mailer user: ' . $error);
        }

$userId = mysqli_insert_id($mailerDb);
        mysqli_stmt_close($insertUserStmt);
    }

$linkCheckStmt = mysqli_prepare(
        $mailerDb,
        "SELECT id FROM group_user WHERE user_id = ? AND group_id = ? LIMIT 1"
    );

if (!$linkCheckStmt) {
        throw new Exception('Unable to prepare group link lookup: ' . mysqli_error($mailerDb));
    }

mysqli_stmt_bind_param($linkCheckStmt, 'ii', $userId, $groupId);
    mysqli_stmt_execute($linkCheckStmt);
    mysqli_stmt_store_result($linkCheckStmt);
    $linkExists = mysqli_stmt_num_rows($linkCheckStmt) > 0;
    mysqli_stmt_free_result($linkCheckStmt);
    mysqli_stmt_close($linkCheckStmt);

if (!$linkExists) {
        $linkStmt = mysqli_prepare($mailerDb, "INSERT INTO group_user (user_id, group_id) VALUES (?, ?)");

if (!$linkStmt) {
            throw new Exception('Unable to prepare group link insert: ' . mysqli_error($mailerDb));
        }

mysqli_stmt_bind_param($linkStmt, 'ii', $userId, $groupId);

if (!mysqli_stmt_execute($linkStmt)) {
            $error = mysqli_stmt_error($linkStmt);
            mysqli_stmt_close($linkStmt);
            throw new Exception('Unable to create group link: ' . $error);
        }

mysqli_stmt_close($linkStmt);
    }

return $userId;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        if (!isset($_FILES['company_sheet']) || $_FILES['company_sheet']['error'] !== UPLOAD_ERR_OK) {
            throw new Exception('Please select a valid Excel or CSV file.');
        }

$rows = readUploadedRows(
            $_FILES['company_sheet']['tmp_name'],
            $_FILES['company_sheet']['name']
        );

if (count($rows) < 2) {
            throw new Exception('The sheet must contain one header row and at least one company row.');
        }

$headerMap = [];

foreach ($rows[0] as $index => $heading) {
            $headerMap[normalizeHeader($heading)] = $index;
        }

foreach ($requiredColumns as $key => $label) {
            if (!isset($headerMap[$key])) {
                throw new Exception("Missing required column: {$label}");
            }
        }

$maxIdResult = mysqli_query($mainDb, "SELECT COALESCE(MAX(id), 0) + 1 AS next_id FROM memberlogin");
        $nextIdRow = mysqli_fetch_assoc($maxIdResult);
        $nextId = (int) $nextIdRow['next_id'];

$insertSql = "
            INSERT INTO memberlogin
            (
                id, name, email, company, region, userid, password, phone, website,
                mainaddress, trademark, acc_status, mem_type, pay_due_date,
                last_pay_date, pay_outstanding, profile_photo, fcm_id, device_id,
                device_name, otp, otp_created_at
            )
            VALUES
            (?, ?, ?, ?, ?, ?, ?, ?, '', ?, '', 1, ?, '', '', '', '', '', '', '', NULL, NULL)
        ";

$insertStmt = mysqli_prepare($mainDb, $insertSql);
        $checkStmt = mysqli_prepare($mainDb, "SELECT id FROM memberlogin WHERE email = ? LIMIT 1");
        $deleteInsertedStmt = mysqli_prepare($mainDb, "DELETE FROM memberlogin WHERE id = ? LIMIT 1");

if (!$insertStmt || !$checkStmt || !$deleteInsertedStmt) {
            throw new Exception('Unable to prepare database queries: ' . mysqli_error($mainDb));
        }

for ($i = 1; $i < count($rows); $i++) {
            $row = $rows[$i];
            $sheetRowNumber = $i + 1;

$category = trim($row[$headerMap['category']] ?? '');
            $company = trim($row[$headerMap['company_na']] ?? '');
            $name = trim($row[$headerMap['name']] ?? '');
            $region = trim($row[$headerMap['region']] ?? '');
            $address1 = trim($row[$headerMap['address_1']] ?? '');
            $address2 = trim($row[$headerMap['address_2']] ?? '');
            $state = trim($row[$headerMap['state']] ?? '');
            $phone = trim($row[$headerMap['phone']] ?? '');
            $email = firstEmail($row[$headerMap['e_mail']] ?? '');

if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $skipped[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'reason' => 'Invalid or missing email',
                ];
                continue;
            }

if ($category === '') {
                $skipped[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'reason' => 'Missing category',
                ];
                continue;
            }

try {
                $groupId = findMailerGroupId($mailerDb, $category);
            } catch (Exception $exception) {
                $errors[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'error' => $exception->getMessage(),
                ];
                continue;
            }

if ($groupId === null) {
                $skipped[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'reason' => "Category group not found: {$category}",
                ];
                continue;
            }

mysqli_stmt_bind_param($checkStmt, 's', $email);
            if (!mysqli_stmt_execute($checkStmt)) {
                $errors[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'error' => mysqli_stmt_error($checkStmt),
                ];
                continue;
            }

mysqli_stmt_store_result($checkStmt);
            $alreadyExists = mysqli_stmt_num_rows($checkStmt) > 0;
            mysqli_stmt_free_result($checkStmt);

if ($alreadyExists) {
                $skipped[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'reason' => 'Email already exists',
                ];
                continue;
            }

$userid = $email;
            $password = generatePassword();
            $passwordHashed = password_hash($password, PASSWORD_DEFAULT);
            $mainAddress = trim(implode(', ', array_filter([$address1, $address2, $state], 'strlen')));

mysqli_stmt_bind_param(
                $insertStmt,
                'isssssssss',
                $nextId,
                $name,
                $email,
                $company,
                $region,
                $userid,
                $passwordHashed,
                $phone,
                $mainAddress,
                $category
            );

if (mysqli_stmt_execute($insertStmt)) {
                try {
                    $mailerUserId = createMailerUser($mailerDb, $email, $groupId);
                } catch (Exception $exception) {
                    mysqli_stmt_bind_param($deleteInsertedStmt, 'i', $nextId);
                    mysqli_stmt_execute($deleteInsertedStmt);

$errors[] = [
                        'row' => $sheetRowNumber,
                        'company' => $company,
                        'error' => $exception->getMessage(),
                    ];
                    $nextId++;
                    continue;
                }

$inserted[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'email' => $email,
                    'userid' => $userid,
                    'password' => $password,
                    'category' => $category,
                    'group_id' => $groupId,
                    'mailer_user_id' => $mailerUserId,
                ];
                $credentialExportRows[] = [
                    'company' => $company,
                    'email' => $email,
                    'password' => $password,
                ];
                $nextId++;
            } else {
                $errors[] = [
                    'row' => $sheetRowNumber,
                    'company' => $company,
                    'error' => mysqli_stmt_error($insertStmt),
                ];
            }
        }
    } catch (Exception $exception) {
        $errors[] = [
            'row' => '-',
            'company' => '-',
            'error' => $exception->getMessage(),
        ];
    }
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Add Companies</title>

.container{
            max-width:1200px;
            margin:auto;
            background:#fff;
            padding:30px;
            border-radius:10px;
        }

.upload-box{
            border:1px solid #ddd;
            padding:20px;
            margin-top:20px;
            border-radius:8px;
            background:#fafafa;
        }

input[type="file"]{
            display:block;
            margin-top:10px;
        }

button{
            padding:12px 20px;
            background:#198754;
            color:#fff;
            border:none;
            margin-top:20px;
            cursor:pointer;
        }

table{
            width:100%;
            border-collapse: collapse;
            margin-top:20px;
        }

table th,
        table td{
            border:1px solid #ddd;
            padding:10px;
            text-align:left;
            font-size:14px;
        }

.success{
            background:#d4edda;
        }

.danger{
            background:#f8d7da;
        }

.warning{
            background:#fff3cd;
        }

.columns{
            margin-top:10px;
            color:#555;
            line-height:1.8;
        }

.export-button{
            background:#0d6efd;
            margin-left:10px;
        }

.credential{
            line-height:1.6;
        }
    </style>
</head>
<body>

<h2>Add Companies</h2>

<div class="upload-box">
        <form method="POST" enctype="multipart/form-data">
            <label>
                Select Excel Sheet
            </label>

<div class="columns">
                Required first row:
                <?php echo html(implode(', ', array_values($requiredColumns))); ?>
            </div>

<button type="submit">
                Import Companies
            </button>
        </form>
    </div>

<?php if ($_SERVER['REQUEST_METHOD'] === 'POST') { ?>
        <hr>

<h3>Import Report</h3>

<p>
            Inserted:
            <b><?php echo count($inserted); ?></b>
        </p>

<p>
            Skipped:
            <b><?php echo count($skipped); ?></b>
        </p>

<p>
            Errors:
            <b><?php echo count($errors); ?></b>
        </p>

<?php if (!empty($inserted)) { ?>
            <button
                type="button"
                class="export-button"
                onclick="exportCredentials()"
            >
                Export Login Credentials
            </button>

<table class="success">
                <tr>
                    <th>Row</th>
                    <th>Company</th>
                    <th>Login Credentials</th>
                    <th>Category</th>
                    <th>Group ID</th>
                    <th>Mailer User ID</th>
                    <th>Status</th>
                </tr>

<?php foreach ($inserted as $item) { ?>
                    <tr>
                        <td><?php echo html($item['row']); ?></td>
                        <td><?php echo html($item['company']); ?></td>
                        <td class="credential">
                            <b>Email:</b> <?php echo html($item['email']); ?><br>
                            <b>Password:</b> <?php echo html($item['password']); ?>
                        </td>
                        <td><?php echo html($item['category']); ?></td>
                        <td><?php echo html($item['group_id']); ?></td>
                        <td><?php echo html($item['mailer_user_id']); ?></td>
                        <td>Inserted</td>
                    </tr>
                <?php } ?>
            </table>
        <?php } ?>

<?php if (!empty($skipped)) { ?>
            <table class="warning">
                <tr>
                    <th>Row</th>
                    <th>Company</th>
                    <th>Reason</th>
                </tr>

<?php foreach ($skipped as $item) { ?>
                    <tr>
                        <td><?php echo html($item['row']); ?></td>
                        <td><?php echo html($item['company']); ?></td>
                        <td><?php echo html($item['reason']); ?></td>
                    </tr>
                <?php } ?>
            </table>
        <?php } ?>

<?php if (!empty($errors)) { ?>
            <table class="danger">
                <tr>
                    <th>Row</th>
                    <th>Company</th>
                    <th>Error</th>
                </tr>

<?php foreach ($errors as $item) { ?>
                    <tr>
                        <td><?php echo html($item['row']); ?></td>
                        <td><?php echo html($item['company']); ?></td>
                        <td><?php echo html($item['error']); ?></td>
                    </tr>
                <?php } ?>
            </table>
        <?php } ?>
    <?php } ?>
</div>

function csvValue(value) {
    return '"' + String(value ?? '').replace(/"/g, '""') + '"';
}

function exportCredentials() {
    const rows = [
        ['Company Name', 'Email', 'Password'],
        ...credentialExportRows.map((item) => [
            item.company,
            item.email,
            item.password
        ])
    ];

const csv = rows
        .map((row) => row.map(csvValue).join(','))
        .join('\n');

const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
    const url = URL.createObjectURL(blob);
    const link = document.createElement('a');

link.href = url;
    link.download = 'company-login-credentials.csv';
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    URL.revokeObjectURL(url);
}
</script>

</body>
</html>

[ CANCEL ]

Name	Type	Actions
.. (Parent Directory)
📄 add-companies.php	FILE	[EDIT]
📄 db-connection.php	FILE	[EDIT]
📄 delete-companies.php	FILE	[EDIT]
📄 developer-tools.php	FILE	[EDIT]
📁 files/	DIR
📄 header.php	FILE	[EDIT]
📄 index.php	FILE	[EDIT]

[ Root System Explorer ]

Editing: add-companies.php