- working on guest account/roles for PA and PPM
- saved alerts 4, 5, 6 to table_queries – this saves the data to alerts table as well as xml data generation
- created scripts for alerts – Alert_4_notObligatedWithin30days.py, Alert_5_notAcceptedWithin30days.py, Alert_6_noOutlaysWithin60days.py
- query find all users on projects (full outer join?)
- SELECT
projects.uid,
GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
GROUP_CONCAT(distinct(m.login)) as service_project_managers,
GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
projects.project_number AS program,
c.center_number as MIPR
FROM projects
LEFT JOIN _projects_portfolio_admins AS ppa ON projects.uid = ppa.project_id
LEFT JOIN _projects_portfolio_mgrs AS ppm ON projects.uid = ppm.project_id
LEFT JOIN _projects_service_project_mgrs AS m ON projects.uid = m.project_id
LEFT JOIN _projects_service_finance_pocs AS psf ON projects.uid = psf.project_id
LEFT JOIN budget_centers AS c on projects.uid = c.project_id
GROUP BY projects.uid
- SELECT
- working on Alerts
- query to find #4 MIPRs, EAs, Reqs, etc. are not obligated within 30 days after the acceptance date
- SELECT
projects.uid,
GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
GROUP_CONCAT(distinct(m.login)) as service_project_managers,
GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
projects.project_number AS program,
c.center_number as MIPR,
‘Not obligated within 30 days after the acceptance date’,
‘OPEN’ AS status
FROM projects
LEFT JOIN _projects_portfolio_admins AS ppa ON projects.uid = ppa.project_id
LEFT JOIN _projects_portfolio_mgrs AS ppm ON projects.uid = ppm.project_id
LEFT JOIN _projects_service_project_mgrs AS m ON projects.uid = m.project_id
LEFT JOIN _projects_service_finance_pocs AS psf ON projects.uid = psf.project_id
LEFT JOIN budget_centers AS c on projects.uid = c.project_id
WHERE projects.uid IN (
SELECT project_id FROM funding_requests f, projects p
WHERE ((ISNULL(obligation_date) AND DATEDIFF(CURDATE(), acceptance_date) > 30)
OR ISNULL(DATEDIFF(obligation_date, acceptance_date))
OR DATEDIFF(obligation_date, acceptance_date) > 30)
AND p.uid = f.project_id
)
GROUP BY projects.uid
- SELECT
- query to find#5 MIPRs, EAs, Reqs, etc. are not accepted within 30 days of the certification date
- SELECT
GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
GROUP_CONCAT(distinct(m.login)) as service_project_managers,
GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
projects.project_number AS program,
c.center_number as MIPR,
‘Not obligated within 30 days after the acceptance date’,
‘OPEN’ AS status
FROM projects
LEFT JOIN _projects_portfolio_admins AS ppa ON projects.uid = ppa.project_id
LEFT JOIN _projects_portfolio_mgrs AS ppm ON projects.uid = ppm.project_id
LEFT JOIN _projects_service_project_mgrs AS m ON projects.uid = m.project_id
LEFT JOIN _projects_service_finance_pocs AS psf ON projects.uid = psf.project_id
LEFT JOIN budget_centers AS c on projects.uid = c.project_id
WHERE projects.uid IN
(SELECT project_id
FROM funding_requests f, projects p
WHERE ((ISNULL(acceptance_date) AND DATEDIFF(CURDATE(), certified_date) > 30)
OR ISNULL(DATEDIFF(acceptance_date, certified_date))
OR DATEDIFF(acceptance_date, certified_date) > 30)
AND p.uid = f.project_id)
GROUP BY projects.uid
- SELECT
- query to find # 6 – MIPRs are accepted as Reimbursable, but no outlays occur within 60 days
- SELECT
GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
GROUP_CONCAT(distinct(m.login)) as service_project_managers,
GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
projects.project_number AS program,
c.center_number as MIPR,
‘Not obligated within 30 days after the acceptance date’,
‘OPEN’ AS status
FROM projects
LEFT JOIN _projects_portfolio_admins AS ppa ON projects.uid = ppa.project_id
LEFT JOIN _projects_portfolio_mgrs AS ppm ON projects.uid = ppm.project_id
LEFT JOIN _projects_service_project_mgrs AS m ON projects.uid = m.project_id
LEFT JOIN _projects_service_finance_pocs AS psf ON projects.uid = psf.project_id
LEFT JOIN budget_centers AS c on projects.uid = c.project_id
WHERE projects.uid IN
(SELECT project_id
FROM funding_requests f, projects p
WHERE (NOT ISNULL(reimbursable_amount)
AND ISNULL(outlay_amount)
AND DATEDIFF(CURDATE(), acceptance_date) > 60)
AND p.uid = f.project_id)
GROUP BY projects.uid
- SELECT
- query to find #4 MIPRs, EAs, Reqs, etc. are not obligated within 30 days after the acceptance date
Phil 11.2.12
8:00 – 11:00, 12:00 – 4:00 ESSO
- Deployed yet another version of vis2. I think it’s free of any ‘stupid’ errors, at any rate.
- Monday 2:00 Morpheus meeting
- Wednesday 9:30 VisTool walkthrough
- Announded the URN victory
- Compiled and burned release builds of other VISIBILITY apps.
11:00 – 12:00 FP
- Meeting with Bill Shewbridge. Looks like studio time on Nov 20 or Dec 4. Preference for Nov 30.
Dong Shin 11.02.2012
- added visibility_scripting to the list of databases for VSS
- working on SQL queries
- get_date_variables example
- CALL `get_date_variables` (@p0 , @p1 , @p2 , @p3);SELECT @p0 AS `current_month` , @p1 AS `current_year` , @p2 AS `previous_month` , @p3 AS `current_month_str` ;
- created a stored procedure to return date variables
- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_date_variables`(OUT `current_month` INT, OUT `current_year` INT, OUT `previous_month` INT, OUT `current_month_str` VARCHAR(25))
BEGIN
SET current_year = IF(MONTH(CURDATE()) > 9, YEAR(CURDATE())+1, YEAR(CURDATE()));
SET current_month := IF(MONTH(CURDATE()) > 9, MONTH(CURDATE())-9, MONTH(CURDATE())+3);
SET previous_month := IF(MONTH(CURDATE()) > 9,
IF(MONTH(CURDATE())=10, 1, MONTH(CURDATE())-10), MONTH(CURDATE())+2);
SET current_month_str := IF(current_month = 1, ‘Oct’,
IF(current_month = 2, ‘Nov’,
IF(current_month = 3, ‘Dec’,
IF(current_month = 4, ‘Jan’,
IF(current_month = 5, ‘Feb’,
IF(current_month = 6, ‘Mar’,
IF(current_month = 7, ‘Apr’,
IF(current_month = 8, ‘May’,
IF(current_month = 9, ‘Jun’,
IF(current_month = 10, ‘Jul’,
IF(current_month = 11, ‘Aug’,
IF(current_month = 12, ‘Sep’, ‘ERR’))))))))))));
END
- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_date_variables`(OUT `current_month` INT, OUT `current_year` INT, OUT `previous_month` INT, OUT `current_month_str` VARCHAR(25))
- queries to get current year, month, previous month, and month string
- SET @current_year = YEAR(CURDATE()), @current_month = MONTH(CURDATE());
SET @current_fy_month := IF(@current_month > 9, @current_month-9, @current_month+3);
SET @previous_fy_month := IF(@current_month > 10, @current_month-9, @current_month+2);
SET @current_month_str := IF(@current_month = 1, ‘Oct’,
IF(@current_fy_month = 2, ‘Nov’,
IF(@current_fy_month = 3, ‘Dec’,
IF(@current_fy_month = 4, ‘Jan’,
IF(@current_fy_month = 5, ‘Feb’,
IF(@current_fy_month = 6, ‘Mar’,
IF(@current_fy_month = 7, ‘Apr’,
IF(@current_fy_month = 8, ‘May’,
IF(@current_fy_month = 9, ‘Jun’,
IF(@current_fy_month = 10, ‘Jul’,
IF(@current_fy_month = 11, ‘Aug’,
IF(@current_fy_month = 12, ‘Sep’, ‘ERR’))))))))))));
SELECT @current_year, @current_month, @current_fy_month, @previous_fy_month, @current_month_str;
- SET @current_year = YEAR(CURDATE()), @current_month = MONTH(CURDATE());
- get_date_variables example
- FGMDEV backup
- ftp keeps timing out on the large file – svn dump…
- created cron job to back up at 1AM
Phil 11.1.12
8:00 – 2:00 ESSO
- We have our VizTool URN!
- Looks like next week for Morpheus
- Interview today?
- Discovered a small bug today when deploying the new version. Although data tips for other items are able to use HTML, tips for pie charts don’t. Pulling out the offending code .
- Talked to Dong about passing variables into SQL, and about creating some release builds to evaluate.
- Burning a new disk
2:00 – 4:00 FP
- More papers and HCC foundations
Dong Shin 11.01.2012
- updated AS3Dictionary.xml to include common SQL’s
- Use Variables CheckBox added to QueryEditor and modified table_queries to have variables have flag
- fgmdev.com backup script is gone…. working on creating another
- zip up all files under htdocs – zip -ru htdocs.zip /opt/lampp/htdocs/blogs
- zipping up nexus (sonatype) take too long, may have to skip
- backing up to fgmdev_backup directory
Dong Shin 10.31.2012
- wrapping up SQLEditor for VSS
- added getSavedSQLs to retrieve save queries
- added SQL error window to FGMFlexUtils45
- double click on sqlResultsDG shows selected row details
- fgmdev.com was down….
Phil 10.31.12
8:00 – 4:00
- Backups and discussions with folks on site. Working on getting a meeting set up with the Morpheus folks.
- fgmdev.com was down. Dong and I spent most of the morning trying to get it up. That you are reading this means success!
- Working on adding color code. Incorporated and working nicely!
- Checking code in
- Burning a disk
Phil 10.30.12
10:00 – 12:00 ESSO
- Still wet, but no wind to speak of. Cold – in the 30’s. We apparently got 80 mph gusts and I’d believe it.
- Updated the WP engine for this blog.
- Looking at some papers about User Interface/ Experience to see how the Alert Manager App should be built.
12:00 – 2:00 FP
- Reading “Facilitators and Barriers to Adopting Robotic-Assisted Surgery: Contextualizing the Unified Theory of Acceptance and Use of Technology”. I want to find a rule for User Interface adoption
“
Dong Shi 10.29.2012
- working at home
- continue working on SQLEditor for VSS
- added method to run SQL string to server – SQLResult runSQLStrings(String databaseName, String sqlList)
- added SQLResult.java to javaUtils – pom.xml is set to generate version 1.1, others are not using it?????
- changed scriptingEngine and VisibilityScriptingServer to use 1.1
- added methods to save and get list of SQLs
Phil 10.29.12
8:00 – 4:00 ESSO
- So begins a very wet day.
- Working on figuring out why coloring chart lines is so different from all the other charts. Going to try building a toy project with just one line chart in it and (maybe?) an item renderer.
- Well that turned out to be easier than I thought.
private function init():void{
var hsb:HsbObject = new HsbObject(0x0000AA);
var lsa:Array = new Array();
lsa.push(createLineSeries("Profit", "Profit", hsb));
hsb.rotateHue(40);
lsa.push(createLineSeries("Expenses", "Expenses", hsb));
hsb.rotateHue(40);
lsa.push(createLineSeries("Amount", "Amount", hsb));
_linechart.series = lsa;
}
private function createLineSeries(yf:String, dn:String, hsb:HsbObject):LineSeries{
var scs:SolidColorStroke = new SolidColorStroke(hsb.getHexColor());
scs.weight = 3;
var ls:LineSeries = new LineSeries();
ls.yField = yf;
ls.displayName = dn;
ls.setStyle("lineStroke", scs);
return ls;
}
The project files are here. There are two projects. The EdgeUtils project contains the HsbObject class.
Dong Shin 10.26.2012
- added dbStoredQuery(String queryName) to ScriptFacades class
- can run set of queries and return the last result in DbTable format
- working on dbStoredProc – runs stored procedure in the database via VSS
- working on QueryEditor in VSS
Phil 10.26.12
8:00 – 4:00 ESSO
- Put in the paperwork for an URN
- Pinged the Morpheus folks to see how we can integrate
- Working on tracking down bugs and coloring lines
- Discussed how to add SQL stored(?) procedures to VSS with Dong.
- Need an editor to create and name scripts
- Need some kind of macro substitution
- Some of this is available in the dbObjects in JavaUtils and FGMUtils, using the classes that I wrote to create databases that map to Flex objects.
- Fixed the bug that was crashing the column rendering in WidgetBase by adding a try/catch block and resetting the array of visibile columns. Not pretty, but it works.
- Still trying to figure out how to change the color of a line. This seems to be the way: http://help.adobe.com/en_US/flex/using/WS2db454920e96a9e51e63e3d11c0bf69084-7c52.html
Phil 10.25.12
8:00 – 2:00 ESSO
- Got the following error:
- RangeError: Index ‘2’ specified is out of bounds.
[C:autobuild3.xframeworksprojectsframeworksrcmxcollectionsListCollectionView.as:529]
at mx.collections::ListCollectionView/addAll()[C:autobuild3.xframeworksprojectsframeworksrcmxcollectionsListCollectionView.as:512]
at com.edgeti::WidgetBase/listColumnsHandler()[C:PhilProject WorkspacesHelios_3.6 FB4.5_32_bit_Aug_2012GenericQueryWidgetsrcmainflexcomedgetiWidgetBase.as:236] - Backups
- Status meeting
- Dave wants a contingincy plan if the server goes down. Need to talk to Vernon about this.
- Looked at integrating with Morphious.
Dong Shin 10.25.2012
- status meeting
- working on running stored queries in ScriptFacades class
- continue working on FMP alerts
- created AlertFMPs.py and AlertFMPsSaveToTable.py scripts
- query to find offending FMP’s in Alert format
- SELECT
GROUP_CONCAT(distinct(ppa.login)) as porfolio_admins,
GROUP_CONCAT(distinct(ppm.login)) as portfolio_mgrs,
GROUP_CONCAT(distinct(psf.login)) as service_finance_pocs,
GROUP_CONCAT(distinct(m.login)) as service_project_managers,
p.project_number as program,
c.center_number as MIPR
FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2,
projects p, obligations_outlays o, budget_centers c,
_projects_service_project_mgrs m,
_projects_portfolio_admins ppa,
_projects_portfolio_mgrs ppm,
_projects_service_finance_pocs psf
WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
AND fmp1.explanation1 = fmp2.explanation1
AND fmp1.explanation2 = fmp2.explanation2
AND fmp1.explanation3 = fmp2.explanation3
AND fmp1.explanation4 = fmp2.explanation4
AND fmp1.explanation5 = fmp2.explanation5
AND fmp1.month + 1 = fmp2.month
AND p.uid = o.project_id
AND c.uid = o.funding_id
AND p.uid = m.project_id
AND p.uid = ppa.project_id
AND p.uid = ppm.project_id
AND p.uid = psf.project_id
AND fmp1.obligation_outlay_uid IN
(SELECT o.uid
FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
WHERE
p.uid = c.project_id
AND a.budget_center_id = c.uid
AND o.project_id = p.uid
AND o.funding_id = c.uid
AND g.appropriation = c.appropriation
AND g.year = o.year_count
AND o.year + o.year_count = 2014
AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount * IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
AND o.month_1 < TRUNCATE(a.amount * IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95)))
GROUP BY o.uid
- SELECT
Dong Shin 10.24.2012
- query to find offending (?) FMPs for underperforming obligations/outlays
- SELECT *
FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2
WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
AND fmp1.explanation1 = fmp2.explanation1
AND fmp1.explanation2 = fmp2.explanation2
AND fmp1.explanation3 = fmp2.explanation3
AND fmp1.explanation4 = fmp2.explanation4
AND fmp1.explanation5 = fmp2.explanation5
AND fmp1.month + 1 = fmp2.month
AND fmp1.obligation_outlay_uid IN
(SELECT o.uid
FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
WHERE
p.uid = c.project_id
AND a.budget_center_id = c.uid
AND o.project_id = p.uid
AND o.funding_id = c.uid
AND g.appropriation = c.appropriation
AND g.year = o.year_count
AND o.year + o.year_count = 2014
AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount * IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
AND o.month_1 < TRUNCATE(a.amount * IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95)))
- SELECT *

You must be logged in to post a comment.