Code

Automating Associate Access Profile Imports With SQL, Batch Files, and Bash

18 min read

One of our integration customers was running in such a way that associates would come in for a short stint, do their thing, and then run off to another facility, potentially never to be seen again.  This meant that I was constantly updating associate access for the RFID cage running at this customer's facility.  And when I say constantly, I mean almost every day.

They would provide a list of associates who should have access with the understanding that if an associate was not on the list, they were not to have access to the RFID cage.  So I basically worked out a process where I would compare the customer's daily access list to the current access list inside of the CribMaster database and remove anyone who needed removed and add anyone that needed added.  

CribMaster has an import tool that lets you setup templates and rule files to allow for batch importing new associates, creating new departments, making changes to existing associates, etc.  So I found myself spending a lot of time just filling out these templates with the results of my access list comparisons.  It got to the point where I would only do it every two or three days as it was just too much to do every day.

"We can't keep living like this!"

So, using the knowledge and the tools I had at my disposal, over the course of a few weeks, I worked through a flow that completely automated the process and took it almost completely off of my plate, except for checking in now and then to make sure things were running smoothly.

The Flow

  1. Grab the latest access file from the customer's FTP server and ingest it into our database via a quick SQL stored procedure run via SQLCMD in a batch file scheduled in Windows Task Scheduler.
  2. Run that customer access list through another SQL stored procedure that would compare the new list to the existing access list and insert various flags that would indicate what would need to happen with each associate.  
    The scenarios being: Removing associate access, adding new associates, updating an associate's badge number, updating an associate's name, updating an associate's ID, updating an associate's department, and if that department did not exist yet, creating it - while also doing error checks to make sure the data was good before proceeding (for example, making sure a new associate's badge number was not already assigned to another associate already in the system...that sort of thing).
  3. Have Windows Task Scheduler run a batch file that would use SQLCMD to execute queries that would use the flags generated in the previous step to generate .csv files that conformed the to templates and rule files CribMaster was expecting.
  4. Finally, have a Bash script running on a Debian VM running on our hypervisor grab these .csv files, chug through them to determine if they even contained data, and transfer the ones that did to our remote CribMaster server via an FTP folder mounted inside the folder where the CribMaster agent was waiting to ingest them and perform whatever operations were prescribed by rule files I had setup beforehand.  The script would archive the .csv files generated during the process and send out an email indicating success or failure as well as listing what scenarios or 'jobs' were done.  Still learning Bash!
  5. Get other work done now that this huge mess was being handled by multiple computers doing this work for me :D

SQL

USE [REPORTINGSERVER]
GO
/****** Object:  StoredProcedure [dbo].[CustomerCurrentAccessListGetterAndDumper]    Script Date: 3/1/2022 1:16:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Philip Barrowcliffe
-- Create date: 04/16/2020
-- Description:	Automating the dumping of the Customer access list FTP upload data into our database for further use.
-- =============================================
ALTER PROCEDURE [dbo].[CustomerCurrentAccessListGetterAndDumper]



AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	CREATE TABLE #LIST
	(
		[empid] NVARCHAR(MAX),
		[badgeid] NVARCHAR(MAX), 
		[CostCenter] NVARCHAR(MAX),
		[FirstName] NVARCHAR(MAX),
		[LastName] NVARCHAR(MAX)
	) 


	BULK INSERT #LIST
	FROM '\\path/to\PPECageEmployee.csv'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '0x0a',
		FIRSTROW = 2
	)

	TRUNCATE TABLE [REPORTINGSERVER].[dbo].[AccessImport]

	INSERT INTO [REPORTINGSERVER].[dbo].[AccessImport]
	SELECT [empid],[badgeid],[CostCenter],[FirstName],[LastName],GETDATE()
	FROM #LIST

END

USE [REPORTINGSERVER]
GO
/****** Object:  StoredProcedure [dbo].[CustomerAccessImportStarter_forTelerik]    Script Date: 3/1/2022 1:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Scott Barrowcliffe
-- Create date: 03272020
-- Description:	Attempting to make my life easier by having sql crunch most of the Customer associate access import for me
-- =============================================
ALTER PROCEDURE [dbo].[CustomerAccessImportStarter] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT *
INTO #CMEmployee
FROM
	(
		SELECT
			ID,
			LastName,
			FirstName,
			Name,
			BadgeNumber,
			User1,
			EmployeeInactive,
			EmployeeSiteID
		FROM
			REDACTED.CM01.dbo.EMPLOYEE
		WHERE EmployeeSiteID = 'Customer'
	) AS CMEmp


SELECT *
INTO #CMDEPT
FROM
	(
		SELECT DISTINCT ID
		FROM REDACTED.CM01.dbo.User1
		WHERE UserSiteID = 'Customer'
	) AS cmdept
		
		
SELECT *
INTO #CurrentImport
FROM
	(
		SELECT
			empid,
			badgeid,
			CostCenter,
			LastName,
			FirstName,
			LastName + ', ' + FirstName AS Name
		FROM
			(
				SELECT
					'PRE' + SUBSTRING(empid, PATINDEX('%[^0]%', empid + '.'), LEN(empid)) AS empid,
					badgeid,
					CostCenter,
					dbo.f_ProperCase(LastName) AS LastName,
					dbo.f_ProperCase(FirstName) AS FirstName
				FROM
					dbo.[AccessImport]
			) AS STUFF
	) AS CurImp
			

SELECT *
INTO #IDCHECK
FROM
	(
		SELECT
			CUSTOMER.empid,
			CM.ID,
			CASE WHEN CM.ID IS NULL THEN 'NEW' ELSE 'EXISTS' END AS Status
		FROM
			#CurrentImport AS CUSTOMER LEFT OUTER JOIN
			(
				SELECT ID
				FROM #CMEmployee
			) AS CM ON CUSTOMER.empid = CM.ID	
	) AS IDChk


SELECT *
INTO #NAMECHECK
FROM
	(
		SELECT
			empid,
			FirstName,
			LastName,
			Name,
			MAX(Status) AS Status,
			CurrentCMIDbyName,
			CurrentCMFirstName,
			CurrentCMLastName,
			CurrentCMName,
			CurrentCMSiteID,
			CurrentCMActiveStatus
		FROM 
			(
				SELECT
					CUSTOMER.empid,
					CUSTOMER.FirstName,
					CUSTOMER.LastName,
					CUSTOMER.Name,
					CASE 
						WHEN NAME.Name IS NULL 
						THEN 'NEW' 
						ELSE 'EXISTS' 
						END AS Status,
					CASE 
						WHEN (
								CASE 
									WHEN NAME.Name IS NULL THEN 'NEW' 
									ELSE 'EXISTS' 
									END
							) = 'NEW' THEN CMID.ID 
						ELSE CMNAME.ID 
						END AS CurrentCMIDbyName,
					CASE 
						WHEN (
								CASE 
									WHEN NAME.Name IS NULL THEN 'NEW' 
									ELSE 'EXISTS' END
							) = 'NEW' THEN CMID.FirstName 
						ELSE CMNAME.FirstName 
						END AS CurrentCMFirstName,
					CASE 
						WHEN (
								CASE WHEN NAME.Name IS NULL THEN 'NEW' 
								ELSE 'EXISTS' 
								END
							) = 'NEW' THEN CMID.LastName 
						ELSE CMNAME.LastName 
						END AS CurrentCMLastName,
					CASE 
						WHEN (
								CASE WHEN NAME.Name IS NULL THEN 'NEW' 
								ELSE 'EXISTS' 
								END
							) = 'NEW' THEN CMID.Name 
						ELSE CMNAME.Name 
						END AS CurrentCMName,
					CASE 
						WHEN (
								CASE WHEN NAME.Name IS NULL THEN 'NEW' 
								ELSE 'EXISTS' 
								END
							) = 'NEW' THEN CMID.EmployeeSiteID 
						ELSE CMNAME.EmployeeSiteID 
						END AS CurrentCMSiteID,
					CASE 
						WHEN (
								CASE WHEN NAME.Name IS NULL THEN 'NEW' 
								ELSE 'EXISTS' 
								END
							) = 'NEW' THEN CMID.CurrentCMActiveStatus 
						ELSE CMNAME.CurrentCMActiveStatus 
						END AS CurrentCMActiveStatus
				FROM
					#CurrentImport AS CUSTOMER LEFT OUTER JOIN
					(
						SELECT	Name
						FROM #CMEmployee
					) AS NAME ON CUSTOMER.Name COLLATE SQL_Latin1_General_CP1_CI_AS = NAME.Name LEFT OUTER JOIN
					(
						SELECT
							ID,
							FirstName,
							LastName,
							Name,
							EmployeeSiteID,
							CASE 
								WHEN EmployeeInactive = 0 THEN 'ACTIVE' 
								WHEN EmployeeInactive = 1 THEN 'INACTIVE' 
								ELSE '' 
								END AS CurrentCMActiveStatus
						FROM #CMEmployee
					) AS CMNAME ON CMNAME.Name = CUSTOMER.Name COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
						(
							SELECT
								ID,
								FirstName,
								LastName,
								Name,
								EmployeeSiteID,
								CASE 
									WHEN EmployeeInactive = 0 THEN 'ACTIVE' 
									WHEN EmployeeInactive = 1 THEN 'INACTIVE' 
									ELSE '' 
									END AS CurrentCMActiveStatus
							FROM
								#CMEmployee
						) AS CMID ON CMID.ID = CUSTOMER.empid
			) AS BLAH
			GROUP BY empid, FirstName, LastName, Name, CurrentCMIDbyName, CurrentCMFirstName, CurrentCMLastName, CurrentCMName, CurrentCMSiteID, CurrentCMActiveStatus
	) AS NmChk
	

SELECT *
INTO #BADGECHECK
FROM
	(
		SELECT
			CUSTOMER.empid,
			CUSTOMER.badgeid,
			CASE 
				WHEN BADGE.BadgeNumber IS NULL THEN 'NEW' 
				ELSE 'EXISTS' 
				END AS Status, 
			CM.BadgeNumber AS [CM BadgeNumber],
			CASE 
				WHEN CUSTOMER.badgeid = CM.BadgeNumber THEN '' 
				ELSE 'NOPE' 
				END AS [Badge Match]
		FROM
			#CurrentImport AS CUSTOMER LEFT OUTER JOIN
			(
				SELECT BadgeNumber
				FROM #CMEmployee
			) AS BADGE ON CUSTOMER.badgeid = BADGE.BadgeNumber LEFT OUTER JOIN
			(
				SELECT
					ID,
					BadgeNumber
				FROM #CMEmployee
			) AS CM ON CM.ID = CUSTOMER.empid
		GROUP BY CUSTOMER.empid, CUSTOMER.badgeid, CASE WHEN BADGE.BadgeNumber IS NULL THEN 'NEW' ELSE 'EXISTS' END, CM.BadgeNumber
	) AS BdgChk



SELECT *
INTO #DEPTCHECK
FROM
	(
		SELECT
			CUSTOMER.CostCenter
			,CM.ID
			,CASE WHEN CM.ID IS NULL THEN 'NEW' ELSE 'EXISTS' END AS [Status]
		FROM
			#CurrentImport AS CUSTOMER LEFT OUTER JOIN
			(
				SELECT ID
				FROM #CMDEPT
			) AS CM ON CUSTOMER.CostCenter = CM.ID	
	) AS deptcheck



SELECT *
INTO #ACCESSNOW
FROM
	(
		SELECT
			EMP.ID,
			EMP.FirstName,
			EMP.LastName,
			EMP.LastName + ', ' + EMP.FirstName AS FullName,
			EMP.User1 AS Department,
			EMP.EmployeeSiteID,
			CAST(CRIB.Crib AS NVARCHAR) AS Crib
		FROM
			REDACTED.CM01.dbo.EMPLOYEE AS EMP INNER JOIN
			REDACTED.CM01.dbo.VEmployeeSiteCrib AS CRIB ON EMP.ID = CRIB.EMPLOYEEID
		WHERE
			(EMP.EmployeeInactive = 0) 
			AND (CRIB.CribAccessOption = 1) 
			AND (CRIB.Crib = '61') 
			AND (EMP.EmployeeSiteID = 'Customer')
	) AS AccNow


SELECT *
INTO #ACCESSCOMPARISON
FROM
	(
		SELECT
			AccNow.ID,
			AccNow.FullName,
			ISNULL(CI.empid, N'') AS empid,
			CASE 
				WHEN CI.empid IS NULL THEN 'TO REMOVE' 
				ELSE 'ACTIVE' 
				END AS Status
		FROM
			#ACCESSNOW AS AccNow LEFT OUTER JOIN
			#CurrentImport AS CI ON CI.empid = AccNow.ID
	) AS AccComp
	
	
	
SELECT *
INTO #MAINCHUNK
FROM	
	(
		SELECT
			CUSTOMER.empid AS [CUSTOMER empid],
			CUSTOMER.badgeid AS [CUSTOMER badgeid],
			CUSTOMER.CostCenter AS [CUSTOMER CostCenter],
			CUSTOMER.FirstName AS [CUSTOMER FirstName],
			CUSTOMER.LastName AS [CUSTOMER LastName],
			CUSTOMER.Name AS [CUSTOMER Name], 
			IDCHECK.Status AS [ID Status],
			NAMECHECK.Status AS [Name Status],
			BADGECHECK.Status AS [Badge Status],
			BADGECHECK.[Badge Match],
			DEPTCHECK.Status AS [Dept Status],
			ISNULL(NAMECHECK.CurrentCMIDbyName, N'') AS CurrentCMIDbyName,
			ISNULL(NAMECHECK.CurrentCMFirstName, N'') AS CurrentCMFirstName,
			ISNULL(NAMECHECK.CurrentCMLastName, N'') AS CurrentCMLastName,
			ISNULL(NAMECHECK.CurrentCMName, N'') AS CurrentCMName,
			ISNULL(BADGECHECK.[CM BadgeNumber], N'') AS CurrentCMBadgeNumber,
			ISNULL(NAMECHECK.CurrentCMSiteID, N'') AS CurrentCMSiteID,
			ISNULL(NAMECHECK.CurrentCMActiveStatus, N'') AS CurrentCMActiveStatus
		FROM
			#CurrentImport AS CUSTOMER LEFT OUTER JOIN
			#NAMECHECK AS NAMECHECK ON CUSTOMER.empid = NAMECHECK.empid LEFT OUTER JOIN
			#IDCHECK AS IDCHECK ON IDCHECK.empid = CUSTOMER.empid LEFT OUTER JOIN
			#BADGECHECK AS BADGECHECK ON BADGECHECK.badgeid = CUSTOMER.badgeid LEFT OUTER JOIN
			#DEPTCHECK AS DEPTCHECK ON DEPTCHECK.CostCenter = CUSTOMER.CostCenter 
	) AS MainChunk

	
	TRUNCATE TABLE [REPORTINGSERVER].[dbo].[AccessImport_Daily]

	INSERT INTO [REPORTINGSERVER].[dbo].[AccessImport_Daily]
	SELECT *
	FROM (
			SELECT 
				[CUSTOMER empid],
				[CUSTOMER badgeid],
				[CUSTOMER CostCenter],
				[CUSTOMER FirstName],
				[CUSTOMER LastName],
				[CUSTOMER Name],
				[ID Status],
				[Name Status],
				[Badge Status],
				[Badge Match],
				[Dept Status],
				[Dept Match],
				CurrentCMIDbyName,
				CurrentCMFirstName,
				CurrentCMLastName,
				CurrentCMName,
				CurrentCMSiteID,
				CurrentCMBadgeNumber,
				CurrentCMActiveStatus,
				[Current Badge Holder CM ID],
				[Current Badge Holder FirstName],
				[Current Badge Holder LastName],
				[Current Badge Holder Name],
				[Current Badge Holder SiteID],
				[Current Badge Holder Access Status],
				GETDATE() AS importDate
			FROM
				(
					SELECT
						MC.[CUSTOMER empid],
						MC.[CUSTOMER badgeid],
						MC.[CUSTOMER CostCenter],
						MC.[CUSTOMER FirstName],
						MC.[CUSTOMER LastName],
						MC.[CUSTOMER Name],
						MC.[ID Status],
						MC.[Name Status],
						MC.[Badge Status],
						MC.[Badge Match],
						MC.[Dept Status],
						CASE WHEN MC.[CUSTOMER CostCenter] = CM.User1 THEN '' ELSE 'NOPE' END AS [Dept Match],
						MC.CurrentCMIDbyName,
						MC.CurrentCMFirstName,
						MC.CurrentCMLastName,
						MC.CurrentCMName,
						MC.CurrentCMSiteID,
						MC.CurrentCMBadgeNumber,
						MC.CurrentCMActiveStatus,
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN CM.ID 
								ELSE '' 
								END,'') AS [Current Badge Holder CM ID],
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN CM.FirstName 
								ELSE '' 
								END,'') AS [Current Badge Holder FirstName],
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN CM.LastName 
								ELSE '' 
								END,'') AS [Current Badge Holder LastName],
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN CM.Name 
								ELSE '' 
								END,'') AS [Current Badge Holder Name],
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN CM.EmployeeSiteID 
								ELSE '' 
								END,'') AS [Current Badge Holder SiteID],
						ISNULL(CASE 
								WHEN MC.[Badge Match] = 'NOPE' THEN ACN.Status 
								ELSE '' 
								END,'') AS [Current Badge Holder Access Status]
					FROM 
						#MAINCHUNK AS MC LEFT OUTER JOIN
						#CMEmployee AS CM ON MC.[CUSTOMER badgeid] = CM.BadgeNumber LEFT OUTER JOIN
						#ACCESSCOMPARISON AS ACN ON CM.ID = ACN.ID
				)
			GROUP BY [CUSTOMER empid],[CUSTOMER badgeid],[CUSTOMER CostCenter],[CUSTOMER FirstName],[CUSTOMER LastName],[CUSTOMER Name],[ID Status],[Name Status],[Badge Status],[Badge Match],[Dept Status],[Dept Match],CurrentCMIDbyName,CurrentCMFirstName,CurrentCMLastName,CurrentCMName,CurrentCMSiteID,CurrentCMBadgeNumber,CurrentCMActiveStatus,[Current Badge Holder CM ID],[Current Badge Holder FirstName],[Current Badge Holder LastName],[Current Badge Holder Name],[Current Badge Holder SiteID],[Current Badge Holder Access Status]
	) AS FINAL
END

Scheduled Batch Jobs

SQLCMD -Q "exec CustomerAccessImportStarter" -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass -o C:\CustomerAccessImportStarter.txt

SQLCMD -Q "exec CustomerAccessListGetterAndDumper" -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass -o C:\CustomerAccessListGetterAndDumper.txt
@ECHO OFF

:: REMOVE
:: Part 1
set "OutputFile=C:\Output\Remove01MakeInactive.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,Inactive' set nocount on; SELECT [ID], '1' AS [Inactive] FROM [vw_Integration_CUSTOMERAccessComparison] WHERE [Status] = 'TO REMOVE' AND LEFT([ID],5) <> 'Customer'" -o %OutputFile% -s "," -W -h -1

:: Part 2
set "OutputFile=C:\Output\Remove02BadgeQuery.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID' set nocount on; SELECT [ID] FROM [vw_Integration_CUSTOMERAccessComparison] WHERE [Status] = 'TO REMOVE' AND LEFT([ID],5) <> 'Customer'" -o %OutputFile% -s "," -W -h -1


:: AddNewDepts
set "OutputFile=C:\Output\AddNewDepts01.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,UserSiteID,UDFAllDeptOptionCompatible' SET NOCOUNT ON; SELECT DISTINCT [CUSTOMER CostCenter],'CUSTOMER' AS UserSiteID,'YES' AS UDFAllDeptOptionCompatible FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [Dept Status] = 'NEW'" -o %OutputFile% -s "," -W -h -1


:: UpdateAssociateDepts
:: Part 1
set "OutputFile=C:\Output\UpdateAssociateDept01User1.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,Dept' set nocount on; SELECT [CUSTOMER empid],[CUSTOMER CostCenter] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [Dept Match] = 'NOPE'" -o %OutputFile% -s "," -W -h -1

:: Part 2
set "OutputFile=C:\Output\UpdateAssociateDept02UserXRef.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,Dept' set nocount on; SELECT [CUSTOMER empid],[CUSTOMER CostCenter] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [Dept Match] = 'NOPE'" -o %OutputFile% -s "," -W -h -1


:: NewAssociate
:: Part 1
set "OutputFile=C:\Output\NewAssociate01NewAssociate.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'EmployeeNumber,FirstName,LastName,Name,AccessCode,BadgeNumber,EmployeeSiteID,UDFAllDeptsOption,UDFRFIDTrainingDate' set nocount on; SELECT [CUSTOMER empid], [CUSTOMER FirstName], [CUSTOMER LastName], '""' + cast ([CUSTOMER Name] as nvarchar(max)) + '""' As [CUSTOMER Name], 'A' AS AccessCode,[CUSTOMER badgeid], 'CUSTOMER' AS EmployeeSiteID,'NO' AS UDFAllDeptsOption,'' AS UDFRFIDTrainingDate FROM dbo.[AccessImport_Daily] WHERE (([ID Status] = 'NEW') AND ([Name Status] = 'NEW')) OR (([ID Status] = 'NEW') AND ([Name Status] = 'EXISTS'))" -o %OutputFile% -s "," -W -h -1

:: Part 2
set "OutputFile=C:\Output\NewAssociate02UserXRef.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,Dept' set nocount on; SELECT [CUSTOMER empid],[CUSTOMER CostCenter] FROM dbo.[AccessImport_Daily] WHERE (([ID Status] = 'NEW') AND ([Name Status] = 'NEW')) OR (([ID Status] = 'NEW') AND ([Name Status] = 'EXISTS'))" -o %OutputFile% -s "," -W -h -1

:: Part 3
set "OutputFile=C:\Output\NewAssociate03DefaultDept.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'EmployeeNumber,User1' set nocount on; SELECT [CUSTOMER empid], [CUSTOMER CostCenter] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE (([ID Status] = 'NEW') AND ([Name Status] = 'NEW')) OR (([ID Status] = 'NEW') AND ([Name Status] = 'EXISTS'))" -o %OutputFile% -s "," -W -h -1

:: Part 4
set "OutputFile=C:\Output\NewAssociate04AssociateSite.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'EmployeeID,siteid,siteaccessoption' set nocount on; SELECT [CUSTOMER empid], 'CUSTOMER' AS [siteid],'1' AS [siteaccessoption] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE (([ID Status] = 'NEW') AND ([Name Status] = 'NEW')) OR (([ID Status] = 'NEW') AND ([Name Status] = 'EXISTS'))" -o %OutputFile% -s "," -W -h -1

:: Part 5
set "OutputFile=C:\Output\NewAssociate06AssociateSecurity.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'EmployeeID,SecurityGrpId,ScopeType' set nocount on; SELECT [CUSTOMER empid],'3' AS [SecurityGrpId],'0' AS [ScopeType] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE (([ID Status] = 'NEW') AND ([Name Status] = 'NEW')) OR (([ID Status] = 'NEW') AND ([Name Status] = 'EXISTS'))" -o %OutputFile% -s "," -W -h -1


:: UpdateID/MergeAssociates
set "OutputFile=C:\Output\UpdateID01.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'CurentID,NewID' set nocount on; SELECT [CurrentCMIDbyName],[CUSTOMER empid] FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [ID Status] = 'NEW' AND [Name Status] = 'EXISTS'" -o %OutputFile% -s "," -W -h -1


:: BadgeChange
set "OutputFile=C:\Output\BadgeChange01.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'ID,Badge,Inactive' set nocount on; SELECT [CUSTOMER empid],[CUSTOMER badgeid],'0' AS Inactive FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [ID Status] = 'EXISTS' AND [Name Status] = 'EXISTS' AND [Badge Status] = 'NEW'" -o %OutputFile% -s "," -W -h -1


:: NameChange
set "OutputFile=C:\Output\NameChange01.csv"

SQLCMD -S .\SQLSERVER -d REPORTINGSERVER -U user -P pass 
-Q "PRINT 'EmployeeNumber,FirstName,LastName,Name,Inactive' set nocount on; SELECT [CUSTOMER empid],[CUSTOMER FirstName],[CUSTOMER LastName],[CUSTOMER Name],'0' AS Inactive FROM [REPORTINGSERVER].[dbo].[AccessImport_Daily] WHERE [ID Status] = 'EXISTS' AND [Name Status] = 'NEW'" -o %OutputFile% -s "," -W -h -1

Bash Script on Debian VM

#!/bin/bash

#calling this script
# ./CMAutoImport.sh -c "Customer" -j "CUST"


#this requires that there is an rclone config for the FTP up and running 
#on the linux VM.
#Need to make sure you go into the advanced settings and turn on the 
#option to not check the host of the SSL certificate


#=========================================================================
#=========================================================================
#Things that need to exist for CMAutoImport to work:

#rclone
#	-	Need to setup the FTP profile before hand
#		-	rclone config
#		-	go through the process
		
#mount point folder where the FTP would be mounted

#IIS FTP folder to the CM Server configured and working

#mail needs to be setup for emailing (/etc/ssmtp/ssmtp.conf is where you 
#set that all up) the linux user must be the same as the email address or 
#it will fail to send....so since the email address is 
#reportingrobot@email.com, the linux user must be reportingrobot

#mount point for the job folder where the Windows Task Scheduler batch 
#files will generate the job files and from which the script will pluck 
#the files for each job

#a CIFS entry in /etc/fstab so that the job folder on the Report Server 
#auto mounts
#update - because the reporting server keeps glitching and dropping off 
#the network for a second and hoses the cifs mount...
#we're going to update to just mount the folder as we need them and unmount
#them when done instead of using the persistent mount.
#to make it work easily without sudo, add ',noauto,user' to close to the 
#end of the /etc/fstab entries.

#an entry in the second case statement in this script to add a choice 
#in order that the script can assign a value to the CIFS_MOUNT_DIR 
#variable based on flags

#=========================================================================
#=========================================================================


#get the flags for the customer name and job ex: "Customer" "CUST"
while getopts c:j: blah
do
	case "${blah}" in
		c) CUSTOMER=${OPTARG};;
		j) JOB=${OPTARG};;
	esac
done

#the job flag determines where we are pulling files from, so we have to 
#set the CIFS_MOUNT_DIR variable based up on the job flag
case "${JOB}" in
	"CUST") CIFS_MOUNT_DIR="/CMAutoImport";;
	# can add more flags as we automate other imports for CM?
esac


#variables

LOG_FILE="/CMAutoImport/${JOB}CMAutoImport_$(date +"%Y%m%d_%T").log"
#this is the batch file output folder where all the CSVs end up when 
#they are generated
#CIFS_MOUNT_DIR="/CMAutoImport"
FTP="/CMAutoImport_CMServer"
ARCHIVE_DIR="/CMAutoImport/Archive"

JOBS_THAT_WON=() #array to hold list of jobs that actually ran
JOBS_THAT_FAILED=() #array to hold list of jobs that failed
FILES_TO_DELETE=() #array to hold list of jobs that didn't have work to do because there were no rows in the document
HAS_FAILURES=0 #0 no failures, 1 some failures, 2 all failed

EMAILS="itperson@email.com,itpersontwo@email.com"

#functions
connectToFTP() {
	#commands to connect to our FTP server
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Connecting to the FTP"  >> $LOG_FILE 2>&1

	rclone mount FTP: CMAutoImport_CMServer --allow-non-empty --daemon 
}

disconnectFromFTP() {
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Disconnecting from the FTP"  >> $LOG_FILE 2>&1
	fusermount -uz /CMAutoImport_CMServer
}



checkExists() {
	#check if the set of files exists.
	THE_JOB=$1 #file to check = the first parameter supplied
	
	echo -e "\n${THE_JOB} passed to the checkExists method" >> $LOG_FILE 2>&1
	echo -e "\n${CIFS_MOUNT_DIR}/${THE_JOB} is the path to that file" >> $LOG_FILE 2>&1
	
	#check if that file actually exists
	ls $CIFS_MOUNT_DIR/$THE_JOB 1> /dev/null >> $LOG_FILE 2>&1
}

checkLength() {
	echo -e "${1} passed to checkLength method" >> $LOG_FILE 2>&1


	THE_FILE="${CIFS_MOUNT_DIR}/${1}"
	
	echo -e $THE_FILE >> $LOG_FILE 2>&1
	
	#check for number of rows in the csv file
	
	if [ "$(wc -l <${THE_FILE})" -eq 1 ];
	then
		#file is empty except for the header row, nothing to do
		echo -e "\n== $(date +"%Y%m%d_%T") ====   File is empty.  Nothing to do."  >> $LOG_FILE 2>&1
		return 1
	else
		#file has more than just the header row, work to be done
		echo -e "\n== $(date +"%Y%m%d_%T") ====   Rows present in csv.  Work to be done"  >> $LOG_FILE 2>&1
		return 0
	fi
}


moveToArchive() { #adding the job prefix to the file name as it goes into the archive folder
	THE_FILE_AND_PATH=$1
	THE_FILE="${THE_FILE_AND_PATH##*/}"
	
	
	mkdir -v -p $ARCHIVE_DIR/$(date +"%Y")/$(date +"%m_%b")/$(date +"%F")/ >> $LOG_FILE 2>&1
	if (mv -v $THE_FILE_AND_PATH $ARCHIVE_DIR/$(date +"%Y")/$(date +"%m_%b")/$(date +"%F")/$JOB"_"$THE_FILE >> $LOG_FILE 2>&1);
	then
		return 0 #success
	else
		echo -e "\n== $(date +"%Y%m%d_%T") ====  Something went wrong moving ${THE_FILE} to the Archive folder"
		return 1 #failed
	fi
}


