def generate_worksheet(well_id, prod_month, rpba):
try:
db = config.get_database()
product = "OIL"
well = db.select1('WellRoyaltyMaster', ID=well_id)
well_lease_link_array = db.select('WellLeaseLink', WellID=well_id)
if len(well_lease_link_array) == 0:
raise AppError("There were no well_lease_link records for " + str(well_id) + str(prod_month))
well_lease_link = well_lease_link_array[0]
royalty = db.select1('LeaseRoyaltyMaster', ID=well_lease_link.LeaseID)
royalty.format_gorr = format_gorr(royalty.Gorr)
lease = db.select1('Lease', ID=well_lease_link.LeaseID)
if rpba:
monthly_array = db.select('Monthly', WellID=well_id, prodMonth=prod_month, product=product, RPBA=rpba)
else:
monthly_array = db.select('Monthly', WellID=well_id, prodMonth=prod_month, product=product)
if len(monthly_array) == 0:
raise AppError("There were no monthly records for " + str(well_id) + str(prod_month) + product)
monthly = monthly_array[0] # if there are multiple pick the first one
ba = db.select1('BAInfo',BAid=monthly.RPBA)
calc = db.select1('Calc', WellID=well_id, ProdMonth=prod_month,RPBA=monthly.RPBA)
rtp_info = db.select1('RTPInfo', WellEvent=well.WellEvent, Product=product, Payer=monthly.RPBA,
Date=prod_month_to_date(prod_month))
# calc = calc_array[0]
# print(monthly)
return render_template('worksheet/calc_worksheet.html',
well=well, rm=royalty, m=monthly, lease=lease,
calc=calc, well_lease_link=well_lease_link, ba=ba, rtp_info=rtp_info)
except Exception as e:
print('views.worksheet: ***Error:', e)
traceback.print_exc(file=sys.stdout)
return "<h2>Error displaying worksheet for well %s</h2><br>" % well_id + str(e)
def test_run(self):
t = prod_month_to_date(201611)
self.assertEqual(2016, t.year)
self.assertEqual(11, t.month)
self.assertEqual(1, t.day)
self.assertEqual(12, t.hour)
self.assertEqual(0, t.minute)
def process_one(self, well_id, prod_month, product):
logging.info('**** Processing *** {0:6d} {1:6d} {2:}'.format(well_id, prod_month, product))
# errorCount = 0
well = self.db.select1('WellRoyaltyMaster', ID=well_id)
well_lease_link_array = self.db.select('WellLeaseLink', WellID=well_id)
if len(well_lease_link_array) == 0:
raise AppError("There were no well_lease_link records for: " + str(well_id) + str(prod_month))
well_lease_link = well_lease_link_array[0]
royalty = self.db.select1('LeaseRoyaltymaster', ID=well_lease_link.LeaseID)
lease = self.db.select1('Lease', ID=well_lease_link.LeaseID)
# todo Create a test to ensure there is a RTPInfo for the well. raise an apperror if note
# todo call the prod_month_to_date in the select and not everytime we use the select.
rtp_info_array = self.db.select('RTPInfo', WellEvent=well.WellEvent, Product='OIL',
Date=prod_month_to_date(prod_month))
if len(rtp_info_array) == 0:
print('--- Well not found in RTPInfo', well.ID, well.WellEvent)
else:
print('--- Well found in RTPInfo Payer:', rtp_info_array[0].Payer)
monthly_list = self.db.select('Monthly', WellID=well_id, ProdMonth=prod_month, Product=product)
if len(monthly_list) == 0:
raise AppError("No monthly data found for WellID: " + str(well_id) + " ProdMonth:" + str(prod_month) +
" Product:" + product)
calc_array = self.db.select('Calc', WellID=well_id, ProdMonth=prod_month)
for calc in calc_array:
self.db.delete('Calc', calc.ID)
for monthly in monthly_list:
# calc_array = self.db.select('Calc', WellID=well_id, ProdMonth=prod_month)
# if len(calc_array) == 0:
# calc = None
# else:
# calc = calc_array[0]
rtp_info = self.db.select1('RTPInfo', WellEvent=well.WellEvent, Product=product, Payer=monthly.RPBA,
Date=prod_month_to_date(prod_month))
# calc = None
calc = self.zero_royalty_calc(prod_month, well_id, product)
self.calc_royalties(well, royalty, calc, monthly, well_lease_link, rtp_info)
calc.RPBA = monthly.RPBA
calc.FNReserveID = lease.FNReserveID
calc.FNBandID = lease.FNBandID
# if len(calc_array) == 0:
self.db.insert(calc)
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 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>'