import_NS_data_sample.php

/Prohits/analyst/import_NS_data_sample.php

http://prohits.googlecode.com/
PHP | 470 lines | 401 code | 23 blank | 46 comment | 77 complexity | b7ece2eafb19776baf96637eaa87b137 MD5 | raw file

<?php
/***********************************************************************
 Copyright 2010 Gingras and Tyers labs, 
 Samuel Lunenfeld Research Institute, Mount Sinai Hospital.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*************************************************************************/
set_time_limit(3600*2);


$frm_username = '';
$PHP_SELF = '';
$frm_password = '';
$frm_table = '';
$theaction = '';
$frm_host = '';
$mysql_link = '';
$frm_db = '';
$frm_delimiter = '';

require_once("../msManager/is_dir_file.inc.php");

if( $_SERVER['REQUEST_METHOD'] == "POST"){
  $request_arr = $_POST;
}else{
  $request_arr = $_GET;
}
foreach ($request_arr as $key => $value) {
  $$key=$value;
}


$username = $frm_username;
$dbpassword = $frm_password;
$host = $frm_host;
$table_name = $frm_table;

if($username and $host and $dbpassword) {
  $mysql_link=mysql_pconnect($host, $username, $dbpassword);
}
if($frm_table and $frm_db and $mysql_link){
  mysql_select_db($frm_db, $mysql_link);
}
if($frm_table and $frm_db){
  //get table structure info
  $table_name = $frm_table;
  $SQL = "select * from $table_name limit 1 ";
	$result = mysql_query ($SQL);
	$fields = mysql_num_fields ($result);
	$rows   = mysql_num_rows ($result);
	$i = 0;
	$table = mysql_field_table ($result, $i);

	//echo "<b>".$table."</b> table has ".$fields." fields and ".$rows." records <BR>";
  //echo "The table has the following fields <BR>";
	while ($i < $fields) {
	    $type[$i]  = mysql_field_type  ($result, $i);
	    $name[$i]  = mysql_field_name  ($result, $i);
	    $len[$i]   = mysql_field_len   ($result, $i);
	    $flags[$i] = mysql_field_flags ($result, $i);
	    //echo $type[$i]." ".$name[$i]." ".$len[$i]." ".$flags[$i]."<BR>";
	    $i++;
	}
}
//preview
if($theaction == 'preview' and $frm_import_file_name){
    
    $tmp_import_file = "./tmp/tmp_import_file_$REMOTE_ADDR.txt";
    $myshellcmd = "mv " . $frm_import_file . " $tmp_import_file";
    $uploaded_file_name = $frm_import_file_name;
    exec($myshellcmd);
		if($frm_other_delimiter){
			$delimiter = $frm_other_delimiter;
		}else{
			$delimiter = $frm_delimiter;
		}
    //process fields_enclose charactor
		if($frm_fields_enclosed_by){
      $frm_fields_enclosed_by = stripslashes($frm_fields_enclosed_by);
      $fd = fopen ($tmp_import_file, "r");
      $fd_tmp = fopen("./tmp/tmp_import_file_$REMOTE_ADDR.modified.txt", "w");
      //go through the oraginal file and remove new line char between fields enclosed chars
      while (!feof ($fd) ) {
			  $buffer = fgets($fd, 4096);
			  $buffer_tmp = trim($buffer);
        if($buffer_tmp){
          $buffer = str_replace("\n","",$buffer);
          //this line has fields enclosed char
          //if find 2 '"' in same line delete '"'
          if(strchr($buffer, $frm_fields_enclosed_by)){
           
            if(strrpos($buffer,'"') !=strpos($buffer,'"')){ 
              $buffer = str_replace('"','',$buffer); 
            }
          }
          if(strchr($buffer, $frm_fields_enclosed_by)) {
             if(!$enclosed_start) {
                $enclosed_start = 1;
                $enclosed_sotp = 0;
             }else {
                $enclosed_start = 0;
                $enclosed_sotp = 1;
             }
          }
          if($enclosed_start) {
            fwrite($fd_tmp, $buffer . "AAAFRANKAAA");
          }else{
             fwrite($fd_tmp, $buffer . "\n");
          }
        }
      }
       fclose ($fd);
       fclose($fd_tmp);
       $tmp_import_file = "./tmp/tmp_import_file_$REMOTE_ADDR.modified.txt";
       //echo $tmp_import_file;
    }
    
    //*******************************************
   if($table_name == "UploadMDS" or $table_name == "UploadMDS2" ){
     require("../classes/yeastDB_class.php");
     require("../db/dbstart.php");
     $YeastDB = new YeastDB();
   }
   $fd = fopen ($tmp_import_file, "r");
   $out_preview =  "<table border=1>";
    
   //only display 20 records in preview
   while (!feof ($fd) and $stop!=20) {
     $buffer = fgets($fd, 4096);
     $row = explode($delimiter, $buffer);
     if($buffer){
	$out_preview .= "<tr>";
	for($i=0; $i < count($row); $i++){
           //============ for UploadMDS only ================= 
           if($table_name == "UploadMDS"  or $table_name == "UploadMDS2"){
               if(strstr($row[$i], "ID #:")){
                 $new_Bait = trim(strtoupper($row[$i+1]));
                 $new_Bait = str_replace("-E","",$new_Bait);
                 $new_Bait = str_replace("-PRE","",$new_Bait);
                 $YeastDB->fetchORForGene($new_Bait);
                 if(!$YeastDB->ORFName){
                    $err_msg .= display_update_yeastDB($new_Bait,'');
                 }
               }else if(preg_match ("/gi\|[0-9]*/", $row[$i], $tmp)){
                  $tmp_arr = explode("AAAFRANKAAA",$row[$i]);
                  for($j=0;$j<count($tmp_arr);$j++){
                    if(preg_match ("/gi\|[0-9]*/", $tmp_arr[$j], $tmp)){
                      $new_GI = str_replace("gi|","",$tmp[0]);
                      $tmp_YeastDB = new YeastDB();
                      $tmp_YeastDB->fetch( '','',$new_GI);
                      if(!$tmp_YeastDB->ORFName){
                         $err_msg .= display_update_yeastDB('',$new_GI);
                      }
                    }
                  }//end for
               }
           }
           //==================================================
 
           $out_preview .= "<td>$row[$i]&nbsp;</td>";
	}
	$out_preview .= "</tr>";
	//$stop++;
     }//end if buffer
  }//end while
  $out_preview .= "</table>";
  if($table_name == "UploadMDS" or $table_name == "UploadMDS2"){
     $out_preview .= "Please click <a href='add_new_yeastDB.php' target=_blank>Here</a> to update the local yeast database.<br>"; 
     $out_preview .= $err_msg;
  }
  $out_preview .= "<br><font color=red><b>This is the end of uploaded file</b></font>";
  fclose ($fd);
}
if($theaction == 'insert' and _is_file($tmp_import_file)){
	  if($frm_other_delimiter){
			$delimiter = $frm_other_delimiter;
		}else{
			$delimiter = $frm_delimiter;
		}
		$fd = fopen ($tmp_import_file, "r");
	  //empty the table
  //**********************************
  if($table_name == "UploadMDS" or $table_name == "UploadMDS2"){
  //**********************************
    mysql_query("delete from $table_name");
		while (!feof ($fd) ) {
			$buffer = fgets($fd, 4096);
			//$buffer = trim($buffer);
			$row = explode($delimiter, $buffer);
			//echo $buffer;
			$SQL = "insert into $table_name set";
			for($i=0; $i < count($name); $i++){
				if($i){
					$SQL .= ",";
				}
				$tmp_value = addslashes(trim($row[$i]));
				$SQL .= " $name[$i]='$tmp_value'";
			}
			
			if($buffer){
			  mysql_query($SQL);
		   	  //echo $SQL."<br>";
			}
	  }//end while
  //******************
  }//end if UploadMDS
  //******************
	  fclose ($fd); 
		$successful_msg = "Data has been imported successully";
}
?>
<html>
<head><title>Import data</title>
<script language='javascript'>

