invisio v2.0

<?php

include('config.php');
	
	$fdate = $_POST['fdate'];
	$from = date('d-m-Y', strtotime($fdate));
	$tdate = $_POST['tdate'];
	$to = date('d-m-Y', strtotime($tdate));
	
	$enq = 'Confirmed';
	$enq1 = 'Finished';
	
	
	/*$sql = $js->prepare('SELECT * FROM customer_entry WHERE (plannningdate >= :fdate AND plannningdate <= :tdate) AND enquirystatus = :enqstatus ORDER BY `plannningdate` ');*/
	$sqlh = $js->prepare('SELECT * FROM `customer_entry` WHERE enquirystatus = :enq OR enquirystatus =  :enq1 ORDER BY `plannningdate` ');
	$sqlh->execute(array(':enq' => $enq,
						':enq1' => $enq1));
						
	$sqlt = $js->prepare('SELECT * FROM `customer_entry` WHERE enquirystatus = :enq OR enquirystatus =  :enq1 ORDER BY `plannningdate` ');
	$sqlt->execute(array(':enq' => $enq,
						':enq1' => $enq1));
	
?>

@media print {
  #table{
    max-width: 2480px;
    width:100%;
  }
  #table td{
    width: auto;
    overflow: hidden;
    word-wrap: break-word;
  }
	.noprint {display:none;}
}
</style>
<script>
function myFunction() {
    window.print();
}
</script>

