<?
/**
===========================================================================
* @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;'> </div></td>
</tr>
<tr><td><input type='checkbox' name='imp_cleantable' /><?=$prmt7?>
<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;'> </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]:' ';
$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;
}
?>
|
Download
