PHP CRequest::addLJoinClause方法代码示例

<?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");

$filter->_ccam_libelle = CValue::get("_ccam_libelle", "1");
$filter->_coordonnees = CValue::get("_coordonnees", 0);
$filter->_notes = CValue::get("_notes", 0);
$filter->_nb_days = CValue::get("_nb_days", 0);
$filter->_by_date = CValue::get("_by_date", 0);
if ($filter->_nb_days) {
    $filter->_date_max = CMbDT::date("+{$filter->_nb_days} days", CMbDT::date($filter->_date_min)) . " 21:00:00";
}
$filter->_service = explode(",", $filter->_service);
$filter->praticien_id = explode(",", $filter->praticien_id);
CMbArray::removeValue(0, $filter->praticien_id);
CMbArray::removeValue(0, $filter->_service);
$total = 0;
$sejours = new CSejour();
$sejourReq = new CRequest();
$sejourReq->addLJoinClause("patients", "patients.patient_id = sejour.patient_id");
$sejourReq->addLJoinClause("users", "users.user_id = sejour.praticien_id");
$sejourReq->addWhereClause("sejour.{$filter->_horodatage}", "BETWEEN '{$filter->_date_min}' AND '{$filter->_date_max}'");
$sejourReq->addWhereClause("sejour.group_id", "= '{$group->_id}'");
$sejourReq->addWhereClause("sejour.annule", "= '0'");
// On supprime les sejours d'urgence
$sejourReq->addWhereClause("sejour.type", "!= 'urg'");
// Clause de filtre par spécialité / chir
if ($filter->_specialite or $filter->praticien_id) {
    $speChirs = new CMediusers();
    $speChirs = $speChirs->loadList(array("function_id" => "= '{$filter->_specialite}'"));
    if (count($filter->praticien_id)) {
        $sejourReq->addWhereClause("sejour.praticien_id", CSQLDataSource::prepareIn($filter->praticien_id));
    } else {
        $sejourReq->addWhereClause("sejour.praticien_id", CSQLDataSource::prepareIn(array_keys($speChirs)));
    }

$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");
        }
        // Ni réalisés ni annulés

$query = new CRequest();
$query->addColumn("GROUP_CONCAT( plageop_id  SEPARATOR '-')", "plage_ids");
$query->addColumn("COUNT( plageop_id )", "plage_count");
$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;
        }

 * @author     SARL OpenXtrem <dev@openxtrem.com>
 * @license    GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version    $Revision$
 */
CCanDo::checkEdit();
$date = CValue::getOrSession("date", CMbDT::date());
$date_min = CMbDT::date("last monday", CMbDT::date("+1 day", $date));
$date_max = CMbDT::date("+7 DAY", $date_min);
// Comptage des événements hors-séjours
$group = "sejour.sejour_id";
$query = new CRequest();
$query->addTable("evenement_ssr");
$query->addColumn("COUNT(evenement_ssr_id)", "evenements_count");
$query->addWhereClause("debut", "BETWEEN '{$date_min}' AND '{$date_max}'");
$query->addWhereClause("type", "= 'ssr'");
$query->addLJoinClause("sejour", "sejour.sejour_id = evenement_ssr.sejour_id");
$query->addColumn("sejour.sejour_id");
$query->addWhereClause(null, "debut NOT BETWEEN DATE(entree) AND DATE(ADDDATE(sortie, 1))");
$query->addWhereClause("sejour.annule", "!= '1'");
$query->addGroup("sejour.sejour_id");
$sejour = new CSejour();
$ds = $sejour->_spec->ds;
$evenements_counts = array();
foreach ($ds->loadList($query->makeSelect()) as $row) {
    $evenements_counts[$row["sejour_id"]] = $row["evenements_count"];
}
// Chargement des séjours concernés
/** @var CSejour[] $sejours */
$sejours = $sejour->loadAll(array_keys($evenements_counts));
$sejours_count = count($sejours);
// Détails sur les séjours concernés

/**
 * $Id$
 *  
 * @category dPurgences
 * @package  Mediboard
 * @author   SARL OpenXtrem <dev@openxtrem.com>
 * @license  GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version  $Revision$
 * @link     http://www.mediboard.org
 */
