Phil 11.12.13

8:00 – 4:00 SR

  • Back from CA and YUI class. It was 72-76 and clear blue skies out there. Here it’s 40 with sprinkles and gloomy clouds. But our bad traffic is way better than their bad traffic.
  • Backups
  • Need to do status for Tangie. Done.
  • Composing email to Jenny Donnelly about YUI
  • Adding Typescript (Cool Typescript Playground)
  • Following directions on the JetBrains site
    • Downloading and installing node.js
    • added NODE_JS_HOME as a path variable
    • installed the typescript package. Note that it gets installed to the user directory, not the node dir: C:\Users\Phil\AppData\Roaming\npm

  • Pressed the “Configure” button, which downloaded sources
  • Poked at the “set scope” link, which doesn’t have an obvious effect.
  • Clicked on “Add Watcher” in the pop-up alert (shown below) that appeared when I tried to save “typescriptTest.ts”

watcher_prompt

  • Added the C:\Users\Phil\AppData\Roaming\npm\tsc.cmp path to the watcher config dialog and then clicked “OK”
  • Ok, so now Typescript works. However, integrating it looks to be a pain..? But at the same time, it looks like there is a GitHub project that has set up all the interfaces?
  • Ok, the typescript runs, the watcher converts it, and a small HTML file launches it.

TypeScript:

class Greeter {
    greeting: string;
    constructor(message: string) {
        this.greeting = message;
    }
    greet() {
        return "Hello, " + this.greeting;
    }
}

var greeter = new Greeter("typescript");

var button = document.createElement('button');
button.textContent = "Say Hello";
button.onclick = function() {
    alert(greeter.greet());
}

document.body.appendChild(button);

Transpiled JavaScript:

var Greeter = (function () {
    function Greeter(message) {
        this.greeting = message;
    }
    Greeter.prototype.greet = function () {
        return "Hello, " + this.greeting;
    };
    return Greeter;
})();

var greeter = new Greeter("typescript");

var button = document.createElement('button');
button.textContent = "Say Hello";
button.onclick = function () {
    alert(greeter.greet());
};

document.body.appendChild(button);
//# sourceMappingURL=typescriptTest.js.map

HTML (Calling JavaScript)

<!DOCTYPE html>
<html>
<title>TypeScript test</title>
<body>
<script type="text/javascript" src="typescriptTest.js">
</script>
</body>
</html>

Phil 11.1.13

8:00 – 4:00 SR

  • Backups
  • Deployed new versions of FA, RA, and Reqonciler
  • Debugged queries with Dong
  • Started to write a Cognos fake data generator. This will produce a .sql file containing a set of projects and a series of excel files that will mimic Cognos downloads. The excel files will have errors in them that mimic what we’re finding in the Cognos pulls that Lenny is doing. A text file will also be produced that lists the number and types of errors. THe goal is that Dong should be able to upload the sql files (which will truncate the project tables and reload them) and then ingest (probably 3) spreadsheets. If there are no errors and all the numbers add up correctly, then we have a bug free system. At this point the project and rough framework are done.

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

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.

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