<div align="center" style="background-color:#FFF">
	<img src="images/logodmc.png"><HR  style="border-color:#5053FD" />
	<h3>Outstanding List from  <?php echo $from?> to <?php echo $to?></h3>
    <table style="border-collapse: collapse; "
		id="table" border="1" cellpadding="5">
        <thead>
        	<tr>
            	<th colspan="10" style="background-color:#1E487C; color:#fff; font-size:20px;" align="center">HOTELS</th>
            </tr>
            <tr style="background-color:#D9E1F3;">
              <th style='width:3%'>#</th>
              <th style='width:9%'>Customer Name</th>
              <th style='width:8%'>Hotel Name</th>
              <th style='width:7%'>Check-In Date</th>
              <th style='width:7%'>Check-Out Date</th>
              <th style='width:8%'>Room Type</th>
              <th style='width:8%'>Rooms</th>
              <th style='width:4%'>Plan</th>
              <th style='width:8%'>Confirmation Number</th>
              <th style='width:6%'>Cost</th>
          </tr>
        </thead>
        <tbody>  
          <?php
		  	
			$h = 1;
			$payamount = 0;
			$totalpaid = 0;
			$hotels = 'hotel';
		  	while($row = $sqlh->fetch(PDO::FETCH_ASSOC)) 
			  {
				  $chid = $row['id'];
				  $custname = strtoupper($row['custname']);
				  
				  $resulthot = $js->prepare('SELECT * FROM `hotelreservations` WHERE custid = :chid AND (coutdate >= :fdate AND coutdate <= :tdate) ORDER BY `coutdate` ');
		 		  $resulthot->execute(array(':chid' => $chid,
				  							':fdate' => $fdate,
											':tdate' => $tdate)); 
						 
				  while($rowhot = $resulthot->fetch(PDO::FETCH_ASSOC)) 
					{
						
						$id = $rowhot['id'];
						$hotel = $rowhot['hotel'];
						$cindate = date('d-m-Y', strtotime($rowhot['cindate']));
						$coutdate = date('d-m-Y', strtotime($rowhot['coutdate']));
						$roomtype = $rowhot['roomtype'];
						$norooms = $rowhot['norooms'];
						$noebs = $rowhot['noebs'];
						if($noebs != '')
						{
							$rooms = $norooms.' + '.$noebs.' Extra-Bed';	
						}
						else
						{
							$rooms = $norooms." Room(s)";	
						}
						$meals = $rowhot['meals'];
						$roomrate = $rowhot['roomrate'];
						$remhotel = $rowhot['remarks'];
						$ebrate = $rowhot['ebrate'];
						if($ebrate != '')
						{
							$roomrate += $ebrate;
						}
						else
						{
							$roomrate = $roomrate;	
						}
						$addservices = $rowhot['addservices']; 
						$totals = $roomrate;
												
						$resultr = $js->prepare('SELECT * FROM `customer_expense` WHERE custid = :chid AND paysubhead = :id AND payhead = :hotels ');
						$resultr->execute(array(':chid' => $chid,
												':id' => $id,
												':hotels' => $hotels)); 
						while($rowr = $resultr->fetch(PDO::FETCH_ASSOC)) 
						{
							$payamount = $rowr['payamount'];
							$totalpaid += $payamount;
						}
							
						
																			
						
						$balance = $totals - $totalpaid;
						
						
						//echo $custname." rate ".$totals." balance ".$balance."<br>";
						
						$resultprod = $js->prepare('SELECT * FROM `hotelvendor` WHERE id = :hotel ');
						$resultprod->execute(array(':hotel' => $hotel)); 
						while($rowprod = $resultprod->fetch(PDO::FETCH_ASSOC)) 
						{
							$location = $rowprod['location'];
							$hotelname = $rowprod['hotelname'];
						}
						$pieces4 = explode(".", $balance);
						$mainmenu4 = $pieces4[0];
						$submenu4 =  $pieces4[1];
						$str= strlen($submenu4);
						if($str == '1')
							$submenu4 .= '00';
						elseif($str == '2')
							$submenu4 .= '0';
						elseif($str == '0')
							$submenu4 .= '000';
						else{}
						$balance = $mainmenu4.".".$submenu4;
						
						if($balance != '0')
						{
							echo "<tr>";
								echo "<td>$h</td>";
								echo "<td align='center'>$custname</td>";
								echo "<td align='center'>$hotelname <br>$location</td>";
								echo "<td align='center'>$cindate</td>";
								echo "<td align='center'>$coutdate</td>";
								echo "<td align='center'>$roomtype</td>";
								echo "<td align='center'>$norooms</td>";
								echo "<td align='center'>$meals</td>";
								echo "<td align='center'>$remhotel</td>";
								echo "<td align='right' style='padding-right:10px;'>$balance</td>";
								$grand_total += $balance;
							echo "</tr>";
							$h++;
						}
						$totals=0;
						$totalpaid = 0;
						$payamount = 0;
						$payamountt=0;
						$payable=0;
						$roomrate=0;
					}
					
			  }
			  
			  $pieces4 = explode(".", $grand_total);
							  $mainmenu4 = $pieces4[0];
							  $submenu4 =  $pieces4[1];
							  $str= strlen($submenu4);
							  if($str == '1')
								  $submenu4 .= '00';
							  elseif($str == '2')
								  $submenu4 .= '0';
							  elseif($str == '0')
								  $submenu4 .= '000';
							  else{}
							  $grand_total = $mainmenu4.".".$submenu4;
			  
		  ?>
          	<tr style="background-color:#67009B; color:#fff; font-weight:600;">
                	<td colspan="9" align="right">TOTAL</td>
                    <td align="right" style="padding-right:10px;"><?php echo $grand_total?></td>
                </tr>
        </tbody>
        </table>
        
        
        
        <table style="border-collapse: collapse; "
		id="table" border="1" cellpadding="5" width="100%">
            <thead>
                <tr>
                    <th colspan="4" style="background-color:#E49922; color:#fff; font-size:20px;" align="center">TRANSPORTATION</th>
                </tr>
                <tr style="background-color:#FAEDCD;">
                  <th style='width:3%'>#</th>
                  <th style='width:50%'>Customer Name</th>
                  <th style="width:25%">Travel Period</th>
                  <th style='width:6%'>Balance</th>
              </tr>
            </thead>
            
        	<tbody>
            	
                <?php
				$t = 1;
				$charge=0;
							  $advance=0;
							  $payamountt=0;
							  $totalpaidtr = 0;
							  $balanceveh=0;
				$transportation = 'transportation';
					while($rowz = $sqlt->fetch(PDO::FETCH_ASSOC)) 
					  {
						  $cid = $rowz['id'];
						  $custname = strtoupper($rowz['custname']);
						  $pdate = date('d-m-Y', strtotime($rowz['plannningdate']));
						  $days = $rowz['daysplanning'];
						  $todate = date('d-m-Y', strtotime($pdate.' + '.$days.' days - 1 day')); 
						  $psdates = date('d-m-Y', strtotime($rowz['plannningdate']));
						  $tosdates = date('Y-m-d', strtotime($psdates.' + '.$days.' days - 1 day')); 
						  
						  if($tosdates >= $fdate && $tosdates <= $tdate)
						  {
							  $resultserpt = $js->prepare('SELECT * FROM `travelassistance` WHERE custid = :cid ORDER BY `id` ');
							  $resultserpt->execute(array(':cid' => $cid)); 
							  $tcount = $resultserpt->rowCount();
							  if($tcount > '0')
							  {
							  while($rowserp = $resultserpt->fetch(PDO::FETCH_ASSOC)) 
							  {
								  $tid = $rowserp['id'];
								  $advance = $rowserp['advance'];
								  
								  $charge = $rowserp['vehiclecharge'];
								  
								  
								  $resultrt = $js->prepare('SELECT * FROM `customer_expense` WHERE custid = :cid AND paysubhead = :tid AND payhead = :transportation ');
								  $resultrt->execute(array(':cid' => $cid,
														  ':tid' => $tid,
														  ':transportation' => $transportation)); 
								  while($rowrt = $resultrt->fetch(PDO::FETCH_ASSOC)) 
								  {
									  $payamountt = $rowrt['payamount'];
									  $totalpaidtr += $payamountt;
								  }
								  
								  $balanceveh = $charge - $advance - $totalpaidtr;
								  $bal = $balanceveh;
								  
								   if($bal != '0' || $bal != '0.000')
									 {
										  echo "<tr>";
											echo "<td>$t</td>";
											echo "<td>$custname</td>";
											echo "<td align='center'>$psdates To $todate</td>";
											echo "<td align='right' style='padding-right:10px;'>$balanceveh</td>";
											 $payable += $bal;
										  echo "</tr>";
										  $t++;
										 
									 }
							  }
							  
							  $pieces4 = explode(".", $balanceveh);
							  $mainmenu4 = $pieces4[0];
							  $submenu4 =  $pieces4[1];
							  $str= strlen($submenu4);
							  if($str == '1')
								  $submenu4 .= '00';
							  elseif($str == '2')
								  $submenu4 .= '0';
							  elseif($str == '0')
								  $submenu4 .= '000';
							  else{}
							  $balanceveh = $mainmenu4.".".$submenu4;
		
							
							  $charge=0;
								  $advance=0;
								  $payamountt=0;
								  $totalpaidtr = 0;
								  $balanceveh=0;
								  $bal=0;
							  }
							 
						  }
					  }
					  
					  $pieces4 = explode(".", $payable);
					  $mainmenu4 = $pieces4[0];
					  $submenu4 =  $pieces4[1];
					  $str= strlen($submenu4);
					  if($str == '1')
						  $submenu4 .= '00';
					  elseif($str == '2')
						  $submenu4 .= '0';
					  elseif($str == '0')
						  $submenu4 .= '000';
					  else{}
					  $payable = $mainmenu4.".".$submenu4;
															
															
				?>
            	<tr style="background-color:#8D6E51; color:#fff; font-weight:600;">
                	<td colspan="3" align="right">TOTAL</td>
                    <td align="right" style="padding-right:10px;"><?php echo $payable?></td>
                </tr>
            </tbody>
          </table>
          
         
       </div>
       
    <div class="noprint">
	<p align="center">
    	<a href="#" onClick="myFunction()" style="border:5px solid; background-color:#093; color:#FFF; padding:8px; text-decoration:none;">PRINT</a>
        
        <a href="home.php" style="border:5px solid; background-color:#F00; color:#FFF; padding:8px; text-decoration:none;">CLOSE</a>
    </p>
</div>
<?php
	include('footer.php');
?>