只要写监控SQL
任务描述
需求描述
背景
移除订单推送服务，如果挂掉，会导致盛宝隆无退货单收货，影响对账，需增加服务监控机制，及时发现推送盛宝隆服务暂停、延迟等风险；
需求
监控推送盛宝隆服务，当移除订单审核状态为“已审核”，详情表中已有平台发货数量和追踪号，如果1小时还未推送，则报警提醒；
验收标准
暂无
背景
移除订单推送服务，如果挂掉，会导致盛宝隆无退货单收货，影响对账，需增加服务监控机制，及时发现推送盛宝隆服务暂停、延迟等风险；
需求
监控推送盛宝隆服务，当移除订单审核状态为“已审核”，详情表中已有平台发货数量和追踪号，如果1小时还未推送，则报警提醒；
| 所属迭代 | 技术研发中心 2月分支版本（2023年） |
|---|---|
| 所属模块 | / |
| 相关需求 | 移除订单推送盛宝隆服务，增加订单监控 |
| 指派给 | Closed 于 2023-06-14 19:47:59 |
| 任务类型 | 后端 |
| 任务标签 | 初始 |
| 开发组 | |
| 任务状态 | 已关闭 |
| 进度 | 100% |
| 优先级 | P2 |
| 关键词 | |
| 抄送给 |
| 由谁创建 | 程伟 于 2023-02-14 13:40:24 |
|---|---|
| 由谁完成 | 李振宇 于 2023-02-14 16:06:51 |
| 由谁取消 | 暂无 |
| 由谁关闭 | 程伟 于 2023-06-14 19:47:59 |
| 关闭原因 | 已完成 |
| 最后编辑 | 程伟 于 2023-06-14 19:47:59 |
| 团队 | 最初预计 | 总计消耗 | 预计剩余 |
|---|
| 最初预计 | 2工时 |
|---|---|
| 总计消耗 | 2工时 |
| 预计剩余 | 0工时 |
| 预计开始 | 0000-00-00 |
| 实际开始 | 2023-02-14 |
| 截止日期 | 0000-00-00 |
备注：统计未推送到盛宝隆的移除订单
SELECT DISTINCT
A.ID,A.CustomerID,
A.RemovalOrderCode,
A.OrderSourceID,
arods.Status
FROM RB_RemovalOrderCreateRecord (NOLOCK) A
LEFT JOIN RB_RemovalOrder_BusinessExpand (NOLOCK) robe
ON robe.CustomerId = A.CustomerID
AND robe.OrderSourceID = A.OrderSourceID
AND robe.RemovalOrderCode = A.RemovalOrderCode
LEFT JOIN RB_Amazon_Removal_Order_Detail (NOLOCK) arod
ON arod.CustomerID = A.CustomerID
AND arod.OrderSourceID = A.OrderSourceID
AND arod.OrderID = A.RemovalOrderCode
LEFT JOIN RB_Amazon_Removal_Order_Detail_SyncStatus (NOLOCK) arods
ON A.CustomerID = arods.CustomerID
AND A.OrderSourceID = arods.OrderSourceID
AND A.RemovalOrderCode = arods.OrderID
WHERE A.CustomerID = 1
--AND 1 = 1
AND A.RemovalOrderType = 'Return'
AND robe.AuditStatus IN ( 3, 4, 5 )
AND A.RemovalOrderStatus = 'Success'
AND ISNULL(arods.Status, 3) > 1
AND A.AddTime >= '2022-11-01'
UNION ALL
SELECT MIN(arod.ID) ID,
MIN(arod.CustomerID) CustomerID,
arod.OrderID AS RemovalOrderCode,
MIN(arod.OrderSourceID) AS OrderSourceID,
arods.Status
FROM RB_Amazon_Removal_Order_Detail (NOLOCK) arod
LEFT JOIN RB_RemovalOrder_BusinessExpand (NOLOCK) robe
ON robe.CustomerId = arod.CustomerID
AND robe.OrderSourceID = arod.OrderSourceID
AND robe.RemovalOrderCode = arod.OrderID
LEFT JOIN RB_Amazon_Removal_Order_Detail_SyncStatus (NOLOCK) arods
ON arod.CustomerID = arods.CustomerID
AND arod.OrderSourceID = arods.OrderSourceID
AND arod.OrderID = arods.OrderID
WHERE arod.CustomerID = 1
--AND 1 = 2
AND arod.OrderType = 'Return'
AND robe.AuditStatus IN ( 3, 4, 5 )
AND ISNULL(arods.Status, 3) > 1
AND arod.AddTime >='2022-11-01'
AND NOT EXISTS
(
SELECT 1
FROM RB_RemovalOrderCreateRecord (NOLOCK) rocr
WHERE rocr.CustomerID = arod.CustomerID
AND rocr.OrderSourceID = arod.OrderSourceID
AND rocr.RemovalOrderCode = arod.OrderID
)
GROUP BY arod.OrderID,
arod.OrderType,
arods.Status;