SQL Multiple table constraint

I've always wanted to know how to do a constraint across tables and someone on StackOverflow did too apparently. Here's the link:

How do I create a multiple-table check constraint?t

CF to .NET: request and session scopes

Coldfusion has a request and session scope and I was determined to find the same in .NET. After some googling I stumbled across this:

HttpContext.Current.Session["Username"] = username;

Using "HttpContext.Current.Session" and "HttpContext.Current.Request" I was able to start a session for use once they logged in. Also I could now store things the request scope which I don't really need because .NET's Viewbag handle most of what I needed for that. In the example above I am storing the username after a login into the session. I am sure there is a more proper way of doing it, but for basic concepts this worked.

Deleting Duplicate Bridge Records

When I was importing some data I mistakenly dup'd records into a bridge (many to many) table. I wanted to delete all but the first occurrence of the relationships for the comibination of ids (snowBoarderID,snowBoardID) and made use of the ROW_NUMBER() over in SQL again.

with data as(
 select
  snowBoarderID,
  snowBoardID,
  ROW_NUMBER() over (partition by snowBoarderID,snowBoardID by SnowboarderToSnowboardID asc ) as rownumber
 from SnowboarderToSnowboard
 where isActive = 1
)
select * 
--delete
from data
where rownumber > 1

SQL MAX() RecordID ordered by date

I had a task where I needed to get the most recent timesheet record for task. A task can have many instances on the timesheet. I made use of the ROW_NUMBER() over SQL statement to first order the records by date. Then I got the record where the recordNumber was equal to 1.

select 
 tt.taskTimesheetID
from(
 select 
  ROW_NUMBER() over (partition by tt.taskID order by tt.startTime asc, tt.CreateDate desc ) as recordNumber
  ,tt.taskTimesheetID
  ,tt.taskID
 from tasktimesheet as tt
 where tt.isactive = 1
  and tt.taskID = 5254
   
 ) as data 
inner join taskTimesheet as tt on tt.taskTimesheetID = data.taskTimesheetID
 and data.recordNumber = 1

Full Calender JS - unique event id's

I was working with full calendar js dragging and dropping my tasks on to the calendar as events for a timesheet app and ran into a wierd issue. When I loaded up the available tasks to be dragged I set the event object's id key equal to the task's id as shown below.

//---load in all of task to be dragged onto the calendar
var loadTasks = function(){
      
 $('##external-events div.external-event').each(function() {
                
  var self = $(this);
  
  var eventObject = {
   title: $.trim(self.text())
   ,id: $.trim(self.data("taskid")) //---RIGHT HERE
   ,taskID:$.trim(self.data("taskid"))
  };
  
  self.data('eventObject', eventObject);
  
  self.draggable({
   zIndex: 999,
   revert: true, 
   revertDuration: 0
  });
 });

};

This will work if you don't drag the same task on the calendar twice. If you worked on a task from 8am to 9am and 1pm to 2pm if you grabbed the 8am to 9am event and attempting to resize it to 8am to 8.30am, full calendar js would move it and then it would also move the 1pm to 2pm event as well to 1pm to 1.30pm.

Every event object you put on the calendar should have a unique id.

If found this little snippet on the web that helped me make unique id's for event objects.

http://snipplr.com/view/2574/

var uid = (
 function(){
  var id=0;
  return function(){
   return id++ ;
   };
  }
)();

And on the "drop" variable of the full calendar js invoking I reset the event's id and rerendered the event.

, drop: function(date, allDay, jsEvent, ui) {   
         
 var originalEventObject = $(this).data('eventObject');
 var copiedEventObject = $.extend({}, originalEventObject);
 copiedEventObject.id = uid(); //---RIGHT HERE
 copiedEventObject.start = date;
 copiedEventObject.end = new Date(date).hours().add(-1);
 copiedEventObject._end = new Date(date).hours().add(-1);
 copiedEventObject.allDay = allDay;

 $('##calendar').fullCalendar('renderEvent', copiedEventObject, true);
 
 saveEvent($(this).data("taskid"),'','',copiedEventObject.start,copiedEventObject.end,'');
 
}

