时间戳

首页 > 解决方案 > 如何根据日期时间搜索计算特殊日和正常日的总金额

php - 如何根据日期时间搜索计算特殊日和正常日的总金额

问题描述

我有一个预订系统，其中 2019-02-15 8:30:00 到 2019-02-16 8:30:00 的特价是 400 美元。房间的正常价格是 360 美元。额外小时费为 30 美元。所以如果有人从 2019-02-14 08:30:00 到 2019-02-17 11:30:00 预订，我如何计算金额。请帮助mysql php。

表#房间

+----+--------+------+-----------+
| ID |  room  | rate | hrly_rate |
+----+--------+------+-----------+
|  1 | room 1 |  360 |        30 |
+----+--------+------+-----------+

表#special_calendar

+----+--------+------+--------------------+--------------------+
| ID |  room  | rate |     start_date     |      end_date      |
+----+--------+------+--------------------+--------------------+
|  1 | room 1 |  400 | 2019-02-15 8:30:00 | 2019-02-16 8:30:00 |
+----+--------+------+--------------------+--------------------+

预期输出：-

Iteration 1: 2019-02-14 08:30:00 - 2019-02-15 08:30:00  Amount $360
Iteration 2: 2019-02-15 08:30:00 - 2019-02-16 08:30:00  Amount $400
Iteration 3: 2019-02-16 08:30:00 - 2019-02-17 08:30:00  Amount $360
Iteration 4: 2019-02-17 08:30:00 - 2019-02-17 11:30:00  Amount $90 (3 X hourly rate)
Total Amount: 1210

对于预订，我从文本框中输入。

Check in: 2019-02-14 08:30:00
Checkout: 2019-02-17 11:30:00

在我的代码下方，但出现 500 错误

    $amt=0;
    $chekin_date=date("Y-m-d H:i:s", strtotime($_GET['chekin_date']));
    $checkout_date=date("Y-m-d H:i:s", strtotime($_GET['checkout_date']));
    $query_result=$this->general->execute_query2("SELECT * from special_calendar where category_id='$row->id' and '$chekin_date'<=start_date && end_date<='$checkout_date'");
         if(!empty($query_result)){
            while($chekin_date<$checkout_date)
              {
                foreach($query_result as $result)
                {
                    if($result->start_date<=$chekin_date && $checkout_date<$result->end_date)
                   {
                     $amt = $amt+$result->rate;  
                   }
                   else
                   {
                     //normal rate
                     $amt = $amt+$row->rate_day;
                   }
                }
                 // increase check in date by 1
$datetime = new DateTime($chekin_date);
$datetime->modify('+1 day');
 $chekin_date = $datetime->format('Y-m-d H:i:s');
              }
         }
          else
            {
            $hrdif= $this->general->dateDifference($chekin_date , $checkout_date , $differenceFormat = '%h' );
              $daydif= $this->general->dateDifference($chekin_date , $checkout_date , $differenceFormat = '%a' );
              $amt=$amt+($daydif*$row->rate_day);
              if($hrdif!=0){
            $amt=$amt+($hrdif*$row->hrly_rate);
        }
       }
        echo $amt;

标签： phpmysql

解决方案

首先，如果有条目，您必须在特殊的日历表上搜索，然后获取费率。如果不是，则在查询中取总和

    //below code Logically presented. proceed with your content
    $amt=0;
    $query = check_in<=special_check_in && special_check_out<=check_out
    $query_result = store result;
    if($query_count>0)
    {
      while($check_in<$check_out)
      {
        foreach($query_result as $result)
        {
           if($result[special_check_in]<=$check_in && $check_in<$result[special_check_out])
           {
             $amt = $amt+$result['special_rate'];  
           }
           else
           {
             //add room table rate fetch by using query
             $amt = $amt+$room_table_rate
           }
        }
        check_in = check_in+1 // increase check in date by 1
      }
    }
    else
    {
       $query = "select SUM(rate) FROM room_table where room_id=your_room_id";
       $amt = $query_result_sum;
    }

echo $amt; //this is your final booking rate

推荐阅读