<?php

define('cn_ExchangeDuesReportExchangeDuesReport', 'ExchangeDuesReport');

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

class ExchangeDuesReport extends DBObj
{
    const LARGE_COMPANY_ID = 1;
    const BROADCAST_TEMPLATE_ID = 14;
    const DATA_FEED_CO = 15;
    const DATA_FEED_INDV = 20;
    const RAW_DATA_FEED = 21;

    var $_filterFields = array('term');
    var $tableName = 'ExchangeDuesReportItems';

    function DuesReport($id=null)
    {
        parent::DBObj($id);
    }

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

    protected function _getReportQuery($countTableName)
    {
        $sql = "SELECT
                    l.nrds_id,
                    m.first_name,
                    m.last_name,
                    number,
                    description,
                    CASE WHEN edri.mncar_market_id = 0 AND edri.edam = 'n' THEN 1
                        WHEN edri.mncar_market_id = 0 AND edri.edam = 'y' AND edri.first_user='y'
                            THEN LEAST(mc.edam, 1)
                        WHEN edri.mncar_market_id = 0 AND edri.edam = 'y' AND edri.first_user='n'
                            THEN GREATEST(mc.edam - 1, 0)
                        WHEN edri.mncar_market_id = 4 AND edri.first_user='y'
                            THEN LEAST(mc.total, 1) /* Duluth Report */
                        WHEN edri.mncar_market_id = 4 AND edri.first_user='n'
                            THEN GREATEST(mc.total - 1, 0) /* Duluth Report */
                        WHEN edri.member='y' AND edri.first_user='y' THEN LEAST(mc.members, 1)
                        WHEN edri.member='y' AND edri.first_user='n' THEN GREATEST(mc.members - 1, 0)
                        WHEN edri.member='n' AND edri.first_user='y' THEN IF(mc.members > 0, 0, LEAST(mc.non_members, 1))
                        WHEN edri.member='n' AND edri.first_user='n'
                            THEN GREATEST(mc.non_members - IF(mc.members > 0, 0, 1), 0)
                    END AS quantity,
                    CONCAT('$', price) AS unit_price,
                    CONCAT('$', FORMAT(CASE WHEN edri.mncar_market_id = 0 AND `edri`.`edam` = 'n' THEN price
                        WHEN edri.mncar_market_id = 4 AND edri.first_user='y'
                            THEN LEAST(mc.total, 1)*price /* Duluth Report */
                        WHEN edri.mncar_market_id = 4 AND edri.first_user='n'
                            THEN GREATEST(mc.total - 1, 0) *price /* Duluth Report */

                        WHEN edri.edam = 'y' AND edri.first_user='y'
                            THEN LEAST(mc.edam, 1)*price /* Edam Report */
                        WHEN edri.edam = 'y' AND edri.first_user='n'
                            THEN GREATEST(mc.edam - 1, 0) *price /* Edam Report */

                        WHEN edri.member='y' AND edri.first_user='y' THEN LEAST(mc.members, 1)*price
                        WHEN edri.member='y' AND edri.first_user='n' THEN GREATEST(mc.members - 1, 0) *price
                        WHEN edri.member='n' AND edri.first_user='y'
                            THEN IF(mc.members > 0, 0, LEAST(mc.non_members, 1))*price
                        WHEN edri.member='n' AND edri.first_user='n'
                            THEN GREATEST(mc.non_members - IF(mc.members > 0, 0, 1), 0)*price
                    END, 2)) AS total,
                l.physical_address,
                l.physical_city,
                l.physical_state,
                l.physical_zip,
                l.name AS location_name,
                c.name AS company_name,
                contact_name,
                IF(
                    payMember.id IS NOT NULL,
                    CONCAT(
                        payMember.first_name,
                        ' ',
                        payMember.middle_initial,
                        IF (payMember.middle_initial = '' , '', '. '),
                        payMember.last_name
                    ),
                    pay_member
                ) AS pay_member,
                payMember.billing_send_paper_copy,
                payMember.billing_omit_from_invoicing,
                CONCAT(CASE WHEN l.large_company='y' THEN 'Large Company'
                	WHEN edri.mncar_market_id = 2 THEN 'StCloud'
                	WHEN edri.mncar_market_id = 3 THEN 'Rochester'
                	WHEN edri.mncar_market_id = 4 THEN 'Duluth'  /* For Duluth report */
                	ELSE 'TwinCities' END, IF(payMember.billing_send_paper_copy != 'y', '-G', '')) AS batch_id
            FROM {$this->db->tblLocation} l
                LEFT JOIN {$this->db->tblMember} m ON l.id_designated_realtor = m.id
                LEFT JOIN {$this->db->tblMember} AS payMember ON payMember.id = l.id_pay_member
                LEFT JOIN {$this->db->tblCompany} c ON c.id = l.id_company
                INNER JOIN {$this->db->tblExchangeDuesReportItems} edri ON
                    edri.mncar_market_id = l.market_id
                    OR (edri.id = " . self::LARGE_COMPANY_ID . " AND l.large_company='y')
                    OR (edri.id = " . self::BROADCAST_TEMPLATE_ID . " AND l.templates='y')
                    OR (edri.id = " . self::DATA_FEED_CO . " AND l.data_feeds_office='y')
                    OR (edri.id = " . self::DATA_FEED_INDV . " AND l.data_feeds_individual = 'y')
                    OR (edri.id = " . self::RAW_DATA_FEED . " AND l.raw_datafeeds = 'y')
                    OR (`edri`.`edam` = 'y' AND edri.mncar_market_id = 0)
                LEFT JOIN `{$countTableName}` mc ON l.id = mc.id
            WHERE
                (`edri`.`edam` = 'n' AND (
                        (edri.mncar_market_id = 0 ) OR
                        (edri.member='y' AND edri.first_user='y' AND mc.members > 0) OR
                        (edri.member='y' AND edri.first_user='n' AND mc.members > 1) OR
                        (edri.member='n' AND edri.first_user='y' AND mc.non_members > 0 AND mc.members = 0) OR
                        (
                            edri.member='n'
                            AND edri.first_user='n'
                            AND (mc.non_members > 1 OR (mc.non_members > 0 AND mc.members > 0))
                        )
                        OR (edri.mncar_market_id = 4 AND edri.first_user='y' AND mc.total > 0) /* For Duluth Report */
                        OR (edri.mncar_market_id = 4 AND edri.first_user='n' AND mc.total > 1) /* For Duluth Report */
                    )
                )
                OR (`edri`.`edam` = 'y'
                    AND edri.mncar_market_id = 0
                    AND `edri`.`first_user` = 'y'
                    AND `mc`.`edam` > 0) /* Edam Report */
                OR (`edri`.`edam` = 'y'
                    AND edri.mncar_market_id = 0
                    AND `edri`.`first_user` = 'n'
                    AND `mc`.`edam` > 1) /* Edam Report */
            HAVING
            (
                (payMember.billing_omit_from_invoicing IS NOT NULL AND payMember.billing_omit_from_invoicing != 'y')
                OR payMember.billing_omit_from_invoicing IS NULL
            )
            ORDER BY l.id, edri.sorter
        ";

        return $sql;
    }

