def update_conductor():
turn_off_sqlalchemy_events()
from purchasing.data.contract_stages import ContractStage, ContractStageActionItem
try:
for contract_stage in ContractStage.query.all():
received_rewind = False
# actions
actions = contract_stage.contract_stage_actions.all()
exits = [i for i in actions if i.action_type == 'exited']
enters = [i for i in actions if i.action_type == 'entered']
for action in actions:
action.taken_by = contract_stage.contract.assigned_to
if action.action_type == 'entered':
try:
action.action_detail = {}
db.session.commit()
action_detail = {
'timestamp':
contract_stage.entered.strftime(
'%Y-%m-%dT%H:%M:%S'),
'date':
contract_stage.entered.strftime('%Y-%m-%d'),
'type':
'entered',
'label':
'Started work',
'stage_name':
contract_stage.stage.name
}
action.update(action_detail=action_detail)
action.update(taken_at=str(
contract_stage.entered.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == 'exited':
try:
action.action_detail = {}
db.session.commit()
action_detail = {
'timestamp':
contract_stage.exited.strftime(
'%Y-%m-%dT%H:%M:%S'),
'date':
contract_stage.exited.strftime('%Y-%m-%d'),
'type':
'exited',
'label':
'Completed work',
'stage_name':
contract_stage.stage.name
}
action.update(action_detail=action_detail)
action.update(taken_at=str(
contract_stage.exited.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == 'reversion':
action.delete()
def do_work(ignore_time=False):
from purchasing.jobs.job_base import JobStatus
turn_off_sqlalchemy_events()
jobs = JobStatus.query.filter(JobStatus.status == 'new').all()
for job in jobs:
task = getattr(nightly_jobs, job.name)(time_override=ignore_time)
task.run_job(job)
turn_on_sqlalchemy_events()
refresh_search_view()
def scrape(_all):
print 'Disabling triggers...'
turn_off_sqlalchemy_events()
from purchasing.data.importer.scrape_county import main
print 'Scraping County'
main(_all)
print 'Scraping Finished'
print 'Enabling triggers...'
turn_on_sqlalchemy_events()
return
def delete_contracts():
if prompt_bool("Are you sure you want to lose all contracts & companies?"):
print 'Disabling triggers...'
turn_off_sqlalchemy_events()
print 'Deleting!'
from purchasing.data.contracts import ContractBase, Company
ContractBase.query.delete()
Company.query.delete()
db.session.commit()
print 'Enabling triggers...'
turn_on_sqlalchemy_events()
return
def import_old_contracts(filepath):
"""
Takes a csv of old contracts and imports them into the DB
"""
print "Disabling triggers..."
turn_off_sqlalchemy_events()
from purchasing.data.importer.old_contracts import main
print "Importing data from {filepath}\n".format(filepath=filepath)
main(filepath)
print "Import finished!"
print "Enabling triggers..."
turn_on_sqlalchemy_events()
print "Refreshing search_view..."
print "Done!"
return
def import_costars(user=None, secret=None, bucket=None, directory=None):
"""
Takes a directory which contains a number of csv files with the
costars data, and then important them into the DB
"""
print "Disabling triggers..."
turn_off_sqlalchemy_events()
from purchasing.data.importer.costars import main
for file in os.listdir(directory):
if file.endswith(".csv"):
print "Importing data from {file}".format(file=file)
main(os.path.join(directory, file), file, user, secret, bucket)
print "Import finished!"
print "Enabling triggers..."
turn_on_sqlalchemy_events()
return
def update_conductor():
turn_off_sqlalchemy_events()
from purchasing.data.contract_stages import ContractStage, ContractStageActionItem
try:
for contract_stage in ContractStage.query.all():
received_rewind = False
# actions
actions = contract_stage.contract_stage_actions.all()
exits = [i for i in actions if i.action_type == "exited"]
enters = [i for i in actions if i.action_type == "entered"]
for action in actions:
action.taken_by = contract_stage.contract.assigned_to
if action.action_type == "entered":
try:
action.action_detail = {}
db.session.commit()
action_detail = {
"timestamp": contract_stage.entered.strftime("%Y-%m-%dT%H:%M:%S"),
"date": contract_stage.entered.strftime("%Y-%m-%d"),
"type": "entered",
"label": "Started work",
"stage_name": contract_stage.stage.name,
}
action.update(action_detail=action_detail)
action.update(taken_at=str(contract_stage.entered.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == "exited":
try:
action.action_detail = {}
db.session.commit()
action_detail = {
"timestamp": contract_stage.exited.strftime("%Y-%m-%dT%H:%M:%S"),
"date": contract_stage.exited.strftime("%Y-%m-%d"),
"type": "exited",
"label": "Completed work",
"stage_name": contract_stage.stage.name,
}
action.update(action_detail=action_detail)
action.update(taken_at=str(contract_stage.exited.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == "reversion":
action.delete()
def update_conductor():
turn_off_sqlalchemy_events()
from purchasing.data.contract_stages import ContractStage, ContractStageActionItem
try:
for contract_stage in ContractStage.query.all():
received_rewind = False
# actions
actions = contract_stage.contract_stage_actions.all()
exits = [i for i in actions if i.action_type == 'exited']
enters = [i for i in actions if i.action_type == 'entered']
for action in actions:
action.taken_by = contract_stage.contract.assigned_to
if action.action_type == 'entered':
try:
action.action_detail = {}
db.session.commit()
action_detail = {
'timestamp': contract_stage.entered.strftime('%Y-%m-%dT%H:%M:%S'),
'date': contract_stage.entered.strftime('%Y-%m-%d'),
'type': 'entered', 'label': 'Started work',
'stage_name': contract_stage.stage.name
}
action.update(action_detail=action_detail)
action.update(taken_at=str(contract_stage.entered.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == 'exited':
try:
action.action_detail = {}
db.session.commit()
action_detail = {
'timestamp': contract_stage.exited.strftime('%Y-%m-%dT%H:%M:%S'),
'date': contract_stage.exited.strftime('%Y-%m-%d'),
'type': 'exited', 'label': 'Completed work',
'stage_name': contract_stage.stage.name
}
action.update(action_detail=action_detail)
action.update(taken_at=str(contract_stage.exited.replace(hour=0, minute=0)))
except Exception, e:
action.delete()
elif action.action_type == 'reversion':
action.delete()
def fix_conductor_start_dates():
turn_off_sqlalchemy_events()
from purchasing.data import contracts as c, contract_stages as cs, flows as f
try:
conductor_contracts = c.ContractBase.query.join(c.ContractType).filter(
c.ContractType.managed_by_conductor == True).all()
flows = f.Flow.query.all()
for contract in conductor_contracts:
for flow in flows:
contract_stages = cs.ContractStage.get_multiple(
contract.id, flow.id, flow.stage_order)
for contract_stage in contract_stages:
if contract_stage.entered and contract_stage.exited:
actions = contract_stage._fix_start_time()
for action in actions:
db.session.add(action)
db.session.commit()
finally:
turn_on_sqlalchemy_events()
def upload_costars_contract(_file):
'''Upload a COSTARS pdf document to S3
Arguments:
_file: A werkzeug `FileStorage`_ object
Returns:
A two-tuple of (the name of the uploaded file, the path/url to the file)
'''
filename = secure_filename(_file.filename)
if current_app.config['UPLOAD_S3']:
try:
turn_off_sqlalchemy_events()
conn, bucket = connect_to_s3(
current_app.config['AWS_ACCESS_KEY_ID'],
current_app.config['AWS_SECRET_ACCESS_KEY'], 'costars')
file_href = upload_file(filename,
bucket,
input_file=_file,
prefix='/',
from_file=True)
return filename, file_href
except Exception:
raise
finally:
turn_on_sqlalchemy_events()
refresh_search_view()
else:
try:
os.mkdir(current_app.config['UPLOAD_DESTINATION'])
except:
pass
filepath = os.path.join(current_app.config['UPLOAD_DESTINATION'],
filename)
_file.save(filepath)
return filename, filepath
def fix_conductor_start_dates():
turn_off_sqlalchemy_events()
from purchasing.data import contracts as c, contract_stages as cs, flows as f
try:
conductor_contracts = c.ContractBase.query.join(c.ContractType).filter(
c.ContractType.managed_by_conductor == True
).all()
flows = f.Flow.query.all()
for contract in conductor_contracts:
for flow in flows:
contract_stages = cs.ContractStage.get_multiple(
contract.id, flow.id, flow.stage_order
)
for contract_stage in contract_stages:
if contract_stage.entered and contract_stage.exited:
actions = contract_stage._fix_start_time()
for action in actions:
db.session.add(action)
db.session.commit()
finally:
turn_on_sqlalchemy_events()
def upload_costars_contract(_file):
'''Upload a COSTARS pdf document to S3
Arguments:
_file: A werkzeug `FileStorage`_ object
Returns:
A two-tuple of (the name of the uploaded file, the path/url to the file)
'''
filename = secure_filename(_file.filename)
if current_app.config['UPLOAD_S3']:
try:
turn_off_sqlalchemy_events()
conn, bucket = connect_to_s3(
current_app.config['AWS_ACCESS_KEY_ID'],
current_app.config['AWS_SECRET_ACCESS_KEY'],
'costars'
)
file_href = upload_file(filename, bucket, input_file=_file, prefix='/', from_file=True)
return filename, file_href
except Exception:
raise
finally:
turn_on_sqlalchemy_events()
refresh_search_view()
else:
try:
os.mkdir(current_app.config['UPLOAD_DESTINATION'])
except:
pass
filepath = os.path.join(current_app.config['UPLOAD_DESTINATION'], filename)
_file.save(filepath)
return filename, filepath
def main(filetarget, filename, access_key, access_secret, bucket):
if not re.match(VALID_FILENAMES, filename):
raise IOError('Not a valid filename. Filenames must have COSTARS with number separated by a dash (Ex. "COSTARS-3.csv").')
data = extract(filetarget)
s3_files = None
# connect to s3 and get contents of bucket
bucket = connect_to_s3_bucket(access_key, access_secret, bucket)
if bucket:
s3_files = bucket.list()
try:
for row in data:
try:
turn_off_sqlalchemy_events()
except InvalidRequestError:
pass
company, new_company = get_or_create(
db.session, Company,
company_name=convert_empty_to_none(row.get('Company'))
)
company_contact = determine_company_contact(row)
if company_contact:
# create the new company contact
company_contact, new_contact = get_or_create(
db.session, CompanyContact,
company_id=company.id,
**company_contact
)
if new_contact:
db.session.add(company_contact)
db.session.commit()
costars_awardee = convert_empty_to_none(row.get('Company'))
try:
expiration = datetime.datetime.strptime(row.get('Expiration'), '%m/%d/%y')
except ValueError:
expiration = None
costars_type, _ = get_or_create(
db.session, ContractType,
name='COSTARS'
)
# create or select the contract object
contract, new_contract = get_or_create(
db.session, ContractBase,
contract_type=costars_type,
expiration_date=expiration,
financial_id=convert_empty_to_none(row.get('CONTROLLER')),
description='{costars} - {company}'.format(
costars=filename.replace('-', ' ').rstrip('.csv').upper(),
company=costars_awardee
)
)
# connect to s3
if s3_files:
# all files start with 'costars-{number}-', which we should be
# able to get from our filename
max_ratio = (None, 0)
startswith = filename.strip('.csv').lower()
for _file in s3_files:
_filename = _file.name.encode('utf-8').strip('.pdf').rstrip('.')
costars_awardee = costars_awardee.rstrip('.')
# because the file start patterns are consistent, strip
# out the costars-{number}-
_file_awardee = _filename.split('-')[2]
# check for absolute matches
match_ratio = SM(lambda x: bool(re.match(JUNK_STRING, x)), costars_awardee, _file_awardee).ratio()
if match_ratio == 1:
# this is an absolute match, insert it into the db and break
max_ratio = (_file.generate_url(expires_in=0, query_auth=False), match_ratio)
if _filename.startswith(startswith):
break
else:
continue
elif match_ratio > max_ratio[1]:
# this is the best match we have so far
max_ratio = (_file.generate_url(expires_in=0, query_auth=False), match_ratio)
continue
# use the best match that we have
print contract.description, max_ratio
if max_ratio[1] > 0.7:
contract.contract_href = max_ratio[0]
for k, v in row.iteritems():
if k in CONSTANT_FIELDS:
continue
# insert a new contract property with where the company is located
elif k == 'County Located':
if row.get('County Located') != '':
county_located, new_county_located = get_or_create(
db.session, ContractProperty,
contract_id=contract.id,
key='Located in',
value=convert_empty_to_none(
'{county} County'.format(county=row.get('County Located'))
)
)
else:
continue
if new_county_located:
db.session.add(county_located)
# insert a new property with the listed manufacturers
elif k == 'Manufacturers':
if convert_empty_to_none(row.get('Manufacturers')):
manufacturer, new_manufacturer = get_or_create(
db.session, ContractProperty,
contract_id=contract.id,
key='List of manufacturers',
value=convert_empty_to_none(row.get('Manufacturers'))
)
if new_manufacturer:
db.session.add(manufacturer)
# we are treating everything else like a line item,
# so upload all of those pieces
else:
if convert_to_bool(convert_empty_to_none(v)):
line_item, new_line_item = get_or_create(
db.session, LineItem,
contract_id=contract.id,
description=convert_empty_to_none(k)
)
else:
continue
if new_line_item:
db.session.add(line_item)
contract.companies.append(company)
db.session.commit()
except Exception:
db.session.rollback()
raise
finally:
turn_on_sqlalchemy_events()
def main(filetarget, filename, access_key, access_secret, bucket):
if not re.match(VALID_FILENAMES, filename):
raise IOError(
'Not a valid filename. Filenames must have COSTARS with number separated by a dash (Ex. "COSTARS-3.csv").'
)
data = extract(filetarget)
s3_files = None
# connect to s3 and get contents of bucket
bucket = connect_to_s3_bucket(access_key, access_secret, bucket)
if bucket:
s3_files = bucket.list()
try:
for row in data:
try:
turn_off_sqlalchemy_events()
except InvalidRequestError:
pass
company, new_company = get_or_create(
db.session,
Company,
company_name=convert_empty_to_none(row.get('Company')))
company_contact = determine_company_contact(row)
if company_contact:
# create the new company contact
company_contact, new_contact = get_or_create(
db.session,
CompanyContact,
company_id=company.id,
**company_contact)
if new_contact:
db.session.add(company_contact)
db.session.commit()
costars_awardee = convert_empty_to_none(row.get('Company'))
try:
expiration = datetime.datetime.strptime(
row.get('Expiration'), '%m/%d/%y')
except ValueError:
expiration = None
costars_type, _ = get_or_create(db.session,
ContractType,
name='COSTARS')
# create or select the contract object
contract, new_contract = get_or_create(
db.session,
ContractBase,
contract_type=costars_type,
expiration_date=expiration,
financial_id=convert_empty_to_none(row.get('CONTROLLER')),
description='{costars} - {company}'.format(
costars=filename.replace('-', ' ').rstrip('.csv').upper(),
company=costars_awardee))
# connect to s3
if s3_files:
# all files start with 'costars-{number}-', which we should be
# able to get from our filename
max_ratio = (None, 0)
startswith = filename.strip('.csv').lower()
for _file in s3_files:
_filename = _file.name.encode('utf-8').strip(
'.pdf').rstrip('.')
costars_awardee = costars_awardee.rstrip('.')
# because the file start patterns are consistent, strip
# out the costars-{number}-
_file_awardee = _filename.split('-')[2]
# check for absolute matches
match_ratio = SM(lambda x: bool(re.match(JUNK_STRING, x)),
costars_awardee, _file_awardee).ratio()
if match_ratio == 1:
# this is an absolute match, insert it into the db and break
max_ratio = (_file.generate_url(expires_in=0,
query_auth=False),
match_ratio)
if _filename.startswith(startswith):
break
else:
continue
elif match_ratio > max_ratio[1]:
# this is the best match we have so far
max_ratio = (_file.generate_url(expires_in=0,
query_auth=False),
match_ratio)
continue
# use the best match that we have
print contract.description, max_ratio
if max_ratio[1] > 0.7:
contract.contract_href = max_ratio[0]
for k, v in row.iteritems():
if k in CONSTANT_FIELDS:
continue
# insert a new contract property with where the company is located
elif k == 'County Located':
if row.get('County Located') != '':
county_located, new_county_located = get_or_create(
db.session,
ContractProperty,
contract_id=contract.id,
key='Located in',
value=convert_empty_to_none(
'{county} County'.format(
county=row.get('County Located'))))
else:
continue
if new_county_located:
db.session.add(county_located)
# insert a new property with the listed manufacturers
elif k == 'Manufacturers':
if convert_empty_to_none(row.get('Manufacturers')):
manufacturer, new_manufacturer = get_or_create(
db.session,
ContractProperty,
contract_id=contract.id,
key='List of manufacturers',
value=convert_empty_to_none(
row.get('Manufacturers')))
if new_manufacturer:
db.session.add(manufacturer)
# we are treating everything else like a line item,
# so upload all of those pieces
else:
if convert_to_bool(convert_empty_to_none(v)):
line_item, new_line_item = get_or_create(
db.session,
LineItem,
contract_id=contract.id,
description=convert_empty_to_none(k))
else:
continue
if new_line_item:
db.session.add(line_item)
contract.companies.append(company)
db.session.commit()
except Exception:
db.session.rollback()
raise
finally:
turn_on_sqlalchemy_events()
def main(file_target='./files/2015-10-27-state-contracts.csv'):
data = extract(file_target)
try:
for row in data:
try:
turn_off_sqlalchemy_events()
except InvalidRequestError:
pass
# create or select the company
try:
company, new_company = get_or_create(
db.session, Company,
company_name=convert_empty_to_none(row.get('COMPANY'))
)
except IntegrityError:
db.session.rollback()
company = None
company_contact = determine_company_contact(row)
if company_contact and company:
# create the new company contact
company_contact, new_contact = get_or_create(
db.session, CompanyContact,
company_id=company.id,
**company_contact
)
if new_contact:
db.session.add(company_contact)
db.session.commit()
try:
expiration = datetime.datetime.strptime(row.get('EXPIRATION'), '%m/%d/%Y')
except ValueError:
expiration = None
try:
_financial_id = convert_empty_to_none(row.get('CONTROLLER'))
except ValueError:
_financial_id = None
contract_type, _ = get_or_create(
db.session, ContractType,
name=convert_empty_to_none(row.get('TYPE OF CONTRACT'))
)
# create or select the contract object
contract, new_contract = get_or_create(
db.session, ContractBase,
contract_type=contract_type,
expiration_date=expiration,
financial_id=_financial_id,
description=convert_empty_to_none(row.get('SERVICE')),
contract_href=BASE_CONTRACT_URL.format(
number=convert_empty_to_none(row.get('CONTRACT')),
type='Overview'
if 'IT SERVICES ITQ' in convert_empty_to_none(row.get('SERVICE')).upper()
else 'ContractFile'
)
)
parent_number, new_parent_number = get_or_create(
db.session, ContractProperty, commit=False,
contract_id=contract.id,
key='Parent Number',
value=convert_empty_to_none(row.get('PARENT'))
)
if new_parent_number:
db.session.add(parent_number)
contract_number, new_contract_number = get_or_create(
db.session, ContractProperty, commit=False,
contract_id=contract.id,
key='Contract Number',
value=convert_empty_to_none(row.get('CONTRACT'))
)
if new_contract_number:
db.session.add(contract_number)
if company:
contract.companies.append(company)
db.session.commit()
except Exception:
db.session.rollback()
raise
finally:
turn_on_sqlalchemy_events()
def main(file_target='./files/2015-10-27-state-contracts.csv'):
data = extract(file_target)
try:
for row in data:
try:
turn_off_sqlalchemy_events()
except InvalidRequestError:
pass
# create or select the company
try:
company, new_company = get_or_create(
db.session,
Company,
company_name=convert_empty_to_none(row.get('COMPANY')))
except IntegrityError:
db.session.rollback()
company = None
company_contact = determine_company_contact(row)
if company_contact and company:
# create the new company contact
company_contact, new_contact = get_or_create(
db.session,
CompanyContact,
company_id=company.id,
**company_contact)
if new_contact:
db.session.add(company_contact)
db.session.commit()
try:
expiration = datetime.datetime.strptime(
row.get('EXPIRATION'), '%m/%d/%Y')
except ValueError:
expiration = None
try:
_financial_id = convert_empty_to_none(row.get('CONTROLLER'))
except ValueError:
_financial_id = None
contract_type, _ = get_or_create(db.session,
ContractType,
name=convert_empty_to_none(
row.get('TYPE OF CONTRACT')))
# create or select the contract object
contract, new_contract = get_or_create(
db.session,
ContractBase,
contract_type=contract_type,
expiration_date=expiration,
financial_id=_financial_id,
description=convert_empty_to_none(row.get('SERVICE')),
contract_href=BASE_CONTRACT_URL.format(
number=convert_empty_to_none(row.get('CONTRACT')),
type='Overview'
if 'IT SERVICES ITQ' in convert_empty_to_none(
row.get('SERVICE')).upper() else 'ContractFile'))
parent_number, new_parent_number = get_or_create(
db.session,
ContractProperty,
contract_id=contract.id,
key='Parent Number',
value=convert_empty_to_none(row.get('PARENT')))
if new_parent_number:
db.session.add(parent_number)
contract_number, new_contract_number = get_or_create(
db.session,
ContractProperty,
contract_id=contract.id,
key='Contract Number',
value=convert_empty_to_none(row.get('CONTRACT')))
if new_contract_number:
db.session.add(contract_number)
if company:
contract.companies.append(company)
db.session.commit()
except Exception:
db.session.rollback()
raise
finally:
turn_on_sqlalchemy_events()
