1:  ALTER View [dbo].[GetInvestUaList]

   2:  with schemabinding

   3:  as

   4:  select

   5:    t.id,

   6:    t.cpv,

   7:    tenderID,

   8:    title,

   9:    isnull(s.Name, s3.Name) StateText,

  10:    t.status,

  11:    description,

  12:    t.value_amount,

  13:    value_currency,

  14:    tenderPeriod_startDate,

  15:    tenderPeriod_endDate,

  16:    auction_date,

  17:    auctionPeriod_startDate,

  18:    procuringEntity_name procuringEntity,

  19:    procuringEntity_id,

  20:    t.dateModified,

  21:    dgfID,

  22:    procurementMethodType,

  23:    tenderAttempts,

  24:    cdb,

  25:    list_mark,

  26:    minimalStep_amount,

  27:    CASE

  28:      WHEN l.id IS NULL THEN 0

  29:      ELSE 1

  30:    END in_list,

  31:    CASE

  32:      WHEN pl.id IS NULL THEN 0

  33:      ELSE 1

  34:    END in_popular_list,

  35:    case

  36:      when (

  37:        (

  38:          CPV = '06000000-2'

  39:          or left(CPV, 2) = '04'

  40:        )

  41:        and (j_content not like '%PA01-7%')

  42:      ) then 1

  43:      else 0

  44:    end as RealEstateSale,

  45:    case

  46:      when (left(CPV, 2) = '06') then 1

  47:      else 0

  48:    end as LandParcel,

  49:    case

  50:      when (

  51:        (

  52:          procurementMethodType = 'sellout.english'

  53:          or procurementMethodType = 'sellout.insider'

  54:        )

  55:        and cdb = '2'

  56:      ) then 1

  57:      else 0

  58:    end as Privatization,

  59:    case

  60:      when left(CPV, 2) = '07' then 1

  61:      else 0

  62:    end as Receivables,

  63:    case

  64:      when list_mark = 'bankrupt' then 1

  65:      else 0

  66:    end as BankruptsProperty,

  67:    case

  68:      when (

  69:        (

  70:          j_content like '%QB29-3%'

  71:          or j_content like '%PA01-7%'

  72:        )

  73:        and status not in (

  74:          'cancelled',

  75:          'complete',

  76:          'unsuccessful',

  77:          'delete',

  78:          'deleted'

  79:        )

  80:      ) then 1

  81:      else 0

  82:    end as Leasehold,

  83:    case

  84:      when (list_mark = 'nadra') then 1

  85:      else 0

  86:    end as SubsoilUse,

  87:    case

  88:      when (

  89:        CPV ! = '34621100-7'

  90:        and left(CPV, 2) = '34'

  91:      ) then 1

  92:      else 0

  93:    end as Vehicles,

  94:    case

  95:      when (

  96:        CPV LIKE('1491%')

  97:        or CPV LIKE('1463%')

  98:      ) then 1

  99:      else 0

 100:    end as ScrapMetal,

 101:    case

 102:      when (

 103:        (

 104:          CPV LIKE('1491%')

 105:          or CPV LIKE('1463%')

 106:        )

 107:        and list_mark = 'timber'

 108:      ) then 1

 109:      else 0

 110:    end as Wood,

 111:    case

 112:      when (j_content like '%DA04-3%') then 1

 113:      else 0

 114:    end as CommercialTrades,

 115:    case

 116:      when (

 117:        (j_content like '%DA04-3%')

 118:        and list_mark = 'railwayCargo'

 119:      ) then 1

 120:      else 0

 121:    end as RailCarHire,

 122:    case

 123:      when (

 124:        (

 125:          procuringEntity_id = '43333684'

 126:          or procuringEntity_id = '43332555'

 127:          or procuringEntity_id = '43350888'

 128:          or procuringEntity_id = '43348711'

 129:          or procuringEntity_id = '43350935'

 130:          or procuringEntity_id = '43334913'

 131:          or procuringEntity_id = '43337207'

 132:          or procuringEntity_id = '43337359'

 133:          or procuringEntity_id = '43333459'

 134:          or procuringEntity_id = '43332675'

 135:          or procuringEntity_id = '43350542'

 136:          or procuringEntity_id = '43350097'

 137:          or procuringEntity_id = '43332958'

 138:          or procuringEntity_id = '43336287'

 139:          or procuringEntity_id = '43335608'

 140:        )

 141:        and (j_content like '%DA04-3%')

 142:      ) then 1

 143:      else 0

 144:    end as Customs,

 145:    case

 146:      when (

 147:        (procuringEntity_id = '37472392')

 148:        and(j_content like '%DA04-3%')

 149:      ) then 1

 150:      else 0

 151:    end as StateReserve,

 152:    case

 153:      when (

 154:        (j_content like '%DA04-3%')

 155:        and list_mark = 'legitimatePropertyLease'

 156:      ) then 1

 157:      else 0

 158:    end as LegitimatePropertyLease,

 159:    case

 160:      when (

 161:        (j_content like '%DA04-3%')

 162:        and list_mark = 'dgf'

 163:      ) then 1

 164:      else 0

 165:    end as Dgf

 166:  FROM

 167:    (

 168:      select

 169:         tt.[id]

 170:        ,tt.[tenderID]

 171:        ,tt.[token]

 172:        ,tt.[id_member]

 173:        ,tt.[id_user]

 174:        ,tt.[dateModified]

 175:        ,tt.[title]

 176:        ,tt.[status]

 177:        ,tt.[description]

 178:        ,tt.[procurementMethodType]

 179:        ,tt.[value_amount]

 180:        ,tt.[value_currency]

 181:        ,tt.[tenderPeriod_startDate]

 182:        ,tt.[tenderPeriod_endDate]

 183:        ,tt.[procuringEntity_name]

 184:        ,tt.[procuringEntity_id]

 185:        ,tt.[procuringEntity_address_region]

 186:        ,tt.[j_content]

 187:        ,tt.[CPV]

 188:        ,tt.[public_id]

 189:        ,tt.[to_handle]

 190:        ,tt.[auctionPeriod_startDate]

 191:        ,tt.[public_date]

 192:        ,tt.[is_test]

 193:        ,tt.[id_opr]

 194:        ,tt.[guarantee_amount]

 195:        ,tt.[dgfID]

 196:        ,tt.[regions]

 197:        ,tt.[auction_date]

 198:        ,tt.[can_public]

 199:        ,tt.[place_fee]

 200:        ,tt.[cdb]

 201:        ,tt.[rentSquare]

 202:        ,tt.[interest]

 203:        ,tt.[merchandisingObject]

 204:        ,tt.[tenderAttempts]

 205:        ,tt.[transfer]

 206:        ,tt.[contractID]

 207:        ,tt.[list_mark]

 208:        ,tt.[minimalStep_amount]

 209:        ,tt.[image]

 210:        ,tt.[additional_documentation]

 211:        ,tt.[adminDescription]

 212:      from

 213:        dbo.tender tt 

 214:        LEFT JOIN dbo.member m on m.id = tt.id_member

 215:      where

 216:         tt.[status] = 'active_tendering' 

 217:    ) AS t

 218:    LEFT JOIN dbo.dics s on s.id_object = 'tender' and t.status = s.id

 219:    LEFT JOIN dbo.TenderInList l ON t.id = l.id_tender

 220:    LEFT JOIN dbo.TenderInPopularList pl ON t.id = pl.id_tender

 221:    LEFT JOIN dbo.dics s3 on s3.id_object = 'tender_cdb3' and t.status = s3.id

 222:  GO