<?php ob_start(); ?>
<?php

//if the pathResFolder exists I get it, else I set the default
$resPath = "../../res/";
if(!isset($resPath) || trim($resPath)===''){
    $resPath = "../res/";
}

include $resPath ."x5engine.php";

function getColsWithTypeAndKey($db) {
	$columns = array();
	$cols = $db->tableColumns('');
	foreach($cols as $col) {
		$columns[$col['Field']] = array(
			'key' => $col['Key']
		);
		$type = explode('(', $col['Type']);
		$type = $type[0];
		switch($type) {
			case 'int':
			case 'tinyint':
			case 'smallint':
			case 'mediumint':
			case 'bigint':
			case 'float':
			case 'double':
			case 'decimal': $columns[$col['Field']]['type'] = 'number'; break;
			case 'date': $columns[$col['Field']]['type'] = 'date'; break;
			/*case 'datetime':
			case 'timestamp':
			case 'time':
			case 'year': */
			default: $columns[$col['Field']]['type'] = 'text'; break;
		}
	}
	return $columns;
}

function listRecords($db, $get, $post) {
	$output = array();
	
	$where = null;
	if(isset($post['filter'])) {
		$cols = getColsWithTypeAndKey($db);
		$filters = array();
		$hidden = explode(',', "");
		for($i = 0; $i < count($hidden); $i++) {
			$hidden[$i] = trim($hidden[$i]);
		}
		foreach($cols as $col => $attr) {
			if(array_search($col, $hidden) === false) {
				if($attr['type'] != 'number')
					$filters[] = '`' . $col . "` like '%" . $db->escapeString($post['filter']) . "%'";
				else if(is_numeric($post['filter']))
					$filters[] = '`' . $col . "` = " . $post['filter'];
			}
		}
		if(count($filters) > 0){
			$where = implode(' or ', $filters);
		}
		else{ //Entered a filter but types not match(eg. only numeric cols and string filter). Need 0 results
			$where = "FALSE";
		}	
	}

	$count = $db->select(array(
		'select' => array('fn' => 'count', 'as' => 'rowscount'),
		'from' => '',
		'where_flat' => $where
	));
	if($count === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile ottenere il conteggio delle righe';
		echo json_encode($output);
		$db->closeConnection();
		exit();
	}
	$rows = $count[0]['rowscount'];

	$select = array('from' => '', 'where_flat' => $where);
	if(isset($get['jtSorting'])) {
		list($field, $direction) = explode(' ', $get['jtSorting']);
		$select['order_by'] = array($field => $direction);
	}
	if(isset($get['jtStartIndex']) && isset($get['jtPageSize']) && is_numeric($get['jtStartIndex']) && is_numeric($get['jtPageSize']))
		$select['limit'] = array($get['jtStartIndex'], $get['jtPageSize']);

	$results = $db->select($select);
	if($results === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile eseguire la query';
		echo json_encode($output);
		$db->closeConnection();
		exit();
	}
	$output['Result'] = 'OK';
	$output['Records'] = $results;
	$output['TotalRecordCount'] = $rows;

	echo json_encode($output);
}

function createRecord($db, $data) {
	return;
	$output = array();
	$cols = getColsWithTypeAndKey($db);
	$values = array();
	foreach($data as $field => $value) {
		if(isset($cols[$field])) {
			if($cols[$field]['type'] != 'number'|| is_numeric($value))
				$values[$field] = $value;
			else {
				$output['Result'] = 'ERROR';
				$output['Message'] = str_replace('{0}', $field, 'Il valore del campo {0} non è valido');
				echo json_encode($output);
				exit();
			}
		}
	}
	
	$result = $db->insert(array('into'=>'', 'values' => $values));
	if($result === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile eseguire la query';
		echo json_encode($output);
		exit();
	}
	$result = $db->select(array('from'=>'', 'where' => $values));
	if($result === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile trovare il record inserito. Ricarica la pagina';
		echo json_encode($output);
		exit();	
	}
	$output['Result'] = 'OK';
	$output['Record'] = $result[0];
	echo json_encode($output);
}

