<?session_start();
include("../opendb.php");
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
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');
	}
	$fp=fopen($filepath.$imagename,"r") or die("Cant open file");
	$i=0;
	//include("opendb.php");
	$dt=date("Y-m-d H:i:s");
	$msql[]='';
	$x=0;
	$err=0;
	while(!feof($fp))
	{
 		$line=fgets($fp,1024);
 		$spline=explode("\t",$line);
 		
 		if(strlen($spline[0])>0)
 		{
			$exphd	= trim($spline[1]);
			$mdate = substr($spline[11],0,10);
			if((substr($mdate,2,1)=='-'))
			    $leddt=implode('-', array_reverse(explode('-', $mdate)));  
			else
			    $leddt	 =$mdate;
			$leddt = str_replace(' ', '', $leddt);
			$vndinvno = trim($spline[2]); 
		    $supnm	= trim($spline[3]); 
			if((substr($spline[4],2,1)=='-'))
			    $supdt=implode('-', array_reverse(explode('-', $spline[4])));  
			else
			    $supdt=$spline[11];
			    
			if(strpos($supdt,'-')==2)
			{
			    $supdt=substr($supdt,-4)."-".substr($supdt,3,2)."-".substr($supdt,0,2);
			}
            $supdt = str_replace(' ', '', $supdt);
			$narrat	= trim($spline[5]);
			$narrat = str_replace('"', '', $narrat);
            $taxtype= 'INTRASTATE';
            $ledtyp=$spline[13];
            if($ledtyp<='')
            {
            $led    = 'Material';
            }
            else
            {
                $led    = $ledtyp;
            }
			$query11=mysqli_query($db,"select max(refno) from gst_expense where brcode='$brcode'");
			$rw11=mysqli_fetch_array($query11);
			if($rw11[0]<1)
			{
			     $refno='000001';
			}
			else
			{
				$refno=substr($rw11[0],-6)+1;
				$refno=substr('000000'.$refno,-6);
			}
			$hsntyp=$spline[12];
			if($hsntyp<='')
            {
                $hsncode='8310';
            }
            else
            {
                $hsncode=$hsntyp;
            }
			
			$befrtx = trim($spline[6]); 
			$befrtx = trim($befrtx,'"');
			$befrtx = str_replace(' ', '', $befrtx);
	        $befrtx = str_replace(',', '', $befrtx);
	        $befrtx = str_replace('-', '0', $befrtx);
			$led    = trim($spline[1]); 
			$cgstamt= trim($spline[8]); 
			$cgstamt = trim($cgstamt,'"');
			$cgstamt = str_replace(' ', '', $cgstamt);
	        $cgstamt = str_replace(',', '', $cgstamt);
	        $cgstamt = str_replace('-', '0', $cgstamt);
			$sgstamt= trim($spline[7]); 
			$sgstamt = trim($sgstamt,'"');
			$sgstamt = str_replace(' ', '', $sgstamt);
	        $sgstamt = str_replace(',', '', $sgstamt);
	        $sgstamt = str_replace('-', '0', $sgstamt);
	        
	        $igstamt= trim($spline[14]); 
			$igstamt = trim($igstamt,'"');
			$igstamt = str_replace(' ', '', $igstamt);
	        $igstamt = str_replace(',', '', $igstamt);
	        $igstamt = str_replace('-', '0', $igstamt);
	        
	        if($igstamt>0)
	        {
	            $igstp	= 18;
			    $cgstamt= 0;
			    $sgstamt= 0;
			    $cgstp= 0;
			    $sgstp= 0;
			    $taxtype= 'INTERSTATE';
	        }
	        else
	        {
	            $igstp	= 0;
			    $igstamt= 0;
			    $cgstp	= 9;
		        $sgstp	= 9;
	        }
			$kfcamt = 0;
			
			$ntamt  = trim($spline[10]); 
			$ntamt = trim($ntamt,'"');
			$ntamt = str_replace(' ', '', $ntamt);
	        $ntamt = str_replace(',', '', $ntamt);
	        $ntamt = str_replace('-', '0', $ntamt);
			$rr=mysqli_query($db,"select max(exp_no) from gst_expense where brcode='$brcode'");
	        $rrrow=mysqli_fetch_row($rr);
	        if($rrrow[0]<=0)
	        {
	 	        $expno=1;
	        }
	        else
	        {
	 	        $expno=($rrrow[0]+1);
	        }
	        
	        $jvres=mysqli_query($db,"select max(jvno) from journal2 where brcode='$brcode'");
		    $jvrow=mysqli_fetch_row($jvres);
		    if($jvrow[0]>0)
		    {
	 	        $jvno=($jvrow[0]+1);
		    }
		    else
		    {
		        $jvno=1;
		    }		
		    //echo "INSERT INTO gst_expense(exp_no,date,expense_head,vendor,narr,before_tax,cgstp,cgstamt,sgstp,sgstamt,expense_amount,brcode,vendrinvno,vendrinvdt,jvno,txtype,acchead,refno,hsncode,kfcamt,igstp,igstamt) VALUES ('$expno','$leddt','$exphd','$supnm','$narrat','$befrtx','$cgstp','$cgstamt','$sgstp','$sgstamt','$ntamt','$brcode','$vndinvno','$supdt','$jvno','$taxtype','$led','$refno','$hsncode','$kfcamt','$igstp','$igstamt')";
		    //exit;
		    //echo "select exp_no from gst_expense where brcode='$brcode' and vendrinvno = '$vndinvno' ";
		    //exit;
		    $rr1=mysqli_query($db,"select exp_no from gst_expense where brcode='$brcode' and vendrinvno = '$vndinvno' and vendor='$supnm'");
	        $rrrow1=mysqli_fetch_row($rr1);
		    if($rrrow1[0]>0)
		    {
		        $rr=0;
		        echo "Already updated Vendor:$supnm, Invno:$vndinvno";
		        echo "<br>";
		    }
		    else
		    {
		        //echo "test";
		        //exit;
			$sql1="INSERT INTO gst_expense(exp_no,date,expense_head,vendor,narr,before_tax,cgstp,cgstamt,sgstp,sgstamt,expense_amount,brcode,vendrinvno,vendrinvdt,jvno,txtype,acchead,refno,hsncode,kfcamt,igstp,igstamt) VALUES ('$expno','$leddt','$exphd','$supnm','$narrat','$befrtx','$cgstp','$cgstamt','$sgstp','$sgstamt','$ntamt','$brcode','$vndinvno','$supdt','$jvno','$taxtype','$led','$refno','$hsncode','$kfcamt','$igstp','$igstamt')";
			print $sql1;
	        mysqli_query($db,$sql1);

			$taxdrres=mysqli_query($db,"select date,exp_no,expense_head,vendor,narr,sum(before_tax),sum(cgstamt),sum(sgstamt),sum(igstamt),sum(expense_amount),cgstp,sgstp,igstp,acchead,refno,jvno from gst_expense where exp_no='$expno' and brcode='$brcode' and cgstp > 0 group by exp_no,acchead,cgstp")or die('Led Post Err3'.mysqli_error());
    	while($taxdrrow=mysqli_fetch_array($taxdrres))
        {
            if($taxdrrow[6]>0)
            {
                $ledger='Input CGST Expense'.$taxdrrow[10].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[6]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
            if($taxdrrow[7]>0)
            {
                $ledger='Input SGST Expense'.$taxdrrow[11].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[7]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
            if($taxdrrow[8]>0)
            {
                $ledger='Input IGST Expense'.$taxdrrow[12].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[8]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
        }
        $taxdrres=mysqli_query($db,"select date,exp_no,expense_head,vendor,narr,sum(kfcamt),acchead,refno,jvno from gst_expense where exp_no='$expno' and brcode='$brcode' and kfcamt > 0 group by exp_no")or die('Led Post Err3'.mysqli_error());
    	while($taxdrrow=mysqli_fetch_array($taxdrres))
    	{
    	    $ledger='KFC 1% On Expense';
            $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[8]','Dr','$ledger','$taxdrrow[5]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[7]','0')";
            mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
    	}
        $taxdrres=mysqli_query($db,"select date,exp_no,expense_head,vendor,narr,sum(before_tax),sum(cgstamt),sum(sgstamt),sum(igstamt),sum(expense_amount),cgstp,sgstp,igstp,acchead,refno,jvno from gst_expense where exp_no='$expno' and brcode='$brcode' and igstp > 0 group by exp_no,acchead,igstp")or die('Led Post Err3'.mysqli_error());
 	    while($taxdrrow=mysqli_fetch_array($taxdrres))
        {
            if($taxdrrow[6]>0)
            {
                $ledger='Input CGST Expense'.$taxdrrow[10].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[6]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
            if($taxdrrow[7]>0)
            {
                $ledger='Input SGST Expense'.$taxdrrow[11].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[7]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
            if($taxdrrow[8]>0)
            {
                $ledger='Input IGST Expense'.$taxdrrow[12].'%';
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[8]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
		}
		$taxdrres=mysqli_query($db,"select date,exp_no,expense_head,vendor,narr,sum(before_tax),sum(cgstamt),sum(sgstamt),sum(igstamt),sum(expense_amount),cgstp,sgstp,igstp,acchead,refno,jvno from gst_expense where exp_no='$expno' and brcode='$brcode' group by exp_no")or die('Led Post Err3'.mysqli_error());
	    while($taxdrrow=mysqli_fetch_array($taxdrres))
        {
			$ledger=$taxdrrow[2];
            $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','$ledger','$taxdrrow[5]','0','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
            mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
			if($taxdrrow[9]>0)
            {
                $ledger=$taxdrrow['vendor'];
                $dif_p=$taxdrrow[9];
                $dif_exp=round($dif_p);

                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Cr','$ledger','0','$dif_exp','$taxdrrow[4]','$brcode','GST Expense','GST Expense','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die('Led Post Err1'.mysqli_error());
            }
			$jvdifres=mysqli_query($db,"select sum(dramt)-sum(cramt) from journal2 where jvno='$taxdrrow[15]' and brcode='$brcode'");
            $jvdifrow=mysqli_fetch_row($jvdifres);
            $dif=$jvdifrow[0];
            if($dif<0)
            {
                $xx=abs($dif);
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Dr','Round Off','".$xx."', '0','Round of GST EXP No: $taxdrrow[14]','$brcode','GST Expense','REF No','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die("J V Write error dif1:".mysqli_error());
            }
            else if($dif>0)
            {
                $xx=abs($dif);
                $sql="insert into journal2 values('$taxdrrow[0]','$taxdrrow[15]','Cr','Round Off','0','".$xx."','Round of GST EXP No: $taxdrrow[14]','$brcode','GST Expense','REF No','$taxdrrow[14]','0')";
                mysqli_query($db,$sql) or die("J V Write error dif:".mysqli_error());
            }
        }
		}
	}
	}
	print "<script> alert('Completed !!!');</script>";
}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'arial narrow';letter-spacing:1;}
tr{background-color:c1d799}
.mnu{margin-left:5}
</style>
<center>
<br><br><br><br><br><br>
<form action='gst_upld.php' method='post' ENCTYPE="multipart/form-data">
<table border=1>
<tr><td>
<table>
<th colspan=2>Upload GST Multiple Expense Entries
<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>Columns :  <tr><td>Sl No	A/C Head	INV NO	SUPPLIER	INV DATE	NARRATION	 B 4 GST 	 SGST 	 CGST 	 RO OFF 	 INV AMT 
</table>
</td></tr>
</table>
</form>
