Langsung ke konten utama

Procedure and Trigger System Flow

PROCEDURE [System].[Flow.Execute]



[sql]
CREATE PROCEDURE [System].[Flow.Execute]
@FlowID INT
AS
BEGIN

SET NOCOUNT ON;

DECLARE
@DoctypeID INT
,@DocID INT
,@PrevSeq TINYINT
,@NextSeq TINYINT
,@DocHeader CHAR(3)
,@ErrMsg NVARCHAR(1000)
,@TableName NVARCHAR(50)
,@SqlCommand NVARCHAR(MAX)
,@LogicID INT
,@ProcCheck VARCHAR(100)
,@ProcUpdate VARCHAR(100)
,@ProcFail VARCHAR(100)
,@ProcNotify VARCHAR(100)
,@DocNo VARCHAR(50)
,@GLNo VARCHAR(50)

IF EXISTS
(
SELECT
flow_id
FROM System.[Flow.History]
WHERE flow_id = @FlowID
)
BEGIN
UPDATE System.[Flow.History]
SET flow_processed = 1
WHERE
flow_id = @FlowID
END
ELSE
BEGIN
PRINT 'Flow '+CONVERT(VARCHAR(9),@FlowID)+' not found'
END

SELECT
@DoctypeID = a.doctype_id
,@DocID = a.doc_id
,@PrevSeq = a.docflow_prev
,@NextSeq = a.docflow_next
,@DocHeader = c.doctype_header
,@TableName = c.doctype_table
FROM System.[Flow.History] a
INNER JOIN System.[Flow.Logic] b ON a.doctype_id = b.doctype_id
INNER JOIN System.[Flow.Document] c ON a.doctype_id = c.doctype_id
WHERE flow_id = @FlowID

SELECT
@LogicID = a.logic_id
,@ProcCheck = a.proc_check
,@ProcUpdate = a.proc_update
,@ProcFail = a.proc_fail
,@ProcNotify = b.proc_notify
FROM System.[Flow.Logic] a
INNER JOIN System.[Flow.State] b ON a.doctype_id = b.doctype_id
AND a.next_seq = b.docflow_seq
WHERE a.doctype_id = @DoctypeID
AND a.prev_seq = @PrevSeq
AND a.next_seq = @NextSeq

--- Error if logic_id not found

IF @LogicID IS NULL
BEGIN
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'Document flow logic for '+@DocHeader+'-'+LTRIM(CONVERT( VARCHAR(3),@PrevSeq))+'-'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))+' is not found'
WHERE
flow_id = @FlowID
RETURN 0
END

-- set default StoredProcedure if not defined

IF isnull(@ProcCheck,'') = ''
BEGIN
SET @ProcCheck = 'DocFlow.'+RTRIM(@DocHeader)+'.Check.'+LTRIM(CONVERT(VARCHAR(3),@PrevSeq))+'.'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))
END
IF isnull(@ProcUpdate,'') = ''
BEGIN
SET @ProcUpdate = 'DocFlow.'+RTRIM(@DocHeader)+'.Update.'+LTRIM(CONVERT(VARCHAR(3),@PrevSeq))+'.'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))
END
IF isnull(@ProcFail,'') = ''
BEGIN
SET @ProcFail = 'DocFlow.'+RTRIM(@DocHeader)+'.Fail.'+LTRIM(CONVERT(VARCHAR(3),@PrevSeq))+'.'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))
END
IF isnull(@ProcNotify,'') = ''
BEGIN
SET @ProcNotify = 'DocFlow.'+RTRIM(@DocHeader)+'.Notify.'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))
END

SET @ErrMsg = ''


-- Run check procedure

BEGIN TRY
IF isnull(@ProcCheck,'') <> ''
BEGIN
IF EXISTS
(
SELECT
name
FROM sys.procedures
WHERE name = @ProcCheck
)
BEGIN
SET @ProcCheck = 'System.['+@ProcCheck+']'
EXEC @ProcCheck @DocID
,@ErrMsg OUTPUT
END
END
END TRY
BEGIN CATCH
PRINT 'Catch error at CHECK Procedure : '+@ProcCheck+' - '+ERROR_MESSAGE()
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'SQL Error: '+ERROR_MESSAGE()
WHERE
flow_id = @FlowID
RETURN 0
END CATCH;


-- If check procedure running and failed - RUN Fail Procedure

BEGIN TRY
IF @ErrMsg <> ''
BEGIN
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = @ErrMsg
WHERE
flow_id = @FlowID

IF isnull(@ProcFail,'') <> ''
BEGIN
IF EXISTS
(
SELECT
name
FROM sys.procedures
WHERE name = @ProcFail
)
BEGIN
SET @ProcFail = 'System.['+@ProcFail+']'
EXEC @ProcFail @DocID
END
END

RETURN 0
END
END TRY
BEGIN CATCH
PRINT 'Catch error at Fail Procedure : '+@ProcFail+' - '+ERROR_MESSAGE()
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'SQL Error: '+ERROR_MESSAGE()
WHERE
flow_id = @FlowID
RETURN 0
END CATCH;


-- Run update procedure

IF @ErrMsg = ''
BEGIN

BEGIN TRY
IF isnull(@ProcUpdate,'') <> ''
BEGIN
IF EXISTS
(
SELECT
name
FROM sys.procedures
WHERE name = @ProcUpdate
)
BEGIN
SET @ProcUpdate = 'System.['+@ProcUpdate+']'
EXEC @ProcUpdate @DocID
,@ErrMsg OUTPUT
END
ELSE
BEGIN
SET @ErrMsg = 'Update procedure '+@ProcUpdate+' for '+@DocHeader+'-'+LTRIM(CONVERT(VARCHAR(3),@PrevSeq))+'-'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))+' is not found'
END
END

IF @ErrMsg <> ''
BEGIN
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = @ErrMsg
WHERE
flow_id = @FlowID
END
END TRY
BEGIN CATCH
PRINT 'Catch error at UPDATE Procedure : '+@ProcUpdate+' - '+ERROR_MESSAGE()
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'SQL Error: '+ERROR_MESSAGE()
WHERE
flow_id = @FlowID

RETURN 0
END CATCH;

BEGIN TRY
UPDATE System.[Flow.History]
SET update_success = 1
,update_log = @ErrMsg
WHERE
flow_id = @FlowID
END TRY
BEGIN CATCH
PRINT 'Catch error at UPDATING LOG to Success : '+CONVERT(VARCHAR(10),@FlowID)+' - '+ERROR_MESSAGE()
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'Can not set update flag'
WHERE
flow_id = @FlowID
END CATCH

BEGIN TRY
INSERT INTO [System].[Notification]
(
[notice_form]
,[doctype_id]
,[doc_id]
,[notice_user]
,[notice_subject]
,[notice_email]
)
SELECT
[notice_form]
,@DoctypeID
,@DocID
,[notice_user]
,[notice_subject]
,[notice_email]
FROM [System].[Flow.Notice]
WHERE doctype_id = @DoctypeID
AND docflow_seq = @NextSeq
END TRY
BEGIN CATCH
PRINT 'Catch error at sending email notification: '+ERROR_MESSAGE()
END CATCH

-- Run notify procedure

BEGIN TRY
IF isnull(@ProcNotify,'') <> ''
BEGIN
IF EXISTS
(
SELECT
name
FROM sys.procedures
WHERE name = @ProcNotify
)
BEGIN
SET @ProcNotify = 'System.['+@ProcNotify+']'

EXEC @ProcNotify @DocID
,@ErrMsg OUTPUT
END
ELSE
BEGIN
SET @ErrMsg = 'Notify procedure '+@ProcNotify+' for '+@DocHeader+'-'+LTRIM(CONVERT(VARCHAR(3),@NextSeq))+' is not found'
END
END

IF @ErrMsg <> ''
BEGIN
UPDATE System.[Flow.History]
SET update_success = 0
,update_log = 'Notify : '+@ErrMsg
WHERE
flow_id = @FlowID
END
END TRY
BEGIN CATCH
PRINT 'Catch error at NOTIFY Procedure: '+@ProcNotify+' - '+ERROR_MESSAGE()
UPDATE System.[Flow.History]
SET update_log = 'SQL Error on notify: '+ERROR_MESSAGE()
WHERE
flow_id = @FlowID
RETURN 0
END CATCH;
END
END
[/sql]

TRIGGER [System].[Flow.History]


