CREATE 
  PROC [dbo].[UpdateDrugPrice]
    @ComputerName NVARCHAR(MAX) ,
    @WearhouseCode VARCHAR(3) ,
    @IsOnlyInsurance BIT ,
    @IsChangeable 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
                    INNER  JOIN Anbar wh ON wh.K_Code = good.K_Code
            WHERE   xls.Computer_Name = @ComputerName
                    AND xls.Update_Status = 1
                    AND xls.Price <> wh.Price_Forosh
                    AND wh.A_Code = @WearhouseCode; 
            PRINT '@IsOnlyInsurance';
        END;
    ELSE
        BEGIN
            IF @IsChangeable = 1
                BEGIN
                    UPDATE  op
                    SET     Status = CASE WHEN op.Status = 6
                                               AND op.Price < wh.Price_Forosh
                                               AND wh.Price_Forosh < xls.Price
                                               AND ISNULL(op.BimarPercent, 0) = 0
                                          THEN 5
                                          WHEN op.Status = 5
                                               AND wh.Price_Forosh > xls.Price
                                               AND ISNULL(op.BimarPercent, 0) = 0
                                               AND op.Price = 0 THEN 6
                                          ELSE op.Status
                                     END ,
                            op.Price = CASE WHEN op.Price < wh.Price_Forosh
                                                 AND wh.Price_Forosh < xls.Price
                                            THEN 0
                                            WHEN ( op.Price < wh.Price_Forosh
                                                   AND op.Price > 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
                                       END
                    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 wh.A_Code = @WearhouseCode
                            INNER JOIN ##tmpView xls ON xls.K_Code = good.K_Code
                                                        AND op.Sazman_Code = xls.Sazman_Code
                    WHERE   xls.Update_Status = 1;
                    
                    PRINT '@IsChangeable';
                END;
            ELSE
                BEGIN

-------------------------------  STEP 1 , 2 
                    UPDATE  wh
                    SET     Price_Forosh = xls.Price
                    FROM    ##tmpView xls
                            INNER JOIN KalaId good ON good.K_Code = xls.K_Code
                            INNER JOIN Anbar wh ON wh.K_Code = good.K_Code
                                                   AND wh.A_Code = @WearhouseCode
                            INNER JOIN Price_Sazman OP ON OP.K_Code = good.K_Code
                                                          AND OP.Sazman_Code = xls.Sazman_Code
                    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.Update_Status = 1
                            AND xls.Computer_Name = @ComputerName;
--------------------------------------STEP   4 , 5
                    UPDATE  OP
                    SET     Status = CASE WHEN OP.Status = 6
                                               AND ISNULL(OP.BimarPercent, 0) = 0
                                               AND ( ( OP.Price < wh.Price_Forosh
                                                       AND wh.Price_Forosh < xls.Price
                                                     )
                                                     OR ( OP.Price > 0
                                                          AND OP.Price < wh.Price_Forosh
                                                          AND wh.Price_Forosh = xls.Price
                                                        )
                                                     OR ( OP.Price > xls.Price
                                                          AND OP.Price < wh.Price_Forosh
                                                        )
                                                   ) THEN 5
                                          ELSE OP.Status
                                     END ,
                            Price = CASE WHEN ( OP.Price < wh.Price_Forosh
                                                AND wh.Price_Forosh < xls.Price
                                              )
                                              OR ( OP.Price > 0
                                                   AND OP.Price < wh.Price_Forosh
                                                   AND wh.Price_Forosh = xls.Price
                                                 )
                                              OR ( OP.Price > xls.Price
                                                   AND OP.Price < wh.Price_Forosh
                                                 ) THEN 0
                                         ELSE xls.Price
                                    END
                    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 wh.A_Code = @WearhouseCode
                            INNER JOIN ##tmpView xls ON xls.K_Code = wh.K_Code
                    WHERE   xls.Update_Status = 1
                            AND ( ( OP.Price < wh.Price_Forosh
                                    AND OP.Price > xls.Price
                                  )
                                  OR ( OP.Price < xls.Price
                                       AND wh.Price_Forosh > xls.Price
                                     )
                                  OR ( OP.Price < wh.Price_Forosh
                                       AND wh.Price_Forosh < xls.Price
                                     )
                                  OR ( OP.Price > 0
                                       AND OP.Price < wh.Price_Forosh
                                       AND wh.Price_Forosh = xls.Price
                                     )
                                )
                            AND xls.Computer_Name = @ComputerName
                            AND OP.Status IN ( 6, 4, 9 )
                            AND OP.Sazman_Code = xls.Sazman_Code;
--------------------------------------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
                            INNER JOIN Price_Sazman OP ON OP.K_Code = wh.K_Code
                                                          AND OP.Sazman_Code = xls.Sazman_Code
                    WHERE   wh.Price_Forosh <> xls.Price
                            AND OP.Price = 0
                            AND xls.Update_Status = 1
                            AND OP.Status IN ( 5, 4, 9 )
                            AND wh.A_Code = @WearhouseCode
                            AND xls.Computer_Name = @ComputerName;
                END;
        END; 
