A simple way to check if all sales orders are invoiced

[Guido1982]

I wrote a small file that does only one simple thing: Check if the sum of all invoices related to a sales order is at least equal to the sales order total. I other words, you can check if you didn't forget to create invoices. It creates a very simple table. Take this code and place it in a NEW file called SO_Invoiced.php, in the modules/SalesOrder folder:

PHP Code:

<?php

// error_reporting(E_ALL);
// ini_set("display_errors", "on");

include_once('vtlib/Vtiger/Module.php');
global $adb;

// Set a new date object for the first of the year
$firstofyear = new DateTime();
// Set a new date object for the last of the year
// $lastofyear = new DateTime();
// Get the current year
$currentYear = Date("Y");
// Get the first day of the current year
$firstofyear = $firstofyear->modify("first day of january".$currentYear);
// Get the last day of the current year
// $lastofyear = $lastofyear->modify("last day of december".$currentYear);
// Format the firstofyear to match database formatting
$firstofyear = $firstofyear->format('Y-m-d G:i:s');

// Create the master array
$SOArray = array();

// Get all the sales orders for the timeframe from the 'crmentity' table
$SOresult = $adb->pquery('SELECT crmid, createdtime FROM vtiger_crmentity WHERE setype=? AND deleted=? AND createdtime > ?',array("SalesOrder",0,$firstofyear));

while($SalesOrderEntity=$adb->fetch_array($SOresult)) {
 // Get the salesorders from the actual salesorders table
 $SalesOrders = $adb->pquery('SELECT salesorderid, subject, salesorder_no, total, accountid, sostatus FROM vtiger_salesorder WHERE salesorderid=?',array($SalesOrderEntity[crmid]));
 // Loop these
 while($SO=$adb->fetch_array($SalesOrders)) {
 // Create array for this sales orders
 $SalesOrder = array();
 // Create empty slot for invoices
 $SalesOrder["Invoices"] = array();
 // Fill it with the order info
 $SODate = new DateTime($SalesOrderEntity[createdtime]);
 $SalesOrder["createdtime"] = $SODate->format('d-m-Y');
 $SalesOrder["crmid"] = $SO[salesorderid];
 $SalesOrder["SO_number"] = $SO[salesorder_no];
 $SalesOrder["subject"] = $SO[subject];
 $SalesOrder["total"] = $SO[total];
 $SalesOrder["sostatus"] = $SO[sostatus];
 // Get the associated account
 $SOAccountResult = $adb->pquery('SELECT accountname FROM vtiger_account WHERE accountid=?',array($SO[accountid]));
 $SOAccount = $adb->query_result_rowdata($SOAccountResult);
 // Place it in the SalesOrder array
 $SalesOrder["Accountname"] = $SOAccount[accountname];
 $SOArray[$SO[salesorder_no]] = $SalesOrder;
 }
}

// Now loop the SOArray and look up all invoices for each Sales Order
foreach($SOArray as $ThisSO){
 // Create an array for invoices
 $Invoices = array();
 // Search the database for Invoices
 $InvoiceResult = $adb->pquery('SELECT subject, invoicedate, total, invoice_no, invoicestatus FROM vtiger_invoice WHERE salesorderid=?',array($ThisSO["crmid"]));
 while ($Invoice=$adb->fetch_array($InvoiceResult)) {
 $ThisInvoice = array();
 $ThisInvoice["subject"] = $Invoice["subject"];
 $Invoicedate = new DateTime($Invoice["invoicedate"]);
 $ThisInvoice["invoicedate"] = $Invoicedate->format('d-m-Y');
 $ThisInvoice["total"] = $Invoice["total"];
 $ThisInvoice["invoice_no"] = $Invoice["invoice_no"];
 $ThisInvoice["status"] = $Invoice["invoicestatus"];
 $Invoices[] = $ThisInvoice;
 }
 // Append all found invoices to the master array
 $SOArray[$ThisSO[SO_number]]["Invoices"] = $Invoices;
}

echo "<table cellpadding='5' border='1' style='width: 80%; margin-left: 10%; border-collapse: collapse'><tbody><tr><td>Orderno:<br>(Except cancelled)</td><td>SO subject line:</td><td>SO date</td><td>Klant</td><td>SO total incl. VAT</td><td>Sum of all invoices <br>(excluding invoices with negative amount)</td><td>Difference</td></tr>";
// Loop through the SO's

foreach ($SOArray as $SO) {
    // Loop through the invoices for this SO
    foreach ($SO["Invoices"] as $Invoice) {
        // Add each invoice amount to the last, creating a sum, but only if the invoice was not a credit invoice
        if ($Invoice["total"] > 0) {
            $Invoiceamount += $Invoice["total"];
        }
    }
    // Now create a line for each SO that was not fully invoiced
    if ($SO["total"] > $Invoiceamount && $SO["sostatus"] != "Cancelled") {
        echo "<tr>";
        echo "<td><a href='/index.php?module=SalesOrder&parenttab=Sales&action=DetailView&record=".$SO["crmid"]."' target='_blank'>".$SO["SO_number"]."</a></td><td>".$SO["subject"]."</td><td>".$SO["createdtime"]."</td><td>".$SO["Accountname"]."</td><td> &euro; ".number_format($SO["total"], 2, ',', '.')."</td><td> &euro; ".number_format($Invoiceamount, 2, ',', '.')."</td><td>&euro; ".number_format(($SO["total"] - $Invoiceamount), 2, ',', '.')."</td>";
        echo "</tr>";
        // Create a sum of ALL SO amounts by adding the current
        // Total to the last ones
        $totallySold += $SO["total"];
        // This value is not reset at the end of the loop and so is a total of all invoiced amount in the table
        $totallyInvoiced += $Invoiceamount;
    }
    // Reset the sum to zero for the next SO
    $Invoiceamount = 0;
}
echo "</tbody></table>";

echo "Total Amount of SO's:<b>&euro; ".number_format($totallySold, 2, ',', '.')."</b><br>";
echo "Total Amount of Invoices:<b>&euro; ".number_format($totallyInvoiced, 2, ',', '.')."</b><br>";
echo "Difference:<b>&euro; ".number_format(($totallySold - $totallyInvoiced), 2, ',', '.')."</b><br><br><br><br><br><br>";

?>

No point your browser to

Code:

http://{your-crm-url}/index.php?module=SalesOrder&action=SalesOrderAjax&file=SO_Invoiced

If you used a different filename, change the last part of the URL (after 'file=') to your file name.