<?php
/**
 * Wombat Newsletter System Issue
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 */

/**
 * Load classes
 */
WBClass::load('WBDatasource_Newsletter');

/**
 * Wombat Newsletter System Issue
 *
 * @version 1.1.2
 * @package WB
 */
class WBDatasource_Newsletter_Issue extends WBDatasource_Newsletter
{
    const TABLE_PAGEVIEW    =   'pageview';

    /**
     * Current Loaded Issue
     * @var array
     */
    private $issue  =   array();

    /**
     * Current Loaded Articles
     * @var array|null
     */
    private $article  =   null;

    /**
     * Current Issue's Topics
     * @var array|null
     */
    private $topic  =   null;

    /**
     * URL Dictinary
     * @var WBDictionary_URL
     */
    private $dict;

    /**
     * 2nd Constructor
     *
     * @param array
     */
    protected function init($parameter = array())
    {
        $this->dict    =   WBClass::create('WBDictionary_URL');
    }

    /**
     * List Most Recent Newsletter Issues
     *
     * @param string mandator id
     * @param int limit
     * @param int sent
     * @return array
     */
    public function getList($mandatorid = -1, $limit = 10, $send = -1)
    {
        $clause     =   array();
        if (-1 != $mandatorid) {
            $clause[]   =   array(
                                    'field' =>  $this->table->getIdentifier(WBDatasource::TABLE_MANDATOR, true),
                                    'value' =>  $mandatorid
                                );
        }

        if (-1 < $send) {
            $clause[]   =   array(
                                'field'     =>  'sent',
                                'value'     =>  '1970-01-01 00:00:00'
                            );
            if (0 < $send) {
                $clause[0]['relation']  =   'gt';
            }
        }
        $clause[]   =   array(
                                'field'     =>  'enabled',
                                'value'     =>  '1'
                            );
        $clause[]   =   array(
                                'field'     =>  'created',
                                'value'     =>  gmdate('Y-m-d H:i:s'),
                                'relation'  =>  'le'
                            );
        $option     =   array(
                                'limit' =>  $limit,
                                'order' =>  array(
                                                    array(
                                                            'field' =>  'created',
                                                            'asc'   =>  0
                                                        )
                                                )
                            );

        $list   =   $this->table->get(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE, null, null, $clause, $option);
        return $list;
    }

    /**
     * Load Issue
     *
     * @todo be more robust
     * @param string $id
     */
    public function load($id)
    {
        if (!empty($this->issue) && $id == $this->issue['id']) {
            return;
        }

        $issue    =   $this->table->get(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE, $id);
        if (1 != count($issue)) {
            return;
        }

        $this->resolveURL($issue[0]);
        $this->issue    =   $issue[0];
        $this->article  =   null;
        $this->topic    =   null;
    }

    /**
     * Tell Whether Current Issue Is Loaded
     *
     * @return bool
     */
    public function isOk()
    {
        if (empty($this->issue)) {
            return false;
        }

        return true;
    }

    /**
     * Get Datetime When Newsletter Issue was Sent
     *
     * @return string datetime or empty string
     */
    public function getSentDate()
    {
        if (!$this->isOk()) {
            return '';
        }

        if ('1970-01-01 00:00:00' == $this->issue['sent']) {
            return '';
        }

        return $this->issue['sent'];
    }

    /**
     * Get Current Newsletter Issue Data
     *
     * @param string column to fetch
     * @return array
     */
    public function get($key = null)
    {
        if (!$this->isOk()) {
            return array();
        }

        if (empty($key)) {
            return $this->issue;
        }

        if (empty($this->issue[$key])) {
            return '';
        }

        return $this->issue[$key];
    }

    /**
     * Get Mandator Data For Current Issue
     *
     * Use mandator loader to extend data. Loader-class is named in config
     *
     * @param string optional mandatorid
     * @return array
     */
    public function getMandatorData($mandatorid = null)
    {
        $name   =   parent::getConfig('mandator/loader', 'Customer');

        /** @var WBDatasource_Newsletter_Mandator_Loader */
        $loader =   WBClass::create('WBDatasource_Newsletter_Mandator_Loader_' . $name);

        if (null === $mandatorid) {
            if (!$this->isOk()) {
                return array();
            }
            $loader->loadByData($this->issue);
            return $loader->get();
        }

        $loader->loadById($mandatorid);
        return $loader->get();
    }

    /**
     * Get Issue's Id
     *
     * @return string
     */
    public function getId()
    {
        if (!$this->isOk()) {
            return '';
        }

        return $this->issue['id'];
    }

    /**
     * Get Issue's Language Code
     *
     * @return string
     */
    public function getLang()
    {
        if (!$this->isOk()) {
            return '';
        }

        return $this->issue['lang'];
    }

    /**
     * Mark Issue as Sent
     *
     * @param string date
     */
    public function setSent($date = 'now')
    {
        if (!$this->isOk()) {
            return;
        }
        $this->issue['sent']    =   gmdate('Y-m-d H:i:s', strtotime($date));

        $save           =   array();
        $save['sent']   =   $this->issue['sent'];
        $this->table->save(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE, $this->issue['id'], $save);
    }

