<%@ LANGUAGE="VBScript" %>
<!--#include file="../../woodstocksiv.com/survey/db.lib.asp" -->
<!--#include file="../../woodstocksiv.com/survey/config.asp" -->
<%

Select Case Request("operation")
  Case "updateoptins"
	' check if customer exists in database
	strquery = "SELECT * FROM customers WHERE email='"& Request("email") &"';"
	Set result = conexion.Execute(strquery)
	arrResult = fetch_table (result)
	if(Ubound(arrResult)=0) then ' Customer exists
		' check how many optins are listed in database
		strquery = "SELECT * FROM optins;"
		Set result = conexion.Execute(strquery)
		arrResult = fetch_table (result)
		' perform actions for each optin based upon retreived form data
		For i = 0 to Ubound(arrResult) ' for each optin
			Set optin = arrResult(i)
			strquery = "SELECT * FROM optinsxcustomers WHERE customerid='"& Request("email") &"' AND optinid=" & optin("id") & ";"
			Set result = conexion.Execute(strquery)
			arrOptResult = fetch_table (result)
			' reads optin value from FORM
			if(Request(optin("optincode"))="Yes" or Request(optin("optincode"))="No") then
				if(Request(optin("optincode"))="Yes") then
					optinvalue = 1
				else
					optinvalue = 0
				end if
				' checks optin setting for this customer
				if(Ubound(arrOptResult)=-1) then ' Optin is NOT set for this customer
					strquery = "INSERT INTO optinsxcustomers (customerid,optinid,optvalue) VALUES ( '" & Request("email") & "', " & optin("id") & ", "& optinvalue &");"
					conexion.Execute(strquery)
				else ' Optin is ALREADY set for this customer
					strquery = "UPDATE optinsxcustomers SET optvalue = "& optinvalue &" WHERE customerid='" & Request("email") & "' AND optinid="& optin("id") &";"
					conexion.Execute(strquery)
				end if
			end if
		Next
		response.redirect(Request("redirect"))
	else ' Customer does not exist
		response.redirect(Request("fail"))
	end if
  Case "addcustomer"
	' check if customer exists in database
	strquery = "SELECT * FROM customers WHERE email='"& Request("email") &"';"
	Set result = conexion.Execute(strquery)
	arrResult = fetch_table (result)
	if(Ubound(arrResult)=-1) then ' Customer does not exist
		' Add customer to database
		strquery = "INSERT INTO customers "
		strquery = strquery & "( firstname, lastname, address, city, state, zip, phone, email, regdate, moddate) "
		strquery = strquery & "VALUES ('"& fNStrSQL(Request("firstName")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("lastName")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("address")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("city")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("state")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("zip")) &"', "
		strquery = strquery & "'"& fNStrSQL(Request("phone")) &"', "
		strquery = strquery & "'"& Request("email") &"', "
		strquery = strquery & "'"& strtoday & "', "
		strquery = strquery & "'"& strtoday & "') "

		conexion.Execute(strquery)
	else ' customer DOES exist
		response.redirect(Request("fail"))
	end if
	' check how many optins are listed in database
	strquery = "SELECT * FROM optins;"
	Set result = conexion.Execute(strquery)
	arrResult = fetch_table (result)
	' perform actions for each optin based upon retreived form data
	For i = 0 to Ubound(arrResult) ' for each optin
		Set optin = arrResult(i)
		strquery = "SELECT * FROM optinsxcustomers WHERE customerid='"& Request("email") &"' AND optinid=" & optin("id") & ";"
		Set result = conexion.Execute(strquery)
		arrOptResult = fetch_table (result)
		' reads optin value from FORM
		if(Request(optin("optincode"))="Yes") then
			optinvalue = 1
		else
			optinvalue = 0
		end if
		' checks optin setting for this customer
		if(Ubound(arrOptResult)=-1) then ' Optin is NOT set for this customer
			strquery = "INSERT INTO optinsxcustomers (customerid,optinid,optvalue) VALUES ( '" & Request("email") & "', " & optin("id") & ", "& optinvalue &");"
			conexion.Execute(strquery)
		else ' Optin is ALREADY set for this customer
			strquery = "UPDATE optinsxcustomers SET optvalue = "& optinvalue &" WHERE customerid='" & Request("email") & "' AND optinid="& optin("id") &";"
			conexion.Execute(strquery)
		end if
	Next
	Session("email") = Request("email")
	Session("valid") = true

	if( Request("signup") = "pie" ) then
		' send coupon by email


