PSBarrowcliffe

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

Philip Scott Barrowcliffe — Thu, 07 Apr 2022 02:51:00 GMT

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

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.
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).
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.
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!
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

ERP Lead Time Averages Vs. SQL Calculated Lead Time Averages

Philip Scott Barrowcliffe — Wed, 06 Apr 2022 22:51:51 GMT

Our purchasing manager was stumbling upon scenarios where the average lead times that were being calculated by our ERP system weren't always reliable, causing issues with maintaining our customers' mix/max levels on certain items.

So she had the thought to come up with something that could/would calculate an average lead time for items she had to order in a way that she felt would be more reliable - by taking the last (up to) three completely received POs and using those to calculate an average that might be more in line with 'recent reality'.

We could then compare the ERP system's stated average lead time and compare it to our calculated average, and whichever one was the greater amount would be the one she would base her purchasing activities on in the hopes of having less "Oh crap, they're going to run out!" moments on critical items.

There was also the added bonus of starting a historical list of item order dates, final receipt dates, and the actual lead times for those completed POs to look back on should the need arise. You can never have too much data.

USE [REPORTINGSERVER]
GO
/****** Object:  StoredProcedure [dbo].[getCompletePOsThenCalculateAveragesGETTERSANDDUMPERS]    Script Date: 3/1/2022 1:13:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Scott Barrowcliffe
-- Create date: 03112021
-- Description:	Trying to get to a point where we have a historical record of completed POs with which to calculate lead time averages.
-- =============================================
ALTER PROCEDURE [dbo].[getCompletePOsThenCalculateAveragesGETTERSANDDUMPERS] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- get a list of POs
	SELECT *
	INTO #POLIST
	FROM
		(
			SELECT
				POHEAD.po AS po
				,POLINE.item AS item
				,POHEAD.ord_date AS ord_date
				,WAITTRANS.trans_date AS rcpt_date
				,POLINE.q_ord AS q_ord
				,WAITTRANS.qty_d AS q_rcv
				,CAST(POHEAD.po AS NVARCHAR) + CAST(POLINE.item AS NVARCHAR) AS maxFinderHelper
			FROM
				OPENQUERY(DISTONE,'SELECT po, ord_date, rcv_date, ord_class, warehouse, rec_seq, invc_date,post_date FROM V2.PUB.po_head WHERE ord_class = ''W'' AND rec_type=''O'' AND company_it = ''MCI'' AND ord_date >= ADD_MONTHS(SYSDATE,-96)') 
				AS POHEAD INNER JOIN
				OPENQUERY(DISTONE, 'SELECT po, rec_type, rec_seq, req_date, line, line_add, descr, dsc_pct, r_ext, item, q_ord, q_ord_d, q_rcv, q_rcv_d, vendor, received, invoice, order, invc_date FROM V2.PUB.po_line WHERE ord_class = ''W'' AND rec_type=''O'' AND warehouse = ''MCA''') 
				AS POLINE ON POHEAD.po = POLINE.po AND POHEAD.rec_seq = POLINE.rec_seq LEFT OUTER JOIN
				OPENQUERY(DISTONE,'SELECT trans_no, item, ref_id, ref_line, trans_date, rec_type, qty_d FROM V2.PUB.wa_it_trans WHERE company_it = ''MCI'' AND warehouse = ''MCA'' AND rec_type = ''R''') 
				AS WAITTRANS ON WAITTRANS.item = POLINE.item AND WAITTRANS.ref_id = POLINE.po AND WAITTRANS.ref_line = POLINE.line_add INNER JOIN
				(
					SELECT item 
					FROM OPENQUERY(DISTONE, 'SELECT item, discontinued FROM V2.PUB.item WHERE company_it = ''MCI''')
					WHERE discontinued = 'False'
				) AS ITEM ON ITEM.item = POLINE.item
			WHERE POLINE.item <> 'TOOL REPAIR'
		) AS polist



	-- ok, now get the complete ones
	SELECT *
	INTO #FINDINGCOMPLETE
	FROM
		(
			SELECT 
				po
				,item
				,ord_date
				,maxFinderHelper
			FROM
				(
					SELECT
						po
						,item
						,MAX(q_ord) AS q_ord
						,SUM(q_rcv) AS sum_q_rcv
						,MAX(ord_date) AS ord_date
						,MAX(maxFinderHelper) AS maxFinderHelper
					FROM #POLIST
					GROUP BY po,item
				) AS COMPLETE
			WHERE q_ord - sum_q_rcv <= 0  -- all received
		) AS findingcomplete



	SELECT *
	INTO #GETTHELASTRCVDATE
	FROM
		(
			SELECT
				po
				,item
				,MAX(rcpt_date) AS last_rcpt_date
			FROM
				#POLIST
			WHERE maxFinderHelper IN (
            							SELECT DISTINCT maxFinderHelper 
                                        FROM #FINDINGCOMPLETE
                                     )
			GROUP BY po, item		
		) AS getthelastrcvdate



	SELECT *
	INTO #COMPLETEPOLIST
	FROM
		(
			SELECT
				FC.po
				,FC.item
				,FC.ord_date
				,GD.last_rcpt_date
				,DATEDIFF(DAY,FC.ord_date,GD.last_rcpt_date) AS leadTime
				,CAST(FC.po AS NVARCHAR) + CAST(FC.item AS NVARCHAR) + CAST(DATEDIFF(DAY,FC.ord_date,GD.last_rcpt_date) AS NVARCHAR) AS thisIsAKey
			FROM
				#FINDINGCOMPLETE AS FC INNER JOIN
				#GETTHELASTRCVDATE AS GD ON FC.po = GD.po AND FC.item = GD.item
		) AS completepolist



	-- now grab existing complete PO list to use as a filter on the 
	-- list we just compiled before we dump new ones to the table
	SELECT *
	INTO #GETEXISTINGFROMTABLE
	FROM
		(
			SELECT DISTINCT thisIsAKey AS thisIsAkey
			FROM [REPORTINGSERVER].[dbo].[CompletePOs] AS fromTheTable
		) AS getexistingfrontable



	INSERT INTO [REPORTINGSERVER].[dbo].[CompletePOs]
	SELECT
		CPL.po
		,CPL.item
		,CPL.ord_date
		,CPL.last_rcpt_date
		,CPL.leadTime
		,CPL.thisIsAKey
		,GETDATE()
	FROM 
		#COMPLETEPOLIST AS CPL LEFT OUTER JOIN
		#GETEXISTINGFROMTABLE AS GE ON CP.thisIsAKey = GE.thisIsAkey
	WHERE 
		CPL.leadTime IS NOT NULL 
		AND GE.thisIsAKey IS NULL


--=========================================================================
--=========================================================================


	-- now get the list of completed POs and add a row number 
    -- to count the number of times an item's PO was completed
	SELECT *
	INTO #GETCOMPLETEPOCOUNTS
	FROM
		(
			SELECT
				po
				,item
				,ord_date
				,last_rcpt_date
				,leadTime
				,ROW_NUMBER() OVER (PARTITION BY item ORDER BY po,last_rcpt_date) AS occurrence
			FROM [REPORTINGSERVER].[dbo].[CompletePOs]
		) AS getcompleteposcounts


	-- then get the last occurrences (up to three) for each item
	SELECT *
	INTO #GETLASTOCCURRENCES
	FROM
		(
			SELECT
				CAST(COUNTS.po AS NVARCHAR(MAX)) AS po
				,CAST(GETMAX.item AS NVARCHAR(MAX)) AS item
				,COUNTS.ord_date
				,COUNTS.last_rcpt_date
				,COUNTS.leadTime
			FROM
				#GETCOMPLETEPOCOUNTS AS COUNTS INNER JOIN
				(
					SELECT
						item
						,MAX(occurrence) AS maxOccurrence
					FROM #GETCOMPLETEPOCOUNTS
					GROUP BY item
				) AS GETMAX ON COUNTS.item = GETMAX.item AND COUNTS.occurrence >= (GETMAX.maxOccurrence - 2)
		) AS getlastoccurrences


	-- and then calculate the average lead times based 
    -- on those last occurrences
	SELECT *
	INTO #GETAVERAGES
	FROM
		(
			SELECT
				item
				,SUM(leadTime)/COUNT(item) AS averageLeadTime
				,COUNT(item) AS basedOnXOccurrences
			FROM #GETLASTOCCURRENCES
			GROUP BY item
		) AS getaverages


	SELECT *
	INTO #GETTHELASTORDERDATE
	FROM
		(
			SELECT
				item
				,MAX(ord_date) AS last_ord_date
			FROM
				#FINDINGCOMPLETE
			WHERE item IN (SELECT DISTINCT item FROM #FINDINGCOMPLETE)
			GROUP BY item		
		) AS getthelastordvdate



	-- This next bit is slow as balls, so we'll try adding 
    -- an index to the table it is going to abuse lol
	CREATE CLUSTERED INDEX GETLASTOCCURRENCES_index ON #GETLASTOCCURRENCES (item,po,ord_date,last_rcpt_date,leadTime)

	SELECT *
	INTO #LASTOCCURRENCESSTUFFED
	FROM
		(
			SELECT
				a.item
				,RIGHT(bb.lastPOs,LEN(bb.lastPOs) - 2) AS lastPOs
				,RIGHT(cc.lastOrderDates,LEN(cc.lastOrderDates) - 2) AS lastOrderDates
				,RIGHT(dd.lastReceiptDates,LEN(dd.lastReceiptDates) - 2) AS lastReceiptDates
				,RIGHT(ee.lastLeadTimes,LEN(ee.lastLeadTimes) - 2) AS lastLeadTimes
			FROM
				(
					SELECT DISTINCT item
					FROM #GETLASTOCCURRENCES
				) AS a CROSS APPLY
				(
					SELECT DISTINCT
						', ' + CONVERT(VARCHAR, b.po, 111)
					FROM #GETLASTOCCURRENCES b
					WHERE b.item = a.item
					FOR XML PATH('')
				) bb (lastPOs) CROSS APPLY
				(
					SELECT DISTINCT
						', ' + CONVERT(VARCHAR, c.ord_date, 111)
					FROM #GETLASTOCCURRENCES c
					WHERE c.item = a.item
					FOR XML PATH('')
				) cc (lastOrderDates) CROSS APPLY
				(
					SELECT DISTINCT
						', ' + CONVERT(VARCHAR, d.last_rcpt_date, 111)
					FROM #GETLASTOCCURRENCES d
					WHERE d.item = a.item
					FOR XML PATH('')
				) dd (lastReceiptDates) CROSS APPLY
				(
					SELECT DISTINCT
						', ' + CAST(e.leadTime AS VARCHAR)
					FROM #GETLASTOCCURRENCES e
					WHERE e.item = a.item
					FOR XML PATH('')
				) ee (lastLeadTimes)			
		) AS lastoccurrencesstuffed



		SELECT *
		INTO #INFOSUMMARY
		FROM
			(
				SELECT
					LO.item
					,LO.lastPOs
					,LO.lastOrderDates
					,LO.lastReceiptDates
					,LO.lastLeadTimes
					,GA.averageLeadTime
					,GA.basedOnXOccurrences
					,LOD.last_ord_date
				FROM
					#LASTOCCURRENCESSTUFFED AS LO INNER JOIN
					#GETAVERAGES AS GA ON LO.item = GA.item INNER JOIN
					#GETTHELASTORDERDATE AS LOD ON LO.item = LOD.item
			) AS infosummary


		SELECT *
		INTO #JOINITALLUP
		FROM
			(
				SELECT
					item
					,Calculated_AVG_leadtime
					,ERP_AVG_leadtime
					,SafestChoice
					,OccurrenceCount
					,lastPOs
					,lastOrderDates
					,lastReceiptDates
					,lastLeadTimes
					,last_ord_date
					,CASE 
						WHEN SafestChoice = 'LAST_3_AVG' 
							AND INFO.averageLeadTime - WAITEM.avg_lead_time <> 1 
							AND	(1 - (WAITEM.avg_lead_time/INFO.averageLeadTime)) > 0.15 
							AND	DATEADD(MONTH,-12,GETDATE()) < INFO.last_ord_date THEN 1 
						ELSE 0 
						END AS specialFilterColumn
				FROM
					(
						SELECT
							WAITEM.item
							,INFO.averageLeadTime AS [Calculated_AVG_leadtime]
							,WAITEM.avg_lead_time AS ERP_AVG_leadtime
							,CASE 
								WHEN INFO.basedOnXOccurrences = 1 THEN 'ERP_AVG' 
								WHEN INFO.averageLeadTime > WAITEM.avg_lead_time THEN 'LAST_3_AVG' 
								WHEN INFO.averageLeadTime < WAITEM.avg_lead_time THEN 'ERP_AVG' 
								WHEN INFO.averageLeadTime IS NULL THEN 'ERP_AVG' 
								WHEN INFO.averageLeadTime = WAITEM.avg_lead_time THEN 'EQUAL' 
								ELSE 'USE JUDGEMENT' 
								END AS SafestChoice
							,ISNULL(INFO.basedOnXOccurrences, 0) AS OccurrenceCount
							,INFO.lastPOs
							,INFO.lastOrderDates
							,INFO.lastReceiptDates
							,INFO.lastLeadTimes
							,INFO.last_ord_date
						FROM
							(
								SELECT
									item
									,avg_lead_time
								FROM
									OPENQUERY(ERP_DB, 'SELECT item,avg_lead_time FROM V2.PUB.wa_item WHERE company_it = ''REDACTED'' AND warehouse = ''REDACTED'' AND "min" > 0 AND "max" > 0') AS wa_item
							) AS WAITEM LEFT OUTER JOIN
							#INFOSUMMARY AS INFO ON WAITEM.item = INFO.ite
					) AS blah
			) AS joinitallup

	-- empty and fill the storage table
	TRUNCATE TABLE [REPORTINGSERVER].[dbo].[CalculatedVsERPAverageLeadTimes]

	INSERT INTO [REPORTINGSERVER].[dbo].[CalculatedVsERPAverageLeadTimes]
	SELECT
		item
		,Calculated_AVG_leadtime
		,ERP_AVG_leadtime
		,SafestChoice
		,OccurrenceCount
		,lastPOs
		,lastOrderDates
		,lastReceiptDates
		,lastLeadTimes
		,last_ord_date
		,specialFilterColumn
		,GETDATE()
	FROM #JOINITALLUP AS alldone
END

SQL ChargeOutInterface

Philip Scott Barrowcliffe — Sun, 03 Apr 2022 20:14:00 GMT

Sometimes requirements make things...difficult and therefore interesting.

So an integration customer wanted a daily transaction report formatted in a certain way to allow their ERP system to ingest the data in order to update their inventory numbers.

This would usually be a straightforward transaction dump, formatted to whatever format their system required. However, in the event that a transaction was updated after the fact, the customer did not want to see the new value for the transaction...they wanted the difference between the original transaction and the updated transaction. Simple enough, yeah?

Not really, because of the way our system handled transaction updates. When an update occurred, the quantity value of the original transaction was shifted to a separate XTRAN transaction and replaced with the new quantity value.

So in order to accomplish their desired output, I had to get a little crazy with WHILE loops and temp tables in order to shift values back to their original positions to then be able to calculate the difference. Things got even crazier when you considered the possibility of multiple updates on the same original transaction.

Excited for the challenge, I set about writing the solution...while working from 'home' over a few nice sunny days on 'vacation' at Lakeside.

BEGIN
  SET NOCOUNT ON;

--*******************************************
-- TELERIK NOT SHOWING DATA SOURCE FIELDS IF STORED PROCEDURE USES TEMP TABLES FIX
-- source of solution: https://www.telerik.com/forums/temporary-table-in-stored-proc

IF 1=0 
  BEGIN
    SET FMTONLY OFF
  END
--*******************************************

DECLARE 
@counter INT = 1
,@loopCounter INT = 0
,@rowCount INT = 0
,@loopRowCount INT = 0
,@currentTransNo NVARCHAR(35) = '' 

--=========================================================

-- first get yesterday's transactions
SELECT *
INTO #YESTERDAY
FROM
  (
    SELECT 
      TRANS.transnumber
      ,TRANS.station
      ,TRANS.bin
      ,TRANS.Item
      ,TRANS.OldItem
      ,TRANS.employee
      ,TRANS.User1
      ,TRANS.User3
      ,TRANS.User2
      ,TRANS.User4
      ,TRANS.cost
      ,TRANS.quantity
      ,TRANS.Transdate
      ,TRANS.Transtime
      ,TRANS.[type]
      ,TRANS.TypeDescription
      ,TRANS.binqty
      ,TRANS.User5
      ,TRANS.SerialID
      ,TRANS.User6
      ,TRANS.RelatedKey
      ,TRANS.[Status]
      ,TRANS.CribBin
      ,TRANS.BatchId
      ,TRANS.IssuedTo
      ,TRANS.Consignment
      ,TRANS.OtherCribBin
      ,TRANS.WONo
      ,TRANS.TransUsage
      ,TRANS.RepairCalCycle
      ,TRANS.Crib
      ,TRANS.LotNo
      ,TRANS.UsageType
      ,TRANS.UsageCribBin
      ,TRANS.UsageItemNumber
      ,TRANS.ReservationNo
      ,TRANS.SubType
      ,TRANS.OtherSiteNo
      ,TRANS.CycleCountClassNo
      ,TRANS.ExpectedAccuracy
      ,TRANS.TransRFIDNo
      ,TRANS.LocalTransDate
      ,TRANSDETAIL.TransReasonCode
    FROM  
      REDACTED_CLOUD_01.CM01.dbo.TRANS AS TRANS LEFT OUTER JOIN
      REDACTED_CLOUD_01.CM01.dbo.Crib AS CRIB ON TRANS.Crib = CRIB.Crib LEFT OUTER JOIN
      REDACTED_CLOUD_01.CM01.dbo.TRANSDETAIL AS TRANSDETAIL ON TRANS.transnumber = TRANSDETAIL.TransNo
    WHERE 
      CAST(Transdate AS DATE) = CAST(DATEADD(day,-1,GETDATE()) AS DATE)
      AND Crib.SiteID = 'REDACTED'
      AND (
            (
              TRANSDETAIL.TransReasonCode <> 'NB' 
              AND TRANSDETAIL.TransReasonCode IS NOT NULL
            )
            OR (TRANSDETAIL.TransReasonCode IS NULL)
          ) 
      AND (
            TRANS.bin <> 'CANDYBIN'
            OR TRANS.Item <> 'REDACTEDCANDY'
          )
  ) AS yesterday


--==========================================================================
-- Ok, first chunk of logic to "show the difference in values" 
-- for the UPDATE/XTRAN tansactions
-- We'll do the one where the UPDATE takes place on a different day
--==========================================================================

-- get two whole months of transactions so that we definitely capture the
-- past month/30 days...many fields in case we need them.
SELECT *
INTO #WHOLEMONTH
FROM
  (
    SELECT 
      TRANS.transnumber
      ,TRANS.station
      ,TRANS.bin
      ,TRANS.Item
      ,TRANS.OldItem
      ,TRANS.employee
      ,TRANS.User1
      ,TRANS.User3
      ,TRANS.User2
      ,TRANS.User4
      ,TRANS.cost
      ,TRANS.quantity
      ,TRANS.Transdate
      ,TRANS.Transtime
      ,TRANS.type
      ,TRANS.TypeDescription
      ,TRANS.binqty
      ,TRANS.User5
      ,TRANS.SerialID
      ,TRANS.User6
      ,TRANS.RelatedKey
      ,TRANS.Status
      ,TRANS.CribBin
      ,TRANS.BatchId
      ,TRANS.IssuedTo
      ,TRANS.Consignment
      ,TRANS.OtherCribBin
      ,TRANS.WONo
      ,TRANS.TransUsage
      ,TRANS.RepairCalCycle
      ,TRANS.Crib
      ,TRANS.LotNo
      ,TRANS.UsageType
      ,TRANS.UsageCribBin
      ,TRANS.UsageItemNumber
      ,TRANS.ReservationNo
      ,TRANS.SubType
      ,TRANS.OtherSiteNo
      ,TRANS.CycleCountClassNo
      ,TRANS.ExpectedAccuracy
      ,TRANS.TransRFIDNo
      ,TRANS.LocalTransDate
    FROM
      REDACTED_CLOUD_01.CM01.dbo.TRANS AS TRANS LEFT OUTER JOIN
      REDACTED_CLOUD_01.CM01.dbo.Crib AS CRIB ON TRANS.Crib = CRIB.Crib
    WHERE 
      CAST(Transdate AS DATE) >= CAST(DATEADD(month,-2,GETDATE()) AS DATE)
      AND Crib.SiteID = 'REDACTED'
      AND (
          TRANS.bin <> 'CANDYBIN'
          OR TRANS.Item <> 'REDACTEDCANDY'
        )
  ) AS wholemonth


-- ok, now get just the XTRANS
SELECT *
INTO #WHOLEMONTH_XTRAN_TRANSNO
FROM
  (
    SELECT DISTINCT BatchId
    FROM #WHOLEMONTH AS blah
    WHERE TypeDescription = 'XTRAN'
  ) AS wholemonth_xtran_transno


-- now get a list of the original transactions for XTRANS
SELECT *
INTO #WHOLEMONTH_XTRAN_OGTRANS
FROM
  (
    SELECT
      ROW_NUMBER() OVER (ORDER BY transnumber) AS theRow
      ,transnumber
      ,Item
      ,quantity
      ,Transdate
      ,Transtime
      ,BatchId
      ,TypeDescription
      ,'OG' AS [Sequence]
    FROM #WHOLEMONTH AS blah
    WHERE transnumber IN (SELECT BatchId FROM #WHOLEMONTH_XTRAN_TRANSNO)
  ) AS wholemonth_xtran_ogtrans


-- now just the XTRANS where the items match (because the XTRAN BatchId 
-  can still be the same for ISSUES that are not the same item
SELECT *
INTO #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
FROM
  (
    SELECT
      b.transnumber
      ,b.Item
      ,b.quantity
      ,b.Transdate
      ,b.Transtime
      ,b.BatchId
      ,b.TypeDescription
      ,ROW_NUMBER() OVER (PARTITION BY b.BatchId ORDER BY b.Transdate,b.Transtime) AS [Sequence]
    FROM 
      #WHOLEMONTH_XTRAN_OGTRANS AS a INNER JOIN
      #WHOLEMONTH AS b ON a.Item = b.Item AND a.transnumber = b.BatchId
    WHERE b.TypeDescription = 'XTRAN'
  ) AS wholemonth_xtran_justrelevantxtran
  
  
  
-- ok, now a while loop to itterate through the OG trans rows, do some
-- stuff, and then dump that stuff into a temp table to use later.
SELECT @rowCount = COUNT(theRow) FROM #WHOLEMONTH_XTRAN_OGTRANS
DROP TABLE IF EXISTS #INSIDELOOP

CREATE TABLE #INSIDELOOP
(
  transnumber NVARCHAR(30) NOT NULL
  ,Item NVARCHAR(30) NOT NULL
  ,ParentTransQty INT NULL
  ,OGTransQty INT NULL
  ,ShiftedTransQty INT NULL
  ,DifferenceQty INT NULL
  ,Transdate DATETIME NULL
  ,Transtime TIME NULL
  ,TypeDescription NVARCHAR(30) NULL
  ,OGTrans NVARCHAR(30) NULL
  ,[Sequence] NVARCHAR(10) NULL
)

WHILE @counter <= @rowCount
  BEGIN    
    -- We need to get the max theRow number where the XTRAN batch id = 
    -- the OG transnumber and set a variable to that theRow number.
    -- Then use that variable to WHILE through stuff and shuffle values 
    -- for the transactions.
    
    SELECT @loopRowCount = 
      COUNT([Sequence]) 
      FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN 
      WHERE BatchId IN (SELECT transnumber FROM #WHOLEMONTH_XTRAN_OGTRANS WHERE theRow = @counter)

    SET @loopRowCount += 1 -- add 1 to account for the OG parent transaction
          
    -- now the next while loop to do some stuff I don't know what yet
    SET @loopCounter = 1
    
    WHILE @loopCounter <= @loopRowCount
      BEGIN
        -- get the first row and 'correct' its value....
        -- the 'original' value of the transaction will be 
        -- the first XTRAN(if multiple)
        IF @loopCounter = 1  -- first run of next inside loop
          BEGIN
            INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
            SELECT
              OG.transnumber
              ,OG.Item
              ,OG.quantity AS ParentTransQty
              ,OG.quantity AS OGTransQty
              ,XTRAN.quantity AS ShiftedTransQty
              ,0 AS DifferenceQty
              ,OG.Transdate
              ,OG.Transtime
              ,OG.TypeDescription
              ,OG.transnumber AS OGTrans
              ,OG.[Sequence]
            FROM
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_OGTRANS
                WHERE theRow = @counter
              ) AS OG LEFT OUTER JOIN
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
              ) AS XTRAN ON OG.transnumber = XTRAN.BatchId AND OG.Item = XTRAN.Item AND XTRAN.[Sequence] = @loopCounter -- 1
              
              SELECT @currentTransNo = transnumber FROM #WHOLEMONTH_XTRAN_OGTRANS WHERE theRow = @counter
          END
        ELSE IF @loopCounter = @loopRowCount AND @loopCounter > 1  -- ELSE IF to handle the last run so we can compare last XTRAN to OG trans again
          BEGIN
            INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
            SELECT
              XTRAN.transnumber
              ,XTRAN.Item
              ,OG.quantity AS ParentTransQty
              ,XTRAN.quantity AS OGTransQty
              ,OG.quantity AS ShiftedTransQty
              ,OG.quantity - XTRAN.quantity AS DifferenceQty
              ,XTRAN.Transdate
              ,XTRAN.Transtime
              ,XTRAN.TypeDescription
              ,XTRAN.BatchId AS OGTrans
              ,XTRAN.[Sequence]
            FROM
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_OGTRANS
                WHERE theRow = @counter
              ) AS OG LEFT OUTER JOIN
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
              ) AS XTRAN ON OG.transnumber = XTRAN.BatchId AND OG.Item = XTRAN.Item AND XTRAN.[Sequence] = @loopRowCount - 1 -- final XTRAN
          END
        ELSE IF @loopCounter < @loopRowCount AND @loopCounter > 1
          BEGIN
          -- ELSE sql statements here to handle any non-first, 
          -- non-last XTRANS, comparing XTRAN to XTRAN
            INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
            SELECT
              XTRAN.transnumber
              ,XTRAN.Item
              ,OG.quantity AS ParentTransQty
              ,XTRAN.quantity AS OGTransQty
              ,XTRAN2.quantity AS ShiftedTransQty
              ,XTRAN2.quantity - XTRAN.quantity AS DifferenceQty
              ,XTRAN.Transdate
              ,XTRAN.Transtime
              ,XTRAN.TypeDescription
              ,XTRAN.BatchId AS OGTrans
              ,XTRAN.[Sequence]
            FROM
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
                WHERE BatchId = @currentTransNo AND [Sequence] = @loopCounter - 1
              ) AS XTRAN LEFT OUTER JOIN
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
              ) AS XTRAN2 ON XTRAN.BatchId = XTRAN2.BatchId AND XTRAN.Item = XTRAN2.Item AND XTRAN2.[Sequence] = @loopCounter LEFT OUTER JOIN
              (
                SELECT
                  transnumber
                  ,Item
                  ,quantity
                  ,Transdate
                  ,Transtime
                  ,BatchId
                  ,TypeDescription
                  ,[Sequence]
                FROM #WHOLEMONTH_XTRAN_OGTRANS
                WHERE Transnumber = @currentTransNo
              ) AS OG ON XTRAN.BatchId = OG.Transnumber AND XTRAN.Item = OG.Item    
          END
        
        SET @loopCounter += 1
      END
    
    SET @counter += 1
  END
  
  
  
-- now get just the OG row from the #INSIDELOOP dataset and add row numbers.
SELECT *
INTO #INSIDELOOP_OGROWS
FROM
  (
    SELECT
      ROW_NUMBER() OVER (ORDER BY transnumber) AS theRow
      ,transnumber
      ,Item
      ,ParentTransQty
      ,OGTransQty
      ,ShiftedTransQty
      ,DifferenceQty
      ,Transdate
      ,Transtime
      ,TypeDescription
      ,OGTrans
      ,[Sequence]
    FROM #INSIDELOOP AS blah
    WHERE [Sequence] = 'OG'
  ) AS insidelooprows

-- ok, I think we're going to have to loop again to better determine which
-- of the scenarios these transaction updates might be:
-- 1. Same day
-- 2. Different Day

-- reset the rowCount and counter variable
SELECT @rowCount = COUNT(theRow) FROM #INSIDELOOP_OGROWS
SELECT @counter = 1

-- create another temp table to dump the results of the looping into
DROP TABLE IF EXISTS #SECONDINSIDELOOP

CREATE TABLE #SECONDINSIDELOOP
(
  transnumber NVARCHAR(30) NULL
  ,Item NVARCHAR(30) NULL
  ,ParentTransQty INT NULL
  ,OGTransQty INT NULL
  ,ShiftedTransQty INT NULL
  ,DifferenceQty INT NULL
  ,Transdate DATETIME NULL
  ,Transtime TIME NULL
  ,TypeDescription NVARCHAR(30) NULL
  ,OGTrans NVARCHAR(30) NULL
  ,[Sequence] NVARCHAR(10) NULL
  ,Scenario INT NULL -- this will hold 1 for parent trans, 2 for same day, 3 for different day
)



--same structure as the last loops sort of 
WHILE @counter <= @rowCount
  BEGIN

    SELECT @loopRowCount = 
      COUNT([Sequence]) 
      FROM #INSIDELOOP
      WHERE OGTrans IN (SELECT OGTrans FROM #INSIDELOOP_OGROWS WHERE theRow = @counter)
          
    SET @loopCounter = 1
    
    WHILE @loopCounter <= @loopRowCount
      BEGIN
        IF @loopCounter = 1 -- first row of the set is the original INSERT transaction
          BEGIN
            INSERT INTO #SECONDINSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence],Scenario)
            SELECT
              OG.transnumber
              ,OG.Item
              ,OG.ParentTransQty
              ,OG.OGTransQty
              ,OG.ShiftedTransQty
              ,OG.DifferenceQty
              ,OG.Transdate
              ,OG.Transtime
              ,OG.TypeDescription
              ,OG.OGTrans
              ,OG.[Sequence]
              ,1 AS Scenario
            FROM 
              (
                SELECT
                  transnumber
                  ,Item
                  ,ParentTransQty
                  ,OGTransQty
                  ,ShiftedTransQty
                  ,DifferenceQty
                  ,Transdate
                  ,Transtime
                  ,TypeDescription
                  ,OGTrans
                  ,[Sequence]
                FROM #INSIDELOOP_OGROWS
                WHERE theRow = @counter
              ) AS OG

            SELECT @currentTransNo = transnumber FROM #INSIDELOOP_OGROWS WHERE theRow = @counter
          END

        ELSE 
        --IF @loopCounter = @loopRowCount AND @loopCounter > 1 -- get the last one
          BEGIN
            INSERT INTO #SECONDINSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence],Scenario)
            SELECT
              XTRAN.transnumber
              ,XTRAN.Item
              ,XTRAN.ParentTransQty
              ,XTRAN.OGTransQty
              ,XTRAN.ShiftedTransQty
              ,XTRAN.DifferenceQty
              ,XTRAN.Transdate
              ,XTRAN.Transtime
              ,XTRAN.TypeDescription
              ,XTRAN.OGTrans
              ,XTRAN.[Sequence]
              ,CASE 
                WHEN FORMAT(OG.Transdate,'yyyy-MM-dd') = FORMAT(XTRAN.Transdate,'yyyy-MM-dd')
                THEN 2
                ELSE 3 END AS Scenario
            FROM 
              (
                SELECT
                  transnumber
                  ,Item
                  ,ParentTransQty
                  ,OGTransQty
                  ,ShiftedTransQty
                  ,DifferenceQty
                  ,Transdate
                  ,Transtime
                  ,TypeDescription
                  ,OGTrans
                  ,[Sequence]
                FROM #INSIDELOOP
                WHERE OGTrans = @currentTransNo AND [Sequence] = 'OG'
              ) AS OG LEFT OUTER JOIN
              (
                SELECT
                  transnumber
                  ,Item
                  ,ParentTransQty
                  ,OGTransQty
                  ,ShiftedTransQty
                  ,DifferenceQty
                  ,Transdate
                  ,Transtime
                  ,TypeDescription
                  ,OGTrans
                  ,[Sequence]
                FROM #INSIDELOOP
              ) AS XTRAN ON OG.OGTrans = XTRAN.OGTrans AND XTRAN.[Sequence] = CAST(@loopCounter - 1 AS NVARCHAR(MAX))  
          END
      

        SET @loopCounter += 1
      END
    
    SET @counter += 1
  END


-- ok, we have to filter out RECVE transactions....and I think the easiest
-- way to do that would be to just filter them and their associated XTRANs
-- all out at the end.  So get the unique RECVE transnumbers to use later 
-- to filter out.
SELECT *
INTO #JUSTRECVE
FROM
  (
    SELECT DISTINCT transnumber
    FROM #WHOLEMONTH
    WHERE TypeDescription = 'RECVE'
  ) AS justrecve

-- And now delete the RECVE trans and their 
-- associated XTRANS from the looping stuff we just did
DELETE FROM #SECONDINSIDELOOP
WHERE OGTrans IN (SELECT transnumber FROM #JUSTRECVE)



--================================
-- Get the pertinent item info
--================================
SELECT *
INTO #THEITEMS
FROM
  (
    SELECT DISTINCT Item
    FROM #YESTERDAY AS gettingtheitems
  ) AS theitems

-- now make an index
IF EXISTS 
  (
    SELECT name
    FROM sys.indexes  
    WHERE name = N'IX_THEITEMS'
  )     
DROP INDEX IX_THEITEMS ON #THEITEMS
  
  
CREATE NONCLUSTERED INDEX IX_THEITEMS
ON #THEITEMS (Item)


SELECT *
INTO #INV
FROM
  (
    SELECT
      ItemNumber
      ,Description2
      ,Manufacturer
      ,MfrNumber
      ,UDFREDACTEDPartReference
    FROM  REDACTED_CLOUD_01.CM01.dbo.INVENTRY AS INVENTRY
    WHERE ItemNumber IN (SELECT Item FROM #THEITEMS)
  ) AS inv



--======================================
-- Get the pertinent STATION info
--=======================================
SELECT *
INTO #THECRIBBINS
FROM
  (
    SELECT DISTINCT CribBin
    FROM #YESTERDAY AS gettingthecribbins
  ) AS thecribbins


-- now make an index
IF EXISTS 
  (
    SELECT name
    FROM sys.indexes  
    WHERE name = N'IX_THECRIBBINS'
  )     
DROP INDEX IX_THECRIBBINS ON #THECRIBBINS
  
  
CREATE NONCLUSTERED INDEX IX_THECRIBBINS
ON #THECRIBBINS (CribBin)

SELECT *
INTO #STA
FROM
  (
    SELECT 
      CribBin
      ,Crib
      ,Bin
      ,Item
      ,Consignment
    FROM  REDACTED_CLOUD_01.CM01.dbo.STATION AS STATION
    WHERE CribBin IN (SELECT CribBin FROM #THECRIBBINS)
  ) AS sta



--======================================
-- Get the pertinent EMPLOYEE info
--=======================================
SELECT *
INTO #EMPLOYEENAME
FROM
  (
    SELECT DISTINCT employee AS empid
    FROM #YESTERDAY AS gettingthename
  ) AS employeename


-- now make an index
IF EXISTS 
  (
    SELECT name
    FROM sys.indexes  
    WHERE name = N'IX_THENAME'
  )     
DROP INDEX IX_THENAME ON #EMPLOYEENAME
  
  
CREATE NONCLUSTERED INDEX IX_THENAME
ON #EMPLOYEENAME (empid)


SELECT *
INTO #EMP
FROM
  (
    SELECT 
      ID
      ,LastName
      ,FirstName
      ,User1
    FROM  REDACTED_CLOUD_01.CM01.dbo.EMPLOYEE AS EMPLOYEE
    WHERE ID IN (SELECT empid FROM #EMPLOYEENAME)
  ) AS emp



--===================================
-- Now put it together I suppose
--===================================

SELECT *
INTO #CHARGEOUTINTERFACE
FROM
  (
    SELECT
      [COMPANY]
      ,[CHARGED_QTY]
      ,[WAREHOUSE]
      ,[BIN]
      ,[DEPARTMENT]
      ,[CHARGED_DATE]
      ,[REDACTED_PART_NUMBER]
      ,[OPERATOR_NAME]
      ,[CREATION_DATETIME]
      ,[TRANSACTION_ID]
      ,[TRANSACTION_TYPE]
      ,[CONSIGNMENT]
    FROM
      (
        SELECT
          'REDACTED' AS [COMPANY]
          -- ok, need some logic to make it so we 
          -- report the DIFFERENCE in the original 
          -- value compared to the XTRAN
          ,CASE 
            WHEN YEST.transnumber IN (SELECT transnumber FROM #SECONDINSIDELOOP)
            THEN 
              CASE 
                WHEN XTRANSORTED.Scenario = 1 THEN XTRANSORTED.ShiftedTransQty
                ELSE XTRANSORTED.DifferenceQty
                END
            ELSE YEST.quantity
            END AS [CHARGED_QTY]
          ,CASE WHEN STA.Crib = 3605 THEN 'JAN' ELSE 'MRO' END AS [WAREHOUSE]
          ,STA.Bin AS [BIN]
          ,CASE
            WHEN (YEST.TypeDescription = 'ADJUS' OR YEST.TypeDescription = 'COUNT') THEN 'REDACTED'
            WHEN YEST.User1 IS NULL THEN EMP.User1 
            WHEN YEST.User1 = '' THEN EMP.User1
            ELSE YEST.User1 
            END AS [DEPARTMENT]
          ,FORMAT(CAST(YEST.Transdate AS DATE), 'yyyy-MM-dd') AS [CHARGED_DATE]
          ,INV.UDFREDACTEDPartReference AS [REDACTED_PART_NUMBER]
          ,UPPER(EMP.LastName) AS [OPERATOR_NAME]
          ,FORMAT(CAST(GETDATE() AS DATE), 'yyyy-MM-dd') AS [CREATION_DATETIME]
          ,CASE WHEN YEST.TypeDescription = 'XTRAN' THEN YEST.BatchId ELSE YEST.transnumber END AS [TRANSACTION_ID]
          ,CASE 
            WHEN YEST.TypeDescription = 'XTRAN' THEN 'U' 
            WHEN YEST.TypeDescription = 'ISSUE' THEN 'I'
            WHEN YEST.TypeDescription = 'COUNT' THEN 'I'
            WHEN YEST.TypeDescription = 'ADJUS' THEN 'I'
            WHEN YEST.TypeDescription = 'CANCL' THEN 'D' 
            ELSE ''
            END AS [TRANSACTION_TYPE]
          ,STA.Consignment AS [CONSIGNMENT]
        FROM
          #YESTERDAY AS YEST INNER JOIN
          #INV AS INV ON YEST.Item = INV.ItemNumber INNER JOIN
          #STA AS STA ON YEST.CribBin = STA.CribBin INNER JOIN
          #EMP AS EMP ON YEST.employee = EMP.ID LEFT OUTER JOIN
          #SECONDINSIDELOOP AS XTRANSORTED ON YEST.transnumber = XTRANSORTED.transnumber
      ) AS blah
    WHERE
      --case statement for a where clause?
      CASE WHEN TRANSACTION_TYPE = 'U' AND CHARGED_QTY = 0 THEN 1 ELSE 0 END = 0 AND WAREHOUSE <> 'JAN'
  ) AS chargeoutinterface


-- now we remove any rows with anything to do with 
-- any RECVE transactions...pretty sure we already 
-- got them, but for good measure
DELETE FROM #CHARGEOUTINTERFACE
WHERE TRANSACTION_ID IN (SELECT transnumber FROM #JUSTRECVE)


SELECT *
FROM #CHARGEOUTINTERFACE


END

A batch job scheduled in Windows Task Scheduler would run this daily to output a csv file that would be fed to a Bash script running on a Debian Linux VM to deposit the file on the customer's FTP server, along with its brother, the ReceiveInterface which followed the same general format.

PIV & Pedestrian Peril C# Game

Philip Scott Barrowcliffe — Sun, 03 Apr 2022 19:10:19 GMT

C# and Teensy code after the video.

As part of adding value for one of our larger integration customers, Honda of North America, we would put on safety training events at the Honda plants peppered throughout Ohio. The events consisted of various booths that would have games the associates could participate in to reinforce safety concepts and habits pertinent to an industrial manufacturing environment.

Our group would consult with Honda about what safety topics to focus on and we would come up with games and training scenarios that would be fun, educational, and challenging for the associates. One such topic was personal industrial vehicle and pedestrian safety within the plants.

I was in charge of coming up with the graphics and posters for these events, but I wanted to challenge myself and come up with something that would take an upcoming event to the next level. I got the thought in my head that a Jeopardy clone would be something fun for me to work on as well as fun for the associates attending the training event.

So I wrote a Jeopardy game in C#, molded to fit the 'flow' of the event in which groups of associates would visit each booth, participate in the game at the booth, and be awarded points - each group vying for the top score for extra prizes.

The game basically runs on a loop (with a secret reset button in case anything goes wrong), fed by a text files containing the question set (allowing for different question sets in the future) as well as text files for the categories and the point values for each question, while keeping track of what questions were answered incorrectly so that management could follow up on topics that might need further attention after the event.

For extra fun, my IT associate found a relay that could be triggered by wireless 'no-battery-required' buttons (by virtue of a charge being generated by the press of the button sending a signal to the relay) in order to control the game. Each person in the group of four associates would get a button with a color that matched an answer choice on the screen of the game. Attached to the relay was a Teensy 3.2 microcontroller that I programmed to act as an HID, accepting button presses from the relay and passing key presses to the game to answer the questions.

Originally the game was called 'Pedestrians in Jeopardy', but the local printers refused to print my original poster for the game over copyright fears. So I was forced to redesign the poster (the title screen of the game) for the new, dry title lol

0:00

PIV & Pedestrian Peril in action.

/* 
  Buttons to USB Keyboard Example used as a starting point
  Also includes logic to blink the Teensy's onboard LED to indicated 
  a button press, for testing the wireless buttons and relay
*/

