<?php session_start(); ob_start(); ?>

<?php  include 'header.php'; ?>

<?php
ini_set('display_errors', 0);

if(empty($_SESSION['LOGINID']) && empty($_SESSION['LOGINNM']) ){

	header("Location:index.php");
}


include("user.php");

$date1 = $_GET["date1"];
	$date2 = $_GET["date2"];

?>



<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Report</title>
    <!-- ================= Favicon ================== -->
    <!-- Standard -->
    <link rel="shortcut icon" href="http://placehold.it/64.png/000/fff">
    <!-- Retina iPad Touch Icon-->
    <link rel="apple-touch-icon" sizes="144x144" href="http://placehold.it/144.png/000/fff">
    <!-- Retina iPhone Touch Icon-->
    <link rel="apple-touch-icon" sizes="114x114" href="http://placehold.it/114.png/000/fff">
    <!-- Standard iPad Touch Icon-->
    <link rel="apple-touch-icon" sizes="72x72" href="http://placehold.it/72.png/000/fff">
    <!-- Standard iPhone Touch Icon-->
    <link rel="apple-touch-icon" sizes="57x57" href="http://placehold.it/57.png/000/fff">
    <!-- Styles -->
     <link href="assets/css/lib/data-table/buttons.bootstrap.min.css" rel="stylesheet" />
    <link href="assets/css/lib/font-awesome.min.css" rel="stylesheet">
    <link href="assets/css/lib/themify-icons.css" rel="stylesheet">
    <link href="assets/css/lib/owl.carousel.min.css" rel="stylesheet" />
    <link href="assets/css/lib/owl.theme.default.min.css" rel="stylesheet" />
    <link href="assets/css/lib/weather-icons.css" rel="stylesheet" />
    <link href="assets/css/lib/menubar/sidebar.css" rel="stylesheet">
    <link href="assets/css/lib/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/lib/unix.css" rel="stylesheet">
    <link href="assets/css/style.css" rel="stylesheet">
</head>

<body>

   
   
   
     <?php include("sidebar.php"); ?>
	
	
    <!-- /# sidebar -->
     <?php include("top.php"); ?>
	<!-- /# topebar --> 
	
	
	
    <div class="content-wrap">
        <div class="main">
            <div class="container-fluid">
                <div class="row">
                    <div class="col-lg-8 p-r-0 title-margin-right">
                        <div class="page-header">
                            <div class="page-title">
                                <h1>Hello, <span>Welcome Here</span></h1>
                            </div>
                        </div>
                    </div>
                    <!-- /# column -->
                    <div class="col-lg-4 p-l-0 title-margin-left">
                        <div class="page-header">
                            <div class="page-title">
                                <ol class="breadcrumb text-right">
                                    <li><a href="#">Dashboard</a></li>
                                    <li class="active">Home</li>
                                </ol>
                            </div>
                        </div>
                    </div>
                    <!-- /# column -->
                </div>
                <!-- /# row -->
                <section id="main-content">
				
				
			
				
				
				
				
				
				<div class="row">
				
				        <div class="col-lg-12">
						
						<div class="panel panel-info m-t-15">
                                            <div class="panel-heading">Report</div>
                                            <div class="panel-body">
						
								  

								  
								  
								  
								 
								  
								  
								  
								  
								  
								  
								  
								  
								  
                                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card alert">
							
							
								
								
								 <div class="bootstrap-data-table-panel">
                                    <div class="table-responsive">
                                        <table id="bootstrap-data-table-export" class="table table-striped table-bordered">
                                            <thead>
                                                <tr> <th>ID</th>
                                                    <th>Client Name</th>
													
														<th>Phone No</th>
														<th>Total Booking Value</th>
												
													<th>Paid</th>
														<th>Due</th>
                                                </tr>
                                            </thead>
                                            <tbody>
											
								<?php 
											
															$sl=0;
                                   $sqlSelectedLG = "select * from (select booking_id,cell_1,name,ev_total,in_amt,paid,ev_total+in_amt-paid due from (select f1.* ,IFNULL(f4.in_amount,0) in_amt,(IFNULL(f2.ev_value,0)+IFNULL(f3.added_later,0))-((IFNULL(f2.ev_value,0)+IFNULL(f3.added_later,0))*IFNULL(f1.discount,0)/100) ev_total,((IFNULL(f2.ev_value,0)+IFNULL(f3.added_later,0)+IFNULL(f4.in_amount,0))-IFNULL(f1.paid,0)) as due,f2.ev_value,IFNULL(f3.added_later,0) added_later from (SELECT t1.id, t1.booking_id,t1.booking_name,t1.cell_1,t1.cell_2,t1.name,t1.approved_at,t1.added_at,IFNULL(t1.discount,0) discount,t1.status, IFNULL(t2.paid,0) as paid FROM `bookings` as t1 LEFT JOIN (SELECT temp_booking_id, sum(IFNULL(amount,0)) as paid FROM `transactions` GROUP BY temp_booking_id) as t2 on t1.id=t2.temp_booking_id) as f1 left join (SELECT temp_id, count(id) ev_total, sum(ev_value) ev_value FROM `events` where added_later='no' GROUP BY temp_id) as f2 on f1.id=f2.temp_id left join (SELECT temp_id, IFNULL(count(id),0) ev_total_later, sum(ev_value) added_later FROM `events` where added_later='yes' GROUP BY temp_id) as f3 on f1.id=f3.temp_id left join (select t2.customer_id, IFNULL(t1.inv_amount,0)-IFNULL(t2.discount,0) in_amount from (SELECT invoice_id, sum(total) inv_amount FROM `invoice_items` group by invoice_id order by invoice_id desc) as t1 left join invoices as t2 on t1.invoice_id=t2.inv_id left join bookings as t3 on t2.customer_id=t3.id) as f4 on f4.customer_id=f1.id where f1.status!=0 AND (date(approved_at) BETWEEN '$date1' AND '$date2')) as k1 where k1.due!=0) as p1 where p1.due!=0 
