File: as_admintool_sqlimport.php

<?
/**
===========================================================================
* @package as_admintool
* @desc as_admintool_sqlimport.php - flexible Import from txt/csv files into SQL data
* @author Alexander Selifonov <as-works@narod.ru>
* @copyright Alexander Selifonov 2008
* @link http://selifan.ru
* @version 1.000.001
* created  13.04.2008
* modified 14.04.2008 (dd.mm.yyyy)
* Read "as_admintool.htm" for detailed instructions
============================================================================
*/
define('ASADM_SQLIMPORT','sqlimport'); // unique string ID for this plugin module

if(!isset($as_admt_pages)) $as_admt_pages=array();
if(!isset($as_admt_plugins)) $as_admt_plugins=array();

# mandatory string - registering this plugin
CAsAdminTool::RegisterPlugin(ASADM_SQLIMPORT,'AsAdm_SqlImport_Form','AsAdm_SqlImport_Exec');

/**
* AsAdm_SqlImport_Form - function for drawing client interface page (FORM)
* This function will be called when CAsAdmin::Draw() is drawing all pages.
* param1-3 are the parameters You've passed to CAsAdminTool::AddPage()
* @param array : [0] - pageid, [1],[2] - parent html table size (width,height)
* @param string - folder with txt files (they will fill select-box)
* @param array an array with fixed table list or empty value/no value to get all table names from current DB
* @return none
*/
function AsAdm_SqlImport_Form($pginfo,$param1=false, $param2=false, $param3=false) {
  global $as_dbengine, $as_iface, $as_cssclass;
  $pageid=isset($pginfo[0])? $pginfo[0]: 0;
  $lwidth = isset($pginfo[1])? $pginfo[1]: 800;
  $lheight = isset($pginfo[2])? $pginfo[2]: 600;
  $r_width = $lwidth-20;
  $r_height = $lheight-260;
  $self = $_SERVER['PHP_SELF'];
  $folder = empty($param1)? './': $param1;

  static $js_drawn = false;
  if(!$js_drawn) { #<3> draw only once !!!
    $js_drawn = true;
?>
<script language='javascript'>
var ajax_sqlimport_busy = false;
function AsAdm_SqlImpChangeParam(pageid) {
  var fm = asGetObj('asadt_sqlimport_'+pageid);
  asGetObj('btnStartImport'+pageid).disabled=true;
  fm.btncolumns.disabled = (fm.imp_table.selectedIndex<=0 || fm.imp_file.selectedIndex<=0);
}

function AsAdm_RunSqlImpt(pageid,action) {
  if (ajax_sqlimport_busy) return;
  if(action=='execimport' && !confirm('Importing process will start. Are You sure ?')) return false;
  var fm = asGetObj('asadt_sqlimport_'+pageid);
  var xmlreq = NewXMLHttpRequest();
  if(!xmlreq) return false;
  ajax_sqlimport_busy = true;
  asGetObj('result_'+pageid).innerHTML = '<?=$as_iface['msg_waiting']?>';
  xmlreq.onreadystatechange= function() { //<3>
    if (xmlreq.readyState == 4) { //<3A>
      resp = xmlreq.responseText;
      var spl = xmlreq.responseText.split("{|}");
      delete xmlreq;
      ajax_sqlimport_busy = false;
      asGetObj('result_'+pageid).innerHTML = "";
      if(spl.length < 2) {
        asGetObj('result_'+pageid).innerHTML ="undef.response from server: "+resp;
      }
      else {
        if(action=='showcolumns') {
          asGetObj('improws_'+pageid).innerHTML = spl[1];
          asGetObj('btnStartImport'+pageid).disabled=false;
        }
        else if(action=='execimport') asGetObj('result_'+pageid).innerHTML = spl[1];
        else if(action=='refreshfiles') {
          flist = spl[1].split("^");
          fm.imp_file.options.length=1;
          fm.imp_file.selectedIndex = 0;
          for(k=0;k<flist.length;k++) { fm.imp_file.options[k+1]=new Option(flist[k],flist[k]); }
        }
      } //<4>
    } //<3A>
  } //<3>

  xmlreq.open('POST','<?=$self?>',true);
  xmlreq.setRequestHeader("Content-Type", postcont);
  params = "adm_action_type=<?=ASADM_SQLIMPORT?>&pageid=" + pageid + "&action="+action+"&"+ComputeParamString("asadt_sqlimport_"+pageid);
//  alert(params);
  xmlreq.send(params);
  return false;
}
</script>
<?
  }
  $prmt1 = isset($as_iface['imp_tableforimport'])? $as_iface['imp_tableforimport']:'Table to import to';
  $prmt2 = isset($as_iface['imp_choosefile'])? $as_iface['imp_choosefile']:'File to import from';
  $prmt3 = isset($as_iface['btn_refreshflist'])? $as_iface['btn_refreshflist']:'refresh file list';
  $prmt4 = isset($as_iface['imp_btn_selectfields'])? $as_iface['imp_btn_selectfields']:'Select fields for columns...';
  $prmt5 = isset($as_iface['imp_promptforselect'])? $as_iface['imp_promptforselect']:'Carefully select fields for each column and then press "Start import"';
  $prmt6 = isset($as_iface['btn_startimport'])? $as_iface['btn_startimport']:'Start import!';
  $prmt7 = isset($as_iface['imp_chk_cleantable'])? $as_iface['imp_chk_cleantable']:'Clean table before operation';
?>
<p align=left>
<table id='asadt_tbl_<?=$pageid?>'>
<tr><form name='asadt_sqlimport_<?=$pageid?>'><input type='hidden' name='folder' value='<?=$folder?>' />
<tr>
  <td valign=bottom><?=$prmt1?><br /><select name='imp_table' class='<?=$as_cssclass['textfield']?>' style='width:150px;' onChange="AsAdm_SqlImpChangeParam(<?=$pageid?>)"/><option value='' selected>----</option>
<?
    if(is_array($param2)) $tables = $param2;
    elseif(is_string($param2)) $tables = explode(',',$param2);
    else $tables = $as_dbengine->GetTableList();
    foreach($tables as $tbname) echo "<option value='$tbname'>$tbname</option>";
?>
  </select></td>
  <td valign=bottom><?=$prmt2?><br /><select name='imp_file' class='<?=$as_cssclass['textfield']?>' style='width:150px;' onChange="AsAdm_SqlImpChangeParam(<?=$pageid?>)"><option value='' selected>----</option>
<?
    $flist = AsAdm_SqlImptFiles($folder);
    foreach($flist as $fl) echo "<option value='$fl'>$fl</option>";
?>
  </select></td>
  <td valign=bottom><button class='button' name='btnrefresh' onClick='AsAdm_RunSqlImpt(<?=$pageid?>,"refreshfiles")'><?=$prmt3?></button>
  <td valign=bottom><button class='button' name='btncolumns' onClick='AsAdm_RunSqlImpt(<?=$pageid?>,"showcolumns")' disabled><?=$prmt4?></button>
</td></tr></table>
<div align=center>
<table width='98%' cellspacing=2>
<tr><td><?=$prmt5?></td>
</tr>
<tr><td><div id='improws_<?=$pageid?>' class='<?=$as_cssclass['resultarea']?>' style='overflow:auto; height:120px; width:<?=$r_width?>px;'>&nbsp;</div></td>
</tr>
<tr><td><input type='checkbox' name='imp_cleantable' /><?=$prmt7?> &nbsp;
<button class='button' id='btnStartImport<?=$pageid?>' onClick='AsAdm_RunSqlImpt(<?=$pageid?>,"execimport")' disabled><?=$prmt6?></button></tr></tr>
<tr height=24><td valign=bottom><?=$as_iface['title_serverresponse']?></td></tr>
<tr><td><div id='result_<?=$pageid?>' class='<?=$as_cssclass['resultarea']?>' style='width:<?=$r_width?>px;'>&nbsp;</div></td>
</tr></form>
</table>
</div>
<?
}

