[asp] optimize this code

[guest18]

<%
sub sortingarray (showtimefirst)
 
 For row = 0 To UBound( showtimefirst ) - 1
 
        StartingKeyValue = showtimefirst ( row )
        NewKeyValue = showtimefirst ( row )
        swap_pos = row
      
        For j = row + 1 to UBound( showtimefirst )
 
            If showtimefirst ( j ) < NewKeyValue Then
     
                swap_pos = j
                NewKeyValue = showtimefirst ( j )
            End If
   
   
        Next
    
        If swap_pos <> row Then
    
            showtimefirst ( swap_pos ) = StartingKeyValue
            showtimefirst ( row ) = NewKeyValue
        End If 
 Next
 
end sub

sub deleteduplicate(duplicatebeingdelete)
 
 For currentshowtimetime = 0 to UBound( duplicatebeingdelete )
  for nextshowtime = currentshowtimetime + 1 to UBound( duplicatebeingdelete )

 if duplicatebeingdelete (currentshowtimetime) = duplicatebeingdelete (nextshowtime) then

  duplicatebeingdelete (nextshowtime) = ""

 end if
 
  next
 next
 
end sub

function settwentyfourhoursformat (thetime)

  set ff = new regexp

  ff.IgnoreCase = true
  ff.global = false
  ff.Pattern = " PM$"
 
  if (ff.test(thetime)) then
    thetime = replace (thetime , ":00 PM" , "")
 thetime = replace (thetime , ":" , "")
 
  if thetime < 1200 and thetime >= 0100  then
  thetime = thetime + 1200
  end if
 
  else
 
  thetime = replace (thetime , "12" , "24")
  thetime = replace (thetime , ":00 AM" , "")
   thetime = replace (thetime , ":" , "")
 
end if
 
if totalshowtime = "" then
totalshowtime = thetime
else
totalshowtime = totalshowtime & " " & thetime 
end if
 
end function

function findingcinemaid(nameofthecinema)

findcinemaid = "select cinemasid from cinemas" &_
         " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'"
set cinemaidfound = objconndb.execute (findcinemaid)

findingcinemaid = cinemaidfound("cinemasid")

end function

connstr = "Provider=sqloledb; Data Source=mysqlserver; Initial Catalog=wheremytableislocated; User ID=username; Password=password
Conndb = Server.CreateObject("ADODB.Connection")
objConndb.Open connstr

foreachcinema = request("cinemanamecounter")

select case foreachcinema

case 0
tgvcinemanamexcel = "[1u_f$]"
cinemaname = "ONE UTAMA"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 1

tgvcinemanamexcel = "[mines_F$]"

cinemaname = "MINES"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 2

tgvcinemanamexcel = "[s2_f$]"

cinemaname = "SEREMBAN 2"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 3

tgvcinemanamexcel = "[kc_f$]"

cinemaname = "KINTA CITY"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 4

tgvcinemanamexcel = "[br_f$]"

cinemaname = "BUKIT RAJA"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 5

tgvcinemanamexcel = "[tc_f$]"

cinemaname = "TEBRAU CITY"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 6

tgvcinemanamexcel = "[sp_f$]"

cinemaname = "SUNWAY PYRAMID"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 7

tgvcinemanamexcel = "[klcc_f$]"

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

case 8

tgvcinemanamexcel = "[klcc_f$]"

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)

optainedcinemaid = (findingcinemaid(cinemaname))

tomovetonextpageornot = 2

end select

dim tgvshowtime() , tgvmovie() , tgvdate() , movie

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "driver=Microsoft Excel Driver (*.xls);DriverId=790;DBQ=wheretheexcelsheetislocated"
 
display = "select * from "&tgvcinemanamexcel&""
set objrs = objconn.execute (display)

cleartable = "TRUNCATE table tgv_showtimetemp"
set tableclear = objconndb.execute (cleartable)

do while not objrs.eof

if tgvcinemanamexcel = "[br_f$]" or tgvcinemanamexcel= "[tc_f$]" or tgvcinemanamexcel= "[sp_f$]" then

tempthisisdate = objrs.Fields.Item(0)
tempthisismovie = objrs.Fields.Item(3)
tempshowtime1 = objrs.Fields.Item(12)
tempshowtime2 = objrs.Fields.Item(13)
tempshowtime3 = objrs.Fields.Item(14)
tempshowtime4 = objrs.Fields.Item(15)
tempshowtime5 = objrs.Fields.Item(16)
tempshowtime6 = objrs.Fields.Item(17)
tempshowtime7 = objrs.Fields.Item(18)

