<?php
// $Id: class.importCSVphp,v 0.96 2009/11/19 12:05:16 yoshis Exp $
//  ------------------------------------------------------------------------ //
//             importCSV - Bluemoon Backup/Restore Module for XOOPS          //
//             Copyright (c) 2005-2011 Yoshi Sakai / Bluemoon inc.           //
//                       <http://www.bluemooninc.biz/>                       //
//  ------------------------------------------------------------------------ //
//  This program is free software; you can redistribute it and/or modify     //
//  it under the terms of the GNU General Public License as published by     //
//  the Free Software Foundation; either version 2 of the License, or        //
//  (at your option) any later version.                                      //
//                                                                           //
//  You may not change or alter any portion of this comment or credits       //
//  of supporting developers from this source code or any supporting         //
//  source code which is considered copyrighted (c) material of the          //
//  original comment or credit authors.                                      //
//                                                                           //
//  This program is distributed in the hope that it will be useful,          //
//  but WITHOUT ANY WARRANTY; without even the implied warranty of           //
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            //
//  GNU General Public License for more details.                             //
//                                                                           //
//  You should have received a copy of the GNU General Public License        //
//  along with this program; if not, write to the Free Software              //
//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA //
//  ------------------------------------------------------------------------ //

class importCSV {
	var $debug = 0;		// Define this to enable debugging
	var $backup_dir;
	var $dump_size;
	var $dump_line;
	var $dump_buffer;
	var $query_res;
	var $download_count;
	var $download_fname;
	var $mime_type;
	var $time_start;
	var $xoopsModuleConfig;
	var $err_msg;

	function importCSV($dirname=NULL,$purgeBefore=NULL){
		global $xoopsModuleConfig,$xoopsModule;
		if (!$dirname){
			$this->xoopsModuleConfig = $xoopsModuleConfig;
			$dirname = $xoopsModule->dirname();
		}else{
			$this->xoopsModuleConfig($dirname);
		}
		$this->backup_dir = XOOPS_ROOT_PATH ."/cache/";
		$this->time_start = time();
		$this->dump_buffer = null;
		$this->dump_line =0;
		$this->dump_size =0;
		$this->download_count = 0;
		$this->download_fname = array();
		$this->mime_type = "";
		$this->query_res = array();
		if ($purgeBefore)
			$this->purge_allfiles($purgeBefore);
	}
	function xoopsModuleConfig($dirname){
		$module_handler =& xoops_gethandler('module');
		$this_module =& $module_handler->getByDirname($dirname);
		$mid = $this_module->getVar('mid');
		$config_handler =& xoops_gethandler('config');
		$this->xoopsModuleConfig =& $config_handler->getConfigsByCat(0, $mid);
	}
	function PMA_backquote($a_name, $do_it = TRUE){
	    if ($do_it
	        && PMA_MYSQL_INT_VERSION >= 32306
	        && !empty($a_name) && $a_name != '*') {
	        return '`' . $a_name . '`';
	    } else {
	        return $a_name;
	    }
	} // end of the 'PMA_backquote()' function
	

	/*
	** $beforeDays : You can purge before N days
	*/
	function purge_allfiles($beforeDays=NULL){
		if ($handle = opendir( $this->backup_dir )) {
		    while (false !== ($file = readdir($handle))) {
		        if (preg_match("/sql/",$file)) {
					$fileDate = filemtime( $this->backup_dir.$file );
		            if ($beforeDays){
		            	$beforeDate = time() - 86400 * intval($beforeDays);
		            	if ( $fileDate < $beforeDate ){
				            if ($this->debug) echo "DELETE - $file $fileDate\n<BR>";
			            	unlink($this->backup_dir.$file);
			            }
		            }else{
			            if ($this->debug) echo "DELETE - $file $fileDate\n<BR>";
		            	unlink($this->backup_dir.$file);
		            }
		        }
		    }
		    closedir($handle);
		}
	}
	function Lock_Tables($tablename_array){
	    $q = "LOCK TABLES";
	
		for ($i = 0; $i <count($tablename_array); $i++) {
	      $q .= " " .$tablename_array[$i] ." read,";
	    }
	    $q = substr($q,0,strlen($q)-1);
	    mysql_query($q);
	}
	/**
	 * CSV の1行をパースします。
	 * この関数が対応しているCSVの行形式は以下の通りです。
	 * ・区切りはカンマである。
	 * ・データにカンマを含む場合はダブルクオートで囲う。
	 * ・ダブルクオートで囲ったデータ中のダブルクオートはダブルクオート2回で置き換える。
	 * ・文字列中にCRLFが在ると ダブルクオート2回置換が入るので事前に<BR>に置換して後で戻す。
	 * @return CSV をパースした結果の配列
	 */
	function csv_parse($csvline){
		$csvline = preg_replace ("/\r\n/","<BR>",$csvline);
		preg_match_all('/("[^"]*(?:""[^"]*)*"|[^,]*),?/', $csvline, $a);
		$i = 0;
		foreach($a[1] as $key => $value) {
			if(preg_match('/^"(.*)"$/', $value, $value2)) {
				$a[1][$key] = preg_replace('/""/', '"', $value2[1]);
			}
			if ( $a[1][$key] != "NULL" ){
				$a[1][$key] = addslashes($a[1][$i]);
			}
			$i++;
		}
		return preg_replace ("/<BR>/","\r\n",$a[1]);
	}
	function csvArray2sql($csvArray,$import_header,$csvHeader){
		//var_dump($import_header);echo "<hr>";
		//var_dump($csvHeader);echo "<hr>";
		//var_dump($csvArray);
		$sqlArray=array();
		foreach ($import_header as $key => $value){
			$sqlArray[$value] = $csvArray[array_search($key,$csvHeader)];
			//echo ",".$csvArray[array_search($key,$csvHeader)];
		}
		//var_dump($sqlArray); die;
		return $sqlArray;
	}
	
