Solved

CF Charting between dates

Posted on 2007-03-24
9
265 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!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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