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