# ECSM業主平台 (業主理貨看板)

###### tags: `SUDA`

## :memo: ECSM理貨看板相關設定

### Step 1:新增功能選單(理貨看板):(Database:ECSM)

:::info
:bulb:

USE [ECSM]
GO

INSERT INTO [ecsm].[Functions]([FuncId],[FuncName],[Uri],[ParentId],[Sort],[IsAjax],[ProjectId],[Enabled],[Deleted],[CreatedAt],[CreatedBy],[UpdatedAt],[UpdatedBy])
select 'D80','理貨看板',null,@TopParentId,0,0,4,1,0,GETDATE(),'NoName',getdate(),'NoName'

declare @ParentId int =-1

select @ParentId=Id from ecsm.Functions
where FuncName ='理貨看板'

INSERT INTO [ecsm].[Functions]([FuncId],[FuncName],[Uri],[ParentId],[Sort],[IsAjax],[ProjectId],[Enabled],[Deleted],[CreatedAt],[CreatedBy],[UpdatedAt],[UpdatedBy])
select 'D8010','業主理貨看板','/Dashboard/Dashboard',@ParentId,0,0,4,1,0,GETDATE(),'NoName',getdate(),'NoName'

INSERT INTO [ecsm].[Functions]([FuncId],[FuncName],[Uri],[ParentId],[Sort],[IsAjax],[ProjectId],[Enabled],[Deleted],[CreatedAt],[CreatedBy],[UpdatedAt],[UpdatedBy])
select 'D8020','業主訂單出貨統計','/Dashboard/DashboardCustomerOrders',@ParentId,1,0,4,1,0,GETDATE(),'NoName',getdate(),'NoName'

GO
--建立功能項目
with fn as
(
select id from ECSM.Functions
where left(FuncId,1)='D' and FuncId in ('D80','D8010','D8020')
),
tp as
(
select 1 as FType union select 2 union select 3 union select 4 union select 5
)
insert [ecsm].[FunctionActionTypes] ([Function_id],[ActionType_id])
select * from fn,tp

GO
:::

### Step 2:以系統管理者登入系統設定角色權限(99999999999)

會員專區 => 權限管理 => 角色維護 =>設定相關角色
- [x] **ecsmadmin**
- [X] **ecsmsers**
=>明細=> 理貨看板功能區全勾

### Step 3:建立View(Database:WMS)

:::warning

USE [WMS]
GO

Create view [wms].[CustomerOrderMappingsDashboardView] as

select a.DCId,a.CustomerId,c.UnifiedBusinessNo,a.CustomerOrderNo ,a.CustomerOrderId
,a.BatchImportId ,b.BatchStatus ,b.DataDate,b.ImportDate,b.OrderImportType
,a.WMSOrderId
,d.WMSOrderStatus
,d.CreatedAt as WMSCreatedAt
,d.WarehouseOutDate
,iif(b.OrderImportType=1,dateadd(D,1, b.DataDate),b.DataDate) as DashboardDate
from wms.CustomerOrderMappings a inner join wms.CustomerBatchImports b on a.BatchImportId =b.BatchImportId and a.CustomerId =b.CustomerId and a.DCId =b.DCId
inner join wms.Customers c on a.CustomerId =c.CustomerId
inner join wms.WMSOrders d on a.WMSOrderId =d.WMSOrderId
where d.WMSOrderStatus<>3
GO

Create view [wms].[CustomerOrderDashboardView] as

select a.CustomerOrderId ,a.CustomerId ,c.UnifiedBusinessNo ,a.DCId ,a.BatchImportId ,a.CustomerOrderNo,a.OrderDate,a.CreatedAt ,a.OrderStatus
,b.BatchStatus ,b.DataDate,b.ImportDate,b.OrderImportType
,iif(b.OrderImportType=1,dateadd(D,1, b.DataDate),b.DataDate) as DashboardDate
from wms.CustomerOrders a (nolock) inner join wms.CustomerBatchImports b (nolock) on a.BatchImportId =b.BatchImportId and a.DCId =b.DCId and a.CustomerId =b.CustomerId
inner join wms.Customers c (nolock) on a.CustomerId =c.CustomerId
and a.OrderStatus in (0,1,2,6)
GO

Create view [wms].[WMSOrderDashboardView] as
select a.WMSOrderId,a.DCId ,a.CustomerId ,c.UnifiedBusinessNo,a.BatchImportId,a.WMSOrderStatus,a.CustomerOrderNo,a.CreatedAt as WMSCreatedAt,a.WarehouseOutDate
,b.BatchStatus ,b.DataDate,b.ImportDate,b.OrderImportType
,iif(b.OrderImportType=1,dateadd(D,1, b.DataDate),b.DataDate) as DashboardDate
from WMS.WMSOrders a (nolock) inner join wms.CustomerBatchImports b (nolock) on a.BatchImportId =b.BatchImportId and a.DCId =b.DCId and a.CustomerId =b.CustomerId
inner join wms.Customers c (nolock) on a.CustomerId =c.CustomerId
where a.WMSOrderStatus in (0,1,2)
GO

