


--exec [dbo].[API_WMSDokCreate] 1517,604, 0,0,0,0;

CREATE PROCEDURE [dbo].[API_WMSDokCreate]
	 @ObjectId as INT,
	 @ObjectType AS INT,
	 @OperatorId as INT,
	 @SyncId as INT,
	 @Additionally as int,
	 @Direction as int -- dodane później

AS 
BEGIN
-- Należy ustalić takie  zmienne
declare @RepositoryId int;

declare @OrgDocumentNumber [nvarchar](30);
declare @Description nvarchar(max);

IF (@ObjectType = 501 
and EXISTS (select * from dbo.Operacje op JOIN dbo.ApiDictionaries ON ParentDefId = 511 and OperacjaWzor = ErpId and OperatorAlgorithm LIKE '%MATERIAL|%' and op.ID = @ObjectId))
BEGIN
select 'wydawanie towaru poprzez akcje';
END
ELSE
BEGIN
IF ( @Additionally=0 and  @ObjectType<>500 and @ObjectType<>501 and  @ObjectType<>604 and 
ISNULL((select count(*) from   [dbo].[ApiWmsBaseLinesView]  where ObjectId = @ObjectId and ObjectType = @ObjectType),0)=0)
BEGIN 
select 'no lines';
INSERT INTO [dbo].[ApiSyncItems]
           ([SyncId],[ObjectSaveTS],[SchemaId],[ObjectType],[ObjectId],[Message],[Status],[HistId],[TS])
     VALUES
           (@SyncId,[dbo].[ApiGetSec](CURRENT_TIMESTAMP),-@ObjectType, 40,@ObjectId,'',2,0,CURRENT_TIMESTAMP);
END
ELSE
BEGIN



-- procedura dla dokumentów ERP (10)
IF (@ObjectType =10 OR @ObjectType =604)
BEGIN
  select  @Direction = case when ( Korekta =0)  then KierunekMagazynu else -1* KierunekMagazynu end, @RepositoryId = Magazyn , @OrgDocumentNumber = NumerPelny , @Description=Opis from dbo.DokHandlowe where ID = @ObjectId;
END
ELSE IF (@ObjectType = 500) -- zlecenie produkcyjne
BEGIN
  select  /*@Direction = -1 , */@RepositoryId = Magazyn , @OrgDocumentNumber = NumerPelny , @Description=Opis from dbo.DokHandlowe where ID = @ObjectId;
END
ELSE IF (@ObjectType = 504) -- oddawanie produktów na odbiciach
BEGIN
  select  /*@Direction = 1 ,*/ @RepositoryId = 1 , @OrgDocumentNumber = o.ProdDocNumber +'['+ Name +']' , @Description=Name+'-'+  ISNULL(a.Description,'') from [dbo].[ApiProdActivities]  a JOIN dbo.ApiProdOperationsView o ON a.OperationId = o.Id where a.Id = @ObjectId;
END
ELSE IF (@ObjectType = 502) -- oddawanie produktów na akcjach -- może też być przyjęcie...
BEGIN
  select  /*@Direction = 1 ,*/ @RepositoryId = 1 , @OrgDocumentNumber = a.NumberString  , @Description= ISNULL(a.Description,'') from [dbo].ApiProdActions a where  a.Id = @ObjectId;
END

ELSE IF (@ObjectType = 501) -- oddawanie produktów
BEGIN
  select  /*@Direction = -1 ,*/ @RepositoryId = 1 , @OrgDocumentNumber = ProdDocNumber +'['+  Name +']' , @Description= Name+'-'+ ISNULL(Description,'')  from [dbo].ApiProdOperationsView where Id = @ObjectId;
END





-- ustalamy definicje WMS oraz kolejmy numer
declare @DefId int;
declare @DefSymbol nvarchar(30);

select  @DefId =Id, @DefSymbol = Symbol from dbo.ApiDefinitions where [Default] = 1 and Type=40 and FlowDirection = @Direction ;

-- ustalamy numer
declare @number int;
set @number = 1;
set @number = ISNULL(( select MAX(Number) from dbo.ApiWmsDocuments where DefId = @DefId and [Year] = Year(CURRENT_TIMESTAMP) and Month = MONTH(CURRENT_TIMESTAMP)),0)+1;



