<?php 
session_start(); 
date_default_timezone_set("Africa/Kampala");
$myid=$_SESSION['id'];
$id_user=$_SESSION['id'];
$branch=$_SESSION['branch_id'];
// $role=$_SESSION['role'];
$business=$_SESSION['business_id'];
?>
<!DOCTYPE html>
<html lang="en" oncontextmenu="return true">
<head>
  <!-- <link rel="stylesheet" type="text/css" href="../receipts/bootstrap/css/bootstrap.css" /> -->
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<style type="text/css">
  tr,td {
    padding: 3px;
    width: auto;
  }
</style>

<?php 
// error_reporting(0);
ob_start();

// include '../includes/connection.php';
// include('../includes/session.php');
$date=date('Y-m-d');
header("Content-Type:application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=sales-$date.xls");


include '../includes/connection.php';



$sql_businesstype=mysqli_fetch_array(mysqli_query($con,"SELECT type from business where business_id='$business'"));
$business_type=$sql_businesstype['type'];


?>
<title>sales</title>
</head>


<body style="font-family: time new roman; font-size: 12px;">
 <h4 class="page-title">Sales</h4>
    
        
       <table border="2px" style="font-size: 16px;">
                                    
                                        <thead>
                                            <tr>
                                                <th>S/NO</th>
                                                <?php if( $_SESSION['branch_id']=="0"){
                                    ?>
                                                <th>Branch</th>
                                                <?php
                                              }
                                                ?>
                                                <?php if( $business_type==1){
                                    ?>
                                                <th>Sale Type</th>
                                                <?php
                                              }
                                                ?>
                                                    <th width="50%">
                                                    <table border="2px" style="font-size: 20px;">
                                                    <td width="40%">Product</td>
                                                    <td width="20%">Quantity</td>
                                                    <td width="20%">Unit Price</td>
                                                    <td width="20%">Total Price</td>
                                                    
                                                    
                                                </table></th>
                                                <th  width="10%">Staff</th>
                                                <th  width="10%">User</th>
                                                <th  width="10%">Total</th>
                                                <th width="20%">Date</th>
                                                
                                            </tr>
                                        </thead>
                                        <tbody>
        <?php

if (isset($_GET['staff'])) {

$staff=$_GET['staff']; 
}else{
$staff=''; 

}
if (isset($_GET['from'])) {
$from=$_GET['from']; 
}else{
$from=''; 

}
if (isset($_GET['to'])) {
 $to=$_GET['to']; 
}else{
 $to=''; 

}
if (isset($_GET['status'])) {
$status=$_GET['status']; 
}else{
 $status=''; 

}




if($status=="pending"){
    $status_querry="AND total_price>paid";
    $defaultquery="SELECT id, total_price,saletype, receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales WHERE total_price>paid && branch_id='$branch'";
}else if($status=="cleared"){
     $status_querry="AND total_price=paid";
     $defaultquery="SELECT id, total_price,saletype, receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales WHERE total_price=paid && branch_id='$branch'";
}else{
    $defaultquery="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where branch_id='$branch'";
     $status_querry="";
}



if(strlen($staff)>0 && strlen($from)==0 && strlen($to)==0){
  $sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where staff='$staff' && branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;
}
if(strlen($staff)>0 && strlen($from)>0 && strlen($to)==0  ){
  $sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where staff='$staff' AND DATE_FORMAT(date_time, '%Y-%m-%d')>='$from' && branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;
}

if(strlen($staff)>0 && strlen($from)>0 &&strlen($to)>0){

$sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where staff='$staff' AND DATE_FORMAT(date_time, '%Y-%m-%d')>='$from' AND DATE_FORMAT(date_time, '%Y-%m-%d')<='$to' && branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;
}


if( strlen($staff)==0 && strlen($from)>0 && strlen($to)==0){
  $sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where  DATE_FORMAT(date_time, '%Y-%m-%d')>='$from' && branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;
}


if( strlen($staff)==0 && strlen($from)>0 && strlen($to)>0 ){

$sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where DATE_FORMAT(date_time, '%Y-%m-%d')>='$from' AND DATE_FORMAT(date_time, '%Y-%m-%d')<='$to' && branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;

}

if( strlen($staff)==0 && strlen($from)==0 && strlen($to)==0 ){

$sql="SELECT id, total_price,saletype,receipt_no,customer_name, (select count(id) from sales_details where order_id=sales.id)as count_orders, date_time, (select full_name from staff where staff_code=sales.staff)as staffname, (select name from users where id=(select distinct user_id from sales_details where order_id=sales.id limit 1))as usernames from sales where  branch_id='$branch' AND EXISTS (SELECT * from sales_details where sales_details.order_id=sales.id)".$status_querry;
}


$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) > 0) {

$totalprice=0;

                                        $cou=1;
              while($row = mysqli_fetch_assoc($result)) {
        $dpprice=$row['total_price'];
                 
                $iddx=$row['id'];
                       
                       $totalprice=$totalprice+$dpprice;
                    ?>
                                            <tr class="odd gradeX">
                                                <td><?php echo $cou; ?></td>
                                                 <?php if( $business_type==1){
                                                  $saletype=$row['saletype'];
                                                  if ($saletype==1) {
                                                   $sst="Wholesale";
                                                  }else{
                                                    $sst="Retail";
                                                  }
                                                ?>
                                                <td><?php echo $sst; ?></td>
                                                <?php
                                              }
                                                ?>
                                                <td>
                                                    <?php include 'sales_details.php'; ?>
                                                </td>
                                                <td><?php echo $row['staffname']; ?></td>
                                                <td><?php echo $row['usernames']; ?></td>
                                                <td><?php echo $row['total_price']; ?></td>
                                                <td><?php echo $row['date_time']; ?></td>
                                                


                                                
                                                
                                                
                                            </tr>
               <?php 
               $cou++;                             
    }
    ?>
    <tr>
      <td colspan="4" style="text-align: center;"><h4>TOTAL SALES</h4></td>
      <td colspan="2"><h4 style="text-align:left;"> <?php echo number_format($totalprice);?></h4></td>
    </tr>
    <?php
} else {
    echo "0 results".mysqli_error($con);
}
?>
      </tbody>      
        </table>
        



</body>
</html>