interest.php

gistfile1.txt

<?php
$loan_amount = 3000000; // 3,000,000.
$interest_annual_rate = 0.17; // 17%.
$expected = '{
    "e_data":[
    {
        "ValueDate": "01-Jul-2023",
        "GL Description": "Opening Balance",
        "Debit": "",
        "Credit": "",
        "Balance": "(1,233,291.17)",
        "Original Value date": "01-Jul-2023",
        "Day end Balance": "(1,233,291.17)",
        "Day": "2.00",
        "Interest": "(1,148.82)",
        "Overdraft Charge": "-"
      },
      {
        "ValueDate": "03-Jul-2023",
        "GL Description": "{Batch#: 14395} BEING MARGIN INTEREST FOR JUNE \'23",
        "Debit": "-15,042.21",
        "Credit": "",
        "Balance": "(1,248,333.38)",
        "Original Value date": "03-Jul-2023",
        "Day end Balance": "(1,248,333.38)",
        "Day": "1.00",
        "Interest": "(581.42)",
        "Overdraft Charge": "-"
      },
      {
        "ValueDate": "04-Jul-2023",
        "GL Description": "Stock Sale: 1,000 DANGSUGAR @NGN25.10 for A/C 5500",
        "Debit": "",
        "Credit": "24,846.27",
        "Balance": "(1,223,487.11)",
        "Original Value date": "04-Jul-2023",
        "Day end Balance": "(1,223,487.11)",
        "Day": "1.00",
        "Interest": "(569.84)",
        "Overdraft Charge": "-"
      }
]}';

$jsonData = '{
    "data": [
    {
        "ValueDate": "7/1/2023 12:00:00 AM",
        "Description": "Opening Balance",
        "Debit": "-1233291.1695",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1233291.1695"
    },
    {
        "ValueDate": "7/4/2023 12:00:00 AM",
        "Description": "Stock Sale: 1,000 DANGSUGAR @NGN25.10 for A/C 5500",
        "Debit": "0.0000",
        "Credit": "24846.2688",
        "Label": "credit",
        "Balance": "-1208444.9007"
    },
    {
        "ValueDate": "7/3/2023 12:00:00 AM",
        "Description": "{Batch#: 14395} BEING MARGIN INTEREST FOR JUNE 23",
        "Debit": "-15042.2100",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1223487.1107"
    },
    {
        "ValueDate": "7/4/2023 12:00:00 AM",
        "Description": "Stock Purchase: 5,000 FIDELITYBK @NGN7.69 for A/C 5500",
        "Debit": "-38703.7444",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1262190.8551"
    },
    {
        "ValueDate": "7/5/2023 12:00:00 AM",
        "Description": "Being payment for STOCKBROKING (NG) from NWANEKEZI  MARYANN UGOCHIs wallet",
        "Debit": "0.0000",
        "Credit": "200000.0000",
        "Label": "credit",
        "Balance": "-1062190.8551"
    },
    {
        "ValueDate": "7/5/2023 12:00:00 AM",
        "Description": "Stock Purchase: 3,000 UBA @NGN12.80 for A/C 5500",
        "Debit": "-38653.4200",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1100844.2751"
    },
    {
        "ValueDate": "7/10/2023 12:00:00 AM",
        "Description": "Stock Sale: 500 DANGCEM @NGN285.00 for A/C 5500",
        "Debit": "0.0000",
        "Credit": "141079.6062",
        "Label": "credit",
        "Balance": "-959764.6689"
    },
    {
        "ValueDate": "7/5/2023 12:00:00 AM",
        "Description": "Stock Purchase: 56,000 JAPAULGOLD @NGN0.92 for A/C 5500",
        "Debit": "-51858.5360",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1011623.2049"
    },
    {
        "ValueDate": "7/5/2023 12:00:00 AM",
        "Description": "Stock Purchase: 74,000 JAPAULGOLD @NGN0.92 for A/C 5500",
        "Debit": "-68525.9690",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1080149.1739"
    },
    {
        "ValueDate": "7/6/2023 12:00:00 AM",
        "Description": "Stock Purchase: 100,000 RTBRISCOE @NGN0.81 for A/C 5500",
        "Debit": "-81529.7875",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1161678.9614"
    },
    {
        "ValueDate": "7/6/2023 12:00:00 AM",
        "Description": "Stock Purchase: 15,240 JAPAULGOLD @NGN1.00 for A/C 5500",
        "Debit": "-15343.1695",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1177022.1309"
    },
    {
        "ValueDate": "7/6/2023 12:00:00 AM",
        "Description": "Stock Purchase: 50,000 RTBRISCOE @NGN0.71 for A/C 5500",
        "Debit": "-35734.6063",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1212756.7372"
    },
    {
        "ValueDate": "7/7/2023 12:00:00 AM",
        "Description": "Stock Purchase: 1,500 DANGSUGAR @NGN27.80 for A/C 5500",
        "Debit": "-41974.8288",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1254731.5660"
    },
    {
        "ValueDate": "7/7/2023 12:00:00 AM",
        "Description": "Stock Purchase: 67 JAPAULGOLD @NGN1.11 for A/C 5500",
        "Debit": "-79.1525",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1254810.7185"
    },
    {
        "ValueDate": "7/12/2023 12:00:00 AM",
        "Description": "Stock Purchase: 30,000 JAPAULGOLD @NGN1.47 for A/C 5500",
        "Debit": "-44390.3988",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1299201.1173"
    },
    {
        "ValueDate": "7/28/2023 12:00:00 AM",
        "Description": "Stock Purchase: 20,000 TRANSCORP @NGN3.68 for A/C 5500",
        "Debit": "-74081.7800",
        "Credit": "0.0000",
        "Label": "debit",
        "Balance": "-1373282.8973"
    }
]
}';