This fixed the issue.

Full Calendar JS

Using full calendar js I was able to make a timesheet app. The app involved dragging tasks from a left on to a day view of calendar. Simliar to this drag and drop events demo. I added trash can on the top of calendar to drag and drop events to be removed. Here is the js I used to make it happen. All of which is in document.ready. We will break down after this code snippet.

var loadTasks = function(){
      
 $('##external-events div.external-event').each(function() {
          
  var self = $(this);
  
  var eventObject = {
   title: $.trim(self.text())
   ,id: $.trim(self.data("taskid"))
   ,taskID:$.trim(self.data("taskid"))
  };
  
  self.data('eventObject', eventObject);
  
  self.draggable({
   zIndex: 999,
   revert: true, 
   revertDuration: 0
  });
 });

};

var saveEvent = function(taskID,orgStartObj,orgEndObj,newStartObj,newEndObj){
 
 var format = "yyyy-MM-dd HH:mm:ss";
 
 $.ajax({
  url:'/timesheet/saveEvent'
  ,type:'POST'
  ,data:{
   orginalStartTime:orgStartObj.toString(format)
   ,orginalEndTime:orgEndObj.toString(format)
   ,newStartTime:newStartObj.toString(format)
   ,newEndTime:newEndObj.toString(format)
   ,taskID:taskID
  }
  ,dataType:'json'
 });
 
};

//---Check if inside the trashcan div
var draggedOverTrashCan = function(draggedItem, dropArea) {    
 var itemOffset = draggedItem.offset;
 var trashCanOffset = $(dropArea).offset(); 

 itemOffset.right = $(draggedItem.helper).outerWidth() + itemOffset.left;
 itemOffset.bottom = $(draggedItem.helper).outerHeight() + itemOffset.top;
 
 trashCanOffset.right = $(dropArea).outerWidth() + trashCanOffset.left;
 trashCanOffset.bottom = $(dropArea).outerHeight() + trashCanOffset.top;
         
 // Compare
 if (itemOffset.right >= trashCanOffset.left
    && itemOffset.bottom >= trashCanOffset.top
    && itemOffset.top <= trashCanOffset.bottom
    && itemOffset.left <= trashCanOffset.right
 ){ 
     return true; 
 }else{
  return false;
 }
};


$('##calendar').fullCalendar({
 header: {left: 'prev,next today',center: 'title',right: 'month,agendaWeek,agendaDay'}
 , editable: true
 , firstHour: 6
 , slotMinutes: 15
 , defaultView: "agendaDay"
 , aspectRatio: "1.60"
 , year: #datePart("yyyy", now())#
 , month: #datePart("m", now())-1#
 , date: #datePart("d", now())#
 , events: #serializeJSON(viewBag.events)#
 , droppable: true
 , drop: function(date, allDay, jsEvent, ui) {   
  
 var originalEventObject = $(this).data('eventObject');
  var copiedEventObject = $.extend({}, originalEventObject);
  copiedEventObject.id = uid();
  copiedEventObject.start = date;
  copiedEventObject.end = new Date(date).hours().add(-1);
  copiedEventObject._end = new Date(date).hours().add(-1);
  copiedEventObject.allDay = allDay;

  $('##calendar').fullCalendar('renderEvent', copiedEventObject, true);
  
  saveEvent($(this).data("taskid"),'','',copiedEventObject.start,copiedEventObject.end,'');

  
 }
 , eventResize: function(event, dayDelta, minuteDelta, revertFunc, jsEvent, ui, view){
  
  var newStart = event.start;
  var newEnd = event.end;
  var orgStart = new Date(newStart);
  var orgEnd = new Date(newEnd).addMinutes(minuteDelta * -1);
  
  saveEvent(event.taskID,orgStart,orgEnd,newStart,newEnd);
  
 }
 , eventDrop: function(event, dayDelta, minuteDelta, allDay, revertFunc, jsEvent, ui, view ){
            
     var newStart = event.start;
  var newEnd = event.end;
  var orgStart = new Date(newStart).addDays(dayDelta * -1);
   orgStart.addMinutes(minuteDelta * -1);
  var orgEnd = new Date(newEnd).addDays(dayDelta * -1);
   orgEnd.addMinutes(minuteDelta * -1);
  
  saveEvent(event.taskID,orgStart,orgEnd,newStart,newEnd);
       
 }
 ,eventDragStop:function(event, jsEvent, ui, view){
  
  
  if (draggedOverTrashCan(ui, $('div##trash-can'))) {
           
       var format = "yyyy-MM-dd HH:mm:ss";
       
         $.ajax({
    url:'/timesheet/deleteEvent'
    ,type:'POST'
    ,data:{
     startTime:event.start.toString(format)
     ,endTime:event.end.toString(format)
     ,taskID:event.taskID
     ,userID:'#viewBag.userID#'
    }
    ,dataType:'json'
    ,success:function(){
     $("##calendar").fullCalendar('removeEvents', event._id);
    }
   });
   
         
     }     
 }
});

