I have a SQL script that runs each day that sends an email to users that have files checked out greater than 3 days. If you use this as shown below you will need to create a new table to log when emails are sent. Mine is listed as reporting.dbo.EmailsDelivered below.
/*
Email Checked Out Summary
This script looks for checked out files in PDM that have been checked out for a set amount of days
and emails a list with the location and description to each user.
Created by Jamey Evans 07/11/2022
*/
DECLARE
@MailProfile NVARCHAR(MAX),
@EmailSubject NVARCHAR(MAX),
@DescriptionVariableID DECIMAL,
@ConfigurationID DECIMAL,
@vault NVARCHAR(MAX),
@MinDays int;
– Customisation
– Database
SET @vault = ‘’
USE [your vault database]
– Mail Profile
SET @MailProfile = ‘SolidWorks_Email_Send’;
– Description VariableID - this is the variable ID (from the “Variables”
– table) that will populate the Description column
SET @DescriptionVariableID = 49;
– Default configurationID - the description must be pulled from a specific configuration
SET @ConfigurationID = 2;
– Min number of days file has been checked out
SET @MinDays = 3;
– For summary query
DECLARE
@Name NVARCHAR(MAX),
@Email NVARCHAR(MAX),
@UserID DECIMAL,
@CO DECIMAL,
@PS DECIMAL,
@T DECIMAL,
@OldestCheckoutDate DATETIME,
@NewestCheckoutDate DATETIME,
@LastEmailDate DATETIME,
@SendEmail BIT;
– For detail query
DECLARE
@Filename NVARCHAR(MAX),
@Folder NVARCHAR(MAX),
@Description NVARCHAR(MAX),
@PrivateState BIT,
@DaysCheckedOut NVARCHAR(MAX),
@LockDomain NVARCHAR(MAX);
DECLARE
@emailbody NVARCHAR(MAX);
DROP TABLE IF EXISTS #Summary;
CREATE TABLE #Summary
(
name NVARCHAR(MAX),
email NVARCHAR(MAX),
userID DECIMAL,
checkedOut DECIMAL,
privateState DECIMAL,
total DECIMAL,
oldestCheckoutDate DATETIME,
newestCheckoutDate DATETIME,
lastEmailDate DATETIME,
ID int
);
DROP TABLE IF EXISTS #Details;
CREATE TABLE #Details
(
userID INT,
lockDomain NVARCHAR(255),
[fileName] NVARCHAR(MAX),
folder NVARCHAR(MAX),
descr NVARCHAR(MAX),
privateState BIT,
daysCheckedOut INT,
tableID BIGINT
);
DROP TABLE IF EXISTS #IndividualDetails;
CREATE TABLE #IndividualDetails
(
[fileName] NVARCHAR(MAX),
folder NVARCHAR(MAX),
descr NVARCHAR(MAX),
privateState BIT,
daysCheckedOut NVARCHAR(MAX),
lockDomain NVARCHAR(255),
ID INT
);
INSERT INTO #Summary
SELECT
CASE WHEN CHARINDEX(‘,’, U.FullName) > 0 THEN SUBSTRING(U.FullName, CHARINDEX(‘,’, U.FullName)+2, LEN(U.FullName) - CHARINDEX(‘,’, U.FullName)-1)
ELSE SUBSTRING(U.FullName, 1, CHARINDEX(’ ', U.FullName)) END AS ‘Name’,
U.[Email],
U.[UserID],
Count([DocTypeID]) AS ‘Checked Out’,
Count() - Count([DocTypeID]) AS ‘Private State’,
Count() AS ‘Total’,
MIN(D.lockdate) AS OldestCheckOutDate,
MAX(D.lockdate) AS NewestCheckOutDate,
E.LastEmailDate,
ROW_NUMBER() OVER (ORDER BY U.UserID) as ID
FROM [Documents] AS D
JOIN [Users] AS U ON U.[UserID] = D.[UserID]
LEFT JOIN reporting.dbo.EmailsDelivered E ON E.UserID = D.UserID
WHERE D.[Deleted] = 0 and D.[UserID] <> 1 AND D.[ObjectTypeID] = 1 AND DATEDIFF(DAY, D.lockdate, GETDATE()) > @MinDays
GROUP BY U.Email, U.FullName, U.UserID, E.LastEmailDate;
– Get Detail Data
INSERT INTO #Details (userID, lockDomain, fileName, folder, descr, privateState, daysCheckedOut, tableID)
SELECT
Q.UserID,
Q.LockDomain,
Q.Filename,
P.Path AS ‘folder’,
VV2.ValueText AS ‘description’,
Q.PrivateState,
Q.DaysCheckedOut,
ROW_NUMBER() OVER (ORDER BY Q.UserID) AS tableID
FROM (
SELECT
D.DocumentID,
D.UserID,
D.Filename,
D.LockDomain,
CAST(COALESCE(DATEDIFF(DAY, D.LockDate, GETDATE()), 0) AS INT) AS ‘DaysCheckedOut’,
MAX(VV.RevisionNo) AS ‘MaxRev’,
CAST (
CASE
WHEN D.DocTypeID is null
THEN 1
ELSE 0
END AS BIT) AS ‘PrivateState’
FROM [Documents] AS D
LEFT OUTER JOIN [VariableValue] as VV
ON VV.DocumentID = D.DocumentID
AND VV.[VariableID] = @DescriptionVariableID
AND VV.[ConfigurationID] = @ConfigurationID
WHERE
D.[Deleted] = 0
AND D.ObjectTypeID = 1
–AND D.[UserID] = @UserID
AND DATEDIFF(DAY, D.LockDate, GETDATE()) > @MinDays
GROUP BY
D.DocumentID,
D.[Filename],
D.DocTypeID,
D.LockDate,
D.LockDate,
D.UserID,
D.LockDomain
) AS Q
INNER JOIN #Summary S on S.userID = Q.UserID
JOIN [DocumentsInProjects] AS DiP
ON DiP.DocumentID = Q.DocumentID
JOIN [Projects] AS P
ON P.ProjectID = DiP.ProjectID
LEFT OUTER JOIN [VariableValue] AS VV2
ON VV2.DocumentID = Q.DocumentID
AND VV2.ConfigurationID = @ConfigurationID
AND VV2.VariableID = @DescriptionVariableID
AND VV2.RevisionNo = Q.MaxRev;
– Temp for testing
–SELECT * FROM #Summary ORDER BY ID asc;
–SELECT * FROM #Details ORDER BY tableID asc;
DECLARE @SummaryRows INT = (SELECT COUNT(*) FROM #Summary), @i INT = 1, @DetailRows INT, @j INT = 1;
WHILE @i <= @SummaryRows
BEGIN
SELECT @Name=[name], @Email=email, @UserID=userID, @CO=checkedOut, @PS=privateState,
@T=total, @OldestCheckoutDate=oldestCheckoutDate, @NewestCheckoutDate=NewestCheckoutDate, @LastEmailDate=lastEmailDate
FROM #Summary
WHERE ID = @i;
– Check if we should send email or not
– Send email after 3 days then send another at 5 days; Send daily after 10 days
SET @SendEmail = 0;
IF coalesce(@LastEmailDate, ‘2000-01-01’) < @OldestCheckoutDate
BEGIN
SET @SendEmail = 1;
END;
– if files have been checked out >= 10 days then send email every day
IF DATEDIFF(DAY, @OldestCheckoutDate, GETDATE()) >= 10 AND @SendEmail = 0
BEGIN
SET @SendEmail = 1;
END;
– Otherwise if it has been 5 days since we sent the last email then send email
IF DATEDIFF(DAY, @LastEmailDate, GETDATE()) >= 5 AND @SendEmail = 0
BEGIN
SET @SendEmail = 1;
END;
IF @SendEmail = 1
BEGIN – Begin IF @SendEmail
– Summary operations
– Set email body
IF(@Description is null)
SET @Description = ‘-’;
SET @emailbody = '