declare @NumberString nvarchar(30);
set @NumberString= CONCAT(@DefSymbol,'/' ,RIGHT('0000'+CAST(@number AS VARCHAR(4)),4), '/',RIGHT('00'+CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR(2)),2),'/',  Year(CURRENT_TIMESTAMP)%100 );

--select @number, @DefId, @Direction,@OrgDocumentNumber,@RepositoryId,@NumberString , @ObjectId;

declare @WmsDocId int;
-- procedura dla dokumentów ERP (10)
IF (@ObjectType =10 OR @ObjectType =500 OR @ObjectType =604)
BEGIN
INSERT INTO [dbo].[ApiWmsDocuments]
           ([ErpId],[Direction],[DefId],[StateId],[Number],[Year],[Month],[Series],[NumberString]
           ,[Priority],[PlaceId],[CustId],[CustInfo]
           ,[Date],[OperatorId],[CreateTS],[OwnerId]
           ,[TS],[Description],[ObjectId],[ObjectType], RepositoryId,OrgDocumentNumber,Info, Info2, Link, ExtId, ReciverId,ForeginNumber,Additionally, CategoryId, StateDoc, DepartamentId,
		 ProjectId,   ProjectLineId
		   )
select  case when (@ObjectType =10 and d.Definicja <> 85) then d.ID else 0 end ErpId, @Direction Direction, 
@DefId DefId,
0 StateId,
@number Number, YEAR(CURRENT_TIMESTAMP) [Year], MONTH(CURRENT_TIMESTAMP)  [Month],'' Series, @NumberString NumberString,
0 [Priority],  0 PlaceId, ISNULL(Kontrahent,0) CustId,CONCAT('[',ISNULL(k.Kod,''),'] ' ,ISNULL(k.Nazwa,'')) CustInfo,
CURRENT_TIMESTAMP Date, 0 OperatorId, CURRENT_TIMESTAMP CreateTS,
@OperatorId OwnerId, CURRENT_TIMESTAMP TS, @Description Description, @ObjectId ObjectId, 
case when (@ObjectType = 604) then 10 else @ObjectType end ObjectType , 
Magazyn RepositoryId,
@OrgDocumentNumber OrgDocumentNumber ,'' Info, '' Info2, '' Link, '' ExtId, 0 ReciverId, '' ForeginNumber, @Additionally,  0 CategoryId, 0 StateDoc, 0 DepartamentId,
0 ProjectId,   0 ProjectLineId
from dbo.DokHandlowe d LEFT OUTER JOIN dbo.Kontrahenci k ON d.Kontrahent = k.ID
 where d.ID = @ObjectId;

  
 set @WmsDocId = (SELECT SCOPE_IDENTITY());
 /*
 select  case when (@ObjectType =10) then d.ID else 0 end ErpId, @Direction Direction, 
@DefId DefId,
0 StateId,
@number Number, YEAR(CURRENT_TIMESTAMP) [Year], MONTH(CURRENT_TIMESTAMP)  [Month],'' Series, @NumberString NumberString,
0 [Priority],  0 PlaceId, ISNULL(Kontrahent,0) CustId,CONCAT('[',ISNULL(k.Kod,''),'] ' ,ISNULL(k.Nazwa,'')) CustInfo,
CURRENT_TIMESTAMP Date, 0 OperatorId, CURRENT_TIMESTAMP CreateTS,
@OperatorId OwnerId, CURRENT_TIMESTAMP TS, @Description Description, @ObjectId ObjectId, @ObjectType ObjectType , Magazyn RepositoryId,
@OrgDocumentNumber OrgDocumentNumber ,'' Info,  '' Info2, '' Link, '' ExtId
from dbo.DokHandlowe d LEFT OUTER JOIN dbo.Kontrahenci k ON d.Kontrahent = k.ID
 where d.ID = @ObjectId;

 */

 END
 ELSE IF (@ObjectType =504)
 BEGIN
 INSERT INTO [dbo].[ApiWmsDocuments]
           ([ErpId],[Direction],[DefId],[StateId],[Number],[Year],[Month],[Series],[NumberString]
           ,[Priority],[PlaceId],[CustId],[CustInfo]
           ,[Date],[OperatorId],[CreateTS],[OwnerId]
           ,[TS],[Description],[ObjectId],[ObjectType], RepositoryId,OrgDocumentNumber,Info, Info2, Link, ExtId, ReciverId,ForeginNumber,Additionally, CategoryId, StateDoc, DepartamentId,
		 ProjectId,   ProjectLineId
		   )
