Реклама
Опрос
Погода
 
|
Григорий Ненашев (все сообщения)
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Собственно вопрос в теме, кто-нибудь разворачивал сервер сервисдеска на 2008 сервере? работает? или только 2003..
Решение:
Работать будет
Сам сервер работает на SUN Java и от Microsoft он использует только ODBC конектор, так что причин не работать нет.
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Задания - Изменения
В поле CCU_CHANGENUMBER3 +1 или -1 если связанное задание в поле факт.окончания (WOR_ACTUALFINISH) не пусто.
<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: +1 and -1 WO from Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_UPDATE]
ON [dbo].[ITSM_WORKORDERS]
FOR UPDATE
AS
IF UPDATE(WOR_CHA_OID)
BEGIN
IF NOT((SELECT WOR_CHA_OID FROM inserted) is NULL)
BEGIN
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(i.WOR_CHA_OID is null)
END
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM deleted d
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON d.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(d.WOR_CHA_OID is null)
END
IF UPDATE(WOR_ACTUALFINISH)
BEGIN
SET NOCOUNT ON
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS AS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(i.WOR_ACTUALFINISH is null)
-- Update +1 if WO set back in production
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS AS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE (i.WOR_ACTUALFINISH is null)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<hr>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Изменения
В поле CCU_CHANGENUMBER3 -1 если удалили связанное задание.
<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: Count WOs and store in Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_DELETE]
ON [dbo].[ITSM_WORKORDERS]
FOR DELETE
AS
BEGIN
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM deleted d
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON d.WOR_CHA_OID = ch.CCU_CHA_OID
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Изменения
В поле CCU_CHANGENUMBER3 +1 если добавили связанное задание.
<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: Count WOs and store in Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_ADD]
ON [dbo].[ITSM_WORKORDERS]
FOR INSERT
AS
--IF UPDATE(WOR_CHA_OID)
BEGIN
set NOCOUNT ON;
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
При связанном изменении поле факт. Окончания не пусто. То вычитает 1 из поля scf_scnumber7 в обращении.
<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================
CREATE TRIGGER [dbo].[CountOfCHANGES_UPDATE]
ON [dbo].[ITSM_CHANGES]
FOR UPDATE
AS
IF UPDATE (CHA_ACTUALFINISH)
BEGIN
IF NOT((SELECT CHA_OID FROM inserted) is NULL)
SET NOCOUNT ON
UPDATE ch
SET ch.scf_scnumber7 = isnull(ch.scf_scnumber7,0) - 1
FROM inserted ITSM_CHANGES
left JOIN ITSM_SERVICE_RELATIONS ON ITSM_CHANGES.cha_oid = ITSM_SERVICE_RELATIONS.SRE_CHA_OID
left join ITSM_SERVICECALLS ON ITSM_SERVICECALLS.SER_OID = ITSM_SERVICE_RELATIONS.SRE_SER_OID
left join ITSM_SER_CUSTOM_FIELDS AS ch ON ITSM_SERVICECALLS.SER_OID = ch.SCF_SER_OID
WHERE not(ITSM_CHANGES.CHA_ACTUALFINISH is null)
END
IF UPDATE (CHA_ACTUALFINISH)
BEGIN
IF NOT((SELECT CHA_OID FROM inserted) is NULL)
SET NOCOUNT ON
UPDATE ch
SET ch.scf_scnumber7 = isnull(ch.scf_scnumber7,0) + 1
FROM inserted ITSM_CHANGES
left JOIN ITSM_SERVICE_RELATIONS ON ITSM_CHANGES.cha_oid = ITSM_SERVICE_RELATIONS.SRE_CHA_OID
left join ITSM_SERVICECALLS ON ITSM_SERVICECALLS.SER_OID = ITSM_SERVICE_RELATIONS.SRE_SER_OID
left join ITSM_SER_CUSTOM_FIELDS AS ch ON ITSM_SERVICECALLS.SER_OID = ch.SCF_SER_OID
WHERE (ITSM_CHANGES.CHA_ACTUALFINISH is null)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Прибавляет Кол. Всех связанных Эл. в карточке обращения к полю scf_scnumber7
<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vasily Kamenev, Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================
CREATE TRIGGER [dbo].[DBRule_setChangePandingToSCADD]
ON [dbo].[ITSM_SERVICE_RELATIONS]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(SRE_SER_OID) AND UPDATE(SRE_CHA_OID)
DECLARE @sc_oid As decimal(18,0),
@ch_oid As decimal(18,0),
@rel_oid AS decimal(18,0),
@target AS datetime,
@folder As nvarchar(255)
SET @rel_oid = (SELECT MAX(SRE_OID) FROM dbo.ITSM_SERVICE_RELATIONS)
SELECT @folder=REP_CODES_TEXT.RCT_NAME, @sc_oid=ITSM_SERVICECALLS.SER_OID
FROM ITSM_SERVICE_RELATIONS INNER JOIN
ITSM_SERVICECALLS ON ITSM_SERVICE_RELATIONS.SRE_SER_OID = ITSM_SERVICECALLS.SER_OID INNER JOIN
REP_CODES_TEXT ON ITSM_SERVICECALLS.SER_POO_OID = REP_CODES_TEXT.RCT_RCD_OID
WHERE (ITSM_SERVICE_RELATIONS.SRE_OID = @rel_oid) AND (REP_CODES_TEXT.RCT_LNG_OID = 1033)
UPDATE ITSM_SER_CUSTOM_FIELDS
SET scf_scnumber7 = isnull(scf_scnumber7,0) + 1
where SCF_SER_OID = @sc_oid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Вычитает Кол. Всех связанных Эл. в карточке обращения к полю scf_scnumber7
<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vasily Kamenev, Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================
CREATE TRIGGER [dbo].[DBRule_setChangeToSCDELETE]
ON [dbo].[ITSM_SERVICE_RELATIONS]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(SRE_SER_OID) AND UPDATE(SRE_CHA_OID)
DECLARE @sc_oid As decimal(18,0),
@rel_oid AS decimal(18,0),
@folder As nvarchar(255)
SET @rel_oid = (SELECT MAX(SRE_OID) FROM dbo.ITSM_SERVICE_RELATIONS)
SELECT @folder=REP_CODES_TEXT.RCT_NAME, @sc_oid=ITSM_SERVICECALLS.SER_OID
FROM ITSM_SERVICE_RELATIONS INNER JOIN
ITSM_SERVICECALLS ON ITSM_SERVICE_RELATIONS.SRE_SER_OID = ITSM_SERVICECALLS.SER_OID INNER JOIN
REP_CODES_TEXT ON ITSM_SERVICECALLS.SER_POO_OID = REP_CODES_TEXT.RCT_RCD_OID
WHERE (ITSM_SERVICE_RELATIONS.SRE_OID = @rel_oid) AND (REP_CODES_TEXT.RCT_LNG_OID = 1033)
UPDATE ITSM_SER_CUSTOM_FIELDS
SET scf_scnumber7 = isnull(scf_scnumber7,0) - 1
where SCF_SER_OID = @sc_oid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Копирует в поле PEC_PERSONDATE4 время захода специалиста в Service Desk.
Поле Custom Filds
<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================
CREATE TRIGGER [dbo].[DATA_UPDATE_USER]
ON [dbo].[REP_SESSIONS]
FOR INSERT
AS
IF UPDATE (SES_CREATED)
BEGIN
UPDATE pe
SET pe.PEC_PERSONDATE4 = REP_SESSIONS.SES_CREATED
FROM inserted REP_SESSIONS
LEFT JOIN REP_ACCOUNTS ON REP_SESSIONS.SES_ACC_OID = REP_ACCOUNTS.ACC_OID
LEFT JOIN ITSM_PERSONS ON REP_ACCOUNTS.ACC_OID = ITSM_PERSONS.PER_ACC_OID
LEFT JOIN ITSM_PER_CUSTOM_FIELDS AS pe ON ITSM_PERSONS.PER_OID = pe.PEC_PER_OID
WHERE not (REP_SESSIONS.SES_CREATED is null)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Позволяет зарегистрировать письмо от человека которого нет в базе и запишет его мыло в поле информация.
<HR>
CREATE TRIGGER [GetMail]
ON [dbo].[ITSM_SER_INFORMATION]
AFTER INSERT
AS
BEGIN
declare @sMail varchar(1024)
declare @CALLER_ID decimal(18,0)
declare @ORG_ID decimal(18,0)
declare @CALLER_ORG_ID decimal(18,0)
DECLARE @errno int /* Номер ошибки */
IF ( (select ins.SEI_INFORMATION from inserted ins) like '%<%>%'
AND
(select SER_CALLER_PER from inserted ins, dbo.ITSM_SERVICECALLS
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID) is Null)
BEGIN
IF EXISTS (SELECT isnull ((SUBSTRING(ins.SEI_INFORMATION, PATINDEX('%<%' , ins.SEI_INFORMATION)+1 , PATINDEX('%>%' , ins.SEI_INFORMATION) - PATINDEX('%<%' , ins.SEI_INFORMATION) -1)), '')
FROM inserted ins)
BEGIN
SELECT @sMail =(SUBSTRING(ins.SEI_INFORMATION, PATINDEX('%<%' , ins.SEI_INFORMATION)+1 , PATINDEX('%>%' , ins.SEI_INFORMATION) - PATINDEX('%<%' , ins.SEI_INFORMATION) -1))
FROM inserted ins
if @sMail <> '' begin
IF @sMail like '%@%%.%'
begin
update dbo.ITSM_SER_CUSTOM_FIELDS
set SCF_SCTEXT11 = @sMail
from inserted ins
where ins.SEI_SER_OID = SCF_SER_OID
select @CALLER_ID= PER_OID from ITSM_PERSONS
where ITSM_PERSONS.PER_EMAIL=@sMail
select @CALLER_ORG_ID=PER_ORG_OID from ITSM_PERSONS
where ITSM_PERSONS.PER_EMAIL=@sMail
update dbo.ITSM_SERVICECALLS
set SER_CALLER_PER=@CALLER_ID
from inserted ins
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID
select @ORG_ID=ORG_OID from dbo.ITSM_ORGANIZATIONS
where ITSM_ORGANIZATIONS.ORG_OID=@CALLER_ORG_ID
update dbo.ITSM_SERVICECALLS
set SER_CALLER_ORG=@ORG_ID
from inserted ins
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID
end
end
end
END
END
<HR>
|
|
|
|
Администратор
Cообщений: 1870
Баллов: 11496
Регистрация: 25.01.2010
|
Что то мне подсказывает что в сд уже есть встроенная интеграция с ад ad.ini
|
|
|
|
Сегодня были (гостей: 1707, пользователей: 0, из них скрытых: 0)
| |