SELECT
	budget_org_id AS '预算主体',
	implement_org_name AS '实施主体名称',
	-- 启动时间
	-- start_date AS '启动时间',
	-- 完成时间
	-- complete_date AS '完成时间',
	ROUND( SUM( investment_amount ), 2 ) AS '项目总投资',
	SUM( ytd_budget_amount ) AS '预算年度投资额' 
	-- 当年招标个数
	-- 当年签订合同个数
	-- 当年签订合同金额
	-- 当年发生额
	-- 完成计划（%）
	-- 当年剩余金额
	-- 当年报账次数
	-- 进度阶段
	-- 当年初始阶段
	-- 当年计划完成阶段
	
FROM
	ads_xm_information_cotracts_fct_new 
	
	
/*
GROUP BY
	year_num DESC,
	second_special_name,
	third_special_name,
	equipment_type_meaning
*/
	
	
	-- ---------------------
	
	
SELECT
	-- 当年招标个数
	COUNT(DISTINCT BID_NUMBER) AS '当年招标个数',
	-- 当年签订合同个数
	COUNT(DISTINCT contract_code) AS '当年签订合同个数',
	-- 当年签订合同金额
	SUM(CONTRACT_AMOUNT) AS '当年签订合同金额',
	-- 当年发生额
	ROUND(SUM(Actual_Amount_Month/10000), 2) AS '当年发生额',
	-- 完成计划（%）
	CONCAT(ROUND(SUM(Actual_Amount_Month/10000)/SUM(ytd_budget_amount), 2)*100, '%') AS '完成计划(%)',
	-- 当年剩余金额
	ROUND((SUM(CONTRACT_AMOUNT) - SUM(Actual_Amount_Month/10000)), 2) AS '当年剩余金额',
	-- 当年报账次数
	-- 进度阶段
	-- 当年初始阶段
	-- 当年计划完成阶段	
	
	
	
FROM
	ads_xm_information_cotracts_fct_new 
WHERE 
	year_num = '2020'
group by 
		year_num desc, 
		second_special_name,
		third_special_name,
		equipment_type_meaning



-- -------------------
SELECT 
	COUNT(DISTINCT INVOICE_NUM) AS '当年报账次数'
FROM ads_xm_information_schedule_month
WHERE 
	year_num = '2020'
group by 
		year_num desc, 
		second_special_name,
		third_special_name,
		equipment_type_meaning



-- --------
SELECT 
	year_num AS '预算年度',
-- 	Period_Name,
	second_special_name AS '二级分类',
	third_special_name AS '三级分类',
	project_type_meaning AS '新/续建',
	equipment_type_meaning AS '软/硬件',
	project_number as '项目编号',
	project_name as '项目名称',
	PROJECT_STAGE_NAME AS '进展阶段',
	PROJECT_STATUS_NAME AS '当年初始阶段',
	PERIOD_COMPLEMENT AS '当年计划完成阶段'
FROM ads_xm_information_month_bi
WHERE
    period_name BETWEEN '{选择日期.value}' AND '{日期23.value}'
    AND (category_id LIKE '{公司类型下拉框.value}%' OR '{公司类型下拉框.value}' = 'a')
    AND (company_code = '{公司名称下拉框.value}' OR '{公司名称下拉框.value}' = 'all')
    AND (second_special_code = '{二级分类.value}' OR '{二级分类.value}' = 'all')
    AND (third_special_code = '{三级分类.value}' OR '{三级分类.value}' = 'all')
    AND budget_org_id LIKE '%{预算主体.value}%'
    AND project_number LIKE '%{项目编号.value}%'
    AND project_name LIKE '%{项目名称.value}%'
    AND implement_org_name LIKE '%{实施主体.value}%'

group by 
		Period_Name LIKE CONCAT(LEFT('2021-08', 4), '%'), 
		second_special_name,
		third_special_name,
		equipment_type_meaning,
	AND Period_Name LIKE CONCAT(LEFT('2021-08', 4), '%')
	AND second_special_name = '信息化建设'
	AND third_special_name = '信息化建设'
	AND project_name LIKE '生产运行系统——防腐管理建设'
	AND equipment_type_meaning = '软件'
