root/Chameleon/trunk/Chameleon/SQLQuery/SQLQuery.widget.php

<?php
/**
 * SQLQuery Widget class
 *
 * @project     CWC2
 * @revision    $Id: 
 * @purpose     SQLQuery Widget class
 * @author      DM Solutions Group (lacroix@dmsolutions.ca)
 * @copyright
 * <b>Copyright (c) 2003, DM Solutions Group Inc.</b>
 * Permission is hereby granted, free of charge, to any person obtaining a
 * copy of this software and associated documentation files (the "Software"),
 * to deal in the Software without restriction, including without limitation
 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included
 * in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL
 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
 * DEALINGS IN THE SOFTWARE.
 */


include_once(dirname(__FILE__)."/../Widget.php");
include_once(dirname(__FILE__)."/../SharedResource/SharedResource.widget.php");

/**
 * SQLQueryWidget
 *
 * @desc Widget That return the result of a given SQL query
 */
class SQLQuery extends CWCWidget
{
    var $mszDBType = "MYSQL";
    var $mszSQLQuery = "";
    var $mszServer   = "";
    var $mszDatabase = "";
    var $mszUserName = "";
    var $mszPassword = "";
    var $mszSharedResourceName = "SQLQuery";
    var $mbSaveQuery = true;
    var $mbRandomResult = false;

    var $maszFilters = array();

    /**
     * SQLQueryWidget
     *
     * Constructor method for the SQLQuery widget.
     */
    function SQLQuery()
    {
        // invoke constructor of parent
        parent::CWCWidget();

        // set the description for this widget
        $this->szWidgetDescription = <<<EOT
SQLQueryWidget is a simple widget that simply return the return value of a given SQL query.
EOT;

        if ($this->mszSQLQuery == "" ) 
            $this->maAttributes["SQLQUERY"] = new StringAttribute( "SQLQUERY", true );

        if ($this->mszServer == "")
            $this->maAttributes["SERVER"] = new StringAttribute( "SERVER", true );

        if ($this->mszDatabase == "")
            $this->maAttributes["DATABASE"] = new StringAttribute( "DATABASE", true );

        if ($this->mszUserName == "")
            $this->maAttributes["USERNAME"] = new StringAttribute( "USERNAME", true );

        if ($this->mszPassword == "")
            $this->maAttributes["PASSWORD"] = new StringAttribute( "PASSWORD", true );

        $this->maAttributes["DBTYPE"] = new StringAttribute( "DBTYPE", false );
        $this->maAttributes["SHAREDRESOURCENAME"] = new StringAttribute( "SHAREDRESOURCENAME", false );
        $this->maAttributes["SAVEQUERY"] =  new BooleanAttribute( "SAVEQUERY", false );

        $this->mnPriority = PRIORITY_SUPER;
        $this->mnMaturityLevel = MATURITY_BETA;
    }

    /**
     * initialize respectable defaults
     */
    function InitDefaults()
    {
        parent::InitDefaults();

        if(isset($this->maParams["DBTYPE"]))
            $this->mszDBType = strtoupper($this->maParams["DBTYPE"]);
        if(isset($this->maParams["SQLQUERY"]))
            $this->mszSQLQuery = $this->maParams["SQLQUERY"];
        if(isset($this->maParams["SERVER"]))
            $this->mszServer = $this->maParams["SERVER"];
        if(isset($this->maParams["DATABASE"]))
            $this->mszDatabase = $this->maParams["DATABASE"];
        if(isset($this->maParams["USERNAME"]))
            $this->mszUserName = $this->maParams["USERNAME"];
        if(isset($this->maParams["PASSWORD"]))
            $this->mszPassword = $this->maParams["PASSWORD"];
        if(isset($this->maParams["SHAREDRESOURCENAME"]))
            $this->mszSharedResourceName=$this->maParams["SHAREDRESOURCENAME"];

        if (isset($this->maParams["SAVEQUERY"]))
            $this->mbSaveQuery = (strtoupper($this->maParams["SAVEQUERY"]) == "FALSE") ? false:true;

        if (isset($this->maParams["RANDOMRESULT"]))
            $this->mbRandomResult = 
                (strtoupper($this->maParams["RANDOMRESULT"])=="TRUE") ? true : false;

        // Get all filters
        if (isset($this->maszContents['FILTERS']))
        {
            foreach ($this->maszContents['FILTERS'] as $oFilter)
            {
                if (isset($oFilter['ID']) && isset($oFilter['FILTER']))
                {
                    $this->maszFilters[$oFilter['ID']] = array("FILTER" => $oFilter['FILTER']);

                    if (isset($oFilter['TABLE']) && isset($oFilter['LINK']))
                    {
//                        array_push($this->maszFilter[$oFilter['ID']], "TABLE" => $oFilter['TABLE'],
//                        "LINK" => $oFilter['LINK']);
                    }
                }
            }
        }
    }

