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()); }
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 payoutAction() { $query = new Am_Query($this->getDi()->affPayoutDetailTable); $query->leftJoin('?_aff_payout', 'p', 'p.payout_id=t.payout_id'); $query->addField('p.*')->addWhere('aff_id=?', $this->user->pk()); $this->view->payouts = $query->selectAllRecords(); $this->view->display('aff/payout.phtml'); }
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; }
/** * @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; }
function historyRowsAction() { $q = new Am_Query($this->getDi()->emailSentTable); $q->leftJoin('?_admin', 'a', 't.admin_id=a.admin_id'); $q->addField('a.login', 'admin_login'); $q->setOrder('email_sent_id', 'DESC'); // dirty hack $withWrap = (bool) $this->_request->get('_h_wrap'); unset($_GET['_h_wrap']); $grid = new Am_Grid_Editable('_h', ___('E-Mails History'), $q, $this->_request, $this->view); $grid->setPermissionId(Am_Auth_Admin::PERM_EMAIL); if ($withWrap) { $grid->isAjax(false); } $grid->setCountPerPage(5); $grid->addField(new Am_Grid_Field_Date('tm_added', ___('Started'))); $grid->addField('subject', ___('Subject')); $grid->addField('admin_login', ___('Sender')); $grid->addField('count_users', ___('Total')); $grid->addField('sent_users', ___('Sent')); $grid->addField('desc_users', ___('To'))->setAttrs(array('class' => 'break')); $grid->actionsClear(); $grid->actionAdd(new Am_Grid_Action_Url('resend', ___('Resend'), REL_ROOT_URL . "/admin-email?resend_id=__ID__"))->setTarget('_top'); $grid->actionAdd(new Am_Grid_Action_Url('continue', ___('Continue'), REL_ROOT_URL . "/admin-email/send?queue_id=__ID__"))->setTarget('_top')->setIsAvailableCallback(array($this, 'needContinueLink')); if ($this->getDi()->authAdmin->getUser()->isSuper()) { $grid->actionAdd(new Am_Grid_Action_Delete()); } $grid->run($this->getResponse()); }
function viewAction() { Am_Aff_PayoutMethod::static_addFields(); // 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->setEventId('gridAffPayoutDetail'); $grid->setPermissionId(Bootstrap_Aff::ADMIN_PERM_ID); $grid->addCallback(Am_Grid_Editable::CB_RENDER_TABLE, array($this, 'addBackLink')); $userUrl = new Am_View_Helper_UserUrl(); $grid->addField('email', ___('E-Mail'))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top')); $grid->addField('name_f', ___('First Name')); $grid->addField('name_l', ___('Last Name')); $grid->addField(new Am_Grid_Field_Enum('type', ___('Payout Method')))->setTranslations(Am_Aff_PayoutMethod::getAvailableOptions()); $grid->addField('amount', ___('Amount'))->setGetFunction(array($this, 'getAmount')); // $grid->addField('receipt_id', ___('Receipt Id')); $grid->addField(new Am_Grid_Field_Enum('is_paid', ___('Is Paid?')))->setTranslations(array(0 => ___('No'), 1 => ___('Yes'))); $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->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'detailCbGetTrAttribs')); $grid->runWithLayout(); }
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); }
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'); }
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); }
function commTabAction() { $hasTiers = $this->getDi()->affCommissionRuleTable->getMaxTier(); $this->setActiveMenu('users-browse'); $ds = new Am_Query($this->getDi()->affCommissionTable); $ds->leftJoin('?_invoice', 'i', 'i.invoice_id=t.invoice_id'); $ds->addField('i.public_id'); $ds->leftJoin('?_user', 'u', 'u.user_id=i.user_id'); $ds->leftJoin('?_product', 'p', 't.product_id=p.product_id')->leftJoin('?_aff_payout_detail', 'apd', 't.payout_detail_id=apd.payout_detail_id')->leftJoin('?_aff_payout', 'ap', 'ap.payout_id=apd.payout_id')->addField('ap.date', 'payout_date')->addField('ap.payout_id')->addField('u.user_id', 'user_id')->addField('CONCAT(u.login, \' (\',u.name_f, \' \',u.name_l,\') #\', u.user_id)', 'user_name')->addField('p.title', 'product_title'); $ds->setOrder('date', 'desc')->addWhere('t.aff_id=?', $this->getParam('user_id')); $grid = new Am_Grid_Editable('_affcomm', ___('Affiliate Commission'), $ds, $this->_request, $this->view); $grid->setPermissionId(Bootstrap_Aff::ADMIN_PERM_ID); $grid->actionsClear(); $userUrl = new Am_View_Helper_UserUrl(); $grid->addField(new Am_Grid_Field_Date('date', ___('Date')))->setFormatDate(); $grid->addField('user_name', ___('User'))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top')); $grid->addField('product_title', ___('Product')); $grid->addField('invoice_id', ___('Invoice'))->setGetFunction(array($this, '_getInvoiceNum'))->addDecorator(new Am_Grid_Field_Decorator_Link('admin-user-payments/index/user_id/{user_id}#invoice-{invoice_id}', '_top')); $fieldAmount = $grid->addField('amount', ___('Commission'))->setRenderFunction(array($this, 'renderCommAmount')); $grid->addField('payout_date', ___('Payout'))->setRenderFunction(array($this, 'renderPayout')); if ($hasTiers) { $grid->addField('tier', ___('Tier'))->setRenderFunction(array($this, 'renderTier')); } $grid->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'commCbGetTrAttribs')); $grid->setFilter(new Am_Grid_Filter_AffCommission()); $grid->actionAdd(new Am_Grid_Action_Total())->addField($fieldAmount, "IF(record_type='void', -1*t.%1\$s, t.%1\$s)"); $grid->actionAdd(new Am_Grid_Action_Aff_Void()); $grid->runWithLayout('admin/user-layout.phtml'); }
protected function createDS() { $query = new Am_Query(Am_Di::getInstance()->helpdeskTicketTable); $query->addField('COUNT(msg.message_id) AS msg_cnt')->addField('m.login AS m_login')->addField('m.name_f AS m_name_f')->addField('m.name_l AS m_name_l')->addField('m.email AS m_email')->leftJoin('?_helpdesk_message', 'msg', 'msg.ticket_id=t.ticket_id')->addWhere('msg.type=?', 'message')->leftJoin('?_user', 'm', 't.user_id=m.user_id')->leftJoin('?_helpdesk_category', 'c', 't.category_id=c.category_id')->addField('c.title', 'c_title')->addOrder('updated', true); return $query; }
function payoutDetailAction() { $ds = new Am_Query($this->getDi()->affCommissionTable); $ds->leftJoin('?_invoice', 'i', 'i.invoice_id=t.invoice_id'); $ds->leftJoin('?_user', 'u', 'u.user_id=i.user_id'); $ds->leftJoin('?_product', 'p', 't.product_id=p.product_id'); $ds->addField('u.user_id', 'user_id')->addField('CONCAT(u.login, \' (\',u.name_f, \' \',u.name_l,\') [#\', u.user_id, \'] \')', 'user_name')->addField('u.email', 'user_email')->addField('p.title', 'product_title'); $ds->addWhere('t.aff_id=?', $this->user_id); $ds->addWhere('payout_detail_id=?', $this->getParam('payout_detail_id')); $ds->setOrder('commission_id', 'desc'); $grid = new Am_Grid_ReadOnly('_affcomm', ___('Affiliate Commission'), $ds, $this->_request, $this->view); $grid->setPermissionId(Bootstrap_Aff::ADMIN_PERM_ID); $grid->setCountPerPage(10); $userUrl = new Am_View_Helper_UserUrl(); $grid->addField(new Am_Grid_Field_Date('date', ___('Date')))->setFormatDate(); $grid->addField('user_name', ___('User'))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top')); $grid->addField('product_title', ___('Product')); $grid->addField('record_type', ___('Type'))->setRenderFunction(array($this, 'renderType')); $fieldAmount = $grid->addField('amount', ___('Commission'))->setRenderFunction(array($this, 'renderCommAmount')); $grid->addField('tier', ___('Tier'))->setRenderFunction(array($this, 'renderTier')); $grid->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'commCbGetTrAttribs')); $grid->runWithLayout('admin/user-layout.phtml'); }
function createInvoicesPage($page) { $query = new Am_Query($this->getDi()->invoiceTable); if ($page == 'not-approved') { $query->addWhere('is_confirmed<1'); } $query->leftJoin('?_user', 'm', 'm.user_id=t.user_id')->addField("(SELECT GROUP_CONCAT(item_title SEPARATOR ', ') FROM ?_invoice_item WHERE invoice_id=t.invoice_id)", 'items')->addField('m.login', 'login')->addField('m.email', 'email')->addField('m.street', 'street')->addField('m.street2', 'street2')->addField('m.city', 'city')->addField('m.state', 'state')->addField('m.country', 'country')->addField('m.phone', 'phone')->addField('m.zip', 'zip')->addField("concat(m.name_f,' ',m.name_l)", 'name')->addField('m.name_f')->addField('m.name_l')->addField('DATE(tm_started)', 'date'); //Additional Fields foreach ($this->getDi()->userTable->customFields()->getAll() as $field) { if (isset($field->from_config) && $field->from_config) { if ($field->sql) { $query->addField('m.' . $field->name, $field->name); } } } $query->setOrder("invoice_id", "desc"); $grid = new Am_Grid_Editable('_invoice', ___('Invoices'), $query, $this->_request, $this->view); $grid->setRecordTitle(array($this, 'getInvoiceRecordTitle')); $grid->actionsClear(); $grid->actionAdd(new Am_Grid_Action_Delete())->setTarget('_top'); $grid->addField(new Am_Grid_Field_Date('tm_added', ___('Added'))); $grid->addField('invoice_id', ___('Invoice'))->setGetFunction(array($this, '_getInvoiceNum'))->addDecorator(new Am_Grid_Field_Decorator_Link('admin-user-payments/index/user_id/{user_id}#invoice-{invoice_id}', '_top')); $grid->addField('status', ___('Status'))->setRenderFunction(array($this, 'renderInvoiceStatus')); $grid->addField('paysys_id', ___('Payment System')); $grid->addField('_total', ___('Billing Terms'), false)->setGetFunction(array($this, 'getInvoiceTotal')); $grid->addField(new Am_Grid_Field_Date('rebill_date', ___('Rebill Date')))->setFormatDate(); $grid->addField('items', ___('Items')); $grid->addField('login', ___('Username'))->addDecorator(new Am_Grid_Field_Decorator_Link('admin-users?_u_a=edit&_u_b={THIS_URL}&_u_id={user_id}', '_top')); $grid->addField('name', ___('Name')); $filter = new Am_Grid_Filter_Invoices(); $grid->setFilter($filter); $stateTitleField = new Am_Grid_Field('state_title', ___('State Title')); $stateTitleField->setGetFunction(array($this, 'getStateTitle')); $countryTitleField = new Am_Grid_Field('country_title', ___('Country Title')); $countryTitleField->setGetFunction(array($this, 'getCountryTitle')); $termsField = new Am_Grid_Field('_total', ___('Billing Terms')); $termsField->setGetFunction(array($this, 'getInvoiceTotal')); $action = new Am_Grid_Action_Export(); $action->addField(new Am_Grid_Field('tm_started', ___('Date/Time')))->addField(new Am_Grid_Field('date', ___('Date')))->addField(new Am_Grid_Field('rebill_date', ___('Rebill Date')))->addField(new Am_Grid_Field('invoice_id', ___('Invoice (Internal Id)')))->addField(new Am_Grid_Field('public_id', ___('Invoice (Public Id)')))->addField(new Am_Grid_Field('status', ___('Status')))->addField($termsField)->addField(new Am_Grid_Field('paysys_id', ___('Payment System')))->addField(new Am_Grid_Field('first_total', ___('First Total')))->addField(new Am_Grid_Field('first_tax', ___('First Tax')))->addField(new Am_Grid_Field('email', ___('Email')))->addField(new Am_Grid_Field('login', ___('Username')))->addField(new Am_Grid_Field('name', ___('Name')))->addField(new Am_Grid_Field('name_f', ___('First Name')))->addField(new Am_Grid_Field('name_l', ___('Last Name')))->addField(new Am_Grid_Field('street', ___('Street')))->addField(new Am_Grid_Field('street2', ___('Street2')))->addField(new Am_Grid_Field('city', ___('City')))->addField(new Am_Grid_Field('state', ___('State')))->addField($stateTitleField)->addField(new Am_Grid_Field('country', ___('Country')))->addField($countryTitleField)->addField(new Am_Grid_Field('phone', ___('Phone')))->addField(new Am_Grid_Field('zip', ___('Zip Code')))->addField(new Am_Grid_Field('item_title', ___('Product Title')))->addField(new Am_Grid_Field('coupon_code', ___('Coupon'))); //Additional Fields foreach ($this->getDi()->userTable->customFields()->getAll() as $field) { if (isset($field->from_config) && $field->from_config) { if ($field->sql) { $action->addField(new Am_Grid_Field($field->name, $field->title)); } } } $action->setGetDataSourceFunc(array($this, 'getExportDs')); $grid->actionAdd($action); if ($this->getDi()->config->get('manually_approve_invoice')) { $grid->actionAdd(new Am_Grid_Action_Group_Callback('approve', ___("Approve"), array($this, 'approveInvoice'))); } return $grid; }
function addDefaultSort(Am_Query $q) { $a = $q->getAlias(); $type = $this->createRecord()->getAccessType(); $q->leftJoin('?_resource_access_sort', "ras", "{$a}.{$this->_key} = ras.resource_id AND ras.resource_type='{$type}'"); $q->addField('ras.sort_order', '_sort_order'); $q->setOrder('_sort_order'); }
protected function createDetailGrid($date) { // public $textNoRecordsFound = "No rebills today - most possible cron job was not running."; $q = new Am_Query($this->getDi()->ccRebillTable); $q->addWhere('t.rebill_date=?', $date); $q->leftJoin('?_invoice', 'i', 'i.invoice_id=t.invoice_id'); $q->addField('i.public_id', 'public_id'); $q->addField('i.user_id', 'user_id'); $grid = new Am_Grid_ReadOnly('_r_d', ___('Detailed Rebill Report for %s', amDate($date)), $q, $this->_request, $this->view); $grid->setPermissionId('cc'); $grid->addField(new Am_Grid_Field_Date('tm_added', 'Started', true)); $grid->addField(new Am_Grid_Field('invoice_id', 'Invoice#', true, '', array($this, 'renderInvoiceLink'))); $grid->addField(new Am_Grid_Field_Date('rebill_date', 'Date', true))->setFormatDate(); $grid->addField('status', 'Status', true)->setFormatFunction(array('CcRebill', 'getStatusText')); $grid->addField('status_msg', 'Message'); $grid->setCountPerPage(10); return $grid; }