<?php
define_class(__FILE__, 'StatusHistory');

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

class StatusHistory extends DBObj
{
    public $tableName = 'StatusHistory';
    public  $itemName = 'Member';

    protected $_memberId = null;

    /** @var StatusHistory */
    protected static $_self;

    public function __construct($memberId = null)
    {
        $this->_memberId = intval($memberId);
        return parent::__construct();
    }

    public static function getInstance($memberId)
    {
        if (empty(self::$_self)) {
            self::$_self = new StatusHistory($memberId);
        }
        return self::$_self;
    }

    function _getRes4Grid($what, $opt)
    {
        $sql = array();
        foreach (array('mncar', 'mncar_l', 'misc') AS $type) {
            $opt['type'] = $type;
            $opt['addonWhere'] = " AND `{$this->tableName}`.`accountType` = " . $this->db->quote($type);
            $sql[] = '('
                . 'SELECT  '
                . $what . ' '
                . 'FROM  `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
                . $this->_getJoin4Grid($opt) . ' '
                . $this->_getWhere4Grid($opt) . ' '
                . $this->_getGroup4Grid($opt)
                . ')' ;
        }

        $sql = implode(' UNION ', $sql);
        $sql .= ' ' . $this->_getOrder4Union($opt);

        if (null !== ($ipp = Qs_Array::get($opt, 'ipp'))) {
            $this->db->setLimit($ipp, Qs_Array::get($opt, 'start', 0));
        }
        $res = $this->db->query($sql);
        if (PEAR::isError($res)) {
            $GLOBALS['_DEBUG']['level'] = D_ECHO;
            dump($res->getUserInfo(), 'getList4Grid error');
        }

        return $res;
    }

    public function getStatusChanges($opt = array())
    {
        $sql = array();
        foreach (array('mncar', 'mncar_l', 'misc') AS $type) {
            $opt['type'] = $type;
            $opt['addonWhere'] = " AND `{$this->tableName}`.`accountType` = " . $this->db->quote($type);
            $sql[] = '(' . 'SELECT '
                . $this->_getWhat4Grid() . ' '
                . 'FROM  `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
                . $this->_getJoin4Union($type) . ' '
                . $this->_getWhere4Grid($opt)
                . ')'
            ;

        }
        $sql = implode(' UNION ', $sql);
        $sql .= ' ' . $this->_getOrder4Union($opt);
        if ($opt['limit']) {
            $sql .= ' LIMIT ' . intval($opt['limit']);
        }
        return $this->db->queryAll($sql);
    }

    public function getMemberId()
    {
        return $this->_memberId;
    }

    function _getWhere4Grid($opt = array())
    {
        if (!isset($opt['addonWhere'])) {
            $opt['addonWhere'] = '';
        }
        $opt['addonWhere'] .= " AND `memberId` = " . $this->db->quote($this->_memberId, 'integer');
        return parent::_getWhere4Grid($opt);
    }

    function _getOrder4Union($opt = array())
    {
        $sql = parent::_getOrder4Grid($opt);
        $orderAdded = "`added` DESC";
        if (empty($sql)) {
            $sql = 'ORDER BY ' . $orderAdded;
        } else {
            $sql .= ', ' . $orderAdded;
        }
        return $sql;
    }

    function _getOrder4Grid($opt = array())
    {
        $sql = parent::_getOrder4Grid($opt);
        $orderAdded = "`{$this->tableName}`.`added` DESC";
        if (empty($sql)) {
            $sql = 'ORDER BY ' . $orderAdded;
        } else {
            $sql .= ', ' . $orderAdded;
        }
        return $sql;
    }

    function _getJoin4Grid($opt)
    {
        return $this->_getJoin4Union($opt['type']);
    }

    function _getJoin4Union($type = 'mncar')
    {
        $join = "
        LEFT JOIN {$this->db->tblDMemberStatus} AS `OldStatus` ON `OldStatus`.`id` = `{$this->tableName}`.`oldStatusId`
        LEFT JOIN {$this->db->tblDMemberStatus} AS `NewStatus` ON `NewStatus`.`id` = `{$this->tableName}`.`newStatusId`
        ";
        $condition = " AND `{$this->tableName}`.`accountType` = " . $this->db->quote($type);;
        switch ($type) {
            case 'mncar':
                $join .=
                    "
                    LEFT JOIN `{$this->db->tblDMncarMemberType}` AS `NewType`
                        ON `NewType`.`id` = `{$this->tableName}`.`newTypeId` " . $condition .
                    "
                    LEFT JOIN `{$this->db->tblDMncarMemberType}` AS `OldType`
                        ON `OldType`.`id` = `{$this->tableName}`.`oldTypeId` " . $condition ;
                break;
            case 'mncar_l':
                $join .=
                    "
                    LEFT JOIN `{$this->db->tblDMncarLMemberType}` AS `NewType`
                        ON `NewType`.`id` = `{$this->tableName}`.`newTypeId`" . $condition .
                        "
                    LEFT JOIN `{$this->db->tblDMncarLMemberType}` AS `OldType`
                        ON `OldType`.`id` = `{$this->tableName}`.`oldTypeId` " . $condition;
                break;
            case 'misc':
                $join .=
                    "
                    LEFT JOIN `{$this->db->tblDMiscMemberType}` AS `NewType`
                    ON `NewType`.`id` = `{$this->tableName}`.`newTypeId`" . $condition .
                        "
                    LEFT JOIN `{$this->db->tblDMiscMemberType}` AS `OldType`
                        ON `OldType`.`id` = `{$this->tableName}`.`oldTypeId` " . $condition;
                break;
            default:
                break;
        }
        return $join;

    }

    function _getWhat4Grid($opt = array())
    {
        return "
        `{$this->tableName}`.`added`, `{$this->tableName}`.`oldDateJoined`, `{$this->tableName}`.`newDateJoined`,
        `{$this->tableName}`.`accountType`, `OldStatus`.`title` AS `oldStatusTitle`,
        `NewStatus`.`title` AS `newStatusTitle`, `OldType`.`title` AS `oldTypeTitle`,
        `NewType`.`title` AS `newTypeTitle`";
    }
}