myCgi.cgi

Last Modified: Fri Jul 01 23:18:20 CEST 2005
Size: 7490 bytes

#!/home/a/amuneric/public_html/cgi-bin/tclkit



lappend auto_path /home/a/amuneric/public_html/lib
package require Mylib
load tclsqlite-3.0.8.so sqlite3

proc getSortedArtistList {{aIdList ""}} {
	# get the artists' names and id

	set aList [list]
	if {$aIdList == ""} {
		set aIdList [db eval "select artists.artistId from artists"]
	}
	foreach id $aIdList {
		set aIdArr($id) $id
	}
	set aIdList [array names aIdArr]
	foreach id $aIdList {
		set name [join [db eval "select artists.lastname,artists.firstName from artists where artists.artistId = \"$id\""] ", "]
		lappend aList [list $name $id]
	}

	# sort by last name

	return [lsort -index 0 $aList]
}

proc findRuns {nl} {
	set seqStart [lindex $nl 0]
	set prev [lindex $nl 0]
	set str [lindex $nl 0]

	foreach number [lrange $nl 1 end] {

		# we are in a range

		if {$number == ($prev + 1)} {
			set prev $number

			# does the string reflect that we are in a range?

			if {[string index $str end] != "-"} {
				append str " -"
			}
		} else {
			# we are begninning a new range

			# do we need to finish previous range

			if {[string index $str end] == "-"} {
				append str " $prev, $number"
			} else {
				append str ", $number"
			}
			set seqStart $number
			set prev $number
		}
	}

	# do we need to finish off a range

	if {[string index $str end] == "-"} {
		append str " $number"
	}

	return $str
}

proc createReport {} {
	array set qarr [::ncgi::nvlist]

	if {[lindex [array get qarr artistId] 1] != "" && [lindex [array get qarr bootlegId] 1] != ""} {

		puts stdout <hr>[::html::h2 [string trim [db eval "select bootlegs.name from bootlegs where bootlegs.bootlegId=\"[lindex [array get qarr bootlegId] 1]\""] "{}"]]<br>

		# create the artist/track table

		set tableList [list]
		foreach artist [getSortedArtistList [db eval "select trackArtists.artistId from trackArtists where trackArtists.bootlegId = \"[lindex [array get qarr bootlegId] 1]\""]] {
			lappend tableList [lindex $artist 0]
			set str ""
			foreach mId [db eval "select distinct trackArtists.mediaId from trackArtists where trackArtists.bootlegId = \"[lindex [array get qarr bootlegId] 1]\" and trackArtists.artistId = \"[lindex $artist 1]\""] {
				append str "media #[set mId] - tracks "

				append str [findRuns [db eval "select trackArtists.trackNum from trackArtists where trackArtists.bootlegId = \"[lindex [array get qarr bootlegId] 1]\" and trackArtists.artistId = \"[lindex $artist 1]\" and trackArtists.mediaId = \"$mId\""]]
				append str <br>
			}
			lappend tableList $str
		}
		puts stdout "<table border=1>[::html::hdrRow Artist Tracks]"
		foreach {artist tracks} $tableList {
			puts stdout [::html::row <i>$artist</i> <i>$tracks</i>]
		}
		puts stdout "</table><br>"

		# create the venue/date/track table

		set tableList [list]
		foreach {date venueId} [db eval "select distinct tracks.recordingDate,tracks.venueId from tracks where tracks.bootlegId = \"[lindex [array get qarr bootlegId] 1]\" order by tracks.recordingDate"] {
			set str "[set date]<br>"
			foreach {venue location} [db eval "select venues.venue,venues.location from venues where venueId = \"$venueId\""] {}
			append str "[set venue]<br>[set location]"
			lappend tableList $str
			set str ""
			foreach mId [db eval "select distinct tracks.mediaId from tracks where tracks.recordingDate = \"$date\" and tracks.venueId = \"$venueId\" and tracks.bootlegId = \"[lindex [array get qarr bootlegId] 1]\""] {
				append str "media #[set mId] - "

				append str "tracks "
				append str [findRuns [db eval "select tracks.trackNum from tracks where tracks.recordingDate = \"$date\" and tracks.venueId = \"$venueId\" and tracks.mediaId = \"$mId\" and tracks.bootlegId = \"[lindex [array get qarr bootlegId] 1]\" order by tracks.trackNum"]]
				append str <br>
			}
			lappend tableList $str
		}
		puts stdout "<table style=\"width: 100%\;\" border=\"1\">[::html::hdrRow Date/Venue Tracks]"
		foreach {dateVenue tracks} $tableList {
			puts stdout [::html::row <i>$dateVenue</i> <i>$tracks</i>]
		}
		puts stdout "</table><br>"

		# print the setlist

		set mNumOld 1
		foreach {bid mNum tNum sId rDate vId} [db eval "select * from tracks where tracks.bootlegId=\"[lindex [array get qarr bootlegId] 1]\""] {
			if {$mNum != $mNumOld} {
				puts stdout <br>
				set mNumOld $mNum
			}
			puts stdout [format %2i $mNum].[format %2i $tNum]\ -\ [string trim [db eval "select songs.title from songs where songs.songId=\"$sId\""] "{}"]<br>
		}
	}
}

