PHP Am_Query::addWhere方法代码示例

 public function createGrid()
 {
     $this->view->headScript()->appendFile(REL_ROOT_URL . "/application/default/views/public/js/jquery/jquery.json.js");
     $this->view->headScript()->appendScript($this->getJs());
     $table = $this->getDi()->savedFormTable;
     $ds = new Am_Query($table);
     $ds->addWhere('`type` in (?a)', array_keys($table->getTypeDefs()));
     $ds->addOrderRaw("`type`='signup' DESC");
     $grid = new Am_Grid_Editable('_s', ___("Forms Editor"), $ds, $this->_request, $this->view);
     $grid->setForm(array($this, 'createForm'));
     $grid->setRecordTitle(' ');
     //$grid->addGridField(new Am_Grid_Field('saved_form_id', '#', true, '', null, '5%'));
     $grid->addGridField(SavedForm::D_SIGNUP, ___('Default Signup'), false)->setWidth('5%')->setRenderFunction(array($this, 'renderDefault'));
     $grid->addGridField(SavedForm::D_MEMBER, ___('Default for Members'), false)->setWidth('5%')->setRenderFunction(array($this, 'renderDefault'));
     $existingTypes = $this->getDi()->savedFormTable->getExistingTypes();
     $grid->actionGet('edit')->setTarget('_top');
     $grid->actionDelete('insert');
     foreach ($this->getDi()->savedFormTable->getTypeDefs() as $type => $typeDef) {
         if (!empty($typeDef['isSingle']) && in_array($type, $existingTypes)) {
             continue;
         }
         $grid->actionAdd(new Am_Grid_Action_Insert('insert-' . $type))->addUrlParam('type', $type)->setTitle(___('New %s', $typeDef['title']));
     }
     $grid->addCallback(Am_Grid_Editable::CB_BEFORE_SAVE, array($this, 'beforeSave'));
     $grid->addGridField(new Am_Grid_Field('type', ___('Type')));
     $grid->addGridField(new Am_Grid_Field('title', ___('Title')));
     $grid->addGridField(new Am_Grid_Field('comment', ___('Comment')));
     $grid->addGridField(new Am_Grid_Field('code', ___('Code')));
     $grid->addGridField(new Am_Grid_Field('url', ___('URL')))->setRenderFunction(array($this, 'renderUrl'));
     $grid->actionGet('delete')->setIsAvailableCallback(create_function('$record', 'return $record->canDelete();'));
     return $grid;
 }

 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
 }

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

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

 function createGrid($id, $title)
 {
     $ds = new Am_Query($this->getDi()->banTable);
     $ds->addWhere("`type` = ?", $id);
     $g = new Am_Grid_Editable('_' . $id, ___("Disallow new Signups by %s", $title), $ds, $this->_request, $this->view);
     $g->setForm(array($this, 'createForm'));
     $g->addGridField("value", ___("Locked Value"));
     $g->addGridField("comment", ___("Comment"));
     $g->addCallback(Am_Grid_ReadOnly::CB_RENDER_TABLE, array($this, 'renderConfig'));
     return $g;
 }

 function createGrid($id, $title)
 {
     $ds = new Am_Query($this->getDi()->banTable);
     $ds->addWhere("`type` = ?", $id);
     $g = new Am_Grid_Editable('_' . $id, ___('Disallow new Signups by %s', $title), $ds, $this->_request, $this->view);
     $g->setPermissionId(Am_Auth_Admin::PERM_BAN);
     $g->setForm(array($this, 'createForm'));
     $g->addField('value', ___('Locked Value'));
     $g->addField('comment', ___('Comment'));
     $g->addCallback(Am_Grid_ReadOnly::CB_RENDER_TABLE, array($this, 'renderConfig'));
     return $g;
 }

 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 getProductsQuery(ProductCategory $category = null)
 {
     if (!$this->query) {
         $scope = false;
         if ($root = $this->getModule()->getConfig('category_id', null)) {
             $scope = array_merge($this->getDi()->productCategoryTable->getSubCategoryIds($root), array($root));
         }
         $this->query = $this->getDi()->productTable->createQuery($category ? $category->product_category_id : null, $this->getHiddenCatCodes(), $scope);
         if ($user = $this->getDi()->auth->getUser()) {
             $products = $this->getDi()->productTable->getVisible();
             $filtered = $this->getDi()->productTable->filterProducts($products, $user->getActiveProductIds(), $user->getExpiredProductIds(), true);
             $hide_pids = array_diff(array_map(function ($p) {
                 return $p->pk();
             }, $products), array_map(function ($p) {
                 return $p->pk();
             }, $filtered));
             if (!empty($hide_pids)) {
                 $this->query->addWhere('p.product_id not in (?a)', $hide_pids);
             }
         }
     }
     return $this->query;
 }

 public function gridAction()
 {
     $prefix = $this->getRequest()->getParam('prefix');
     if (!$prefix) {
         throw new Am_Exception_InputError('prefix is undefined');
     }
     if (!$this->getDi()->uploadAcl->checkPermission($prefix, Am_Upload_Acl::ACCESS_LIST, $this->getDi()->authAdmin->getUser())) {
         throw new Am_Exception_AccessDenied();
     }
     $ds = new Am_Query($this->getDi()->uploadTable);
     $ds->addWhere('prefix=?', $prefix);
     $grid = new Am_Grid_Editable_Uploads('_files', 'Files', $ds, $this->getRequest(), $this->view);
     $grid->setPermissionId('grid_content');
     $grid->addField(new Am_Grid_Field('name', 'Name', true))->setRenderFunction(array($this, 'renderName'));
     $grid->addField(new Am_Grid_Field('desc', 'Description', true));
     $grid->actionsClear();
     $grid->actionAdd(new Am_Grid_Action_LiveEdit('desc'));
     $grid->addCallback(Am_Grid_ReadOnly::CB_RENDER_STATIC, array($this, 'addJs'));
     $grid->isAjax(false);
     $response = $grid->run();
     $response->sendHeaders();
     $response->sendResponse();
 }

 /**
  * select resource accessible for customers using
  * records (user_id, resource_id, resource_type, login, email)
  * @return Am_Query
  */
 function getResourcesForMembers($types = null, $condition = "1=1")
 {
     if ($types && !is_array($types)) {
         $types = (array) $types;
     }
     $qfree = new Am_Query($this, 'rfree');
     $qfree->crossJoin('?_user', 'u')->clearFields()->addField('u.user_id')->addField('rfree.resource_id')->addField('rfree.resource_type')->addField('u.login')->addField('u.email')->addField("rfree.fn", 'fn')->addField("rfree.id", 'fn_id')->groupBy('user_id, resource_id, resource_type', 'u')->addWhere("rfree.fn IN ('free', 'free_without_login')")->addWhere("(\n                            (rfree.start_days IS NULL AND rfree.stop_days IS NULL) \n                            OR\n                            (CEIL((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(u.added))/86400) BETWEEN IFNULL(rfree.start_days,0) AND IFNULL(rfree.stop_days, 90000)) \n                            OR\n                            (CEIL((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(u.added))/86400) >= IFNULL(rfree.start_days,0) AND rfree.stop_days = -1) \n                       )");
     if ($types) {
         $qfree->addWhere('rfree.resource_type IN (?a) AND ' . $condition, $types);
     }
     $q = $this->_getBaseQuery();
     $q->clearFields();
     $q->clearOrder();
     $q->addField('DISTINCT c.user_id')->addField('r.resource_id')->addField('r.resource_type')->addField('u.login')->addField('u.email')->addField("r.fn", 'fn')->addField("r.id", 'fn_id')->leftJoin('?_user', 'u', 'u.user_id=c.user_id')->addOrder('user_id')->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=resource_id AND _resource_type=resource_type LIMIT 1),\n                 resource_id, resource_type")->groupBy('user_id, resource_id, resource_type', 'c')->addWhere("r.fn NOT IN ('free', 'free_without_login')")->addUnion($qfree);
     if ($types) {
         $q->addWhere('r.resource_type IN (?a) AND ' . $condition, $types);
     }
     return $q;
 }

 protected function createAdapter()
 {
     $q = new Am_Query(Am_Di::getInstance()->newsletterListTable);
     $q->addWhere('IFNULL(disabled,0)=0');
     $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;
 }

    public function detailAction()
    {
        $id = (int) $this->getParam('id');
        if (!$id) {
            throw new Am_Exception_InputError('Empty id passed to ' . __METHOD__);
        }
        $ds = new Am_Query($this->getDi()->couponTable);
        $ds->leftJoin('?_user', 'u', 't.user_id=u.user_id');
        $ds->addField('u.login', 'u_login');
        $ds->addWhere('batch_id=?d', $id);
        $grid = new Am_Grid_Editable('_detail', ___('Coupons'), $ds, $this->_request, $this->view);
        $grid->setPermissionId('grid_coupon');
        $grid->setEventId('gridCoupon');
        $grid->actionsClear();
        $grid->addField('code', ___('Code'), true, null);
        $grid->addField(new Am_Grid_Field_Expandable('used_count', ___('Used For'), false))->setGetFunction(array($this, 'getUsedCount'))->setPlaceholder(array($this, 'getPlaceholder'))->setEscape(false);
        $grid->addField('user_id', ___('User'))->setGetFunction(array($this, 'getUser'));
        $grid->setFilter(new Am_Grid_Filter_Text(___('Filter by Code'), array('code' => 'LIKE')));
        $grid->actionAdd(new Am_Grid_Action_LiveEdit('code'));
        $grid->actionAdd(new Am_Grid_Action_LiveEdit('user_id', ___('Click to Assign')))->setInitCallback('l = function(){this.autocomplete({
    minLength: 2,
    source: window.rootUrl + "/admin-users/autocomplete"
});}')->getDecorator()->setInputTemplate(sprintf('<input type="text" placeholder="%s" />', ___('Type Username or E-Mail')));
        $grid->isAjax(false);
        $grid->addCallback(Am_Grid_ReadOnly::CB_RENDER_CONTENT, array($this, 'couponRenderContent'));
        $response = $grid->run();
        $response->sendHeaders();
        $response->sendResponse();
    }

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

 protected function getQuery()
 {
     $q = new Am_Query($this->getDi()->uploadTable);
     $q->addOrder('name');
     if ($this->prefix) {
         $q->addWhere('prefix=?', $this->prefix);
     }
     return $q;
 }

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