<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
include '../../../include/conf.php';
$hsn_array= array(
   "30049013_1" => "12",
   "30049013_2" => "5",
   "21069099_3" => "18",
   "30_4" => "5",
   "3004_5" => "12",
);
$htmlTable='';
$cond = "and date(order_date_time) >='".$_POST['from_date']."' and date(order_date_time) <='".$_POST['to_date']."'";
$incr = 0;
$grand_total = 0;
$get_ord = mysqli_query($conn, "SELECT orderid, orderstatus, contry, `state`, userid, dif_name, refid, DATE(order_date_time) as ordtime, offer from tbl_confirmorder where orderstatus in (0,1,5,15,2,3,24,4,25,90) and company_name='Energy Foods' and refid not like '%CO%' and orderid IN(SELECT orderid from tbl_order where totprice!=0) $cond  order by orid asc");
$htmlTable= '<table class="table table-bordered" style="width:100%; font-size:13px">
    <thead>
    <tr>
    <th>S.No</th>
    <th>Invoice No</th>
    <th>Date and Time</th>
    <th>Name</th>
    <th>GST No.</th>
    <th>State</th>
    <th>Country</th>
    <th>Qty</th>
    <th>Tax Value</th>
    <th>HSN</th>
    <th>GST %</th>
    <th>CGST</th>
    <th>SGST</th>
    <th>IGST</th>
    <th>GST</th>
    <th>Amount</th>
    </tr>
    </thead>
    <tbody>';
