<?
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>
#loading {
    position: absolute; width: 100%; height: 100%; background: url('images/loader.gif') no-repeat center center;
}
</style>
<script>

$(window).ready(function() {
    $('#loading').hide();
});

</script>
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(95, 197, 17);}
.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=60%>
<tr><td>
<table width=100%>
<th colspan=2><h1>Check Jobcard Invoices</h1>
<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>Always download the Jobcard invoices from SAP with below columns in ANY ORDER (Job Card No.,Invoice Date,Dealer Invoice Number,Billing Type,<br>Item Type,Customer Name,Item No.,Item Description,Quantity Billed,List Price,Net Taxable Amount,Discount Amount,Tax Rate(%),Handling,Tax Amount,Net Amount,MRP,Dealer Code)<br>(Convert it to TAB TEXT Format and upload)
 </td></tr>
</table>
</td></tr>
</table>
</form>
</body>
</html>
<?

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="jcardparts".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl(`jcno` int(12) UNSIGNED NOT NULL,`brcode` varchar(25) DEFAULT NULL,`partcode` varchar(100) DEFAULT NULL,`partname` varchar(100) DEFAULT NULL,`rate` double(14,2) DEFAULT NULL,`qty` double(14,2) DEFAULT NULL,`amt` double(14,2) DEFAULT NULL,`disc` double(14,2) DEFAULT NULL,`vat` double(14,2) DEFAULT NULL,`cessp` double(14,2) DEFAULT NULL,`cessamt` double(14,2) DEFAULT NULL,`hnd` double(14,2) DEFAULT NULL,`taxamt` double(14,2) DEFAULT NULL,`gtot` double(14,2) DEFAULT NULL,`jvno` int(11) DEFAULT '0',`invno` varchar(50) DEFAULT NULL,`date` date DEFAULT NULL,`itemtype` varchar(50) NOT NULL)");

