home *** CD-ROM | disk | FTP | other *** search
/ bombers.k12.ar.us / bombers.k12.ar.us.tar / bombers.k12.ar.us / survey_unconfigured / ExportDataAction.asp < prev    next >
Text File  |  2006-07-05  |  64KB  |  1,459 lines
  1. <!--#Include File="Include/Top_inc.asp"-->
  2. <%
  3. '***********************************************************************
  4. '   Application: SelectSurveyASP Advanced v8.0.1
  5. '   Author: Aaron Baril for ClassApps.com
  6. '   Page Description: This page creates a CSV file.  Note that the header and content types
  7. '                      on this page are set so that instead of creating a file on the file
  8. '                      system, the .CSV file is immediately offered for download to the user.
  9. '
  10. '   COPYRIGHT NOTICE
  11. '
  12. '   See attached Software License Agreement
  13. '
  14. '   (c) Copyright 2002 - 2006 by ClassApps.com.  All rights reserved.
  15. '***********************************************************************
  16.  
  17. Server.ScriptTimeout = 10800
  18.  
  19. %>
  20. <!--#Include File="Include/Config_inc.asp"-->
  21. <!--#Include File="Include/Utility_inc.asp"-->
  22. <!--#Include File="Include/SurveySecurity_inc.asp"-->
  23. <!--#Include File="Include/SurveyUtility_inc.asp"-->
  24. <!--#Include File="Include/CurrentUser_inc.asp"-->
  25. <!--#Include File="Include/adovbs_inc.asp"-->
  26. <!--#Include File="Include/Constants_inc.asp"-->
  27. <!--#Include File="Include/SurveyReportFilter_inc.asp"-->
  28. <%
  29.     Dim rsExport
  30.     Dim rsQuestions
  31.     Dim strSQL
  32.     Dim strHeader
  33.     Dim strTempItemText
  34.     Dim strResponse
  35.     Dim strResponseAnswerText
  36.     Dim strItemAnswerText
  37.     Dim strOtherText
  38.     Dim strFirstName
  39.     Dim strLastName
  40. '--SB 5/8/2006
  41.     Dim strEmailFirstName
  42.     Dim strEmailLastName
  43. '--SB 5/31/2006
  44.     Dim strEmailListAddress
  45.     Dim strEmailAddress
  46.     Dim strCompany
  47.     Dim strCurrentPosition
  48.     Dim strLocation
  49.     Dim strUsername
  50.     Dim strStartDate
  51.     Dim strNullMatrixQuestions
  52.     Dim strEndDate
  53.     Dim strIPAddress
  54.     Dim strItemText
  55.     Dim strSubItemText
  56.     Dim strAnswerText
  57.     Dim strItemAlias
  58.     Dim strResponseID
  59.     Dim lngSurveyID
  60.     Dim strReportFilters
  61.     Dim strHeaderAdditionalColumns
  62.     Dim strSQLAdditionalColumns
  63.     Dim lngNextAnswerItemID
  64.     Dim lngQuestionCount
  65.     Dim lngItemTypeID
  66.     Dim lngItemID
  67.     Dim lngResponseID
  68.     Dim lngResponseCount
  69.     Dim i
  70.     Dim x
  71.     Dim y
  72.     Dim strColumnCodes
  73.     Dim arrResponses
  74.     Dim lngNextItemID
  75.     Dim arrIDs
  76.     Dim lngSubItemID
  77.     Dim lngAnswerID
  78.     Dim lngMaximumValue
  79.     Dim lngMinimumValue
  80.     Dim lngTotalColumnCount
  81.     Dim lngCurrentRatingValue
  82.     Dim strOtherYN
  83.     Dim strOtherItemText
  84.     Dim flgOtherYN
  85.     Dim lngCurrentItemID
  86.     Dim arrColumnIDs
  87.     Dim rsResponseHiddenFields
  88.     Dim lngHiddenFieldCount
  89.  
  90.     'Initialization
  91.     lngSurveyID = Request.Form("SurveyID")
  92.     lngQuestionCount = 0
  93.     lngHiddenFieldCount = GetHiddenFieldCount(lngSurveyID)
  94.     Set rsExport = Server.CreateObject("ADODB.Recordset")
  95.     Set rsQuestions = Server.CreateObject("ADODB.Recordset")
  96.     Set rsResponseHiddenFields = Server.CreateObject("ADODB.Recordset")
  97.  
  98.     'Set up the headers so that the CSV automatically opens in the user's browser
  99.     Response.ContentType="text/x-comma-separated-values"
  100.     Response.AddHeader "Content-Disposition", "inline; filename=SurveyReport.csv;"
  101.  
  102.     'Check to see if the hidden fields should be included in the results
  103.     If LCase(Request.Form("chkHiddenFields")) = "on" Then
  104.         'The order of the hidden fields depends on the export format
  105.         If Request.Form("optDataFormat") = SUR_EXPORT_DATA_FORMAT_INDIVIDUAL_RESPONSES Then
  106.             strSQL = "SELECT r.response_id, answer_text " & _
  107.                      "FROM sur_response r, sur_response_hidden_field rhf, sur_hidden_field hf, sur_survey_to_item_mapping m " & _
  108.                      "WHERE r.response_id = rhf.response_id " & _
  109.                      "AND rhf.hidden_field_id = hf.hidden_field_id " & _
  110.                      "AND hf.survey_id = m.survey_id " & _
  111.                      "AND m.survey_id = " & lngSurveyID & _
  112.                      " ORDER BY m.order_number, response_start_date, hf.order_number"
  113.         Else
  114.             strSQL = "SELECT r.response_id, answer_text " & _
  115.                      "FROM sur_response r, sur_response_hidden_field rhf, sur_hidden_field hf " & _
  116.                      "WHERE r.response_id = rhf.response_id " & _
  117.                      "AND rhf.hidden_field_id = hf.hidden_field_id " & _
  118.                      "AND r.survey_id = " & lngSurveyID & _
  119.                      " ORDER BY response_start_date, r.response_id, order_number"
  120.         End If
  121.         rsResponseHiddenFields.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  122.     End If
  123.  
  124.     'Check to see if each optional field was selected.  If so, for each field, add a header to the header string
  125.     'and add the field to the query
  126.     If LCase(Request.Form("chkFirstName")) = "on" Then
  127.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """First Name"","
  128.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_user.first_name, "
  129.     End If
  130.     If LCase(Request.Form("chkLastName")) = "on" Then
  131.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Last Name"","
  132.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_user.last_name, "
  133.     End If
  134. '--SB 5-8-2006 START
  135. '--added email first name and email last name
  136.     If LCase(Request.Form("chkEmailFirstName")) = "on" Then
  137.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Email First Name"","
  138.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_email_address.first_name AS email_first_name, "
  139.     End If
  140.     If LCase(Request.Form("chkEmailLastName")) = "on" Then
  141.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Email Last Name"","
  142.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_email_address.last_name AS email_last_name, "
  143.     End If
  144.  
  145. '--SB 5-8-2006 END
  146.  
  147.  
  148. '--SB 5-31-2006 START
  149.     If LCase(Request.Form("chkEmailListAddress")) = "on" Then
  150.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Email List Address"","
  151.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_email_address.email_address AS email_list_address, "
  152.     End If
  153.  
  154. '--SB 5-31-2006 END
  155.     If LCase(Request.Form("chkEmailAddress")) = "on" Then
  156.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Email Address"","
  157.         strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_user.email_address, "
  158.     End If
  159.     If LCase(Request.Form("chkCompany")) = "on" Then
  160.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Company"","
  161.         strSQLAdditionalColumns = strSQLAdditionalColumns & "company, "
  162.     End If
  163.     If LCase(Request.Form("chkCurrentPosition")) = "on" Then
  164.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Current Position"","
  165.         strSQLAdditionalColumns = strSQLAdditionalColumns & "current_position, "
  166.     End If
  167.     If LCase(Request.Form("chkLocation")) = "on" Then
  168.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Location"","
  169.         strSQLAdditionalColumns = strSQLAdditionalColumns & "location, "
  170.     End If
  171.     'Do not include the username for user response format, since this field is already included
  172.     If Request.Form("optDataFormat") <> SUR_EXPORT_DATA_FORMAT_USER_RESPONSES And Request.Form("optDataFormat") <> SUR_EXPORT_DATA_FORMAT_SPSS Then
  173.         If LCase(Request.Form("chkUsername")) = "on" Then
  174.             strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Username"","
  175.             strSQLAdditionalColumns = strSQLAdditionalColumns & "sur_user.username, "
  176.         End If
  177.     End If
  178.     If LCase(Request.Form("chkDateStarted")) = "on" Or LCase(Request.Form("chkTimeStarted")) = "on" Then
  179.         strSQLAdditionalColumns = strSQLAdditionalColumns & "response_start_date, "
  180.     End If
  181.     If LCase(Request.Form("chkDateStarted")) = "on" Then
  182.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Date Started"","
  183.     End If
  184.     If LCase(Request.Form("chkTimeStarted")) = "on" Then
  185.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Time Started"","
  186.     End If
  187.     If LCase(Request.Form("chkDateCompleted")) = "on" Or LCase(Request.Form("chkTimeCompleted")) = "on" Then
  188.         strSQLAdditionalColumns = strSQLAdditionalColumns & "response_end_date, "
  189.     End If
  190.     If LCase(Request.Form("chkDateCompleted")) = "on" Then
  191.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Date Completed"","
  192.     End If
  193.     If LCase(Request.Form("chkTimeCompleted")) = "on" Then
  194.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """Time Completed"","
  195.     End If
  196.     If LCase(Request.Form("chkIPAddress")) = "on" Then
  197.         strHeaderAdditionalColumns = strHeaderAdditionalColumns & """IP Address"","
  198.         strSQLAdditionalColumns = strSQLAdditionalColumns & "ip_address, "
  199.     End If
  200.  
  201.     'If the last character on the header string is a comma, strip it off.  Then add a carrage return/line feed.
  202.     If Right(strHeaderAdditionalColumns, 1) = "," Then
  203.         strHeaderAdditionalColumns = Left(strHeaderAdditionalColumns, Len(strHeaderAdditionalColumns) - 1)
  204.     End If
  205.     strHeaderAdditionalColumns = strHeaderAdditionalColumns
  206.  
  207.     'Export the data based on the data format export
  208.     If Request.Form("optDataFormat") = SUR_EXPORT_DATA_FORMAT_INDIVIDUAL_RESPONSES Then
  209.         'Initialize with the list of hidden fields
  210.         strHeader = GetHiddenFieldHeaders()
  211.  
  212.         'Add the list of standard headers
  213.         strHeader = strHeader & """Question"",""Row Text"",""Response ID"",""Response""," & strHeaderAdditionalColumns & vbCrLf
  214.  
  215.         'Display the header
  216.         Response.Write strHeader
  217.  
  218.         'Initialize the SQL string
  219.         strSQL = "SELECT sur_response.response_id As ResponseID, "
  220.  
  221.         'Add the additional columns to the SQL string.  Also add the columns of type text to the end, because they
  222.         'must be at the end to be supported by some database drivers.
  223.         strSQL = strSQL & strSQLAdditionalColumns & "sur_item.item_alias As ItemAlias, " & _
  224.                  "sur_item.item_text As ItemText, sur_response_answer.other_text, sur_item.other_text As ItemOtherText, " & _
  225.                  "other_yn, sur_response_answer.answer_text As ResponseAnswerText, "
  226.  
  227.         'Complete the SQL statement
  228.         'strSQL = strSQL & "sur_item_answer.answer_text As ItemAnswerText, subitem_text " & _
  229.         '            "FROM ((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  230.         '            "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) " & _
  231.         '            "INNER JOIN sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) " & _
  232.         '            "INNER JOIN ((sur_response INNER JOIN sur_response_answer ON " & _
  233.         '            "sur_response.response_id = sur_response_answer.response_id) " & _
  234.         '            "LEFT JOIN sur_user ON sur_response.username = sur_user.username) " & _
  235.         '            "ON sur_item.item_id = sur_response_answer.item_id) " & _
  236.         '            "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  237.         '            "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id " & _
  238.         '            "WHERE sur_survey.survey_id = " & lngSurveyID
  239.  
  240.         strSQL = strSQL & "sur_item_answer.answer_text As ItemAnswerText, subitem_text " & _
  241.                     "FROM (((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  242.                     "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) " & _
  243.                     "INNER JOIN sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) " & _
  244.                     "INNER JOIN ((sur_response INNER JOIN sur_response_answer ON " & _
  245.                     "sur_response.response_id = sur_response_answer.response_id) " & _
  246.                     "LEFT JOIN sur_user ON sur_response.username = sur_user.username) " & _
  247.                     "ON sur_item.item_id = sur_response_answer.item_id) " & _
  248.                     "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  249.                     "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id) " & _
  250.                     "left JOIN sur_email_address ON sur_response.email_address_id = sur_email_address.email_address_id " & _
  251.                     "WHERE sur_survey.survey_id = " & lngSurveyID
  252.  
  253.  
  254.  
  255.  
  256.         'If there are report filters, display only the data from questions that match the filter criteria
  257.         strReportFilters = GetFilteredResponseList(lngSurveyID)
  258.         If CStr(strReportFilters) <> SUR_REPORT_FILTER_NO_ACTIVE_FILTERS Then
  259.             strSQL = strSQL & " AND sur_response_answer.response_id IN(" & strReportFilters & ")"
  260.         End If
  261.  
  262.         'Add the ORDER BY clause to the SQL statement
  263.         strSQL = strSQL & " ORDER BY sur_survey_to_item_mapping.order_number, sur_response.response_start_date"
  264.  
  265. 'Response.Write strSQLAdditionalColumns & vbCrLf
  266. 'Response.Write strSQL
  267. 'Response.End
  268.  
  269.         'Open the recordset of answers
  270.         rsExport.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  271.  
  272.         'Loop through the recordset and display the data.
  273.         If Not rsExport.EOF Then
  274.             rsExport.MoveFirst
  275.  
  276.             Do While Not rsExport.EOF
  277.                 'Preserve the values for the fields in variables.  This is done because of a database driver problem when
  278.                 'running against SQL.
  279.                 strItemAlias = Replace(Trim(rsExport("ItemAlias")), vbCrLf, " ")
  280.                 strItemText = Replace(Trim(rsExport("ItemText")), vbCrLf, " ")
  281.  
  282.                 strSubItemText = Trim(rsExport("subitem_text"))
  283.                 If Len(strSubItemText) = 0 Then
  284.                     strSubItemText = Trim(rsExport("ItemAnswerText"))
  285.                     If (Len(strSubItemText) = 0 Or IsNull(strSubItemText) = True) And rsExport("other_yn") = SUR_BOOLEAN_POSITIVE Then
  286.                         strSubItemText = rsExport("ItemOtherText")
  287.                     End If
  288.                 End If
  289.  
  290.                 If Len(strItemAlias) > 0 Then
  291.                     strItemText = strItemAlias
  292.                 End If
  293.                 strResponseID = rsExport("ResponseID")
  294.                 strOtherText = Replace(rsExport("other_text"), vbCrLf, " ")
  295.                 strResponseAnswerText = Replace(rsExport("ResponseAnswerText"), vbCrLf, " ")
  296.                 If LCase(Request.Form("chkFirstName")) = "on" Then
  297.                     strFirstName = rsExport("first_name")
  298.                 End If
  299.                 If LCase(Request.Form("chkLastName")) = "on" Then
  300.                     strLastName = rsExport("last_name")
  301.                 End If
  302. '--SB 5-8-2006 START
  303.                 If LCase(Request.Form("chkEmailFirstName")) = "on" Then
  304.                     strEmailFirstName = rsExport("email_first_name")
  305.                 End If
  306.                 If LCase(Request.Form("chkEmailLastName")) = "on" Then
  307.                     strEmailLastName = rsExport("email_last_name")
  308.                 End If
  309. '--SB 5-8-2006 END
  310. '--SB 5-31-2006 START
  311.                 If LCase(Request.Form("chkEmailListAddress")) = "on" Then
  312.                     strEmailListAddress = rsExport("email_list_address")
  313.                 End If
  314. '--SB 5-31-2006 END
  315.                 If LCase(Request.Form("chkEmailAddress")) = "on" Then
  316.                     strEmailAddress = rsExport("email_address")
  317.                 End If
  318.                 If LCase(Request.Form("chkCompany")) = "on" Then
  319.                     strCompany = rsExport("company")
  320.                 End If
  321.                 If LCase(Request.Form("chkCurrentPosition")) = "on" Then
  322.                     strCurrentPosition = rsExport("current_position")
  323.                 End If
  324.                 If LCase(Request.Form("chkLocation")) = "on" Then
  325.                     strLocation = rsExport("location")
  326.                 End If
  327.                 If LCase(Request.Form("chkUsername")) = "on" Then
  328.                     strUsername = rsExport("username")
  329.                 End If
  330.                 If LCase(Request.Form("chkDateStarted")) = "on" Or LCase(Request.Form("chkTimeStarted")) = "on" Then
  331.                     strStartDate = CustomDateFormatDisplay(rsExport("response_start_date"))
  332.                 End If
  333.                 If LCase(Request.Form("chkDateCompleted")) = "on" Or LCase(Request.Form("chkTimeCompleted")) = "on" Then
  334.                     strEndDate = CustomDateFormatDisplay(rsExport("response_end_date"))
  335.                 End If
  336.                 If LCase(Request.Form("chkIPAddress")) = "on" Then
  337.                     strIPAddress = rsExport("ip_address")
  338.                 End If
  339.                 strItemAnswerText = rsExport("ItemAnswerText")
  340.  
  341.                 'Determine the answer.  The answer is either a typed-in value, a pre-canned answer, or an "Other" answer
  342.                 If strResponseAnswerText = SUR_ANSWER_TEXT_NULL And strOtherText = SUR_ANSWER_TEXT_NULL Then
  343.                     'If it appears that an answer was actually provided, check to make sure that there is a value.
  344.                     'If so, display the answer provided.  If not, display the phrase, "[No Answer Entered]"
  345.                     If Len(strItemAnswerText) = 0 Or IsNull(strItemAnswerText) Then
  346.                         strResponse = SUR_ANSWER_NULL_DISPLAY
  347.                     Else
  348.                         strResponse = strItemAnswerText
  349.                     End If
  350.                 ElseIf strResponseAnswerText <> SUR_ANSWER_TEXT_NULL And strResponseAnswerText <> SUR_ANSWER_TEXT_OTHER_SELECTED Then
  351.                     strResponse = strResponseAnswerText
  352.                 ElseIf strResponseAnswerText = SUR_ANSWER_TEXT_OTHER_SELECTED Then
  353.                     'If "Other" was selected, check to make sure that an "Other" answer was provided.  If so, display the answer
  354.                     'provided.  If not, display the phrase, "[No Answer Entered]"
  355.                     If Len(strOtherText) = 0 Then
  356.                         strResponse = SUR_ANSWER_NULL_DISPLAY
  357.                     Else
  358.                         strResponse = strOtherText
  359.                     End If
  360.                 End If
  361.  
  362.                 'Render the hidden fields, if selected
  363.                 If LCase(Request.Form("chkHiddenFields")) = "on" Then
  364.                     Response.Write GetHiddenFieldValues(rsResponseHiddenFields, lngHiddenFieldCount)
  365.                 End If
  366.  
  367.                 'Render the fileds of data.  The first few are always present, while the rest are optional.
  368.                 Response.Write("""" & QuoteDoubleQuotes(strItemText) & """,""" & QuoteDoubleQuotes(strSubItemText) & """,""" & QuoteDoubleQuotes(strResponseID) & """,""" & QuoteDoubleQuotes(strResponse) & """")
  369.  
  370.                 If LCase(Request.Form("chkFirstName")) = "on" Then
  371.                     Response.Write(",""" & QuoteDoubleQuotes(strFirstName) & """")
  372.                 End If
  373.                 If LCase(Request.Form("chkLastName")) = "on" Then
  374.                     Response.Write(",""" & QuoteDoubleQuotes(strLastName) & """")
  375.                 End If
  376. '--SB 5/8/2006 START
  377.                 If LCase(Request.Form("chkEmailFirstName")) = "on" Then
  378.                     Response.Write(",""" & QuoteDoubleQuotes(strEmailFirstName) & """")
  379.                 End If
  380.                 If LCase(Request.Form("chkEmailLastName")) = "on" Then
  381.                     Response.Write(",""" & QuoteDoubleQuotes(strEmailLastName) & """")
  382.                 End If
  383. '--SB 5/8/2006 END
  384. '--SB 5-31-2006 START
  385.                 If LCase(Request.Form("chkEmailListAddress")) = "on" Then
  386.                     Response.Write(",""" & QuoteDoubleQuotes(strEmailListAddress) & """")
  387.                 End If
  388. '--SB 5-31-2006 END
  389.                 If LCase(Request.Form("chkEmailAddress")) = "on" Then
  390.                     Response.Write(",""" & QuoteDoubleQuotes(strEmailAddress) & """")
  391.                 End If
  392.                 If LCase(Request.Form("chkCompany")) = "on" Then
  393.                     Response.Write(",""" & QuoteDoubleQuotes(strCompany) & """")
  394.                 End If
  395.                 If LCase(Request.Form("chkCurrentPosition")) = "on" Then
  396.                     Response.Write(",""" & QuoteDoubleQuotes(strCurrentPosition) & """")
  397.                 End If
  398.                 If LCase(Request.Form("chkLocation")) = "on" Then
  399.                     Response.Write(",""" & QuoteDoubleQuotes(strLocation) & """")
  400.                 End If
  401.                 'If the username does not exist, display "Anonymous"
  402.                 If LCase(Request.Form("chkUsername")) = "on" Then
  403.                     If IsNull(strUsername) Or Len(strUsername) = 0 Then
  404.                         Response.Write(",""" & SUR_ANONYMOUS_USER & """")
  405.                     Else
  406.                         Response.Write(",""" & QuoteDoubleQuotes(strUsername) & """")
  407.                     End If
  408.                 End If
  409.                 If LCase(Request.Form("chkDateStarted")) = "on" Then
  410.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  411.                         Response.Write ","""""
  412.                     Else
  413.                         Response.Write(",""" & DateValue(strStartDate) & """")
  414.                     End If
  415.                 End If
  416.                 If LCase(Request.Form("chkTimeStarted")) = "on" Then
  417.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  418.                         Response.Write ","""""
  419.                     Else
  420.                         Response.Write(",""" & TimeValue(strStartDate) & """")
  421.                     End If
  422.                 End If
  423.                 If LCase(Request.Form("chkDateCompleted")) = "on" Then
  424.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  425.                         Response.Write ","""""
  426.                     Else
  427.                         Response.Write(",""" & DateValue(strEndDate) & """")
  428.                     End If
  429.                 End If
  430.                 If LCase(Request.Form("chkTimeCompleted")) = "on" Then
  431.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  432.                         Response.Write ","""""
  433.                     Else
  434.                         Response.Write(",""" & TimeValue(strEndDate) & """")
  435.                     End If
  436.                 End If
  437.                 If LCase(Request.Form("chkIPAddress")) = "on" Then
  438.                     Response.Write(",""" & strIPAddress & """")
  439.                 End If
  440.  
  441.                 'Add a carriage return/line feed
  442.                 Response.Write(vbCrLf)
  443.  
  444.                 rsExport.MoveNext
  445.             Loop
  446.         End If
  447.     ElseIf Request.Form("optDataFormat") = SUR_EXPORT_DATA_FORMAT_USER_RESPONSES Then
  448.  
  449.         'Initialize with the list of hidden fields
  450.         strHeader = GetHiddenFieldHeaders()
  451.  
  452.         'Add the username and the additional columns to the header
  453.         strHeader = strHeader & """Username"","
  454.  
  455.         'Add the additional columns to the header, if there are any
  456.         If Len(strHeaderAdditionalColumns) > 0 Then
  457.             strHeader = strHeader & strHeaderAdditionalColumns & ","
  458.         End If
  459.  
  460.         'Open the recordset of questions
  461.         strSQL = "SELECT i.item_id, item_text, item_alias " & _
  462.                  "FROM sur_item i, sur_survey_to_item_mapping m, sur_item_type it " & _
  463.                  "WHERE i.item_id = m.item_id " & _
  464.                  "AND i.item_type_id = it.item_type_id " & _
  465.                  "AND m.survey_id = " & lngSurveyID & _
  466.                  " AND it.question_yn = " & SQLEncode(SUR_BOOLEAN_POSITIVE) & _
  467.                  " ORDER BY m.order_number"
  468.         rsQuestions.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  469.  
  470.         'Loop through the questions, and add each to the header
  471.         If Not rsQuestions.EOF Then
  472.             rsQuestions.MoveFirst
  473.             Do While Not rsQuestions.EOF
  474.                 'Determine whether to use the actual question or the alias as the column heading
  475.                 strItemAlias = Trim(rsQuestions("item_alias"))
  476.                 If Len(strItemAlias) = 0 Or IsNull(strItemAlias) = True Then
  477.                     strItemText = Trim(rsQuestions("item_text"))
  478.                 Else
  479.                     strItemText = strItemAlias
  480.                 End If
  481.  
  482.                 'Remove any carriage returns from the text
  483.                 strItemText = Replace(strItemText, vbCrLf, " ")
  484.  
  485.                 'Create a string of item ID's
  486.                 strColumnCodes = strColumnCodes & rsQuestions("item_id") & "||"
  487.  
  488.                 'Add the question text or alias to the header list
  489.                 strHeader = strHeader & """" & strItemText & ""","
  490.  
  491.                 'Increment the question count
  492.                 lngQuestionCount = lngQuestionCount + 1
  493.                 rsQuestions.MoveNext
  494.             Loop
  495.  
  496.             'After looping, strip the final separator off of the string of item ID's
  497.             strColumnCodes = Left(strColumnCodes, Len(strColumnCodes) - 2)
  498.         End If
  499.  
  500.         'If the last character on the header string is a comma, strip it off.  Then add a carrage return/line feed.
  501.         If Right(strHeader, 1) = "," Then
  502.             strHeader = Left(strHeader, Len(strHeader) - 1)
  503.         End If
  504.         strHeader = strHeader & vbCrLf
  505.  
  506.         'Display the header
  507.         Response.Write strHeader
  508.  
  509.         'Initialize the SQL string
  510.         strSQL = "SELECT sur_response.response_id, sur_item.item_id, item_type_id, sur_user.username, "
  511.  
  512.         'Add the additional columns to the SQL string
  513.         strSQL = strSQL & strSQLAdditionalColumns
  514.  
  515.         'Complete the SQL statement
  516.         'strSQL = strSQL & "sur_response_answer.other_text, sur_response_answer.answer_text AS ResponseAnswerText, " & _
  517.         '            "sur_item_answer.answer_text AS ItemAnswerText, sur_subitem.subitem_text " & _
  518.         '            "FROM ((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  519.         '            "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) INNER JOIN " & _
  520.         '            "sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) INNER JOIN " & _
  521.         '            "((sur_response INNER JOIN sur_response_answer ON sur_response.response_id = " & _
  522.         '            "sur_response_answer.response_id) LEFT JOIN sur_user ON sur_response.username = " & _
  523.         '            "sur_user.username) ON sur_item.item_id = sur_response_answer.item_id) " & _
  524.         '            "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  525.         '            "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id " & _
  526.         '            "WHERE sur_survey.survey_id = " & lngSurveyID
  527.  
  528.  
  529.  
  530.  
  531.         strSQL = strSQL & "sur_response_answer.other_text, sur_response_answer.answer_text AS ResponseAnswerText, " & _
  532.                     "sur_item_answer.answer_text AS ItemAnswerText, sur_subitem.subitem_text " & _
  533.                     "FROM (((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  534.                     "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) INNER JOIN " & _
  535.                     "sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) INNER JOIN " & _
  536.                     "((sur_response INNER JOIN sur_response_answer ON sur_response.response_id = " & _
  537.                     "sur_response_answer.response_id) LEFT JOIN sur_user ON sur_response.username = " & _
  538.                     "sur_user.username) ON sur_item.item_id = sur_response_answer.item_id) " & _
  539.                     "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  540.                     "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id) " & _
  541.                     "left JOIN sur_email_address ON sur_response.email_address_id = sur_email_address.email_address_id " & _
  542.                     "WHERE sur_survey.survey_id = " & lngSurveyID
  543.  
  544.         'If there are report filters, display only the data from questions that match the filter criteria
  545.         strReportFilters = GetFilteredResponseList(lngSurveyID)
  546.         If CStr(strReportFilters) <> SUR_REPORT_FILTER_NO_ACTIVE_FILTERS Then
  547.             strSQL = strSQL & " AND sur_response_answer.response_id IN(" & strReportFilters & ")"
  548.         End If
  549.  
  550.         'Add the ORDER BY clause to the SQL statement
  551.         strSQL = strSQL & " ORDER BY sur_response.response_start_date, sur_response.response_id, " & _
  552.                           "sur_survey_to_item_mapping.order_number, sur_subitem.order_number"
  553. '--SB 5-8-2006
  554. 'Response.Write strSQL
  555. 'Response.End
  556.         'Open the recordset of answers
  557.         rsExport.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenStatic, adLockReadOnly, adCmdText
  558.  
  559.         'Loop through the recordset and display the data.
  560.         If Not rsExport.EOF Then
  561.             rsExport.MoveFirst
  562.  
  563.             'Initialize the response ID
  564.             lngResponseID = rsExport("response_id")
  565.  
  566.             'Loop through all of the responses for this survey, and display the response to each question
  567.             Do While Not rsExport.EOF
  568.                 'Render the hidden fields, if selected
  569.                 If LCase(Request.Form("chkHiddenFields")) = "on" Then
  570.                     Response.Write GetHiddenFieldValues(rsResponseHiddenFields, lngHiddenFieldCount)
  571.                 End If
  572.  
  573.                 'Render all of the fields of data about the user next.
  574.                 'Start with rendering the username, which is always present, first.
  575.                 'If the username does not exist, display "Anonymous".
  576.                 strUsername = rsExport("username")
  577.                 If IsNull(strUsername) Or Len(strUsername) = 0 Then
  578.                     Response.Write("""" & SUR_ANONYMOUS_USER & """")
  579.                 Else
  580.                     Response.Write("""" & QuoteDoubleQuotes(strUsername) & """")
  581.                 End If
  582.  
  583.                 'Render the optional fields of data
  584.                 If LCase(Request.Form("chkFirstName")) = "on" Then
  585.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("first_name")) & """")
  586.                 End If
  587.                 If LCase(Request.Form("chkLastName")) = "on" Then
  588.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("last_name")) & """")
  589.                 End If
  590. '--SB 5-8-2006 START
  591.                 If LCase(Request.Form("chkEmailFirstName")) = "on" Then
  592.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_first_name")) & """")
  593.                 End If
  594.                 If LCase(Request.Form("chkEmailLastName")) = "on" Then
  595.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_last_name")) & """")
  596.                 End If
  597. '--SB 5-8-2006 END
  598. '--SB 5/31/2006 START
  599.                 If LCase(Request.Form("chkEmailListAddress")) = "on" Then
  600.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_list_address")) & """")
  601.                 End If
  602. '--SB 5/31/2006 END
  603.                 If LCase(Request.Form("chkEmailAddress")) = "on" Then
  604.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_address")) & """")
  605.                 End If
  606.                 If LCase(Request.Form("chkCompany")) = "on" Then
  607.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("company")) & """")
  608.                 End If
  609.                 If LCase(Request.Form("chkCurrentPosition")) = "on" Then
  610.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("current_position")) & """")
  611.                 End If
  612.                 If LCase(Request.Form("chkLocation")) = "on" Then
  613.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("location")) & """")
  614.                 End If
  615.                 If LCase(Request.Form("chkDateStarted")) = "on" Then
  616.                     strStartDate = CustomDateFormatDisplay(rsExport("response_start_date"))
  617.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  618.                         Response.Write ","""""
  619.                     Else
  620.                         Response.Write(",""" & DateValue(strStartDate) & """")
  621.                     End If
  622.                 End If
  623.                 If LCase(Request.Form("chkTimeStarted")) = "on" Then
  624.                     strStartDate = rsExport("response_start_date")
  625.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  626.                         Response.Write ","""""
  627.                     Else
  628.                         Response.Write(",""" & TimeValue(strStartDate) & """")
  629.                     End If
  630.                 End If
  631.                 If LCase(Request.Form("chkDateCompleted")) = "on" Then
  632.                     strEndDate = CustomDateFormatDisplay(rsExport("response_end_date"))
  633.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  634.                         Response.Write ","""""
  635.                     Else
  636.                         Response.Write(",""" & DateValue(strEndDate) & """")
  637.                     End If
  638.                 End If
  639.                 If LCase(Request.Form("chkTimeCompleted")) = "on" Then
  640.                     strEndDate = rsExport("response_end_date")
  641.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  642.                         Response.Write ","""""
  643.                     Else
  644.                         Response.Write(",""" & TimeValue(strEndDate) & """")
  645.                     End If
  646.                 End If
  647.                 If LCase(Request.Form("chkIPAddress")) = "on" Then
  648.                     Response.Write(",""" & rsExport("ip_address") & """")
  649.                 End If
  650.  
  651.                 'Next, render all of the answers for this user's response.
  652.                 lngResponseCount = 0
  653.  
  654.                 'Get an array of the item ID's
  655.                 arrColumnIDs = Split(strColumnCodes, "||")
  656.                 lngCurrentItemID = arrColumnIDs(lngResponseCount)
  657.  
  658.                 'Loop through all of the questions for this response, and concatenate each into a string
  659.                 Do While lngResponseID = rsExport("response_id") And Not rsExport.EOF
  660.                     'Initialize the item ID and item type ID
  661.                     lngItemTypeID = rsExport("item_type_id")
  662.                     lngItemID = rsExport("item_id")
  663.  
  664.                     'Set variables for all of the possible answer fields
  665.                     strOtherText = Trim(Replace(rsExport("other_text") & " ", vbCrLf, " "))
  666.                     strResponseAnswerText = Trim(Replace(rsExport("ResponseAnswerText") & " ", vbCrLf, " "))
  667.                     strItemAnswerText = Trim(Replace(rsExport("ItemAnswerText") & " ", vbCrLf, " "))
  668.                     strSubItemText = Trim(Replace(rsExport("subitem_text") & " ", vbCrLf, " "))
  669.  
  670.                     'Determine the answer.  The answer is either a typed-in value, a pre-canned answer, or an "Other" answer
  671.                     If strResponseAnswerText = SUR_ANSWER_TEXT_NULL And strOtherText = SUR_ANSWER_TEXT_NULL Then
  672.                         'If it appears that an answer was actually provided, check to make sure that there is a value.
  673.                         'If so, display the answer provided.  If not, display the phrase, "[No Answer Entered]"
  674.                         If Len(strItemAnswerText) = 0 Or IsNull(strItemAnswerText) Then
  675.                             strResponse = strResponse & SUR_ANSWER_NULL_DISPLAY & "; "
  676.                         Else
  677.                             'If there is a subitem, include the subitem text
  678.                             If Len(strSubItemText) > 0 Then
  679.                                 strResponse = strResponse & strSubItemText & " = " & strItemAnswerText & "; "
  680.                             Else
  681.                                 strResponse = strResponse & strItemAnswerText & "; "
  682.                             End If
  683.                         End If
  684.                     ElseIf strResponseAnswerText <> SUR_ANSWER_TEXT_NULL And strResponseAnswerText <> SUR_ANSWER_TEXT_OTHER_SELECTED Then
  685.                         'If there is a subitem, include the subitem text
  686.                         If Len(strSubItemText) > 0 Then
  687.                             If CLng(lngItemTypeID) = CLng(SUR_ITEM_MATRIX_TEXT_BOXES) Then
  688.                                 strResponse = strResponse & strSubItemText & " (" & strItemAnswerText & ") = " & strResponseAnswerText & "; "
  689.                             Else
  690.                                 strResponse = strResponse & strSubItemText & " = " & strResponseAnswerText & "; "
  691.                             End If
  692.                         Else
  693.                             strResponse = strResponse & strResponseAnswerText & "; "
  694.                         End If
  695.                     ElseIf strResponseAnswerText = SUR_ANSWER_TEXT_OTHER_SELECTED Then
  696.                         'If "Other" was selected, check to make sure that an "Other" answer was provided.  If so, display the answer
  697.                         'provided.  If not, display the phrase, "[No Answer Entered]"
  698.                         If Len(strOtherText) = 0 Then
  699.                             strResponse = strResponse & SUR_ANSWER_NULL_DISPLAY & "; "
  700.                         Else
  701.                             strResponse = strResponse & strOtherText & "; "
  702.                         End If
  703.                     End If
  704.  
  705.                     'If EOF is reached, display the last result
  706.                     If rsExport.EOF Then
  707.                         'Display the individual response
  708.                         DisplayIndividualResponse strResponse, lngItemTypeID
  709.                         lngResponseCount = lngResponseCount + 1
  710.                         strResponse = ""
  711.                     Else
  712.                         rsExport.MoveNext
  713.  
  714.                         'If EOF is reached, display the last item
  715.                         If rsExport.EOF Then
  716.                             'Display the individual response
  717.                             DisplayIndividualResponse strResponse, lngItemTypeID
  718.                             lngResponseCount = lngResponseCount + 1
  719.                             strResponse = ""
  720.                             Exit Do
  721.                         Else
  722.                             'After each item is complete, display the result
  723.                             If lngResponseID <> rsExport("response_id") Or lngItemID <> rsExport("item_id") Then
  724.                                 'Display the individual response
  725.                                 DisplayIndividualResponse strResponse, lngItemTypeID
  726.                                 lngResponseCount = lngResponseCount + 1
  727.  
  728.                                 'Add "No Answer Entered" placeholders for questions that were not answered
  729.                                 If lngResponseCount < UBound(arrColumnIDs) Then
  730.                                     Do While CStr(lngResponseID) = CStr(rsExport("response_id")) And CStr(arrColumnIDs(lngResponseCount)) <> CStr(rsExport("item_id"))
  731.                                         Response.Write ",""" & SUR_ANSWER_NULL_DISPLAY & """"
  732.                                         lngResponseCount = lngResponseCount + 1
  733.                                         If lngResponseCount > UBound(arrColumnIDs) Then
  734.                                             Exit Do
  735.                                         End If
  736.                                     Loop
  737.                                 End If
  738.                                 strResponse = ""
  739.                             End If
  740.                         End If
  741.                     End If
  742.                 Loop
  743.  
  744.                 For i = lngResponseCount + 1 To lngQuestionCount
  745.                     Response.Write(",""" & QuoteDoubleQuotes(SUR_ANSWER_NULL_DISPLAY) & """")
  746.                 Next
  747.  
  748.                 'Add a carriage return/line feed
  749.                 Response.Write(vbCrLf)
  750.  
  751.                 'Reset the response ID to the current record
  752.                 If Not rsExport.EOF Then
  753.                     lngResponseID = rsExport("response_id")
  754.                 End If
  755.             Loop
  756.         End If
  757.     ElseIf Request.Form("optDataFormat") = SUR_EXPORT_DATA_FORMAT_SPSS Then
  758.         'Initialize with the list of hidden fields
  759.         strHeader = GetHiddenFieldHeaders()
  760.  
  761.         'Initialize the header with the username and the additional columns
  762.         strHeader = strHeader & """Username"","
  763.  
  764.         'Add the additional columns to the header, if there are any
  765.         If Len(strHeaderAdditionalColumns) > 0 Then
  766.             strHeader = strHeader & strHeaderAdditionalColumns & ","
  767.         End If
  768.  
  769.         'Open the recordset of questions
  770.         strSQL = "SELECT it.item_type_id, i.item_id, si.subitem_id, ia.answer_id, it.item_type_id, " & _
  771.                  "minimum_value, maximum_value, other_yn, si.subitem_text, ia.answer_text, item_alias, i.item_text " & _
  772.                  "FROM sur_item_type it INNER JOIN (sur_survey_to_item_mapping m INNER JOIN (" & _
  773.                  "sur_item_answer ia RIGHT JOIN (sur_subitem si RIGHT JOIN sur_item i ON " & _
  774.                  "si.item_id = i.item_id) ON ia.item_id = i.item_id) ON m.item_id = i.item_id) ON " & _
  775.                  "it.item_type_id = i.item_type_id " & _
  776.                  "WHERE m.survey_id = " & lngSurveyID & _
  777.                  " AND it.question_yn = " & SQLEncode(SUR_BOOLEAN_POSITIVE) & _
  778.                  "ORDER BY m.order_number, si.order_number, ia.order_number"
  779.         rsQuestions.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  780.  
  781.         'Loop through the questions, and add each to the header
  782.         If Not rsQuestions.EOF Then
  783.             rsQuestions.MoveFirst
  784.             Do While Not rsQuestions.EOF
  785.                 lngItemID = rsQuestions("item_id")
  786.  
  787.                 'Create a collection of ID's to identify each column that contains question answers.  The format for each
  788.                 'item is ITEM_ID||SUBITEM_ID||ANSWER_ID||ITEM_TYPE_ID||OTHER_YN, with each item, in that format,separated by a
  789.                 'semi-colon, as follows:
  790.                 'ITEM_ID||SUBITEM_ID||ANSWER_ID||ITEM_TYPE_ID||OTHER_YN;ITEM_ID||SUBITEM_ID||ANSWER_ID||ITEM_TYPE_ID||OTHER_YN;
  791.                 'Note that only one entry is present for both Yes/No and True/False questions.  If an ID is blank or Null,
  792.                 'add a constant called SUR_ANSWER_ID_NULL (which is -1)
  793.                 If Len(rsQuestions("item_id")) > 0 Then
  794.                     strColumnCodes = strColumnCodes & lngItemID & "||"
  795.                 Else
  796.                     strColumnCodes = strColumnCodes & SUR_ANSWER_ID_NULL & "||"
  797.                 End If
  798.                 If Len(rsQuestions("subitem_id")) > 0 Then
  799.                     strColumnCodes = strColumnCodes & rsQuestions("subitem_id") & "||"
  800.                 Else
  801.                     strColumnCodes = strColumnCodes & SUR_ANSWER_ID_NULL & "||"
  802.                 End If
  803.                 If Len(rsQuestions("answer_id")) > 0 Then
  804.                     strColumnCodes = strColumnCodes & rsQuestions("answer_id") & "||"
  805.                 Else
  806.                     strColumnCodes = strColumnCodes & SUR_ANSWER_ID_NULL & "||"
  807.                 End If
  808.                 If Len(rsQuestions("item_type_id")) > 0 Then
  809.                     strColumnCodes = strColumnCodes & rsQuestions("item_type_id") & "||"
  810.                 Else
  811.                     strColumnCodes = strColumnCodes & SUR_ANSWER_ID_NULL & "||"
  812.                 End If
  813.                 strColumnCodes = strColumnCodes & rsQuestions("other_yn") & ";"
  814.  
  815.                 'Determine whether to use the actual question or the alias as the column heading
  816.                 strItemAlias = Trim(rsQuestions("item_alias"))
  817.                 If Len(strItemAlias) = 0 Or IsNull(strItemAlias) = True Then
  818.                     strItemText = Trim(rsQuestions("item_text"))
  819.                 Else
  820.                     strItemText = strItemAlias
  821.                 End If
  822.  
  823.                 'Preserver the item text to be used, optionally, with the "other" choice, below
  824.                 strOtherItemText = strItemText
  825.  
  826.                 'Add one column to the header for every possible response to the question
  827.                 If CStr(rsQuestions("item_type_id")) = CStr(SUR_ITEM_YES_NO) Then
  828.                     strItemText = strItemText & " (Yes)" & """,""" & strItemText & " (No)"
  829.                 ElseIf CStr(rsQuestions("item_type_id")) = CStr(SUR_ITEM_TRUE_FALSE) Then
  830.                     strItemText = strItemText & " (True)" & """,""" & strItemText & " (False)"
  831.                 ElseIf CStr(rsQuestions("item_type_id")) = CStr(SUR_ITEM_MATRIX_RATING_SCALE) Then
  832.                     'Get the row text, and the minimum and maximum value for the rating scale
  833.                     strSubItemText = rsQuestions("subitem_text")
  834.                     lngMinimumValue = CLng(rsQuestions("minimum_value"))
  835.                     lngMaximumValue = CLng(rsQuestions("maximum_value"))
  836.  
  837.                     'Loop through all of the possible rating values, and create a header for each
  838.                     For x = lngMinimumValue To lngMaximumValue
  839.                         strTempItemText = strTempItemText & strItemText & " (" & strSubItemText & ", " & x & ")"","""
  840.                     Next
  841.                     strItemText = Left(strTempItemText, Len(strTempItemText) - 3)
  842.                     strTempItemText = ""
  843.                 Else
  844.                     strSubItemText = rsQuestions("subitem_text")
  845.                     strAnswerText = rsQuestions("answer_text")
  846.                     'If there are multiple rows or columns, add those to the heading
  847.                     If Len(strSubItemText) > 0 And Len(strAnswerText) > 0 Then
  848.                         strItemText = strItemText & " (" & strSubItemText & ", " & strAnswerText & ")"
  849.                     ElseIf Len(strSubItemText) > 0 Then
  850.                         strItemText = strItemText & " (" & strSubItemText & ")"
  851.                     ElseIf Len(strAnswerText) > 0 Then
  852.                         strItemText = strItemText & " (" & strAnswerText & ")"
  853.                     End If
  854.                 End If
  855.  
  856.                 'Remove any carriage returns from the text
  857.                 strItemText = Replace(strItemText, vbCrLf, " ")
  858.  
  859.                 'Add the question text or alias to the header list
  860.                 strHeader = strHeader & """" & strItemText & ""","
  861.  
  862.                 'Increment the question count
  863.                 lngQuestionCount = lngQuestionCount + 1
  864.  
  865.                 'If the item type is checkboxes or options buttons (the two types that support the "other" option), find out
  866.                 'whether or not an "other" option is being used so an extra column can be added.
  867.                 If CStr(rsQuestions("item_type_id")) = CStr(SUR_ITEM_MULTISELECT_CHECKBOXES) Or CStr(rsQuestions("item_type_id")) = CStr(SUR_ITEM_SINGLE_SELECT_OPTIONS) Then
  868.                     strOtherYN = rsQuestions("other_yn")
  869.                 End If
  870.  
  871.                 'Advanced the recordset
  872.                 rsQuestions.MoveNext
  873.  
  874.                 'Check to see whether or not an "other" item should be added to the list of headers
  875.                 If rsQuestions.EOF = True Then
  876.                     If strOtherYN = SUR_BOOLEAN_POSITIVE Then
  877.                         strHeader = strHeader & """" & strOtherItemText & " (Other)"","
  878.                     End If
  879.                 Else
  880.                     'Check to see if the recordset is on a new question
  881.                     If CStr(lngItemID) <> CStr(rsQuestions("item_id")) And strOtherYN = SUR_BOOLEAN_POSITIVE Then
  882.                         strHeader = strHeader & """" & strOtherItemText & " (Other)"","
  883.                     End If
  884.                 End If
  885.  
  886.                 'Reset the variable for the "other" indicator
  887.                 strOtherYN = ""
  888.             Loop
  889.         End If
  890.  
  891.         'If the last character on the header string is a comma, strip it off.  Then add a carrage return/line feed.
  892.         If Right(strHeader, 1) = "," Then
  893.             strHeader = Left(strHeader, Len(strHeader) - 1)
  894.         End If
  895.         strHeader = strHeader & vbCrLf
  896.  
  897.         'Strip off the trialing semi-colon on the list of column codes
  898.         strColumnCodes = Left(strColumnCodes, Len(strColumnCodes) - 1)
  899.  
  900.         'Display the header
  901.         Response.Write strHeader
  902.  
  903.         'Initialize the SQL string
  904.         strSQL = "SELECT sur_response.response_id, sur_item.item_id, item_type_id, sur_user.username, "
  905.  
  906.         'Add the additional columns to the SQL string
  907.         strSQL = strSQL & strSQLAdditionalColumns
  908.  
  909.         'Complete the SQL statement
  910.         'strSQL = strSQL & "sur_subitem.subitem_id, sur_response_answer.answer_id, minimum_value, maximum_value, " & _
  911.         '            "other_yn, sur_response_answer.other_text, sur_response_answer.answer_text AS ResponseAnswerText " & _
  912.         '            "FROM ((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  913.         '            "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) INNER JOIN " & _
  914.         '            "sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) INNER JOIN " & _
  915.         '            "((sur_response INNER JOIN sur_response_answer ON sur_response.response_id = " & _
  916.         '            "sur_response_answer.response_id) LEFT JOIN sur_user ON sur_response.username = " & _
  917.         '            "sur_user.username) ON sur_item.item_id = sur_response_answer.item_id) " & _
  918.         '            "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  919.         '            "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id " & _
  920.         '            "WHERE sur_survey.survey_id = " & lngSurveyID
  921.  
  922.         strSQL = strSQL & "sur_subitem.subitem_id, sur_response_answer.answer_id, minimum_value, maximum_value, " & _
  923.                     "other_yn, sur_response_answer.other_text, sur_response_answer.answer_text AS ResponseAnswerText " & _
  924.                     "FROM (((((sur_survey INNER JOIN sur_survey_to_item_mapping ON " & _
  925.                     "sur_survey.survey_id = sur_survey_to_item_mapping.survey_id) INNER JOIN " & _
  926.                     "sur_item ON sur_survey_to_item_mapping.item_id = sur_item.item_id) INNER JOIN " & _
  927.                     "((sur_response INNER JOIN sur_response_answer ON sur_response.response_id = " & _
  928.                     "sur_response_answer.response_id) LEFT JOIN sur_user ON sur_response.username = " & _
  929.                     "sur_user.username) ON sur_item.item_id = sur_response_answer.item_id) " & _
  930.                     "LEFT JOIN sur_item_answer ON sur_response_answer.answer_id = sur_item_answer.answer_id) " & _
  931.                     "LEFT JOIN sur_subitem ON sur_response_answer.subitem_id = sur_subitem.subitem_id) " & _
  932.                     "left JOIN sur_email_address ON sur_response.email_address_id = sur_email_address.email_address_id " & _
  933.                     "WHERE sur_survey.survey_id = " & lngSurveyID
  934.  
  935.  
  936.  
  937.  
  938.         'If there are report filters, display only the data from questions that match the filter criteria
  939.         strReportFilters = GetFilteredResponseList(lngSurveyID)
  940.         If CStr(strReportFilters) <> SUR_REPORT_FILTER_NO_ACTIVE_FILTERS Then
  941.             strSQL = strSQL & " AND sur_response_answer.response_id IN(" & strReportFilters & ")"
  942.         End If
  943.  
  944.         'Add the ORDER BY clause to the SQL statement
  945.         strSQL = strSQL & " ORDER BY sur_response.response_start_date, sur_response.response_id, " & _
  946.                           "sur_survey_to_item_mapping.order_number, sur_subitem.order_number, " & _
  947.                           "sur_item_answer.order_number"
  948.  
  949.         'Open the recordset of answers
  950.         rsExport.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenStatic, adLockReadOnly, adCmdText
  951.  
  952.         'Loop through the recordset and display the data.
  953.         If Not rsExport.EOF Then
  954.             rsExport.MoveFirst
  955.  
  956.             'Initialize the response ID and current item ID
  957.             lngResponseID = rsExport("response_id")
  958.             lngCurrentItemID = rsExport("item_id")
  959.  
  960.             'Loop through all of the responses for this survey, and display the response to each question
  961.             Do While Not rsExport.EOF
  962.                 'Render the hidden fields, if selected
  963.                 If LCase(Request.Form("chkHiddenFields")) = "on" Then
  964.                     Response.Write GetHiddenFieldValues(rsResponseHiddenFields, lngHiddenFieldCount)
  965.                 End If
  966.  
  967.                 'Render all of the fields of data about the user next.
  968.                 'Start with rendering the username, which is always present, first.
  969.                 'If the username does not exist, display "Anonymous".
  970.                 strUsername = rsExport("username")
  971.                 If IsNull(strUsername) Or Len(strUsername) = 0 Then
  972.                     Response.Write("""" & SUR_ANONYMOUS_USER & """")
  973.                 Else
  974.                     Response.Write("""" & QuoteDoubleQuotes(strUsername) & """")
  975.                 End If
  976.  
  977.                 'Render the optional fields of data
  978.                 If LCase(Request.Form("chkFirstName")) = "on" Then
  979.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("first_name")) & """")
  980.                 End If
  981.                 If LCase(Request.Form("chkLastName")) = "on" Then
  982.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("last_name")) & """")
  983.                 End If
  984. '--SB 5-8-2006 START
  985.                 If LCase(Request.Form("chkEmailFirstName")) = "on" Then
  986.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_first_name")) & """")
  987.                 End If
  988.                 If LCase(Request.Form("chkEmailLastName")) = "on" Then
  989.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_last_name")) & """")
  990.                 End If
  991. '--SB 5-8-2006 END
  992. '--SB 5/31/2006 START
  993.                 If LCase(Request.Form("chkEmailListAddress")) = "on" Then
  994.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_list_address")) & """")
  995.                 End If
  996. '--SB 5/31/2006 END
  997.  
  998.                 If LCase(Request.Form("chkEmailAddress")) = "on" Then
  999.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("email_address")) & """")
  1000.                 End If
  1001.                 If LCase(Request.Form("chkCompany")) = "on" Then
  1002.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("company")) & """")
  1003.                 End If
  1004.                 If LCase(Request.Form("chkCurrentPosition")) = "on" Then
  1005.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("current_position")) & """")
  1006.                 End If
  1007.                 If LCase(Request.Form("chkLocation")) = "on" Then
  1008.                     Response.Write(",""" & QuoteDoubleQuotes(rsExport("location")) & """")
  1009.                 End If
  1010.                 If LCase(Request.Form("chkDateStarted")) = "on" Then
  1011.                     strStartDate = CustomDateFormatDisplay(rsExport("response_start_date"))
  1012.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  1013.                         Response.Write ","""""
  1014.                     Else
  1015.                         Response.Write(",""" & DateValue(strStartDate) & """")
  1016.                     End If
  1017.                 End If
  1018.                 If LCase(Request.Form("chkTimeStarted")) = "on" Then
  1019.                     strStartDate = rsExport("response_start_date")
  1020.                     If IsNull(strStartDate) = True Or Len(strStartDate) = 0 Then
  1021.                         Response.Write ","""""
  1022.                     Else
  1023.                         Response.Write(",""" & TimeValue(strStartDate) & """")
  1024.                     End If
  1025.                 End If
  1026.                 If LCase(Request.Form("chkDateCompleted")) = "on" Then
  1027.                     strEndDate = CustomDateFormatDisplay(rsExport("response_end_date"))
  1028.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  1029.                         Response.Write ","""""
  1030.                     Else
  1031.                         Response.Write(",""" & DateValue(strEndDate) & """")
  1032.                     End If
  1033.                 End If
  1034.                 If LCase(Request.Form("chkTimeCompleted")) = "on" Then
  1035.                     strEndDate = rsExport("response_end_date")
  1036.                     If IsNull(strEndDate) = True Or Len(strEndDate) = 0 Then
  1037.                         Response.Write ","""""
  1038.                     Else
  1039.                         Response.Write(",""" & TimeValue(strEndDate) & """")
  1040.                     End If
  1041.                 End If
  1042.                 If LCase(Request.Form("chkIPAddress")) = "on" Then
  1043.                     Response.Write(",""" & rsExport("ip_address") & """")
  1044.                 End If
  1045.  
  1046.                 'Get an array of response ID's.  Each item in the array is in the format:
  1047.                 ' ITEM_ID||SUBITEM_ID||ANSWER_ID||ITEM_TYPE_ID
  1048.                 arrResponses = Split(strColumnCodes, ";")
  1049.  
  1050.                 'Each set of response ID's represents a column in the database.  Loop through, and render data for each entry
  1051.                 For i = 0 To UBound(arrResponses)
  1052.                     'Each time through, check to see if there is an "other" answer that needs to be displayed.
  1053.                     If strOtherYN = SUR_BOOLEAN_POSITIVE Then
  1054.                         If CStr(lngItemID) <> CStr(lngNextItemID) Then
  1055.                             If flgOtherYN = True Then
  1056.                                 Response.Write ",""" & Replace(QuoteDoubleQuotes(strOtherText & ""), vbCrLf, " ") & """"
  1057.                             Else
  1058.                                 Response.Write ","""""
  1059.                             End If
  1060.                             strOtherText = ""
  1061.                             flgOtherYN = False
  1062.                         End If
  1063.                     End If
  1064.                     'Get each of the ID's, which are in the format:
  1065.                     ' ITEM_ID||SUBITEM_ID||ANSWER_ID||ITEM_TYPE_ID||OTHER_YN
  1066.                     arrIDs = Split(arrResponses(i), "||")
  1067.                     lngItemID = arrIDs(0)
  1068.                     lngSubItemID = arrIDs(1)
  1069.                     lngAnswerID = arrIDs(2)
  1070.                     lngItemTypeID = arrIDs(3)
  1071.                     strOtherYN = arrIDs(4)
  1072.  
  1073.                     'Get the next item ID and answer ID
  1074.                     If i < UBound(arrResponses) Then
  1075.                         arrIDs = Split(arrResponses(i + 1), "||")
  1076.                         lngNextItemID = arrIDs(0)
  1077.                         lngNextAnswerItemID = arrIDs(2)
  1078.                     End If
  1079.  
  1080.                     'If the end of file has been reached, display blanks for the remaining entries
  1081.                     If rsExport.EOF Then
  1082.                         If InStr(strNullMatrixQuestions, "|" & lngItemID & "|") = 0 Then
  1083.                             Response.Write ","""""
  1084.                         End If
  1085.                     Else
  1086.                         'If the item_id in the recordset matches the current item ID in the column codes collection, check
  1087.                         'to see if it's a match, and if so, render the answer.  Otherwise, render a blank placeholder.
  1088.                         If CStr(lngItemID) = CStr(rsExport("item_id")) And CStr(lngResponseID = rsExport("response_id")) Then
  1089.                             'Based on the item type, check to see if the current row in the recordset is the same row.  If so,
  1090.                             'display the data from the row.  If not, render an empty field
  1091.                             If CStr(lngItemTypeID) = CStr(SUR_ITEM_MATRIX_MULTISELECT_CHECKBOXES) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_MATRIX_SINGLE_SELECT_OPTIONS) Then
  1092.                                 'If the answer was null, display a null value for each entry for this item
  1093.                                 If CStr(rsExport("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
  1094.                                     'Get the row and column counts, and multiply them together for the total number of
  1095.                                     'columns in the spreadsheet
  1096.                                     lngTotalColumnCount = CLng(GetSubItemCount(rsExport("item_id"))) * CLng(GetAnswerCount(rsExport("item_id")))
  1097.                                     For x = 1 to lngTotalColumnCount
  1098.                                         Response.Write ","""""
  1099.                                     Next
  1100.                                     strNullMatrixQuestions = strNullMatrixQuestions & "|" & rsExport("item_id") & "|"
  1101.                                     rsExport.MoveNext
  1102.                                 Else
  1103.                                     'If the current answer matches, display "Selected"
  1104.                                     If CStr(lngSubItemID) = CStr(rsExport("subitem_id")) And CStr(lngAnswerID) = CStr(rsExport("answer_id")) Then
  1105.                                         Response.Write ",""Selected"""
  1106.                                         rsExport.MoveNext
  1107.                                     Else
  1108.                                         Response.Write ","""""
  1109.                                     End If
  1110.                                 End If
  1111.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_MATRIX_RATING_SCALE) Then
  1112.                                 'Get the minimum and maximum value for the rating scale
  1113.                                 lngMinimumValue = CLng(rsExport("minimum_value"))
  1114.                                 lngMaximumValue = CLng(rsExport("maximum_value"))
  1115.  
  1116.                                 'If the answer was null, display a null value for each entry for this item
  1117.                                 If CStr(rsExport("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
  1118.                                     'Get the row and column counts, and multiply them together for the total number of
  1119.                                     'columns in the spreadsheet.  The column count is the difference between the ends of the rating scale, plus one
  1120.                                     lngTotalColumnCount = CLng(GetSubItemCount(rsExport("item_id"))) * ((lngMaximumValue - lngMinimumValue) + 1)
  1121.                                     For x = 1 to lngTotalColumnCount
  1122.                                         Response.Write ","""""
  1123.                                     Next
  1124.                                     strNullMatrixQuestions = strNullMatrixQuestions & "|" & rsExport("item_id") & "|"
  1125.                                     rsExport.MoveNext
  1126.                                 Else
  1127.                                     If CStr(lngSubItemID) = CStr(rsExport("subitem_id")) Then
  1128.                                         'Loop through the rating scale range, from low to high, and check each number to see
  1129.                                         'if it was selected
  1130.                                         lngCurrentRatingValue = lngMinimumValue
  1131.                                         Do While lngCurrentRatingValue <= lngMaximumValue
  1132.                                             'Display the appropriate value
  1133.                                             If CStr(rsExport("ResponseAnswerText")) = CStr(lngCurrentRatingValue) Then
  1134.                                                 Response.Write ",""Selected"""
  1135.                                                 'Once an answer is found, display the rest of the columns for this row
  1136.                                                 'with no data
  1137.                                                 For y = lngCurrentRatingValue + 1 To lngMaximumValue
  1138.                                                     Response.Write ","""""
  1139.                                                 Next
  1140.                                                 lngCurrentRatingValue = lngMaximumValue + 1
  1141.                                                 rsExport.MoveNext
  1142.                                             Else
  1143.                                                 Response.Write ","""""
  1144.                                             End If
  1145.                                             lngCurrentRatingValue = lngCurrentRatingValue + 1
  1146.                                         Loop
  1147.                                     Else
  1148.                                         For x = lngMinimumValue To lngMaximumValue
  1149.                                             Response.Write ","""""
  1150.                                         Next
  1151.                                     End If
  1152.                                 End If
  1153.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_MATRIX_TEXT_BOXES) Then
  1154.                                 'If the answer was null, display a null value for each entry for this item
  1155.                                 If CStr(rsExport("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
  1156.                                     'Get the row and column counts, and multiply them together for the total number of
  1157.                                     'columns in the spreadsheet
  1158.                                     lngTotalColumnCount = CLng(GetSubItemCount(rsExport("item_id"))) * CLng(GetAnswerCount(rsExport("item_id")))
  1159.                                     For x = 1 to lngTotalColumnCount
  1160.                                         Response.Write ","""""
  1161.                                     Next
  1162.                                     strNullMatrixQuestions = strNullMatrixQuestions & "|" & rsExport("item_id") & "|"
  1163.                                     rsExport.MoveNext
  1164.                                 Else
  1165.                                     'If the current answer matches, display the text
  1166.                                     If CStr(lngSubItemID) = CStr(rsExport("subitem_id")) And CStr(lngAnswerID) = CStr(rsExport("answer_id")) Then
  1167.                                         Response.Write ",""" & Replace(QuoteDoubleQuotes(rsExport("ResponseAnswerText") & ""), vbCrLf, " ") & """"
  1168.                                         rsExport.MoveNext
  1169.                                     Else
  1170.                                         Response.Write ","""""
  1171.                                     End If
  1172.                                 End If
  1173.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_OPEN_ENDED_ONE_LINE) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_OPEN_ENDED_COMMENTS_BOX) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_DATE) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_NUMBER) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_DATABASE_DROPDOWN) Then
  1174.                                 If rsExport("ResponseAnswerText") = SUR_ANSWER_TEXT_NULL Then
  1175.                                     Response.Write ","""""
  1176.                                 Else
  1177.                                     Response.Write ",""" & Replace(QuoteDoubleQuotes(rsExport("ResponseAnswerText") & ""), vbCrLf, " ") & """"
  1178.                                 End If
  1179.                                 rsExport.MoveNext
  1180.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_CONSTANT_SUM) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_RANKING) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_OPEN_ENDED_ONE_OR_MORE_LINES) Then
  1181.                                 If rsExport("ResponseAnswerText") = SUR_ANSWER_TEXT_NULL Then
  1182.                                     Response.Write ","""""
  1183.                                     rsExport.MoveNext
  1184.                                 Else
  1185.                                     'If the current answer matches, display the text
  1186.                                     If CStr(lngSubItemID) = CStr(rsExport("subitem_id")) Then
  1187.                                         Response.Write ",""" & Replace(QuoteDoubleQuotes(rsExport("ResponseAnswerText") & ""), vbCrLf, " ") & """"
  1188.                                         rsExport.MoveNext
  1189.                                     Else
  1190.                                         Response.Write ","""""
  1191.                                     End If
  1192.                                 End If
  1193.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_YES_NO) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_TRUE_FALSE) Then
  1194.                                 'If the answer is Null, display empty text for both fields
  1195.                                 If rsExport("ResponseAnswerText") = SUR_ANSWER_TEXT_NULL Then
  1196.                                     Response.Write ","""","""""
  1197.                                     rsExport.MoveNext
  1198.                                 Else
  1199.                                     If CStr(lngItemTypeID) = CStr(SUR_ITEM_YES_NO) Then
  1200.                                         'Render both the Yes and No columns
  1201.                                         If CStr(SUR_BOOLEAN_POSITIVE_DISPLAY) = CStr(rsExport("ResponseAnswerText")) Then
  1202.                                             Response.Write ",""" & SUR_BOOLEAN_POSITIVE_DISPLAY & """"
  1203.                                             Response.Write ","""""
  1204.                                         Else 'Must equal SUR_BOOLEAN_NEGATIVE_DISPLAY
  1205.                                             Response.Write ","""""
  1206.                                             Response.Write ",""" & SUR_BOOLEAN_NEGATIVE_DISPLAY & """"
  1207.                                         End If
  1208.                                     Else 'SUR_ITEM_TRUE_FALSE
  1209.                                         'Render both the True and False columns
  1210.                                         If CStr(SUR_TRUE_FALSE_QUESTION_VALUE_TRUE) = CStr(rsExport("ResponseAnswerText")) Then
  1211.                                             Response.Write ",""" & SUR_TRUE_FALSE_QUESTION_VALUE_TRUE & """"
  1212.                                             Response.Write ","""""
  1213.                                         Else 'Must equal SUR_TRUE_FALSE_QUESTION_VALUE_FALSE
  1214.                                             Response.Write ","""""
  1215.                                             Response.Write ",""" & SUR_TRUE_FALSE_QUESTION_VALUE_FALSE & """"
  1216.                                         End If
  1217.                                     End If
  1218.                                     rsExport.MoveNext
  1219.                                 End If
  1220.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_SINGLE_SELECT_DROPDOWN) Then
  1221.                                 If CStr(rsExport("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
  1222.                                     Response.Write ","""""
  1223.                                     rsExport.MoveNext
  1224.                                 Else
  1225.                                     'If the current answer matches, display the text
  1226.                                     If CStr(lngAnswerID) = CStr(rsExport("answer_id")) Then
  1227.                                         Response.Write ",""Selected"""
  1228.                                         rsExport.MoveNext
  1229.                                     Else
  1230.                                         Response.Write ","""""
  1231.                                     End If
  1232.                                 End If
  1233.                             ElseIf CStr(lngItemTypeID) = CStr(SUR_ITEM_SINGLE_SELECT_OPTIONS) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_MULTISELECT_CHECKBOXES) Then
  1234.                                 'Capture a flag indicating whether or not this item has "other" text
  1235.                                 If rsExport("other_yn") = SUR_BOOLEAN_POSITIVE Then
  1236.                                     flgOtherYN = True
  1237.                                     'Capture the "other" text, if present, to render later
  1238.                                     If CStr(rsExport("ResponseAnswerText")) = CStr(SUR_ANSWER_TEXT_OTHER_SELECTED) Then
  1239.                                         strOtherText = rsExport("other_text")
  1240.                                         If strOtherText = SUR_ANSWER_TEXT_NULL Then
  1241.                                             strOtherText = ""
  1242.                                         End If
  1243.                                         rsExport.MoveNext
  1244.                                         If rsExport.EOF = True Then
  1245.                                             Response.Write ","""""
  1246.                                         End If
  1247.                                     End If
  1248.                                 Else
  1249.                                     flgOtherYN = False
  1250.                                 End If
  1251.  
  1252.                                 'If this is the same question, render the answer
  1253.                                 If Not rsExport.EOF Then
  1254.                                     If CStr(lngItemID) = CStr(rsExport("item_id")) Then
  1255.                                         If CStr(rsExport("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
  1256.                                             Response.Write ","""""
  1257.                                             rsExport.MoveNext
  1258.                                         Else
  1259.                                             'If the current answer matches, display the text
  1260.                                             If CStr(lngAnswerID) = CStr(rsExport("answer_id")) Then
  1261.                                                 Response.Write ",""Selected"""
  1262.                                                 rsExport.MoveNext
  1263.                                             Else
  1264.                                                 Response.Write ","""""
  1265.                                             End If
  1266.                                         End If
  1267.                                     Else
  1268.                                         Response.Write ","""""
  1269.                                     End If
  1270.                                 End If
  1271.                             Else
  1272.                                 Response.Write ","""""
  1273.                             End If
  1274.                         Else
  1275.                             'Display the appropriate number of blank columns
  1276.                             If CStr(lngItemTypeID) = CStr(SUR_ITEM_YES_NO) Or CStr(lngItemTypeID) = CStr(SUR_ITEM_TRUE_FALSE) Then
  1277.                                 Response.Write ","""","""""
  1278.                             Else
  1279.                                 If InStr(strNullMatrixQuestions, "|" & lngItemID & "|") = 0 Then
  1280.                                     Response.Write ","""""
  1281.                                 End If
  1282.                             End If
  1283.                         End If
  1284.  
  1285.                         'Advance the item ID
  1286.                         If rsExport.EOF = False Then
  1287.                             If CStr(lngItemID) <> CStr(rsExport("item_id")) Then
  1288.                                 lngCurrentItemID = rsExport("item_id")
  1289.                             End If
  1290.                         End If
  1291.                     End If
  1292.                 Next
  1293.  
  1294.                 'If the last question had an "other" option, display either the "other" response or a blank placeholder
  1295.                 If strOtherYN = SUR_BOOLEAN_POSITIVE Then
  1296.                     If flgOtherYN = True Then
  1297.                         Response.Write ",""" & Replace(QuoteDoubleQuotes(strOtherText & ""), vbCrLf, " ") & """"
  1298.                     Else
  1299.                         Response.Write ","""""
  1300.                     End If
  1301.                     strOtherText = ""
  1302.                     flgOtherYN = False
  1303.                 End If
  1304.  
  1305.                 'Add a carriage return/line feed
  1306.                 Response.Write(vbCrLf)
  1307.  
  1308.                 'Reset the response ID to the current record
  1309.                 If Not rsExport.EOF Then
  1310.                     lngResponseID = rsExport("response_id")
  1311.                 End If
  1312.  
  1313.                 'Reset the list of Null matrix questions for the new response
  1314.                 strNullMatrixQuestions = ""
  1315.             Loop
  1316.         End If
  1317.     Else
  1318.         'No other export formats
  1319.     End If
  1320.  
  1321.     'Clean up
  1322.     rsExport.Close
  1323.     Set rsExport = Nothing
  1324.     If LCase(Request.Form("chkHiddenFields")) = "on" Then
  1325.         rsResponseHiddenFields.Close
  1326.         Set rsResponseHiddenFields = Nothing
  1327.     End If
  1328.  
  1329. 'Helper function for rendering a response
  1330. Sub DisplayIndividualResponse(strResponse, lngItemTypeID)
  1331.  
  1332.     Dim vaAnswers
  1333.     Dim vaNameValuePair
  1334.     Dim strName
  1335.     Dim strValue
  1336.     Dim strResponseModified
  1337.     Dim lngPosition
  1338.     Dim i
  1339.  
  1340.     'Clean off an extraneous trailing semi-colon and space, and display the response
  1341.     strResponse = Trim(strResponse)
  1342.     strResponse = Left(strResponse, Len(strResponse) - 1)
  1343.  
  1344.     'If the item type is a SUR_ITEM_MATRIX_MULTISELECT_CHECKBOXES, compress all of the answers into a more readable format.
  1345.     'The format is currently name value pairs, with multiple identical names.  Merge all values into one name with multiple
  1346.     'values.
  1347.     If CLng(lngItemTypeID) = SUR_ITEM_MATRIX_MULTISELECT_CHECKBOXES Then
  1348.         If strResponse = SUR_ANSWER_NULL_DISPLAY Then
  1349.             strResponseModified = SUR_ANSWER_NULL_DISPLAY
  1350.         Else
  1351.             'Split the string on the delimiter between each name/value pair
  1352.             vaAnswers = Split(strResponse, "; ")
  1353.  
  1354.             'Loop through each of the name/value pairs
  1355.             For i = 0 To UBound(vaAnswers)
  1356.                 'Extract the name and value from the name/value pair
  1357.                 vaNameValuePair = Split(vaAnswers(i), " = ")
  1358.                 strName = vaNameValuePair(0)
  1359.                 'fix 4/2006 for subscript out of range error
  1360.                 If UBound(vaNameValuePair) > 0 Then
  1361.                     strValue = vaNameValuePair(1)
  1362.                 Else
  1363.                     strValue = "[No Value Found]"
  1364.                 End If
  1365.  
  1366.                 'If this name is not yet in the modified string, add it along with the value
  1367.                 If InStr(strResponseModified, strName & " = ") = 0 Then
  1368.                     strResponseModified = strResponseModified & strName & " = " & strValue & "; "
  1369.                 Else
  1370.                     'The name is already in the string, so just add the new value to the string
  1371.                     lngPosition = InStr(strResponseModified, strName & " = ")
  1372.                     lngPosition = InStr(lngPosition + 1, strResponseModified, ";")
  1373.                     strResponseModified = Left(strResponseModified, lngPosition - 1) & ", " & strValue & Right(strResponseModified, Len(strResponseModified) - lngPosition + 1)
  1374.                 End If
  1375.             Next
  1376.         End If
  1377.  
  1378.         strResponse = strResponseModified
  1379.     End If
  1380.  
  1381.     'Remove the value SurveyAnswerTextNull if it was answered for the Other text
  1382.     If InStr(strResponse, SUR_ANSWER_TEXT_NULL) > 0 Then
  1383.         strResponse = Replace(strResponse, SUR_ANSWER_TEXT_NULL, "")
  1384.     End If
  1385.  
  1386.     If Left(strResponse, 1) = ";" Then
  1387.         strResponse = Right(strResponse, Len(strResponse) - 1)
  1388.     End If
  1389.  
  1390.     Response.Write(",""" & QuoteDoubleQuotes(Trim(strResponse)) & """")
  1391. End Sub
  1392.  
  1393. Function GetHiddenFieldHeaders()
  1394.  
  1395.     Dim strReturnValue
  1396.     Dim rsHiddenFields
  1397.     Dim strSQL
  1398.  
  1399.     'Initialization
  1400.     strReturnValue = ""
  1401.     Set rsHiddenFields = Server.CreateObject("ADODB.Recordset")
  1402.  
  1403.     'Check to see if the hidden fields should be displayed.  If so, add all of the hidden fields to the header
  1404.     If LCase(Request.Form("chkHiddenFields")) = "on" Then
  1405.         'Select all of the hidden fields for this survey
  1406.         strSQL = "SELECT hidden_field_display_name " & _
  1407.                  "FROM sur_hidden_field " & _
  1408.                  "WHERE survey_id = " & lngSurveyID & _
  1409.                  " ORDER BY order_number"
  1410.         rsHiddenFields.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  1411.  
  1412.         'If there are hidden fields, loop through and add each one to the list of columns
  1413.         If Not rsHiddenFields.EOF Then
  1414.             Do While Not rsHiddenFields.EOF
  1415.                 strReturnValue = strReturnValue & """" & rsHiddenFields("hidden_field_display_name") & ""","
  1416.                 rsHiddenFields.MoveNext
  1417.             Loop
  1418.         End If
  1419.  
  1420.         'Clean up
  1421.         rsHiddenFields.Close
  1422.     End If
  1423.  
  1424.     'Return
  1425.     GetHiddenFieldHeaders = strReturnValue
  1426.  
  1427. End Function
  1428.  
  1429. 'This function returns the values for the specified number of hidden fields
  1430. Function GetHiddenFieldValues(rsResponseHiddenFields, lngHiddenFieldCount)
  1431.  
  1432.     Dim strReturnValue
  1433.     Dim i
  1434.  
  1435.     'Check to see if the hidden fields should be displayed.  If so, add all of the hidden fields to the header
  1436.     If LCase(Request.Form("chkHiddenFields")) = "on" Then
  1437.     x = ""
  1438.         'Loop through the recordset the specified number of times
  1439.         For i = 1 To lngHiddenFieldCount
  1440.             If Not rsResponseHiddenFields.EOF Then
  1441.                 If IsNull(rsResponseHiddenFields("answer_text")) Then
  1442.                     x = ""
  1443.                 Else
  1444.                     x = rsResponseHiddenFields("answer_text")
  1445.                 End If
  1446.             End If
  1447.             strReturnValue = strReturnValue & """" & x & ""","
  1448.             If Not rsResponseHiddenFields.EOF Then
  1449.                 rsResponseHiddenFields.MoveNext
  1450.             End If
  1451.         Next
  1452.     End If
  1453.  
  1454.     'Return
  1455.     GetHiddenFieldValues = strReturnValue
  1456.  
  1457. End Function
  1458. %>
  1459.