---
# System prepended metadata

title: Review Plan Specification (draft)

---

**Huey Note:**
```
Review plan:
    planheader: chứa information của plan.
    PlandetailItem: chứa data theo Item level
    PlandetailStoreItem: chứa data theo store, item level
Tap ReFINE:
    đỗ data từ planconfig vào.
        Nếu planconfig mà ko có attribute đó => đổ từ lookuplist vào
    Có các table A_master per tenant và store_master trên shared
    Em có thể lookup để left join với mấy bảng plan để filter trong Refine

[10:05] Huey Lewis
Database:
    planDetailItem => product level
    planDetailStoreItem => product + store level.
- RestAPI để lấy data.
- websocket để nhận data realtime => product+ store level?
- Post plandetail/id:
    requestbody:
    {
        filter product attributes,
        filter product selection,
        filter store attributes,
        TimeFrame: Weekly/Monly
    }
    response body:
    product level + list storeID
object FE:    
plandetails:[
        timelines: [startdate...],
        storeIds:[s1, s2, s3,....,s10],
        product1: {
            productID,
            productname,
            productAttributes,
            price: xxx
            "promo activity": {
                startDate: value,
            },
            "baseline forecast": {
                startDate: value,
            }
        }
    ]
websocket:
    product1, store1, startdate1, ........ X1
    product2, store1, startdate1, ........ X1
    if storeIds contains store1
        product1."promo activity"["startdate1"] = promo activity of X1
        plandetails.["product2"]....
```


Section:
| Component | API                                                 | Developer |
|-----------|-----------------------------------------------------|-----------------|
| DMND_PLAN | Chart Data /{plan_header_id}                        | DUY             |
| DMND_PLAN | Update status product                               | BINH            |
| DMND_PLAN | Get data of Refine panel                            | BINH            |
| DMND_PLAN | Update status of plan header                        | DUY             |
| DMND_PLAN | Add Event                                           | TODO            |
| GATE_WAY  | Get promote activity                                | BINH            |
| GATE_WAY  | Product cost price                                  | BINH             |
| GATE_WAY  | Aggregate Sales, Sales Less Outlier, Sales LY       | BINH            |
| GATE_WAY  | Map from raw Plan Store Item to FE                  | DUY             |
| GATE_WAY  | Aggregate from ALL data to FE                       | DUY             |
| GATE_WAY  | Cache                                               | TODO            |

#### 1. Chart Data /{plan_header_id} [DMND_PLAN]

**Description**:
    - This return all product info in store level

**POST** /api/plandetails/{plan_header_id}

**Request Body:**
```json
{
    "productAttribute": {
        "department": ["dep_1", "dep_2"], // skip when null 
        "supplier": [],
        "status": [],
        "class": [],
        "subClass": [],
    },
    "productSelection": {
        "productValues": [],
        "productSelectionType": 500
    
    },
    "storeLocation": {
          "stateIds": [], // a
          "regionIds": [], // b
          "locationIds": [], // "storeID" // a b c d
    },
    "searchTime": {
        "startDate": "YYYY-MM-DD",
        "endDate": "YYYY-MM-DD",
        /* MONTHLY or WEEKLY */
        "groupBy": "WEEKLY", 
        // Note: when time is so small 2023-07-30
    }
}
```