$nigeria_holidays = [
    "2023-01-01",
    "2023-10-01"
];

// Convert JSON data to PHP array
$data = json_decode($jsonData, true)['data'];
// dd($data);
function is_business_day($date) {
    return date('N', strtotime($date)) < 6 && !in_array(date('Y-m-d', strtotime($date)), $GLOBALS['nigeria_holidays']);
}

function dd ($data)
{
    var_dump($data);
    die();
}

function add_business_days($start_date, $num_days) {
    $current_date = date('Y-m-d', strtotime($start_date));
    while ($num_days > 0) {
        $current_date = date('Y-m-d', strtotime($current_date . ' +1 day'));
        if (is_business_day($current_date)) {
            $num_days--;
        }
    }
    return $current_date;
}

function add_t_plus_3($value_date, $description) {
    $value_date = date('Y-m-d', strtotime($value_date));
    if (strpos(strtolower($description), 'purchase') != false) {
        $value_date = date('Y-m-d', strtotime($value_date));
        return add_business_days($value_date, 3);
    } 
    else {
        // return add_business_days($value_date, 3);
        return $value_date;
    }
}

function process_json_data($json_data) {
    $processed_data = [];
    foreach ($json_data as $json_object) {
        if (isset($json_object["ValueDate"]) && isset($json_object["Description"])) {
            $new_value_date = add_t_plus_3($json_object["ValueDate"], $json_object["Description"]);
            $json_object["NewValueDate"] = $new_value_date;
            $processed_data[] = $json_object;
        }
    }
    $appopriateBalance = calculateBalances($processed_data);
    return $appopriateBalance;
}

function calculateBalances($data) {
    $balance = $data[0]['Balance'];

    foreach ($data as &$entry) {
        $debit = $entry['Debit'];
        $credit = $entry['Credit'];

        if (!empty($debit)) {
            $balance += (float) $debit; // Cast to float to avoid non-numeric error.
        }

        if (!empty($credit)) {
            $balance += (float) $credit; // Cast to float to avoid non-numeric error.
        }

        $entry['Balance'] = number_format($balance, 2, '.', '');
    }

    return $data;
}

/**
 * Calculates interest.
 * 
 * @param string $d_e_b Day end balance
 * @param int    $day_diff Day difference
 * @param int    $loan_amount
 * @param double $interest_annual_rate 
 * @param int    $decimals (optional) How many decimal points, default is 2.
 * @param int    $no_of_days (optional) No of days in a year. Default is 365
 * @return mixed
 */
function calculate_interest($d_e_b, $day_diff, $loan_amount, $interest_annual_rate, $decimals = 2, $no_of_days = 365) {
    $d_e_b = (int) $d_e_b;

    // Check if Day end balance is negative.
    if ($d_e_b > 0) {
        return 0;
    }

    // Calculate.
    $interest = $d_e_b * ($interest_annual_rate / $no_of_days ) * $day_diff;
    return number_format(abs($interest), $decimals, '.', ''); 

}

/**
 * Calculates Overdraft.
 * 
 * @param string $d_e_b Day end balance
 * @param int    $day_diff Day difference
 * @param int    $loan_amount 
 * @param int    $decimals (optional) How many decimal points, default is 2.
 * @param double $overdraft_rate (optional) In %, Meaining 44/100, so Default value is xx
 * @return mixed
 */
function calculate_overdraft($d_e_b, $day_diff, $loan_amount, $decimals = 2, $overdraft_rate = 0.44) {
    $d_e_b = (int) $d_e_b;
    
    // Check if Day end balance is negative.
    if ($d_e_b > 0) {
        return 0;
    }

    // Check if Day end balance is greater than loan amount to continue.
    if (abs($d_e_b) < $loan_amount) {
        return 0;
    }

    $difference = abs($d_e_b) - $loan_amount;

    // Calculate.
    $overdraft = $difference * $overdraft_rate * $day_diff;

    return number_format(abs($overdraft), $decimals, '.', '');
}