deleteEmptyFiles() {
	FILE_PATTERN=$1
	
	shopt -s nullglob
	FILES_TO_DELETE+=($CIFS_MOUNT_DIR/$FILE_PATTERN*.csv)
	
	shopt -u nullglob
	
	for doomed in "${FILES_TO_DELETE[@]}";
	do
		#delete the file
		rm -rfv $doomed
	done
}


moveJobFilesToFTP() {
	FILE_PATTERN=$1
	
	echo -e "\n== $(date +"%Y%m%d_%T") ====   ${FILE_PATTERN} sent to moveJobFilesToFTP method"
	
	shopt -s nullglob #this makes sure if there are no files that match, the array won't be populated with the literal string of the pattern it was looking for....so basically, ensures a null array if nothing is found
	FILES_TO_WORK=($CIFS_MOUNT_DIR/$FILE_PATTERN*.csv)
	
	#work through array of list of files to transfer them to the FTP
	for file in "${FILES_TO_WORK[@]}";
	do
		echo -e "\n== $(date +"%Y%m%d_%T") ====   Moving ${file} to FTP"
		#copy the file to the mounted FTP
		if ( rclone copy -vv $file FTP:); #rclone copy is the only way I could get EVERY file to actually transfer
		then
			echo -e "\n== $(date +"%Y%m%d_%T") ====   Moving ${file} to Archive folder"
			if (moveToArchive $file);
			then
				echo -e "\n== $(date +"%Y%m%d_%T") ====   Waiting for 30 seconds to allow CMAgent to ingest this file" >> $LOG_FILE 2>&1
				sleep 30s	
				echo -e "\n== $(date +"%Y%m%d_%T") ====   Done waiting.  On to the next thing" >> $LOG_FILE 2>&1
				#return 0
			else
				echo -e "\n== $(date +"%Y%m%d_%T") ====   Something went wrong moving ${file} to the Archive folder"
				return 1 #failed
			fi			
		else
			#something went wrong
			return 1 #failure
		fi
	done
	
	echo -e "\n== $(date +"%Y%m%d_%T") ====   ${FILE_PATTERN} file/s done transferring" >> $LOG_FILE 2>&1
	return 0 #success
}


