function sumByStatus($cc = '')
{
$sh = new SearchHandler($this, FALSE);
if ($cc instanceof ConstraintChain) {
$sh->addConstraintChain($cc);
}
$sh->setFields(array('status as id', 'status', 'sum(num_days) as num_days'));
$sh->setGroupBy(array('status as id', 'status'));
$sh->setOrderBy('status');
return $this->load($sh);
}
public function getAuthSummary($_order_id)
{
$sh = new SearchHandler($this, false);
$fields = array("glcentre||' '||glaccount", 'glcentre_id', 'glaccount_id');
$sh->setGroupBy($fields);
$sh->setOrderBy($fields);
$fields[] = 'sum(base_net_value) as net_value';
$sh->setFields($fields);
$sh->addConstraint(new Constraint('order_id', '=', $_order_id));
$sh->addConstraint(new Constraint('status', '!=', $this->_templateobject->cancelStatus()));
$this->load($sh);
}
function summaryPayments()
{
$sh = new SearchHandler($this, false);
$sh->addConstraint(new Constraint('for_payment', 'is', 'true'));
$sh->addConstraint(new Constraint('status', '=', 'O'));
$fields = array('currency', 'payment_type', 'currency_id', 'payment_type_id');
$sh->setGroupBy($fields);
$sh->setOrderby($fields);
$fields[] = 'sum(os_value) as payment';
$fields[] = 'count(*) as records';
$sh->setFields($fields);
$this->load($sh);
}
function summaryPayments()
{
$sh = new SearchHandler($this, false);
$sh->addConstraint(new Constraint('for_payment', 'is', 'true'));
$sh->addConstraint(new Constraint('status', '=', 'O'));
$fields = array('currency', 'payment_type', 'currency_id', 'payment_type_id');
$sh->setGroupBy($fields);
$sh->setOrderby($fields);
// Sum the os_value of all rows marked for_payment
// less the settlement_discount for those rows marked for_payment and include_discount
$fields[] = 'sum(os_value-cast(include_discount as integer)*coalesce(settlement_discount,0)) as payment';
$fields[] = 'count(*) as records';
$sh->setFields($fields);
$this->load($sh);
}
function statusSummary($orderline_id)
{
// Returns an array of the statuses of POReceived lines
// for the supplied orderline_id
// i.e. there can be more than one received line (part delivery) for an order line
$sh = new SearchHandler($this, false);
$sh->addConstraint(new Constraint('orderline_id', '=', $orderline_id));
$sh->setFields(array('status', 'sum(received_qty) as received_qty'));
$sh->setGroupBy(array('status'));
$sh->setOrderBy(array('status'));
$rows = $this->load($sh, null, RETURN_ROWS);
$status = array();
foreach ($rows as $line) {
$status[$line['id']] = $line['received_qty'];
}
return $status;
}
function getServiceHistory()
{
$enddate = fix_date('01/' . date('m/Y'));
$startdate = fix_date(date(DATE_FORMAT, strtotime("-12 months", strtotime($enddate))));
$enddate = fix_date(date(DATE_FORMAT));
$this->_tablename = 'customer_service_summary';
$sh = new SearchHandler($this, FALSE);
$sh->addConstraint(new Constraint('despatch_date', 'between', "'" . $startdate . "' and '" . $enddate . "'"));
$sh->setFields(array('year_month', 'sum(ontime) as ontime', 'sum(infull) as infull', 'sum(otif) as otif', 'sum(count) as count'));
$sh->setGroupBy("year_month");
$sh->setOrderBy("year_month");
$this->load($sh);
for ($i = 0; $i < 13; $i++) {
$date = date('Y/m', strtotime("+" . $i . " months", strtotime($startdate)));
$this->customerorders['previous'][$date]['ontime'] = 0;
$this->customerorders['previous'][$date]['infull'] = 0;
$this->customerorders['previous'][$date]['ontime_infull'] = 0;
$this->customerorders['previous'][$date]['count'] = 0;
}
foreach ($this as $despatch) {
if (isset($this->customerorders['previous'][$despatch->id])) {
$this->customerorders['previous'][$despatch->id]['ontime'] = $despatch->ontime;
$this->customerorders['previous'][$despatch->id]['infull'] = $despatch->infull;
$this->customerorders['previous'][$despatch->id]['ontime_infull'] = $despatch->otif;
$this->customerorders['previous'][$despatch->id]['count'] = $despatch->count;
}
}
foreach ($this->customerorders['previous'] as $date => $month) {
if ($month['count'] == 0) {
$this->customerorders['previous'][$date]['ontime%'] = 0;
$this->customerorders['previous'][$date]['infull%'] = 0;
$this->customerorders['previous'][$date]['ontime_infull%'] = 0;
} else {
$this->customerorders['previous'][$date]['ontime%'] = $month['ontime'] * 100 / $month['count'];
$this->customerorders['previous'][$date]['infull%'] = $month['infull'] * 100 / $month['count'];
$this->customerorders['previous'][$date]['ontime_infull%'] = $month['ontime_infull'] * 100 / $month['count'];
}
}
return $this->customerorders;
}
function populate()
{
$orderline = DataObjectFactory::Factory('SOrderLine');
$cc = new ConstraintChain();
$cc->add(new Constraint('status', 'in', "('D', 'P')"));
$order_total = $orderline->getSum('base_net_value', $cc);
$orders = new SOrderLineCollection($orderline);
$orders->setParams();
$sh = new SearchHandler($orders, FALSE);
$fields = array('order_id', 'order_number', 'customer', 'order_date', 'actual_despatch_date');
$sh->setGroupBy($fields);
$sh->setOrderBy(array('actual_despatch_date', 'order_number', 'customer'));
$fields[] = 'sum(base_net_value) as base_net_value';
$sh->setFields($fields);
$sh->addConstraint($cc);
$this->setSearchLimit($sh);
$orders->load($sh);
$orders->collection_date_label = 'actual_despatch_date';
$orders->collection_total_label = 'Total (Base Net Value) Order Lines not Invoiced';
$orders->collection_total = $order_total;
$this->contents = $orders;
}
/**
* getWritableCalendars
*
* Used to return an array of calendars that a specific user has write access to
* This will automatically exclude gcal until write functionality has been developed
*
* @return array of writable calendars
*/
function getWritableCalendars($calendar_id = '')
{
$calendar = new CalendarCollection($this);
$sh = new SearchHandler($calendar, false);
if (!empty($calendar_id)) {
$sh->addConstraint(new Constraint('id', '=', $calendar_id));
}
$sh->addConstraint(new Constraint('type', '!=', 'gcal'));
$sh->addConstraint(new Constraint('owner', '=', EGS_USERNAME));
$cc = new ConstraintChain();
$cc->add(new Constraint('type', '=', 'group'));
$cc->add(new Constraint('username', '=', EGS_USERNAME));
$sh->addConstraintChain($cc, 'OR');
$sh->setOrderby('name');
$sh->setGroupBy('id');
$calendar->load($sh);
$calendar_id = array();
foreach ($calendar as $key => $value) {
$calendar_id[$value->id] = $value->name . " (" . $value->owner . ")";
}
return $calendar_id;
}
function getYearEndBalances($glperiod_ids, $type)
{
if (count($glperiod_ids) > 0) {
$sh = new SearchHandler($this, false);
switch ($type) {
case 'P':
$fields = array('actype');
break;
case 'B':
$fields = array('glcentre_id||\'-\'||glaccount_id', 'glcentre_id', 'glaccount_id');
break;
}
$sh->setGroupBy($fields);
$sh->setOrderBy($fields);
$fields[] = 'sum(value) as value';
$sh->setFields($fields);
$periodsYTD = '(' . implode(',', $glperiod_ids) . ')';
$sh->addConstraint(new Constraint('actype', '=', $type));
$sh->addConstraint(new Constraint('glperiods_id', 'in', $periodsYTD));
$this->load($sh);
}
}
protected function getLineStatuses($_orderline, $_orderlines)
{
$linestatus = array();
$statuses = $_orderline->getEnumOptions('status');
foreach ($statuses as $key => $name) {
$linestatus['linecount'] = 0;
$linestatus['count'][$key] = 0;
$linestatus['value'][$name] = 0;
}
$sh = new SearchHandler($_orderlines, false);
$sh->setFields(array('status', 'sum(net_value) as net_value', 'count(*) as count'));
$sh->setGroupBy('status');
$sh->setOrderBy('status');
$sh->addConstraint(new Constraint('order_id', '=', $this->id));
$_orderlines->load($sh);
if ($_orderlines->count() > 0) {
foreach ($_orderlines as $status) {
$linestatus['linecount'] += $status->count;
$linestatus['count'][$status->id] = $status->count;
$linestatus['value'][$statuses[$status->id]] = $status->net_value;
}
}
return $linestatus;
}
function getVAT($data, $glperiod_ids, $control_accounts, $sum = false, $paging = false)
{
if (count($glperiod_ids) > 0) {
$glperiods = implode(', ', $glperiod_ids);
// Set data source
switch ($data['box']) {
// Tax overview
case 1:
case 4:
case 7:
case 6:
$this->_tablename = 'gltransactions_vat';
break;
// EU sales
// EU sales
case 8:
$this->_tablename = 'gl_taxeusales';
break;
// EU purchases
// EU purchases
case 2:
case 9:
$this->_tablename = 'gl_taxeupurchases';
break;
}
// Set constraints
$cc = new ConstraintChain();
$cc->add(new Constraint('glperiods_id', ' IN', '(' . $glperiods . ')'));
switch ($data['box']) {
// VAT output
case 1:
$cc->add(new Constraint('glaccount_id', '=', $control_accounts['vat_output']));
break;
// EU acquisitions
// EU acquisitions
case 2:
case 9:
$cc->add(new Constraint('glaccount_id', '=', $control_accounts['eu_acquisitions']));
break;
// VAT input - also requires value from box 2
// VAT input - also requires value from box 2
case 4:
$cc->add(new Constraint('glaccount_id', '=', $control_accounts['vat_input']));
break;
// VAT output
// VAT output
case 6:
case 8:
$cc->add(new Constraint('glaccount_id', '=', $control_accounts['vat_output']));
break;
// VAT input
// VAT input
case 7:
$cc->add(new Constraint('glaccount_id', '=', $control_accounts['vat_input']));
break;
}
if (isset($data['page'])) {
$sh = new SearchHandler($this, true);
} else {
$sh = new SearchHandler($this, false);
}
if ($sum) {
$fields = array('1');
$sh->setGroupBy($fields);
$sh->setOrderby($fields);
// Set aggregate field
switch ($data['box']) {
// VAT output
case 1:
$fields[] = 'SUM(vat) * -1 AS sum';
break;
// EU acquisitions (positive values only)
// EU acquisitions (positive values only)
case 2:
// VAT input/EU acquisitions (positive values only)
// VAT input/EU acquisitions (positive values only)
case 4:
$fields[] = 'SUM(vat) AS sum';
break;
// VAT output
// VAT output
case 6:
case 8:
$fields[] = 'SUM(net) * -1 AS sum';
break;
// VAT input
// VAT input
case 7:
case 9:
$fields[] = 'SUM(net) AS sum';
break;
}
} else {
// $this->num_pages = 1;
// $this->cur_page = 1;
$fields = array('id', 'docref', 'glaccount_id', 'glcentre_id', 'glperiods_id', 'transaction_date', 'source', 'comment', 'type', 'usercompanyid');
switch ($data['box']) {
// VAT output
case 1:
case 6:
case 8:
$fields[] = 'vat * -1 AS vat';
$fields[] = 'net * -1 AS net';
break;
default:
$fields[] = 'vat';
$fields[] = 'net';
break;
}
$sh->setOrderby(array('transaction_date', 'docref'));
if ($paging) {
$sh->extract();
}
}
$sh->setFields($fields);
$sh->addConstraintChain($cc);
$this->load($sh);
}
}
public function ordersForInvoicing()
{
$sh = new SearchHandler($this, false);
$DisplayFields = array('order_id', 'order_number');
$sh->setOrderby(array('order_number'), array('ASC'));
$sh->setFields($DisplayFields);
$sh->setGroupBy($DisplayFields);
$order = DataObjectFactory::Factory('Sorder');
$sh->addConstraint(new Constraint('status', '=', $order->despatchStatus()));
$this->load($sh);
return $this;
}
function getTopSales($top = 10, $type = 'customer')
{
$startdate = fix_date('01/' . date('m/Y'));
$enddate = fix_date(date(DATE_FORMAT, strtotime("-1 days", strtotime("+1 months", strtotime($startdate)))));
$fields = array();
switch ($type) {
case 'customer':
$fields[] = 'customer';
$sumby = 'base_net_value';
break;
case 'item by qty':
$fields[] = 'stitem';
$sumby = 'sales_qty';
$sh->addConstraint(new Constraint('stitem', 'is not', 'NULL'));
break;
case 'item by value':
$fields[] = 'stitem';
$sumby = 'base_net_value';
$sh->addConstraint(new Constraint('stitem', 'is not', 'NULL'));
break;
}
$invoice_fields = $fields;
$invoices = clone $this;
$sh = new SearchHandler($invoices, false);
$sh->setGroupBy($invoice_fields);
$sh->setOrderby($invoice_fields);
$invoice_fields[] = 'sum(' . $sumby . ') as value';
$sh->setFields($invoice_fields);
$sh->addConstraint(new Constraint('invoice_date', 'between', "'" . $startdate . "' and '" . $enddate . "'"));
$sh->addConstraint(new Constraint('transaction_type', '=', 'I'));
$invoice_data = $invoices->load($sh, null, RETURN_ROWS);
$credit_fields = $fields;
$sh = new SearchHandler($this, false);
$sh->setGroupBy($credit_fields);
$sh->setOrderby($credit_fields);
$credit_fields[] = 'sum(' . $sumby . ') as value';
$sh->setFields($credit_fields);
$sh->addConstraint(new Constraint('invoice_date', 'between', "'" . $startdate . "' and '" . $enddate . "'"));
$sh->addConstraint(new Constraint('transaction_type', '=', 'C'));
$credits = $this->load($sh, null, RETURN_ROWS);
$data = array();
if (is_array($invoice_data)) {
foreach ($invoice_data as $invoice) {
$data[$invoice['id']] = $invoice['value'];
}
}
if (is_array($credits)) {
foreach ($credits as $credit) {
if (isset($data[$credit['id']])) {
$data[$credit['id']] -= $credit['value'];
}
}
}
arsort($data, SORT_NUMERIC);
$typesarray = array('customer' => 'By Customer', 'item by qty' => 'By Item Quantity', 'item by value' => 'By Item Value');
$this->customerorders = array('source' => 'invoices', 'controller' => 'sinvoices', 'submodule' => 'sales_invoicing', 'types' => $typesarray, 'type' => $type, 'details' => array());
$count = 0;
foreach ($data as $key => $value) {
if ($count < $top) {
$this->customerorders['details'][$key] = $value;
} else {
break;
}
$count++;
}
return $this->customerorders;
}
function getSalesHistory()
{
$db = DB::Instance();
$startmonth = fix_date('01/' . date('m/Y'));
$currentmonth = date('Y/m');
$startdate = fix_date(date(DATE_FORMAT, strtotime("-12 months", strtotime($startmonth))));
$today = fix_date(date(DATE_FORMAT));
$enddate = $today;
$lastweekend = fix_date(date(DATE_FORMAT, strtotime("last sunday")));
$lastweekstart = fix_date(date(DATE_FORMAT, strtotime("-6 days", strtotime($lastweekend))));
$thisweekend = fix_date(date(DATE_FORMAT, strtotime("next sunday")));
$thisweekstart = fix_date(date(DATE_FORMAT, strtotime("-6 days", strtotime($thisweekend))));
$glperiod = DataObjectFactory::Factory('GLPeriod');
$currentperiod = $glperiod->loadPeriod(date(DATE_FORMAT));
$glperiod->loadBy(array('year', 'period'), array($currentperiod->year, 1));
$yearstart = fix_date($glperiod->getPeriodStartDate($currentperiod->id));
for ($i = 11; $i >= 0; $i--) {
$date = date('Y/m', strtotime("+" . $i . " months", strtotime($startdate)));
$this->customerorders['previous'][$date]['start_date'] = fix_date(date(DATE_FORMAT, strtotime("+" . $i . " months", strtotime($startdate))));
$this->customerorders['previous'][$date]['end_date'] = fix_date(date(DATE_FORMAT, strtotime("+" . ($i + 1) . " months", strtotime($startdate)) - 1));
$this->customerorders['previous'][$date]['value'] = '0.00';
}
$this->customerorders['current']['today']['start_date'] = $today;
$this->customerorders['current']['today']['end_date'] = $today;
$this->customerorders['current']['today']['value'] = '0.00';
$this->customerorders['current']['this_week']['start_date'] = $thisweekstart;
$this->customerorders['current']['this_week']['end_date'] = $thisweekend;
$this->customerorders['current']['this_week']['value'] = '0.00';
$this->customerorders['current']['last_week']['start_date'] = $lastweekstart;
$this->customerorders['current']['last_week']['end_date'] = $lastweekend;
$this->customerorders['current']['last_week']['value'] = '0.00';
$this->customerorders['current']['this_month_to_date']['start_date'] = $startmonth;
$this->customerorders['current']['this_month_to_date']['end_date'] = $today;
$this->customerorders['current']['this_month_to_date']['value'] = '0.00';
$this->customerorders['current']['year_to_date']['start_date'] = $yearstart;
$this->customerorders['current']['year_to_date']['end_date'] = $today;
$this->customerorders['current']['year_to_date']['value'] = '0.00';
$current = DataObjectFactory::Factory('SInvoice');
$transaction_types = $current->getEnumOptions('transaction_type');
$multipliers = $current->getMultipliers();
foreach ($transaction_types as $transaction_type => $transaction_type_title) {
$sh = new SearchHandler($this, false);
$sh->setFields(array("to_char(invoice_date, 'YYYY/MM')", 'sum(base_net_value) as value'));
$sh->setGroupBy("to_char(invoice_date, 'YYYY/MM')");
$sh->setOrderBy("to_char(invoice_date, 'YYYY/MM')");
$sh->addConstraint(new Constraint('invoice_date', 'between', "'" . $startdate . "' and '" . $enddate . "'"));
$sh->addConstraint(new Constraint('transaction_type', '=', $transaction_type));
$this->load($sh);
foreach ($this as $invoice) {
$value = bcmul($invoice->value, $multipliers[$transaction_type]);
if ($invoice->id == $currentmonth) {
$this->customerorders['current']['this_month_to_date']['value'] = bcadd($value, $this->customerorders['current']['this_month_to_date']['value']);
} else {
$this->customerorders['previous'][$invoice->id]['value'] = bcadd($value, $this->customerorders['previous'][$invoice->id]['value']);
}
}
$this->clear();
}
foreach ($transaction_types as $transaction_type => $transaction_type_title) {
$cc = new ConstraintChain();
$cc->add(new Constraint('invoice_date', 'between', "'" . $lastweekstart . "' and '" . $lastweekend . "'"));
$cc->add(new Constraint('transaction_type', '=', $transaction_type));
$value = bcmul($current->getSum('base_net_value', $cc), $multipliers[$transaction_type]);
$this->customerorders['current']['last_week']['value'] = bcadd($value, $this->customerorders['current']['last_week']['value']);
}
foreach ($transaction_types as $transaction_type => $transaction_type_title) {
$cc = new ConstraintChain();
$cc->add(new Constraint('invoice_date', 'between', "'" . $thisweekstart . "' and '" . $thisweekend . "'"));
$cc->add(new Constraint('transaction_type', '=', $transaction_type));
$value = bcmul($current->getSum('base_net_value', $cc), $multipliers[$transaction_type]);
$this->customerorders['current']['this_week']['value'] = bcadd($value, $this->customerorders['current']['this_week']['value']);
}
foreach ($transaction_types as $transaction_type => $transaction_type_title) {
$cc = new ConstraintChain();
$cc->add(new Constraint('invoice_date', '=', $db->qstr(fix_date(date(DATE_FORMAT)))));
$cc->add(new Constraint('transaction_type', '=', $transaction_type));
$value = bcmul($current->getSum('base_net_value', $cc), $multipliers[$transaction_type]);
$this->customerorders['current']['today']['value'] = bcadd($value, $this->customerorders['current']['today']['value']);
}
foreach ($transaction_types as $transaction_type => $transaction_type_title) {
$cc = new ConstraintChain();
$cc->add(new Constraint('invoice_date', 'between', "'" . $yearstart . "' and '" . $thisweekend . "'"));
$cc->add(new Constraint('transaction_type', '=', $transaction_type));
$value = bcmul($current->getSum('base_net_value', $cc), $multipliers[$transaction_type]);
$this->customerorders['current']['year_to_date']['value'] = bcadd($value, $this->customerorders['current']['year_to_date']['value']);
}
return $this->customerorders;
}