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)