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

require_once('app/BroadcastEmail/BroadcastEmail.php');

class BroadcastEmailRepository extends BroadcastEmail
{
    var $itemName = 'Email';
    var $_filterFields = array('Email.body', 'Email.subject');

    function BroadcastEmailRepository($id = null, $id_individual = null)
    {
        $this->DBObj($id);
        $this->id_individual = intval($id_individual);
        $this->tblIndividual = $this->db->tblMember;
    }

    function _getWhat4Grid($opt = array())
    {
        return "
            {$this->tableName}.*,
            CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as individual_full_name,
            Individual.img as individual_img,
            Company.name as company_name,
            DEmailType.title as type
        ";
    }

    function _getJoin4Grid()
    {
        return "
            JOIN {$this->db->tblDEmailType} as DEmailType
                ON DEmailType.id = {$this->tableName}.id_type
            JOIN {$this->tblIndividual} as Individual
                ON Individual.id = {$this->tableName}.id_individual
            LEFT JOIN {$this->db->tblLocation} as Location
                ON Location.id = Individual.id_location
            LEFT JOIN {$this->db->tblCompany} as Company
                ON Company.id = Location.id_company
        ";
    }

    function _getWhere4Grid($opt = array())
    {
        $where = parent::_getWhere4Grid($opt);
        $where .= " AND {$this->tableName}.completed = 'y' ";
        return $where;
    }

