Boxes (and contained items) generated in a base within certain time,

filterable by

• time range
• product name
• product category
• product gender
• (Tags, no priority)

grouping
-boxes/items

parameter: base

facts: number of boxes and number of items

1. fact table
   (cat_id)
   prod_id
   createdOn
   gender_name
   #boxes
   #items

2. dim-category
   cat_id
   cat_name

3. dim-products
   prod_id
   prod_name

Data format

{
  "facts": [
    {"categoryId": 1, "productId": 1, "createdOn": "2022-08-22", "gender": "Male", "boxesCount": 3, "itemsCount": 44},
    ...
  ],
  "dimensions": {
    "product": [
      {"id": 1, "name": "T-Shirts"},
      ...
    ],
    "category": [
      {"id": 1, "name": "Clothing"},
      ...
    ]
  }
}

GraphQL query example

query { createdBoxes {
        facts {
            createdOn categoryId productId gender boxesCount itemsCount
        }
        dimensions {
            product { id name }
            category { id name }
    } } }

Currently proposed:

• an actor distributing aid, e.g. Free Shop Lebanon
• an actor with a warehouse on the receiving end, e.g. IHA

From #950
We should define a rough threshold for the data size to be returned from BE to FE and monitor it.

Flowchart Sankey diagram

Aim is an ADR to decide how we will built the frontend - either based on fully fletched out visualization tools like graphana or js libraries like D3.

Filters and Styling if single visualizations are viewed

• How should a user copy the url and filters to embed via an iframe into a webpage or newsletter?
• What options should there be for exporting?
• Should we give them different fixed size options?
• should there be a share button?
• should the filter be extra or in the visualizations?

under this point falls:

1. align tsconfig, eslint, prettier config
2. remove synk and update the dependabot config
3. updating yarn
4. updating target ESMA script in package.json

• migrating from create-react-app to Vite
• introduce lerna.js or NX
• share packages between the FEs
• creating shared components
• migrate Auth0 rules to actions (deadline November) task for Vahid

react/require-default-props
loading
error handling
testing
sentry
pre-commit
app.yaml
shared assets
README
logout with dropapp
analyze bundle size

The bundle size is significantly larger than recommended.
Consider reducing it with code splitting: https://goo.gl/9VhYWB
You can also analyze the project dependencies: https://goo.gl/LeUzfb

Browserslist: caniuse-lite is outdated. Please run:
npx update-browserslist-db@latest
Why you should do it regularly: https://github.com/browserslist/update-db#readme

Acc. to #903 the following interface is proposed

type Query {
  topProductsCheckedOut(baseId: Int!): TopProductsCheckedOutData
  topProductsDonated(baseId: Int!): TopProductsDonatedData
}

type TopProductsCheckedOutData implements DataCube {
  facts: [TopProductsCheckedOutResult]
  dimensions: TopProductsDimensions
}

type TopProductsCheckedOutResult {
  checkedOutOn: Date
  productId: Int
  categoryId: Int
  rank: Int
  itemsCount: Int
}

type TopProductsDonatedData implements DataCube {
  facts: [TopProductsDonatedResult]
  dimensions: TopProductsDimensions
}

type TopProductsDonatedResult {
  createdOn: Date
  donatedOn: Date
  sizeId: Int
  productId: Int
  categoryId: Int
  rank: Int
  itemsCount: Int
}

type TopProductsDimensions {
  " Always null for topProductsCheckedOut query "
  size: [ResultIdName]
  product: [ResultIdName]
  category: [ResultIdName]
}

Last updated by Roanna on 2023/7/22; will have complete proposal by EOD 2023/7/23

Suggested Beneficiary or Distribution Based Feature:

Create a graph that shows the beneficiary population 1) Bounded by time (i.e., either last active / distributed or registration date) [population pyramid]; that is 2) filterable or visually categorized by a parameter such as age, tag, or family size [bar graph].

Related User Stories:

A) As a impact manager, I wish to be able to make our impact visible to the public by showing our most current meaningful interactions with beneficiaries, instead of just showing our historical work as an org. This is most effective when showing our time-bound beneficiary population, as well as being able to paint a picture of what type of people we are actually helping with our work.
B) As a distribution coordinator on the field, I want to be able to track our distribution operations over time and how that is changing with the beneficiary population, so that I can have a better idea of what is needed by beneficiaries for future distributions, as well as analyze how well our past distributions are meeting beneficiaries' needs.

Suggested Inventory or Shipment Based Feature:

Create a graphs that show the creation of boxes and aid inventory both current and over time by product category [stacked bar chart or area chart; pie/donut chart], and then [something related to outflows and donations, possibly sankey diagram, or bar chart for destinations]

Related User Stories

