Category Archives: Dong Shin

Dong Shin 06.18.2012

  • added copyProject to PPM Server that copies complete set of project data
  • added Duplicate Project to PPM client to support the copyProject functionality
    • tested and checked in
    • ready for deployment
    • database updates – DBUpdateSQLs06152012.sql

Dong Shin 06.15.2012

  • Copy Project capability wouldn’t quite do the jobs users wanted to do because it doesn’t copy of the monthly financial data. We proposed that another functionality to copy/duplicate entire project data.
  • working on duplicating project data
  • changing database structure and logging in to DBUpdateSQLs06152012.sql
  • copy a project
    • INSERT INTO projects (
      project_number, title, exec_summary, description, location, begin_year, end_year,
      total_budget, proj_mgr_login, proj_admin_login, portfolio_mgr_login, portfolio_admin_login,
      priority, services, TRL_start, TRL_current, TRL_end, TCL_start, TCL_current, TCL_end,
      TP_start, TP_current, TP_end)
      SELECT project_number, title, exec_summary, description, location, begin_year, end_year,
      total_budget, proj_mgr_login, proj_admin_login, portfolio_mgr_login, portfolio_admin_login,
      priority, services, TRL_start, TRL_current, TRL_end, TCL_start, TCL_current, TCL_end,
      TP_start, TP_current, TP_end
      FROM projects
      WHERE uid=0

Dong Shin 06.14.2012

  • weekly status meeting @ Fort
  • trouble-shooting
    • cascade problem – caused by new fields added (funding_type, org, location)
    • Comments not showing – deleted user
  • bug fixes
    • Copy Projects
    • View Comments to show all the comments

