<!doctype html>
<html>
<head>
<title>Spreadsheet - RJM Programming - November, 2016</title>
<link href='//www.rjmprogramming.com.au/PHP/emboss_h1.css' rel='stylesheet' type='text/css'>
<style>
body { background-color: #f0f0f0; }
thead { background-color: pink; }
tbody { background-color: yellow; }
tbody { background-color: yellow; }
</style>

<script type='text/javascript'>

var vals=[];
var ids=[];
var colids=['A'];
var colvalues=['A'];

var colletters=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
var rownumbers=['0','1','2','3','4','5','6','7','8','9'];

var lastcolnum=0;
var lastrownum=1;

var lastbiggestcolnum=0;
var lastbiggestrownum=1;

var lastcol='A';
var lastrow='1';

var lastbiggestcol='A';
var lastbiggestrow='1';

var tname='';
var xjsn="";

var curcol='A';
var currow=1;
var proposed_innerHTML = "<thead id='data_thead'><tr id='data_tr0' style='background-color:lightgray;'><th id='data_th0'><input title='Click to rename' type='button' id='A_0' value='A'></input></th></tr></thead><tbody id='data_tbody'><tr id='data_tr1'><td id='data_td_A_1'><input onblur='assess(this);' type='text' id='A_1' value=''></input></td></tr></tbody>";
var curcnt=0;

var dbName = "todonew";
var dbVersion = 4;
var todoDB = {};
var indexedDB;
var idb=null;
//var db;
//var trans;
//var store;
//var request;

function createDatabase() { // thanks to https://www.html5rocks.com/en/tutorials/webdatabase/websql-indexeddb/
if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") dbVersion++;
if (idb == null) {
indexedDB = window.indexedDB || window.webkitIndexedDB || window.mozIndexedDB || window.msIndexedDB;
idb=indexedDB;

// Handle the prefix of Chrome to IDBTransaction/IDBKeyRange.
todoDB.indexedDB = {};
todoDB.indexedDB.db = null;
if ('webkitIndexedDB' in window) {
window.IDBTransaction = window.webkitIDBTransaction;
window.IDBKeyRange = window.webkitIDBKeyRange;
}

// Hook up the errors to the console so we could see it.
// In the future, we need to push these messages to the user.
indexedDB.onerror = function(e) {
console.log(e);
};

//window.indexedDB.open(dbName);

//alert(98 + ' ' + document.getElementById('tablename').value);
tname=document.getElementById('tablename').value;
createTable(document.getElementById('tablename').value);
//alert(99);
} else if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") {
//alert(document.getElementById('tablename').value);
tname=document.getElementById('tablename').value;
dbVersion++;
createTable(document.getElementById('tablename').value);
} else {
//alert('here4');
populateTable();
}
}

function createTable(tblname) {
if (tblname == "") {
tname="worksheet1";
} else {
tname=tblname;
}

//alert(799);
//indexedDB.open = function() {
//alert(199);
var request = window.indexedDB.open(dbName, dbVersion);

request.onerror = function(event) {
console.log('Error loading database.');
};

request.onupgradeneeded = function(event) {
//alert(399);
console.log ("going to upgrade our DB!");

todoDB.indexedDB.db = event.target.result;
var db = todoDB.indexedDB.db;

//db.onerror = function(event) {
// note.innerHTML += '<li>Error loading database.</li>';
//};

// Create an objectStore for this database
//var objectStore = db.createObjectStore("toDoList", { keyPath: "taskTitle" });




//todoDB.indexedDB.db = event.target.result;
//var db = todoDB.indexedDB.db;
//if(db.objectStoreNames.contains(tname)) {
// db.deleteObjectStore(tname);
//}
var store = db.createObjectStore(tname, { autoIncrement : true }); //, {keyPath: "timeStamp"});
//todoDB.indexedDB.getAllTodoItems();
//alert('here44');
setTimeout(populateTable,500);
};

request.onsuccess = function(event) {
//alert(299);
console.log ("success to open DB: " + dbName);
todoDB.indexedDB.db = event.target.result;
var db = todoDB.indexedDB.db;
if (db.setVersion) {
console.log("in old setVersion: "+ db.setVersion);
if (db.version != dbVersion) {
var req = db.setVersion(dbVersion);
req.onsuccess = function () {
if(db.objectStoreNames.contains(tname)) {
db.deleteObjectStore(tname);
}
var store = db.createObjectStore(tname, { autoIncrement : true }); //, {keyPath: "timeStamp"});
var trans = req.result;
trans.oncomplete = function(e) {
console.log("== trans oncomplete ==");
todoDB.indexedDB.getAllTodoItems();
//alert('here444');
populateTable();
};
};
} else {
//window.todoDB.indexedDB.getAllTodoItems();
//alert('here14');
populateTable();
}
} else {
//window.todoDB.indexedDB.getAllTodoItems();
//alert('here24');
populateTable();
}
};


//};

//window.indexedDB.open(dbName); // new
}

