def build_section_incomes(self):
query = """
select o.id, s.income_year, i.size, s.person_id
from declarations_section s
join declarations_office o on s.office_id=o.id
join declarations_income i on i.section_id=s.id
join declarations_source_document d on s.source_document_id=d.id
where s.income_year >= {} and
s.income_year <= {} and
i.size < {} and
i.size > 50000 and
s.person_id is not null and
d.median_income > 10000 and
i.relative='{}'
order by o.id
""".format(self.income_stat_start_year, self.last_year,
self.max_income, models.Relative.main_declarant_code)
office_stats = TAllGroupIncomeStats()
rubric_stats = TAllGroupIncomeStats()
with connection.cursor() as cursor:
cursor.execute(query)
for office_id, office_items in groupby(cursor, itemgetter(0)):
rubric_id = RUSSIA.get_office(office_id).rubric_id
for _, year, income, person_id in office_items:
if income / 12 < RUSSIA.get_mrot(year):
continue
office_stats.add_income(office_id, person_id, year, income)
rubric_stats.add_income(rubric_id, person_id, year, income)
return office_stats, rubric_stats
def build_declarant_incomes(self,
year,
max_income=5000000
) -> TAllRegionStatsForOneYear:
region_data = TAllRegionStatsForOneYear(
year, file_name=self.options.get('output_json'))
minOboronyId = 450
query = """
select o.region_id, i.size
from declarations_section s
join declarations_office o on s.office_id=o.id
join declarations_income i on i.section_id=s.id
where s.income_year = {} and
i.size < {} and
i.size > 0 and
i.relative='{}' and
o.id != {} and
o.region_id is not null and
o.region_id != {}
order by o.region_id, i.size
""".format(year, max_income, models.Relative.main_declarant_code,
minOboronyId, TRussianRegions.Russia_as_s_whole_region_id)
regions = TRussianRegions()
mrot = RUSSIA.get_mrot(year)
assert mrot is not None
with connection.cursor() as cursor:
cursor.execute(query)
for region_id, items in groupby(cursor, itemgetter(0)):
incomes = list(income for _, income in items
if income / 12 > mrot)
if region_id == TRussianRegions.Baikonur:
continue
region = regions.get_region_by_id(region_id)
if region.joined_to is not None:
region = regions.get_region_by_id(region.joined_to)
stat_info = region_data.ross_stat.get_data(region.id, year)
if stat_info is None:
raise Exception(
"cannot find stat_info for region.id={}, region.name={}"
.format(region.id, region.name))
population = stat_info.population
population_median_income = region_data.ross_stat.get_or_predict_median_salary(
region.id, year)
if population_median_income is None:
raise Exception(
"cannot estimate population median_income for region.id={}, region.name={}"
.format(region.id, region.name))
s = TRegionYearStats(
region.id, region.name, incomes, population_median_income,
population,
region_data.ross_stat.get_data(region.id,
2021).er_election_2021)
region_data.add_snapshot(s)
region_data.calc_aux_params()
return region_data