def index():
# Something in order troubleshoot: literally calling just about everything in the file. (May have to re-instantiate the database from dummyTester.py because no vin and things yet.
entries = query_db("""
SELECT customers.customerName, customers.customerEmail, vehicles.make, vehicles.model, repairs.repairType, repairs.repairId
FROM ((vehicles INNER JOIN customers ON vehicles.customerID = customers.customerID)
INNER JOIN repairs ON vehicles.vehicleId = repairs.vehicleID)""")
getId = query_db("SELECT vehicleId FROM vehicles ORDER BY vehicleId DESC",
one=True)
print(getId)
# for entry in entries:
#This is where we can put the tests to make
#sure that Sean's things actually work.
# print ("Repair Type:", entry["repairType"])
# print ("Repair ID:", entry["repairId"])
#EZ.test()
#You have to grab the repairIds again because they've changed.
myIds = EZ.getRepairIds()
EZ.test()
print("BEFORE:")
myIds = EZ.getRepairIds()
#Getting repair Types with the getter.
for repairId in myIds:
print(repairId)
print("Repair ID:", repairId, "\nRepair Type:",
EZ.getRepairType(repairId))
print("AFTTER:")
return render_template("index.html")
def view_info(page):
all_values = query_db("select * from {}".format(page))
total_count = query_db("select COUNT(*) from {}".format(page))[0][0]
print('--------------> Display info for:', page)
print('--------------> Total values are:', all_values)
print('--------------> Total data is:', total_count)
return render_template('info.html', display= all_values, name_of_table= page)
def return_latlong_fromid(id=''):
try:
if id.find('F_') != -1:
s = ("select A.lat, A.long from farmer as A where A.fid='{}'"
).format(id)
result = query_db(s)
return result
elif id.find('LD_') != -1:
s = ("select A.lat, A.long from land as A where A.lid='{}'"
).format(id)
result = query_db(s)
return result
elif id.find('SV_') != -1:
s = ("select A.lat, A.long from shopvendor as A where A.svid='{}'"
).format(id)
result = query_db(s)
return result
elif id.find('SP_') != -1:
s = ("select A.lat, A.long from storageprov as A where A.spid='{}'"
).format(id)
result = query_db(s)
return result
except Exception as e:
print(e)
return (0, 0)
def RemoveCustomer(cusID):
#Gets all vehicleIds associated with this customer.
vehIds = getAssociatedVehicles(cusID)
for vehId in vehIds:
RemoveVehicle(vehId["vehicleId"])
query_db("DELETE FROM customers WHERE customerId = ?", (int(cusID), ))
return "Customer has been deleted"
def update_shopinv_amount(units, svid, item_name):
quant = units
s = ('select units from shop_inv where svid ="{}" and item_name="{}"'
).format(svid, item_name)
res = query_db(s)
quant += res[0][0]
s = (
'update shop_inv set item_units={} where svid ="{}" and item_name="{}"'
).format(quant, svid, item_name)
query_db(s)
def details():
url = request.args.get('url')
if not url:
return redirect(url_for('admin'))
inlinks = query_db("Select * from outlinks where outlink = (?)", [url])
outlinks = query_db("Select * from outlinks where url = (?)", [url])
return render_template('admin/details.html',
url=url,
inlinks=inlinks,
outlinks=outlinks)
def details():
url = request.args.get('url')
if not url:
return redirect(url_for('admin'))
inlinks = query_db("Select * from outlinks where outlink = (?)", [url])
outlinks = query_db("Select * from outlinks where url = (?)", [url])
return render_template('admin/details.html',
url=url,
inlinks=inlinks,
outlinks=outlinks)
def createVehicle(make=None,
model=None,
year=None,
vin=None,
customerId='default'):
#If customerId isn't specified, gets the most recent one.
if (customerId == 'default'):
getId = query_db(
"SELECT customerId FROM customers ORDER BY customerId DESC",
one=True)
query_db(
"INSERT INTO vehicles (make, model, year, vin2, customerId) VALUES(?,?,?,?,?)",
(make, model, year, vin, getId['customerId']))
return "Vehicle has been created."
def add_shopvendor():
form = AddShopVendor()
if form.validate_on_submit():
print('\n\nIncoming Data: ', (form.name.data, int(form.contact.data), float(float(form.lat.data)), float(float(form.long.data)), 0))
store_length= query_db('Select COUNT(*) from shopvendor')
print('Total records before a shopvendor was added: ', store_length[0][0])
new_id= 'SV_'+str(int(store_length[0][0])+1)
print('New ID allocated to the transporter is: ', new_id)
insert('shopvendor', ('svid', 'svname', 'scontact', 'lat', 'long', 'authorized'), (new_id, form.name.data, int(form.contact.data), float(float(form.lat.data)), float(float(form.long.data)), 0))
print('All records for the shopvendor are: ')
print(query_db("Select * from shopvendor"))
flash("Successfully added new shopvendor {}!".format(form.name.data))
return redirect(url_for('add_shopvendor'))
return render_template('shopvendor.html', title="Shop Vendor", form=form)
def add_crop():
form = AddCrop()
if form.validate_on_submit():
store_length= query_db('Select COUNT(*) from crop')
print('Total records before a crop was added: ', store_length[0][0])
new_id= 'C_'+str(int(store_length[0][0])+1)
print('New ID allocated to the transporter is: ', new_id)
insert('crop', ('cid', 'cname', 'units', 'typeoffarming', 'quantity', 'price'), (new_id, form.name.data, int(float(form.units.data)), form.farming.data, float(float(form.quantity.data)), float(float(form.price.data))))
print('All records for the crop are: ')
print(query_db("Select * from crop"))
insert('landcrop',('lid','cid'),(form.land_id.data,new_id))
flash("Successfully added new crop {}!".format(new_id))
return redirect(url_for('add_crop'))
return render_template('crop.html', title="Crop", form=form)
def createRepair(repairType,
repairDescription=None,
accepted=None,
completed=False,
vehicleId='default'):
#If vehicleId isn't specified, gets the most recent one
if (vehicleId == 'default'):
getId = query_db(
"SELECT vehicleId FROM vehicles ORDER BY vehicleId DESC", one=True)
query_db(
"INSERT INTO repairs (repairType, repairDescription, accepted, completed, vehicleId) VALUES(?,?,?,?,?)",
(repairType, repairDescription, accepted, completed,
getId['vehicleId']))
return "Repair has been created."
def add_transporter():
form = AddTransporter()
if form.validate_on_submit():
store_length= query_db('Select COUNT(*) from transporter')
print('Total records before a transporter was added: ', store_length[0][0])
new_id= 'T_'+str(int(store_length[0][0])+1)
print('New ID allocated to the transporter is: ', new_id)
insert('transporter', ('tid', 'tname', 'price', 'mintwht' , 'maxtwht', 'lat', 'long', 'resavl', 'authorized'), (new_id, form.tname.data, float(float(form.price.data)), float(form.mintwht.data), float(form.maxtwht.data), float(float(form.lat.data)), float(float(form.long.data)), float(form.resavl.data), 0))
print('All records for the transporter are: ')
print(query_db("Select * from transporter"))
flash("Successfully added new transporter {}!".format(new_id))
return redirect(url_for('add_transporter'))
store_length= query_db('Select COUNT(*) from transporter')
print('Total records before loading transporter: ', store_length[0][0])
return render_template('transporter.html', title="Transporter", form=form)
def add_storage_provider():
form = AddStorageProvider()
if form.validate_on_submit():
store_length= query_db('Select COUNT(*) from storageprov')
print('Total records before a storageprov was added: ', store_length[0][0])
new_id= 'SP_'+str(int(store_length[0][0])+1)
print('New ID allocated to the storageprov is: ', new_id)
insert('storageprov', ('spid','sname','contact','lat','long','authorized'), (new_id, form.name.data, int(form.contact.data), float(float(form.lat.data)), float(float(form.long.data)), 0))
print('All records for the storageprov are: ')
print(query_db("Select * from storageprov"))
flash("Successfully added new storageprov {}!".format(new_id))
return redirect(url_for('add_storage_provider'))
store_length= query_db('Select COUNT(*) from storageprov')
print('Total records before loading storageprov: ', store_length[0][0])
return render_template('storageprov.html', title="Storageprov", form=form)
def getAssociatedVehicles(customerID):
#THIS RETURNS AN ARRAY OF DICTIONARIES
#Which should be fine since you're the only using it within this file.
vehicleIds = query_db(
"SELECT vehicleId FROM vehicles WHERE customerId = ?",
(int(customerID), ))
return vehicleIds
def add_registerland():
form = AddLand()
if form.validate_on_submit():
print('\n\nIncoming Data: ', (float(form.areaocc.data), float(float(form.lat.data)), float(float(form.long.data))))
store_length= query_db('Select COUNT(*) from land')
print('Total records before a land was added: ', store_length[0][0])
new_id= 'LD_'+str(int(store_length[0][0])+1)
print('New ID allocated to the transporter is: ', new_id)
insert('land', ('lid', 'areaocc', 'lat', 'long'), (new_id, float(float(form.areaocc.data)), float(float(form.lat.data)), float(float(form.long.data))))
print('All records for the land are: ')
print(query_db("Select * from land"))
flash("Successfully added new land {}!".format(new_id))
insert('landcrop',('cid','lid'),(form.Crop_id.data,new_id))
insert('farmerland',('fid','lid'),(form.Farmer_id.data,new_id))
return redirect(url_for('add_registerland'))
return render_template('registerland.html', title="Land", form=form)
def admin():
crawls = query_db("select * from crawls order by crawl_date desc", [])
crawls = [{
'crawl_date': crawl['crawl_date'],
'filepath': basename(crawl['filepath'])
} for crawl in crawls]
return render_template('admin/dashboard.html', crawls=crawls)
def getAssociatedRepairs(vehID):
#THIS RETURNS AN ARRAY OF DICTIONARIES
#Which should be fine since you're the only using it within this file.
repairIds = query_db(
"SELECT repairId FROM repairs WHERE vehicleId = ?",
int(vehID),
)
return repairIds
def admin():
crawls = query_db("select * from crawls order by crawl_date desc", [])
crawls = [{
'crawl_date': crawl['crawl_date'],
'filepath': basename(crawl['filepath'])
} for crawl in crawls]
return render_template('admin/dashboard.html', crawls=crawls)
def auth_no_inc_trans(lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = " Select count(*) from shopvendor as A where A.lat between {} and {} and A.long between {} and {} ".format(
lat_min, lat_max, lon_min, lon_max)
res = query_db(s)
return res
def auth_rates_off_loc(crop_name, lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = "select c.cid, c.cname, c.units, c.price from crop c inner join landcrop lc on c.cid=lc.cid inner join land l on lc.lid=l.lid inner join farmerland fl on l.lid=fl.lid inner join farmer f on fl.fid=f.fid where f.lat between {} and {} and f.long between {} and {} and c.cname='{}'".format(
lat_min, lat_max, lon_min, lon_max, crop_name)
res = query_db(s)
return res
def add_bank():
form = Addbank()
if(form.validate_on_submit()):
length = query_db('select count(*) from bank')
new_id = 'B_' + str(int(length[0][0]) + 1)
insert('bank',('bid', 'lat', 'long', 'rateoffr'),(new_id,float(form.lat.data),float(form.long.data),float(form.rateoffr.data)))
flash("Successfully added")
return redirect(url_for('add_bank'))
return render_template('bank.html',title="bank",form=form)
def farmer_nearby_storage_fac(lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"select * from storagefacloc as D where D.lat between {} and {} and D.long between {} and {}"
).format(lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def shopvend_priceofcrop_in_mylocality(crop_name, lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"select item_price from shop_inv where item_name='{}' and svid in ( select svid from shopvendor as A where A.lat between {} and {} and A.long between {} and {})"
).format(crop_name, lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def farmer_nearby_transport_fac(lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"select * from transporter where transporter.lat between {} and {} and transporter.long between {} and {}"
).format(lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def bank_rate_offr(lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"select bank.rateoffr from bank where bank.lat between {} and {} and bank.long between {} and {}"
).format(lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def farmer_nearby_crop_price(crop_name, lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"select price from crop where cname='{}' and cid in (select cid from landcrop where lid in (select lid from land as L where L.lat between {} AND {} and L.long between {} and {}))"
).format(crop_name, lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def users():
if not g.user['is_admin']:
return redirect(url_for('admin'))
if request.method == 'POST':
create_user(request.form['username'], request.form['password'], request.form.get('admin', False))
return redirect(url_for('users'))
users = query_db("select * from users", [])
return render_template('admin/users.html', users=users)
def getRepairIds():
#array for all the repairIds
myIds = []
#Returns a list of dictionaries of all repairIds with key repairId
repairIds = query_db("""SELECT repairId FROM repairs""")
#puts all the repair Ids into the array for Matias
for myId in repairIds:
myIds.append(myId["repairId"])
return myIds
def shopvend_rates_nearby_forloan(lat, long):
lat_max = lat + 20
lat_min = lat - 20
lon_min = long - 20
lon_max = long + 20
s = (
"Select bank.rateoffr from bank where bank.lat between {} and {} and bank.long between {} and {}"
).format(lat_min, lat_max, lon_min, lon_max)
result = query_db(s)
return result
def add_storagefac():
form = AddStoragefac()
if form.validate_on_submit():
length = query_db('Select COUNT(*) from storagefacloc')
new_id = 'S_' + str(int(length[0][0]) + 1)
insert('storagefacloc',('sid', 'suitcond', 'size', 'unit', 'price', 'lat', 'long', 'typeoffarming', 'spaceleft', 'availability'),
(new_id,form.suitcond.data,float(form.size.data),form.unit.data,float(form.price.data),float(form.lat.data),float(form.long.data),form.typeoffarming.data,float(float(form.spaceleft.data)),form.availability.data))
insert('spstorage',('sid','spid'),(new_id,form.Storageprov_id.data))
flash("Successfully added")
return redirect(url_for('add_storagefac'))
return render_template('storagefac.html',title="storage facility location",form = form)
def add_shop_inv():
form = AddShopInv()
if form.validate_on_submit():
length = query_db(('Select COUNT(*) from shop_inv where svid=\'{}\' and item_name=\'{}\'').format(form.svid.data,form.item_name.data))
if(length[0][0] == 0):
insert('shop_inv',('svid','item_name','item_price','units'),(form.svid.data,form.item_name.data,float(form.item_price.data),float(form.units.data)))
else:
update_shopinv_amount(float(form.units.data),form.svid.data,form.item_name.data)
flash("Successfully added")
return redirect(url_for('add_shop_inv'))
return render_template('shop_inv.html',title="shop inventory",form=form)
def add_loan():
form = AddLoan()
if form.validate_on_submit():
length = query_db('Select COUNT(*) from loan')
new_id= 'L_'+str(int(length[0][0])+1)
insert('loan',('lid', 'rateoffr', 'dateoffr', 'offrto', 'iniamt', 'pendamt'),(new_id,float(form.rateoffr.data),form.date.data,form.fid.data,float(form.iniamt.data),float(form.pendamt.data)))
insert('loantrans',('lid','transid'),(new_id,form.trans_id.data))
insert('bankfloan',('lid','bid','fid'),(new_id,form.bid.data,form.fid.data))
flash("Successfully added")
return redirect(url_for('add_loan'))
return render_template('loan.html',title="loan",form=form)
def users():
if not g.user['is_admin']:
return redirect(url_for('admin'))
if request.method == 'POST':
create_user(request.form['username'], request.form['password'],
request.form.get('admin', False))
return redirect(url_for('users'))
users = query_db("select * from users", [])
return render_template('admin/users.html', users=users)
def duplicates():
filter_type = request.args.get('type', 'near')
where = ''
if filter_type == 'exact':
where = 'where similarity = 1'
elif filter_type == 'near':
where = 'where similarity < 1'
duplicates = query_db("Select * from duplicates " + where + " order by similarity desc")
return render_template('admin/duplicates.html',
duplicates=duplicates,
type=filter_type)