//--- add a trash can div to the top of the calendar    
$('##calendar').children('.fc-content').prepend('<div id="trash-can" style="border: 2px solid ##C1454B;padding:15px 100px 15px;border-radius:5px;background:##DD9094;text-align:center;">Drag Events Here To Remove</div>');

//---make the tasks available.
loadTasks();

loadTasks() - is used to make the tasks in the left nav drag and droppable for the calendar. The task are created in ColdFusion like this.

<div id="external-events">
 <ul>
  <cfloop query="viewBag.tasks">
   <li>
    <div class="external-event ui-draggable" data-taskID="#viewBag.tasks.taskID#">
     <div><strong>###viewBag.tasks.taskID# #viewBag.tasks.name#</strong></div>
     <div><strong>Status: </strong>#viewBag.tasks.taskStatusName#</div>
    </div>
   </li>
  </cfloop>
 </ul>
</div>

Then document.ready I do this:

var loadTasks = function(){
 
 //--- loop through each draggable task   
 $('##external-events div.external-event').each(function() {
          
  var self = $(this);
  
  //---set the event Object's title which will be seen on the calendar, set the id so that each event on the calendar is unique, and set any other values you want to be carried along with that event. in the case below taskID is an extra value I want. It will be used later to saveACalendarEvent.
  var eventObject = {
   title: $.trim(self.text())
   ,id: $.trim(self.data("taskid"))
   ,taskID:$.trim(self.data("taskid"))
  };
  
  self.data('eventObject', eventObject);
  
  //---make the task draggable
  self.draggable({
   zIndex: 999,
   revert: true, 
   revertDuration: 0
  });
 });

};

saveEvent() - is used to make an ajax call with the taskID, orginal start time, orginal end time, new start time, and new end time. I send the orginal and new dates so if I am resizing a task or moving the task on calendar I want to make sure I delete the old record.

var saveEvent = function(taskID,orgStartObj,orgEndObj,newStartObj,newEndObj){
 
 var format = "yyyy-MM-dd HH:mm:ss";
 
 $.ajax({
  url:'/timesheet/saveEvent'
  ,type:'POST'
  ,data:{
   orginalStartTime:orgStartObj.toString(format)
   ,orginalEndTime:orgEndObj.toString(format)
   ,newStartTime:newStartObj.toString(format)
   ,newEndTime:newEndObj.toString(format)
   ,taskID:taskID
  }
  ,dataType:'json'
 });
 
};

draggedOverTrashCan() - checks to see if the user drags a calendar event over the trash can. The trash can code gets prepended later in code.

var draggedOverTrashCan = function(draggedItem, dropArea) {    
 var itemOffset = draggedItem.offset;
 var trashCanOffset = $(dropArea).offset(); 

 itemOffset.right = $(draggedItem.helper).outerWidth() + itemOffset.left;
 itemOffset.bottom = $(draggedItem.helper).outerHeight() + itemOffset.top;
 
 trashCanOffset.right = $(dropArea).outerWidth() + trashCanOffset.left;
 trashCanOffset.bottom = $(dropArea).outerHeight() + trashCanOffset.top;
         
 // Compare
 if (itemOffset.right >= trashCanOffset.left
    && itemOffset.bottom >= trashCanOffset.top
    && itemOffset.top <= trashCanOffset.bottom
    && itemOffset.left <= trashCanOffset.right
 ){ 
     return true; 
 }else{
  return false;
 }
};

