跨越多个日期的酒店价格问题 [英] Hotel prices spanning multiple dates issue

问题描述

问题与该问题有某种联系，除了我使用参数外。
按钮上有这个按钮：

Question is somehow related to this one, with the exception that I use parameters. I have this on my button click :

procedure TForm1.Button1Click(Sender: TObject);
begin
with ABSQuery1 do begin
ABSQuery1.Close;
ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Add('select * from ROOM_RATES where CENIK_ID = :a4 and ROOM_TYPE = :A1');
ABSQuery1.SQL.Add('and rate_Start_DATE < :a3 AND rate_End_DATE  > :a2 ORDER BY rate_Start_DATE ASC ');
ABSQuery1.Params.ParamByName('a1').Value:= cxLookupComboBox2.Text;
ABSQuery1.Params.ParamByName('a2').Value:= cxDateEdit1.Date;
ABSQuery1.Params.ParamByName('a3').Value := cxDateEdit2.Date;
ABSQuery1.Params.ParamByName('a4').Value := cxLookupComboBox1.Text;
ABSQuery1.Open;
end;
end;

这种类型的作品，但实际上并不是我想要的。问题与此相关：
酒店预订率SQL问题

This kind of works but not what I want actually.Problem is related to this one: Hotel Booking Rates SQL Problem

问题是像上面提到的超链接中那样有重叠的日期。现在我明白了：

Problem is with the overlapping dates like in the mentioned hyperlink.Right now I am getting this :

我如何在上面提到的超链接中获得类似的结果例子？

How can I obtain result similar in the mentioned hyperlink with the above example ?

这是数据库表的快照：

This is the snapshot of the db table :

更新（新）：
这是单击按钮的代码：

Update (NEW): This is the code on the button click :

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
var
     nxt             : integer;
     mem_from         : TDateTime;
     mem_to           : TDateTime;
     mem_RATE_ID      : integer;
     mem_ROOM_TYPE    : string[10];
     mem_Start_DATE_1 : TDateTime;
     mem_End_DATE_1   : TDateTime;
     mem_RATE_Price_1 : Currency;
     mem_calc_END     : TDateTime;
     mem_calc_DAYS    : integer;
     c_from           : TDateTime;
     c_to             : TDateTime;
     c_from_test      : TDateTime;
     c_to_test        : TDateTime;

begin
ABSQuery2.Close;
ABSQuery2.SQL.Text:='DELETE from TEMP';
ABSQuery2.ExecSQL;
ABSQuery2.SQL.Text:='SELECT * from TEMP ORDER BY ID ';
ABSQuery2.Open;

c_from := cxDateEdit1.Date;
c_to   := cxDateEdit2.Date;

mem_from := cxDateEdit1.Date;
mem_to   := cxDateEdit2.Date;

with ABSQuery1 do begin
ABSQuery1.Close;
ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Add('select * from ROOM_RATES where CENIK_ID = :a4 and ROOM_TYPE = :A1');
ABSQuery1.SQL.Add('and rate_Start_DATE < :a3 AND rate_End_DATE  > :a2 ORDER BY rate_Start_DATE ASC ');
ABSQuery1.Params.ParamByName('a1').Value:= cxLookupComboBox2.Text;
ABSQuery1.Params.ParamByName('a2').Value:= cxDateEdit1.Date;
ABSQuery1.Params.ParamByName('a3').Value := cxDateEdit2.Date;
ABSQuery1.Params.ParamByName('a4').Value := cxLookupComboBox1.Text;
ABSQuery1.Open;

     nxt              := 1;
     mem_RATE_ID      := ABSQuery1.FieldByName('RATE_ID').AsInteger;
     mem_ROOM_TYPE    := ABSQuery1.FieldByName('ROOM_TYPE').AsString ;
     mem_Start_DATE_1 := ABSQuery1.FieldByName('RATE_START_DATE').AsDateTime;
     mem_End_DATE_1   := ABSQuery1.FieldByName('RATE_END_DATE').AsDateTime;
     mem_RATE_Price_1 := ABSQuery1.FieldByName('RATE_PRICE').AsCurrency;

     if mem_to > mem_End_DATE_1 then begin
         mem_calc_END  := mem_End_DATE_1;
         mem_calc_DAYS := Daysbetween(mem_from,mem_End_DATE_1);
     end else begin
         mem_calc_END  := mem_to;
         mem_calc_DAYS := Daysbetween(mem_from,mem_calc_END);
     end;
end;

