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