calendar, reservation system

justbradford - June 30, 2009 - 22:59

This may be by nature a fairly general question; but this is definitely an area I need to "measure twice, cut once," and get the planning right before taking off on it.

I need to design a calendar/entry form system for booking our video services. The important criteria I can think of are;

  • Reservation form accessible by authenticated users (both clients and us)...so clients can request a booking, and we can edit
  • fields would include job#, date, time, address, job name, client name, company, billing address, etc.
  • we would receive an email notification to evaluate the request and assign a videographer if we have one available
  • resulting complete calendar only visible to us (e.g., admin and certain in-house roles); but clients able to view a filtered calendar of just their own bookings
  • resulting calendar will show client, videographer, job# and job name (we need to also allow a variation to just show [videographer], "busy" so we know when a certain videographer is booked for an external job)
  • automatically assign a sequential job# once job has been confirmed (**this might be the toughest one for me)
  • client would be able to take advantage of autocomplete or a dropdown widget to quickly re-enter info entered on previous reservations, could edit any mistakes previously entered...some default info might initially be pulled from their user profile info (phone, email, etc.)
  • conditional colors on calendar items to denote status (tentative, all information complete, confirmed)
  • email a confirmation request the day before for clients and videographers, preferably with a link for them to click to confirm "the gig is still on for tomorrow" and asking them to verify or correct/update the time/location, etc. (after which the calendar item finally changes to a "green" color indication)
  • this reservation form might be expanded later into a "phone Q/A script" for a booking person to operate from, which might branch into other nodes for other of our services
  • for *super* bonus points, the relevant client:job info would be able to be imported into QuickBooks Premier or Enterprise

Whew! I think that's the gist of it...if anyone can point me toward the right modules/strategies, I would be grateful...we really need this to streamline the workflow and eliminate errors. I have *some* limited experience with CCK, Views, and the Calendar module, but need to dust off the cobwebs. I have looked at the Webform module documentation also...not sure where to start. Hopefully the above "problem statement(s)" will help someone point me in the right direction. The first thing might be: CCK, Webform, or other?

Thanks so much!

Easy Peasey, mostly

Cayenne - July 1, 2009 - 01:27

For the sign-up: Webforms
For the calendar and dates, Calendar and Dates (plus CCK, I think). Views and CCK field permissions will provide for all the filtering you need.
Sequential job number. Umm. you may want to do that by hand, or you can create a "job ticket" node (with date enablement for the calendar) and create some custom PHP to add a number. Or just use the node ID.

Autocomplete on webforms can be done easily from the user's profile, once the profile is completed

Calendar colors, maybe. Other stuff, I don't know. Surely doable, but for how much effort?

---------------------

"He's said to be outspoken, but nobody's actually seen anyone do it"

One more thing

Cayenne - July 1, 2009 - 01:29

If you don't mind creating a lot of nodes, you can let your customers create CCK nodes that have all the data in them and then you can use workflow editing or a private field to indicate whether you accept the transaction or not. I suggest using Mollom to cut down on the spam.

---------------------

"He's said to be outspoken, but nobody's actually seen anyone do it"

Thanks!

justbradford - July 1, 2009 - 01:55

Webform sounds like the deal, thanks! Having trouble right now even getting a manual Job# field (first addl field I've created) to show up when I go to create content with the new Video Event content type.

Do you know of any way to have some sort of database autocomplete or dropdown implementation so that if the user enters a previously entered "project name" it will autofill all the relevant fields with the appropriate previously entered info for the project (so no need to keep re-entering the same info for separate video shoots on the same project...the job#, date, time, and maybe address would change, but the billing address, project name, and various other fields will be the same...)

Thanks again!

So far...

justbradford - July 1, 2009 - 03:00

I have a feeling that Webform (maybe plus an autocomplete module) may help with the autocomplete feature I was thinking of. Looks like Webform may store the submission values in a (new?) table in the main mysql database?

Error msg below...I keep getting this when I add a webform component, although it does add the component regardless:

user warning: Access denied for user 'Bradford'@'localhost' to database 'ELITtestSITE' query: LOCK TABLES webform_component WRITE in /Applications/MAMP/htdocs/ELITtestSITE-drupal-6.12/includes/database.mysqli.inc on line 330.

I am not sure that a regular

Cayenne - July 1, 2009 - 03:18

I am not sure that a regular Autocomplete module will work with webform. I had planned on using Javascript for a similar goal.

Umm. not sure why that error is coming up. SOmetimes things like that go away after running Update.

But yes, it stores the stuff in a series of tables on mySQL with easy download as csv.

---------------------

"He's said to be outspoken, but nobody's actually seen anyone do it"

Ah....

justbradford - July 1, 2009 - 04:08

Thanks, I'll try update... I've got a nice sample webform that is only available to the appropriate roles...now I just need to figure how to access/address/retrieve the data, and how to display it on the calendar view...no luck with that yet. It did take me a lot of poking around to get that to work on my music site, www.justbradford.com ...but that involved creating an Event content type, vs. webform...

Can this work?

mattyoung - July 1, 2009 - 06:10

Interesting

justbradford - July 1, 2009 - 15:15

Wow, very interesting, thanks! If nothing else, that may well help us track our gear inventory. I may have to look at approaching this via CCK; but I haven't quite sussed out how to "retrieve" and manipulate the database items created via Webform.

One thing that seems necessary is being able to have a key attribute "project name" in the database table that can pull up all the default field values for new "job tickets" for that ongoing project...so the user can accept or edit them for this job...ah, heck, that opens another can of worms if different tickets under the same project may have slightly different billing info, different "attn to:", etc...yikes.

Measure twice, cut once, indeed.

Check out this brand new

mattyoung - July 16, 2009 - 00:19

Check out this brand new module: http://drupal.org/project/simple_reservation

Cool, thanks!

justbradford - July 31, 2009 - 04:19

Cool, thanks!

 
 

Drupal is a registered trademark of Dries Buytaert.