[asp] optimize this code
-
<%
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 subsub 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 subfunction 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 functionfunction 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 connstrforeachcinema = 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 ifif 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
loopshowtimeopen = "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 ifif showtime2 <> "" then
settwentyfourhoursformat(showtime2)
end ifif showtime3 <> "" then
settwentyfourhoursformat(showtime3)
end ifif showtime4 <> "" then
settwentyfourhoursformat(showtime4)
end ifif showtime5 <> "" then
settwentyfourhoursformat(showtime5)
end ifif showtime6 <> "" then
settwentyfourhoursformat(showtime6)
end ifif showtime7 <> "" then
settwentyfourhoursformat(showtime7)
end ifReDim 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 iftgvdate(counter) = maybedifhall("tgv_showtimetemp_datescreening")
tgvmovie(counter) = maybedifhall("tgv_showtimetemp_moviename")maybedifhall.movenext
loopcounter = counter + 1
openshowtime.movenext
loopfor 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)+1select 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 2displayallshowtime = "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.eofdatescreen = 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 withshowtimedisplay.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 ?
-
[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.
-
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 .
-
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.
-
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
-
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!
-
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