• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

coldfusion calendar

hi,
i need to create a START AND END DATE calendar (no next and previous months links) and populate the values with dates including patientname & caregivername etc, for user to print it. I am generating the weeks in a dropdown for eg:
09/02/2012 - 09/08/2012
09/09/2012 - 09/15/2012
09/16/2012 - 09/22/2012
09/23/2012 - 09/29/2012
09/30/2012 - 10/06/2012

user going to select only a week range. according to user selection i need to create a calender which will populates the value inside the calender. So, after running a query i'm getting the startdate, enddate, patientname,caregivername and the visit dates.

so if the start date 9/2/2012 is end date is 9/8/2012, then system will generate the september calender, and the visit dates are 9/3/2012, 9/6/2012, 9/8/2012 then it will populate the values in it.

if the start date 09/30/2012 is end date is 10/06/2012, then system will generate the september/october calender, and if the visit dates are 9/30/2012, 10/5/2012 then it will populate the values in it.

the screenshot of required calender is also attached with this question. any help, comments or feedback would be deeply appreciated.
screenshot.jpg
0
s_hausen
Asked:
s_hausen
  • 8
  • 6
1 Solution
 
_agx_Commented:
If you're using sql server you could use a CTE to generate the calendar dates (and visits).

The html is extremely ugly, but it should get you started.

<cfparam name="url.dateRange" default="">

<!--- get first sunday of the current month --->
<cfset firstOfMonth = createDate(year(now()), month(now()), 1)>
<cfset firstSundayOfMonth = firstOfMonth>
<cfif dayOfWeek(firstOfMonth) gt 1>
	<cfset offset = 8-dayOfWeek(firstOfMonth)>
	<cfset firstSundayOfMonth = dateAdd("d", offset, firstOfMonth)>
</cfif>

<!--- FORM --->
<form method="get">
	<select name="dateRange">
		<cfset startDate = firstSundayOfMonth>
		<!--- number of weeks should be dynamic --->
		<cfloop from="0" to="4" index="num">
			<cfset startDate = dateAdd("ww", num, startDate)>
			<cfset endDate = dateAdd("d", 6, startDate)>
			<cfset currDateRange = dateFormat(startDate, "mm/dd/yyyy") &" - "& dateFormat(endDate, "mm/dd/yyyy")>
			<cfoutput>
			<option value="#currDateRange#" <cfif url.dateRange eq currDateRange>selected</cfif>>#currDateRange#</option>
			</cfoutput>
		</cfloop>
	</select>
	<input type="submit" name="submit">
</form>

<!--- RESULTS --->
<cfif structKeyExists(URL, "submit")>
	<!--- extract the date range --->
	<cfset startDate = trim(getToken(URL.dateRange, 1, "-"))>
	<cfset endDate = trim(getToken(URL.dateRange, 2, "-"))>

	<!--- if its not valid, default to this week --->
	<cfif not isDate(startDate) or not isDate(endDate)>
		<cfset startDate = firstSundayOfMonth>
		<cfset endDate = dateAdd("d", 6, startDate)>
	</cfif>
	

    <!--- get weekly calendar --->
    <cfquery name="getWeeklyVisits" datasource="YourDSNName"> 
         <!---
           NOTE, if vt.VisitDate contains a date AND time, you must truncate 
           the time in the join clause ie 
           c.CalendarDate = convert(datetime, convert(varchar, vt.VisitDate, 112), 112)
          --->
        ;WITH Calendar
        AS
        (
          SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS CalendarDate
          UNION ALL
          SELECT DATEADD(dd, 1, CalendarDate)
          FROM   Calendar
          WHERE  DATEADD(dd, 1, CalendarDate) <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">
        )
        SELECT	c.CalendarDate, vt.patientName, vt.caregiverName
        FROM	Calendar c 
        			LEFT JOIN YourVisitTable AS vt ON c.CalendarDate = vt.VisitDate
        ORDER BY c.CalendarDate
        OPTION (MAXRECURSION 7);
        </cfquery>

<style type="text/css">
	td.visitCell { 	vertical-align: top; 
					height: 100; width: 15%; 
					font-family: arial,verdana; 
					font-size: 0.75em; }
</style>
<table border="1" width="95%">
<tr><cfoutput query="getWeeklyVisits" group="CalendarDate">
	<!--- output each day of the week --->
	<td class="visitCell">
			#datePart("d", calendarDate)#<br><br>
			<!--- display any visits for this date --->
			<cfif len(trim(patientName))>
				<cfoutput>
					patient: #patientName#<br>
					care: #caregiverName#<br>
				</cfoutput>
			</cfif>
	</td>
	</cfoutput>
