CargoPhysical: If Cost does not have an Artist, it should still be processed as a Cost

[richardbirkin]

See Cargo Physical 2023 Q4.

There are a couple of costs that do not have an Artist in the Artist column.
They should still be imported, but be listed as Unknown Cost

This also relates to another feature that I need to document, but is not dependent on that to be done if you do this first.

Once this is done, the total should be accurate.

[g105b]

Once you've taken a look at the current implimentation, we should have a chat about how artistless costs should be displayed. You might be right that "Unknown Cost" is the pseudo-Artist, but take a look and let me know how it feels.

[richardbirkin]

Coolio. Is it easy to pull down?

[g105b]

Yeah it's in the branch and working fine as far as I can tell.

Take a look at the screenshot - it estimates the date based on the name of the file when there isn't a date field (nearest quarter)_.

[richardbirkin]

Great - I can see that now.

As a result, the total on the Uploads page is wrong. See sub issue @g105b

[richardbirkin]

@g105b I have added two sub issues above.
I believe they are just calculation errors in the front-end tables rather than anything related to processing.

[richardbirkin]

@g105b

I have made a Cargo Physical file for testing. Remember, this has been edited in Numbers and exported to .xlsx so might have some header gubbins in there. The data should handle all of the variations we need to test for:

Earnings
Returns (negative earnings)
Costs with associated product (e.g. shipping a box of a release to the label, promotion of an existing release)
Costs without associated product (e.g. manufacture of a release that doesn't exist yet)
Storage Costs

All data has been anonymised.

I did notice in doing this that Costs without associated product actually do have a catalogue number. So if the product exists in TS it will have a catalogue number...so there is a link there that TS can make without user input.

888888_2024Q3.xlsx

[richardbirkin]

[richardbirkin]

Summary Table:

Earnings: £293.288

Costs: £2656.47

Outgoing: 0.00 (no splits for now)

Profit: - £2272.18

Products

Artist 1 Album 1

Earnings: £139.624
Costs: £35.88

Artist 1 Album 2

Earnings: £0.00
Costs: £23.44

Artist 2 Album 1

Earnings: £153.664
Costs: £2488.20

Non-product Costs:

£17.95

[g105b]

I've made one extra database migration (renaming the Upload.cachedEarnings column to Upload.cachedProfit), so you'll need to run gt migrate before re-testing things.

[richardbirkin]

Uploads table:

• Total profit should be Total and be the amount that the user received with the statement i.e., the most basic summing of Net Amounts

Summary Table:

• Costs total doesn't correctly reflect the costs listed in the Costs table.

Product Table:

• Products without Earnings BUT with Costs should appear in Products table
• Product table headings should include Balance (Earnings minus Costs)

[richardbirkin]

Expectations from Test .xlsx file as uploaded previously:

Uploads Tab

Uploads Table

Net amount (not Total Profit) = -£2,272.18

The uploads table should reflect the NET amount that the Label would see from the distributor when the statement is delivered. Then we break it down on the Products Tab.

Products Tab

Summary Table

Earnings	Costs	Balance	Outgoing	Profit
£293.29	£2565.47	-£2272.18	0.00	-£2272.18

Products Table

Artist	Title	Earnings	Costs	Balance	Outgoing	Profit
Artist 1	Album 1	£139.62	£35.88	£103.74	0.00	£103.74
Artist 1	Album 2	£0.00	£23.44	-£23.44	0.00	-£23.44
Artist 2	Album 1	£153.66	£3.20	£150.46	0.00	£150.46
GVLI88888	Artist 2 - Album 1	0.00	£2225.00	-£2225.00	0.00	-£2225.00
June 2024 Cargo Collective	ARTIST2 - ARTIST2ALBUM1	0.00	£260.00	-£260.00	0.00	-£260.00
To Greg	various - 35 units	0.00	£17.95	-£17.95	0.00	-£17.95

Note:

For Cargo Physical:

Total Costs = COSTS + STORAGE COSTS
Total Earnings = Sales + Returns (GROSS VALUE * DISTRIBUTION FEE)

[richardbirkin]

@g105b see above. I made some corrections after coming back from a cup of tea and seeing some glaring errors on my part.

[g105b]

I think there are still some errors but they're obvious as to what they should be (GVLI88888 Artist 2 - Album 1 should not have £2K profit, for example).

[richardbirkin]

@g105b there is a minus there, it's just been skewed by the page formatting I think

[g105b]

I see what you mean. I've just updated the code, you should be able to do a pull. There's a new column in the Product table to keep track of its original Upload, so a migration is necessary.

[richardbirkin]

Ace!

We're so close. I found one more bug. I'm adding something new to the test file so will send an updated file and output expectations.

But simply - a Cost doesn't have to have an Artist to be imported.

In Q4 2023 file there are some barcode reworks that have no Artist. There are two of them, and they total £13.14 which is the difference between what I see in TS and what I'm expecting.

[richardbirkin]

New file with a no Artist COST:

888888_2024Q3.xlsx

[richardbirkin]

Expectations from Test .xlsx file as uploaded previously:

Uploads Tab

Uploads Table

Net amount (not Total Profit) = -£2,277.85

The uploads table should reflect the NET amount that the Label would see from the distributor when the statement is delivered. Then we break it down on the Products Tab.

Products Tab

Summary Table

Earnings	Costs	Balance	Outgoing	Profit
£293.29	£2571.14	-£2277.85	0.00	-£2277.85

Products Table

Artist	Title	Earnings	Costs	Balance	Outgoing	Profit
Artist 1	Album 1	£139.62	£35.88	£103.74	0.00	£103.74
Artist 1	Album 2	£0.00	£23.44	-£23.44	0.00	-£23.44
Artist 2	Album 1	£153.66	£3.20	£150.46	0.00	£150.46
GVLI88888	Artist 2 - Album 1	0.00	£2225.00	-£2225.00	0.00	-£2225.00
June 2024 Cargo Collective	ARTIST2 - ARTIST2ALBUM1	0.00	£260.00	-£260.00	0.00	-£260.00
To Greg	various - 35 units	0.00	£17.95	-£17.95	0.00	-£17.95
-	60 units reworked	0.00	£5.67	-£5.67	0.00	-£5.67

Note:

For Cargo Physical:

Total Costs = COSTS + STORAGE COSTS
Total Earnings = Sales + Returns (GROSS VALUE * DISTRIBUTION FEE)

[g105b]

Thank you. This is really clear and easy to work with.

[g105b]

Can you take a look at the latest Excel file uploaded - there are strange things happening with this file, and I think it's to do with column U, which used to be Qty In Stock but now has financial data in it.

[richardbirkin]

My bad. That was left over from me doing calcs.

here's a clean version:

888888_2024Q3.xlsx