job() {
	ON_DECK=$1
	
	FILECHECK_PATTERN="${ON_DECK}01*.csv"
	
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Checking if ${ON_DECK} files exist"
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Sending ${FILECHECK_PATTERN} to checkExists method"
	if (checkExists $FILECHECK_PATTERN >> $LOG_FILE 2>&1);
	then
		#file exists
		#check length to see if there is anything to do
		echo -e "\n== $(date +"%Y%m%d_%T") ====   ${ON_DECK} file/s exist!" >> $LOG_FILE 2>&1
		echo -e "\n== $(date +"%Y%m%d_%T") ====   Checking length of first ${ON_DECK} file to see if there is actually work to do"
		if (checkLength $FILECHECK_PATTERN >> $LOG_FILE 2>&1);
		then
			echo -e "\n== $(date +"%Y%m%d_%T") ====   New data, work to do"
			#more than just the header row is present
			#move file to our FTP
			echo -e "\n== $(date +"%Y%m%d_%T") ====   Moving ${ON_DECK} files to FTP" 
			if (moveJobFilesToFTP $ON_DECK >> $LOG_FILE 2>&1);
			then
				#files moved successfull
				echo -e "\n== $(date +"%Y%m%d_%T") ====   ${ON_DECK} files moved to FTP successfully"
				JOBS_THAT_WON+=("${ON_DECK}")
			else
				#files did not move successfully
				echo -e "\n== $(date +"%Y%m%d_%T") ====   Something went wrong.  ${ON_DECK} files did not transfer to the FTP successfully"
				JOBS_THAT_FAILED+=("${ON_DECK}")
				HAS_FAILURES=1
			fi
		else
			#file is empty, nothing to do
			echo -e "\n== $(date +"%Y%m%d_%T") ====   ${ON_DECK} files are empty.  Nothing to do"
			echo -e "\n== $(date +"%Y%m%d_%T") ====   Deleting ${ON_DECK} files"
			deleteEmptyFiles $ON_DECK >> $LOG_FILE 2>&1
		fi
	else
		#file does not exists
		echo -e "\n== $(date +"%Y%m%d_%T") ====   ${ON_DECK} file/s do not exist"
		echo -e "\n== $(date +"%Y%m%d_%T") ====   Something must have gone wrong with the Windows batch files generating the files"
		JOBS_THAT_FAILED+=("${ON_DECK}")
		HAS_FAILURES=1
	fi
}


