DataAccess/mysql/Catalog.php

Go to the documentation of this file.

<?php
// {{{ license

// +----------------------------------------------------------------------+
// | FastFrame Application Framework                                      |
// +----------------------------------------------------------------------+
// | Copyright (c) 2002-2006 The Codejanitor Group                        |
// +----------------------------------------------------------------------+
// | This source file is subject to the GNU Lesser Public License (LGPL), |
// | that is bundled with this package in the file LICENSE, and is        |
// | available at through the world-wide-web at                           |
// | http://www.fsf.org/copyleft/lesser.html                              |
// | If you did not receive a copy of the LGPL and are unable to          |
// | obtain it through the world-wide-web, you can get it by writing the  |
// | Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, |
// | MA 02111-1307, USA.                                                  |
// +----------------------------------------------------------------------+
// | Authors: Jason Rust <jrust@codejanitor.com>                          |
// +----------------------------------------------------------------------+

// }}}
// {{{ requires

require_once FASTFRAME_ROOT . 'lib/FastFrame/DataAccess.php';

// }}}
// {{{ constants

// The type constants
define('FILTER_BY_CREATOR', 1);
define('FILTER_BY_REP', 2);
// Exported constants
define('REQUEST_NOT_EXPORTED', 0);
define('REQUEST_EXPORTED', 1);
define('REQUEST_DOWNLOADED', 2);

// }}}
// {{{ class FF_DataAccess_Catalog_mysql 

// }}}
class FF_DataAccess_Catalog_mysql extends FF_DataAccess {
    // {{{ constructor

    function FF_DataAccess_Catalog_mysql()
    {
        FF_DataAccess::FF_DataAccess();
        $this->table = $this->o_registry->getConfigParam('data/table', 'catalog_requests');
    }

    // }}}
    // {{{ update()

    function update($in_data)
    {
        $in_data['appt_date_rep'] = $this->timestampToISODate($in_data['appt_date_rep']);
        $in_data['exported'] = REQUEST_NOT_EXPORTED;
        $in_data['deleted'] = false;
        unset($in_data['create_date']);
        unset($in_data['creator_id']);
        return parent::update($in_data);
    }

    // }}}
    // {{{ add()

    function add($in_data)
    {
        $in_data['creator_id'] = FF_Auth::getCredential('userId');
        $in_data['create_date'] = $this->timestampToISODate(time());
        $in_data['appt_date_rep'] = $this->timestampToISODate($in_data['appt_date_rep']);
        $in_data['exported'] = REQUEST_NOT_EXPORTED;
        return parent::add($in_data);
    }

    // }}}
    // {{{ remove()

    function remove($in_id, $in_exported = false)
    {
        $o_result = new FF_Result();
        if ($in_exported) { 
            $s_where = 'exported > ' . REQUEST_NOT_EXPORTED;
        }
        else { 
            $s_where = 'id=' . $this->o_data->quoteSmart($in_id);
        }

        $result = $this->o_data->autoExecute($this->table, array('deleted' => true), DB_AUTOQUERY_UPDATE, $s_where);
        if (DB::isError($result)) {
            $o_result->addMessage($result->getMessage());
            $o_result->setSuccess(false);
        }

        return $o_result;
    }

    // }}}
    // {{{ markAsExported()

    function markAsExported()
    {
        $o_result = new FF_Result();
        $result = $this->o_data->autoExecute($this->table, array('exported' => REQUEST_EXPORTED), DB_AUTOQUERY_UPDATE, 'exported=' . REQUEST_DOWNLOADED);
        if (PEAR::isError($result)) {
            $o_result->setSuccess(false);
            $o_result->addMessage($result->getMessage());
        }

        return $o_result;
    }

    // }}}
    // {{{ markAsDownloaded()

    function markAsDownloaded()
    {
        $o_result = new FF_Result();
        $result = $this->o_data->autoExecute($this->table, array('exported' => REQUEST_DOWNLOADED), DB_AUTOQUERY_UPDATE, 'exported=' . REQUEST_NOT_EXPORTED);
        if (PEAR::isError($result)) {
            $o_result->setSuccess(false);
            $o_result->addMessage($result->getMessage());
        }

        return $o_result;
    }

    // }}}
    // {{{ getDataByPrimaryKey()

