sql - texto - que es una tabla matriz

¿Cuáles son las mejores formas de implementar un informe dinámico de matriz utilizando APEX?

Question

Necesito completar esta tarea usando el marco Oracle Application Express.

Digamos que tenemos una consulta así:

select
col1,
col2,
val1,
val2,
val3,
val4,
val5,
val6,
val7,
val8,
val9,
val10,
val11
from table(mega_function(city => ?, format => ?, percent => ?, days => ?));

Y esta consulta devuelve algo como esto (se muestra en formato CSV):

col1;col2;val1;val2;val3;val4;val5;val6;val7;val8;val9;val10;val11
S2;C1;32000;120;"15:38:28";1450;120;1500;1200;31000;120;32600;300
S1;C1;28700;120;"15:35:01";150;120;1500;1800;2700;60;28900;120
S1;C2;27000;240;"14:44:23";0;1500;240;1200;25500;60;null;null

Para simplificar, la consulta se basa en una función canalizada que toma algunos parámetros y devuelve un conjunto de valores para diferentes pares de valores de las dos primeras columnas col1;col2 .

Lo que necesito implementar es un informe de matriz donde los valores de col1 se usan como filas del informe y los valores de col2 como columnas. En la intersección hay celdas que contienen un conjunto de valores para el par con algunos formatos y estilos aplicados. Lo que también se necesita es clasificar por filas (que debe ordenar columnas por valores de la columna ''val1'').

O si mostramos las necesidades anteriores en una maqueta:

Entonces, la pregunta es: ¿cuáles son las mejores prácticas para implementar dicho informe de matriz con algunos estilos de interacción y personalizados?

Lo que ya he tratado de investigar:

