<?
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");
?>
<html>
<head>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(95, 197, 544);}
.mnu{margin-left:5}
</style>
</head>
<body>
<center>
<br><br><br><br><br><br>
<form action='' method='post' ENCTYPE="multipart/form-data">
<table border=1 width=40%>
<tr><td>
<table width=100%>
<th colspan=2><h1>Checking Spare Invoices</h1>
<tr><td>File <input type=file name='ff'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type=submit value='Upload' name='upd'><tr><td>Date Period- From dd <input type=text name='d1' size=2 value='<?print date("d")?>'>
mm <input type=text name='m1' size=2 value='<?print date("m")?>'>
yy <input type=text name='y1' size=4 value='<?print date("Y")?>'></td></tr>
<tr><td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dd <input type=text name='d2' size=2 value='<?print date("d")?>'>
mm <input type=text name='m2' size=2 value='<?print date("m")?>'>
yy <input type=text name='y2' size=4 value='<?print date("Y")?>'></tr>
</table>
</td></tr>
</table>
</form>
</body>
</html>
<?
$d1=$_REQUEST['d1'];
$m1=$_REQUEST['m1'];
$y1=$_REQUEST['y1'];
$d2=$_REQUEST['d2'];
$m2=$_REQUEST['m2'];
$y2=$_REQUEST['y2'];
$df=$y1."-".$m1."-".$d1;
$dt=$y2."-".$m2."-".$d2;
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="spareinvoice".$brcode.$user;
mysqli_query($db,"drop table $tbl");

