class Channel:
def __init__(self, net_id, obj_repo):
self.repo = obj_repo
self.objSrvc = Service(obj_repo=obj_repo)
self.ws_provider = NETWORKS[net_id]['ws_provider']
self.obj_util = Utils()
def get_channel_info(self, user_address, service_id, org_id):
print('Inside get_channel_info::user_address', user_address, '|',
service_id, '|', org_id)
try:
if user_address is not None and service_id is not None and org_id is not None:
query = 'SELECT M.*, T.endpoint, T.is_available FROM service_group G, mpe_channel M, service_status T, service S ' \
'WHERE G.group_id = M.groupId AND G.group_id = T.group_id and S.row_id = T.service_row_id and T.service_row_id = G.service_row_id ' \
'AND T.is_available = 1 AND G.service_id = %s AND G.org_id = %s AND M.sender = %s ORDER BY M.expiration DESC '
print('query: ', query)
result = self.repo.execute(query,
[service_id, org_id, user_address])
return self._manage_channel_data(result=result,
service_id=service_id,
org_id=org_id)
except Exception as err:
print('Error in get_channel_info: ', err)
def _manage_channel_data(self, result, service_id, org_id):
channels_data = {}
if result is not None and result != []:
for rec in result:
group_id = rec['groupId']
if group_id not in channels_data.keys():
channels_data[group_id] = {}
channels_data[group_id]['endpoint'] = []
channels_data[group_id]['channels'] = {}
channel_id = rec['channel_id']
if channel_id not in channels_data[group_id]['channels'].keys(
):
channels_data[group_id]['channels'][channel_id] = {}
channels_data[group_id]['channels'][channel_id].update({
'channelId':
channel_id,
'balance_in_cogs':
str(rec['balance_in_cogs']),
'pending':
str(rec['pending']),
'nonce':
rec['nonce'],
'expiration':
rec['expiration'],
'signer':
rec['signer']
})
channels_data[group_id].update({
'groupId': group_id,
'sender': rec['sender'],
'recipient': rec['recipient']
})
channels_data[group_id]['endpoint'].append(rec['endpoint'])
channels_data[group_id]['channels'] = [
val for val in channels_data[group_id]['channels'].values()
]
channels_data = [val for val in channels_data.values()]
return channels_data
else:
query = ' SELECT T.group_id, T.endpoint, T.is_available, G.payment_address as recipient FROM service_group G, service_status T, service S ' \
'WHERE G.group_id = T.group_id AND S.row_id = T.service_row_id AND T.service_row_id = G.service_row_id ' \
'AND T.is_available = 1 AND G.service_id = %s AND G.org_id = %s '
print('query: ', query)
result = self.repo.execute(query, [service_id, org_id])
if result is not None and result != []:
for rec in result:
group_id = rec['group_id']
if group_id not in channels_data.keys():
channels_data[group_id] = {}
channels_data[group_id]['groupId'] = group_id
channels_data[group_id]['recipient'] = rec['recipient']
channels_data[group_id]['endpoint'] = []
channels_data[group_id]['channels'] = []
channels_data[group_id]['endpoint'].append(rec['endpoint'])
channels_data = [val for val in channels_data.values()]
return channels_data
return []
def get_latest_block_no(self):
w3Obj = Web3(web3.providers.WebsocketProvider(self.ws_provider))
return w3Obj.eth.blockNumber
def get_expired_channel_info(self, user_address):
print('Inside get_expired_channel_info::user_address', user_address)
try:
expired_chnl_data = list()
last_block_no = self.get_latest_block_no()
print("last_block_no: ", last_block_no)
qry = "SELECT G.org_id, D.display_name, M.* FROM mpe_channel M, service_group G ,service_metadata D, " \
"organization O WHERE M.groupId = G.group_id AND M.recipient = G.payment_address AND " \
"D.service_row_id = G.service_row_id AND O.org_id = G.org_id AND balance_in_cogs > 0 AND " \
"expiration < %s AND sender = %s "
print("qry: ", qry)
channel_details = self.repo.execute(qry,
[last_block_no, user_address])
for detail in channel_details:
expired_chnl_data.append(self.obj_util.clean_row(detail))
except Exception as e:
print(repr(e))
raise e
return expired_chnl_data
class Service:
def __init__(self, obj_repo):
self.repo = obj_repo
self.obj_utils = Utils()
def get_group_info(self, org_id=None, srvc_id=None):
print('Inside grp info')
service_status_dict = {}
try:
if org_id is None:
status = self.repo.execute(
"SELECT G.*, S.endpoint, S.is_available, S.last_check_timestamp FROM service_group G, " \
"service_status S WHERE G.service_row_id = S.service_row_id AND G.group_id = S.group_id " \
"AND G.service_row_id IN (SELECT row_id FROM service WHERE is_curated = 1)")
elif org_id is not None and srvc_id is None:
query = "SELECT G.*, S.endpoint, S.is_available, S.last_check_timestamp FROM service_group G, " \
"service_status S WHERE G.service_row_id = S.service_row_id AND G.group_id = S.group_id " \
"AND G.service_row_id IN (SELECT row_id FROM service WHERE is_curated = 1 and org_id = %s)"
status = self.repo.execute(query, org_id)
else:
query = "SELECT G.*, S.endpoint, S.is_available, S.last_check_timestamp FROM service_group G, " \
"service_status S WHERE G.service_row_id = S.service_row_id AND G.group_id = S.group_id " \
"AND G.service_row_id IN (SELECT row_id FROM service WHERE is_curated = 1 and org_id = %s " \
"AND service_id = %s ) "
status = self.repo.execute(query, [org_id, srvc_id])
self.obj_utils.clean(status)
for rec in status:
srvc_rw_id = rec['service_row_id']
grp_id = rec['group_id']
endpoint_value = {
'is_available': rec['is_available'],
'last_check_timestamp': rec['last_check_timestamp']
}
endpoint = rec['endpoint']
if srvc_rw_id not in service_status_dict.keys():
service_status_dict[srvc_rw_id] = {
'service_id': rec['service_id'],
'org_id': rec['org_id']
}
service_status_dict[srvc_rw_id]['grp'] = {}
if grp_id not in service_status_dict.get(srvc_rw_id).keys():
service_status_dict[srvc_rw_id]['grp'][grp_id] = {}
service_status_dict[srvc_rw_id]['grp'][grp_id][
'payment_address'] = rec['payment_address']
service_status_dict[srvc_rw_id]['grp'][grp_id][
'endpoint'] = {}
service_status_dict[srvc_rw_id]['grp'][grp_id][
'endpoint'].update({endpoint: endpoint_value})
service_status = self.process_service_status(service_status_dict)
except Exception as e:
print(repr(e))
raise e
return service_status
def process_service_status(self, service_status_dict):
service_status = []
for srvc_rw_id in service_status_dict.keys():
is_available = 0
grps_dict = service_status_dict.get(srvc_rw_id, {})
grps = []
for grp_id in grps_dict['grp'].keys():
grp_available = 0
endpts_dict = grps_dict['grp'].get(grp_id, {})
endpts = []
for endpt in endpts_dict['endpoint'].keys():
endpt_available = endpts_dict['endpoint'][endpt].get(
'is_available', None)
last_check_timestamp = endpts_dict['endpoint'][endpt].get(
'last_check_timestamp', None)
endpts.append({
'endpoint': endpt,
'is_available': endpt_available,
'last_check_timestamp': last_check_timestamp
})
grp_available = grp_available or endpt_available
is_available = is_available or grp_available
grps.append({
'group_id':
grp_id,
'endpoints':
endpts,
'is_available':
grp_available,
'payment_address':
endpts_dict.get('payment_address', '')
})
service_status.append({
'service_row_id': srvc_rw_id,
'service_id': grps_dict['service_id'],
'org_id': grps_dict['org_id'],
'groups': grps,
'is_available': is_available
})
return service_status
def fetch_total_count(self):
user_vote_count_dict = {}
try:
count_details = self.repo.execute(
"SELECT org_id, service_id, vote, Count(*) AS count FROM user_service_vote GROUP BY vote, service_id, org_id"
)
for rec in count_details:
org_id = rec.get('org_id', '')
service_id = rec.get('service_id', '')
vote = rec.get('vote', '')
count = rec.get('count', '')
if not org_id in user_vote_count_dict.keys():
user_vote_count_dict[org_id] = {}
if not service_id in user_vote_count_dict[org_id].keys():
user_vote_count_dict[org_id][service_id] = {}
user_vote_count_dict[org_id][service_id][vote] = count
except Exception as e:
print(repr(e))
raise e
return user_vote_count_dict
def vote_mapping(self, vote):
return {'up_vote': (vote == 1), 'down_vote': (vote == 0)}
def fetch_user_vote(self, user_address):
user_vote_dict = {}
try:
votes = self.repo.execute(
"SELECT * FROM user_service_vote WHERE user_address = %s",
(user_address))
self.obj_utils.clean(votes)
for rec in votes:
org_id = rec['org_id']
service_id = rec['service_id']
comment = rec.get('comment', None)
if org_id not in user_vote_dict.keys():
user_vote_dict[org_id] = {}
user_vote_dict[org_id][service_id] = {
'user_address': rec['user_address'],
'org_id': rec['org_id'],
'service_id': service_id,
'comment': comment
}
user_vote_dict[org_id][service_id].update(
self.vote_mapping(rec['vote']))
except Exception as e:
print(repr(e))
raise e
return user_vote_dict
def get_user_vote(self, user_address):
vote_list = []
try:
count_details = self.fetch_total_count()
votes = self.fetch_user_vote(user_address)
for org_id in count_details.keys():
srvcs_data = count_details[org_id]
for service_id in srvcs_data.keys():
rec = {
'org_id':
org_id,
'service_id':
service_id,
'up_vote_count':
srvcs_data.get(service_id).get(1, 0),
'down_vote_count':
srvcs_data.get(service_id).get(0, 0),
"up_vote":
votes.get(org_id, {}).get(service_id,
{}).get('up_vote', False),
"down_vote":
votes.get(org_id, {}).get(service_id,
{}).get('down_vote', False)
}
vote_list.append(rec)
except Exception as e:
print(repr(e))
raise e
return vote_list
def fetch_user_feedbk(self, user_address):
user_vote_dict = {}
try:
votes = self.repo.execute(
"SELECT * FROM user_service_vote WHERE user_address = %s",
(user_address))
self.obj_utils.clean(votes)
feedbk = self.repo.execute(
"SELECT A.* FROM user_service_feedback A INNER JOIN (SELECT user_address,org_id, service_id, "
"max(row_updated) latest_dt FROM user_service_feedback WHERE user_address = %s GROUP BY user_address, "
"org_id, service_id) B on A.user_address = B.user_address AND A.org_id = B.org_id AND A.service_id = "
"B.service_id AND A.row_updated = B.latest_dt", (user_address))
self.obj_utils.clean(feedbk)
for rec in votes:
org_id = rec['org_id']
service_id = rec['service_id']
if org_id not in user_vote_dict.keys():
user_vote_dict[org_id] = {}
user_vote_dict[org_id][service_id] = {
'user_address': rec['user_address'],
'org_id': rec['org_id'],
'service_id': service_id
}
user_vote_dict[org_id][service_id].update(
self.vote_mapping(rec['vote']))
for rec in feedbk:
org_id = rec['org_id']
service_id = rec['service_id']
user_vote_dict[org_id][service_id]['comment'] = rec['comment']
except Exception as e:
print(repr(e))
raise e
return user_vote_dict
def get_usr_feedbk(self, user_address):
vote_list = []
try:
count_details = self.fetch_total_count()
votes = self.fetch_user_feedbk(user_address)
for org_id in count_details.keys():
srvcs_data = count_details[org_id]
for service_id in srvcs_data.keys():
rec = {
'org_id':
org_id,
'service_id':
service_id,
'up_vote_count':
srvcs_data.get(service_id).get(1, 0),
'down_vote_count':
srvcs_data.get(service_id).get(0, 0),
"up_vote":
votes.get(org_id, {}).get(service_id,
{}).get('up_vote', False),
"down_vote":
votes.get(org_id, {}).get(service_id,
{}).get('down_vote', False),
"comment":
votes.get(org_id, {}).get(service_id,
{}).get('comment', None)
}
vote_list.append(rec)
except Exception as e:
print(repr(e))
raise e
return vote_list
def is_valid_vote(self, net_id, vote_info_dict):
try:
provider = web3.HTTPProvider(NETWORKS[net_id]['http_provider'])
w3 = web3.Web3(provider)
message_text = str(vote_info_dict['user_address']) + str(vote_info_dict['org_id']) + \
str(vote_info_dict['up_vote']).lower() + str(vote_info_dict['service_id']) + \
str(vote_info_dict['down_vote']).lower()
message = w3.sha3(text=message_text)
message_hash = defunct_hash_message(primitive=message)
recovered = str(
w3.eth.account.recoverHash(
message_hash, signature=vote_info_dict['signature']))
return str(
vote_info_dict['user_address']).lower() == recovered.lower()
except Exception as e:
print(repr(e))
raise e
return False
def set_user_vote(self, vote_info_dict, net_id):
try:
vote = -1
if vote_info_dict['up_vote']:
vote = 1
elif vote_info_dict['down_vote']:
vote = 0
if self.is_valid_vote(net_id=net_id,
vote_info_dict=vote_info_dict):
query = "Insert into user_service_vote (user_address, org_id, service_id, vote, row_created) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE VOTE = %s"
q_params = [
vote_info_dict['user_address'], vote_info_dict['org_id'],
vote_info_dict['service_id'], vote,
datetime.datetime.utcnow(), vote
]
res = self.repo.execute(query, q_params)
else:
raise Exception("Signature of the vote is not valid.")
except Exception as e:
print(repr(e))
raise e
return True
def is_valid_feedbk(self, net_id, usr_addr, msg_txt, sign):
try:
provider = web3.HTTPProvider(NETWORKS[net_id]['http_provider'])
w3 = web3.Web3(provider)
message = w3.sha3(text=msg_txt)
message_hash = defunct_hash_message(primitive=message)
recovered = str(
w3.eth.account.recoverHash(message_hash, signature=sign))
return str(usr_addr).lower() == recovered.lower()
except Exception as e:
print(repr(e))
raise e
return False
def set_usr_feedbk(self, feedbk_info, net_id):
try:
vote = -1
if feedbk_info['up_vote']:
vote = 1
elif feedbk_info['down_vote']:
vote = 0
curr_dt = datetime.datetime.utcnow()
usr_addr = feedbk_info['user_address']
org_id = feedbk_info['org_id']
srvc_id = feedbk_info['service_id']
comment = feedbk_info['comment']
msg_txt = str(usr_addr) + str(org_id) + str(feedbk_info['up_vote']).lower() + str(srvc_id) + \
str(feedbk_info['down_vote']).lower() + str(comment).lower()
if self.is_valid_feedbk(net_id=net_id,
usr_addr=usr_addr,
msg_txt=msg_txt,
sign=feedbk_info['signature']):
self.repo.begin_transaction()
insrt_vote = "INSERT INTO user_service_vote (user_address, org_id, service_id, vote, row_updated, row_created) " \
"VALUES (%s, %s, %s, %s, %s, %s) " \
"ON DUPLICATE KEY UPDATE vote = %s, row_updated = %s"
insrt_vote_params = [
usr_addr, org_id, srvc_id, vote, curr_dt, curr_dt, vote,
curr_dt
]
self.repo.execute(insrt_vote, insrt_vote_params)
insrt_feedbk = "INSERT INTO user_service_feedback (user_address, org_id, service_id, comment, " \
"row_updated, row_created)" \
"VALUES (%s, %s, %s, %s, %s, %s) "
insrt_feedbk_params = [
usr_addr, org_id, srvc_id, comment, curr_dt, curr_dt
]
self.repo.execute(insrt_feedbk, insrt_feedbk_params)
self.repo.commit_transaction()
else:
raise Exception("signature of the vote is not valid.")
except MySQLError as e:
self.repo.rollback_transaction()
raise e
except Exception as err:
print(repr(err))
raise err
return True
def get_curated_services(self):
try:
services = self.repo.execute(
"SELECT * FROM service S, service_metadata M WHERE S.row_id = M.service_row_id AND S.is_curated = 1"
)
groups = self.repo.execute("SELECT G.*, E.* FROM service S, service_group G, service_endpoint E WHERE " \
"G.group_id = E.group_id AND G.service_row_id = S.row_id AND " \
"E.service_row_id = S.row_id AND S.is_curated = 1")
tags = self.repo.execute(
"SELECT T.* FROM service S, service_tags T WHERE T.service_row_id = S.row_id AND S.is_curated = 1"
)
grouped_services = self.__merge_service_data(
services, groups, tags)
except Exception as e:
print(repr(e))
raise e
return grouped_services
def get_profile_details(self, user_address):
try:
mpe_details = list()
qry = "SELECT G.org_id, D.display_name, M.* FROM mpe_channel M, service_group G ,service_metadata D, " \
"organization O WHERE M.groupId = G.group_id AND M.recipient = G.payment_address AND " \
"D.service_row_id = G.service_row_id AND O.org_id = G.org_id AND sender = %s "
channel_details = self.repo.execute(qry, (user_address))
for detail in channel_details:
mpe_details.append(self.obj_utils.clean_row(detail))
except Exception as e:
print(repr(e))
raise e
return mpe_details
def __merge_service_data(self, services, groups, tags):
tag_map = self.__map_to_service(tags)
groups_map = self.__map_to_service(groups)
for service in services:
self.obj_utils.clean_row(service)
service_row_id = service['service_row_id']
service['groups'] = self.__get_group_with_endpoints(
groups_map[service_row_id])
if service_row_id in tag_map:
service['tags'] = [
tag['tag_name'] for tag in tag_map[service_row_id]
]
else:
service['tags'] = []
return services
def __map_to_service(self, rows):
map = dict()
for row in rows:
service_id = row['service_row_id']
if service_id not in map:
map[service_id] = list()
map[service_id].append(row)
return map
def __get_group_with_endpoints(self, groups):
segregated_groups = dict()
for group in groups:
group_name = group['group_name']
if group_name not in segregated_groups:
group_details = dict()
group_details['endpoints'] = list()
segregated_groups[group_name] = group_details
else:
group_details = segregated_groups[group_name]
group_details['payment_address'] = group['payment_address']
group_details['group_id'] = group['group_id']
group_details['endpoints'].append(group['endpoint'])
return segregated_groups
