campaigns.php

<?php

class Campaigns
{

    private $database;

    const FIELDS_gebuchte_werbung = ' `t1`.`id`, `t1`.`uid`, `t1`.`tan`, `t1`.`kid`, `t1`.`ziel`, `t1`.`banner`,
                                      `t1`.`verdienst`, `t1`.`preis`, `t1`.`aufendhalt`, `t1`.`menge`, `t1`.`reload`,
                                      `t1`.`sponsor`, `t1`.`werbeart`, `t1`.`status` ';

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

    public function decreaseAvailCountById( $cid, $value = 1 )
    {
        $sql = 'UPDATE `'. DB_PREFIX . '_gebuchte_werbung` SET `menge` = GREATEST(0, `menge` - :value ) WHERE `id`= :id';
        $sql_params = array(
                           ':id' => $cid,
                           ':value' => $value
                      );
        return $this->database->sqlUpdate($sql, $sql_params);
    }

    public function getIsClickableById( $cid, $ip, $uid, $time )
    {
        $sql = self::FIELDS_gebuchte_werbung .'
               FROM `'. DB_PREFIX .'_gebuchte_werbung` AS `t1`
               LEFT JOIN `'. DB_PREFIX .'_campaign_reloads` AS `t2` ON 
                   (
                     `t1`.`id` = `t2`.`cid`  AND
                     (
                        `t2`.`uid` = :t2uid OR
                        `t2`.`ip` = :t2ip
                     ) AND
                     `t2`.`until` > :t2until
                   )
               WHERE
              `t1`.`id` = :t1cid AND
              `t2`.`cid` IS NULL AND
              `t1`.`menge` >= 1 AND
              `t1`.`status` = 1 AND
              `t1`.`sponsor` != :t1uid
              LIMIT 1';

        $sql_params = array(
                           ':t2uid'  => $uid,
                           ':t2ip'   => inet_pton($ip),
                           ':t2until'=> $time,
                           ':t1cid'  => $cid,
                           ':t1uid'  => $uid,
                     );  
        $result = $this->database->select( $sql, $sql_params );
        return ( isset($result[0]) ) ? $result[0] : false;

    }

    public function getIsClickableByTanAndType( $tan, $type, $ip, $uid, $zeit )
    {
        $sql = self::FIELDS_gebuchte_werbung .'
               FROM `'. DB_PREFIX .'_gebuchte_werbung` AS `t1`
               LEFT JOIN `'. DB_PREFIX .'_campaign_reloads` AS `t2` ON 
                   (
                     `t1`.`id` = `t2`.`cid`  AND
                     (
                        `t2`.`uid` = :t2uid OR
                        `t2`.`ip` = :t2ip
                     ) AND
                     `t2`.`until` > :t2until
                   )
               WHERE
              `t1`.`tan` = :t1tan AND
              `t2`.`cid` IS NULL AND
              `t1`.`werbeart` = :t1type AND
              `t1`.`menge` >= 1 AND
              `t1`.`status` = 1 AND
              `t1`.`sponsor` != :t1uid
              LIMIT 1';

        $sql_params = array(
                           ':t2uid'   => $uid,
                           ':t2ip'    => inet_pton($ip),
                           ':t2until' => $zeit,
                           ':t1tan'   => $tan,
                           ':t1type'  => $type,
                           ':t1uid'   => $uid,
                     );
        $result = $this->database->select( $sql, $sql_params );
        return ( isset($result[0]) ) ? $result[0] : false;

    }

    public function getByTanAndType( $tan, $type, $status = false )
    {
        $where_status = ( false !== $status ) ? ' AND `status` = :status ' : '';

        $sql = self::FIELDS_gebuchte_werbung .' FROM `'.DB_PREFIX.'_gebuchte_werbung` AS `t1` WHERE `tan` = :tan AND `werbeart` = :type '.$where_status.' LIMIT 1';
        $sql_params = array(
                           ':tan'     => $tan,
                           ':type'    => $type,
                      );
        if( false !== $status )
        {
            $sql_params[':status'] = $status;
        }

        $result = $this->database->select($sql, $sql_params);
        return ( isset($result[0]) ) ? $result[0] : false;
    }