	function sql_log($sql){
		global $denylog;
		// Save to file
		$fp = fopen("sql.log", "a+b");
		flock($fp, LOCK_EX);
		fwrite($fp, date("Y/m/d H:i:s ", time()) . "{$sql}\r\n");
		fclose($fp);
		return;
	}
	/*
	 * $pkey = "primary key" or "primary key|key2|key3|key4|key5"
	 */
	function import_data($filename, $tblname, $primarykey, $import_header){
		global $xoopsDB;
		$debug = 0;
		if (!file_exists($filename)) exit();
		$fp_csv = fopen("$filename", "r");
		$prefix ='';
		$icount = $i = 0;	
		$pkeys = explode("|",$primarykey);
		$ins_header = implode(",", $import_header);
		$ins_header.= ',formatted_address';
		$headers = array_values($import_header);
		while(!feof($fp_csv)){
		    $csvline = fgets($fp_csv, 8192);
		    $csvline = mb_convert_encoding($csvline,"EUCJP-win","SJIS-win");
			if ($i==0){
				$header = preg_replace("/\"/","",$csvline);
				$cstr = preg_replace ("/,/","",$csvline);
				$colnum = strlen($csvline) - strlen($cstr);
				$csvHeader = $this->csv_parse($csvline);
			}elseif($csvline){
				$cstr = preg_replace ("/,/","",$csvline);
				$cnum = strlen($csvline) - strlen($cstr);
				if( $colnum > $cnum ){
					do {
						$chkend = fgets($fp_csv, 8192);
						$csvline .= $chkend;
						$cstr = preg_replace ("/,/","",$chkend);
						$cnum += strlen($chkend) - strlen($cstr);
					}while( $colnum > $cnum );
					//$pluginmsg .= substr($csvline ,0, 11) . $colnum . ":" . $cnum . "\r\n";
					//$pluginmsg .= mb_convert_encoding($csvline , "EUC-JP", "SJIS"). "\r\n";
				}
				while(strpos($csvline,",,")){
					$csvline = preg_replace("/,,/",",NULL,",$csvline);
				}
				$csvline = preg_replace("/,\r|,\n|,\r\n/",",NULL",$csvline);	// for end of csvline
				$csvArray = $this->csv_parse($csvline);
				$sqldat = $this->csvArray2sql($csvArray,$import_header,$csvHeader);

				//
				// Seek primarykey record
				//
				$wstr = "";
				foreach($pkeys as $k){
					// For WHERE parameter
					if(strlen($wstr)>0) $wstr .= " and ";
					$j = array_search($k,$headers);
					$wstr .= $k . "=" . $sqldat[$headers[$j]];
					//var_dump($sqldat); echo $wstr; die;
				}
				$sqlc = "SELECT id,updated FROM ${tblname} WHERE ${wstr}";
				//if ($debug) echo $sqlc."<BR>";
				$res = $xoopsDB->query($sqlc);
				if ( !$xoopsDB->getRowsNum($res) ){
					// Insert record
					$istr = "";
					foreach($sqldat as $dat){
						if(strlen($istr)>0) $istr .= ",";
						$istr .= "'".$dat."'";
					}
					$istr .= ",\"" . preg_replace("/\"/" , "" , $sqldat['prefecture'].$sqldat['county'].$sqldat['township'].$sqldat['address1']) . "\"";
					$sql = "insert into ${tblname} (${ins_header}) values (${istr})";
					if ($debug) echo $sql."<BR>";
					//if ($debug) $this->sql_log($sql); 
					//$xoopsDB->query("SET NAMES SJIS;");
					$res = $xoopsDB->queryF($sql);
					if ($res) $icount++;
				}else{
					list($id, $updated) = $xoopsDB->fetchRow($res);
					// Update record
					$j = 0;
					$wstr = $upstr = "";
					if ($debug) echo $primarykey;
					foreach($headers as $h){
						// For Update parameter
						if(strlen($upstr)>0) $upstr .= ",";
						$upstr .= $h . "=" . $sqldat[$j];
						if ($h=="updated"){
							$result = preg_match('/"(.*?)"/', $sqldat[$j], $matches);
							$ymd = explode("-", $matches[1] );
							if ($matches[1])
								$csv_update = date("Y-m-d", mktime(0,0,0,$ymd[1],$ymd[2],$ymd[0]));
							else
								$csv_update = NULL;
						}
						$j++;
					}
					$sqlu = "update ${tblname} set ${upstr} where id=${id}";
					if( $updated < $csv_update ){
						echo $updated ."<>". $csv_update . "<br />";
						if ($debug) $this->sql_log($sqlu); 
						$res = $xoopsDB->queryF($sqlu);
						echo $sqlu."<br />";
						if ($res) $ucount++;
						else {
							$pluginmsg.= "error :" . $sqlu  . "\n";
							// For sql debug
							$pluginfp = fopen($log_dir . 'csv2sql.log', 'a');
							fwrite($pluginfp , $sql."\r\n");
							fwrite($pluginfp , $sqlu."\r\n");
							fclose($pluginfp);
						}
					}
				}
			}
			$i++;
		}
		fclose($fp_csv);
	}
	function get_module_tables($dirname)
	{
	    global $xoopsConfig;
	    if (!$dirname ) return;
	    $module_handler =& xoops_gethandler('module');
	    $module =& $module_handler->getByDirname($dirname);
	    // Get tables used by this module
	    $modtables = $module->getInfo('tables');
	    if ($modtables != false && is_array($modtables)) {
	        return $modtables;
	    }
	}
	function make_module_selection($select_dirname='',$addblank=0)
	{
	    global $xoopsDB;
		$sql = "SELECT name,dirname FROM ".$xoopsDB->prefix('modules');
		if (!$result = $xoopsDB->query($sql)) {
			return false;
		}
		$mod_selections  = "<select name=\"dirname\">\n";
		$mod_selections .= $addblank ? "<option value=''></option>\n" : "" ;
		while(list($name, $dirname) = $xoopsDB->fetchRow( $result ) ) {
			if (strcmp($dirname,$select_dirname)==0) $opt = "selected";
			else $opt="";
			$mod_selections .= "<option value=\"${dirname}\" ${opt}>${name}</option>\n";
		}
		$mod_selections .= "</select>\n";
		return $mod_selections;
	}
	/**
	 * Maximum upload size as limited by PHP
	 * Used with permission from Moodle (http://moodle.org) by Martin Dougiamas
	 *
	 * this section generates $max_upload_size in bytes
	 */
	
