View Single Post
  #4 (permalink)  
Old October 20th, 2005, 09:12 AM
neo_jakey neo_jakey is offline
Authorized User
 
Join Date: Jun 2003
Location: Mexico City, Distrito Federal, Mexico.
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried to incorporate your solution into my code but have run into problems. I want to user the resulting vrsHours in a calculation. The page extract a datetime e.g. 10/20/2005 8:59:04 AM from data base and uses Left() and Right() functions to leave me with just the number of hours. I then want to say:
  rsTotalHoursWorked = vrsHours2 - vrsHours1
  Response.Write rsTotalHoursWorked

Take a look at my code for the complete page. Some of the text is in spanish.

Code:
<%@ LANGUAGE="VBScript" %> <% Response.Buffer = true %>


<%
rsNum = Request("StaffNum")
Set oConn=Server.CreateObject("ADODB.connection")
Set oRSbt=Server.CreateObject("ADODB.recordset")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH_DB & ";"
strSQL = "SELECT * FROM CardInfo WHERE CardID = '" & rsNum & "';"
oRSbt.open strSQL, oConn
oRSbt.MoveFirst
rsSpacer = "&nbsp;"
rsFullName = oRSbt("FirstName") & rsSpacer & oRSbt("LastName") 
CurrentDate = now()
CurrentDayName = WeekDayName(WeekDay(Date()))
CurrentDayNum = WeekDay(Date())
IF CurrentDayName = "Monday" THEN
  CurrentDayName = "Lunes"
End IF
IF CurrentDayName = "Tuesday" THEN
  CurrentDayName = "Martes"
End IF
IF CurrentDayName = "Wednesday" THEN
  CurrentDayName = "Miercoles"
End IF
IF CurrentDayName = "Thursday" THEN
  CurrentDayName = "Jueves"
End IF
IF CurrentDayName = "Friday" THEN
  CurrentDayName = "Viernes"
End IF
IF CurrentDayName = "Saturday" THEN
  CurrentDayName = "Sabado"
End IF
IF CurrentDayName = "Sunday" THEN
  CurrentDayName = "Domingo"
End IF

rsNum = Right(rsNum, 1)

xYear = LEN(Year(Now()))
xMonth = LEN(Month(Now()))
xDay = LEN(Day(Now()))
nCharacters = (xYear + xMonth + xDay) + 2

CurrentDate = Left(CurrentDate, nCharacters)

rsStart = CurrentDate & " 00:00:00 AM"
rsEnd = CurrentDate & " 11:59:59 PM"

%>
<html>

<head>

<meta http-equiv="Content-Language" content="es-mx">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Hoy: <%=rsFullName%> | <%=BUILD_NUMBER%></title>
<link rel="stylesheet" type="text/css" href="css/blue.css">
<script language="JavaScript" type="text/javascript" src="jsc/date.js"></script>
<script Language="JScript">
<!--
function mouseovertd (o){
    o.style.backgroundColor = '#B4C0E2';
    o.style.color = '#003194';
    o.style.cursor = 'hand';    
}
function mouseouttd (o){
    o.style.backgroundColor = '#4665B5';
    o.style.color = '#FFFFFF';
}
// -->
</script>
</head>

<body background="img/gbckgrnd.gif">

<table border="0" cellpadding="0" cellspacing="0" width="750" align="center">
  <tr>
    <td class="tb" colspan="5"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr class="th">
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="20"></td>
    <td width="598" class="hdr" height="19">&nbsp;Servicios Interactivos Internacionales
    <sup>TM</sup> |
    <script language="JavaScript" type="text/javascript">document.write("Hoy es " + dayName[now.getDay()] + ", " + monName[now.getMonth()] + " " + now.getDate() + ", " + now.getYear() + ".")
    </script>
    </td>
    <td width="150" class="hdr" height="19" align="right">Sesión de:
    <%=Session("iNetDMUser")%> </td>
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="20"></td>
  </tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" width="750" align="center">
  <tr>
    <td width="750" class="tb" colspan="3">
    <img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td width="748" class="tl"><img src="img/pixel.gif" width="1" height="1"></td>
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" width="750" align="center">
  <tr>
    <td class="tb"><img src="img/pixel.gif" width="1" height="78"></td>
    <td width="750" colspan="3" class="titleimage"></td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="78"></td>
  </tr>
