hello friends plz help me,
iam uploading excel sheets into the database. it is succefully entering . the problem is when again uploading excel sheet i want to check the database and if excel sheet which i send is there already i have to restrict by inserting into the table(resource_table) and i should enter these dublicate excel sheet to another table(resource_table_backup).
please dude help me in this soon.
How about copy the excel cheets over to anouther server automatically with php so you already have a back up. Then when people upload the excel sheets check weather they are there or not, if they are there then tell them that the sheet has already been uploaded, are you sure you want to upload the file say for file changes etc...
Hello dude,
excel_files is the folder in which excel files r placed every time iam unlinking(deleting excel files) after uploading to database and iam retrieving excel files from mail so if any body send same excel sheet to mail it will download to folder excel_files then it should be uploaded when i call excel2.php. so now i need to check whether the database has this excel sheet already . if it have the records i want to insert into another database .
so will it happens like this plz give idea. iam pasting my code here
<?php
require_once 'db_conn.php';
require_once 'Spreadsheet/Excel/reader.php';
date_default_timezone_set('Asia/Calcutta');
//setlocale(LC_TIME,'in_IN');
//echo LC_TIME;
if ($handle = opendir('./excel_files/')) {
/* This is the correct way to loop over the directory. */
$file_array = array();
while (false !== ($file = readdir($handle))) {
$file_array[] = $file;
}
//print_r($file_array);exit;
closedir($handle);
}
$regexp = '/\d{4}-\d{2}-\d{2}/si';
if(count($file_array) >2)
{
foreach($file_array as $file_key=>$file_value)
{
if( !is_dir($file_value))
{
$reader = new Spreadsheet_Excel_Reader();
$reader->setOutputEncoding("UTF-8");
$reader->read("./excel_files/".$file_value);
$regex = '/\d{4}-\d{2}-\d{2}/si';
$data = array();
$arr = array();
$k = 0;
$dbconn = db_conn();
$previous_dates = array();
$arrayOfObjects=array();
//print_r($file_value);exit;
for ($i = 0; $i <= $reader->sheets[0]["numRows"]; $i++)
{
for ($j = 0; $j <= $reader->sheets[0]["numCols"]; $j++)
{
if(strcasecmp(trim($reader->sheets[0]["cells"][$i][$j]),'Resource')==0)
{
$resource = $reader->sheets[0]["cells"][$i][$j+1];
$p=$i;
}
if(strcasecmp(trim($reader->sheets[0]["cells"][($i+$p)-1][$j]),'Date')==0)
{
$y=($i+$p)-1;
$x=0;
}
if($i>$y)
{
switch($j)
{
case 2:
if($reader->sheets[0]["cellsInfo"][$i][$j]['type']=='unknown')
{
//Make a date for unknown format if coloumn 2 is has digits
//Check for date coloumn is not empty and hours are also filled
if(!empty($reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]))
{
//Check for digits in date coloumn if not make it the coloumn null
if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
{
$reader->curformat = 'Y-m-d';
$date_with_utc_value = $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
$date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;
}
else
{
$reader->sheets[0]["cells"][$i][$j] ='';
$reader->sheets[0]["cells"][$i][$j+1] ='';
$date = null;
}
}
else
{
//Check any of the excel sheet is not sending the date and hours in a row,
//then fill the other coloumns with previous date
if( !empty($reader->sheets[0]["cells"][$i][$j]) && empty($reader->sheets[0]["cells"][$i][$j+1]) && empty($reader->sheets[0]["cells"][$i+1][$j]) && !empty($reader->sheets[0]["cells"][$i+1][$j+1]) )
{
$k=1;
do
{
if(empty($reader->sheets[0]["cells"][$i+$k][$j]))
{
$reader->sheets[0]["cells"][$i+$k][$j] =$reader->sheets[0]["cells"][$i][$j] ;
$k++;
}
else
{
$k=0;
}
}while($k>0);
if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
{
$reader->curformat = 'Y-m-d';
$date_with_utc_value = $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
$date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;
}
}
}
}
else if($reader->sheets[0]["cellsInfo"][$i][$j]['type']=='date')
{
//If the type is specifically a date then convert them into required database format date
list($day,$month,$year) = explode("/", $reader->sheets[0]["cells"][$i][$j]);
$date = $year."-".$month."-".$day;
}
else
{
if(empty($reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]) && !empty($reader->sheets[0]["cells"][$i+1][$j]))
{
$reader->sheets[0]["cells"][$i][$j] = $reader->sheets[0]["cells"][$i+1][$j];
if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
{
$reader->curformat = 'Y-m-d';
$date_with_utc_value = $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
$date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;
}
}
else
{
$date = null;
}
}
$reader->sheets[0]["cells"][$i][$j] = $date;
if(!empty($reader->sheets[0]["cells"][$i][$j]) )
{
if($reader->sheets[0]["cells"][$i][$j] != $reader->sheets[0]["cells"][$i+1][$j])
{
array_push($previous_dates,$reader->sheets[0]["cells"][$i][$j]);
}
}
//echo $reader->sheets[0]["cells"][$i][$j]." ".$reader->sheets[0]["cells"][$i][$j+1]."<br />";
if(!empty($reader->sheets[0]["cells"][$i][$j]) && preg_match($regexp,$reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]))
{
$object = new stdClass();
$object->resource = $resource;
$object->date = $reader->sheets[0]["cells"][$i][$j];
$object->hours = $reader->sheets[0]["cells"][$i][$j+1];
$object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
}
else
{
if(!empty($reader->sheets[0]["cells"][$i][$j+1]) && !preg_match('/\w/',$reader->sheets[0]["cells"][$i][$j]) )
{
$object = new stdClass();
$object->resource = $resource;
$object->date = array_pop($previous_dates);
array_push($previous_dates,$object->date);
$object->hours = $reader->sheets[0]["cells"][$i][$j+1];
$object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
}
else
{
if(!empty($reader->sheets[0]["cells"][$i][$j+1]) && empty($reader->sheets[0]["cells"][$i][$j]))
{
$object = new stdClass();
$object->resource = $resource;
$object->date = array_pop($previous_dates);
array_push($previous_dates,$object->date);
$object->hours = $reader->sheets[0]["cells"][$i][$j+1];
$object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
}
}
}
break;
case 5:
if(preg_match($regexp,$reader->sheets[0]["cells"][$i][$j-3]) || !empty($reader->sheets[0]["cells"][$i][$j-2]) )
{
$project = $reader->sheets[0]["cells"][$i][$j];
//print_R($project);exit;
if(!empty($project))
{
if($reader->sheets[0]["cells"][$i-1][$j] != $reader->sheets[0]["cells"][$i][$j])
{
$query = getIdQuery('project' , $project );
$result = $dbconn->query($query);
$result_arr = $result->fetch_row();
$project_id = $result_arr[0];
}
if(!empty($project_id))
{
$object->project = $project_id;
}else
{
$object->project = 22;
}
}
else
{
if(!isset($reader->sheets[0]["cells"][$i][$j]) && (isset($reader->sheets[0]["cells"][$i][$j-2])) )
{
if(isset($reader->sheets[0]["cells"][$i][$j-3]) && !preg_match('/\w/si',$reader->sheets[0]["cells"][$i][$j-3]))
{
$object->project = 22;
}
if( !empty($object->date) && !empty($object->hours) && !empty($object->desc))
{
$object->project = 22;
}
}
}
}
break;
case 6:
if((preg_match($regex,$reader->sheets[0]["cells"][$i][$j-4])) || !empty($reader->sheets[0]["cells"][$i][$j-3]) )
{
$task = $reader->sheets[0]["cells"][$i][$j];
if(!empty($task))
{
if($reader->sheets[0]["cells"][$i-1][$j] != $reader->sheets[0]["cells"][$i][$j])
{
$query = getIdQuery('task' , $task );
$result = $dbconn->query($query);
$result_arr = $result->fetch_row();
$task_id = $result_arr[0];
}
if(!empty($task))
{
$object->task = $task_id;
}else
{
$object->task = 18;
}
}
else
{
if( !isset($reader->sheets[0]["cells"][$i][$j]) && (isset($reader->sheets[0]["cells"][$i][$j-3])) )
{
if(!empty($reader->sheets[0]["cells"][$i][$j-3]))
{
if(isset($reader->sheets[0]["cells"][$i][$j-3]) && !isset($reader->sheets[0]["cells"][$i][$j-3])&& !preg_match('/\w/',$reader->sheets[0]["cells"][$i][$j-4]))
{
$object->task = 18;
}
if( !empty($object->date) && !empty($object->hours) && !empty($object->desc))
{
$object->task = 18;
}
}
}
}
}
break;
}
}
}
if($i>$y)
{
$x++;
}
array_push($arrayOfObjects,$object);
unset($object);
}//End of loop I
echo "<pre>";
//print_r($arrayOfObjects);exit;
$arr = array();
$err_arr = array();
if(count($arrayOfObjects) !=0)
{
//checking if the records already present in the database
//$chk="select * from resource_tasks where name='$name'";
$chk="SELECT
DATE_FORMAT( rt.day, '%Y/%m/%d') as day , rt.resource_id, p.project_name, t.task_name, rt.description, rt.hours
FROM
resource_tasks AS rt, project AS p, task AS t
WHERE
t.task_id = rt.resource_task
AND
p.project_id = rt.resource_project
AND
rt.day
BETWEEN
STR_TO_DATE('$start_date','%Y-%c-%e') AND STR_TO_DATE('$end_date','%Y-%c-%e') $groupby order by rt.resource_id,day";
//print_r($chk);exit;
$result=mysqli_query($chk);
$rec=mysqli_num_rows($result);
//echo $rec;exit;
if($rec>0)
{
$insert = "INSERT INTO resource_tasks_backup(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
$string = "";
//$res=9 ;
//$msg="username is already existing Please please try with another user!";
//echo '<META HTTP-EQUIV="REFRESH" CONTENT="0;URL=administrator_adddomain.php?res='.$res.'">';
//exit;
/*foreach($arrayOfObjects as $key=>$value)
{
if(is_object($value))
{
$fullobject = get_object_vars($value);
if(count($fullobject) == 6 )
{
$i=0;
//echo "<pre>";
//print_r($fullobject);
foreach($fullobject as $fullkey=>$fullvalue)
{
switch($fullkey)
{
case 'task':
if(strtotime($value->date)>0)
{
$task_val = "'$value->task')";
$arr[$i] = $arr[$i].$task_val;
$task_val = "";
}
else
{
$task_val = "'$value->task')";
$err_arr[$i] = $err_arr[$i].$task_val;
$task_val = "";
}
break;
case 'project':
if(strtotime($value->date)>0)
{
$project_val = "'$value->project',";
$arr[$i] = $arr[$i].$project_val;
$project_val = "";
}
else
{
$project_val = "'$value->project',";
$err_arr[$i] = $err_arr[$i].$project_val;
$project_val = "";
}
break;
case 'date':
if(strtotime($value->date)>0)
{
$dtvalue = date('Y-m-d',strtotime($value->date));
$date = "("."'$value->resource',";
$date .= "'$dtvalue',";
$arr[$i] = $date;
$date = "";
}
else
{
$dtvalue = date('Y-m-d',strtotime($value->date));
$date = "("."'$value->resource',";
$date .= "'$dtvalue',";
$err_arr[$i] = $date;
$date = "";
}
break;
case 'hours':
if(strtotime($value->date)>0)
{
$hours = "'$value->hours',";
$arr[$i] = $arr[$i].$hours;
$hours = "";
}
else
{
$hours = "'$value->hours',";
$err_arr[$i] = $err_arr[$i].$hours;
$hours = "";
}
break;
case 'desc':
if(strtotime($value->date)>0)
{
$desc = "'$value->desc',";
$arr[$i] = $arr[$i].$desc;
$desc = "";
}
else
{
$desc = "'$value->desc',";
$err_arr[$i] = $err_arr[$i].$desc;
$desc = "";
}
break;
}//End of switch
}//End of foreach for object
$i++;
//echo "<pre>";
//print_r($arr);
//echo "<pre>";
//print_r($err_arr);
if(count($arr) !=0)
{
$string = implode(',',$arr);
$insert .= $string.",";
}
if(count($err_arr) !=0)
{
$error_string = implode(',',$err_arr);
$error_insert .= $error_string.",";
}
}
}//End of If condition for object vars
}//End of check for object
//End of arrayOfObjects
//End of arrayOfObjects count check
if(count($arr) !=0)
{
$insert = substr_replace($insert,';', -1);
//echo "insert ".$insert;
$dbconn->multi_query($insert);
}*/
}
/*
$result=mysql_query($chk);
$rec=mysql_num_rows($result);
if($rec>0)
{
$res=9 ;
//$msg="username is already existing Please please try with another user!";
echo '<META HTTP-EQUIV="REFRESH" CONTENT="0;URL=administrator_adddomain.php?res='.$res.'">';
exit;
}*/
else
{
$insert = "INSERT INTO resource_tasks(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
$string = "";
$error_insert = "INSERT INTO error_resource_tasks(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
$error_string = "";
foreach($arrayOfObjects as $key=>$value)
{
if(is_object($value))
{
$fullobject = get_object_vars($value);
if(count($fullobject) == 6 )
{
$i=0;
//echo "<pre>";
//print_r($fullobject);
foreach($fullobject as $fullkey=>$fullvalue)
{
switch($fullkey)
{
case 'task':
if(strtotime($value->date)>0)
{
$task_val = "'$value->task')";
$arr[$i] = $arr[$i].$task_val;
$task_val = "";
}
else
{
$task_val = "'$value->task')";
$err_arr[$i] = $err_arr[$i].$task_val;
$task_val = "";
}
break;
case 'project':
if(strtotime($value->date)>0)
{
$project_val = "'$value->project',";
$arr[$i] = $arr[$i].$project_val;
$project_val = "";
}
else
{
$project_val = "'$value->project',";
$err_arr[$i] = $err_arr[$i].$project_val;
$project_val = "";
}
break;
case 'date':
if(strtotime($value->date)>0)
{
$dtvalue = date('Y-m-d',strtotime($value->date));
$date = "("."'$value->resource',";
$date .= "'$dtvalue',";
$arr[$i] = $date;
$date = "";
}
else
{
$dtvalue = date('Y-m-d',strtotime($value->date));
$date = "("."'$value->resource',";
$date .= "'$dtvalue',";
$err_arr[$i] = $date;
$date = "";
}
break;
case 'hours':
if(strtotime($value->date)>0)
{
$hours = "'$value->hours',";
$arr[$i] = $arr[$i].$hours;
$hours = "";
}
else
{
$hours = "'$value->hours',";
$err_arr[$i] = $err_arr[$i].$hours;
$hours = "";
}
break;
case 'desc':
if(strtotime($value->date)>0)
{
$desc = "'$value->desc',";
$arr[$i] = $arr[$i].$desc;
$desc = "";
}
else
{
$desc = "'$value->desc',";
$err_arr[$i] = $err_arr[$i].$desc;
$desc = "";
}
break;
}//End of switch
}//End of foreach for object
$i++;
//echo "<pre>";
//print_r($arr);
//echo "<pre>";
//print_r($err_arr);
if(count($arr) !=0)
{
$string = implode(',',$arr);
$insert .= $string.",";
}
if(count($err_arr) !=0)
{
$error_string = implode(',',$err_arr);
$error_insert .= $error_string.",";
}
}
}//End of If condition for object vars
}//End of check for object
}//End of arrayOfObjects
}//End of arrayOfObjects count check
if(count($arr) !=0)
{
$insert = substr_replace($insert,';', -1);
//echo "insert ".$insert;
$dbconn->multi_query($insert);
}
if(count($err_arr) !=0)
{
$error_insert = substr_replace($error_insert,';', -1);
//echo "error insert ".$error_insert;
$dbconn->multi_query($error_insert);
}
$dbconn->close();
}//end of file checks for current and parent directories
}// end of foreach of files in the directory
}//End of If condition
//////////To delete files code here//
//if ($handle = opendir('./excel_files/')) {
//
// /* This is the correct way to loop over the directory. */
// $file_array = array();
// while (false !== ($file = readdir($handle))) {
// $file_array[] = $file;
// }
// closedir($handle);
// }
//if ($handle1 = opendir('./excel_files_backup/')) {
//
// /* This is the correct way to loop over the directory. */
// $file_array1 = array();
// while (false !== ($file1 = readdir($handle1))) {
// $file_array1[] = $file1;
// }
// closedir($handle1);
// }
// if(count($file_array) >2)
// {
//
// foreach($file_array as $file_key=>$file_value)
// {
// if( !is_dir($file_value))
// {
// copy("./excel_files/".$file_value,"./excel_files_backup/");
//
// }
// }
// }
//
//
/*$folder = 'excel_files';
$backup = 'excel_files_backup';
full_copy($folder, $backup);
function full_copy( $source, $target )
{
if ( is_dir( $source ) )
{
@mkdir( $target );
$d = dir( $source );
while ( FALSE !== ( $entry = $d->read() ) )
{
if ( $entry == '.' || $entry == '..' )
{ continue;
}
$Entry = $source . '/' . $entry;
//print_r($Entry);exit;
if ( ! is_dir( $Entry ) )
{
full_copy( $Entry, $target . '/' . $entry );
continue; }
copy($target . '/' . $entry);
}
// unlink($source);
$d->close();
} else
{
copy( $source, $target );
} }
*/
if ($handle = opendir('./excel_files/')) {
/* This is the correct way to loop over the directory. */
$file_array = array();
while (false !== ($file = readdir($handle))) {
$file_array[] = $file;
}
closedir($handle);
}
if(count($file_array) >2)
{
foreach($file_array as $file_key=>$file_value)
{
if( !is_dir($file_value))
{
unlink("./excel_files/".$file_value);
}
}
}
?>
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.