I'm presently moving the Witchford Archers site away from the LAMP stack towards a serverless architecture on AWS and so I needed to move the attendance records from MySQL to DynamoDB. Presently they look like this:
CREATE TABLE IF NOT EXISTS `attendance` ( `date` date NOT NULL, `username` varchar(255) NOT NULL DEFAULT '0', `name` varchar(255) DEFAULT NULL, `dues` tinyint(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `attendance` -- INSERT INTO `attendance` (`date`, `username`, `name`, `dues`) VALUES ('2015-01-07', 'Others', '8', NULL), ('2015-01-14', 'Others', '10', NULL), ('2015-01-21', 'Others', '9', NULL), ('2015-01-28', 'Others', '8', NULL), ('2015-02-04', 'Others', '8', NULL), ('2015-12-09', 'Others', '9', NULL), ('2015-12-16', 'Others', '9', NULL), ('2015-12-23', 'Others', '8', NULL), ('2015-12-30', 'Others', '7', NULL), ('2016-01-13', 'user1@example.com', 'Dominic', 1), ('2016-01-13', 'user2@example.com', 'Derek', 1), ('2016-01-13', 'user3@example.com', 'Gemma', 1), ('2016-01-13', 'user5@example.com', 'Chris', 1), ('2016-01-13', 'user6+user2@example.com', 'Jack', 1), ('2016-01-13', 'user4@example.com', 'James', 1), ('2016-01-13', 'user7@example.com', 'Peter', 1), ('2016-01-13', 'user8@example.com', 'Mariusz', 1), ('2016-01-13', 'user9@example.com', 'Patrick', 1), ('2016-01-13', 'user11@example.com', 'Russell', 1), ('2016-01-13', 'user10@example.com', 'Sarah', 1), ('2016-01-17', 'user1@example.com', 'Dominic', 1), ('2016-01-17', 'user2@example.com', 'Derek', 0), ('2016-01-17', 'user3@example.com', 'Gemma', 0), ('2016-01-17', 'user5@example.com', 'Chris', 0), ('2016-01-17', 'user6+user2@example.com', 'Jack', 0), ('2016-01-17', 'user7@example.com', 'Peter', 0), ('2016-01-19', 'user1@example.com', 'Dominic', 0), ('2016-01-19', 'user2@example.com', 'Derek', 0), ('2016-01-19', 'user3@example.com', 'Gemma', 0), ('2016-01-19', 'user4@example.com', 'James', 0);
Using a Text Editor and a few Macros I converted this into a CSV file:
date,username,name,dues 2015-01-07,Others,8,NULL 2015-01-14,Others,10,NULL 2015-01-21,Others,9,NULL 2015-01-28,Others,8,NULL 2015-02-04,Others,8,NULL 2015-12-09,Others,9,NULL 2015-12-16,Others,9,NULL 2015-12-23,Others,8,NULL 2015-12-30,Others,7,NULL 2016-01-13,user1@example.com,Dominic,1 2016-01-13,user2@example.com,Derek,1 2016-01-13,user3@example.com,Gemma,1 2016-01-13,user5@example.com,Chris,1 2016-01-13,user6+user2@example.com,Jack,1 2016-01-13,user4@example.com,James,1 2016-01-13,user7@example.com,Peter,1 2016-01-13,user8@example.com,Mariusz,1 2016-01-13,user9@example.com,Patrick,1 2016-01-13,user11@example.com,Russell,1 2016-01-13,user10@example.com,Sarah,1 2016-01-17,user1@example.com,Dominic,1 2016-01-17,user2@example.com,Derek,0 2016-01-17,user3@example.com,Gemma,0 2016-01-17,user5@example.com,Chris,0 2016-01-17,user6+user2@example.com,Jack,0 2016-01-17,user7@example.com,Peter,0 2016-01-19,user1@example.com,Dominic,0 2016-01-19,user2@example.com,Derek,0 2016-01-19,user3@example.com,Gemma,0 2016-01-19,user4@example.com,James,0
With this I used Papa Parse and a few other scripts/plugins to create the data I needed for DynamoDB. I guess the main issue I wanted to raise was the use of JSFiddle. This is the final result:
[ { "Date": "2015-01-07", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-01-14", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-01-21", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-01-28", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-02-04", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-12-09", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-12-16", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-12-23", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2015-12-30", "Attendance": [ { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" }, { "Name": "Unknown", "User": "Unknown", "Status": "Paid" } ] }, { "Date": "2016-01-13", "Attendance": [ { "Name": "Dominic", "User": "user1@example.com", "Status": "Paid" }, { "Name": "Derek", "User": "user2@example.com", "Status": "Paid" }, { "Name": "Gemma", "User": "user3@example.com", "Status": "Paid" }, { "Name": "Chris", "User": "user5@example.com", "Status": "Paid" }, { "Name": "Jack", "User": "user6+user2@example.com", "Status": "Paid" }, { "Name": "James", "User": "user4@example.com", "Status": "Paid" }, { "Name": "Peter", "User": "user7@example.com", "Status": "Paid" }, { "Name": "Mariusz", "User": "user8@example.com", "Status": "Paid" }, { "Name": "Patrick", "User": "user9@example.com", "Status": "Paid" }, { "Name": "Russell", "User": "user11@example.com", "Status": "Paid" }, { "Name": "Sarah", "User": "user10@example.com", "Status": "Paid" } ] }, { "Date": "2016-01-17", "Attendance": [ { "Name": "Dominic", "User": "user1@example.com", "Status": "Paid" }, { "Name": "Derek", "User": "user2@example.com", "Status": "Unpaid" }, { "Name": "Gemma", "User": "user3@example.com", "Status": "Unpaid" }, { "Name": "Chris", "User": "user5@example.com", "Status": "Unpaid" }, { "Name": "Jack", "User": "user6+user2@example.com", "Status": "Unpaid" }, { "Name": "Peter", "User": "user7@example.com", "Status": "Unpaid" } ] }, { "Date": "2016-01-19", "Attendance": [ { "Name": "Dominic", "User": "user1@example.com", "Status": "Unpaid" }, { "Name": "Derek", "User": "user2@example.com", "Status": "Unpaid" }, { "Name": "Gemma", "User": "user3@example.com", "Status": "Unpaid" }, { "Name": "James", "User": "user4@example.com", "Status": "Unpaid" } ] } ]
JSFiddle was created by Piotr Zalewa and it is described by him thus:
JsFiddle is a playground for web developers, a tool which may be used in many ways. One can use it as an online editor for snippets build from HTML, CSS and JavaScript.
The code can then be shared with others, embedded on a blog, etc. Using this approach, JavaScript developers can very easily isolate bugs. We aim to support all actively developed frameworks - it helps with testing compatibility.
I love it! I use it ever-so-much for all sorts of things, not least because it's a quick way of getting a proof of concept up and running as quickly as possible. I use all sorts of IDEs and Text Editor in my work but JSFiddle, as well as Chrome's developer console, is where I do most of my work.
Anyway, the rather over-engineered code which produced the JSON above is this:
let syntaxHighlight = (json) => json .replace(/&/g, '&') .replace(/</g, '<') .replace(/>/g, '>') .replace(/("(\\u[a-zA-Z0-9]{4}|\\[^u]|[^\\"])*"(\s*:)?|\b(true|false|null)\b|-?\d+(?:\.\d*)?(?:[eE][+\-]?\d+)?)/g, (match) => { var cls = 'number'; if (/^"/.test(match)) { if (/:$/.test(match)) { cls = 'key'; } else { cls = 'string'; } } else if (/true|false/.test(match)) { cls = 'boolean'; } else if (/null/.test(match)) { cls = 'null'; } return '<span class="' + cls + '">' + match + '</span>'; }); $("form").on("click", ".fileinput-upload", (e) => { e.preventDefault(); if (window.File && window.FileReader && window.FileList && window.Blob) { var files = document.getElementById("input").files; if (files[0].type === "application/vnd.ms-excel") { data = []; Papa.parse(files[0], { "download": true, "header": true, "dynamicTyping": true, "complete": results => { let data = []; let Status = ["Unpaid", "Paid", "Subscriber"]; results.data.forEach((element) => { let index = data.findIndex(e => e.Date === element.date); if (index === -1) { if (element.username === "Others") { let Attendance = []; for (let i = 0; i < element.name; i++) { Attendance.push({ "Name": "Unknown", "User": "Unknown", "Status": Status[1] }); } data.push({ "Date": element.date, "Attendance": Attendance }); } else { data.push({ "Date": element.date, "Attendance": [{ "Name": element.name, "User": element.username, "Status": Status[element.dues] }] }); } } else { if (element.username === "Others") { for (let i = 0; i < element.name; i++) { data[index].Attendance.push({ "Name": "Unknown", "User": "Unknown", "Status": Status[1] }); } } else { data[index].Attendance.push({ "Name": element.name, "User": element.username, "Status": Status[element.dues] }); } } }); $("#output").html(syntaxHighlight(JSON.stringify(data, undefined, 4))).show(); } }); } else { toastr["error"]("Please only upload CSV format files.", "Naughty!"); } } else { toastr["warning"]("The File APIs are not fully supported in this browser.", "Oops!"); } });
I'm using jQuery, BootStrap, Toastr, Papa Parse, Bootstrap File Input and I borrowed user123444555621's answer on Stack Overflow. Many of the resources were grabbed from cdnjs.
But I guess the main thing is that I managed to get all this done really quickly and relatively painlessly thanks to the wealth of resources out there, often available for free! I can spin up a simple application to process data just in my browser rather than setting up a server and pulling in external resources, pulling in Frameworks and libraries as I like. This isn't without some problems though and I guess the skill lies in knowing which things work well with others.
No comments:
Post a Comment