<?
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']))
{
    $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;
    $invno_arr[]='';
    $invno_prv='';
    $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
    $i=1;
    $invno_prv1='';
    print "<table width=70%   align='center' class='tbl1' border='1'>";
    print "<tr><td rowspan=2>JC No.</td><td rowspan=2>Invoice No.</td><td rowspan=2>Invoice Date.</td><td rowspan=2>Item/Labour</td><td colspan=2>Before Tax</td><td colspan=2>CGST Amount</td><td colspan=2>SGST Amount</td><td colspan=2>IGST Amount</td><td colspan=2>TOTAL</td></tr>";
    print "<tr><td>Online</td><td>SAP</td><td>Online</td><td>SAP</td><td>Online</td><td>SAP</td><td>Online</td><td>SAP</td><td>Online</td><td>SAP</td></tr>";
    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($jcno)<=0 && $i<=3 )
            {
                print "<script>alert('Invalid Jobcard Number !!!');</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);
            if($dlco>'')
            {
                $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
                if(mysqli_num_rows($query)<1)
                {
                    print "<script>alert('You have Selected a Wrong File');</script>";
                    return;
                }
            }
            $jcno_no1 = $jcno_no;
            if($jcno_no==0)
                $jcno_no1 = 1;
            if($jcno_no1<='' || $jcdt_no<='' || $invno_no<='' || $cname_no<='' || $itemno_no<='' || $qty_no<='' || $tax_no<='' || $tottax_no<='' || $total_no<='')
            {
                print "<script>alert('Some Fields Does Not Exist In File...!!!');</script>";
                return;
            }
            $k=$i-1;
            if($tot>0)
            {
            if(strlen($invno)>0)
            {
                $cname=mysqli_escape_string($cname);
                $brrow=mysqli_fetch_array($brres);
                if($itemtype=='Labor Value'||$itemtype=='Outside Labor'||$itemtype=='Misc. Labor')
                {
                    $brres=mysqli_query($db,"select * from jcardserv where brcode = '$brcode' and jcno = '$jcno' and service = '$itemdesc'");
                    $brrow=mysqli_fetch_array($brres);
                    if(mysqli_num_rows($brres)>=1)
                    {
                        if($brrow[servamt]!=$grsval || $brrow[tot]!=$tot || $brrow[cgstamt]!=$cgstamt || $brrow[sgstamt]!=$sgstamt || $brrow[igstamt]!=$igstamt)
                        {
                            print "<tr><td>".$jcno."</td><td>".$invno."</td><td>".$jcdt."</td><td>$itemdesc</td><td>"."$brrow[servamt]</td><td>$grsval</td><td>$brrow[cgstamt]</td><td>$cgstamt</td><td> $brrow[sgstamt]</td><td>$sgstamt</td><td> $brrow[igstamt]</td><td>$igstamt</td><td>$brrow[tot]</td><td>$tot</td></tr>";
                        }
                    }
                    else
                    {
                        print "<tr><td><<JC No.$jcno Inv.No.:$invno Not found in online system>></td>";
                    }
                }
                else
                {
                    $brres=mysqli_query($db,"select * from jcardparts where brcode = '$brcode' and jcno = '$jcno' and partcode = '$itemno'");
                    
                           print    $b=("select * from jcardparts where brcode = '$brcode' and jcno = '$jcno' and partcode = '$itemno'");
                    
                    $brrow=mysqli_fetch_array($brres);
                    if(mysqli_num_rows($brres)>=1)
                    {
                        if($brrow[amt]!=$grsval || $brrow[gtot]!=$tot || $brrow[cgstamt]!=$cgstamt || $brrow[sgstamt]!=$sgstamt || $brrow[igstamt]!=$igstamt)
                        {
                            print "<tr><td>".$jcno."</td><td>".$invno."</td><td>$itemno</td><td>"."$brrow[amt]</td><td>$grsval</td><td>$brrow[cgstamt]</td><td>$cgstamt</td><td>$brrow[sgstamt]</td><td>$sgstamt</td><td>$brrow[igstamt]</td><td>$igstamt</td><td>$brrow[gtot]</td><td>$tot</td></tr>";
                        }
                    }
                    else
                    {
                        print "<tr><td>$jcno</td><td>$invno</td><td colspan=4>Not found in online system</td>";
                    }    
                }
            }
            }
            
        }
        $i++;
    }
}
print "</table>";
print "<script> alert('Checking Completed....!!!');</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, 17);}
.mnu{margin-left:5}
.tbl1 td{
border: 1px solid black;
border-bottom:0px solid white;
font-family:Calibri;
border-collapse:collapse;
margin-top:25px;
}
.tbl1 tr{
border-bottom:1px solid black;

}
</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 checking</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>