while($set_ord = mysqli_fetch_assoc($get_ord)){
    // Yoga Videos
    
   $get_yoga = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s=1 and td.orderid='".$set_ord['orderid']."'");
   $get_status = mysqli_query($conn, "SELECT new_status from shop_user where user_id = '".$set_ord['userid']."' and new_status=1");
   $stat_rows = mysqli_num_rows($get_status);
   if($stat_rows > 0){
      $bg_color='yellow';
   }else{
      $bg_color='';
   }
   $yoga_rows = mysqli_num_rows($get_yoga);
   if($yoga_rows > 0){
    $cgst='';
    $sgst = '';
    $igst='';
    $gst = '';
    $tax_amount='';
    $my_tax='';
    $gst_percent = '';

     $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s=1 and td.orderid='".$set_ord['orderid']."'");
     $set_calc = mysqli_fetch_assoc($get_calc);
     if($set_ord['offer'] ==''){
      $ord_offer = 0;
     }else{
      $ord_offer = $set_ord['offer'];
     }
     $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
     $total_amount = $set_calc['total_amount'] - $calc_offer;
     $gst_rate = 18; // GST percentage
     if($set_ord['contry'] == 'India'){
        $base_price =  $total_amount / (1 + $gst_rate / 100);
        $my_tax  =  $total_amount - $base_price;
        $temp_tax_amount = $total_amount - $my_tax;
        $cgst = $my_tax / 2;
        $sgst = $my_tax / 2;
        $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
     }
    
    $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
    $user_row = mysqli_num_rows($get_user);
    if($user_row > 0){
     $set_user = mysqli_fetch_assoc($get_user); 
     $name = $set_user['name'];
     if($set_user['gst_no'] ==''){
         $gst_no ='NOT APPLICABLE';
     }else{
     $gst_no = $set_user['gst_no'];
     }
    }else{
     $name=$set_ord['dif_name'];
    }
    
    if($set_ord['contry'] == 'India'){
     $state = $set_ord['state'];
     $country = $set_ord['contry'];
     $gst_percent = '18';
     if($set_ord['state'] != 'Tamilnadu'){
        $cgst='';
        $sgst = ''; 
        $igst = $total_amount - $base_price;
     }else{
      $igst = "0.00";
     }
    }else{
     $state = $set_ord['contry'];
     $country = $set_ord['contry'];
     $gst_percent = '';
    }
    if($set_ord['orderstatus']==90){
     $color="red";
     $gst_no ='DELETED';
 }else{
     $color='';
 }
 if($set_ord['orderstatus']==90){
    $total_amount =0;
    $cgst='';
    $sgst = '';
    $igst='';
    $gst = '';
    $tax_amount='';
    $my_tax='';
    $gst_percent = ''; 
 }
 $grand_total+=$total_amount;
 $htmlTable.= '
     <tr style="color:'.$color.'; background-color:'.$bg_color.'">
     <td>'.($incr=$incr+1).'</td>
     <td>'.$set_ord['refid'].'</td>
  <td>'.date('d-m-Y', strtotime($set_ord['ordtime'])).'</td>
     <td>'.strtoupper($name).'</td>
     <td>'.$gst_no.'</td>
     <td>'.$state.'</td>
     <td>'.$country.'</td>
     <td>'.$stat_rows.'</td>
     <td>'.$tax_amount.'</td>
     <td>999723</td>
     <td>'.$gst_percent.'</td>
     <td>'.number_format((float)$cgst, 2, '.', '').'</td>
     <td>'.number_format((float)$sgst, 2, '.', '').'</td>
     <td>'.number_format((float)$igst, 2, '.', '').'</td>
     <td>'.number_format((float)$my_tax, 2, '.', '').'</td>
     <td>'.$total_amount.'</td>
     </tr>';
   
   }
    // Book 

    $get_book = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name='T.I. Book - New' and td.orderid='".$set_ord['orderid']."'");
   $book_rows = mysqli_num_rows($get_book);
   if($book_rows > 0){
    $cgst='';
    $sgst = '';
    $gst = '';
    $igst='';
    $tax_amount='';
    $my_tax='';
    $gst_percent='';
     $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name='T.I. Book - New' and td.orderid='".$set_ord['orderid']."'");
     $set_calc = mysqli_fetch_assoc($get_calc);
     if($set_ord['offer'] ==''){
      $ord_offer = 0;
     }else{
      $ord_offer = $set_ord['offer'];
     }
     $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
     $total_amount = $set_calc['total_amount'] - $calc_offer;
  
     $gst_rate = 5;
     if($set_ord['contry'] == 'India'){
        $base_price =  $total_amount / (1 + $gst_rate / 100);
        $my_tax  =  $total_amount - $base_price;
        $temp_tax_amount = $total_amount - $my_tax;
        $cgst = $my_tax / 2;
        $sgst = $my_tax / 2;
        $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
     }
    
    $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
    $user_row = mysqli_num_rows($get_user);
    if($user_row > 0){
     $set_user = mysqli_fetch_assoc($get_user); 
     $name = $set_user['name'];
     if($set_user['gst_no'] ==''){
         $gst_no ='NOT APPLICABLE';
     }else{
     $gst_no = $set_user['gst_no'];
     }
    }else{
     $name=$set_ord['dif_name'];
    }
    
    if($set_ord['contry'] == 'India'){
     $state = $set_ord['state'];
     $country = $set_ord['contry'];
     $gst_percent = '5';
     if($set_ord['state'] != 'Tamilnadu'){
        $cgst='';
        $sgst = ''; 
        $igst = $total_amount - $base_price;
     }else{
      $igst = "0.00";
     }
    }else{
     $state = $set_ord['contry'];
     $country = $set_ord['contry'];
     $gst_percent = '';
    }
    if($set_ord['orderstatus']==90){
     $color="red";
     $gst_no ='DELETED';
 }else{
     $color='';
 }
 if($set_ord['orderstatus']==90){
    $total_amount =0;
    $cgst='';
    $sgst = '';
    $gst = '';
    $igst='';
    $tax_amount='';
    $my_tax='';
    $gst_percent = '';
 }
 $grand_total+=$total_amount;
 $htmlTable.= '
     <tr style="color:'.$color.'; background-color:'.$bg_color.'">
     <td>'.($incr=$incr+1).'</td>
     <td>'.$set_ord['refid'].'</td>
   <td>'.date('d-m-Y', strtotime($set_ord['ordtime'])).'</td>
     <td>'.strtoupper($name).'</td>
     <td>'.$gst_no.'</td>
     <td>'.$state.'</td>
     <td>'.$country.'</td>
      <td>'.$book_rows.'</td>
     <td>'.$tax_amount.'</td>
     <td>49011010</td>
     <td>'.$gst_percent.'</td>
     <td>'.number_format((float)$cgst, 2, '.', '').'</td>
     <td>'.number_format((float)$sgst, 2, '.', '').'</td>
      <td>'.number_format((float)$igst, 2, '.', '').'</td>
     <td>'.number_format((float)$my_tax, 2, '.', '').'</td>
     <td>'.$total_amount.'</td>
     </tr>';
   
   }
// remaining products

   foreach($hsn_array as $key=>$value){
    $exp = explode('_',$key);
    $get_other = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name!='T.I. Book - New' and sp.v_s not in (1,4) and tc.hsn='$exp[0]' and tc.gst_percent='$value' and td.orderid='".$set_ord['orderid']."'");
    $other_rows = mysqli_num_rows($get_other);
    if($other_rows > 0){
     $cgst='';
     $sgst = '';
     $gst = '';
     $igst='';
     $tax_amount='';
     $my_tax='';
     $gst_percent='';
      $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount, SUM(td.qty) as sum_qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s not in (1,4) and tc.hsn='$exp[0]' and tc.gst_percent='$value' and td.orderid='".$set_ord['orderid']."'");
      $set_calc = mysqli_fetch_assoc($get_calc);
      if($set_ord['offer'] ==''){
         $ord_offer = 0;
        }else{
         $ord_offer = $set_ord['offer'];
        }
        $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
        $total_amount = $set_calc['total_amount'] - $calc_offer;
   
      if($set_ord['contry'] == 'India'){
        $base_price =  $total_amount / (1 + $value / 100);
        $my_tax  =  $total_amount - $base_price;
         $temp_tax_amount = $total_amount - $my_tax;
         $cgst = $my_tax / 2;
         $sgst = $my_tax / 2;
         $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
      }
     
     $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
     $user_row = mysqli_num_rows($get_user);
     if($user_row > 0){
      $set_user = mysqli_fetch_assoc($get_user); 
      $name = $set_user['name'];
      if($set_user['gst_no'] ==''){
          $gst_no ='NOT APPLICABLE';
      }else{
      $gst_no = $set_user['gst_no'];
      }
     }else{
      $name=$set_ord['dif_name'];
     }
     
     if($set_ord['contry'] == 'India'){
      $state = $set_ord['state'];
      $country = $set_ord['contry'];
      $gst_percent = $value;
      if($set_ord['state'] != 'Tamilnadu'){
        $cgst='';
        $sgst = ''; 
        $igst = $total_amount - $base_price;
     }else{
      $igst = "0.00";
     }
     }else{
      $state = $set_ord['contry'];
      $country = $set_ord['contry'];
      $gst_percent = '';
     }
     if($set_ord['orderstatus']==90){
      $color="red";
      $gst_no ='DELETED';
  }else{
      $color='';
  }
  if($set_ord['orderstatus']==90){
    $total_amount =0;
    $cgst='';
    $sgst = '';
    $igst='';   
    $gst = '';
    $my_tax='';
    $tax_amount='';
    $gst_percent = '';
 }
  $grand_total+=$total_amount;
  $htmlTable.= '
      <tr style="color:'.$color.'; background-color:'.$bg_color.'">
      <td>'.($incr=$incr+1).'</td>
      <td>'.$set_ord['refid'].'</td>
      <td>'.date('d-m-Y', strtotime($set_ord['ordtime'])).'</td>
      <td>'.strtoupper($name).'</td>
      <td>'.$gst_no.'</td>
      <td>'.$state.'</td>
      <td>'.$country.'</td>
      <td>'.$set_calc['sum_qty'].'</td>
      <td>'.$tax_amount.'</td>
      <td>'.$exp[0].'</td>
      <td>'.$gst_percent.'</td>
      <td>'.number_format((float)$cgst, 2, '.', '').'</td>
      <td>'.number_format((float)$sgst, 2, '.', '').'</td>
      <td>'.number_format((float)$igst, 2, '.', '').'</td>
      <td>'.number_format((float)$my_tax, 2, '.', '').'</td>
      <td>'.$total_amount.'</td>
      </tr>';
    
    }
   }
}
$htmlTable.=  '<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>Total</td>
<td>'.$grand_total.'</td>
</tr>';
$htmlTable.= '</tbody>
    </table>';
   //  table ends here
    $i=0;
    $incr1 = 2;
    $grand_total=0;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'S.No');