• Interactive Report Pivot functional ( https://docs.oracle.com/cd/E71588_01/AEEUG/managing-pivot-reports.htm#AEEUG29137 ) - carece de personalización, funciona mal con muchos valores, especialmente cuando no son números.
• Informe clásico basado en la función: he implementado la función PL / SQL que devuelve la consulta dinámica PIVOT SQL, en las propiedades del informe Use Generic Column Names establecidos en Yes (para analizar la consulta solo en tiempo de ejecución) y para los encabezados del informe Utilicé otra función PL / SQL , que genera una cadena en el formato heading1:headning2:...:headingN . La solución funciona (puede consultarla aquí - https://apex.oracle.com/pls/apex/f?p=132832:2 ), pero necesito actualizar el informe dinámicamente cada, digamos, 5 segundos, y será una mierda en términos de rendimiento (el SQL dinámico siempre es malo y no se puede administrar si hablamos de planes de ejecución). Además, esta solución no encaja, porque los encabezados no están coordinados con los datos (de hecho, utilicé el order by col1 en las consultas en las funciones PL / SQL para que los títulos estén en su lugar) y no sé cómo hacer filas ordenable aquí.
• Región de contenido dinámico PL / SQL: no he intentado codificar algo aquí, pero me doy cuenta de que aquí es posible hacer cualquier cosa con el paquete HTP y API APEX. Lo complicado es que esta solución es bastante compleja, tendré que implementar toda la lógica del informe ''desde cero'' y creo que hay una manera mejor y más fácil de tener éxito en la tarea, que no sé.

Answer 1

En función de sus requisitos, es probable que APEX no sea la herramienta adecuada, ya que estará infinitamente limitado por las bibliotecas y capacidades subyacentes de JQuery, etc.

No me atrevería a desarrollar dicha aplicación en APEX. En mi opinión, los requisitos están muy lejos de la tecnología ofrecida en APEX.

Por otro lado, habla sobre el rendimiento de DB. Es bueno que lo tenga en cuenta desde la fase de planificación, pero es malo porque ya se está limitando ... hay opciones ... por ejemplo, obtener una opción en memoria en la base de datos y / almacenar en caché los resultados de una vista Materializada. Fijar las tablas en la memoria.

Answer 2

Estás en lo correcto. Solo proporcioné una opinión basada en mi comprensión temprana de su descripción de requisitos. Ahora que lo leí con más cuidado, cambié de opinión porque me di cuenta de que no es una matriz difícil lo que tiene ... De hecho, tengo muchos de esos. .

No conozco las mejores prácticas per se pero puedo compartir con usted lo que he hecho con requisitos similares:

Para los informes basados ​​en matriz, prefiero los Informes clásicos, coloco mis criterios de filtrado en la sección del encabezado (al igual que en su simulacro) y, según la selección del usuario, la información cambia muy bien. Así es como manejo el filtrado y la clasificación.

La parte más difícil (IMO) con sus requisitos es la verificación de celdas para fines de exportación. Debería poder activar un control de alternancia dinámicamente en su consulta y con un poco de AJAX debería ser capaz de recoger los elegidos para las exportaciones .

Answer 3

Lamentablemente, ninguna de las opciones que mencioné en la pregunta cumplía con todos los requisitos debido a las condiciones en las que se realizará el informe:

• Los datos se deben actualizar dinámicamente cada, digamos, 5 segundos.
• El estado del informe debe guardarse sobre las actualizaciones de datos.
• El número de columnas del informe es variable (la definición de columnas se proporciona con datos), el número de filas también es variable. El informe debe tener opciones de clasificación, paginación y desplazamiento (por X e Y). Todas las cosas (clasificación, etc.) deben hacerse en el lado del cliente.
• Los estilos y la representación de celda personalizada se deben aplicar a las celdas de la tabla.
• Se debe poder hacer clic en las celdas (el clic debe generar un evento, que es interceptable).

Me di cuenta de que para una tarea así, es mejor manipular DOM sobre la marcha en el lado del cliente en lugar de utilizar algunas soluciones APEX listas para usar, como informes clásicos, informes interactivos o grillas.

Usé el plugin DataQables.js jQuery para este enfoque. Después de una semana de estimar la tecnología y aprender algunos JavaScript básicos (que no es mi habilidad principal), tuve lo siguiente:

En la aplicación APEX implementé un proceso de devolución de llamada de Ajax (llamado TEST_AJAX ), ejecuta el código PL / SQL, que devuelve el objeto JSON a la salida SYS.HTP (usando paquetes APEX_JSON o HTP ). Su fuente:

declare
l_temp sys_refcursor;
begin
open l_temp for go_pivot;
APEX_JSON.open_object;
APEX_JSON.open_array(''columns'');
APEX_JSON.open_object;
APEX_JSON.write(''data'', ''COL2'');
APEX_JSON.write(''title'', ''/'');
APEX_JSON.close_object;
for x in (select distinct col1 from test order by 1) loop
APEX_JSON.open_object;
APEX_JSON.write(''data'', upper(x.col1));
APEX_JSON.write(''title'', x.col1);
APEX_JSON.close_object;
end loop;
APEX_JSON.close_array;
APEX_JSON.write(''data'', l_temp);
APEX_JSON.close_object;
end;

La fuente de la función go_pivot :

create or replace function go_pivot return varchar2
is
l_query long := ''select col2'';
begin
for x in (select distinct col1 from test order by col1)
loop
l_query := l_query ||
replace('', min(decode(col1,''''$X$'''',v)) $X$'',
''$X$'',
x.col1);
end loop;
l_query := l_query || '' from test group by col2'';
return l_query;
end;

Luego creé una región de Contenido estático en la página, cuya fuente es la siguiente:

<div id="datatable_test_container"></div>

Cargué archivos CSS y JS de DataTables.js en archivos estáticos de la aplicación y los incluí en las propiedades de la página. En la sección de JavaScript de la página de Function and Global Variable Declaration agregué este código de JavaScript:

var $ = apex.jQuery;
var table;
var columns;
var rows;
//table initialization function
function table_init(json_data) {
return $(''#datatable_test'').DataTable({
//column defaults options
columnDefs: [{
"data": null,
"defaultContent": "-",
"targets": "_all"
}],
columns: json_data.columns,
data: json_data.data,
stateSave: true
});
}
//function to asynchronously get data from APEX AJAX CALLBACK
//process and then to draw a table based on this data
function worker() {
//run the process called TEST_JSON
apex.server.process(
"TEST_JSON", {}, {
success: function(pData) {
//on first run we need to initialize the table
if (typeof table == ''undefined'') {
//save current data for future use
columns = $.extend(true, [], pData.columns);
rows = $.extend(true, [], pData.data);
//generate empty html-table in the container
$(''#datatable_test_container'').append(''<table id = "datatable_test" class = "display" cellspacing = "0" width = "100%" > < /table>'');
//init the table
table = table_init(pData);
//when columns of the table changes we need to
//reinitialize the table (DataTables require it due to architecture)
} else if (JSON.stringify(columns) !=
JSON.stringify(pData.columns)) {
//save current data for future use
columns = $.extend(true, [], pData.columns);
rows = $.extend(true, [], pData.data);
//delete the table from DOM
table.destroy(true);
//generate empty html-table in the container
$(''#datatable_test_container'').append(''<table id = "datatable_test" class = "display" cellspacing = "0" width = "100%" > < /table>'');
//reinit the table
table = table_init(pData);
}
//if data changes, clear and re-draw the table
else if (JSON.stringify(rows) != JSON.stringify(pData.data)) {
//save current data for future use
//we don''t need to save the columns, they didn''t change
rows = $.extend(true, [], pData.data);
//clear table, add rows from recieved JSON-object, re-
draw the table with new data
table.clear().rows.add(pData.data).draw(false);
}
//if nothing changes, we do nothing
}
}
);
//repeat the procedure in a second
setTimeout(worker, 1000);
};

Para Execute when Page Loads , agregué:

$(document).ready(function() {
worker();
});

Que hace todo esto?

1. Static <div> en la región de contenido estático recibe una tabla vacía donde se aplica el constructor DataTables.
2. El código de JavaScript comienza su trabajo activando el proceso del servidor de devolución de llamada de Ajax, y cuando usa correctamente el resultado, este proceso retorna.
3. El constructor DataTables admite diferentes tipos de fuentes de datos, por ejemplo, puede analizar una tabla html o realizar una llamada ajax, pero prefiero usar un proceso APEX y luego basar la tabla en el objeto JSON, que este proceso devuelve.
4. Luego, el script observa los cambios. Si las columnas cambian, la tabla se elimina del documento y se reinicializa con nuevos datos, si solo cambian las filas, entonces la tabla simplemente se vuelve a dibujar con estos datos. Si nada cambia en los datos, el script no hace nada.
5. Este proceso se repite cada segundo.

Como resultado, hay un informe totalmente interactivo, que se renueva dinámicamente, con opciones tales como clasificación, paginación, búsqueda, manejo de eventos, etc. Y todo esto se hace en el lado del cliente sin consultas adicionales al servidor.

Puede verificar el resultado usando esta demostración en vivo (la región superior es el informe de tablas de datos, debajo de ella hay una cuadrícula interactiva editable en la tabla fuente, para ver los cambios, puede cambiar los datos usando la cuadrícula interactiva).

No sé si este es el mejor enfoque, pero cumple mis requisitos.

ACTUALIZADO 05.09.2017: Lista agregada del APEX_JSON de APEX_JSON llamada Ajax go_pivot y la go_pivot PL / SQL de go_pivot .