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()