Category Archives: Dong Shin

Dong Shin 11.22.2011

  • pulling my hair over SQL queries……….
  • get status of selected project by year and appropriation year
    • SELECT c.*, a.uid as a_uid, a.amount as amount, o.year, o.year_count,
      IF (
      (((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6)
      AND o.year+year_count=2013) OR
      ((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_3), 1, 0)) = 6 OR SUM(IF(ISNULL(month_4), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_5), 1, 0)) = 6 OR SUM(IF(ISNULL(month_6), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_7), 1, 0)) = 6 OR SUM(IF(ISNULL(month_8), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_9), 1, 0)) = 6 OR SUM(IF(ISNULL(month_10), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_11), 1, 0)) = 6 OR SUM(IF(ISNULL(month_12), 1, 0)) = 6)
      AND o.year+year_count<2013))
      ,’OVERDUE’,
      IF (
      (((SUM(IF(ISNULL(month_2), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_2), 1, 0)) > 0) AND o.year+year_count=2013) OR
      ((SUM(IF(ISNULL(month_1), 1, 0)) > 0 OR SUM(IF(ISNULL(month_2), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_3), 1, 0)) > 0 OR SUM(IF(ISNULL(month_4), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_5), 1, 0)) > 0 OR SUM(IF(ISNULL(month_6), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_7), 1, 0)) > 0 OR SUM(IF(ISNULL(month_8), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_9), 1, 0)) > 0 OR SUM(IF(ISNULL(month_10), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_11), 1, 0)) > 0 OR SUM(IF(ISNULL(month_12), 1, 0)) > 0)
      AND o.year+year_count<2013))
      , ‘INCOMPLETE’, ‘CURRENT’))
      as status
      FROM budget_centers c, obligations_outlays o, budget_amounts a
      WHERE o.project_id = 171
      AND c.uid = o.funding_id
      AND a.budget_center_id = c.uid AND a.year = year_count
      GROUP BY o.year, year_count
  • get status of project  for year 2012 and month 2
    • 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) = 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) < 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) = 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) < 2013)
      GROUP BY funding_id, year, year_count
      ) AS FOO
      WHERE data_count < 6), ‘INCOMPLETE’, ‘CURRENT’)
      ) as status
      FROM projects p

Dong Shin 11.21.2011

  • performance review
  • working down the list of PPM enhancement
    • added auto entry to fill the financial data up to specified month
    • added capability to automatically add data to the end of the appropriation when value reaches 100%
    • added button to start Financial Data Entry from Project Editor
    • Project Assistant (PA) show FACTS and Reported Outlay to only Admins
    • added title to PA and PPM
    • PM Actuals gets copied to Reported Outlay on data entry (when value is null)
    • added Cumulative/Monthly DropDownList to PA (Only for Monthly). When Monthly is specified, add from Previous Month data. If previous month is null, the DropDownList is disabled and defaults to Cumulative
    • Editing disabled on Year View in PA for non-admins
    • changed ItemRenderer for Identifier in PA to show unique fields
    • started on Financial Data navigation on PA

Dong Shin 11.14.2011

  • PPM
    • created HeaderRenderer for the yearly view in FinancialDataNavigator
      • selected month – blue, current month – red
    • changed NavBar button to ButtonBar in FinancialDataNavigator and created skin for it.
  • Experimenting with Google Web Toolkit (GWT)

