Dong Shin 02.10.2011

  • while searching for ways to save PPM data into excel format, found
  • PPM Changes
    • saving database changes to DBUpdateSQLs02102011.sql
    • Funding Request Changes
      • larger mailing address POC
      • added email and phone/fax for mailing address POC
      • two more fields to funding_requests table – mailing_address_email and mailing_address_phone
        • ALTER TABLE `funding_requests` ADD `mailing_address_email` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `mailing_address` ,
          ADD `mailing_address_phone` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `mailing_address_email`
      • removed Contracts from Reimbursable
      • Add Contract added to Direct Cite window
      • added a new field to contracts table
        • ALTER TABLE `contracts` ADD `ACRN` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `contract_number`
      • remove all, but FY and Budget Center in BudgetCentersWindow for Financial Status
    • working on a pivot query to return financial data for selected project, month, year
      • find all first month data for year 2011 from projects and obligations_outlays tables
        SELECT year, project_id, year_count,
        GROUP_CONCAT(IF(type=’Planned Obligated $:’, month_1, NULL)) AS ‘Planned Obligated $:’,
        GROUP_CONCAT(IF(type=’Reported Actual Obligated $:’, month_1, NULL)) AS ‘Reported Actual Obligated $:’,
        GROUP_CONCAT(IF(type=’Planned Outlay $:’, month_1, NULL)) AS ‘Planned Outlay $:’,
        GROUP_CONCAT(IF(type=’Reported Outlay $:’, month_1, NULL)) AS ‘Reported Outlay $:’,
        GROUP_CONCAT(IF(type=’PM Actuals Outlay $:’, month_1, NULL)) AS ‘PM Actuals Outlay $:’,
        GROUP_CONCAT(IF(type=’Outlay $ (Reported in FACTS)’, month_1, NULL)) AS ‘Outlay $ (Reported in FACTS)’,
        p.*
        FROM obligations_outlays o, projects p
        WHERE year = 2011 AND year_count = 1 AND o.project_id = p.uid
        GROUP BY year_count, year, project_id
        ORDER BY project_id, year