#include 
#include 

int state; // 0 for default 'nothing' state 1 for A 2 for B 3 for C 4 for D
int prevState; // hold the last state for comparison
int ApinVal, BpinVal, CpinVal, DpinVal; // to hold pin readings
bool doUpdate; //true if the state has changed, false if the state is the same as the last check
bool doBlink;
long interval = 1000;
long previousMillis = 0;
int ledState = LOW;
int ledPin = 13;
int Apin = 0;
int Bpin = 1;
int Cpin = 2;
int Dpin = 3;
int bounceVal = 10;
Bounce aButton = Bounce(Apin,bounceVal);
Bounce bButton = Bounce(Bpin,bounceVal);
Bounce cButton = Bounce(Cpin,bounceVal);
Bounce dButton = Bounce(Dpin,bounceVal);

void setup() {
    pinMode(Apin, INPUT_PULLUP);
    pinMode(Bpin, INPUT_PULLUP);
    pinMode(Cpin, INPUT_PULLUP);
    pinMode(Dpin, INPUT_PULLUP);
    pinMode(ledPin,OUTPUT);
}

void loop() {
    readPins();
    updateState();
    doStateAction();
}


void readPins() {
  aButton.update();
  bButton.update();
  cButton.update();
  dButton.update();
}

void updateState() {

  if(aButton.risingEdge()) {
    state = 1;
    ledState = HIGH;
  } else if(bButton.risingEdge()) {
    state = 2;
    ledState = HIGH;
  } else if(cButton.risingEdge()) {
    state = 3;
    ledState = HIGH;
  } else if(dButton.risingEdge()) {
    state = 4;
    ledState = HIGH;
  } else {
    state = 0;
    ledState = LOW;
  }

  digitalWrite(ledPin, ledState);

  if(state != prevState) { //if the state has changed, then do a thing based on the state.
    doUpdate = true;
    doBlink = false;
  }
}


