function BuildSQLString($s) {
//echo "<pre>";
//print_r($s);
//print_r($resultorder);
//echo "</pre>";
/* escape all the variables and put them back into meaningful variable names */
foreach ($s as $key => $value) {
if (is_scalar($value)) {
$$key = trim(mysql_real_escape_string($s[$key]));
}
else {
$$key = $s[$key];
}
}
/* make modality lower case to conform with table names... MySQL table names are case sensitive when using the 'show tables' command */
$s_studymodality = strtolower($s_studymodality);
/* check if modality_series table actually exists */
$sqlstring = "show tables from " . $GLOBALS['cfg']['mysqldatabase'] . " like '$s_studymodality" . "_series'";
$result = MySQLQuery($sqlstring,__FILE__,__LINE__);
if (mysql_num_rows($result) < 1) {
?>
<?php
echo $s_studymodality;
?>
_series table does not exist. Unable to query information about <?php
echo $s_studymodality;
?>
type series
<?
return "";
}
/* determine which fields have criteria, build the where clause */
if (($s_subjectuid != "") || ($s_subjectgroupid != "")) {
if ($s_subjectgroupid != "") {
$ids = GetIDListFromGroup($s_subjectgroupid);
$sqlwhere .= " and `subjects`.subject_id in (" . $ids . ")";
}
else {
if (preg_match('/[\^\,;\-\'\s]/', $s_subjectuid) == 0) {
$sqlwhere .= " and `subjects`.uid = '$s_subjectuid'";
}
else {
$sqlwhere .= " and `subjects`.uid in (" . MakeSQLList($s_subjectuid) . ")";
}
}
}
if ($s_subjectaltuid != "") {
if (preg_match('/[\^\,;\-\'\s]/', $s_subjectaltuid) == 0) {
$sqlwhere .= "and `subject_altuid`.altuid like '%$s_subjectaltuid%'";
}
else {
$sqlwhere .= "and `subject_altuid`.altuid in (" . MakeSQLList($s_subjectaltuid) . ")";
}
}
if ($s_subjectname != "") { $sqlwhere .= " and `subjects`.name like '%$s_subjectname%'"; }
if ($s_subjectdobstart != "") { $sqlwhere .= " and `subjects`.birthdate >= '$s_subjectdobstart'"; }
if ($s_subjectdobend != "") { $sqlwhere .= " and `subjects`.birthdate <= '$s_subjectdobend'"; }
if ($s_subjectgender != "") { $sqlwhere .= " and `subjects`.gender = '$s_subjectgender'"; }
if ($s_projectid != "all") { $sqlwhere .= " and `projects`.project_id = $s_projectid"; }
if ($s_enrollsubgroup != "") { $sqlwhere .= " and `enrollment`.enroll_subgroup = '$s_enrollsubgroup'"; }
if ($s_studygroupid != "") {
$studyids = GetIDListFromGroup($s_studygroupid);
$sqlwhere .= " and `studies`.study_id in (" . $studyids . ")";
}
if ($s_studyinstitution != "") { $sqlwhere .= " and `studies`.study_institution like '%$s_studyinstitution%'"; }
if ($s_studyequipment != "") { $sqlwhere .= " and `studies`.study_site like '%$s_studyequipment%'"; }
if ($s_studyaltscanid != "") {
if (preg_match('/[\^\,;\-\'\s]/', $s_studyaltscanid) == 0) {
$sqlwhere .= " and `studies`.study_alternateid like '%$s_studyaltscanid%'";
}
else {
$sqlwhere .= " and `studies`.study_alternateid in (" . MakeSQLList($s_studyaltscanid) . ")";
}
}
if ($s_studydatestart != "") { $sqlwhere .= " and `studies`.study_datetime >= '$s_studydatestart 00:00:00'"; }
if ($s_studydateend != "") { $sqlwhere .= " and `studies`.study_datetime <= '$s_studydateend 23:59:59'"; }
if ($s_studydesc != "") { $sqlwhere .= " and `studies`.study_desc like '%$s_studydesc%'"; }
if ($s_studyphysician != "") { $sqlwhere .= " and `studies`.study_performingphysician like '%$s_studyphysician%'"; }
if ($s_studyoperator != "") { $sqlwhere .= " and `studies`.study_operator like '%$s_studyoperator%'"; }
if ($s_studytype != "") { $sqlwhere .= " and `studies`.study_type like '%$s_studytype%'"; }
if ($s_seriesgroupid != "") {
$seriesids = GetIDListFromGroup($s_seriesgroupid);
$sqlwhere .= " and `$s_studymodality" . "_series`.$s_studymodality" . "series_id in (" . $seriesids . ")";
}
if ($s_seriesdesc != "") {
$sqlwhere .= " and (";
/* if it contains a comma, the search will be OR */
//if (strpos($s_seriesdesc,',') !== false) {
$seriesdescs = explode(',',$s_seriesdesc);
$wheres = array();
foreach ($seriesdescs as $seriesdesc) {
if ($s_usealtseriesdesc) {
$wheres[] = "(`$s_studymodality" . "_series`.series_altdesc like '%" . trim($seriesdesc) . "%')";
}
else {
$wheres[] = "(`$s_studymodality" . "_series`.series_desc like '%" . trim($seriesdesc) . "%')";
}
}
$sqlwhere .= implode(" or ", $wheres);
//}
//else {
/* otherwise the search is an AND */
//$seriesdescs = explode(';',$s_seriesdesc);
//$wheres = array();
//foreach ($seriesdescs as $seriesdesc) {
// $wheres[] = "(`$s_studymodality" . "_series`.series_desc like '%" . trim($seriesdesc) . "%')";
//}
//$sqlwhere .= implode(" and ", $wheres);
//}
$sqlwhere .= ")";
}
if ($s_seriessequence != "") { $sqlwhere .= " and `$s_studymodality" . "_series`.series_sequencename like '%$s_seriessequence%'"; }
if ($s_seriesimagetype != "") {
$sqlwhere .= " and (";
$seriesimagetypes = explode(',',$s_seriesimagetype);
$wheres = array();
foreach ($seriesimagetypes as $seriesimagetype) {
if (strpos($seriesimagetype,'*') !== false) {
$seriesimagetype = str_replace('*','%',$seriesimagetype);
$wheres[] = "(`$s_studymodality" . "_series`.image_type like '" . trim($seriesimagetype) . "')";
}
else {
$wheres[] = "(`$s_studymodality" . "_series`.image_type = '" . trim($seriesimagetype) . "')";
}
}
$sqlwhere .= implode(" or ", $wheres);
$sqlwhere .= ")";
//$sqlwhere .= " and `$s_studymodality" . "_series`.image_type like '%$s_seriesimagetype%'";
}
if ($s_seriesimagecomments != "") { $sqlwhere .= " and `$s_studymodality" . "_series`.image_comments like '%$s_seriesimagecomments%'"; }
if ($s_seriestr != "") { $sqlwhere .= " and `$s_studymodality" . "_series`.series_tr = '$s_seriestr'"; }
if ($s_seriesnum != "") {
if (substr($s_seriesnum,0,2) == '>=') {
$val = substr($s_seriesnum,2);
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num >= '$val'";
}
elseif (substr($s_seriesnum,0,2) == '<=') {
$val = substr($s_seriesnum,2);
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num <= '$val'";
}
elseif (substr($s_seriesnum,0,1) == '>') {
$val = substr($s_seriesnum,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num > '$val'";
}
elseif (substr($s_seriesnum,0,1) == '<') {
$val = substr($s_seriesnum,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num < '$val'";
}
elseif (substr($s_seriesnum,0,1) == '~') {
$val = substr($s_seriesnum,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num <> '$val'";
}
else {
$sqlwhere .= " and `$s_studymodality" . "_series`.series_num = '$s_seriesnum'";
}
//$sqlwhere .= " and `$s_studymodality" . "_series`.series_num = '$s_seriesnum'";
}
if ($s_seriesnumfiles != "") {
if (substr($s_seriesnumfiles,0,2) == '>=') {
$val = substr($s_seriesnumfiles,2);
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles >= '$val'";
}
elseif (substr($s_seriesnumfiles,0,2) == '<=') {
$val = substr($s_seriesnumfiles,2);
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles <= '$val'";
}
elseif (substr($s_seriesnumfiles,0,1) == '>') {
$val = substr($s_seriesnumfiles,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles > '$val'";
}
elseif (substr($s_seriesnumfiles,0,1) == '<') {
$val = substr($s_seriesnumfiles,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles < '$val'";
}
elseif (substr($s_seriesnumfiles,0,1) == '~') {
$val = substr($s_seriesnumfiles,1);
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles <> '$val'";
}
else {
$sqlwhere .= " and `$s_studymodality" . "_series`.numfiles = '$s_seriesnumfiles'";
}
}
if ($s_measuresearch != "") {
$tmpsqlstring = "select measurename_id from measurenames where measure_name = '$s_measures'";
$tmpresult = MySQLQuery($tmpsqlstring,__FILE__,__LINE__);
$tmprow = mysql_fetch_array($tmpresult, MYSQL_ASSOC);
$measurenameid = $tmprow['measurename_id'];
if (is_numeric($measurevalue)) {
$valtype = "measure_valuenum";
}
else {
$valtype = "measure_valuestring";
}
switch ($s_measurecriteria) {
case "contains": $val = " like '%$s_measurevalue%'"; break;
case "eq": $val = " = '$s_measurevalue'"; break;
case "gt": $val = " > '$s_measurevalue'"; break;
case "lt": $val = " < '$s_measurevalue'"; break;
}
$measuresearch = ParseMeasureSearchList($s_measuresearch);
if ($measuresearch != "") {
$sqlwhere .= " and " . $measuresearch;
}
}
if ($s_formvalue[0] != "") {
/* get the formfield datatype to make sure we compare against the correct assessment_data value */
$tmpsqlstring = "select * from assessment_formfields where formfield_id = $s_formfieldid[0]";
$tmpresult = MySQLQuery($tmpsqlstring,__FILE__,__LINE__);
$tmprow = mysql_fetch_array($tmpresult, MYSQL_ASSOC);
$datatype = $tmprow['formfield_datatype'];
switch ($datatype) {
case "string": $valtype = "value_string"; break;
case "number": $valtype = "value_number"; break;
case "multichoice": $valtype = "value_text"; break;
case "singlechoice": $valtype = "value_text"; break;
case "text": $valtype = "value_text"; break;
case "date": $valtype = "value_binary"; break;
case "binary": $valtype = "value_binary"; break;
case "header": $valtype = "value_text"; break;
}
switch ($s_formcriteria[0]) {
case "contains": $val = " like '%$s_formvalue[0]%'"; break;
case "eq": $val = " = '$s_formvalue[0]'"; break;
case "gt": $val = " > '$s_formvalue[0]'"; break;
case "lt": $val = " < '$s_formvalue[0]'"; break;
}
$sqlwhere .= " and `assessment_formfields`.formfield_id = $s_formfieldid[0] and `assessment_data`.$valtype $val";
}
if ($s_pipelineid != ""){
//echo "s_pipelineid is not blank";
$sqlwhere .= " and `analysis`.pipeline_id = $s_pipelineid";
if ($s_pipelineresultname != "") {
//echo "s_pipelineresultname is not blank";
/* need to do a subquery outside of the main query to get the list of result names. This is due to a bug in the 5.x series of MySQL */
$sqlstringX = "select resultname_id from analysis_resultnames where result_name like '%$s_pipelineresultname%' ";
$resultX = MySQLQuery($sqlstringX, __FILE__, __LINE__);
if (mysql_num_rows($resultX) > 0) {
while ($rowX = mysql_fetch_array($resultX, MYSQL_ASSOC)) {
$resultnames[] = $rowX['resultname_id'];
}
$resultnamelist = implode2(',',$resultnames);
$sqlwhere .= " and `analysis_results`.`result_nameid` in ($resultnamelist) ";
}
else {
$sqlwhere .= " and `analysis_results`.`result_nameid` = '' ";
}
//$sqlwhere .= " and `analysis`.pipeline_id = $s_pipelineid and `analysis_resultnames`.`result_name` like '%$s_pipelineresultname%' ";
}
if ($s_pipelineresultunit != "") {
//echo "s_pipelineresultunit is not blank";
/* need to do a subquery outside of the main query to get the list of result names. This is due to a bug in the 5.x series of MySQL */
$sqlstringX = "select resultunit_id from analysis_resultunit where result_unit like '%$s_pipelineresultunit%' ";
$resultX = MySQLQuery($sqlstringX, __FILE__, __LINE__);
if (mysql_num_rows($resultX) > 0) {
while ($rowX = mysql_fetch_array($resultX, MYSQL_ASSOC)) {
$resultunit[] = $rowX['resultunit_id'];
}
$resultunitlist = implode2(',',$resultunit);
$sqlwhere .= " and `analysis_results`.`result_unitid` in ($resultunitlist) ";
}
else {
$sqlwhere .= " and `analysis_results`.`result_unitid` = '' ";
}
}
if ($s_pipelineresultvalue != "") {
//echo "s_pipelineresultvalue is not blank";
$sqlwhere .= " and `analysis_results`.`result_value` $s_pipelineresultcompare '$s_pipelineresultvalue' ";
}
if ($s_pipelineresulttype != "") {
//echo "s_pipelineresulttype is not blank";
$sqlwhere .= " and `analysis_results`.`result_type` = '$s_pipelineresulttype' ";
}
}
/* ----- build SQL query ----- */
if ($s_resultorder == "pipeline") {
//$sqlstring = "select analysis_resultnames.result_name, subjects.uid, studies.study_num, studies.study_id, studies.study_datetime, subjects.subject_id, subjects.birthdate, subjects.gender, analysis_results.*";
$sqlstring = "select subjects.uid, studies.study_num, studies.study_id, studies.study_datetime, subjects.subject_id, subjects.birthdate, subjects.gender, analysis_results.*";
}
elseif ($s_resultorder == "long") {
if ($s_usealtseriesdesc) {
$sqlstring = "select subjects.uid, studies.study_id, studies.study_num, studies.study_datetime, studies.study_modality, subjects.subject_id, `" . $s_studymodality . "_series`.series_altdesc";
}
else {
$sqlstring = "select subjects.uid, studies.study_id, studies.study_num, studies.study_datetime, studies.study_modality, subjects.subject_id, `" . $s_studymodality . "_series`.series_desc";
}
}
elseif ($s_resultorder == 'subject') {
$sqlstring = "select subjects.*, projects.*, enrollment.*";
}
elseif ($s_resultorder == 'uniquesubject') {
$sqlstring = "select subjects.subject_id, subjects.uid, studies.study_id, studies.study_alternateid, studies.study_num";
}
else {
$sqlstring = "select *";
}
/* check if the measures should be returned as well */
if ($s_measuresearch != ""){
$sqlstring .= ", measures.*, measurenames.measure_name";
}
if ($s_pipelineid == ""){
$sqlstring .= ", `$s_studymodality" . "_series`.$s_studymodality" . "series_id";
}
$sqlstring .= " from `enrollment`
join `projects` on `enrollment`.project_id = `projects`.project_id
join `subjects` on `subjects`.subject_id = `enrollment`.subject_id
join `studies` on `studies`.enrollment_id = `enrollment`.enrollment_id";
/* join in other tables if necessary */
if ($s_subjectaltuid != "") {
$sqlstring .= " join `subject_altuid` on `subjects`.subject_id = `subject_altuid`.subject_id";
}
if ($s_pipelineid == ""){
$sqlstring .= " join `" . $s_studymodality . "_series` on `" . $s_studymodality . "_series`.study_id = `studies`.study_id";
}
if ($s_measuresearch != ""){
/* join in the measure table if there is a measure to search for */
$sqlstring .= " left join `measures` on `measures`.enrollment_id = `enrollment`.enrollment_id left join `measurenames` on `measures`.measurename_id = `measurenames`.measurename_id";
}
if ($s_formvalue[0] != ""){
/* join in the form tables if there is formfield criteria to search for */
$sqlstring .= " join `assessments` on `assessments`.enrollment_id = `enrollment`.enrollment_id
join `assessment_formfields` on `assessment_formfields`.form_id = `assessments`.form_id
join `assessment_data` on `assessment_data`.formfield_id = `assessment_formfields`.formfield_id";
}
if ($s_pipelineid != ""){
/* join in the pipeline tables if there is formfield criteria to search for */
$sqlstring .= " join `analysis` on `analysis`.study_id = `studies`.study_id
join `analysis_results` on `analysis_results`.analysis_id = `analysis`.analysis_id";
//join `analysis_resultnames` on `analysis_results`.result_nameid = `analysis_resultnames`.resultname_id";
}
if (($s_studymodality == "mr") && ($s_pipelineid == "")) {
$sqlstring .= " left join `" . $s_studymodality . "_qa` on `" . $s_studymodality . "_qa`.$s_studymodality" . "series_id = `" . $s_studymodality . "_series`.$s_studymodality" . "series_id";
}
/* merge the joins and where clauses */
$sqlstring .= " where `subjects`.isactive = 1 and `studies`.study_modality = '$s_studymodality' $sqlwhere ";
if ($s_resultorder == 'subject') {
$sqlstring .= " group by enrollment.enrollment_id order by subjects.uid, projects.project_name";
}
elseif ($s_resultorder == 'uniquesubject') {
$sqlstring .= " group by studies.study_id";
}
else {
if ($s_formvalue[0] != ""){
$sqlstring .= " group by `" . $s_studymodality . "_series`.$s_studymodality" . "series_id ";
}
if (($s_resultorder != "pipeline") && ($s_resultorder != "pipelinecsv")) {
$sqlstring .= " order by `studies`.study_datetime, `studies`.study_id";
if ($s_pipelineid == ""){
$sqlstring .= ", `" . $s_studymodality . "_series`.series_num";
}
}
}
//echo "[$sqlstring] [$sqlwhere]";
//exit(0);
return $sqlstring;
}
function BuildSQLString($s)
{
Debug(__FILE__, __LINE__, "<pre>" . print_r($s, true) . "</pre>");
/* escape all the variables and put them back into meaningful variable names */
foreach ($s as $key => $value) {
if (is_scalar($value)) {
${$key} = trim(mysql_real_escape_string($s[$key]));
} else {
${$key} = $s[$key];
}
}
/* make modality lower case to conform with table names... MySQL table names are case sensitive when using the 'show tables' command */
$s_studymodality = strtolower($s_studymodality);
$modality = $s_studymodality;
/* also make a variable for the series table */
$modalitytable = $s_studymodality . "_series";
/* check if modality_series table actually exists */
$sqlstring = "show tables from " . $GLOBALS['cfg']['mysqldatabase'] . " like '{$modalitytable}'";
$result = MySQLQuery($sqlstring, __FILE__, __LINE__);
if (mysql_num_rows($result) < 1) {
?>
<?php
echo $modality;
?>
_series table does not exist. Unable to query information about <?php
echo $modality;
?>
type series
<?php
return "";
}
/* determine which fields have criteria, build the where clause */
if ($s_subjectuid != "" || $s_subjectgroupid != "") {
if ($s_subjectgroupid != "") {
$ids = GetIDListFromGroup($s_subjectgroupid);
$sqlwhere .= " and `subjects`.subject_id in (" . $ids . ")";
} else {
if (preg_match('/[\\^\\,;\\-\'\\s]/', $s_subjectuid) == 0) {
$sqlwhere .= " and `subjects`.uid = '{$s_subjectuid}'";
} else {
$sqlwhere .= " and `subjects`.uid in (" . MakeSQLList($s_subjectuid) . ")";
}
}
}
if ($s_subjectaltuid != "") {
if (preg_match('/[\\^\\,;\\-\'\\s]/', $s_subjectaltuid) == 0) {
$sqlwhere .= "and `subject_altuid`.altuid like '%{$s_subjectaltuid}%'";
} else {
$sqlwhere .= "and `subject_altuid`.altuid in (" . MakeSQLList($s_subjectaltuid) . ")";
}
}
if ($s_subjectname != "") {
$sqlwhere .= " and `subjects`.name like '%{$s_subjectname}%'";
}
if ($s_subjectdobstart != "") {
$sqlwhere .= " and `subjects`.birthdate >= '{$s_subjectdobstart}'";
}
if ($s_subjectdobend != "") {
$sqlwhere .= " and `subjects`.birthdate <= '{$s_subjectdobend}'";
}
if ($s_subjectgender != "") {
$sqlwhere .= " and `subjects`.gender = '{$s_subjectgender}'";
}
if ($s_projectid != "all") {
$sqlwhere .= " and `projects`.project_id = {$s_projectid}";
} else {
$tmpsqlstring = "select project_id from projects where instance_id = '" . $_SESSION['instanceid'] . "'";
$tmpresult = MySQLQuery($tmpsqlstring, __FILE__, __LINE__);
while ($tmprow = mysql_fetch_array($tmpresult, MYSQL_ASSOC)) {
if ($tmprow['project_id'] != "") {
$projectids[] = $tmprow['project_id'];
}
}
$projectidlist = implode(",", $projectids);
$sqlwhere .= " and `projects`.project_id in ({$projectidlist})";
}
if ($s_enrollsubgroup != "") {
$sqlwhere .= " and `enrollment`.enroll_subgroup = '{$s_enrollsubgroup}'";
}
if ($s_studygroupid != "") {
$studyids = GetIDListFromGroup($s_studygroupid);
$sqlwhere .= " and `studies`.study_id in (" . $studyids . ")";
}
if ($s_studyinstitution != "") {
$sqlwhere .= " and `studies`.study_institution like '%{$s_studyinstitution}%'";
}
if ($s_studyequipment != "") {
$sqlwhere .= " and `studies`.study_site like '%{$s_studyequipment}%'";
}
if ($s_studyaltscanid != "") {
if (preg_match('/[\\^\\,;\\-\'\\s]/', $s_studyaltscanid) == 0) {
$sqlwhere .= " and `studies`.study_alternateid like '%{$s_studyaltscanid}%'";
} else {
$sqlwhere .= " and `studies`.study_alternateid in (" . MakeSQLList($s_studyaltscanid) . ")";
}
}
if ($s_studydatestart != "") {
$sqlwhere .= " and `studies`.study_datetime >= '{$s_studydatestart} 00:00:00'";
}
if ($s_studydateend != "") {
$sqlwhere .= " and `studies`.study_datetime <= '{$s_studydateend} 23:59:59'";
}
if ($s_studydesc != "") {
$sqlwhere .= " and `studies`.study_desc like '%{$s_studydesc}%'";
}
if ($s_studyphysician != "") {
$sqlwhere .= " and `studies`.study_performingphysician like '%{$s_studyphysician}%'";
}
if ($s_studyoperator != "") {
$sqlwhere .= " and `studies`.study_operator like '%{$s_studyoperator}%'";
}
if ($s_studytype != "") {
$sqlwhere .= " and `studies`.study_type like '%{$s_studytype}%'";
}
if ($s_seriesgroupid != "") {
$seriesids = GetIDListFromGroup($s_seriesgroupid);
$sqlwhere .= " and `{$modalitytable}`.{$modality}" . "series_id in (" . $seriesids . ")";
}
if ($s_seriesdesc != "") {
$sqlwhere .= " and (";
/* if it contains a comma, the search will be OR */
//if (strpos($s_seriesdesc,',') !== false) {
$seriesdescs = explode(',', $s_seriesdesc);
$wheres = array();
foreach ($seriesdescs as $seriesdesc) {
if ($s_usealtseriesdesc) {
$wheres[] = "(`{$modalitytable}`.series_altdesc like '%" . trim($seriesdesc) . "%')";
} else {
$wheres[] = "(`{$modalitytable}`.series_desc like '%" . trim($seriesdesc) . "%')";
}
}
$sqlwhere .= implode(" or ", $wheres);
//}
//else {
/* otherwise the search is an AND */
//$seriesdescs = explode(';',$s_seriesdesc);
//$wheres = array();
//foreach ($seriesdescs as $seriesdesc) {
// $wheres[] = "(`$modalitytable`.series_desc like '%" . trim($seriesdesc) . "%')";
//}
//$sqlwhere .= implode(" and ", $wheres);
//}
$sqlwhere .= ")";
}
if ($s_seriessequence != "") {
$sqlwhere .= " and `{$modalitytable}`.series_sequencename like '%{$s_seriessequence}%'";
}
if ($s_seriesimagetype != "") {
$sqlwhere .= " and (";
$seriesimagetypes = explode(',', $s_seriesimagetype);
$wheres = array();
foreach ($seriesimagetypes as $seriesimagetype) {
if (strpos($seriesimagetype, '*') !== false) {
$seriesimagetype = str_replace('*', '%', $seriesimagetype);
$wheres[] = "(`{$modalitytable}`.image_type like '" . trim($seriesimagetype) . "')";
} else {
$wheres[] = "(`{$modalitytable}`.image_type = '" . trim($seriesimagetype) . "')";
}
}
$sqlwhere .= implode(" or ", $wheres);
$sqlwhere .= ")";
//$sqlwhere .= " and `$modalitytable`.image_type like '%$s_seriesimagetype%'";
}
if ($s_seriesimagecomments != "") {
$sqlwhere .= " and `{$modalitytable}`.image_comments like '%{$s_seriesimagecomments}%'";
}
if ($s_seriestr != "") {
$sqlwhere .= " and `{$modalitytable}`.series_tr = '{$s_seriestr}'";
}
if ($s_seriesnum != "") {
if (substr($s_seriesnum, 0, 2) == '>=') {
$val = substr($s_seriesnum, 2);
$sqlwhere .= " and `{$modalitytable}`.series_num >= '{$val}'";
} elseif (substr($s_seriesnum, 0, 2) == '<=') {
$val = substr($s_seriesnum, 2);
$sqlwhere .= " and `{$modalitytable}`.series_num <= '{$val}'";
} elseif (substr($s_seriesnum, 0, 1) == '>') {
$val = substr($s_seriesnum, 1);
$sqlwhere .= " and `{$modalitytable}`.series_num > '{$val}'";
} elseif (substr($s_seriesnum, 0, 1) == '<') {
$val = substr($s_seriesnum, 1);
$sqlwhere .= " and `{$modalitytable}`.series_num < '{$val}'";
} elseif (substr($s_seriesnum, 0, 1) == '~') {
$val = substr($s_seriesnum, 1);
$sqlwhere .= " and `{$modalitytable}`.series_num <> '{$val}'";
} else {
$sqlwhere .= " and `{$modalitytable}`.series_num = '{$s_seriesnum}'";
}
}
if ($s_seriesnumfiles != "") {
if (substr($s_seriesnumfiles, 0, 2) == '>=') {
$val = substr($s_seriesnumfiles, 2);
$sqlwhere .= " and `{$modalitytable}`.numfiles >= '{$val}'";
} elseif (substr($s_seriesnumfiles, 0, 2) == '<=') {
$val = substr($s_seriesnumfiles, 2);
$sqlwhere .= " and `{$modalitytable}`.numfiles <= '{$val}'";
} elseif (substr($s_seriesnumfiles, 0, 1) == '>') {
$val = substr($s_seriesnumfiles, 1);
$sqlwhere .= " and `{$modalitytable}`.numfiles > '{$val}'";
} elseif (substr($s_seriesnumfiles, 0, 1) == '<') {
$val = substr($s_seriesnumfiles, 1);
$sqlwhere .= " and `{$modalitytable}`.numfiles < '{$val}'";
} elseif (substr($s_seriesnumfiles, 0, 1) == '~') {
$val = substr($s_seriesnumfiles, 1);
$sqlwhere .= " and `{$modalitytable}`.numfiles <> '{$val}'";
} else {
$sqlwhere .= " and `{$modalitytable}`.numfiles = '{$s_seriesnumfiles}'";
}
}
if ($s_measuresearch != "") {
$tmpsqlstring = "select measurename_id from measurenames where measure_name = '{$s_measures}'";
$tmpresult = MySQLQuery($tmpsqlstring, __FILE__, __LINE__);
$tmprow = mysql_fetch_array($tmpresult, MYSQL_ASSOC);
$measurenameid = $tmprow['measurename_id'];
if (is_numeric($measurevalue)) {
$valtype = "measure_valuenum";
} else {
$valtype = "measure_valuestring";
}
switch ($s_measurecriteria) {
case "contains":
$val = " like '%{$s_measurevalue}%'";
break;
case "eq":
$val = " = '{$s_measurevalue}'";
break;
case "gt":
$val = " > '{$s_measurevalue}'";
break;
case "lt":
$val = " < '{$s_measurevalue}'";
break;
}
$measuresearch = ParseMeasureSearchList($s_measuresearch);
if ($measuresearch != "") {
$sqlwhere .= " and " . $measuresearch;
}
}
if ($s_formvalue[0] != "") {
/* get the formfield datatype to make sure we compare against the correct assessment_data value */
$tmpsqlstring = "select * from assessment_formfields where formfield_id = {$s_formfieldid['0']}";
$tmpresult = MySQLQuery($tmpsqlstring, __FILE__, __LINE__);
$tmprow = mysql_fetch_array($tmpresult, MYSQL_ASSOC);
$datatype = $tmprow['formfield_datatype'];
switch ($datatype) {
case "string":
$valtype = "value_string";
break;
case "number":
$valtype = "value_number";
break;
case "multichoice":
$valtype = "value_text";
break;
case "singlechoice":
$valtype = "value_text";
break;
case "text":
$valtype = "value_text";
break;
case "date":
$valtype = "value_binary";
break;
case "binary":
$valtype = "value_binary";
break;
case "header":
$valtype = "value_text";
break;
}
switch ($s_formcriteria[0]) {
case "contains":
$val = " like '%{$s_formvalue['0']}%'";
break;
case "eq":
$val = " = '{$s_formvalue['0']}'";
break;
case "gt":
$val = " > '{$s_formvalue['0']}'";
break;
case "lt":
$val = " < '{$s_formvalue['0']}'";
break;
}
$sqlwhere .= " and `assessment_formfields`.formfield_id = {$s_formfieldid['0']} and `assessment_data`.{$valtype} {$val}";
}
Debug(__FILE__, __LINE__, "Checkpoint A");
if ($s_pipelineid != "") {
Debug(__FILE__, __LINE__, "Checkpoint B");
$sqlwhere .= " and `analysis`.pipeline_id = {$s_pipelineid}";
if ($s_pipelineresultname != "") {
//echo "s_pipelineresultname is not blank";
/* need to do a subquery outside of the main query to get the list of result names. This is due to a bug in the 5.x series of MySQL */
$sqlstringX = "select resultname_id from analysis_resultnames where result_name like '%{$s_pipelineresultname}%' ";
$resultX = MySQLQuery($sqlstringX, __FILE__, __LINE__);
if (mysql_num_rows($resultX) > 0) {
while ($rowX = mysql_fetch_array($resultX, MYSQL_ASSOC)) {
$resultnames[] = $rowX['resultname_id'];
}
$resultnamelist = implode2(',', $resultnames);
$sqlwhere .= " and `analysis_results`.`result_nameid` in ({$resultnamelist}) ";
} else {
$sqlwhere .= " and `analysis_results`.`result_nameid` = '' ";
}
}
Debug(__FILE__, __LINE__, "Checkpoint C");
if ($s_pipelineresultunit != "") {
Debug(__FILE__, __LINE__, "Checkpoint D");
/* need to do a subquery outside of the main query to get the list of result names. This is due to a bug in the 5.x series of MySQL */
$sqlstringX = "select resultunit_id from analysis_resultunit where result_unit like '%{$s_pipelineresultunit}%' ";
$resultX = MySQLQuery($sqlstringX, __FILE__, __LINE__);
if (mysql_num_rows($resultX) > 0) {
while ($rowX = mysql_fetch_array($resultX, MYSQL_ASSOC)) {
$resultunit[] = $rowX['resultunit_id'];
}
$resultunitlist = implode2(',', $resultunit);
$sqlwhere .= " and `analysis_results`.`result_unitid` in ({$resultunitlist}) ";
} else {
$sqlwhere .= " and `analysis_results`.`result_unitid` = '' ";
}
}
if ($s_pipelineresultvalue != "") {
Debug(__FILE__, __LINE__, "Checkpoint E");
//echo "s_pipelineresultvalue is not blank";
$sqlwhere .= " and `analysis_results`.`result_value` {$s_pipelineresultcompare} '{$s_pipelineresultvalue}' ";
}
if ($s_pipelineresulttype != "") {
Debug(__FILE__, __LINE__, "Checkpoint F");
//echo "s_pipelineresulttype is not blank";
$sqlwhere .= " and `analysis_results`.`result_type` = '{$s_pipelineresulttype}' ";
}
}
/* ----- put the whole SQL query together ----- */
/* first setup the SELECT, depending on the type of query ... */
if ($s_resultorder == "pipeline") {
Debug(__FILE__, __LINE__, "Checkpoint G");
$sqlstring = "select subjects.uid, studies.study_num, studies.study_id, studies.study_datetime, subjects.subject_id, subjects.birthdate, subjects.gender, timestampdiff(MONTH, subjects.birthdate, studies.study_datetime) 'ageinmonths', analysis_results.*";
} elseif ($s_resultorder == "pipelinelong") {
Debug(__FILE__, __LINE__, "Checkpoint H");
$sqlstring = "select subjects.uid, studies.study_num, studies.study_id, studies.study_datetime, subjects.subject_id, subjects.birthdate, subjects.gender, timestampdiff(MONTH, subjects.birthdate, studies.study_datetime) 'ageinmonths', analysis_results.*";
} elseif ($s_resultorder == "pipelinelongyear") {
Debug(__FILE__, __LINE__, "Checkpoint I");
$sqlstring = "select subjects.uid, studies.study_num, studies.study_id, studies.study_datetime, subjects.subject_id, subjects.birthdate, subjects.gender, timestampdiff(YEAR, subjects.birthdate, studies.study_datetime) 'ageinmonths', analysis_results.*";
} elseif ($s_resultorder == "long") {
if ($s_usealtseriesdesc) {
$sqlstring = "select subjects.uid, studies.study_id, studies.study_num, studies.study_datetime, studies.study_modality, subjects.subject_id, `{$modalitytable}`.series_altdesc";
} else {
$sqlstring = "select subjects.uid, studies.study_id, studies.study_num, studies.study_datetime, studies.study_modality, subjects.subject_id, `modalitytable`.series_desc";
}
} elseif ($s_resultorder == 'qctable' || $s_resultorder == 'qcchart') {
$sqlstring = "select subjects.subject_id, subjects.uid, studies.study_datetime, studies.study_num, unix_timestamp(DATE(series_datetime)) 'studydate', {$modalitytable}.series_desc, {$modality}" . "_qa.*";
} elseif ($s_resultorder == 'subject') {
$sqlstring = "select subjects.*, projects.*, enrollment.*";
} elseif ($s_resultorder == 'uniquesubject') {
$sqlstring = "select subjects.subject_id, subjects.uid, studies.study_id, studies.study_alternateid, studies.study_num";
} else {
$sqlstring = "select *";
}
/* check if the measures should be returned as well */
if ($s_measuresearch != "") {
$sqlstring .= ", measures.*, measurenames.measure_name";
}
if ($s_pipelineid == "") {
$sqlstring .= ", `{$modalitytable}`.{$modality}" . "series_id";
}
/* ... then add the table JOINs ... */
$sqlstring .= " from `enrollment`\r\n\t\tjoin `projects` on `enrollment`.project_id = `projects`.project_id\r\n\t\tjoin `subjects` on `subjects`.subject_id = `enrollment`.subject_id\r\n\t\tjoin `studies` on `studies`.enrollment_id = `enrollment`.enrollment_id";
/* join in other tables if necessary */
if ($s_subjectaltuid != "") {
$sqlstring .= " join `subject_altuid` on `subjects`.subject_id = `subject_altuid`.subject_id";
}
if ($s_pipelineid == "") {
$sqlstring .= " join `{$modalitytable}` on `{$modalitytable}`.study_id = `studies`.study_id";
}
if ($s_measuresearch != "") {
/* join in the measure table if there is a measure to search for */
$sqlstring .= " left join `measures` on `measures`.enrollment_id = `enrollment`.enrollment_id left join `measurenames` on `measures`.measurename_id = `measurenames`.measurename_id";
}
if ($s_formvalue[0] != "") {
/* join in the form tables if there is formfield criteria to search for */
$sqlstring .= " join `assessments` on `assessments`.enrollment_id = `enrollment`.enrollment_id\r\n\t\t\tjoin `assessment_formfields` on `assessment_formfields`.form_id = `assessments`.form_id\r\n\t\t\tjoin `assessment_data` on `assessment_data`.formfield_id = `assessment_formfields`.formfield_id";
}
if ($s_pipelineid != "") {
/* join in the pipeline tables if there is formfield criteria to search for */
$sqlstring .= " join `analysis` on `analysis`.study_id = `studies`.study_id\r\n\t\t\tjoin `analysis_results` on `analysis_results`.analysis_id = `analysis`.analysis_id";
}
if ($modality == "mr" && $s_pipelineid == "") {
$sqlstring .= " left join `mr_qa` on `mr_qa`.mrseries_id = `mr_series`.mrseries_id";
}
/* ... then add the WHERE clause (created earlier) and the ORDER BY and GROUP BY clauses if necessary */
$sqlstring .= " where `subjects`.isactive = 1 and `studies`.study_modality = '{$modality}' {$sqlwhere} ";
if ($s_resultorder == 'subject') {
$sqlstring .= " group by enrollment.enrollment_id order by subjects.uid, projects.project_name";
} elseif ($s_resultorder == 'uniquesubject') {
$sqlstring .= " group by studies.study_id";
} else {
if ($s_formvalue[0] != "") {
$sqlstring .= " group by `{$modalitytable}`.{$modality}" . "series_id ";
}
if ($s_resultorder != "pipeline" && $s_resultorder != "pipelinecsv" && $s_resultorder != "pipelinelong" && $s_resultorder != "pipelinelongyear") {
$sqlstring .= " group by `{$modalitytable}`.{$modality}" . "series_id order by `studies`.study_datetime, `studies`.study_id";
if ($s_pipelineid == "") {
$sqlstring .= ", `{$modalitytable}`.series_num";
}
}
}
return $sqlstring;
}