query = '''
drop table ##pt
select distinct inn into ##pt from OOS.dbo.ParticipantTable as partT with(nolock)
where cast([FirstInsertDate] as date)>'2015-01-01' and partT.inn in {0}

select distinct
nt.notificationNumber 'id_procedure',
lt.ordinalNumber 'id_lot',
partT.inn 'inn_ich',
isnull(d.prsAdmitted,'0') 'admitted',
isnull(w.prsWinner,'0') 'is_winner',
nt.[EndDate] 'end_date',
isnull(dct.[СonclusionDate],dct.[PublishDate]) 'date_contract',
ct.regnum 'num_contract',
pa.inn 'inn_postav'

from ##pt t
inner join OOS.dbo.ParticipantTable as partT with(nolock) on t.inn=partT.inn
inner join OOS.dbo.applicationParticipantLinkTable as apppartlT with(nolock) on apppartlT.participantLink=partT.GUID
INNER JOIN OOS.dbo.applicationTable AS appT WITH (NOLOCK) ON appT.GUID=apppartlT.applicationParticipantGUIDLink
INNER JOIN OOS.dbo.protocolLotTable AS plt WITH (NOLOCK) ON plt.GUID = appT.applicationGUIDLink
inner join OOS.dbo.ProtocolTable  protT with(nolock) on protT.GUID = plt.protocolLotGUIDLink and ((prs_cancel IS NULL OR prs_cancel = 0) and (protT.actual is null or protT.actual=1))
inner join [OOS].[dbo].[NotificationTable] nt WITH(NOLOCK) on protT.notificationNumber=nt.notificationNumber
cross apply (select top 1 * from OOS.dbo.ProtocolTable  prt with(nolock) where (prs_cancel IS NULL OR prs_cancel = 0) and (protT.actual is null or protT.actual=1) and prt.notificationNumber=nt.notificationNumber order by protT.[protocolNumber]) prtt
inner join [OOS].[dbo].[LotTable] lt WITH(NOLOCK) on lt.lotGUIDLink = nt.guid and isnull([lotNumber],1)=lt.ordinalNumber and prtt.id=protT.id
outer apply (SELECT DISTINCT dense_rank () OVER (partition by protT.notificationNumber,plt.lotNumber order by protT.[protocolNumber] desc) id,
[journalNumber]
,protT.notificationNumber
,plt.[lotNumber]
,protT.[protocolNumber]
,appt.PrsAdmitted
from OOS.dbo.ProtocolTable  protT with(nolock)
INNER JOIN OOS.dbo.protocolLotTable AS plt WITH (NOLOCK) ON protT.GUID = plt.protocolLotGUIDLink
INNER JOIN OOS.dbo.applicationTable AS app WITH (NOLOCK) ON plt.GUID = app.applicationGUIDLink
where (prs_cancel IS NULL OR prs_cancel = 0) and (protT.actual is null or protT.actual=1) and protT.notificationNumber=nt.notificationNumber and isnull(plt.[lotNumber],1)=lt.ordinalNumber and app.[journalNumber]=appT.journalNumber) d
outer apply (SELECT DISTINCT dense_rank () OVER (partition by protT.notificationNumber,plt.lotNumber order by protT.[protocolNumber] desc) id,
 [journalNumber]
,protT.notificationNumber
                    ,plt.[lotNumber]
,protT.[protocolNumber]
,appt.PrsWinner
from OOS.dbo.ProtocolTable  protT with(nolock)
INNER JOIN OOS.dbo.protocolLotTable AS plt WITH (NOLOCK) ON protT.GUID = plt.protocolLotGUIDLink
INNER JOIN OOS.dbo.applicationTable AS app WITH (NOLOCK) ON plt.GUID = app.applicationGUIDLink
where (prs_cancel IS NULL OR prs_cancel = 0) and (protT.actual is null or protT.actual=1) and app.PrsWinner=1 and protT.notificationNumber=nt.notificationNumber and isnull(plt.[lotNumber],1)=lt.ordinalNumber and app.journalNumber=appT.journalNumber) w
left join [OOS].[dbo].[ContractTable] ct with(nolock) on lt.id=ct.lotid
left join dicts.dbo.ContractTable dct WITH(NOLOCK) on dct.guid=ct.guid
left join [OOS].[dbo].[applicationParticipantLinkTable] apa with(nolock) on apa.[applicationParticipantGUIDLink]=ct.guid
left join [OOS].[dbo].[ParticipantTable] pa with(nolock) on pa.guid = apa.[participantLink]
where (d.id=1 or d.id is null) and (w.id=1 or w.id is null) and partT.inn in {0}

union

select
null 'id_procedure',
null 'id_lot',
partT.inn 'inn_participant',
null 'admitted',
'1' as 'is_winner',
null 'end_date',
isnull(dct.[СonclusionDate],dct.[PublishDate]) 'date_contract',
ct.regnum 'regnum_contract',
partT.inn 'inn_provider'
from ##pt t
inner join OOS.dbo.ParticipantTable as partT with(nolock) on t.inn=partT.inn
inner join [OOS].[dbo].[applicationParticipantLinkTable] apa with(nolock) on partT.guid = apa.[participantLink]
inner join oos.dbo.ContractTable ct WITH(NOLOCK) on apa.[applicationParticipantGUIDLink]=ct.guid
inner join dicts.dbo.ContractTable dct WITH(NOLOCK) on dct.guid=ct.guid and dct.[ContractTypeID] in (9,40)
where partT.inn in {0}
'''.format(inn)