Dong Shin 01.08.2014

  • fixed some bugs and went through FA, RA, and Reqconciler to use GLOBALS 
  • working on queries for presentation using Phil’s test data
    • query to get all obligations/outlays for current fiscal year
      • SELECT * FROM projects p LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id WHERE oo.year_count = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
    • query to get monthly committed amounts by req_id
      • SELECT budget_center_id, year(committed_date), month(committed_date), sum(committed_amount),
        SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
        SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
        SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
        SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
        SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
        SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
        SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
        SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
        SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
        SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
        SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
        SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
        FROM committed_amounts
        GROUP BY budget_center_id