1047 lines
36 KiB
HTML
1047 lines
36 KiB
HTML
<!DOCTYPE html>
|
|
|
|
<html>
|
|
|
|
<head>
|
|
|
|
<meta charset="utf-8" />
|
|
<meta name="generator" content="pandoc" />
|
|
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />
|
|
|
|
|
|
|
|
|
|
<title>sqlScripts.knit</title>
|
|
|
|
<script src="site_libs/header-attrs-2.16/header-attrs.js"></script>
|
|
<script src="site_libs/jquery-3.6.0/jquery-3.6.0.min.js"></script>
|
|
<meta name="viewport" content="width=device-width, initial-scale=1" />
|
|
<link href="site_libs/bootstrap-3.3.5/css/cosmo.min.css" rel="stylesheet" />
|
|
<script src="site_libs/bootstrap-3.3.5/js/bootstrap.min.js"></script>
|
|
<script src="site_libs/bootstrap-3.3.5/shim/html5shiv.min.js"></script>
|
|
<script src="site_libs/bootstrap-3.3.5/shim/respond.min.js"></script>
|
|
<style>h1 {font-size: 34px;}
|
|
h1.title {font-size: 38px;}
|
|
h2 {font-size: 30px;}
|
|
h3 {font-size: 24px;}
|
|
h4 {font-size: 18px;}
|
|
h5 {font-size: 16px;}
|
|
h6 {font-size: 12px;}
|
|
code {color: inherit; background-color: rgba(0, 0, 0, 0.04);}
|
|
pre:not([class]) { background-color: white }</style>
|
|
<script src="site_libs/jqueryui-1.11.4/jquery-ui.min.js"></script>
|
|
<link href="site_libs/tocify-1.9.1/jquery.tocify.css" rel="stylesheet" />
|
|
<script src="site_libs/tocify-1.9.1/jquery.tocify.js"></script>
|
|
<script src="site_libs/navigation-1.1/tabsets.js"></script>
|
|
<link href="site_libs/highlightjs-9.12.0/default.css" rel="stylesheet" />
|
|
<script src="site_libs/highlightjs-9.12.0/highlight.js"></script>
|
|
<link href="site_libs/font-awesome-5.1.0/css/all.css" rel="stylesheet" />
|
|
<link href="site_libs/font-awesome-5.1.0/css/v4-shims.css" rel="stylesheet" />
|
|
<link rel='shortcut icon' type='image/x-icon' href='favicon.ico' />
|
|
|
|
<style type="text/css">
|
|
code{white-space: pre-wrap;}
|
|
span.smallcaps{font-variant: small-caps;}
|
|
span.underline{text-decoration: underline;}
|
|
div.column{display: inline-block; vertical-align: top; width: 50%;}
|
|
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
|
|
ul.task-list{list-style: none;}
|
|
</style>
|
|
|
|
<style type="text/css">code{white-space: pre;}</style>
|
|
<script type="text/javascript">
|
|
if (window.hljs) {
|
|
hljs.configure({languages: []});
|
|
hljs.initHighlightingOnLoad();
|
|
if (document.readyState && document.readyState === "complete") {
|
|
window.setTimeout(function() { hljs.initHighlighting(); }, 0);
|
|
}
|
|
}
|
|
</script>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<link rel="stylesheet" href="style.css" type="text/css" />
|
|
|
|
|
|
|
|
<style type = "text/css">
|
|
.main-container {
|
|
max-width: 940px;
|
|
margin-left: auto;
|
|
margin-right: auto;
|
|
}
|
|
img {
|
|
max-width:100%;
|
|
}
|
|
.tabbed-pane {
|
|
padding-top: 12px;
|
|
}
|
|
.html-widget {
|
|
margin-bottom: 20px;
|
|
}
|
|
button.code-folding-btn:focus {
|
|
outline: none;
|
|
}
|
|
summary {
|
|
display: list-item;
|
|
}
|
|
details > summary > p:only-child {
|
|
display: inline;
|
|
}
|
|
pre code {
|
|
padding: 0;
|
|
}
|
|
</style>
|
|
|
|
|
|
<style type="text/css">
|
|
.dropdown-submenu {
|
|
position: relative;
|
|
}
|
|
.dropdown-submenu>.dropdown-menu {
|
|
top: 0;
|
|
left: 100%;
|
|
margin-top: -6px;
|
|
margin-left: -1px;
|
|
border-radius: 0 6px 6px 6px;
|
|
}
|
|
.dropdown-submenu:hover>.dropdown-menu {
|
|
display: block;
|
|
}
|
|
.dropdown-submenu>a:after {
|
|
display: block;
|
|
content: " ";
|
|
float: right;
|
|
width: 0;
|
|
height: 0;
|
|
border-color: transparent;
|
|
border-style: solid;
|
|
border-width: 5px 0 5px 5px;
|
|
border-left-color: #cccccc;
|
|
margin-top: 5px;
|
|
margin-right: -10px;
|
|
}
|
|
.dropdown-submenu:hover>a:after {
|
|
border-left-color: #adb5bd;
|
|
}
|
|
.dropdown-submenu.pull-left {
|
|
float: none;
|
|
}
|
|
.dropdown-submenu.pull-left>.dropdown-menu {
|
|
left: -100%;
|
|
margin-left: 10px;
|
|
border-radius: 6px 0 6px 6px;
|
|
}
|
|
</style>
|
|
|
|
<script type="text/javascript">
|
|
// manage active state of menu based on current page
|
|
$(document).ready(function () {
|
|
// active menu anchor
|
|
href = window.location.pathname
|
|
href = href.substr(href.lastIndexOf('/') + 1)
|
|
if (href === "")
|
|
href = "index.html";
|
|
var menuAnchor = $('a[href="' + href + '"]');
|
|
|
|
// mark the anchor link active (and if it's in a dropdown, also mark that active)
|
|
var dropdown = menuAnchor.closest('li.dropdown');
|
|
if (window.bootstrap) { // Bootstrap 4+
|
|
menuAnchor.addClass('active');
|
|
dropdown.find('> .dropdown-toggle').addClass('active');
|
|
} else { // Bootstrap 3
|
|
menuAnchor.parent().addClass('active');
|
|
dropdown.addClass('active');
|
|
}
|
|
|
|
// Navbar adjustments
|
|
var navHeight = $(".navbar").first().height() + 15;
|
|
var style = document.createElement('style');
|
|
var pt = "padding-top: " + navHeight + "px; ";
|
|
var mt = "margin-top: -" + navHeight + "px; ";
|
|
var css = "";
|
|
// offset scroll position for anchor links (for fixed navbar)
|
|
for (var i = 1; i <= 6; i++) {
|
|
css += ".section h" + i + "{ " + pt + mt + "}\n";
|
|
}
|
|
style.innerHTML = "body {" + pt + "padding-bottom: 40px; }\n" + css;
|
|
document.head.appendChild(style);
|
|
});
|
|
</script>
|
|
|
|
<!-- tabsets -->
|
|
|
|
<style type="text/css">
|
|
.tabset-dropdown > .nav-tabs {
|
|
display: inline-table;
|
|
max-height: 500px;
|
|
min-height: 44px;
|
|
overflow-y: auto;
|
|
border: 1px solid #ddd;
|
|
border-radius: 4px;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs > li.active:before {
|
|
content: "";
|
|
font-family: 'Glyphicons Halflings';
|
|
display: inline-block;
|
|
padding: 10px;
|
|
border-right: 1px solid #ddd;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs.nav-tabs-open > li.active:before {
|
|
content: "";
|
|
border: none;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs.nav-tabs-open:before {
|
|
content: "";
|
|
font-family: 'Glyphicons Halflings';
|
|
display: inline-block;
|
|
padding: 10px;
|
|
border-right: 1px solid #ddd;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs > li.active {
|
|
display: block;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs > li > a,
|
|
.tabset-dropdown > .nav-tabs > li > a:focus,
|
|
.tabset-dropdown > .nav-tabs > li > a:hover {
|
|
border: none;
|
|
display: inline-block;
|
|
border-radius: 4px;
|
|
background-color: transparent;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs.nav-tabs-open > li {
|
|
display: block;
|
|
float: none;
|
|
}
|
|
|
|
.tabset-dropdown > .nav-tabs > li {
|
|
display: none;
|
|
}
|
|
</style>
|
|
|
|
<!-- code folding -->
|
|
|
|
|
|
|
|
<style type="text/css">
|
|
|
|
#TOC {
|
|
margin: 25px 0px 20px 0px;
|
|
}
|
|
@media (max-width: 768px) {
|
|
#TOC {
|
|
position: relative;
|
|
width: 100%;
|
|
}
|
|
}
|
|
|
|
@media print {
|
|
.toc-content {
|
|
/* see https://github.com/w3c/csswg-drafts/issues/4434 */
|
|
float: right;
|
|
}
|
|
}
|
|
|
|
.toc-content {
|
|
padding-left: 30px;
|
|
padding-right: 40px;
|
|
}
|
|
|
|
div.main-container {
|
|
max-width: 1200px;
|
|
}
|
|
|
|
div.tocify {
|
|
width: 20%;
|
|
max-width: 260px;
|
|
max-height: 85%;
|
|
}
|
|
|
|
@media (min-width: 768px) and (max-width: 991px) {
|
|
div.tocify {
|
|
width: 25%;
|
|
}
|
|
}
|
|
|
|
@media (max-width: 767px) {
|
|
div.tocify {
|
|
width: 100%;
|
|
max-width: none;
|
|
}
|
|
}
|
|
|
|
.tocify ul, .tocify li {
|
|
line-height: 20px;
|
|
}
|
|
|
|
.tocify-subheader .tocify-item {
|
|
font-size: 0.90em;
|
|
}
|
|
|
|
.tocify .list-group-item {
|
|
border-radius: 0px;
|
|
}
|
|
|
|
|
|
</style>
|
|
|
|
|
|
|
|
</head>
|
|
|
|
<body>
|
|
|
|
|
|
<div class="container-fluid main-container">
|
|
|
|
|
|
<!-- setup 3col/9col grid for toc_float and main content -->
|
|
<div class="row">
|
|
<div class="col-xs-12 col-sm-4 col-md-3">
|
|
<div id="TOC" class="tocify">
|
|
</div>
|
|
</div>
|
|
|
|
<div class="toc-content col-xs-12 col-sm-8 col-md-9">
|
|
|
|
|
|
|
|
|
|
<div class="navbar navbar-default navbar-fixed-top" role="navigation">
|
|
<div class="container">
|
|
<div class="navbar-header">
|
|
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-bs-toggle="collapse" data-target="#navbar" data-bs-target="#navbar">
|
|
<span class="icon-bar"></span>
|
|
<span class="icon-bar"></span>
|
|
<span class="icon-bar"></span>
|
|
</button>
|
|
<a class="navbar-brand" href="index.html"><div><img src="ohdsi16x16.png"></img> OMOP Common Data Model </div></a>
|
|
</div>
|
|
<div id="navbar" class="navbar-collapse collapse">
|
|
<ul class="nav navbar-nav">
|
|
<li>
|
|
<a href="index.html">
|
|
<span class="fa fa-house"></span>
|
|
|
|
</a>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-landmark"></span>
|
|
|
|
Background
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="background.html">Model Background</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdmRefreshProcess.html">CDM Refresh Process</a>
|
|
</li>
|
|
<li>
|
|
<a href="vocabulary.html">How the Vocabulary is Built</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-list-alt"></span>
|
|
|
|
Conventions
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="dataModelConventions.html">General Conventions</a>
|
|
</li>
|
|
<li>
|
|
<a href="ehrObsPeriods.html">Observation Periods for EHR Data</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdmPrivacy.html">Patient Privacy and OMOP</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-history"></span>
|
|
|
|
CDM Versions
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="cdm30.html">CDM v3.0</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdm60.html">CDM v6.0</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdm53.html">CDM v5.3</a>
|
|
</li>
|
|
<li class="dropdown-submenu">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">NEW CDM v5.4</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="cdm54.html">CDM v5.4</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdm54Changes.html">Changes from CDM v5.3</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-plus-square"></span>
|
|
|
|
CDM Proposals
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="cdmRequestProcess.html">How to Propose Changes to the CDM</a>
|
|
</li>
|
|
<li>
|
|
<a href="https://github.com/OHDSI/CommonDataModel/issues?q=is%3Aopen+is%3Aissue+label%3AProposal">Under Review</a>
|
|
</li>
|
|
<li class="dropdown-submenu">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">Accepted</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="https://github.com/OHDSI/CommonDataModel/issues/252">Region_concept_id</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-question"></span>
|
|
|
|
How to
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="download.html">Download the DDL</a>
|
|
</li>
|
|
<li>
|
|
<a href="cdmRPackage.html">Use the CDM R Package</a>
|
|
</li>
|
|
<li>
|
|
<a href="drug_dose.html">Calculate Drug Dose</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li class="dropdown">
|
|
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
|
|
<span class="fa fa-life-ring"></span>
|
|
|
|
Support
|
|
|
|
<span class="caret"></span>
|
|
</a>
|
|
<ul class="dropdown-menu" role="menu">
|
|
<li>
|
|
<a href="cdmDecisionTree.html">Help! My Data Doesn't Fit!</a>
|
|
</li>
|
|
<li>
|
|
<a href="faq.html">FAQ</a>
|
|
</li>
|
|
<li>
|
|
<a href="sqlScripts.html">SQL Scripts</a>
|
|
</li>
|
|
<li>
|
|
<a href="contribute.html">Ask a Question</a>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
</ul>
|
|
<ul class="nav navbar-nav navbar-right">
|
|
<li>
|
|
<a href="https://github.com/OHDSI/CommonDataModel">
|
|
<span class="fa fa-github"></span>
|
|
|
|
</a>
|
|
</li>
|
|
</ul>
|
|
</div><!--/.nav-collapse -->
|
|
</div><!--/.container -->
|
|
</div><!--/.navbar -->
|
|
|
|
<div id="header">
|
|
|
|
|
|
|
|
|
|
</div>
|
|
|
|
|
|
<div id="sql-scripts" class="section level1">
|
|
<h1><strong>SQL Scripts</strong></h1>
|
|
<p>These SQL scripts have been developed by the community to facilitate
|
|
the Extract, Transform, and Load (ETL) process from source data to the
|
|
OMOP CDM.</p>
|
|
<div id="vocabulary-mapping" class="section level2">
|
|
<h2><strong>Vocabulary Mapping</strong></h2>
|
|
<div id="source-to-standard" class="section level3">
|
|
<h3>Source to Standard</h3>
|
|
<p>This script will create a large table containing all source codes and
|
|
the Standard Concepts they map to. By creating this table ahead of time
|
|
and referencing it during ETL, you can save processing time than if you
|
|
were to map source codes on the fly. It is important to note that this
|
|
script will show ALL mappings, not just those to Standard, Valid
|
|
Concepts. Be sure to add a filter that will only grab the correct
|
|
Standard, Valid codes.</p>
|
|
<pre class="sql"><code>SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.concept_name AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID, c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, c.VALID_START_DATE AS SOURCE_VALID_START_DATE, c.VALID_END_DATE AS SOURCE_VALID_END_DATE, c.INVALID_REASON AS SOURCE_INVALID_REASON, c1.concept_id AS TARGET_CONCEPT_ID, c1.concept_name AS TARGET_CONCEPT_NAME, c1.VOCABULARY_ID AS TARGET_VOCABUALRY_ID, c1.domain_id AS TARGET_DOMAIN_ID, c1.concept_class_id AS TARGET_CONCEPT_CLASS_ID, c1.INVALID_REASON AS TARGET_INVALID_REASON, c1.standard_concept AS TARGET_STANDARD_CONCEPT
|
|
FROM CONCEPT C
|
|
JOIN CONCEPT_RELATIONSHIP CR
|
|
ON C.CONCEPT_ID = CR.CONCEPT_ID_1
|
|
AND CR.invalid_reason IS NULL
|
|
AND lower(cr.relationship_id) = 'maps to'
|
|
JOIN CONCEPT C1
|
|
ON CR.CONCEPT_ID_2 = C1.CONCEPT_ID
|
|
AND C1.INVALID_REASON IS NULL
|
|
UNION
|
|
SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, c1.VALID_START_DATE AS SOURCE_VALID_START_DATE, c1.VALID_END_DATE AS SOURCE_VALID_END_DATE, stcm.INVALID_REASON AS SOURCE_INVALID_REASON,target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, target_vocabulary_id, c2.domain_id AS TARGET_DOMAIN_ID, c2.concept_class_id AS TARGET_CONCEPT_CLASS_ID, c2.INVALID_REASON AS TARGET_INVALID_REASON, c2.standard_concept AS TARGET_STANDARD_CONCEPT
|
|
FROM source_to_concept_map stcm
|
|
LEFT OUTER JOIN CONCEPT c1
|
|
ON c1.concept_id = stcm.source_concept_id
|
|
LEFT OUTER JOIN CONCEPT c2
|
|
ON c2.CONCEPT_ID = stcm.target_concept_id
|
|
WHERE stcm.INVALID_REASON IS NULL
|
|
</code></pre>
|
|
</div>
|
|
<div id="source-to-source" class="section level3">
|
|
<h3>Source to Source</h3>
|
|
<p>This script will create a large table containing all source codes and
|
|
their referent Concepts. While you may be tempted to use the Source to
|
|
Standard query to find these, it is important to use this one instead to
|
|
populate *_SOURCE_CONCEPT_ID fields. Some source codes map to multiple
|
|
Standard Concepts though they have only one source Concept. By using the
|
|
Source to Standard query you may inadvertently create duplicate records
|
|
in your CDM instance.</p>
|
|
<pre class="sql"><code>SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.CONCEPT_NAME AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID, c.domain_id AS SOURCE_DOMAIN_ID, c.concept_class_id AS SOURCE_CONCEPT_CLASS_ID, c.VALID_START_DATE AS SOURCE_VALID_START_DATE, c.VALID_END_DATE AS SOURCE_VALID_END_DATE, c.invalid_reason AS SOURCE_INVALID_REASON, c.concept_ID as TARGET_CONCEPT_ID, c.concept_name AS TARGET_CONCEPT_NAME, c.vocabulary_id AS TARGET_VOCABULARY_ID, c.domain_id AS TARGET_DOMAIN_ID, c.concept_class_id AS TARGET_CONCEPT_CLASS_ID, c.INVALID_REASON AS TARGET_INVALID_REASON, c.STANDARD_CONCEPT AS TARGET_STANDARD_CONCEPT
|
|
FROM CONCEPT c
|
|
UNION
|
|
SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, c1.VALID_START_DATE AS SOURCE_VALID_START_DATE, c1.VALID_END_DATE AS SOURCE_VALID_END_DATE,stcm.INVALID_REASON AS SOURCE_INVALID_REASON, target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, target_vocabulary_id, c2.domain_id AS TARGET_DOMAIN_ID, c2.concept_class_id AS TARGET_CONCEPT_CLASS_ID, c2.INVALID_REASON AS TARGET_INVALID_REASON, c2.standard_concept AS TARGET_STANDARD_CONCEPT
|
|
FROM source_to_concept_map stcm
|
|
LEFT OUTER JOIN CONCEPT c1
|
|
ON c1.concept_id = stcm.source_concept_id
|
|
LEFT OUTER JOIN CONCEPT c2
|
|
ON c2.CONCEPT_ID = stcm.target_concept_id
|
|
WHERE stcm.INVALID_REASON IS NULL</code></pre>
|
|
</div>
|
|
</div>
|
|
<div id="era-tables" class="section level2">
|
|
<h2><strong>Era Tables</strong></h2>
|
|
<div id="condition-eras" class="section level3">
|
|
<h3>Condition Eras</h3>
|
|
<p>This script will insert values into the CONDITION_ERA table given
|
|
that the CONDITION_OCCURRENCE table is populated. It will string
|
|
together condition records that have <= 30 days between them into
|
|
eras during which the Person is assumed to have the given condition.
|
|
<strong>NOTE</strong> This query only works with 5.3 and below.</p>
|
|
<pre class="sql"><code>/****************************************************
|
|
OHDSI-SQL File Instructions
|
|
-----------------------------
|
|
1. Set parameter name of schema that contains CDMv4 instance
|
|
(@SOURCE_CDMV4, @SOURCE_CDMV4_SCHEMA)
|
|
2. Set parameter name of schema that contains CDMv5 instance
|
|
(@TARGET_CDMV5, @TARGET_CDMV5_SCHEMA)
|
|
3. Run this script through SqlRender to produce a script that will work in your
|
|
source dialect. SqlRender can be found here: https://github.com/OHDSI/SqlRender
|
|
4. Run the script produced by SQL Render on your target RDBDMS.
|
|
<RDBMS> File Instructions
|
|
-------------------------
|
|
1. This script will hold a number of placeholders for your CDM V4 and CDMV5
|
|
database/schema. In order to make this file work in your environment, you
|
|
should plan to do a global "FIND AND REPLACE" on this file to fill in the
|
|
file with values that pertain to your environment. The following are the
|
|
tokens you should use when doing your "FIND AND REPLACE" operation:
|
|
|
|
[CDM]
|
|
[CDM].[CDMSCHEMA]
|
|
|
|
*********************************************************************************/
|
|
/* SCRIPT PARAMETERS */
|
|
|
|
|
|
{DEFAULT @TARGET_CDMV5 = '[CDM]' } -- The target CDMv5 database name
|
|
{DEFAULT @TARGET_CDMV5_SCHEMA = '[CDM].[CDMSCHEMA]' } -- the target CDMv5 database plus schema
|
|
|
|
USE @TARGET_CDMV5;
|
|
|
|
|
|
|
|
/****
|
|
CONDITION ERA
|
|
Note: Eras derived from CONDITION_OCCURRENCE table, using 30d gap
|
|
****/
|
|
IF OBJECT_ID('tempdb..#condition_era_phase_1', 'U') IS NOT NULL
|
|
DROP TABLE #condition_era_phase_1;
|
|
|
|
/* / */
|
|
|
|
IF OBJECT_ID('tempdb..#cteConditionTarget', 'U') IS NOT NULL
|
|
DROP TABLE #cteConditionTarget;
|
|
|
|
/* / */
|
|
|
|
-- create base eras from the concepts found in condition_occurrence
|
|
SELECT co.PERSON_ID
|
|
,co.condition_concept_id
|
|
,co.CONDITION_START_DATE
|
|
,COALESCE(co.CONDITION_END_DATE, DATEADD(day, 1, CONDITION_START_DATE)) AS CONDITION_END_DATE
|
|
INTO #cteConditionTarget
|
|
FROM @TARGET_CDMV5_SCHEMA.CONDITION_OCCURRENCE co;
|
|
|
|
/* / */
|
|
|
|
IF OBJECT_ID('tempdb..#cteCondEndDates', 'U') IS NOT NULL
|
|
DROP TABLE #cteCondEndDates;
|
|
|
|
/* / */
|
|
|
|
SELECT PERSON_ID
|
|
,CONDITION_CONCEPT_ID
|
|
,DATEADD(day, - 30, EVENT_DATE) AS END_DATE -- unpad the end date
|
|
INTO #cteCondEndDates
|
|
FROM (
|
|
SELECT E1.PERSON_ID
|
|
,E1.CONDITION_CONCEPT_ID
|
|
,E1.EVENT_DATE
|
|
,COALESCE(E1.START_ORDINAL, MAX(E2.START_ORDINAL)) START_ORDINAL
|
|
,E1.OVERALL_ORD
|
|
FROM (
|
|
SELECT PERSON_ID
|
|
,CONDITION_CONCEPT_ID
|
|
,EVENT_DATE
|
|
,EVENT_TYPE
|
|
,START_ORDINAL
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY PERSON_ID
|
|
,CONDITION_CONCEPT_ID ORDER BY EVENT_DATE
|
|
,EVENT_TYPE
|
|
) AS OVERALL_ORD -- this re-numbers the inner UNION so all rows are numbered ordered by the event date
|
|
FROM (
|
|
-- select the start dates, assigning a row number to each
|
|
SELECT PERSON_ID
|
|
,CONDITION_CONCEPT_ID
|
|
,CONDITION_START_DATE AS EVENT_DATE
|
|
,- 1 AS EVENT_TYPE
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY PERSON_ID
|
|
,CONDITION_CONCEPT_ID ORDER BY CONDITION_START_DATE
|
|
) AS START_ORDINAL
|
|
FROM #cteConditionTarget
|
|
|
|
UNION ALL
|
|
|
|
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
|
|
SELECT PERSON_ID
|
|
,CONDITION_CONCEPT_ID
|
|
,DATEADD(day, 30, CONDITION_END_DATE)
|
|
,1 AS EVENT_TYPE
|
|
,NULL
|
|
FROM #cteConditionTarget
|
|
) RAWDATA
|
|
) E1
|
|
INNER JOIN (
|
|
SELECT PERSON_ID
|
|
,CONDITION_CONCEPT_ID
|
|
,CONDITION_START_DATE AS EVENT_DATE
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY PERSON_ID
|
|
,CONDITION_CONCEPT_ID ORDER BY CONDITION_START_DATE
|
|
) AS START_ORDINAL
|
|
FROM #cteConditionTarget
|
|
) E2 ON E1.PERSON_ID = E2.PERSON_ID
|
|
AND E1.CONDITION_CONCEPT_ID = E2.CONDITION_CONCEPT_ID
|
|
AND E2.EVENT_DATE <= E1.EVENT_DATE
|
|
GROUP BY E1.PERSON_ID
|
|
,E1.CONDITION_CONCEPT_ID
|
|
,E1.EVENT_DATE
|
|
,E1.START_ORDINAL
|
|
,E1.OVERALL_ORD
|
|
) E
|
|
WHERE (2 * E.START_ORDINAL) - E.OVERALL_ORD = 0;
|
|
|
|
/* / */
|
|
|
|
IF OBJECT_ID('tempdb..#cteConditionEnds', 'U') IS NOT NULL
|
|
DROP TABLE #cteConditionEnds;
|
|
|
|
/* / */
|
|
|
|
SELECT c.PERSON_ID
|
|
,c.CONDITION_CONCEPT_ID
|
|
,c.CONDITION_START_DATE
|
|
,MIN(e.END_DATE) AS ERA_END_DATE
|
|
INTO #cteConditionEnds
|
|
FROM #cteConditionTarget c
|
|
INNER JOIN #cteCondEndDates e ON c.PERSON_ID = e.PERSON_ID
|
|
AND c.CONDITION_CONCEPT_ID = e.CONDITION_CONCEPT_ID
|
|
AND e.END_DATE >= c.CONDITION_START_DATE
|
|
GROUP BY c.PERSON_ID
|
|
,c.CONDITION_CONCEPT_ID
|
|
,c.CONDITION_START_DATE;
|
|
|
|
/* / */
|
|
|
|
INSERT INTO @TARGET_CDMV5_SCHEMA.condition_era (
|
|
condition_era_id
|
|
,person_id
|
|
,condition_concept_id
|
|
,condition_era_start_date
|
|
,condition_era_end_date
|
|
,condition_occurrence_count
|
|
)
|
|
SELECT row_number() OVER (
|
|
ORDER BY person_id
|
|
) AS condition_era_id
|
|
,person_id
|
|
,CONDITION_CONCEPT_ID
|
|
,min(CONDITION_START_DATE) AS CONDITION_ERA_START_DATE
|
|
,ERA_END_DATE AS CONDITION_ERA_END_DATE
|
|
,COUNT(*) AS CONDITION_OCCURRENCE_COUNT
|
|
FROM #cteConditionEnds
|
|
GROUP BY person_id
|
|
,CONDITION_CONCEPT_ID
|
|
,ERA_END_DATE;</code></pre>
|
|
</div>
|
|
<div id="drug-eras" class="section level3">
|
|
<h3>Drug Eras</h3>
|
|
<p>If the DRUG_EXPOSURE table is populated this script will string
|
|
together periods of time that a person is exposed to an active drug
|
|
ingredient, allowing for 30 gaps in between. It will then insert the
|
|
resulting era records into the DRUG_ERA table. <strong>NOTE</strong>
|
|
This query only works with the <strong>version 5</strong> series and
|
|
below.</p>
|
|
<pre class="sql"><code>
|
|
-- Code taken from:
|
|
-- https://github.com/OHDSI/ETL-CMS/blob/master/SQL/create_CDMv5_drug_era_non_stockpile.sql
|
|
|
|
|
|
if object_id('tempdb..#tmp_de', 'U') is not null drop table #tmp_de;
|
|
|
|
WITH
|
|
ctePreDrugTarget(drug_exposure_id, person_id, ingredient_concept_id, drug_exposure_start_date, days_supply, drug_exposure_end_date) AS
|
|
(-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date
|
|
SELECT
|
|
d.drug_exposure_id
|
|
, d.person_id
|
|
, c.concept_id AS ingredient_concept_id
|
|
, d.drug_exposure_start_date AS drug_exposure_start_date
|
|
, d.days_supply AS days_supply
|
|
, COALESCE(
|
|
---NULLIF returns NULL if both values are the same, otherwise it returns the first parameter
|
|
NULLIF(drug_exposure_end_date, NULL),
|
|
---If drug_exposure_end_date != NULL, return drug_exposure_end_date, otherwise go to next case
|
|
NULLIF(dateadd(day,days_supply,drug_exposure_start_date), drug_exposure_start_date),
|
|
---If days_supply != NULL or 0, return drug_exposure_start_date + days_supply, otherwise go to next case
|
|
dateadd(day,1,drug_exposure_start_date)
|
|
---Add 1 day to the drug_exposure_start_date since there is no end_date or INTERVAL for the days_supply
|
|
) AS drug_exposure_end_date
|
|
FROM @cdm_schema.drug_exposure d
|
|
JOIN @cdm_schema.concept_ancestor ca ON ca.descendant_concept_id = d.drug_concept_id
|
|
JOIN @cdm_schema.concept c ON ca.ancestor_concept_id = c.concept_id
|
|
WHERE c.vocabulary_id = 'RxNorm' ---8 selects RxNorm from the vocabulary_id
|
|
AND c.concept_class_id = 'Ingredient'
|
|
AND d.drug_concept_id != 0 ---Our unmapped drug_concept_id's are set to 0, so we don't want different drugs wrapped up in the same era
|
|
AND coalesce(d.days_supply,0) >= 0 ---We have cases where days_supply is negative, and this can set the end_date before the start_date, which we don't want. So we're just looking over those rows. This is a data-quality issue.
|
|
)
|
|
|
|
, cteSubExposureEndDates (person_id, ingredient_concept_id, end_date) AS --- A preliminary sorting that groups all of the overlapping exposures into one exposure so that we don't double-count non-gap-days
|
|
(
|
|
SELECT person_id, ingredient_concept_id, event_date AS end_date
|
|
FROM
|
|
(
|
|
SELECT person_id, ingredient_concept_id, event_date, event_type,
|
|
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY event_date, event_type ROWS unbounded preceding) AS start_ordinal,
|
|
-- this pulls the current START down from the prior rows so that the NULLs
|
|
-- from the END DATES will contain a value we can compare with
|
|
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY event_date, event_type) AS overall_ord
|
|
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
|
|
FROM (
|
|
-- select the start dates, assigning a row number to each
|
|
SELECT person_id, ingredient_concept_id, drug_exposure_start_date AS event_date,
|
|
-1 AS event_type,
|
|
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY drug_exposure_start_date) AS start_ordinal
|
|
FROM ctePreDrugTarget
|
|
|
|
UNION ALL
|
|
|
|
SELECT person_id, ingredient_concept_id, drug_exposure_end_date, 1 AS event_type, NULL
|
|
FROM ctePreDrugTarget
|
|
) RAWDATA
|
|
) e
|
|
WHERE (2 * e.start_ordinal) - e.overall_ord = 0
|
|
)
|
|
|
|
, cteDrugExposureEnds (person_id, drug_concept_id, drug_exposure_start_date, drug_sub_exposure_end_date) AS
|
|
(
|
|
SELECT
|
|
dt.person_id
|
|
, dt.ingredient_concept_id
|
|
, dt.drug_exposure_start_date
|
|
, MIN(e.end_date) AS drug_sub_exposure_end_date
|
|
FROM ctePreDrugTarget dt
|
|
JOIN cteSubExposureEndDates e ON dt.person_id = e.person_id AND dt.ingredient_concept_id = e.ingredient_concept_id AND e.end_date >= dt.drug_exposure_start_date
|
|
GROUP BY
|
|
dt.drug_exposure_id
|
|
, dt.person_id
|
|
, dt.ingredient_concept_id
|
|
, dt.drug_exposure_start_date
|
|
)
|
|
--------------------------------------------------------------------------------------------------------------
|
|
, cteSubExposures(row_number, person_id, drug_concept_id, drug_sub_exposure_start_date, drug_sub_exposure_end_date, drug_exposure_count) AS
|
|
(
|
|
SELECT ROW_NUMBER() OVER (PARTITION BY person_id, drug_concept_id, drug_sub_exposure_end_date ORDER BY person_id)
|
|
, person_id, drug_concept_id, MIN(drug_exposure_start_date) AS drug_sub_exposure_start_date, drug_sub_exposure_end_date, COUNT(*) AS drug_exposure_count
|
|
FROM cteDrugExposureEnds
|
|
GROUP BY person_id, drug_concept_id, drug_sub_exposure_end_date
|
|
--ORDER BY person_id, drug_concept_id
|
|
)
|
|
--------------------------------------------------------------------------------------------------------------
|
|
/*Everything above grouped exposures into sub_exposures if there was overlap between exposures.
|
|
*So there was no persistence window. Now we can add the persistence window to calculate eras.
|
|
*/
|
|
--------------------------------------------------------------------------------------------------------------
|
|
, cteFinalTarget(row_number, person_id, ingredient_concept_id, drug_sub_exposure_start_date, drug_sub_exposure_end_date, drug_exposure_count, days_exposed) AS
|
|
(
|
|
SELECT row_number, person_id, drug_concept_id, drug_sub_exposure_start_date, drug_sub_exposure_end_date, drug_exposure_count
|
|
, datediff(day,drug_sub_exposure_start_date,drug_sub_exposure_end_date) AS days_exposed
|
|
FROM cteSubExposures
|
|
)
|
|
--------------------------------------------------------------------------------------------------------------
|
|
, cteEndDates (person_id, ingredient_concept_id, end_date) AS -- the magic
|
|
(
|
|
SELECT person_id, ingredient_concept_id, dateadd(day,-30,event_date) AS end_date -- unpad the end date
|
|
FROM
|
|
(
|
|
SELECT person_id, ingredient_concept_id, event_date, event_type,
|
|
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal,
|
|
-- this pulls the current START down from the prior rows so that the NULLs
|
|
-- from the END DATES will contain a value we can compare with
|
|
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY event_date, event_type) AS overall_ord
|
|
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
|
|
FROM (
|
|
-- select the start dates, assigning a row number to each
|
|
SELECT person_id, ingredient_concept_id, drug_sub_exposure_start_date AS event_date,
|
|
-1 AS event_type,
|
|
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
|
|
ORDER BY drug_sub_exposure_start_date) AS start_ordinal
|
|
FROM cteFinalTarget
|
|
|
|
UNION ALL
|
|
|
|
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
|
|
SELECT person_id, ingredient_concept_id, dateadd(day,30,drug_sub_exposure_end_date), 1 AS event_type, NULL
|
|
FROM cteFinalTarget
|
|
) RAWDATA
|
|
) e
|
|
WHERE (2 * e.start_ordinal) - e.overall_ord = 0
|
|
|
|
)
|
|
, cteDrugEraEnds (person_id, drug_concept_id, drug_sub_exposure_start_date, drug_era_end_date, drug_exposure_count, days_exposed) AS
|
|
(
|
|
SELECT
|
|
ft.person_id
|
|
, ft.ingredient_concept_id
|
|
, ft.drug_sub_exposure_start_date
|
|
, MIN(e.end_date) AS era_end_date
|
|
, drug_exposure_count
|
|
, days_exposed
|
|
FROM cteFinalTarget ft
|
|
JOIN cteEndDates e ON ft.person_id = e.person_id AND ft.ingredient_concept_id = e.ingredient_concept_id AND e.end_date >= ft.drug_sub_exposure_start_date
|
|
GROUP BY
|
|
ft.person_id
|
|
, ft.ingredient_concept_id
|
|
, ft.drug_sub_exposure_start_date
|
|
, drug_exposure_count
|
|
, days_exposed
|
|
)
|
|
SELECT
|
|
row_number()over(order by person_id) drug_era_id
|
|
, person_id
|
|
, drug_concept_id
|
|
, MIN(drug_sub_exposure_start_date) AS drug_era_start_date
|
|
, drug_era_end_date
|
|
, SUM(drug_exposure_count) AS drug_exposure_count
|
|
, datediff(day,MIN(drug_sub_exposure_start_date),drug_era_end_date)-SUM(days_exposed) as gap_days
|
|
INTO #tmp_de
|
|
FROM cteDrugEraEnds dee
|
|
GROUP BY person_id, drug_concept_id, drug_era_end_date;
|
|
|
|
INSERT INTO @cdm_schema.drug_era(drug_era_id,person_id, drug_concept_id, drug_era_start_date, drug_era_end_date, drug_exposure_count, gap_days)
|
|
SELECT * FROM #tmp_de;</code></pre>
|
|
</div>
|
|
</div>
|
|
<div id="example-etl-script" class="section level2">
|
|
<h2><strong>Example ETL Script</strong></h2>
|
|
<div id="cdm_source-table" class="section level3">
|
|
<h3>CDM_SOURCE Table</h3>
|
|
<p>The script below is an example for how to fill in the CDM_SOURCE
|
|
table. This table is required for the <a
|
|
href="https://github.com/OHDSI/DataQualityDashboard">Data Quality
|
|
Dashboard</a> package to run. This was taken from the <a
|
|
href="https://github.com/OHDSI/ETL-Synthea">ETL-Synthea</a> package, a
|
|
good example on how to write a complete extract-transform-load script
|
|
from start to finish, including vocabulary import.</p>
|
|
<pre class="sql"><code>insert into @cdm_schema.cdm_source (
|
|
cdm_source_name,
|
|
cdm_source_abbreviation,
|
|
cdm_holder,
|
|
source_description,
|
|
source_documentation_reference,
|
|
cdm_etl_reference,
|
|
source_release_date,
|
|
cdm_release_date,
|
|
cdm_version,
|
|
vocabulary_version
|
|
)
|
|
select
|
|
'Synthea synthetic health database',
|
|
'Synthea',
|
|
'OHDSI Community',
|
|
'SyntheaTM is a Synthetic Patient Population Simulator. The goal is to output synthetic, realistic (but not real), patient data and associated health records in a variety of formats.',
|
|
'https://synthetichealth.github.io/synthea/',
|
|
'https://github.com/OHDSI/ETL-Synthea',
|
|
getdate(), -- NB: Set this value to the day the source data was pulled
|
|
getdate(),
|
|
'v5.3',
|
|
vocabulary_version
|
|
from @cdm_schema.vocabulary
|
|
where vocabulary_id = 'None';</code></pre>
|
|
</div>
|
|
</div>
|
|
<div id="bonus-queries" class="section level2">
|
|
<h2><strong>Bonus Queries</strong></h2>
|
|
<div id="visit-concept-roll-up" class="section level3">
|
|
<h3>Visit Concept Roll-up</h3>
|
|
<p>The query below will utilize the Visit Concept hierarcy to find the
|
|
highest-level ancestors. In the case that both the VISIT_OCCURRENCE and
|
|
VISIT_DETAIL tables are populated, it is good practice (though not
|
|
required) to use the highest-level ancestors as the VISIT_CONCEPT_IDs in
|
|
the VISIT_OCCURRENCE table and their children as the
|
|
VISIT_DETAIL_CONCEPT_IDs in the VISIT_DETAIL table. This relationship
|
|
between the VISIT_OCCURRENCE and VISIT_DETAIL tables allow for
|
|
standardized Visit logic to be written, building Visits from Visit
|
|
Details. For more information on how this can be done, please see the <a
|
|
href="https://ohdsi.github.io/ETL-LambdaBuilder/Optum%20Clinformatics/Optum_visit_occurrence.html">Optum
|
|
Extended ETL documentation</a>.</p>
|
|
<pre class="sql"><code>SELECT concept_id, concept_name
|
|
FROM concept
|
|
LEFT JOIN concept_ancestor
|
|
ON concept_id=descendant_concept_id
|
|
AND ancestor_concept_id!=descendant_concept_id
|
|
WHERE domain_id='Visit'
|
|
AND standard_concept='S'
|
|
AND ancestor_concept_id IS NULL</code></pre>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
|
|
|
|
</div>
|
|
</div>
|
|
|
|
</div>
|
|
|
|
<script>
|
|
|
|
// add bootstrap table styles to pandoc tables
|
|
function bootstrapStylePandocTables() {
|
|
$('tr.odd').parent('tbody').parent('table').addClass('table table-condensed');
|
|
}
|
|
$(document).ready(function () {
|
|
bootstrapStylePandocTables();
|
|
});
|
|
|
|
|
|
</script>
|
|
|
|
<!-- tabsets -->
|
|
|
|
<script>
|
|
$(document).ready(function () {
|
|
window.buildTabsets("TOC");
|
|
});
|
|
|
|
$(document).ready(function () {
|
|
$('.tabset-dropdown > .nav-tabs > li').click(function () {
|
|
$(this).parent().toggleClass('nav-tabs-open');
|
|
});
|
|
});
|
|
</script>
|
|
|
|
<!-- code folding -->
|
|
|
|
<script>
|
|
$(document).ready(function () {
|
|
|
|
// temporarily add toc-ignore selector to headers for the consistency with Pandoc
|
|
$('.unlisted.unnumbered').addClass('toc-ignore')
|
|
|
|
// move toc-ignore selectors from section div to header
|
|
$('div.section.toc-ignore')
|
|
.removeClass('toc-ignore')
|
|
.children('h1,h2,h3,h4,h5').addClass('toc-ignore');
|
|
|
|
// establish options
|
|
var options = {
|
|
selectors: "h1,h2,h3,h4,h5",
|
|
theme: "bootstrap3",
|
|
context: '.toc-content',
|
|
hashGenerator: function (text) {
|
|
return text.replace(/[.\\/?&!#<>]/g, '').replace(/\s/g, '_');
|
|
},
|
|
ignoreSelector: ".toc-ignore",
|
|
scrollTo: 0
|
|
};
|
|
options.showAndHide = true;
|
|
options.smoothScroll = true;
|
|
|
|
// tocify
|
|
var toc = $("#TOC").tocify(options).data("toc-tocify");
|
|
});
|
|
</script>
|
|
|
|
<!-- dynamically load mathjax for compatibility with self-contained -->
|
|
<script>
|
|
(function () {
|
|
var script = document.createElement("script");
|
|
script.type = "text/javascript";
|
|
script.src = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
|
|
document.getElementsByTagName("head")[0].appendChild(script);
|
|
})();
|
|
</script>
|
|
|
|
</body>
|
|
</html>
|