Author Archives: gvr4wd

Unknown's avatar

About gvr4wd

...hmmm

Dong Shin 11.21.2013

  • new Furniture!
  • working on Reqonciler
    • trouble-shooting incorrect ingest… changed budget_center to sub_budget_center in post-processing query
    • added update obligations and outlays queries to the post processing

Dong Shin 11.20.2013

  • deployed FA – Export to Viz works!
    • fixed table name not using the textInput
  • duplicate projects listed in Project Mgmt Panel – fixed
    • caused by incorrect parent id set from the server….. 0 instead of NULL
    • must use ResultSet.wasNull() to see if the last getXXX returned is NULL
  • created test XSLX spreadsheet to duplicate incorrect Outlay data on-site….

Dong Shin 11.14.2013

  • deployed new FA, RA, FinancialAssistant.jar, and queries
    • Obligations/Outlays not meeting goals (95%) should be navigated thru month combobox
    • change the query to include all fiscal years for the selected FY
    • Excel export from Query Builder
    • FA missing Roles Management panel
    • modified Overdue Obligations/Outlay queries to ignore more than 2000 days….
  • working on FA
    • changed the query to return all the data for the selected FY
    • added UserRolesManagement panel from FA3
    • working on queries to navigate obligations/outlays not meeting goals by monthly

Dong Shin 11.01.2013

  • FA/RA fixes
    • added Project ID and Project Name to RA
    • fixed filtering/searching not working.
    • added currency to queryBuilderConfix.xml
  • deployed new FA/RA and queries to update obligations/outlays
    • cc.po_reference = bcc.po_reference needed for __view_monthly_obligations_outlays_by_contract

Dong Shin 10.30.2013

  • changed Numeric to Currency in queryBuilderConfig.xml, Numeric is now number, now $ values
  • working on Outlays query…
    • decided to update the outlays data similar to obligations, may not have to use python script at all.
    • created views to replace the views created yesterday!
      • __view_monthly_obligations_outlays_by_contract, to be used for both outlay and obligations
      • __view_monthly_obligations_outlays_by_req_id, to be used to update obligations_outlays table
    • two update queries
      • UPDATE obligations_outlays oo
        LEFT JOIN __view_monthly_obligations_outlays_by_req_id AS vv
        ON vv.bc_uid = oo.funding_id
        SET oo.month_1 = vv.month_1_obligated, oo.month_2 = vv.month_2_obligated, oo.month_3 = vv.month_3_obligated, oo.month_4 = vv.month_4_obligated,
        oo.month_5 = vv.month_5_obligated, oo.month_6 = vv.month_6_obligated, oo.month_7 = vv.month_7_obligated, oo.month_8 = vv.month_8_obligated,
        oo.month_9 = vv.month_9_obligated, oo.month_10 = vv.month_10_obligated, oo.month_11 = vv.month_11_obligated, oo.month_12 = vv.month_12_obligated
        WHERE type = ‘Reported FACTS Obligated $:’
        AND year=2013 AND year_count=1
      • UPDATE obligations_outlays oo
        LEFT JOIN __view_monthly_obligations_outlays_by_req_id AS vv
        ON vv.bc_uid = oo.funding_id
        SET oo.month_1 = vv.month_1_outlay, oo.month_2 = vv.month_2_outlay, oo.month_3 = vv.month_3_outlay, oo.month_4 = vv.month_4_outlay,
        oo.month_5 = vv.month_5_outlay, oo.month_6 = vv.month_6_outlay, oo.month_7 = vv.month_7_outlay, oo.month_8 = vv.month_8_outlay,
        oo.month_9 = vv.month_9_outlay, oo.month_10 = vv.month_10_outlay, oo.month_11 = vv.month_11_outlay, oo.month_12 = vv.month_12_outlay
        WHERE type = ‘Outlay $ (Reported in FACTS)’
        AND year=2013 AND year_count=1

Dong Shin 10.29.2013

  • weird MySQL database problem on-site, but resolved – details at Phil’s blog
  • very slow query response in RA retrieving list of projects on-site
    • create a buffer table?
    • enable cache? – http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html
  • working on Obligations Update query – update_obligations_queries.sql
    • updated queries to work on all obligations, not just EA
    • remove obligations update part in the python script!
    • create two views to stage the data for update
      • __view_obligations_by_contracts - monthly obligations data by requisition id in COGNOS data
      • __view_obligations_by_req_id - monthly obligations summary data by req id used in FA
    • update query for Obligations/Outlays table
      • UPDATE obligations_outlays oo
        LEFT JOIN __view_obligations_by_req_id AS vv
        ON vv.bc_uid = oo.funding_id
        SET oo.month_1 = vv.month_1, oo.month_2 = vv.month_2, oo.month_3 = vv.month_3, oo.month_4 = vv.month_4, oo.month_5 = vv.month_5, oo.month_6 = vv.month_6,
        oo.month_7 = vv.month_7, oo.month_8 = vv.month_8, oo.month_9 = vv.month_9, oo.month_10 = vv.month_10, oo.month_11 = vv.month_11, oo.month_12 = vv.month_12
        WHERE type = ‘Reported FACTS Obligated $:’
        AND year=2013 AND year_count=1

