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(); }