if ABSQuery1.RecordCount > nxt then ABSQuery1.Next;
with ABSQuery2 do begin
open;
Insert;
  ABSQuery2.FieldByName('RATE_ID').AsInteger:=mem_RATE_ID;
  ABSQuery2.FieldByName('ROOM_TYPE').AsString:=mem_ROOM_TYPE;
  ABSQuery2.FieldByName('DATE_FROM').AsDateTime:=mem_from;
  ABSQuery2.FieldByName('DATE_TO').AsDateTime:= mem_to;//mem_calc_END;
  ABSQuery2.FieldByName('RATE_PRICE').AsCurrency:=mem_RATE_PRICE_1;
  ABSQuery2.FieldByName('DAYS').AsInteger:=mem_calc_DAYS;
  ABSQuery2.FieldByName('TOTAL').AsCurrency:=mem_RATE_PRICE_1 * mem_calc_DAYS;
post;
end;  ///////////////////////////////////////////////////////////////////
if ABSQuery1.RecordCount > nxt then begin
     inc(nxt);

    if mem_to < ABSQuery1.FieldByName('rate_End_DATE').AsDateTime then begin
       mem_calc_END   := mem_to;
       mem_calc_DAYS  := Daysbetween(ABSQuery1.FieldByName('rate_Start_DATE').AsDateTime,mem_calc_END);
    end else begin
       mem_calc_END   := ABSQuery1.FieldByName('rate_End_DATE').AsDateTime;
       mem_calc_DAYS  := Daysbetween(ABSQuery1.FieldByName('rate_Start_DATE').AsDateTime, ABSQuery1.FieldByName('rate_End_DATE').AsDateTime);
    end;
       mem_RATE_ID      := ABSQuery1.FieldByName('RATE_ID').AsInteger;
       mem_ROOM_TYPE    := ABSQuery1.FieldByName('ROOM_TYPE').AsString;
       mem_Start_DATE_1 := ABSQuery1.FieldByName('rate_Start_DATE').AsDateTime;
       mem_End_DATE_1   := ABSQuery1.FieldByName('rate_End_DATE').AsDateTime;
       mem_Rate_Price_1 := ABSQuery1.FieldByName('RATE_PRICE').AsCurrency;

    // calculation : second row.
with ABSQuery2 do begin
Insert;
  FieldByName('RATE_ID').AsInteger:=mem_RATE_ID;
  FieldByName('ROOM_TYPE').AsString:=mem_ROOM_TYPE;
  FieldByName('DATE_FROM').AsDateTime:=mem_Start_DATE_1;
  FieldByName('DATE_TO').AsDateTime:= mem_calc_END;
  FieldByName('RATE_PRICE').AsCurrency:=mem_RATE_PRICE_1;
  FieldByName('DAYS').AsInteger:=mem_calc_DAYS;
  FieldByName('TOTAL').AsCurrency:=mem_RATE_PRICE_1 * mem_calc_DAYS;
post;
end;
 ABSQuery2.refresh;
end;
end;

我得到的结果是：

The result I get is this :

从数据库快照中可以看到，价格已确定。

As you can see from the database snapshot, prices are set OK.

推荐答案

在Delphi 2010中进行了测试。

Tested with Delphi 2010.

您只有一个DBGrid，与数据集表价格列表关联

Your only one DBGrid, are associated with the dataset-table-pricelist

匹配两行数据集表价格列表，因此在您的ABSQuery1 DBGrid

价格的第1行

显示价格清单中的第3行。

matches two rows of dataset-table-pricelist and so in your ABSQuery1 DBGrid
Row 1 from price list is shown.
Row 3 from price list is shown.

两个行过程现在都 ABSQuery1CalcFields（DataSet：TDataSet ）;

被调用为具有相同值!!

Now for both Rows procedure ABSQuery1CalcFields(DataSet: TDataSet);
is called with the same values !!

Daysbetween(cxDateEdit1.Date,cxDateEdit2.Date) = allways 19

。

procedure TForm1.ABSQuery1CalcFields(DataSet: TDataSet);
begin
ABSQuery1.FieldByName('Days').Value := IntToStr(Daysbetween(cxDateEdit1.Date,cxDateEdit2.Date));
ABSQuery1.FieldByName('TOTAL').AsCurrency :=ABSQuery1.FieldByName('Days').Value * ABSQuery1.FieldByName('RATE_PRICE').Value ;
end;

因此，您在DBGrid中有两次，因此，天数为19
这两个字段是From和To come

因此，您将无法看到自己的数据从和到。

Therefore you have in your DBGrid twice Days are 19 The two fields From and To come also from the Table Price List.
Therefore, you can not see your own data From and To.

您应该有2张桌子

• 价格表


• 计算

在价目表上循环，获取价格表中所需的数据。

With a loop on the table pricelist, fetch the required data of the price list.

• 清晰的计算。


• 插入从价格表中获得的数据。

< img src = https://i.stack.imgur.com/dQ4Iy.jpg alt =在此处输入图片描述>

因为我不确定如何设置表，必须使代码适应数据库和表。

