Dong Shin 08.24.2012

  • Jessica reported that she couldn’t delete projects – found too many foreign keys on _projects_portfolio_admins
    • need to remove by ALTER TABLE _projects_portfolio_admins DROP FOREIGN KEY <KEY_NAME>
  • starting fresh on Flex Projects with the SVN repo – done
  • query to retrieve O&M (FY10) data for 2012
    • SELECT p.project_number, p.title, c.appropriation, c.center_number, c.center_name, a.year, a.amount,
      o.uid, o.type, o.year, o.year_count,
      IF(o.year_count = 1, month_1, 0) as Oct_2011,
      IF(o.year_count = 1, month_2, 0) as Nov_2011,
      IF(o.year_count = 1, month_3, 0) as Dec_2011,
      IF(o.year_count = 1, month_4, 0) as Jan_2012,
      IF(o.year_count = 1, month_5, 0) as Feb_2012,
      IF(o.year_count = 1, month_6, 0) as Mar_2012,
      IF(o.year_count = 1, month_7, 0) as Apr_2012,
      IF(o.year_count = 1, month_8, 0) as May_2012,
      IF(o.year_count = 1, month_9, 0) as Jun_2012,
      IF(o.year_count = 1, month_10, 0) as Jul_2012,
      IF(o.year_count = 1, month_11, 0) as Aug_2012,
      IF(o.year_count = 1, month_12, 0) as Sep_2012,
      IF(o.year_count = 2, month_1, 0) as Oct_2012,
      IF(o.year_count = 2, month_2, 0) as Nov_2012,
      IF(o.year_count = 2, month_3, 0) as Dec_2012,
      IF(o.year_count = 2, month_4, 0) as Jan_2013,
      IF(o.year_count = 2, month_5, 0) as Feb_2013,
      IF(o.year_count = 2, month_6, 0) as Mar_2013,
      IF(o.year_count = 2, month_7, 0) as Apr_2013,
      IF(o.year_count = 2, month_8, 0) as May_2013,
      IF(o.year_count = 2, month_9, 0) as Jun_2013,
      IF(o.year_count = 2, month_10, 0) as Jul_2013,
      IF(o.year_count = 2, month_11, 0) as Aug_2013,
      IF(o.year_count = 2, month_12, 0) as Sep_2013
      FROM `projects` p, budget_centers c, budget_amounts a, obligations_outlays o
      WHERE
      p.uid = 176
      AND p.uid = c.project_id
      AND c.uid = a.budget_center_id
      AND p.begin_year + a.year = 2013
      AND c.uid = o.funding_id
      AND o.year = 2012
      AND c.appropriation IN (SELECT type FROM appropriations WHERE duration = 2)
      GROUP BY o.type, c.uid
    • SELECT p.project_number, p.title, c.appropriation, c.center_number, c.center_name, a.year, a.amount,
      a.amount * SUM(IF(g.year = 1, obligation_month_1, 0)) / 100 as Oct_2011,
      a.amount * SUM(IF(g.year = 1, obligation_month_2, 0)) / 100 as Nov_2011,
      a.amount * SUM(IF(g.year = 1, obligation_month_3, 0)) / 100 as Dec_2011,
      a.amount * SUM(IF(g.year = 1, obligation_month_4, 0)) / 100 as Jan_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_5, 0)) / 100 as Feb_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_6, 0)) / 100 as Mar_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_7, 0)) / 100 as Apr_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_8, 0)) / 100 as May_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_9, 0)) / 100 as Jun_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_10, 0)) / 100 as Jul_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_11, 0)) / 100 as Aug_2012,
      a.amount * SUM(IF(g.year = 1, obligation_month_12, 0)) / 100 as Sep_2012,
      a.amount * SUM(IF(g.year = 2, obligation_month_1, 0)) / 100 as Oct_2012,
      a.amount * SUM(IF(g.year = 2, obligation_month_2, 0)) / 100 as Nov_2012,
      a.amount * SUM(IF(g.year = 2, obligation_month_3, 0)) / 100 as Dec_2012,
      a.amount * SUM(IF(g.year = 2, obligation_month_4, 0)) / 100 as Jan_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_5, 0)) / 100 as Feb_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_6, 0)) / 100 as Mar_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_7, 0)) / 100 as Apr_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_8, 0)) / 100 as May_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_9, 0)) / 100 as Jun_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_10, 0)) / 100 as Jul_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_11, 0)) / 100 as Aug_2013,
      a.amount * SUM(IF(g.year = 2, obligation_month_12, 0)) / 100 as Sep_2013
      FROM `projects` p, budget_centers c, budget_amounts a, obligations_outlays_goals g
      WHERE
      p.uid = 176
      AND p.uid = c.project_id
      AND c.uid = a.budget_center_id
      AND p.begin_year + a.year = 2013
      AND g.appropriation = c.appropriation
      AND c.appropriation IN (SELECT type FROM appropriations WHERE duration = 2)
      GROUP BY c.uid