CREATE PROC [dbo].[spGetUpdateableDrug]
    @WearhouseCode VARCHAR(10),
    @ComputerName NVARCHAR(30),
    @CodeType SMALLINT,
    @IsOrgType BIT,
    @OrgCode VARCHAR(MAX),
    @UpdateType SMALLINT,
    @JustSelect BIT
AS
IF @JustSelect = 0
BEGIN
    DECLARE @cmd NVARCHAR(MAX),
            @field NVARCHAR(MAX),
            @join NVARCHAR(MAX),
            @ins NVARCHAR(MAX);
    ---------------------------------------------------------------------------------
    IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##tmpView')
    BEGIN
        SET @cmd = N'DELETE FROM dbo.##tmpView  WHERE Computer_Name = ' + @ComputerName;
        EXEC sp_executesql @cmd;
    END;
    ELSE
        CREATE TABLE ##tmpView
        (
            K_Code VARCHAR(15),
            Name1 NVARCHAR(50),
            [Name] NVARCHAR(100),
            M_Code VARCHAR(8),
            Darou_Flag CHAR(1),
            Price MONEY,
            UpdateStatus TINYINT,
            UpdateStatusName NVARCHAR(20),
            Computer_Name NVARCHAR(30),
            Condition NVARCHAR(80),
            [Percent] REAL,
            Brand_Code VARCHAR(6),
            Org_Code VARCHAR(6),
            isReduce BIT,
            Old_Price MONEY,
            sa_Price MONEY,
            [Status] CHAR(1),
            CurPercent REAL,
            Sazman_Code VARCHAR(3),
            [Type] NVARCHAR(MAX)
        );
    ---------------------------------------------------------------------------------
    SET @ins
        = N'INSERT INTO ##tmpView ' + +N'('
          + N'K_Code,Name1,Name,M_Code,Darou_Flag,Price,UpdateStatus,UpdateStatusName,Computer_Name,Condition,[Percent],Brand_Code,Org_Code,isReduce,'
          + N'Old_Price,sa_Price,Status,CurPercent,Sazman_Code,[Type] ' + +N') ';
    ---------------------------------------------------------------------------------

    IF @CodeType = 2
        SET @join = N' join KalaId G ON G.K_Code COLLATE  Arabic_CI_AS = tmp.K_Code ';
    IF @CodeType = 1
        SET @join = N' Inner join KalaId G ON G.Brand_Code COLLATE  Arabic_CI_AS = tmp.Brand_Code ';
    IF @CodeType = 3
        SET @join = N' Inner join KalaId G ON G.Org_Code COLLATE  Arabic_CI_AS = tmp.Org_Code ';
    IF @CodeType = 0
        SET @join = N' Inner join KalaId G ON G.Meli_Code COLLATE  Arabic_CI_AS = tmp.M_code ';
    ---------------------------------------------------------------------------------
    IF @CodeType <> 4
    BEGIN
        IF @IsOrgType = 1
        BEGIN
            SET @cmd = N' G.Darou_Flag = ''6'' AND PO.Sazman_Code ' + @OrgCode;
            SET @field
                = N' , PO.Price AS sa_Price, PO.Status ,ISNULL(PO.[BimarPercent],0) CurPercent , PO.Sazman_Code';
            SET @join
                = @join
                  + N' INNER JOIN  Price_Sazman PO ON G.K_Code = PO.K_Code AND PO.Status NOT in (0 , 1 , 2 , 3 ) AND '
                  + @cmd;
        END;
        ELSE
            SET @field
                = N' , Anbar.Price_Kharid AS sa_Price, Darou_Flag AS Status , 0 as CurPercent , ''01'' as Sazman_Code ';
        SET @cmd
            = @ins
              + N'SELECT  G.K_Code , G.Name1 , tmp.Name ,tmp.M_Code , G.Darou_Flag ,tmp.Price , tmp.Update_Status , UpdateStatusName = CASE WHEN tmp.Update_Status = 1 THEN N''اعمال شود'' ELSE N''اعمال نشود'' END , tmp.Computer_Name ,'
              + N'tmp.Condition , tmp.[Percent] , tmp.Brand_Code,tmp.Org_Code , 0 , Anbar.Price_Forosh ' + @field
              + N', ''NoOrg''  ' + N'FROM  ##tmpExcel tmp  ' + @join
              + N'Inner Join Anbar  ON Anbar.K_Code     = G.K_Code ' + N'WHERE tmp.Computer_Name = ' + @ComputerName
              + N' AND Anbar.A_Code = ' + @WearhouseCode + N'';

        IF @UpdateType = 2
            SET @cmd = @cmd + N' AND Anbar.Price_Forosh <> tmp.Price AND G.Darou_Flag = ''5'' ';
        ELSE IF @UpdateType = 1
            SET @cmd = @cmd + N' AND Anbar.Price_Forosh = tmp.Price AND G.Darou_Flag <> ''6'' ';
        ELSE IF @UpdateType = 3
        BEGIN
            SET @cmd
                = @cmd
                  + N' AND ( ((PO.Status = ''6'' AND tmp.Price > PO.Price ) OR (PO.Status not in ( ''4'' , ''6'' , ''9'' ) AND tmp.Price > Anbar.Price_Forosh))'
                  + N' OR  (PO.Status in (''4'' , ''9'') AND (( PO.Price > 0 AND tmp.Price > PO.Price ) OR (PO.Price = 0 AND tmp.Price > Anbar.Price_Forosh ))) '
                  + N' OR ((PO.Status = ''6'' AND tmp.Price < PO.Price ) OR (PO.Status not in (''4'' , ''6'' , ''9'' ) AND tmp.Price < Anbar.Price_Forosh))'
                  + N' OR (PO.Status in (''4'' , ''9'') AND ( ( PO.Price > 0 AND tmp.Price < PO.Price ) OR (PO.Price = 0 AND tmp.Price < Anbar.Price_Forosh))) )'
                  + N' AND NOT( PO.Status = ''6'' AND Anbar.Price_Forosh = tmp.Price AND ISNULL(PO.BimarPercent , 0) > 0 AND PO.Price = 0 ) ';
        END;
    END;
    ELSE
    BEGIN
        ---------------------------------------------------------------------------------
        IF @UpdateType = 2
            SET @join = N' AND Anbar.Price_Forosh <> tmp.Price AND G.Darou_Flag = ''5'' ';
        ELSE IF @UpdateType = 1
            SET @join = N' AND Anbar.Price_Forosh = tmp.Price AND G.Darou_Flag <> ''6'' ';
        ELSE IF @UpdateType = 3
        BEGIN
            SET @join
                = +N' AND ( ((PO.Status = ''6'' AND tmp.Price > PO.Price ) OR (PO.Status not in ( ''4'' , ''6'' , ''9'' ) AND tmp.Price > Anbar.Price_Forosh))'
                  + N' OR  (PO.Status in (''4'' , ''9'') AND (( PO.Price > 0 AND tmp.Price > PO.Price ) OR (PO.Price = 0 AND tmp.Price > Anbar.Price_Forosh ))) '
                  + N' OR ((PO.Status = ''6'' AND tmp.Price < PO.Price ) OR (PO.Status not in (''4'' , ''6'' , ''9'' ) AND tmp.Price < Anbar.Price_Forosh))'
                  + N' OR (PO.Status in (''4'' , ''9'') AND ( ( PO.Price > 0 AND tmp.Price < PO.Price ) OR (PO.Price = 0 AND tmp.Price < Anbar.Price_Forosh))) )'
                  + N' AND NOT( PO.Status = ''6'' AND Anbar.Price_Forosh = tmp.Price AND ISNULL(PO.BimarPercent , 0) > 0 AND PO.Price = 0 ) ';
        END;
        ELSE
            SET @join = N' ';
        SET @cmd
            = @ins
              + N'SELECT g.K_Code,g.Name1,tmp.Name,EIC.Code,g.Darou_Flag,tmp.Price,tmp.Update_Status,CASE WHEN Update_Status = 1 THEN N''اعمال شود'' ELSE N''اعمال نشود'' END,'
              + N'tmp.Computer_Name,tmp.Condition,tmp.[Percent],tmp.Brand_Code,tmp.Org_Code,0,Anbar.Price_Forosh,PO.Price,PO.Status, '
              + N'ISNULL(PO.[BimarPercent], 0) BimarPercent,InsuranceCode,''Exclusive''' + N'FROM ##tmpExcel tmp '
              + N'JOIN dbo.ExclusiveInsuranceCode EIC ON EIC.Code = tmp.ExclusiveCode AND EIC.InsuranceCode '
              + @OrgCode + N'JOIN dbo.KalaId g ON g.K_Code = EIC.GoodsCode '
              + N'JOIN Price_Sazman PO ON g.K_Code = PO.K_Code AND PO.Status NOT IN ( 0, 1, 2, 3 ) AND g.Darou_Flag = ''6'' AND PO.Sazman_Code = EIC.InsuranceCode '
              + N'JOIN dbo.Anbar ON dbo.Anbar.K_Code = g.K_Code ' + N'WHERE Computer_Name = ' + @ComputerName
              + N'AND Anbar.A_Code = ' + @WearhouseCode + @join;
        ---------------------------------------------------------------------------------
        SET @cmd
            = @cmd + @ins
              + N'SELECT g.K_Code,g.Name1,tmp.Name,g.Meli_Code,g.Darou_Flag,tmp.Price,tmp.Update_Status,CASE WHEN Update_Status = 1 THEN N''اعمال شود'' ELSE N''اعمال نشود'' END, '
              + N'tmp.Computer_Name,tmp.Condition,tmp.[PERCENT],tmp.Brand_Code,tmp.Org_Code,0,Anbar.Price_Forosh,PO.Price,'
              + N'PO.Status,ISNULL(PO.[BimarPercent], 0),PO.Sazman_Code,''MCode'' [Type] ' + N'FROM ##tmpExcel tmp '
              + N'JOIN dbo.KalaId g ON g.Meli_Code = tmp.M_Code '
              + N'JOIN Price_Sazman PO ON PO.K_Code = g.K_Code AND PO.Status NOT IN ( 0, 1, 2, 3 ) AND g.Darou_Flag = ''6'' AND PO.Sazman_Code '
              + @OrgCode + N'JOIN dbo.Anbar ON dbo.Anbar.K_Code = g.K_Code ' + N'WHERE Computer_Name = '
              + @ComputerName + N'AND Anbar.A_Code = ' + @WearhouseCode
              + N'AND g.K_Code NOT IN  ( SELECT K_Code COLLATE Arabic_CI_AS FROM ##tmpView ) ' + @join;
    ---------------------------------------------------------------------------------
    END;
    --PRINT '--->' + @cmd;
    EXEC sp_executesql @cmd;
--------------------------------------------------------------------------------
END;
UPDATE ##tmpView
SET isReduce = CASE
                   WHEN
                   (
                       Price < Old_Price
                       AND
                       (
                           Price < sa_Price
                           OR
                           (
                               sa_Price > 0
                               OR Status IN ( 4, 5, 9 )
                           )
                       )
                   ) THEN
                       1
                   ELSE
                       0
               END;

SELECT [Name],
       M_Code,
       Price,
       Name1,
       K_Code,
       Old_Price,
       Darou_Flag,
       sa_Price,
       Status,
       UpdateStatus,
       UpdateStatusName,
       Condition,
       Computer_Name,
       [Percent],
       CurPercent,
       Status_Name = CASE
                         WHEN Darou_Flag <> 6 THEN
                             dbo.Find_Status(Darou_Flag)
                         ELSE
                             dbo.Find_Status(Status)
                     END,
       Brand_Code,
       Org_Code,
       Sazman_Code,
       isReduce
FROM ##tmpView
ORDER BY CASE
             WHEN @CodeType = 1 THEN
                 Brand_Code
             WHEN @CodeType = 2 THEN
                 K_Code
             WHEN @CodeType = 3 THEN
                 Org_Code
             ELSE
                 M_Code
         END;