Because I do not know exactly how your table is set up, you have to adapt the code to the database and your table.

为了更好地显示必要的步骤，请在下面的代码中进行操作。 br>
更新：在这里，现在是完整的代码。

In order to show the necessary steps better, here the following code.
Update : Here, now the complete code.

unit PriceList;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Db, ZAbstractRODataset, ZAbstractDataset,
  ZDataset, ZConnection;

type
  TForm1 = class(TForm)
    ZConnection1: TZConnection;
    ABSQuery1: TZQuery;
    calculation: TZQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    DoCalc: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    RATE_ID: TLargeintField;
    CENIK_ID: TLargeintField;
    ROOM_TYPE: TWideStringField;
    RATE_START_DATE: TDateTimeField;
    RATE_END_DATE: TDateTimeField;
    RATE_PRICE: TFloatField;
    calculationID: TLargeintField;
    calcRATE_ID: TLargeintField;
    calcROOM_TYPE: TWideStringField;
    calcDFROM: TDateTimeField;
    calcDTO: TDateTimeField;
    calcRATE_PRICE: TFloatField;
    calcDAYS: TLargeintField;
    calcTOTAL: TFloatField;
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

uses DateUtils;

procedure TForm1.DoCalcClick(Sender: TObject);
var
     nxt             : integer;
     mem_from         : TDateTime;
     mem_to           : TDateTime;

     mem_RATE_ID      : integer;
     mem_ROOM_TYPE    : string[20];
     mem_Start_DATE_1 : TDateTime;
     mem_End_DATE_1   : TDateTime;
     mem_RATE_Price_1 : Currency;
     mem_calc_END     : TDateTime;
     mem_calc_DAYS    : integer;
     c_from           : string[19];
     c_to             : string[19];
     c_from_test      : string[19];
     c_to_test        : string[19];

begin
calculation.Close;
calculation.SQL.Text:='DELETE from calculation';
calculation.ExecSQL;
calculation.SQL.Text:='SELECT * from calculation ORDER BY ID ';
calculation.Open;

c_from := Edit3.Text;
c_to   := Edit4.Text;
c_from_test := copy(Edit3.Text,7,4)+'.'+copy(Edit3.Text,4,2)+'.'+copy(Edit3.Text,1,2); // From 01.01.2013
c_to_test   := copy(Edit4.Text,7,4)+'.'+copy(Edit4.Text,4,2)+'.'+copy(Edit4.Text,1,2);
mem_from         := StrToDateTime(c_from);
mem_to           := StrToDateTime(c_to);

