Monthly Archives: March 2014

Dong Shin 03.31.2014

  • deployed database updates for the big data table…. lot quicker than existing query!
    • add FMP’s and invoices?
    • tried to ingest to Visibility, but too may long fields to try….
    • added more fields to format as Currency
  • changed the colors of the chart items in Req Data Analysis panel to match the briefing charts
  • working on queries to update data when claim/un-claim happens in Project Editor

Phil 3.28.14

8:00 – 4:00 SR

  • Did not see snow today.
  • Backups.
  • Showed Tangie a screenshot of the roles page – some people want more access?
  • JavaScript
  • Chapter 8 and 9. Starting on 10.
    • The HUD example should probably serve as the basis for the framework. It’s got picking, and 2D overlay. No lighting or reflection maps though. It’s a good start though. Will need to have a shape manager and then shapes. Also dprint.
  • wow: http://glsl.heroku.com/

Dong Shin 03.28.2014

  • reworking Req Data Analysis using the new view – __view_project_detailed_data
    • done… lot faster than before!
    • committed amounts not cumulative, working on it. fixed! using functions….
  • created few support functions in mysql
    • get_cumulative_committed_amount(budget_center_id, year_count, month_number)
    • get_fiscal_month (Date)
    • get_fiscal_year (Date)
  • using MySQL Workbench instead of phpMyAdmin – more robust and convenient!

Dong Shin 03.27.2014

  • working on generating big data table for FA. __view_project_detailed_data
    • created sub views to work-around nested SELECT limitation in view
      • __view_yearly_totals_by_project, __view_financial_analysts_by_project, __view_portfolio_mgrs_by_project, __view_total_committed_amount_by_budget_center, __view_service_pocs_by_budget_center
    • remember not t use nested SELECTs in views!!!!
    • adding monthly committed amounts take too long to process….. 3 sec compared to 1 sec…
    • *THIS* is the join that produces a year data per row that has all obligations, outlays, and goals!
      • SELECT SUM(IF(oo.type LIKE ‘%Planned%Oblig%’,1,0)) AS planned_obligated_count,
        SUM(IF(oo.type LIKE ‘%Reported%Oblig%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%Planned%Outlay%’,1,0)) AS planned_outlay_count,
        SUM(IF(oo.type LIKE ‘%Reported%Outlay%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%PM%Actuals%’,1,0)) AS pm_actuals_count,
        SUM(IF(oo.type LIKE ‘%Outlay%FACTS%’,1,0)) AS outlay_facts_count,
        p.*, bc.*
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN obligations_outlays_goals oog ON oog.appropriation = p.appropriation AND oog.year = oo.year_count
        LEFT JOIN committed_amounts ca ON ca.budget_center_id = bc.uid AND (bc.fiscal_year – YEAR(ca.committed_date) + 1) = oo.year_count
        LEFT JOIN __view_yearly_totals_by_project AS yt ON yt.project_id = p.uid
        LEFT JOIN __view_financial_analysts_by_project AS fa ON fa.project_id = p.uid
        LEFT JOIN __view_portfolio_mgrs_by_project pm ON pm.project_id = p.uid
        LEFT JOIN __view_service_pocs_by_budget_center AS spbc ON spbc.budget_center_id = bc.uid
        LEFT JOIN __view_total_committed_amount_by_budget_center ca_totals ON ca_totals.budget_center_id = bc.uid
        GROUP BY oo.funding_id, oo.year_count

Phil 3.27.14

8:00 – 5:00 SR

  • Backups
  • Testing Dong’s theory that spaces are killing the VSS query. Well, it was either that or the wrong quoting of the table name in the python script.
  • Broken Queries
    • Projects_underbudget
    • alert_1_30_days_overdue
    • alert_2_planned_values
    • alert_3_FMPs
    • alert_4_not_obligated_within_30_days
    • alert_5_not_accepted_within_30_days
    • alert_6_no_outlays_within_60_days
    • alert_7_UsersNotLoggedIn30days returns, but MIPR, portfolio_administrator, portfolio_manager, program, service_finance_pocs and service_project_managers all come up null
  • JavaScript
    • Installed WebStorm 8.0, as our support license expires on Monday.
    • It turns out that you can get a glsl plugin. Very nice. http://plugins.jetbrains.com/plugin/6993?pr=
    • Hmm. I’m starting to think that I could pass up the points in a Mass-Spring-Damper to the GPU and just update the clock if there is either (a) a way to save information between frames or (b) a way to write back into the array buffers. All the needed math functions are there. The problem is that GLSL appears to only do calculation on a per-vertex basis. Oh, wait, there’s this: http://webcl.nokiaresearch.com/

