If management won't help, I'm afraid there is little you can do. We send out a report from time to time from the DB with how many files the users have checked out and for how long.
Code: Select all
SELECT USR.FullName, COUNT(doc.LockPath) as 'Checked out files'
from Documents DOC
join Users USR on USR.UserID=DOC.UserID
where LockPath != '' and DOC.Deleted=0 and DOC.UserID != 1 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000'
group by USR.Fullname
go
SELECT DOC.Filename
,USR.FullName
,DOC.LockDomain as Computer
,DOC.LockPath as Path
,DOC.LatestRevisionNo as Version
,STA.Name as State
,DOC.LockDate
FROM Documents DOC
join Users USR on USR.UserID=DOC.UserID
join Status STA on STA.StatusID=DOC.CurrentStatusID
where doc.LockDomain != '' and DOC.Deleted=0 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000' --Object type 1 is normal file, 2 is virtual
--order by LockDate
order by USR.FullName,DOC.Filename
go
That's close to a sql command we have in a C# program that spits out an html doc, sort of a wall of shame. We included the lock date to get the longest checked out file and the mean days checked out. We started with API calls, first searched by files locked by users, that didn't work very well. So switched to get the list of locked files from vault object and work that by user. That was better but we could not find any way to get the lock date from API. So we went to the trusty SQL query method and now have number of files locked, longest file locked for and mean days locked.jcapriotti wrote: ↑Thu Sep 23, 2021 2:24 pm Here's some sql code to build a list
Code: Select all
SELECT USR.FullName, COUNT(doc.LockPath) as 'Checked out files' from Documents DOC join Users USR on USR.UserID=DOC.UserID where LockPath != '' and DOC.Deleted=0 and DOC.UserID != 1 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000' group by USR.Fullname go SELECT DOC.Filename ,USR.FullName ,DOC.LockDomain as Computer ,DOC.LockPath as Path ,DOC.LatestRevisionNo as Version ,STA.Name as State ,DOC.LockDate FROM Documents DOC join Users USR on USR.UserID=DOC.UserID join Status STA on STA.StatusID=DOC.CurrentStatusID where doc.LockDomain != '' and DOC.Deleted=0 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000' --Object type 1 is normal file, 2 is virtual --order by LockDate order by USR.FullName,DOC.Filename go
Have those who can't be bothered have their entire access be managed by you and you only? If they need files, they ask you, you give them the file, they do their save and then give it back to you..?bnemec wrote: ↑Mon Mar 29, 2021 1:34 pm All users are a bit different. Some are paranoid of loosing data and will check in the file every time they accomplish something. Some don't bother checking in the first time until their done with it. Some respond to reminders, others are too busy. Anyway, I'd like to set up notification to users that have files in the elusive <private state>, Since it's not actually a state there's no way to do it out of the box. Which really stinks in my mind as it's one of the worst "states" a file could be left in. Either delete it or check it in, right?
So what are other people using to nag those users that are "too busy" to clean up or just don't think it's that important for whatever reason? Hoping for an existing tool for this in PDM that's not a cobbled box of work arounds. Thanks.
image.png
I didn't even consider going to this kind of extremes. We jumped from SE to SW and from NFS to PDM at the same time and the way it was done felt, to me at least, like replacing the outboard and transom in a boat. While it was in the water, not the marina, but like the middle of Lake Mich, with the usual 3 foot rollers. People already hate me for the mess, especially sales when every request started taking literally two to three times longer. I was in no position to hold up production on principle. Those kinds of rules, although they sound like fun, just weren't practical. We couldn't stop all progress until everyone understood how to use SW and PDM "properly". Heck, back when I posted this, march '21, we were 16 months since go live and our SW & PDM task force was still trying to figure out our best practices.AlexLachance wrote: ↑Wed Jul 13, 2022 9:09 am Have those who can't be bothered have their entire access be managed by you and you only? If they need files, they ask you, you give them the file, they do their save and then give it back to you..?
After a few days of having to bother themselves to ask you for access to save files, they should most likely agree to take care of the file state management properly.
Edit: Geez, I'm late to the party
Perhaps then, identify times that they have been 'bitten' by the file being in the wrong state and show them the benefits to managing the states properly..?bnemec wrote: ↑Wed Jul 13, 2022 9:57 am I didn't even consider going to this kind of extremes. We jumped from SE to SW and from NFS to PDM at the same time and the way it was done felt, to me at least, like replacing the outboard and transom in a boat. While it was in the water, not the marina, but like the middle of Lake Mich, with the usual 3 foot rollers. People already hate me for the mess, especially sales when every request started taking literally two to three times longer. I was in no position to hold up production on principle. Those kinds of rules, although they sound like fun, just weren't practical. We couldn't stop all progress until everyone understood how to use SW and PDM "properly". Heck, back when I posted this, march '21, we were 16 months since go live and our SW & PDM task force was still trying to figure out our best practices.
It has just taken time, lots of time, for users to settle into the new system.
Yep, we did this,. Some got it right away, with others it was similar to explaining why we don't lean on the electric fence. But like explaining it to someone from the 15th century. The look was, "what is this electricity you speak of" == "What is a state? Is that like whether it's checked in or not?"AlexLachance wrote: ↑Wed Jul 13, 2022 10:37 am Perhaps then, identify times that they have been 'bitten' by the file being in the wrong state and show them the benefits to managing the states properly..?
Well, the file is saved to local disk it's not easy to lose work. The problem has proved more to be in users not understanding what versions are than the state the file is in. Also, <Private State> isn't really a state, the file has been added to the vault but never checked in. I call it the twilight zone where the file is vaulted but it's not; as far as user's concerned it not in vault so no one else can access the file.AlexLachance wrote: ↑Wed Jul 13, 2022 10:37 am Sounds to me like there are a lot of chances for things to get 'lost' from not getting saved because of improper file management, but I've never worked with PDM, so I could be wrong.
Oh yeah, screen shot of the workflow. The first workflow as originally set up turned out to be no good, WAY too complicated. Never should have tried to involve other departments. Since the actual part life process is not well defined the workflow of the file that represents the part became a rat's nest. It became apparent that we were trying to model an undefined PLM process in a file management workflow and that was not going to work so we actually abandoned the first workflow and moved into a simplified one. All these on-the-fly changes in the implementation kept the users confused.AlexLachance wrote: ↑Wed Jul 13, 2022 10:37 am Do you have a graphic that explains the proper file management process so that people can understand it properly..?
Sounds like fun, even though it is a lot of "problem solving", I love that.bnemec wrote: ↑Wed Jul 13, 2022 11:56 am Yep, we did this,. Some got it right away, with others it was similar to explaining why we don't lean on the electric fence. But like explaining it to someone from the 15th century. The look was, "what is this electricity you speak of" == "What is a state? Is that like whether it's checked in or not?"
Well, the file is saved to local disk it's not easy to lose work. The problem has proved more to be in users not understanding what versions are than the state the file is in. Also, <Private State> isn't really a state, the file has been added to the vault but never checked in. I call it the twilight zone where the file is vaulted but it's not; as far as user's concerned it not in vault so no one else can access the file.
Oh yeah, screen shot of the workflow. The first workflow as originally set up turned out to be no good, WAY too complicated. Never should have tried to involve other departments. Since the actual part life process is not well defined the workflow of the file that represents the part became a rat's nest. It became apparent that we were trying to model an undefined PLM process in a file management workflow and that was not going to work so we actually abandoned the first workflow and moved into a simplified one. All these on-the-fly changes in the implementation kept the users confused.
Thank you very much for sharing this. I am not very skilled at SQL and I was looking for something like this that would email individual users regarding checked out files. I am decent at augmenting scripts that have already been written though
Can anyone help me? I'm fairly good at editing things like this. I just need a little guidance to get started. Where does this SQL go? Does it go on the server using the Management Studio? And how do I make thejamey270 wrote: ↑Tue Jul 12, 2022 11:14 pm 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;
Can anyone help me? I'm fairly good at editing things like this. I just need a little guidance to get started. Where does this SQL go? Does it go on the db server using the Management Studio? And how do I make the reporting.dbo.EmailsDelivered table? Just make a blank table with that name? And save the query above? Thank you!jamey270 wrote: ↑Tue Jul 12, 2022 11:14 pm 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;
