AiS dZddlZddlZddlZddlmZmZddlmZejjdejjejjejje ddl mZmZmZmZmZmZmZmZ ddl mZdZd Zd Zd Zd Zd ZdZdZde fdZ!d-de de"fdZ#de de fdZ$de de fdZ%de de fdZ&de'de'fdZ(de de fdZ)de de fdZ*de de fdZ+de de fdZ,de de fd Z-de de fd!Z.d.de de d"e'fd#Z/d.de de d"e'fd$Z0d.d%e1d"e'fd&Z2de de fd'Z3d/de de d"e'fd(Z4de de fd)Z5d*Z6d.de de d"e'fd+Z7d/de de d"e'fd,Z8y#e$rdZYwxYw)0uM 月次レポート生成サービス - データベース接続・クエリ DB_TYPE設定値: - 'sqlite' : ローカル開発用SQLite - 'neon' : Neon PostgreSQL（外部ホスト、SSL必須） - 'lightsail' : Lightsail内PostgreSQL（localhost、SSL不要） - 'pgsql' : 後方互換（neonと同じ動作） N)datetime timedelta) monthrange)DB_TYPE SQLITE_PATHPG_HOSTPG_PORT PG_DBNAMEPG_USER PG_PASSWORDUNANSWERED_PATTERNS) PG_SSLMODEctdvS)u"PostgreSQL系かどうかを判定)neon lightsailpgsql)r+/var/www/html/chat/reports/core/database.py _is_postgresrs 4 44rcVtrtStdk(s tdk(rytdk(ryy)u@SSLモードを取得（DB_TYPEに応じたデフォルト値）rrrequirerdisableprefer)rrrrr _get_sslmoder#s+&Gw.+ rc $tr;ddl}t}|jtt t tt|}|St}tjj|s{tjjtjjtjjtjjt |}t#j|}t"j$|_|S)u)DB接続を取得（読み取り専用）rN)hostportdbnameuserpasswordsslmode)rpsycopg2rconnectrr r r r rospathisabsjoindirnameabspath__file__sqlite3Row row_factory)r$r#conndb_paths rget_connectionr2.s~.     ww}}W%ggll277??277??277??S[C\3]#^`ghGw'";; rctr*ddl}|j|jjS|jS)u6カーソルを取得（PostgreSQLはRealDictCursor）rN)cursor_factory)rpsycopg2.extrascursorextrasRealDictCursor)r0r$s r get_cursorr9Fs1~{{(//*H*H{II ;;=rctryy)u2DBタイプに応じた日付キャストを返すztimestamp::datezDATE(timestamp)rrrr_get_date_castr<Ns ~ rctryy)u,DBタイプに応じた時間抽出を返すz%EXTRACT(HOUR FROM timestamp)::INTEGERz*CAST(strftime('%H', timestamp) AS INTEGER)r;rrr_get_hour_extractr>Us ~6 7rctryy)udDBタイプに応じた曜日抽出を返す（0=日曜, 6=土曜 → 0=月曜, 6=日曜に変換）z/(EXTRACT(DOW FROM timestamp)::INTEGER + 6) %% 7z4(CAST(strftime('%w', timestamp) AS INTEGER) + 6) % 7r;rrr_get_day_of_week_extractr@\s~A Arcg}tD]7}tr|jd|d#|jd|d9dj|S)u$未回答判定のSQL条件を構築zassistant_message LIKE '%%z%%'zassistant_message LIKE '%z%'z OR )r rappendr)) conditionspatterns r_build_unanswered_conditionrEfs^J&G >    :7)3G H    9'"E F G ;;z ""rqueryc>tr|jddS|S)u5DBタイプに応じてプレースホルダを変換?z%s)rreplace)rFs r_prepare_queryrJrs~}}S$'' Lrparamsc>t|}|j||y)u9クエリを実行（プレースホルダ変換付き）N)rJexecute)r6rFrKconverted_querys r_executerOzs$U+O NN?F+r start_dateend_datect}t|}t}d|d}t||||f|j }|j |Dcgc] }t |c}Scc}w)u期間内の会話を取得z SELECT id, session_id, user_message, assistant_message, timestamp, ip_address, user_agent FROM conversations WHERE 4 BETWEEN ? AND ? ORDER BY timestamp ASC r2r9r<rOfetchallclosedictrPrQr0r6 date_castrFrowsrows rget_conversationsr\t  D  F Ik E  VUZ23 ?? DJJL!% &#DI && &A.ct}t|}t}d|d}t||||f|j }|j |Dcgc] }t |c}Scc}w)u*期間内のフィードバックを取得zw SELECT id, session_id, feedback, question, answer, timestamp, ip_address FROM feedbacks WHERE rSrTrXs r get_feedbacksr`r]r^ct}t|}t}t}i}d|d}t ||||f|j d|d<d|d|d}t ||||f|j d|d<d|d|d}t ||||f|j d|d <d |d}t ||||f|j d|d <d |d }t ||||f|j d|d<d |d}t ||||f|j d|d<d|d}t ||||f|j d|d<|d|dz|d<|j |S)u期間の統計サマリーzC SELECT COUNT(*) as count FROM conversations WHERE  BETWEEN ? AND ? counttotal_conversationsz" BETWEEN ? AND ? AND NOT () answered_count BETWEEN ? AND ? AND (unanswered_countzU SELECT COUNT(DISTINCT session_id) as count FROM conversations WHERE unique_sessionsz? SELECT COUNT(*) as count FROM feedbacks WHERE z3 BETWEEN ? AND ? AND feedback = 'like' likes_countz6 BETWEEN ? AND ? AND feedback = 'dislike' dislikes_countzU SELECT COUNT(DISTINCT ip_address) as count FROM conversations WHERE zM BETWEEN ? AND ? AND ip_address IS NOT NULL AND ip_address != '' unique_ipstotal_feedbacks)r2r9r<rErOfetchonerV)rPrQr0r6rYunanswered_condstatsrFs rget_statistics_for_periodrqs(  D  F I13O Ek E VUZ23#)??#4W#=E  k!"# E  VUZ23$oo/8E k  E  VUZ23 & 1' :E k E VUZ23%09E k E  VUZ23!??,W5E-k E  VUZ23$oo/8E k E  VUZ23 //+G4E, %]3e.calc_changes6 q=!A+3 ,1 ,g(H4s:A>>rrdrfrhrjrk)rdrfrhrjrk)r|r}changes)rrq)rrrs_last_day current_start current_end prev_year prev_month prev_last_day prev_startprev_end current_stats prev_statsr~s rget_monthly_comparisonrsKT5)KAxfAeC[,MF!E#;a~6K z1H   QY !)Z8A};a 3/s3JAj-Q}S.ABH-m[IM*:x@J? !#.}=R/SU_`uUv#w)-8H*I:VfKgh +M:L,MzZlOm n&}]'CZP]E^_)-8H*I:VfKgh    rc vt}t|}t}t}d|d|d|d|d|d|d }t ||||f|j }|j g}|D]D} t| } t| dd r| djd | d<|j| F|S) u 日別統計 SELECT z as date, COUNT(*) as total_conversations, COUNT(DISTINCT session_id) as unique_sessions, SUM(CASE WHEN (zI) THEN 1 ELSE 0 END) as unanswered_count, SUM(CASE WHEN NOT (zP) THEN 1 ELSE 0 END) as answered_count FROM conversations WHERE " BETWEEN ? AND ? GROUP BY  ORDER BY ASC datestrftime%Y-%m-%d) r2r9r<rErOrUrVrWhasattrrrB) rPrQr0r6rYrorFrZresultr[ds rget_daily_statisticsrs  D  F I13O K,,- /01k E VUZ23 ?? DJJL F I 1V9j )& **:6AfI a  Mrc  t}t|}t}t}t }d|d|d|d|d }t ||||f|j }|j|D cgc] } t| c} Scc} w)u時間帯別統計rzR as hour, COUNT(*) as total_conversations, SUM(CASE WHEN (R) THEN 1 ELSE 0 END) as unanswered_count FROM conversations WHERE rz ORDER BY hour ASC r2r9r<r>rErOrUrVrW rPrQr0r6rY hour_extractrorFrZr[s rget_hourly_statisticsr9s  D  F I$&L13O N+,-k  E VUZ23 ?? DJJL!% &#DI && &6B c  t}t|}t}t}t }d|d|d|d|d }t ||||f|j }|j|D cgc] } t| c} Scc} w)u'曜日別統計（月曜=0, 日曜=6）rzY as day_of_week, COUNT(*) as total_conversations, SUM(CASE WHEN (rrz& ORDER BY day_of_week ASC ) r2r9r<r@rErOrUrVrW) rPrQr0r6rY dow_extractrorFrZr[s rget_day_of_week_statisticsrQs  D  F I*,K13O M+,-k E VUZ23 ?? DJJL!% &#DI && &rc &t}t|}t}t}t }d|d|d|d|d|d }t ||||f|j }|j|D cgc] } t| c} Scc} w)u2曜日×時間帯分布（ヒートマップ用）rz as day_of_week, zQ as hour, COUNT(*) as count FROM conversations WHERE rz, z( ORDER BY day_of_week, hour ) r2r9r<r>r@rOrUrVrW) rPrQr0r6rYrrrFrZr[s rget_hour_by_day_of_weekris  D  F I$&L*,K M Nkb/ E VUZ23 ?? DJJL!% &#DI && &s9Bclt}t|}t}d|d}t||||f|j }|j |sdddddS|Dcgc]}|d }}t t|t|z dt|t|t|dScc}w)u6平均会話ターン数（セッションあたり）zt SELECT session_id, COUNT(*) as turn_count FROM conversations WHERE z1 BETWEEN ? AND ? GROUP BY session_id r)averagemaxmintotal_sessions turn_count) r2r9r<rOrUrVr{sumlenrr) rPrQr0r6rYrFrZr[turnss rget_average_turnsrs  D  F I k  E VUZ23 ?? DJJL QqAFF*. /3S  /E /Uc%j0!45z5ze*   0s" B1c  t}t|}t}t}t }d|d|d|d|d }t ||||f|j }|j|D cgc] } t| c} Scc} w)u未解決が多い時間帯rz\ as hour, COUNT(*) as unanswered_count FROM conversations WHERE rgz) GROUP BY z, ORDER BY unanswered_count DESC rrs rget_unresolved_by_hourrs  D  F I$&L13O Nk   E VUZ23 ?? DJJL!% &#DI && &rlimitct}t|}t}d|d}t||||f|j }|j t |Dcgc]}|d c}|Scc}w)u頻出キーワード> SELECT user_message FROM conversations WHERE rb user_message)r2r9r<rOrUrV_extract_keywords rPrQrr0r6rYrFrZr[s r get_keywordsrsz  D  F Ik E VUZ23 ?? DJJL TBcc.1BE JJBs A2ct}t|}t}t}d|d|d}t ||||f|j }|j t|D cgc]} | d c} |Scc} w)u!未解決に多いキーワードrrgrer)r2r9r<rErOrUrVr) rPrQrr0r6rYrorFrZr[s rget_unresolved_keywordsrs  D  F I13Ok  E  VUZ23 ?? DJJL TBcc.1BE JJBs+ A?messagescddl}hd}i}|D]J}|s|jd|}|D].}||vst|dk\s|j|ddz||<0Lt |j dd }|d|S) u-メッセージからキーワードを抽出rN>"かがでとにのはをあるいるこのするそのたいていてるですとはどうどのないなるなんますれる しまう できる ですか ほしい ますか られる おねがい ください についてu*[ぁ-んァ-ヶー一-龠々〆ヵヶ]{2,}rrwc |dSNrwrxs rz#_extract_keywords..s QqTrTkeyreverse)refindallrgetsorteditems) rrr stop_wordskeywordsmessagewordswordsorted_keywordss rrrs jJ H;  H'R ;D:%#d)q.!)dA!6!: ; ;X^^->4PO 6E ""rct}t|}t}t}d|d|d}t ||||f|j }|j |Dcgc] }t|c}Scc}w)u$回答できなかった質問一覧z] SELECT user_message, timestamp, session_id FROM conversations WHERE rgz&) ORDER BY timestamp DESC )r2r9r<rErOrUrVrW) rPrQr0r6rYrorFrZr[s rget_unanswered_questionsrs  D  F I13Ok   E VUZ23 ?? DJJL!% &#DI && &s&A;ct}t|}t}d|d}t|||||f|j }|j |Dcgc] }t |c}Scc}w)u低評価フィードバックzQ SELECT question, answer, timestamp FROM feedbacks WHERE zf BETWEEN ? AND ? AND feedback = 'dislike' ORDER BY timestamp DESC LIMIT ? rTrs rget_recent_dislikedrsv  D  F Ik E VUZ59: ?? DJJL!% &#DI && &sA/c Vt}t|}t}d|d|d|d|d }t||||f|j }|j g}|D]D}t |} t| ddr| djd| d<|j| F|S) u!日別フィードバック統計rz as date, SUM(CASE WHEN feedback = 'like' THEN 1 ELSE 0 END) as likes, SUM(CASE WHEN feedback = 'dislike' THEN 1 ELSE 0 END) as dislikes, COUNT(*) as total FROM feedbacks WHERE rrrrrr) r2r9r<rOrUrVrWrrrB) rPrQr0r6rYrFrZrr[rs rget_feedback_daily_statsrs  D  F I K k E VUZ23 ?? DJJL F I 1V9j )& **:6AfI a  Mrcvtjjtjjtjjtd}i}tjj |s|S t |dd5}|D]g}|j}d|vs|jdd\}}|jdd}||vsF|jjd ||<i d d d |S#1swY|SxYw#t$r}td |Yd }~|Sd }~wwxYw) u<category.txtからカテゴリマッピングを読み込みz category.txtrzutf-8)encoding:rwrur"Nu/カテゴリファイル読み込みエラー: ) r&r'r)r*r,existsopenstripsplit Exceptionprint) category_path categoriesflinecat_idcat_namemain_ides rload_category_mappingr 9sGGLL1J!K^\MJ 77>>- ( E -w 7 J1 Jzz|$;'+zz#q'9$FH$ll3/2Gj0.6nn.>.D.DS.I 7+ J J  J  E ?sCDD EsBDD ,-D $D ?D DDD D8D33D8c|ddl}t}t|}t}d|d}t ||||f|j }|j i} |D]<} | dxsd} |jd| } | D]} | j| ddz| | <>t| jd d }|d|S) u'よく参照されるQ&AランキングrNC SELECT assistant_message FROM conversations WHERE rbassistant_messager\*\*Q(\d+(?:-\d+)?):rwc |dSrrrs rrz&get_qa_usage_ranking..ks !rTr) rr2r9r<rOrUrVrrrr)rPrQrrr0r6rYrFrZ qa_countsr[msgidsqa_id sorted_qas rget_qa_usage_rankingrQs  D  F Ik E VUZ23 ?? DJJLI;%&,"jj0#6 ;E(}}UA6:Ie  ; ;y(ndKI Ve rc0ddl}t}t|}t}d|d}t ||||f|j }|j t} i} |D]i} | dxsd} |jd| } | D]G}|jdd}| j|d |}|| vr|dd | |<| |d xxd z cc<Ikt| jd d}|Dcgc]\}}||d|d f}}}|d|Scc}}w)u'カテゴリ別Q&A参照ランキングrNr rbr rrruu カテゴリ)namercrcrwc|ddS)Nrwrcrrs rrz,get_category_usage_ranking..sad7mrTrr) rr2r9r<rOrUrVr rrrrr)rPrQrrr0r6rYrFrZrcategory_countsr[rrrrrsorted_categoriesdatars rget_category_usage_rankingros^  D  F Ik E VUZ23 ?? DJJL'(JO 2%&,"jj0#6 2E[[%a(F!~~f VH.EFH_,3;a*H' F #G , 1 , 2 2 #IZ Z vtF|T']3 ZF Z &5>[s5D)r)) )9__doc__r-r&sysrrcalendarrr'insertr*r+r,configrrrr r r r r r ImportErrorrrr2r9r<r>r@rEstrrJtuplerOr\r`rqintrrrrrrrrrlistrrrrr rrrrrr)sX (277??277??277??83L#MNO ! 5 08B ##,C,, '#''$'c'S'$J#JJZ$$S$NSCB'c'S'0'3'#'0''s'0#<'s'c'0KSKCKK"KKsK3K&##S#0'''('C'3's'(<0SC<+3+#+c+kJsE**E43E4