Dong Shin 07.17.2012

  • space between month and year causing Ingest Manager to fail, but works fine here… changed all the scripts to have month_year
  • scripts created
    • to retrieve FY Goals – FY12Goals.py
    • to retrieve PPM tool usages for the users – PPMUsersUsages.py
    •  to retrieve FMPs for 2012 – FY12FMPs.py, need clarification on how to do rates/goals
    • to calculate monthly status (actual / (goal * budget amount)) -FY12MonthlyStatus.py
  • query to retrieve tool usages
    • SELECT
      user, date, MAX(timestamp) as start_time, MIN(timestamp) as end_time, (UNIX_TIMESTAMP(MAX(timestamp)) – UNIX_TIMESTAMP(MIN(timestamp))) as duration_seconds
      FROM query_logs
      GROUP BY date
  • query to retrieve all obligation/outlay goals
    • SELECT
      appropriation, ‘obligation’ as type,
      SUM(IF(year=1, obligation_month_1, NULL)) as ‘Oct 2011’,
      SUM(IF(year=1, obligation_month_2, NULL)) as ‘Nov 2011’,
      SUM(IF(year=1, obligation_month_3, NULL)) as ‘Dec 2011’,
      SUM(IF(year=1, obligation_month_4, NULL)) as ‘Jan 2012’,
      SUM(IF(year=1, obligation_month_5, NULL)) as ‘Feb 2012’,
      SUM(IF(year=1, obligation_month_6, NULL)) as ‘Mar 2012’,
      SUM(IF(year=1, obligation_month_7, NULL)) as ‘Apr 2012’,
      SUM(IF(year=1, obligation_month_8, NULL)) as ‘May 2012’,
      SUM(IF(year=1, obligation_month_9, NULL)) as ‘Jun 2012’,
      SUM(IF(year=1, obligation_month_10, NULL)) as ‘Jul 2012’,
      SUM(IF(year=1, obligation_month_11, NULL)) as ‘Aug 2012’,
      SUM(IF(year=1, obligation_month_12, NULL)) as ‘Sep 2012’,
      SUM(IF(year=2, obligation_month_1, NULL)) as ‘Oct 2012’,
      SUM(IF(year=2, obligation_month_2, NULL)) as ‘Nov 2012’,
      SUM(IF(year=2, obligation_month_3, NULL)) as ‘Dec 2012’,
      SUM(IF(year=2, obligation_month_4, NULL)) as ‘Jan 2013’,
      SUM(IF(year=2, obligation_month_5, NULL)) as ‘Feb 2013’,
      SUM(IF(year=2, obligation_month_6, NULL)) as ‘Mar 2013’,
      SUM(IF(year=2, obligation_month_7, NULL)) as ‘Apr 2013’,
      SUM(IF(year=2, obligation_month_8, NULL)) as ‘May 2013’,
      SUM(IF(year=2, obligation_month_9, NULL)) as ‘Jun 2013’,
      SUM(IF(year=2, obligation_month_10, NULL)) as ‘Jul 2013’,
      SUM(IF(year=2, obligation_month_11, NULL)) as ‘Aug 2013’,
      SUM(IF(year=2, obligation_month_12, NULL)) as ‘Sep 2013’,
      SUM(IF(year=3, obligation_month_1, NULL)) as ‘Oct 2013’,
      SUM(IF(year=3, obligation_month_2, NULL)) as ‘Nov 2013’,
      SUM(IF(year=3, obligation_month_3, NULL)) as ‘Dec 2013’,
      SUM(IF(year=3, obligation_month_4, NULL)) as ‘Jan 2014’,
      SUM(IF(year=3, obligation_month_5, NULL)) as ‘Feb 2014’,
      SUM(IF(year=3, obligation_month_6, NULL)) as ‘Mar 2014’,
      SUM(IF(year=3, obligation_month_7, NULL)) as ‘Apr 2014’,
      SUM(IF(year=3, obligation_month_8, NULL)) as ‘May 2014’,
      SUM(IF(year=3, obligation_month_9, NULL)) as ‘Jun 2014’,
      SUM(IF(year=3, obligation_month_10, NULL)) as ‘Jul 2014’,
      SUM(IF(year=3, obligation_month_11, NULL)) as ‘Aug 2014’,
      SUM(IF(year=3, obligation_month_12, NULL)) as ‘Sep 2014’,
      SUM(IF(year=4, obligation_month_1, NULL)) as ‘Oct 2014’,
      SUM(IF(year=4, obligation_month_2, NULL)) as ‘Nov 2014’,
      SUM(IF(year=4, obligation_month_3, NULL)) as ‘Dec 2014’,
      SUM(IF(year=4, obligation_month_4, NULL)) as ‘Jan 2015’,
      SUM(IF(year=4, obligation_month_5, NULL)) as ‘Feb 2015’,
      SUM(IF(year=4, obligation_month_6, NULL)) as ‘Mar 2015’,
      SUM(IF(year=4, obligation_month_7, NULL)) as ‘Apr 2015’,
      SUM(IF(year=4, obligation_month_8, NULL)) as ‘May 2015’,
      SUM(IF(year=4, obligation_month_9, NULL)) as ‘Jun 2015’,
      SUM(IF(year=4, obligation_month_10, NULL)) as ‘Jul 2015’,
      SUM(IF(year=4, obligation_month_11, NULL)) as ‘Aug 2015’,
      SUM(IF(year=4, obligation_month_12, NULL)) as ‘Sep 2015’
      FROM obligations_outlays_goals
      GROUP BY appropriation
      UNION
      SELECT
      appropriation, ‘outlay’ as type,
      SUM(IF(year=1, outlay_month_1, NULL)) as ‘Oct 2011’,
      SUM(IF(year=1, outlay_month_2, NULL)) as ‘Nov 2011’,
      SUM(IF(year=1, outlay_month_3, NULL)) as ‘Dec 2011’,
      SUM(IF(year=1, outlay_month_4, NULL)) as ‘Jan 2012’,
      SUM(IF(year=1, outlay_month_5, NULL)) as ‘Feb 2012’,
      SUM(IF(year=1, outlay_month_6, NULL)) as ‘Mar 2012’,
      SUM(IF(year=1, outlay_month_7, NULL)) as ‘Apr 2012’,
      SUM(IF(year=1, outlay_month_8, NULL)) as ‘May 2012’,
      SUM(IF(year=1, outlay_month_9, NULL)) as ‘Jun 2012’,
      SUM(IF(year=1, outlay_month_10, NULL)) as ‘Jul 2012’,
      SUM(IF(year=1, outlay_month_11, NULL)) as ‘Aug 2012’,
      SUM(IF(year=1, outlay_month_12, NULL)) as ‘Sep 2012’,
      SUM(IF(year=2, outlay_month_1, NULL)) as ‘Oct 2012’,
      SUM(IF(year=2, outlay_month_2, NULL)) as ‘Nov 2012’,
      SUM(IF(year=2, outlay_month_3, NULL)) as ‘Dec 2012’,
      SUM(IF(year=2, outlay_month_4, NULL)) as ‘Jan 2013’,
      SUM(IF(year=2, outlay_month_5, NULL)) as ‘Feb 2013’,
      SUM(IF(year=2, outlay_month_6, NULL)) as ‘Mar 2013’,
      SUM(IF(year=2, outlay_month_7, NULL)) as ‘Apr 2013’,
      SUM(IF(year=2, outlay_month_8, NULL)) as ‘May 2013’,
      SUM(IF(year=2, outlay_month_9, NULL)) as ‘Jun 2013’,
      SUM(IF(year=2, outlay_month_10, NULL)) as ‘Jul 2013’,
      SUM(IF(year=2, outlay_month_11, NULL)) as ‘Aug 2013’,
      SUM(IF(year=2, outlay_month_12, NULL)) as ‘Sep 2013’,
      SUM(IF(year=3, outlay_month_1, NULL)) as ‘Oct 2013’,
      SUM(IF(year=3, outlay_month_2, NULL)) as ‘Nov 2013’,
      SUM(IF(year=3, outlay_month_3, NULL)) as ‘Dec 2013’,
      SUM(IF(year=3, outlay_month_4, NULL)) as ‘Jan 2014’,
      SUM(IF(year=3, outlay_month_5, NULL)) as ‘Feb 2014’,
      SUM(IF(year=3, outlay_month_6, NULL)) as ‘Mar 2014’,
      SUM(IF(year=3, outlay_month_7, NULL)) as ‘Apr 2014’,
      SUM(IF(year=3, outlay_month_8, NULL)) as ‘May 2014’,
      SUM(IF(year=3, outlay_month_9, NULL)) as ‘Jun 2014’,
      SUM(IF(year=3, outlay_month_10, NULL)) as ‘Jul 2014’,
      SUM(IF(year=3, outlay_month_11, NULL)) as ‘Aug 2014’,
      SUM(IF(year=3, outlay_month_12, NULL)) as ‘Sep 2014’,
      SUM(IF(year=4, outlay_month_1, NULL)) as ‘Oct 2014’,
      SUM(IF(year=4, outlay_month_2, NULL)) as ‘Nov 2014’,
      SUM(IF(year=4, outlay_month_3, NULL)) as ‘Dec 2014’,
      SUM(IF(year=4, outlay_month_4, NULL)) as ‘Jan 2015’,
      SUM(IF(year=4, outlay_month_5, NULL)) as ‘Feb 2015’,
      SUM(IF(year=4, outlay_month_6, NULL)) as ‘Mar 2015’,
      SUM(IF(year=4, outlay_month_7, NULL)) as ‘Apr 2015’,
      SUM(IF(year=4, outlay_month_8, NULL)) as ‘May 2015’,
      SUM(IF(year=4, outlay_month_9, NULL)) as ‘Jun 2015’,
      SUM(IF(year=4, outlay_month_10, NULL)) as ‘Jul 2015’,
      SUM(IF(year=4, outlay_month_11, NULL)) as ‘Aug 2015’,
      SUM(IF(year=4, outlay_month_12, NULL)) as ‘Sep 2015’
      FROM obligations_outlays_goals
      GROUP BY appropriation
  • query to retrieve Financial Mitigation Plans for 2012
    • SELECT p.uid as project_uid, p.project_number, c.appropriation, c.center_number, c.center_name,
      CASE fmp.month WHEN 1 THEN ‘Oct 2011’ WHEN 2 THEN ‘Nov 2011’ WHEN 3 THEN ‘Dec 2011’ WHEN 4 THEN ‘Jan 2012’ WHEN 5 THEN ‘Feb 2012’ WHEN 6 THEN ‘Mar 2012’ WHEN 7 THEN ‘Apr 2012’ WHEN 8 THEN ‘May 2012’ WHEN 9 THEN ‘Jun 2012’ WHEN 10 THEN ‘Jul 2012’ WHEN 11 THEN ‘Aug 2012’ WHEN 12 THEN ‘Sep 2012’ END as month_year,
      o.type, a.amount,
      CASE fmp.month WHEN 1 THEN o.month_1 WHEN 2 THEN o.month_2 WHEN 3 THEN o.month_3 WHEN 4 THEN o.month_4 WHEN 5 THEN o.month_5 WHEN 6 THEN o.month_6 WHEN 7 THEN o.month_7 WHEN 8 THEN o.month_8 WHEN 9 THEN o.month_9 WHEN 10 THEN o.month_10 WHEN 11 THEN o.month_11 WHEN 12 THEN o.month_12 END as month_value,
      IF(o.type LIKE ‘%Obligat%’,
      CASE fmp.month WHEN 1 THEN g.obligation_month_1 * a.amount / 100 WHEN 2 THEN g.obligation_month_2 * a.amount / 100 WHEN 3 THEN g.obligation_month_3 * a.amount / 100 WHEN 4 THEN g.obligation_month_4 * a.amount / 100 WHEN 5 THEN g.obligation_month_5 * a.amount / 100 WHEN 6 THEN g.obligation_month_6 * a.amount / 100 WHEN 7 THEN g.obligation_month_7 * a.amount / 100 WHEN 8 THEN g.obligation_month_8 * a.amount / 100 WHEN 9 THEN g.obligation_month_9 * a.amount / 100 WHEN 10 THEN g.obligation_month_10 * a.amount / 100 WHEN 11 THEN g.obligation_month_11 * a.amount / 100 WHEN 12 THEN g.obligation_month_12 * a.amount / 100 END,
      CASE fmp.month WHEN 1 THEN g.outlay_month_1 * a.amount / 100 WHEN 2 THEN g.outlay_month_2 * a.amount / 100 WHEN 3 THEN g.outlay_month_3 * a.amount / 100 WHEN 4 THEN g.outlay_month_4 * a.amount / 100 WHEN 5 THEN g.outlay_month_5 * a.amount / 100 WHEN 6 THEN g.outlay_month_6 * a.amount / 100 WHEN 7 THEN g.outlay_month_7 * a.amount / 100 WHEN 8 THEN g.outlay_month_8 * a.amount / 100 WHEN 9 THEN g.outlay_month_9 * a.amount / 100 WHEN 10 THEN g.outlay_month_10 * a.amount / 100 WHEN 11 THEN g.outlay_month_11 * a.amount / 100 WHEN 12 THEN g.outlay_month_12 * a.amount / 100 END) as month_goal
      FROM project_portfolio.financial_mitigation_plans fmp,
      project_portfolio.obligations_outlays o,
      project_portfolio.projects p,
      project_portfolio.budget_centers c,
      project_portfolio.budget_amounts a,
      project_portfolio.obligations_outlays_goals g
      WHERE fmp.obligation_outlay_uid = o.uid
      AND o.project_id = p.uid
      AND p.uid = c.project_id
      AND o.funding_id = c.uid
      AND o.year = 2012
      AND o.year_count = 1
      AND a.budget_center_id = c.uid
      AND a.year = 2012 – p.begin_year + 1
      AND g.appropriation = c.appropriation
      AND g.year = 1
  • query to calculate goals reached for 2012
    • SELECT p.uid, p.project_number, p.proj_mgr_login, services, p.begin_year,
      c.uid, c.center_number, c.center_name, c.appropriation,
      a.amount,
      o.uid, o.type,
      FORMAT((o.month_1 / (g.obligation_month_1 * a.amount / 100)) * 100, 2) as ‘Oct 2011’,
      FORMAT((o.month_2 / (g.obligation_month_2 * a.amount / 100)) * 100, 2) as ‘Nov 2011’,
      FORMAT((o.month_3 / (g.obligation_month_3 * a.amount / 100)) * 100, 2) as ‘Dec 2011’,
      FORMAT((o.month_4 / (g.obligation_month_4 * a.amount / 100)) * 100, 2) as ‘Jan 2012’,
      FORMAT((o.month_5 / (g.obligation_month_5 * a.amount / 100)) * 100, 2) as ‘Feb 2012’,
      FORMAT((o.month_6 / (g.obligation_month_6 * a.amount / 100)) * 100, 2) as ‘Mar 2012’,
      FORMAT((o.month_7 / (g.obligation_month_7 * a.amount / 100)) * 100, 2) as ‘Apr 2012’,
      FORMAT((o.month_8 / (g.obligation_month_8 * a.amount / 100)) * 100, 2) as ‘May 2012’,
      FORMAT((o.month_9 / (g.obligation_month_9 * a.amount / 100)) * 100, 2) as ‘Jun 2012’,
      FORMAT((o.month_10 / (g.obligation_month_10 * a.amount / 100)) * 100, 2) as ‘Jul 2012’,
      FORMAT((o.month_11 / (g.obligation_month_11 * a.amount / 100)) * 100, 2) as ‘Aug 2012’,
      FORMAT((o.month_12 / (g.obligation_month_12 * a.amount / 100)) * 100, 2) as ‘Sep 2012’FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o,
      obligations_outlays_goals g
      WHERE p.uid = c.project_id
      AND c.uid = a.budget_center_id
      AND p.begin_year + a.year – 1 = 2012
      AND p.uid = o.project_id
      AND o.year = 2012
      AND o.year_count = 1
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = 1