[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 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 ?



  • [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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.