Hotel prices spanning multiple dates issue

Question

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;

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.

Answer 1

Tested with Delphi 2010.

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

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.

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;

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.

You should have 2 tables

• Price list
• calculation

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

• clear calculation.
• insert the data you get from Table 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.

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 calculation

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;

Update :

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;

Update 2 : above, now the complete code.

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

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

I ask you 3 times .

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

It must be to accurately 14-04-2013 00:00:00 and 26-04-2013 00:00:00 .

Create a new test programm.

Controlling what you get with.

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

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);

All times should be 00:00:00

For example:

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

more accurately: 11 Days : 23 Hours : 59 minutes : 55 seconds.