public static function doSelectFiltered(Criteria $criteria, $con = null) { $criteria->add(VSpecPeer::PARENT, 1, Criteria::IN); $c3 = $criteria->getCriterion(VSpecPeer::PARENT); $c4 = new Criterion($criteria, VSpecPeer::PARENT, 7, Criteria::IN); $c5 = new Criterion($criteria, VSpecPeer::PARENT, 0, Criteria::IN); $c4->addOr($c5); $c3->addOr($c4); $tmp_depts = VSpecPeer::doSelect($criteria); $depts = array(); foreach ($tmp_depts as $key => $val) { $pi = $val->getParentalIndex(); $prefix = ''; for ($i = 0; $i < $val->level - 1; $i++) { $prefix .= ParamsPeer::retrieveByCode('tree_node_mark')->getValue(); } $val->setCode($prefix . $val->getCode()); $val->setDescription($prefix . $val->getDescription()); $depts[$pi] = $val; } ksort($depts); $result = array(); foreach ($depts as $r) { $result[] = $r; } return $result; }
public function applyFilter(Criteria $criteria_to_filter) { if (!$this->enable) { return; } if (!isset($criteria_to_filter->creteria_filter_attached)) { $criteria_to_filter->creteria_filter_attached = true; // copy all constraints from the criteria to $criteria_to_filter $columns = $this->criteria->keys(); foreach ($columns as $column) { $filter_criterion = $this->criteria->getCriterion($column); $new_crit = $criteria_to_filter->getNewCriterion($filter_criterion->getTable() . "." . $filter_criterion->getColumn(), $filter_criterion->getValue(), $filter_criterion->getComparison()); $existing_criterion = $criteria_to_filter->getCriterion($column); // don't add duplicates !! if ($existing_criterion && ($existing_criterion->getValue() == $filter_criterion->getValue() && $existing_criterion->getComparison() == $filter_criterion->getComparison())) { continue; } // go one step deeper to copy the inner clauses $this->addClauses($this->criteria, $filter_criterion, $new_crit); $criteria_to_filter->addAnd($new_crit); } // TODO - adda more robust way to copy the orderBy from this->criteria $orderBy = $this->criteria->getOrderByColumns(); if ($orderBy) { foreach ($orderBy as $orderByColumn) { @(list($name, $order) = explode(" ", $orderByColumn)); if ($order == Criteria::ASC) { $criteria_to_filter->addAscendingOrderByColumn($name); } else { $criteria_to_filter->addDescendingOrderByColumn($name); } } } } }
public function executeListApplicant() { $group_id = $this->getContext()->getUser()->getAttribute('group_id', null, 'bo'); $c = new Criteria(); $c->add(JobPeer::CODE, 'sm_book/listApplicant'); $job = JobPeer::doSelectOne($c); $acl = AclPeer::retrieveByPK($group_id, $job->getId()); $this->can_add = $acl->getAddPriv() == 1; $this->can_edit = $acl->getEditPriv() == 1; $this->can_remove = $acl->getRemovePriv() == 1; $c = new Criteria(); $dept = $this->getContext()->getUser()->getAttribute('department', null, 'bo'); $c->add(TestApplicantPeer::DEPARTMENT_1, $dept->getChildRecurs(), Criteria::IN); $c3 = $c->getCriterion(TestApplicantPeer::DEPARTMENT_1); $c4 = new Criterion($c, TestApplicantPeer::DEPARTMENT_2, $dept->getChildRecurs(), Criteria::IN); $c3->addOr($c4); $this->sortApplicant($c); if ($this->getRequest()->hasParameter('filters')) { $filters = $this->getRequestParameter('filters'); if ($filters == 'clear') { $this->filters = null; } else { $defined_filter = false; foreach ($filters as $f) { if ($f != null && $f != '') { $defined_filter = true; break; } } if ($defined_filter) { $this->filters = $filters; $this->filterApplicant($c, $this->getRequestParameter('filters')); } } } $rpp = $this->getRequestParameter('max_per_page', $this->getUser()->getAttribute('max_per_page', ParamsPeer::retrieveByCode('row_per_page')->getValue(), 'applicant')); $this->getUser()->setAttribute('max_per_page', $rpp, 'applicant'); $pager = new sfPropelPager('TestApplicant', $rpp); $pager->setCriteria($c); $page = $this->getRequestParameter('page', $this->getUser()->getAttribute('page', 1, 'applicant')); $this->getUser()->setAttribute('page', $page, 'applicant'); $pager->setPage($page); $pager->init(); $this->pager = $pager; $actions = array(array('name' => 'filter', 'color' => 'white')); $filter_string = ""; if ($this->filters) { foreach ($this->filters as $key => $val) { $filter_string .= "&filters[{$key}]={$val}"; } $filter_string = preg_replace('/^&/', '', $filter_string); } array_unshift($actions, array('name' => '_AS_CSV_', 'url' => "sm_book/listApplicantAsCSV?{$filter_string}", 'color' => 'black', 'type' => 'direct')); array_unshift($actions, array('name' => '_AS_PDF_', 'url' => "sm_book/listApplicantAsPDF?{$filter_string}", 'color' => 'black', 'type' => 'direct')); $this->actions = $actions; }
public static function doBackupRecord(\Criteria $criteria, PropelPDO $con) { $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $keys = $criteria->keys(); if (!empty($keys)) { $tableName = $criteria->getTableName($keys[0]); } else { throw new PropelException("Database insert attempted without anything specified to insert"); } $tableMap = $dbMap->getTable($tableName); $whereClause = array(); $peer = $tableMap->getPeerClassname(); $versionTable = $peer::$workspaceBehaviorVersionName; $originTable = $tableMap->getName(); $tables = $criteria->getTablesColumns(); if (empty($tables)) { throw new \PropelException("Empty Criteria"); } $fields = array_keys($tableMap->getColumns()); $fields = implode(', ', $fields); foreach ($tables as $tableName => $columns) { $whereClause = array(); $params = array(); $stmt = null; try { foreach ($columns as $colName) { $sb = ""; $criteria->getCriterion($colName)->appendPsTo($sb, $params); $whereClause[] = $sb; } $sql = sprintf("INSERT INTO %s (%s) SELECT %s FROM %s WHERE %s", $versionTable, $fields, $fields, $originTable, implode(" AND ", $whereClause)); $stmt = $con->prepare($sql); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); $stmt->closeCursor(); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute INSERT INTO statement [%s]', $sql), $e); } } // for each table }
protected static function getInvalidationKeysForQuery($invalidationKeyRules, Criteria $criteria) { foreach ($invalidationKeyRules as $invalidationKeyRule) { $invalidationKeys = array($invalidationKeyRule[0]); for ($colIndex = 1; $colIndex < count($invalidationKeyRule); $colIndex++) { $columnName = $invalidationKeyRule[$colIndex]; $criterion = $criteria->getCriterion($columnName); if (!$criterion) { $invalidationKeys = null; break; } if (in_array(Criterion::ODER, $criterion->getConjunctions())) { $invalidationKeys = null; break; } if ($criterion->getComparison() == Criteria::EQUAL) { $values = array($criterion->getValue()); } else { if ($criterion->getComparison() == Criteria::IN && count($criterion->getValue()) < self::MAX_IN_CRITERION_INVALIDATION_KEYS) { $values = $criterion->getValue(); } else { $invalidationKeys = null; break; } } $newInvalidationKeys = array(); foreach ($invalidationKeys as $invalidationKey) { foreach ($values as $value) { $newInvalidationKeys[] = self::replaceVariable($invalidationKey, str_replace(' ', '_', $value)); } } $invalidationKeys = $newInvalidationKeys; } if (!is_null($invalidationKeys)) { return $invalidationKeys; } } return array(); }
/** * Builds a params array, like the kind populated by Criterion::appendPsTo(). * This is useful for building an array even when it is not using the appendPsTo() method. * @param array $columns * @param Criteria $values * @return array params array('column' => ..., 'table' => ..., 'value' => ...) */ private static function buildParams($columns, Criteria $values) { $params = array(); foreach ($columns as $key) { if ($values->containsKey($key)) { $crit = $values->getCriterion($key); $params[] = array('column' => $crit->getColumn(), 'table' => $crit->getTable(), 'value' => $crit->getValue()); } } return $params; }
/** * Method to create select SQL. * * @param Criteria $criteria object used to create the SELECT statement. * @param String $tableName * @param Array &$params * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. */ private function createSelectSql($criteria, $tableName, &$params) { $db = Propel::getDB($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); $fromClause[] = $criteria->getDBArrayTable(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strpos($columnName, '('); $dotPos = strpos($columnName, '.'); // [HL] I think we really only want to worry about adding stuff to // the fromClause if this function has a TABLE.COLUMN in it at all. // e.g. COUNT(*) should not need this treatment -- or there needs to // be special treatment for '*' if ($dotPos !== false) { if ($parenPos === false) { // table.column $tableName = substr($columnName, 0, $dotPos); } else { // FUNC(table.column) $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1)); // functions may contain qualifiers so only take the last // word as the table name. // COUNT(DISTINCT books.price) $lastSpace = strpos($tableName, ' '); if ($lastSpace !== false) { // COUNT(DISTINCT books.price) $tableName = substr($tableName, $lastSpace + 1); } } $tableName2 = $criteria->getTableForAlias($tableName); if ($tableName2 !== null) { $fromClause[] = $tableName2 . ' ' . $tableName; } else { $fromClause[] = $tableName; } } // if $dotPost !== null } // set the aliases foreach ($aliases as $alias => $col) { $selectClause[] = $col . " AS " . $alias; } // add the criteria to WHERE clause foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $someCriteria = $criterion->getAttachedCriterion(); $someCriteriaLength = count($someCriteria); $table = null; for ($i = 0; $i < $someCriteriaLength; $i++) { $tableName = $someCriteria[$i]->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType() == "string"; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $cri['table'] = $criterion->table; $cri['field'] = $criterion->column; $cri['comparison'] = $criterion->comparison == '=' ? '==' : $criterion->comparison; $cri['value'] = $criterion->getValue(); $sb = ""; $sb .= "\$row['" . $cri['field'] . "'] " . $cri['comparison'] . "'" . $cri['value'] . "'"; $params[] = $cri; //$criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // Unique from clause elements $fromClause = array_unique($fromClause); 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 = strpos($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 = ''; } $orderByClause[] = array('columnName' => $columnName, 'direction' => $direction); } } // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . $fromClause[0] . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : ""); $dataSql['selectClause'] = $selectClause; $dataSql['fromClause'] = $fromClause; $dataSql['whereClause'] = $whereClause; $dataSql['orderByClause'] = $orderByClause; $dataSql['sql'] = $sql; return $dataSql; }
public function testClone() { $c1 = new Criteria(); $c1->add('tbl.COL1', 'foo', Criteria::EQUAL); $c2 = clone $c1; $c2->addAnd('tbl.COL1', 'bar', Criteria::EQUAL); $nbCrit = 0; foreach ($c1->keys() as $key) { foreach ($c1->getCriterion($key)->getAttachedCriterion() as $criterion) { $nbCrit++; } } $this->assertEquals(1, $nbCrit, 'cloning a Criteria clones its Criterions'); }
protected static function getInvalidationKeysForQuery($invalidationKeyRules, Criteria $criteria) { foreach ($invalidationKeyRules as $invalidationKeyRule) { $invalidationKeys = array($invalidationKeyRule[0]); // first element is the format string for ($colIndex = 1; $colIndex < count($invalidationKeyRule); $colIndex++) { $columnName = $invalidationKeyRule[$colIndex]; $criterion = $criteria->getCriterion($columnName); if (!$criterion) { $invalidationKeys = null; break; } $values = self::getCriterionValues($criterion, $columnName); if ($values === null) { $invalidationKeys = null; break; } $newInvalidationKeys = array(); foreach ($invalidationKeys as $invalidationKey) { foreach ($values as $value) { $value = strtolower(str_replace(' ', '_', $value)); $newInvalidationKeys[] = self::replaceVariable($invalidationKey, $value); } } $invalidationKeys = $newInvalidationKeys; } if (!is_null($invalidationKeys)) { return $invalidationKeys; } } return array(); }
public function executeEditParalel() { $counseling_id = $this->getRequestParameter('counseling_id'); $counseling = CounselingPeer::retrieveByPK($counseling_id); $this->forward404Unless($counseling); $accal_id = $this->getRequestParameter('accal_id'); $academic_calendar = AcademicCalendarPeer::retrieveByPK($accal_id); $this->forward404Unless($academic_calendar); #Kelas 1 if ($counseling->getClassGroup()->getParent() == 42) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 42); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 2 } elseif ($counseling->getClassGroup()->getParent() == 48 || $counseling->getClassGroup()->getParent() == 123) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 48, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 123, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 3 } elseif ($counseling->getClassGroup()->getParent() == 54 || $counseling->getClassGroup()->getParent() == 124) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 54, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 124, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 4 } elseif ($counseling->getClassGroup()->getParent() == 59 || $counseling->getClassGroup()->getParent() == 125) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 59, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 125, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 5 } elseif ($counseling->getClassGroup()->getParent() == 65) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 65); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 6 } elseif ($counseling->getClassGroup()->getParent() == 70) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 70); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 7 } elseif ($counseling->getClassGroup()->getParent() == 80 || $counseling->getClassGroup()->getParent() == 95) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 80, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 95); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 8 } elseif ($counseling->getClassGroup()->getParent() == 75 || $counseling->getClassGroup()->getParent() == 90) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 75, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 90); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 9 } elseif ($counseling->getClassGroup()->getParent() == 85 || $counseling->getClassGroup()->getParent() == 117) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 85, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 117); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); } else { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, $counseling->getClassGroup()->getParent()); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); } $rpp = $this->getRequestParameter('max_per_page', $this->getUser()->getAttribute('max_per_page', ParamsPeer::retrieveByCode('row_per_page')->getValue(), 'student_accal')); $this->getUser()->setAttribute('max_per_page', $rpp, 'student_accal'); $pager = new sfPropelPager('StudentAccal', $rpp); $pager->setCriteria($c); $page = $this->getRequestParameter('page', $this->getUser()->getAttribute('page', 1, 'student_accal')); $this->getUser()->setAttribute('page', $page, 'student_accal'); $pager->setPage($page); $pager->init(); $this->pager = $pager; $actions = array(array('name' => 'proses', 'type' => 'image', 'options' => array('class' => 'save_button', 'onclick' => "action_type.value=this.value"))); $this->actions = $actions; $actions2 = array(array('name' => '<span>' . $academic_calendar->toString() . '</span>', 'url' => 'counseling_hist/listDetail?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); array_push($actions2, array('name' => '<span>Rekap Nilai UTS</span>', 'url' => 'counseling_hist/listUts?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); array_push($actions2, array('name' => '<span>Rekap Nilai UAS</span>', 'url' => 'counseling_hist/listUas?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); array_push($actions2, array('name' => '<span>Ledger Kelas</span>', 'url' => 'counseling_hist/listLedger?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); array_push($actions2, array('name' => '<span>Ranking Paralel</span>', 'url' => 'counseling_hist/listParalel?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun', 'type' => 'direct')); array_push($actions2, array('name' => '<span>Printout Raport</span>', 'url' => 'counseling_hist/listRaport?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); $this->actions2 = $actions2; $this->counseling = $counseling; $this->subtitle = $academic_calendar->getName() . ' # ' . $counseling->getClassGroup()->getParentName(); $this->academic_calendar = $academic_calendar; }
public static function slaveConnection($peerClassName, Criteria $criteria, &$con) { //Retrieve options for the current Peer $options =& self::$_peerOptions[$peerClassName]; $ops = array(Criteria::LESS_EQUAL, Criteria::LESS_THAN, Criteria::EQUAL); $apply = true; //Check if the peer options includes conditions, if not use the slave. if (isset($options['conditions']) && sizeof($options['conditions']) > 0) { //Use slave when ANY of the conditions is satisfied. $apply = false; //Build the current context pattern to check against the ctx condition. $pattern = self::getContextPattern(); //Iterate over the conditions expressions. foreach ($options['conditions'] as &$cond) { //Check if the ctx is given. if (isset($cond['ctx']) && !(preg_match($pattern, $cond['ctx']) == 1)) { continue; } if (isset($options['gen_column']) && isset($cond['added'])) { //If it is the first time, convert the added component to be a timestamp if (is_array($cond['added'])) { $cond['added'] = self::calcTimeStamp($cond['added']); } $criterion = $criteria->getCriterion($options['gen_column']); if ($criterion !== null && in_array($criterion->getComparison(), $ops)) { $added = strtotime($criterion->getValue()) <= strtotime(date('Y-m-d')) - $cond['added']; if (!$added) { continue; } } } //Redirect to the slave, and stop iterating. $apply = true; break; } } if ($apply) { $con = self::getConnectionToSlave($peerClassName::DATABASE_NAME); } }
/** * 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) { echo 1; die; $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); // redundant definition $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { // expect every column to be of "table.column" formation // it could be a function: e.g. MAX(books.price) $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strrpos($columnName, '('); $dotPos = strrpos($columnName, '.', $parenPos !== false ? $parenPos : 0); // [HL] I think we really only want to worry about adding stuff to // the fromClause if this function has a TABLE.COLUMN in it at all. // e.g. COUNT(*) should not need this treatment -- or there needs to // be special treatment for '*' if ($dotPos !== false) { if ($parenPos === false) { // table.column $tableName = substr($columnName, 0, $dotPos); } else { // FUNC(table.column) $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1)); // functions may contain qualifiers so only take the last // word as the table name. // COUNT(DISTINCT books.price) $lastSpace = strpos($tableName, ' '); if ($lastSpace !== false) { // COUNT(DISTINCT books.price) $tableName = substr($tableName, $lastSpace + 1); } } $tableName2 = $criteria->getTableForAlias($tableName); if ($tableName2 !== null) { $fromClause[] = $tableName2 . ' ' . $tableName; } else { $fromClause[] = $tableName; } } // if $dotPost !== null } // set the aliases foreach ($aliases as $alias => $col) { $selectClause[] = $col . " AS " . $alias; } // add the criteria to WHERE clause // this will also add the table names to the FROM clause if they are not already // invluded via a LEFT JOIN foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $someCriteria = $criterion->getAttachedCriterion(); $someCriteriaLength = count($someCriteria); $table = null; for ($i = 0; $i < $someCriteriaLength; $i++) { $tableName = $someCriteria[$i]->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && strpos($dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType(), "VARCHAR") !== false; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $sb = ""; $criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // 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 ((array) $criteria->getJoins() as $join) { // The join might have been established using an alias name $leftTable = $join->getLeftTableName(); $leftTableAlias = ''; if ($realTable = $criteria->getTableForAlias($leftTable)) { $leftTableAlias = " {$leftTable}"; $leftTable = $realTable; } $rightTable = $join->getRightTableName(); $rightTableAlias = ''; if ($realTable = $criteria->getTableForAlias($rightTable)) { $rightTableAlias = " {$rightTable}"; $rightTable = $realTable; } // determine if casing is relevant. if ($ignoreCase = $criteria->isIgnoreCase()) { $leftColType = $dbMap->getTable($leftTable)->getColumn($join->getLeftColumnName())->getType(); $rightColType = $dbMap->getTable($rightTable)->getColumn($join->getRightColumnName())->getType(); $ignoreCase = $leftColType == 'string' || $rightColType == 'string'; } // build the condition $condition = ''; foreach ($join->getConditions() as $index => $conditionDesc) { if ($ignoreCase) { $condition .= $db->ignoreCase($conditionDesc['left']) . $conditionDesc['operator'] . $db->ignoreCase($conditionDesc['right']); } else { $condition .= implode($conditionDesc); } if ($index + 1 < $join->countConditions()) { $condition .= ' AND '; } } // add 'em to the queues.. if ($joinType = $join->getJoinType()) { // real join if (!$fromClause) { $fromClause[] = $leftTable . $leftTableAlias; } $joinTables[] = $rightTable . $rightTableAlias; $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ({$condition})"; } else { // implicit join, translates to a where $fromClause[] = $leftTable . $leftTableAlias; $fromClause[] = $rightTable . $rightTableAlias; $whereClause[] = $condition; } } // 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()) { $orderByClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}") . $direction; $selectClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); } else { $orderByClause[] = $orderByColumn; } } } if (empty($fromClause) && $criteria->getPrimaryTableName()) { $fromClause[] = $criteria->getPrimaryTableName(); } // from / join tables quoten if it is necessary if ($db->useQuoteIdentifier()) { $fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause); $joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause); } // 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 = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " 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()); } return $sql; }
public function executeEditParalelByEmployee() { $group_id = $this->getContext()->getUser()->getAttribute('group_id', null, 'bo'); $c = new Criteria(); $c->add(JobPeer::CODE, 'counseling_menu/listLedger'); $job = JobPeer::doSelectOne($c); $acl = AclPeer::retrieveByPK($group_id, $job->getId()); if (!$acl) { $this->forward('default', 'error404'); } $this->can_edit = $acl->getEditPriv() == 1; $this->can_remove = $acl->getRemovePriv() == 1; $employee_id = sfContext::getInstance()->getUser()->getAttribute('user_id', null, 'bo'); $employee = EmployeePeer::retrieveByPK($employee_id); $this->forward404Unless($employee); $counseling_id = $this->getRequestParameter('counseling_id'); $counseling = CounselingPeer::retrieveByPK($counseling_id); $this->forward404Unless($counseling); $accal_id = $this->getRequestParameter('accal_id'); $academic_calendar = AcademicCalendarPeer::retrieveByPK($accal_id); $this->forward404Unless($academic_calendar); #Kelas 1 if ($counseling->getClassGroup()->getParent() == 42) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 42); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 2 } elseif ($counseling->getClassGroup()->getParent() == 48 || $counseling->getClassGroup()->getParent() == 123) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 48, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 123, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 3 } elseif ($counseling->getClassGroup()->getParent() == 54 || $counseling->getClassGroup()->getParent() == 124) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 54, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 124, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 4 } elseif ($counseling->getClassGroup()->getParent() == 59 || $counseling->getClassGroup()->getParent() == 125) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 59, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 125, Criteria::IN); $c3->addOr($c4); $c->add($c3); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 5 } elseif ($counseling->getClassGroup()->getParent() == 65) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 65); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 6 } elseif ($counseling->getClassGroup()->getParent() == 70) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 70); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 7 } elseif ($counseling->getClassGroup()->getParent() == 80 || $counseling->getClassGroup()->getParent() == 95) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 80, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 95); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 8 } elseif ($counseling->getClassGroup()->getParent() == 75 || $counseling->getClassGroup()->getParent() == 90) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 75, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 90); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); #Kelas 9 } elseif ($counseling->getClassGroup()->getParent() == 85 || $counseling->getClassGroup()->getParent() == 117) { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, 85, Criteria::IN); $c3 = $c->getCriterion(ClassGroupPeer::PARENT); $c4 = new Criterion($c, ClassGroupPeer::PARENT, 117); $c3->addOr($c4); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); } else { $c = new Criteria(); $c->add(ClassGroupPeer::PARENT, $counseling->getClassGroup()->getParent()); $c->addJoin(StudentAccalPeer::CLASS_GROUP_ID, ClassGroupPeer::ID); $c->add(StudentAccalPeer::ACADEMIC_CALENDAR_ID, $academic_calendar->getId()); $c->addDescendingOrderByColumn(StudentAccalPeer::TOTAL_SCORE); } $rpp = $this->getRequestParameter('max_per_page', $this->getUser()->getAttribute('max_per_page', ParamsPeer::retrieveByCode('row_per_page')->getValue(), 'student_accal')); $this->getUser()->setAttribute('max_per_page', $rpp, 'student_accal'); $pager = new sfPropelPager('StudentAccal', $rpp); $pager->setCriteria($c); $page = $this->getRequestParameter('page', $this->getUser()->getAttribute('page', 1, 'student_accal')); $this->getUser()->setAttribute('page', $page, 'student_accal'); $pager->setPage($page); $pager->init(); $this->pager = $pager; $actions = array(array('name' => 'proses', 'type' => 'image', 'options' => array('class' => 'save_button', 'onclick' => "action_type.value=this.value"))); $this->actions = $actions; $actions2 = array(array('name' => '<span>' . $academic_calendar->toString() . '</span>', 'url' => 'counseling_menu/listLedger', 'color' => 'sun')); array_push($actions2, array('name' => '<span>Ledger Nilai Raport</span>', 'url' => 'counseling_menu/listAllByEmployee?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun')); array_push($actions2, array('name' => '<span>Ranking Paralel</span>', 'url' => 'counseling_menu/listParalelByEmployee?accal_id=' . $academic_calendar->getId() . '&counseling_id=' . $counseling->getId(), 'color' => 'sun', 'type' => 'direct')); $this->actions2 = $actions2; $this->counseling = $counseling; $this->subtitle = $academic_calendar->toString() . ' # ' . $counseling->getClassGroup()->getParentName(); $this->employee_id = $employee_id; $this->employee = $employee; $this->academic_calendar = $academic_calendar; }