jotaelesalinas/geoloc.html

## geoloc.html
<!doctype html>

<html lang="en">

<head>
	<meta charset="utf-8">

	<title>Geoloc</title>

	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.9.1/underscore-min.js" integrity="sha256-G7A4JrJjJlFqP0yamznwPjAApIKPkadeHfyIwiaa9e0=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha256-CjSoeELFOcH0/uxWu6mC/Vlrc1AARqbm/jiiImDGV3s=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min.js" integrity="sha256-AdQN98MVZs44Eq2yTwtoKufhnU+uZ7v2kXnD5vqzZVo=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.26/moment-timezone-with-data.min.js" integrity="sha256-6EFCRhQs5e10gzbTAKzcFFWcpDGNAzJjkQR3i1lvqYE=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/jQuery-JSONP/2.4.0/jquery.jsonp.min.js" integrity="sha256-ka5MC632qsBwDMI0pQsPze+TU+xqYboMNdnH/cGtfeY=" crossorigin="anonymous"></script>

	<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha256-YLGeXaapI0/5IgZopewRJcFXomhRMlYYjugPLSyNjTY=" crossorigin="anonymous" />

	<style>
		body {
			margin: 2rem;
		}
		textarea {
			width: 100%;
			height: 12em;
			font-family: monospace;
		}
		#ip-msg, #dt-msg {
			margin: 0;
			margin-top: .25em;
		}
		.ok:before {
			content: '✔️ ';
			color: green;
		}
		.err:before {
			content: '❌ ';
			color: red;
		}
		.text-monospace {
			border: 1px solid grey;
			border-radius: .25em;
			padding: .1em .25em;
			background-color: antiquewhite;
			color: dimgray;
		}
		h1, h2, h3, h4, h5, h6 {
			_margin-top: 2rem;
			font-family: cambria;
			font-weight: bolder;
		}
		h2 {
			font-size: 2rem;
			text-transform: uppercase;
			font-size: unset;
		}
		section {
			margin-top: 2rem;
		}
	</style>
</head>

<body>
	<div class="container">
		<h1>IP Geoloc</h1>

		<section class="row">
			<div class="col-12">
				<h2>Input data</h2>
				<p>Copy and paste from your Excel/CSV file.</p>
				<textarea id="input-csv"></textarea>
			</div>
		</section>

		<section class="row">
			<div class="col-12">
				<h2>Options</h2>
				<div class="form-group row">
					<label class="col-sm-2">Headers</label>
					<div class="col-sm-10">
						<input type="checkbox" id="check-headers" checked>
						<label for="check-headers">First row contains column names</label>
					</div>
				</div>
				<div class="form-group row">
					<label class="col-sm-2">IP address column</label>
					<div class="col-sm-10">
						<select id="sel-col-ip"></select>
						<p id="ip-msg" class="small text-muted"></p>
					</div>
				</div>
				<div class="form-group row">
					<label class="col-sm-2">Date column</label>
					<div class="col-sm-10">
						<select id="sel-col-date">
							<option value="">There is no date column</option>
						</select>
					</div>
				</div>
				<div class="form-group row tz d-none">
					<div class="col-sm-2">
						<label>Date format</label>
					</div>
					<div class="col-sm-10">
						<p class="tz d-none">
							<input type="radio" name="dt-format" value="guess" id="dt-format-guess" checked>
								<label for="dt-format-guess">Let the system guess</label>
							<br>
							<input type="radio" name="dt-format" value="D/M/YYYY H:m:s" id="dt-format-dmy">
								<label for="dt-format-dmy"><tt>D/M/YYYY H:m:s</tt></label>
							<br>
							<input type="radio" name="dt-format" value="M/D/YYYY H:m:s" id="dt-format-mdy">
								<label for="dt-format-mdy"><tt>M/D/YYYY H:m:s</tt></label>
							<br>
							<input type="radio" name="dt-format" value="other" id="dt-format-other">
								<label for="dt-format-other">Custom:</label>
								<input id="dt-format-custom" value="YYYY-M-D H:m:s" class="sm">
								<a href="https://momentjs.com/docs/#/parsing/string-format/" class="small" target="_blank">Help</a><br>
						</p>
						<p id="dt-msg" class="small text-muted"></p>
					</div>
				</div>
				<div class="form-group row tz d-none">
					<label class="col-sm-2">Timezone</label>
					<div class="col-sm-10">
						<input type="radio" name="tz" value="unknown" id="tz-unknown" checked>
						<label for="tz-unknown">Unknown or included in the date</label>
						<br>
						<input type="radio" name="tz" value="utc" id="tz-utc">
						<label for="tz-utc">UTC (GMT+00:00)</label>
						<br>
						<input type="radio" name="tz" value="local" id="tz-local">
						<label for="tz-local">Local (...)</label>
						<br>
						<input type="radio" name="tz" value="name" id="tz-name">
						<label for="tz-name">Location</label>
						<select id="sel-tz-location"></select>
						<!--
						<br>
						<input type="radio" name="tz" value="offset" id="tz-offset">
						<label for="tz-offset">Offset</label>
						<input type="text" name="tz-offset" id="txt-tz-offset" placeholder="e.g. -08:00">
						-->
					</div>
				</div>
				<div class="form-group row">
					<label class="col-sm-2">Output</label>
					<div class="col-sm-10">
						<input type="radio" name="out-type" value="append" id="out-type-append-geoloc" checked>
						<label for="out-type-append-geoloc">Append geolocation columns</label>
						<br>
						<input type="radio" name="out-type" value="clean" id="out-type-only-geoloc">
						<label for="out-type-only-geoloc">Only geolocation info</label>
					</div>
				</div>
				<button id="btn-proceed" class="btn btn-dark">Proceed</button>
			</div>
		</section>

		<section class="row prg-section" style="display:none;">
			<div class="col-12">
				Progress: <span class="prg-done"></span> of <span class="prg-total"></span> done.
			</div>
			<div class="col-12">
				<div class="progress">
					<div id="prg" class="progress-bar progress-bar-striped" role="progressbar" style="width: 0%" aria-valuenow="10" aria-valuemin="0" aria-valuemax="100"></div>
				</div>
			</div>
		</section>

		<section class="row out-section" style="display:none;">
			<div class="col-12">
				<h2>Results</h2>
				<textarea id="out-text"></textarea>
			</div>
		</section>

	</div>

