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