Module:Tour stats

Documentation for this module may be created at Module:Tour stats/doc

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

local function get_keys(t)
  local keys={}
  for key,_ in pairs(t) do
    table.insert(keys, key)
  end
  return keys
end

local function fetchShow(song, firstShow, fullShow, artist)
	local order = 'ASC'
	local fullSongSql = ''
	local artistSql = ''
	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 artist ~= '' then
		artistSql = ' AND shows.artist = "' .. artist .. '"'
	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.location=location, live_songs.tease=tease, live_songs.abandoned=abandoned, live_songs.soundcheck=soundcheck',
		{
			where = 'name = "' .. song .. '"' .. fullSongSql .. artistSql,
			join = 'live_songs._pageName = shows._pageName',
			groupBy = 'page_name, live_songs._ID',
			orderBy = 'date ' .. order,
			limit = 1
		}
	)
end

local function fetchLongestShow(song, artist)
	local artistSql = ''
	if artist ~= '' then
		artistSql = ' AND artist = "' .. artist .. '"'
	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.location=location, live_songs.tease=tease, live_songs.abandoned=abandoned, live_songs.soundcheck=soundcheck, live_songs.length=length',
		{
			where = 'name = "' .. song .. '"' .. artistSql,
			join = 'live_songs._pageName = shows._pageName',
			orderBy = 'seconds DESC',
			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['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['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
end

function p._show_count(song, artist, full)
	local root = mw.html.create()

	local after_sql = ''
	if artist ~= '' and artist ~= nil then
		after_sql = ' AND artist = "' .. artist .. '"'
	end

	if full ~= '' and full ~= nil then
		after_sql = ' AND tease = 0 AND abandoned = 0 AND soundcheck = 0 AND prerecorded = 0'
	end

	local result = cargo.query(
		'live_songs, shows',
		"COUNT(live_songs._ID)=total",
		{
			where = 'live_songs.name = "' .. song .. '"' .. after_sql,
			join = 'shows._pageName = live_songs._pageName'
		}
	)[1]

	return result['total']
end

function p.show_count(frame)
	local song = frame.args[1]
	local artist = frame.args[2]
	local full = frame.args[3]
	return p._show_count(song, artist, full)
end

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

	local artist_sql = ''
	if artist ~= '' then
		artist_sql = ' AND artist = "' .. artist .. '"'
	end

	local counts = cargo.query(
		'live_songs, shows',
		"COUNT(live_songs._ID)=total, 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 .. '"' .. artist_sql,
			join = 'shows._pageName = live_songs._pageName'
		}
	)[1]

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

	local statsRoot = root:tag('ul')

	-- Totals
	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']) - prerecorded
	local numArtists = tonumber(counts['artists'])
	local totalsRow = statsRoot:tag('li')
	totalsRow:tag('b'):wikitext('Total plays: ')

	-- FIX ME: make these accept artist
	local totalsStr = "'''[https://spcodex.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.acoustic%2C+live_songs.piano%2C+live_songs.piano%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%2C+live_songs.acoustic" ..
		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&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 .. " plays]''' "

	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
			-- FIXME: make these accept artist
			local prerecordedStr = "[https://spcodex.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.acoustic%2C+live_songs.piano%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&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

		-- FIX ME: make these accept artist
		local fullPlaysLink = "[https://spcodex.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.acoustic%2C+live_songs.piano%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&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) .. ' full]'
		totalsStr = totalsStr .. '(' .. fullPlaysLink .. ', ' .. table.concat(subcounts, ', ') .. ')'
	end
	if numArtists > 1 then
		totalsStr = totalsStr .. ', ' .. numArtists .. ' artists '
	end
	totalsRow:tag('span')
		:addClass('plainlinks')
		:wikitext(totalsStr)

	-- First show
	local firstShow = fetchShow(song, true, false, artist)[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, artist)
			if next(firstFullShow) ~= nil then
				createRowForShow(statsRoot, firstFullShow[1], 'First full performance')
			end
		end

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

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

	return tostring(root)
end

function p.main(frame)
	local song = frame.args[1]
	local longest = frame.args[2]
	local artist = frame.args[3]
	return p._main(song, longest, artist)
end

local function normalize_song_option(value)
	if value:sub(1, 7) == 'Include' then
		return 1
	end
	return 0
end

function p._query(options)
	local tables = { 'shows', 'live_songs', 'live_show_photos', 'live_show_videos' }
	local fields = {
		'shows._pageName=page_name',
		'shows.artist=artist',
		'shows.featuring_artist=featuring_artist',
		'shows.tour=tour',
		"(CASE WHEN shows.date__precision = 1 THEN shows.date WHEN shows.date__precision = 2 THEN DATE_FORMAT(shows.date, '%Y-%m-XX') ELSE DATE_FORMAT(shows.date, '%Y-XX-XX') END)=date",
		'shows.venue=venue',
		'shows.venue_wikitext=venue_wikitext',
		"shows.venue_type=venue_type",
		'shows.location=location',
		'shows.festival=festival',
		'shows.notes=notes',
		"IF(live_show_photos._pageName IS NULL AND shows.poster IS NULL, NULL, '1')=photos",
		"IF(live_show_videos._pageName IS NULL, NULL, '1')=video",
	}
	local wheres = {}
	local joins = {
		['live_songs._pageName'] = 'shows._pageName',
		['shows._pageName'] = 'live_show_photos._pageName',
		['live_show_photos._pageName'] = 'live_show_videos._pageName'
	}
	local groups = {'shows._pageName'}

	-- live_songs options
	if options['songs'] then
		local songs = mw.text.split(options['songs'], ';', true)
		multi_song = #songs > 1
		local songWheres = {}
		for r = 1, #songs do
			table.insert(songWheres, "live_songs.name = \"" .. songs[r] .. "\"")
		end
		table.insert(wheres, "(" .. table.concat(songWheres, ' OR ') .. ")")
	end
	if multi_song or (options['songs'] and options['grouping'] == 'Group by song') then
		table.insert(groups, "live_songs._ID")
	end
	if options['songs'] then
		table.insert(fields, "live_songs.name=song")
		table.insert(fields, "live_songs.acoustic=acoustic")
		table.insert(fields, "live_songs.piano=piano")
		table.insert(fields, "live_songs.tease=tease")
		table.insert(fields, "live_songs.abandoned=abandoned")
		table.insert(fields, "live_songs.length=length")
		table.insert(fields, "live_songs.vip=vip")
		table.insert(fields, "live_songs.soundcheck=soundcheck")
	end
	if options['acoustic'] and options['acoustic'] ~= 'All' then
		table.insert(wheres, "live_songs.acoustic = " .. normalize_song_option(options['acoustic']))
	end
	if options['piano'] and options['piano'] ~= 'All' then
		table.insert(wheres, "live_songs.piano = " .. normalize_song_option(options['piano']))
	end
	if options['soundcheck'] and options['soundcheck'] ~= 'All' then
		table.insert(wheres, "live_songs.soundcheck = " .. normalize_song_option(options['soundcheck']))
	end
	if options['prerecorded'] and options['prerecorded'] ~= 'All' then
		table.insert(wheres, "live_songs.prerecorded = " .. normalize_song_option(options['prerecorded']))
	end
	if options['vip'] and options['vip'] ~= 'All' then
		table.insert(wheres, "live_songs.vip = " .. normalize_song_option(options['vip']))
	end
	if options['tease'] and options['tease'] ~= 'All' then
		table.insert(wheres, "live_songs.tease = " .. normalize_song_option(options['tease']))
	end
	if options['abandoned'] and options['abandoned'] ~= 'All' then
		table.insert(wheres, "live_songs.abandoned = " .. normalize_song_option(options['abandoned']))
	end
	if options['cover_artist'] and options['cover_artist'] ~= 'No' then
		table.insert(wheres, "live_songs.cover = \"" .. options['cover_artist'] .. "\"")
	end
	if options['length'] and options['length'] ~= 'No' then
		local secs = require('Module:Live song')._time_to_seconds(options['length'])
		table.insert(wheres, "live_songs.seconds >= " .. secs)
	end

	-- shows options
	if options['artist'] then
		table.insert(wheres, "(shows.artist =\"" .. options['artist'] .. "\" OR shows.featuring_artist = \"" .. options['artist'] .. "\")")
	end
	if options['personnel'] then
		local personnel = mw.text.split(options['personnel'], ',', true)
		local personnelWheres = {}
		for r = 1, #personnel do
			table.insert(personnelWheres, "shows.lineup HOLDS \"" .. personnel[r] .. "\"")
		end
		table.insert(wheres, "(" .. table.concat(personnelWheres, ' OR ') .. ")")
	end
	if options['bands'] then
		local bands = mw.text.split(options['bands'], ',', true)
		local bandsWheres = {}
		for r = 1, #bands do
			table.insert(bandsWheres, "shows.bands HOLDS \"" .. bands[r] .. "\"")
		end
		table.insert(wheres, "(" .. table.concat(bandsWheres, ' OR ') .. ")")
	end
	if options['country'] then
		table.insert(wheres, "country = \"" .. options['country'] .. "\"")
	end
	if options['venue'] then
		table.insert(wheres, "venue = \"" .. options['venue'] .. "\"")
	end
	if options['venue_type'] then
		table.insert(wheres, "venue_type = \"" .. options['venue_type'] .. "\"")
	end
	if options['tour'] then
		table.insert(wheres, "tour = \"" .. options['tour'] .. "\"")
	end
	if options['date_after'] then
		table.insert(wheres, "shows.date >= \"" .. options['date_after'] .. "\"")
	end
	if options['date_before'] then
		table.insert(wheres, "shows.date <= \"" .. options['date_before'] .. "\"")
	end

	local joins2 = {}
	for key, value in pairs(joins) do
		table.insert(joins2, key .. ' = ' .. value)
	end

	local offset = options['offset'] or 0
	
	local results = cargo.query(
		table.concat(tables, ','),
		table.concat(fields, ','),
		{
			where = table.concat(wheres, ' AND '),
			join = table.concat(joins2, ','),
			offset = offset,
			groupBy = table.concat(groups, ','),
			orderBy = 'shows.date ASC',
			limit = 5000
		}
	)

	return results
end

function p.query(frame)
	local getArgs = require('Module:Arguments').getArgs
	local args = getArgs(frame)

	local results = p._query(args)
	local root = mw.html.create('div'):addClass('search-results')

	local title = 'Results'
	root:tag('h3'):wikitext(title)
	-- TODO: maybe give more fitting title, at least mention song(s) names

	local lang = mw.getContentLanguage()
	local totals_div = root:tag('div'):addClass('search-results--totals')
		:wikitext(#results .. ' ' .. lang:plural(#results, 'result', 'results'))

	local get_row = require('Module:Live show row')._main
	local list = root:tag('ul')
	local artists = {}
	local teases = 0
	local abandoned = 0
	local acoustic = 0
	local piano = 0
	local soundcheck = 0
	for r = 1, #results do
		local result = results[r]
		if multi_song then
			result['multi_song'] = '1'
		end
		local success, row_node = pcall(get_row, result)
		if success == false then
			error('Too many results! Try narrowing your search criteria')
		else
			list:node(row_node)
		end

		-- For counts
		artists[result['artist']] = true

		if result['tease'] == '1' then
			teases = teases + 1
		end
		if result['abandoned'] == '1' then
			abandoned = abandoned + 1
		end
		if result['acoustic'] == '1' then
			acoustic = acoustic + 1
		end
		if result['piano'] == '1' then
			piano = piano + 1
		end
		if result['soundcheck'] == '1' then
			soundcheck = soundcheck + 1
		end
	end

	totals_info = {}
	local num_artists = #get_keys(artists)
	if num_artists > 1 then
		table.insert(totals_info, num_artists .. ' artists')
	end
	if acoustic > 0 then
		table.insert(totals_info, acoustic .. ' acoustic')
	end
	if piano > 0 then
		table.insert(totals_info, piano .. ' piano')
	end
	if teases > 0 then
		table.insert(totals_info, teases .. ' ' .. lang:plural(teases, 'tease', 'teases'))
	end
	if abandoned > 0 then
		table.insert(totals_info, abandoned .. ' abandoned')
	end
	if soundcheck > 0 then
		table.insert(totals_info, soundcheck .. ' soundcheck')
	end

	if #totals_info > 0 then
		totals_div:wikitext(' (' .. table.concat(totals_info, ', ') .. ')')
	end

	return tostring(root)
end

return p