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