PHP Am_Query::groupBy方法代码示例

 protected function createAdapter()
 {
     $q = new Am_Query(new CcRebillTable());
     $q->clearFields();
     $q->groupBy('rebill_date');
     $q->addField('rebill_date');
     $q->addField('(1)', 'is_log');
     $q->addField('COUNT(t.rebill_date)', 'total');
     $q->addField('SUM(IF(t.status=0, 1, 0))', 'status_0');
     $q->addField('SUM(IF(t.status=1, 1, 0))', 'status_1');
     $q->addField('SUM(IF(t.status=2, 1, 0))', 'status_2');
     $q->addField('SUM(IF(t.status=3, 1, 0))', 'status_3');
     $q->addField('SUM(IF(t.status=4, 1, 0))', 'status_4');
     $u = new Am_Query(new InvoiceTable(), 'i');
     $u->addWhere('i.paysys_id IN (?a)', $this->getPlugins());
     $u->groupBy('rebill_date');
     $u->clearFields()->addField('i.rebill_date');
     $u->addField('(0)', 'is_log');
     $u->addField('COUNT(i.invoice_id)', 'total');
     for ($i = 1; $i < 6; $i++) {
         $u->addField('(NULL)');
     }
     $u->leftJoin('?_cc_rebill', 't', 't.rebill_date=i.rebill_date');
     $u->addWhere('i.rebill_date IS NOT NULL');
     $u->addWhere('t.rebill_date IS NULL');
     $q->addUnion($u);
     $q->addOrder('rebill_date', true);
     return $q;
 }

 /**
  * @return Am_Query $query;
  */
 function getMainDataSource()
 {
     $ds = new Am_Query($this->getDi()->invoicePaymentTable);
     $ds->leftJoin('?_user', 'u', 't.user_id = u.user_id')->leftJoin('?_data', 'user_country', 't.invoice_id = user_country.id and `table` = "invoice" and `key`="tax_user_country"')->leftJoin('?_invoice_refund', 'refund', 't.invoice_payment_id = refund.invoice_payment_id')->leftJoin('?_invoice', 'invoice', 't.invoice_id = invoice.invoice_id')->addField('if(user_country.value is null, if(u.country is null, "", u.country), user_country.value)', 'country')->addField('invoice.tax_rate')->addField('round(sum(if(refund.amount, t.amount-refund.amount, t.amount)/t.base_currency_multi),2)', 'sales_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'tax_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount) - (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, t.amount-if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'sales_without_tax_amount')->addField('concat(if(country is null, "", country), "-", if(invoice.tax_rate is null, "", invoice.tax_rate))', 'country_rate');
     $ds->groupBy('country_rate', '');
     $ds->addOrder('country');
     return $ds;
 }

 public function buildQuery(Am_Query $q, $pointField, Am_Report_Date $report)
 {
     $f = $this->getSqlExpr($pointField);
     $q->addField($f, Am_Report_Abstract::POINT_FLD);
     $q->groupBy(Am_Report_Abstract::POINT_FLD, "");
 }

 function applyQueryInterval(Am_Query $q)
 {
     $dateField = $this->getPointField();
     $f = $this->quantity->getSqlExpr($dateField);
     $q->addField($f, self::POINT_FLD);
     $q->groupBy(self::POINT_FLD, "");
     $q->addWhere("{$dateField} BETWEEN ? AND ?", $this->start, $this->stop);
 }

 public function keywordsAction()
 {
     $ds = new Am_Query($this->getDi()->affKeywordTable);
     $ds->addField('t.`value`', 'keyword');
     $ds->addField('count(clicks.log_id)', 'clicks_count');
     $ds->addField('count(distinct leads.user_id)', 'leads_count');
     $ds->addField('sum(if(commissions.record_type="commission", 1, 0))', 'sales_count');
     $ds->addField('sum(if(commissions.record_type="commission", amount, -amount))', 'sales_amount');
     $ds->addWhere('t.aff_id=?', $this->getDi()->auth->getUserId());
     $ds->leftJoin('?_aff_click', 'clicks', 't.keyword_id = clicks.keyword_id');
     $ds->leftJoin('?_aff_commission', 'commissions', 't.keyword_id = commissions.keyword_id');
     $ds->leftJoin('?_aff_lead', 'leads', 't.keyword_id = leads.keyword_id');
     $ds->groupBy('keyword_id');
     $grid = new Am_Grid_ReadOnly('_aff_keywords', 'Keywords', $ds, $this->getRequest(), $this->getView());
     $grid->addField('keyword', ___('Keyword'));
     $grid->addField('clicks_count', ___('Clicks'));
     $grid->addField('leads_count', ___('Leads'));
     $grid->addField('sales_count', ___('Sales'));
     $grid->addField('sales_amount', ___('Commissions'))->setRenderFunction(function ($record) {
         return "<td>" . Am_Currency::render($record->sales_amount) . "</td>";
     });
     $grid->runWithLayout('aff/keywords.phtml');
 }

 /**
  * Return allowed product emails as objects
  * @return array of ResourceAbstract
  * @see self::selectAllowedResources
  */
 function getProductWelcomeEmails($product_ids)
 {
     $ret = array();
     $groups = $this->getDi()->db->selectCol("SELECT product_category_id from ?_product_product_category where product_id IN (?a)", $product_ids);
     $groups[] = -1;
     $q = new Am_Query($this, 'r');
     $q->clearFields();
     $q->addField('DISTINCT r.resource_id', 'resource_id');
     $q->leftJoin('?_email_template', 'et', '(r.resource_id = et.email_template_id)');
     $q->addWhere("resource_type = ?", ResourceAccess::EMAILTEMPLATE);
     $q->addWhere("(r.fn = 'product_id' AND r.id IN (?a) ) OR (r.fn = 'product_category_id' AND r.id IN (?a) )", $product_ids, $groups);
     $q->addWhere('et.name=?', EmailTemplate::PRODUCTWELCOME);
     $q->groupBy('resource_id');
     $res = $this->_db->fetchRows($q->query());
     $ret = array();
     foreach ($res as $r) {
         $ret[] = $this->getDi()->emailTemplateTable->load($r['resource_id']);
     }
     return $ret;
 }