function populateTable() {
var thiscolis="", thisvalis="", delim='';
var strt=null;
xjsn="[]";
var db = window.todoDB.indexedDB.db;
var trans = db.transaction(tname, 'readwrite');
var store = trans.objectStore(tname);
//alert(tname);
// if(db.objectStoreNames.contains(tname)) {
// db.deleteObjectStore(tname);
// }
for (var irow=1; irow<=eval(lastbiggestrow); irow++) {
for (var icol=0; icol<colids.length; icol++) {
thiscolis=colvalues[icol];
thisvalis=document.getElementById(colids[icol].split('_')[0] + '_' + irow).value;
//{ x: undefined, y: Object
if (icol == 0) {
xjsn=xjsn.replace(']', delim + '{ "' + thiscolis + '": "' + thisvalis + '" }]');
} else {
xjsn=xjsn.replace('}]', delim + '"' + thiscolis + '": "' + thisvalis + '" }]');
}
if (eval(1 + icol) == colids.length) {
if (1 == 1) {
strt=JSON.parse(xjsn.replace('[','').replace(']',''));
store.add(strt);
xjsn="[]";
delim='';
} else {
xjsn=xjsn.replace('}]', delim + '"' + "timeStamp" + '": new Date().getTime()}]');
}
} else {
delim=', ';
}
}
}

if (1 == 1) {
if (tname == '') {
db.close();
if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") dbVersion++;
top.location.href=document.URL.split('?')[0].split('#')[0] + '?tablename=worksheet1&dbname=' + dbName + '&dbversion=' + dbVersion;
} else {
db.close();
if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") dbVersion++;
top.location.href=document.URL.split('?')[0].split('#')[0] + '?tablename=' + encodeURIComponent(tname) + '&dbname=' + dbName + '&dbversion=' + dbVersion;
}

} else {

todoDB.indexedDB.addTodo = function() {
var db = todoDB.indexedDB.db;
var trans = db.transaction(tname, 'readwrite');
var store = trans.objectStore(tname);

//var data = {
// "text": todoText, // todoText should be visible here
// "timeStamp": new Date().getTime()
//};

//alert('1:' + xjsn);
var reqt = store.put(xjsn);
//alert('2:' + xjsn);

reqt.oncomplete = function(e) {
todoDB.indexedDB.getAllTodoItems();
if (tname == '') {
db.close();
if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") dbVersion++;
top.location.href=document.URL.split('?')[0].split('#')[0] + '?tablename=worksheet1&dbname=' + dbName + '&dbversion=' + dbVersion;
} else {
db.close();
if (tname != document.getElementById('tablename').value && document.getElementById('tablename').value != "") dbVersion++;
top.location.href=document.URL.split('?')[0].split('#')[0] + '?tablename=' + encodeURIComponent(tname) + '&dbname=' + dbName + '&dbversion=' + dbVersion;
}
};

reqt.onerror = function(e) {
console.log("Error Adding: ", e);
};
};
}
//window.indexedDB.addTodo();
}

