The Conversion: I see U/IX - Part 2

Dates. I hate dates. I haven't quite figured out how to be proficient with dates moving from system to system, especially when the user has to enter them. For this project, four dates and times need be stored: outbound departure and arrival; and inbound departure and arrival. To achieve the data entry aspect of this, I am going to utilize the date and time pickers in AngularStrap.

[The Next Day]

Did I mention I hate dates. The UI is a bit more challenging that I thought it would be. There are 8 inputs and six labels. If I remember correctly, I had the same problem with the Flex-based system as well. After mulling it around it wound up looking not too bad. This is the before:

Not too bad - functional in the least.

[The Next Next Day]

The new semester has started and I've been putting small fires out here and there. But now it's time to get this project back on track!

At the moment, this is the after:

This looks a little less cluttered.  The user will select a date from a calendar. The time is selected via the AngularStrap timepicker. While I find that control a little bit confusing at first, I think users will get use to it. I am not overly concerned because this is not an application the typical user will be in every day.

The next step is to write the CFC function that is going to get the departure and return information. These data elements, as you may recall if you've been following along this series, are stored in a vertical table where a field stop_type informs the row as to whether it's a departure / return / campus / destination record. So following that idea, writing the getTripStops function naturally materializes into the following:

 <cffunction name="getTripStops" returnformat="json" returntype="array">  
   <cfargument name="_id" required="yes" type="string">  
   <cfquery name="_qStops" datasource="#_ds#">  
     select *  
     from  trip_stops  
     where  trip_id = <cfqueryparam value="#_id#" cfsqltype="cf_sql_integer">  
   </cfquery>  
   <cfset _stops = arrayNew(1)>  
   <cfloop query="_qStops">  
     <cfset _stop = structNew()>  
     <cfset _stop.date = dateformat(stop_datetime,'YYYY-MM-DD')>  
     <cfset _stop.time = timeformat(stop_datetime,'hh:nn tt')>  
     <cfset _stop.stopType = stop_type>  
     <cfset _stop.location = location>  
     <cfset arrayAppend(_stops,_stop)>  
   </cfloop>  
   <cfreturn _stops>  
 </cffunction>  

This output is not consistent with the above design to capture return and departure dates and times. The trip_stops table also can store a location of the stop. This is a case where the database design doesn't match up with the application design.

Originally, when the application was developed, it was determined that Campus Departure and Campus Return dates and times to and from the destination were all that was needed. But I try to anticipate the needs of the application owners and the users. Normally, we could have just created 4 fields in the database table and called it a day. I prefer to create a product that has some inherent potential, even if the owner or user doesn't see it, or the UI doesn't immediately leverage the additional data points.

Since this is essentially a new application, I am going to allow users to enter any stop they want to as well as require the four original stops. This will add some value to the application. So that means a change to the UI because we are enhancing the UX. The data will be displayed in a table in date-time order.

The result is the following:

This UI is very simple. The user can add and delete stops. The Add Trip Stop element will not appear until the Trip Stops edit icon is clicked. This way the screen is not cluttered, just like the trip details, with items that are not used all the time. Generally, once the user has entered dates, they are not likely to go back and change it.

When a stop is entered or deleted, only the stops are refreshed on the page. For example, the removeTripStop function in CF is the following:

 <cffunction name="removeTripStop" returnformat="json" returntype="array" access="remote">  
   <cfargument name="_tripId" required="yes" type="string">  
   <cfargument name="_stopId" required="yes" type="string">  
   <cfquery datasource="#_ds#">  
     delete from trip_stops where trip_stop_id = <cfqueryparam value="#_stopId#" cfsqltype="cf_sql_integer">  
   </cfquery>  
   <cfreturn getTripStops(_tripId)>  
 </cffunction>  

After the stop is deleted, the stops are returned back to the trip.js controller via the dataService.

 $scope.deleteTripStop = function(_stop){  
     dataService.call('removeTripStop&_tripId=' + $stateParams.id + '&_stopId=' + _stop.ID).then(function(data){  
       $scope.trip.STOPS = data.data;  
     })  
   }  

Only the $scope.trip.STOPS are refreshed. Technically, I could return the whole trip again. But since only one part can be edited at a time, it makes sense to only return back the aspect that was updated.

How the Dates and Times are Handled

I don't like messing around with dates. With that in mind, my approach here is extremely blunt with little nuance. Both the AngularStrap date and time controls allow you to specify the input as a variety of data types. In this particular case, I chose string with a very specific format for both date and time. That way, there is not ambiguity to what is being sent to the database. String works because there is not going to be any calculation or comparison regarding the stop entries other than ensuring all four are entered.

 <cffunction name="saveTripStop" returnformat="json" returntype="array" access="remote">  
   <cfset _params = getHttpRequestData()>  
   <cfset _content = deserializeJSON(_params.content)>  
   <cfset _tripStop = deserializeJSON(_content.objectData)>  
 
   <cfquery datasource="#_ds#">  
     insert  
     into  trip_stops  
     (    trip_stop_id,  
         trip_id,  
         location,  
         stop_type,  
         stop_datetime,  
         last_update,  
         last_update_pidm  
     )  values (  
         trip_stops_seq.nextval,  
         <cfqueryparam value="#_tripStop.tripId#" cfsqltype="cf_sql_integer">,  
         <cfqueryparam value="#_tripStop.location#" cfsqltype="cf_sql_varchar">,  
         <cfqueryparam value="#_tripStop.type#" cfsqltype="cf_sql_varchar">,  
         to_date('#_tripStop.date# #_tripStop.time#','MM/DD/YYYY HH:MI AM'),  
         sysdate,  
         <cfqueryparam value="#session.intUserPIDM#" cfsqltype="cf_sql_integer">  
     )  
   </cfquery>  
   <cfreturn getTripStops(_tripStop.tripId)>  
 </cffunction>  

As you can see in the saveTripStop() function, the trip date and time is put into the to_date() Oracle function. Of course there needs to be additional validation of the values, but that will happen prior to being rolled out to the end user for testing.

Okay. For some reason this post took a long time from start to finish and I'm over it. Part 3 will focus on the trip participants and auxiliary details of the trip. Cheers!