Add fields to partner agency export to support NDBN reporting.

[cielf]

Summary

Add several fields to the partner agency export, as described, to support NBDN reporting.

Justification

Slack message from Judi Meighan #humanessentials Jun 9 "The export on the Partner Agencies Page only downloads 5 fields. When we do the NDBN report we have to upload a file of our partners. It would be great if we could export from this page and then upload that file. Currently we have to add more info manually and all that info should be in HE. We need Address, city, state, zip, website, type of partner, all counties served, all added to the export. We also need an indicator if we're providing them Diapers and an indicator if we're providing period supplies. TIA!"

Details

Add the following to the partner agencies export. Most of this will be found on the partner profiles record:

• Agency address (line 1 and line 2)
• Agency city
• Agency state
• Agency zip code
• website
• Agency type
• list of counties served [blocked until #2899 in place, but if this is done first, we'll do a separate issue for it]
• providing diapers [= are they in a partner group that is allowed to request diapers]
• providing period supplies [= are they in a partner group that is allowed to request period supplies]

Things to know:

This mayhave to be split at least in two, or is blocked -- the counties served is not yet available information. See issue #2899. We have other requests for the address info, though, so it is worth doing that much first.

Criteria for completion

• [ ] tests supporting the updated export
• [ ] all fields described are in the updated export
• [ ] if counties can not be included at this time, add an issue, referring to #2899 as a blocker, to have them added

[cielf]

The counties stuff mentioned is in the PR stage - and we've pretty much missed the window for this years NDBN report.

[sunsheeppoplar]

@cielf would love to take this one even though reporting window is past

[dorner]

It's yours!

[sunsheeppoplar]

@dorner @cielf thanks for assigning. Started looking into this and was wondering if I might be able to get a data dump from production or something. If not, I'm kind of unsure how to approach these two points in the acceptance criteria:

- providing diapers [= are they in a partner group that is allowed to request diapers]
- providing period supplies [= are they in a partner group that is allowed to request period supplies]

When looking at a sample query, I can't really tell how we'd determine that. Is it sufficient enough to look at the name and / or description to sort that out?

Partner.find(1).partner_group.item_categories

I see this bit of view logic that is kind of confusing to me. Is it showing that a given partner can request certain items or be requested of for certain items?

[cielf]

Let's see if I can clear things up on how to tell what a partner can request A partner belongs to a partner group A partner group is allowed to request certain item categories

Pondering this further, I'm wondering if we might want make this so you have a column for each item category -- because different banks divide up their diaper world differently. (In fact, the bank that requested this does not actually divide things up in the same categories as the NDBN report) Would that be as easy?

[cielf]

@sunsheeppoplar please see above

[sunsheeppoplar]

Let's see if I can clear things up on how to tell what a partner can request A partner belongs to a partner group A partner group is allowed to request certain item categories

Pondering this further, I'm wondering if we might want make this so you have a column for each item category -- because different banks divide up their diaper world differently. (In fact, the bank that requested this does not actually divide things up in the same categories as the NDBN report) Would that be as easy?

Thanks, @cielf I think that makes sense to have individual columns. I mostly wasn't sure what we would display. When I run that query from above

Partner.find(1).partner_group.item_categories

the output I get is

Partner Load (2.5ms)  SELECT "partners".* FROM "partners" WHERE "partners"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  PartnerGroup Load (1.7ms)  SELECT "partner_groups".* FROM "partner_groups" WHERE "partner_groups"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ItemCategory Load (2.5ms)  SELECT "item_categories".* FROM "item_categories" INNER JOIN "item_categories_partner_groups" ON "item_categories"."id" = "item_categories_partner_groups"."item_category_id" WHERE "item_categories_partner_groups"."partner_group_id" = $1  [["partner_group_id", 1]]
=> [#<ItemCategory:0x000000011860dce0
  id: 1,
  name: "Category Diapers",
  description:
   "Autem non minima. Et optio quis. Occaecati voluptas facere. At quia illo. Et exercitationem blanditiis. Praesentium architecto possimus. Cum harum qui. Expedita eaque praesentium. Alias eius natus. Ea qui laboriosam. Deserunt sit ipsam. Rerum aspern.",
  organization_id: 1,
  created_at: Sun, 23 Apr 2023 18:23:54.008493000 UTC +00:00,
  updated_at: Sun, 23 Apr 2023 18:23:54.008493000 UTC +00:00>,
 #<ItemCategory:0x00000001185beed8
  id: 2,
  name: "Category Period Supplies",
  description:
   "Ut distinctio nisi. Id consequatur voluptatem. Minus enim consequatur. Omnis nobis dignissimos. Provident quia alias. Reiciendis pariatur quisquam. Molestiae atque non. Alias et voluptatibus. Aspernatur officiis et. Neque expedita laboriosam. Et sae.",
  organization_id: 1,
  created_at: Sun, 23 Apr 2023 18:23:54.021634000 UTC +00:00,
  updated_at: Sun, 23 Apr 2023 18:23:54.021634000 UTC +00:00>]

which leads me to my question, is that correct? are we meant to display the name of each of the item categories or am I misunderstanding that those are the items that they're allowed to request?

[cielf]

Hmm. For maximum clarity, I would think the column heading should be the name of the item category preceded by "Can Request " In the case above 'Can Request Category Diapers' and 'Can Request Category Period Supplies' , with the entry for each partner being a 'Y' or 'N'. It's a bit awkward, but will be clear, and flexible across banks that use different categories. What do you think?

[sunsheeppoplar]

Hi, @cielf, thanks for more details. I guess when would we hit the 'N' case? Aren't all item_categories that belong to a a partner_group the 'Y' case? That's what I'm not understanding about the data I guess?

[cielf]

@sunsheeppoplar

If a partner is not in a partner_group that can order the category, it would be a 'N' Say there are 3 categories diapers, period products, other items Say group 1 is allowed to order diapers and period products and other items But group 2 is only allowed to order diapers If partner A is in group 2, the entries in the columns would by Y N N But if they are in group 1, they would be Y Y Y

Hope that makes things clearer.

[sunsheeppoplar]

@sunsheeppoplar

If a partner is not in a partner_group that can order the category, it would be a 'N' Say there are 3 categories diapers, period products, other items Say group 1 is allowed to order diapers and period products and other items But group 2 is only allowed to order diapers If partner A is in group 2, the entries in the columns would by Y N N But if they are in group 1, they would be Y Y Y

Hope that makes things clearer.

@cielf oh, are we only concerned with the three categorizations: diapers, period products, other? If so that makes more sense now

[github-actions[bot]]

This issue has been inactive for 262 hours (10.92 days) and will be automatically unassigned after 98 more hours (4.08 days).

[sunsheeppoplar]

@sunsheeppoplar If a partner is not in a partner_group that can order the category, it would be a 'N' Say there are 3 categories diapers, period products, other items Say group 1 is allowed to order diapers and period products and other items But group 2 is only allowed to order diapers If partner A is in group 2, the entries in the columns would by Y N N But if they are in group 1, they would be Y Y Y Hope that makes things clearer.

@cielf oh, are we only concerned with the three categorizations: diapers, period products, other? If so that makes more sense now

@cielf sorry been a bit busy at work, just wanted to follow up on this issue

[cielf]

@sunsheeppoplar Sorry I missed answering -- the core need is that they want to be able to indicate diapers, period products, etc. However, since some banks use different categorizations (for instance, they might have things such as "hygiene products" that they want to lump together), I think the best we can do is to have a column for each category -- and if they don't match perfectly the NDBN categories, well, the bank will have to figure that out.

[github-actions[bot]]

This issue has been inactive for 243 hours (10.13 days) and will be automatically unassigned after 117 more hours (4.88 days).

[github-actions[bot]]

This issue has been inactive for 363 hours (15.13 days) and is past the limit of 360 hours (15.00 days) so is being unassigned.

[sunsheeppoplar]

Still able to work on this. I think I can manage something later this week!

[github-actions[bot]]

This issue has been inactive for 263 hours (10.96 days) and will be automatically unassigned after 97 more hours (4.04 days).

[github-actions[bot]]

This issue has been inactive for 383 hours (15.96 days) and is past the limit of 360 hours (15.00 days) so is being unassigned.

[sunsheeppoplar]

@sunsheeppoplar

If a partner is not in a partner_group that can order the category, it would be a 'N' Say there are 3 categories diapers, period products, other items Say group 1 is allowed to order diapers and period products and other items But group 2 is only allowed to order diapers If partner A is in group 2, the entries in the columns would by Y N N But if they are in group 1, they would be Y Y Y

Hope that makes things clearer.

Hi @cielf, sorry I wasn't able to finish this earlier this year. I've been going back over this and trying to look through a visualized schema of the database and I'm not sure if this would just be easier for me to talk to you about during an office hours. For now I'll try and describe what I'm still confused about after rereading our thread...

In essence, what are all or the full list of the item categories that we're concerned with? I understand the yes cases I think given the query I posted before, but what are the ones that we're comparing against for the no cases? Is it all the item_categories that the Organization has? That is, I noticed that PartnerGroup has an organization_id and when calling Organization.find(1).item_categories I see additional ones that aren't included when I call Partner.find(1).partner_group.item_categories

I'm not sure if that's just happenstance because of what data is available in the seed when setting up the dev environment...

[cielf]

Hi @sunsheeppoplar -- one thing before I try to answer your other question.

I had a conversation with NDBN, and out of that, there are some more fields that should be in the export.

Partner Agency Type Other Field (obviously after the agency type field) Contact Name Email Website

[cielf]

@sunsheepoplar -- I get the confusion! I've been getting more into this myself lately. I expect my picture of things have changed in the meantime. Please read the following and see if it makes sense.

So, the question is, are there any items the partner can order that are diapers? How do we know what the partner can order?

• If they belong to a partner group, then what they can order is determined by that partner group. (I'm pretty sure if there are no partner groups, the partners can order any of the organization's items that are active and visible)
• • that partner group has item categories that they can order -- the partner can order any items that are active and visible to partners in that item category.

But how do we know if something is a diaper (similarly for menstrual supplies)

• each item is based on a BaseItem
• each BaseItem has a category
• If that category contains the word "diaper", consider the item a diaper. If it contains the word "Menstrual", it's period supplies.

(When I look at the actual categories, I think that maybe incontinence pads and training pants should be in the diaper category, but I would be surprised indeed to find someone being allowed to get either of those, but not getting diapers)

[cielf]

(Note: we've had a new request for this on Dec 29, 2023.)

[callmarx]

Hi @cielf, I've opened a PR for a partial solution for this issue.

[github-actions[bot]]

This issue is marked as stale due to no activity within 30 days. If no further activity is detected within 7 days, it will be unassigned.