    function getDataByPrimaryKey($in_id, $in_fields = '*')
    {
        return parent::getDataByPrimaryKey($in_id, $this->_getSelectFields());
    }

    // }}}
    // {{{ getDataToExport()

    function &getDataToExport()
    {
        $s_query = "SELECT name, address, city, state, zip, phone, mail_method, '3' AS weight, rep, country,
                    DATE_FORMAT(appt_date_rep, '%W') AS appt_day,
                    DATE_FORMAT(appt_date_rep, '%c/%e/%Y') AS appt_date,
                    DATE_FORMAT(appt_date_rep, '%l:%i %p') AS appt_time_rep,
                    DATE_FORMAT(appt_date_rep + INTERVAL canidate_time_offset HOUR_MINUTE, '%l:%i %p') AS appt_time_canidate,
                    program, special_requests
                    FROM $this->table 
                    WHERE deleted = 0 AND (exported = " . REQUEST_NOT_EXPORTED . " OR
                    exported = " . REQUEST_DOWNLOADED . ") ORDER BY country, mail_method, rep";
        return $this->o_data->query($s_query);
    }

    // }}}
    // {{{ getListData()

    function getListData($in_where, $in_orderByField, $in_orderByDir, $in_fields = '*')
    {
        return parent::getListData($in_where, $in_orderByField, $in_orderByDir, $this->_getSelectFields());
    }

    // }}}
    // {{{ getListFilter()

    function getListFilter($in_searchString, $in_searchFields, $in_filter, $in_filterData)
    {
        if ($in_filter == FILTER_BY_CREATOR) {
            $s_filter = 'creator_id = ' . $this->o_data->quoteSmart($in_filterData['userId']) . ' AND ';
        }
        elseif ($in_filter == FILTER_BY_REP) {
            if (!empty($in_filterData['repId'])) {
                $s_filter = 'rep = ' . $this->o_data->quoteSmart($in_filterData['repId']) . ' AND ';
            }
            else {
                $s_filter = '';
            }
        }
        else {
            $s_filter = 'deleted != 1 AND ';
        }

        $s_filter .= '(' . parent::getListFilter($in_searchString, $in_searchFields, $in_filter, $in_filterData) . ')';
        return $s_filter;
    }

    // }}}
    // {{{ getStats()

    function getStats($in_start, $in_end)
    {
        $a_stats = array();
        $in_start = $this->timestampToISODate($in_start);
        $in_end = $this->timestampToISODate($in_end);
        $s_query = "SELECT COUNT(*) FROM $this->table 
                    WHERE create_date > ? AND create_date < ?";
        $a_stats['total'] = $this->o_data->getOne($s_query, array($in_start, $in_end));

        $s_query = "SELECT mail_method,COUNT(mail_method) AS tmp_count
                    FROM $this->table 
                    WHERE create_date > ? AND create_date < ?
                    GROUP BY mail_method ORDER BY tmp_count DESC";
        $a_stats['count_by_mail_method'] = $this->o_data->getAssoc($s_query, false, array($in_start, $in_end));

        $s_profileTable = $this->o_registry->getConfigParam('data/table', 'profile', 'profile');
        $s_query = "SELECT CONCAT(firstname, ' ', lastname), COUNT(creator_id) AS tmp_count
                    FROM $this->table AS c
                    INNER JOIN $s_profileTable AS p ON c.creator_id=p.id
                    WHERE create_date > ? AND create_date < ?
                    GROUP BY creator_id ORDER BY tmp_count DESC";
        $a_stats['count_by_rep'] = $this->o_data->getAssoc($s_query, false, array($in_start, $in_end));
        return $a_stats;
    }

    // }}}
    // {{{ getRepOptions()

    function getRepOptions()
    {
        $s_table = $this->o_registry->getConfigParam('data/reps_table');
        return $this->o_data->getAssoc("SELECT rep, rep FROM $s_table ORDER BY rep");
    }

    // }}}
    // {{{ _getSelectFields()

    function _getSelectFields()
    {
        return 'id, name, address, city, state, zip, phone, country, UNIX_TIMESTAMP(appt_date_rep) AS appt_date_rep, canidate_time_offset, program, rep, mail_method, special_requests, exported, manager_approved, UNIX_TIMESTAMP(create_date) AS create_date';
    }

    // }}}
}
?>

---