[SOLVED] Fullcalendar event submit data validation

[shortbow]

Another day, another challenge
More than anything, I seeking a confirmation before I start changing anything

I have a fullcalender where I aim to schedule equipment rentals. eache rental can be one or more days, and I can have several rentals in the same day with different types of equipment, or the same type, depending what I have in stock each day.

I have several types of equipment with different quantitys in stock. So I have to check their availability before submitting the rental\event.
I have to mysql tables.
event (event_id, equipment_id, equipment_type_id, ... )
equipment_type (type_id, type, quantity, description, ....)
conectet by equipment_type_id ->type_id

Ideally I would like to have a dropdown\databasejoin of the types of equipment available for the date interval loaded by default with the form creation and when\if those dates are changed by the user, the types of equipment available are updated in the dropdown.

From what I've tried and researched I'm thinking the best (maybe only way) for me to do this would be to have a dropdown element (not a databasejoin) populated by some query, and in the date elements get some javascript code for the event "change" to somehow update the dropdown..

my thanks for any tips

 

[cheesegrits]

It's not entirely clear from your description, but I presume you need to filter your list of available equipment by checking to see what equipment is not already booked for the selected start/end dates in the event.

See this thread for a discussion about doing this:

http://fabrikar.com/forums/index.ph...-comparaison-between-dates.45585/#post-235267

So yes, it is possible to apply a WHERE filter to your equipment join element which will be dynamic (so it changes as they select different dates), and then do a validation on the submission.

Definitely do NOT try and use a dropdown. Everything you need is in the join element, including the AJAX updating.

-- hugh

 

[shortbow]

Sorry, I tried but is a bit confusing even for me.
I'm trying to filter by the date AND the number of available equipment.

I have an event\rentals table with the dates and a "equipment type" table that have the number of equipments available per type.
I'm making a databasejoin of those 2 tables using the equipment_type field.
That means I have to make a count of the number of events in a given date by type of equipment:

select count(*)
from j_marc jm, j_type_equip jte
where jm.equip_typo = jte.typo_id
and (dstart<= '2017-11-01' and dEnd >= '2017-11-01')
group by jte.type_id

and then would have to use the result for each type do compare with the total number of machines
something like:

select type
from j_marc jm, j_type_equip jte
where jm.equip_typo = jte.typo_id
and jte.num_equip <
(select count(*)
from j_marc jm, j_type_equip jte
where jm.equip_typo = jte.typo_id
and (dstart<= '2017-11-01' and dEnd >= '2017-11-01')
group by jte.type_id )

and that gives me back an error because the subquery is returning more than on value.
I think I would need a recursive query.. something I really don't like doing...

Another option for me would be have a fullcalendar with all the events of all types of equipment, but would only be possible to edit events, not create new ones, and a fullCalendar for each type of equipment (they are no so many really) where the user could create new events or edit existing ones, and the days already fully booked would be somehow visually disabled (greyed-out) somehow...

 

[shortbow]

Hi...

I think I've got a possible solution... but not a very elegant but... more like a "bigger hammer", if anyone
I'm going to create a new table with:
start_date
end_date
equip_type,
number_equip_booked
max number

it will have a record for each start_date/end_date/equip_type "group".
Each time a new event is added or edited this table will be updated...

In the calendar I will make a databasejoin with this table and will be able to make a "where" to only compare between the number_equip_booked <= max number...

 

[cheesegrits]

I really wouldn't go that route. Much better to figure out the query to count the available equipment on the fly. Maintaining a running count is a pain. Seriously. Don't do it.

The first answer on this Stack Overflow question gives you pretty much the exact query you need, in terms of rental properties with multiple rooms.

https://stackoverflow.com/questions...y-to-search-for-availability-of-multiple-rows

the @checkin and @checkout would bre replaced by placeholders for your start and end dates, like '{yourtable___start_date}', and the property and bookings tables and field names with your equipment and event tables and fields. Select * rather than id in the first select, and wrap the whole thing in the "WHERE {thistable}.id IN (...)".

-- hugh

 

[shortbow]

Hugh you are my hero!

I have now idea how you found that post, because I've searched there too and didn't found anything applying to my problem.

For future reference, it's working like a charm with some slights teawks...

the query ended like this :
SELECT *
FROM joo_type_equip jte
WHERE type_id NOT IN (
-- List all properties sold-out on any day in range
SELECT DISTINCT Z.equip_type
FROM (
-- List sold-out properties by date
SELECT jm.equip_type, jte.num_maqs,COUNT(*)
FROM j_marc jm
INNER JOIN j_type_equip jte on jm.equip_type = jte.type_id
WHERE jm.dStart >= '{j_marc___dStart}' AND jm.dEnd <= '{j_marc___dEnd}'
GROUP BY jm.equip_type, jte.num_maqs
HAVING jte.num_maqs - COUNT(*) <= 0
) as Z
)

and I had to set this in the element so it updates on changes:


thanks!

 

[cheesegrits]

My Google Fu is strong.




Sent from my HTC6545LVW using Tapatalk