def getOptions(tab, var, val, con=None):
kilcon = False
sel = ['Continent', 'Country', 'Region', 'State', 'City']
var = var[:-2]
sel = sel[sel.index(var.capitalize()) + 1:sel.index(tab.capitalize())]
info = []
if len(sel) > 0:
if con == None:
con = dbh.Connect()
kilcon = True
info += [{
"var":
dbh.appendQuery("%0Id", [sel[0].lower()]),
"opts": [{
"name": "Select %s" % (sel[0])
}] + makeSelectOpts(sel[0].lower(), var, val, con)
}]
if kilcon:
con.close()
if len(sel) > 1:
for i in range(1, len(sel)):
info += [{
"var": dbh.appendQuery("%0Id", [sel[i].lower()]),
"opts": [{
"name": "Select %s First" % (sel[0])
}]
}]
return info
def newState(form=None, token=None):
if form == None:
data = [{
"name": "Name",
"value": "",
"var": "stateName"
}, {
"name": "Abbr",
"value": "",
"var": "stateSName"
}, {
"name": "Region",
"value": 0,
"var": "regionId",
"opts": [{
"name": "Select Continent First"
}]
}, {
"name": "Country",
"var": "countryId",
"value": 0,
"opts": [{
"name": "Select Continent First"
}]
}, {
"name":
"Continent",
"var":
"continentId",
"value":
0,
"opts": [{
"name": "Select Continent"
}] + makeSelectOpts("continent")
}]
data = {
"data":
data,
"col": [
"stateName", "stateSName", "regionId", "countryId",
"continentId"
],
"token":
requestToken()
}
return data
else:
con = dbh.Connect()
id = checkValidity(0, form, "State", con)
if id == True:
if requestToken(con, token):
cols = con.desc("Mstate", ["name"])
ins = {}
for i in cols:
if i["name"] in form:
ins[i["name"]] = form[i["name"]]
id = con.insertIntoTable("Mstate", ins, returnId=True)
con.close()
return id
def makeSelectOpts(get, where=None, id=None, con=None):
kilcon = False
if con == None:
con = dbh.Connect()
kilcon = True
if where == None:
where = ""
else:
where = {where + "Id": id}
data = con.getTable(dbh.appendQuery("M%0", [get.lower()]), [
dbh.appendQuery("M%0.id", [get.lower()]),
dbh.appendQuery("%0Name", [get.lower()])
],
where,
columnNames=["id", "name"],
ext=dbh.appendQuery("order by %0Name", [get]))
if kilcon:
con.close()
return data
def checkValidity(id, data, tab, con=None):
kilcon = False
if con == None:
con = dbh.Connect()
kilcon = True
resp = True
qr = {}
for i in data:
if data[i] == "" or data[i] == None:
resp = "Empty value detected!"
if i[-4:] == "Code":
if len(
con.getTable(
dbh.appendQuery("M%0", [i[:-4]]), ["id"],
con.appendQuery("%0=\"%1\" and id!=%2",
[i, data[i], id]))) > 0:
resp = "%s code already exists and must be unique!" % (
i[:-4].capitalize())
if kilcon:
con.close()
return resp
def saveEdit(tab, form, id, token):
con = dbh.Connect()
resp = checkValidity(id, form, tab, con)
print(resp)
if resp == True:
print("Yes")
if requestToken(con, token):
print("Valid")
cols = con.desc(dbh.appendQuery("M%0", [tab.lower()]), ["name"])
ins = {}
for i in cols:
if i["name"] in form:
ins[i["name"]] = form[i["name"]]
con.updateTable(dbh.appendQuery("M%0", [tab.lower()]), ins,
{"id": id})
resp = id
else:
print("Invalid")
else:
print("No")
con.close()
return resp
def requestToken(
con=None,
id=None,
):
kilcon = False
resp = 0
if con == None:
con = dbh.Connect()
kilcon = True
con.deleteFromTable(
"formHandler",
dbh.appendQuery("time>\"%0\"",
[dt.datetime.now() + dt.timedelta(minutes=20)]))
if id == None:
resp = con.insertIntoTable("formHandler", {"memo": "ok"},
returnId=True)
else:
resp = [False, True][con.deleteFromTable("formHandler", {"id": id},
returnCount=True)]
if kilcon:
con.close()
return resp
def deleteEntry(tab, id, token):
con = dbh.Connect()
sel = ['Country', 'Region', 'State', 'City']
resp = "Invalid Table"
if tab in sel:
resp = True
if sel.index(tab.capitalize()) + 1 < len(sel):
print("i")
if len(
con.getTable(
dbh.appendQuery("M%0", [
sel[sel.index(tab.capitalize()) + 1].lower()
]), ["id"], {dbh.appendQuery("%0Id", [tab]): id})) > 0:
resp = dbh.appendQuery("Selected %0 has dependent entries",
[tab.lower()])
print("dependencies")
if resp == True:
if requestToken(con, token):
con.deleteFromTable(dbh.appendQuery("M%0", [tab.lower()]),
{"id": id})
resp = True
con.close()
return resp
def getCountryTab(id=None, query={}, con=None, edit=False):
kilcon = False
if con == None:
con = dbh.Connect()
kilcon = True
if id == None:
cols = ["countryName", "countryCode", "continentName", "continentCode"]
qr = ""
if query != {}:
if type(query) == str:
q = {}
for i in cols:
q[i] = query
query = q
qr = makeWhereQuery(query, cols)
qr = "(%s)" % (qr)
data = con.getTable("Mcountry,Mcontinent", ["Mcountry.id"] + cols,
qr,
join={"continentId": "Mcontinent.id"},
ext="order by countryName",
columnNames=["id"] + cols)
data = {
"data": data,
"cols": ["Country", "Country Code", "Continent", "Continent Code"],
"keys": cols
}
else:
if edit:
data = con.getTable("Mcountry",
["countryName", "countryCode", "continentId"],
{"Mcountry.id": id})[0]
data = [{
"name": "Name",
"value": data["countryName"],
"var": "countryName"
}, {
"name": "Country Code",
"value": data["countryCode"],
"var": "countryCode"
}, {
"name": "Continent",
"value": data["continentId"],
"var": "continentId",
"opts": makeSelectOpts("continent", con=con)
}]
data = {
"data": data,
"col": ["countryName", "countryCode", "continentId"],
"token": requestToken(con)
}
else:
data = con.getTable(
"Mcountry,Mcontinent",
["countryName", "countryCode", "continentName"],
{"Mcountry.id": id},
join={"continentId": "Mcontinent.id"})[0]
data = [{
"name": "Name",
"value": data["countryName"]
}, {
"name": "Country Code",
"value": data["countryCode"]
}, {
"name": "Continent",
"value": data["continentName"]
}]
if kilcon:
con.close()
return data
def getCityTab(id=None, query={}, con=None, edit=False):
kilcon = False
if con == None:
con = dbh.Connect()
kilcon = True
if id == None:
cols = [
"cityName", "citySName", "stateName", "countryName",
"continentName"
]
qr = ""
if query != {}:
if type(query) == str:
q = {}
for i in cols:
q[i] = query
query = q
qr = makeWhereQuery(query, cols)
qr = "(%s)" % (qr)
data = con.getTable("Mcity,Mstate,Mregion,Mcountry,Mcontinent",
["Mcity.id"] + cols,
qr,
join={
"regionId": "Mregion.id",
"stateId": "Mstate.id",
"countryId": "Mcountry.id",
"continentId": "Mcontinent.id"
},
ext="order by cityName",
columnNames=["id"] + cols)
data = {
"data": data,
"cols": ["City", "Abbr", "State", "Country", "Continent"],
"keys": cols
}
else:
if edit:
data = con.getTable("Mcity,Mstate,Mregion,Mcountry", [
"cityName", "citySName", "stateId", "regionId", "countryId",
"continentId", "timezone"
], {"Mcity.id": id},
join={
"stateId": "Mstate.id",
"Mregion.id": "regionId",
"Mcountry.id": "countryId"
},
columnNames=[
"cityName", "citySName", "stateId",
"regionId", "countryId", "continentId",
"timezone"
])[0]
data = [{
"name": "Name",
"value": data["cityName"],
"var": "cityName"
}, {
"name": "Abbr",
"value": data["citySName"],
"var": "citySName"
}, {
"name":
"State",
"value":
data["stateId"],
"var":
"stateId",
"opts":
makeSelectOpts("state", "region", data["regionId"], con=con)
}, {
"name":
"Region",
"value":
data["regionId"],
"var":
"regionId",
"opts":
makeSelectOpts("region", "country", data["countryId"], con=con)
}, {
"name":
"Country",
"var":
"countryId",
"value":
data["countryId"],
"opts":
makeSelectOpts("country",
"continent",
data["continentId"],
con=con)
}, {
"name": "Continent",
"var": "continentId",
"value": data["continentId"],
"opts": makeSelectOpts("continent", con=con)
}, {
"name": "Timezone",
"var": "timezone",
"value": data["timezone"],
"opts": zones
}]
data = {
"data":
data,
"col": [
"cityName", "citySName", "stateId", "regionId",
"countryId", "continentId", "timezone"
],
"token":
requestToken(con)
}
else:
data = con.getTable("Mcity,Mstate,Mregion,Mcountry,Mcontinent", [
"cityName", "citySName", "stateName", "regionName",
"countryName", "continentName", "timezone"
],
where={"Mcity.id": id},
join={
"stateId": "Mstate.id",
"regionId": "Mregion.id",
"countryId": "Mcountry.id",
"continentId": "Mcontinent.id"
})[0]
data = [{
"name": "Name",
"value": data["cityName"]
}, {
"name": "Abbr",
"value": data["citySName"]
}, {
"name": "State",
"value": data["stateName"]
}, {
"name": "Region",
"value": data["regionName"]
}, {
"name": "Country",
"value": data["countryName"]
}, {
"name": "Continent",
"value": data["continentName"]
}, {
"name": "Timezone",
"value": data["timezone"]
}]
if kilcon:
con.close()
return data
