PHP CRequest::addTable方法代码示例

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

 $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");
     }
     $request_b->addLJoin($ljoin);

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

$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;
        $query1->addWhereClause("evenement_ssr.annule", "= '1'");
        foreach ($ds->loadList($query1->makeSelect()) as $_row) {
            $stats->addTotal($_row["user_id"], $_row["refdate"], $_row["total"], "annules");
        }

$owner = CView::post("owner", "str");
CView::checkin();
$modeles_ids = explode("-", $modeles_ids);
CMbArray::removeValue(array(), $modeles_ids);
if (!count($modeles_ids)) {
    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);

 /**
  * @see parent::check()
  */
 function check()
 {
     $msg = null;
     $ds = $this->_spec->ds;
     if (!$this->perm_object_id) {
         $where = array();
         $where["user_id"] = $ds->prepare("= %", $this->user_id);
         $where["object_class"] = $ds->prepare("= %", $this->object_class);
         if ($this->object_id) {
             $where["object_id"] = $ds->prepare("= %", $this->object_id);
         } else {
             $where["object_id"] = "IS NULL";
         }
         $query = new CRequest();
         $query->addSelect("count(perm_object_id)");
         $query->addTable("perm_object");
         $query->addWhere($where);
         $nb_result = $ds->loadResult($query->makeSelect());
         if ($nb_result) {
             $msg .= "Une permission sur cet objet existe déjà.<br />";
         }
     }
     return $msg . parent::check();
 }

        $where["sejour.type"] = " = '{$type}'";
    }
} else {
    $where["sejour.type"] = "!= 'urg' AND `sejour`.`type` != 'seances'";
}
// 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']);

$where_column = CValue::get("where_column");
$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();

 /**
  * Vérifie l'unicité d'une aide à la saisie
  * 
  * @return string
  */
 function check()
 {
     $msg = "";
     $ds = $this->_spec->ds;
     $where = array();
     if ($this->user_id) {
         $where["user_id"] = $ds->prepare("= %", $this->user_id);
     } else {
         if ($this->function_id) {
             $where["function_id"] = $ds->prepare("= %", $this->function_id);
         } else {
             $where["group_id"] = $ds->prepare("= %", $this->group_id);
         }
     }
     $where["class"] = $ds->prepare("= %", $this->class);
     $where["field"] = $ds->prepare("= %", $this->field);
     $where["depend_value_1"] = $ds->prepare("= %", $this->depend_value_1);
     $where["depend_value_2"] = $ds->prepare("= %", $this->depend_value_2);
     $where["text"] = $ds->prepare("= %", $this->text);
     $where["aide_id"] = $ds->prepare("!= %", $this->aide_id);
     $sql = new CRequest();
     $sql->addSelect("count(aide_id)");
     $sql->addTable("aide_saisie");
     $sql->addWhere($where);
     $nb_result = $ds->loadResult($sql->makeSelect());
     if ($nb_result) {
         $msg .= "Cette aide existe déjà<br />";
     }
     return $msg . parent::check();
 }

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