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