Add reporting feature #5155

NicolasDorier · 2023-07-05T11:40:54Z

Features

Out of scope

While the Greenfield route has been done, I think we should disable it for now, as we are still unsure of proper API design.

Additional reports will be in different PRs after we could validate the design of this PR.

To do later

Report for Off-Chain payments (will need to have date base filtering of invoices on lightning library)
Point of Sale/Crowdfunding stats on items sold (see Articles report / Payments report #4598)
Refund reports
Payouts reports

UX

This add a new page at store level: Reporting:

When clicking on it, a new page open

Note on the UX: The hard part is that the designer is unable to predict how many columns will be there. That said, cell content can be customized depending on the type of the column.

Implementation details

Backend

A new class ReportService is exposing report related methods.
A base class ReportProvider can be subclassed to implement new types of reports.
At the moment, only two implementations exists PaymentsReportProvider and OnChainWalletsReportProvider, but many more will come for next major release.

Front end

I am using VueJs, the whole page is coded as a single page application.

Linked discussions

#5095
#937
#2561
#2601
#4598

Kukks · 2023-07-05T12:15:07Z

BTCPayServer.Client/BTCPayServer.Client.csproj

@@ -33,6 +33,8 @@
      <PackageReference Include="BTCPayServer.Lightning.Common" Version="1.3.21" />
      <PackageReference Include="NBitcoin" Version="7.0.24" />
      <PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
+      <PackageReference Include="NodaTime" Version="3.1.9" />


Do we really need a whole new library on the client library level?

I need to remove it, we dont

Kukks · 2023-07-05T12:18:54Z

BTCPayServer/wwwroot/js/store-reports.js

+    }
+};
+
+$(function () {


reduce jquery usage:

Suggested change

$(function () {

document.addEventListener("DOMContentLoaded", () => {

Kukks · 2023-07-05T12:20:59Z

BTCPayServer/wwwroot/js/store-reports.js

+};
+
+$(function () {
+    $(".flatdtpicker").on("input", function () {


Suggested change

$(".flatdtpicker").on("input", function () {

document.querySelectorAll('.flatdtpicker').forEach(el => el.addEventListener("input", ()=>{

Kukks · 2023-07-05T12:21:55Z

BTCPayServer/wwwroot/js/store-reports.js

+$(function () {
+    $(".flatdtpicker").on("input", function () {
+        // We don't use vue to bind dates, because VueJS break the flatpickr as soon as binding occurs.
+        var to = $("#toDate").val();


Suggested change

var to = $("#toDate").val();

const to = document.querySelector("#toDate").value

NicolasDorier · 2023-07-05T13:38:51Z

What is the problem with jquery?

NicolasDorier · 2023-07-10T07:30:36Z

@dstrukt @dennisreimann I just added a commit to generate fake data in the reporting.

When chosing a new view, I believe it is better to not show the raw data upfront unlike I am doing now. Reasons are:
- For 10000 rows, the UI seems to be slow to render for some reason. (Probably something that can be fixed)
- Few people would want to see the raw data, in general I expect they are more interested in the aggregates.
- The raw data having many column, it can't look nice

dennisreimann · 2023-07-11T15:35:38Z

UX: This add a new page at store level: Reporting

I think we could either put it up between Dashboard and Settings or at the end of the Payments section. Regardless of where we position it, we need a new icon for this feature :) @dstrukt

dennisreimann · 2023-07-11T16:41:03Z

Introduced a fixed header and made the tables auto-width, so that they read a bit better. Will experiment with sticky table header rows next.

NicolasDorier · 2023-07-12T01:40:34Z

The make me more happy

BTCPayServer/Views/UIReports/StoreReports.cshtml

dennisreimann · 2023-07-12T08:51:30Z

BTCPayServer/wwwroot/js/datatable.js

+        // filter(){ return filter1 && filter2 && filter3; }
+        var newData = [];
+        var o = {};
+        eval('function filter() {return ' + filterStrings.join(' && ') + ';}');


The basic idea is that the report provider can decide to filter rows before building the table.
I use it for the summary of lightning addresses payments. Without this, the datatable would show a row with empty ligthning address if none are present. Instead, I want this line to be gone.

Note that the conversion from tabular data to a representation bindable to VueJS for summary tables is done JS side.
I am unsure this is good idea, I believe we could just do that C# side.

It would also make it easier to test.

dstrukt

Few initial thoughts after reviewing, some might be out of scope, others are minor nits and suggestions:

Happy to make some of these changes post-merge as well, if it makes more sense.

Search

Do we want the ability to search? e.g. something like this:
Calendar icons to the left of the inputs for the date selection could be nice too

Balance Tables

Any reason we don't want to conserve a bit of space by doing two columns like so. Am I missing something? Might be nice to consolidate in the future, but for the time being.

Payments

Could be improved with updated labels:

Lightning Address label updates
Makes me think some of the other Amount labels could be Balance instead...hmm

On-Chain

Could be improved with updating the label and pushing the header into the table key value for the on-chain view:
Context: I found the Balance Change aspect of the On-Chain table a little confusing... as the test data amounts were mostly negative values?

Still exploring ways of consolidating the tables for more space and ease of parsing the data, just sharing, might be a later follow up if any of the explorations make sense:

e.g: On-Chain/Lightning, doesn't hold up as well, but interesting nonetheless:

works in a simple context as well:

Products

Summary by items -> Summary Could this label just be shortened, and still make sense?
Should it be App ID or the App/Plugin Name? Or perhaps both?

Raw Data

Payments

Is the date formatting messed up? 2023-07-05T01:42:41-06:00

On-Chain

Suggestions for improvements
Unconfirmed would be a red X icon instead of the green checkmark
Balance Change decrease would be -1.06150021 and be colored red

Filter Dropdown

Copy suggestion: Items sold -> Products or Products Sold? To keep it consistent with the POS label
Copy suggestion: On-Chain Wallets -> On-Chain or Wallets? On second thought, might be fine with the original..

General

I noticed there's an additional scrollbar at the bottom of the page, can we remove this? I think the table specific scrollbar makes sense, given the width of it's contents.

The main CTA could arguably just be Export cleaner, as the CSV bit isn't necessary imo

Really great work!

NicolasDorier · 2023-07-18T04:10:15Z

About wallet balance confirmed/unconfirmed: I don't believe a bar make sense. In most case, unconfirmed will be ridiculously low compared to confirmed, so this doesn't add lot's of information.

For on-chain versus lightning, it might make more sense.

Also, I was thinking: Some tables are grouping on Invoice currency. I believe this is overkill, as most of the time, the invoice currency is the same.

Rather than grouping per currency, I will just make additional chart per currency?

NicolasDorier · 2023-07-18T04:19:49Z

@dstrukt I want to add, the aggregated tables I am showing could potentially be replaced by any chart that make sense.

Kukks · 2023-07-18T10:18:55Z

BTCPayServer/Services/Reporting/PaymentsReportProvider.cs

+            values.Add((string)r.order_id);
+            if (PaymentMethodId.TryParse((string)r.payment_type, out var paymentType))
+            {
+                if (paymentType.PaymentType == PaymentTypes.LightningLike || paymentType.PaymentType == PaymentTypes.LNURLPay)


use the payment type tostring instead of hardcoding

I did this because because the difference between lightning versus LNURL doesn't matter at all for merchants.

They want the consolidated view of how much they received on lightning, they don't care whether it come from LNURL or not, so we need to map them to the same value.

Kukks

Missing api docs and tests! 😂

NicolasDorier · 2023-07-18T12:31:06Z

Missing tests, will do. For the API, I will remove it from this PR, exposing it as a NonAction.

dstrukt · 2023-07-18T15:43:48Z

About wallet balance confirmed/unconfirmed: I don't believe a bar make sense. In most case, unconfirmed will be ridiculously low compared to confirmed, so this doesn't add lot's of information.

Tend to agree, it's a bit much for what would only be a pending state at best, and rarely visible for instances with low volume.

For on-chain versus lightning, it might make more sense.

Also agree, but wasn't intended to be included in this PR, just a quick visual exploration to see if it made sense / worth the time to implement!

Also, I was thinking: Some tables are grouping on Invoice currency. I believe this is overkill, as most of the time, the invoice currency is the same.

Rather than grouping per currency, I will just make additional chart per currency?

That could work, I do appreciate the flexibility/clarity of the current implementation, but I think I'd have to see it.

pavlenex · 2023-07-19T10:28:30Z

@NicolasDorier Any chance fake data can generate items for POS? I think this is one of the highest-requested reports, so would be good to test it out.

dstrukt · 2023-07-19T21:19:02Z

ACK to fake data, if possible! Was my biggest challenge in providing more feedback as well.

NicolasDorier · 2023-07-20T00:33:54Z

@dstrukt @pavlenex done

pavlenex · 2023-07-21T19:56:23Z

@NicolasDorier I've tested draft a bit, so far so good.
Question, is export always meant to give raw data, not data according to tables?
When do you plan to add fitlers and groups?
When do you think you'll be able to get this one out of draft?

We're thinking of RC for Monday, but unsure how much you have to do on this one.

dstrukt

Overall, looks really solid, there's some more finessing of labels and UI I'd like to do, but I don't want to hold up the release to make these minor tweaks.

Especially if our RC deadline is monday, this is something @dennisreimann and I could easily do a follow up PR to address.

NicolasDorier · 2023-07-24T00:24:28Z

it's bit tight, so let's merge this one and incrementally improve on dstrukt feedback

NicolasDorier · 2023-07-24T00:29:05Z

is export always meant to give raw data, not data according to tables?`

@pavlenex I don't know what you mean, but the CSV export is exporting the raw data, because all other structures can be trivially created again through Excel. The idea is to provide just enough feature to be useful for simple cases, delegating complex cases to Excel.

Filtering for now is only on date, I believe allowing some simple client side filters on the raw data.
We can do that in minor release or major release later.

About Grouping: The summary tables that you see is grouping. If what you want is letting the user drag and drop components to make their own group, I wished to do that but the existing open source components for pivot table just sucks and aren't maintained anymore.

dstrukt · 2023-07-24T18:50:22Z

it's bit tight, so let's merge this one and incrementally improve on dstrukt feedback

Sounds good - will make an issue with some of the remaining items post-release. Great work!

JamesMessick1965 · 2023-08-01T21:21:04Z

Good

NicolasDorier marked this pull request as draft July 5, 2023 11:41

Kukks reviewed Jul 5, 2023

View reviewed changes

NicolasDorier force-pushed the qitntqbe branch from 993c10f to 8f7c849 Compare July 5, 2023 14:19

NicolasDorier added 5 commits July 11, 2023 09:44

Add reporting feature

abe7e1d

Remove nodatime

51d18c6

Add summaries

7d56bef

work...

61e9b9e

Add chart title

2245922

NicolasDorier force-pushed the qitntqbe branch from ed9244c to 2245922 Compare July 11, 2023 00:44

NicolasDorier added 3 commits July 11, 2023 09:56

Fix error

ddfe0de

Allow to set hour in the field

bcc9df7

Merge branch 'master' into qitntqbe

32631aa

pavlenex linked an issue Jul 11, 2023 that may be closed by this pull request

Improve invoice reporting #5095

Closed

UI updates

382b3cb

NicolasDorier and others added 4 commits July 12, 2023 12:44

Fix fake data

9b43068

ViewDefinitions can be dynamic

231e7b4

Add items sold

064c296

Sticky table headers

65eedd2

dennisreimann reviewed Jul 12, 2023

View reviewed changes

BTCPayServer/Views/UIReports/StoreReports.cshtml Outdated Show resolved Hide resolved

dennisreimann reviewed Jul 12, 2023

View reviewed changes

dennisreimann and others added 5 commits July 12, 2023 11:05

Update JS and remove jQuery usages

2c7b2fb

JS click fix

f07b85a

Handle tag all invoices for app

de736a7

fix dup row in items report

65a98dc

Can cancel invoice request

8b300fa

dstrukt self-requested a review July 17, 2023 19:55

dstrukt suggested changes Jul 17, 2023

View reviewed changes

Kukks reviewed Jul 18, 2023

View reviewed changes

Kukks requested changes Jul 18, 2023

View reviewed changes

Add tests

aa6dd82

Fake data for items sold

21ba188

NicolasDorier added 2 commits July 20, 2023 10:14

Rename Items to Products, improve navigation F5

98389de

Use bordered table for summaries

ebdc781

dstrukt self-requested a review July 20, 2023 22:16

dstrukt approved these changes Jul 21, 2023

View reviewed changes

NicolasDorier marked this pull request as ready for review July 24, 2023 00:24

NicolasDorier merged commit dc98695 into btcpayserver:master Jul 24, 2023
4 checks passed

NicolasDorier deleted the qitntqbe branch July 24, 2023 00:24

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add reporting feature #5155

Add reporting feature #5155

NicolasDorier commented Jul 5, 2023 •

edited

Kukks Jul 5, 2023

NicolasDorier Jul 5, 2023 •

edited

Kukks Jul 5, 2023

Kukks Jul 5, 2023

Kukks Jul 5, 2023

NicolasDorier commented Jul 5, 2023

NicolasDorier commented Jul 10, 2023

dennisreimann commented Jul 11, 2023

dennisreimann commented Jul 11, 2023 •

edited

NicolasDorier commented Jul 12, 2023

dennisreimann Jul 12, 2023

NicolasDorier Jul 12, 2023

NicolasDorier Jul 12, 2023 •

edited

dstrukt left a comment •

edited

NicolasDorier commented Jul 18, 2023

NicolasDorier commented Jul 18, 2023

Kukks Jul 18, 2023

NicolasDorier Jul 18, 2023 •

edited

Kukks left a comment

NicolasDorier commented Jul 18, 2023

dstrukt commented Jul 18, 2023

pavlenex commented Jul 19, 2023

dstrukt commented Jul 19, 2023

NicolasDorier commented Jul 20, 2023

pavlenex commented Jul 21, 2023

dstrukt left a comment

NicolasDorier commented Jul 24, 2023

NicolasDorier commented Jul 24, 2023

dstrukt commented Jul 24, 2023

JamesMessick1965 commented Aug 1, 2023

	$(function () {
	document.addEventListener("DOMContentLoaded", () => {

	$(".flatdtpicker").on("input", function () {
	document.querySelectorAll('.flatdtpicker').forEach(el => el.addEventListener("input", ()=>{

	var to = $("#toDate").val();
	const to = document.querySelector("#toDate").value

Add reporting feature #5155

Add reporting feature #5155

Conversation

NicolasDorier commented Jul 5, 2023 • edited

Features

Out of scope

To do later

UX

Implementation details

Backend

Front end

Linked discussions

Kukks Jul 5, 2023

Choose a reason for hiding this comment

NicolasDorier Jul 5, 2023 • edited

Choose a reason for hiding this comment

Kukks Jul 5, 2023

Choose a reason for hiding this comment

Kukks Jul 5, 2023

Choose a reason for hiding this comment

Kukks Jul 5, 2023

Choose a reason for hiding this comment

NicolasDorier commented Jul 5, 2023

NicolasDorier commented Jul 10, 2023

dennisreimann commented Jul 11, 2023

dennisreimann commented Jul 11, 2023 • edited

NicolasDorier commented Jul 12, 2023

dennisreimann Jul 12, 2023

Choose a reason for hiding this comment

NicolasDorier Jul 12, 2023

Choose a reason for hiding this comment

NicolasDorier Jul 12, 2023 • edited

Choose a reason for hiding this comment

dstrukt left a comment • edited

Choose a reason for hiding this comment

Search

Balance Tables

Payments

On-Chain

Products

Raw Data

Payments

On-Chain

Filter Dropdown

General

Really great work!

NicolasDorier commented Jul 18, 2023

NicolasDorier commented Jul 18, 2023

Kukks Jul 18, 2023

Choose a reason for hiding this comment

NicolasDorier Jul 18, 2023 • edited

Choose a reason for hiding this comment

Kukks left a comment

Choose a reason for hiding this comment

NicolasDorier commented Jul 18, 2023

dstrukt commented Jul 18, 2023

pavlenex commented Jul 19, 2023

dstrukt commented Jul 19, 2023

NicolasDorier commented Jul 20, 2023

pavlenex commented Jul 21, 2023

dstrukt left a comment

Choose a reason for hiding this comment

NicolasDorier commented Jul 24, 2023

NicolasDorier commented Jul 24, 2023

dstrukt commented Jul 24, 2023

JamesMessick1965 commented Aug 1, 2023

NicolasDorier commented Jul 5, 2023 •

edited

NicolasDorier Jul 5, 2023 •

edited

dennisreimann commented Jul 11, 2023 •

edited

NicolasDorier Jul 12, 2023 •

edited

dstrukt left a comment •

edited

NicolasDorier Jul 18, 2023 •

edited