- deployed new FA/queries
- found that committed amounts are not generated when there is no data for the year – fixed
- fixed various errors from Lenny
- ReqDataAnalysis now has combination of bar and area just like the presentation…
- new query slows down the __view_financial_data…
- new __view_monthly_committed view
- CREATE ALGORITHM = UNDEFINED VIEW `__view_monthly_committed` AS
select ‘Committed Monthly’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
o.year_count AS `year_count`,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0), 0)) as month_1,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0), 0)) as month_2,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0), 0)) as month_3,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0), 0)) as month_4,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0), 0)) as month_5,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0), 0)) as month_6,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0), 0)) as month_7,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0), 0)) as month_8,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0), 0)) as month_9,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0), 0)) as month_10,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0), 0)) as month_11,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0), 0)) as month_12,
SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), `ca`.`committed_amount`, 0)) as year_total
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
left join obligations_outlays o on p.uid = o.project_id AND o.funding_id = bc.uid AND o.type = ‘Reported Outlay $:’
group by `ca`.`budget_center_id`,o.year_count;
- CREATE ALGORITHM = UNDEFINED VIEW `__view_monthly_committed` AS
Monthly Archives: March 2014
Phil 3.19.14
8:00 – 4:00 SR
- There is still snow on the ground. It is still cold.
- Backups.
- New FA
- Multiple DB updates and fixes. Committed not rolling over from one FY to another.
- JavaScript
- WebGL
- This could be really nice. It looks like something I would write: https://github.com/evanw/lightgl.js/. Might try wrapping it in a YUI module, since there’s not that much code.
- Well, it runs great in FF, slow in Chrome, and is broken in IE (unable to return the PointSize attribute from the shader).
- This is *much* faster:
Dong Shin 03.18.2014
- found that Committed Amounts not rolling over from previous year…
- created a view to store monthly committed amounts in a year – __view_monthly_committed
- reworked __view_financial_data view for the __view_monthly_committed
Phil 3.18.14
8:00 – 4:00 SR
- Backups
- Installed new certs on the server. Important note – the alias in the keystore insert request has to be the same as the one in the keystore certreq. Otherwise you get the helpful error message: “java.lang.Exception: Input not an X.509 certificate”
- JavaScript
- WebGL!
- Starting with the WebGL programming guide
- Also dug up my ShaderLearning C++ application. It might come in handy. Need to put it up on GitHub. Done: https://github.com/pgfeldman/FltkShaderSupportDll
- This is the page to test on all our target browsers: http://www.magic.ubc.ca/webgl-pg/uploads/examples/ch03/HelloTriangle.html
Dong Shin 03.17.2014
- working on queries for reports
- required Appropriation/Capabilities combo for 2013
- O&M – ACC, GCC, TRCO
- RDT&E – ACC, CCRSE, GCC, MCC, TRCO
- PROC – GCC, TRCO
- created python script to generate 2013 data – generate_report_data_2013.py
- reworked _view_financial_data to include cumulative committed amounts – DBUpdateEnhancements.031714.sql
- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_financial_data` AS
select ‘Obligation Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,
`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
((`bc`.`funded_budget` * `oo`.`obligation_month_1`) / 100) AS `month_1`,
((`bc`.`funded_budget` * `oo`.`obligation_month_2`) / 100) AS `month_2`,
((`bc`.`funded_budget` * `oo`.`obligation_month_3`) / 100) AS `month_3`,
((`bc`.`funded_budget` * `oo`.`obligation_month_4`) / 100) AS `month_4`,
((`bc`.`funded_budget` * `oo`.`obligation_month_5`) / 100) AS `month_5`,
((`bc`.`funded_budget` * `oo`.`obligation_month_6`) / 100) AS `month_6`,
((`bc`.`funded_budget` * `oo`.`obligation_month_7`) / 100) AS `month_7`,
((`bc`.`funded_budget` * `oo`.`obligation_month_8`) / 100) AS `month_8`,
((`bc`.`funded_budget` * `oo`.`obligation_month_9`) / 100) AS `month_9`,
((`bc`.`funded_budget` * `oo`.`obligation_month_10`) / 100) AS `month_10`,
((`bc`.`funded_budget` * `oo`.`obligation_month_11`) / 100) AS `month_11`,
((`bc`.`funded_budget` * `oo`.`obligation_month_12`) / 100) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
union
select ‘Outlay Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
((`bc`.`funded_budget` * `oo`.`outlay_month_1`) / 100) AS `month_1`,
((`bc`.`funded_budget` * `oo`.`outlay_month_2`) / 100) AS `month_2`,
((`bc`.`funded_budget` * `oo`.`outlay_month_3`) / 100) AS `month_3`,
((`bc`.`funded_budget` * `oo`.`outlay_month_4`) / 100) AS `month_4`,
((`bc`.`funded_budget` * `oo`.`outlay_month_5`) / 100) AS `month_5`,
((`bc`.`funded_budget` * `oo`.`outlay_month_6`) / 100) AS `month_6`,
((`bc`.`funded_budget` * `oo`.`outlay_month_7`) / 100) AS `month_7`,
((`bc`.`funded_budget` * `oo`.`outlay_month_8`) / 100) AS `month_8`,
((`bc`.`funded_budget` * `oo`.`outlay_month_9`) / 100) AS `month_9`,
((`bc`.`funded_budget` * `oo`.`outlay_month_10`) / 100) AS `month_10`,
((`bc`.`funded_budget` * `oo`.`outlay_month_11`) / 100) AS `month_11`,
((`bc`.`funded_budget` * `oo`.`outlay_month_12`) / 100) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
union
select ‘Committed Monthly’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
union
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 3
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 2
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 1
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 0
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
union
select if((`oo`.`type` = ‘Reported Outlay $:’),’Outlays’,if((`oo`.`type` = ‘Reported FACTS Obligated $:’),’Obligated’,’PM Actuals’)) AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,`p`.`type` AS `type`,
`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,
`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year_count` AS `year_count`,
`oo`.`month_1` AS `month_1`,`oo`.`month_2` AS `month_2`,`oo`.`month_3` AS `month_3`,`oo`.`month_4` AS `month_4`,`oo`.`month_5` AS `month_5`,
`oo`.`month_6` AS `month_6`,`oo`.`month_7` AS `month_7`,`oo`.`month_8` AS `month_8`,`oo`.`month_9` AS `month_9`,`oo`.`month_10` AS `month_10`,
`oo`.`month_11` AS `month_11`,`oo`.`month_12` AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays` `oo` on(((`p`.`uid` = `oo`.`project_id`) and (`bc`.`uid` = `oo`.`funding_id`))))
where ((`oo`.`type` = ‘Reported Outlay $:’) or (`oo`.`type` = ‘Reported FACTS Obligated $:’) or (`oo`.`type` = ‘PM Actuals (Invoiced) Outlay $:’));
- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_financial_data` AS
Dong Shin 03.14.2014
- fixed few SQL errors on-site. Month 5 Committed not calculated correctly, used month_2 throughout in the sql
- generated data for the chart and worked!
- changed Requisition Data to view in Advanced DataGrid in ReqDataAnalysis
- added Currency label function to dollar columns
- set initial year to current fiscal year
- working on query to set committed amounts from previous year to next year….
Phil 3.14.14
8:00 – 4:00 SR
- and a happy Pi day to you!
- Deployed new vis
- Made charts
- Made slide show
- Made tables
- Ran script to populate tables
- Success!
- JavaScript
- fix ATR link
- fix ipsum lorem
- Finally figured out the problem with scrollto. The time is in milliseconds. The examples are incorrect, but the documentation is right.
- Commenting
- start on webgl
Dong Shin 03.13.2014
- deployed new FA, queries
- updated committed_amounts to be cumulative in __view_financial_data
- query to update chart data!
- Insert new row with string replace…
INSERT INTO table_dashboards (id, name, owner, xml_layout, published)
SELECT null, name, owner, REPLACE(CONVERT(xml_layout USING utf8),’2013_RDTE_GCC’,’2014_RDTE_GCC’), published FROM table_dashboards
WHERE id = 1 - update a row using string replace…..
UPDATE table_dashboards SET xml_layout=BINARY(REPLACE(CONVERT(xml_layout USING utf8),’2014_RDTE_GCC’,’2015_RDTE_GCC’))
WHERE id = 2
- Insert new row with string replace…
- query for committed amounts
- SELECT bc.uid,bc.fiscal_year,
YEAR(ca.committed_date)-bc.fiscal_year+1 AS year_count,
SUM(IF(MONTH(committed_date)=1,committed_amount,0)) AS month_1,
SUM(IF(MONTH(committed_date)<=2, committed_amount,0)) AS month_2,
SUM(IF(MONTH(committed_date)<=3, committed_amount,0)) AS month_3,
SUM(IF(MONTH(committed_date)<=4, committed_amount,0)) AS month_4,
SUM(IF(MONTH(committed_date)<=5, committed_amount,0)) AS month_5,
SUM(IF(MONTH(committed_date)<=6, committed_amount,0)) AS month_6,
SUM(IF(MONTH(committed_date)<=7, committed_amount,0)) AS month_7,
SUM(IF(MONTH(committed_date)<=8, committed_amount,0)) AS month_8,
SUM(IF(MONTH(committed_date)<=9, committed_amount,0)) AS month_9,
SUM(IF(MONTH(committed_date)<=10, committed_amount,0)) AS month_10,
SUM(IF(MONTH(committed_date)<=11, committed_amount,0)) AS month_11,
SUM(IF(MONTH(committed_date)<=12, committed_amount,0)) AS month_12,
SUM(committed_amount),
YEAR(ca.committed_date),
MONTH(ca.committed_date)
FROM budget_centers bc
LEFT JOIN committed_amounts ca
ON bc.uid = ca.budget_center_id
- SELECT bc.uid,bc.fiscal_year,
Phil 3.13.14
8:00 – 5:00 SR
- Backups
- Charts! Made one, decided that it might be easier to update the Trend Widget to point at different data sources. Wound up allowing edits on the XML in the desktop “save as” option. Built a release and sent to Bill V.
- Deploying new FA
- JavaScript
- Making text unselectable: http://stackoverflow.com/questions/826782/css-rule-to-disable-text-selection-highlighting
- Based on some user testing, I may need to make it so that clicking toggles the selection of a node, rather than selection while mousedown.
Dong Shin 03.12.2014
- deployed FA and the new view __view_financial_data
- committed need to rollover to next month
- working on queries/scripts for reports
- create queries for 2013, 2014 , each appropriation, and each capability – 30 total
- created script to run the queries and save to Visibility database
Phil 3.12.14
8:00 – 4:00 SR
- Backups
- Deployed new FA, and a *huge* view
- Lenny is having a problem where EAs that have been claimed are getting unclaimed.
- Printed slideshow screenshots that we need to match
- Had the evil “Main application must be in the list of application paths” message. This page had the answer. It looks like the mxml file had been dropped from the project file.
- Found the code and the format for building reusable charts. Dong’s building the queries. We’ll try them out tomorrow.
- JavaScript
Dong Shin 03.11.2014
- trouble-shoot Req mapping problem, various of mistakes… noted on Phil’s blog
- working on queries for reports
- created Req Data Analysis panel and added to FA
- __view_financial_data will be added to site
Phil 8.11.14
8:00 – 5:30 SR
- Backups
- More Server Cert work
- Discussions with Lenny
- Need to add a search to the page that will find text in paragraphs and link back to the Physics Shape. All paragraphs that don’t match the text are hidden? Wonder how that will affect the scrollto. Also need an introduction paragraph and a title.
- When a cognos data is manually mapped, obligations and outlays should be added to monthly financial data
- Second year query needs to be fixed for month order
- Need to add a pop-up warning about claiming a req where the subBC doesn’t match.
- RQ notes
- Add a show/hide hidden fields. This does mean that when checked, an item disappears
- Add “strong hide” that keeps the line in the DB, but never shows the line
- Carry through ignore from previous steps. In other words, ignore “hidden” items from subsequent queries
- JavaScript
- Figured out how to grab a redirect request and use it in the context of the operation. Also how to prevent default behavior.
- Added content. Discovered more about stylesheets.
- The descriptor text for Physics shapes was disappearing after a mousup event on the scrollable list. Added a refresh call to the ShapeManager.
- Helped Dong a bit with data providers.
Dong Shin 03.10.2014
- Lenny reported that Req 173756 doesn’t show Obligation Amount / Date in Claimed section… will take a look tomorrow
- working on little Flex App to verify the Financial Data
- reworked Financial Data query for report to generate all the data for all years
- __view_financial_data contains….
- SELECT ‘Obligation Goal’ AS oo_type,
p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
(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
UNION
SELECT ‘Outlay Goal’ AS oo_type,
p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
(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
UNION
SELECT ‘Committed’ AS oo_type,
p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year,
(IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date)) – bc.fiscal_year + 1) AS year_count,
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, IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date))
UNION
SELECT
IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year_count,
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.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced) Outlay $:’)
- SELECT ‘Obligation Goal’ AS oo_type,
Phil 3.10.14
8:00 – 4:00 SR
- Backups
- Started server certs renewal
- There are still some problems with lost req’s. Got a screenshot that has all the pieces.
- Javascript
- Find out while null objects are being returned in events. It turns out that the “id” tag cannot have any special characters in it, or Y.one()/Y.all() will fail. Added a “safeIdString” variable to the PhysicsShape class that runs a regex [^0-9-a-Z] on all names and copies the result to the variable. This still means that the id tag for paragraphs, etc needs to be safe, but it also allows the shapes to show the full name.
- Discovered that my Chrome browser on my dev machine is broken. Tooltip formatting is wrong and hover does not work at all. Need to reinstall. And I.E. is still slooooooow with all this
viztales 
You must be logged in to post a comment.