ขอคำแนะนำเพิ่มเติมครับ เพื่อสรุปรูปแบบบัญชี โดยแยกจำนวนเงินไปแสดงตามช่อง ที่กำหนด อย่างไรครับ
รูปแสดงข้อมูล แยกประเภท จำนวนเงิน และยอดรวมของแต่ละวัน ****บรรทัด 303 ถึง 308 ผมลองเขียนดู ข้อมูลมา ที่(House used) อย่างเดียว อย่างอื่นไม่มา น่าจะวนลูปไม่ถูกแน่ๆ**** Code (PHP) <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-gb" lang="en-gb" >
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
include 'header_admin2.php';
?>
<script type="text/javascript">
// stop window onload
window.onload=function(){
var auto = setTimeout(function(){ autoRefresh(); }, 100);}
function autoRefresh(){
clearTimeout(auto);
auto = setTimeout(function(){ submitform(); autoRefresh(); }, 10000);
}
</script>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script type="text/javascript" src="jQueryui/jQuery.js"></script>
<script type="text/javascript" src="jQueryui/jQuery-ui.js"></script>
</head>
<script type="text/javascript">
// กำหนดสี billing Status
var search = 'Reservation';
$(document).ready(function () {
$("div:contains('"+search+"')").each(function () {
var regex = new RegExp(search,'gi');
$(this).html($(this).text().replace(regex, "<span class='red'>"+search+"</span>"));
});
});
</script>
<style type="text/css">
table#gridview {
clear: both;
font-weight: bold;
font-size: 14px;
}
table#gridview td {
padding:3px 0px;
}
table#gridview tr.head {
background:#B18904;
font-family:Arial, Helvetica, sans-serif;
text-align:center;
}
table#gridview tr.head td {
padding:3px 0px;
}
text-indent:0.5in;
#gridviewsss {
color: #FFF;
}
body {
background-color: #D3D3D3;
}
body,td,th {
color: #333;
}
thead > tr{background:#;}
th, td{font-family:Arial, Helvetica, sans-serif; font-weight:normal}
thead th{color:#;font-size:12px;font-weight:bold;}
tr{height:20px;}
tbody > tr:nth-child(even) {background: #f3f3f3}
tbody > tr:nth-child(odd) {background: #fff}
tr.tr2{background:#FFB7B7 !important}
tr.tr3 {background:#ABEBC6 !important}
tbody > tr:hover, tr.tr2:hover, tr.tr3:hover{background:#fff5e6 !important}
tbody td{font-size:12px;color:#000}
</style>
<style type="text/css">
@charset "utf-8";
.red{
color: red;
}
p.ex {
color:#31B404;
background: none;
}
mark.red {
color:green;
background: none;
}
mark.blue {
color:gray;
background: none;
}
p.ex { color:#31B404; background: none}
div.canceled > p.ex { color:#a8a8a8 !important;}
</style>
<body>
<?php include('config.php');?>
<form action="Hotel_Summary.php" method="post" name="adminForm" id="adminForm">
<p><br />
<br />
<br />
</p>
<p><br />
</p>
<table width="106%" height="32" border="0" cellpadding="0" cellspacing="0">
<thead>
<tr bgcolor="#C7C7C7" style="font-family:Arial, Helvetica, sans-serif;font-style:normal;font-weight:normal; font-size:13px;color:black">
<td width="192" align="center">Search Room :
<input type="text" name="room_search" id="room_search" style="background: #F5DA81;width:100px;" value="<?=$_POST['room_search'];?>" onChange="document.adminForm.submit();" />
</td>
<td width="68" align="right" class="key"> <span class="ff2">Date : </span></td>
<td width="157">
<input type="text" id= "datepicker" name="date_start" value="<?=$_POST['date_start'];?>"
size="10" style="width:150px;" onChange="document.adminForm.submit();" >
</td>
<td width="68" align="right" class="ff2"> Until Date : </td>
<td width="187" align="left" class="ff2">
<input type="text" id= "datepicker2" name="date_stop" value="<?=$_POST['date_stop'];?>"
size="10" style="width:150px;" onChange="document.adminForm.submit();" />
</td>
<td width="67" align="right" class="ff2">Customer :</td>
<td width="240"><select name="customer_search" id="customer_search" size="1" style="width:200px;" onChange="document.adminForm.submit();">
<option value="" selected="selected">- Select a Category -</option>
<option value="<?=$_POST['customer_search'];?>">
<?=$_POST['customer_search'];?>
</option>
<?
$strSQL = "SELECT * FROM customer_table ORDER BY ID ASC";
$objQuery = mysql_query($strSQL);
while($objResuut = mysql_fetch_array($objQuery))
{
?>
<option value="<?=$objResuut["customer"];?>">
<?=$objResuut["ID"]." ".$objResuut["customer"];?>
</option>
<?
}
?>
</select></td>
<td width="217" align="right"> </td>
<td width="231" align="left"> <span class="ff2">Status :</span><select name="billing_search" id="billing_search" class="inputbox" size="1" onChange="document.adminForm.submit();">
<option value="" selected="selected">- Select Status-</option>
<option value="<?=$_POST['billing_search'];?>"> <?=$_POST['billing_search'];?></option>
<option value="Reservation" >Reservation</option>
<option value="Servicing" >Servicing</option>
<option value="Done" >Done</option>
<option value="canceled" >canceled</option>
</select>
</td>
</tr></thead>
</table>
</form>
<table class="display" width="1367" border="0" align="center" cellpadding="1" cellspacing="1" id="gridview">
<thead>
<tr bgcolor="#D2B4DE" style="font-family:Arial, Helvetica, sans-serif;font-style:normal; font-size:13px;color:black" >
<th width="74" height="34">Date</th>
<th width="71">
Voucher No </th>
<th width="65">
Room </th>
<th width="120">
Guest Name </th>
<th width="149">
Pickup From -
<br/> Send To </th>
<th width="33"> </th>
<th width="85">
Amount </th>
<th width="102">
Account </th>
<th width="108">Room</th>
<th width="85">Credit</th>
<th width="102">Package</th>
<th width="92">House used</th>
<th width="112">
TOTAL</th>
<th width="161">Status </th>
</tr>
</thead>
<?php
$sql_show = "select * from ots_table";
if($_POST['date_start'])
{
$date_start = $_POST['date_start'];
$date_stop = $_POST['date_stop'];
$room_search = $_POST['room_search'];
$Credit="Credit";
$Room="Room";
$Package="Package";
$House="House used";
$customer_search = $_POST['customer_search'];
$billing_search = $_POST['billing_search'];
$sql_show .=" where (pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."')
and roomnumber like '%$room_search%'
and chargedby in ('".$Credit."' , '".$Room."', '".$Package."', '".$House."')
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
ORDER BY pickup_start_date, voucher_no ASC";
}
$result_show = mysql_query($sql_show) or die(mysql_error());
$chk_date = false;
while($rs = mysql_fetch_array($result_show))
{
$pickup_start_date = $rs["pickup_start_date"];
$date = ($chk_date <> $pickup_start_date ? $pickup_start_date : '');
$id = $rs["id"];
$remark = $rs["remark"];
$edit_class = (trim($remark) == '') ? '' : ' hide';
$level = $rs['level'];
$level2 = $rs["level2"];
$tr_class = '';
if ($level == '2') {
$tr_class = ' class="tr2"';
}
else if ($level2 == '3') {
$tr_class = ' class="tr3"';
}
echo '<tr'.$tr_class.'>';
?>
<?php echo '<td height="42" align="center">'.$date.'</td>';?>
<td align="center"> <a href="up_FormReservation.php?data_id=<?php echo $rs['id'];?>"><?php echo $rs['voucher_no'];?></a></td>
<td height="42" align="center">
<?php echo $rs['roomnumber'];?></td>
<td height="42" align="center">
<a href="up_FormReservation.php?data_id=<?php echo $rs['id'];?>"><?php echo $rs['greeting'];?></a></td>
<td height="42" align="center">
<?php echo $rs['destination'];?>-<?php echo $rs['sendto'];?> </td>
<td align="center">
<?php echo $rs['flight_arrival'];?><br /> <span style="font-size:12px; color:#2ECC71;"><?php echo $rs['show_fasktrack'];?></span></td>
<td height="42" align="center">
<? echo number_format($rs['amount']); ?>
</td>
<td height="42" align="center">
<?php echo $rs['chargedby'];?></td>
<td height="42" align="center"> </td>
<td align="center"> </td>
<td height="42" align="center"> </td>
<td align="center"><?php
if($rs["chargedby"] == "House used"){
echo $rs["amount"];
}?></td>
<td align="center"> </td>
<td align="center"><strong>
<div class="<?php echo $rs["billing_status"];?>">
<p class="ex"><?php echo $rs["billing_status"];?></p>
</div>
</strong></td>
<?php
echo '</tr>';
$chk_date = $pickup_start_date;
}
?>
<thead>
<td height="42" colspan="8" align="right"><strong>TOTAL AMOUNT : </strong></td>
<td align="center" bgcolor="#C7C7C7"><strong><?php
// Show Room amount
$query = "SELECT chargedby, SUM(amount) FROM ots_table WHERE chargedby ='Room'
and roomnumber like '%$room_search%'
and reservationby like '%$fleet_search%'
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
and pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'
GROUP BY pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo number_format($row['SUM(amount)']);
}
?></strong></td>
<td height="42" align="center" bgcolor="#C7C7C7">
<strong><?php
// Show Credit amount
$query = "SELECT chargedby, SUM(amount) FROM ots_table WHERE chargedby ='Credit '
and roomnumber like '%$room_search%'
and reservationby like '%$fleet_search%'
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
and pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'
GROUP BY pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo number_format($row['SUM(amount)']);
}
?>
</strong></td>
<td align="center" bgcolor="#C7C7C7">
<strong><?php
// Show Package amount
$query = "SELECT chargedby, SUM(amount) FROM ots_table WHERE chargedby ='Package'
and roomnumber like '%$room_search%'
and reservationby like '%$fleet_search%'
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
and pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'
GROUP BY pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo number_format($row['SUM(amount)']);
}
?>
</strong></td>
<td align="center" bgcolor="#C7C7C7">
<strong><?php
// Show House used amount
$query = "SELECT chargedby, SUM(amount) FROM ots_table WHERE chargedby ='House used'
and roomnumber like '%$room_search%'
and reservationby like '%$fleet_search%'
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
and pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'
GROUP BY pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo number_format($row['SUM(amount)']);
}
?>
</strong></td>
<td align="center" bgcolor="#C7C7C7">
<strong><?php
// Make a MySQL Connection
$query = "SELECT SUM(amount) as s_Total FROM ots_table
where pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'
and chargedby in ('".$Credit."' , '".$Room."', '".$Package."', '".$House."')
and roomnumber like '%$room_search%'
and reservationby like '%$fleet_search%'
and customeragency like '%$customer_search%'
and billing_status like '%$billing_search%'
GROUP BY pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."'";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo number_format($row['s_Total']);
}
?></strong>
</td>
</thead>
</table>
<br />
<br />
</body>
<script type="text/javascript" src="jQueryui/jQuery.js"></script>
<script type="text/javascript" src="jQueryui/jQuery-ui.js"></script>
<script type="text/javascript">
//กำหนด Fleet Serach ทำงาน
document.getElementById("fleet_search").value = "<?=$_POST["fleet_search"];?>";
</script>
<script type="text/javascript">
document.getElementById("customer_search").value = "<?=$_POST["customer_search"];?>";
//-->
</script>
<script type="text/javascript">
document.getElementById("billing_search").value = "<?=$_POST["billing_search"];?>";
//-->
</script>
<script type="text/javascript">
$(function(){
$("#datepicker").datepicker({ dateFormat: "yy-mm-dd" }).val('<?=$_POST['date_start'];?>')
});
</script>
<script type="text/javascript">
$(function(){
$("#datepicker2").datepicker({ dateFormat: "yy-mm-dd" }).val('<?=$_POST['date_stop'];?>')
});
</script>
<link href="jQueryui/jQuery-ui.css" rel="stylesheet" type="text/css"/>
</html>
Tag PHP
ประวัติการแก้ไข
Date :
2016-07-06 07:10:02By :
panya@otsView :
1033Reply :
5
ใช้ sql query sum() แต่ละธนาคารมาครับ เช่่นมี 3 ธนาคารก็
Date :
2016-07-06 16:08:34By :
Dragons_first
หรืออีกเทคนิคนึงคือ ใช้ sql if ครับSELECT p.*,IF(bank='1',amount,'') AS price1,IF(bank='2',amount,'') AS price2,IF(bank='3',amount,'') AS price3 FROM product p INNER JOIN bank bk ON p.bank=bk.id WHERE ตรงนี้ตามเงื่อนไขเลยครับ
Date :
2016-07-06 16:11:32By :
Dragons_first
ขอบคุณครับผม
Date :
2016-07-06 17:04:57By :
panya@ots
join product orders และ bank เพื่อดึงค่าบางค่ามาเฉยๆครับ ผมยกเฉพาะคำสั่งที่น่าจะใช้ได้ ซึ่งมันใกล้เคียงหรือเหมือนกันกับที่ผมทำอยู่ ก็เลยยกตัวอย่างมาแบบนี้ คุณต้องเอาไปปรับใช้ต่อครับ ติดตรงไหนก็สอบถามได้ครับ
Date :
2016-07-06 17:08:55By :
Dragons_first
ขอบคุณครับ ยังหาวิธีไม่ได้ครับ
Date :
2016-07-07 20:05:19By :
panya@ots
Server 04
Load balance : Server 04
