Category Archives: Dong Shin

Dong Shi 10.29.2012

  • working at home
  • continue working on SQLEditor for VSS
    • added method to run SQL string to server – SQLResult runSQLStrings(String databaseName, String sqlList)
    • added SQLResult.java to javaUtils – pom.xml is set to generate version 1.1, others are not using it?????
      • changed scriptingEngine and VisibilityScriptingServer to use 1.1
    • added methods to save and get list of SQLs

Dong Shin 10.26.2012

  • added dbStoredQuery(String queryName) to ScriptFacades class
    • can run set of queries and return the last result in DbTable format
  • working on dbStoredProc – runs stored procedure in the database via VSS
  • working on QueryEditor in VSS

Dong Shin 10.25.2012

  • status meeting
  • working on running stored queries in ScriptFacades class
  • continue working on FMP alerts
  • created AlertFMPs.py and AlertFMPsSaveToTable.py scripts
  • query to find offending FMP’s in Alert format
    • SELECT
      GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
      GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
      GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
      GROUP_CONCAT(distinct(m.login)) as service_project_managers,
      p.project_number as program,
      c.center_number as MIPR
      FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2,
      projects p, obligations_outlays o, budget_centers c,
      _projects_service_project_mgrs m,
      _projects_portfolio_admins ppa,
      _projects_portfolio_mgrs ppm,
      _projects_service_finance_pocs psf
      WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
      AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
      AND fmp1.explanation1 = fmp2.explanation1
      AND fmp1.explanation2 = fmp2.explanation2
      AND fmp1.explanation3 = fmp2.explanation3
      AND fmp1.explanation4 = fmp2.explanation4
      AND fmp1.explanation5 = fmp2.explanation5
      AND fmp1.month + 1 = fmp2.month
      AND p.uid = o.project_id
      AND c.uid = o.funding_id
      AND p.uid = m.project_id
      AND p.uid = ppa.project_id
      AND p.uid = ppm.project_id
      AND p.uid = psf.project_id
      AND fmp1.obligation_outlay_uid IN
      (SELECT o.uid
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE
      p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count
      AND o.year + o.year_count = 2014
      AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
      AND o.month_1 <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95)))
      GROUP BY o.uid

Dong Shin 10.24.2012

  • query to find offending (?) FMPs for underperforming obligations/outlays
    • SELECT *
      FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2
      WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
      AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
      AND fmp1.explanation1 = fmp2.explanation1
      AND fmp1.explanation2 = fmp2.explanation2
      AND fmp1.explanation3 = fmp2.explanation3
      AND fmp1.explanation4 = fmp2.explanation4
      AND fmp1.explanation5 = fmp2.explanation5
      AND fmp1.month + 1 = fmp2.month
      AND fmp1.obligation_outlay_uid IN
      (SELECT o.uid
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE
      p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count
      AND o.year + o.year_count = 2014
      AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
      AND o.month_1 <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95)))

Dong Shin 10.23.2012

  • created a script to save Planned Value alerts to Alerts table – AlertPlannedValuesSaveToTable.py
  • working on script to generate alerts for unchanged Financial Mitigation Plans
  • query to get unchanged FMPs from previous months
    • SELECT *
      FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2
      WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
      AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
      AND fmp1.explanation1 = fmp2.explanation1
      AND fmp1.explanation2 = fmp2.explanation2
      AND fmp1.explanation3 = fmp2.explanation3
      AND fmp1.explanation4 = fmp2.explanation4
      AND fmp1.explanation5 = fmp2.explanation5
      AND fmp1.month + 1 = fmp2.month

