Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
<?php
class Stats
{
private $database;
public function __construct( $database )
{
$this->database = $database;
}
public function adsTotalsCount( $art, $personal = false )
{
$sql = ' COUNT(`t1`.`tan`) AS `ganzahl`, SUM(`t1`.`verdienst`) AS `gverdienst`, SUM(`t1`.`aufendhalt`) AS `gaufenthalt`,
SUM(`t1`.`menge` * `t1`.`verdienst`) AS `totalvalue`,
SUM(`t1`.`menge`) AS `totalclicks`
FROM `'.DB_PREFIX.'_gebuchte_werbung` AS `t1` ';
if( false !== $personal )
$sql .= ' LEFT JOIN `'.DB_PREFIX.'_userblacklist` `t4` ON `t4`.`uid` = :juid ';
// TODO fix join on blacklist, kid only works with sponsor, going to use id anyway...
$sql .= ' LEFT JOIN `'.DB_PREFIX.'_fb_blacklist` AS `t3` ON `t3`.`kid` = `t1`.`kid` AND `t3`.`werbeart`=`t1`.`werbeart`
WHERE
(`t3`.`kid` IS NULL OR LOCATE(`t3`.`sponsor`, `t1`.`ziel`) = 0) AND
`t1`.`werbeart` = :wart AND
`t1`.`reload` >= 100 AND
`t1`.`menge` > 0 AND
`t1`.`status` = 1 AND
`t1`.`verdienst` >= 0 ';
if( false !== $personal )
$sql .= ' AND `t1`.`sponsor` != :spuid ';
$sql_params = array(':wart' => $art);
if( false !== $personal )
{
$sql_params[':juid'] = $personal;
$sql_params[':spuid'] = $personal;
}
$result = $this->database->select( $sql, $sql_params );
return ( isset($result[0]) ) ? $result[0] : false;
}
public function adsClickableCount( $art, $uid, $ip )
{
$zeit = time();
$sql = ' COUNT(`t1`.`tan`) AS `uanzahl`, SUM(`t1`.`verdienst`) AS `uverdienst` FROM `'.DB_PREFIX.'_gebuchte_werbung` AS `t1`
LEFT JOIN `'.DB_PREFIX.'_reloads` AS `t2` ON (`t1`.`tan` = `t2`.`tan` AND ( `t2`.`uid` = :ruid OR `t2`.`ip` = :ip ) AND t2.bis >= :zeit )
LEFT JOIN `'.DB_PREFIX.'_fb_blacklist` AS `t3` ON `t3`.`kid` = `t1`.`kid` AND `t3`.`werbeart`=`t1`.`werbeart`
LEFT JOIN `'.DB_PREFIX.'_userblacklist` AS `t4` ON `t4`.`uid` = :buid
WHERE
( `t3`.`kid` IS NULL OR LOCATE(`t3`.`sponsor`, `t1`.`ziel`) = 0) AND
`t2`.`tan` IS NULL AND
`t1`.`werbeart` = :wart AND
`t1`.`reload` >= 100 AND
`t1`.`menge` > 0 AND
`t1`.`status` = 1 AND
`t1`.`verdienst` >= 0 AND
`t1`.`sponsor` != :spuid ';
$sql_params = array(
':ruid' => $uid,
':ip' => $ip,
':zeit' => $zeit,
':buid' => $uid,
':spuid'=> $uid,
':wart' => $art,
);
$result = $this->database->select( $sql, $sql_params );
return ( isset($result[0]) ) ? $result[0] : false;
}
}