<%@ LCID = 5129 %> <% Response.expires = 0 Response.expiresabsolute = Now() - 1 Response.addHeader "pragma", "no-cache" Response.addHeader "cache-control", "private" Response.CacheControl = "no-cache" %> <% ewCurSec = 0 ' Initialise ' User levels Const ewAllowAdd = 1 Const ewAllowDelete = 2 Const ewAllowEdit = 4 Const ewAllowView = 8 Const ewAllowList = 8 Const ewAllowReport = 8 Const ewAllowSearch = 8 Const ewAllowAdmin = 16 %> <% ' Initialize common variables x_id = Null x_title = Null x_first_name = Null x_last_name = Null x_dob = Null x_sex = Null x_initials = Null x_address = Null x_post_code = Null x_tel = Null x_mob = Null x_fax = Null x_form = Null x_adm_no = Null x_daily_spend = Null x_MAL = Null x_MAL_date_from = Null x_MAL_date_to = Null x_MAL_time_from = Null x_MAL_time_to = Null x_user_group = Null x_updated = Null x_report = Null x_image = Null fs_x_image = 0 fn_x_image = "" ct_x_image = "" w_x_image = 0 h_x_image = 0 a_x_image = "" x_csn = Null x_today = Null %> <% sExport = Request.QueryString("export") ' Load Export Request If sExport = "excel" Then Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment; filename=user_data.xls" End If If sExport = "word" Then Response.ContentType = "application/vnd.ms-word" Response.AddHeader "Content-Disposition:", "attachment; filename=user_data.doc" End If If sExport = "xml" Then Response.ContentType = "text/xml" Response.AddHeader "Content-Disposition:", "attachment; filename=user_data.xml" End If %> <% nStartRec = 0 nStopRec = 0 nTotalRecs = 0 nRecCount = 0 nRecActual = 0 sKeyMaster = "" sDbWhereMaster = "" sSrchAdvanced = "" sSrchBasic = "" sSrchWhere = "" sDbWhere = "" sDefaultOrderBy = "" sDefaultFilter = "" sWhere = "" sGroupBy = "" sHaving = "" sOrderBy = "" sSqlMaster = "" nDisplayRecs = 20 nRecRange = 10 ' Open connection to the database Set conn = Server.CreateObject("ADODB.Connection") conn.Open xDb_Conn_Str ' Handle Reset Command ResetCmd() ' Get Search Criteria for Basic Search SetUpBasicSearch() ' Build Search Criteria If sSrchAdvanced <> "" Then sSrchWhere = sSrchAdvanced ' Advanced Search ElseIf sSrchBasic <> "" Then sSrchWhere = sSrchBasic ' Basic Search End If ' Save Search Criteria If sSrchWhere <> "" Then Session("user_data_searchwhere") = sSrchWhere ' Reset start record counter (new search) nStartRec = 1 Session("user_data_REC") = nStartRec Else sSrchWhere = Session("user_data_searchwhere") End If ' Build WHERE condition sDbWhere = "" If sDbWhereMaster <> "" Then sDbWhere = sDbWhere & "(" & sDbWhereMaster & ") AND " End If If sSrchWhere <> "" Then sDbWhere = sDbWhere & "(" & sSrchWhere & ") AND " End If If Len(sDbWhere) > 5 Then sDbWhere = Mid(sDbWhere, 1, Len(sDbWhere)-5) ' Trim rightmost AND End If ' Build SQL sSql = "SELECT * FROM [user_data]" ' Load Default Filter sDefaultFilter = "" sGroupBy = "" sHaving = "" ' Load Default Order sDefaultOrderBy = "" sWhere = "" If sDefaultFilter <> "" Then sWhere = sWhere & "(" & sDefaultFilter & ") AND " End If If sDbWhere <> "" Then sWhere = sWhere & "(" & sDbWhere & ") AND " End If If Right(sWhere, 5) = " AND " Then sWhere = Left(sWhere, Len(sWhere)-5) If sWhere <> "" Then sSql = sSql & " WHERE " & sWhere End If If sGroupBy <> "" Then sSql = sSql & " GROUP BY " & sGroupBy End If If sHaving <> "" Then sSql = sSql & " HAVING " & sHaving End If ' Set Up Sorting Order sOrderBy = "" SetUpSortOrder() If sOrderBy <> "" Then sSql = sSql & " ORDER BY " & sOrderBy End If 'Session("ewmsg") = sSql ' Uncomment to show SQL for debugging ' Export Data only If sExport = "xml" Or sExport = "csv" Then Call ExportData(sExport, sSql) conn.Close ' Close Connection Set conn = Nothing Response.End End If %> <% If sExport <> "word" And sExport <> "excel" Then %> <% End If %> <% ' Set up Record Set Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.Open sSql, conn, 1, 2 nTotalRecs = rs.RecordCount If nDisplayRecs <= 0 Then ' Display All Records nDisplayRecs = nTotalRecs End If nStartRec = 1 SetUpStartRec() ' Set Up Start Record Position %>

