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 $query; */ function getMainDataSource() { $ds = new Am_Query($this->getDi()->invoicePaymentTable); $ds->leftJoin('?_user', 'u', 't.user_id = u.user_id')->leftJoin('?_data', 'user_country', 't.invoice_id = user_country.id and `table` = "invoice" and `key`="tax_user_country"')->leftJoin('?_invoice_refund', 'refund', 't.invoice_payment_id = refund.invoice_payment_id')->leftJoin('?_invoice', 'invoice', 't.invoice_id = invoice.invoice_id')->addField('if(user_country.value is null, if(u.country is null, "", u.country), user_country.value)', 'country')->addField('invoice.tax_rate')->addField('round(sum(if(refund.amount, t.amount-refund.amount, t.amount)/t.base_currency_multi),2)', 'sales_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'tax_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount) - (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, t.amount-if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'sales_without_tax_amount')->addField('concat(if(country is null, "", country), "-", if(invoice.tax_rate is null, "", invoice.tax_rate))', 'country_rate'); $ds->groupBy('country_rate', ''); $ds->addOrder('country'); return $ds; }
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, ""); }
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); }
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'); }
/** * 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; }