Location: PHPKode > projects > PowerMovieList > powermovielist-0_14_beta/import.php
<?php
/** powermovielist import
 * $Id: import.php,v 1.13 2005/11/23 18:35:59 niko Exp $
*/
$FILE_SELF = "import.php";
$LoadSmarty=true;
include_once("application.php");

RequestLogin(PML_LoginStyle_AccessDenied, PML_Rights_ListAdmin);

$DOC_TITLE = "Import";
include("top.html");

if(isset($_GET['action'])) $action = $_GET['action']; else $action = "";
if(isset($_POST['CsvInputNr'])) $CsvInputNr = $_POST['CsvInputNr']; else $CsvInputNr = "";

switch($action) {
	case "":
	case "import":
	case "import1":
?>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%' align='center'>
  <tr class='top'>
    <td colspan='2'><h1>CSV-Import</h1></td>
  </tr>
  <tr class='row1'>
    <td colspan='2'>You can import CSV-Files into your list. You won't find an application that doesn't support exporting CSV-Data :D<br>
All Fields that you want to import have to exist in your list! Define new ones <a href='editprop.php<?php echo $GlobalArg; ?>'>here</a>.</td>
  </tr>
</table>
<br>

<FORM METHOD=POST  ENCTYPE='multipart/form-data' ACTION='import.php<?php echo $GlobalArg; ?>action=import2'>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%'>
  <tr class='top'>
    <td colspan='2'>Step 1: Upload the CSV-File</td>
  </tr>
  <tr class='row1'>
    <td align='right'>the file</td>
    <td>
<!-- 	  <INPUT TYPE='hidden' name='MAX_FILE_SIZE' value='1048576'> -->
	  <INPUT NAME='userfile' TYPE='file' size='40'>
	</td>
  </tr>
  <tr class='row2'>
    <td align='right'><b>column-separator:</b><br>should be ',' or ';'</td>
    <td>
	  <INPUT TYPE='text' name='delemiter' value=',' size='1'>
	</td>
  </tr>
  <tr class='row1'>
    <td align='right'><b>Field-Names included:</b></td>
    <td>
	  <input type='checkbox' name='fieldnames' checked>
	</td>
  </tr>
  <tr class='row1'>
    <td>&nbsp;</td>
	<td><INPUT TYPE='submit' value='Upload'></td>
  </tr>
</table>
</form>
	<?php
		break;
	case "import2":
		$filename = $_FILES['userfile']['tmp_name'];
		$fp = @fopen ($filename, "r");
		if(!$fp) die("can't open uploaded file");		
		$MaxItems = 0;
		$Lines=array();
		while ($data = fgetcsv ($fp, 10240, $_POST['delemiter'])) {		   
		   if($MaxItems<count($data)) $MaxItems = count($data);
		   foreach($data as $k=>$i)
		      $data[$k] = str_replace("<br>", "\n", $i);
		   $Lines[] = $data;
		}

		if(!isset($_SESSION['CsvInput'])) $_SESSION['CsvInput']=array();

		//get a free index ($CsvInputNr)
		$CsvInputNr=0;
		while(1) {
			if(!isset($_SESSION['CsvInput'][$CsvInputNr])) break;
			$CsvInputNr++;
		}

		$set = array();		
		$set['fieldnames'] = isset($_POST['fieldnames']);
		
		//the lines will be saved here in the session-var:
		//(will be deleted after a timeout in the application.php)
		$_SESSION['CsvInput'][$CsvInputNr] = array("Lines"=>$Lines, "time"=>time(), "settings"=>$set);
		
		
?>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%' align='center'>
  <tr class='top'>
    <td colspan='2'><h1>CSV-Import</h1></td>
  </tr>
  <tr class='row1'>
    <td colspan='2'>You can import CSV-Files into your list. You won't find an application that doesn't support exporting CSV-Data :D<br>
All Fields that you want to import have to exist in your list! Define new ones <a href='editprop.php<?php echo $GlobalArg; ?>'>here</a>.</td>
  </tr>
</table>
<br>
<FORM METHOD=POST  ACTION='import.php<?php echo $GlobalArg; ?>action=import3'>
<input type='hidden' name='CsvInputNr' value='<?php echo $CsvInputNr; ?>'>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%'>
  <tr class='top'>
    <td colspan='6'>Step 2: Preview, select the fields to import</td>
  </tr>
  <tr class='top'>
    <td>#</td>
    <td>Field</td>
	<td>Import as</td>
	<td>Preview 1</td>
	<td>Preview 2</td>
	<td>Preview 3</td>
  </tr><?php
	LoadPropAll();
	$cls = 0;
	for($i=0;$i<$MaxItems;$i++) {
		echo "  <tr class='";
		if($cls++%2) echo "row1"; else echo "row2";
		echo "'>\n";
		echo "    <td>$i</td>\n";
		echo "    <td>";
		if($_SESSION['CsvInput'][$CsvInputNr]['settings']['fieldnames'] && isset($Lines[0][$i]))
			echo $Lines[0][$i];
		else
			echo "field $i";
		echo "</td>\n";
		echo "    <td align='center'>";
		echo "<select name='fld[$i]'>";
		echo "<option value='0'>don't import</option>";
		foreach($PropAll as $Prop) {
            if($GLOBALS['ActiveUserRights'] < $Prop['RequiredRights']) continue;
            if($Prop['RequiredRights']==-1 && $GLOBALS['ActiveUser']['name'] == "Guest") continue;
			if($Prop['PropType']==PML_PropType_FileUpload || $Prop['PropType']==PML_PropType_UserRating || $Prop['PropType']==PML_PropType_StaticText  || $Prop['PropType']==PML_PropType_DisplayNr || $Prop['PropType']==PML_PropType_DownloadLinkFileUpload || in_array($Prop['PropType'], array_keys($CFG['CustomPropTypes']))) continue;
			echo "<option value='$Prop[ID]'";
			if(isset($Lines[0][$i])) {
				if($Lines[0][$i]==$Prop['Name'])
					echo " selected";
			}
			echo ">$Prop[Name]</option>";
		}
		echo "</select>";
		echo "</td>\n";
		for($j=1;$j<4;$j++) {
			if(isset($Lines[$j][$i]))
				$Dat = $Lines[$j][$i];
			else
				$Dat = "";
			echo "    <td>".substr($Dat, 0, 30);
            if(strlen($Dat)>30) echo "...";
            echo "</td>\n";
		}
		echo "  </tr>\n";
	}
	echo "  <tr class='";
	if($cls++%2) echo "row1"; else echo "row2";
	echo "'>
    <td colspan='2'>&nbsp;</td>
	<td align='center'><INPUT TYPE='submit' value='Continue'></td>
    <td colspan='3'>&nbsp;</td>
  </tr>
</table>
</form>\n";
	break;
//**************************************************************************************************************************************************
case "import3":
		if(!isset($_SESSION['CsvInput'][$CsvInputNr]))
			die("<b>ERROR:</b> no data found, please upload the file again at step 1");
?>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%' align='center'>
  <tr class='top'>
    <td colspan='2'><h1>CSV-Import</h1></td>
  </tr>
  <tr class='row1'>
    <td colspan='2'>You can import CSV-Files into your list. You won't find an application that doesn't support exporting CSV-Data :D<br>
All Fields that you want to import have to exist in your list! Define new ones <a href='editprop.php<?php echo $GlobalArg; ?>'>here</a>.</td>
  </tr>
</table>
<br>
<FORM METHOD=POST  ACTION='import.php<?php echo $GlobalArg; ?>action=import4'>
<input type='hidden' name='CsvInputNr' value='<?php echo $CsvInputNr; ?>'><?php
foreach($_POST['fld'] as $k=>$i) {
	echo "<input type='hidden' name='fld[$k]' value='$i'>\n";
}
?>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%'>
  <tr class='top'>
    <td>Step 3: checking fields</td>
  </tr>
  <tr class='row1'>
    <td><?php
LoadPropAll();
$Lines = $_SESSION['CsvInput'][$CsvInputNr]['Lines'];
echo "checking " . count($Lines) . " entries...<br>please wait...<br>";
flush();
$ErrorFound = false;
foreach($Lines as $kLine=>$Line) {
	if($kLine==0 && $_SESSION['CsvInput'][$CsvInputNr]['settings']['fieldnames']) continue; //don't import header
	foreach($Line as $kFld=>$Fld) {
		if($_POST['fld'][$kFld]==0) continue;
		$Prop = $PropAll[$_POST['fld'][$kFld]];
		switch($Prop['PropType']) {
			case PML_PropType_ListBox: //ListBox
			case PML_PropType_Boolean: //Boolean
			case PML_PropType_ListBoxMulti: //ListBoxMulti
				if($Prop['PropType']==PML_PropType_ListBox)
					$Fld = array($Fld);
				if($Prop['PropType']==PML_PropType_ListBoxMulti)
					$Fld = split(',', $Fld);

				if($Prop['PropType']==PML_PropType_Boolean) {
					$Fld = strtolower($Fld);
					if($Fld=="1" || $Fld=="true" || $Fld=="yes" || $Fld=="t") {
						$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND Value='1'";
					} else {
						$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND Value='0'";
					}				
					$q = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
					if(mysql_num_rows($q)==0) {
						echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: invalid propval-entries for boolean! Try creating a new property.<br>\n";
						$ErrorFound = true;
					}
				} else {
					foreach($Fld as $k=>$f) {
						$f = trim($f);
                        $f = addslashes($f);
						$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND (DisplayText LIKE '$f' OR DisplayTextShort LIKE '$f' OR FetchText LIKE '$f')";
						$q = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
						if(mysql_num_rows($q)==0) {
							echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: value not found ($f)<br>\n";
							$ErrorFound = true;
							continue;
						}
					}
				}				
				break;
		} //end swtich proptype
	} //end foreach fld
}//end foreach lines
	echo "check done...<br>\n";
	if($ErrorFound) {
		echo "<b>You can ignore these errors, but no data will be imported for these fields.<br>You could also correct them <a href='editprop.php$GlobalArg'>here</a> by adding the missing values.</b>";
	} else {
		echo "No errors were found, you can continue now importing the data.";
	}
	echo "    </td>\n";
	echo "  </tr>\n";
	echo "  <tr class='row2'><td><INPUT TYPE='submit' value='Continue'></td></tr>\n";
	echo "</table>\n";
	break;
//**************************************************************************************************************************************************
case "import4":
		if(!isset($_SESSION['CsvInput'][$CsvInputNr]))
			die("<b>ERROR:</b> no data found, please upload the file again at step 1");
?>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%' align='center'>
  <tr class='top'>
    <td colspan='2'><h1>CSV-Import</h1></td>
  </tr>
  <tr class='row1'>
    <td colspan='2'>You can import CSV-Files into your list. You won't find an application that doesn't support exporting CSV-Data :D<br>
All Fields that you want to import have to exist in your list! Define new ones <a href='editprop.php<?php echo $GlobalArg; ?>'>here</a>.</td>
  </tr>
</table>
<br>
<table border='0' cellspacing='1' cellpadding='5' class='tblback' width='100%'>
  <tr class='top'>
    <td>Step 4: importing...</td>
  </tr>
  <tr class='row1'>
    <td><?php
LoadPropAll();
$Lines = $_SESSION['CsvInput'][$CsvInputNr]['Lines'];
echo "importing " . count($Lines) . " entries...<br>please wait...<br>";
flush();
foreach($Lines as $kLine=>$Line) {
	if($Line==array()) continue; //don't import empty lines	
	if($kLine==0 && $_SESSION['CsvInput'][$CsvInputNr]['settings']['fieldnames']) continue; //don't import header
	$strSql = "INSERT INTO $CFG[Prefix]movies (ListID, DateAdded, UserAddedID)
		VALUES ('$ActiveList[ID]','" . date("Y-m-d H:i:s") . "','$ActiveUser[ID]')";
	pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);	
	$ID = mysql_insert_id();
	$strSql = "INSERT INTO $CFG[Prefix]movies_$ActiveList[name] (MovieID) VALUES ('$ID')";
	pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);	
	foreach($Line as $kFld=>$Fld) {
		if($_POST['fld'][$kFld]==0) continue;
		$Prop = $PropAll[$_POST['fld'][$kFld]];
		switch($Prop['PropType']) {
			case PML_PropType_Text: //Text
			case PML_PropType_Date: //Date
			case PML_PropType_Textfield: //Textfield
			case PML_PropType_Url: //Url
			case PML_PropType_UrlCached: //cached Url
            case PML_PropType_AutoIncrement:            
				if($Prop['PropType']==PML_PropType_UrlCached) { //cached Url
					//the url is saved, and the file is saved local
					// in Cache/$ID."-".$Prop['ID']."-".urlfilename			

					if ($Fld!=''){
	                    //check if Dat has a valid extension:
	                    $Ext = substr($Fld, strrpos($Fld, "."));
	                    if(!in_array(strtolower($Ext), explode(";", $GLOBALS['CFG']['UploadExt']))) {
							echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: invalid extension (" . $Fld . ")<br>\n";                        
	                        break;
	                    }
	
	                    $Dat = GetFileName($Fld,true);
						if($Dat!="") {
							$FileName = $CFG['CacheDir'] . $ID."-".$Prop['ID']."-".$Dat;
							if(!DownloadFile($Fld, $FileName)) {
								echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: can't download url (" . $Fld . ")<br>\n";
								$Fld =  "";
							}
						}
					}
				}
				else { // so for all props, except UrlCached
                    //if no value specified, retrieve default value
                    if ($Fld=='')
                    	$Fld=GetStandardValue($Prop);
                    else
                		//remove any delimiter
                    	$Fld = str_replace(PML_PropertyDelem, '', $Fld);
                }
				$strSql = "UPDATE $CFG[Prefix]movies_$ActiveList[name] SET `$Prop[Name]`='" . addslashes($Fld) . "' WHERE MovieID='$ID'";
				pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
				break;
			case PML_PropType_ListBox: //ListBox
			case PML_PropType_Boolean: //Boolean
			case PML_PropType_ListBoxMulti: //ListBoxMulti
				if ($Fld==''){
					// Get default value(s)
                    $Fld=GetStandardValue($Prop);
                }
				else {
				
					if($Prop['PropType']==PML_PropType_ListBox)
						$Fld = array($Fld);
					if($Prop['PropType']==PML_PropType_ListBoxMulti)
						$Fld = split(',', $Fld);
	
					if($Prop['PropType']==PML_PropType_Boolean) {
						$Fld = strtolower($Fld);
						if($Fld=="1" || $Fld=="true" || $Fld=="yes" || $Fld=="t") {
							$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND Value='1'";
						} else {
							$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND Value='0'";
						}				
						$q = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
						if(mysql_num_rows($q)==0) {
							//don't print error echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: invalid propval-entries for boolean!<br>\n";
							break;
						} else {
							$r = mysql_fetch_assoc($q);				
							$Fld = array($r['ID']);								
						}
					} else {
						foreach($Fld as $k=>$f) {
	                        $f = addslashes(trim($f));
							$strSql = "SELECT * FROM $CFG[Prefix]propval WHERE PropID=$Prop[ID] AND (DisplayText LIKE '$f' OR DisplayTextShort LIKE '$f' OR FetchText LIKE '$f')";
							$q = pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
							if(mysql_num_rows($q)==0) {
								//don't print error echo "<b>ERROR:</b> Line $kLine, $Prop[Name]: value not found!<br>\n";
								unset($Fld[$k]);
								continue;
							}
							$r = mysql_fetch_assoc($q);				
							$Fld[$k]=$r['ID'];
						}
					}				
				}
				//insert the entries
				if($Prop['PropType']==PML_PropType_ListBoxMulti) {
					$Fld = "_".implode("_", $Fld)."_";
				} else {
					$Fld = $Fld[0];
				}
				$strSql = "UPDATE $CFG[Prefix]movies_$ActiveList[name] SET `$Prop[Name]`='" . addslashes($Fld) . "' WHERE MovieID='$ID'";
				pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);

				break;
			case PML_PropType_DownloadLink: //Download-Link	
                $i = explode(PML_PropertyDelem, $Fld);
                $Fld = array();
                if(isset($i[0])) $Fld['Link'] = $i[0]; else $Fld['Link']="";
                if(isset($i[1])) $Fld['Text'] = $i[1]; else $Fld['Text']="";
                if(isset($i[2])) $Fld['Size'] = $i[2]; else $Fld['Size']="";
                foreach($Fld as $k=>$i)
                    $Fld[$k] = addslashes($Fld[$k]);
				$strSql = "UPDATE $CFG[Prefix]movies_$ActiveList[name] SET `$Prop[Name]_link`='$Fld[Link]', `$Prop[Name]_text`='$Fld[Text]', `$Prop[Name]_size`='$Fld[Size]' WHERE MovieID='$ID'";
				pml_mysql_query($strSql, $pmldb) or trigger_error("can't execute:<pre>$strSql</pre><i>".mysql_error($pmldb)."</i>",E_USER_ERROR);
                break;
		} //end swtich proptype
	}// end foreach Fld
}//end foreach Line

echo "<b>DONE!</b>";
unset($_SESSION['CsvInput'][$CsvInputNr]); //finaly delete the session-variablee
echo "    </td>\n";
echo "  </tr>\n";
echo "</table>\n";
$smarty->clear_cache(null,"list|$Active");
break;
}//end switch action
include("bottom.html");
?>
Return current item: PowerMovieList