Dong Shin 10.22.2012

  • working on queries to calculate actual performance of Financial data
  • created a script to generate alerts for planned values that are out of acceptable range (+/- 5%) – AlertPlannedValues.py
  • query to find planned values under or over 5%
    • SELECT p.uid, c.uid, c.center_number, c.appropriation,
      a.year, a.amount,
      o.uid, o.type, o.year, o.year_count, o.month_1,
      g.uid,
      @goal_month_1 := IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) as goal_month_1,
      TRUNCATE(a.amount * @goal_month_1 / 100, 2) as goal_month_1_amount,
      @goal_month_1_min := TRUNCATE(a.amount * @goal_month_1 / 100, 2) * 0.95 as goal_month_1_min,
      @goal_month_1_max := TRUNCATE(a.amount * @goal_month_1 / 100, 2) * 1.05 as goal_month_1_max
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE p.uid = 176
      AND p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count
      AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
      AND o.month_1 <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95))
      AND o.type = ‘Planned Obligated $:’
  • query to calculate goals and actuals
    • SELECT p.uid, c.uid, c.center_number, c.appropriation,
      a.year, a.amount,
      o.uid, o.type, o.year, o.year_count, o.month_1,
      g.uid,
      @goal_month_1 := IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) as goal_month_1,
      TRUNCATE(a.amount * @goal_month_1 / 100, 2) as goal_month_1_amount
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE p.uid = 176
      AND p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count

Dong Shin 10.17.2012

  • fixed alerts with no users (such as Service/PMs not using tool) not showing in Alerts Panel for Administrators
  • removed other users columns (Portfolio Admin, Portfolio Mgrs, Serivce POCs) from Alerts Datagrid in PA to reflect the requirements doc
  • reworked Alerts data retrieval to include comments count and added it to Alerts Datagrid
  • added refresh Alerts data when Alert Edit is done.

Dong Shin 10.16.2012

  • continue working on Alerts
  • query to alert users that have not been logged on for more than 30 days
    • SELECT
      NULL as porfolio_admins,
      NULL as portfolio_mgrs,
      NULL as service_finance_pocs,
      NULL as service_project_managers,
      ” as program,
      ” as MIPR,
      CONCAT(login, ‘ has not been logged in for more than 30 days ‘) as alert,
      ‘OPEN’ as status
      FROM `users`
      WHERE DATEDIFF(now(), last_login) > 30
    • script – UsersNotLoggedIn30Days.py and UsersNotLoggeIn30DaysSaveToTable.py
  • added TAB support for PrettyTextEditor in VSS4
    • protected function keyFocusChange(evt:FocusEvent) : void {
      evt.preventDefault();
      evt.currentTarget.insertText(”    “);
      }
  • created a script to return status of Alerts with days – AlertsStatus.py

Dong Shin 10.11.2012

  • Weekly status meeting
  • deployed new Visiblity and PA – Visibility and AccountManagers didn’t work
    • put the old ones back and works!
    • compiled new ones on my PC and it works
    • looks like we deployed old (really old) version from Maven repo that were packaged together by Visibility server project
  • working on overdue projects alerts
    • query to get overdue projects in ALERT format
    • SELECT GROUP_CONCAT(m.login) as service_project_managers,
      GROUP_CONCAT(ppa.login) as porfolio_admins,
      GROUP_CONCAT(ppm.login) as portfolio_mgrs,
      GROUP_CONCAT(psf.login) as service_finance_pocs,
      p.project_number as program,
      c.center_number as MIPR,
      ’30 days overdue’ as alerts,
      ‘OPEN’ as status,
      p.project_number, p.description, a.amount, o.uid, o.type, o.year,
      o.year_count, o.month_1
      FROM projects p,
      budget_centers c,
      budget_amounts a,
      obligations_outlays o,
      _projects_service_project_mgrs m,
      _projects_portfolio_admins ppa,
      _projects_portfolio_mgrs ppm,
      _projects_service_finance_pocs psf
      WHERE p.uid  = c.project_id
      AND a.budget_center_id = c.uid
      ANd a.year  = 2013 – p.begin_year + 1
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND o.year + o.year_count – 1 = 2013
      AND o.year_count = 1
      AND ISNULL(o.month_1)
      AND p.uid = m.project_id
      AND p.uid = ppa.project_id
      AND p.uid = ppm.project_id
      AND p.uid = psf.project_id
      GROUP BY o.uid

 

Dong Shin 10.01.2012

  • syncing DEV works perfect…, but Visibility wouldn’t work on my laptop, but works on the desktop…..
  • found PA month and year calculations wrong when new FY kicked in!! – fixed
  • added SQLErrorWindow to PA
  • Alerts panel removed when no alerts are found