appendEmailBody() {
	APPENDME=""
	case $HAS_FAILURES in
		0) #all succeeded, but some might have been deleted for having no work
			APPENDME+="====   Jobs That Had Work And Completed Successfully:"
			for winner in "${JOBS_THAT_WON[@]}";
			do
				APPENDME+="\n${winner}"
			done
			
			APPENDME+="\n\n====   Files That Did Not Have Work And Were Deleted:"
			
			for deleted in "${FILES_TO_DELETE[@]}";
			do
				APPENDME+="\n${deleted##*/}"
			done
			;;
		1) #some failures, so we need both the successes and the failures.  Two loops to do.
			APPENDME+="====   Jobs That Had Work And Completed Successfully:"
			for winner in "${JOBS_THAT_WON[@]}";
			do
				APPENDME+="\n${winner}"
			done
			
			APPENDME+="\n\n====   Jobs That Failed:"
			
			for failure in "${JOBS_THAT_FAILED[@]}";
			do
				APPENDME+="\n${failure}"
			done
			
			APPENDME+="\n\n====   Files That Did Not Have Work And Were Deleted:"
			
			for deleted in "${FILES_TO_DELETE[@]}";
			do
				APPENDME+="\n${deleted##*/}"  ##*/    <--- this pattern gets the string after the last / from a file path
			done
			
			APPENDME+="\n\nPlease check the attached log file to see why the failed jobs failed so hard.\n\n"
			;;
		2) #all failed, so we just need the failure list.  Just one loop.
			APPENDME+="====   Jobs That Failed:\n"
			
			for failure in "${JOBS_THAT_FAILED[@]}";
			do
				APPENDME+="\n${failure}"
			done
			
			APPENDME+="\n\nPlease check the attached log file to see why the failed jobs failed so hard.\n\n"
			;;
	esac
	
	echo -e "${APPENDME}"
}


