public function getrecurring_next_formatted()
{
return $this->recurring_next ? HelperFormatted::date($this->recurring_next, HelperFormatted::TIME_FORMAT_DATE) : '';
}
public function getForGraph()
{
$searchFields = $this->searchFields;
if (!$searchFields['initial_dates']) {
return array();
}
$billingCycle = (int) $searchFields['billing_cycle'];
if ($billingCycle < 1 || $billingCycle > 2) {
$billingCycle = 1;
}
$initialDates = explode('-', $searchFields['initial_dates']);
$dateStartT = explode('.', $initialDates[0]);
$dateStart = array_reverse($dateStartT);
$dateStart = implode('-', $dateStart);
$dateFinishT = explode('.', $initialDates[1]);
$dateFinish = array_reverse($dateFinishT);
$dateFinish = implode('-', $dateFinish);
$datetime1 = new DateTime($dateStart);
$datetime2 = new DateTime($dateFinish);
$interval = $datetime1->diff($datetime2);
$int = $interval->days;
$dates = array();
if ($int < 10) {
for ($i = 0; $i <= $int; $i++) {
$dates[] = $datetime1->format('Y-m-d');
$datetime1->add(new DateInterval('P1D'));
}
} else {
$maxSelect = 10;
$max = ceil($int / $maxSelect);
$min = floor($interval->days / 10);
$dif = $min == $max ? 1 : $min - $max;
$x = ($int - $max * $maxSelect) / $dif;
$y = ($int - $min * $x) / $max;
while ($datetime1->getTimestamp() <= $datetime2->getTimestamp()) {
$dates[] = $datetime1->format('Y-m-d');
if ($x) {
$step = $min;
$x--;
} elseif ($y) {
$step = $max;
$y--;
}
$datetime1->add(new DateInterval('P' . $step . 'D'));
}
}
$db = self::$_msql;
$this->whereAndLimit();
$tempReport = array();
$campaignsName = array();
foreach ($dates as $date) {
$sql = "SELECT `campaign_id`, 'ALL' AS `aff_id`, `col_name`, SUM(`data`) AS `data`, `campaign_name`\n FROM (\n SELECT `date`, `campaign_id`, `aff_id`, `col_name`, `data`, `campaign_name`\n FROM (\n SELECT rh.*, c.`campaign_name`\n FROM `retention_history` as rh\n LEFT JOIN `campaigns` as c USING(`campaign_id`)\n WHERE ?p\n AND rh.`id_date` <= ?s\n AND rh.`col_name` IN ('c{$billingCycle}_net_revenue', 'c{$billingCycle}_potential_revenue')\n ORDER BY rh.`id_date` DESC\n ) `t`\n GROUP BY `date`, `campaign_id`, `aff_id`, `col_name`\n ) `t2`\n GROUP BY `campaign_id`, `col_name`\n ORDER BY `campaign_id`";
$r = $db->getAll($sql, $this->where, $date);
$tArrayByCampaign = array();
foreach ($r as $item) {
if (!isset($campaignsName[$item['campaign_id']])) {
$campaignsName[$item['campaign_id']] = $item['campaign_name'];
}
if (!isset($tArrayByCampaign[$item['campaign_id']])) {
$tArrayByCampaign[$item['campaign_id']] = array();
}
$colNameArray = explode('_', $item['col_name'], 2);
$tArrayByCampaign[$item['campaign_id']][$colNameArray[1]] = $item['data'];
}
foreach ($tArrayByCampaign as $k => $v) {
if (!isset($tempReport[$k])) {
$tempReport[$k] = array();
}
$tempReport[$k][$date] = isset($v['potential_revenue']) && $v['potential_revenue'] && isset($v['net_revenue']) ? round($v['net_revenue'] / $v['potential_revenue'], 4) * 100 : null;
}
unset($tArrayByCampaign, $k, $v, $date, $colNameArray, $item, $r);
}
if (!$tempReport) {
return array();
}
$campaigns = array_keys($campaignsName);
$report = array(0 => array('Dates'));
foreach ($campaignsName as $k => $v) {
$tName = '[' . $k . '] ';
$tName .= $v ? $v : __('no_name');
array_push($report[0], $tName);
}
$i = 1;
foreach ($dates as $date) {
$dateFormatted = HelperFormatted::date($date, HelperFormatted::TIME_FORMAT_DATE);
$report[$i] = array($dateFormatted);
foreach ($campaigns as $campaignID) {
array_push($report[$i], isset($tempReport[$campaignID][$date]) ? $tempReport[$campaignID][$date] : 0);
}
$i++;
}
return $report;
}
public function getcreated_formatted()
{
return $this->created ? HelperFormatted::date($this->created) : null;
}
public function getcreated_formatted()
{
return HelperFormatted::date($this->created);
}
private static function clearDateArray($FilterFields, $dataArray)
{
foreach ($FilterFields as $k => $v) {
if (!in_array($k, $dataArray)) {
continue;
}
$dArray = explode('-', $v);
if (isset($dArray[0]) && isset($dArray[1])) {
$d1 = HelperFormatted::date($dArray[0], HelperFormatted::TIME_FORMAT_DATE);
$d2 = HelperFormatted::date($dArray[1], HelperFormatted::TIME_FORMAT_DATE);
if ($d1 && $d2) {
$FilterFields[$k . '_post'] = $FilterFields[$k];
$FilterFields[$k] = $d1 . '-' . $d2;
}
}
}
return $FilterFields;
}
function newretentionAction()
{
$clearArray = array('campaign_id', 'detail_dates', 'detail_affiliates', 'detail_sid', 'simple', 'currency_id', 'country_id');
$this->filter($clearArray);
$filterFields = $this->params;
//AFActiveDataProvider::clearDateArray($this->params, array('r_dates'));
if (!isset($filterFields['circle']) || isset($filterFields['circle']) && !$filterFields['circle']) {
$filterFields['circle'] = 0;
}
if ($clearArray) {
foreach ($clearArray as $value) {
if (isset($filterFields[$value])) {
$filterFields[$value] = explode(',', $filterFields[$value]);
}
}
}
// build filter select datasources
$currencies = Currency::model()->cache()->findAllInArray();
$countries = Country::model()->cache()->findAllInArray();
$campaigns = isset($filterFields['campaign_id']) ? $filterFields['campaign_id'] : null;
$country_id = isset($filterFields['country_id']) ? $filterFields['country_id'] : null;
$currency_id = isset($filterFields['currency_id']) ? $filterFields['currency_id'] : null;
$groupDate = isset($filterFields['detail_dates']) ? false : true;
$groupAfid = isset($filterFields['detail_affiliates']) ? false : true;
$groupSid = isset($filterFields['detail_sid']) ? false : true;
$msql = SafeMySQL::getInstance();
$sql = 'SELECT campaign_id, campaign_name
FROM `campaigns`
ORDER BY `campaign_id`';
$campaignsFilterTemp = $msql->getAll($sql);
$campaignsFilter = array();
foreach ($campaignsFilterTemp as $v) {
$campaignsFilter[$v['campaign_id']] = $v;
}
unset($campaignsFilterTemp);
$sql = 'SELECT aff_id, aff_name
FROM `affiliates`
ORDER BY `aff_id`';
$affidsTemp = $msql->getAll($sql);
$affids = array();
foreach ($affidsTemp as $v) {
$affids[$v['aff_id']] = $v;
}
unset($affidsTemp);
$where = '';
if ($campaigns) {
$where .= $msql->parse(" AND `campaign_id` IN (?a)", $campaigns);
}
if ($currency_id) {
$where .= $msql->parse(" AND `campaign_id` IN ( select campaign_id from campaigns where currency_id in (?a))", $currency_id);
}
if ($country_id) {
$where .= $msql->parse(" AND `campaign_id` IN ( select campaign_id from campaigns where country_id in (?a))", $country_id);
}
if (!isset($filterFields['r_dates'])) {
$filterFields['r_dates'] = date('d.m.Y-d.m.Y');
}
$dates = explode('-', $filterFields['r_dates']);
if (isset($dates[0]) && isset($dates[1])) {
$dateStartT = explode('.', $dates[0]);
$dateStart = array_reverse($dateStartT);
$dateStart = implode('-', $dateStart);
$dateFinishT = explode('.', $dates[1]);
$dateFinish = array_reverse($dateFinishT);
$dateFinish = implode('-', $dateFinish);
$where .= $msql->parse(" AND DATE(`date`) BETWEEN ?s AND ?s", $dateStart, $dateFinish);
unset($dateStartT, $dateFinishT);
}
if (!isset($filterFields['report_date'])) {
$filterFields['report_date'] = date('d.m.Y');
}
$reportDateArray = explode('.', $filterFields['report_date']);
if (isset($reportDateArray[0]) && isset($reportDateArray[1]) && isset($reportDateArray[2])) {
$reportDate = array_reverse($reportDateArray);
$reportDate = implode('-', $reportDate);
$where .= $msql->parse("AND `id_date` <= ?s", $reportDate);
unset($reportDate, $reportDateArray);
}
$where .= $msql->parse("AND CAST(SUBSTR(`col_name`,2,1) AS UNSIGNED) <= ?i", (int) $filterFields['circle']);
$sql = "\n SELECT " . ($groupDate ? "'ALL' AS " : '') . "`date`, `campaign_id`, " . ($groupAfid ? "'ALL' AS " : '') . "`aff_id`, " . ($groupSid ? "'ALL' AS " : '') . "`sid`, `col_name`, SUM(`data`) AS `data`, `currency_id`\n FROM (\n SELECT `date`, `campaign_id`, `aff_id`, `sid`, `col_name`, `data`, `currency_id`\n FROM (\n SELECT `retention_history`.*, `campaigns`.`currency_id`\n FROM `retention_history`\n LEFT JOIN `campaigns` USING(`campaign_id`)\n WHERE 1 " . $where . "\n ORDER BY `id_date` DESC\n ) `t`\n GROUP BY `date`, `campaign_id`, `aff_id`, `col_name`\n ) `t2`\n GROUP BY " . ($groupDate ? '' : "`date`, ") . "`campaign_id`, " . ($groupAfid ? '' : "`aff_id`, ") . "`col_name`\n ";
$result = $msql->getAll($sql);
$report = array();
$reportCur = array();
$countR = 0;
foreach ($result as $row) {
if (isset($row['col_name'])) {
$colNameArr = explode('_', $row['col_name']);
if (isset($colNameArr[0])) {
$countCycles = (int) substr($colNameArr[0], 1, 1);
if ($countCycles > $countR) {
$countR = $countCycles;
}
}
}
if (!isset($report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']]['c' . ($row['col_name'][1] + 1) . '_gross'])) {
if (!isset($report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']])) {
$report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] = array();
}
$report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] += self::cycle_array($row['col_name'][1]);
}
$report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']][$row['col_name']] = $row['data'];
if (!isset($reportCur[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']])) {
$reportCur[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] = $row['currency_id'];
}
}
$countR++;
unset($result);
foreach ($report as $row => $data) {
for ($i = 0; isset($data['c' . ($i + 1) . '_gross']); $i++) {
$report[$row]['c' . $i . '_gross'] = (int) $report[$row]['c' . $i . '_gross'];
$report[$row]['c' . $i . '_declined'] = (int) $report[$row]['c' . $i . '_declined'];
$report[$row]['c' . $i . '_void'] = (int) $report[$row]['c' . $i . '_void'];
$report[$row]['c' . $i . '_pending'] = (int) $report[$row]['c' . $i . '_pending'];
$report[$row]['c' . $i . '_rma'] = (int) $report[$row]['c' . $i . '_rma'];
$report[$row]['c' . $i . '_partial_refund'] = (int) $report[$row]['c' . $i . '_partial_refund'];
$report[$row]['c' . $i . '_chargeback'] = (int) $report[$row]['c' . $i . '_chargeback'];
if ($i) {
$report[$row]['c' . $i . '_discounts'] = (int) $report[$row]['c' . $i . '_discounts'];
}
$report[$row]['c' . $i . '_decline_rate'] = $report[$row]['c' . $i . '_gross'] ? round($report[$row]['c' . $i . '_declined'] / $report[$row]['c' . $i . '_gross'], 4) : NULL;
$report[$row]['c' . $i . '_paid'] = $report[$row]['c' . $i . '_gross'] - $report[$row]['c' . $i . '_declined'];
$report[$row]['c' . $i . '_void_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_void'] / $report[$row]['c' . $i . '_paid'], 4) : NULL;
$report[$row]['c' . $i . '_net_approved'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_void'];
$report[$row]['c' . $i . '_approval_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_net_approved'] / $report[$row]['c' . $i . '_paid'], 4) : NULL;
$report[$row]['c' . $i . '_cancelled'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_pending'] - $report[$row]['c' . ($i + 1) . '_gross'];
$report[$row]['c' . $i . '_cancel_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_cancelled'] / $report[$row]['c' . $i . '_paid'], 4) : NULL;
$report[$row]['c' . $i . '_subscriptions_approved'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_cancelled'];
$report[$row]['c' . $i . '_rma_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_rma'] / $report[$row]['c' . $i . '_paid'], 4) : NULL;
// $report[$row]['c'.$i.'_pending_rebill'] = $report[$row]['c'.$i.'_pending'] - $report[$row]['c'.$i.'_rma'];
$report[$row]['c' . $i . '_chargeback_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_chargeback'] / $report[$row]['c' . $i . '_paid'], 4) : NULL;
if ($i) {
$report[$row]['c' . $i . '_retention_count_rate'] = $report[$row]['c' . ($i - 1) . '_paid'] ? round($report[$row]['c' . $i . '_paid'] / $report[$row]['c' . ($i - 1) . '_paid'], 4) : NULL;
$report[$row]['c' . $i . '_retention_rate'] = $report[$row]['c' . $i . '_potential_revenue'] ? round($report[$row]['c' . $i . '_net_revenue'] / $report[$row]['c' . $i . '_potential_revenue'], 4) : NULL;
}
}
if (isset($report[$row]['c' . $countR . '_gross']) && !$report[$row]['c' . $countR . '_gross']) {
unset($report[$row]['c' . $countR . '_gross']);
}
}
$extraFields = array(array('gross', 'declined', 'decline_rate', 'paid', 'void', 'void_rate', 'net_approved', 'approval_rate', 'cancelled', 'cancel_rate', 'subscriptions_approved', 'pending', 'rma', 'rma_rate', 'partial_refund', 'refund_amount', 'chargeback', 'chargeback_rate', 'chargeback_loss', 'net_revenue'), array('gross2', 'declined', 'decline_rate', 'paid', 'void', 'void_rate', 'net_approved', 'approval_rate', 'cancelled', 'cancel_rate', 'subscriptions_approved', 'pending', 'rma', 'rma_rate', 'discounts', 'discount_amount', 'partial_refund', 'refund_amount', 'chargeback', 'chargeback_rate', 'chargeback_loss', 'retention_count_rate', 'net_revenue', 'potential_revenue', 'retention_rate'));
$simpleReportFields = array('gross', 'gross2', 'decline_rate', 'cancel_rate', 'chargeback_rate', 'pending', 'net_revenue', 'potential_revenue', 'retention_rate');
// CSV builder
if (isset($this->params['download_csv']) && $this->params['download_csv']) {
self::retentionCsvBuilder($report, $extraFields, $campaignsFilter, $affids, $countR, $simpleReportFields, $filterFields);
die;
}
$tempArray = explode('-', $filterFields['r_dates']);
$filterFields['r_dates_post'] = $filterFields['r_dates'];
$filterFields['r_dates'] = HelperFormatted::date($tempArray[0], HelperFormatted::TIME_FORMAT_DATE) . '-' . HelperFormatted::date($tempArray[1], HelperFormatted::TIME_FORMAT_DATE);
$filterFields['report_date'] = HelperFormatted::date($filterFields['report_date'], HelperFormatted::TIME_FORMAT_DATE);
// set ajax table
if (AF::isAjaxRequestModels()) {
$this->view->includeFile('_newretantion_table', array('application', 'views', 'reports'), array('access' => $this->access, 'controller' => $this->controller, 'report' => $report, 'extraFields' => $extraFields, 'countR' => $countR, 'campaignsFilter' => $campaignsFilter, 'filterFields' => $filterFields, 'affids' => $affids, 'simpleReportFields' => $simpleReportFields, 'simple' => isset($this->params['simple']) ? (int) $this->params['simple'] : 0, 'currencies' => $currencies, 'countries' => $countries, 'reportCur' => $reportCur));
die;
}
Assets::css('jquery-ui');
Assets::js('//code.jquery.com/ui/1.10.3/jquery-ui.js');
Assets::js('dateRange/jquery.daterange');
Assets::js('jquery.form');
// for ajax submission used to build csv
Assets::js('ajax_table');
Assets::js('af_input_field');
$this->render('newretention', array('report' => $report, 'extraFields' => $extraFields, 'countR' => $countR, 'campaignsFilter' => $campaignsFilter, 'filterFields' => $filterFields, 'affids' => $affids, 'simpleReportFields' => $simpleReportFields, 'simple' => isset($this->params['simple']) ? (int) $this->params['simple'] : 0, 'currencies' => $currencies, 'countries' => $countries, 'reportCur' => $reportCur));
}
public function getupdated_formatted_date()
{
return $this->updated ? HelperFormatted::date($this->updated, HelperFormatted::TIME_FORMAT_DATE) : null;
}