void doStateAction() {
  if(doUpdate) {
    switch(state) {
      case 0: {
        interval = 100;
        break;
      }   
      case 1: {
        Keyboard.print("a");  //send the letter 'a' to the game
        interval = 1000;
        break;
      }           
      case 2: {
        Keyboard.print("b");  //send the letter 'b' to the game
        interval = 1000;
        break;
      }
      case 3: {
        Keyboard.print("c");  //send the letter 'c' to the game
        interval = 1000;
        break;
      }
      case 4: {
        Keyboard.print("d");  //send the letter 'd' to the game
        interval = 1000;
        break;
      }
    }
    
    prevState = state;
    doUpdate = false; 
    doBlink = true;  
  }
}

TeensyHID.ino

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Drawing;

namespace PIJ 
/// PIJ because the game was originally called Pedestrians in Jeopardy
{

    public partial class MainWindow : Window
    {
        System.Drawing.Color pijBlue = ColorTranslator.FromHtml("#001fb2");
        String TitleImagePath = "/Resources/image/titleimage.png";

        public MainWindow()
        {
            InitializeComponent();
        }

        private void StartButton_Click(object sender, RoutedEventArgs e)
        {
            GameBoard theGameBoard = new GameBoard();

            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            theGameBoard.BeginAnimation(PIJQuestionWindow.OpacityProperty, animFadeIn);
            theGameBoard.Show();
        }
    }
}

MainWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Drawing;

namespace PIJ
{
    public partial class GameBoard : Window
    {
        System.Drawing.Color pijBlue = ColorTranslator.FromHtml("#001fb2");
        List> clues = new List>();
        public ScoreBoard theScore = new ScoreBoard();
        System.Windows.Threading.DispatcherTimer dispatcherTimer = new System.Windows.Threading.DispatcherTimer();

        // flag to determine if all the other question windows etc are closed.  0 means other windows are open, 1 means they are all closed.
        public int allWindowsClosedFlag = 1;

        // number of questions to be answered, when this reaches 0, the game can be over
        public int questionCounter;

        System.Windows.Threading.DispatcherTimer waitForQuestionsToBeOverTimer = new System.Windows.Threading.DispatcherTimer();

        public SoundBoard ThePlayer { get; set; }
        public PIJQuestionWindow ClueWindow { get; private set; }

        public GameBoard()
        {
            InitializeComponent();

            questionCounter = 9;

            MakeRandomClues();
            FillBoard();
            CreateTimer();
            CreateWindowWaitTimer();
            ThePlayer = new SoundBoard();
            ThePlayer.PlayTheSound("RoundStart");
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            string nameOfSender = ((Button)sender).Name.ToString();
            int row = Grid.GetRow((Button)sender) - 2;
            int col = Grid.GetColumn((Button)sender);

            ClueWindow = null;

            // subtract one from the questionCounter variable
            questionCounter -= 1;

            Button theButton = (Button)sender;
            theButton.IsEnabled = false;

            Question theClue = new Question();
            theClue = clues[col][row];

            if (!theClue.IsPictureQuestion)
            {
                ClueWindow = new QuestionWindow(theClue, this)
                {
                    AllowsTransparency = true
                };
            }
            else
            {
                ClueWindow = new PictureQuestionWindow(theClue, this)
                {
                    AllowsTransparency = true
                };
            }

            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            // the next two lines clear the text of the button that has already been pressed.
            TextBlock theText = FindVisualChild((Button)sender);
            theText.Text = System.String.Empty;

            ClueWindow.BeginAnimation(PIJQuestionWindow.OpacityProperty, animFadeIn);
            ClueWindow.Show();

            //Question Window is now open, so set to 0, indicating that all windows are not closed
            allWindowsClosedFlag = 0;

        }

        //options button to be implemented later
        private void Options_Click(object sender, RoutedEventArgs e)
        {
            var OptionsWindow = new Options(ClueWindow, this, theScore.Score);
            OptionsWindow.ShowDialog();
        }

        private void FillCat()
        {
            List cat = FileReader.ReadTheFile(FileReader.pathToCategories);
            Category_0.Text = cat.ElementAt(0);
            Category_1.Text = cat.ElementAt(1);
            Category_2.Text = cat.ElementAt(2);
        }

        private void FillBoard()
        {
            List board = FileReader.ReadTheFile(FileReader.pathToBoard);
            TB0_2.Text = board.ElementAt(0);
            TB1_2.Text = board.ElementAt(0);
            TB2_2.Text = board.ElementAt(0);

            TB0_3.Text = board.ElementAt(1);
            TB1_3.Text = board.ElementAt(1);
            TB2_3.Text = board.ElementAt(1);

            TB0_4.Text = board.ElementAt(2);
            TB1_4.Text = board.ElementAt(2);
            TB2_4.Text = board.ElementAt(2);

            Score.Text = string.Format("{0:0.00}", theScore.Score);
        }

        public void MakeClues()
        {
            clues = FileReader.MakeTheClues(FileReader.pathToClues);
        }

        public void MakeRandomClues()
        {
            List temp = QuestionSetMaker.ReadClueFileToTempArray(FileReader.pathToClues);

            clues = QuestionSetMaker.getCluesFromList(QuestionSetMaker.pickRandomCategories(QuestionSetMaker.categories, 3), temp);

            Category_0.Text = QuestionSetMaker.categories.ElementAt(0);
            Category_1.Text = QuestionSetMaker.categories.ElementAt(1);
            Category_2.Text = QuestionSetMaker.categories.ElementAt(2);
        }

        private static TextBlock FindVisualChild(DependencyObject parent) where TextBlock : DependencyObject
        {
            for (int i = 0; i < VisualTreeHelper.GetChildrenCount(parent); i++)
            {
                DependencyObject child = VisualTreeHelper.GetChild(parent, i);
                if (child != null && child is TextBlock)
                    return (TextBlock)child;
                else
                {
                    TextBlock childOfChild = FindVisualChild(child);
                    if (childOfChild != null)
                        return childOfChild;
                }
            }
            return null;
        }

        public void CreateTimer()
        {
            dispatcherTimer.Tick += new EventHandler(dispatcherTimer_Tick);
            dispatcherTimer.Interval = new TimeSpan(0, 0, 120);
            dispatcherTimer.Start();
        }

        public void CreateWindowWaitTimer()
        {
            waitForQuestionsToBeOverTimer.Tick += new EventHandler(waitForQuestionsToBeOverTimer_Tick);
            waitForQuestionsToBeOverTimer.Interval = new TimeSpan(0, 0, 1);
        }

        private void waitForQuestionsToBeOverTimer_Tick(object sender, EventArgs e)
        {
            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            waitForQuestionsToBeOverTimer.Stop();

            CheckWindowsClosedFlag(animFadeIn);
        }

        private void dispatcherTimer_Tick(object sender, EventArgs e)
        {
            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            dispatcherTimer.Stop();

            CheckWindowsClosedFlag(animFadeIn);
        }

        private void OpenGameOverWindow(System.Windows.Media.Animation.DoubleAnimation animFadeIn)
        {
            GameOver GameOverWindow = null;

            GameOverWindow = new GameOver(theScore.Score, this)
            {
                AllowsTransparency = true
            };

            GameOverWindow.BeginAnimation(GameOver.OpacityProperty, animFadeIn);
            GameOverWindow.Show();

        }

        private void CheckWindowsClosedFlag(System.Windows.Media.Animation.DoubleAnimation animFadeIn)
        {
            if (allWindowsClosedFlag == 1) // if all the other windows are closed
            {
                // open gameover window
                OpenGameOverWindow(animFadeIn);
            }
            else
            {
                // start wait timer and check again
                waitForQuestionsToBeOverTimer.Start();
            }
        }

        public void CloseTheGame()
        {
            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            // stop the game timer
            dispatcherTimer.Stop();

            // open gameover window
            OpenGameOverWindow(animFadeIn);
        }

        public void CloseTheGame(int fromOptions)
        {
            System.Windows.Media.Animation.DoubleAnimation animFadeIn = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeIn.From = 0;
            animFadeIn.To = 1;
            animFadeIn.Duration = new Duration(TimeSpan.FromSeconds(.4));

            // stop the game timers
            dispatcherTimer.Stop();
            waitForQuestionsToBeOverTimer.Stop();

            // open gameover window
            OpenGameOverWindow(animFadeIn);
        }