:::

:::info
:bulb: **Hint:**
批次
| BatchStatus| Memo|
| -----------|:----|
| 0 |待處理|
| 1 |庫分中|
| 2 |已庫分|
| 5 |有缺貨|
| 6 |待補貨|
| 7 |已取消|
| 8 |失敗|
| 9 |已刪除|

訂單：
| OrderStatus | Memo |
| -------- | -------- |
| 0 | 待庫分 |
| 1 | 庫分中 |
| 2 | 已庫分 |
| 6 | 缺貨 |
| 9 | 取消訂單 |

出貨單：
| WMSOrderStatus | Memo |
| -------- | -------- |
| 0 | 待處理 |
| 1 | QC中 |
| 2 | 已出貨 |
| 3 | 取消出貨 |

:::

### Step 4:建立View(Database:ECSM)

:::info

USE [ECSM]
GO

Create VIEW [ECSM].[WmsCustomerOrderMappingsDashboardView] as

select * FROM [WMSDB].wms.Wms.CustomerOrderMappingsDashboardView

GO
Create VIEW [ECSM].[WmsCustomerOrderDashboardView] as

select * from [WMSDB].[wms].[wms].[CustomerOrderDashboardView]

GO
Create VIEW [ECSM].[WmsWMSOrderDashboardView] as

select * from [WMSDB].[wms].[wms].[WMSOrderDashboardView]

GO
:::

## :memo: 前端設計模型:

:::info

namespace ECSM.DomainModels.Dashboard
{

        /// <summary>
        /// 查詢泛用型別
        /// </summary>
        
    public class DashboardDailyQuery
    {
        /// <summary>
        /// 特販代號
        /// </summary>
        [Display(Name = "DC編號")]
        public string DCId { get; set; }

        [Display(Name = "業主")]
        public string UnifiedBusinessNo{ get; set; }
        /// <summary>
        /// 業主代號
        /// </summary>
        [Display(Name = "業主")]
        public int? CustomerId { get; set; }

        /// <summary>
        /// 查詢日期
        /// </summary>
        public DateTime? BeginDate { get; set; }

        public DateTime? EndDate { get; set; }

    }

        /// <summary>
        /// 區間查詢結果：回傳每日資料的集合
        /// </summary>
    public class DashboardQueryRangeOrdersResult
    {
        public IList<DashboardDailyQueryResult> RangeOrders { get; set; } = new List<DashboardDailyQueryResult>();
    }
    
        /// <summary>
        /// 日查詢結果：回傳該日資料
        /// </summary>
    public class DashboardDailyQueryResult
    {

        [Display(Name = "DC編號")]
        public string DCId { get; set; }

        public string DCName { get; set; }

        [Display(Name = "業主")]
        public int CustomerId { get; set; }

        [Display(Name = "通路")]
        public int DistributorId { get; set; }

        [Display(Name = "業主訂單日期")]
        
        public string DashboardDate { get; set; }
        /// <summary>
        /// 訂單日期
        /// </summary>
        [Display(Name = "業主訂單時間")]
        public DateTime? OrderTime { get; set; }

        /// <summary>
        /// 出貨日期
        /// </summary>
        [Display(Name = "出貨日期")]
        public DateTime ShipmentTime { get; set; }

        /// <summary>
        /// 訂單完成率
        /// </summary>
        /// 
        [Display(Name = "訂單完成率")]
        public decimal OrderPercentage { get; set; }

        /// <summary>
        /// 訂單總數
        /// </summary>
        [Display(Name = "訂單筆數")]
        public int OrderTotal { get; set; }
        /// <summary>
        /// 訂單完成數
        /// </summary>

        [Display(Name = "訂單完成數")]
        public int OrderFinishedTotal { get; set; }

        /// <summary>
        /// PCS總數 需出貨數：商品SKU
        /// </summary>
        [Display(Name = "PCS總數")]
        public int PCSTotal { get; set; }
        /// <summary>
        /// PCS完成數 商品出貨數 商品SKU
        /// </summary>
        [Display(Name = "PCS完成數")]
        public int PCSFinishedTotal { get; set; }

