def create_excel_file(excel_data, data_type, file_format):
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
export_from_tables(excel_data, export_file, file_format)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def zip_folder(pdf_files):
zip_hash = uuid.uuid4().hex
icds_file = IcdsFile(blob_id=zip_hash, data_type='issnip_monthly')
in_memory = BytesIO()
zip_file = zipfile.ZipFile(in_memory, 'w', zipfile.ZIP_DEFLATED)
files_to_zip = IcdsFile.objects.filter(blob_id__in=list(pdf_files.keys()), data_type='issnip_monthly')
for pdf_file in files_to_zip:
zip_file.writestr(
'ICDS_CAS_monthly_register_{}.pdf'.format(pdf_files[pdf_file.blob_id]),
pdf_file.get_file_from_blobdb().read()
)
zip_file.close()
# we need to reset buffer position to the beginning after creating zip, if not read() will return empty string
# we read this to save file in blobdb
in_memory.seek(0)
icds_file.store_file_in_blobdb(in_memory, expired=60 * 60 * 24)
icds_file.save()
return zip_hash
def create_pdf_file(pdf_context):
pdf_hash = uuid.uuid4().hex
template = get_template("icds_reports/icds_app/pdf/issnip_monthly_register.html")
resultFile = BytesIO()
icds_file = IcdsFile(blob_id=pdf_hash, data_type='issnip_monthly')
try:
pdf_page = template.render(pdf_context)
except Exception as ex:
pdf_page = str(ex)
pisa.CreatePDF(
pdf_page,
dest=resultFile,
show_error_as_pdf=True)
# we need to reset buffer position to the beginning after creating pdf, if not read() will return empty string
# we read this to save file in blobdb
resultFile.seek(0)
icds_file.store_file_in_blobdb(resultFile, expired=60 * 60 * 24)
icds_file.save()
return pdf_hash
def collect_inactive_awws():
celery_task_logger.info("Started updating the Inactive AWW")
filename = "inactive_awws_%s.csv" % date.today().strftime('%Y-%m-%d')
last_sync = IcdsFile.objects.filter(
data_type='inactive_awws').order_by('-file_added').first()
# If last sync not exist then collect initial data
if not last_sync:
last_sync_date = datetime(2017, 3, 1).date()
else:
last_sync_date = last_sync.file_added
_aggregate_inactive_aww(last_sync_date)
celery_task_logger.info("Collecting inactive AWW to generate zip file")
excel_data = AggregateInactiveAWW.objects.all()
celery_task_logger.info("Preparing data to csv file")
columns = [x.name for x in AggregateInactiveAWW._meta.fields
] + ['days_since_start', 'days_inactive']
rows = [columns]
for data in excel_data:
rows.append([_get_value(data, field) for field in columns])
celery_task_logger.info("Creating csv file")
export_file = BytesIO()
export_from_tables([['inactive AWWSs', rows]], export_file, 'csv')
celery_task_logger.info("Saving csv file in blobdb")
sync = IcdsFile(blob_id=filename, data_type='inactive_awws')
sync.store_file_in_blobdb(export_file)
sync.save()
celery_task_logger.info("Ended updating the Inactive AWW")
def create_pdf_file(pdf_context):
from weasyprint import HTML, CSS
pdf_hash = uuid.uuid4().hex
template = get_template("icds_reports/icds_app/pdf/issnip_monthly_register.html")
resultFile = BytesIO()
icds_file = IcdsFile(blob_id=pdf_hash, data_type='issnip_monthly')
try:
pdf_page = template.render(pdf_context)
except Exception as ex:
pdf_page = str(ex)
base_url = os.path.join(settings.FILEPATH, 'custom', 'icds_reports', 'static')
resultFile.write(HTML(string=pdf_page, base_url=base_url).write_pdf(
stylesheets=[CSS(os.path.join(base_url, 'css', 'issnip_monthly_print_style.css')), ])
)
# we need to reset buffer position to the beginning after creating pdf, if not read() will return empty string
# we read this to save file in blobdb
resultFile.seek(0)
icds_file.store_file_in_blobdb(resultFile, expired=60 * 60 * 24)
icds_file.save()
return pdf_hash
def create_excel_file_in_openpyxl(excel_data, data_type):
workbook = Workbook()
first_worksheet = True
for worksheet_data in excel_data:
if first_worksheet:
worksheet = workbook.active
worksheet.title = worksheet_data[0]
first_worksheet = False
else:
worksheet = workbook.create_sheet(worksheet_data[0])
for row_number, row_data in enumerate(worksheet_data[1], start=1):
for column_number, cell_data in enumerate(row_data, start=1):
worksheet.cell(row=row_number, column=column_number).value = cell_data
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_excel_file(excel_data, data_type, file_format):
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
export_from_tables(excel_data, export_file, file_format)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def collect_inactive_awws():
celery_task_logger.info("Started updating the Inactive AWW")
filename = "inactive_awws_%s.csv" % date.today().strftime('%Y-%m-%d')
last_sync = IcdsFile.objects.filter(data_type='inactive_awws').order_by('-file_added').first()
# If last sync not exist then collect initial data
if not last_sync:
last_sync_date = datetime(2017, 3, 1).date()
else:
last_sync_date = last_sync.file_added
_aggregate_inactive_aww(last_sync_date)
celery_task_logger.info("Collecting inactive AWW to generate zip file")
excel_data = AggregateInactiveAWW.objects.all()
celery_task_logger.info("Preparing data to csv file")
columns = [x.name for x in AggregateInactiveAWW._meta.fields] + [
'days_since_start',
'days_inactive'
]
rows = [columns]
for data in excel_data:
rows.append(
[_get_value(data, field) for field in columns]
)
celery_task_logger.info("Creating csv file")
export_file = BytesIO()
export_from_tables([['inactive AWWSs', rows]], export_file, 'csv')
celery_task_logger.info("Saving csv file in blobdb")
sync = IcdsFile(blob_id=filename, data_type='inactive_awws')
sync.store_file_in_blobdb(export_file)
sync.save()
celery_task_logger.info("Ended updating the Inactive AWW")
def _save_icds_file(query):
location_data = io.BytesIO()
copy_query = "COPY ({query}) TO STDOUT WITH CSV HEADER".format(
query=query)
cursor.cursor.copy_expert(copy_query, location_data)
icds_file = IcdsFile(blob_id=uuid.uuid4().hex,
data_type='location_change')
location_data.seek(0)
icds_file.store_file_in_blobdb(location_data)
icds_file.save()
return icds_file
def create_pdf_file(pdf_context):
pdf_hash = uuid.uuid4().hex
template = get_template(
"icds_reports/icds_app/pdf/issnip_monthly_register.html")
resultFile = BytesIO()
icds_file = IcdsFile(blob_id=pdf_hash, data_type='issnip_monthly')
try:
pdf_page = template.render(pdf_context)
except Exception as ex:
pdf_page = str(ex)
pisa.CreatePDF(pdf_page, dest=resultFile, show_error_as_pdf=True)
# we need to reset buffer position to the beginning after creating pdf, if not read() will return empty string
# we read this to save file in blobdb
resultFile.seek(0)
icds_file.store_file_in_blobdb(resultFile, expired=60 * 60 * 24)
icds_file.save()
return pdf_hash
def zip_folder(pdf_files):
zip_hash = uuid.uuid4().hex
icds_file = IcdsFile(blob_id=zip_hash, data_type='issnip_monthly')
in_memory = BytesIO()
zip_file = zipfile.ZipFile(in_memory, 'w', zipfile.ZIP_DEFLATED)
files_to_zip = IcdsFile.objects.filter(blob_id__in=list(pdf_files.keys()), data_type='issnip_monthly')
for pdf_file in files_to_zip:
zip_file.writestr(
'ICDS_CAS_monthly_register_{}.pdf'.format(pdf_files[pdf_file.blob_id]),
pdf_file.get_file_from_blobdb().read()
)
zip_file.close()
# we need to reset buffer position to the beginning after creating zip, if not read() will return empty string
# we read this to save file in blobdb
in_memory.seek(0)
icds_file.store_file_in_blobdb(in_memory, expired=60 * 60 * 24)
icds_file.save()
return zip_hash
def create_pdf_file(pdf_context):
pdf_hash = uuid.uuid4().hex
template = get_template("icds_reports/icds_app/pdf/issnip_monthly_register.html")
resultFile = BytesIO()
icds_file = IcdsFile(blob_id=pdf_hash, data_type='issnip_monthly')
try:
pdf_page = template.render(pdf_context)
except Exception as ex:
pdf_page = str(ex)
base_url = os.path.join(settings.FILEPATH, 'custom', 'icds_reports', 'static')
resultFile.write(HTML(string=pdf_page, base_url=base_url).write_pdf(
stylesheets=[CSS(os.path.join(base_url, 'css', 'issnip_monthly_print_style.css')), ])
)
# we need to reset buffer position to the beginning after creating pdf, if not read() will return empty string
# we read this to save file in blobdb
resultFile.seek(0)
icds_file.store_file_in_blobdb(resultFile, expired=ONE_DAY)
icds_file.save()
return pdf_hash
def collect_inactive_dashboard_users():
celery_task_logger.info("Started updating the Inactive Dashboard users")
end_date = datetime.utcnow()
start_date_week = end_date - timedelta(days=7)
start_date_month = end_date - timedelta(days=30)
not_logged_in_week = get_dashboard_users_not_logged_in(
start_date_week, end_date)
not_logged_in_month = get_dashboard_users_not_logged_in(
start_date_month, end_date)
week_file_name = 'dashboard_users_not_logged_in_{:%Y-%m-%d}_to_{:%Y-%m-%d}.csv'.format(
start_date_week, end_date)
month_file_name = 'dashboard_users_not_logged_in_{:%Y-%m-%d}_to_{:%Y-%m-%d}.csv'.format(
start_date_month, end_date)
rows_not_logged_in_week = _get_inactive_dashboard_user_rows(
not_logged_in_week)
rows_not_logged_in_month = _get_inactive_dashboard_user_rows(
not_logged_in_month)
sync = IcdsFile(blob_id="inactive_dashboad_users_%s.zip" %
date.today().strftime('%Y-%m-%d'),
data_type='inactive_dashboard_users')
in_memory = BytesIO()
zip_file = zipfile.ZipFile(in_memory, 'w', zipfile.ZIP_DEFLATED)
zip_file.writestr(week_file_name, '\n'.join(rows_not_logged_in_week))
zip_file.writestr(month_file_name, '\n'.join(rows_not_logged_in_month))
zip_file.close()
# we need to reset buffer position to the beginning after creating zip, if not read() will return empty string
# we read this to save file in blobdb
in_memory.seek(0)
sync.store_file_in_blobdb(in_memory)
sync.save()
def create_excel_file_in_openpyxl(excel_data, data_type):
workbook = Workbook()
first_worksheet = True
for worksheet_data in excel_data:
if first_worksheet:
worksheet = workbook.active
worksheet.title = worksheet_data[0]
first_worksheet = False
else:
worksheet = workbook.create_sheet(worksheet_data[0])
for row_number, row_data in enumerate(worksheet_data[1], start=1):
for column_number, cell_data in enumerate(row_data, start=1):
worksheet.cell(row=row_number, column=column_number).value = cell_data
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_lady_supervisor_excel_file(excel_data, data_type, month, aggregation_level):
export_info = excel_data[1][1]
state = export_info[1][1] if aggregation_level > 0 else ''
district = export_info[2][1] if aggregation_level > 1 else ''
block = export_info[3][1] if aggregation_level > 2 else ''
excel_data = [line[aggregation_level:] for line in excel_data[0][1]]
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "LS Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
amount_of_columns = 9 - aggregation_level
last_column = string.ascii_uppercase[amount_of_columns]
worksheet.merge_cells('B2:{0}2'.format(last_column))
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "Lady Supervisor Performance Report for the {}".format(month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
columns = [string.ascii_uppercase[i] for i in range(1, amount_of_columns + 1)]
# sheet header
header_cells = ['{0}3'.format(column) for column in columns]
for cell in header_cells:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
if state:
worksheet['B3'].value = "State: {}".format(state)
worksheet.merge_cells('B3:C3')
if district:
worksheet['D3'].value = "District: {}".format(district)
if block:
worksheet['E3'].value = "Block: {}".format(block)
date_cell = '{0}3'.format(last_column)
date_description_cell = '{0}3'.format(string.ascii_uppercase[amount_of_columns - 1])
worksheet[date_description_cell].value = "Date when downloaded:"
worksheet[date_description_cell].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet[date_cell].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet[date_cell].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
header_data = excel_data[0]
headers = ["S.No"]
headers.extend(header_data)
table_header = {}
for col, header in zip(columns, headers):
table_header[col] = header
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {}
widths_columns = ['A']
widths_columns.extend(columns)
standard_widths = [4, 7]
standard_widths.extend([15] * (4 - aggregation_level))
standard_widths.extend([25, 15, 25, 15])
for col, width in zip(widths_columns, standard_widths):
widths[col] = width
widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0)
widths['D'] = 9 + (len(district) * 4 // 3 if district else 0)
widths['E'] = 8 + (len(block) * 4 // 3 if district else 0)
columns = columns[1:]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(row[column_index], bytes)
else six.text_type(row[column_index])
)
for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0
)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
for n, export_info_item in enumerate(export_info, start=1):
worksheet2['A{0}'.format(n)].value = export_info_item[0]
worksheet2['B{0}'.format(n)].value = export_info_item[1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_aww_performance_excel_file(excel_data, data_type, month, state, district=None, block=None, beta=False):
aggregation_level = 3 if block else (2 if district else 1)
export_info = excel_data[1][1]
excel_data = [line[aggregation_level:] for line in excel_data[0][1]]
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "AWW Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
if beta:
worksheet.merge_cells('B2:{0}2'.format(
"K" if aggregation_level == 3 else ("L" if aggregation_level == 2 else "M")
))
else:
worksheet.merge_cells('B2:{0}2'.format(
"J" if aggregation_level == 3 else ("K" if aggregation_level == 2 else "L")
))
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "AWW Performance Report for the month of {}".format(month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
# sheet header
if beta:
header_cells = ["B3", "C3", "D3", "E3", "F3", "G3", "H3", "I3", "J3", "K3"]
if aggregation_level < 3:
header_cells.append("L3")
if aggregation_level < 2:
header_cells.append("M3")
date_description_cell_start = "I3" if aggregation_level == 3 else \
("J3" if aggregation_level == 2 else "K3")
date_description_cell_finish = "J3" if aggregation_level == 3 else \
("K3" if aggregation_level == 2 else "L3")
date_column = "K3" if aggregation_level == 3 else ("L3" if aggregation_level == 2 else "M3")
else:
header_cells = ["B3", "C3", "D3", "E3", "F3", "G3", "H3", "I3", "J3"]
if aggregation_level < 3:
header_cells.append("K3")
if aggregation_level < 2:
header_cells.append("L3")
date_description_cell_start = "H3" if aggregation_level == 3 else \
("I3" if aggregation_level == 2 else "J3")
date_description_cell_finish = "I3" if aggregation_level == 3 else \
("J3" if aggregation_level == 2 else "K3")
date_column = "J3" if aggregation_level == 3 else ("K3" if aggregation_level == 2 else "L3")
for cell in header_cells:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet.merge_cells('B3:C3')
worksheet['B3'].value = "State: {}".format(state)
if district:
worksheet['D3'].value = "District: {}".format(district)
worksheet.merge_cells('E3:F3')
if block:
worksheet['E3'].value = "Block: {}".format(block)
worksheet.merge_cells('{0}:{1}'.format(
date_description_cell_start,
date_description_cell_finish,
))
worksheet[date_description_cell_start].value = "Date when downloaded:"
worksheet[date_description_cell_start].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet[date_column].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet[date_column].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
headers = ["S.No"]
if aggregation_level < 2:
headers.append("District")
if aggregation_level < 3:
headers.append("Block")
if beta:
headers.extend([
'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'
])
columns = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K']
if aggregation_level < 3:
columns.append('L')
if aggregation_level < 2:
columns.append('M')
else:
headers.extend(["Supervisor", "AWC", "AWW Name", "AWW Contact Number", "Home Visits Conducted",
"Number of Days AWC was Open", "Weighing Efficiency", "Eligible for Incentive"])
columns = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
if aggregation_level < 3:
columns.append('K')
if aggregation_level < 2:
columns.append('L')
table_header = {}
for col, header in zip(columns, headers):
table_header[col] = header
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {}
widths_columns = ['A']
widths_columns.extend(columns)
standard_widths = [4, 7, 15]
standard_widths.extend([15] * (3 - aggregation_level))
standard_widths.extend([13, 12, 13, 15, 11, 14, 14])
if beta:
standard_widths.append(14)
for col, width in zip(widths_columns, standard_widths):
widths[col] = width
widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0)
widths['D'] = 13 + (len(district) * 4 // 3 if district else 0)
widths['F'] = max(widths['F'], len(block) * 4 // 3 if block else 0)
for column in ["C", "E", "G"]:
if widths[column] > 25:
worksheet.row_dimensions[3].height = max(
16 * ((widths[column] // 25) + 1),
worksheet.row_dimensions[3].height
)
widths[column] = 25
columns = columns[1:]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(row[column_index], bytes)
else six.text_type(row[column_index])
)
for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0
)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
for n, export_info_item in enumerate(export_info, start=1):
worksheet2['A{0}'.format(n)].value = export_info_item[0]
worksheet2['B{0}'.format(n)].value = export_info_item[1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_lady_supervisor_excel_file(excel_data, data_type, month, aggregation_level):
export_info = excel_data[1][1]
state = export_info[1][1] if aggregation_level > 0 else ''
district = export_info[2][1] if aggregation_level > 1 else ''
block = export_info[3][1] if aggregation_level > 2 else ''
excel_data = [line[aggregation_level:] for line in excel_data[0][1]]
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "LS Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
amount_of_columns = 9 - aggregation_level
last_column = string.ascii_uppercase[amount_of_columns]
worksheet.merge_cells('B2:{0}2'.format(last_column))
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "Lady Supervisor Performance Report for the {}".format(month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
columns = [string.ascii_uppercase[i] for i in range(1, amount_of_columns + 1)]
# sheet header
header_cells = ['{0}3'.format(column) for column in columns]
for cell in header_cells:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
if state:
worksheet['B3'].value = "State: {}".format(state)
worksheet.merge_cells('B3:C3')
if district:
worksheet['D3'].value = "District: {}".format(district)
if block:
worksheet['E3'].value = "Block: {}".format(block)
date_cell = '{0}3'.format(last_column)
date_description_cell = '{0}3'.format(string.ascii_uppercase[amount_of_columns - 1])
worksheet[date_description_cell].value = "Date when downloaded:"
worksheet[date_description_cell].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet[date_cell].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet[date_cell].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
header_data = excel_data[0]
headers = ["S.No"]
headers.extend(header_data)
table_header = {}
for col, header in zip(columns, headers):
table_header[col] = header
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {}
widths_columns = ['A']
widths_columns.extend(columns)
standard_widths = [4, 7]
standard_widths.extend([15] * (4 - aggregation_level))
standard_widths.extend([25, 15, 25, 15])
for col, width in zip(widths_columns, standard_widths):
widths[col] = width
widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0)
widths['D'] = 9 + (len(district) * 4 // 3 if district else 0)
widths['E'] = 8 + (len(block) * 4 // 3 if district else 0)
columns = columns[1:]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(row[column_index], bytes)
else six.text_type(row[column_index])
)
for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0
)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
for n, export_info_item in enumerate(export_info, start=1):
worksheet2['A{0}'.format(n)].value = export_info_item[0]
worksheet2['B{0}'.format(n)].value = export_info_item[1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_aww_performance_excel_file(excel_data, data_type, month, state, district, block):
export_info = excel_data[1][1]
excel_data = [line[3:] for line in excel_data[0][1]]
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "AWW Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
worksheet.merge_cells('B2:J2')
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "AWW Performance Report for the month of {}".format(month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
# sheet header
for cell in {"B3", "C3", "D3", "E3", "F3", "G3", "H3", "J3"}:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet.merge_cells('B3:C3')
worksheet['B3'].value = "State: {}".format(state)
worksheet['D3'].value = "District: {}".format(district)
worksheet.merge_cells('E3:F3')
worksheet['E3'].value = "Block: {}".format(block)
worksheet.merge_cells('H3:I3')
worksheet['H3'].value = "Date when downloaded:"
worksheet['H3'].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet['J3'].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet['J3'].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
table_header = {
'B': "S.No",
'C': "Supervisor",
'D': "AWC",
'E': "AWW Name",
'F': "AWW Contact Number",
'G': "Home Visits Conducted",
'H': "Number of Days AWC was Open",
'I': "Weighing Efficiency",
'J': "Eligible for Incentive",
}
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
columns = ["B", "C", "D", "E", "F", "G", "H", "I", "J"]
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {
'A': 4,
'B': 7,
'C': max(15, len(state) * 4 // 3),
'D': 13 + (len(district) * 4 // 3),
'E': 12,
'F': max(13, len(block) * 4 // 3),
'G': 15,
'H': 11,
'I': 14,
'J': 14,
}
for column in ["C", "E", "G"]:
if widths[column] > 25:
worksheet.row_dimensions[3].height = max(
16 * ((widths[column] // 25) + 1),
worksheet.row_dimensions[3].height
)
widths[column] = 25
columns = ["C", "D", "E", "F", "G", "H", "I", "J"]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(row[column_index], bytes)
else six.text_type(row[column_index])
)
for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0
)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
worksheet2['A1'].value = export_info[0][0]
worksheet2['B1'].value = export_info[0][1]
worksheet2['A2'].value = export_info[1][0]
worksheet2['B2'].value = export_info[1][1]
worksheet2['A3'].value = export_info[2][0]
worksheet2['B3'].value = export_info[2][1]
worksheet2['A4'].value = export_info[3][0]
worksheet2['B4'].value = export_info[3][1]
worksheet2['A4'].value = export_info[4][0]
worksheet2['B4'].value = export_info[4][1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_aww_performance_excel_file(excel_data, data_type, month, state, district=None, block=None, beta=False):
aggregation_level = 3 if block else (2 if district else 1)
export_info = excel_data[1][1]
excel_data = [line[aggregation_level:] for line in excel_data[0][1]]
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "AWW Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
if beta:
worksheet.merge_cells('B2:{0}2'.format(
"K" if aggregation_level == 3 else ("L" if aggregation_level == 2 else "M")
))
else:
worksheet.merge_cells('B2:{0}2'.format(
"J" if aggregation_level == 3 else ("K" if aggregation_level == 2 else "L")
))
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "AWW Performance Report for the month of {}".format(month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
# sheet header
if beta:
header_cells = ["B3", "C3", "D3", "E3", "F3", "G3", "H3", "I3", "J3", "K3"]
if aggregation_level < 3:
header_cells.append("L3")
if aggregation_level < 2:
header_cells.append("M3")
date_description_cell_start = "I3" if aggregation_level == 3 else \
("J3" if aggregation_level == 2 else "K3")
date_description_cell_finish = "J3" if aggregation_level == 3 else \
("K3" if aggregation_level == 2 else "L3")
date_column = "K3" if aggregation_level == 3 else ("L3" if aggregation_level == 2 else "M3")
else:
header_cells = ["B3", "C3", "D3", "E3", "F3", "G3", "H3", "I3", "J3"]
if aggregation_level < 3:
header_cells.append("K3")
if aggregation_level < 2:
header_cells.append("L3")
date_description_cell_start = "H3" if aggregation_level == 3 else \
("I3" if aggregation_level == 2 else "J3")
date_description_cell_finish = "I3" if aggregation_level == 3 else \
("J3" if aggregation_level == 2 else "K3")
date_column = "J3" if aggregation_level == 3 else ("K3" if aggregation_level == 2 else "L3")
for cell in header_cells:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet.merge_cells('B3:C3')
worksheet['B3'].value = "State: {}".format(state)
if district:
worksheet['D3'].value = "District: {}".format(district)
worksheet.merge_cells('E3:F3')
if block:
worksheet['E3'].value = "Block: {}".format(block)
worksheet.merge_cells('{0}:{1}'.format(
date_description_cell_start,
date_description_cell_finish,
))
worksheet[date_description_cell_start].value = "Date when downloaded:"
worksheet[date_description_cell_start].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet[date_column].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet[date_column].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
headers = ["S.No"]
if aggregation_level < 2:
headers.append("District")
if aggregation_level < 3:
headers.append("Block")
if beta:
headers.extend([
'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'
])
columns = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K']
if aggregation_level < 3:
columns.append('L')
if aggregation_level < 2:
columns.append('M')
else:
headers.extend(["Supervisor", "AWC", "AWW Name", "AWW Contact Number", "Home Visits Conducted",
"Number of Days AWC was Open", "Weighing Efficiency", "Eligible for Incentive"])
columns = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
if aggregation_level < 3:
columns.append('K')
if aggregation_level < 2:
columns.append('L')
table_header = {}
for col, header in zip(columns, headers):
table_header[col] = header
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {}
widths_columns = ['A']
widths_columns.extend(columns)
standard_widths = [4, 7, 15]
standard_widths.extend([15] * (3 - aggregation_level))
standard_widths.extend([13, 12, 13, 15, 11, 14, 14])
if beta:
standard_widths.append(14)
for col, width in zip(widths_columns, standard_widths):
widths[col] = width
widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0)
widths['D'] = 13 + (len(district) * 4 // 3 if district else 0)
widths['F'] = max(widths['F'], len(block) * 4 // 3 if block else 0)
for column in ["C", "E", "G"]:
if widths[column] > 25:
worksheet.row_dimensions[3].height = max(
16 * ((widths[column] // 25) + 1),
worksheet.row_dimensions[3].height
)
widths[column] = 25
columns = columns[1:]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(row[column_index], bytes)
else six.text_type(row[column_index])
)
for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0
)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
for n, export_info_item in enumerate(export_info, start=1):
worksheet2['A{0}'.format(n)].value = export_info_item[0]
worksheet2['B{0}'.format(n)].value = export_info_item[1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
def create_aww_performance_excel_file(excel_data, data_type, month, state,
district, block):
export_info = excel_data[1][1]
excel_data = [line[3:] for line in excel_data[0][1]]
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
warp_text_alignment = Alignment(wrap_text=True)
bold_font = Font(bold=True)
blue_fill = PatternFill("solid", fgColor="B3C5E5")
grey_fill = PatternFill("solid", fgColor="BFBFBF")
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "AWW Performance Report"
worksheet.sheet_view.showGridLines = False
# sheet title
worksheet.merge_cells('B2:J2')
title_cell = worksheet['B2']
title_cell.fill = PatternFill("solid", fgColor="4472C4")
title_cell.value = "AWW Performance Report for the month of {}".format(
month)
title_cell.font = Font(size=18, color="FFFFFF")
title_cell.alignment = Alignment(horizontal="center")
# sheet header
for cell in {"B3", "C3", "D3", "E3", "F3", "G3", "H3", "J3"}:
worksheet[cell].fill = blue_fill
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet.merge_cells('B3:C3')
worksheet['B3'].value = "State: {}".format(state)
worksheet['D3'].value = "District: {}".format(district)
worksheet.merge_cells('E3:F3')
worksheet['E3'].value = "Block: {}".format(block)
worksheet.merge_cells('H3:I3')
worksheet['H3'].value = "Date when downloaded:"
worksheet['H3'].alignment = Alignment(horizontal="right")
utc_now = datetime.now(pytz.utc)
now_in_india = utc_now.astimezone(india_timezone)
worksheet['J3'].value = custom_strftime('{S} %b %Y', now_in_india)
worksheet['J3'].alignment = Alignment(horizontal="right")
# table header
table_header_position_row = 5
table_header = {
'B': "S.No",
'C': "Supervisor",
'D': "AWC",
'E': "AWW Name",
'F': "AWW Contact Number",
'G': "Home Visits Conducted",
'H': "Number of Days AWC was Open",
'I': "Weighing Efficiency",
'J': "Eligible for Incentive",
}
for column, value in table_header.items():
cell = "{}{}".format(column, table_header_position_row)
worksheet[cell].fill = grey_fill
worksheet[cell].border = thin_border
worksheet[cell].font = bold_font
worksheet[cell].alignment = warp_text_alignment
worksheet[cell].value = value
# table contents
row_position = table_header_position_row + 1
for enum, row in enumerate(excel_data[1:], start=1):
columns = ["B", "C", "D", "E", "F", "G", "H", "I", "J"]
for column_index in range(len(columns)):
column = columns[column_index]
cell = "{}{}".format(column, row_position)
worksheet[cell].border = thin_border
if column_index == 0:
worksheet[cell].value = enum
else:
worksheet[cell].value = row[column_index - 1]
row_position += 1
# sheet dimensions
title_row = worksheet.row_dimensions[2]
title_row.height = 23
worksheet.row_dimensions[table_header_position_row].height = 46
widths = {
'A': 4,
'B': 7,
'C': max(15,
len(state) * 4 // 3),
'D': 13 + (len(district) * 4 // 3),
'E': 12,
'F': max(13,
len(block) * 4 // 3),
'G': 15,
'H': 11,
'I': 14,
'J': 14,
}
for column in ["C", "E", "G"]:
if widths[column] > 25:
worksheet.row_dimensions[3].height = max(
16 * ((widths[column] // 25) + 1),
worksheet.row_dimensions[3].height)
widths[column] = 25
columns = ["C", "D", "E", "F", "G", "H", "I", "J"]
# column widths based on table contents
for column_index in range(len(columns)):
widths[columns[column_index]] = max(
widths[columns[column_index]],
max(
len(row[column_index].decode('utf-8') if isinstance(
row[column_index], bytes) else six.
text_type(row[column_index])) for row in excel_data[1:]) *
4 // 3 if len(excel_data) >= 2 else 0)
for column, width in widths.items():
worksheet.column_dimensions[column].width = width
# export info
worksheet2 = workbook.create_sheet("Export Info")
worksheet2.column_dimensions['A'].width = 14
worksheet2['A1'].value = export_info[0][0]
worksheet2['B1'].value = export_info[0][1]
worksheet2['A2'].value = export_info[1][0]
worksheet2['B2'].value = export_info[1][1]
worksheet2['A3'].value = export_info[2][0]
worksheet2['B3'].value = export_info[2][1]
worksheet2['A4'].value = export_info[3][0]
worksheet2['B4'].value = export_info[3][1]
worksheet2['A4'].value = export_info[4][0]
worksheet2['B4'].value = export_info[4][1]
# saving file
file_hash = uuid.uuid4().hex
export_file = BytesIO()
icds_file = IcdsFile(blob_id=file_hash, data_type=data_type)
workbook.save(export_file)
export_file.seek(0)
icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24)
icds_file.save()
return file_hash
