Home > ASP Development > Calculating clients hours

Calculating clients hours



My Code:


Code:


<%@LANGUAGE="VBSCRIPT"%>

<!-- #include file="common.asp" -->

<!-- #include file="../adovbs.inc" -->

<%

' technocurve arc 3 asp vb bv block1/3 start

Dim myColor1, myColor2, myColor

myColor1 = "#FFFFFF"

myColor2 = "#CCCCCC"

myColor = myColor1

' technocurve arc 3 asp vb bv block1/3 start

%>

<html>

<head>

<link rel=stylesheet href="backOfficeCssNet.css" type="text/css">

<title><-------------------------- Altra.Net ----------------------------------------------------------------------------------------------></title>

</head>

<BODY>

<span Class = "TData_Text">	

<%

dtmTargetDate = Date

intDay = Day(dtmTargetDate)



dtmLastDay = dtmTargetDate - intDay

dtmFirstDay = Month(dtmLastDay) & "/1/" & Year(dtmLastDay)



strFrom = dtmFirstDay

strTo = dtmLastDay



strSQL = "SELECT * FROM tClient WHERE dormant = 'No' ORDER BY id"

dim objRS

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open objConn

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.open strSQL,Conn

PreviousHours = objRS("HoursCarried")

%>

<TABLE width = 100%>

  <TR   Class = "TData_Info"> 

    <TD width="8%">Client ID</TD>

    <TD width="14%">Client</TD>

    <TD width="10%">Total Hours</TD>

    <TD width="8%">Retainer Hours</TD>

    <TD width="8%">Hours Over</TD>

    <TD width="15%">Previous Hours Carried</TD>

	<TD width="10%">New Hours Carried</TD>

    <TD width="8%"></TD>

    <TD width="8%"></TD>

  </TR>

<%

While Not objRS.EOF

strSQL2 = "SELECT ClientId, SUM(HoursSpent) AS Total FROM tJob WHERE ClientId = " & objRS("id") & " AND theDate BETWEEN #" & strFrom & "# AND #" & strTo & "# GROUP BY ClientId"

Set rs = Server.CreateObject("ADODB.Recordset")

rs.open strSQL2,Conn



While Not rs.EOF

FreeHours = objRS("NoFreeHours")

TotalHours = rs("Total")



if TotalHours > FreeHours then

HoursOver = 0

else if FreeHours > TotalHours then

HoursOver = Freehours - TotalHours

end if

end if



HoursCarried = 0

if HoursOver < PreviousHours then

HoursCarried = HoursOver - PreviousHours

else if PreviousHours > HoursOver then

HoursCarried = FreeHours

else if PreviousHours = 0 then

HoursCarried = HoursOver

end if

end if

end if

Conn.Execute "UPDATE tClient SET hoursCarried = "& HoursCarried &" WHERE id = " & rs("ClientId")

%>

<TR <%

' technocurve arc 3 asp vb bv block2/3 start

Response.Write(" style='background-color:" & myColor & "'")

' technocurve arc 3 asp vb bv block2/3 start

%> Class = "TData_Text" > 

    <td width="8%"><%=rs("ClientId")%></td>

    <td width="25%"><%=objRS("Name")%></td>

    <TD width="10%"><%=rs("Total")%></TD>	

    <TD width="8%"><%=objRS("NoFreeHours")%></TD>

    <TD width="8%"><%=HoursOver%></TD>

    <TD width="15%"><%=PreviousHours%></TD>

	<TD width="10%"><%=HoursCarried%></TD>

    <TD width="8%"></TD>

    <TD width="8%"></TD>

  </TR>

  <%

' technocurve arc 3 asp vb bv block3/3 start

if myColor = myColor1 then

	myColor = myColor2

else

	myColor = myColor1

end if

' technocurve arc 3 asp vb bv block3/3 start

%>

<%

rs.MoveNext

Wend

objRS.MoveNext

Wend

objRS.Close

Set objRS = Nothing

Conn.Close

set Conn = Nothing

%>

</TABLE>	

</SPAN> 

<!--------------/page information--------------->

