flowchart TD A["employeesOnboardPreprocess file_id"] --> B{"file row exists"} B -->|no| Z1["return file not found"] B -->|yes| C{"physical xlsx exists"} C -->|no| Z2["file status failed"] C -->|yes| D["columns_to_check from file action"] D --> E["getPolicyDetails"] E --> F["load spreadsheet rangeToArray"] F --> G["getPolicySlabRatesForEmpOnboard"] G --> H["getExisitingUnits"] H --> I["data_group_by_family from excel"] I --> J{"dependent_addition"} J -->|yes| K["merge DB family transform_db_data_to_excel"] K --> L["self_rata_premium on dependents"] J -->|no| M["for each family"] L --> M M --> N["calculate_premium_new"] N --> O["employeesOnboardProcess"] O --> P{"insert count gt 0"} P -->|yes| Q["file status success"] P -->|no| R["file failed rack slab hint"]

flowchart TD START([Start calculate_premium_new]) START --> INIT[Initialize Variables] INIT --> GROUP_SLAB[Group slab details based on rack rate name] GROUP_SLAB --> FAMILY_COMP[Construct incoming family composition & counts] FAMILY_COMP --> LOOP_SLABS_START{{Loop Each Grouped Slab}} %% ========================================================= %% SLAB APPLICABILITY SECTION %% ========================================================= LOOP_SLABS_START --> COMPARE_SLAB[Compare incoming family composition with configured slab] COMPARE_SLAB --> SLAB_APPLICABLE{Is Slab Applicable?} SLAB_APPLICABLE -->|No| MARK_NOT_APPLICABLE[Mark slab as NOT applicable] MARK_NOT_APPLICABLE --> NEXT_SLAB SLAB_APPLICABLE -->|Yes| MARK_APPLICABLE[Mark slab as applicable] MARK_APPLICABLE --> STORE_MEMBERS[Store applicable members] STORE_MEMBERS --> GET_APPLICABLE_MEMBERS[Get applicable family members from overall family] GET_APPLICABLE_MEMBERS --> INIT_SELF_FLAG[Initialize acting self flag = false] INIT_SELF_FLAG --> LOOP_APPLICABLE_MEMBERS{{Loop Applicable Members}} LOOP_APPLICABLE_MEMBERS --> CHECK_SELF{Is acting self already assigned?} CHECK_SELF -->|No| CHECK_RELATIONSHIP{Relationship = Self ?} CHECK_RELATIONSHIP -->|Yes| SET_ACTING_SELF_TRUE[Set acting self = true] CHECK_RELATIONSHIP -->|No| SET_FIRST_AS_SELF[Set first applicable member as acting self] SET_ACTING_SELF_TRUE --> UPDATE_SELF_FLAG SET_FIRST_AS_SELF --> UPDATE_SELF_FLAG UPDATE_SELF_FLAG[Set self flag assigned = true] --> UPDATE_MEMBER_TEMP CHECK_SELF -->|Yes| UPDATE_MEMBER_TEMP UPDATE_MEMBER_TEMP[Update member temp data: - max age - max count - grid name - grid master - acting self - premium type] UPDATE_MEMBER_TEMP --> MORE_APPLICABLE_MEMBERS{More applicable members?} MORE_APPLICABLE_MEMBERS -->|Yes| LOOP_APPLICABLE_MEMBERS MORE_APPLICABLE_MEMBERS -->|No| NEXT_SLAB NEXT_SLAB --> MORE_SLABS{More slabs available?} MORE_SLABS -->|Yes| LOOP_SLABS_START %% ========================================================= %% FAMILY MEMBER PROCESSING SECTION %% ========================================================= MORE_SLABS -->|No| LOOP_FAMILY_START{{Loop Each Family Member}} LOOP_FAMILY_START --> SET_GRID_INFO[Set grid info into fileArr] SET_GRID_INFO --> COPY_SELF_VALUES[Copy self values: - SI - Grade - Unit to current member] COPY_SELF_VALUES --> CHECK_UNIT{Is Unit Empty?} CHECK_UNIT -->|Yes| SET_DEFAULT_UNIT[Assign default existing unit] CHECK_UNIT -->|No| TRANSFORM_MEMBER SET_DEFAULT_UNIT --> TRANSFORM_MEMBER TRANSFORM_MEMBER[Transform Excel row to DB structure] TRANSFORM_MEMBER --> CHECK_ACTION{Action NOT in D/C/SI ?} CHECK_ACTION -->|Yes| GENERATE_REL_CODE[Generate relationship code & emp type] CHECK_ACTION -->|No| BUILD_CONDITIONS GENERATE_REL_CODE --> BUILD_CONDITIONS %% ========================================================= %% CONDITIONS SECTION %% ========================================================= BUILD_CONDITIONS[Build Premium Calculation Conditions] BUILD_CONDITIONS --> PRIMARY_GRID_CONDITION[ Build Primary Grid Type Condition ] PRIMARY_GRID_CONDITION --> FINAL_CONDITION{ Allow Premium Calculation? } FINAL_CONDITION -->|No| NEXT_MEMBER FINAL_CONDITION -->|Yes| PREMIUM_MANAGER[Call Premium Calculation Manager] PREMIUM_MANAGER --> STORE_RESULT[Append transformed member to result] STORE_RESULT --> NEXT_MEMBER NEXT_MEMBER --> MORE_MEMBERS{More family members?} MORE_MEMBERS -->|Yes| LOOP_FAMILY_START %% ========================================================= %% FINAL VALIDATION SECTION %% ========================================================= MORE_MEMBERS -->|No| CHECK_DEPENDENT_ADDITION{ File Action = dependent_addition? } CHECK_DEPENDENT_ADDITION -->|Yes| VALIDATE_FLOATER[ Validate Family Floater Rata Premium ] CHECK_DEPENDENT_ADDITION -->|No| RETURN_RESULT VALIDATE_FLOATER --> RETURN_RESULT RETURN_RESULT([Return Result])

