...
Блок кода | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
declare @scriptGuid uniqueidentifier, @taskNumber varchar(10), @scriptName varchar(150), @scriptdescription varchar(max) set @scriptGuid = 'A7681C28-A62D-4E78-B258-3AE919ECD83B' --для конкретного скрипта присваиваем значение нового гуида set @taskNumber = 'TMMIS-3333' --присваиваем номер задачи Jira set @scriptName = 'Администрирование. Триггер. (TMMIS-3333). Изменение статуса счёта после выполнения всех его услуг' --наименование скрипта (краткое описание) set @scriptdescription = 'Администрирование. Триггер. (TMMIS-3333). Изменение статуса счёта после выполнения всех его услуг' --описание скрипта if (not exists(select 1 from web_script where [guid] = @scriptGuid)) begin insert into web_script(x_edition, x_status, [guid], taskNumber, Name, [description], RunCount, Success) select 1, 1, @scriptGuid, @taskNumber, @scriptName, @scriptdescription, 1, 0 end else begin update web_script set RunCount = RunCount + 1 where [guid] = @scriptGuid end /* ----------------- end of: Начало выполнения запроса ------------------------- */ if exists (select 1 from sys.objects where name = 'InvoiceStatusTrigger' and type = 'TR') begin drop trigger [dbo].InvoiceStatusTrigger end go create trigger [dbo].InvoiceStatusTrigger on hlt_SMTAP after insert as begin if ( ((select max(rf_InvoiceID) from inserted)declare @date datetime = 0)convert( or ((datetime, (select isnull((select top 1 ValueIntValueStr from x_UserSettings where Property = 'Платные услуги: АвансАванс: Дата включения (ДД.ММ.ГГГГ)' and rf_UserID = 1), '01.01.2222')), 0) 104) if ( ((select max(rf_InvoiceID) from inserted) = 0) or ((select isnull((select top 1 ValueDateValueInt from x_UserSettings where Property = 'АвансПлатные услуги: Дата включенияАванс' and rf_UserID = 1), '22220101'0)) = '22220101'0) or @date > getdate() ) return declare @doneStatusId int = (select isnull((select top 1 InvoiceStatusID from hlt_InvoiceStatus where Code = '10'), 0)) --оплачен и оказан declare @ofdStatusId int = (select isnull((select top 1 InvoiceStatusID from hlt_InvoiceStatus where Code = '9'), 0)) --требуется отправка в ОФД update invoice set rf_InvoiceStatusID = ( select case when count(smt.SMTAPID) >= count(bs.BillServiceId) then case when (select isnull((select top 1 ValueInt from x_UserSettings where Property = 'Интеграция с 1С' and rf_UserID = 1), 0)) > 0 then @ofdStatusId else @doneStatusId end else invoice.rf_InvoiceStatusID end from hlt_Invoice inv join hlt_InvoiceStatus status on inv.rf_InvoiceStatusID = status.InvoiceStatusID and status.Code = '3' --оплачен left join hlt_BillService bs on bs.rf_InvoiceID = inv.InvoiceID left join hlt_SMTAP smt on smt.rf_InvoiceID = inv.InvoiceID and smt.rf_BillServiceID = bs.BillServiceID where inv.InvoiceID = invoice.InvoiceID ) from hlt_Invoice invoice join hlt_InvoiceStatus status on invoice.rf_InvoiceStatusID = status.InvoiceStatusID and status.Code = '3' --оплачен where invoice.InvoiceID in (select distinct rf_InvoiceID from inserted) and invoice.Date >= (select top 1 ValueDate from x_UserSettings where Property = 'Аванс: Дата включения' and rf_UserID = 1)@date end go /* ----------------- Конец выполнения запроса --------------------------------- */ update web_script set Success = 1 where [guid] = 'A7681C28-A62D-4E78-B258-3AE919ECD83B' /* ----------------- end of: Конец выполнения --------------------------------- */ |
...