228 lines
10 KiB
PHP
228 lines
10 KiB
PHP
<?php
|
|
chdir("/m/data1/app.hotlunches.net");
|
|
include "db.php";
|
|
|
|
$currencySymbol = '$';
|
|
|
|
$fixDetail = true;
|
|
$showDetail = false;
|
|
|
|
$swhere = "AND record_num = 374";
|
|
|
|
// Get school records
|
|
$schools = db_query("SELECT * FROM schools WHERE status = 'Active' $swhere ORDER BY school_name");
|
|
while ($school_record = db_fetch_assoc($schools)) {
|
|
date_default_timezone_set($school_record['timezone']);
|
|
if ($showDetail) echo($school_record['school_name'] . "\n");
|
|
$school_num = $school_record['record_num'];
|
|
|
|
|
|
// Check for a receipt number
|
|
$results = db_query("SELECT * FROM school_receipt_number WHERE school_num = $school_num");
|
|
if (db_num_rows($results) == 0) {
|
|
db_query("INSERT INTO school_receipt_number VALUES ($school_num,117)");
|
|
}
|
|
else db_query("UPDATE school_receipt_number SET receipt_number = 117 WHERE school_num = $school_num");
|
|
|
|
|
|
$query = "SELECT DISTINCT user_num FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"ORDER BY transaction_datetime";
|
|
$users = db_query($query);
|
|
while ($row = db_fetch_assoc($users)) {
|
|
extract($row);
|
|
|
|
$query = "SELECT sum(transaction_amount) AS balance FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL)";
|
|
$balance = db_getVal($query);
|
|
|
|
$userid = db_getVal("SELECT userid FROM users WHERE school_num = $school_num AND record_num = $user_num");
|
|
|
|
$numDetails = db_getVal("SELECT count(*) FROM user_account_detail WHERE school_num = $school_num AND user_num = $user_num");
|
|
if ($fixDetail) {
|
|
db_query("DELETE FROM user_account_detail WHERE school_num = $school_num AND user_num = $user_num");
|
|
db_query("UPDATE user_account SET transaction_status = 'Open' WHERE school_num = $school_num AND user_num = $user_num AND (deleted != 'Y' OR deleted IS NULL)");
|
|
}
|
|
|
|
if ($showDetail) echo("$user_num: $userid ($numDetails) -- $balance\n");
|
|
$trx_amount = 0;
|
|
|
|
// Check for a credit transaction
|
|
$query = "SELECT * FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"AND transaction_amount < 0.00 " .
|
|
"ORDER BY transaction_datetime";
|
|
$credits = db_query($query);
|
|
|
|
if (db_num_rows($credits) > 0) {
|
|
|
|
// Get total of all payments
|
|
$query = "SELECT sum(transaction_amount) AS amount, count(*) AS num FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"AND transaction_type = 'Payment'";
|
|
$results = db_query($query);
|
|
$row = db_fetch_assoc($results);
|
|
$pTotal = $row['amount'];
|
|
$pNum = $row['num'];
|
|
|
|
// Get total of all other credits
|
|
$query = "SELECT sum(transaction_amount) AS amount, count(*) AS num FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"AND transaction_amount < 0.00 " .
|
|
"AND transaction_type != 'Payment'";
|
|
$results = db_query($query);
|
|
$row = db_fetch_assoc($results);
|
|
$cTotal = $row['amount'];
|
|
$cNum = $row['num'];
|
|
|
|
// Get total of all charges
|
|
$query = "SELECT sum(transaction_amount) AS amount, count(*) AS num FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"AND transaction_amount > 0.00";
|
|
$results = db_query($query);
|
|
$row = db_fetch_assoc($results);
|
|
$dTotal = $row['amount'];
|
|
$dNum = $row['num'];
|
|
|
|
$net = number_format($dTotal + $cTotal + $pTotal,2);
|
|
|
|
if ($showDetail) echo("Payments: $currencySymbol$pTotal ($pNum)\n");
|
|
if ($showDetail) echo("Credits: $currencySymbol$cTotal ($cNum)\n");
|
|
if ($showDetail) echo("Debits: $currencySymbol$dTotal ($dNum)\n");
|
|
if ($showDetail) echo("Net: $currencySymbol$net\n");
|
|
if ($showDetail) echo("\n");
|
|
|
|
$total_credits = 0;
|
|
$total_debits = 0;
|
|
|
|
|
|
// Process each credit transaction
|
|
while ($row = db_fetch_assoc($credits)) {
|
|
$trx_amount = $row['transaction_amount'];
|
|
$trx_type = $row['transaction_type'];
|
|
$ptrxNum = $row['record_num'];
|
|
$note = $row['note'];
|
|
$oNum = $row['order_num'];
|
|
$trxPayments = $row['transaction_payments'];
|
|
|
|
if ($trx_type == 'Credit') {
|
|
$trx_type = 'Generated Credit';
|
|
db_query("UPDATE user_account SET transaction_type = '$trx_type' WHERE school_num = $school_num AND record_num = $ptrxNum");
|
|
}
|
|
|
|
$receipt_status = 'Open';
|
|
$now = time();
|
|
|
|
if ($showDetail) echo("CREDIT TRX: $trx_type: $trx_amount - $note ($ptrxNum)\n");
|
|
|
|
$today = date("Y-m-d",time());
|
|
$order_payments = "";
|
|
$transaction_payments = "";
|
|
|
|
$total_credits += $row['transaction_amount'];
|
|
|
|
$trxComplete = false;
|
|
|
|
$allocated_total = 0;
|
|
|
|
$twhere = "";
|
|
if (strlen($trxPayments)) {
|
|
$entries = explode('|',$trxPayments);
|
|
$trxNums = "";
|
|
for ($i = 0; $i < count($entries); $i++) {
|
|
$code = str_replace('{','',str_replace('}','',$entries[$i]));
|
|
list($trx,$amt) = explode(":",$code);
|
|
if (strlen($trxNums)) $trxNums .= ",";
|
|
$trxNums .= $trx;
|
|
}
|
|
$twhere = "AND record_num IN ($trxNums) ";
|
|
}
|
|
|
|
|
|
// Get current transactions
|
|
$query = "SELECT * FROM user_account " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND user_num = $user_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"AND transaction_amount > 0.00 " .
|
|
"AND transaction_status = 'Open' " .
|
|
$twhere .
|
|
"ORDER BY transaction_datetime, transaction_amount ASC";
|
|
$results = db_query($query);
|
|
while (!$trxComplete && $row = db_fetch_assoc($results)) {
|
|
extract($row);
|
|
|
|
$dtrxNum = $record_num;
|
|
|
|
$tAmt = $transaction_amount * -1;
|
|
$aAmt = floatval(db_getVal("SELECT sum(posted_amount) FROM user_account_detail WHERE school_num = $school_num AND posted_to_transaction = $dtrxNum"));
|
|
$pAmt = $tAmt - $aAmt;
|
|
$total_debits += $transaction_amount;
|
|
$total_net = $total_debits + $total_credits;
|
|
|
|
if ($showDetail) echo("-- DEBIT TRX: $transaction_type $order_num $dtrxNum tAmt: $tAmt aAmt: $aAmt outstanding: $pAmt\n");
|
|
if ($showDetail) echo(" total_debits: $total_debits total_credits: $total_credits total_net: $total_net\n");
|
|
|
|
$available = $trx_amount - $allocated_total;
|
|
if ($showDetail) echo(" - Available: $available -- Allocated: $allocated_total -- Trx Amount: $transaction_amount\n");
|
|
if ($transaction_amount > 0 && abs($pAmt) > abs($available)) {
|
|
$pAmt = $available;
|
|
}
|
|
$allocated_total += $pAmt;
|
|
if ($showDetail) echo(" - Available: $available -- Allocated: $allocated_total -- Amount Posted: $pAmt\n");
|
|
|
|
if ($showDetail) echo(" $transaction_datetime / $dtrxNum -- $transaction_type - $note ($transaction_status) $transaction_amount -- $balance\n");
|
|
|
|
$schedule_num = intval($schedule_num);
|
|
$query = "INSERT INTO user_account_detail (school_num,user_num,receipt_number,receipt_status,transaction_num,posted_to_transaction,posted_amount,posted_schedule,entered_time,deleted) " .
|
|
"VALUES ($school_num,$user_num,'none','$receipt_status',$ptrxNum,$dtrxNum,$pAmt,$schedule_num,$now,'N')";
|
|
if ($fixDetail) db_query($query);
|
|
if ($showDetail) echo(" -> $query\n");
|
|
|
|
// Get total allocated to current transaction
|
|
$postedTotal = db_getVal("SELECT sum(posted_amount) FROM user_account_detail WHERE school_num = $school_num AND posted_to_transaction = $dtrxNum");
|
|
db_query("UPDATE user_account SET amount_paid = $postedTotal WHERE school_num = $school_num AND record_num = $dtrxNum");
|
|
if ($showDetail) echo(" Transaction Total Posted: $postedTotal $transaction_amount\n");
|
|
|
|
if ($postedTotal + $transaction_amount == 0.00) {
|
|
db_query("UPDATE user_account SET transaction_status = 'Closed' WHERE school_num = $school_num AND record_num = $dtrxNum");
|
|
if ($showDetail) echo("DEBIT TRX CLOSED **********************************\n");
|
|
}
|
|
|
|
if ($allocated_total == $trx_amount) {
|
|
$trxComplete = true;
|
|
db_query("UPDATE user_account SET transaction_status = 'Closed' WHERE school_num = $school_num AND record_num = $ptrxNum");
|
|
if ($showDetail) echo("CREDIT TRX CLOSED >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");
|
|
|
|
if ($trx_type == 'Payment') {
|
|
db_query("UPDATE school_receipt_number SET receipt_number = receipt_number + 1 WHERE school_num = $school_num");
|
|
$rcptNum = db_getVal("SELECT receipt_number FROM school_receipt_number WHERE school_num = $school_num");
|
|
$trxDT = date("ymdhi",strtotime($transaction_datetime));
|
|
$rcptNum = $user_num . "-$trxDT";
|
|
db_query("UPDATE user_account_detail SET receipt_status = 'Closed', receipt_number = '$rcptNum' WHERE school_num = $school_num AND user_num = $user_num AND receipt_status = 'Open'");
|
|
db_query("UPDATE user_account_detail SET receipt_number = '$rcptNum' WHERE school_num = $school_num AND user_num = $user_num AND receipt_number = 'none'");
|
|
}
|
|
}
|
|
|
|
if ($showDetail) echo("\n");
|
|
}
|
|
}
|
|
|
|
}
|
|
if ($showDetail) echo("\n");
|
|
}
|
|
}
|
|
|
|
?>
|