PHP Classes

File: as_admintool_sqlimport.php

Recommend this page to a friend!
  Classes of Alexander Selifonov   site administrator tool set   as_admintool_sqlimport.php   Download  
File: as_admintool_sqlimport.php
Role: Auxiliary script
Content type: text/plain
Description: Plugin : flexible import data into DB from TXT files
Class: site administrator tool set
Web interface to manage site resources
Author: By
Last change: --
Date: 15 years ago
Size: 12,327 bytes
 

Contents

Class file image Download
<? /** =========================================================================== * @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; } ?>