PHP SearchHandler::setGroupBy方法代码示例

 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 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:
//.........这里部分代码省略.........

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