# from ast import main from xxlimited import new import fiona import requests import utm from geopy.geocoders import Nominatim import time import csv import json import pandas as pd import xlrd import matplotlib.path as mplPath import numpy as np from statistics import median import math import firebase_admin from firebase_admin import credentials from firebase_admin import db from firebase_admin import messaging from firebase_admin import firestore import threading import xlsxwriter import os from numba import jit, cuda cred = credentials.Certificate('servicekey.json') try: firebase_admin.initialize_app(cred, {'databaseURL': 'https://farmbase-b2f7e-31c0c.firebaseio.com/'}) except: print('fire running') kmlfarms = 'Downloads/chinoz.xlsx' amrofilepath = 'amro.xlsx' def try_lower(temp_str): try: temp_str = temp_str.lower() except: wew = 1 return temp_str def get_ttg_data(fieldobj, dataKey): try: ttg_data = fieldobj[dataKey] except: ttg_data = None return ttg_data def get_heath_status(index): if int(index) > 45: status = "good" else: status = "bad" return status def add_farms_num(obj, key, value): try: obj[key] = int(obj[key]) + value except: obj[key] = value return obj[key] def add_value(obj, key, value): value = float(value)/10000 try: obj[key] = float(obj[key]) + float(value) except: obj[key] = float(value) return (round(100*obj[key])/100) def get_phone_number(data_obj): phone1 = data_obj["PhoneNumber"] phone2 = data_obj["PhoneNumber2"] if len(phone2) > len(phone1): phone1 = phone2 try: phone1 = int(phone1) except: phone1 = phone1 return phone1 def set_value(obj,key): try: tempObj = obj[key] except: obj[key] = 0 return obj[key] def format_region_wise_sheet(sheet_obj): new_obj = {} for (region, regionObj) in sheet_obj.items(): cluster = regionObj["Cluster"] try: temp_obj = new_obj[cluster] temp_obj[region] = regionObj new_obj[cluster] = temp_obj except: temp_obj = {} temp_obj[region] = regionObj new_obj[cluster] = temp_obj return new_obj def format_detailed_sheet(sheet_obj): new_obj = {} for (fieldid, fieldObj) in sheet_obj.items(): cluster = fieldObj["Cluster"] region = fieldObj["Region"] temp_field_obj = {} temp_field_obj[fieldid] = fieldObj try: #if cluster obj exists cluster_obj = new_obj[cluster] try: #if region obj exists region_obj = cluster_obj[region] region_obj[fieldid] = temp_field_obj cluster_obj[region] = region_obj except: #if region obj doesnt exsit region_obj = {} region_obj[fieldid] = temp_field_obj cluster_obj[region] = region_obj except: #if cluster obj doesnt exist cluster_obj = {} region_obj = {} region_obj[fieldid] = temp_field_obj cluster_obj[region] = region_obj new_obj[cluster] = cluster_obj #cluster_obj[cluster] = region_obj # try: # #cluster key and region key exist # temp_obj = new_obj[cluster][region] # temp_obj[fieldid] = fieldObj # new_obj[cluster][region] = temp_obj # #print(new_obj) # except Exception as e: # #print(e) # #cluster key exists and region key doesn't exisit # try: # temp_obj = new_obj[cluster] # temp_region_obj = {} # temp_field_obj = {} # temp_field_obj[fieldid] = fieldObj # temp_region_obj[region] = temp_field_obj # temp_obj[cluster][region] = temp_region_obj # new_obj[cluster] = temp_obj # #cluster key and region key dont exist # except Exception as e: # #print(e) # temp_region_obj = {} # temp_field_obj = {} # temp_field_obj[fieldid] = fieldObj # temp_region_obj[region] = temp_field_obj # new_obj[cluster] = temp_region_obj num = 0 for(p,q) in new_obj.items(): #print(p) for (x,y) in q.items(): #print(x) for (m,n) in y.items(): #print(m) num = num+1 print(num) return new_obj workbook_kmlfarms = xlrd.open_workbook(kmlfarms) sheet_amrofarms = workbook_kmlfarms.sheet_by_index(0) plot_num =0 err_num = 0 uid = 'mFFHQdEtiSbn2hbYQAwwoIdYVi02' dbf = firestore.client() ttg_fields = db.reference('PaidMonitoredFields').child('PMF').child(uid).get() summary_sheet_obj = {} region_sheet_obj = {} detailed_report_sheet_obj = {} temp_num = 0 for (fieldid, fieldobj) in ttg_fields.items(): ttg_data = get_ttg_data(fieldobj,"TTGData") senseddaysobj = get_ttg_data(fieldobj, "SensedDays") sensed_days_arr = [] if senseddaysobj is not None: for (sensedday, day_obj) in senseddaysobj.items(): sensed_days_arr.append(sensedday) if len(sensed_days_arr) > 0: latest_day = max(sensed_days_arr) else: latest_day = None if ttg_data is not None: temp_num = temp_num + 1 if ttg_data is not None and latest_day is not None: #temp_num = temp_num + 1 try: ndvi = int(fieldobj["Health"]["ndvi"][latest_day]) ndwi = int(fieldobj["Health"]["ndwi"][latest_day]) except: ndvi, ndwi = 0, 0 latest_day = str(latest_day) latest_day = latest_day[6:] + "-" + latest_day[4:6] + "-" + latest_day[0:4] region_leader_number = ttg_data["LeaderPhoneNumber"] try: region_leader_number = int(region_leader_number) except: region_leader_number = region_leader_number #make summary_sheet_obj try: temp_summary_sheet_obj = summary_sheet_obj[ttg_data["Cluster"]] except: temp_summary_sheet_obj = {} temp_summary_sheet_obj["TotalFarms"] = add_farms_num(temp_summary_sheet_obj, "TotalFarms",1) temp_summary_sheet_obj["TotalArea"] = round(100*add_value(temp_summary_sheet_obj, "TotalArea", fieldobj["FieldArea"]))/100 temp_summary_sheet_obj["SatelliteVisitDate"] = latest_day values_to_set = ["BadHealthArea", "BadHealthAreaPecentage", "BadIrrigationArea", "BadIrrigationAreaPercentage", "TotalGoodArea", "TotalGoodAreaPercentage"] for i in range(0, len(values_to_set)): temp_summary_sheet_obj[values_to_set[i]] = set_value(temp_summary_sheet_obj, values_to_set[i]) temp_summary_sheet_obj["BadHealthArea"] = round(100*add_value(temp_summary_sheet_obj, "BadHealthArea", (1-ndvi/80)*fieldobj["FieldArea"]))/100 bad_health_per = 100*round(100*temp_summary_sheet_obj["BadHealthArea"]/temp_summary_sheet_obj["TotalArea"])/100 temp_summary_sheet_obj["BadIrrigationArea"] = round(100*add_value(temp_summary_sheet_obj, "BadIrrigationArea", (1-ndwi/80)*fieldobj["FieldArea"]))/100 bad_irr_per = 100*round(100*temp_summary_sheet_obj["BadIrrigationArea"]/temp_summary_sheet_obj["TotalArea"])/100 bad_health_area = temp_summary_sheet_obj["BadHealthArea"] bad_irr_area = temp_summary_sheet_obj["BadIrrigationArea"] try: total_good_area = round(100*(temp_summary_sheet_obj["TotalArea"] - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(temp_summary_sheet_obj["TotalArea"]))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - temp_summary_sheet_obj["TotalArea"]))/100 except: total_good_area = round(100*(temp_summary_sheet_obj["TotalArea"] - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(temp_summary_sheet_obj["TotalArea"]))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - temp_summary_sheet_obj["TotalArea"]))/100 #temp_region_sheet_obj["BadHealthArea"] = bad_health_area temp_summary_sheet_obj["BadHealthAreaPercentage"] = bad_health_per #temp_region_sheet_obj["BadIrrigationArea"] = bad_irr_area temp_summary_sheet_obj["TotalBadIrrigationAreaPercentage"] = bad_irr_per temp_summary_sheet_obj["TotalGoodArea"] = total_good_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_summary_sheet_obj["TotalGoodAreaPercentage"] = round(100*total_good_area/(temp_summary_sheet_obj["TotalArea"])) temp_summary_sheet_obj["TotalBadArea"] = total_bad_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_summary_sheet_obj["TotalBadAreaPercentage"] = round(100*total_bad_area/(temp_summary_sheet_obj["TotalArea"])) summary_sheet_obj[ttg_data["Cluster"]] = temp_summary_sheet_obj #make region_sheet_obj try: temp_region_sheet_obj = region_sheet_obj[ttg_data["Region"]] except: temp_region_sheet_obj = {} for i in range(0, len(values_to_set)): temp_region_sheet_obj[values_to_set[i]] = set_value(temp_region_sheet_obj, values_to_set[i]) temp_region_sheet_obj["Cluster"] = ttg_data["Cluster"] temp_region_sheet_obj["RegionLeader"] = ttg_data["RegionLeader"] temp_region_sheet_obj["TotalFarms"] = add_farms_num(temp_region_sheet_obj, "TotalFarms",1) temp_region_sheet_obj["LeaderPhoneNumber"] = region_leader_number temp_region_sheet_obj["TotalArea"] = add_value(temp_region_sheet_obj, "TotalArea", fieldobj["FieldArea"]) temp_region_sheet_obj["SatelliteVisitDate"] = latest_day # if get_heath_status(ndvi) == "bad": temp_region_sheet_obj["BadHealthArea"] = round(100*add_value(temp_region_sheet_obj, "BadHealthArea", (1-ndvi/80)*fieldobj["FieldArea"]))/100 bad_health_per = 100*round(100*temp_region_sheet_obj["BadHealthArea"]/temp_region_sheet_obj["TotalArea"])/100 temp_region_sheet_obj["BadIrrigationArea"] = round(100*add_value(temp_region_sheet_obj, "BadIrrigationArea", (1-ndwi/80)*fieldobj["FieldArea"]))/100 bad_irr_per = 100*round(100*temp_region_sheet_obj["BadIrrigationArea"]/temp_region_sheet_obj["TotalArea"])/100 bad_health_area = temp_region_sheet_obj["BadHealthArea"] bad_irr_area = temp_region_sheet_obj["BadIrrigationArea"] try: total_good_area = round(100*(temp_region_sheet_obj["TotalArea"] - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(temp_region_sheet_obj["TotalArea"]))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - temp_region_sheet_obj["TotalArea"]))/100 except: total_good_area = round(100*(temp_region_sheet_obj["TotalArea"] - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(temp_region_sheet_obj["TotalArea"]))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - temp_region_sheet_obj["TotalArea"]))/100 #temp_region_sheet_obj["BadHealthArea"] = bad_health_area temp_region_sheet_obj["BadHealthAreaPercentage"] = bad_health_per #temp_region_sheet_obj["BadIrrigationArea"] = bad_irr_area temp_region_sheet_obj["TotalBadIrrigationAreaPercentage"] = bad_irr_per temp_region_sheet_obj["TotalGoodArea"] = total_good_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_region_sheet_obj["TotalGoodAreaPercentage"] = round(100*total_good_area/(temp_region_sheet_obj["TotalArea"])) temp_region_sheet_obj["TotalBadArea"] = total_bad_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_region_sheet_obj["TotalBadAreaPercentage"] = round(100*total_bad_area/(temp_region_sheet_obj["TotalArea"])) region_sheet_obj[ttg_data["Region"]] = temp_region_sheet_obj temp_detailed_obj = {} temp_detailed_obj["FieldID"]= fieldid temp_detailed_obj["Cluster"] = ttg_data["Cluster"] temp_detailed_obj["Region"] = ttg_data["Region"] temp_detailed_obj["FarmerName"] = ttg_data["Name"] temp_detailed_obj["FarmerPhoneNumber"] = get_phone_number(ttg_data) temp_detailed_obj["RegionLeader"] = ttg_data["RegionLeader"] temp_detailed_obj["LeaderPhoneNumber"] = region_leader_number temp_detailed_obj["TotalArea"] = add_value(temp_detailed_obj, "TotalArea", fieldobj["FieldArea"]) temp_detailed_obj["SatelliteVisitDate"] = latest_day ndvi,ndwi = int(ndvi),int(ndwi) bad_health_area = round(100*(1-ndvi/80)*add_value(temp_detailed_obj, "BadHealthArea", fieldobj["FieldArea"]))/100 bad_health_per = 100*round((1-ndvi/80)*100*temp_detailed_obj["BadHealthArea"]/temp_detailed_obj["TotalArea"])/100 bad_irr_area = round(100*(1-ndwi/80)*add_value(temp_detailed_obj, "BadIrrigationArea", fieldobj["FieldArea"]))/100 bad_irr_per = 100*round((1-ndwi/80)*100*temp_detailed_obj["BadIrrigationArea"]/temp_detailed_obj["TotalArea"])/100 total_good_area = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(fieldobj["FieldArea"]/10000))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - fieldobj["FieldArea"]/10000))/100 temp_detailed_obj["BadHealthArea"] = bad_health_area temp_detailed_obj["BadHealthAreaPercentage"] = bad_health_per temp_detailed_obj["BadIrrigationArea"] = bad_irr_area temp_detailed_obj["TotalBadIrrigationAreaPercentage"] = bad_irr_per temp_detailed_obj["TotalGoodArea"] = total_good_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_detailed_obj["TotalGoodAreaPercentage"] = round(100*total_good_area/(fieldobj["FieldArea"]/10000)) temp_detailed_obj["TotalBadArea"] = total_bad_area #temp_detailed_obj["TotalGoodArea"] = round(100*(fieldobj["FieldArea"]/10000 - (bad_health_area*bad_irr_area)/((fieldobj["FieldArea"]/10000))))/100 temp_detailed_obj["TotalBadAreaPercentage"] = round(100*total_bad_area/(fieldobj["FieldArea"]/10000)) detailed_report_sheet_obj[fieldid] = temp_detailed_obj # print(summary_sheet_obj) main_width = 18 main_ttg_obj = {} main_ttg_obj["Summary"] = summary_sheet_obj main_ttg_obj["RegionWise"] = region_sheet_obj main_ttg_obj["Detailed"] = detailed_report_sheet_obj # #print(main_ttg_obj) # def setSheets(collection_ref, sheet_ref): # for(p,q) in sheet_ref.items(): # dbf.collection(collection_ref).document(p).set(q) # time.sleep(0.1) # setSheets("TTGSummary", summary_sheet_obj) # setSheets("TTGRegionWise", region_sheet_obj) # setSheets("TTGDetailed", detailed_report_sheet_obj) # dbf.collection("TTGSummary").document("Summary").set(summary_sheet_obj) # db.reference('TTGData').child('LatestData').set(main_ttg_obj) #print(detailed_report_sheet_obj) print(temp_num) def get_per(num, dem): return int(float(num)*100/float(dem)) workbook = xlsxwriter.Workbook('TTG-Data.xlsx') workbook.formats[0].set_font_size(12) bold = workbook.add_format({'bold': True, 'border':1}) text_wrap = workbook.add_format({'text_wrap': True}) blue_bg_merge = workbook.add_format({'fg_color':'blue', 'italic':True, 'bold':True, 'align':'center', 'valign':'center','font_size':14, 'font_color':'white'}) white_bg_merge = workbook.add_format({'fg_color':'white', 'italic':True, 'bold':True, 'align':'center', 'valign':'center','font_size':14, 'font_color':'black'}) white_bg_merge2 = workbook.add_format({'border':1,'fg_color':'white', 'italic':True, 'bold':True,'font_size':14, 'valign': 'top', 'font_color':'black'}) gray_bg_merge = workbook.add_format({'border':1,'fg_color':'gray', 'italic':True, 'bold':True, 'align':'center', 'font_color':'#800202'}) gray_bg_cell = workbook.add_format({'fg_color':'gray','border':1}) white_bg_cell = workbook.add_format({'fg_color':'white','border':1}) gray_bg_merge_with_top_border = workbook.add_format({'border':1,'top':2,'top_color':'#008080','fg_color':'gray', 'italic':True, 'bold':True, 'align':'center', 'font_color':'#800202'}) gray_bg_cell_bold = workbook.add_format({'bold':True,'fg_color':'gray','border':1}) yellow_bg_cell_bold = workbook.add_format({'bold':True,'fg_color':'yellow','border':1}) light_blue_bg_cell_bold = workbook.add_format({'bold':True,'fg_color':'#add8e6','border':1}) def get_format_type(bad_area_per, bad_irri_per, bad_health_per): format_green = workbook.add_format({'font_color': 'green'}) format_orange = workbook.add_format({'font_color': 'orange'}) format_purple = workbook.add_format({'font_color': 'purple'}) format_red = workbook.add_format({'font_color': 'red'}) if float(bad_area_per) > 25: return format_red if float(bad_irri_per) >= float(bad_health_per) and float(bad_irri_per) > 30 : return format_purple if float(bad_health_per) >= float(bad_irri_per) and float(bad_health_per) > 30 : return format_orange return format_green worksheet = workbook.add_worksheet("Summary Sheet") worksheet.freeze_panes(4,0) worksheet.merge_range('A1:J1', 'SUMMARY REPORT', blue_bg_merge) worksheet.merge_range('A3:A4', 'Cluster', gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', 'Total Area (Ha)', gray_bg_merge_with_top_border) worksheet.merge_range('C3:D3', 'Total Good', gray_bg_merge_with_top_border) worksheet.merge_range('E3:F3', 'Total Bad', gray_bg_merge_with_top_border) worksheet.merge_range('G3:H3', 'Irrigation Required', gray_bg_merge_with_top_border) worksheet.merge_range('I3:J3', 'Fertilizer Required', gray_bg_merge_with_top_border) worksheet.write(3,2,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,3, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,4,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,5, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,6,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,7, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,8,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,9, 'in %', gray_bg_merge_with_top_border) all_rows = [] all_rows.append([]) # first_row = ["Cluster", "Total Area (Ha)", "Area With Healthy Growth & Irrigation", "Area With Healthy Growth & Irrigation %", "Area Requiring Both Fertilizer & Irrigation", "Area Requiring Both Fertilizer & Irrigation %","Irrigation Required in (Ha)", "Irrigation Required in (%)", "Fertilizer Required in (Ha)", "Fertilizer Required in (%)"] # all_rows.append(first_row) for (p, q) in summary_sheet_obj.items(): try: p = str(p).upper() except: awa = 1 new_row = [p, q["TotalArea"], q["TotalGoodArea"], q["TotalGoodAreaPercentage"], q["TotalBadArea"],q["TotalBadAreaPercentage"], q["BadIrrigationArea"], q["TotalBadIrrigationAreaPercentage"],q["BadHealthArea"], q["BadHealthAreaPercentage"]] all_rows.append(new_row) sum_row = [] sum_row.append('TOTAL') for i in range (1, len(all_rows[1])): sum_row.append(0) #print(sum_row) for i in range(0, len(all_rows)): temp_row = all_rows[i] for j in range(0, len(temp_row)): if i > 0 and j > 0: sum_row[j] = sum_row[j] + temp_row[j] if (i+4)%2 == 0: worksheet.write((i+3),j,temp_row[j], gray_bg_cell) else: worksheet.write((i+3),j,temp_row[j],white_bg_cell) #worksheet.write(i,j,temp_row[j],get_format_type(temp_row[5], temp_row[7], temp_row[9])) sum_row[3] = (round(100*sum_row[2]/sum_row[1])) sum_row[5] = (round(100*sum_row[4]/sum_row[1])) sum_row[7] = (round(100*sum_row[6]/sum_row[1])) sum_row[9] = (round(100*sum_row[8]/sum_row[1])) for i in range(0,len(sum_row)): worksheet.write((len(all_rows)+3),i, sum_row[i],bold) worksheet.set_column(0,9, main_width) worksheet = workbook.add_worksheet("Region Wise Sheet") worksheet.freeze_panes(4,0) worksheet.merge_range('A1:M1', 'REGION WISE REPORT', blue_bg_merge) worksheet.merge_range('A3:A4', 'Cluster', gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', 'Region', gray_bg_merge_with_top_border) worksheet.merge_range('C3:C4', 'Leader Name', gray_bg_merge_with_top_border) worksheet.merge_range('D3:D4', 'Leader Phone Number', gray_bg_merge_with_top_border) worksheet.merge_range('E3:E4', 'Total Area (Ha)', gray_bg_merge_with_top_border) worksheet.merge_range('F3:G3', 'Total Good', gray_bg_merge_with_top_border) worksheet.merge_range('H3:I3', 'Total Bad', gray_bg_merge_with_top_border) worksheet.merge_range('J3:K3', 'Irrigation Required', gray_bg_merge_with_top_border) worksheet.merge_range('L3:M3', 'Fertilizer Required', gray_bg_merge_with_top_border) worksheet.write(3,5,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,6, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,7,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,8, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,9,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,10, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,11,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,12, 'in %', gray_bg_merge_with_top_border) all_rows = [] all_rows.append([]) all_rows = [] # first_row = ["Cluster", "Region", "Leader Name", "Leader Phone Number", "Total Area (Ha)", "Area With Healthy Growth & Irrigation", "Area With Healthy Growth & Irrigation %", "Area Requiring Both Fertilizer & Irrigation", "Area Requiring Both Fertilizer & Irrigation %", "Irrigation Required in (Ha)", "Irrigation Required in (%)", "Fertilizer Required in (Ha)", "Fertilizer Required in (%)"] # all_rows.append(first_row) new_region_wise_sheet = format_region_wise_sheet(region_sheet_obj) # print(new_region_wise_sheet) region_num = 0 all_sum_row = [] all_sum_row.append('TOTAL') all_sum_row.append('') all_sum_row.append('') all_sum_row.append('') for i in range(0,9): all_sum_row.append(0) row_num = 4 for(cluster, clusterObj) in new_region_wise_sheet.items(): this_region_num = 0 #initate cluster_sum_row cluster_sum_row = [] cluster_sum_row.append('') cluster_sum_row.append(('SUB TOTAL: ' + cluster.title())) cluster_sum_row.append('') cluster_sum_row.append('') for i in range(0,9): cluster_sum_row.append(0) #print(cluster_sum_row) #add data in all_rows for(region, q) in clusterObj.items(): #print(region) region_num = region_num + 1 this_region_num = this_region_num + 1 num_arr = ["FarmerPhoneNumber", "LeaderPhoneNumber"] for key in num_arr: try: q[key] = str(q[key]).replace("-","") q[key] = str(q[key]).replace("(","") q[key] = str(q[key]).replace(")","") q[key] = str(q[key]).replace(" ", "") q[key] = str(q[key]).replace("998", "",1) except: awaa = 1 for (x,y) in q.items(): try: q[x]= str(q[x]).title() except: awa = 1 try: region = str(region).title() except: awa = 1 new_row = [cluster,region,q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], q["TotalGoodArea"], q["TotalGoodAreaPercentage"],q["TotalBadArea"],q["TotalBadAreaPercentage"], q["BadIrrigationArea"], q["TotalBadIrrigationAreaPercentage"],q["BadHealthArea"], q["BadHealthAreaPercentage"]] all_rows.append(new_row) #add sum of the cluster values for k in range(4, len(new_row)): #print(new_row[k]) cluster_sum_row[k] = cluster_sum_row[k] + float(new_row[k]) all_sum_row[k] = all_sum_row[k] + float(new_row[k]) #write_data_rows for j in range(0, len(new_row)): worksheet.write(row_num,j,new_row[j],white_bg_cell) row_num = row_num + 1 #print(cluster) m_range = ('A' + str((1+row_num-this_region_num))+':'+'A'+str((row_num))) #print(m_range) worksheet.merge_range(m_range, cluster.upper(),white_bg_merge) #write sub total row cluster_sum_row[6] = (round(100*cluster_sum_row[5]/cluster_sum_row[4])) cluster_sum_row[8] = (round(100*cluster_sum_row[7]/cluster_sum_row[4])) cluster_sum_row[10] = (round(100*cluster_sum_row[9]/cluster_sum_row[4])) cluster_sum_row[12] = (round(100*cluster_sum_row[11]/cluster_sum_row[4])) for i in range(0,len(cluster_sum_row)): worksheet.write((row_num),i, cluster_sum_row[i],gray_bg_cell_bold) row_num = row_num +1 #write all sum row # all_sum_row[6] = (round(100*all_sum_row[5]/all_sum_row[4])) all_sum_row[8] = (round(100*all_sum_row[7]/all_sum_row[4])) all_sum_row[10] = (round(100*all_sum_row[9]/all_sum_row[4])) all_sum_row[12] = (round(100*all_sum_row[11]/all_sum_row[4])) for i in range(0,len(all_sum_row)): worksheet.write((row_num),i, all_sum_row[i],yellow_bg_cell_bold) worksheet.set_column(0,(len(all_rows[1])-1), main_width) worksheet = workbook.add_worksheet("Detailed Sheet") worksheet.freeze_panes(4,0) worksheet.merge_range('A1:Q1', 'DETAILED REPORT', blue_bg_merge) worksheet.merge_range('A3:A4', 'Link to The Farm', gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', 'Cluster', gray_bg_merge_with_top_border) worksheet.merge_range('C3:C4', 'Region', gray_bg_merge_with_top_border) worksheet.merge_range('D3:D4', 'Farmer Name', gray_bg_merge_with_top_border) worksheet.merge_range('E3:E4', 'Farmer Phone Number', gray_bg_merge_with_top_border) worksheet.merge_range('F3:F4', 'Satellite Visit Date', gray_bg_merge_with_top_border) worksheet.merge_range('G3:G4', 'Leader Name', gray_bg_merge_with_top_border) worksheet.merge_range('H3:H4', 'Leader Phone Number', gray_bg_merge_with_top_border) worksheet.merge_range('I3:I4', 'Total Area (Ha)', gray_bg_merge_with_top_border) worksheet.merge_range('J3:K3', 'Total Good', gray_bg_merge_with_top_border) worksheet.merge_range('L3:M3', 'Total Bad', gray_bg_merge_with_top_border) worksheet.merge_range('N3:O3', 'Irrigation Required', gray_bg_merge_with_top_border) worksheet.merge_range('P3:Q3', 'Fertilizer Required', gray_bg_merge_with_top_border) # worksheet.write(3,5,'in (Ha)', gray_bg_merge_with_top_border) # worksheet.write(3,6, 'in %', gray_bg_merge_with_top_border) # worksheet.write(3,7,'in (Ha)', gray_bg_merge_with_top_border) # worksheet.write(3,8, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,9,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,10, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,11,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,12, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,13,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,14, 'in %', gray_bg_merge_with_top_border) worksheet.write(3,15,'in (Ha)', gray_bg_merge_with_top_border) worksheet.write(3,16, 'in %', gray_bg_merge_with_top_border) all_rows = [] all_rows.append([]) all_rows = [] # first_row = ["Cluster", "Region", "Leader Name", "Leader Phone Number", "Total Area (Ha)", "Area With Healthy Growth & Irrigation", "Area With Healthy Growth & Irrigation %", "Area Requiring Both Fertilizer & Irrigation", "Area Requiring Both Fertilizer & Irrigation %", "Irrigation Required in (Ha)", "Irrigation Required in (%)", "Fertilizer Required in (Ha)", "Fertilizer Required in (%)"] # all_rows.append(first_row) new_detailed_sheet = format_detailed_sheet(detailed_report_sheet_obj) # print(new_region_wise_sheet) region_num = 0 cluster_num = 0 all_sum_row = [] all_sum_row.append('TOTAL') all_sum_row.append('') for kk in range(0,6): all_sum_row.append('') for i in range(0,9): all_sum_row.append(0) row_num = 4 for(cluster, clusterObj) in new_detailed_sheet.items(): this_cluster_num = 0 #initate cluster_sum_row cluster_sum_row = [] cluster_sum_row.append('') cluster_sum_row.append('') cluster_sum_row.append(('SUB TOTAL: ' + cluster.title())) for kk in range(0,5): cluster_sum_row.append('') #cluster_sum_row.append('') for i in range(0,9): cluster_sum_row.append(0) #print(cluster_sum_row) #add data in all_rows this_region_num = 0 region_num = 0 for (region, regionObj) in clusterObj.items(): region_num = region_num + 1 region_sum_row = [] region_sum_row.append('') region_sum_row.append('') region_sum_row.append(('SUB TOTAL: ' + region.title())) for kk in range(0,5): region_sum_row.append('') for i in range(0,9): region_sum_row.append(0) #print(region_sum_row) this_region_num = 0 for(key1, key_obj) in regionObj.items(): for(fieldid, q) in key_obj.items(): this_region_num = this_region_num + 1 this_cluster_num = this_cluster_num + 1 #print(q) num_arr = ["FarmerPhoneNumber", "LeaderPhoneNumber"] for key in num_arr: try: if len(q[key])> 9: q[key] = str(q[key]).replace("998", "",1) q[key] = str(q[key]).replace("-","") q[key] = str(q[key]).replace("(","") q[key] = str(q[key]).replace(")","") q[key] = str(q[key]).replace(" ", "") except: awaa = 1 for (x,y) in q.items(): try: q[x]= str(q[x]).title() except: awa = 1 try: region = str(region).title() except: awa = 1 web_url = "https://farmonaut.com/web-app?UID="+ str(uid)+"&FieldID=" + str(fieldid) new_row = [web_url,q["Cluster"], q["Region"],q["FarmerName"], q["FarmerPhoneNumber"],q["SatelliteVisitDate"],q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], q["TotalGoodArea"], q["TotalGoodAreaPercentage"], q["TotalBadArea"],q["TotalBadAreaPercentage"], q["BadIrrigationArea"], q["TotalBadIrrigationAreaPercentage"],q["BadHealthArea"],q["BadHealthAreaPercentage"]] #new_row = [cluster,region,q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], q["TotalGoodArea"], q["TotalGoodAreaPercentage"],q["TotalBadArea"],q["TotalBadAreaPercentage"], q["BadIrrigationArea"], q["TotalBadIrrigationAreaPercentage"],q["BadHealthArea"], q["BadHealthAreaPercentage"]] all_rows.append(new_row) #add sum of the cluster values for k in range(8, len(new_row)): #print(new_row[k]) cluster_sum_row[k] = cluster_sum_row[k] + float(new_row[k]) region_sum_row[k] = region_sum_row[k] + float(new_row[k]) all_sum_row[k] = all_sum_row[k] + float(new_row[k]) #write_data_rows for j in range(0, len(new_row)): if j == 0: worksheet.write_url(row_num,j, new_row[j], string = 'See Farm On Map') else: worksheet.write(row_num,j,new_row[j],get_format_type(new_row[12], new_row[14], new_row[16])) row_num = row_num + 1 # print(row_num) # print(this_region_num) m_range = ('C' + str((1+row_num-this_region_num))+':'+'C'+str((row_num))) #print(m_range) worksheet.merge_range(m_range, region.upper(),white_bg_merge2) #write region sub total row region_sum_row[10] = (round(100*region_sum_row[9]/region_sum_row[8])) region_sum_row[12] = (round(100*region_sum_row[11]/region_sum_row[8])) region_sum_row[14] = (round(100*region_sum_row[13]/region_sum_row[8])) region_sum_row[16] = (round(100*region_sum_row[15]/region_sum_row[8])) for i in range(0,len(region_sum_row)): worksheet.write((row_num),i, region_sum_row[i],gray_bg_cell_bold) row_num = row_num +1 #print(cluster) # print(row_num) # print(this_cluster_num) # print(region_num) m_range = ('B' + str((1+row_num-(this_cluster_num+region_num)))+':'+'B'+str((row_num-1))) #print(m_range) worksheet.merge_range(m_range, cluster.upper(),white_bg_merge2) #write cluster sub total row cluster_sum_row[10] = (round(100*cluster_sum_row[9]/cluster_sum_row[8])) cluster_sum_row[12] = (round(100*cluster_sum_row[11]/cluster_sum_row[8])) cluster_sum_row[14] = (round(100*cluster_sum_row[13]/cluster_sum_row[8])) cluster_sum_row[16] = (round(100*cluster_sum_row[15]/cluster_sum_row[8])) for i in range(0,len(cluster_sum_row)): worksheet.write((row_num),i, cluster_sum_row[i],yellow_bg_cell_bold) row_num = row_num +1 #write all sum row all_sum_row[10] = (round(100*all_sum_row[9]/all_sum_row[8])) all_sum_row[12] = (round(100*all_sum_row[11]/all_sum_row[8])) all_sum_row[14] = (round(100*all_sum_row[13]/all_sum_row[8])) all_sum_row[16] = (round(100*all_sum_row[15]/all_sum_row[8])) for i in range(0,len(all_sum_row)): worksheet.write((row_num),i, all_sum_row[i],light_blue_bg_cell_bold) worksheet.set_column(0,(len(all_rows[1])-1), main_width) filter_range = 'A4:Q' + str(row_num) worksheet.autofilter(filter_range) workbook.close()