<?php
include "db.php";

$currencySymbol = '$';

$fixDetail = true;
$showDetail = false;


// Get school records
$schools = db_query("SELECT * FROM schools WHERE status = 'Active' ORDER BY school_name");
while ($school_record = db_fetch_assoc($schools)) {
   date_default_timezone_set($school_record['timezone']);
   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'];

            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;


            // 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' " .
                     "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_status,transaction_num,posted_to_transaction,posted_amount,posted_schedule,entered_time,deleted) " .
                           "VALUES ($school_num,$user_num,'$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");
                     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'");
                  }
               }

               if ($showDetail) echo("\n");
            }
         }

      }
      if ($showDetail) echo("\n");
   }
}

?>