IF NOT EXISTS SELECT NULL FROM sys objects WHERE object_id OBJECT_ID d

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LDUsedScripts]') AND type in (N'U'))
CREATE TABLE DBO.LDUsedScripts (id INT identity(1,1) , name nvarchar(1024),description nvarchar(4000),userlogin varchar(128),ExecDate datetime)
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GRK_REP_REESTRINERC') AND type in (N'P', N'PC'))
EXEC ('CREATE PROCEDURE GRK_REP_REESTRINERC AS RETURN')
GO
--Процедура для отчета "Реестр входящей корреспонденции"
ALTER PROCEDURE [dbo].[GRK_REP_REESTRINERC] (@pDepartmentID INT
,@pUserID INT)
AS
BEGIN
DECLARE @tmpTableResult TABLE (ERCID INT
,DocN VARCHAR(128)
,RegDate VARCHAR(10)
,PartnerName VARCHAR(512)
,AuthorName VARCHAR(512)
,OrganizationName VARCHAR(512)
,NOutDoc VARCHAR(128)
,NOutDocDate VARCHAR(10)
,StatusDoc VARCHAR(128));
DECLARE @tmpERCID INT
,@tmpMainMailID INT
,@tmpFirstMailID INT
,@tmpDeliveryTypeID INT
,@tmpNum INT
,@tmpReestrType INT
,@tmpMailID INT
,@ReturnParm INT
,@tmpDelTypeID INT
DECLARE @tmpTableDocID TABLE (ID INT, JournalID INT, RegDate DATETIME, DocN NVARCHAR(2000))
-- Ускорение работы
DECLARE @users TABLE (ID INT PRIMARY KEY); -- сотрудники заданного департамента
INSERT @users SELECT ID FROM dbo.LDUSER WHERE DepartmentID = @pDepartmentID;
DECLARE @journals TABLE (ID INT PRIMARY KEY); -- журналы, на которые юзер имеет права
INSERT @journals SELECT ID FROM dbo.GRK_REP_VALIDJOURNALLIST(@pUserID, '2006,2007');
DECLARE @docsInReestr TABLE (ID INT PRIMARY KEY); -- документы в реестре заданного департамента
INSERT @docsInReestr SELECT ParentDocID FROM dbo.GRK_INREESTR
WHERE DepartmentID = @pDepartmentID;
/* Временное ограничение на даты создания сообщений */
DECLARE @minDate DATETIME;
SET @minDate = CONVERT(DATETIME, '22.09.2014', 104);
INSERT INTO @tmpTableDocID (ID, JournalID, RegDate, DocN)
SELECT doc.ID, doc.JournalID, doc.RegDate, doc.DocN
FROM dbo.LDERC doc
INNER JOIN dbo.GRK_COMMONFIELDS cf ON cf.ID = doc.ID
WHERE doc.StateID NOT IN (5,6)
AND doc.JournalID IN (SELECT ID FROM @journals) --
AND YEAR(doc.RegDate) = YEAR(GETDATE()) -- Док-ты текущего года
AND doc.ID NOT IN (SELECT ID FROM @docsInReestr) -- отсутствующие в реестре заданного департамента
AND ((cf.MainMailID IS NOT NULL -- Имеющие главное сообщение
AND EXISTS (SELECT NULL FROM dbo.LDMAIL m -- и имеются неотозванные сообщения, где
WHERE m.ERCID = doc.ID -- главный исполнитель или один из соисполнителей из заданного департамента
AND (m.ID = cf.MainMailID OR (m.ParentID = cf.MainMailID AND m.MailStateID <> 5 /*AND m.RedirectFlag IS NULL*/)) -- и сообщения активны и не перенаправлены
AND m.ReceiverID IN (SELECT ID FROM @users))
AND EXISTS (SELECT NULL FROM dbo.LDMAIL m
WHERE m.ID = cf.MainMailID
AND m.CreatorID = @pUserID -- и отправитель главного сообщения - текущий юзер
AND m.CreateDateTime > @minDate))
OR (cf.MainMailID IS NULL -- или не имеющие главного сообщения
AND EXISTS (SELECT NULL FROM dbo.LDMAIL m
WHERE m.ERCID = doc.ID
AND m.MailStateID NOT IN (1,5,11) -- и имеющие неотозванные и не проектные сообщения
AND m.ReceiverID IN (SELECT ID FROM @users) -- на соисполнителей из заданного департамента
AND m.CreatorID = @pUserID -- и отправитель которых - текущий юзер
AND m.CreateDateTime > @minDate)))
INSERT INTO @tmpTableResult (ERCID, DocN, RegDate, PartnerName, AuthorName, OrganizationName, NOutDoc, NOutDocDate)
SELECT doc.ID -- AS ERCID
,CASE WHEN doc.RegDate IS NOT NULL THEN doc.DocN+', ' ELSE doc.DocN END -- AS DocN
,CONVERT(VARCHAR(10), doc.RegDate, 104) -- AS RegDate
,ISNULL(p.Name,'') -- AS PartnerName
,(CASE WHEN (ISNULL(p.Name,'') != '' AND ISNULL(i.Author,'') != '') THEN (', ' + ISNULL(i.Author,'')) ELSE ISNULL(i.Author,'') END)-- AS AuthorName
,ISNULL(o.Name,'') -- AS OrganizationName
,CASE WHEN i.NOutDoc IS NOT NULL AND i.NOutDocDate IS NOT NULL THEN i.NOutDoc + ', ' ELSE ISNULL(i.NOutDoc, '') END -- AS NOutDoc
,ISNULL(CONVERT(VARCHAR(10), i.NOutDocDate, 104), '') -- AS NOutDocDate
FROM @tmpTableDocID doc
LEFT JOIN dbo.LDJOURNAL j ON j.ID = doc.JournalID
LEFT JOIN dbo.LDINERC i ON i.ID = doc.ID
LEFT JOIN dbo.LDVOCABULARY p ON p.ID = i.PartnerID
LEFT JOIN GRK_APPEALERC ae ON ae.ID = doc.ID
LEFT JOIN dbo.LDVOCABULARY o ON o.ID = ae.OrgID
-- ORDER BY j.Name, doc.RegDate, doc.DocN
SELECT @tmpNum = Number FROM dbo.GRK_GENIRATEREESTRERCNANO WHERE [YEAR] = YEAR(GETDATE());
DECLARE tmpCur CURSOR LOCAL FOR
SELECT ERCID FROM @tmpTableResult
OPEN tmpCur
FETCH tmpCur INTO @tmpERCID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @tmpFirstMailID = NULL
SET @tmpMainMailID = NULL
SET @tmpDeliveryTypeID = NULL
SELECT @tmpMainMailID = cf.MainMailID FROM dbo.GRK_COMMONFIELDS cf WHERE cf.ID = @tmpERCID
IF @tmpMainMailID IS NULL
SELECT @tmpFirstMailID = m.ID
FROM dbo.LDMAIL m
WHERE m.ERCID = @tmpERCID
AND m.MailStateID NOT IN (1,5,11)
AND m.ReceiverID IN (SELECT ID FROM @users)
AND m.CreatorID = @pUserID
AND m.ID IN (SELECT TOP 1 ID FROM LDMAIL m1 WHERE m1.ERCID = @tmpERCID
AND m1.MailStateID NOT IN (1,5,11)
ORDER BY m1.CreateDateTime)
SELECT @tmpDeliveryTypeID = DeliveryTypeID FROM dbo.LDERC WHERE ID = @tmpERCID
SELECT @tmpDelTypeID = ID FROM dbo.LDVOCABULARY WHERE Name = 'АСД ЛанДокс'
IF (@tmpMainMailID IS NOT NULL OR @tmpFirstMailID IS NOT NULL)
AND EXISTS (SELECT NULL FROM dbo.LDMAIL m
-- INNER JOIN dbo.LDUSER u ON u.ID = m.ReceiverID AND u.DepartmentID = @pDepartmentID
WHERE m.ID IN (@tmpMainMailID, @tmpFirstMailID)
AND m.ReceiverID IN (SELECT ID FROM @users))
BEGIN
IF @tmpDeliveryTypeID = @tmpDelTypeID
BEGIN
UPDATE @tmpTableResult SET StatusDoc = 'Оригинал/LanDocs' WHERE ERCID = @tmpERCID
SET @tmpReestrType = 3
END
ELSE
BEGIN
UPDATE @tmpTableResult SET StatusDoc = 'Оригинал' WHERE ERCID = @tmpERCID;
SET @tmpReestrType = 1
END
END
ELSE
BEGIN
IF @tmpDeliveryTypeID = @tmpDelTypeID
BEGIN
UPDATE @tmpTableResult SET StatusDoc = 'Копия/LanDocs' WHERE ERCID = @tmpERCID;
SET @tmpReestrType = 4
END
ELSE
BEGIN
UPDATE @tmpTableResult SET StatusDoc = 'Копия' WHERE ERCID = @tmpERCID;
SET @tmpReestrType = 2
END
END
SELECT @tmpMailID = ISNULL(@tmpMainMailID, @tmpFirstMailID)
EXEC GRK_UPDPREESTRINERC @tmpERCID, @tmpNum, @pDepartmentID, @tmpMailID, @tmpReestrType, @ReturnParm OUTPUT
FETCH tmpCur INTO @tmpERCID
END
CLOSE tmpCur
SELECT DocN, RegDate, PartnerName, AuthorName, OrganizationName, NOutDoc, NOutDocDate, StatusDoc
FROM @tmpTableResult
ORDER BY AuthorName, RegDate, DocN
END
GO
GRANT EXECUTE ON DBO.GRK_REP_REESTRINERC TO LDUSER
GRANT EXECUTE ON DBO.GRK_REP_REESTRINERC TO LDADMIN
GO
--Автор: Ершова Л.М.
--Дата создания: 31.03.2015
--Описание: Отчет о проведенных контрольных мероприятиях
DECLARE @tmpErrorMsg nvarchar(4000), @tmpScriptName nvarchar(512)
SET @tmpScriptName = N'Изменение хранимой процедуры GRK_REP_REESTRINERC'
BEGIN TRY
IF EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'GRK_REGDBOBJECT') AND type in (N'P', N'PC'))
BEGIN
DECLARE @tmpDBObject_Name nvarchar(512),@tmpScriptComment nvarchar(2000),@tmpDBObjectID INT
SET @tmpDBObject_Name = N'GRK_REP_REESTRINERC'
SET @tmpScriptComment = N'Отчет о проведенных контрольных мероприятиях'
EXEC GRK_REGDBOBJECT @tmpDBObject_Name,'Хранимая процедура',NULL,N'Ершова Л.М.',N'20150331',N'3.1.2.43',N'Ершова Л.М. от 31.03.2015',@tmpScriptComment,@tmpDBObjectID output
IF EXISTS(SELECT NULL FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'GRK_DBOBJECTS' AND sc.name = 'IsSystem')
AND @tmpDBObjectID > 0
BEGIN
DECLARE @tmpUpDateDBOBJECTS nvarchar(4000)
SET @tmpUpDateDBOBJECTS = 'UPDATE dbo.GRK_DBOBJECTS SET IsSystem = ''-'' WHERE ID = ' + CAST(@tmpDBObjectID as varchar)
EXEC(@tmpUpDateDBOBJECTS)
END
END
DECLARE @tmpLDUsedScript_Name nvarchar(1024), @tmpLDUsedScript_Descr nvarchar(4000)
SET @tmpLDUsedScript_Name = N'"Изменение хранимой процедуры GRK_REP_REESTRINERC" от "31.03.2015" автор "Ершова Л.М."'
SET @tmpLDUsedScript_Descr = N'Отчет о проведенных контрольных мероприятиях'
INSERT INTO DBO.LDUsedScripts (name,description,userlogin,ExecDate) SELECT @tmpLDUsedScript_Name,@tmpLDUsedScript_Descr,USER_NAME(),GETDATE()
END TRY
BEGIN CATCH
SET @tmpErrorMsg = N'При выполнении задачи "' + @tmpScriptName + '" произошла ошибка: ' + ERROR_MESSAGE()
SET @tmpLDUsedScript_Name = N'"Изменение хранимой процедуры GRK_REP_REESTRINERC" от "31.03.2015" автор "Ершова Л.М."'
SET @tmpLDUsedScript_Descr = @tmpErrorMsg
INSERT INTO DBO.LDUsedScripts (name,description,userlogin,ExecDate) SELECT @tmpLDUsedScript_Name,@tmpLDUsedScript_Descr,USER_NAME(),GETDATE()
RAISERROR(@tmpErrorMsg, 16, 1)
END CATCH
GO