'Bazaarvoice, Inc.')
for inputAccountTeamRecord in inputDictReader:
totalCount += 1
#Validate input record
if inputAccountTeamRecord['Account ID'] is not None and len(inputAccountTeamRecord['Account ID']) > 0 and inputAccountTeamRecord['Account ID'] not in badAccountID and "Generated" not in inputAccountTeamRecord['Account ID']:
# Check to see if we have seen this record
SQL = '''SELECT ID,
SF_ID_18,
CSD,
TSM,
TAM
FROM Accounts
WHERE ID = ?;'''
SQLdata = (cleanStr(inputAccountTeamRecord['Account ID']), )
dbReadCursor.execute(SQL, SQLdata)
dbAccountRecord = dbReadCursor.fetchone()
if dbAccountRecord is None:
#Record does not exist. Adding a new record.
uniqueKeyCount += 1
logger.warning('Account ' + cleanStr(inputAccountTeamRecord['Account ID']) + ' does not exist.')
else:
#Record exists. Will update if flag is set.
updateKeyCount += 1
if args.update:
logger.debug('Updating: ' + cleanStr(inputAccountTeamRecord['Account ID']))
if inputAccountTeamRecord['Team Role'] == 'Client Partner':
if dbAccountRecord[2] is not None:
logger.info('Updating existing TAM value with new value. ID=' + dbAccountRecord[0] +
', dbValue=' + cleanStr(dbAccountRecord[2]) +
and "Generated" not in inputAccountRecord['Account ID']):
# Check to see if we have seen this record
SQL = '''SELECT ID,
SF_ID_18,
Name,
Parent_Account_ID,
Parent_Account_Name,
Ultimate_Parent_Account_ID,
Ultimate_Parent_Account_Name,
Country,
Country_Code,
CS_Segment,
CS_Vertical
FROM Accounts
WHERE ID = ?;'''
SQLdata = (cleanStr(inputAccountRecord['Account ID']), )
dbReadCursor.execute(SQL, SQLdata)
dbAccountRecord = dbReadCursor.fetchone()
if dbAccountRecord is None:
#Record does not exist. Adding a new record.
uniqueKeyCount += 1
SQL = '''INSERT INTO Accounts (ID,
SF_ID_18,
Name,
Parent_Account_ID,
Parent_Account_Name,
Ultimate_Parent_Account_ID,
Ultimate_Parent_Account_Name,
Country,
Country_Code,
CS_Segment,
apiRowCount = apiResponseJSON['_metadata'].get('rowCount')
apiOffset = apiOffset + apiLimit
for key in apiResponseJSON['keys']:
keyCount += 1
logger.debug(str(keyCount) + " " + key.get('apiKey'))
# Check to see if we have seen this key
SQL = 'SELECT API_Key, fk_Site_ID, fk_Member_ID, Application_Name, Description, KMS_Valid FROM Keys WHERE API_key = ?;'
SQLdata = (cleanStrLower(key.get('apiKey')), )
dbCursor.execute(SQL, SQLdata)
dbKeyRecord = dbCursor.fetchone()
if dbKeyRecord is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Keys (API_Key, fk_Site_ID, fk_Member_ID, Application_Name, Description, KMS_Valid) VALUES (?, ?, ?, ?, ?, 1);'
SQLdata = (cleanStrLower(key.get('apiKey')),
cleanStrLower(key.get('customer')),
cleanStr(key['member'].get('userName')),
cleanStr(key['application'].get('name')),
cleanStr(key['application'].get('description')), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on insert into Keys: ' + str(errorMessage) + ' on record: ' + key.get('apiKey'))
else:
if args.update:
updateKeyCount += 1
logger.debug('Updating: ' + cleanStr(key.get('email')))
SQL = 'UPDATE Members SET fk_Site_ID = ?, fk_Member_ID = ?, Application_Name = ?, Description = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (cleanStrLower(key.get('customer')),
cleanStr(key['member'].get('userName')),
cleanStr(key['application'].get('name')),
cleanStr(key['application'].get('description')), )
#print(apiResponseJSON['_metadata'])
#print(apiResponseJSON['_metadata'].get('totalRows'))
apiRowCount = apiResponseJSON['_metadata'].get('rowCount')
apiOffset = apiOffset + apiLimit
for member in apiResponseJSON['members']:
memberCount += 1
logger.debug(str(memberCount) + " " + member.get('email'))
# Check to see if we have seen this member
SQL = 'SELECT ID, User_Email, User_Last_Name, User_First_Name FROM Members WHERE ID = ?;'
SQLdata = (cleanStrLower(member.get('userName')), )
dbCursor.execute(SQL, SQLdata)
dbMemberRecord = dbCursor.fetchone()
if dbMemberRecord is None:
uniqueMemberCount += 1
SQL = 'INSERT INTO Members (ID, User_Email, User_Last_Name, User_First_Name, KMS_Valid) VALUES (?, ?, ?, ?, 1);'
SQLdata = (cleanStrLower(member.get('userName')), cleanStrLower(member.get('email')), cleanStr(member.get('lastName')), cleanStr(member.get('firstName')), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Members: ' + str(errorMessage) + ' on record: ' + member.get('email'))
else:
if args.update:
updateMemberCount += 1
logger.debug('Updating: ' + cleanStr(member.get('email')))
SQL = 'UPDATE Members SET User_Email = ?, User_Last_Name = ?, User_First_Name = ?, KMS_Valid = 1 WHERE ID = ?;'
SQLdata = (cleanStrLower(member.get('email')), cleanStr(member.get('lastName')), cleanStr(member.get('firstName')), cleanStrLower(member.get('userName')), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Members: ' + str(errorMessage) + ' on record: ' + member.get('email'))
else:
def processQuery (database, SQL):
matchCount = 0
emailCount = 0
nameCount = 0
descriptionCount = 0
SQLdata = ()
dbCursor.execute(SQL, SQLdata)
queryRecords = dbCursor.fetchall()
if queryRecords is None:
logger.info('No rows retrieved from query: ' + SQL)
else:
readRow = dict(
key = str(),
application_name = str(),
email = str())
for queryRecord in queryRecords:
matchCount += 1
readRow = dict(
key = queryRecord[0],
application_name = queryRecord[1],
email = queryRecord[2],
description = queryRecord[3])
writeRow = dict(
key = str(),
application_name = str(),
description = str(),
email =str(),
last_name = str(),
first_name = str())
URL = 'https://kmsapi.portal.bazaarvoice.com/api/v1/keys/' + readRow['key']
payload = {'passkey': args.apiKey}
keyDetails = requests.get(URL, params=payload)
if keyDetails.status_code == 200:
writeRow['key'] = cleanStrLower(readRow['key'])
keySet = keyDetails.json()
for keyDetailsJSON in keySet['keys']:
applicationJSON = keyDetailsJSON.get('application')
if applicationJSON:
logger.debug('Application JSON: ' + str(applicationJSON))
logger.debug('Application Name WriteRow: ' + str(writeRow))
writeRow['application_name'] = cleanStr(applicationJSON.get('name'))
if readRow['application_name'] is None and writeRow['application_name'] is not None:
nameCount += 1
SQL = 'UPDATE Keys SET Application_Name = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['application_name'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update application name: ' + str(errorMessage) + ' on record: ' + str(writeRow))
writeRow['description'] = cleanStr(applicationJSON.get('description'))
if readRow['description'] is None and writeRow['description'] is not None:
descriptionCount += 1
SQL = 'UPDATE Keys SET Description = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['description'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update description: ' + str(errorMessage) + ' on record: ' + str(writeRow))
memberJSON = keyDetailsJSON.get('member')
if memberJSON:
writeRow['email'] = cleanStrLower(memberJSON.get('email'))
writeRow['last_name'] = cleanStr(memberJSON.get('last_name'))
writeRow['first_name'] = cleanStr(memberJSON.get('first_name'))
if readRow['email'] is None and writeRow['email'] is not None:
emailCount += 1
logger.debug('Email JSON: ' + str(memberJSON))
logger.debug('Email WriteRow: ' + str(writeRow))
SQL = 'SELECT User_Email FROM Members WHERE User_Email = ?;'
SQLdata = (writeRow['email'], )
dbEmailLookupCursor.execute(SQL, SQLdata)
dbMemberRecord = dbEmailLookupCursor.fetchone()
if dbMemberRecord is None:
SQL = 'INSERT INTO Members (User_Email, User_Last_Name, User_First_Name) VALUES (?, ?, ?);'
SQLdata = (writeRow['email'], writeRow['last_name'], writeRow['first_name'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on insert into Member: ' + str(errorMessage) + ' on record: ' + str(writeRow))
SQL = 'UPDATE Keys SET fk_User_Email = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['email'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update email in Keys: ' + str(errorMessage) + ' on record: ' + str(writeRow))
else:
if keyDetails.status_code == 404:
logger.debug('Key: ' + readRow['key'] + ', ' + 'keyDetails.status_code: ' + str(keyDetails.status_code))
SQL = 'UPDATE Keys SET KMS_Valid = 0 WHERE API_Key = ?;'
SQLdata = (writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update KMS_Valid = 0 in Keys: ' + str(errorMessage) + ' on record: ' + str(writeRow))
else:
logger.error('Key: ' + readRow['key'] + ', ' + 'keyDetails.status_code: ' + str(keyDetails.status_code))
logger.debug('dbrecord: ' + str(queryRecord))
dbConnection.commit()
logger.info('Matched Criteria: ' + str(matchCount))
logger.info('Application Name Found in KMS: ' + str(nameCount))
logger.info('Application Description Found in KMS: ' + str(descriptionCount))
logger.info('Emails Found in KMS: ' + str(emailCount))
return()
uniqueKeyCount += 1
SQL = 'INSERT INTO Clients (Client, SF_Account_Name, SF_Account_ID, CS_Segment, SF_CSD, SF_CSP, Country_Name, Country_Code) VALUES (?, ?, ?, ?, ?, ?, ?, ?);'
SQLdata = (tempClient, inputFileRecord['sf_accountname'], inputFileRecord['account ID'], inputFileRecord['CS_Segment'], inputFileRecord['sf_csd'], inputFileRecord['sf_csp'], inputFileRecord['Country Name'], inputFileRecord['Country_Code'], )
dbCursor.execute(SQL, SQLdata)
else:
updateKeyCount += 1
SQL = '''UPDATE Clients SET Client = ?,
SF_Account_Name = ?,
SF_Account_ID = ?,
CS_Segment = ?,
SF_CSD = ?,
SF_CSP = ?,
Country_Name = ?,
Country_Code = ?
WHERE Client = ?;'''
SQLdata = (cleanStr(inputFileRecord['name']),
cleanStr(inputFileRecord['sf_accountname']),
cleanStr(inputFileRecord['account ID']),
cleanStr(inputFileRecord['CS_Segment']),
cleanStr(inputFileRecord['sf_csd']),
cleanStr(inputFileRecord['sf_csp']),
cleanStr(inputFileRecord['Country Name']),
cleanStr(inputFileRecord['Country_Code']),
tempClient, )
dbCursor.execute(SQL, SQLdata)
inputFileHandle.close()
dbConnection.commit()
dbConnection.close()
logger.info('Total Records: ' + str(totalCount))
SQLdata = (cleanStrLower(inputMemberRecord['apikey']), )
dbCursor.execute(SQL, SQLdata)
dbKeyRecord = dbCursor.fetchone()
if dbKeyRecord is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Keys (API_key, fk_Site_ID, fk_User_Email) VALUES (?, ?, ?);'
SQLdata = (cleanStrLower(inputMemberRecord['apikey']), cleanStrLower(inputMemberRecord['client']), cleanStrLower(inputMemberRecord['email']), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Sites: ' + str(errorMessage) + ' on record: ' + str(inputMemberRecord))
else:
# Make sure site ID matches
if cleanStrLower(dbKeyRecord[1]) != cleanStrLower(inputMemberRecord['client']):
keyClientErrorCount += 1
logger.warning("Key " + cleanStrLower(inputMemberRecord['apikey']) + " has mismatched site ID data DB Site_ID = " + cleanStr(dbKeyRecord[1]) + " input file Client = " + cleanStr(inputMemberRecord['client']))
if args.update:
logger.debug('Updating API key:' + cleanStr(inputMemberRecord['apikey']) + ' with member:' + cleanStrLower(inputMemberRecord['email']))
if dbKeyRecord[2] is not None:
logger.info('Updating existing email value with new value. ID=' + dbKeyRecord[0] +
', dbValue=' + cleanStr(dbKeyRecord[2]) +
', newValue=' + cleanStr(inputMemberRecord['email']))
SQL = 'UPDATE Keys SET fk_User_Email = ? WHERE API_Key = ?;'
SQLdata = (cleanStrLower(inputMemberRecord['email']), cleanStrLower(inputMemberRecord['apikey']), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Sites: ' + str(errorMessage) + ' on record: ' + str(inputMemberRecord))
else:
logger.debug('Skipping - No Update Flag - API key:' + cleanStr(inputMemberRecord['apikey']) + ' with member:' + cleanStrLower(inputMemberRecord['email']))
WHERE ID = ?;'''
SQLdata = (cleanStrLower(inputSiteRecord['BV Site: BV Site Name']), )
dbReadCursor.execute(SQL, SQLdata)
dbSiteRecord = dbReadCursor.fetchone()
if dbSiteRecord is None:
#Record does not exist. Adding a new record.
uniqueKeyCount += 1
SQL = '''INSERT INTO Sites (ID,
SF_ID,
SF_ID_18,
Name,
fk_Account_ID,
fk_Accounts_SF_ID_18)
VALUES (?, ?, ?, ?, ?, ?);'''
SQLdata = (cleanStrLower(inputSiteRecord['BV Site: BV Site Name']),
cleanStr(inputSiteRecord['BV Site: ID']),
"",
cleanStr(inputSiteRecord['BV Site: BV Site Name']),
cleanStr(inputSiteRecord['Account ID']),
cleanStr(inputSiteRecord['SFDC Account ID 18']), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on insert into Sites: ' + str(errorMessage) + ' on record: ' + str(inputSiteRecord))
else:
#Record exists. Will update if flag is set.
updateKeyCount += 1
if args.update:
logger.info('Updating: ' + cleanStr(inputSiteRecord['BV Site: ID']) + ', "' + cleanStr(inputSiteRecord['BV Site: BV Site Name']) + '"')
SQL = '''UPDATE Sites SET SF_ID = ?,
SF_ID_18 = ?,
