Monthly Archives: September 2013

Dong Shin 09.30.2013

  • working on Query Builder
    • modifying Obligation/Outlay update scripts to update EA Req’s
  • added Database Management Panel to FA
    • added database name to the setUpModel.xml – was using hardcoded project_portfolio
  • fixed Capability not showing in ReqAssistant

Dong Shin 09.27.2013

  • deployed new FA and SQL Query to update obligations/outlays
    • Sub Projects working, list of projects shown
    • update query works!
  • cleaned up tables that uses AddableListCombo – with Lenny’s input
    • set defaults for some comboboxes…. table with table name, default value, and editable?

Phil 9.27.13

8:00 – 11:30 SR

  • Parent projects can only be created if there are no REQ’s. If there are, pop up a dialog that says REQ’s must be eliminated. A parent project has no visible REQ tab.
  • Add table_combobox_defaults with 3 columns 1) Editable, 2) Default value, 3) Table name
  • Unresolved discussion about REQ tracking discussion with Lenny.
  • Timesheets.

11:30 – FP

  • More paper

Phil 9.26.13

8:00 – 10:30

  • Backups
  • Non-meeting with PIT Crew. Some fire that Chris was working on?
  • Filled out new role paperwork. Got to use Bill’s justification again…
  • Status report for Tangie.

10:30 – SR

  • Continuing to transpose the “Sensory Substitution” paper to a useful format from the non-text PDF. FreeOCR is pretty cool.

Dong Shin 09.26.2013

  • no meeting today….
  • Sub Project bugs
    • Requisition Data saves to the parent project (not clearing?) – fixed
    • Select Sub Project in Link Project shows empty list – cannot duplicate here….
  • added obligation/outlay goals and values percent for all months. This can be filtered by selecting month from the month combobox
  • working on updating Obligations/Outlays with maximum values in current and past months
    • UPDATE obligations_outlays_test
      SET month_2 = GREATEST(IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_3 = GREATEST(IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_4 = GREATEST(IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_5 = GREATEST(IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_6 = GREATEST(IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_7 = GREATEST(IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_8 = GREATEST(IF(ISNULL(month_8), 0, month_8), IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_9 = GREATEST(IF(ISNULL(month_9), 0, month_9), IF(ISNULL(month_8), 0, month_8), IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_10 = GREATEST(IF(ISNULL(month_10), 0, month_10), IF(ISNULL(month_9), 0, month_9), IF(ISNULL(month_8), 0, month_8), IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_11 = GREATEST(IF(ISNULL(month_11), 0, month_11), IF(ISNULL(month_10), 0, month_10), IF(ISNULL(month_9), 0, month_9), IF(ISNULL(month_8), 0, month_8), IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1)),
      month_12 = GREATEST(IF(ISNULL(month_12), 0, month_12), IF(ISNULL(month_11), 0, month_11), IF(ISNULL(month_10), 0, month_10), IF(ISNULL(month_9), 0, month_9), IF(ISNULL(month_8), 0, month_8), IF(ISNULL(month_7), 0, month_7), IF(ISNULL(month_6), 0, month_6), IF(ISNULL(month_5), 0, month_5), IF(ISNULL(month_4), 0, month_4), IF(ISNULL(month_3), 0, month_3), IF(ISNULL(month_2), 0, month_2), IF(ISNULL(month_1), 0, month_1))
      WHERE type = ‘Reported FACTS Obligated $:’ OR type = ‘Outlay $ (Reported in FACTS)’

Dong Shin 09.25.2013

  • deployed new FA and database updates today – notes at Phil’s blog.
  • working on setting correct Obligation/Outlay values

Phil 9.25.13

8:00 – 1:00 SR

  • Backups
  • Got my permissions straightened out in Xacta!
  • Deploying new FA with DB changes today
  • Tangie needs status reports by next Tuesday
  • Create an “table_errors” table that has the application, user, date, time, query and error message, and take out the “Mail to Admin note”
  • Project Budget info has scroll bars. It needs tooltips instead
  • Budget info needs to be cumulative – i.e. add the current month to the previous month inside the Cognos script;

Dong Shin 09.24.2013

  • working on Query Builder
    • added ProgressBar to the Query Builder Panel
    • added req totals view
      • CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_req_totals` AS select `budget_centers`.`req_id` AS `req_id`,sum(`budget_centers`.`funded_budget`) AS `req_id_total` from `budget_centers` group by `budget_centers`.`req_id`;
    • added % values to the queries…
      • capability, bcc.budget_center_name AS ‘Budget Center Name’, bcc.budget_center AS ‘Budget Center’, req_id AS ‘Req ID’, vrt.req_id_total AS ‘Total’,
        CONCAT(obligation_month_12,’%’) AS ‘Obligation Goal Percent’,
        CONCAT(IF(o.type = ‘Reported FACTS Obligated $:’,month_12/vrt.req_id_total*100,0),’%’) AS ‘Obligated Value Percent’
        WHERE capability = ‘ACC’
        GROUP BY bcc.budget_center, req_id
  • Sub Projects are back!
    • reworked Project Editor panel to show Sub Project

Phil 0.24.13

8:00 – 11:00 SR

  • Big wreck on 95 this morning meant back roads coming in. Actually very pleasant commute 🙂
  • Backups
  • Continuing my server paperwork saga.
  • Subprojects are back!
    • Add “SubProject” to project information titlebar
    • Copy info from parent (Project Budget info, etc)
    • It might be possible to calculate some budget components. Not sure if needed.

11:00 – FP

  • Got dispensation to drop the headset paper. Working on the Phantom paper.

Dong Shin 09.23.2013

  • working on Query Builder
    • working on query to retrieve Obligation/Outlay % values
    • corrected joining tables to get unique obligation/outlay entries…
      • SELECT * FROM
        obligations_outlays o
        LEFT JOIN budget_centers AS bc ON o.funding_id = bc.uid
        LEFT JOIN projects AS p ON o.project_id = p.uid
        LEFT JOIN obligations_outlays_goals g ON g.appropriation = p.appropriation AND g.year = o.year_count
        LEFT JOIN __view_project_totals AS t ON t.project_id = p.uid
        LEFT JOIN __view_budget_center_totals AS t3 ON t3.budget_center = p.center_name
        LEFT JOIN __view_sub_budget_center_totals AS t2 ON t2.sub_budget_center_number = bc.sub_budget_center_number
        LEFT JOIN
        ( SELECT budget_center_id, sub_budget_center, sub_budget_center_name, executing_budget_center, ebc_name, appropriation_year, requisition_id,
        po_reference, acr, MAX(committed_date), SUM(committed_amount), MAX(obligation_date), SUM(obligated_amount), MAX(expensed_date),
        SUM(expensed_amount), MAX(po_start_date), MAX(po_end_date), GROUP_CONCAT(DISTINCT contract_type), GROUP_CONCAT(DISTINCT po_type_code),
        GROUP_CONCAT(contract_no), GROUP_CONCAT(DISTINCT vendor_id) FROM budget_center_contracts GROUP BY budget_center_id ) AS bcc ON bcc.budget_center_id = bc.uid
    • fixed queries using the new joining tables