Monthly Archives: December 2012

Phil 12.7.12

8:00 – 4:00 ESSO

  • Much running around yesterday, and I never got a chance to write things.
    • Backups
    • No meeting. Don had not set it up and was not there anyway.
    • Worked on getting VisibilityScripting alert scripts working, but the stored procedure that was needed didn’t store properly.
    • More ADF
    • Also, FP – in-process presentations
  • Backups
  • Got Dong’s stored procedure working
  • Ingested Lenny’s FY13 spreadsheet with minor tweaks. He’ll have everything ready for Tuesday
  • 11:00 – 1:00 meeting on Monday.
  • JDeveloper. Hooking up to data now, which is pretty cool. I have not seen how to duplicate a line like in Eclipse <CTRL-Shift-Arrow> I miss that. I am learning enough that I can stumble around and fix bugs that I cause.
  • Progress for the day:

Dong Shin 12.06.2012

  • forgot to include ‘DELIMITER $$’ for stored procedures this morning, will try again tomorrow
  • stored procedure to separate service project mgrs in the alerts table – need more tweaks to work with VSS
    • DELIMITER $$DROP PROCEDURE IF EXISTS explode_table $$
      CREATE PROCEDURE explode_table(bound VARCHAR(255))BEGIN

      DECLARE uid INT DEFAULT 0;
      DECLARE user TEXT;
      DECLARE occurance INT DEFAULT 0;
      DECLARE i INT DEFAULT 0;
      DECLARE splitted_user TEXT;
      DECLARE portfolio_admins TEXT;
      DECLARE portfolio_mgrs TEXT;
      DECLARE service_finance_pocs TEXT;
      DECLARE program TEXT;
      DECLARE MIPR TEXT;
      DECLARE alert TEXT;
      DECLARE status TEXT;
      DECLARE timestamp TIMESTAMP;
      DECLARE done INT DEFAULT 0;
      DECLARE cur1 CURSOR FOR SELECT alerts.uid, alerts.service_project_mgrs,
      alerts.portfolio_admins, alerts.portfolio_mgrs,
      alerts.service_finance_pocs, alerts.program,
      alerts.MIPR, alerts.alert, alerts.status, alerts.timestamp
      FROM alerts
      WHERE alerts.service_project_mgrs != ”;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      DROP TEMPORARY TABLE IF EXISTS table2;
      CREATE TEMPORARY TABLE table2(
      `uid` INT NOT NULL,
      `user` VARCHAR(255) NOT NULL,
      `portfolio_admins` VARCHAR(255),
      `portfolio_mgrs` VARCHAR(255),
      `service_finance_pocs` VARCHAR(255),
      `program` VARCHAR(255),
      `MIPR` VARCHAR(255),
      `alert` VARCHAR(255),
      `status` VARCHAR(255),
      `timestamp` TIMESTAMP
      ) ENGINE=Memory;

      OPEN cur1;
      read_loop: LOOP
      FETCH cur1 INTO uid, user, portfolio_admins, portfolio_mgrs, service_finance_pocs, program, MIPR, alert, status, timestamp;
      IF done THEN
      LEAVE read_loop;
      END IF;

      SET occurance = (SELECT LENGTH(user)
      – LENGTH(REPLACE(user, bound, ”))
      +1);
      SET i=1;
      WHILE i <= occurance DO
      SET splitted_user = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(user, bound, i),
      LENGTH(SUBSTRING_INDEX(user, bound, i – 1)) + 1), ‘,’, ”));

      INSERT INTO table2 VALUES (uid, splitted_user, portfolio_admins, portfolio_mgrs, service_finance_pocs,
      program, MIPR, alert, status, timestamp);
      SET i = i + 1;

      END WHILE;
      END LOOP;

      SELECT * FROM table2;
      CLOSE cur1;
      END; $$

Dong Shin 12.05.2012

  • working on FACTS Outlay updates
    • create a script – UpdateFACTSOutlayData.py
    • query to construct update query
      • SELECT MID(requisition_id, LENGTH(requisition_id) – 5, 6) as req_id,
        appropriation_year,
        SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) as month,
        expensed_date,
        SUM(expensed_amount),
        CONCAT(‘UPDATE  project_portfolio.obligations_outlays as o,  project_portfolio.projects AS p,project_portfolio.budget_centers AS c SET ‘,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 1, ‘month_4’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 2, ‘month_5’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 3, ‘month_6’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 4, ‘month_7’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 5, ‘month_8’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 6, ‘month_9’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 7, ‘month_1-‘,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 8, ‘month_11’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 9, ‘month_12’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 10, ‘month_1’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 11, ‘month_2’,
        IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 12, ‘month_3’, ‘unknown’))))))))))))
        , ‘=’, SUM(expensed_amount),
        ‘ WHERE o.type = ‘Outlay $ (Reported in FACTS)’ AND o.year + o.year_count = ‘, appropriation_year + 1, ‘ AND o.year_count = 1 ‘,
        ‘ AND p.uid = o.project_id AND p.uid = c.project_id AND c.uid = o.funding_id ‘,
        ‘ AND c.funding_transaction_number = ”, MID(requisition_id, LENGTH(requisition_id) – 5, 6), ”’) as sqlstring
        FROM `visibility_dev2`.`facts_outlay_update_test`
        WHERE LENGTH(requisition_id) > 6 AND
        (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1)  > 0 AND SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1)  < 13)
        GROUP BY month, requisition_id

Phil 12.5.12

8:30 – 2:30 ESSO

  • Backups
  • Deployed new versions of PPM and PA. The PA html file won’t load properly in IE, though it does in Firefox. Trued taking the PPM html file which does work fine, and replace all ocurrances of ProjPortfolioMgr with ProjectAssistant. Same problem. And also the same condition that it works in FF but not IE. Odd. Will show Dong tomorrow.
  • Discussions with Lenny about getting our data pulls either tomorrow or Friday. Was also directed to make sure that Lenny and I were present on any demo that Don gives.
  • Checked out what Dong is doing with his queries. We’ll also try to implement the Alert query so we can have a fun display for the presentation later this month.
  • Back to ADF and JDeveloper.

2:30 – 5:30 White Cliffs

Phil 12.4.12

8:30 – 2:20 ESSO

  • More ADF
  • Backups, and a non-demo. Will be rescheduled for later.
  • Got reamed for watching a YOuTube video. But did find that inspirational in the end.
  • Need to write up status report for Jim G.
  • Lenny found an odd bug where the text in one of the panels overwrites itself. Unable to print out. Will try tomorrow.

2:30 – 4:30 FP

  • Project review

Phil 12.3.12

8:00 – 10:00 ESSO

  • Backups.
  • Waited for Don a bit but no show. I guess we’ll figure out what kind of demo we’re going to do tomorrow when we get there.
  • Verified that Lenny had made a script for Carla. Yay, Lenny!

10:00 – 12:00 FP

  • Thermal interfaces and tangible interactions with multiple objects using Kinect. Cool stuff.

12:00 – 2:00 OH

  • FGM->White Cliff transition
  • Sent a note to Jim asking how we take our assets with us.

2:00 – 4:00 ESSO