zStats.php

<?php

class zStats
{

    public $parent;


    public function __construct( $zVms )
    {
        $this->parent = $zVms;
    }

    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->parent->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.'_campaign_reloads` AS `t2` ON (`t1`.`id` = `t2`.`cid` AND ( `t2`.`uid` = :ruid  OR `t2`.`ip` = :ip ) AND `t2`.`until` >= :until )
                 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`.`cid` 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,
                             ':until' => $zeit,
                             ':buid'  => $uid,
                             ':spuid' => $uid,
                             ':wart'  => $art,
                      );
        $result = $this->parent->database->select( $sql, $sql_params );
        return ( isset($result[0]) ) ? $result[0] : false;
    }

}