|
A
acpan author
We want to make sure that the person who import the data,
can only import to his table, not mess up other people data.
This is done will forcing a foreign key as part of the data to be imported.
We have Export Event and I asked for Import Events, as we need
to do some task before data is imported as above. So i modified
the import php file generated at the root folder: XXX_import.php
Hope this help someone and hope we will have import events soon.
Steps:
- Store the value at BeforeQueryList event.
function BeforeQueryList(&$strSQL,&$strWhereClause,&$strOrderBy)
// Add this to be used later in import file:
global $conn, $strTableName;
$_SESSION["tid_value"]= $_SESSION[$strTableName."_masterkey1"] ;
----------
- In the generated import file, modify the codes:
<?php
ini_set("display_errors","1");
ini_set("display_startup_errors","1");
session_cache_limiter("none");
set_magic_quotes_runtime(0);
header("Expires: Thu, 01 Jan 1970 00:00:01 GMT");
include("include/dbcommon.php");
include("include/_Phonebook_variables.php");
include("libs/excelreader.php");
$strOriginalTableName="`bulksms_addressbook`";
if(!@$_SESSION["UserID"])
{
$_SESSION["MyURL"]=$_SERVER["SCRIPT_NAME"]."?".$_SERVER["QUERY_STRING"];
header("Location: login.php?message=expired");
return;
}
if(!CheckSecurity(@$SESSION["".$strTableName."_OwnerID"],"Import"))
{
echo "<p>"."You don't have permissions to access this table"."<a href=\"login.php\">"."Back to login page"."</a></p>";
return;
}
//connect to the database
$conn = db_connect();
// keys array
$keys[]=AddFieldWrappers("id");
$keys_present=1;
$fields=array();
set_error_handler("import_error_handler");
$total_records=0;
$goodlines = 0;
// ** PAC Code: I want his OLD Data to be deleted ****
$strSQL_rid = "delete from addressbook where cat_id =".$_SESSION["tid_value"]. " AND id_client=".$_SESSION["id_client"];
$rs_rid = db_exec($strSQL_rid,$conn);
// ** PAC Code: I want his OLD Data to be deleted ****
//////////////////////
// import from Excel
//////////////////////
function ImportFromExcel($uploadfile)
{
$ret = 1;
global $error_message, $keys, $fields, $goodlines, $total_records;
$data = new Spreadsheet_Excel_Reader();
$data->read($uploadfile);
// populate field names array
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
$fields[] = AddFieldWrappers($data->sheets[0]['cells'][1][$j]);
$total_records = $data->sheets[0]['numRows']-1;
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++)
{
$arr = array();
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
{
$arr[] = $data->sheets[0]['cells'][$i][$j];
}
$ret = InsertRecord($arr);
}
return $ret;
}
/////////////////////////
// import from CSV
/////////////////////////
function ImportFromCSV($uploadfile)
{
$ret = 1;
global $error_message, $keys, $fields, $goodlines, $total_records;
$fcontents = file($uploadfile);
$line2 = trim($fcontents[0]);
$fields = explode(",", $line2);
// populate field names array
for ($j=0;$j<count($fields);$j++)
{
$fields[$j] = AddFieldWrappers(str_replace('"', "", $fields[$j]));
}
$keys_present=1;
for($k=0; $k<sizeof($keys); $k++)
{
if (!in_array($keys[$k], $fields))
{
$keys_present=0;
break;
}
}
// scan the file line by line
$total_records = sizeof($fcontents)-1;
for($i=1; $i<sizeof($fcontents); $i++)
{
$line = trim($fcontents[$i]);
$arr = ParseCSVLine($line);
$ret = InsertRecord($arr);
}
return $ret;
}
function import_error_handler($errno, $errstr, $errfile, $errline)
{
global $error_happened;
// echo $errstr ."
";
$error_happened=1;
}
function ParseCSVLine($line)
{
$arr = array();
$inword=0;
$hasquotes=0;
$start=0;
for ($i=0;$i<strlen($line);$i++)
{
$c = $line[$i];
switch ($c)
{
case "\"":
if (!$inword)
{
$inword=1;
$hasquotes=1;
$start=$i+1;
}
else
{
if ($line[$i+1]=="\"")
{
$i++;
continue;
}
else
{
$inword=0;
$hasquotes=0;
$arr[] = substr($line, $start, $i-$start);
$start=$i+1;
}
}
break;
case ",":
if (!$inword)
{
if ($line[$i+1]==",") $inword=1;
$hasquotes=0;
$start=$i+1;
}
else
{
if (!$hasquotes)
{
$inword=0;
if ($line[$i+1]==",") $inword=1;
$hasquotes=0;
$arr[] = substr($line, $start, $i-$start);
$start=$i+1;
}
}
break;
case " ":
break;
default:
$inword=1;
break;
}
}
if ($start<strlen($line))
$arr[] = substr($line, $start);
return $arr;
}
function InsertRecord($arr)
{
global $fields, $goodlines, $conn, $error_message, $keys_present, $keys, $strOriginalTableName;
$ret=1;
for ($j=0;$j<count($arr);$j++)
{
$type=GetFieldType(RemoveFieldWrappers($fields[$j]));
if(IsBinaryType($type))
$arr[$j] = db_addslashesbinary($arr[$j]);
if(($arr[$j]==="" || $arr[$j]===FALSE) && !ischartype($arr[$j]))
$arr[$j] = "null";
if ($arr[$j]=="null")
$arr[$j] = "NULL";
else
{
if(NeedQuotes($type))
{
if(!IsDateFieldType($type))
$arr[$j]="'".db_addslashes($arr[$j])."'";
else
if (strtotime($arr[$j])!=-1)
$arr[$j]=db_datequotes(date("Y-m-d",strtotime($arr[$j])));
else
$arr[$j]=db_datequotes($arr[$j]);
}
else
{
$strvalue = (string)$arr[$j];
$strvalue = str_replace(",",".",$strvalue);
$arr[$j]=0+$strvalue;
//check numbers
}
}
}
// *** PAC Code: Add a fixed field belong to the particular client login **
// PAC: $_SESSION["tid_value"] is saved at the list page before SQL event as session variable, so we know
// who is the person import
$v_cat_id = $_SESSION["tid_value"];
if (strlen($_SESSION["tid_value"]) == 0)
{
$v_cat_id = 0;
}
if (1 == 1)
{
// PAC: Now we force the value to the owner of this data using the session variable we saved before
$sql = "insert into ".AddTableWrappers($strOriginalTableName)." (cat_id,id_client,name,".implode(",", $fields).") values (".$v_cat_id.",".$_SESSION["id_client"].",".$_SESSION["id_name"].",".implode(",", $arr) .")";
// PAC: This is the original code
// $sql = "insert into ".AddTableWrappers($strOriginalTableName)." (".implode(",", $fields).") values (".implode(",", $arr) .")";
// ** PAC Code ****
if (db_exec($sql,$conn))
{
$goodlines++;
}
else
{
$temp_error_message="<b>Error:</b> in the line: ".implode(",",$arr)."
";
// we'll try to update the record
// * PAC Code: Don't want to update record since we want old data to delete first, so disable update ****
/ PAC
if ($keys_present)
{
$sql = "update ".AddTableWrappers($strOriginalTableName)." set ";
$where = " where ";
for($k=0; $k<sizeof($fields); $k++)
{
if (!in_array($fields[$k], $keys))
$sql .= $fields[$k] . "=". $arr[$k] . ", ";
else
$where.= $fields[$k] . "=". $arr[$k] . " and ";
}
$sql = substr($sql, 0, strlen($sql)-2);
$where = substr($where, 0, strlen ($where)-5);
$sql.= " " . $where;
$rstmp=db_query("select from " .AddTableWrappers($strOriginalTableName). " " . $where,$conn);
$data=db_fetch_array($rstmp);
if ($data)
{
if (db_exec($sql,$conn))
{
// update successfull
$goodlines++;
}
else
{
// update not successfull
$error_message .= $temp_error_message;
$ret = 0;
}
}
else // nothing to update
{
$error_message .= $temp_error_message;
$ret = 0;
}
}
else
PAC */
// **** PAC Code ****
$error_message .= $temp_error_message;
}
return $ret;
// ** PAC Code: Give some meaningful error message ****
}
ELSE
{
$temp_error_message ="<font color='red'><b>Error!</b></font> Wrong Cat ID observed in the line: ".implode(",",$arr)."
";
$temp_error_message .="=>Your Cat ID is:<font color='red'>".$_SESSION["tid_value"]." inseatd of ".$arr[0]."</font> ";
// ** PAC Code: Give some meaningful error message ****
$error_message .= $temp_error_message;
}
}
//$avalues=array();
$error_message = "";
if(@$_POST["a"]=="added")
{
$value=$_REQUEST["value_ImportFileName"];
$type=@$_POST["type_ImportFileName"];
$file=&$_FILES["file_ImportFileName"];
//check the file extension
$ext = substr($value, strlen($value)-4);
$ext=strtoupper($ext);
if($ext==".XLS")
{
ImportFromExcel($file['tmp_name']);
}
else
{
ImportFromCSV($file['tmp_name']);
}
//echo "goodlines: " . $goodlines ."
";
//echo "total_records: " . $total_records ."
";
if ($goodlines==$total_records)
{
$error_message = "<font size=2>" . $goodlines . " records were imported</font>
";
$error_message .= "<font size=2>To back to your list click on the <b>Back to list</b> button.</font>";
// ** PAC Code: PoP Up ****
$error_message1 = $goodlines . " records were imported";
$error_message1 .= "\\nClick Back to list button to go back to main page.";
echo("<script>alert('".$error_message1."');</script>");
// ** PAC Code ****
}
else
{
$error_message .= "Number of records: ". $total_records ."
";
$error_message .= "Imported: ".$goodlines."
";
$error_message .= "Not imported: ";
$error_message .= $total_records-$goodlines ."
";
// ** PAC Code: PoP Up ****
$error_message2 .= "Number of records: ". $total_records ."\\n";
$error_message2 .= "Imported: ".$goodlines."\\n";
$error_message2 .= "Not imported: ";
$error_message2 .= $total_records-$goodlines ."\\n";
echo("<script>alert('".$error_message2."');</script>");
// ** PAC Code ****
}
}
include('libs/xtempl.php');
$xt = new Xtempl();
$body=array();
$body["begin"]="<FORM name=frmimport id=frmimport action=\"_Phonebook_import.php\" method=post encType=multipart/form-data >".
"<input type=hidden name=\"a\" value=\"added\">".
"<input type=\"hidden\" name=\"type_ImportFileName\" value=\"upload2\">".
"<input type=hidden name=\"value_ImportFileName\" size=\"30\" maxlength=\"100\">";
$body["end"]="</FORM><script>SetToFirstControl();</script>";
$xt->assignbyref("body",$body);
$xt->assign("importfile_attrs","name=\"file_ImportFileName\" onChange=\"var path=this.form.file_ImportFileName.value; var wpos=path.lastIndexOf('\\\\'); var upos=path.lastIndexOf('/');
var pos=wpos; if(upos>wpos) pos=upos; this.form.value_ImportFileName.value=path.substr(pos+1);\"");
$xt->assign("backtolist_attrs","onclick=\"java script: window.location='_Phonebook_list.php?a=return';\"");
$xt->assign("error_message",$error_message);
$xt->display("_Phonebook_import.htm");
?> |
|