- deployed new FA4, some bugs
- fixed total_budget as Currency – TOTAL regex added
- removed the charts from Financial Data Viewer – performance should be lot better
- created ServerLogPanel and LocalLogPanel that are draggable and resizable and moved out of main canvas
- changed references to budget_center.total_budget to t.total_budget in the Query Builder queries to correct obligations and outlays
- added support for column names with spaces in Query Builder – wrap it with a single quote ‘
Author Archives: gvr4wd
Dong Shin 09.04.2013
- deployed new FA (Flex 4.5!), found some bugs
- fixed logout not clearing panels
- fixed Total Allocated not showing
- change the name of the Flex App – FinancialAssistant4
- continue working on Query Builder
- created a view to pre-set the project totals for the queries – __view_project_totals
- fixed group by errors when loading saved queries…
Dong Shin 09.03.2013
- working on Query Builder
- moved the columns build after the quest has run
- reworked the load/build query to use AS and functions
- added description to the user_queries and Load/Save Query Windows
Dong Shin 08.30.2013
- continue working on Query Builder (FA)
- added support for the columns that don’t exist in the column config. This allows SQL functions and alias names using ‘AS’
- added GROUP BY support
- reworked WHERE configuration to work with GROUP BY
- SparkResizableTitleWindow is now functional
Dong Shin 08.29.2013
- continue working on Query Builder (FA)
- added Obligation/Outlay goals to the query
- added Export to Viz button (not functional, yet!)
- created SparkResizableTitleWindow (needs more work!)
- working on queries
- adding date calculations to the query xml config
- Flash Builder 4.7 allows more Java Heap Memory allocation (64bit!!) – seems to solve some slowness issues after increasing it to 2GB
Dong Shin 08.28.2013
- continue working on Query Builder (FA)
- changed the way how Obligated/Outlay/PM Actual columns are handled. Had all the columns and set the visible property to false on all, but the one that’s selected. This seems confuses the FooterDatagrid as it showed all the footers in the bottom,….. Now one column for each, just change the dataField properties when the month selection happens.
- need to delete unnecessary columns from obligations_outlays_goals
- ALTER TABLE `obligations_outlays_goals` DROP `project_id`, DROP `funding_id`;
- query to find obligations/outlays/pm actuals and goals/percents
- SELECT project_number, req_description, req_id, projects.appropriation, budget_centers.total_budget,
GROUP_CONCAT(o.uid),
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_1, 0)) AS obligated_month_1, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_2, 0)) AS obligated_month_2,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_3, 0)) AS obligated_month_3, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_4, 0)) AS obligated_month_4,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_5, 0)) AS obligated_month_5, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_6, 0)) AS obligated_month_6,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_7, 0)) AS obligated_month_7, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_8, 0)) AS obligated_month_8,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_9, 0)) AS obligated_month_9, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_10, 0)) AS obligated_month_10,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_11, 0)) AS obligated_month_11, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_12, 0)) AS obligated_month_12,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_1, 0)) AS outlay_month_1, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_2, 0)) AS outlay_month_2,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_3, 0)) AS outlay_month_3, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_4, 0)) AS outlay_month_4,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_5, 0)) AS outlay_month_5, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_6, 0)) AS outlay_month_6,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_7, 0)) AS outlay_month_7, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_8, 0)) AS outlay_month_8,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_9, 0)) AS outlay_month_9, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_10, 0)) AS outlay_month_10,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_1, 0)) AS pm_actuals_month_1, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_2, 0)) AS pm_actuals_month_2,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_3, 0)) AS pm_actuals_month_3, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_4, 0)) AS pm_actuals_month_4,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_5, 0)) AS pm_actuals_month_5, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_6, 0)) AS pm_actuals_month_6,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_7, 0)) AS pm_actuals_month_7, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_8, 0)) AS pm_actuals_month_8,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_9, 0)) AS pm_actuals_month_9, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_10, 0)) AS pm_actuals_month_10,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_11, 0)) AS pm_actuals_month_11, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_12, 0)) AS pm_actuals_month_12,(g.obligation_month_1 * budget_centers.total_budget) / 100 AS obligation_goal_month_1,
(g.obligation_month_2 * budget_centers.total_budget) / 100 AS obligation_goal_month_2,
(g.obligation_month_3 * budget_centers.total_budget) / 100 AS obligation_goal_month_3,
(g.obligation_month_4 * budget_centers.total_budget) / 100 AS obligation_goal_month_4,
(g.obligation_month_5 * budget_centers.total_budget) / 100 AS obligation_goal_month_5,
(g.obligation_month_6 * budget_centers.total_budget) / 100 AS obligation_goal_month_6,
(g.obligation_month_7 * budget_centers.total_budget) / 100 AS obligation_goal_month_7,
(g.obligation_month_8 * budget_centers.total_budget) / 100 AS obligation_goal_month_8,
(g.obligation_month_9 * budget_centers.total_budget) / 100 AS obligation_goal_month_9,
(g.obligation_month_10 * budget_centers.total_budget) / 100 AS obligation_goal_month_10,
(g.obligation_month_11 * budget_centers.total_budget) / 100 AS obligation_goal_month_11,
(g.obligation_month_12 * budget_centers.total_budget) / 100 AS obligation_goal_month_12,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_1 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_1,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_2 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_2,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_3 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_3,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_4 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_4,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_5 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_5,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_6 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_6,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_7 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_7,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_8 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_8,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_9 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_9,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_10 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_10,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_11 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_11,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_12 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_12,(g.outlay_month_1 * budget_centers.total_budget) / 100 AS outlay_goal_month_1,
(g.outlay_month_2 * budget_centers.total_budget) / 100 AS outlay_goal_month_2,
(g.outlay_month_3 * budget_centers.total_budget) / 100 AS outlay_goal_month_3,
(g.outlay_month_4 * budget_centers.total_budget) / 100 AS outlay_goal_month_4,
(g.outlay_month_5 * budget_centers.total_budget) / 100 AS outlay_goal_month_5,
(g.outlay_month_6 * budget_centers.total_budget) / 100 AS outlay_goal_month_6,
(g.outlay_month_7 * budget_centers.total_budget) / 100 AS outlay_goal_month_7,
(g.outlay_month_8 * budget_centers.total_budget) / 100 AS outlay_goal_month_8,
(g.outlay_month_9 * budget_centers.total_budget) / 100 AS outlay_goal_month_9,
(g.outlay_month_10 * budget_centers.total_budget) / 100 AS outlay_goal_month_10,
(g.outlay_month_11 * budget_centers.total_budget) / 100 AS outlay_goal_month_11,
(g.outlay_month_12 * budget_centers.total_budget) / 100 AS outlay_goal_month_12,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_1 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_1,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_2 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_2,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_3 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_3,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_4 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_4,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_5 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_5,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_6 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_6,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_7 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_7,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_8 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_8,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_9 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_9,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_10 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_10,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_11 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_11,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_12 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_12FROM projects
RIGHT JOIN budget_centers ON projects.uid = budget_centers.project_id
RIGHT JOIN budget_center_contracts ON budget_center_contracts.budget_center_id = budget_centers.uid
RIGHT JOIN obligations_outlays AS o ON o.project_id = projects.uid
RIGHT JOIN obligations_outlays_goals AS g ON projects.appropriation = g.appropriation
WHERE o.year = 2013 AND o.year_count = 1 AND (o.type = ‘Reported FACTS Obligated $:’ OR o.type = ‘Outlay $ (Reported in FACTS)’ OR o.type = ‘PM Actuals (Invoiced) Outlay $:’)
AND g.year = 1
GROUP BY funding_id
- SELECT project_number, req_description, req_id, projects.appropriation, budget_centers.total_budget,
Dong Shin 08.27.2013
- continue working on FA
- working on Query Builder
- added Obligations/Outlays/PM Actuals to the query
- added Obligations/Outlays/PM Actuals Check Boxes
- created GLOBALS.as class for global constants and static methods
- working on Query Builder
Dong Shin 08.26.2013
- continue working on FA
- working on Query Builder
- working on queries for Chris
- saving queries to QueryBuilderQueries.sql in resources/database/
- adding Obligations/Outlays to the Query Builder
- query to find obligaed, outlay, pm actuals
- SELECT projects.*, bc.*, bcc.*,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_1, 0)) AS obligated_month_1,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_2, 0)) AS obligated_month_2,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_3, 0)) AS obligated_month_3,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_4, 0)) AS obligated_month_4,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_5, 0)) AS obligated_month_5,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_6, 0)) AS obligated_month_6,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_7, 0)) AS obligated_month_7,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_8, 0)) AS obligated_month_8,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_9, 0)) AS obligated_month_9,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_10, 0)) AS obligated_month_10,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_11, 0)) AS obligated_month_11,
SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_12, 0)) AS obligated_month_12,SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_1, 0)) AS outlay_month_1,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_2, 0)) AS outlay_month_2,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_3, 0)) AS outlay_month_3,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_4, 0)) AS outlay_month_4,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_5, 0)) AS outlay_month_5,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_6, 0)) AS outlay_month_6,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_7, 0)) AS outlay_month_7,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_8, 0)) AS outlay_month_8,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_9, 0)) AS outlay_month_9,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_10, 0)) AS outlay_month_10,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_11, 0)) AS outlay_month_11,
SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_12, 0)) AS outlay_month_12,SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_1, 0)) AS pm_actuals_month_1,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_2, 0)) AS pm_actuals_month_2,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_3, 0)) AS pm_actuals_month_3,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_4, 0)) AS pm_actuals_month_4,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_5, 0)) AS pm_actuals_month_5,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_6, 0)) AS pm_actuals_month_6,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_7, 0)) AS pm_actuals_month_7,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_8, 0)) AS pm_actuals_month_8,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_9, 0)) AS pm_actuals_month_9,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_10, 0)) AS pm_actuals_month_10,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_11, 0)) AS pm_actuals_month_11,
SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_12, 0)) AS pm_actuals_month_12FROM projects
RIGHT JOIN budget_centers AS bc ON projects.uid = bc.project_id
RIGHT JOIN budget_center_contracts AS bcc ON bcc.budget_center_id = bc.uid
RIGHT JOIN obligations_outlays AS o ON o.project_id = projects.uid
WHERE o.year = 2013 AND o.year_count = 1
AND (o.type = ‘Reported FACTS Obligated $:’ OR o.type = ‘Outlay $ (Reported in FACTS)’ OR o.type = ‘PM Actuals (Invoiced) Outlay $:’)
GROUP BY funding_id
Dong Shin 08.20.2013
- continue working on RA/FA
- working on Auto-Fill to 100% – done. Auto-fills all the way to the end of the appropriation years
- added a ProgressBar to RequistionsView in RA
- working on QueryBuilder
Dong Shin 08.20.2013
- deployed new RA, few bugs @ requirements page
- updated financial data – $0 up to July for FY13 projects
- working on FA/RA
- clear data on logout of RA
- working on RDT&E and Procurement not creating financial data correctly…..
- fixed in FA – wasn’t getting correct Appropriation Lenth
- need to fix the database manually – FixFinancialData.sql in resources directory under FA
- FA’s user managements menus are all functional
- FA’s COGNOS Data Mgmt is functional
- working on Auto-Filling of 100%
Dong Shin 08.19.2013
- continue working on FA4/RA
- working on the project status query for RA – done!
- added project budget information to RA (filterable, configurable)
- refreshing RA data keeps the filter…
- FMP/Invoice not required for Req’s with $0 funded budget
- fixed status filter not working
- working on Dynamic Panel not working – ManagedCanvas.minimizeAll()
Dong Shin 08.16.2013
- continue working on FA4/RA
- all components in Dynamic Panel must be wrapped in <s:Scroller /> to resize properly!
- going through the list from yesterday’s meeting
- removed Projects View
- created FilterableDGSpark for Spark DataGrid
- reworking to get status from three row types instead of all row types (Obligation Outlay Types)
- database update!
- ALTER TABLE `obligation_outlay_types` CHANGE `type` `type` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
- ALTER TABLE `obligation_outlay_types` ADD `status` TINYINT NULL DEFAULT NULL ;
- query to get OVERDUE requisitions
- SELECT ‘DUE’ AS status, funding_id, c.* FROM (
SELECT funding_id FROM (
SELECT * FROM (
SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_1)
AND (o.type in (SELECT `type` FROM obligation_outlay_types WHERE status = 1 ))
GROUP BY year, year_count, funding_id
UNION
SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_2)
AND (o.type in (SELECT `type` FROM obligation_outlay_types WHERE status = 1 ))
GROUP BY year, year_count, funding_id
AND (o.type in (SELECT `type` FROM obligation_outlay_types WHERE status = 1 ))
GROUP BY year, year_count, funding_id
) AS q WHERE null_count = (SELECT COUNT(*) FROM obligation_outlay_types WHERE status = 1) ) AS t ) AS t2
LEFT JOIN budget_centers c ON t2.funding_id = c.uid
- SELECT ‘DUE’ AS status, funding_id, c.* FROM (
Dong Shin 08.15.2013
- VizTool meeting – notes @ requirements page…
- working on Dynamic Panel – done!
- checked in as DynamicPanel Flex Component project
- continue working on FA4
- incorporating the new Dynamic Panel
Dong Shin 08.14.2013
- continue working on FA4
- it looks like everything has been converted…
- testing!
- DynamicPanel acting weird – components on upper right corner not getting clicked, events get to TitleBar instead…
- rewriting DynamicPanel using spark skins…..
Dong Shin 08.13.2013
- continue working on FA4
- adding QueryBuilder
viztales 
You must be logged in to post a comment.