Closed AngelaKabari closed 1 year ago
The data was synced manually using Postman but the dashboard does not show any data. Below are the links for the plan reports:
We will need to get help from someone on the Canopy team to understand if the data was synced to canopy and why it does not show up on the web. I can confirm that the data is present in the OpenSRP database
cc @AngelaKabari
Originally posted by @ekigamba in https://github.com/opensrp/support/issues/272#issuecomment-1347906516
Plan has been synced from opensrp to the canopy tables, both the raw and transactional tables
reveal1=> select count(*) from thailand_prod.raw_plans where id = '5bc3f9c7-5763-5645-a6df-f457c86f03dd';
count
-------
1
(1 row)
reveal1=> select count(*) from thailand_prod.plans where identifier = '5bc3f9c7-5763-5645-a6df-f457c86f03dd';
count
-------
1
(1 row)
reveal1=> select count(*) from thailand_prod.plans_materialzied_view where plan_id = '5bc3f9c7-5763-5645-a6df-f457c86f03dd';
count
-------
1
(1 row)
Checking if the slice used in the webapp includes the plan
@ekigamba Is there a filter in the tasks endpoint that allows one to retrieve tasks related to a specific plan in this case 5bc3f9c7-5763-5645-a6df-f457c86f03dd
rest/task/sync
allows one to get tasks for a specific plan, group and/or ownerTasks are in sync between the opensSRP and Canopy DBs
OpenSRP DB
opensrp=# select count(*) from core.task where json ->> 'planIdentifier' = '5bc3f9c7-5763-5645-a6df-f457c86f03dd';
count
-------
1
(1 row)
Canopy DB
reveal1=> select count(*) from thailand_prod.tasks where plan_identifier ='5bc3f9c7-5763-5645-a6df-f457c86f03dd' ;
count
-------
1
(1 row)
Thanks @HusnaHariz I will be manually uploading the tasks from the user given the new information
The client database was provided and only 531 of 534 events are marked as synced
There are other issues noted:
Originally posted by @ekigamba in https://github.com/opensrp/support/issues/272#issuecomment-1333720141
I have uploaded 190 tasks to the OpenSRP server using Postman
Endpoint {{url}}/opensrp/rest/v2/task/add POST
Queries used to extract data from the DB:
Extract Tasks as JSON Payload
SELECT group_concat(jsonn) FROM (SELECT '{"identifier": "' || _id || '","planIdentifier": "' || plan_id || '", "groupIdentifier": "' || group_id || '", "status": "' || status || '", "businessStatus": "' || business_status || '", "priority": "' || priority || '", "code": "' || code || '", "description":"' || description || '", "focus": "' || focus || '", "forEntity":"' || for || '", "authoredOn":"' || strftime("%Y-%m-%dT%H%M", datetime(authored_on/1000, 'unixepoch')) || '", "lastModified": "' || strftime("%Y-%m-%dT%H%M", datetime(last_modified/1000, 'unixepoch')) || '", "owner": "' || owner || '", "syncStatus": "' || sync_status || '", "structureId": "' || structure_id || '"}' AS jsonn FROM task WHERE sync_status = "Created")
SELECT group_concat( "'" || _id || "'") FROM task;
SELECT count(*) FROM core.tasks_metadata WHERE identifier IN ('03dfcf46-bc40-46b5-819c-2aebf990621a','0439ede9-a093-4d26-a38d-a5f1d747e85f','05b24d88-3dbc-450c-af02-33737d0b3209','0723adb5-577a-47dc-934e-01815ae8ca69','08c2720e-b1f5-49c5-b2ca-a7f85b58a282','09e1d6f7-a9bd-47d4-a10d-378c553a1820','09ef56f0-251d-4af0-9fdd-ad01b124dcc7','0adf7704-ef0a-436a-873d-68d8aa839c28','0b959762-d27c-4712-b901-3cdeeaa8a7c5','0d4a0ea8-1a95-4cef-8f33-7921f19f1b31','0f5ca11b-64c6-4876-aa28-1896f6112fd9','108c44a4-7ee5-40ab-8cd9-1a6dfca8d051','1171a16a-bf00-4610-baf7-754fb381669c','13e042bc-d7ff-4a82-8859-e79c1f46291a','14cb5922-c917-46a6-b353-863999b0101b','15e4bcd6-f9a8-4856-9c57-d358b467bd64','174cdd62-0e0f-43f1-bf76-8f708b4ad18a','1948426b-b6b7-4240-a3b0-e997d8248a9d','1b236f93-f47b-4f3a-8a8d-804a31c221e0','1b4f18ef-c58e-4927-9714-53c5b3ee6891','1bdff423-2b9d-4c64-80ba-aac6d66e4100','1e6a6a55-74e4-4451-89a8-37cf3f999cd7','1ea7bf63-3a11-4fbc-a322-d54221fb7ed0','2005e408-b223-4b58-9e0e-63af9e803bc9','2201812b-5b2a-462b-8549-40ce31fc4cf2','222b0683-c73b-4fac-8c0e-be628c17281a','23f98cbd-c93e-4f64-a9d1-d1f12c3d72f3','24b0df0c-5455-44e0-bf45-bbb3545db095','2663a5e9-40e8-4000-97f2-4452b47402e2','26755831-40a4-4d6c-a7f8-dd52b7566172','26bef3a6-e048-4035-9fbb-0c8fbbc6a02a','288c91f1-9a15-4470-b9d0-2a905a9cce52','29bf7b37-46db-436b-b43c-6f1e95106d14','2a7ee5c6-c186-42c2-bc9c-41b97926818e','2acd265b-dcfc-41e0-b6a4-fb1f1af59099','2e03da66-7437-405c-a8b8-b75f319586ed','3298df33-24cc-4461-a2d9-99677d88f4bb','34aea8ce-9e5c-4e57-ba89-d5d409f660bd','36966924-6bc3-4f60-87d0-33ff9108f161','37737e27-31b7-4b6a-985c-0dbcc71c5885','39303c23-6447-4a59-8e15-c07e48958382','3adba688-188a-4b29-bba9-8415d28d7287','3cc8b694-a674-4035-9cd4-46af867938b9','3cede40a-d28c-4fb3-9961-103dc5177f39','3e58cd15-a58d-4dc8-ba07-4d26d967aad8','3f2677ce-a2b4-426d-b7fa-460d22a8a9e3','3f4969b5-02ae-4d27-8813-dcca0a74d137','3f95ec7f-93f2-49ca-b301-5d12721eda15','40871989-2035-44ea-81b3-0cf1770c2dd6','43d44b4e-8224-495e-a64d-bdaae1c01afc','44048393-f05c-47dc-8a23-591ae1c1f001','451fb595-3da1-49e9-9a96-69a9b5d79861','46467902-c67f-4c87-81f9-6a916e74fdac','46e943b6-9046-454e-b178-af9d736897a8','473c7a60-59ad-495e-9ce2-14b34a052992','47a15eee-d003-4f51-86b6-8661eb021176','4830fcfb-96b0-4667-a17b-1698812d6249','4889e4a5-4269-479a-b41c-6ef3871575f3','489be79e-be01-4f0a-8518-cc1a9b150e22','492f6be7-a0a2-40e8-88cf-34ddedfe1780','494f7994-4efd-45ae-886e-7c2181f6d61b','4bc1fabc-a909-40f3-b8e3-77d0823f76eb','4cb97d51-13ff-4860-ba39-c5840b16e519','4db210a1-fc2d-470a-a523-7ac79d36169d','50ecd2ba-457f-4a0b-ae26-feef66af0253','5160bc99-3863-4a8b-b9e9-66e158b09b9d','526a668c-2de4-417d-8780-5ef4401f3f5d','52fb8a8d-cb87-4340-8ddb-d8b2bf731f0a','53a56730-75ee-41d6-8cb6-7b07c69095b0','540d84d4-ad6f-4069-bdc6-6c5a2847502b','54143175-d07e-472f-9ac3-4aa3210a6250','548206f7-f8d1-4408-a888-a193ebca74d7','5613a245-7dc5-4a4c-8004-0b47b7e3295d','56266d67-fa99-4e9d-8651-7c4ebaf12382','58ce40e6-f4cb-4615-b2dc-a96c297bfc57','58f4a5ee-b2b0-4972-bb86-0ac7a6418dd0','5adf0311-ef7e-4159-b0b3-8ae911c56f1b','5c6629e2-ed0c-4d3f-9def-e30e1ba0d260','5c8918b4-0877-4910-8ab9-cb05876dfcd0','5dcff467-d234-4001-a573-90dce10a622a','5f230736-67c2-4dcc-a6ab-488278edcbfa','6032c76f-8e3a-4dd6-8ac9-5cc5795c57d0','60354d0c-fb3d-4d76-b603-271bcdb1c75b','604dc4a9-7281-4e57-882d-f6e29f8e27c3','614393f9-e9c4-4a68-ba7f-ee4818012c9a','66301d20-74a0-4592-b9b0-103b81dea5d8','666e0957-2de5-4f4a-a4bf-6f060687073e','669f4d60-a820-464b-ab63-4735d34b5b8b','6ad04ecd-c34a-4bbf-82a2-9a8fd01b9dce','6c244a4c-8b85-4ad3-aa3d-f74713738d9c','6c6fff2b-cc35-4bc6-b823-fc3d6b10240a','6ec90e8c-999b-4fb9-85ea-ac011320800a','6ff4cc54-2c5c-4e73-9d92-f8ff2e30f2d4','70526176-9906-4149-8aeb-9be42ab42eb0','7154302c-d6e8-4778-96c6-51e758fbd247','717532ce-14d1-4f5c-95c3-7cd2021b3990','73b3f78d-4708-4b20-a2e3-d5a29a9a8b23','7627c2fe-1841-44d1-b068-44c662fc03fd','780cbddf-3095-48b4-b278-37659dab26df','788ffdcc-a54f-4bed-8f89-6a49e27f650c','7a0da883-dcc4-4392-bb93-600f65c0baa3','7b1449c8-f830-4e29-bf76-b6d1a5747b4d','7c5a7a22-a30b-4571-b851-338bc95b06a1','7d01add8-214d-4b7e-820c-176339ef46d3','7d15f043-594d-4d57-9adb-edaf6cb39145','7e6e120a-dc35-42aa-b301-1e645bfe0d94','7ed42171-eb61-4a45-a8a8-2e76dd4280a6','805c8028-73a1-42d3-b2b8-e043863a1bb6','809132dc-a755-4ae8-97b7-603497debed4','839f7a17-ce19-4d7d-8a06-e05fb6201449','84ec3319-32f6-4405-a065-af62ecd4e4c2','86a7dc28-2a33-438f-8e2b-b1700478e3cb','86f04f51-adbe-4964-b9b1-f558ede4b8dc','8960e4b8-3270-437d-8be7-8c42604b2a0a','8b39d876-1fd1-43ee-8422-01f75fc6c3bf','8cce317e-47cb-4c54-a4b0-4991ac08fe5b','90691890-dd53-4867-873d-347059a258e4','90bc9fd8-d597-4843-aab1-6dd11f111d9d','960f15b7-4133-4c49-9f43-79aac9f809e5','978bed0d-3f76-4f3f-bc17-8440a50f3457','981191e6-3a40-49d5-8615-8c96238de920','9b59db6f-887e-4898-bca0-84510b99da49','9fe62cc3-808e-46c1-b85d-06ef9941d139','a0f8ce57-c039-4e5c-9723-ffe92007431a','a13a971e-da06-428c-9ec3-d3afcd4a45b2','a14350c3-3020-4eb7-a0f4-61542d96edb2','a19a14c2-3e07-4c06-a1b8-0c911157cc64','a19c96dd-be0b-478c-8186-bae59195089b','a22587aa-9aa8-48eb-8746-c687d9eb6528','a320781b-03a5-455f-bdea-11281c1b9d28','a5f40d7d-ee59-4870-b344-ca4da339ba25','a7ec068d-8fbc-4b19-b0fb-75e3d5b3a0fb','a86ce8f8-bfb0-46d5-ae4f-9bda5c546abf','a977a21e-7ef5-49da-90b5-2d208f662009','aa36c271-06b6-40c5-a502-ec5523dd6b7e','af781a14-797f-433c-bf18-6459a47a4900','b0052a53-aa8e-4c99-89f0-dfc60ab03bae','b07b19bd-0e8a-4bc9-9f67-55ca1b02b9ee','b34adb96-149e-4356-8007-5b97f9f2411b','b3ded213-75a4-4a48-95ef-edc3a188420b','b45a5430-55fd-44de-88f7-d23f9ae0c7bd','b5a9479e-3649-4057-8cab-8c6d25ebecfa','baba412e-54ce-407f-826d-244c052d571c','bbe0c395-bbd1-40de-a84c-f81f37c2f3bb','bd7a71af-75e5-422b-97b7-943c395de89b','beb8e11c-6489-4733-85d8-32e039295064','bee557d2-8ef3-4483-8633-052efcaf0a1e','bf587681-78c7-4333-9de6-9a8af00623da','c02dab12-80f7-4cd2-aaf8-737fb9bb7b13','c0bd6da7-2a9d-4070-b40a-136013c56280','c11aec6a-6abf-4df5-800a-28d124bbcaef','c1d84f24-2fe5-4fb6-833e-42716baa8bbe','c347db28-0ae0-49ba-9727-abb0f4be0fac','c45b5df2-667f-487c-9a56-855a29563bb7','c4daf1be-a133-4ba6-b8ed-d7b8b6082f37','c58e8483-e155-47d9-8c72-efeafabb0aac','c6338d70-6206-425d-9091-41365585df0a','c68f9fae-a1d4-4d00-bb03-ec435ebe1954','c772711f-0877-4e39-80e0-853c14b8044d','cb225771-b59b-4da9-9562-b44dac424f49','cbe5f22d-ed41-4d5a-a4c2-b8f65f168788','cc28a8e4-e45a-4313-8d28-585b13f851c7','cd3537de-95fd-4f30-a3ac-2b3099eba160','d3b58896-c532-4468-9b28-d0a63d841239','d6855170-613f-4574-a30f-260dd8879dec','d6986d77-edf6-4649-8713-4a2e8851f950','d9917130-26e9-4c51-b822-c4a9fa89c168','db05a5cc-0fa0-46f4-9d04-d76e80b93cd2','dd96e1c6-782d-4eed-80b9-e8998a45c3b3','de2ed966-e0af-4a12-9af9-da90a54e4fee','de37dab5-df52-4d76-819d-72b66b244297','de7f3e59-e908-464d-88c1-dbaa4eab936e','e44a380e-9622-40e0-b53b-8acf9af77030','e5f77262-7e2a-42ef-9ab7-305ee4b4f1b4','e70767a7-c67a-4255-b0c3-4e26616f4764','e97849d4-2448-4575-89ba-565d89183f0a','e9a576f0-3c77-4bca-a805-e2dc16e6468a','ea4b1e14-1ddb-49e5-b6f6-8deb92b83759','ecd10544-ae31-442f-8094-47bb8c92947b','ee26843c-6050-4be0-8f34-f671450a9fd8','ee42edc8-3680-44a6-ba8a-40b814ec56b8','f11c64f7-6d3d-49bd-ad0f-55a96b2c5eec','f11cc9f5-aff9-4a73-97b5-b8aac6806c32','f4b249cd-6c27-4227-8f87-f79d60a00297','f596386a-2eef-4983-b193-d6de037c8542','f809b9dd-b80a-4174-9615-1460c782a4b4','f85923c8-d42b-4439-ae28-f771620c229e','f89c1be6-eb45-4dfd-8904-c9fef1f3055c','fa24a1fa-4f4c-41b1-aca2-dff6b000d483','fa6ab892-091f-4a72-8845-36b37021b06e','fbd421ee-d7b5-4f44-8bea-ef6e4ec23e77','fe0d2ac3-5d4e-4dce-a7ed-1495b654790c','ff2da091-435f-4148-b444-9f21b2ab6d5a')
Canopy needs to pull the tasks and build the materialized view from which the API makes the query calls. Canopy pulls data from OpenSRP every 5 minutes but the materialized view takes time to build and might require to be refreshed according to @HusnaHariz
Thanks for this @ekigamba. I'll check tomorrow and see whether the tasks are now visible on the dashboard.
Hello @ekigamba I confirm that I can now see I confirm that I can see the progress on map view page (https://mhealth.ddc.moph.go.th/focus-investigation/map/7015b227-1a4a-56b9-b5c4-9c60e4b151db). However, when I look at the plan view page (https://mhealth.ddc.moph.go.th/focus-investigation/view/5bc3f9c7-5763-5645-a6df-f457c86f03dd) I get the error ”An error occurred, please try refreshing the page. The specific error is : Could not load points on map.” (see below screenshot). The view page also isn’t loading the plans and activities.
@HusnaHariz please look into the above error and let me know what’s causing it, and what the fix would be?
I have also noted that while the map view page is displaying the progress towards goals, the GPS points aren't loading and showing on the map.
See below an example screenshot of what should be displayed:
vs. what is being displayed on the map view page:
structures
table) JSON column contains everything required for synccc @AngelaKabari
This means that I need to upload the structures from the users data dump
Thanks for the update @ekigamba. Let me know when this is complete?
@AngelaKabari I have uploaded 99 structures to Reveal for this user. This should have reflected by tomorrow morning
Hello @ekigamba I've just checked the Reveal Web Dashboard and the structures are still not displaying in both focus investigation plan pages (views and map). Kindly take a look at it again?
All the structures are in the Reveal production database
Thanks @ekigamba let me loop in the OpenSRP Web Team to help troubleshoot this.
8
.The request to fetch the tasks data fails with a 403 error.
https://supersetmhealth.ddc.moph.go.th/superset/slice_json/8?form_data={%22adhoc_filters%22:[{%22clause%22:%22WHERE%22,%22expressionType%22:%22SQL%22,%22sqlExpression%22:%22(plan_id%20=%20%275bc3f9c7-5763-5645-a6df-f457c86f03dd%27)%20OR%20\n%20%20%20%20%20%20%20%20%20%20%20%20(jurisdiction_id%20=%20%2794c94f23-568a-4467-8959-5d02b8f9f49f%27%20AND%20goal_id%20=%20%27Case_Confirmation%27)%22}],%22row_limit%22:250}
Running it on the browser yields this view:
@AngelaKabari I understand we do not manage the firewall, I would suggest reaching to the client's technical team to confirm why they firewall fails this request. cc @bennsimon .
@p-netm I shall check with DVBD what's going on with the firewall as it is also created #15 where routine action plans are not being generated.
Once that is resolved then we can pick this up.
Hello @p-netm the Reveal Thailand system is back online so let us pick this us again next week.
@AngelaKabari
5bc3f9c7-5763-5645-a6df-f457c86f03dd
do not have a geometry in their geojson.cc @HusnaHariz
Thanks for getting to the root of this. @p-netm I think the missing geometry in the geojsons for the tasks might be stemming from the manual addition of those tasks to the database from the user's data dump. Let me consult @Rkareko and @ekigamba on this before we loop @HusnaHariz back in.
@AngelaKabari I can confirm that I uploaded the structures containing the geojson. The tasks reference these structures.
Is there someone who can explain why this was happening?
On the map page mhealth.ddc.moph.go.th/focus-investigation/map/7015b227-1a4a-56b9-b5c4-9c60e4b151db, I can see that the page is loading the 99 structures from the network requests but they are still not displaying once you click on the household registration
cc @Rkareko
@ekigamba is it possible to share some of the manually uploaded tasks ans structure ids, so we can try track them in the warehouse?
Had a chance to look at this yesterday with a lot of help from @HusnaHariz @lincmba and @ekigamba . Let me know if I missed anything or need to add more contextual info.
so what we knew:
The tasks from superset do not have geometries.
The tasks were manually uploaded to the OpenSRP db .
What we checked:
Identified the the data source to the tasks slice on superset, which is task_structures_geojson_slice
task_structures_materialized_view
materialized view.tasks
and locations
based on task.task_for
and locations.locationId
task_for
field set to NULL.Checked NIFI to see what field in the raw_task
table is mapped to the tasks
transactional table for the task_for
field.
Noticed the raw_tasks were missing the geojson ->> for
field.
Confirmed the tasks in the opensrp db were also missing the geojson ->> for
field.
Regenerated the tasks with the correct values for geojson ->> for
and re-uploaded them to the opensrp db.
Re-ran the ETL to update the raw_tasks
in the canopy warehouse, and also updated tasks data being served by the task_structures_materialized_view
cc @AngelaKabari
Thanks @p-netm both for your hard work and for this very comprehensive explanation. I appreciate the level of detail. Thank you too @ekigamba, @lincmba, and @HusnaHariz for your valuable input into troubleshooting and resolving this.
I have checked and the structures are displaying as they should on Reveal Web.
@pongthepm kindly ask DVBD to confirm that this has been satisfactorily resolved so that we can close it out?
What is the nature of this Technical Support Request? Please describe the need/requirement in detail
(A clear and concise description of what the problem is)
A member of the DVBD field staff is experiencing a problem with a plan id: 5bc3f9c7-5763-5645-a6df-f457c86f03dd with user: vbdu_2.1.1-1. They filled in the data, then sync completed, but there is no data on the web and dashboard.
Describe the solution you'd like
(A clear and concise description of what you want to happen)
Describe alternatives you've considered
None.
Additional context
(Add any other context or screenshots about the request here)
None.