Category Archives: Dong Shin

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.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.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.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

Dong Shin 09.05.2013

  • 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 ‘

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…