Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CF Charting between dates

Posted on 2007-03-24
9
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 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