</tr>
</table>
</cfif>		

Open in new window

0
 
_agx_Commented:
Whoops... that only generates a weekly calendar.  You'll need something like this calendar udf to generate the whole month.

That said, it seems a little confusing to show individual weeks in the drop down, then display a calendar for a whole month?
0
 
s_hausenAuthor Commented:
hi agx,
i do appreciate your response, but is there a way i can have html calendar, and if you think monthly calendar is an issue, can we generate weekly calendar??
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
_agx_Commented:
Did you try the code above? Just plug in your dsn and table name.  It generates a weekly schedule. You just need to add day name headers and styling.

You could display a whole month, but that seems odd in terms of usability. ie Why require the user to select a week if you're going to display a whole month? Why not have them select a month and year instead?

What does this "html" calendar need to do besides show the events? The hardest Once you have the query logic (like above) you can style the output however you wish. There's also a bunch of prebuilt ones that may do what you need, or that you could adapt, such as:

http://www.riaforge.org/index.cfm?event=page.search#Calendar_catid=1
http://www.cflib.org/udf/createCalendar
http://www.raymondcamden.com/index.cfm/2006/6/2/Ask-a-Jedi-ColdFusion-Calendar
http://www.hotscripts.com/category/scripts/cfml/scripts-programs/calendars/
0
 
s_hausenAuthor Commented:
hi agx,
looks like, i've almost solve the calendar. i'm just having a display look, in which the data is not aligning right. the code is as under:
<cfquery name="getInfo" datasource="#this.dsn#">
SELECT medicaidSchedule.patientsRef, medicaidSchedule.weekno, medicaidSchedule.weekrange, medicaidSchedule.dateweek, SUBSTRING(medicaidSchedule.weekrange, 1, 10) AS startDate, SUBSTRING(medicaidSchedule.weekrange, 14, 10) AS endDate, medicaidSchedule.caregiver, medicaidSchedule.timein, medicaidSchedule.services, medicaidSchedule.admittedby, medicaidSchedule.comments, patient.patientsname 
FROM  medicaidSchedule
INNER JOIN Patient ON
medicaidSchedule.patientsRef = patient.Ref
WHERE medicaidSchedule.RecordClosed = 'False' AND (medicaidSchedule.weekno = '#FORM.weekno#')
ORDER BY medicaidSchedule.dateweek
</cfquery>

<table width="100%" border="1" cellspacing="2" cellpadding="2" align="center" style=" table-layout:fixed;">
<cfoutput>
  <tr>
  <cfloop index="d" from="0" to="6" step="1">
  <cfset weeklyDates = "#dateFormat(dateAdd('d', d, '#getInfo.startDate#'), 'mm/dd/yyyy')#"/>
  <cfset weeklyDays="#DayOfWeek("#VARIABLES.weeklyDates#")#">
    <td width="14%" align="center">#DayOfWeekAsString(VARIABLES.weeklyDays)#</td>
  </cfloop>
  </tr>
  <tr>
  <cfloop index="d" from="0" to="6" step="1">
  <cfset weeklyDates = "#dateFormat(dateAdd('d', d, '#getInfo.startDate#'), 'mm/dd/yyyy')#"/>
  <cfset weeklyDays="#DayOfWeek("#VARIABLES.weeklyDates#")#">
    <td width="14%" align="center">#VARIABLES.weeklyDates#</td>
  </cfloop>
  </tr>
</cfoutput> 
<cfoutput query="getInfo"> 
    <tr> 
  <cfloop index="d" from="0" to="6" step="1">
  <cfset weeklyDates = "#dateFormat(dateAdd('d', d, '#getInfo.startDate#'), 'mm/dd/yyyy')#"/>
  <cfset weeklyDays="#DayOfWeek("#VARIABLES.weeklyDates#")#"> 
    <td width="14%" align="center" valign="top">#weeklyDays#<cfif VARIABLES.weeklyDates EQ DateFormat(dateweek, "mm/dd/yyyy")>
        <strong>Patient:</strong> #patientsname#<br />
        <strong>Caregiver:</strong> #caregiver#<br />
      </cfif></td>
  </cfloop>
  </tr> 
</cfoutput>
</table>

Open in new window


the data supposed to be, start from top to bottom format. like in the Monday section.
screenshot1.jpg
0
 
_agx_Commented:
You can't really do it like. The problem is the code basically says: for each row in the query create a new table row but only fill in 1 of the 7 cells. That's why it looks misaligned.  Because of the way html tables work you must know all 7 values in advance. Otherwise you end up with misaligned results.