/**
 * Adds interest rate and extras to the json data.
 *
 * If the Json data doesnt have NewValueDate set, it returns false.
 * 
 * @param array $json_data The already processed data.
 * @return array|bool False if newValueDate doesnt exist.
 */
function process_interest_saga_to_json_data($json_data) {
    // Get global values.
    global $loan_amount, $interest_annual_rate;

    // Needed variables.
    $new_json = $json_data;
    $i = 0;
    $day_diff = 0;
    $next_value_date = '';
    $previous_value_date = null; // To store the last different date, because there can be multiple transactions in a day.
    $previous_nth_index = null; // To store last valid transaction index, in case of multiple same day transactions.

	foreach ($json_data as $json_obj) {
		
		// First check if the json_data is processed, so we can use NewValueDate.
		if ( ! isset($json_obj['NewValueDate']) ) {
			return false;
		}
		
		// Looks good, go ahead.
        $new_json[$i]['DayDifference']  = '';
        $new_json[$i]['MarginInterest'] = '';
		$new_json[$i]['Overdraft'] = '';
		$new_json[$i]['MarginPlusOverdraft'] = '';
        

        $next_value_date = isset($new_json[($i+1)]) ? $new_json[($i+1)]['NewValueDate'] : null; // isset($previous_obj['NewValueDate']) ? $previous_obj['NewValueDate'] : '';

        // Check if the next value date isn't same as current.
        if ($next_value_date !== $json_obj['NewValueDate']) { // Last Transaction, calculate.
            $current_date = $json_obj['NewValueDate'];
            $day_diff = ! is_null($previous_value_date) ? (strtotime($current_date) - strtotime($previous_value_date)) / (60 * 60 * 24) : '';

            // Since we're setting day difference to the previous day, run only when previous date has value, meaning when its not the first data.
            if ( !is_null($previous_value_date)) {

                // Note we might have multiple transactions in a day, so the $i-1 value might be faulty.
                if ($new_json[($i-1)]['NewValueDate'] !== $current_date) { // Last data date is not same as current date.
                    $previous_nth_index = ($i-1);
                } else { // It's the same.
                    //$previous_nth_index = ($i-1);
                }

                // Calculate margin interest and overdraft.
                $margin_interest = calculate_interest($new_json[$previous_nth_index]['Balance'], (int) $day_diff, $loan_amount, $interest_annual_rate);
                $overdraft = calculate_overdraft($new_json[$previous_nth_index]['Balance'], (int) $day_diff, $loan_amount, $interest_annual_rate);

                $new_json[$previous_nth_index]['DayDifference']  = (string) $day_diff;
                $new_json[$previous_nth_index]['MarginInterest'] = (string) $margin_interest;
                $new_json[$previous_nth_index]['Overdraft'] = (string) $overdraft;
                $new_json[$previous_nth_index]['MarginPlusOverdraft'] = (string) ($margin_interest + $overdraft);

               // echo "day difference:";
               // var_dump($day_diff);
               // echo "previous nth index:";
              //  var_dump($previous_nth_index);

            }

            // Since this is a last transaction, Store current nth index so as to sort multiple same day transaction issue.
            $previous_nth_index = $i;
            
            // Now store some data for the next row.
            $previous_value_date = $current_date;
        }
      
        // Increment properly to get correct values assigned.
        ++$i;
	}

    return $new_json;
	
}

/**
 * Adds total to specified columns in JSON Date.
 *
 * If the column name doesn't exist, it doesn't do anything.
 * 
 * @param array $json_data The already processed data.
 * @param array $column_names (optional) The column to be summed up.
 * @param string $prefix (optional) Jara, prefix to the new column data. e.g Total-Overdraft.
 * @return array
 */
function add_total_to_json_data($json_data, $column_names = array('DayDifference', 'MarginInterest', 'Overdraft'), $prefix = 'Total-') {
    if (!is_array($column_names)) {
        return $json_data;
    }

    $new_json = $json_data;
    $sum_array = array();

	foreach ($json_data as $json_obj) {
        foreach ($column_names as $column) {
            if(isset($json_obj[$column])) {
                $sum_array[($prefix . $column)] = !isset($sum_array[($prefix . $column)]) ? null : $sum_array[($prefix . $column)]; // Assign first to avoid throwing php warnings.
                $sum_array[($prefix . $column)] += (int) $json_obj[$column];
            }
        } 
    }

    // Add to the last row or whatever.
    $new_json[(count($new_json))] = $sum_array;
    return $new_json;
}

$processed_data = process_json_data($data);

// Testing functions here.
//var_dump(json_decode($expected, true));
var_dump($processed_data);
//var_dump( calculate_interest('-1233291.17', 2, $loan_amount, $interest_annual_rate));
//var_dump( calculate_overdraft('-150000', 3, $loan_amount, $interest_annual_rate));
$proccessed_interest_data = process_interest_saga_to_json_data($processed_data);
var_dump(add_total_to_json_data($proccessed_interest_data));