コード例 #1
0
ファイル: search.php プロジェクト: EugeniaRadulescu/nidb
	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;
	}	
コード例 #2
0
ファイル: search.php プロジェクト: pmolfese/nidb
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;
}