        public void ResetTheGame()
        {
            // stop the game timers
            dispatcherTimer.Stop();
            waitForQuestionsToBeOverTimer.Stop();
        }
    }
}

GameBoard.xmal.cs

GameBoard.xaml

using System;
using System.Collections.Generic;
using System.Linq;
using System.Media;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace PIJ
{
    public partial class QuestionWindow : PIJQuestionWindow
    {
        public QuestionWindow(Question theClue, GameBoard theGameBoard)
        {
            InitializeComponent();
            TheClue = theClue;
            TheGameBoard = theGameBoard;
            SetUpAnimation();
            StartQuestionTimer();

            ClueText.Text = theClue.Clue;
            AnswerA.Text = theClue.A;
            AnswerB.Text = theClue.B;
            AnswerC.Text = theClue.C;
            AnswerD.Text = theClue.D;
        }
    }
}

QuestionWindow.xaml.cs

QuestionWindow.xaml

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace PIJ
{
    public partial class PictureQuestionWindow : PIJQuestionWindow
    {
        public PictureQuestionWindow(Question theClue, GameBoard theGameBoard)
        {
            InitializeComponent();
            TheClue = theClue;
            TheGameBoard = theGameBoard;
            ClueImage.Source = new BitmapImage(new Uri(TheClue.PathToImage.ToString(), UriKind.Relative));
            //ClueImage.Source = new BitmapImage(new Uri(TheClue.PathToImage, UriKind.Relative));
            SetUpAnimation();
            StartQuestionTimer();
            ClueText.Text = theClue.Clue;
            AnswerA.Text = theClue.A;
            AnswerB.Text = theClue.B;
            AnswerC.Text = theClue.C;
            AnswerD.Text = theClue.D;
        }
    }
}

PictureQuestionWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PIJ
{
    public class Question
    {
        public Question()
        {
            Category = System.String.Empty;
            Clue = System.String.Empty;
            A = System.String.Empty;
            B = System.String.Empty;
            C = System.String.Empty;
            D = System.String.Empty;
            CorrectAnswer = System.String.Empty;
            Points = 0;
            IsPictureQuestion = false;
            PathToImage = System.String.Empty;
        }

        public string Category { get; set; }
        public string Clue { get; set; }
        public string A { get; set; }
        public string B { get; set; }
        public string C { get; set; }
        public string D { get; set; }
        public string CorrectAnswer { get; set; }
        public decimal Points { get; set; }
        public bool IsPictureQuestion { get; set; }
        public String PathToImage { get; set; }
    }
}

Question.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Windows.Threading;

namespace PIJ
{
    public partial class AnswerWindow : Window
    {
        System.Windows.Threading.DispatcherTimer dispatcherTimer = new System.Windows.Threading.DispatcherTimer();
        PIJQuestionWindow CurrentQuestion;
        GameBoard TheGameBoard;

        public AnswerWindow(PictureQuestionWindow pictureQuestionWindow)
        {
            InitializeComponent();
            CreateTimer();
        }

        // constructor ought to accept both the regular question window and the picture question window
        public AnswerWindow(PIJQuestionWindow theCurrentQuestionWindow, GameBoard theGameBoard)
        {
            InitializeComponent();

            CurrentQuestion = theCurrentQuestionWindow;
            TheGameBoard = theGameBoard;

            CreateTimer();
        }

        public AnswerWindow(PictureQuestionWindow theCurrentQuestionWindow, GameBoard theGameBoard)
        {
            InitializeComponent();

            CurrentQuestion = theCurrentQuestionWindow;
            TheGameBoard = theGameBoard;

            CreateTimer();
        }

        private void dispatcherTimer_Tick(object sender, EventArgs e)
        {
            this.Close();
            CurrentQuestion.Close();
        }

        private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            Closing -= Window_Closing;
            e.Cancel = true;

            System.Windows.Media.Animation.DoubleAnimation animFadeOut = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeOut.From = 1;
            animFadeOut.To = 0;
            animFadeOut.Duration = new Duration(TimeSpan.FromSeconds(.5));
            animFadeOut.Completed += (s, _) => this.Close();

            this.BeginAnimation(UIElement.OpacityProperty, animFadeOut);
            dispatcherTimer.Stop();
            TheGameBoard.Score.Text = string.Format("{0:00.00}", TheGameBoard.theScore.Score);
            TheGameBoard.allWindowsClosedFlag = 1;

            if (TheGameBoard.questionCounter == 0)
            {
                TheGameBoard.CloseTheGame();
            }
        

        public void CreateTimer()
        {
            dispatcherTimer.Tick += new EventHandler(dispatcherTimer_Tick);
            dispatcherTimer.Interval = new TimeSpan(0, 0, 5);
            dispatcherTimer.Start();
        }
    }
}

AnswerWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PIJ
{
    public class ScoreBoard
    {
        public ScoreBoard()
        {
            Score = 0;
            Correct = 0;
            Incorrect = 0;
        }
        
        public decimal Score { get; set; }
        public int Correct { get; set; }
        public int Incorrect { get; set; }
    }
}

ScoreBoard.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Media;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.IO;
using System.Resources;
using System.Diagnostics;

namespace PIJ
{
    public class SoundBoard
    {
        SoundPlayer thePlayer = null;
        String thePath = System.String.Empty;

        public SoundBoard ()
        {
            thePlayer = new SoundPlayer();
        }

        // paths to the sounds
        public String BuzzIn = "Resources\\sound\\JeopardyBuzzin.wav";
        public String Correct = "Resources\\sound\\JeopardyCorrect.wav";
        public String DailyDouble = "Resources\\sound\\JeopardyDailyDouble.wav";
        public String FinalReveal = "Resources\\sound\\JeopardyFinalCategoryReveal.wav";
        public String NoAnswer = "Resources\\sound\\JeopardyNoAnswer.wav";
        public String RoundTimeOver = "Resources\\sound\\JeopardyRoundOutOfTime.wav";
        public String RoundStart = "Resources\\sound\\JeopardyStartOfRound.wav";
        public String WrongAnswer = "Resources\\sound\\JeopardyWrongAnswer.wav";

        public void PlayTheSound(String type)
        {
            switch (type)
            {
                case "BuzzIn":
                    thePath = BuzzIn;
                    break;
                case "Correct":
                    thePath = Correct;
                    break;
                case "DailyDouble":
                    thePath = DailyDouble ;
                    break;
                case "FinalReveal":
                    thePath = FinalReveal;
                    break;
                case "NoAnswer":
                    thePath = NoAnswer;
                    break;
                case "RoundTimeOver":
                    thePath = RoundTimeOver;
                    break;
                case "RoundStart":
                    thePath = RoundStart;
                    break;
                case "WrongAnswer":
                    thePath = WrongAnswer;
                    break;
            }
        
            thePlayer.SoundLocation = thePath;
            thePlayer.Load();
            thePlayer.Play();
        }
    }
}

SoundBoard.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Windows.Threading;

namespace PIJ
{
    public partial class GameOver : Window
    {
        public SoundBoard ThePlayer { get; set; }
        public GameBoard TheGameBoard { get; set; }

        public DoubleAnimation animFadeIn { get; set; }

        public decimal TheScore { get; set; }

        System.Windows.Threading.DispatcherTimer windowCloseTimer = new System.Windows.Threading.DispatcherTimer();
        System.Windows.Threading.DispatcherTimer gameBoardCloseTimer = new System.Windows.Threading.DispatcherTimer();

        public GameOver(decimal score, GameBoard theGameBoard)
        {
            InitializeComponent();
            CreateTimer();

            this.AllowsTransparency = true;

            ThePlayer = new SoundBoard();
            TheScore = score;
            TheGameBoard = theGameBoard;

            DoScoreStuff();
            ThePlayer.PlayTheSound("RoundTimeOver");
        }

        private void DoScoreStuff()
        {
            FinalScore.Text = string.Format("{0:0.00}", TheScore);
            FileWriter.ScoreWriteToFile(TheScore.ToString(), DateTime.Now.ToString());
        }

        private void WindowCloseTimer_Tick(object sender, EventArgs e)
        {
            System.Windows.Media.Animation.DoubleAnimation animFadeOut = new System.Windows.Media.Animation.DoubleAnimation();
            animFadeOut.From = 1;
            animFadeOut.To = 0;
            animFadeOut.Duration = new Duration(TimeSpan.FromSeconds(.5));
            animFadeOut.Completed += (s, _) => this.Close();

            this.BeginAnimation(UIElement.OpacityProperty, animFadeOut);
            windowCloseTimer.Stop();
        }

        protected void GameBoardCloseTimer_Tick(object sender, EventArgs e)
        {
            TheGameBoard.Close();
            gameBoardCloseTimer.Stop();
        }

        public void CreateTimer()
        {
            gameBoardCloseTimer.Tick += new EventHandler(GameBoardCloseTimer_Tick);
            gameBoardCloseTimer.Interval = new TimeSpan(0, 0, 1);
            gameBoardCloseTimer.Start();

            windowCloseTimer.Tick += new EventHandler(WindowCloseTimer_Tick);
            windowCloseTimer.Interval = new TimeSpan(0, 0, 12);
            windowCloseTimer.Start();
        }
    }
}

GameOver.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace PIJ
{
    public partial class Options : Window
    {
        PIJQuestionWindow CurrentQuestion;
        GameBoard TheGameBoard;

        public decimal TheScore { get; set; }

        public Options(PIJQuestionWindow theCurrentQuestionWindow, GameBoard theGameBoard, decimal score)
        {
            InitializeComponent();

            TheScore = score;
            CurrentQuestion = theCurrentQuestionWindow;
            TheGameBoard = theGameBoard;
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            PIJMessageBox message = new PIJMessageBox();
            bool? result = message.ShowDialog();

            if (result ?? true) {
                FileWriter.ResetWriteToFile(TheScore.ToString(), DateTime.Now.ToString());
                TheGameBoard.ResetTheGame();
                TheGameBoard.Close();
                this.Close();
            }
            else {
                this.Close();
            }            
        }

        private void Cancel_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }
    }
}

Optons.xaml.cs

Options.xaml

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace PIJ
{
    public partial class PIJMessageBox : Window
    {
        public PIJMessageBox()
        {
            InitializeComponent();
        }

        private void OK_Click(object sender, RoutedEventArgs e)
        {
            this.DialogResult = true;
        }

        private void Cancel_Click(object sender, RoutedEventArgs e)
        {
            this.DialogResult = false;
        }
    }
}

MessageBox.xaml.cs

MessageBox.xaml

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PIJ
{
    public class FileReader
    {
        List categories = new List();
        List questions = new List();
        List answers = new List();
        List boardSquares = new List();

        public static string dir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

        public static String pathToCategories = dir + @"\text\pij_cat.txt";
        public static String pathToClues = dir + @"\text\pij_clues.txt";
        public static String pathToBoard = dir + @"\text\pij_board.txt";

        // method to read the text files and add the stuff to a list
        public static List ReadTheFile(String thePath)
        {
            List data = new List();
            using (StreamReader theFile = new StreamReader(thePath))
            {
                while (theFile.Peek() >= 0)
                {
                    data.Add(theFile.ReadLine());
                }
            }
            return data;
        }

        // old
        public static List> MakeTheClues (String thePath)
        {
            List> clueSet = new List>();
            int i;
            int j;
            int points= 0;
            int twoHundred = 200;

            using (StreamReader theFile = new StreamReader(thePath))
            {
                //string line;
                //while ((line = theFile.ReadLine()) != null)
                    while (theFile.Peek() >= 0)
                {
                    // create the clue set, six columns of 5 clues
                    for (i = 0; i < 6; i++)
                    {
                        points = twoHundred;
                        List row = new List();
                        // create a row of clues
                        for (j = 0; j < 5; j++)
                        {
                            // do the actual populating of the Question object
                            Question aClue = new Question();
                            aClue.Clue = theFile.ReadLine();
                            aClue.A = theFile.ReadLine();
                            aClue.B = theFile.ReadLine();
                            aClue.C = theFile.ReadLine();
                            aClue.D = theFile.ReadLine();
                            aClue.CorrectAnswer = theFile.ReadLine();
                            aClue.Points = points;

                            //check for the bool
                            String boolString = theFile.ReadLine();

                            if (boolString == "0")
                            {
                                aClue.IsPictureQuestion = false;
                            }
                            if (boolString == "1")
                            {
                                aClue.IsPictureQuestion = true;
                            }

                            aClue.PathToImage = theFile.ReadLine();

                            row.Add(aClue);
                            points += twoHundred;
                        }
                        clueSet.Add(row);
                    }
                }
            }
                return clueSet;
        }
    }    
}

