<?php



//require_once 'pdf.php';

 

 include('db_con.php');

 //$html = '';

 $fdate = $_GET['fdate'];

 

 $tdate = $_GET['tdate'];



 $branch_id = $_GET['branch_id'];

 $sm_id = $_GET['sm_id'];



 $cust_id = $_GET['cust_id'];

 

 $count = 0;



 $tot_amount = 0;

 $tot_discount = 0;

 $tot_cost = 0;

 $tot_profit = 0;



 $sm_name = 'All';



 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();

 }



 $c_name = 'All';



 if($cust_id > 0)

 {

   $sql = "SELECT LEFT(name,25) FROM customers where cust_id ='".$cust_id."'";

                 

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

                          $statement->execute(); // no need to add `$sql` here, you can take that out

                $c_name = $statement->fetchColumn();

 }





 

                



             



 $tot_amount = 0;

 $tot_discount = 0;

 $tot_cost = 0;

 $tot_profit = 0;





  $statement = $connect->prepare("select m.s_id as id,m.inv_no,m.dated,'S' as term,c.name,b.branch_name,m.discount,sum(d.qty*d.rate-d.dis_amount) as amount,

    sum(d.qty*d.cost) as cost from sale m,sale_detail d,customers c,branch b, sales_man s

    where m.s_id=d.s_id and m.branch_id=b.branch_id and m.cust_id=c.cust_id and m.sm_id=s.sm_id and

    b.branch_id LIKE '%".$branch_id."%' and c.cust_id LIKE '%".$cust_id."%' and

    s.sm_id LIKE '%".$sm_id."%' and

     m.dated between :fdate and :tdate group by id,dated,name,branch_name,discount

     UNION ALL

     select m.sr_id as id,m.inv_no,m.dated,'R' as term,c.name,b.branch_name,m.discount * -1,sum(d.qty*d.rate-d.dis_amount) * -1 as amount,

    sum(d.qty*d.cost) * -1 as cost from sr m,sr_detail d,customers c,branch b, sales_man s

    where m.sr_id=d.sr_id and m.branch_id=b.branch_id and m.cust_id=c.cust_id and m.sm_id=s.sm_id and

    b.branch_id LIKE '%".$branch_id."%' and c.cust_id LIKE '%".$cust_id."%' and

    s.sm_id LIKE '%".$sm_id."%' and

     m.dated between :fdate and :tdate

      group by id,dated,name,branch_name,discount order by dated,id");

  



 $statement->execute(

  array(

   

   ':fdate'       =>  $fdate,

   ':tdate'       =>  $tdate

  )

 );

 



             

 $result = $statement->fetchAll(); 

 ?>



 <html>

 <head>

 <style>

    @page { margin:80px 20px; }

    @page {margin-bottom: 40px;}

    #header { position: fixed; left: 0px; top: -60px; right: 0px; height: 60px; text-align: center; }

    #footer { position: fixed; left: 0px; bottom: -20px; right: 0px; height: 50px; background-color: #fff; }

    #content {margin:0;font-size:13px;font-weight:normal;}

    .rightside{text-align:right;font-size:12px;}

    #footer .page:after { content: counter(page); }

    

    #table_header th {

    border-top:1px solid black;

    border-bottom:1px solid black;

  }

  .smallfont{font-size:12px;text-align:right;}

  table .bor {

    border-top : 1px solid black;

  }

  </style>

  </head>

  <body>



  <div id="header">



  <table width="100%">

  <tr>

  <td width="40%">Sale Report</td><td width="10%"></td>

  <td width="5%" class="rightside">From :</td><td width="12%" class="rightside"><?php echo''.$fdate.'';?></td>

  <td width="10%"></td><td width="8%" class="rightside">To :</td><td width="15%" class="rightside"><?php echo ''.$tdate.'';?></td>

  </tr>

  

  </table>

  <table width="100%" id="table_header">



  <tr>

  <th width="2%">#</th>

  <th width="6%">ID</th>

  <th width="4%">InvNo</th>

  <th width="8%">Date</th> 

  <th width="10%">Branch</th> 

  <th width="46%">Customer</th>

  

  <th width="6%" class="rightside">Amount</th>

  <th width="6%" class="rightside">Discount</th>

  <th width="6%" class="rightside">Cost</th>

  <th width="6%" class="rightside">Profit</th>







  </tr>



  </table>



  </div>



  <div id="content">

  

  <table width="100%">

  <?php 



  



foreach($result as $row)

 {

  $amount = $row["amount"];

  $discount = $row["discount"];

  $cost = $row["cost"];

  $profit = $amount - $discount -$cost;



  $count = $count+1;



  $tot_amount = $tot_amount + $amount;



  $tot_discount = $tot_discount + $discount;



  $tot_cost = $tot_cost + $cost;



  $tot_profit = $tot_amount - $tot_discount - $tot_cost;







 

  

    

  

  





  echo '

<tr>

<td width="2%">'.$count.'</td>

  <td width="4%">'.$row["id"].'</td>

  <td width="2%">'.$row["term"].'</td>

   <td width="4%">'.$row["inv_no"].'</td>

<td width="8%">'.$row["dated"].'</td>

<td width="10%">'.$row["branch_name"].'</td>

<td width="46%">'.$row["name"].'</td>



<td width="6%" class="rightside">'.number_format($amount, 2).'</td>

   <td width="6%" class="rightside">'.number_format($discount, 2).'</td>

    <td width="6%" class="rightside">'.number_format($cost, 2).'</td>

    <td width="6%" class="rightside">'.number_format($profit, 2).'</td>

  

   

    

   </tr>



   ';

 }

?>   

  </table>





  </div>



  <div id="footer">

  

  <table width="100%">

  <tr><td></td><td></td><td></td><td></td>

  <td width="76%" class="rightside bor">Total : </td><td width="6%" class="rightside bor"><?php echo''.number_format($tot_amount, 3).'';?></td>

  <td width="6%" class="rightside bor"><?php echo''.number_format($tot_discount, 3).'';?></td>

  <td width="6%" class="rightside bor"><?php echo''.number_format($tot_cost, 3).'';?></td>

  <td width="6%" class="rightside bor"><?php echo''.number_format($tot_profit, 3).'';?></td>

  </tr>

  </table>

    

  </div>



  </body>



  

  </html>

  

    

