File "print_all_soa.php"

Full Path: /home/leadltht/fastlinkinternet.com/wp-comment-form/reports/print_all_soa.php
File size: 17.29 KB
MIME-type: text/x-php
Charset: utf-8

<?php
require_once('/home/leadltht/fastlinkinternet.com/administrator/data-provider/config.php'); // Ensure the correct path to config.php
$server = '';
$status = '';
$accountFilter = 'all'; // Default account filter
$reportDetails = [];
$totalAmount = 0;
$countYes = 0;
$clientCount = 0;

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['server'])) {
    $server = $_POST['server'];
    $status = isset($_POST['status_filter']) ? $_POST['status_filter'] : '';
    $accountFilter = isset($_POST['account_filter']) ? $_POST['account_filter'] : 'all';

    // Main SQL query to fetch client details and calculate totalAccountBalance
    $sqlReport = "SELECT 
                    ml.id, 
                    CONCAT(ml.firstname, ' ', ml.lastname) AS client_name, 
                    ml.installation_date, 
                    ml.server, 
                    ml.status, 
                    ml.cut_off_day, 
                    ml.installation_fee, 
                    ml.plan_price, 
                    ml.plan_name,  -- Fetching plan_name from database
                    ml.previous_bal, 
                    ml.inactive_date, 
                    ml.for_pd,
                    ml.fb_account, -- Facebook account
                    IFNULL(SUM(cl.total_amount), 0) AS total_collected, -- Total collected payments
                    GROUP_CONCAT(cl.note SEPARATOR '; ') AS note,

                    -- Calculating installation balance
                    (ml.installation_fee - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Installation Fee'), 0)) AS installation_balance,
                    
                    -- Check if inactive and calculate the remaining unpaid monthly fee
                    IF(ml.inactive_date IS NOT NULL, 
                        (ml.plan_price * TIMESTAMPDIFF(MONTH, ml.installation_date, ml.inactive_date)), 
                        (ml.plan_price * TIMESTAMPDIFF(MONTH, ml.installation_date, NOW()))
                    ) - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Net Monthly Fee'), 0) AS remaining_unpaid_amount,
                    
                    -- Remaining previous balance calculation
                    (ml.previous_bal - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Balance From Previous Bills'), 0)) AS remaining_previous_balance,

                    -- Total account balance calculation
                    ((ml.installation_fee - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Installation Fee'), 0))
                    + IF(ml.inactive_date IS NOT NULL, 
                        (ml.plan_price * TIMESTAMPDIFF(MONTH, ml.installation_date, ml.inactive_date)), 
                        (ml.plan_price * TIMESTAMPDIFF(MONTH, ml.installation_date, NOW()))
                    ) - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Net Monthly Fee'), 0)
                    + (ml.previous_bal - IFNULL((SELECT SUM(c.total_amount) FROM collection_list c WHERE c.member_id = ml.id AND c.name_of_payment = 'Balance From Previous Bills'), 0))) AS totalAccountBalance

                  FROM 
                    member_list ml
                  LEFT JOIN 
                    collection_list cl 
                  ON 
                    ml.id = cl.member_id
                  WHERE 
                    ml.server = '$server'";

    // Add status filter if selected
    if ($status !== '') {
        $sqlReport .= " AND ml.status = '$status'";
    }

    // Add grouping
    $sqlReport .= " GROUP BY ml.id";

    // Add account balance filtering
    if ($accountFilter === 'with_balance') {
        $sqlReport .= " HAVING totalAccountBalance > 0";
    }

    // Execute the query
    $resultReport = $conn->query($sqlReport);

    // Process the results if any
    if ($resultReport && $resultReport->num_rows > 0) {
        while ($row = $resultReport->fetch_assoc()) {
            $clientId = $row['id'];
            $clientName = $row['client_name'];
            $installationDate = new DateTime($row['installation_date']);
            $currentDate = new DateTime();

            // Initialize client details array
            $clientDetails = [
                'client_name' => $clientName,
                'installation_date' => $row['installation_date'],
                'server' => $row['server'],
                'status' => $row['status'],
                'cut_off_day' => $row['cut_off_day'],
                'installation_fee' => $row['installation_fee'],
                'plan_price' => $row['plan_price'],
                'plan_name' => $row['plan_name'], // Add plan_name to details
                'previous_bal' => $row['previous_bal'],
                'inactive_date' => $row['inactive_date'],
                'totalInstallationPayments' => 0,
                'totalMonthlyFeePayments' => 0,
                'totalPreviousBalancePayments' => 0,
                'totalAccountBalance' => 0,
                'installationBalance' => $row['installation_balance'],
                'remainingUnpaidAmount' => $row['remaining_unpaid_amount'],
                'remainingPreviousBalance' => $row['remaining_previous_balance'],
                'fb_account' => $row['fb_account'], // Adding Facebook account to client details
                'for_pd' => $row['for_pd'],
                'note' => $row['note'],
                'billingCutOff' => '',
                'fiveDaysBillDueDate' => ''
            ];

            // Calculate billingCutOff to always be in the current month
            $cutOffDay = $clientDetails['cut_off_day'] ?: $installationDate->format('d'); // Fallback to installation day
            $currentMonth = $currentDate->format('m');
            $currentYear = $currentDate->format('Y');

            // Ensure that the billingCutOff is within the current month and year
            $billingCutOff = new DateTime("$currentYear-$currentMonth-$cutOffDay");
            $clientDetails['billingCutOff'] = $billingCutOff->format('Y-m-d');

            // Calculate fiveDaysBillDueDate as 5 days after the billing cut-off
            $fiveDaysBillDueDate = clone $billingCutOff;
            $fiveDaysBillDueDate->modify('+5 days');
            $clientDetails['fiveDaysBillDueDate'] = $fiveDaysBillDueDate->format('Y-m-d');

            // Calculate total account balance
            $clientDetails['totalAccountBalance'] = $clientDetails['installationBalance']
                                                    + $clientDetails['remainingUnpaidAmount']
                                                    + $clientDetails['remainingPreviousBalance'];

            // Only include clients with balance if filtering or all clients if no filtering
            if ($clientDetails['totalAccountBalance'] > 0 || $accountFilter === 'all') {
                $reportDetails[] = $clientDetails;
                $countYes++;
                $totalAmount += $clientDetails['totalAccountBalance'];
            }
        }
    }

    // Count total clients per server
    $sqlClientCount = "SELECT COUNT(*) AS clientCount FROM member_list WHERE server = '$server'";
    if ($status) {
        $sqlClientCount .= " AND status = '$status'";
    }
    $resultClientCount = $conn->query($sqlClientCount);
    if ($resultClientCount) {
        $clientCountRow = $resultClientCount->fetch_assoc();
        $clientCount = $clientCountRow['clientCount'];
    }
}

// Fetch distinct servers for the dropdown
$serverResult = $conn->query("SELECT DISTINCT server FROM member_list ORDER BY server ASC");
$servers = $serverResult->fetch_all(MYSQLI_ASSOC);
?>


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Print All SOA</title>
    <!-- Include CSS and JS files for styling and functionality -->
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    <style>
        .please-pay {
            font-size: 24px;
            font-weight: bold;
            color: red;
            padding: 10px;
            border: 2px solid red;
            display: inline-block;
            margin-top: 0;
            line-height: 1;
        }
        .payment-reminder {
            border: 2px solid blue;
            padding: 15px;
            margin-top: 0;
            background-color: #f9f9f9;
            font-size: 16px;
            font-weight: bold;
            color: #333;
            line-height: 1;
        }
        .company-header {
            text-align: center;
            margin-bottom: 10px;
            line-height: 1;
        }
        @media print {
            .no-print {
                display: none;
            }
            .row {
                page-break-inside: avoid;
                margin-bottom: 0;
            }
            .row:nth-child(odd) {
                page-break-after: always;
            }
        }
        h3.no-spacing {
            margin: 0;
            padding: 0;
        }
    </style>

    <script>
        function prepareForPrint() {
            document.querySelector('.no-print').style.display = 'none';
            window.print();
        }

        function copyStyledClientInfo(clientName, cutoffDate, totalAccountBalance, dueDate) {
            const htmlContent = `
                <div style="border: 2px solid #333; padding: 10px; font-family: Arial, sans-serif; max-width: 300px;">
                    <div style="text-align: center;">
                        <h2>FASTLINK INTERNET - Internet Bill</h2>
                    </div>
                    <p style="font-weight: bold;">${clientName}</p>
                    <p>Bill Cut-off Date: ${cutoffDate}</p>
                    <p style="color: red; font-weight: bold;">Please Pay: ₱${totalAccountBalance}</p>
                    <p>on or before the Due Date: ${dueDate}</p>
                    <hr style="border-top: 1px dashed #ccc;">
                    <p>To avoid disconnection of the service, pay your account balance to Felix I. Albotra Jr.:</p>
                    <p>GCASH# 09453306374</p>
                </div>
            `;

            const tempElement = document.createElement('div');
            tempElement.innerHTML = htmlContent;
            document.body.appendChild(tempElement);

            const range = document.createRange();
            range.selectNode(tempElement);
            window.getSelection().removeAllRanges();
            window.getSelection().addRange(range);

            document.execCommand('copy');
            document.body.removeChild(tempElement);
            window.getSelection().removeAllRanges();

            alert("Client information copied to clipboard");
        }

        function sendPaymentConfirmation(clientName) {
            const confirmationMessage = `
                Dear ${clientName},

                Thank you for your payment! We have successfully received your payment. We are now processing your transaction and will update your account shortly.

                If you have any questions or need further assistance, please feel free to reach out to us.

                Thank you for your continued trust and support.

                Best regards,
                FASTLINK INTERNET
            `;

            const tempElement = document.createElement('textarea');
            tempElement.value = confirmationMessage;
            document.body.appendChild(tempElement);

            tempElement.select();
            document.execCommand('copy');
            document.body.removeChild(tempElement);

            alert("Payment confirmation message copied to clipboard");
        }
    </script>
</head>
<body>
    <div class="container mt-4">
        <div class="no-print">
            <h3>Select Server, Status, and Account Filter</h3>
            <form method="POST" action="">
    <div class="form-group row align-items-center">
        <label for="server" class="col-sm-1 col-form-label">Server:</label>
        <div class="col-sm-2">
            <select name="server" id="server" class="form-control">
                <option value="">Select a server</option>
                <?php foreach ($servers as $server): ?>
                    <option value="<?= htmlspecialchars(strtolower($server['server'])) ?>"><?= htmlspecialchars($server['server']) ?></option>
                <?php endforeach; ?>
            </select>
        </div>

        <label for="status_filter" class="col-sm-1 col-form-label">Status:</label>
        <div class="col-sm-2">
            <select name="status_filter" id="status_filter" class="form-control">
                <option value="">All Statuses</option>
                <option value="active">Active</option>
                <option value="inactive">Inactive</option>
            </select>
        </div>

        <label for="account_filter" class="col-sm-2 col-form-label">Filter Accounts:</label>
        <div class="col-sm-2">
            <select name="account_filter" id="account_filter" class="form-control">
                <option value="all">All Accounts</option>
                <option value="with_balance">Accounts with Balance</option>
            </select>
        </div>

        <!-- Combine the button in the same row with minimal spacing -->
        <div class="col-sm-2">
            <button type="submit" class="btn btn-primary">Filter Accounts</button>
        </div>
    </div>
</form>


        </div>

        <?php if (!empty($reportDetails)): ?>
            <div class="mt-4">
                <h4 class="no-print">Statement of Account</h4>
                <?php foreach ($reportDetails as $client): ?>
                    <div class="row mb-4">
                        <div class="col-md-12 company-header">
                            <img src="https://fastlinkinternet.com/administrator/data-provider/uploads/fastlink.png" alt="Logo" style="max-width: 150px;">
                            <h2>FASTLINK INTERNET - Internet Bill</h2>
                            <h3 class="no-spacing">As of <?= date('F Y'); ?></h3>
                        </div>
                        <div class="col-md-6">
                            <h4><?= htmlspecialchars($client['client_name']) ?></h4>
                            <p>Plan Name: <?= htmlspecialchars($client['plan_name']) ?></p>
                            <p>Billing Cut Off: <?= htmlspecialchars($client['billingCutOff']) ?></p>
                            <p class="please-pay">Please Pay: ₱<?= number_format($client['totalAccountBalance'], 2) ?></p>
                            <p>On or Before the Disconnection Date: <?= htmlspecialchars($client['fiveDaysBillDueDate']) ?></p>
                        </div>
                        <div class="col-md-6">
                            <div class="payment-reminder">
                                <p>To avoid disconnection of the service,</p>
                                <p>pay your account balance to</p>
                                <h3>Felix I. Albotra Jr.</h3>
                                <p>GCASH# 09453306374</p>
                            </div>
                            <?php if (!empty($client['fb_account'])): ?>
                                <p class="no-print">Facebook: <a href="<?= htmlspecialchars($client['fb_account']) ?>" target="_blank">Message client.</a></p>
                                <button class="btn btn-primary no-print" 
                                    onclick="copyStyledClientInfo(
                                        '<?= htmlspecialchars($client['client_name']) ?>', 
                                        '<?= htmlspecialchars($client['billingCutOff']) ?>',
                                        '<?= number_format($client['totalAccountBalance'], 2) ?>', 
                                        '<?= htmlspecialchars($client['fiveDaysBillDueDate']) ?>'
                                    )">
                                    Send a payment reminder.
                                </button>
                                <button class="btn btn-secondary no-print" 
                                    onclick="sendPaymentConfirmation('<?= htmlspecialchars($client['client_name']) ?>')">
                                    Send a payment confirmation.
                                </button>
                            <?php else: ?>
                                <p class="no-print">Facebook: No FB Account provided</p>
                            <?php endif; ?>
                        </div>
                    </div>
                    <hr>
                <?php endforeach; ?>
                <button class="btn btn-success no-print" onclick="prepareForPrint()">Print</button>
            </div>
        <?php elseif ($_SERVER['REQUEST_METHOD'] === 'POST'): ?>
            <p>No clients found for the selected server with a non-zero balance.</p>
        <?php endif; ?>
    </div>
</body>
</html>