PHP CRequest::makeSelect方法代码示例

 /**
  * Get the patient merge by date
  *
  * @param Date $before before date
  * @param Date $now    now date
  *
  * @return array
  */
 static function getPatientMergeByDate($before, $now)
 {
     $where = array("date >= '{$before} 00:00:00'", "date <= '{$now} 23:59:59'", "type = 'merge'", "object_class = 'CPatient'");
     $ds = CSQLDataSource::get("std");
     $ds->exec("SET SESSION group_concat_max_len = 100000;");
     $request = new CRequest();
     $request->addSelect("DATE(date) AS 'date', COUNT(*) AS 'total', GROUP_CONCAT( object_id  SEPARATOR '-') as ids");
     $request->addTable("user_log");
     $request->addWhere($where);
     $request->addGroup("DATE(date)");
     return $ds->loadList($request->makeSelect());
 }

 /**
  * @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());
 }

 /**
  * Ajout des codes pour les Groups
  *
  * @return bool
  */
 protected function addCodeToGroups()
 {
     $ds = CSQLDataSource::get("std");
     $request = new CRequest();
     $request->addSelect(array("group_id", "code", "text"));
     $request->addTable("groups_mediboard");
     $query = $request->makeSelect();
     $groups = $ds->loadList($query);
     foreach ($groups as $_group) {
         $group_id = $_group["group_id"];
         $code = CMbString::makeInitials($_group["text"]);
         $query = $ds->prepare("UPDATE `groups_mediboard` SET `code`=?1 WHERE `group_id`= ?2", $code, $group_id);
         $ds->exec($query);
     }
     return true;
 }

 function check()
 {
     $msg = parent::check();
     if (!$msg) {
         $where = array();
         $where["date"] = $this->_spec->ds->prepare("= %", $this->date);
         $where["affectation_id"] = $this->_spec->ds->prepare("= %", $this->affectation_id);
         $where["typerepas_id"] = $this->_spec->ds->prepare("= %", $this->typerepas_id);
         if ($this->repas_id) {
             $where["repas_id"] = $this->_spec->ds->prepare("!= %", $this->repas_id);
         }
         $select = "count(`" . $this->_spec->key . "`) AS `total`";
         $sql = new CRequest();
         $sql->addTable($this->_spec->table);
         $sql->addSelect($select);
         $sql->addWhere($where);
         $nbRepas = $this->_spec->ds->loadResult($sql->makeSelect());
         if ($nbRepas) {
             $msg .= "Un repas a déjà été créé, vous ne pouvez pas en créer un nouveau.";
         }
     }
     return $msg;
 }

 /**
  * Nettoie les operation_id
  *
  * @return bool
  */
 protected function cleanOperationIdError()
 {
     $ds = $this->ds;
     $where = array();
     $where["consultation_anesth.operation_id"] = "!= 0";
     $where[] = "consultation_anesth.operation_id IS NOT NULL";
     $where[] = "(SELECT COUNT(operations.operation_id) FROM operations WHERE operation_id=consultation_anesth.operation_id)=0";
     $query = new CRequest();
     $query->addSelect("consultation_anesth_id");
     $query->addTable("consultation_anesth");
     $query->addWhere($where);
     $aKeyxAnesth = $ds->loadColumn($query->makeSelect());
     if ($aKeyxAnesth === false) {
         return false;
     }
     if (count($aKeyxAnesth)) {
         $query = "UPDATE consultation_anesth SET operation_id = NULL WHERE (consultation_anesth_id " . CSQLDataSource::prepareIn($aKeyxAnesth) . ")";
         if (!$ds->exec($query)) {
             return false;
         }
         return true;
     }
     return true;
 }

// 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";
$request->addWhere($where);

<?php

/**
 * $Id$
 *
 * @package    Mediboard
 * @subpackage SSR
 * @author     SARL OpenXtrem <dev@openxtrem.com>
 * @license    GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version    $Revision$
 */
