100 lines
4.0 KiB
SQL
100 lines
4.0 KiB
SQL
DROP VIEW pc.opportunity_extract;
|
|
CREATE OR REPLACE VIEW pc.opportunity_extract AS
|
|
SELECT
|
|
id,
|
|
notif_source,
|
|
NULLIF(notif_date,'')::DATE notif_date,
|
|
notif_url::TEXT, --'<a href=' || notif_url || 'target="_blank">Link</a>' notif_url,
|
|
notif_details,
|
|
oppty_desc,
|
|
oppty_source,
|
|
NULLIF(oppty_date,'')::DATE oppty_date,
|
|
oppty_url::TEXT, --'<a href=' || oppty_url || 'target="_blank">Link</a>' oppty_url,
|
|
(oppty_details::jsonb->'data'->>'naics')::jsonb->0->'code'->>0 naics,
|
|
(oppty_details::jsonb->'data'->>'placeOfPerformance')::jsonb->'state'->>'code' pop,
|
|
(oppty_details::jsonb->'data'->>'solicitation')::jsonb->>'setAside' setaside,
|
|
(oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->0->>'fullName' primary_poc_name,
|
|
(oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->0->>'email' primary_poc_email,
|
|
(oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->1->>'fullName' secondary_poc_fullname,
|
|
(oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->1->>'email' secondary_poc_email,
|
|
FROM pc.opportunity o
|
|
LEFT JOIN pc.naics n ON ( ((oppty_details::jsonb->'data'->>'naics')::jsonb->0->'code'->>0)::INTEGER = n.naics_code)
|
|
/*WHERE CASE WHEN match = 'MATCH' THEN 1
|
|
WHEN n.naics_code IS NOT NULL THEN 1
|
|
WHEN (oppty_details::jsonb->'data'->>'placeOfPerformance')::jsonb->'state'->>'code' IN ('NY', 'CT', 'MA', 'RI') THEN 1
|
|
ELSE 0 END = 1*/
|
|
ORDER BY notif_date DESC
|
|
|
|
CREATE TABLE opportunity_report AS
|
|
SELECT * FROM pc.opportunity_extract;
|
|
|
|
|
|
INSERT INTO opportunity_report
|
|
SELECT 1, e.* FROM pc.opportunity_extract e LEFT JOIN pc.opportunity_report r USING (id)
|
|
WHERE r.id IS NULL
|
|
|
|
INSERT INTO match_type (match_type)
|
|
SELECT DISTINCT match FROM opportunity_report;
|
|
|
|
UPDATE opportunity_report o SET match_id = m.id
|
|
FROM match_type m
|
|
WHERE m.match_type = o.match_id
|
|
|
|
UPDATE opportunity_report SET match_id = 1 WHERE notif_date = '2019-12-22'
|
|
|
|
WITH comb AS (
|
|
SELECT primary_poc_name AS name, lower(primary_poc_email) AS email
|
|
FROM pc.opportunity_report
|
|
UNION ALL
|
|
SELECT secondary_poc_fullname, lower(secondary_poc_email)
|
|
FROM pc.opportunity_report
|
|
) SELECT DISTINCT email FROM comb
|
|
|
|
|
|
WITH omit AS(
|
|
SELECT oppty_details::jsonb->'opportunityId' o FROM pc.opportunity WHERE match = 'SKIP')
|
|
UPDATE pc.opportunity
|
|
SET match = 'SKIP'
|
|
WHERE match IS NULL AND oppty_details::jsonb->'opportunityId' IN (SELECT * FROM omit)
|
|
|
|
|
|
WITH omit AS(
|
|
SELECT COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') o, oppty_details::jsonb->>'modifiedDate' d FROM pc.opportunity WHERE NULLIF(match,'') IS NULL),
|
|
rec AS (SELECT o, COUNT(*) c, MAX(d) m FROM omit GROUP BY o HAVING COUNT(*) > 1)
|
|
UPDATE pc.opportunity o
|
|
SET --match = 'SKIP'
|
|
match = CASE WHEN oppty_details::jsonb->>'modifiedDate' != rec.m THEN 'SKIP' END
|
|
-- SELECT *
|
|
FROM rec
|
|
WHERE COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') = rec.o
|
|
-- AND match IS NULL
|
|
-- AND oppty_details::jsonb->>'modifiedDate' != rec.m
|
|
|
|
WITH omit AS(
|
|
SELECT COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') o, oppty_details::jsonb->>'status' d, match
|
|
FROM pc.opportunity WHERE NULLIF(match,'') IS NULL),
|
|
rec AS (SELECT o, COUNT(*) c, MAX(d) m FROM omit GROUP BY o HAVING COUNT(*) > 1)
|
|
SELECT *
|
|
FROM pc.opportunity, rec
|
|
WHERE COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') = rec.o
|
|
--AND match IS NULL
|
|
--AND oppty_details::jsonb->>'modifiedDate' != rec.m
|
|
|
|
|
|
UPDATE pc.opportunity
|
|
SET match = 'SKIP'
|
|
WHERE notif_source = 'Sources Sought' AND NULLIF(match, '') IS NULL
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT jsonb_pretty(oppty_details::jsonb) FROM pc.opportunity
|
|
|
|
SELECT oppty_details::jsonb->'description'->0->>'body' FROM pc.opportunity
|
|
WHERE oppty_details::jsonb->'description'->0->>'body' ~* 'hardware'
|
|
|
|
SELECT oppty_details::jsonb->'parent'->>'opportunityId'
|
|
FROM pc.opportunity
|
|
WHERE notif_details ~ 'Enterprise Administrative Support Services' |