Was there a problem with the earlier CTE I posted - or is it just that you need to use separate <table> cells for each visit? Also, why all the substrings? Doesn't your table have a datetime column? If so, does it contain just a date mm/dd/yyyy or both a date and time mm/dd/yyyy hh:mm:ss?
0
 
s_hausenAuthor Commented:
hi agx,
I'm not familiar with CTE's and having trouble to understand'em. Please help me, what would be the best route to accomplish this task.
0
 
s_hausenAuthor Commented:
i'm also sending you the screenshot of the table. tableScreenShot
0
 
_agx_Commented:
Ok, so "dateWeek" is our datetime column.  This CTE's is very simple.  It just creates a "virtual" table of 7 dates and gives it the alias "Calendar". Then you can use "Calendar" in a JOIN as if it were a regular table.

        ;WITH Calendar
        AS
        ( --- translated
          SELECT '09/12/2012' AS CalendarDate
          UNION ALL
          SELECT DATEADD(dd, 1, CalendarDate)
          FROM   Calendar
          WHERE  DATEADD(dd, 1, CalendarDate) <= '09/08/2012'
        )


Getting back to your db. Try substituting the query below for the one in my earlier example. It only needs a #startDate# and #endDate# range, like 09/02/2012 to 09/08/2012 to work .

<cfquery name="getWeeklyVisits" datasource="YourDSNName"> 
    ;WITH Calendar
    AS
    ( 
          SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS CalendarDate
          UNION ALL
          SELECT DATEADD(dd, 1, CalendarDate)
          FROM   Calendar
          WHERE  DATEADD(dd, 1, CalendarDate) <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">
    )
SELECT c.CalendarDate, sh.patientsRef, sh.weekno, sh.weekrange, sh.dateweek
       ,  SUBSTRING(sh.weekrange, 1, 10) AS startDate, SUBSTRING(sh.weekrange, 14, 10) AS endDate
       , sh.caregiver, sh.timein, sh.services, sh.admittedby, sh.comments, p.patientsname 
FROM  Calendar c 
          LEFT JOIN medicaidSchedule sh ON c.CalendarDate = sh.VisitDate
                AND  sh.RecordClosed = 'False' 
                AND  sh.weekno = '#FORM.weekno#'
          LEFT JOIN Patient p ON sh.patientsRef = p.Ref
ORDER BY c.CalendarDate
OPTION (MAXRECURSION 7);

Open in new window

0
 
_agx_Commented:
That should work fine. Note, it doesn't use separate <td> cells for each visit. If that's a "must have" we'll have to take a different approach. Because to properly align everything in a <table> we'll have to know all 7 values upfront..
0
 
s_hausenAuthor Commented:
hi agx,
i ran the code, as you mentioned as below:

<cfset this.dsn = "#session.providerDataSrc#">
<cfparam name="url.dateRange" default="">

<!--- get first sunday of the current month --->
<cfset firstOfMonth = createDate(year(now()), month(now()), 1)>
<cfset firstSundayOfMonth = firstOfMonth>
<cfif dayOfWeek(firstOfMonth) gt 1>
	<cfset offset = 8-dayOfWeek(firstOfMonth)>
	<cfset firstSundayOfMonth = dateAdd("d", offset, firstOfMonth)>
</cfif>

<!--- FORM --->
<form method="get">
	<select name="dateRange">
		<cfset startDate = firstSundayOfMonth>
		<!--- number of weeks should be dynamic --->
		<cfloop from="0" to="4" index="num">
			<cfset startDate = dateAdd("ww", num, startDate)>
			<cfset endDate = dateAdd("d", 6, startDate)>
			<cfset currDateRange = dateFormat(startDate, "mm/dd/yyyy") &" - "& dateFormat(endDate, "mm/dd/yyyy")>
			<cfoutput>
			<option value="#currDateRange#" <cfif url.dateRange eq currDateRange>selected</cfif>>#currDateRange#</option>
			</cfoutput>
		</cfloop>
	</select>
	<input type="submit" name="submit">
</form>

<!--- RESULTS --->
<cfif structKeyExists(URL, "submit")>
	<!--- extract the date range --->
	<cfset startDate = trim(getToken(URL.dateRange, 1, "-"))>
	<cfset endDate = trim(getToken(URL.dateRange, 2, "-"))>

	<!--- if its not valid, default to this week --->
	<cfif not isDate(startDate) or not isDate(endDate)>
		<cfset startDate = firstSundayOfMonth>
		<cfset endDate = dateAdd("d", 6, startDate)>
	</cfif>
	

    <!--- get weekly calendar --->
    <cfquery name="getWeeklyVisits" datasource="#this.dsn#"> 
         <!---
           NOTE, if vt.VisitDate contains a date AND time, you must truncate 
           the time in the join clause ie 
           c.CalendarDate = convert(datetime, convert(varchar, vt.VisitDate, 112), 112)
          --->
            ;WITH Calendar
    AS
    ( 
          SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS CalendarDate
          UNION ALL
          SELECT DATEADD(dd, 1, CalendarDate)
          FROM   Calendar
          WHERE  DATEADD(dd, 1, CalendarDate) <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">
    )