FileReader.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PIJ
{
    public static class FileWriter
    {
        public static string dir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

        private static String PathToWrongAnswerFile = dir + @"\text\pij_wronganswers.txt";
        private static String PathToCorrectAnswerFile = dir + @"\text\pij_correctanswers.txt";
        private static String PathToScore = dir + @"\text\pij_scores.txt";
        private static String PathToReset = dir + @"\text\pij_resets.txt";


        public static void WrongAnswerWriteToFile(String theClue, String thePlayerAnswer, string time)
        {
            System.IO.StreamWriter writer;
            writer = new System.IO.StreamWriter(PathToWrongAnswerFile, true);

            writer.WriteAsync(time + ";");
            writer.WriteAsync(theClue + ";");
            writer.WriteAsync(thePlayerAnswer + ";");
            writer.WriteLineAsync("");
            writer.Close();
        }

        public static void CorrectAnswerWriteToFile(String theClue,String thePlayerAnswer, string time)
        {
            System.IO.StreamWriter writer;
            writer = new System.IO.StreamWriter(PathToCorrectAnswerFile, true);
            
            writer.WriteAsync(time + ";");
            writer.WriteAsync(theClue + ";");
            writer.WriteAsync(thePlayerAnswer + ";");
            writer.WriteLineAsync("");
            writer.Close();
        }

        public static void ScoreWriteToFile(String theScore, string time)
        {
            System.IO.StreamWriter writer;
            writer = new System.IO.StreamWriter(PathToScore, true);

            writer.WriteAsync(time + ";");
            writer.WriteAsync(theScore + ";");
            writer.WriteLineAsync("");
            writer.Close();
        }

        public static void ResetWriteToFile(String theScore, string time)
        {
            System.IO.StreamWriter writer;
            writer = new System.IO.StreamWriter(PathToReset, true);
            writer.WriteAsync("Reset;");
            writer.WriteAsync(time + ";");
            writer.WriteAsync(theScore + ";");
            writer.WriteLineAsync("");
            writer.Close();
        }
    }
}

FileWriter.cs

Sheesh that's a ton of code for a silly little Jeopardy clone.

Processing Door Sign

Philip Scott Barrowcliffe — Sat, 02 Apr 2022 22:38:45 GMT

Teensy and Processing code after the video.

So this was my portion of a prototype 'miniature' RFID automated store room. The prototype was designed to take the functionality of the full-sized CribMaster Accuport, strip the mats out of the process, and replace them with buttons in order to allow the contraption to operate with a smaller footprint. This also meant that we really needed to make it so only one person was in the cage at a time. What better way than a colorful sign that asks people to wait their turn?

The door sign runs on a tiny Rapsberry-Pi-like SOC, the Asus Tinkerboard, chosen for its beefier cpu power over that of a regular Raspberry Pi. To transmit the 'state' of the RFID machine to the Tinkerboard, I programmed a Teensy 3.2 microcontroller to act as an HID device in order to receive signals from a relay and then send key presses to the Tinkerboard to manipulate the states of the door sign.

I had a lot of fun diving into Processing, though I really had to strip back my ambitions for the project due to the limits of the SOC running things. Having the ability to import SVG files to manipulate was exciting, but things slow way down when you start animating hundreds of vertices. However, simple shapes can also be a good time.

0:00

Processing Door Sign in action.

Teensy 3.2 Code:

/* 
  Based on the Buttons to USB Keyboard Example
*/

#include 

Bounce button1 = Bounce(4, 10);  
Bounce button2 = Bounce(11, 10);  

void setup() {
  pinMode(4, INPUT_PULLDOWN);  
  pinMode(11, INPUT_PULLDOWN);
}

void loop() {

  button1.update();
  button2.update();

  if (button1.risingEdge()) {
    Keyboard.println("o");
  }
  if (button2.risingEdge()) {
    Keyboard.println("c");
  }
}

Processing Code:

//mcadoorsign_release_v1

DoorSignController ds;


void setup() {
  size(1280,720,JAVA2D);
  noCursor();
  ds = new DoorSignController();
}

void draw() {
  ds.loopDeLoop();
}

void keyPressed() { 
  ds.currentKey = key;
    
  if(ds.currentKey == ds.lastKey) {
    ds.counter++;
  } else {
    ds.counter = 0;
  }
    
  if(ds.counter == 0) {
    if ((key == 'o') || (key == 'O')) {
    //Matt's relay has sent the available signal
      ds.state = 1;
    }
      
    if ((key == 'c') || (key == 'C')) {
    //Matt's relay has sent the unavailable signal
      ds.state = 3;
    }
      
    ds.lastKey = key;
      
    if(ds.state != ds.lastState) {
      ds.toUpdate = true;
      ds.lastState = ds.state;
    } else {
      ds.toUpdate = false;
    }
  }
}

mcadoorsign_release_v1.pde

class AnimationTimer {
  
  // class variables
  float start, runtime = 0;
  
  //constructor
  AnimationTimer() {
    start = millis();
  }
  
  //methods
  void update() {
    runtime = millis() - start;
  }
  
  float getRuntime() {
    return runtime;    
  }
  
  void setStart() {
    start = millis();
  }
  
  float getStart() {
    return start;
  }
}

AnimationTimer.pde

class BackgroundController_new {
  int counter;
  int state = 0; 
  int mainAlpha = 255;
  int x,y = 0;
  int cols = 20;
  int rows = 11;
  int size;
  color green = #207528;
  color red = #530000;  
  color backgroundColor;
  int wipingCounter;
  int waitingCounter;
  boolean wiping,holding;
  int k = 0;
  int lastK = 0;
  ColorSquare[][] grid;
  
  //constructor
  BackgroundController_new(int[][] green, int[][] red, int[][][] alt) {
    backgroundColor = color(0,0,0);
    holding = true;
    wiping = false;
    wipingCounter = 1;
    waitingCounter = 1;
    size = (int)sqrt(((height/width)*width/height)*cols);
    grid = new ColorSquare[cols][rows];
    
    for(int i = 0; i < cols; i++) {
      for(int j = 0; j < rows; j++) {
        int x = (int)(i*width/(cols*1.0));
        int y = (int)(j*height/(rows*1.0));
        grid[i][j] = new ColorSquare(55,x+4,y+4,0,green,red,alt);
      }
    }
  }
  
  //methods
  void update() {
    drawOnCanvas();
  }

  void drawOnCanvas() {
  
    if(state == 0) { // holding in green
      holding = true;
      wiping = false;
      drawHolding();
    }
    
    if(state == 1) { // holding in red
      holding = true;
      wiping = false;
      drawHolding();
    }
    
    if(state == 2) { // wiping to red
      holding = false;
      wiping = true;
      drawWipingToRed();
    }
    
    if(state == 3) { // wiping to green
      holding = false;
      wiping = true;
      drawWipingToGreen();
    }
    
    if(state == 4) { // wiping to alt color
      holding = false;
      wiping = true;
      drawWipingToAlt();
    }
    
    if(state == 5) { // holding alt
      holding = true;
      wiping = false;
      drawHolding();
    }
  }
  
  void drawHolding() {
    for(int i = 0; i < cols; i++) {
      for(int j = 0; j < rows; j++) {
        grid[i][j].holding();
        fill(grid[i][j].C1,grid[i][j].C2,grid[i][j].C3);
        rect(grid[i][j].xPos,grid[i][j].yPos,grid[i][j].size,grid[i][j].size);
      }
    }
  }
  
  void drawWipingToRed() {
    for(int i = 0; i < cols; i++) {
      for(int j = 0; j < rows; j++) {
        grid[i][j].becomeRed();
      }
    }
    state = 2;
    drawHolding();
  }
  
  void drawWipingToGreen() {
    for(int i = 0; i < cols; i++) {
      for(int j = 0; j < rows; j++) {
        grid[i][j].becomeGreen();
      }
    }
    state = 0;
    drawHolding();
  }
  
  void drawWipingToAlt() {
    for(int i = 0; i < cols; i++) {
      for(int j = 0; j < rows; j++) {
        grid[i][j].becomeAlt(k);
      }
    }
    state = 5;
    drawHolding();
  }
  
  int makeRandomIndex() {
    k = (int)random(0,grid[0][0].alts.length);
    if(k == lastK) {
      makeRandomIndex();
    } else {
      lastK = k;
    }    
    return k;  
  }
}

BackgroundController.pde

class ColorSquare {
  
  int size;
  int xPos;
  int yPos;
  int C1;
  int C2;
  int C3;
  int newC1;
  int newC2;
  int newC3;
  int[][] greens;
  int[][] reds;
  int[][][] alts;
  int last;
  int state = 0;
  int type; //0 is a greeney, 1 is a reddy
  boolean start,fading,changing;
  int greenCounter;
  int redCounter;
  int fadeCounter;
  int fadeSteps;
  int c1FadeAmt = 0;
  int c2FadeAmt = 0;
  int c3FadeAmt = 0;
  int clrNmbr;
  
  //constructor
  ColorSquare(int sz, int x, int y, int t, int[][] gr, int[][] rd, int[][][] alt) {
    size = sz;
    xPos = x;
    yPos = y;
    type = t;
    greens = gr;
    reds = rd;
    alts = alt;
    fadeSteps = (int)random(45,75);
    fadeCounter = 0;
    start = true;
    fading = false;
    changing = true;
    last = (int)random(0,2);
    
    if(type == 0) {
      pickRandomGreen();
      start = false;
      pickRandomGreen();    
    }
    
    if(type == 1) {
      pickRandomRed();
      start = false;
      pickRandomRed();
    }
    
    if(type == 2) {
      pickRandomAlt();
      start = false;
      pickRandomAlt();
    }
  }
  
  //methods
  void pickRandomGreen() {
    int i = (int)random(0,greens.length);
    if(i != last) {
      if(start) {
        C1 = greens[i][0];
        C2 = greens[i][1];
        C3 = greens[i][2];
      } else {
        newC1 = greens[i][0];
        newC2 = greens[i][1];
        newC3 = greens[i][2];
      }
      last = i;
    } else {
      pickRandomGreen();
    }
  }
  
  void pickRandomRed() {
    int i = (int)random(0,reds.length);
    if(i != last) {
      if(start) {
        C1 = reds[i][0];
        C2 = reds[i][1];
        C3 = reds[i][2];
      } else {
        newC1 = reds[i][0];
        newC2 = reds[i][1];
        newC3 = reds[i][2];
      }
      last = i;
    } else {
      pickRandomRed();
    }
  }
  
  void pickRandomAlt() {
    //int i = (int)random(0,alts.length);
    int j = (int)random(0,alts[clrNmbr].length);
    if(j != last) {
      if(start) {
        C1 = alts[clrNmbr][j][0];
        C2 = alts[clrNmbr][j][1];
        C3 = alts[clrNmbr][j][2];
      } else {
        newC1 = alts[clrNmbr][j][0];
        newC2 = alts[clrNmbr][j][1];
        newC3 = alts[clrNmbr][j][2];
      }
      last = clrNmbr;
    } else {
      pickRandomAlt();
    }
  }
  
  //this is the main loop for the square
  void drawSquare() {
    if(state == 0) {
      holding();
    }
    
    if(state == 1) {
      becomeGreen();
    }
    
    if(state == 2) {
      becomeRed();
    }
    
    if(state == 3) {
      becomeAlt((int)random(0,alts.length - 1));
    }
  }
  
  void holding() {
    if(changing) {
      //get new random color in set
      if(type == 0) {
        pickRandomGreen();
      }
      if(type == 1) {
        pickRandomRed();
      }
      if(type == 2) {
        pickRandomAlt();
      }
      changing = false;
      fading = true;
      fadeSteps = (int)random(45,75);
      holding();
    }
    
    if(fading) {
      C1 = (int)lerp(C1,newC1,0.07);
      C2 = (int)lerp(C2,newC2,0.07);
      C3 = (int)lerp(C3,newC3,0.07);
      
      fadeCounter+=1;
      if(fadeCounter == fadeSteps) {
        fading = false;
        changing = true;
        fadeCounter = 1;
      }
    }
  }
  
  int fadeColor(int in, int amt) {
    return in + amt;
  }
  
  void becomeGreen() {
    type = 0;
  }
  
  void becomeRed() {
    type = 1;
  }
  
  void becomeAlt(int randomClr) {
    clrNmbr = randomClr;
    type = 2;
  }  
}

ColorSquare.pde

class Logo {
  boolean drawFadeIn, drawHold, drawFadeOut, off = false;
  PShape mcaLogo, mca, ind;
  color rectColor = color(0,0,0);
  DoorSign parent;
  PShape grp;
  PShape[] theLetters;
  PShape theMessageShape;
  int alpha = 255;
  float ratio = 0.9;
  int rectAlpha = 0;
  int finalRectAlpha = 229;
  int offCounter;
  int fadingCounter;
  int x,y,targetX,targetY;
  boolean moveUp, moveDown, hold;
  float lerpAmount = 0.06;
  float fadeLerpAmount = 0.2;

  //constructor
  Logo() {
    off = true;
    fadingCounter = 0;
    rectAlpha = (int)(alpha * ratio);
    mcaLogo = loadShape("mcalogo.svg");
    mca = mcaLogo.getChild("MCA");
    ind = mcaLogo.getChild("INDUSTRIAL");
    mca.disableStyle();
    ind.disableStyle();
    x = 0;
    y = 721;
    targetX =0;
    targetY = 389+65;
    hold = true;
    moveUp = false;
    moveDown = false;
  }
  
  //methods
  void draw() {
  
    if(drawFadeIn) {
      fadeIn();
    }
    
    if(drawHold) {
      messageHold();
    }
    
    if(drawFadeOut) {
      fadeOut();
    }  
    
    if(off) {
      off();
    }
  }
  
