def _srch_qry_dta(self, sub_qry, sort_by, order_by, offset, limit): try: srch_qry = "SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM service_metadata M " \ "LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id WHERE " \ + sub_qry.replace('%', '%%') + " GROUP BY M.org_id, M.service_id ORDER BY %s %s LIMIT %s , %s" qry_dta = self.repo.execute(srch_qry, [sort_by, order_by, int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id),) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[rec['org_id']] = {'service_id': service_id, 'tags': tags } qry_part = " AND (S.org_id, S.service_id) IN " + str(org_srvc_tuple).replace(',)', ')') services = self.repo.execute("SELECT M.* FROM service_metadata M, service S WHERE " "S.row_id = M.service_row_id " + qry_part) obj_utils = Utils() obj_utils.clean(services) votes_count = self.fetch_total_count() count = 0 for rec in services: services[count].update({"votes":votes_count[rec["org_id"]][rec["service_id"]]}) services[count].update({"tags":rslt[rec["org_id"]]["tags"]}) count = count + 1 return services except Exception as err: raise err
def _search_query_data(self, sub_qry, sort_by, order_by, offset, limit, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query srch_qry = "SELECT * FROM service A, (SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id " \ "LEFT JOIN service_endpoint E ON M.service_row_id = E.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + \ " GROUP BY M.org_id, M.service_id ORDER BY E.is_available DESC, " + sort_by + " " + order_by + \ " ) B WHERE A.service_id = B.service_id AND A.org_id=B.org_id AND A.is_curated= 1 LIMIT %s , %s" qry_dta = self.repo.execute(srch_qry, values + [int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id), ) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[org_id][service_id] = {} rslt[org_id][service_id]["tags"] = tags qry_part = " AND (S.org_id, S.service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') print("qry_part::", qry_part) sort_by = sort_by.replace("org_id", "M.org_id") services = self.repo.execute( "SELECT M.*,O.organization_name,O.org_assets_url FROM service_endpoint E, service_metadata M, service S " ", organization O WHERE O.org_id = S.org_id AND S.row_id = M.service_row_id AND " "S.row_id = E.service_row_id " + qry_part + "ORDER BY E.is_available DESC, " + sort_by + " " + order_by) obj_utils = Utils() obj_utils.clean(services) available_service = self._get_is_available_service() for rec in services: self._convert_service_metadata_str_to_json(rec) org_id = rec["org_id"] service_id = rec["service_id"] tags = [] is_available = 0 if rslt.get(org_id, {}).get(service_id, {}).get("tags", None) is not None: tags = rslt[org_id][service_id]["tags"].split(",") if (org_id, service_id) in available_service: is_available = 1 rec.update({"tags": tags}) rec.update({"is_available": is_available}) return services except Exception as err: raise err
def get_org_details(self, org_id): """ Method to get org details for given org_id. """ try: org_details = self.repo.execute( "SELECT * from organization o, (select org_id, count(*) as service_count " "from service where org_id = %s) s where o.org_id = %s and o.org_id = s.org_id", [org_id, org_id]) obj_utils = Utils() obj_utils.clean(org_details) if len(org_details) > 0: members = self._get_all_members(org_id) org_details[0]["members"] = members return org_details except Exception as e: print(repr(e)) raise e
class Registry: def __init__(self, obj_repo): self.repo = obj_repo self.obj_utils = Utils() @staticmethod def service_build_status_notifier(org_id, service_id, build_status): is_curated = False if build_status == BUILD_CODE['SUCCESS']: is_curated = True service = new_service_repo.get_service(org_id=org_id, service_id=service_id) if service: service.is_curated = is_curated new_service_repo.create_or_update_service(service=service) else: raise Exception(f"Unable to find service for service_id {service_id} and org_id {org_id}") demo_build_status = BuildStatus.SUCCESS if build_status == BUILD_CODE['SUCCESS'] else BuildStatus.FAILED offchain_attributes = OffchainServiceAttribute( org_id, service_id, {"demo_component_status": demo_build_status} ) OffchainServiceConfigRepository().save_offchain_service_attribute(offchain_attributes) def _get_all_service(self): """ Method to generate org_id and service mapping.""" try: all_orgs_srvcs_raw = self.repo.execute( "SELECT O.org_id, O.organization_name,O.org_assets_url, O.owner_address, S.service_id FROM service S, " "organization O WHERE S.org_id = O.org_id AND S.is_curated = 1") all_orgs_srvcs = {} for rec in all_orgs_srvcs_raw: if rec['org_id'] not in all_orgs_srvcs.keys(): all_orgs_srvcs[rec['org_id']] = {'service_id': [], 'organization_name': rec["organization_name"], 'owner_address': rec['owner_address']} all_orgs_srvcs[rec['org_id']]['service_id'].append( rec['service_id']) return all_orgs_srvcs except Exception as e: print(repr(e)) raise e def _get_all_members(self, org_id=None): """ Method to generate org_id and members mapping.""" try: query = "SELECT org_id, `member` FROM members M" params = None if org_id is not None: query += " where M.org_id = %s" params = [org_id] all_orgs_members_raw = self.repo.execute(query, params) all_orgs_members = defaultdict(list) for rec in all_orgs_members_raw: all_orgs_members[rec['org_id']].append(rec['member']) return all_orgs_members except Exception as e: print(repr(e)) raise e def get_all_org(self): """ Method to get list and high level details of all organization.""" try: all_orgs_srvcs = self._get_all_service() all_orgs_members = self._get_all_members() all_orgs_data = [] for org_rec in all_orgs_srvcs: data = {"org_id": org_rec, "org_name": all_orgs_srvcs[org_rec]["organization_name"], "owner_address": all_orgs_srvcs[org_rec]['owner_address'], "service_id": all_orgs_srvcs[org_rec]['service_id'], "members": all_orgs_members.get(org_rec, [])} all_orgs_data.append(data) return all_orgs_data except Exception as e: print(repr(e)) raise e def _prepare_subquery(self, s, q, fm): try: sub_qry = "" if s == "all": for rec in fm: sub_qry += fm[rec] + " LIKE '%" + str(q) + "%' OR " sub_qry = sub_qry[:-3] if sub_qry.endswith("OR ") else sub_qry else: sub_qry += fm[s] + " LIKE '%" + str(q) + "%' " return sub_qry.replace("org_id", "M.org_id") except Exception as err: raise err def _get_total_count(self, sub_qry, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query search_count_query = "SELECT count(*) as search_count FROM service A, (SELECT DISTINCT M.org_id, M.service_id FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + ") B WHERE A.service_id = B.service_id " \ "AND A.org_id = B.org_id AND A.is_curated = 1 " res = self.repo.execute(search_count_query, values) return res[0].get("search_count", 0) except Exception as err: raise err def _convert_service_metadata_str_to_json(self, record): record["service_rating"] = json.loads(record["service_rating"]) record["org_assets_url"] = json.loads(record["org_assets_url"]) record["contributors"] = json.loads(record.get("contributors", "[]")) record["contacts"] = json.loads(record.get("contacts", "[]")) if record["contacts"] is None: record["contacts"] = [] if record["contributors"] is None: record["contributors"] = [] def _search_query_data(self, sub_qry, sort_by, order_by, offset, limit, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query srch_qry = "SELECT * FROM service A, (SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id " \ "LEFT JOIN service_endpoint E ON M.service_row_id = E.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + \ " GROUP BY M.org_id, M.service_id ORDER BY E.is_available DESC, " + sort_by + " " + order_by + \ " ) B WHERE A.service_id = B.service_id AND A.org_id=B.org_id AND A.is_curated= 1 LIMIT %s , %s" qry_dta = self.repo.execute( srch_qry, values + [int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id),) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[org_id][service_id] = {} rslt[org_id][service_id]["tags"] = tags qry_part = " AND (S.org_id, S.service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') qry_part_where = " AND (org_id, service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') print("qry_part::", qry_part) sort_by = sort_by.replace("org_id", "M.org_id") if org_srvc_tuple: services = self.repo.execute( "SELECT DISTINCT M.row_id, M.service_row_id, M.org_id, M.service_id, M.display_name, M.description, M.url, M.json, M.model_ipfs_hash, M.encoding, M.`type`," " M.mpe_address,M.service_rating, M.ranking, M.contributors, M.short_description," "O.organization_name,O.org_assets_url FROM service_endpoint E, service_metadata M, service S " ", organization O WHERE O.org_id = S.org_id AND S.row_id = M.service_row_id AND " "S.row_id = E.service_row_id " + qry_part + "ORDER BY E.is_available DESC, " + sort_by + " " + order_by) services_media = self.repo.execute( "select org_id ,service_id,file_type ,asset_type,url,alt_text ,`order`,row_id from service_media where asset_type = 'hero_image' " + qry_part_where) else: services = [] services_media = [] obj_utils = Utils() obj_utils.clean(services) available_service = self._get_is_available_service() for rec in services: self._convert_service_metadata_str_to_json(rec) org_id = rec["org_id"] service_id = rec["service_id"] tags = [] is_available = 0 if rslt.get(org_id, {}).get(service_id, {}).get("tags", None) is not None: tags = rslt[org_id][service_id]["tags"].split(",") if (org_id, service_id) in available_service: is_available = 1 asset_media = [] if len(services_media) > 0: asset_media = [x for x in services_media if x['service_id'] == service_id] if len(asset_media) > 0: asset_media = asset_media[0] rec.update({"tags": tags}) rec.update({"is_available": is_available}) rec.update({"media": asset_media}) return services except Exception as err: raise err def _search_response_format(self, tc, offset, limit, rslt): try: return {"total_count": tc, "offset": offset, "limit": limit, "result": rslt} except Exception as err: raise err def get_all_srvcs(self, qry_param): try: fields_mapping = {"display_name": "display_name", "tag_name": "tag_name", "org_id": "org_id"} s = qry_param.get('s', 'all') q = qry_param.get('q', '') offset = qry_param.get('offset', GET_ALL_SERVICE_OFFSET_LIMIT) limit = qry_param.get('limit', GET_ALL_SERVICE_LIMIT) sort_by = fields_mapping.get( qry_param.get('sort_by', None), "ranking") order_by = qry_param.get('order_by', 'desc') if order_by.lower() != "desc": order_by = "asc" sub_qry = self._prepare_subquery(s=s, q=q, fm=fields_mapping) print("get_all_srvcs::sub_qry: ", sub_qry) filter_qry = "" if qry_param.get("filters", None) is not None: filter_query, values = self._filters_to_query( qry_param.get("filters")) print("get_all_srvcs::filter_query: ", filter_query, "|values: ", values) total_count = self._get_total_count( sub_qry=sub_qry, filter_query=filter_query, values=values) if total_count == 0: return self._search_response_format(total_count, offset, limit, []) q_dta = self._search_query_data(sub_qry=sub_qry, sort_by=sort_by, order_by=order_by, offset=offset, limit=limit, filter_query=filter_query, values=values) return self._search_response_format(total_count, offset, limit, q_dta) except Exception as e: print(repr(e)) raise e def get_group_info(self, org_id, service_id): try: group_data = self.repo.execute( "SELECT G.*, E.* FROM service_group G INNER JOIN service_endpoint E ON G.group_id = E.group_id AND G.service_row_id = E.service_row_id WHERE " "G.org_id = %s AND G.service_id = %s ", [org_id, service_id]) self.obj_utils.clean(group_data) groups = {} for rec in group_data: group_id = rec['group_id'] if group_id not in groups.keys(): groups = {group_id: {"group_id": rec['group_id'], "group_name": rec['group_name'], "pricing": json.loads(rec['pricing']), "endpoints": [], "free_calls": rec.get("free_calls", 0), "free_call_signer_address": rec.get("free_call_signer_address", "") } } groups[group_id]['endpoints'].append({"endpoint": rec['endpoint'], "is_available": rec['is_available'], "last_check_timestamp": rec["last_check_timestamp"]}) return list(groups.values()) except Exception as e: print(repr(e)) raise e def _get_is_available_service(self): try: available_service = [] srvc_st_dta = self.repo.execute( "SELECT DISTINCT org_id, service_id FROM service_endpoint WHERE is_available = 1") for rec in srvc_st_dta: available_service.append((rec['org_id'], rec['service_id']), ) return available_service except Exception as err: print(repr(err)) raise err def _filter_condition_to_query(self, filter_condition): value = [] if filter_condition.attr in ["org_id", "service_id"]: filter_condition.attr = "M." + filter_condition.attr if filter_condition.operator == "=": value = filter_condition.value return '%s %s"%s"' % (filter_condition.attr, filter_condition.operator, "%s"), value if filter_condition.operator == "IN": value = filter_condition.value return '%s %s %s' % ( filter_condition.attr, filter_condition.operator, "(" + (("%s,") * len(value))[:-1] + ")"), value if filter_condition.operator == "BETWEEN": value = filter_condition.value return '%s %s %s AND %s' % (filter_condition.attr, filter_condition.operator, "%s", "%s"), value def _filters_to_query(self, filter_json): query = "" filters = [] values = [] for filter in filter_json: filters.append(Filter(filter)) for filter in filters: query += "(" for filter_condition in filter.get_filter().get("filter"): sub_query, value = self._filter_condition_to_query( filter_condition) values += value query = query + "(" + sub_query + ") AND " if query.endswith(" AND "): query = query[:-5] query += ") OR " if query.endswith(" OR "): query = query[:-4] return query, values def get_filter_attribute(self, attribute): """ Method to fetch filter metadata based on attribute.""" try: filter_attribute = {"attribute": attribute, "values": []} if attribute == "tag_name": filter_data = self.repo.execute( "SELECT DISTINCT tag_name AS 'key', tag_name AS 'value' FROM service_tags T, service S " "WHERE S.row_id = T.service_row_id AND S.is_curated = 1") elif attribute == "display_name": filter_data = self.repo.execute( "SELECT DISTINCT S.service_id AS 'key',display_name AS 'value' FROM service_metadata M, service S " "WHERE S.row_id = M.service_row_id AND S.is_curated = 1") elif attribute == "org_id": filter_data = self.repo.execute( "SELECT DISTINCT O.org_id AS 'key' ,O.organization_name AS 'value' from organization O, service S " "WHERE S.org_id = O.org_id AND S.is_curated = 1") else: return filter_attribute for rec in filter_data: filter_attribute["values"].append(rec) return filter_attribute except Exception as e: print(repr(e)) raise e def get_all_group_for_org_id(self, org_id): """ Method to get all group data for given org_id. This includes group data at org level""" try: groups_data = self.repo.execute( "SELECT group_id, group_name, payment FROM org_group WHERE org_id = %s", [org_id]) [group_record.update({'payment': json.loads(group_record['payment'])}) for group_record in groups_data] groups = {"org_id": org_id, "groups": groups_data} return groups except Exception as e: print(repr(e)) raise e def get_group_details_for_org_id(self, org_id, group_id): """ Method to get group data for given org_id and group_id. This includes group data at org level""" group_data = self.repo.execute( "SELECT group_id, group_name, payment , org_id FROM org_group WHERE org_id = %s and group_id = %s", [org_id, group_id] ) [group_record.update({'payment': json.loads(group_record['payment'])}) for group_record in group_data] return {"groups": group_data} def get_service_data_by_org_id_and_service_id(self, org_id, service_id): try: """ Method to get all service data for given org_id and service_id""" tags = [] org_groups_dict = {} basic_service_data = self.repo.execute( "SELECT M.row_id, M.service_row_id, M.org_id, M.service_id, M.display_name, M.description, M.url, M.json, M.model_ipfs_hash, M.encoding, M.`type`," " M.mpe_address,M.service_rating, M.ranking, M.contributors, M.short_description, M.demo_component_available," " S.*, O.org_id, O.organization_name, O.owner_address, O.org_metadata_uri, O.org_email, " "O.org_assets_url, O.description as org_description, O.contacts " "FROM service_metadata M, service S, organization O " "WHERE O.org_id = S.org_id AND S.row_id = M.service_row_id AND S.org_id = %s " "AND S.service_id = %s AND S.is_curated = 1", [org_id, service_id]) if len(basic_service_data) == 0: return [] self.obj_utils.clean(basic_service_data) org_group_data = self.repo.execute( "SELECT * FROM org_group WHERE org_id = %s", [org_id]) self.obj_utils.clean(org_group_data) service_group_data = self.get_group_info( org_id=org_id, service_id=service_id) tags = self.repo.execute("SELECT tag_name FROM service_tags WHERE org_id = %s AND service_id = %s", [org_id, service_id]) service_media = service_media_repo.get_service_media(org_id=org_id, service_id=service_id) media = [media_data.to_dict() for media_data in service_media] result = basic_service_data[0] self._convert_service_metadata_str_to_json(result) offchain_service_configs = Registry.prepare_offchain_service_attributes(org_id, service_id) for rec in org_group_data: org_groups_dict[rec['group_id']] = { "payment": json.loads(rec["payment"])} is_available = 0 # Hard Coded Free calls in group data for rec in service_group_data: rec["free_calls"] = rec.get("free_calls", 0) if is_available == 0: endpoints = rec['endpoints'] for endpoint in endpoints: is_available = endpoint['is_available'] if is_available == 1: break rec.update(org_groups_dict.get(rec['group_id'], {})) result.update({"is_available": is_available, "groups": service_group_data, "tags": tags, "media": media}) result.update(offchain_service_configs) return result except Exception as e: print(repr(e)) raise e @staticmethod def prepare_offchain_service_attributes(org_id, service_id): offchain_attributes = {} offchain_service_config_repo = OffchainServiceConfigRepository() offchain_service_config = offchain_service_config_repo.get_offchain_service_config( org_id=org_id, service_id=service_id ) offchain_attributes_db = offchain_service_config.attributes demo_component_required = offchain_attributes_db.get("demo_component_required", 0) demo_component = service_factory.create_demo_component_domain_model(offchain_service_config.attributes) demo_component = demo_component.to_dict() # prepare format demo_last_modified = offchain_attributes_db.get("demo_component_last_modified", "") demo_component.update({"demo_component_last_modified": demo_last_modified if demo_last_modified else ""}) offchain_attributes.update({"demo_component_required": demo_component_required}) offchain_attributes.update({"demo_component": demo_component}) return offchain_attributes def get_org_details(self, org_id): """ Method to get org details for given org_id. """ try: org_details = self.repo.execute( "SELECT * from organization o, (select org_id, count(*) as service_count " "from service where org_id = %s) s where o.org_id = %s and o.org_id = s.org_id", [org_id, org_id]) obj_utils = Utils() obj_utils.clean(org_details) if len(org_details) > 0: members = self._get_all_members(org_id) org_details[0]["members"] = members return org_details except Exception as e: print(repr(e)) raise e def update_service_rating(self, org_id, service_id): """ Method updates service_rating and total_user_rated when user rating is changed for given service_id and org_id. """ try: update_service_metadata = self.repo.execute( "UPDATE service_metadata A INNER JOIN " "(SELECT U.org_id, U.service_id, AVG(U.rating) AS service_rating, count(*) AS total_users_rated " "FROM user_service_vote AS U WHERE U.rating IS NOT NULL GROUP BY U.service_id, U.org_id ) AS B " "ON A.org_id=B.org_id AND A.service_id=B.service_id SET A.service_rating " "= CONCAT('{\"rating\":', B.service_rating, ' , \"total_users_rated\":', B.total_users_rated, '}') " "WHERE A.org_id = %s AND A.service_id = %s ", [org_id, service_id]) return "success" except Exception as e: print(repr(e)) raise e def curate_service(self, org_id, service_id, curated): service_repo = ServiceRepository(self.repo) if str(curated).lower() == "true": service_repo.curate_service(org_id, service_id, 1) elif str(curated).lower() == "false": service_repo.curate_service(org_id, service_id, 0) else: Exception("Invalid curation flag")
def _search_query_data(self, sub_qry, sort_by, order_by, offset, limit, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query srch_qry = "SELECT * FROM service A, (SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id " \ "LEFT JOIN service_endpoint E ON M.service_row_id = E.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + \ " GROUP BY M.org_id, M.service_id ORDER BY E.is_available DESC, " + sort_by + " " + order_by + \ " ) B WHERE A.service_id = B.service_id AND A.org_id=B.org_id AND A.is_curated= 1 LIMIT %s , %s" qry_dta = self.repo.execute( srch_qry, values + [int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id),) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[org_id][service_id] = {} rslt[org_id][service_id]["tags"] = tags qry_part = " AND (S.org_id, S.service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') qry_part_where = " AND (org_id, service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') print("qry_part::", qry_part) sort_by = sort_by.replace("org_id", "M.org_id") if org_srvc_tuple: services = self.repo.execute( "SELECT DISTINCT M.row_id, M.service_row_id, M.org_id, M.service_id, M.display_name, M.description, M.url, M.json, M.model_ipfs_hash, M.encoding, M.`type`," " M.mpe_address,M.service_rating, M.ranking, M.contributors, M.short_description," "O.organization_name,O.org_assets_url FROM service_endpoint E, service_metadata M, service S " ", organization O WHERE O.org_id = S.org_id AND S.row_id = M.service_row_id AND " "S.row_id = E.service_row_id " + qry_part + "ORDER BY E.is_available DESC, " + sort_by + " " + order_by) services_media = self.repo.execute( "select org_id ,service_id,file_type ,asset_type,url,alt_text ,`order`,row_id from service_media where asset_type = 'hero_image' " + qry_part_where) else: services = [] services_media = [] obj_utils = Utils() obj_utils.clean(services) available_service = self._get_is_available_service() for rec in services: self._convert_service_metadata_str_to_json(rec) org_id = rec["org_id"] service_id = rec["service_id"] tags = [] is_available = 0 if rslt.get(org_id, {}).get(service_id, {}).get("tags", None) is not None: tags = rslt[org_id][service_id]["tags"].split(",") if (org_id, service_id) in available_service: is_available = 1 asset_media = [] if len(services_media) > 0: asset_media = [x for x in services_media if x['service_id'] == service_id] if len(asset_media) > 0: asset_media = asset_media[0] rec.update({"tags": tags}) rec.update({"is_available": is_available}) rec.update({"media": asset_media}) return services except Exception as err: raise err
class MPE: def __init__(self, net_id, obj_repo): self.repo = obj_repo self.objSrvc = Registry(obj_repo=obj_repo) self.ws_provider = NETWORKS[net_id]['ws_provider'] self.obj_util = Utils() def get_latest_block_no(self): w3Obj = Web3(web3.providers.WebsocketProvider(self.ws_provider)) return w3Obj.eth.blockNumber def get_channels_by_user_address(self, user_address, org_id=None, service_id=None): try: print('Inside get_channel_info::user_address', user_address, '|', org_id, '|', service_id) last_block_no = self.get_latest_block_no() params = [last_block_no] sub_qry = "" if user_address is not None and service_id is not None and org_id is not None: sub_qry = " AND S.org_id = %s AND S.service_id = %s " params.append(org_id) params.append(service_id) params.append(user_address) raw_channel_dta = self.repo.execute( 'SELECT C.*, E.*, IF(C.expiration > %s, "active","inactive") AS status FROM ' 'service_group G, mpe_channel C, service_endpoint E, service S WHERE G.group_id = C.groupId AND ' 'G.group_id = E.group_id and S.row_id = E.service_row_id and E.service_row_id = G.service_row_id ' 'AND S.is_curated = 1 ' + sub_qry + ' AND C.sender = %s ORDER BY C.expiration DESC', params) self.obj_util.clean(raw_channel_dta) channel_dta = {} for rec in raw_channel_dta: group_id = rec['groupId'] if group_id not in channel_dta.keys(): channel_dta[group_id] = { 'group_id': group_id, 'org_id': rec['org_id'], 'service_id': rec['service_id'], 'group': { "endpoints": [] }, 'channels': [] } channel = { 'channel_id': rec['channel_id'], 'recipient': rec['recipient'], 'balance_in_cogs': rec['balance_in_cogs'], 'pending': rec['pending'], 'nonce': rec['nonce'], 'expiration': rec['expiration'], 'signer': rec['signer'], 'status': rec['status'] } endpoint = { 'endpoint': rec['endpoint'], 'is_available': rec['endpoint'], 'last_check_timestamp': rec['last_check_timestamp'] } channel_dta[group_id]['group']['endpoints'].append(endpoint) channel_dta[group_id]['channels'].append(channel) return list(channel_dta.values()) except Exception as e: print(repr(e)) raise e
class WalletService: def __init__(self, repo): self.repo = repo self.boto_utils = BotoUtils(region_name=REGION_NAME) self.blockchain_util = BlockChainUtil( provider_type="HTTP_PROVIDER", provider=NETWORKS[NETWORK_ID]['http_provider']) self.utils = Utils() self.channel_dao = ChannelDAO(repo=self.repo) self.wallet_dao = WalletDAO(repo=self.repo) def create_and_register_wallet(self, username): address, private_key = self.blockchain_util.create_account() wallet = Wallet(address=address, private_key=private_key, type=GENERAL_WALLET_TYPE, status=WalletStatus.ACTIVE.value) self._register_wallet(wallet, username) return wallet.to_dict() def _register_wallet(self, wallet, username): existing_wallet = self.wallet_dao.get_wallet_details(wallet) if len(existing_wallet) == 0: self.wallet_dao.insert_wallet(wallet) self.wallet_dao.add_user_for_wallet(wallet, username) def register_wallet(self, wallet_address, wallet_type, status, username): wallet = Wallet(address=wallet_address, type=wallet_type, status=status) self._register_wallet(wallet, username) return wallet.to_dict() def remove_user_wallet(self, username): self.wallet_dao.remove_user_wallet(username) def get_wallet_details(self, username): """ Method to get wallet details for a given username. """ logger.info(f"Fetching wallet details for {username}") wallet_data = self.wallet_dao.get_wallet_data_by_username(username) self.utils.clean(wallet_data) logger.info( f"Fetched {len(wallet_data)} wallets for username: {username}") wallet_response = {"username": username, "wallets": wallet_data} return wallet_response def __generate_signature_details(self, recipient, group_id, agi_tokens, expiration, message_nonce, signer_key): data_types = [ "string", "address", "address", "address", "address", "bytes32", "uint256", "uint256", "uint256" ] values = [ "__openChannelByThirdParty", self.mpe_address, self.EXECUTOR_WALLET_ADDRESS, SIGNER_ADDRESS, recipient, group_id, agi_tokens, expiration, message_nonce ] signature = self.blockchain_util.generate_signature( data_types=data_types, values=values, signer_key=signer_key) v, r, s = Web3.toInt( hexstr="0x" + signature[-2:]), signature[:66], "0x" + signature[66:130] return r, s, v, signature def __calculate_amount_in_cogs(self, amount, currency): if currency == "USD": amount_in_cogs = round(amount) else: raise Exception("Currency %s not supported.", currency) return amount_in_cogs def record_create_channel_event(self, payload): current_time = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S") if not self.channel_dao.persist_create_channel_event( payload, current_time): raise Exception("Failed to create record") return {} def open_channel_by_third_party(self, order_id, sender, signature, r, s, v, group_id, org_id, amount, currency, recipient, current_block_no, amount_in_cogs): self.EXECUTOR_WALLET_ADDRESS = self.boto_utils.get_ssm_parameter( EXECUTOR_ADDRESS) self.EXECUTOR_WALLET_KEY = self.boto_utils.get_ssm_parameter( EXECUTOR_KEY) method_name = "openChannelByThirdParty" self.mpe_address = self.blockchain_util.read_contract_address( net_id=NETWORK_ID, path=MPE_ADDR_PATH, key='address') # 1 block no is mined in 15 sec on average, setting expiration as 10 years expiration = current_block_no + (10 * 365 * 24 * 60 * 4) # amount_in_cogs = self.__calculate_amount_in_cogs(amount=amount, currency=currency) self.__validate__cogs(amount_in_cogs=amount_in_cogs) group_id_in_hex = "0x" + base64.b64decode(group_id).hex() positional_inputs = (sender, SIGNER_ADDRESS, recipient, group_id_in_hex, amount_in_cogs, expiration, current_block_no, v, r, s) transaction_object = self.blockchain_util.create_transaction_object( *positional_inputs, method_name=method_name, address=self.EXECUTOR_WALLET_ADDRESS, contract_path=MPE_CNTRCT_PATH, contract_address_path=MPE_ADDR_PATH, net_id=NETWORK_ID) raw_transaction = self.blockchain_util.sign_transaction_with_private_key( transaction_object=transaction_object, private_key=self.EXECUTOR_WALLET_KEY) transaction_hash = self.blockchain_util.process_raw_transaction( raw_transaction=raw_transaction) logger.info( "openChannelByThirdParty::transaction_hash : %s for order_id : %s", transaction_hash, order_id) self.channel_dao.insert_channel_history( order_id=order_id, amount=amount, currency=currency, group_id=group_id, org_id=org_id, type=method_name, recipient=recipient, address=sender, signature=signature, request_parameters=str(positional_inputs), transaction_hash=transaction_hash, status=TransactionStatus.PENDING) return { "transaction_hash": transaction_hash, "signature": signature, "amount_in_cogs": amount_in_cogs, "type": method_name } def set_default_wallet(self, username, address): self.wallet_dao.set_default_wallet(username=username, address=address) return "OK" def add_funds_to_channel(self, org_id, group_id, channel_id, sender, recipient, order_id, amount, currency, amount_in_cogs): self.EXECUTOR_WALLET_ADDRESS = self.boto_utils.get_ssm_parameter( EXECUTOR_ADDRESS) self.EXECUTOR_WALLET_KEY = self.boto_utils.get_ssm_parameter( EXECUTOR_KEY) method_name = "channelAddFunds" # amount_in_cogs = self.__calculate_amount_in_cogs(amount=amount, currency=currency) self.__validate__cogs(amount_in_cogs=amount_in_cogs) positional_inputs = (channel_id, amount_in_cogs) transaction_object = self.blockchain_util.create_transaction_object( *positional_inputs, method_name=method_name, address=self.EXECUTOR_WALLET_ADDRESS, contract_path=MPE_CNTRCT_PATH, contract_address_path=MPE_ADDR_PATH, net_id=NETWORK_ID) raw_transaction = self.blockchain_util.sign_transaction_with_private_key( transaction_object=transaction_object, private_key=self.EXECUTOR_WALLET_KEY) transaction_hash = self.blockchain_util.process_raw_transaction( raw_transaction=raw_transaction) logger.info("channelAddFunds::transaction_hash: %s for order_id: %s", transaction_hash, order_id) self.channel_dao.insert_channel_history( order_id=order_id, amount=amount, currency=currency, group_id=group_id, org_id=org_id, type=method_name, recipient=recipient, address=sender, signature=None, request_parameters=str(positional_inputs), transaction_hash=transaction_hash, status=TransactionStatus.PENDING) return { "transaction_hash": transaction_hash, "amount_in_cogs": amount_in_cogs, "type": method_name } def get_transactions_from_username_recipient(self, username, org_id, group_id): logger.info( f"Fetching transactions for {username} to org_id: {org_id} group_id: {org_id}" ) channel_data = self.channel_dao.get_channel_transactions_for_username_recipient( username=username, group_id=group_id, org_id=org_id) self.utils.clean(channel_data) logger.info(f"Fetched {len(channel_data)} transactions") transaction_details = {"username": username, "wallets": []} wallet_transactions = dict() for rec in channel_data: sender_address = rec["address"] if rec["address"] not in wallet_transactions: wallet_transactions[rec["address"]] = { "address": sender_address, "is_default": rec["is_default"], "type": rec["type"], "transactions": [] } if rec['recipient'] is None: continue transaction = { "org_id": org_id, "group_id": group_id, "recipient": rec["recipient"], "amount": rec["amount"], "transaction_type": rec["transaction_type"], "currency": rec["currency"], "status": rec["status"], "created_at": rec["created_at"], } wallet_transactions[sender_address]["transactions"].append( transaction) for key in wallet_transactions: wallet = wallet_transactions[key] transaction_details["wallets"].append(wallet) return transaction_details def get_channel_transactions_against_order_id(self, order_id): transaction_history = self.channel_dao.get_channel_transactions_against_order_id( order_id) for record in transaction_history: record["created_at"] = record["created_at"].strftime( "%Y-%m-%d %H:%M:%S") return {"order_id": order_id, "transactions": transaction_history} def __validate__cogs(self, amount_in_cogs): if amount_in_cogs < MINIMUM_AMOUNT_IN_COGS_ALLOWED: raise Exception( "Insufficient amount to buy minimum amount in cogs allowed.")
class Registry: def __init__(self, obj_repo): self.repo = obj_repo self.obj_utils = Utils() def _get_all_service(self): try: all_orgs_srvcs_raw = self.repo.execute("SELECT O.org_id, O.owner_address, S.service_id FROM service S, " "organization O WHERE S.org_id = O.org_id AND S.is_curated = 1") all_orgs_srvcs = {} for rec in all_orgs_srvcs_raw: if rec['org_id'] not in all_orgs_srvcs.keys(): all_orgs_srvcs[rec['org_id']] = {'service_id': [], 'owner_address': rec['owner_address']} all_orgs_srvcs[rec['org_id']]['service_id'].append(rec['service_id']) return all_orgs_srvcs except Exception as e: print(repr(e)) raise e def _get_all_members(self): try: all_orgs_members_raw = self.repo.execute("SELECT org_id, member FROM members M") all_orgs_members = defaultdict(list) for rec in all_orgs_members_raw: all_orgs_members[rec['org_id']].append(rec['member']) print(all_orgs_members) return all_orgs_members except Exception as e: print(repr(e)) raise e def get_all_org(self): try: all_orgs_srvcs = self._get_all_service() all_orgs_members = self._get_all_members() all_orgs_data = [] for rec in all_orgs_srvcs: data = {"org_id": rec, "owner_address": all_orgs_srvcs[rec]['owner_address'], "service_id": all_orgs_srvcs[rec]['service_id'], "members": all_orgs_members.get(rec, [])} all_orgs_data.append(data) return all_orgs_data except Exception as e: print(repr(e)) raise e def _prepare_subquery(self, s, q, fm): try: sub_qry = "" if s == "all": for rec in fm: sub_qry += fm[rec] + " LIKE '" + str(q) + "%' OR " sub_qry = sub_qry[:-3] else: sub_qry += fm[s] + " LIKE '" + str(q) + "%' " return sub_qry.replace("org_id", "M.org_id") except Exception as err: raise err def _get_total_count(self, sub_qry): try: count_qry = "SELECT DISTINCT M.org_id, M.service_id FROM service_metadata M INNER JOIN service_tags T ON " \ "M.service_row_id = T.service_row_id WHERE (" + sub_qry.replace('%', '%%') + ") "; res = self.repo.execute(count_qry) return len(res) except Exception as err: raise err def _srch_qry_dta(self, sub_qry, sort_by, order_by, offset, limit): try: srch_qry = "SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM service_metadata M " \ "LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id WHERE " \ + sub_qry.replace('%', '%%') + " GROUP BY M.org_id, M.service_id ORDER BY %s %s LIMIT %s , %s" qry_dta = self.repo.execute(srch_qry, [sort_by, order_by, int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id),) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[rec['org_id']] = {'service_id': service_id, 'tags': tags } qry_part = " AND (S.org_id, S.service_id) IN " + str(org_srvc_tuple).replace(',)', ')') services = self.repo.execute("SELECT M.* FROM service_metadata M, service S WHERE " "S.row_id = M.service_row_id " + qry_part) obj_utils = Utils() obj_utils.clean(services) votes_count = self.fetch_total_count() count = 0 for rec in services: services[count].update({"votes":votes_count[rec["org_id"]][rec["service_id"]]}) services[count].update({"tags":rslt[rec["org_id"]]["tags"]}) count = count + 1 return services except Exception as err: raise err def _srch_resp_format(self, tc, offset, limit, rslt): try: return {"total_count": tc, "offset": offset, "limit": limit, "result": rslt} except Exception as err: raise err def get_all_srvcs(self, qry_param): try: fields_mapping = {"dn": "display_name", "tg": "tag_name", "org": "org_id"} s = qry_param.get('s', 'all') q = qry_param.get('q', '') offset = qry_param.get('offset', 0) limit = qry_param.get('limit', 15) sort_by = fields_mapping.get(qry_param.get('sort_by', None), "display_name") order_by = qry_param.get('order_by', 'asc') sub_qry = self._prepare_subquery(s=s, q=q, fm=fields_mapping) total_count = self._get_total_count(sub_qry=sub_qry) if total_count == 0: return self._srch_resp_format(total_count, offset, limit, []) q_dta = self._srch_qry_dta(sub_qry=sub_qry, sort_by=sort_by, order_by=order_by, offset=offset, limit=limit) return self._srch_resp_format(total_count, offset, limit, q_dta) except Exception as e: print(repr(e)) raise e def get_group_info(self, org_id, service_id): try: grp_info_raw = self.repo.execute( "SELECT G.*, E.* FROM service_group G INNER JOIN service_endpoint E ON G.group_id = E.group_id WHERE " "G.org_id = %s AND G.service_id = %s ", [org_id, service_id]) self.obj_utils.clean(grp_info_raw) groups = {} for rec in grp_info_raw: group_id = rec['group_id'] if group_id not in groups.keys(): groups = {group_id: {"group_id": rec['group_id'], "group_name": rec['group_name'], "payment_address": rec['payment_address'], "endpoints": [] } } groups[group_id]['endpoints'].append({"endpoint": rec['endpoint'], "is_available": rec['is_available'], "last_check_timestamp": rec["last_check_timestamp"]}) return list(groups.values()) except Exception as e: print(repr(e)) raise e 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_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_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
class User: def __init__(self, obj_repo): self.repo = obj_repo self.obj_utils = Utils() self.ssm_client = boto3.client('ssm') self.boto_client = BotoUtils(region_name=REGION_NAME) def _set_user_data(self, user_data): """ Method to set user information. """ try: claims = user_data['authorizer']['claims'] email_verified = claims['email_verified'] status = 0 if email_verified: status = 1 else: raise Exception("Email verification is pending.") q_dta = [ claims['email'], user_data['accountId'], claims['nickname'], claims['email'], status, status, user_data['requestId'], user_data['requestTimeEpoch'], dt.utcnow(), dt.utcnow() ] set_usr_dta = self.repo.execute( "INSERT INTO user (username, account_id, name, email, email_verified, status, request_id, " "request_time_epoch, row_created, row_updated) " "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", q_dta) if len(set_usr_dta) > 0: return "success" else: return "User already exist" except Exception as e: print(repr(e)) raise e def _fetch_private_key_from_ssm(self, address): try: store = self.ssm_client.get_parameter(Name=PATH_PREFIX + str(address), WithDecryption=True) return store['Parameter']['Value'] except Exception as e: print(repr(e)) raise Exception("Error fetching value from parameter store.") def user_signup(self, user_data): """ Method to assign pre-seeded wallet to user. This is one time process. """ try: username = user_data['authorizer']['claims']['email'] set_user_data = self._set_user_data(user_data) print(set_user_data) return set_user_data except Exception as e: self.repo.rollback_transaction() print(repr(e)) raise e def get_user_profile(self, user_data): """ Method to fetch user profile data. """ try: username = user_data['authorizer']['claims']['email'] result = self.repo.execute( "SELECT * FROM user WHERE username = %s", [username]) self.obj_utils.clean(result) return {"success": "success", "data": result} except Exception as e: print(repr(e)) raise e def update_user_profile(self, email_alerts, is_terms_accepted, user_data): """ Method to update user profile data. """ try: username = user_data['authorizer']['claims']['email'] result = self.repo.execute( "UPDATE user SET email_alerts = %s, is_terms_accepted = %s WHERE username = %s", [ int(email_alerts is True), int(is_terms_accepted is True), username ]) return {"success": "success", "data": []} except Exception as e: print(repr(e)) raise e def validate_and_set_user_feedback(self, feedback_data, user_data): """ Method to validate and set user feedback data. """ schema = Schema([{ 'org_id': And(str), 'service_id': And(str), 'user_rating': And(str), 'comment': And(str) }]) try: feedback_data = schema.validate([feedback_data]) feedback_recorded = self._set_user_feedback(feedback_data[0], user_data=user_data) if feedback_recorded: return [] return None except Exception as err: print("Invalid Input ", err) return None def get_user_feedback(self, user_data, org_id, service_id): """ Method to get user feedback data. """ try: user_rating_dict = {} username = user_data['authorizer']['claims']['email'] query_part = "" query_part_values = [] if org_id is not None: query_part = "AND org_id = %s " query_part_values.append(org_id) if service_id is not None: query_part += "AND service_id = %s " query_part_values.append(service_id) rating_query = "SELECT * FROM user_service_vote WHERE username = %s " + query_part rating = self.repo.execute(rating_query, [username] + query_part_values) self.obj_utils.clean(rating) feedback_query = "SELECT * FROM user_service_feedback WHERE username = %s " + query_part feedback = self.repo.execute(feedback_query, [username] + query_part_values) self.obj_utils.clean(feedback) for record in feedback: org_id = record['org_id'] service_id = record['service_id'] if org_id not in user_rating_dict.keys(): user_rating_dict[org_id] = {} if service_id not in user_rating_dict.keys(): user_rating_dict[org_id][service_id] = {} user_rating_dict[org_id][service_id]['comment'] = [] user_rating_dict[org_id][service_id]['comment'].append( record['comment']) for record in rating: org_id = record['org_id'] service_id = record['service_id'] record.update({ 'comment': user_rating_dict.get(org_id, {}).get(service_id, {}).get("comment", []) }) return rating except Exception as e: print(repr(e)) raise e def _set_user_feedback(self, feedback_data, user_data): """ Method to set user rating and feedback. """ try: user_rating = str(feedback_data['user_rating']) if float(user_rating) > 5.0 or float(user_rating) < 1.0: raise Exception( "Invalid Rating. Provided user rating should be between 1.0 and 5.0 ." ) curr_dt = dt.utcnow() username = user_data['authorizer']['claims']['email'] org_id = feedback_data['org_id'] service_id = feedback_data['service_id'] comment = feedback_data['comment'] self.repo.begin_transaction() set_rating = "INSERT INTO user_service_vote (username, org_id, service_id, rating, row_updated, row_created) " \ "VALUES (%s, %s, %s, %s, %s, %s) " \ "ON DUPLICATE KEY UPDATE rating = %s, row_updated = %s" set_rating_params = [ username, org_id, service_id, user_rating, curr_dt, curr_dt, user_rating, curr_dt ] self.repo.execute(set_rating, set_rating_params) set_feedback = "INSERT INTO user_service_feedback (username, org_id, service_id, comment, row_updated, row_created)" \ "VALUES (%s, %s, %s, %s, %s, %s)" set_feedback_params = [ username, org_id, service_id, comment, curr_dt, curr_dt ] self.repo.execute(set_feedback, set_feedback_params) self._update_service_rating(org_id=org_id, service_id=service_id) self.repo.commit_transaction() return True except Exception as e: self.repo.rollback_transaction() print(repr(e)) raise e def _update_service_rating(self, org_id, service_id): """ Method updates service_rating and total_user_rated when user rating is changed for given service_id and org_id. """ try: update_service_metadata = self.repo.execute( "UPDATE service_metadata A INNER JOIN (SELECT U.org_id, U.service_id, AVG(U.rating) AS service_rating, " "count(*) AS total_users_rated FROM user_service_vote AS U WHERE U.rating IS NOT NULL GROUP BY " "U.service_id, U.org_id ) AS B ON A.org_id=B.org_id AND A.service_id=B.service_id SET A.service_rating " "= CONCAT('{\"rating\":', B.service_rating, ' , \"total_users_rated\":', B.total_users_rated, '}') " "WHERE A.org_id = %s AND A.service_id = %s ", [org_id, service_id]) except Exception as e: print(repr(e)) raise e
class MPE: def __init__(self, obj_repo): self.repo = obj_repo self.obj_util = Utils() self.blockchain_util = BlockChainUtil( provider_type="WS_PROVIDER", provider=NETWORKS[NETWORK_ID]["ws_provider"]) def get_channels(self, user_address, org_id=None, service_id=None, group_id=None): if user_address and org_id and group_id: return self.get_channels_by_user_address_org_group( user_address=user_address, org_id=org_id, group_id=group_id) elif user_address is not None: return self.get_channels_by_user_address(user_address, service_id, org_id) else: raise Exception("Invalid Request") def get_channels_by_user_address_v2(self, user_address): last_block_no = self.blockchain_util.get_current_block_no() logger.info(f"got block number {last_block_no}") channel_details_query = "SELECT mc.channel_id, mc.sender, mc.recipient, mc.groupId as group_id, " \ "mc.balance_in_cogs, mc.pending, mc.nonce, mc.consumed_balance, mc.expiration, " \ "mc.signer, og.org_id, " \ "org.organization_name, IF(mc.expiration > %s, 'active','inactive') AS status, " \ "og.group_name, org.org_assets_url " \ "FROM mpe_channel AS mc JOIN org_group AS og ON mc.groupId = og.group_id " \ "JOIN organization AS org ON og.org_id = org.org_id WHERE mc.sender = %s " params = [last_block_no, user_address] channel_details = self.repo.execute(channel_details_query, params) self.obj_util.clean(channel_details) channel_details_response = { "wallet_address": user_address, "organizations": self.segregate_org_channel_details(channel_details) } return channel_details_response def segregate_org_channel_details(self, raw_channel_data): org_data = {} for channel_record in raw_channel_data: org_id = channel_record["org_id"] group_id = channel_record["group_id"] if org_id not in org_data: org_data[org_id] = { "org_name": channel_record["organization_name"], "org_id": org_id, "hero_image": json.loads(channel_record["org_assets_url"]).get( "hero_image", ""), "groups": {} } if group_id not in org_data[org_id]["groups"]: org_data[org_id]["groups"][group_id] = { "group_id": group_id, "group_name": channel_record["group_name"], "channels": [] } channel = { "channel_id": channel_record["channel_id"], "recipient": channel_record["recipient"], 'balance_in_cogs': channel_record['balance_in_cogs'], 'consumed_balance': channel_record["consumed_balance"], 'current_balance': str( decimal.Decimal(channel_record['balance_in_cogs']) - decimal.Decimal(channel_record["consumed_balance"])), "pending": channel_record["pending"], "nonce": channel_record["nonce"], "expiration": channel_record["expiration"], "signer": channel_record["signer"], "status": channel_record["status"] } org_data[org_id]["groups"][group_id]["channels"].append(channel) for org_id in org_data: org_data[org_id]["groups"] = list( org_data[org_id]["groups"].values()) return list(org_data.values()) def get_channels_by_user_address(self, user_address, service_id, org_id): last_block_no = self.blockchain_util.get_current_block_no() params = [last_block_no] print('Inside get_channel_info::user_address', user_address, '|', org_id, '|', service_id) sub_qry = "" if user_address is not None and service_id is not None and org_id is not None: sub_qry = " AND S.org_id = %s AND S.service_id = %s " params.append(org_id) params.append(service_id) params.append(user_address) raw_channel_dta = self.repo.execute( 'SELECT C.*, E.*, IF(C.expiration > %s, "active","inactive") AS status FROM ' 'service_group G, mpe_channel C, service_endpoint E, service S WHERE G.group_id = C.groupId AND ' 'G.group_id = E.group_id and S.row_id = E.service_row_id and E.service_row_id = G.service_row_id ' 'AND S.is_curated = 1 ' + sub_qry + ' AND C.sender = %s ORDER BY C.expiration DESC', params) self.obj_util.clean(raw_channel_dta) channel_dta = {} for rec in raw_channel_dta: group_id = rec['groupId'] if group_id not in channel_dta.keys(): channel_dta[group_id] = { 'group_id': group_id, 'org_id': rec['org_id'], 'service_id': rec['service_id'], 'group': { "endpoints": [] }, 'channels': [] } channel = { 'channel_id': rec['channel_id'], 'recipient': rec['recipient'], 'balance_in_cogs': rec['balance_in_cogs'], 'consumed_balance': rec["consumed_balance"], 'current_balance': str( decimal.Decimal(rec['balance_in_cogs']) - decimal.Decimal(rec["consumed_balance"])), 'pending': rec['pending'], 'nonce': rec['nonce'], 'expiration': rec['expiration'], 'signer': rec['signer'], 'status': rec['status'] } endpoint = { 'endpoint': rec['endpoint'], 'is_available': rec['endpoint'], 'last_check_timestamp': rec['last_check_timestamp'] } channel_dta[group_id]['group']['endpoints'].append(endpoint) channel_dta[group_id]['channels'].append(channel) return list(channel_dta.values()) def get_channels_by_user_address_org_group(self, user_address, org_id=None, group_id=None): last_block_no = self.blockchain_util.get_current_block_no() params = [last_block_no, org_id, group_id, user_address] raw_channel_data = self.repo.execute( "SELECT C.* , OG.payment, OG.org_id, IF(C.expiration > %s, 'active','inactive') AS status FROM " "mpe_channel C JOIN org_group OG ON C.groupId = OG.group_id " "where OG.org_id = %s and C.groupId = %s and C.sender = %s", params) self.obj_util.clean(raw_channel_data) channel_data = {'group_id': group_id, 'org_id': org_id, 'channels': []} for record in raw_channel_data: record["payment"] = json.loads(record["payment"]) if record["recipient"] == record["payment"]["payment_address"]: channel = { 'channel_id': record['channel_id'], 'recipient': record['recipient'], 'balance_in_cogs': record['balance_in_cogs'], 'consumed_balance': record["consumed_balance"], 'current_balance': str( decimal.Decimal(record['balance_in_cogs']) - decimal.Decimal(record["consumed_balance"])), 'pending': record['pending'], 'nonce': record['nonce'], 'expiration': record['expiration'], 'signer': record['signer'], 'status': record['status'] } channel_data['channels'].append(channel) return channel_data def get_channel_data_by_group_id_and_channel_id(self, group_id, channel_id): try: result = self.repo.execute( "SELECT * FROM mpe_channel WHERE groupId = %s AND channel_id = %s", [group_id, channel_id]) self.obj_util.clean(result) return result except Exception as e: print(repr(e)) raise e def update_consumed_balance(self, channel_id, authorized_amount, full_amount, nonce): mpe_repo = MPERepository(self.repo) channel = mpe_repo.get_mpe_channels(channel_id) if len(channel) != 0 and self.validate_channel_consume_data( channel[0], authorized_amount, full_amount, nonce): mpe_repo.update_consumed_balance(channel_id, authorized_amount) else: raise Exception("Channel validation failed") return {} @staticmethod def validate_channel_consume_data(channel_details, authorized_amount, full_amount, nonce): if channel_details["consumed_balance"] < authorized_amount \ and channel_details["balance_in_cogs"] == full_amount \ and channel_details["nonce"] == nonce: return True return False
class Registry: def __init__(self, obj_repo): self.repo = obj_repo self.obj_utils = Utils() def _get_all_service(self): """ Method to generate org_id and service mapping.""" try: all_orgs_srvcs_raw = self.repo.execute( "SELECT O.org_id, O.organization_name,O.org_assets_url, O.owner_address, S.service_id FROM service S, " "organization O WHERE S.org_id = O.org_id AND S.is_curated = 1" ) all_orgs_srvcs = {} for rec in all_orgs_srvcs_raw: if rec['org_id'] not in all_orgs_srvcs.keys(): all_orgs_srvcs[rec['org_id']] = { 'service_id': [], 'owner_address': rec['owner_address'] } all_orgs_srvcs[rec['org_id']]['service_id'].append( rec['service_id']) return all_orgs_srvcs except Exception as e: print(repr(e)) raise e def _get_all_members(self, org_id=None): """ Method to generate org_id and members mapping.""" try: query = "SELECT org_id, member FROM members M" params = None if org_id is not None: query += " where M.org_id = %s" params = [org_id] all_orgs_members_raw = self.repo.execute(query, params) all_orgs_members = defaultdict(list) for rec in all_orgs_members_raw: all_orgs_members[rec['org_id']].append(rec['member']) return all_orgs_members except Exception as e: print(repr(e)) raise e def get_all_org(self): """ Method to get list and high level details of all organization.""" try: all_orgs_srvcs = self._get_all_service() all_orgs_members = self._get_all_members() all_orgs_data = [] for rec in all_orgs_srvcs: data = { "org_id": rec, "owner_address": all_orgs_srvcs[rec]['owner_address'], "service_id": all_orgs_srvcs[rec]['service_id'], "members": all_orgs_members.get(rec, []) } all_orgs_data.append(data) return all_orgs_data except Exception as e: print(repr(e)) raise e def _prepare_subquery(self, s, q, fm): try: sub_qry = "" if s == "all": for rec in fm: sub_qry += fm[rec] + " LIKE '%" + str(q) + "%' OR " sub_qry = sub_qry[:-3] if sub_qry.endswith("OR ") else sub_qry else: sub_qry += fm[s] + " LIKE '%" + str(q) + "%' " return sub_qry.replace("org_id", "M.org_id") except Exception as err: raise err def _get_total_count(self, sub_qry, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query search_count_query = "SELECT count(*) as search_count FROM service A, (SELECT DISTINCT M.org_id, M.service_id FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + ") B WHERE A.service_id = B.service_id " \ "AND A.org_id = B.org_id AND A.is_curated = 1 " res = self.repo.execute(search_count_query, values) return res[0].get("search_count", 0) except Exception as err: raise err def _convert_service_metadata_str_to_json(self, record): record["service_rating"] = json.loads(record["service_rating"]) record["assets_url"] = json.loads(record["assets_url"]) record["org_assets_url"] = json.loads(record["org_assets_url"]) record["assets_hash"] = json.loads(record["assets_hash"]) def _search_query_data(self, sub_qry, sort_by, order_by, offset, limit, filter_query, values): try: if filter_query != "": filter_query = " AND " + filter_query srch_qry = "SELECT * FROM service A, (SELECT M.org_id, M.service_id, group_concat(T.tag_name) AS tags FROM " \ "service_metadata M LEFT JOIN service_tags T ON M.service_row_id = T.service_row_id WHERE (" \ + sub_qry.replace('%', '%%') + ")" + filter_query + " GROUP BY M.org_id, M.service_id ORDER BY " + sort_by + " " + order_by + " ) B WHERE " \ "A.service_id = B.service_id AND A.org_id=B.org_id AND A.is_curated= 1 LIMIT %s , %s" qry_dta = self.repo.execute(srch_qry, values + [int(offset), int(limit)]) org_srvc_tuple = () rslt = {} for rec in qry_dta: org_id = rec['org_id'] service_id = rec['service_id'] tags = rec['tags'] org_srvc_tuple = org_srvc_tuple + ((org_id, service_id), ) if org_id not in rslt.keys(): rslt[org_id] = {} if service_id not in rslt[org_id].keys(): rslt[org_id][service_id] = {} rslt[org_id][service_id]["tags"] = tags qry_part = " AND (S.org_id, S.service_id) IN " + \ str(org_srvc_tuple).replace(',)', ')') print("qry_part::", qry_part) sort_by = sort_by.replace("org_id", "M.org_id") services = self.repo.execute( "SELECT M.*,O.organization_name,O.org_assets_url FROM service_metadata M, service S , organization O WHERE O.org_id = S.org_id and " "S.row_id = M.service_row_id " + qry_part + "ORDER BY " + sort_by + " " + order_by) obj_utils = Utils() obj_utils.clean(services) available_service = self._get_is_available_service() for rec in services: self._convert_service_metadata_str_to_json(rec) org_id = rec["org_id"] service_id = rec["service_id"] tags = [] is_available = 0 if rslt.get(org_id, {}).get(service_id, {}).get("tags", None) is not None: tags = rslt[org_id][service_id]["tags"].split(",") if (org_id, service_id) in available_service: is_available = 1 rec.update({"tags": tags}) rec.update({"is_available": is_available}) return services except Exception as err: raise err def _search_response_format(self, tc, offset, limit, rslt): try: return { "total_count": tc, "offset": offset, "limit": limit, "result": rslt } except Exception as err: raise err def get_all_srvcs(self, qry_param): try: fields_mapping = { "display_name": "display_name", "tag_name": "tag_name", "org_id": "org_id" } s = qry_param.get('s', 'all') q = qry_param.get('q', '') offset = qry_param.get('offset', GET_ALL_SERVICE_OFFSET_LIMIT) limit = qry_param.get('limit', GET_ALL_SERVICE_LIMIT) sort_by = fields_mapping.get(qry_param.get('sort_by', None), "ranking") order_by = qry_param.get('order_by', 'desc') if order_by.lower() != "desc": order_by = "asc" sub_qry = self._prepare_subquery(s=s, q=q, fm=fields_mapping) print("get_all_srvcs::sub_qry: ", sub_qry) filter_qry = "" if qry_param.get("filters", None) is not None: filter_query, values = self._filters_to_query( qry_param.get("filters")) print("get_all_srvcs::filter_query: ", filter_query, "|values: ", values) total_count = self._get_total_count(sub_qry=sub_qry, filter_query=filter_query, values=values) if total_count == 0: return self._search_response_format(total_count, offset, limit, []) q_dta = self._search_query_data(sub_qry=sub_qry, sort_by=sort_by, order_by=order_by, offset=offset, limit=limit, filter_query=filter_query, values=values) return self._search_response_format(total_count, offset, limit, q_dta) except Exception as e: print(repr(e)) raise e def get_group_info(self, org_id, service_id): try: group_data = self.repo.execute( "SELECT G.*, E.* FROM service_group G INNER JOIN service_endpoint E ON G.group_id = E.group_id AND G.service_row_id = E.service_row_id WHERE " "G.org_id = %s AND G.service_id = %s ", [org_id, service_id]) self.obj_utils.clean(group_data) groups = {} for rec in group_data: group_id = rec['group_id'] if group_id not in groups.keys(): groups = { group_id: { "group_id": rec['group_id'], "group_name": rec['group_name'], "pricing": json.loads(rec['pricing']), "endpoints": [] } } groups[group_id]['endpoints'].append({ "endpoint": rec['endpoint'], "is_available": rec['is_available'], "last_check_timestamp": rec["last_check_timestamp"] }) return list(groups.values()) except Exception as e: print(repr(e)) raise e def _get_is_available_service(self): try: available_service = [] srvc_st_dta = self.repo.execute( "SELECT DISTINCT org_id, service_id FROM service_endpoint WHERE is_available = 1" ) for rec in srvc_st_dta: available_service.append((rec['org_id'], rec['service_id']), ) return available_service except Exception as err: print(repr(err)) raise err def _filter_condition_to_query(self, filter_condition): value = [] if filter_condition.attr in ["org_id", "service_id"]: filter_condition.attr = "M." + filter_condition.attr if filter_condition.operator == "=": value = filter_condition.value return '%s %s"%s"' % (filter_condition.attr, filter_condition.operator, "%s"), value if filter_condition.operator == "IN": value = filter_condition.value return '%s %s %s' % (filter_condition.attr, filter_condition.operator, "(" + (("%s,") * len(value))[:-1] + ")"), value if filter_condition.operator == "BETWEEN": value = filter_condition.value return '%s %s %s AND %s' % (filter_condition.attr, filter_condition.operator, "%s", "%s"), value def _filters_to_query(self, filter_json): query = "" filters = [] values = [] for filter in filter_json: filters.append(Filter(filter)) for filter in filters: query += "(" for filter_condition in filter.get_filter().get("filter"): sub_query, value = self._filter_condition_to_query( filter_condition) values += value query = query + "(" + sub_query + ") AND " if query.endswith(" AND "): query = query[:-5] query += ") OR " if query.endswith(" OR "): query = query[:-4] return query, values def get_filter_attribute(self, attribute): """ Method to fetch filter metadata based on attribute.""" try: filter_attribute = {"attribute": attribute, "values": []} if attribute == "tag_name": filter_data = self.repo.execute( "SELECT DISTINCT tag_name AS 'key', tag_name AS 'value' FROM service_tags T, service S " "WHERE S.row_id = T.service_row_id AND S.is_curated = 1") elif attribute == "display_name": filter_data = self.repo.execute( "SELECT DISTINCT S.service_id AS 'key',display_name AS 'value' FROM service_metadata M, service S " "WHERE S.row_id = M.service_row_id AND S.is_curated = 1") elif attribute == "org_id": filter_data = self.repo.execute( "SELECT DISTINCT O.org_id AS 'key' ,O.organization_name AS 'value' from organization O, service S " "WHERE S.org_id = O.org_id AND S.is_curated = 1") else: return filter_attribute for rec in filter_data: filter_attribute["values"].append(rec) return filter_attribute except Exception as e: print(repr(e)) raise e def get_all_group_for_org_id(self, org_id): """ Method to get all group data for given org_id. This includes group data at org level""" try: groups_data = self.repo.execute( "SELECT group_id, group_name, payment FROM org_group WHERE org_id = %s", [org_id]) [ rec.update({'payment': json.loads(rec['payment'])}) for rec in groups_data ] groups = {"org_id": org_id, "groups": groups_data} return groups except Exception as e: print(repr(e)) raise e def get_service_data_by_org_id_and_service_id(self, org_id, service_id): try: """ Method to get all service data for given org_id and service_id""" tags = [] org_groups_dict = {} basic_service_data = self.repo.execute( "SELECT * FROM service S, service_metadata M, organization O WHERE O.org_id = S.org_id AND S.row_id = M.service_row_id AND S.org_id = %s " "AND S.service_id = %s AND S.is_curated = 1", [org_id, service_id]) if len(basic_service_data) == 0: return [] self.obj_utils.clean(basic_service_data) org_group_data = self.repo.execute( "SELECT * FROM org_group WHERE org_id = %s", [org_id]) self.obj_utils.clean(org_group_data) service_group_data = self.get_group_info(org_id=org_id, service_id=service_id) tags = self.repo.execute( "SELECT tag_name FROM service_tags WHERE org_id = %s AND service_id = %s", [org_id, service_id]) result = basic_service_data[0] self._convert_service_metadata_str_to_json(result) for rec in org_group_data: org_groups_dict[rec['group_id']] = { "payment": json.loads(rec["payment"]) } is_available = 0 for rec in service_group_data: if is_available == 0: endpoints = rec['endpoints'] for endpoint in endpoints: is_available = endpoint['is_available'] if is_available == 1: break rec.update(org_groups_dict.get(rec['group_id'], {})) result.update({"is_available": is_available}) result.update({"groups": service_group_data}) result.update({"tags": tags}) return result except Exception as e: print(repr(e)) raise e def get_org_details(self, org_id): """ Method to get org details for given org_id. """ try: org_details = self.repo.execute( "SELECT * from organization o, (select org_id, count(*) as service_count " "from service where org_id = %s) s where o.org_id = %s and o.org_id = s.org_id", [org_id, org_id]) obj_utils = Utils() obj_utils.clean(org_details) if len(org_details) > 0: members = self._get_all_members(org_id) org_details[0]["members"] = members return org_details except Exception as e: print(repr(e)) raise e def update_service_rating(self, org_id, service_id): """ Method updates service_rating and total_user_rated when user rating is changed for given service_id and org_id. """ try: update_service_metadata = self.repo.execute( "UPDATE service_metadata A INNER JOIN " "(SELECT U.org_id, U.service_id, AVG(U.rating) AS service_rating, count(*) AS total_users_rated " "FROM user_service_vote AS U WHERE U.rating IS NOT NULL GROUP BY U.service_id, U.org_id ) AS B " "ON A.org_id=B.org_id AND A.service_id=B.service_id SET A.service_rating " "= CONCAT('{\"rating\":', B.service_rating, ' , \"total_users_rated\":', B.total_users_rated, '}') " "WHERE A.org_id = %s AND A.service_id = %s ", [org_id, service_id]) return "success" except Exception as e: print(repr(e)) raise e
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