$tbl1="jcardserv".$brcode.$user;
mysqli_query($db,"drop table $tbl1");
mysqli_query($db,"CREATE TABLE $tbl1(`jcno` varchar(50) DEFAULT NULL,`brcode` varchar(100) DEFAULT NULL,`service` varchar(250) DEFAULT NULL,`servamt` double(14,2) DEFAULT NULL,`descr` varchar(250) DEFAULT '-',`servtx` double(14,2) DEFAULT '0.00',`edutx` double(14,2) DEFAULT '0.00',`hredutx` double(14,2) DEFAULT '0.00',`tot` double(14,2) DEFAULT '0.00',`jvno` int(11) DEFAULT '0',`invno` varchar(50) 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(trim($spline[$x])=='Job Card No.')
		   $jcno_no = $x;
	 	 if($spline[$x]=='Invoice Date')
		  $jcdt_no = $x;
         if($spline[$x]=='Dealer Invoice Number')
          $invno_no = $x;
         if($spline[$x]=='Billing Type')
          $invtype_no = $x;
         if($spline[$x]=='Item Type')
          $itemtype_no = $x;
         if($spline[$x]=='Customer Name')
          $cname_no = $x;
         if($spline[$x]=='Item No.')
          $itemno_no = $x;
         if($spline[$x]=='Item Description')
          $itemdesc_no = $x;
         if($spline[$x]=='Quantity Billed')
          $qty_no = $x;
         if($spline[$x]=='List Price') 
          $lstprice_no = $x;
         if($spline[$x]=='Net Taxable Amount')
          $grsval_no = $x;
         if($spline[$x]=='Discount Amount')
          $disc_no = $x;
         if($spline[$x]=='Tax Rate(%)')
          $tax_no = $x;
         if($spline[$x]=='Handling')
          $hnd_no = $x;
         if($spline[$x]=='Tax Amount')
          $tottax_no = $x;
         if($spline[$x]=='Net Amount')
          $total_no = $x;
         if($spline[$x]=='MRP')
          $mrp_no = $x;
          if($spline[$x]=='Dealer Code')
          $dlco_no = $x;
               }
    }
    if($i>1)
    {
          $jcno = trim($spline[$jcno_no]);	 			
		  $jcdt = trim($spline[$jcdt_no]);	
		  $jcdt = str_replace('/', '-', $jcdt);
		  $jcdt = implode('-', array_reverse(explode('-', $jcdt)));
		  $invno = trim($spline[$invno_no]);
          $invtype = trim($spline[$invtype_no]);
          $cname = trim($spline[$cname_no]);
          
          $itemno = trim($spline[$itemno_no]);
          $itemdesc = trim($spline[$itemdesc_no]);
          $itemdesc =mysqli_escape_string($itemdesc);
          $qty = trim($spline[$qty_no]);
          $itemtype = trim($spline[$itemtype_no]);
          $lstprice = trim($spline[$lstprice_no]);
          $tottax = trim($spline[$tottax_no]);
          $disc = trim($spline[$disc_no]);
          $grsval = trim($spline[$grsval_no]);
          $hnd = trim($spline[$hnd_no]);
          $tax = trim($spline[$tax_no]);
          $tot = trim($spline[$total_no]);
          $mrp = trim($spline[$mrp_no]);
          $dlco = trim($spline[$dlco_no]);
          
          $cname = trim($cname, '"');
          $qty = trim($qty,'"');
          $qty = str_replace(',', '', $qty);
          $lstprice = trim($lstprice,'"');
          $lstprice = str_replace(',', '', $lstprice);
          $tottax = trim($tottax,'"');
          $tottax = str_replace(',', '', $tottax);
          $disc = str_replace(',', '', $disc);
          $disc = trim($disc,'"');
          $grsval = trim($grsval,'"');
          $grsval = str_replace(',', '', $grsval);
          $hnd = trim($hnd,'"');
          $hnd = str_replace(',', '', $hnd);
          $tax = trim($tax,'"');
          $tax = trim($tax,'%-');
          $tax = str_replace(',', '', $tax);
          $tot = trim($tot,'"');
          $tot = str_replace(',', '', $tot);
          $mrp = trim($mrp,'"');
          $mrp = str_replace(',', '', $mrp);
          $dlco = str_replace(',', '', $dlco);
		  $cname=mysqli_escape_string($cname);

        $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 File');</script>";
            return;
        }
        if($jcno_no<='' || $jcdt_no<='' || $invno_no<='' || $cname_no<='' || $itemno_no<='' || $qty_no<='' || $tax_no<='' || $tottax_no<='' || $total_no<='')
        {
            print "<script>alert('You Are Selected a Wrong  File...!!!');</script>";
            return;
        }
      $k=$i-1;
      if(strlen($invno)>0)
      {
               if($tot>0)
               {
                    if($itemtype=='Labor Value'||$itemtype=='Outside Labor')
                        {
                            $sql1="insert into $tbl1 (jcno,brcode,service,servamt,descr,servtx,tot,invno) values('$jcno','$brcode','$itemdesc','$grsval','$itemdesc','$tax','$tot','$invno')";
        
                            //print "<font color='blue'>".$i.". ".$sql1."HH".$itemtype."<br></font>";
							
                        }
                        else
                        {       if($itemtype=='Spares'||$itemtype=='Local Parts')
                                $mitemtype='Spares';
                                else
                                $mitemtype=$itemtype;
                            $sql1="insert into $tbl (jcno,brcode,partcode,partname,rate,qty,amt,disc,vat,hnd,taxamt,gtot,invno,date,itemtype) values('$jcno','$brcode','$itemno','$itemdesc','$lstprice','$qty','$grsval','$disc','$tax','$hnd','$tottax','$tot','$invno','$jcdt','$mitemtype')";
								
                            //print "<font color='red'>".$i.". ".$sql1."HH".$itemtype."<br></font>";
            
                        }
                
                mysqli_query($db,$sql1) or die('Insert Err1:'.mysqli_error($db));
                //print "<font color='blue'>".$tot.".Jobcard Inserted Successsfuly--".$sql1."<br></font>";
               }
           
      }
    }
    
    $i++;
  }
