def insertDataIntoTable(tableName, name, surname, email, password, mobileno,
licenseno):
result = -1
status = "default"
status_who = ""
try:
createRegistrationTable()
if (isTableExist(tableName) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
insert_query = "INSERT INTO " + tableName + " (Name,Surname,Email,Password,mobileno,licenseno,activestatus) VALUES ('" + name + "','" + surname + "', '" + email + "','" + password + "','" + mobileno + "','" + licenseno + "','1' );"
record_to_insert = (tableName, name, surname, email, password,
mobileno, licenseno)
print(insert_query)
staticVar.cursor.execute(insert_query)
staticVar.connection.commit()
print("done inserting in db...Registered")
count = staticVar.cursor.rowcount
status = "success"
status_who = statuswho.REGISTRATION_STATUS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.REGISTRATION_STATUS_FAIL
result = "EmailID ."
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def createRegistrationTable():
result = -1
status = "default"
status_who = ""
try:
if (isTableExist(appConf.registrationtableName)):
status = "success"
status_who = statuswho.TABLE_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
else:
create_table_query = f'CREATE TABLE IF NOT EXISTS public."{appConf.registrationtableName}"(UID SERIAL PRIMARY KEY,Name varchar,Surname varchar,Email varchar UNIQUE NOT NULL,Password varchar,mobileno bigint,licenseno varchar UNIQUE NOT NULL,ActiveStatus bit,RegDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);'
alter_table_query = f'ALTER TABLE public."{appConf.registrationtableName}" OWNER to {appConf.registrationDBConfig["user"]};'
# print(create_table_query)
# print(alter_table_query)
staticVar.cursor.execute(create_table_query)
staticVar.cursor.execute(create_table_query)
staticVar.connection.commit()
status = "success"
status_who = statuswho.CREATE_TABLE_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def dropTable(tableName):
result = -1
status = "default"
status_who = ""
try:
if (isTableExist(tableName)):
staticVar.cursor.execute("DROP TABLE " + tableName + " CASCADE;")
staticVar.connection.commit()
status = "success"
status_who = statuswho.DROP_TABLE_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
else:
status = "error"
status_who = statuswho.DROP_TABLE_FAILURE
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def checkOwner(tablevehicle, tableusers, carregistrationno):
result = -1
status = "default"
status_who = ""
checktype = ""
count = 0
counttemp = 0
countown = 0
try:
if (isTableExist(tablevehicle) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
select_query = "SELECT vehicletype FROM " + tablevehicle + " WHERE carregistrationno='" + carregistrationno + "' AND vehicletype='owner';"
print("*********", select_query)
staticVar.cursor.execute(select_query)
staticVar.cursor.fetchall()
count = staticVar.cursor.rowcount
if not (count == 0):
#if (vehicleType == 'owner'):
select1_query = "SELECT email FROM " + tableusers + " INNER JOIN " + tablevehicle + " ON " + tableusers + ".uid=" + tablevehicle + ".uid WHERE carregistrationno='" + carregistrationno + "' AND vehicletype='owner';"
staticVar.cursor.execute(select1_query)
staticVar.connection.commit()
uservehicleData = staticVar.cursor.fetchall()
results = {}
email = ""
for row in uservehicleData:
email = str(row[0])
results = {"ownerEmail": email}
staticVar.connection.commit()
status = "success"
status_who = statuswho.CHECK_OWNER_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
results)
else:
result = {
"Msg":
"Owner doesnot exists for this vehicle registration number."
}
status = "error"
status_who = statuswho.CHECK_OWNER_FAILURE
return retcommon_status.createJSONResponse(status, status_who,
result)
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def showVehicleData(tablevehicle, tableuser, email):
results = -1
result = -1
status = "default"
status_who = ""
uservehicleData = ""
try:
if (isTableExist(tablevehicle) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
#staticVar.cursor = staticVar.connection.cursor(cursor_factory=RealDictCursor)
fetch_query = "SELECT carregistrationno,vehicletype FROM " + tablevehicle + " INNER JOIN " + tableuser + " ON " + tablevehicle + ".uid=" + tableuser + ".uid WHERE email='" + email + "' ;"
print("*********", fetch_query)
staticVar.cursor.execute(fetch_query)
staticVar.connection.commit()
uservehicleData = staticVar.cursor.fetchall()
results = []
carRegNumber = ""
vehicleType = ""
count = 0
for row in uservehicleData:
carRegNumber = str(row[0])
vehicleType = str(row[1])
#results[count] = {"carRegNumber": carRegNumber,"vehicleType": vehicleType}
#count = count + 1
results.append({
"carRegNumber": carRegNumber,
"vehicleType": vehicleType
})
staticVar.connection.commit()
status = "success"
status_who = statuswho.FETCH_ALL_SUCCESS
return retcommon_status.createJSONResponse(status, status_who, results)
#results = uservehicleData
#return results
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def viewTicketChecker():
result=-1
# jsonresult={}
jsonresult={}
status="default"
email=""
status_who=""
parkdate=""
timeremaining=""
parkinglocation=""
parkingfare=""
parkedcarregno=""
parkingEmail=""
uid_temp=""
uid=""
try:
parkedcarregno=request.json["parkedCarRegNo"]
status="success"
except:
status="error"
status_who=statuswho.JSON_INPUT_INCORRECT
try:
conn =psycopg2.connect(dbname=DB_NAME,user=DB_USER,password=DB_PASS,host=DB_HOST)
cur=conn.cursor()
except:
status="error"
status_who=statuswho.DB_CONNECTION_FAILED
if status=="success":
try:
SQL="select ac.parkingstartdate,ac.timeremaining,ac.parkinglocation,ac.parkingfare,ac.parkedcarregno,ac.parkingEmail,u.email from "+ vehicles_table +" as v join "+ activepark_view + " as ac on v.uid=ac.uid join tbl_user as u on u.uid=v.uid where v.carregistrationno in ('"+ parkedcarregno +"') and v.vehicletype='owner' and ac.parkedcarregno in ('" + parkedcarregno +"')"
cur.execute(SQL)
result=cur.fetchall()
if result==[]:
result="No user found"
status="error"
status_who=statuswho.NO_DATA_TO_DISPLAY
else:
try:
parkdate=str(result[0][0])
timeremaining=str(result[0][1])
parkinglocation=str(result[0][2])
parkingfare=str(result[0][0])
parkedcarregno=str(result[0][4])
parkingEmail=str(result[0][5])
email=str(result[0][5])
jsonresult={"parkingStartDate":parkdate,"remainingParkingDuration":timeremaining, "parkingLocation":parkinglocation, "parkingFare":parkingfare, "parkedCarRegNo":parkedcarregno,"parkingEmail":parkingEmail,"email":email}
status="success"
status_who=statuswho.GENERIC_STATUS
except:
status_who=statuswho.NO_DATA_TO_DISPLAY
status="error"
conn.commit()
conn.close()
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
if result==[]:
result=-1
return retcommon_status.createJSONResponse(status,status_who,jsonresult)
def putFine():
result=-1
parkingfine=""
finedate=""
jsonresult={}
parkedcarregno=""
checkerId=""
try:
parkedcarregno=request.json["parkedCarRegNo"]
parkingfine=request.json["parkingFine"]
checkerId=request.json["empId"]
status="success"
except:
status="error"
status_who=statuswho.JSON_INPUT_INCORRECT
try:
conn =psycopg2.connect(dbname=DB_NAME,user=DB_USER,password=DB_PASS,host=DB_HOST)
cur=conn.cursor()
except:
status="error"
status_who=statuswho.DB_CONNECTION_FAILED
if status=="success":
try:
SQL="select uid from "+ vehicles_table +" where carregistrationno in ('"+ parkedcarregno +"') and vehicletype='owner'"
cur.execute(SQL)
uid_temp=cur.fetchall()
if uid_temp==[]:
result="No car owner found"
status="error"
status_who=statuswho.LOGIN_STATUS_FAIL
else:
try:
uid=str(uid_temp[0][0])
SQL="insert into " + penalty_table + " values ('" + uid + "', '" +parkingfine +"' , ' " + parkedcarregno+ "', now() , '0' ,"+checkerId+")"
#SQL="select 1 from "+ penalty_table + " where uid in ('" + uid + "')"
cur.execute(SQL)
status="success"
status_who=statuswho.GENERIC_STATUS
# result=cur.fetchall()
# SQL="select parkingfine,finedate from " + penalty_table + " where uid in ('" + uid + "')"
# cur.execute(SQL)
# result=cur.fetchall()
# parkingfine=str(result[0][0])
# finedate=str(result[0][1])
# # jsonresult='{"parkdate":"'+parkdate+'","timeremaining":"'+ timeremaining+'", "parkinglocation:"'+parkinglocation+'", "parkingfare:"'+parkingfare+'", "parkedcarregno""'+parkedcarregno +'"}'
# jsonresult={"parkingfine":parkingfine,"finedate":finedate}
# status="success"
# status_who=statuswho.GENERIC_STATUS
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
if result==[]:
result=-1
return retcommon_status.createJSONResponse(status,status_who,jsonresult)
def closeConnection():
result = -1
status = "default"
status_who = ""
try:
if (staticVar.DBConnectionData["isDBConnected"]):
staticVar.cursor.close()
staticVar.connection.close()
staticVar.DBConnectionData["isDBConnected"] = False
status = "success"
status_who = statuswho.DB_CLOSE_CONNECTION_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.Error) as error:
status = "error"
status_who = statuswho.DB_CLOSE_CONNECTION_FAILURE
return retcommon_status.createJSONResponse(status, status_who,
str(result))
def UpdateUsertable(tableName, name, surname, email, mobileno):
result = -1
status = "default"
status_who = ""
try:
if (isTableExist(tableName)):
status = "success"
if not name is None:
staticVar.cursor.execute(" UPDATE " + tableName +
" SET name ='" + name +
"' WHERE email ='" + email + "';")
else:
print("name is null")
if not surname is None:
staticVar.cursor.execute(" UPDATE " + tableName +
" SET surname ='" + surname +
"' WHERE email ='" + email + "';")
else:
print("surname is null")
if not mobileno is None:
staticVar.cursor.execute(" UPDATE " + tableName +
" SET mobileno ='" + mobileno +
"' WHERE email ='" + email + "';")
else:
print("surname is null")
staticVar.connection.commit()
status_who = statuswho.UPDATE_TABLE_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
else:
status = "error"
status_who = statuswho.UPDATE_TABLE_FAILURE
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def fetchDataFromTable(tableName):
result = -1
status = "default"
status_who = ""
try:
createRegistrationTable()
if (isTableExist(tableName) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
staticVar.cursor = staticVar.connection.cursor(
cursor_factory=RealDictCursor)
fetch_query = "SELECT * FROM " + tableName + ";"
print("*********", fetch_query) # Should be logged
staticVar.cursor.execute(fetch_query)
staticVar.connection.commit()
results = staticVar.cursor.fetchall()
userdata = {}
count = 0
for row in results:
userdata[count] = row
count = count + 1
status = "success"
status_who = statuswho.FETCH_ALL_SUCCESS
result = result[0][0]
return retcommon_status.createJSONResponse(status, status_who,
str(result))
return userdata
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.FETCH_ALL_FAILURE
result = "EmailID or License Number already exists."
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def updatePassword(tableName, email, password, updatepassword):
result = -1
status = "default"
status_who = ""
try:
if (isTableExist(tableName) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
select_query = "SELECT * FROM " + tableName + " WHERE email='" + email + "' AND password='******';"
print("*********", select_query)
staticVar.cursor.execute(select_query)
staticVar.cursor.fetchall()
count = staticVar.cursor.rowcount
if not (count == 0):
results = {}
status = "success"
staticVar.cursor.execute(" UPDATE " + tableName +
" SET password ='******' WHERE email ='" + email +
"'AND password='******';")
staticVar.connection.commit()
result = {"Msg": "Password updated successfully."}
status_who = statuswho.UPDATE_TABLE_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
result)
else:
status = "error"
status_who = statuswho.UPDATE_PASSWORD_FAILURE
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
def saveloc():
rf = request.form
for key in rf.keys():
data = key
print(data)
data_dic = json.loads(data)
print(data_dic.keys())
lat = data_dic['lat']
lon = data_dic['lon']
resp_dic = {"lat": lat, "lon": lon}
resp = jsonify(resp_dic)
resp.headers['Access-Control-Allow-Origin'] = '*'
return retcommon_status.createJSONResponse(status, status_who, str(result))
def getDbConnection():
result = -1
status = "default"
status_who = ""
try:
#DB Details fetched from the Configuration file
staticVar.connection = psycopg2.connect(
user=appConf.registrationDBConfig["user"],
password=appConf.registrationDBConfig["password"],
host=appConf.registrationDBConfig["host"],
port=appConf.registrationDBConfig["port"],
database=appConf.registrationDBConfig["database"])
staticVar.cursor = staticVar.connection.cursor()
status = "success"
status_who = statuswho.DB_CONNECTION_SUCCESS
return retcommon_status.createJSONResponse(status, status_who,
str(result))
except (Exception, psycopg2.Error) as error:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
return retcommon_status.createJSONResponse(status, status_who,
str(result))
def clearPayment():
result=-1
jsonresult=[]
SQL=""
try:
email=request.json["email"]
status="success"
paymentType=request.json["pendingAmountType"]
except:
status="error"
status_who=statuswho.JSON_INPUT_INCORRECT
try:
conn =psycopg2.connect(dbname=DB_NAME,user=DB_USER,password=DB_PASS,host=DB_HOST)
cur=conn.cursor()
except:
status="error"
status_who=statuswho.DB_CONNECTION_FAILED
if status=="success":
try:
SQL="select uid from "+ User_table +" where email in ('"+ email +"')"
cur.execute(SQL)
uid_temp=cur.fetchall()
if uid_temp==[]:
result="No user found"
status="error"
status_who=statuswho.LOGIN_STATUS_FAIL
else:
try:
uid=str(uid_temp[0][0])
if (paymentType=="fineAmount"):
SQL="update "+ penalty_table + " set paidstatus='1' where uid in ('" + uid + "') and paidstatus='0'"
cur.execute(SQL)
elif (paymentType=="ticketAmount"):
SQL="update "+ parking_table + " set paidstatus='1' where uid in ('" + uid + "') and parkingactive='0' and paidstatus='0'"
cur.execute(SQL)
SQL="update "+ history_tabe + " set paidstatus='1' where uid in ('" + uid + "') and paidstatus='0'"
cur.execute(SQL)
status="success"
status_who=statuswho.GENERIC_STATUS
except:
status="error"
status_who=statuswho.NO_DATA_TO_DISPLAY
conn.commit()
conn.close()
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
if result==[]:
result=-1
return retcommon_status.createJSONResponse(status,status_who,jsonresult)
def resetpassword():
result = -1
status = "default"
status_who = ""
try:
Username = request.json["email"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select email from " + User_table + " where email in ('" + Username + "')"
cur.execute(SQL)
result = cur.fetchall()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = 0
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
result = result[0][0]
email_flag = SendEmail(result)
if email_flag == 0:
status = "success"
status_who = statuswho.EMAIL_STATUS_SUCCESS
else:
status = "error"
status_who = statuswho.EMAIL_STATUS_FAILED
conn.commit()
conn.close()
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, str(result))
def createpassword_new():
result = -1
status = "default"
status_who = ""
try:
Username = request.json["email"]
Password = request.json["Password"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
SQL_checkuser = "******" + User_table + " where Username in ('" + Username + "')"
cur.execute(SQL_checkuser)
if (cur.fetchall()[0][0] == 1):
try:
SQL = "update " + User_table + " set PasswordHash='" + Password + "' where Username='******'"
cur.execute(SQL)
#result=cur.fetchall()
status = "success"
status_who = statuswho.INSERTION_PASSWORD_SUCCESS
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
else:
status = "error"
status_who = statuswho.INSERTION_PASSWORD_FAIL
conn.commit()
conn.close()
return retcommon_status.createJSONResponse(status, status_who)
def isTableExist(tableName):
result = -1
status = "default"
status_who = ""
try:
getDbConnection()
table_exist_query = "SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name ='" + tableName + "');"
staticVar.cursor.execute(table_exist_query) # Should be logged
if (staticVar.cursor.fetchone()[0]):
status = "success"
status_who = statuswho.TABLE_EXIST
return True
else:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return False
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
def getTicketHistory():
result=-1
# jsonresult={}
jsonresult=[]
status="default"
email=""
SQL=""
status_who=""
parkdate=""
parkinglocation=""
parkingfare=""
parkedcarregno=""
parkingEmail=""
name=""
uid_temp=""
parkingstartdate=""
uid=""
try:
email=request.json["email"]
status="success"
except:
status="error"
status_who=statuswho.JSON_INPUT_INCORRECT
try:
conn =psycopg2.connect(dbname=DB_NAME,user=DB_USER,password=DB_PASS,host=DB_HOST)
cur=conn.cursor()
except:
status="error"
status_who=statuswho.DB_CONNECTION_FAILED
if status=="success":
try:
SQL="select uid from "+ User_table +" where email in ('"+ email +"')"
cur.execute(SQL)
uid_temp=cur.fetchall()
if uid_temp==[]:
result="No user found"
status="error"
status_who=statuswho.NO_DATA_TO_DISPLAY
else:
try:
uid=str(uid_temp[0][0])
SQL="select name,surname,email,parkingstartdate,parkingenddate,parkinglocation,parkingfare,parkedcarregno,paidstatus,parkingemail from "+ history_tabe + " where uid in ('" + uid + "')"
cur.execute(SQL)
result=cur.fetchall()
if result==[]:
status="error"
status_who=statuswho.NO_DATA_TO_DISPLAY
else:
try:
for i in range(len(result)):
name=str(result[i][0])
surname=str(result[i][1])
email=str(result[i][2])
parkingstartdate=str(result[i][3])
parkingenddate=str(result[i][4])
parkinglocation=str(result[i][5])
parkingfare=str(result[i][6])
parkedcarregno=str(result[i][7])
if str(result[i][8]) =='0':
paidstatus='unpaid'
else:
paidstatus='paid'
parkingEmail=str(result[i][9])
jsonresult.append({"name":name,"email":email,"parkingStartDate":parkingstartdate,"parkingEndDate":parkingenddate, "parkingLocation":parkinglocation, "parkingFare":parkingfare, "parkedCarRegNo":parkedcarregno,"paymentStatus":paidstatus,"parkingEmail":parkingEmail})
status="success"
status_who=statuswho.GENERIC_STATUS
except:
status_who=statuswho.TABLE_DOESNOT_EXIST
status="error"
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
if result==[]:
result=-1
return retcommon_status.createJSONResponse(status,status_who,jsonresult)
def viewIssuedFine():
result=-1
name=""
email=""
mobileno=""
carRegistrationNo=""
fineDate=""
paidStatus=""
parkingFine=""
checkerId=""
jsonresult=[]
try:
checkerId=request.json["empId"]
status="success"
except:
status="error"
status_who=statuswho.JSON_INPUT_INCORRECT
try:
conn =psycopg2.connect(dbname=DB_NAME,user=DB_USER,password=DB_PASS,host=DB_HOST)
cur=conn.cursor()
except:
status="error"
status_who=statuswho.DB_CONNECTION_FAILED
if status=="success":
try:
SQL="select u.name,u.email,u.mobileno,p.carregistrationno,p.finedate,p.paidstatus,p.parkingfine from "+ penalty_table +" p join tbl_user u on p.uid=u.uid where p.issuedcid in ('"+ checkerId +"')"
cur.execute(SQL)
result=cur.fetchall()
if result==[]:
result="No user found"
status="error"
status_who=statuswho.LOGIN_STATUS_FAIL
else:
try:
for i in range(len(result)):
name=str(result[i][0])
email=str(result[i][1])
mobileno=str(result[i][2])
carRegistrationNo=str(result[i][3])
fineDate=str(result[i][4])
paidStatus=str(result[i][5])
parkingFine=str(result[i][6])
if (paidStatus=="0"):
paidStatus="Not Paid"
else:
paidStatus="Paid"
jsonresult.append({"name":name,"email":email,"mobileNo":mobileno,"parkingFine":parkingFine + " EUR","fineDate":fineDate,"carRegistrationNo":carRegistrationNo,"paidStatus":paidStatus})
# jsonresult='{"parkdate":"'+parkdate+'","timeremaining":"'+ timeremaining+'", "parkinglocation:"'+parkinglocation+'", "parkingfare:"'+parkingfare+'", "parkedcarregno""'+parkedcarregno +'"}'
status="success"
status_who=statuswho.GENERIC_STATUS
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status="error"
status_who=statuswho.TABLE_DOESNOT_EXIST
if result==[]:
result=-1
return retcommon_status.createJSONResponse(status,status_who,jsonresult)
def buyticket():
result = -1
status = "default"
email = ""
carregno = ""
minutesparking = ""
parkloc = ""
uid_temp = ""
uid = ""
parkingfare = ""
jsonresult = {}
parkingEmail = ""
try:
email = request.json["email"]
carregno = request.json["parkedCarRegNo"]
minutesparking = request.json["parkingDuration"]
parkloc = request.json["parkedLocation"]
if (request.json["parkingEmail"]):
parkingEmail = request.json["parkingEmail"]
else:
parkingEmail = email
status = "success"
parkingfare = float(parking_fare.calfare(minutesparking))
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select uid from " + User_table + " where email in ('" + email + "')"
cur.execute(SQL)
uid_temp = cur.fetchall()
if uid_temp == []:
result = "No user found"
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
try:
uid = str(uid_temp[0][0])
SQL = "select 1 from " + parking_table + " where uid in ('" + uid + "') and parkedcarregno in ('" + carregno + "')"
#SQL="select 1 from " + parking_table + " where uid in ('"+ uid + "')"# and ParkedCarRegNo in ('"+ carregno +"') and parkingactive in ('1')"
print(SQL)
cur.execute(SQL)
result = cur.fetchall()
if result == []:
try:
SQL = "insert into " + parking_table + " values( " + uid + " , '1', now(), now() + interval '1' minute * " + minutesparking + ",'" + parkloc + "','" + str(
parkingfare
) + "','" + carregno + "','" + parkingEmail + "')"
print(SQL)
cur.execute(SQL)
status = "success"
status_who = statuswho.PARKING_SUCCESSFUL
jsonresult = {"parkingFare": parkingfare}
except:
status_who = statuswho.PARKING_INSERTION_FAILED
status = "error"
else:
status = "error"
status_who = statuswho.PARKING_ALREADY_EXISTS
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def extendticket():
result = -1
status = "default"
status_who = ""
email = ""
carregno = ""
minutesextended = ""
parkloc = ""
uid_temp = ""
uid = ""
parkingfare = ""
parkingemail = ""
parkingStartDate = ""
parkingEndDate = ""
parkingfare_new = ""
jsonresult = {}
try:
email = request.json["email"]
carregno = request.json["parkedCarRegNo"]
# parkStartDate=request.json["parking"]
minutesextended = request.json["timeToExtend"]
# parkloc=request.json["parkedLocation"]
parkingemail = request.json["parkingEmail"]
status = "success"
#parkingfare=float(parking_fare.calfare(minutesextended))
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select uid from " + User_table + " where email in ('" + email + "')"
cur.execute(SQL)
uid_temp = cur.fetchall()
if uid_temp == []:
result = "No user found"
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
try:
uid = str(uid_temp[0][0])
try:
SQL = "select parkingstartdate,parkingenddate,parkingfare,extract(epoch from (parkingenddate-parkingstartdate) / 60 ) from " + parking_table + " where uid in ('" + uid + "') and parkedcarregno in ('" + carregno + "') and parkingemail in ('" + str(
parkingemail) + "')"
cur.execute(SQL)
result = cur.fetchall()
parkingStartDate = result[0][0]
parkingEndDate = result[0][1]
parkingfare = result[0][2]
parkingtime_temp = result[0][3]
parkingtime_new = parkingtime_temp + int(
minutesextended)
parkingfare_new = parking_fare.calfare(parkingtime_new)
except:
status = "error"
status_who = statuswho.NO_DATA_TO_DISPLAY
SQL = "update tbl_parkdetails set parkingfare='" + str(
parkingfare_new
) + "',parkingenddate=(parkingenddate+interval '1' minute * '" + str(
minutesextended
) + "') where parkingactive='1' and parkedcarregno in ('" + str(
carregno) + "') and parkingemail in ('" + str(
parkingemail) + "')"
cur.execute(SQL)
status = "success"
status_who = statuswho.PARKING_SUCCESSFUL
jsonresult = {"parkingFare": parkingfare_new}
#result=cur.fetchall()
except:
status = "error"
status_who = statuswho.UPDATE_DATA_FAILED
conn.commit()
conn.close()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def putFine():
result = -1
parkingfine = ""
finedate = ""
jsonresult = {}
try:
email = request.json["email"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select uid from " + User_table + " where email in ('" + email + "')"
cur.execute(SQL)
uid_temp = cur.fetchall()
if uid_temp == []:
result = "No user found"
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
try:
uid = str(uid_temp[0][0])
SQL = "select 1 from " + penalty_table + " where uid in ('" + uid + "')"
cur.execute(SQL)
result = cur.fetchall()
if result == []:
status = "error"
status_who = statuswho.NO_DATA_TO_DISPLAY
else:
try:
SQL = "select parkingfine,finedate from " + penalty_table + " where uid in ('" + uid + "')"
cur.execute(SQL)
result = cur.fetchall()
parkingfine = str(result[0][0])
finedate = str(result[0][1])
# jsonresult='{"parkdate":"'+parkdate+'","timeremaining":"'+ timeremaining+'", "parkinglocation:"'+parkinglocation+'", "parkingfare:"'+parkingfare+'", "parkedcarregno""'+parkedcarregno +'"}'
jsonresult = {
"parkingfine": parkingfine,
"finedate": finedate
}
status = "success"
status_who = statuswho.GENERIC_STATUS
except:
status_who = statuswho.TABLE_DOESNOT_EXIST
status = "error"
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def viewTotalFines():
result = -1
# jsonresult={}
jsonresult = {}
status = "default"
email = ""
status_who = ""
parkdate = ""
timeremaining = ""
parkinglocation = ""
parkingfare = ""
parkedcarregno = ""
parkingEmail = ""
uid_temp = ""
uid = ""
try:
email = request.json["email"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select uid from " + User_table + " where email in ('" + email + "')"
cur.execute(SQL)
uid_temp = cur.fetchall()
if uid_temp == []:
result = "No user found"
status = "error"
status_who = statuswho.NO_DATA_TO_DISPLAY
else:
try:
uid = str(uid_temp[0][0])
SQL = "select sum(parkingfine) from " + penalty_table + " where uid in ('" + uid + "')"
cur.execute(SQL)
result = cur.fetchall()
if result == []:
status = "error"
status_who = statuswho.NO_DATA_TO_DISPLAY
else:
try:
jsonresult = {"parkingFine": str(result[0][0])}
status = "success"
status_who = statuswho.GENERIC_STATUS
except:
status_who = statuswho.TABLE_DOESNOT_EXIST
status = "error"
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def viewTotalFare():
result = -1
# jsonresult={}
jsonresult = {}
status = "default"
email = ""
status_who = ""
parkdate = ""
timeremaining = ""
parkinglocation = ""
parkingfare = ""
parkedcarregno = ""
parkingEmail = ""
uid_temp = ""
uid = ""
try:
email = request.json["email"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select uid from " + User_table + " where email in ('" + email + "')"
cur.execute(SQL)
uid_temp = cur.fetchall()
if uid_temp == []:
result = "No user found"
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
try:
uid = str(uid_temp[0][0])
SQL = "select 1 from " + activepark_view + " where uid in ('" + uid + "')"
cur.execute(SQL)
result = cur.fetchall()
if result == []:
status = "error"
status_who = ""
else:
try:
SQL = "select parkingstartdate,timeremaining,parkinglocation,parkingfare,parkedcarregno,parkingEmail from " + activepark_view + " where uid in ('" + uid + "')"
cur.execute(SQL)
result = cur.fetchall()
for i in range(len(result)):
parkdate = str(result[i][0])
timeremaining = str(result[i][1])
parkinglocation = str(result[i][2])
parkingfare = str(result[i][3])
parkedcarregno = str(result[i][4])
parkingEmail = str(result[i][5])
# if(len(jsonresult) is None):
# jsonresult={"parkingStartDate":parkdate,"remainingParkingDuration":timeremaining, "parkingLocation":parkinglocation, "parkingFare":parkingfare, "parkedCarRegNo":parkedcarregno,"parkingEmail":parkingEmail}
# else:
# jsonresult=jsonresult+{"parkingStartDate":parkdate,"remainingParkingDuration":timeremaining, "parkingLocation":parkinglocation, "parkingFare":parkingfare, "parkedCarRegNo":parkedcarregno,"parkingEmail":parkingEmail}
jsonresult.append({
"parkingStartDate": parkdate,
"remainingParkingDuration": timeremaining,
"parkingLocation": parkinglocation,
"parkingFare": parkingfare,
"parkedCarRegNo": parkedcarregno,
"parkingEmail": parkingEmail
})
# jsonresult='{"parkdate":"'+parkdate+'","timeremaining":"'+ timeremaining+'", "parkinglocation:"'+parkinglocation+'", "parkingfare:"'+parkingfare+'", "parkedcarregno""'+parkedcarregno +'"}'
status = "success"
status_who = statuswho.GENERIC_STATUS
except:
status_who = statuswho.TABLE_DOESNOT_EXIST
status = "error"
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
conn.commit()
conn.close()
except:
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def loginvalid():
result = -1
status = "default"
status_who = ""
Username = ""
Password = ""
authentication = ""
firstname = ""
surname = ""
email = ""
licenseNumber = ""
mobileNumber = ""
jsonresult = {}
#jsonstr=json.loads(request.json,strict=False)
#print(jsonstr)
try:
Username = request.json["email"]
Password = request.json["password"]
status = "success"
except:
status = "error"
status_who = statuswho.JSON_INPUT_INCORRECT
try:
conn = psycopg2.connect(dbname=DB_NAME,
user=DB_USER,
password=DB_PASS,
host=DB_HOST)
cur = conn.cursor()
except:
status = "error"
status_who = statuswho.DB_CONNECTION_FAILED
if status == "success":
try:
SQL = "select 1 from " + User_table + " where email in ('" + Username + "') and Password in ('" + Password + "')"
cur.execute(SQL)
result = cur.fetchall()
if result == []:
result = "false"
status = "error"
status_who = statuswho.LOGIN_STATUS_FAIL
else:
try:
status = "success"
status_who = statuswho.LOGIN_STATUS
authentication = "true"
SQL = "select name,surname,email,licenseno,mobileno from " + User_table + " where email in ('" + Username + "')"
cur.execute(SQL)
result = cur.fetchall()
firstname = str(result[0][0])
surname = str(result[0][1])
email = str(result[0][2])
licenseNumber = str(result[0][3])
mobileNumber = str(result[0][4])
jsonresult = {
"authentication": authentication,
"email": email,
"firstname": firstname,
"surname": surname,
"licenseNumber": licenseNumber,
"mobileNumber": mobileNumber
}
except:
status_who = statuswho.TABLE_DOESNOT_EXIST
status = "error"
conn.commit()
conn.close()
except:
#print(SQL)
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
if result == []:
result = -1
return retcommon_status.createJSONResponse(status, status_who, jsonresult)
def addVehicleData(tablevehicle, tableusers, carregistrationno, vehicleType,
email):
result = -1
status = "default"
status_who = ""
checktype = ""
count = 0
counttemp = 0
countown = 0
try:
if (isTableExist(tablevehicle) == False):
status = "error"
status_who = statuswho.TABLE_DOESNOT_EXIST
return retcommon_status.createJSONResponse(status, status_who,
str(result))
select_query = "SELECT vehicletype FROM tbl_vehicles WHERE carregistrationno='" + carregistrationno + "' AND vehicletype='owner';"
print("*********", select_query)
staticVar.cursor.execute(select_query)
staticVar.cursor.fetchall()
count = staticVar.cursor.rowcount
if not (count == 0):
if (vehicleType == 'owner'):
select1_query = "SELECT carregistrationno,vehicletype FROM tbl_vehicles INNER JOIN tbl_user ON tbl_vehicles.uid=tbl_user.uid WHERE email='" + email + "' AND carregistrationno='" + carregistrationno + "' AND vehicletype='owner';"
staticVar.cursor.execute(select1_query)
staticVar.cursor.fetchall()
countown = staticVar.cursor.rowcount
if not (countown == 0):
result = {
"registrationMsg":
"You are the owner of this vehicle and it already exists in your profile."
}
status = "error"
status_who = statuswho.INSERTION_CARREGNO_FAIL
return retcommon_status.createJSONResponse(
status, status_who, result)
else:
print(
str(count) + " Owner already exists for this vehicle.")
result = {
"registrationMsg":
"Owner already exists for the vehicle"
}
status = "error"
status_who = statuswho.INSERTION_CARREGNO_FAIL
return retcommon_status.createJSONResponse(
status, status_who, result)
else:
select_query = "SELECT vehicletype FROM tbl_vehicles INNER JOIN tbl_user ON tbl_vehicles.uid=tbl_user.uid WHERE email='" + email + "' AND carregistrationno='" + carregistrationno + "';"
print("*********", select_query)
staticVar.cursor.execute(select_query)
staticVar.cursor.fetchall()
counttemp = staticVar.cursor.rowcount
if (counttemp == 0):
insert_query = "INSERT INTO " + tablevehicle + "(uid,carregistrationno,vehicleType,currentactive) VALUES((SELECT uid FROM " + tableusers + " WHERE email='" + email + "'),'" + carregistrationno + "','" + vehicleType + "','1') ;"
print("*********", insert_query)
staticVar.cursor.execute(insert_query)
staticVar.connection.commit()
result = {
"registrationMsg": "Vehicle registered successfully"
}
status = "success"
status_who = statuswho.INSERTION_CARREGNO_SUCCESS
return retcommon_status.createJSONResponse(
status, status_who, result)
else:
result = {
"registrationMsg":
"Vehicle already exists in your profile."
}
status = "error"
status_who = statuswho.INSERTION_CARREGNO_FAIL
return retcommon_status.createJSONResponse(
status, status_who, result)
else:
select_query = "SELECT vehicletype FROM tbl_vehicles INNER JOIN tbl_user ON tbl_vehicles.uid=tbl_user.uid WHERE email='" + email + "' AND carregistrationno='" + carregistrationno + "' AND vehicletype='temporary';"
print("*********", select_query)
staticVar.cursor.execute(select_query)
staticVar.cursor.fetchall()
counttemp = staticVar.cursor.rowcount
if (counttemp == 0):
print(str(counttemp) + " is null")
insert_query = "INSERT INTO " + tablevehicle + "(uid,carregistrationno,vehicleType,currentactive) VALUES((SELECT uid FROM " + tableusers + " WHERE email='" + email + "'),'" + carregistrationno + "','" + vehicleType + "','1') ;"
print("*********", insert_query)
staticVar.cursor.execute(insert_query)
staticVar.connection.commit()
count = staticVar.cursor.rowcount
#result = "New Vehicle Registration Number "+carregistrationno+" added successfully."
result = {"registrationMsg": "Vehicle registered successfully"}
status = "success"
status_who = statuswho.INSERTION_CARREGNO_SUCCESS
return retcommon_status.createJSONResponse(
status, status_who, result)
else:
result = {
"registrationMsg":
"Vehicle already exists in your profile."
}
status = "error"
status_who = statuswho.INSERTION_CARREGNO_FAIL
return retcommon_status.createJSONResponse(
status, status_who, result)
except (Exception, psycopg2.DatabaseError) as error:
status = "error"
status_who = statuswho.DATABASE_ERROR
return retcommon_status.createJSONResponse(status, status_who,
str(result))
finally:
closeConnection()