  void fadeIn() {
    if(fadingCounter == 0) {
      alpha = 0;
      rectAlpha = 0;
    }
    updateFadeCounters();
    float b;
    if(alpha < 255) {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y,width,200);
      noStroke();
      fill(255,255,255,alpha);
      shape(mca,50,y);
      fill(235,34,40,alpha);
      shape(ind,50,y);
      b = (int)lerp(alpha,255,fadeLerpAmount);
      alpha = (int)b;
      if(alpha >= 250) {
        alpha = 255;
      }
      rectAlpha = (int)(alpha * ratio);
      if(rectAlpha > finalRectAlpha) {
        rectAlpha = finalRectAlpha;
      }
    } else {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y,width,200);
      noStroke();
      fill(255,255,255,alpha);
      shape(mca,50,y);
      fill(235,34,40,alpha);
      shape(ind,50,y);   
    }
    
    if(alpha == 255) {
      off = false;
      drawFadeIn = false;
      drawHold = true;
    }
  }
  
  void messageHold() {
    fadingCounter = 0;
    alpha = 255;
    rectAlpha = (int)(alpha * ratio);
    off = false;
    drawFadeIn = false;
    drawFadeOut = false;
    stroke(0,0,0,alpha);
    strokeWeight(4);
    fill(rectColor,rectAlpha);
    rect(0,y,width,200);
    noStroke();
    fill(255,255,255,alpha);
    shape(mca,50,y);
    fill(235,34,40,alpha);
    shape(ind,50,y);
  }
  
  void fadeOut() {
    if(fadingCounter == 0) {
      alpha = 255;
      rectAlpha = (int)(alpha * ratio);
    }
    updateFadeCounters();
    float b;    
    if(alpha > 0) {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y,width,200);
      noStroke();
      fill(255,255,255,alpha);
      shape(mca,50,y);
      fill(235,34,40,alpha);
      shape(ind,50,y);
      b = (int)lerp(alpha,0,fadeLerpAmount);
      alpha = (int)b-1;
      rectAlpha = (int)(alpha * ratio);
    } else {
      alpha = 0;
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y,width,200);
      noStroke();
      fill(255,255,255,alpha);
      shape(mca,50,y);
      fill(235,34,40,alpha);
      shape(ind,50,y);
    }
    
    if(alpha == 0) {
      drawFadeOut = false;
      drawHold = false;
      off = true;
    }
  }
  
  void moveUp() {
    float b;
    if(y > targetY) {
      b = (int)lerp(y,targetY,lerpAmount);
      y = (int)b-1;
    } else {
      moveUp = false;
      hold = true;
      y = targetY;
    }
  }
  
  void moveDown() {
    float b;
    if(y < targetY) {
      b = (int)lerp(y,targetY,lerpAmount);
      y = (int)b+1;
    } else {
      moveDown = false;
      drawHold = false;
      hold = false;
      off = true;
      y = targetY;
    }
  }
  
  
  void updateFadeCounters() {
    fadingCounter+=1;
    
    if(fadingCounter > 5) {
      fadingCounter = 1;
    }
  } 
}

Logo.pde

class Message {
  boolean drawFadeIn, drawHold, drawFadeOut, off = false;
  DoorSign parent;
  PShape grp;
  PShape[] theLetters;
  PShape theMessageShape;
  int alpha;
  int offCounter;
  int x,y,targetX,targetY;
  boolean moveUp, moveDown, hold;
  float lerpAmount = 0.1;
  float fadeLerpAmount = 0.2;
  color rectColor = color(28,28,28);
  int rectSize = 328;
  float ratio = 0.6;
  int rectAlpha;
  int finalRectAlpha = 140;
  int fadingCounter = 0;
  
  //constructor
  Message(int type, DoorSign theParent) {
    
    parent = theParent;
    if(type == 0) {
      grp = loadShape("available.svg");
      drawHold = true;
      drawFadeIn = false;
      drawFadeOut = false;
      off = false;
      alpha = 255;
      rectAlpha = (int)(alpha * ratio);
    }
    
    if(type == 1) {
      grp = loadShape("inuse.svg");
      drawHold = false;
      drawFadeIn = false;
      drawFadeOut = false;
      off = true;
      alpha = 0;
      rectAlpha = 0;
    }
    grp.disableStyle();
    x = 0;
    y = 0;
    targetX =0;
    targetY = -360;
    hold = true;
    moveUp = false;
    moveDown = false;
  }
  
  void draw() {
    if(drawFadeIn) {
      fadeIn();
    }
    
    if(drawHold) {
      messageHold();
    }
    
    if(drawFadeOut) {
      fadeOut();
    }  
    
    if(off) {
      off();
    }
  }
  
  void fadeIn() {
    if(fadingCounter == 0) {
      alpha = 0;
      rectAlpha = 0;
    }
    updateFadeCounters();
    if(alpha < 255) {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y+193,width,rectSize);
      noStroke();
      fill(color(255,255,255,alpha));
      shape(grp,x,y);
      alpha = (int)lerp(alpha,255,fadeLerpAmount);
      if(alpha >= 250) {
        alpha = 255;
      }
      rectAlpha = (int)(alpha * ratio);
      if(rectAlpha > finalRectAlpha) {
        rectAlpha = finalRectAlpha;
      }
    } else {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y+193,width,rectSize);
      noStroke();
      fill(color(255,255,255,alpha));
      shape(grp,x,y);        
    }
    
    if(alpha == 255 && parent.timeWaited > parent.waitTime) {
      off = false;
      drawFadeIn = false;
      drawHold = true;
    }
  }
  
  void messageHold() {
    fadingCounter = 0;
    off = false;
    drawFadeIn = false;
    drawFadeOut = false;
    stroke(0,0,0,alpha);
    strokeWeight(4);
    fill(rectColor,rectAlpha);
    rect(0,y+193,width,rectSize);
    noStroke();
    fill(color(255,255,255));
    shape(grp,x,y);
  }
  
  void fadeOut() {
    
    if(fadingCounter == 0) {
      alpha = 255;
      rectAlpha = (int)(alpha * ratio);
    }
    updateFadeCounters();
    
    if(alpha > 0) {
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y+193,width,rectSize);
      noStroke();
      fill(color(255,255,255,alpha));
      shape(grp,x,y);
      alpha = (int)lerp(alpha,0,fadeLerpAmount);
      rectAlpha = (int)(alpha * ratio);
    } else {
      alpha = 0;
      rectAlpha = 0;
      stroke(0,0,0,alpha);
      strokeWeight(4);
      fill(rectColor,rectAlpha);
      rect(0,y+193,width,rectSize);
      noStroke();
      fill(color(255,255,255,alpha));
      shape(grp,x,y);
    }

    if(alpha == 0) {
      drawFadeOut = false;
      drawHold = false;
      off = true;
    }

  }
  
  void moveUp() {
    float b;
    if(y > targetY) {
      b = (int)lerp(y,targetY,lerpAmount);
      y = (int)b-1;
    } else {
      moveUp = false;
      hold = true;
      y = targetY;
    }
  }
  
  void moveDown() {
    if(y < targetY) {
      y = (int)lerp(y,targetY,lerpAmount);
    } else {
      moveDown = false;
      hold = true;
      y = targetY;
    }
  }

  void updateFadeCounters() {
    fadingCounter+=1;
    
    if(fadingCounter > 5) {
      fadingCounter = 1;
    }
  }
}

Message.pde

class DoorSign {
  Logo logo;
  Message ava;
  Message inUse;
  BackgroundController_new aBack;
  AllTheColors someColors;
  color theColor, theBackgroundColor;
  int counter = 0;
  int transformCounter = 0;
  int availableCounter = 0;
  int unavailableCounter = 0;
  int fadingCounter = 0;
  int runCounter = 0;
  
  // Door sign operation variables
  boolean available, becomingAvailable, unavailable, becomingUnavailable,transformAvailable,recheck;
  AnimationTimer waitTimer,displayTimer;
  float timeWaited = 0;
  float waitTime = 1000;
  float displayTime = 20000;
  boolean availableState,unavailableState;
  DoorSignController parent;
  int randomIndex = 0;

  //constructor
  DoorSign() {
    someColors = new AllTheColors();
    logo = new Logo();
    ava = new Message(0,this);
    inUse = new Message(1,this);
    aBack = new BackgroundController_new(someColors.greens,someColors.reds,someColors.alts);
    waitTimer = new AnimationTimer();
    waitTimer.setStart();
    displayTimer = new AnimationTimer();
    displayTimer.setStart();
    available = true;
    becomingAvailable = false;
    unavailable = false;
    becomingUnavailable = false;
	
    inUse.alpha = 0;
    inUse.off = true;
    inUse.drawHold = false;
    inUse.drawFadeIn = false;
    inUse.drawFadeOut = false;
 
    ava.alpha = 255;
    ava.drawHold = true;
    ava.off = false;
    ava.drawFadeIn = false;
    ava.drawFadeOut = false;
    
    aBack.state = 1;
    availableState = false;
    unavailableState = false;
    
    logo.off = true;
  }
  
  //methods
  void draw() {
    noStroke();
    aBack.update();
    waitTimer.update();
    displayTimer.update();
    
    //if available, check the displayTimer
    if(available) {
    
	  // if the display time limit has been reached, switch to opposite states
      if(displayTimer.getRuntime() > displayTime) { 
        availableState = !availableState;
        unavailableState = !unavailableState;
        
        if(availableState) {
          ava.moveUp = true;
          ava.moveDown = false;
          ava.hold = false;
          ava.targetY = -130; // to move up
          logo.off = false;
          logo.moveUp = true;
          logo.moveDown = false;
          logo.hold = false;
          logo.drawHold = true;
          logo.targetY = 389+65;
          aBack.state = 4;
          randomIndex = aBack.makeRandomIndex();
        }
        
        if(!availableState) {
          ava.moveUp = false;
          ava.moveDown = true;
          ava.hold = false;
          ava.targetY = 0; // back down
          logo.moveUp = false;
          logo.moveDown = true;
          logo.hold = false;
          logo.targetY = 721;
          aBack.state = 3;
        }

        transformAvailable = true;
        available = false;
        runCounter+=1;
        if(runCounter < 10) {
          runCounter = 1;
        }
      }
    } //end of available displayTimer check
    
    // if in the normal state
    if(availableState) { //normal state
    
      if(available) { 
        drawAvailable(); // draw the regular availabe
      }

      if(becomingAvailable) {
        drawBecomingAvailableAlt(); // draw the regular becoming available
      }
      
      if(transformAvailable) {
        drawTransformAvailable();
      }
    }
    
    // if in the alternate state
    if(!availableState) { //alternate state
      if(available) {
	    // draw alternate available
        drawAvailable();
      }
      
      if(becomingAvailable) {
	    // draw alternate becoming available
        drawBecomingAvailable();
      }
      
      if(transformAvailable) {
        drawTransformAvailableAlt(); // draw the alternate transform back to normal state available
      }
    }
   
    // if in the normal state
    if(unavailableState) { // normal state
  
      if(unavailable) {
        drawUnavailable(); // draw unavailable
      }
  
      if(becomingUnavailable) {
        drawBecomingUnavailableAlt(); // draw normal becoming unavailalbe
      } 
    }
    
    // if in the alternate state
    if(!unavailableState) { //alternate state
    
      if(unavailable) {
        drawUnavailable();
      }
          
      if(becomingUnavailable) {
	    // draw the alternate becoming unavailable state in 
		// which both the available and McAuliffe's logo fade out.
        drawBecomingUnavailableAlt(); 
      }
    }
  }
  
//=====  DRAW METHODS BASED ON STATE OF THE DOOR SIGN ====
  void drawBecomingUnavailableAlt() {
    if(fadingCounter == 0) {
      inUse.alpha = 0;
      ava.alpha = 255;
      logo.alpha = 255;
    }
    if(!ava.off) {
      ava.drawHold = false;
      ava.drawFadeOut = true;
      logo.drawHold = false;
      logo.drawFadeOut = true;
      logo.draw();
      ava.draw();
      updateFadeCounters();
    } else if(ava.off && !inUse.drawHold) {
      timeWaited = waitTimer.getRuntime();
      if(timeWaited > waitTime) {
        inUse.off = false;
        inUse.drawFadeIn = true;
        inUse.draw();
        updateFadeCounters();
      }
    } else if(ava.off && logo.off && inUse.drawHold) {
      becomingUnavailable = false;
      unavailable = true;
      fadingCounter = 0;
      inUse.draw();
      randomIndex = aBack.makeRandomIndex();
      logo.rectColor = color(aBack.grid[0][0].alts[aBack.k][3][0],aBack.grid[0][0].alts[aBack.k][3][1],aBack.grid[0][0].alts[aBack.k][3][2]);
    }
  }

  void drawBecomingAvailableAlt() {
    color(aBack.grid[0][0].alts[aBack.k][3][0],aBack.grid[0][0].alts[aBack.k][3][1],aBack.grid[0][0].alts[aBack.k][3][2]);
    if(fadingCounter == 0) {
      ava.alpha = 0;
      logo.alpha = 0;
      logo.rectAlpha = 0;
      inUse.alpha = 255;
    }
    
    fadingCounter+=1;
    
    if(fadingCounter > 5) {
      fadingCounter = 1;
    }
    
    if(!inUse.off) {
      inUse.drawHold = false;
      inUse.drawFadeOut = true;
      inUse.draw();
      updateFadeCounters();
    } else if(inUse.off && !ava.drawHold) {
      timeWaited = waitTimer.getRuntime();
      if(timeWaited > waitTime) {
        ava.off = false;
        ava.drawFadeIn = true;
        logo.off = false;
        logo.drawFadeIn = true;
        logo.draw();
        ava.draw();
        updateFadeCounters();
      }
    } else if(inUse.off && ava.drawHold && logo.drawHold) {
      becomingAvailable = false;
      available = true;
      logo.draw();
      ava.draw();
    }
  }
  
