PHP CSQLDataSource类代码示例

 /**
  * Set the hash for the user mails
  *
  * @return bool
  */
 protected function setUserMailHash()
 {
     $ds = CSQLDataSource::get("std");
     $mails = $ds->loadList("SELECT m.user_mail_id, m.account_class, m.account_id, m.from, m.to, m.subject, c.content FROM user_mail as m, content_html as c WHERE m.account_class IS NOT NULL AND m.account_id IS NOT NULL AND m.text_html_id = c.content_id ORDER BY m.user_mail_id DESC;");
     if (count($mails)) {
         $values = array();
         foreach ($mails as $_mail) {
             $data = "==FROM==\n" . $_mail['from'] . "\n==TO==\n" . $_mail['to'] . "\n==SUBJECT==\n" . $_mail['subject'] . "\n==CONTENT==\n" . $_mail['content'];
             $hash = CMbSecurity::hash(CMbSecurity::SHA256, $data);
             $values[] = '(' . $_mail['user_mail_id'] . ', ' . $_mail['account_id'] . ', \'' . $_mail['account_class'] . "', '{$hash}')";
         }
         $mails = $ds->loadList("SELECT m.user_mail_id, m.account_class, m.account_id, m.from, m.to, m.subject, c.content FROM user_mail AS m, content_any AS c WHERE m.account_class IS NOT NULL AND m.account_id IS NOT NULL AND m.text_html_id IS NULL AND m.text_plain_id = c.content_id ORDER BY m.user_mail_id DESC;");
         foreach ($mails as $_mail) {
             $data = "==FROM==\n" . $_mail['from'] . "\n==TO==\n" . $_mail['to'] . "\n==SUBJECT==\n" . $_mail['subject'] . "\n==CONTENT==\n" . $_mail['content'];
             $hash = CMbSecurity::hash(CMbSecurity::SHA256, $data);
             $values[] = '(' . $_mail['user_mail_id'] . ', ' . $_mail['account_id'] . ', \'' . $_mail['account_class'] . "', '{$hash}')";
         }
         $query = "INSERT INTO `user_mail` (`user_mail_id`, `account_id`, `account_class`, `hash`) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE `hash` = VALUES(`hash`);";
         $ds->query($query);
         if ($msg = $ds->error()) {
             CAppUI::stepAjax($msg, UI_MSG_WARNING);
             return false;
         }
     }
     return true;
 }

 /**
  * @see parent::open()
  */
 function open()
 {
     if (self::$ds = CSQLDataSource::get("std")) {
         return true;
     }
     return false;
 }

 /**
  * Change prat usernames to prat ids
  *
  * @return bool
  */
 protected function swapPratIds()
 {
     $ds = CSQLDataSource::get("std");
     CApp::setTimeLimit(1800);
     $user = new CUser();
     // Changement des chirurgiens
     $query = "SELECT id_chir\r\n        FROM plagesop\r\n        GROUP BY id_chir";
     $listPlages = $ds->loadList($query);
     foreach ($listPlages as $plage) {
         $where["user_username"] = "= '" . $plage["id_chir"] . "'";
         $user->loadObject($where);
         if ($user->user_id) {
             $query = "UPDATE plagesop\r\n            SET chir_id = '{$user->user_id}'\r\n            WHERE id_chir = '{$user->user_username}'";
             $ds->exec($query);
             $ds->error();
         }
     }
     //Changement des anesthésistes
     $query = "SELECT id_anesth\r\n         FROM plagesop\r\n         GROUP BY id_anesth";
     $listPlages = $ds->loadList($query);
     foreach ($listPlages as $plage) {
         $where["user_username"] = "= '" . $plage["id_anesth"] . "'";
         $user->loadObject($where);
         if ($user->user_id) {
             $query = "UPDATE plagesop\r\n            SET anesth_id = '{$user->user_id}'\r\n            WHERE id_anesth = '{$user->user_username}'";
             $ds->exec($query);
             $ds->error();
         }
     }
     return true;
 }

 /**
  * Get full database structure
  *
  * @param string $dsn   Datasource name
  * @param bool   $count Count each table entries
  *
  * @return mixed
  * @throws Exception
  */
 static function getDatabaseStructure($dsn, $count = false)
 {
     $databases = CImportTools::getAllDatabaseInfo();
     if (!isset($databases[$dsn])) {
         throw new Exception("DSN not found : {$dsn}");
     }
     $db_info = $databases[$dsn];
     $ds = CSQLDataSource::get($dsn);
     // Description file
     $description = new DOMDocument();
     $description->load($db_info["description_file"]);
     $description->_xpath = new DOMXPath($description);
     $db_info["description"] = $description;
     // Tables
     $table_names = $ds->loadTables();
     $tables = array();
     foreach ($table_names as $_table_name) {
         $_table_info = CImportTools::getTableInfo($ds, $_table_name);
         if ($count) {
             $_table_info["count"] = $ds->loadResult("SELECT COUNT(*) FROM {$_table_name}");
         }
         $tables[$_table_name] = $_table_info;
     }
     $db_info["tables"] = $tables;
     return $db_info;
 }

 /**
  * Tells if the "user_authentication" table exists
  *
  * @return bool
  */
 static function authReady()
 {
     static $ready = null;
     if ($ready === null) {
         $ds = CSQLDataSource::get("std");
         $ready = $ds->loadTable("user_authentication") != null;
     }
     return $ready;
 }

