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');
}
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');
}
public function clickAction()
{
$ds = new Am_Query($this->getDi()->notificationClickTable);
$ds->leftJoin('?_user', 'u', 't.user_id=u.user_id')->addField('COUNT(t.notification_click_id)', 'cnt')->addField('u.*')->addField("CONCAT(u.name_f, ' ', u.name_l)", 'name')->groupBy('user_id', 'u')->addWhere('notification_id=?', $this->getParam('id'));
$grid = new Am_Grid_ReadOnly('_n_c', ___('Statistics'), $ds, $this->getRequest(), $this->getView(), $this->getDi());
$userUrl = new Am_View_Helper_UserUrl();
$grid->addField('login', ___('Username'))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top'));
$grid->addField('name', ___('Name'));
$grid->addField('email', ___('E-Mail Address'));
$grid->addField('cnt', ___('Clicks'), null, null, null, '1%');
$grid->runWithLayout($this->layout);
}
public function keywordsAction()
{
$uid = $this->getDi()->auth->getUserId();
$db = $this->getDi()->db;
$db->query('DROP TEMPORARY TABLE IF EXISTS ?_aff_keywords_tmp');
$db->query("CREATE TEMPORARY TABLE ?_aff_keywords_tmp (\n keyword_id int not null DEFAULT 0,\n keyword varchar(64) not null DEFAULT '',\n clicks_count int not null DEFAULT 0,\n leads_count int not null DEFAULT 0,\n sales_count int not null DEFAULT 0,\n sales_amount int not null DEFAULT 0,\n PRIMARY KEY (`keyword_id`)\n )");
//clicks
$db->query("INSERT into ?_aff_keywords_tmp (keyword_id, clicks_count) \n SELECT keyword_id, count(*) from ?_aff_click where keyword_id is not null and aff_id = ? group by keyword_id", $uid);
//leads
$db->query("INSERT into ?_aff_keywords_tmp (keyword_id, leads_count) \n SELECT keyword_id, cnt from (SELECT keyword_id, count(*) as cnt from ?_aff_lead \n where keyword_id is not null and aff_id = ? group by keyword_id) s\n ON DUPLICATE KEY UPDATE leads_count = s.cnt", $uid);
//sales_count
$db->query("INSERT into ?_aff_keywords_tmp (keyword_id, sales_count) \n SELECT keyword_id, cnt from (SELECT keyword_id, sum(if(record_type='commission', 1, 0)) as cnt from ?_aff_commission \n where keyword_id is not null and aff_id = ? group by keyword_id) s\n ON DUPLICATE KEY UPDATE sales_count = s.cnt", $uid);
//sales_amount
$db->query("INSERT into ?_aff_keywords_tmp (keyword_id, sales_amount) \n SELECT keyword_id, cnt from (SELECT keyword_id, sum(if(record_type='commission', amount, -amount)) as cnt from ?_aff_commission \n where keyword_id is not null and aff_id = ? group by keyword_id) s\n ON DUPLICATE KEY UPDATE sales_amount = s.cnt", $uid);
//keyword
$db->query("UPDATE ?_aff_keywords_tmp t set keyword = (SELECT value from ?_aff_keyword s where s.keyword_id = t.keyword_id)");
$ds = new Am_Query(new Am_Table($db, '?_aff_keywords_tmp', 't'));
$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');
}
