Location: PHPKode > projects > QUICKLYWEB > quicklyweb/admin/cron_nightlybilling.php
<?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);
                 }
             }
         }
     }
?>
Return current item: QUICKLYWEB