</table>
<table width="750" cellpadding="0" cellspacing="0" border="0" align="center">
  <tr>
    <td class="tb" height="20"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="th" align="right" nowrap id="c1" height="20"><%=LOCATION%></td>
    <td class="tb" height="20"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td bgcolor="4665B5">
    <table border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;<img src="img/yarrow.gif" width="13" height="11"></td>
        <td class="nav" nowrap>&nbsp;<a href="default.asp" title="Página Principal" class="bcrumb">Página 
        Principal</a></td>
        <td>&nbsp;<img src="img/yarrow.gif" width="13" height="11"></td>
        <td class="nav" nowrap>&nbsp;<a href="empleados.asp" title="Manejo de Empleados" class="bcrumb">Manejo 
        de Empleados</a></td>
        <td>&nbsp;<img src="img/yarrow.gif" width="13" height="11"></td>
        <td class="nav" nowrap>&nbsp;Hoy&nbsp;:&nbsp;<%=rsFullName%></td>
      </tr>
    </table>
    </td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td class="th" align="right">&nbsp;</td>
    <td class="tb" rowspan="1"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr class="tw">
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
    <td width="748" valign="top" align="center" background="img/login_background2.jpg">
    <table border="0" cellpadding="0" cellspacing="0" width="5">
      <tr>
        <td width="5"><img src="img/pixel.gif" width="5" height="10"></td>
      </tr>
    </table>
    <table width="700" cellpadding="0" cellspacing="0" border="0">
      <tr class="tm">
        <td valign="top" width="11"><img border="0" src="img/hm_cctl.gif"></td>
        <td class="nav" width="678">Empleado</td>
        <td valign="top" align="right" width="11"><img src="img/hm_cctr.gif"></td>
      </tr>
    </table>
    <table width="700" cellpadding="0" cellspacing="0" border="0">
      <tr align="center">
        <td width="2" class="tm"><img src="img/pixel.gif" width="2" height="1"></td>
        <td width="9" bgcolor="ECE9D8" valign="bottom">&nbsp;</td>
        <td width="678" bgcolor="ECE9D8">
        <table>
          <tr>
            <td height="5"></td>
          </tr>
        </table>
        <table border="0" cellpadding="0" cellspacing="0" width="650" background="img/xpbackgrnd.jpg">
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" bgcolor="919B9D" colspan="2">
            <img src="img/pixel.gif"></td>
            <td width="1" bgcolor="919B9D" width="1"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" colspan="2">
            <img src="img/pixel.gif" width="5" height="5"></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" colspan="2" height="22" align="center" valign="top">
            <table border="0" cellpadding="0" cellspacing="0" width="633">
              <tr>
                <td width="13" height="5"></td>
                <td width="27" height="5"><img src="img/people/lilman_top.gif"></td>
                <td width="593" height="5"></td>
              </tr>
              <tr>
                <td width="13" class="tm" height="19"></td>
                <td width="27" class="tm" height="19"><img src="img/people/lilman.gif"></td>
                <td width="593" class="tm" height="19"><span class="a" id="d3"><b>&nbsp;<%=rsFullName%></b></span></td>
              </tr>
              <tr>
                <td width="13" height="8"></td>
                <td width="27" height="8"><img src="img/people/lilman_bottom.gif"></td>
                <td width="593" height="8"></td>
              </tr>
            </table>
            <table border="0" cellpadding="0" cellspacing="0" width="490">
              <tr>
                <td width="166" class="a" align="right"><b>Numero de [u]C</u>redencial&nbsp;:&nbsp;</b></td>
                <td width="324" class="a" id="d2" bgcolor="F4F3EE"><%=oRSbt("CardId")%></td>
              </tr>
              <tr>
                <td colspan="2" height="6"></td>
              </tr>
              <tr>
                <td width="166" class="a" align="right"><b>[u]H</u>oy&nbsp;:&nbsp;</b></td>
                <td width="324" class="a" id="d2" bgcolor="F4F3EE"><%=CurrentDayName%><%=rsSpacer%>-<%=rsSpacer%><%=CurrentDate%></td>
              </tr>
              <tr>
                <td colspan="2" height="6"></td>
              </tr>
              <% Set oRSbn=Server.CreateObject("ADODB.recordset")
              strSQL = "SELECT * FROM CardTransHistory" _
                     & " WHERE TransDate BETWEEN #" & rsStart & "# AND #" & rsEnd & "#" _
                     & " AND CardId = 'Card#=" & rsNum & "';"
              oRSbn.open strSQL, oConn
              Set oRSbz=Server.CreateObject("ADODB.recordset")
              strSQLCount = "SELECT count(*) as nTrans FROM CardTransHistory" _
                          & " WHERE TransDate BETWEEN #" & rsStart & "# AND #" & rsEnd & "#" _
                          & " AND CardId = 'Card#=" & rsNum & "';"
              oRSbz.open strSQLCount, oConn
              nTrans = oRSbz("nTrans")
              oRSbz.Close
              SET oRSbz = NOTHING %>
              <tr>
                <td width="166" class="a" align="right" valign="top"><b>T[u]r</u>ansacciones&nbsp;:&nbsp;</b></td>
                <% IF oRSbn.EOF THEN %>
                <td width="324" class="a" id="d2" bgcolor="F4F3EE">No hay transacciones de hoy</td>
                <% ELSE %>
                <td width="324" class="a" id="d2" bgcolor="F4F3EE">
                <table border="0" width="100%" cellpadding="0" cellspacing="0">
                  <% oRSbn.MoveFirst
                  rsTransCnt = 1
                  rsHours = 0
                  FOR i = 1 TO nTrans
                  rsTotalCnt  = LEN(oRSbn("TransDate"))
                  IF TRIM(oRSbn("Reader")) = "Reader=A" THEN
                    rsReader = "Entrada"
                  ELSE
                    rsReader = "Salida"
                  END IF %>
                  <tr>
                    <% IF rsTotalCnt = 21 THEN
                    rsAMPM = Right(oRSbn("TransDate"), 2)
                    rsHours = Right(oRSbn("TransDate"), nCharacters)
                    rsHours = Left(rsHours, 1)
                    IF rsAMPM = "PM" THEN
                      IF rsHours <> 12 THEN
                        rsHours = rsHours + 12
                      END IF
                    END IF
                    vrsHours="rsHours"
                    EXECUTE(vrsHours&i & " = rsHours") %> 
                    <td class="a" id="d2"><%=Right(oRSbn("TransDate"), nCharacters)%>&nbsp;-&nbsp;<%=rsReader%></td>
                    <% ELSE
                    rsAMPM = Right(oRSbn("TransDate"), 2)
                    rsHours = Right(oRSbn("TransDate"), nCharacters + 1)
                    rsHours = Left(rsHours, 2)
                    IF rsAMPM = "PM" THEN
                      IF rsHours <> 12 THEN
                        rsHours = rsHours + 12
                      END IF
                    END IF
                    vrsHours="rsHours"
                    EXECUTE(vrsHours&i & " = rsHours") %>
                    <td class="a" id="d2"><%=Right(oRSbn("TransDate"), nCharacters+1)%>&nbsp;-&nbsp;<%=rsReader%></td>
                    <% END IF %>
                  </tr>
                  <% rsTransCnt = rsTransCnt + 1
                  oRSbn.movenext
                  NEXT %>
                </table></td>
              </tr>
              <% END IF %>
              <tr>
                <td colspan="2" height="6"></td>
              </tr>
              <tr>
                <td width="166" class="a" align="right"><b>H[u]o</u>ras&nbsp;:&nbsp;</b></td>
                <td width="324" class="a" id="d2" bgcolor="F4F3EE">
                <% FOR i = 1 TO nTrans
                Response.Write (eval(vrsHours&i))
                NEXT %>h:m</td>
              </tr>
              <tr>
                <td colspan="2" height="6"></td>
              </tr>
              <tr>
                <td width="166" class="a" align="right"><b>Estado [u]A</u>ctual&nbsp;:&nbsp;</b></td>
                <td width="324" class="a" bgcolor="F4F3EE">AUSENTE</td>
              </tr>
              <tr>
                <td width="490" class="a" colspan="2">&nbsp;</td>
              </tr>
            </table>
            </td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" colspan="2" align="center">
            <table border="0" cellpadding="2" cellspacing="0" width="633">
              <tr>
                <td height="5"></td>
              </tr>
              <tr>
                <td height="19" class="tm">&nbsp;</td>
              </tr>
              <tr>
                <td height="8"></td>
              </tr>
            </table>
            </td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" colspan="2">
            <img src="img/pixel.gif" width="5" height="5"></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td colspan="2" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
        </table>
        <table>
          <tr>
            <td height="5"></td>
          </tr>
        </table>
        <table border="0" cellpadding="0" cellspacing="0" width="650" background="img/xpbackgrnd.jpg">
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td colspan="2" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="648" colspan="2" class="a" align="center" height="22"><b>Horas:</b>&nbsp;<b>Hoy</b>&nbsp;|&nbsp;<a href="semana.asp?StaffNum=<%=oRSbt("CardId")%>">Esta Semana</a>&nbsp;|&nbsp;<a href="mes.asp?StaffNum=<%=oRSbt("CardId")%>">Este Mes</a>&nbsp;|&nbsp;<a href="ano.asp?StaffNum=<%=oRSbt("CardId")%>">Este Año</a>&nbsp;|&nbsp;<a href="faltas.asp?StaffNum=<%=oRSbt("CardId")%>">Faltas</a>&nbsp;|&nbsp;<a href="retardos.asp?StaffNum=<%=oRSbt("CardId")%>">Retardos</a>&nbsp;|&nbsp;<a href="textra.asp?StaffNum=<%=oRSbt("CardId")%>">Tiempo Extra</a></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
          <tr>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td colspan="2" bgcolor="919B9D"><img src="img/pixel.gif"></td>
            <td width="1" bgcolor="919B9D"><img src="img/pixel.gif"></td>
          </tr>
        </table>
        <table>
          <tr>
            <td height="5"></td>
          </tr>
        </table>
        <table border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
            <td width="100%" align="center"></td>
          </tr>
        </table>
        </td>
        <td width="9" bgcolor="ECE9D8">
        <img src="img/pixel.gif" width="9" height="1"></td>
        <td width="2" class="tm"><img src="img/pixel.gif" width="2" height="1"></td>
      </tr>
    </table>
    <table width="700" cellpadding="0" cellspacing="0" border="0">
      <tr class="tm">
        <td width="700"><img src="img/pixel.gif" width="1" height="2"></td>
      </tr>
    </table>
    <table>
      <tr>
        <td height="10"></td>
      </tr>
    </table>
    </td>
    <td width="1" class="tb"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
