<?php

define_class(__FILE__, 'Sponsor');

require_once('class/DB/DBObj.php');

class Sponsor extends DBObj
{
    var $tableName = 'Sponsor';
    var $itemName = 'Sponsor Logo/Banner';
    var $itemsName = 'Sponsor Logos';
    var $_urlParseFields = array();
    var $timeInterval = 2592000; // 30 days
    var $timeUserClickInterval = 86400; // 24 hours
    var $_filterFields = array('company', 'contact', 'url');

    function Sponsor($id = null)
    {
        $this->DBObj($id);
    }

    function _getWhat4Grid($opt = array())
    {
        $time = time() - $this->timeInterval;
        return "{$this->tableName}.*, DSponsorType.title as type, " . (int)$this->filter['year'] . " AS yearFilter,
            (SELECT COUNT(*) FROM {$this->tableNameDB}History WHERE id_parent = {$this->tableName}.id
                AND UNIX_TIMESTAMP(added) >= {$time}) AS monthCount,
            (SELECT COUNT(*) FROM {$this->tableNameDB}History WHERE id_parent = {$this->tableName}.id" .
                ($this->filter['year'] ? (" AND YEAR(added) = " . (int)$this->filter['year']) : "") . ") AS totalCount";
    }

    function _getJoin4Grid()
    {
        $time = time() - $this->timeInterval;
        return "
            JOIN {$this->db->tblDSponsorType} as DSponsorType
                ON DSponsorType.id = {$this->tableName}.id_type
        ";
    }

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');
        $sql = DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);
        if (!$sql) {
            $sql = "";
        }
        $filter = &$this->filter;
        foreach ($filter as $field=>$value) {
            if ($value == "" || $value == 'all') {
                continue;
            }
            switch($field) {
            case 'active':
                if (in_array($value, array('y', 'n'))) {
                    $sql .= " AND {$this->tableName}.{$field} = '{$value}'";
                }
                break;
            case 'id_type':
                $sql .= " AND {$this->tableName}.{$field} = ".$this->db->quote($value, 'integer');
                break;
            }
        }
        return (empty($sql))?false:$sql;
    }

    function _getGroup4Grid()
    {
        return "GROUP BY {$this->tableName}.id";
    }

    function initFromForm(&$frm)
    {
        $files = array();
        $files['img']  = $frm->getElementValue('img');
        $data = $frm->exportValues();
        $data['id_sponsor_area_type'] = $this->initCheckBoxListFromForm($data['id_sponsor_area_type']);
        $this->arrayParseUrl2Tag($data);
        $this->initFromArray($data, $files);
        return true;
    }

    function initCheckBoxListFromForm($list)
    {
        if (is_array($list) && !empty($list)) {
            $value = current($list);
            if (is_array($value)) {
                foreach ($list as $k => &$v) {
                    $list[$k] = $this->initCheckBoxListFromForm($v);
                }
                return $list;
            } else {
                return array_filter($list, 'intval');
            }
        }
        return array();
    }

    function getFromDB($id, $field = false)
    {
        $id = intval($id);
        $sql = "SELECT {$this->tableName}.*
                FROM {$this->tableNameDB} AS {$this->tableName}
                WHERE {$this->tableName}.id = $id";
        $res = $this->db->queryRow($sql);
        if ($field === false) {
            $res['id_sponsor_area_type'] = $this->getIdsFromDB($this->tableNameDB.'2AreaType', $this->db->tblDSponsorAreaType, 'id_sponsor_area_type', $id);
        }
        $this->arrayParseTag2Url(&$res);
        return $this->getSubElem($res, $field);
    }

    function getIdsFromDB($tableName, $DTableName, $field_name, $id_parent)
    {
        $sql = "
                SELECT tblLink.{$field_name} as id, tblLink.{$field_name} as title
                FROM {$tableName} as tblLink
                JOIN {$DTableName} as Dictionary
                    ON tblLink.{$field_name} = Dictionary.id
                WHERE tblLink.id_parent = ".$id_parent;
        $res = $this->db->queryAll($sql, 0, nnull, true);
        $this->_sqlError($res);
        return $res;
    }

    function delete()
    {
        $this->_files = array(
            'img'  => array('del' => 1),
        );
        parent::delete();
        return true;
    }

    function uniqueUser()
    {
        $time = time() - $this->timeUserClickInterval;
        $sql = "
            SELECT COUNT(*)
            FROM {$this->tableNameDB}History
            WHERE
                id_parent = ".$this->db->quote($this->id, 'integer')."
                AND ip = ".$this->db->quote($_SERVER['REMOTE_ADDR'], 'text')."
                AND UNIX_TIMESTAMP(added) > {$time}
            ";
        $res = $this->db->queryOne($sql);
        $this->_sqlError($res);
        return !(bool)$res;
    }

    function click()
    {
        if (!$this->uniqueUser()) {
            return false;
        }
        require_once('app/Member/MemberAuth.php');
        require_once('class/DB/DBTable.php');

        $History = new DBTable($this->tableName.'History');
        $historyData = array(
            'id_parent' => intval($this->id),
            'id_individual' => intval(MemberAuth::getSessionData('id')),
            'ip' => strval($_SERVER['REMOTE_ADDR']),
            'user_agent' => strval($_SERVER['HTTP_USER_AGENT']),
            'page' => BASE_URL.'/'.CURR_PAGE.((empty($_SERVER['QUERY_STRING']))?'':'?'.$_SERVER['QUERY_STRING']),
        );
        $History->insert($historyData);
        $this->initFromDB();
        $data = array(
            'total_cnt'     => (int)$this->getData('total_cnt') + 1,
            'last_month_cnt'=> $this->getLastMonthCnt()
        );
        $this->_initTable();
        $this->table->update($data, 'id='.$this->db->quote($this->id, 'integer'));
        $this->db->query($sql);
        $this->_sqlError($res);
        //$this->clearOldHistory();
    }

    function getLastMonthCnt()
    {
        $time = time() - $this->timeInterval;
        $sql = "
            SELECT COUNT(*)
            FROM {$this->tableNameDB}History
            WHERE
                id_parent = ".$this->db->quote($this->id, 'integer')."
                AND UNIX_TIMESTAMP(added) >= {$time}
        ";
        $res = $this->db->queryOne($sql);
        $this->_sqlError($res);
        return (int)$res;
    }

    function clearOldHistory()
    {
        $time = time() - $this->timeInterval;
        $sql = "DELETE FROM {$this->tableNameDB}History WHERE UNIX_TIMESTAMP(added) < {$time}";
        $res = $this->db->query($sql);
        $this->_sqlError($res);
    }

    function initSponsotTypeId($name)
    {
        $sql = "SELECT id FROM {$this->db->tblDSponsorType} WHERE title = ".$this->db->quote($name, 'text');
        $this->id_type = (int)$this->db->queryOne($sql);
    }

    public function isUrl($value)
    {
        return false === strpos($value, '@');
    }

    function getRandomBlock($id_type, $current_ids, $markets = null)
    {
        $ids_sponsor_area_type = array();
        if (is_string($markets) && !empty($markets)) {
            $ids_sponsor_area_type = explode(',', $markets);
            $ids_sponsor_area_type = array_filter($ids_sponsor_area_type, 'intval');
            $joinSponsorAreaType = '';
        }
        if (!empty($ids_sponsor_area_type)) {
            $joinSponsorAreaType .= "
                JOIN {$this->tableNameDB}2AreaType as Sponsor2AreaType
                    ON  Sponsor2AreaType.id_parent = Sponsor.id
                    AND Sponsor2AreaType.id_sponsor_area_type IN (".implode(',', $ids_sponsor_area_type).")
            ";
        }

        $id_type = intval($id_type);
        if ($id_type == 1) {
            $limit = 2;
        } else {
            $limit = 1;
        }
        $sql = "
            SELECT id, id_type, company, contact, img, url
            FROM {$this->tableNameDB} as Sponsor
            {$joinSponsorAreaType}
            WHERE Sponsor.active = 'y'
        ";
        if (is_array($current_ids) && !empty($current_ids)) {
            function arr_intval(&$item) {intval($item); }
            array_walk($current_ids, 'arr_intval');
            $sql .= " AND Sponsor.id NOT IN (".implode(',', $current_ids).")";
        }
        if ($id_type) {
            $sql .= " AND Sponsor.id_type = ".$this->db->quote($id_type, 'integer');
        }
        $sql .= " GROUP BY Sponsor.id ORDER BY RAND() ";
        $sql .= " LIMIT ".intval($limit);
        $res = $this->db->queryAll($sql);
        $this->_sqlError($res);
        $this->_prepareList($res);
        return $res;
    }

    protected function _prepareRow(&$row)
    {
        $row['isUrl'] = $this->isUrl($row['url']);
        return $this;
    }

    protected function _prepareList(&$list)
    {
        foreach ($list as &$row) {
            $this->_prepareRow($row);
        }
        return $this;
    }

    function initFromDB()
    {
        parent::initFromDB();
        if (is_array($this->_data)) {
            $this->_prepareRow($this->_data);
        }
    }

    function insert($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $data = $this->_data;
        unset($data['id']);
        $fields = $this->getSimpleFieldList();
        if (isset($fields['sorter']) && $data['id_type'] == 1) {
            $data['sorter'] = $this->getSorter();
        }
        $this->id = $this->table->insert($data);
        $this->updateIds($this->tableNameDB.'2AreaType', 'id_sponsor_area_type', $this->_data['id_sponsor_area_type']);
        $this->handleFiles();
        return $this->id;
    }

    function update($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $this->table->update($this->_data, 'id = '. $this->db->quote($this->id, 'integer') );
        $this->updateIds($this->tableNameDB.'2AreaType', 'id_sponsor_area_type', $this->_data['id_sponsor_area_type']);
        $this->handleFiles();
        return true;
    }
}