else
tempthisisdate = objrs.Fields.Item(0)
tempthisismovie = objrs.Fields.Item(3)
tempshowtime1 = objrs.Fields.Item(11)
tempshowtime2 = objrs.Fields.Item(12)
tempshowtime3 = objrs.Fields.Item(13)
tempshowtime4 = objrs.Fields.Item(14)
tempshowtime5 = objrs.Fields.Item(15)
tempshowtime6 = objrs.Fields.Item(16)
tempshowtime7 = objrs.Fields.Item(17)
end if

if tempthisismovie <> "" then
insertemptable = "insert into tgv_showtimetemp(tgv_showtimetemp_moviename , tgv_showtimetemp_datescreening , tgv_showtimetemp_timescreen , tgv_showtimetemp_timescreen1 , tgv_showtimetemp_timescreen2, tgv_showtimetemp_timescreen3, tgv_showtimetemp_timescreen4, tgv_showtimetemp_timescreen5, tgv_showtimetemp_timescreen6)" &_
     " values ('"&tempthisismovie&"' , '"&tempthisisdate&"' , '"&tempshowtime1&"' , '"&tempshowtime2&"' , '"&tempshowtime3&"' , '"&tempshowtime4&"' , '"&tempshowtime5&"' , '"&tempshowtime6&"' , '"&tempshowtime7&"' ) "

set temptable = objConndb.execute (insertemptable) 
   end if
objrs.movenext
loop

showtimeopen = "select tgv_showtimetemp_moviename , tgv_showtimetemp_datescreening from tgv_showtimetemp"
set openshowtime = objconndb.execute (showtimeopen)

do while not openshowtime.eof
checkdupmoviename = openshowtime("tgv_showtimetemp_moviename")
checkdupmoviedate = openshowtime("tgv_showtimetemp_datescreening")

checkduplicate = "select * from tgv_showtimetemp" &_
     " where tgv_showtimetemp_moviename = '"&checkdupmoviename&"' and tgv_showtimetemp_datescreening = '"&checkdupmoviedate&"'"
    
set maybedifhall = objconndb.execute(checkduplicate)

do while not maybedifhall.eof

totalshowtime = ""
     
showtime1 = maybedifhall ("tgv_showtimetemp_timescreen")
showtime2 = maybedifhall ("tgv_showtimetemp_timescreen1")
showtime3 = maybedifhall ("tgv_showtimetemp_timescreen2")
showtime4 = maybedifhall ("tgv_showtimetemp_timescreen3")
showtime5 = maybedifhall ("tgv_showtimetemp_timescreen4")
showtime6 = maybedifhall ("tgv_showtimetemp_timescreen5")
showtime7 = maybedifhall ("tgv_showtimetemp_timescreen6")

if showtime1 <> "" then
settwentyfourhoursformat(showtime1)
end if

if showtime2 <> "" then
settwentyfourhoursformat(showtime2)
end if

if showtime3 <> "" then
settwentyfourhoursformat(showtime3)
end if 

if showtime4 <> "" then
settwentyfourhoursformat(showtime4)
end if

if showtime5 <> "" then
settwentyfourhoursformat(showtime5)
end if

if showtime6 <> "" then
settwentyfourhoursformat(showtime6)
end if

if showtime7 <> "" then
settwentyfourhoursformat(showtime7)
end if

ReDim preserve tgvshowtime(counter)
redim preserve tgvdate(counter)
redim preserve tgvmovie(counter)

if tgvshowtime(counter)  <> "" then
tgvshowtime(counter) = totalshowtime & " " & tgvshowtime(counter)
else
tgvshowtime(counter) = totalshowtime
end if

tgvdate(counter) = maybedifhall("tgv_showtimetemp_datescreening")
tgvmovie(counter) = maybedifhall("tgv_showtimetemp_moviename")

maybedifhall.movenext
loop

counter = counter + 1
openshowtime.movenext
loop

for i = 0 to counter - 1

movie = trim(tgvmovie(i))

showtimebeforesort = tgvshowtime(i)
firstshowtime = split(showtimebeforesort, " ")
sortingarray firstshowtime
showtime = join(firstshowtime," ")
showtime = replace (showtime , "24" , "00")
showtime = split(showtime, " ")
deleteduplicate showtime
showtime = join(showtime," ")