  // this method, we are starting normal available, moving up the 
  // available message, and bringing in the mcauliffe's logo
  void drawTransformAvailable() {  
    if(transformCounter == 0) {
      randomIndex = aBack.makeRandomIndex();
      logo.rectColor = color(aBack.grid[0][0].alts[aBack.k][3][0],aBack.grid[0][0].alts[aBack.k][3][1],aBack.grid[0][0].alts[aBack.k][3][2]);
    }
    transformCounter+=1;
    if(transformCounter > 10) {
      transformCounter = 1;
    }
    
	// make sure that when the ava y coordinate is at the  
	// targetY to set the moveUp to false and the hold to true
    if(ava.moveUp) {
      ava.moveUp();
    }
    
    if(logo.moveUp) {
      logo.moveUp();
    }

    ava.draw();
    logo.draw();

    if(ava.hold && logo.hold) {
      transformAvailable = false;
      available = true;
      displayTimer.setStart();
      transformCounter = 0;
    }
  } // END OF DRAWTRANSFORMAVAILABLE()
  
  // this method changes the avilable message from the alternate 
  // state back to the normal state available message without the logo.
  void drawTransformAvailableAlt() {
    if(ava.moveDown) {
      ava.moveDown();
    }
  
    if(logo.moveDown) {
      logo.moveDown();
    }

    ava.draw();
    logo.draw();
    
    if(ava.hold && logo.off) {
      transformAvailable = false;
      available = true;
      displayTimer.setStart();
    }
  }
  
  void drawAvailable() {
    fadingCounter = 0;
    ava.drawHold = true;
    inUse.off = true;
    ava.draw(); 
    if(!logo.off) {
      logo.draw();
    }
  }
  
  void drawAvailableAlt() {
    fadingCounter = 0;
    ava.drawHold = true;
    inUse.off = true;
    ava.draw(); 
    logo.draw();
  }
  
  void drawBecomingAvailable() {
    if(fadingCounter == 0) {
      ava.alpha = 0;
      inUse.alpha = 255;
    }
    if(!inUse.off) {
      inUse.drawHold = false;
      inUse.drawFadeOut = true;
      inUse.draw();
      updateFadeCounters();
    } else if(inUse.off && !ava.drawHold) {
      timeWaited = waitTimer.getRuntime();
      if(timeWaited > waitTime) {
        ava.off = false;
        ava.drawFadeIn = true;
        ava.draw();
        updateFadeCounters();
      }
    } else if(inUse.off && ava.drawHold) {
      becomingAvailable = false;
      available = true;
      ava.draw();
    }
    
    fadingCounter+=1;
    
    if(fadingCounter > 5) {
      fadingCounter = 1;
    }
  }
  
  void drawUnavailable() {
    fadingCounter = 0;
    inUse.drawHold = true;
    ava.off = true;
    inUse.draw(); 
  }
  
  void drawBecomingUnavailable() {
    if(fadingCounter == 0) {
      inUse.alpha = 0;
      ava.alpha = 255;
    }
    if(!ava.off) {
      ava.drawHold = false;
      ava.drawFadeOut = true;
      ava.draw();
      updateFadeCounters();
    } else if(ava.off && !inUse.drawHold) {
      timeWaited = waitTimer.getRuntime();
      if(timeWaited > waitTime) {
        inUse.off = false;
        inUse.drawFadeIn = true;
        inUse.draw();
        updateFadeCounters();
      }
    } else if(ava.off && inUse.drawHold) {
      becomingUnavailable = false;
      unavailable = true;
      randomIndex = aBack.makeRandomIndex();
      logo.rectColor = color(aBack.grid[0][0].alts[aBack.k][3][0],aBack.grid[0][0].alts[aBack.k][3][1],aBack.grid[0][0].alts[aBack.k][3][2]);
      fadingCounter = 0;
      inUse.draw();
    }
  }
  
  void updateFadeCounters() {
    fadingCounter+=1;
    
    if(fadingCounter > 5) {
      fadingCounter = 1;
    }
  }
  
  void update() {
    waitTimer.update();    
  }
  
  void makeBecomingAvailable() {
    becomingAvailable = true;
    becomingUnavailable = false;
    unavailable = false;
    available = false;
    if(availableState) {
      aBack.state = 4;
      logo.rectColor = color(aBack.grid[0][0].alts[aBack.k][3][0],aBack.grid[0][0].alts[aBack.k][3][1],aBack.grid[0][0].alts[aBack.k][3][2]);
    } else {
      aBack.state = 3;
    }
    
    waitTimer.setStart();
    displayTimer.setStart();
  }
  
  void makeBecomingUnavailable() {
    becomingAvailable = false; 
    available = false;
    becomingUnavailable = true;
    unavailable = false;
    aBack.state = 2;
    waitTimer.setStart();
    displayTimer.setStart();
  } 
}

DoorSign.pde

class DoorSignController {
  DoorSign doorSign;
  char lastKey;
  char currentKey;
  int counter;
  int state; //0 available, 1 becomingAvailable, 2 unavailable, 3 becomingUnavailable
  int lastState;//
  boolean recheck;
  boolean toUpdate;
  boolean updateColors;
  
  //constructor
  DoorSignController() {
    recheck = false;
    toUpdate = false;
    updateColors = false;
    doorSign = new DoorSign();
    lastKey = 'o';
    counter = 0;  
    state = 0;
    lastState = 0;
  }
  
  //methods
  void loopDeLoop() {
    background(doorSign.aBack.backgroundColor);
    checkState(); // check for new button presses?
    doorSign.update();
    doorSign.draw();
    //println(frameRate);
  }
  
  void checkState() {
    if(toUpdate) {
      if(doorSign.available || doorSign.unavailable) {
        if(state == 1) {
          doorSign.makeBecomingAvailable();
          toUpdate = false;
          state = 0;
        }
        
        if(state == 3) {
          doorSign.makeBecomingUnavailable();
          toUpdate = false;
          state = 2;
        }
      }
    }
  }
  
  void updateColors() {
    doorSign.randomIndex = doorSign.aBack.makeRandomIndex();
    doorSign.logo.rectColor = color(doorSign.aBack.grid[0][0].alts[doorSign.aBack.k][3][0],doorSign.aBack.grid[0][0].alts[doorSign.aBack.k][3][1],doorSign.aBack.grid[0][0].alts[doorSign.aBack.k][3][2]);
    updateColors = false;
  }
}

DoorSignController.pde

class AllTheColors {
  int[][] greens;
  int[][] reds;
  int[][][] alts;
  //midGreen 
  int mgC1 = 78;
  int mgC2 = 130;
  int mgC3 = 0;
  //midLightGreen
  int mlgC1 = 115;
  int mlgC2 = 173;
  int mlgC3 = 27;
  //darkGreen
  int dgC1 = 66;
  int dgC2 = 102;
  int dgC3 = 13;
  //lightGreen
  int lgC1 = 155;
  int lgC2 = 255;
  int lgC3 = 0;
  //midRed 
  int mrC1 = 174;
  int mrC2 = 0;
  int mrC3 = 0;
  //midLightRed
  int mlrC1 = 229;
  int mlrC2 = 0;
  int mlrC3 = 0;
  //darkRed 
  int drC1 = 119;
  int drC2 = 1;
  int drC3 = 1;
  //lightRed 
  int lrC1 = 255;
  int lrC2 = 12;
  int lrC3 = 12;
  //midBlue 
  int mbC1 = 13;
  int mbC2 = 89;
  int mbC3 = 170;
  //midLightBlue
  int mlbC1 = 0;
  int mlbC2 = 115;
  int mlbC3 = 239;
  //darkBlue 
  int dbC1 = 11;
  int dbC2 = 95;
  int dbC3 = 186;
  //lightBlue 
  int lbC1 = 93;
  int lbC2 = 170;
  int lbC3 = 252;
  // light purple
  int lpC1= 239;
  int lpC2= 93;
  int lpC3= 212;
  // mid light purple
  int mlpC1= 237;
  int mlpC2= 64;
  int mlpC3= 205;
  // mid purple
  int mpC1= 160;
  int mpC2= 25;
  int mpC3= 135;
  // dark purple
  int dpC1= 76;
  int dpC2= 0;
  int dpC3= 62;
  // light yellow
  int lyC1= 255;
  int lyC2= 255;
  int lyC3= 73;
  // mid light yellow
  int mlyC1= 247;
  int mlyC2= 247;
  int mlyC3= 2;
  // mid yellow
  int myC1= 216;
  int myC2= 216;
  int myC3= 2;
  // dark yellow
  int dyC1= 200;
  int dyC2= 200;
  int dyC3= 0;
  // light some other color
  int ltC1= 200;
  int ltC2= 200;
  int ltC3= 200;
  // mid light some other color
  int mltC1= 128;
  int mltC2= 128;
  int  mltC3= 128;
  // mid some other color
  int mtC1= 49;
  int mtC2= 49;
  int mtC3= 49;
  // dark some other color
  int dtC1= 0;
  int dtC2= 0;
  int dtC3= 0;
  
  //constructor
  AllTheColors() {
    makeColorArrays();    
  }
  
  //methods
  void makeColorArrays() {
    greens = new int[4][3];
    reds = new int[4][3];
    alts = new int[4][4][3];
    
    makeAlts();
    
    greens[0][0] = lgC1;
    greens[0][1] = lgC2;
    greens[0][2] = lgC3;
    greens[1][0] = mlgC1;
    greens[1][1] = mlgC2;
    greens[1][2] = mlgC3;
    greens[2][0] = mgC1;
    greens[2][1] = mgC2;
    greens[2][2] = mgC3;
    greens[3][0] = dgC1;
    greens[3][1] = dgC2;
    greens[3][2] = dgC3;
    reds[0][0] = lrC1;
    reds[0][1] = lrC2;
    reds[0][2] = lrC3;
    reds[1][0] = mlrC1;
    reds[1][1] = mlrC2;
    reds[1][2] = mlrC3;
    reds[2][0] = mrC1;
    reds[2][1] = mrC2;
    reds[2][2] = mrC3;
    reds[3][0] = drC1;
    reds[3][1] = drC2;
    reds[3][2] = drC3;
  }

  void makeAlts() {
    alts[0][0][0] = lbC1;
    alts[0][0][1] = lbC2;
    alts[0][0][2] = lbC3;
    alts[0][1][0] = mlbC1;
    alts[0][1][1] = mlbC2;
    alts[0][1][2] = mlbC3;
    alts[0][2][0] = mbC1;
    alts[0][2][1] = mbC2;
    alts[0][2][2] = mbC3;
    alts[0][3][0] = dbC1;
    alts[0][3][1] = dbC2;
    alts[0][3][2] = dbC3;
    alts[1][0][0] = lpC1;
    alts[1][0][1] = lpC2;
    alts[1][0][2] = lpC3;
    alts[1][1][0] = mlpC1;
    alts[1][1][1] = mlpC2;
    alts[1][1][2] = mlpC3;
    alts[1][2][0] = mpC1;
    alts[1][2][1] = mpC2;
    alts[1][2][2] = mpC3;
    alts[1][3][0] = dpC1;
    alts[1][3][1] = dpC2;
    alts[1][3][2] = dpC3;
    alts[2][0][0] = lyC1;
    alts[2][0][1] = lyC2;
    alts[2][0][2] = lyC3;
    alts[2][1][0] = mlyC1;
    alts[2][1][1] = mlyC2;
    alts[2][1][2] = mlyC3;
    alts[2][2][0] = myC1;
    alts[2][2][1] = myC2;
    alts[2][2][2] = myC3;
    alts[2][3][0] = dyC1;
    alts[2][3][1] = dyC2;
    alts[2][3][2] = dyC3;
    alts[3][0][0] = ltC1;
    alts[3][0][1] = ltC2;
    alts[3][0][2] = ltC3;
    alts[3][1][0] = mltC1;
    alts[3][1][1] = mltC2;
    alts[3][1][2] = mltC3;
    alts[3][2][0] = mtC1;
    alts[3][2][1] = mtC2;
    alts[3][2][2] = mtC3;
    alts[3][3][0] = dtC1;
    alts[3][3][1] = dtC2;
    alts[3][3][2] = dtC3;
  }
}

AllTheColors.pde

Now that I've gone and looked through all this code again for the first time in a few years, I'm a) realizing that I could probably reformat things to tidy it up a little (sooo many variables for the colors) and b) feeling pretty good I pulled this one off given it was my first foray into Processing. My door sign has been running 24/7 for about 4 years now without a hitch :D

Projects test

Philip Scott Barrowcliffe — Sat, 02 Apr 2022 16:14:36 GMT

testing projects

Testing Design

Philip Scott Barrowcliffe — Sat, 02 Apr 2022 16:13:42 GMT

Design test.