function updateRecord($db, $data) {
	return;
	$output = array();
	$cols = getColsWithTypeAndKey($db);
	$wheres = array();
	$values = array();
	foreach($data as $field => $value) {
		if(isset($cols[$field])) {
			if($cols[$field]['type'] == 'number' && !is_numeric($value)) {
				$output['Result'] = 'ERROR';
				$output['Message'] = str_replace('{0}', $field, 'Il valore del campo {0} non è valido');
				echo json_encode($output);
				exit();
			}
			if($cols[$field]['key'] == 'PRI' || $cols[$field]['key'] == 'MUL')
				$wheres[$field] = $value;
			else
				$values[$field] = $value;
		}
	}
	$result = false;
	if(count($wheres)){
		$query = array(
			'update'=> '', 'set' => $values, 
			'from'=>'',
			'where' => $wheres
		);
		$result = $db->update($query);
	}
	if($result === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile eseguire la query';
		echo json_encode($output);
		exit();
	}
	$result = $db->select($query);
	if($result === false) {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile trovare il record aggiornato. Ricarica la pagina';
		echo json_encode($output);
		exit();	
	}
	$output['Result'] = 'OK';
	$output['Record'] = $result[0];
	echo json_encode($output);
}

function deleteRecord($db, $data) {
	return;
	$output = array();
	$cols = getColsWithTypeAndKey($db);
	$filter = array();
	foreach($data as $field => $value) {
		if(isset($cols[$field])) {
			if($cols[$field]['type'] != 'number'|| is_numeric($value))
				$filter[$field] = $value;
		}
	}
	if(count($filter) > 0) {
		$result = $db->delete(array('from'=>'', 'where'=> $filter));
		if($result !== false)
			$output['Result'] = 'OK';
		else {
			$output['Result'] = 'ERROR';
			$output['Message'] = 'Impossibile eseguire la query';
		}
	}
	else {
		$output['Result'] = 'ERROR';
		$output['Message'] = 'Impossibile eliminare il record';
	}
	echo json_encode($output);
}

function exportCsv($db, $get) {
	return;
	header("Content-Type: text/plain");

	$where = null;
	$cols = getColsWithTypeAndKey($db);
	$filters = array();
	$select_cols = array();
	$hidden = explode(',', "");
	for($i = 0; $i < count($hidden); $i++) {
		$hidden[$i] = trim($hidden[$i]);
	}
	foreach($cols as $col => $attr) {
		if(array_search($col, $hidden) === false) {
			$select_cols[] = $col;
			if(isset($get['filter'])) {
				if($attr['type'] != 'number')
					$filters[] = "`" . $col . "` like '%" . $db->escapeString($get['filter']) . "%'";
				else if(is_numeric($get['filter']))
					$filters[] = "`" . $col . "` = " . $get['filter'];
			}
		}
	}
	echo '"' . implode('";"', $select_cols) . "\"\n";
	if(isset($get['filter'])) {
		if(count($filters) > 0){
			$where = implode(' or ', $filters);
		}
		else{ //Entered a filter but types not match(eg. only numeric cols and string filter). Need 0 results
			$where = "FALSE";
		}
	}
	
	$results = $db->select(array('select' => $select_cols, 'from' => '', 'where_flat' => $where));
	if($results === false) {
		echo 'Impossibile eseguire la query';
		$db->closeConnection();
		exit();
	}

	header('Content-Type: text/csv; charset=utf-8');
	header('Content-Disposition: attachment; filename=export.csv');

	foreach($results as $row) {
		$values = array();
		foreach($row as $key => $value) {
			$values[] = str_replace('"', '\"', $value);
		}
		echo '"' . implode('";"', $values) . "\"\n";
	}
}

$db_data = array('host' => '',
								 'database' => '',
								 'user' => '',
								 'password' => '',
								 'table_prefix' => '');
$db = ImDb::from_db_data($db_data);