/**
* AsAdm_SqlImport_Exec - function that executes action on server and returns result string.
* @param array $parms, exactly what POST data contained, but converted from UTF-8 if needed
* @return 'delimited' result. I use a string '{|}' as a delimiter.
*/
function AsAdm_SqlImport_Exec($parms) {
  global $as_dbengine, $as_iface, $as_admt_bckpfolder;
  $pageid = isset($parms['pageid'])? $parms['pageid'] : '1';
  $folder = isset($parms['folder'])? $parms['folder'] : '';
  $ret = "$pageid{|}";
  $action = $parms['action'];
  switch($action) {
  case 'refreshfiles':
    $fls = AsAdm_SqlImptFiles($folder);
    for($kk=0;$kk<count($fls); $kk++)  $ret .=$fls[$kk].($kk+1<count($fls)? '^':'');
    break;
  case 'showcolumns':
    $ret .= AsAdm_SqlImp_BuildColumnSelect($parms);
    break;
  case 'execimport':
    $ret .=AsAdm_SqlImp_EcecuteImport($parms);
    break;
  }
  return $ret;
}
function AsAdm_SqlImptFiles($folder) {
  $tflist = array();
  $txt_exts = array('txt','csv','tdf'); # only these text files can be treated as import source
  if (($handle = @opendir($folder))) {
    while (($file = readdir($handle))) {
      $fext = asadmtFileExt($folder.$file);
      if (is_file($folder.$file) && in_array($fext,$txt_exts)) $tflist[] = $file;
    }
    closedir($handle);
  }
  natsort($tflist);
  return $tflist;
}
/**
* @desc make HTML code for choosing table fields for each column in text file
*/
function AsAdm_SqlImp_BuildColumnSelect($parms) {
  global $as_dbengine, $as_iface, $as_admt_bckpfolder,$as_cssclass;
  $pageid = isset($parms['pageid'])? $parms['pageid'] : '1';
  $imp_table = isset($parms['imp_table'])? $parms['imp_table'] : '';
  $imp_file = isset($parms['imp_file'])? $parms['imp_file'] : '';
  $folder = isset($parms['folder'])? $parms['folder'] : '';
  $flds = $as_dbengine->GetFieldList($imp_table);
  $lines = array();
  if(filesize($folder.$imp_file)<50000) $lines = file($folder.$imp_file);
  else {
     $fh = fopen($folder.$imp_file,'r');
     $iline = 0;
     while(is_resource($fh) && ($ln=fgets($fh)) && (++$iline<20)) $lines[] = $ln;
     if(is_resource($fh)) fclose($fh);
  }
  if(count($lines)<1) return 'No data in selected text file !';

  $curdelim = "\t"; # let's detect delimiter char
  $delimarr = array("\t",';','|');
  $tokencnt = 0;
  for($kk=0; $kk<count($lines); $kk++) {
    $lines[$kk] = trim($lines[$kk]);
    for($idl=0; $idl<count($delimarr); $idl++) {
      $spt = explode($delimarr[$idl],$lines[$kk]);
      if(count($spt)>$tokencnt) { $tokencnt = count($spt); $curdelim = $delimarr[$idl]; }
    }
  }
  # $curdelim is detected text delimiter character !
  $cdelim=ord($curdelim);
  $ret = "<table border=0 callspacing=1 cellpadding=0><input type='hidden' name='delimchar' value='$cdelim' /><tr>";
  for($icol=0; $icol<$tokencnt; $icol++){
    $ret .="<td  class='{$as_cssclass['tdhead']}'><select name='impcolumn{$icol}'><option value=''>---</option>";
    foreach($flds as $fld) {
#      if($fld[3]=='PRI') continue; # uncomment it if You want to hide Primary key field
      $ret .="<option value='{$fld[0]}'>{$fld[0]}</option>";
    }
    $ret.='</select></td>';
  }
  $ret .='</tr>';
  for($kl=0; $kl<min(20,count($lines)); $kl++) {
    $toks = explode($curdelim,$lines[$kl]);
    $classname = ($kl % 2) ? $as_cssclass['trowodd']:$as_cssclass['troweven'];
    $ret .= "<tr class='$classname'><td>{$toks[0]}</td>";
    for($icol=1; $icol<$tokencnt; $icol++){
      $val = isset($toks[$icol])? $toks[$icol]:'&nbsp;';
      $ret .= "<td>$val</td>";
    }
    $ret .='</tr>';
  }
  $ret .="</table>";
  return $ret;
}
/**
* @desc execute import from txt file to selected table columns
*/
function AsAdm_SqlImp_EcecuteImport($parms) {
  global $as_dbengine, $as_iface, $as_admt_bckpfolder,$as_cssclass;
  $pageid = isset($parms['pageid'])? $parms['pageid'] : '1';
  $imp_table = isset($parms['imp_table'])? $parms['imp_table'] : '';
  $imp_file = isset($parms['imp_file'])? $parms['imp_file'] : '';
  $folder = isset($parms['folder'])? $parms['folder'] : '';
  $imp_cleantable=isset($parms['imp_cleantable'])? $parms['imp_cleantable'] : 0;
  $curdelim = isset($parms['delimchar'])? chr($parms['delimchar']) : "\t";
  $ret = '';
  $cntinsert = $cnterror = 0;
  $flds = array();
  for($kk=0; $kk<1024; $kk++) {
    if(!isset($parms['impcolumn'.$kk])) break;
    if(!empty($parms['impcolumn'.$kk])) $flds[] = $parms['impcolumn'.$kk];
  }
  if(count($flds)>0) {
    $fh = fopen($folder.$imp_file,'r');
    $lineno = 0;
    if(is_resource($fh)) {
      if(!empty($imp_cleantable)) $as_dbengine->sql_query("TRUNCATE $imp_table");
      while(!feof($fh)) {
        $line = trim(fgets($fh));
        $lineno++;
        if($line==='') continue;
        $vals = explode($curdelim,$line);
        $ins = array();
        for($kfld=0; $kfld<count($flds);$kfld++) {
          $ins[$flds[$kfld]] = isset($vals[$kfld])? $vals[$kfld]:'';
        }
        if(count($ins)) $inserted = $as_dbengine->SQLBuildAndExecute($imp_table,'I',$ins);
        if($inserted) $cntinsert ++;
        else { $cnterror++; $ret .="line $lineno Inserting error : ".$as_dbengine->sql_error().'<br />'; }
      }
      fclose($fh);
      $ret .= ($ret==''? '':'<hr>')."Inserted records : $cntinsert".(($cnterror)?", failed inserts: $cnterror":'');
    }
    else $ret = "Failed to open text file $folder.$imp_file for reading, import canceled";
  }
  else $ret = 'No columns selected. Import canceled';
  return $ret;
}
?>