    public function getNewAdData( $art, $uid, $ip, $limit = 3 )
    {
        $zeit = time();
        $result = array('count' => 0, 'data' => array() );
        $num_limit = (int)$limit;

        $sql = self::FIELDS_gebuchte_werbung .' 
                    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` = :ubuid
                    WHERE
                    (`t3`.`kid` IS NULL OR LOCATE(`t3`.`sponsor`, `t1`.`ziel`) = 0) AND 
                    `t2`.`cid` IS NULL AND
                    `t1`.`werbeart` = :wart AND
                    `t1`.`menge` > 0 AND
                    `t1`.`status` = 1 AND
                    `t1`.`verdienst` > 0 AND
                    `t1`.`sponsor` != :spuid
                    ORDER BY `t1`.`verdienst` DESC LIMIT '.$num_limit;

        $sql_params = array(
                            ':ruid'  => $uid,
                            ':ip'    => inet_pton($ip),
                            ':until' => $zeit,
                            ':ubuid' => $uid,
                            ':wart'  => $art,
                            ':spuid' => $uid,
                     );

        $result['data']  = $this->database->select($sql, $sql_params);
        $result['count'] = count($result['data']);

        return $result;
    }

    public function getAll( $art, $status = false, $sponsor = false, $limit = false, $start = false)
    {
        $result = array('count' => 0, 'data' => array() );
        $sql_params = array();

        $sql = self::FIELDS_gebuchte_werbung .' FROM `' . DB_PREFIX . '_gebuchte_werbung` AS `t1`
                WHERE';

        if( false !== $sponsor )
        {
            $sql .= '`t1`.`sponsor` = :sponsor AND ';
            $sql_params[':sponsor'] = $sponsor;
        }

        if( false !== $status )
        {
            $sql .= '`t1`.`status` = :status AND ';
            $sql_params[':status'] = $status;
        }

        $sql .= '`t1`.`werbeart` = :wart
                  ORDER BY kid ASC ';

        $sql_params[':wart']  = $art;

        $result['data']  = $this->database->select($sql, $sql_params);
        $result['count'] = count($result['data']);

        return $result;
    }

    public function delete( $where )
    {
        return $this->database->delete( '`' . DB_PREFIX . '_gebuchte_werbung`' , $where );
    }

    public function update( $data, $where )
    {
        return $this->database->update( '`' . DB_PREFIX . '_gebuchte_werbung`' , $data, $where );
    }

    public function insert( $data )
    {
        return $this->database->insert( '`' . DB_PREFIX . '_gebuchte_werbung`' , $data );
    }

    public function getTimeToFirstOffReloadAd( $art, $uid )
    {
        $zeit = time();
        $retval = NULL;

        $sql = ' `r`.`until` FROM `'.DB_PREFIX.'_campaign_reloads` AS `r`
                         LEFT JOIN `'.DB_PREFIX.'_gebuchte_werbung` AS `ad` ON (`ad`.`id` = `r`.`cid` AND `ad`.`status` = 1 AND `ad`.`werbeart` = :wart AND `ad`.`sponsor` != :spuid)
                         WHERE
                         `r`.`uid` = :ruid  AND `ad`.`id` IS NOT NULL  AND `r`.`until` > :until
                         ORDER BY `r`.`until` ASC LIMIT 1';
        $sql_params = array(
                            ':wart'   => $art,
                            ':spuid'  => $uid,
                            ':ruid'   => $uid,
                            ':until'  => $zeit,
                      );
        $result = $this->database->select($sql, $sql_params);
        if( isset($result[0]) )
        {
            $res    = $result[0];
            $retval = (($res->until - $zeit ) >= 0)  ?  ($res->until - $zeit) : NULL;
        }

        return $retval;

    }
}