screeningdate = tgvdate(i)

checkmovieinswitcher = "select tgv_switcher_moviedbid from tgv_switcher" &_
        " where tgv_switcher_excelname like '"&movie&"'"
     
set moviecheck = objconndb.execute(checkmovieinswitcher)

if moviecheck.bof and moviecheck.eof then
if movie <> "" then
%>
<a href="addtoswitcher.asp?moviename=<%=movie%>"target="_blank">name not recognise , add <%=movie%> to switcher</a><br><br>
<%
tomovetonextpageornot = 1
end if
else
moviedbid = moviecheck("tgv_switcher_moviedbid")

checkshowtime = "select tgv_showtime_id from tgv_showtime" &_
    " where tgv_showtime_datescreen ='"&screeningdate&"' " &_
    " and tgv_showtime_switcher_moviedb_moviedbid  = '"&moviedbid&"' " &_
    " and tgv_showtime_cinemas_cinemaid =  '"&optainedcinemaid&"' "
    
set showtimecheck = objconndb.execute (checkshowtime)
    
if showtimecheck.eof and showtimecheck.bof then
insertintotgvshowtime = " insert tgv_showtime(tgv_showtime_datescreen , tgv_showtime_timescreen , tgv_showtime_switcher_moviedb_moviedbid , tgv_showtime_cinemas_cinemaid , tgv_showtime_asterisk , tgv_showtime_hall)" &_
      " values ( '"&screeningdate&"' , '"&showtime&"' , '"&moviedbid&"' ,'"&optainedcinemaid&"' , 0 , 0   )"

set tgvshowtimeinsert = objconndb.execute (insertintotgvshowtime)
end if
end if
next
foreachcinema = cint(foreachcinema)+1

select case tomovetonextpageornot

case 0
response.redirect "grabexcel.asp?cinemanamecounter="&foreachcinema&""
case 1
%>
  <input type="button" onclick=window.location.reload(); value = "Click here after you finish adding all to switcher" >
<%
case 2

displayallshowtime =  "select tgv_showtime_datescreen , tgv_showtime_timescreen , movielamb , cinemaplace from tgv_showtime , cinemas , moviedb " &_
       " where tgv_showtime_switcher_moviedb_moviedbid = moviesid " &_
       " and  tgv_showtime_cinemas_cinemaid = cinemasid" &_
       " order by cinemaplace"
     
set showtimedisplay = objconndb.execute (displayallshowtime)

response.write "<table>"  
do while not showtimedisplay.eof 

datescreen = showtimedisplay("tgv_showtime_datescreen")
timescreen = showtimedisplay("tgv_showtime_timescreen")
moviename  = showtimedisplay("movielamb")
cinemaname = showtimedisplay("cinemaplace")
 
with response
.write "<tr><td>" & (datescreen) & "</td>"
.write "<td>" & (timescreen) & "</td>"
.write "<td>" & (moviename) & "</td>"
.write "<td>" & (cinemaname) & "</td></tr>"
end with

showtimedisplay.movenext

loop

response.write "</table>" 

end select

%>

basically this code are suppose to grab cinema showtime from excel sheet put it into temporary db , then combine all the showtime into 1 long string , convert it to 24 hours format (xxxx) , get movie name from movie database , and then store all this data into 1 table

it work quite fine , but IM not statisfy with the code , seem too many repetation , too many crude way of doing thing and there is some copy and paste code that I halfway throught getting know how it work . and one of it problem also is repetation of reading same row . it wont recognise the same row have been readed and thus this create some peformance hog , Im still trying to figure out how to not allow the program read the same row over and over again

my asp is self learn . so I dont really know much , working with it for 2 month abit more only . can anyone teach me how to improve this code ?

[Angstrom]

[quote user="guest18"]sub sortingarray (showtimefirst)

 For row = 0 To UBound( showtimefirst ) - 1
 
        StartingKeyValue = showtimefirst ( row )
        NewKeyValue = showtimefirst ( row )
        swap_pos = row
      
        For j = row + 1 to UBound( showtimefirst )
 
            If showtimefirst ( j ) < NewKeyValue Then
     
                swap_pos = j
                NewKeyValue = showtimefirst ( j )
            End If
   
   
        Next
    
        If swap_pos <> row Then
    
            showtimefirst ( swap_pos ) = StartingKeyValue
            showtimefirst ( row ) = NewKeyValue
        End If 
 Next
 
