


CREATE PROCEDURE [dbo].[ApiProdOperationList]

	@StateId AS INT
	, @RefOpeId AS int
	, @Size decimal
	, @Material nvarchar(10)
	, @VendorId int
	, @Page int
	, @ReadyTodo int
	, @OperatorParticipant int
		, @Filter nvarchar(100)
	
AS 
BEGIN

IF ( @StateId =0)
BEGIN
select * , 
@StateId StateId , 0 OperatorId , cast (0 as decimal) SumQuantity ,CURRENT_TIMESTAMP TS, cast (0 as decimal) ActivQuantity
from dbo.[ApiProdOperationsAllBaseView]
where ProdDocStateId = @StateId 
and (@RefOpeId =0 and 
ISNULL((select p.Permission from  dbo.ApiPermissions p JOIN dbo.ApiDictionaries d ON d.Id = p.ObjectId where   d.ErpId = ReferenceOpeId and p.OperatorId = @VendorId),2) >=1
OR (@RefOpeId <>0 and ReferenceOpeId = @RefOpeId))
and ( @Material = '' OR @Material  = Material)
and ( @Size =0 OR Size = @Size)

and (@Filter = 'all' OR CustInfo LIKE '%'+@Filter+'%' OR Name LIKE '%'+@Filter+'%'  OR Code LIKE '%'+@Filter+'%'
OR ProdDocNumber LIKE '%'+@Filter+'%' OR ProductCode LIKE '%'+@Filter+'%' OR ProductName LIKE '%'+@Filter+'%'
OR MainProdDocNumber LIKE '%'+@Filter+'%' OR MainProductCode LIKE '%'+@Filter+'%' OR MainProductName LIKE '%'+@Filter+'%'
)

ORDER BY DueDate 
OFFSET  50*(@Page - 1) ROWS FETCH NEXT 50 ROWS ONLY;

END
IF ( @StateId = 10)
BEGIN
select * , 
@StateId StateId , 0 OperatorId , cast (0 as decimal) SumQuantity,CURRENT_TIMESTAMP TS
from dbo.[ApiProdOperationsAllBaseView] ope
where 
  (@RefOpeId =0 and 
ISNULL((select p.Permission from  dbo.ApiPermissions p JOIN dbo.ApiDictionaries d ON d.Id = p.ObjectId where   d.ErpId = ReferenceOpeId and p.OperatorId = @VendorId),2) >=1
OR (@RefOpeId <>0 and ReferenceOpeId = @RefOpeId))
 and NOT EXISTS ( select * from dbo.ApiProdActivities where OperationId = ope.ID)
 and ( @Material = '' OR @Material  = Material)
 and ( @Size =0 OR Size = @Size)
 and (( @ReadyTodo =0 and ActivQuantity> 0) OR ( @ReadyTodo= 1 and ActivQuantity <=0) OR @ReadyTodo=2)

 and (@Filter = 'all' OR CustInfo LIKE '%'+@Filter+'%' OR Name LIKE '%'+@Filter+'%'  OR Code LIKE '%'+@Filter+'%'
OR ProdDocNumber LIKE '%'+@Filter+'%' OR ProductCode LIKE '%'+@Filter+'%' OR ProductName LIKE '%'+@Filter+'%'
OR MainProdDocNumber LIKE '%'+@Filter+'%' OR MainProductCode LIKE '%'+@Filter+'%' OR MainProductName LIKE '%'+@Filter+'%'
)

 ORDER BY DueDate 
 OFFSET  50*(@Page - 1) ROWS FETCH NEXT 50 ROWS ONLY;

END
IF ( @StateId = 20) -- uruchomione
BEGIN
select * , 
@StateId StateId , opeSum.OperatorId OperatorId , opeSum.SumQuantity ,opeSum.TS 
from dbo.[ApiProdOperationsAllBaseView] ope
JOIN ( select OperationId, SUM(Quantity) SumQuantity, MAX(TS) TS, MAX(OperatorId) OperatorId  from dbo.ApiProdActivities GROUP BY OperationId) opeSum ON opeSum.OperationId = ope.Id
where 
  (@RefOpeId =0 and 
ISNULL((select p.Permission from  dbo.ApiPermissions p JOIN dbo.ApiDictionaries d ON d.Id = p.ObjectId where   d.ErpId = ReferenceOpeId and p.OperatorId = @VendorId),2) >=1
OR (@RefOpeId <>0 and ReferenceOpeId = @RefOpeId))
 and ( @Material = '' OR @Material  = Material)
 and
EXISTS  ( select * from dbo.ApiUnfinishedProdActivities where OperationId = ope.ID)
and ( @Size =0 OR Size = @Size)
 and (( @ReadyTodo =0 and ActivQuantity> opeSum.SumQuantity) OR ( @ReadyTodo= 1 and ActivQuantity <=opeSum.SumQuantity) OR @ReadyTodo=2)

and (@Filter = 'all' OR CustInfo LIKE '%'+@Filter+'%' OR Name LIKE '%'+@Filter+'%'  OR Code LIKE '%'+@Filter+'%'
OR ProdDocNumber LIKE '%'+@Filter+'%' OR ProductCode LIKE '%'+@Filter+'%' OR ProductName LIKE '%'+@Filter+'%'
OR MainProdDocNumber LIKE '%'+@Filter+'%' OR MainProductCode LIKE '%'+@Filter+'%' OR MainProductName LIKE '%'+@Filter+'%'
)

