\w5i'dZddlmZddlmZddlmZddlmZddl m Z ddl m Z eGdd Z Gd d ZGd d Zy)z*Google Sheets client for reading KPI data.) annotations) dataclass)Path)Any) Credentials)buildcLeZdZUdZded<ded<ded<d d dZed d Zy ) SheetDatazContainer for sheet data.z list[str]headerszlist[dict[str, Any]]rowszlist[list[Any]]rawci}|jD]]}|j|}|si}|jD]$\}}||k(r |j|}| |||<&||t |<_|S)zConvert to period-based dictionary. Returns: Dictionary mapping period to column values. Example: {"2024-01": {"leads": 1000, "revenue": 50000}} )r getitems _parse_numberstr) self period_columnresultrowperiodvalueskeyvalueparseds f/var/www/tkim.planitai.co.jp/gemegg/20251207-make-pdf-report/project/planitai-kpi/src/sheets/client.pyto_period_dictzSheetData.to_period_dicts/199 )CWW]+F')F!iik ) U-'++E2%"(F3K  )#)F3v;  ) c||dk(ry t|jddjddj}t|S#tt f$rYywxYw)zParse value to number.N,%)rreplacestripfloat ValueError TypeError)rcleans rrzSheetData._parse_number/sd =ERK J&&sB/77R@FFHE< I&  sAAA A N)r)rrreturndict[str, dict[str, float]])rrr) float | None)__name__ __module__ __qualname____doc____annotations__r staticmethodrrrr r s0#   4  rr ceZdZdZdgZd dZed dZd dZ d ddZ ddZ e dd Z y)GoogleSheetsClientzGoogle Sheets API client.z5https://www.googleapis.com/auth/spreadsheets.readonlyc2t||_d|_y)zInitialize with service account credentials. Args: credentials_path: Path to service account JSON file. N)rcredentials_path_service)rr6s r__init__zGoogleSheetsClient.__init__Bs !%%5 6 rc|jGtjt|j|j }t dd||_|jS)zLazy-load the Sheets service.)scopessheetsv4) credentials)r7rfrom_service_account_filerr6SCOPESr)rcredss rservicezGoogleSheetsClient.serviceKsN == 99D))*4;;E"(DeDDM}}rc|jjjj||j }|jdg}|st gggS|dDcgc]}t |j}}g}|ddD]D}i} t|D]!\} } | t|kr || | | <d| | <#|j| Ft |||Scc}w)zRead data from a sheet range. Args: sheet_id: Google Sheets ID. range_name: Range in A1 notation (e.g., "Sheet1!A1:Z100"). Returns: SheetData containing headers, rows, and raw data. ) spreadsheetIdranger)r r r rN) rA spreadsheetsrrexecuter rr$ enumeratelenappend) rsheet_id range_namerrhr r row_valuesrow_dictiheaders r read_rangezGoogleSheetsClient.read_rangeUs LL % % ' VX SxzS : WY Hb)Rbb9 9+1!95a3q6<<>55%' * "J')H&w/ , 6s:&'1!}HV$'+HV$  , KK ! "t@@6s1 C/Nc|r|j|||xsiS|j||}|j|}i} |r#|j||} | j|} || fS)aGet KPI actual and target data. Args: sheet_id: Google Sheets ID. data_range: Range for actual data. target_range: Optional range for target data. period_column: Column name for period. pivot_format: If True, parse as pivot table format. pivot_config: Configuration for pivot format parsing. Returns: Tuple of (actual_data, target_data) as period-based dictionaries. )get_kpi_data_pivotrRr) rrK data_range target_ranger pivot_format pivot_config actual_sheet actual_data target_data target_sheets r get_kpi_datazGoogleSheetsClient.get_kpi_dataysy, **8ZASQST Tx< "11-@ 46 ??8\BL&55mDKK''rc|j||}|j}|siifS|jdd}|jdd}|jdd}|jdd} |jd d} |jd d} |jd d } |jd d} |jdi}g}g}t||kDr||}| }|t|kr}|t|kr||nd}|rRt |j r9|j t |j |j ||| z }|t|kr}|siifS|Dcic]}|i}}|Dcic]}|i}}t|t|D]}||}t|| kr|| rt || j nd}|s=|j||}tt||D]K\}\}}|| z}|| z}|j||}|j||} |||||<| D| |||<M||fScc}wcc}w)uParse pivot table format sheet. Expected format: - Row with periods (e.g., 2025-04, 2025-05, ...) - Row with headers (e.g., 予算, 実績, 差異, 達成率) repeated for each period - Data rows with item names and values Args: sheet_id: Google Sheets ID. data_range: Range to read. config: Pivot configuration with: - period_row: Row index for periods (0-based), default 3 - header_row: Row index for headers (0-based), default 4 - data_start_row: Row index where data starts, default 5 - item_col: Column index for item names, default 1 - data_start_col: Column index where data starts, default 4 - cols_per_period: Number of columns per period, default 4 - budget_col_offset: Offset for budget column within period, default 0 - actual_col_offset: Offset for actual column within period, default 1 - item_mapping: Mapping of sheet item names to KPI IDs Returns: Tuple of (actual_data, target_data) as period-based dictionaries. period_row header_rowdata_start_rowitem_colrEdata_start_colcols_per_periodbudget_col_offsetractual_col_offset item_mappingr ) rRr rrIrr$rJrDrHzip_parse_cell_value)!rrKrUconfig sheet_datar r_rarcrerfrgrhrirjperiods period_colsperiod_row_datacol period_valprZr[row_idxr item_namekpi_idrPr budget_col actual_col budget_val actual_vals! rrTz%GoogleSheetsClient.get_kpi_data_pivots<__Xz: nnr6MZZ a0 ZZ a0 $4a8::j!,$4a8 **%6:"JJ':A>"JJ':A>zz."5  !# s8j !*oO CO,,583;O5O_S1UW #j/"7"7"9NN3z?#8#8#:;&&s+& O,,r6MDK3KaArE3K 3KCJ3KaArE3K 3K^SX6 =Gg,C3x8#69(mCM*002I"%%i;F%.c';.G$H = =FC #44  #44 !33CD !33CD )2rsK0"!5+ ,, ,^OOd''r