**Response Body:**
```json
{
  "status": "SUCCESS",
  "message": "",
  "data": {
      "forecastTimeFrame": [],  // This will return along with planDetailStoreItems for API for aggregate 
      "historicalTimeFrame": [],
      "planDetailItemVo":[
             {
                  "planHeaderId": 123,
                  "planDetailItemId": 456,
                  "retailerId": "retailer_1",
                  "productId": "product_12345",
                  "supplierProductId": "", // NEW FIELD
                  "storeIds": [ id1, id2, id3 ],
                  "retailStoreIds": [ retailStoreId2, retailStoreId2], 
                  "sellThroughForecastVnet": 100.5, // SUM
                  "sellInForecastVnet": 200, // SUM
                  "sellThroughAdjustment": 10, // SUM
                  "sellThroughFinalOverrides": 5, // SUM
                  "sellThroughConsensus": 15, // SUM
                  "sellThroughForecastRetailer": 50, // SUM
                  "sellThroughForecastSupplier": 70, // SUM
                  "sellInForecastFinalCommitment": 180, // SUM
                  "sellInForecastRetailer": 60, // SUM
                  "sellInForecastSupplier": 90, // SUM
                  "inStockPercentage": 95.5, // SUM
                  "confidenceLevel": 0.85, // SUM
                  "status": "NOT_STARTED", // get[0] của plandetailStoreItem
                  "lower": 80.5, //SUM
                  "upper": 120.5, //SUM
                  "startDate": 20230801,
                  "endDate": 20230831,
                  "lastYearStartDate": 20220801,
                  "lastYearEndDate": 20220831,
              }
          ]
      }
  ]
}
```
**API Gateway - Response Body:**
```json
{
  "status": "SUCCESS",
  "message": "",
  "data": [
      "timeFrames": [],  // merge forecasttimeframe and historical timeframe
      "planDetailItems":[
             {
                  "planHeaderId": 123,
                  "planDetailItemId": 456,
                  "retailerId": "retailer_1",
                  "productId": "product_12345",
                  "storeIds": [ id1, id2, id3 ],
                  "retailStoreIds": [ retailStoreId2, retailStoreId2], 
                  "sellThroughForecastVnet": 100.5, // SUM
                  "sellInForecastVnet": 200, // SUM
                  "sellThroughAdjustment": 10, // SUM
                  "sellThroughFinalOverrides": 5, // SUM
                  "sellThroughConsensus": 15, // SUM
                  "sellThroughForecastRetailer": 50, // SUM
                  "sellThroughForecastSupplier": 70, // SUM
                  "sellInForecastFinalCommitment": 180, // SUM
                  "sellInForecastRetailer": 60, // SUM
                  "sellInForecastSupplier": 90, // SUM
                  "inStockPercentage": 95.5, // SUM
                  "confidenceLevel": 0.85, // SUM
                  "status": "NOT_STARTED", // get[0] của plandetailStoreItem
                  "lower": 80.5, //SUM
                  "upper": 120.5, //SUM
                  "startDate": 20230801,
                  "endDate": 20230831,
                  "lastYearStartDate": 20220801,
                  "lastYearEndDate": 20220831,
              }
          ]
  ]
}

#### 2. Update plan detail status /{plan_header_id} [DMND_PLAN]

**Description**:
    - Update product status and all of its product store.

**PUT** /api/planheaders/{plan_header_id}/plandetailitem

**Request Body:**
```json
[
    {
        "planDetailItemId": 1,
        "status": "DONE"
    }
]
```

**Response Body:**
```json
{
    "status": "SUCCESS",
    "message": ""
}
```

#### 3. Get data of Refine panel /{plan_header_id} [DMND_PLAN]

**Description**:
    - Return a list of selectable department, supplier for refine panel

**POST** /api/demandplanning/planheaders/{plan_header_id}/refine

**Response Body:**
```json
{
  "status": "SUCCESS",
  "message": "",
  "data": {
    "productAttribute": {
      "department": [
        {
          "id": "",
          "value": ""
        }
      ],
      "supplier": [
        {
          "id": "",
          "value": ""
        }
      ],
      "status": [
        {
          "id": "",
          "value": ""
        }
      ],
      "class": [
        {
          "id": "",
          "value": ""
        }
      ],
      "subClass": [
        {
          "id": "",
          "value": ""
        }
      ]
    },
    "storeLocation": {
      "states": [
        {
          "id": "",
          "value": ""
        }
      ],
      "regions": [
        {
          "id": "",
          "value": ""
        }
      ],
      "locations": [
        {
          "id": "",
          "value": ""
        }
      ]
    }
  }
}
```

#### 4. Update plan header status /{plan_header_id} [DMND_PLAN]

**Description**:
    - Update plan header status

**PUT** /api/planheaders/{plan_header_id}

**Request Body:**
```json
{
  "status": "DONE",
}
```

#### 5. Add Event (TODO)

#### 6. Get promote activity [GATE_WAY]
- Confirm that: do we need show forecast data only or require historical data, if so how the chart is structure.
- Confirm about promote data:
  - Which level that have promote: store or product ? 
  - Which data they contain ? %40 OFF for example
  - How to aggregate promote to product level in chart table

NOTE:
    - Yes have to show historical data.
    - In summary table, only show yes and no. 
    - In product show lastest promote
    
Question: 
- [bdoan-20230807] Should it be group by week, or should it be returned for each date ?

<details><summary>Promotion API</summary>
    
**POST** /api/promo/promostoreproducts

**Request Body:**
```json
{
    "retailerId": "00151"
    "storeProducts": [
        {
            "storeId": "", 
            "productId": ""
        }
    ],
    "startDate": yyyyMMdd <= ngay dau tien của tuần đầu tiên.
    "endDate": yyyyMMdd => ngày kết thúc của tuần cuối cùng.
}
```

**Response Body:**
```json
{
    "status": "SUCCESS",
    "message": "",
    "data": [
        {
            "date": yyyyMMdd,
            "retailerId": "",
            "productId": "",
            "storeId": "",
            "promoCount": 0 <=== Just an example. Which data is supported in Promo Service? If latest, which data should it return?
        }
    ]
}
```
</details>

#### 7. Product cost price historical week => forecast week [GATE_WAY]
- Send historical week and list of product id and store id.
- Confirm about price data:
  - How to aggregate price data to product level in chart table

NOTE:
    - Only product level
    - Only historical week
    - Get both price and cost

#### 8. Actual Sales, Actual Sales Less Outlier, Actual Sales LY, Actual Inventory [GATE_WAY]
- Send historical week and list of product id and store id, receive four array of Actual Sales, Actual Sales Less outlier, Actual Sales LY, Actual Inventory.
- Confirm about sale data:
  - How to aggregate sale data to product level in chart table

NOTE:
    - Get historical

<details><summary>Actual Sales - Actual Sales LY</summary>
    

**POST** /api/salesinventory/sales

**Request body:**
```json 
{
   "retailerIds": ["00151"]
    "storeProducts": [
        {"storeId": "", "productId": ""}
    ],
    "startDate": yyyyMMdd,
    "endDate": yyyyMMdd
}
```

**Response Body:**
```json
{
    "status": "SUCCESS",
    "message": "",
    "data": [
        {
            "date": yyyyMMdd,
            "retailerId": "",
            "productId": "",
            "storeId": "",
            "salesUnit": 0,
            "salesPrice": 0,
            "salesCost": 0
        }
    ]
}
```
</details>

<details><summary>Actual Inventory</summary>
    
**POST** /api/salesinventory/inventory

**Request body:**
```json 
{
   "retailerIds": ["00151"]
    "storeProducts": [
        {"storeId": "", "productId": ""}
    ],
    "startDate": yyyyMMdd,
    "endDate": yyyyMMdd
}
```

**Response Body:**
```json
{
    "status": "SUCCESS",
    "message": "",
    "data": [
        {
            "date": yyyyMMdd,
            "retailerId": "",
            "productId": "",
            "storeId": "",
            "totalStockOnHand": 0, 
            "stockOnHand": 0,
            "stockOnOrder": 0,
            "stockInTransit": 0
        }
    ]
}
```
</details>

<details><summary>Actual Sales Less Outlier - What is this data? Where is this data come from ??? </summary>
    
**POST** /api/salesinventory/saleslessoutlier

**Request body:**
```json 
{
   "retailerIds": ["00151"]
    "storeProducts": [
        {"storeId": "", "productId": ""}
    ],
    "startDate": yyyyMMdd,
    "endDate": yyyyMMdd
}
```

**Response Body:** 
```json
{
    "status": "SUCCESS",
    "message": "",
    "data": [
        // TODO: Data is ...
    ]
}
```
</details>



#### 9. Map from raw Plan Store Item from Demand plan to FE (IN API GATEWAY) [GATE_WAY]

**POST** /api/plandetails/{plan_header_id}

**Request Body:**
```json
{
  "productAttribute": {
        "department": [],
        "supplier": [],
        "status": [],
        "class": [],
        "subClass": [],
        "productValues": [],
        "productSelectionType": 500
    },
    "storeLocation": {
          "stateIds": [],
          "regionIds": [],
          "locations": []
    },
    "searchTime": {
        "startDate": "YYYY-MM-DD",
        "endDate": "YYYY-MM-DD",
        /* MONTHLY or WEEKLY */
        "groupBy": "WEEKLY",
    }
}
```

- This API will map this raw result from **DEMAND PLANNING** service 
```json
{
  "data": [
      "storeIds": [],     
      "productIds": [],  
      "timeFrame": [],  
      "planDetailStoreItems": [
          {
              "id": 1,
              "planHeaderId": 123,
              "planDetailItemId": 456,
              "retailerId": "retailer_1", ... 
          }
    ]
  ]
}
```

Into this format: 
```json 
{
  "data": [
    {
      "20230702": {
        "promoActivity": "500",
        "baselineForecast": "500",
        "adjustForecast": "500"
      },
      "20230709": {
        "promoActivity": "500",
        "baselineForecast": "500",
        "adjustForecast": "500"
      },
      "20230716": {
        "promoActivity": "500",
        "baselineForecast": "500",
        "adjustForecast": "500"
      },
      "id": 100,
      "product": "product1",
      "description": "Product 01 description",
      "status": "Done"
    },
    {
      
      "id": 200,
      "product": "product2",
      "description": "Product 02 description",
      "status": "Done" .... 
    }
  ]
}
```

Then from this format, front end will convert to **AG Grid** table using this function, when there is new object come, just convert that object and append to current array:
```typescript
const convertedData: ConvertedData[] = [];
originalData.forEach((product) => {
  const { name, description, status, sellData } = product;
  const productKeys = Object.keys(sellData);
  productKeys.forEach((date) => {
    convertedData.push({
      product: `product${product.id}`,
      description,
      status,
      [date]: sellData[date].promoActivity,
      title: 'Promo Activity',
    });

    convertedData.push({
      product: `product${product.id}`,
      description,
      status,
      [date]: sellData[date].baselineForecast,
      title: 'Baseline Forecast',
    });

    convertedData.push({
      product: `product${product.id}`,
      description,
      status,
      [date]: sellData[date].upperConfidence,
      title: 'Upper Confidence',
    });
  });
});

