<?
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='';
  $invno_arr2[]='';
  $invno_prv2='';

    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 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'";
        //print $query;
        $rs=mysqli_query($db,$query);
        if(mysqli_num_rows($rs)>0)
        {
            $invno_arr[$ctr++]=$invno1;
        }
        else
        {
          $query="select invno from salesretnew 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]=='Dealer Invoice no')
          $invno_no = $x;
         if($spline[$x]=='Invoice Type Desc.')
          $invtype_no = $x;
         if($spline[$x]=='Invoice date')
          $invdate_no = $x;
         if($spline[$x]=='SO No.')
          $so_no = $x;
            if($spline[$x]=='Customer Code')
            $vendor_no = $x;
         if($spline[$x]=='Customer Name')
          $cname_no = $x;
         if($spline[$x]=='Material')
          $itemno_no = $x;
         if($spline[$x]=='Material Desc.')
          $itemdesc_no = $x;
         if($spline[$x]=='Delivered Qty')
          $qty_no = $x;
         if($spline[$x]=='List price Per Unit')
          $lstprice_no = $x;
         if($spline[$x]=='Spares Discount')
          $disc_no = $x;
         if($spline[$x]=='Total taxable')
          $tottax_no = $x;
         if($spline[$x]=='Handling charges')
          $hnd_no = $x;
         if($spline[$x]=='VAT Rate')
          $tax_no = $x;
         if($spline[$x]=='VAT Amount')
          $vat_no = $x;
          if(trim($spline[$x])=='Grand Total')
          $total_no = $x;
       
       if($spline[$x]=='Customer GST No')
            $gst_no = $x;
       
          if($spline[$x]=='Material Type')
          $itemtype_no = $x;
          if($spline[$x]=='Dealer Plant')
          $dlco_no = $x;
         if($spline[$x]=='Invoice cancelled')
          $invcan_no = $x;
          if($spline[$x]=='Other charges')
          $oth_no = $x;
          
          if($spline[$x]=='Integrated GST Rate')
          $igst_rate_no = $x;
          //print $spline[$x]."--".substr($spline[$x],0,20).strlen($spline[$x])."<br>";
          if(substr($spline[$x],0,20)=='Integrated GST Amoun')
          $igst_amount_no = $x;
          if($spline[$x]=='State GST Rate')
          $sgst_rate_no = $x;
          if($spline[$x]=='State GST Amount')
          $sgst_amount_no = $x;
          if($spline[$x]=='Central GST Rate')
          $cgst_rate_no = $x;
          if($spline[$x]=='Central GST Amount')
          $cgst_amount_no = $x;
          if($spline[$x]=='HSN Code')
          $hsn_no = $x;
          
          if($spline[$x]=='SAP Invoice no')
          $sapinv_no = $x;
          
          if($spline[$x]=='Total Tax Amount')
          $tottaxa_no = $x;
          
               }
    }
    if($i>1)
    {
         
          $invno = $spline[$invno_no];
          $invtype = trim($spline[$invtype_no]);
          $invdate = trim($spline[$invdate_no]);
          $invdate = str_replace('/', '-', $invdate);
          $invdate = implode('-', array_reverse(explode('-', $invdate)));
          $sono = trim($spline[$so_no]);
          $cname = trim($spline[$cname_no]);
          $itemno = trim($spline[$itemno_no]);
          $itemdesc = trim($spline[$itemdesc_no]);
          $itemdesc =mysqli_escape_string($db,$itemdesc);
          $qty = trim($spline[$qty_no]);
          $lstprice = trim($spline[$lstprice_no]);
          $tottax = trim($spline[$tottax_no]);
          $disc = trim($spline[$disc_no]);
          $hnd = trim($spline[$hnd_no]);
          $tax = trim($spline[$tax_no]);
          $vat = trim($spline[$vat_no]);
          $tot = trim($spline[$total_no]);
          $itemtype = trim($spline[$itemtype_no]);
          $dlco = trim($spline[$dlco_no]);
          $invcan = trim($spline[$invcan_no]);
          $oth = trim($spline[$oth_no]);
         
         
          $igst_rate = trim($spline[$igst_rate_no]);
          $igst_amount = trim($spline[$igst_amount_no]);
          $sgst_rate = trim($spline[$sgst_rate_no]);
          $sgst_amount = trim($spline[$sgst_amount_no]);
          $cgst_rate = trim($spline[$cgst_rate_no]);
          $cgst_amount = trim($spline[$cgst_amount_no]);
          $hsn = trim($spline[$hsn_no]);
      $sapinv = trim($spline[$sapinv_no]);
      $tottaxa_amount = trim($spline[$tottaxa_no]);
      
          $cname = trim($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 = trim($disc,'"');
          $disc = str_replace(',', '', $disc);
          $hnd = trim($hnd,'"');
          $hnd = str_replace(',', '', $hnd);
          $tax = trim($tax,'"');
          $tax = trim($tax,'%-');
          $tax = str_replace(',', '', $tax);
          $tot = trim($tot,'"');
          $tot = str_replace(',', '', $tot);
          $vat = trim($vat,'"');
          $vat = str_replace(',', '', $vat);
          $itemtype = str_replace(',', '', $itemtype);
          $dlco = str_replace(',', '', $dlco);
          $invcan = trim($invcan, '"');
          $invcan=str_replace("'","", $invcan);
          $oth = trim($oth,'"');
          $oth = str_replace(',', '', $oth);
          $hsn = trim($hsn,'"');
          $igst_rate = trim($igst_rate,'"');
          $igst_rate = trim($igst_rate,'%-');
          $igst_rate = str_replace(',', '', $igst_rate);
          $igst_amount = trim($igst_amount,'"');
          $igst_amount = str_replace(',', '', $igst_amount);
          
          $cgst_rate = trim($cgst_rate,'"');
          $cgst_rate = trim($cgst_rate,'%-');
          $cgst_rate = str_replace(',', '', $cgst_rate);
          $cgst_amount = trim($cgst_amount,'"');
          $cgst_amount = str_replace(',', '', $cgst_amount);
          
          $sgst_rate = trim($sgst_rate,'"');
          $sgst_rate = trim($sgst_rate,'%-');
          $sgst_rate = str_replace(',', '', $sgst_rate);
          $sgst_amount = trim($sgst_amount,'"');
          $sgst_amount = str_replace(',', '', $sgst_amount);
          
          $tottaxa_amount = str_replace(',', '', $tottaxa_amount);
            $tottaxa_amount = trim($tottaxa_amount,'"');
         if($igst_rate<='')
            $igst_rate=0;
          if($igst_amount<='')
            $igst_amount=0;
          if($cgst_rate<='')
            $cgst_rate=0;
          if($cgst_amount<='')
            $cgst_amount=0;
          if($sgst_rate<='')
            $sgst_rate=0;
          if($sgst_amount<='')
            $sgst_amount=0;
            //if($tot<='')
            //$tot=0;
          $vendor_code = trim($spline[$vendor_no]);
          
          $gst_code = trim($spline[$gst_no]);
          
/*if (substr($vendor_code,-4)=='3776')
    $cname = $cname.', COCHIN';
if (substr($vendor_code,-4)=='3793')
    $cname = $cname.', PAVANGAD';
if (substr($vendor_code,-4)=='3774')
    $cname = $cname.', KANHANGAD';
if (substr($vendor_code,-4)=='3771')
    $cname = $cname.', KANNUR';
if (substr($vendor_code,-4)=='3770')
    $cname = $cname.', MALAPARAMBA';
if (substr($vendor_code,-4)=='3772')
    $cname = $cname.', MANJERY';
if (substr($vendor_code,-4)=='3773')
    $cname = $cname.', S.BATHERY';
if (substr($vendor_code,-4)=='3775')
    $cname = $cname.', VENGALAM';
if (substr($vendor_code,-4)=='3777')
    $cname = $cname.', EDAYAR';
if (substr($vendor_code,-4)=='3781')
    $cname = $cname.', MUKKAM';
if (substr($vendor_code,-4)=='3779')
    $cname = $cname.', KUTTIYADY';
if (substr($vendor_code,-4)=='3780')
    $cname = $cname.', KUTTIPURAM';
       $cname=$cname." "."(".$vendor_code.")";  */
       
       if(strlen($dlco)<4)
            $dlco=substr($invno,0,4);
       $pq="select brman from branch where brman='$dlco' and brcode='$brcode'";
       //print $pq;
       if($dlco!='')
       {
        $query=mysqli_query($db,"select brman from branch where brman='$dlco' and brcode='$brcode'");
        if(mysqli_num_rows($query)<=0 )
        {
            print "<script>alert('You Are Selected a Wrong Branch...!!!');</script>";
            return;
        }
       }
       //*****************************
       print $invno_no."invno <br>";
      print $invtype_no."invtype<br>";print $invdate_no." invdate<br>";print $cname_no."cname <br>";
      print $itemno_no."itemno <br>";
      print $itemdesc_no." itemdes <br>";
      print $qty_no."qty <br>";print $lstprice_no."list <br>";print $tottax_no."tot <br>";print $igst_rate_no."igst <br>";print $igst_amount_no."igsta <br>";print $sgst_amount_no."ststa <br>";print $cgst_amount_no."cgsta <br>";print $cgst_rate_no."cgstp <br>";print $sgst_rate_no."sgstp <br>";
      //*************************************************************
        if($invno_no<='' || $invtype_no<='' || $invdate_no<='' || $cname_no<='' || $itemno_no<='' || $itemdesc_no<='' || $qty_no<='' || $lstprice_no<='' || $tottax_no<=''||$vendor_no<='')
        
        {
            print "<script>alert('You Are Selected a Wrong  File 2...!!!');</script>";
            return;
        }
       
      $k=$i-1;
      //echo "<br>";
      //echo $invdate."test";
     // echo "<br>";
      //echo $acf."test";
     // echo "<br>";
      //echo $act."test";
      //echo "<br>";
      if(strlen($invno)>0    && $invcan!='YES' && ($invtype!='Cancel.Inv.spares-S1' && $invtype!='Spares Credit Memo' && $invtype!='Cancl.Inv.service-S1') && $invdate>=$acf && $invdate<=$act && $cname!='PSN Automotive Marketing Pvt Ltd')
      {
          //print "hello";
         /* if($tax==''||$tax=='0')
          {
              $tax='14.50';
              $vat=$tottax*$tax/100;
              $tot=$vat+$tottax;
          }*/
          $hnd=$hnd+$oth;
          $fcessa=0;
           $tcsa=0;
          if(array_search($invno,$invno_arr)>0)
           {
               print "<font color='red'>Already Inserted.....!!!--".$invno."<br></font>";
           }
           else
           {
                            if($itemtype=='ZLUB')
                            {
                                $mitemtype='Lubricant';
                            }
                            else
                            {
                                $mitemtype='Spares';
                            }
                            
                            if($gst_code!='')
                            {
                             $tcsa=$tottaxa_amount-($cgst_amount+$sgst_amount+$igst_amount);   
                            }
                            else
                            {
                            $fcessa=$tottaxa_amount-($cgst_amount+$sgst_amount+$igst_amount);
                            }
                            if($fcessa=='')
                            {
                                $fcessa=0;
                            }
                            if($tcsa=='')
                            {
                                $tcsa=0;
                            }
                            $itemdesc=remove_special($itemdesc);
        $sql="insert into spareinvoice (invno,invtype,invdate,entrydate,saleordno,customer,partcode,partname,qty,price,disc,aprice,hand,tot,brcode,itemtype,msno,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,hsn,sapinvno,fcessa,gstno,adltcsamt) 
        values('$invno','$invtype','$invdate','$invdate','$sono','$cname','$itemno','$itemdesc','$qty','$lstprice','$disc','$tottax','$hnd','$tot','$brcode','$mitemtype','$vendor_code','$cgst_rate','$cgst_amount','$sgst_rate','$sgst_amount','$igst_rate','$igst_amount','$hsn','$sapinv','$fcessa','$gst_code','$tcsa')";
         print $sql."<br>";
        mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
         print "<font color='blue'>Successfuly Inserted.....!!!--".$invno."<br></font>";
      
           }
      }
      else
      {
          //print "no";
      }
      if(strlen($invno)>0 && ($invdate>=$acf && $invdate<=$act) && ($invtype=='Spares Credit Memo' ))
      {
          
           /*if($tax=='')
          {
              $tax='14.50';
              $vat=$tottax*$tax/100;
              $tot=$vat+$tottax;
          }*/
          $hnd=$hnd+$oth;
          $fcessa=0;
           $tcsa=0;
          if(array_search($invno,$invno_arr)>0)
           {
               print "<font color='red'>Already Inserted.....!!!--".$invno."<br></font>";
           }
           else
           {
                            if($itemtype=='ZLUB')
                            {
                                $mitemtype='Lubricant';
                            }
                            else
                            {
                                $mitemtype='Spares';
                            }
                            $tottaxa_amount1=$tottaxa_amount*-1;
                            
                            
                            if($gst_code!='')
                            {
                             $tcsa=$tottaxa_amount1-($cgst_amount+$sgst_amount+$igst_amount);   
                            }
                            else
                            {
                            $fcessa=$tottaxa_amount1-($cgst_amount+$sgst_amount+$igst_amount);
                            }
                            if($fcessa=='')
                            {
                                $fcessa=0;
                            }
                            if($tcsa=='')
                            {
                                $tcsa=0;
                            }
                            
                            /*$fcessa=$tottaxa_amount1-($cgst_amount+$sgst_amount+$igst_amount);
                            if($fcessa=='')
                            {
                                $fcessa=0;
                            }
                            */
                            $itemdesc=remove_special($itemdesc);
        $sql="insert into salesretnew (invno,invtype,invdate,entrydate,saleordno,customer,partcode,partname,qty,price,disc,aprice,hand,tot,brcode,itemtype,msno,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,fcessa,gstno,tcsa) 
        values('$invno','$invtype','$invdate','$invdate','$sapinv','$cname','$itemno','$itemdesc','$qty','$lstprice','$disc','$tottax','$hnd','$tot','$brcode','$mitemtype','$vendor_code','$cgst_rate','$cgst_amount','$sgst_rate','$sgst_amount','$igst_rate','$igst_amount','$fcessa','$gst_code','$tcsa')";
        echo $sql;
        print "<font color='blue'>Successfuly Inserted.....!!!--".$invno."<br></font>";
        mysqli_query($db,$sql) or die('Insert Err:'.mysqli_error($db));
        
           }
      }
    }
    $i++;
  }
  print "<script> alert('Successfully Uploaded....!!!');//document.location='upld_spareinvoicegst.php';</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, 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 Spare Invoices GST</h1>
<tr ><td bgcolor='yellow'><font color='red'><blink>PLEASE  DELETE (Customer type,Customer Classificat,Customer Contact,Location,Customer Payment Terms,Dealer Plant Desc,Plant City)
 FIELD FROM EXCEL FILE</blink></font></td></tr>

<tr><td>File <input type=file name='ff'><input type=submit value='Upload' name='upd'></td></tr>
<tr><td>Always download the spare invoices from SAP with below columns in ANY ORDER(Invoice Type Desc.,Invoice date,SO No.,Customer Name,Material,<br>Material Desc.,Delivered Qty,List price Per Unit,Spares Discount,Total taxable,Handling charges,VAT Rate,VAT Amount,Grand Total,Material Type,Dealer Code) <br>(Convert it to TAB TEXT Format and upload)
 </td></tr>
</table>
</td></tr>
</table>
</form>