Insert partly existing data

Question

I need to add extra data to my TEMP table shown at the bottom on button click. I need all existing occurences of FROM - TO - DAYS in the table duplicated and inserted. RATE_PRICE would be predetermined & and the same for all new inserts (when inserting). and also inserted.

Example : I have an extra bed to add and it costs 15 euros. Now I would like to, when I check a checkbox (and click on a button) to insert that value (15) in the TEMP table but it must follow the displayed dates values in the grid. I had in mind adding extra field in the TEMP table called EXTRA which would be invisible unless checkbox checked. So when I check an option of adding an extra bed, the extra bed would follow the displayed.Rate price would be 15 then ... dates.

How can I insert desired data ?

UPDATE

I did it on button click :

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
with ABSQuery4 do begin
ABSQuery4.Close;
ABSQuery4.SQL.Clear;
ABSQuery4.SQL.Text := 'INSERT INTO TEMP (extra,Date_From,Date_To,Rate_price,Days,Total) VALUES (:a1,:a2,:a3,:a4,:a5,:a6)';
ABSquery4.Params.ParamByName('a1').asString :='TT';
ABSquery4.Params.ParamByName('a2').value := cxDateEdit1.date;
ABSquery4.Params.ParamByName('a3').value := cxDateEdit2.date;
ABSquery4.Params.ParamByName('a4').value :='1';
ABSquery4.Params.ParamByName('a5').value :=Daysbetween(cxDateEdit1.Date,cxDateEdit2.Date);
ABSquery4.Params.ParamByName('a6').value := (ABSquery4.Params.ParamByName('a4').value)*(ABSquery4.Params.ParamByName('a5').value);
 ABSquery4.ExecSQL ;
 ABSquery2.Refresh;
end;
end;

Any more elegant way ?

Answer 1

You should new readers to point out to which, previous question (code), you are referring. Without this information, they can not understand your question. Hotel prices spanning multiple dates issue

Now to your Question.

Forget your with absquery4 do begin, there is an easier way.
With only 2 new lines of code added to your existing code.

• Expand your price list to all the extras you need.
• e.g. DBLMSExtraBed : 'Price:15,00`.
• Add a new Button Caption:='Add Extras`.
• clickEvent pointing to your CalculationButtonClick.
• Test in CalculationButtonClick from wich button the event is fired.
• Fill out ROOM_TYPE TEditfield with DBLMSExtraBed.
• Add following WITH .. IF .. THEN

...

with sender As TButton do if name='DoCalc'  then begin

above

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

and an end;

end;

If the Event is fired from AddExtrasButton . It will not clear your Temp-Table.
So the Table is still open and the new Data will be inserted.
Now you have 3 Rows, last is :

With the AddExtrasButton you can add as much extras you like, as long there are extras in the Pricelist.

Used Table:

CREATE DATABASE IF NOT EXISTS pricelist;
USE pricelist;

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;


/*!40000 ALTER TABLE `room_rates` DISABLE KEYS */;
INSERT INTO `room_rates` (`ID`,`CENIK_ID`,`ROOM_TYPE`,`RATE_START_DATE`,`RATE_END_DATE`,`RATE_PRICE`) VALUES 
 (1,1,'DBLMS','2013-01-02 00:00:00','2013-04-26 00:00:00','128.00'),
 (2,1,'DBLMS','2013-10-22 00:00:00','2013-12-18 00:00:00','128.00'),
 (3,1,'DBLMS','2013-04-26 00:00:00','2013-06-22 00:00:00','146.00'),
 (4,1,'DBLMS','2013-09-21 00:00:00','2013-10-20 00:00:00','146.00'),
 (5,1,'DBLMSExtraBed','2013-01-02 00:00:00','2013-06-22 00:00:00','15.00');

Update :

Only a Tipp:

Create in your Temp Table a Field EXTRAS.

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
var
 isExtra : Boolean;
[...]


with sender As TButton do if name='AdvGlowButton1' then 
     isExtra := False else 
     isExtra := True;

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

[...]

if isExtra then 
   ABSQuery2.FieldByName('EXTRAS').AsString:=mem_ROOM_TYPE 
   else
   ABSQuery2.FieldByName('ROOM_TYPE').AsString:=mem_ROOM_TYPE;