private static function prepareData(Blueprint $blueprint, $where = NULL, array $filters = NULL) { $tag = "EntityExporter::prepareData()"; Log::notice("{$tag}"); /* // BUILD QUERY */ $query = new EntityQuery($blueprint); // WHERE if ($where != NULL) { $query->where($where); } // FILTERS if ($filters != NULL) { foreach ($filters as $key => $value) { if (ereg("^filter_(.+)_(.+)", $key, $regs)) { $filter_field = $regs[1]; $filter_type = $regs[2]; $field = $blueprint->get($filter_field); switch ($field->getDataType()) { case "string": switch ($filter_type) { case "like": $query->where("{$filter_field} LIKE '%{$value}%'"); break; case "equals": $query->where("{$filter_field}='{$value}'"); break; } break; case "int": switch ($filter_type) { case "equals": $query->where("{$filter_field}={$value}"); break; case "min": $query->where("{$filter_field}>={$value}"); break; case "max": $query->where("{$filter_field}<={$value}"); } break; case "decimal": switch ($filter_type) { case "equals": $query->where("{$filter_field}='{$value}'"); break; case "min": $query->where("{$filter_field}>='{$value}'"); break; case "max": $query->where("{$filter_field}<='{$value}'"); } break; case "date": case "datetime": switch ($filter_type) { case "equals": $query->where("{$filter_field}='{$value}'"); break; case "min": $query->where("{$filter_field}>='{$value}'"); break; case "max": $query->where("{$filter_field}<='{$value}'"); } break; case "enum": switch ($filter_type) { case "like": $query->where("{$filter_field} LIKE '%{$value}%'"); break; case "equals": $query->where("{$filter_field}='{$value}'"); break; } break; } // END: switch($field->getDataType()) } // END: if(ereg("^filter_(.+)_(.+)", $key, $regs)) } // END: foreach($filters as $key=>$value) } // END: if($filters != NULL) // Execute Query $sql = new DatabaseQuery($query->toString()); try { $sql->doQuery(); $num_rows = $sql->get_num_rows(); Log::debug("{$tag}: Exporting {$num_rows} rows"); return $sql; } catch (Exception $e) { Log::error("{$tag}: [" . $sql->err_code . "] " . $sql->err_message); throw $e; } }
echo "modified = " . $access->getModified() . "<br/>"; echo "time = " . $access->get("time") . "<br/>"; echo "<br/>"; echo "<strong>Forcing update in session timezone: {$session_timezone_offset}</strong><br/><br/>"; // Update the Access (force Session timezone) unset($accessDAO); $accessDAO = new EntityDAO($accessBP, $session_timezone_offset); $access->set("time", date("Y-m-d H:i:s")); $access->set("description", "Updated Timezone (with forced session timezone) Test"); $accessDAO->update($access); echo "Updated Access with id {$access_id}<br/>"; echo "<br/>"; unset($access); // Load Access with EntityQuery $accessQuery = new EntityQuery($accessBP); $accessQuery->where("Access.id={$access_id}"); echo "QUERY:<br/>" . $accessQuery->toString() . "<br/><br/>"; $sql = new DatabaseQuery($accessQuery->toString()); $sql->doQuery(); echo "Selected Access with id {$access_id}<br/>"; $row = $sql->get_next_row(); $modified = $row->modified; $time = $row->time; echo "modified = {$modified}<br/>"; echo "time = {$time}<br/>"; echo "<br/>"; unset($row); unset($sql); unset($accessQuery); } catch (Exception $e) { echo "Caught: " . $e->getMessage() . "<br/><br/>";
public function findWhere($keys, $values) { $tag = "EntityDAO: findWhere()"; Log::notice("{$tag}: ({$keys}, {$values})"); $blueprint = $this->blueprint; $timezone_offset = $this->timezone_offset_select; $blueprintKey = $this->tableName(); $query = new EntityQuery($blueprint, $timezone_offset); if (!is_array($keys) && !is_array($values)) { // convert non-array arguments into single element arrays $keys = array($keys); $values = array($values); } else { if (is_array($keys) && !is_array($values) || count($keys) != count($values)) { throw new Exception("Length of (key,value) arguments do not match"); } } try { for ($i = 0; $i < count($keys); $i++) { $key = $keys[$i]; $value = $values[$i]; $field = $blueprint->get($key); switch ($field->getDataType()) { case "int": $query->where("{$blueprintKey}.{$key}={$value}"); break; case "datetime": case "time": $query->where("{$blueprintKey}.{$key}=CONVERT_TZ('{$value}', '{$timezone_offset}', '" . BPTimezone::UTC . "')"); break; default: $query->where("{$blueprintKey}.{$key}='{$value}'"); break; } } } catch (Exception $e) { Log::error("{$tag}: Field [{$key}] is not defined by [" . $blueprint->getKey() . "]"); throw $e; } try { $sql = new DatabaseQuery($query->toString()); $sql->doQuery(); $matches = array(); $num_rows = $sql->get_num_rows(); Log::debug("{$tag}: Found {$num_rows} matches"); for ($i = 0; $i < $sql->get_num_rows(); $i++) { $row = $sql->get_next_row(); $entity = new Entity($blueprint); $entity->setId($row->id); $entity->setModified($row->modified); foreach ($blueprint->fields() as $field) { $key = $field->getKey(); $value = $row->{$key}; if ($field->isForeignKey()) { $foreignValueColumn = str_replace(".", "_", $field->getForeignValue()); $foreignValue = $row->{$foreignValueColumn}; $entity->setForeignValue($key, $foreignValue); } switch ($field->getDataType()) { case "binary": if (get_magic_quotes_gpc()) { $value = stripslashes($str); } $lengthKey = $key . "_length"; $length = $row->{$lengthKey}; $entity->length($key, $length); break; } $entity->set($key, $value); } $matches[] = $entity; } return $matches; } catch (Exception $e) { Log::error("{$tag}: [" . $sql->err_code . "] " . $sql->err_message); throw $e; } }
protected function initEntityQuery() { $tag = "ListDrafter: initEntityQuery()"; Log::debug("$tag"); // convenience pointeres $entityBP = $this->entityBlueprint; $listBP = $this->listBlueprint; /* // Build an entity query */ $query = new EntityQuery($this->entityBlueprint); // where $where = ""; if(array_key_exists("where", $this->params)) { $query->where($this->params["where"]); } // process filters $sessionFilters = Session::user("_CONDUIT_FILTERS"); if( (!empty($sessionFilters)) && (is_array($sessionFilters)) && (count($sessionFilters)>0) ) { $filterKeys = array_keys($sessionFilters); foreach($filterKeys as $keyPath) { Log::debug("$tag: Considering filter: $keyPath"); $filter = $sessionFilters["$keyPath"]; $fieldKey = $filter->getKey(); $blueprintKey = $filter->getBlueprintKey(); // should this filter be applied to our list? $applyFilter = false; // does this filter apply to our entity? if($blueprintKey == $entityBP->getKey()) { if($listBP) { if($listBP->keyExists($fieldKey)) { $applyFilter = true; } } else if($entityBP->keyExists($fieldKey)) { $applyFilter = true; } } if( ($applyFilter) && ($filter->hasValue()) ) { Log::debug("$tag: Applying filter: $keyPath"); switch($filter->dataType) { case "string": case "text": case "enum": switch($filter->type) { case "like": $like = $filter->value["like"]; $query->where("$keyPath LIKE '%$like%'"); break; case "equals": $equals = $filter->value["equals"]; $query->where("$keyPath='$equals'"); break; } break; case "int": switch($filter->type) { case "equals": $equals = $filter->value["equals"]; $query->where("$keyPath=$equals"); break; case "min": $min = $filter->value["min"]; $query->where("$keyPath>=$min"); break; case "max": $max = $filter->value["max"]; $query->where("$keyPath<=$max"); break; case "range": $min = $filter->value["min"]; $max = $filter->value["max"]; if( (!empty($min) || $min==0) && (!empty($max) || $max==0) ) $query->where("( ($keyPath>=$min) AND ($keyPath<=$max) )"); else if( (!empty($min) || $min==0) ) $query->where("$keyPath>=$min"); else $query->where("$keyPath<=$max"); break; } break; case "decimal": case "date": case "datetime": switch($filter->type) { case "equals": $equals = $filter->value["equals"]; $query->where("$keyPath='$equals'"); break; case "min": $min = $filter->value["min"]; $query->where("$keyPath>='$min'"); break; case "max": $max = $filter->value["max"]; $query->where("$keyPath<='$max'"); break; case "range": $min = $filter->value["min"]; $max = $filter->value["max"]; if( !empty($min) && !empty($max) ) $query->where("( ($keyPath>='$min') AND ($keyPath<='$max') )"); else if( !empty($min) ) $query->where("$keyPath>='$min'"); else $query->where("$keyPath<='$max'"); break; } break; } // END: switch($filter->dataType) } // END: if($applyFilter) } // END: foreach($filterKeys as $keyPath) } // END: if( (!empty($sessionFilters)) && (is_array($sessionFilters)) && (count($sessionFilters)>0) ) // ORDER BY if(array_key_exists("order", $this->params)) { $order = $this->params["order"]; if(is_array($order)) { foreach($order as $o) { $query->order($o); } } else { $query->order($order); } } // LIMIT (PAGING) if(array_key_exists("page", $this->params)) { // Init page number $page = 1; if( (is_int($this->params["page"])) && ($this->params["page"]>=1) ) $page = $this->params["page"]; // Init page limit $page_limit = 20; // default if( (array_key_exists("page_limit", $this->params)) && (is_int((int) $this->params["page_limit"])) && ($this->params["page_limit"]>=1) ) $page_limit = $this->params["page_limit"]; else $this->params["page_limit"] = $page_limit; // init default for use by javascript // Calculate OFFSET (First row offset=0) $page_offset = ($page-1) * $page_limit; // Count the total number of rows that this query will produce (without LIMITs) $countQuery = clone $query; $countQuery->select("COUNT(*) AS count", true); $sql = new DatabaseQuery($countQuery->toString()); try { $sql->doQuery(); $row = $sql->get_next_row(); $count = $row->count; Log::debug("$tag: COUNT = $count"); // Save "count" to params (for use by javascript) $this->params["count"] = (int) $count; } catch(Exception $e) { Log::error("$tag: Caught: " . $e->getMessage()); } // Add LIMIT clause to query $query->limit("$page_offset, $page_limit"); } $this->entityQuery = $query; }