**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]
}
```
Google Drive
Gist
Clipboard
Sign in with Wallet
