def dbdata(connStr): utility.write_to_file( config.ConfigManager().LogFile, 'a', 'req_desc_file_extract running' + ' ' + str(datetime.datetime.now())) cursor = dbmanager.cursor_odbc_connection(connStr) query = custom.fetch_query(config.ConfigManager().STReqDocQueryId) configdocs = custom.retrieve_data_from_DB( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection) query = query.replace('##jobDocumentID##', str(configdocs[0]['STreqDocpk'])) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] count = 0 data_dict = {} connection = dbmanager.mongoDB_connection( int(config.ConfigManager().MongoDBPort)) for row in db_data: try: data_dict = dict(utility.zip_list(column_headers, row)) reqdescfilepath = config.ConfigManager().ReqDocDirectory + '/' + '_' + \ str(data_dict['requirementID']) + '_' + \ str(data_dict['documentName']) filewrite = open(reqdescfilepath, 'wb') filewrite.write(data_dict['jobDocumentFile']) count += 1 except BaseException as ex: exception_message = '\n' + 'Exception:' + \ str(datetime.datetime.now()) + '\n' exception_message += 'File: ' + '\n' exception_message += '\n' + str(ex) + '\n' exception_message += '-' * 100 utility.write_to_file(config.ConfigManager().LogFile, 'a', exception_message) if 'jobDocumentID' in data_dict: primary_key = data_dict['jobDocumentID'] UpdateTemplateSet = utility.clean_dict() UpdateTemplateWhere = utility.clean_dict() UpdateTemplateSet['STreqDocpk'] = primary_key reqDocUpdateDelta = int(primary_key) - \ int(configdocs[0]['STreqDocpk']) utility.write_to_file( config.ConfigManager().LogFile, 'a', 'Req docs updated - ' + str(count) + ' ' + str(datetime.datetime.now())) UpdateTemplateWhere['_id'] = configdocs[0]['_id'] DBSet = utility.clean_dict() DBSet['$set'] = UpdateTemplateSet custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection, UpdateTemplateWhere, DBSet, connection)
def create_sql_data_list(query, connStr, keyName): cursor = dbmanager.cursor_odbc_connection(connStr) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] valueList = [] valueList = [((dict(utility.zip_list(column_headers, row)))[keyName]).lower() for row in db_data] return valueList
def requirement_update(): (dictionaries.DBWhereCondition1)['documentType'] = 'candidate details' (dictionaries.DBWhereCondition1)['dataSource'] = 'Smart Track' docs = custom.retrieve_rowdata_from_DB_notimeout( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().DataCollectionDBCollection, dictionaries.DBWhereCondition1) recordnumber = 0 for doc in docs: recordnumber += 1 requirementIDList = [] query = custom.fetch_query( config.ConfigManager().STCandidateSubmissionsQueryId) cursor = dbmanager.cursor_odbc_connection( config.ConfigManager().STConnStr) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] for row in db_data: try: data_dict = dict(utility.zip_list(column_headers, row)) if (data_dict['CandidateID'] == doc['candidateid']): requirementIDList.append(data_dict['requirementID']) except BaseException as ex: exception_message = '\n' + 'Exception:' + \ str(datetime.datetime.now()) + '\n' exception_message += 'File: ' + '\n' exception_message += '\n' + str(ex) + '\n' exception_message += '-' * 100 utility.write_to_file(config.ConfigManager().LogFile, 'a', exception_message) dictionaries.UpdateTemplateSet['requirementIDList'] = requirementIDList dictionaries.UpdateTemplateWhere['candidateid'] = doc['candidateid'] dictionaries.DBSet['$set'] = dictionaries.UpdateTemplateSet print(recordnumber, doc['candidateid'], requirementIDList) custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().DataCollectionDBCollection, dictionaries.UpdateTemplateWhere, dictionaries.DBSet)
def stcandidate_update(): utility.write_to_file( config.ConfigManager().LogFile, 'a', 'in stcandidates update currency Code running.!' + ' ' + str(datetime.datetime.now())) recordnumber = 0 query = custom.fetch_query( config.ConfigManager().STCandidateCurrencyQueryId) print(query) query = custom.query_variable_replace( query, config.ConfigManager().STCandidateCurrencyDetails, config.ConfigManager().ST) print(query) cursor = dbmanager.cursor_odbc_connection(config.ConfigManager().STConnStr) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] connection = dbmanager.mongoDB_connection( int(config.ConfigManager().MongoDBPort)) data_dict1 = {} req_list = [] candidateDatesList = [] for row1 in db_data: try: print(data_dict1) strtimestamp = str(datetime.datetime.now()) recordnumber += 1 print(recordnumber) data_dict1 = dict(utility.zip_list(column_headers, row1)) STCandidateCollection.update( { "$and": [{ "candidateid": data_dict1['candidateid'] }, { "requirementRateStatusList": { "$elemMatch": { "requirementId": data_dict1['requirementid'] } } }] }, { "$set": { "requirementRateStatusList.$.currencyCode": data_dict1['currencycode'], "requirementRateStatusList.$.SupplierCurrencyCode": data_dict1['SupplierCurrencyCode'], "requirementRateStatusList.$.supplierRegBillRateEX": str(data_dict1['supplierRegBillRateEX']) } }) candidateDatesList.append(data_dict1['dateCreated']) except BaseException as ex: print(ex) utility.log_exception_file(ex, config.ConfigManager().LogFile) if 'dateCreated' in data_dict1: maxCandDate = max(candidateDatesList) UpdateTemplateWhere = utility.clean_dict() UpdateTemplateSet = utility.clean_dict() UpdateTemplateWhere['_id'] = configdocs[0]['_id'] print(maxCandDate) print(str(maxCandDate)) UpdateTemplateSet['STCandidateCurrencyCodeLastDate'] = str(maxCandDate) DBSet = utility.clean_dict() DBSet['$set'] = UpdateTemplateSet custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection, UpdateTemplateWhere, DBSet, connection)
def dbdata(connStr): utility.write_to_file( config.ConfigManager().LogFile, 'a', 'resume_extract running' + ' ' + str(datetime.datetime.now())) st_db_name_list = utility.find_string_inbetween( config.ConfigManager().STConnStr, "DATABASE=", ";UID") cursor = dbmanager.cursor_odbc_connection(connStr) query = custom.fetch_query( config.ConfigManager().STCandidateResumesQueryId) configdocs = custom.retrieve_data_from_DB( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection) query = query.replace('##candidateResumeID##', str(configdocs[0]['STcandidateResumepk'])).replace( '##STDB##', st_db_name_list[0]) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] count = 0 data_dict = {} connection = dbmanager.mongoDB_connection( int(config.ConfigManager().MongoDBPort)) for row in db_data: try: data_dict = dict(utility.zip_list(column_headers, row)) resumefilepath = config.ConfigManager().ResumeDirectory + '/' + '_' + \ str(data_dict['candidateID']) + '_' + \ str(data_dict['documentName']) filewrite = open(resumefilepath, 'wb') f = io.BytesIO(data_dict['ResumeFile']) filewrite.write(data_dict['ResumeFile']) filefilepath = config.ConfigManager().fileDirectory + '/' + '_' + \ str(data_dict['candidateResumeID'])+'-'+str(data_dict['supplierID']) + '_' + \ str(data_dict['documentName']) file_write = open(filefilepath, 'wb') file_write.write(data_dict['ResumeFile']) except BaseException as ex: exception_message = '\n' + 'Exception:' + \ str(datetime.datetime.now()) + '\n' exception_message += 'File: ' + '\n' exception_message += '\n' + str(ex) + '\n' exception_message += '-' * 100 utility.write_to_file(config.ConfigManager().LogFile, 'a', exception_message) if 'candidateResumeID' in data_dict: primary_key = data_dict['candidateResumeID'] UpdateTemplateWhere = utility.clean_dict() UpdateTemplateSet = utility.clean_dict() UpdateTemplateSet['STcandidateResumepk'] = primary_key resumeUpdateDelta = int(primary_key) - \ int(configdocs[0]['STcandidateResumepk']) utility.write_to_file( config.ConfigManager().LogFile, 'a', 'Resumes updated - ' + str(resumeUpdateDelta) + ' ' + str(datetime.datetime.now())) UpdateTemplateWhere['_id'] = configdocs[0]['_id'] DBSet = utility.clean_dict() DBSet['$set'] = UpdateTemplateSet custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection, UpdateTemplateWhere, DBSet, connection)
def requirement_update(): utility.write_to_file( config.ConfigManager().LogFile, 'a', 'requpdatefastest running' + ' ' + str(datetime.datetime.now())) recordnumber = 0 configdocs = custom.retrieve_data_from_DB( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection) query = custom.fetch_query( config.ConfigManager().STCandidateSubmissionsQueryId) if '.' in str(configdocs[0]['submissionsdateCreated']): query = query.replace( '##dateCreated##', str(configdocs[0]['submissionsdateCreated']).split('.')[0] + ('.') + str(configdocs[0]['submissionsdateCreated']).split('.')[1][:3]) else: query = query.replace('##dateCreated##', str(configdocs[0]['submissionsdateCreated'])) # query = query.replace('##dateCreated##', str((datetime.datetime.now() - datetime.timedelta(minutes=480))).split('.')[0] + ('.') + str((datetime.datetime.now() - datetime.timedelta(minutes=480))).split('.')[1][:3]) print(query) cursor = dbmanager.cursor_odbc_connection(config.ConfigManager().STConnStr) db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] connection = dbmanager.mongoDB_connection( int(config.ConfigManager().MongoDBPort)) data_dict1 = {} req_list = [] for row1 in db_data: try: strtimestamp = str(datetime.datetime.now()) recordnumber += 1 print(recordnumber) data_dict1 = dict(utility.zip_list(column_headers, row1)) if not (data_dict1['requirementID']).strip(): requirementIDListperCandidate = [] reqratelist = [] reqratestatdictlist = [] else: reqratelist = [] reqratestatdictlist = [] requirementIDListperCandidate = [ reqID for reqID in (data_dict1['requirementID']).split(',') ] for reqratecombID in requirementIDListperCandidate: reqratelistinit = [ reqrateID for reqrateID in (reqratecombID.strip().replace( '~!@- ', '~!@-')).split('~!@-') ] print(reqratelistinit[0]) reqratestatdict = {} req_list.append(int(reqratelistinit[1])) if reqratelistinit[0] == "0.00": reqratelistinit[0] = "" reqratestatdict['rate'] = reqratelistinit[0] reqratestatdict['requirementId'] = int(reqratelistinit[1]) reqratestatdict['candidateStatus'] = reqratelistinit[2] reqratelist.append(reqratelistinit) reqratestatdictlist.append(reqratestatdict) strtimestamp += ' ' + str(datetime.datetime.now()) UpdateTemplateSet = utility.clean_dict() UpdateTemplateWhere = utility.clean_dict() UpdateTemplateSet[ 'requirementIDList'] = reqratelist # requirementIDListperCandidate UpdateTemplateSet[ 'requirementRateStatusList'] = reqratestatdictlist UpdateTemplateWhere['candidateid'] = data_dict1['CandidateID'] UpdateTemplateWhere['documentType'] = 'candidate details' UpdateTemplateWhere['dataSource'] = 'Smart Track' DBSet = utility.clean_dict() DBSet['$set'] = UpdateTemplateSet print(UpdateTemplateSet['requirementRateStatusList']) custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().STCandidateCollection, UpdateTemplateWhere, DBSet, connection) strtimestamp += ' ' + str(datetime.datetime.now()) except BaseException as ex: utility.log_exception(ex) if 'dateCreated' in data_dict1: if not data_dict1['dateCreated'] is None: UpdateTemplateSet = utility.clean_dict() UpdateTemplateWhere = utility.clean_dict() UpdateTemplateSet['submissionsdateCreated'] = data_dict1[ 'dateCreated'] UpdateTemplateWhere['_id'] = configdocs[0]['_id'] DBSet = utility.clean_dict() DBSet['$set'] = UpdateTemplateSet custom.update_data_to_Db_noupsert( int(config.ConfigManager().MongoDBPort), config.ConfigManager().DataCollectionDB, config.ConfigManager().ConfigCollection, UpdateTemplateWhere, DBSet, connection) if req_list: print(list(set(req_list))) utility.write_to_file( config.ConfigManager().LogFile, 'a', 'requirement id list - ' + str(json.dumps(list(set(req_list)))) + ' ' + str(datetime.datetime.now())) db_requirements_candidate.generate_req_candidate_file_selected_req( list(set(req_list))) utility.write_to_file( config.ConfigManager().LogFile, 'a', 'Number of records for which requirement list was updated - ' + str(recordnumber) + ' ' + str(datetime.datetime.now()))
exception_message += 'File: ' + '\n' exception_message += '\n' + str(ex) + '\n' exception_message += '-' * 100 utility.write_to_file(config.ConfigManager().LogFile, 'a', exception_message) batchId_set = set(batchId_list) unique_batchId_list = list(batchId_set) query_where = '' for item in unique_batchId_list: query_where += "'" + item + "'" + ',' query_where = query_where[:-1] query = 'SELECT * FROM Files WHERE batchId IN' + '(' + query_where + ')' db_data_dict = dbmanager.cursor_execute(cursor, query) db_data = db_data_dict['dbdata'] db_data_cursorexec = db_data_dict['cursor_exec'] cursor_description = db_data_cursorexec.description column_headers = [column[0] for column in cursor_description] print(query) for row in db_data: try: data_dict = dict(utility.zip_list(column_headers, row)) filepath = config.ConfigManager().fileDirectory + '/' + '_' + str( data_dict['batchId']) + '_' + str(data_dict['documentName']) filewrite = open(filepath, 'wb') print(data_dict['fileData']) filewrite.write((data_dict['fileData'])) except BaseException as ex: