var custom_queries = [[],[],[],[],[]];
var custom_query_modes = ['AND', 'AND', 'AND', 'AND', 'AND'];
var custom_query_count = 0;
var custom_query_in_use = false;

/////////////////////////////////////////////////////////////////////////////////////////

function initQueries()
{
	custom_query_count = getCookieInt('custom_query_count', 1);
	var qs = getCookie('custom_queries', "");
	try {
		if(qs != "") 
			custom_queries = JSON.parse(qs);
	}
	catch(err) {
		console.log('Error: '+err);
		custom_queries = [[],[],[],[],[]];
	}
	var qsm = getCookie('custom_query_modes', "");
	try {
		if(qsm != "") 
			custom_query_modes = JSON.parse(qsm);
	}
	catch(err) {
		console.log('Error'+err);
		custom_query_modes = ['AND', 'AND', 'AND', 'AND', 'AND'];
	}
	initCustomSQLTab();
	initCustomQueryButtons();
}

/////////////////////////////////////////////////////////////////////////////////////////

function init_panel_querybuilder(cqid)
{
	var index = parseInt(cqid);
	positionCustomQueriesWindow();
	initQueryBuilder(index);
	$('#qb-index-text').html(index+1);
	showPanel("panel-querybuilder", 'left');		
	return true;
}

/////////////////////////////////////////////////////////////////////////////////////////

function initCustomSQLTab()
{
	$('#custom-queries').html('');
	var cqcount = custom_query_count;
	var cqdata = "";
	cqcount = cqcount > 5 ? 5 : cqcount;
	for(var i = 0; i < cqcount; i++) {
		var cqtext = "";
		if(JSON.stringify(custom_queries[i]).length)
			cqtext = getLabelForQuery(custom_queries[i]);
		cqdata += '<div id="cq-' + i + '" class="cq-query">' + cqtext + '</div>';
		if(i > 0)
			cqdata += '<div class="float-left"><a class="btn btn-sm btn-default btn-menu btn-nav delete-cq" id="delete-cq-' + i + '"><img src="lib/layouts/images/menu/remove.png"></a></div>';
		cqdata += '<div class="float-left"><a class="btn btn-sm btn-default btn-menu btn-nav edit-cq" id="edit-cq-' + i + '"><img src="lib/layouts/images/menu/edit.png"></a></div>';
		if(i == (cqcount-1) && i < 4) 
			cqdata += '<div class="float-right"><a class="btn btn-sm btn-default btn-menu btn-nav edit-cq" id="edit-cq-' + (i+1) + '"><img src="lib/layouts/images/menu/addnew.png"></a></div>';
		if( i < (cqcount-1)){
			cqdata += '<div class="float-right">';
			cqdata += '<select id="cq-combine-' + i + '" name="cq-bool-combine" class="form-control pretty input-sm cq-combine">';
			cqdata += '<option value="AND">and</option>';
			cqdata += '<option value="OR">or</option>';
			cqdata += '<option value="-">ignore</option>';
			cqdata += '</select>';
			cqdata += '</div>'
		}
		cqdata += '<div style="clear: both;"></div>';
	}
	if(cqcount)
		initQueryBuilder(0);
	$('#custom-queries').html(cqdata);
	for(i = 0; i < 5; i++) 
		$('#cq-combine-' + i).val(custom_query_modes[i]);
}

/////////////////////////////////////////////////////////////////////////////////////////

function initQueryBuilder(index)
{
	var datastr = get_ajax_datastr("list", "category", "user");
	datastr += "&id=0";
	ajax_call(datastr, ajax_init_catlist, [index]);
}

/////////////////////////////////////////////////////////////////////////////////////////

function ajax_init_catlist(x, jqXHR, textStatus, params)
{
	if(x != null) {
		var cbdata = '';
		if(x.status == 'ok') {
			var count = x.ids.length;
			for(var i = 0; i < count; i++) {
				var name = x.ids[i].name;
				var id = x.ids[i].id;
				if(id > 1) {
					cbdata += '<option value="' + id + '">' + name + '</option>';
				}
			}
			$('#qb_catlist').html(cbdata);
			$('#qb_catlist').multipleSelect('destroy')
			$('#qb_catlist').multipleSelect();
			
			datastr = get_ajax_datastr("list", "domain", "columns");
			datastr += "&group=type";
			ajax_call(datastr, ajax_fill_query_columns, params);
		}
	}
}

/////////////////////////////////////////////////////////////////////////////////////////

function get_selected_category_ids()
{
	var selected = $('#qb_catlist').multipleSelect('getSelects');
	var cids = []
	for(var i = 0; i < selected.length; i++) {
		var s = parseInt(selected[i]);
		cids.push(s);
	}
	return cids;
}

/////////////////////////////////////////////////////////////////////////////////////////

function get_selected_category_names()
{
	var selected = $('#qb_catlist').multipleSelect('getSelects', 'text');
	var cids = []
	for(var i = 0; i < selected.length; i++) {
		var s = parseInt(selected[i]);
		cids.push(s);
	}
	return cids;
}

/////////////////////////////////////////////////////////////////////////////////////////

function ajax_fill_query_columns(x, jqXHR, textStatus, params)
{
	if(x != null) {
		var cbdata = '';
		var count = x.columns.string.length;
		for(var i = 0; i < count; i++) {
			var name = x.columns.string[i]['name'];
			var label = x.columns.string[i]['label'];
			cbdata += '<option value=d.' + name + '>' + label + '</option>';
		}
		$('#qb-text-columns').html(cbdata);
		cbdata = '';
		
		count = x.columns.integer.length;
		for(var i = 0; i < count; i++) {
			var name = x.columns.integer[i]['name'];
			if(name != "days_to_expiry") {
				var label = x.columns.integer[i]['label'];
				cbdata += '<option value=d.' + name + '>' + label + '</option>';
			}
		}
		$('#qb-number-columns').html(cbdata);
		cbdata = '';
		
		count = x.columns.date.length;
		for(var i = 0; i < count; i++) {
			var name = x.columns.date[i]['name'];
			var label = x.columns.date[i]['label'];
			cbdata += '<option value=d.' + name + '>' + label + '</option>';
		}
		count = x.columns.datetime.length;
		for(var i = 0; i < count; i++) {
			var name = x.columns.datetime[i]['name'];
			var label = x.columns.datetime[i]['label'];
			cbdata += '<option value=d.' + name + '>' + label + '</option>';
		}
		$('#qb-date-columns').html(cbdata);
		cbdata = '';
		
		count = x.columns.boolean.length;
		for(var i = 0; i < count; i++) {
			var name = x.columns.boolean[i]['name'];
			var label = x.columns.boolean[i]['label'];
			cbdata += '<option value=d.' + name + '>' + label + '</option>';
		}
		$('#qb-bool-columns').html(cbdata);
		cbdata = '';
		var index = params[0];
		custom_queries[index]['qb-index'] = index;
		$('#qb-form').values(custom_queries[index]);
		$('#qb-index').val(index);
		
		var options = custom_queries[index];
		var cids = [];
		if("qb_catlist" in options) {
			var keys = options['qb_catlist'][0];
			for(var i = 0; i < keys.length; i++) {
				var cid = parseInt(keys[i]);
				cids.push(cid);
			}
		}
		$('#qb_catlist').multipleSelect('setSelects', cids);
	}
}

/////////////////////////////////////////////////////////////////////

function querybuilder_submit()
{
	getCustomQuery();
	hideAllPanels();
	cq_enable();
}

/////////////////////////////////////////////////////////////////////

function getSQLParam(entry, param)
{
	if(entry == "BW")
		return param + "%";
	else if(entry == "EW")
		return "%" + param;
	else if(entry == "CN")
		return "%" + param + "%";
	else
		return param;
}

/////////////////////////////////////////////////////////////////////