    public function getReportDbResult()
    {
        $this->_createTemporaryTableForMembersCount('tempMemberCount');
        return $this->db->query($this->_getReportQuery('tempMemberCount'));
    }

    protected function _createTemporaryTableForMembersCount($temporaryTableName)
    {
        $sqlTemporary = "
            CREATE TEMPORARY TABLE `{$temporaryTableName}` AS
            SELECT
                l.id,
                SUM(IF(m.id_misc_type = 1 AND id_misc_status IN(1, 3), 1, 0)) non_members,
                SUM(IF(
                        (   (m.id_mncar_type IN (1, 2, 3, 4, 5, 6, 7) AND id_mncar_status IN(1, 3))
                            OR
                            (m.id_mncar_l_type IN (1, 2, 3, 4) AND id_mncar_l_status IN(1, 3))
                        ),
                        1,
                        0
                      )
                    )
                    members,
                SUM(1) total,
                SUM(IF(m.id_mncar_l_type = 4 AND m.id_mncar_l_status = 1, 1, 0)) AS edam
            FROM {$this->db->tblLocation} l
            INNER JOIN {$this->db->tblMember} m ON m.id_location = l.id
            WHERE 1
                AND m.allow_mcpe_access = 'y'
                AND m.id_mcpe_access_type = 1
            GROUP BY l.id
            ORDER BY l.id";
        //vdie($sqlTemporary);
        $this->db->query($sqlTemporary);
        $this->db->query("ALTER TABLE `{$temporaryTableName}` ADD INDEX ( `id` )");

//        vdie($this->db->queryAll('SELECT * FROM tempMemberCount;'));
        return $this;
    }
}