/**
 * Récupération des statistiques du nombre de consultations par mois
 * selon plusieurs filtres
 *
 * @param string $debut   Date de début
 * @param string $fin     Date de fin
 * @param int    $prat_id Identifiant du praticien
 *
 * @return array
 */
function graphConsultations($debut = null, $fin = null, $prat_id = 0)
{
    if (!$debut) {
        $debut = CMbDT::date("-1 YEAR");
    }
    if (!$fin) {
        $fin = CMbDT::date();
    }
    $rectif = CMbDT::transform("+0 DAY", $debut, "%d") - 1;
    $debutact = CMbDT::date("-{$rectif} DAYS", $debut);
    $rectif = CMbDT::transform("+0 DAY", $fin, "%d") - 1;
    $finact = CMbDT::date("-{$rectif} DAYS", $fin);
    $finact = CMbDT::date("+ 1 MONTH", $finact);
    $finact = CMbDT::date("-1 DAY", $finact);
    $pratSel = new CMediusers();
    $pratSel->load($prat_id);
    $ticks = array();
    $serie_total = array('label' => 'Total', 'data' => array(), 'markers' => array('show' => true), 'bars' => array('show' => false));
    for ($i = $debut; $i <= $fin; $i = CMbDT::date("+1 MONTH", $i)) {
        $ticks[] = array(count($ticks), CMbDT::transform("+0 DAY", $i, "%m/%Y"));
        $serie_total['data'][] = array(count($serie_total['data']), 0);
    }
    $ds = CSQLDataSource::get("std");
    $total = 0;
    $series = array();
    $query = "SELECT COUNT(consultation.consultation_id) AS total,\r\n    DATE_FORMAT(plageconsult.date, '%m/%Y') AS mois,\r\n    DATE_FORMAT(plageconsult.date, '%Y%m') AS orderitem\r\n    FROM consultation\r\n    INNER JOIN plageconsult\r\n    ON consultation.plageconsult_id = plageconsult.plageconsult_id\r\n    INNER JOIN users_mediboard\r\n    ON plageconsult.chir_id = users_mediboard.user_id\r\n    WHERE plageconsult.date BETWEEN '{$debutact}' AND '{$finact}'\r\n    AND consultation.annule = '0'";
    if ($prat_id) {
        $query .= "\nAND plageconsult.chir_id = '{$prat_id}'";
    }
    $query .= "\nGROUP BY mois ORDER BY orderitem";
    $serie = array('data' => array());
    $result = $ds->loadlist($query);
    foreach ($ticks as $i => $tick) {
        $f = true;
        foreach ($result as $r) {
            if ($tick[1] == $r["mois"]) {
                $serie["data"][] = array($i, $r["total"]);
                $serie_total["data"][$i][1] += $r["total"];
                $total += $r["total"];
                $f = false;
                break;
            }
        }
        if ($f) {
            $serie["data"][] = array(count($serie["data"]), 0);
        }
    }
    $series[] = $serie;
    // Set up the title for the graph
    $title = "Nombre de consultations";
    $subtitle = "- {$total} consultations -";
    if ($prat_id) {
        $subtitle .= " Dr {$pratSel->_view} -";
    }
    $options = CFlotrGraph::merge("bars", array('title' => utf8_encode($title), 'subtitle' => utf8_encode($subtitle), 'xaxis' => array('ticks' => $ticks), 'bars' => array('stacked' => true, 'barWidth' => 0.8)));
    return array('series' => $series, 'options' => $options);
}

 /**
  * Check HL7v2 tables presence
  *
  * @return bool
  */
 protected function checkHL7v2Tables()
 {
     $dshl7 = CSQLDataSource::get("hl7v2", true);
     if (!$dshl7 || !$dshl7->loadTable("table_entry")) {
         CAppUI::setMsg("CHL7v2Tables-missing", UI_MSG_ERROR);
         return false;
     }
     return true;
 }

 function CDoRepasAddEdit()
 {
     global $m;
     $this->CDoObjectAddEdit("CRepas", "repas_id");
     $this->redirect = "m={$m}&tab=vw_planning_repas";
     // Synchronisation Offline
     $this->synchro = CValue::post("_syncroOffline", false);
     $this->synchroConfirm = CValue::post("_synchroConfirm", null);
     $this->synchroDatetime = CValue::post("_synchroDatetime", null);
     $this->ds = CSQLDataSource::get("std");
 }

 /**
  * Search an ICR by it's code
  *
  * @param string $code The code to find
  *
  * @return mixed|null
  */
 static function searchICR($code)
 {
     $ds = CSQLDataSource::get("ccamV2");
     $query = $ds->prepare("SELECT * FROM ccam_ICR WHERE code = %", $code);
     $result = $ds->exec($query);
     if ($ds->numRows($result)) {
         $row = $ds->fetchArray($result);
         return $row['ICR'];
     }
     return null;
 }

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

 static function insert($value)
 {
     $ds = CSQLDataSource::get("std");
     if (!$ds) {
         throw new Exception("No datasource available");
     }
     $query = "INSERT INTO `error_log_data` (`value`, `value_hash`)\n    VALUES (?1, ?2)\n    ON DUPLICATE KEY UPDATE `error_log_data_id` = LAST_INSERT_ID(`error_log_data_id`)";
     $query = $ds->prepare($query, $value, md5($value));
     if (!@$ds->exec($query)) {
         throw new Exception("Exec failed");
     }
     return $ds->insertId();
 }

