def getQuote(form):
response = {}
if ('gallons' not in form or 'date' not in form or 'username' not in form):
print('field missing')
response['error'] = 'field missing'
return response
price = PR.getQuote(form)
today = str(date.today())
response['price'] = price
response['today'] = today
response['price'] = form['price']
response['history'] = form['history']
response['season'] = form['season']
response['location'] = form['location']
response['amount'] = form['amount']
response['profit'] = form['profit']
response['rate'] = form['rate']
db.runInsertQuery('INSERT INTO Quote (Username_ID, Number_of_Gallons, Price, Request_Date, Request_Delivery_Date) VALUES (%s, %s, %s, "%s", "%s")' \
% (form['id'], form['gallons'], price, today, form['date']))
response['quote_id'] = db.runQuery(
'SELECT LAST_INSERT_ID()')[0]['LAST_INSERT_ID()']
return response
def registerUser(user):
response = {}
if (('username' not in user) or ('password' not in user)):
print('user or pass is missing for registration')
response['error'] = 'user/pass missing'
return response
elif (not UR.emailIsValid(user['username'])):
print('email format is wrong.')
response['error'] = 'wrong email format'
return response
elif (not UR.passwordIsValid(user['password'])):
print('password is too weak.')
response['error'] = 'password is weak'
return response
elif (not UR.emailAvailable(user['username'])):
print('email is taken.')
response['error'] = 'email is taken'
return response
user['type'] = 'client'
user['date'] = str(date.today())
q = 'INSERT INTO Login_Info (Username, Passwd, Type_of_user, Register_Date) VALUES ("%s", "%s", "%s", "%s");' \
% (user['username'], user['password'], user['type'], user['date'])
db.runInsertQuery(q)
id = db.runQuery('SELECT Username_ID FROM Login_Info WHERE Username = "******"' \
% (user['username']))[0]['Username_ID']
db.runInsertQuery('INSERT INTO Profile_for_Users (Username_ID) VALUES (%s)' \
% (id))
response['status'] = 'added'
return response
def processOrder(form):
response = {}
if ('invoice_id' not in form or 'username' not in form):
print('field missing')
response['error'] = 'field missing'
return response
db.runInsertQuery('UPDATE Invoice SET Invoice.Delivery_Date = CURDATE() WHERE Invoice.Invoice_ID = %s'\
% (form['invoice_id']))
return response
def executeScriptsFromFile(filename):
# Open and read the file as a single buffer
fd = open(filename, 'r')
sqlFile = fd.read()
fd.close()
# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
for command in sqlCommands:
try:
db.run(command)
print('command executed')
except:
print('command failed')
def getUserHistoryFactor(user):
q = 'SELECT * FROM Invoice WHERE Username_ID = %s AND Paid = 1' \
% (user)
curProfile = db.runQuery(q)
if (len(curProfile) > 0):
return 0.01
return 0.0
def getUserInfo(user):
result = db.getData("*", "Login_Info", "Username = '******'username'] + "'")
if(len(result) > 0):
result = result[0]
else:
result = {}
return result
def getProfile(form):
response = {}
if ('id' not in form):
print('field missing')
response['error'] = 'field missing'
return response
q = 'SELECT * FROM Profile_for_Users WHERE Username_ID = "%s"' \
% (form['id'])
curProfile = db.runQuery(q)
return curProfile
def getRequestList(user):
response = {}
if ('username' not in user or 'id' not in user):
print('field missing')
response['error'] = 'field missing'
return response
q = 'SELECT * FROM Quote, Invoice, Profile_for_Users WHERE Invoice.Username_ID = Profile_for_Users.Username_ID AND Invoice.Quote_ID = Quote.Quote_ID AND Invoice.Delivery_Date IS NULL'
result = db.runQuery(q)
return result
def Pay(form):
response = {}
if ('username' not in form or 'invoice_id' not in form):
print('field missing')
response['error'] = 'field missing'
return response
q = 'UPDATE Invoice Set Paid = 1 WHERE Invoice.Invoice_ID = %s' \
% (form['invoice_id'])
result = db.runInsertQuery(q)
return response
def getAllTransactionHistory(user):
response = {}
if ('username' not in user or 'id' not in user):
print('field missing')
response['error'] = 'field missing'
return response
q = 'SELECT * FROM Invoice, Profile_for_Users, Quote WHERE Invoice.Username_ID = Profile_for_Users.Username_ID AND Invoice.Quote_ID = Quote.Quote_ID'
result = db.runQuery(q)
return result
return ""
def getQuoteHistory(user):
response = {}
if ('username' not in user or 'id' not in user):
print('field missing')
response['error'] = 'field missing'
return response
q = 'SELECT * FROM Quote WHERE Username_ID = "%s"' \
% (user['id'])
result = db.runQuery(q)
return result
def getInvoices(user):
response = {}
if ('username' not in user):
print('field missing')
response['error'] = 'field missing'
return response
q = 'SELECT Number_of_Gallons, Price, Request_Delivery_Date, Delivery_Date, Invoice_ID, Paid FROM Invoice, Quote WHERE Invoice.Username_ID = "%s" AND Invoice.Quote_ID = Quote.Quote_ID' \
% (user['id'])
result = db.runQuery(q)
return result
def createInvoice(form):
response = {}
if ('quote_id' not in form or 'username' not in form):
print('field missing')
response['error'] = 'field missing'
return response
q = 'INSERT INTO Invoice (Paid, Username_ID, Quote_ID) SELECT 0, Quote.Username_ID, %s FROM Quote WHERE Quote.Quote_ID = %s' \
% (form['quote_id'], form['quote_id'])
result = db.runInsertQuery(q)
return response
def modifyProfile(form):
print(form)
response = {}
if('username' not in form or 'lastname' not in form or 'company' not in form or 'address1' not in form or\
'city' not in form or 'state' not in form or 'zipcode' not in form or 'firstname' not in form):
print('field missing.')
response['error'] = "field missing."
return response
q = 'SELECT * FROM Profile_for_Users INNER JOIN Login_Info ON Profile_for_Users.Username_ID = Login_Info.Username_ID WHERE Username = "******"' \
% (form['username'])
curProfile = db.runQuery(q)
print(len(curProfile))
if (len(curProfile) == 0):
id = db.runQuery('SELECT Username_ID FROM Login_Info WHERE Username = "******"' \
% (form['username']))[0]['Username_ID']
db.runInsertQuery('INSERT INTO Profile_for_Users (Username_ID) VALUES (%s)' \
% (id))
q = 'UPDATE Profile_for_Users NATURAL JOIN Login_Info SET First_Name = "%s", Last_Name = "%s", Company_Name = "%s", Address = "%s", Address2 = "%s", City = "%s", State = "%s", Zipcode = %s WHERE Username = "******";' \
% (form['firstname'], form['lastname'], form['company'], form['address1'], form['address2'], form['city'], form['state'], form['zipcode'], form['username'])
db.runInsertQuery(q)
response['status'] = 'updated'
return response