<?
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");
if(isset($_POST['upd']))
{
	$tbl="jobcardcheck".$brcode;
	mysqli_query($db,"drop table $tbl");
	mysqli_query($db,"CREATE TABLE $tbl (invno varchar(20),qty double(10,2) default 0,tot double(10,2),partcode varchar(40),itemdesc varchar(200),invdate date)");
	mysqli_query($db,"delete from $tbl");
	$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;
	}
  $ctr=1;
  $j=1;
  $invnoamt_arr[]='';
  $invno_prv='';
  $from_dt ='';
  $to_dt = '';
  $t=1;
  $j=1;
  $jcdt_no=0;
  $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
  $ctr=1;
  $j=1;
  $invno_arr[]='';
  $invno_prv='';
  $flg = 0;
  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]=='Invoice Date')
					$jcdt_no = $x;			
				if($spline[$x]=='Dealer Invoice Number')
					$invno_no1 = $x;
			}
		}
		if($j>1)
		{
			$invno1 = trim($spline[$invno_no1]);
			if($invno1!=$invno_prv)
			{
				$tot = 0;
				$query="select invno from jobcard where invno='$invno1' and brcode='$brcode'";
				$rs=mysqli_query($db,$query);
				if(mysqli_num_rows($rs)>0)
				{
					$invno_arr[$ctr]=$invno1;
					$sql="select sum(amt)sparice,sum(taxamt)stx,sum(cessamt)ces,sum(gtot)tt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt)igstamt from jcardparts where invno='$invno1' and brcode='$brcode' group by invno";
					//print $sql.'<br>';
					$invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
					$invrow=mysqli_fetch_array($invre);
					$tot = $invrow[3];
					$sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$invno1' and brcode='$brcode' group by invno";
					$jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
					$jrow2=mysqli_fetch_array($jres2);
					$tot = $tot + $jrow2[4];
					$invnoamt_arr[$ctr]=$tot;					
				}
				$query="select sum(tot) from spareinvoice where invno='$invno1' and brcode='$brcode'";
				$invre=mysqli_query($db,$query) or die('Err:'.mysqli_error($db));
				$invrow=mysqli_fetch_array($invre);
				if($invrow[0]>0)
				{
					$invno_arr[$ctr]=$invno1;
					$tot += $invrow[0];
					$invnoamt_arr[$ctr]=$tot;					
				}
				$ctr++;
			}
			$invno_prv=$invno1;
   			if($spline[$jcdt_no]>'' && $spline[$jcdt_no] !='Invoice Date')
			{
				$jcdt = trim($spline[$jcdt_no]);
				$jcdt = str_replace('/', '-', $jcdt);
				$jcdt = implode('-', array_reverse(explode('-', $jcdt)));
				if($t==1)
				{	
					$from_dt =$jcdt;
					$t++;
				}
			}
		}$j++;
  }
  $to_dt =$jcdt;
  print  "<br><br>Date From ".implode('-', array_reverse(explode('-', $from_dt)))." to ".implode('-', array_reverse(explode('-', $to_dt)))."<br>";  

  //-------------------------------------------------------------
  $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
  $i=1;
  $invno_prv1='';
  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 (Bill to Party )')
          $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($spline[$x]=='JOIG Rate(%)')
            $igstp_no = $x;
         if($spline[$x]=='JOCG Rate(%)')
            $cgstp_no = $x;
         if($spline[$x]=='JOSG Rate(%)')
            $sgstp_no = $x;
        if($spline[$x]=='Invoice Number')
            $sapinvno_no = $x;
        if($spline[$x]=='Registration No.')
            $msno_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]);
          if(strlen($tot)<=0 && $i<=3 )
          {
                print "<script>alert('Invalid data !!!');</script>";
                return;
          }
          $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]);
          $cgstp = trim($spline[$cgstp_no]);
          $sgstp = trim($spline[$sgstp_no]);
          $igstp = trim($spline[$igstp_no]);
          $tot = trim($spline[$total_no]);
          $mrp = trim($spline[$mrp_no]);
          $dlco = trim($spline[$dlco_no]);
          $sapinvno = trim($spline[$sapinvno_no]);
          $msno = trim($spline[$msno_no]);
          $sapinvno = trim($sapinvno,'"');
          $sapinvno = str_replace(',', '', $sapinvno);
          $cname = trim($cname, '"');
          $cname=str_replace("'","", $cname);
          $cname = str_replace(';', '', $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);

          $cgstp = trim($cgstp,'"');
          $cgstp = trim($cgstp,'%-');
          $cgstp = str_replace(',', '', $cgstp);
          $sgstp = trim($sgstp,'"');
          $sgstp = trim($sgstp,'%-');
          $sgstp = str_replace(',', '', $sgstp);
          $igstp = trim($igstp,'"');
          $igstp = trim($igstp,'%-');
          $igstp = str_replace(',', '', $igstp);          
          if($cgstp<='')
          $cgstp=0;
          if($sgstp<='')
          $sgstp=0;
          if($igstp<='')
          $igstp=0;
          if($cgstp>0)
          {
             $cgstamt = $tottax/2;
             $sgstamt = $tottax/2;
          }
          else
          {
              $cgstamt = 0;
              $sgstamt = 0;
          }
          
          if($igstp>0)
             $igstamt = $tottax;             
          else
             $igstamt = 0;             
          $tot = trim($tot,'"');
          $tot = str_replace(',', '', $tot);
          $mrp = trim($mrp,'"');
          $mrp = str_replace(',', '', $mrp);
          $dlco = str_replace(',', '', $dlco);
        $cessamt = $tot - $tottax - $grsval;
        if($cessamt>0)
        {
            $cessp=1;
        }
        else
        {
            $cessamt=0;
            $cessp=0;
        }
        $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
        if(mysqli_num_rows($query)<1)
        {
			if($dlco>'')
	            print "<script>alert('You have Selected a Wrong File');</script>";
            return;
        }
        $jcno_no1 = $jcno_no;
        if($jcno_no==0)
           $jcno_no1 = 1;
        if($invno_no<='' || $tax_no<='' || $tottax_no<='' || $total_no<='')
        {
            print "<script>alert('Some Fields Does Not Exist In File...!!!');</script>";
            return;
        }
      $k=$i-1;
	  //print $invno."***<br>";
      if(strlen($invno)>0 && $grsval>0)
      {
        //print $b=array_search($invno,$invno_arr);
        $cname=mysqli_escape_string($cname);
        if(array_search($invno,$invno_arr)>0)
        {
            if($tot>=0)
            {
                if($itemtype=='Labor Value'||$itemtype=='Outside Labor'||$itemtype=='Misc. Labor')
                {
					mysqli_query($db,"insert into $tbl values ('$invno','$qty','$tot','$itemno','$itemdesc','$jcdt')");
                }
                else
                {       
                    $pcode = $itemno;
                    if($itemtype=='Spares'||$itemtype=='Local Parts')
                        $mitemtype='Spares';
                    else
                        $mitemtype=$itemtype;
					mysqli_query($db,"insert into $tbl values ('$invno','$qty','$tot','$itemno','$itemdesc','$jcdt')");
                }
            }
            //print "<font color='blue'>".$i.".Jobcard Inserted Successsfuly $tot--".$invno."<br></font>";
        }
        else
        {
			$flg = 1;
			print "<font color='red'>".$i.".Invoice not found.....!!!--".$invno."<br></font>";
        }
    }
  }
    $i++;
  }
	$sq="select sum(tot),invno,invdate from $tbl group by invno";
	$jres11=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
	print "<table>";
	$j=1;
	while($jrow11=mysqli_fetch_array($jres11))
	{
		$invno = $jrow11[1];
		$no = array_search($invno,$invno_arr);
        if($no>0)
        {
			if(abs($invnoamt_arr[$no]-$jrow11[0])>1)
			{
				if($j==1)
				{
					print "<tr><th>Mismatch found in Online & SAP </th><th></th><th>Online</th><th>SAP</th><tr>";
					print "<tr><th>Invoice No.</th><th>Invoice Date</th><th>Amount</th><th>Amount</th><tr>" ;
					$j++;
					$flg = 1;
				}
				$minvdate = implode('-', array_reverse(explode('-', $jrow11[2])));
				print "<tr><td><a href ='invoice_comp1.php?invno=$invno_arr[$no]'>".$invno_arr[$no]."</a></td><td>".$minvdate."</td><td align='right'>".$invnoamt_arr[$no]."</td><td align='right'>".$jrow11[0]." </td><tr>";
			}
		}
	}	
	$sq="select distinct(invno) from jobcard where entrydate >= '$from_dt' and entrydate <='$to_dt' and brcode = '$brcode' ";
	$jres11=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
	print "<table>";
	$j=1;
	while($jrow11=mysqli_fetch_array($jres11))
	{
		$sql="select invno from $tbl where invno='$jrow11[0]'";
		$jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
		$jrow2=mysqli_fetch_array($jres2);
		if($jrow2[0]<='')
		{
			print "<tr><td><b> Service Invoice No. $jrow11[0] Not found in SAP </td></tr>";
			$flg=1;
		}
	}
	$sq="select distinct(invno) from spareinvoice where entrydate >= '$from_dt' and entrydate <='$to_dt' and brcode = '$brcode' ";
	$jres11=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
	print "<table>";
	$j=1;
	while($jrow11=mysqli_fetch_array($jres11))
	{
		$sql="select invno from $tbl where invno='$jrow11[0]'";
		$jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
		$jrow2=mysqli_fetch_array($jres2);
		if($jrow2[0]<='')
		{
			print "<tr><td><b> Spare Invoice No. $jrow11[0] Not found in SAP </td></tr>";
			$flg=1;
		}
	}

	if($flg == 0)
		print "NO MISMATCHES FOUND ............";
}

?>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(15, 217, 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 Jobcard Invoices (For verifying purpose only)</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>