CREATE PROCEDURE [dbo].[CreateSummaryAccountingDocuments] @DocId BIGINT = NULL
 AS
DECLARE @DocumentId BIGINT 
DECLARE @DocumentGuid UNIQUEIDENTIFIER 
DECLARE @StoreCode VARCHAR(3)
DECLARE @Cache AS TABLE
(
	DocumentId BIGINT,
	Type CHAR(1) ,
	Acc_Code VARCHAR(20) ,
	BEDEH MONEY,
	BESTAN MONEY,
	Store_Code VARCHAR(3)
)

DECLARE @Document AS TABLE
(
	DocumentId BIGINT,
	Type CHAR(1) ,
	Acc_Code VARCHAR(20) ,
	BEDEH MONEY,
	BESTAN MONEY,
	Store_Code VARCHAR(3)
)

BEGIN TRY
BEGIN TRAN
--------------------(Update Store_Code At Heads And Sanad Table When Store_code Is Null)-------------------------
SELECT @StoreCode = Code FROM dbo.Shops WHERE IsDefault = 1
UPDATE Sanad SET Store_Code = @StoreCode
WHERE ISNULL(Store_Code ,'') = '' and Id_Sanad=ISNULL(@DocId, Id_Sanad)

UPDATE Heads SET Store_Code = @StoreCode
WHERE ISNULL(Store_Code ,'') = '' and Id_Sanad=ISNULL(@DocId, Id_Sanad)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO @Document (DocumentId, Acc_code,  Bedeh,  Bestan, Type,Store_Code)
SELECT id_Sanad, Acc_code, SUM(Bedeh) Bedeh, SUM(Bestan) Bestan, Type,Store_Code FROM dbo.Sanad WITH (NOLOCK)
WHERE ISNULL(ChainFlag,0) = 0 and Id_Sanad=ISNULL(@DocId, Id_Sanad)
GROUP BY Id_Sanad, Acc_code, Type,Store_Code
ORDER BY Id_Sanad

DECLARE CursorSummarySanad CURSOR FOR
SELECT DocumentId FROM @Document
GROUP BY DocumentId
OPEN CursorSummarySanad
FETCH NEXT FROM CursorSummarySanad INTO @DocumentId
WHILE @@FETCH_STATUS = 0 
BEGIN
  SET @DocumentGuid = NEWID()
  IF NOT EXISTS(SELECT DocumentId FROM dbo.SummaryAccountingDocuments WHERE DocumentId = @DocumentId)
  BEGIN 
	INSERT INTO dbo.SummaryAccountingDocuments  ( Id , Number , TotalDebt , TotalCreditor , IsBalance , Note , IsLock , IsAuto ,Tarikh , Store_Code, DocumentId )
	SELECT @DocumentGuid, Nu_Manual , T_BEDEH , T_BESTAN , Balance , Note , Fiexd , Auto ,Tarikh_User , Store_Code, Id_Sanad 
	FROM Heads WITH (NOLOCK) WHERE Id_Sanad = @DocumentId
  END
  ELSE
    SELECT @DocumentGuid = Id FROM SummaryAccountingDocuments WHERE DocumentId = @DocumentId

  INSERT INTO @Cache ( DocumentId ,Type ,Acc_Code , BEDEH , BESTAN ,Store_Code)
  SELECT DocumentId ,Type,  Code , Debtor , Creditor ,Store_Code FROM dbo.SummaryAccountingDocumentDetails WHERE DocumentId = @DocumentId

  INSERT INTO dbo.SummaryAccountingDocumentDetails (Code , Type , Debtor ,Creditor , Store_Code, AccountingdocumentId, DocumentId )
  SELECT Acc_Code, Type, Bedeh ,  Bestan, Store_Code,  @DocumentGuid, @DocumentId  FROM @Document
  WHERE  DocumentId = @DocumentId 
  AND NOT EXISTS(SELECT Acc_Code FROM dbo.SummaryAccountingDocumentDetails WHERE DocumentId = @DocumentId AND dbo.SummaryAccountingDocumentDetails.Code = [@Document].Acc_Code AND dbo.SummaryAccountingDocumentDetails.Type = [@Document].Type )

  UPDATE Sanad SET Id = SummaryAccountingDocumentDetails.Id FROM dbo.Sanad
  INNER JOIN SummaryAccountingDocumentDetails ON Sanad.Acc_Code = dbo.SummaryAccountingDocumentDetails.Code
  AND Sanad.Type = SummaryAccountingDocumentDetails.Type 
  WHERE Id_Sanad = @DocumentId

  UPDATE dbo.SummaryAccountingDocumentDetails SET Debtor = T.BEDEH, Creditor = T.BESTAN FROM dbo.SummaryAccountingDocumentDetails
  INNER JOIN 
  (
	   SELECT [@Cache].DocumentId, [@Cache].Acc_Code, [@Cache].Type, [@Cache].BEDEH + [@Document].BEDEH Bedeh, 
	   [@Cache].BESTAN + [@Document].BESTAN BESTAN FROM @Cache INNER JOIN @Document 
	   ON [@Document].Acc_Code = [@Cache].Acc_Code AND [@Document].DocumentId = [@Cache].DocumentId
	   AND [@Cache].Type = [@Document].Type
  ) AS T ON SummaryAccountingDocumentDetails.Code = T.Acc_Code AND SummaryAccountingDocumentDetails.DocumentId = T.DocumentId AND T.Type = SummaryAccountingDocumentDetails.Type

  UPDATE Sanad SET ChainFlag = 1, AccountingdocumentId = @DocumentGuid WHERE Id_Sanad = @DocumentId AND ISNULL(ChainFlag,0) = 0
  UPDATE Heads SET Id = @DocumentGuid WHERE Id_Sanad = @DocumentId

  FETCH NEXT FROM CursorSummarySanad INTO @DocumentId
END
CLOSE CursorSummarySanad
DEALLOCATE CursorSummarySanad

--------------------------------(Update T_Bedeh And T_Bestan In AccountingDocuments)--------------------------------
  UPDATE dbo.SummaryAccountingDocuments SET TotalDebt = Bedeh  , TotalCreditor = Bestan FROM dbo.SummaryAccountingDocuments
  INNER JOIN 
  (
  SELECT SumSanad.DocumentId, SumSanad.SumBedeh + SumCache.SumBedeh Bedeh, SumSanad.SumBestan + SumCache.SumBestan Bestan 
  FROM (
        SELECT DocumentId , SUM(Bedeh) SumBedeh, SUM(Bestan) SumBestan FROM @Document
        GROUP BY DocumentId
	   ) AS SumSanad 
  INNER JOIN 
  (
    SELECT DocumentId , SUM(Bedeh) SumBedeh, SUM(Bestan) SumBestan FROM @Cache
    GROUP BY DocumentId
  ) AS SumCache ON SumSanad.DocumentId = SumCache.DocumentId
  ) AS Total ON Total.DocumentId =  dbo.SummaryAccountingDocuments.DocumentId

COMMIT TRAN
END TRY 
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    ROLLBACK TRANSACTION;
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH




