
<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
 return;
}
include("includes/top.php");
include("opendb.php");

$brst=mysqli_query($db,"select brstate from branch where brcode='$brcode' ") or die ('err'.mysqli_error($db));
     $brrow=mysqli_fetch_row($brst);

      $brst1= $brrow[0];

$acpd=mysqli_query($db,"select * from acperiod") or die ('err'.mysqli_error($db));
     $acrow=mysqli_fetch_row($acpd);

      $acf= $acrow[0];
      $act= $acrow[1];
      
if(isset($_POST['upd']))
{
    $ff=$_REQUEST['ff'];
  if($ff !="none")
  {
       $imagename=$_FILES['ff']['name'];
       $filepath= "";
       $tmp = $_FILES['ff']['tmp_name'];
       move_uploaded_file($tmp, $filepath . $imagename) or die(' err:could not copy file');
        print "File copied :".$fl;
   }
     $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
  $ctr=1;
  $j=1;
  $invno_arr[]='';
  $invno_prv='';
    while(!feof($fp))
  {
     $line=fgets($fp,1024);
     $spline=explode("\t",$line);
    if($j==1)
    {
      $no_col=sizeof($spline);
      for($x=0;$x<=$no_col;$x++)
      { 
           if($spline[$x]=='GRN NUMBER')
            $invno_no1 = $x;
      }
    }
    if($j>1)
    {
		 $invno1 = trim($spline[$invno_no1]);
    
    if($invno1!=$invno_prv)
    {
        $query="select grno from sparepurchasevecv where grno='$invno1' and brcode='$brcode'";
        //print $query;
        $rs=mysqli_query($db,$query);
        if(mysqli_num_rows($rs)>0)
        {
            $invno_arr[$ctr++]=$invno1;
        }
    }
        $invno_prv=$invno1;
        
    }$j++;
  }
  
  
  
  //-------------------------------------------------------------
  $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
  $invpr='';
  $i=1;
  while(!feof($fp))
  {
     $line=fgets($fp,1024);
     $spline=explode("\t",$line);
              if($i==1)
    {
      $no_col=sizeof($spline);
      for($x=0;$x<=$no_col;$x++)
      {
        
         if($spline[$x]=='Invoice Number')
          $invno_no = $x;
          if($spline[$x]=='Line')
          $cname_no = $x;
 if($spline[$x]=='Customer City')
          $dlco_no = $x;
          if($spline[$x]=='Plant')
            $vendor_no = $x;
         if($spline[$x]=='Invoice Date')
          $invdate_no = $x;
          if($spline[$x]=='GRN DATE')
          $grdate_no = $x;
         if($spline[$x]=='Material No.')
          $itemno_no = $x;
         if($spline[$x]=='Description')
          $itemdesc_no = $x;
         //if($spline[$x]=='Quantity')
          //$qty_no = $x;
          
          if($spline[$x]=='Billing Qty')
          $qty_no = $x;
         if($spline[$x]=='Total Net Sales')
          $lstprice_no = $x;
         
         if($spline[$x]=='Total Net Sales')
          $tottax_no = $x;
         /*if($spline[$x]=='Handling Charges')
          $hnd_no = $x;
         if($spline[$x]=='Tax Code')
          $tax_no = $x;*/
         if($spline[$x]=='Tax Value')
          $vat_no = $x;
         if($spline[$x]=='Total in INR')
          $total_no = $x;
          if($spline[$x]=='Billing Type')
          $itemtype_no = $x;
           if($spline[$x]=='GRN NUMBER')
          $grno_no = $x;
         /*if($spline[$x]=='Tax Description')
          $tper_no = $x;en
          if($spline[$x]=='HSN Code')
          $hsn_no = $x;*/
               }
    }
    if($i>1)
    {
         
          $invno = $spline[$invno_no];
          $invdate = trim($spline[$invdate_no]);
          $invdate = str_replace('/', '-', $invdate);
          $invdate = implode('-', array_reverse(explode('-', $invdate)));
          
          $grdate = trim($spline[$grdate_no]);
          $grdate = str_replace('/', '-', $grdate);
          $grdate = implode('-', array_reverse(explode('-', $grdate)));
        
          $itemno = trim($spline[$itemno_no]);
          $itemdesc = trim($spline[$itemdesc_no]);
          $itemdesc =mysqli_escape_string($itemdesc);
          $qty = trim($spline[$qty_no]);
          $lstprice = trim($spline[$lstprice_no]);
          $tottax = trim($spline[$tottax_no]);
          $disc = trim($spline[$disc_no]);
          $hnd = trim($spline[$hnd_no]);
          $tax = trim($spline[$tax_no]);
          $vat = trim($spline[$vat_no]);
          $tot = trim($spline[$total_no]);
          $itemtype = trim($spline[$itemtype_no]);
          $grno = trim($spline[$grno_no]);
          $cname = trim($spline[$cname_no]);
          
          
          $tper = trim($spline[$tper_no]);
         $hsn = trim($spline[$hsn_no]);
          $lstprice1 = trim($spline[$lstprice1_no]);
          $lstprice2 = trim($spline[$lstprice2_no]);
          $lstprice3 = trim($spline[$lstprice3_no]);
         
         $dlco = trim($spline[$dlco_no]);
         $dlco = str_replace(',', '', $dlco);
        
        
          $qty = trim($qty,'"');
          $qty = str_replace(',', '', $qty);
          $lstprice = trim($lstprice,'"');
          $lstprice = str_replace(',', '', $lstprice);
          $tottax = trim($tottax,'"');
          $tottax = str_replace(',', '', $tottax);
          $disc = trim($disc,'"');
          $disc = str_replace(',', '', $disc);
          $hnd = trim($hnd,'"');
          $hnd = str_replace(',', '', $hnd);
          $tax = trim($tax,'"');
          $tax = trim($tax,'%-');
          $tax = str_replace(',', '', $tax);
          $tot = trim($tot,'"');
          $tot = str_replace(',', '', $tot);
          $vat = trim($vat,'"');
          $vat = str_replace(',', '', $vat);
          $grno = str_replace(',', '', $grno);
          $cname = str_replace(',', '', $cname);
          $cname = trim($cname, '"');
          $cname=str_replace("'","", $cname);
          $lstprice1 = trim($lstprice1,'"');
          $lstprice1 = str_replace(',', '', $lstprice1);
          $lstprice2 = trim($lstprice2,'"');
          $lstprice2 = str_replace(',', '', $lstprice2);
          $lstprice3 = trim($lstprice3,'"');
          $lstprice3 = str_replace(',', '', $lstprice3);
         
          $vendor_code = trim($spline[$vendor_no]);

        $cname=mysqli_escape_string($cname);
        $query="select supname from sup_master where code='$vendor_code' ";
    //print $q="select supname from sup_master where code='$vendor_code' ";
        
        $rss=mysqli_query($db,$query);
        $rsrw=mysqli_fetch_array($rss);
        if($rsrw[0]!='')
        {
        //print  
        $sname1=trim($rsrw[0]);
        $cname=$sname1." "."(".$vendor_code.")"; 
        //print "aa";
        }
        else
        {
        //print 
        $cname=$cname." "."(".$vendor_code.")";
        //print "bb";
        }
        
       $vc=$vendor_code;
        
        
        
        
        $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
        $nn=mysqli_num_rows($query);
        //print $nn;
        
        if(mysqli_num_rows($query)<=0 && $dlco!='')
        {
            print "<script>alert('You Are Selected a Wrong Branch');</script>";
            return;
        }
        
        
    if($itemno_no<='' || $itemdesc_no<='' || $qty_no<='' || $tottax_no<='' || $total_no<='' || $grno_no<='')
    {
       print"inv ". $invno_no."<br>";
        print "inv d ".$invdate_no."<br>";
        print " part ".$itemno_no."<br>";
        print "part nam ".$itemdesc_no."<br>";
        print "qty".$qty_no."<br>";
        print "lst ".$lstprice_no."<br>";
        print "ttax ".$tottax_no."<br>";
        print "tax ".$tax_no."<br>";
        print "tot ".$total_no."<br>";
        print "grn ".$grno_no."<br>";
        
        print "<script>alert('You Are Selected a Wrong File...!!!');</script>";
        return;
    }
   /* else
    {
        print "<script>alert('You Are Selected a correct File...!!!');</script>";
       
    }*/
      $k=$i-1;
      //print"grn ". $grno."<br>";
        //print "inv d ".$grdate."<br>";
       // print " part ".$qty."<br>";
       
       
        if(strlen($grno)>0 && $qty>0 && ($grdate>=$acf && $grdate<=$act) )
      {
          //print "<script>alert('You Are Selected a correct22 File...!!!');</script>";
       
          if($grno!='')
      {
          if(array_search($grno,$invno_arr)>0)
           {
               print "<font color='red'>Already Inserted.....!!!--".$grno."<br></font>";
           }
           else
           {
        if($invpr!=$grno) 
        {
        $query=mysqli_query($db,"select max(purno) from sparepurchasevecv where brcode='$brcode'");
        $row=mysqli_fetch_row($query);
        if(mysqli_num_rows($query)<=0)
            $purno=1;
        else
            $purno=$row[0]+1;
        }
                            if($itemtype=='ZLUB')
                                $mitemtype='Lubricant';
                            else
                                $mitemtype='Spares';
                                
                                $lstprice = $lstprice1+$lstprice2+$lstprice+$lstprice3;
          $ttper= preg_replace('/[^0-9\.]/', '', $tper);
      /*$tax=number_format($ttper,2);
      $ttype=substr($tper,0,3);
      $dec=substr($tottax,-3);
    $tottax1=substr($tottax,0,-3);
    $tottax1 = str_replace('.', '', $tottax1);
         $tottax2=$tottax1+$dec;
         
         $dectax=substr($vat,-3);
    $tottax1tax=substr($vat,0,-3);
    $tottax1tax = str_replace('.', '', $tottax1tax);
         $tottax2tax=$tottax1tax+$dectax;
         
         $dectot=substr($tot,-3);
    $tottax1tot=substr($tot,0,-3);
    $tottax1tot = str_replace('.', '', $tottax1tot);
         $tottax2tot=$tottax1tot+$dectot;
         
    print $tottax2."<br>";
    $taxp=$tottax2tax/$tottax2*100;
    $taxp=round($taxp,0);
    */
    
    $tax=number_format($ttper,2);
      $ttype=$tper;
      $dec=$tottax;
    $tottax1=$tottax;
    $tottax1 = str_replace(',', '', $tottax1);
         $tottax2=$tottax1;
         
         $dectax=$vat;
    $tottax1tax=$vat3;
    $tottax1tax = str_replace(',', '', $tottax1tax);
         $tottax2tax=$tottax1tax+$dectax;
         
         $dectot=$tot;
    $tottax1tot=$tot3;
    $tottax1tot = str_replace(',', '', $tottax1tot);
         $tottax2tot=$tottax1tot+$dectot;
         
    print $tottax2."<br>";
    $taxp=$tottax2tax/$tottax2*100;
    $taxp=round($taxp,0);
    
    if ($vc=='1110')
    {
        $supp='Eicher Truck And Buses (VE2000)';
        
    }
    else
    {
        $supp='VE Commercial RDC Banglore (VE1153)';
        
    }
    if (($vc=='1110' && $brst1=='KARNATAKA')|| $brst1=='KERALA')
    {
     $gsttype='IGST';   
    }
   else
   {
        $gsttype='CGST';  
   }
        $sql="insert into sparepurchasevecv (purno,suplier,pbillno,purdate,entrydate,partcode,partname,supqty,price,disc,aprice,hand,tax,taxamt,tot,brcode,taxtype,itemtype,grno,porderno,hsncode) 
        values('$purno','$supp','$invno','$invdate','$grdate','$itemno','$itemdesc','$qty','$tottax2','0','$tottax2','0','$taxp','$tottax2tax','$tottax2tot','$brcode','$gsttype','$mitemtype','$grno','$vc','$hsn')";
       print $i.". ".$sql ."<br>";
         mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
          print "<font color='blue'>Successfuly Inserted.....!!!--".$grno."<br></font>";
      
        $cname="";
           }
      }
      }
    }
    $invpr=$grno;
    $i++;
  }
  print "<script> alert('Successfully Uploaded....!!!');document.location='upld_sparepurchasegstvecv.php';</script>";

}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(95, 197, 117);}
.mnu{margin-left:5}
</style>
<center>
<br><br><br><br><br><br>
<form action='' method='post' ENCTYPE="multipart/form-data">
<table border=1 width=60%>
<tr><td>
<table width=100%>
<th colspan=2><h1>Upload Spare Purchase VECV</h1>
<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>Always download the spare purchases from SAP with below columns in ANY ORDER (Dealer Invoice no,Invoice date,Material,Material Desc.,<br>Delivered Qty,List price Per Unit,Category Discount Value,Total taxable,Handling charges,VAT Rate,VAT Amount,Grand Total,Material Type)<br>(Convert it to TAB TEXT Format and upload)
 </td></tr>
</table>
</td></tr>
</table>
</form>