function db_selected(){
  document.forms[0].submit();
}
function table_selected(){  
  var sel = document.forms[0].frm_table;
  if(sel.options[sel.selectedIndex].value == ''){
		alert("Pleast select a table!");
	} else {
		document.forms[0].submit();
	}
}
function preview(){
	document.forms[0].theaction.value = 'preview';
	if(document.forms[0].frm_import_file.value == ''){
		alert("pleas upload a file!");
		return false;
	}
	if(!checkDelimiter() && document.forms[0].frm_other_delimiter.value == ''){
		alert("Please select a delimiter!");
		return false;
	}
	document.forms[0].submit();
}
function checkDelimiter() {
  for (var i=0; i < document.forms[0].frm_delimiter.length; i++) {
    if (document.forms[0].frm_delimiter[i].checked){
      return true;
		}
  }
  return false;
}
function clearRadio(){
	for (var i=0; i < document.forms[0].frm_delimiter.length; i++) {
     document.forms[0].frm_delimiter[i].checked = false;
      
  }
}
function saveToMysql(){
	document.forms[0].theaction.value = 'insert';
	document.forms[0].submit();
}
</script>
</head>
<body>
<h1><font face="Arial" color=red>Import Data Into MYSQL database</font></h1>
<pre>
This is a tool which can import data from text file. Before you run 
this script you should create table in your mysql database. and make
sure thate field type match the text file.
</pre>
 <br><strong><font size="+1">Step 1: Select database and table</font></strong>
