nhance/app/Views/docs/eb-rack-rate-calculation.php

<?php
/**
 * EB rack rate calculation — content only
 * app/Views/docs/eb-rack-rate-calculation.php
 */
?>

<h2 id="scope">Scope and entry point</h2>

<p>
  This page documents how <strong>configured rack rates</strong> (policy premium slabs and grid metadata loaded from the DB)
  are <strong>matched to each family</strong> and how <strong>premium, pro-rata, and GST</strong> are written onto each member row
  during <strong>Excel-driven employee onboarding</strong>.
</p>

<div class="callout info">
  <span>i</span>
  <div>
    <strong>In scope here:</strong> the branch of
    <code>EmployeeServiceController::employeesOnboardPreprocess()</code> that runs when <code>$params['file_id']</code> is set
    (physical workbook under <code>WRITEPATH/uploads/excel/</code>), and Excel actions
    <strong>inception</strong>, <strong>missed_inception</strong>, <strong>addition</strong>, and <strong>dependent_addition</strong> only.
    The enrollment-to-inception branch (<code>client_policy_id</code> without a file) is out of scope.
    UI configuration of racks remains on
    <a href="<?= base_url('docs/eb-rack-rate-config') ?>">EB rack rate config</a>.
  </div>
</div>

<p>
  Primary symbols:
  <code>app/Controllers/EmployeeServiceController.php</code> → <code>employeesOnboardPreprocess()</code>;
  <code>app/Helpers/excel_util_helper.php</code> → <code>calculate_premium_new()</code> and its callees;
  <code>app/Helpers/excel_util_helper.php</code> → <code>premium_calculation_manager()</code> for grid-type-specific slab row matching.
</p>

<h2 id="inputs">Data loaded before premium</h2>

<table>
  <thead>
    <tr><th>Source</th><th>What it is</th><th>Used for</th></tr>
  </thead>
  <tbody>
    <tr>
      <td><code>clientPolicyModel->getPolicyDetails()</code></td>
      <td>Policy terms (start/end dates, insurer id, GST default, flags such as <code>is_addon</code>).</td>
      <td>Pro-rata denominators, optional +1 day on coverage for additions, post-match premium_type rules.</td>
    </tr>
    <tr>
      <td><code>policiesModel->getPolicySlabRatesForEmpOnboard()</code></td>
      <td>Array shaped as <code>['slab_rates' => [...rows...], 'grid_master' => ..., 'additional_slab_info' => ...]</code> from <code>PolicyPremium1Model</code> / <code>PolicyPremium2Model</code> plus joined <code>grid_master</code> per row.</td>
      <td>Every premium grid row (<code>rack_rate_name</code>, SI, age, grade, unit, <code>premium_type</code>, <code>additional_relationship</code> JSON, etc.).</td>
    </tr>
    <tr>
      <td><code>clientBranchModel->getExisitingUnits()</code></td>
      <td>List of valid unit names for the branch.</td>
      <td>Default unit when Excel unit column is empty; unit matching inside slab loops.</td>
    </tr>
    <tr>
      <td>Excel sheet</td>
      <td>Rows parsed to a numeric-indexed array per member (see below).</td>
      <td>Family composition, SI, DOB, dates, band, unit.</td>
    </tr>
  </tbody>
</table>

<h2 id="excel-columns">Excel row shape (numeric columns)</h2>

<p>
  After <code>rangeToArray</code>, each family member row is a 0-based array. The premium path relies heavily on these indices
  inside helpers (e.g. <code>transform_excel_data_to_db</code>, <code>get_applicable_familiy_members</code>).
</p>

<table>
  <thead>
    <tr><th>Index</th><th>Typical meaning</th></tr>
  </thead>
  <tbody>
    <tr><td><code>1</code></td><td>Employee code (family key).</td></tr>
    <tr><td><code>2</code></td><td>Name.</td></tr>
    <tr><td><code>3</code></td><td>DOB (age and age-band grids).</td></tr>
    <tr><td><code>5</code></td><td>Relationship (Self, Spouse, …) — slugified for composition and applicability.</td></tr>
    <tr><td><code>6</code></td><td>Basic cover SI.</td></tr>
    <tr><td><code>7</code></td><td>Date of coverage.</td></tr>
    <tr><td><code>9</code></td><td>Basic pay (GPA grid 1 basic-pay path).</td></tr>
    <tr><td><code>10</code></td><td>Band / grade.</td></tr>
    <tr><td><code>18</code></td><td>Unit name.</td></tr>
  </tbody>
</table>

<p>
  The <strong>first row in each grouped family array is treated as the self anchor</strong> inside <code>calculate_premium_new</code>:
  SI (<code>[6]</code>), band (<code>[10]</code>), and a few other fields are copied from <code>$family_data[0]</code> onto every member before transform.
