/** * 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; }
/** * 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; }
/** * @see parent::open() */ function open() { if (self::$ds = CSQLDataSource::get("std")) { return true; } return false; }
/** * 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; }
/** * 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; }
/** * 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); }
/** * 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; }
/** * 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; }
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"); }
/** * 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(); }
/** * 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"; }
/** * 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); }
/** * Construct **/ function __construct() { parent::__construct(); $this->mod_name = "dPccam"; $this->makeRevision("all"); $query = "CREATE TABLE `ccamfavoris` (\r\n `favoris_id` bigint(20) NOT NULL auto_increment,\r\n `favoris_user` int(11) NOT NULL default '0',\r\n `favoris_code` varchar(7) NOT NULL default '',\r\n PRIMARY KEY (`favoris_id`)\r\n ) /*! ENGINE=MyISAM */ COMMENT='table des favoris'"; $this->addQuery($query); $this->makeRevision("0.1"); $query = "ALTER TABLE `ccamfavoris` \r\n CHANGE `favoris_id` `favoris_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\r\n CHANGE `favoris_user` `favoris_user` int(11) unsigned NOT NULL DEFAULT '0';"; $this->addQuery($query); $this->makeRevision("0.11"); $query = "ALTER TABLE `ccamfavoris`\r\n ADD `object_class` VARCHAR(25) NOT NULL DEFAULT 'COperation';"; $this->addQuery($query); $this->makeRevision("0.12"); $query = "ALTER TABLE `ccamfavoris` \r\n ADD INDEX (`favoris_user`);"; $this->addQuery($query); $this->makeRevision("0.13"); $query = "CREATE TABLE `frais_divers` (\r\n `frais_divers_id` INT (11) UNSIGNED NOT NULL auto_increment PRIMARY KEY,\r\n `type_id` INT (11) UNSIGNED NOT NULL,\r\n `coefficient` FLOAT NOT NULL DEFAULT '1',\r\n `quantite` INT (11) UNSIGNED,\r\n `facturable` ENUM ('0','1') NOT NULL DEFAULT '0',\r\n `montant_depassement` DECIMAL (10,3),\r\n `montant_base` DECIMAL (10,3),\r\n `executant_id` INT (11) UNSIGNED NOT NULL,\r\n `object_id` INT (11) UNSIGNED NOT NULL,\r\n `object_class` VARCHAR (255) NOT NULL\r\n ) /*! ENGINE=MyISAM */;"; $this->addQuery($query); $query = "ALTER TABLE `frais_divers` \r\n ADD INDEX (`type_id`),\r\n ADD INDEX (`executant_id`),\r\n ADD INDEX (`object_id`);"; $this->addQuery($query); $query = "CREATE TABLE `frais_divers_type` (\r\n `frais_divers_type_id` INT (11) UNSIGNED NOT NULL auto_increment PRIMARY KEY,\r\n `code` VARCHAR (16) NOT NULL,\r\n `libelle` VARCHAR (255) NOT NULL,\r\n `tarif` DECIMAL (10,3) NOT NULL,\r\n `facturable` ENUM ('0','1') NOT NULL DEFAULT '0'\r\n ) /*! ENGINE=MyISAM */;"; $this->addQuery($query); $this->makeRevision("0.14"); $this->addPrefQuery("new_search_ccam", "1"); $this->makeRevision("0.15"); $query = "ALTER TABLE `frais_divers` \r\n CHANGE `facturable` `facturable` ENUM ('0','1') NOT NULL DEFAULT '1';"; $this->addQuery($query); $this->makeRevision("0.16"); $this->addPrefQuery("multiple_select_ccam", "0"); $this->makeRevision("0.17"); $this->addPrefQuery("user_executant", "0"); $this->makeRevision("0.18"); $this->addDependency("dPcabinet", "0.1"); $this->addDependency("dPplanningOp", "1.07"); $query = "ALTER TABLE `frais_divers`\r\n ADD `execution` DATETIME NOT NULL;"; $this->addQuery($query); $query = "UPDATE `frais_divers`\r\n INNER JOIN `consultation` ON (`frais_divers`.`object_id` = `consultation`.`consultation_id`)\r\n INNER JOIN `plageconsult` ON (`consultation`.`plageconsult_id` = `plageconsult`.`plageconsult_id`)\r\n SET `frais_divers`.`execution` = CONCAT(`plageconsult`.`date`, ' ', `consultation`.`heure`)\r\n WHERE `frais_divers`.`object_class` = 'CConsultation';"; $this->addQuery($query); $query = "UPDATE `frais_divers`\r\n INNER JOIN `operations` ON (`frais_divers`.`object_id` = `operations`.`operation_id`)\r\n INNER JOIN `plagesop` ON (`operations`.`plageop_id` = `plagesop`.`plageop_id`)\r\n SET `frais_divers`.`execution` = CONCAT(`plagesop`.`date`, ' ', `operations`.`time_operation`)\r\n WHERE `frais_divers`.`object_class` = 'COperation'\r\n AND `operations`.`date` IS NULL;"; $this->addQuery($query); $query = "UPDATE `frais_divers`\r\n INNER JOIN `operations` ON (`frais_divers`.`object_id` = `operations`.`operation_id`)\r\n SET `frais_divers`.`execution` = CONCAT(`operations`.`date`, ' ', `operations`.`time_operation`)\r\n WHERE `frais_divers`.`object_class` = 'COperation'\r\n AND `operations`.`date` IS NOT NULL;"; $this->addQuery($query); $query = "UPDATE `frais_divers`\r\n INNER JOIN `sejour` ON (`frais_divers`.`object_id` = `sejour`.`sejour_id`)\r\n SET `frais_divers`.`execution` = `sejour`.`entree`\r\n WHERE `frais_divers`.`object_class` = 'CSejour';"; $this->addQuery($query); $this->makeRevision("0.19"); $query = "ALTER TABLE `frais_divers`\r\n ADD `num_facture` INT (11) UNSIGNED NOT NULL DEFAULT '1'"; $this->addQuery($query); $query = "ALTER TABLE `frais_divers`\r\n ADD INDEX (`execution`),\r\n ADD INDEX (`object_class`);"; $this->addQuery($query); $this->makeRevision("0.20"); $query = "ALTER TABLE `acte_ccam`\r\n ADD `position_dentaire` VARCHAR (255),\r\n ADD `numero_forfait_technique` INT (11) UNSIGNED,\r\n ADD `numero_agrement` BIGINT (20) UNSIGNED,\r\n ADD `rapport_exoneration` ENUM ('4','7','C','R');"; $this->addQuery($query); $this->makeRevision('0.21'); $query = "CREATE TABLE `codage_ccam` (\r\n `codage_ccam_id` INT (11) UNSIGNED NOT NULL auto_increment PRIMARY KEY,\r\n `association_rule` ENUM('G1', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG1', 'EG2',\r\n 'EG3', 'EG4', 'EG5', 'EG6', 'EG7', 'EH', 'EI', 'GA', 'GB', 'G2'),\r\n `association_mode` ENUM('auto', 'user_choice') DEFAULT 'auto',\r\n `codable_class` ENUM('CConsultation', 'CSejour', 'COperation') NOT NULL,\r\n `codable_id` INT (11) UNSIGNED NOT NULL,\r\n `praticien_id` INT (11) UNSIGNED NOT NULL,\r\n `locked` ENUM('0', '1') NOT NULL DEFAULT '0'\r\n ) /*! ENGINE=MyISAM */;"; $this->addQuery($query); $query = "ALTER TABLE `codage_ccam`\r\n ADD INDEX (`codable_class`, `codable_id`),\r\n ADD INDEX (`praticien_id`),\r\n ADD UNIQUE INDEX (`codable_class`, `codable_id`, `praticien_id`);"; $this->addQuery($query); $this->makeRevision('0.22'); $query = "ALTER TABLE `codage_ccam`\r\n ADD `nb_acts` INT (2) NOT NULL;"; $this->addQuery($query); $this->makeRevision('0.23'); $query = "ALTER TABLE `codage_ccam`\r\n CHANGE `association_rule` `association_rule` ENUM('G1', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG1', 'EG2',\r\n 'EG3', 'EG4', 'EG5', 'EG6', 'EG7', 'EH', 'EI', 'GA', 'GB', 'G2', 'M');"; $this->addQuery($query); $this->makeRevision('0.24'); $query = "ALTER TABLE `codage_ccam`\r\n DROP `nb_acts`;"; $this->addQuery($query); $this->makeRevision('0.25'); $query = "ALTER TABLE `codage_ccam` DROP INDEX `codable_class_2`;"; $this->addQuery($query); $query = "ALTER TABLE `codage_ccam`\r\n ADD `activite_anesth` ENUM('0', '1') NOT NULL DEFAULT '0',\r\n ADD UNIQUE INDEX uk_codage_ccam (`codable_class`, `codable_id`, `praticien_id`, `activite_anesth`);"; $this->addQuery($query); $this->makeRevision('0.26'); $this->addPrefQuery('actes_comp_supp_favoris', '1'); $query = "ALTER TABLE `acte_ccam`\r\n ADD `accord_prealable` ENUM ('0', '1') DEFAULT '0',\r\n ADD `date_demande_accord` DATE;"; $this->addQuery($query); $this->makeRevision('0.27'); $query = "ALTER TABLE `codage_ccam`\r\n ADD `date` DATE NOT NULL,\r\n DROP INDEX uk_codage_ccam;"; $this->addQuery($query); $query = "ALTER TABLE `frais_divers`\r\n ADD `gratuit` ENUM('0', '1') NOT NULL DEFAULT '0';"; $this->addQuery($query); $this->addMethod('updateDateCodage'); $this->makeRevision('0.28'); $query = "CREATE TABLE `devis_codage` (\r\n `devis_codage_id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\r\n `codable_class` ENUM('CConsultation', 'COperation') NOT NULL,\r\n `codable_id` INT(11) UNSIGNED NOT NULL,\r\n `patient_id` INT(11) UNSIGNED NOT NULL,\r\n `praticien_id` INT(11) UNSIGNED NOT NULL,\r\n `creation_date` DATETIME NOT NULL,\r\n `date` DATE,\r\n `event_type` ENUM('CConsultation', 'COperation') DEFAULT 'CConsultation',\r\n `libelle` VARCHAR (255),\r\n `comment` TEXT,\r\n `codes_ccam` VARCHAR(255),\r\n `facture` ENUM ('0','1') DEFAULT '0',\r\n `tarif` VARCHAR(50),\r\n `exec_tarif` DATETIME,\r\n `consult_related_id` INT (11) UNSIGNED,\r\n `base` FLOAT(6),\r\n `dh` FLOAT(6),\r\n `ht` FLOAT(6),\r\n `tax_rate` FLOAT\r\n ) /*! ENGINE=MyISAM */;"; $this->addQuery($query); $query = "ALTER TABLE `devis_codage`\r\n ADD INDEX (`codable_class`),\r\n ADD INDEX (`codable_id`);"; $this->addQuery($query); $query = "ALTER TABLE `codage_ccam`\r\n CHANGE `codable_class` `codable_class` ENUM('CConsultation', 'CSejour', 'COperation', 'CDevisCodage') NOT NULL;"; $this->addQuery($query); $this->makeRevision('0.29'); $this->addPrefQuery('precode_modificateur_7', CAppUi::conf('dPccam CCodable precode_modificateur_7')); $this->addPrefQuery('precode_modificateur_J', CAppUi::conf('dPccam CCodable precode_modificateur_J')); $this->makeRevision('0.30'); $this->makeRevision('0.31'); $this->addPrefQuery('spread_modifiers', 0); $this->mod_version = '0.32'; // Data source query // Nouvelle version CCAM $query = "SHOW TABLES LIKE 'p_acte'"; $this->addDatasource("ccamV2", $query); // Tarifs de convergence if (array_key_exists('ccamV2', CAppUI::conf('db'))) { $dsn = CSQLDataSource::get('ccamV2'); if ($dsn->fetchRow($dsn->exec('SHOW TABLES LIKE \'convergence\';'))) { // Nouvelle table de convergence $query = "SELECT COUNT(*) FROM `convergence` HAVING COUNT(*) = 3761;"; $this->addDatasource("ccamV2", $query); } else { $query = "SHOW TABLES LIKE 'convergence'"; $this->addDatasource("ccamV2", $query); } } // Tarifs NGAP $query = "SHOW TABLES LIKE 'tarif_ngap';"; $this->addDatasource("ccamV2", $query); if (array_key_exists('ccamV2', CAppUI::conf('db'))) { $dsn = CSQLDataSource::get('ccamV2'); if ($dsn->fetchRow($dsn->exec('SHOW TABLES LIKE \'tarif_ngap\';'))) { // Suppression des actes CNP et VNP (codes exacts CNPSY et VNPSY) $query = "SELECT * FROM `tarif_ngap` WHERE `code` = 'CNPSY';"; $this->addDatasource("ccamV2", $query); } } // Nouvelle architecture CCAM $query = "SHOW TABLES LIKE 'p_acte';"; $this->addDatasource("ccamV2", $query); // Version 40 de la CCAM $query = "SELECT *\r\n FROM `c_notesarborescence`\r\n WHERE `CODEMENU` LIKE '000583'\r\n AND TEXTE LIKE 'Facturation : Possibilité de facturation de l\\'association des actes GAQE001 et GCQE001%'"; $this->addDatasource("ccamV2", $query); }
static function loadValuesFromDB() { global $dPconfig; $ds = CSQLDataSource::get("std"); $request = "SELECT * FROM config_db WHERE config_db.key " . CSQLDataSource::prepareNotIn(self::$forbidden_values); $configs = $ds->loadList($request); foreach ($configs as $_value) { CMbConfig::loadConf(explode(" ", $_value['key']), $_value['value'], $dPconfig); } // Réinclusion du config_overload if (is_file(__DIR__ . "/config_overload.php")) { include __DIR__ . "/config_overload.php"; } }
/** * Get the latest constantes values * * @param int|CPatient $patient The patient to load the constantes for * @param string $datetime The reference datetime * @param array $selection A selection of constantes to load * @param CMbObject $context The context * @param boolean $use_cache Force the function to return the latest_values is already set * * @return array The constantes values and dates */ static function getLatestFor($patient, $datetime = null, $selection = array(), $context = null, $use_cache = true) { $patient_id = $patient instanceof CPatient ? $patient->_id : $patient; if (isset(self::$_latest_values[$patient_id][$datetime]) && $use_cache === true) { return self::$_latest_values[$patient_id][$datetime]; } if (empty($selection)) { $list_constantes = CConstantesMedicales::$list_constantes; } else { $list_constantes = array_intersect_key(CConstantesMedicales::$list_constantes, array_flip($selection)); } // Constante que l'on va construire $constante = new CConstantesMedicales(); if (!$patient_id) { return array($constante, array()); } $constante->patient_id = $patient_id; $constante->datetime = CMbDT::dateTime(); $constante->loadRefPatient(); $where = array("patient_id" => "= '{$patient_id}'"); if ($context) { $where["context_class"] = " = '{$context->_class}'"; $where["context_id"] = " = '{$context->_id}'"; } if ($datetime) { $where["datetime"] = "<= '{$datetime}'"; } if (count($selection)) { $ors = array(); foreach ($selection as $_item) { $ors[] = "{$_item} IS NOT NULL"; } $where[] = implode(" OR ", $ors); } $count = $constante->countList($where); // Load all constants instead of checking every type to reduce number of SQL queries /** @var self[] $all_list */ $all_list = array(); if ($count <= 30) { $all_list = $constante->loadList($where, "datetime DESC"); } $list_datetimes = array(); foreach ($list_constantes as $type => $params) { $list_datetimes[$type] = null; if ($type[0] == "_") { continue; } // Load them, if any ... if ($count > 0) { // Load them all and dispatch if ($count <= 30) { foreach ($all_list as $_const) { $_value = $_const->{$type}; if ($_value != null) { $constante->{$type} = $_value; $list_datetimes[$type] = $_const->datetime; break; } } } else { $_where = $where; $_where[$type] = "IS NOT NULL"; $_list = $constante->loadList($_where, "datetime DESC", 1); if (count($_list)) { $_const = reset($_list); $constante->{$type} = $_const->{$type}; $list_datetimes[$type] = $_const->datetime; } } } } // Cumul de la diurese if ($datetime) { foreach ($list_constantes as $_name => $_params) { if (isset($_params["cumul_reset_config"]) || isset($_params["formula"])) { $day_defore = CMbDT::dateTime("-24 hours", $datetime); if (isset($_params["cumul_reset_config"]) && !isset($_params['formula'])) { $cumul_field = '_' . $_name . '_cumul'; $reset_hour = str_pad(self::getHostConfig($_params["cumul_reset_config"], $context), 2, '0', STR_PAD_LEFT); $cumul_begin = ''; $cumul_end = ''; if ($datetime >= CMbDT::format($datetime, "%Y-%m-%d {$reset_hour}:00:00")) { $cumul_begin = CMbDT::format($datetime, "%Y-%m-%d {$reset_hour}:00:00"); $cumul_end = CMbDT::format(CMbDT::date('+1 DAY', $datetime), "%Y-%m-%d {$reset_hour}:00:00"); } else { $cumul_begin = CMbDT::format(CMbDT::date('-1 DAY', $datetime), "%Y-%m-%d {$reset_hour}:00:00"); $cumul_end = CMbDT::format($datetime, "%Y-%m-%d {$reset_hour}:00:00"); } $query = new CRequest(); $query->addSelect("SUM(`{$_name}`)"); $query->addTable('constantes_medicales'); $query->addWhere(array("`datetime` >= '{$cumul_begin}'", "`datetime` <= '{$cumul_end}'", "`{$_name}` IS NOT NULL", "`patient_id` = {$patient_id}")); $ds = CSQLDataSource::get('std'); $constante->{$cumul_field} = $ds->loadResult($query->makeSelect()); } else { // cumul de plusieurs champs (avec formule) $formula = $_params["formula"]; foreach ($formula as $_field => $_sign) { $_where = $where; $_where[$_field] = "IS NOT NULL"; $_where[] = "datetime >= '{$day_defore}'"; $_list = $constante->loadList($_where); foreach ($_list as $_const) { if ($_sign === "+") { $constante->{$_name} += $_const->{$_field}; } else { $constante->{$_name} -= $_const->{$_field}; } } } } } } } $constante->updateFormFields(); // Don't cache partial loadings if (empty($selection)) { self::$_latest_values[$patient_id][$datetime] = array($constante, $list_datetimes); } return array($constante, $list_datetimes); }
<?php /** * $Id: httpreq_do_ngap_autocomplete.php 23335 2014-05-27 12:51:12Z asmiane $ * * @package Mediboard * @subpackage Cabinet * @author SARL OpenXtrem <*****@*****.**> * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html * @version $Revision: 23335 $ */ CCanDo::check(); $ds = CSQLDataSource::get("ccamV2"); $object_id = CValue::get("object_id"); $object_class = CValue::get("object_class"); $code = CValue::post("code"); $executant_id = CValue::post("executant_id"); // Chargement de l'object /** @var CCodable $object */ $object = new $object_class(); $object->load($object_id); // Chargement de ses actes NGAP $object->countActes(); $praticien = CMediusers::get($executant_id); $praticien->loadRefFunction(); $praticien->spec_cpam_id ? $spe_undefined = false : ($spe_undefined = true); $praticien->spec_cpam_id ? $spe = $praticien->spec_cpam_id : ($spe = 1); // Creation de la requete permettant de retourner tous les codes correspondants if ($code) { $sql = "SELECT c.`code`, c.`libelle`, c.`lettre_cle`, t.`tarif`\r\n FROM `codes_ngap` as c, `tarif_ngap` as t, `specialite_to_tarif_ngap` as s\r\n WHERE c.`code` LIKE ?1 AND t.`code` = c.`code` AND t.`zone` = ?2 AND s.`specialite` = ?3 AND t.`tarif_ngap_id` = s.`tarif_id`"; if (!$object->_count_actes) {
} if ($type_name == "UF") { // Chargement de l'uf à ajouter/éditer $uf = new CUniteFonctionnelle(); $uf->group_id = $group->_id; $uf->load($uf_id); $uf->loadRefUm(); $uf->loadRefsNotes(); // Récupération des ufs $order = "group_id, code"; $ufs = array("hebergement" => $uf->loadGroupList(array("type" => "= 'hebergement'"), $order), "medicale" => $uf->loadGroupList(array("type" => "= 'medicale'"), $order), "soins" => $uf->loadGroupList(array("type" => "= 'soins'"), $order)); // Récupération des Unités Médicales (pmsi) $ums = array(); $ums_infos = array(); $um = new CUniteMedicale(); if (CSQLDataSource::get("sae") && CModule::getActive("atih")) { $um_infos = new CUniteMedicaleInfos(); $ums = $um->loadListUm(); $group = CGroups::loadCurrent(); $where["group_id"] = " = '{$group->_id}'"; $where["mode_hospi"] = " IS NOT NULL"; $where["nb_lits"] = " IS NOT NULL"; $ums_infos = $um_infos->loadList($where); } $smarty->assign("ufs", $ufs); $smarty->assign("uf", $uf); $smarty->assign("ums", $ums); $smarty->assign("ums_infos", $ums_infos); $smarty->display("inc_vw_idx_ufs.tpl"); } if ($type_name == "secteurs") {
* @author SARL OpenXtrem <*****@*****.**> * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html * @version $Revision$ * @link http://www.mediboard.org */ CCanDo::checkAdmin(); $dsn = CValue::get("dsn"); $table = CValue::get("table"); $tooltip = CValue::get("tooltip"); $start = (int) CValue::get("start"); $count = (int) CValue::getOrSession("count", 50); $order_column = CValue::getOrSession("order_column"); $order_way = CValue::getOrSession("order_way", "ASC"); $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}");
<?php /** * $Id$ * * @category EAI * @package Mediboard * @author SARL OpenXtrem <*****@*****.**> * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html * @version $Revision$ * @link http://www.mediboard.org */ CCanDo::checkAdmin(); $ds = CSQLDataSource::get("ASIP"); $path = "modules/eai/resources"; if (!$ds) { CAppUI::stepAjax("Import impossible - Aucune source de données", UI_MSG_ERROR); CApp::rip(); } $files = glob("{$path}/*.jv"); $lineCount = 0; foreach ($files as $_file) { $name = basename($_file); $name = substr($name, strpos($name, "_") + 1); $table = substr($name, 0, strrpos($name, ".")); $table = strtolower($table); if (!$ds) { CAppUI::stepAjax("Import impossible - Source non présente", UI_MSG_ERROR); continue; } $ds->query("CREATE TABLE IF NOT EXISTS `{$table}` (\r\n `table_id` INT (11) UNSIGNED NOT NULL auto_increment PRIMARY KEY,\r\n `code` VARCHAR (255) NOT NULL,\r\n `oid` VARCHAR (255) NOT NULL,\r\n `libelle` VARCHAR (255) NOT NULL,\r\n INDEX (`table_id`)\r\n )/*! ENGINE=MyISAM */;");
<?php $_date_min = CValue::post('_date_min'); $_date_max = CValue::post('_date_max'); $user_id = CValue::post('user_id'); $duration = CValue::post('duration'); $duration_operand = CValue::post('duration_operand'); $purge_limit = CValue::post('purge_limit', '100'); $just_count = CValue::post('just_count'); $purge_limit = $purge_limit ? $purge_limit : 100; $ds = CSQLDataSource::get('std'); $log = new CLongRequestLog(); $where = array(); if ($_date_min) { $where[] = $ds->prepare('`datetime` >= ?', $_date_min); } if ($_date_max) { $where[] = $ds->prepare('`datetime` <= ?', $_date_max); } if ($user_id) { $where['user_id'] = $ds->prepare('= ?', $user_id); } if ($duration && in_array($duration_operand, array('<', '<=', '=', '>', '>='))) { $where['duration'] = $ds->prepare("{$duration_operand} ?", $duration); } $count = $log->countList($where); $msg = '%d CLongRequestLog to be removed.'; if ($count == 1) { $msg = 'One CLongRequestLog to be removed.'; } elseif (!$count) { $msg = 'No CLongRequestLog to be removed.';
<?php /** * dPcim10 * * @category Cim10 * @package Mediboard * @author SARL OpenXtrem <*****@*****.**> * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html * @version SVN: $Id:$ * @link http://www.mediboard.org */ global $can; $can->needsAdmin(); CApp::setTimeLimit(360); $sourcePath = "modules/dPcim10/base/cim10.tar.gz"; $targetDir = "tmp/cim10"; $targetPath = "tmp/cim10/cim10.sql"; // Extract the SQL dump if (null == ($nbFiles = CMbPath::extract($sourcePath, $targetDir))) { CAppUI::stepAjax("Erreur, impossible d'extraire l'archive", UI_MSG_ERROR); } CAppUI::stepAjax("Extraction de {$nbFiles} fichier(s)", UI_MSG_OK); $ds = CSQLDataSource::get("cim10"); if (null == ($lineCount = $ds->queryDump($targetPath))) { $msg = $ds->error(); CAppUI::stepAjax("Erreur de requête SQL: {$msg}", UI_MSG_ERROR); } CAppUI::stepAjax("Import effectué avec succès de {$lineCount} lignes", UI_MSG_OK);
function countMatchingPatients() { $ds = CSQLDataSource::get("std"); $res = $ds->query("SELECT COUNT(*) AS total,\r\n CONVERT( GROUP_CONCAT(`patient_id` SEPARATOR '|') USING latin1 ) AS ids ,\r\n LOWER( CONCAT_WS( '-',\r\n REPLACE( REPLACE( REPLACE( REPLACE( `nom` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) ,\r\n REPLACE( REPLACE( REPLACE( REPLACE( `prenom` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) ,\r\n `naissance`\r\n , QUOTE( REPLACE( REPLACE( REPLACE( REPLACE( `nom_jeune_fille` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) )\r\n , QUOTE( REPLACE( REPLACE( REPLACE( REPLACE( `prenom_2` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) )\r\n , QUOTE( REPLACE( REPLACE( REPLACE( REPLACE( `prenom_3` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) )\r\n , QUOTE( REPLACE( REPLACE( REPLACE( REPLACE( `prenom_4` , '\\\\', '' ) , \"'\", '' ) , '-', '' ) , ' ', '' ) )\r\n )) AS hash\r\n FROM `patients`\r\n GROUP BY hash\r\n HAVING total > 1"); return intval($ds->numRows($res)); }
/** * Launches module upgrade process * * @param string $oldRevision Revision before upgrade * @param bool $core_upgrade True if it's a core module upgrade * * @return string|null New revision, null on error */ function upgrade($oldRevision, $core_upgrade = false) { /*if (array_key_exists($this->mod_version, $this->queries)) { CAppUI::setMsg("Latest revision '%s' should not have upgrade queries", UI_MSG_ERROR, $this->mod_version); return; }*/ if (!array_key_exists($oldRevision, $this->queries) && !array_key_exists($oldRevision, $this->config_moves) && !array_key_exists($oldRevision, $this->functions)) { CAppUI::setMsg("No queries, functions or config moves for '%s' setup at revision '%s'", UI_MSG_WARNING, $this->mod_name, $oldRevision); return null; } // Point to the current revision reset($this->revisions); while ($oldRevision != ($currRevision = current($this->revisions))) { next($this->revisions); } $depFailed = false; do { // Check for dependencies foreach ($this->dependencies[$currRevision] as $dependency) { $module = @CModule::getInstalled($dependency->module); if (!$module || $module->mod_version < $dependency->revision) { $depFailed = true; CAppUI::setMsg("Failed module depency for '%s' at revision '%s'", UI_MSG_WARNING, $dependency->module, $dependency->revision); } } if ($depFailed) { return $currRevision; } // Set Time Limit if ($this->timeLimit[$currRevision]) { CApp::setTimeLimit($this->timeLimit[$currRevision]); } // Query upgrading foreach ($this->queries[$currRevision] as $_query) { list($query, $ignore_errors, $dsn) = $_query; $ds = $dsn ? CSQLDataSource::get($dsn) : $this->ds; if (!$ds->exec($query)) { if ($ignore_errors) { CAppUI::setMsg("Errors ignored for revision '%s'", UI_MSG_OK, $currRevision); continue; } CAppUI::setMsg("Error in queries for revision '%s': see logs", UI_MSG_ERROR, $currRevision); return $currRevision; } } // Callback upgrading foreach ($this->functions[$currRevision] as $function) { if (!call_user_func($function)) { $function_name = get_class($function[0]) . "->" . $function[1]; CAppUI::setMsg("Error in function '%s' call back for revision '%s': see logs", UI_MSG_ERROR, $function_name, $currRevision); return $currRevision; } } // Preferences foreach ($this->preferences[$currRevision] as $_pref) { list($_name, $_default, $_restricted) = $_pref; // Former pure SQL system // Cannot check against module version or fresh install will generate errors if (self::isOldPrefSystem($core_upgrade)) { $query = "SELECT * FROM `user_preferences` WHERE `pref_user` = '0' AND `pref_name` = '{$_name}'"; $result = $this->ds->exec($query); if (!$this->ds->numRows($result)) { $query = "INSERT INTO `user_preferences` (`pref_user` , `pref_name` , `pref_value`)\n VALUES ('0', '{$_name}', '{$_default}');"; $this->ds->exec($query); } } else { $pref = new CPreferences(); $where = array(); $where["user_id"] = " IS NULL"; $where["key"] = " = '{$_name}'"; if (!$pref->loadObject($where)) { $pref->key = $_name; $pref->value = $_default; $pref->restricted = $_restricted ? "1" : "0"; $pref->store(); } } } // Config moves if (count($this->config_moves[$currRevision])) { foreach ($this->config_moves[$currRevision] as $config) { CAppUI::setConf($config[1], CAppUI::conf($config[0])); } } } while ($currRevision = next($this->revisions)); return $this->mod_version; }
<?php /** * $Id: httpreq_do_add_insee.php 19219 2013-05-21 12:26:07Z phenxdesign $ * * @package Mediboard * @subpackage Patients * @author SARL OpenXtrem <*****@*****.**> * @license GNU General Public License, see http://www.gnu.org/licenses/gpl.html * @version $Revision: 19219 $ */ CCanDo::checkAdmin(); $sourcePath = "modules/dPpatients/INSEE/insee.tar.gz"; $targetDir = "tmp/insee"; $targetPath = "tmp/insee/insee.sql"; // Extract the SQL dump if (null == ($nbFiles = CMbPath::extract($sourcePath, $targetDir))) { CAppUI::stepAjax("Erreur, impossible d'extraire l'archive", UI_MSG_ERROR); } CAppUI::stepAjax("Extraction de {$nbFiles} fichier(s)", UI_MSG_OK); $ds = CSQLDataSource::get("INSEE"); if (null == ($lineCount = $ds->queryDump($targetPath, true))) { $msg = $ds->error(); CAppUI::stepAjax("Erreur de requête SQL: {$msg}", UI_MSG_ERROR); } CAppUI::stepAjax("import effectué avec succès de {$lineCount} lignes", UI_MSG_OK);
/** * Get supply stats * * @param CProduct[] $products List of products * @param string $since Date start offset * @param string $date_max Max date * * @return array */ static function getSupplyMultiple($products, $since = "-1 MONTH", $date_max = null) { $ds = CSQLDataSource::get("std"); $where = array("product.product_id" => $ds->prepareIn(CMbArray::pluck($products, "_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"); $sql->addSelect("product.product_id, SUM(product_order_item_reception.quantity) AS sum"); $sql->addLJoin($ljoin); $sql->addGroup("product.product_id"); $sql->addWhere($where); return $ds->loadHashList($sql->makeSelect()); }
$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"]) {
$type = CValue::get("type", "traitant"); CView::enforceSlave(); $group_id = CGroups::loadCurrent()->_id; // Compteur d'années $years = array(); for ($_year = 1980; $_year <= 2030; $_year++) { $years[] = $_year; } // En utilisant les médecins adressant le séjour $queryAdresse = "SELECT\n COUNT(DISTINCT(`sejour`.`sejour_id`)) AS total,\n `medecin`.`nom`, `medecin`.`prenom`, `medecin`.`adresse`, `medecin`.`ville`, `medecin`.`cp`\n FROM `sejour`\n LEFT JOIN `medecin`\n ON `medecin`.`medecin_id` = `sejour`.`adresse_par_prat_id`\n WHERE `sejour`.`entree` BETWEEN '{$year}-01-01' AND '{$year}-12-31'\n AND `sejour`.`group_id` = '{$group_id}'\n GROUP BY `sejour`.`adresse_par_prat_id`\n ORDER BY total DESC"; // En utilisant le médecin traitant $queryTraitant = "SELECT\n COUNT(DISTINCT(`sejour`.`sejour_id`)) AS total,\n `medecin`.`nom`, `medecin`.`prenom`, `medecin`.`adresse`, `medecin`.`ville`, `medecin`.`cp`\n FROM `sejour`\n LEFT JOIN `patients`\n ON `patients`.`patient_id` = `sejour`.`patient_id`\n LEFT JOIN `medecin`\n ON `medecin`.`medecin_id` = `patients`.`medecin_traitant`\n WHERE `sejour`.`entree` BETWEEN '{$year}-01-01' AND '{$year}-12-31'\n AND `sejour`.`group_id` = '{$group_id}'\n GROUP BY `patients`.`medecin_traitant`\n ORDER BY total DESC"; // En utilisant l'adresse du patient $baseINSEE = CSQLDataSource::get("INSEE")->config["dbname"]; $queryPatient = "SELECT\n COUNT(DISTINCT(`sejour`.`sejour_id`)) AS total,\n `{$baseINSEE}`.`communes_france`.`commune` AS ville, `patients`.`cp`\n FROM `sejour`\n LEFT JOIN `patients`\n ON `patients`.`patient_id` = `sejour`.`patient_id`\n LEFT JOIN `{$baseINSEE}`.`communes_france`\n ON `{$baseINSEE}`.`communes_france`.`code_postal` = `patients`.`cp`\n WHERE `sejour`.`entree` BETWEEN '{$year}-01-01' AND '{$year}-12-31'\n AND `sejour`.`group_id` = '{$group_id}'\n GROUP BY `patients`.`cp`\n ORDER BY total DESC"; $source = CSQLDataSource::get("std"); $listResult = array(); switch ($type) { case "traitant": $listResult = $source->loadList($queryTraitant); break; case "adresse": $listResult = $source->loadList($queryAdresse); break; case "domicile": $listResult = $source->loadList($queryPatient); break; default: $listResult = $source->loadList($queryTraitant); } // Création du template