Monthly Archives: November 2012

Phil 11.15.12

8:00 – 2:00 ESSO

  • Backups and meetings, oh my.
  • Working on getting Don and Lenny to focus on “report wishlists”. Meeting in two weeks to discuss?
  • Found a “duplicate project” bug(?) for Dong. He says that there are a set of criteria that Trish layed out that might be getting triggered. Need to look into that.
  • Need to clear out old password when changing password.

2:00 – 4:00 FP

  • Qualitative methods. Need to look into developing data dictionary-based mechanism for that which is able to point into AV stream.

Phil 11.14.12

8:00 – 4:00 ESSO

  • Deployed new versions of PPM, PA (new view only permissions), and Vis2 (bug fixes). Checked the fact that the bug fixes in fact do work by having Dan, my favorite bug-finding user try out this system.
  • Cleaned out old backups to free up disk space. All teh backups are still on the network drive, so this was redundant. Still keeping the most recent backups on the server in case everything goes down and I lose connectivity with the network drive, since that’s happened before.
  • Back to GWT. Rather than the book, I’m going to try something quicker. We’ll see if Google keeps up on its own technology well.
    • Made a GWT_HOME environment variable so that I can run webAppCreator.
    • That seems to be working well. Even ran everything from the command line.
    • Importing it into the IDE didn’t result in a project that was recognized as a GWT project. Had to re-create it in the IDE.
    • Stockwatcher (again)

Dong Shin 11.13.2012

  • continue working on Alerts
    • alerts that are not closed and older than 7 days query
      • SELECT *, DATEDIFF(NOW(), timestamp) AS days
        FROM ppm_alerts.alerts
        WHERE status <> ‘CLOSED’
        AND DATEDIFF(NOW(), timestamp) > 7
    • Alert_8_notResolvedIn7Days.py – not saving into alerts table, report only
    • saved table_queries – visibility_scripting.table_queries.sql