flowchart TD START([Start get_familiy_composition]) START --> INIT["Initialize family_composition array and slug service"] INIT --> LOOP_MEMBERS{"Loop each family member"} LOOP_MEMBERS --> GET_REL["Read relationship from row[5]"] GET_REL --> SLUGIFY["Slugify relationship"] %% SELF SLUGIFY --> CHECK_SELF{"relationship == self"} CHECK_SELF -->|Yes| SET_SELF_ONE["Set self = 1"] CHECK_SELF -->|No| CHECK_SELF_EXISTS{"self key exists?"} CHECK_SELF_EXISTS -->|No| SET_SELF_ZERO["Set self = 0"] CHECK_SELF_EXISTS -->|Yes| CHECK_SPOUSE SET_SELF_ONE --> CHECK_SPOUSE SET_SELF_ZERO --> CHECK_SPOUSE %% SPOUSE CHECK_SPOUSE{"relationship == spouse"} CHECK_SPOUSE -->|Yes| SET_SPOUSE_ONE["Set spouse = 1"] CHECK_SPOUSE -->|No| CHECK_SPOUSE_EXISTS{"spouse key exists?"} CHECK_SPOUSE_EXISTS -->|No| SET_SPOUSE_ZERO["Set spouse = 0"] CHECK_SPOUSE_EXISTS -->|Yes| CHECK_CHILDREN SET_SPOUSE_ONE --> CHECK_CHILDREN SET_SPOUSE_ZERO --> CHECK_CHILDREN %% CHILDREN CHECK_CHILDREN{"relationship == son OR daughter"} CHECK_CHILDREN -->|Yes| INC_CHILDREN["Increment childrens count"] CHECK_CHILDREN -->|No| CHECK_CHILDREN_EXISTS{"childrens key exists?"} CHECK_CHILDREN_EXISTS -->|No| SET_CHILDREN_ZERO["Set childrens = 0"] CHECK_CHILDREN_EXISTS -->|Yes| CHECK_PARENTS INC_CHILDREN --> CHECK_PARENTS SET_CHILDREN_ZERO --> CHECK_PARENTS %% PARENTS CHECK_PARENTS{"relationship == father OR mother"} CHECK_PARENTS -->|Yes| INC_PARENTS["Increment parents count"] CHECK_PARENTS -->|No| CHECK_PARENTS_EXISTS{"parents key exists?"} CHECK_PARENTS_EXISTS -->|No| SET_PARENTS_ZERO["Set parents = 0"] CHECK_PARENTS_EXISTS -->|Yes| CHECK_INLAWS INC_PARENTS --> CHECK_INLAWS SET_PARENTS_ZERO --> CHECK_INLAWS %% PARENTS IN LAW CHECK_INLAWS{"relationship == father-in-law OR mother-in-law"} CHECK_INLAWS -->|Yes| INC_INLAWS["Increment parents-in-law count"] CHECK_INLAWS -->|No| CHECK_INLAW_EXISTS{"parents-in-law key exists?"} CHECK_INLAW_EXISTS -->|No| SET_INLAW_ZERO["Set parents-in-law = 0"] CHECK_INLAW_EXISTS -->|Yes| NEXT_MEMBER INC_INLAWS --> NEXT_MEMBER SET_INLAW_ZERO --> NEXT_MEMBER %% LOOP NEXT_MEMBER --> MORE_MEMBERS{"More family members?"} MORE_MEMBERS -->|Yes| LOOP_MEMBERS MORE_MEMBERS -->|No| RETURN_RESULT RETURN_RESULT(["Return family_composition"])

