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 = '<your 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 = '<html>
<head>
<style>
<!--
body {
font-family: Roboto, ''Noto Sans JP'', Arial, sans-serif;
font-size: 15;
color: #333333;}
table {
border-collapse: collapse;
font-size: 13}
table, th, td {
border: 1px solid #CCCCCC;
padding: 5px 10px 5px 10px;
text-align: left;}
.days{
text-align: center;}
tr.summary {
height: 2.8em;
background-color: #303E48;
color: white;
font-weight: bold;
border-bottom: none;}
tr.header {
height: 2em;
background-color: #eeeeee;
color: #555555;
font: bold 13px Roboto, ''Noto Sans JP'', Arial, sans-serif;}
td.days{
font-weight: bold;}
a {
color: #3668b1;}
em {
font-weight: bold;}
p.signature {
font-color: #303E48;
font-weight: bold;
font-style: italic;
font-size: 18px;
margin: 0;}
li {
margin-bottom: 10px;}
-->
</style>
</head>
<body>
<p>Hi ';
SET @emailbody += LTRIM(RTRIM(@Name));
SET @emailbody += ',</p>
<p>You have a total of <strong>';
SET @emailbody += CAST(@T AS NVARCHAR);
SET @emailbody += '</strong> overdue file(s) checked out within PDM.</p>
<table>
';
-- Populate individual details
TRUNCATE TABLE #IndividualDetails;
INSERT INTO #IndividualDetails (fileName, folder, descr, privateState, daysCheckedOut, lockDomain, ID)
SELECT [fileName], folder, descr, privateState,
daysCheckedOut, lockDomain, ROW_NUMBER() OVER (ORDER BY privateState, folder, [fileName]) AS ID
FROM #Details
WHERE userID = @UserID;
SET @j = 1;
SELECT @DetailRows = (SELECT COUNT(*) FROM #IndividualDetails);
-- Initialize variables
SELECT @Filename=[fileName], @Folder=folder, @Description=descr, @PrivateState=privateState,
@DaysCheckedOut=daysCheckedOut, @LockDomain=lockDomain
FROM #IndividualDetails
WHERE ID = @j;
-- temp for testing
--IF @i = 1
-- SELECT * FROM #IndividualDetails;
-- Handle checked out files (not private state)
IF @CO > 0 BEGIN
SET @emailbody += '<tr class="summary"><td style="border-right: none;">Checked Out: ' + CAST(@CO AS NVARCHAR) + '</td><td colspan="3" style="border-left: none; text-align: right">' + @vault + '</td></tr>
<tr class="header"><th>Filename</th><th>Folder</th><th class="days">Days</th><th>Locked On</th></tr>
';
WHILE @PrivateState = 0 AND @j <= @DetailRows BEGIN
IF(@Description is NULL)
SET @Description = '-';
IF(@LockDomain is NULL)
SET @LockDomain = '-';
SET @emailbody += '<tr><td>' + @Filename + '</td><td><a href="C:\' + @vault + '\' + @Folder + '">C:\' + @vault + @Folder + '</a></td><td class="days">' + @DaysCheckedOut + '</td><td>' + @LockDomain + '</tr>
';
SET @j = @j + 1;
SELECT @Filename=[fileName], @Folder=folder, @Description=descr, @PrivateState=privateState, @LockDomain=lockDomain,
@DaysCheckedOut=daysCheckedOut
FROM #IndividualDetails
WHERE ID = @j
END; -- End while
END; -- End check out if
-- Handle private state details
IF @PS > 0
BEGIN
SET @emailbody += '<tr class="summary"><td colspan=4>Private State: ' + CAST(@PS AS NVARCHAR) + '</td></tr>
<tr class="header"><th>Filename</th><th>Folder</th><th class="days">Days</th><th>Locked On</th></tr>
';
WHILE @j <= @DetailRows
BEGIN
SELECT @Filename=[fileName], @Folder=folder, @Description=descr, @PrivateState=privateState,
@LockDomain=lockDomain, @DaysCheckedOut=daysCheckedOut
FROM #IndividualDetails
WHERE ID = @j;
IF(@Description is NULL)
SET @Description = '-';
IF(@LockDomain is NULL)
SET @LockDomain = '-';
SET @emailbody += '<tr><td>' + @Filename + '</td><td><a href="C:\' + @vault + '\' + @Folder + '">C:\' + @vault + @Folder + '</a></td><td class="days">' + @DaysCheckedOut + '</td><td>' + @LockDomain + '</tr>
';
SET @j = @j + 1;
END; -- END while
END; -- END private state if
SET @emailbody += '</table>
';
SET @emailbody += '<p>You are receiving this email because you have one or more files that is in violation of the PDM file policy. <Some Company>''s PDM policy states that files must be checked in every <em>THREE</em> days to avoid data loss in the event of a disaster. Best practice for users is to check in all files on a daily basis. However, it if is necessary to keep checked out more than one day please select the "Keep Checked Out" option when checking in files each day prior to leaving work.</p>
';
SET @emailbody += '<ul><li>Files that are "Checked Out" are not included in any backup or replication and any changes made since last check in will be lost if <some company> were to experience the loss of your computer or a server.</li>';
SET @emailbody += '<li>Files that are in “Private State” are not included in any backup as they have not been added to the actual PDM vault and exist only in your computer’s local PDM cache. These files will be completely lost if you experience loss or failure of your computer.</li></ul>
';
SET @emailbody += '<p>Please check in any files you currently have checked out. If files are not checked in within <em>TEN</em> days you will receive a daily email until the files have been checked in. If you have issues or questions checking the files in please send an email to <some email address> and a PDM Admin will assist you.</p>
';
SET @emailbody += '<p>Thank you for your cooperation,</p><p class="signature">CAD Administration Team</p><a><some email address></a><br><br>
';
SET @emailbody += '</body></html>';
SET @EmailSubject = 'You have PDM Items Checked Out Over ' + CAST(DATEDIFF(DAY, @NewestCheckoutDate, GETDATE()) AS nvarchar(MAX)) + ' Days';
-- Temp print email body to console for testing
--PRINT @EmailSubject
--PRINT @emailbody;
-- Send Email
EXEC msdb.dbo.sp_send_dbmail @profile_name=@MailProfile,
@recipients=@Email,
@subject=@EmailSubject,
@body=@emailbody,
@body_format = 'html';
-- Log that email was delivered
IF EXISTS(SELECT UserID FROM reporting.dbo.EmailsDelivered WHERE UserID = @UserID)
BEGIN
UPDATE reporting.dbo.EmailsDelivered
SET LastEmailDate = GETDATE(), EmailCount = EmailCount + 1
WHERE UserID = @UserID;
END
ELSE
BEGIN
INSERT INTO reporting.dbo.EmailsDelivered (UserID, LastEmailDate, EmailCount)
VALUES (@UserID, GETDATE(), 1);
END;
END; -- End IF to send email
SET @i = @i + 1;
END;
-- Clean Up temp tables
DROP TABLE #Summary;
DROP TABLE #Details;
DROP TABLE #IndividualDetails;
