<?php

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

abstract class App_DuesReport_AbstractObj extends DBObj
{
    var $_filterFields = array('term');

    function getFromDB($id, $field = false)
    {
        $list = $this->getList4Grid();
        $res = array('price' => array());
        foreach ($list['list'] as $item) {
            $res['price'][$item['id']] = $item['price'];
        }
        return $this->getSubElem($res, $field);
    }

    function update($data)
    {
        $this->_initTable();
        foreach ($data['price'] as $id => $price) {
            $this->table->update(array('price' => $price), 'id = ' . (int) $id);
        }
    }

    public function getMemberTypes()
    {
        $sql = 'SELECT DISTINCT `member_type` FROM ' . $this->tableNameDB . '2MemberType';
        $res = $this->db->queryCol($sql);
        return $res;
    }

    protected function _getReportGroupBy()
    {
        return '';
    }


    public function getReportRes()
    {
        $memberTypes = $this->getMemberTypes();
        $unionParts = array();
        $_sql = 'SELECT '
             . '    `m`.`nrds_id`, '
             . '    `l`.`nrds_id` AS `location_nrds_id`, '
             . '    `m`.`first_name`, '
             . '    `m`.`last_name`, '
             . '    dmmt.title AS mncar_type, '
             . '    dmlmt.title AS mncar_l_type, '
             . '    CONCAT(dmmt.title, IF(m.billing_send_paper_copy = "y", "-G", "")) AS batch_id, '
             . '    ' . $this->quoteField('number', 'dr') . ', '
             . '    ' . $this->quoteField('description', 'dr') . ', '
             . '    ' . $this->quoteField('quantity', 'dr') . ', '
             . '    CONCAT("$", price) AS unit_price, '
             . '    IF(`m`.`bill_to_home_address` = "y", `m`.`address`, l.physical_address) AS address, '
             . '    IF(`m`.`bill_to_home_address` = "y", `m`.`city`, l.physical_city) AS city, '
             . '    IF(`m`.`bill_to_home_address` = "y", `m`.`state`, l.physical_state) AS state, '
             . '    IF(`m`.`bill_to_home_address` = "y", `m`.`zip`, l.physical_zip) AS zip, '
             . '    `l`.`nrds_id` AS location_nrds_id, '
             . '    `l`.`name` AS location_name, '
             . '    `c`.`name` AS company_name, '
             . '    `dr`.sorter as sorter, '
             . '    `dr`.subsorter as subsorter '
             . 'FROM ' . $this->_getPair($this->tableName, 'dr'). ' '
             . 'JOIN ' . $this->_getPair($this->tableName . '2MemberType', 'dr2mt') . ' '
             . '    ON `dr2mt`.`id_dues_report` = `dr`.`id` '
             . 'JOIN ' . $this->_getPair('Member', 'm') . ' '
             . '    ON `m`.`id_[memberType]_type`   = `dr2mt`.`id_member_type` AND `dr2mt`.`member_type` = "[memberType]" '
             . 'LEFT JOIN ' . $this->_getPair('Location', 'l') . ' '
             . '    ON `l`.`id` = `m`.`id_location` '
             . 'LEFT JOIN ' . $this->_getPair('Company', 'c') . ' '
             . '    ON `c`.`id` = `l`.`id_company` '
             . 'LEFT JOIN ' . $this->_getPair('DMncarMemberType', 'dmmt') . ' '
             . '    ON `dmmt`.`id` = `m`.`id_mncar_type` '
             . 'LEFT JOIN ' . $this->_getPair('DMncarLMemberType', 'dmlmt') . ' '
             . '    ON `dmlmt`.`id` = `m`.`id_mncar_l_type` '
             . 'WHERE 1 '
             . '    AND ( '
             . '       `m`.`id_mncar_type` IN (1, 2, 3, 4, 5, 6, 7) '
             . '       OR `m`.`id_mncar_l_type` IN (1, 2, 3, 4) '
             . '       OR `m`.`id_misc_type` IN (1) '
             . '    ) '
             . '    ' . $this->_getReportWhereMemberStatus() . ' '
             . '    AND `m`.`billing_omit_from_invoicing` != "y" '
             . $this->_getReportGroupBy() . ' '
             . '';

        foreach ($memberTypes as $memberType) {
            $unionParts[] = str_replace('[memberType]', $memberType, $_sql);
        }
        $sql = implode(' UNION ', $unionParts);
        $sql .= 'ORDER BY sorter, last_name, subsorter';
        $res = $this->db->query($sql);
        $this->isDBError($res);
        return $res;
    }

    abstract protected function _getReportWhereMemberStatus();
}