

--exec [dbo].[API_DocumentUpdateErp] 84232

CREATE PROCEDURE [dbo].[API_DocumentUpdateErp]
	@DocErpId AS INT
AS 
BEGIN

-- zabezpieczenie przed pętlą czyli sprawdzenie czy 

IF (NOT EXISTS( select * from  dbo.ApiSyncItems where ObjectId = @DocErpId and ObjectType=0 and  HistId = 
(select MAX(ci.Id) 
from dbo.ChangeInfos ci JOIN dbo.DokHandlowe z ON  SourceTable='DokHandlowe' and SourceGuid= z.Guid
where z.ID = @DocErpId)))
BEGIN
 

UPDATE
dbo.ApiDocuments -- WITH (UPDLOCK, SERIALIZABLE) 
  SET  
  [ErpId] = cv.ErpId
           ,[CustId]= cv.[CustId]
           ,[CustCustId] = cv.[CustCustId]
           ,[CustLocId] = cv.[CustLocId]
           ,[CustLocLocId] = cv.[CustLocLocId]
           ,[NumberString] = cv.[NumberString]
           ,[ErpNumberString] = cv.[ErpNumberString]
           ,[Number]  = cv.[Number]
           ,[Year] = cv.[Year]
           ,[Month] = cv. [Month]
           ,[DefId] = cv.[DefId]
           ,[Series] = cv. [Series]
           ,[Date] = cv.Date
           ,[Date2] = cv.Date2
           ,[Description] = cv.[Description]
           ,[State] = cv.[State]
           ,[BarCode] = cv. [BarCode]
           ,[VendorId] = cv. [VendorId]
           ,[Lines] = cv. Lines
           ,[Value] = cv.Value
           ,[Currency] = cv. [Currency]
           ,[VeryfiedDoc] = cv. [VeryfiedDoc]
           ,[RepositoryId] = cv. [RepositoryId]
           ,[Info] = cv.Info
           ,[FlowDirection] = cv. [FlowDirection]
           ,[PrecedentDoc] = cv. [PrecedentDoc]
           ,[PrecedentDocNumber] = cv.[PrecedentDocNumber]
           ,[ConfirmTransfer] = cv. [ConfirmTransfer]
           ,[TS] = CURRENT_TIMESTAMP
           ,[SaveTS] = cv.[SaveTS]
           ,[DeviceUid] = cv. [DeviceUid]
           ,[ForeginNumber] = cv. [ForeginNumber]
           ,[Category] = cv.[Category]
           ,[CustErpId] = cv.[CustErpId]
           ,[NettoBrutto] = cv.[NettoBrutto]
           ,[Netto] = cv. [Netto]
           ,[Brutto]= cv. Brutto
           ,[StateDoc]= cv.[StateDoc]
           ,[ReservationResource] = cv.[ReservationResource]
           ,[ReservationType] = cv.[ReservationType]
           ,[OperatorId] = cv.[OperatorId]
           ,[LinesInfo] = cv.[LinesInfo]
           ,[Notes] = cv.Notes
           ,[CreateTS] = cv.[CreateTS]
           ,[ProjectId] = cv.[ProjectId]
           ,[ProjectLineId] = cv. [ProjectLineId]
  from (select *from  dbo.ApiErpDocumentsSyncView cc where cc.ErpId=@DocErpId) as cv
  WHERE 
  dbo.ApiDocuments.ErpId = cv.ErpId and 
  dbo.ApiDocuments.ErpId = @DocErpId;


IF @@ROWCOUNT = 0
BEGIN
  
	--select 'trzeba dodac';  
 INSERT INTO [dbo].ApiDocuments
           ([DocId]
           ,[ErpId]
           ,[CustId]
           ,[CustCustId]
           ,[CustLocId]
           ,[CustLocLocId]
           ,[NumberString]
           ,[ErpNumberString]
           ,[Number]
           ,[Year]
           ,[Month]
           ,[DefId]
           ,[Series]
           ,[Date]
           ,[Date2]
           ,[Description]
           ,[State]
           ,[BarCode]
           ,[VendorId]
           ,[Lines]
           ,[Value]
           ,[Currency]
           ,[VeryfiedDoc]
           ,[RepositoryId]
           ,[Info]
           ,[FlowDirection]
           ,[PrecedentDoc]
           ,[PrecedentDocNumber]
           ,[ConfirmTransfer]
           ,[TS]
           ,[SaveTS]
           ,[DeviceUid]
           ,[ForeginNumber]
           ,[Category]
           ,[CustErpId]
           ,[NettoBrutto]
           ,[Netto]
           ,[Brutto]
           ,[StateDoc]
           ,[ReservationResource]
           ,[ReservationType]
           ,[OperatorId]
           ,[LinesInfo]
           ,[Notes]
           ,[CreateTS]
           ,[ProjectId]
           ,[ProjectLineId])
    select 
            [DocId]
           ,[ErpId]
           ,[CustId]
           ,[CustCustId]
           ,[CustLocId]
           ,[CustLocLocId]
           ,[NumberString]
           ,[ErpNumberString]
           ,[Number]
           ,[Year]
           ,[Month]
           ,[DefId]
           ,[Series]
           ,[Date]
           ,[Date2]
           ,[Description]
           ,[State]
           ,[BarCode]
           ,[VendorId]
           ,[Lines]
           ,[Value]
           ,[Currency]
           ,[VeryfiedDoc]
           ,[RepositoryId]
           ,[Info]
           ,[FlowDirection]
           ,[PrecedentDoc]
           ,[PrecedentDocNumber]
           ,[ConfirmTransfer]
           ,[TS]
           ,[SaveTS]
           ,[DeviceUid]
           ,[ForeginNumber]
           ,[Category]
           ,[CustErpId]
           ,[NettoBrutto]
           ,[Netto]
           ,[Brutto]
           ,[StateDoc]
           ,[ReservationResource]
           ,[ReservationType]
           ,[OperatorId]
           ,[LinesInfo]
           ,[Notes]
           ,[CreateTS]
           ,[ProjectId]
           ,[ProjectLineId]
		   from dbo.ApiErpDocumentsSyncView where ErpId = @DocErpId

