PHP CRequest::addLJoin方法代码示例

 /**
  * Get all the fields' names
  *
  * @param bool $name_as_key Put the names in the keys
  * @param bool $all_groups  Load all groups
  *
  * @return string[] List of field names
  */
 function getFieldNames($name_as_key = true, $all_groups = true)
 {
     $ds = $this->_spec->ds;
     $req = new CRequest();
     $req->addTable($this->_spec->table);
     $req->addSelect("ex_class_field.name, ex_class_field_translation.std AS locale");
     $ljoin = array("ex_class_field_translation" => "ex_class_field_translation.ex_class_field_id = ex_class_field.ex_class_field_id");
     $req->addLJoin($ljoin);
     $this->completeField("ex_group_id");
     $where = array();
     if ($all_groups) {
         $ex_group = $this->loadRefExGroup();
         $where_ids = array("ex_class_id" => $ds->prepare("= %", $ex_group->ex_class_id));
         $ids = $ex_group->loadIds($where_ids);
         $where["ex_group_id"] = $ds->prepareIn($ids);
     } else {
         $where["ex_group_id"] = $ds->prepare("= %", $this->ex_group_id);
     }
     $req->addWhere($where);
     $results = $ds->loadList($req->makeSelect());
     if ($name_as_key) {
         return array_combine(CMbArray::pluck($results, "name"), CMbArray::pluck($results, "locale"));
     }
     return array_combine(CMbArray::pluck($results, "locale"), CMbArray::pluck($results, "name"));
 }

} 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']);
unset($where['sejour.entree_preparee']);
$request->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));
 }

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

         $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);
     $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();

        $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();
    $_consult->loadRefPatient();
    $period = preg_replace('/(\\d{4})-(\\d{2})-(\\d{2})/', $php_date_format, $_consult->_ref_patient->naissance);

 * $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));
        if (!$article->loadObject($where)) {
            $where = array("cip" => $article->_spec->ds->prepare("=%", $_reference->loadRefProduct()->code));

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

 /**
  * Inits locale cache
  *
  * @return void
  */
 static function initLocales()
 {
     if (self::$_locales_ready) {
         return;
     }
     $lang = CAppUI::pref("LOCALE");
     $_all_locales = DSHM::get("exclass-locales-{$lang}");
     if (!$_all_locales) {
         $undefined = CAppUI::tr("Undefined");
         $ds = CSQLDataSource::get("std");
         $_all_locales = array();
         $request = new CRequest();
         $request->addTable("ex_class_field_translation");
         $request->addWhere(array("lang" => "= '{$lang}'"));
         $request->addLJoin(array("ex_class_field" => "ex_class_field.ex_class_field_id = ex_class_field_translation.ex_class_field_id", "ex_concept" => "ex_concept.ex_concept_id = ex_class_field.concept_id", "ex_class_field_group" => "ex_class_field_group.ex_class_field_group_id = ex_class_field.ex_group_id"));
         $request->addSelect(array("ex_class_field_translation.std", "IF(ex_class_field_translation.desc  IS NOT NULL, ex_class_field_translation.desc,  ex_class_field_translation.std) AS `desc`", "IF(ex_class_field_translation.court IS NOT NULL, ex_class_field_translation.court, ex_class_field_translation.std) AS `court`", "ex_class_field.ex_class_field_id AS field_id", "ex_class_field.name", "ex_class_field.prop", "ex_class_field.concept_id", "ex_class_field_group.ex_class_id", "ex_concept.ex_list_id"));
         $list = $ds->loadList($request->makeSelect());
         // Chargement des list_items par concept, field ou list
         $request = new CRequest();
         $request->addTable("ex_list_item");
         $request->addSelect(array("ex_list_item_id", "list_id", "concept_id", "field_id", "name"));
         $list_items = $ds->loadList($request->makeSelect());
         // Chargement en une seule requete de toutes les traductions de champs
         $enum_list_cache = array("list" => array(), "concept" => array(), "field" => array());
         $mapper = array("list_id" => "list", "concept_id" => "concept", "field_id" => "field");
         foreach ($list_items as $_item) {
             $_item_id = $_item["ex_list_item_id"];
             $_item_name = $_item["name"];
             foreach ($mapper as $_field_name => $_to) {
                 if ($_field_value = $_item[$_field_name]) {
                     $enum_list_cache[$_to][$_field_value][$_item_id] = $_item_name;
                 }
             }
         }
         foreach ($list as $_item) {
             $_locales = array();
             $key = "-{$_item['name']}";
             $_locales[$key] = $_item["std"];
             if ($_item["desc"]) {
                 $_locales["{$key}-desc"] = $_item["desc"];
             }
             if ($_item["court"]) {
                 $_locales["{$key}-court"] = $_item["court"];
             }
             $_ex_class_id = $_item['ex_class_id'];
             $_prefix = "CExObject_{$_ex_class_id}";
             $prop = $_item["prop"];
             if (strpos($prop, "enum") === false && strpos($prop, "set") === false) {
                 if (!isset($_all_locales[$_prefix])) {
                     $_all_locales[$_prefix] = array();
                 }
                 $_all_locales[$_prefix] = array_merge($_all_locales[$_prefix], $_locales);
                 continue;
             }
             $key = ".{$_item['name']}";
             $_locales["{$key}."] = $undefined;
             $concept_id = $_item["concept_id"];
             $ex_list_id = $_item["ex_list_id"];
             $field_id = $_item["field_id"];
             $enum_list = array();
             if ($concept_id) {
                 if ($ex_list_id) {
                     if (isset($enum_list_cache["list"][$ex_list_id])) {
                         $enum_list = $enum_list_cache["list"][$ex_list_id];
                     }
                 } else {
                     if (isset($enum_list_cache["concept"][$concept_id])) {
                         $enum_list = $enum_list_cache["concept"][$concept_id];
                     }
                 }
             } else {
                 if (isset($enum_list_cache["field"][$field_id])) {
                     $enum_list = $enum_list_cache["field"][$field_id];
                 }
             }
             foreach ($enum_list as $_value => $_locale) {
                 $_locales["{$key}.{$_value}"] = $_locale;
             }
             if (!isset($_all_locales[$_prefix])) {
                 $_all_locales[$_prefix] = array();
             }
             $_all_locales[$_prefix] = array_merge($_all_locales[$_prefix], $_locales);
         }
         DSHM::put("exclass-locales-{$lang}", $_all_locales, true);
     }
     foreach ($_all_locales as $_prefix => $_locales) {
         CAppUI::addLocales($_prefix, $_locales);
     }
     self::$_locales_ready = true;
 }

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

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

/**
 * 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();
//.........这里部分代码省略.........