<?php

define_class(__FILE__, 'App_Member_Invoice_Admin_Obj');

require_once('app/Invoice/AbstractObj.php');

class App_Member_Invoice_Admin_Obj extends App_Invoice_AbstractObj
{
    var $delMethod  = DB_OBJ_DEL_METHOD_LOGICAL;
    var $fileFields = array('attachment');
    var $ownersName = 'members';
    var $_filterFields = array(
        '`Invoice`.`number`', 
        '`Member`.`nrds_id`', 
        "CONCAT(`Member`.`last_name`, IF(`Member`.`last_name` = '','', ', '), `Member`.`first_name`, IF(`Member`.`middle_initial` = '' , '', ' '), `Member`.`middle_initial`, IF(`Member`.`middle_initial` = '' , '', '. '))"
    );
    protected $_objectReferenceField = 'id_member';
    
    function _getWhat4Grid($opt = array())
	{
        $sql = parent::_getWhat4Grid($opt);
        $sql .= ', `Member`.`id`  as `id_member`';
        $sql .= ", CONCAT(`Member`.`last_name`, IF(`Member`.`last_name` = '','', ', '), `Member`.`first_name`, "
              . "IF (`Member`.`middle_initial` = '' , '', ' '), `Member`.`middle_initial`, "
              . "IF (`Member`.`middle_initial` = '' , '', '. ')) AS `member_name`";
		return $sql;
	}
    
    function _getJoin4Grid()
	{
        $sql = '';
        $sql .= 'JOIN `' . $this->db->tblMember . '` AS `Member` '
              . 'ON `Member`.`id` = `' . $this->tableName . '`.`id_member` ';
		return $sql;
	}

    function _getJoinLocation4Grid()
    {
        $sql = '';
        $sql .= 'JOIN `' . $this->db->tblLocation . '` AS `Location` '
              . 'ON `Location`.`id` = `' . $this->tableName . '`.`id_location` ';
        $sql .= 'JOIN `' . $this->db->tblMember . '` AS `Member` '
              . 'ON `Member`.`id` = `Location`.`id_pay_member` ';
        return $sql;
    }
    
    function _getRes4Grid($what, $opt)
    {
		$sql = '(SELECT ' . $what . ' '
             . 'FROM  `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
             . $this->_getJoin4Grid() . ' '
			 . $this->_getWhere4Grid($opt) . ' '
			 . $this->_getGroup4Grid($opt) . ' '
			 . $this->_getHaving4Grid($opt) . ' '
             . ') UNION (SELECT ' . $what . ' '
             . 'FROM  `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
             . $this->_getJoinLocation4Grid() . ' '
			 . $this->_getWhere4Grid($opt) . ' '
			 . $this->_getGroup4Grid($opt) . ' '
			 . $this->_getHaving4Grid($opt) . ' '
             . ') '
			 . $this->_getOrder4Grid($opt);
        //vdie($sql);
		$res = 	$this->db->query($sql);
        if (PEAR::isError($res)) {
        	$GLOBALS['_DEBUG']['level'] = D_ECHO;
        	dump($res->getUserInfo(), 'getList4Grid error');
		}
		return $res;
    }
    
    function getTotalAmountDue()
    {
        $addonWhere = 'AND `' . $this->tableName . '`.`paid` = \'n\' ';
        return $this->_getAmount($addonWhere);
    }
    
    protected function _getAmount($addonWhere = '')
    {
        // SQL PARTS
        $filter = array_intersect_key(
            $this->_filter, 
            array_fill_keys(array('id_member', 'id_location'), '')
        );
        $from = 'SELECT SUM(`' . $this->tableName . '`.`amount`) '
              . 'FROM `' . $this->tableNameDB . '` AS `' . $this->tableName . '` ';
        $where = 'WHERE 1 ' . $addonWhere . ' '
               . $this->_getWhereDeleted()
               . $this->_getWhereFilter($filter);
        // MAIN SQL
        $sql = '(' . $from . ' '
             . $this->_getJoin4Grid() . ' '
             . $where
             . ') UNION (' . $from . ' '
             . $this->_getJoinLocation4Grid() . ' '
             . $where
             . ')';
        $res = $this->db->queryCol($sql);
        $this->isDBError($res);
        $res = array_sum($res);
        return $res;
    }
    
    function getAmountOverdue()
    {
        $addonWhere = 'AND `' . $this->tableName . '`.`paid` = \'n\' '
                    . 'AND `' . $this->tableName . '`.`date_due` < CURRENT_DATE()';
        return $this->_getAmount($addonWhere);
    }
}