$sheet->setCellValue('B1', 'Invoice No');
$sheet->setCellValue('C1', 'Date and Time');
$sheet->setCellValue('D1', 'Name');
$sheet->setCellValue('E1', 'GST No.');
$sheet->setCellValue('F1', 'State');
$sheet->setCellValue('G1', 'Country');
$sheet->setCellValue('H1', 'Qty');
$sheet->setCellValue('I1', 'Tax Value');
$sheet->setCellValue('J1', 'HSN');
$sheet->setCellValue('K1', 'GST %');
$sheet->setCellValue('L1', 'CGST');
$sheet->setCellValue('M1', 'SGST');
$sheet->setCellValue('N1', 'IGST');
$sheet->setCellValue('O1', 'GST');
$sheet->setCellValue('P1', 'Amount');
$sheet->getStyle('A1:P1')->getFill()
    ->setFillType(Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFFA500'); // ORANGE
   
    $get_ord = mysqli_query($conn, "SELECT orderid, orderstatus, contry, `state`, userid, dif_name, refid, DATE(order_date_time) as ordtime, offer from tbl_confirmorder where orderstatus in (0,1,5,15,2,3,24,4,25,90) and company_name='Energy Foods' and refid not like '%CO%' and orderid IN(SELECT orderid from tbl_order where totprice!=0) $cond  order by orid asc");
    while($set_ord = mysqli_fetch_assoc($get_ord)){
      
      $get_yoga = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s=1 and td.orderid='".$set_ord['orderid']."'");
   $get_status = mysqli_query($conn, "SELECT new_status from shop_user where user_id = '".$set_ord['userid']."' and new_status=1");
   $stat_rows = mysqli_num_rows($get_status);
  
   $yoga_rows = mysqli_num_rows($get_yoga);
   if($yoga_rows > 0){
      $tag = "A".$incr1.":P".$incr1;
      if($stat_rows > 0){
         $bg_color='FFFFFF00';
         $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_SOLID);
         $sheet->getStyle($tag)->getFill()->getStartColor()->setARGB($bg_color);
        
      }else{
         $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_NONE);
        
      }
    $cgst='';
    $sgst = '';
    $igst='';
    $gst = '';
    $tax_amount='';
    $my_tax='';
    $gst_percent = '';

     $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s=1 and td.orderid='".$set_ord['orderid']."'");
     $set_calc = mysqli_fetch_assoc($get_calc);
     if($set_ord['offer'] ==''){
      $ord_offer = 0;
     }else{
      $ord_offer = $set_ord['offer'];
     }
     $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
     $total_amount = $set_calc['total_amount'] - $calc_offer;
     
     if($set_ord['contry'] == 'India'){
        $base_price =  $total_amount / (1 + 18 / 100);
        $my_tax  =  $total_amount - $base_price;
        $temp_tax_amount = $total_amount - $my_tax;
        $cgst = $my_tax / 2;
        $sgst = $my_tax / 2;
        $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
     }
    
    $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
    $user_row = mysqli_num_rows($get_user);
    if($user_row > 0){
     $set_user = mysqli_fetch_assoc($get_user); 
     $name = $set_user['name'];
     if($set_user['gst_no'] ==''){
         $gst_no ='NOT APPLICABLE';
     }else{
     $gst_no = $set_user['gst_no'];
     }
    }else{
     $name=$set_ord['dif_name'];
    }
    
    if($set_ord['contry'] == 'India'){
     $state = $set_ord['state'];
     $country = $set_ord['contry'];
     $gst_percent = '18';
     if($set_ord['state'] != 'Tamilnadu'){
        $cgst='';
        $sgst = ''; 
        $igst = $total_amount - $base_price;
     }else{
      $igst = "0.00";
     }
    }else{
     $state = $set_ord['contry'];
     $country = $set_ord['contry'];
     $gst_percent = '';
    }
    if($set_ord['orderstatus']==90){
     $color="FFFF0000";
     $gst_no ='DELETED';
     $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color);
 }else{
     $color='FF000000';
     $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color); 
 }
 if($set_ord['orderstatus']==90){
    $total_amount =0;
    $cgst='';
    $sgst = '';
    $gst = '';
    $igst='';
    $tax_amount='';
    $my_tax='';
    $gst_percent = ''; 
 }
 $grand_total+=$total_amount;

      $sheet->setCellValue('A'.$incr1, ($i=$i+1));
      $sheet->setCellValue('B'.$incr1, $set_ord['refid']);
      $sheet->setCellValue('C'.$incr1, date('d-m-Y', strtotime($set_ord['ordtime'])));
      $sheet->setCellValue('D'.$incr1, strtoupper($name));
      $sheet->setCellValue('E'.$incr1, $gst_no);
      $sheet->setCellValue('F'.$incr1, $state);
      $sheet->setCellValue('G'.$incr1, $country);
      $sheet->setCellValue('H'.$incr1, $yoga_rows);
      $sheet->setCellValue('I'.$incr1, $tax_amount);
      $sheet->setCellValue('J'.$incr1, '999723');
      $sheet->setCellValue('K'.$incr1, $gst_percent);
      $sheet->setCellValue('L'.$incr1, number_format((float)$cgst, 2, '.', ''));
      $sheet->setCellValue('M'.$incr1, number_format((float)$sgst, 2, '.', ''));
      $sheet->setCellValue('N'.$incr1, number_format((float)$igst, 2, '.', ''));
      $sheet->setCellValue('O'.$incr1, number_format((float)$my_tax, 2, '.', ''));
      $sheet->setCellValue('P'.$incr1, $total_amount);
      
    
      $incr1++;
   
   }
   $get_book = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name='T.I. Book - New' and td.orderid='".$set_ord['orderid']."'");
   $book_rows = mysqli_num_rows($get_book);
   if($book_rows > 0){
      $tag = "A".$incr1.":P".$incr1;
      if($stat_rows > 0){
         $bg_color='FFFFFF00';
         $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_SOLID);
         $sheet->getStyle($tag)->getFill()->getStartColor()->setARGB($bg_color);
        
      }else{
         $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_NONE);
        
      }
    $cgst='';
    $sgst = '';
    $gst = '';
    $igst='';
    $tax_amount='';
    $my_tax='';
    $gst_percent='';
     $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name='T.I. Book - New' and td.orderid='".$set_ord['orderid']."'");
     $set_calc = mysqli_fetch_assoc($get_calc);
     if($set_ord['offer'] ==''){
      $ord_offer = 0;
     }else{
      $ord_offer = $set_ord['offer'];
     }
     $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
     $total_amount = $set_calc['total_amount'] - $calc_offer;
  
     
     if($set_ord['contry'] == 'India'){
        $base_price =  $total_amount / (1 + 5 / 100);
        $my_tax  =  $total_amount - $base_price;
        $temp_tax_amount = $total_amount - $my_tax;
        $cgst = $my_tax / 2;
        $sgst = $my_tax / 2;
        $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
     }
    
    $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
    $user_row = mysqli_num_rows($get_user);
    if($user_row > 0){
     $set_user = mysqli_fetch_assoc($get_user); 
     $name = $set_user['name'];
     if($set_user['gst_no'] ==''){
         $gst_no ='NOT APPLICABLE';
     }else{
     $gst_no = $set_user['gst_no'];
     }
    }else{
     $name=$set_ord['dif_name'];
    }
    
    if($set_ord['contry'] == 'India'){
     $state = $set_ord['state'];
     $country = $set_ord['contry'];
     $gst_percent = '5';
     if($set_ord['state'] != 'Tamilnadu'){
        $cgst='';
        $sgst = ''; 
        $igst = $total_amount - $base_price;
     }else{
      $igst = "0.00";
     }
    }else{
     $state = $set_ord['contry'];
     $country = $set_ord['contry'];
     $gst_percent = '';
    }
    if($set_ord['orderstatus']==90){
      $color="FFFF0000";
      $gst_no ='DELETED';
      $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color);
  }else{
      $color='FF000000';
      $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color); 
  }
 if($set_ord['orderstatus']==90){
    $total_amount =0;
    $cgst='';
    $sgst = '';
    $gst = '';
    $igst='';
    $tax_amount='';
    $my_tax='';
    $gst_percent = '';
 }
 $grand_total+=$total_amount;
 $sheet->setCellValue('A'.$incr1, ($i=$i+1));
 $sheet->setCellValue('B'.$incr1, $set_ord['refid']);
 $sheet->setCellValue('C'.$incr1, date('d-m-Y', strtotime($set_ord['ordtime'])));
 $sheet->setCellValue('D'.$incr1, strtoupper($name));
 $sheet->setCellValue('E'.$incr1, $gst_no);
 $sheet->setCellValue('F'.$incr1, $state);
 $sheet->setCellValue('G'.$incr1, $country);
 $sheet->setCellValue('H'.$incr1, $book_rows);
 $sheet->setCellValue('I'.$incr1, $tax_amount);
 $sheet->setCellValue('J'.$incr1, '49011010');
 $sheet->setCellValue('K'.$incr1, $gst_percent);
 $sheet->setCellValue('L'.$incr1, number_format((float)$cgst, 2, '.', ''));
 $sheet->setCellValue('M'.$incr1, number_format((float)$sgst, 2, '.', ''));
 $sheet->setCellValue('N'.$incr1, number_format((float)$igst, 2, '.', ''));
 $sheet->setCellValue('O'.$incr1, number_format((float)$my_tax, 2, '.', ''));
 $sheet->setCellValue('P'.$incr1, $total_amount);
 

 $incr1++;
   
   }
   foreach($hsn_array as $key=>$value){
      $exp = explode('_',$key);
      $get_other = mysqli_query($conn,"select td.orderid, sp.name, sp.vstatus, tc.name, tc.gst_percent, tc.hsn, td.price, td.qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.name!='T.I. Book - New' and sp.v_s not in (1,4) and tc.hsn='$exp[0]' and tc.gst_percent='$value' and td.orderid='".$set_ord['orderid']."'");
      $other_rows = mysqli_num_rows($get_other);
      if($other_rows > 0){
         $tag = "A".$incr1.":P".$incr1;
         if($stat_rows > 0){
            $bg_color='FFFFFF00';
            $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_SOLID);
            $sheet->getStyle($tag)->getFill()->getStartColor()->setARGB($bg_color);
           
         }else{
            $sheet->getStyle($tag)->getFill()->setFillType(Fill::FILL_NONE);
           
         }
         
        
       $cgst='';
       $sgst = '';
       $igst='';
       $gst = '';
       $tax_amount='';
       $my_tax='';
       $gst_percent='';
        $get_calc = mysqli_query($conn,"select SUM(td.price * td.qty) as total_amount, SUM(td.qty) as sum_qty from tbl_order td inner join shop_product sp on sp.product_id=td.productid inner join tbl_company tc on tc.id=sp.vstatus where sp.v_s not in (1,4) and tc.hsn='$exp[0]' and tc.gst_percent='$value' and td.orderid='".$set_ord['orderid']."'");
        $set_calc = mysqli_fetch_assoc($get_calc);
        if($set_ord['offer'] ==''){
           $ord_offer = 0;
          }else{
           $ord_offer = $set_ord['offer'];
          }
          $calc_offer = $set_calc['total_amount'] * ($ord_offer/100);
          $total_amount = $set_calc['total_amount'] - $calc_offer;
     
        if($set_ord['contry'] == 'India'){
            $base_price =  $total_amount / (1 + $value / 100);
            $my_tax  =  $total_amount - $base_price;
           $temp_tax_amount = $total_amount - $my_tax;
           $cgst = $my_tax / 2;
           $sgst = $my_tax / 2;
           $tax_amount = number_format((float)$temp_tax_amount, 2, '.', '');
        }
       
       $get_user = mysqli_query($conn, "SELECT `name`,gst_no from shop_user where user_id='".$set_ord['userid']."'");
       $user_row = mysqli_num_rows($get_user);
       if($user_row > 0){
        $set_user = mysqli_fetch_assoc($get_user); 
        $name = $set_user['name'];
        if($set_user['gst_no'] ==''){
            $gst_no ='NOT APPLICABLE';
        }else{
        $gst_no = $set_user['gst_no'];
        }
       }else{
        $name=$set_ord['dif_name'];
       }
       
       if($set_ord['contry'] == 'India'){
        $state = $set_ord['state'];
        $country = $set_ord['contry'];
        $gst_percent = $value;
        if($set_ord['state'] != 'Tamilnadu'){
          $cgst='';
          $sgst = ''; 
          $igst = $total_amount - $base_price;
       }else{
         $igst = "0.00";
       }
       }else{
        $state = $set_ord['contry'];
        $country = $set_ord['contry'];
        $gst_percent = '';
       }
       if($set_ord['orderstatus']==90){
         $color="FFFF0000";
         $gst_no ='DELETED';
         $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color);
     }else{
         $color='FF000000';
         $sheet->getStyle($tag)->getFont()->getColor()->setARGB($color); 
     }
    if($set_ord['orderstatus']==90){
      $total_amount =0;
      $cgst='';
      $sgst = '';
      $gst = '';
      $igst='';
      $my_tax='';
      $tax_amount='';
      $gst_percent = '';
   }
    $grand_total+=$total_amount;
    $sheet->setCellValue('A'.$incr1, ($i=$i+1));
 $sheet->setCellValue('B'.$incr1, $set_ord['refid']);
 $sheet->setCellValue('C'.$incr1, date('d-m-Y', strtotime($set_ord['ordtime'])));
 $sheet->setCellValue('D'.$incr1, strtoupper($name));
 $sheet->setCellValue('E'.$incr1, $gst_no);
 $sheet->setCellValue('F'.$incr1, $state);
 $sheet->setCellValue('G'.$incr1, $country);
 $sheet->setCellValue('H'.$incr1, $set_calc['sum_qty']);
 $sheet->setCellValue('I'.$incr1, $tax_amount);
 $sheet->setCellValue('J'.$incr1, $exp[0]);
 $sheet->setCellValue('K'.$incr1, $gst_percent);
 $sheet->setCellValue('L'.$incr1, number_format((float)$cgst, 2, '.', ''));
 $sheet->setCellValue('M'.$incr1, number_format((float)$sgst, 2, '.', ''));
 $sheet->setCellValue('N'.$incr1, number_format((float)$igst, 2, '.', ''));
 $sheet->setCellValue('O'.$incr1, number_format((float)$my_tax, 2, '.', ''));
 $sheet->setCellValue('P'.$incr1, $total_amount);
 $incr1++;
      }
     }
    }
    $sheet->setCellValue('O'.$incr1, 'Total');
    $sheet->setCellValue('P'.$incr1, $grand_total);
