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 */ 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 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("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 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; }
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); }
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()); }
/** * @return Am_Query */ protected function _getBaseQuery($joinConditions = "") { if ($joinConditions) { $joinConditions = "(" . $joinConditions . ") AND "; } $q = new Am_Query($this, 'r'); $q->clearFields(); $q->addField('DISTINCT r.resource_id', 'resource_id'); $q->addField('resource_type', 'resource_type'); $q->leftJoin("?_access_cache", "c", "\n {$joinConditions} \n (((c.fn = r.fn) AND (c.id = r.id)) OR (r.fn='product_category_id' AND r.id=-1)) \n AND (\n (c.status='active' AND r.start_days IS NULL AND r.stop_days IS NULL) \n OR\n (c.status='active' AND c.days BETWEEN IFNULL(r.start_days,0) AND IFNULL(r.stop_days, 90000)) \n OR\n (c.days >= IFNULL(r.start_days,0) AND r.stop_days = -1) \n )"); // is available if free, or if user has equal subscription record in access_cache $q->addWhere("(r.fn='free' OR c.user_id IS NOT NULL)"); return $q; }
public function createMailQueue() { $ds = new Am_Query($this->getDi()->mailQueueTable); $ds->clearFields(); $ds->addField('recipients')->addField('added')->addField('sent')->addField('subject')->addField('queue_id'); $ds->setOrder('added', true); $g = new Am_Grid_Editable('_mail', ___("E-Mail Queue"), $ds, $this->getRequest(), $this->view); $g->setPermissionId(Am_Auth_Admin::PERM_LOGS_MAIL); $g->addField(new Am_Grid_Field('recipients', ___('Recipients'), true, '', null, '20%')); $g->addField(new Am_Grid_Field_Date('added', ___('Added'), true)); $g->addField(new Am_Grid_Field_Date('sent', ___('Sent'), true)); $g->addField(new Am_Grid_Field('subject', ___('Subject'), true, '', null, '30%'))->setRenderFunction(array($this, 'renderSubject')); $g->addField(new Am_Grid_Field_Expandable('queue_id', ___('Mail'), false, '', null, '20%'))->setAjax(REL_ROOT_URL . '/admin-logs/get-mail?id={queue_id}'); $g->setFilter(new Am_Grid_Filter_Text(___("Filter by subject or recipient"), array('subject' => 'LIKE', 'recipients' => 'LIKE'))); $g->actionsClear(); $g->actionAdd(new Am_Grid_Action_MailRetry('retry')); if ($this->getDi()->authAdmin->getUser()->isSuper()) { $g->actionAdd(new Am_Grid_Action_Delete()); $g->actionAdd(new Am_Grid_Action_Group_Delete()); } return $g; }
function applySimpleSearch() { $search = array(); foreach ($this->options as $string) { @(list($k, $id) = explode('-', $string, 2)); $search[$k][] = $id == 'all' ? 'all' : intval($id); } $queries = array(); // union these queries with $this->query $this->queryDescription = array(); foreach ($search as $k => $items) { if ($k == 'all') { $q = new Am_Query_User(); $q->addWhere("IFNULL(u.unsubscribed,0)=0"); $queries = array($q); $this->queryDescription = array(___("All Users")); break; } switch ($k) { case 'aff': $q = new Am_Query_User(); $q->addWhere("IFNULL(u.unsubscribed,0)=0"); $q->addWhere("is_affiliate>0"); $queries[] = $q; $this->queryDescription[] = ___("All Affiliates"); break; case 'active': case 'expired': $q = new Am_Query_User(); $q->addWhere("IFNULL(u.unsubscribed,0)=0"); $product_ids = in_array('all', $items) ? null : $items; $q->add(new Am_Query_User_Condition_HaveSubscriptionTo($product_ids, $k == 'expired' ? User::STATUS_EXPIRED : User::STATUS_ACTIVE)); $queries[] = $q; $this->queryDescription[] = ($k == 'expired' ? ___("Expired Users") : ___("Active Users")) . ($product_ids ? " " . ___("of products") . " " . join(",", $product_ids) : null); break; case 'newsletter': if (Am_Di::getInstance()->modules->isEnabled('newsletter')) { $q = new Am_Query_User(); $q->addWhere("IFNULL(u.unsubscribed,0)=0"); $q->add(new Am_Query_User_Condition_SubscribedToNewsletter($items)); $queries[] = $q; $this->queryDescription[] = ___("Users subscribed to Newsletter Threads #") . join(',', $items); } break; } } if (@$search['guest'] || @$search['newsletter']) { if (Am_Di::getInstance()->modules->isEnabled('newsletter')) { $q = new Am_Query(new NewsletterGuestTable(), 'g'); if ($queries) { $fields = Am_Di::getInstance()->userTable->getFields(true); $q->clearFields(); $guestFields = array('name_f', 'name_l', 'email'); foreach ($fields as $k) { $q->addField(in_array($k, $guestFields) ? $k : '(NULL)', $k); } } $this->queryDescriptionGuest = ___("All Guests"); if (!@$search['guest'] && @$search['newsletter']) { $ids = join(',', $search['newsletter']); $q->innerJoin('?_newsletter_guest_subscription', 'gs', "gs.guest_id=g.guest_id AND list_id IN ({$ids})"); $this->queryDescriptionGuest = ___("Guests having subscription to newsletters %s", $ids); } $queries[] = $q; } } if ($queries) { $this->query = array_shift($queries); foreach ($queries as $q) { $this->query->addUnion($q); } } else { $this->query->addWhere('0=1'); } $this->queryDescription = join(' ,also ', $this->queryDescription); }
/** * 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; }
/** @return Am_Query */ public function getQuery() { $q = new Am_Query($this->getDi()->fileDownloadTable, 'fd'); $q->clearFields(); foreach ($this->getFiles() as $k => $v) { $q->addField(sprintf('SUM(IF(file_id=%d,1,0))', $k), 'cnt_' . $k); } return $q; }