<% '"SELECT PeopleID, PeopleName, PeopleEmail, PeopleOrderMilk, PeopleOrderButter, PeopleOrderCream FROM tblpeople" '***************************************************** '* Function GetClientLoginInfo() '* Get a specific client using either email or username '***************************************************** Function GetLoginInfo(sEmail) Dim sSQL,sSQLEmailUser 'check if it is a username or password If InStr(sEmail,"@") > 0 AND InStr(sEmail,".") > 0 Then 'looks like an email address with the @ sign sSQLEmailUser="WHERE PeopleEmail='" & parsfield(sEmail) & "'" End If sSQL = "SELECT PeopleID, PeopleName, PeopleEmail, PeopleOrderMilk, PeopleOrderButter, PeopleOrderCream " &_ "FROM tblpeople " &_ sSQLEmailUser Set GetLoginInfo = DBExecute(sSQL) End Function '***************************************************** '* Function GetList() '* Get a list of members and the bottles returned '***************************************************** Function GetList() Dim sSQL,sSQLEmailUser 'check there is an id sSQL = "SELECT t1.OID, t1.OPeopleID, t1.ODate, t1.OBottlesReturned, t2.PeopleID, t2.PeopleName, t2.PeopleEmail, "&_ "t2.PeopleOrderMilk, t2.PeopleOrderButter, t2.PeopleOrderCream "&_ "FROM tblpeople AS t2 LEFT JOIN tblorders AS t1 ON t2.PeopleID = t1.OPeopleID " ' "FROM tblpeople AS t2 LEFT JOIN tblorders AS t1 ON t2.PeopleID = t1.OPeopleID "&_ ' "WHERE DATE(t1.ODate) < DATE(NOW())" ' "WHERE t1.ODate < " & mysqlDate(dateadd("d",2,date()),1) ' sSQL = "SELECT PeopleID, PeopleName, PeopleEmail, PeopleOrderMilk, PeopleOrderButter, PeopleOrderCream " &_ ' "FROM tblpeople" Set GetList = DBExecute(sSQL) End Function '***************************************************** '* Function GetUserList() '* Get a list of all active members '***************************************************** Function GetUserList() Dim sSQL,sSQLEmailUser 'check there is an id sSQL = "SELECT PeopleID, PeopleName, PeopleEmail, PeopleOrderMilk, PeopleOrderButter, PeopleOrderCream, PeopleZeroPrice "&_ "FROM tblpeople "&_ "WHERE PeopleActive = 1 "&_ "ORDER BY PeopleName" ' "FROM tblpeople AS t2 LEFT JOIN tblorders AS t1 ON t2.PeopleID = t1.OPeopleID "&_ ' "WHERE DATE(t1.ODate) < DATE(NOW())" ' "WHERE t1.ODate < " & mysqlDate(dateadd("d",2,date()),1) ' sSQL = "SELECT PeopleID, PeopleName, PeopleEmail, PeopleOrderMilk, PeopleOrderButter, PeopleOrderCream " &_ ' "FROM tblpeople" Set GetUserList = DBExecute(sSQL) End Function '***************************************************** '* Sub GetBottlesReturned(pid,bottles,orderdate) '* Get the number of bottles returned for a specific user '***************************************************** Sub GetBottlesReturned(pid,bottles,orderdate) Dim sSQL,sSQLEmailUser, rsTemp, bOrderDateValid bOrderDateValid=false 'check there is an id 'setup dates to check against. 'SELECT OID, OPeopleID, ODate, OBottlesReturned FROM tblorders WHERE OPeopleID = 40 AND DATE(2013-2-6) > DATE(2013-1-30) AND DATE(2013-2-6) < DATE(2013-2-13) iDateClosed=orderdate iLastOrderDate=DateAdd("d",-7,iDateClosed) iNextOrderDate=DateAdd("d",7,iDateClosed) iDisplayOrderDate=DateAdd("d",-1,iDateClosed) iTodaysDate=Date() 'response.write iDateClosed & " _ " & iLastOrderDate & " _ " & iNextOrderDate & " _ " & iTodaysDate & "
" If DateValue(iTodaysDate) < DateValue(iDateClosed) AND DateValue(iTodaysDate) > iLastOrderDate Then 'Response.Write "This date is before the od
" sSQL = "SELECT OID, OPeopleID, ODate, OBottlesReturned "&_ "FROM tblorders "&_ "WHERE OPeopleID = " & pid &_ " AND ODate > '"&mysqlDate(iLastOrderDate,1)&"'"&_ " AND ODate < '"&mysqlDate(iDateClosed,1)&"'" 'response.write sSQL &"
" Set rsTemp = DBExecute(sSQL) bOrderDateValid=true Elseif DateValue(iTodaysDate) = DateValue(iDateClosed) Then 'Response.Write "This date is the od
" sSQL = "SELECT OID, OPeopleID, ODate, OBottlesReturned "&_ "FROM tblorders "&_ "WHERE OPeopleID = " & pid &_ " AND ODate > '"&mysqlDate(iDateClosed,1)&"'"&_ " AND ODate < '"&mysqlDate(iNextOrderDate,1)&"'" 'response.write sSQL &"
" Set rsTemp = DBExecute(sSQL) bOrderDateValid=true Else 'Response.Write "This date is after the od
" End If 'Response.write iPID&", "&pid&", "&bOrderDateValid&", "&iTodaysDate&", "&iDisplayOrderDate&"
" If Not rsTemp.EOF Then 'response.write "
"&bOrderDateValid &"
" 'compare logged persons id with each id If iPID = pid AND bOrderDateValid AND iTodaysDate < iDisplayOrderDate Then If rsTemp("OBottlesReturned") <> "" Then Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf Else Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf End If ElseIf iPID = pid AND bOrderDateValid AND iTodaysDate = iDisplayOrderDate Then Response.write bottles&vbcrlf Else If rsTemp("OBottlesReturned") <> "" Then response.write rsTemp("OBottlesReturned") Else response.write "0" End If End If Elseif iPID = pid AND bOrderDateValid Then Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf Response.write ""&vbcrlf End If End Sub Function CheckOrderDate() sDate = Date() 'Response.write sDate & "
" sDay=WeekDay(sDate) 'Response.write sDay & ", "&SClosedOrder&" - weekday Sunday 1 to Saturday 7
" If cint(sDay) < SClosedOrder Then CheckOrderDate=DateAdd("d",(SClosedOrder-sDay),sDate) Elseif cint(sDay) = SClosedOrder Then CheckOrderDate = sDate Else CheckOrderDate=DateAdd("d",(SClosedOrder-sDay+7),sDate) End If 'response.write CheckOrderDate & " - Order Date
" End Function function mysqlDate(d,dir) 'if not isDate( d ) then call errorMessage( d & " is not a date " ) 'if not isDate( d ) then exit function if not isDate( d ) then d = Date() dim strNewDate select case dir case 1 '=== store in db strNewDate = year( d ) & "-" & month( d ) & "-" & day( d ) case 2 '=== use with asp strNewDate = month( d )& "/" & day( d ) & "/" & year( d ) end select 'strNewDate = cDate( strNewDate ) mysqlDate = strNewDate end function %>