    /**
     * Connect to the database and run the query
     */
    function ParseURL()
    {
        // Replace dynamic values in the SQL query
        $this->ParseSQLQuery();

        $szQueryType = strtoupper(substr($this->mszSQLQuery, 0, 6));

        if($this->mbSaveQuery && $szQueryType == "SELECT" &&
           isset($_SESSION["SessSQLQuery"][$this->mszSQLQuery]))
        {
            $this->maSharedResourceWidgets[$this->mszSharedResourceName] = 
                $_SESSION["SessSQLQuery"][$this->mszSQLQuery];

            // Add the number of Row/Col in the SharedResource
            $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumRows"] = 
                count($this->maSharedResourceWidgets[$this->mszSharedResourceName]);
            if(isset($this->maSharedResourceWidgets[$this->mszSharedResourceName][0]))
                $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 
                    count($this->maSharedResourceWidgets[$this->mszSharedResourceName][0]);
            else
                $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 0;

            if(count($this->maSharedResourceWidgets[$this->mszSharedResourceName]) == 0)
            {
                $szErrorLevel = $this->ProcessEvent("NoRowReturned");
                if($szErrorLevel == ONEVENT_FATAL)
                    return false;
            }

            return true;
        }

        switch ($this->mszDBType)
        {
          case "MYSQL":
            // Connect to the database
            $link = @mysql_connect($this->mszServer, $this->mszUserName, 
                                  $this->mszPassword);
            if($link == false)
            {
                $this->ProcessEvent("ConnectFailed");
                return false;
            }
            if(!mysql_select_db($this->mszDatabase, $link))
            {
                $this->ProcessEvent("SelectDBFailed");
                return false;
            }

            // Run the SQL query
            $hRes = mysql_query($this->mszSQLQuery, $link);

            if($hRes == false)
            {
                $this->ProcessEvent("QueryFailed");
                return false;
            }

            switch($szQueryType)
            {
              case "SELECT":
                // Get all returned rows and put them in a Shared Resource
                $i = 0;
                $aMySQLResult = array();
                while ($aRow = mysql_fetch_array($hRes, MYSQL_ASSOC))
                {
                    $aMySQLResult[$i] = $aRow;
                    $i++;
                }

                if ($this->mbRandomResult)
                {
                    srand((float)microtime()*1000000);
                    shuffle($aMySQLResult);
                }

                $this->maSharedResourceWidgets[$this->mszSharedResourceName] =
                    $aMySQLResult;

                // Add the number of Row/Col in the SharedResource
                $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumRows"] = 
                    count($this->maSharedResourceWidgets[$this->mszSharedResourceName]);

                if (count($this->maSharedResourceWidgets[$this->mszSharedResourceName])>0)
                    $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 
                        count($this->maSharedResourceWidgets[$this->mszSharedResourceName][0]);
                else
                    $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 0;

                if($this->mbSaveQuery)
                {
                    $_SESSION["SessSQLQuery"][$this->mszSQLQuery] = 
                        $aMySQLResult;
                }

                if(($numRows = mysql_num_rows($hRes)) <= 0)
                {
                    $szErrorLevel = $this->ProcessEvent("NoRowReturned");
                    if($szErrorLevel == ONEVENT_FATAL)
                        return false;
                }

                break;
              case "INSERT":
              case "UPDATE":
              case "DELETE":
                if(($numRows = mysql_affected_rows($link)) <= 0)
                {
                    $szErrorLevel = $this->ProcessEvent("NoRowReturned");
                    if($szErrorLevel == ONEVENT_FATAL)
                        return false;
                }
                break;
            }

            // Close the connection
            mysql_close($link);
            break;
        case "PGSQL":
            // Connect to the database
            $szConnect = '';
            $szConnect .= ($this->mszServer != '')?"host=$this->mszServer ":'';
            $szConnect .= ($this->mszServer != '')?"user=$this->mszUserName ":'';
            $szConnect .= ($this->mszServer != '')?"password=$this->mszPassword ":'';
            $szConnect .= ($this->mszServer != '')?"dbname=$this->mszDatabase ":'';
            $link = @pg_connect($szConnect);
            if($link == false)
            {
                $this->ProcessEvent("ConnectFailed");
                return false;
            }
//            if(!mysql_select_db($this->mszDatabase, $link))
//            {
//                $this->ProcessEvent("SelectDBFailed");
//                return false;
//            }

            // Run the SQL query
            $hRes = pg_query($link, $this->mszSQLQuery);

            if($hRes == false)
            {
                $this->ProcessEvent("QueryFailed");
                return false;
            }

            switch($szQueryType)
            {
              case "SELECT":
                // Get all returned rows and put them in a Shared Resource
                $i = 0;
                $aResult = array();
                while ($aRow = pg_fetch_assoc($hRes))
                {
                    $aResult[$i] = $aRow;
                    $i++;
                }

                if ($this->mbRandomResult)
                {
                    srand((float)microtime()*1000000);
                    shuffle($aResult);
                }

                $this->maSharedResourceWidgets[$this->mszSharedResourceName] =
                    $aResult;

                // Add the number of Row/Col in the SharedResource
                $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumRows"] = 
                    count($this->maSharedResourceWidgets[$this->mszSharedResourceName]);

                if (count($this->maSharedResourceWidgets[$this->mszSharedResourceName])>0)
                    $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 
                        count($this->maSharedResourceWidgets[$this->mszSharedResourceName][0]);
                else
                    $this->maSharedResourceWidgets[$this->mszSharedResourceName."_NumCols"] = 0;

                if($this->mbSaveQuery)
                {
                    $_SESSION["SessSQLQuery"][$this->mszSQLQuery] = 
                        $aResult;
                }

                if(($numRows = pg_num_rows($hRes)) <= 0)
                {
                    $szErrorLevel = $this->ProcessEvent("NoRowReturned");
                    if($szErrorLevel == ONEVENT_FATAL)
                        return false;
                }

                break;
              case "INSERT":
              case "UPDATE":
              case "DELETE":
                if(($numRows = pg_affected_rows($link)) <= 0)
                {
                    $szErrorLevel = $this->ProcessEvent("NoRowReturned");
                    if($szErrorLevel == ONEVENT_FATAL)
                        return false;
                }
                break;
            }

            // Close the connection
            pg_close($link);
            break;
        }

        return true;
    }