<form method=post name=db_form action='' enctype="multipart/form-data">
<input type=hidden name=frm_host value='<? echo $frm_host; ?>'>
<input type=hidden name=frm_username value='<? echo $frm_username; ?>'>
<input type=hidden name=frm_password value='<? echo $frm_password; ?>'>
<input type=hidden name=theaction value=''>
<input type=hidden name=uploaded_file value='<?=$uploaded_file_name;?>'>
<input type=hidden name=tmp_import_file value='<?=$tmp_import_file;?>'>
<table border=3>
<? if(!$mysql_link) { ?>
  <tr>
    <td>
      Host Name:
	</td>
	<td>
	  <input name=frm_host value='<?= ($frm_host)?$frm_host:"localhost"; ?>'>
	</td>
  </tr>
  <tr>
	<td>
	User Name:
	</td>
	<td>
	  <input name=frm_username value=<? echo $frm_username; ?>>
	</td>
  </tr>
  <tr>
    <td>
      Password:
	</td>
	<td>
	  <input type=password name=frm_password value=<? echo $frm_password; ?>>
	</td>
  </tr>

<? }else{ ?>
  <tr>
	<td>
	  Select Database:
    </td>
	<td>
     <select name="frm_db" onchange="javascriopt: db_selected()">
	  <option value=''>--select database--
<?
if($mysql_link){
  $db_list = mysql_list_dbs($mysql_link);
   while ($row = mysql_fetch_array($db_list)) {
     if($frm_db == $row[0]){
       echo "<option value='".$row[0]."' selected>".$row[0]."\n";
	 }else{
	   echo "<option value='".$row[0]."' >".$row[0]."\n";
	 }
   }
}
?>
    </select>
	</td>
  </tr>
  <tr>
	<td>
      Select Table:
	</td>
    <td>
	  <select name="frm_table">
     <option value=''>--select table--
<?
if($mysql_link){
  if($frm_db){
	  $tables=@mysql_list_tables( $frm_db, $mysql_link);
	  while ($bla=@mysql_fetch_array($tables)) {
	     //echo $bla." sind die tabellennamen";
		 if($frm_table == $bla[0]){
	       echo "<option value='".$bla[0]."' selected>".$bla[0]."\n";
		 }else{
		   echo "<option value='".$bla[0]."' >".$bla[0]."\n";
		 }

	   }//end while
   }
}
?>
   </select>
   </td>
   </tr>
<? } ?>
   <tr>
     <td colspan=2 align=center>
<? if($mysql_link){ ?>
     <input type=button value="Select Table" onClick="javascript: table_selected();">
<? }else{ ?>
	 <input type=button value="Connect to Mysql" onClick="document.forms[0].submit();">
<? } ?>
     </td>
   </tr>
  </table>