select  0 [ErpId], @Direction Direction, 
@DefId DefId,
0 StateId,
@number Number, YEAR(CURRENT_TIMESTAMP) [Year], MONTH(CURRENT_TIMESTAMP)  [Month],'' Series, @NumberString NumberString,
0 [Priority],  0 PlaceId, ISNULL(a.CustId,0) CustId, a.CustInfo CustInfo,
CURRENT_TIMESTAMP Date, 0 OperatorId, CURRENT_TIMESTAMP CreateTS,
@OperatorId OwnerId, CURRENT_TIMESTAMP TS, @Description Description, 
/*@ObjectId*/ a.OperationId ObjectId, /*@ObjectType*/ 501 ObjectType ,
@RepositoryId RepositoryId,
@OrgDocumentNumber OrgDocumentNumber ,'' Info, '' Info2, '' Link, '' ExtId, 0 ReciverId, '' ForeginNumber, @Additionally,  0 CategoryId, 0 StateDoc, 0 DepartamentId,
0 ProjectId,   0 ProjectLineId
from dbo.ApiProdActivities a
 where a.ID = @ObjectId;

  
 set @WmsDocId = (SELECT SCOPE_IDENTITY());

 -- dodaje relacje pomiędzy akcją 

 END
 ELSE IF (@ObjectType =502)
 BEGIN
 INSERT INTO [dbo].[ApiWmsDocuments]
           ([ErpId],[Direction],[DefId],[StateId],[Number],[Year],[Month],[Series],[NumberString]
           ,[Priority],[PlaceId],[CustId],[CustInfo]
           ,[Date],[OperatorId],[CreateTS],[OwnerId]
           ,[TS],[Description],[ObjectId],[ObjectType], RepositoryId,OrgDocumentNumber,Info, Info2, Link, ExtId, ReciverId,ForeginNumber,Additionally, CategoryId, StateDoc, DepartamentId,
		 ProjectId,   ProjectLineId
		   )
select  ErpId, @Direction Direction, 
@DefId DefId,
0 StateId,
@number Number, YEAR(CURRENT_TIMESTAMP) [Year], MONTH(CURRENT_TIMESTAMP)  [Month],'' Series, @NumberString NumberString,
0 [Priority],  0 PlaceId, ISNULL(a.CustId,0) CustId, a.CustInfo CustInfo,
CURRENT_TIMESTAMP Date, 0 OperatorId, CURRENT_TIMESTAMP CreateTS,
@OperatorId OwnerId, CURRENT_TIMESTAMP TS, @Description Description, @ObjectId ObjectId, @ObjectType ObjectType , @RepositoryId RepositoryId,
@OrgDocumentNumber OrgDocumentNumber ,'' Info, '' Info2, '' Link, '' ExtId, 0 ReciverId, '' ForeginNumber, @Additionally,  0 CategoryId, 0 StateDoc, 0 DepartamentId,
0 ProjectId,   0 ProjectLineId
from dbo.ApiProdActions a
 where a.ID = @ObjectId;

  
 set @WmsDocId = (SELECT SCOPE_IDENTITY());

 -- dodaje relacje pomiędzy akcją 

 END
 ELSE IF (@ObjectType =501)
 BEGIN
 INSERT INTO [dbo].[ApiWmsDocuments]
           ([ErpId],[Direction],[DefId],[StateId],[Number],[Year],[Month],[Series],[NumberString]
           ,[Priority],[PlaceId],[CustId],[CustInfo]
           ,[Date],[OperatorId],[CreateTS],[OwnerId]
           ,[TS],[Description],[ObjectId],[ObjectType], RepositoryId,OrgDocumentNumber,Info, Info2, Link, ExtId, ReciverId,ForeginNumber,Additionally, CategoryId, StateDoc, DepartamentId,
		 ProjectId,   ProjectLineId
		   )
