function phpAds_PriorityGetHourlyProfile($days, $offset)
{
global $phpAds_config;
$profile = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
// Determine days
$offset = $offset * (60 * 60 * 24);
$begin = date('Ymd', phpAds_makeTimestamp(phpAds_CurrentDay, -1 - $offset));
$end = date('Ymd', phpAds_makeTimestamp(phpAds_CurrentDay, -(60 * 60 * 24 * $days) - $offset));
$query = "\n\t\tSELECT hour, SUM(views) AS sum_views\n\t\tFROM " . $phpAds_config['tbl_adstats'] . "\n\t\tWHERE day <= " . $begin . "\n\t\tAND day >= " . $end . "\n\t\tGROUP BY hour\n\t";
$res = phpAds_dbQuery($query);
while ($row = phpAds_dbFetchArray($res)) {
$profile[$row['hour']] = (int) $row['sum_views'];
}
return $profile;
}
function phpAds_PriorityGetHourlyProfile($days, $offset)
{
global $phpAds_config;
$profile = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
// Determine days
$offset = $offset * (60 * 60 * 24);
if ($phpAds_config['compact_stats']) {
$begin = date('Ymd', phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), -1 - $offset));
$end = date('Ymd', phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), -(60 * 60 * 24 * $days) - $offset));
$query = "\n\t\t\tSELECT hour, SUM(views) AS sum_views\n\t\t\tFROM " . $phpAds_config['tbl_adstats'] . "\n\t\t\tWHERE day <= " . $begin . "\n\t\t\tAND day >= " . $end . "\n\t\t\tGROUP BY hour\n\t\t";
} else {
$begin = date('YmdHis', phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), -1 - $offset));
$end = date('YmdHis', phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), -(60 * 60 * 24 * $days) - $offset));
$query = "\n\t\t\tSELECT HOUR(t_stamp) AS hour, COUNT(*) AS sum_views\n\t\t\tFROM " . $phpAds_config['tbl_adviews'] . "\n\t\t\tWHERE t_stamp <= " . $begin . "\n\t\t\tAND t_stamp >= " . $end . "\n\t\t\tGROUP BY hour\n\t\t";
}
$res = phpAds_dbQuery($query);
while ($row = phpAds_dbFetchArray($res)) {
$profile[$row['hour']] = (int) $row['sum_views'];
}
return $profile;
}
function phpAds_cleanTables($weeks, $stats)
{
global $phpAds_config;
$report = '';
// Determine tables
if ($stats) {
$tables = array($phpAds_config['tbl_adstats'] => array('day', 'Ymd'), $phpAds_config['tbl_adviews'] => array('t_stamp', 'YmdHis'), $phpAds_config['tbl_adclicks'] => array('t_stamp', 'YmdHis'), $phpAds_config['tbl_adconversions'] => array('t_stamp', 'YmdHis'));
} else {
$tables = array($phpAds_config['tbl_userlog'] => array('timestamp', ''));
}
$t_stamp = phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), (-7 * $weeks + 1) * 60 * 60 * 24);
while (list($k, $v) = each($tables)) {
if (!$v[1]) {
$begin = $t_stamp;
} else {
$begin = date($v[1], $t_stamp);
}
phpAds_dbQuery("\n\t\t\tDELETE FROM\n\t\t\t\t" . $k . "\n\t\t\tWHERE\n\t\t\t\t" . $v[0] . " < " . $begin . "\n\t\t");
$report .= 'Table ' . $k . ': deleted ' . phpAds_dbAffectedRows() . ' rows' . "\n";
}
return $report;
}
function phpAds_AutoTargetingPrepareProfile($weeks = 2)
{
global $phpAds_config, $phpAds_dbmsname;
$profile = array(0, 0, 0, 0, 0, 0, 0);
// Get the number of days running
$res = phpAds_dbQuery("SELECT UNIX_TIMESTAMP(MIN(day)) AS days_running FROM " . $phpAds_config['tbl_adstats'] . " WHERE day > 0 AND hour > 0 ORDER BY day LIMIT 1");
$days_running = phpAds_dbResult($res, 0, 'days_running');
if ($days_running > 0) {
$now = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$days_running = $now - $days_running + (date('I', $days_running) - date('I', $now)) * 60;
$days_running = round($days_running / (60 * 60 * 24)) - 1;
}
if ($days_running < $weeks * 7) {
if ($days_running < 7) {
// Not enough stats
return $profile;
} else {
// Use only the available weeks
$weeks = floor($days_running / 7);
}
}
$begin = date('Ymd', phpAds_makeTimestamp(phpAds_LastMidnight, -(60 * 60 * 24 * 7 * $weeks)));
$end = date('Ymd', phpAds_makeTimestamp(phpAds_LastMidnight));
$res_views = phpAds_dbQuery("\n\t\tSELECT\n\t\t\tSUM(views) AS sum_views,\n\t\t\tDATE_FORMAT(day, '%w') AS dow\n\t\tFROM\n\t\t\t" . $phpAds_config['tbl_adstats'] . "\n\t\tWHERE\n\t\t\tday >= {$begin} AND\n\t\t\tday < {$end}\n\t\tGROUP BY\n\t\t\tdow\n\t\tORDER BY\n\t\t\tdow\n\t\t");
// Fill profile
while ($row = phpAds_dbFetchArray($res_views)) {
// PostgreSQL dow starts with 1 instead of 0
if ($phpAds_dbmsname == "PostgreSQL") {
$row['dow']--;
}
$profile[$row['dow']] = round($row['sum_views'] / $weeks);
}
// Use a 9 days profile to make calculations easier
$profile[-1] = $profile[6];
// Saturday
$profile[7] = $profile[0];
// Monday
return $profile;
}
function phpAds_upgradeTargetStats()
{
global $phpAds_config;
if (!isset($phpAds_config['config_version']) || $phpAds_config['config_version'] < 200.13) {
$res = phpAds_dbQuery("\n\t\t\tSELECT\n\t\t\t\ttimestamp,\n\t\t\t\tdetails\n\t\t\tFROM\n\t\t\t\t" . $phpAds_config['tbl_userlog'] . "\n\t\t\tWHERE\n\t\t\t\taction = 11\n\t\t\tORDER BY\n\t\t\t\ttimestamp\n\t\t\t");
while ($row = phpAds_dbFetchArray($res)) {
while (ereg('\\[id([0-9]+)\\]: ([0-9]+)', $row['details'], $match)) {
$day = date('Y-m-d', $row['timestamp']);
if (!isset($start)) {
$start = $row['timestamp'];
}
$autotargets[$day][$match[1]]['target'] = $match[2];
$row['details'] = str_replace($match[0], '', $row['details']);
}
}
if (!isset($start)) {
// No autotargeting logs, exit
return;
}
$t_stamp = mktime(0, 0, 0, date('m', $start), date('d', $start), date('Y', $start));
$t_stamp_now = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
while ($t_stamp < $t_stamp_now) {
$day = date('Ymd', $t_stamp);
$begin = $day . '000000';
$end = $day . '235959';
$campaigns = array();
if (isset($autotargets[$day])) {
while (list($campaignid, ) = each($autotargets[$day])) {
$campaigns[] = $campaignid;
// We need to figure out how to upgrade to batch statistics...
if ($phpAds_config['compact_stats']) {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tSUM(views) AS sum_views\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t" . $phpAds_config['tbl_adstats'] . " AS v,\n\t\t\t\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tv.day = " . $day . " AND\n\t\t\t\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\t\t\t\tb.clientid = " . $campaignid . "\n\t\t\t\t\t\t\t");
} else {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tCOUNT(*) AS sum_views\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t" . $phpAds_config['tbl_adviews'] . " AS v,\n\t\t\t\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tv.t_stamp >= " . $begin . " AND\n\t\t\t\t\t\t\t\tv.t_stamp <= " . $end . " AND\n\t\t\t\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\t\t\t\tb.campaignid = " . $campaignid . "\n\t\t\t\t\t\t\t");
}
if ($views = phpAds_dbResult($res_views, 0, 0)) {
$autotargets[$day][$campaignid]['views'] = $views;
}
}
}
if (count($campaigns)) {
// We need to figure out how to upgrade to batch statistics...
if ($phpAds_config['compact_stats']) {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tSUM(views) AS sum_views\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_adstats'] . " AS v,\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tv.day = " . $day . " AND\n\t\t\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\t\t\tb.campaignid NOT IN (" . join(', ', $campaigns) . ")\n\t\t\t\t\t\t");
} else {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tCOUNT(*) AS sum_views\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_adviews'] . " AS v,\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tv.t_stamp >= " . $begin . " AND\n\t\t\t\t\t\t\tv.t_stamp <= " . $end . " AND\n\t\t\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\t\t\tb.campaignid NOT IN (" . join(', ', $campaigns) . ")\n\t\t\t\t\t\t");
}
} else {
// We need to figure out how to upgrade to batch statistics
if ($phpAds_config['compact_stats']) {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tSUM(views) AS sum_views\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_adstats'] . " AS v\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tv.day = " . $day . "\n\t\t\t\t\t\t");
} else {
$res_views = phpAds_dbQuery("\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tCOUNT(*) AS sum_views\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . $phpAds_config['tbl_adviews'] . " AS v\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tv.t_stamp >= " . $begin . " AND\n\t\t\t\t\t\t\tv.t_stamp <= " . $end . "\n\t\t\t\t\t\t");
}
}
$views = phpAds_dbResult($res_views, 0, 0);
$autotargets[$day][0]['views'] = $views ? $views : 0;
$t_stamp = phpAds_makeTimestamp($t_stamp, 60 * 60 * 24);
}
for (reset($autotargets); $day = key($autotargets); next($autotargets)) {
reset($autotargets[$day]);
while (list($campaignid, $value) = each($autotargets[$day])) {
phpAds_dbQuery("\n\t\t\t\t\tINSERT INTO\n\t\t\t\t\t\t" . $phpAds_config['tbl_targetstats'] . " (\n\t\t\t\t\t\t\tday,\n\t\t\t\t\t\t\tcampaignid,\n\t\t\t\t\t\t\ttarget,\n\t\t\t\t\t\t\tviews\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'" . $day . "',\n\t\t\t\t\t\t\t" . $campaignid . ",\n\t\t\t\t\t\t\t" . (isset($value['target']) ? (int) $value['target'] : 0) . ",\n\t\t\t\t\t\t\t" . (isset($value['views']) ? (int) $value['views'] : 0) . "\n\t\t\t\t\t\t)\n\t\t\t\t\t");
}
}
}
}
function phpAds_TargetStatsSaveViews()
{
global $phpAds_config;
$campaigns = array();
$day = date('Ymd', phpAds_makeTimestamp(mktime(0, 0, 0, date('m'), date('d'), date('Y')), -(60 * 60 * 24)));
$begin = $day . '000000';
$end = $day . '235959';
// Get total views
if ($phpAds_config['compact_stats']) {
$query = "\n\t\t\tSELECT\n\t\t\t\tSUM(views) AS sum_views\n\t\t\tFROM\n\t\t\t\t" . $phpAds_config['tbl_adstats'] . "\n\t\t\tWHERE\n\t\t\t\tday = " . $day . "\n\t\t";
} else {
$query = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*) AS sum_views\n\t\t\tFROM\n\t\t\t\t" . $phpAds_config['tbl_adviews'] . "\n\t\t\tWHERE\n\t\t\t\tt_stamp >= " . $begin . " AND\n\t\t\t\tt_stamp <= " . $end . "\n\t\t";
}
$sum_views = phpAds_dbResult(phpAds_dbQuery($query), 0, 'sum_views');
$totalviews = 0;
$res = phpAds_dbQuery("\n\t\tSELECT\n\t\t\tclientid\n\t\tFROM\n\t\t\t" . $phpAds_config['tbl_targetstats'] . "\n\t\tWHERE\n\t\t\tday = " . $day . " AND\n\t\t\tclientid > 0\n\t\t");
while ($row = phpAds_dbFetchArray($res)) {
if ($phpAds_config['compact_stats']) {
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tSUM(views) AS sum_views\n\t\t\t\tFROM\n\t\t\t\t\t" . $phpAds_config['tbl_adstats'] . " AS v,\n\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\tWHERE\n\t\t\t\t\tv.day = " . $day . " AND\n\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\tb.clientid = " . $row['clientid'] . "\n\t\t\t";
} else {
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOUNT(*) AS sum_views\n\t\t\t\tFROM\n\t\t\t\t\t" . $phpAds_config['tbl_adviews'] . " AS v,\n\t\t\t\t\t" . $phpAds_config['tbl_banners'] . " AS b\n\t\t\t\tWHERE\n\t\t\t\t\tv.t_stamp >= " . $begin . " AND\n\t\t\t\t\tv.t_stamp <= " . $end . " AND\n\t\t\t\t\tb.bannerid = v.bannerid AND\n\t\t\t\t\tb.clientid = " . $row['clientid'] . "\n\t\t\t";
}
$views = (int) phpAds_dbResult(phpAds_dbQuery($query), 0, 'sum_views');
$totalviews += $views;
$campaigns[$row['clientid']] = $views;
}
$campaigns[0] = $sum_views - $totalviews;
while (list($campaignid, $views) = each($campaigns)) {
if ($campaignid) {
phpAds_dbQuery("\n\t\t\t\tUPDATE \n\t\t\t\t\t" . $phpAds_config['tbl_targetstats'] . "\n\t\t\t\tSET\n\t\t\t\t\tviews = " . $views . "\n\t\t\t\tWHERE\n\t\t\t\t\tclientid = " . $campaignid . " AND\n\t\t\t\t\tday = " . $day . "\n\t\t\t\t");
} else {
phpAds_dbQuery("\n\t\t\t\tINSERT INTO " . $phpAds_config['tbl_targetstats'] . "\n\t\t\t\t\t(day, clientid, target, views)\n\t\t\t\tVALUES\n\t\t\t\t\t(" . $day . ", " . $campaignid . ", 0, " . $views . ")\n\t\t\t\t");
}
}
}
function phpAds_deleteVerbose($base, $count)
{
global $phpAds_config;
$begin_timestamp = date('YmdHis', phpAds_makeTimestamp($base, $count * 60 * 60 * 24));
$end_timestamp = date('YmdHis', phpAds_makeTimestamp($base, ($count + 1) * 60 * 60 * 24 - 1));
// Delete views
$result = phpAds_dbQuery("\n\t\tDELETE \n\t\t\tLOW_PRIORITY \n\t\tFROM\n\t\t\t" . $phpAds_config['tbl_adviews'] . "\n\t\tWHERE\n\t\t\tt_stamp >= {$begin_timestamp} AND t_stamp <= {$end_timestamp}\n\t") or phpAds_sqlDie();
// Delete clicks
$result = phpAds_dbQuery("\n\t\tDELETE \n\t\t\tLOW_PRIORITY \n\t\tFROM\n\t\t\t" . $phpAds_config['tbl_adclicks'] . "\n\t\tWHERE\n\t\t\tt_stamp >= {$begin_timestamp} AND t_stamp <= {$end_timestamp}\n\t") or phpAds_sqlDie();
}
