Link Planter Organization-id to Organization Map

[Davidezrajay]

The organization map should be driven from the planter's organzition_id.

• The organization map should not be linked to the Certificate/token.

Suggestions:

1. Every tree should be nested under an organization _id.... manually populated (user_id, or device_id, or another table).
   It is manually populated because users often spell their organization incorrectly or often add organizations that do not exist.

2. All trees that are assigned under the organization_id show up under the map www.treetracker.org/?organization='X'

Possible Problems:

• In many cases, users have multiple user_id.
• removing the organization map from the current certificate_id process: org/donor/certificate may break all the links that have the www.treetracker.org/?organization='x'
• If the user leaves an org... all their trees will leave as well. A possible solution is to add the organizations_id under each device_id. Most devices do not change users.

[ZavenArra]

organization should be linked to trees, not via user. However this can happen when trees are uploaded - if a user is part of an org, then this can be copied to the trees. We can also make scripts to retroactively assign trees in orgs when you update the user with their organization id.

[Davidezrajay]

Exactly. Let's leave the retroactive script out for now.

If the organization_id field in the user table is not void, the tree is tagged with that organizaiton_id
For now, we can retroactively do in manually. When a new user shows up thier trees won't be tagged with any organization id until we have verified the organization id and entered in manually in the user table. (Lots of users seem to spell the organization's name incorrectly.)

[ZavenArra]

And this is always the planting organization right? The trees will need a column 'planting_organization_id'

[Davidezrajay]

yes

[petersenpdx]

Are we able to close this one? I think so

[Davidezrajay]

Notes from Slack for Refreence... _The planting_organizations_id in the trees table is not pulling from the entity table, it is linked to the organization table?

Ezra 2 hours ago
Ok... tried to add the that but it doesn't seem to populate anything.

Mark 2 hours ago
Should happen relatively fast... Within seconds but I am not sure with the cluster involved if it needs to update the other servers. (edited)

Mark 2 hours ago
I think

Mark 2 hours ago
@Ezra Its certificate_id column in the tree_table that is controling the map.

Ezra 1 hour ago
This is going to take a long time when they have 4 million trees... atm changing 2281 Certificate_id numbers starting now

Mark 1 hour ago
We need to create a function that transfers the org_id into the tree table depending on the Planter_id planter_org....

Ezra 1 hour ago
So in this case, we have all the planters assigned to the Justdigit entity_id. This would autofill the planting_organizations_id ?

Mark 1 hour ago
Then we just map the planter_org instead of the certificate

Mark 1 hour ago
Right

Ezra 1 hour ago
#5

DavidezrajayDavidezrajay
#5 Link Planter Organization-id to Organization Map
The organization map should be driven from the planter's organzition_id.
• The organization map should not be linked to the Certificate/token.
Suggestions:

1. Every tree should be nested under an organization _id.... manually populated (user_id, or device_id, or another table).
   It is manually populated because users often spell their organization incorrectly or often add organizations that do not exist.
2. All trees that are assigned under the organization_id show up under the map www.treetracker.org/?organization='X'
   Possible Problems:
   • In many cases, users have multiple user_id.
   • removing the organization map from the current certificate_id process: org/donor/certificate may break all the lin… Show more
   Comments
   5
   https://github.com/Greenstand/treetracker-database|Greenstand/treetracker-databaseGreenstand/treetracker-database | Oct 4th, 2019 | Added by GitHub

Mark 1 hour ago
Then planting_organization_id would just map like the certificate does now

So the ticket should actually say something like:
The 'planter_organization_id' should be linked to the entity_id.
The planter_organization_id should map like the certificate does.

It is almost like we thought about this one before #4

https://github.com/Greenstand/treetracker-database|Greenstand/treetracker-databaseGreenstand/treetracker-database

https://www.treetracker.org/?token=LEAD_

[petersenpdx]

@deepwinter (or anyone else) - Can you give a little technical insight on this being feasible or the best way to go about it.

Goal: To have a map that will automatically show the amount of trees on the map that organization has planted.

Possible Solutions
1.In the tree_table, update planter_organization_id by inserting CREATE TRIGGER Function and pg_NOTIFY:
CREATE TRIGGER Insert_planter_org_trigger
AFTER
INSERT on tree
SET planter_organization_id = planter.organization_id
FROM planter_table
WHERE tree.planter_id = planter.id;_
Then all planter_organization_id would have the correct value based on whomever the planter is... The next step would be just to add in a url like we have for certification column on tree_table. The url can be documented on the entity_table.
2. We just will have to do it the way run person trees now in entity_id and add tree_id to token table. Planting organization will have own there trees to show on a map. The issue here is that we would have to manually transfer trees to planter_organization wallet AND this would only show if they OWN the tree, not on how many a planting_org planted.
3. Using the admin panel to update approved (or all) trees with a planter_organization associated to the id in planter table.

There are plenty of ways on doing this. I am open to whatever is going to work best. Just wanted to put the idea out there before I started working on it.

[ZavenArra]

planting_organization_id can be populated during the initial INSERT into the trees table. We look up the planter by their identifier to get their user id, so we have their planting org id also IF it has been populated.

how do you want to handle this if planting_organization_id has not been populated yet on a planter record? planting org id will be null, or we could flag that each tree with a new field to or planting org id (-1 ? ) to indicate that the planter did not have an org assigned.

[petersenpdx]

Ya.. I think that assigning the planter_organization_id something like -1 or 0 would be the best. The concern with NULL would be that since the initial INSERT is done for every line item that is populated, then if a change is made to the organization_id on the planter_table would not change the planter_organization_id on tree_table. In order to do this we would have to made a update trigger.

Although, I think I'll start with the initial INSERT on the tree_table and see things are after.