Jack_Walter
Posts: 39
|
| Posted: 08/21/2008, 3:44 PM |
|
Hi,
I think I should begin with what I have...
4 important tables (there a more but not needed in this case)
ship (ship_id, ship_name)
tour (tour_id, ship_id, tour_name, tour_departure_date, {more entries})
cabin (cabin_id, ship_id, cabin_number, deck_id, side_id)
reservation (reservation_id, ship_id, tour_id, cabin_id, {more entries})
The ship has a 1:n relationship to cabin so there is one "cabinset" per ship.
In this case the cabin table has 90 entries (if a new ship is bought additional
entries for the second cabinset are created (manually))
.
E.g.:
1, 1, 101, 1, 1
2, 1, 102, 1, 2
3, 1, 103, 1, 1
...
90,1, 317, 3, 1
When somebody creates a new tour he enters some values for the tour itself (tour_name, tour_departure_date, tour_note, etc.) and when he clicks
on Insert I'd like to perform the additional task, that all cabins from the cabinset assigned to this ship get an entry for the new tour in the reservation table... reservation_id is the primary, autoincrement key, ship_id is selected via a drop down field.
reservation table should look like this after clicking on the insert field:
reservation (reservation_id, ship_id, tour_id, cabin_id, {more entries})
1, ship_id, tour_id, (first cabin id for this ship)
2, ship_id, tour_id, (second cabin id for this ship)
...
90, ship_id, tour_id, (last cabin id for this ship)
The new creation of the second tour would lead to this:
91, ship_id, tour_id+1, (first cabin id for this ship)
92, ship_id, tour_id+1, (second cabin id for this ship)
...
180, ship_id, tour_id+1, (last cabin id for this ship)
and so on...
How can I accomplish this with a click on the Insert button when a new tour is created? I'm not a programmer (that's why I'm trying to use CodeCharge), so a good explation would be very helpful...
If there are any questions or if you need more information (tables in full detail or anything else) don't hesitate to ask!
Tia,
Jack
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 08/23/2008, 3:54 PM |
|
It is almost as if you are specialy asking for my Assistance.........
You: Jack _ Me:Walter <-> Jack_Walter
So how could I refuse.
As I understand it, you are reffering to InsertButton on the Tour RecordForm.
A Recordform with textboxes on it for tour_name, tour_departure_date, tour_note, etc.
Not to clear is if the dropdown 'ships' is also on this RecordForm, but I assume it is.
Is tour_id also an autoincremented primary key? I assume it is.
What do you want to do with editing existing tours?
Do you allow change of ship_id, choosing another ship or will you disable that in editmode?
Well these are my questions for now.
Meanwhile you can read this:
We are going to do this through a function, which we call 'CreateReservation'.
Open your Page in Design, in its Properties, choose the Event Tab.
Rightclick the AfterInitialize event and choose Add Code, this will open CodeView for your page.
between the
// -------------------------
// Write your own code here.
// -------------------------
and
//End Custom Code
type:
function CreateReservation($ship_id, $tour_id ) {
// creating reservations based on tour/ship
}
Switch back to Design View.
Save.
Nothing has been done yet, but you could post back in such a way that I can find out what you do or don't know.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Jack_Walter
Posts: 39
|
| Posted: 08/23/2008, 8:31 PM |
|
Quote :It is almost as if you are specialy asking for my Assistance.........
You: Jack _ Me:Walter <-> Jack_Walter
So how could I refuse...
*g*
Quote :As I understand it, you are reffering to InsertButton on the Tour RecordForm.
A Recordform with textboxes on it for tour_name, tour_departure_date, tour_note, etc.
Yes, exactly.
Quote :Not to clear is if the dropdown 'ships' is also on this RecordForm, but I assume it is.
Yes, it is. Each tour does only belong to _one_ ship and if a new tour is created it is associated with it's ship (therefore the 'tour.ship_id')
Quote :Is tour_id also an autoincremented primary key? I assume it is.
Yes, every {table}.{table}_id in the db is a primary, autoincremented key.
Quote :What do you want to do with editing existing tours?
Do you allow change of ship_id, choosing another ship or will you disable that in editmode?
Because every tour does belong only to one ship I'll have to make this drop down field selectable when a tour is created but after creation it should be visible but not alterable any more. The reason is simple. Let's say we have 2 ships and therefore 2 'cabinsets'. The first cabinset has 90 cabins, the second one e.g. 110 (because the second ship is a bit larger). The cabin table assigns each cabin to it's ship with the help of the ship_id key. If we create a new tour and select the first ship the click on the Insert button would create 90 entries in the reservation table for this ship. If it'd be possible to change the ship afterwards on the tour edit page (e.g. to the second ship) it wouldn't have the correct assigments any more and more obiously the edit page for the reservation would show 90 cabins and not the 110 ones... The drawback ist that the user that creates a new tour has to take care of selecting the correct ship but I think this is unavoidable?
Quote :We are going to do this through a function, which we call 'CreateReservation'.
Ok, did that (reading, editing the event, saving :)
I'll add two screenshots.
The first image shows creating a new tour where the button 'Neuer Datensatz' (<- this is the insert button) is the one that should create the tour _and_ the additional entries in the reservation table. The 'Schiff' drop-down field on the top of the grid is available at this stage.
http://www.bildercache.de/anzeige/20080824-052356-950.jpg
The second image shows the tour details where the 'Neuer Datensatz' is not available (the tour was created in the image before and this is only the edit of the existing tour) and the 'Schiff' is visible but not editable any more.
http://www.bildercache.de/anzeige/20080824-052516-972.jpg
Thanks for your time and patience Walter!
Regards,
Jack
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/24/2008, 9:52 AM |
|
Ok, here goes:
First the code for the function: (alter the one we had earlier)
function CreateReservation($tour_id, $ship_id) {
// creating reservations based on tour/ship
// $tour_id is tour_id as passed by caller
// $ship_id is ship_id as passed by caller
// <connectionname you have to replace with your Connectionname as used in Project Settings->Connections.
$db = new clsDB<connectionname>; // create a new database connection to be read from
$sql ='Select cabin_id from cabin where ship_id=' . $db->ToSQL($ship_id, ccsInteger);
$dbw = new clsDB<connectionname>; // and another database connection to be written into
$db->query($sql);
while ( $db->next_record() ) { // as long as read connection has rows to process
$sqlw='insert into reservation (tour_id, ship_id, cabin_id) VALUES (';
$sqlw .= $dbw->ToSQL($tour_id, ccsInteger);
$sqlw .= ', ' . $dbw->ToSQL($ship_id, ccsInteger);
$sqlw .= ', ' . $db->ToSQL($db1->f('cabin_id'), ccsInteger);
$sqlw .= ')';
$dbw->query($sqlw); // execute the constructed SQL
}
$dbw->close(); // close the connection(s)
$db->close();
}
Save and Close your page.
Next:
Now that we have the function we need to tell the RecordForm to execute it when a new Tour is Added.
The common problem with an Insert based on autonumkey is how to retreive the new tour_id.
So we handle that first:
Open your Page and click the recordform, Properties EventTab, rightclick the AfterInsert, AddAction: DLookup.
Expression: 'last_insert_id()'
Domain: ''
Criteria: ''
Connection: Select it from the dropdownlist <connectionname>
Convert resutl to: Integer (from the list)
Type of Target: Variable (from the list)
Target: tour_id
This is by far the easiest way to retreive the last inserted id.
Select the Recordform, Properties, EventTab, rightclick the AfterInsert again but this time we AddAction: 'Call Function' !below the DLookup!
Function Name: CreateReservation
Class: leave blank
Parameter 1: $tour_id (the one we declared in the DLookup earlier
Parameter 2: $Component->Ship_id->GetValue()
leave the rest blank
Click the Page, Save it and Publish(F9).
Now test the Tour addnew, it shoudl add all the cabins in the cabin table for that ship_id.
Please test and report back any difficulties you may have, however this is all tested.
Walter
http://donate.consultair.eu
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Jack_Walter
Posts: 39
|
| Posted: 08/24/2008, 10:41 AM |
|
Added everything but I get this error when I click on the insert button:
Fatal error: Class 'clsConnectToDB' not found in W:\www\cruiser\tourEdit_events.php on line 76
The dbname that I use (ConnectToDB) is the one that I use in Project settings -> Connections -> Connection name: ConnectToDB
Screenshot for the Create Reservation function:
http://www.bildercache.de/anzeige/20080824-194100-338.jpg
Screenshot for DLookup entries:
http://www.bildercache.de/anzeige/20080824-193625-207.jpg
Screenshot for Call Function:
http://www.bildercache.de/anzeige/20080824-193759-700.jpg
Regards,
Jack
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/24/2008, 11:45 AM |
|
Jack it is:
$db= new clsDB<connectionname>
So:
$db= new clsDBConnectToDB;
$dbw= new clsDBConnectToDB;
Go on, do and test.
I also have a variant in which I can securely do the Update and the Delete.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Jack_Walter
Posts: 39
|
| Posted: 08/24/2008, 12:08 PM |
|
Quote :$db= new clsDB<connectionname>
So:
$db= new clsDBConnectToDB;
$dbw= new clsDBConnectToDB;
Ah, I see :)
I had to change the following variables as well:
$db2 -> $dbw
$db1 -> $db
$sqlw .= $dbw->ToSQL($tour_id, ccsInteger);
$sqlw .= ', ' . $dbw->ToSQL($ship_id, ccsInteger);
$sqlw .= ', ' . $db->ToSQL($db->f('cabin_id'), ccsInteger);
And... tadaaaa. it works! Just... wow!
Quote :I also have a variant in which I can securely do the Update and the Delete.
That would be a great feature!
Regards,
Jack
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/24/2008, 12:20 PM |
|
Edited the previous post to reflect that.
I had been writing it in a separate file, and somehow I copied the wrong part into that small textarea the forum offers me on this machine.
The variant would globaly do this:
If Delete is pressed it will delete the tour record (it already does) call the same routine (slightly adjusted) and delete the cabinset for that tour.
if Submit is pressed (update of tourrecord) the tour record will be updated (also already so) call the same routine (slightly altered) delete the cabinset for that tour and then (suprise) do the insert!
LOL
You however have to cater for stopping update/delete when a booking is already made in that tour/cabinset (which I imagine to be the objective of the application.
Well at least for now, we are where you wanted to be.
Have fun, und ein schönes Sonntag.
(Pls change title to [Resolved]......)
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Jack_Walter
Posts: 39
|
| Posted: 08/24/2008, 12:45 PM |
|
Quote :Well at least for now, we are where you wanted to be.
Yes and it's the most important thing right now :)
Quote :Have fun, und ein schönes Sonntag.
Danke, dir auch *g*
Thanks again for your time and effort Walter!
Regards,
Jack
|
|
|
|
|