invoking the full calendar js
drop() - handles dragging new events from off the calendar onto the calendar.

eventResize(),eventDrop() - handles just that. Full calendar give you back the deltas from where the event used to be on the calendar. With those we can create the orginal date.

eventDragStop() - used when event has stopped being dragged. For my use case, I want to know if they drug the task over the tash can so I can do an ajax call and delete the task.

$('##calendar').fullCalendar({
 header: {left: 'prev,next today',center: 'title',right: 'month,agendaWeek,agendaDay'}
 , editable: true
 , firstHour: 6
 , slotMinutes: 15
 , defaultView: "agendaDay"
 , aspectRatio: "1.60"
 , year: #datePart("yyyy", now())#
 , month: #datePart("m", now())-1#
 , date: #datePart("d", now())#
 , events: #serializeJSON(viewBag.events)#
 , droppable: true
 , drop: function(date, allDay, jsEvent, ui) {   
  
  var originalEventObject = $(this).data('eventObject');
  var copiedEventObject = $.extend({}, originalEventObject);
  copiedEventObject.start = date;
  copiedEventObject.end = new Date(date).hours().add(-1);
  copiedEventObject._end = new Date(date).hours().add(-1);
  copiedEventObject.allDay = allDay;

  $('##calendar').fullCalendar('renderEvent', copiedEventObject, true);
  
  saveEvent($(this).data("taskid"),'','',copiedEventObject.start,copiedEventObject.end,'');

  
 }
 , eventResize: function(event, dayDelta, minuteDelta, revertFunc, jsEvent, ui, view){
  
  var newStart = event.start;
  var newEnd = event.end;
  var orgStart = new Date(newStart);
  var orgEnd = new Date(newEnd).addMinutes(minuteDelta * -1);
  
  saveEvent(event.taskID,orgStart,orgEnd,newStart,newEnd);
  
 }
 , eventDrop: function(event, dayDelta, minuteDelta, allDay, revertFunc, jsEvent, ui, view ){
            
     var newStart = event.start;
  var newEnd = event.end;
  var orgStart = new Date(newStart).addDays(dayDelta * -1);
   orgStart.addMinutes(minuteDelta * -1);
  var orgEnd = new Date(newEnd).addDays(dayDelta * -1);
   orgEnd.addMinutes(minuteDelta * -1);
  
  saveEvent(event.taskID,orgStart,orgEnd,newStart,newEnd);
       
 }
 ,eventDragStop:function(event, jsEvent, ui, view){
  
  if (draggedOverTrashCan(ui, $('div##trash-can'))) {
           
       var format = "yyyy-MM-dd HH:mm:ss";
       
         $.ajax({
    url:'/timesheet/deleteEvent'
    ,type:'POST'
    ,data:{
     startTime:event.start.toString(format)
     ,endTime:event.end.toString(format)
     ,taskID:event.taskID
     ,userID:'#viewBag.userID#'
    }
    ,dataType:'json'
    ,success:function(){
     $("##calendar").fullCalendar('removeEvents', event._id);
    }
   });
   
         
     }     
 }
});

This piece prepends a div to the top of the calendar to be used as a trash can.

$('##calendar').children('.fc-content').prepend('<div id="trash-can" style="border: 2px solid ##C1454B;padding:15px 100px 15px;border-radius:5px;background:##DD9094;text-align:center;">Drag Events Here To Remove</div>');

Railo Annotations

I've spent some time using Railo Coldfusion and recently purchased a home. I haven't had much time to write so I am catching up now.

I got a message that Railo is finally going to support annotations. Why is this a good thing? Because I can now use ColdMVC.