> Imported from Azure DevOps wiki (Products/Custodian/Custodian-Integrations.md). Generated 2026-05-05T21:22:35Z from clone /Users/ck/Sites/asp-azure-tenant/vendor/StrategicDigitalServices.wiki. > > Attachment links may still point at paths in the wiki repository; open the clone under .attachments/ if previews break.
Solution comprises 2 windows service applications, although they can also be run from the command line. Both services access Niche SQL Server database directly as at the time of development, Niche Web API did not support required functionality.
Both applications respond to service bus queue messages.
NOTE: A console program is also available for development and testing.
Expects a message that is de-serializable into a _CustodianModel_ object …
public class CustodianModel
{
public string[] CustodyReferences { get; set; }
public DateTime LastProcessedDate { get; set; }
}… and extracts all Niche custodies that are either contained in the list of _CustodyReferences_ or have been added since _LastProcessedDate_.
Niche client is called and executes …
return await dbContext.Custodies
.Where(x => (x.CustodyNumber.StartsWith("C") && x.CustodyCreated > custodianModel.LastProcessedDate) || custodianModel.CustodyReferences.Contains(x.CustodyNumber))
.OrderBy(x => x.CustodyNumber)
.ThenByDescending(x => x.OffenceId)
.ThenByDescending(x => x.DisposalDateTime)
.Distinct()
.ToArrayAsync();… to retrieve Custody records.
Niche client executes a raw SQL query to obtain the data directly from its database …
SELECT distinct
arrest.CustodyNumber AS [CustodyNumber],
custodyEvent.LawyerCallCentreRefNumber AS [SolicitorCallCentreRef],
case
when custodyEvent.ESCustOfficerSignTime is not null then custodyEvent.ESCustOfficerSignTime
else custodyEvent.StartTime
end AS [DetentionAuthorisedDateTime],
detainee.Surname_cache AS [PrisonerSurname],
detainee.Id AS [PersonId],
disposals.Id AS [DisposalId],
charge.Id AS [OffenceId],
ISNULL(report.Title, 'Pending') AS [DisposalType],
disposalsEvent.StartTime AS [DisposalDateTime],
legalDoc.CourtBailTypeG AS [BailType],
disposalsEvent.StartTime AS [TimeBailDecided],
legalDoc.GPPLDAppearanceCourt_RoomName AS [AppearanceRoom],
legalDoc.GPPLDAppearanceCourt_Time AS [AppearanceDateTime],
court.Surname_cache AS [AppearanceLocation],
ISNULL(disposals.CustodySequenceNumber, 0) AS [BailDisposal],
stdCharge.ChargeWording AS [Offences],
bailConditions.Condition AS [Conditions],
case
when email.CommAddress_CommAddress is not null then lower(email.CommAddress_CommAddress)
else ''
end AS [OICEmail],
arrestEvent.CreTime AS [CustodyCreated],
OccPersonOic.EffectiveFromTime AS [OICEffectiveDate],
courtFolder.IdNumber AS [Urn],
occurrence.OccurrenceFileNo AS [OccurrenceFileNo],
case
when supervisorEmail.CommAddress_CommAddress is not null then lower(supervisorEmail.CommAddress_CommAddress)
else ''
end AS [SupervisorEmail]
FROM dbo.VW_GPersonPolCustody arrest
LEFT JOIN dbo.VW_GPersonPolCustody primaryArrest ON primaryArrest.GPersonArrest_PrimaryArrestId = arrest.Id
LEFT JOIN dbo.VW_GPersonPoliceEvent arrestEvent ON arrestEvent.Id = arrest.Id
LEFT JOIN dbo.VW_GPPCustodyEvent custodyEvent ON custodyEvent.WId = arrestEvent.Id
LEFT JOIN dbo.VW_GOccurrence occurrence ON occurrence.Id = arrestEvent.GPPEventGOcc_RId
LEFT JOIN dbo.VW_GPersonCharge charge ON charge.GPChargeGPArrest_RId = primaryArrest.Id
LEFT JOIN dbo.VW_StandardCharge stdCharge ON stdCharge.Id = charge.GPChargeStdCharge_RId
JOIN VW_GPerson detainee ON detainee.Id = arrestEvent.WId
LEFT JOIN dbo.VW_GPPLDAssocGPersonCharge agpc ON agpc.RId = charge.Id
LEFT JOIN dbo.VW_GPersonPolASDisposition disposals ON disposals.Id = agpc.LId
LEFT JOIN dbo.VW_GPersonPoliceEvent disposalsEvent ON disposalsEvent.Id = disposals.Id
LEFT JOIN dbo.VW_GPersonPolLegalDoc legalDoc ON legalDoc.Id = disposals.Id
LEFT JOIN dbo.VW_GPersonPolReport report ON report.Id = disposals.Id
LEFT JOIN dbo.VW_GPerson court ON court.Id = legalDoc.GPPLDAppearanceCourt_RId
LEFT JOIN dbo.VW_GPPLDCondition bailConditions ON bailConditions.WId = report.Id
LEFT JOIN dbo.VW_CourtFolder courtFolder ON courtFolder.Id = report.GPersonPolReportCourtFolder_RId
LEFT JOIN dbo.VW_GOccInvGPerson OccPersonOic ON OccPersonOic.Lid = occurrence.Id
AND OccPersonOic.Classification LIKE '%OIC%'
AND (OccPersonOic.EffectiveToTime IS NULL OR OccPersonOic.EffectiveToTime >= GETDATE())
AND OccPersonOic.EffectiveFromTime = (select max(EffectiveFromTime) from VW_GOccInvGPerson where Lid = occurrence.Id AND Classification LIKE '%OIC%')
LEFT JOIN dbo.VW_GPerson OicOfficer ON OicOfficer.Id = OccPersonOic.RId
LEFT JOIN dbo.VW_GPersonAssocGAddress emailLink ON (emailLink.LId = OicOfficer.Id)
AND emailLink.EffectiveFromTime = (select max(emailLinkChk.EffectiveFromTime)
from VW_GPersonAssocGAddress emailLinkChk
join VW_GAddress emailChk ON (emailChk.Id = emailLinkChk.RId AND emailChk.EntityType = 'EMail')
where emailLinkChk.Lid = OicOfficer.Id)
LEFT JOIN dbo.VW_GAddress email ON (email.Id = emailLink.RId AND email.EntityType = 'EMail')
LEFT JOIN dbo.VW_GPerson OicSupervisor ON OicSupervisor.Id = OicOfficer.GPersonEmploySup_RId
LEFT JOIN dbo.VW_GPersonAssocGAddress supervisorEmailLink ON (supervisorEmailLink.LId = OicSupervisor.Id)
AND supervisorEmailLink.EffectiveFromTime = (select max(supEmailLinkChk.EffectiveFromTime)
from VW_GPersonAssocGAddress supEmailLinkChk
join VW_GAddress supEmailChk ON (supEmailChk.Id = supEmailLinkChk.RId AND supEmailChk.EntityType = 'EMail')
where supEmailLinkChk.Lid = OicSupervisor.Id)
LEFT JOIN dbo.VW_GAddress supervisorEmail ON (supervisorEmail.Id = supervisorEmailLink.RId AND supervisorEmail.EntityType = 'EMail')Custody Officer Logs are also required because there are instances where the OIC is to remain anonymous, which is recorded in custody logs.
So, the call to retrieve custodies results in several objects for each custody reference which must be mapped and de-duplicated, into a single object per custody reference.
The results are then sent to the Custodian web app via https in batches (so that the maximum https message length is not exceeded).
To help when problems arise, the data can be written to local files via the application’s configuration …
"Niche": {
"LastProcessedDateAdjustHours": -6,
"WriteDataModel": true,
"FilePath": "C:\\NicheData"
}… , but leaving this facility turned on will consume disk space and the files are quite large.
This application contains a number of functions where emails are sent from Custodian …
Although it is capable of using Sendgrid, the force’s exchange server is used because criminal justice email handlers restrict incoming email to certain domains.
Which of the functions is called depends upon the service bus queue used to send an appropriate message …
Received object caontains the link data and recipient.
Received object contains recipient and custody references that have received an update.
NuGet packages _PdfSharp.MigraDoc.netstandard_ & _PdfSharp.Core_ are used to generate PDFs (derived from _iTextSharp_).
It expects a single object containing a custody reference and an email address.
public class CustodyDetailRequest
{
public string Email { get; set; }
public string CustodyReference { get; set; }
}Niche client is used to retrieve 5 sections to build the report, using raw SQL queries. As with the other custodian integration, the data retrieved is mapped into more manageable objects and de-duplicated.
_Niche log data_ is a special case. Log data is stored in 2 places ! 1 place is text stored within Niche entities, however only the first 1000 characters; anything after that is truncated. Log data is also stored in a Log table as a Binary Large Object (BLOB). Each record in the log table has an indicator of the type of data the record holds. For the purposes of the Custody report, we are only interested in types labelled "_TXT;LZ77_". This indicates text data that has been compressed into the gzip file format. You can verify this by dumping the raw data into a disk file having a gzip extension, and then de-compress the file using gzip/7-zip. MS office docs also use this file format, so by experimenting with file extensions, we can discover what the log document types mean.
This is the code to de-compress gzipped log data …
public static class NicheModelExtensions
{
public static IEnumerable<CustodyLog> DecompressLogData(this IEnumerable<CustodyLog> custodyLogs)
{
foreach (var log in custodyLogs)
if (log.DocType == CustodyLogDocType.TxtLz77)
log.LogData = DecompressByteArray(log.DocData);
return custodyLogs;
}
private static string DecompressByteArray(byte[] byteArray)
{
using var from = new MemoryStream(byteArray);
using var gZipStream = new GZipStream(from, CompressionMode.Decompress);
using var streamReader = new StreamReader(gZipStream);
return streamReader.ReadToEnd();
}
}With all sections retrieved and mapped into a source object, the whole lot is passed to a static PDF generator class to create a Custody Report PDF document, the format of which is hard-coded.
At this point, we also have the option to dump the source object to disk, and use it as source data for a console test application to call the same static PDF generator class (useful when things go wrong).
Documentation is available for PdfSharp here.
Expects an _OfficerBailSummary_ object …
public class OfficerBailSummary
{
public string Email { get; set; }
public int Days { get; set; }
public IEnumerable<OffenceInfo> Offences { get; set; }
}… used to fill an html template for an email body, which is then emailed to an OIC; 1 email per OIC, potentially containing many suspect entries.