DataAccess/mysql/Item.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>                          |
// | Authors: Greg Gilbert <ggilbert@codejanitor.com>                     |
// +----------------------------------------------------------------------+

// }}}
// {{{ requires

require_once FASTFRAME_ROOT . 'lib/FastFrame/DataAccess.php';
require_once dirname(__FILE__) . '/../../Model/Log.php';

// }}}
// {{{ constants

// The filters for the list page
define('ITEMS_FILTER_CATEGORY', 1);
define('ITEMS_FILTER_UNFILED', 2);
define('ITEMS_FILTER_ALL', 3);
define('ITEMS_GROUPS_FILTER_CATEGORY', 4);
define('ITEMS_GROUPS_FILTER_UNFILED', 5);
define('ITEMS_GROUPS_FILTER_ALL', 6);
define('GROUPS_FILTER_CATEGORY', 7);
define('GROUPS_FILTER_UNFILED', 8);
define('GROUPS_FILTER_ALL', 9);
define('ITEMS_FILTER_NO_RESULTS', 10);
define('ITEMS_FILTER_MISSING', 11);
define('ITEMS_FILTER_VENDOR', 12);
define('ITEMS_FILTER_RULE', 13);

// }}}
// {{{ class FF_DataAccess_Item 

// }}}
class FF_DataAccess_Item_mysql extends FF_DataAccess {
    // {{{ properties

    var $attributesTable;

    var $categoriesTable;

    var $ruleItemsTable;

    var $userId;

    var $filterName;

    // }}}
    // {{{ constructor

    function FF_DataAccess_Item_mysql()
    {
        FF_DataAccess::FF_DataAccess();
        $this->table = $this->o_registry->getConfigParam('data/items_table');
        $this->attributesTable = $this->o_registry->getConfigParam('data/item_attributes_table');
        $this->categoriesTable = $this->o_registry->getConfigParam('data/categories_table');
        $this->ruleItemsTable = $this->o_registry->getConfigParam('data/rule_items_table');
        $this->userId = FF_Auth::getCredential('userId'); 
        $this->o_log =& new FF_Model_Log();
    }

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

    function update($in_data)
    {
        $a_attrib = $in_data['attributes'];
        unset($in_data['attributes']);
        unset($in_data['isGroup']);
        unset($in_data['newComment']);
        unset($in_data['commentsToRemove']);
        unset($in_data['rules']);
        $o_result =& parent::update($in_data);
        if ($o_result->isSuccess()) {
            $tmp_result =& $this->o_log->newLogEntry('UPDATE', $in_data['id'], $this->table);
        }

        $tmp_result =& $this->updateAttributes($in_data['id'], $a_attrib);
        if (!$tmp_result->isSuccess()) {
            $o_result->addMessage(_('Unable to add item attributes.'));
            $o_result->setSuccess(false);
        }

        return $o_result;
    }

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

    function add($in_data)
    {
        $a_attrib = $in_data['attributes'];
        unset($in_data['attributes']);
        unset($in_data['isGroup']);
        unset($in_data['newComment']);
        unset($in_data['commentsToRemove']);
        unset($in_data['rules']);
        $in_data['deleted'] = false;
        $o_result =& parent::add($in_data);
        if ($o_result->isSuccess()) {
            $tmp_result =& $this->o_log->newLogEntry('INSERT', $in_data['id'], $this->table);
        }

        $tmp_result =& $this->updateAttributes($in_data['id'], $a_attrib);
        if (!$tmp_result->isSuccess()) {
            $o_result->addMessage(_('Unable to add item attributes.'));
            $o_result->setSuccess(false);
        }

        return $o_result;
    }

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