</p>

<p>
  A family may match <strong>more than one</strong> named rack. The code walks racks in the order returned by
  <code>group_slab_rates_basedon_name</code>; each applicable rack overwrites <code>$family_data[i]['temp']</code> for the same indexes,
  so the <strong>last matching rack in iteration order</strong> wins for <code>grid_name</code>, <code>grid_master</code>, and
  <code>premium_type</code> on that row before per-member pricing runs.
</p>

<h2 id="flow-preprocess">Flow: <code>employeesOnboardPreprocess</code> (Excel path)</h2>

<p>
  High-level orchestration: validate file, resolve column set from <code>$file['action']</code>, load policy + slabs + units,
  group rows by employee code, optionally merge DB family for dependent addition, call <code>calculate_premium_new</code> per family,
  then <code>employeesOnboardProcess</code> to persist. File status becomes <code>success</code> only if at least one family inserted;
  otherwise a generic rack-configuration failure is recorded.
</p>

<div class="mermaid-wrapper" id="flowchart-employees-onboard-preprocess">
  <div class="mermaid">
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"]
  </div>
</div>

<h2 id="flow-calculate-premium">Flow: <code>calculate_premium_new</code></h2>

<p>
  Two conceptual phases: (1) <strong>rack selection</strong> — for each named rack, decide if the family matches the configured
  relationship pattern and stamp <code>temp</code> metadata on applicable Excel rows; (2) <strong>per-member pricing</strong> —
  normalize row, optionally call <code>premium_calculation_manager</code>, collect results. Dependent addition runs an extra
  normalisation pass <code>validatet_family_floter_rata_premium</code>.
</p>

<div class="mermaid-wrapper" id="flowchart-calculate-premium-new">
  <div class="mermaid">
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])
  </div>
</div>



<h2 id="flow-family-composition"><code>get_familiy_composition</code></h2>

<p>
  Builds a compact associative array of <strong>counts / presence flags</strong> from the <strong>incoming workbook family only</strong>
  (already grouped to one employee). Keys align with the JSON used in rack configuration (<code>additional_relationship</code>), except
  <code>either-parents-pil</code> and <code>elders_count</code> which are stripped before comparison.
</p>
<p>
  This function mainly:
<ul>
  <li>Reads all family members</li>
  <li>Normalizes relationship names</li>
  <li>Builds a summarized family composition object</li>
  <li>Maintains counts for:
    <ul style="padding-left: 20px;">
      <li>self</li>
      <li>spouse</li>
      <li>children</li>
      <li>parents</li>
      <li>parents-in-law</li>
    </ul>
  </li>
</ul>
</p>

<div class="mermaid-wrapper" id="flowchart-family-composition">
  <div class="mermaid">
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"])
  </div>
</div>

<h2 id="flow-compare-slab"><code>compare_incoming_family_slab_with_configured_slab</code></h2>

<p>
  The first row of each rack’s <code>slab_rates</code> carries <code>additional_relationship</code> (JSON). After removing
  <code>either-parents-pil</code> and <code>elders_count</code>, each remaining key is evaluated in sequence:
</p>

<ul>
  <li>If the configured value is <code>'NA'</code>, that dimension is ignored (does not participate in match or applicable-member list).</li>
  <li>Otherwise the incoming composition <strong>must</strong> contain the same key. If <code>incoming[key] == configured</code> <em>or</em> configured is <code>'any'</code>, the key contributes applicable relationship tokens (via an internal map: self, spouse, son/daughter, parents, in-laws).</li>
  <li>On the first failed key, the rack is rejected (<code>is_applicable</code> false, applicable members cleared) and the loop stops.</li>
  <li>If the decoded JSON is empty, the rack is not applicable.</li>
</ul>

<div class="mermaid-wrapper" id="flowchart-compare-slab">
  <div class="mermaid">
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"])

  </div>
</div>

<h2 id="flow-applicable-members"><code>get_applicable_familiy_members</code> and <code>acting_self</code></h2>

<p>
  Given the list of relationship strings that matched the rack (e.g. <code>self</code>, <code>spouse</code>, <code>son</code>), this helper returns:
</p>

<ul>
  <li><code>index</code>: Excel row indexes whose slugified relationship is in that list.</li>
  <li><code>max_age</code>: list of ages (years from DOB column <code>[3]</code> to “today” in <code>calculate_days_bw_dates</code>).</li>
  <li><code>max_count</code>: count of those indexes (used by grids 10 and 11).</li>
</ul>

<p>
  <code>calculate_premium_new</code> then walks <code>index</code> in order and sets <code>acting_self</code>: the <strong>first</strong> applicable member
  receives <code>acting_self = true</code>; subsequent applicable members get <code>false</code>. Combined with relationship checks later in
  <code>premium_calculation_manager</code>, this distinguishes who carries floater-style premium when <code>premium_type == 1</code>.