/**
 * Delete content and update exchange
 *
 * @param CContentTabular $content_tabular Content tabular
 * @param int             $type_content_id Content ID
 * @param date            $date_max        Date max
 * @param int             $max             Max exchange
 *
 * @return int
 */
function deleteContentAndUpdateExchange(CContentTabular $content_tabular, $type_content_id, $date_max, $max)
{
    $ds = $content_tabular->_spec->ds;
    // Récupère les content Tabulé
    $query = "SELECT cx.content_id\r\n            FROM content_tabular AS cx, exchange_hl7v2 AS ec\r\n            WHERE ec.`date_production` < '{$date_max}'\r\n            AND ec.{$type_content_id} = cx.content_id\r\n            LIMIT {$max};";
    $ids = CMbArray::pluck($ds->loadList($query), "content_id");
    // Suppression du contenu Tabulé
    $query = "DELETE FROM content_tabular\r\n            WHERE content_id " . CSQLDataSource::prepareIn($ids);
    $ds->exec($query);
    // Mise à jour des échanges
    $query = "UPDATE exchange_hl7v2\r\n              SET `{$type_content_id}` = NULL \r\n              WHERE `{$type_content_id}` " . CSQLDataSource::prepareIn($ids);
    $ds->exec($query);
    $count = $ds->affectedRows();
    return $count;
}

 /**
  * Build an SQL query to replace a template string
  * Will check over content_html table to specify update query
  *
  * @param string $search              text to search
  * @param string $replace             text to replace
  * @param bool   $force_content_table Update content_html or compte_rendu table [optional]
  *
  * @return string The sql query
  */
 static function replaceTemplateQuery($search, $replace, $force_content_table = false)
 {
     static $_compte_rendu = null;
     static $_compte_rendu_content_id = null;
     $search = htmlentities($search);
     $replace = htmlentities($replace);
     $ds = CSQLDataSource::get("std");
     if ($_compte_rendu === null || $_compte_rendu_content_id === null) {
         $_compte_rendu = $ds->loadTable("compte_rendu") != null;
         $_compte_rendu_content_id = $_compte_rendu && $ds->loadField("compte_rendu", "content_id");
     }
     // Content specific table
     if ($force_content_table || $_compte_rendu && $_compte_rendu_content_id) {
         return "UPDATE compte_rendu AS cr, content_html AS ch\r\n        SET ch.content = REPLACE(`content`, '{$search}', '{$replace}')\r\n        WHERE cr.object_id IS NULL\r\n        AND cr.content_id = ch.content_id";
     }
     // Single table
     return "UPDATE `compte_rendu` \r\n      SET `source` = REPLACE(`source`, '{$search}', '{$replace}') \r\n      WHERE `object_id` IS NULL";
 }

 /**
  * count list of Op not linked to a plage
  *
  * @param date      $start    date de début
  * @param date|null $end      date de fin
  * @param array     $chir_ids chir targeted
  *
  * @return int number of HP found
  */
 static function countForDates($start, $end = null, $chir_ids = array())
 {
     $d_start = $start;
     $d_end = $end ? $end : $start;
     $op = new COperation();
     $ljoin = array();
     $ljoin["sejour"] = "sejour.sejour_id = operations.sejour_id";
     $where = array();
     if (count($chir_ids)) {
         $where["chir_id"] = CSQLDataSource::prepareIn($chir_ids);
     }
     $where["operations.plageop_id"] = "IS NULL";
     $where["operations.date"] = "BETWEEN '{$d_start}' AND '{$d_end}'";
     $where["operations.annulee"] = "= '0'";
     $where["sejour.group_id"] = "= '" . CGroups::loadCurrent()->_id . "'";
     /** @var COperation[] $listHorsPlage */
     return $op->countList($where, null, $ljoin);
 }

 static function loadAllFor($libelles)
 {
     $libelles = array_map("strtoupper", $libelles);
     // Initialisation du tableau
     $colors_by_libelle = array();
     foreach ($libelles as $_libelle) {
         $color = new self();
         $color->libelle = $_libelle;
         $colors_by_libelle[$_libelle] = $color;
     }
     $color = new self();
     $where = array();
     $libelles = array_map("addslashes", $libelles);
     $where["libelle"] = CSQLDataSource::prepareIn($libelles);
     foreach ($color->loadList($where) as $_color) {
         $colors_by_libelle[$_color->libelle] = $_color;
     }
     return $colors_by_libelle;
 }