TABLE: user data <% If sExport = "" Then %>   Export to Excel   Export to Word   Export to XML <% End If %>

<% If sExport = "" Then %>
   Show all  
Exact phrase  All words  Any word
<% End If %> <% If Session("ewmsg") <> "" Then %>

<%= Session("ewmsg") %>

<% Session("ewmsg") = "" ' Clear message End If %>
<% If nTotalRecs > 0 Then %> <% If sExport = "" Then %> <% End If %> <% End If %> <% ' Avoid starting record > total records If CLng(nStartRec) > CLng(nTotalRecs) Then nStartRec = nTotalRecs End If ' Set the last record to display nStopRec = nStartRec + nDisplayRecs - 1 ' Move to first record directly for performance reason nRecCount = nStartRec - 1 If Not rs.Eof Then rs.MoveFirst rs.Move nStartRec - 1 End If nRecActual = 0 Do While (Not rs.Eof) And (nRecCount < nStopRec) nRecCount = nRecCount + 1 If CLng(nRecCount) >= CLng(nStartRec) Then nRecActual = nRecActual + 1 ' Set row color sItemRowClass = " class=""ewTableRow""" ' Display alternate color for rows If nRecCount Mod 2 <> 0 Then sItemRowClass = " class=""ewTableAltRow""" End If x_id = rs("id") x_title = rs("title") x_first_name = rs("first_name") x_last_name = rs("last_name") x_dob = rs("dob") x_sex = rs("sex") x_initials = rs("initials") x_address = rs("address") x_post_code = rs("post_code") x_tel = rs("tel") x_mob = rs("mob") x_fax = rs("fax") x_form = rs("form") x_adm_no = rs("adm_no") x_daily_spend = rs("daily_spend") x_MAL = rs("MAL") x_MAL_date_from = rs("MAL_date_from") x_MAL_date_to = rs("MAL_date_to") x_MAL_time_from = rs("MAL_time_from") x_MAL_time_to = rs("MAL_time_to") x_user_group = rs("user_group") x_updated = rs("updated") x_report = rs("report") x_image = rs("image") x_csn = rs("csn") x_today = rs("today") %> > <% If sExport = "" Then %> <% End If %> <% End If rs.MoveNext Loop %>
<% If sExport <> "" Then %> id <% Else %> " style="color: #FFFFFF;">id<% If Session("user_data_x_id_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_id_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> title <% Else %> " style="color: #FFFFFF;">title (*)<% If Session("user_data_x_title_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_title_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> first name <% Else %> " style="color: #FFFFFF;">first name (*)<% If Session("user_data_x_first_name_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_first_name_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> last name <% Else %> " style="color: #FFFFFF;">last name (*)<% If Session("user_data_x_last_name_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_last_name_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> dob <% Else %> " style="color: #FFFFFF;">dob<% If Session("user_data_x_dob_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_dob_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> sex <% Else %> " style="color: #FFFFFF;">sex (*)<% If Session("user_data_x_sex_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_sex_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> initials <% Else %> " style="color: #FFFFFF;">initials (*)<% If Session("user_data_x_initials_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_initials_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> address <% Else %> " style="color: #FFFFFF;">address (*)<% If Session("user_data_x_address_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_address_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> post code <% Else %> " style="color: #FFFFFF;">post code (*)<% If Session("user_data_x_post_code_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_post_code_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> tel <% Else %> " style="color: #FFFFFF;">tel (*)<% If Session("user_data_x_tel_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_tel_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> mob <% Else %> " style="color: #FFFFFF;">mob (*)<% If Session("user_data_x_mob_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_mob_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> fax <% Else %> " style="color: #FFFFFF;">fax (*)<% If Session("user_data_x_fax_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_fax_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> form <% Else %> " style="color: #FFFFFF;">form (*)<% If Session("user_data_x_form_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_form_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> adm no <% Else %> " style="color: #FFFFFF;">adm no (*)<% If Session("user_data_x_adm_no_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_adm_no_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> daily spend <% Else %> " style="color: #FFFFFF;">daily spend<% If Session("user_data_x_daily_spend_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_daily_spend_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> MAL <% Else %> " style="color: #FFFFFF;">MAL<% If Session("user_data_x_MAL_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_MAL_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> MAL date from <% Else %> " style="color: #FFFFFF;">MAL date from (*)<% If Session("user_data_x_MAL_date_from_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_MAL_date_from_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> MAL date to <% Else %> " style="color: #FFFFFF;">MAL date to (*)<% If Session("user_data_x_MAL_date_to_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_MAL_date_to_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> MAL time from <% Else %> " style="color: #FFFFFF;">MAL time from (*)<% If Session("user_data_x_MAL_time_from_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_MAL_time_from_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> MAL time to <% Else %> " style="color: #FFFFFF;">MAL time to (*)<% If Session("user_data_x_MAL_time_to_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_MAL_time_to_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> user group <% Else %> " style="color: #FFFFFF;">user group (*)<% If Session("user_data_x_user_group_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_user_group_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> updated <% Else %> " style="color: #FFFFFF;">updated<% If Session("user_data_x_updated_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_updated_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> csn <% Else %> " style="color: #FFFFFF;">csn (*)<% If Session("user_data_x_csn_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_csn_Sort") = "DESC" Then %><% End If %> <% End If %> <% If sExport <> "" Then %> today <% Else %> " style="color: #FFFFFF;">today<% If Session("user_data_x_today_Sort") = "ASC" Then %><% ElseIf Session("user_data_x_today_Sort") = "DESC" Then %><% End If %> <% End If %>
<% Response.Write x_id %> <% Response.Write x_title %> <% Response.Write x_first_name %> <% Response.Write x_last_name %> <% If IsDate(x_dob) Then Response.Write EW_FormatDateTime(x_dob,7) Else Response.Write x_dob End If %> <% Response.Write x_sex %> <% Response.Write x_initials %> <% Response.Write x_address %> <% Response.Write x_post_code %> <% Response.Write x_tel %> <% Response.Write x_mob %> <% Response.Write x_fax %> <% Response.Write x_form %> <% Response.Write x_adm_no %> <% Response.Write x_daily_spend %> <% If x_MAL = True Then sTmp = "Yes" Else sTmp = "No" End If ox_MAL = x_MAL ' Backup Original Value x_MAL = sTmp %> <% Response.Write x_MAL %> <% x_MAL = ox_MAL ' Restore Original Value %> <% Response.Write x_MAL_date_from %> <% Response.Write x_MAL_date_to %> <% Response.Write x_MAL_time_from %> <% Response.Write x_MAL_time_to %> <% Response.Write x_user_group %> <% If x_updated = True Then sTmp = "Yes" Else sTmp = "No" End If ox_updated = x_updated ' Backup Original Value x_updated = sTmp %> <% Response.Write x_updated %> <% x_updated = ox_updated ' Restore Original Value %> <% Response.Write x_csn %> <% If x_today = True Then sTmp = "Yes" Else sTmp = "No" End If ox_today = x_today ' Backup Original Value x_today = sTmp %> <% Response.Write x_today %> <% x_today = ox_today ' Restore Original Value %>
<% ' Close recordset and connection rs.Close Set rs = Nothing conn.Close Set conn = Nothing %> <% If sExport = "" Then %>
<% If nTotalRecs > 0 Then rsEof = (nTotalRecs < (nStartRec + nDisplayRecs)) PrevStart = nStartRec - nDisplayRecs If PrevStart < 1 Then PrevStart = 1 NextStart = nStartRec + nDisplayRecs If NextStart > nTotalRecs Then NextStart = nStartRec LastStart = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1 %> <% If CLng(nStartRec)=1 Then %> <% Else %> <% End If %> <% If CLng(PrevStart) = CLng(nStartRec) Then %> <% Else %> <% End If %> <% If CLng(NextStart) = CLng(nStartRec) Then %> <% Else %> <% End If %> <% If CLng(LastStart) = CLng(nStartRec) Then %> <% Else %> <% End If %>
Page  FirstFirstPreviousPrevious NextNextLastLast of <%=(nTotalRecs-1)\nDisplayRecs+1%>
<% If CLng(nStartRec) > CLng(nTotalRecs) Then nStartRec = nTotalRecs nStopRec = nStartRec + nDisplayRecs - 1 nRecCount = nTotalRecs - 1 If rsEOF Then nRecCount = nTotalRecs If nStopRec > nRecCount Then nStopRec = nRecCount %> Records <%= nStartRec %> to <%= nStopRec %> of <%= nTotalRecs %> <% Else %> No records found <% End If %>
<% End If %> <% If sExport <> "word" And sExport <> "excel" Then %> <% End If %> <% '------------------------------------------------------------------------------- ' Function BasicSearchSQL ' - Build WHERE clause for a keyword Function BasicSearchSQL(Keyword) Dim sKeyword sKeyword = AdjustSql(Keyword) BasicSearchSQL = "" BasicSearchSQL = BasicSearchSQL & "[title] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[first_name] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[last_name] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[sex] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[initials] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[address] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[post_code] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[tel] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[mob] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[fax] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[form] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[adm_no] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[MAL_date_from] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[MAL_date_to] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[MAL_time_from] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[MAL_time_to] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[user_group] LIKE '%" & sKeyword & "%' OR " BasicSearchSQL = BasicSearchSQL & "[csn] LIKE '%" & sKeyword & "%' OR " If Right(BasicSearchSQL, 4) = " OR " Then BasicSearchSQL = Left(BasicSearchSQL, Len(BasicSearchSQL)-4) End Function '------------------------------------------------------------------------------- ' Function SetUpBasicSearch ' - Set up Basic Search parameter based on form elements pSearch & pSearchType ' - Variables setup: sSrchBasic Sub SetUpBasicSearch() Dim sSearch, sSearchType, arKeyword, sKeyword sSearch = Request.QueryString("psearch") sSearchType = Request.QueryString("psearchType") If sSearch <> "" Then If sSearchType <> "" Then While InStr(sSearch, " ") > 0 sSearch = Replace(sSearch, " ", " ") Wend arKeyword = Split(Trim(sSearch), " ") For Each sKeyword In arKeyword sSrchBasic = sSrchBasic & "(" & BasicSearchSQL(sKeyword) & ") " & sSearchType & " " Next Else sSrchBasic = BasicSearchSQL(sSearch) End If End If If Right(sSrchBasic, 4) = " OR " Then sSrchBasic = Left(sSrchBasic, Len(sSrchBasic)-4) If Right(sSrchBasic, 5) = " AND " Then sSrchBasic = Left(sSrchBasic, Len(sSrchBasic)-5) End Sub '------------------------------------------------------------------------------- ' Function SetUpSortOrder ' - Set up Sort parameters based on Sort Links clicked ' - Variables setup: sOrderBy, Session("Table_OrderBy"), Session("Table_Field_Sort") Sub SetUpSortOrder() Dim sOrder, sSortField, sLastSort, sThisSort Dim bCtrl ' Check for an Order parameter If Request.QueryString("order").Count > 0 Then sOrder = Request.QueryString("order") ' Field id If sOrder = "id" Then sSortField = "[id]" sLastSort = Session("user_data_x_id_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_id_Sort") = sThisSort Else If Session("user_data_x_id_Sort") <> "" Then Session("user_data_x_id_Sort") = "" End If ' Field title If sOrder = "title" Then sSortField = "[title]" sLastSort = Session("user_data_x_title_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_title_Sort") = sThisSort Else If Session("user_data_x_title_Sort") <> "" Then Session("user_data_x_title_Sort") = "" End If ' Field first_name If sOrder = "first_name" Then sSortField = "[first_name]" sLastSort = Session("user_data_x_first_name_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_first_name_Sort") = sThisSort Else If Session("user_data_x_first_name_Sort") <> "" Then Session("user_data_x_first_name_Sort") = "" End If ' Field last_name If sOrder = "last_name" Then sSortField = "[last_name]" sLastSort = Session("user_data_x_last_name_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_last_name_Sort") = sThisSort Else If Session("user_data_x_last_name_Sort") <> "" Then Session("user_data_x_last_name_Sort") = "" End If ' Field dob If sOrder = "dob" Then sSortField = "[dob]" sLastSort = Session("user_data_x_dob_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_dob_Sort") = sThisSort Else If Session("user_data_x_dob_Sort") <> "" Then Session("user_data_x_dob_Sort") = "" End If ' Field sex If sOrder = "sex" Then sSortField = "[sex]" sLastSort = Session("user_data_x_sex_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_sex_Sort") = sThisSort Else If Session("user_data_x_sex_Sort") <> "" Then Session("user_data_x_sex_Sort") = "" End If ' Field initials If sOrder = "initials" Then sSortField = "[initials]" sLastSort = Session("user_data_x_initials_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_initials_Sort") = sThisSort Else If Session("user_data_x_initials_Sort") <> "" Then Session("user_data_x_initials_Sort") = "" End If ' Field address If sOrder = "address" Then sSortField = "[address]" sLastSort = Session("user_data_x_address_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_address_Sort") = sThisSort Else If Session("user_data_x_address_Sort") <> "" Then Session("user_data_x_address_Sort") = "" End If ' Field post_code If sOrder = "post_code" Then sSortField = "[post_code]" sLastSort = Session("user_data_x_post_code_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_post_code_Sort") = sThisSort Else If Session("user_data_x_post_code_Sort") <> "" Then Session("user_data_x_post_code_Sort") = "" End If ' Field tel If sOrder = "tel" Then sSortField = "[tel]" sLastSort = Session("user_data_x_tel_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_tel_Sort") = sThisSort Else If Session("user_data_x_tel_Sort") <> "" Then Session("user_data_x_tel_Sort") = "" End If ' Field mob If sOrder = "mob" Then sSortField = "[mob]" sLastSort = Session("user_data_x_mob_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_mob_Sort") = sThisSort Else If Session("user_data_x_mob_Sort") <> "" Then Session("user_data_x_mob_Sort") = "" End If ' Field fax If sOrder = "fax" Then sSortField = "[fax]" sLastSort = Session("user_data_x_fax_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_fax_Sort") = sThisSort Else If Session("user_data_x_fax_Sort") <> "" Then Session("user_data_x_fax_Sort") = "" End If ' Field form If sOrder = "form" Then sSortField = "[form]" sLastSort = Session("user_data_x_form_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_form_Sort") = sThisSort Else If Session("user_data_x_form_Sort") <> "" Then Session("user_data_x_form_Sort") = "" End If ' Field adm_no If sOrder = "adm_no" Then sSortField = "[adm_no]" sLastSort = Session("user_data_x_adm_no_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_adm_no_Sort") = sThisSort Else If Session("user_data_x_adm_no_Sort") <> "" Then Session("user_data_x_adm_no_Sort") = "" End If ' Field daily_spend If sOrder = "daily_spend" Then sSortField = "[daily_spend]" sLastSort = Session("user_data_x_daily_spend_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_daily_spend_Sort") = sThisSort Else If Session("user_data_x_daily_spend_Sort") <> "" Then Session("user_data_x_daily_spend_Sort") = "" End If ' Field MAL If sOrder = "MAL" Then sSortField = "[MAL]" sLastSort = Session("user_data_x_MAL_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_MAL_Sort") = sThisSort Else If Session("user_data_x_MAL_Sort") <> "" Then Session("user_data_x_MAL_Sort") = "" End If ' Field MAL_date_from If sOrder = "MAL_date_from" Then sSortField = "[MAL_date_from]" sLastSort = Session("user_data_x_MAL_date_from_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_MAL_date_from_Sort") = sThisSort Else If Session("user_data_x_MAL_date_from_Sort") <> "" Then Session("user_data_x_MAL_date_from_Sort") = "" End If ' Field MAL_date_to If sOrder = "MAL_date_to" Then sSortField = "[MAL_date_to]" sLastSort = Session("user_data_x_MAL_date_to_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_MAL_date_to_Sort") = sThisSort Else If Session("user_data_x_MAL_date_to_Sort") <> "" Then Session("user_data_x_MAL_date_to_Sort") = "" End If ' Field MAL_time_from If sOrder = "MAL_time_from" Then sSortField = "[MAL_time_from]" sLastSort = Session("user_data_x_MAL_time_from_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_MAL_time_from_Sort") = sThisSort Else If Session("user_data_x_MAL_time_from_Sort") <> "" Then Session("user_data_x_MAL_time_from_Sort") = "" End If ' Field MAL_time_to If sOrder = "MAL_time_to" Then sSortField = "[MAL_time_to]" sLastSort = Session("user_data_x_MAL_time_to_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_MAL_time_to_Sort") = sThisSort Else If Session("user_data_x_MAL_time_to_Sort") <> "" Then Session("user_data_x_MAL_time_to_Sort") = "" End If ' Field user_group If sOrder = "user_group" Then sSortField = "[user_group]" sLastSort = Session("user_data_x_user_group_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_user_group_Sort") = sThisSort Else If Session("user_data_x_user_group_Sort") <> "" Then Session("user_data_x_user_group_Sort") = "" End If ' Field updated If sOrder = "updated" Then sSortField = "[updated]" sLastSort = Session("user_data_x_updated_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_updated_Sort") = sThisSort Else If Session("user_data_x_updated_Sort") <> "" Then Session("user_data_x_updated_Sort") = "" End If ' Field csn If sOrder = "csn" Then sSortField = "[csn]" sLastSort = Session("user_data_x_csn_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_csn_Sort") = sThisSort Else If Session("user_data_x_csn_Sort") <> "" Then Session("user_data_x_csn_Sort") = "" End If ' Field today If sOrder = "today" Then sSortField = "[today]" sLastSort = Session("user_data_x_today_Sort") If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC" Session("user_data_x_today_Sort") = sThisSort Else If Session("user_data_x_today_Sort") <> "" Then Session("user_data_x_today_Sort") = "" End If Session("user_data_OrderBy") = sSortField & " " & sThisSort Session("user_data_REC") = 1 End If sOrderBy = Session("user_data_OrderBy") If sOrderBy = "" Then sOrderBy = sDefaultOrderBy Session("user_data_OrderBy") = sOrderBy End If End Sub '------------------------------------------------------------------------------- ' Function SetUpStartRec ' - Set up Starting Record parameters based on Pager Navigation ' - Variables setup: nStartRec Sub SetUpStartRec() Dim nPageNo ' Check for a START parameter If Request.QueryString("start").Count > 0 Then nStartRec = Request.QueryString("start") Session("user_data_REC") = nStartRec ElseIf Request.QueryString("pageno").Count > 0 Then nPageNo = Request.QueryString("pageno") If IsNumeric(nPageNo) Then nStartRec = (nPageNo-1)*nDisplayRecs+1 If nStartRec <= 0 Then nStartRec = 1 ElseIf nStartRec >= ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1 Then nStartRec = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1 End If Session("user_data_REC") = nStartRec Else nStartRec = Session("user_data_REC") If Not IsNumeric(nStartRec) Or nStartRec = "" Then nStartRec = 1 ' Reset start record counter Session("user_data_REC") = nStartRec End If End If Else nStartRec = Session("user_data_REC") If Not IsNumeric(nStartRec) Or nStartRec = "" Then nStartRec = 1 'Reset start record counter Session("user_data_REC") = nStartRec End If End If End Sub '------------------------------------------------------------------------------- ' Function ResetCmd ' - Clear list page parameters ' - RESET: reset search parameters ' - RESETALL: reset search & master/detail parameters ' - RESETSORT: reset sort parameters Sub ResetCmd() Dim sCmd ' Get Reset Cmd If Request.QueryString("cmd").Count > 0 Then sCmd = Request.QueryString("cmd") ' Reset Search Criteria If UCase(sCmd) = "RESET" Then sSrchWhere = "" Session("user_data_searchwhere") = sSrchWhere ' Reset Search Criteria & Session Keys ElseIf UCase(sCmd) = "RESETALL" Then sSrchWhere = "" Session("user_data_searchwhere") = sSrchWhere ' Reset Sort Criteria ElseIf UCase(sCmd) = "RESETSORT" Then sOrderBy = "" Session("user_data_OrderBy") = sOrderBy If Session("user_data_x_id_Sort") <> "" Then Session("user_data_x_id_Sort") = "" If Session("user_data_x_title_Sort") <> "" Then Session("user_data_x_title_Sort") = "" If Session("user_data_x_first_name_Sort") <> "" Then Session("user_data_x_first_name_Sort") = "" If Session("user_data_x_last_name_Sort") <> "" Then Session("user_data_x_last_name_Sort") = "" If Session("user_data_x_dob_Sort") <> "" Then Session("user_data_x_dob_Sort") = "" If Session("user_data_x_sex_Sort") <> "" Then Session("user_data_x_sex_Sort") = "" If Session("user_data_x_initials_Sort") <> "" Then Session("user_data_x_initials_Sort") = "" If Session("user_data_x_address_Sort") <> "" Then Session("user_data_x_address_Sort") = "" If Session("user_data_x_post_code_Sort") <> "" Then Session("user_data_x_post_code_Sort") = "" If Session("user_data_x_tel_Sort") <> "" Then Session("user_data_x_tel_Sort") = "" If Session("user_data_x_mob_Sort") <> "" Then Session("user_data_x_mob_Sort") = "" If Session("user_data_x_fax_Sort") <> "" Then Session("user_data_x_fax_Sort") = "" If Session("user_data_x_form_Sort") <> "" Then Session("user_data_x_form_Sort") = "" If Session("user_data_x_adm_no_Sort") <> "" Then Session("user_data_x_adm_no_Sort") = "" If Session("user_data_x_daily_spend_Sort") <> "" Then Session("user_data_x_daily_spend_Sort") = "" If Session("user_data_x_MAL_Sort") <> "" Then Session("user_data_x_MAL_Sort") = "" If Session("user_data_x_MAL_date_from_Sort") <> "" Then Session("user_data_x_MAL_date_from_Sort") = "" If Session("user_data_x_MAL_date_to_Sort") <> "" Then Session("user_data_x_MAL_date_to_Sort") = "" If Session("user_data_x_MAL_time_from_Sort") <> "" Then Session("user_data_x_MAL_time_from_Sort") = "" If Session("user_data_x_MAL_time_to_Sort") <> "" Then Session("user_data_x_MAL_time_to_Sort") = "" If Session("user_data_x_user_group_Sort") <> "" Then Session("user_data_x_user_group_Sort") = "" If Session("user_data_x_updated_Sort") <> "" Then Session("user_data_x_updated_Sort") = "" If Session("user_data_x_csn_Sort") <> "" Then Session("user_data_x_csn_Sort") = "" If Session("user_data_x_today_Sort") <> "" Then Session("user_data_x_today_Sort") = "" End If ' Reset Start Position (Reset Command) nStartRec = 1 Session("user_data_REC") = nStartRec End If End Sub '------------------------------------------------------------------------------- ' Function ExportData ' - Export Data in Xml or Csv format Sub ExportData(sExport, sSql) Dim oXmlDoc, oXmlTbl, oXmlRec, oXmlFld Dim sCsvStr Dim rs ' Set up Record Set Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.Open sSql, conn, 1, 2 nTotalRecs = rs.RecordCount nStartRec = 1 SetUpStartRec() ' Set Up Start Record Position If sExport = "xml" Then Set oXmlDoc = Server.CreateObject("MSXML.DOMDocument") Set oXmlTbl = oXmlDoc.createElement("table") End If ' Avoid starting record > total records If CLng(nStartRec) > CLng(nTotalRecs) Then nStartRec = nTotalRecs End If ' Set the last record to display If nDisplayRecs < 0 Then nStopRec = nTotalRecs Else nStopRec = nStartRec + nDisplayRecs - 1 End If ' Move to first record directly for performance reason nRecCount = nStartRec - 1 If Not rs.Eof Then rs.MoveFirst rs.Move nStartRec - 1 End If nRecActual = 0 Do While (Not rs.Eof) And (nRecCount < nStopRec) nRecCount = nRecCount + 1 If CLng(nRecCount) >= CLng(nStartRec) Then nRecActual = nRecActual + 1 x_id = rs("id") x_title = rs("title") x_first_name = rs("first_name") x_last_name = rs("last_name") x_dob = rs("dob") x_sex = rs("sex") x_initials = rs("initials") x_address = rs("address") x_post_code = rs("post_code") x_tel = rs("tel") x_mob = rs("mob") x_fax = rs("fax") x_form = rs("form") x_adm_no = rs("adm_no") x_daily_spend = rs("daily_spend") x_MAL = rs("MAL") x_MAL_date_from = rs("MAL_date_from") x_MAL_date_to = rs("MAL_date_to") x_MAL_time_from = rs("MAL_time_from") x_MAL_time_to = rs("MAL_time_to") x_user_group = rs("user_group") x_updated = rs("updated") x_csn = rs("csn") x_today = rs("today") If sExport = "xml" Then Set oXmlRec = oXmlDoc.createElement("record") Call oXmlTbl.appendChild(oXmlRec) ' Field id Set oXmlFld = oXmlDoc.createElement("id") sTmp = x_id if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field title Set oXmlFld = oXmlDoc.createElement("title") sTmp = x_title if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field first_name Set oXmlFld = oXmlDoc.createElement("first_name") sTmp = x_first_name if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field last_name Set oXmlFld = oXmlDoc.createElement("last_name") sTmp = x_last_name if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field dob Set oXmlFld = oXmlDoc.createElement("dob") sTmp = x_dob if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field sex Set oXmlFld = oXmlDoc.createElement("sex") sTmp = x_sex if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field initials Set oXmlFld = oXmlDoc.createElement("initials") sTmp = x_initials if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field address Set oXmlFld = oXmlDoc.createElement("address") sTmp = x_address if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field post_code Set oXmlFld = oXmlDoc.createElement("post_code") sTmp = x_post_code if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field tel Set oXmlFld = oXmlDoc.createElement("tel") sTmp = x_tel if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field mob Set oXmlFld = oXmlDoc.createElement("mob") sTmp = x_mob if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field fax Set oXmlFld = oXmlDoc.createElement("fax") sTmp = x_fax if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field form Set oXmlFld = oXmlDoc.createElement("form") sTmp = x_form if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field adm_no Set oXmlFld = oXmlDoc.createElement("adm_no") sTmp = x_adm_no if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field daily_spend Set oXmlFld = oXmlDoc.createElement("daily_spend") sTmp = x_daily_spend if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field MAL Set oXmlFld = oXmlDoc.createElement("MAL") sTmp = x_MAL if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field MAL_date_from Set oXmlFld = oXmlDoc.createElement("MAL_date_from") sTmp = x_MAL_date_from if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field MAL_date_to Set oXmlFld = oXmlDoc.createElement("MAL_date_to") sTmp = x_MAL_date_to if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field MAL_time_from Set oXmlFld = oXmlDoc.createElement("MAL_time_from") sTmp = x_MAL_time_from if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field MAL_time_to Set oXmlFld = oXmlDoc.createElement("MAL_time_to") sTmp = x_MAL_time_to if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field user_group Set oXmlFld = oXmlDoc.createElement("user_group") sTmp = x_user_group if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field updated Set oXmlFld = oXmlDoc.createElement("updated") sTmp = x_updated if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field csn Set oXmlFld = oXmlDoc.createElement("csn") sTmp = x_csn if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) ' Field today Set oXmlFld = oXmlDoc.createElement("today") sTmp = x_today if IsNull(sTmp) then sTmp = "" oXmlFld.Text = sTmp Call oXmlRec.AppendChild(oXmlFld) Set oXmlRec = Nothing End If End If rs.MoveNext Loop ' Close recordset and connection rs.Close Set rs = Nothing If sExport = "xml" Then Response.Write "" & vbcrlf Response.Write oXmlTbl.xml Set oXmlTbl = Nothing Set oXmlDoc = Nothing End If End Sub %>