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

/**
 * Fonction de construction du cache d'info des durées
 * d'hospi et d'interv
 *
 * @param string $tableName   Nom de la table de cache
 * @param string $tableFields Champs de la table de cache
 * @param array  $queryFields Liste des champs du select
 * @param string $querySelect Chaine contenant les éléments SELECT à utiliser
 * @param array  $queryWhere  Chaine contenant les éléments WHERE à utiliser
 *
 * @return void
 */
function buildPartialTables($tableName, $tableFields, $queryFields, $querySelect, $queryWhere)
{
    $ds = CSQLDataSource::get("std");
    $joinedFields = join(", ", $queryFields);
    // Intervale de temps
    $intervalle = CValue::get("intervalle");
    switch ($intervalle) {
        case "month":
            $deb = CMbDT::date("-1 month");
            break;
        case "6month":
            $deb = CMbDT::date("-6 month");
            break;
        case "year":
            $deb = CMbDT::date("-1  year");
            break;
        default:
            $deb = CMbDT::date("-10 year");
    }
    $fin = CMbDT::date();
    // Suppression si existe
    $drop = "DROP TABLE IF EXISTS `{$tableName}`";
    $ds->exec($drop);
    // Création de la table partielle
    $create = "CREATE TABLE `{$tableName}` (" . "\n`chir_id` int(11) unsigned NOT NULL default '0'," . "{$tableFields}" . "\n`ccam` varchar(255) NOT NULL default ''," . "\nKEY `chir_id` (`chir_id`)," . "\nKEY `ccam` (`ccam`)" . "\n) /*! ENGINE=MyISAM */;";
    $ds->exec($create);
    // Remplissage de la table partielle
    $query = "INSERT INTO `{$tableName}` ({$joinedFields}, `chir_id`, `ccam`)\r\n    SELECT {$querySelect}\r\n    operations.chir_id,\r\n    operations.codes_ccam AS ccam\r\n    FROM operations\r\n    LEFT JOIN users\r\n    ON operations.chir_id = users.user_id\r\n    LEFT JOIN plagesop\r\n    ON operations.plageop_id = plagesop.plageop_id\r\n    WHERE operations.annulee = '0'\r\n    {$queryWhere}\r\n    AND operations.date BETWEEN '{$deb}' AND '{$fin}'\r\n    GROUP BY operations.chir_id, ccam\r\n    ORDER BY ccam;";
    $ds->exec($query);
    CAppUI::stepAjax("Nombre de valeurs pour la table '{$tableName}': " . $ds->affectedRows(), UI_MSG_OK);
    // Insert dans la table principale si vide
    if (!$ds->loadResult("SELECT COUNT(*) FROM temps_op")) {
        $query = "INSERT INTO temps_op ({$joinedFields}, `chir_id`, `ccam`)\r\n      SELECT {$joinedFields}, `chir_id`, `ccam`\r\n      FROM {$tableName}";
        $ds->exec($query);
    } else {
        $query = "UPDATE temps_op, {$tableName} SET ";
        foreach ($queryFields as $queryField) {
            $query .= "\ntemps_op.{$queryField} = {$tableName}.{$queryField}, ";
        }
        $query .= "temps_op.chir_id = {$tableName}.chir_id" . "\nWHERE temps_op.chir_id = {$tableName}.chir_id" . "\nAND temps_op.ccam = {$tableName}.ccam";
        $ds->exec($query);
    }
}

/**
 * Parse le fichier et remplit la table correspondante
 *
 * @param string $file  File path
 * @param string $table Table name
 *
 * @return void
 */
function addFileIntoDB($file, $table)
{
    $reussi = 0;
    $echoue = 0;
    $ds = CSQLDataSource::get("cdarr");
    $handle = fopen($file, "r");
    // Ne pas utiliser fgetcsv, qui refuse de prendre en compte les caractères en majusucules accentués (et d'autres caractères spéciaux)
    while ($line = fgets($handle)) {
        $line = str_replace("'", "\\'", $line);
        $datas = explode("|", $line);
        $query = "INSERT INTO {$table} VALUES('" . implode("','", $datas) . "')";
        $ds->exec($query);
        if ($msg = $ds->error()) {
            $echoue++;
        } else {
            $reussi++;
        }
    }
    fclose($handle);
    CAppUI::stepAjax("ssr-import-cdarr-report", UI_MSG_OK, $file, $table, $reussi, $echoue);
}

