prepare('
(SELECT
round(sum('.$wpdb->prefix.'ap_transaction.Commission),2) as commission,
round(sum('.$wpdb->prefix.'ap_transaction.Confirmed), 2) as confirmed,
'.$wpdb->posts.'.ID AS postID,
if (postID = -1, "- - Homepage - -", ifnull('.$wpdb->posts.'.post_title, "- unknown -")) as name
FROM '.$wpdb->prefix.'ap_transaction
LEFT JOIN '.$wpdb->prefix.'ap_clickout
ON '.$wpdb->prefix.'ap_transaction.SubId = '.$wpdb->prefix.'ap_clickout.ap_clickoutID
LEFT JOIN '.$wpdb->posts.'
ON '.$wpdb->prefix.'ap_clickout.postID = '.$wpdb->prefix.'posts.ID
WHERE '.$wpdb->prefix.'ap_transaction.SubId >= 1000000
AND TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY name )
UNION
(SELECT
round(sum('.$wpdb->prefix.'ap_transaction.Commission),2) as commission,
round(sum('.$wpdb->prefix.'ap_transaction.Confirmed), 2) as confirmed,
'.$wpdb->posts.'.ID AS postID,
ifnull('.$wpdb->posts.'.post_title, "- unknown -") as name
FROM '.$wpdb->prefix.'ap_transaction
LEFT JOIN '.$wpdb->posts.'
ON '.$wpdb->prefix.'ap_transaction.SubId = '.$wpdb->prefix.'posts.ID
WHERE '.$wpdb->prefix.'ap_transaction.SubId < 1000000
AND TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY name )
ORDER BY commission DESC
LIMIT 12', $date_from_db, $date_to_db, $date_from_db, $date_to_db);
$topArticleData = $wpdb->get_results($sql, ARRAY_A);
//Top Partner
$sql = $wpdb->prepare('
SELECT concat (ProgramTitle, " (", network, ")") as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY ProgramId, network
ORDER BY sum(Commission) DESC
LIMIT 12', $date_from_db, $date_to_db);
$topPartnerData = $wpdb->get_results($sql, ARRAY_A);
//Networks
$sql = $wpdb->prepare('
SELECT network as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY network
ORDER BY sum(Commission) DESC
LIMIT 12', $date_from_db, $date_to_db);
$networkData = $wpdb->get_results($sql, ARRAY_A);
$landingData = $refererData = $keywordData = array(
array(
'name' => __('Only in the premium version', 'affiliate-power'),
'commission' => '0',
'confirmed' => '0'
)
);
//Days
$sql = $wpdb->prepare('
SELECT date_format(date, "%%d.%%m.%%Y") as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY date(date)
ORDER BY date DESC',
$date_from_db, $date_to_db);
$dayData = $wpdb->get_results($sql, ARRAY_A);
//Weeks
$sql = $wpdb->prepare('
SELECT concat ("KW ", weekofyear(date), ", ", year(date)) as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY weekofyear(date)
ORDER BY date DESC',
$date_from_db, $date_to_db);
$weekData = $wpdb->get_results($sql, ARRAY_A);
//Months
$sql = $wpdb->prepare('
SELECT concat (
date_format(date, "%%M"),
" ",
year(date))
as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY month(date), year(date)
ORDER BY year(date) DESC,
month(date) DESC',
$date_from_db, $date_to_db);;
$monthData = $wpdb->get_results($sql, ARRAY_A);
//statistics to create
$arr_statistics = array(
'posts' => $topArticleData,
'partner' => $topPartnerData,
'network' => $networkData,
'landing' => $landingData,
'referer' => $refererData,
'keyword' => $keywordData,
'day' => $dayData,
'week' => $weekData,
'month' => $monthData
);
$statisticHtml = '';
$i = 1;
foreach ($arr_statistics as $type => $statistic) {
$statisticHtml .= self::getStatisticHtml($type, $statistic);
if ($i % 3 == 0) $statisticHtml .= '
';
$i += 1;
}
echo '
';
echo '
';
_e ('
Affiliate Power Statistics
', 'affiliate-power');
//Check Licence
if (isset($options['licence-key'])) {
echo '
'.__('You entered a valid licence key but you did not download the premium version yet. Please go to the Update Page and update to the premium version. It can take up to 5 minutes until WordPress notifies you about the new version.', 'affiliate-power').'