	function get_real_size($size=0) {
	/// Converts numbers like 10M into bytes
	    if (!$size) {
	        return 0;
	    }
	    $scan['MB'] = 1048576;
	    $scan['Mb'] = 1048576;
	    $scan['M'] = 1048576;
	    $scan['m'] = 1048576;
	    $scan['KB'] = 1024;
	    $scan['Kb'] = 1024;
	    $scan['K'] = 1024;
	    $scan['k'] = 1024;
	
	    while (list($key) = each($scan)) {
	        if ((strlen($size)>strlen($key))&&(substr($size, strlen($size) - strlen($key))==$key)) {
	            $size = substr($size, 0, strlen($size) - strlen($key)) * $scan[$key];
	            break;
	        }
	    }
	    return $size;
	}
	/**
	* Displays the maximum size for an upload
	*
	* @param   integer  the size
	*
	* @return  string   the message
	*
	* @access  public
	*/
	function PMA_displayMaximumUploadSize($max_upload_size) {
	    list($max_size, $max_unit) = $this->PMA_formatByteDown($max_upload_size);
	    return '(' . sprintf(_AM_SELECTAFILE_DESC, $max_size, $max_unit) . ')';
	}
	/**
	 * Formats $value to byte view
	 *
	 * @param    double   the value to format
	 * @param    integer  the sensitiveness
	 * @param    integer  the number of decimals to retain
	 *
	 * @return   array    the formatted value and its unit
	 *
	 * @access  public
	 *
	 * @author   staybyte
	 * @version  1.2 - 18 July 2002
	 */
	function PMA_formatByteDown($value, $limes = 6, $comma = 0)
	{
	    $dh           = pow(10, $comma);
	    $li           = pow(10, $limes);
	    $return_value = $value;
		$byteunits = array('Byte', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB');
	    $unit         = $byteunits[0];
		$number_thousands_separator = ',';
		$number_decimal_separator = '.';
	
	    for ( $d = 6, $ex = 15; $d >= 1; $d--, $ex-=3 ) {
	        if (isset($byteunits[$d]) && $value >= $li * pow(10, $ex)) {
	            $value = round($value / ( pow(1024, $d) / $dh) ) /$dh;
	            $unit = $byteunits[$d];
	            break 1;
	        } // end if
	    } // end for
	
	    if ($unit != $byteunits[0]) {
	        $return_value = number_format($value, $comma, $number_decimal_separator, $number_thousands_separator);
	    } else {
	        $return_value = number_format($value, 0, $number_decimal_separator, $number_thousands_separator);
	    }
	
	    return array($return_value, $unit);
	} // end of the 'PMA_formatByteDown' function
	function download_fname(){
		return $this->download_fname;
	}
	// end function
}
?>