<script>
'use strict';

var tz_local = moment.tz.guess();
$('label[for="tz-local"]').html('Local (' + tz_local + ')');
$('#tz-local').attr('data-tz', tz_local);
var tzs = moment.tz.names();
tzs.sort();
tzs = tzs.filter(x => x.match(/\//) && !x.match(/^Etc\//));
tzs.forEach(tz => $('#sel-tz-location').append('<option value="' + tz + '">' + tz + '</option>'));

if ( typeof ObjArr === 'undefined' ) {
    var ObjArr = {};

    ObjArr.allKeys = function (data) {
        var onlyunique = function (value, index, self) {
            return self.indexOf(value) === index;
        };

        var cols = [];
        data.map(x => Object.keys(x))
            .forEach(x => cols.push(...x));
        cols = cols.filter(onlyunique);
        return cols;
    };

    ObjArr.toTSV = function (data) {
        var cols = ObjArr.allKeys(data);

        var tsv = [];
        tsv.push(cols.join("\t"));

        data.map(x => cols.map(c => typeof x[c] != 'undefined' ? x[c] : ''))
            .forEach(x => tsv.push(x.join("\t")));

        return tsv.join("\n") + "\n";
    };

    ObjArr.fromTSV = function (data) {
        var header2js = function (line) {
            var headers = line.split(/\t/g)
                              .map(x => slug(x, '_'));

            headers.forEach((header, colnum) => {
                if ( headers.indexOf(header) == colnum ) {
                    return;
                }

                var n = 2;
                while ( headers.indexOf(header + '_' + n) > -1 ) {
                    n++;
                }
                headers[colnum] = header + '_' + n;
            } );

            return headers;
        };

        var row2js = function (line, headers) {
            line = line.split(/\t/g);
            var js = {};
            for ( var i = 0, l = headers.length; i < l; i++ ) {
                js[headers[i]] = typeof line[i] !== 'undefined' ? line[i] : null;
            }
            return js;
        };

        var lines = data.replace(/\r/g, '')
                        .split(/\n/g);

        var headers = header2js(lines[0]);

        var rows = [],
            row_js = null;
        for ( var i = 1, l = lines.length; i < l; i++ ) {
            if ( lines[i].trim() === '' ) {
                continue;
            }

            try {
                row_js = row2js(lines[i], headers);
                if ( typeof row_js !== 'undefined' && row_js !== null ) {
                    row_js._rownum = i;
                    rows.push(row_js);
                }
            } catch (err) {
                // do nothing
            }
        }

        return rows;
    };
}

var ucfirst = function (str) {
	return str.charAt(0).toUpperCase() + str.slice(1);
};

var slug = function (str, separator) {
	if ( typeof separator === 'undefined' ) {
		separator = '-';
	}
	return str.replace(/\W+/g, ' ')
			  .replace(/^\s+|\s+$/g, '')
			  .replace(/\s+/g, separator)
			  .toLowerCase();
};

var excel2js = function (str, mapper) {
	var header2js = function (line) {
		var headers = _(line.split(/\t/g)).map(x => slug(x, '_'));
		_(headers).each( function (h, colnum) {
			if ( _(headers).indexOf(h) == colnum ) {
				return;
			}

			var n = 2;
			while ( _(headers).indexOf(h + '_' + n) > -1 ) {
				n++;
			}
			headers[colnum] = h + '_' + n;
		} );
		return headers;
	};

	var row2js = function (line, headers) {
		line = line.split(/\t/g);
		var js = {};
		for ( var i = 0, l = headers.length; i < l; i++ ) {
			js[headers[i]] = typeof line[i] !== 'undefined' ? line[i] : null;
		}
		return js;
	};

	if ( typeof mapper === 'undefined' ) {
		mapper = false;
	}

	var lines = str.replace(/\r/g, '')
				   .split(/\n/g);
	var headers = header2js(lines[0]);
	var rows = [],
		row_js = null;
	for ( var i = 1, l = lines.length; i < l; i++ ) {
		if ( lines[i].trim() === '' ) {
			continue;
		}

		row_js = row2js(lines[i], headers);
		if ( row_js ) {
			row_js._rownum = i;
		}
		if ( mapper ) {
			row_js = mapper(row_js);
		}
		if ( typeof row_js !== 'undefined' && row_js !== null ) {
			rows.push(row_js);
		}
	}

	return rows;
};

var ctrlc = function (text) {
	var ID_NEW_TEXTAREA = 'mycustom-clipboard-textarea-hidden-id';
	var elTextarea = document.getElementById(text);
	if ( !elTextarea ) {
		// create fake textarea
		// console.log("Creating textarea...");
		var textarea = document.createElement("textarea");
		textarea.id = ID_NEW_TEXTAREA;
		document.body.appendChild(textarea);
		elTextarea = document.getElementById(id);
		elTextarea.value = text;
		// console.log("The textarea now exists :)");
	} else {
		// console.log("The textarea already exists :3")
	}

	elTextarea.select();
	try {
		var status = document.execCommand('copy');
		if( !status ) {
			console.error("Cannot copy text");
			return false;
		} else {
			// console.log("The text is now on the clipboard");
		}
	} catch (err) {
		console.log('Unable to copy.');
		return false;
	}

	var child = document.getElementById(ID_NEW_TEXTAREA);
	if ( child ) {
		// remove fake textarea
		document.body.removeChild(child);
		//console.log("Textarea removed.");
	} else {
		// remove selection from existing textarea
		if ( elTextarea.setSelectionRange ) {
			elTextarea.focus();
			elTextarea.setSelectionRange(0, 0);
		} else if ( elTextarea.createTextRange ) {
			var range = elTextarea.createTextRange();
			range.moveStart('character', 0);
			range.select();
		}
	}

	return true;
};

var clear_options = function () {
	$('#sel-col-ip').find('option').remove();
	$('#sel-col-date').find('option[value!=""]').remove();
	$('.tz').hide();
};

var input_data = null;

var set_options = function () {
	var has_headers = $('#check-headers').is(':checked');

	var text = $('#input-csv').val();
	if ( text.trim() === '' ) {
		input_data = null;
		return;
	}

	if ( !has_headers ) {
		var num_columns = text.split("\n", 1)[0]
							  .split("\t")
							  .length;
		var headers = [...Array(num_columns).keys()].map(x => 'column_' + (x+1))
										  .join("\t");
		text = headers + "\n" + text;
	}

	input_data = excel2js(text);
	console.log('input_data');
	console.log(input_data);

	clear_options();

	if ( input_data.length == 0 ) {
		input_data = null;
		return;
	}

	var sample = input_data[0];

	var options_ip = [],
		options_dt = [];
	for ( var k in sample ) {
		if ( k == '_rownum' ) {
			continue;
		}
		var item = sample[k].replace(/"/g, '');
		options_ip.push('<option value="' + k + '" data-item="' + item + '">' + k + ' (' + item + ')</option>');
		options_dt.push('<option value="' + k + '" data-item="' + item + '">' + k + ' (' + item + ')</option>');
	}
	$('#sel-col-ip').html(options_ip.join("\n"));
	$('#sel-col-date').append(options_dt.join("\n"));
	check_valid_ip();
};

var check_valid_ip = function () {
	var r_v4 = /^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/;
	var r_v6 = /^((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4}))*::((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4}))*|((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4})){7}$/;

	var column = $('#sel-col-ip').val();
	var ip = $('#sel-col-ip').find("option[value='" + column + "']")
	                         .attr('data-item')
							 .trim()
							 .split(/\s+/)[0];

	var ip_html = '<span class="text-monospace">' + ip + '</span>';

	if ( ip.match(r_v4) ) {
		$('#ip-msg').removeClass('err')
		            .addClass('ok')
					.html(ip_html + ' <i>looks like</i> a valid IP v4 address.');
	} else if ( ip.match(r_v6) ) {
		$('#ip-msg').removeClass('err')
		            .addClass('ok')
					.html(ip_html + ' <i>looks like</i> a valid IP v6 address.');
	} else {
		$('#ip-msg').removeClass('ok')
		            .addClass('err')
					.html(ip_html + ' is not a valid IP address.');
	}
};

