Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
A simple way to check if all sales orders are invoiced
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:

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


// 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));

$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;

"<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($Invoiceamount2',''.')."</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;

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


No point your browser to

If you used a different filename, change the last part of the URL (after 'file=') to your file name.
You could add this as an inline detail view block on the invoices:

very useful
Hey Joe,

Thanks, I was planning on implementing this into the system. Just not sure in what form yet. The current behaviour is derived from the workflow that sales orders are leading and the company I made this for wants to list all of them to see if they are totally invoiced. I was thinking in incorporating this into the sales order listview, preceded by a popup that lets you choose things like the timeframe of orders you'd like to check, the status ( or multiple) you'd like to in- or exclude and then maybe the possibility to create a PDF (I have some experience with MPDF). Your idea would work as well, only I would add this to the sales order detailview, since it is meant to see if a sales order was completely invoiced. As soon I have a little time, I'll put some work into this.
What I usually do for this use case is add a button to the list view (your ideas is along the right path :-) ). That way the user can use all the filtering conditions and or search facilities the application gives you, which the user already knows how to use, the user then selects the SO he wants to check and the button goes off to another script which receives the list of selected records and does the magic it has to do.

Thanks, I'll try to create something that takes advantage of the already in place filter mechanism. Basically I need to alter my starting-point (a query that retrieves ALL sales orders since january first of the current year) into something that creates a set of sales orders in the current filter view and start collecting invoices from there I think. The rest of the script can stay the same (more or less).
Optional Step: Custom Links
You can add custom web link to the module using the following API:
$moduleInstance = Vtiger_Module::getInstance('ModuleName');
$moduleInstance->addLink(<LinkType>, <LinkLabel>, <LinkURL>);
LinkType Type of Link like -
DETAILVIEW : Will add a link in the 'More Actions' menu on the Detail View of the record.
DETAILVIEWBASIC : Will add a link to the 'Actions' list on the Detail View of the Record.
DETAILVIEWWIDGET : Will add a widget on the right hand side of the Detail View of the
Record, similar to Tag Cloud widget.
LISTVIEW : Will add a link under the 'More Actions' button on the List View of a module.
LISTVIEWBASIC : Will add a button on the List View of the module similar to Delete, Mass
Edit buttons.
LinkLabel Label to use for the link when displaying
LinkURL URL of the link. You can use variables like $variablename$

I'm guessing you meant this? Will implement this when I'm finished with my current Exactonline project.
Yes, a DETAILVIEWWIDGET with the "block:/" option

Forum Jump:

Users browsing this thread: 1 Guest(s)