    function remove($in_itemId)
    {
        $o_result = new FF_Result();
        // Return the item from any loan it is in first
        $o_dao =& FF_DataAccess::factory('Transaction');
        // This has the loan type constants
        require_once dirname(__FILE__) . '/Loan.php';
        $o_dao->returnItemsInTransaction(array(array('itemId' => $in_itemId)));
        $s_where = 'id=' . $this->o_data->quoteSmart($in_itemId);
        $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);
        }
        else {
            // Remove associated image
            $o_fileCache =& FF_FileCache::singleton();
            $o_fileCache->remove(array('subdir' => 'item_images', 'name' => $in_itemId), true);
            // Remove it from any groups
            $o_dao =& FF_DataAccess::factory('Group');
            $o_dao->removeItemFromAllGroups($in_itemId);
            $tmp_result =& $this->o_log->newLogEntry('DELETE', $in_itemId, $this->table);
        }

        return $o_result;
    }

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

    function getDataByPrimaryKey($in_id, $in_fields = '*')
    {
        $a_data = parent::getDataByPrimaryKey($in_id, $in_fields);
        if (count($a_data) != 0) {
            $s_query = "SELECT k, v FROM $this->attributesTable WHERE item_id=?"; 
            $a_data['attributes'] = $this->o_data->getAssoc($s_query, false, $in_id);
            $o_rulesDataAccess =& FF_DataAccess::factory('Rule');
            $a_data['rules'] = $o_rulesDataAccess->getRulesForItem($in_id);
        }

        return $a_data;
    }

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

    function getListData($in_where, $in_orderByField, $in_orderByDir, $in_fields = '*')
    {
        if (!empty($this->filterName)) {
            $s_query = $this->_getFilterQuery($in_where);
            $s_query .= " ORDER BY $in_orderByField " . $this->_getOrderByDirection($in_orderByDir);
            return $this->o_data->query($s_query);
        }
        else {
            return parent::getListData($in_where, $in_orderByField, $in_orderByDir, $in_fields); 
        }
    }

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

    function getListFilter($in_searchString, $in_searchFields, $in_filter, $in_filterData)
    {
        // Filter is needed in getListData as well
        $this->filterName = $in_filter;
        $s_filter = 'deleted != 1 AND ';
        if ($this->filterName == ITEMS_FILTER_NO_RESULTS) {
            return '0=1';
        }
        elseif ($this->filterName == ITEMS_FILTER_MISSING) {
            $s_filter .= sprintf('status = %s AND ', 
                    $this->o_data->quoteSmart(ITEM_STATUS_MISSING));
        }
        elseif ($this->filterName == ITEMS_FILTER_VENDOR) {
            $s_filter .= sprintf('vendor_id = %s AND ', 
                    $this->o_data->quoteSmart($in_filterData['vendor_id']));
        }
        elseif ($this->filterName == ITEMS_FILTER_RULE) {
            $s_filter .= sprintf('rule_id = %s AND ', 
                    $this->o_data->quoteSmart($in_filterData['rule_id']));
        }
        elseif ($this->filterName == ITEMS_FILTER_CATEGORY ||
                $this->filterName == ITEMS_GROUPS_FILTER_CATEGORY ||
                $this->filterName == GROUPS_FILTER_CATEGORY) {
            $s_filter .= sprintf('category_id = %s AND ', 
                    $this->o_data->quoteSmart($in_filterData['catId']));
        }
        elseif ($this->filterName == ITEMS_FILTER_UNFILED ||
                $this->filterName == ITEMS_GROUPS_FILTER_UNFILED ||
                $this->filterName == GROUPS_FILTER_UNFILED) {
            $in_filterData['tableName'] = 't1';
            $s_filter .= 't2.id IS NULL AND ';
        }

        // See if we should only show those items that are shopper viewable
        if ($in_filterData['shopperViewable']) {
            $s_filter .= 't1.shopper_viewable = 1 AND ';
        }

        // See if we need to restrict by store id.
        if (!empty($in_filterData['storeId'])) {
            $s_filter .= sprintf('(t1.store_id = %s', $this->o_data->quoteSmart($in_filterData['storeId']));
            // See if we want to include items that are marked as being in all stores
            if (!empty($in_filterData['includeAllStore'])) {
                $s_filter .= ' OR t1.store_id = 0';
            }

            $s_filter .= ') AND ';
        }


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

    // }}}
    // {{{ updateAttributes()

    function &updateAttributes($in_itemId, $in_attributes)
    {
        $o_result = new FF_Result();
        if (count($in_attributes)) {
            // First remove all attributes for the item 
            $s_stmt = $this->o_data->prepare("DELETE FROM $this->attributesTable WHERE item_id=?");
            if (DB::isError($result = $this->o_data->execute($s_stmt, $in_itemId))) {
                $o_result->addMessage($result->getMessage());
                $o_result->setSuccess(false);
                return $o_result;
            }

            // Add all attributes for the user
            $s_stmt = $this->o_data->autoPrepare($this->attributesTable, array('item_id', 'k', 'v'));
            $a_data = array();
            foreach ($in_attributes as $s_key => $s_val) {
                $a_data[] = array($in_itemId, $s_key, $s_val);
            }

            if (DB::isError($result = $this->o_data->executeMultiple($s_stmt, $a_data))) {
                $o_result->addMessage($result->getMessage());
                $o_result->setSuccess(false);
            } 
        }

        return $o_result;
    }

    // }}}
    // {{{ updateStatusOfItems()

    function &updateStatusOfItems($in_items, $in_status)
    {
        $o_result = new FF_Result();
        $s_itemList = '';
        foreach ($in_items as $s_item) {
            $s_itemList .= 'id = ' . $this->o_data->quoteSmart($s_item) . " OR \n";
        }

        $s_itemList .= '0=1';
        $result = $this->o_data->autoExecute($this->table, array('status' => $in_status), DB_AUTOQUERY_UPDATE, $s_itemList);
        if (DB::isError($result)) {
            $o_result->addMessage($result->getMessage());
            $o_result->setSuccess(false);
        }

        return $o_result;
    }

    // }}}
    // {{{ getItemIdByBarcode()

    function getItemIdByBarcode($in_barcode)
    {
        $s_query = "SELECT id FROM $this->table WHERE barcode=? AND deleted=0";
        return $this->o_data->getOne($s_query, $in_barcode);
    }

    // }}}
    // {{{ isBarcodeUnique()

    function isBarcodeUnique($in_barcode, $in_id, $in_isUpdate)
    {
        $s_where = 'AND deleted != 1';
        return parent::isDataUnique('barcode', $in_barcode, $in_id, $in_isUpdate, $s_where);
    }

    // }}}
    // {{{ getNextId()

    function getNextId()
    {
        // change sequence name since we give it the full table name
        $this->o_data->setOption('seqname_format', '%s');
        return $this->o_data->nextId($this->o_registry->getConfigParam('data/sequence_table'));
    }

    // }}}
    // {{{ _getFilterQuery()

    function _getFilterQuery($in_where)
    {
        if ($this->filterName == GROUPS_FILTER_CATEGORY ||
            $this->filterName == GROUPS_FILTER_UNFILED ||
            $this->filterName == GROUPS_FILTER_ALL) {
            return $this->_getGroupListQuery($in_where);
        }
        // Filter: gettting both items and groups
        elseif ($this->filterName == ITEMS_GROUPS_FILTER_CATEGORY ||
                $this->filterName == ITEMS_GROUPS_FILTER_UNFILED ||
                $this->filterName == ITEMS_GROUPS_FILTER_ALL ||
                $this->filterName == ITEMS_FILTER_RULE) {
            return $this->_getItemGroupListQuery($in_where);
        }
        // Filter: gettting only items
        else {
            return $this->_getItemListQuery($in_where);
        }
    }

    // }}}
    // {{{ _getGroupListQuery()

    function _getGroupListQuery($in_where)
    {
        $s_join = '';
        $s_field = '';
        if ($this->filterName == GROUPS_FILTER_UNFILED ||
            $this->filterName == ITEMS_GROUPS_FILTER_UNFILED) {
            // Have to alias the field so it doesn't overwrite 'id'
            $s_field = ', t2.id AS catId';
            $s_join = "LEFT JOIN $this->categoriesTable AS t2 ON t1.category_id = t2.id";
        }
        elseif ($this->filterName == ITEMS_FILTER_RULE) {
            $s_join = "LEFT JOIN $this->ruleItemsTable AS t2 ON t1.id = t2.item_id";
        }

        $s_groupsTable = $this->o_registry->getConfigParam('data/groups_table');
        // Make it always match deleted clause (F != T)
        $in_where = str_replace('deleted !=', '0 !=', $in_where);
        // Remove status, barcode, and serial num clause since it doesn't have those fields
        $in_where = preg_replace('/(t1\.)?`status` LIKE \'%.*?%\'/s', '0=1', $in_where);
        $in_where = preg_replace('/(t1\.)?`barcode` LIKE \'%.*?%\'/s', '0=1', $in_where);
        $in_where = preg_replace('/(t1\.)?`serial_num` LIKE \'%.*?%\'/s', '0=1', $in_where);
        $s_na = $this->o_data->quoteSmart(_('N/A'));
        return "SELECT t1.id, t1.name, t1.description, $s_na AS barcode, -1 AS status, 
                $s_na AS serial_num, 1 AS isGroup $s_field
                FROM $s_groupsTable AS t1 $s_join WHERE $in_where";
    }

    // }}}
    // {{{ _getItemListQuery()

    function _getItemListQuery($in_where)
    {
        $s_join = '';
        $s_field = '';
        if ($this->filterName == ITEMS_FILTER_UNFILED ||
            $this->filterName == ITEMS_GROUPS_FILTER_UNFILED) {
            // Have to alias the field so it doesn't overwrite 'id'
            $s_field = ', t2.id AS catId';
            $s_join = "LEFT JOIN $this->categoriesTable AS t2 ON t1.category_id= t2.id";
        }
        elseif ($this->filterName == ITEMS_FILTER_RULE) {
            $s_join = "LEFT JOIN $this->ruleItemsTable AS t2 ON t1.id = t2.item_id";
        }

        return "SELECT t1.id, t1.name, t1.description, t1.barcode, t1.status, 
                t1.serial_num, 0 AS isGroup $s_field 
                FROM $this->table AS t1 $s_join WHERE $in_where";
    }

    // }}}
    // {{{ _getItemGroupListQuery()

    function _getItemGroupListQuery($in_where)
    {
        return sprintf('(%s) UNION ALL (%s)', 
                $this->_getItemListQuery($in_where),
                $this->_getGroupListQuery($in_where));
    }

    // }}}
}
?>

---