var get_dt_format = function () {
	var fmt = $("input[name='dt-format']:checked").attr('value');

	if ( fmt == 'guess' ) {
		fmt = null;
	} else if ( fmt == 'other' ) {
		fmt == $('#dt-format-custom').val().trim();
		if ( fmt === '' ) {
			fmt = null;
		}
	}

	return fmt;
};

var get_dt_tz = function () {
	var tz_type = $('input[name=tz]:checked').attr('value');

	var tz = tz_local;
	if ( tz_type == 'utc' ) {
		tz = 'UTC';
	} else if ( tz_type == 'name' ) {
		tz = $('#sel-tz-location').find('option:checked').val();
	} else if ( tz_type == 'offset' ) {
		tz = $('#txt-tz-offset').val().trim();
	}

	return tz;
};

var check_valid_date = function () {
	var column = $('#sel-col-date').val();
	var dt = $('#sel-col-date').find("option[value='" + column + "']")
	                           .attr('data-item')
							   .replace(/\s+/g, ' ')
							   .trim();
	console.log('dt', dt);

	var dt_html = '<span class="text-monospace">' + dt + '</span>';

	var fmt = get_dt_format()
	console.log('fmt', fmt);

	var tz = get_dt_tz();
	console.log('tz', tz);

	try {
		var d = moment.tz(dt, fmt, tz);
		if ( !d.isValid() ) {
			throw 'Invalid date';
		}
		console.log('d', d.toDate());
	} catch (errmsg) {
		$('#dt-msg').removeClass('ok')
		            .addClass('err')
					.html(dt_html + ' is not recognised as a valid date.');
		return;
	}

	$('#dt-msg').removeClass('err')
				.addClass('ok')
				.html(dt_html + ' <i>looks like</i> a valid date. Make sure that it is what you expect:' +
				      '<br>ISO standard (UTC): <b>' + d.tz('UTC').format() + '</b>' +
					  '<br>Local (' + tz_local + '): <b>' + d.tz(tz_local).toDate() + '</b>');
};

var dt_to_date = function (dt, fmt, tz) {
	var d = moment.tz(dt, fmt, tz);
	return d.tz('UTC').format().slice(0,10);
};

