Solved

CF Charting between dates

Posted on 2007-03-24
9
272 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

734 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