IF EXISTS(select name from sysobjects where name='cro_enforcement')
DROP PROCEDURE cro_enforcement
GO
CREATE PROCEDURE cro_enforcement
@datefrom DATETIME, @dateto DATETIME, @bolCrystal BIT
AS
SET NOCOUNT ON
--chris hardy circa Sep 2003
--use reports
DELETE t_enforcement
DECLARE @bol1stfailure BIT,
@nulldate DATETIME,
@returnvalue INT,
@bolstopcount BIT,
@bolproceed BIT,
@bolreqrecord BIT,
@appliedfortargetdate DATETIME,
@crn VARCHAR(40),
@bolnoaction BIT,
@bol1stbreach BIT,
@eventid INT,
@iCurrentRowId INT,
@ucount INT,
@bolappoccurred BIT,
@bolnewsuper BIT,
@2nducontactdate DATETIME,
@v_bolappoccurred BIT,
@v_bolreqrecord BIT,
@v_bolnewsuper BIT,
@v_surname VARCHAR(20),
@v_forename VARCHAR(40),
@v_crn VARCHAR(7),
@v_teamname VARCHAR(30),
@v_supervisiontype VARCHAR(2),
@v_commencement DATETIME,
@v_eventid INT,
@v_termdate DATETIME,
@v_ordertype VARCHAR(45),
@v_apptkept VARCHAR(2),
@v_action VARCHAR(50),
@v_contactdate DATETIME,
@v_contacttype CHAR(2),
@v_officerid CHAR(7),
@v_offsurname VARCHAR(20),
@v_offforename VARCHAR(20),
@v_supervisioncount INT,
@v_breachcount INT,
@v_targetdate DATETIME,
@v_noactionoccurred INT
SELECT @bol1stfailure = 1,
@bolstopcount = 0,
@bolproceed = 1,
@bolappoccurred = 0,
@bolreqrecord = 0,
@ucount = 0,
@bolnewsuper = 0,
@nulldate = NULL,
@bolnoaction = 0,
@bol1stbreach = 1
INSERT INTO t_enforcement
SELECT @bolappoccurred,
@bolreqrecord,
@bolnewsuper,
c.surname,
c.forename,
c.crn,
t.teamname,
s.supervisiontype,
s.commencement,
s.eventid,
s.termdate,
o.type,
l.apptkept,
l.action,
l.contactdate,
l.contacttype,
p.personalno AS officerid,
p.surname AS offsurname,
p.forename AS offforename,
0,
0,
@nulldate,
0
FROM
client c,
event e,
supervision s,
orders o,
contactlog l,
team t,
po p
WHERE
c.dbnumber = e.dbnumber
AND c.urn = e.urn
AND e.dbnumber = s.dbnumber
AND e.urn = s.urn
AND e.supid = s.eventid
AND s.supervisiontype = o.code
AND s.dbnumber = l.dbnumber
AND s.eventid = l.eventid
AND s.urn = l.urn
AND s.teamcode = t.teamcode
AND p.personalno = s.suppopersonalno
AND c.dlf IS NULL --not deleted flag in client table
AND e.dlf IS NULL --not deleted flag in event table
AND s.supervisiontype ='01'
AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
AND s.transout IS NULL --not transferred out (of area)
AND s.commencement BETWEEN @datefrom AND @dateto
--and s.commencement between '01-sep-2002' and '01-sep-2003'
--and s.termdate is null -- not terminated
AND (l.apptkept = 'u' --unattended
OR (l.action LIKE '%summ%%appl%' AND l.action NOT LIKE '%VOID%')
OR l.action LIKE '%warrant applied%'
OR l.action LIKE '%breach%%action%%reque%'
OR l.action LIKE '%warrant req%'
-- OR l.action LIKE '%induction%'
OR l.action LIKE '%to contin%'
OR l.action LIKE '%otc%'
OR l.action LIKE '%br%%ex%' --breach exempt
OR (l.action LIKE '%withdrawn%'AND l.action not LIKE '%ins%'AND l.action not LIKE '%nvq%')) --'%breach withdrawn%'
AND
(SELECT COUNT(*)
FROM
client c1,
event e1,
supervision s1,
orders o1,
contactlog l1,
team t1,
po p1
WHERE
c1.dbnumber = e1.dbnumber
AND c1.urn = e1.urn
AND e1.dbnumber = s1.dbnumber
AND e1.urn = s1.urn
AND e1.supid = s1.eventid
AND s1.supervisiontype = o1.code
AND s1.teamcode = t1.teamcode
AND s1.dbnumber = l1.dbnumber
AND s1.eventid = l1.eventid
AND s1.urn = l1.urn
AND p1.personalno = s1.suppopersonalno
--AND c1.crn = c.crn
and s1.eventid = s.eventid
AND c1.dlf IS NULL --not deleted flag in client table
AND e1.dlf IS NULL --not deleted flag in event table
AND l1.apptkept = 'u' --unattended
AND s1.supervisiontype ='01'
AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
AND s1.commencement BETWEEN @datefrom AND @dateto
--and s1.commencement between '01-sep-2002' and '01-sep-2003'
AND s1.transout IS NULL)>1 --not transferred out (of area)
ORDER BY s.eventid, l.contactdate
------------------------------------------------------------------------------
DECLARE @iLoopControl INT,
@iNextRowId INT
SELECT @iLoopControl = 1,
@iNextRowId = Min(id) FROM t_Enforcement
SELECT @iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_supervisiontype = csupervisiontype,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_ordertype = cordertype,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred
FROM t_enforcement
WHERE id = @iNextRowId
--print @v_surname
WHILE @iLoopControl = 1
--now iterate through the cursor to establish the target date (within working 10 days)for the referral to
--court after the 2nd 'u'. establish if they hit it or not and flag the record as required.
BEGIN
SELECT @bolreqrecord = 0
SELECT @bolappoccurred = 0
IF @v_eventid <> @eventid --have we moved on to a new crn?
BEGIN
IF @bolnoaction = 1 AND @eventid IS NOT NULL AND @bol1stfailure = 1 --no breach action has been taken
BEGIN
--SELECT @iCurrentRowId = @@IDENTITY --the previous eventid had no action taken,@@identity
--should give the previous id inserted. however
--have problems using @@indentity variable leaave this
--in could be used with sql 2000 as a function.
SELECT @iCurrentRowId = (SELECT MAX(id) FROM t_enforcement WHERE ceventid = @eventid)
UPDATE t_enforcement SET cbolreqrecord = 1, cnoactionoccurred = 1 WHERE IDENTITYCOL = @iCurrentRowId
END
SELECT @bol1stfailure = 1,
@bol1stbreach = 1,
@bolnoaction = 0,
@appliedfortargetdate = GETDATE(), -- set to a neutral date
@eventid = @v_eventid,
@bolproceed = 1,
@bolstopcount = 0,
@ucount = 0,
@2nducontactdate = NULL
END
IF (@v_action LIKE '%to contin%' OR @v_action LIKE '%otc%' OR @v_action LIKE '%to contin%' OR @v_action LIKE '%br%%ex%')
OR (@v_action LIKE '%withdrawn%' AND @v_action not LIKE '%ins%'AND @v_action not LIKE '%nvq%' ) --court appearance with an otc etc..
--so start counting u's from 0
BEGIN
SELECT @bol1stbreach = 1,
@bolnoaction = 0,
@appliedfortargetdate = GETDATE(), -- set to a neutral date
@bolproceed = 1,
@2nducontactdate = NULL,
@bolstopcount = 0,
@ucount = 0
END
IF @bolproceed = 1
BEGIN
IF @v_apptkept = 'u' AND @bolstopcount = 0
BEGIN
SELECT @ucount = @ucount + 1
IF @ucount = 2 --2nd 'u'
BEGIN
IF @bol1stbreach = 1
BEGIN
SELECT @bolnoaction = 1 --no action has been taken as yet so set to 1
END
SELECT @bol1stbreach = 0
EXEC @returnvalue = calc_working_days @datefrom = @v_contactdate, @number = 10
SELECT @appliedfortargetdate = DATEADD(dd,@returnvalue,@v_contactdate)
IF @appliedfortargetdate > GETDATE() OR @appliedfortargetdate > @v_termdate --terminated before target date
BEGIN
SELECT @v_bolnewsuper = 1,
@bolnoaction = 0 --no action is required anyway
END
SELECT @v_targetdate = @appliedfortargetdate,
@ucount = 0,
@bolstopcount = 1,
@2nducontactdate = @v_contactdate
END
END
IF @v_apptkept = '-' AND ( @v_action LIKE '%summ%%appl%'
OR @v_action LIKE '%warrant applied%'
--OR @v_action LIKE '%induction%' --missed appt
OR @v_action LIKE '%breach%%action%%reque%'
OR @v_action LIKE '%warrant req%')
BEGIN
SELECT @ucount = 0, --reset counter
@bolproceed = 0
END
IF @2nducontactdate IS NOT null
BEGIN
IF @v_apptkept = '-' AND ( @v_action LIKE '%summ%%appl%'
OR @v_action LIKE '%warrant applied%'
--OR @v_action LIKE '%induction%' --missed appt
OR @v_action LIKE '%breach%%action%%reque%'
OR @v_action LIKE '%warrant req%')
-- AND @v_contacttype <> 'c' -- equates to a court referral
AND @v_contactdate >= @2nducontactdate
BEGIN
SELECT @bolappoccurred = 1,
@bolnoaction = 0 --action has been taken
IF @v_contactdate > @appliedfortargetdate
BEGIN
IF @bol1stfailure = 1
BEGIN
SELECT @bolreqrecord = 1,
@bol1stfailure = 0
END
END
--SELECT @bolproceed = 0
END
END
END
UPDATE t_enforcement
SET
cbolappoccurred = @v_bolappoccurred,
cbolreqrecord = @v_bolreqrecord,
cbolnewsuper = @v_bolnewsuper,
csurname = @v_surname,
cforename = @v_forename,
ccrn = @v_crn,
csupervisiontype = @v_supervisiontype,
ccommencement = @v_commencement,
ceventid = @v_eventid,
ctermdate = @v_termdate,
cordertype = @v_ordertype,
capptkept = @v_apptkept,
caction = @v_action,
ccontactdate = @v_contactdate,
ccontacttype = @v_contacttype,
cofficerid = @v_officerid,
coffsurname = @v_offsurname,
coffforename = @v_offforename,
csupervisioncount = @v_supervisioncount,
cbreachcount = @v_breachcount,
ctargetdate = @v_targetdate,
cnoactionoccurred = @v_noactionoccurred
WHERE
id = @iNextRowId
SELECT @iNextRowId = NULL,
@iNextRowId = MIN(id)
FROM t_enforcement
WHERE id > @iCurrentRowId
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
SELECT
@iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_supervisiontype = csupervisiontype,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_ordertype = cordertype,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred
FROM t_enforcement
WHERE id = @iNextRowId
END
--------------------------------------------------------------------------------------------
--insert supervision count data for each relevant po for the crystal report
INSERT INTO t_enforcement
(cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
cteamname,
cofficerid,
coffsurname,
coffforename,
csupervisioncount)
SELECT
0,
0,
0,
--0,
t.teamname,
p.personalno AS personalno,
p.surname AS offsurname,
p.forename AS offforename,
COUNT (s.eventid) AS supervisioncount
FROM
client c,
event e,
supervision s,
po p,
team t
WHERE
c.dbnumber = e.dbnumber
AND c.urn = e.urn
AND e.dbnumber = s.dbnumber
AND e.urn = s.urn
AND e.supid = s.eventid
AND p.personalno = s.suppopersonalno
AND s.teamcode = t.teamcode
AND c.dlf IS NULL --not deleted flag in client table
AND e.dlf IS NULL --not deleted flag in event table
AND s.transout IS NULL --not transferred out (of area)
AND s.supervisiontype ='01'
AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
--and s.additionalreq1 <> 'h'
AND s.commencement BETWEEN @datefrom AND @dateto
--and s.commencement between '01-sep-2002' and '01-sep-2003'
GROUP BY
t.teamname,
p.personalno,
p.surname,
p.forename
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--insert breach count data for each relevant po for the crystal report
INSERT INTO t_enforcement
(cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
cteamname,
cofficerid,
coffsurname,
coffforename,
cbreachcount)
SELECT
0,
0,
0,
--0,
cteamname,
cofficerid,
coffsurname,
coffforename,
COUNT (distinct(t_enforcement.ceventid))
FROM
t_enforcement
WHERE
cbolnewsuper = 0
GROUP BY
cteamname,
cofficerid,
coffsurname,
coffforename
--------------------------------------------------------------------------------
IF @bolCrystal = 1
SELECT
cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
csurname,
cforename,
ccrn,
cteamname,
csupervisiontype,
ccommencement,
ceventid,
ctermdate,
cordertype,
capptkept,
caction,
ccontactdate,
ccontacttype,
cofficerid,
coffsurname,
coffforename,
csupervisioncount,
cbreachcount,
ctargetdate,
cnoactionoccurred
FROM
t_enforcement e2
WHERE
(select
COUNT(*)
FROM t_enforcement
WHERE
ceventid = e2.ceventid AND cbolreqrecord = 1) >0
OR
ceventid IS NULL -- add the po and breach totals
ELSE
-------------------------------------------------------------------------------------------------
--returns for excel workbook---------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--declare @test int
--select @test = (SELECT COUNT(cnoactionoccurred) FROM t_enforcement WHERE cnoactionoccurred = 1)
SELECT SUM (csupervisioncount) AS SupervisionCount,SUM (cbreachcount) AS Breaches,
SUM(cnoactionoccurred) AS NoActiontaken,
--(SELECT COUNT(ctargetdate) FROM t_enforcement WHERE ctargetdate IS NOT NULL) as anything,
(SELECT COUNT(cbolreqrecord) FROM t_enforcement WHERE cbolreqrecord = 1) AS NotMeetingTargetCount
FROM
t_enforcement
RETURN