if (isset($_GET['action'])) {
	switch($_GET['action']) {
		case 'list': listRecords($db, $_GET, $_POST); break;
		case 'create': createRecord($db, $_POST); break;
		case 'update': updateRecord($db, $_POST); break;
		case 'delete': deleteRecord($db, $_POST); break;
		case 'export': exportCsv($db, $_GET); break;
		default: break;
	}
} else {
	header("Content-Type: application/javascript");
?>
$(document).ready(function () {
    $('#pluginAppObj_8_03_jtable').jtable({
    	title: 'Database Viewer',
        paging: true,
        pageSize: 10,
        pageSizeChangeArea: false,
        sorting: true,
        actions: {
            listAction: 'pluginAppObj/pluginAppObj_8_03/dbviewer.php?action=list',
			
        },
        fields: {
<?php
	$hidden = explode(',', "");
	for($i = 0; $i < count($hidden); $i++) {
		$hidden[$i] = trim($hidden[$i]);
	}
	$cols = getColsWithTypeAndKey($db);
	foreach ($cols as $col => $attr) {
		$show = (array_search($col, $hidden) === false);
?>
			'<?php echo str_replace("'", "\\'", $col); ?>': {
				key: <?php echo ($attr['key'] == 'PRI' || $attr['key'] == 'MUL' ? "true" : "false"); ?>,
				create: <?php echo ($show ? "true" : "false"); ?>,
				edit: <?php echo (($show && !($attr['key'] == 'PRI' || $attr['key'] == 'MUL')) ? "true" : "false"); ?>,
				list: <?php echo ($show ? "true" : "false"); ?>,
				title: '<?php echo str_replace("\'", "\\\'", $col); ?>',
				<?php echo ($attr['type'] == 'date' ? "type: 'date'\n" : "\n"); ?>
			},
<?php
	}
?>
        },
        formCreated : function(event, data) {
        	if(data.formType == 'error')
        		return;
        	var div = data.form[0].parentElement;
        	if(data.formType != 'delete')
        		div = div.parentElement;
        	$(div).addClass('pluginAppObj_8_03_dialog');
        },
        messages: {
		    serverCommunicationError: 'Si è verificato un errore di comunicazione con il server.',
		    loadingMessage: 'Caricamento dei record...',
		    noDataAvailable: 'Non ci sono dati disponibili!',
		    addNewRecord: 'Crea un nuovo record',
		    editRecord: 'Modifica record',
		    areYouSure: 'Sei sicuro?',
		    deleteConfirmation: 'Il record verrà eliminato. Sei sicuro?',
		    save: 'Salva',
		    saving: 'Salvataggio',
		    cancel: 'Annulla',
		    deleteText: 'Elimina',
		    deleting: 'Eliminazione',
		    error: 'Errore',
		    close: 'Chiudi',
		    cannotLoadOptionsFor: 'Opzioni non disponibili per il campo {0}',
		    pagingInfo: 'Record {0}-{1} su {2}',
		    pageSizeChangeLabel: 'Righe per pagina',
		    gotoPageLabel: 'Vai alla pagina',
		    canNotDeletedRecords: 'Impossibile eliminare il record {0} di {1}!',
		    deleteProggress: 'Eliminazione di {0} di {1} record in corso...'
		}
    });

    $('#pluginAppObj_8_03_filter_button').click(function (e) {
        e.preventDefault();
        var filter = $('#pluginAppObj_8_03_filter_field').val();
        if(filter == undefined || filter == ""){
        	var params = {};
        }
        else{
        	var params = {filter : filter};
        }
        $('#pluginAppObj_8_03_jtable').jtable('load', params);
    });

    $('#pluginAppObj_8_03_csv_button').click(function (e) {
        e.preventDefault();
        var filter = $('#pluginAppObj_8_03_filter_field').val();
        if(filter == undefined || filter == ""){
        	filter = '';
        }
        else{
        	filter = '&filter=' + filter;
        }
        location.href = 'pluginAppObj/pluginAppObj_8_03/dbviewer.php?action=export' + filter;
    });

    $('#pluginAppObj_8_03_jtable').jtable('load');
});
<?php
}
ob_flush();
$db->closeConnection();