home *** CD-ROM | disk | FTP | other *** search
/ bombers.k12.ar.us / bombers.k12.ar.us.tar / bombers.k12.ar.us / survey_unconfigured / CreateEmailListAction.asp < prev    next >
Text File  |  2006-10-25  |  12KB  |  287 lines
  1. <!--#Include File="Include/Top_inc.asp"-->
  2. <%
  3. '***********************************************************************
  4. '   Application: SelectSurveyASP Advanced v8.1.11
  5. '   Author: Aaron Baril for ClassApps.com
  6. '   Page Description: This page creates a new email list either from scratch or by
  7. '                      copying an existing email list.
  8. '
  9. '   COPYRIGHT NOTICE                                
  10. '
  11. '   See attached Software License Agreement
  12. '
  13. '   (c) Copyright 2002 - 2006 by ClassApps.com.  All rights reserved.
  14. '***********************************************************************
  15. %>
  16. <!--#Include File="Include/Config_inc.asp"-->
  17. <!--#Include File="Include/Utility_inc.asp"-->
  18. <!--#Include File="Include/adovbs_inc.asp"-->
  19. <!--#Include File="Include/ID_inc.asp"-->
  20. <!--#Include File="Include/CurrentUser_inc.asp"-->
  21. <!--#Include File="Include/SurveySecurity_inc.asp"-->
  22. <!--#Include File="Include/Constants_inc.asp"-->
  23. <!--#Include File="Include/Collection_inc.asp"-->
  24. <%
  25.     'If the user does not have "Create" or "Admin" permission, redirect them to the access denied page.
  26.     If lngUserSecurityLevel <> SUR_SECURITY_LEVEL_CREATE And lngUserSecurityLevel <> SUR_SECURITY_LEVEL_ADMIN Then
  27.         Response.Redirect "AccessDenied.asp?Reason=" & SUR_ACCESS_DENIED_NOT_ADMIN_SECURITY_LEVEL
  28.     End If
  29.  
  30.     Dim strSQL
  31.     Dim conNewEmailList
  32.     Dim lngOriginalEmailListID
  33.     Dim lngNewEmailListID
  34.     Dim rsEmailAddressListSource
  35.     Dim rsCount
  36.     Dim strActiveYN
  37.     Dim strEmailListName
  38.     Dim strEmailListDescription
  39.     Dim lngOriginalEmailAddressCount
  40.     Dim lngEmailAddressCount
  41.     Dim lngNewEmailAddressID
  42.     Dim strCustomData1
  43.     Dim strCustomData2
  44.     Dim strCustomData3
  45.     Dim strWhere
  46.                         
  47.     'Initialization
  48.     Set rsEmailAddressListSource = Server.CreateObject("ADODB.Recordset")
  49.     Set rsCount = Server.CreateObject("ADODB.Recordset")
  50.     Set conNewEmailList = Server.CreateObject("ADODB.Connection")
  51.     conNewEmailList.Open SURVEY_APP_CONNECTION
  52.     lngNewEmailListID = ID_GetNextAvailableID("SurveyGenerationEmailList")
  53.     strEmailListName = Request.Form("txtEmailListName")
  54.     lngEmailAddressCount = 0
  55.     
  56.     'Set the values for all fields in the email list based on whether this is a copy or a new email list
  57.     If CLng(Request.Form("optType")) = SUR_CREATE_EMAIL_LIST_FROM_SCRATCH Then
  58.         'No fields specific to creating an email list from scratch
  59.     ElseIf CLng(Request.Form("optType")) = SUR_CREATE_EMAIL_LIST_FROM_USERS Then
  60.         'Select the users to copy for the new list
  61.         strSQL = "SELECT email_address, first_name, last_name, custom_data_1, custom_data_2, custom_data_3 " & _
  62.                   "FROM sur_user " & _
  63.                   "WHERE user_id = user_id "
  64.         'If the user is filtering the users, add a filter to the query
  65.         If CStr(Request.Form("optUserFilterType")) = CStr(SUR_EMAIL_LIST_WITH_FILTER) Then
  66.             'Add the filter for the active flag
  67.             If Request.Form("cboUserActive") <> SUR_COMBO_ALL Then
  68.                 strWhere = strWhere & " AND active_yn = " & SQLEncode(Request.Form("cboUserActive"))
  69.             End If
  70.  
  71.             'Add the filter for the email address
  72.             If Len(Trim(Request.Form("txtUserEmailAddress"))) > 0 Then
  73.                 strWhere = strWhere & " AND email_address LIKE " & SQLEncodeContains(Request.Form("txtUserEmailAddress"))
  74.             End If
  75.  
  76.             'Add the filter for the position field
  77.             If Len(Trim(Request.Form("txtUserPosition"))) > 0 Then
  78.                 strWhere = strWhere & " AND current_position LIKE " & SQLEncodeContains(Request.Form("txtUserPosition"))
  79.             End If
  80.  
  81.             'Add the filter for the company field
  82.             If Len(Trim(Request.Form("txtUserCompany"))) > 0 Then
  83.                 strWhere = strWhere & " AND company LIKE " & SQLEncodeContains(Request.Form("txtUserCompany"))
  84.             End If
  85.  
  86.             'Add the filter for the location field
  87.             If Len(Trim(Request.Form("txtUserLocation"))) > 0 Then
  88.                 strWhere = strWhere & " AND location LIKE " & SQLEncodeContains(Request.Form("txtUserLocation"))
  89.             End If
  90.  
  91.             'Add the filter for the first custom data field
  92.             If Len(Trim(Request.Form("txtUserCustomData1"))) > 0 Then
  93.                 strWhere = strWhere & " AND custom_data_1 LIKE " & SQLEncodeContains(Request.Form("txtUserCustomData1"))
  94.             End If
  95.  
  96.             'Add the filter for the second custom data field
  97.             If Len(Trim(Request.Form("txtUserCustomData2"))) > 0 Then
  98.                 strWhere = strWhere & " AND custom_data_2 LIKE " & SQLEncodeContains(Request.Form("txtUserCustomData2"))
  99.             End If
  100.  
  101.             'Add the filter for the third custom data field
  102.             If Len(Trim(Request.Form("txtUserCustomData3"))) > 0 Then
  103.                 strWhere = strWhere & " AND custom_data_3 LIKE " & SQLEncodeContains(Request.Form("txtUserCustomData3"))
  104.             End If
  105.         End If
  106.  
  107.         'Put the SQL together
  108.         strSQL = strSQL & strWhere
  109.         
  110.         'Open the recordset
  111.         rsEmailAddressListSource.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  112.         
  113.         'If there are users to copy, copy them for the new list
  114.         If Not rsEmailAddressListSource.EOF Then
  115.             'Get the count of users to be imported
  116.             strSQL = "SELECT count(user_id) As UserCount " & _
  117.                      "FROM sur_user " & _
  118.                      "WHERE user_id = user_id " & strWhere
  119.             rsCount.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  120.             rsCount.MoveFirst
  121.             lngOriginalEmailAddressCount = CLng(rsCount("UserCount"))
  122.             rsCount.Close
  123.             Set rsCount = Nothing
  124.             'Get a range of ID's for email addresses
  125.             lngNewEmailAddressID = ID_GetRangeOfIDs("SurveyGenerationEmailAddress", lngOriginalEmailAddressCount)
  126.  
  127.             rsEmailAddressListSource.MoveFirst
  128.             Do While Not rsEmailAddressListSource.EOF
  129.                 strCustomData1 = rsEmailAddressListSource("custom_data_1")
  130.                 If Len(strCustomData1) = 0 Or IsNull(strCustomData1) = True Then
  131.                     strCustomData1 = ""
  132.                 End If
  133.                 strCustomData2 = rsEmailAddressListSource("custom_data_2")
  134.                 If Len(strCustomData2) = 0 Or IsNull(strCustomData2) = True Then
  135.                     strCustomData2 = ""
  136.                 End If
  137.                 strCustomData3 = rsEmailAddressListSource("custom_data_3")
  138.                 If Len(strCustomData3) = 0 Or IsNull(strCustomData3) = True Then
  139.                     strCustomData3 = ""
  140.                 End If
  141.                 
  142.                 'Insert the new email address
  143.                 strSQL = "INSERT INTO sur_email_address(email_address_id, email_list_id, email_address, first_name, " & _
  144.                             "last_name, custom_data_1, custom_data_2, custom_data_3, active_yn, deleted_yn) " & _
  145.                          "VALUES(" & _
  146.                          lngNewEmailAddressID & ", " & _
  147.                          lngNewEmailListID & ", " & _
  148.                          SQLEncode(rsEmailAddressListSource("email_address")) & ", " & _
  149.                          SQLEncode(rsEmailAddressListSource("first_name")) & ", " & _
  150.                          SQLEncode(rsEmailAddressListSource("last_name")) & ", " & _
  151.                          SQLEncode(strCustomData1) & ", " & _
  152.                          SQLEncode(strCustomData2) & ", " & _
  153.                          SQLEncode(strCustomData3) & ", " & _
  154.                          SQLEncode(SUR_BOOLEAN_POSITIVE) & ", " & _
  155.                          SQLEncode(SUR_BOOLEAN_NEGATIVE) & ")"
  156.                 conNewEmailList.Execute ConvertSQL(strSQL), , adCmdText
  157.                 
  158.                 'Track the number of actual email addresses inserted
  159.                 lngEmailAddressCount = lngEmailAddressCount + 1
  160.                 
  161.                 'Incremeent the email address ID counter
  162.                 lngNewEmailAddressID = lngNewEmailAddressID + 1
  163.                 
  164.                 rsEmailAddressListSource.MoveNext
  165.             Loop
  166.  
  167.             'Clean up
  168.             rsEmailAddressListSource.Close
  169.         End If
  170.  
  171.         'Finish cleaning up
  172.         Set rsEmailAddressListSource = Nothing
  173.     Else 'SUR_CREATE_EMAIL_LIST_FROM_EXISTING
  174.         'Load the existing data from the database
  175.         strSQL = "SELECT email_address_count, list_description " & _
  176.                   "FROM sur_email_list " & _
  177.                   "WHERE email_list_id = " & Request.Form("cboEmailList")
  178.         rsEmailAddressListSource.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  179.         rsEmailAddressListSource.MoveFirst
  180.  
  181.         'Capture all of the fields in local variables
  182.         strEmailListDescription = rsEmailAddressListSource("list_description")
  183.         lngOriginalEmailAddressCount = CLng(rsEmailAddressListSource("email_address_count"))
  184.             
  185.         'Clean up
  186.         rsEmailAddressListSource.Close
  187.         
  188.         'Copy the email addresses for the new list
  189.         strSQL = "SELECT email_address, first_name, last_name, custom_data_1, custom_data_2, custom_data_3, " & _
  190.                     "active_yn " & _
  191.                   "FROM sur_email_address " & _
  192.                   "WHERE email_list_id = " & Request.Form("cboEmailList")
  193.         'If the user is filtering the email addresses, add a filter to the query
  194.         If CStr(Request.Form("optFilterType")) = CStr(SUR_EMAIL_LIST_WITH_FILTER) Then
  195.             'Add the filter for the active flag
  196.             If Request.Form("cboActive") <> SUR_COMBO_ALL Then
  197.                 strSQL = strSQL & " AND active_yn = " & SQLEncode(Request.Form("cboActive"))
  198.             End If
  199.  
  200.             'Add the filter for the email address
  201.             If Len(Trim(Request.Form("txtEmailAddress"))) > 0 Then
  202.                 strSQL = strSQL & " AND email_address LIKE " & SQLEncodeContains(Request.Form("txtEmailAddress"))
  203.             End If
  204.  
  205.             'Add the filter for the first custom data field
  206.             If Len(Trim(Request.Form("txtCustomData1"))) > 0 Then
  207.                 strSQL = strSQL & " AND custom_data_1 LIKE " & SQLEncodeContains(Request.Form("txtCustomData1"))
  208.             End If
  209.  
  210.             'Add the filter for the second custom data field
  211.             If Len(Trim(Request.Form("txtCustomData2"))) > 0 Then
  212.                 strSQL = strSQL & " AND custom_data_2 LIKE " & SQLEncodeContains(Request.Form("txtCustomData2"))
  213.             End If
  214.  
  215.             'Add the filter for the third custom data field
  216.             If Len(Trim(Request.Form("txtCustomData3"))) > 0 Then
  217.                 strSQL = strSQL & " AND custom_data_3 LIKE " & SQLEncodeContains(Request.Form("txtCustomData3"))
  218.             End If
  219.         End If
  220.  
  221.         'Open the recordset
  222.         rsEmailAddressListSource.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
  223.         
  224.         'If there are email addresses, copy them for the new list
  225.         If Not rsEmailAddressListSource.EOF Then
  226.             'Get a range of ID's for email addresses
  227.             lngNewEmailAddressID = ID_GetRangeOfIDs("SurveyGenerationEmailAddress", lngOriginalEmailAddressCount)
  228.         
  229.             rsEmailAddressListSource.MoveFirst
  230.             Do While Not rsEmailAddressListSource.EOF
  231.                 'Insert the new email address
  232.                 strSQL = "INSERT INTO sur_email_address(email_address_id, email_list_id, email_address, first_name, " & _
  233.                             "last_name, custom_data_1, custom_data_2, custom_data_3, active_yn, deleted_yn) " & _
  234.                          "VALUES(" & _
  235.                          lngNewEmailAddressID & ", " & _
  236.                          lngNewEmailListID & ", " & _
  237.                          SQLEncode(rsEmailAddressListSource("email_address")) & ", " & _
  238.                          SQLEncode(rsEmailAddressListSource("first_name")) & ", " & _
  239.                          SQLEncode(rsEmailAddressListSource("last_name")) & ", " & _
  240.                          SQLEncode(rsEmailAddressListSource("custom_data_1")) & ", " & _
  241.                          SQLEncode(rsEmailAddressListSource("custom_data_2")) & ", " & _
  242.                          SQLEncode(rsEmailAddressListSource("custom_data_3")) & ", " & _
  243.                          SQLEncode(rsEmailAddressListSource("active_yn")) & ", " &  _
  244.                          SQLEncode(SUR_BOOLEAN_NEGATIVE) & ")"
  245.                 conNewEmailList.Execute ConvertSQL(strSQL), , adCmdText
  246.                 
  247.                 'Track the number of actual email addresses inserted
  248.                 lngEmailAddressCount = lngEmailAddressCount + 1
  249.                 
  250.                 'Incremeent the email address ID counter
  251.                 lngNewEmailAddressID = lngNewEmailAddressID + 1
  252.                 
  253.                 rsEmailAddressListSource.MoveNext
  254.             Loop
  255.  
  256.             'Clean up
  257.             rsEmailAddressListSource.Close
  258.         End If
  259.  
  260.         'Finish cleaning up
  261.         Set rsEmailAddressListSource = Nothing
  262.     End If
  263.  
  264.     'Create the new email list using all of the values set above
  265.     strSQL = "INSERT INTO sur_email_list(email_list_id, user_id, list_name, list_description, created_date, " & _
  266.                 "default_yn, email_address_count) " & _
  267.              "VALUES(" & lngNewEmailListID & ", " & _
  268.                 GetUserID() & ", " & _
  269.                 SQLEncode(strEmailListName) & ", " & _
  270.                 SQLEncode(strEmailListDescription) & ", " & _
  271.                 "GETDATE(), " & _
  272.                 SQLEncode(SUR_BOOLEAN_NEGATIVE) & ", " & _
  273.                 lngEmailAddressCount & ")"
  274.     conNewEmailList.Execute ConvertSQL(strSQL), , adCmdText
  275.  
  276.     'Clean up
  277.     conNewEmailList.Close
  278.     Set conNewEmailList = Nothing
  279.  
  280.     'If the user created a new email list from scratch, redirect to the import page to add email addresses to the list. 
  281.     'Otherwise, redirect to the main list of email lists.
  282.     If CLng(Request.Form("optType")) = SUR_CREATE_EMAIL_LIST_FROM_SCRATCH Then
  283.         Response.Redirect "ImportEmailAddresses.asp?EmailListID=" & lngNewEmailListID & "&EmailListName=" & strEmailListName
  284.     Else
  285.         Response.Redirect "EmailListList.asp?EmailListID=" & lngNewEmailListID
  286.     End If
  287. %>