Category Archives: Dong Shin

Dong Shin 07.23.2014

reworked __view_project_detailed_data to include all the calculations (goals, totals) – still quick enough?
changed column config for the new Query Builder
added Show Goals CheckBox to hide/show obligation and outlay goals

set the detailed data table to view – processed as fast enough….
created SQL/script to run the truancy report and a list of people for FY14
working on Query Builder
- added tableName, generateData to the queryBuilderConfig.xml
- generateData button is not optional
- need to fix missing obligations/outlays for the old Procurement and RDT&E – FixFinancialData.sql

deployed new detailed data table… working at ~4 sec.
- __view_obligations_outlays_by_budget_center was modified to handle 0 values – -1 for nulls
generated PM Actuals status reports for Lenny
working on the new Query Builder

deployed new FA, new queries for detailed data
- using views reduced the time to generate the detailed data to 3 seconds from 5 min!!!!
working on new Query Builder
- added more fields
- modified generate_detailed_data() procedure to use the new views and queries
- use new table qb_queries for user queries, left the previous one – user_queries
- adding more column configurations to queryBuilderConfig2.xml

working on queries for Query Builder
- added indexes to the tables used in the detailed data
- created views for tables that use SUM() and GROUP_CONCAT()
- seemed to make queries faster, will try these tomorrow on-site

fixed weird cell height problem in Query Builder (happens in FireFox and IE)
added itemRenderer to format % values to one decimal points that are less than 100%
working on Query Builder
trying profileSQL – http://www.profilesql.com/download/

deployed FA – font for Status Yellow turns white!!! – fixed
created a query to generate master data per budget center, seems slow… will test tomorrow on-site

deployed new FA
- Status ItemRenderer changes when scrolling in Query Builder – fixed
working on new Query Builder
- refining master data table to include all appropriation years in a row instead of separated by a year
- SELECT
  SUM(IF(year_count=1,SUM(IF(type = ‘Planned Obligated $:’, month_1, 0)),0)) AS planned_obligated_year_1_month_1,
  SUM(IF(year_count=1,SUM(IF(type = ‘Reported FACTS Obligated $:’, month_1, 0)),0)) AS reported_facts_obligated_year_1_month_1,
  SUM(IF(year_count=1,SUM(IF(type = ‘Planned Outlay $:’, month_1, 0)),0)) AS planned_outlay_year_1_month_1,
  SUM(IF(year_count=1,SUM(IF(type = ‘Reported Outlay $:’, month_1, 0)),0)) AS reported_outlay_year_1_month_1,
  SUM(IF(year_count=1,SUM(IF(type = ‘PM Actuals (Invoiced) Outlay $:’, month_1, 0)),0)) AS pm_actuals_year_1_month_1,
  SUM(IF(year_count=1,SUM(IF(type = ‘Outlay $ (Reported in FACTS)’, month_1, 0)),0)) AS reported_facts_outlay_year_1_month_1
  FROM obligations_outlays
  GROUP BY funding_id

< would not work in FAChangeLogs.xml….. > works though. This caused the FA app to crash!
changed Font Colors for Obligation/Outlay/PM Actuals status to white when the status is Green/Red

tried to deploy the new Query Builder, the queries are too slow… 6 minutes! will try this tomorrow
- ‘Copying to tmp table’, ‘2.432915’
  ‘Sorting result’, ‘0.899327’
  ‘Sending data’, ‘0.783034’
- SHOW VARIABLES LIKE ‘max_heap_table_size’;
  - ‘max_heap_table_size’, ‘16777216’
- SHOW VARIABLES LIKE ‘tmp_table_size’;
  - ‘tmp_table_size’, ‘16777216’
added last COGNOS upload status to FA
added status (Red, Yellow, Green) to Obligated, Outlay, PM Actuals % value columns in Query Builder (old)

added last file uploaded info to Reqonciler
working on Query Builder
- load column info to Query Builder
- added color coded status to Obligations, Outlays, and PM Actuals – configure in queryBuilderConfig2.xml

working on Query Builder
- fixed publish/unpublish bug
- fixed save
- fixed NULL returning in percent values – NULL ≠0!!!!
- fixed Req Info update bug

put old FA (Query Builder) back on-site
working on Query Builder
- added obligation, outlay, pm actual percents in config
- fixed Update Requisition
- reworked __view_project_detailed_data to include service poc’s and fixed goals percents

deployed new FA with the reworked Query Builder – all is well.
experimented with MariaDB and some database performance testing on-site.
working on Query Builder
- added Obligation, Outlay, PM Actuals columns back…. still need % values
- required columns added to queryBuilderConfig.xml