11-18-2015, 02:41 PM
(This post was last modified: 11-19-2015, 10:20 AM by Guido1982.
Edit Reason: Updated code, total amounts were not calculated correctly
)
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:
No point your browser to
If you used a different filename, change the last part of the URL (after 'file=') to your file name.
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> € ".number_format($SO["total"], 2, ',', '.')."</td><td> € ".number_format($Invoiceamount, 2, ',', '.')."</td><td>€ ".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>€ ".number_format($totallySold, 2, ',', '.')."</b><br>";
echo "Total Amount of Invoices:<b>€ ".number_format($totallyInvoiced, 2, ',', '.')."</b><br>";
echo "Difference:<b>€ ".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_InvoicedIf you used a different filename, change the last part of the URL (after 'file=') to your file name.
