asked on

CF Charting between dates

I am having a problem creating a list that I can chart for an employees work week. All I am trying to do is chart the hours he has put in for the week and NOT going past 7 days. Here is what I have so far.
<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset mytoday = "#DayofWeekAsString(DayOfWeek(yourDate))#">
<cfif #mrtoday# eq "Sunday">
<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
<cfelseif #mrtoday# eq "Saturday">
<cfset YourDate = #DATEFORMAT(NOW()+1, 'mm-dd-yyyy')#>
<cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
</cfif>
<cfquery name="gettime" datasource="DB">
select emp_time from timesheet where emp_id = #emp_id# and emp_time <> '' and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
</cfquery>
<cfset myweek = "">
<cfloop query="gettime">
<cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)
<cf_activechart
      width="200"
      title="Employee Weekly Activity"
      values="#myweek#"
      legend="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
      urls="http://www.somesite.net">

All I am wanting is to graph the field on what ever day for the week. Employee enters various times throughout the day and week. He maybe mailed something and charges 1 hour on monday, and later charges 2 hours for copying. Tuesday he works 8 hours as one entry and so on. Can this be done?

gdemaria

What is this line supposed to do?

<cfloop query="gettime">
<cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)

Looks like you loop over every time entry and OVERWRITE the myweek variable with the emp_time. At the end, the my_week variable will contain the LAST record's value.

Did you mean to add them up?

Or perhaps the </CFLOOP> should be Below the <cf_activeChart ?

dudeatwork

ASKER

I know it's over writting the loop but I can't seem to figure out how to add the hours per eveyday of the week for one employee. That loop is crap, a vain last effort before bed. I just want to add the value per day and chart the week on a graph or chart. Also, that particular chart sucks, is there a better looking one you know of?

gdemaria

You need two values in your query; one for the number of hours worked, and one for the date/time of when the person did the work. I think, in your case, these are emp_time (number of hours) and myDate (the date worked).

You can add the time and group by date so the query does the work for you... the result is the total number of hours worked on each date...

select sum(emp_time) as DaysTotal
, convert(myDate, 101) as myDate
from timesheet
where emp_id = #emp_id#
and emp_time <> ''
and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
group by convert(myDate, 101)

dudeatwork

ASKER

It's the organization of all the code I am having problems with. Thats helps, but it doesn't work because of the logic. Thoughts?

gdemaria

Sure, just use the query with CFCHART..

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-a13.htm#wp2741830

<cfchart format="png" scalefrom="-50" scaleTo="100" gridlines="7">
<cfchartseries type="line" query="getTime"
itemColumn="DaysTotal" valueColumn="myDate">
</cfchart>

dudeatwork

ASKER

None worked and I am too new at cfcharting. Argh!!

gdemaria

ok, well, why don't you post your updated code and we'll try to see what's happening

dudeatwork

ASKER

<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset mytoday = "#DayofWeekAsString(DayOfWeek(yourDate))#">
<cfif #mrtoday# eq "Sunday">
<cfset YourDate = #DATEFORMAT(NOW(), 'mm-dd-yyyy')#>
<cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
<cfelseif #mrtoday# eq "Saturday">
<cfset YourDate = #DATEFORMAT(NOW()+1, 'mm-dd-yyyy')#>
<cfset EndDate = #DATEFORMAT(NOW()-7, 'mm-dd-yyyy')#>
</cfif>
<cfquery name="gettime" datasource="DB">
select sum(emp_time) as DaysTotal
, convert(myDate, 101) as myDate
from timesheet
where emp_id = #emp_id#
and emp_time <> ''
and mydate BETWEEN #createodbcdate(Yourdate)# and #createodbcdate(EndDate)#
group by convert(myDate, 101)
</cfquery>
<cfset myweek = "">
<cfloop query="gettime">
<cfset myweek = "#gettime.emp_time#">
</cfloop>
<cfoutput>#myweek#</cfoutput>(test)
<cfchart format="png" scalefrom="-50" scaleTo="100" gridlines="7">
<cfchartseries type="line" query="getTime"
itemColumn="DaysTotal" valueColumn="myDate">
</cfchart>
I just want to show what they have worked/hours up till "today" from the begining of the week. I am confused but really would like to see a charting of their time.

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial