Author Archives: gvr4wd

Unknown's avatar

About gvr4wd

...hmmm

Dong Shin 02.03.2014

  • working on FMP viewer for FA – done, testing on-site tomorrow?
    • added FMPDataNavContent to AddBudgetCenterWindow, non-editable textArea!
    • query to get FMPs
      •  SELECT * FROM financial_mitigation_plans fmp LEFT JOIN obligations_outlays AS oo ON fmp.obligation_outlay_uid = oo.uid WHERE oo.project_id = 24 AND oo.funding_id = 1 AND year_count = 1 AND fmp.month = 2

Dong Shin 01.29.2014

  • fixed FA causing SQL errors entering committed amounts to un-saved Req
  • fixed Next button not working in Pre-Processing in Reqconciler
  • fixed Funded Amount in RA – total of Committed Amounts
  • working on adding FMP viewer to Req Editor…
  • included my.ini to try tomorrow

Dong Shin 01.27.2014

  • working on COGNOS data ingest
    • modified server code to accept new set of columns
    • updated the whitelist in FinancialAssistant.properties
    • need to modify columns field in _reqonciler_processing table
      • budget_center,sub_budget_center, executing_budget_center,appropriation_year,requisition_id,po_reference,acr,obligation_date,obligated_amount,expensed_date,expensed_amount
    • need to modify two queries in POST Processing query – details in working queries.sql

