Module:Availability

This module implements the {{availability}} template, which is placed on every song page. This generates the "Availability" and "Tour stats" sections, as applicable.

---

local p = {}
local cargo = mw.ext.cargo

local function pairsByKeys (t, f)
	local a = {}
	for n in pairs(t) do table.insert(a, n) end
	table.sort(a, f)
	local i = 0      -- iterator variable
	local iter = function ()   -- iterator function
		i = i + 1
		if a[i] == nil then return nil
		else return a[i], t[a[i]]
		end
	end
	return iter
end

local function dateFormat(str, precision)
	if precision == '2' then
		return string.sub(str, 0, -3) .. 'XX'
	elseif precision == '3' then
		return string.sub(str, 0, -6) .. 'XX-XX'
	else
		return str
	end
end

local function typeFormat(str)
	local ret = ''

	for t in string.gmatch(str, "[^,]+") do
		if t == 'ep' then
			t = 'EP'
		else
			t = t:gsub("^%l", string.upper)
		end

		ret = ret .. t .. ' • '
	end
	
	if string.sub(ret, -8) == ' • ' then
		ret = string.sub(ret, 0, -9)
	end

	return ret
end

function p._main(song)
	local albums = cargo.query(
		'track_listings, albums, releases',
		"track_listings._pageName=title, releases.release_date=date, release_date__precision=precision, albums.type=type, track_listings.listing_id=listing_id, headline",
		{
			where = "releases.release_date IS NOT NULL AND song_page_title = \"" .. song .. '"',
			join = 'track_listings._pageName=albums._pageName,albums._pageName=releases._pageName',
			groupBy = 'track_listings.work',
			orderBy = 'releases.release_date ASC'
		}
	)
	local songs = cargo.query(
		'track_listings, songs, releases',
		"track_listings._pageName=title, releases.release_date=date, release_date__precision=precision, songs.type=type, track_listings.listing_id=listing_id, headline",
		{
			where = "releases.release_date IS NOT NULL AND song_page_title = \"" .. song .. '"',
			join = 'track_listings._pageName=songs._pageName,songs.name=releases.work',
			groupBy = 'track_listings.work',
			orderBy = 'releases.release_date ASC'
		}
	)
	local misc = cargo.query(
		'track_listings',
		"track_listings._pageName=title",
		{
			where = "song_page_title = \"" .. song .. '"',
			groupBy = 'track_listings._pageName'
		}
	)

	if next(albums) == nil and next(songs) == nil and next(misc) then
		return ''
	end

	local releasesByDate = {}
	local releaseTitles = {}
	local has_headline = false

	for r = 1, #misc do
		releaseTitles[misc[r]['title']] = true
	end
	for r = 1, #songs do
		releasesByDate[songs[r]['date'] .. r] = songs[r]
		releaseTitles[songs[r]['title']] = nil
		if songs[r]['headline'] ~= nil then
			has_headline = true
		end
	end
	for r = 1, #albums do
		releasesByDate[albums[r]['date'] .. r] = albums[r]
		releaseTitles[albums[r]['title']] = nil
		if albums[r]['headline'] ~= nil then
			has_headline = true
		end
	end

	local has_result = false
	local root = mw.html.create()
	local tableRoot = root:tag('table')
	tableRoot:addClass('wikitable sortable')
	local headerRow = tableRoot:tag('tr')
	headerRow:tag('th')
		:addClass('headerSort')
		:wikitext('Title')

	if has_headline then
		headerRow:tag('th')
			:wikitext('Notes')
	end
	-- headerRow:tag('th')
	-- 	:addClass('headerSort')
	-- 	:wikitext('Release date')
	headerRow:tag('th')
		:addClass('headerSort')
		:wikitext('Type')

	for _date, release in pairsByKeys(releasesByDate) do
		has_result = true
		local row = tableRoot:tag('tr')
		row:tag('td'):wikitext('[[' .. release['title'] .. '#track-listing-' .. release['listing_id'] .. '|' .. release['title'] .. ']]')

		if has_headline then
			row:tag('td'):wikitext(release['headline'])
		end

		-- row:tag('td'):wikitext(dateFormat(release['date'], release['precision']))

		row:tag('td'):wikitext(typeFormat(release['type'] or ''))
	end

	for title,_ in pairs(releaseTitles) do
		has_result = true
		local row = tableRoot:tag('tr')
		row:tag('td'):wikitext('[[' .. title .. ']]' )
		if has_headline then
			row:tag('td')
		end
		-- FIXME: HACK: since we don't have a 'type' for podcasts,
		--   we just look at the page title. Bad. But it works for now.
		if string.find(title, "podcast") then
			row:tag('td'):wikitext('Podcast')
		end
	end

	if has_result then
		return '== Availability ==\n' .. tostring(root)
	end

	return '[[Category:Songs with no availability]]'