Dong Shin 11.12.2012

  • reorganizing Alerts to use new stored queries interface
    • Alert_1_30DaysOverdue.py query
      • CALL visibility_scripting.get_date_variables (@current_fy, @current_month, @previous_moth, @curruent_month_str);
        SET @previous_month_str := ‘month_’ + @previous_month;INSERT INTO ppm_alerts.alerts (uid, portfolio_admins, portfolio_mgrs, service_finance_pocs,
        service_project_mgrs, program, MIPR, alert, status)
        SELECT NULL,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,
        ’30 days overdue on Year:  + getYear() +  Month:  + getMonthStr(prevMonth) + ‘ as alert,
        ‘OPEN’ as status
        FROM projects p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        _projects_service_project_mgrs m,
        _projects_portfolio_admins ppa,
        _projects_portfolio_mgrs ppm,
        _projects_service_finance_pocs psf
        WHERE p.uid  = c.project_id
        AND a.budget_center_id = c.uid
        ANd a.year  =  @current_fy  – p.begin_year + 1
        AND o.project_id = p.uid
        AND o.funding_id = c.uid
        AND o.year + o.year_count – 1 =  @current_fy
        AND o.year_count = 1
        AND ISNULL(@previous_month_str)
        AND p.uid = m.project_id
        AND p.uid = ppa.project_id
        AND p.uid = ppm.project_id
        AND p.uid = psf.project_id
        GROUP BY o.uid;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,
        ’30 days overdue on Year:  + getYear() +  Month:  + getMonthStr(prevMonth) + ‘ as alert,
        ‘OPEN’ as status
        FROM projects p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        _projects_service_project_mgrs m,
        _projects_portfolio_admins ppa,
        _projects_portfolio_mgrs ppm,
        _projects_service_finance_pocs psf
        WHERE p.uid  = c.project_id
        AND a.budget_center_id = c.uid
        ANd a.year  =  @current_fy  – p.begin_year + 1
        AND o.project_id = p.uid
        AND o.funding_id = c.uid
        AND o.year + o.year_count – 1 =  @current_fy
        AND o.year_count = 1
        AND ISNULL(@previous_month_str)
        AND p.uid = m.project_id
        AND p.uid = ppa.project_id
        AND p.uid = ppm.project_id
        AND p.uid = psf.project_id
        GROUP BY o.uid;
    • Alert_2_planned_values.py
      • CALL visibility_scripting.get_date_variables (@current_fy, @current_month, @previous_moth, @curruent_month_str);
        SET @previous_month_str := ‘month_’ + @previous_month;
        SET @obligation_month_str := ‘g.obligation_month_’ + @previous_month;
        SET @outlay_month_str := ‘g.outlay_month_’ + @previous_month;INSERT INTO ppm_alerts.alerts (uid, portfolio_admins, portfolio_mgrs, service_finance_pocs,
        service_project_mgrs, program, MIPR, alert, status)
        SELECT NULL,
        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,
        ‘Planned values are out of acceptable range on Year:  + getYear() +  Month:  + getMonthStr(getPrevMonthNum()) + ‘ as alert,
        ‘OPEN’ as status
        FROM projects p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g,
        _projects_service_project_mgrs m,
        _projects_portfolio_admins ppa,
        _projects_portfolio_mgrs ppm,
        _projects_service_finance_pocs psf
        WHERE p.uid  = c.project_id
        AND a.budget_center_id = c.uid
        ANd a.year = @current_fy – p.begin_year + 1
        AND o.project_id = p.uid
        AND o.funding_id = c.uid
        AND o.year + o.year_count – 1 = @current_fy
        AND o.year_count = 1
        AND g.appropriation = c.appropriation
        AND g.year = o.year_count
        AND (ISNULL(@previous_month_str) OR
        (@previous_month_str > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 1.05
        AND @previous_month_str <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 0.95))
        AND o.type = ‘Planned Obligated $:’
        AND p.uid = m.project_id
        AND p.uid = ppa.project_id
        AND p.uid = ppm.project_id
        AND p.uid = psf.project_id
        GROUP BY o.uidSELECT
        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,
        ‘Planned values are out of acceptable range on Year:  + getYear() +  Month:  + getMonthStr(getPrevMonthNum()) + ‘ as alert,
        ‘OPEN’ as status
        FROM projects p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g,
        _projects_service_project_mgrs m,
        _projects_portfolio_admins ppa,
        _projects_portfolio_mgrs ppm,
        _projects_service_finance_pocs psf
        WHERE p.uid  = c.project_id
        AND a.budget_center_id = c.uid
        ANd a.year = @current_fy – p.begin_year + 1
        AND o.project_id = p.uid
        AND o.funding_id = c.uid
        AND o.year + o.year_count – 1 = @current_fy
        AND o.year_count = 1
        AND g.appropriation = c.appropriation
        AND g.year = o.year_count
        AND (ISNULL(@previous_month_str) OR
        (@previous_month_str > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 1.05
        AND @previous_month_str <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 0.95))
        AND o.type = ‘Planned Obligated $:’
        AND p.uid = m.project_id
        AND p.uid = ppa.project_id
        AND p.uid = ppm.project_id
        AND p.uid = psf.project_id
        GROUP BY o.uid
    • Alert_3_FMPs.py
      • CALL visibility_scripting.get_date_variables (@current_fy, @current_month, @previous_moth, @current_month_str);
        SET @previous_month_str := ‘month_’ + @previous_month;
        SET @obligation_month_str := ‘g.obligation_month_’ + @previous_month;
        SET @outlay_month_str := ‘g.outlay_month_’ + @previous_month;INSERT INTO ppm_alerts.alerts (uid, portfolio_admins, portfolio_mgrs, service_finance_pocs,
        service_project_mgrs, program, MIPR, alert, status)
        SELECT NULL,
        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,
        CONCAT(‘Financial Mitigation Strategy has not changed from previous month for ‘, @current_fy, ‘ ‘, @current_month_str) as alerts,
        ‘OPEN’ as status
        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 fmp2.month = @current_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 = @current_fy
        AND (ISNULL(@previous_month_str) OR (@previous_month_str > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 1.05
        AND @previous_month_str <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 0.95)))
        GROUP BY o.uid;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,
        CONCAT(‘Financial Mitigation Strategy has not changed from previous month for ‘, @current_fy, ‘ ‘, @current_month_str) as alerts,
        ‘OPEN’ as status
        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 fmp2.month = @current_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 = @current_fy
        AND (ISNULL(@previous_month_str) OR (@previous_month_str > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 1.05
        AND @previous_month_str <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,@obligation_month_str, @outlay_month_str) / 100, 2) * 0.95)))
        GROUP BY o.uid;
    • Alert_7_UsesNotLoggedIn30Day.py
      • INSERT INTO ppm_alerts.alerts (uid, portfolio_admins, portfolio_mgrs, service_finance_pocs,
        service_project_mgrs, program, MIPR, alert, status)
        SELECT NULL, NULL as porfolio_admins, NULL as portfolio_mgrs, NULL as service_finance_pocs, NULL as service_project_managers,
        ” as program, ” as MIPR, CONCAT(login, ‘ has not been logged in for more than 30 days ‘) as alert, ‘OPEN’ as status
        FROM `users` WHERE DATEDIFF(now(), last_login) > 30;SELECT NULL as porfolio_admins, NULL as portfolio_mgrs, NULL as service_finance_pocs, NULL as service_project_managers,
        ” as program, ” as MIPR, CONCAT(login, ‘ has not been logged in for more than 30 days ‘) as alert, ‘OPEN’ as status
        FROM `users` WHERE DATEDIFF(now(), last_login) > 30;

