<?php
  $page_title = 'Upload Sales Plan';
  require_once('includes/load.php');
  require('library/php-excel-reader/excel_reader2.php');
  require('library/SpreadsheetReader.php');
  define ('SITE_ROOT1', realpath(dirname(__FILE__)));
  // Checkin What level user has permission to view this page
   page_require_level(1);
   $all_plans = find_all('plandetails');
?>
<?php
  if(isset($_POST['Submit'])){

   $req_fields = array('planid');
   validate_fields($req_fields);

   /*---if(find_by_groupName($_POST['group-name']) === false ){
     $session->msg('d','<b>Sorry!</b> Entered Group Name already in database!');
     redirect('add_group.php', false);
   }elseif(find_by_groupLevel($_POST['group-level']) === false) {
     $session->msg('d','<b>Sorry!</b> Entered Group Level already in database!');
     redirect('add_group.php', false);
   }--*/
   if(empty($errors)){
    echo $_FILES["file"]["type"];
           $plan = remove_junk($db->escape($_POST['planid']));
           //echo $plan;
              //attached file formate
            $mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
            
            if(in_array($_FILES["file"]["type"],$mimes)){
               //echo SITE_ROOT1;die();
          
              $uploadFilePath = 'uploads/SalesPlans/'.basename($_FILES['file']['name']);
              $uploadFilePath1 = '/uploads/SalesPlans/'.basename($_FILES['file']['name']);
              
              move_uploaded_file($_FILES['file']['tmp_name'], SITE_ROOT1.$uploadFilePath1);
              //move_uploaded_file($_FILES['file']['tmp_name'], $uploadFilePath);
              
          
              $Reader = new SpreadsheetReader($uploadFilePath);
          
          
              $totalSheet = count($Reader->sheets());
          
          
              echo "You have total ".$totalSheet." sheets";

          
              //$html="<table border='1'>";
              //$html.="<tr><th>SBU</th><th>ArtNumber</th><th>Colour</th><th>Size</th><th>Case</th><th>Pair</th><th>SAPCode</th><th>Category</th></tr>";
          
          
              /* For Loop for all sheets */
              for($i=0;$i<$totalSheet;$i++){
          
          
                $Reader->ChangeSheet($i);
                
                $SBUarray=array();
                $k=0;
                foreach ($Reader as $Row)
                {
                    if($k!=0) {
                  //$html.="<tr>";
                  //$SBU = isset($Row[0]) ? $Row[0] : '';
                  $Artno = isset($Row[0]) ? $Row[0] : '';
                  $Colour= isset($Row[1]) ? $Row[1] : '';
                  $Size= isset($Row[2]) ? $Row[2] : '';
                  
                  $Category= isset($Row[6]) ? $Row[6] : '';
                
                  $Case[]= isset($Row[3]) ? $Row[3] : '';
                  $Pair[]= isset($Row[4]) ? $Row[4] : '';
                  $SAPCode[]= isset($Row[5]) ? $Row[5] : '';
                 

                  if(find_by_columnnew('articledetails',$Artno,'ArticleName') === false ){
                    $session->msg('d','<b>Kindly Recheck the uploaded file!</b> Entered Article'.$Artno.' is not in the in database!');
                    redirect('salesplan_upload.php', false);
                  } else {
                  $ArticleResult=find_by_columnnew('articledetails',$Artno,'ArticleName');
                  if(isset($ArticleResult['ArticleId'])) {
                  $ArticleId=$ArticleResult['ArticleId'];
                  $ArticleArray[]=$ArticleResult['ArticleId'];;
                  }
                  }

                  if(find_by_columnnew('colordetails',$Colour,'ColorName') === false ){
                    $session->msg('d','<b>Kindly Recheck the uploaded file!</b> Entered Colour'.$Colour.' is not in the database!');
                    redirect('salesplan_upload.php', false);
                  } else {
                  $ColorResult=find_by_columnnew('colordetails',$Colour,'ColorName');
                  if(isset($ColorResult['ColorId'])) {
                  $ColorId=$ColorResult['ColorId'];
                  $ColorArray[]=$ColorResult['ColorId'];;
                  }
                  }

                  if(find_by_columnnew('sizedetails',$Size,'SizeAssortment') === false ){
                    $session->msg('d','<b>Kindly Recheck the uploaded file!</b> Entered Colour'.$Size.' is not in the database!');
                    redirect('salesplan_upload.php', false);
                  } else {
                  $SizeResult=find_by_columnnew('sizedetails',$Size,'SizeAssortment');
                  if(isset($SizeResult['SizeId'])) {
                  $SizeId=$SizeResult['SizeId'];
                  $SizeArray[]=$SizeResult['SizeId'];;
                  }
                  }

                  if(find_by_columnnew('categorydetails',$Category,'CategoryName') === false ){
                    $session->msg('d','<b>Kindly Recheck the uploaded file!</b> Entered Category'.$Category.' is not in the database!');
                    redirect('salesplan_upload.php', false);
                  } else {
                  $CategoryResult=find_by_columnnew('categorydetails',$Category,'CategoryName');
                  if(isset($CategoryResult['CategoryId'])) {
                  $CategoryId=$CategoryResult['CategoryId'];
                  $CategoryArray[]=$CategoryResult['CategoryId'];
                  }
                  }

                  /*--$html.="<td>".$SBU."</td>";
                  $html.="<td>".$Artno."</td>";
                  $html.="<td>".$Colour."</td>";
                  $html.="<td>".$Size."</td>";
                  $html.="<td>".$Case."</td>";
                  $html.="<td>".$Pair."</td>";
                  $html.="<td>".$SAPCode."</td>";
                  $html.="<td>".$Category."</td>";
                  $html.="</tr>";--*/

          
                  
                 // $query = "insert into items(title,description) values('".$title."','".$description."')";
          
          
                 // $mysqli->query($query);
                }
                $k=$k+1;
                 }
                 $userid = current_user();
                 $p_userid = $userid['id'];
                 //echo $plan;
                 //print_r($SBUarray);
                 //print_r($ArticleArray);
                 //print_r($ColorArray);
                 //print_r($SizeArray);
                 //print_r($Case);
                 //print_r($Pair);
                 //print_r($SAPCode);
                 //print_r($CategoryArray);
                 //print_r($p_userid);
                 //die();

                 //$sql =$db->query("Select * from salesplandetails where PlanId='".$plan."' LIMIT 1");
                 //$splandetails = $db->fetch_assoc($sql);
                 //if(!$splandetails) {
                  //echo "Insert";die();
                  $insertpass=0;
                  for($i=0;$i<count($ArticleArray);$i++){
 
                  $query  = "INSERT INTO salesplandetails (";
                  $query .=" PlanId,UnitId,ArticleId,ColorId,SizeId,CaseCount,PairCount,SAPCode,CategoryId,UserId";
                  $query .=") VALUES (";
                  $query .=" '{$plan}', 5, '{$ArticleArray[$i]}', '{$ColorArray[$i]}', '{$SizeArray[$i]}', '{$Case[$i]}', '{$Pair[$i]}', '{$SAPCode[$i]}', '{$CategoryArray[$i]}', '{$p_userid}'";
                  $query .=")";
                  //$query .=" ON DUPLICATE KEY UPDATE name='{$p_name}'";
                  if($db->query($query)){
                   $insertpass=$insertpass+1;
                  } else {
                   $insertpass=0;
                  }
                 
                  }
                 //} else {
                  //echo "Update";die();
                  //$insertpass=0;
                  //for($i=0;$i<count($SBUarray);$i++){
 
                  //$query  = "Update salesplandetails SET";
                  //$query .=" UnitId='{$SBUarray[$i]}',ArticleId='{$ArticleArray[$i]}',ColorId='{$ColorArray[$i]}',SizeId='{$SizeArray[$i]}',CaseCount='{$Case[$i]}',PairCount='{$Pair[$i]}',SAPCode='{$SAPCode[$i]}',CategoryId='{$CategoryArray[$i]}',UserId='{$p_userid}'";
                  //$query .=" Where PlanId='{$plan}'";
                
                  //if($db->query($query)){
                  // $insertpass=$insertpass+1;
                 // } else {
                  // $insertpass=0;
                  //}
                 
                 // }
                 //}



              if($insertpass>0){
                $session->msg('s',"Plan added/Updated Successfully ");
                redirect('salesplan_upload.php', false);
              } else {
                $session->msg('d',' Sorry failed to add the entire plan!');
                redirect('salesplan_upload.php', false);
              }  
          
          
              }
          
          
              //$html.="</table>";
              //echo $html;
              //echo "<br />Data Inserted in dababase";
              

            }
   

        /*---$query  = "INSERT INTO user_groups (";
        $query .="group_name,group_level,group_status";
        $query .=") VALUES (";
        $query .=" '{$name}', '{$level}','{$status}'";
        $query .=")";
        if($db->query($query)){
          //sucess
          $session->msg('s',"Group has been creted! ");
          redirect('add_group.php', false);
        } else {
          //failed
          $session->msg('d',' Sorry failed to create Group!');
          redirect('add_group.php', false);
        }--*/
   } else {
     $session->msg("d", $errors);
      redirect('salesplan_upload.php',false);
   }
 }
?>
<?php include_once('layouts/header.php'); ?>
<div class="container">
   <h2>Sales Plan Upload</h2>
   <div class="col-md-12">
    <?php echo display_msg($msg); ?>
  </div>
   <form method="POST" action="salesplan_upload.php" enctype="multipart/form-data">
   <div class="dsp form-group">
                    <select class="form-control" id="plannum" name="planid";>
                      <option value="">Select Plan</option>
                    <?php  foreach ($all_plans as $plan): ?>
                      <option value="<?php echo $plan['PlanId']; ?>">
                        <?php echo $plan['PlanName'] ?></option>
                    <?php endforeach; ?>
                    </select>
                     </div>
      <div class="dsp form-group">
         <label>Upload Sales Plan</label>
         <input type="file" name="file" class="form-control">
      </div>
      <div class="dsp form-group">
         <button type="submit" name="Submit" class="btn btn-success">Upload</button>
      </div>
      <p>Download Demo File from here : <a href="https://simplepro.in/PTP/Template/Sales_Plan_Template.xlsx"><strong>Sales Plan Template.xlxs</strong></a></p>
   </form>
</div>

<?php include_once('layouts/footer.php'); ?>
