Category Archives: Dong Shin

Dong Shin 11.27.2012

• COGNOS Data to update PPM data
  1. Match COGNOS Requisition ID to MIPR (Funding Transaction Number)
  2. COGNOS Expensed Amount summed by month
  3. Overwrite existing PPM data
  4. Update continuously (daily?)
  5. If there is no value for Expensed Amount COGNOS column, then skip
  6. If there is a negative amount – i.e. ($10,123), subtract in the sum
  7. 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
• 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

Dong Shin 11.26.2012

• got Visibility Login working using Oracle ADF!

Dong Shin 11.21.2012

 

• going through Oracle ADF tutorials
• JSF components showcase – trend?
  • RichFaces
  • PrimeFaces – charts generated as images!
  • ICEfaces – charts generated as images!
  • OpenFaces – Data Table
• ADF Faces component guide
• Custom Declarative Components in ADF Faces Rich Client

Dong Shin 11.19.2012

• JDeveloper tutorials
  • http://docs.oracle.com/cd/E18941_01/tutorials/toc.htm
  • MySQL set up is painful! requires mysql jar file in multiple locations when using WebLogic server for testing
  • using Oracle Database Expression Edition 11g Release 2 – got it working!
  • JDeveloper/Maven Integration
  • Creating and Using an ADF Declarative Component
  • How to deploy a 11g ADF application to Tomcat 6.x – should work with 7

Dong Shin 11.16.2012

• interview
• talked to Len about duplicate projects warning bug. The current criteria is the same number of years with same total FY budget, disabled for now. may want to add it later
• looking into Oracle JDeveloper/ADF
  • going through tutorials
  • Useful links
    • http://technology.amis.nl/2010/08/26/manage-jdeveloper-external-libraries/
    • http://technology.amis.nl/2010/08/29/change-the-jdeveloper-system-directory/
    • http://www.oracle.com/technetwork/developer-tools/jdev/maven11g-090173.html

Dong Shin 11.14.2012

• Alert_UpdateAlertsNotResolvedIn7Days.py – should run every day to update alert status
• rearranged panels VSS4 so the panel select works properly
• modified backup.sh to delete files older than 3 days

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;

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

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

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

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

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….