var start = function () {
	var ip_column = $('#sel-col-ip').val();
	console.log('ip_column', ip_column);
	if ( !ip_column ) {
		alert('The column for the IP address is not selected.');
		return;
	}

	var dt_column = $('#sel-col-date').val() || null;
	console.log('dt_column', dt_column);

	var fmt = null;
	var tz = null;
	if ( dt_column ) {
		fmt = get_dt_format();
		tz = get_dt_tz();
		console.log('fmt', fmt);
		console.log('tz', tz);
	}

	var list = input_data.map(x => {
		var ip = x[ip_column].trim().split(/\s+/)[0];
		if ( dt_column ) {
			var dt = dt_to_date(x[dt_column], fmt, tz);
			return ip + '#' + dt;
		} else {
			return ip;
		}
	});
	list = _(list).uniq();

	progress_start(list);
	list.forEach(x => {
		var p = x.split(/#/);
		lookup(p[0], p[1] || null, progress_ok, progress_fail);
	});
};

var lookup_base_url = 'http://127.0.0.1:8000/ajax/';

var lookup = function (ip, dt, on_success, on_fail) {
	var url = lookup_base_url;
	url += '?ip_address=' + ip;
	if ( dt ) {
		url += '&ip_date=' + dt;
	}
	url += '&callback=?'

	console.log(url);
	$.jsonp({
		url: url,
		success: data => on_success(ip, dt, data),
		error: err => on_fail(ip, dt, err)
	});
};

var progress_total = null;
var progress_pending = null;

var progress_start = function (list) {
	//console.log('progress_start()');

	results = {};
	progress_total = list.length;
	progress_pending = list.length;

	$('.prg-total').text(progress_total);
	$('.prg-done').text(progress_total - progress_pending);
	$('#prg').css('width', '0%');
	$('.prg-section').show();
};
var progress_ok = function (ip, dt, data) {
	//console.log('progress_ok()');
	add_results(ip, dt, data);
	progress_decrease_pending();
};
var progress_fail = function (ip, dt, errmsg) {
	//console.log('progress_fail()');
	add_results(ip, dt, 'error');
	progress_decrease_pending();
};
var progress_decrease_pending = function () {
	progress_pending -= 1;

	$('.prg-done').text(progress_total - progress_pending);
	$('#prg').css('width', (100 - (progress_pending * 100 / progress_total)).toFixed(0) + '%');

	if ( progress_pending == 0 ) {
		progress_end();
	}
};
var progress_end = function () {
	//console.log('progress_end()');
	console.log(results);
	show_results(results);
};

var results = {};

var flatten_results = function (res) {
	var obj = {};

	for ( var k1 in res ) {
		for ( var k2 in res[k1] ) {
			obj[k1 + '_' + k2] = res[k1][k2];
		}
	}

	return obj;
};

var add_results = function (ip, dt, data) {
	if ( typeof results[ip] == 'undefined' ) {
		results[ip] = {};
	}
	if ( dt === null ) {
		dt = '_no_date_';
	}

	if ( data == 'error' ) {
		results[ip][dt] = { error: 'Error' };
	} else {
		//console.log(data);
		results[ip][dt] = flatten_results(data);
	}
};

var show_results = function (data) {
	var append = $('input[name=out-type]:checked').attr('value') == 'append';

	var out = [];

	if ( !append ) {
		for ( var ip in data ) {
			for ( var dt in data[ip] ) {
				out.push(Object.assign({}, {ip: ip, datetime: dt}, data[ip][dt]));
			}
		}
	} else {
		var ip_column = $('#sel-col-ip').val();
		//console.log('ip_column', ip_column);
		var dt_column = $('#sel-col-date').val() || null;
		//console.log('dt_column', dt_column);

		var fmt = null;
		var tz = null;
		if ( dt_column ) {
			fmt = get_dt_format();
			tz = get_dt_tz();
			//console.log('fmt', fmt);
			//console.log('tz', tz);
		}

		input_data.forEach(x => {
			delete x._rownum;
			var ip = x[ip_column].trim().split(/\s+/)[0];
			//console.log('ip', ip);
			var dt = dt_column ? x[dt_column] : null;
			//console.log('dt', dt);
			var day = dt ? dt_to_date(dt, fmt, tz) : '_no_date_';
			//console.log('day', day);
			out.push(Object.assign({}, x, ip != x[ip_column] ? {_lookup_ip: ip} : {}, {_lookup_day: day}, data[ip][day]));
		});
	}

	$('#out-text').val(ObjArr.toTSV(out));
	$('.out-section').show();
};

$(document).ready( function () {
	$('#input-csv').blur( function () {
		var text = $('#input-csv').val().trim();
		if ( text === '' ) {
			clear_options();
		} else {
			set_options();
		}
	} );

	$('#check-headers').change(set_options);

	$('#sel-col-ip').change(check_valid_ip);

	$('#sel-col-date').change( function () {
		if ( $(this).val() !== '' ) {
			$('.tz').removeClass('d-none');
			$('.tz').show();
			check_valid_date();
		} else {
			$('.tz').hide();
		}
	} );
	$('#check-swap-date').change(check_valid_date);
	$('input[name=tz]').change(check_valid_date);
	$('#sel-tz-location').change(check_valid_date);
	$('#txt-tz-offset').change(check_valid_date);
	$("input[name='dt-format']").change(check_valid_date);
	$("#dt-format-custom").change(check_valid_date);

	$('#btn-proceed').click(start);
} );
</script>

</body>

</html>
	<!doctype html>

	<html lang="en">

	<head>
	<meta charset="utf-8">

	<title>Geoloc</title>

	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.9.1/underscore-min.js" integrity="sha256-G7A4JrJjJlFqP0yamznwPjAApIKPkadeHfyIwiaa9e0=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha256-CjSoeELFOcH0/uxWu6mC/Vlrc1AARqbm/jiiImDGV3s=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min.js" integrity="sha256-AdQN98MVZs44Eq2yTwtoKufhnU+uZ7v2kXnD5vqzZVo=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.26/moment-timezone-with-data.min.js" integrity="sha256-6EFCRhQs5e10gzbTAKzcFFWcpDGNAzJjkQR3i1lvqYE=" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/jQuery-JSONP/2.4.0/jquery.jsonp.min.js" integrity="sha256-ka5MC632qsBwDMI0pQsPze+TU+xqYboMNdnH/cGtfeY=" crossorigin="anonymous"></script>

	<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha256-YLGeXaapI0/5IgZopewRJcFXomhRMlYYjugPLSyNjTY=" crossorigin="anonymous" />

	<style>
	body {
	margin: 2rem;
	}
	textarea {
	width: 100%;
	height: 12em;
	font-family: monospace;
	}
	#ip-msg, #dt-msg {
	margin: 0;
	margin-top: .25em;
	}
	.ok:before {
	content: '✔️ ';
	color: green;
	}
	.err:before {
	content: '❌ ';
	color: red;
	}
	.text-monospace {
	border: 1px solid grey;
	border-radius: .25em;
	padding: .1em .25em;
	background-color: antiquewhite;
	color: dimgray;
	}
	h1, h2, h3, h4, h5, h6 {
	_margin-top: 2rem;
	font-family: cambria;
	font-weight: bolder;
	}
	h2 {
	font-size: 2rem;
	text-transform: uppercase;
	font-size: unset;
	}
	section {
	margin-top: 2rem;
	}
	</style>
	</head>

	<body>
	<div class="container">
	<h1>IP Geoloc</h1>

	<section class="row">
	<div class="col-12">
	<h2>Input data</h2>
	<p>Copy and paste from your Excel/CSV file.</p>
	<textarea id="input-csv"></textarea>
	</div>
	</section>

	<section class="row">
	<div class="col-12">
	<h2>Options</h2>
	<div class="form-group row">
	<label class="col-sm-2">Headers</label>
	<div class="col-sm-10">
	<input type="checkbox" id="check-headers" checked>
	<label for="check-headers">First row contains column names</label>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-sm-2">IP address column</label>
	<div class="col-sm-10">
	<select id="sel-col-ip"></select>
	<p id="ip-msg" class="small text-muted"></p>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-sm-2">Date column</label>
	<div class="col-sm-10">
	<select id="sel-col-date">
	<option value="">There is no date column</option>
	</select>
	</div>
	</div>
	<div class="form-group row tz d-none">
	<div class="col-sm-2">
	<label>Date format</label>
	</div>
	<div class="col-sm-10">
	<p class="tz d-none">
	<input type="radio" name="dt-format" value="guess" id="dt-format-guess" checked>
	<label for="dt-format-guess">Let the system guess</label>
	<br>
	<input type="radio" name="dt-format" value="D/M/YYYY H:m:s" id="dt-format-dmy">
	<label for="dt-format-dmy"><tt>D/M/YYYY H:m:s</tt></label>
	<br>
	<input type="radio" name="dt-format" value="M/D/YYYY H:m:s" id="dt-format-mdy">
	<label for="dt-format-mdy"><tt>M/D/YYYY H:m:s</tt></label>
	<br>
	<input type="radio" name="dt-format" value="other" id="dt-format-other">
	<label for="dt-format-other">Custom:</label>
	<input id="dt-format-custom" value="YYYY-M-D H:m:s" class="sm">
	<a href="https://momentjs.com/docs/#/parsing/string-format/" class="small" target="_blank">Help</a><br>
	</p>
	<p id="dt-msg" class="small text-muted"></p>
	</div>
	</div>
	<div class="form-group row tz d-none">
	<label class="col-sm-2">Timezone</label>
	<div class="col-sm-10">
	<input type="radio" name="tz" value="unknown" id="tz-unknown" checked>
	<label for="tz-unknown">Unknown or included in the date</label>
	<br>
	<input type="radio" name="tz" value="utc" id="tz-utc">
	<label for="tz-utc">UTC (GMT+00:00)</label>
	<br>
	<input type="radio" name="tz" value="local" id="tz-local">
	<label for="tz-local">Local (...)</label>
	<br>
	<input type="radio" name="tz" value="name" id="tz-name">
	<label for="tz-name">Location</label>
	<select id="sel-tz-location"></select>
	<!--
	<br>
	<input type="radio" name="tz" value="offset" id="tz-offset">
	<label for="tz-offset">Offset</label>
	<input type="text" name="tz-offset" id="txt-tz-offset" placeholder="e.g. -08:00">
	-->
	</div>
	</div>
	<div class="form-group row">
	<label class="col-sm-2">Output</label>
	<div class="col-sm-10">
	<input type="radio" name="out-type" value="append" id="out-type-append-geoloc" checked>
	<label for="out-type-append-geoloc">Append geolocation columns</label>
	<br>
	<input type="radio" name="out-type" value="clean" id="out-type-only-geoloc">
	<label for="out-type-only-geoloc">Only geolocation info</label>
	</div>
	</div>
	<button id="btn-proceed" class="btn btn-dark">Proceed</button>
	</div>
	</section>

	<section class="row prg-section" style="display:none;">
	<div class="col-12">
	Progress: <span class="prg-done"></span> of <span class="prg-total"></span> done.
	</div>
	<div class="col-12">
	<div class="progress">
	<div id="prg" class="progress-bar progress-bar-striped" role="progressbar" style="width: 0%" aria-valuenow="10" aria-valuemin="0" aria-valuemax="100"></div>
	</div>
	</div>
	</section>

	<section class="row out-section" style="display:none;">
	<div class="col-12">
	<h2>Results</h2>
	<textarea id="out-text"></textarea>
	</div>
	</section>

	</div>

	<script>
	'use strict';

	var tz_local = moment.tz.guess();
	$('label[for="tz-local"]').html('Local (' + tz_local + ')');
	$('#tz-local').attr('data-tz', tz_local);
	var tzs = moment.tz.names();
	tzs.sort();
	tzs = tzs.filter(x => x.match(/\//) && !x.match(/^Etc\//));
	tzs.forEach(tz => $('#sel-tz-location').append('<option value="' + tz + '">' + tz + '</option>'));

	if ( typeof ObjArr === 'undefined' ) {
	var ObjArr = {};

	ObjArr.allKeys = function (data) {
	var onlyunique = function (value, index, self) {
	return self.indexOf(value) === index;
	};

	var cols = [];
	data.map(x => Object.keys(x))
	.forEach(x => cols.push(...x));
	cols = cols.filter(onlyunique);
	return cols;
	};

	ObjArr.toTSV = function (data) {
	var cols = ObjArr.allKeys(data);

	var tsv = [];
	tsv.push(cols.join("\t"));

	data.map(x => cols.map(c => typeof x[c] != 'undefined' ? x[c] : ''))
	.forEach(x => tsv.push(x.join("\t")));

	return tsv.join("\n") + "\n";
	};

	ObjArr.fromTSV = function (data) {
	var header2js = function (line) {
	var headers = line.split(/\t/g)
	.map(x => slug(x, '_'));

	headers.forEach((header, colnum) => {
	if ( headers.indexOf(header) == colnum ) {
	return;
	}

	var n = 2;
	while ( headers.indexOf(header + '_' + n) > -1 ) {
	n++;
	}
	headers[colnum] = header + '_' + n;
	} );

	return headers;
	};

	var row2js = function (line, headers) {
	line = line.split(/\t/g);
	var js = {};
	for ( var i = 0, l = headers.length; i < l; i++ ) {
	js[headers[i]] = typeof line[i] !== 'undefined' ? line[i] : null;
	}
	return js;
	};

	var lines = data.replace(/\r/g, '')
	.split(/\n/g);

	var headers = header2js(lines[0]);

	var rows = [],
	row_js = null;
	for ( var i = 1, l = lines.length; i < l; i++ ) {
	if ( lines[i].trim() === '' ) {
	continue;
	}

	try {
	row_js = row2js(lines[i], headers);
	if ( typeof row_js !== 'undefined' && row_js !== null ) {
	row_js._rownum = i;
	rows.push(row_js);
	}
	} catch (err) {
	// do nothing
	}
	}

	return rows;
	};
	}

	var ucfirst = function (str) {
	return str.charAt(0).toUpperCase() + str.slice(1);
	};

	var slug = function (str, separator) {
	if ( typeof separator === 'undefined' ) {
	separator = '-';
	}
	return str.replace(/\W+/g, ' ')
	.replace(/^\s+\|\s+$/g, '')
	.replace(/\s+/g, separator)
	.toLowerCase();
	};

	var excel2js = function (str, mapper) {
	var header2js = function (line) {
	var headers = _(line.split(/\t/g)).map(x => slug(x, '_'));
	_(headers).each( function (h, colnum) {
	if ( _(headers).indexOf(h) == colnum ) {
	return;
	}

	var n = 2;
	while ( _(headers).indexOf(h + '_' + n) > -1 ) {
	n++;
	}
	headers[colnum] = h + '_' + n;
	} );
	return headers;
	};

	var row2js = function (line, headers) {
	line = line.split(/\t/g);
	var js = {};
	for ( var i = 0, l = headers.length; i < l; i++ ) {
	js[headers[i]] = typeof line[i] !== 'undefined' ? line[i] : null;
	}
	return js;
	};

	if ( typeof mapper === 'undefined' ) {
	mapper = false;
	}

	var lines = str.replace(/\r/g, '')
	.split(/\n/g);
	var headers = header2js(lines[0]);
	var rows = [],
	row_js = null;
	for ( var i = 1, l = lines.length; i < l; i++ ) {
	if ( lines[i].trim() === '' ) {
	continue;
	}

	row_js = row2js(lines[i], headers);
	if ( row_js ) {
	row_js._rownum = i;
	}
	if ( mapper ) {
	row_js = mapper(row_js);
	}
	if ( typeof row_js !== 'undefined' && row_js !== null ) {
	rows.push(row_js);
	}
	}

	return rows;
	};

	var ctrlc = function (text) {
	var ID_NEW_TEXTAREA = 'mycustom-clipboard-textarea-hidden-id';
	var elTextarea = document.getElementById(text);
	if ( !elTextarea ) {
	// create fake textarea
	// console.log("Creating textarea...");
	var textarea = document.createElement("textarea");
	textarea.id = ID_NEW_TEXTAREA;
	document.body.appendChild(textarea);
	elTextarea = document.getElementById(id);
	elTextarea.value = text;
	// console.log("The textarea now exists :)");
	} else {
	// console.log("The textarea already exists :3")
	}

	elTextarea.select();
	try {
	var status = document.execCommand('copy');
	if( !status ) {
	console.error("Cannot copy text");
	return false;
	} else {
	// console.log("The text is now on the clipboard");
	}
	} catch (err) {
	console.log('Unable to copy.');
	return false;
	}

	var child = document.getElementById(ID_NEW_TEXTAREA);
	if ( child ) {
	// remove fake textarea
	document.body.removeChild(child);
	//console.log("Textarea removed.");
	} else {
	// remove selection from existing textarea
	if ( elTextarea.setSelectionRange ) {
	elTextarea.focus();
	elTextarea.setSelectionRange(0, 0);
	} else if ( elTextarea.createTextRange ) {
	var range = elTextarea.createTextRange();
	range.moveStart('character', 0);
	range.select();
	}
	}

	return true;
	};

	var clear_options = function () {
	$('#sel-col-ip').find('option').remove();
	$('#sel-col-date').find('option[value!=""]').remove();
	$('.tz').hide();
	};

	var input_data = null;

	var set_options = function () {
	var has_headers = $('#check-headers').is(':checked');

	var text = $('#input-csv').val();
	if ( text.trim() === '' ) {
	input_data = null;
	return;
	}

	if ( !has_headers ) {
	var num_columns = text.split("\n", 1)[0]
	.split("\t")
	.length;
	var headers = [...Array(num_columns).keys()].map(x => 'column_' + (x+1))
	.join("\t");
	text = headers + "\n" + text;
	}

	input_data = excel2js(text);
	console.log('input_data');
	console.log(input_data);

	clear_options();

	if ( input_data.length == 0 ) {
	input_data = null;
	return;
	}

	var sample = input_data[0];

	var options_ip = [],
	options_dt = [];
	for ( var k in sample ) {
	if ( k == '_rownum' ) {
	continue;
	}
	var item = sample[k].replace(/"/g, '');
	options_ip.push('<option value="' + k + '" data-item="' + item + '">' + k + ' (' + item + ')</option>');
	options_dt.push('<option value="' + k + '" data-item="' + item + '">' + k + ' (' + item + ')</option>');
	}
	$('#sel-col-ip').html(options_ip.join("\n"));
	$('#sel-col-date').append(options_dt.join("\n"));
	check_valid_ip();
	};

	var check_valid_ip = function () {
	var r_v4 = /^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/;
	var r_v6 = /^((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4}))::((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4}))\|((?:[0-9A-Fa-f]{1,4}))((?::[0-9A-Fa-f]{1,4})){7}$/;

	var column = $('#sel-col-ip').val();
	var ip = $('#sel-col-ip').find("option[value='" + column + "']")
	.attr('data-item')
	.trim()
	.split(/\s+/)[0];

	var ip_html = '<span class="text-monospace">' + ip + '</span>';

	if ( ip.match(r_v4) ) {
	$('#ip-msg').removeClass('err')
	.addClass('ok')
	.html(ip_html + ' <i>looks like</i> a valid IP v4 address.');
	} else if ( ip.match(r_v6) ) {
	$('#ip-msg').removeClass('err')
	.addClass('ok')
	.html(ip_html + ' <i>looks like</i> a valid IP v6 address.');
	} else {
	$('#ip-msg').removeClass('ok')
	.addClass('err')
	.html(ip_html + ' is not a valid IP address.');
	}
	};

	var get_dt_format = function () {
	var fmt = $("input[name='dt-format']:checked").attr('value');

	if ( fmt == 'guess' ) {
	fmt = null;
	} else if ( fmt == 'other' ) {
	fmt == $('#dt-format-custom').val().trim();
	if ( fmt === '' ) {
	fmt = null;
	}
	}

	return fmt;
	};

	var get_dt_tz = function () {
	var tz_type = $('input[name=tz]:checked').attr('value');

	var tz = tz_local;
	if ( tz_type == 'utc' ) {
	tz = 'UTC';
	} else if ( tz_type == 'name' ) {
	tz = $('#sel-tz-location').find('option:checked').val();
	} else if ( tz_type == 'offset' ) {
	tz = $('#txt-tz-offset').val().trim();
	}

	return tz;
	};

	var check_valid_date = function () {
	var column = $('#sel-col-date').val();
	var dt = $('#sel-col-date').find("option[value='" + column + "']")
	.attr('data-item')
	.replace(/\s+/g, ' ')
	.trim();
	console.log('dt', dt);

	var dt_html = '<span class="text-monospace">' + dt + '</span>';

	var fmt = get_dt_format()
	console.log('fmt', fmt);

	var tz = get_dt_tz();
	console.log('tz', tz);

	try {
	var d = moment.tz(dt, fmt, tz);
	if ( !d.isValid() ) {
	throw 'Invalid date';
	}
	console.log('d', d.toDate());
	} catch (errmsg) {
	$('#dt-msg').removeClass('ok')
	.addClass('err')
	.html(dt_html + ' is not recognised as a valid date.');
	return;
	}

	$('#dt-msg').removeClass('err')
	.addClass('ok')
	.html(dt_html + ' <i>looks like</i> a valid date. Make sure that it is what you expect:' +
	'<br>ISO standard (UTC): <b>' + d.tz('UTC').format() + '</b>' +
	'<br>Local (' + tz_local + '): <b>' + d.tz(tz_local).toDate() + '</b>');
	};

	var dt_to_date = function (dt, fmt, tz) {
	var d = moment.tz(dt, fmt, tz);
	return d.tz('UTC').format().slice(0,10);
	};

	var start = function () {
	var ip_column = $('#sel-col-ip').val();
	console.log('ip_column', ip_column);
	if ( !ip_column ) {
	alert('The column for the IP address is not selected.');
	return;
	}

	var dt_column = $('#sel-col-date').val() \|\| null;
	console.log('dt_column', dt_column);

	var fmt = null;
	var tz = null;
	if ( dt_column ) {
	fmt = get_dt_format();
	tz = get_dt_tz();
	console.log('fmt', fmt);
	console.log('tz', tz);
	}

	var list = input_data.map(x => {
	var ip = x[ip_column].trim().split(/\s+/)[0];
	if ( dt_column ) {
	var dt = dt_to_date(x[dt_column], fmt, tz);
	return ip + '#' + dt;
	} else {
	return ip;
	}
	});
	list = _(list).uniq();

	progress_start(list);
	list.forEach(x => {
	var p = x.split(/#/);
	lookup(p[0], p[1] \|\| null, progress_ok, progress_fail);
	});
	};

	var lookup_base_url = 'http://127.0.0.1:8000/ajax/';

	var lookup = function (ip, dt, on_success, on_fail) {
	var url = lookup_base_url;
	url += '?ip_address=' + ip;
	if ( dt ) {
	url += '&ip_date=' + dt;
	}
	url += '&callback=?'

	console.log(url);
	$.jsonp({
	url: url,
	success: data => on_success(ip, dt, data),
	error: err => on_fail(ip, dt, err)
	});
	};

	var progress_total = null;
	var progress_pending = null;

	var progress_start = function (list) {
	//console.log('progress_start()');

	results = {};
	progress_total = list.length;
	progress_pending = list.length;

	$('.prg-total').text(progress_total);
	$('.prg-done').text(progress_total - progress_pending);
	$('#prg').css('width', '0%');
	$('.prg-section').show();
	};
	var progress_ok = function (ip, dt, data) {
	//console.log('progress_ok()');
	add_results(ip, dt, data);
	progress_decrease_pending();
	};
	var progress_fail = function (ip, dt, errmsg) {
	//console.log('progress_fail()');
	add_results(ip, dt, 'error');
	progress_decrease_pending();
	};
	var progress_decrease_pending = function () {
	progress_pending -= 1;

	$('.prg-done').text(progress_total - progress_pending);
	$('#prg').css('width', (100 - (progress_pending * 100 / progress_total)).toFixed(0) + '%');

	if ( progress_pending == 0 ) {
	progress_end();
	}
	};
	var progress_end = function () {
	//console.log('progress_end()');
	console.log(results);
	show_results(results);
	};

	var results = {};

	var flatten_results = function (res) {
	var obj = {};

	for ( var k1 in res ) {
	for ( var k2 in res[k1] ) {
	obj[k1 + '_' + k2] = res[k1][k2];
	}
	}

	return obj;
	};

	var add_results = function (ip, dt, data) {
	if ( typeof results[ip] == 'undefined' ) {
	results[ip] = {};
	}
	if ( dt === null ) {
	dt = '_no_date_';
	}

	if ( data == 'error' ) {
	results[ip][dt] = { error: 'Error' };
	} else {
	//console.log(data);
	results[ip][dt] = flatten_results(data);
	}
	};

	var show_results = function (data) {
	var append = $('input[name=out-type]:checked').attr('value') == 'append';

	var out = [];

	if ( !append ) {
	for ( var ip in data ) {
	for ( var dt in data[ip] ) {
	out.push(Object.assign({}, {ip: ip, datetime: dt}, data[ip][dt]));
	}
	}
	} else {
	var ip_column = $('#sel-col-ip').val();
	//console.log('ip_column', ip_column);
	var dt_column = $('#sel-col-date').val() \|\| null;
	//console.log('dt_column', dt_column);

	var fmt = null;
	var tz = null;
	if ( dt_column ) {
	fmt = get_dt_format();
	tz = get_dt_tz();
	//console.log('fmt', fmt);
	//console.log('tz', tz);
	}

	input_data.forEach(x => {
	delete x._rownum;
	var ip = x[ip_column].trim().split(/\s+/)[0];
	//console.log('ip', ip);
	var dt = dt_column ? x[dt_column] : null;
	//console.log('dt', dt);
	var day = dt ? dt_to_date(dt, fmt, tz) : '_no_date_';
	//console.log('day', day);
	out.push(Object.assign({}, x, ip != x[ip_column] ? {_lookup_ip: ip} : {}, {_lookup_day: day}, data[ip][day]));
	});
	}

	$('#out-text').val(ObjArr.toTSV(out));
	$('.out-section').show();
	};

	$(document).ready( function () {
	$('#input-csv').blur( function () {
	var text = $('#input-csv').val().trim();
	if ( text === '' ) {
	clear_options();
	} else {
	set_options();
	}
	} );

	$('#check-headers').change(set_options);

	$('#sel-col-ip').change(check_valid_ip);

	$('#sel-col-date').change( function () {
	if ( $(this).val() !== '' ) {
	$('.tz').removeClass('d-none');
	$('.tz').show();
	check_valid_date();
	} else {
	$('.tz').hide();
	}
	} );
	$('#check-swap-date').change(check_valid_date);
	$('input[name=tz]').change(check_valid_date);
	$('#sel-tz-location').change(check_valid_date);
	$('#txt-tz-offset').change(check_valid_date);
	$("input[name='dt-format']").change(check_valid_date);
	$("#dt-format-custom").change(check_valid_date);

	$('#btn-proceed').click(start);
	} );
	</script>

	</body>

	</html>