<?
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");

$acpd=mysqli_query($db,"select * from acperiod") or die ('err'.mysqli_error($db));
     $acrow=mysqli_fetch_row($acpd);

      $acf= $acrow[0];
      $act= $acrow[1];
      $fag10=0;
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;
   }
    $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);
    if($j==1)
    {
      $no_col=sizeof($spline);
      for($x=0;$x<=$no_col;$x++)
      { 
           if($spline[$x]=='Dealer Invoice Number')
            $invno_no1 = $x;
      }
    }
    if($j>1)
    {
		 $invno1 = trim($spline[$invno_no1]);
    
    if($invno1!=$invno_prv)
    {
        $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;
        }
    }
        $invno_prv=$invno1;
        
    }$j++;
  }
  
  
  
  //-------------------------------------------------------------
  $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')
          $cname_no = $x;
         if($spline[$x]=='Customer Number')
          $cusno_no = $x;
           //if($spline[$x]=='Customer(Bill to Party)')
          //$cusno_no = $x;
          
         if($spline[$x]=='Item No.')
          $itemno_no = $x;
          if($spline[$x]=='Registration No.')
          $reg_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]=='Mechanic Name')
          $mech_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')
          $intref_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]);
          $cusno = trim($spline[$cusno_no]);
          $regno = trim($spline[$reg_no]);
          
          $itemno = trim($spline[$itemno_no]);
          $itemdesc = trim($spline[$itemdesc_no]);
          $itemdesc =mysqli_escape_string($db,$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]);
         
          $cgstp = trim($spline[$cgstp_no]);
          $sgstp = trim($spline[$sgstp_no]);
          $igstp = trim($spline[$igstp_no]);
        $intrefno = trim($spline[$intref_no]);
         $dlco = trim($spline[$dlco_no]);
         $dlco = str_replace(',', '', $dlco);
          
          $mech= trim($spline[$mech_no]);
          $mech = trim($mech, '"');
          $mech =mysqli_escape_string($db,$mech);
          $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);
          
           $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;             
         
         
         //print $dlco;
         

        $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
        $nn=mysqli_num_rows($query);
        //print $nn;
        
        if(mysqli_num_rows($query)<=0 && $dlco!='')
        {
            print "<script>alert('You Are Selected a Wrong Branch');</script>";
            return;
        }
        else
        {
           //print "<script>alert('You Are Selected a correct File');</script>";
           
        }
        if($jcno_no<='' || $jcdt_no<='' || $invno_no<='' || $cname_no<='' || $itemno_no<='' || $qty_no<=''  || $tottax_no<='' || $total_no<='')
        {
            print "<script>alert('You Are Selected a Wrong  File...!!!');</script>";
            return;
        }
      $k=$i-1;
      
       //print "inv d ".$jcdt."<br>";
      if(strlen($invno)>0 && ($jcdt>=$acf && $jcdt<=$act))
      {
          
           /*print"inv ". $invno."<br>";
        print "inv d ".$jcdt_no."<br>";
        print " part ".$itemno_no."<br>";
        print "part nam ".$itemdesc_no."<br>";
        print "qty".$qty."<br>";
        print "lst ".$lstprice_no."<br>";
        print "ttax ".$tottax_no."<br>";
        print "tax ".$tax_no."<br>";
        print "tot ".$total_no."<br>";
        print "grn ".$grno_no."<br>";*/
        
        //print $b=array_search($invno,$invno_arr);
        $cname=mysqli_escape_string($db,$cname);
        $cust=mysqli_escape_string($db,$cname);
        
        $cname=$cname." "."(".$cusno.")";
        
           if(array_search($invno,$invno_arr)>0)
           {
               print "<font color='red'>Already Inserted.....!!!--".$invno."<br></font>";
           }
           else
           {
                if($qty>0)
            {
               //if($tot>0)
               //{
               if($invno_prv1!=$invno)
               {
                $sql="insert into jobcard (jcno,date,cname,brcode,invtype,invno,entrydate,msno,model,engineer,user,ptype) values('$jcno','$jcdt','".addslashes($cname)."','$brcode','$invtype','$invno','$jcdt','$cusno','$regno','$mech','$cust','$intrefno')";
                mysqli_query($db,$sql) or die('Insert Err0:'.mysqli_error($db));
               }
               
               

$itemdesc=remove_special($itemdesc);
     
     
                    if($itemtype=='Labor Value'||$itemtype=='Outside Labor'||$itemtype=='Misc. Labor'||$itemtype=='Ancillary order W/O'||$itemtype=='Telematics')
                        {
                            
                             $fcessamt=$tot-($grsval+$cgstamt+$sgstamt+$igstamt);
                                if ($fcessamt=='')
                                {
                                    $fcessamt=0;
                                }
                                
                        
                            $sql1="insert into jcardserv (jcno,brcode,service,servamt,descr,tot,invno,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,servtx) values('$jcno','$brcode','$itemdesc','$grsval','$itemdesc','$tot','$invno','$cgstp','$cgstamt','$sgstp','$sgstamt','$igstp','$igstamt','$fcessamt')";
                            $invno_prv1=$invno;
                            print "<font color='blue'>".$i.". ".$sql1."HH".$itemtype."<br></font>";
                        }
                        else
                        {       if($itemtype=='Spares'||$itemtype=='Local Parts' ||$itemtype=='Outside Parts')
                                 {
                                $mitemtype='Spares';
                                 }
                                else
                                {
                                $mitemtype=$itemtype;
                                }
                                /*if($tax==''||$tax=='0')
                                {
                                   $tax=14.50;
                                    $tottax=$grsval*$tax/100;
                                    $tot=$grsval+$tottax;
                                }*/
                                $fcessamt=$tot-($grsval+$cgstamt+$sgstamt+$igstamt);
                                if ($fcessamt=='')
                                {
                                    $fcessamt=0;
                                }
                               
                               $tcsamt=$tot-($grsval+$cgstamt+$sgstamt+$igstamt);
                                if ($tcsamt=='')
                                {
                                    $tcsamt=0;
                                }
                                
                            $sql1="insert into jcardparts (jcno,brcode,partcode,partname,rate,qty,amt,disc,hnd,gtot,invno,date,itemtype,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,cessamt,tcsamt) values('$jcno','$brcode','$itemno','$itemdesc','$lstprice','$qty','$grsval','$disc','$hnd','$tot','$invno','$jcdt','$mitemtype','$cgstp','$cgstamt','$sgstp','$sgstamt','$igstp','$igstamt','$fcessamt','$tcsamt')";
                            $invno_prv1=$invno;
                            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'>".$i.".Jobcard Inserted Successsfuly--".$invno."<br></font>";
               }
           }
           
      }
      else
           {
            $flag10=1;   
           }
    }
    
    $i++;
  }
  //print "<script> alert('Successfully Uploaded....!!!');document.location='upld_jobcard.php';</script>";
  if($flag10==1)
  {
   // print "<script> alert('Some Items are not in Current Financialyear....!!!');</script>";
  
  }
print "<script> alert('Successfully Uploaded....!!!');</script>";

}

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;
     }
?>
<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}
</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  GST</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>