function getCustomQueryData()
{
	var sqlarray = [], params = [];
	var cqcount = custom_query_count;
	cqcount = cqcount > 5 ? 5 : cqcount;
	for(var i = 0; i < cqcount; i++) {
		var options = custom_queries[i];
		var sql = "";
		if(options['qb-catsel-mode'] != "IGNORE") {
			if(typeof options['qb_catlist'] != "undefined") {
				var keys = options['qb_catlist'][0];
				var cids = "";
				for(var j = 0; j < keys.length; j++) {
					var cid = parseInt(keys[j]);
					if(cids != "")
						cids += " OR ";
					cids += "c.cid = " + cid;
				}
				if(keys.length)
					sql += "(d.sid " + options['qb-catsel-mode'] + " (SELECT DISTINCT c.did from [CATCONNTABLE] c WHERE c.did=d.sid AND (" + cids + ")))"; 
			}
		}

		if(options['qb-text-combine'] != "IGNORE") {
			if( (options['qb-text-parameter'] != "" && typeof options['qb-text-parameter'] != "undefined") ||
				(options['qb-text-comp-mode'] == "IS NULL" || options['qb-text-comp-mode'] == "IS NOT NULL")) {
				if(sql != "")
					sql += " " + options['qb-text-combine'] + " ";
				sql += " " + options['qb-text-columns'] + " ";
				sql += " " + options['qb-text-comp-mode'] + " ";
				if(options['qb-text-parameter'] != "" && typeof options['qb-text-parameter'] != "undefined") {
					sql += " ? ";
					params.push(getSQLParam(options['qb-text-comp-mode'], options['qb-text-parameter']));
				}
			}
		}

		if(options['qb-date-combine'] != "IGNORE") {
			if((options['qb-date-comp-mode'] == "IS NULL" || options['qb-date-comp-mode'] == "IS NOT NULL") ||
				(options['qb-date-parameter'] != "" && typeof options['qb-date-parameter'] != "undefined")) {
				if(sql != "")
					sql += " " + options['qb-date-combine'] + " ";
				sql += " " + options['qb-date-columns'] + " ";
				sql += " " + options['qb-date-comp-mode'] + " ";
				if(options['qb-date-parameter'] != "" && typeof options['qb-date-parameter'] != "undefined") {
					sql += " ? ";
					params.push(getSQLParam(options['qb-date-comp-mode'], options['qb-date-parameter']));
				}
			}
		}

		if(options['qb-number-combine'] != "IGNORE") {
			if((options['qb-number-comp-mode'] == "IS NULL" || options['qb-number-comp-mode'] == "IS NOT NULL") ||
				(options['qb-number-parameter'] != "" && typeof options['qb-number-parameter'] != "undefined")) {
				if(sql != "")
					sql += " " + options['qb-number-combine'] + " ";
				sql += " " + options['qb-number-columns'] + " ";
				sql += " " + options['qb-number-comp-mode'] + " ";
				if(options['qb-number-parameter'] != "" && typeof options['qb-number-parameter'] != "undefined") {
					sql += " ? ";
					params.push(getSQLParam(options['qb-number-comp-mode'], options['qb-number-parameter']));
				}
			}
		}
		
		if(options['qb-bool-combine'] != "IGNORE") {
			if(sql != "")
				sql += " " + options['qb-bool-combine'] + " ";
			sql += " " + options['qb-bool-columns'] + " ";
			if(options['qb-bool-comp-mode'] == "EQ 1") {
				sql += " EQ ? ";
				params.push("1");
			}
			else if(options['qb-bool-comp-mode'] == "EQ 0") {
				sql += " EQ ? ";
				params.push("0");
			}
			else
				sql += " " + options['qb-bool-comp-mode'] + " ";
		}
		
		sql = sql.trim();
		sqlarray.push(sql);
	}
	
	var sql = "";
	for(i = 0; i < sqlarray.length; i++) {
		if(sqlarray[i] != "") {
			if(i > 0 && sql != "")
				sql += " " + custom_query_modes[i-1] + " ";
			sql += "(" + sqlarray[i] + ") ";
		}
	}
	return "query=" + encodeURIComponent(sql.trim()) + "&params=" + encodeURIComponent(params.join("\n"));
}

/////////////////////////////////////////////////////////////////////

function getCustomQuery()
{
	var options = $('#qb-form').values();
	var index = parseInt(options['qb-index']);
	if(index >= 0 && index < 5) {
		custom_queries[index] = options;
		if(custom_query_count < (index+1)) {
			custom_query_count = index+1;
		}
		$.cookie('custom_query_count', custom_query_count);
		$.cookie('custom_queries', JSON.stringify(custom_queries));
	}
	initCustomSQLTab();
	initCustomQueryButtons();
}

/////////////////////////////////////////////////////////////////////

function initCustomQueryButtons()
{
	$(".edit-cq").off('click');
	$(".edit-cq").click(function(event){
		event.preventDefault();
		if($('#panel-querybuilder').is(':visible')) 
			hidePanelWithId('panel-querybuilder');
		else {
			var cpid = $(this).attr('id');
			cpid = cpid.replace("edit-cq-", "");
			init_panel_querybuilder(cpid);
		}
	});
	
	$(".delete-cq").off('click');
	$(".delete-cq").click(function(event){
		event.preventDefault();
		var cpid = $(this).attr('id');
		var index = parseInt(cpid.replace("delete-cq-", ""));
		if(index > 0 && index < 5) {
			custom_queries[index] = [];
			custom_query_modes[index] = ['AND'];
			for(var i = index; i < 5;  i++) {
				if((index+1) < 5) {
					custom_queries[index] = custom_queries[index+1];
					custom_query_modes[index] = custom_query_modes[index+1];
				}
			}
			custom_query_count--;
			$.cookie('custom_query_count', custom_query_count);
			$.cookie('custom_queries', JSON.stringify(custom_queries));
			initCustomSQLTab();
			initCustomQueryButtons();
		}
	});
	
	$(".cq-combine").off('change');
	$(".cq-combine").change(function(event){
		event.preventDefault();
		var cpid = $(this).attr('id');
		var index = parseInt(cpid.replace("cq-combine-", ""));
		custom_query_modes[index] = $(this).val();
		$.cookie('custom_query_modes', JSON.stringify(custom_query_modes));
	});
}

