<?
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");
?>
<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</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>
<?
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;
   }
     
  $tbl="sparepurchase".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl(`purno` int(10) DEFAULT NULL,`porderno` int(10) DEFAULT NULL,`suplier` varchar(200) DEFAULT NULL,`pbillno` varchar(15) DEFAULT NULL,`purdate` date DEFAULT NULL,`entrydate` date DEFAULT NULL,`partcode` varchar(200) DEFAULT NULL,`partname` varchar(200) DEFAULT NULL,`supqty` double(10,2) DEFAULT NULL,`mrp` double(10,2) DEFAULT NULL,`price` double(10,2) DEFAULT NULL,`disc` double(10,2) DEFAULT NULL,`aprice` double(10,2) DEFAULT NULL,`hand` double(10,2) DEFAULT NULL,`tax` double(10,2) DEFAULT NULL,`taxamt` double(10,2) DEFAULT NULL,
  `tot` double(10,2) DEFAULT NULL,`brcode` varchar(200) DEFAULT NULL,`taxtype` varchar(10) DEFAULT '-',`jvno` int(11) DEFAULT '0',`cessp` double(14,2) DEFAULT '0.00',`cessamt` double(14,2) DEFAULT '0.00',`other` double(14,2) DEFAULT '0.00',`rndoff` double(14,2) DEFAULT '0.00',
  `itemtype` varchar(50) NOT NULL,`ac_postdt` datetime DEFAULT NULL,`grno` varchar(20) NOT NULL)");
  //-------------------------------------------------------------
  $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]=='Vendor Invoice No.')
          $invno_no = $x;
          if($spline[$x]=='Vendor Name')
          $cname_no = $x;
          
          if($spline[$x]=='Vendor Code')
            $vendor_no = $x;
         if($spline[$x]=='Purchase Date')
          $invdate_no = $x;
         if($spline[$x]=='Material')
          $itemno_no = $x;
         if($spline[$x]=='Material Descirption')
          $itemdesc_no = $x;
         if($spline[$x]=='Quantity')
          $qty_no = $x;
         if($spline[$x]=='List Price per Unit')
          $lstprice_no = $x;
          if($spline[$x]=='Local Part Price Per Unit')
          $lstprice1_no = $x;
          if($spline[$x]=='Lubricant List Price Per Unit')
          $lstprice2_no = $x;
         if($spline[$x]=='Discount % on MRP')
          $disc_no = $x;
         if($spline[$x]=='Total Taxable Amount')
          $tottax_no = $x;
         if($spline[$x]=='Handling Charges')
          $hnd_no = $x;
         if($spline[$x]=='Tax Code')
          $tax_no = $x;
         if($spline[$x]=='Tax Amount')
          $vat_no = $x;
         if($spline[$x]=='Amount')
          $total_no = $x;
          if($spline[$x]=='Material Type')
          $itemtype_no = $x;
           if($spline[$x]=='GR No.')
          $grno_no = $x;
         
               }
    }
    if($i>1)
    {
         
          $invno = $spline[$invno_no];
          $invdate = trim($spline[$invdate_no]);
          $invdate = str_replace('/', '-', $invdate);
          $invdate = implode('-', array_reverse(explode('-', $invdate)));
          $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]);
           $cname =mysqli_escape_string($cname);
          $lstprice1 = trim($spline[$lstprice1_no]);
          $lstprice2 = trim($spline[$lstprice2_no]);
          
          $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);
          $lstprice1 = trim($lstprice1,'"');
          $lstprice1 = str_replace(',', '', $lstprice1);
          $lstprice2 = trim($lstprice2,'"');
          $lstprice2 = str_replace(',', '', $lstprice2);
          $vendor_code = trim($spline[$vendor_no]);
if (substr($vendor_code,-4)=='3776')
    $cname = $cname.', COCHIN';
if (substr($vendor_code,-4)=='3793')
    $cname = $cname.', PAVANGAD';
if (substr($vendor_code,-4)=='3774')
    $cname = $cname.', KANHANGAD';
if (substr($vendor_code,-4)=='3771')
    $cname = $cname.', KANNUR';
if (substr($vendor_code,-4)=='3770')
    $cname = $cname.', MALAPARAMBA';
if (substr($vendor_code,-4)=='3772')
    $cname = $cname.', MANJERY';
if (substr($vendor_code,-4)=='3773')
    $cname = $cname.', S.BATHERY';
if (substr($vendor_code,-4)=='3775')
    $cname = $cname.', VENGALAM';
if (substr($vendor_code,-4)=='3777')
    $cname = $cname.', EDAYAR';
if (substr($vendor_code,-4)=='3781')
    $cname = $cname.', MUKKAM';
if (substr($vendor_code,-4)=='3779')
    $cname = $cname.', KUTTIYADY';
if (substr($vendor_code,-4)=='3780')
    $cname = $cname.', KUTTIPURAM';
          
        if($tax=='VA')
            $tax='14.50';
        else
            $tax='5.00';
    
    if($invno_no<=''|| $invdate_no<='' || $itemno_no<='' || $itemdesc_no<='' || $qty_no<='' || $lstprice_no<='' || $tottax_no<='' || $tax_no<='' || $vat_no<='' || $total_no<='' || $grno_no<='')
    {
        print "<script>alert('You Are Selected a Wrong File...!!!');</script>";
        return;
    }
      $k=$i-1;
      if(strlen($grno)>0)
      {
          if($cname!='')
      {
         
        if($invpr!=$grno) 
        {
        $query=mysqli_query($db,"select max(purno) from $tbl 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 = ($tot-$vat)/$qty;
                                $tottax = $tot-$vat;
          
        $sql="insert into $tbl (purno,suplier,purdate,entrydate,partcode,partname,supqty,price,disc,aprice,hand,tax,taxamt,tot,brcode,taxtype,itemtype,grno) 
        values('$purno','$cname','$invdate','$invdate','$itemno','$itemdesc','$qty','$lstprice','$disc','$tottax','$hnd','$tax','$vat','$tot','$brcode','VAT','$mitemtype','$grno')";
        //print $i.". ".$sql."<br>";
        //print "<font color='blue'>Successfuly Inserted.....!!!--".$invno."<br></font>";
        mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
        
           
      }
      }
    }
    
    $i++;
  }
  //print "<script> alert('Successfully Uploaded....!!!');document.location='upld_sparepurchase.php';</script>";
	$sql1=mysqli_query($db,"select sum(supqty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,grno from $tbl where brcode='$brcode' group by grno");
  //echo "select sum(supqty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,grno from $tbl where brcode='$brcode' group by grno";
	while($row1=mysqli_fetch_array($sql1))
	{
		$qry1=mysqli_query($db,"select sum(supqty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,grno from sparepurchase where grno='$row1[grno]' and brcode='$brcode' group by grno");
		while($re=mysqli_fetch_array($qry1))
		{
			if($re[0]!=$row1[0])
			{
			  print "<font color=red>Sap Qty ($row1[0]) Is Not Equal Psn Qty ($re[0])...Of INVNO: $row1[grno]</font><br>";
			  $flg=1;
			}
			if($re[1]!=$row1[1])
			{
			  print "<font color=red>Sap Aprice ($row1[1]) Is Not Equal Psn Aprice ($re[1])...Of INVNO: $row1[grno]</font><br>";
			  $flg=1;
			}
			if($re[2]!=$row1[2])
			{
			  print "<font color=red>Sap price ($row1[2]) Is Not Equal Psn price ($re[2])...Of INVNO: $row1[grno]</font><br>";	
			  $flg=1;
			}
			if($re[3]!=$row1[3])
			{
			  print "<font color=red>Sap Taxamount ($row1[3]) Is Not Equal Psn Taxamount ($re[3])...Of INVNO: $row1[grno]</font><br>";	
			  $flg=1;
			}
			if($re[4]!=$row1[4])
			{
			  print "<font color=red>Sap Total ($row1[4]) Is Not Equal Psn Total ($re[4])...Of INVNO: $row1[grno]</font><br>";	
			  $flg=1;
			}
			if($re[tax]!=$row1[tax])
			{
			  print "<font color=red>Sap Tax ($row1[tax]) Is Not Equal Psn Tax ($re[tax])...Of INVNO: $row1[grno]</font><br>";	
			  $flg=1;
			}
		}
	}
			if($flg!=1)
			{
			  print "<font color=blue>Checking Done Successfuly...!!!</font><br>";
			}
}
?>
