Topic: Viewing sqlite view of an MVD project on mobile
Sharing: after searching the Play Store, I found the Filex application (freeware), which reads sqlite files (the best I found). I wanted to access a project's sqlite.db, run queries, and save them for future use. I came to the conclusion that it would be faster to create views in sqlite.db using, for example, Sqlite Expert Personal (freeware), than to create a query directly in Filex. Then, with the help of ChatGPT, I generated the views. Below is an example of a view script and a screen with the data displayed by the view, which is the same as what Filex shows on a cell phone. Just copy the sqlite.db to a folder on your phone. It's not the best of all worlds, but it's a simple way to have synthetic analytical information from an MVD project on your cell phone!
WITH
[subquery] AS(
SELECT
STRFTIME ("%Y", [data]) AS [ano],
[lancamento_all_view].[id_tipo_operacao],
CASE WHEN [lancamento_all_view].[id_tipo_operacao] = 1 THEN "Receitas" ELSE "Despesas" END AS "Operacao",
[lancamento_all_view].[id_categoria],
[lancamento_all_view].[categoria],
CASE WHEN [id_tipo_operacao] = 1 THEN SUM ([entrada] - [saida]) ELSE SUM ([saida] - [entrada]) END AS [valor]
FROM [lancamento_all_view]
WHERE [lancamento_all_view].[id_categoria] <> 0
AND [lancamento_all_view].[ocultar_mining] = 0
GROUP BY
[lancamento_all_view].[id_tipo_operacao],
[lancamento_all_view].[categoria],
[ano]
),
[category_totals] AS(
SELECT
[categoria],
SUM ([valor]) AS [total_valor_categoria]
FROM [subquery]
GROUP BY [categoria]
),
[cumulative_totals] AS(
SELECT
[a].[ano],
[a].[id_tipo_operacao],
[a].[Operacao],
[a].[categoria],
[a].[valor],
(SELECT SUM ([b].[valor])
FROM [subquery] [b]
WHERE [b].[categoria] = [a].[categoria]
AND [b].[ano] <= [a].[ano]) AS [acumulado]
FROM [subquery] [a]
),
[total_receitas] AS(
SELECT
NULL AS [ano],
"Total Receitas" AS [Operacao],
NULL AS [categoria],
SUM ([valor]) AS [valor],
SUM ([valor]) AS [acumulado],
NULL AS [part%],
NULL AS [mini_grafico]
FROM [subquery]
WHERE [id_tipo_operacao] = 1
),
[total_despesas] AS(
SELECT
NULL AS [ano],
"Total Despesas" AS [Operacao],
NULL AS [categoria],
SUM ([valor]) AS [valor],
SUM ([valor]) AS [acumulado],
NULL AS [part%],
NULL AS [mini_grafico]
FROM [subquery]
WHERE [id_tipo_operacao] = 2
),
[main_query] AS(
SELECT
[subquery].[ano],
[subquery].[Operacao],
[subquery].[categoria],
PRINTF ('%.2f', [subquery].[valor]) AS [valor],
PRINTF ('%.2f', [cumulative_totals].[acumulado]) AS [acumulado],
PRINTF ('%.2f', ([subquery].[valor] / [category_totals].[total_valor_categoria]) * 100) AS [part%],
SUBSTR ('██████████████████████████████████████████████████████', 1, ROUND (([subquery].[valor] / [category_totals].[total_valor_categoria]) * 50)) || " (" || [subquery].[id_categoria] || ")" AS [mini_grafico],
[subquery].[id_categoria]
FROM [subquery]
JOIN [category_totals] ON [subquery].[categoria] = [category_totals].[categoria]
JOIN [cumulative_totals] ON [subquery].[ano] = [cumulative_totals].[ano]
AND [subquery].[categoria] = [cumulative_totals].[categoria]
AND [subquery].[id_tipo_operacao] = [cumulative_totals].[id_tipo_operacao]
),
[blank_lines] AS(
SELECT DISTINCT
NULL AS [ano],
NULL AS [Operacao],
[categoria],
NULL AS [valor],
NULL AS [acumulado],
NULL AS [part%],
NULL AS [mini_grafico],
[id_categoria]
FROM [main_query]
)
SELECT
[ano],
[Operacao],
[categoria],
[valor],
[acumulado],
[part%],
[mini_grafico]
FROM (SELECT
[ano],
[Operacao],
[categoria],
[valor],
[acumulado],
[part%],
[mini_grafico],
[id_categoria],
0 AS [sort_order]
FROM [main_query]
UNION ALL
SELECT
'***********' AS [ano],
'***********' AS [Operacao],
'***********' AS [categoria],
'***********' AS [valor],
'***********' AS [acumulado],
'***********' AS [part%],
'***********' AS [mini_grafico],
[id_categoria],
1 AS [sort_order]
FROM [blank_lines])
ORDER BY
[id_categoria],
[sort_order],
[ano],
[Operacao]
img_sql_view.jpg 208.48 kb, 10 downloads since 2024-07-16