```
    
#### 10. Aggregate from all service (promote, sale inventory, product) to FE (IN API GATEWAY) [GATE_WAY]

- VO from API Gateway
```java
public class PlanAggregateItemVo implements Serializable {
    
    private Long planDetailItemId;
    private String retailerId;
    private String productId;
    private String supplierProductId;
    private String productName; // thiếu cả bên demand planning
    private List<String> storeIds;

    // SELL THROUGH
    private Integer sellThroughForecastVnet; =>  Baseline Forecast // Sau khi aggregate thì phải làm tròn. 
    private Integer sellThroughAdjustment;  => Adjusted Forecast 
    private Integer sellThroughConsensus; => Consensus Forecast
    private Integer sellThroughForecastRetailer; => Retailer Forecast
    private Integer sellThroughForecastSupplier; => Supplier Forecast
    private Integer sellThroughFinalOverrides; => Final Overrides

    // SELL IN
    private Integer sellInForecastVnet;
    private Integer sellInForecastFinalCommitment;
    private Integer sellInForecastRetailer;
    private Integer sellInForecastSupplier;

    private String status;

    // Upper Confidence | Lower Confidence
    private Double upperConfidence; => Upper
    private Double lowerConfidence; =>Lower 

    /* PROMOTE */
    private Promotion promotion; promoId and promoName

