refactor :Update the DB

[ACU017]

This is not for our team but please use the DatabaExt.txt where everything is explained.

Just in case I repeat the info here :

Question number 1 - URL names

• Option 1 : reusing the numbers 

/Focus-objective1/KeyArea1/expected-result1/a

• Option 2 : Semantinc Url for imporved accesibility 

/homepage/         /Focus Objective Name /Key Area ID name/Expected Result title / 

/target-dashboard/protection-of-livestock/risk-monitoring/fast-global-surveillance

Database needs: 

*Change table names for columns : 

• focus_objective table

divide focus_objective_name into 2 

focus_objective_name and  focus_objective_subtitle that we will use to show the full text 

ex see the PERFECT OBJECT bellow 

• targets table *  rename to = target so that we can grab target.id 

indicator would a letter 

target_description can be just description and remove the "i)"

Restructuring the Json elements

We need to destructure : expected_result and indicator so that they are numeric rather than string/text

This is our current call and our current Object we receive from the DB 

SELECT * FROM targets WHERE key_area_id = 1 AND expected_result LIKE '1.1%'

result 
 {
        "id": 1,
        "key_area_id": 1,
        "expected_result_number": "1.1.FAST global surveillance sustained and viral intelligence up-scaled ",
        "indicator": "1.1.1aNumber of reports on global FMD surveillance produced\n\n                   ",
        "target_description": "i) 16 quarterly reports and 4 annual reports",
        "Q4/2023": null,
        "Q1/2024": 3,
        "Q2/2024": null,
        "Q3/2024": null,
        "Q4/2024": null,
        "annual_target": 16,
        "program_target": null
    },

This is our current call for the ideal Object 

SELECT
  fo.id AS focus_objective_id,
  fo.name AS focus_objective_name,
  ka.id AS key_area_id,
  ka.name AS key_area_name,
  t.id AS target_id,
  t.indicator,
  t.target_description,
  t.expected_result,
  t.annual_target,
  t.program_target,
  t.\`Q1/2024\`,
  t.\`Q2/2024\`,
  t.\`Q3/2024\`,
  t.\`Q4/2023\`,
  t.\`Q4/2024\`
FROM
  focus_objectives fo
INNER JOIN key_areas ka ON fo.id = ka.\`focus_objectives_id\`
INNER JOIN targets t ON ka.id = t.key_area_id
WHERE
  fo.id = 1 // 
ORDER BY
  ka.name ASC, t.id ASC;
`;

This is the object we receive 

 {
        "focus_objective_id": 1,
        "focus_objective_name": "PROTECTION of LIVESTOCK",
        "focus_objective_subtitle": " Improved protection of livestock sector in EuFMD Member Nations from FAST introduction and spread",
        "key_area_id": 2,
        "key_area_name": "[RISK MITIGATION] Enhance prevention, confidence of freedom, laboratory biosafety to increase protection against FAST diseases",
        "target_id": 19,
        "indicator": "2.1.1.a Number FAST and biosecurity awareness material produced and provided to farmers/private vets/industry \n\n",
        "target_description": "i) 8 Innovative communication and awareness material developed (video, social media comms campaign): 4 for FMD and 4 for priority FAST, 3 for biosecurity (LR, SR, Pig farms)\n(Amplified Disease Awareness:\nImpact Target: Develop and disseminate 8 innovative communication materials (videos and social media campaigns), generating a 30% increase in views, downloads, and shares. This will elevate public awareness about FMD and priority FAST diseases, as well as enhance understanding of biosecurity measures on Livestock Farms (LR, SR) and Pig Farms.\nMeans of Verification: Track metrics such as views, downloads, shares, and comments on various platforms.)\n",
        "expected_result": "2.1.Improved livestock biosecurity in MNs",
        "annual_target": 8,
        "program_target": null,
        "Q1/2024": 0,
        "Q2/2024": null,
        "Q3/2024": null,
        "Q4/2023": null,
        "Q4/2024": null
    },

We need to divide the expected_result in 2 to include expected_result_number

and

if we have targets renamed as target  all is good

    The IDEAL Object would be or rename the table targets to target I've marked with a star ("*") all the changes

{
     "focus_objective_id": 1,
       * "focus_objective_name": " Improved PROTECTION of LIVESTOCK sector in EuFMD Member Nations from FAST introduction and spread",
       * "focus_objective_subtitle": " Improved PROTECTION of LIVESTOCK sector in EuFMD Member Nations from FAST introduction and spread",
        "key_area_id": 2,
        "key_area_id": 2,
        "key_area_name": "[RISK MITIGATION]"
       * "key_area_desc": "Enhance prevention, confidence of freedom, laboratory biosafety to increase protection against FAST diseases",
        "target_id": 19,
       * "target_indicator_id":a
       * "target_indicator_description":" Number FAST and biosecurity awareness material produced and provided to farmers/private vets/industry \n\n",
       * "target_description": "i) 8 Innovative communication and awareness material developed (video, social media comms campaign): 4 for FMD and 4 for priority FAST, 3 for biosecurity (LR, SR, Pig farms)\n(Amplified Disease Awareness:\nImpact Target: Develop and disseminate 8 innovative communication materials (videos and social media campaigns), generating a 30% increase in views, downloads, and shares. This will elevate public awareness about FMD and priority FAST diseases, as well as enhance understanding of biosecurity measures on Livestock Farms (LR, SR) and Pig Farms.\nMeans of Verification: Track metrics such as views, downloads, shares, and comments on various platforms.)\n",
       * "target_expected_result_num": 2 
       * "target_expected_result_desc""Improved livestock biosecurity in MNs",
        "annual_target": 8,
        "program_target": null,
        "Q1/2024": 0,
        "Q2/2024": null,
        "Q3/2024": null,
        "Q4/2023": null,
        "Q4/2024": null
    },

[ACU017]

Miro Explanation for FS dev

[irenezatti]

@ACU017 add the data science team please! :)