def projectNew(username):
form = NewProjectForm()
allUsers = User.query.filter(User.username != "admin").all()
currentRole = User.query.filter_by(
username=current_user.username).first_or_404()
user = User.query.filter_by(username=username).first_or_404()
if form.validate_on_submit():
if form.priority.data == "":
form.priority.data = "9999"
project = Projects(description=form.description.data,
company=form.company.data,
requester=form.requester.data,
status=form.status.data,
priority=form.priority.data,
priority_dept=form.priority_dept.data,
department=form.department.data,
ticket=form.ticket.data,
hours=form.hours.data,
user_id=user.id)
db.session.add(project)
db.session.commit()
flash('Project Added!')
return redirect(url_for('developer', username=username))
return render_template('newprojects.html',
title='Add Projects',
user=user,
form=form,
allUsers=allUsers,
currentRole=currentRole,
jfilesize=jfilesize,
cfilesize=cfilesize,
dev=username)
def add_loc_proj(i, warning=None):
""" Compiles location and project data given an index for the tbl_project
DataFrame
"""
# -- Adding Location Data ----------------------------------------------- #
state_code = tbl_project['txt_StateCode'][i]
state_name = lkp_stateandfhwadistrict.loc[
lkp_stateandfhwadistrict['txt_StateCode'] == state_code,
'txt_StateName'].values[0] if not pd.isna(state_code) else None
country_code = tbl_project['txt_CountryCode'][i]
country_name = tbl_country.loc[tbl_country['txt_CountryCode'] ==
country_code,
'txt_CountryDescription'].values[0]
loc = Locations(
address=tbl_project['txt_Address'][i]
if not pd.isna(tbl_project['txt_Address'][i]) else None,
city=tbl_project['txt_City'][i]
if not pd.isna(tbl_project['txt_City'][i]) else None,
county=tbl_project['txt_County'][i]
if not pd.isna(tbl_project['txt_County'][i]) else None,
state=state_name,
country=country_name,
latitude=tbl_project['dbl_Latitude'][i]
if abs(tbl_project['dbl_Latitude'][i]) < 100 else None,
longitude=tbl_project['dbl_Longitude'][i]
if abs(tbl_project['dbl_Longitude'][i]) < 100 else None,
)
db.session.add(loc)
# -- Adding Project Data ------------------------------------------------ #
prj = Projects(
location=loc,
user_id=data_owner().id,
source_db='FHWA DFLTD v.2',
source_id=int(tbl_project['lng_KeyProject'][i]),
description=tbl_project['mem_Remarks'][i]
if not pd.isna(tbl_project['mem_Remarks'][i]) else None,
site_name=tbl_project['txt_ProjectName'][i]
if not pd.isna(tbl_project['txt_ProjectName'][i]) else None,
source_ref=tbl_project['txt_Publication'][i]
if not pd.isna(tbl_project['txt_Publication'][i]) else None,
contractor=tbl_project['txt_GeneralContractor'][i]
if not pd.isna(tbl_project['txt_GeneralContractor'][i]) else None,
number=tbl_project['txt_ProjectID'][i]
if not pd.isna(tbl_project['txt_ProjectID'][i]) else None,
title=tbl_project['txt_Title'][i]
if not pd.isna(tbl_project['txt_Title'][i]) else None,
date_added=pd.to_datetime(tbl_project['dte_AddDate'][i] if not pd.isna(
tbl_project['dte_AddDate'][i]) else None),
warning=warning)
return prj
def UpdateProject(url, data_http=None, data_https=None):
search_project = Projects.query.filter_by(url=url).first()
if not search_project:
project = Projects(url=url,
json_http=data_http,
json_https=data_https,
json_last_updated=datetime.datetime.now())
db.session.add(project)
db.session.commit()
return print('Added ' + url + ' to DB.')
search_project.json_https = data_https
db.session.add(search_project)
db.session.commit()
def admin_Projects():
project = Projects.query.all()
if request.method == 'POST':
file = request.files['p_foto']
filename = file.filename
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
proje = Projects(p_title=request.form['p_title'],
p_header=request.form['p_header'],
p_url=request.form['p_url'],
p_foto=filename)
db.session.add(proje)
db.session.commit()
return redirect('/admin/projects')
return loginCheck('/admin/a_Projects.html', project=project)
def add_loc_proj(prj_id, pile_id):
""" Compiles location and project data given an index for the tbl_project
DataFrame
"""
# -- Adding Location Data ----------------------------------------------- #
parish_id = int(dt_projects['Parish_1'][dt_projects.ID == prj_id])
parish = lst_parishes['Parish_Name'][lst_parishes.ID ==
parish_id].values[0]
lat = dt_testpiles['Latitude'][dt_testpiles.ID == pile_id].values[0]
lon = dt_testpiles['Longitude'][dt_testpiles.ID == pile_id].values[0]
loc = Locations(county=parish,
state='Louisiana',
country='USA',
latitude=lat,
longitude=lon)
db.session.add(loc)
# -- Adding Project Data ------------------------------------------------ #
contr_id = int(dt_projects['Contractor'][dt_projects.ID == prj_id])
contractor = lst_contractors['Contractor'][lst_contractors.ID ==
contr_id].values[0]
source_ref = 'From: {}'.format(
source_dic[dt_projects['Source'][dt_projects.ID == prj_id].values[0]])
number = '{}; {}'.format(
dt_projects['Project_Num_H'][dt_projects.ID == prj_id].values[0],
dt_projects['Project_Num_old'][dt_projects.ID == prj_id].values[0])
prj = Projects(
location=loc,
user_id=data_owner().id,
source_db='LTRC LAPLTD',
source_id=prj_id,
source_ref=source_ref,
number=number,
title=dt_projects['Project_Name'][dt_projects.ID == prj_id].values[0],
site_name=dt_projects['Route'][dt_projects.ID == prj_id].values[0] if
not pd.isna(dt_projects['Route'][dt_projects.ID == prj_id].values[0])
else None,
contractor=contractor)
return prj
def load_iowa_records():
""" Iterates through source files and adds the Iowa PILOT records to the
database
"""
plt_records = pd.read_csv(plt_records_path)
counties = pd.read_csv(counties_path)
pile_types = pd.read_csv(pile_types_path)
avg_soil = pd.read_csv(avg_soil_path)
slt_results = pd.read_csv(slt_path)
print('--- Importing Iowa PILOT records ---')
for i in tqdm(range(len(plt_records))):
# -- Adding Location Data ------------------------------------------- #
loc = Locations(
county=counties.loc[
counties['ID'] == plt_records['County'][i], 'County'].iloc[0],
township=plt_records['Township'][i],
description=plt_records['Pile Location'][i])
db.session.add(loc)
# -- Adding Project Data -------------------------------------------- #
wt_loc = plt_records['Water Table Location'][i]
if pd.isna(wt_loc):
desc = None
else:
desc = 'Water Table ' + str(wt_loc)
prj = Projects(
user_id=data_owner().id,
source_db='Iowa PILOT',
source_id=int(plt_records['ID'][i]),
contractor=plt_records['Contractor'][i],
number=plt_records['Project Number'][i],
description=desc,
location=loc)
db.session.add(prj)
# -- Adding Pile Data ----------------------------------------------- #
emb_pile_length = plt_records['Embedded Pile Length'][i]
pile_length = plt_records['Pile Length'][i]
if pd.isna(pile_length):
pile_length = pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Length'
].iloc[0]
if pd.isna(pile_length):
pile_length = emb_pile_length
pile = Piles(
project=prj,
type=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Type'
].iloc[0],
shape=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Shape'
].iloc[0] if not pd.isna(pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Shape'
].iloc[0]) else None,
emb_length=emb_pile_length,
length=pile_length,
diameter=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Diameter'
].iloc[0] if not pd.isna(pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Diameter'
].iloc[0]) else None,
cross_area=plt_records['Pile Cross-Sectional Area'][i] if not
pd.isna(plt_records['Pile Cross-Sectional Area'][i]) else None,
weight=plt_records['Weight of Pile'][i] if not pd.isna(
plt_records['Weight of Pile'][i]) else None,
predrill_depth=plt_records['Initial Bored Hole Depth'][i] if
plt_records['Initial Bored Hole Depth'][i] != 0 else None,
predrilled=True if plt_records['Initial Bored Hole Depth'][i] != 0
else None,
date_driven=pd.to_datetime(
plt_records['Date Driven'][i] if not
pd.isna(plt_records['Date Driven'][i]) else None),
design_load=plt_records['Design Load'][i] if not
pd.isna(plt_records['Design Load'][i]) else None,
toe_elevation=plt_records['Pile Toe Elevation'][i] if not
pd.isna(plt_records['Pile Toe Elevation'][i]) else None,
)
db.session.add(pile)
# -- Adding Soil Data ----------------------------------------------- #
soil_class = {1: 'CLAY', 2: 'SAND', 3: 'SACL'}
p_soil = plt_records['Test Site Soil Classification'][i]
boring = Borings(
project=prj,
name='Average Soil Profile',
predom_soil=soil_class[p_soil] if not pd.isna(p_soil) else None,
remarks=plt_records['Borehole Numbers at Test Pile Location'][i],
)
db.session.add(boring)
layers = avg_soil[avg_soil['Record ID'] == i+1]
for l in layers.index:
layer = Layers(
boring=boring,
index=layers['Soil Layer'][l] if not
pd.isna(layers['Soil Layer'][l]) else None,
soil_type=iowa_soil_dict[layers['Material Description'][l]] if
not pd.isna(layers['Material Description'][l]) else None,
description=layers['Material Description'][l],
height=layers['Thickness'][l],
nval=layers['NAVG'][l]
if not pd.isna(layers['NAVG'][l]) else None,
iowa_unit_friction=layers['Unit Friction'][l],
iowa_total_friction=layers['Total Friction'][l],
)
db.session.add(layer)
# -- Adding load test data ------------------------------------------ #
load_test = LoadTests(
pile=pile,
direction='Compression (Static)',
tested_by=plt_records['Tested By'][i],
date_tested=pd.to_datetime(
plt_records['Date Tested'][i] if not
pd.isna(plt_records['Date Tested'][i]) else None),
rebound_time=plt_records['Rebound Time Duration'][i] if not
pd.isna(plt_records['Rebound Time Duration'][i]) else None,
rebound_displacement=plt_records['Rebound Gauge Reading'][i],
remarks=plt_records['Static Load Test Remarks'][i],
reliable=True
if plt_records['Load Test Reliability Classification'][i] == 1
else False,
)
db.session.add(load_test)
static_test = slt_results[slt_results['Record ID'] == i+1]
i_slt = 1
for p in static_test.index:
slt_point = StaticTests(
index=i_slt,
load=static_test['Load (Tons)'][p] * 1000,
displacement=static_test['Gauge Reading (in)'][p],
load_test=load_test
)
db.session.add(slt_point)
i_slt += 1
# -- Adding Interpreted Capacity Data ------------------------------- #
interp_dict = {
'Maximum Applied Load': 'Maximum Load',
'Highest Gauge Reading': 'Maximum Displacement',
'Davisson Pile Capacity': 'Standard Davisson'
}
for key in interp_dict:
ival = plt_records[key][i]
if not pd.isna(ival) and float(ival) > 0:
ic = InterpCapacities(
load=float(ival) * 2 if key != 'Highest Gauge Reading'
else None,
displacement=float(ival) if key == 'Highest Gauge Reading'
else None,
type=interp_dict[key],
origin='source DB',
load_test=load_test)
db.session.add(ic)
# -- Adding Calculated Capacity Data -------------------------------- #
calc_dict = {
'Formula Bearing': 'Iowa DOT Modified ENR (bearing)',
'Theoretical End Bearing': 'Iowa Theoretical End Bearing',
'Theoretical Pile Capacity': 'Iowa Theoretical Capacity',
'Blue Book Capacity': 'Iowa Blue Book Method',
'SPT Capacity': 'Meyerhof',
'Alpha Capacity': 'API 1984',
'Beta Capacity': 'Beta Burland 1973',
'Nordland Capacity': 'Nordlund',
'ENR Capacity': 'ENR Formula',
'Modified ENR Capacity': 'Iowa DOT Modified ENR',
'Gates Capacity': 'Gates Formula',
'FHWA Modified Gates Capacity': 'FHWA Modified Gates Formula',
'Janbu Capacity': 'Janbu Formula',
'PCUBC Capacity': 'Pacific Coast Uniform BC Formula',
'WSDOT Capacity': 'Washington DOT Formula'
}
for key in calc_dict:
cval = plt_records[key][i]
if not pd.isna(cval) and float(cval) > 0:
cc = CalcCapacities(
load=float(cval) * 2,
type=calc_dict[key],
origin='source DB',
pile=pile)
db.session.add(cc)
# -- Adding Attachments --------------------------------------------- #
attach_attr = ['Attachments (1)', 'Attachments (2)', 'Attachments (3)',
'Attachments (4)', 'Attachments (5)', 'Attachments (6)']
for attr in attach_attr:
val = plt_records[attr][i]
if not pd.isna(val):
fname = val.split('#')[0]
furl = val.split('#')[1]
attachment = Attachments(
project=prj,
file_name=fname,
file_url=furl
)
db.session.add(attachment)
# -- Adding Miscellaneous Data -------------------------------------- #
misc = Misc(
project=prj,
iowa_test_folder=plt_records['Test Folder'][i],
iowa_lab_number=plt_records['Lab Number'][i],
iowa_design_number=plt_records['Design Number'][i],
iowa_section=plt_records['Section'][i],
iowa_date_reported=pd.to_datetime(
plt_records['Date Reported'][i] if not
pd.isna(plt_records['Date Reported'][i]) else None),
iowa_record_complete=plt_records['Record Complete'][i],
iowa_borehole_count=plt_records['Total Number of Boreholes'][i]
if not pd.isna(
plt_records['Total Number of Boreholes'][i]) else None,
iowa_spt_count=plt_records['Boreholes With SPT Data'][i] if not
pd.isna(plt_records['Boreholes With SPT Data'][i]) else None,
iowa_borehole_near_pile=plt_records[
'Borehole at Test Pile Location'][i]
)
db.session.add(misc)
db.session.commit()
def add_olson_data(record, qs=None):
""" Accepts an Olson record as extracted from ``extract_olson_values()``
and commits this record to the database.
"""
loc = Locations(description=record['location'])
db.session.add(loc)
prj = Projects(user_id=data_owner().id,
source_db='Olson APC',
source_id=record['source_id'],
description=record['description'],
warning=record['warning'],
source_ref=record['source_ref'],
dqf=record['dqf'],
location=loc)
db.session.add(prj)
misc = Misc(reo_check=record['reo_check'],
ts_check=record['ts_check'],
ut_boring=record['ut_boring'],
ut_sounding=record['ut_sounding'],
i_code=record['i_code'],
project=prj)
db.session.add(misc)
boring = Borings(predom_soil=record['predom_soil'],
layer_count=record['layer_count'],
ewt=record['ewt'],
sqf=record['sqf'],
project=prj)
db.session.add(boring)
cnt = len(record['layers']['index'])
for i in range(cnt):
layer = Layers(index=record['layers']['index'][i],
soil_type=record['layers']['soil_type'][i],
height=record['layers']['height'][i],
evso=record['layers']['evso'][i],
tuw=record['layers']['tuw'][i],
water_content=record['layers']['water_content'][i],
nval=record['layers']['nval'][i],
cnval=record['layers']['cnval'][i],
ssuu=record['layers']['ssuu'][i],
ssfv=record['layers']['ssfv'][i],
ssms=record['layers']['ssms'][i],
ssqt=record['layers']['ssqt'][i],
qc=record['layers']['qc'][i],
fsl=record['layers']['fsl'][i],
liquid_limit=record['layers']['liquid_limit'][i],
plasticity=record['layers']['plasticity'][i],
boring=boring)
db.session.add(layer)
pile = Piles(type=record['type'],
shape=record['shape'],
length=record['length'],
emb_length=record['emb_length'],
diameter=record['diameter'],
circumference=record['circumference'],
square_circ=record['square_circ'],
aeol=record['aeol'],
weight=record['weight'] * 1000,
dratio=record['dratio'],
tapered=record['tapered'],
vibro=record['vibro'],
cased=record['cased'],
predrilled=record['predrilled'],
relief_drilled=record['relief_drilled'],
jetted=record['jetted'],
project=prj)
db.session.add(pile)
load_test = LoadTests(
direction='Compression (Static)'
if record['direction'] == 'COMP' else 'Tension (Static)',
setup_time=record['setup_time'],
pile=pile)
db.session.add(load_test)
if qs:
i_slt = 1
for i in qs:
static_test = StaticTests(index=i_slt,
load=i[0],
displacement=i[1],
load_test=load_test)
db.session.add(static_test)
i_slt += 1
if record['half_inch_capacity']:
ic = InterpCapacities(load=record['half_inch_capacity'],
displacement=0.5,
type='Load @ 0.5 inches',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['davisson_capacity']:
ic = InterpCapacities(load=record['davisson_capacity'],
displacement=record['davisson_displacement'],
type='Standard Davisson',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['brown_capacity']:
ic = InterpCapacities(load=record['brown_capacity'],
type='Brown',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['max_load']:
ic = InterpCapacities(load=record['max_load'],
type='Maximum Load',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['max_displacement']:
ic = InterpCapacities(displacement=record['max_displacement'],
type='Maximum Displacement',
origin='source DB',
load_test=load_test)
db.session.add(ic)
db.session.commit()
def test_check_name(self): u = Projects(project_name='Marat') self.assertFalse(u.if_name_is_empty()) self.assertTrue(u.if_name_is_valid())
def projects():
# RETURNS ALL DATA
if request.method == 'GET':
return_projects = {}
all_projects = Projects.query.all()
for project in all_projects:
return_projects[project.id] = {
'name': project.name,
'time': project.time,
'tech': project.tech,
'link': project.link,
'link2': project.link2,
'date': project.date,
'prio': project.prio
}
return return_projects, 200
# CREATE NEW PROJECT
if request.method == 'POST':
args = {}
args['name'] = request.args.get('name')
args['date'] = request.args.get('date')
args['time'] = request.args.get('time') or None
args['tech'] = request.args.get('tech') or None
args['link'] = request.args.get('link') or None
args['link2'] = request.args.get('link2') or None
args['prio'] = request.args.get('prio') or None
if not args['name']:
return {'Error': 'Please provide a project name'}, 400
if not args['date']:
return {'Error': 'Please provide a project date'}, 400
new_project = Projects(name=args['name'],
time=args['time'],
tech=args['tech'],
link=args['link'],
link2=args['link2'],
date=args['date'],
prio=args['prio'])
db.session.add(new_project)
db.session.commit()
return {
'Success': 'Created the following item',
'item': {
'id': new_project.id,
'name': new_project.name,
'time': new_project.time,
'tech': new_project.tech,
'link': new_project.link,
'link2': new_project.link2,
'date': new_project.date,
'prio': new_project.prio
}
}, 200
# DELETE PROJECT
if request.method == 'DELETE':
to_del_id = request.args.get('id')
if not to_del_id:
return {'Error': 'Please provide a project id to delete'}, 400
project_to_delete = Projects.query.filter_by(id=to_del_id).first()
if not project_to_delete:
return {'Message': 'No project by that id!'}, 404
db.session.delete(project_to_delete)
db.session.commit()
return {
'Success': 'Deleted the following item',
'item': {
'id': project_to_delete.id,
'name': project_to_delete.name,
'time': project_to_delete.time,
'tech': project_to_delete.tech,
'link': project_to_delete.link,
'date': project_to_delete.date,
'prio': project_to_delete.prio
}
}, 200
# UPDATE / EDIT PROJECT
if request.method == 'PUT':
# get args
args = {}
args['id'] = request.args.get('id')
args['name'] = request.args.get('name') or None
args['time'] = request.args.get('time') or None
args['tech'] = request.args.get('tech') or None
args['link'] = request.args.get('link') or None
args['link2'] = request.args.get('link2') or None
args['date'] = request.args.get('date') or None
args['prio'] = request.args.get('prio') or None
if not args['id']:
return {'Error': 'Please provide a project id'}, 400
# get the project
project_to_modify = Projects.query.filter_by(id=args['id']).first()
if not project_to_modify:
return {'Message': 'No project by that id!'}, 404
# store old info for return message
item_old = {}
item_old['name'] = project_to_modify.name
item_old['time'] = project_to_modify.time
item_old['tech'] = project_to_modify.tech
item_old['link'] = project_to_modify.link
item_old['link2'] = project_to_modify.link2
item_old['date'] = project_to_modify.date
item_old['prio'] = project_to_modify.prio
# change project info, after ensuring data was passed
if args['name']:
project_to_modify.name = args['name']
if args['time']:
project_to_modify.time = args['time']
if args['tech']:
project_to_modify.tech = args['tech']
if args['link']:
project_to_modify.link = args['link']
if args['link2']:
project_to_modify.link2 = args['link2']
if args['date']:
project_to_modify.date = args['date']
if args['prio']:
project_to_modify.prio = args['prio']
# save to db
db.session.add(project_to_modify)
db.session.commit()
return {
'Success': 'Changed the following item',
'item-old': {
'id': project_to_modify.id,
'name': item_old['name'],
'time': item_old['time'],
'tech': item_old['tech'],
'link': item_old['link'],
'link2': item_old['link2'],
'date': item_old['date'],
'prio': item_old['prio']
},
'item-new': {
'id': project_to_modify.id,
'name': project_to_modify.name,
'time': project_to_modify.time,
'tech': project_to_modify.tech,
'link': project_to_modify.link,
'link2': project_to_modify.link2,
'date': project_to_modify.date,
'prio': project_to_modify.prio
}
}, 200