Author Archives: gvr4wd

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

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

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

Dong Shin 09.20.2013

• VizTool meeting – notes @ requirements page
• working on Query Builder
  • removed Query column from Load Saved Query window
  • allow saving of query when no data returned
  • added GROUP BY req_id to the queries
  • updated the queries to use aliases
  • added table aliases to queryBuilderConfig.xml so that the duplicate column names are not used
  • working on replacing Obligation/Outlay goal $ with %

Dong Shin 09.19.2013

• tried deploying FA/RA… had old SWF’s in the CD. Put the old FA back and will try again
  • redeployed in the afternoon
• created new CD with the whole FA webapp directory as well as the new files…
• Flex 4 auto sizes the row height of the datagrids messing up the display….. bug or feature? Set rowHeight=26 to fix
• continue working on Query Builder
  • created a view, __budget_center_totals for Total Committed, Total Obligated and Total Expensed
    • CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_budget_center_totals` AS select `budget_center_contracts`.`budget_center` AS `budget_center`,sum(`budget_center_contracts`.`committed_amount`) AS `total_committed`,sum(`budget_center_contracts`.`obligated_amount`) AS `total_obligated`,sum(`budget_center_contracts`.`expensed_amount`) AS `total_expensed` from `budget_center_contracts` group by `budget_center_contracts`.`budget_center`;

Dong Shin 09.18.2013

• working on Query Builder
  • working on queries for Chris
    • set up five queries organized by Capability
  • double click on the results brings Project Editor or Project Select Window if there are more than one project associated
  • added query name to the Query Builder title
  • dumped user_queries table in resources directory for tomorrow

Dong Shin 09.17.2013

• adding ScriptIf to FA server and client – done!
  • added runPythonScript(scriptName) to ScriptFacade
  • added Remote Object interface
  • added call in ContractsParser
  • did get some JVM MaxPermSize errors, fixed by adding more memory in startup.sh
• fixed RA adding 1 to the month fields in the Invoices

Dong Shin 09.16.2013

• deployed new VSS and scripts/queries
  • obligation/outlay imports work!
  • few notes at Phil’s blog
• fixed Outlay, expensed not committed!
• working on Server to run python scripts

Dong Shin 09.13.2013

• finished up the script to update Obligation/Outlay from COGNOS, packing up for testing…
  • obligations_outlay_queries.sql – should go into VSS Query Editor
  • update_obligation_outlays.py – python script to update data
  • packaged up VSS client and server stuff

Dong Shin 09.12.2013

• VizTool meeting – notes at the requirements page
• working on VSS script for Obligation/Outlay import
  • got Outlay update script to work!
  • verifying……

Dong Shin 09.11.2013

• bugs from Carla for RA
  • Date DFAS Paid for the invoice changes the month (adding by one)
  • add “Edit” Invoice
• reworked FA for meeting tomorrow
  • fixed some bugs not parsing queries correctly
  • reworked the queries for Chris
    • not sure if TST and non-TST’s should be separated for the overdue queries
  • added appropriation
• working on Obligation/Outlay update script in VSS

Dong Shin 09.10.2013

• working on Obligation/Outlay import
  • added a method to parse DBTable to HashMap – toHashMapList
  • working on Python Script to import Outlay data – save_outlay.py

Dong Shin 09.09.2013

• working on Query Builder
• working on importing Obligations/Outlays from COGNOS
  • set up VisibilityScriptingServer
    • change project_portfolio to project_portfolio_enh in visibility.properties
    • updated pom.xml to generate VisibilityScriptingServer-1.0.jar
    • rebuilt with the latest code
  • created sample xlsx data for Obligated and Outlay data
  • saved following queries in VSS – get_cognos_outlays and get_cognos_obligations
  • query to get monthly outlay data – 59958230400 is for 1900-01-01 dates which should be ignored
    • SELECT sub_budget_center, requisition_id,
      MONTH(committed_date), YEAR(committed_date)
      committed_date, SUM(committed_amount)
      FROM `contracts_cognos`
      WHERE to_seconds(committed_date) > 59958230400
      GROUP BY sub_budget_center, requisition_id, MONTH(committed_date), YEAR(committed_date)
      ORDER BY sub_budget_center, requisition_id, YEAR(committed_date), MONTH(committed_date)
  • query to get monthly obligation data
    • SELECT sub_budget_center, requisition_id,
      MONTH(obligation_date), YEAR(obligation_date)
      committed_date, SUM(obligated_amount)
      FROM `contracts_cognos`
      WHERE to_seconds(obligation_date) > 59958230400
      GROUP BY sub_budget_center, requisition_id, MONTH(obligation_date), YEAR(obligation_date)
      ORDER BY sub_budget_center, requisition_id, YEAR(obligation_date), MONTH(obligation_date)

Dong Shin 09.06.2013

• new FA4 deployed, more bugs
  • fixed Financial Data not showing for multiple Req’s
  • removed chart from Summary Data
  • wrap components in FinancialDataPanel with <s:Scroller /> to get all the components
  • resize datagrid to eliminate vertical scrollers
• working on Query Builder
  • reworked __view_project_totals so that it includes all total_budgets from budget_centers table
  • created view __view_sub_budget_center_totals for TST
  • fixed WHERE and GROUP BY not parsed properly in Query Builder