<?php
 
 include('db_con.php');
 include('function.php');
//require_once 'pdf.php';
 $html = '';
 
 $sm_id = $_GET['sm_id'];
 $cust_id = $_GET['cust_id'];
 $fdate = $_GET['fdate'];
 $tdate = $_GET['tdate'];
 
 //$store_id = $_Get['fitem_no'];
// $store_id = $_Get['titem_no'];
 
$sm_name = 'ALL';
$cust_name = 'ALL';
$tot_qty = 0;
$tot_amount = 0;
$tot_discount = 0;
$tot_freight = 0;
$tot_ramount = 0;
$gtot_qty = 0;
$gtot_amount = 0;
$tot_gst_amount = 0;
$tot_rgst_amount = 0;
$tot_rqty = 0;
if($sm_id > 0)
{
 
 $sql = "SELECT sname FROM sales_man where sm_id ='".$sm_id."'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           $sm_name = $statement->fetchColumn();
         
}else if($sm_id==0){$sm_name='Cash Sale';}else if($sm_id==''){$sm_name='ALL';}
if($cust_id > 0)
{
 
 $sql = "SELECT name FROM customers where cust_id ='".$cust_id."'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           $cust_name = $statement->fetchColumn();
         
}

$sql = "select ifnull(sum(m.discount),0) from  sale m, customers r where  m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and m.dated between '".$fdate."' and '".$tdate."'";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
 $discount = $statement->fetchColumn();
 $sql = "select ifnull(sum(m.freight),0) from  sale m, customers r where  m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and m.dated between '".$fdate."' and '".$tdate."'";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
 $freight = $statement->fetchColumn();

 
  $sql = "select z.item_name,z.qty,z.rate,z.tamount,z.amount, tamount - amount gst_amount,z.uom_name from(
  select CONCAT(i.item_name, ' ',t.type_name,' ', b.brand_name,' ',c.color_name) item_name,d.qty,0 rate,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty) + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 as tamount,
  sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty) amount,u.uom_name from sale_detail d, sale m, customers r,items i,item_color c,item_brand b,item_type t,item_uom u where m.s_id=d.s_id and m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and i.type_id=t.type_id and i.color_id=c.color_id and b.brand_id=i.brand_id and u.uom_id=i.uom and  m.dated between '".$fdate."' and '".$tdate."' and
  i.item_id=d.item_no group by item_id order by d.s_id)z";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
  
 
 $result = $statement->fetchAll();
 
  ?>
<!DOCTYPE html>
<html>
<head>
  <title>Sales Summery</title>
  <style>
    * {
      font-family: arial;
    }
.rightside{text-align: right;}
table td,th{
border-bottom: 1px solid black;
}
</style>
</head>
<body>
<table width="100%">
<tr>
<td>Average Sale Summary</td>
<td>From : <?php echo $fdate;?></td><td>To : <?php echo $tdate;?></td>
</tr>
<tr>
<td>Sales Man :</td><td><?php echo $sm_name;?></td><td>Customer : <?php echo $cust_name;?></td>
</tr>
</table>
  <table width="100%" class="items">
  <tr>
  
  <th width="55%">Item Name</th>
  
<th width="10%">UOM</th>
  <th width="10%" class="rightside">Qty</th>
  <th width="10%" class="rightside">Rate</th>
<th width="15%" class="rightside">Amount</th>
  </tr>
<?php
foreach($result as $row)
 {
  
  $qty = $row["qty"];
  
  $amount = $row["amount"];
  $gst_amount = $row["gst_amount"];
  

  
  $rate = $amount / $qty;

$tot_gst_amount = $tot_gst_amount + $gst_amount;

echo '
  
  
  
<tr>
  
<td width="55%">'.$row["item_name"].'</td>
<td width="10%">'.$row["uom_name"].'</td>
<td width="10%" class="rightside">'.number_format($qty, 2).'</td>
<td width="10%" class="rightside">'.number_format($rate, 2).'</td>
   <td width="15%" class="rightside">'.number_format($amount, 2).'</td>
  
   
    
   </tr>
  ';
   
   $tot_qty = $tot_qty + $qty;
   $tot_amount = $tot_amount + $amount;
  }
echo '
  <tr>
  <td width="55" class="rightside"><b>Discount : </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$discount.'</b></td>
</tr>
<tr>
  <td width="55" class="rightside"><b>Freight : </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$freight.'</b></td>
</tr>
<tr>
  <td width="55" class="rightside"><b>GST Amount: </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$tot_gst_amount.'</b></td>
