help me tweak some ASP

Discussion in 'OT Technology' started by johnnywallywallace, Sep 29, 2004.

  1. Code:
    strSQL = "SELECT DISTINCT(tM.strMovieName), tM.idMovie FROM tShowtime tS INNER JOIN tMovie tM ON tM.idMovie = tS.idMovie INNER JOIN tTheater tT ON tT.idTheater = tS.idTheater WHERE tS.dtShow BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "' AND tT.donotuseWeekend = 0 ORDER BY tM.strMovieName, tM.idMovie"
    
    	Set rsMovies = oConn.Execute(strSQL)
    	strOut = ""
    	Do While Not rsMovies.EOF
    		strMovieName = Replace(rsMovies("strMovieName"), "--", "{mdash}")
    
    		idMovie = rsMovies.Collect(1)
    
    		strSQL = "SELECT tM.* FROM tMovie tM WHERE tM.idMovie = " & idMovie
    
    		Set rsMovieInfo = oConn.Execute(strSQL)
    
    		Do While Not rsMovieInfo.EOF
    		'tsOut1.WriteLine("<intro>")
    		strRating = rsMovieInfo("strMPAARating")
    		strLength = rsMovieInfo("strRunningTime")
    
    		useDay = WeekDay(dtStartDate)
    		vDay = GetDay(useDay)
    
    		'If countme = 0 Then
    			strOut = strOut & GetMovieName(strMovieName) & vbCrLf 
    			strOut = strOut & "<lead_in>"
    			intRating = rsMovieInfo("intJacksRating")
    			
    			
    			strOut = strOut & GetRatingLength(strRating, strLength)
    			strOut = strOut & "</lead_in>" & vbCrLf
    			
    			strOut = strOut & "<intro>"
    			If IsNull(intRating) Or Len(Trim(intRating)) < 1 Then
    				intRating = "0"
    			End If
    			If CStr(intRating) <> "0" Then
    				If intRating = 85 Then
    					intRating = 9
    				ElseIf intRating = 99 Then
    					intRating = 10
    				End If
    
    				strOut = strOut & "<drop_initial>" & intRating & "</drop_initial>" & vbCrLf
    			Else
    				strOut = strOut & "<drop_initial>Unrated</drop_initial>" & vbCrLf
    			End If
    
    			strCapsule = rsMovieInfo("strCapsule")
    			strCapsule = Replace(strCapsule, "<i>", "<italic>")
    			strCapsule = Replace(strCapsule, "</i>", "</italic>")
    			strCapsule = Replace(strCapsule, "<I>", "<italic>")
    			strCapsule = Replace(strCapsule, "</I>", "</italic>")
    			strOut = strOut & Replace(strCapsule, "--", "{mdash}") & "<ep>" & vbCrLf
    			If Len(Trim(rsMovieInfo("strAdvisory"))) > 0 Then
    				strOut = strOut & " <parent> " & rsMovieInfo("strAdvisory") & "."
    			End If
    			strOut = strOut  & "</intro><ep>" & vbCrLf
    			
    			tsOut1.WriteLine(strOut)
    
    			strOut = ""
    
    			strSQL = "SELECT * FROM tTheater WHERE donotuseWeekend = 0 ORDER BY strTheater"
    
    			Set rsTheaters = oConn.Execute(strSQL)
    
    			Do While Not rsTheaters.EOF
    			
    				idTheater = rsTheaters.Collect(0)
    				strTheater = rsTheaters.Collect(1)
    				maxDays = DateDiff("d", dtStartDate, dtEndDate)
    				didTheater = False
    				For loopDays = 0 To maxDays
    					dtUse = DateAdd("d", loopDays, dtStartDate)
    					'Response.Write("Getting times for " & strTheater & ": " & dtUse & "<br>" & vbCrLf)
    
    					strSQL = "SELECT tS.strTimes FROM tShowtime tS WHERE idMovie = " & idMovie & " AND dtShow = '" & dtUse & "' AND idTheater = " & idTheater
    					
    					'Response.Write(strSQL & "<br>" & vbCrLf)
    					Response.Flush
    
    					Set rsTimes = oConn.Execute(strSQL)
    
    					Do While Not rsTimes.EOF
    						If Not didTheater Then
    							strOut = strOut & "<lead_in>" & strTheater
    							
    							If rsTheaters("isIL") Then
    								strOut = strOut & "<TH>(IL)"
    							ElseIf rsTheaters("isIR") Then
    								strOut = strOut & "<TH>(IR)"
    							ElseIf rsTheaters("isFM") Then
    								strOut = strOut & "<TH>(FM)"
    							End If
    							strOut = strOut & "</lead_in><ep>" & vbCrLf
    
    							didTheater = True
    						End If
    						'Movies 10 (IR): 3:10, 7:10, 10:05<EP>
    	
    						strTimes = Replace(rsTimes("strTimes"), ":00", "")
    						'strTimes = Replace(strTimes, "12 am", "Midnight")
    						strTimes = Replace(strTimes, "am", "a.m.")
    						strTimes = Replace(strTimes, "pm", "")
    						strOut = strOut & GetDay(WeekDay(dtUse)) & ": " & strTimes & "<ep>" & vbCrLf
    						'Response.Write("strOut: " & strOut & "<br>" & vbCrLf)
    
    						'Response.Write("Writing: " & GetDay(WeekDay(dtUse)) & ": " & rsTimes("strTimes") & "<br>" & vbCrLf)
    						Response.Flush
    
    						rsTimes.MoveNext
    					Loop
    				
    				Next
    			
    				rsTheaters.MoveNext
    			Loop
    
    			tsOut1.WriteLine(strOut)
    			strOut = ""
    
    			rsMovieInfo.MoveNext
    		Loop
    
    			rsMovieInfo.Close
    			Set rsMovieInfo = Nothing
    
    			rsMovies.MoveNext
    		Loop
    
    		'strOut = strOut & "<ep>" & vbCrLf
    
    		rsMovies.Close
    
    produces, for instance:
    Code:
    <lead_in>Cinemark Tinseltown<TH>(IR)</lead_in><ep>
    Thur.: 1:50, 4:30, 7:05, 9:50<ep>
    <lead_in>Geneva Movieplex</lead_in><ep>
    Thur.: 6:45, 9<ep>
    Fri.: 9<ep>
    Sat.: 9<ep>
    Sun.: 9<ep>
    <lead_in>Regal Culver Ridge<TH>(IR)</lead_in><ep>
    Thur.: 2:05, 4:40, 7:55, 10:30<ep>
    [color="red"]Fri.: 1:35, 7:25<ep>
    Sat.: 1:35, 7:25<ep>
    Sun.: 1:35, 7:25<ep>[/color]
    <lead_in>Regal Eastview<TH>(IR)</lead_in><ep>
    Thur.: 1:45, 4:25, 7:10, 9:50<ep>
    <lead_in>Regal Henrietta<TH>(FM)</lead_in><ep>
    Thur.: 12:05, 9:10<ep>
    
    Those repeating sequential movie times need to be condensed into a single time, preferably, such as:
    Thur.-Sun.: 1:35, 7:25<ep>

    I'd like to think there's some smart way of using GROUP BY/HAVING to group those times and either directly produce the day of week header in SQL, or at least make it obvious enough in the resultset that I can sort the header out when the output is produced. Any thoughts?

    I've tried doing it purely using collections and/or arrays on the clientside, and it gets too messy -- I've given up two previous efforts.
     

Share This Page