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