</tr>';
$tot_amount = $tot_amount - $discount + $freight + $tot_gst_amount;
$gtot_amount = $tot_amount;
$gtot_qty = $tot_qty;
$tot_amount = number_format($tot_amount, 2);
  $tot_qty = number_format($tot_qty, 2);


echo '
  
<tr>
  <td width="55" class="rightside"><b>Total Sale : </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b>'.$tot_qty.'</b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$tot_amount.'</b></td>
</tr>
  
  
<tr>
  
<td colspan="5" width="100%"></td>
  
   
    
   </tr>
  ';

echo '
  
  
  
<tr>
  
<td colspan="5" width="100%"><b>Sale Return</b></td>
  
   
    
   </tr>
  ';

echo '
  
  
  
<tr>
  
<td colspan="5" width="100%"></td>
  
   
    
   </tr>
  ';
  $sql = "select ifnull(sum(m.discount),0) from  sr m, customers r where  m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and m.dated between '".$fdate."' and '".$tdate."'";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
 $rdiscount = $statement->fetchColumn();
 $sql = "select ifnull(sum(m.freight),0) from  sr m, customers r where  m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and m.dated between '".$fdate."' and '".$tdate."'";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
 $rfreight = $statement->fetchColumn();


 
  $sql = "select z.item_name,z.qty,z.rate,z.tamount,z.amount, tamount - amount gst_amount,z.uom_name from(
  select CONCAT(i.item_name, ' ',t.type_name,' ', b.brand_name,' ',c.color_name) item_name,d.qty,0 rate,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty) + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 as tamount,
  sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty) amount,u.uom_name from sr_detail d, sr m, customers r,items i,item_color c,item_brand b,item_type t,item_uom u where m.sr_id=d.sr_id and m.cust_id=r.cust_id and r.sm_id LIKE '%".$sm_id."%' and i.type_id=t.type_id and i.color_id=c.color_id and b.brand_id=i.brand_id and u.uom_id=i.uom and  m.dated between '".$fdate."' and '".$tdate."' and
  i.item_id=d.item_no group by item_id order by d.sr_id)z";
  

   $statement = $connect->prepare($sql);
 $statement->execute();
  
 
 $result = $statement->fetchAll();

 foreach($result as $row)
 {
  
  $rqty = $row["qty"];
  
  $ramount = $row["amount"];
  
$rgst_amount = $row["gst_amount"];
  
  $rrate = $ramount / $rqty;

$tot_rgst_amount = $tot_rgst_amount + $rgst_amount;

echo '
  
  
  
<tr>
  
<td width="55%">'.$row["item_name"].'</td>
<td width="10%">'.$row["uom_name"].'</td>
<td width="10%" class="rightside">'.number_format($rqty, 2).'</td>
<td width="10%" class="rightside">'.number_format($rrate, 2).'</td>
   <td width="15%" class="rightside">'.number_format($ramount, 2).'</td>
  
   
    
   </tr>
  ';
   
   $tot_rqty = $tot_rqty + $rqty;
   $tot_ramount = $tot_ramount + $ramount + $tot_rgst_amount;
  }
  
  
  $gtot_qty = $gtot_qty -$tot_rqty;
  $gtot_amount = $gtot_amount - $tot_ramount;
$tot_ramount = number_format($tot_ramount, 2);
  $tot_rqty = number_format($tot_rqty, 2);
  $gtot_amount = number_format($gtot_amount, 2);
  $gtot_qty = number_format($gtot_qty, 2);


echo '
  

<tr>
  <td width="55" class="rightside"><b>Discount : </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$rdiscount.'</b></td>
</tr>
<tr>
  <td width="55" class="rightside"><b>Freight : </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$rfreight.'</b></td>
</tr>

<tr>
  <td width="55" class="rightside"><b>GST Amount: </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b></b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$tot_rgst_amount.'</b></td>
</tr>
<tr>
  <td width="55" class="rightside"><b>Total Sale Return: </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b>'.$tot_rqty.'</b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$tot_ramount.'</b></td>
</tr>
<tr>
  <td width="55" class="rightside"><b>Net Total: </b></td>
  <td width="10%"></td>
  <td width="10%" class="rightside"><b>'.$gtot_qty.'</b></td>
  <td width="10%"></td>
  <td width="15%" class="rightside"><b>'.$gtot_amount.'</b></td>
</tr>';
  
  
  ?>
</body>
</html>