ALTER view [dbo].[V_WorkOrderReport]
as
select NEWID() as V_WorkOrderReportOID,m.FWorkShopName,m.FItemCode,m.FItemName,m.FItemModel,m.FSeOrderType,m.F_108,m.FLotNo,n.FAuxQty,n.FAccountQty,m.FAuxQtyM,x.FPassQty,m.FAuxQtyM*m.F_108-x.FPassQty as FSelfNQ,
case when (m.FWorkShopName = '卷对卷' or m.FWorkShopName = '本体' or m.FWorkShopName = 'FPC') then m.FTotalNQ else x.FTotalNQ end as FTotalNQ,y.FTotalNG1,z.FTotalNG2,m.FTotalHoldQty,m.FSetQty1,0 as FSetQty2,w.FFaceNQ,
m.FTotalHoldQty+m.FSetQty1+0+w.FFaceNQ as FTotalNQM,
(m.FTotalHoldQty+m.FSetQty1+0+w.FFaceNQ)*m.F_108 as FTotalNQPic,
m.FManTime,m.FMachineTime,m.FworkBillNo,n.FPlanStartDatetime,n.FPlanEndDatetime,
x.FActStartDatetime,x.FActEndDatetime,null as FRemark
from(
select t3.FItemCode,t3.FItemName,t3.FItemModel,t3.FSeOrderType,t4.F_108,t1.FLotNo,
SUM(t1.FAuxQty-t1.FAuxNGQty) as FAuxQtyM,SUM(t1.FNQ) as FTotalNQ,SUM(t1.FHoldQty) as FTotalHoldQty,
SUM(t1.FSetQty)/case isnull(t4.F_108,0) when 0 then 1 else t4.F_108 end as FSetQty1,
SUM(t1.FRealTime) as FManTime,SUM(t1.FRealTime) as FMachineTime,t1.FworkBillNo,t5.FWorkShopName,
t1.FworkBillNo+t3.FItemCode+t1.FLotNo as FLink
from NSDispatchList t1
left join nsworkrouting t2 on t1.NSWorkRouting_FK = t2.NSWorkRoutingOID
left join NSWorkOrder t3 on t2.NSWorkOrder_FK = t3.NSWorkOrderOID
left join NSItemMaster t4 on t3.FItemCode = t4.FItemCode
left join NSWorkShopView t5 on t3.NSWorkShopView_FK = t5.NSWorkShopViewOID
group by t3.FItemCode,t3.FItemName,t3.FItemModel,t3.FSeOrderType,t4.F_108,t1.FLotNo,t1.FworkBillNo+t3.FItemCode+t1.FLotNo,t1.FworkBillNo,t5.FWorkShopName
) as m
---------------------------------------------------------------------------------------
left join(--首工序
select t1.FAuxQty,t1.FAccountQty,t1.FPlanStartDatetime,t1.FPlanEndDatetime,t5.FLink
from NSDispatchList t1
left join NSWorkOrder t2 on t1.NSWorkOrder_FK = t2.NSWorkOrderOID
left join(select t3.FOperCode,t3.FworkBillNo+t4.FItemCode+t3.FLotNo as FLink from NSDispatchList t3
left join NSWorkOrder t4 on t3.NSWorkOrder_FK = t4.NSWorkOrderOID where t3.FPreOperCode is null
) as t5 on t1.FworkBillNo+t2.FItemCode+t1.FLotNo = t5.FLink and t1.FOperCode = t5.FOperCode
) as n on m.FLink = n.FLink
---------------------------------------------------------------------------------------
left join(--末工序
select top 1 case when t4.FRateWay = 0 then
ISNULL(t1.FAccountQty,0)-isnull(t1.FNQ,0)-isnull(t1.FHoldQty,0)-isnull(t1.FSetQty,0)
else
ISNULL(t1.FAccountQty,0)-isnull(t1.FNQ,0)-isnull(t1.FHoldQty,0)-isnull(t1.FSourceNGQty,0)
end as FPassQty,t1.FActStartDatetime,t1.FActEndDatetime,t1.FNQ as FTotalNQ,
t1.FworkBillNo+t2.FItemCode+t1.FLotNo as FLink
from NSDispatchList t1
left join NSWorkOrder t2 on t1.NSWorkOrder_FK = t2.NSWorkOrderOID
left join nsworkrouting t3 on t1.NSWorkRouting_FK = t3.NSWorkRoutingOID
left join nsoperate t4 on t1.FOperCode = t4.FOperCode
order by t3.FSortNo desc
) as x on m.FLink = x.FLink
---------------------------------------------------------------------------------------
left join(
select sum(t1.FNGQty) as FTotalNG1,t2.FworkBillNo+t3.FItemCode+t2.FLotNo as FLink
from NSDispatchNGDetail t1
left join NSDispatchList t2 on t1.NSDispatchList_FK = t2.NSDispatchListOID
left join NSWorkOrder t3 on t2.NSWorkOrder_FK = t3.NSWorkOrderOID
left join NSNGDetail t4 on t1.NSNGDetail_FK = NSNGDetailOID
where t4.FNGType = 1 group by t2.FworkBillNo+t3.FItemCode+t2.FLotNo
) as y on m.FLink = y.FLink
---------------------------------------------------------------------------------------
left join(
select sum(t1.FNGQty) as FTotalNG2,t2.FworkBillNo+t3.FItemCode+t2.FLotNo as FLink
from NSDispatchNGDetail t1
left join NSDispatchList t2 on t1.NSDispatchList_FK = t2.NSDispatchListOID
left join NSWorkOrder t3 on t2.NSWorkOrder_FK = t3.NSWorkOrderOID
left join NSNGDetail t4 on t1.NSNGDetail_FK = NSNGDetailOID
where t4.FNGType = 0 group by t2.FworkBillNo+t3.FItemCode+t2.FLotNo
) as z on m.FLink = z.FLink
---------------------------------------------------------------------------------------
left join(
select t1.FAuxNGQty as FFaceNQ,t1.FworkBillNo+t2.FItemCode+t1.FLotNo as FLink from NSDispatchList t1
left join NSWorkOrder t2 on t1.NSWorkOrder_FK = t2.NSWorkOrderOID
where t1.FOperName like '%外观检查%'
) as w on m.FLink = w.FLink