<?php
 include('function.php');
 include('db_con.php');
 $count = 0;
 $gtot_qty = 0;
 $gtot_ctn = 0;
 $gtot_amount  = 0;
 $grand_total = 0;
$dated = date('d-m-Y h:i:s a');
 
  $fdate = $_GET['fdate'];
  $tdate = $_GET['tdate'];
  
$cust_id = $_GET['cust_id'];
$emp_id = $_GET['emp_id'];
$sm_id = $_GET['sm_id'];
$sql = "select ifnull(first_name,'ALL') from employees where emp_id = '".$emp_id."'";
$statement = $connect->prepare($sql);
$statement->execute();
$emp_name = $statement->fetchColumn();
$sql = "select ifnull(name,'ALL') from customers where cust_id = '".$cust_id."'";
$statement = $connect->prepare($sql);
$statement->execute();
$cust_name = $statement->fetchColumn();
$sql = "select ifnull(sname,'ALL') from sales_man where sm_id = '".$sm_id."'";
$statement = $connect->prepare($sql);
$statement->execute();
$sm_name = $statement->fetchColumn();
$dated= date('d-m-Y');
if($cust_name==''){$cust_name='All - ';}
if($sm_name==''){$sm_name='All - ';}
if($emp_name==''){$emp_name='All - ';}
  $statement = $connect->prepare("select m.sr_id,m.inv_no,m.dated,m.cur_stamp ,c.name,c.phone,m.discount,m.freight,m.gst_pr,m.discount_dsc,m.freight_dsc from sr m , customers c 
    where c.cust_id=m.cust_id and m.emp_id LIKE '%".$emp_id."%' and m.cust_id LIKE '%".$cust_id."%' and c.sm_id LIKE '%".$sm_id."%' and m.inv_type='Credit' and  m.dated between :fdate and :tdate
    order by dated,sr_id");
 $statement->execute(
  array(   
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
$result = $statement->fetchAll();
 ?>
<!DOCTYPE html>
<html>
<head>
  <title>
  
  </title>
  <style>
    * {
      font-family: arial;
      font-size: 12px;
    }
  .rightside {text-align: right;}
  @media print {
  @page { margin: 0; }
  body { margin: 1cm; }
}
  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;
  }
  .boldtext {
    font-weight: bold;
  }
  .report_title {
    font-weight: bold;
    font-family: arial;
    font-size: 14px;
    
    border-bottom: 1px solid gray;
  }
  .updated {
    color:red;
  }
  </style>
</head>
<body>
<table width="100%">
    <tr class="borderline">
<td colspan="4" class="report_title" width="100%">
  Credit Sale Return Report
</td>
</tr>
<tr class="borderline">
  
<td colspan="4" width="100%"><?php echo '<b>Customer : </b>'.$cust_name .' -- <b>Delivered By: </b>' .$emp_name .' -- <b>Sales Man: </b>' .$sm_name;?></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="46%" class="font-bold">Item Name</td>
  <td width="10%" class="font-bold">UOM</td>
  
  <td width="7%" class="rightside font-bold">Ctn</td>
  <td width="10%" class="rightside font-bold">Qty</td>
  
  <td width="10%"class="rightside font-bold">Rate</td>
  <td colspan="2" width="10%"class="rightside font-bold">Discount</td>
  <td width="10%" class="rightside font-bold">Amount</td>
 
  </tr>
  </thead>
  
<tbody>
  
<?php
foreach($result as $row)
 {
$sr_id = $row["sr_id"];
   $count = $count+1;
          $detail_statement = $connect->prepare("select d.item_no,CONCAT(i.item_name,' ',t.type_name,' ',l.color_name,' ',b.brand_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_brand b,item_type t,item_color l, sr_detail d,sr m,item_uom u,
    customers c where m.sr_id=d.sr_id and m.inv_type='Credit' and i.brand_id=b.brand_id and i.color_id=l.color_id and t.type_id=i.type_id and d.item_no=i.item_id and i.uom=u.uom_id and c.cust_id=m.cust_id and
    m.sr_id = '".$sr_id."'");
         $detail_statement->execute(
                   );
         $detail_result = $detail_statement->fetchAll();
 $sql = "SELECT sum(qty*rate) from sr_detail where sr_id = '".$sr_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 sr_detail where sr_id = '".$sr_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 sr_id = '".$sr_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">
<td width="5%">'.$count.'</td>
<td colspan="8" width="95%">ID: '.$row["sr_id"] .' --- Date : '.$row['dated'].' --- Inv No.: '.$row["inv_no"].' --- '.$row["name"].' --- <span class="updated">Date: '.$row["cur_stamp"].'</span></td>
  
</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="39%" class="small-font">'.$detail_row["item_name"].'</td>
<td width="7%" class="small-font">'.$detail_row["uom_name"].'</td>
<td width="7%" class="rightside small-font">'.$detail_row["ctn"].'</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="7%" class="rightside small-font">'.number_format($dis_given, 2).'</td>
   <td width="3%" class="rightside small-font">'.$dis_type.'</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'];
   if($gst_pr > 0){$gst_amount = $tot_amount * $gst_pr / 100;}
   $net_amount = $tot_amount + $gst_amount - $discount + $freight;
   echo '
     <tr>
                       
                    
<td colspan="3" width="44%" class="rightside small-font">Total :</td>
<td width="7%" class="rightside small-font">'.number_format($tot_ctn, 0).'</td>
<td width="8%" class="rightside small-font">'.number_format($tot_qty, 2).'</td>
   <td width="10%" class="rightside small-font"></td>
   <td width="7%" class="rightside small-font"></td>
   <td width="3%" 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="8" width="90%" class="rightside small-font">GST Amount : '.$gst_pr.' (%)</td>
   <td width="10%" class="rightside small-font">'.number_format($gst_amount, 2).'</td>
  
   
    
   </tr>
   ';
 }
 if($discount > 0)
 {
  echo '
    <tr>
                       
                    
   <td colspan="8" width="90%" class="rightside small-font">'.$row["discount_dsc"].'</td>
   <td width="10%" class="rightside small-font">'.number_format($discount, 2).'</td>
  
   
    
   </tr>
   ';
 }
 if($freight > 0)
 {
  echo '
   <tr>
                       
                    
   <td colspan="8" width="90%" class="rightside small-font">'.$row["freight_dsc"].'</td>
   <td width="10%" class="rightside small-font">'.number_format($freight, 2).'</td>
  
   
    
   </tr>
   ';
 }
 echo '
   <tr>
                       
                    
   <td colspan="8" width="90%" class="rightside small-font">Net Amount :</td>
   <td width="10%" class="rightside small-font">'.number_format($net_amount, 2).'</td>
  
   
    
   </tr>
   ';
$grand_total = $grand_total + $net_amount;
$gtot_ctn = $gtot_ctn + $tot_ctn;
$gtot_qty = $gtot_qty + $tot_qty;
 }
echo '<tr>
<td colspan="3" width="51%" class="rightside small-font">Grand Total :</td>
<td width="7%" class="rightside small-font">'.number_format($gtot_ctn, 0).'</td>
<td width="8%" class="rightside small-font">'.number_format($gtot_qty, 2).'</td>
<td width="10%" class="rightside small-font"></td>
<td width="7%" class="rightside small-font"></td>
<td width="3%" class="rightside small-font"></td>
<td width="14%" class="rightside small-font">'.number_format($grand_total, 2).'</td>
</tr>'
?>
</tbody>
  
  
</tfoot>
 
 </table><!--end main table-->
    
  
 </body>
 </html>