CCanDo::checkEdit();
// Liste des RHSs
$rhs = new CRHS();
$req = new CRequest();
$req->addTable("rhs");
$req->addLJoinClause("sejour", "sejour.sejour_id = rhs.sejour_id");
$req->addColumn("date_monday", "mondate");
$req->addColumn("COUNT(*)", "count");
$req->addWhereClause("rhs.facture", " = '0'");
$req->addWhereClause("sejour.annule", " = '0'");
$req->addGroup("date_monday");
$ds = $rhs->_spec->ds;
$rhs_counts = $ds->loadList($req->makeSelect());
foreach ($rhs_counts as &$_rhs_count) {
    $_rhs_count["sundate"] = CMbDT::date("+6 DAYS", $_rhs_count["mondate"]);
}
// Création du template
$smarty = new CSmartyDP();
$smarty->assign("rhs_counts", $rhs_counts);
$smarty->display("vw_facturation_rhs.tpl");

        $where["prescription.type"] = "= 'sejour'";
        $where["prescription.object_class"] = "= 'CSejour'";
        $where["prescription.object_id"] = $ds->prepareIn($sejour_ids);
        $line_ids = $line->loadIds($where, null, null, null, $join);
        // Prescriptions exécutables
        $query = new CRequest();
        $query->addSelect("DISTINCT prescription_id");
        $query->addTable("prescription_line_element");
        $query->addWhereClause("prescription_line_element_id", $ds->prepareIn($line_ids));
        $prescription_ids = $ds->loadColumn($query->makeSelect());
        // Séjours planifiables
        $query = new CRequest();
        $query->addSelect("DISTINCT object_id");
        $query->addTable("prescription");
        $query->addWhereClause("prescription_id", $ds->prepareIn($prescription_ids));
        $sejour_ids = $ds->loadColumn($query->makeSelect());
        $where = array();
        $where["sejour_id"] = $ds->prepareIn($sejour_ids);
        $join = array();
        $join["patients"] = "patients.patient_id = sejour.patient_id";
        $order = "nom, prenom";
        if ($mode == "count") {
            $counts["plannable"] = $sejour->countList($where, null, $join);
        } else {
            $sejours = $sejour->loadList($where, $order, null, null, $join);
        }
    }
}
// Mode count
if ($mode == "count") {
    $smarty = new CSmartyDP();

         $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);
     $request_b->addWhere($where);
     $request_b->addWhere($whereMix);
     if (!$export) {
         $request_b->setLimit("{$start},30");
     }
     $results = array_merge($results, $ds->loadList($request_b->makeSelect()));
 }
 foreach ($results as $_result) {
     $_patient_id = $_result["patient_id"];
     $pat = new CPatient();
     $pat->load($_patient_id);
     // Recherche sur un antécédent
     if (isset($_result["antecedent_id"])) {
         $_atcd = new CAntecedent();
         $_atcd->load($_result["antecedent_id"]);
         $pat->_ref_antecedent = $_atcd;
     } else {
         // On affiche tous les antécédents du patient
         $dossier_medical = $pat->loadRefDossierMedical(false);
         $pat->_refs_antecedents = $dossier_medical->loadRefsAntecedents();
         $pat->_refs_allergies = $dossier_medical->loadRefsAllergies();

<?php

/**
 * Add domain with idex EAI
 *
 * @category EAI
 * @package  Mediboard
 * @author   SARL OpenXtrem <dev@openxtrem.com>
 * @license  GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version  SVN: $Id:$
 * @link     http://www.mediboard.org
 */
CCanDo::checkAdmin();
$domain = new CDomain();
// Récupération des objet_class
$req = new CRequest();
$req->addTable("id_sante400");
$req->addColumn("object_class");
$req->addGroup("object_class");
$ds = CSQLDataSource::get("std");
$idexs_class = CMbArray::pluck($ds->loadList($req->makeSelect()), "object_class");
// Création du template
$smarty = new CSmartyDP();
$smarty->assign("domain", $domain);
$smarty->assign("idexs_class", $idexs_class);
$smarty->display("inc_add_domain_with_idex.tpl");

/* $Id$ */
/**
 * @package Mediboard
 * @subpackage sante400
 * @version $Revision$
 * @author SARL OpenXtrem
 * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html 
 */
CCanDo::checkEdit();
CView::enforceSlave();
// Statistiques sur les id400
$req = new CRequest();
$req->addTable("id_sante400");
$req->addColumn("COUNT(DISTINCT object_id)", "nbObjects");
$req->addColumn("COUNT(id_sante400_id)", "nbID400s");
$ds = CSQLDataSource::get("std");
$statTotal = $ds->loadList($req->makeSelect());
$statTotal = $statTotal[0];
$req->addSelect("object_class");
$req->addGroup("object_class");
$stats = $ds->loadList($req->makeSelect());
// Computes average ID400 count per object
foreach ($stats as &$stat) {
    $stat["average"] = $stat["nbID400s"] / $stat["nbObjects"];
}
$statTotal["average"] = @($statTotal["nbID400s"] / $statTotal["nbObjects"]);
// Création du template
$smarty = new CSmartyDP();
$smarty->assign("stats", $stats);
$smarty->assign("statTotal", $statTotal);
$smarty->display("stats_identifiants.tpl");

        }
        $orderby = "{$order_column} {$order_way}";
    }
}
$request = new CRequest();
$request->addTable($table);
$request->addSelect("*");
$request->setLimit("{$start},{$count}");
if ($orderby) {
    $request->addOrder($orderby);
}
if ($where_column) {
    $where = array($where_column => $ds->prepare("=?", $where_value));
    $request->addWhere($where);
}
$rows = $ds->loadList($request->makeSelect());
$request->setLimit(null);
$request->order = null;
$total = $ds->loadResult($request->makeSelectCount());
$counts = array(10, 50, 100, 200, 500, 1000, 5000);
$smarty = new CSmartyDP();
$smarty->assign("rows", $rows);
$smarty->assign("columns", $columns);
$smarty->assign("tooltip", $tooltip);
$smarty->assign("dsn", $dsn);
$smarty->assign("table", $table);
$smarty->assign("total", $total);
$smarty->assign("start", $start);
$smarty->assign("count", $count);
$smarty->assign("counts", $counts);
$smarty->assign("order_column", $order_column);

}
$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) {
        if ($dry_run) {
            $count++;

 /**
  * MassCount des allergies
  *
  * @param array $dossiers Dossier médicaux
  *
  * @return array
  */
 static function massCountAllergies($dossiers = array())
 {
     $antecedent = new CAntecedent();
     $where["type"] = "= 'alle'";
     $where["annule"] = " ='0'";
     $where["dossier_medical_id"] = CSQLDataSource::prepareIn($dossiers);
     $where["rques"] = 'NOT IN ("' . str_replace('|', '","', CAppUI::conf("soins Other ignore_allergies", CGroups::loadCurrent()->_guid)) . '")';
     $request = new CRequest();
     $request->addColumn("dossier_medical_id");
     $request->addColumn("count(*)", "c");
     $request->addWhere($where);
     $request->addGroup("dossier_medical_id");
     $request->addTable("antecedent");
     return $antecedent->getDS()->loadHashList($request->makeSelect());
 }

    $where["sejour.praticien_id"] = " = '{$prat_id}'";
}
$month_min = CMbDT::dateTime(null, $month_min);
$nextmonth = CMbDT::dateTime(null, $nextmonth);
// Liste des sorties par jour
$request = new CRequest();
$request->addSelect(array("DATE_FORMAT(sejour.sortie_reelle, '%Y-%m-%d') AS 'date'", "COUNT(sejour.sejour_id) AS 'num'"));
$request->addTable("sejour");
$where["sejour.sortie_reelle"] = "BETWEEN '{$month_min}' AND '{$nextmonth}'";
$where["sejour.group_id"] = " = '{$group->_id}'";
$where["sejour.annule"] = " = '0'";
$request->addWhere($where);
$request->addLJoin($leftjoin);
$request->addGroup("date");
$request->addOrder("date");
foreach ($ds->loadHashList($request->makeSelect()) as $day => $_sortie) {
    $days[$day]["sortie"] = $_sortie;
}
// Liste des sorties dont le dossier n'a pas été reçu
$leftjoin["traitement_dossier"] = "traitement_dossier.sejour_id = sejour.sejour_id";
$where["traitement_dossier.traitement"] = " IS NOT NULL";
$request->addWhere($where);
$request->addLJoin($leftjoin);
foreach ($ds->loadHashList($request->makeSelect()) as $day => $_traitement) {
    $days[$day]["traitement"] = $_traitement;
}
// Liste des sorties dont le dossier est traité
unset($where['traitement_dossier.traitement']);
$request->where = array();
$where["traitement_dossier.validate"] = " IS NOT NULL";
$request->addWhere($where);