<?php
session_start();
require("quicklyweb.php");
# This page is to be run nightly to bill any customers accounts which are due
$db = getDBConnection();
# Build an array of billing periods.
$bsql = "SELECT * FROM BillInterval LEFT JOIN PackageGroup On BillInterval.BillIntervalID=PackageGroup.BillIntervalID";
$period_results = DBquery($bsql, $db);
checkDBError($db);
While($periods_row = DBfetch_array($period_results)){
If ($periods_row["PackageGroupID"] != NULL){
$pday = $periods_row["Day"];
$pmonth = $periods_row["Month"];
$pyear = $periods_row["Year"];
# Get the number of days next month.
# Note: we add one month to this one and use today for the day
# to return the end of the next billing period.
$nextmonth = mktime (0, 0, 0, date("m") + $pmonth, date("d") + $pday, date("Y") + $pyear);
# Get the number of days this month.
# Note: we add one month to this one and use 0 for the days
# to return the number of days in the month.
$thismonth = mktime (0, 0, 0, date("m") + 1, 0, date("Y"));
# Get the number of days last month. This time we dont add
# anything to the month and we get the number of days for last
# month.
$lastmonth = mktime (0, 0, 0, date("m"), 0, date("Y"));
$endlastmonth = mktime (0, 0, 0, date("m") - $pmonth, 0, date("Y") - $pyear);
if (date("d", $thismonth) >= date("d", $lastmonth) OR (date("d", $thismonth) > date("d", $now))){
# This month has more days than there was last month or today is not the last day of the
# month.
$onemonthago = mktime (0, 0, 0, date("m") - $pmonth, date("d") - $pday, date("Y") - $pyear);
$now = date("Y-m-d", $onemonthago);
$endnow = date("Y-m-d", $endlastmonth);
$thisnow = date("Y-m-d");
$nextnow = date("Y-m-d", $nextmonth);
# Select all the accounts which havnt been billed yet or have a last billed date of today
# one month ago and have not been marked as closed.
$sql = "SELECT CustomerID FROM Accounts WHERE (LastDateBilled='$now') AND (PackageGroupID = $periods_row[PackageGroupID]) OR";
$sql .= " (LastDateBilled='0000-00-00') AND (Status = 'Open') AND (DateClosed = '0000-00-00') GROUP by CustomerID";
$bill2_result = DBquery($sql, $db);
checkDBError($db);
# Step through the results.
while($bill2_row = DBfetch_array($bill2_result)){
$sql = "SELECT A.AccountID, A.CustomerID, A.Username, A.DateOpened, A.LastDateBilled, A.Domain, A.Price, ";
$sql .= "TaxInfo.Quantity, PackageGroup.Description FROM Accounts AS A RIGHT JOIN PackageGroup ";
$sql .= "ON PackageGroup.PackageGroupID=A.PackageGroupID LEFT JOIN Customers ON Customers.CustomerID=A.CustomerID ";
$sql .= "LEFT JOIN TaxInfo ON Customers.TaxID=TaxInfo.TaxInfoID WHERE (A.CustomerID = '" . $bill2_row["CustomerID"] . "') ";
$sql .= "AND (A.LastDateBilled='$now') OR (A.LastDateBilled='0000-00-00') ";
$sql .= "AND (A.Status = 'Open')";
$bill_result = DBquery($sql, $db);
checkDBError($db);
$sqlbill = NULL;
# Insert the new Invoice into the Invoices table.
$sqlbill = "INSERT INTO Invoices VALUES('','" . $bill2_row["CustomerID"] . "','$thisnow','Open','No')";
DBquery($sqlbill, $db);
checkDBError($db);
# Get the insert_id for the Invoice to be used on the InvoiceItems Table.
$insertid = mysql_insert_id();
# Step through the results.
while($bill_row = DBfetch_array($bill_result)){
# Calculate the Tax for the new Invoice.
$taxtotal = ($bill_row["Quantity"] / 100) * $bill_row["Price"];
# Insert the InvoiceItems information along with the dates.
$sqlbillitem = "INSERT INTO InvoiceItems VALUES('','$insertid','" . $bill_row["Description"];
$sqlbillitem .= ", $thisnow to $nextnow";
If ($bill_row["Username"] != ""){
$sqlbillitem .= " - Username: " . $bill_row["Username"];
}
if ($bill_row["Domain"] != ""){
$sqlbillitem .= " - Domain Name: " . $bill_row["Domain"];
}
$sqlbillitem .= "','" . $bill_row["Price"] . "','$taxtotal')";
DBquery($sqlbillitem, $db);
checkDBError($db);
# Update the Accounts table. Set the LastDateBilled field to todays date.
$accountsql = "UPDATE Accounts SET LastDateBilled='$thisnow' WHERE AccountID='" . $bill_row["AccountID"] . "'";
DBquery($accountsql, $db);
checkDBError($db);
}
# This will update the balance field in the Customers Table.
$balance_update = DBquery("SELECT * FROM Invoices WHERE CustomerID='$bill2_row[CustomerID]'", $db);
$totalamount = NULL;
$totalpayments = NULL;
while ($balance_row = DBfetch_array($balance_update)){
$inv_total = DBquery("SELECT Amount, Taxes FROM InvoiceItems WHERE InvoiceID='$balance_row[InvoiceID]'", $db);
while ($inv_row = DBfetch_array($inv_total)){
$totalamount += $inv_row["Amount"];
$totalamount += $inv_row["Taxes"];
}
}
$payment_made = DBquery("SELECT Amount FROM Payments WHERE CustomerID='$bill2_row[CustomerID]'", $db);
while ($payment_row = DBfetch_array($payment_made)){
$totalpayments += $payment_row["Amount"];
}
$gtotal = $totalpayments - $totalamount;
$update_balance = "UPDATE Customers SET Balance='$gtotal' WHERE CustomerID='$bill2_row[CustomerID]'";
DBquery($update_balance, $db);
}
}else{
# If its the last day of the month ->
# This month is shorter than last month so we need to bill all accounts that fall on this
# and following days last month. EG: if today is the 28th of Feb, we will need to bill all
# accounts that were last billed between the 28th to the 31st of Jan.
# Select all the accounts which havnt been billed yet or have a last billed date of today
# one month ago and have not been marked as closed.
$onemonthago = mktime (0, 0, 0, date("m")-1, date("d"), date("Y"));
$now = date("Y-m-d", $onemonthago);
$endnow = date("Y-m-d", $endlastmonth);
$thisnow = date("Y-m-d");
$nextnow = date("Y-m-d", $nextmonth);
$sql = "SELECT A.AccountID, A.CustomerID, A.Username, A.DateOpened, A.LastDateBilled, A.Domain, A.Price, TaxInfo.Quantity, PackageGroup.Description";
$sql .= " FROM Accounts AS A RIGHT JOIN PackageGroup ON PackageGroup.PackageGroupID=A.PackageGroupID LEFT JOIN TaxInfo ON PackageGroup.Tax=TaxInfo.TaxInfoID ";
$sql .= " WHERE (A.LastDateBilled >='$now') AND (A.LastDateBilled <= '$endnow') OR (A.LastDateBilled='0000-00-00')";
$sql .= " AND (A.Status = 'Open') AND (DateClosed = '0000-00-00') order by Username";
$bill_result = DBquery($sql, $db);
checkDBError($db);
# Step through the results.
while($bill_row = DBfetch_array($bill_result)){
$sqlbill = NULL;
# Insert the new Invoice into the Invoices table.
$sqlbill = "INSERT INTO Invoices VALUES('','" . $bill_row["CustomerID"] . "','$thisnow','Open','No')";
DBquery($sqlbill, $db);
checkDBError($db);
# Get the insert_id for the Invoice to be used on the InvoiceItems Table.
$insertid = mysql_insert_id();
# Calculate the Tax for the new Invoice.
$taxtotal = ($bill_row["Quantity"] / 100) * $bill_row["Price"];
# Insert the InvoiceItems information along with the dates.
$sqlbillitem = "INSERT INTO InvoiceItems VALUES('','$insertid','" . $bill_row["Description"];
$sqlbillitem .= ", $thisnow to $nextnow";
If ($bill_row["Username"] != ""){
$sqlbillitem .= " - Username: " . $bill_row["Username"];
}
if ($bill_row["Domain"] != ""){
$sqlbillitem .= " - Domain Name: " . $bill_row["Domain"];
}
$sqlbillitem .= "','" . $bill_row["Price"] . "','$taxtotal')";
DBquery($sqlbillitem, $db);
checkDBError($db);
# Update the Accounts table. Set the LastDateBilled field to todays date.
$accountsql = "UPDATE Accounts SET LastDateBilled='$thisnow' WHERE AccountID='" . $bill_row["AccountID"] . "'";
DBquery($accountsql, $db);
checkDBError($db);
# This will update the balance field in the Customers Table.
$balance_update = DBquery("SELECT * FROM Invoices WHERE CustomerID='$bill2_row[CustomerID]'", $db);
$totalamount = NULL;
$totalpayments = NULL;
while ($balance_row = DBfetch_array($balance_update)){
$inv_total = DBquery("SELECT Amount, Taxes FROM InvoiceItems WHERE InvoiceID='$balance_row[InvoiceID]'", $db);
while ($inv_row = DBfetch_array($inv_total)){
$totalamount += $inv_row["Amount"];
$totalamount += $inv_row["Taxes"];
}
}
$payment_made = DBquery("SELECT Amount FROM Payments WHERE CustomerID='$bill2_row[CustomerID]'", $db);
while ($payment_row = DBfetch_array($payment_made)){
$totalpayments += $payment_row["Amount"];
}
$gtotal = $totalpayments - $totalamount;
$update_balance = "UPDATE Customers SET Balance='$gtotal' WHERE CustomerID='$bill2_row[CustomerID]'";
DBquery($update_balance, $db);
}
}
}
}
?>