Dong Shin 03.26.2014

  • deployed new views and presentation looks better!
  • few bugs
    • fix __view_financial_data – add sum and group by for year 2
    • Planned Obligations/Outlay Update SQL errors when it reaches 100% – uid, project_id, year_count are null
    • Recalculate Obligations/Outlays when Claimed/Unclaimed Contracts are updated
    • rework Query Builder!
  • bc.total_budget for Req Allocation and vct.committed_amount for Req Funded
  • downloaded and played with JSPMyAdmin – JSP based MySQL Admin tool that can run on Tomcat…. a bit slow and looks like missing lots of features compared to phpMyAdmin, not sure if I want to use it.
  • working on getting entire data for big table
    • this may be good enough?
      • SELECT *
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(fiscal_year) AS fiscal_years,
        SUM(budget_amount) AS budget_amount FROM yearly_totals GROUP BY project_id) AS yt ON yt.project_id = p.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(login) AS financial_analysts FROM _projects_portfolio_mgrs GROUP BY project_id) AS fa ON fa.project_id = p.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(login) AS portfolio_managers FROM _projects_portfolio_admins GROUP BY project_id) AS pm ON pm.project_id = p.uid
        LEFT JOIN (SELECT budget_center_id, SUM(committed_amount) AS total_committed_amount, GROUP_CONCAT(committed_date) AS committed_dates
        FROM committed_amounts GROUP BY budget_center_id) AS ca ON ca.budget_center_id = bc.uid

Phil 3.26.14

8:00 – 4:00 SR

  • Backups
  • Got my copy of the OpenGL ES programming guide
  • Database updates
  • The DbTable.writeToVizDb() Query: is not showing the query
  • There is a ScriptFacades.dbStoredQuery saying that there is a syntax error at line 1 in  listing of a ‘2013 Summary by Appropriation and Capability’. Turns out that it was spaces in the column names. We’ll fix that tomorrow.
  • Javascript
    • NURBS and such for way-cool charts. Trust me on this. http://verbnurbs.com/
    • Firefox apparantly has some odd bug. When gl.getShaderInfoLog(shader) is called, it returns a string that breaks Strings. Assigning it to a string or sending it to the cosole causes JavaScript to become unresponsive. The results can be put into an alert, though about 50% of the time, the following error appears: TelemetryStopwatch: key “FX_PAGE_LOAD_MS” was already initialized resource://gre/modules/TelemetryStopwatch.jsm
      NS_ERROR_NOT_AVAILABLE: prompt aborted by user resource://gre/components/nsPrompter.js
    • This appears to be a known FF bug?
    • Nice online shader editor: http://shdr.bkcore.com/
    • Damn – *Firefox* has built in shader editors!
    • So now that we’ve had the opportunity to see some helpful error messages, here’s how you set up and use multiple array buffers in WebGL:

First, the shaders. Data comes into the vertex shader and is then copied over to the varying variable(s)

var VSHADER_SOURCE =
  'attribute vec4 a_Position;\n' +
  'attribute vec4 a_Color;\n' +
  'varying vec4 v_Color;\n' +
  'void main() {\n' +
  '  gl_Position = a_Position;\n' +
  '  gl_PointSize = 5.0;\n' +
  '  v_Color = a_Color;\n' +
  '}\n';

// Fragment shader program
var FSHADER_SOURCE =
    '#ifdef GL_ES\n' +
    '   precision mediump float;\n' +
    '#endif\n' +
    'varying vec4 v_Color;\n' +    // Receive the data from the vertex shader
    'void main() {\n' +
    '   gl_FragColor = v_Color;\n' +
    '}\n';

Then we create the data:

    // Create a buffer object
    g_vertexBuffer = gl.createBuffer();
    g_colorBuffer = gl.createBuffer();
    if (!g_vertexBuffer || !g_colorBuffer) {
        var msg = 'Failed to create the buffer object';
        console.log(msg);
        alert(msg);
        return -1;
    }

    // Bind the buffer object to target
    gl.bindBuffer(gl.ARRAY_BUFFER, g_vertexBuffer);
    var a_Position = gl.getAttribLocation(gl.program, 'a_Position');
    if (a_Position < 0) {
        var msg = 'Failed to get the storage location of a_Position';
        console.log(msg);
        alert(msg);
        return -1;
    }
    // Assign the buffer object to a_Position variable
    gl.vertexAttribPointer(a_Position, 2, gl.FLOAT, false, 0, 0);
    // Enable the assignment to a_Position variable
    gl.enableVertexAttribArray(a_Position);

    // Bind the buffer object to target
    gl.bindBuffer(gl.ARRAY_BUFFER, g_colorBuffer);
    var a_Color = gl.getAttribLocation(gl.program, 'a_Color');
    if (a_Position < 0) {
        var msg = 'Failed to get the storage location of a_Color';
        console.log(msg);
        alert(msg);
        return -1;
    }
    // Assign the buffer object to a_Color variable
    gl.vertexAttribPointer(a_Color, 3, gl.FLOAT, false, 0, 0);
    // Enable the assignment to a_Position variable
    gl.enableVertexAttribArray(a_Color);

    // clear everything at the end
    gl.bindBuffer(gl.ARRAY_BUFFER, null);

