protected function assertCriteriaTranslation($criteria, $expectedSql, $expectedParams, $message = '')
{
$params = array();
$result = BasePeer::createSelectSql($criteria, $params);
$this->assertEquals($expectedSql, $result, $message);
$this->assertEquals($expectedParams, $params, $message);
}
public function testApplyLimitDuplicateColumnName()
{
Propel::setDb('oracle', new DBOracle());
$c = new Criteria();
$c->setDbName('oracle');
BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);
$c->setLimit(1);
$params = array();
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS book_ID, book.TITLE AS book_TITLE, book.ISBN AS book_ISBN, book.PRICE AS book_PRICE, book.PUBLISHER_ID AS book_PUBLISHER_ID, book.AUTHOR_ID AS book_AUTHOR_ID, author.ID AS author_ID, author.FIRST_NAME AS author_FIRST_NAME, author.LAST_NAME AS author_LAST_NAME, author.EMAIL AS author_EMAIL, author.AGE AS author_AGESELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, author.ID, author.FIRST_NAME, author.LAST_NAME, author.EMAIL, author.AGE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found');
}
public function testApplyLimitDuplicateColumnNameWithColumn()
{
Propel::setDb('oracle', new DBOracle());
$c = new Criteria();
$c->setDbName('oracle');
BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);
$c->addAsColumn('BOOK_PRICE', BookPeer::PRICE);
$c->setLimit(1);
$params = array();
$asColumns = $c->getAsColumns();
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS ORA_COL_ALIAS_0, book.TITLE AS ORA_COL_ALIAS_1, book.ISBN AS ORA_COL_ALIAS_2, book.PRICE AS ORA_COL_ALIAS_3, book.PUBLISHER_ID AS ORA_COL_ALIAS_4, book.AUTHOR_ID AS ORA_COL_ALIAS_5, author.ID AS ORA_COL_ALIAS_6, author.FIRST_NAME AS ORA_COL_ALIAS_7, author.LAST_NAME AS ORA_COL_ALIAS_8, author.EMAIL AS ORA_COL_ALIAS_9, author.AGE AS ORA_COL_ALIAS_10, book.PRICE AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found');
$this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column');
}
public function testApplyLimitDuplicateColumnNameWithColumn()
{
Propel::setDb('oracle', new DBOracle());
$c = new Criteria();
$c->setDbName('oracle');
BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);
$c->addAsColumn('BOOK_PRICE', BookPeer::PRICE);
$c->setLimit(1);
$params = array();
$asColumns = $c->getAsColumns();
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.id AS ORA_COL_ALIAS_0, book.title AS ORA_COL_ALIAS_1, book.isbn AS ORA_COL_ALIAS_2, book.price AS ORA_COL_ALIAS_3, book.publisher_id AS ORA_COL_ALIAS_4, book.author_id AS ORA_COL_ALIAS_5, author.id AS ORA_COL_ALIAS_6, author.first_name AS ORA_COL_ALIAS_7, author.last_name AS ORA_COL_ALIAS_8, author.email AS ORA_COL_ALIAS_9, author.age AS ORA_COL_ALIAS_10, book.price AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found');
$this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column');
}
public static function getCriteriaCacheKey(Criteria $c)
{
$cloned_c = clone $c;
$arr = array();
$str = BasePeer::createSelectSql($c, $arr);
$param_map = array();
$keys = $c->keys();
foreach ($keys as $k) {
$val = $c->getCriterion($k);
if ($val) {
$param_map[$k] = $val->hashCode();
}
}
$str .= print_r($param_map, true);
$cache_key = md5($str);
return $cache_key;
}
public static function getMiSQL($criteria)
{
if (!isset($criteria)) {
return null;
}
if (!$criteria->getSelectColumns()) {
$criteria = clone $criteria;
self::addSelectColumns($criteria);
}
$criteria->setDbName(self::DATABASE_NAME);
$con = Propel::getConnection($criteria->getDbName());
$params = array();
$sql = BasePeer::createSelectSql($criteria, $params);
$stmt = null;
$stmt = $con->prepare($sql);
$stmt->setLimit($criteria->getLimit());
$stmt->setOffset($criteria->getOffset());
return $stmt;
}
public function testAddSelectModifier()
{
$c = new Criteria();
$c->setDistinct();
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier to the Criteria');
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier only once');
$params = array();
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT DISTINCT SQL_CALC_FOUND_ROWS FROM ', $result, 'addSelectModifier() adds a modifier to the final query');
}
/**
* Do Explain Plan for query object or query string
*
* @param PropelPDO $con propel connection
* @param ModelCriteria|string $query query the criteria or the query string
* @throws PropelException
* @return PDOStatement A PDO statement executed using the connection, ready to be fetched
*/
public function doExplainPlan(PropelPDO $con, $query)
{
if ($query instanceof ModelCriteria) {
$params = array();
$dbMap = Propel::getDatabaseMap($query->getDbName());
$sql = BasePeer::createSelectSql($query, $params);
$sql = 'EXPLAIN ' . $sql;
} else {
$sql = 'EXPLAIN ' . $query;
}
$stmt = $con->prepare($sql);
if ($query instanceof ModelCriteria) {
$this->bindValues($stmt, $params, $dbMap);
}
$stmt->execute();
return $stmt;
}
public function testMssqlApplyLimitWithOffsetMultipleOrderBy()
{
$db = Propel::getDB(BookPeer::DATABASE_NAME);
if (!$db instanceof DBMSSQL) {
$this->markTestSkipped();
}
$c = new Criteria(BookPeer::DATABASE_NAME);
$c->addSelectColumn(BookPeer::ID);
$c->addSelectColumn(BookPeer::TITLE);
$c->addSelectColumn(PublisherPeer::NAME);
$c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)');
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN);
$c->addDescendingOrderByColumn('PublisherName');
$c->addAscendingOrderByColumn(BookPeer::TITLE);
$c->setOffset(20);
$c->setLimit(20);
$params = array();
$expectedSql = "SELECT [book.ID], [book.TITLE], [publisher.NAME], [PublisherName] FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) DESC, book.TITLE ASC) AS [RowNumber], book.ID AS [book.ID], book.TITLE AS [book.TITLE], publisher.NAME AS [publisher.NAME], (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) AS [PublisherName] FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID)) AS derivedb WHERE RowNumber BETWEEN 21 AND 40";
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals($expectedSql, $sql);
}
protected function doCount($con)
{
$dbMap = Propel::getDatabaseMap($this->getDbName());
$db = Propel::getDB($this->getDbName());
// check that the columns of the main class are already added (if this is the primary ModelCriteria)
if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) {
$this->addSelfSelectColumns();
}
$this->configureSelectColumns();
$needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers()) || count($this->selectQueries) > 0;
$params = array();
if ($needsComplexCount) {
if (BasePeer::needsSelectAliases($this)) {
if ($this->getHaving()) {
throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part');
}
$db->turnSelectColumnsToAliases($this);
}
$selectSql = BasePeer::createSelectSql($this, $params);
$sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt';
} else {
// Replace SELECT columns with COUNT(*)
$this->clearSelectColumns()->addSelectColumn('COUNT(*)');
$sql = BasePeer::createSelectSql($this, $params);
}
try {
$stmt = $con->prepare($sql);
$db->bindValues($stmt, $params, $dbMap);
$stmt->execute();
} catch (Exception $e) {
Propel::log($e->getMessage(), Propel::LOG_ERR);
throw new PropelException(sprintf('Unable to execute COUNT statement [%s]', $sql), $e);
}
return $stmt;
}
/**
* Do Explain Plan for query object or query string
*
* @param PropelPDO $con propel connection
* @param ModelCriteria|string $query query the criteria or the query string
* @throws PropelException
* @return PDOStatement A PDO statement executed using the connection, ready to be fetched
*/
public function doExplainPlan(PropelPDO $con, $query)
{
$con->beginTransaction();
if ($query instanceof ModelCriteria) {
$params = array();
$dbMap = Propel::getDatabaseMap($query->getDbName());
$sql = BasePeer::createSelectSql($query, $params);
} else {
$sql = $query;
}
// unique id for the query string
$uniqueId = uniqid('Propel', true);
$stmt = $con->prepare($this->getExplainPlanQuery($sql, $uniqueId));
if ($query instanceof ModelCriteria) {
$this->bindValues($stmt, $params, $dbMap);
}
$stmt->execute();
// explain plan is save in a table, data must be commit
$con->commit();
$stmt = $con->prepare($this->getExplainPlanReadQuery($uniqueId));
$stmt->execute();
return $stmt;
}
public function testUseFkQueryNested()
{
$q = ReviewQuery::create()->useBookQuery()->useAuthorQuery()->filterByFirstName('Leo')->endUse()->endUse();
$q1 = ReviewQuery::create()->join('Review.Book', Criteria::LEFT_JOIN)->join('Book.Author', Criteria::LEFT_JOIN)->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
// embedded queries create joins that keep a relation to the parent
// as this is not testable, we need to use another testing technique
$params = array();
$result = BasePeer::createSelectSql($q, $params);
$expectedParams = array();
$expectedResult = BasePeer::createSelectSql($q1, $expectedParams);
$this->assertEquals($expectedParams, $params, 'useFkQuery() called nested creates two joins');
$this->assertEquals($expectedResult, $result, 'useFkQuery() called nested creates two joins');
}
public function testCombineAndFilterBy()
{
$params = array();
$sql = "SELECT FROM `book` WHERE ((book.title LIKE :p1 OR book.isbn LIKE :p2) AND book.title LIKE :p3)";
$c = BookQuery::create()->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or')->filterByTitle('%test3%');
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals($result, $sql);
$params = array();
$sql = "SELECT FROM `book` WHERE (book.title LIKE :p1 AND (book.title LIKE :p2 OR book.isbn LIKE :p3))";
$c = BookQuery::create()->filterByTitle('%test3%')->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or');
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals($result, $sql);
}
public function testaddUsingOperatorResetsDefaultOperator()
{
$c = new Criteria();
$c->addUsingOperator('foo1', 'bar1');
$c->_or();
$c->addUsingOperator('foo2', 'bar2');
$c->addUsingOperator('foo3', 'bar3');
$expected = 'SELECT FROM WHERE (foo1=:p1 OR foo2=:p2) AND foo3=:p3';
$params = array();
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals($expected, $result);
}
public function testDistinct()
{
$c = new Criteria();
$c->addSelectColumn('*');
$c->add('TABLE.string', 'foo');
$c->setDistinct();
$c->add('TABLE.id', 10);
$c->setDistinct();
$params = array();
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT DISTINCT * FROM TABLE WHERE TABLE.string=:p1 AND TABLE.id=:p2', $result);
}
/**
* Method to create an SQL query based on values in a Criteria.
*
* This method creates only prepared statement SQL (using ? where values
* will go). The second parameter ($params) stores the values that need
* to be set before the statement is executed. The reason we do it this way
* is to let the PDO layer handle all escaping & value formatting.
*
* @param Criteria $criteria Criteria for the SELECT query.
* @param array &$params Parameters that are to be replaced in prepared statement.
* @return string
* @throws PropelException Trouble creating the query string.
*/
public static function createSelectSql(Criteria $criteria, &$params)
{
$db = Propel::getDB($criteria->getDbName());
$dbMap = Propel::getDatabaseMap($criteria->getDbName());
$fromClause = array();
$joinClause = array();
$joinTables = array();
$whereClause = array();
$orderByClause = array();
$orderBy = $criteria->getOrderByColumns();
$groupBy = $criteria->getGroupByColumns();
$ignoreCase = $criteria->isIgnoreCase();
// get the first part of the SQL statement, the SELECT part
$selectSql = $db->createSelectSqlPart($criteria, $fromClause);
// Handle joins
// joins with a null join type will be added to the FROM clause and the condition added to the WHERE clause.
// joins of a specified type: the LEFT side will be added to the fromClause and the RIGHT to the joinClause
foreach ($criteria->getJoins() as $join) {
$join->setDB($db);
// add 'em to the queues..
if (!$fromClause) {
$fromClause[] = $join->getLeftTableWithAlias();
}
$joinTables[] = $join->getRightTableWithAlias();
$joinClause[] = $join->getClause($params);
}
// add the criteria to WHERE clause
// this will also add the table names to the FROM clause if they are not already
// included via a LEFT JOIN
foreach ($criteria->keys() as $key) {
$criterion = $criteria->getCriterion($key);
$table = null;
foreach ($criterion->getAttachedCriterion() as $attachedCriterion) {
$tableName = $attachedCriterion->getTable();
$table = $criteria->getTableForAlias($tableName);
if ($table !== null) {
$fromClause[] = $table . ' ' . $tableName;
} else {
$fromClause[] = $tableName;
$table = $tableName;
}
if (($criteria->isIgnoreCase() || $attachedCriterion->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($attachedCriterion->getColumn())->isText()) {
$attachedCriterion->setIgnoreCase(true);
}
}
$criterion->setDB($db);
$sb = '';
$criterion->appendPsTo($sb, $params);
$whereClause[] = $sb;
}
// Unique from clause elements
$fromClause = array_unique($fromClause);
$fromClause = array_diff($fromClause, array(''));
// tables should not exist in both the from and join clauses
if ($joinTables && $fromClause) {
foreach ($fromClause as $fi => $ftable) {
if (in_array($ftable, $joinTables)) {
unset($fromClause[$fi]);
}
}
}
// Add the GROUP BY columns
$groupByClause = $groupBy;
$having = $criteria->getHaving();
$havingString = null;
if ($having !== null) {
$sb = '';
$having->appendPsTo($sb, $params);
$havingString = $sb;
}
if (!empty($orderBy)) {
foreach ($orderBy as $orderByColumn) {
// Add function expression as-is.
if (strpos($orderByColumn, '(') !== false) {
$orderByClause[] = $orderByColumn;
continue;
}
// Split orderByColumn (i.e. "table.column DESC")
$dotPos = strrpos($orderByColumn, '.');
if ($dotPos !== false) {
$tableName = substr($orderByColumn, 0, $dotPos);
$columnName = substr($orderByColumn, $dotPos + 1);
} else {
$tableName = '';
$columnName = $orderByColumn;
}
$spacePos = strpos($columnName, ' ');
if ($spacePos !== false) {
$direction = substr($columnName, $spacePos);
$columnName = substr($columnName, 0, $spacePos);
} else {
$direction = '';
}
$tableAlias = $tableName;
if ($aliasTableName = $criteria->getTableForAlias($tableName)) {
$tableName = $aliasTableName;
}
$columnAlias = $columnName;
if ($asColumnName = $criteria->getColumnForAs($columnName)) {
$columnName = $asColumnName;
}
$column = $tableName ? $dbMap->getTable($tableName)->getColumn($columnName) : null;
if ($criteria->isIgnoreCase() && $column && $column->isText()) {
$ignoreCaseColumn = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}");
$orderByClause[] = $ignoreCaseColumn . $direction;
$selectSql .= ', ' . $ignoreCaseColumn;
} else {
$orderByClause[] = $orderByColumn;
}
}
}
if (empty($fromClause) && $criteria->getPrimaryTableName()) {
$fromClause[] = $criteria->getPrimaryTableName();
}
// tables should not exist as alias of subQuery
if ($criteria->hasSelectQueries()) {
foreach ($fromClause as $key => $ftable) {
if (strpos($ftable, ' ') !== false) {
list($realtable, $tableName) = explode(' ', $ftable);
} else {
$tableName = $ftable;
}
if ($criteria->hasSelectQuery($tableName)) {
unset($fromClause[$key]);
}
}
}
// from / join tables quoted if it is necessary
if ($db->useQuoteIdentifier()) {
$fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause);
$joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause);
}
// add subQuery to From after adding quotes
foreach ($criteria->getSelectQueries() as $subQueryAlias => $subQueryCriteria) {
$fromClause[] = '(' . BasePeer::createSelectSql($subQueryCriteria, $params) . ') AS ' . $subQueryAlias;
}
// build from-clause
$from = '';
if (!empty($joinClause) && count($fromClause) > 1) {
$from .= implode(" CROSS JOIN ", $fromClause);
} else {
$from .= implode(", ", $fromClause);
}
$from .= $joinClause ? ' ' . implode(' ', $joinClause) : '';
// Build the SQL from the arrays we compiled
$sql = $selectSql . " FROM " . $from . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : "");
// APPLY OFFSET & LIMIT to the query.
if ($criteria->getLimit() || $criteria->getOffset()) {
$db->applyLimit($sql, $criteria->getOffset(), $criteria->getLimit(), $criteria);
}
return $sql;
}
/**
* Returns the taggings associated to one tag or a set of tags.
*
* The second optionnal parameter permits to restrict the results with
* different criterias
*
* @param mixed $tags Array of tag strings or string
* @param array $options Array of options parameters
* @return array
*/
public static function getTaggings($tags = array(), $options = array())
{
$tags = sfPropelActAsTaggableToolkit::explodeTagString($tags);
if (is_string($tags)) {
$tags = array($tags);
}
$c = new Criteria();
$c->addJoin(TagPeer::ID, TaggingPeer::TAG_ID);
if (count($tags) > 0) {
$c->add(TagPeer::NAME, $tags, Criteria::IN);
$having = $c->getNewCriterion('COUNT(' . TaggingPeer::TAGGABLE_MODEL . ') ', count($tags), Criteria::GREATER_EQUAL);
$c->addHaving($having);
}
$c->addGroupByColumn(TaggingPeer::TAGGABLE_ID);
$c->clearSelectColumns();
$c->addSelectColumn(TaggingPeer::TAGGABLE_MODEL);
$c->addSelectColumn(TaggingPeer::TAGGABLE_ID);
// Taggable model class option
if (isset($options['model'])) {
if (!class_exists($options['model']) || !is_callable(array(new $options['model'](), 'getPeer'))) {
throw new PropelException(sprintf('The class "%s" does not exist, or it is not a model class.', $options['model']));
}
$c->add(TaggingPeer::TAGGABLE_MODEL, $options['model']);
} else {
$c->addGroupByColumn(TaggingPeer::TAGGABLE_MODEL);
}
if (isset($options['triple'])) {
$c->add(TagPeer::IS_TRIPLE, $options['triple']);
}
if (isset($options['namespace'])) {
$c->add(TagPeer::TRIPLE_NAMESPACE, $options['namespace']);
}
if (isset($options['key'])) {
$c->add(TagPeer::TRIPLE_KEY, $options['key']);
}
if (isset($options['value'])) {
$c->add(TagPeer::TRIPLE_VALUE, $options['value']);
}
$param = array();
$sql = BasePeer::createSelectSql($c, $param);
$con = Propel::getConnection();
if (Propel::VERSION < '1.3') {
$stmt = $con->prepareStatement($sql);
$position = 1;
foreach ($tags as $tag) {
$stmt->setString($position, $tag);
$position++;
}
if (isset($options['model'])) {
$stmt->setString($position, $options['model']);
$position++;
}
if (isset($options['triple'])) {
$stmt->setBoolean($position, $options['triple']);
$position++;
}
if (isset($options['namespace'])) {
$stmt->setString($position, $options['namespace']);
$position++;
}
if (isset($options['key'])) {
$stmt->setString($position, $options['key']);
$position++;
}
if (isset($options['value'])) {
$stmt->setString($position, $options['value']);
$position++;
}
} else {
$stmt = $con->prepare($sql);
$position = 1;
foreach ($tags as $tag) {
$stmt->bindValue(':p' . $position, $tag, PDO::PARAM_STR);
$position++;
}
if (isset($options['model'])) {
$stmt->bindValue(':p' . $position, $options['model'], PDO::PARAM_STR);
$position++;
}
if (isset($options['triple'])) {
$stmt->bindValue(':p' . $position, $options['triple']);
$position++;
}
if (isset($options['namespace'])) {
$stmt->bindValue(':p' . $position, $options['namespace'], PDO::PARAM_STR);
$position++;
}
if (isset($options['key'])) {
$stmt->bindValue(':p' . $position, $options['key'], PDO::PARAM_STR);
$position++;
}
if (isset($options['value'])) {
$stmt->bindValue(':p' . $position, $options['value'], PDO::PARAM_STR);
$position++;
}
}
if (!isset($options['nb_common_tags']) || $options['nb_common_tags'] > count($tags)) {
$options['nb_common_tags'] = count($tags);
}
if ($options['nb_common_tags'] > 0) {
if (Propel::VERSION >= '1.3') {
$stmt->bindValue(':p' . $position, $options['nb_common_tags'], PDO::PARAM_STR);
} else {
$stmt->setString($position, $options['nb_common_tags']);
}
}
$taggings = array();
if (Propel::VERSION >= '1.3') {
$rs = $stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$model = $row[0];
if (!isset($taggings[$model])) {
$taggings[$model] = array();
}
$taggings[$model][] = $row[1];
}
} else {
$rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
while ($rs->next()) {
$model = $rs->getString(1);
if (!isset($taggings[$model])) {
$taggings[$model] = array();
}
$taggings[$model][] = $rs->getInt(2);
}
}
return $taggings;
}
/**
* Produces a union-query from two queries.
*
* @todo Implement support for ORDER, LIMIT etc.
*
* @param ModelCriteria $mc1
* @param ModelCriteria $mc2
* @return mixed
*/
public static function union(ModelCriteria $mc1, ModelCriteria $mc2)
{
$dbMap = Propel::getDatabaseMap($mc1->getDbName());
$db = Propel::getDB($mc1->getDbName());
$con = Propel::getConnection($mc1->getDbName(), Propel::CONNECTION_READ);
// we may modify criteria, so copy it first
$c1 = clone $mc1;
$c2 = clone $mc2;
// check that the columns of the main class are already added (if this is the primary ModelCriteria)
if (!$c1->hasSelectClause() && !$c1->getPrimaryCriteria()) {
$c1->addSelfSelectColumns();
}
if (!$c2->hasSelectClause() && !$c2->getPrimaryCriteria()) {
$c2->addSelfSelectColumns();
}
$con->beginTransaction();
try {
$params = array();
$sql1 = BasePeer::createSelectSql($c1, $params);
$sql2 = BasePeer::createSelectSql($c2, $params);
$stmt = $con->prepare("({$sql1}) UNION ALL ({$sql2})");
$db->bindValues($stmt, $params, $dbMap);
$stmt->execute();
$con->commit();
} catch (PropelException $e) {
$con->rollback();
throw $e;
}
return $c1->getFormatter()->init($c1)->format($stmt);
}
/**
* Get the condition for Cases List
*
* @name getConditionCasesList
* @param string $sTypeList
* @param string $sUIDUserLogged
* @param string $ClearSession
* @param string $aAdditionalFilter
* @return array
*/
public function getConditionCasesList($sTypeList = 'all', $sUIDUserLogged = '', $ClearSession = true, $aAdditionalFilter = null)
{
$c = new Criteria('workflow');
$c->clearSelectColumns();
$c->addSelectColumn(ApplicationPeer::APP_UID);
$c->addSelectColumn(ApplicationPeer::APP_NUMBER);
$c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE);
$c->addSelectColumn(ApplicationPeer::PRO_UID);
$c->addSelectColumn(ApplicationPeer::APP_INIT_USER);
$c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY);
//$c->addSelectColumn(AppDelegationPeer::DEL_TASK_DUE_DATE);
$c->addAsColumn(
'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(), CONCAT('<span style=\'color:red\';>', " .
AppDelegationPeer::DEL_TASK_DUE_DATE . ", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") "
);
global $RBAC;
//seems the PM_SUPERVISOR can delete a completed case
if ($sTypeList == "completed" && $RBAC->userCanAccess('PM_SUPERVISOR') == 1) {
$c->addAsColumn("DEL_LINK", "CONCAT('" . G::LoadTranslation('ID_DELETE') . "')");
}
$c->addSelectColumn(AppDelegationPeer::DEL_INDEX);
$c->addSelectColumn(AppDelegationPeer::TAS_UID);
$c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE);
$c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE);
$c->addSelectColumn(UsersPeer::USR_UID);
$c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)");
$c->addSelectColumn(ApplicationPeer::APP_STATUS);
$c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE');
$c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE');
$c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE');
//$c->addAsColumn('APP_DEL_PREVIOUS_USER', 'APP_LAST_USER.USR_USERNAME');
$c->addAsColumn(
'APP_DEL_PREVIOUS_USER', "CONCAT(APP_LAST_USER.USR_LASTNAME,
' ',
APP_LAST_USER.USR_FIRSTNAME)"
);
$c->addAlias("APP_TITLE", 'CONTENT');
$c->addAlias("PRO_TITLE", 'CONTENT');
$c->addAlias("TAS_TITLE", 'CONTENT');
$c->addAlias("APP_PREV_DEL", 'APP_DELEGATION');
$c->addAlias("APP_LAST_USER", 'USERS');
$c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN);
$c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN);
$appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID);
$appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX);
$c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN);
$c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN);
$del = DBAdapter::getStringDelimiter();
$appTitleConds = array();
$appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID');
$appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del);
$appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN);
$proTitleConds = array();
$proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID');
$proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del);
$proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN);
$tasTitleConds = array();
$tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID');
$tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del);
$tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN);
$prevConds = array();
$prevConds[] = array(ApplicationPeer::APP_UID, 'APP_PREV_DEL.APP_UID');
$prevConds[] = array('APP_PREV_DEL.DEL_INDEX', AppDelegationPeer::DEL_PREVIOUS);
$c->addJoinMC($prevConds, Criteria::LEFT_JOIN);
$usrConds = array();
$usrConds[] = array('APP_PREV_DEL.USR_UID', 'APP_LAST_USER.USR_UID');
$c->addJoinMC($usrConds, Criteria::LEFT_JOIN);
$c->add(TaskPeer::TAS_TYPE, 'SUBPROCESS', Criteria::NOT_EQUAL);
//gral, to_revise, to_reassign dont have userid in the query
if ($sTypeList != 'gral' && $sTypeList != 'to_revise' && $sTypeList != 'to_reassign' &&
$sTypeList != 'my_started' && $sTypeList != 'sent') {
$c->add(UsersPeer::USR_UID, $sUIDUserLogged);
}
/**
* Additional filters
* By Erik <*****@*****.**>
*/
if (isset($aAdditionalFilter) && is_array($aAdditionalFilter)) {
foreach ($aAdditionalFilter as $sFilter => $sValue) {
switch ($sFilter) {
case 'PRO_UID':
if ($sValue != "0") {
$c->add(ApplicationPeer::PRO_UID, $sValue, Criteria::EQUAL);
}
break;
case 'READ':
$c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNOTNULL);
break;
case 'UNREAD':
$c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNULL);
break;
}
}
}
$filesList = array(
//7 standard list
'to_do' => 'cases/cases_ListTodo',
'draft' => 'cases/cases_ListDraft',
'paused' => 'cases/cases_ListOnHold',
'cancelled' => 'cases/cases_ListCancelled',
'completed' => 'cases/cases_ListCompleted',
'sent' => 'cases/cases_ListSent',
'selfservice' => 'cases/cases_ListSelfService',
//5 admin list
'all' => 'cases/cases_ListAll',
'to_revise' => 'cases/cases_ListToRevise',
'to_reassign' => 'cases/cases_ListAll_Reassign',
'my_started' => 'cases/cases_ListStarted',
'Alldelete' => 'cases/cases_ListAllDelete'
);
switch ($sTypeList) {
case 'all':
$c->add(
$c->getNewCriterion(
AppThreadPeer::APP_THREAD_STATUS, 'OPEN')->
addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')->
addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0)
)
)
);
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'my_started':
$oCriteria = new Criteria('workflow');
$oCriteria->addSelectColumn(AppDelayPeer::APP_UID);
$oCriteria->add(
$oCriteria->getNewCriterion(
AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL
)->addOr(
$oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0)
)
);
//$oCriteria->add(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL);
$oDataset = AppDelayPeer::doSelectRS($oCriteria);
$oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC);
$oDataset->next();
$aProcesses = array();
while ($aRow = $oDataset->getRow()) {
$aProcesses[] = $aRow['APP_UID'];
$oDataset->next();
}
$c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged));
$c->add(
$c->getNewCriterion(
AppThreadPeer::APP_THREAD_STATUS, 'OPEN'
)->addOr(
$c->getNewCriterion(
ApplicationPeer::APP_STATUS, 'COMPLETED'
)->addAnd(
$c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0)
)
)
);
$c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN));
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'to_do':
$c->add(ApplicationPeer::APP_STATUS, 'TO_DO');
$c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL);
$c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN');
$c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN');
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'draft':
$c->add(ApplicationPeer::APP_STATUS, 'DRAFT');
$c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL);
$c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN');
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'paused':
$appDelayConds[] = array(ApplicationPeer::APP_UID, AppDelayPeer::APP_UID);
$appDelayConds[] = array(AppDelegationPeer::DEL_INDEX, AppDelayPeer::APP_DEL_INDEX);
$c->addJoinMC($appDelayConds, Criteria::LEFT_JOIN);
$c->add(AppDelayPeer::APP_DELAY_UID, null, Criteria::ISNOTNULL);
$c->add(AppDelayPeer::APP_TYPE, array("REASSIGN", "ADHOC", "CANCEL"), Criteria::NOT_IN);
$c->add(
$c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL)->
addOr($c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0))
);
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'cancelled':
$c->add(
$c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'CLOSED')->
addAnd($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'CANCELLED'))
);
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'completed':
$c->add(ApplicationPeer::APP_STATUS, 'COMPLETED');
$c->add(AppDelegationPeer::DEL_PREVIOUS, '0', Criteria::NOT_EQUAL);
//$c->addAsColumn('DEL_FINISH_DATE', 'max('.AppDelegationPeer::DEL_FINISH_DATE.')');
$c->addGroupByColumn(ApplicationPeer::APP_UID);
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'gral':
$c->add(
$c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'OPEN')->
addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')->
addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0)))
);
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
$params = array();
$sSql = BasePeer::createSelectSql($c, $params);
break;
case 'to_revise':
$oCriteria = new Criteria('workflow');
$oCriteria->add(ProcessUserPeer::USR_UID, $sUIDUserLogged);
$oCriteria->add(ProcessUserPeer::PU_TYPE, 'SUPERVISOR');
$oDataset = ProcessUserPeer::doSelectRS($oCriteria);
$oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC);
$oDataset->next();
$aProcesses = array();
while ($aRow = $oDataset->getRow()) {
$aProcesses[] = $aRow['PRO_UID'];
$oDataset->next();
}
$c->add(ApplicationPeer::PRO_UID, $aProcesses, Criteria::IN);
$c->add(ApplicationPeer::APP_STATUS, 'TO_DO');
$c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL);
$c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN');
$c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN');
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'selfservice':
//get the valid selfservice tasks for this user
$oCase = new Cases();
$tasks = $oCase->getSelfServiceTasks($_SESSION['USER_LOGGED']);
$aTasks = array();
foreach ($tasks as $key => $val) {
if (strlen(trim($val['uid'])) > 10) {
$aTasks[] = $val['uid'];
}
}
$c = new Criteria('workflow');
$c->clearSelectColumns();
$c->addSelectColumn(ApplicationPeer::APP_UID);
$c->addSelectColumn(ApplicationPeer::APP_NUMBER);
$c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE);
$c->addSelectColumn(ApplicationPeer::PRO_UID);
$c->addSelectColumn(ApplicationPeer::APP_INIT_USER);
$c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY);
$c->addAsColumn(
'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(),
CONCAT('<span style=\'color:red\';>', " . AppDelegationPeer::DEL_TASK_DUE_DATE .
", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") "
);
$c->addSelectColumn(AppDelegationPeer::DEL_INDEX);
$c->addSelectColumn(AppDelegationPeer::TAS_UID);
$c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE);
$c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE);
$c->addSelectColumn(UsersPeer::USR_UID);
$c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)");
$c->addSelectColumn(ApplicationPeer::APP_STATUS);
$c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE');
$c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE');
$c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE');
$c->addAlias("APP_TITLE", 'CONTENT');
$c->addAlias("PRO_TITLE", 'CONTENT');
$c->addAlias("TAS_TITLE", 'CONTENT');
$c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN);
$c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN);
$appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID);
$appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX);
$c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN);
$c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN);
$del = DBAdapter::getStringDelimiter();
$appTitleConds = array();
$appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID');
$appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del);
$appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN);
//
$proTitleConds = array();
$proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID');
$proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del);
$proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN);
//
$tasTitleConds = array();
$tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID');
$tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del);
$tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del);
$c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN);
$c->add(AppDelegationPeer::USR_UID, '');
$c->add(AppDelegationPeer::TAS_UID, $aTasks, Criteria::IN);
break;
case 'to_reassign':
$c->add(
$c->getNewCriterion(ApplicationPeer::APP_STATUS, 'TO_DO')->
addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'DRAFT'))
);
$c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL);
$c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN');
$c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN');
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
case 'sent':
$oCriteria = new Criteria('workflow');
$oCriteria->addSelectColumn(AppDelayPeer::APP_UID);
$oCriteria->add(
$oCriteria->getNewCriterion(
AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL
)->addOr(
$oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0)
)
);
$oDataset = AppDelayPeer::doSelectRS($oCriteria);
$oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC);
$oDataset->next();
$aProcesses = array();
while ($aRow = $oDataset->getRow()) {
$aProcesses[] = $aRow['APP_UID'];
$oDataset->next();
}
if (isset($aAdditionalFilter) && isset($aAdditionalFilter['MINE'])) {
$c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged));
} else {
$c->add(
$c->getNewCriterion(
ApplicationPeer::APP_INIT_USER, $sUIDUserLogged
)->addOr(
$c->getNewCriterion(
AppDelegationPeer::USR_UID, $sUIDUserLogged
)
)
);
}
if (isset($aAdditionalFilter) && isset($aAdditionalFilter['APP_STATUS_FILTER'])) {
$c->add(ApplicationPeer::APP_STATUS, $sValue, Criteria::EQUAL);
} else {
$c->add(ApplicationPeer::APP_STATUS, 'DRAFT', Criteria::NOT_EQUAL);
}
$c->add(
$c->getNewCriterion(
AppDelegationPeer::DEL_THREAD_STATUS, 'CLOSED'
)->addOr(
$c->getNewCriterion(
ApplicationPeer::APP_STATUS, 'COMPLETED'
)->addAnd(
$c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0)
)
)
);
$c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN));
$c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER);
break;
}
//select the xmlList to show
if ($sTypeList == 'gral') {
if ($RBAC->userCanAccess('PM_DELETECASE') == 1) {
$xmlfile = $filesList['Alldelete'];
} else {
$xmlfile = $filesList['all'];
}
} else {
$xmlfile = $filesList[$sTypeList];
}
if ($ClearSession) {
//OPCION_1: claening the $_SESSION and updating the List.... only case List
foreach ($filesList as $file) {
$id = G::createUID('', $file . '.xml');
unset($_SESSION['pagedTable[' . $id . ']']);
unset($_SESSION[$id]);
}
//OPTION_2: cleaning the $_SESSION and whole List and xmls
$cur = array_keys($_SESSION);
foreach ($cur as $key) {
if (substr($key, 0, 11) === "pagedTable[") {
unset($_SESSION[$key]);
} else {
$xml = G::getUIDName($key, '');
if (strpos($xml, '.xml') !== false) {
unset($_SESSION[$key]);
}
}
}
}
return array($c, $xmlfile);
}
/**
* Test the Criteria::CUSTOM behavior.
*/
public function testCustomOperator()
{
$c = new Criteria();
$c->addSelectColumn('A.COL');
$c->add('A.COL', 'date_part(\'YYYY\', A.COL) = \'2007\'', Criteria::CUSTOM);
$expected = "SELECT A.COL FROM A WHERE date_part('YYYY', A.COL) = '2007'";
$result = BasePeer::createSelectSql($c, $params = array());
$this->assertEquals($expected, $result);
}
/**
* Build a string representation of the Criteria.
*
* @return string A String with the representation of the Criteria.
*/
public function toString()
{
$sb = "Criteria:";
try {
$params = array();
$sb .= "\nSQL (may not be complete): " . BasePeer::createSelectSql($this, $params);
$sb .= "\nParams: ";
$paramstr = array();
foreach ($params as $param) {
$paramstr[] = $param['table'] . '.' . $param['column'] . ' => ' . var_export($param['value'], true);
}
$sb .= implode(", ", $paramstr);
} catch (Exception $exc) {
$sb .= "(Error: " . $exc->getMessage() . ")";
}
return $sb;
}
$headPublisher = &headPublisher::getSingleton();
//cambiar esto por PROPEL //CASE_CONSOLIDATED TASK
$usrUid = $_SESSION["USER_LOGGED"];
$oCriteria = new Criteria("workflow");
$oCriteria->addSelectColumn("*");
$oCriteria->addSelectColumn(CaseConsolidatedPeer::TAS_UID);
$oCriteria->addJoin(CaseConsolidatedPeer::TAS_UID,ContentPeer::CON_ID, Criteria::LEFT_JOIN);
$oCriteria->addJoin(CaseConsolidatedPeer::TAS_UID,TaskPeer::TAS_UID, Criteria::LEFT_JOIN);
$oCriteria->addAnd(ContentPeer::CON_CATEGORY, "TAS_TITLE");
$oCriteria->addAnd(ContentPeer::CON_LANG, "en");
$params = array(); //This will be filled with the parameters
$sql = BasePeer::createSelectSql($oCriteria, $params);
$oDataset = CaseConsolidatedPeer::doSelectRS($oCriteria);
$oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC);
//$oDataset->next();
while ($oDataset->next()) {
$aRow = $oDataset->getRow();
//$aTaskConsolidated [] = $aRow;
}
$query = "SELECT *
FROM CASE_CONSOLIDATED LEFT JOIN CONTENT ON
(CASE_CONSOLIDATED.TAS_UID = CONTENT.CON_ID) LEFT JOIN TASK ON (CASE_CONSOLIDATED.TAS_UID = TASK.TAS_UID)
WHERE CONTENT.CON_CATEGORY='TAS_TITLE' AND CONTENT.CON_LANG='en'";
$aTaskConsolidated = executeQuery($query);
protected function getCountStatement($con = null)
{
$dbMap = Propel::getDatabaseMap($this->getDbName());
$db = Propel::getDB($this->getDbName());
if ($con === null) {
$con = Propel::getConnection($this->getDbName(), Propel::CONNECTION_READ);
}
// check that the columns of the main class are already added (if this is the primary ModelCriteria)
if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) {
$this->addSelfSelectColumns();
}
$needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers());
$con->beginTransaction();
try {
$this->basePreSelect($con);
$params = array();
if ($needsComplexCount) {
if (BasePeer::needsSelectAliases($this)) {
if ($this->getHaving()) {
throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part');
}
BasePeer::turnSelectColumnsToAliases($this);
}
$selectSql = BasePeer::createSelectSql($this, $params);
$sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt';
} else {
// Replace SELECT columns with COUNT(*)
$this->clearSelectColumns()->addSelectColumn('COUNT(*)');
$sql = BasePeer::createSelectSql($this, $params);
}
$stmt = $con->prepare($sql);
BasePeer::populateStmtValues($stmt, $params, $dbMap, $db);
$stmt->execute();
$con->commit();
} catch (PropelException $e) {
$con->rollback();
throw $e;
}
return $stmt;
}
/**
*
*/
public function testMixedJoinOrder()
{
$this->markTestIncomplete();
$c = new Criteria(BookPeer::DATABASE_NAME);
$c->addSelectColumn(BookPeer::ID);
$c->addSelectColumn(BookPeer::TITLE);
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN);
$c->addJoin(BookPeer::AUTHOR_ID, AuthorPeer::ID);
$params = array();
$sql = BasePeer::createSelectSql($c, $params);
$expectedSql = "SELECT book.ID, book.TITLE FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID), author WHERE book.AUTHOR_ID=author.ID";
// print $sql . "\n";
}
public function testClone()
{
$bookQuery1 = BookQuery::create()->filterByPrice(1);
$bookQuery2 = clone $bookQuery1;
$bookQuery2->filterByPrice(2);
$params = array();
$sql = BasePeer::createSelectSql($bookQuery1, $params);
$this->assertEquals('SELECT FROM `book` WHERE book.PRICE=:p1', $sql, 'conditions applied on a cloned query don\'t get applied on the original query');
}
public function testCombineDirtyOperators()
{
$this->c->addCond('cond1', "INVOICE.COST1", "1000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond2', "INVOICE.COST2", "2000", Criteria::LESS_EQUAL);
$this->c->combine(array('cond1', 'cond2'), 'AnD', 'cond12');
$this->c->addCond('cond3', "INVOICE.COST3", "8000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond4', "INVOICE.COST4", "9000", Criteria::LESS_EQUAL);
$this->c->combine(array('cond3', 'cond4'), 'aNd', 'cond34');
$this->c->combine(array('cond12', 'cond34'), 'oR');
$expect = "SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))";
$expect_params = array(array('table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'), array('table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'), array('table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'), array('table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000'));
$params = array();
$result = BasePeer::createSelectSql($this->c, $params);
$this->assertEquals($expect, $result);
$this->assertEquals($expect_params, $params);
}
/**
* Build a string representation of the Criteria.
*
* @return string A String with the representation of the Criteria.
*/
public function toString()
{
$sb = "Criteria:: ";
try {
$sb .= "\nCurrent Query SQL (may not be complete or applicable): " . BasePeer::createSelectSql($this, $params = array());
$sb .= "\nParameters to replace: " . var_export($params, true);
} catch (Exception $exc) {
$sb .= "(Error: " . $exc->getMessage() . ")";
}
return $sb;
}
public static function doSelectWithTitle(Criteria $c, $culture = null, $include_unpublished_pages = false, $con = null)
{
$dbMap = Propel::getDatabaseMap($c->getDbName());
if ($con === null) {
$con = Propel::getConnection($c->getDbName());
}
if ($culture === null) {
$culture = sfContext::getInstance()->getUser()->getCulture();
}
// Set the correct dbName if it has not been overridden
if ($c->getDbName() == Propel::getDefaultDB()) {
$c->setDbName(self::DATABASE_NAME);
}
self::addSelectColumns($c);
$startcol = self::NUM_COLUMNS - self::NUM_LAZY_LOAD_COLUMNS + 1;
$c->addSelectColumn(sfSimpleCMSSlotPeer::VALUE);
if (!$include_unpublished_pages) {
$c->add(self::IS_PUBLISHED, true);
}
// Start of the complicated stuff
// ------------------------------
// big hack to have the join operate on three conditions
$c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID . ' AND ' . sfSimpleCMSSlotPeer::CULTURE . ' = ? AND ' . sfSimpleCMSSlotPeer::NAME . ' = \'title\'', Criteria::RIGHT_JOIN);
// but now we need to populate the statement by hand
$params = array();
$sql = BasePeer::createSelectSql($c, $params);
array_unshift($params, array('column' => sfSimpleCMSSlotPeer::CULTURE, 'table' => sfSimpleCMSSlotPeer::TABLE_NAME, 'value' => $culture));
$stmt = $con->prepareStatement($sql);
$stmt->setLimit($c->getLimit());
$stmt->setOffset($c->getOffset());
$i = 1;
foreach ($params as $param) {
$tableName = $param['table'];
$columnName = $param['column'];
$value = $param['value'];
if ($value === null) {
$stmt->setNull($i++);
} else {
$cMap = $dbMap->getTable($tableName)->getColumn($columnName);
$setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType());
$stmt->{$setter}($i++, $value);
}
}
$rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
// ----------------------------
// End of the complicated stuff
/*
// The complicated code above is there just to add a join on the three conditions
// It could be achieved in a simpler way with these lines
$c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID, Criteria::RIGHT_JOIN);
$c->add(sfSimpleCMSSlotPeer::CULTURE, $culture);
$c->add(sfSimpleCMSSlotPeer::NAME, 'title');
// But then pages with no title would not be visible in menus
// So we do it with more code and it's both safe and functional
*/
$results = array();
while ($rs->next()) {
$page = new sfSimpleCMSPage();
$page->hydrate($rs);
//$page->setCulture($culture);
$page->setTitle($rs->getString($startcol));
$results[] = $page;
}
return $results;
}
