<?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'];
  
$acc_no = $_GET['acc_no'];

$sm_id = $_GET['sm_id'];
$inv_type = $_GET['inv_type'];
if($acc_no !=='')
{
 $sql = "select ifnull(acc_name,'ALL') from accounts where acc_no = '".$acc_no."'";
$statement = $connect->prepare($sql);
$statement->execute();
$acc_name = $statement->fetchColumn(); 
}else {$acc_name = "ALL";}

if($sm_id > 0)
{
  $sql = "select sname from sales_man where sm_id = '".$sm_id."'";
  $statement=$connect->query($sql);
  $sname = $statement->fetchColumn();
}else{$sname = "ALL";}
$dated= date('d-m-Y');

if($acc_no > 0)
{
  $sql = "select m.s_id, m.dated, concat(t.type_name, ' ' , i.item_name) item_name ,d.qty,d.rate,d.cost, a.acc_name from sale m, sale_detail d, accounts a, items i, item_type t where m.s_id=d.s_id and t.type_id=i.type_id and d.item_no=i.item_id and m.cust_id=a.acc_no and m.inv_type LIKE '%".$inv_type."%' and m.cust_id = '".$acc_no."' and m.dated between '".$fdate."' and '".$tdate."' order by m.s_id asc";
}elseif($sm_id > 0)
{
  $sql = "select m.s_id, m.dated, concat(t.type_name, ' ' , i.item_name) item_name , d.qty,d.rate,d.cost, a.acc_name from sale m, sale_detail d, accounts a , items i, item_type t where m.s_id=d.s_id and t.type_id=i.type_id and d.item_no=i.item_id and m.cust_id=a.acc_no and m.inv_type LIKE '%".$inv_type."%' and  and a.sm_id='".$sm_id."' and m.dated between '".$fdate."' and '".$tdate."' order by m.s_id asc" ;
}else{
 $sql = "select m.s_id, m.dated, concat(t.type_name, ' ' , i.item_name) item_name , d.qty,d.rate,d.cost, a.acc_name from sale m, sale_detail d, accounts a, items i, item_type t where m.s_id=d.s_id and t.type_id=i.type_id and d.item_no=i.item_id and m.cust_id=a.acc_no and m.inv_type LIKE '%".$inv_type."%'  and m.dated between '".$fdate."' and '".$tdate."' order by m.s_id asc"; 
}

$statement = $connect->query($sql);


$result = $statement->fetchAll();
 ?>
<!DOCTYPE html>
<html>
<head>
  <title>
  Sales with Profit Report
  </title>
  <style>
    * {
      font-family: arial;
    
    }
  .rightside {text-align: right;}
  @media print {
  @page { margin: 0;
  size: landscape; }
  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: 12px;
    
    border-bottom: 1px solid gray;
  }
  .updated {
    color:red;
  }
  table.data_table {
    font-size: 10px;
    font-family: calibri;
  }
  table.report_header {
    font-size: 12px;
  }
  .total {
    font-weight: bold;
    font-size: 11px;
  }
  </style>
</head>
<body>
<table class="report_header" width="100%">
    <tr class="borderline">
<td colspan="4" class="report_title" width="100%">
  <?php echo $inv_type;?> Sales Profit Report
</td>
</tr>
<tr class="borderline">
  
<td colspan="4" width="100%"><?php echo '<b>Customer : </b>'.$acc_name .' | <b>Sales Man: </b>' .$sname;?></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 class="data_table" width="100%">
  
  <thead>
<tr>
  <td width="3%" class="font-bold">Sr</td>
  <td width="5%" class="font-bold">SID</td>
  <td width="10%" class="font-bold">Date</td>
  <td width="22%" class="font-bold">To | Customer</td>
  <td width="17%" class="font-bold">Item </td>
  <td width="4%" class="rightside font-bold">Qty</td> 
  <td width="6%" class="rightside font-bold">Rate</td>
  <td width="7%" class="rightside font-bold">Amount</td>
  <td width="6%" class="rightside font-bold">Cost</td>
  <td width="7%" class="rightside font-bold">CostAmt</td>
  <td width="6%" class="rightside font-bold">Profit</td>
  <td width="7%" class="rightside font-bold">ProfitAmt</td>
 
  </tr>
  </thead>
  
<tbody>
  
<?php
$sr = 0;
$tot_amount = 0;
$tot_cost = 0;
$tot_profit = 0;
$tot_qty = 0;
foreach($result as $row)
 {
  $sr++;
  $qty = $row["qty"];
  $rate = $row["rate"];
  $amount = $qty * $rate;
  $cost = $row["cost"];
  $costamt = $qty * $cost;
  $profit = $rate - $cost;
  $tot_qty = $tot_qty + $qty;
  $profitamt = $profit * $qty;

  $tot_amount = $tot_amount + $amount;
  $tot_cost = $tot_cost + $costamt;
  $tot_profit = $tot_profit + $profitamt;
  echo '

  <tr>
  <td>'.$sr.'</td>
  <td>'.$row["s_id"].'</td>
  <td>'.$row["dated"].'</td>
  <td>'.$row["acc_name"].'</td>
  <td>'.$row["item_name"].'</td>
  <td class="rightside">'.number_format($qty,0).'</td>
  <td class="rightside">'.number_format($rate,0).'</td>
  <td class="rightside">'.number_format($amount,0).'</td>
  <td class="rightside">'.number_format($cost,0).'</td>
  <td class="rightside">'.number_format($costamt,0).'</td>
  <td class="rightside">'.number_format($profit,0).'</td>
  <td class="rightside">'.number_format($profitamt,0).'</td>
  </tr>
  ';
 }
 echo '

  <tr class="total">
  <td colspan="5" class="rightside">Total: </td>
  
  <td class="rightside">'.number_format($tot_qty,0).'</td>
  <td class="rightside"></td>
  <td class="rightside">'.number_format($tot_amount,0).'</td>
  <td class="rightside"></td>
  <td class="rightside">'.number_format($tot_cost,0).'</td>
  <td class="rightside"></td>
  <td class="rightside">'.number_format($tot_profit,0).'</td>
  </tr>
  ';
?>
</tbody>
  
 </table><!--end main table-->
    
  
 </body>
 </html>