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 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()
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 } ) ) if contract_stage.entered and not contract_stage.exited and not received_rewind: contract_stage.contract.current_stage_id = contract_stage.stage.id db.session.commit() for enter in enters[1:]: enter.delete() for exit in exits[1:]: exit.delete() db.session.commit() except Exception, e: print 'Problem! Message: {}'.format(e) db.session.rollback() turn_on_sqlalchemy_events() manager.add_command('server', Server(port=os.environ.get('PORT', 9000))) manager.add_command('shell', Shell(make_context=_make_context)) manager.add_command('db', MigrateCommand) manager.add_command('assets', ManageAssets) if __name__ == '__main__': manager.run()
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()