function getLabelForQuery(options)
{
	var label = "";
	var cats = [];
	if(options['qb-catsel-mode'] != "IGNORE") {
		if(typeof options['qb_catlist'] != "undefined") {
			var keys = options['qb_catlist'][0];
			for(var i = 0; i < keys.length; i++) {
				var cid = parseInt(keys[i]);
				if(typeof categoryNames[cid] != "undefined")
					cats.push(categoryNames[cid]);
			}
		}
		label = (options['qb-catsel-mode'] == "IN" ? "Belong to " : "Not Belong To ");
		if(cats.length)
			label += '[' + cats.join(",") + '] ';
		else
			label = "";
	}
	
	if(options['qb-text-combine'] != "IGNORE") {
		if(options['qb-text-comp-mode'] == "IS NULL" || options['qb-text-comp-mode'] == "IS NOT NULL") {
			label += options['qb-text-combine'] + ' ';
			label += options['qb-text-columns'] + ' ';
			label += options['qb-text-comp-mode'] + ' ';
		}
		else if(options['qb-text-parameter'] != "" && typeof options['qb-text-parameter'] != "undefined") {
			label += options['qb-text-combine'] + ' ';
			label += options['qb-text-columns'] + ' ';
			label += options['qb-text-comp-mode'] + ' ';
			label += options['qb-text-parameter'] + ' ';
		}
	}
	
	if(options['qb-date-combine'] != "IGNORE") {
		if(options['qb-date-comp-mode'] == "IS NULL" || options['qb-date-comp-mode'] == "IS NOT NULL") {
			label += options['qb-date-combine'] + ' ';
			label += options['qb-date-columns'] + ' ';
			label += options['qb-date-comp-mode'] + ' ';
		}
		else if(options['qb-date-parameter'] != "" && typeof options['qb-date-parameter'] != "undefined") {
			label += options['qb-date-combine'] + ' ';
			label += options['qb-date-columns'] + ' ';
			label += options['qb-date-comp-mode'] + ' ';
			label += options['qb-date-parameter'] + ' ';
		}
	}
	
	if(options['qb-number-combine'] != "IGNORE") {
		if(options['qb-number-comp-mode'] == "IS NULL" || options['qb-number-comp-mode'] == "IS NOT NULL") {
			label += options['qb-number-combine'] + ' ';
			label += options['qb-number-columns'] + ' ';
			label += options['qb-number-comp-mode'] + ' ';
		}
		else if(options['qb-number-parameter'] != "" && typeof options['qb-number-parameter'] != "undefined") {
			label += options['qb-number-combine'] + ' ';
			label += options['qb-number-columns'] + ' ';
			label += options['qb-number-comp-mode'] + ' ';
			label += options['qb-number-parameter'] + ' ';
		}
	}
	
	if(options['qb-bool-combine'] != "IGNORE") {
		if(typeof options['qb-bool-combine'] != "undefined") {
			label += options['qb-bool-combine'] + ' ';
			label += options['qb-bool-columns'] + ' ';
			label += options['qb-bool-comp-mode'] + ' ';
		}
	}
	
	// startsWith
	if(label.indexOf("AND ") == 0)
		label = label.substr(4);
	// endsWith
	if(label.indexOf(" AND ", label.length - 5) !== -1)
		label = label.substr(0, label.length-5);
	return label;
}

/////////////////////////////////////////////////////////////////////////////////////////

function cq_enable()
{
	selectCustomQuery(true);
	showQueryPreview();
}

/////////////////////////////////////////////////////////////////////////////////////////

function cq_disable()
{
	selectCustomQuery(false);
}

/////////////////////////////////////////////////////////////////////////////////////////

function cq_save_as_autoquery()
{
	var name = $("#cq-query-name").val();
	var querydata = getCustomQueryData();
	querydata += "&name=" + encodeURIComponent(name);
	var datastr = get_ajax_datastr("grid", "query", "add");
	datastr += "&" + querydata;
	ajax_call(datastr, ajax_create_autoquery, null);
}

/////////////////////////////////////////////////////////////////////////////////////////

function ajax_create_autoquery(x, jqXHR, textStatus, params)
{
	if(x != null) {
		if(x.status == 'ok') {
			$("#domainquerytable").trigger("reloadGrid"); 
		}
	}
}

/////////////////////////////////////////////////////////////////////

function showQueryPreview()
{
	var qdata = decodeURIComponent(getCustomQueryData());
	qdata = replaceAll(qdata, "query=", "");
	var p = qdata.split("&params=");
	$('#cq-query-preview').val(p[0]);
	$('#cq-params-preview').val(p[1]);
}

/////////////////////////////////////////////////////////////////////////////////////////
