def getCouponSentHistoryList_ignoring(couponSeriesId, couponIssuedId=0):
query = "SELECT * FROM `luci`.`coupon_sent_history` WHERE `org_id` = " + str(constant.config['orgId']) + \
" AND `coupon_series_id` = " + str(couponSeriesId)
if couponIssuedId != 0:
query = query + " AND `coupon_issued_id` = " + str(couponIssuedId)
result = dbHelper.queryDB(query, "luci")
couponSentHistory = []
if len(result) == 0:
Logger.log('No records found')
return couponSentHistory
else:
for k in result:
couponSentHistory.append({
'id': k[0],
'orgId': k[1],
'couponSeriesId': k[2],
'couponIssuedId': k[3],
'sentDate': k[4],
'sentFromStore': k[5],
'notes': k[6],
'autoUpdatedTime': k[7]
})
return couponSentHistory
def getUsersFromCampaignGroupRecipient(bucketId,
groupVersionId,
channel='sms',
testControl=None,
reachabilityCalculated=False):
hashId = dbCallsList.getHashIdAsPerChannel(channel, groupVersionId)
query = 'select distinct(user_id) from campaign_data_details.campaign_group_recipients__{} where group_version_id={} and org_id ={}'.format(
bucketId, groupVersionId, constant.config['orgId'])
if hashId is not None:
query = query + ' and hash_id = {}'.format(hashId)
else:
Logger.log('HashId Not Found , so querying without using HashID'
) # TODO: Temporary Solution
if testControl is not None:
query = query + ' and test_control = {}'.format(str(testControl))
if reachabilityCalculated:
query = query + ' and reachability_type_id IN ( -1,4,13,14,27,28,30,32,33,34,35,40,1,15,18,20,22,37,2 )'
result = dbHelper.queryDB(query, 'campaign_data_details')
listOfUsers = []
for eachUser in result:
listOfUsers.append(eachUser[0])
return listOfUsers
def getJobDetailFromPreExecutionJobStatus(self, camaignId, messageId,
jobType):
query = 'select campaign_id,job_status,error_description,params from pre_execution_job_status where org_id ={} and campaign_id = {} and message_id = "{}" and job_type = "{}" and event_type = "{}" order by id desc limit 1'.format(
constant.config['orgId'], camaignId, messageId, jobType,
self.event)
for _ in range(25):
result = dbHelper.queryDB(query, 'veneno')
if len(result) == 0 or result[0][1] in ('OPEN', 'PROCESSING'):
time.sleep(10)
else:
break
if len(result) == 0:
Logger.log(
'Retiral 15 Times with each 10 secs Gap , but No Data Found')
raise Exception(
'NoEntryInPreExecutionJobStatusForMessageId:{},JobType:{}'.
format(messageId, jobType))
return {
'campaign_id': result[0][0],
'job_status': result[0][1],
'error_description': result[0][2],
'params': result[0][3]
}
def getCampaignGroupRecipient(bucketId, groupVersionId, hashId):
Logger.log(
'Getting Campaign Group Recipient with bucketId :{} , groupVersionId :{} and hashId :{}'
.format(bucketId, groupVersionId, hashId))
query = 'select hash_id,reachability_type_id,count(*) as userCount from campaign_group_recipients__' + str(
bucketId) + ' where group_version_id = ' + str(
groupVersionId) + ' and org_id = ' + str(
constant.config['orgId']) + ' group by hash_id'
buildResult = {}
for numberOfTries in range(10):
time.sleep(10)
result = dbHelper.queryDB(query, 'campaign_data_details')
if len(result) > 0:
for eachResult in result:
buildResult[eachResult[0]] = {
'reachability_type_id': eachResult[1],
'userCount': eachResult[2]
}
if buildResult[hashId]['reachability_type_id'] != -1:
break
Logger.log('Result Getting Returned for CGR : {}', buildResult)
return buildResult
def getCouponsIssuedList(couponSeriesId, dbTimeJustBeforeUpload=None):
couponsIssuedList = []
query = "SELECT `id`, `coupon_code`, `issued_to`, `coupon_series_id`, `issued_by` " + \
"FROM `luci`.`coupons_issued` WHERE `org_id` = " + str(constant.config['orgId']) + \
" AND `coupon_series_id` = " + str(couponSeriesId)
if dbTimeJustBeforeUpload is not None:
query = query + " AND `issued_date` > '" + str(
dbTimeJustBeforeUpload) + "'"
result = dbHelper.queryDB(query, "luci")
if len(result) == 0:
Logger.log('No records found')
return couponsIssuedList
else:
for k in result:
couponsIssuedList.append({
'id': k[0],
'couponCode': k[1],
'issuedTo': k[2],
'couponSeriesId': k[3],
'issuedBy': k[4]
})
return couponsIssuedList
def getCouponsCreated(couponSeriesId, isValid=-1, couponCode=None):
couponsCreatedList = []
query = "SELECT `id`, `coupon_code`, `coupon_series_id`, `series_expiry_date`, `is_valid` " \
"FROM `luci`.`coupons_created` WHERE `org_id` = " + str(constant.config['orgId']) + \
" AND `coupon_series_id` = " + str(couponSeriesId)
if couponCode != None:
query += " AND `coupon_code` = '" + couponCode + "'"
if isValid >= 0:
query = query + " AND is_valid = " + str(isValid)
result = dbHelper.queryDB(query, "luci")
if len(result) == 0:
Logger.log('No records found')
return couponsCreatedList
else:
for k in result:
couponsCreatedList.append({
'id': k[0],
'couponCode': k[1],
'coupon_series_id': k[2],
'series_expiry_date': k[3],
'is_valid': k[4]
})
return couponsCreatedList
def getInvalidObjectiveId():
query = 'select max(id) from objective_meta_details limit 1'
result = dbHelper.queryDB(query, 'campaigns')[0]
return {'id': int(result[0]) + 1}
def getValidObjectiveId():
query = 'select id from objective_meta_details limit 1'
result = dbHelper.queryDB(query, 'campaigns')[0]
return str(result[0])
def getCommunicationDetails(campaignId):
query = "select id,bucket_id,target_type,expected_delivery_count,overall_recipient_count,state from communication_details where org_id={} and campaign_id={} order by id desc limit 1".format(
constant.config['orgId'], campaignId)
time.sleep(5)
result = dbHelper.queryDB(query, 'veneno')
return result[0]
def getMaxVersionOfGroup(self, groupId):
query = 'select max(version_number) from group_version_details where group_id = {}'.format(
groupId)
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def getUploadedFileUrl(self, groupId):
query = "select file_url from create_audience_job where org_id = {} and group_version_id = {}".format(
constant.config['orgId'], self.getGroupVersionId(groupId))
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def updateGroupVersionDetailAsInactive(self, groupId):
query = "update group_version_details set is_active = 0 where org_id = {} and group_id = {}".format(
constant.config['orgId'], groupId)
dbHelper.queryDB(query, 'campaign_meta_details')
def getCustomerCountInGVD(self, groupId):
query = "select customer_count from group_version_details where group_id = {} and org_id = {} and target_type = 'TEST' and is_active = 1".format(
groupId, constant.config['orgId'])
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def test_Count_Messages_DELAYED_SCHEDULED(self):
currentMonth = NSAdminHelper.getTableName()
query = 'SELECT COUNT( * ) FROM ' + currentMonth + ' where sent_time >= SUBDATE( current_date, 2) and sent_time < current_date and status in (33)'
output = dbHelper.queryDB(query, 'nsadmin')
Assertion.constructAssertion(output[0][0] == 0,
'DELAYED_SCHEDULED count')
def getLatestCampaignIdOfV1(self):
query = 'select id from campaigns_base where org_id = {} order by id desc limit 1 '.format(
constant.config['orgId'])
result = dbHelper.queryDB(query, 'campaigns')
return result[0][0]
def getMergeUserLogList(fromUserId, toUserId):
query = "SELECT * FROM `merge_user_log` WHERE `org_id` = " + constant.config['orgId'] + \
" AND `from_user_id` = " + fromUserId + " AND `to_user_id` = " + toUserId
result = dbHelper.queryDB(query, "luci")
if len(result) == 0:
Logger.log('No records found')
def getAllUserInformationFromOrg(loyaltyType='loyalty'):
query = "select u.firstname,u.lastname,u.mobile from users u,loyalty l where u.id=l.user_id and u.email like 'iris%' and u.org_id = " + str(constant.config['orgId']) + " and l.publisher_id = " + str(constant.config['orgId']) + " and l.type = '{}' order by u.id desc ".format(loyaltyType)
result = dbHelper.queryDB(query, 'user_management')
return result
def getCouponRedemptionsListByCouponIssuedId(couponIssuedId):
query = "SELECT * FROM `luci`.`coupon_redemptions` WHERE `coupon_issued_id` = " + couponIssuedId
result = dbHelper.queryDB(query, "luci")
if len(result) == 0:
Logger.log('No records found')
Logger.log(
"Tried Adding Number :{} as Android User in List but failed due to Exception :{}"
.format(
constant.config['mobilepush']
['androidE2E_User'], exp))
Logger.log(
'List Of Existing Users :{}'.format(listOfMobilePushUsers))
return listOfMobilePushUsers
else:
query = "select u.id,u.firstname,u.lastname,u.mobile,u.email,l.external_id from users u,loyalty l where u.id=l.user_id and u.email like 'iris_automation%' and u.org_id = " + str(
constant.config['orgId']
) + " and l.publisher_id = " + str(
constant.config['orgId']
) + " and l.type = 'loyalty' order by u.id asc limit {},{} ".format(
offset, numberOfusers)
result = dbHelper.queryDB(query, 'user_management')
return result
def getCustomerCountInGVD(self, groupId):
query = "select customer_count from group_version_details where group_id = {} and org_id = {} and target_type = 'TEST' and is_active = 1".format(
groupId, constant.config['orgId'])
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def getParamsFromGVD(self, groupId):
query = "select params from group_version_details where group_id = {} and org_id = {} and target_type = 'TEST'".format(
groupId, constant.config['orgId'])
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def getIdOfReloadedList(self, testControl):
def getCampaignIdFromCampaignName(campaignName):
query = "select id from campaigns_base where name ='" + str(
campaignName) + "'"
result = dbHelper.queryDB(query, 'campaigns')[0]
return str(result[0])
def bucketDetails(self):
query = 'select rows_count from bucket_details order by id desc limit 1 '
result = dbHelper.queryDB(query, 'campaign_meta_details')[0]
self.metaDetail.update({'bucketDetails': result})
def getCampaignTagsFromCampaignId(campaignId):
query = 'select id,tags from campaign_tags where campaign_id =' + str(
campaignId)
result = dbHelper.queryDB(query, 'campaigns')[0]
return {'id': result[0], 'tags': result[1]}
def getNonVisibleList(self, type):
query = "select id from group_details where org_id = {} and is_visible =0 and type = '{}' order by id desc limit 1".format(
constant.config['orgId'], type)
result = dbHelper.queryDB(query, 'campaign_meta_details')
return {'id': result[0][0]}
def getGenericIncentiveId(message_queue_id):
query = 'select incentive_type_id,campaign_id from incentive_mapping where message_queue_id = ' + str(message_queue_id)
result = dbHelper.queryDB(query, 'campaigns')[0]
return {'incentive_type_id' : result[0], 'campaign_id':result[1]}
def getGroupVersionId(self, groupId):
query = "select id from group_version_details where group_id = {} and org_id = {} and target_type = 'TEST'".format(
groupId, constant.config['orgId'])
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def getValidGoalId():
query = 'select id from campaign_roi_types limit 1'
result = dbHelper.queryDB(query, 'campaigns')[0]
return str(result[0])
def getGroupLabelById(self, groupId):
query = "select group_label from group_details where org_id = {} and id = {}".format(
constant.config['orgId'], groupId)
result = dbHelper.queryDB(query, 'campaign_meta_details')
return result[0][0]
def getInvalidGoalId():
query = 'select max(id) from campaign_roi_types limit 1'
result = dbHelper.queryDB(query, 'campaigns')[0]
return {'id': int(result[0]) + 1}
def getObjectiveMappingIdFromCampaignId(campaignId):
query = 'select id,objective_type_id from objective_mapping where campaign_id=' + str(
campaignId)
result = dbHelper.queryDB(query, 'campaigns')[0]
return {'id': result[0], 'objective_type_id': result[1]}
def getMaxConn():
Logger.log('Checking Max connection for DB')
queries = ['show variables like "max_connections"', 'SHOW GLOBAL STATUS LIKE "%Threads_running%"', "SHOW GLOBAL STATUS LIKE '%Threads_connected%'", "select USER, count(*) from information_schema.PROCESSLIST group by USER", "select DB, count(*) from information_schema.PROCESSLIST group by DB"]
for query in queries:
result = dbHelper.queryDB(query, 'warehouse')
Logger.log('Query : ', result)