' Email to the Customer


Dim dFutureDate
dFutureDate = DateAdd("d", 30, Now)
expirationdate = Month(dFutureDate) & "/" & Day(dFutureDate) & "/" & Year(dFutureDate)

		strMailFromName = "Woodstock's P.I.E."
		strMailFrom = "pie@" & strdomain
		strMailSubject = "Woodstock's Promotions In Email"

		Set Mailer = Server.CreateObject("SMTPsvg.Mailer")

		Mailer.FromName= strMailFromName
		Mailer.FromAddress= strMailFrom
		Mailer.RemoteHost = "mail." & strdomain '"mail.woodstockscalifornia.com"
		Mailer.ReplyTo= cStr(strMailFrom) 

		Mailer.AddRecipient request("firstname") & " " & request("lastname"), request("email")

		Mailer.Subject = strMailSubject
		Mailer.ContentType = "text/html"

		strBody = ""

		strBody = strBody & "<html><body style=""font-family: Arial, Helvetica, sans-serif;"" bgcolor=""#CCB399"" background=""" & strhttpserver & "images/bgBeige.gif"" leftmargin=""0"" topmargin=""0"" marginwidth=""0"" marginheight=""0"">"
		strBody = strBody & "<center><br>Thank you for joining P.I.E. Here's your coupon.</center><br><table width=""360"" border=""0"" align=""center"" cellpadding=""0"" cellspacing=""0"" bgcolor=""#FFFFFF"">"
		strBody = strBody & "<tr><td colspan=""3""><img src=""" & strhttpserver & "images/coupons/pie2.gif"" alt="""" name=""surveyCoupon2_r1_c1"" width=""360"" height=""280"" border=""0"" id=""surveyCoupon2_r1_c1"" /></td><td><img src=""" & strhttpserver & "images/home/survey/spacer.gif"" alt="""" name=""undefined_2"" width=""1"" height=""280"" border=""0"" id=""undefined_2"" /></td></tr><tr><td rowspan=""3""><img src=""" & strhttpserver & "images/home/survey/surveyCoupon2_r2_c1.gif"" alt="""" name=""surveyCoupon2_r2_c1"" width=""10"" height=""57"" border=""0"" id=""surveyCoupon2_r2_c1"" /></td><td align=""center"">"
		strBody = strBody & "Expires " & expirationdate & "</td>"
		strBody = strBody & "<td rowspan=""3""><img src=""" & strhttpserver & "images/home/survey/surveyCoupon2_r2_c3.gif"" alt="""" name=""surveyCoupon2_r2_c3"" width=""10"" height=""57"" border=""0"" id=""surveyCoupon2_r2_c3"" /></td><td><img src=""" & strhttpserver & "images/home/survey/spacer.gif"" alt="""" name=""undefined_2"" width=""1"" height=""23"" border=""0"" id=""undefined_2"" /></td></tr><tr><td align=""center"" valign=""top"">"
		strBody = strBody & "Please mention coupon # when ordering.&nbsp;&nbsp;<strong> #P11</strong>"
		strBody = strBody & "</td><td><img src=""" & strhttpserver & "images/home/survey/spacer.gif"" alt="""" name=""undefined_2"" width=""1"" height=""24"" border=""0"" id=""undefined_2"" /></td></tr><tr><td><img src=""" & strhttpserver & "images/home/survey/surveyCoupon2_r4_c2.gif"" alt="""" name=""surveyCoupon2_r4_c2"" width=""340"" height=""10"" border=""0"" id=""surveyCoupon2_r4_c2"" /></td><td><img src=""" & strhttpserver & "images/home/survey/spacer.gif"" alt="""" name=""undefined_2"" width=""1"" height=""10"" border=""0"" id=""undefined_2"" /></td></tr></table></body></html>"

		Mailer.BodyText = strBody
		Mailer.SendMail

		' end send coupon by email
	end if

	response.redirect(Request("redirect"))

  Case "checkemail"
	' checks if user is in database and redirects accordingly
	strquery = "SELECT * FROM customers WHERE email='"& Request("email") &"';"
	Set result = conexion.Execute(strquery)
	arrResult = fetch_table (result)
	if(Ubound(arrResult)=-1) then ' Customer does not exist
		Session("email") = Request("email")
		response.redirect(Request("false"))
	else
		Session("email") = arrResult(0)("email")
		Session("valid") = true
		response.redirect(Request("true"))
	end if

  Case "storesurvey"

		' 2005-02-25
		' Check for surveys posted today
		strquery = "SELECT * FROM surveys WHERE customerid='"&Session("email")&"' AND surveydate=#"&strtoday&"#"
		Set result = conexion.Execute(strquery)
		arrResult = fetch_table (result)
		if Ubound(arrResult) = -1 then
			allowed = true
		else
			response.redirect("oneAday.htm") ' redirect to a "just one survey per day" page
			response.end
			allowed = false
		end if


	' check if orderNumber is already in Database
	'	strquery = "SELECT * FROM surveys WHERE ordernum = '"&Request("orderNumber")&"';"
	'Set result = conexion.Execute(strquery)
	'arrResult = fetch_table (result)

	'if(Ubound(arrResult)=-1) then ' doesn't exist. good!
		' Add a New Survey
		strquery = "INSERT INTO surveys (ordernum,orderdate,surveydate,customerid,serviceid,response)"
		strquery = strquery & " VALUES ( " 
		strquery = strquery & "'" & Request("orderNumber") & "', "
		strquery = strquery & "'" & change_date_format (Request("orderDate")) & "', "
		strquery = strquery & "'" & strtoday & "', "
		strquery = strquery & "'" & Request("email") & "', "
		strquery = strquery & Request("serviceid") & ", "

		if Request("response") = "Yes" then
			strresponse = "1"
		else
			strresponse = "0"
		end if

		strquery = strquery & strresponse 
		strquery = strquery & ");"
 
		conexion.Execute(strquery)

		Set rsID = conexion.Execute("SELECT @@IDENTITY AS NewID")
		newsurveyid = rsID.Fields("NewID").value

'		response.write(newsurveyid)

		arrQuestions = get_questions ( Request("serviceid") )


' ----------------------------------------------------------------

		For i = 0 to Ubound(arrQuestions)
			Set oQuestion = arrQuestions(i)

			Select case oQuestion("answertype")
				Case "1" ' Yes/No Questions
					if Request( oQuestion("questioncode") ) = "Yes" then
						answervalue = "1"
					else
						answervalue = "0"
					end if
					usercomments = fNStrSQL(Request( oQuestion("questioncode") & "Comm" ))
				Case "2" ' Rate Questions
					answerindex = Request( oQuestion("questioncode") )
					if(answerindex="") then
						answerindex = 0
					end if
					answervalue = answerindex  
					usercomments = fNStrSQL(Request( oQuestion("questioncode") & "Comm" ))			
				Case "3" ' Multiple Selection Questions
					Dim arrCodes
					arrCodes = Split(oQuestion("questioncode"), ",")
					answerdecimal = 0
					For u = 0 to Ubound(arrCodes)
						if( Request( arrCodes(u) ) <> "" ) then
							newvalue = 2 ^ (3 - u)
						else
							newvalue = 0
						end if
						answerdecimal = answerdecimal + newvalue
					Next
					answervalue = answerdecimal
					usercomments = ""			
				Case "4" ' Text Questions
					answervalue = "0"
					usercomments =  fNStrSQL(Request( oQuestion("questioncode") ) )
				Case "5" ' Time Rate Questions
					answerindex = Request( oQuestion("questioncode") )
					if(answerindex="") then
						answerindex = 0
					end if
					answervalue = answerindex  
					usercomments = fNStrSQL(Request( oQuestion("questioncode") & "Comm" ))	
			end Select
			
			strquery = "INSERT INTO answers (questionid, surveyid, answervalue, usercomments)"
			strquery = strquery & " VALUES ( " 

			strquery = strquery & oQuestion("questionnumber") & ", "

			strquery = strquery & Cstr(newsurveyid) & ", "
		
			strquery = strquery & answervalue & ", "
			strquery = strquery & "'" & usercomments & "' "
			strquery = strquery & " );"

			conexion.Execute(strquery)

	Next

' ------------------------------------------------------------------
' Sending email...

		Set arrCustomer = get_customer_details ( Request("email") )

		strMailFromName = "Woodstock's Pizza Survey"
		strMailFrom = "Admin@" & strdomain
		strMailSubject = "New Woodstock's " & get_service_name( Request("serviceid") ) & " Customer Survey"

		Set Mailer = Server.CreateObject("SMTPsvg.Mailer")

		Mailer.FromName= strMailFromName
		Mailer.FromAddress= strMailFrom
		Mailer.RemoteHost = "mail." & strdomain '"mail.woodstockscalifornia.com"
		Mailer.ReplyTo= cStr( arrCustomer("email") )

		'Mailer.AddRecipient arrCustomer("firstname") & " " & arrCustomer("lastname"), arrCustomer("email")
		if(strtestmode="") then 
			Mailer.AddRecipient "Dan Soucek", "dsoucek@woodstocksiv.com"
			Mailer.AddCC "Tom Soucek", "tsoucek@woodstockscalifornia.com"
			Mailer.AddCC "Laura Ambrose", "LAmbrose@woodstockscalifornia.com"
			Mailer.AddCC "Jeff Lafranchi", "Jlafranchi@woodstockscalifornia.com"
			Mailer.AddBCC "Kerri", "kerri@lefthem.com"
			Mailer.AddBCC "Jason", "getinfo@lefthem.com"
		else
			Mailer.AddRecipient "JulianG", "juliang@cantimplora.com.ar"
			Mailer.AddBCC "Tester", "kerri@lefthem.com"
		end if

		Mailer.Subject = strMailSubject
		Mailer.ContentType = "text/html"

		strBody = ""
		strBody = strBody & "<html><body><h3><font face='Arial, Helvetica, sans-serif'>"
		strBody = strBody & "New " & get_service_name( Request("serviceid") ) & " Customer Survey</font></h3>" 
		strBody = strBody & "<p><font face='Arial, Helvetica, sans-serif'>First: <strong>"
		strBody = strBody & cStr(arrCustomer("firstname")) & "</strong><br>"
		strBody = strBody & "Last: <strong>" & cStr(arrCustomer("lastname")) & "</strong><br>"
		strBody = strBody & "Address: <strong>" & cStr(arrCustomer("address")) & "</strong><br>"
		strBody = strBody & "City: <strong>" & cStr(arrCustomer("city")) & "</strong><br>"
		strBody = strBody & "State: <strong>" & cStr(arrCustomer("state")) & "</strong><br>"
		strBody = strBody & "Zip: <strong>" & cStr(arrCustomer("zip")) & "</strong><br>"
		strBody = strBody & "Phone: <strong>" & cStr(arrCustomer("phone")) & "</strong><br>"
		strBody = strBody & "Email: <strong>" & cStr(arrCustomer("email")) & "</strong></p>"

		strBody = strBody & "<hr>"
		strBody = strBody & "<table>"

		'-----------------------------------------------------------

		strquery2 = "SELECT surveys.surveydate, surveys.customerid, surveys.orderdate, surveys.ordernum, answers.surveyid, services.name, questions.questionnumber, questions.questiontext, questions.answertype, answers.answervalue, answers.usercomments, surveys.response AS rsp FROM ((questions INNER JOIN answers ON questions.questionnumber = answers.questionid) INNER JOIN surveys ON (questions.serviceid = surveys.serviceid) AND (answers.surveyid = surveys.id)) INNER JOIN services ON surveys.serviceid = services.id WHERE (((answers.surveyid)=" & newsurveyid &"));"

		Set result2 = conexion.Execute(strquery2)
		arrResult2 = fetch_table (result2)





		For i = 0 to Ubound(arrResult2)

			Set oAnswer = arrResult2(i)

			Select case oAnswer("answertype")
				Case "1" ' Yes/No Questions
					answervalue = fYes(oAnswer("answervalue"))
					usercomments = oAnswer("usercomments")
				Case "2" ' Rate Questions
					Dim arrRates
					arrRates = Array("-","Poor","Fair","Good","Very Good","Excellent")
					answerindex = oAnswer("answervalue")
					answervalue =  arrRates( answerindex ) 
					usercomments = oAnswer("usercomments")		
				Case "3" ' Multiple Selection Questions
					strbinary = Cstr(DecToBin(oAnswer("answervalue")))
					arrNames = Array("Salads","Wildebread","Wings","Cinnabread")
					
					answervalue = ""			
					for u = 0 to 3
						if ( mid(strbinary,u+1,1) = "1" ) then
							answervalue = answervalue & arrNames(u) & " "
						end if
					next
					usercomments = oAnswer("usercomments")
				Case "4" ' Text Questions
					answervalue = ""
					usercomments =  oAnswer("usercomments")
				Case "5" ' Time Rate Questions
					arrRates = Array("-","10-20 minutes","21-30 minutes","31-40 minutes","41-50 minutes","Longer than 50")
					answerindex = oAnswer("answervalue")
					answervalue =  arrRates( answerindex ) 
					usercomments = oAnswer("usercomments")		
			end Select

			strBody = strBody & "<tr><td valign='top'  width='50%'>" & oAnswer("questiontext") & "</td>" & "<td valign='top' >" & answervalue & " </td>" & "<td valign='top' width='35%'>" & usercomments & "&nbsp;</td></tr>"

		Next

		'--------------------------------------------
		strBody = strBody & "</table>"
		strBody = strBody & "<hr>"


		' 2005-02-25
		if Request("response") = "Yes" then
			strBody = strBody & "<p>Response to this survey: Yes</p>"
		else
			strBody = strBody & "<p>Response to this survey: No</p>"
		end if

		strBody = strBody & "<hr>"
		strBody = strBody & "<p>For detailed information, please visit: <a href='"& strhttpserver &"survey"& strtestmode &"/admin/'>"& strhttpserver &"survey/admin/</a></p>"
		strBody = strBody & "<p>Or just <a href='"& strhttpserver &"survey"& strtestmode &"/admin/viewsurvey.asp?id="& Cstr(newsurveyid) &"'>click here</a> to see this survey details.</p>"		
		
		strBody = strBody & "</body></html>"

		Mailer.BodyText = strBody
		Mailer.SendMail

		response.redirect("./thanks.asp")

	'else ' ordernumber already in database (never happens, it is stopped at previewsurvey.asp)
	'	response.redirect("./survey.asp")
	'end if

  Case Else

End Select


'--------------------------------------------------------------------------------------
'		' UPDATE customer details
'		strquery = "UPDATE customers SET"
'		strquery = strquery & " firstname = '"& fNStrSQL(Request("firstName")) &"', "
'		strquery = strquery & " lastname = '"& fNStrSQL(Request("lastName")) &"', "
'		strquery = strquery & " address = '"& fNStrSQL(Request("address")) &"', "
'		strquery = strquery & " city = '"& fNStrSQL(Request("city")) &"', "
'		strquery = strquery & " state = '"& fNStrSQL(Request("state")) &"', "
'		strquery = strquery & " zip = '"& fNStrSQL(Request("zip")) &"', "
'		strquery = strquery & " phone = '"& fNStrSQL(Request("phone")) &"', "
'		strquery = strquery & " moddate = '"& strtoday & "', "
'		strquery = strquery & " WHERE email = '"& Request("email") &"';"
'		conexion.Execute(strquery)
Function fYes (astr)
	if(Cint(astr)>0) then
		fYes = "Yes"
	else
		fYes = "-"
	end if
end function


Function fYes2 (aboolean)
	if(aboolean) then
		fYes2 = "Yes"
	else
		fYes2 = "No"
	end if
end function


Function DecToBin(intDec)
  dim strResult
  dim intValue
  dim intExp

  strResult = ""

  intValue = intDEC
  intExp = 15
  while intExp >= 1
    if intValue >= intExp then
      intValue = intValue - intExp
      strResult = strResult & "1"
    else
      strResult = strResult & "0"
    end if
    intExp = intExp / 2
  wend

  DecToBin = strResult
End Function



%>