Dong Shin 11.09.2011

  • updating UI…
  • updated __view_budget_center_appropriation view to calculate total_budget correctly (__view_budget_center_appropriation.sql)
    • CREATE OR REPLACE
      ALGORITHM = UNDEFINED
      VIEW `__view_budget_center_appropriation`
      AS select budget_centers.uid AS uid,budget_centers.project_id AS project_id,budget_centers.center_number AS center_number,
      budget_centers.center_name AS center_name,budget_centers.funding_transaction_name AS funding_transaction_name,budget_centers.
      funding_transaction_number AS funding_transaction_number,
      budget_centers.funding_transaction_type AS funding_transaction_type,
      budget_centers.capability AS capability,budget_centers.expenditure_center AS expenditure_center,
      budget_centers.investment_portfolio AS investment_portfolio,budget_centers.appropriation AS appropriation,
      SUM(budget_amounts.amount) as total_budget, budget_centers.type AS type,budget_centers.program_element AS program_element,
      budget_centers.facts_pe AS facts_pe,appropriations.duration AS duration,appropriations.status AS status
      from budget_centers, appropriations, budget_amounts WHERE budget_centers.appropriation = appropriations.type
      AND budget_centers.uid = budget_amounts.budget_center_id
      GROUP BY budget_amounts.budget_center_id

Dong Shin 11.08.2011

  • reworked to disable goals in Financial Data
  • added NumberFormatter to format % values to 2 decimal points
  • Current button disabled on data when FY is not available
  • added DataGrid for Full FY Data, switch between monthly and yearly
  • reworking UI

Dong Shin 11.07.2011

  • Meeting with Tangi, found minor bugs
    • Goals should be disabled
    • format % values to 2 decimals
    • disable current button where FY is not available
  • changed ProjectFilterButtonBar skins to reflect the query
  • updated ItemRenderer to show INCOMPLETE
  • query to find OVERDUE, INCOMPLETE, CURRENT status of the projects
    • SELECT p.*,
      IF(p.uid IN (
      SELECT p.uid FROM obligations_outlays o, projects p
      WHERE o.project_id = p.uid
      AND ISNULL(o.month_2)
      AND (o.year + o.year_count – 1) = 2012),
      ‘OVERDUE’,
      (IF
      (p.uid in (SELECT p.uid FROM obligations_outlays o, projects p WHERE o.project_id = p.uid AND
      (o.year + o.year_count – 1) < 2012
      AND
      (ISNULL(o.month_1) OR
      ISNULL(o.month_2) OR
      ISNULL(o.month_3) OR
      ISNULL(o.month_4) OR
      ISNULL(o.month_5) OR
      ISNULL(o.month_6) OR
      ISNULL(o.month_7) OR
      ISNULL(o.month_8) OR
      ISNULL(o.month_9) OR
      ISNULL(o.month_10) OR
      ISNULL(o.month_11) OR
      ISNULL(o.month_12)
      )
      UNION
      SELECT p.uid FROM obligations_outlays o, projects p WHERE o.project_id = p.uid AND
      (o.year + o.year_count – 1) < 2012
      AND (ISNULL(o.month_1) OR ISNULL(o.month_2))
      ), ‘INCOMPLETE’,’CURRENT’))

      ) AS status

      FROM obligations_outlays o, projects p
      WHERE o.project_id = p.uid  AND (o.year + o.year_count – 1) = 2012
      GROUP by p.uid

Dong Shin 11.04.2011

  • meeting with Tangi, few enhancementsand fixes
    • add INCOMPLETE for projects that are missing previous months’ data
    • add ‘Goals’ string to Obligation/Outlay (11/04)
    • Incomplete, Overdue, Current to filter projects list (11/04)
    • fix month when navigating thru Overdue projects (11/04)

Dong Shin 11.03.2011

  • PPM
    • changed PPM Widgets to Project Assistant
    • bold fonts on DropDowList
    • lighter gray for disabled Budget Centers and Years
    • added Budget Center information on Financial Data Navigator
    • fixed triple click/double tab to edit bug
  • Updated SF86 and sent to Carrie
  • Updating Performance Plan

Dong Shin 11.01.2011

  • PPM
    • only Budget Centers in the selected project shown for Funding Request
    • removed Funding with $0 budget
    • changed the debug login settings
  • FMGDEV updates for demo
    • database updates
    • PPM and Widgets uploaded
    • renamed old ones – ProjPortfolioMgr_11012011, project_portfolio_110111