union 
select p2.* from (select l1.booking_id,l1.cell_1,l1.name,0 ev_total,IFNULL(l2.inv_amount,0)-IFNULL(l2.discount,0) in_amt,IFNULL(l2.paid,0) paid,IFNULL(l2.inv_amount,0)-IFNULL(l2.discount,0)-IFNULL(l2.paid,0) due from bookings as l1 left join (select t1.*,IFNULL(t4.paid,0) paid,(t1.inv_amount-t2.discount-IFNULL(t4.paid,0) ) as due, t2.id, t2.date, t2.customer_id,t2.notes,t2.discount, t2.added_at, t2.updated_at,t3.booking_id,t3.name c_name from (SELECT invoice_id, sum(total) inv_amount FROM `invoice_items` group by invoice_id order by invoice_id desc) as t1 left join invoices as t2 on t1.invoice_id=t2.inv_id left join bookings as t3 on t2.customer_id=t3.id left join (SELECT inv_id, sum(amount) paid FROM `transactions` where type=2 GROUP by inv_id) as t4 on t1.invoice_id=t4.inv_id where (date(t2.date) BETWEEN '$date1' AND '$date2' )) as l2 on l1.booking_id=l2.booking_id where l1.booking_id LIKE '9%') as p2 where p2.due!=0								";

                               $sqlSelectedLG = mysqli_query($connection, $sqlSelectedLG);

$tdue=0;
$ttotal_charge=0;
$tpaid=0;
                               while ($list = mysqli_fetch_object($sqlSelectedLG)) {

                                 
                                  $idn = $list->booking_id;
								   $cell_1 = $list->cell_1;
									$client_name = $list->name;
									$sub_total = $list->ev_value;
									$added_later = $list->added_later;
									$total_charge1 = $list->ev_total;
									
									$in_amt=$list->in_amt;
									
									
									$total_charge=$total_charge1+$in_amt;
									$paid = $list->paid;
									$tpaid=$tpaid+$paid;
								    
								
									$ev_total = $list->ev_total;  
									
									$due = $list->due;
									
										 $ttotal_charge=$ttotal_charge+$total_charge;	
			                  
								$tdue=$tdue+$due;
								
									
									?>
											
											
											
											
                                                <tr>
                                                      <td>   <?php echo $idn; ?> </td>
                                                    <td> <?php echo $client_name; ?></td>
                                                    <td> <?php echo $cell_1; ?></td>
                                                   
													
													 <td>	 <?php echo $total_charge; ?> </td>
													  <td>	 <?php echo $paid; ?>  </td>
												 <td>	 <?php echo $due; ?>  </td>
													
													
                                                </tr>
                                                
												
<?php } ?>			
												
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
								
								
								
								
								
								
								
                            </div>
                            <!-- /# card -->
                        </div>
                        <!-- /# column -->
                    </div>

                                   


							 </div> </div>
							
                        </div>
				
				
                        <!-- /# column -->
                        
                    </div>
				
				
				
				
				
                    <!-- /# row -->
                     <?php include("footer.php"); ?>
                </section>
            </div>
        </div>
    </div>

    <div id="search">
        <button type="button" class="close">×</button>
        <form>
            <input type="search" value="" placeholder="type keyword(s) here" />
            <button type="submit" class="btn btn-primary">Search</button>
        </form>
    </div>


    </div>
    <script src="assets/js/lib/jquery.min.js"></script>
    <!-- jquery vendor -->
    <script src="assets/js/lib/jquery.nanoscroller.min.js"></script>
    <!-- nano scroller -->
    <script src="assets/js/lib/menubar/sidebar.js"></script>
    <script src="assets/js/lib/preloader/pace.min.js"></script>
    <!-- sidebar -->
    <script src="assets/js/lib/bootstrap.min.js"></script>
    <!-- bootstrap -->
   
    <script src="assets/js/scripts.js"></script>
	
	 <script src="assets/js/lib/data-table/datatables.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.dataTables.min.js"></script>
    <script src="assets/js/lib/data-table/dataTables.buttons.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.flash.min.js"></script>
    <script src="assets/js/lib/data-table/jszip.min.js"></script>
    <script src="assets/js/lib/data-table/pdfmake.min.js"></script>
    <script src="assets/js/lib/data-table/vfs_fonts.js"></script>
    <script src="assets/js/lib/data-table/buttons.html5.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.print.min.js"></script>
    <script src="assets/js/lib/data-table/datatables-init.js"></script>



</body>

</html>