# snake_case is used for variable names # creating specifically for DCM right now***** import json 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 from tempfile import tempdir import firebase_admin from firebase_admin import credentials from firebase_admin import db # from find_s1_image2 import find_img_value import traceback import json import json import xlsxwriter 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"] # try: # cred = credentials.Certificate("servicekey.json") # firebase_admin.initialize_app( # cred, {"databaseURL": "https://farmbase-b2f7e-31c0c.firebaseio.com/"} # ) # except: # print("fire running") 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"] # district region = detailedFieldObj["Region"] # village 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): if isTest: print("") else: 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) def vertMergePos(col, row, no): endRow = row + no - 1 return f"{col}{row}:{col}{endRow}" def horiMergePos(cols, row): col = cols[0] endCol = cols[1] return f"{col}{row}:{endCol}{row}" def horiNos(colsstr): col = colsstr[0] endCol = colsstr[1] return ord(col) - 96, ord(endCol) - 96 def makeClusterSheet(uid, isTest=False, fileNo=0): KEYS = { "farmerName": "farmerName", "farmerPhoneNo": "farmerPhoneNo", "fatherName": "fatherName", "satDate": "satelliteVisitDate", "isCloudy": "isCloudy", "leaderName": "leaderName", "leaderPhoneNo": "leaderPhoneNo", "totalArea": "totalArea", "totalBad": "totalBad", "irriReq": "Irrigation Required", "fertReq": "Fertilizer Required", } ######## 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", ] val_to_col = { "Cluster": "OrgDistrict", "Region": "OrgVillageName", "FarmerName": "OrgRyotName", "FatherName": "OrgFthHusName", "RegionLeader": "", "FarmerPhoneNumber": "OrgMobileNo", "FarmerPhoneNumber2": "OrgWhatsappNo", "PlotNumber":"OrgPlot_No" } emptyCellValue = "-" # don't use it for hyphen str 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 org_data = field_obj.get("OrgData", None) 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 org_data is None: no_data_fields_count = no_data_fields_count + 1 if org_data is not None and latest_day is not None: ####### start getting useful data cluster = org_data.get(val_to_col["Cluster"], emptyCellValue) farmerName = org_data.get(val_to_col["FarmerName"], emptyCellValue) fatherName = org_data.get(val_to_col["FatherName"], emptyCellValue) regionVal = org_data.get(val_to_col["Region"], emptyCellValue) regionLeader = org_data.get(val_to_col["RegionLeader"], emptyCellValue) plotNumber = org_data.get(val_to_col["PlotNumber"], emptyCellValue) print(("p_n: " +str(plotNumber))) farmerNo = get_phone_number( org_data.get("OrgMobileNo", ""), org_data.get("OrgWhatsappNo", ""), emptyCellValue, ) latestDayObj = sensed_days[latest_day] isCloudy = emptyCellValue if type(latestDayObj) == str else latestDayObj["isCloudy"] # try: # ndvi = int(field_obj["Health"]["ndvi"][latest_day]) # ndwi = int(field_obj["Health"]["ndwi"][latest_day]) # except: # ndvi, ndwi = 0, 0 try: fert_zones, irri_zones = 0, 0 cropRedZones = field_obj["SensedDays"][latest_day]["cropRedZones"] irrigationRedZones = field_obj["SensedDays"][latest_day][ "irrigationRedZones" ] clouds = field_obj["SensedDays"][latest_day]["isCloudy"] if cropRedZones != "empty": try: for tt in cropRedZones: fert_zones = fert_zones + 1 except: fert_zones = 0 if irrigationRedZones != "empty": try: for pp in irrigationRedZones: irri_zones = irri_zones + 1 except: irri_zones = 0 #if number of red zones is greater than 1, trigger alert ndvi = round(float(100 * (9 - fert_zones) / 9)) ndwi = round(float(100 * (9 - irri_zones) / 9)) except: print(traceback.format_exc()) ndvi, ndwi = 0, 0 fert_zones, irri_zones = 0, 0 #if clouds== False: # try: # ndvi = int(field_obj["Health"]["ndvi"][latest_day]) # ndwi = int(field_obj["Health"]["ndwi"][latest_day]) # except: # ndwi, ndvi = 0,0 print([field_id,clouds, 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", field_obj["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) * field_obj["FieldArea"] fieldBadIrriArea = (1 - ndwi / 50) * field_obj["FieldArea"] #fieldBadCropArea = fert_zones #fieldBadIrriArea = irri_zones 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"] = add_area_value( temp_region_sheet_obj, "TotalArea", field_obj["FieldArea"] ) 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_sheet_obj temp_detailed_obj = {} temp_detailed_obj["FieldID"] = field_id temp_detailed_obj["Cluster"] = cluster temp_detailed_obj["Region"] = regionVal temp_detailed_obj["FarmerName"] = farmerName temp_detailed_obj[KEYS["fatherName"]] = fatherName temp_detailed_obj[KEYS["isCloudy"]] = "Yes" if isCloudy else "No" temp_detailed_obj["FarmerPhoneNumber"] = farmerNo temp_detailed_obj["RegionLeader"] = regionLeader temp_detailed_obj["PlotNumber"] = plotNumber temp_detailed_obj["LeaderPhoneNumber"] = region_leader_number temp_detailed_obj["TotalArea"] = add_area_value( temp_detailed_obj, "TotalArea", field_obj["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 * ( field_obj["FieldArea"] / 10000 - (bad_health_area + bad_irr_area) + ( bad_irr_area * bad_health_area / (field_obj["FieldArea"] / 10000) ) ) ) / 100 ) total_bad_area = ( round( 100 * ( bad_health_area + bad_irr_area + total_good_area - field_obj["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 / (field_obj["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 / (field_obj["FieldArea"] / 10000) ) temp_detailed_obj["FertZones"] = fert_zones temp_detailed_obj["IrriZones"] = irri_zones 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)) ########## start filling data to the sheet fileNameSuffix = "" if fileNo == 0 else f"-{fileNo}" workbook = xlsxwriter.Workbook(f"DCM-Data{fileNameSuffix}.xlsx") workbook.formats[0].set_font_size(12) ######### created formats 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 def get_format_type2(val): 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"}) 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 # get translations lang = "hi" isEn = lang == "en" enTrans = getTranslations("en", isTest) langTrans = getTranslations(lang, isTest) if not isEn else enTrans def getTrans(key, onlyLang=False, lang=lang): if key not in enTrans: 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") totalBadTrans = getTrans("totalBad") totalGoodTrans = getTrans("totalGood") irriReqTrans = getTrans("irriRequired") fertReqTrans = getTrans("fertilizerRequired") clusterTrans = getTrans("district") leaderNameTrans = getTrans("leaderName") leaderPhoneTrans = getTrans("leaderPhoneNo") regionTrans = getTrans("village") ######### sheet1 filling 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", totalGoodTrans, gray_bg_merge_with_top_border) worksheet.merge_range("E3:F3", totalBadTrans, 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) # 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 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) ######### sheet2 filling worksheet2 = workbook.add_worksheet(getTrans("regionWiseReport", onlyLang=True)) worksheet2.freeze_panes(4, 0) worksheet2.merge_range("A1:M1", getTrans("regionWiseReport"), blue_bg_merge) worksheet2.merge_range("A3:A4", clusterTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("B3:B4", getTrans("region"), gray_bg_merge_with_top_border) worksheet2.merge_range("C3:C4", leaderNameTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("D3:D4", leaderPhoneTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("E3:E4", totalAreaTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("F3:G3", totalBadTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("H3:I3", totalGoodTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("J3:K3", irriReqTrans, gray_bg_merge_with_top_border) worksheet2.merge_range("L3:M3", fertReqTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 5, inHaTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 6, percentTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 7, inHaTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 8, percentTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 9, inHaTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 10, percentTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 11, inHaTrans, gray_bg_merge_with_top_border) worksheet2.write(3, 12, percentTrans, gray_bg_merge_with_top_border) all_rows2 = [] # 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_rows2.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"], ] print(new_row) # new_row = [cluster,region,q["RegionLeader"],q["LeaderPhoneNumber"], q["TotalArea"], irrigation_required, fertilizer_required] all_rows2.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]: worksheet2.write( row_num, j, new_row[j], get_format_type2(new_row[j]) ) elif j == 6: worksheet2.write( row_num, j, new_row[j], get_format_type2((100 - float(new_row[j]))), ) else: worksheet2.write(row_num, j, new_row[j], white_bg_cell) worksheet2.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) worksheet2.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)): worksheet2.write((row_num), key, cluster_sum_row[key], gray_bg_cell_bold) worksheet2.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)): worksheet2.write((row_num), key, all_sum_row[key], yellow_bg_cell_bold) worksheet2.set_column(0, (len(all_rows2[1]) - 1), main_width) ######### sheet3 filling colIndices = { KEYS["farmerName"]: "D", KEYS["farmerPhoneNo"]: "E", KEYS["fatherName"]: "F", KEYS["satDate"]: "G", KEYS["totalArea"]: "K", KEYS["isCloudy"]: "H", KEYS["leaderName"]: "I", KEYS["leaderPhoneNo"]: "J", KEYS["totalBad"]: "LM", } worksheet3 = workbook.add_worksheet(getTrans("detailedReport", onlyLang=True)) worksheet3.freeze_panes(4, 0) worksheet3.merge_range("A1:Q1", getTrans("detailedReport"), blue_bg_merge) worksheet3.merge_range( "A3:A4", getTrans("irriRequired"), gray_bg_merge_with_top_border ) worksheet3.merge_range("B3:B4", clusterTrans, gray_bg_merge_with_top_border) worksheet3.merge_range("C3:C4", regionTrans, gray_bg_merge_with_top_border) worksheet3.merge_range( "D3:D4", getTrans("farmerName"), gray_bg_merge_with_top_border ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["farmerPhoneNo"]], 3, 2), getTrans("farmerPhoneNo"), gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["fatherName"]], 3, 2), getTrans(KEYS["fatherName"]), gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["isCloudy"]], 3, 2), getTrans(KEYS["isCloudy"]), gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["satDate"]], 3, 2), getTrans("satelliteVisitDate"), gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["leaderName"]], 3, 2), leaderNameTrans, gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["leaderPhoneNo"]], 3, 2), leaderPhoneTrans, gray_bg_merge_with_top_border, ) worksheet3.merge_range( vertMergePos(colIndices[KEYS["totalArea"]], 3, 2), totalAreaTrans, gray_bg_merge_with_top_border, ) worksheet3.merge_range( "L3:M3", totalBadTrans, gray_bg_merge_with_top_border, ) # worksheet3.merge_range("L3:M3", totalGoodTrans, gray_bg_merge_with_top_border) # worksheet3.merge_range('N3:O3', irriReqTrans, gray_bg_merge_with_top_border) # worksheet3.merge_range('P3:Q3', fertReqTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,5,'in (Ha)', gray_bg_merge_with_top_border) # worksheet3.write(3,6, 'in %', gray_bg_merge_with_top_border) # worksheet3.write(3,7,'in (Ha)', gray_bg_merge_with_top_border) # worksheet3.write(3,8, 'in %', gray_bg_merge_with_top_border) # worksheet3.write(3,9,inHaTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,10, percentTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,11,inHaTrans, gray_bg_merge_with_top_border) irriFerNos = horiNos(colIndices[KEYS["totalBad"]]) worksheet3.write( 3, irriFerNos[0] - 1, "Irrigation Required", gray_bg_merge_with_top_border ) worksheet3.write( 3, irriFerNos[1] - 1, "Fertilizer Required", gray_bg_merge_with_top_border ) # worksheet3.write(3,11, "Fertilizer Required", gray_bg_merge_with_top_border) # worksheet3.write(3,12, percentTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,13,inHaTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,14, percentTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,15,inHaTrans, gray_bg_merge_with_top_border) # worksheet3.write(3,16, percentTrans, gray_bg_merge_with_top_border) # 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_rows3 = [] # all_rows3.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.append("") for kk in range(0, 8): all_sum_row.append("") for key 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((getTrans("subtotal") + ": " + cluster.title())) for kk in range(0, 7): 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 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((getTrans("subtotal") + ": " + region.title())) for kk in range(0, 7): region_sum_row.append("") for key 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 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://farmonaut.com/web-app?UID=" + str(uid) + "&FieldID=" + str(field_id) + "&SensedDay=" + sensed_day_yyyymmdd ) irrigation_required = "No" fertilizer_required = "No" #if ndwi < 35: # irrigation_required = "Yes" #if ndvi < 35: # fertilizer_required = "Yes" if q[KEYS["isCloudy"]] == "Yes": bad_irri_thres = 20 bad_health_thres = 25 fert_thres = 0 irri_thres = 1 else: bad_irri_thres = 20 bad_health_thres = 25 fert_thres = 1 irri_thres = 1 #if float(q["TotalBadIrrigationAreaPercentage"]) > bad_irri_thres: # irrigation_required = "Yes" #if float(q["BadHealthAreaPercentage"]) > bad_health_thres: # fertilizer_required = "Yes" if int(q["FertZones"]) > fert_thres: fertilizer_required = "Yes" if int(q["IrriZones"]) > irri_thres: irrigation_required = "Yes" if q[KEYS["isCloudy"]] == "No" and int(q["FertZones"]) > 7 and int(q["IrriZones"]) > 7: # and float(q["TotalBadIrrigationAreaPercentage"]) > (100-bad_irri_thres) and float(q["BadHealthAreaPercentage"]) > (100-bad_health_thres): irrigation_required = "very small crop or no crop found" fertilizer_required = "very small crop or no crop found" print(q["FertZones"], q["IrriZones"],[q["TotalBadIrrigationAreaPercentage"],q["BadHealthAreaPercentage"], q[KEYS["isCloudy"]], field_id,irrigation_required, fertilizer_required]) # 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 = [ web_url, q["Cluster"], q["Region"], q["FarmerName"], q["FarmerPhoneNumber"], q[KEYS["fatherName"]], q["SatelliteVisitDate"], q[KEYS["isCloudy"]], q["PlotNumber"], q["LeaderPhoneNumber"], q["TotalArea"], irrigation_required, fertilizer_required, ] # 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_rows3.append(new_row) # add sum of the cluster values 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: wwa = 1 # print(traceback.format_exc()) # write_data_rows for j in range(0, len(new_row)): if j == 0: worksheet3.write_url( row_num, j, new_row[j], string="See Farm On Map" ) else: if j in [14, 16]: worksheet3.write( row_num, j, new_row[j], get_format_type2(new_row[j]) ) elif j in [11, 12]: try: worksheet3.write( row_num, j, new_row[j], get_format_type2((100 - float(new_row[j]))), ) 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": 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) worksheet3.merge_range(m_range, region.upper(), white_bg_merge2) # write region sub total row region_sum_row[12] = round(100 * region_sum_row[11] / region_sum_row[10]) region_sum_row[14] = round(100 * region_sum_row[13] / region_sum_row[10]) region_sum_row[16] = round(100 * region_sum_row[15] / region_sum_row[10]) region_sum_row[18] = round(100 * region_sum_row[17] / region_sum_row[10]) 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[12] = round(100 * cluster_sum_row[11] / cluster_sum_row[10]) cluster_sum_row[14] = round(100 * cluster_sum_row[13] / cluster_sum_row[10]) cluster_sum_row[16] = round(100 * cluster_sum_row[15] / cluster_sum_row[10]) cluster_sum_row[18] = round(100 * cluster_sum_row[17] / cluster_sum_row[10]) 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[12] = round(100 * all_sum_row[11] / all_sum_row[10]) all_sum_row[14] = round(100 * all_sum_row[13] / all_sum_row[10]) all_sum_row[16] = round(100 * all_sum_row[15] / all_sum_row[10]) all_sum_row[18] = round(100 * all_sum_row[17] / all_sum_row[10]) for key in range(0, len(all_sum_row)): worksheet3.write((row_num), key, all_sum_row[key], light_blue_bg_cell_bold) worksheet3.set_column(0, (len(all_rows3[1]) - 1), main_width) filter_range = "A4:Q" + str(row_num) worksheet3.autofilter(filter_range) workbook.close() uid = "M53J9SImW9My4vghlCaaWLYxsc22" # dcmID; 'mFFHQdEtiSbn2hbYQAwwoIdYVi02' ttgID #uid = "sAiJIWRbNOR5grDmRabVxW5BXp03" # triveni makeClusterSheet(uid, isTest=False, fileNo=3) # print(vertMergePos("D", 2, 2))