- 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))BEGINDECLARE 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; $$
- DELIMITER $$DROP PROCEDURE IF EXISTS explode_table $$
Author Archives: pgfeldman
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
- SELECT MID(requisition_id, LENGTH(requisition_id) – 5, 6) as req_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
Dong Shin 12.04.2012
- demo cancelled
- created database user to limit access – visibility, DBUpdateSQLs12042012.sql
- adjusted htmls and PA swf to resize in smaller displays
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
- Back to this ADF tutorial.
Phil 11.29.12
8:00 – 4:00 ESSO
- Backups
- Wrote up the process for getting admin accounts and sent them to Chris B. And Dan W.
- Did a short training with Carla. Lenny will (hopefully) write a script for her to use on Monday
- Meeting with Dave(?) and Kristi from GAO on integrating Vistool/Data into their process
- Working with Dong on Dealing with how PPM and PA should deal with small monitors.
- Now that JSF is done, starting on ADF (Oracle® Fusion Middleware Web User Interface Developer’s Guide for Oracle Application Development Framework 11g Release 2 (11.1.2.0.0))
- Killed JDeveloper again. That’s annoying, and disturbingly common.
Dong Shin 11.29.2012
- weekly cum bi-weekly status meeting is now weekly again.
- added scroll bars to PA
- created visibility_scripting.stored_procedures.sql – missing from today’s deployment
- burned a cd for tomorrow
Phil 11.28.12
8:00 – 4:00 ESSO
- Backups
- Many meetings, plus setup for tomorrows bi weekley
- More JDeveloper tutorials
- The server seems to get completely messed up when my box sleeps (is left alone?)
- Binding a JSF Page to a Managed Bean – Done. Very cool IDE.
Dong Shin 11.28.2012
- continue working on update FACTS Outlays script
- adding getColumn method to ScriptFacades – done
- burned VisibilityScripting on a CD for deployment tomorrow
Dong Shin 11.27.2012
- COGNOS Data to update PPM data
- Match COGNOS Requisition ID to MIPR (Funding Transaction Number)
- COGNOS Expensed Amount summed by month
- Overwrite existing PPM data
- Update continuously (daily?)
- If there is no value for Expensed Amount COGNOS column, then skip
- If there is a negative amount – i.e. ($10,123), subtract in the sum
- Ingest as “FACTS_Outlay_update”
- created dummy FACTS_Outlay_update spreadsheet and ingested it – FACTS_Outlay_update.xls
- working on queries to update PPM data – update query can be constructed in python script using the query below
- SELECT requisition_id,
appropriation_year,
SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) as month,
expensed_date,
SUM(expensed_amount),
CONCAT(‘UPDATE project_portfolio.obligations_outlays o 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.year + o.year_count = ” + appropriation_year + ” AND o.year_count = 1 AND o.type = ‘Outlay $ (Reported in FACTS'” ) as sqlstring
FROM `facts_outlay_update`
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
- SELECT requisition_id,
- When adding Budget Center to a Project, Budget Center columns selection should default to previous entry except for
- Funding Transaction Name
- Funding Transaction Number
- Funding Transaction Type
- Org
- Location
Phil 11.27.12
8:00 – 2:00 ESSO
- Discussion with Lenny and Dong about how to read in cognos data and use it to populate the FACTS fields in PPM and PA.
- Backups
- Working on the JDeveloper JSF tutorials – managed to crash JDeveloper.
- Talked to Dong about next dev environments. We discussed Adobe and Netbeans in addition to what we’re using now. My thought is that if we can make a table and chart that allow us to visualize data sitting in a DB here and on site, we have a viable option. If it’s open source and supported by a player unlikely to go out of business any time soon, then so much the better.
2:00 – 4:00 FP
- Qualitative Methods.
Phil 11.26.12
8:30 – 4:30 ESSO
- Backups
- More interaction with Don abo0ut what kind of visualizations he wants. Printed out a description of the putative table that we need to produce. Dong and I will try to code it tomorrow in site.
- Put together FlexiCharts of ACC, since that appears to have vanished.
- Got started back on JDeveloper and ADF.
Dong Shin 11.26.2012
- got Visibility Login working using Oracle ADF!
Phil 11.21.12
8:00 – 4:00 ESSO
- Meetings with everyone, Dan, Lenny, Chris.
- Backups
- 2:00pm meeting with Kristen
- Meeting went well. I basically walked through how to use the Data Visualization and how PPM and PA generated data and where it was stored. We then talked about generating more graphics for her and how scripting could produce the tables she (implicitly) needs for the charts she wants.

You must be logged in to post a comment.