with ABSQuery1 do begin
    Close;
    SQL.Clear;
    SQL.Add('select * from ROOM_RATES where CENIK_ID = "'+Edit1.Text+'" and ROOM_TYPE = "'+Edit2.Text+'"');
    SQL.Add('and RATE_START_DATE < '''+c_to_test+''' AND RATE_END_DATE  > '''+c_from_test+''' ORDER BY RATE_START_DATE ASC ');
    Open;
     nxt              := 1;
     mem_RATE_ID      := RATE_ID.AsLargeInt;
     mem_ROOM_TYPE    := ROOM_TYPE.AsString ;
     mem_Start_DATE_1 := RATE_START_DATE.AsDateTime;
     mem_End_DATE_1   := RATE_END_DATE.AsDateTime;
     mem_RATE_Price_1 := RATE_PRICE.AsCurrency;

     if mem_to > mem_End_DATE_1 then begin
         mem_calc_END  := mem_End_DATE_1;
         mem_calc_DAYS := Daysbetween(mem_from,mem_End_DATE_1);
     end else begin
         mem_calc_END  := mem_to;
         mem_calc_DAYS := Daysbetween(mem_from,mem_calc_END);
     end;

end;

if ABSQuery1.RecordCount > nxt then ABSQuery1.Next;

with calculation do begin
open;
Insert;
calculation.FieldByName('RATE_ID').AsInteger:=mem_RATE_ID;
calculation.FieldByName('ROOM_TYPE').AsString:=mem_ROOM_TYPE;
calculation.FieldByName('DFROM').AsDateTime:=mem_from;
calculation.FieldByName('DTO').AsDateTime:= mem_calc_END;
calculation.FieldByName('RATE_PRICE').AsCurrency:=mem_RATE_PRICE_1;
calculation.FieldByName('DAYS').AsInteger:=mem_calc_DAYS;
calculation.FieldByName('TOTAL').AsCurrency:=mem_RATE_PRICE_1 * mem_calc_DAYS;
post;
end;

if ABSQuery1.RecordCount > nxt then begin
     inc(nxt);
    if mem_to < rate_End_DATE.AsDateTime then begin
         mem_calc_END   := mem_to;
         mem_calc_DAYS  := Daysbetween(rate_Start_DATE.AsDateTime,mem_calc_END);
    end else begin
         mem_calc_END   := rate_End_DATE.AsDateTime;
         mem_calc_DAYS  := Daysbetween(rate_Start_DATE.AsDateTime, rate_End_DATE.AsDateTime);
    end;
         mem_RATE_ID      := RATE_ID.AsInteger;
         mem_ROOM_TYPE    := ROOM_TYPE.AsString;
         mem_Start_DATE_1 := rate_Start_DATE.AsDateTime;
         mem_End_DATE_1   := rate_End_DATE.AsDateTime;
         mem_Rate_Price_1 := RATE_PRICE.AsCurrency;

with calculation do begin
Insert;
FieldByName('RATE_ID').AsInteger:=mem_RATE_ID;
FieldByName('ROOM_TYPE').AsString:=mem_ROOM_TYPE;
FieldByName('DFROM').AsDateTime:=mem_Start_DATE_1;
FieldByName('DTO').AsDateTime:= mem_calc_END;
FieldByName('RATE_PRICE').AsCurrency:=mem_RATE_PRICE_1;
FieldByName('DAYS').AsInteger:=mem_calc_DAYS;
FieldByName('TOTAL').AsCurrency:=mem_RATE_PRICE_1 * mem_calc_DAYS;
post;
end;
end;
calculation.refresh;
end;

end.

由于时间限制，代码未优化。仅仅是显示必要的步骤。

Of time constraints the code is not optimized. It is only to show the necessary steps.

TABLE room_rates

DROP TABLE IF EXISTS `room_rates`;
CREATE TABLE  `room_rates` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CENIK_ID` int(10) unsigned NOT NULL,
  `ROOM_TYPE` varchar(45) NOT NULL,
  `RATE_START_DATE` datetime NOT NULL,
  `RATE_END_DATE` datetime NOT NULL,
  `RATE_PRICE` decimal(5,2) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

TABLE 计算

DROP TABLE IF EXISTS `calculation`;
CREATE TABLE  `calculation` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `RATE_ID` int(10) unsigned NOT NULL,
  `ROOM_TYPE` varchar(45) NOT NULL,
  `DFROM` datetime NOT NULL,
  `DTO` datetime NOT NULL,
  `RATE_PRICE` decimal(5,2) NOT NULL,
  `DAYS` int(10) unsigned NOT NULL,
  `TOTAL` decimal(7,2) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

更新：

如果mem_to>搜索 mem_End_DATE_1

要更改总计0,00€您必须展开

Search for if mem_to > mem_End_DATE_1
To change Total 0,00€ You have to expand

     if mem_to > mem_End_DATE_1 then begin
         mem_calc_END  := mem_End_DATE_1;
         mem_calc_DAYS := Daysbetween(mem_from,mem_End_DATE_1);
     end else begin 
         mem_calc_END  := mem_to;
         mem_calc_DAYS := Daysbetween(mem_from,mem_calc_END);
     end;

更新2 ：上面是完整的代码。

Update 2 : above, now the complete code.

更新3 ：但我仍然可以从14/4/2013到26/4/2013 11天！ user763539

Update 3 : but still I get from 14/4/2013 to 26/4/2013 11 DAYS ! user763539

此行为来自 DaysBetween（..，..），而不是我的代码。

DaysBetween是一个delphi函数！

This behavior comes from DaysBetween(..,..) and not from my code.
DaysBetween is a delphi function!

我问你3次。

您是否检查了从 cxDateEdit1.Date 和 cxDateEdit2.Date 得到的结果。

Did you check what you get from cxDateEdit1.Date and cxDateEdit2.Date.

必须准确地 14-04-2013 00:00:00 和 26-04-2013 00:00:00 。

创建一个新的测试程序。

Create a new test programm.

控制您得到的东西。

DateTimeToString(formattedDateTime, 'c', cxDateEdit1.Date);
Memo1.Lines.Add(formattedDateTime);

对所有 ROOM_RATES记录进行循环您还应该检查 ROOM_RATES 中的所有日期字段。

With a loop over all ROOM_RATES records You should also check all date fields in ROOM_RATES.

DateTimeToString(formattedDateTime, 'c', ABSQuery1.FieldByName('RATE_START_DATE').AsDateTime);
Memo1.Lines.Add(formattedDateTime);

所有时间应为00:00:00

All times should be 00:00:00

例如：

DaysBetween .. 14-04-2013 12:15:10 和 26-04-2013 12:15:05 == 11天`

DaysBetween .. 14-04-2013 12:15:10and26-04-2013 12:15:05==11 Days`

更准确： 11天：23小时：59分钟：55秒。

这篇关于跨越多个日期的酒店价格问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！