function showAll() {
tname=location.search.split('tablename=')[1] ? location.search.split('tablename=')[1].split('&')[0] : 'worksheet1';
dbname=location.search.split('dbname=')[1] ? location.search.split('dbname=')[1].split('&')[0] : dbName;
dbVersion=location.search.split('dbversion=')[1] ? location.search.split('dbversion=')[1].split('&')[0] : dbVersion;

if (document.URL.indexOf('tablename=') != -1) {
if (tname == "") tname='worksheet1';
document.getElementById('tablename').value=tname;

indexedDB = window.indexedDB || window.webkitIndexedDB || window.mozIndexedDB || window.msIndexedDB;

// Handle the prefix of Chrome to IDBTransaction/IDBKeyRange.
todoDB.indexedDB = {};
todoDB.indexedDB.db = null;
if ('webkitIndexedDB' in window) {
window.IDBTransaction = window.webkitIDBTransaction;
window.IDBKeyRange = window.webkitIDBKeyRange;
}

// Hook up the errors to the console so we could see it.
// In the future, we need to push these messages to the user.
indexedDB.onerror = function(e) {
console.log(e);
};


//todoDB.indexedDB.open = function(event) {
// var v = "2.0 beta"; // yes! you can put strings in the version not just numbers
// var request = indexedDB.open(dbName, v);

// request.onupgradeneeded = function(e) {
// var db = request.result;
// var store = db.createObjectStore(tname, { autoIncrement : true }); //, {keyPath: "timeStamp"});
// };

// request.onsuccess = function(e) {
// todoDB.indexedDB.db = e.target.result;
// todoDB.indexedDB.getAllTodoItems();
// };

// request.onfailure = todoDB.indexedDB.onerror;
//};

request = window.indexedDB.open(dbName, dbVersion);

request.onupgradeneeded = function(event) {
//alert(399);
console.log ("going to upgrade our DB!");

todoDB.indexedDB.db = event.target.result;
var db = todoDB.indexedDB.db;

//db.onerror = function(event) {
// note.innerHTML += '<li>Error loading database.</li>';
//};

// Create an objectStore for this database
//var objectStore = db.createObjectStore("toDoList", { keyPath: "taskTitle" });

//todoDB.indexedDB.db = event.target.result;
//var db = todoDB.indexedDB.db;
//if(db.objectStoreNames.contains(tname)) {
// db.deleteObjectStore(tname);
//}
var store = db.createObjectStore(tname, { autoIncrement : true }); //, {keyPath: "timeStamp"});
//todoDB.indexedDB.getAllTodoItems();
//alert('here44');
setTimeout(populateTable,500);
};

request.onsuccess = function(event) {
// Enumerate the entire object store.
//var ul = document.createElement("ul");
todoDB.indexedDB.db = event.target.result;
var db = todoDB.indexedDB.db;
//alert(tname);
var fldnames=[], ifld;
var trans = db.transaction(tname, 'readonly');
var req = trans.objectStore(tname).openCursor();

req.onsuccess = function(event) {
var curvalue="", acurvals;
var cursor = req.result;
curcol='A';
currow=1;
var xcurcnt=0, cval="", prefbit="", found=-1, ik, proposedid='';

// If cursor is null then we've completed the enumeration - so update the DOM
if (cursor) {
//var li = document.createElement("div");
//li.textContent = "key: " + cursor.key + " => Todo text: " + cursor.value.text;
//ul.appendChild(li);
if (curcnt == 0) {
for(var field in cursor.value) {
fldnames.push(field);
if (proposed_innerHTML.indexOf("id='" + curcol + "_0' value='" + field + "'") == -1) {
if (proposed_innerHTML.indexOf("id='" + curcol + "_0'") != -1) {
acurvals=proposed_innerHTML.split("id='" + curcol + "_0' value='");
curvalue=acurvals[1].split("'")[0];
proposed_innerHTML=proposed_innerHTML.replace("<th id='data_th" + xcurcnt + "'><input title='Click to rename' type='button' id='" + curcol + "_0' value='" + curvalue + "'></input>" + "</th></tr>", "<th id='data_th" + curcnt + "'><input title='Click to rename' type='button' id='" + curcol + "_0' value='" + field + "'></input>" + "</th></tr>");
//alert("here in first");
} else {
//alert("here presecond=" + proposed_innerHTML);
proposed_innerHTML=proposed_innerHTML.replace("</th></tr>", "<th id='data_th" + xcurcnt + "'><input title='Click to rename' type='button' id='" + curcol + "_0' value='" + field + "'></input>" + "</th></tr>");
//alert("here postsecond=" + proposed_innerHTML);
}
}
lastbiggestcol=curcol;
curcol=increment(curcol);
//alert(curcol);
xcurcnt++;
}

}

curcnt++;
curcol='A';
prefbit="";
for (ifld=0; ifld<fldnames.length; ifld++) {
cval=eval("cursor.value." + fldnames[ifld]);
if (typeof(cval) == "undefined") cval="";
//alert("cursor.value." + fldnames[ifld] + "=" + eval("cursor.value." + fldnames[ifld]));
// <tr id='data_tr1'><td id='data_td_A_1'><input onblur='assess(this);' type='text' id='A_1' value=''></input></td></tr>
proposedid=curcol + "_" + curcnt;
found=-1;
for (ik=0; ik<ids.length; ik++) {
if (proposedid == ids[ik]) found=ik;
}
if (found == -1) {
vals.push(cval);
ids.push(proposedid);
} else {
vals[found]=cval;
ids[found]=proposedid;
}

if (proposed_innerHTML.indexOf("id='" + curcol + "_" + curcnt + "' value='" + cval + "'") == -1) {
if (proposed_innerHTML.indexOf("id='" + curcol + "_" + curcnt + "'") != -1) {
acurvals=proposed_innerHTML.split("id='" + curcol + "_" + curcnt + "' value='");
curvalue=acurvals[1].split("'")[0];
proposed_innerHTML=proposed_innerHTML.replace("<td id='data_td_" + curcol + "_" + curcnt + "'><input onblur='assess(this);' type='text' id='" + curcol + "_" + curcnt + "' value='" + curvalue + "'></input>" + "</td></tr>", "<td id='data_td_" + curcol + "_" + curcnt + "'><input onblur='assess(this);' type='text' id='" + curcol + "_" + curcnt + "' value='" + cval + "'></input>" + "</td></tr>");
//if (curcnt == 1 && cval == 'The') alert("herex in first " + "<td id='data_td_" + curcol + "_" + curcnt + "'><input onblur='assess(this);' type='text' id='" + curcol + "_" + curcnt + "' value='" + curvalue + "'></input>" + "</td></tr>" + " Vs " + proposed_innerHTML);
} else {
//if (curcnt == 1 && cval == 'The') alert("here prexsecond=" + proposed_innerHTML);
if (curcol == 'A') {
prefbit="</td></tr><tr id='data_tr" + curcnt + "'>";
} else {
prefbit="</td>";
}
proposed_innerHTML=proposed_innerHTML.replace("</td></tr></tbody>", prefbit + "<td id='data_td_" + curcol + "_" + curcnt + "'><input onblur='assess(this);' type='text' id='" + curcol + "_" + curcnt + "' value='" + cval + "'></input>" + "</td></tr></tbody>");
//if (curcnt == 1 && cval == 'The') alert("here postxsecond=" + proposed_innerHTML);
}
}
curcol=increment(curcol);

}

lastbiggestrow=curcnt;
cursor.continue();
}
else {
//alert("herepostxsecond=" + proposed_innerHTML);
document.getElementById("data_table").innerHTML=proposed_innerHTML;
//valuepopulate();
db.close();
todoDB = {};
indexedDB=null;
}
};
};
}
}