moveLogFileToArchive() {
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Moving Log file to Archive Folder"
	mkdir -v -p $ARCHIVE_DIR/$(date +"%Y")/$(date +"%m_%b")/$(date +"%F")/ >> $LOG_FILE 2>&1
	mv -v $LOG_FILE $ARCHIVE_DIR/$(date +"%Y")/$(date +"%m_%b")/$(date +"%F")/"${JOB}CMAutoImport_"$(date +"%Y%m%d_%T").log
}


finishUpAndEmail() {
	#tidy up and send the email
	# 1 - we made it to the end of the jobs
	# 2 - could not connect to the FTP
	# 3 - Reporting Server CIFS mount not mounted
	
	OUTCOME=$1
	EMAIL_SUBJECT=""
	EMAIL_BODY=""
	
	case $OUTCOME in
		1)
			#made it through all the jobs
			#check for failures
			#then send the email
			case $HAS_FAILURES in
				0)
					#no failures....everything succeeded
					EMAIL_SUBJECT="${CUSTOMER} CMAutoImport Complete - All jobs successfully transferred to FTP"
					EMAIL_BODY="Greetings,\n\nThe ${CUSTOMER} CM Auto Import process completed successfully and without any errors.\n\n"
					
					#even though everything succeeded, there might have been jobs that were deleted to list
					EMAIL_BODY+=$(appendEmailBody)
					
					EMAIL_BODY+="\n\nGood day.\n\nReporting Robot"
					;;
				1)
					#failures occurred even though we made it to the end
					#iterate through the WIN & FAILED arrays and print their values in the email body
					EMAIL_SUBJECT="${CUSTOMER} CMAutoImport Complete w/Errors - Some jobs did not complete successfully"
					EMAIL_BODY="Greetings,\n\nThe ${CUSTOMER} CM Auto Import process completed with errors along the way.  Please see the list of what was successful and what failed and take measures to correct the jobs that errored out.\n\n"
					
					#iterate through the WIN & FAILED arrays and print their values in the email body
					EMAIL_BODY+=$(appendEmailBody)
					
					EMAIL_BODY+="\n\nGood day.\n\nReporting Robot"
					;;
			esac
			;;
			
		2)
			#could not connect to the FTP
			EMAIL_SUBJECT="${CUSTOMER} CMAutoImport Failed - Could not connect to the FTP"
			EMAIL_BODY="Greetings,\n\nThe ${CUSTOMER} CM Auto Import process failed because a connection to the FTP could not be established.  Therefore, the following jobs still need to be performed to update ${CUSTOMER} associate access:\n\n"
			
			EMAIL_BODY+=$(appendEmailBody)
			
			EMAIL_BODY+="\n\nGood day.\n\nReporting Robot"
			;;
			
		3)
			# Reporting server CIFS mount not mounted
			EMAIL_SUBJECT="${CUSTOMER} CMAutoImport Failed - Reporting Server CIFS mount not mounted"
			EMAIL_BODY="Greetings,\n\nThe ${CUSTOMER} CM Auto Import process failed because the Reporting Server CIFS mount is not mounted.  Therefore, the following jobs still need to be performed to update ${CUSTOMER}'s associate access:\n\n"
			
			EMAIL_BODY+=$(appendEmailBody)
			
			EMAIL_BODY+="\n\nGood day.\n\nReporting Robot"
			;;
	esac
	
	#send email
	echo -e "${EMAIL_BODY}" | mail -s "${EMAIL_SUBJECT}" -A $LOG_FILE $EMAILS
	
	#move log file to archive
	moveLogFileToArchive
}