//print "<script> alert('Successfully Uploaded....!!!');document.location='upld_jobcard.php';</script>";

	$sql1=mysqli_query($db,"select sum(qty),sum(rate),sum(amt),sum(taxamt),sum(gtot),invno from $tbl where brcode='$brcode' group by invno");
	//echo "select sum(qty),sum(rate),sum(amt),sum(taxamt),sum(gtot),invno from $tbl where brcode='$brcode' group by invno<br>";
    while($row1=mysqli_fetch_array($sql1))
	{
		$qry1=mysqli_query($db,"select sum(qty),sum(rate),sum(amt),sum(taxamt),sum(gtot),invno from jcardparts where invno='$row1[invno]' and brcode='$brcode' group by invno");
		//echo "select sum(qty),sum(rate),sum(amt),sum(taxamt),sum(gtot),invno from jcardparts where invno='$row1[invno]' and brcode='$brcode' group by invno<br>";
		while($re=mysqli_fetch_array($qry1))
		{
			if($re[0]!=$row1[0])
			{
			  print "<font color=red>Jcardparts---SAP Qty($row1[0]) Is Not Equal PSN Qty($re[0])...Of INVNO: $row1[invno]</font><br>";
			  $flg1=1;
			}
			if($re[1]!=$row1[1])
			{
			  print "<font color=red>Jcardparts---SAP Rate($row1[1]) Is Not Equal PSN Rate($re[1])...Of INVNO: $row1[invno]</font><br>";
			  $flg1=1;
			}
			if($re[2]!=$row1[2])
			{
			  print "<font color=red>Jcardparts---SAP Amt($row1[2]) Is Not Equal PSN Amt($re[2])...Of INVNO: $row1[invno]</font><br>";	
			  $flg1=1;
			}
			if($re[3]!=$row1[3])
			{
			  print "<font color=red>Jcardparts---SAP Taxamount ($row1[3]) Is Not Equal PSN Taxamount($re[3])...Of INVNO: $row1[invno]</font><br>";	
			  $flg1=1;
			}
			if($re[4]!=$row1[4])
			{
			  print "<font color=red>Jcardparts---SAP GTotal ($row1[4]) Is Not Equal PSN GTotal($re[4])...Of INVNO: $row1[invno]</font><br>";	
			  $flg1=1;
			}
		}
		
	 }
			if($flg1!=1)
			{
				print "<font color=blue>Checking Jcardparts Done Successfuly...!!!</font><br>";
			}
    $sql12=mysqli_query($db,"select sum(servamt),sum(tot),invno from $tbl1 where brcode='$brcode' group by invno");
	//echo "select sum(servamt),sum(servtx),sum(tot),invno from $tbl1 where brcode='$brcode' group by invno<br>";
	while($row12=mysqli_fetch_array($sql12))
	{
		$qry12=mysqli_query($db,"select sum(servamt),sum(tot) from jcardserv where invno='$row12[invno]' and brcode='$brcode' group by invno");
		while($re1=mysqli_fetch_array($qry12))
		{
			if($re1[0]!=$row12[0])
			{
			  print "<font color=red>Jcardserv---SAP Service Amount($row12[0]) Is Not Equal PSN Service Amount($re1[0])...Of INVNO: $row12[invno]</font><br>";
			  $flg2=1;
			}
			if($re1[1]!=$row12[1])
			{
			  print "<font color=red>Jcardserv---SAP Total($row12[1]) Is Not Equal PSN Total($re1[1])...Of INVNO: $row12[invno]</font><br>";	
			  $flg2=1;
			}
		}
	 }
			if($flg2!=1)
			{
				print "<font color=blue>Checking Jcardserv Done Successfuly...!!!</font><br>";
			}
}
?>