import pymysql.cursors
import pandas as pd
import pymssql

connection = pymssql.connect(server='virt135.aetp.nn',
                             user='AETP\\popova.a',
                             password='WiW7RWfbQW',
                             charset='cp1251')


cursor = connection.cursor()
query = '''
drop table ##pt
select distinct top 2 inn into ##pt from OOS.dbo.ParticipantTable as partT with(nolock) where cast([FirstInsertDate] as date)>'2015-01-01'

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)
order by nt.notificationNumber
'''
data = pd.read_sql(query,connection) #filter(lambda x: x in printable, query),conn)
cursor.close()
data.head()