Dong Shin 03.27.2014

  • working on generating big data table for FA. __view_project_detailed_data
    • created sub views to work-around nested SELECT limitation in view
      • __view_yearly_totals_by_project, __view_financial_analysts_by_project, __view_portfolio_mgrs_by_project, __view_total_committed_amount_by_budget_center, __view_service_pocs_by_budget_center
    • remember not t use nested SELECTs in views!!!!
    • adding monthly committed amounts take too long to process….. 3 sec compared to 1 sec…
    • *THIS* is the join that produces a year data per row that has all obligations, outlays, and goals!
      • SELECT SUM(IF(oo.type LIKE ‘%Planned%Oblig%’,1,0)) AS planned_obligated_count,
        SUM(IF(oo.type LIKE ‘%Reported%Oblig%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%Planned%Outlay%’,1,0)) AS planned_outlay_count,
        SUM(IF(oo.type LIKE ‘%Reported%Outlay%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%PM%Actuals%’,1,0)) AS pm_actuals_count,
        SUM(IF(oo.type LIKE ‘%Outlay%FACTS%’,1,0)) AS outlay_facts_count,
        p.*, bc.*
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN obligations_outlays_goals oog ON oog.appropriation = p.appropriation AND oog.year = oo.year_count
        LEFT JOIN committed_amounts ca ON ca.budget_center_id = bc.uid AND (bc.fiscal_year – YEAR(ca.committed_date) + 1) = oo.year_count
        LEFT JOIN __view_yearly_totals_by_project AS yt ON yt.project_id = p.uid
        LEFT JOIN __view_financial_analysts_by_project AS fa ON fa.project_id = p.uid
        LEFT JOIN __view_portfolio_mgrs_by_project pm ON pm.project_id = p.uid
        LEFT JOIN __view_service_pocs_by_budget_center AS spbc ON spbc.budget_center_id = bc.uid
        LEFT JOIN __view_total_committed_amount_by_budget_center ca_totals ON ca_totals.budget_center_id = bc.uid
        GROUP BY oo.funding_id, oo.year_count