<?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.0
 * @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;

    /**
     * List Most Recent Newsletter Issues
     *
     * @param int limit
     * @param int sent
     * @return array
     */
    public function getList($limit = 10, $send = -1)
    {
        $clause     =   array();
        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->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
     *
     * @return array
     */
    public function get()
    {
        if (!$this->isOk()) {
            return array();
        }

        return $this->issue;
    }

    public function getId()
    {
        if (!$this->isOk()) {
            return array();
        }

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

    public function getLang()
    {
        if (!$this->isOk()) {
            return '';
        }

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

    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);
    }


    public function countRcpt()
    {
        if (empty($this->getSentDate())) {
            return 0;
        }
        $clause     =   array();
        $clause[]   =   array(
                            'field' =>  $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE),
                            'value' =>  $this->issue['id']
                        );
        $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();
        $clause[]   =   array(
                            'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETOPIC,
                            'field' =>  $pIssue,
                            'value' =>  $this->issue['id']
                            );
        $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
                        );
        $clause[]   =   array(
                            'field' =>  $pIssue,
                            'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUEARTICLE,
                            'value' =>  $this->issue['id']
                        );

        $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);
        return $this->article;
    }

    /**
     * Return Config Values
     *
     * @param string configuration path
     * @return array
     */
    public function getConfig($path = '')
    {
        if (!empty($path)) {
            $path   =   '/' . $path;
        }
        return $this->config->get('issue' . $path);
    }

    /**
     * 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)
                        );
        $clause[]   =   array(
                            'field' =>  $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE),
                            'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETRACKLINK,
                            'value' =>  $this->issue['id']
                        );

        $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;

    }

    /**
     * Get Top Clicked Pages

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

        $list       =   array();

        $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' =>  $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_NEWSLETTERISSUE),
                            'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETRACKLINK,
                            'value' =>  $this->issue['id']
                        );

        $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['join'][]      =   array(
                                    'table' =>  WBDatasource_Newsletter::TABLE_NEWSLETTERISSUETRACKLINK,
                                    'using' =>  $this->table->getIdentifier(WBDatasource_Newsletter::TABLE_TRACKLINK),
                                );
        $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;
     }
}