Hello

I am working with a new project involving a TMS DBPlanner, and I have the normal facilities working with a query that loads the data for the day or days being displayed, in columns that are linked to the resources. However, I would like to be able to show background items that illustrate when that resource is unavailable.

That is easy, as long as the data stays in the table that holds the time slots that are entered, but what if they know that resource X is always unavailable on a Friday morning? Rather than have the user enter the unavailable records for every Friday of the year (and beyond) I would like to be able to look up a table that will return the records that apply to the day or days being displayed.

So the Bookings table holds all the booking data, StartTimeStamp, EndTimeStamp, the ResourceID to put the records in the correct column, and the type of booking, an integer. The NotAvailable table holds the DayOfWeek integer, the start Time, the end Time, and an integer for the type of exclusion.

Is there a way to query these very different tables, or should I use an in-memory table (or a ClientDataSet), and run through that putting in the not-available time slots, before displaying the day? I should also consider annual holidays in the mix too. Hoo boy!!

Regards & TIA

Peter

Peter


What you're looking for should be achievable with a join, but before anyone can even guess at what the sql should look like we need the table structures and the structure for the result set you use to drive the DBPlanner

Roy Lambert [Team Elevate]

Hi Peter,

Quick question are you blocking all resources for the period OR only
certain resources for the period?

Cheers,

Phil.


On 4/11/2012 9:24 PM, Peter wrote:
> Hello
>
> I am working with a new project involving a TMS DBPlanner, and I have the normal facilities working with a query that loads the data for the day or days being displayed, in columns that are linked to the resources. However, I would like to be able to show background items that illustrate when that resource is unavailable.
>
> That is easy, as long as the data stays in the table that holds the time slots that are entered, but what if they know that resource X is always unavailable on a Friday morning? Rather than have the user enter the unavailable records for every Friday of the year (and beyond) I would like to be able to look up a table that will return the records that apply to the day or days being displayed.
>
> So the Bookings table holds all the booking data, StartTimeStamp, EndTimeStamp, the ResourceID to put the records in the correct column, and the type of booking, an integer. The NotAvailable table holds the DayOfWeek integer, the start Time, the end Time, and an integer for the type of exclusion.
>
> Is there a way to query these very different tables, or should I use an in-memory table (or a ClientDataSet), and run through that putting in the not-available time slots, before displaying the day? I should also consider annual holidays in the mix too. Hoo boy!!
>
> Regards & TIA
>
> Peter
>

Roy: the tables follow. Phil: Only certain times for any of the resources. For instance resource A could be unavailable Monday morning from 9:00 until 11, and again 2 to 3, then againThursday for a few hours in the morning. Resource B might also be unavailable Tues afternoon and Wednesday morning etc. There will also be date ranges when one or all of the resources are unavailable - holidays and so on.

CREATE TABLE "Booking"
(
"BookingID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
" StartTimeStamp " TIMESTAMP,
" EndTimeStamp " TIMESTAMP,
"LocationID" INTEGER,
"ResourceID" INTEGER,
"EventID" INTEGER,
CONSTRAINT "KeyBookingIDInx" PRIMARY KEY ("BookingID")
)
VERSION 1.00 etc…
CREATE TABLE " NotAval"
(
" NotAvalID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ResourceID" INTEGER,
"EventID" INTEGER,
"DOfW" INTEGER,
"TimeStart" TIME,
"TimeEnd" TIME,
CONSTRAINT " NotAvalID _PrimaryKey" PRIMARY KEY ("NotAvalID ")
)
VERSION 1.00 etc

Confused? I am

Regards

Peter

> Phil: Only certain times for any of the resources. For instance resource A could be unavailable Monday morning from 9:00 until 11, and again 2 to 3, then againThursday for a few hours in the morning. Resource B might also be unavailable Tues afternoon and Wednesday morning etc. There will also be date ranges when one or all of the resources are unavailable - holidays and so on.

Peter, if that's the case, I'd also inquire could those periods change
over time, say in 1 years time they may want other periods blocked off
and will they want to then go back in history on the DBPlanner and see
those blocked off sections as they where at the time...

If all the above is true, I'd consider using the SAME table as the
normal items and flagging them with a boolean field say 'bgitem' which
you can then make use of in the DBDBPlanners ItemSource Event
"OnFieldsToItem" and if true make the item FIXED in both position and
size OR just use the read only (however you want to work it).

Item->FixedPos = true;
Item->FixedPosition = true;
Item->FixedSize = true;
Item->ReadOnly

To cover your other need where the user of course wont want to enter
100's of background items for the same period each week, simply create a
form that lets them create background items for a particular resource,
say Every Tuesday from 9am to 11am from the next X weeks or however you
want it to happen... Then have it create the records and just refresh
the DBPlanner.

This is basically similar to what I've done and it's much easier keeping
all the items in one table and they are easily distinguishable using a
status flag or the bgitem bool I spoke of, infact however you prefer to
do it.

I don't know if that's best for your situation, but hopefully it may help,

Cheers,

Phil.

Peter


Its a while since I used the DBPlanner but my suggestion (taking into account Phil's comments as well) would be to run a bit of SQL at start of day to create events for the people which can then be displayed eg

INSERT INTO BOOKING
SELECT
0, <<<<<<<<<< booking ID
CAST(CAST(CURRENT_DATE AS VARCHAR(10)) + ' '+  CAST(TimeStart AS VARCHAR(5)) AS TIMESTAMP),
CAST(CAST(CURRENT_DATE AS VARCHAR(10)) + ' '+  CAST(TimeEnd AS VARCHAR(5)) AS TIMESTAMP),
0, <<<<<<<<< location
ResourceID,
EventID
FROM NotAval
WHERE
EXTRACT(DAYOFWEEK, CURRENT_DATE) = DofW


If you set a booking ID and location up of UNAVAILABLE then it should work

Roy Lambert [Team Elevate]

Thanks for the feedback. Phil, yes, the periods of non-available-ness could be altered often, and I hadn't considered the scenario where they might want to review the background items exactly as they appeared on the day. Good point.

I have done that, but I am still loathe to pre-populate the table x months in advance, so I add the new background records on the fly, as the user scrolls to the day. I have it look at the NotAval table for the records for that DayOfWeek, looping through each record in turn, see if there is already a record in Booking, and if not, add that background record. It is all very fast, and I used elements of the SQL that Roy posted to build a query string.

It would be nice if there were functions that returned the integer and fraction parts of a TIMESTAMP, but that can be done in Delphi.

Thanks for your help

Regards

Peter