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;