def main():
dfs = open_excel_file(config.SHEET)
config.SHEET_BANKBRANCH = "Bank Branch"
config.SHEET_BANKACCOUNT = "Bank Account"
bankbranch = get_sheet(dfs, config.SHEET_BANKBRANCH)
bankaccount = get_sheet(dfs, config.SHEET_BANKACCOUNT)
config.COLUMN_BANKBRANCH_BRANCHCODE = "Branch Code*"
config.COLUMN_BANKBRANCH_PHONE = "Phone"
config.COLUMN_BANKBRANCH_CONTACTPERSON = "Contact Person"
config.COLUMN_BANKACCOUNT_ACCOUNTNUMBER = "Account Number *"
template = dict()
template["__city__"] = config.CITY_NAME.lower()
template["__contact__"] = fix_value(
bankbranch.iloc[0][get_column_index(bankbranch, config.COLUMN_BANKBRANCH_PHONE)], default_nan="")
template["__contact_person__"] = fix_value(
bankbranch.iloc[0][get_column_index(bankbranch, config.COLUMN_BANKBRANCH_CONTACTPERSON)], default_nan="")
template["__bankbranchcode__"] = fix_value(
bankbranch.iloc[0][get_column_index(bankbranch, config.COLUMN_BANKBRANCH_BRANCHCODE)])
template["__account_number__"] = fix_value(
bankaccount.iloc[0][get_column_index(bankaccount, config.COLUMN_BANKACCOUNT_ACCOUNTNUMBER)])
print(json.dumps(template, indent=2))
response = os.getenv("ASSUME_YES", None) or input("Do you want to generate the SQL template (y/[n])?")
if response.lower() == "y":
with open(config.BASE_PATH + "/sql/templates/PTGoLive.template.sql") as f:
data = f.read()
for key, value in template.items():
data = data.replace(key, value)
with open("../sql/output/PT/PTGoLive.{}.sql".format(config.CITY_NAME), mode="w") as w:
w.write(data)
else:
print("Not generating sql template")
def create_and_update_billing_slab(auth_token, tenant):
response = os.getenv("ASSUME_YES", None) or input(
"Your ENV is \"{}\" and TENANT ID is \"{}\", You want to proceed (y/[n])?"
.format(config.CONFIG_ENV, tenant))
if response.lower() == "n":
os._exit(0)
tenant_id = tenant
config.CITY_NAME = tenant.replace(" ", "").replace("pb.", "")
load_config()
billing_slabs = search_tl_billing_slab(auth_token)["billingSlab"]
get_slab_by_id_from_bs = {
slab["id"]: slab
for slab in billing_slabs if slab["id"]
}
dfs = open_excel_file(config.BASE_PPATH / "source" /
"{}_tl_billing_slab.xls".format(tenant_id))
data = get_sheet(dfs, "Trades_and_Accessories")
duplicate_id_check = []
error_lis = []
row_data_code_map = {}
update_slabs = []
new_slabs = []
for _id, slabs in data.iterrows():
row_data = get_slab_object(slabs.to_dict(), tenant_id)
slab_code = get_slab_code(row_data)
if "id" in row_data:
if row_data[
"id"] in duplicate_id_check: # Checking Duplicate id in sheet
error_lis.append("Duplicate id in the sheet: {}".format(
row_data["id"]))
elif row_data[
"id"] not in get_slab_by_id_from_bs: # Checking Invalid ID in sheet
error_lis.append("Wrong id in the sheet: {}".format(
row_data["id"]))
else:
duplicate_id_check.append(row_data["id"])
if slab_code not in row_data_code_map:
row_data_code_map[slab_code] = []
row_data_code_map[slab_code].append(row_data)
if compare_slabs_with_same_id(
row_data, get_slab_by_id_from_bs[row_data["id"]]):
update_slabs.append(row_data)
elif slab_code in row_data_code_map:
row_data_code_map[slab_code].append(row_data)
if compare_slabs_with_same_id(
row_data, get_slab_by_id_from_bs[row_data["id"]]):
update_slabs.append(row_data)
get_slab_by_id_from_bs.pop(row_data["id"], None)
elif is_new_billing_slab(row_data):
if slab_code not in row_data_code_map:
row_data_code_map[slab_code] = []
row_data_code_map[slab_code].append(row_data)
new_slabs.append(row_data)
elif slab_code in row_data_code_map:
row_data_code_map[slab_code].append(row_data)
new_slabs.append(row_data)
if len(get_slab_by_id_from_bs
) > 0: # Checking if any ID is missing in Sheet
error_lis.append("Missing IDs from sheet: {}".format(
[missing_id for missing_id in get_slab_by_id_from_bs.keys()]))
for code, slabs in row_data_code_map.items():
if len(slabs) > 1:
is_overlapped = check_for_overlapping_slabs(slabs)
if is_overlapped:
error_lis.append("Overlapped Slabs for code : {}".format(code))
if error_lis:
for err in error_lis:
print(err)
else:
if update_slabs:
update_billing_slab(update_slabs, auth_token, tenant_id)
if new_slabs:
create_billing_slab(new_slabs, auth_token, tenant_id)
existing_slab_data = {
get_slab_id(slab): slab
for slab in slabs.json()["billingSlab"]
}
# print(json.dumps(existing_slab_data, indent=2))
source_file = config.BASE_PPATH / "source" / "{}.xlsx".format(tenant_id)
create_trade_n_accessory_data(tenant_id,
source_file,
destination_path=config.BASE_PPATH /
"source",
template_file_path="source/template.xlsx")
dfs = open_excel_file(config.BASE_PPATH / "source" /
"{}.processed.xls".format(tenant_id))
data = get_sheet(dfs, tenant_id)
# data.fillna(value=0)
columns = [
"tenantid", "id", "licensetype", "structuretype", "tradetype",
"accessorycategory", "type", "uom", "fromuom", "touom", "rate",
"createdtime", "createdby", "lastmodifiedtime", "lastmodifiedby"
]
fields = {field: index for index, field in enumerate([])}
count = 0
new_slabs = {}
update_slabs = {}
slabs_processed = set()
for id, row in data.iterrows():
row_data = row.to_dict()
slab_id = get_slab_id(row_data)
def create_boundary_new(auth_token, config_function, boundary_type):
current_boundary_type = boundary_type
config_function()
tenant_id = config.TENANT_ID
# boundary_datas = get_mdms_boundary_data(auth_token, tenant_id)
#
# for boundary_datas in boundary_datas:
# if boundary_datas["hierarchyType"]["code"] == boundary_type:
# boundary_datas = boundary_datas["boundary"]["children"]
# break
dfs = open_excel_file(config.SHEET)
locality = get_sheet(dfs, config.SHEET_LOCALITY)
index_zone_name = get_column_index(locality, config.COLUMN_ZONE_NAME) or 1
index_zone_code = get_column_index(locality, config.COLUMN_ZONE_CODE) or 2
index_ward_name = get_column_index(locality, config.COLUMN_WARD_NAME) or 3
index_ward_code = get_column_index(locality, config.COLUMN_WARD_CODE) or 4
index_locality_name = get_column_index(locality,
config.COLUMN_LOCALITY_NAME) or 5
index_locality_code = get_column_index(locality,
config.COLUMN_LOCALITY_CODE) or 6
index_area_name = get_column_index(locality,
config.COLUMN_LOCALITY_AREA) or 7
zone_to_code_map = {}
zone_code_map = {}
ward_code_map = {}
ward_to_code_map = {}
locality_data = []
ward_data = {}
zone_ward_map = {}
for _, row in locality.iterrows():
zone_to_code_map[
row[index_zone_name].strip()] = row[index_zone_code].strip()
zone_to_code_map[
row[index_zone_code].strip()] = row[index_zone_name].strip()
zone_code_map[
row[index_zone_code].strip()] = row[index_zone_name].strip()
ward_to_code_map[
row[index_ward_name].strip()] = row[index_ward_code].strip()
ward_to_code_map[
row[index_ward_code].strip()] = row[index_ward_name].strip()
ward_code_map[
row[index_ward_code].strip()] = row[index_ward_name].strip()
zone_ward_map[row[index_zone_code].strip()] = zone_ward_map.get(
row[index_zone_code].strip(), set())
zone_ward_map[row[index_zone_code].strip()].add(
row[index_ward_code].strip())
if row[index_ward_name] not in ward_data:
ward_data[row[index_ward_name]] = []
ward_data[row[index_ward_name]].append({
"id":
str(uuid.uuid4()),
"boundaryNum":
1,
"name":
row[index_ward_name].strip(),
"localname":
row[index_ward_name].strip(),
"longitude":
None,
"latitude":
None,
"label":
"Block",
"code":
row[index_ward_code].strip(),
"zone":
row[index_zone_code].strip(),
"children": []
})
area = "" if current_boundary_type == "ADMIN" else row[
index_area_name].strip().upper().replace(" ", "").replace("-", "")
area_code = area.replace("AREA", "A")
if current_boundary_type == "REVENUE" and area not in ("AREA1",
"AREA2",
"AREA3"):
raise InvalidArgumentException("Area type is not valid - " + area)
if area_code:
area_code = " - " + area_code
locality_data.append({
"id":
str(uuid.uuid4()),
"boundaryNum":
1,
"name":
row[index_locality_name].strip() + " - " +
ward_to_code_map[row[index_ward_name].strip()] + area_code,
"localname":
row[index_locality_name].strip() + " - " +
ward_to_code_map[row[index_ward_name].strip()] + area_code,
"longitude":
None,
"latitude":
None,
"label":
"Locality",
"code":
row[index_locality_code].strip(),
"ward":
row[index_ward_code].strip(),
"area":
area,
"children": []
})
ward_list = []
zone_ward_len = 0
for code, zone_ward in zone_ward_map.items():
for ward in zone_ward:
ward_list.append(ward)
zone_ward_len = zone_ward_len + len(zone_ward)
zone_data = []
for zones in zone_code_map:
name = zone_code_map[zones]
zone_data.append({
"id": str(uuid.uuid4()),
"boundaryNum": 1,
"name": name.strip(),
"localname": name.strip(),
"longitude": None,
"latitude": None,
"label": "Zone",
"code": zones.strip(),
"children": []
})
# for boundary_data in boundary_datas:
# for ward in boundary_data["children"]:
# for revloc in ward["children"]:
# for locality in locality_data:
# if locality['code'] == revloc['code']:
# # locality['id']=None
# locality['id'] = revloc['id']
ward_loc_data = []
for id, wards_data in ward_data.items():
for wards in wards_data:
for locality in locality_data:
if locality["ward"] == wards["code"]:
wards["children"].append(locality)
ward_loc_data.append(wards)
# for boundary_data in boundary_datas:
# for zones in zone_data:
# if zones['code'] == boundary_data['code']:
# zones['id'] = boundary_data['id']
# for ward in boundary_data["children"]:
# for loc_ward in ward_loc_data:
# if loc_ward["code"] == ward["code"]:
# loc_ward["id"] = ward["id"]
for ward_loc in ward_loc_data:
for loc in ward_loc["children"]:
if loc["ward"]:
loc.pop("ward")
for zone in zone_data:
for ward_loc in ward_loc_data:
if zone["code"] == ward_loc["zone"]:
zone["children"].append(ward_loc)
for zones in zone_data:
for wards in zones["children"]:
if wards['zone']:
wards.pop('zone')
import json
# current_boundary_type = "ADMIN"
new_boundary_data = {
"hierarchyType": {
"code": current_boundary_type,
"name": current_boundary_type
},
"boundary": {
"id": 1,
"boundaryNum": 1,
"name": config.CITY_NAME,
"localname": config.CITY_NAME,
"longitude": None,
"latitude": None,
"label": "City",
"code": config.TENANT_ID,
"children": zone_data
}
}
final_data = {
"tenantId": config.TENANT_ID,
"moduleName": "egov-location",
"TenantBoundary": [new_boundary_data]
}
data = json.dumps(final_data, indent=2)
auth_token = superuser_login()["access_token"]
errors = validate_boundary_data(auth_token, final_data, boundary_type,
config.BOUNDARY_DUPLICATE_CHECK,
config.BOUNDARY_USED_CHECK)
# validate for one block is in multiple zones
freq = {}
for item in ward_list:
if (item in freq):
freq[item] += 1
else:
freq[item] = 1
for key, value in freq.items():
if value > 1:
errors.append(
"\nBlock : \"{}\" is in multiple zones ".format(key))
if len(errors) > 0:
print("error list")
for error in errors:
print(error)
return
print(data)
import os
response = os.getenv(
"ASSUME_YES",
None) or input("Do you want to append the data in repo (y/[n])?")
if response.lower() == "y":
boundary_path = config.MDMS_LOCATION / config.CITY_NAME.lower(
) / "egov-location"
os.makedirs(boundary_path, exist_ok=True)
if os.path.isfile(boundary_path / "boundary-data.json"):
with open(boundary_path / "boundary-data.json") as f:
existing_boundary_data = json.load(f)
if len(existing_boundary_data["TenantBoundary"]) == 0:
# should never happen but just in case
existing_boundary_data["TenantBoundary"].append(
new_boundary_data)
print("Boundary added")
elif len(existing_boundary_data["TenantBoundary"]) == 1:
if existing_boundary_data["TenantBoundary"][0][
"hierarchyType"]["code"] == current_boundary_type:
existing_boundary_data["TenantBoundary"][
0] = new_boundary_data
print("Boundary already exists. Overwriting")
else:
existing_boundary_data["TenantBoundary"].append(
new_boundary_data)
print("Boundary file exists. Adding new data")
elif len(existing_boundary_data["TenantBoundary"]) == 2:
if existing_boundary_data["TenantBoundary"][0][
"hierarchyType"]["code"] == current_boundary_type:
existing_boundary_data["TenantBoundary"][
0] = new_boundary_data
else:
existing_boundary_data["TenantBoundary"][
1] = new_boundary_data
print("Boundary already exists. Overwriting")
print("File Path : ", boundary_path, "boundary-data.json")
else:
# the file doesn't exists already, so we can safely generate current boundary
print("Boundary didn't exist. Creating one")
print("File Path : ", boundary_path + "boundary-data.json")
existing_boundary_data = final_data
with open(boundary_path / "boundary-data.json", "w") as f:
json.dump(existing_boundary_data, f, indent=2)