SELECT c.CalendarDate, sh.patientsRef, sh.weekno, sh.weekrange, sh.dateweek
       ,  SUBSTRING(sh.weekrange, 1, 10) AS startDate, SUBSTRING(sh.weekrange, 14, 10) AS endDate
       , sh.caregiver, sh.timein, sh.services, sh.admittedby, sh.comments, p.patientsname 
FROM  Calendar c 
          LEFT JOIN medicaidSchedule sh ON c.CalendarDate = sh.VisitDate
                AND  sh.RecordClosed = 'False' 
                AND  sh.weekno = '#FORM.weekno#'
          LEFT JOIN Patient p ON sh.patientsRef = p.Ref
ORDER BY c.CalendarDate
OPTION (MAXRECURSION 7);
        </cfquery>

<style type="text/css">
	td.visitCell { 	vertical-align: top; 
					height: 100; width: 15%; 
					font-family: arial,verdana; 
					font-size: 0.75em; }
</style>
<table border="1" width="95%">
<tr><cfoutput query="getWeeklyVisits" group="CalendarDate">
	<!--- output each day of the week --->
	<td class="visitCell">
			#datePart("d", calendarDate)#<br><br>
			<!--- display any visits for this date --->
			<!---<cfif len(trim(patientName))>--->
				<cfoutput>
					<!---patient: #patientName#<br>--->
					care: #calendarDate#<br>
				</cfoutput>
			<!---</cfif>--->
	</td>
	</cfoutput>
</tr>
</table>
</cfif>		

Open in new window


it gives me an error.

Error Occurred While Processing Request
Element WEEKNO is undefined in FORM.
 
The error occurred in C:\ColdFusion8\wwwroot\TynetWebApp\test.cfm: line 525

523 :           LEFT JOIN medicaidSchedule sh ON c.CalendarDate = sh.VisitDate
524 :                 AND  sh.RecordClosed = 'False' 
525 :                 AND  sh.weekno = '#FORM.weekno#'
526 :           LEFT JOIN Patient p ON sh.patientsRef = p.Ref
527 : ORDER BY c.CalendarDate

Open in new window


so i put the value of "362012" and it gives me this error.

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'VisitDate'.
 
The error occurred in C:\ColdFusion8\wwwroot\TynetWebApp\test.cfm: line 517

515 :           SELECT DATEADD(dd, 1, CalendarDate)
516 :           FROM   Calendar
517 :           WHERE  DATEADD(dd, 1, CalendarDate) <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">
518 :     )
519 : SELECT c.CalendarDate, sh.patientsRef, sh.weekno, sh.weekrange, sh.dateweek

Open in new window


any help...
0
 
_agx_Commented:
Sorry I forgot to replace one of you column names. Change this line

       LEFT JOIN medicaidSchedule sh ON c.CalendarDate = sh.VisitDate

to:

       LEFT JOIN medicaidSchedule sh ON c.CalendarDate = sh.dateWeek
0
 
s_hausenAuthor Commented:
now its working, is there a way, i can print on top day & date??
0
 
_agx_Commented:
Just use the startDate and dateAdd:

Edit:  Removed the day number from the cells since you're now printing it on top of the column.

<table border="1" width="95%">
<cfoutput>
<!--- display day names --->
<tr><cfloop from="0" to="6" index="days">
      <th>#dateFormat(dateAdd("d", days, startDate), "dddd")#</th>
    </cfloop>
</tr>
<!--- display date --->
<tr><cfloop from="0" to="6" index="days">
      <td>#dateFormat(dateAdd("d", days, startDate), "mm/dd/yyyy")#</td>
    </cfloop>
</tr>
</cfoutput>
<tr><cfoutput query="getWeeklyVisits" group="CalendarDate">
      <!--- output each day of the week --->
      <td class="visitCell">
                  <!--- display any visits for this date --->
                  <cfif len(trim(patientName))>
                        <cfoutput>
                              patient: #patientName#<br>
                              care: #caregiverName#<br>
                        </cfoutput>
                  </cfif>
                  &nbsp;
      </td>
      </cfoutput>
</tr>
</table>
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now