function ourmax(an1, an2) {
if (an1.length > an2.length) return an1;
if (an2.length > an1.length) return an2;
if (an1 == an2) return an1;
if (an1 > an2) return an1;
return an2;
}

function increment(row_col) {
var outrow_col=row_col, suffix="A", vmt=-2;
if (rownumbers.indexOf(row_col.substring(0,1)) != -1) {
outrow_col=eval(1 + eval(row_col));
} else if (colletters.indexOf(row_col.substring(eval(-1 + row_col.length),row_col.length)) == eval(-1 + colletters.length)) {
while (outrow_col != "" && colletters.indexOf(outrow_col.substring(eval(-1 + outrow_col.length),outrow_col.length)) == eval(-1 + colletters.length)) {
outrow_col=outrow_col.replace(colletters[eval(-1 + colletters.length)],"")
}
if (outrow_col != "") {
if (row_col.length == 2) {
outrow_col=increment(row_col.substring(0,1)) + "A";
} else if (row_col.substring(eval(-2 + row_col.length),eval(-1 + row_col.length)) == colletters[eval(-1 + colletters.length)]) {
vmt=-2;
suffix="A";
while (row_col.substring(eval(vmt + row_col.length),eval(vmt + 1 + row_col.length)) == colletters[eval(-1 + colletters.length)]) {
suffix+="A";
vmt--;
}
outrow_col=row_col.substring(0,eval(vmt + row_col.length)) + increment(row_col.substring(eval(vmt + row_col.length),eval(vmt + 1 + row_col.length))) + suffix;
} else {
outrow_col=row_col.substring(0,eval(-2 + row_col.length)) + increment(row_col.substring(eval(-2 + row_col.length),eval(-1 + row_col.length))) + "A";
}
} else {
outrow_col="A";
for (var i=0; i<row_col.length; i++) {
outrow_col+="A";
}
}
} else if (row_col.length > 1) {
outrow_col=row_col[0,eval(0,eval(-1 + row_col.length))] + colletters[eval(1 + colletters.indexOf(row_col.substring(eval(-1 + row_col.length),row_col.length)))];
} else {
outrow_col=colletters[eval(1 + colletters.indexOf(row_col.substring(eval(-1 + row_col.length),row_col.length)))];
}
return outrow_col;
}