#=========================================================================
#=========================================================================
#===================   Main script logic starts here   ===================
#=========================================================================
#=========================================================================


#mount reporting server folder.  If you add ,noauto,user to the end of /etc/fstab, 
#you can just use 'mount /mount/point' to do the mounting without sudo.

mount $CIFS_MOUNT_DIR

#make sure CIFs mount is mounted
echo -e "\n== $(date +"%Y%m%d_%T") ====   Checking if CIFs mount is mounted" >> $LOG_FILE 2>&1

if (grep -qs $CIFS_MOUNT_DIR /proc/mounts >> $LOG_FILE 2>&1);
then
	#is mounted, do stuff
	#connect to the FTP
	if (connectToFTP >> $LOG_FILE 2>&1); #technically, since we're using rclone copy, we don't need to mount the FTP, but it's a good test to see if it's even available for the rclone copy commands later, so we'll just roll with it.
	then
		#connected to FTP
		job "Remove" >> $LOG_FILE 2>&1
		job "AddNewDepts" >> $LOG_FILE 2>&1
		job "BadgeChange" >> $LOG_FILE 2>&1
		job "NameChange" >> $LOG_FILE 2>&1
		job "NewAssociate" >> $LOG_FILE 2>&1
		job "UpdateID" >> $LOG_FILE 2>&1
		job "UpdateAssociateDept" >> $LOG_FILE 2>&1
		disconnectFromFTP
		finishUpAndEmail 1
		
		#now unmount the reporting server folder
		umount $CIFS_MOUNT_DIR
		
		exit 0
	else
		#not connected to FTP
		echo -e "\n== $(date +"%Y%m%d_%T") ====   Something went wrong.  Could not connect to the FTP"
		#which means everything failed
		JOBS_THAT_FAILED+=( "Remove" "AddNewDepts" "BadgeChange" "NameChange" "NewAssociate" "UpdateID" "UpdateAssociateDept" )
		HAS_FAILURES=2
		finishUpAndEmail 2
		
		#now unmount the reporting server folder
		umount $CIFS_MOUNT_DIR
		
		exit 1
	fi
else
	#is not mounted
	echo -e "\n== $(date +"%Y%m%d_%T") ====   Something went wrong.  Reporting Server CIFS mount not mounted"
	#which means everything failed
	JOBS_THAT_FAILED+=( "Remove" "AddNewDepts" "BadgeChange" "NameChange" "NewAssociate" "UpdateID" "UpdateAssociateDept" )
	HAS_FAILURES=3
	finishUpAndEmail 3
	
	exit 1
fi