proc createRecordingDateSelection {bootlegIds} {
	array set qarr [::ncgi::nvlist]

	if {[lindex [array get qarr artistId] 1] != ""} {
		puts stdout "or a show date<br>"
		# also make a list of dates

		set recordingDate [list]
		foreach bId $bootlegIds {
			if 0 {
			array unset tempArr
			foreach date [db eval "select tracks.recordingDate from tracks where tracks.bootlegId=\"$bId\""] {
				set tempArr($date) $bId
			}
			foreach name [array names tempArr] {
				lappend recordingDate [list [string trim $name "{}"]  $bId]
			}
			}
			foreach {date id} [db eval "select distinct tracks.recordingDate,tracks.bootlegId from tracks where tracks.bootlegId=\"$bId\""] {
				lappend recordingDate [list $date $id]
			}
		}
		set recordingDate [lsort -index 0 $recordingDate]

		puts stdout {<form action="myCgi.cgi" method="POST">}
		puts stdout [::html::select bootlegId recordingDate [eval concat $recordingDate] [lindex [array get qarr bootlegId] 1]]
		puts stdout [::html::submit recordingDate]
		puts stdout [::html::getFormInfo artistId]
		puts stdout {</form>}

	}
}

proc createBootlegSelection {} {
	array set qarr [::ncgi::nvlist]

	set bIds [list]
	if {[lindex [array get qarr artistId] 1] != ""} {

		puts stdout "Select a bootleg<br>"
		# get all the names of those bootlegs

		set bootlegNames [list]
		set bIds [db eval "select distinct trackArtists.bootlegId from trackArtists where trackArtists.artistId=\"[lindex [array get qarr artistId] 1]\""]
		foreach bId $bIds {
			lappend bootlegNames [list [string trim [db eval "select bootlegs.name from bootlegs where bootlegs.bootlegId=\"$bId\""] "{}"]  $bId]
		}
		set bootlegNames [lsort -index 0 $bootlegNames]

		puts stdout {<form action="myCgi.cgi" method="POST">}
		puts stdout [::html::select bootlegId bootlegName [eval concat $bootlegNames] [lindex [array get qarr bootlegId] 1]]
		puts stdout [::html::submit bootleg]
		puts stdout [::html::getFormInfo artistId]
		puts stdout {</form>}
	}
	return $bIds
}


proc createArtistSelection {} {
	array set qarr [::ncgi::nvlist]

	set aList [getSortedArtistList]

	# flatten the list and put into a selection

	puts stdout {<form action="myCgi.cgi" method="POST">}
	puts stdout [::html::select artistId artistLastname [eval concat $aList] [lindex [array get qarr artistId] 1]]
	puts stdout [::html::submit artist]
	puts stdout {</form>}
}

proc main {} {

	::ncgi::header
	::html::init
	#puts stdout {<a href="myCgi.cgi?showSource=1">Click to show the code that generates this page</a><br>}

	myHeader
	array set qarr [::ncgi::nvlist]
	if {[array get qarr showSource] != ""} {
		exec /home/a/amuneric/public_html/cgi-bin/tclkit /home/a/amuneric/public_html/cgi-bin/tcl2html.tcl -stdout /home/a/amuneric/public_html/cgi-bin/myCgi.cgi
	} else {
		sqlite3 db bldb.sqlite
		puts stdout "Select an artist or band<br>"
		createArtistSelection
		set bootlegIds [createBootlegSelection]
		createRecordingDateSelection $bootlegIds
		createReport
		db close
	}
	myFooter
}

main
logAccess $env(REMOTE_ADDR) $env(REMOTE_HOST)