function addFileIntoDB($file, $table)
{
    $reussi = 0;
    $echoue = 0;
    $ds = CSQLDataSource::get("ccamV2");
    $handle = fopen($file, "r");
    $values = array();
    $batch = 50;
    // Ne pas utiliser fgetcsv, qui refuse de prendre en compte les caractères en majusucules accentués (et d'autres caractères spéciaux)
    while ($line = fgets($handle)) {
        $line = str_replace("'", "\\'", $line);
        $values[] = explode("|", $line);
        if (count($values) == $batch) {
            insertValues($ds, $table, $values, $echoue, $reussi);
            $values = array();
        }
    }
    if (count($values)) {
        insertValues($ds, $table, $values, $echoue, $reussi);
    }
    CAppUI::stepAjax("Import du fichier {$file} dans la table {$table} : {$reussi} lignes ajoutée(s), {$echoue} échouée(s)", UI_MSG_OK);
    fclose($handle);
}

 * $Id$
 *
 * @package    Mediboard
 * @subpackage dPfacturation
 * @author     SARL OpenXtrem <[email protected]>
 * @license    GNU General Public License, see http://www.gnu.org/licenses/gpl.html
 * @version    $Revision$
 */
$out = fopen('php://output', 'w');
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="ExportCompta.xls"');
$facture_class = CValue::get("facture_class", 'CFactureEtablissement');
$factures_id = CValue::get("factures", array());
$factures_id = explode("|", $factures_id);
$where = array();
$where["facture_id"] = CSQLDataSource::prepareIn(array_values($factures_id));
$facture = new $facture_class();
$factures = $facture->loadList($where);
// Ligne d'entête
$fields = array();
$fields[] = "Date";
$fields[] = "Facture";
$fields[] = "Patient";
$fields[] = "Montant";
fputcsv($out, $fields, ';');
foreach ($factures as $_facture) {
    /* @var CFactureEtablissement $_facture*/
    $_facture->loadRefPatient();
    $_facture->loadRefsObjects();
    $_facture->loadRefsReglements();
    $fields = array();

$fin = CValue::get("fin", CMbDT::date());
$prat_id = CValue::get("prat_id", 0);
$service_id = CValue::get("service_id", 0);
$pratSel = new CMediusers();
$pratSel->load($prat_id);
$service = new CSalle();
$service->load($service_id);
$datax = array();
for ($i = $debut; $i <= $fin; $i = CMbDT::date("+1 MONTH", $i)) {
    $datax[] = CMbDT::transform("+0 DAY", $i, "%m/%Y");
}
$sql = "SELECT * FROM service WHERE";
if ($service_id) {
    $sql .= "\nAND id = '{$service_id}'";
}
$ds = CSQLDataSource::get("std");
$services = $ds->loadlist($sql);
$opbysalle = array();
foreach ($services as $service) {
    $id = $service["service_id"];
    $opbysalle[$id]["nom"] = $salle["nom"];
    $sql = "SELECT COUNT(sejour.sejour_id) AS total," . "\nDATE_FORMAT(sejour.entree_prevue, '%m/%Y') AS mois," . "\nDATE_FORMAT(sejour.entre_prevue, '%Y%m') AS orderitem," . "\nservice.nom AS nom" . "\nFROM sejour, affectation, services, chambre, lit" . "\nWHERE sejour.annule = '0'" . "\nAND sejour.entree_prevue BETWEEN '{$debut}' AND '{$fin}'";
    if ($prat_id) {
        $sql .= "\nAND sejour.praticien_id = '{$prat_id}'";
    }
    $sql .= "\nAND service.service_id = chambre.service_id" . "\nAND chambre.chambre_id = lit.chambre_id" . "\nAND lit.affectation_id = affectation.affectation_id" . "\nAND affectation.sejour_id = sejour.sejour_id" . "\nAND service.service_id = '{$id}'" . "\nGROUP BY mois" . "\nORDER BY orderitem";
    $result = $ds->loadlist($sql);
    foreach ($datax as $x) {
        $f = true;
        foreach ($result as $totaux) {
            if ($x == $totaux["mois"]) {