CREATE PROCEDURE [dbo].[Cash_GetMultiplePrescriptionByUserId] @UserId SMALLINT
AS
DECLARE @SalesNumber VARCHAR(MAX) = '',
        @SalesId VARCHAR(MAX) = '',
        @RetSalesNubmer VARCHAR(MAX) = '',
        @RetSalesId VARCHAR(MAX) = '';

SELECT @RetSalesNubmer = @RetSalesNubmer + Nu_Bargasht_Forosh + ', ',
       @RetSalesId = @RetSalesId + CAST(Id_Havaleh AS VARCHAR(20)) + ' , '
FROM dbo.TmpDrugH
WHERE Kind_Flag = 2
      AND kind_Save IN ( 2, 3 )
      AND ISNULL(Nu_Bargasht_Forosh, 0) <> 0
      AND MultipleFactorUserId = @UserId;

SELECT @SalesNumber = @SalesNumber + ISNULL(Nu_Forosh, 0) + ', ',
       @SalesId = @SalesId + CAST(Id_Havaleh AS VARCHAR(20)) + ', '
FROM dbo.TmpDrugH
WHERE Kind_Flag = 1
      AND kind_Save IN ( 2, 3 )
      AND ISNULL(Nu_Forosh, 0) <> 0
      AND MultipleFactorUserId = @UserId;


SET @SalesNumber = CASE
                       WHEN LEN(@SalesNumber) > 0 THEN
                           LEFT(@SalesNumber, LEN(@SalesNumber) - 1)
                       ELSE
                           ''
                   END;

SET @RetSalesNubmer = CASE
                          WHEN LEN(@RetSalesNubmer) > 0 THEN
                              LEFT(@RetSalesNubmer, LEN(@RetSalesNubmer) - 1)
                          ELSE
                              ''
                      END;
SET @SalesId = CASE
                   WHEN LEN(@SalesId) > 0 THEN
                       ISNULL(LEFT(@SalesId, LEN(@SalesId) - 1), '')
                   ELSE
                       ''
               END;
SET @RetSalesId = CASE
                      WHEN LEN(@RetSalesId) > 0 THEN
                          ISNULL(LEFT(@RetSalesId, LEN(@RetSalesId) - 1), '')
                      ELSE
                          ''
                  END;

SELECT @SalesNumber SalesNumberList,
       @SalesId SalesIdList,
       @RetSalesNubmer RetSalesNumerList,
       @RetSalesId RetSalesIdList,
       TotalSalesAmount = ISNULL(SUM(   CASE
                                            WHEN Kind_Flag = 1 THEN
                                                TotPriceDaryafti
                                        END
                                    ),
                                 0
                                ),
       TotalRetSalesAmount = ISNULL(SUM(   CASE
                                               WHEN Kind_Flag = 2 THEN
                                                   TotPriceDaryafti
                                           END
                                       ),
                                    0
                                   ),
       SalesPrescriptionCount = COUNT(   CASE
                                             WHEN Kind_Flag = 1 THEN
                                                 Id_Havaleh
                                         END
                                     ),
       RetSalesPrescriptionCount = COUNT(   CASE
                                                WHEN Kind_Flag = 2 THEN
                                                    Id_Havaleh
                                            END
                                        )
FROM dbo.TmpDrugH
WHERE kind_Save IN ( 2, 3 )
      AND MultipleFactorUserId = @UserId;