PHP CRequest::addOrder方法代码示例

 /**
  * @return string[]
  */
 function getItemNames()
 {
     $item = new CExListItem();
     $where = array($this->getBackRefField() => "= '{$this->_id}'");
     $request = new CRequest();
     $request->addWhere($where);
     $request->addTable($item->_spec->table);
     $request->addOrder("LPAD(code, 20, '0'), name");
     $request->addSelect(array($item->_spec->key, "name"));
     $ds = $item->_spec->ds;
     return $ds->loadHashList($request->makeSelect());
 }

 /**
  * Get random value
  *
  * @param string $field       Field name
  * @param bool   $is_not_null Search field not null
  *
  * @return mixed
  */
 function getRandomValue($field, $is_not_null = false)
 {
     $ds = $this->getDS();
     $query = new CRequest();
     $query->addSelect($field);
     $query->addTable($this->_spec->table);
     if ($is_not_null) {
         $query->addWhereClause($field, "IS NOT NULL");
     }
     $query->addOrder("RAND()");
     $query->setLimit(1);
     return $ds->loadResult($query->makeSelect());
 }

if ($merge_dates) {
    CMbArray::removeValue("date_debut", $fields);
}
$select = $fields;
$select["TOTAL"] = "COUNT(*)";
$select["IDS"] = "GROUP_CONCAT(CAST({$spec->key} AS CHAR))";
$orderby = "TOTAL DESC";
$count_min = max(1, $count_min);
$having = array("TOTAL" => $spec->ds->prepare("> ?", $count_min));
$where = array("patient_id" => "IS NOT NULL");
$request = new CRequest();
$request->addSelect($select);
$request->addTable($spec->table);
$request->addGroup($fields);
$request->addWhere($where);
$request->addOrder($orderby);
$request->addHaving($having);
$list = $spec->ds->loadList($request->makeSelect());
$count_total = 0;
foreach ($list as $_corresp) {
    $ids = explode(",", $_corresp["IDS"]);
    if (empty($ids)) {
        continue;
    }
    array_unique($ids);
    sort($ids);
    array_pop($ids);
    // Only keep last
    CAppUI::stepAjax(" -- Patient #" . $_corresp["patient_id"], UI_MSG_OK);
    $count = 0;
    foreach ($ids as $_id) {

        $sejourReq->addWhereClause("sejour.praticien_id", CSQLDataSource::prepareIn(array_keys($speChirs)));
    }
}
if ($filter->_filter_type) {
    $sejourReq->addWhereClause("sejour.type", "= '{$filter->_filter_type}'");
}
if ($filter->convalescence == "o") {
    $sejourReq->addWhereClause(null, "(sejour.convalescence IS NOT NULL AND sejour.convalescence != '')");
}
if ($filter->convalescence == "n") {
    $sejourReq->addWhereClause(null, "(sejour.convalescence IS NULL OR sejour.convalescence = '')");
}
if ($filter->consult_accomp) {
    $sejourReq->addWhereClause(null, "(sejour.consult_accomp = '" . $filter->consult_accomp . "')");
}
$sejourReq->addOrder("DATE(sejour.{$filter->_horodatage})");
$sejourReq->addOrder("users.user_last_name");
$sejourReq->addOrder("users.user_first_name");
if ($filter->_admission == "heure") {
    $sejourReq->addOrder("TIME(sejour.{$filter->_horodatage})");
} else {
    $sejourReq->addOrder("patients.nom");
    $sejourReq->addOrder("patients.prenom");
    $sejourReq->addOrder("DATE(sejour.{$filter->_horodatage})");
}
$sejours = $sejours->loadListByReq($sejourReq);
$listDays = array();
$listPrats = array();
// Liste des services
$service = new CService();
$where = array();

 /**
  * Object count of a multiple list by an SQL request constructor using group-by statement
  *
  * @param array        $where  Array of where clauses
  * @param array|string $order  Order statement
  * @param array|string $group  Group by statement
  * @param array        $ljoin  Array of left join clauses
  * @param array        $fields Append fields to the SELECT
  * @param array|string $index  Force index
  *
  * @return self[]
  */
 function countMultipleList($where = null, $order = null, $group = null, $ljoin = null, $fields = array(), $index = null)
 {
     if (!$this->_ref_module) {
         return null;
     }
     $request = new CRequest();
     $request->addWhere($where);
     $request->addOrder($order);
     $request->addGroup($group);
     $request->addLJoin($ljoin);
     $request->addForceIndex($index);
     $ds = $this->_spec->ds;
     return $ds->loadList($request->makeSelectCount($this, $fields));
 }

}
// filtre sur le praticien
if ($prat_id) {
    $where["sejour.praticien_id"] = "= '{$prat_id}'";
}
$where["sejour.entree"] = " BETWEEN '{$month_min}' AND '{$nextmonth}'";
$where["sejour.group_id"] = " = '{$group->_id}'";
$where["sejour.annule"] = " = '0'";
// Liste des admissions par jour
$request = new CRequest();
$request->addSelect(array("DATE_FORMAT(sejour.entree, '%Y-%m-%d') AS 'date'", "COUNT(sejour.sejour_id) AS 'num'"));
$request->addTable("sejour");
$request->addWhere($where);
$request->addLJoin($leftjoin);
$request->addGroup("date");
$request->addOrder("date");
foreach ($ds->loadHashList($request->makeSelect()) as $day => $num1) {
    $days[$day]["admissions"] = $num1;
}
// Liste des admissions non préparées
$where["sejour.entree_preparee"] = " = '0'";
$request->addWhere($where);
foreach ($ds->loadHashList($request->makeSelect()) as $day => $num3) {
    $days[$day]["admissions_non_preparee"] = $num3;
}
// Liste des admissions non effectuées par jour
unset($where['sejour.entree']);
unset($where['sejour.entree_preparee']);
$request->where = array();
$where["sejour.entree_prevue"] = " BETWEEN '{$month_min}' AND '{$nextmonth}'";
$where["sejour.entree_reelle"] = " IS NULL";

 // Eventuelle deuxième requête (pour les lines mixes)
 if (!$commentaire && $one_field_presc) {
     $request_b = new CRequest();
     if ($one_field_presc) {
         $other_fields = ", prescription_line_mix_item.prescription_line_mix_item_id";
     }
     if ($one_field_atcd) {
         $other_fields .= ", antecedent.antecedent_id";
     }
     if ($one_field_traitement) {
         $other_fields .= ", traitement.traitement_id";
     }
     if ($consult_filled) {
         $request_b->addSelect("consultation.consultation_id, patients.patient_id" . $other_fields);
         $request_b->addTable("consultation");
         $request_b->addOrder("patients.nom ASC, plageconsult.date ASC");
     } elseif ($sejour_filled) {
         $request_b->addSelect("sejour.sejour_id, patients.patient_id" . $other_fields);
         $request_b->addTable("sejour");
         $request_b->addOrder("patients.nom ASC, sejour.entree_prevue ASC");
     } elseif ($interv_filled) {
         $request_b->addSelect("operations.operation_id, patients.patient_id" . $other_fields);
         $request_b->addTable("operations");
         $request_b->addOrder("patients.nom ASC, operations.date ASC");
     } else {
         $request_b->addSelect("patients.patient_id");
         $request_b->addTable("patients");
         $request_b->addOrder("patients.nom ASC");
     }
     $request_b->addLJoin($ljoin);
     $request_b->addRJoin($rjoinMix);

$error_logs_similar = array();
$list_ids = array();
/** @var CErrorLog[] $error_logs */
$error_logs = array();
/** @var CUser[] $users */
$users = array();
if ($group_similar && $group_similar !== 'no') {
    if ($group_similar === 'signature') {
        $groupby = "signature_hash";
    }
    if ($group_similar === 'similar') {
        $groupby = "text, stacktrace_id, param_GET_id, param_POST_id";
    }
    $request = new CRequest();
    $request->addWhere($where);
    $request->addOrder($order);
    $request->addGroup($groupby);
    $request->setLimit($limit);
    $fields = array("GROUP_CONCAT(error_log_id) AS similar_ids", "GROUP_CONCAT(user_id)      AS similar_user_ids", "GROUP_CONCAT(server_ip)    AS similar_server_ips", "MIN(datetime) AS datetime_min", "MAX(datetime) AS datetime_max");
    $error_logs_similar = $ds->loadList($request->makeSelectCount($error_log, $fields));
    $request->setLimit(null);
    $total = count($ds->loadList($request->makeSelectCount($error_log, $fields)));
    $user_ids = array();
    foreach ($error_logs_similar as $_info) {
        $similar_ids = explode(",", $_info["similar_ids"]);
        $error_log = new CErrorLog();
        $error_log->load(reset($similar_ids));
        $error_log->_similar_ids = $similar_ids;
        $error_log->_similar_count = $_info["total"];
        $error_log->_datetime_min = $_info["datetime_min"];
        $error_log->_datetime_max = $_info["datetime_max"];

$query->addColumn("date");
$query->addGroup("date");
$query->addColumn("debut");
$query->addGroup("debut");
$query->addColumn("fin");
$query->addGroup("fin");
$query->addColumn("chir_id");
$query->addGroup("chir_id");
$query->addColumn("spec_id");
$query->addGroup("spec_id");
$query->addColumn("plagesop.salle_id");
$query->addGroup("plagesop.salle_id");
$query->addLJoinClause("sallesbloc", "sallesbloc.salle_id = plagesop.salle_id");
$query->addLJoinClause("bloc_operatoire", "bloc_operatoire.bloc_operatoire_id = sallesbloc.bloc_id");
$query->addWhereClause("bloc_operatoire.group_id", "= '{$group->_id}'");
$query->addOrder("plagesop.date");
$query->addHaving("plage_count > 1");
$ds = $plage->_spec->ds;
$duplicates = $ds->loadColumn($query->makeSelect($plage));
$count = count($duplicates);
$success_count = 0;
$failures = array();
$i = $max;
if ($merge) {
    foreach ($duplicates as $_plage_ids) {
        if (!$i--) {
            break;
        }
        $plage_ids = explode("-", $_plage_ids);
        $plages = $plage->loadAll($plage_ids);
        /** @var CPlageOp $first */

CCanDo::checkAdmin();
$type = CValue::get("type", "CEvenementSSR");
$date = CValue::get("date");
$period = CValue::get("period", "month");
$stats = new CMediusersStats($date, $period, "DATE(debut)", 18);
$consult = new CConsultation();
$ds = $consult->_spec->ds;
$group = CGroups::loadCurrent();
$query = new CRequest();
$query->addColumn("COUNT(*) total");
$query->addColumn("therapeute_id", "user_id");
$query->addColumn($stats->sql_date, "refdate");
$query->addWhere("{$stats->sql_date} BETWEEN '{$stats->min_date}' AND '{$stats->max_date}'");
$query->addWhereClause("functions_mediboard.group_id", "= '{$group->_id}'");
$query->addGroup("therapeute_id, refdate");
$query->addOrder("refdate DESC");
$totals = array();
switch ($type) {
    case "CEvenementSSR":
        $query->addTable("`evenement_ssr`");
        $query->addLJoinClause("sejour", "sejour.sejour_id = evenement_ssr.sejour_id");
        $query->addLJoinClause("users_mediboard", "users_mediboard.user_id = evenement_ssr.therapeute_id");
        $query->addLJoinClause("functions_mediboard", "functions_mediboard.function_id = users_mediboard.function_id");
        // Réalisés
        $query1 = clone $query;
        $query1->addWhereClause("evenement_ssr.realise", "= '1'");
        foreach ($ds->loadList($query1->makeSelect()) as $_row) {
            $stats->addTotal($_row["user_id"], $_row["refdate"], $_row["total"], "realises");
        }
        // Annulés
        $query1 = clone $query;

 * @package    Mediboard
 * @subpackage Stock
 * @author     SARL OpenXtrem <dev@openxtrem.com>
 * @license    GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version    $Revision$
 */
CCanDo::checkEdit();
$ratio = (double) CValue::get("ratio", 2);
CApp::setMemoryLimit('512M');
CApp::setTimeLimit(120);
$sql = new CRequest();
$sql->addTable("product_order_item");
$sql->addSelect("\r\n  product_order_item.order_item_id,\r\n  product_order_item.reference_id, \r\n  product_reference.price AS RP, \r\n  product_order_item.unit_price AS OP, \r\n  product_order_item.quantity AS OQ, \r\n  product_order.order_id, \r\n  product_order.order_number, \r\n  product_order.date_ordered");
$sql->addLJoin(array("product_reference" => "product_reference.reference_id = product_order_item.reference_id", "product_order" => "product_order.order_id = product_order_item.order_id"));
$sql->addWhere("\r\n  product_order.cancelled = '0' \r\n  AND (product_reference.cancelled = '0' OR product_reference.cancelled IS NULL)\r\n  AND product_reference.price != product_order_item.unit_price\r\n  AND (\r\n    product_order_item.unit_price > product_reference.price*{$ratio} OR \r\n    product_reference.price > product_order_item.unit_price*{$ratio}\r\n  )");
$sql->addOrder("product_reference.code");
$changes = $this->_spec->ds->loadList($sql->makeSelect());
$changes_struct = array();
$references = array();
$references_cahpp = array();
foreach ($changes as $_change) {
    if (!isset($references[$_change["reference_id"]])) {
        $_reference = new CProductReference();
        $_reference->load($_change["reference_id"]);
        $references[$_reference->_id] = $_reference;
        $article = new CCAHPPArticle();
        $where = array("reference_fournisseur" => $article->_spec->ds->prepare("=%", $_reference->supplier_code));
        if (!$article->loadObject($where)) {
            $where = array("cip" => $article->_spec->ds->prepare("=%", $_reference->loadRefProduct()->code));
            $article->loadObject($where);
        }

 /**
  * Get observation results for this object
  *
  * @param CMbObject $object Reference object
  * @param bool      $utf8   Encode data int UTF-8
  *
  * @return array|CObservationResultSet[]
  */
 static function getResultsFor(CMbObject $object, $utf8 = true)
 {
     $request = new CRequest();
     $request->addTable("observation_result");
     $request->addSelect("*");
     $request->addLJoin(array("observation_result_set" => "observation_result_set.observation_result_set_id = observation_result.observation_result_set_id", "user_log" => "observation_result_set.observation_result_set_id = user_log.object_id AND\r\n                                     user_log.object_class = 'CObservationResultSet' AND user_log.type = 'create'", "users" => "users.user_id = user_log.user_id"));
     $request->addWhere(array("observation_result_set.context_class" => "= '{$object->_class}'", "observation_result_set.context_id" => "= '{$object->_id}'"));
     $request->addOrder("observation_result_set.datetime");
     $request->addOrder("observation_result.observation_result_id");
     $results = $object->_spec->ds->loadList($request->makeSelect());
     $times = array();
     $data = array();
     foreach ($results as $_result) {
         $_time = CMbDate::toUTCTimestamp($_result["datetime"]);
         $times[$_time] = $_result["datetime"];
         $unit_id = $_result["unit_id"] ? $_result["unit_id"] : "none";
         $label = null;
         if ($_result["label_id"]) {
             $label_obj = new CSupervisionGraphAxisValueLabel();
             $label_obj->load($_result["label_id"]);
             $label = $label_obj->title;
         }
         $float_value = $_result["value"];
         $float_value = CMbFieldSpec::checkNumeric($float_value, false);
         $_user_name = $_result["user_first_name"] . " " . $_result["user_last_name"];
         $data[$_result["value_type_id"]][$unit_id][] = array(0 => $_time, 1 => $float_value, "ts" => $_time, "value" => $_result["value"], "datetime" => $_result["datetime"], "file_id" => $_result["file_id"], "set_id" => $_result["observation_result_set_id"], "result_id" => $_result["observation_result_id"], "label_id" => $_result["label_id"], "label" => $utf8 ? utf8_encode($label) : $label, "user_id" => $_result["user_id"], "user" => $utf8 ? utf8_encode($_user_name) : $_user_name);
     }
     return array($data, $times);
 }

 /**
  * Get the formula field
  *
  * @param string $field_name Field name
  * @param array  $where      The WHERE statement
  *
  * @return array|null
  */
 function getFormulaResult($field_name, $where)
 {
     $ds = $this->getDS();
     $table = $this->getTableName();
     $where["ex_link.ex_class_id"] = "= '{$this->_id}'";
     $ljoin = array("ex_link" => "ex_link.ex_object_id = {$table}.ex_object_id");
     $request = new CRequest();
     $request->addSelect($field_name);
     $request->addTable($table);
     $request->addWhere($where);
     $request->addLJoin($ljoin);
     $request->addOrder("ex_link.ex_object_id DESC");
     return $ds->loadResult($request->makeSelect());
 }

/**
 * Récuparation du graphique du nombre d'interventions annulées le jour même
 *
 * @param string $date_min      Date de début
 * @param string $date_max      Date de fin
 * @param int    $prat_id       Filtre du praticien
 * @param int    $salle_id      Filtre de la salle
 * @param int    $bloc_id       Filtre du bloc
 * @param int    $func_id       Filtre sur un cabinet
 * @param int    $discipline_id Filtre sur une discipline
 * @param string $code_ccam     Code CCAM
 * @param string $type_sejour   Type de séjour
 * @param bool   $hors_plage    Prise en charge des hors plage
 *
 * @return array
 */
function graphWorkflowOperation($date_min = null, $date_max = null, $prat_id = null, $salle_id = null, $bloc_id = null, $func_id = null, $discipline_id = null, $code_ccam = null, $type_sejour = null, $hors_plage = false)
{
    $miner = new COperationWorkflow();
    $miner->warnUsage();
    if (!$date_min) {
        $date_min = CMbDT::date("-1 YEAR");
    }
    if (!$date_max) {
        $date_max = CMbDT::date();
    }
    $date_min = CMbDT::format($date_min, "%Y-%m-01");
    $date_max = CMbDT::transform("+1 MONTH", $date_max, "%Y-%m-01");
    // Series declarations
    $labels = array("op_count" => utf8_encode("Nombre d'interventions"), "creation" => utf8_encode("Planification intervention"), "consult_chir" => utf8_encode("Consultation chirurgicale"), "consult_anesth" => utf8_encode("Consultation anesthésiste"), "visite_anesth" => utf8_encode("Visite anesthésiste"), "creation_consult_chir" => utf8_encode("RDV de consultation chirurgicale"), "creation_consult_anesth" => utf8_encode("RDV de consultation anesthésiste"));
    $salles = CSalle::getSallesStats($salle_id, $bloc_id);
    $query = new CRequest();
    $query->addColumn("DATE_FORMAT(date_operation, '%Y-%m')", "mois");
    $query->addColumn("COUNT(operations.operation_id)", "op_count");
    // Prévention des données négatives aberrantes
    $tolerance_in_days = 0;
    $columns = array("creation", "consult_chir", "consult_anesth", "visite_anesth", "creation_consult_chir", "creation_consult_anesth");
    foreach ($columns as $_column) {
        $field = "date_{$_column}";
        $diff = "DATEDIFF(ow.date_operation, ow.{$field})";
        $query->addColumn("AVG  (IF({$diff} > {$tolerance_in_days}, {$diff}, NULL))", $_column);
        $query->addColumn("COUNT(IF({$diff} > {$tolerance_in_days}, {$diff}, NULL))", "count_{$_column}");
    }
    $query->addTable("operations");
    $query->addLJoin("operation_workflow AS ow ON ow.operation_id = operations.operation_id");
    $query->addWhereClause("date_operation", "BETWEEN '{$date_min}' AND '{$date_max}'");
    $query->addWhereClause("salle_id", CSQLDataSource::prepareIn(array_keys($salles)));
    $query->addGroup("mois");
    $query->addOrder("mois");
    $subtitle = "";
    // Filtre sur hors plage
    if (!$hors_plage) {
        $query->addWhereClause("plageop_id", "IS NOT NULL");
        $subtitle .= " - sans hors plage";
    }
    // Filtre sur le salle (pas besoin de clause supplémentaire)
    if ($salle_id) {
        $salle = reset($salles);
        $subtitle .= " - {$salle->_view}";
    }
    // Filtre sur le praticien
    if ($prat_id) {
        $query->addWhereClause("operations.chir_id", "= '{$prat_id}'");
        $prat = new CMediusers();
        $prat->load($prat_id);
        $subtitle .= " - Dr {$prat->_view}";
    }
    // Filtre sur le cabinet
    if ($func_id) {
        $query->addLJoinClause("users_mediboard", "operations.chir_id = users_mediboard.user_id");
        $query->addWhereClause("users_mediboard.function_id", "= '{$func_id}'");
        $func = new CFunctions();
        $func->load($func_id);
        $subtitle .= " - {$func->_view}";
    }
    // Filtre sur la discipline
    if ($discipline_id) {
        $discipline = new CDiscipline();
        $discipline->load($discipline_id);
        $query->addLJoinClause("users_mediboard", "operations.chir_id = users_mediboard.user_id");
        $query->addWhereClause("users_mediboard.discipline_id", "= '{$discipline_id}'");
        $subtitle .= " - {$discipline->_view}";
    }
    // Filtre sur les codes CCAM
    if ($code_ccam) {
        $query->addWhereClause("operations.codes_ccam", "LIKE '%{$code_ccam}%'");
        $subtitle .= " - CCAM: {$code_ccam}";
    }
    // Filtre sur le type d'hospitalisation
    if ($type_sejour) {
        $query->addLJoinClause("sejour", "sejour.sejour_id = operations.sejour_id");
        $query->addWhereClause("sejour.type", "= '{$type_sejour}'");
        $subtitle .= " - " . CAppUI::tr("CSejour.type.{$type_sejour}");
    }
    // Query result
    $ds = CSQLDataSource::get("std");
    $all_values = $ds->loadHashAssoc($query->makeSelect());
    // Build horizontal ticks
    $months = array();
    $ticks = array();
//.........这里部分代码省略.........

 * $Id$
 *
 * @package    Mediboard
 * @subpackage PlanningOp
 * @author     SARL OpenXtrem <dev@openxtrem.com>
 * @license    GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version    $Revision$
 */
CCanDo::checkAdmin();
// Tous les libelllés
$query = new CRequest();
$query->addTable("sejour");
$query->addColumn("COUNT(libelle)", "libelle_count");
$query->addColumn("libelle");
$query->addWhereClause("type", "= 'ssr'");
$query->addOrder("libelle_count DESC");
$query->addGroup("libelle");
$sejour = new CSejour();
$ds = $sejour->_spec->ds;
$libelle_counts = array();
foreach ($ds->loadList($query->makeSelect()) as $row) {
    $libelle_counts[$row["libelle"]] = $row["libelle_count"];
}
unset($libelle_counts[""]);
// Libellés disponibles
$colors = CColorLibelleSejour::loadAllFor(array_keys($libelle_counts));
// Création du template
$smarty = new CSmartyDP();
$smarty->assign("libelle_counts", $libelle_counts);
$smarty->assign("colors", $colors);
$smarty->display("vw_idx_colors.tpl");