CREATE PROC [dbo].[UpdateDrugPrice]
    @ComputerName NVARCHAR(MAX),
    @WearhouseCode VARCHAR(3),
    @IsOnlyInsurance BIT,
    @IsChangeable BIT,
    @UpdateAllWarehouse BIT
AS
IF @IsOnlyInsurance = 1
BEGIN
    UPDATE wh
    SET Price_Forosh = xls.Price
    FROM ##tmpView xls
        INNER JOIN KalaId good
            ON good.K_Code = xls.K_Code COLLATE Arabic_CI_AS
        INNER JOIN Anbar wh
            ON wh.K_Code = good.K_Code
    WHERE xls.Computer_Name = @ComputerName
          AND xls.UpdateStatus = 1
          AND xls.Price <> wh.Price_Forosh
          AND
          (
              (
                  @UpdateAllWarehouse = 0
                  AND wh.A_Code = @WearhouseCode
              )
              OR @UpdateAllWarehouse = 1
          );
    PRINT '@IsOnlyInsurance';
END;
ELSE
BEGIN
    IF @IsChangeable = 1
    BEGIN
        UPDATE op
        SET Status = CASE
                         WHEN op.Status = 5
                              AND wh.Price_Forosh <> xls.Price THEN
                             6
                         ELSE
                             op.Status
                     END,
            op.Price = ISNULL(   CASE
                                     WHEN ISNULL(op.Price, 0) < wh.Price_Forosh
                                          AND wh.Price_Forosh < xls.Price THEN
                                         wh.Price_Forosh
                                     WHEN (
                                              op.Price < wh.Price_Forosh
                                              AND ISNULL(op.Price, 0) > xls.Price
                                          )
                                          OR
                                          (
                                              op.Price < xls.Price
                                              AND wh.Price_Forosh > xls.Price
                                          )
                                          OR
                                          (
                                              op.Status = 5
                                              AND wh.Price_Forosh > xls.Price
                                          ) THEN
                                         xls.Price
                                     WHEN op.Price = wh.Price_Forosh
                                          AND op.Status IN ( 6, 9, 4 )
                                          AND xls.Price > op.Price THEN
                                         op.Price
                                     WHEN op.Price = wh.Price_Forosh
                                          AND op.Status IN ( 6, 9, 4 )
                                          AND xls.Price < op.Price THEN
                                         xls.Price
                                     WHEN xls.Price = wh.Price_Forosh
                                          AND op.Status IN ( 6, 9, 4 )
                                          AND op.Price < xls.Price THEN
                                         xls.Price
                                     ELSE
                                         op.Price
                                 END,
                                 0
                             )
        FROM dbo.Price_Sazman op
            INNER JOIN KalaId good
                ON good.K_Code = op.K_Code
            INNER JOIN Anbar wh
                ON wh.K_Code = good.K_Code
                   AND
                   (
                       (
                           @UpdateAllWarehouse = 0
                           AND wh.A_Code = @WearhouseCode
                       )
                       OR @UpdateAllWarehouse = 1
                   )
            INNER JOIN ##tmpView xls
                ON xls.K_Code = good.K_Code COLLATE Arabic_CI_AS
                   AND op.Sazman_Code = xls.Sazman_Code COLLATE Arabic_CI_AS
        WHERE xls.Computer_Name = @ComputerName
              AND xls.UpdateStatus = 1;
        PRINT '@IsChangeable';
    END;
    ELSE
    BEGIN

        -------------------------------  STEP 1 , 2 
        --UPDATE wh
        --SET Price_Forosh = -9999 --xls.Price
        --FROM ##tmpView xls
        --    INNER JOIN KalaId good
        --        ON good.K_Code = xls.K_Code COLLATE Arabic_CI_AS
        --    INNER JOIN Anbar wh
        --        ON wh.K_Code = good.K_Code
        --           AND
        --           (
        --               (
        --                   @UpdateAllWarehouse = 0
        --                   AND wh.A_Code = @WearhouseCode
        --               )
        --               OR @UpdateAllWarehouse = 1
        --           )
        --    INNER JOIN Price_Sazman OP
        --        ON OP.K_Code = good.K_Code
        --           AND OP.Sazman_Code = xls.Sazman_Code COLLATE Arabic_CI_AS
        --WHERE good.Darou_Flag = 6
        --      AND OP.Status IN ( 5, 4, 9 )
        --      AND
        --      (
        --          xls.Price < wh.Price_Forosh
        --          OR xls.Price > wh.Price_Forosh
        --      )
        --      --AND OP.Price = 0
        --      AND xls.UpdateStatus = 1
        --      AND xls.Computer_Name = @ComputerName;
        --------------------------------------STEP   4 , 5
        UPDATE OP
        SET Price = ISNULL(   CASE
                                  WHEN (
                                           OP.Price < wh.Price_Forosh
                                           AND wh.Price_Forosh < xls.Price
                                       )
                                       OR
                                       (
                                           OP.Price > xls.Price
                                           AND OP.Price < wh.Price_Forosh
                                       ) THEN
                                      xls.Price --OP.Price 
                                  WHEN wh.Price_Forosh = OP.Price THEN
                                      xls.Price
                                  ELSE
                                      xls.Price
                              END,
                              0
                          )
        FROM dbo.Price_Sazman OP
            INNER JOIN KalaId good
                ON good.K_Code = OP.K_Code
            INNER JOIN dbo.Anbar wh
                ON wh.K_Code = good.K_Code
                   AND
                   (
                       (
                           @UpdateAllWarehouse = 0
                           AND wh.A_Code = @WearhouseCode
                       )
                       OR @UpdateAllWarehouse = 1
                   )
            INNER JOIN ##tmpView xls
                ON xls.K_Code = wh.K_Code COLLATE Arabic_CI_AS
        WHERE xls.UpdateStatus = 1
              AND xls.Computer_Name = @ComputerName
              AND OP.Status IN ( 6, 4, 9 )
              AND OP.Sazman_Code = xls.Sazman_Code COLLATE Arabic_CI_AS;
        --------------------------------------STEP  3
        UPDATE wh
        SET Price_Forosh = xls.Price
        FROM Anbar wh
            INNER JOIN KalaId good
                ON good.K_Code = wh.K_Code
            INNER JOIN ##tmpView xls
                ON xls.K_Code = wh.K_Code COLLATE Arabic_CI_AS
            INNER JOIN Price_Sazman OP
                ON OP.K_Code = wh.K_Code
                   AND OP.Sazman_Code = xls.Sazman_Code COLLATE Arabic_CI_AS
        WHERE wh.Price_Forosh <> xls.Price
              AND xls.UpdateStatus = 1
              AND OP.Status IN ( 5, 4, 9, 6 )
              AND
              (
                  (
                      @UpdateAllWarehouse = 0
                      AND wh.A_Code = @WearhouseCode
                  )
                  OR @UpdateAllWarehouse = 1
              )
              AND xls.Computer_Name = @ComputerName;
    END;
END;