Dong Shin 10.28.2013

  • deployed Reqonciler, few bugs
    • fixed not going to next pre-processing when no ignore is selected
    • added uniqueColumnIdName to setupModel.xml for to use uid of the table
    • made debug menu visible on login
  • working on correct EA Obligations calculation for Query Builder
    • created two view – MySQL doesn’t allow creating a view with sub query!!!
      • __view_ea_obligations_cognos – individual months by cognos req_id
      • __view_ea_obligations_by_req_id – summary using __view_ea_obligations_cognos
      • based on this query -SELECT bc_uid, sub_budget_center, requisition_id, funded_budget,
        SUM(month_1_obligated) AS month_1,
        SUM(GREATEST (month_1_obligated, month_2_obligated)) AS month_2,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated)) AS month_3,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated)) AS month_4,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated)) AS month_5,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated)) AS month_6,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated)) AS month_7,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated)) AS month_8,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated)) AS month_9,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated)) AS month_10,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated, month_11_obligated)) AS month_11,
        SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated, month_11_obligated, month_12_obligated)) AS month_12
        FROM (
        SELECT bc.uid as bc_uid, bc.sub_budget_center_number AS sub_budget_center, bc.req_id AS requisition_id, bc.funded_budget AS funded_budget,
        SUM(IF(month(cc.obligation_date)=10, cc.obligated_amount, 0)) as month_1_obligated,
        SUM(IF(month(cc.obligation_date)=11, cc.obligated_amount, 0)) as month_2_obligated,
        SUM(IF(month(cc.obligation_date)=12, cc.obligated_amount, 0)) as month_3_obligated,
        SUM(IF(month(cc.obligation_date)=1, cc.obligated_amount, 0)) as month_4_obligated,
        SUM(IF(month(cc.obligation_date)=2, cc.obligated_amount, 0)) as month_5_obligated,
        SUM(IF(month(cc.obligation_date)=3, cc.obligated_amount, 0)) as month_6_obligated,
        SUM(IF(month(cc.obligation_date)=4, cc.obligated_amount, 0)) as month_7_obligated,
        SUM(IF(month(cc.obligation_date)=5, cc.obligated_amount, 0)) as month_8_obligated,
        SUM(IF(month(cc.obligation_date)=6, cc.obligated_amount, 0)) as month_9_obligated,
        SUM(IF(month(cc.obligation_date)=7, cc.obligated_amount, 0)) as month_10_obligated,
        SUM(IF(month(cc.obligation_date)=8, cc.obligated_amount, 0)) as month_11_obligated,
        SUM(IF(month(cc.obligation_date)=9, cc.obligated_amount, 0)) as month_12_obligated
        FROM budget_centers bc
        LEFT JOIN budget_center_contracts AS bcc ON bc.uid = bcc.budget_center_id
        LEFT JOIN contracts_cognos AS cc ON cc.sub_budget_center = bcc.sub_budget_center
        AND SUBSTRING(cc.requisition_id, 5) = bcc.requisition_id
        WHERE bc.req_type = ‘EA’ AND to_seconds(cc.obligation_date) > 59958230400
        GROUP BY cc.requisition_id) AS t
        GROUP BY bc_uid

Dong Shin 10.25.2013

  • working on Reqonciler
    • added a method to the server to save the data on XLSX parse
    • changed Reqonciler to call the new method, importXLXSFile()
    • working on Post Processing queries
      • added three quries for post-processing, update summary to budget_center_contracts, add new summary, update vendor information
    • added searchTextInput to ReviewQueriesWindows and PreProcessingWindow

Dong Shin 10.24.2013

  • went over COGNOS data pull with Lenny –  disappeared commitments have appeared again….
  • asked Lenny to put list of data conflicts that should be reviewed
  • working on Reqonciler
    • pre and post processing done!
    • working on pre and post queries for FinancialAssitant!
    • working on server code to keep historical data
      • created class based on ContactsParser – ReqoncilerDataParser.java
      • added prop to FinancialAssistant.properties – reqonciler.tableName

Dong Shin 10.23.2013

  • deployed new FinancialAssistant.jar – solved the year problem
  • fixed obligations/outlays import queries to correct doubling/tripling committed amount and obligations
  • still problem on obligations with multiple req’s
  • meeting at NBP today at 1