flowchart TD START([Start compare_incoming_family_slab_with_configured_slab]) START --> INIT_MAP["Initialize relationship mapping"] INIT_MAP --> INIT_RESULT["Initialize result is_applicable = false applicable_members = []"] INIT_RESULT --> READ_CONFIG["Read configured family composition from slab"] READ_CONFIG --> REMOVE_KEYS["Remove ignored keys either-parents-pil elders_count"] REMOVE_KEYS --> CHECK_CONFIG{"Configured composition has values?"} %% EMPTY CONFIGURATION CHECK_CONFIG -->|No| RESET_RESULT["Set result as not applicable"] RESET_RESULT --> RETURN_RESULT %% LOOP START CHECK_CONFIG -->|Yes| LOOP_CONFIG{"Loop configured relationships"} %% CHECK NA LOOP_CONFIG --> CHECK_NA{"Value != NA ?"} CHECK_NA -->|No| NEXT_RELATION %% CHECK KEY EXISTS CHECK_NA -->|Yes| CHECK_KEY_EXISTS{"Relationship exists in incoming composition?"} CHECK_KEY_EXISTS -->|No| INVALID_RESULT_1["Set: is_applicable = false applicable_members = []"] INVALID_RESULT_1 --> BREAK_LOOP %% VALUE COMPARISON CHECK_KEY_EXISTS -->|Yes| CHECK_MATCH{ Incoming count == configured count OR configured value == any } %% MATCH FOUND CHECK_MATCH -->|Yes| SET_APPLICABLE["Set is_applicable = true"] SET_APPLICABLE --> MERGE_MEMBERS["Merge mapped relationships into applicable_members"] MERGE_MEMBERS --> NEXT_RELATION %% MATCH FAILED CHECK_MATCH -->|No| INVALID_RESULT_2["Set: is_applicable = false applicable_members = []"] INVALID_RESULT_2 --> BREAK_LOOP %% LOOP CONTROL NEXT_RELATION --> MORE_RELATIONS{"More relationships?"} MORE_RELATIONS -->|Yes| LOOP_CONFIG MORE_RELATIONS -->|No| RETURN_RESULT BREAK_LOOP --> RETURN_RESULT %% RETURN RETURN_RESULT(["Return result array"])

flowchart TD START([Start get_applicable_familiy_members]) START --> INIT["Initialize: index = [] max_age = [] max_count = 0"] INIT --> INIT_SLUG["Initialize slug service"] INIT_SLUG --> LOOP_MEMBERS{"Loop each family member"} %% READ RELATIONSHIP LOOP_MEMBERS --> READ_REL["Read relationship from family_member[5]"] READ_REL --> SLUGIFY["Slugify relationship"] %% CHECK APPLICABLE SLUGIFY --> CHECK_APPLICABLE{ Relationship exists in applicable_members? } %% MATCH FOUND CHECK_APPLICABLE -->|Yes| STORE_INDEX["Add member index into result.index"] STORE_INDEX --> CALCULATE_AGE["Calculate member age from DOB"] CALCULATE_AGE --> STORE_AGE["Add age into result.max_age"] STORE_AGE --> NEXT_MEMBER %% NO MATCH CHECK_APPLICABLE -->|No| NEXT_MEMBER %% LOOP CONTROL NEXT_MEMBER --> MORE_MEMBERS{"More family members?"} MORE_MEMBERS -->|Yes| LOOP_MEMBERS %% FINAL COUNT MORE_MEMBERS -->|No| CALCULATE_COUNT["Set max_count = count(result.index)"] CALCULATE_COUNT --> RETURN_RESULT %% RETURN RETURN_RESULT(["Return result array"])

