PHP Am_Query::addField方法代码示例

 function viewAction()
 {
     // display payouts list date | method | total | paid |
     $id = $this->getInt('payout_id');
     if (!$id) {
         throw new Am_Exception_InputError("Not payout_id passed");
     }
     $ds = new Am_Query($this->getDi()->affPayoutDetailTable);
     $ds->leftJoin('?_aff_payout', 'p', 'p.payout_id=t.payout_id');
     $ds->leftJoin('?_user', 'u', 't.aff_id=u.user_id');
     $ds->addField('u.*');
     $ds->addField('p.type', 'type');
     $ds->addWhere('t.payout_id=?d', $id);
     $grid = new Am_Grid_Editable('_d', ___("Payout %d Details", $id), $ds, $this->_request, $this->view);
     $grid->setPermissionId('affiliates');
     $grid->addCallback(Am_Grid_Editable::CB_RENDER_TABLE, array($this, 'addBackLink'));
     $grid->addField('email', ___('E-Mail'));
     $grid->addField('name_f', ___('First Name'));
     $grid->addField('name_l', ___('Last Name'));
     $grid->addField('type', ___('Payout Method'));
     $grid->addField('amount', ___('Amount'));
     //        $grid->addField('receipt_id', ___('Receipt Id'));
     $grid->addField('is_paid', ___('Is Paid?'));
     $grid->addField(new Am_Grid_Field_Expandable('_details', ___('Payout Details')))->setGetFunction(array($this, 'getPayoutDetails'));
     $grid->actionsClear();
     //$grid->actionAdd(new Am_Grid_Action_LiveEdit('receipt_id'));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkPaid('mark_paid', ___("Mark Paid")));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkNotPaid('mark_notpaid', ___("Mark NOT Paid")));
     $grid->runWithLayout();
     // detail payout records date | method | paid | receipt_id | aff. payout fields
 }

 public function createInvoice()
 {
     $query = new Am_Query(new InvoiceLogTable());
     $query->addField("m.login", "login");
     $query->addField("m.user_id", "user_id");
     $query->addField("i.public_id");
     $query->leftJoin("?_user", "m", "t.user_id=m.user_id");
     $query->leftJoin("?_invoice", "i", "t.invoice_id=i.invoice_id");
     $query->setOrder('log_id', 'desc');
     $g = new Am_Grid_Editable('_invoice', ___('Invoice Log'), $query, $this->getRequest(), $this->view);
     $g->setPermissionId(Am_Auth_Admin::PERM_LOGS_INVOICE);
     $userUrl = new Am_View_Helper_UserUrl();
     $g->addField(new Am_Grid_Field_Date('tm', ___('Date/Time'), true));
     $g->addField(new Am_Grid_Field('invoice_id', ___('Invoice'), true, '', array($this, 'renderInvoice'), '5%'));
     $g->addField(new Am_Grid_Field('login', ___('User'), true))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top'));
     $g->addField(new Am_Grid_Field('remote_addr', ___('IP'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('paysys_id', ___('Paysystem'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '25%'));
     $g->addField(new Am_Grid_Field_Expandable('details', ___('Details'), false, '', null, '25%'))->setGetFunction(array($this, 'renderInvoiceDetails'));
     $g->actionsClear();
     $g->actionAdd(new Am_Grid_Action_InvoiceRetry('retry'));
     $g->setFilter(new Am_Grid_Filter_InvoiceLog());
     $g->actionAdd(new Am_Grid_Action_Group_Callback('retrygroup', ___("Repeat Action Handling"), array('Am_Grid_Action_InvoiceRetry', 'groupCallback')));
     return $g;
 }

 protected function createAdapter()
 {
     $q = new Am_Query(Am_Di::getInstance()->newsletterListTable);
     $q->leftJoin('?_newsletter_user_subscription', 's', 's.list_id = t.list_id AND s.is_active > 0');
     $q->addField('COUNT(s.list_id)', 'subscribed_users');
     return $q;
 }

 /** @return Am_Query */
 public function getQuery()
 {
     $q = new Am_Query($this->getDi()->helpdeskMessageTable, 'hm');
     $q->clearFields();
     $q->addField('COUNT(message_id)', 'cnt');
     $q->addWhere('admin_id IS NULL');
     return $q;
 }

 protected function createAdapter()
 {
     $q = new Am_Query(new CcRebillTable());
     $q->clearFields();
     $q->groupBy('rebill_date');
     $q->addField('rebill_date');
     $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->groupBy('rebill_date');
     $u->clearFields()->addField('i.rebill_date');
     for ($i = 0; $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');
     return $q;
 }

 public function renderOut(&$out)
 {
     $titles = array();
     $this->ds->clearFields()->clearOrder()->toggleAutoGroupBy(false);
     foreach ($this->fields as $field) {
         /* @var $field Am_Grid_Field */
         $name = $field->getFieldName();
         $stm = $this->stms[$name];
         $this->ds->addField(sprintf("SUM({$stm})", $name), '_' . $name);
         $titles['_' . $name] = $field->getFieldTitle();
     }
     $totals = array();
     foreach (Am_Di::getInstance()->db->selectRow($this->ds->getSql()) as $key => $val) {
         $totals[] = sprintf('%s %s: <strong>%s</strong>', ___('Total'), $titles[$key], Am_Currency::render($val));
     }
     $count = count($this->grid->getFields());
     $tr = sprintf('<tr><td class="grid-total" colspan="%d">%s</td></td>', $count, implode(',', $totals));
     $out = preg_replace('|(<tr>\\s*<th>)|', str_replace('$', '\\$', $tr) . '\\1', $out);
 }

 /** @return Am_Query */
 public function getQuery()
 {
     $q = new Am_Query(new AffCommissionTable(), 'cl');
     $q->clearFields();
     $q->addField("SUM(IF(cl.record_type='commission', cl.amount, -cl.amount)) AS commission");
     if ($this->aff_id) {
         $q->addWhere("aff_id = ?d", $this->aff_id);
     }
     return $q;
 }

 /** @return Am_Query */
 public function getQuery()
 {
     $q = new Am_Query(new InvoicePaymentTable(), 'p');
     $q->clearFields();
     foreach ($this->getPaysystems() as $k => $ps) {
         $ps = $q->escape($ps);
         $q->addField("ROUND(SUM(IF(p.paysys_id={$ps}, p.amount/p.base_currency_multi, 0)),2)\n", 'amt_' . $k);
     }
     return $q;
 }

 /** @return Am_Query */
 public function getQuery()
 {
     $q = new Am_Query(new AffCommissionTable(), 'cl');
     $q->clearFields();
     $q->addField("COUNT(DISTINCT invoice_payment_id) AS sales");
     if ($this->aff_id) {
         $q->addWhere("aff_id = ?d", $this->aff_id);
     }
     return $q;
 }

 function renderInvoiceCommissions(Invoice $invoice, Am_View $view)
 {
     $query = new Am_Query($this->getDi()->affCommissionTable);
     $query->leftJoin('?_invoice', 'i', 'i.invoice_id=t.invoice_id');
     $query->leftJoin('?_user', 'a', 't.aff_id=a.user_id');
     $query->leftJoin('?_product', 'p', 't.product_id=p.product_id');
     $query->addField('CONCAT(a.login, \' (\', a.name_f, \' \', a.name_l,\') [#\', a.user_id, \']\')', 'aff_name')->addField('p.title', 'product_title')->addField('IF(payout_detail_id IS NULL, \'no\', \'yes\')', 'is_paid');
     $query->setOrder('commission_id', 'desc');
     $query->addWhere('t.invoice_id=?', $invoice->pk());
     $items = $query->selectAllRecords();
     $view->comm_items = $items;
     $view->invoice = $invoice;
     return $view->render('blocks/admin-user-invoice-details.phtml');
 }

 public function createInvoice()
 {
     $query = new Am_Query(new InvoiceLogTable());
     $query->addField("m.login", "login");
     $query->leftJoin("?_user", "m", "t.user_id=m.user_id");
     $query->setOrder('tm', 'desc');
     $g = new Am_Grid_Editable('_invoice', ___('Invoice Log'), $query, $this->getRequest(), $this->view);
     $g->addField(new Am_Grid_Field('tm', ___('Time'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('invoice_id', ___('Invoice'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('login', ___('User'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('remote_addr', ___('IP'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('paysys_id', ___('Paysystem'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '25%'));
     $g->addField(new Am_Grid_Field_Expandable('details', ___('Details'), false, '', null, '25%'))->setGetFunction(array($this, 'renderInvoiceDetails'));
     $g->actionsClear();
     $g->actionAdd(new Am_Grid_Action_InvoiceRetry());
     $g->setFilter(new Am_Grid_Filter_InvoiceLog());
     return $g;
 }

 public function createGrid()
 {
     $ds = new Am_Query($this->getDi()->countryTable);
     $ds->addField('ABS(tag)', 'tag_abs');
     $ds->setOrderRaw('tag_abs desc, title');
     $grid = new Am_Grid_Editable('_c', ___("Browse Countries"), $ds, $this->_request, $this->view);
     $grid->setPermissionId(Am_Auth_Admin::PERM_COUNTRY_STATE);
     $grid->addField(new Am_Grid_Field('tag_abs', ___('Sort Order'), true, null, null, '10%'));
     $grid->addField(new Am_Grid_Field('title', ___('Title'), true));
     $grid->addField(new Am_Grid_Field('country', ___('Code'), true));
     $grid->setForm(array($this, 'createForm'));
     $grid->actionAdd(new Am_Grid_Action_Url_Country('states', ___('Edit States'), '__ROOT__/admin-states/?country=__COUNTRY__'))->setTarget('_top');
     $grid->actionDelete('delete');
     $grid->actionDelete('insert');
     $grid->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'getTrAttribs'));
     $grid->addCallback(Am_Grid_Editable::CB_VALUES_TO_FORM, array($this, 'valuesToForm'));
     $grid->addCallback(Am_Grid_Editable::CB_VALUES_FROM_FORM, array($this, 'valuesFromForm'));
     $grid->actionAdd(new Am_Grid_Action_LiveEdit('title'));
     $grid->actionAdd(new Am_Grid_Action_Group_CountryEnable(false));
     $grid->actionAdd(new Am_Grid_Action_Group_CountryEnable(true));
     $grid->setFilter(new Am_Grid_Filter_Text(___('Filter by Counrty Title'), array('title' => 'LIKE')));
     return $grid;
 }

 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, "");
 }

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

 public function __construct(Am_Request $request, Am_View $view)
 {
     $di = Am_Di::getInstance();
     $ds = null;
     $i = 0;
     $key = null;
     foreach ($di->resourceAccessTable->getAccessTables() as $k => $t) {
         $q = new Am_Query($t);
         $q->clearFields();
         if (empty($key)) {
             $key = $t->getKeyField();
         }
         $q->addField($t->getKeyField(), $key);
         $type = $t->getAccessType();
         $q->addField("'{$type}'", 'resource_type');
         $q->addField($t->getTitleField(), 'title');
         $q->addField($q->escape($t->getAccessTitle()), 'type_title');
         $q->addField($q->escape($t->getPageId()), 'page_id');
         if ($t instanceof EmailTemplateTable) {
             $q->addWhere('name IN (?a)', array(EmailTemplate::AUTORESPONDER, EmailTemplate::EXPIRE));
         }
         if (empty($ds)) {
             $ds = $q;
         } else {
             $ds->addUnion($q);
         }
     }
     // yes we need that subquery in subquery to mask field names
     // to get access of fields of main query (!)
     $ds->addOrderRaw("(SELECT _sort_order\n             FROM ( SELECT sort_order as _sort_order,\n                    resource_type as _resource_type,\n                    resource_id as _resource_id\n                  FROM ?_resource_access_sort ras) AS _ras\n             WHERE _resource_id={$key} AND _resource_type=resource_type LIMIT 1),\n             {$key}, resource_type");
     parent::__construct('_all', ___('All Content'), $ds, $request, $view, $di);
     $this->addField('type_title', ___('Type'));
     $this->addField('title', ___('Title'));
     if ($this->getDi()->db->selectCell("SELECT COUNT(*) FROM ?_resource_resource_category")) {
         $this->addField(new Am_Grid_Field('rgroup', ___('Categories'), false))->setRenderFunction(array($this, 'renderCategory'));
     }
     $this->actionDelete('insert');
     $this->actionDelete('edit');
     $this->actionDelete('delete');
     $this->actionAdd(new Am_Grid_Action_ContentAllEdit('edit', ___('Edit'), ''));
     $this->actionAdd(new Am_Grid_Action_SortContent());
 }