CREATE PROCEDURE [dbo].[Sp_UserShiftWork] @DocumentDate VARCHAR(10), @Users VARCHAR(MAX), @DocType TINYINT,  @DocNumber int, @UserId SMALLINT, @StoreCode varchar(3), @OutPutId INT OUTPUT AS 
BEGIN
	DECLARE @Date SMALLDATETIME = dbo.ShamsiToMiladi(@DocumentDate)
	DECLARE @DocumentId BIGINT 
	DECLARE @Number INT
	DECLARE @Note NVARCHAR(MAX) 
    DECLARE @Id UNIQUEIDENTIFIER
	DECLARE @Price MONEY
	DECLARE @AccountingCode Varchar(20)
	DECLARE @AccountingName NVarchar(90)
	DECLARE @AccountingUser VARCHAR(20)
	DECLARE @Description NVARCHAR(25)
	DECLARE @Name NVARCHAR(50)
	DECLARE @NoteDetail NVARCHAR(120)  
	DECLARE @IsRegister BIT = 0

	DECLARE @CacheData AS TABLE
	(
	  Id UNIQUEIDENTIFIER,
	  UserShiftWorkId UNIQUEIDENTIFIER,
	  AccountingCode VARCHAR(20),
	  AccountingName NVARCHAR(90),
	  AccountingUser VARCHAR(20),
	  DifferTreasury Money, 
	  DESCRIPTION  Nvarchar(25) ,
	  Name nvarchar(50)
	)
	
	DELETE FROM @CacheData
	INSERT INTO @CacheData(Id, UserShiftWorkId, AccountingCode, AccountingName, AccountingUser,  Name, DifferTreasury, DESCRIPTION)
	SELECT T.Id, UserShiftWorkId , AccountingCode, AccountingName ,Acc_Code AccountingUser, T.Name, DifferTreasury, T.Description FROM (
	SELECT dbo.UserShiftWorkDetail.Id, dbo.UserShiftWorkDetail.UserShiftWorkId , Acc_Code AccountingCode,Acc_Name AccountingName,Users.UserName,DocumentDate,dbo.UserShiftWork.Status,
	UserAccountingCode.AccountId,Users.Name, 
	CASE 
	  WHEN IsReadOnly = 0 And TreasuryPrice IS NOT NULL  THEN TreasuryPrice - InitialExpectationPrice
	ELSE 0 END AS DifferTreasury, Description                                                                                      
	 FROM dbo.UserShiftWorkDetail WITH (NOLOCK)
	INNER JOIN dbo.Account WITH (NOLOCK) ON AutoId = AccountId
	INNER JOIN dbo.UserShiftWork WITH (NOLOCK) ON UserShiftWork.Id = UserShiftWorkDetail.UserShiftWorkId
	INNER JOIN dbo.UserAccountingCode WITH (NOLOCK) ON UserAccountingCode.Id = UserShiftWork.UserId
	INNER JOIN [Manager].dbo.Users Users ON Users.UserName = UserAccountingCode.UserName
	WHERE IsReadOnly = 0 
	) AS T 
	INNER JOIN dbo.Account WITH (NOLOCK) ON dbo.Account.AutoId = T.AccountId
	WHERE ISNULL(T.DifferTreasury,0) <> 0 
	AND DocumentDate = @Date AND UserName in(SELECT splitdata FROM dbo.fnSplitString(@Users,','))
	AND T.Status = 0

	IF (SELECT COUNT(0) FROM @CacheData) = 0  
	BEGIN
	   SET @OutPutId = -2
	   SELECT @OutPutId  
	   RETURN 	  
    END 	

	BEGIN TRY
    BEGIN TRAN

	IF @DocType IN(0,1) 
	BEGIN
	  DECLARE @IsAuto BIT 
	  IF @DocType = 0 SET @IsAuto = 1 ELSE SET @DocType = 0
	  SET @DocumentId = (SELECT Id_Sanad FROM Heads WITH (NOLOCK) WHERE @Date = CONVERT(VARCHAR(10), Tarikh_User, 111) AND Auto = @IsAuto)
	  SET @Number = (SELECT Nu_Manual FROM Heads WHERE Id_Sanad = @DocumentId)
	  IF (@DocumentId IS Null)
	  BEGIN
		SET @Number = (SELECT ISNULL(MAX(Nu_Manual),0)+1 FROM dbo.HeadS WITH (NOLOCK))
		SET @Note = N'سند خودکار کسري و اضافي صندوق مورخ ' + @DocumentDate
		INSERT INTO Heads (Nu_Manual, Auto,Note, Tarikh_User, User_Id, Store_Code)
		VALUES(@Number, @IsAuto, @Note, @Date, @UserId,@StoreCode) 
		SET @DocumentId = SCOPE_IDENTITY()
	  END
    END
	ELSE
	BEGIN
	  SET @DocumentId = (SELECT Id_Sanad FROM Heads WITH (NOLOCK) WHERE Nu_Manual = @DocNumber)
	  IF (@DocumentId IS Null)
	  BEGIN
	    ROLLBACK TRAN
	    SET @OutPutId = -1
	    SELECT @OutPutId  
	    RETURN 	  
	  END
	  ELSE
        SET @Number = @DocNumber
	END  
    
	UPDATE dbo.UserShiftWork SET Status = 2 FROM dbo.UserShiftWork
	INNER JOIN @CacheData ON [@CacheData].UserShiftWorkId = UserShiftWork.Id

    DECLARE CursorShiftWork CURSOR FOR
	SELECT Id, AccountingCode, AccountingName, AccountingUser,  DifferTreasury, DESCRIPTION, Name FROM @CacheData
	OPEN CursorShiftWork
	FETCH NEXT FROM CursorShiftWork INTO @Id, @AccountingCode,  @AccountingName, @AccountingUser, @Price, @Description, @Name
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
      SET @IsRegister =1
	  IF ISNULL(@Description,'') <> ''
	    SET @Description = ' بابت ' + ISNULL(@Description,'')
	  ELSE
        SET @Description = ''
	  IF @Price < 0 
	  BEGIN
		SET @NoteDetail = 'کسري ' +  @AccountingName + ' کاربر '  + @Name + @Description
	    INSERT INTO Sanad(Id_Sanad, Acc_Code, Type, Bedeh, Bestan , Note, Edit_FLag, User_ID, Store_Code)
		VALUES(@DocumentId, @AccountingUser, 0, ABS(@Price), 0, @NoteDetail, 1,@UserId,@StoreCode)

	    INSERT INTO Sanad(Id_Sanad, Acc_Code, Type, Bedeh, Bestan , Note, Edit_FLag, User_ID, Store_Code)
		VALUES(@DocumentId, @AccountingCode, 1, 0 , ABS(@Price),  @NoteDetail, 1, @UserId, @StoreCode)
	  END 
	  ELSE IF @Price > 0 
      BEGIN
	    SET @NoteDetail = 'اضافي ' + @AccountingName + ' کاربر ' + @Name +  @Description
		INSERT INTO Sanad(Id_Sanad, Acc_Code, Type, Bedeh, Bestan , Note, Edit_FLag, User_ID, Store_Code)
		VALUES(@DocumentId, @AccountingCode, 0, @Price, 0, @NoteDetail, 1, @UserId, @StoreCode)

	    INSERT INTO Sanad(Id_Sanad, Acc_Code, Type, Bedeh, Bestan , Note, Edit_FLag, User_ID, Store_Code)
		VALUES(@DocumentId, @AccountingUser, 1, 0 , @Price,  @NoteDetail, 1, @UserId, @StoreCode)
      END 
	  FETCH NEXT FROM CursorShiftWork INTO @Id, @AccountingCode, @AccountingName, @AccountingUser, @Price,@Description, @Name
	END
	CLOSE CursorShiftWork
	DEALLOCATE CursorShiftWork 

    COMMIT TRAN
    SET @OutPutId = @Number
    SELECT @OutPutId

  END TRY
  BEGIN CATCH
    IF CURSOR_STATUS('global','CursorShiftWork') = 1
	BEGIN
	  CLOSE CursorShiftWork
	  DEALLOCATE CursorShiftWork
    END    
    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();
	IF @@TRANCOUNT > 0 
	ROLLBACK TRAN
	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	SET @OutPutId = 0
	SELECT @OutPutId    
  END CATCH
END