    function _getGroup4Grid()
    {
        return " GROUP BY Email.id";
    }

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');
        $sql = DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);
        if (!$sql) {
            $sql = "";
        }
        if (is_array($this->filter) && !empty($this->filter)) {
            $filter_by_property_size = false;
            foreach ($this->filter as $field => $value) {
                if ($value === '' || $value === 'all') {
                    continue;
                }
                switch($field) {
                case 'id_specialty':
                case 'id_type':
                    $sql .= " AND {$this->tableName}.{$field} =  ".$this->db->quote($value, 'integer');
                    break;
                case 'location_id':
                    if ($this->filter['id_type']=='all' || $this->filter['id_type']==1) {
                        $sql .= " AND {$this->tableName}.{$field} =  ".$this->db->quote($value, 'integer');
                    }
                    break;
                case 'primary_use_id':
                    if ($this->filter['id_type']!=3) {
                        $sql .= " AND {$this->tableName}.{$field} =  ".$this->db->quote($value, 'integer');
                    }
                    break;
                case 'subtype_id':
                    if ($this->filter['id_type']!=3) {
                        $sql .= " AND {$this->tableName}.id IN (
                            SELECT email_id
                            FROM {$this->db->tblEmail2SubType}
                            WHERE subtype_id = ".$this->db->quote($value, 'integer') . ')';
                    }
                    break;
                case 'investment':
                    if ($this->filter['id_type']!=3 && $this->filter['subtype_id']!=1 && $this->filter['subtype_id']!=2) {
                        $sql .= " AND {$this->tableName}.{$field} = 'y'";
                    }
                    break;
                case 'subject':
                    $ret = DBObj_Filter::perseQr($this->db, array('Email.subject'), $value);
                    if ($ret) {
                        $sql .= $ret;
                    }
                    break;
                case 'subject_radio3':
                    $sql .= " AND {$this->tableName}.{$field} =  ".$this->db->quote($value, 'text');
                    break;
                case 'no_subject':
                    $ret = DBObj_Filter::perseQr($this->db, array('Email.subject'), $value, 'NOT LIKE', 'AND');
                    if ($ret) {
                        $sql .= $ret;
                    }
                    break;
                case 'no_query':
                    $ret = DBObj_Filter::perseQr($this->db, $this->_filterFields, $value, 'NOT LIKE', 'AND');
                    if ($ret) {
                        $sql .= $ret;
                    }
                    break;
                case 'day_cnt':
                    $time = time() - 3600*24*intval($value);
                    $sql .= " AND UNIX_TIMESTAMP({$this->tableName}.added) >= ".$time;
                    break;
                    /*
                case 'property_size_min':
                    $sql .= " AND {$this->tableName}.property_size >= ".$this->db->quote($value, 'integer');
                    $filter_by_property_size = true;
                    break;
                case 'property_size_max':
                    if (!empty($this->filter['property_size_min'])) {
                        $sql .= " AND {$this->tableName}.property_size <= ".$this->db->quote($value, 'integer');
                    } else {
                        $sql .= " AND ({$this->tableName}.property_size <= ".$this->db->quote($value, 'integer')." OR {$this->tableName}.property_size IS NULL) ";
                    }
                    $filter_by_property_size = true;
                    break;
                    */
                case 'sender':
                    $ret = DBObj_Filter::perseQr($this->db, array("CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name)", 'Individual.email'), $value);
                    if ($ret) {
                        $sql .= $ret;
                    }
                    break;
                }
            }
            if ($this->filter['use_range'] == 'y') {
                $isMin = (isset($this->filter['property_size_min']) && !empty($this->filter['property_size_min']));
                $isMax = (isset($this->filter['property_size_max']) && !empty($this->filter['property_size_max']));
                if (!$isMin && $isMax) {
                    $filter_by_property_size = true;
                    $sql .= " AND
                    (
                        ({$this->tableName}.use_range = 'n' AND {$this->tableName}.property_size_min <= ".$this->db->quote($this->filter['property_size_max'], 'integer').")
                        OR
                        ({$this->tableName}.use_range = 'y' AND {$this->tableName}.property_size_max <= ".$this->db->quote($this->filter['property_size_max'], 'integer').")
                    )
                    ";
                } else if ($isMin && !$isMax) {
                    $filter_by_property_size = true;
                    $sql .= " AND {$this->tableName}.property_size_min >= ".$this->db->quote($this->filter['property_size_min'], 'integer')."";
                } else if ($isMin && $isMax) {
                    $filter_by_property_size = true;
                    $sql .= " AND
                    (
                        ".$this->db->quote($this->filter['property_size_min'], 'integer')." < {$this->tableName}.property_size_max
                        AND ".$this->db->quote($this->filter['property_size_max'], 'integer')." > {$this->tableName}.property_size_min
                    )
                    ";
                }
            } else {
                if (isset($this->filter['property_size']) && !empty($this->filter['property_size']) && $this->filter['id_type']!=3) {
                    $filter_by_property_size = true;
                    $sql .= " AND (".$this->db->quote($this->filter['property_size'], 'integer')." BETWEEN {$this->tableName}.property_size_min AND {$this->tableName}.property_size_max)";
                }
            }
            if ($filter_by_property_size && $this->filter['property_unit']) {
                $sql .= " AND {$this->tableName}.property_unit = ".$this->db->quote($this->filter['property_unit'], 'text');
            }
        }
        return (empty($sql))?false:$sql;
    }

    function getFromDB4View($id, $field = false)
    {
        $id = intval($id);
        $sql = "SELECT
                    {$this->tableName}.*,
                    CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as full_name,
                    Individual.email as individual_email
                FROM {$this->tableNameDB} AS {$this->tableName}
                JOIN {$this->tblIndividual} as Individual
                    ON Individual.id = {$this->tableName}.id_individual
                WHERE {$this->tableName}.id = $id";
        $res = $this->db->queryRow($sql);
        $this->arrayParseTag2Url(&$res);
        return $this->getSubElem($res, $field);
    }

    function getSummarySpecials($id_type)
    {
        $sql = "
            SELECT pu.id, pu.title, COUNT(Email.id) as cnt
            FROM {$this->db->tblDPrimaryUse } as pu
            LEFT JOIN {$this->tableNameDB} as Email
                ON  Email.completed = 'y'
                AND pu.id = Email.primary_use_id
                AND Email.id_type = ".$this->db->quote($id_type, 'integer')."
            WHERE 1 AND pu.type LIKE '%" . ($id_type==1?'listing':'requirement'). "%'
            GROUP BY pu.id
            ORDER BY pu.sorter
        ";
        $res = $this->db->queryAll($sql);
        $this->_sqlError($res);
        return $res;
    }

    function getDEmailType4Summary()
    {
        $sql = "
            SELECT DEmailType.*, count(Email.id) as cnt
            FROM {$this->db->tblDEmailType} as DEmailType
            LEFT JOIN {$this->tableNameDB} as Email
                ON DEmailType.id = Email.id_type
                AND Email.completed = 'y'
            GROUP BY DEmailType.id
            ORDER BY DEmailType.sorter
        ";
        $res = $this->db->queryAll($sql);
        $this->_sqlError($res);
        return $res;
    }

    function getSummary()
    {
        $listType = $this->getDEmailType4Summary();
        $list = array();
        foreach ($listType as $k => $v) {
            $listItem = $this->getList4Grid(array('order_by'=>'added DESC', 'ipp'=>2, 'addonWhere'=>'AND Email.id_type='.intval($v['id'])));
            $v = array_merge($v, $listItem);
            if ($v['id'] != 3) {
                // ��� Announcements �� ���������� Specialty
                $v['primaryUseList'] = $this->getSummarySpecials($v['id']);
            }
            $list[] = $v;
        }
        return $list;
    }

    function setFilter($filter)
    {
        if (isset($filter['query']) && !empty($filter['query'])) {
            $this->_filterQuery = (string)$filter['query'];
            unset($filter['query']);
        }
        $this->filter = $filter;
    }
}