PHP CRequest::addSelect方法代码示例

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

        $join["element_prescription"] = "element_prescription.element_prescription_id = prescription_line_element.element_prescription_id";
        $where["element_prescription.category_prescription_id"] = $ds->prepareIn($category_ids);
        $join["prescription"] = "prescription.prescription_id = prescription_line_element.prescription_id";
        $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);
        }
    }
}

 }
 $results = $ds->loadList($request->makeSelect());
 // 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");
     }

 /**
  * Computes the weighted average price (PMP)
  *
  * @param string $since    [optional]
  * @param string $date_max [optional]
  * @param bool   $ttc      Include taxes
  *
  * @return float
  */
 function getWAP($since = "-1 MONTH", $date_max = null, $ttc = false)
 {
     $qty = $this->getSupply($since, $date_max);
     if (!$qty) {
         return null;
     }
     $where = array("product.product_id" => "= '{$this->_id}'", "product_order_item_reception.date > '" . CMbDT::date($since) . "'");
     if ($date_max) {
         $where[] = "product_order_item_reception.date <= '" . CMbDT::date($date_max) . "'";
     }
     $ljoin = array("product_order_item" => "product_order_item.order_item_id = product_order_item_reception.order_item_id", "product_reference" => "product_reference.reference_id = product_order_item.reference_id", "product" => "product.product_id = product_reference.product_id");
     $sql = new CRequest();
     $sql->addTable("product_order_item_reception");
     $select = "SUM(product_order_item_reception.quantity * product_order_item.unit_price)";
     if ($ttc) {
         $ttc_select = "product_order_item.unit_price + (product_order_item.unit_price * (product_order_item.tva / 100))";
         $select = "SUM(product_order_item_reception.quantity * ({$ttc_select}))";
     }
     $sql->addSelect($select);
     $sql->addLJoin($ljoin);
     $sql->addWhere($where);
     $total = $this->_spec->ds->loadResult($sql->makeSelect());
     return $total / $qty;
 }

 /**
  * Get the configuration values of an object, without inheritance
  *
  * @param string  $object_class Object class
  * @param integer $object_id    Object ID
  * @param array   $config_keys  The keys of the values to get
  *
  * @return array The configuration values
  */
 protected static function getSelfConfig($object_class = null, $object_id = null, $config_keys = null)
 {
     static $cache_data = null;
     static $cache = array();
     $key = "{$object_class}/{$object_id}/" . ($config_keys ? implode("-", $config_keys) : "");
     if (isset($cache[$key])) {
         return $cache[$key];
     }
     if ($cache_data === null) {
         $spec = self::_getSpec();
         $request = new CRequest();
         $request->addTable($spec->table);
         $request->addSelect(array("feature", "value", "object_class", "object_id"));
         $cache_data = $spec->ds->loadList($request->makeSelect());
     }
     if ($object_class && $object_id) {
         $data = array_filter($cache_data, function ($v) use($object_class, $object_id) {
             return $v["object_class"] === $object_class && $v["object_id"] === $object_id;
         });
     } else {
         $data = array_filter($cache_data, function ($v) {
             return $v["object_class"] === null && $v["object_id"] === null;
         });
     }
     if ($config_keys) {
         $data = array_filter($data, function ($v) use($config_keys) {
             return in_array($v["feature"], $config_keys);
         });
     }
     $final_data = array();
     foreach ($data as $_data) {
         $final_data[$_data["feature"]] = $_data["value"];
     }
     return $cache[$key] = $final_data;
 }

 /**
  * Get all number patient by a state and the filter
  *
  * @param String $date_min Date minimum
  * @param String $date_max Date maximum
  *
  * @return array
  */
 static function getAllNumberPatient($date_min = null, $date_max = null)
 {
     $patients_count = array();
     $leftjoin = null;
     $where = array();
     if ($date_min) {
         $where["entree"] = ">= '{$date_min}'";
         $leftjoin["sejour"] = "patients.patient_id = sejour.patient_id";
     }
     if ($date_max) {
         $where["entree"] = "<= '{$date_max}'";
         $leftjoin["sejour"] = "patients.patient_id = sejour.patient_id";
     }
     $ds = CSQLDataSource::get("std");
     $request = new CRequest();
     $request->addSelect("`status`, COUNT(DISTINCT(`patients`.`patient_id`)) as `total`");
     $request->addTable("patients");
     $request->addLJoin($leftjoin);
     $request->addWhere($where);
     $request->addGroup("`status`");
     $result = $ds->loadList($request->makeSelect());
     $state_count = array();
     foreach ($result as $_result) {
         $state_count[$_result["status"]] = $_result["total"];
     }
     foreach (self::$list_state as $_state) {
         $patients_count[CMbString::lower($_state)] = CMbArray::get($state_count, $_state, 0);
         if ($_state == "CACH") {
             $where = array("vip" => "= '1'", "status" => "!= 'VALI'");
             $patient = new CPatient();
             $patients_count[CMbString::lower($_state)] = $patient->countList($where, null, $leftjoin);
         }
         if ($_state == "DPOT") {
             $patient_link = new CPatientLink();
             $patients_count[CMbString::lower($_state)] = $patient_link->countList($where, null, array("sejour" => "patient_link.patient_id1 = sejour.patient_id"));
         }
     }
     return $patients_count;
 }

