home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
bombers.k12.ar.us
/
bombers.k12.ar.us.tar
/
bombers.k12.ar.us
/
survey_unconfigured
/
CopyPage.asp
< prev
next >
Wrap
Text File
|
2006-10-25
|
16KB
|
367 lines
<!--#Include File="Include/Top_inc.asp"-->
<%
'***********************************************************************
' Application: SelectSurveyASP Advanced v8.1.11
' Author: Aaron Baril for ClassApps.com
' Page Description: This page copies an entire page in a survey.
'
' COPYRIGHT NOTICE
'
' See attached Software License Agreement
'
' (c) Copyright 2002 - 2006 by ClassApps.com. All rights reserved.
'***********************************************************************
%>
<!--#Include File="Include/Config_inc.asp"-->
<!--#Include File="Include/Utility_inc.asp"-->
<!--#Include File="Include/adovbs_inc.asp"-->
<!--#Include File="Include/ID_inc.asp"-->
<!--#Include File="Include/CurrentUser_inc.asp"-->
<!--#Include File="Include/SurveySecurity_inc.asp"-->
<!--#Include File="Include/Constants_inc.asp"-->
<!--#Include File="Include/SurveyUtility_inc.asp"-->
<!--#Include File="Include/Collection_inc.asp"-->
<%
'If the user does not have "Create" or "Admin" permission, redirect them to the access denied page.
If lngUserSecurityLevel <> SUR_SECURITY_LEVEL_CREATE And lngUserSecurityLevel <> SUR_SECURITY_LEVEL_ADMIN Then
Response.Redirect "AccessDenied.asp?Reason=" & SUR_ACCESS_DENIED_NOT_ADMIN_SECURITY_LEVEL
End If
'Do not allow a user to copy a page in a survey that already has responses. Instead, redirect the user back
'to the modify survey page
If HasResponses(Request.Form("SurveyID")) = True Then
Response.Redirect "ModifySurvey.asp?SurveyID=" & Request.Form("SurveyID")
End If
Dim strSQL
Dim conNewPage
Dim lngSurveyID
Dim rsOriginalPage
Dim rsItemCountOnPage
Dim lngItemCountOnPage
Dim strOldToNewItemIDs
Dim strOldToNewAnswerIDs
Dim lngTempID
Dim lngNewItemID
Dim lngNewAnswerID
Dim lngPageNumber
Dim lngMinimumNumberResponses
Dim lngMaximumNumberResponses
Dim lngRowTextWidth
Dim lngAnswerTotal
Dim strItemAlias
Dim lngAnswerValue
'Initialization
Set rsOriginalPage = Server.CreateObject("ADODB.Recordset")
Set conNewPage = Server.CreateObject("ADODB.Connection")
Set rsItemCountOnPage = Server.CreateObject("ADODB.Recordset")
conNewPage.Open SURVEY_APP_CONNECTION
lngSurveyID = Request.Form("SurveyID")
lngPageNumber = Request.Form("PageNumber")
'1. Get the count of items on the specified page
strSQL = "SELECT count(item_id) As ItemCountOnPage " & _
"FROM sur_survey_to_item_mapping " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & lngPageNumber
rsItemCountOnPage.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
rsItemCountOnPage.MoveFirst
lngItemCountOnPage = rsItemCountOnPage("ItemCountOnPage")
rsItemCountOnPage.Close
Set rsItemCountOnPage = Nothing
'2. Make space in the mapping table for the copied set of items. Increase the page number by one starting with
'the page selected, and increase the order number by the number of items on the page.
strSQL = "UPDATE sur_survey_to_item_mapping " & _
"SET page_number = page_number + 1, " & _
"order_number = order_number + " & lngItemCountOnPage & _
" WHERE page_number >= " & lngPageNumber & _
" AND survey_id = " & lngSurveyID
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
'3. Update the page number for any page conditions that might have been associated with the original page
strSQL = "UPDATE sur_page_condition " & _
"SET page_number = page_number + 1 " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & lngPageNumber
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
'4. Update the page number for any page property entries that might have been associated with the original page
strSQL = "UPDATE sur_page " & _
"SET page_number = page_number + 1 " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number >= " & lngPageNumber
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
'5. Copy all of the data from the item table (SUR_ITEM) for the specified page
strSQL = "SELECT * FROM sur_item WHERE item_id IN (" & _
ConvertSQLInClause("SELECT item_id " & _
"FROM sur_survey_to_item_mapping " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & (lngPageNumber + 1), "item_id") & ")"
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there are items for this survey
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
'Build the SQL statements to insert the new records into the SUR_ITEM table
Do While Not rsOriginalPage.EOF
'Create values needed for the insert
lngMinimumNumberResponses = rsOriginalPage("minimum_number_responses")
If IsNull(lngMinimumNumberResponses) = True Then
lngMinimumNumberResponses = "Null"
End If
lngMaximumNumberResponses = rsOriginalPage("maximum_number_responses")
If IsNull(lngMaximumNumberResponses) = True Then
lngMaximumNumberResponses = "Null"
End If
lngRowTextWidth = rsOriginalPage("row_text_width")
If IsNull(lngRowTextWidth) = True Then
lngRowTextWidth= "Null"
End If
lngAnswerTotal = rsOriginalPage("answer_total")
If IsNull(lngAnswerTotal) = True Or Len(lngAnswerTotal) = 0 Then
lngAnswerTotal = "Null"
End If
strItemAlias = rsOriginalPage("item_alias")
If IsNull(strItemAlias) = True Or Len(strItemAlias) = 0 Then
strItemAlias = ""
End If
'Create the new ID for the new item
lngTempID = ID_GetNextAvailableID("SurveyGenerationItem")
'Create dictionary of old item ID's to new item ID's
strOldToNewItemIDs = strOldToNewItemIDs & rsOriginalPage("item_id") & ";" & lngTempID & ";"
strSQL = "INSERT INTO sur_item(item_id, item_type_id, item_text, item_sub_text, item_alias, required_yn, " & _
"random_answer_order_yn, email_address_yn, other_yn, other_text, minimum_value, maximum_value, answer_total, " & _
"minimum_number_responses, maximum_number_responses, row_text_width, display_format, image_path, " & _
"image_width, image_height, image_alignment, default_value, database_sql, database_dsn, " & _
"subitem_count) VALUES(" & _
lngTempID & ", " & _
rsOriginalPage("item_type_id") & ", " & _
SQLEncode(rsOriginalPage("item_text")) & ", " & _
SQLEncode(rsOriginalPage("item_sub_text")) & ", " & _
SQLEncode(strItemAlias) & ", " & _
SQLEncode(rsOriginalPage("required_yn")) & ", " & _
SQLEncode(rsOriginalPage("random_answer_order_yn")) & ", " & _
SQLEncode(rsOriginalPage("email_address_yn")) & ", " & _
SQLEncode(rsOriginalPage("other_yn")) & ", " & _
SQLEncode(rsOriginalPage("other_text")) & ", "
'For minimum and maximum values, make sure that Null is not used
If IsNull(rsOriginalPage("minimum_value")) = True Then
strSQL = strSQL & "'', "
Else
strSQL = strSQL & SQLEncode(rsOriginalPage("minimum_value")) & ", "
End If
If IsNull(rsOriginalPage("maximum_value")) = True Then
strSQL = strSQL & "'', "
Else
strSQL = strSQL & SQLEncode(rsOriginalPage("maximum_value")) & ", "
End If
strSQL = strSQL & lngAnswerTotal & ", " & _
lngMinimumNumberResponses & ", " & _
lngMaximumNumberResponses & ", " & _
lngRowTextWidth & ", " & _
SQLEncode(rsOriginalPage("display_format")) & ", " & _
SQLEncode(rsOriginalPage("image_path")) & ", " & _
SQLEncode(rsOriginalPage("image_width")) & ", "
'For image height, make sure that Null is not used
If IsNull(rsOriginalPage("image_height")) = True Then
strSQL = strSQL & "'', "
Else
strSQL = strSQL & SQLEncode(rsOriginalPage("image_height")) & ", "
End If
strSQL = strSQL & SQLEncode(rsOriginalPage("image_alignment")) & ", " & _
SQLEncode(rsOriginalPage("default_value")) & ", " & _
SQLEncode(rsOriginalPage("database_sql")) & ", " & _
SQLEncode(rsOriginalPage("database_dsn")) & ", "
If IsNull(rsOriginalPage("subitem_count")) = True Then
strSQL = strSQL & "''"
Else
strSQL = strSQL & SQLEncode(rsOriginalPage("subitem_count"))
End If
strSQL = strSQL & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
rsOriginalPage.MoveNext
Loop
End If
rsOriginalPage.Close
'6. Copy all of the data from the item answer table (SUR_ITEM_ANSWER)
strSQL = "SELECT * FROM sur_item_answer WHERE item_id IN (" & _
ConvertSQLInClause("SELECT item_id " & _
"FROM sur_survey_to_item_mapping " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & (lngPageNumber + 1), "item_id") & ")"
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there are item answers for this survey
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
'Build the SQL statements to insert the new records into the SUR_ITEM_ANSWER table
Do While Not rsOriginalPage.EOF
'Get the new item ID from the collection that maps old item ID's to new item ID's
lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalPage("item_id")))
'Create the new answer ID for the new answer
lngTempID = ID_GetNextAvailableID("SurveyGenerationAnswer")
'Create dictionary of old item ID's to new item ID's
strOldToNewAnswerIDs = strOldToNewAnswerIDs & rsOriginalPage("answer_id") & ";" & lngTempID & ";"
'Get the answer value
If IsNull(rsOriginalPage("answer_value")) = True Then
lngAnswerValue = "Null"
Else
lngAnswerValue = rsOriginalPage("answer_value")
End If
strSQL = "INSERT INTO sur_item_answer(item_id, answer_id, answer_text, answer_value, order_number, " & _
"default_yn) VALUES(" & _
lngNewItemID & ", " & _
lngTempID & ", " & _
SQLEncode(rsOriginalPage("answer_text")) & ", " & _
lngAnswerValue & ", " & _
rsOriginalPage("order_number") & ", " & _
SQLEncode(rsOriginalPage("default_yn")) & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
rsOriginalPage.MoveNext
Loop
End If
rsOriginalPage.Close
'7. Copy all of the data from the mapping table (SUR_SURVEY_TO_ITEM_MAPPING)
strSQL = "SELECT * FROM sur_survey_to_item_mapping " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & (lngPageNumber + 1)
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there are items in the mapping table for this survey
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
'Build the SQL statements to insert the new records into the SUR_SURVEY_TO_ITEM_MAPPING table
Do While Not rsOriginalPage.EOF
'Get the new item ID from the collection that maps old item ID's to new item ID's.
lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalPage("item_id")))
'Note that we subtract the number of items on the page from the order number so we can fill in the
'spaces made earlier. Also, we are using the page number of the original page, since the original
'page was bumped up one page.
strSQL = "INSERT INTO sur_survey_to_item_mapping(survey_id, item_id, order_number, page_number) VALUES(" & _
lngSurveyID & ", " & _
lngNewItemID & ", " & _
rsOriginalPage("order_number") - lngItemCountOnPage & ", " & _
lngPageNumber & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
rsOriginalPage.MoveNext
Loop
End If
rsOriginalPage.Close
'8. Copy all of the data from the subitem table (SUR_SUBITEM)
strSQL = "SELECT * FROM sur_subitem WHERE item_id IN (" & _
ConvertSQLInClause("SELECT item_id " & _
"FROM sur_survey_to_item_mapping " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & (lngPageNumber + 1), "item_id") & ")"
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there are subitems for this survey
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
'Build the SQL statements to insert the new records into the SUR_SUBITEM table
Do While Not rsOriginalPage.EOF
'Get the new item ID from the collection that maps old item ID's to new item ID's
lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalPage("item_id")))
'Create the new answer ID for the new answer
lngTempID = ID_GetNextAvailableID("SurveyGenerationSubitem")
strSQL = "INSERT INTO sur_subitem(subitem_id, item_id, subitem_text, order_number) " & _
"VALUES(" & _
lngTempID & ", " & _
lngNewItemID & ", " & _
SQLEncode(rsOriginalPage("subitem_text")) & ", " & _
rsOriginalPage("order_number") & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
rsOriginalPage.MoveNext
Loop
End If
rsOriginalPage.Close
'9. Copy all of the data from the page condition table (SUR_PAGE_CONDITION)
strSQL = "SELECT * FROM sur_page_condition " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & lngPageNumber + 1
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there are page conditions for this survey
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
'Build the SQL statements to insert the new records into the SUR_PAGE_CONDITION table
Do While Not rsOriginalPage.EOF
'Generate a new value for the page_condition_id column
lngTempID = ID_GetNextAvailableID("SurveyGenerationPageConditionID")
'Get the new answer ID. If the old answer ID equals SUR_ANSWER_ID_NULL then use SUR_ANSWER_ID_NULL.
'Otherwise, get the answer from the collection that maps old answer ID's to new answer ID's
If CStr(rsOriginalPage("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
lngNewAnswerID = SUR_ANSWER_ID_NULL
Else
lngNewAnswerID = GetValueFromCollection(strOldToNewAnswerIDs, CStr(rsOriginalPage("answer_id")))
End If
strSQL = "INSERT INTO sur_page_condition(page_condition_id, survey_id, page_number, dependent_item_id, " & _
"group_number, operator_id, answer_id, answer_text) VALUES(" & _
lngTempID & ", " & _
lngSurveyID & ", " & _
lngPageNumber & ", " & _
rsOriginalPage("dependent_item_id") & ", " & _
rsOriginalPage("group_number") & ", " & _
rsOriginalPage("operator_id") & ", " & _
rsOriginalPage("answer_id") & ", " & _
SQLEncode(rsOriginalPage("answer_text")) & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
rsOriginalPage.MoveNext
Loop
End If
rsOriginalPage.Close
'10. Copy all of the data from the page property table (SUR_PAGE)
strSQL = "SELECT * FROM sur_page " & _
"WHERE survey_id = " & lngSurveyID & _
" AND page_number = " & lngPageNumber + 1
Set rsOriginalPage = conNewPage.Execute(ConvertSQL(strSQL), , adCmdText)
'Make sure that there is a page property entry for this page
If Not rsOriginalPage.EOF Then
rsOriginalPage.MoveFirst
strSQL = "INSERT INTO sur_page(survey_id, page_number, page_title, page_introduction) " & _
"VALUES(" & _
rsOriginalPage("survey_id") & ", " & _
lngPageNumber & ", " & _
SQLEncode(rsOriginalPage("page_title")) & ", " & _
SQLEncode(rsOriginalPage("page_introduction")) & ")"
conNewPage.Execute ConvertSQL(strSQL), , adCmdText
End If
rsOriginalPage.Close
'Clean up
conNewPage.Close
Set conNewPage = Nothing
'Redirect to the modify survey page for the survey just created
Response.Redirect "ModifySurvey.asp?SurveyID=" & lngSurveyID & "ItemID" & lngTempID
%>