END


-- elementy urządzenia w zadaniu
-- dodajmy te których nie ma
INSERT INTO [dbo].ApiLines
           ([lineId]
           ,[ErpId]
           ,[lp]
           ,[documentId]
           ,[IdDocId]
           ,[itemId]
           ,[itemName]
           ,[itemCode]
           ,[itemDescription]
           ,[itemPrice]
           ,[itemOrgPrice]
           ,[discount]
           ,[quantity]
           ,[value]
           ,[itemEAN]
           ,[itemUM]
           ,[itemUM2]
           ,[veryfiedQuantity]
           ,[LocationName]
           ,[Batch]
           ,[BatchId]
           ,[BatchEAN]
           ,[CategoryId]
           ,[CategoryName]
           ,[CategoryName2]
           ,[VendorId]
           ,[Status]
           ,[ItemStatus]
           ,[lineStatus]
           ,[lineChance]
           ,[lineType]
           ,[Netto]
           ,[Brutto]
           ,[TaxRate]
           ,[TaxDesc]
           ,[ReservationType]
           ,[Direction]
           ,[RepositoryId])
   SELECT [lineId]
           ,[ErpId]
           ,[lp]
           ,[documentId]
           ,[IdDocId]
           ,[itemId]
           ,[itemName]
           ,[itemCode]
           ,[itemDescription]
           ,[itemPrice]
           ,[itemOrgPrice]
           ,[discount]
           ,[quantity]
           ,[value]
           ,[itemEAN]
           ,[itemUM]
           ,[itemUM2]
           ,[veryfiedQuantity]
           ,[LocationName]
           ,[Batch]
           ,[BatchId]
           ,[BatchEAN]
           ,[CategoryId]
           ,[CategoryName]
           ,[CategoryName2]
           ,[VendorId]
           ,[Status]
           ,[ItemStatus]
           ,[lineStatus]
           ,[lineChance]
           ,[lineType]
           ,[Netto]
           ,[Brutto]
           ,[TaxRate]
           ,[TaxDesc]
           ,[ReservationType]
           ,[Direction]
           ,[RepositoryId]
  FROM [dbo].ApiErpDocLinesSyncView
  where ErpDocId = @DocErpId and [ErpId] NOT in ( select ErpId from dbo.ApiLines /*where Modified>=0*/)



-- update wszystkich

  UPDATE [dbo].ApiLines
  set 
  [lineId] = dl.[lineId]
           ,[ErpId] = dl.[ErpId]
           ,[lp] = dl.lp
           ,[documentId] = dl.[documentId]
           ,[IdDocId] = dl.[IdDocId]
           ,[itemId] = dl.[itemId]
           ,[itemName] = dl.[itemName]
           ,[itemCode] = dl.[itemCode]
           ,[itemDescription] = dl.[itemDescription]
           ,[itemPrice] = dl.[itemPrice]
           ,[itemOrgPrice] = dl.[itemOrgPrice]
           ,[discount] = dl.[discount]
           ,[quantity] = dl.[quantity]
           ,[value] = dl.[value]
           ,[itemEAN] = dl.[itemEAN]
           ,[itemUM] = dl.[itemUM]
           ,[itemUM2] = dl.[itemUM2]
           ,[veryfiedQuantity] = dl.[veryfiedQuantity]
           ,[LocationName] = dl.[LocationName]
           ,[Batch] = dl.[Batch]
           ,[BatchId] = dl.[BatchId]
           ,[BatchEAN] = dl.[BatchEAN]
           ,[CategoryId] = dl.[CategoryId]
           ,[CategoryName] = dl.[CategoryName]
           ,[CategoryName2] = dl.[CategoryName2]
           ,[VendorId] = dl.[VendorId]
           ,[Status] = dl.[Status]
           ,[ItemStatus] = dl.[ItemStatus]
           ,[lineStatus] = dl.[lineStatus]
           ,[lineChance] = dl.[lineChance]
           ,[lineType] = dl.[lineType]
           ,[Netto] = dl.[Netto]
           ,[Brutto] = dl.[Brutto]
           ,[TaxRate] = dl.[TaxRate]
           ,[TaxDesc] = dl.[TaxDesc]
           ,[ReservationType] = dl.[ReservationType]
           ,[Direction] = dl.[Direction]
           ,[RepositoryId] = dl.[RepositoryId]
		   from (select * from  dbo.ApiErpDocLinesSyncView dlv where dlv.ErpDocId=@DocErpId) as dl
  WHERE 
  dbo.ApiLines.ErpId = dl.ErpId and 
  dbo.ApiLines.documentId = dl.documentId


  --and ApiLines.Modified>=0
  delete dbo.ApiLines where documentId =@DokId and ErpId<>0 
  and not exists ( select * from dbo.ApiLinesInfoView where dbo.ApiLinesInfoView.ErpId =  dbo.ApiLines.ErpId);

END
END