mysqli_query($db,"CREATE TABLE $tbl(`invno` varchar(50) NOT NULL DEFAULT '0',`invtype` varchar(30) DEFAULT NULL,`mattype` varchar(30) DEFAULT NULL,'dealercode` varchar(30) DEFAULT NULL,`invdate` date DEFAULT NULL,`entrydate` date DEFAULT NULL,`total` double(14,2) DEFAULT '0.00')");
 
 
 
 
  //-------------------------------------------------------------
  $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
  
  $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]=='Dealer Invoice no')
          $invno_no = $x;
         if($spline[$x]=='Invoice Type Desc.')
          $invtype_no = $x;
         if($spline[$x]=='Invoice date')
          $invdate_no = $x;
       
         if($spline[$x]=='Grand Total')
          $total_no = $x;
          if($spline[$x]=='Material Type')
          $itemtype_no = $x;
          if($spline[$x]=='Dealer Plant')
          $dlco_no = $x;
               }
    }
    if($i>1)
    {
          $invno = $spline[$invno_no];
          $invtype = trim($spline[$invtype_no]);
          $invdate = trim($spline[$invdate_no]);
          $invdate = str_replace('/', '-', $invdate);
          $invdate = implode('-', array_reverse(explode('-', $invdate)));
          $itemtype = trim($spline[$itemtype_no]);
          $dlco = trim($spline[$dlco_no]);
          
            $tot = trim($spline[$total_no]);
            $tot = trim($tot,'"');
            $tot = str_replace(',', '', $tot);
       
          
      
        $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
    if(mysqli_num_rows($query)<0)
        {
            print "<script>alert('You Are Selected a Wrong Branch...!!!');</script>";
            return;
        }
        
        print $invno_no;
        
        
    if($invno_no<='')
        {
            print "<script>alert('$invno_no You Are Selected a Wrong  File...!!!');</script>";
            return;
        }
      $k=$i-1;
    if(strlen($invno)>0 && $qty>0)
      {
          
                            if($itemtype=='ZLUB')
                                $mitemtype='Lubricant';
                            else
                                $mitemtype='Spares';
        print $sql="insert into $tbl (invno,invtype,invdate,entrydate,mattype,dealercode,total) 
        values('$invno','$invtype','$invdate','$invdate','$mitemtype','$tot',$dlco,'$brcode')";
        
        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_spareinvoice.php';</script>";
  print "<font color=green><<==================================SAP v/s PSN Software===========================================>><br>";
	$sql1=mysqli_query($db,"select sum(qty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,invno from $tbl where brcode='$brcode' group by invno");
    while($row1=mysqli_fetch_array($sql1))
	{
	    $ctr=0;
	    $flg=0;
		$qry1=mysqli_query($db,"select sum(qty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,invno from spareinvoice where invno='$row1[invno]' and brcode='$brcode' group by invno");
		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[invno]</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[invno]</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[invno]</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[invno]</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[invno]</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[invno]</font><br>";	
			  $flg=1;
			}
			if($flg!=1)
			{
			  print "<font color=blue>Checking Done Successfuly...!!!$row1[invno]</font><br>";
			}
			$ctr++;
		}
		if($ctr==0)
		    print "<font color=red>Sap Invoice No ($row1[invno]) not updated in Our System ............!!!</font><br>";	
	}
	print "<font color=black><<==================================PSN Software v/s SAP===========================================>><br>";
	$sql1=mysqli_query($db,"select sum(qty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,invno from spareinvoice where brcode='$brcode' and entrydate >= '$df' and entrydate <= '$dt' group by invno");
    while($row1=mysqli_fetch_array($sql1))
	{
	    $ctr=0;
	    $flg=0;
		$qry1=mysqli_query($db,"select sum(qty),sum(aprice),sum(price),sum(taxamt),sum(tot),tax,invno from $tbl where invno='$row1[invno]' and brcode='$brcode' group by invno");
		while($re=mysqli_fetch_array($qry1))
		{
			if($re[0]!=$row1[0])
			{
			  print "<font color=red>PSN Qty ($row1[0]) Is Not Equal SAP Qty ($re[0])...Of INVNO: $row1[invno]</font><br>";
			  $flg=1;
			}
			if($re[1]!=$row1[1])
			{
			  print "<font color=red>PSN Aprice ($row1[1]) Is Not Equal SAP Aprice ($re[1])...Of INVNO: $row1[invno]</font><br>";
			  $flg=1;
			}
			if($re[2]!=$row1[2])
			{
			  print "<font color=red>PSN price ($row1[2]) Is Not Equal SAP price ($re[2])...Of INVNO: $row1[invno]</font><br>";	
			  $flg=1;
			}
			if($re[3]!=$row1[3])
			{
			  print "<font color=red>PSN Taxamount ($row1[3]) Is Not Equal SAP Taxamount ($re[3])...Of INVNO: $row1[invno]</font><br>";	
			  $flg=1;
			}
			if($re[4]!=$row1[4])
			{
			  print "<font color=red>PSN Total ($row1[4]) Is Not Equal SAP Total ($re[4])...Of INVNO: $row1[invno]</font><br>";	
			  $flg=1;
			}
			if($re[tax]!=$row1[tax])
			{
			  print "<font color=red>PSN Tax ($row1[tax]) Is Not Equal SAP Tax ($re[tax])...Of INVNO: $row1[invno]</font><br>";	
			  $flg=1;
			}
			if($flg!=1)
			{
			  print "<font color=blue>Checking Done Successfuly...!!!$row1[invno]</font><br>";
			}
			$ctr++;
		}
		if($ctr==0)
		    print "<font color=red>PSN Invoice No ($row1[invno]) not updated in SAP System ............!!!</font><br>";	
	}

	/*
	$sql2=mysqli_query($db,"select * from spareinvoice where brcode='$brcode' group by invno,partcode");
    while($row2=mysqli_fetch_array($sql2))
	{
		$qry2=mysqli_query($db,"select invno from $tbl where invno='$row2[invno]' and qty='$row2[qty]' and aprice='$row2[aprice]' and tax='$row2[tax]' and taxamt='$row1[taxamt]' and tot='$row1[tot]' and brcode='$brcode'");
		if(mysqli_num_rows($qry2)<0)
		{
			print "<font color=blue>Checking Done $row2[invno] Successfuly...!!!</font><br>";
		}
		else
		{
			print "<font>Invoice Number $row2[invno] Does Not Exist In</font><br>";
		}
	}
	*/
}
?>