    /**
     * Count Number of Recipients
     *
     * @return int
     */
    public function countRcpt()
    {
        if (empty($this->getSentDate())) {
            return 0;
        }

        $clause     =   array();
        $key        =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE);
        if (!is_array($key)) {
            $key    =   array($key);
        }
        foreach ($key as $k) {
            $clause[]   =   array(
                                'field' =>  $k,
                                'value' =>  $this->issue[$k]
                            );
        }

        $cnt    =   $this->table->count(WBDatasource_Newsletter::TABLE_SUBSCRIBERSENT, null, null, $clause);
        return $cnt;
    }

    /**
     * Get List of Newsletter Topics
     *
     * @return array
     */
    public function getTopic()
    {
        if (!$this->isOk()) {
            return array();
        }

        if (is_array($this->topic)) {
            return $this->topic;
        }

        $pIssue     =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE);
        $pTop       =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERTOPIC);
        $clause     =   array();
        if (!is_array($pIssue)) {
            $pIssue    =   array($pIssue);
        }
        foreach ($pIssue as $k) {
            $clause[]   =   array(
                                'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETOPIC,
                                'field' =>  $k,
                                'value' =>  $this->issue[$k]
                            );
        }
        $opt        =   array(
                            'join'      =>  array(),
                            'order'     =>  array(
                                                array(
                                                        'field'     =>  'title',
                                                        'asc'       =>  1
                                                    )
                                            )
                        );
        $opt['join'][]      =   array(
                                    'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETOPIC,
                                    'using' =>  $pTop,
                                );


        $this->topic    =   $this->table->get(WBDatasource_Newsletter::TABLE_NEWSLETTERTOPIC, null, null, $clause, $opt);
        return $this->topic;
    }

    /**
     * Get List of Articles in Issue
     *
     * @return array
     */
    public function getArticle()
    {
        if (!$this->isOk()) {
            return array();
        }

        if (is_array($this->article)) {
            return $this->article;
        }
        $pArt       =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERARTICLE);
        $pIssue     =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE);
        $clause     =   array();
        $clause[]   =   array(
                            'field' =>  'enabled',
                            'value' =>  1
                        );
        if (!is_array($pIssue)) {
            $pIssue    =   array($pIssue);
        }
        foreach ($pIssue as $k) {
            $clause[]   =   array(
                                'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUEARTICLE,
                                'field' =>  $k,
                                'value' =>  $this->issue[$k]
                            );
        }

        $opt        =   array(
                            'join'      =>  array(),
                            'limit'     =>  10,
                            'order'     =>  array(
                                                array(
                                                        'field'     =>  'created',
                                                        'asc'       =>  0
                                                    )
                                            )
                        );
        $opt['join'][]      =   array(
                                    'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUEARTICLE,
                                    'using' =>  $pArt,
                                );
        $this->article  =   $this->table->get(WBDatasource_Newsletter::TABLE_NEWSLETTERARTICLE, null, null, $clause, $opt);
        foreach ($this->article as &$art) {
            $this->resolveURL($art);
        }
        return $this->article;
    }

    /**
     * Return Config Values
     *
     * @param string configuration path
     * @return array
     */
    public function getConfig($path = '', $default = null)
    {
        if (!empty($path)) {
            $path   =   '/' . $path;
        }
        return parent::getConfig('issue' . $path, $default);
    }

    /**
     * Get Number Of Counted Tracking Pixel
     *
     * @param string $path
     * @return int
     */
    public function getTrackingPixelCount($path = '')
    {
        if (!$this->isOk()) {
            return 0;
        }

        $clause     =   array();
        $clause[]   =   array(
                            'field' =>  'namespace',
                            'value' =>  'pixel'
                        );
        $clause[]   =   array(
                            'field'     =>  'created',
                            'relation'  =>  'ge',
                            'value'     =>  $this->issue['created']
                        );
        if (!empty($path)) {
            $clause[]   =   array(
                            'field' =>  'path',
                            'value' =>  $path
                        );
        }

        return $this->table->count(self::TABLE_PAGEVIEW, null, null, $clause);
    }

    /**

    SELECT linkid, count(*) AS cnt FROM wblinktracking WHERE linkid IN (6,7,8,9,10,11,12,13,14,15) AND created > "2017-09-13"  GROUP BY linkid ORDER BY cnt DESC;

     * @todo reconsider
     */
    public function getTracklink()
    {
        if (!$this->isOk()) {
            return array();
        }

        $created    =   strtotime($this->issue['created']);

        $list       =   array();
        $pLink      =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_TRACKLINK);
        $clause     =   array();
        $clause[]   =   array(
                            'field'     =>  'created',
                            'relation'  =>  'ge',
                            'value'     =>  date('Y-m-d', $created)
                        );
        $clause[]   =   array(
                            'field'     =>  'created',
                            'relation'  =>  'lt',
                            'value'     =>  date('Y-m-d', $created + 7 * 24 * 3600)
                        );
        $pIssue     =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE);
        if (!is_array($pIssue)) {
            $pIssue    =   array($pIssue);
        }
        foreach ($pIssue as $k) {
            $clause[]   =   array(
                                'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETRACKLINK,
                                'field' =>  $k,
                                'value' =>  $this->issue[$k]
                            );
        }

        $opt        =   array(
                            'column'    =>  array(),
                            'join'      =>  array(),
                            'limit'     =>  100,
                            'groupby'   =>  array(
                                                    'field' =>  'linkid',
                                                    'table' =>  WBDatasource_Newsletter::TABLE_LINKTRACKING
                                                ),
                            'order'     =>  array(
                                                array(
                                                        'field'     =>  'field',
                                                        'native'    =>  'cnt',
                                                        'asc'       =>  0
                                                    )
                                            )
                        );
        $opt['join'][]      =   array(
                                    'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETRACKLINK,
                                    'using' =>  $pLink,
                                );
        $opt['column'][]    =   array(
                                    'table'     =>  WBDatasource_Newsletter::TABLE_LINKTRACKING,
                                    'field'     =>  $pLink,
                                    'function'  =>  'count',
                                    'as'        =>  'cnt'
                                );
        $opt['column'][]    =   array(
                                    'field' =>  $pLink
                                );
        $opt['column'][]    =   array(
                                    'table'     =>  WBDatasource_Newsletter::TABLE_LINKTRACKING,
                                    'field'     =>  $pLink,
                                    'function'  =>  'count',
                                    'as'        =>  'cnt'
                                );

        $list   =   $this->table->get(WBDatasource_Newsletter::TABLE_LINKTRACKING, null, null, $clause, $opt);
        return $list;
    }

    /**
     * Get Top Clicked Pages

SELECT p.path, count(p.path) AS cnt
    FROM wbpageview p
    JOIN wblinktracking t USING (trackid)
    WHERE
        p.namespace="site"
        AND p.created > "2017-09-01"
        AND t.linkid=123
    GROUP BY p.path
    ORDER BY cnt DESC
    LIMIT 200;
     * @return array $list
     */
     public function getMostClickedPages()
     {
        if (!$this->isOk()) {
            return array();
        }

        $created    =   strtotime($this->issue['created']);

        $pLink      =   $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_TRACKLINK);

        $clause     =   array();
        $clause[]   =   array(
                            'field'     =>  'namespace',
                            'table'     =>  self::TABLE_PAGEVIEW,
                            'value'     =>  'site'
                        );
        $clause[]   =   array(
                            'field'     =>  'created',
                            'table'     =>  self::TABLE_PAGEVIEW,
                            'relation'  =>  'ge',
                            'value'     =>  $this->issue['created']
                        );
        $clause[]   =   array(
                            'field'     =>  'created',
                            'table'     =>  self::TABLE_PAGEVIEW,
                            'relation'  =>  'ge',
                            'value'     =>  date('Y-m-d', $created + 7 * 24 * 3600)
                        );
        $clause[]   =   array(
                            'field' =>  $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_TRACKLINK),
                            'table' =>  WBDatasource_Newsletter::TABLE_LINKTRACKING,
                            'value' =>  $this->issue[$pLink]
                        );
        $opt        =   array(
                            'column'    =>  array(),
                            'join'      =>  array(),
                            'limit'     =>  200,
                            'groupby'   =>  array(
                                                    'field' =>  'path',
                                                    'table' =>  self::TABLE_PAGEVIEW
                                                ),
                            'order'     =>  array(
                                                array(
                                                        'field'     =>  'field',
                                                        'native'    =>  'cnt',
                                                        'asc'       =>  0
                                                    )
                                            )
                        );
        $opt['join'][]      =   array(
                                    'table' =>  WBDatasource_Newsletter::TABLE_LINKTRACKING,
                                    'using' =>  'trackid',
                                );
        $opt['column'][]    =   array(
                                    'table'     =>  self::TABLE_PAGEVIEW,
                                    'field'     =>  'path'
                                );
        $opt['column'][]    =   array(
                                    'table'     =>  self::TABLE_PAGEVIEW,
                                    'field'     =>  'path',
                                    'function'  =>  'count',
                                    'as'        =>  'cnt'
                                );

        $list   =   $this->table->get(self::TABLE_PAGEVIEW, null, null, $clause, $opt);
        return $list;

    }

    /**
     * Use Dictionary 2 Resolve URLid
     *
     * @param array
     */
    private function resolveURL(&$data)
    {
        $key    =   'urlid';

        if (empty($data[$key])) {
            return;
        }

        $this->dict->load($data[$key]);
        $word   =   $this->dict->getWord();
        $info   =   $this->dict->get();

        $data[$key . '_protocol']   =   $info['protocol'];
        if ('self' == $info['protocol']) {
            $word   =   '[[PROTOCOL]]://[[SERVER]]' . $word;
        }
        $data[$key . '_url']    =   $word;
    }
}