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