</table>
</td>

<table width="750" cellpadding="0" cellspacing="0" border="0" align="center">
  <tr class="th">
    <td class="tb" width="1"><img src="img/pixel.gif" width="1" height="1"></td>
    <td width="10"><img src="img/pixel.gif" width="10" height="1"></td>
    <td class="info" width="506">
    <table width="100%" cellpadding="0" cellspacing="0" border="0">
      <tr>
        <td height="6"></td>
      </tr>
    </table>
    <b>Servicios Interactivos Internacionales S.A. de C.V.</b><br>

    <table width="100%" cellpadding="0" cellspacing="0" border="0">
      <tr>
        <td height="6"></td>
      </tr>
      <tr>
        <td class="tm"><img src="img/pixel.gif" width="1" height="1"></td>
      </tr>
      <tr>
        <td height="6"></td>
      </tr>
    </table>
    © 2002 Servicios Interactivos Internacionales, Todos los Derechos Reservados
    <table width="100%" cellpadding="0" cellspacing="0" border="0">
      <tr>
        <td height="6"></td>
      </tr>
    </table>
    </td>
    <td width="232" align="center">
    <a href="http://www.grupo-sii.com/" title="Servicios Interactivos Internacionales S.A. de C.V.">
    <img border="0" src="img/sii_dblue.gif"></a></td>
    <td class="tb" width="1"><img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
  <tr>
    <td class="tb" colspan="5" width="750">
    <img src="img/pixel.gif" width="1" height="1"></td>
  </tr>
</table>
</center>
<%
oRSbt.CLOSE
SET oRSbt=NOTHING
oRSbn.CLOSE
SET oRSbn=NOTHING
oConn.CLOSE
SET oConn=NOTHING
%>
</body>

</html>
Look carefully and you can see where the code vrsHours goes in the Horas part. Unfortunately this is outside the main for next loop.

I want to use the returned dynamic variable in calculations in order to figure out the total hours work during the day.

Any help in resolve this problem would be greatly received and I will always be in your debt.

Many thanks,
neo_jakey

Reply With Quote