$nb_month = CValue::get("month_maintenance", "1");
$date = CMbDT::dateTime("-" . $nb_month . "MONTH");
$crequest = new CRequest();
$crequest->addSelect(array("`sejour`.`sejour_id`", "`sejour`.`sortie`"));
$crequest->addTable("`sejour`");
$crequest->addLJoinClause("rpu", "`sejour`.`sejour_id` = `rpu`.`sejour_id`");
$crequest->addWhereClause("sejour.entree", "> '{$date}'");
$crequest->addGroup(array("`sejour_id`"));
$crequest->addHaving(array("COUNT(`sejour`.`sejour_id`)>1"));
$sejour = new CSejour();
$ds = $sejour->getDS();
$list_id_sejour = $ds->loadList($crequest->makeSelect());
usort($list_id_sejour, function ($a, $b) {
    return -strnatcmp($a["sortie"], $b["sortie"]);
});
$list_sejour = array();
$patients = array();
$guesses = array();
foreach ($list_id_sejour as $_id_sejour) {
    $sejour = new CSejour();
    $sejour->load($_id_sejour["sejour_id"]);

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

 * @author   SARL OpenXtrem <dev@openxtrem.com>
 * @license  GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version  $Revision$
 * @link     http://www.mediboard.org
 */
CCanDo::checkAdmin();
global $dPconfig;
$dPconfig["sa"]["trigger_sejour"] = "facture";
$limit = CValue::get("limit", 10);
$day = CValue::get("day", 3);
$sejour = new CSejour();
$ds = $sejour->getDS();
$request = new CRequest();
$request->addSelect("sejour.sejour_id");
$request->addTable("sejour");
$request->addLJoinClause("rpu", "rpu.sejour_id = sejour.sejour_id");
$request->addWhereClause("annule", "!='1'");
$request->addWhereClause("facture", "!='1'");
$request->addWhereClause("sortie_reelle", "BETWEEN '" . CMbDT::dateTime("-{$day}DAY") . "' AND '" . CMbDT::dateTime() . "'");
$request->addWhereClause("rpu.sejour_id", "IS NOT NULL");
$request->addGroup("sejour.sejour_id");
$request->addHaving("count(*) = 1");
$request->setLimit($limit);
$list_sejour = $ds->loadList($request->makeSelect());
foreach ($list_sejour as $_sejour_id) {
    $sejour = new CSejour();
    $sejour->load($_sejour_id["sejour_id"]);
    if (!($nda = $sejour->getTagNDA())) {
        continue;
    }
    $rpu = $sejour->loadRefRPU();

/**
 * 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     Identifiant du praticien
 * @param int    $salle_id    Identifiant de la salle
 * @param int    $bloc_id     Identifiant du bloc
 * @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 graphOpAnnulees($date_min = null, $date_max = null, $prat_id = null, $salle_id = null, $bloc_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");
    $prat = new CMediusers();
    $prat->load($prat_id);
    $serie_total = array('label' => 'Total', 'data' => array(), 'markers' => array('show' => true), 'bars' => array('show' => false));
    $salles = CSalle::getSallesStats($salle_id, $bloc_id);
    $query = new CRequest();
    $query->addColumn("salle_id");
    $query->addColumn("DATE_FORMAT(date_operation, '%Y-%m')", "mois");
    $query->addColumn("COUNT(DISTINCT(operations.operation_id))", "total");
    $query->addTable("operations");
    $query->addLJoinClause("operation_workflow", "operation_workflow.operation_id = operations.operation_id");
    $query->addWhere("DATE(date_cancellation) = DATE(date_operation)");
    $query->addWhereClause("date_operation", "BETWEEN '{$date_min}' AND '{$date_max}'");
    $query->addWhereClause("salle_id", CSQLDataSource::prepareIn(array_keys($salles)));
    $query->addGroup("mois, salle_id");
    $query->addOrder("mois, salle_id");
    // Filtre sur hors plage
    if (!$hors_plage) {
        $query->addWhereClause("plageop_id", "IS NOT NULL");
    }
    // Filtre sur le praticien
    if ($prat_id) {
        $query->addWhereClause("operations.chir_id", "= '{$prat_id}'");
    }
    // Filtre sur les codes CCAM
    if ($code_ccam) {
        $query->addWhereClause("operations.codes_ccam", "LIKE '%{$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}'");
    }
    // Query result
    $ds = CSQLDataSource::get("std");
    $tree = $ds->loadTree($query->makeSelect());
    // Build horizontal ticks
    $months = array();
    $ticks = array();
    for ($_date = $date_min; $_date < $date_max; $_date = CMbDT::date("+1 MONTH", $_date)) {
        $count_ticks = count($ticks);
        $ticks[] = array($count_ticks, CMbDT::format($_date, "%m/%Y"));
        $months[CMbDT::format($_date, "%Y-%m")] = $count_ticks;
        $serie_total['data'][] = array(count($serie_total['data']), 0);
    }
    // Build series
    $series = array();
    $total = 0;
    foreach ($salles as $_salle) {
        $_serie = array("label" => utf8_encode($bloc_id ? $_salle->nom : $_salle->_view));
        $data = array();
        foreach ($months as $_month => $_tick) {
            $value = isset($tree[$_salle->_id][$_month]) ? $tree[$_salle->_id][$_month] : 0;
            $data[] = array($_tick, $value);
            $serie_total["data"][$_tick][1] += $value;
            $total += $value;
        }
        $_serie["data"] = $data;
        $series[] = $_serie;
    }
    $series[] = $serie_total;
    // Set up the title for the graph
    $title = "Interventions annulées le jour même";
    $subtitle = "{$total} interventions";
    if ($prat_id) {
        $subtitle .= " - Dr {$prat->_view}";
    }
    if ($salle_id) {
        $salle = reset($salles);
        $subtitle .= " - {$salle->_view}";
    }
    if ($code_ccam) {
        $subtitle .= " - CCAM : {$code_ccam}";
    }
    if ($type_sejour) {
//.........这里部分代码省略.........