Dong Shin 01.10.2013

  • working on adding “DUE” status to individual projects
  • PA defaults to “Reporting Months” on CURRENT and DUE status
  • continue working on “DUE” status for list of projects in PA – done!
    • modified the query to get status
      • SELECT p.*, IF(p.uid IN (SELECT project_id FROM (SELECT project_id, year, year_count, count(funding_id) as data_count FROM obligations_outlays WHERE ((ISNULL(month_1) OR ISNULL(month_2)) AND (year+year_count-1) = 2013) OR ((ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6) OR ISNULL(month_7) OR ISNULL(month_8) OR ISNULL(month_9) OR ISNULL(month_10) OR ISNULL(month_11) OR ISNULL(month_12)) AND (year+year_count-1) < 2013) GROUP BY funding_id, year, year_count ) AS FOO WHERE data_count = 6), ‘OVERDUE’, IF(p.uid IN (SELECT project_id FROM (SELECT project_id, year, year_count, count(funding_id) as data_count FROM obligations_outlays WHERE ((ISNULL(month_1) OR ISNULL(month_2)) AND (year+year_count-1) = 2013) OR ((ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6) OR ISNULL(month_7) OR ISNULL(month_8) OR ISNULL(month_9) OR ISNULL(month_10) OR ISNULL(month_11) OR ISNULL(month_12)) AND (year+year_count-1) < 2013) GROUP BY funding_id, year, year_count ) AS FOO WHERE data_count < 6), ‘INCOMPLETE’, IF(p.uid IN (SELECT project_id FROM (SELECT project_id, year, year_count, count(funding_id) as data_count FROM obligations_outlays WHERE (ISNULL(month_3) AND (year+year_count-1) = 2013) GROUP BY funding_id, year, year_count ) AS FOO WHERE data_count > 1), ‘DUE’, ‘CURRENT’) ) ) as status FROM projects p WHERE p.uid in (SELECT project_id FROM budget_centers)