</body>

</html>






ok this calculates hours spent on a client and their retainer hours, their hours left over and wot gets carried to the next month.



now their hours carried over to the next month can never exceed their retainer hours.



when i initally execute the script everything works out perfect, but if i execute it again on the same month, it gets it all wrong?



i have uploaded to documents showing the first and 2nd execution of this file.



thanks

    
Guest


howzit shem

change


Code:




if TotalHours > FreeHours then

HoursOver = 0

else if FreeHours > TotalHours then

HoursOver = Freehours - TotalHours

end if

end if



HoursCarried = 0

if HoursOver < PreviousHours then

HoursCarried = HoursOver - PreviousHours

else if PreviousHours > HoursOver then

HoursCarried = FreeHours

else if PreviousHours = 0 then

HoursCarried = HoursOver

end if

end if

end if




to this


Code:




if TotalHours > FreeHours then

	HoursOver = 0

elseif FreeHours > TotalHours then

	HoursOver = Freehours - TotalHours

end if



HoursCarried = 0

if HoursOver < PreviousHours then

	HoursCarried = HoursOver - PreviousHours

elseif PreviousHours > HoursOver then

	HoursCarried = FreeHours

elseif PreviousHours = 0 then

	HoursCarried = HoursOver

end if




its a bit shorter



I dont think you will be able to run this app twice a month, because you

are going to use the PreviousHours twice.



I think you'll have to keep track of if you have updated the hoursCarried

for that month somehow...



why would you want to run this more than once a month?

Was this answer helpful ? Yes No   
Guest


oh i tried to run it twice coz i thought it would just run, and keep calculating? shouldn't it? i was just testing



surely it would not make a diff how many time it runs?



thanks for shortening it for me

Was this answer helpful ? Yes No   
Guest


shem,

this statement doesn't make sense to me


Code:




if HoursOver < PreviousHours then

	HoursCarried = HoursOver - PreviousHours

elseif PreviousHours > HoursOver then

	HoursCarried = FreeHours

elseif PreviousHours = 0 then

	HoursCarried = HoursOver

end if




shouldn't the HoursOver be added to the PreviousHours to create the new HoursCarried?

You owe me 3 hours from last month and 2 hours for this month,

and I will get -1 hours instead of 5 hours?

Was this answer helpful ? Yes No   
Guest


first off ... aren't the two bolded lines the exact same thing written two different ways


Code:




if HoursOver < PreviousHours  then

	HoursCarried = HoursOver - PreviousHours

elseif PreviousHours > HoursOver  then



Was this answer helpful ? Yes No   
Guest


tru.. and the HoursCarried can't be more than FreeHours...



so how do i write that?

Was this answer helpful ? Yes No   
Guest


ok so i need to check first if the two must be added, if not then subtract them, and making sure that it never exceeds FreeHours

Was this answer helpful ? Yes No   
Guest


shem, could you just list all the variables and what

values they are given? I am getting confused now

Was this answer helpful ? Yes No   
Guest


lol me too...



FreeHours = retainer hours

TotalHours = total time spent on that client for the month

HoursOver = the hours they have left over for the month



PreviousHours = last months HoursOver

HoursCarried = the new calculated hours to be carried over to next month



HoursCarried can't exceed FreeHours



i keep getting the formulas wrong

Was this answer helpful ? Yes No   
Guest


I wonder if something like this would work?


Code:




HoursOver = FreeHours - TotalHours



PreviousHours = PreviousHours + HoursOver



if PreviousHours < 0 then

	HoursCarried = 0

elseif PreviousHours > 0 and PreviousHours < FreeHours Then

	hoursCarried = PreviousHours

elseif PreviousHours > 0 and PreviousHours > FreeHours Then

	HoursCarried = FreeHours

end if



Was this answer helpful ? Yes No   
Guest
1    [2]    Next    Last
Answer it
Ask Question About ASP Development
 
 
Home - About Infoqu - Contact - Privacy Statement - Link to Infoqu - Bookmark Infoqu

Copyright 2007-2008 by Infoqu. All rights reserved