USE [Procedure]
GO

/****** Object:  View [dbo].[vTender_OOS]    Script Date: 12.02.2016 12:47:07 ******/
DROP VIEW [dbo].[vTender_OOS]
GO

/****** Object:  View [dbo].[vTender_OOS]    Script Date: 12.02.2016 12:47:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE VIEW [dbo].[vTender_OOS]
AS
       SELECT
        ut.task_id AS [id]
       ,ut.id AS [mytenderid]
       ,ut.[guid] AS [guid]
       ,ut.purchaseId AS [PurchaseID]
       ,ust.sort_field [Status]
       ,ut.[bossId] AS [bossid]
       ,ut.ownerId AS [OwnerId]
       ,CONVERT(DATETIME, ut.insertDate) AS [InsertDate]
       ,CONVERT(DATETIME, ut.lastActivityDate) AS [lastActivityDate]
       ,ut.reportId AS [reportId]
       ,ISNULL(ut.supViser, '') AS [supViser]
       ,COALESCE(lt.subject, nt.orderName, '') AS [Name]
       ,ISNULL(nt.OrganizerName, '') AS [Organizer]
       ,ISNULL(STUFF((SELECT
                        [Procedure].dbo.strconcat(DISTINCT '|| ' + CUST.fullName)
                      FROM
                        OOS.dbo.OrganizationTable CUST WITH (NOLOCK)
                        INNER JOIN OOS.dbo.CustomerRequirementTable CRT WITH (NOLOCK) ON CUST.GUID = CRT.customer
                      WHERE
                        CRT.customerRequirementGUIDLink = lt.GUID
                     ), 1, 3, ''), '') AS [Customer]
       ,ISNULL(nt.Amount, 0) AS [GarantyAmount]
       ,ISNULL(nt.Amount_con, 0) AS [GuaranteeAmountCon]
       ,ISNULL(NPT.deliveryPlace, '') AS [DeliveryPlace]
       ,ISNULL(ctp.price, 0) AS resultSum
       ,--winner_value
        ISNULL(nt.notificationNumber, '') AS [NotificationNumber]
       ,COALESCE((SELECT
                    SUM(CRT1.maxPrice)
                  FROM
                    OOS.dbo.CustomerRequirementTable CRT1 WITH (NOLOCK)
                  WHERE
                    CRT1.customerRequirementGUIDLink = lt.GUID
                 ), lt.maxPrice, 0) AS [PurchaseStartPrice]
       ,ISNULL(nct.HoldingDate, '1970-01-01') AS [AuctionDate]
       ,ISNULL(rrt.Sort_field, 0) AS [RegionID]
       ,ISNULL(nt.StartDate, '1970-01-01') AS [DateStart]
       ,ISNULL(nt.EndDate, '1970-01-01') AS [DateFinish]
       ,0 AS [ex_DateStart]
       ,0 AS [ex_DateFinish]
       ,ISNULL(nt.href, '') AS [SourceLink]
       ,ISNULL(rctt.Sort_field, 0) AS [ContractTypeID]
       ,ISNULL(refCur.sort_field, 12000) AS [CurrencyID]
       ,ISNULL(rept.ID, 0) AS PlatformID
       ,ISNULL(crmreg.sort_field, 0) AS userregion_id
       ,ISNULL(crmmreg.sort_field, 0) AS usermregion_id
       ,COALESCE(opf.alias + ' ', '') + ISNULL(cpt.[name], '') winner_name
       ,ISNULL(ut.source, 0) AS FilterID
       FROM
        [mt].[UserTasks] ut
        LEFT JOIN [mt].[UsersTasksStatuses] ust ON ut.status = ust.id
        LEFT JOIN OOS.dbo.LotTable AS lt WITH (NOLOCK) ON lt.GUID = (SELECT TOP 1
                                                                        LotGUID
                                                                     FROM
                                                                        OOS.dbo.lotHashTable WITH (NOLOCK)
                                                                     WHERE
                                                                        Hash = ut.lotHash
                                                                     ORDER BY
                                                                        ID DESC
                                                                    )
        LEFT JOIN OOS.dbo.NotificationTable nt WITH (NOLOCK) ON nt.ID = ut.purchaseId
        LEFT JOIN Dicts.dbo.RefCurrencyTable AS refCur WITH (NOLOCK) ON refCur.GUID = nt.Currency
        LEFT JOIN OOS.dbo.NotificationPlacementTable NPT WITH (NOLOCK) ON NPT.notificationPlacementGUIDLink = nt.GUID
        LEFT JOIN OOS.dbo.NotificationCommissionTable nct WITH (NOLOCK) ON nct.GUID = nt.notificationCommission
        LEFT JOIN Dicts.dbo.RefRegionTable AS rrt WITH (NOLOCK) ON nt.OrganizerRegionGuid = rrt.GUID
        LEFT JOIN Dicts.dbo.RefContractTypeTable AS rctt WITH (NOLOCK) ON nt.ContractType = rctt.GUID
        LEFT JOIN OOS.dbo.RefEPTable rept WITH (NOLOCK) ON rept.GUID = nt.EP AND rept.GUID != '354B82CB-F6FF-4220-826F-9211C17BD3AF'
/*региоы и макро регионы*/
        LEFT JOIN [mt].[UserOrg] cuo ON cuo.usrId = ut.ownerId
        LEFT JOIN [mt].[UsersRegions] ur WITH (NOLOCK) ON ur.user_id = ut.ownerId
        LEFT JOIN [mt].[RefRegionTable] crmreg WITH (NOLOCK) ON crmreg.id = [region_id]
        LEFT JOIN [mt].[RefRegionsMacroRegion] rmr WITH (NOLOCK) ON rmr.org_guid = cuo.orgGuid AND rmr.region_id = ur.region_id
        LEFT JOIN [mt].[RefMacroRegionTable] crmmreg WITH (NOLOCK) ON crmmreg.id = rmr.maro_region_id
/*победитель*/
        LEFT JOIN [mt].[TendersParticipants] ctp WITH (NOLOCK) ON ctp.tender_id = ut.task_id AND ctp.winner = 1
        LEFT JOIN [mt].[ParticipantTable] cpt WITH (NOLOCK) ON cpt.part_id = ctp.participant_id
        LEFT JOIN [mt].[ParticipantOPF] opf WITH (NOLOCK) ON cpt.opf_id = opf.id
       WHERE
        ut.reportId = 32




GO