Dong Shin 01.24.2014

  • checked the Funded Budget, found that the calculated values not stored correctly. The field needs gone.
  • ran the query for the reports, quick enough.
  • ran through the queries that Lenny runs to get the data from Cognos. I tried to look for the relation tables, but no luck. Somehow it mashes the tables up behind… Then we ran the query with minimal number of columns that we think we definitely need, all the duplicates disappeared! There are more than 7 tables to get all the data and I think the duplicates are generated because of the relations….
  • Minimal Columns!
    • Budget Center, Sub-Budget Center, Executing Budget Center, Appropriation Year, Requisition ID, PO Reference, ACR, Obligation Date, Obligated Amount, Expensed Date, Expensed Amount, Voucher ID
  • working on the new COGNOS data structure changes
    • new contracts_cognos tabel structure
      • CREATE TABLE IF NOT EXISTS `contracts_cognos` (
        `uid` int(11) NOT NULL AUTO_INCREMENT,
        `budget_center` varchar(10) NOT NULL,
        `sub_budget_center` varchar(128) DEFAULT NULL,
        `executing_budget_center` varchar(128) DEFAULT NULL,
        `appropriation_year` int(11) NOT NULL,
        `requisition_id` varchar(10) NOT NULL,
        `po_reference` varchar(40) DEFAULT NULL,
        `acr` varchar(3) DEFAULT NULL,
        `obligation_date` date DEFAULT NULL,
        `obligated_amount` decimal(10,2) DEFAULT NULL,
        `expensed_date` date DEFAULT NULL,
        `expensed_amount` decimal(10,2) DEFAULT NULL,
        `voucher_id` varchar(255) DEFAULT NULL,
        `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `hide_flag` tinyint(4) NOT NULL DEFAULT ‘0’,
        PRIMARY KEY (`uid`)
        ) ENGINE=InnoDB;

Dong Shin 01.23.2014

  • bugs from site
    • Funded Amount is different between FA and RA – verified… will try new SWF’s tomorrow
    • need to be able to edit the COGNOS data to resolve duplicate errors – Lenny recommended using Voucher ID(?)
  • working on query for reports – query to retrieve the data….
    • SELECT ‘Obligation Goal’ AS oo_type, p.*, bc.*,
      (bc.funded_budget * obligation_month_1 / 100) as month_1,
      (bc.funded_budget * obligation_month_2 / 100) as month_2,
      (bc.funded_budget * obligation_month_3 / 100) as month_3,
      (bc.funded_budget * obligation_month_4 / 100) as month_4,
      (bc.funded_budget * obligation_month_5 / 100) as month_5,
      (bc.funded_budget * obligation_month_6 / 100) as month_6,
      (bc.funded_budget * obligation_month_7 / 100) as month_7,
      (bc.funded_budget * obligation_month_8 / 100) as month_8,
      (bc.funded_budget * obligation_month_9 / 100) as month_9,
      (bc.funded_budget * obligation_month_10 / 100) as month_10,
      (bc.funded_budget * obligation_month_11 / 100) as month_11,
      (bc.funded_budget * obligation_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      WHERE oo.year = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      UNION
      SELECT ‘Outlay Goal’ AS oo_type, p.*, bc.*,
      (bc.funded_budget * outlay_month_1 / 100) as month_1,
      (bc.funded_budget * outlay_month_2 / 100) as month_2,
      (bc.funded_budget * outlay_month_3 / 100) as month_3,
      (bc.funded_budget * outlay_month_4 / 100) as month_4,
      (bc.funded_budget * outlay_month_5 / 100) as month_5,
      (bc.funded_budget * outlay_month_6 / 100) as month_6,
      (bc.funded_budget * outlay_month_7 / 100) as month_7,
      (bc.funded_budget * outlay_month_8 / 100) as month_8,
      (bc.funded_budget * outlay_month_9 / 100) as month_9,
      (bc.funded_budget * outlay_month_10 / 100) as month_10,
      (bc.funded_budget * outlay_month_11 / 100) as month_11,
      (bc.funded_budget * outlay_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      WHERE oo.year = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      UNION
      SELECT ‘Committed’ AS oo_type, p.*, bc.*,
      SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
      SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
      SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
      SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
      SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
      SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
      SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
      SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
      SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
      SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
      SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
      SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN committed_amounts AS ca ON ca.budget_center_id = bc.uid
      GROUP BY budget_center_id
      UNION

      SELECT
      IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
      p.*, bc.*,
      month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      WHERE oo.year_count = 1
      AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      AND (oo.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced Outlay) $:’)

Dong Shin 01.22.2014

  • working on queries for reports
    • saving working stuff to working queries.sql in FA project
    • got Obligation Goal, Outlay Goal, Committed, Obligated, PM Actuals, Outlays queries working
    • combining queries to one gigantic query!

Dong Shin 01.17.2014

  • realized that I have not put in anything for past two days….
  • fixed Reqonciler resetting hide_flag when there is no row selected
  • changed FA to accept negative (-) amounts per Lenny’s request – Committed Amounts
  • working on queries for reports
  • created ResizableTitleWindow in ResizableControls to prevent the windows to disappear outside the application content area – not used in any apps yet.
  • added doMove event handler in ResizableTitleWindow that extends from MX components, this behaves better than Spark component above….
    • created MTitleWindow and ResizableTitleWindow extends from it. MTitleWindow can be used as a TitleWindow

Dong Shin 01.13.2014

  • working on adding FMP’s to FA
  • fixed RA throwing exception on refreshing Financial Data
  • experimented MySQL with some different configuration, but no noticeable difference as mine is already quick, but will try on-site tomorrow
    • key_buffer = 16M
      max_allowed_packet = 100M
      table_cache = 64
      sort_buffer_size = 512K
      net_buffer_length = 8K
      read_buffer_size = 256K
      read_rnd_buffer_size = 512K
      myisam_sort_buffer_size = 8M
    • innodb_buffer_pool_size = 16M
      innodb_additional_mem_pool_size = 2M
      ## Set .._log_file_size to 25 % of buffer pool size
      innodb_log_file_size = 5M
      innodb_log_buffer_size = 8M
      innodb_flush_log_at_trx_commit = 1
      innodb_lock_wait_timeout = 50

      key_buffer = 20M
      sort_buffer_size = 20M
      read_buffer = 2M
      write_buffer = 2M

Dong Shin 01.09.2014

  • Phil found that RA takes too long to retrieve the list of Req’s
    • looked at various MySQL configurations – will try my-large.ini or my-huge.ini tomorrow (xampp/mysql)
  • working on queries for reports
    • now I have the goals, committed, obligation/outlay, PM actuals!
    • get goals matching project’s appropriation
      • SELECT p.*, bc.*,
        (bc.funded_budget * obligation_month_1 / 100) as month_1,
        (bc.funded_budget * obligation_month_2 / 100) as month_2,
        (bc.funded_budget * obligation_month_3 / 100) as month_3,
        (bc.funded_budget * obligation_month_4 / 100) as month_4,
        (bc.funded_budget * obligation_month_5 / 100) as month_5,
        (bc.funded_budget * obligation_month_6 / 100) as month_6,
        (bc.funded_budget * obligation_month_7 / 100) as month_7,
        (bc.funded_budget * obligation_month_8 / 100) as month_8,
        (bc.funded_budget * obligation_month_9 / 100) as month_9,
        (bc.funded_budget * obligation_month_10 / 100) as month_10,
        (bc.funded_budget * obligation_month_11 / 100) as month_11,
        (bc.funded_budget * obligation_month_12 / 100) as month_12,
        (bc.funded_budget * outlay_month_1 / 100) as outlay_month_1,
        (bc.funded_budget * outlay_month_2 / 100) as outlay_month_2,
        (bc.funded_budget * outlay_month_3 / 100) as outlay_month_3,
        (bc.funded_budget * outlay_month_4 / 100) as outlay_month_4,
        (bc.funded_budget * outlay_month_5 / 100) as outlay_month_5,
        (bc.funded_budget * outlay_month_6 / 100) as outlay_month_6,
        (bc.funded_budget * outlay_month_7 / 100) as outlay_month_7,
        (bc.funded_budget * outlay_month_8 / 100) as outlay_month_8,
        (bc.funded_budget * outlay_month_9 / 100) as outlay_month_9,
        (bc.funded_budget * outlay_month_10 / 100) as outlay_month_10,
        (bc.funded_budget * outlay_month_11 / 100) as outlay_month_11,
        (bc.funded_budget * outlay_month_12 / 100) as outlay_month_12
        FROM projects p
        LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
        LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
        WHERE oo.year = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)

Dong Shin 01.08.2014

  • fixed some bugs and went through FA, RA, and Reqconciler to use GLOBALS 
  • working on queries for presentation using Phil’s test data
    • query to get all obligations/outlays for current fiscal year
      • SELECT * FROM projects p LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id WHERE oo.year_count = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
    • query to get monthly committed amounts by req_id
      • SELECT budget_center_id, year(committed_date), month(committed_date), sum(committed_amount),
        SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
        SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
        SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
        SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
        SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
        SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
        SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
        SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
        SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
        SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
        SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
        SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
        FROM committed_amounts
        GROUP BY budget_center_id