/*	===============================================================================================
	Project: ICST | BI2I | Tax Calculator
	-----------------------------------------------------------------------------------------------
	Date: Aug 11, 2017
	Details: 
		1) 	Create final propoerty summary table
	============================================================================================= */

	
/*	Step 01: Join int_007_final_potential_using_new_tax_calc and original_propertysummary
	=============================================================================================== 
*/
alter table bi2i_tax_calc.original_propertysummary add index (sas_base_app_no);
alter table bi2i_tax_calc.int_007_final_potential_using_new_tax_calc add index (sas_base_app_no);

create table bi2i_tax_calc.temp_01 as 
select 
a.id,
a.countryid,
a.stateid,
a.districtid,
a.zoneid,
a.wardid,
a.wardno,
a.streetno,
a.sas_base_app_no,
a.new_pid_no,
a.old_pid_no,
a.owner_name,
a.address,
round(a.total_tax_todate, 0) as total_tax_todate,
round(a.avg_tax_per_year, 0) as avg_tax_per_year,
b.first_year as first_year_tax_paid,
b.last_year as last_year_tax_paid,
(case when (b.years_defaulted < 0) then 0 else b.years_defaulted end) as defaulted_years,
b.cov_tax as tax_variance,
round(b.ideal_tax_cap_till_2015, 0) as tax_cap_2015,
round(b.tax_paid_08, 0) as tax_paid_08,
round(b.tax_paid_09, 0) as tax_paid_09,
round(b.tax_paid_10, 0) as tax_paid_10,
round(b.tax_paid_11, 0) as tax_paid_11,
round(b.tax_paid_12, 0) as tax_paid_12,
round(b.tax_paid_13, 0) as tax_paid_13,
round(b.tax_paid_14, 0) as tax_paid_14,
round(b.tax_paid_15, 0) as tax_paid_15,
round(b.tax_paid_16, 0) as tax_paid_16,
round(b.ideal_tax_08, 0) as ideal_tax_08,
round(b.ideal_tax_09, 0) as ideal_tax_09,
round(b.ideal_tax_10, 0) as ideal_tax_10,
round(b.ideal_tax_11, 0) as ideal_tax_11,
round(b.ideal_tax_12, 0) as ideal_tax_12,
round(b.ideal_tax_13, 0) as ideal_tax_13,
round(b.ideal_tax_14, 0) as ideal_tax_14,
round(b.ideal_tax_15, 0) as ideal_tax_15,
round(b.ideal_tax_16, 0) as ideal_tax_16,
(case when b.tax_default_potential < 0 then 0 else round(b.tax_default_potential, 0) end) as tax_default_potential,
a.cc_code,
a.khata_survey_no,
a.site_area_sqft,
a.built_up_area_sqft,
a.usage_of_property,
a.self_or_tenant,
a.zone_res,
a.zone_non_res,
a.demand,
a.data_score
from bi2i_tax_calc.original_propertysummary as a
left join bi2i_tax_calc.int_007_final_potential_using_new_tax_calc as b
on ((a.districtid = b.districtid) and (a.sas_base_app_no = b.sas_base_app_no));





/*	Step 02: Create final table with rank based on district x ward x new_total_potential
	=============================================================================================== 
*/
set @rk = 1;
set @dist_no = '';
set @ward_no = '';
set @new_tp = '';

create table bi2i_tax_calc.final_propertysummary as 
select
id,
countryid,
stateid,
districtid,
zoneid,
wardid,
wardno,
streetno,
sas_base_app_no,
new_pid_no,
old_pid_no,
owner_name,
address,
total_tax_todate,
avg_tax_per_year,
first_year_tax_paid,
last_year_tax_paid,
defaulted_years,
tax_variance,
tax_paid_08,
tax_paid_09,
tax_paid_10,
tax_paid_11,
tax_paid_12,
tax_paid_13,
tax_paid_14,
tax_paid_15,
tax_paid_16,
ideal_tax_08,
ideal_tax_09,
ideal_tax_10,
ideal_tax_11,
ideal_tax_12,
ideal_tax_13,
ideal_tax_14,
ideal_tax_15,
ideal_tax_16,
tax_default_potential,
cc_code,
khata_survey_no,
site_area_sqft,
built_up_area_sqft,
usage_of_property,
self_or_tenant,
zone_res,
zone_non_res,
demand,
data_score,
rank_dist_ward_new_tp
from (
	select 
	*,
	@rk := if(@dist_no = districtid, if(@ward_no = wardid, if(@new_tp = tax_default_potential, @rk, @rk + 1), 1), 1) as rank_dist_ward_new_tp,
	@dist_no := districtid,
	@ward_no := wardid,
	@new_tp := tax_default_potential
	from bi2i_tax_calc.temp_01
	order by districtid, wardid, tax_default_potential desc
) a;

drop table bi2i_tax_calc.temp_01;


/*	Step 03: Create Data Flag and Criticality Flag from final_propertysummary table
	=============================================================================================== 
*/

alter table bi2i_tax_calc.final_propertysummary 
add critical_flag varchar (10),
add data_flag varchar (10);

update bi2i_tax_calc.final_propertysummary
set 
critical_flag = 
case 
when (tax_default_potential > 100000) or (defaulted_years >= 5) or 
	 (defaulted_years >= 6 and tax_default_potential > 0 )
	then 'Red'
when (tax_variance > 0.3 and critical_flag != 'Red') 
	then 'Amber'
	else 'Green'	
end,
data_flag = 
case when data_score < 10 then 'Brown' else 'No Colour' end;





/*	Step 04: Create sample view with top 100 from each ward
	=============================================================================================== 
*/
create table bi2i_tax_calc.final_propertysummary_top_100 as 
select * from bi2i_tax_calc.final_propertysummary
where rank_dist_ward_new_tp <= 100;