flowchart TD T["transform_excel_data_to_db"] --> G{"need relationship_code"} G -->|not deletion correction SI| R["generate_relationship_code"] G -->|skip| C2["build conditions"] R --> C2 C2 --> P{"excel upload or enrollment or DA primary single self path"} P -->|yes| M["premium_calculation_manager"] P -->|no| X["member not added to priced result"]

flowchart TD START([Start premium_calculation_manager]) START --> INIT[Initialize Logger, Slug Service, Grid Type, Slab Index] INIT --> CHECK_GRID{Grid Name Available?} CHECK_GRID -->|No| RETURN_FALSE[Return False] CHECK_GRID -->|Yes| GET_SLAB_RATES[Get Current Slab Rates] %% ===================================================== %% DRAFT / AUDIT HISTORY SECTION %% ===================================================== GET_SLAB_RATES --> CHECK_EMP_DRAFT{ Employee Source = Enrollment AND Emp Status = Draft AND Policy Status = Draft ? } CHECK_EMP_DRAFT -->|Yes| FETCH_EMP_AUDIT[Fetch Original Employee Audit Data] CHECK_EMP_DRAFT -->|No| CHECK_POLICY_DRAFT FETCH_EMP_AUDIT --> CHECK_POLICY_DRAFT CHECK_POLICY_DRAFT{ Fetch Original Policy Audit Data? } CHECK_POLICY_DRAFT -->|Yes| FETCH_POLICY_AUDIT[Fetch Original Policy Audit Records] CHECK_POLICY_DRAFT -->|No| CHECK_ADDITION_ACTION FETCH_POLICY_AUDIT --> CHECK_ADDITION_ACTION %% ===================================================== %% ADDITION ACTION SECTION %% ===================================================== CHECK_ADDITION_ACTION{ Action = DA or A ? } CHECK_ADDITION_ACTION -->|No| INIT_CALCULATION CHECK_ADDITION_ACTION -->|Yes| FETCH_INSURER FETCH_INSURER[Fetch Insurer Master] FETCH_INSURER --> CHECK_ADD_DAY{ addition_add_day enabled? } CHECK_ADD_DAY -->|Yes| ADD_ONE_DAY[Add +1 day to coverage date] CHECK_ADD_DAY -->|No| INIT_CALCULATION ADD_ONE_DAY --> INIT_CALCULATION %% ===================================================== %% MAIN CALCULATION SECTION %% ===================================================== INIT_CALCULATION[Initialize: - GST - is_match_found = false] INIT_CALCULATION --> SWITCH_GRID{{Switch Grid Type}} %% ===================================================== %% GRID TYPE 1 %% ===================================================== SWITCH_GRID --> GRID1[Grid Type 1: GPA - SI * Multiplier] GRID1 --> GRID1_LOOP{{Loop Slab Rates}} GRID1_LOOP --> GRID1_MATCH{ SI + Unit Match? OR Grade + SI + Unit Match? } GRID1_MATCH -->|Yes| COMMON_ASSIGNMENT_1 GRID1_MATCH -->|No| GRID1_NEXT GRID1_NEXT --> MORE_GRID1{More Slabs?} MORE_GRID1 -->|Yes| GRID1_LOOP MORE_GRID1 -->|No| CHECK_AUTO_CALC CHECK_AUTO_CALC{ SI/BP Type = Basic Pay? } CHECK_AUTO_CALC -->|No| END_GRID1 CHECK_AUTO_CALC -->|Yes| AUTO_CALC_SI AUTO_CALC_SI[Auto Calculate: - SI using Basic Pay - Premium using Multiplier] AUTO_CALC_SI --> COMMON_ASSIGNMENT_1 COMMON_ASSIGNMENT_1[ Assign: - SI - Coverage Dates - Days - Premium - Rata Premium - GST Set Match Found = true ] COMMON_ASSIGNMENT_1 --> END_GRID1 END_GRID1 --> POST_SWITCH %% ===================================================== %% GRID TYPE 2 %% ===================================================== SWITCH_GRID --> GRID2[Grid Type 2: GPA Flat Rate] GRID2 --> SIMPLE_SI_MATCH_2 SIMPLE_SI_MATCH_2{ SI + Unit Match? } SIMPLE_SI_MATCH_2 -->|Yes| COMMON_ASSIGNMENT_2 SIMPLE_SI_MATCH_2 -->|No| POST_SWITCH COMMON_ASSIGNMENT_2[ Assign Premium Details Set Match Found = true ] COMMON_ASSIGNMENT_2 --> POST_SWITCH %% ===================================================== %% GRID TYPE 3 %% ===================================================== SWITCH_GRID --> GRID3[Grid Type 3: GMC - SI] GRID3 --> SIMPLE_SI_MATCH_3 SIMPLE_SI_MATCH_3{ SI + Unit Match? } SIMPLE_SI_MATCH_3 -->|Yes| COMMON_ASSIGNMENT_3 SIMPLE_SI_MATCH_3 -->|No| POST_SWITCH COMMON_ASSIGNMENT_3[ Assign Premium Details Set Match Found = true ] COMMON_ASSIGNMENT_3 --> POST_SWITCH %% ===================================================== %% GRID TYPE 4 - 7 %% ===================================================== SWITCH_GRID --> GRID4TO7[Grid Types 4-7: Age Based Calculation] GRID4TO7 --> CALCULATE_AGE[Calculate Employee Age] CALCULATE_AGE --> AGE_MATCH_LOOP{{Loop Slab Rates}} AGE_MATCH_LOOP --> AGE_MATCH{ SI + Unit + Age Band Match? } AGE_MATCH -->|Yes| AGE_ASSIGNMENT AGE_MATCH -->|No| AGE_NEXT AGE_NEXT --> MORE_AGE_SLABS{More Slabs?} MORE_AGE_SLABS -->|Yes| AGE_MATCH_LOOP MORE_AGE_SLABS -->|No| POST_SWITCH AGE_ASSIGNMENT[ Assign: - Premium - Age Band - GST - Rata Premium Set Match Found = true ] AGE_ASSIGNMENT --> POST_SWITCH %% ===================================================== %% GRID TYPE 8 %% ===================================================== SWITCH_GRID --> GRID8[Grid Type 8: Grade/Band Based SI] GRID8 --> GRID8_MATCH{ Grade + SI + Unit Match? } GRID8_MATCH -->|Yes| COMMON_ASSIGNMENT_8 GRID8_MATCH -->|No| POST_SWITCH COMMON_ASSIGNMENT_8[ Assign Premium Details Set Match Found = true ] COMMON_ASSIGNMENT_8 --> POST_SWITCH %% ===================================================== %% GRID TYPE 9 %% ===================================================== SWITCH_GRID --> GRID9[Grid Type 9: Flat Rate For All] GRID9 --> GRID9_MATCH{ SI + Unit Match? } GRID9_MATCH -->|Yes| COMMON_ASSIGNMENT_9 GRID9_MATCH -->|No| POST_SWITCH COMMON_ASSIGNMENT_9[ Assign Premium Details Set Match Found = true ] COMMON_ASSIGNMENT_9 --> POST_SWITCH %% ===================================================== %% GRID TYPE 10 %% ===================================================== SWITCH_GRID --> GRID10[Grid Type 10: Max Dependent Age] GRID10 --> GET_MAX_AGE[Get Maximum Family Age] GET_MAX_AGE --> GRID10_MATCH{ SI + Unit + Max Age Match? } GRID10_MATCH -->|Yes| GRID10_ASSIGN GRID10_MATCH -->|No| POST_SWITCH GRID10_ASSIGN[ Assign: - Premium - Age Band - GST ] GRID10_ASSIGN --> POST_SWITCH %% ===================================================== %% GRID TYPE 11 %% ===================================================== SWITCH_GRID --> GRID11[Grid Type 11: Max Family Count] GRID11 --> GET_MAX_COUNT[Get Family Member Count] GET_MAX_COUNT --> CALCULATE_FAMILY_SI[Calculate Family SI] CALCULATE_FAMILY_SI --> LIMIT_MAX_SI[Apply Max SI Limit] LIMIT_MAX_SI --> GET_PREMIUM_BY_SI[Fetch Premium using Family SI] GET_PREMIUM_BY_SI --> GRID11_ASSIGN GRID11_ASSIGN[ Assign Family Premium Details ] GRID11_ASSIGN --> POST_SWITCH %% ===================================================== %% GRID TYPE 12 %% ===================================================== SWITCH_GRID --> GRID12[Grid Type 12: Relationship Based] GRID12 --> NORMALIZE_RELATIONSHIP[Normalize Relationship] NORMALIZE_RELATIONSHIP --> GRID12_MATCH{ SI + Unit + Relationship Match? } GRID12_MATCH -->|Yes| GRID12_ASSIGN GRID12_MATCH -->|No| POST_SWITCH GRID12_ASSIGN[ Assign Premium Details ] GRID12_ASSIGN --> POST_SWITCH %% ===================================================== %% GRID TYPE 13 %% ===================================================== SWITCH_GRID --> GRID13[Grid Type 13: Relationship + Age] GRID13 --> CALCULATE_REL_AGE[Calculate Age & Normalize Relationship] CALCULATE_REL_AGE --> GRID13_MATCH{ SI + Unit + Relationship + Age Match? } GRID13_MATCH -->|Yes| GRID13_ASSIGN GRID13_MATCH -->|No| POST_SWITCH GRID13_ASSIGN[ Assign Premium + Age Band ] GRID13_ASSIGN --> POST_SWITCH %% ===================================================== %% DEFAULT %% ===================================================== SWITCH_GRID --> GRID_DEFAULT[Unknown Grid Type] GRID_DEFAULT --> LOG_GRID_ERROR[Log Grid Type Error] LOG_GRID_ERROR --> POST_SWITCH %% ===================================================== %% POST PROCESSING %% ===================================================== POST_SWITCH --> CHECK_FLOATER{ Match Found AND Relationship != Self AND Premium Type = 3 AND Addon != 3 ? } CHECK_FLOATER -->|Yes| RESET_DEPENDENT_SI[Set Dependent SI = 0] CHECK_FLOATER -->|No| CHECK_MATCH_FOUND RESET_DEPENDENT_SI --> CHECK_MATCH_FOUND %% ===================================================== %% NO MATCH SECTION %% ===================================================== CHECK_MATCH_FOUND{ Match Found? } CHECK_MATCH_FOUND -->|Yes| CHECK_DEPENDENT_DA CHECK_MATCH_FOUND -->|No| HANDLE_NO_MATCH HANDLE_NO_MATCH{ Premium Type = 1 ? } HANDLE_NO_MATCH -->|Yes| RESET_SELF_ONLY HANDLE_NO_MATCH -->|No| LOG_SLAB_NOT_FOUND RESET_SELF_ONLY[ Reset: - SI - Premium - GST - Rata Premium ] RESET_SELF_ONLY --> LOG_ERROR LOG_SLAB_NOT_FOUND[ Log: Slab Rate Not Found ] LOG_SLAB_NOT_FOUND --> LOG_ERROR LOG_ERROR[Write Error Log] LOG_ERROR --> CHECK_DEPENDENT_DA %% ===================================================== %% DEPENDENT ADDITION LOGIC %% ===================================================== CHECK_DEPENDENT_DA{ Dependent AND Acting Self Empty AND Premium Type = 1 AND Action = DA ? } CHECK_DEPENDENT_DA -->|Yes| HANDLE_DA_PREMIUM CHECK_DEPENDENT_DA -->|No| CHECK_OTHER_ACTIONS HANDLE_DA_PREMIUM[ Set: - SI = 0 - Premium = 0 Recalculate Rata Premium Difference ] HANDLE_DA_PREMIUM --> RETURN_RESULT CHECK_OTHER_ACTIONS{ Action = I/A/MI ? } CHECK_OTHER_ACTIONS -->|Yes| RESET_DEPENDENT_VALUES CHECK_OTHER_ACTIONS -->|No| RETURN_RESULT RESET_DEPENDENT_VALUES[ Reset: - SI - Premium - GST - Days ] RESET_DEPENDENT_VALUES --> RETURN_RESULT RETURN_RESULT([Return Employee Data])