end

function p.main(frame)
	local song = frame.args[1]
	return p._main(song)
end

local function fetchShow(song, firstShow, fullShow, nonVipShow)
	local order = 'ASC'
	local fullSongSql = ''
	if firstShow == false then
		order = 'DESC'
	end
	if fullShow == true then
		fullSongSql = ' AND tease = 0 AND abandoned = 0 AND soundcheck = 0 AND prerecorded = 0'
	end
	if nonVipShow == true then
		fullSongSql = fullSongSql .. ' AND vip = 0'
	end
	return cargo.query(
		'live_songs, shows',
		'shows._pageName=page_name, shows.artist=artist, shows.featuring_artist=featuring_artist, shows.date=date, shows.venue=venue, shows.festival=festival, ' ..
			'shows.location=location, live_songs.tease=tease, live_songs.abandoned=abandoned, live_songs.soundcheck=soundcheck, live_songs.prerecorded=prerecorded, live_songs.vip=vip',
		{
			where = 'name = "' .. song .. '"' .. fullSongSql,
			join = 'live_songs._pageName = shows._pageName',
			groupBy = 'page_name',
			orderBy = 'date ' .. order,
			limit = 1
		}
	)
end

local function fetchLongestShow(song)
	return cargo.query(
		'live_songs, shows',
		'shows._pageName=page_name, shows.artist=artist, shows.featuring_artist=featuring_artist, shows.date=date, shows.venue=venue, shows.festival=festival, ' ..
			'shows.location=location, live_songs.tease=tease, live_songs.abandoned=abandoned, live_songs.soundcheck=soundcheck, live_songs.length=length, live_songs.prerecorded=prerecorded, live_songs.vip=vip',
		{
			where = 'name = "' .. song .. '"',
			join = 'live_songs._pageName = shows._pageName',
			orderBy = 'seconds DESC',
			limit = 1
		}
	)
end

local function fetchShortestShow(song)
	return cargo.query(
		'live_songs, shows',
		'shows._pageName=page_name, shows.artist=artist, shows.featuring_artist=featuring_artist, shows.date=date, shows.venue=venue, shows.festival=festival, ' ..
			'shows.location=location, live_songs.tease=tease, live_songs.abandoned=abandoned, live_songs.soundcheck=soundcheck, live_songs.length=length, live_songs.prerecorded=prerecorded, live_songs.vip=vip',
		{
			where = 'name = "' .. song .. '" AND live_songs.length IS NOT NULL AND live_songs.abandoned=0 AND live_songs.tease=0',
			join = 'live_songs._pageName = shows._pageName',
			orderBy = 'seconds ASC',
			limit = 1
		}
	)
end

local function createRowForShow(ulRoot, data, label)
	local showRow = ulRoot:tag('li')
	showRow:tag('b'):wikitext(label .. ': ')

	local showStr = '[[' .. data['page_name'] .. '|' .. data['artist'] .. ' ' .. data['date'] .. ']] '
	if data['featuring_artist'] ~= nil and data['featuring_artist'] ~= data['artist'] then
		showStr = showStr .. '(featuring [[' .. data['featuring_artist'] .. ']]) '
	end
	if data['festival'] ~= nil then
		showStr = showStr .. 'at ' .. data['festival']
		if data['location'] ~= '' then
			showStr = showStr .. ', '
		else
			showStr = showStr .. ' '
		end
	elseif data['venue'] ~= nil then
		showStr = showStr .. 'at ' .. data['venue']
		if data['location'] ~= '' then
			showStr = showStr .. ', '
		else
			showStr = showStr .. ' '
		end
	end
	if data['location'] ~= nil then
		showStr = showStr .. data['location']
	end

	showRow:tag('span'):wikitext(showStr .. ' ')

	if data['vip'] == '1' then
		showRow:tag('span'):css('color', 'gray'):wikitext('(VIP) ')
	end

	if data['length'] ~= nil then
		showRow:tag('span'):wikitext('[' .. data['length'] .. ']')
	end

	if data['soundcheck'] == '1' then
		showRow:tag('span'):css('color', 'gray'):wikitext('(soundcheck) ')
	end
	if data['tease'] == '1' then
		showRow:tag('span'):css('color', 'gray'):wikitext('(tease) ')
	end
	if data['abandoned'] == '1' then
		showRow:tag('span'):css('color', 'gray'):wikitext('(abandoned) ')
	end
	if data['prerecorded'] == '1' then
		showRow:tag('span'):css('color', 'gray'):wikitext('(prerecorded) ')
	end