Phil 11.8,9.12

8:00 – 4:00

  • Meeting with Lenny and Don about VisTool. After a lot of going around, they want more (as yet unspecified) scripts. After having now interacted with Ray (An actual user!), I want to have fewer Financial Mitigation pages popped up at people who have no money to spend yet.
  • Was able to replicate the freeze problem. If the user has not set up any “desktop” styles, the objects that define the panel borders are null, which freaks the color calculations out. I added traps for that, and also experimented with what the best change in the ‘per wedge’ hue rotation in a pie chart is. It looks like it’s 23 degrees, and I took out the code that varies it as a function of the hue difference between the panel bg and border.
  • Committed all changes and burned the new vis2, PPM and PA to disk.

Dong Shin 11.09.2012

  • added Change Log to VSS4
  • working on skipping FMPs with $0 committed
    • added configuration check box to Role Management in PPM
    • database update – DBUpdateSQLs11092012.sql
    • added a flag to check for commit amount and use it for FMP requirement
    • built release version of PPM and PA

Dong Shin 11.08.2012

  • deployed new PPM and PA with demo/guest capability – everything looks good
  • discussed about the projects with no budgets allocated
    • when there is no committed amount available in funding request, FMPs can be skipped?
  • reorganizing alert scripts and storing queries in the database

Phil 11.7.12

7:30 – 3:30 ESSO

  • Backups
  • Meeting with Ray(?) to go over how to use Project Assistant. Some issues, particularly when there is no allocated budget. We really need to essentially replicate the FRP until the funds are allocated.
  • Need to write up my status report for Jim G. Done
  • Still looking for the freeze problem. Can’t replicate today regardless of what machine or what browser I try it on.
  • Finished testing of using Google Drive as an ‘interim backup’. Works well enough that I’m now trying it with a copy of my actual workspace
  • Burned disk of PPM and PA, along with status report.

Dong Shin 11.07.2012

  • working on guest/demo account of PPM and PA
    • created demo role with permissions enabled – DBUpdateSQLs11072012.sql
      • INSERT INTO `roles` (`type`, `create_project`, `copy_project`, `modify_project`, `duplicate_project`, `enter_monthly_status_data`, `financial_status_data`, `create_funding_request`, `modify_funding_request`, `manage_contracts`, `manage_appropriations`, `monthly_status_report`, `create_new_user`, `user_management`, `activate_new_users`, `local_logs`, `server_logs`, `query_logs`, `database_management`, `role_management`, `view_only_associated_data`) VALUES
        (‘demo’, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0)
    • PA modifications – done!
      • return full list of projects and alerts for demo
      • disabled editing of Alerts and comments
      • disabled editing of financial data
      • added alert for demo account login
    • PPM modifications – done!
      • added alert for demo account login in PPM
      • buttons disabled in CreateProjectPanel
      • disabled save in FinancialStatusPanel
      • disabled editing and auto entry on FinancialDataDataGrid
      • disabled save, update, remove in Contracts
      • disabled save on FinancialMitigationPlanWindow
      • disabled save, remove on InvoiceFormWindow
      • disabled save, delete Funding Requests
      • disabled add, changed, remove Direct Cites
      • disabled unlock project, delete project in ProjectMgmtPanel
      • disabled save in UserManagement, Activate New Users
      • disabled save in UserRolesManagementPanel
      • disabled save in DBMgmtPanel
  • due for PR, need to start on SF-86

Phil 11.5.12 – 11.6.12

11.5.12: 7:30 – 9:30, 1:00 – 4:00 ESSO

  • Backups
  • Deployed release versions of everything.
  • Meeting with Morpheus folks. Demoed everything, and they are interested. Had an odd experience, where the chart view in the data visualizer froze. Upon reloading, everything was fine.

11.5.12 10:00 – 1:00 FP

  • 810, then video production presentation

11.6.12: 730 – 2:00 ESSO

  • Backups
  • Wrote up meeting notes for Dave W.
  • Jim G. wants status for October
  • Set up a bunch of users for Data Visualization. When showing one how things work, I ran into the freezing problem again. I reset the deployed code to be the debug build, and can’t get it to happen on my box, but it happens on his. I need to test further. Not even sure how to debug this one yet.
  • Experimenting with using Google Drive to hold my Eclipse development directory. If it works, then there is always a snapshot of the current codebase. THis doesn’t replace SVN, which hold the ‘working’ codebase. Instead, this is the version that each user is working on, before it’s shared.

2:00 – 4:00 FP

  • 805, and a bit of coding to determine Levenshtein distance for strings.

Dong Shin 11.06.2012

  • 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
  • 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
    • 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
    • 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

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
    • 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;
  • 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