Valid Email in Query

create function [dbo].[fn_isValidEmail](@string varchar(500))
returns int
as
begin
return (
 select
   case when @string LIKE '%_@_%_.__%'
     --AND @test NOT LIKE '%[]%'
   then 1
   else 0
   end
  ) 
end

Flatten Tree to Array

ITree is a partial class for EF that just has Parent and Children props on it

private List FlattenTree(List items,List outputItems) where T:ITree
{

 foreach(var item in items){
 
 outputItems.Add(item);
 
 if(item.Children.Count > 0){
  return FlattenTree(item.Children, outputItems);
 }
 
 }

 return outputItems;
}

Strip Non Alpha in Query

create function [dbo].[fn_stripNonAlpha](@temp varchar(1000))
returns varchar(1000)
as
begin
 while patIndex('%[^a-z]%', @temp) > 0
  set @temp = stuff(@temp, patIndex('%[^a-z]%', @temp), 1, '')

 return @temp
end

Strip Non Numerics in Query

create function[dbo].[fn_StripNonNumeric]( @string varchar(max))
returns varchar(max)
as
begin
if patindex('%[^0-9]%', @string) > 0
while patindex('%[^0-9]%', @string) > 0
set @string = stuff(@string, patindex('%[^0-9]%', @string), 1, '')
return @string
end

Replace Vanity Phone Numbers with Numbers

create function replacePhoneNumberVanityChars(@string varchar(max))
returns varchar(max)
begin

 declare @len as int = len(@string);
 declare @i as int = 1;
 declare @char as varchar(1) = null;

 while(@i<=@len)
 begin

  set @char = right(left(@string,@i),1);

  if(@char in ('A','B','C'))
   set @char = '2';
  if(@char in ('D','E','F'))
   set @char = '3';
  if(@char in ('G','H','I'))
   set @char = '4';
  if(@char in ('J','K','L'))
   set @char = '5';
  if(@char in ('M','N','O'))
   set @char = '6';
  if(@char in ('P','Q','R','S'))
   set @char = '7';
  if(@char in ('T','U','V'))
   set @char = '8'; 
  if(@char in ('W','X','Y','Z'))
   set @char = '9';
  
  set @string = left(@string,@i-1) + @char  + right(@string,len(@string)-@i);

  set @char = null;
  set @i = @i +1;

 end
 
 return @string;

end

Database Lookup on Route in .NET MVC4

I started messing around in .NET MVC4's Routing and was wondering if you had a custom route where you need to look up something in the database, how would that work?

I found out this was possible using the constraints attribute when you setup your route. I created a new constraint by extending IRouteConstraint and forwarded the look up on to a model where I did the database look up.

App_Start >> RouteConfig

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using myApp.App_Start;

namespace myApp
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "LocationSite",
                url: "{id}/{action}",
                defaults: new { controller = "Snowboard", action = "Index", id = UrlParameter.Optional },
                constraints: new { id = new SnowboardSiteRouteConstraint()  }/*---database lookup for snowboards---*/
            );
         }
    }
}

App_Start >> SnowboardSiteRouteConstraint

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Routing;
using myApp.Models;

namespace myApp.App_Start
{
    public class SnowboardSiteRouteConstraint: IRouteConstraint
    {
        public bool Match(HttpContextBase httpContext, Route route, string parameterName,RouteValueDictionary values, RouteDirection routeDirection)
        {
           /*---database lookup for snowboards---*/

            return SnowboardSite.IsSnowboardSiteName((string)values["id"]);

        }
    }
}

Model >> SnowboardSite

using System;
using System.Collections.Generic;
using System.Web;
using myApp.Persistence;
using myApp.Entities;
using NHibernate;
using NHibernate.Linq;

namespace myApp.Models{
    public class SnowboardSite{
        public static bool IsSnowboardSiteName(string boardName){

            /*---database lookup for snowboards, return the result as boolean---*/

            return [[result of database lookup goes here as a boolean]];
        }
    }
}

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.

cfsavecontent vs CSS when wanting to add styles.

I ran into some confrontation when I decided to use a cfsavecontent and write some styles in <style/> tags and do a <cfhtmlhead/>. Another developer prefers everything in a .css file. While, yes all styles are in one place in the app, unneeded styles are being loaded on pages that don't need them.

I prefer the other route of doing styles in style tags on the page I am working in and then adding them to the head section. Don't get me wrong I still have some global styles set in .css files. Most of the pages I am working on are all custom interfaces and the .css file(s) would be unnecessary large between pages. Not to mention lots and/or large .css files bring down browser load time.

Thoughts?

Submit button name when posting form in JS

I am sure plenty of people out there have had this issue. So I had a submit button named "submit" and tried to do form submit in js by doing $("#theform").submit(); and I kept getting this error saying that .submit() wasn't a function. After some digging apparently if you name a submit button in a form "submit" it overwrites the submit() with submit button element. Not cool.

Developer Phases

1. How do I do that? (Entry)
2. Yeah, I can do that. (Mid)
3. Sigh. What was I thinking when I wrote this? (Adv)

Hover Bike

I am a big fan of hover technology and want to share this break through with a hover bike.

http://gearpatrol.com/blog/2011/06/09/bmw-powered-twin-rotorhoverbike/

It kind of reminds of a speeder bike from Star Wars.

Writing text over an image

I know alot of people have already done this, but I thought I would share writing text over image as well. Only because the first time you do it, it's really cool.

Read in the image

 <cfimage name = "local.image" action="read" source="test.jpg"/>

Turn on anti-aliasing ("softens jagged edges")

<cfset ImageSetAntiAliasing(local.image)/>

Set the text color

<cfset ImageSetDrawingColor(local.image, "000000")/>

Set extra attributes

<cfset local.attrs = {
    Font = 'Arial',
    Size = 36,
    Style = 'bold'
   }/>

Draw over the image (image,text,x-position,y-position,attributes)

<cfset ImageDrawText(local.image, "Hello World!", 10, 10, local.attrs)/>

SQL replace() blank stuff

Will never return 0

select replace('','',0)

Will return zero if the value is ''

select case when '' = '' then 0 else 1 end

SQL Selects ='s

Instead of writing a select in a query like this

select 
[user].id,
 [user].name,
 snowboard.name as snowboard
from [user]
 inner join snowboard on snowboard.id = [user].snowboard_id

try this

select 
 [user].id,
 [user].name,
 snowboard = snowboard.name
from [user]
 inner join snowboard on snowboard.id = [user].snowboard_id

It helps all the columns line up and read nicer.