/**
* Pass in parameter to get query SQL
* @param String $sTable
* @param array $aCond
* @param array $aGroupBy
* @param array $aOrderby
* @param String $sSelect
* @param int $iLimit
* @param int $iOffset
*/
public function getQuery($sTable = "", $aCond = array(), $aGroupBy = null, $aOrderby = null, $sSelect = null, $iLimit = null, $iOffset = 0)
{
$bDebug = false;
$xpdo = FlexiModelUtil::getInstance()->getXPDO();
$sFromQuery = $this->getFromQuerySyntax($sTable);
//any auto condition changes here
//pass to event handler, throw exception to stop process
//$this->onQueryParam($sTable, $aCond, $aGroupBy, $aOrderby, $sSelect, $iLimit, $iOffset);
$sSQL = "";
if (empty($sSelect)) {
$sSQL = "SELECT * FROM " . $sFromQuery;
} else {
$sSQL = $sSelect . " FROM " . $sFromQuery;
}
$aParam = array();
$aCond = $this->getObjectCond($aCond);
$aWhere = FlexiModelUtil::parseSQLCond($aCond);
if (!empty($aWhere["sql"])) {
$sSQL .= " WHERE " . $aWhere["sql"];
$aParam = array_merge($aParam, $aWhere["param"]);
}
//if ($bDebug) var_dump($aCond);
//if ($bDebug) var_dump($aWhere);
//if ($bDebug) var_dump($aParam);
$sGroupSQL = "";
if (!is_null($aGroupBy)) {
foreach ($aGroupBy as $sGroup) {
$sGroupSQL .= empty($sGroupSQL) ? "" : ",";
$sGroupSQL .= FlexiModelUtil::parseSQLName($sGroup);
}
$sSQL .= !empty($sGroupSQL) ? " group by " . $sGroupSQL : "";
}
if (!is_null($aOrderby)) {
$sOrderbySQL = "";
foreach ($aOrderby as $sOrderBy) {
$sOrderbySQL .= empty($sOrderbySQL) ? "" : ",";
$aOrder = explode(" ", $sOrderBy);
$sOrderType = count($aOrder) > 1 ? strtolower($aOrder[1]) == "asc" ? "ASC" : "DESC" : "ASC";
$sOrderbySQL .= FlexiModelUtil::parseSQLName($aOrder[0]) . " " . $sOrderType;
}
$sSQL .= !empty($sOrderbySQL) ? " order by " . $sOrderbySQL : "";
}
if ($iLimit > 0) {
$sSQL .= " limit " . $iLimit . " offset " . $iOffset;
}
if ($bDebug) {
echo __METHOD__ . ": " . $sSQL . "<br/>\n";
}
$this->onParseQueryBinding($sSQL, $aParam);
$sResultSQL = $xpdo->parseBindings($sSQL, $aParam);
if ($bDebug) {
echo __METHOD__ . ": " . $sResultSQL . "<br/>\n";
}
return $sResultSQL;
}
function dbUpdate($oRow, $sTable, $mPrimary = "id", $pdo = null)
{
$pdo = is_null($pdo) ? FlexiModelUtil::getInstance()->getXPDO() : $pdo;
$bDebug = false;
$aPrimary = !is_array($mPrimary) ? explode(",", $mPrimary . "") : $mPrimary;
if ($bDebug) {
echo __METHOD__ . ": primary:" . print_r($aPrimary, true) . "\n<br/>";
}
$aWhere = FlexiModelUtil::parseSQLCondKeyValue($aPrimary, $oRow);
$sFields = "";
$aParam = $aWhere["param"];
foreach ($oRow as $sField => $sValue) {
$sFieldRaw = dbCleanName($sField);
$sFieldName = FlexiModelUtil::parseSQLName($sField);
$sFields .= empty($sFields) ? "" : ",";
$sFields .= $sFieldName . "=:_update_" . $sFieldRaw;
$aParam[":_update_" . $sFieldRaw] = $oRow[$sField];
}
$sSQL = "UPDATE " . FlexiModelUtil::parseSQLName($sTable) . " SET " . $sFields . " WHERE " . $aWhere["sql"];
if ($bDebug) {
echo __METHOD__ . ": sql:" . $sSQL . "\n<br/>";
}
if ($bDebug) {
echo __METHOD__ . ": param:" . print_r($aWhere["param"], true) . "\n<br/>";
}
$oStatement = $pdo->prepare($sSQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$mResult = $oStatement->execute($aParam);
if ($mResult === false) {
$aError = $oStatement->errorInfo();
throw new Exception("Query failed: " . $aError[2] . ":" . $sSQL . ":" . print_r($aParam, true));
}
return $mResult;
}