<?if($frm_table){?>
   <br><strong><font size="+1">Step 2: Upload text file</font></strong>
  <table border = 1>
	<tr>
		<td>upload data file: </td>
		<td><input type='file' name='frm_import_file' size='30'></td>
	</tr>
	<tr>
		<td>Field delimiter:</td>
		<td><input type=radio name=frm_delimiter value='	' <?=($frm_delimiter=='	' or !$frm_delimiter)?'checked':''; if(!$frm_delimiter) ?>> Tab &nbsp;
				<input type=radio name=frm_delimiter value=';' <?=($frm_delimiter==';')?'checked':'';?>>Semicolon &nbsp;
 				<input type=radio name=frm_delimiter value=',' <?=($frm_delimiter==',')?'checked':'';?>> Comma &nbsp;
				<input type=radio name=frm_delimiter value=' ' <?=($frm_delimiter==' ')?'checked':'';?>>Space &nbsp;
				<input type=text name=frm_other_delimiter value='<?=stripslashes($frm_other_delimiter);?>' size=1 onFocus="javascript:clearRadio();">Other &nbsp;
		</td>
	</tr>
  <tr>
    <td>Filed encloased by</td>
    <td>
      <select name=frm_fields_enclosed_by>
          <option value="">none
          <option value='"' <?=($frm_fields_enclosed_by=='\"' or !$frm_fields_enclosed_by)?'selected':'';?>>"
          <option value="'" <?=($frm_fields_enclosed_by=="\'")?'selected':'';?>>'
          <option value="|" <?=($frm_fields_enclosed_by=='|')?'selected':'';?>>|
      </select>
     </td>     
  </tr>
	<tr>
		<td colspan=2 align=center><input type=button value='Preview' onClick="javascript: preview()">
		</td>
	</tr>
</table>
<?}?>
<?if($theaction == 'preview' and is_file($tmp_import_file)){ ?>
	<br><strong><font size="+1">Step 3: Insert into MYSQL databbase</font></strong>
	<table border=0>
		<tr>
			<td align=center>  <input type=button value='empty the table then save the new data to mysql database' onClick="javascript: saveToMysql()">
		</tr>
	</table>
<?}?>
</form>
<?
if(!$frm_table or !$frm_db) die;

//echo $frm_buffer;
//--------------------work with each table--------------------------------------
//output table structure
$output .= '<pre>/***************************************************************************';
$output .= "\n";
 
$output .=  "<b> $table_name </b>";
$output .= "\n";
$output .= ' +----------------+---------------+------+-----+---------+----------------+';
$output .= "\n";
$output .= ' | Field          | Type          | Null | Key | Default | Extra          |';
$output .= "\n";
$output .= ' +----------------+---------------+------+-----+---------+----------------+';
$output .= "\n";
for($i = 0; $i < $fields; $i++){
 $name_tmp = $name[$i];
 for($k = strlen($name[$i]); $k < 15; $k++){
   $name_tmp .= ' ';
 }
 $type_tmp = $type[$i].'('.$len[$i]. ')';
 for($k = strlen($type[$i].'('.$len[$i]. ')'); $k < 15; $k++){
   $type_tmp .= ' ';
 }
 $output .= ' | '. $name_tmp .'|'. $type_tmp.'|'. $flags[$i];
 $output .= "\n";

}
$output .= ' +----------------+---------------+------+-----+---------+----------------+';
$output .= "\n";
$output .=  '****************************************************************************/</pre>';
echo $output;
if($successful_msg){
	echo "<font size=+2 color=red>$successful_msg</font>";
  if(strstr($table_name, "UploadMDS") and strstr($username, "uploadmds") ){
     echo "<br>";
     echo "<a href='./UploadMDS.php?user=$frm_username&database=$frm_db&table=$table_name&uploaded_file=$uploaded_file' target=new><h2>Save Data to $frm_db</h2></a>";
  }
}else{
	echo $out_preview;
}
//end of file
//-------------------------
function display_update_yeastDB($ORFName='',$GI=''){
  $output = '<br>';
  if($GI){
    $output .=  "Update <b>GI|$GI</b><a href='http://www3.ncbi.nlm.nih.gov/htbin-post/Entrez/query?form=6&db=p&Dopt=g&uid=$GI' target=ncbi>NCBI</a> ";
  }else if($ORFName){
    $output .=" Update <b>$ORFName</b> <a href='http://genome-www4.stanford.edu/cgi-bin/SGD/locus.pl?locus=$ORFName' target=sgd>SGD</a>";
 }
  return $output;
}

//end of file
?>