Monthly Archives: October 2013

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
Advertisements

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

Phil 10.29.13

8:00 – 11:30 SR

  • WordPress was too slow to add things yesterday morning
  • Weird problems with the server. SQL queries that were going through remote objects in Dong’s code were returning “Prolog Error” responses. In the end, restarting Tomcat fixed everything.
  • This may have been the problem for numerous bugs, such as obligations not being calculated. After rerunning the scripts, that all seemed to be fixed.
  • Backups
  • No progress on G2C
  • RA initial and refresh query is running *very* slow – over 30 seconds.

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

Phil 10.28.13

8:00 – 12:30 SR

  • In Reqonciler, an empty result causes the query sequence to break
  • Have to select “refresh” several times when copying a project before it shows up on the project management list
  • Fix Lenny’s bug so that it shows a scanned $100 bill and says, “Lenny! Here’s some money!”

– TIMEOFF

  • Prepping for Thursday comprehensive exam.

Phil 10.25.13

8:00 – SR

  • Backups
  • Painful server paperwork.
  • YUI – more chapter 4

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

Phil 10.24.13

8:00 – 4:00 SR

  • Backups
  • Based on conversations with Lenny and Dong, changed some of the requirements for the ReQonciler.
  • Beat on training a bit more. Done and registered!
  • YUI – Chapter 4.

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