/**
* Yield total percentage between periods
*
* @param array $yearsminmax [2010,2013]
* @param string $groupby [year, month, quarter]
* @return array
*
* This is a sample of the array result of this method:
*
* [0] => array(21) {
["invoice_id"] => string(3) "532"
["year"] => string(4) "2012"
["gross_grandtotal"] => string(8) "10802.53"
["gross_total"] => string(7) "9102.29"
["gross_vat"] => string(7) "1700.23"
["creditmemo_grandtotal"] => NULL
["creditmemo_total"] => NULL
["creditmemo_vat"] => NULL
["net_grandtotal"] => float(8470.35)
["net_total"] => float(7143.5)
["net_vat"] => float(1326.84)
["month"] => string(1) "4"
["monthname"] => string(5) "April"
["yieldrate"] => string(6) "602,60"
["incdec"] => float(6126.77)
["old"] => array(21) {
["invoice_id"] => string(2) "61"
["year"] => string(4) "2011"
["gross_grandtotal"] => string(7) "2465.52"
["gross_total"] => string(7) "2054.60"
["gross_vat"] => string(6) "410.92"
["creditmemo_grandtotal"] => NULL
["creditmemo_total"] => NULL
["creditmemo_vat"] => NULL
["net_grandtotal"] => float(1242.45)
["net_total"] => float(1016.73)
["net_vat"] => float(225.72)
["month"] => string(1) "4"
["monthname"] => string(5) "April"
["yieldrate"] => int(0)
["incdec"] => int(0)
["old"] => array(0) {
}
["costs_total"] => string(7) "1037.87"
["costs_vat"] => string(6) "185.20"
["costs_grandtotal"] => string(7) "1223.07"
["output_vat"] => string(6) "410.92"
["input_vat"] => string(6) "185.20"
}
["costs_total"] => string(7) "1958.79"
["costs_vat"] => string(6) "373.39"
["costs_grandtotal"] => string(7) "2332.18"
["output_vat"] => string(7) "1700.23"
["input_vat"] => string(6) "373.39"
}
* @return ArrayObject
*/
public static function incomes(array $years = array(), $groupby = "year", $recursive = true)
{
$diff = 0;
// Get the year incomes total and subtract the credit memo
$dq = Doctrine_Query::create()->select("invoice_id, YEAR(i.invoice_date) as year, SUM(o.grandtotal) as gross_grandtotal, SUM(o.total) as gross_total, SUM(o.vat) as gross_vat,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t SUM(cm.total) as creditmemo_grandtotal, SUM(cm.total_net) as creditmemo_total, SUM(cm.total_vat) as creditmemo_vat,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t (SUM(o.grandtotal) - ifnull(SUM(cm.total), 0)) as net_grandtotal,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t (SUM(o.total) - ifnull(SUM(cm.total_net), 0)) as net_total,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t (SUM(o.vat) - ifnull(SUM(cm.total_vat), 0)) as net_vat")->from('Invoices i')->leftJoin('i.Orders o')->leftJoin('o.Customers c')->leftJoin('i.CreditNotes cm')->where('o.status_id = ? OR o.status_id = ?', array(Statuses::id('paid', 'orders'), Statuses::id('complete', 'orders')))->andWhere('c.isp_id = ?', Isp::getCurrentId());
// Group by the prefered fields
if ($groupby == "month") {
$dq->addSelect('MONTH(i.invoice_date) as month');
$dq->addSelect('MONTHNAME(i.invoice_date) as monthname');
$dq->groupBy("month, year")->orderBy('year, month');
} elseif ($groupby == "quarter") {
$dq->addSelect('QUARTER(i.invoice_date) as quarter');
$dq->groupBy("quarter, year")->orderBy('year, quarter');
} else {
$dq->groupBy("year")->orderBy('year');
}
if (!empty($years[0]) && !empty($years[1])) {
$dq->addWhere('YEAR(i.invoice_date) >= ?', $years[0]);
$dq->addWhere('YEAR(i.invoice_date) <= ?', $years[1]);
} elseif (!empty($years[0]) && is_numeric($years[0])) {
$dq->addWhere('YEAR(i.invoice_date) = ?', $years);
}
$income = $dq->execute(null, Doctrine::HYDRATE_ARRAY);
for ($i = 0; $i < count($income); $i++) {
// Yield Percentage
$income[$i]['yieldrate'] = 0;
// Increment / Decrement
$income[$i]['incdec'] = 0;
// Before the last year
$income[$i]['old'] = array();
$income[$i]['costs_total'] = 0;
$income[$i]['costs_vat'] = 0;
$income[$i]['costs_grandtotal'] = 0;
if ($groupby == "month") {
// Load all the purchase invoices per month
$costs = PurchaseInvoices::getSummary($income[$i]['year'], false, false, false, true);
foreach ($costs as $out) {
if ($out['month'] == $income[$i]['month']) {
$income[$i]['costs_total'] = $out['total'];
$income[$i]['costs_vat'] = $out['vat'];
$income[$i]['costs_grandtotal'] = $out['grandtotal'];
$income[$i]['output_vat'] = $income[$i]['net_vat'];
$income[$i]['input_vat'] = $income[$i]['costs_vat'];
$income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
$income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
$income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
break;
}
}
} elseif ($groupby == "quarter") {
// Load all the purchase invoices per quarter
$costs = PurchaseInvoices::getSummary($income[$i]['year'], false, false, true);
foreach ($costs as $out) {
if ($out['quarter'] == $income[$i]['quarter']) {
$income[$i]['costs_total'] = $out['total'];
$income[$i]['costs_vat'] = $out['vat'];
$income[$i]['costs_grandtotal'] = $out['grandtotal'];
$income[$i]['output_vat'] = $income[$i]['net_vat'];
$income[$i]['input_vat'] = $income[$i]['costs_vat'];
$income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
$income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
$income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
break;
}
}
} else {
$costs = PurchaseInvoices::getSummary($income[$i]['year'], false, true);
// Load all the purchase invoices per year
foreach ($costs as $out) {
if ($out['year'] == $income[$i]['year']) {
$income[$i]['costs_total'] = $out['total'];
$income[$i]['costs_vat'] = $out['vat'];
$income[$i]['costs_grandtotal'] = $out['grandtotal'];
$income[$i]['output_vat'] = $income[$i]['net_vat'];
$income[$i]['input_vat'] = $income[$i]['costs_vat'];
$income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
$income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
$income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
break;
}
}
}
// For each Income read the old years values
foreach ($income as $item) {
// If the selected year is before last year
if ($item['year'] == $income[$i]['year'] - 1) {
if ($groupby == "month") {
if ($income[$i]['month'] == $item['month']) {
if ($income[$i]['net_total'] > 0) {
$diff = $income[$i]['net_total'] - $item['net_total'];
$percent = $diff / $item['net_total'] * 100;
} else {
$percent = 0;
}
$income[$i]['old'] = $item;
// Assign the yield percentage value
$income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
$income[$i]['incdec'] = $diff;
// Increase / Decrease
}
} elseif ($groupby == "quarter") {
if ($income[$i]['quarter'] == $item['quarter']) {
if ($income[$i]['net_total'] > 0) {
$diff = $income[$i]['net_total'] - $item['net_total'];
$percent = $diff / $item['net_total'] * 100;
} else {
$percent = 0;
}
$income[$i]['old'] = $item;
// Assign the yield percentage value
$income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
$income[$i]['incdec'] = $diff;
// Increase / Decrease
}
} else {
// Calculate the Yield percentage on diff
if ($income[$i]['net_total'] > 0) {
$diff = $income[$i]['net_total'] - $item['net_total'];
$percent = $diff / $item['net_total'] * 100;
} else {
$percent = 0;
}
$income[$i]['old'] = $item;
// Assign the yield percentage value
$income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
$income[$i]['incdec'] = $diff;
// Increase / Decrease
}
}
}
}
return !empty($income[0]) ? $income : array();
}