[sql]
CREATE TRIGGER [System].[Flow.ExecuteTrigger] ON [System].[Flow.History]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@Result TABLE
(
[doctype_id] [TINYINT]
,[doc_id] [INT]
,[docflow_prev] [TINYINT]
,[docflow_next] [TINYINT]
,[update_log] [NVARCHAR](MAX)
,[update_success] [BIT]
)

DECLARE
@FLOW_ID INT
,@LastID INT
,@Show BIT
SELECT TOP 1
@FLOW_ID = flow_id
,@Show = flow_result
FROM System.[Flow.History]
WHERE flow_processed = 0
ORDER BY
1 ASC

WHILE @FLOW_ID IS NOT NULL
BEGIN

BEGIN TRY
EXEC System.[Flow.Execute] @Flow_ID
END TRY
BEGIN CATCH
UPDATE System.[Flow.History]
SET flow_processed = 1
,update_success = 0
,update_log = ERROR_MESSAGE()
WHERE
flow_id = @Flow_ID
END CATCH

IF @Show = 1
BEGIN
INSERT INTO @Result
SELECT
doctype_id
,doc_id
,docflow_prev
,docflow_next
,update_log
,update_success
FROM System.[Flow.History]
WHERE flow_id = @Flow_ID
END
SELECT
@LastID = @Flow_ID
,@FLOW_ID = NULL
SELECT TOP 1
@FLOW_ID = flow_id
FROM System.[Flow.History]
WHERE flow_processed = 0
ORDER BY
1 ASC
PRINT 'Execute Flow : '+CONVERT(VARCHAR,@Flow_ID)
END

IF
(
SELECT
COUNT(*)
FROM @Result
) > 0
BEGIN
SELECT
*
FROM @Result
END
END
[/sql]
Label:

Komentar

Posting Komentar

Postingan populer dari blog ini

Totolink N300RT

Panduan singkat install OpenWrt Totolink N300RT Download FrmUpg :  http://www.anphat.vn/download/FirmwareUpgradeTool.zip Putty : http://www.putty.org/ Frimware totolink v2.1.6 :  http://www.totolink.net/include/download.asp?path=down/010300&file=N300RT-V2.1.6_20160516.zip Bootloder boot96E_32M:  https://drive.google.com/open?id=1p0SreEifzElS_cyngZZRL2lmGnR-Mvvi OpenWrt  :  https://drive.google.com/open?id=1ykv6QkrqXJ_8vnX-6imib44XGFVNJZoA A. Downgrade Frimware Totolink ke v2.1.6 Dalam router dalam keadaan mati tekan Tombol Reset lalu hidupkan power. tunggu sampai lampu indikator lan berkedip. Matikan Firewall dan Buka FrmUpg.exe dan colokan LAN ke Port 1,2,3,4 Di Frimware File pilih file Totolink v2.1.6 dan klik send, Tunggu sampai prosess selesai lalu restart router, cek 192.168.1.1 apakah sudah berhasil downgrade. B. Upgrade OpenWrt Ulangi langkah no 1, Pilih file bootloder boot96E_32M. klik send. tunggu sampai selesa...
3 komentar
Baca selengkapnya

How to BLOCK Select SSMS

SQL Server Management Studio has so much capability, yet most people I meet don’t seem to have read the manual to discover it. So I thought I might post a few of features I find invaluable. This works for SQL 2005, SQL2008, SQL2008 R2 & Visual Studio. Summary: Tip 1:  It is possible to “ALT + Select” to highlight a block of text to work on. Tip 2:  If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines. The following is a quick example of what I mean plus a few other uses you may not have thought of. Example: Tip 1:  It is possible to “ALT + Select” to highlight a block of text to work on. eg: Hold the ALT key, Left Click & move your Mouse produces the Blue “Selected” region in picture below. You must press ALT prior to moving the mouse.   If I hit DELETE, then I remove just those characters so they all line up nex...
Posting Komentar
Baca selengkapnya

How to customize tab colors in SSMS

Applies to ApexSQL Complete Summary This article describes how to set a custom connection color to a query tab in Microsoft  SQL Server Management Studio  (SSMS) using a feature called  Tab coloring  in ApexSQL Complete. Description Tab coloring can set custom connection colors for individual instances of SQL Server, down to the database level. The user has ability to assign SQL servers, and databases to a specific environment to help quickly identify which connection a tab is currently using.
Posting Komentar
Baca selengkapnya