def details(lease_num):
db = config.get_database()
if request.method == 'POST' and request.form['action'] == 'delete':
print(request.form)
print('about to delete')
try:
lease_id = int(request.form['ID'])
print(lease_id)
db.delete('Lease', lease_id)
db.delete('LeaseRoyaltyMaster', lease_id)
flash('Successfully deleted lease ' + str(lease_id))
return redirect(url_for('leases.search'))
except Exception as e:
print('Failed to delete lease ' + request.form['ID'], e)
flash('Failed to delete lease ' + request.form['ID'])
return redirect(url_for('leases.search'))
elif request.method == 'POST' and request.form['action'] == 'update':
print('about to update')
ds = db.select1('Lease', ID=lease_num)
print(ds)
try:
for i in request.form:
if i != 'action':
setattr(ds, i, request.form[i])
print(ds)
db.update(ds)
flash('Successfully updated lease ' + lease_num)
return redirect(url_for('leases.details', lease_num=lease_num))
except Exception as e:
flash('Couldn\'t update a lease')
print('Couldn\'t update a lease: ', e)
return redirect(url_for('leases.search'))
elif request.method == 'GET':
try:
db = config.get_database()
lease = db.select1('Lease', ID=lease_num)
royaltymaster = db.select1('LeaseRoyaltyMaster', ID=lease_num)
wellevent_statement = """SELECT WellEventInfo.* FROM WellEventInfo, WellLeaseLink
WHERE WellLeaseLink.LeaseID="%s"
AND WellEventInfo.WellEvent=WellLeaseLink.WellID"""
# wellevents = db.select_sql(wellevent_statement % lease_num) # not sure what the situation is here, None for now
wellevents = None
return render_template('leases/details.html',
new=False,
lease=lease,
royaltymaster=royaltymaster,
wellevents=wellevents)
except Exception as e:
print(e)
abort(404)
def db(self, attrs=None):
if attrs and not isinstance(attrs, dict):
raise TypeError("Argument attrs must be a dict "
"{host: X, database: y, collection: Z}")
host = attrs.get("host", None)
database = attrs.get("database", None)
collection = attrs.get("collection", None)
if host or database or collection:
self._db = config.get_database(host=host, database=database,
collection=collection)
else:
if not self._db:
self._db = config.get_database()
def battdiagram():
# try:
db = config.get_database()
# statement = """SELECT distinct facility from VolumetricInfo"""
statement = """SELECT facility, sum(volume) as totalVol from VolumetricInfo group by facility"""
results = db.select_sql(statement)
return render_template('/reports/battdiagram.html', result=results)
def search():
if not request.args: return render_template('leases/search.html')
try:
db = config.get_database()
argument_tables = {
'Prov': 'Lease',
'ID': 'Lease'
} # allowed Property:Table pairs
kwargs = dict((k, v) for k, v in request.args.items()
if v and k in argument_tables) # this is to get rid
# of empty values coming from forms and convert multidict to dict and to check if argument is allowed
search_arguments = ""
for arg in kwargs:
compound = argument_tables[arg] + '.' + arg + '=' + '"' + kwargs[
arg] + '"'
search_arguments += " AND " + compound
statement = """SELECT * FROM Lease
WHERE DATE("{proddate}") BETWEEN Lease.StartDate and Lease.EndDate
""".format(proddate=get_proddate()) + search_arguments
result = db.select_sql(statement)
if result:
return render_template('leases/search.html',
results=result,
search_terms=request.args.to_dict())
else:
flash('No matching leases found', 'error')
return render_template('leases/search.html',
search_terms=request.args.to_dict())
except Exception as e:
print(e)
abort(404)
def update_link_row():
utils = Utils()
db = config.get_database()
return_data = dict()
try:
print('AppServer.update_link_row running', request.method)
data = utils.json_decode(request)
print('data:', data)
linktab = db.get_data_structure('LinkTab')
utils.dict_to_obj(data, linktab)
print('just before if data:', data)
print('just before if data:', data['ID'])
if data['ID'] == '0':
db.insert(linktab)
else:
db.update(linktab)
return_data['StatusCode'] = 0
return json.dumps(return_data)
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
return_data['StatusCode'] = -1
return_data['Message'] = str(e)
return json.dumps(return_data)
def get_link_row():
utils = Utils()
db = config.get_database()
try:
print('AppServer.get_link_row running', request.method)
print('Instance:', config.get_database_name(),
config.get_environment())
print('Tables', config.get_database_instance().get_table_names())
data = utils.json_decode(request)
link = db.select("LinkTab",
TabName=data['TabName'],
AttrName=data['AttrName'])
print('link', link)
if not link:
data['ID'] = 0
data['LinkName'] = ''
data['BaseTab'] = 0
data['ShowAttrs'] = ''
else:
data['ID'] = link[0].ID
data['LinkName'] = link[0].LinkName
data['BaseTab'] = link[0].BaseTab
data['ShowAttrs'] = link[0].ShowAttrs
return json.dumps(data)
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
def get_link_row():
utils = Utils()
db = config.get_database()
try:
print('AppServer.get_link_row running', request.method)
print('Instance:', config.get_database_name(), config.get_environment())
print('Tables', config.get_database_instance().get_table_names())
data = utils.json_decode(request)
link = db.select("LinkTab", TabName=data['TabName'], AttrName=data['AttrName'])
print('link', link)
if not link:
data['ID'] = 0
data['LinkName'] = ''
data['BaseTab'] = 0
data['ShowAttrs'] = ''
else:
data['ID'] = link[0].ID
data['LinkName'] = link[0].LinkName
data['BaseTab'] = link[0].BaseTab
data['ShowAttrs'] = link[0].ShowAttrs
return json.dumps(data)
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
def update_link_row():
utils = Utils()
db = config.get_database()
return_data = dict()
try:
print('AppServer.update_link_row running', request.method)
data = utils.json_decode(request)
print('data:', data)
linktab = db.get_data_structure('LinkTab')
utils.dict_to_obj(data, linktab)
print('just before if data:', data)
print('just before if data:', data['ID'])
if data['ID'] == '0':
db.insert(linktab)
else:
db.update(linktab)
return_data['StatusCode'] = 0
return json.dumps(return_data)
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
return_data['StatusCode'] = -1
return_data['Message'] = str(e)
return json.dumps(return_data)
def data_dictionary_save():
req_data = request.get_json()
db = config.get_database()
_id = req_data['ID']
if _id: # This means there is an id so it is an update
datadic = db.select1('DataDictionary', ID=int(req_data['ID']))
else:
class DataDic:
pass
req_data = request.get_json()
datadic = DataDic()
datadic._table_name = 'DataDictionary'
# Add or Update do the move
datadic.TableName = req_data['Subject']
datadic.SortOrder = int(req_data['Order'])
datadic.Attribute = req_data['Attribute']
datadic.Documentation = req_data['Description']
print('--->', req_data)
print('---ID:', _id)
if _id:
db.update(datadic)
else:
db.insert(datadic)
return "ok from datadic"
def db(self, attrs=None):
if attrs and not isinstance(attrs, dict):
raise TypeError("Argument attrs must be a dict "
"{host: X, database: y, collection: Z}")
host = attrs.get("host", None)
database = attrs.get("database", None)
collection = attrs.get("collection", None)
if host or database or collection:
self._db = config.get_database(host=host,
database=database,
collection=collection)
else:
if not self._db:
self._db = config.get_database()
def new_wellresults():
try:
db = config.get_database()
results = db.select('Well')
return render_template('new/wellresults.html', results=results)
except:
return "<h2>No results found</h2>"
def new_wellinfo():
try:
db = config.get_database()
result = db.select1('Well', ID=request.args.get('ID'))
return render_template('new/wellinfo.html', result=result)
except:
return "<h2>Well details not found</h2>"
def test_set_methods(self):
""" this tests all of the set methods since they need to work together """
save_config_file = config.ConfigObject.CONFIG_FILE
# Test the default with no file is 'unittest'
config.reset()
config.ConfigObject.CONFIG_FILE = 'badfile.xxx'
self.assertEqual(config.get_environment(), 'unittest')
config.ConfigObject.CONFIG_FILE = save_config_file
# test that if we the database name we have an unknown environmentwe
config.reset()
config.set_database_name(':memory:')
self.assertEqual(config.get_environment(), '????')
# test that if we the database name we have an unknown environmentwe
config.reset()
config.set_enviornment('test')
self.assertTrue(config.get_database_name())
# test that we have an instance and database
config.reset()
self.assertTrue(config.get_environment()) # All these values must be set... Can't test to what though
self.assertTrue(config.get_database_name())
self.assertTrue(config.get_database_instance())
self.assertTrue(config.get_database())
def search():
if not request.args:
return render_template('wells/search.html')
try:
argument_tables = {'Prov': 'WellRoyaltyMaster', 'WellType': 'WellRoyaltyMaster',
'ID': 'WellRoyaltyMaster', 'Classification': 'WellRoyaltyMaster',
'FNBandID': 'Lease'} # allowed Property:Table pairs
kwargs = dict((k, v) for k, v in request.args.items() if v and k in argument_tables) # this is to get rid
# of empty values coming from forms and convert multidict to dict and to check if argument is allowed
print(kwargs)
search_arguments = ""
for arg in kwargs:
compound = argument_tables[arg] + '.' + arg + '=' + '"' + kwargs[arg] + '"'
search_arguments += " AND " + compound
statement = """SELECT WellRoyaltyMaster.*, FNBand.FNBandName, Lease.FNBandID FROM WellRoyaltyMaster, FNBand, WellLeaseLink, Lease
WHERE WellRoyaltyMaster.ID = WellLeaseLink.WellID
AND WellLeaseLink.LeaseID = Lease.ID
AND Lease.FNBandID = FNBand.ID
AND DATE("{proddate}") BETWEEN WellRoyaltyMaster.StartDate and WellRoyaltyMaster.EndDate
""".format(proddate=get_proddate()) + search_arguments
db = config.get_database()
print(statement)
result = db.select_sql(statement)
if result:
print(result)
return render_template('wells/search.html', results=result, search_terms=request.args.to_dict())
else:
flash('No matching wells found', 'error')
return render_template('wells/search.html', search_terms=request.args.to_dict())
except Exception as e:
print(e)
abort(404)
def calculate():
from src.calc.calcroyalties import ProcessRoyalties
pr = ProcessRoyalties()
print("We are in the calculate thing..... for ", request.args)
db = config.get_database()
for monthly in db.select('Monthly',
ExtractDate=int(request.args.get('ExtractDate')),
ProdMonth=int(request.args.get('ProdMonth')),
Entity=request.args.get('Entity'),
EntityId=int(request.args.get('EntityID'))):
try:
print("about to calculate...")
pr.process_one(monthly.ExtractDate, monthly.Entity,
monthly.EntityID, monthly.ProdMonth,
monthly.Product)
except Exception as e:
print("We have an error")
traceback.print_exc(file=sys.stdout)
tb = traceback.format_exc()
return "<h2>Error displaying worksheet for " + monthly.Entity + " %s</h2><br>" % monthly.EntityID + \
str(e) + '<plaintext>' + \
tb + '</plaintext>'
# print(e)
# return 'Something went wrong during calculation for %s, %s, %i, %s:<br />%s' % \
# (monthly.Entity, monthly.EntityID, monthly.ProdMonth, monthly.Product, str(e))
return "Processing complete."
def details(facility_num):
db = config.get_database()
facility = db.select1('FacilityInfo', Facility=facility_num)
wellevents = db.select('WellFacilityLink', Facility=facility_num)
return render_template('facility/details.html',
facility=facility,
wellevents=wellevents)
def test_set_methods(self):
""" this tests all of the set methods since they need to work together """
save_config_file = config.ConfigObject.CONFIG_FILE
# Test the default with no file is 'unittest'
config.reset()
config.ConfigObject.CONFIG_FILE = 'badfile.xxx'
self.assertEqual(config.get_environment(), 'unittest')
config.ConfigObject.CONFIG_FILE = save_config_file
# test that if we the database name we have an unknown environmentwe
config.reset()
config.set_database_name('MyTest.db')
self.assertEqual(config.get_environment(), '????')
os.remove('MyTest.db')
# test that if we the database name we have an unknown environmentwe
config.reset()
config.set_enviornment('test')
self.assertTrue(config.get_database_name())
# test that we have an instance and database
config.reset()
self.assertTrue(config.get_environment(
)) # All these values must be set... Can't test to what though
self.assertTrue(config.get_database_name())
self.assertTrue(config.get_database_instance())
self.assertTrue(config.get_database())
def new_wellinfo():
try:
db = config.get_database()
result = db.select1('Well', ID=request.args.get('ID'))
return render_template('new/wellinfo.html', result = result)
except:
return "<h2>Well details not found</h2>"
def new_wellresults():
try:
db = config.get_database()
results = db.select('Well')
return render_template('new/wellresults.html', results = results)
except:
return "<h2>No results found</h2>"
def adriennews():
if request.args:
db = config.get_database()
wellIds = request.args["WellId"]
well_id = int(wellIds)
prodMonth = 201501
product = "Oil"
well = db.select1('Well', ID=well_id)
royalty = db.select1('Royaltymaster', ID=well.LeaseID)
lease = db.select1('Lease', ID=well.LeaseID)
monthly = db.select1('Monthly',
WellID=well_id,
prodMonth=prodMonth,
product=product)
calc_array = db.select('Calc', WellID=well_id, prodMonth=prodMonth)
calc = calc_array[0]
print(monthly)
else:
print("No monthly data for this well")
return render_template('worksheetas.html',
well=well,
rm=royalty,
m=monthly,
lease=lease,
calc=calc)
def setUp(self):
self.assertEqual(config.get_environment(),'unittest') # Distructive Tests must run in unittest enviornment
self.dbi = config.get_database_instance()
self.db = config.get_database()
self.dbu = DatabaseUtilities()
self.db_create = DatabaseCreate()
self.dbu.delete_all_tables()
def details(well_num):
try:
db = config.get_database()
result = db.select1('WellRoyaltyMaster', ID=well_num)
return render_template('wells/details.html', well=result)
except Exception as e:
print(e)
abort(404)
def setUp(self):
self.assertEqual('unittest', config.get_environment()) # Destructive Tests must run in unittest environment
self.dbi = config.get_database_instance()
self.db = config.get_database()
self.dbu = DatabaseUtilities()
self.db_create = DatabaseCreate()
self.dbu.delete_all_tables()
def find_calcs():
db = config.get_database()
vars = {}
# vars['ID'] = 1
vars['ExtractDate'] = 20150929
calcs = db.select('Calc', **vars)
print(len(calcs))
def new_facility_results():
try:
db = config.get_database()
results = db.select('FacilityInfo')
return render_template('new/facilityresults.html', results = results)
except Exception as e:
print('views.new_facility_results: ***Error:',e)
traceback.print_exc(file=sys.stdout)
return "<h2>No results found</h2>"
def test_get_link_data(self):
#setup stuff
utils = Utils()
db = config.get_database()
dbu = DatabaseUtilities()
db_create = DatabaseCreate()
dbu.delete_all_tables()
db_create.linktab()
dbu.create_some_test_well_royalty_masters()
dbu.create_some_test_leases()
linktab = db.get_data_structure('LinkTab')
linktab.TabName = 'Lease'
linktab.AttrName = 'ID'
linktab.LinkName = 'Lease'
linktab.BaseTab = 1
linktab.ShowAttrs = 'ID,Lessor'
db.insert(linktab)
linktab = db.get_data_structure('LinkTab')
linktab.TabName = 'Well'
linktab.AttrName = 'LeaseID'
linktab.LinkName = 'Lease'
linktab.BaseTab = 0
linktab.ShowAttrs = ''
db.insert(linktab)
data = dict()
data["TabName"] = 'Well'
data["AttrName"] = 'LeaseID'
data["AttrValue"] = 2
json_from_browser = json.dumps(data)
print('json_from_browser', json_from_browser)
# Data should be found
resp = self.myapp.post('/data/getLinkData.json',
data=json_from_browser)
print("resp:", resp)
self.assertEqual(resp.status_code, 200)
data = utils.json_decode(resp)
print('data', data)
rows = data['BaseData']
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'ID')
self.assertEqual(rows[0][1], 'Lessor')
self.assertEqual(rows[1][0], 2)
self.assertEqual(rows[1][1], 2346)
rows = data['Links']
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'Lease')
self.assertEqual(rows[0][1], 'ID')
self.assertEqual(rows[1][0], 'Well')
self.assertEqual(rows[1][1], 'LeaseID')
def new_facility_results():
try:
db = config.get_database()
results = db.select('FacilityInfo')
return render_template('new/facilityresults.html', results=results)
except Exception as e:
print('views.new_facility_results: ***Error:', e)
traceback.print_exc(file=sys.stdout)
return "<h2>No results found</h2>"
def test_get_link_data(self):
#setup stuff
utils = Utils()
db = config.get_database()
dbu = DatabaseUtilities()
db_create = DatabaseCreate()
dbu.delete_all_tables()
db_create.linktab()
dbu.create_some_test_well_royalty_masters()
dbu.create_some_test_leases()
linktab = db.get_data_structure('LinkTab')
linktab.TabName = 'Lease'
linktab.AttrName = 'ID'
linktab.LinkName = 'Lease'
linktab.BaseTab = 1
linktab.ShowAttrs = 'ID,Lessor'
db.insert(linktab)
linktab = db.get_data_structure('LinkTab')
linktab.TabName = 'Well'
linktab.AttrName = 'LeaseID'
linktab.LinkName = 'Lease'
linktab.BaseTab = 0
linktab.ShowAttrs = ''
db.insert(linktab)
data = dict()
data["TabName"] = 'Well'
data["AttrName"] = 'LeaseID'
data["AttrValue"] = 2
json_from_browser = json.dumps(data)
print('json_from_browser', json_from_browser)
# Data should be found
resp = self.myapp.post('/data/getLinkData.json',data=json_from_browser)
print("resp:",resp)
self.assertEqual(resp.status_code, 200)
data = utils.json_decode(resp)
print('data',data)
rows = data['BaseData']
self.assertEqual(len(rows),2)
self.assertEqual(rows[0][0],'ID')
self.assertEqual(rows[0][1],'Lessor')
self.assertEqual(rows[1][0],2)
self.assertEqual(rows[1][1],2346)
rows = data['Links']
self.assertEqual(len(rows),2)
self.assertEqual(rows[0][0],'Lease')
self.assertEqual(rows[0][1],'ID')
self.assertEqual(rows[1][0],'Well')
self.assertEqual(rows[1][1],'LeaseID')
def welleventinfomissing():
try:
db = config.get_database()
statement = """SELECT * FROM WellEventInfo
WHERE WellEvent NOT IN (SELECT WellEvent FROM WellRoyaltyMaster)"""
results = db.select_sql(statement)
return render_template("/reports/welleventinfomissing.html", result=results)
except Exception as e:
print(e)
abort(404)
def wells(lease_num):
db = config.get_database()
wells_statement = """SELECT WellRoyaltyMaster.* FROM WellRoyaltyMaster, WellLeaseLink
WHERE WellLeaseLink.LeaseID="%s"
AND WellRoyaltyMaster.ID=WellLeaseLink.WellID"""
wells = db.select_sql(wells_statement % lease_num)
result = []
for w in wells:
result.append(w.json_dumps())
return json.dumps(result)
def leases(lease_num):
db = config.get_database()
leases_statement = """SELECT LeaseRoyaltyMaster.* FROM LeaseRoyaltyMaster, WellLeaseLink
WHERE WellLeaseLink.WellID="%s"
AND LeaseRoyaltyMaster.ID=WellLeaseLink.LeaseID"""
lease_r = db.select_sql(leases_statement % lease_num)
result = []
for l in lease_r:
result.append(l.json_dumps())
return json.dumps(result)
def royalties():
db = config.get_database()
proddate = get_proddate_int()
statement = """SELECT * from calc, WellRoyaltyMaster where calc.EntityID = WellRoyaltyMaster.ID
and calc.prodmonth = {proddate}""".format(proddate=proddate)
result = db.select_sql(statement)
if result:
return render_template('reports/royalties.html', result=result)
else:
return "Nothing found"
def get_link_data():
utils = Utils()
db = config.get_database()
try:
data = utils.json_decode(request)
# print('data', data)
link = db.select("LinkTab",
TabName=data['TabName'],
AttrName=data['AttrName'])
# print('link',link)
if len(link) > 0:
result_rows = db.select("LinkTab",
LinkName=link[0].LinkName,
BaseTab=1)
# print('result:',result_rows)
# print('result.type:',type(result_rows))
# Get the base table
for result in result_rows:
print('We have a base table')
attrs_to_show = result.ShowAttrs.split(',')
args = dict()
args[result.AttrName] = data['AttrValue']
key_data_rows = db.select(result.TabName, **args)
rows = []
for keyData in key_data_rows:
row = []
for a in attrs_to_show:
row.append(keyData.__dict__[a])
rows.append(attrs_to_show)
rows.append(row)
data['BaseData'] = rows
# Get all the tables that the link uses
result_rows = db.select("LinkTab", LinkName=link[0].LinkName)
rows = []
for result in result_rows:
row = []
row.append(result.TabName)
row.append(result.AttrName)
rows.append(row)
data['Links'] = rows
else:
data["Message"] = data['AttrName'] + " has not been linked."
return json.dumps(data)
#
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
print("hello")
def wellroyaltymastermissing():
try:
db = config.get_database()
statement = """SELECT * FROM WellRoyaltyMaster
WHERE WellEvent NOT IN (SELECT WellEvent FROM WellEventInfo)"""
results = db.select_sql(statement)
return render_template('/reports/wellroyaltymastermissing.html',
result=results)
except Exception as e:
print(e)
abort(404)
def volumetric_to_monthly():
db = config.get_database()
statement = """SELECT * from VolumetricInfo
JOIN Well on VolumetricInfo.FromTo = Well.WellEvent
WHERE VolumetricInfo.Activity='PROD' and VolumetricInfo.Amendment=0 """
volumetric = db.select_sql(statement)
print(volumetric)
updated_counter = 0
inserted_counter = 0
total_counter = 0
for a in volumetric:
total_counter += 1
old_prod = a.ProdMonth.isoformat()[0:7]
new_prod = old_prod.replace('-', '')
existing = db.select('Monthly',
AmendNo=0,
Product=a.Product,
WellId=a.ID,
ProdMonth=new_prod)
if len(existing) == 1:
print('Already in the table: ', existing)
existing[0].ProdHours = a.Hours
existing[0].ProdVol = a.Volume
db.update(existing[0])
updated_counter += 1
elif len(existing) == 0:
ds = db.get_data_structure('Monthly')
ds.ProdMonth = new_prod
ds.WellID = a.ID
if a.Hours == 'NULL':
ds.ProdHours = None
else:
ds.ProdHours = a.Hours
ds.Product = a.Product
ds.AmendNo = a.Amendment
ds.ProdVol = a.Volume
print(ds)
print('Inserting ', ds.WellID)
db.insert(ds)
inserted_counter += 1
else:
raise AppError(
str(len(existing)) + " records found in table Monthly for " +
a)
return (
'Complete. %i records traversed, %i records inserted, %i records updated'
% total_counter, inserted_counter, updated_counter)
def royalties():
db = config.get_database()
proddate = get_proddate_int()
statement = """SELECT * from calc, wellleaselink where calc.wellid = wellleaselink.wellid
and calc.prodmonth = {proddate}""".format(
proddate=proddate
)
result = db.select_sql(statement)
if result:
return render_template("reports/royalties.html", result=result)
else:
return "Nothing found"
def user_details():
""" handles all user-related ajax calls, both for user list and individual users """
if not request.is_xhr:
abort(404)
db = config.get_database()
if request.method == 'GET' and not request.args:
""" get an entire user list """
results = db.select('Users')
return render_template('admin/users_search_results.html',
users=results)
elif request.method == 'GET':
""" get info for a user """
results = db.select1('Users', ID=request.args.get('ID'))
return render_template('admin/users_details.html', user=results)
elif request.method == 'DELETE':
""" delete user """
db.delete('Users', int(request.form['ID']))
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
elif request.method == 'POST':
""" update info for a user """
req_data = request.get_json()
user = db.select1('Users', ID=req_data['ID'])
user.Login = req_data['Login']
user.Name = req_data['Name']
user.Email = req_data['Email']
user.Permissions = req_data['Permissions']
db.update(user)
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
elif request.method == 'PATCH':
""" get an empty form to create a new user """
return render_template('admin/users_details.html', user=None)
elif request.method == 'PUT':
""" create new user """
class User:
pass
req_data = request.get_json()
user = User()
user._table_name = 'Users'
user.Login = req_data['Login']
user.Name = req_data['Name']
user.Email = req_data['Email']
user.Permissions = req_data['Permissions']
db.insert(user)
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
else:
abort(400)
def old_details():
if not request.is_xhr: abort(404)
try:
db = config.get_database()
result = db.select1('FacilityInfo', Facility=request.args.get('ID'))
wells = db.select('WellFacilityLink', Facility=request.args.get('ID'))
print('new_facility_info:',len(wells),"found")
return render_template('facility/details.html', result = result, wells = wells)
except Exception as e:
print('views.new_facility_info: ***Error:',e)
traceback.print_exc(file=sys.stdout)
return "<h2>Facility details not found</h2>"
def welleventinfomissing():
try:
db = config.get_database()
statement = """SELECT * FROM WellEventInfo
WHERE WellEvent NOT IN (SELECT WellEvent FROM WellRoyaltyMaster)"""
results = db.select_sql(statement)
return render_template('/reports/welleventinfomissing.html',
result=results)
except Exception as e:
print(e)
traceback.print_exc(file=sys.stdout)
abort(404)
def lfs():
# print('--lfs()', request.args["batt"])
db = config.get_database()
# proddate = get_proddate_int()
results = db.select("VolumetricInfo", Facility=request.args["batt"])
data = {}
facilities = []
wells = []
disps = []
unique = {}
# print(results)
for r in results:
facl = {}
facl['Facility'] = r.Facility
facl['Activity'] = r.Activity
facl['Product'] = r.Product
facl['FromTo'] = r.FromTo
facl['Volume'] = r.Volume
setattr(r, 'InorOut', inorout.get(r.Activity, '?'))
facl['InorOut'] = inorout.get(r.Activity, '?')
facl['Key'] = faclsort(facl)
setattr(r, 'Key', faclsort(facl))
facilities.append(facl)
if r.FromTo and r.FromTo not in unique:
unique[r.FromTo] = 0
if len(r.FromTo) > 12:
wells.append({"name": r.FromTo})
else:
disps.append({"name": r.FromTo})
facilities.sort(key=faclsort)
# results.sort(key=lambda r: r.Key)
results.sort(key=lambda x: x.Key)
html = render_template('/reports/battdiagramvolinout.html',
result=results,
facility=request.args["batt"])
data['html'] = html
data['facilities'] = facilities
data['wells'] = wells
data['disps'] = disps
data['batt'] = [{"name": request.args["batt"]}]
data['count'] = len(results)
# print('---lfs()--', wells)
# print('---lfs()--->', len(results))
# print(json.dumps(data))
return json.dumps(data)
def calc_list():
db = config.get_database()
statement = """SELECT * from calc, wellroyaltymaster,monthly
where calc.wellid = wellroyaltymaster.id and
calc.wellid = monthly.wellid and
calc.ProdMonth = monthly.ProdMonth
order by calc.prodMonth,calc.wellid
"""
result = db.select_sql(statement)
print("we have found: ", len(result))
if result:
return render_template("reports/calclist.html", result=result)
else:
return "Nothing found"
def old_details():
if not request.is_xhr: abort(404)
try:
db = config.get_database()
result = db.select1('FacilityInfo', Facility=request.args.get('ID'))
wells = db.select('WellFacilityLink', Facility=request.args.get('ID'))
print('new_facility_info:', len(wells), "found")
return render_template('facility/details.html',
result=result,
wells=wells)
except Exception as e:
print('views.new_facility_info: ***Error:', e)
traceback.print_exc(file=sys.stdout)
return "<h2>Facility details not found</h2>"
def details(lease_num):
try:
db = config.get_database()
lease = db.select1('Lease', ID=lease_num)
royaltymaster = db.select1('LeaseRoyaltyMaster', ID=lease_num)
wellevent_statement = """SELECT WellEventInfo.* FROM WellEventInfo, WellLeaseLink
WHERE WellLeaseLink.LeaseID="%s"
AND WellEventInfo.WellEvent=WellLeaseLink.WellID"""
# wellevents = db.select_sql(wellevent_statement % lease_num) # not sure what the situation is here, None for now
wellevents = None
return render_template('leases/details.html', lease = lease, royaltymaster = royaltymaster, wellevents=wellevents)
except Exception as e:
print(e)
abort(404)
def get_calc_list():
print("Hello Calc List")
import config
db = config.get_database()
statement = """SELECT * from calc, wellroyaltymaster,monthly
where calc.wellid = wellroyaltymaster.id and
calc.wellid = monthly.wellid and
calc.ProdMonth = monthly.ProdMonth and
calc.Product = monthly.Product
order by calc.prodMonth, calc.LeaseID, calc.wellid
"""
result = db.select_sql(statement)
print('we have found: ', len(result))
return result
def details(well_num):
db = config.get_database()
if request.method == 'POST' and request.form['action'] == 'delete':
try:
print('about to delete')
well_id = int(request.form['ID'])
print(well_id)
db.delete('WellRoyaltyMaster', well_id)
flash('Successfully deleted well ' + str(well_id))
return redirect(url_for('wells.search'))
except Exception as e:
print('Failed to delete well ' + request.form['ID'], e)
flash('Failed to delete well ' + request.form['ID'])
return redirect(url_for('wells.search'))
elif request.method == 'POST' and request.form['action'] == 'update':
ds = db.select1('WellRoyaltyMaster', ID=well_num)
try:
for i in request.form:
if i != 'action':
setattr(ds, i, request.form[i])
db.update(ds)
flash('Successfully updated well ' + well_num)
return redirect(url_for('wells.details', well_num=well_num))
except Exception as e:
flash('Couldn\'t update a well')
print('Couldn\'t update a well: ', e)
return redirect(url_for('wells.search'))
elif request.method == 'GET':
try:
db = config.get_database()
result = db.select1('WellRoyaltyMaster', ID=well_num)
return render_template('wells/details.html',
new=False,
well=result)
except Exception as e:
print(e)
abort(404)
def get_link_data():
utils = Utils()
db = config.get_database()
try:
data = utils.json_decode(request)
# print('data', data)
link = db.select("LinkTab", TabName=data['TabName'], AttrName=data['AttrName'])
# print('link',link)
if len(link) > 0:
result_rows = db.select("LinkTab", LinkName=link[0].LinkName, BaseTab=1)
# print('result:',result_rows)
# print('result.type:',type(result_rows))
# Get the base table
for result in result_rows:
print('We have a base table')
attrs_to_show = result.ShowAttrs.split(',')
args = dict()
args[result.AttrName] = data['AttrValue']
key_data_rows = db.select(result.TabName, **args)
rows = []
for keyData in key_data_rows:
row = []
for a in attrs_to_show:
row.append(keyData.__dict__[a])
rows.append(attrs_to_show)
rows.append(row)
data['BaseData'] = rows
# Get all the tables that the link uses
result_rows = db.select("LinkTab", LinkName=link[0].LinkName)
rows = []
for result in result_rows:
row = []
row.append(result.TabName)
row.append(result.AttrName)
rows.append(row)
data['Links'] = rows
else:
data["Message"] = data['AttrName'] + " has not been linked."
return json.dumps(data)
#
except Exception as e:
print('AppServer.link: ***Error:', e)
traceback.print_exc(file=sys.stdout)
print("hello")
def calc_list():
db = config.get_database()
statement = """SELECT calc.ID, calc.ExtractDate, calc.ProdMonth, calc.BaseNetRoyaltyValue,
calc.GorrNetRoyaltyValue, calc.FNBandID, calc.FNReserveID, calc.LeaseID, calc.Entity,
calc.EntityID, calc.RPBA, calc.Product, wrm.WellEvent
from calc, wellroyaltymaster wrm
where calc.EntityID = wrm.id
order by calc.ExtractDate, calc.prodMonth, calc.EntityID
"""
result = db.select_sql(statement)
# print('we have found: ', len(result))
if result:
return render_template('reports/calclist.html', result=result)
else:
return "Nothing found"
def volumetric_to_monthly():
db = config.get_database()
statement = """SELECT * from VolumetricInfo
JOIN Well on VolumetricInfo.FromTo = Well.WellEvent
WHERE VolumetricInfo.Activity='PROD' and VolumetricInfo.Amendment=0 """
volumetric = db.select_sql(statement)
print(volumetric)
updated_counter = 0
inserted_counter = 0
total_counter = 0
for a in volumetric:
total_counter += 1
old_prod = a.ProdMonth.isoformat()[0:7]
new_prod = old_prod.replace('-', '')
existing = db.select('Monthly', AmendNo=0, Product=a.Product, WellId=a.ID, ProdMonth=new_prod)
if len(existing) == 1:
print('Already in the table: ', existing)
existing[0].ProdHours = a.Hours
existing[0].ProdVol = a.Volume
db.update(existing[0])
updated_counter += 1
elif len(existing) == 0:
ds = db.get_data_structure('Monthly')
ds.ProdMonth = new_prod
ds.WellID = a.ID
if a.Hours == 'NULL':
ds.ProdHours = None
else:
ds.ProdHours = a.Hours
ds.Product = a.Product
ds.AmendNo = a.Amendment
ds.ProdVol = a.Volume
print(ds)
print('Inserting ', ds.WellID)
db.insert(ds)
inserted_counter += 1
else:
raise AppError(str(len(existing)) + " records found in table Monthly for " + a)
return('Complete. %i records traversed, %i records inserted, %i records updated' %
total_counter, inserted_counter, updated_counter)
def login():
if request.method == 'POST':
try:
db = config.get_database()
user = db.select1('Users', Login=request.form['login'])
if user:
session['login'] = user.Login
session['name'] = user.Name
session['prod_month'] = user.ProdMonth
session['permissions'] = user.Permissions
return redirect(url_for('index'))
else:
return "User not found"
except:
flash("No such user was found in the system.")
return redirect(url_for('index'))
return render_template('login.html')
def adriennews():
if request.args:
db = config.get_database()
wellIds = request.args["WellId"]
well_id = int(wellIds)
prodMonth = 201501
product = "Oil"
well = db.select1('Well', ID=well_id)
royalty = db.select1('Royaltymaster', ID=well.LeaseID)
lease = db.select1('Lease', ID=well.LeaseID)
monthly = db.select1('Monthly', WellID = well_id, prodMonth = prodMonth, product = product)
calc_array = db.select('Calc', WellID=well_id, prodMonth = prodMonth)
calc = calc_array[0]
print(monthly)
else:
print("No monthly data for this well")
return render_template('worksheetas.html', well=well, rm=royalty, m=monthly, lease=lease, calc=calc)
def user_details():
""" handles all user-related ajax calls, both for user list and individual users """
if not request.is_xhr: abort(404)
db = config.get_database()
if request.method == 'GET' and not request.args:
""" get an entire user list """
results = db.select('Users')
return render_template('admin/users_search_results.html', users=results)
elif request.method == 'GET':
""" get info for a user """
results = db.select1('Users', ID=request.args.get('ID'))
return render_template('admin/users_details.html', user=results)
elif request.method == 'DELETE':
""" delete user """
db.delete('Users', int(request.form['ID']))
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
elif request.method == 'POST':
""" update info for a user """
req_data = request.get_json()
print(req_data)
user = db.select1('Users', ID=req_data['ID'])
user.Login = req_data['Login']
user.Name = req_data['Name']
user.Email = req_data['Email']
user.Permissions = req_data['Permissions']
db.update(user)
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
elif request.method == 'PATCH':
""" get an empty form to create a new user """
return render_template('admin/users_details.html', user=None)
elif request.method == 'PUT':
""" create new user """
class User():
None
req_data = request.get_json()
user = User()
user._table_name = 'Users'
user.Login = req_data['Login']
user.Name = req_data['Name']
user.Email = req_data['Email']
user.Permissions = req_data['Permissions']
db.insert(user)
return json.dumps({'success': True}), 200, {'ContentType': 'application/json'}
else:
abort(400)
def map():
import random, json
db = config.get_database()
statement_wellevents = "SELECT * FROM WellEventInfo LIMIT 5"
wellevents = db.select_sql(statement_wellevents)
results = []
for wellevent in wellevents:
json_obj = {}
json_obj['type'] = 'Feature'
json_obj['properties'] = {}
json_obj['properties']['name'] = wellevent.WellEvent
json_obj['properties']['popupContent'] = '<b>%s</b> <br> Pool Name: %s<br><a href="/wellevent/%s">Details</a>'\
% (wellevent.WellEvent, wellevent.PoolName, wellevent.WellEvent)
json_obj['geometry'] = {}
json_obj['geometry']['type'] = 'Point'
json_obj['geometry']['coordinates'] = [random.randint(-50, 50), random.randint(-100, 100)]
results.append(json_obj)
return render_template('map.html', results=json.dumps(results))
def test_process_monthly(self):
db = config.get_database()
dbu = DatabaseUtilities()
dbu.delete_all_tables()
dbu.create_some_test_well_royalty_masters()
dbu.create_some_test_lease_royalty_masters()
dbu.create_some_test_leases()
dbu.create_some_test_well_lease_link()
dbu.create_some_test_monthly()
dbu.create_some_test_econdata()
dbu.create_some_test_rtp_info()
dbu.create_calc()
pr = ProcessRoyalties()
pr.process_one(4, 201501, "OIL")
# Check to see if the oper record exists
self.assertEqual(2, db.count("calc"))
pr.process_all()
self.assertEqual(3, db.count("calc"))
def test_run(self):
self.assertEqual(config.get_environment(), 'unittest') # Distructive Tests must run in unittest enviornment
dbu = DatabaseUtilities()
# dbc = DatabaseCreate()
loader = Loader()
dbu.delete_all_tables()
dbi = config.get_database_instance()
db = config.get_database()
loader.open_excel(self.TEST_SPREADSHEET)
# Test that the worksheet has x number of tabs
self.assertEqual(len(loader.wb.get_sheet_names()), 2)
# Test that each tab has x number of columns
self.assertEqual(len(loader.wb['Well'].columns), 12)
self.assertEqual(len(loader.wb['Royalty Master'].columns), 11)
# Test that each tab has x number of rows
self.assertEqual(len(loader.wb['Well'].rows), 9)
self.assertEqual(len(loader.wb['Royalty Master'].rows), 11)
loader.load_all_sheets()
# Test that we have x number of tables
self.assertEqual(len(dbi.get_table_names()), 2)
# check the rows and columns for well
rows = db.select('Well')
self.assertEqual(len(rows), 8)
self.assertEqual(len(dbi.get_column_names()), 12)
# check the rows and columns for royalty master
rows = db.select('RoyaltyMaster')
self.assertEqual(len(rows), 10)
self.assertEqual(len(dbi.get_column_names()), 11)
def test_get_link_row(self):
#setup stuff
db = config.get_database()
dbu = DatabaseUtilities()
db_create = DatabaseCreate()
utils = Utils()
dbu.delete_all_tables()
db_create.linktab()
linktab = db.get_data_structure('LinkTab')
linktab.TabName = 'Well'
linktab.AttrName = 'ID'
json_to_browser = json.dumps(utils.obj_to_dict(linktab))
print('json_to_browser', json_to_browser)
# Data should not be found but there should not be an error
resp = self.myapp.post('/data/getLinkRow.json',data=json_to_browser)
self.assertEqual(resp.status_code, 200)
data = utils.json_decode(resp)
self.assertEqual(data["LinkName"],'')
linktab.LinkName = 'Well'
linktab.BaseTab = 0
linktab.ShowAttrs = 'ID,UWI'
db.insert(linktab)
# Data should be found
resp = self.myapp.post('/data/getLinkRow.json',data=json_to_browser)
data = utils.json_decode(resp)
self.assertEqual(resp.status_code, 200)
self.assertEqual(data["LinkName"],'Well')
self.assertEqual(data["ShowAttrs"],'ID,UWI')
print('status:',resp.status)
print('status code:',resp.status_code)
print(resp)
print(resp.data)
def search():
if not request.args: return render_template('leases/search.html')
try:
db = config.get_database()
argument_tables = {'Prov': 'Lease', 'ID': 'Lease'} # allowed Property:Table pairs
kwargs = dict((k, v) for k, v in request.args.items() if v and k in argument_tables) # this is to get rid
# of empty values coming from forms and convert multidict to dict and to check if argument is allowed
search_arguments = ""
for arg in kwargs:
compound = argument_tables[arg] + '.' + arg + '=' + '"' + kwargs[arg] + '"'
search_arguments += " AND " + compound
statement = """SELECT * FROM Lease
WHERE DATE("{proddate}") BETWEEN Lease.StartDate and Lease.EndDate
""".format(proddate=get_proddate()) + search_arguments
result = db.select_sql(statement)
if result:
return render_template('leases/search.html', results=result, search_terms=request.args.to_dict())
else:
flash('No matching leases found', 'error')
return render_template('leases/search.html', search_terms=request.args.to_dict())
except Exception as e:
print(e)
abort(404)
def test_link_update(self):
db = config.get_database()
dbu = DatabaseUtilities()
db_create = DatabaseCreate()
utils = Utils()
dbu.delete_all_tables()
db_create.linktab()
# Test Insert
data = dict()
data["ID"] = '0'
data["TabName"] = 'Lease'
data["AttrName"] = 'ID'
data["LinkName"] = 'Lease'
json_to_browser = json.dumps(data)
resp = self.myapp.post('/data/updateLinkRow.json',data=json_to_browser)
self.assertEqual(resp.status_code, 200)
data = utils.json_decode(resp)
self.assertEqual(data["StatusCode"],0)
result = db.select('LinkTab', TabName='Lease', AttrName='ID')
self.assertEqual(len(result), 1)
# Test Update
data = utils.obj_to_dict(result[0])
data['LinkName'] = 'Changed'
json_to_browser = json.dumps(data)
resp = self.myapp.post('/data/updateLinkRow.json',data=json_to_browser)
self.assertEqual(resp.status_code, 200)
data = utils.json_decode(resp)
self.assertEqual(data["StatusCode"],0)
result = db.select('LinkTab', TabName='Lease', AttrName='ID')
self.assertEqual(len(result), 1)
self.assertEqual(result[0].LinkName,'Changed')
def details(facility_num):
db = config.get_database()
facility = db.select1('FacilityInfo', Facility=facility_num)
wellevents = db.select('WellFacilityLink', Facility=facility_num)
return render_template('facility/details.html', facility = facility, wellevents = wellevents)
import config
import psycopg2
import urlparse
from os import environ
_conn = None
def connect():
""" Connect to PostgreSQL database. The connection information for the
database is retrieved via Heroku environment variable. """
global _conn
urlparse.uses_netloc.append('postgres')
url = urlparse.urlparse(config.get_database())
try:
_conn = psycopg2.connect(
database = url.path[1:],
user = url.username,
password = url.password,
host = url.hostname,
port = url.port)
print('Connected to database!')
return True
except:
print('Connection to database failed.')
exit()
from flask import jsonify, Response
from peewee import CharField, DateTimeField, IntegerField, Model
import config
#-------------------------------------------------------------------------------
# Constants
#-------------------------------------------------------------------------------
MAX_TOP_DREAMS = 8
EXTERNAL_RESOURCE_REFRESH_FREQ = 30
#-------------------------------------------------------------------------------
# Config
#-------------------------------------------------------------------------------
app = config.get_app()
db = config.get_database()
#-------------------------------------------------------------------------------
# Models
#-------------------------------------------------------------------------------
class Dream(Model):
"""" Dream model. """
name = CharField()
count = IntegerField(default=0)
picURL = CharField(null=True)
picURLthn = CharField(null=True)
definition = CharField(null=True)
created_on = DateTimeField(default=datetime.datetime.now)
modified_on = DateTimeField(default=datetime.datetime.now)