from flask import Blueprint, request import logging from datetime import datetime, timedelta from firebase.firebase import Firebase import pytz import utility.function as func import os import ast import json import uuid from dashboard.search import C360Search from dashboard.profile import fucntion as profileFunction logging.basicConfig(level=logging.INFO) evntFunction = func.Event() dashboard_profile = Blueprint('dashboard_profile', __name__, url_prefix='/dashboard/profile') fb = Firebase(host=os.environ.get("FIREBASE_HOST")) timezone = pytz.timezone('Asia/Bangkok') @dashboard_profile.route('/explore', methods=['GET']) def dashboard_profile_explore(): try: if request.method == 'GET': data = request.args for req in ['property_id', 'key', 'value']: if req not in data: return {'message': f"{req} is required"}, 400 property_id = data.get('property_id') key = data.get("key") value = data.get("value") if key not in ['phoneNumber', 'email', 'customer_id', 'user_pseudo_id']: return {'message': f"{key} not support for search function"}, 400 if key != 'user_pseudo_id': c360search = C360Search(property_id=property_id) user_pseudo_id = c360search.get_profile(key, value) else: user_pseudo_id = value if not user_pseudo_id: return {'message': f"user not fond in this property"}, 404 import concurrent.futures from utility.lookerSDK import LookerSDK import pandas as pd looker = LookerSDK(property_id=property_id) #start query full_result = [] queries = { "lifetimeSpend": { "view": "profile_explore", "fields": ["transaction.total_price"], "filters": {"profile_explore.user_pseudo_id": user_pseudo_id}, "model": f"c360_client_{property_id}", "limit": "1" }, "name": { "view": "profile_explore", "fields": ["profile_explore__user_property.value_string"], "filters": { "profile_explore__user_property.key": "FirstName", "profile_explore.user_pseudo_id": user_pseudo_id }, "model": f"c360_client_{property_id}", "limit": "1" }, "transaction": { "view": "profile_explore", "fields": [ "transaction.receipt_date_date", "transaction.receipt_no", "transaction.item_name", "transaction.total_price" ], "filters": { "transaction.receipt_no": "-NULL", "profile_explore.user_pseudo_id": user_pseudo_id }, "sorts": ["transaction.receipt_date_date"], "model": f"c360_client_{property_id}", "limit": "-1" }, "transaction_overview": { "view": "profile_explore", "fields": [ "transaction.total_price", "transaction.total_order", "transaction.average_order_amount", ], "filters": { "transaction.receipt_no": "-NULL", "profile_explore.user_pseudo_id": user_pseudo_id }, "model": f"c360_client_{property_id}", "limit": "1" }, "keywords": { "view": "profile_explore", "fields": [ "profile_explore.keywords_string" ], "filters": { "profile_explore.user_pseudo_id": user_pseudo_id }, "model": f"c360_client_{property_id}", "limit": "1" }, "branch": { "view": "event_offline", "fields": [ "event_offline__event_property.value" ], "filters": { "event_offline__event_property.key": "%branch%", "event_offline.user_pseudo_id": user_pseudo_id }, "filter_expression": "matches_filter(${event_offline__event_property.key}, `-%code%`)", "model": f"c360_client_{property_id}", "limit": "1" } } def run_looker_query(query_obj): try: query = looker.sdk.create_query(query_obj) result = looker.sdk.run_query(query.id, result_format="json", cache=True) return json.loads(result) except: return [] def get_user_profile(): user_profile = fb.db.reference().child(f"account/{property_id}/profile/{user_pseudo_id}").get() return profileFunction.transformUserData_main_profile(user_profile) def get_journey(): return profileFunction.transformUserData_journey(property_id=property_id, user_pseudo_id=user_pseudo_id) def get_second_profile(): return profileFunction.transformUserData_second_profile() def get_channel_engagement(): return profileFunction.transformUserData_channel_engagement(property_id=property_id, user_pseudo_id=user_pseudo_id) def get_conversation(): return profileFunction.transformUserData_conversation(property_id=property_id, user_pseudo_id=user_pseudo_id) with concurrent.futures.ThreadPoolExecutor(max_workers=6) as executor: futures = { "user_profile": executor.submit(get_user_profile), "journey": executor.submit(get_journey), "second_profile": executor.submit(get_second_profile), "channel_engagement": executor.submit(get_channel_engagement), "conversation": executor.submit(get_conversation), "lifetimeSpend": executor.submit(run_looker_query, queries["lifetimeSpend"]), "name": executor.submit(run_looker_query, queries["name"]), "transaction": executor.submit(run_looker_query, queries["transaction"]), "transaction_overview": executor.submit(run_looker_query, queries["transaction_overview"]), "keywords": executor.submit(run_looker_query, queries["keywords"]), "branch": executor.submit(run_looker_query, queries["branch"]), } results = {k: f.result() for k, f in futures.items()} # --- Process results --- user_profile_object = results["user_profile"] # Enrich with lifetime spend lifetimeSpend = results["lifetimeSpend"] if lifetimeSpend and isinstance(lifetimeSpend, list): user_profile_object['data']['calculate']['lifetime_spend'] = lifetimeSpend[0].get('transaction.total_price', 0) # Enrich with name name = results["name"] if name and isinstance(name, list): user_profile_object['data']['name'] = name[0].get('profile_explore__user_property.value_string', '-') #Enrich Keyword keywords = results['keywords'] if keywords and isinstance(keywords, list): results['second_profile']['data']['interests'] = keywords #Enrich branch branch = results['branch'] if branch and isinstance(branch, list): results['second_profile']['data']['branch'] = branch # Transaction data transaction_object = { "id": "transaction", "life_time_order": results['transaction_overview'][0]['transaction.total_order'] if len(results['transaction_overview'])>0 else '-', "lifetime_spend": results['transaction_overview'][0]['transaction.total_price'] if len(results['transaction_overview'])>0 else '-', "average_order_amount": results['transaction_overview'][0]['transaction.average_order_amount'] if len(results['transaction_overview'])>0 else '-', "data": results["transaction"] } # Combine results full_result.extend([ user_profile_object, results["journey"], results["second_profile"], results["channel_engagement"], results["conversation"], transaction_object ]) final_object = { "status": "ok", "user_pseudo_id": user_pseudo_id, "widgets": full_result } logging.info("dashboard_profile_explore complete") return final_object, 200 return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500 @dashboard_profile.route('/detail/journey', methods=['GET']) def dashboard_profile_detail_journey(): try: if request.method == 'GET': data = request.args for req in ['property_id', 'user_pseudo_id']: if req not in data: return {'message': f"{req} is required"}, 400 property_id = data.get('property_id') user_pseudo_id = data.get('user_pseudo_id') #Query part object = profileFunction.transformUserData_detail_journey(property_id, user_pseudo_id) if 'data' not in object: object['status'] = 'not_fond' return object, 404 else: object['status'] = 'ok' return object, 200 return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500 @dashboard_profile.route('/detail/profile', methods=['GET']) def dashboard_profile_detail_profile(): try: if request.method == 'GET': data = request.args for req in ['property_id', 'user_pseudo_id']: if req not in data: return {'message': f"{req} is required"}, 400 property_id = data.get('property_id') user_pseudo_id = data.get('user_pseudo_id') returnObject = profileFunction.transformUserData_detail_profile(property_id,user_pseudo_id, fb) returnObject['status'] = 'ok' return returnObject, 200 return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500 @dashboard_profile.route('/list', methods=['GET']) def dashboard_profile_list(): try: if request.method == 'GET': data = request.args for req in ['property_id']: if req not in data: return {'message': f"{req} is required"}, 400 property_id = data.get("property_id") listProfile = fb.db.reference().child(f"account/{property_id}/cache/profile_explore").get() return {'status': 'ok', 'data': listProfile}, 200 return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500 #Looker tab in @dashboard_profile.route('/search', methods=['GET']) @dashboard_profile.route('/search/', methods=['GET']) def dashboard_profile_search(): try: if request.method == 'GET': data = request.args for req in ['property_id', 'query']: if req not in data: return {'message': f"{req} is required"}, 400 else: if req in ["start", "end"]: val = data.get(req) if '/' not in val: return {'message': f"{req} must be in format YYYY/MM/DD"}, 400 dateNow = datetime.now().strftime('%Y/%m/%d') dateNow_1 = (datetime.now() - timedelta(days=1)).strftime('%Y/%m/%d') property_id = data.get("property_id") search = data.get("query") start = data.get('start', None) end = data.get('end', None) page = data.get('page', 1) page_size = data.get('page_size', 50) page = int(page) page_size = int(page_size) from utility.lookerSDK import LookerSDK import pandas as pd looker = LookerSDK(property_id=property_id) #Handle date if start and end: if start == end: datefilter = start else: datefilter = f"{start} to {end}" else: datefilter = "1 year" query = { "view": "profile_explore", "fields": [ "profile_explore.user_pseudo_id_list" ], "filter_expression": f'''(matches_filter(${{profile_explore__user_property.value_string}}, `%{search.replace("_", "^_")}%`) AND matches_filter(${{profile_explore.lastupdate_date}}, `{datefilter}`)) OR (matches_filter(${{profile_explore.keywords_string}}, `%{search.replace("_", "^_")}%`) AND matches_filter(${{profile_explore.lastupdate_date}}, `{datefilter}`)) OR (matches_filter(${{profile_explore.user_pseudo_id}}, `%{search.replace("_", "^_")}%`) AND matches_filter(${{profile_explore.lastupdate_date}}, `{datefilter}`))''', "model": f"c360_client_{property_id}", "limit": "-1" } data = looker.sdk.run_query(looker.sdk.create_query(query).id, result_format="json") data = json.loads(data) if not data: return {'status': 'not_fond', 'message': f"{search} not match any result"}, 404 userList = data[0]['profile_explore.user_pseudo_id_list'] if not userList: return {'status': 'not_fond', 'message': f"{search} not match any result"}, 404 userListString = ",".join(userList) queryProfile = { "view": "profile_explore", "fields": [ "profile_explore.user_pseudo_id", "profile_explore.lastupdate_time", "profile_explore.user_property", "profile_explore.keywords_string" ], "filters": { "profile_explore.user_pseudo_id": userListString }, "sorts": [ "profile_explore.lastupdate_time desc" ], "model": f"c360_client_{property_id}", "limit": "-1" } dataProfile = looker.sdk.run_query(looker.sdk.create_query(queryProfile).id, result_format="json") dataProfile = json.loads(dataProfile) df =pd.DataFrame(dataProfile) df['profile_explore.user_property'] = df['profile_explore.user_property'].apply(json.loads) df.rename(columns={ "profile_explore.user_pseudo_id": "user_pseudo_id", "profile_explore.lastupdate_time": "lastupdate", "profile_explore.user_property": "user_property", "profile_explore.keywords_string": "keywords", }) start_page = (page - 1) * page_size end_page = start_page + page_size df_page = df.iloc[start_page:end_page] total_items = len(df) total_pages = (total_items + page_size - 1) // page_size records = df_page.to_dict(orient='records') return {'status': 'ok', 'data': records, 'page': page, 'page_size': page_size, 'total_pages': total_pages, 'total_records': total_items}, 200 return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500 @dashboard_profile.route('/explore/v2', methods=['GET']) @dashboard_profile.route('/explore/v2/', methods=['GET']) def dashboard_profile_explore_v2(): try: if request.method == 'GET': data = request.args for req in ['property_id', 'user_pseudo_id']: if req not in data: return {'message': f"{req} is required"}, 400 property_id = data.get('property_id') user_pseudo_id = data.get("user_pseudo_id") from utility.lookerSDK import LookerSDK import pandas as pd looker = LookerSDK(property_id=property_id) queryProfile = { "view": "profile_explore", "fields": [ "profile_explore.user_pseudo_id", "profile_explore.lastupdate_time", "profile_explore.user_property", "profile_explore.keywords_string", "transaction.total_price" ], "filters": { "profile_explore.user_pseudo_id": user_pseudo_id }, "model": f"c360_client_{property_id}", "limit": "-1" } dataProfile = looker.sdk.run_query(looker.sdk.create_query(queryProfile).id, result_format="json", cache=True) dataProfile = json.loads(dataProfile) df =pd.DataFrame(data) df['profile_explore.user_property'] = df['profile_explore.user_property'].apply(json.loads) return {'status': 'error', 'message': 'Method not allowed'}, 405 except Exception as e: logging.error(e) return {'status': 'error', 'message': e}, 500