function valuepopulate() {
var ot="", thiscol="", thisrow=0, onetrs="", trs="<tr id='data_tr1'><td id='data_td_A_1'><input onblur='assess(this);' type='text' id='A_1' value=''></input></td></tr>", startwith="";
while (thiscol != lastbiggestcol) {
if (thiscol == "") {
thiscol="A";
} else {
trs=trs.replace("</tr>", "<td id='data_td_" + thiscol + "_1'><input onblur='assess(this);' type='text' title='" + thiscol + "_1' id='" + thiscol + "_1' value=''></input></td>" + "</tr>");
}
thiscol=increment(thiscol);
}
if (lastbiggestcol != "A") trs=trs.replace("</tr>", "<td id='data_td_" + lastbiggestcol + "_1'><input onblur='assess(this);' type='text' id='" + lastbiggestcol + "_1' value=''></input></td>" + "</tr>");
for (var kk=1; kk<=lastbiggestrow; kk++) {
if (thisrow == 0) {
thisrow=1;
onetrs=trs;
} else {
ot=onetrs.replace("data_tr" + thisrow, "data_tr" + kk);
while (ot.indexOf("_" + thisrow) != -1) {
ot=ot.replace("_" + thisrow, "_" + kk);
}
trs+=ot;
onetrs=ot;
thisrow=kk;
}
}
document.getElementById('data_tbody').innerHTML=trs;

for (var ii=0; ii<ids.length; ii++) {
document.getElementById(ids[ii]).value=vals[ii];
}
}

function ourfocus(what) {
var it=what.split('_');
document.getElementById('column').value=it[0];
document.getElementById('row').value=it[1];
document.getElementById('val').value=document.getElementById(what).value;
document.getElementById(what).focus();
}

