- VizTool review
- All fields with $ should accept negative values
- get a list of budget values for FY13 and FY14 charts
- create a query for Planned Obligated vs. FACTS
- Outlay $ Reported row should not be editable
- invoice dialog box should not show if the line item is not a MIPR or EAO
- total budget in slides should be project total, not requistiion total
- Req Data Analysis should show Req Allocated, Req Funded (Committed)
- reworked __view_monthly_committed and __view_financial_data
- returns view in projects instead of requisitions….
Category Archives: Projects
Dong Shin 03.24.2014
- reworked all stored queries for briefing data to have 5 months, fixed month_2 typos – user_queries.sql
- removed year 3 and 4 to speed up __view_financial_data view – DBUpdateEnhancements.032414.sql
- created queries for Lenny – summary of FY13 and FY14 by appropriation and capability
- FY13 Summary by Appropriation and Capability, FY14 Summary by Appropriation and Capability
- select capability, appropriation,
SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.total_budget, 0)) AS total_budget,
SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.funded_budget, 0)) AS funded_budget,
SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, oo.month_5, 0)) AS ‘Obligated Feb 2013’,SUM(IF(oo.type = ‘Outlay $ (Reported in FACTS)’, oo.month_5, 0)) AS ‘Outlay Feb 2013’,
SUM(IF(oo.type = ‘PM Actuals (Invoiced) Outlay $:’, oo.month_5, 0)) AS ‘PM Actuals Feb 2013’,
SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, committed_amount,0 )) AS total_committed
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
LEFT JOIN __view_committed_totals vct ON bc.uid = vct.budget_center_id
where fiscal_year = 2013 AND oo.year_count = 2 AND
(oo.type = ‘Outlay $ (Reported in FACTS)’ OR
oo.type = ‘Reported FACTS Obligated $:’ OR
oo.type = ‘PM Actuals (Invoiced) Outlay $:’)
GROUP BY capability, appropriation
Dong Shin 03.21.2014
- need to remove current month from the queries for the reports/presencation
- tried to create view, __view_financial_data, only to find out that variables are *NOT* allowed! Ugh, need to find a workaround.
- created a trigger – update_tmp_view_financial_data to update __tmp_view_financial_data on obligations/outlays update. this deletes all entries and regenerates financial data whenever obligations/outlays are updated….
- created stored procedure – generate_financial_data()
- created a table – __tmp_view_financial_data from the query in DBUpdateEnhancements.032114.sql
- working on refining queries for financial data
- currently takes ~8 seconds for the query
- optimized to 0.5 using user defined variable for year_total
- select ‘Committed’ AS oo_type, project_number, title, begin_year, end_year, center_name, center_number,
appropriation, type, capability, expenditure_center, investment_portfolio, program_element,
facts_pe, sub_center_name, sub_center_number, req_id, req_type, total_budget, funded_budget,
fiscal_year, year_count,
@year_total := IFNULL((SELECT SUM(year_total) FROM __tmp_monthly_committed vmc2 WHERE vmc1.req_id = vmc2.req_id
AND year_count < 4 GROUP BY vmc2.req_id),0) as year_total ,
@year_total + month_1 AS month_1,
@year_total + month_1+month_2 AS month_2,
@year_total + month_1+month_2+month_3 AS month_3,
@year_total + month_1+month_2+month_3+month_4 AS month_4,
@year_total + month_1+month_2+month_3+month_4+month_5 AS month_5,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6 AS month_6,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7 AS month_7,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8 AS month_8,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9 AS month_9,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10 AS month_10,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11 AS month_11,
@year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11+month_12 AS month_12
FROM __tmp_monthly_committed vmc1
WHERE year_count = 4
- select ‘Committed’ AS oo_type, project_number, title, begin_year, end_year, center_name, center_number,
Phil 3.21.14
8:00 – SR
- Backups
- Add code to MySQLif that does the following to a table stored in visibility_dev2:
- Get a hash of the query string
- start a timer
- run the query
- stop the timer
- SELECT the row with a matching hash or make a new one.
- Add the time to the running total
- increment the count of calls
- This will allow us to find the queries that are being run the most often and the ones that take the most time. We should focus our efforts on the longest queries that are called the most.
- Build a query (per Lenny’s instruction) that shows who’s late and who’s under. Include names, project info and contacts.
Dong Shin 03.20.2014
- deployed new view – __view_monthly_committed and FA
- spent most of the day fixing SQL’s and scripts for the charts……
- need to make sure to use Outlay $ (Reported in FACTS) instead of Reported Outlay $:
- both Obligations and Outlays need second year updates
- fixed stored queries (2013 and 2014) that uses month_2!!!
- performance of __view_financial_data using __view_monthly_committed is very bad… may have to remove the year 3 and 4 for better performance as without it cuts down half time….
Phil 3.20.14
8:00 – 4:00 SR
- Backups
- fixed __view_monthly_committed. It was not summing for year 2
- Put together 2013 charts
- Generate separate financial data for fake cognos data to test chart queries. Nearly done. Need to walk through with Dong and verify that all the needed columns are present.
Dong Shin 03.19.2014
- 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
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.
viztales 
You must be logged in to post a comment.