/* $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");

    CAppUI::stepMessage("Aucun modèle à exporter");
    CApp::rip();
}
$doc = new CMbXMLDocument(null);
$root = $doc->createElement("modeles");
$doc->appendChild($root);
$where = array("compte_rendu_id" => CSQLDataSource::prepareIn($modeles_ids));
// Récupération des header_id, footer_id, preface_id et ending_id
$ds = CSQLDataSource::get("std");
$request = new CRequest();
$request->addTable("compte_rendu");
$request->addWhere($where);
$components_ids = array();
foreach (array("header_id", "footer_id", "preface_id", "ending_id") as $_component) {
    $request->select = array();
    $request->addSelect($_component);
    $components_ids = array_merge($components_ids, $ds->loadColumn($request->makeSelect()));
}
$modeles_ids = array_unique(array_merge($components_ids, $modeles_ids));
CMbArray::removeValue("", $modeles_ids);
foreach ($modeles_ids as $_modele_id) {
    $modele = CApp::fetch("dPcompteRendu", "ajax_export_modele", array("modele_id" => $_modele_id));
    $doc_modele = new CMbXMLDocument(null);
    @$doc_modele->loadXML($modele);
    // Importation du noeud CPrescription
    $modele_importe = $doc->importNode($doc_modele->firstChild, true);
    // Ajout de ce noeud comme fils de protocoles
    $doc->documentElement->appendChild($modele_importe);
}
$filename = 'Modèles ' . ($owner ? " - {$owner}" : '') . ($object_class ? " - " . CAppUI::tr($object_class) : '') . '.xml';
$content = $doc->saveXML();

$spec = $correspondant->_spec;
// Don't group by key... of course
CMbArray::removeValue($spec->key, $fields);
// Don't group by date
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);

$where_value = CValue::get("where_value");
$ds = CSQLDataSource::get($dsn);
$columns = CImportTools::getColumnsInfo($ds, $table);
$orderby = "";
if ($order_column) {
    $order_column = preg_replace('/[^-_\\w]/', "", $order_column);
    if (in_array($order_column, array_keys($columns))) {
        if (!in_array($order_way, array("ASC", "DESC"))) {
            $order_way = "ASC";
        }
        $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);

        break;
    case 'month':
        $mysql_date_format = '%Y-%m';
        $php_date_format = '\\2';
        break;
    case 'year':
        $mysql_date_format = '%Y';
        $php_date_format = '\\1';
        break;
    default:
        CAppUI::stepAjax('common-error-Invalid parameter', UI_MSG_ERROR);
}
$order_by[] = "DATE_FORMAT(`{$patient->_spec->table}`.`naissance`, '{$mysql_date_format}')";
$order_by[] = "`{$patient->_spec->table}`.`nom`, `{$patient->_spec->table}`.`prenom`";
$request = new CRequest();
$request->addSelect('`cs`.*');
$request->addTable("{$consult->_spec->table} AS `cs`");
$request->addLJoin($ljoin);
$request->addWhere($where);
$request->addOrder($order_by);
$consultations = $consult->loadQueryList($request->makeSelect());
CStoredObject::massLoadFwdRef($consultations, 'plageconsult_id');
$patients = CStoredObject::massLoadFwdRef($consultations, 'patient_id');
if ($_print_ipp) {
    CPatient::massLoadIPP($patients);
}
$sorted_consults = array();
/** @var CConsultation $_consult */
foreach ($consultations as $_consult) {
    // Implicit loadRefPlageConsult()
    $_consult->loadRefPraticien();