end

function p._tour_history(song, longest)
	local root = mw.html.create()

	local counts = cargo.query(
		'live_songs, shows',
		"COUNT(live_songs._ID)=total, SUM(live_songs.acoustic)=acoustic, SUM(live_songs.piano)=piano, SUM(live_songs.tease)=teases, SUM(live_songs.abandoned)=abandons, SUM(live_songs.soundcheck)=soundchecks, SUM(live_songs.prerecorded)=prerecorded, COUNT(DISTINCT shows.artist)=artists",
		{
			where = 'live_songs.name = "' .. song .. '"',
			join = 'shows._pageName = live_songs._pageName'
		}
	)[1]

	if counts['total'] == '0' then
		return ''
	end

	local statsRoot = root:tag('ul')

	-- Totals
	local acoustic = tonumber(counts['acoustic'])
	local piano = tonumber(counts['piano'])
	local teases = tonumber(counts['teases'])
	local abandons = tonumber(counts['abandons'])
	local soundchecks = tonumber(counts['soundchecks'])
	local prerecorded = tonumber(counts['prerecorded'])
	local totalPlays = tonumber(counts['total'])
	local numArtists = tonumber(counts['artists'])
	local totalsRow = statsRoot:tag('li')
	totalsRow:tag('b'):wikitext('Total plays: ')
	local playStr = (totalPlays - prerecorded) .. ' play'
	if totalPlays - prerecorded ~= 1 then
		playStr = playStr .. 's'
	end
	local totalsStr = "'''[https://spcodex.wiki/wiki/Special:CargoQuery?title=Special%3ACargoQuery&tables=live_songs%2C+shows%2C+live_show_photos%2C+live_show_videos&fields=live_songs._pageName%3Dpage_name%2C+shows.date%3Ddate%2C+shows.artist%3Dartist%2C+shows.featuring_artist%3Dfeaturing_artist%2C+shows.venue%3Dvenue%2C+shows.location%3Dlocation%2C+shows.festival%3Dfestival%2C+shows.notes%3Dnotes%2C+live_songs.acoustic%3Dacoustic%2C+live_songs.piano%3Dpiano%2C+live_songs.tease%3Dtease%2C+live_songs.abandoned%3Dabandoned%2C+live_songs.soundcheck%3Dsoundcheck%2C+live_songs.vip%3Dvip%2C+IF%28live_show_photos._pageName+IS+NULL+AND+shows.poster+IS+NULL%2C+%27%27%2C+%271%27%29%3Dphotos%2C+live_show_videos._ID%3Dvideo&where=live_songs.prerecorded+%3D+0+AND+live_songs.name+%3D+%22" ..
		mw.uri.encode(song) .. '%22&join_on=shows._pageName+%3D+live_songs._pageName%2C+shows._pageName%3Dlive_show_photos._pageName%2C+shows._pageName%3Dlive_show_videos._pageName&group_by=shows._pageName,live_songs._ID&having=&order_by%5B0%5D=shows.date+ASC&order_by_options%5B0%5D=ASC&limit=1000&format=template&template=Live+show+row&named+args=yes ' .. playStr .. "]'''"

	if teases + abandons + soundchecks + prerecorded > 0 then
		local subcounts = {}
		if teases > 0 then
			table.insert(subcounts, teases .. ' tease')
		end
		if abandons > 0 then
			table.insert(subcounts, abandons .. ' abandoned')
		end
		if soundchecks > 0 then
			table.insert(subcounts, soundchecks .. ' soundcheck')
		end
		if prerecorded > 0 then
			local prerecordedStr = "[https://spcodex.wiki/wiki/Special:CargoQuery?title=Special%3ACargoQuery&tables=live_songs%2C+shows%2C+live_show_photos%2C+live_show_videos&fields=live_songs._pageName%3Dpage_name%2C+shows.date%3Ddate%2C+shows.artist%3Dartist%2C+shows.featuring_artist%3Dfeaturing_artist%2C+shows.venue%3Dvenue%2C+shows.location%3Dlocation%2C+shows.festival%3Dfestival%2C+shows.notes%3Dnotes%2C+live_songs.tease%3Dtease%2C+live_songs.abandoned%3Dabandoned%2C+live_songs.soundcheck%3Dsoundcheck%2C+live_songs.vip%3Dvip%2C+IF%28live_show_photos._pageName+IS+NULL+AND+shows.poster+IS+NULL%2C+%27%27%2C+%271%27%29%3Dphotos%2C+live_show_videos._ID%3Dvideo&where=live_songs.prerecorded+%3D+1+AND+live_songs.name+%3D+%22" ..
				mw.uri.encode(song) .. '%22&join_on=shows._pageName+%3D+live_songs._pageName%2C+shows._pageName%3Dlive_show_photos._pageName%2C+shows._pageName%3Dlive_show_videos._pageName&group_by=shows._pageName,live_songs._ID&having=&order_by%5B0%5D=shows.date+ASC&order_by_options%5B0%5D=ASC&limit=1000&format=template&template=Live+show+row&named+args=yes ' .. prerecorded .. " prerecorded]"
			table.insert(subcounts, prerecordedStr)
		end

		local fullPlaysLink = "[https://spcodex.wiki/wiki/Special:CargoQuery?title=Special%3ACargoQuery&tables=live_songs%2C+shows%2C+live_show_photos%2C+live_show_videos&fields=live_songs._pageName%3Dpage_name%2C+shows.date%3Ddate%2C+shows.artist%3Dartist%2C+shows.featuring_artist%3Dfeaturing_artist%2C+shows.venue%3Dvenue%2C+shows.location%3Dlocation%2C+shows.festival%3Dfestival%2C+shows.notes%3Dnotes%2C+live_songs.tease%3Dtease%2C+live_songs.abandoned%3Dabandoned%2C+live_songs.soundcheck%3Dsoundcheck%2C+live_songs.vip%3Dvip%2C+live_songs.acoustic%3Dacoustic%2C+live_songs.piano%3Dpiano%2C+IF%28live_show_photos._pageName+IS+NULL+AND+shows.poster+IS+NULL%2C+%27%27%2C+%271%27%29%3Dphotos%2C+live_show_videos._ID%3Dvideo&where=live_songs.tease+%3D+0+AND+live_songs.abandoned+%3D+0+AND+live_songs.prerecorded+%3D+0+AND+live_songs.name+%3D+%22" ..
			mw.uri.encode(song) .. '%22&join_on=shows._pageName+%3D+live_songs._pageName%2C+shows._pageName%3Dlive_show_photos._pageName%2C+shows._pageName%3Dlive_show_videos._pageName&group_by=shows._pageName,live_songs._ID&having=&order_by%5B0%5D=shows.date+ASC&order_by_options%5B0%5D=ASC&limit=1000&format=template&template=Live+show+row&named+args=yes ' .. (totalPlays - teases - abandons - prerecorded) .. ' full]'
		totalsStr = totalsStr .. ' (' .. fullPlaysLink .. ', ' .. table.concat(subcounts, ', ') .. ')'
	end
	if acoustic + piano > 0 and acoustic < totalPlays and piano < totalPlays then
		local subcounts = {}
		if acoustic > 0 then
			totalsStr = totalsStr .. ', [https://spcodex.wiki/wiki/Special:CargoQuery?title=Special%3ACargoQuery&tables=live_songs%2C+shows%2C+live_show_photos%2C+live_show_videos&fields=live_songs._pageName%3Dpage_name%2C+shows.date%3Ddate%2C+shows.artist%3Dartist%2C+shows.featuring_artist%3Dfeaturing_artist%2C+shows.venue%3Dvenue%2C+shows.location%3Dlocation%2C+shows.festival%3Dfestival%2C+shows.notes%3Dnotes%2C+live_songs.acoustic%3Dacoustic%2C+live_songs.piano%3Dpiano%2C+live_songs.tease%3Dtease%2C+live_songs.abandoned%3Dabandoned%2C+live_songs.soundcheck%3Dsoundcheck%2C+live_songs.vip%3Dvip%2C+IF%28live_show_photos._pageName+IS+NULL+AND+shows.poster+IS+NULL%2C+%27%27%2C+%271%27%29%3Dphotos%2C+live_show_videos._ID%3Dvideo&where=live_songs.prerecorded+%3D+0+AND+'
				.. 'live_songs.name+%3D+%22' .. mw.uri.encode(song) .. '%22+AND+live_songs.acoustic+%3D+1&join_on=shows._pageName+%3D+live_songs._pageName%2C+shows._pageName%3Dlive_show_photos._pageName%2C+shows._pageName%3Dlive_show_videos._pageName&group_by=shows._pageName&having=&order_by%5B0%5D=shows.date+ASC&order_by_options%5B0%5D=ASC&limit=1000&offset=&format=template&template=Live+show+row&named+args=yes&delimiter= '
				.. acoustic .. ' acoustic]'
		end
		if piano > 0 then
			totalsStr = totalsStr .. ', [https://spcodex.wiki/wiki/Special:CargoQuery?title=Special%3ACargoQuery&tables=live_songs%2C+shows%2C+live_show_photos%2C+live_show_videos&fields=live_songs._pageName%3Dpage_name%2C+shows.date%3Ddate%2C+shows.artist%3Dartist%2C+shows.featuring_artist%3Dfeaturing_artist%2C+shows.venue%3Dvenue%2C+shows.location%3Dlocation%2C+shows.festival%3Dfestival%2C+shows.notes%3Dnotes%2C+live_songs.acoustic%3Dacoustic%2C+live_songs.piano%3Dpiano%2C+live_songs.tease%3Dtease%2C+live_songs.abandoned%3Dabandoned%2C+live_songs.soundcheck%3Dsoundcheck%2C+live_songs.vip%3Dvip%2C+IF%28live_show_photos._pageName+IS+NULL+AND+shows.poster+IS+NULL%2C+%27%27%2C+%271%27%29%3Dphotos%2C+live_show_videos._ID%3Dvideo&where=live_songs.prerecorded+%3D+0+AND+'
				.. 'live_songs.name+%3D+%22' .. mw.uri.encode(song) .. '%22+AND+live_songs.piano+%3D+1&join_on=shows._pageName+%3D+live_songs._pageName%2C+shows._pageName%3Dlive_show_photos._pageName%2C+shows._pageName%3Dlive_show_videos._pageName&group_by=shows._pageName&having=&order_by%5B0%5D=shows.date+ASC&order_by_options%5B0%5D=ASC&limit=1000&offset=&format=template&template=Live+show+row&named+args=yes&delimiter= '
				.. piano .. ' piano]'
		end
	end
	if numArtists > 1 then
		totalsStr = totalsStr .. ', ' .. numArtists .. ' artists '
	end
	totalsRow:tag('span')
		:addClass('plainlinks')
		:wikitext(totalsStr)

	-- First show
	local firstShow = fetchShow(song, true)[1]

	if totalPlays == 1 then
		createRowForShow(statsRoot, firstShow, 'Only performance')
	else
		createRowForShow(statsRoot, firstShow, 'First performance')

		if (firstShow['tease'] == '1' or firstShow['abandoned'] == '1' or firstShow['soundcheck'] == '1') then
			local firstFullShow = fetchShow(song, true, true)
			if next(firstFullShow) ~= nil then
				createRowForShow(statsRoot, firstFullShow[1], 'First full performance')
			end
		end

		-- Last show
		local lastShow = fetchShow(song, false)[1]
		createRowForShow(statsRoot, lastShow, 'Last performance')
		if lastShow['tease'] == '1' or lastShow['abandoned'] == '1' then
			local lastFullShow = fetchShow(song, false, true)
			if next(lastFullShow) ~= nil then
				createRowForShow(statsRoot, lastFullShow[1], 'Last full performance')
			end
		end
		if lastShow['vip'] == '1' then
			local lastNonVipShow = fetchShow(song, false, false, true)
			if next(lastNonVipShow) ~= nil then
				createRowForShow(statsRoot, lastNonVipShow[1], 'Last non-VIP performance')
			end
		end

		if longest ~= '' then
			local longestShow = fetchLongestShow(song)[1]
			createRowForShow(statsRoot, longestShow, 'Longest performance')
			local shortestShow = fetchShortestShow(song)[1]
			if shortestShow ~= nil then
				createRowForShow(statsRoot, shortestShow, 'Shortest performance')
			end
		end
	end

	return '== Tour stats ==\n' .. tostring(root)
end

function p.tour_history(frame)
	local song = frame.args[1]
	local longest = frame.args[2]
	return p._tour_history(song, longest)
end

function p._all(song, longest)
	local availability = p._main(song)
	local tour_history = p._tour_history(song, longest)

	if availability ~= '' then
		if tour_history ~= '' then
			return availability .. "\n" .. tour_history
		end
		return availability
	end

	return tour_history
end

function p.all(frame)
	local song = frame.args[1]
	local longest = frame.args[2]
	return p._all(song, longest)
end

return p