Hello Friends,
Plz Help Me regarding this.
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 will be inserted into resorce_task database table when i call excel2.php. so now i need to check whether the database has this excel sheet already . if the records have already in the database then it should be insert into another database called resource_tasks_backup .
so will it happens like this plz give idea. iam paisting 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;
}
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();
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);
$arr = array();
$err_arr = array();
if(count($arrayOfObjects) !=0)
{
$chk="SELECT
rt.day 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 =$date
AND
rt.description= $desc
AND
rt.hours=$hours ";
print_r($chk);exit;
$result=mysql_query($chk);
$rec=mysql_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 = "";
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
}
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
//objectarray();
}
/* function objectarray()
{
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//
// before uploading excel files to database a copy of excel files copied into excel_files_backup folder
$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 );
} }
// after uploading excel files to database deleting excel files from excel_files folder
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);
}
}
}
?>