Dong Shin 06.13.2012

  • issues at the site
    • using sub projects to limit what users can see
    • copy project generates error – some cascading error, but can’t duplicate it on mine.
    • users having problems viewing projects assigned to – fixed
  • getting error when logged out and try to log back in… and the login screen locks up
    • Error logggin in!
      [FaultEvent fault=[RPC Fault faultString=”[MessagingError message=’Destination ‘PPMUserService’ either does not exist or the destination has no channels defined (and the application does not define any default channels.)’]” faultCode=”InvokeFailed” faultDetail=”Couldn’t establish a connection to ‘PPMUserService'”] messageId=”C6D2C3B1-E087-9059-0A89-E669D5CDC239″ type=”fault” bubbles=false cancelable=true eventPhase=2]
  • fixed projects not showing for users in PPM
    • SELECT projects.*, projects.uid as ID, projects.project_number as ‘Project ID’, projects.title as ‘Project Name’, begin_year as ‘Start FY’, end_year as ‘End FY’, begin_year as startYear, IF(ISNULL(lock_id), if (total_budget = 0, ‘INCOMPLETE’, ‘EDITABLE’), IF (lock_id=’dongshin’, if (total_budget = 0, ‘INCOMPLETE’, ‘EDITABLE’), ‘LOCKED’)) as Status, (end_year – begin_year) as duration, CONCAT(o.first_name, ‘ ‘, o.last_name) AS ‘Project Mgr’, CONCAT(a.first_name, ‘ ‘, a.last_name) AS ‘Portfolio Admin’,  CONCAT(o.first_name, ‘ ‘, o.last_name) AS projMgrName, CONCAT(b.first_name, ‘ ‘, b.last_name) AS ‘Project Admin’, CONCAT(c.first_name, ‘ ‘, c.last_name) AS ‘Portfolio Mgr’, services, o.unclass_phone as projMgrPhone, o.unclass_phone_ext as projMgrPhoneExt, o.JWICS_email as projMgrJWICSemail, o.SIPR_email as projSIPRemail, o.unclass_email as projUnClassEmail, CONCAT(c.first_name, ‘ ‘, c.last_name) AS projSupervisorName,  c.unclass_phone as projSupervisorPhone, c.unclass_phone_ext as projSupervisorPhoneExt, c.JWICS_email as projSupervisorJWICSemail, c.SIPR_email as projSupervisorSIPRemail, c.unclass_email as projSupervisorUnClassEmail, projects.total_budget as ‘Total Budget’, sub.project_id as parent_project_id, sub.sub_project_id FROM projects
      LEFT JOIN _project_sub_projects as sub ON uid = sub_project_id
      LEFT JOIN users AS o ON projects.proj_mgr_login = o.login
      LEFT JOIN users AS a ON portfolio_mgr_login = a.login
      LEFT JOIN users AS b ON proj_admin_login = b.login
      LEFT JOIN users AS c ON portfolio_admin_login = c.login
      WHERE (projects.proj_mgr_login = ‘dongshin’ OR projects.portfolio_mgr_login = ‘dongshin’ OR projects.proj_admin_login = ‘dongshin’ OR projects.portfolio_admin_login = ‘dongshin’)
      OR (projects.uid in (SELECT project_id FROM _projects_portfolio_mgrs WHERE login = ‘dongshin’))
      OR (projects.uid in (SELECT project_id FROM _projects_portfolio_admins WHERE login = ‘dongshin’))
      OR (projects.uid in (SELECT project_id FROM _projects_service_finance_pocs WHERE login = ‘dongshin’))
      OR (projects.uid in (SELECT project_id FROM _projects_service_project_mgrs WHERE login = ‘dongshin’))
  • fixed View Comments button not enabling after adding comments

Dong Shin 06.11.2012

  • fixed ProjectManagement panel messing up projects grid when filter is applied; AdvancedDataGrid requires IHierarchicalCollectionView
  • investing Comments now showing up properly – working properly, need to check at the site.
  • looking at adding status/progress bar to panels

Dong Shin 06.07.2012

  • added MySQL database connection clean up routines to PPM server
  • mvn tomcat:redeploy generates 403 error on Tomcat 7. Maven’s Tomcat 7 requires different configuration… http://stackoverflow.com/questions/3714080/tomcat-7-maven-plugin
    • in tomcat-users.xml, add
      • <role rolename=”manager-gui”/>
        <role rolename=”manager-script”/>
        <user password=”admin” roles=”manager-gui, manager-script” username=”admin”/>
    • in pom.xml, add
      • <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>tomcat-maven-plugin</artifactId>
        <version>1.0-beta-1</version>
        <configuration>
        <url>http://127.0.0.1:8080/manager/text</url&gt;
        <server>localTomcat</server>
        <path>/${project.build.finalName}</path>
        </configuration>
        </plugin>
  • added the new config to PPM server’s pom.xml, now mvn tomcat:redeploy works.

Dong Shin 06.05.2012

  • checked in fresh copy of ProjPortfolioMgrClient to SVN and moved ProjPortfolioMgrClient to old
  • deployed new PPM and PA at the Fort. new fields seem working
  • User Management errors – Database Null?
    • fixed
  • Delete User errors – fixed
    • DBUpdateSQLs06052012.sql in src/main/resources/database directory
    • caused by cascading foreign keys… this fixes it
      • ALTER TABLE `projects` DROP FOREIGN KEY `projects_ibfk_3` ;
        ALTER TABLE `projects` ADD FOREIGN KEY ( `proj_mgr_login` ) REFERENCES `project_portfolio`.`users` (        `login`) ON DELETE SET NULL ON UPDATE CASCADE ;ALTER TABLE `projects` DROP FOREIGN KEY `projects_ibfk_2` ;
        ALTER TABLE `projects` ADD FOREIGN KEY ( `proj_admin_login` ) REFERENCES `project_portfolio`.`users` (`login`) ON DELETE SET NULL ON UPDATE CASCADE ;ALTER TABLE `projects` DROP FOREIGN KEY `projects_ibfk_1` ;
        ALTER TABLE `projects` ADD FOREIGN KEY ( `portfolio_mgr_login` ) REFERENCES `project_portfolio`.`users` (`login`) ON DELETE SET NULL ON UPDATE CASCADE ;ALTER TABLE `projects` DROP FOREIGN KEY `projects_ibfk_4` ;
        ALTER TABLE `projects` ADD FOREIGN KEY ( `portfolio_admin_login` ) REFERENCES `project_portfolio`.`users` (`login`) ON DELETE SET NULL ON UPDATE CASCADE ; 

Dong Shin 06.04.2012

  • tested new PPM and PA on FGMDEV, seems fine.
    • database updates – DBUpdatesSQLs05242012.sql
    • saved old – ProjPortfolioMgr_06042012 and project_portfolio_06042012
  • found ProjectAssistant gets confused with endpoint after AutoLogin changes – fixed

Dong Shin 05.31.2012

  • cleaned up PPM project structure – moved scripts to new directories
  • working on PPM to add new fields
    • created VO’s for Funding Type, Organization, and Location
    • updated BudgetCenterData with new fields
    • modified CreateProjectPanel to work.
    • testing

Dong Shin 05.24.2012

  • Demo/Tutorial at the site with Mike
  • fixed scriptEngine returning Byte to String for VisibilityScripting
  • fixed SolrXMLIngest to change illegal characters in column names to ‘_’
  • following fields to be added to PPM’s Budget Centers
    • Funding Type – BASE or OCO
    • Location
    • Org
  • SQL to add the fields to the budget centers
    • ALTER TABLE `budget_centers` ADD `funding_type` VARCHAR( 255 ) NULL DEFAULT ‘BASE’,
      ADD `org` VARCHAR( 255 ) NULL DEFAULT ‘S1’,
      ADD `location` VARCHAR( 255 ) NULL DEFAULT ‘Fort Meade, MD’;

Dong Shin 05.22.2012

  • stopped by the Fort, computers were down. Could not get V badge….
  • a bug in VisibilityScripting not returning data fields in correct format for complex SQL queries (Join tables?)
  • fixed COGNOS SQL using CAST function
    • SELECT f.*,
      CAST(
      CASE WHEN SUBSTR(FY_Month, 8, 3) = ‘Oct’ THEN  month_1
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Nov’ THEN  month_2
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Dec’ THEN  month_3
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jan’ THEN  month_4
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Feb’ THEN  month_5
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Mar’ THEN  month_6
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Apr’ THEN  month_7
      WHEN SUBSTR(FY_Month, 8, 3) = ‘May’ THEN  month_8
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jun’ THEN  month_9
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jul’ THEN  month_10
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Aug’ THEN  month_11
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Sep’ THEN  month_12
      ELSE ‘other’
      END AS DECIMAL) AS PM_Actuals
      FROM visibility_dev2.facts_fy12 f, project_portfolio.budget_centers c, project_portfolio.obligations_outlays o
      WHERE c.uid = o.funding_id
      AND f.Program = c.facts_pe
      AND f.BC = c.center_number
      AND f.Approp = c.appropriation
      AND o.year = SUBSTR(FY_Month, 3, 4)
      AND o.year_count = 1
      AND o.type LIKE “PM Actuals%”

Dong Shin 05.21.2012

  • decided to use SQL query to genenrate COGNOS supporting data (PM Actuals) instead of a lengthy Python script
  • working on SQL queries…
  • COGNOS with matching PM Actuals
    • SELECT f.*,
      CASE WHEN SUBSTR(FY_Month, 8, 3) = ‘Oct’ THEN  month_1
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Nov’ THEN  month_2
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Dec’ THEN  month_3
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jan’ THEN  month_4
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Feb’ THEN  month_5
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Mar’ THEN  month_6
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Apr’ THEN  month_7
      WHEN SUBSTR(FY_Month, 8, 3) = ‘May’ THEN  month_8
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jun’ THEN  month_9
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jul’ THEN  month_10
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Aug’ THEN  month_11
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Sep’ THEN  month_12
      ELSE ‘other’
      END AS PM_Actuals
      FROM visibility_dev2.facts_fy12 f, project_portfolio.budget_centers c, project_portfolio.obligations_outlays o
      WHERE c.uid = o.funding_id
      AND f.Program = c.facts_pe
      AND f.BC = c.center_number
      AND f.Approp = c.appropriation
      AND o.year = SUBSTR(FY_Month, 3, 4)
      AND o.year_count = 1
      AND o.type LIKE “PM Actuals%”
      ORDER BY f.uid
  • match COGNOS data with PPM data
    • SELECT f.*, o.month_1 as PM_Actuals FROM visibility_dev2.facts_fy12 f, project_portfolio.budget_centers c, project_portfolio.obligations_outlays o
      WHERE c.uid = o.funding_id
      AND f.Program = c.facts_pe
      AND f.BC = c.center_number
      AND f.Approp = c.appropriation
      AND o.year = 2012
      AND o.year_count = 1
      AND o.type LIKE “PM Actuals%”
  • parse FY_Month field for year and month
    • SELECT f.FY_Month, SUBSTR(FY_Month, 3, 4) as year,
      CASE WHEN SUBSTR(FY_Month, 8, 3) = ‘Oct’ THEN  ‘month_1’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Nov’ THEN  ‘month_2’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Dec’ THEN  ‘month_3’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jan’ THEN  ‘month_4’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Feb’ THEN  ‘month_5’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Mar’ THEN  ‘month_6’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Apr’ THEN  ‘month_7’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘May’ THEN  ‘month_8’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jun’ THEN  ‘month_9’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jul’ THEN  ‘month_10’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Aug’ THEN  ‘month_11’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Sep’ THEN  ‘month_12’
      ELSE ‘other’
      END AS month
      FROM visibility_dev2.facts_fy12 f

Dong Shin 05.18.2012

  • spent sometime setting up Visibility and Scripting Servers for COGNOS data
  • COGNOS data fields mapping to the PPM database fields, others are calculated…
    • FY Month (FYYYYY MMM) – FY2012 Oct
    • Capability
    • Program (FACTS)
    • Service
    • Sub-Project (Expenditure Center)
    • BC  (Budget Center)
    • Approp
    • Current Budget (FY Budget)