end sub[/quote]

 Congratulations, you've invented bubble sort.  That's pretty impressive when you have an RDBMS right there at your disposal.

[guest18]

I use bubble sort cause I only rmember how to do that one , other like heap sort and someother thing , IM not too sure , will google for more info later  

rdbms ? http://en.wikipedia.org/wiki/Relational_database_management_system ? hmm interesting ... can teach me more ?

 it is not really my code , I just randomly type bubble sort in google and it come up with that code from 4guyfromrolla . IM still unsure how the code work but since it work , I put it there temporary until I figure out how it work . lol .

 

[Angstrom]

Yes, that's exactly what I meant.  You're using one already: SQL Server is an RDBMS.

The first thing I'd do with that code is break it into two or more pages -- one to handle importing data from Excel, and one to handle displaying it.  The display code is almost embarassingly simple (you've written most of it there in case 2), and by breaking it up you won't have to worry about accidentally breaking it when working on the import logic.

The second thing would be to keep the showtimes separate in the database, combining them into one line only on the display page (not in the database itself).  You will need two tables for this -- one for shows, with no showtime information in it, and one for show-time pairs.  Look up the concept of a join.

The third thing I'd do is let the database do the sorting.  Putting an "ORDER BY" clause at the end of the query lets you sort them by whatever column you like, and it's almost guaranteed to use an extremely efficient sort algorithm.  Plus, then you don't have to maintain your own.

 

[guest18]

IM sorry but I cant break them into 2 page or more , my company ask me to do in 1 page . must be 1 page no matter what unless there is really good reason and most of the time , whatever reason is not really good enought

keeping them all in 1 line also is company standard , really cant help it

 well I can try on the order by method ... I will try it out , thanks alot

 oh yeah here is a sample data

 

16-Dec ERAGON(U) 11:00 AM 1:30 PM 4:00 PM 6:30 PM 9:00 PM 11:20 PM
16-Dec ERAGON(U) 11:50 AM 2:15 PM 4:35 PM 7:00 PM 9:30 PM 11:55 PM
16-Dec ERAGON(U) 11:50 AM 2:15 PM 4:35 PM 7:00 PM 9:30 PM 11:55 PM
16-Dec CICAK MAN(U) 11:20 AM 1:45 PM 4:15 PM 6:40 PM 9:05 PM 11:30 PM
16-Dec MISI 1511(U) 11:00 AM 1:30 PM 4:00 PM 6:30 PM 9:00 PM 11:45 PM
16-Dec FEET(U) HAPPY FEET 5:30 PM
16-Dec BARNYARD(U) 11:30 AM 1:30 PM 3:30 PM 7:50 PM 9:50 PM 11:45 PM
16-Dec FEET(U) HAPPY FEET 11:20 AM
16-Dec DHOOM 2(U) 5:00 PM
16-Dec BAABUL(H) 1:40 PM 8:10 PM 11:30 PM
16-Dec FEET(U) HAPPY FEET 3:10 PM
16-Dec HAUNTED(18PL) HAUNTED APARTMENT 11:00 AM 1:05 PM 5:30 PM 7:35 PM 9:40 PM 11:55 PM
16-Dec CASINO(U) CASINO ROYALE 1:10 PM 9:00 PM 11:55 PM
16-Dec CINTA(U) 10:45 AM 4:10 PM 6:35 PM
16-Dec CINTA(U) 11:10 PM
16-Dec DÉJÀ VU(U) 1:35 PM 6:25 PM
16-Dec ARANG(18PL) 11:30 AM 4:20 PM 9:05 PM
16-Dec THE HOST(U) 12:30 PM 5:45 PM
16-Dec GRIDIRON(U) GRIDIRON GANG 3:05 PM 8:30 PM 11:10 PM
16-Dec COLIC(18PL) 11:40 AM 4:30 PM
16-Dec WISE GUYS(U) WISE GUYS NEVER DIE2:30 PM 7:20 PM 9:20 PM 11:20 PM

 

[Angstrom]

You should display them on one row as you're doing, but you shouldn't store them that way.  That's my point.

And it sounds like your requirements are guaranteed to produce a WTF, from how you've described them.  Good luck!
 

[guest18]

nah I usually do on two page then combine then , hardly anything can go wrong ... hardly but there will be chance . maybe I should do a validation on the else statement ... well as long as the user dont simply key in thing in the url counter , then it should be fine