pokeloken

Back Back Up Advanced Editor

<?php

function calculateAccountBalance($conn, $clientId) {
    $sql = "SELECT SUM(amount) as totalAccountBalance FROM transactions WHERE client_id = '$clientId'";
    $result = $conn->query($sql);
    $data = $result->fetch_assoc();

return [
        'totalAccountBalance' => $data['totalAccountBalance'],
        'billingCutOff' => '2024-06-30', // Example value
        'forPaymentReminder' => 'Yes', // Example value
        'fiveDaysBillDueDate' => '2024-07-05', // Example value
        'forDisconnection' => 'No' // Example value
    ];
}

function getBillingCutOff($installationYear, $installationMonth, $installationDay, $cut_off_day) {
    $installationDate = DateTime::createFromFormat('Y-m-d', "$installationYear-$installationMonth-$installationDay");
    $currentDate = new DateTime();
    $currentYear = (int)$currentDate->format('Y');
    $currentMonth = (int)$currentDate->format('m');
    $installationWithinCurrentMonth = ($installationDate->format('Y') == $currentYear && $installationDate->format('m') >= $currentMonth);

if ($cut_off_day == 0) {
        if ($installationWithinCurrentMonth) {
            $billingCutOff = new DateTime("$currentYear-$currentMonth-$installationDay");
            $billingCutOff->modify('+1 month');
        } else {
            $billingCutOff = new DateTime("$currentYear-$currentMonth-$installationDay");
        }
    } else {
        $billingCutOff = new DateTime("$currentYear-$currentMonth-$cut_off_day");
    }

return $billingCutOff->format('Y-m-d');
}

function calculateClientDetails($conn, $clientId) {
    // Fetch client details
    $sqlClient = "SELECT firstname, lastname, server, status, installation_date, plan_name, plan_price, installation_fee, cut_off_day FROM member_list WHERE id = '$clientId'";
    $resultClient = $conn->query($sqlClient);
    $clientData = $resultClient->fetch_assoc();

if (!$clientData) {
        return null; // Client not found
    }

// Calculate account balance
    $accountDetails = calculateAccountBalance($conn, $clientId);

// Billing Cut Off Calculation
    $installationDate = new DateTime($clientData['installation_date']);
    $billingCutOff = getBillingCutOff($installationDate->format('Y'), $installationDate->format('m'), $installationDate->format('d'), $clientData['cut_off_day']);

// Number of Months Since Installation Date
    $currentDate = new DateTime();
    $interval = $installationDate->diff($currentDate);
    $numberOfMonthsSinceInstallation = ($interval->y * 12) + $interval->m;

// 5 Days Bill Due Date
    $billingCutOffDateTime = new DateTime($billingCutOff);
    $fiveDaysBillDueDate = clone $billingCutOffDateTime;
    $fiveDaysBillDueDate->add(new DateInterval('P5D'));

// Total Installation Payments Calculation
    $clientName = $clientData['firstname'] . ' ' . $clientData['lastname'];
    $sqlInstallationPayments = "SELECT SUM(total_amount) AS totalInstallationPayments 
                                FROM collection_list 
                                WHERE name_of_payment = 'Installation Fee' AND member_id = '$clientId'";
    
    $resultInstallationPayments = $conn->query($sqlInstallationPayments);
    $totalInstallationPayments = $resultInstallationPayments ? $resultInstallationPayments->fetch_assoc()['totalInstallationPayments'] : 0;

// Calculate Installation Balance
    $installationFee = $clientData['installation_fee'];
    $installationBalance = $installationFee - $totalInstallationPayments;

// Calculate Total Monthly Usage
    $totalMonthlyUsage = 0;
    if ($currentDate > $billingCutOffDateTime) {
        $totalMonthlyUsage = $clientData['plan_price'] * $numberOfMonthsSinceInstallation;
    }

// Total Monthly Fee Payments Calculation
    $sqlMonthlyFeePayments = "SELECT SUM(total_amount) AS totalMonthlyFeePayments 
                              FROM collection_list 
                              WHERE name_of_payment = 'Net Monthly Fee' AND member_id = '$clientId'";
    
    $resultMonthlyFeePayments = $conn->query($sqlMonthlyFeePayments);
    $totalMonthlyFeePayments = $resultMonthlyFeePayments ? $resultMonthlyFeePayments->fetch_assoc()['totalMonthlyFeePayments'] : 0;

// Total ProRate Payments Calculation
    $sqlProRatePayments = "SELECT SUM(total_amount) AS totalProRatePayments 
                           FROM collection_list 
                           WHERE name_of_payment = 'ProRate' AND member_id = '$clientId'";
    
    $resultProRatePayments = $conn->query($sqlProRatePayments);
    $totalProRatePayments = $resultProRatePayments ? $resultProRatePayments->fetch_assoc()['totalProRatePayments'] : 0;

// Calculate Remaining Unpaid Amount
    $remainingUnpaidAmount = $totalMonthlyUsage - $totalMonthlyFeePayments;

$totalAccountBalance = $installationBalance + $remainingUnpaidAmount;

// For Payment Reminder?
    $forPaymentReminder = 'No';
    if ($totalAccountBalance > 0 && ($currentDate > $installationDate || $currentDate >= $billingCutOffDateTime)) {
        $forPaymentReminder = 'Yes';
    }

// For Disconnection?
    $forDisconnection = ($remainingUnpaidAmount > 0) ? 'Yes' : 'No';

// For Permanent Disconnection?
    $forPermanentDisconnection = 'No'; // Set a default value or add your logic here

return array_merge($clientData, $accountDetails, [
        'billingCutOff' => $billingCutOff,
        'numberOfMonthsSinceInstallation' => $numberOfMonthsSinceInstallation,
        'fiveDaysBillDueDate' => $fiveDaysBillDueDate->format('Y-m-d'),
        'totalInstallationPayments' => $totalInstallationPayments,
        'installationBalance' => $installationBalance,
        'totalMonthlyUsage' => $totalMonthlyUsage,
        'totalMonthlyFeePayments' => $totalMonthlyFeePayments,
        'totalProRatePayments' => $totalProRatePayments,
        'remainingUnpaidAmount' => $remainingUnpaidAmount,
        'totalAccountBalance' => $totalAccountBalance,
        'forPaymentReminder' => $forPaymentReminder,
        'forDisconnection' => $forDisconnection,
        'forPermanentDisconnection' => $forPermanentDisconnection // Ensure this key is set
    ]);
}
?>