$filename = "enf_reports/enf_report.xlsx";
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
echo $htmlTable;
?>
S.No	Invoice No	Date and Time	Name	GST No.	State	Country	Qty	Tax Value	HSN	GST %	CGST	SGST	IGST	GST	Amount
'.($incr=$incr+1).'	'.$set_ord['refid'].'	'.date('d-m-Y', strtotime($set_ord['ordtime'])).'	'.strtoupper($name).'	'.$gst_no.'	'.$state.'	'.$country.'	'.$stat_rows.'	'.$tax_amount.'	999723	'.$gst_percent.'	'.number_format((float)$cgst, 2, '.', '').'	'.number_format((float)$sgst, 2, '.', '').'	'.number_format((float)$igst, 2, '.', '').'	'.number_format((float)$my_tax, 2, '.', '').'	'.$total_amount.'
'.($incr=$incr+1).'	'.$set_ord['refid'].'	'.date('d-m-Y', strtotime($set_ord['ordtime'])).'	'.strtoupper($name).'	'.$gst_no.'	'.$state.'	'.$country.'	'.$book_rows.'	'.$tax_amount.'	49011010	'.$gst_percent.'	'.number_format((float)$cgst, 2, '.', '').'	'.number_format((float)$sgst, 2, '.', '').'	'.number_format((float)$igst, 2, '.', '').'	'.number_format((float)$my_tax, 2, '.', '').'	'.$total_amount.'
'.($incr=$incr+1).'	'.$set_ord['refid'].'	'.date('d-m-Y', strtotime($set_ord['ordtime'])).'	'.strtoupper($name).'	'.$gst_no.'	'.$state.'	'.$country.'	'.$set_calc['sum_qty'].'	'.$tax_amount.'	'.$exp[0].'	'.$gst_percent.'	'.number_format((float)$cgst, 2, '.', '').'	'.number_format((float)$sgst, 2, '.', '').'	'.number_format((float)$igst, 2, '.', '').'	'.number_format((float)$my_tax, 2, '.', '').'	'.$total_amount.'
														Total	'.$grand_total.'