<?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> </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'> </td>
<td align='center'><INPUT TYPE='submit' value='Continue'></td>
<td colspan='3'> </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");
?>