\n";
}
}
else $ISOLATE_ID = "";
$required_segments = array();
$print_segments = array();
$vars = (!empty($_GET['stats'])) ? $_GET : $_POST;
$method = (!empty($_GET['stats'])) ? "get" : "post";
// echo "
\n";
$where = "";
# if(!empty($_GET['geoentity'])) $COUNTRY[] = $_GET['geoentity'];
######### Get isolates from map #############
if(!empty($_POST['getIsolatesFromMap'])){
$qmap = "select GEODIVISIONS.GEODIVISION_ID, LONGITUDE, LATITUDE from GEOENTITIES inner join GEODIVISIONS on (GEODIVISIONS.GEODIVISION_ID = GEOENTITIES.COUNTRY_ID) where GEOENTITIES.CV_TYPE = 'COUNTRY'";
$r = ExecRequeteNC($qmap);
$points = explode(";",$_SESSION['map_coords']);
$points[] = $points[0];
$polygon = array();
foreach($points as $coord){
$pieces = explode(",",$coord);
$polygon[] = new Point($pieces[0],$pieces[1]);
}
while($s = LigneSuivante($r)){
$p = new Point($s->LONGITUDE,$s->LATITUDE);
if(pointInside($p,$polygon)){ $COUNTRY[] = $s->GEODIVISION_ID;}
}
oci_free_statement($r);
}
######## Check pertinence of the query #####
if(array_search("A",$IV_TYPE) === FALSE){
$IV_HSUBTYPE = array();
$IV_NSUBTYPE = array();
}
$GEO_ROOT_ID = array();
if(!count($COUNTRY)){
$continents = array();
if(!empty($_POST[$prefix.'continents'])){
for($i = 0; $i < count($_POST[$prefix.'continents']); $i++) $continents[$i] = $_POST[$prefix.'continents'][$i];
}
elseif(!empty($_REQUEST['stats']) && $_REQUEST['stats'] == 'CONT'){
$continents[] = (!empty($_REQUEST[$prefix.'geoentity'])) ? $_REQUEST[$prefix.'geoentity'] : "";
}
if(count($continents)){
if(count($continents) && $continents[0] != '-ALL-' ){
$GEO_ROOT_ID = $continents;
}
else{
$qcont = "select GEODIV_NAME, GEODIVISION_ID from GEODIVISIONS where CV_TYPE = 'CONT' order by GEODIV_NAME";
$r = ExecRequeteNC($qcont);
while($s = LigneSuivante($r)) $GEO_ROOT_ID[] = $s->GEODIVISION_ID;
}
}
else{
$qcont = "select GEODIV_NAME, GEODIVISION_ID from GEODIVISIONS where CV_TYPE = 'CONT' order by GEODIV_NAME";
$r = ExecRequeteNC($qcont);
while($s = LigneSuivante($r)) $GEO_ROOT_ID[] = $s->GEODIVISION_ID;
}
}
// if($where) $where = substr($where,0,-3)." and ";
######## Construct the query #####
if(!empty($_REQUEST['stats'])){
$_SESSION['isolate'] = array();
$_SESSION['refsets'] = array();
if(!empty($_GET['set_seqtype_from_stats'])) $_SESSION['prefs']['seq_type'] = $_GET['set_seqtype_from_stats'];
$slider_value = ($_SESSION['prefs']['seq_type'] == 'DNA') ? 0 : 1;
$script = "Event.observe(window,'load',function(){initializeProtDnaSlider($slider_value);},false);\n";
if(empty($_GET['seq_type'])){
head('browse',$script);
$class_dna = (!$slider_value) ? "bold" : "fade";
$class_prot = ($slider_value) ? "bold" : "fade";
$icon_dna = (!$slider_value) ? "icon_dna.png" : "icon_dna_fade.png";
$icon_prot = ($slider_value) ? "icon_protein.png" : "icon_protein_fade.png";
$direction = ($slider_value) ? "right" : "left";
echo "
\n";
}
# ==> GET VALUES FROM STATS
$_SESSION['stat_graph_state'] = 0;
$wheres = array();
$stat_value = (!empty($_GET['stat_value'])) ? $_GET['stat_value'] : "";
$xaxis = (!empty($_GET['xaxis'])) ? $_GET['xaxis'] : "";
$entry_ids = (!empty($_GET['entry_ids'])) ? $_GET['entry_ids'] : "";
if(empty($_SESSION['stats_filters'])) $_SESSION['stats_filters'] = array();
foreach($_SESSION['stats_filters'] as $key => $value){
$key_tabs = explode("_",$key,2);
if($value){
$key_tabs[1] = str_replace("_SUBTYPE","SUBTYPE",$key_tabs[1]);
if($key_tabs[1] == 'CONT') $wheres[] = " GEO_ROOT_ID = $value";
elseif($key_tabs[1] == 'SOURCE_ID' && $xaxis != 'SOURCE_NAME'){
$wheres[] = " SOURCE_ROOT_ID = $value";
}
elseif($key_tabs[1] != 'SOURCE_ID') $wheres[] = " ".(strtoupper($key_tabs[1]))." = '$value'";
}
}
if($xaxis && $stat_value){
if($xaxis == 'COLLECT_YEAR') $wheres[] = " COLLECT_YEAR = $stat_value ";
elseif($xaxis == 'SOURCE_NAME'){
$wheres[] = " SOURCE_ROOT_ID = '$stat_value'";
}
elseif($xaxis == 'IV_TYPE'){
$vartypes = array("ivtype" => "IV_TYPE","htype" => "HSUBTYPE","ntype" => "NSUBTYPE");
foreach($vartypes as $vartype => $var_cvtype) $$vartype = "";
if(ereg("([AB]):H(.)N(.)",$stat_value,$regs)){
$ivtype = $regs[1];
$htype = $regs[2];
$ntype = $regs[3];
}
elseif($stat_value == 'A' || $stat_value == 'B') $iv_type = $stat_value;
foreach($vartypes as $vartype => $var_cvtype){
if($$vartype) $wheres[] = " $var_cvtype = '".$$vartype."' ";
}
}
elseif($xaxis == 'LAB_ID'){
$wheres[] = " LAB_ID = '$stat_value' ";
}
}
elseif($xaxis == 'LAB_ID' && $stat_value == 0) $wheres[] = " LAB_ID = 0";
elseif($xaxis == 'LAB_ID' && !$stat_value) $wheres[] = " LAB_ID is NULL";
elseif($entry_ids) $wheres[] = " ISOLATE_ID in (select distinct(ISOLATE_ID) from SEQS_BY_ISOLATE where ENTRY_ID in (".(str_replace(";",",",$entry_ids))."))";
elseif($_REQUEST['stats'] == 'mds'){
$axis = (!empty($_SESSION['mds_axis'])) ? explode(":",$_SESSION['mds_axis']) : array('X','Y');
$zoom_coords = (!empty($_SESSION['zoom_coords'])) ? explode(":",$_SESSION['zoom_coords']) : array(0,0,$width,$height,$axis[0],$axis[1]);
if($axis[0] != 'Y' && $axis[0] != 'X' && $axis[0] != 'Z') $axis[0] = 'X';
if($axis[1] != 'Y' && $axis[1] != 'X' && $axis[1] != 'Z') $axis[1] = 'Y';
$_SESSION['mds_axis'] = $axis[0].":".$axis[1];
if(count($zoom_coords)< 6){
$zoom_coords = array(0,0,$width,$height,$axis[0],$axis[1]);
}
else{
if($zoom_coords[4] != 'Y' && $zoom_coords[4] != 'X' && $zoom_coords[4] != 'Z') $zoom_coords[4] = 'X';
if($zoom_coords[5] != 'Y' && $zoom_coords[5] != 'X' && $zoom_coords[5] != 'Z') $zoom_coords[5] = 'Y';
}
$error = 0;
$setname = (!empty($_SESSION['mds_setname'])) ? $_SESSION['mds_setname'] : "";
$annotation = (!empty($_SESSION['mds_annotations'])) ? $_SESSION['mds_annotations'] : "";
if($setname){
$width = 750;
$height = 500;
$mds_table = (in_array(substr($setname,-3),array('int','ext','all'))) ? "ISOLATE_PCA_CONVERTED" : "BIOSEQ_MDS_CONVERTED";
$qmds = "select min(X) as MINX, min(Y) as MINY, min(Z) as MINZ, max(X) as MAXX, max(Y) as MAXY, max(Z) as MAXZ from $mds_table where SETNAME = '$setname'";
$rmds = ExecRequeteNC($qmds);
if($smds = LigneSuivante($rmds)){
$xmin = (($smds->{"MAX".$zoom_coords[4]} - $smds->{"MIN".$zoom_coords[4]})/($width)*($zoom_coords[0]))+$smds->{"MIN".$zoom_coords[4]};
$ymin = $smds->{"MAX".$zoom_coords[5]} - (($smds->{"MAX".$zoom_coords[5]} - $smds->{"MIN".$zoom_coords[5]})/($height)*($zoom_coords[3]));
$xmax = (($smds->{"MAX".$zoom_coords[4]} - $smds->{"MIN".$zoom_coords[4]})/($width)*($zoom_coords[2]))+$smds->{"MIN".$zoom_coords[4]};
$ymax = $smds->{"MAX".$zoom_coords[5]} - (($smds->{"MAX".$zoom_coords[5]} - $smds->{"MIN".$zoom_coords[5]})/($height)*($zoom_coords[1]));
$wheres[] = " $zoom_coords[4] >= $xmin and $zoom_coords[4] <= $xmax and $zoom_coords[5] >= $ymin and $zoom_coords[5] <= $ymax and SETNAME = '$setname' ";
}
else $wheres[] = "";
}
}
$where = implode(" and",$wheres);
}
## ==> END STATS
$browse_table_name = ($estimate && !$store_query) ? "BROWSE_NT_SEQ" : "BROWSE_NT_SEQ_2";
$params = array(
"ISOLATE_ID" => "ISOLATE_ID",
"IS_COMPLETE" => "IS_COMPLETE",
"ISOLATE_NAME" => "ISOLATE_NAME",
"PASSAGE_HISTORY" => "PASSAGE_HISTORY",
"to_char($browse_table_name.CREATE_DATE, 'YYYY-MM-DD')" => "CREATE_DATE",
"COLLECT_YEAR" => "COLLECT_YEAR",
"COUNTRY_NAME" => "GEODIV_NAME",
"SOURCE_NAME" => "HOST",
"IV_TYPE" => "IV_TYPE",
"SEGMENT_NAME" => "SEGMENT",
"SEQUENCE_LENGTH" => "SEQUENCE_LENGTH",
"COUNTRY_ID" => "COUNTRY_ID",
"LONGITUDE" => "LONGITUDE",
"LATITUDE" => "LATITUDE ",
"HSUBTYPE" => "HSUBTYPE",
"NSUBTYPE" => "NSUBTYPE",
"IV_LINEAGE" => "IV_LINEAGE",
"LABEL_CLADE_ID" => "LABEL_CLADE_ID",
"LAB_ID" => "LAB_ID",
"USER_ID" => "USER_ID",
"COMPLETE_GENOME" => "COMPLETE_GENOME"
);
$q = "select ";
if($estimate && !$store_query){
$q .= "count(distinct(BROWSE_NT_SEQ.ISOLATE_ID)) as NB, count(distinct(BROWSE_NT_SEQ.ENTRY_ID)) as TOTAL ";
if($_SESSION['ESTIMATE_MAP'] == 1) $q .= ", COUNTRY_NAME ";
}
else{
foreach($params as $field => $print) $q .= (strpos($field,'(') === FALSE) ? " $browse_table_name.$field as $print, " : " $field as $print, ";
}
$q = substr($q,0,-2);
if(!empty($_REQUEST['stats']) && $_REQUEST['stats'] == 'mds'){
if(substr($_SESSION['mds_setname'],0,1) == 'P' && (empty($_GET['seq_type']) || $_GET['seq_type'] == 'PROTEIN')){
$q = str_replace("BROWSE_NT_SEQ_2","BROWSE_AA_SEQ_2",$q);
$q .= " FROM BROWSE_AA_SEQ_2 "
." INNER JOIN ISOLATE_ENTRY_ASSOC ON BROWSE_AA_SEQ_2.ISOLATE_ID = ISOLATE_ENTRY_ASSOC.ISOLATE_ID "
." INNER JOIN ENTRIES ON ENTRIES.ENTRY_ID = ISOLATE_ENTRY_ASSOC.ENTRY_ID "
." INNER JOIN ANNOTATIONS ON (ANNOTATIONS.BIOSEQUENCE_ID = ENTRIES.BIOSEQUENCE_ID) "
." INNER JOIN BIOSEQ_MDS_CONVERTED ON (ANNOTATIONS.BIOSEQUENCE_RESULT_ID = BIOSEQ_MDS_CONVERTED.BIOSEQUENCE_ID) WHERE ";
$_SESSION['prefs']['seq_type'] = 'PROTEIN';
$q = str_replace("SEGMENT_NAME","PROTEIN_NAME",$q);
}
elseif(substr($_SESSION['mds_setname'],0,1) == 'P' && !empty($_GET['seq_type']) && $_GET['seq_type'] == 'DNA'){
$q .= " FROM BROWSE_NT_SEQ_2 "
." INNER JOIN ISOLATE_ENTRY_ASSOC ON BROWSE_NT_SEQ_2.ISOLATE_ID = ISOLATE_ENTRY_ASSOC.ISOLATE_ID "
." INNER JOIN ENTRIES ON ENTRIES.ENTRY_ID = ISOLATE_ENTRY_ASSOC.ENTRY_ID "
." INNER JOIN ANNOTATIONS ON (ANNOTATIONS.BIOSEQUENCE_ID = ENTRIES.BIOSEQUENCE_ID) "
." INNER JOIN BIOSEQ_MDS_CONVERTED ON (ANNOTATIONS.BIOSEQUENCE_RESULT_ID = BIOSEQ_MDS_CONVERTED.BIOSEQUENCE_ID) WHERE ";
$_SESSION['prefs']['seq_type'] = 'DNA';
}
else{
if(substr($_SESSION['mds_setname'],-3) == 'int' || substr($_SESSION['mds_setname'],-3) == 'ext' || substr($_SESSION['mds_setname'],-3) == 'all'){
$q .= " FROM ISOLATE_PCA_CONVERTED "
." INNER JOIN BROWSE_NT_SEQ_2 ON ISOLATE_PCA_CONVERTED.ISOLATE_ID = BROWSE_NT_SEQ_2.ISOLATE_ID where ";
}
else{
$q .= " from BROWSE_NT_SEQ_2 "
."INNER JOIN ISOLATE_ENTRY_ASSOC ON BROWSE_NT_SEQ_2.ISOLATE_ID = ISOLATE_ENTRY_ASSOC.ISOLATE_ID "
."INNER JOIN ENTRIES ON ISOLATE_ENTRY_ASSOC.ENTRY_ID = ENTRIES.ENTRY_ID "
."INNER JOIN BIOSEQ_MDS_CONVERTED ON ENTRIES.BIOSEQUENCE_ID = BIOSEQ_MDS_CONVERTED.BIOSEQUENCE_ID where ";
}
}
}
elseif((isset($_REQUEST['H1N1_SWINE_SET']) && ($_REQUEST['H1N1_SWINE_SET'] == 1 || $_REQUEST['H1N1_SWINE_SET'] == '0')) || (isset($H1N1_SWINE_SET) && ($H1N1_SWINE_SET == '0' || $H1N1_SWINE_SET == 1))){
if((isset($_REQUEST['H1N1_SWINE_SET']) && $_REQUEST['H1N1_SWINE_SET'] == 1) || (isset($H1N1_SWINE_SET) && $H1N1_SWINE_SET == 1)){
$q .= " from $browse_table_name inner join ISOLATE_PROPERTIES SWINE_PROP on (SWINE_PROP.ISOLATE_ID = $browse_table_name.ISOLATE_ID and SWINE_PROP.CV_TYPE = 'IV_LINEAGE' and SWINE_PROP.CV_VALUE = 'SWL') where ";
}
else{
$q .= " from $browse_table_name left join ISOLATE_PROPERTIES SWINE_PROP on (SWINE_PROP.ISOLATE_ID = $browse_table_name.ISOLATE_ID and SWINE_PROP.CV_TYPE = 'IV_LINEAGE') where ";
$where .= " (SWINE_PROP.CV_VALUE IS NULL) ";
}
}
else $q .= " from $browse_table_name where ";
$q .= ($where) ? " $where and " : "";
$where = "";
if(!empty($_REQUEST['stats']) && $_REQUEST['stats'] == 'CONT' && $continent = $_REQUEST['geoentity']) $q .= " GEO_ROOT_ID = $continent and ";
if($ISOLATE_IDS){
if(strpos($ISOLATE_IDS,"OFL_ISL_") !== FALSE || strpos($ISOLATE_IDS,"EPI_ISL_") !== FALSE || ereg("^[0-9;+:-\ \n\r]+$",strtolower($ISOLATE_IDS)) !== FALSE){
$ISOLATE_IDS = str_replace(array("OFL_ISL_","EPI_ISL_"),"",$ISOLATE_IDS);
$ISOLATE_IDS = str_replace(array(";","+","-",":","\n","\r"," ","\t","\s"),", ",$ISOLATE_IDS);
$ISL_IDS = explode(",",$ISOLATE_IDS);
$ISL_IDS = array_filter($ISL_IDS,"array_remove_empty_values");
$where .= " ISOLATE_ID in (".(implode(",",$ISL_IDS)).") and ";
}
}
if($SEARCH_STRING){
$from = array("(",")","[","]",".",":","?","!","+"," ");
$to = array('\(','\)','\[','\]','\.','\:','\?','\!','\+','.');
$value = str_replace($from,$to,$SEARCH_STRING);
$names = explode(",",str_replace(array(";","+","-",":","\n","\r"),", ",$value));
if(count($names) > 1){
$where .= " (regexp_like(ISOLATE_NAME, '".(trim($names[0]))."','i')";
for($k = 1; $k < count($names); $k++) if($names[$k]) $where .= " or regexp_like(ISOLATE_NAME, '".(trim($names[$k]))."','i') ";
$where .= ") and ";
}
else $where .= " regexp_like(ISOLATE_NAME, '$value','i') and ";
}
if($COMPLETE) $where .= " COMPLETE_GENOME = 'Y' and ";
if($FROM_COLLECTION_DATE){
if(preg_match("/^[0-9]{4}$/",$FROM_COLLECTION_DATE)) $FROM_COLLECTION_DATE = $FROM_COLLECTION_DATE."-01-01";
elseif(preg_match("/^[0-9]{4}-[0-9]{2}$/",$FROM_COLLECTION_DATE)) $FROM_COLLECTION_DATE = $FROM_COLLECTION_DATE."-01";
if(preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$FROM_COLLECTION_DATE)) $where .= " COLLECT_DATE >= TO_DATE('$FROM_COLLECTION_DATE','yyyy-mm-dd') and ";
}
if($TO_COLLECTION_DATE){
if(preg_match("/^[0-9]{4}$/",$TO_COLLECTION_DATE)) $TO_COLLECTION_DATE = $TO_COLLECTION_DATE."-12-31";
elseif(preg_match("/^[0-9]{4}-[0-9]{2}$/",$TO_COLLECTION_DATE)) $TO_COLLECTION_DATE = $TO_COLLECTION_DATE."-31";
if(preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$TO_COLLECTION_DATE)) $where .= " COLLECT_DATE < TO_DATE('$TO_COLLECTION_DATE','yyyy-mm-dd') + 1 and "; }
if($FROM_SUBMISSION_DATE){
if(preg_match("/^[0-9]{4}$/",$FROM_SUBMISSION_DATE)) $FROM_SUBMISSION_DATE = $FROM_SUBMISSION_DATE."-01-01";
elseif(preg_match("/^[0-9]{4}-[0-9]{2}$/",$FROM_SUBMISSION_DATE)) $FROM_SUBMISSION_DATE = $FROM_SUBMISSION_DATE."-01";
if(preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$FROM_SUBMISSION_DATE)) $where .= " CREATE_DATE >= TO_DATE('$FROM_SUBMISSION_DATE','yyyy-mm-dd') and ";
}
if($TO_SUBMISSION_DATE){
if(preg_match("/^[0-9]{4}$/",$TO_SUBMISSION_DATE)) $TO_SUBMISSION_DATE = $TO_SUBMISSION_DATE."-12-31";
elseif(preg_match("/^[0-9]{4}-[0-9]{2}$/",$TO_SUBMISSION_DATE)) $TO_SUBMISSION_DATE = $TO_SUBMISSION_DATE."-31";
if(preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$TO_SUBMISSION_DATE)) $where .= " CREATE_DATE < TO_DATE('$TO_SUBMISSION_DATE','yyyy-mm-dd') + 1 and ";
}
if(isset($SEQ_LAB) && $SEQ_LAB != '') $where .= " LAB_ID = $SEQ_LAB and ";
if(isset($LABEL_CLADE_ID) && $LABEL_CLADE_ID !== '') $where .= " LABEL_CLADE_ID like '$LABEL_CLADE_ID%' and ";
if($ISOLATE_ID){
if(is_array($ISOLATE_ID) === TRUE){
foreach($ISOLATE_ID as $isolate_key => $isolate_value){
if(!$isolate_value) unset($ISOLATE_ID[$isolate_key]);
}
if(count($ISOLATE_ID)) $where .= " ISOLATE_ID in (".implode(', ',$ISOLATE_ID).") and ";
}
else $where .= " ISOLATE_ID = '$ISOLATE_ID' and ";
}
$MIN_SEQ_LENGTH = preg_replace("/[^0-9]/","",$MIN_SEQ_LENGTH);
if($MIN_SEQ_LENGTH) $where .= " SEQUENCE_LENGTH >= $MIN_SEQ_LENGTH and ";
if($ACCESSION_NUMBER){
if(stripos($ACCESSION_NUMBER,"OFL") !== FALSE||stripos($ACCESSION_NUMBER,"EPI") !== FALSE){
$ACCESSION_NUMBER = explode(";",preg_replace("/[^0-9;]/","",$ACCESSION_NUMBER));
if($ACCESSION_NUMBER) $where .= " ENTRY_ID in (".(implode(", ",$ACCESSION_NUMBER)).") and ";
}
else{
$ACCESSION_NUMBER = explode(";",$ACCESSION_NUMBER);
$where .= " ENTRY_NAME in ('".implode("','",$ACCESSION_NUMBER)."') and ";
}
}
if($PASSAGE_HISTORY){
$from = array("(",")","[","]",".",":","?","!","+");
$to = array('\(','\)','\[','\]','\.','\:','\?','\!','\+');
$value = str_replace($from,$to,$PASSAGE_HISTORY);
$where .= " regexp_like(PASSAGE_HISTORY, '$value','i') and ";
}
if($PRIMARY_DEPOSITION){
if($PRIMARY_DEPOSITION == '1'){
$where .= " LAB_ID IS NOT NULL and ";
}
}
if($EXPORT_STATUS){
if($EXPORT_STATUS == '1'){
$where .= " (LAB_ID IS NULL or $browse_table_name.EXPORT_STATUS IS NOT NULL) and ";
}
}
if($IS_COMPLETE){
if($IS_COMPLETE == '1') $where .= " IS_COMPLETE = 'Y' and ";
}
if($EMPRES_OUTBREAK_ID){
if($EMPRES_OUTBREAK_ID == '1'){
$where .= " EMPRES_OUTBREAK_ID IS NOT NULL and ";
}
}
if($USER_ID){
$where .= " USER_ID = $USER_ID and ";
}
if($_SESSION['prefs']['seq_type'] == 'PROTEIN') $q = str_replace("SEGMENT_NAME","PROTEIN_NAME",$q);
foreach($post_variables as $key => $type){
if($type == 'multiple'){
if(count($$key) > 0){
$trim = 0;
foreach($$key as $indice => $value){
if($key == "SOURCE_NAME"){
if(++$trim == 1) $where .= " SOURCE_ROOT_ID in (";
$where .= "$value, ";
}
elseif($key == "IV_HSUBTYPE"){
if(++$trim == 1) $where .= " HSUBTYPE in (";
$where .= "'".(addslashes($value))."', ";
}
elseif($key == "IV_NSUBTYPE"){
if(++$trim == 1) $where .= " NSUBTYPE in (";
$where .= "'".(addslashes($value))."', ";
}
elseif($key == "IV_LINEAGE"){
if(++$trim == 1) $where .= " IV_LINEAGE in (";
$where .= "'".(addslashes($value))."', ";
}
elseif(strpos($key,"TYPE") !== FALSE){
if(++$trim == 1) $where .= " IV_TYPE in (";
$where .= "'$value', ";
}
elseif($key == "COUNTRY" && (empty($_GET[$prefix.'geoentity']))){
if(++$trim == 1) $where .= " COUNTRY_ID in (";
$where .= "'$value', ";
}
elseif($key == "SEGMENT"){
$print_segments[] = $value;
if(++$trim == 1) $where .= " SEGMENT_NAME in (";
$where .= "'".(addslashes($value))."', ";
}
}
if($trim){
$where = substr($where,0,-2);
$where .= ") and ";
}
}
}
}
if(!array_key_exists("SOURCE_NAME",$post_variables)){
$q1 = "SELECT ASCENDANT_ID FROM SOURCE_ASSOC WHERE ASCENDANT_ID = DESCENDANT_ID AND DISTANCE = 0";
$r1 = ExecRequeteNC($q1);
$sources = array();
while($s1 = LigneSuivante($r1)){
$sources[] = $s1->ASCENDANT_ID;
}
$where .= " SOURCE_ROOT_ID in (".(implode(", ",$sources)).") and ";
}
if(!array_key_exists("COUNTRY",$post_variables) || !count($COUNTRY)){
$where .= " GEO_ROOT_ID in (".(implode(", ",$GEO_ROOT_ID)).") and ";
}
if(!empty($_REQUEST['FROMSSM'])) $_REQUEST['QUICK_SEARCH'] = $_REQUEST['FROMSSM'];
######### QUICK SEARCH ############
if(!empty($_REQUEST['QUICK_SEARCH'])){
$quick_fields = array("ofl_isl_id" => "ISOLATE_ID","epi_id" => "ENTRY_ID","entry_name" => "ENTRY_NAME","isolate_name" => "ISOLATE_NAME");
foreach($quick_fields as $quick_field => $quick_sql) ${"quick_".$quick_field} = array();
$quick_wheres = array();
$quick_terms = explode(";",str_replace(array(",",":","\n","\r"),";",$_REQUEST['QUICK_SEARCH']));
foreach($quick_terms as $quick_term){
$quick_term = trim($quick_term);
if($quick_term){
if((strpos($quick_term,"OFL_ISL_") !== FALSE) || (strpos($quick_term,"EPI_ISL_") !== FALSE)) $quick_ofl_isl_id[] = str_replace(array("OFL_ISL_","EPI_ISL_"),"",$quick_term);
elseif(ereg("OFL([0-9]+)",$quick_term,$eregs) !== FALSE) $quick_epi_id[] = $eregs[1];
elseif(ereg("[A-C]\/[^\/]+\/[^\/]+",$quick_term) !== FALSE) $quick_isolate_name[] = "'".(strtolower($quick_term))."'";
else $quick_entry_name[] = "'$quick_term'";
}
}
foreach($quick_fields as $quick_field => $quick_sql){
if($quick_field == 'isolate_name') $quick_sql = "LOWER($quick_sql)";
if(count(${"quick_".$quick_field})){
if($quick_sql == 'ENTRY_ID'){
foreach(${"quick_".$quick_field} as $tmp){
ExecRequeteNC("INSERT into TMP_BROWSE_IDS (IDS) values ($tmp)");
}
CommitIt();
$q = str_replace("from $browse_table_name","from $browse_table_name inner join TMP_BROWSE_IDS on $quick_sql = TMP_BROWSE_IDS.IDS",$q);
}
else $quick_wheres[] = "$quick_sql in (".implode(",",${"quick_".$quick_field}).")";
}
}
if(count($quick_wheres)) $where .= "(".(implode(" or ",$quick_wheres)).") ";
else $where = rtrim($where,"and ");
}
####### END QUICK SEARCH ##########
elseif($where) $where = substr($where,0,-5);
else $q = substr($q,0,-5);
###### Check read access #######
$logged_user = new user($user_id);
if($logged_user->IS_WHO != 'Y') $where .= " and (IS_PUBLIC = 'Y')";
// if($is_general_user) $where .= " and (LAB_ID IS NULL or EXPORT_STATUS IS NOT NULL or USER_ID = $user_id)";
// $where .= " and (LAB_ID IS NULL or $browse_table_name.EXPORT_STATUS = 'CONFIRMED' or $browse_table_name.EXPORT_STATUS = 'FALSE_EXPORT')";
# if($logged_user->IS_ANNOTATOR != 'Y' && !$SEQ_LAB) $where .= " and (SEQS_BY_ISOLATE.LAB_ID = '".$logged_user->LAB_ID."') ";
if(!empty($_GET[$prefix.'geoentity'])){
$geoentity_level = (!empty($_REQUEST['stats'])) ? $_REQUEST['stats'] : "GEOENTITY_ID";
if($geoentity_level != 'CONT') $where .= " and $geoentity_level = ".$_GET['geoentity']." ";
}
$q .= $where;
if($_SESSION['prefs']['seq_type'] == 'PROTEIN') $q = str_replace("BROWSE_NT_SEQ_2","BROWSE_AA_SEQ_2",$q);
// if($_SESSION['prefs']['seq_type'] == 'PROTEIN') $q = str_replace("SEQS","PROTS",$q);
if($estimate && $_SESSION['ESTIMATE_MAP'] == 1) $q .= "group by COUNTRY_NAME";
return $q;
}
}
if(isset($_SESSION['segments'])) unset($_SESSION['segments']);
$estimate = (!empty($_REQUEST['estimate'])) ? $_REQUEST['estimate'] : "";
$store_query = (!empty($_REQUEST['store_query'])) ? $_REQUEST['store_query'] : "";
$query_builder = (!empty($_REQUEST['builder'])) ? $_REQUEST['builder'] : 0;
if($store_query) $estimate = 0;
#### Get POST variables ########
if($query_builder == 1){
$queries = array();
foreach($_SESSION['queries'] as $QID => $query_id){
if(!empty($_REQUEST[$query_id]) && $_REQUEST[$query_id] == 1 && $_SESSION['selected_queries'][$QID]){
$queries[] = prepare_query("Q".$QID."_");
}
}
$combine = (!empty($_REQUEST['combine'])) ? $_REQUEST['combine'] : "UNION";
$q = implode(" $combine ",$queries);
}
else $q = prepare_query();
if($estimate){
// error_log($q);
if($debug) error_log("ESTIMATE: ".$q);
$r = ExecRequeteNC($q);
$NB = 0;
$TOTAL = 0;
$data_geo = array();
$min_geo = 0;
$max_geo = 0;
if($_SESSION['ESTIMATE_MAP'] == 1){
while($s = LigneSuivante($r)){
$NB += $s->NB;
$TOTAL += $s->TOTAL;
// $_SESSION['browse_query'] = $q;
if(isset($iso_countries[strtoupper($s->COUNTRY_NAME)])){
$iso = $iso_countries[strtoupper($s->COUNTRY_NAME)];
$data_geo[$iso] = $s->NB;
if(!$min_geo || $min_geo > $s->NB) $min_geo = $s->NB;
if($max_geo < $s->NB) $max_geo = $s->NB;
}
}
if(!$store_query){
echo "approx. ".$NB." isolates (".$TOTAL." sequences)::::";
$colors = "";
$max = 10;
$gradient = $min_geo." ";
for($i = 1; $i < $max; $i++){
$color= makeColorGradient($max,$i,3,1,6.5);
$color = str_replace("#","",substr(strrev(strstr(strrev($color), strrev(":"))), 0, -strlen(":")));
$colors .= ",".$color;
$gradient .= "
█";
}
$gradient .= " $max_geo";
$geo_continent="world";
$geo_continents = array(
17=>"africa",
132=>"asia",
6907=>"europe",
10328=>"south_america",
);
if(!empty($_SESSION['prefs']['continents']) && count($_SESSION['prefs']['continents']) == 1){
reset($_SESSION['prefs']['continents']);
$continent = key($_SESSION['prefs']['continents']);
$geo_continent = isset($geo_continents[$continent]) ? $geo_continents[$continent] : "world";
}
echo "
![world map](http://chart.apis.google.com/chart?chs=300x150&chd=t:".(implode(",",array_values($data_geo)))."&cht=t&chtm=$geo_continent&chf=bg,s,F7F8FA&chco=FFFFFF$colors&chld=".(implode("",array_keys($data_geo))).")
hide map$gradient
\n";
}
else echo "";
$_SESSION['WARNING_NB'] = $NB;
}
else{
if($s = LigneSuivante($r)){
if(!$store_query) echo "approx. ".$s->NB." isolates (".$s->TOTAL." sequences)";
$_SESSION['WARNING_NB'] = $s->NB;
// $_SESSION['browse_query'] = $q;
}
else echo "";
}
}
else{
$q .= " order by ISOLATE_ID, SEGMENT, SEQUENCE_LENGTH asc";
##### DEBUG
if($debug){
echo "
$q
";
// error_log($q);
}
$_SESSION['browse_query'] = $q;
############
// error_log("START: ".time());
if($debug < 2) $r = ExecRequeteNC($q);
else exit;
$segments = array();
$fields = array("isolate_id" => "ISOLATE_ID",
"action" => "action",
"name" => "ISOLATE_NAME",
"subtype" => "ISOLATES_PROPS.IV_TYPE",
"collect year" => "COLLECT_YEAR",
"passage" => "PASSAGE_HISTORY",
"submission date" => "CREATE_DATE",
"host" => "SOURCE_NAME",
"country" => "GEODIV_NAME"
);
$IV_TYPE = (!empty($_REQUEST['IV_TYPE'])) ? $_REQUEST['IV_TYPE'] : array("A","B","C");
// if(count($IV_TYPE) == 0) $IV_TYPE = array();
if(in_array("-ALL-",$IV_TYPE) && count($IV_TYPE == 1)) $IV_TYPE = array("A","B","C");
$all_segments = array(); $all_segments["A"] = array(); $all_segments["B"] = array(); $all_segments["C"] = array();
if($_SESSION['prefs']['seq_type'] == 'PROTEIN'){
foreach($IV_TYPE as $key => $value){
$q1 = "select * from IV_PROTEIN where IV_TYPE = '$value' order by DISPLAY_ORDER";
$r1 = ExecRequeteNC($q1);
while($s1 = LigneSuivante($r1)){
if(!count($print_segments) || in_array($s1->SEGMENT_NAME,$print_segments)){
$s1->PROTEIN_NAME = str_replace(" (NS2)","",$s1->PROTEIN_NAME);
$fields[$s1->PROTEIN_NAME] = $s1->PROTEIN_NAME;
$segments[$s1->DISPLAY_ORDER] = $s1->PROTEIN_NAME;
}
}
oci_free_statement($r1);
}
}
else{
foreach($IV_TYPE as $key => $value){
$q1 = "select * from IV_SEGMENT where IV_TYPE = '$value' order by DISPLAY_ORDER";
$r1 = ExecRequeteNC($q1);
while($s1 = LigneSuivante($r1)){
$all_segments[$value][] = $s1->SEGMENT_NAME;
if(!count($print_segments) || in_array($s1->SEGMENT_NAME,$print_segments)){
$fields[$s1->SEGMENT_NAME] = $s1->SEGMENT_NAME;
$segments[$s1->DISPLAY_ORDER] = $s1->SEGMENT_NAME;
}
}
oci_free_statement($r1);
}
}
$_SESSION['segments'] = $segments;
// $_SESSION['isolate'] = array();
$table = new table('search_result');
$table->filter = 1;
$table->order = 'CREATE_DATE';
$table->sense = "DESC";
$table->set_nb_rows = 1;
$table->styleclass = 'small_font';
foreach($fields as $print => $field){
if($field == 'action'){
$$field = new table_field('action',"search_result");
$$field->print = "
";
$$field->onclick = "onclick = \"check_all_in_table('search_result','isolate',$('isolate_form_check_all').checked);update_nb_isolate();\"";
}
elseif($field == 'CREATE_DATE'){
$print = "
";
$print .= "
";
$$field = new table_field($field,"search_result");
$$field->onclick = "";
$$field->styleclass = 'center';
$$field->print = $print;
$$field->hidden = 1;
}
elseif($field == 'COLLECT_YEAR'){
$print = "
";
$print .= "
";
$$field = new table_field($field,"search_result");
$$field->onclick = "";
$$field->styleclass = 'center';
$$field->print = $print;
$$field->hidden = 1;
}
elseif($field == 'PASSAGE_HISTORY'){
$print = "
";
$print .= "
";
$$field = new table_field($field,"search_result");
$$field->onclick = "";
$$field->styleclass = 'center';
$$field->print = $print;
}
elseif($field == 'SOURCE_NAME'){
$print = "
";
$print .= "
";
$$field = new table_field($field,"search_result");
$$field->print = $print;
$$field->onclick = "";
$$field->styleclass = 'center';
$$field->hidden = 1;
}
else{
$$field = new table_field($field,"search_result");
$$field->print = $print;
if($print != 'name') $$field->styleclass ='center';
if($print == 'isolate_id') $$field->hidden = 1;
}
$table->fields[] = $$field;
}
$old_isolate_id = "";
$nb = 0;
$map_coord = array();
$found = 0;
$present_segments = array();
if($_SESSION['prefs']['seq_type'] == 'PROTEIN'){
$qsp = "select PROTEIN_NAME,SEGMENT_NAME,IV_TYPE from IV_PROTEIN";
$rsp = ExecRequeteNC($qsp);
while($ssp = LigneSuivante($rsp)){
if(!is_array($seg2prot[$ssp->IV_TYPE])) $seg2prot[$ssp->IV_TYPE] = array();
if(!is_array($seg2prot[$ssp->IV_TYPE][$ssp->SEGMENT_NAME])) $seg2prot[$ssp->IV_TYPE][$ssp->SEGMENT_NAME] = array();
$seg2prot[$ssp->IV_TYPE][$ssp->SEGMENT_NAME][] = $ssp->PROTEIN_NAME;
}
}
$all_lineages = array();
$qlin = "select DISPLAY_LABEL,CV_VALUE from CV_PROPERTY_VALUE where PROPERTY_TYPE = 'IV_LINEAGE'";
$rlin = ExecRequeteNC($qlin);
while($slin = LigneSuivante($rlin)) $all_lineages[$slin->CV_VALUE] = $slin->DISPLAY_LABEL;
$current_iv_type = "";
while($s = LigneSuivante($r)){
$current_iv_type = $s->IV_TYPE;
if($old_isolate_id != $s->ISOLATE_ID){
$found = 1;
if($old_isolate_id){
$ok = 1;
if(count($required_segments)){
foreach($required_segments as $seg){
if($_SESSION['prefs']['seq_type'] == 'PROTEIN'){
if(is_array($seg2prot[$s->IV_TYPE]) && is_array($seg2prot[$s->IV_TYPE][$seg])){
$ok_prot = 0;
foreach($seg2prot[$s->IV_TYPE][$seg] as $PROTEIN_NAME){
if($row->cols[str_replace(" (NS2)","",$PROTEIN_NAME)] != '-' && strlen($row->cols[str_replace(" (NS2)","",$PROTEIN_NAME)]) > 0) $ok_prot = 1;
if(!$ok_prot) $ok = 0;
}
}
}
elseif(in_array($seg,$all_segments[$s->IV_TYPE]) && $row->cols[$seg] == '-'){ $ok = 0;}
}
}
if($ok){
$map_coord[] = $s->LONGITUDE."::".$s->LATITUDE."::"."$s->ISOLATE_NAME"."::".$s->ISOLATE_ID;
$table->rows[] = $row;
if(++$nb >= $display_limit-1) break;
}
}
// elseif($nb==0) $nb=1;
$row = new table_row();
$row->styleclass = (($logged_user->LAB_ID == $s->LAB_ID && $s->LAB_ID) || $logged_user->USER_ID == $s->USER_ID) ? 'highlight_hover_red' : "highlight_hover";
$link_edit = ($logged_user->LAB_ID == $s->LAB_ID && $s->LAB_ID) ? "
" : "";
$year = $s->COLLECT_YEAR;
$checked = (!empty($_SESSION['isolate']) && is_array($_SESSION['isolate']) && in_array($s->ISOLATE_ID,$_SESSION['isolate'])) ? "checked = 'checked'" : "";
$row->cols['action'] = "
ISOLATE_ID); else remove_session_value('isolate[$s->ISOLATE_ID]'); update_nb_isolate();\" $checked />";
$row->cols['ISOLATE_ID'] = $s->ISOLATE_ID;
$row->cols['ISOLATE_NAME'] = "
$s->ISOLATE_NAME".$s->ISOLATE_NAME."$link_edit";
if($s->IV_TYPE == 'A'){
if(in_array("A",$IV_TYPE) !== FALSE){
$iv_hsubtype = ($s->HSUBTYPE) ? "H".$s->HSUBTYPE : "";
$iv_nsubtype = ($s->NSUBTYPE) ? "N".$s->NSUBTYPE : "";
$iv_subtype = $iv_hsubtype.$iv_nsubtype;
if($iv_lineage = $s->IV_LINEAGE) $iv_subtype .= " ".$all_lineages[$iv_lineage];
if($iv_subtype == 'HN' || !$iv_subtype){
$isolate = new isolate($s->ISOLATE_ID);
$iv_subtype = $isolate->get_subtype(1);
}
}
else{
$iv_subtype = $s->IV_LINEAGE;
// $isolate = new isolate($s->ISOLATE_ID);
// $iv_subtype = $isolate->get_subtype(1);
}
$row->cols['ISOLATES_PROPS.IV_TYPE'] = $iv_subtype;
}
else $row->cols['ISOLATES_PROPS.IV_TYPE'] = $all_lineages[$s->IV_LINEAGE];
$row->cols['COLLECT_YEAR'] = "
".$year."";
$row->cols['CREATE_DATE'] = "
".$s->CREATE_DATE."";
$row->cols['PASSAGE_HISTORY'] = "
".$s->PASSAGE_HISTORY."";
$row->cols['SOURCE_NAME'] = "
".$s->HOST."";
$row->cols['GEODIV_NAME'] = "
".$s->GEODIV_NAME."";
foreach($segments as $seg_order => $seg_name) $row->cols[$seg_name] = "-";
$row->cols[$s->SEGMENT] = ($s->IS_COMPLETE == 'Y') ? "
".$s->SEQUENCE_LENGTH."" : $s->SEQUENCE_LENGTH;
$old_isolate_id = $s->ISOLATE_ID;
$present_segments[$s->SEGMENT] = 1;
}
else{
$row->cols[$s->SEGMENT] = ($s->IS_COMPLETE == 'Y') ? "
".$s->SEQUENCE_LENGTH."" : $s->SEQUENCE_LENGTH;
$present_segments[$s->SEGMENT] = 1;
}
}
if($found == 1 && $nb == 0) $nb = 1;
$ok = 1;
if(count($required_segments)){
foreach($required_segments as $seg){
if($_SESSION['prefs']['seq_type'] == 'PROTEIN'){
if(is_array($seg2prot[$current_iv_type]) && in_array($seg,$seg2prot[$current_iv_type])){
$PROTEIN_NAME = $seg2prot[$current_iv_type][$seg];
$ok_prot = 0;
if($row->cols[str_replace(" (NS2)","",$PROTEIN_NAME)] != '-' && strlen($row->cols[str_replace(" (NS2)","",$PROTEIN_NAME)]) > 0) $ok_prot = 1;
if(!$ok_prot) $ok = 0;
}
}
elseif(in_array($seg,$all_segments[$current_iv_type]) && $row->cols[$seg] == '-'){ $ok = 0;}
}
}
if($nb && $ok){ $table->rows[] = $row;}
// echo $q;
if(!empty($_SESSION['refsets'])){
$_SESSION['tablesearch_result'] = serialize($table);
$refset_action = (!empty($_SESSION['refset_action'])) ? $_SESSION['refset_action'] : "add";
if($refset_action == 'load') $table->rows = array();
foreach($_SESSION['refsets'] as $refset_id){
$refset = new reference_set($refset_id);
$out = $refset->add_to_browse_table("search_result",$refset_action);
}
$table = unserialize($_SESSION['tablesearch_result']);
}
$required_segments = array();
$print_segments = array();
$vars = (!empty($_GET['stats'])) ? $_GET : $_POST;
$method = (!empty($_GET['stats'])) ? "get" : "post";
echo "
\n";
echo "
\n";
ob_start();
if(count($table->rows) == $display_limit) echo "
Your query has returned more than $display_limit isolates. Only the first $display_limit are displayed. You may want to refine your filters.
\n";
echo $table->print_header();
echo $table->print_body();
echo $table->print_footer();
echo "
".(count($_SESSION['isolate'])) ." isolate".((count($_SESSION['isolate'])>1) ? "s" : "")." selectedcomplete ".(($_SESSION['prefs']['seq_type'] == 'PROTEIN')?"proteins":"CDS")." in bold ";
if(check_submitter()) echo "| highlight my isolates\n";
echo "
";
$download = "";
if(count($table->rows)){
// ob_start();
$seq_type = ($_SESSION['prefs']['seq_type'] == 'PROTEIN') ? "proteins" : "segments";
$download .= "
\n";
$download .= "
\n";
######## ANALYISIS
$download .= "
Analysis:
\n";
$download .= "
\n";
// $download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
";
##### DOWNLOADS ##
$download .= "
\n";
$download .= "
Downloads:
\n";
$download .= "
selected isolates
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
ALL isolates
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
";
######## DONWLOADS OPTIONS ##########
$download .= "
\n";
###### REFERENCE SETS ##
if(!$is_general_user){
$download .= "
\n";
$download .= "
Reference sets:
\n";
$download .= "
selected isolates
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
ALL isolates
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
From a file
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
\n";
$download .= "
";
}
$download .= "
\n";
$download .= "
";
$download .= "
\n";
$download .= "
\n";
}
$user = new user($user_id);
$nb_refs = count($user->reference_sets());
$title = "Search result ";
if(!$is_general_user) $title .= "
my reference sets (".$nb_refs.") 
";
$print_isolates = ($nb > 1) ? "isolates" : "isolate";
$content = "
\n";
$content .= "
\n";
echo "
\n";
echo "
\n";
echo "
\n";
if((!empty($_REQUEST['stats']) && empty($_GET['seq_type']))||!empty($_GET['ISOLATE_ID']) || !empty($_GET['epi']) || !empty($_GET['QUICK_SEARCH']) || !empty($_GET['create_date']) || !empty($_GET['H1N1_SWINE_SET'])){
if(!empty($_REQUEST['epi'])){
$ACCESSION_NUMBER = $_REQUEST['epi'];
if(strpos($ACCESSION_NUMBER,';') === FALSE){
if(stripos($ACCESSION_NUMBER,"OFL") !== FALSE || stripos($ACCESSION_NUMBER,"EPI") !== FALSE){
$ACCESSION_NUMBER = str_ireplace(array("EPI","OFL"),"",$ACCESSION_NUMBER);
$q = "select ISOLATE_ID from BROWSE_NT_SEQ_2 where ENTRY_ID = $ACCESSION_NUMBER";
}
else $q = "select ISOLATE_ID from BROWSE_NT_SEQ_2 where ENTRY_NAME = '$ACCESSION_NUMBER'";
############ Check Read Access ##################
if($logged_user->IS_WHO != 'Y') $q .= " and (IS_PUBLIC = 'Y')";
// if($is_general_user) $q .= " and (LAB_ID IS NULL or EXPORT_STATUS IS NOT NULL or USER_ID = $user_id)";
// $q .= " and (LAB_ID IS NULL or BROWSE_NT_SEQ_2.EXPORT_STATUS = 'CONFIRMED' or BROWSE_NT_SEQ_2.EXPORT_STATUS = 'FALSE_EXPORT')";
$r = ExecRequeteNC($q);
if($s = LigneSuivante($r)) echo "\n";
}
}
if(!empty($_REQUEST['ISOLATE_ID']) && strpos($_REQUEST['ISOLATE_ID'],";") === FALSE){
echo "\n";
}
echo "
\n";
footer();
}
$_SESSION['map_coords'] = $map_coord;
oci_free_statement($r);
}
}
}
?>
";
}
else $icon = "
";
echo "
back to form