and (@OperatorParticipant =0 OR (@OperatorParticipant<>0 and EXISTS (select * from dbo.ApiProdActivities where OperationId = ope.Id and OperatorId = @OperatorParticipant ) ))

ORDER BY DueDate
OFFSET  50*(@Page - 1) ROWS FETCH NEXT 50 ROWS ONLY;

END
IF ( @StateId = 50) 
BEGIN
select * , 
@StateId StateId , opeSum.OperatorId OperatorId , opeSum.SumQuantity ,opeSum.TS 
from dbo.[ApiProdOperationsAllBaseView] ope
JOIN ( select OperationId, SUM(Quantity) SumQuantity, MAX(TS) TS, MAX(OperatorId) OperatorId  from dbo.ApiProdActivities GROUP BY OperationId) opeSum ON opeSum.OperationId = ope.Id
where 
  (@RefOpeId =0 and 
ISNULL((select p.Permission from  dbo.ApiPermissions p JOIN dbo.ApiDictionaries d ON d.Id = p.ObjectId where   d.ErpId = ReferenceOpeId and p.OperatorId = @VendorId),2) >=1
OR (@RefOpeId <>0 and ReferenceOpeId = @RefOpeId))
 and ( @Material = '' OR @Material  = Material) and
EXISTS  ( select * from dbo.ApiProdActivities where OperationId = ope.ID and StateId=50)
and ( @Size =0 OR Size = @Size)
and (( @ReadyTodo =0 and ActivQuantity> opeSum.SumQuantity) OR ( @ReadyTodo= 1 and ActivQuantity <=opeSum.SumQuantity) OR @ReadyTodo=2)

and (@Filter = 'all' OR CustInfo LIKE '%'+@Filter+'%' OR Name LIKE '%'+@Filter+'%'  OR Code LIKE '%'+@Filter+'%'
OR ProdDocNumber LIKE '%'+@Filter+'%' OR ProductCode LIKE '%'+@Filter+'%' OR ProductName LIKE '%'+@Filter+'%'
OR MainProdDocNumber LIKE '%'+@Filter+'%' OR MainProductCode LIKE '%'+@Filter+'%' OR MainProductName LIKE '%'+@Filter+'%'
)

and (@OperatorParticipant =0 OR (@OperatorParticipant<>0 and EXISTS (select * from dbo.ApiProdActivities where OperationId = ope.Id and OperatorId = @OperatorParticipant ) ))

ORDER BY DueDate 
OFFSET  50*(@Page - 1) ROWS FETCH NEXT 50 ROWS ONLY;
END
IF ( @StateId = 30) 
BEGIN
select * , 
@StateId StateId , opeSum.OperatorId OperatorId , opeSum.SumQuantity ,opeSum.TS 
from dbo.[ApiProdOperationsAllBaseView] ope
JOIN ( select OperationId, SUM(Quantity) SumQuantity, MAX(TS) TS, MAX(OperatorId) OperatorId  from dbo.ApiProdActivities GROUP BY OperationId) opeSum ON opeSum.OperationId = ope.Id
where 
  (@RefOpeId =0 and 
ISNULL((select p.Permission from  dbo.ApiPermissions p JOIN dbo.ApiDictionaries d ON d.Id = p.ObjectId where   d.ErpId = ReferenceOpeId and p.OperatorId = @VendorId),2) >=1
OR (@RefOpeId <>0 and ReferenceOpeId = @RefOpeId))
 and ( @Material = '' OR @Material  = Material) and
NOT EXISTS  ( select * from dbo.ApiUnfinishedProdActivities where OperationId = ope.ID)
and NOT EXISTS  ( select * from dbo.ApiProdActivities where OperationId = ope.ID and StateId=50)
and ( @Size =0 OR Size = @Size)
and (( @ReadyTodo =0 and ActivQuantity> opeSum.SumQuantity) OR ( @ReadyTodo= 1 and ActivQuantity <=opeSum.SumQuantity) OR @ReadyTodo=2)

and (@Filter = 'all' OR CustInfo LIKE '%'+@Filter+'%' OR Name LIKE '%'+@Filter+'%'  OR Code LIKE '%'+@Filter+'%'
OR ProdDocNumber LIKE '%'+@Filter+'%' OR ProductCode LIKE '%'+@Filter+'%' OR ProductName LIKE '%'+@Filter+'%'
OR MainProdDocNumber LIKE '%'+@Filter+'%' OR MainProductCode LIKE '%'+@Filter+'%' OR MainProductName LIKE '%'+@Filter+'%'
)
and (@OperatorParticipant =0 OR (@OperatorParticipant<>0 and EXISTS (select * from dbo.ApiProdActivities where OperationId = ope.Id and OperatorId = @OperatorParticipant ) ))

ORDER BY DueDate 
--OFFSET  50*(@Page - 1) ROWS FETCH NEXT 50 ROWS ONLY;
END


 
END
