Location: PHPKode > scripts > Impleo Music Collection Script > impleo/classes/Factory.class.php
<?php
/* ----------------------------------------------------------------------------
 * "THE BEER-WARE LICENSE" (Revision 42):
 * <hide@address.com> wrote this file. As long as you retain this notice you
 * can do whatever you want with this stuff. If we meet some day, and you think
 * this stuff is worth it, you can buy me a beer in return Christoffer Kjeldgaard Petersen
 * ----------------------------------------------------------------------------
 */

require_once(dirname(__FILE__) . '/Database.class.php');
require_once(dirname(__FILE__) . '/MusicItem.class.php');
    
class Factory
{
    private $db;
    private static $instance;
    private $tblStart;
    private $detailInfo;

    /**
     * Array of allowed characters for keys in detail types.
     */
    protected $ARR_ALLOWED_CHARS = array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "x", "y", "z");

    protected function __construct()
    {
        $this->db = Database::getInstance();
        $this->tblStart = Database::getTableStart();

        $sql = "SELECT * FROM {$this->tblStart}detail_types ORDER BY `order`+0";
        $this->detailInfo = $this->db->fetch($sql);
    }

    /**
     * Singleton
     * @return <Factory> Facotory-instance
     */
    public static function getInstance()
    {
        if (!self::$instance instanceof self)
        {
            self::$instance = new self;
        }
        return self::$instance;
    }

    /**
     * Changes the password for a user.
     *
     * @param string $user Username of the user.
     * @param string $currentPassword The user's current password.
     * @param string $newPassword The new password.
     * @return true|string Returns true if the password was changed or an error message.
     */
    public function changePassword($user, $currentPassword, $newPassword)
    {
        if (empty($currentPassword) || empty($newPassword))
            return "Current and new password must be set.";

        $user = $this->db->escapeString($user);
        $current = md5($this->db->escapeString($currentPassword));
        $sql = "SELECT COUNT(*) AS count FROM {$this->tblStart}user WHERE username = '$user' AND password = '$current'";
        $arrRs = $this->db->fetchRow($sql);

        if ($arrRs['count'] != '1')
            return "Current password not correct.";
        else
        {
            $new = md5($newPassword);
            $sqlUpdate = "UPDATE {$this->tblStart}user SET password = '{$new}' WHERE username = '$user'";
            $this->db->query($sqlUpdate);

            return true;
        }
    }

    /**
     * Deletes an existing detail type.
     *
     * @param string $name Current ID (name) of the detail type to delete.
     */
    public function deleteDetailType($name)
    {
        $name = $this->db->escapeString(htmlspecialchars($name));
        $sqlGetOrder = "SELECT `order` FROM {$this->tblStart}detail_types WHERE name = '$name'";
        $arrOrder = $this->db->fetchRow($sqlGetOrder);
        $order = $arrOrder['order'];

        $sqlDeleteInfo = "DELETE FROM `{$this->tblStart}info_value` WHERE `detail` = '{$name}'";
        $this->db->query($sqlDeleteInfo);

        $sqlDeleteAdd = "DELETE FROM `{$this->tblStart}detail_types` WHERE `name` = '{$name}'";
        $this->db->query($sqlDeleteAdd);

        $sqlUpdateOrder = "UPDATE {$this->tblStart}detail_types SET `order` = `order`-1 WHERE `order` > {$order}";
        $this->db->query($sqlUpdateOrder);
    }

    /**
     * Deletes an image of a record.
     *
     * @param int $recordID ID of the record
     */
    public function deleteImage($recordID)
    {
        $recordID = $this->db->escapeString($recordID);
        $imgName = $this->getImageNameByItemID($recordID);
        if ($imgName)
        {
            @unlink("../images/uploads/" . $imgName);
            $sqlDelete = "DELETE FROM `{$this->tblStart}image` WHERE `id` = {$recordID}";
            $this->db->query($sqlDelete);
            $this->setItemUpdated($recordID);
        }
    }

    /**
     * Deletes a record.
     *
     * @param <int> $recordID ID of the record to delete.
     * @return <bool> True if deleted. False otherwise.
     */
    public function deleteRecord($recordID)
    {
        if (is_numeric($recordID))
        {
            $sqlTypes = "DELETE FROM `{$this->tblStart}info_value` WHERE item = '{$recordID}'";
            $this->db->query($sqlTypes);

            $sqlDelete = "DELETE FROM {$this->tblStart}music_item WHERE id = '{$recordID}'";
            $this->db->query($sqlDelete);
            return true;
        }
        else
            return false;
    }

    /**
     * Gets all the detail types specified for the collection.
     * @return array An array of detail info types.
     */
    public function getDetailInfo()
    {
        return $this->detailInfo;
    }

    /**
     * Get detail values for a specific record item.
     *
     * @param int $itemID ID of the item
     * @return array Array with detail values
     */
    private function getDetailArrayForItem($itemID)
    {
        $arrDetails = $this->getDetailInfo();
        $arrItemsInfo = array();
        if (count($arrDetails) > 0)
        {
            $sqlValue = "SELECT detail, value FROM {$this->tblStart}info_value WHERE item = '{$itemID}'";
            $arrValue = $this->db->fetch($sqlValue);
            $arrValues = array();
            for($i = 0; $i < count($arrValue); $i++)
                $arrValues[$arrValue[$i]['detail']] = $arrValue[$i]['value'];

            foreach ($arrDetails as $arrDetail)
            {
                $arrItemsInfo[$arrDetail['key']] = $arrValues[$arrDetail['name']];
            }
        }
        return $arrItemsInfo;
    }

    /**
     * Counts the records with a specific attribute in the collection.
     *
     * @param string $attribute
     * @return int Number of records
     */
    public function getAttributeCount($attribute)
    {
        $attribute = $this->db->escapeString($attribute);
        if ($attribute == "artist" || $attribute == "title" || $attribute == "year")
            $sql = "SELECT COUNT(*) AS count FROM {$this->tblStart}music_item GROUP BY {$attribute}";
        else
            $sql = "SELECT COUNT(*) AS count FROM {$this->tblStart}info_value v INNER JOIN {$this->tblStart}detail_types a ON v.detail = a.name WHERE a.key = '{$attribute}' GROUP BY v.value";

        $arrCount = $this->db->fetch($sql);
        return count($arrCount);
    }

    public function getAttributeListCount($attribute, $intLimit='')
    {
        $attribute = $this->db->escapeString($attribute);

        $limit = "";
        if (is_numeric($intLimit))
            $limit = " LIMIT " . $intLimit;

        if ($attribute == "artist" || $attribute == "title" || $attribute == "year")
            $sql = "SELECT {$attribute}, COUNT(*) AS count FROM {$this->tblStart}music_item GROUP BY {$attribute} ORDER BY count DESC{$limit}";
        else
            $sql = "SELECT v.value AS {$attribute}, COUNT(*) AS count FROM {$this->tblStart}info_value v INNER JOIN {$this->tblStart}detail_types a ON v.detail = a.name WHERE a.key = '{$attribute}' GROUP BY v.value ORDER BY count DESC{$limit}";
      
        $arrRS = $this->db->fetch($sql);

        if (count($arrRS) > 0)
        {
            $arrList = array();
            foreach($arrRS as $item)
                $arrList[$item[$attribute]] = $item['count'];
            return $arrList;
        }
        else
            return null;
    }

    /**
     * Gets the general settings for the system.
     * @return <array> Array of general settings for the system.
     */
    public function getGeneralSettings()
    {
        $sql = "SELECT * FROM {$this->tblStart}settings WHERE id = '1'";
        return $this->db->fetchRow($sql);
    }

    /**
     * Gets the image name for a record
     * @param int $id ID of the record
     * @return The image name if found. Null otherwise.
     */
    public function getImageNameByItemID($id)
    {
        $id = $this->db->escapeString($id);
        $sqlImg = "SELECT img_name FROM {$this->tblStart}image WHERE id = '{$id}'";
        $arrImg = $this->db->fetchRow($sqlImg);
        if (count($arrImg) > 0)
            return $arrImg['img_name'];
        return null;
    }

    /**
     * Gets a specific music item by ID.
     *
     * @param int $id ID of the record
     * @return A music item if found. Null otherwise.
     */
    public function getMusicItem($id)
    {
        $id = $this->db->escapeString($id);

        if (is_numeric($id))
        {
            $sqlGetItem = "SELECT * FROM {$this->tblStart}music_item WHERE id = '{$id}'";
            $arrItem = $this->db->fetchRow($sqlGetItem);
            if (count($arrItem) > 0)
            {
                $arrItemsInfo = $this->getDetailArrayForItem($arrItem['id']);
                $img = $this->getImageNameByItemID($arrItem['id']);
                return new MusicItem($arrItem['id'], stripslashes($arrItem['artist']), stripslashes($arrItem['title']), $arrItem['year'], $arrItem['date_added'], $arrItem['date_updated'],$arrItemsInfo, $img);
            }
            else
            return null;
        }
        return null;
    }

    /**
     * Gets all music items in the collection.
     *
     * @param bool $getImages Get image name. Default = false.
     * @return An array of music item objects.
     */
    public function getMusicItems($getImages=false)
    {
        $sqlGetItems = "SELECT * FROM {$this->tblStart}music_item i ";
        $sqlGetItems.= "ORDER BY artist, year";

        $arrItems = $this->db->fetch($sqlGetItems);
        $arrMusicItems = array();
        
        if (count($arrItems) > 0)
        {
            foreach ($arrItems as $arrItem)
            {
                $arrItemsInfo = $this->getDetailArrayForItem($arrItem['id']);
                if ($getImages)
                    $img = $this->getImageNameByItemID($arrItem['id']);
                else
                    $img = '';
                $musicItem = new MusicItem($arrItem['id'], stripslashes($arrItem['artist']), stripslashes($arrItem['title']), $arrItem['year'], $arrItem['date_added'], $arrItem['date_updated'], $arrItemsInfo, $img);
                $arrMusicItems[] = $musicItem;
            }
        }
        return $arrMusicItems;
    }

    /**
     * Gets the total number of music items in the collection
     *
     * @return int Number of music items
     */
    public function getNumberOfItems()
    {
        $sql = "SELECT COUNT(*) AS count FROM {$this->tblStart}music_item";
        $arrRs = $this->db->fetchRow($sql);
        return $arrRs['count'];
    }

    /**
     * Inserts new detail type for the collection's records.
     *
     * @param string $name Name of new detail type.
     * @param bool $required 1 if detail type must be specified for new records. 0 otherwise.
     * @param bool $show 1 if the detail type should be visible in the main table. 0 otherwise.
     * @return string Returns "ok" if type was inserted or an error message on false.
     */
    public function insertDetailType($name, $required, $show)
    {
        $arrDetails = $this->getDetailInfo();

        foreach ($arrDetails as $arrDetail)
        {
            if ($arrDetail['name'] == $name)
                return "Detail type already exists.";
        }

        $nameLower = strtolower($name);
        $key = "";
        for ($i = 0; $i < strlen($nameLower); $i++)
        {
            if (in_array($nameLower{$i}, $this->ARR_ALLOWED_CHARS))
            $key.= $nameLower{$i};
        }
        if (strlen($key) == 0)
            return "Detail type name must contain alphabetic characters (a-z).";

        $name = $this->db->escapeString(htmlspecialchars($name));
        $order = count($arrDetails)+1;
        $sql = "INSERT INTO {$this->tblStart}detail_types (`name`, `key`, `required`, `show_in_table`, `order`) VALUES ('$name', '$key', '$required', '$show', '$order')";
        $this->db->query($sql);
        return "ok";
    }

    /**
     * Inserts a new record in the collection.
     *
     * @param string $artist Name of the artist
     * @param string $title Title of the record
     * @param int $year Year of release
     * @param array $arrDetails Array of detail info of the record
     * @param string $imageName [optional] Name of the image file.
     */
    public function insertRecord($artist, $title, $year, $arrDetails, $imageName=null)
    {
        $artist = $this->db->escapeString(htmlspecialchars($artist));
        $title = $this->db->escapeString(htmlspecialchars($title));
        $year = $this->db->escapeString(htmlspecialchars($year));

        $sqlItemInsert = "INSERT INTO {$this->tblStart}music_item (artist, title, year, date_added) VALUES ('{$artist}', '{$title}', '{$year}', NOW())";
        $intLastID = $this->db->query($sqlItemInsert);
        //$intLastID = mysql_insert_id();

        foreach ($arrDetails as $k => $v)
        {
            $value = $this->db->escapeString(htmlspecialchars($v));
            $sql = "INSERT INTO {$this->tblStart}info_value (item, detail, value) VALUES ('{$intLastID}', '{$k}', '{$value}')";
            $this->db->query($sql);
        }

        if ($imageName)
        {
            $sqlImg = "INSERT INTO {$this->tblStart}image (id, img_name) VALUES ('{$intLastID}', '{$imageName}')";
            $this->db->query($sqlImg);
        }
    }

    /**
     * Finds whether a music item exists or not.
     * @param int $itemID ID of the music item.
     * @return bool Returns true if the item exists, false otherwise.
     */
    public function itemExists($itemID)
    {
        $itemID = $this->db->escapeString(htmlspecialchars($itemID));
        $sqlGet = "SELECT COUNT(*) AS count FROM {$this->tblStart}music_item WHERE id = '$itemID'";
        $arrRs = $this->db->fetchRow($sqlGet);

        if ($arrRs['count'] == '1')
            return true;
        else
            return false;
    }

    /**
     * Login to admin
     *
     * @param string $username Username
     * @param string $password Password
     * @return bool Returns true if username and password is correct, false otherwise.
     */
    public function loginUser($username, $password)
    {
        $user = $this->db->escapeString($username);
        $pass = md5($this->db->escapeString($password));

        $sql = "SELECT COUNT(*) AS count FROM {$this->tblStart}user WHERE username = '$user' AND password = '$pass'";
        $arrRs = $this->db->fetchRow($sql);

        if ($arrRs['count'] == '1')
            return true;
        else
            return false;
    }

    /**
     * Moves detail type item down in the detail queue.
     *
     * @param int $order Current order of the item to move down.
     */
    public function moveDetailTypeDown($order)
    {
        $arrDetails = $this->getDetailInfo();
        if ($order < count($arrDetails)-1)
        {
            $name = $arrDetails[$order]['name'];
            $newOrderOrg =  $order+2;
            $newOrderDowner =  $newOrderOrg-1;
            $nameDowner = $arrDetails[$order+1]['name'];

            $sqlDowner = "UPDATE {$this->tblStart}detail_types SET `order` = '{$newOrderDowner}' WHERE name = '{$nameDowner}'";
            $sql = "UPDATE {$this->tblStart}detail_types SET `order` = '{$newOrderOrg}' WHERE name = '{$name}'";

            $this->db->query($sqlDowner);
            $this->db->query($sql);
        }
    }

    /**
     * Moves detail type item up in the detail queue.
     *
     * @param int $order Current order of the item to move up.
     */
    public function moveDetailTypeUp($order)
    {
        $arrDetails = $this->getDetailInfo();
        if ($order > 0 && $order < count($arrDetails))
        {
            $name = $arrDetails[$order]['name'];
            $orderUpper =  $order+1;
            $nameUpper = $arrDetails[$order-1]['name'];

            $sqlUpper = "UPDATE {$this->tblStart}detail_types SET `order` = '{$orderUpper}' WHERE name = '{$nameUpper}'";
            $sql = "UPDATE {$this->tblStart}detail_types SET `order` = '{$order}' WHERE name = '{$name}'";

            $this->db->query($sqlUpper);
            $this->db->query($sql);
        }
    }

    /**
     * Search for records in the collection.
     *
     * @param string $query Query to search for.
     * @param string $term Search term like artist, title, year or any detail info.
     * @return array Array of music items
     */
    public function searchMusic($query, $term)
    {
        $q = $query;
        $q = $this->db->escapeString($query);
        $t = $this->db->escapeString($term);

        if ($term == "artist" || $term == "title" || $term == "year")
            $sql = "SELECT * FROM {$this->tblStart}music_item WHERE {$t} LIKE '%{$q}%' ORDER BY {$t}";
        else
        {
            $sql = "SELECT i.* ";
            $sql.= "FROM {$this->tblStart}info_value v ";
            $sql.= "INNER JOIN {$this->tblStart}detail_types a ON a.name = v.detail ";
            $sql.= "INNER JOIN {$this->tblStart}music_item i ON v.item = i.id ";
            $sql.= "WHERE v.value LIKE '%{$q}%' AND a.key = '{$t}' ";
            $sql.= "ORDER BY v.detail";
        }

        $arrItems = $this->db->fetch($sql);
        $arrMusicItems = array();
        if (count($arrItems) > 0)
        {
            foreach ($arrItems as $arrItem)
            {
                $arrItemsInfo = $this->getDetailArrayForItem($arrItem['id']);               
                $musicItem = new MusicItem($arrItem['id'], $arrItem['artist'], $arrItem['title'], $arrItem['year'], $arrItem['date_added'], $arrItem['date_updated'], $arrItemsInfo, '');
                $arrMusicItems[] = $musicItem;
            }
        }
        return $arrMusicItems;
    }

    /**
     * Sets the update time of a record to the current time.
     * @param <int> $recordID ID of the record to update.
     */
    public function setItemUpdated($recordID)
    {
        $id = $this->db->escapeString($recordID);
        $sql = "UPDATE {$this->tblStart}music_item SET date_updated = NOW() WHERE id = '{$id}'";
        $this->db->query($sql);
    }

    /**
     * Updated an detail type.
     *
     * @param string $id Current ID (name) of the detail type to update.
     * @param string $name New name of new detail type.
     * @param bool $required 1 if detail type must be specified for new records. 0 otherwise.
     * @param bool $show 1 if the detail type should be visible in the main table. 0 otherwise.
     * @return string Returns "ok" if type was inserted or an error message on false.
     */
    public function updateDetailType($id, $name, $required, $show)
    {
        $arrDetails = $this->getDetailInfo();

        foreach ($arrDetails as $arrDetail)
        {
            if ($arrDetail['name'] == $name && $name != $id)
            return "Attribute already exists.";
        }

        $nameLower = strtolower($name);
        $key = "";
        for ($i = 0; $i < strlen($nameLower); $i++)
        {
            if (in_array($nameLower{$i}, $this->ARR_ALLOWED_CHARS))
            $key.= $nameLower{$i};
        }
        if (strlen($key) == 0)
            return "Detail name must contain alphabetic characters (a-z).";

        $name = $this->db->escapeString(htmlspecialchars($name));
        $id = $this->db->escapeString(htmlspecialchars($id));
        $sql = "UPDATE {$this->tblStart}detail_types SET `name` = '{$name}', `key` = '{$key}', `required` = '{$required}', `show_in_table` = '{$show}' WHERE `name` = '{$id}'";
        $this->db->query($sql);
        return "ok";
    }

    /**
     * Updates the general settings for the system.
     *
     * @param <string> $collectionName Name of the collection.
     * @param <int> $pageLimit Number of records per page. 0 for unlimited.
     * @param <bool> $link Link to record details in main table.
     * @param <int> $imgWidth Resize uploaded images to this width.
     */
    public function updateGeneralSettings($collectionName, $pageLimit, $link, $imgWidth)
    {
        $name = $this->db->escapeString(htmlspecialchars($collectionName));
        if (!is_numeric($pageLimit))
            $pageLimit = 10;
        if (!is_numeric($imgWidth))
            $imgWidth = 300;
        if ($link)
            $l = '1';
        else
            $l = '0';

        $sqlUpdate = "UPDATE {$this->tblStart}settings SET ";
        $sqlUpdate.= "name = '{$name}', ";
        $sqlUpdate.= "records_per_page = '{$pageLimit}', ";
        $sqlUpdate.= "link = '{$l}', ";
        $sqlUpdate.= "image_width = '{$imgWidth}' ";
        $sqlUpdate.= "WHERE id = '1'";
        $this->db->query($sqlUpdate);
    }

    /**
     * Updates an item.
     *
     * @param int $id ID of the record to update
     * @param string $artist Artist name
     * @param string $title Title of item
     * @param int $year Year of release
     * @param array $arrDetails Array of detail for the item
     * @param string $imageName Filename of image
     */
    public function updateRecord($id, $artist, $title, $year, $arrDetails, $imageName)
    {
        $currentItem = $this->getMusicItem($id);

        $artist = $this->db->escapeString(htmlspecialchars($artist));
        $title = $this->db->escapeString(htmlspecialchars($title));
        $year = $this->db->escapeString(htmlspecialchars($year));

        $sqlUpdateItem = "UPDATE {$this->tblStart}music_item SET ";
        $sqlUpdateItem.= "artist = '{$artist}', ";
        $sqlUpdateItem.= "title = '{$title}', ";
        $sqlUpdateItem.= "year = '{$year}', ";
        $sqlUpdateItem.= "date_updated = NOW() ";
        $sqlUpdateItem.= "WHERE id = '$id'";
        $this->db->query($sqlUpdateItem);

        foreach ($this->getDetailInfo() as $attribute)
        {
            $sqlDel = "DELETE FROM {$this->tblStart}info_value WHERE item = '{$id}' AND detail = '{$attribute['name']}'";
            $this->db->query($sqlDel);

            if (array_key_exists($attribute['name'], $arrDetails))
            {
                $sql = "INSERT INTO {$this->tblStart}info_value (item, detail, value) VALUES ('{$id}', '{$attribute['name']}', '{$arrDetails[$attribute['name']]}')";
                $this->db->query($sql);
            }
        }

        if ($imageName != null)
        {
            if ($currentItem->getImageName())
            {
                $sqlImg = "UPDATE {$this->tblStart}image SET img_name = '{$imageName}' WHERE id = '{$id}'";
                $this->db->query($sqlImg);
            }
            else if ($imageName)
            {
                $sqlImg = "INSERT INTO {$this->tblStart}image (id, img_name) VALUES ('{$id}', '{$imageName}')";
                $this->db->query($sqlImg);
            }
        }
    }

} // End class
?>
Return current item: Impleo Music Collection Script