    /* COST PRICE */
    private Double productPrice; // Nếu historical timeframe thì lấy từ product services, nếu forecasttimeframe thì lấy từ pdi_futureprice.

    /* HISTORICAL SALE (Actual Sales) */
    private Double actualSales;
    private Double actualSalesLessOutlier;
    private Double actualSalesLY;

    /* INVENTORY DATA */
    private Long actualInventory; // Recorded historical inventory available in the platform
    private Long forecastBaselineInventory;
    private Long forecastAdjustedInventory;
    private Long forecastFinalInventory;

    /* ORDERS */
    private Long actualOrder;
    
    private Integer startDate;
}

public class PlanChartDataVo implements Serializable {
    Long PlanHeaderId;
    List<PlanAggregateItemVo> planChartAggregateItems;
    List<Integer> timeFrames; // historical + forecase
}

```

------------------------------------------------------
**STEP by step convert API GW Response thanh FE data:**
```json
    FE Data format:
    {
      product: 'product1',
      description: 'Product 01 description',
      status: 'Done',
      20230702: '500',
      20230709: '600',
      20230716: '600',
      20230723: '700',
      20230730: '500',
      title: 'Promo Activity',
    } 
```


- 1. **Loop planChartAggregateItems qua Construct 1 Map voi**
    ```
    KEY = <PRODUCT_ID + TIMEFRAME> 
    VALUE { PlanAggregateItemVo }
    (tam goi la MAP_1)
    ```
- 2. **Loop values cua planChartAggregateItems de construct 1 list product object**
```
[
    {
        product: 'product1',
    }... 
] 
```
- 3. **Voi moi column da duoc define san, append title cua tung column**
```
[
    {
        product: 'product1',
        colKey: 'promo_activity',
        title: 'Promo Activity',
    },
    {
        product: 'product1',
        colKey: 'actual_sale',
        title: 'Actual Sale',
    },
    {
        product: 'product2',
        colKey: 'promo_activity',
        title: 'Promo Activity',
    },
    {
        product: 'product2',
        colKey: 'actual_sale',
        title: 'Actual Sale',
    },... 
] 
```
- 4. **Add timeframe vao tung element o buoc tren**
```
[
    {
        product: 'product1',
        colKey: 'promo_activity',
        title: 'Promo Activity',
        20230702: null,
        20230709: null,
        20230716: null,
    },
    {
        product: 'product1',
        colKey: 'actual_sale',
        title: 'Actual Sale',
        20230702: null,
        20230709: null,
        20230716: null,
    },
    {
        product: 'product2',
        colKey: 'promo_activity',
        title: 'Promo Activity',
        20230702: null,
        20230709: null,
        20230716: null,
    }... 
] 
```   
    
- 5. **Fill data vao table dung MAP_1**
```
[
    {
        product: 'product1',
        colKey: 'promo_activity',
        title: 'Promo Activity',
        20230702: MAP_1.get(20230702, 'product1').promoteValue,
        20230709: MAP_1.get(20230709, 'product1').promoteValue,
        20230716: MAP_1.get(20230716, 'product1').promoteValue,
    },
    {
        product: 'product1',
        colKey: 'actual_sale',
        title: 'Actual Sale',
        20230702: MAP_1.get(20230702, 'product1').actual_sale,
        20230709: MAP_1.get(20230702, 'product1').actual_sale,
        20230716: MAP_1.get(20230702, 'product1').actual_sale,
    },
    {
        product: 'product2',
        colKey: 'promo_activity',
        title: 'Promo Activity',
        20230702: MAP_1.get(20230702, 'product2').promoteValue,
        20230709: MAP_1.get(20230709, 'product2').promoteValue,
        20230716: MAP_1.get(20230716, 'product1').promoteValue,
    }... 
] 
```
    
- DONE

DEMAND:
- add field future price entity + liquidbase
- add future price lay first giong status
- add product desc, name DTO 
- Double -> Int 
- API GW: Inventory -> Int
    
    
#### 11. Chart Plan API [GATE_WAY]

**Get chat data API**:
```
POST
{{api-url-local}}/api/demandplanning/plandetails/charts/{plan_header_ID}


