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,
ifnull('.$wpdb->posts.'.post_title, "- unbekannt -") 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
OR '.$wpdb->prefix.'ap_transaction.SubId = '.$wpdb->prefix.'posts.ID AND '.$wpdb->prefix.'posts.ID < 1000000
WHERE TransactionStatus <> "Cancelled"
AND date BETWEEN %s and %s
GROUP BY name
ORDER BY sum('.$wpdb->prefix.'ap_transaction.Commission) DESC
LIMIT 12', $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' => 'Nur in der Premium Version',
'commission' => '0',
'confirmed' => '0'
)
);
/*
//Last Days
$sql = '
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"
GROUP BY date(date)
ORDER BY date DESC
LIMIT 12';
$dayData = $wpdb->get_results($sql, ARRAY_A);
//Last Weeks
$sql = '
SELECT concat ("KW ", weekofyear(date)) as name,
round(sum(Commission),2) as commission,
round(sum(Confirmed), 2) as confirmed
FROM '.$wpdb->prefix.'ap_transaction
WHERE TransactionStatus <> "Cancelled"
GROUP BY weekofyear(date)
ORDER BY date DESC
LIMIT 12';
$weekData = $wpdb->get_results($sql, ARRAY_A);
//Last Months
$sql = '
SELECT concat (monthname(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"
GROUP BY month(date), year(date)
ORDER BY year(date) DESC,
month(date) DESC
LIMIT 12';
$monthData = $wpdb->get_results($sql, ARRAY_A);
*/
//statistics to create
$arr_statistics = array(
'Artikel' => $topArticleData,
'Partner' => $topPartnerData,
'Netzwerk' => $networkData,
'Einstiegsseite' => $landingData,
'Besucherquelle' => $refererData,
'Keyword' => $keywordData
);
$statisticHtml = '';
$i = 1;
foreach ($arr_statistics as $headline => $statistic) {
$statisticHtml .= self::getStatisticHtml($headline, $statistic);
if ($i % 3 == 0) $statisticHtml .= '
';
$i += 1;
}
echo 'Statistiken
';
echo '';
echo $statisticHtml;
}
function getStatisticHtml($headline, $statistic) {
$html = '
'.$headline.'
| '.$headline.' |
Einnahmen |
| '.$headline.' |
Einnahmen |
';
foreach ($statistic as $row) {
$total_earning = number_format($row['commission'], 2, ',', '.');
$confirmed_earning = number_format($row['confirmed'], 2, ',', '.');
$output_earnings = $total_earning . ' € ('.$confirmed_earning.' €)';
$html .= '| '.$row['name'].' | '.$output_earnings.' |
';
}
$html .= '
';
return $html;
}
}
?>