<?php

namespace CGClientTickets\Repository;

/**
 * This class generates a ticket list based on various criteria.
 * It is used for the tickets tab in the admin interface, and possibly the
 * frontend user ticket list interface.
 */
class TicketListResultset extends \CGExtensions\query\resultset
{
    public function __construct(TicketListQuery $filter) {
        // @todo: check the params
        $this->_filter = $filter;
    }

    protected function _query()
    {
        if( $this->_rs ) return;
        $db = \cge_utils::get_db();
        $where = array();
        $parms = array();
        $cols = explode(',','T.id,T.owner,T.alert_group,T.e_key1,T.e_key2,T.e_key3,T.status,T.priority,T.created,COUNT(M.id) AS nmsgs, M.subject, MAX(M.modified) AS modified_date');
        $sql = 'SELECT SQL_CALC_FOUND_ROWS %COLS% FROM '.CGST_TICKETS_TABLE.' T LEFT JOIN '.CGST_MSGS_TABLE.' M ON T.id = M.ticket_id';

        $unread_by = 'junk';
        if( isset($this->_filter['unread_by']) ) $unread_by = $this->_filter['unread_by'];
        switch( $unread_by ) {
        case 'any':
            $sql .= ' LEFT JOIN (SELECT ticket_id,COUNT(id) AS n_unread FROM '.CGST_MSGS_TABLE.' WHERE viewed_by IS NULL GROUP BY ticket_id) S ON T.id = S.ticket_id';
            $cols[] = 'S.n_unread';
            break;

        case 'admin':
            $sql .= ' LEFT JOIN (SELECT ticket_id,COUNT(id) AS n_unread FROM '.CGST_MSGS_TABLE.' WHERE dest < 0 AND viewed_by IS NULL GROUP BY ticket_id) S ON T.id = S.ticket_id';
            $cols[] = 'S.n_unread';
            break;

        case 'alert_group':
            die('incomplete');
            break;

        case 'owner':
            if( !(isset($this->_filter['owner']) && $this->_filter['owner']) ) throw new \LogicException('Cannot return unread by owner if owner is not specified');
            $sql .= ' LEFT JOIN (SELECT ticket_id,COUNT(id) AS n_unread FROM '.CGST_MSGS_TABLE.' WHERE dest = ? AND viewed_by IS NULL GROUP BY ticket_id) S ON T.id = S.ticket_id';
            $cols[] = 'S.n_unread';
            $parms[] = $this->_filter['owner'];
            break;

        default:
            $cols[] = '0 AS n_unread';
            break;
        }

        if( isset($this->_filter['owner']) && $this->_filter['owner'] ) {
            $where[] = 'T.owner = ?';
            $parms[] = $this->_filter['owner'];
        }
        else if( isset($this->_filter['alert_group']) ) {
            $tmp = $this->_filter['alert_group'];
            $str = null;
            if( is_array($tmp) ) {
                $tmp = array_unique($tmp);
                foreach( $tmp as $one ) {
                    if( $tmp >= 0 ) throw new \LogicException('Invalid alert_group specified in query for '.__CLASS__);
                }
                $str = 'IN ('.implode(',',$tmp).')';
            }
            else {
                $tmp = (int) $tmp;
                if( $tmp >= 0 ) throw new \LogicException('Invalid alert_group specified in query for '.__CLASS__);
                $str = "= $tmp";
            }
            $where[] = "T.alert_group $str";
        }
        else {
            throw new \LogicException('Must either specify an alert group, or an owner for a '.get_class($this->_filter));
        }

        if( $this->_filter['status'] != '' ) {
            $where[] = "T.status = ?";
            $parms[] = $this->_filter['status'];
        }
        if( $this->_filter['priority'] != '' ) {
            $where[] = "T.priority = ?";
            $parms[] = $this->_filter['priority'];
        }
        if( ($e_key1 = $this->_filter['e_key1']) ) {
            $where[] = 'T.e_key1 = ?';
            $parms[] = $e_key1;
            if( ($e_key2 = $this->_filter['e_key2']) ) {
                $where[] = 'T.e_key2 = ?';
                $parms[] = $e_key2;
                if( ($e_key3 = $this->_filter['e_key3']) ) {
                    $where[] = 'T.e_key3 = ?';
                    $parms[] = $e_key3;
                }
            }
        }

        // FINAL QUERY ASSEMBLY
        $sql = str_replace('%COLS%',implode(', ',$cols),$sql);
        if( count($where) ) $sql .= " WHERE ".implode(' AND ',$where);
        $sql .= ' GROUP BY T.id ORDER BY modified_date DESC';

        try {
            $this->_rs = $db->SelectLimit($sql,$this->_filter['limit'],$this->_filter['offset'],$parms);
        }
        catch( \Exception $e ) {
            die($db->sql.' '.$db->ErrorMsg());
        }

        $this->_totalmatching = $db->GetOne('SELECT FOUND_ROWS()');
    }

    public function &get_object()
    {
        // this time we're just gonna return the data
        $obj = \CGClientTickets\SubData\TicketListItem::create_from_data($this->fields);
        return $obj;
    }

} // end of class
