Module:Tour stats

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

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

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

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.location=location',
		'shows.festival=festival',
		"live_songs.acoustic=acoustic",
		"live_songs.piano=piano",
		"live_songs.tease=tease",
		"live_songs.abandoned=abandoned",
		"live_songs.length=length",
		"live_songs.vip=vip",
		"live_songs.soundcheck=soundcheck",
		"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'
	}

	-- live_songs options
	if options['songs'] then
		local songs = mw.text.split(options['songs'], ';', true)
		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 options['acoustic'] and options['acoustic'] ~= 'No' then
		table.insert(wheres, "live_songs.acoustic = 1")
	end
	if options['piano'] and options['piano'] ~= 'No' then
		table.insert(wheres, "live_songs.piano = 1")
	end
	if options['soundcheck'] and options['soundcheck'] ~= 'No' then
		table.insert(wheres, "live_songs.soundcheck = 1")
	end
	if options['prerecorded'] and options['prerecorded'] ~= 'No' then
		table.insert(wheres, "live_songs.prerecorded = 1")
	end
	if options['vip'] and options['vip'] ~= 'No' then
		table.insert(wheres, "live_songs.vip = 1")
	end
	if options['cover_artist'] and options['cover_artist'] ~= 'No' then
		table.insert(wheres, "live_songs.cover = \"" .. options['cover_artist'] .. "\"")
	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 \"" .. personnel[r] .. "\"")
		end
		table.insert(wheres, "(" .. table.concat(bandsWheres, ' OR ') .. ")")
	end
	if options['country'] then
		table.insert(where, "country = \"" .. options['country'] .. "\"")
	end
	if options['venue'] then
		table.insert(where, "venue = \"" .. options['venue'] .. "\"")
	end
	if options['tour'] then
		table.insert(where, "tour = \"" .. options['tour'] .. "\"")
	end
	if options['date_after'] then
		table.insert(where, "date >= \"" .. options['date_after'] .. "\"")
	end
	if options['date_before'] then
		table.insert(where, "date <= \"" .. options['date_before'] .. "\"")
	end

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

	local results = cargo.query(
		table.concat(tables, ','),
		table.concat(fields, ','),
		{
			where = table.concat(wheres, ' AND '),
			join = table.concat(joins2, ',')
		}
	)

	return results
end

function p.query(frame)
	local getArgs = require('Module:Arguments').getArgs
	local args = getArgs(frame)
	local filters = {
		songs = false,
		acoustic = false,
		piano = false,
		soundcheck = false,
		tease = false,
		abandoned = false,
		prerecorded = false,
		vip = false,
		cover_artist = false,
		artist = false,
		personnel = false,
		bands = false,
		country = false,
		coords = false,
		venue = false,
		venue_type = false,
		tour = false,
		date_after = false,
		date_before = false,
	}

	-- for key, value in pairs(args) do
	-- 	if filters[key] == false then
	-- 		filters[key] = value
	-- 	end
	-- end

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

	local title = 'Results'
	root:tag('h3'):wikitext(title)

	local get_row = require('Module:Live show row')._main
	local list = root:tag('ul')
	for r = 1, #results do
		local result = results[r]
		list:tag('li'):wikitext(result['page_title'])
		list:node(get_row(result))
	end

	return tostring(root)
end

return p