def run_royalties_and_worksheet():
pr = ProcessRoyalties()
# pr.process_all()
pr.process_one(6, 201501, 'Oil')
# pr.process(config.get_file_dir() + 'database.xlsx')
# pr.process('d:/$temp/sample.xlsx')
print('os name is:',os.name)
if os.name != "posix":
subprocess.call(['notepad.exe', config.get_temp_dir() + 'log.txt'])
subprocess.call(['notepad.exe', config.get_temp_dir() + 'Royalty Worksheet.txt'])
def run_royalties_and_worksheet():
pr = ProcessRoyalties()
# pr.process_all()
pr.process_one(6, 201501, 'Oil')
# pr.process(config.get_file_dir() + 'database.xlsx')
# pr.process('d:/$temp/sample.xlsx')
print('os name is:', os.name)
if os.name != "posix":
subprocess.call(['notepad.exe', config.get_temp_dir() + 'log.txt'])
subprocess.call(
['notepad.exe',
config.get_temp_dir() + 'Royalty Worksheet.txt'])
def test_play_with_stuff(self):
myapp = app.app.test_client()
# resp = myapp.get('/wells')
resp = myapp.get('/well/1')
# resp = myapp.get('/adriennews/1')
html = resp.data.decode('ascii')
print(html)
f = open(config.get_temp_dir() + 'teststuff.html','w')
f.write(html)
f.flush()
f.close()
os.remove(config.get_temp_dir() + 'teststuff.html')
def process(self):
self.process_tab('BA Info', self.process_ba_info_row)
self.process_tab('WellEvent Info', self.process_well_event_row)
self.process_tab('RTA Header', self.process_rta_header_row)
self.process_tab('RTA Mineral Ownership', self.process_rta_mineral_ownership_row)
self.process_tab('WellEvent Status', self.process_well_event_status_row)
self.process_tab('Well Info', self.process_well_info_row)
self.process_tab('Well Licence', self.process_well_licence_row)
self.process_tab('RTA Wells in PE', self.process_rta_wells_in_pe_row)
self.process_tab('Facility Info', self.process_facility_info_row)
self.process_tab('Facility Licence', self.process_facility_licence_row)
self.process_tab('Facility Operator', self.process_facility_operator_row)
self.process_tab('Facility Status', self.process_facility_status_row)
self.process_tab('Well Facility link', self.process_well_facility_link_row)
self.process_tab('RTP Info', self.process_rtp_info_row)
self.process_tab('Volumetric Info', self.process_volumetric_info_row)
self.process_tab('Proration Factor', self.process_proration_factor_row)
self.process_tab('OVRTP Facility', self.process_ovrtp_facility_row)
self.process_tab('OVRTP Unit', self.process_ovrtp_unit_row)
self.process_tab('WellLeaseLink', self.process_well_lease_row)
self.process_tab('Lease', self.process_lease_row)
self.process_tab('RoyaltyMaster', self.process_royalty_master_row)
self.process_tab('Well', self.process_well_row)
# self.process_tab('Monthly', self.process_monthl_row)
self.process_tab('FNBand', self.process_fnband_row)
self.process_tab('FNReserve', self.process_fnreserve_row)
self.wb.save(config.get_temp_dir() + self.new_worksheet_name)
def process(self):
self.process_tab('BA Info', self.process_ba_info_row)
self.process_tab('WellEvent Info', self.process_well_event_row)
self.process_tab('RTA Header', self.process_rta_header_row)
self.process_tab('RTA Mineral Ownership',
self.process_rta_mineral_ownership_row)
self.process_tab('WellEvent Status',
self.process_well_event_status_row)
self.process_tab('Well Info', self.process_well_info_row)
self.process_tab('Well Licence', self.process_well_licence_row)
self.process_tab('RTA Wells in PE', self.process_rta_wells_in_pe_row)
self.process_tab('Facility Info', self.process_facility_info_row)
self.process_tab('Facility Licence', self.process_facility_licence_row)
self.process_tab('Facility Operator',
self.process_facility_operator_row)
self.process_tab('Facility Status', self.process_facility_status_row)
self.process_tab('Well Facility link',
self.process_well_facility_link_row)
self.process_tab('RTP Info', self.process_rtp_info_row)
self.process_tab('Volumetric Info', self.process_volumetric_info_row)
self.process_tab('Proration Factor', self.process_proration_factor_row)
self.process_tab('OVRTP Facility', self.process_ovrtp_facility_row)
self.process_tab('OVRTP Unit', self.process_ovrtp_unit_row)
self.process_tab('WellLeaseLink', self.process_well_lease_row)
self.process_tab('Lease', self.process_lease_row)
self.process_tab('RoyaltyMaster', self.process_royalty_master_row)
self.process_tab('Well', self.process_well_row)
# self.process_tab('Monthly', self.process_monthl_row)
self.process_tab('FNBand', self.process_fnband_row)
self.process_tab('FNReserve', self.process_fnreserve_row)
self.wb.save(config.get_temp_dir() + self.new_worksheet_name)
def browse_file():
import os
import subprocess
import config
print('os name is:', os.name)
if os.name != "posix":
subprocess.call(['notepad.exe', config.get_temp_dir() + 'log.txt'])
def process_xls(file):
results = ""
try:
start_logging()
logging.info('Batch started: ' + str(datetime.datetime.now()))
results = 'Testing the Excel file...'
wb_temp = load_workbook(file, read_only=True)
del wb_temp
results += '<br>Seems OK. Dropping tables...'
drop_create_tables()
results += '<br>Loading data from the Excel sheet...'
sqlite_load_excel.load_sheet(file)
results += '<br>Processing royalties...'
process_royalties()
results += '<br><span style="color:green;">Done. File <b>%s</b> processed successfully.</span>' % file.filename
logging.info('Batch finished: ' + str(datetime.datetime.now()))
except Exception as e:
results += '<span style="color:red;"><br>Error: %s</span>' % str(e)
print('Error -->', e)
traceback.print_exc(file=sys.stdout)
try:
with open(config.get_temp_dir() + 'calc.log') as f:
log = f.read()
log = "<xmp>" + log + "</xmp>"
except:
log = "No log was created."
return results, log
def start_logging():
import logging
import sys
import config
root = logging.getLogger()
if len(root.handlers) > 0:
root.handlers[0].close()
else:
logging.basicConfig(filename=config.get_temp_dir() + 'calc.log',
filemode='w',
level=logging.INFO)
root.setLevel(logging.INFO)
log = logging.getLogger('werkzeug')
log.setLevel(logging.CRITICAL)
ch = logging.StreamHandler(sys.stdout)
ch.setLevel(logging.DEBUG)
# formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
formatter = logging.Formatter('%(levelname)s - %(message)s')
ch.setFormatter(formatter)
root.addHandler(ch)
def write_excel():
print("About to write some excel.")
wb = Workbook()
ws = wb.active
ws1 = wb.create_sheet("Mysheet")
ws1.cell(row=1, column=1, value=12345)
wb.save(config.get_temp_dir() + 'lfsdata.xlsx')
def update_id():
wb = openpyxl.load_workbook(config.get_temp_dir() + 'sample_data.xlsx')
d = {}
well_royalty_master = wb.get_sheet_by_name('WellRoyaltyMaster')
monthly = wb.get_sheet_by_name('Monthly')
monthly['R1'] = 'WellEvent'
entity_lease_link = wb.get_sheet_by_name('EntityLeaseLink')
entity_lease_link['H1'] = 'WellEvent'
for row in range(2, well_royalty_master.max_row + 1):
id = well_royalty_master["A" + str(row)].value
well_event = well_royalty_master["D" + str(row)].value
d[id] = well_event
for row in range(2, monthly.max_row + 1):
well_id = monthly["E" + str(row)].value
if well_id in d.keys():
monthly['R' + str(row)] = d[well_id]
for row in range(2, entity_lease_link.max_row + 1):
well_id = entity_lease_link["E" + str(row)].value
if well_id in d.keys():
entity_lease_link['H' + str(row)] = d[well_id]
wb.save('sample_data_new.xslx')
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 environment
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())
# test that the default pdf location is the temp directory
config.reset()
self.assertTrue(config.get_pdf_location())
self.assertEqual(config.get_pdf_location(), config.get_temp_dir())
def load_all_from_scratch(file_name):
db_create = DatabaseCreate()
db_create.create_all()
worksheet = config.get_temp_dir() + file_name
loader = Loader()
loader.open_excel(worksheet)
loader.load_all_sheets()
def start_logging():
logging.basicConfig(filename=config.get_temp_dir() + 'calc.log', level=logging.INFO)
root = logging.getLogger()
root.setLevel(logging.INFO)
ch = logging.StreamHandler(sys.stdout)
ch.setLevel(logging.DEBUG)
# formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
formatter = logging.Formatter('%(levelname)s - %(message)s')
ch.setFormatter(formatter)
root.addHandler(ch)
def browse_file():
print('os name is:', os.name)
if os.name != "posix":
subprocess.call(['notepad.exe', config.get_temp_dir() + 'log.txt'])
def test_get_temp_dir(self):
self.assertTrue(os.path.isdir(config.get_temp_dir()),
'File directory must exist: ' + config.get_temp_dir())
def __init__(self):
self.ws = open(config.get_temp_dir() + 'Royalty Worksheet.txt','w')
self.ws.write ("Hello World - Royalty Worksheet.\n")
self.count = 0
root = logging.getLogger()
root.setLevel(logging.INFO)
ch = logging.StreamHandler(sys.stdout)
ch.setLevel(logging.DEBUG)
# formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
formatter = logging.Formatter('%(levelname)s - %(message)s')
ch.setFormatter(formatter)
root.addHandler(ch)
if __name__ == "__main__":
start_logging()
t1 = datetime.datetime.now()
logging.info('Batch started: ' + str(t1))
drop_create_tables()
# drop_table("DataDictionary")
# load_sheet(config.get_temp_dir() + "DataDictionary.xlsx")
# load_sheet("K:\\lms\\Analysis and Design\\Sample Data Saved\\DataDictionary.xlsx")
load_sheet(config.get_temp_dir() + "sample_data.xlsx")
# load_sheet("K:\\lms\\Analysis and Design\\Sample Data Saved\\sample_data 2016-10-17.xlsx")
process_royalties()
t2 = datetime.datetime.now()
logging.info('Ended: ' + str(t2))
t3 = t2 - t1
logging.info('Took: ' + str(t3))
# /Users/lshumlich/Documents/hanggliding/2016 Flights/2016-07-29_21-57.igc
"""Return a pretty-printed XML string for the Element.
"""
rough_string = ElementTree.tostring(self.root, 'utf-8')
reparsed = minidom.parseString(rough_string)
return reparsed.toprettyxml(indent=" ")
# UML:Model
ea = EA_Tables()
dbi = config.get_database_instance()
tables = dbi.get_table_names()
for t in tables:
ea.table(t)
c = dbi.execute("PRAGMA table_info(" + t + ");")
for row in c:
ea.attribute(row[1])
# print(row,row[1],row[2])
txt = ea.prettify()
f = open(config.get_temp_dir() + 'ea.xml', 'w')
f.write(txt)
f.close()
print(txt)
order = 0
row = 2
output_structure(e, 0, None)
def process_xml_dir(name):
for f in os.listdir(name):
if f.endswith('.xml'):
# print(name+f)
process_xml_file(name + f)
# process_xml_file('\\$temp\\xml\\Sample-VOLUMETRIC.xml')
# tree = et.parse('\\$temp\\xml\\Sample-FACILITY.xml')
# process_xml_file(config.get_temp_dir() + 'Sample-FACILITY.xml')
process_xml_dir(config.get_temp_dir())
# print('Length=',len(e),' Length=',len(e[0]))
# e = get_dataarea()
# print(e)
# show_children(e)
#
# e = e.firstChild
# show_children(e)
#
excel_file = config.get_temp_dir() + 'lfsdata.xlsx'
print("Writing Excel file:", excel_file)
wb.save(excel_file)
def prettify(self):
"""Return a pretty-printed XML string for the Element.
"""
rough_string = ElementTree.tostring(self.root, 'utf-8')
reparsed = minidom.parseString(rough_string)
return reparsed.toprettyxml(indent=" ")
# UML:Model
ea = EA_Tables()
dbi = config.get_database_instance()
tables = dbi.get_table_names()
for t in tables:
ea.table(t)
c = dbi.execute("PRAGMA table_info(" + t + ");")
for row in c:
ea.attribute(row[1])
# print(row,row[1],row[2])
txt = ea.prettify()
f = open(config.get_temp_dir() + 'ea.xml', 'w')
f.write(txt)
f.close()
print(txt)
def load_all_from_scratch(file_name):
db_create = DatabaseCreate()
db_create.create_all()
worksheet = config.get_temp_dir() + file_name
loader = Loader()
loader.open_excel(worksheet)
loader.load_all_sheets()
# worksheet = config.get_temp_dir() + 'sample_data.xlsx'
# loader = Loader()
# loader.open_excel(worksheet)
# loader.load_all_sheets()
# loader.close()
def load_sheet(file_name):
loader = Loader()
loader.open_excel(file_name)
loader.load_all_sheets()
if __name__ == '__main__':
#
# Note: Set the new database in config.json
#
# load_sheet()
load_all_from_scratch(config.get_temp_dir() + 'sample_data.xlsx')
self.fnreserves[v] = n
row[i].value = n
def lookup_fnband(self, row, name):
i = self.get_index(name)
v = row[i].value
if v in self.fnbands:
n = self.fnbands[v]
else:
n = random.randint(7000, 7999)
self.fnbands[v] = n
row[i].value = n
def lookup_lessor(self, row, name):
i = self.get_index(name)
v = row[i].value
if v in self.lessors:
n = self.lessors[v]
else:
n = random.randint(700000, 799999)
self.lessors[v] = n
row[i].value = n
print('Hello World!')
o = Obfuscator(config.get_temp_dir() + 'Pnx IOGC Onion Lake SK wells.xlsx')
o.process()
print('Catch you on the flip side!')
