<?
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("../opendb.php");
include("../test.php");
 function remove_special($str){
     $newstr = "";
     for ($ctr=0;$ctr<=strlen($str);$ctr++)
     {
         if(substr($str,$ctr,1)>=' ' && substr($str,$ctr,1)<='}')
             $newstr=$newstr.substr($str,$ctr,1);
     }
     return $newstr;
}
if($_REQUEST['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;
    }
   
    $filepathval=$filepath.$imagename;
    $fp=fopen($filepath.$imagename,"r") or die("Cant open file");
     
    $ctr=1;
    $j=1;
    $invno_arr[]='';
    $invno_prv='';
    while(!feof($fp))
    {
        $line=fgets($fp,1024);
        $spline=explode("\t",$line);
        //print_r($spline);
        if($j==1)
        {
            $no_col=sizeof($spline);
			for($x=0;$x<=$no_col;$x++)
	        { 
		       if($spline[$x]=='Invoice No.')
	            $invno_no1 = $x;
	        }
	    }
	    if($j>1)
		{
			$invno1 = trim($spline[$invno_no1]);
			if($invno1!=$invno_prv)
		    {
				$query="select invno from spareinvoice where invno='$invno1' and brcode='$brcode'";
		        $rs=mysqli_query($db,$query);
		        if(mysqli_num_rows($rs)>0)
		        {
		            $invno_arr[$ctr++]=$invno1;
		        }
		    }
	        $invno_prv=$invno1;
	    }$j++;
	}
 
 
    //-------------------------------------------------------------
    $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]=='Invoice No.')
                    $invno_no = $x;
                if($spline[$x]=='Customer Code')
                    $customercode_no = $x;
                if($spline[$x]=='Fert Code')
                    $model_code_no = $x;
                if($spline[$x]=='DSE Name')
                    $rep_no = $x;
                if($spline[$x]=='District')
                    $area_no = $x;
                if($spline[$x]=='Financier Name')
                    $fin_no = $x;
                if($spline[$x]=='Invoice Date')
                    $invdate_no = $x;
                if($spline[$x]=='FERT Description')
                    $config_no = $x;
                if($spline[$x]=='Invoice Amount')
                    $unit_no = $x;
                if($spline[$x]=='Invoice Amount')
                    $netamt_no = $x;
                if($spline[$x]=='Integrated GST Rate')
                    $igstp_no = $x;
                if($spline[$x]=='State GST Rate')
                    $sgstp_no = $x;
                if($spline[$x]=='Central GST Rate')
                    $cgstp_no = $x;
                if(substr($spline[$x],0,20)=='Integrated GST Amoun')
                    $igstamt_no = $x;
                if($spline[$x]=='State GST Amount')
                    $sgstamt_no = $x;
                if($spline[$x]=='Central GST Amount')
                    $cgstamt_no = $x;                    
                if($spline[$x]=='Engine No.')
                    $engno_no = $x;
                if($spline[$x]=='Chassis No.')
                    $chasis_no = $x;
                if($spline[$x]=='TCS')
                    $tcsper_no = $x;                    
                if($spline[$x]=='TCS Amount')
                    $tcsamt_no = $x;                    
                if($spline[$x]=='HSN Code')
                    $hsncode_no = $x;                    
                if($spline[$x]=='Ship to Party Name')
                    $shipto_no = $x;       
                if($spline[$x]=='Cess Value')
                    $cess_no = $x;
                if($spline[$x]=='Cess %')
                    $cessp_no = $x;       

            }
        
        }
        $invno=$spline[$invno_no];
        
        if($i>1 && strlen($invno)>0) 
        {
            $invdate=$spline[$invdate_no];
            $customercode=$spline[$customercode_no];
            $model_code=$spline[$model_code_no];
            if(substr($invdate,2,1)=='-')
            {
                $invdate = substr($invdate,-4)."-".substr($invdate,3,2)."-".substr($invdate,0,2);
            }
            $rep=$spline[$rep_no];
            $area=$spline[$area_no];
            $fin=$spline[$fin_no];
            $config=$spline[$config_no];
            $config=remove_special($config);
            
            $unit=$spline[$unit_no];
            $netamt=$spline[$netamt_no];
            $igstp=$spline[$igstp_no];
            $sgstp=$spline[$sgstp_no];
            $cgstp=$spline[$cgstp_no];
            $igstamt=$spline[$igstamt_no];
            $sgstamt=$spline[$sgstamt_no];
            $cgstamt=$spline[$cgstamt_no];
            $cessamt=$spline[$cess_no];
            $cessp=$spline[$cessp_no];
            $taxp=$igstp+$cgstp+$sgstp;
            $igstamt=remove_special($igstamt);
            $cgstamt=remove_special($cgstamt);
            $sgstamt=remove_special($sgstamt);
            $igstamt=str_replace(",","", $igstamt);
            $igstamt=str_replace('"',"", $igstamt);
            $cgstamt=str_replace(",","", $cgstamt);
            $cgstamt=str_replace('"',"", $cgstamt);
            $sgstamt=str_replace(",","", $sgstamt);
            $sgstamt=str_replace('"',"", $sgstamt);
            $tax=$igstamt+$cgstamt+$sgstamt;
            $engno=$spline[$engno_no];
            $chasis=$spline[$chasis_no];
            $tcsper=$spline[$tcsper_no];
            $tcsamt=$spline[$tcsamt_no];
            $hsncode=$spline[$hsncode_no];
            $shipto=$spline[$shipto_no];
            
            $unit=str_replace(",","", $unit);
            $unit=str_replace('"',"", $unit);
            $netamt=str_replace(",","", $netamt);
            $netamt=str_replace('"',"", $netamt);
            $tcsamt=str_replace(",","", $tcsamt);
            $tcsamt=str_replace('"',"", $tcsamt);
            
            if(strlen($cessamt)<=0)$cessamt=0;
            $cessamt=str_replace(",","", $cessamt);
            $cessamt=str_replace('"',"", $cessamt);
            if(strlen($cessp)<=0)$cessp=0;
            
            $amt2=$unit-$tcsamt-$igstamt-$cgstamt-$sgstamt-$cessamt;
            
            if($igstp<='') $igstp=0;
            if($cgstp<='') $cgstp=0;
            if($sgstp<='') $sgstp=0;
            if($igstamt<='')$igstamt=0;
            if($cgstamt<='')$cgstamt=0;
            if($sgstamt<='')$sgstamt=0;
            if($igstp>0)$taxtype ='INTERSTATE'; else $taxtype='INTRASTATE';
            if(strlen($invno)>0)
            {
                $cust=mysqli_query($db,"select code,acname from cust_master where corporate = '$customercode' ");
                $custdb=mysqli_fetch_row($cust);
                $mcustcode=$custdb[0];
                $inchk=mysqli_query($db,"select invno from veh_invoice where invno = '$invno' ");
                $inchkdb=mysqli_fetch_row($inchk);
                if(strlen($inchkdb[0])>0)
                {
                    print "<font color='red'>Already Inserted.....!!!--".$invno."<br></font>";
                }
                else if(strlen($mcustcode[0])<=0)
                {
                    print "<font color='red'>Customer Not Found/Invalid SAP ID....!!!--".$invno."<br></font>";
                }
                else
                {
                    $cust=mysqli_query($db,"select code,acname from cust_master where corporate = '$customercode' ");
                    $custdb=mysqli_fetch_row($cust);
                    $mcustcode=$custdb[0];
                    $jvnosql=mysqli_query($db,"select max(jvno) from journal2 where brcode = '$brcode' ");
                    $jvnodb=mysqli_fetch_row($jvnosql);
                    $jvno=$jvnodb[0]+1;
                    $resdn=mysqli_query($db,"select max(jvno) from psnindia_psndbdn.journal2 where brcode='$brcode'");
                    $rowdn=mysqli_fetch_row($resdn);
                    if($rowdn[0]<=0)
                    {
                        $jvnodn=1;
                    }
                    else
                    {
                        $jvnodn=($rowdn[0]+1);
                    }
                    
                    
                    $sql = "insert into veh_invoice (cust,model_code,rep,area,fin,invno,invdate,da,config,unit,disc,netamt,taxp,tax,amt2,color,engno,segment,chasis,remark,brcode,type,jvno,cessp,cessamt,taxtype,tcsper,tcsamt,cgstp,sgstp,igstp,cgstamt,sgstamt,igstamt,hsncode,ship_to,ship_addr,ship_pin,ship_dist,ship_state,ship_pan,ship_gst) values ('$mcustcode','$model_code','$rep','$area','$fin','$invno','$invdate','','$config','$unit','0','$unit','$taxp','$tax','$amt2','','$engno','$segment','$chasis','','$brcode','-','$jvno','$cessp','$cessamt','$taxtype','$tcsper','$tcsamt','$cgstp','$sgstp','$igstp','$cgstamt','$sgstamt','$igstamt','$hsncode',NULL,'','','','','','')";
                    //echo $sql;
                    mysqli_query($db,$sql);
                    
                    $sqldn = "insert into psnindia_psndbdn.veh_invoice (cust,model_code,rep,area,fin,invno,invdate,da,config,unit,disc,netamt,taxp,tax,amt2,color,engno,segment,chasis,remark,brcode,type,jvno,cessp,cessamt,taxtype,tcsper,tcsamt,cgstp,sgstp,igstp,cgstamt,sgstamt,igstamt,hsncode,ship_to,ship_addr,ship_pin,ship_dist,ship_state,ship_pan,ship_gst) values ('$mcustcode','$model_code','$rep','$area','$fin','$invno','$invdate','','$config','$unit','0','$unit','$taxp','$tax','$amt2','','$engno','$segment','$chasis','','$brcode','-','$jvnodn','0','0','$taxtype','$tcsper','$tcsamt','$cgstp','$sgstp','$igstp','$cgstamt','$sgstamt','$igstamt','$hsncode',NULL,'','','','','','')";
                    //echo $sql;
                    mysqli_query($db,$sqldn);
                    
                    // Account posting.....
                    
                    log_entry($brcode,$user,"$jvno|Veh Invoice|Add","Veh Invoice entry-$invno",$db);
                    $sql="insert into veh_movement (date,inv_no,qty,type,brcode,sup_type,da) values('$invdate', '$invno','1','CST sale','$brcode','$mcustcode[1]','$chasis')";
                    
                    mysqli_query($db,$sql)or die(mysqli_error());
  
                    mysqli_query($db,"update vehopstock set sold='$invno' where chno='$chasis' and brcode='$brcode'");

                    $sql="update veh_purchase set sale_invoice='$invno', type='sale' where chasis='$chasis' and brcode='$brcode'";
                    mysqli_query($db,$sql)or die(mysqli_error());
                    mysqli_query($db,"update veh_transin set sale_invoice='$invno' where chasis='$chasis' and brcode='$brcode'") or die(mysqli_error());
                    $cure=mysqli_query($db,"select code,acname from cust_master where corporate='$customercode'");
                    $cust_row=mysqli_fetch_row($cure);
                    $cust_name=strtoupper($cust_row[1]);
                    if($cust_name=='')
                    {
                        $cure=mysqli_query($db,"select code,supname from sup_master where code='$mcustcode[0]'");
                        $cust_row=mysqli_fetch_row($cure);
                        $cust_name=strtoupper($cust_row[1]);
                    }

                    if($taxtype=='INTERSTATE')
                    {
    	                $sql="insert into journal2 values('$invdate','$jvno','Dr','$cust_name','$netamt', '0','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sql) or die("J V Write error 1:".mysqli_error());
    	                $sql="insert into journal2 values('$invdate','$jvno','Cr','IGST Output $taxp%', '0','$tax','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sql) or die("J V Write error 2:".mysqli_error());
    	                $sql="insert into journal2 values('$invdate','$jvno','Cr','INTERSTATE Sale $taxp%(vehicle)','0','$amt2','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sql) or die("J V Write error 3:".mysqli_error());
    	                if($tcsper>0)
                        {
	                        $sql="insert into journal2 values('$invdate','$jvno','Cr','Tax Collected at Source (TCS) @1%','0','$tcsamt','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
	                        mysqli_query($db,$sql) or die("J V Write error 4:".mysqli_error());
		                }
		                
		                
		                $sqldn="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Dr','$cust_name','$netamt', '0','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sqldn) or die("J V Write error 1:".mysqli_error());
    	                $sqldn1="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','IGST Output $taxp%', '0','$tax','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sqldn1) or die("J V Write error 2:".mysqli_error());
    	                $sqldn2="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','INTERSTATE Sale $taxp%(vehicle)','0','$amt2','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
    	                mysqli_query($db,$sqldn2) or die("J V Write error 3:".mysqli_error());
    	                if($tcsper>0)
                        {
	                        $sqldn3="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Tax Collected at Source (TCS) @1%','0','$tcsamt','INTERSTATE Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
	                        mysqli_query($db,$sqldn3) or die("J V Write error 4:".mysqli_error());
		                }
		                
		                
		                
		                
		                
		                

                    }// end if cst
                    else
                    {
    		            $sql="insert into journal2 values('$invdate','$jvno','Dr','$cust_name','$netamt', '0','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sql) or die("J V Write error 1:".mysqli_error());
		                $sqldn="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Dr','$cust_name','$netamt', '0','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sqldn) or die("J V Write error 1:".mysqli_error());
		                if($brcode=='PSN672')
		                {  
    			            $sql="insert into journal2 values('$invdate','$jvno','Cr','Output tax 5', '0','$tax','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sql) or die("J V Write error 2:".mysqli_error());
			                $sqldn1="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Output tax 5', '0','$tax','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sqldn1) or die("J V Write error 2:".mysqli_error());
		                }
		                else
		                {
    			            $cgstamt = $tax /2;
                            $cgstp = $taxp/2;    			
			                $sql="insert into journal2 values('$invdate','$jvno','Cr','CGST Output $cgstp%', '0','$cgstamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sql) or die("J V Write error 2:".mysqli_error());
			                $sql="insert into journal2 values('$invdate','$jvno','Cr','SGST Output $cgstp%', '0','$cgstamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sql) or die("J V Write error 2:".mysqli_error());
			                $sqldn2="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','CGST Output $cgstp%', '0','$cgstamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sqldn2) or die("J V Write error 2:".mysqli_error());
			                $sqldn3="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','SGST Output $cgstp%', '0','$cgstamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sqldn3) or die("J V Write error 2:".mysqli_error());
    		            }
    		            if($cessamt>0)
    		            {
		                    $sql="insert into journal2 values('$invdate','$jvno','Cr','Kerala Flood Cess @ 1%', '0','$cessamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                    mysqli_query($db,$sql) or die("J V Write error cess:".mysqli_error());
		                    $sqldn4="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Kerala Flood Cess @ 1%', '0','$cessamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                    mysqli_query($db,$sqldn4) or die("J V Write error cess:".mysqli_error());
    		            }
		                $sql="insert into journal2 values('$invdate','$jvno','Cr','Sales $taxp%(vehicle)','0','$amt2','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sql) or die("J V Write error 3:".mysqli_error());
		                $sqldn5="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Sales $taxp%(vehicle)','0','$amt2','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sqldn5) or die("J V Write error 3:".mysqli_error());
	                    if($tcsper>0)
                        {
    			            $sql="insert into journal2 values('$invdate','$jvno','Cr','Tax Collected at Source (TCS) @1%','0','$tcsamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sql) or die("J V Write error 3:".mysqli_error());
			                $sqldn6="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Tax Collected at Source (TCS) @1%','0','$tcsamt','Sales INV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
			                mysqli_query($db,$sqldn6) or die("J V Write error 3:".mysqli_error());
        	            }
	                }
	                $jvd=mysqli_query($db,"select sum(dramt)-sum(cramt) from journal2 where jvno='$jvno' and brcode='$brcode'");
	                $difrow=mysqli_fetch_array($jvd);
	                $dif=$difrow[0];
	                $x=abs($dif);
	                if($dif<0)
	                {
    		            $sql="insert into journal2 values('$invdate','$jvno','Dr','Round Off','$x', '0','Round ofSINV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sql) or die("J V Write error dif1:".mysqli_error());
		                $sqldn7="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Dr','Round Off','$x', '0','Round ofSINV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sqldn7) or die("J V Write error dif1:".mysqli_error());
	                }
	                else if($dif>0)
	                {
    		            $sql="insert into journal2 values('$invdate','$jvno','Cr','Round Off','0','$x','Round of adjusted SINV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sql) or die("J V Write error dif:".mysqli_error());
		                $sqldn8="insert into psnindia_psndbdn.journal2 values('$invdate','$jvnodn','Cr','Round Off','0','$x','Round of adjusted SINV No: $invno','$brcode','V sale','DA','$chasis','$invno')";
		                mysqli_query($db,$sqldn8) or die("J V Write error dif:".mysqli_error());
	                }
                    
                    print "<font color='blue'>Inserted Sucessfully -----".$invno."<br></font>";
                    //---------------
                }
            }
      
        }
        $i++;
    }
    print "OK DOne";
    print "<script> alert('Successfully Uploaded....!!!');</script>";
    unlink($filepathval);
}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<style>
body,td{font-family:'calibri';letter-spacing:1;}
tr{background-color:rgb(95, 197, 187);}
.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 Vehicle Invoiecs</h1>
<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>
 </td></tr>
</table>
</td></tr>
</table>
</form>

