跨越多个日期的酒店价格问题

本文介绍了跨越多个日期的酒店价格问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 问题与该问题有某种联系，除了我使用参数外。 按钮上有这个按钮：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);beginwith ABSQuery1 do beginABSQuery1.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;beginABSQuery2.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 beginABSQuery1.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 beginopen;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 beginInsert; 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 DBGridRow 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);beginABSQuery1.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 19The 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. 清晰的计算。 插入从价格表中获得的数据。因为我不确定如何设置表，必须使代码适应数据库和表。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;interfaceuses 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];begincalculation.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.2013c_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 beginopen;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 beginInsert;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_ratesDROP 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_1To 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天！ user763539Update 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:00All 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秒。 这篇关于跨越多个日期的酒店价格问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！