BODY: all of this are optional
{
    {
      "productSelection": {
        "productSelectionValues": ["product1", "product2", "product3"],
        "productSelectionType": 500 // 500 -> 503
      },
      "productAttribute": {
        "departments": ["dept1", "dept2"],
        "suppliers": ["supplier1", "supplier2"],
        "status": ["active", "inactive"],
        "classes": ["class1", "class2"],
        "subClass": ["subClass1", "subClass2"]
      },
      "storeLocation": {
        "stateIds": ["state1", "state2"],
        "regionIds": ["region1", "region2"],
        "locationIds": ["location1", "location2"]
      },
      "searchTime": {
        "startDate": 20231001,
        "endDate": 20231015,
        "groupBy": "WEEKLY"
      }
    }

}
    
RESPONSE:

{
  "planHeaderId": 1,
  "planChartAggregateItems": [
    {
      "planDetailItemId": 987654,
      "retailerId": "retailer123",
      "productId": "product123",
      "supplierProductId": "supplierProduct123",
      "storeIds": ["store1", "store2"],
      "productName": "Product A",
      "futurePrice": 29.99,
      "sellThroughForecastVnet": 100,
      "sellThroughAdjustment": -5,
      "sellThroughConsensus": 90,
      "sellThroughForecastRetailer": 85,
      "sellThroughForecastSupplier": 95,
      "sellThroughFinalOverrides": 2,
      "sellInForecastVnet": 110,
      "sellInForecastFinalCommitment": 105,
      "sellInForecastRetailer": 100,
      "sellInForecastSupplier": 102,
      "status": "active",
      "lowerConfidence": 0.88,
      "upperConfidence": 0.92,
      "promoteData": {
        "promotionType": "Discount",
        "promotionAmount": 5.0
      },
      "productCost": 20.0,
      "actualSales": 120.0,
      "actualSalesLessOutlier": 115.0,
      "actualSalesLY": 100.0,
      "actualInventory": 500,
      "forecastBaselineInventory": 550,
      "forecastAdjustedInventory": 530,
      "forecastFinalInventory": 520,
      "actualOrder": 110,
      "startDate": 20231001
    }
  ],
  "timeFrames": [20231001, 20231008, 20231015]
}

```
    