    /**
     * DrawPublish()
     *
     * return an empty string. Should be redefined for Widgets
     * returning HTML code.
     */
    function DrawPublish()
    {
        $szReturn = parent::DrawPublish();
        return $szReturn;
    }

    /**
     * ParseSQLQuery()
     *
     * Replace the dynamic values (SharedResource) in the SQL query
     */
    function ParseSQLQuery()
    {
        foreach ($this->maSharedResourceWidgets as $szSRName => $szValue)
        {
            if (stristr($this->mszSQLQuery, "[$".$szSRName) !== false)
            {
                if(is_array($szValue))
                {
                    $aKeys = array_keys($szValue);
                    $numRow = count($szValue);
                    $oSR = $szValue;
                    for($i=0; $i<$numRow; $i++)
                    {
                        if(stristr($this->mszSQLQuery, 
                                   "[$".$szSRName.".".$aKeys[$i]) !== false)
                        {
                            $szSRName .= ".".$aKeys[$i];
                            if(!is_array($oSR[$aKeys[$i]]))
                            {
                                $szValue = $oSR[$aKeys[$i]];
                                break;
                            }
                            $oSR = $oSR[$aKeys[$i]];
                            $i = -1;
                            $aKeys = array_keys($oSR);
                            $numRow = count($oSR);
                        }
                    }
                }
                if(!is_array($szValue))
                    $this->mszSQLQuery = str_replace("[$".$szSRName."]",
                                                 $szValue, $this->mszSQLQuery);
            }
        }
    }
}
?>