</p>

<div class="mermaid-wrapper" id="flowchart-applicable-members">
  <div class="mermaid">
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"])
  </div>
</div>

<h2 id="flow-per-member">Per-member transform and premium gate</h2>

<p>
  For each raw Excel row, <code>transform_excel_data_to_db</code> builds the associative structure expected by persistence and by
  <code>premium_calculation_manager</code>, including <code>temp.grid_type</code> (rack name), <code>temp.grid_id</code> (grid master
  <code>ui_type</code>), <code>temp.action</code> (single-letter code I, A, DA, MI, …), and nested <code>policy_details</code>.
</p>

<p>
  Premium is only calculated when any of the following holds (Excel onboarding path simplifies to the first two in practice):
</p>

<ul>
  <li><code>isEmployeeSourceEnrollment</code>: <code>fileArr['id'] == null</code> (not used in the scoped Excel path).</li>
  <li><code>isEmployeeSourceExcelFile</code>: <code>temp.source == 'excel'</code> — normal onboarding uploads.</li>
  <li><code>primaryGridTypeCondition</code>: dependent addition + primary grid + premium_type single + relationship self + basic SI already set (additional-grid path; omitted from diagrams above for brevity).</li>
</ul>

<div class="mermaid-wrapper" id="flowchart-per-member-gate">
  <div class="mermaid">
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"]
  </div>
</div>

<h2 id="flow-premium-manager"><code>premium_calculation_manager</code> (grid types 1–13)</h2>

<p>
  Resolves <code>$emp_data['temp']['grid_name']</code> to the rack bucket, takes that rack’s <code>slab_rates</code> rows, and branches on
  <code>temp.grid_id</code> (string <code>"1"</code> … <code>"13"</code>). Common outcomes for a match:
</p>

<ul>
  <li>Set <code>policy_details.basic_cover_si</code>, <code>date_coverage</code>, <code>policy_end_date</code>, <code>days</code>.</li>
  <li>Set annual <code>premium</code>, then <code>rata_premimum</code> via <code>calculate_pro_rata_premimum(premium, employee_days, policy_days)</code>.</li>
  <li>Set <code>gst</code> from policy GST percent (default 18).</li>
</ul>

<p>
  Special cases worth reading in source: GPA grid <strong>1</strong> also supports <code>si_or_bp == 2</code> auto SI from basic pay when no slab row matches;
  grids <strong>10</strong> and <strong>11</strong> consume <code>temp.max_age</code> / <code>temp.max_count</code> from the rack-selection phase;
  grids <strong>12</strong> and <strong>13</strong> match slugified relationship (child merges son/daughter).
</p>

<div class="mermaid-wrapper" id="flowchart-premium-calculation-manager">
  <div class="mermaid">

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])


  </div>
</div>

<h2 id="dependent-addition">Dependent addition extras</h2>

<p>
  Before <code>calculate_premium_new</code>, the controller loads active family members from the DB, maps them into the same Excel column layout,
  merges them with new dependents, and re-groups so <strong>Self stays first</strong>. It copies the self member’s
  <code>temp.rata_premimum</code> onto each non-self row as <code>self_rata_premium</code> for downstream floater math.
</p>

<p>
  After pricing, <code>validatet_family_floter_rata_premium</code> adjusts dependents when <code>premium_type == 1</code> so that only the
  intended Excel dependents retain non-zero rata (see implementation for the two-pass rules and the <code>data_from == excel</code> filter).
</p>

<h2 id="failure-modes">Failure: zero successful families</h2>

<p>
  If every family iteration yields no successful insert from <code>employeesOnboardProcess</code>, the file is marked failed with a message
  pointing operators at slab / rack configuration. That usually means no family produced priced rows that the persistence layer accepted,
  which often traces back to rack mismatch, missing slab rows for SI/unit/age, or <code>premium_calculation_manager</code> returning
  unmatched state for all members.
</p>

<h2 id="related">Related</h2>

<ul>
  <li><a href="<?= base_url('docs/eb-rack-rate-config') ?>">EB rack rate config</a> — UI fields, <code>premium_type</code> semantics, grid catalogue.</li>
  <li><code>app/Controllers/EmployeeServiceController.php</code> — <code>employeesOnboardPreprocess</code>, <code>employeesOnboardProcess</code>.</li>
  <li><code>app/Helpers/excel_util_helper.php</code> — <code>calculate_premium_new</code>, <code>premium_calculation_manager</code>, composition/compare helpers.</li>
  <li><code>app/Models/PolicesModel.php</code> — <code>getPolicySlabRatesForEmpOnboard()</code>.</li>
</ul>