Dong Shin 12.05.2012

  • working on FACTS Outlay updates
    • create a script – UpdateFACTSOutlayData.py
    • query to construct update query
      • SELECT MID(requisition_id, LENGTH(requisition_id) – 5, 6) as req_id,
        appropriation_year,
        SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) as month,
        expensed_date,
        SUM(expensed_amount),
        CONCAT(‘UPDATE  project_portfolio.obligations_outlays as o,  project_portfolio.projects AS p,project_portfolio.budget_centers AS c 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.type = ‘Outlay $ (Reported in FACTS)’ AND o.year + o.year_count = ‘, appropriation_year + 1, ‘ AND o.year_count = 1 ‘,
        ‘ AND p.uid = o.project_id AND p.uid = c.project_id AND c.uid = o.funding_id ‘,
        ‘ AND c.funding_transaction_number = ”, MID(requisition_id, LENGTH(requisition_id) – 5, 6), ”’) as sqlstring
        FROM `visibility_dev2`.`facts_outlay_update_test`
        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