C) As a warehouse volunteer, I want to be able to see when and where the boxes I've packed are getting sent to the field, so that I can directly see/feel the connection between my work and how it impacts real people.
D) As an impact manager, I want to link the goods we have on hand, the goods we source, and the goods we supply to other organizations easily linkable to international aid standards of core needs, such as SPHERE, so that I can work more easily with INGOs as well as clearly report on M&E in grants.
E) As a logistics coordinator on the field ....(work with the UN? cooperate with other orgs? manage operations? what specific part of operations? What else? )

Reference Tickets in Boxtribute backlog:

Beneficiary or Distribution Based

Export of Beneficiaries - https://trello.com/c/NWwAF15B
Planning Distributions - https://trello.com/c/rwl6sx7h and https://trello.com/c/NVPEcTDV
Filtering of graphs of beneficiary population and distributions by time - https://trello.com/c/FcRn3LBt
search beneficiaries by DOB - https://trello.com/c/VtdYj036
grouping of distributed products by gender and category - https://trello.com/c/XtXEkSeR

Inventory or Shipment Based

Stock moves to a donated locations over time - https://trello.com/c/YxNWMBIx
warehouse data - https://trello.com/c/r2DHkBAu
Boxes created each day - https://trello.com/c/0vSxVCFf
notification of distributed boxes - https://trello.com/c/2A3OA2Cb

Sunburst Diagram with drill down showing all boxes that currently have state 'in stock'. For this first version of the sunburst we can realize the same grouping and the same hierarchy as the Boxtribute Stock Overview:

1. product category in the centre
2. product name (merged from product with same name but different gender)
3. gender
4. sizes

FE

• Showing both amount of items AND amount of boxes. Amount of items should be the default number making up 100% of the sunburst. Secondary number should not be included in the calculation of the ratio.

Grouping

• Option to toggle this to boxes would be very helpfull

Filter

• Tags
• Locations
• by box state: doing the same thing for other box states would be interesting (total amount donated, Lost etc.), but 'In Stock' as default
• (optional further down the road: filter by time and show Boxes 'in stock' at certain point in time in the past)

This is for coordinator of donations on both sides, sending and receiving NGOs:
I, as coordinator of donations of a sending NGO need an quick overview that is easily explorable of what we have in Stock, in order to decide whether we can go ahead with the organizations of a shipment to a NGO with specific needs. Here it would be good to have both items and box numbers in sight: Items are relevant to compare with the communicated need of the receiving NGO. Box number is relevant in order to estimate, whether this is worth packing on a pallet for shipment (ca. 22 boxes are necessary to fill a pallet)

createdBoxes query: data transformation required

Pie Chart component

Development of a resize component observing changes to the width and hight and dynamically adjust the child chart.

requirements:

• styling from chakra and Boxtribute branding
• be responsive
• tooltips
• (maybe animations)

Requirements:

1. It must be possible to customize the start and end date of the period displaying flows.
2. It must be possible to filter the flow of inventory by one or more product types.
3. It must be possible to filter the flow of inventory by category type.
4. "Place" can be defined as a warehouse location or transfer source/destination.

Stretch goal:
Do a similar exploration but with box_state.

Sankey diagram for donated boxes

Given a source base S, show the number of boxes that were donated to multiple destination bases D(n) within a time frame.
In the best case, destination bases can be grouped by country --> use colours or patterns for that grouping instead of a additional node (see comments)

Due to different sources of data we need to seperate two sections:

• the top one using the data from the TRANSFER functionality,

• the bottom one the data from boxes in locations with DONATED being their default state. Location names will be the names of the destinations in the Sankey.

• FE can separate these two scenarios using 'types' of data to implement two different (hardcoded) flows and their decoration
  -- BE doesn't have to include 'source' for each 'target'
  -- flow from source to target becomes clear by type, in the current case

1. If via TRANSFERS: Moved out of Stock - Outgoing Shipments - Transferred Donations - Target
2. If via state 'donated': Moved out of Stock - Outgoing Shipments - Selfreported Donations - Target

