Underground-Digital/Workflow-Engine
0
1from datetime import datetime2from decimal import Decimal3 4import pytz5from flask import jsonify6from flask_login import current_user7from flask_restful import Resource, reqparse8 9from controllers.console import api10from controllers.console.app.wraps import get_app_model11from controllers.console.wraps import account_initialization_required, setup_required12from extensions.ext_database import db13from libs.helper import DatetimeString14from libs.login import login_required15from models.model import AppMode16 17 18class DailyMessageStatistic(Resource):19 @setup_required20 @login_required21 @account_initialization_required22 @get_app_model23 def get(self, app_model):24 account = current_user25 26 parser = reqparse.RequestParser()27 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")28 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")29 args = parser.parse_args()30 31 sql_query = """SELECT32 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,33 COUNT(*) AS message_count34FROM35 messages36WHERE37 app_id = :app_id"""38 arg_dict = {"tz": account.timezone, "app_id": app_model.id}39 40 timezone = pytz.timezone(account.timezone)41 utc_timezone = pytz.utc42 43 if args["start"]:44 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")45 start_datetime = start_datetime.replace(second=0)46 47 start_datetime_timezone = timezone.localize(start_datetime)48 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)49 50 sql_query += " AND created_at >= :start"51 arg_dict["start"] = start_datetime_utc52 53 if args["end"]:54 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")55 end_datetime = end_datetime.replace(second=0)56 57 end_datetime_timezone = timezone.localize(end_datetime)58 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)59 60 sql_query += " AND created_at < :end"61 arg_dict["end"] = end_datetime_utc62 63 sql_query += " GROUP BY date ORDER BY date"64 65 response_data = []66 67 with db.engine.begin() as conn:68 rs = conn.execute(db.text(sql_query), arg_dict)69 for i in rs:70 response_data.append({"date": str(i.date), "message_count": i.message_count})71 72 return jsonify({"data": response_data})73 74 75class DailyConversationStatistic(Resource):76 @setup_required77 @login_required78 @account_initialization_required79 @get_app_model80 def get(self, app_model):81 account = current_user82 83 parser = reqparse.RequestParser()84 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")85 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")86 args = parser.parse_args()87 88 sql_query = """SELECT89 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,90 COUNT(DISTINCT messages.conversation_id) AS conversation_count91FROM92 messages93WHERE94 app_id = :app_id"""95 arg_dict = {"tz": account.timezone, "app_id": app_model.id}96 97 timezone = pytz.timezone(account.timezone)98 utc_timezone = pytz.utc99 100 if args["start"]:101 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")102 start_datetime = start_datetime.replace(second=0)103 104 start_datetime_timezone = timezone.localize(start_datetime)105 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)106 107 sql_query += " AND created_at >= :start"108 arg_dict["start"] = start_datetime_utc109 110 if args["end"]:111 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")112 end_datetime = end_datetime.replace(second=0)113 114 end_datetime_timezone = timezone.localize(end_datetime)115 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)116 117 sql_query += " AND created_at < :end"118 arg_dict["end"] = end_datetime_utc119 120 sql_query += " GROUP BY date ORDER BY date"121 122 response_data = []123 124 with db.engine.begin() as conn:125 rs = conn.execute(db.text(sql_query), arg_dict)126 for i in rs:127 response_data.append({"date": str(i.date), "conversation_count": i.conversation_count})128 129 return jsonify({"data": response_data})130 131 132class DailyTerminalsStatistic(Resource):133 @setup_required134 @login_required135 @account_initialization_required136 @get_app_model137 def get(self, app_model):138 account = current_user139 140 parser = reqparse.RequestParser()141 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")142 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")143 args = parser.parse_args()144 145 sql_query = """SELECT146 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,147 COUNT(DISTINCT messages.from_end_user_id) AS terminal_count148FROM149 messages150WHERE151 app_id = :app_id"""152 arg_dict = {"tz": account.timezone, "app_id": app_model.id}153 154 timezone = pytz.timezone(account.timezone)155 utc_timezone = pytz.utc156 157 if args["start"]:158 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")159 start_datetime = start_datetime.replace(second=0)160 161 start_datetime_timezone = timezone.localize(start_datetime)162 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)163 164 sql_query += " AND created_at >= :start"165 arg_dict["start"] = start_datetime_utc166 167 if args["end"]:168 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")169 end_datetime = end_datetime.replace(second=0)170 171 end_datetime_timezone = timezone.localize(end_datetime)172 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)173 174 sql_query += " AND created_at < :end"175 arg_dict["end"] = end_datetime_utc176 177 sql_query += " GROUP BY date ORDER BY date"178 179 response_data = []180 181 with db.engine.begin() as conn:182 rs = conn.execute(db.text(sql_query), arg_dict)183 for i in rs:184 response_data.append({"date": str(i.date), "terminal_count": i.terminal_count})185 186 return jsonify({"data": response_data})187 188 189class DailyTokenCostStatistic(Resource):190 @setup_required191 @login_required192 @account_initialization_required193 @get_app_model194 def get(self, app_model):195 account = current_user196 197 parser = reqparse.RequestParser()198 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")199 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")200 args = parser.parse_args()201 202 sql_query = """SELECT203 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,204 (SUM(messages.message_tokens) + SUM(messages.answer_tokens)) AS token_count,205 SUM(total_price) AS total_price206FROM207 messages208WHERE209 app_id = :app_id"""210 arg_dict = {"tz": account.timezone, "app_id": app_model.id}211 212 timezone = pytz.timezone(account.timezone)213 utc_timezone = pytz.utc214 215 if args["start"]:216 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")217 start_datetime = start_datetime.replace(second=0)218 219 start_datetime_timezone = timezone.localize(start_datetime)220 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)221 222 sql_query += " AND created_at >= :start"223 arg_dict["start"] = start_datetime_utc224 225 if args["end"]:226 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")227 end_datetime = end_datetime.replace(second=0)228 229 end_datetime_timezone = timezone.localize(end_datetime)230 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)231 232 sql_query += " AND created_at < :end"233 arg_dict["end"] = end_datetime_utc234 235 sql_query += " GROUP BY date ORDER BY date"236 237 response_data = []238 239 with db.engine.begin() as conn:240 rs = conn.execute(db.text(sql_query), arg_dict)241 for i in rs:242 response_data.append(243 {"date": str(i.date), "token_count": i.token_count, "total_price": i.total_price, "currency": "USD"}244 )245 246 return jsonify({"data": response_data})247 248 249class AverageSessionInteractionStatistic(Resource):250 @setup_required251 @login_required252 @account_initialization_required253 @get_app_model(mode=[AppMode.CHAT, AppMode.AGENT_CHAT, AppMode.ADVANCED_CHAT])254 def get(self, app_model):255 account = current_user256 257 parser = reqparse.RequestParser()258 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")259 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")260 args = parser.parse_args()261 262 sql_query = """SELECT263 DATE(DATE_TRUNC('day', c.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,264 AVG(subquery.message_count) AS interactions265FROM266 (267 SELECT268 m.conversation_id,269 COUNT(m.id) AS message_count270 FROM271 conversations c272 JOIN273 messages m274 ON c.id = m.conversation_id275 WHERE276 c.override_model_configs IS NULL277 AND c.app_id = :app_id"""278 arg_dict = {"tz": account.timezone, "app_id": app_model.id}279 280 timezone = pytz.timezone(account.timezone)281 utc_timezone = pytz.utc282 283 if args["start"]:284 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")285 start_datetime = start_datetime.replace(second=0)286 287 start_datetime_timezone = timezone.localize(start_datetime)288 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)289 290 sql_query += " AND c.created_at >= :start"291 arg_dict["start"] = start_datetime_utc292 293 if args["end"]:294 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")295 end_datetime = end_datetime.replace(second=0)296 297 end_datetime_timezone = timezone.localize(end_datetime)298 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)299 300 sql_query += " AND c.created_at < :end"301 arg_dict["end"] = end_datetime_utc302 303 sql_query += """304 GROUP BY m.conversation_id305 ) subquery306LEFT JOIN307 conversations c308 ON c.id = subquery.conversation_id309GROUP BY310 date311ORDER BY312 date"""313 314 response_data = []315 316 with db.engine.begin() as conn:317 rs = conn.execute(db.text(sql_query), arg_dict)318 for i in rs:319 response_data.append(320 {"date": str(i.date), "interactions": float(i.interactions.quantize(Decimal("0.01")))}321 )322 323 return jsonify({"data": response_data})324 325 326class UserSatisfactionRateStatistic(Resource):327 @setup_required328 @login_required329 @account_initialization_required330 @get_app_model331 def get(self, app_model):332 account = current_user333 334 parser = reqparse.RequestParser()335 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")336 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")337 args = parser.parse_args()338 339 sql_query = """SELECT340 DATE(DATE_TRUNC('day', m.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,341 COUNT(m.id) AS message_count,342 COUNT(mf.id) AS feedback_count343FROM344 messages m345LEFT JOIN346 message_feedbacks mf347 ON mf.message_id=m.id AND mf.rating='like'348WHERE349 m.app_id = :app_id"""350 arg_dict = {"tz": account.timezone, "app_id": app_model.id}351 352 timezone = pytz.timezone(account.timezone)353 utc_timezone = pytz.utc354 355 if args["start"]:356 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")357 start_datetime = start_datetime.replace(second=0)358 359 start_datetime_timezone = timezone.localize(start_datetime)360 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)361 362 sql_query += " AND m.created_at >= :start"363 arg_dict["start"] = start_datetime_utc364 365 if args["end"]:366 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")367 end_datetime = end_datetime.replace(second=0)368 369 end_datetime_timezone = timezone.localize(end_datetime)370 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)371 372 sql_query += " AND m.created_at < :end"373 arg_dict["end"] = end_datetime_utc374 375 sql_query += " GROUP BY date ORDER BY date"376 377 response_data = []378 379 with db.engine.begin() as conn:380 rs = conn.execute(db.text(sql_query), arg_dict)381 for i in rs:382 response_data.append(383 {384 "date": str(i.date),385 "rate": round((i.feedback_count * 1000 / i.message_count) if i.message_count > 0 else 0, 2),386 }387 )388 389 return jsonify({"data": response_data})390 391 392class AverageResponseTimeStatistic(Resource):393 @setup_required394 @login_required395 @account_initialization_required396 @get_app_model(mode=AppMode.COMPLETION)397 def get(self, app_model):398 account = current_user399 400 parser = reqparse.RequestParser()401 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")402 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")403 args = parser.parse_args()404 405 sql_query = """SELECT406 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,407 AVG(provider_response_latency) AS latency408FROM409 messages410WHERE411 app_id = :app_id"""412 arg_dict = {"tz": account.timezone, "app_id": app_model.id}413 414 timezone = pytz.timezone(account.timezone)415 utc_timezone = pytz.utc416 417 if args["start"]:418 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")419 start_datetime = start_datetime.replace(second=0)420 421 start_datetime_timezone = timezone.localize(start_datetime)422 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)423 424 sql_query += " AND created_at >= :start"425 arg_dict["start"] = start_datetime_utc426 427 if args["end"]:428 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")429 end_datetime = end_datetime.replace(second=0)430 431 end_datetime_timezone = timezone.localize(end_datetime)432 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)433 434 sql_query += " AND created_at < :end"435 arg_dict["end"] = end_datetime_utc436 437 sql_query += " GROUP BY date ORDER BY date"438 439 response_data = []440 441 with db.engine.begin() as conn:442 rs = conn.execute(db.text(sql_query), arg_dict)443 for i in rs:444 response_data.append({"date": str(i.date), "latency": round(i.latency * 1000, 4)})445 446 return jsonify({"data": response_data})447 448 449class TokensPerSecondStatistic(Resource):450 @setup_required451 @login_required452 @account_initialization_required453 @get_app_model454 def get(self, app_model):455 account = current_user456 457 parser = reqparse.RequestParser()458 parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")459 parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")460 args = parser.parse_args()461 462 sql_query = """SELECT463 DATE(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz )) AS date,464 CASE465 WHEN SUM(provider_response_latency) = 0 THEN 0466 ELSE (SUM(answer_tokens) / SUM(provider_response_latency))467 END as tokens_per_second468FROM469 messages470WHERE471 app_id = :app_id"""472 arg_dict = {"tz": account.timezone, "app_id": app_model.id}473 474 timezone = pytz.timezone(account.timezone)475 utc_timezone = pytz.utc476 477 if args["start"]:478 start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")479 start_datetime = start_datetime.replace(second=0)480 481 start_datetime_timezone = timezone.localize(start_datetime)482 start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)483 484 sql_query += " AND created_at >= :start"485 arg_dict["start"] = start_datetime_utc486 487 if args["end"]:488 end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")489 end_datetime = end_datetime.replace(second=0)490 491 end_datetime_timezone = timezone.localize(end_datetime)492 end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)493 494 sql_query += " AND created_at < :end"495 arg_dict["end"] = end_datetime_utc496 497 sql_query += " GROUP BY date ORDER BY date"498 499 response_data = []500 501 with db.engine.begin() as conn:502 rs = conn.execute(db.text(sql_query), arg_dict)503 for i in rs:504 response_data.append({"date": str(i.date), "tps": round(i.tokens_per_second, 4)})505 506 return jsonify({"data": response_data})507 508 509api.add_resource(DailyMessageStatistic, "/apps/<uuid:app_id>/statistics/daily-messages")510api.add_resource(DailyConversationStatistic, "/apps/<uuid:app_id>/statistics/daily-conversations")511api.add_resource(DailyTerminalsStatistic, "/apps/<uuid:app_id>/statistics/daily-end-users")512api.add_resource(DailyTokenCostStatistic, "/apps/<uuid:app_id>/statistics/token-costs")513api.add_resource(AverageSessionInteractionStatistic, "/apps/<uuid:app_id>/statistics/average-session-interactions")514api.add_resource(UserSatisfactionRateStatistic, "/apps/<uuid:app_id>/statistics/user-satisfaction-rate")515api.add_resource(AverageResponseTimeStatistic, "/apps/<uuid:app_id>/statistics/average-response-time")516api.add_resource(TokensPerSecondStatistic, "/apps/<uuid:app_id>/statistics/tokens-per-second")517