View Single Post
  #1  
Old Nov 1, 2009, 10:19 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Lightbulb VGMdb Tool : Display your collection as a table (Greasemonkey script)

Hi all,

I talked with Secret Squirrel a while ago about a different way to display collections, say more spreadsheet like.
But, most certainly due to others priorities and all, it seems that no one at VGMdb would have had time to work on this.

So i took a look at the "Greasemonkey" Firefox extension, which offers a scripting system to modify pages on websites, so that you can change the way these are displayed and even add features.
And it turned out that i could actually get what i wanted using this system.

After a week-end of nasty javascript/xpath/jquery coding (and then some times over various week-end to add or fix things in the latest years ), here is the result.

What does it do ?
The collections are now displayed as a table with "catalog number", "album title" and "parent folders path" columns.
The table is filterable (enter text in field at the top of the table, it filters over the three columns) and sortable (use SHIFT key for multi-column sort selection).
You can export the table data to CSV (including album page URL and album type).

To install the script :
- You first need to install the Greasemonkey extension on Firefox or Tampermonkey on Chrome/etc... (Tampermonkey compatibility only tested on Chrome).
- Then add a new script in the Greasemonkey/Tampermonkey menu, copy/paste the content of the code box bellow over all existing text of that script window, and save (ctrl+S).
- Head over to your collection page (or anyone else's), wait for complete page loading and processing, it will now be displayed as a table.

Changes history:

0.9 2020/08/23
- fixing original list action buttons removal

0.8 2018/12/06
- fixing parsing of private albums

0.7 2018/05/20
- more explicit export filename

0.6 2018/05/20
- add date and time to CSV export filename

0.5 2018/05/20
- add CSV export feature (including album page URL and album type)

0.4 2018/04/21
- handling secure VGMdb URL
- fixing jquery-tablesorter-filter version for a working one
- only filtering cat and title columns (not much point in filtering the folder column after all)

0.3 2010/03/12
- process corrected according to the new collection page structure

Code:
// ==UserScript==
// @name           VGMdb Collection
// @version        0.9
// @author         Mathieu Stempell (Dma-Sc) [email protected]
// @namespace      http://dma-sc.atari.org/vgmdb_collection
// @description    Display VGMdb collection as a sortable and filterable table
// @include        /^https?://vgmdb\.net/db/collection\.php\?do=view.*$/
// @grant          none
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/jquery-1-3-2.js
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/tablesorter.js
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/tablesorter_filter.js
// ==/UserScript==

// changelog:
// 0.9 2020/08/23
// - fixing original list action buttons removal
// 0.8 2018/12/06
// - fixing parsing of private albums
// 0.7 2018/05/20
// - more explicit export filename
// 0.6 2018/05/20
// - add date and time to CSV export filename
// 0.5 2018/05/20
// - add CSV export feature (including album page and album type)
// 0.4 2018/04/21
// - handling secure VGMdb URL
// - fixing jquery-tablesorter-filter version for a working one
// - only filtering cat and title columns (not much point in filtering the folder column after all)
// 0.3 2010/03/12
// - process corrected according to the new collection page structure

function addGlobalStyle(css) {
	var head, style;
	head = document.getElementsByTagName('head')[0];
	if (!head) { return; }
	style = document.createElement('style');
	style.type = 'text/css';
	style.innerHTML = css;
	head.appendChild(style);
}

function evaluteXPath(query, baseNode, unique) {
	var returnValue;
	returnValue = document.evaluate(query,
	baseNode,
	null,
	XPathResult.ORDERED_NODE_SNAPSHOT_TYPE,
	null);
	
	if (unique) {
		
		// if multiple results, returning first result with real text value
		if (returnValue.snapshotLength > 1) {
			for (var returnValueIt = 0; returnValueIt < returnValue.snapshotLength; returnValueIt++) {
				var currentReturnValueTest = jQuery.trim(returnValue.snapshotItem(returnValueIt).data);
				if (currentReturnValueTest != null && currentReturnValueTest != ""
						&& currentReturnValueTest != "/" // if title display is set to original or romanized, a slash character is present in the result
						) {
					return returnValue.snapshotItem(returnValueIt);
				}
			}
		}
		return returnValue.snapshotItem(0);
	}
	return returnValue;
}

function evaluteXPathMultipleReturn(query, baseNode) {
	return evaluteXPath(query, baseNode, false);
}

function evaluteXPathSingleReturn(query, baseNode) {
	return evaluteXPath(query, baseNode, true);
}

function appendDataCell(tableRow, data) {
	
	var tableRowCell = document.createElement("td");
	tableRowCell.setAttribute("class", "smallfont");
	tableRowCell.innerHTML=data;
	tableRow.appendChild(tableRowCell);
}

function appendHeaderCell(tableRow, title) {
	
	var tableRowHeader = document.createElement("th");
	tableRowHeader.innerHTML=title;
	tableRow.appendChild(tableRowHeader);
}

function padOnTwoDigits(val) {
	return (val < 10) ? ("0" + val) : val;
}

// Original credit for this export function goes to https://stackoverflow.com/a/16203218
function exportTableToCSV($table, filename) {
	
	var $rows = $table.find('tr:has(td)'),

	tmpColDelim = String.fromCharCode(11), // vertical tab character
	tmpRowDelim = String.fromCharCode(0), // null character
	colDelim = '";"',
	rowDelim = '"\r\n"',
	header = 'cat' + colDelim + 'title' + colDelim + 'url' + colDelim + 'type' + colDelim + 'folder' + rowDelim;

	csv = '"' + header + $rows.map(function(i, row) {
		var $row = $(row),
		$cols = $row.find('td');

		return $cols.map(function(j, col) {
			var $col = $(col),
			text = $col.text();
			
			// Add album page link URL and album type (extracted from title class) as additional column
			if (j == 1) {
				$col.find('a.albumtitle').each(function (k, element) {
					
					text = text + tmpColDelim + $(element).attr('href');
					
					var type = '';
					$.each($(element).attr('class').split(/\s+/), function (l, albumClass) {
						if (/^album-/.test(albumClass)) {
							type = albumClass.split("album-").join("");
						}
					});
					text = text + tmpColDelim + type;
				});
			}

			return text.replace(/"/g, '""'); // escape double quotes

		}).get().join(tmpColDelim);

	}).get().join(tmpRowDelim)
	.split(tmpRowDelim).join(rowDelim)
	.split(tmpColDelim).join(colDelim) + '"';

	if (window.Blob && window.URL) {
	// HTML5 Blob file export where available
	var blob = new Blob([csv], {
		type: 'text/csv;charset=utf-8'
	});
	var csvUrl = URL.createObjectURL(blob);

	$(this)
		.attr({
		'download': filename,
		'href': csvUrl
		});
	} else {
	// Data URI file export as a fallback
	var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

	$(this)
		.attr({
		'download': filename,
		'href': csvData,
		'target': '_blank'
		});
	}
}

	(function() {

		if(unsafeWindow.console){
			var GM_log = unsafeWindow.console.log;
		}

		addGlobalStyle("table.tablesorter thead tr th { background-repeat: no-repeat; background-position: 3px 3px; }");
		addGlobalStyle("table.tablesorter thead tr th.headerSortUp { background-image: url(../forums/images/buttons/sortdesc.gif); }");
		addGlobalStyle("table.tablesorter thead tr th.headerSortDown { background-image: url(../forums/images/buttons/sortasc.gif); }");
		addGlobalStyle("table.tablesorter thead tr th.header { cursor: pointer; min-width: 60px; width:auto !important; _width: 60px; /* IE6 hack */ border: 2px solid #3C405C; }");
		addGlobalStyle("table.tablesorter tbody tr td { border-bottom: 1px solid #3C405C; }");
		addGlobalStyle("table.tablesorter { border-left: 2px solid #3C405C; border-right: 2px solid #3C405C; padding: 0px; }");

		var searchMainContentNode = "/html/body/div/div/div/div/ul[@id='mycollection']/..";
		var searchCollectionNode = "./ul[@id='mycollection']";
		var searchButtonsNode = "./div/a[text() = 'Expand All']/..";
		var searchAlbums = ".//li/a/..|.//li/b/a/../..";
		var searchAlbumPage = "./descendant::a[contains(@class,'albumtitle')]/@href";
		var searchAlbumTitle = "./descendant::a/span[contains(@style,'inline')]//text()";
		var searchAlbumTitleColor = "./descendant::a[contains(@class,'albumtitle')]/@class";
		var searchAlbumRef = "./descendant::span[contains(@class,'catalog')]/text()";
		var searchAlbumFolders = "ancestor::li[@class='submenu']/b/text()|ancestor::li[@class='submenu']/text()";
	
		var currentAlbum;
		var currentAlbumTitle;
		var currentAlbumTitleColor;
		var currentAlbumRef;
		var currentAlbumPage;
		var currentAlbumId;
		var currentAlbumFolders;
		var currentAlbumFoldersIt;
		var currentAlbumFolder;
		var currentAlbumFolderSub;
		var currentAlbumRow;
		var currentAlbumRowCell;
		var gotFolders=false;
	
		var mainContentNode = evaluteXPathSingleReturn(searchMainContentNode, document);
		
		var albumsTable = document.createElement("table");
		albumsTable.setAttribute("id", "collectionTable");
		albumsTable.setAttribute("class", "tablesorter");
		albumsTable.setAttribute("border", 0);
		albumsTable.setAttribute("cellspacing", 2);
		albumsTable.setAttribute("style", "padding-top: 6px");
	
		var albumsTableHeader = document.createElement("thead");
		albumsTable.appendChild(albumsTableHeader);
		
		var albumsTableHeaderRow = document.createElement("tr");
		appendHeaderCell(albumsTableHeaderRow, "cat");
		appendHeaderCell(albumsTableHeaderRow, "title");
		albumsTableHeader.appendChild(albumsTableHeaderRow);
	
		var albumsTableBody = document.createElement("tbody");
		albumsTable.appendChild(albumsTableBody);
	
		var collectionNode = evaluteXPathSingleReturn(searchCollectionNode, mainContentNode);
			
		var allAlbums = evaluteXPathMultipleReturn(searchAlbums, collectionNode);
			
		for (var allAlbumsIt = 0; allAlbumsIt < allAlbums.snapshotLength; allAlbumsIt++) {
	
			currentAlbum = allAlbums.snapshotItem(allAlbumsIt);
			currentAlbumTitle = evaluteXPathSingleReturn(searchAlbumTitle, currentAlbum).data;
			currentAlbumTitleColor = evaluteXPathSingleReturn(searchAlbumTitleColor, currentAlbum).value;
			currentAlbumRef = evaluteXPathSingleReturn(searchAlbumRef, currentAlbum).data;
			currentAlbumPage = evaluteXPathSingleReturn(searchAlbumPage, currentAlbum).value;
			currentAlbumId = currentAlbumPage.substring(currentAlbumPage.lastIndexOf('/')+1);
			
			// handling parent folders search not contained in any dedicated element (like a "div" or a "span" node)
			currentAlbumFolders = evaluteXPathMultipleReturn(searchAlbumFolders, currentAlbum);
			currentAlbumFolder="";
			for (currentAlbumFoldersIt = 0; currentAlbumFoldersIt < currentAlbumFolders.snapshotLength; currentAlbumFoldersIt++) {
				currentAlbumFolderSub = jQuery.trim(currentAlbumFolders.snapshotItem(currentAlbumFoldersIt).data);
				// if folders aren't in a "b" node (private), many empty or space filled texts are returned along the interesting text value, filtering those
			if (currentAlbumFolderSub != null && currentAlbumFolderSub != "") {
				gotFolders=true;
				currentAlbumFolder = currentAlbumFolder+"/"+currentAlbumFolderSub;
			}
		}
		
		currentAlbumRow = document.createElement("tr");
		appendDataCell(currentAlbumRow, "<span class='time'>"+currentAlbumRef+"</span>");
		appendDataCell(currentAlbumRow, "<a href='"+currentAlbumPage+"' class='"+currentAlbumTitleColor+"'>"+currentAlbumTitle+"</a>");
		appendDataCell(currentAlbumRow, "<span class='time' style='color: silver'>"+currentAlbumFolder+"</span>");
		albumsTableBody.appendChild(currentAlbumRow);
	}

	if (gotFolders) {
		appendHeaderCell(albumsTableHeaderRow, "folder");
	}

	mainContentNode.appendChild(albumsTable);
	mainContentNode.removeChild(collectionNode);

	var buttonsNode = evaluteXPathSingleReturn(searchButtonsNode, mainContentNode);
	mainContentNode.removeChild(buttonsNode);

	$(".smallfont.time").eq(0).append(" - Filter: <input name='filter' id='filter-box' value='' maxlength='30' size='30' type='text' class='bginput smallfont'/><input id='filter-clear-button' type='submit' value='Clear' class='button'/> (use SHIFT key for multi-column sorting) - <a href='#' id='export-link'>Export to CSV</a>");
	$("#collectionTable").tablesorter()
	.tablesorterFilter({filterContainer: $("#filter-box"), filterClearContainer: $("#filter-clear-button"), filterColumns: [0, 1], filterCaseSensitive: false});

	// Export element must be a hyperlink
	$("#export-link").click(function(event) {
		var d = new Date();
		var date = d.getFullYear() + '_' + padOnTwoDigits(d.getMonth() + 1) + '_' + padOnTwoDigits(d.getDate());
		var time = padOnTwoDigits(d.getHours()) + '_' + padOnTwoDigits(d.getMinutes()) + '_' + padOnTwoDigits(d.getSeconds());
		exportTableToCSV.apply(this, [$('#collectionTable'), 'vgmdb-collection-' + date + '-' + time + '.csv']);
		// No "event.preventDefault()" or "return false" to behave like a hyperlink
	});
}());

Last edited by dma; Aug 23, 2020 at 07:18 AM.
Reply With Quote