0

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.

2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by udaydesai
0

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...

0

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]."&nbsp;".$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);                

        }
    }
  }        

?>

Edited by Dani: Formatting fixed

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.