(I'm not sure where Lost&Scrap belongs)

BE

• Lost
• Scrap
• Transfer
• moved to donated

FE

Nodes of the sankey in order from left to right (WIP):

1. "Moved out of stock" (100%)
2. "Lost" (terminates here) – "Scrap" (terminates here) – "Outgoing Shipments"
3. (Outgoing Shipments:) "Transfer of Donations" (data from shipment functionality) – "Self-Reported Donations" (data from base-locations status 'donated', that is from the 'moved out'-query)
4. (Transfer:) "Country/Region" – (Self-Reported:)N/A
5. (Country/Region:) "NGO/base" – (Self-Reported:) "NGO/base" printed as documented in the Location-name with status 'donated'

Grouping

• Box / Item

Filter

• Tag
• Product category
• product name
• No. of shipment: like time span, but oriented on events of outgoing transfers/moved-boxes

Mockup solving the question of country-grouping: via colour/pattern

Corresponding User Story: "I, as part of the fundraising team, try to fill our quarterly newsletter with content about our shipments in the last quarter/ half-year/ year to prove to our donors that we are using their donations in the right way."

Please share your thoughts in case you have feedback @aerinsol, also considering the labeling of the nodes.

filterable by

• time range
• product name
• product category
• product gender
• (tags, no priority)

grouping
-boxes/items

Evaluate https://projects.wojtekmaj.pl/react-daterange-picker/
Advantages:

• User can only input valid time ranges by design
• Time Range selection is more intuitive

topProductsCheckedOut/Donated query

From #950
[Data Structure] figure out which transactions are tracked incorrectly or not at all in the history table.

• clarify meaning of viz
  • if neccessary modify titles of viz (i.e. Created Boxes)
  • legends
  • axis description
• Sankey:
  • also show percentage
  • “Can we order the flows from biggest to smallest? What about alphabetically?”
  • “Can we feed in some custom coloring based on our own parameters (e.g., this is to Greece, this is to Serbia, this is to France, etc.)”
  • what shipping state is depicted on the far right? 'sent'/'receiving'/'completed'? Clarify on axis
    (- Sunburst (after filtering improvements):
  • if neccessary set default to 'show only the inner two rings'
    • if possible make it customizable )

Follow-up to #909

UPDATE: the below is superseded by this

Extending the thoughts from this comment the following GraphQL interface is proposed. It's quite generic, for the current use case (donated boxes), boxState: Donated and a donation "pseudo" location are returned.

type Query {
  movedBoxes(baseId: Int!): MovedBoxesData
}

type MovedBoxesData implements DataCube {
  facts: [MovedBoxesResult]
  dimensions: MovedBoxDataDimensions
}

"""
A box can be moved in various ways:
- within a base (location ID with InStock/Donated)
- because it's lost (Lost)
- because it becomes scrap (Scrap)
- because it's about to be shipped (target base ID with MarkedForShipment)
- because it's being shipped (target base ID with InTransit/Receiving)
- 
"""
type MovedBoxesResult {
  movedOn: Date!
  boxState: BoxState!
  locationId: Int
  baseId: Int
  categoryId: Int!
  boxesCount: Int!
}

type MovedBoxDataDimensions {
  category: [DimensionInfo]
  tag: [TagDimensionInfo]
  location: [DimensionInfo]
  base: [DimensionInfo]
}

user story

I, as a warehouse coordinator, want to see how many boxes and items I have moved from a warehouse location to a donated location like a free shop in a time range, possibly filterable by product, size, gender. The diagram should be a bar plot where each bar represents a customizable time period.

Task description

@pylipp figure out what we would have to do to prepare the data and pass it to the frontend.
Please start a doc where we collect the additional queries we have to create for the visualizations with their requirements, variables, user stories, and the data structure that is returned.

Please consider at least two options: write the query in the current data-structure and in the perfect data structure for the query.
What would be the trade-offs in performance, cost and implementing the data structure/query

Update 2023-07-24: list of boxes grouped by location for one product in a given time range
Update August 2023: first user story shall be about beneficiary demographics.

Incorporate results of filter design meetings and implement filters on old Vizs (1-4)

global:

• time span
• product category

local:

• Grouping by boxes/items
• Tags

these are two possible Designers from my (Hans) environment:
https://bit.ly/UXportfolio_22
https://www.behance.net/karyadirik

hosting statviz FE on a separate Google Bucket under the address
statviz-staging.boxtribute.org
statviz-demo.boxtribute.org
statviz.boxtribute.org

To improve the speed of frontend development we evaluate to include NIVO and use it for most Visualizations.
Its not meant to complement Visx

• Pre build Visualizations from Nivo for most use cases
  ** PieChart, BarChart, Sankey

• Custom Visualizations with Visx for specific use cases
  ** DemographicChart

Tasks:

• Add Nivo to dependencies
• Add BarChart (2nd viz)
• Add PieChart (3rd viz)

In the discussion around #950 I'm updating the content. The goal is to

• integrate reconstruction of historical data in createdBoxes query
• get production DB dump
• run performance analysis (nr of DB reads/MySQL server response time)

Metrics:

• https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html

SELECT
   schema_name,
   digest,
   COUNT_STAR AS total_queries,
   SUM(NO_INDEX_USED + NO_GOOD_INDEX_USED) AS queries_without_index,
   SUM(rows_read) AS total_rows_read
FROM
   performance_schema.events_statements_summary_by_digest
WHERE
   schema_name = 'your_database_name' -- Replace with your database name
GROUP BY
   schema_name, digest
ORDER BY
   total_rows_read DESC;

We want the StatViz back-end to access the replica.

https://cloud.google.com/sql/docs/mysql/replication