<?php
 include('function.php');
 
 include('db_con.php');
 //stock_update($connect);
 
 $html = '';
 
 $count = 0;
 $gtot_qty = 0;
 $gtot_ctn = 0;
 $gtot_amount  = 0;
 $grand_total = 0;
 
  $fdate = $_GET['fdate'];
  $tdate = $_GET['tdate'];
  
$branch_id = 1;
$user_id = $_GET['user_id'];
$dated= date('Y-m-d');
    
$branch_name = 'ALL';
  $statement = $connect->prepare("select m.s_id,m.inv_no,m.dated,LEFT(m.cust_name,20) cust_name,u.user_name,m.cur_stamp,m.contact,m.discount,m.freight,m.gst_pr,m.discount_dsc,m.freight_dsc from sale m,user_detail u  
    where m.user_id=u.user_id and m.inv_type='Cash' and m.dated between :fdate and :tdate and m.branch_id LIKE '%".$branch_id."%' and m.user_id LIKE '%".$user_id."%'
    order by s_id");
 $statement->execute(
  array(
   
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
$result = $statement->fetchAll();
 ?>
<!DOCTYPE html>
<html>
<head>
  <title>
  
  </title>
  <style>
  .rightside {text-align: right;}
 
  table td{
    
    border-bottom: 1px solid gray;
  }
  .head_row {
    font-weight: bold;
  }
  thead td {
    font-weight: bold;
     border-bottom: 1px solid gray;
      border-top: 1px solid gray;
       background-color: :  gray;
  }
  </style>
</head>
<body>
  <table width="100%">
    <tr class="borderline">
<td colspan="4" class="boldtext">
  Cash Sale Report
</td>
    </tr>
    <tr>
      <td width="20%"></td><td width="30%"></td><td width="20%" class="rightside">From :</td><td width="30%" class="rightside"><?php echo $fdate;?></td>
    </tr>
    <tr>
      <td width="20%"></td><td width="30%"></td><td width="20%" class="rightside">To :</td><td width="30%" class="rightside"><?php echo $tdate;?></td>
    </tr>
  </table>
 
  
  <table id="main" width="100%">
  
  <thead>
<tr>
  <td width="5%" class="font-bold">Sr</td>
  
  <td width="56%" class="font-bold">Item Name</td>
  <td width="7%" class="font-bold">UOM</td>
  
  
  <td width="8%" class="rightside font-bold">Qty</td>
  
  <td width="10%"class="rightside font-bold">Rate</td>
  
  <td width="14%" class="rightside font-bold">Amount</td>
 
  </tr>
  </thead>
  
<tbody>
  
<?php
foreach($result as $row)
 {
$s_id = $row["s_id"];
   $count = $count+1;
          $detail_statement = $connect->prepare("select d.item_no,CONCAT(t.type_name,' ',i.item_name) as item_name,u.uom_name,d.ctn,d.qty,d.rate,d.dis_pr,d.dis_rate,0 as amount from  items i,item_type t, sale_detail d,sale m,item_uom u
     where m.s_id=d.s_id and m.inv_type='Cash' and t.type_id=i.type_id and d.item_no=i.item_id and i.uom=u.uom_id and
    m.s_id = '".$s_id."'");
         $detail_statement->execute(
                   );
         $detail_result = $detail_statement->fetchAll();
 $sql = "SELECT sum(qty*rate) from sale_detail where s_id = '".$s_id."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $tot_amount = $statement->fetchColumn();
           $sql = "SELECT sum(dis_rate*qty)  from sale_detail where s_id = '".$s_id."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $tot_dis_amount = $statement->fetchColumn();
$sql = "SELECT sum(qty) from sale_detail where s_id = '".$s_id."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $tot_qty = $statement->fetchColumn();
 
  
  echo '
  
<tr class="head_row">
<b>
<td width="5%">'.$count.'</td>
<td colspan="5" width="95%">S_ID : '.$row['s_id'].'--'.$row["dated"].' , Inv No. '.$row["inv_no"].',  '.$row["cust_name"].' , '.$row["contact"].' --- '.$row['user_name'].' --- '.$row['cur_stamp'].' </td>
  
</b>
</tr>
   ';
   
 
   $sr = 0;
   $tot_qty = 0;
   $tot_ctn = 0;
   $tot_amount = 0;
   $gst_amount = 0;
   $discount = 0;
   $freight  = 0;
   //$tot_amount = 0;
   //$tot_qty = 0;
   //$tot_dis_amount = 0;
                foreach($detail_result as $detail_row)
                  {
                  //  $gst_pr = $detail_row["gst_pr"];
  $qty = $detail_row["qty"];
  $ctn = $detail_row["ctn"];
  $tot_qty = $tot_qty + $qty;
  $tot_ctn = $tot_ctn + $ctn;
  $rate = $detail_row["rate"];
  $dis_rate = $detail_row["dis_rate"];
  $dis_pr = $detail_row["dis_pr"];
  if($dis_rate > 0)
  {
    $dis_item = $qty * $dis_rate; 
    $dis_type = "PP";
    $dis_given = $dis_rate;
  }else{
    $dis_rate = $rate * $dis_pr / 100;
    $dis_item = $qty * $dis_rate;
    $dis_given = $dis_pr;
    $dis_type="%";}
                      $sr = $sr+1;
                    
                      $dis_amount = $detail_row["dis_rate"];
                      $amount = $qty * $rate - $dis_item;
                      $tot_amount = $tot_amount + $amount;
                     
                      //$tot_amount = $tot_amount + $amount;
                     // $tot_qty = $tot_qty + $qty;
                     // $tot_dis_amount = $tot_dis_amount + $dis_amount;
                echo '
                      <tr>
                       
                    <td width="5%" class="small-font">'.$sr.'</td>  
                    <td width="56%" class="small-font">'.$detail_row["item_name"].'</td>
<td width="7%" class="small-font">'.$detail_row["uom_name"].'</td>

<td width="8%" class="rightside small-font">'.number_format($qty, 2).'</td>
   <td width="10%" class="rightside small-font">'.number_format($rate, 2).'</td>

   <td width="14%" class="rightside small-font">'.number_format($amount, 2).'</td>
  
   
    
   </tr>';
   }
   $gst_pr  = $row['gst_pr'];
   $discount = $row['discount'];
   $freight = $row['freight'];
   $net_amount = $tot_amount + $gst_amount - $discount + $freight;
   if($gst_pr > 0){$gst_amount = $tot_amount * $gst_pr / 100;}
   echo '
     <tr>
                       
                    
<td colspan="3" width="68%" class="rightside small-font">Total :</td>

<td width="8%" class="rightside small-font">'.number_format($tot_qty, 2).'</td>
   <td width="10%" class="rightside small-font"></td>
   
   <td width="14%" class="rightside small-font">'.number_format($tot_amount, 2).'</td>
  
   
    
   </tr>
   ';
   if($gst_pr > 0)
   {
    echo '
    <tr>
                       
                    
   <td colspan="5" width="86%" class="rightside small-font">GST Amount : '.$gst_pr.' (%)</td>
   <td width="14%" class="rightside small-font">'.number_format($gst_amount, 2).'</td>
  
   
    
   </tr>
   ';
   }
   if($discount > 0)
   {
    echo '
    <tr>
                       
                    
   <td colspan="5" width="86%" class="rightside small-font">'.$row["discount_dsc"].'</td>
   <td width="14%" class="rightside small-font">'.number_format($discount, 2).'</td>
  
   
    
   </tr>
   ';
  }
  if($freight > 0)
  {
    echo '
   <tr>
                       
                    
   <td colspan="5" width="86%" class="rightside small-font">'.$row["freight_dsc"].'</td>
   <td width="14%" class="rightside small-font">'.number_format($freight, 2).'</td>
  
   
    
   </tr>
   ';
 }
 echo '
   <tr>
                       
                    
   <td colspan="5" width="86%" class="rightside small-font">Net Amount :</td>
   <td width="14%" class="rightside small-font">'.number_format($net_amount, 2).'</td>
  
   
    
   </tr>
   ';
$grand_total = $grand_total + $net_amount;
 }
echo '<tr>
<td colspan="5" width="86%" class="rightside small-font">Grand Total :</td>
<td width="14%" class="rightside small-font">'.number_format($grand_total, 2).'</td>
</tr>'
?>
</tbody>
  
  
</tfoot>
 
 </table><!--end main table-->
    
  
 </body>
 </html>