def get_excel_data(self):
def evaulate_value(row, headers_with_columns):
row_data = []
for header in headers_with_columns:
if len(header) == 2:
if header[1] in (
'num_awcs_conducted_cbe',
'num_awcs_conducted_vhnd'
) and self.config['aggregation_level'] == 5:
row_data.append('Yes' if row[header[1]] == 1 else 'No')
else:
row_data.append(row[header[1]])
else:
if row[header[2]]:
percentage = row[header[1]]/row[header[2]] * 100
else:
percentage = 0
row_data.append("%.2f" % percentage)
return row_data
filters = copy.deepcopy(self.config)
del filters['beneficiary_category']
del filters['domain']
order_by = self.location_columns
values = {header[1] for header in self.headers_and_calculation if len(header) == 2}
headers = [header[0] for header in self.headers_and_calculation]
data = ServiceDeliveryReportView.objects.filter(**filters).order_by(*order_by).values(*values)
excel_rows = [headers]
for row in data:
excel_rows.append(evaulate_value(row, self.headers_and_calculation))
filters = [['Generated at', india_now()]]
if self.location:
locs = SQLLocation.objects.get(location_id=self.location).get_ancestors(include_self=True)
for loc in locs:
filters.append([loc.location_type.name.title(), loc.name])
else:
filters.append(['Location', 'National'])
date = self.config['month']
filters.append(['Month', date.strftime("%B")])
filters.append(['Year', date.year])
return [
[
self.title,
excel_rows
],
[
'Export Info',
filters
]
]
def get_excel_data(self, location):
aggregation_level = self.loc_level
filters = {}
filters[
'aggregation_level'] = aggregation_level # this report is needed district wise only
export_filters = [['Generated at', india_now()]]
if location:
try:
locs = SQLLocation.objects.get(
location_id=location).get_ancestors(include_self=True)
for loc in locs:
export_filters.append(
[loc.location_type.name.title(), loc.name])
location_key = '%s_id' % loc.location_type.code
filters.update({
location_key: loc.location_id,
})
except SQLLocation.DoesNotExist:
pass
order_by = ('state_name', 'district_name')
if self.report_type == 'month':
filters['month'] = self.config['month']
excel_rows = self.month_wise(filters, order_by)
else:
filters['month__in'] = generate_quarter_months(
self.quarter, self.year)
excel_rows = self.quarter_wise(filters, order_by,
aggregation_level)
export_filters.append(['Report Layout', self.layout.title()])
export_filters.append(['Data Period', self.report_type.title()])
return [[self.title, excel_rows], ['Export Info', export_filters]]
def get_excel_data(self, location):
def _format_infrastructure_data(data):
return data if data is not None else DATA_NOT_ENTERED
def _format_date(data):
return data.strftime(
"%d-%m-%Y") if data is not DATA_NOT_ENTERED else data
export_filters = [['Generated at', india_now()]]
filters = {}
if location:
try:
locs = SQLLocation.objects.get(
location_id=location).get_ancestors(include_self=True)
for loc in locs:
export_filters.append(
[loc.location_type.name.title(), loc.name])
location_key = '%s_id' % loc.location_type.code
filters.update({
location_key: loc.location_id,
})
except SQLLocation.DoesNotExist:
pass
order_by = ('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name')
query_set = AggregateInactiveAWW.objects.filter(**filters).order_by(
*order_by)
data = query_set.values('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name', 'awc_site_code',
'first_submission', 'last_submission',
'no_of_days_since_start',
'no_of_days_inactive')
headers = [
'State', 'District', 'Block', 'Supervisor name', 'AWC Name',
'AWC site code', 'First submission date', 'Last submission date',
'Days since start', 'Days inactive'
]
excel_rows = [headers]
for row in data:
row_data = [
row['state_name'], row['district_name'], row['block_name'],
row['supervisor_name'], row['awc_name'], row['awc_site_code'],
_format_date(
_format_infrastructure_data(row['first_submission'])),
_format_date(
_format_infrastructure_data(row['last_submission'])),
_format_infrastructure_data(row['no_of_days_since_start']),
_format_infrastructure_data(row['no_of_days_inactive'])
]
excel_rows.append(row_data)
return [[self.title, excel_rows], ['Export Info', export_filters]]
def get_excel_data(self, location, system_usage_num_launched_awcs_formatting_at_awc_level=False):
excel_rows = []
headers = []
for column in self.columns:
if isinstance(column, Column):
headers.append(column.header)
else:
headers.append(column['header'])
excel_rows.append(headers)
for row in self.get_data():
row_data = []
for c in self.columns:
if isinstance(c, Column):
cell = row[c.slug]
else:
cell = row[c['slug']]
if not isinstance(cell, dict):
row_data.append(cell if cell else DATA_NOT_ENTERED)
else:
row_data.append(cell['sort_key'] if cell and 'sort_key' in cell else cell)
excel_rows.append(row_data)
filters = [['Generated at', india_now()]]
if location:
locs = SQLLocation.objects.get(location_id=location).get_ancestors(include_self=True)
for loc in locs:
filters.append([loc.location_type.name.title(), loc.name])
if 'aggregation_level' in self.config:
levels = ['State', 'District', 'Block', 'Supervisor', 'AWC']
filters.append(['Grouped By', levels[self.config['aggregation_level'] - 1]])
if 'month' in self.config:
date = self.config['month']
filters.append(['Month', date.strftime("%B")])
filters.append(['Year', date.year])
if 'filters' in self.config:
filter_values = []
for filter_by in self.config['filters']:
filter_values.append(FILTER_BY_LIST[filter_by])
filters.append(['Filtered By', ', '.join(filter_values)])
# as DatabaseColumn from corehq.apps.reports.sqlreport doesn't format None
if system_usage_num_launched_awcs_formatting_at_awc_level and NUM_LAUNCHED_AWCS in excel_rows[0]:
num_launched_awcs_column = excel_rows[0].index(NUM_LAUNCHED_AWCS)
for record in excel_rows[1:]:
if record[num_launched_awcs_column] == DATA_NOT_ENTERED:
record[num_launched_awcs_column] = 'Not Launched'
else:
record[num_launched_awcs_column] = \
'Launched' if record[num_launched_awcs_column] else 'Not Launched'
return [
[
self.title,
excel_rows
],
[
'Export Info',
filters
]
]
def get_excel_data(self, location, system_usage_num_launched_awcs_formatting_at_awc_level=False,
system_usage_num_of_days_awc_was_open_formatting=False):
excel_rows = []
headers = []
for column in self.columns:
if isinstance(column, Column):
headers.append(column.header)
else:
headers.append(column['header'])
excel_rows.append(headers)
for row in self.get_data():
row_data = []
for c in self.columns:
if isinstance(c, Column):
cell = row[c.slug]
else:
cell = row[c['slug']]
if not isinstance(cell, dict):
row_data.append(cell if cell else DATA_NOT_ENTERED)
else:
row_data.append(cell['sort_key'] if cell and 'sort_key' in cell else cell)
excel_rows.append(row_data)
filters = [['Generated at', india_now()]]
if location:
locs = SQLLocation.objects.get(location_id=location).get_ancestors(include_self=True)
for loc in locs:
filters.append([loc.location_type.name.title(), loc.name])
if 'aggregation_level' in self.config:
levels = ['State', 'District', 'Block', 'Supervisor', 'AWC']
filters.append(['Grouped By', levels[self.config['aggregation_level'] - 1]])
if 'month' in self.config:
date = self.config['month']
filters.append(['Month', date.strftime("%B")])
filters.append(['Year', date.year])
if 'filters' in self.config:
filter_values = []
for filter_by in self.config['filters']:
filter_values.append(FILTER_BY_LIST[filter_by])
filters.append(['Filtered By', ', '.join(filter_values)])
# as DatabaseColumn from corehq.apps.reports.sqlreport doesn't format None
if system_usage_num_launched_awcs_formatting_at_awc_level and NUM_LAUNCHED_AWCS in excel_rows[0]:
num_launched_awcs_column = excel_rows[0].index(NUM_LAUNCHED_AWCS)
for record in excel_rows[1:]:
if record[num_launched_awcs_column] == DATA_NOT_ENTERED:
record[num_launched_awcs_column] = 'Not Launched'
else:
record[num_launched_awcs_column] = \
'Launched' if record[num_launched_awcs_column] else 'Not Launched'
if system_usage_num_of_days_awc_was_open_formatting and \
self.loc_level <= 4 and NUM_OF_DAYS_AWC_WAS_OPEN in excel_rows[0]:
num_of_days_awc_was_open_column = excel_rows[0].index(NUM_OF_DAYS_AWC_WAS_OPEN)
for record in excel_rows[1:]:
if record[num_of_days_awc_was_open_column] == DATA_NOT_ENTERED:
record[num_of_days_awc_was_open_column] = 'Not Applicable'
return [
[
self.title,
excel_rows
],
[
'Export Info',
filters
]
]
def get_excel_data(self):
def _format_infrastructure_data(data):
return data if data else DATA_NOT_ENTERED
data = AWWIncentiveReport.objects.filter(
month=self.month, block_id=self.block
).order_by(
'-supervisor_name'
).values(
'state_name', 'district_name', 'block_name', 'supervisor_name', 'awc_name', 'aww_name',
'contact_phone_number', 'wer_weighed', 'wer_eligible', 'awc_num_open', 'valid_visits',
'expected_visits'
)
headers = [
'State', 'District', 'Block', 'Supervisor', 'AWC', 'AWW Name', 'AWW Contact Number',
'Home Visits Conducted', 'Number of Days AWC was Open', 'Weighing Efficiency', 'Eligible for Incentive'
]
excel_rows = [headers]
for row in data:
home_visit_percent = row['valid_visits'] / int(row['expected_visits']) if int(row['expected_visits']) else 1
weighing_efficiency = row['wer_weighed'] / row['wer_eligible'] if row['wer_eligible'] else 1
if home_visit_percent > 1:
home_visit_percent = 1
if row['awc_num_open'] is None:
num_open = DATA_NOT_ENTERED
else:
num_open = row['awc_num_open']
excel_rows.append(
[
row['state_name'],
row['district_name'],
row['block_name'],
row['supervisor_name'],
row['awc_name'],
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(row['contact_phone_number']),
'{:.2%}'.format(home_visit_percent),
num_open,
'{:.2%}'.format(weighing_efficiency),
'Yes' if weighing_efficiency >= 0.6 and home_visit_percent >= 0.6 else 'No'
]
)
return [
[
'AWW Performance Report',
excel_rows
],
[
'Export Info',
[
['Generated at', india_now()],
['Grouped By', 'AWC'],
['Month', self.month.month],
['Year', self.month.year],
['Disclaimer', "The information in the report is based on the self-reported data entered by "
"the Anganwadi Worker in ICDS-CAS mobile application and is subject to timely "
"data syncs."]
]
]
]
def get_excel_data(self):
def _format_infrastructure_data(data):
return data if data else DATA_NOT_ENTERED
if self.aggregation_level == 1:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month, state_id=self.location
).order_by('-district_name', '-block_name', '-supervisor_name')
elif self.aggregation_level == 2:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month, district_id=self.location
).order_by('-block_name', '-supervisor_name')
else:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month, block_id=self.location
).order_by('-supervisor_name')
data = data.values(
'state_name', 'district_name', 'block_name', 'supervisor_name', 'awc_name', 'aww_name',
'contact_phone_number', 'wer_weighed', 'wer_eligible', 'awc_num_open', 'valid_visits',
'expected_visits', 'is_launched'
)
if self.beta:
headers = [
'State', 'District', 'Block', 'Supervisor', 'AWC', 'AWW Name', 'AWW Contact Number',
'Home Visits Conducted', 'Weighing Efficiency', 'AWW Eligible for Incentive',
'Number of Days AWC was Open', 'AWH Eligible for Incentive'
]
else:
headers = [
'State', 'District', 'Block', 'Supervisor', 'AWC', 'AWW Name', 'AWW Contact Number',
'Home Visits Conducted', 'Number of Days AWC was Open', 'Weighing Efficiency',
'Eligible for Incentive'
]
excel_rows = [headers]
for row in data:
row_data = [
row['state_name'],
row['district_name'],
row['block_name'],
row['supervisor_name'],
row['awc_name'],
]
# AWC not launched
if row['is_launched'] != 'yes':
AWC_NOT_LAUNCHED = 'AWC not launched'
row_data.extend([
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED
])
if self.beta:
row_data.append(AWC_NOT_LAUNCHED)
else:
if self.month.year < 2019 or (self.month.year == 2019 and self.month.month < 3):
func = int
else:
func = round
home_visit_percent = row['valid_visits'] / func(row['expected_visits']) if \
func(row['expected_visits']) else 1
weighing_efficiency_percent = row['wer_weighed'] / row['wer_eligible'] if \
row['wer_eligible'] else 1
if home_visit_percent > 1:
home_visit_percent = 1
home_visit_conducted = '{:.2%}'.format(home_visit_percent)
if row['awc_num_open'] is None:
num_open = DATA_NOT_ENTERED
else:
num_open = row['awc_num_open']
weighing_efficiency = '{:.2%}'.format(weighing_efficiency_percent)
eligible_for_incentive = 'Yes' if \
weighing_efficiency_percent >= 0.6 and home_visit_percent >= 0.6 else 'No'
no_visits = row['valid_visits'] == 0 and row['expected_visits'] == 0
no_weights = row['wer_eligible'] == 0
if no_visits:
home_visit_conducted = "No expected home visits"
if no_weights:
weighing_efficiency = "No expected weight measurement"
if no_visits and no_weights:
eligible_for_incentive = "Yes"
if self.beta:
awh_eligible_for_incentive = 'Yes' if num_open >= 21 else 'No'
row_data.extend([
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(row['contact_phone_number']),
home_visit_conducted,
weighing_efficiency,
eligible_for_incentive,
num_open,
awh_eligible_for_incentive
])
else:
row_data.extend([
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(row['contact_phone_number']),
home_visit_conducted,
num_open,
weighing_efficiency,
eligible_for_incentive
])
excel_rows.append(row_data)
return [
[
'AWW Performance Report',
excel_rows
],
[
'Export Info',
[
['Generated at', india_now()],
['Grouped By', 'AWC'],
['Month', self.month.month],
['Year', self.month.year],
['Disclaimer', "The information in the report is based on the self-reported data entered by "
"the Anganwadi Worker in ICDS-CAS mobile application and is subject to timely "
"data syncs."]
]
]
]
def get_excel_data(self):
def _format_infrastructure_data(data):
return data if data else DATA_NOT_ENTERED
if self.aggregation_level == 1:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month,
state_id=self.location).order_by('-district_name',
'-block_name',
'-supervisor_name')
elif self.aggregation_level == 2:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month,
district_id=self.location).order_by('-block_name',
'-supervisor_name')
else:
data = AWWIncentiveReportMonthly.objects.filter(
month=self.month,
block_id=self.location).order_by('-supervisor_name',
'awc_name')
data = data.values('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name', 'aww_name',
'contact_phone_number', 'wer_weighed',
'wer_eligible', 'awc_num_open', 'valid_visits',
'expected_visits', 'is_launched')
if self.beta:
headers = [
'State', 'District', 'Block', 'Supervisor', 'AWC', 'AWW Name',
'AWW Contact Number', 'Home Visits Conducted',
'Weighing Efficiency', 'AWW Eligible for Incentive',
'Number of Days AWC was Open', 'AWH Eligible for Incentive'
]
else:
headers = [
'State', 'District', 'Block', 'Supervisor', 'AWC', 'AWW Name',
'AWW Contact Number', 'Home Visits Conducted',
'Number of Days AWC was Open', 'Weighing Efficiency',
'Eligible for Incentive'
]
excel_rows = [headers]
for row in data:
row_data = [
row['state_name'],
row['district_name'],
row['block_name'],
row['supervisor_name'],
row['awc_name'],
]
# AWC not launched
if row['is_launched'] != 'yes':
AWC_NOT_LAUNCHED = 'AWC not launched'
row_data.extend([
AWC_NOT_LAUNCHED, AWC_NOT_LAUNCHED, AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED, AWC_NOT_LAUNCHED, AWC_NOT_LAUNCHED
])
if self.beta:
row_data.append(AWC_NOT_LAUNCHED)
else:
if self.month.year < 2019 or (self.month.year == 2019
and self.month.month < 3):
func = int
else:
func = round
home_visit_percent = row['valid_visits'] / func(row['expected_visits']) if \
func(row['expected_visits']) else 1
weighing_efficiency_percent = row['wer_weighed'] / row['wer_eligible'] if \
row['wer_eligible'] else 1
if home_visit_percent > 1:
home_visit_percent = 1
home_visit_conducted = '{:.2%}'.format(home_visit_percent)
if row['awc_num_open'] is None:
num_open = DATA_NOT_ENTERED
awh_eligible_for_incentive = 'No'
else:
num_open = row['awc_num_open']
awh_eligible_for_incentive = 'Yes' if int(
num_open) >= 21 else 'No'
weighing_efficiency = '{:.2%}'.format(
weighing_efficiency_percent)
eligible_for_incentive = 'Yes' if \
weighing_efficiency_percent >= 0.6 and home_visit_percent >= 0.6 else 'No'
no_visits = row['valid_visits'] == 0 and row[
'expected_visits'] == 0
no_weights = row['wer_eligible'] == 0
if no_visits:
home_visit_conducted = "No expected home visits"
if no_weights:
weighing_efficiency = "No expected weight measurement"
if no_visits and no_weights:
eligible_for_incentive = "Yes"
if self.beta:
row_data.extend([
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(
row['contact_phone_number']), home_visit_conducted,
weighing_efficiency, eligible_for_incentive, num_open,
awh_eligible_for_incentive
])
else:
row_data.extend([
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(
row['contact_phone_number']), home_visit_conducted,
num_open, weighing_efficiency, eligible_for_incentive
])
excel_rows.append(row_data)
return [
['AWW Performance Report', excel_rows],
[
'Export Info',
[['Generated at', india_now()], ['Grouped By', 'AWC'],
['Month', self.month.month], ['Year', self.month.year],
[
'Disclaimer',
"The information in the report is based on the self-reported data entered by "
"the Anganwadi Worker in ICDS-CAS mobile application and is subject to timely "
"data syncs."
]]
]
]
def get_excel_data(self):
def _format_infrastructure_data(data):
return data if data is not None else DATA_NOT_ENTERED
filters = {"month": self.month, "aggregation_level": 5}
if self.loc_level == 4:
filters['supervisor_id'] = self.location
order_by = ('awc_name', )
elif self.loc_level == 3:
filters['block_id'] = self.location
order_by = ('supervisor_name', 'awc_name')
elif self.loc_level == 2:
filters['district_id'] = self.location
order_by = ('block_name', 'supervisor_name', 'awc_name')
elif self.loc_level == 1:
filters['state_id'] = self.location
order_by = ('district_name', 'block_name', 'supervisor_name',
'awc_name')
else:
order_by = ('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name')
query_set = TakeHomeRationMonthly.objects.filter(**filters).order_by(
*order_by)
data = query_set.values('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name', 'aww_name',
'contact_phone_number', 'is_launched',
'total_thr_candidates', 'thr_given_21_days',
'thr_distribution_image_count')
headers = [
'State', 'District', 'Block', 'Sector', 'Awc Name', 'AWW Name',
'AWW Phone No.', 'Total No. of Beneficiaries eligible for THR',
'Total No. of Beneficiaries received THR>21 days in given month',
'Total No of Pictures taken by AWW'
]
excel_rows = [headers]
for row in data:
row_data = [
row['state_name'],
row['district_name'],
row['block_name'],
row['supervisor_name'],
row['awc_name'],
]
if row['is_launched'] != 'yes':
AWC_NOT_LAUNCHED = 'AWC Not Launched'
row_data.extend([
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
AWC_NOT_LAUNCHED,
])
else:
row_data.extend([
_format_infrastructure_data(row['aww_name']),
_format_infrastructure_data(row['contact_phone_number']),
_format_infrastructure_data(row['total_thr_candidates']),
_format_infrastructure_data(row['thr_given_21_days']),
_format_infrastructure_data(
row['thr_distribution_image_count'])
])
excel_rows.append(row_data)
filters = [['Generated at', india_now()]]
if self.location:
locs = SQLLocation.objects.get(
location_id=self.location).get_ancestors(include_self=True)
for loc in locs:
filters.append([loc.location_type.name.title(), loc.name])
else:
filters.append(['Location', 'National'])
date = self.month
filters.append(['Month', date.strftime("%B")])
filters.append(['Year', date.year])
return [[self.title, excel_rows], ['Export Info', filters]]
def get_excel_data(self):
# cells under all location columns, these will be blank
location_column_list = [''] * self.config['aggregation_level']
# cells to contain actual grand total
actual_value_columns_list = [0] * (
len(self.headers_and_calculation) - self.config['aggregation_level']
)
total_sum_row = location_column_list + actual_value_columns_list
total_sum_row[0] = 'Grand Total'
def evaulate_value(row, headers_with_columns):
row_data = []
for index, header in enumerate(headers_with_columns):
if len(header) == 2:
if header[1] in (
'num_awcs_conducted_cbe',
'num_awcs_conducted_vhnd'
) and self.config['aggregation_level'] == 5:
row_data.append('Yes' if row[header[1]] == 1 else 'No')
total_sum_row[index] = 'N/A'
else:
if header[1] not in self.location_columns:
total_sum_row[index] += row[header[1]]
row_data.append(row[header[1]])
else:
if row[header[2]]:
percentage = row[header[1]]/row[header[2]] * 100
else:
percentage = 0
row_data.append("{}%".format("%.2f" % percentage))
if total_sum_row[index - 1]:
percentage = (total_sum_row[index - 2] / total_sum_row[index - 1]) * 100
else:
percentage = 0
total_sum_row[index] = "{}%".format("%.2f" % percentage)
return row_data
filters = copy.deepcopy(self.config)
del filters['beneficiary_category']
del filters['domain']
order_by = self.location_columns
values = {header[1] for header in self.headers_and_calculation if len(header) == 2}
headers = [header[0] for header in self.headers_and_calculation]
data = ServiceDeliveryReportView.objects.filter(**filters).order_by(*order_by).values(*values)
data = apply_exclude(self.config['domain'], data)
excel_rows = [headers]
for row in data:
excel_rows.append(evaulate_value(row, self.headers_and_calculation))
excel_rows.append(total_sum_row)
filters = [['Generated at', india_now()]]
if self.location:
locs = SQLLocation.objects.get(location_id=self.location).get_ancestors(include_self=True)
for loc in locs:
filters.append([loc.location_type.name.title(), loc.name])
else:
filters.append(['Location', 'National'])
date = self.config['month']
filters.append(['Month', date.strftime("%B")])
filters.append(['Year', date.year])
return [
[
self.title,
excel_rows
],
[
'Export Info',
filters
]
]
def get_excel_data(self):
excel_rows = []
filters = [['Generated at', india_now()]]
end_date = datetime.datetime.utcnow()
start_date = end_date - datetime.timedelta(days=7)
headers = [
'Sr.No', 'State/UT Name', 'District Name', 'Block Name',
'Username', 'Level', 'Role', 'Launched?', 'Last Login',
'Logged in the last week?', 'Total', 'Child', 'Pregnant Women',
'Demographics', 'System Usage', 'AWC infrastructure',
'Child Growth Monitoring List', 'ICDS - CAS Monthly Register',
'AWW Performance Report', 'LS Performance Report',
'Take Home Ration'
]
serial_count = 0
logged_in_user_locations = list(
self.user.get_sql_locations(self.domain))
if not logged_in_user_locations:
self.national_user = True
loop_counter = 0
# Need to fetch all users for a national user
while (self.national_user and loop_counter < 1
) or len(logged_in_user_locations) > loop_counter:
# getting the location types to retrieve for this user location
location_type_filter = {'aggregation_level': 5}
if not self.national_user:
user_location = logged_in_user_locations[loop_counter]
user_location_type_name = \
self.get_location_id_string_from_location_type(user_location.location_type_name)
location_type_filter[
user_location_type_name] = user_location.get_id
else:
user_location_type_name = None
for test_location in self.location_test_fields:
location_type_filter[test_location] = 0
all_awc_locations = AwcLocation.objects.filter(
**location_type_filter).values(*self.required_fields)
# converting the result set to matrix to fetch ancestors for a given location
location_matrix, location_ids =\
self.convert_rs_to_matrix(all_awc_locations, user_location_type_name)
users = self.get_users_by_location(location_ids)
dashboard_uname_rx = re.compile(r'^\d*\.[a-zA-Z]*@.*')
usernames = [
user['username'] for user in users
if dashboard_uname_rx.match(user['username'])
]
records = list(
ICDSAuditEntryRecord.objects.filter(
url='/a/{}/icds_export_indicator'.format(self.domain),
username__in=usernames,
time_of_use__gte=start_date,
time_of_use__lt=end_date).annotate(
indicator=KeyTextTransform('indicator', 'post_data')).
values('indicator',
'username').annotate(count=Count('indicator')).order_by(
'username', 'indicator'))
if self.national_user:
self.prepare_is_launched_agg_list()
else:
self.prepare_is_launched_agg_list(
user_location.location_type_name, user_location.get_id)
user_counts = defaultdict(int)
user_indicators = defaultdict(lambda: [0] * 10)
for record in records:
# ignoring the dashboard usage report
if int(record['indicator']) < 11:
user_counts[record['username']] += record['count']
# updating the counts as per the index which is the indicator number
user_indicators[record['username']][
int(record['indicator']) - 1] = record['count']
# accumulating the indicator counts
for user in users:
if not dashboard_uname_rx.match(user['username']):
continue
indicator_count = user_indicators[user['username']]
user_sql_location_ids = user['assigned_location_ids']
if isinstance(user_sql_location_ids, str):
user_sql_location_ids = [user_sql_location_ids]
for user_sql_location in user_sql_location_ids:
# getting the location type to look up in matrix
if user_sql_location not in self.sql_locations:
continue
location_type_id = self.sql_locations[user_sql_location]
column_index = self.location_types.index(location_type_id)
user_location_row = None
# iterating and getting the db row from matrix
for row in location_matrix:
if row[2 * column_index] == user_sql_location:
user_location_row = copy.deepcopy(row)
break
user_location_type = self.get_location_type_string_from_location_id(
location_type_id)
if user_location_row is not None:
if user_location_type == 'state':
user_location_row[3] = ''
user_location_row[5] = ''
if user_location_type == 'district':
user_location_row[5] = ''
last_login, logged_in_last_week = self.check_if_date_in_last_week(
user['last_login'])
excel = [
serial_count, user_location_row[1],
user_location_row[3], user_location_row[5],
user['username'].split('@')[0], user_location_type,
self.get_role_from_username(user['username']),
self.convert_boolean_to_string(
self.agg_list[user_sql_location]), last_login,
self.convert_boolean_to_string(
logged_in_last_week),
user_counts[user['username']]
]
excel.extend(indicator_count)
excel_rows.append(excel)
loop_counter += 1
excel_rows = sorted(excel_rows, key=lambda x: (x[1], x[2], x[3]))
excel_rows.insert(0, headers)
return [[self.title, excel_rows], ['Export Info', filters]]
def get_excel_data(self):
excel_rows = []
filters = [['Generated at', india_now()]]
headers = [
'Sr.No', 'State/UT Name', 'District Name', 'Block Name',
'Username', 'Level', 'Role', 'Launched?', 'Last Activity ',
'Activity in the last 7 days?'
]
serial_count = 0
location_type_filter = {'aggregation_level': 3}
if not self.is_national_user:
user_location = self.user.get_sql_location(self.domain)
user_loc_id_key = \
self.get_location_id_string_from_location_type(user_location.location_type_name)
location_type_filter[user_loc_id_key] = user_location.get_id
else:
user_loc_id_key = None
for test_location in self.location_test_fields:
location_type_filter[test_location] = 0
all_awc_locations = AwcLocation.objects.filter(
**location_type_filter).values(*self.required_fields)
self.populate_location_id_vs_name_mapping(all_awc_locations,
user_loc_id_key)
date = datetime.datetime.now()
usage_data = []
dashboard_filters = {}
if not self.is_national_user:
# retrieving the user_level from logged in user location type
user_level = self.user_levels.index(
user_loc_id_key.replace('_id', '')) + 1
dashboard_filters['user_level__gt'] = user_level
dashboard_filters[user_loc_id_key] = user_location.get_id
# keep the record in searched - current - month
while not usage_data:
usage_data = self.get_data_for_usage_report(
date, dashboard_filters)
date -= relativedelta(days=1)
for record in usage_data:
user_activity = record.last_activity if record.location_launched else None
last_activity, activity_in_last_week = self.check_if_date_in_last_week(
user_activity)
state_name = self.get_location_name_from_id(record.state_id)
district_name = self.get_location_name_from_id(record.district_id)
block_name = self.get_location_name_from_id(record.block_id)
excel = [
serial_count, state_name, district_name, block_name,
record.username.split('@')[0],
self.user_levels[record.user_level - 1],
self.get_role_from_username(record.username),
self.convert_boolean_to_string(record.location_launched),
last_activity,
self.convert_boolean_to_string(activity_in_last_week)
]
excel_rows.append(excel)
excel_rows = sorted(excel_rows, key=lambda x: (x[1], x[2], x[3]))
# appending serial numbers
for i in range(len(excel_rows)):
serial_count += 1
excel_rows[i][0] = serial_count
excel_rows.insert(0, headers)
return [[self.title, excel_rows], ['Export Info', filters]]