Last, we enter the drawing loop. First, we get the handles to the attributes, then sequentially bind, enable and overwrite their data buffers

    var a_Position = gl.getAttribLocation(gl.program, 'a_Position');
    var a_Color = gl.getAttribLocation(gl.program, 'a_Color');
    // Clear 
    gl.clear(gl.COLOR_BUFFER_BIT);

    // make some new vertex data
    for(var i = 0;i < (n*2); ++i){
        vertexDataArray[i] = Math.random()*2.0 - 1.0; // make some new vertex positions
    }
    // Write date into the vertex buffer object
    gl.bindBuffer(gl.ARRAY_BUFFER, g_vertexBuffer);
    gl.enableVertexAttribArray(a_Position);
    gl.bufferData(gl.ARRAY_BUFFER, vertexDataArray, gl.STATIC_DRAW); // use the new vertex positions
   
    // Write date into the vertex buffer object
    gl.bindBuffer(gl.ARRAY_BUFFER, g_colorBuffer);
    gl.enableVertexAttribArray(a_Color);
    gl.bufferData(gl.ARRAY_BUFFER, colorDataArray, gl.STATIC_DRAW); // write the same color values

    gl.bindBuffer(gl.ARRAY_BUFFER, null);

    // Draw the points
    gl.drawArrays(gl.POINTS, 0, n);

Phil 3.25.14

8:00 – 5:30 SR

  • Backups
  • Deployed new FA, VSS, and some new views
  • Meeting with Chris, Tangie, Carla, Betty, Pat and Lenny going over the VizTool presentation
  • Back to JavaScript and WebGL
  • Spent a good deal of time working with how to get multiple buffers to work. Here’s the basic pattern:
function draw(gl, n) {
    // Clear <canvas>
    gl.clear(gl.COLOR_BUFFER_BIT);

    // repeat the following two lines for all your buffers
    gl.bindBuffer(gl.ARRAY_BUFFER, g_vertexSizeBuffer);
    gl.bufferData(gl.ARRAY_BUFFER, g_verticesSizes, gl.STATIC_DRAW);
 
    // once done with updating the data unbind.
    gl.bindBuffer(gl.ARRAY_BUFFER, null);

    // Draw the points
    gl.drawArrays(gl.POINTS, 0, n);
}
  • The basic idea is to bind a buffer, then write into it, write into another, rinse, lather repeat until all the buffers (or whichever ones you pick) are updated. Then tell WebGL to go draw all the buffers. Here’s an example:
  • points
  • Starting on textures.

Dong Shin 03.25.2014

  • VizTool review
    • All fields with $ should accept negative values
    • get a list of budget values for FY13 and FY14 charts
    • create a query for Planned Obligated vs. FACTS
    • Outlay $ Reported row should not be editable
    • invoice dialog box should not show if the line item is not a MIPR or EAO
    • total budget in slides should be project total, not requistiion total
    • Req Data Analysis should show Req Allocated, Req Funded (Committed)
  • reworked __view_monthly_committed and __view_financial_data
    • returns view in projects instead of requisitions….

Dong Shin 03.24.2014

  • reworked all stored queries for briefing data to have 5 months, fixed month_2 typos – user_queries.sql
  • removed year 3 and 4 to speed up __view_financial_data view – DBUpdateEnhancements.032414.sql
  • created queries for Lenny – summary of FY13 and FY14 by appropriation and capability
    • FY13 Summary by Appropriation and Capability, FY14 Summary by Appropriation and Capability
    • select capability, appropriation,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.total_budget, 0)) AS total_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.funded_budget, 0)) AS funded_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, oo.month_5, 0)) AS ‘Obligated Feb 2013’,

      SUM(IF(oo.type = ‘Outlay $ (Reported in FACTS)’, oo.month_5, 0)) AS ‘Outlay Feb 2013’,

      SUM(IF(oo.type = ‘PM Actuals (Invoiced) Outlay $:’, oo.month_5, 0)) AS ‘PM Actuals Feb 2013’,

      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, committed_amount,0 )) AS total_committed
      from projects p
      LEFT JOIN budget_centers bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      LEFT JOIN __view_committed_totals vct ON bc.uid = vct.budget_center_id
      where fiscal_year = 2013 AND oo.year_count = 2 AND
      (oo.type = ‘Outlay $ (Reported in FACTS)’ OR
      oo.type = ‘Reported FACTS Obligated $:’ OR
      oo.type = ‘PM Actuals (Invoiced) Outlay $:’)
      GROUP BY capability, appropriation