Solved

CF Charting between dates

Posted on 2007-03-24
9
270 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:dudeatwork
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 18788069

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 ?

0
 

Author Comment

by:dudeatwork
ID: 18788134
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?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18790451

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)


0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:dudeatwork
ID: 18790516
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?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18790596

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>
0
 

Author Comment

by:dudeatwork
ID: 18836140
None worked and I am too new at cfcharting. Argh!!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18836203

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

Author Comment

by:dudeatwork
ID: 18836298
<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.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18836446

 Ok, thanks.  Let's go step by step, if that's Ok.   What do the results of that query look like?  If possible, can you post that as well?    ( to do so, you can either pull the query from the CF dubug information at the bottom of the screen OR  you can  copy the SELECT and wrap it in CFOUTPUT to it shows on the screen; then paste it into your database and see what comes of it )

Once we are sure the query is pulling the right data and it looks good, we will look at the next part, the graph...
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
COLD FUSION 6 27
404 Error after upgrading from Coldfusion 9 to Coldfusion 11 2 180
Coldfusion Report Builder HTML table 2 69
ColdFusion Rereplace 3 82
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question