def generate_a_file(self, agency_code, file_path): """ Write file A to an appropriate CSV. """ self.job.filename = file_path self.job.original_filename = file_path.split('/')[-1] self.sess.commit() log_data = {'message': 'Starting file A generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'agency_code': agency_code, 'file_type': self.job.file_type.letter_name, 'start_date': self.job.start_date, 'end_date': self.job.end_date, 'filename': self.job.original_filename} logger.info(log_data) local_file = "".join([CONFIG_BROKER['d_file_storage_path'], self.job.original_filename]) headers = [key for key in fileA.mapping] # add 3 months to account for fiscal year period_date = self.job.end_date + relativedelta(months=3) log_data['message'] = 'Writing A file CSV: {}'.format(self.job.original_filename) logger.info(log_data) query_utils = {"agency_code": agency_code, "period": period_date.month, "year": period_date.year, "sess": self.sess} logger.debug({'query_utils': query_utils}) # Generate the file and put in S3 write_stream_query(self.sess, a_file_query(query_utils), local_file, self.job.filename, self.is_local, header=headers) log_data['message'] = 'Finished writing A file CSV: {}'.format(self.job.original_filename) logger.info(log_data)
def generate_a_file(self, agency_code, file_path): """ Write file A to an appropriate CSV. """ self.job.filename = file_path self.job.original_filename = file_path.split('/')[-1] self.sess.commit() log_data = {'message': 'Starting file A generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'agency_code': agency_code, 'file_type': self.job.file_type.letter_name, 'start_date': self.job.start_date, 'end_date': self.job.end_date, 'filename': self.job.original_filename} logger.info(log_data) local_file = "".join([CONFIG_BROKER['d_file_storage_path'], self.job.original_filename]) headers = [key for key in fileA.mapping] # add 3 months to account for fiscal year period_date = self.job.end_date + relativedelta(months=3) log_data['message'] = 'Writing A file CSV: {}'.format(self.job.original_filename) logger.info(log_data) query_utils = {"agency_code": agency_code, "period": period_date.month, "year": period_date.year, "sess": self.sess} logger.debug({'query_utils': query_utils}) # Generate the file and put in S3 write_stream_query(self.sess, a_file_query(query_utils), local_file, self.job.filename, self.is_local, header=headers) log_data['message'] = 'Finished writing A file CSV: {}'.format(self.job.original_filename) logger.info(log_data)
def generate_f_file(self): """ Write rows from fileF.generate_f_rows to an appropriate CSV. """ log_data = { 'message': 'Starting file F generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'submission_id': self.job.submission_id, 'file_type': 'sub_award' } logger.info(log_data) file_f_sql = fileE_F.generate_file_f_sql(self.job.submission_id) # writing locally first without uploading log_data['message'] = 'Writing F file CSV: {}'.format( self.job.original_filename) logger.info(log_data) # Generate the file and put in S3 write_stream_query(self.sess, file_f_sql, self.job.original_filename, self.job.filename, self.is_local, generate_headers=True, generate_string=False) log_data['message'] = 'Finished writing F file CSV: {}'.format( self.job.original_filename) logger.info(log_data)
def generate_e_file(self): """ Write file E to an appropriate CSV. """ log_data = { 'message': 'Starting file E generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'submission_id': self.job.submission_id, 'file_type': 'executive_compensation' } logger.info(log_data) file_e_sql = fileE_F.generate_file_e_sql(self.job.submission_id) log_data['message'] = 'Writing E file CSV: {}'.format( self.job.original_filename) logger.info(log_data) # Generate the file and put in S3 write_stream_query(self.sess, file_e_sql, self.job.original_filename, self.job.filename, self.is_local, generate_headers=True, generate_string=False) log_data['message'] = 'Finished writing E file CSV: {}'.format( self.job.original_filename) logger.info(log_data)
def generate_d_file(self, file_path): """ Write file D1 or D2 to an appropriate CSV. """ log_data = { 'message': 'Starting file {} generation'.format(self.file_type), 'message_type': 'ValidatorInfo', 'agency_code': self.file_generation.agency_code, 'agency_type': self.file_generation.agency_type, 'start_date': self.file_generation.start_date, 'end_date': self.file_generation.end_date, 'file_generation_id': self.file_generation.file_generation_id, 'file_type': self.file_type, 'file_format': self.file_generation.file_format, 'file_path': file_path } logger.info(log_data) original_filename = file_path.split('/')[-1] local_file = "".join([CONFIG_BROKER['d_file_storage_path'], original_filename]) # Prepare file data if self.file_type == 'D1': file_utils = fileD1 elif self.file_type == 'D2': file_utils = fileD2 else: raise ResponseException('Failed to generate_d_file with file_type:{} (must be D1 or D2).'.format( self.file_type)) headers = [key for key in file_utils.mapping] log_data['message'] = 'Writing {} file {}: {}'.format(self.file_type, self.file_generation.file_format.upper(), original_filename) logger.info(log_data) query_utils = { "sess": self.sess, "file_utils": file_utils, "agency_code": self.file_generation.agency_code, "agency_type": self.file_generation.agency_type, "start": self.file_generation.start_date, "end": self.file_generation.end_date} logger.debug({'query_utils': query_utils}) # Generate the file locally, then place in S3 write_stream_query(self.sess, d_file_query(query_utils), local_file, file_path, self.is_local, header=headers, file_format=self.file_generation.file_format) log_data['message'] = 'Finished writing {} file {}: {}'.format(self.file_type, self.file_generation.file_format.upper(), original_filename) logger.info(log_data) self.file_generation.file_path = file_path self.sess.commit() for job in self.sess.query(Job).filter_by(file_generation_id=self.file_generation.file_generation_id).all(): copy_file_generation_to_job(job, self.file_generation, self.is_local)
def generate_d_file(self, file_path): """ Write file D1 or D2 to an appropriate CSV. """ log_data = { 'message': 'Starting file {} generation'.format(self.file_type), 'message_type': 'ValidatorInfo', 'agency_code': self.file_generation.agency_code, 'agency_type': self.file_generation.agency_type, 'start_date': self.file_generation.start_date, 'end_date': self.file_generation.end_date, 'file_generation_id': self.file_generation.file_generation_id, 'file_type': self.file_type, 'file_path': file_path } logger.info(log_data) original_filename = file_path.split('/')[-1] local_file = "".join([CONFIG_BROKER['d_file_storage_path'], original_filename]) # Prepare file data if self.file_type == 'D1': file_utils = fileD1 elif self.file_type == 'D2': file_utils = fileD2 else: raise ResponseException('Failed to generate_d_file with file_type:{} (must be D1 or D2).'.format( self.file_type)) headers = [key for key in file_utils.mapping] log_data['message'] = 'Writing {} file CSV: {}'.format(self.file_type, original_filename) logger.info(log_data) query_utils = { "sess": self.sess, "file_utils": file_utils, "agency_code": self.file_generation.agency_code, "agency_type": self.file_generation.agency_type, "start": self.file_generation.start_date, "end": self.file_generation.end_date} logger.debug({'query_utils': query_utils}) # Generate the file locally, then place in S3 write_stream_query(self.sess, d_file_query(query_utils), local_file, file_path, self.is_local, header=headers) log_data['message'] = 'Finished writing {} file CSV: {}'.format(self.file_type, original_filename) logger.info(log_data) self.file_generation.file_path = file_path self.sess.commit() for job in self.sess.query(Job).filter_by(file_generation_id=self.file_generation.file_generation_id).all(): copy_file_generation_to_job(job, self.file_generation, self.is_local)
def generate_f_file(self): """ Write rows from fileF.generate_f_rows to an appropriate CSV. """ log_data = { 'message': 'Starting file F generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'submission_id': self.job.submission_id, 'file_type': 'sub_award' } logger.info(log_data) f_file_contracts_query, f_file_grants_query = fileF.generate_f_file_queries( self.job.submission_id) # writing locally first without uploading log_data['message'] = 'Writing F file contracts to CSV: {}'.format( self.job.original_filename) logger.info(log_data) local_f_file = self.job.filename if self.is_local else self.job.original_filename write_query_to_file(self.sess, f_file_contracts_query, local_f_file, generate_headers=True, generate_string=False) # writing locally again but then uploading log_data['message'] = 'Writing F file grants to CSV: {}'.format( self.job.original_filename) logger.info(log_data) write_stream_query(self.sess, f_file_grants_query, self.job.original_filename, self.job.filename, self.is_local, generate_headers=False, generate_string=False) log_data['message'] = 'Finished writing F file CSV: {}'.format( self.job.original_filename) logger.info(log_data)
def generate_f_file(self): """ Write rows from fileF.generate_f_rows to an appropriate CSV. """ log_data = {'message': 'Starting file F generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'submission_id': self.job.submission_id, 'file_type': 'sub_award'} logger.info(log_data) f_file_contracts_query, f_file_grants_query = fileF.generate_f_file_queries(self.job.submission_id) # writing locally first without uploading log_data['message'] = 'Writing F file contracts to CSV: {}'.format(self.job.original_filename) logger.info(log_data) local_f_file = self.job.filename if self.is_local else self.job.original_filename write_query_to_file(self.sess, f_file_contracts_query, local_f_file, generate_headers=True, generate_string=False) # writing locally again but then uploading log_data['message'] = 'Writing F file grants to CSV: {}'.format(self.job.original_filename) logger.info(log_data) write_stream_query(self.sess, f_file_grants_query, self.job.original_filename, self.job.filename, self.is_local, generate_headers=False, generate_string=False) log_data['message'] = 'Finished writing F file CSV: {}'.format(self.job.original_filename) logger.info(log_data)
def generate_e_file(self): """ Write file E to an appropriate CSV. """ log_data = {'message': 'Starting file E generation', 'message_type': 'ValidatorInfo', 'job_id': self.job.job_id, 'submission_id': self.job.submission_id, 'file_type': 'executive_compensation'} logger.info(log_data) # Get the raw SQL to work with sql_dir = os.path.join(CONFIG_BROKER["path"], "dataactcore", "scripts", "raw_sql") with open(os.path.join(sql_dir, 'fileE.sql'), 'r') as file_e: file_e_sql = file_e.read() # Remove newlines (write_stream_query doesn't like them) and add the submission ID to the query file_e_sql = file_e_sql.replace('\n', ' ') file_e_sql = file_e_sql.format(self.job.submission_id) log_data['message'] = 'Writing E file CSV: {}'.format(self.job.original_filename) logger.info(log_data) # Generate the file and put in S3 write_stream_query(self.sess, file_e_sql, self.job.original_filename, self.job.filename, self.is_local, generate_headers=True, generate_string=False) log_data['message'] = 'Finished writing E file CSV: {}'.format(self.job.original_filename) logger.info(log_data)
submission = sess.query(Submission).filter_by( submission_id=submission_id).one() filename = 'SubID-{}_comments_{}.csv'.format( submission_id, filename_fyp_sub_format(submission)) local_file = "".join([CONFIG_BROKER['broker_files'], filename]) file_path = local_file if is_local else '{}/{}'.format( str(submission_id), filename) unpublished_query = sess.query(FileType.name, Comment.comment).\ join(FileType, Comment.file_type_id == FileType.file_type_id).\ filter(Comment.submission_id == submission_id) # Generate the file locally, then place in S3 write_stream_query(sess, unpublished_query, local_file, file_path, is_local, header=headers) logger.info('Finished generating unpublished comments files') # Published comments files logger.info('Copying published comments files') commented_pub_submissions = sess.query( PublishedComment.submission_id).distinct() for published_submission in commented_pub_submissions: submission_id = published_submission.submission_id submission = sess.query(Submission).filter_by( submission_id=submission_id).one() filename = 'SubID-{}_comments_{}.csv'.format(
headers = ['File', 'Comment'] commented_submissions = sess.query(Comment.submission_id).distinct() for submission in commented_submissions: # Preparing for the comments files filename = 'submission_{}_comments.csv'.format(submission.submission_id) local_file = "".join([CONFIG_BROKER['broker_files'], filename]) file_path = local_file if is_local else '{}/{}'.format(str(submission.submission_id), filename) uncertified_query = sess.query(FileType.name, Comment.comment).\ join(FileType, Comment.file_type_id == FileType.file_type_id).\ filter(Comment.submission_id == submission.submission_id) # Generate the file locally, then place in S3 write_stream_query(sess, uncertified_query, local_file, file_path, is_local, header=headers) logger.info('Finished generating uncertified comments files') # Certified comments files logger.info('Copying certified comments files') commented_cert_submissions = sess.query(CertifiedComment.submission_id).distinct() for certified_submission in commented_cert_submissions: submission_id = certified_submission.submission_id submission = sess.query(Submission).filter_by(submission_id=submission_id).one() filename = 'submission_{}_comments.csv'.format(str(submission_id)) # See if we already have this certified file in the list existing_cert_history = sess.query(CertifiedFilesHistory).\ filter(CertifiedFilesHistory.submission_id == submission_id,