HACKERTHON DATA SOURCE

gistfile1.txt

HACKATHON CONCEPT NOTE (what is the hackathon about?)
https://bit.ly/37S2TMp


Presentation on Open Contracting
https://www.canva.com/design/DAEQFB42Wn4/xlA_-SRHpPFWdjOH74OVRw/view?utm_content=DAEQFB42Wn4&utm_campaign=designshare&utm_medium=link&utm_source=publishsharelink

--------------------------------------------------------------------------------------------------

DATASETS (FOR HUMANS)

All Procurement Plans
https://gppdata.s3.ap-south-1.amazonaws.com/procuremet_plans_updated_Dec_9_2020.xls

All Awarded Contracts
https://gppdata.s3.ap-south-1.amazonaws.com/awards_updated_11_dec_2020.xlsx

-----------------------------------------------------------------------------------------------------------
GPP API

List of all PDES
https://gpp.ppda.go.ug/adminapi/public/api/general/pdes

List of procurement types
https://gpp.ppda.go.ug/adminapi/public/api/general/procurement-types

List of sectors
https://gpp.ppda.go.ug/adminapi/public/api/general/sectors

List all Financial Years
https://gpp.ppda.go.ug/adminapi/public/api/general/financial-years

Fetch total value of procurement plan by year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/planning/fetch-plan-value?fy=2020-2021 

Get tenders number and value by financial year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/tender/tenders-by-year?fy=2020-2021

Get all tender notices by finacial year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/tender/notices?fy=2020-2021

Count all signed contracts by financial year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/contract/signed-contracts-by-year?fy=2020-2021

Count all completed contracts by financial year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/contract/completed-contracts-by-year?fy=2020-2021

---------------------------------------------------------------------------------------------------------------------

OPEN DATA 
Get summary vy financial year //fy is a variable e.g use ?fy=2018-2019
https://gpp.ppda.go.ug/adminapi/public/api/open-data/v1/summary?fy=2016-2017

-----------------------------------------------------------------------------------------------------------------------

VISUALIZATIONS
https://public.tableau.com/profile/cengkuru.michael#!/

https://open-data.africafoicentre.org

------------------------------------------------------------------------------------------------------------

WEBSITES

Registry of Providers
https://www.ppdaproviders.ug/providers?sl=2

Government Procurement Portal
www.gpp.ppda.go.ug

SET SQL_MODE = '';
SELECT pe.entity_name AS pde_name,
pb.procurement_reference_number AS procurement_reference_number,
IF(uc.family_name IS NULL, ppi.item_description, uc.family_name) AS category_of_procurement,
pb.subject_of_procurement AS subject_of_procurement,
(CASE
WHEN pe.entity_group = 'Central Government' THEN ppm.procurement_method_name
WHEN pe.entity_group = 'Local Government' THEN plgpm.procurement_method_name
ELSE '' END) AS procurement_method,
DATE(pcbeb.public_publish_date) AS actual_date_of_award,
DATE(pcw.updated_at) AS contract_committee_approval_date,
fprov.provider_name AS name_of_provider,
pcbeb.lot_id,
fprov.id AS provider_id,
pcbeb.provider_id AS pcbeb_provider_id,
pfap.provider_id AS pfap_provider_id,
pb.awarded_provider AS pb_awarded_provider,
pb.currency AS currency,
pcbeb.contract_amount AS beb_contract_amount,
pfap.amount AS contract_value,
pcbeb.contract_end_date AS completion_date,
ppt.title AS procurement_type,
pb.estimated_amount AS estimated_amount,
pcbeb.contract_type AS contract_type,
pcpo.warranty_gcc_29_3 AS warranty,
fprov.account_type AS country_of_origin_of_the_provider,
DATE(pcbeb.solicitor_general_approved_on) AS date_of_sg_clearance,
sfsp.name AS source_of_funds,
'' AS exchange_rate,
DATE(ppi.contract_signing_date) AS contract_signing_date,
pb.financial_year,
prr.location_of_delivery AS location_of_supplies,
FORMAT(IF(pcmpw.proportion_of_payments_to_be_retained > 0, pcmpw.proportion_of_payments_to_be_retained, 0),
0) AS retention_fee
FROM egp_pde.pde_contracting_best_evaluated_bidder AS pcbeb
INNER JOIN egp_pde.pde_bids AS pb ON pb.id = pcbeb.bid_id
LEFT JOIN egp_pde.pde_framework_agreement AS pfa
ON pb.id = pcbeb.bid_id AND pcbeb.lot_id = pfa.lot_id
LEFT JOIN egp_pde.pde_framework_agreement_providers AS pfap ON pfap.framework_id = pfa.id
LEFT JOIN egp_supplier.app_providers AS fprov ON fprov.id = pfap.provider_id
INNER JOIN egp_pde.pde_contracting_workflow AS pcw ON pcw.beb_id = pcbeb.id
LEFT JOIN egp_pde.pde_entities AS pe ON pe.id = pcbeb.pde_entity_id
LEFT JOIN egp_pde.pde_departments AS pd ON pd.id = pb.pde_dept_id
LEFT JOIN egp_pde.pde_procurement_methods AS ppm ON ppm.id = pb.procurement_method
LEFT JOIN egp_pde.pde_local_government_procurement_methods AS plgpm
ON plgpm.id = pb.procurement_method
LEFT JOIN egp_pde.pde_contracting_purchase_orders AS pcpo ON pcpo.bid_id = pb.id
LEFT JOIN egp_pde.pde_requisition_request AS prr ON prr.id = pb.requisition_id
LEFT JOIN egp_pde.pde_procurement_items AS ppi ON ppi.id = prr.plan_item_id
LEFT JOIN egp_pde.pde_procurement_types AS ppt ON ppt.id = ppi.procurement_type_id
LEFT JOIN egp_pde.settings_ifms_funding_source AS sfsp ON sfsp.id = ppi.funding_source
LEFT JOIN egp_pde.pde_contract_mgt_plan_works AS pcmpw ON pcmpw.bid_id = pb.id
LEFT JOIN unspsc_codes AS uc ON uc.id = ppi.item_category
WHERE pcbeb.public_status <> 0
AND pcbeb.contract_type = 'framework'
GROUP BY pcbeb.lot_id
ORDER BY pcbeb.contract_amount DESC
LIMIT 50000;