# snake_case is used for variable names # creating specifically for DCM right now***** 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, db from google.cloud import storage # import threading import xlsxwriter from tempfile import tempdir # import pandas as pd # import statsmodels.api as sm import seaborn as sns # import csv import asyncio import traceback import json # import numpy as np from sklearn.cluster import KMeans # from find_study_area_values import find_study_area_values # from make_area_estimate_image import make_area_estimate_image import json import xlsxwriter # import datetime # import requests # from sentinelhub import WebFeatureService, BBox, CRS, MimeType, CRS, BBox, WmsRequest,DataCollection from create_instructions import getLocalDirByZone, getLocalDirAbrByZone sns.set() x = 0.0003 y = 0.0003 fromdate = '20220701' todate = '20220715' s1_images = ['IW-VH-DB'] s2_images = [] # s1_images = ["B02", "B03", "B04", "B05"] #s2_images = ["RVI-NEW"] BUCKET_NAME = "farmbase-b2f7e.appspot.com" def try_lower(temp_str): try: temp_str = temp_str.lower() except: wew = 1 return temp_str def get_heath_status(index): if int(index) > 45: status = "good" else: status = "bad" return status def add_farms_num(obj, key, value): try: return int(obj[key]) + value except: return value def add_area_value(obj, key, value): value = float(value)/10000 #converting sq m to hectare try: value = float(obj[key]) + float(value) except: value = float(value) return (round(100*value)/100) def get_phone_number(phone1, phone2, emptyValue=""): # returns all no with comma seperation if phone1=="" and phone2=="": return emptyValue if phone1 != "": return phone1 elif phone1 == "" and phone2 != "": return phone2 else: return emptyValue # return f'{phone1}{"" if len(str(phone2))==0 else ("" if len(str(phone1))==0 else ","+str(phone2))}' 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(detailed_obj): new_obj = {} for (fieldid, detailedFieldObj) in detailed_obj.items(): cluster = detailedFieldObj["Cluster"] region = detailedFieldObj["Region"] temp_field_obj = {} temp_field_obj[fieldid] = detailedFieldObj 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 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 def getDictFromJsonFile(path): with open(path, 'r', encoding='utf-8') as f: return json.loads(f.read()) def getUserFields(uid, isTest=False): if isTest: return getDictFromJsonFile("jsons2/2tempFieldsData.json") else: # print("") return db.reference('PaidMonitoredFields').child('PMF').child(uid).get() def initFirebase(isTest=False): # print("") try: app = firebase_admin.get_app("[DEFAULT]") except: app = None if app == None: cred = credentials.Certificate('servicekey.json') firebase_admin.initialize_app(cred, {'databaseURL': 'https://farmbase-b2f7e-31c0c.firebaseio.com/'}) def getTranslations(lang, isTest=False): if isTest: return getDictFromJsonFile(f'jsons/app_{lang}.json') else: # print("") return db.reference('ReportStrings2022').child(lang).get() def saveJsonDataToFile(path, data): with open(path, 'w', encoding='utf-8') as f: json.dump(data, f, ensure_ascii=False, indent=4) # It containes the path for values of different columns; it's based on formats - 1, 2 # ; the columns are - Cluster, FarmerName, Region, RegionLeader, OrgMobileNo, OrgWhatsappNo, OrgFieldID, FarmerID val_to_col = { 1:{ "Cluster":["OrgData.OrgDistrict"], "Region":["OrgData.OrgVillageName"], "RegionLeader":[""], "FarmerName":["OrgData.OrgRyotName"], "FarmerID":["OrgData.OrgRyotCode"], "OrgFieldID":["OrgData.OrgFieldID", "OrgData.OrgPlot"], "FarmerPhoneNumber":["OrgData.OrgMobileNo"], "FarmerPhoneNumber2":["OrgData.OrgWhatsappNo"] }, 2:{ "Cluster":[""], "Region":[""], "RegionLeader":[""], "FarmerName":["Name", "FarmerName"], "FarmerID":[""], "OrgFieldID":["FieldID"], "FarmerPhoneNumber":["PhoneNumber", "FarmerPhoneNumber", "Phone"], "FarmerPhoneNumber2":["WhatsappPhoneNumber"] } } # used in sheet gen emptyCellValue = "-" #don't use it for hyphen str # the key terminology is just defined for the code def getOrgValueByKey(obj, key, emptyVal=emptyCellValue, formatNo=1): format_val_to_col = val_to_col.get(formatNo) assert key in format_val_to_col for dataKey in format_val_to_col[key]: val = getValByKeyPath(obj, dataKey) if val: return val return emptyVal # KeyPath is string of keys separated by . def getValByKeyPath(obj, keyPath): keys = keyPath.split(".") for key in keys: obj = obj.get(key) return obj # ** ONLY FOR DCM ** # Makes excel sheet of all fields of an account with region wise aggregated data # and field wise detailed data on crop and irrigation stress; and farmer info # # process- # 1. get all fields data and translations # 2. need to create summary, region and cluster wise data objects # 3. create respective sheets one by one # 4. while creating sheets first a new_row list is created with final data # 5. save all sheets def makeClusterSheet(uid, isTest=False, fileNo=0): ##### init firebase app for later usage initFirebase(isTest) ##### get all fields data and extract useful ones values_to_set = ["BadHealthArea", "BadHealthAreaPecentage", "BadIrrigationArea", "BadIrrigationAreaPercentage", "TotalGoodArea", "TotalGoodAreaPercentage"] ##### get translations lang = "hi" isEn = lang == "en" enTrans = getTranslations("en", isTest) langTrans = getTranslations(lang, isTest) if not isEn else enTrans org_fields = getUserFields(uid, isTest) summary_sheet_obj = {} region_sheet_obj = {} detailed_report_sheet_obj = {} no_data_fields_count = 0 for (field_id, field_obj) in org_fields.items(): ###### checking if data present for the field field_org_data = field_obj.get("OrgData", None) sensed_days = field_obj.get("SensedDays", None) if sensed_days is not None: sensed_days_list = list(sensed_days.keys().sort(reverse=True)) latest_day = sensed_days_list[4] else: latest_day = None if field_org_data is None: no_data_fields_count = no_data_fields_count + 1 fieldArea = field_obj.get("FieldArea", 0) if field_org_data is not None and latest_day is not None and fieldArea > 110: ###### start getting useful data cluster = getOrgValueByKey(field_obj, "Cluster") farmerName = getOrgValueByKey(field_obj, "FarmerName", emptyCellValue) regionVal = getOrgValueByKey(field_obj, "Region", emptyCellValue) regionLeader = getOrgValueByKey(field_obj, "RegionLeader", emptyCellValue) farmerNo = get_phone_number(field_org_data.get("OrgMobileNo", ""), field_org_data.get("OrgWhatsappNo", ""), emptyCellValue) farmerID = getOrgValueByKey(field_obj, "FarmerID", emptyCellValue) orgFieldID = getOrgValueByKey(field_obj, "OrgFieldID", emptyCellValue) try: fert_zones, irri_zones = 0,0 latestDayData = sensed_days[latest_day] cropRedZones = latestDayData["cropRedZones"] if type(latestDayData) != str else None irrigationRedZones = latestDayData["irrigationRedZones"] if type(latestDayData) != str else None ndviVal = field_obj["Health"]["ndvi"].get(latest_day, 0) ndwiVal = field_obj["Health"]["ndwi"].get(latest_day, 0) try: for tt in cropRedZones: fert_zones = fert_zones+1 except: fert_zones = 0 try: for pp in irrigationRedZones: irri_zones = irri_zones+1 except: irri_zones = 0 ndvi = round(float(100*(9-fert_zones)/9)) ndwi = round(float(100*(9-irri_zones)/9)) def getLocalDir(zone): return getLocalDirByZone(zone, langTrans, lang, False) # print("zones", cropRedZones, irrigationRedZones) cropRedZoneStr = (", ".join(list(map(getLocalDir, cropRedZones)))) if (cropRedZones != None and type(cropRedZones) == list and len(cropRedZones) != 0) else emptyCellValue irrigationRedZoneStr = (", ".join(list(map(getLocalDir, irrigationRedZones)))) if (irrigationRedZones != None and type(irrigationRedZones) == list and len(irrigationRedZones) != 0) else emptyCellValue #print("zones", cropRedZoneStr, irrigationRedZoneStri) except: print(traceback.format_exc()) ndvi, ndwi, ndviVal, ndwiVal = 0,0,0,0 cropRedZoneStr, irrigationRedZoneStr = emptyCellValue, emptyCellValue # print("zones", cropRedZoneStr, irrigationRedZoneStr) # print([field_id, latest_day, ndvi, ndwi]) latest_day = str(latest_day) latest_day = latest_day[6:] + "-" + latest_day[4:6] + "-" + latest_day[0:4] region_leader_number = "-" try: region_leader_number = int(region_leader_number) except: region_leader_number = region_leader_number ###### make summary_sheet_obj temp_summary_sheet_obj = summary_sheet_obj.get(cluster, {}) temp_summary_sheet_obj["TotalFarms"] = add_farms_num( temp_summary_sheet_obj, "TotalFarms", 1 ) temp_summary_sheet_obj["TotalArea"] = round( 100*add_area_value(temp_summary_sheet_obj, "TotalArea", fieldArea) )/100 temp_summary_sheet_obj["SatelliteVisitDate"] = latest_day for key in values_to_set: temp_summary_sheet_obj[key] = temp_summary_sheet_obj.get(key, 0) fieldBadCropArea = (1-ndvi/55)*fieldArea fieldBadIrriArea = (1-ndwi/50)*fieldArea if fieldBadCropArea < 0: fieldBadCropArea = 0 if fieldBadIrriArea < 0: fieldBadIrriArea = 0 temp_summary_sheet_obj["BadHealthArea"] = round( 100 * add_area_value( temp_summary_sheet_obj, "BadHealthArea", fieldBadCropArea ) )/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_area_value(temp_summary_sheet_obj, "BadIrrigationArea", fieldBadIrriArea))/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[cluster] = temp_summary_sheet_obj ###### make region_sheet_obj try: temp_region_sheet_obj = region_sheet_obj[regionVal] except: temp_region_sheet_obj = {} for key in values_to_set: temp_region_sheet_obj[key] = temp_region_sheet_obj.get(key, 0) temp_region_sheet_obj["Cluster"] = cluster temp_region_sheet_obj["RegionLeader"] = 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"] = float(add_area_value(temp_region_sheet_obj, "TotalArea", fieldArea)) # print("console", fieldArea, temp_region_sheet_obj["TotalArea"]) temp_region_sheet_obj["SatelliteVisitDate"] = latest_day # if get_heath_status(ndvi) == "bad": temp_region_sheet_obj["BadHealthArea"] = round( 100 * add_area_value( temp_region_sheet_obj, "BadHealthArea", fieldBadCropArea ) )/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_area_value(temp_region_sheet_obj, "BadIrrigationArea", fieldBadIrriArea))/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[regionVal] = temp_region_sheet_obj ###### make detailed_report_sheet_obj temp_detailed_obj = {} temp_detailed_obj["FieldID"]= field_id temp_detailed_obj["OrgFieldID"]= orgFieldID temp_detailed_obj["Cluster"] = cluster temp_detailed_obj["Region"] = regionVal temp_detailed_obj["FarmerID"] = farmerID temp_detailed_obj["FarmerName"] = farmerName temp_detailed_obj["FarmerPhoneNumber"] = farmerNo temp_detailed_obj["RegionLeader"] = regionLeader temp_detailed_obj["LeaderPhoneNumber"] = region_leader_number temp_detailed_obj["TotalArea"] = add_area_value(temp_detailed_obj, "TotalArea", fieldArea) temp_detailed_obj["SatelliteVisitDate"] = latest_day ndvi,ndwi = int(ndvi),int(ndwi) bad_health_area = round( 100 * add_area_value( {}, "BadHealthArea", fieldBadCropArea ) )/100 bad_health_per = 100*round( 100 * bad_health_area / temp_detailed_obj["TotalArea"] )/100 bad_irr_area = round( 100 * add_area_value( {}, "BadIrrigationArea", fieldBadIrriArea ) )/100 bad_irr_per = 100*round(100*bad_irr_area/temp_detailed_obj["TotalArea"])/100 total_good_area = round(100*(fieldArea/10000 - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(fieldArea/10000))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - fieldArea/10000))/100 temp_detailed_obj["BadHealthArea"] = round(bad_health_area*100)/100 temp_detailed_obj["BadHealthAreaPercentage"] = bad_health_per temp_detailed_obj["BadIrrigationArea"] = round(bad_irr_area*100)/100 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/(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/(fieldArea/10000)) temp_detailed_obj["BadCropDirections"] = cropRedZoneStr temp_detailed_obj["BadIrriDirections"] = irrigationRedZoneStr temp_detailed_obj["CropIndexVal"] = ndvi temp_detailed_obj["IrriIndexVal"] = ndwi #print(temp_detailed_obj["BadCropDirections"],temp_detailed_obj["BadIrriDirections"]) detailed_report_sheet_obj[field_id] = temp_detailed_obj # print(summary_sheet_obj) # saveJsonDataToFile("dcmSheet.json", { # "summary": summary_sheet_obj, # "region": region_sheet_obj, # "detailed": detailed_report_sheet_obj # }) main_width = 18 print(no_data_fields_count, "no_data_fields_count") # exit() # def get_per(num, dem): # return int(float(num)*100/float(dem)) fileNameSuffix = '' if fileNo==0 else f'-{fileNo}' workbook = xlsxwriter.Workbook(f'DCM-Data{fileNameSuffix}.xlsx') workbook.formats[0].set_font_size(12) format_green = workbook.add_format({'font_color': 'black'}) format_orange = workbook.add_format({'font_color': 'orange'}) format_purple = workbook.add_format({'font_color': 'purple'}) format_red = workbook.add_format({'font_color': 'red'}) 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}) # Returns sheet cell format based on value and type # # ARGs - type: the type of decision logic; val: the value of the field # type 0: green-low, orange-mid, red-high # type 1: red- < 30, orange- 30 - 60, green- > 60 def get_format_type(val, type=0): if type == 0: if val == "low": return format_green elif val == "mid": return format_orange elif val == "high": return format_red elif type == 1: if float(val) < 30: return format_red elif float(val) < 60: return format_orange else: return format_green return format_green # returns sheet cell format based on value # ; red if val is not 'yes' and float > 20; else green def get_format_type2(val): try: if float(val) > 20: return format_red except: try: if val.lower() == "yes": return format_red except: return format_green # 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 # returns both lang and en words # ; except if onlyLang is True or lang is en def getTrans(key, onlyLang=False, lang=lang, canReturnKey=False): if key not in enTrans: if canReturnKey: return key return "" if lang=="en": return enTrans[key] if onlyLang: return langTrans[key] return f'{langTrans[key]} - {enTrans[key]}' def getTransFormat(keys, format): if format==None or len(keys)==0: return "" if len(keys)==1: return getTrans(keys[0]) if isEn: for i,key in enumerate(keys): mEnTrans = getTrans(key, onlyLang=True) format = format.replace("{%s}" % (i), mEnTrans) else: format = format+" - "+format # print(format) for i,key in enumerate(keys): mEnTrans = getTrans(key, onlyLang=True, lang="en") mLangTrans = getTrans(key, onlyLang=True) # print(mEnTrans, mLangTrans) format = format.replace("{%s}" % (i+1), mLangTrans, 1) format = format.replace("{%s}" % (i+1), mEnTrans, 1) # print(format) return format totalAreaTrans = getTransFormat(["totalArea", "hectareAbv"], "{1} ({2})") # print(totalAreaTrans) # return percentTrans = getTrans("in%") inHaTrans = getTrans("inHa") totalGoodTrans = getTrans("totalBad") totalBadTrans = getTrans("totalGood") irriReqTrans = getTrans("irriRequired") fertReqTrans = getTrans("fertilizerRequired") clusterTrans = getTrans("district") leaderNameTrans = getTrans("leaderName") leaderPhoneTrans = getTrans("leaderPhoneNo") regionTrans = getTrans("village") worksheet = workbook.add_worksheet(getTrans("summaryReport", onlyLang=True)) worksheet.freeze_panes(4,0) worksheet.merge_range('A1:J1', getTrans("summaryReport"), blue_bg_merge) worksheet.merge_range('A3:A4', clusterTrans, gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', totalAreaTrans, gray_bg_merge_with_top_border) worksheet.merge_range('C3:D3', totalBadTrans, gray_bg_merge_with_top_border) worksheet.merge_range('E3:F3', totalGoodTrans, gray_bg_merge_with_top_border) worksheet.merge_range('G3:H3', irriReqTrans, gray_bg_merge_with_top_border) worksheet.merge_range('I3:J3', fertReqTrans, gray_bg_merge_with_top_border) worksheet.write(3,2,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,3, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,4,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,5, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,6,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,7, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,8,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,9, percentTrans, gray_bg_merge_with_top_border) all_rows = [] all_rows.append([]) # first_row = ["District", "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(getTrans("total")) for key in range (1, len(all_rows[1])): sum_row.append(0) #print(sum_row) for key in range(0, len(all_rows)): temp_row = all_rows[key] for j in range(0, len(temp_row)): if key > 0 and j > 0: sum_row[j] = sum_row[j] + temp_row[j] if j in [5,7,9]: worksheet.write((key+3),j,temp_row[j],get_format_type2(temp_row[j])) elif j ==3: worksheet.write((key+3),j,temp_row[j],get_format_type2((100-float(temp_row[j])))) else: worksheet.write((key+3),j,temp_row[j],white_bg_cell) 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 key in range(0,len(sum_row)): worksheet.write((len(all_rows)+3),key, sum_row[key],bold) worksheet.set_column(0,9, main_width) worksheet = workbook.add_worksheet(getTrans("regionWiseReport", onlyLang=True)) worksheet.freeze_panes(4,0) worksheet.merge_range('A1:M1', getTrans("regionWiseReport"), blue_bg_merge) worksheet.merge_range('A3:A4', clusterTrans, gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', getTrans("region"), gray_bg_merge_with_top_border) worksheet.merge_range('C3:C4', leaderNameTrans, gray_bg_merge_with_top_border) worksheet.merge_range('D3:D4', leaderPhoneTrans, gray_bg_merge_with_top_border) worksheet.merge_range('E3:E4', totalAreaTrans, gray_bg_merge_with_top_border) worksheet.merge_range('F3:G3', totalGoodTrans, gray_bg_merge_with_top_border) worksheet.merge_range('H3:I3', totalBadTrans, gray_bg_merge_with_top_border) worksheet.merge_range('J3:K3', irriReqTrans, gray_bg_merge_with_top_border) worksheet.merge_range('L3:M3', fertReqTrans, gray_bg_merge_with_top_border) worksheet.write(3,5,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,6, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,7,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,8, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,9,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,10, percentTrans, gray_bg_merge_with_top_border) worksheet.write(3,11,inHaTrans, gray_bg_merge_with_top_border) worksheet.write(3,12, percentTrans, gray_bg_merge_with_top_border) all_rows = [] all_rows.append([]) all_rows = [] # first_row = ["District", "Village", "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(getTrans("total")) all_sum_row.append('') all_sum_row.append('') all_sum_row.append('') for key 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((getTrans("subtotal")+": " + cluster.title())) cluster_sum_row.append('') cluster_sum_row.append('') for key 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"]] #new_row = [cluster,region,q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], irrigation_required, fertilizer_required] all_rows.append(new_row) #add sum of the cluster values for k in range(4, len(new_row)): #print(new_row[k]) try: cluster_sum_row[k] = cluster_sum_row[k] + float(new_row[k]) all_sum_row[k] = all_sum_row[k] + float(new_row[k]) except: qqqq = 1 #write_data_rows for j in range(0, len(new_row)): if j in [8,10,12]: worksheet.write(row_num,j,new_row[j],get_format_type2(new_row[j])) elif j == 6: worksheet.write(row_num,j,new_row[j],get_format_type2((100-float(new_row[j])))) else: worksheet.write(row_num,j,new_row[j],white_bg_cell) worksheet.set_row(row_num, None, None, {'level':2, 'hidden': True}) 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_merge2) #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 key in range(0,len(cluster_sum_row)): worksheet.write((row_num),key, cluster_sum_row[key],gray_bg_cell_bold) worksheet.set_row(row_num, None, None, {'level':1, 'collapsed':True}) 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 key in range(0,len(all_sum_row)): worksheet.write((row_num),key, all_sum_row[key],yellow_bg_cell_bold) worksheet.set_column(0,(len(all_rows[1])-1), main_width) # Columns - WebURL, Cluster, Region, FieldID, FarmerName, FarmerID, FarmerPhoneNumber, SateliteVisitDate, RegionLeader, LeaderPhoneNumber, TotalArea, IrriStatusLevel, CropStatusLevel, IrriIndexVal, CropIndexVal, BadIrriDirections, BadCropDirections worksheet = workbook.add_worksheet(getTrans("detailedReport", onlyLang=True)) worksheet.freeze_panes(4,0) worksheet.merge_range('A1:Q1', getTrans("detailedReport"), blue_bg_merge) worksheet.merge_range('A3:A4', getTrans("Field URL", canReturnKey=True), gray_bg_merge_with_top_border) worksheet.merge_range('B3:B4', clusterTrans, gray_bg_merge_with_top_border) worksheet.merge_range('C3:C4', regionTrans, gray_bg_merge_with_top_border) worksheet.merge_range('D3:D4', getTrans("Field ID"), gray_bg_merge_with_top_border) worksheet.merge_range('E3:E4', getTrans("farmerName"), gray_bg_merge_with_top_border) worksheet.merge_range('F3:F4', getTrans("Ryot ID"), gray_bg_merge_with_top_border) worksheet.merge_range('G3:G4', getTrans("farmerPhoneNo"), gray_bg_merge_with_top_border) worksheet.merge_range('H3:H4', getTrans("satelliteVisitDate"), gray_bg_merge_with_top_border) worksheet.merge_range('I3:I4', leaderNameTrans, gray_bg_merge_with_top_border) worksheet.merge_range('J3:J4', leaderPhoneTrans, gray_bg_merge_with_top_border) worksheet.merge_range('K3:K4', totalAreaTrans, gray_bg_merge_with_top_border) worksheet.merge_range("L3:L4","Irrigation Stress", gray_bg_merge_with_top_border) worksheet.merge_range("M3:M4", "Crop Health Stress", gray_bg_merge_with_top_border) worksheet.merge_range('N3:N4', "Irrigation Index Value", gray_bg_merge_with_top_border) worksheet.merge_range('O3:O4', "Crop Health Index Value", gray_bg_merge_with_top_border) worksheet.merge_range('P3:P4', "Bad Irrigation Directions", gray_bg_merge_with_top_border) worksheet.merge_range('Q3:Q4', "Bad Crop Directions", 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,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,10, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,11,inHaTrans, gray_bg_merge_with_top_border) #worksheet.write(3,11, "Fertilizer Required", gray_bg_merge_with_top_border) # worksheet.write(3,12, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,13,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,14, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,15,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,16, percentTrans, gray_bg_merge_with_top_border) all_rows = [] # first_row = ["District", "Village", "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(getTrans("total")) all_sum_row += 9 * [''] all_sum_row += 1 * [0] all_sum_row += 2 * [''] all_sum_row += 2 * [0] all_sum_row += 2 * [''] 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 += 2 * [''] cluster_sum_row.append((getTrans("subtotal")+": " + cluster.title())) cluster_sum_row += 7 * [''] cluster_sum_row += 1 * [0] cluster_sum_row += 2 * [''] cluster_sum_row += 2 * [0] cluster_sum_row += 2 * [''] #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 += 2 * [''] region_sum_row.append((getTrans("subtotal")+": " + region.title())) region_sum_row += 7 * [''] region_sum_row += 1 * [0] region_sum_row += 2 * [''] region_sum_row += 2 * [0] region_sum_row += 2 * [''] #print(region_sum_row) this_region_num = 0 for(key1, key_obj) in regionObj.items(): for(field_id, 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 main_sensed_day = str(q["SatelliteVisitDate"]) sensed_day_yyyymmdd = main_sensed_day[6:] + main_sensed_day[3:5] + main_sensed_day[:2] web_url = "https://sat.farmonaut.com?UID="+ str(uid)+"&FieldID=" + str(field_id) + "&SensedDay=" + sensed_day_yyyymmdd irrigation_status = "low" fertilizer_status = "low" # printfloat(q["TotalBadIrrigationAreaPercentage"]), if int(q["IrriIndexVal"]) < 18:#if float(q["TotalBadIrrigationAreaPercentage"]) > 70: irrigation_status = "high" elif int(q["IrriIndexVal"]) > 18 and int(q["IrriIndexVal"]) < 43:# float(q["TotalBadIrrigationAreaPercentage"]) > 30: irrigation_status = "mid" if int(q["CropIndexVal"]) < 18: #float(q["BadHealthAreaPercentage"]) > 70: fertilizer_status = "high" elif int(q["CropIndexVal"]) > 18 and int(q["CropIndexVal"]) < 43: #float(q["BadHealthAreaPercentage"]) > 30: fertilizer_status = "mid" # print(ndvi, ndwi, irrigation_status, fertilizer_status) # total elements in new_row is 17 new_row = [web_url, q["Cluster"], q["Region"], q["OrgFieldID"], q["FarmerName"], q["FarmerID"], q["FarmerPhoneNumber"], q["SatelliteVisitDate"], q["RegionLeader"], q["LeaderPhoneNumber"], q["TotalArea"], irrigation_status, fertilizer_status, q["IrriIndexVal"], q["CropIndexVal"], q["BadIrriDirections"], q["BadCropDirections"]] all_rows.append(new_row) # add sum of the cluster values, whichever is possible; index 10 is TotalArea for k in range(10, len(new_row)): #print(new_row[k]) try: 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]) except: faltuVariable = 1 # added just because something need to be added #print(traceback.format_exc()) ##### 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: if j in [11,12]: worksheet.write(row_num, j, new_row[j], get_format_type(new_row[j], 0)) elif j in [14,13]: try: worksheet.write(row_num, j, new_row[j], get_format_type2(new_row[j], 1)) except: #print(traceback.format_exc()) worksheet.write(row_num,j,new_row[j],get_format_type2(new_row[j])) else: worksheet.write(row_num, j, new_row[j]) worksheet.set_row(row_num, None, None, {'level':2, 'hidden': True}) 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[13] = region_sum_row[13]/len(key_obj) # calculating avg region_sum_row[14] = region_sum_row[14]/len(key_obj) for key in range(0,len(region_sum_row)): worksheet.write((row_num),key, region_sum_row[key],gray_bg_cell_bold) worksheet.set_row(row_num, None, None, {'level':1, 'collapsed': True}) 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[13] = cluster_sum_row[13]/len(key_obj) # calculating avg cluster_sum_row[14] = cluster_sum_row[14]/len(key_obj) for key in range(0,len(cluster_sum_row)): worksheet.write((row_num),key, cluster_sum_row[key],yellow_bg_cell_bold) row_num = row_num +1 #write all sum row all_sum_row[13] = all_sum_row[13]/len(key_obj) # calculating avg all_sum_row[14] = all_sum_row[14]/len(key_obj) for key in range(0,len(all_sum_row)): worksheet.write((row_num),key, all_sum_row[key],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() # Returns boolean value def check_field_obj_correct(field_obj, uid): if uid in ["VKXzYbNY89YD7UePtUL5KbxLYTE3"]: return field_obj != None and len(field_obj) > 0 return field_obj != None and field_obj.get("OrgData") != None def makeClusterSheet2(uid, isTest=False, fileNo=0): ##### init firebase app for later usage initFirebase(isTest) ##### get all fields data and extract useful ones # this list is used to set default value as 0 values_to_set = ["BadHealthArea", "BadHealthAreaPecentage", "BadIrrigationArea", "BadIrrigationAreaPercentage", "TotalGoodArea", "TotalGoodAreaPercentage"] uidToFormatNo = { "VKXzYbNY89YD7UePtUL5KbxLYTE3": 2 } ##### get translations lang = "en" isEn = lang == "en" enTrans = getTranslations("en", isTest) langTrans = getTranslations(lang, isTest) if not isEn else enTrans org_fields = getUserFields(uid, isTest) summary_sheet_obj = {} region_sheet_obj = {} detailed_report_sheet_obj = {} no_data_fields_count = 0 for (field_id, field_obj) in org_fields.items(): ###### checking if data present for the field sensed_days = field_obj.get("SensedDays", None) if sensed_days is not None: latest_day = max(list(sensed_days.keys())) else: latest_day = None if not check_field_obj_correct(field_obj, uid): no_data_fields_count = no_data_fields_count + 1 fieldArea = field_obj.get("FieldArea", 0) if check_field_obj_correct(field_obj, uid) and latest_day is not None and fieldArea > 110: ###### start getting useful data cluster = getOrgValueByKey(field_obj, "Cluster", formatNo=uidToFormatNo.get(uid, 1)) farmerName = getOrgValueByKey(field_obj, "FarmerName", emptyCellValue, formatNo=uidToFormatNo.get(uid, 1)) regionVal = getOrgValueByKey(field_obj, "Region", emptyCellValue, formatNo=uidToFormatNo.get(uid, 1)) regionLeader = getOrgValueByKey(field_obj, "RegionLeader", emptyCellValue, formatNo=uidToFormatNo.get(uid, 1)) farmerNo = get_phone_number( getOrgValueByKey(field_obj, "FarmerPhoneNumber", "", formatNo=uidToFormatNo.get(uid, 1)), getOrgValueByKey(field_obj, "FarmerPhoneNumber2", "", formatNo=uidToFormatNo.get(uid, 1)), emptyCellValue ) farmerID = getOrgValueByKey(field_obj, "FarmerID", emptyCellValue, formatNo=uidToFormatNo.get(uid, 1)) orgFieldID = getOrgValueByKey(field_obj, "OrgFieldID", emptyCellValue, formatNo=uidToFormatNo.get(uid, 1)) try: fert_zones, irri_zones = 0,0 latestDayData = sensed_days[latest_day] cropRedZones = latestDayData.get("cropRedZonesMajor", latestDayData.get("cropRedZones")) if type(latestDayData) != str else None irrigationRedZones = latestDayData.get("irrigationRedZonesMajor", latestDayData.get("irrigationRedZones")) if type(latestDayData) != str else None ndviVal = field_obj["Health"]["ndvi"].get(latest_day, 0) ndwiVal = field_obj["Health"]["ndwi"].get(latest_day, 0) try: for tt in cropRedZones: fert_zones = fert_zones+1 except: fert_zones = 0 try: for pp in irrigationRedZones: irri_zones = irri_zones+1 except: irri_zones = 0 ndvi = round(float(100*(9-fert_zones)/9)) ndwi = round(float(100*(9-irri_zones)/9)) def getLocalDir(zone): return getLocalDirAbrByZone(zone, langTrans, lang, False) # print("zones", cropRedZones, irrigationRedZones) cropRedZoneStr = (", ".join(list(map(getLocalDir, cropRedZones)))) if (cropRedZones != None and type(cropRedZones) == list and len(cropRedZones) != 0) else emptyCellValue irrigationRedZoneStr = (", ".join(list(map(getLocalDir, irrigationRedZones)))) if (irrigationRedZones != None and type(irrigationRedZones) == list and len(irrigationRedZones) != 0) else emptyCellValue #print("zones", cropRedZoneStr, irrigationRedZoneStri) except: print(traceback.format_exc()) ndvi, ndwi, ndviVal, ndwiVal = 0,0,0,0 cropRedZoneStr, irrigationRedZoneStr = emptyCellValue, emptyCellValue # print("zones", cropRedZoneStr, irrigationRedZoneStr) # print([field_id, latest_day, ndvi, ndwi]) latest_day = str(latest_day) latest_day = latest_day[6:] + "-" + latest_day[4:6] + "-" + latest_day[0:4] region_leader_number = "-" try: region_leader_number = int(region_leader_number) except: region_leader_number = region_leader_number ###### make summary_sheet_obj temp_summary_sheet_obj = summary_sheet_obj.get(cluster, {}) temp_summary_sheet_obj["TotalFarms"] = add_farms_num( temp_summary_sheet_obj, "TotalFarms", 1 ) temp_summary_sheet_obj["TotalArea"] = round( 100*add_area_value(temp_summary_sheet_obj, "TotalArea", fieldArea) )/100 temp_summary_sheet_obj["SatelliteVisitDate"] = latest_day for key in values_to_set: temp_summary_sheet_obj[key] = temp_summary_sheet_obj.get(key, 0) fieldBadCropArea = (1-ndvi/55)*fieldArea fieldBadIrriArea = (1-ndwi/50)*fieldArea if fieldBadCropArea < 0: fieldBadCropArea = 0 if fieldBadIrriArea < 0: fieldBadIrriArea = 0 temp_summary_sheet_obj["BadHealthArea"] = round( 100 * add_area_value( temp_summary_sheet_obj, "BadHealthArea", fieldBadCropArea ) )/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_area_value(temp_summary_sheet_obj, "BadIrrigationArea", fieldBadIrriArea))/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[cluster] = temp_summary_sheet_obj ###### make region_sheet_obj try: temp_region_sheet_obj = region_sheet_obj[regionVal] except: temp_region_sheet_obj = {} for key in values_to_set: temp_region_sheet_obj[key] = temp_region_sheet_obj.get(key, 0) temp_region_sheet_obj["Cluster"] = cluster temp_region_sheet_obj["RegionLeader"] = 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"] = float(add_area_value(temp_region_sheet_obj, "TotalArea", fieldArea)) # print("console", fieldArea, temp_region_sheet_obj["TotalArea"]) temp_region_sheet_obj["SatelliteVisitDate"] = latest_day # if get_heath_status(ndvi) == "bad": temp_region_sheet_obj["BadHealthArea"] = round( 100 * add_area_value( temp_region_sheet_obj, "BadHealthArea", fieldBadCropArea ) )/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_area_value(temp_region_sheet_obj, "BadIrrigationArea", fieldBadIrriArea))/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[regionVal] = temp_region_sheet_obj ###### make detailed_report_sheet_obj temp_detailed_obj = {} temp_detailed_obj["FieldID"]= field_id temp_detailed_obj["OrgFieldID"]= orgFieldID temp_detailed_obj["Cluster"] = cluster temp_detailed_obj["Region"] = regionVal temp_detailed_obj["FarmerID"] = farmerID temp_detailed_obj["FarmerName"] = farmerName temp_detailed_obj["FarmerPhoneNumber"] = farmerNo temp_detailed_obj["RegionLeader"] = regionLeader temp_detailed_obj["LeaderPhoneNumber"] = region_leader_number temp_detailed_obj["TotalArea"] = add_area_value(temp_detailed_obj, "TotalArea", fieldArea) temp_detailed_obj["SatelliteVisitDate"] = latest_day ndvi,ndwi = int(ndvi),int(ndwi) bad_health_area = round( 100 * add_area_value( {}, "BadHealthArea", fieldBadCropArea ) )/100 bad_health_per = 100*round( 100 * bad_health_area / temp_detailed_obj["TotalArea"] )/100 bad_irr_area = round( 100 * add_area_value( {}, "BadIrrigationArea", fieldBadIrriArea ) )/100 bad_irr_per = 100*round(100*bad_irr_area/temp_detailed_obj["TotalArea"])/100 total_good_area = round(100*(fieldArea/10000 - (bad_health_area + bad_irr_area) + (bad_irr_area*bad_health_area/(fieldArea/10000))))/100 total_bad_area = round(100*(bad_health_area+bad_irr_area+ total_good_area - fieldArea/10000))/100 temp_detailed_obj["BadHealthArea"] = round(bad_health_area*100)/100 temp_detailed_obj["BadHealthAreaPercentage"] = bad_health_per temp_detailed_obj["BadIrrigationArea"] = round(bad_irr_area*100)/100 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/(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/(fieldArea/10000)) temp_detailed_obj["BadCropDirections"] = cropRedZoneStr temp_detailed_obj["BadIrriDirections"] = irrigationRedZoneStr temp_detailed_obj["CropIndexVal"] = ndvi temp_detailed_obj["IrriIndexVal"] = ndwi #print(temp_detailed_obj["BadCropDirections"],temp_detailed_obj["BadIrriDirections"]) detailed_report_sheet_obj[field_id] = temp_detailed_obj # print(summary_sheet_obj) # saveJsonDataToFile("dcmSheet.json", { # "summary": summary_sheet_obj, # "region": region_sheet_obj, # "detailed": detailed_report_sheet_obj # }) print(no_data_fields_count, "no_data_fields_count") # exit() # def get_per(num, dem): # return int(float(num)*100/float(dem)) fileNameSuffix = '' if fileNo==0 else f'-{fileNo}' workbook = xlsxwriter.Workbook(f'DCM-Data{fileNameSuffix}.xlsx') workbook.formats[0].set_font_size(12) format_green = workbook.add_format({'font_color': 'black'}) format_orange = workbook.add_format({'font_color': 'orange'}) format_purple = workbook.add_format({'font_color': 'purple'}) format_red = workbook.add_format({'font_color': 'red'}) 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}) # Returns sheet cell format based on value and type # # ARGs - type: the type of decision logic; val: the value of the field # type 0: green-low, orange-mid, red-high # type 1: red- < 30, orange- 30 - 60, green- > 60 def get_format_type(val, type=0): if type == 0: if val == "low": return format_green elif val == "mid": return format_orange elif val == "high": return format_red elif type == 1: if float(val) < 30: return format_red elif float(val) < 60: return format_orange else: return format_green return format_green # returns sheet cell format based on value # ; red if val is not 'yes' and float > 20; else green def get_format_type2(val): try: if float(val) > 20: return format_red except: try: if val.lower() == "yes": return format_red except: return format_green # 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 # returns both lang and en words # ; except if onlyLang is True or lang is en def getTrans(key, onlyLang=True, lang=lang, canReturnKey=True): if key not in enTrans: if canReturnKey: return key return "" if lang=="en": return enTrans.get(key, key) if onlyLang: return langTrans.get(key, key) return f'{langTrans.get(key, key)} - {enTrans.get(key, key)}' def getTransFormat(keys, format): if format==None or len(keys)==0: return "" if len(keys)==1: return getTrans(keys[0]) if isEn: for i,key in enumerate(keys): mEnTrans = getTrans(key, onlyLang=True) format = format.replace("{%s}" % (i+1), mEnTrans) else: format = format+" - "+format # print(format) for i,key in enumerate(keys): mEnTrans = getTrans(key, onlyLang=True, lang="en") mLangTrans = getTrans(key, onlyLang=True) # print(mEnTrans, mLangTrans) format = format.replace("{%s}" % (i+1), mLangTrans, 1) format = format.replace("{%s}" % (i+1), mEnTrans, 1) # print(format) return format totalAreaTrans = getTransFormat(["totalArea", "hectareAbv"], "{1} ({2})") # print(totalAreaTrans) # return percentTrans = getTrans("in%") inHaTrans = getTrans("inHa") totalGoodTrans = getTrans("totalBad") totalBadTrans = getTrans("totalGood") irriReqTrans = getTrans("irriRequired") fertReqTrans = getTrans("fertilizerRequired") clusterTrans = getTrans("district") leaderNameTrans = getTrans("leaderName") leaderPhoneTrans = getTrans("leaderPhoneNo") regionTrans = getTrans("village") # worksheet = workbook.add_worksheet(getTrans("summaryReport", onlyLang=True)) # worksheet.freeze_panes(4,0) # worksheet.merge_range('A1:J1', getTrans("summaryReport"), blue_bg_merge) # worksheet.merge_range('A3:A4', clusterTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('B3:B4', totalAreaTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('C3:D3', totalBadTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('E3:F3', totalGoodTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('G3:H3', irriReqTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('I3:J3', fertReqTrans, gray_bg_merge_with_top_border) # worksheet.write(3,2,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,3, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,4,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,5, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,6,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,7, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,8,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,9, percentTrans, gray_bg_merge_with_top_border) # all_rows = [] # all_rows.append([]) # # first_row = ["District", "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(getTrans("total")) # for key in range (1, len(all_rows[1])): # sum_row.append(0) # #print(sum_row) # for key in range(0, len(all_rows)): # temp_row = all_rows[key] # for j in range(0, len(temp_row)): # if key > 0 and j > 0: # sum_row[j] = sum_row[j] + temp_row[j] # if j in [5,7,9]: # worksheet.write((key+3),j,temp_row[j],get_format_type2(temp_row[j])) # elif j ==3: # worksheet.write((key+3),j,temp_row[j],get_format_type2((100-float(temp_row[j])))) # else: # worksheet.write((key+3),j,temp_row[j],white_bg_cell) # 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 key in range(0,len(sum_row)): # worksheet.write((len(all_rows)+3),key, sum_row[key],bold) # worksheet.set_column(0,9, main_width) # worksheet = workbook.add_worksheet(getTrans("regionWiseReport", onlyLang=True)) # worksheet.freeze_panes(4,0) # worksheet.merge_range('A1:M1', getTrans("regionWiseReport"), blue_bg_merge) # worksheet.merge_range('A3:A4', clusterTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('B3:B4', getTrans("region"), gray_bg_merge_with_top_border) # worksheet.merge_range('C3:C4', leaderNameTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('D3:D4', leaderPhoneTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('E3:E4', totalAreaTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('F3:G3', totalGoodTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('H3:I3', totalBadTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('J3:K3', irriReqTrans, gray_bg_merge_with_top_border) # worksheet.merge_range('L3:M3', fertReqTrans, gray_bg_merge_with_top_border) # worksheet.write(3,5,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,6, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,7,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,8, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,9,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,10, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,11,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,12, percentTrans, gray_bg_merge_with_top_border) # all_rows = [] # all_rows.append([]) # all_rows = [] # # first_row = ["District", "Village", "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(getTrans("total")) # all_sum_row.append('') # all_sum_row.append('') # all_sum_row.append('') # for key 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((getTrans("subtotal")+": " + cluster.title())) # cluster_sum_row.append('') # cluster_sum_row.append('') # for key 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"]] # #new_row = [cluster,region,q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], irrigation_required, fertilizer_required] # all_rows.append(new_row) # #add sum of the cluster values # for k in range(4, len(new_row)): # #print(new_row[k]) # try: # cluster_sum_row[k] = cluster_sum_row[k] + float(new_row[k]) # all_sum_row[k] = all_sum_row[k] + float(new_row[k]) # except: # qqqq = 1 # #write_data_rows # for j in range(0, len(new_row)): # if j in [8,10,12]: # worksheet.write(row_num,j,new_row[j],get_format_type2(new_row[j])) # elif j == 6: # worksheet.write(row_num,j,new_row[j],get_format_type2((100-float(new_row[j])))) # else: # worksheet.write(row_num,j,new_row[j],white_bg_cell) # worksheet.set_row(row_num, None, None, {'level':2, 'hidden': True}) # 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_merge2) # #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 key in range(0,len(cluster_sum_row)): # worksheet.write((row_num),key, cluster_sum_row[key],gray_bg_cell_bold) # worksheet.set_row(row_num, None, None, {'level':1, 'collapsed':True}) # 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 key in range(0,len(all_sum_row)): # worksheet.write((row_num),key, all_sum_row[key],yellow_bg_cell_bold) # worksheet.set_column(0,(len(all_rows[1])-1), main_width) # Columns - FieldID, RyotID, FarmerName, SateliteVisitDate, TotalArea, IrriStatusLevel, CropStatusLevel, IrriIndexVal, CropIndexVal, BadIrriDirections, BadCropDirections worksheet3 = workbook.add_worksheet(getTrans("detailedReport", onlyLang=True)) worksheet3.freeze_panes(1,0) # worksheet3.merge_range('A1:Q1', getTrans("detailedReport"), blue_bg_merge) worksheet3.write(0, 0, getTrans("Field ID", canReturnKey=True), gray_bg_merge_with_top_border) worksheet3.write(0, 1, getTrans("Ryot ID"), gray_bg_merge_with_top_border) worksheet3.write(0, 2, getTrans("farmerName"), gray_bg_merge_with_top_border) # worksheet3.write(0, 3,'D3:D4', getTrans("Field ID"), gray_bg_merge_with_top_border) # worksheet3.write(0, 3,'G3:G4', getTrans("farmerPhoneNo"), gray_bg_merge_with_top_border) worksheet3.write(0, 3, getTrans("satelliteVisitDate"), gray_bg_merge_with_top_border) # worksheet3.write(0, 3,'I3:I4', leaderNameTrans, gray_bg_merge_with_top_border) # worksheet3.write(0, 3,'J3:J4', leaderPhoneTrans, gray_bg_merge_with_top_border) worksheet3.write(0, 4, totalAreaTrans, gray_bg_merge_with_top_border) worksheet3.write(0, 5,"Irrigation Stress", gray_bg_merge_with_top_border) worksheet3.write(0, 6, "Crop Health Stress", gray_bg_merge_with_top_border) worksheet3.write(0, 7, "Irrigation Index Value", gray_bg_merge_with_top_border) worksheet3.write(0, 8, "Crop Health Index Value", gray_bg_merge_with_top_border) worksheet3.write(0, 9, "Bad Irrigation Directions", gray_bg_merge_with_top_border) worksheet3.write(0, 10, "Bad Crop Directions", 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,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,10, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,11,inHaTrans, gray_bg_merge_with_top_border) #worksheet.write(3,11, "Fertilizer Required", gray_bg_merge_with_top_border) # worksheet.write(3,12, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,13,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,14, percentTrans, gray_bg_merge_with_top_border) # worksheet.write(3,15,inHaTrans, gray_bg_merge_with_top_border) # worksheet.write(3,16, percentTrans, gray_bg_merge_with_top_border) all_rows = [] # first_row = ["District", "Village", "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(getTrans("total")) all_sum_row += 9 * [''] all_sum_row += 1 * [0] all_sum_row += 2 * [''] all_sum_row += 2 * [0] all_sum_row += 2 * [''] row_num = 1 for(cluster, clusterObj) in new_detailed_sheet.items(): this_cluster_num = 0 #initate cluster_sum_row cluster_sum_row = [] cluster_sum_row += 2 * [''] cluster_sum_row.append((getTrans("subtotal")+": " + cluster.title())) cluster_sum_row += 7 * [''] cluster_sum_row += 1 * [0] cluster_sum_row += 2 * [''] cluster_sum_row += 2 * [0] cluster_sum_row += 2 * [''] #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 += 2 * [''] region_sum_row.append((getTrans("subtotal")+": " + region.title())) region_sum_row += 7 * [''] region_sum_row += 1 * [0] region_sum_row += 2 * [''] region_sum_row += 2 * [0] region_sum_row += 2 * [''] #print(region_sum_row) this_region_num = 0 for(key1, key_obj) in regionObj.items(): for(field_id, 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 main_sensed_day = str(q["SatelliteVisitDate"]) sensed_day_yyyymmdd = main_sensed_day[6:] + main_sensed_day[3:5] + main_sensed_day[:2] web_url = "https://sat.farmonaut.com?UID="+ str(uid)+"&FieldID=" + str(field_id) + "&SensedDay=" + sensed_day_yyyymmdd irrigation_status = "low" fertilizer_status = "low" # printfloat(q["TotalBadIrrigationAreaPercentage"]), if int(q["IrriIndexVal"]) < 18:#if float(q["TotalBadIrrigationAreaPercentage"]) > 70: irrigation_status = "high" elif int(q["IrriIndexVal"]) > 18 and int(q["IrriIndexVal"]) < 43:# float(q["TotalBadIrrigationAreaPercentage"]) > 30: irrigation_status = "mid" if int(q["CropIndexVal"]) < 18: #float(q["BadHealthAreaPercentage"]) > 70: fertilizer_status = "high" elif int(q["CropIndexVal"]) > 18 and int(q["CropIndexVal"]) < 43: #float(q["BadHealthAreaPercentage"]) > 30: fertilizer_status = "mid" # print(ndvi, ndwi, irrigation_status, fertilizer_status) # total elements in new_row is 11 new_row = [q["OrgFieldID"], q["FarmerID"], q["FarmerName"], q["SatelliteVisitDate"], q["TotalArea"], irrigation_status, fertilizer_status, q["IrriIndexVal"], q["CropIndexVal"], q["BadIrriDirections"], q["BadCropDirections"]] all_rows.append(new_row) # add sum of the cluster values, whichever is possible; index 4 is TotalArea # for k in range(4, len(new_row)): # #print(new_row[k]) # try: # 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]) # except: # faltuVariable = 1 # added just because something need to be added # #print(traceback.format_exc()) ##### write_data_rows for j in range(0, len(new_row)): if j in [5, 6]: worksheet3.write(row_num, j, new_row[j], get_format_type(new_row[j], 0)) elif j in [7, 8]: try: worksheet3.write(row_num, j, new_row[j], get_format_type2(new_row[j], 1)) except: #print(traceback.format_exc()) worksheet3.write(row_num, j, new_row[j], get_format_type2(new_row[j])) else: worksheet3.write(row_num, j, new_row[j]) # worksheet3.set_row(row_num, None, None, {'level':0, 'hidden': False}) 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) # worksheet3.merge_range(m_range, region.upper(),white_bg_merge2) #write region sub total row # region_sum_row[13] = region_sum_row[13]/len(key_obj) # calculating avg # region_sum_row[14] = region_sum_row[14]/len(key_obj) # for key in range(0,len(region_sum_row)): # worksheet3.write((row_num),key, region_sum_row[key],gray_bg_cell_bold) # worksheet3.set_row(row_num, None, None, {'level':1, 'collapsed': True}) # 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) # worksheet3.merge_range(m_range, cluster.upper(),white_bg_merge2) #write cluster sub total row # cluster_sum_row[13] = cluster_sum_row[13]/len(key_obj) # calculating avg # cluster_sum_row[14] = cluster_sum_row[14]/len(key_obj) # for key in range(0,len(cluster_sum_row)): # worksheet3.write((row_num),key, cluster_sum_row[key],yellow_bg_cell_bold) # row_num = row_num +1 #write all sum row # all_sum_row[13] = all_sum_row[13]/len(key_obj) # calculating avg # all_sum_row[14] = all_sum_row[14]/len(key_obj) # for key in range(0,len(all_sum_row)): # worksheet3.write((row_num),key, all_sum_row[key],light_blue_bg_cell_bold) main_width = 18 worksheet3.set_column(0, max(0, len(all_rows[1]) - 1), main_width) filter_range = 'A1:K' + str(row_num) worksheet3.autofilter(filter_range) workbook.close() def get_storage_download(storage_path, file_path, bucket_name=BUCKET_NAME): storage_client = storage.Client() bucket = storage_client.get_bucket(bucket_name) blob = bucket.blob(storage_path) blob.download_to_filename(file_path) # def test(uid, isTest=True): # """Regenerate field redZone data for DCM fields # get index image as per cloudy # calculate redZone for the img # save it to DB # """ # initFirebase(isTest=isTest) # all_fields = getUserFields(uid, isTest=isTest) # # # for field_id, field_data in all_fields.items(): # def update_field_red_zone(field_data): # sensed_days = field_data.get("SensedDays", {}) # if len(sensed_days) == 0: # return # latest_date = max(sensed_days.keys()) # storage_path = "" # # # try: # loop = asyncio.get_event_loop() # except: # loop = asyncio.new_event_loop() # asyncio.set_event_loop(loop) # loop.run_until_complete( # asyncio.gather( # *(_rc_pause_sub_field(app1, uid, field_id) for field_id in field_ids) # ) # ) # uid = "M53J9SImW9My4vghlCaaWLYxsc22" #dcmID; # uid = 'mFFHQdEtiSbn2hbYQAwwoIdYVi02' #ttgID #uid= "HF9dSIoDEYcTwebrRWVzb08SQln2" # dcm loni uid = "VKXzYbNY89YD7UePtUL5KbxLYTE3" #masum grp, sunpure #makeClusterSheet(uid, isTest=False, fileNo=2) makeClusterSheet2(uid, isTest=False, fileNo=9) # test(uid)