function reassess(itxt) {
var proposedid=document.getElementById('column').value + '_' + document.getElementById('row').value;
var found=-1;
for (var ik=0; ik<ids.length; ik++) {
if (proposedid == ids[ik]) found=ik;
}
if (found == -1) {
vals.push(itxt.value);
ids.push(proposedid);
} else {
vals[found]=itxt.value;
ids[found]=proposedid;
}
valuepopulate();
}

function ourrename(iid) {
var ii=0;
for (var jj=0; jj<colids.length; jj++) {
if (colids[jj] == iid.id) ii=jj;
}
var huhg=prompt("Optionally, please rename column " + iid.id.split('_')[0] + ".", colvalues[ii]);
if (huhg != null) {
if (huhg.trim() != "") {
colvalues[ii]=huhg;
} else {
colvalues[ii]=colids[ii].split('_')[0];
}
document.getElementById(iid.id).value=colvalues[ii];
}
}

function assess(itext) {
vals.push(itext.value);
ids.push(itext.id);

document.getElementById('bsave').style.display='block';

var it=itext.id.split('_'), jj=1, thiscol="A";
var nextcol=increment(it[0]);
var nextrow=increment(it[1]);
var startwith="<tr id='data_tr" + nextrow + "'><td id='data_td_A_" + nextrow + "'><input onblur='assess(this);' type='text' title='A_" + nextrow + "' id='A_" + nextrow + "' value=''></input></td></tr>";
if (it[0] == ourmax(it[0],lastbiggestcol)) {
colids.push(nextcol + "_0");
colvalues.push(nextcol);
document.getElementById('column').innerHTML+="<option value='" + nextcol + "'>" + nextcol + "</option>";
document.getElementById('data_tr0').innerHTML+="<th id='data_th" + it[1] + "'><input title='Click to rename' onclick='ourrename(this);' type='button' id='" + nextcol + "_0' value='" + nextcol + "'></input></th>";
lastbiggestcol=nextcol;
}
if (it[1] == ourmax(it[1],lastbiggestrow)) {
lastbiggestrow=nextrow;
document.getElementById('row').innerHTML+="<option value='" + nextrow + "'>" + nextrow + "</option>";
valuepopulate();
if (itext.value != "") ourfocus(nextcol + "_" + eval(-1 + nextrow));
} else if (it[0] == ourmax(it[0],lastbiggestcol)) {
valuepopulate();
if (itext.value != "") ourfocus(nextcol + "_" + it[1]);
} else {
valuepopulate();
if (itext.value != "") ourfocus(nextcol + "_" + it[1]);
}

}

</script>
</head>
<body onload=" showAll(); if (lastbiggestcol == 'A' && lastbiggestrow == '1') { document.getElementById('A_1').focus(); } ">
<table>
<tr>
<td style="width:60%;">Title: <input type='text' id='tablename' value=''></input> <br><br>Column: <select id='column'><option value='A'>A</option></select> Row: <select id='row'><option value='1'>1</option></select> <br><br>Value: <input type='text' id='val' value='' onblur='reassess(this);'></input></td><td><h1>Spreadsheet <br>RJM Programming - <br>November, 2016</h1></td></tr></table>
<table id='data_table'>
<thead id='data_thead'><tr id='data_tr0' style='background-color:lightgray;'><th id='data_th0'><input title='Click to rename' type='button' id='A_0' value='A'></input></th></tr>
</thead>
<tbody id='data_tbody'>
<tr id='data_tr1'><td id='data_td_A_1'><input onblur='assess(this);' type='text' id='A_1' value=''></input></td></tr>
</tbody></table>
<br><input type='button' value='Save to Database' title='Save to Database' onclick='createDatabase();' style='display:none;' id='bsave'></input>
<input type='text' style='margin-left:-6786px;width:1px;height:1px;' value=''></input>
</body>
</html>