9.8 KiB
9.8 KiB
Project Data Management Premises
- Do not implement a frontend administration page at this stage. Do not add create, update, or delete features for individual information records unless a future approved requirement explicitly expands the scope.
- Supply and maintain the data required by this project through Excel-based bulk upload instead of individual administration features.
- Keep user and file data outside the Excel bulk-import scope. Import the remaining project data from one Excel workbook containing multiple sheets.
- The Excel workbook has eleven required table-named sheets:
t_wave,t_wave_info,t_survey,t_survey_item,t_part,t_part_item,t_part_item_sub,t_part_item_base,t_part_item_chart,t_part_item_chart_highlight, andt_part_item_data. Headers use database names and constrained headers list every allowed value in parentheses. The workbook must contain all referenced logical parents. - Omit every auto-increment
_idxcolumn from Excel. Replacement imports regenerate identities. - Reject duplicate Excel keys: Wave info
(wave_cd,wave_info_order); survey item(wave_cd,survey_cd,survey_item_order); base(wave_cd,part_cd,part_item_cd,survey_cd,part_item_sub_cd); chart/highlight omitsurvey_cd; data addspart_item_data_orderto base. - Define the Excel import/export schema once with KriCommon
ExcelColumnDTOs. Use KriCommonExcelMapperfor workbook mapping and keep only project-specific type, value, and cross-sheet relationship rules in a consumer-local validator; do not duplicate a general-purpose Excel parser. - Excel endpoints are
POST /api/v1/excel/import,GET /api/v1/excel/template,GET /api/v1/excel/waves/{waveCd}/export, andDELETE /api/v1/excel/waves/{waveCd}. Downloads use the same eleven sheets, order, and headers; a missing Wave exports an empty workbook. - Imports atomically replace every included Wave: validate first, delete all eleven managed tables, then insert. Failures restore prior data. Standalone reset uses the same child-to-root deletion and retains
t_filemetadata and physical files, allowing temporary orphans until thatwave_cdis reimported. - A reference-file upload writes both the physical file and its
t_filemetadata. AcceptfileTitleandfileOrderas multipart request fields, derivefile_typefrom the lowercase filename extension, and keep the original filename asfile_name. - For reference-file uploads, accept
waveCdas a path variable and store files under{kri.file.base-dir}/References/{waveCd}using their original names. Treat(wave_cd, file_order)as the upload logical key: repeat uploads preservefile_idx, replace metadata and the physical file, and remove the prior filename after commit. Reject a filename already used by another order in the same Wave. - Reject a reference-file upload when its
waveCddoes not exist. Coordinate the database transaction and filesystem replacement so a database rollback restores the previous file or removes a newly created file.
Dashboard Data Model Contract
- Treat
t_waveas the root of the dashboard hierarchy. Most child data is logically scoped bywave_cd. - Preserve this logical hierarchy:
t_waveowns wave information, surveys, file metadata, and parts; a survey owns survey items; a part owns part items; and a part item owns optional sub-items, base counts, and result data. - Do not add physical database foreign keys. The backend must validate all logical parent-child relationships during Excel import and any future data mutation.
- Treat
(wave_cd, part_cd, part_item_cd)as the logical item key. Addsurvey_cdto identify a survey-specific result set and addpart_item_sub_cdwhen a sub-item exists. t_part_item_subhas no physical primary key in the planned schema. Treat(wave_cd, part_cd, part_item_cd, part_item_sub_cd)as its logical identifier and lookup key.- Store the nullable
t_part_item_sub.part_item_sub_commentvalue and expose it aspartItemSubCommentin Part-detail sub-item responses. - A normal item has no
t_part_item_subrows, and both base and data rows must usepart_item_sub_cd = NULL. Never use0as a substitute for thisNULLvalue. - An item with sub-items has one or more
t_part_item_subrows. Every non-nullpart_item_sub_cdin its base and data rows must reference a sub-item belonging to the same(wave_cd, part_cd, part_item_cd). - Every survey code used by
t_part_item_baseort_part_item_datamust belong to the same Wave. Survey items must likewise belong to an existing(wave_cd, survey_cd). - Store four
VARCHAR(8)survey palette colors: requiredsurvey_colorplus nullable highlight, negative, and negative-highlight colors. Include all in Excel/API; fall back to default, default, and negative/highlight/default respectively. - Use
t_part_item_baseas the preferred source for determining which survey audiences apply to an item. Display the single matchingsurvey_aliaswhen only one audience applies; display공통when all defined audiences apply. - Store an optional display string in
t_part_item_base.base_text(VARCHAR(128)). Do not reconstruct it frombase_count, and do not retain the removedbase_pre_textorbase_post_textfields in entities, Excel contracts, or query assembly. - Use the corresponding
*_ordercolumn for API and screen ordering:wave_info_order,survey_order,survey_item_order,file_order,part_order,part_item_order, andpart_item_data_order. Usepart_item_sub_cdas the default sub-item order because the schema has no separate sub-item order column. - Use the same
t_part_item_datarows for both chart and statistical-table views.is_chart_visibleis a required Boolean column with database default1; statistical tables use every row, while charts omit categories and matching values whose flag is false. Do not introduce a separate table-data source that could diverge from chart values. - Chart/highlight key is
(wave_cd,part_cd,part_item_cd,part_item_sub_cd); one config applies to all survey series in its group. Chart type/gap default toBAR/NONE; colors come from survey palettes. - Resolve chart highlights independently and return mode/value without calculating highlighted categories. Supported modes are
MAX_VALUE,LAST_VISIBLE, andSPECIFIED; onlySPECIFIEDaccepts a non-null comma-separated integer list. Highlight colors come only from the survey palette. - Store item and sub-item negative mode/order independently (
DEFAULT,NEGATIVE,SPECIFIED); sub settings never inherit parent settings. OnlySPECIFIEDaccepts an order list. Return these settings and survey palette colors so the frontend classifies rows and applies final colors. - Treat null
part_item_sub_cdvalues as equal when matching normal-item chart/highlight settings. Validate Excel logical-key uniqueness, null sub codes for normal items, negative/highlight order syntax, and palette colors as#RRGGBB. - Avoid N+1 query patterns when assembling a Wave dashboard. Fetch the required Part, Item, Sub, Base, and Data ranges in batches and assemble the response hierarchy in the backend.
Frontend API Contract
- Provide screen-oriented APIs rather than exposing table-shaped CRUD endpoints. Use
GET /api/v1/wavesfor the header Wave list,GET /api/v1/waves/{waveCd}for the Wave overview/navigation/download model, andGET /api/v1/waves/{waveCd}/parts/{partCd}for all items and result data in one Part. - Return every Wave in the header list and include both
isOpenand a backend-computedaccessiblevalue. A user withuserRole < 2010may access open and closed Waves; a user withuserRole >= 2010, or with no role, may access only Waves whoseis_openis true. - Revalidate Wave access on both Wave-detail and Part-detail requests. Throw a KriCommon
BusinessExceptionwith forbidden status when a restricted user requests a closed Wave; frontend visibility is not an authorization boundary. - Assemble normal and sub-item results through one
resultGroupsresponse shape. A normal item has one group withsubItem = null; an item with sub-items has one group per Sub inpart_item_sub_cdorder. - Return categories as objects containing
order,label, andchartVisible, alongside complete survey value series. The frontend uses all category/value indexes for statistical tables and filters matching indexes only for chart rendering. Reject Excel imports when category labels, display orders, or chart-visibility flags differ between survey series in the same Item/Sub result group. - Expose effective
color,highlightColor,negativeColor, andnegativeHighlightColorin both Wave-detail surveys and Part-detail series. Return item/sub-itemnegativeModeand nullablenegativeOrder; the frontend classifies and colors chart rows. - Return the stored nullable
t_part_item_base.base_textvalue unchanged as Part-detailseries[].baseText;baseCountremains a separate numeric field. - Return
series[].chartGapMode; the frontend appliesNONE,ALL, orLAST_VISIBLE, where the last mode uses the greatest visiblepart_item_data_order. - When importing
part_item_data_value, round to scale 10 withRoundingMode.HALF_UPbefore validating and storing it asDECIMAL(14,10). Reject only values that still exceed precision 14 after rounding. - When
part_item_commentis blank in Excel, store an empty string rather thanNULLbecause the database column is required. Preserve the existingNULLsemantics of other optional Excel fields. - Manage chart types as code enums in
t_part_item_chart. The initial supported type isBAR; Excel accepts its labelbarcase-insensitively, the database stores codeBAR, and Part-detail series responses return codeBAR. Reject unsupported labels during Excel validation. - Build file download URLs in the backend from the configured file resource path and the stored file metadata. Do not require the frontend to construct filesystem paths.