select  0 ErpId, @Direction Direction, 
@DefId DefId,
0 StateId,
@number Number, YEAR(CURRENT_TIMESTAMP) [Year], MONTH(CURRENT_TIMESTAMP)  [Month],'' Series, @NumberString NumberString,
0 [Priority],  0 PlaceId, ISNULL(o.CustId,0) CustId, o.CustInfo CustInfo,
CURRENT_TIMESTAMP Date, 0 OperatorId, CURRENT_TIMESTAMP CreateTS,
@OperatorId OwnerId, CURRENT_TIMESTAMP TS, @Description Description, @ObjectId ObjectId, @ObjectType ObjectType , @RepositoryId RepositoryId,
@OrgDocumentNumber OrgDocumentNumber ,'' Info, '' Info2, '' Link, '' ExtId, 0 ReciverId, '' ForeginNumber, @Additionally,  0 CategoryId, 0 StateDoc, 0 DepartamentId,
0 ProjectId,   0 ProjectLineId
from dbo.ApiProdOperationsView o
 where o.ID = @ObjectId;

  
 set @WmsDocId = (SELECT SCOPE_IDENTITY());
 END


 
--select @WmsDocId;
 exec [dbo].[API_WMSDokLinesCreate]
	 @Direction ,	 
	 @RepositoryId , 
	 @ObjectId ,
	 @ObjectType ,
	 @WmsDocId,
	 @NumberString, @Additionally ;


-- weryfikacja czy są wszystkie pozycje przeniesione oprócz usługowych
-- weryfikacja czy ilość w liniach się zgadzają
-- i czy są jakiekolwiek linie


 IF( 
 ISNULL((select count(*) from dbo.[ApiWmsDocLines] where DocumentId=@WmsDocId),0) =0
 OR  -- do do ewentualnego wykomentowania
 (@ObjectType = 10 and ISNULL((select count(distinct ErpId) from dbo.[ApiWmsDocLines] where DocumentId = @WmsDocId),0)
<> 
ISNULL((select count(*) from dbo.[ApiWmsBaseLinesView] p  where  Quantity<>0 and   ObjectId= @ObjectId and ObjectType = @ObjectType ),0)
AND @Additionally=0 )

OR

(EXISTS(select *
from (select ErpId, SUM(OrgQuantity) Quant,DocumentId from dbo.ApiWmsDocLines where ErpId<>0 and DocumentId=@WmsDocId Group By ErpId, DocumentId) wmsLines
JOIN dbo.[ApiWmsBaseLinesView] poz ON poz.LineId = ErpId and poz.ObjectId = @ObjectId and ObjectType = @ObjectType
where poz.Quantity <>wmsLines.Quant
) and @Additionally=0)

)
 
 BEGIN
 
 select 'to delete';
/*
 select *
from (select ErpId, SUM(OrgQuantity) Quant,DocumentId from dbo.ApiWmsDocLines where ErpId<>0 and DocumentId=@WmsDocId Group By ErpId, DocumentId) wmsLines
JOIN dbo.[ApiWmsBaseLinesView] poz ON poz.LineId = ErpId and poz.ObjectId = @ObjectId and ObjectType = @ObjectType
where poz.Quantity <>wmsLines.Quant;
*/
--select * from dbo.ApiWmsDocLines where DocumentId= @WmsDocId;
select * from dbo.PozycjeDokHan p where Dokument = @ObjectId 
and NOT EXISTS ( select * from dbo.ApiWmsDocLines where DocumentId= @WmsDocId and ErpId = p.ID);

    delete dbo.ApiWmsDocLines where DocumentId= @WmsDocId;
	delete dbo.ApiWmsDocuments where Id = @WmsDocId;

	 select 'czy jest dokument?';
	select * from  dbo.ApiWmsDocLines where DocumentId= @WmsDocId;

 END
 ELSE
 BEGIN
 
 INSERT INTO [dbo].[ApiSyncItems]
           ([SyncId],[ObjectSaveTS],[SchemaId],[ObjectType],[ObjectId],[Message],[Status],[HistId],[TS])
     VALUES
           (@SyncId,[dbo].[ApiGetSec](CURRENT_TIMESTAMP),-@ObjectType, 40,@ObjectId,'',2,0,CURRENT_TIMESTAMP); 
		   
  IF (@Additionally <>0)
  BEGIN
	update dbo.ApiWmsAdditionallyDocs set WmsDocumentId = @WmsDocId where WmsDocumentId = 0 and ObjectId =@ObjectId and ObjectType = @ObjectType;
  END
END

 END

 select * from dbo.ApiWmsDocuments where Id = @WmsDocId;
 END
 
 END
