Monthly Archives: March 2014

Dong Shin 03.07.2014

  • deployed SQL’s update the obligations/outlays to 100% – works!
  • found that some of the COGNOS contracts data have budget_center_id set to 0, it should be null or some budget_center_id. This caused the data not to show up in claimed/unclaimed contracts in Req Info panel
    • cleaned up 0’s and some of the data that were deleted
    • need a SQL to clean up before mapping?
  • looked at Carla’s project saving error, caused by long budget center name from budget_center_names table. cleaned up the table.

Dong Shin 03.06.2014

  • trying to figure out if months can be updated in a single query……. instead of separate queries for each month!
    • UPDATE obligations_outlays oo
      SET month_12 = CASE
      WHEN (month_11 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_11
      WHEN (month_10 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_10
      WHEN (month_9 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_9
      WHEN (month_8 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_8
      WHEN (month_7 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_7
      WHEN (month_6 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_6
      WHEN (month_5 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_5
      WHEN (month_4 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_4
      WHEN (month_3 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_3
      WHEN (month_2 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_2
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_12 END,
      month_11 = CASE
      …………………….
      month_2 = CASE
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_2 END
      WHERE oo.year_count = 1
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • finally got the query working! – that update first month second year when last month first year is at 100%
    • UPDATE obligations_outlays oo1
      LEFT JOIN obligations_outlays oo2
      ON (oo1.project_id = oo2.project_id
      AND oo1.funding_id = oo2.funding_id
      AND oo1.type = oo2.type
      AND oo1.uid <> oo2.uid)
      LEFT JOIN budget_centers bc
      ON( oo1.funding_id = bc.uid)
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
      AND (oo2.month_12 >= bc.total_budget)
      SET oo1.month_1 = oo2.month_12
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
  • added months updates queries tor year 2 in Post Processing
  • talked Kelley yesterday and passed along the contact info, she said she would email me the details, never did
    • emailed Kelley this morning and got response, forward it to Chris

Phil 3.6.14

8:00 – 4:00 SR

  • Backups
  • Renewed the viztool urn and deleted visibility. Well, tried, anyway.
  • JavaScript
    • Add scrollto text based on item clicked event – Done, but now the scrolling function seems kinda messed up. The scroller wants to bounce back to some point that seems to be related to long contact drag times. Short flicks work?
    • See if scrolling can fire an event that can be used to get the current visible text (id? label?) that can be used to determine what to show in the network chart. Alternatively, listen to click events from the wrapping node or potentially the <p> items. Done!
    • Break slideshow into sections that are relevant to the clicked items.

Phil 3.5.14

8:00 – 4:00 SR

  • Backups
  • Resubmitted more paperwork for to keep my server access
  • Fixed some queries with Dong. Did you know that in SQL 10+0 != 10+NULL?
  • JavaScript
    • Finally fixed the ‘max pixels’ bug in the PhysicsShape. Vectors were being calculated for anchor objects, when they should have been zero.
    • Boo. You can’t animate colors on a shape.

Dong Shin 03.05.2014

  • put old Reqonciler back to resolve the datagrid issue
  • updated 2nd year query – success after some fiddling…
    • clean obligation/outlays in the first step using SET month = 0, not SET month=NULL.
    • changed the query to update Obligations, not Outlays
  • query to update Obligations/Outlays when it reaches 100%?
    • UPDATE obligations_outlays oo
      SET month_12 = GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0))
      WHERE oo.year_count = 1
      AND GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0)) >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • query to find second year that needs to be set to 100%
    • SELECT IFNULL(oo1.month_1,0), IFNULL(oo2.month_12,0)
      FROM obligations_outlays oo1, obligations_outlays oo2, budget_centers bc
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND oo1.project_id = oo2.project_id AND oo1.funding_id = oo2.funding_id AND oo1.type = oo2.type
      AND (
      oo1.type = ‘Outlay $ (Reported in FACTS)’
      OR oo1.type = ‘Reported FACTS Obligated $:’
      )
      AND oo2.month_12 >= bc.total_budget
      AND oo1.funding_id = bc.uid

Phil 3.4.14

8:00 – 4:00 SR

  • Heading in to work on the servers at 2:00, due to the snow. Javascript this morning.
  • Adding scrolling – done! Very nice. You can tell the scroller to go to a particular section. You can even name the section.
  • Turning slideshow into a module so that it can be faded up and down as a whole, and that I can put the Ken Burns effect in.
  • Reqonciler ignore selection doesn’t work – data grid problem?
  • Bring in second year updates query.

Dong Shin 03.04.2014

  • deployed database updates and new queries
    • Reqonciler has some datagrid problem – not updating hide_flags and weird mouse over highlighting, but no problems here back at the office…
    • second year processing missing, need to add this to post-processing – in reqonciler_processing.sql
      • UPDATE obligations_outlays o1,
        (SELECT month_12 FROM obligations_outlays oo2 WHERE type = ‘Outlay $ (Reported in FACTS)’ AND year_count = 1) AS o2
        SET o1.month_1 = o2.month_12 + o1.month_1,
        o1.month_2 = o2.month_12 + o1.month_2,
        o1.month_3 = o2.month_12 + o1.month_3,
        o1.month_4 = o2.month_12 + o1.month_4,
        o1.month_5 = o2.month_12 + o1.month_5,
        o1.month_6 = o2.month_12 + o1.month_6,
        o1.month_7 = o2.month_12 + o1.month_7,
        o1.month_8 = o2.month_12 + o1.month_8,
        o1.month_9 = o2.month_12 + o1.month_9,
        o1.month_10 = o2.month_12 + o1.month_10,
        o1.month_11 = o2.month_12 + o1.month_11,
        o1.month_12 = o2.month_12 + o1.month_12
        WHERE type = ‘Outlay $ (Reported in FACTS)’
        AND (year + year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
        AND year_count = 2
  • working on query for Chris/Lenny – show PM Actuals and Planned Outlay with differences in $ and % for current month
    • capability AS Capability, bcc.budget_center_name AS ‘Budget Center’, req_id AS ‘Req ID’, req_description AS ‘Req Title’,
      vrt.req_id_total AS ‘Req Funded Amount’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0)),0)))))))))))) AS ‘Outlay’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘PM Actuals’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘Diff $’,
      IF(MONTH(CURDATE())=10,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0))-SUM(IF(o.type=’Planned Outlay $:’,month_1,0))*100,1),’%’),IF(MONTH(CURDATE())=11,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0))/SUM(IF(o.type=’Planned Outlay $:’,month_2,0))*100,1),’%’),IF(MONTH(CURDATE())=12,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0))/SUM(IF(o.type=’Planned Outlay $:’,month_3,0))*100,1),’%’),IF(MONTH(CURDATE())=1,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0))/SUM(IF(o.type=’Planned Outlay $:’,month_4,0))*100,1),’%’),IF(MONTH(CURDATE())=2,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0))/SUM(IF(o.type=’Planned Outlay $:’,month_5,0))*100,1),’%’),IF(MONTH(CURDATE())=3,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0))/SUM(IF(o.type=’Planned Outlay $:’,month_6,0))*100,1),’%’),IF(MONTH(CURDATE())=4,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0))/SUM(IF(o.type=’Planned Outlay $:’,month_7,0))*100,1),’%’),IF(MONTH(CURDATE())=5,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0))/SUM(IF(o.type=’Planned Outlay $:’,month_8,0))*100,1),’%’),IF(MONTH(CURDATE())=6,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0))/SUM(IF(o.type=’Planned Outlay $:’,month_9,0))*100,1),’%’),IF(MONTH(CURDATE())=7,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0))/SUM(IF(o.type=’Planned Outlay $:’,month_10,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0))/SUM(IF(o.type=’Planned Outlay $:’,month_11,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0))/SUM(IF(o.type=’Planned Outlay $:’,month_12,0))*100,1),’%’),0)))))))))))) AS ‘Diff %’
      WHERE fiscal_year=2014
      GROUP BY capability, bc.sub_budget_center_number, req_id