def generate_pdfworksheet_from_calc(calc):
try:
db = config.get_database()
if calc.Entity == 'Well':
well = db.select1('WellRoyaltyMaster', ID=calc.EntityID)
else:
well = None
entity_lease_link_array = db.select('EntityLeaseLink', Entity=calc.Entity, EntityID=calc.EntityID)
if len(entity_lease_link_array) == 0:
raise AppError("There were no well_lease_link records for " + str(calc.WellID) + str(calc.ProdMonth))
entity_lease_link = entity_lease_link_array[0]
royalty = db.select1('LeaseRoyaltyMaster', ID=calc.LeaseID)
lease = db.select1('Lease', ID=calc.LeaseID)
history = db.select_sql("""SELECT ID, ExtractDate, BaseNetRoyaltyValue, GorrNetRoyaltyValue
from Calc
WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
and Product = "{}" and RPBA = "{}"
order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
calc.Product, calc.RPBA))
prev_BaseNetRoyaltyValue = 0.0
prev_GorrNetRoyaltyValue = 0.0
for h in history:
h.BookedBaseNetRoyaltyValue = h.BaseNetRoyaltyValue - prev_BaseNetRoyaltyValue
h.BookedGorrNetRoyaltyValue = h.GorrNetRoyaltyValue - prev_GorrNetRoyaltyValue
h.Booked = h.BookedBaseNetRoyaltyValue + h.BookedGorrNetRoyaltyValue
prev_BaseNetRoyaltyValue = h.BaseNetRoyaltyValue
prev_GorrNetRoyaltyValue = h.GorrNetRoyaltyValue
monthly_array = db.select('Monthly', ExtractDate=calc.ExtractDate, Entity=calc.Entity, EntityID=calc.EntityID,
prodMonth=calc.ProdMonth, product=calc.Product, RPBA=calc.RPBA)
if len(monthly_array) == 0:
raise AppError("There were no monthly records for well: " + str(calc.WellID) + " ProdDate: " +
str(calc.ProdMonth) + " Product: " + calc.Product)
monthly = monthly_array[0] # if there are multiple pick the first one
ba = db.select1('BAInfo', BAid=monthly.RPBA, BAType='RTP')
calc_specific = DataStructure(calc.RoyaltySpecific)
rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=calc.Product, Payer=monthly.RPBA,
Date=prod_month_to_date(calc.ProdMonth))
royalty.format_gorr = format_gorr(calc.Gorr)
return render_template('worksheet/pdfcalc_worksheet.html',
well=well, rm=royalty, m=monthly, lease=lease,
calc=calc, calc_sp=calc_specific, entity_lease_link=entity_lease_link,
ba=ba, rtp_info=rtp_info, history=history)
except Exception as e:
print('views.worksheet: ***Error:', e)
traceback.print_exc(file=sys.stdout)
tb = traceback.format_exc()
return "<h2>Error displaying worksheet for " + calc.Entity + " %s</h2><br>" % calc.EntityID + str(e) + \
'<plaintext>' + tb + '</plaintext>'
def insert_data(self, table_name, row, header_row):
insert = 'INSERT INTO ' + table_name + ' VALUES ('
data = ""
i = 0
for h in header_row:
cell = row[i]
if type(cell.value) is str:
data = data + "'" + str(cell.value).replace('\'',
'\'\'') + "',"
# data = data + "'" + cell.value + "',"
# data = data + '"' + cell.value + '",'
elif type(cell.value) is int:
data = data + str(cell.value) + ','
elif type(cell.value) is float:
data = data + str(cell.value) + ','
elif type(cell.value) is datetime.datetime:
data = data + '"' + str(cell.value) + '",'
elif cell.value is None:
data += ' Null,'
else:
raise AppError('*** Not Loaded: ' + cell.value + " " +
type(cell.value))
i += 1
data += ')'
data = data.replace(',)', ')')
insert += data
# if config.debug_sql():
# print('>' + str(config.debug_sql()) + insert)
self.dbi.execute(insert)
def execute(self, statement):
if self.debug_sql:
print('SqliteInstance.execute:', statement)
try:
return self.cursor.execute(statement)
except OperationalError as e:
raise AppError(str(e) + '--> ' + statement)
def lookup_vars(self, var_list, monthly, calc=None):
var_values = {}
for v in var_list:
if v == "sales":
var_values[v] = monthly.SalesVol
elif v == "prod":
var_values[v] = monthly.ProdVol
elif v == "gj":
var_values[v] = monthly.GJ
elif v == "heat":
var_values[v] = monthly.Heat
elif v == "price":
var_values[v] = monthly.SalesPrice
elif v == "royalty_price":
var_values[v] = calc.RoyaltyPrice
elif v == "trans":
var_values[v] = monthly.TransRate
elif v == "processing":
var_values[v] = monthly.ProcessingRate
elif v == "gca":
var_values[v] = monthly.GCARate
else:
if v[0:2] == 'm.':
# v = v[2:]
var_values[v] = self.lookup(v, monthly.ProdMonth)
else:
var_values[v] = self.lookup(v, 0)
if not var_values[v]:
raise AppError(
"Looking up '{}' for a Formula but it was not found or it was 'None'"
.format(v))
return var_values
def select1(self, table, **kwargs):
result = self.select(table, **kwargs)
if len(result) == 1:
return result[0]
else:
raise AppError(
"sqlite_database.select1 should have only found 1, but we found "
+ str(len(result)) + " in table: " + table +
". We are only looking for " + str(kwargs))
def select_sql(self, statement):
if not statement.startswith('SELECT'):
raise AppError(
'Only SELECT statements are allowed, this is not one: "%s"' %
statement)
self.dbi.execute(statement)
result = self.dbi.cursor.fetchall()
result = self.sql_to_object('join', result)
return result
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 to_db_value(self, value):
if type(value) is str:
# return '"' + value + '"'
return "'" + value + "'"
elif type(value) is int or type(value) is float:
return str(value)
elif type(value) is bool:
return '1' if value else '0'
elif not value:
return 'null'
raise AppError(
'sqlite_database.to_db_value can not handle update of: ' +
str(type(value)) + ':' + str(value))
def volume(self, start_date, prod_date, volume):
"""
A volume is being added or modified to the total incentive
this will return what the total incentive volume is
"""
if start_date > prod_date:
raise AppError('Incentive Start Date: ' + str(start_date) +
' must be before Prod Date: ' + str(prod_date))
if self.start_date is not start_date:
if start_date > prod_date:
raise AppError('Incentive Start Date: ' + str(start_date) +
' must be before Prod Date: ' + str(prod_date))
self.start_date_changed(start_date)
i = self.index(start_date, prod_date)
while len(self.volume_array) <= i:
self.volume_array.append(0.0)
self.volume_array[i] = volume
if prod_date > self.last_prod_date:
self.last_prod_date = prod_date
return self.total()
def update(self, ds):
# Rule 1: all tables that can be updated must have an ID attrabute that is the primary key.
orig_ds = self.select(ds._table_name, ID=ds.ID)
if len(orig_ds) == 0:
raise AppError('sqlite_database.update can not find: ' +
str(ds.ID) + ' to update.')
orig_dict = orig_ds[0].__dict__
new_dict = ds.__dict__
to_update = ''
for attr in orig_dict:
if not attr.startswith('_'):
if orig_dict[attr] != new_dict[attr]:
# todo: Do that audit of the records right here.
# print('difference:',attr,orig_dict[attr])
if len(to_update) > 0:
to_update += ','
to_update += attr + '=' + self.to_db_value(new_dict[attr])
if len(to_update) > 0:
statement = 'UPDATE ' + ds._table_name + ' SET ' + to_update + ' where ID = ' + str(
ds.ID)
self.dbi.execute(statement)
self.dbi.commit()
def find_expression(s):
a = s.find('=(')
if a < 0:
brace_count = 0
# raise AppError('No formula found in string: ' + s)
else:
brace_count = 1
i = 0
for i in range(a + 2, len(s)):
if s[i] == ')':
brace_count -= 1
if s[i] == '(':
brace_count += 1
if brace_count == 0:
break
if brace_count != 0:
raise AppError(
'The formula was not ended. Looking for matching ending ")" in string: '
+ s)
if a == -1:
return 0, len(s)
return a, i
def get_proddate_int() -> int:
try:
return int(request.cookies['proddate'])
except:
raise AppError('Proddate cookie not set for some reason')
def get_proddate():
cookie = request.cookies['proddate']
if not cookie:
raise AppError('Proddate cookie not set for some reason')
proddate = cookie[0:4] + "-" + cookie[4:6] + "-01"
return proddate
def raise_app_error(self):
raise AppError("There is an apperror")
def generate_worksheet_from_calc(calc, with_js='y'):
"""
Generate html for the calc row that is passed
:param calc: a full calc record
:param with_js: default is 'y' only other option is 'n' and should only be used to work around a
pdf generation issue
:return: html of the worksheet for the calc record
"""
try:
db = config.get_database()
if calc.Entity == 'Well':
well = db.select1('WellRoyaltyMaster', ID=calc.EntityID)
else:
well = None
entity_lease_link_array = db.select('EntityLeaseLink', Entity=calc.Entity, EntityID=calc.EntityID)
if len(entity_lease_link_array) == 0:
raise AppError("There were no well_lease_link records for " + str(calc.WellID) + str(calc.ProdMonth))
entity_lease_link = entity_lease_link_array[0]
royalty = db.select1('LeaseRoyaltyMaster', ID=calc.LeaseID)
lease = db.select1('Lease', ID=calc.LeaseID)
history = db.select_sql("""SELECT *
from Calc
WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
and Product = "{}" and RPBA = "{}"
order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
calc.Product, calc.RPBA))
# history = db.select_sql("""SELECT ID, ExtractDate, BaseNetRoyaltyValue, GorrNetRoyaltyValue
# from Calc
# WHERE ProdMonth = "{}" and LeaseID = "{}" and Entity = "{}" and EntityID = "{}"
# and Product = "{}" and RPBA = "{}"
# order by ExtractDate""".format(calc.ProdMonth, calc.LeaseID, calc.Entity, calc.EntityID,
# calc.Product, calc.RPBA))
prev_BaseNetRoyaltyValue = 0.0
prev_GorrNetRoyaltyValue = 0.0
i = 0
for h in history:
h.BookedBaseNetRoyaltyValue = h.BaseNetRoyaltyValue - prev_BaseNetRoyaltyValue
h.BookedGorrNetRoyaltyValue = h.GorrNetRoyaltyValue - prev_GorrNetRoyaltyValue
h.Booked = h.BookedBaseNetRoyaltyValue + h.BookedGorrNetRoyaltyValue
prev_BaseNetRoyaltyValue = h.BaseNetRoyaltyValue
prev_GorrNetRoyaltyValue = h.GorrNetRoyaltyValue
# Set the original calc record to the history record the one before this one
if h.ExtractDate == calc.ExtractDate and i > 0:
calc.original(history[i-1])
i += 1
monthly_array = db.select('Monthly', ExtractDate=calc.ExtractDate, Entity=calc.Entity, EntityID=calc.EntityID,
prodMonth=calc.ProdMonth, product=calc.Product, RPBA=calc.RPBA)
if len(monthly_array) == 0:
raise AppError("There were no monthly records for well: " + str(calc.WellID) + " ProdDate: " +
str(calc.ProdMonth) + " Product: " + calc.Product)
monthly = monthly_array[0] # if there are multiple pick the first one
ba = db.select1('BAInfo', BAid=monthly.RPBA, BAType='RTP')
calc_specific = DataStructure(calc.RoyaltySpecific)
rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=calc.Product, Payer=monthly.RPBA,
Date=prod_month_to_date(calc.ProdMonth))
royalty.format_gorr = format_gorr(calc.Gorr)
return render_template('worksheet/calc_worksheet.html',
with_js=with_js,
well=well, rm=royalty, m=monthly, lease=lease,
calc=calc, calc_sp=calc_specific, entity_lease_link=entity_lease_link,
ba=ba, rtp_info=rtp_info, history=history)
except Exception as e:
print('views.worksheet: ***Error:', e)
traceback.print_exc(file=sys.stdout)
tb = traceback.format_exc()
return "<h2>Error displaying worksheet for " + calc.Entity + " %s</h2><br>" % calc.EntityID + str(e) + \
'<plaintext>' + tb + '</plaintext>'