        /// <summary>
        /// PCS完成比例 商品出貨數比例 商品SKU
        /// </summary>
        /// 
        [Display(Name = "PCS完成比例")]
        public decimal PCSPercentage { get; set; }
        /// <summary>
        /// 出貨總張數
        /// </summary>
        [Display(Name = "出貨單數")]
        public int ShipmentTotal { get; set; }
        /// <summary>
        /// 出貨完成張數
        /// </summary>
        [Display(Name = "出貨單完成數")]
        public int ShipmentFinishedTotal { get; set; }

        /// <summary>
        /// 出貨完成比例
        /// </summary>
        /// 
        [Display(Name = "出貨完成比例")]
        public decimal ShipmentPercentage { get; set; }
                
        /// <summary>
        /// 分時明細資料
        /// </summary>        
        public IList<OrderDaily> DahboardOrderDailyDetails { get; set; } = new List<OrderDaily>();

        /// <summary>
        /// 圓餅圖資料
        /// </summary>
        public IList<DashboardChartQueryResult> DashboardChartResultMaster { get; set; } = new List<DashboardChartQueryResult>();

        /// <summary>
        /// 分時長條圖資料
        /// </summary>
        public IList<DashboardChartQueryResult> DashboardChartResultDetails { get; set; } = new List<DashboardChartQueryResult>();
                
    }


    public class OrderDaily
    {
        public string Title { get; set; }
        public DateTime OrderTime { get; set; }
        public int OrderTotal { get; set; }
        public int OrderFinishedTotal { get; set; }
        public int ShipmentTotal { get; set; }
        public int ShipmentFinishedTotal { get; set; }

    }
    public class DashboardChartQueryResult
    {
        public string Category { get; set; }
        public decimal Value { get; set; }
        public string OrderBy { get; set; }

    }
}

:::

:::success
:bulb: **Hint:** 定義說明
* DashBoardDate(看板訂單日期)：
依BAT轉入時的類別在View中設定:0:AM單;1:PM單;2:業主自訂
wms.CustomerBatchImports.OrderImportType
EX:PM單以艾多美算隔日訂單加一天處理;餘日期同步資料日期
DashboardDate =iif(b.OrderImportType=1,dateadd(D,1, b.DataDate),b.DataDate)
* 業主看版：
訂單完成率：當日業主訂單數轉庫分數/當日業主訂單數
訂單數：當日業主訂單數(排除作廢)
出貨單數:該日出貨單總數。(以出貨單對照到的批次記錄DashboardDate認列。)
出貨完成數：該日出貨單狀態=2(已出貨)數量

* 業主訂單出貨統計：以業主訂單為主(會拆併單。有其一未出貨則算未完成)
以wms.CustomerOrderMappings中的出貨及Bat中記錄為主來源。
訂單數：為該集合中(CustomerOrderNo).distinct.count
未完成訂單數：出貨單狀態<>2集合中(CustomerOrderNo).distinct.count

:::

:::danger
:bulb: **Hint:** 圖表相關(Kendo Ui)
圓餅圖：

script:
$scope.vm.PieChart = response.data.DashboardChartResultMaster;

html:
<div kendo-chart
                             k-title="{ text: '訂單完成率', position: 'top' }"
                             k-series-defaults="{ type: 'pie',labels: { visible: true ,format:'{0}筆'} }"
                             k-series="[{
                                    field: 'Value',
                                    categoryField: 'Category',
                                    padding: 0,
                                    color: '#b8b8b8',
                              }]"
                             k-data-source=vm.PieChart
                             k-series-colors="['#66b3ff', '#b2d9ff']"
                             k-tooltip="{ visible: true, template: '${ category }: #:value# 筆 (#=percentage.toFixed(3) * 100 # %)' }"
                             k-legend="{ position: 'left' }"
                             style="height: 300px; width:300px;">
                        </div>

長條圖：

Script:
var ds = new kendo.data.DataSource({
                            data: response.data.DashboardChartResultDetails,
                            group: [
                                { field: "OrderBy", dir: "asc" }
                            ],
                            sort: [
                                { field: "Category", dir: "asc" }
                            ]
                        });
                        $scope.vm.StackbarChart = ds;
                        
html:
<div kendo-chart
                             k-title="{ text: '訂單分時完成率', position: 'top' }"
                             k-series-defaults="{ type: 'bar', stack: true }"
                             k-series="[{
                                 field: 'Value',
                                 categoryField: 'Category'
                              }]"
                             k-data-source=vm.StackbarChart
                             categoryAxis="{field:'Value'}"
                             @*k-series-colors="['#66b3ff', '#b2d9ff']"*@
                             k-tooltip="{ visible: true, template: '#= category # : #= value #%' }"
                             k-legend="{ position: 'left' }"
                             style="height: 800px; width:500px;">
                        </div>

:::