
/******************************************************************************
@owner: SVSi
@author: Arthur Johnson
@functions: JAVASCRIPT Database querying utilities
@rowcount
@insert/update
@select
*******************************************************************************/

function dropUnitTables(websql){
	websql.executeSql('DROP TABLE nseriestxrx');
	websql.executeSql('DROP TABLE dvr_hardware');
	websql.executeSql('DROP TABLE nvr_hardware');
	websql.executeSql('DROP TABLE audiotransceivers');
	websql.executeSql('DROP TABLE wallpanels');
	websql.executeSql('DROP TABLE windowprocessors');
}

function queryJS(obj, websql){

	var opts = obj.opts;
	if (!undef(obj.data)) var data = json_encode(obj.data);
	
	//console.log('queryJS:', obj, websql);

	function echo(text){
		
		if (!undef(obj.callback)){
			if (!undef(obj.script)) 
				obj.callback(text, obj.script,
					obj.self, obj.scriptorconditional);
			else obj.callback(text);
		}
	}

	if (!activepanel) {
		//websql.executeSql('DROP TABLE IF EXISTS panels');
	}
	
	websql.executeSql('CREATE TABLE IF NOT EXISTS panels\
		(id bigint, name text, data text, datemodified bigint, buttonlib text,\
			templatelib text, servervars text)');
			
	websql.executeSql('CREATE TABLE IF NOT EXISTS nseriestxrx\
		(name text, ip text, mac text, type text)');
		
	websql.executeSql('CREATE TABLE IF NOT EXISTS dvr_hardware\
		(name text, ip text, mac text, type text)');

	websql.executeSql('CREATE TABLE IF NOT EXISTS nvr_hardware\
		(name text, ip text, mac text, type text)');
				
	websql.executeSql('CREATE TABLE IF NOT EXISTS audiotransceivers\
		(name text, ip text, mac text, type text)');
		
	websql.executeSql('CREATE TABLE IF NOT EXISTS wallpanels\
		(name text, ip text, mac text, type text)');
		
	websql.executeSql('CREATE TABLE IF NOT EXISTS windowprocessors\
		(name text, ip text, mac text, type text)');
			
	websql.executeSql("SELECT * FROM panels",
				[], function(sql, results){
				
					var len = results.rows.length, i;
					for (i = 0; i < len; i++) {
						//console.log(results.rows.item(i).data);
					}
					
				}); 

	switch (opts['query']){
		case 'addunits':
		
			console.log('addunits');
		
			var units = opts['units'];

			for (var i=0; i<units.length; i++){
				switch (units[i][1]){
					case 'N1-DEC': case 'N2-DEC': case 'N3-DEC':
					case 'N1-ENC': case 'N2-ENC': case 'N3-ENC':
					case 'LEG-DEC': case 'LEG-ENC':
						websql.executeSql("INSERT INTO nseriestxrx(name, ip, mac, type) VALUES (?,?,?,?)",
							[units[i][0], units[i][6], units[i][2], units[i][1]]);
						break;
					case 'ATR':
						websql.executeSql("INSERT INTO audiotransceivers(name, ip, mac, type) VALUES (?,?,?,?)",
							[units[i][0], units[i][6], units[i][2], units[i][1]]);
						break;
					case 'DVR':
						websql.executeSql("INSERT INTO dvr_hardware(name, ip, mac, type) VALUES (?,?,?,?)",
							[units[i][0], units[i][6], units[i][2], units[i][1]]);
						break;
					case 'WP':
						websql.executeSql("INSERT INTO windowprocessors(name, ip, mac, type) VALUES (?,?,?,?)",
							[units[i][0], units[i][6], units[i][2], units[i][1]]);
						break;
					default:
						console.log('undefined unit type.');
						break;
				}
			}
			
			echo('');
			
			break;
		case 'removeunits':
		
			console.log('removeunits');
		
			var units = opts['units'];

			for (var i=0; i<units.length; i++){
				
				websql.executeSql("DELETE FROM nseriestxrx WHERE mac = '"+units[i]+"'");
				websql.executeSql("DELETE FROM audiotransceivers WHERE mac = '"+units[i]+"'");
				websql.executeSql("DELETE FROM dvr_hardware WHERE mac = '"+units[i]+"'");
				websql.executeSql("DELETE FROM windowprocessors WHERE mac = '"+units[i]+"'");

			}
			
			echo('');
			
			break;
		case 'getunits':
		
			var callback = opts['callback'];
			
			var tlist = [];
			
			console.log('getunits');

			websql.executeSql("SELECT * FROM nseriestxrx", [], function(sql, results){

				var klist = [];
				for (var i=0; i<results.rows.length; i++) klist.push(results.rows.item(i));
				tlist.push(klist);
				
				websql.executeSql("SELECT * FROM audiotransceivers", [], function(sql, results){
					
					var klist = [];
					for (var i=0; i<results.rows.length; i++) klist.push(results.rows.item(i));
					tlist.push(klist);
					
					websql.executeSql("SELECT * FROM dvr_hardware", [], function(sql, results){
						
						var klist = [];
						for (var i=0; i<results.rows.length; i++) klist.push(results.rows.item(i));
						tlist.push(klist);
						
						websql.executeSql("SELECT * FROM windowprocessors", [], function(sql, results){
							
							var klist = [];
							for (var i=0; i<results.rows.length; i++) klist.push(results.rows.item(i));
							tlist.push(klist);
							
							callback(tlist);
						});
					});
				});
			});
			
			echo('');
			
			break;
		case 'rowcount':

			websql.executeSql("SELECT max(id) FROM panels",
				[], function(sql, results){
				
					console.log('rowcountcheck:', results.rows.item(0)['max(id)']);
					
					if (!results.rows.item(0)['max(id)']){
						echo('0');
					}
					else {
						echo(results.rows.item(0)['max(id)']);
					}
					
				}); 
			
			break;
		case 'insert':
		
			var id = opts['id'];
			
			var name = '', datemodified = '', buttonlib = '', templatelib = '', servervars = '{}';
			
			if (!undef(opts['name'])) name = opts['name'];
			if (!undef(opts['datemodified'])) datemodified = opts['datemodified'];
			if (!undef(opts['buttonlib'])) buttonlib = jsonSerialize(opts['buttonlib']);
			if (!undef(opts['templatelib'])) templatelib = jsonSerialize(opts['templatelib']);
			
			websql.executeSql("SELECT * FROM panels WHERE id = '"+id+"'", [], function(sql, results){
				if (results.rows.length < 1){
					websql.executeSql("INSERT INTO panels(id, data, name, datemodified, buttonlib, templatelib, servervars) VALUES (?,?,?,?,?,?,?)",
						[id, data, name, datemodified, buttonlib, templatelib, servervars]);
				}
				else {
					websql.executeSql("UPDATE panels SET data = ?, name = ?, datemodified = ?, buttonlib = ?, templatelib = ? WHERE id = ?",
						[data, name, datemodified, buttonlib, templatelib, id]);
				}
				echo('');
			});
			
			

			break;
		case 'updateDateModified':
		
			var id = opts['id'];
			
			datemodified = opts['datemodified'];
			
			websql.executeSql("UPDATE panels SET datemodified = '"+datemodified+"' WHERE id = '"+id+"'");
			
			echo('');

			break;
		case 'select':
		
			var id = opts['id'];
			console.log('selectswitch',id);
			
			websql.executeSql("SELECT data FROM panels", [], function(sql, results){
				console.log('select', results.rows.length);
				if (results.rows.length >= 1) echo(results.rows.item(parseInt(id-1)).data);
				else echo('');
			});

			break;
		case 'selectbyname':
		
			var name = opts['name'];
			
			websql.executeSql("SELECT data FROM panels WHERE name = '"+name+"'", [], function(sql, results){
				if (results.rows.length >= 1) echo(results.rows.item(0).data);
				else echo('');
			});

			break;
		case 'selectbuttonlibbyname':
		
			var name = opts['name'];
			
			websql.executeSql("SELECT buttonlib FROM panels WHERE name = '"+name+"'", [], function(sql, results){
				if (results.rows.length >= 1) echo(results.rows.item(0).buttonlib);
				else echo('');
			});

			break;
		case 'selecttemplatelibbyname':
		
			var name = opts['name'];
			
			websql.executeSql("SELECT templatelib FROM panels WHERE name = '"+name+"'", [], function(sql, results){
				if (results.rows.length >= 1) echo(results.rows.item(0).templatelib);
				else echo('');
			});

			break;
		case 'selectallbuttonlibs':
		
			websql.executeSql("SELECT buttonlib FROM panels", [], function(sql, results){
				
				if (results.rows.length < 1) echo('');
				else {
					var tstring = '';
					for (index=0; index<results.rows.length; index++){
					
						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).buttonlib+"-?-";
						else tstring += results.rows.item(index).buttonlib;
						
					}
					echo(tstring);
				}
				
			});

			break;
		case 'selectalltemplatelibs':
			
			websql.executeSql("SELECT templatelib FROM panels", [], function(sql, results){
				
				if (results.rows.length < 1) echo('');
				else{
					var tstring = '';
					for (index=0; index<results.rows.length; index++){
					
						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).templatelib+"-?-";
						else tstring += results.rows.item(index).templatelib;
						
					}
					echo(tstring);
				}
			});

			break;
		case 'selectrecent':
		
			var num = parseInt(opts['num']);
			
			websql.executeSql("SELECT * FROM panels ORDER BY datemodified desc LIMIT 8", [], function(sql, results){
				
				if (results.rows.length < num-1) echo('');
				else{
					echo(results.rows.item(num).data);
				}
				
			});
				
			break;
		case 'selectallrecent':
			
			websql.executeSql("SELECT * FROM panels ORDER BY datemodified desc LIMIT 8", [], function(sql, results){
				
				if (results.rows.length <= num) echo('');
				else{
					var tstring = '';
					for (index=0; index<results.rows.length; index++){

						if (results.rows.item(index).data == '[object Object]') continue;
					
						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).data+"-?-";
							else tstring += results.rows.item(index).data;
						
					} 
					console.log('tstring',tstring);
					echo(tstring);
				}
				
			});
				
			break;
		case 'selectrecentname':
		
			var num = parseInt(opts['num']);
	
			websql.executeSql("SELECT name FROM panels ORDER BY datemodified desc LIMIT 8", [], function(sql, results){
				
				if (results.rows.length <= num+1) echo('');
				else{
					echo(results.rows.item(num).name);
				}
				
			});
			
			break;
		case 'selectall':
		
			websql.executeSql("SELECT * FROM panels ORDER BY id desc", [], function(sql, results){
				
				if (results.rows.length == 0) echo('');
				else{
					var tstring = '';
					for (index=0; index<results.rows.length; index++){

						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).data+"-?-";
							else tstring += results.rows.item(index).data;
						
					} 
					echo(tstring);
				}
				
			});

			break;
		case 'selectallnames':
		
			websql.executeSql("SELECT name FROM panels ORDER BY id desc", [], function(sql, results){
				
				if (results.rows.length == 0) echo('');
				else{
					var tstring = '';
					for (index=0; index<results.rows.length; index++){

						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).name+"-?-";
						else tstring += results.rows.item(index).name;
						
					} 
					echo(tstring);
				}
				
			});

			break;
		case 'delete':
		
			var id = opts['id'];
	
			websql.executeSql("DELETE FROM panels WHERE id = '"+id+"'");
			
			echo('');

			break;
		case 'deletebyname':
		
			var name = opts['name'];
	
			websql.executeSql("DELETE FROM panels WHERE name = '"+name+"'");
			
			echo('');

			break;
		case 'checkname':
		
			var name = opts['name'];
			
			websql.executeSql("SELECT * FROM panels WHERE name = '"+name+"'", [],  function(sql, results){
				if (results.rows.length == 0) echo('0');
				else echo('1');
			});

			break;
		case 'prequery':
		
			websql.executeSql("SELECT name FROM panels ORDER BY datemodified desc", [], function(sql, results){
				if (results.rows.length == 0) return '';
				else {
					var tstring = '';
					for (index=0; index<results.rows.length; index++){

						if (index!=(results.rows.length-1)) tstring += results.rows.item(index).name+"-?-";
						else tstring += results.rows.item(index).name;
					
					}
					window.projectlist = tstring;
				}
			});

			break;
		default:
	}

}