A simple way to check if all sales orders are invoiced

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

?>

Code:

Optional Step: Custom Links

You can add custom web link to the module using the following API:

include_once('vtlib/Vtiger/Module.php');

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

In