stockman to bts handover

notes.md

AWS root account

Account ID: 637321691218

Canonical user ID: 57a3fc67b73394f1d7da86de24a1ba55c8a00ebf4227162effe41bafcb69a5b4

Account name: fagersta

Email: adming@fagersta.com.au

Pass: green42#pilot

I've removed the MFA from the account

Amazon SES (Simple Email Service)

Region: N. Virginia us-east-1

On-prem SQL Server details

Fagersta

  Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64)
  	Nov  1 2020 00:48:37
  	Copyright (c) Microsoft Corporation
  	Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SELECT database_name = DB_NAME(database_id), log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id

   DATABASE NAME   | LOG SIZE MB | ROW SIZE MB | TOTAL SIZE MB
-------------------+-------------+-------------+----------------
  fagerstadatabase |     2062.06 |     9600.69 |      11662.75
-------------------+-------------+-------------+----------------

E-Steel

  Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64)
  	Nov  1 2020 00:48:37
  	Copyright (c) Microsoft Corporation
  	Express Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

SELECT database_name = DB_NAME(database_id), log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)), total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id

  DATABASE NAME  | LOG SIZE MB | ROW SIZE MB | TOTAL SIZE MB
-----------------+-------------+-------------+----------------
  esteeldatabase |     8816.25 |     3364.88 |      12181.13
-----------------+-------------+-------------+----------------

Microsoft SQL Server 2012 SP4 - Download link

Version: 11.0.7001.0 (KB4018073) - will ideally be updated to KB4583465

Compatible with Windows Server 2019 and below, and Windows 10 and below

https://www.microsoft.com/en-us/download/details.aspx?id=56040

Removing on-prem server

SQL Server

• Currently the GCP Stockman app communicates with the on-prem mssql db via a ssh tunnel through the on-prem ubuntu server
• Sql Server version being used is 2012 Express Edition

Stockman Auth

• Uses on-prem Windows Server LDAP to auth logins and db connections.
• LDAP tcp conns are fired from the on-prem ubuntu vm to the on-prem win server so if the ldap svc is moved off-prem the requests might not be secure.
• I can bypass the LDAP part of auth from Stockman entirely (just return true without actually doing the check) but then there will be no password verification when logging into Stockman (you may or may not mind that)

Ubuntu VM (on-prem)

• Used as ssh tunnel entry point by GCP-hosted Stockman app for database and ldap conns
• Runs a microservice that consumes a task queue populated by Stockman. These tasks communicate with:
  • local printers (label printers and laserjets)
  • AWS SES (Simple Email Service)
  • Xero api
  • Google gmail api

Stockman user permissions script

Copy the appropriate set of EXEC statements from below into the script before running

Fagersta exec statements

-- below covers users != inactive as of 2024-01-31, feel free to add/remove users as desired
EXEC usp_InsertReadOnlyPermissionsForUser 'AlvinA';
EXEC usp_InsertReadOnlyPermissionsForUser 'AmariFBR';
EXEC usp_InsertReadOnlyPermissionsForUser 'AmariFME';
EXEC usp_InsertReadOnlyPermissionsForUser 'AndrewB';
EXEC usp_InsertReadOnlyPermissionsForUser 'Benf';
EXEC usp_InsertReadOnlyPermissionsForUser 'BennyA';
EXEC usp_InsertReadOnlyPermissionsForUser 'BradynC';
EXEC usp_InsertReadOnlyPermissionsForUser 'Cherylc';
EXEC usp_InsertReadOnlyPermissionsForUser 'ChrisGlenn';
EXEC usp_InsertReadOnlyPermissionsForUser 'ConnerJ';
EXEC usp_InsertReadOnlyPermissionsForUser 'ConradP';
EXEC usp_InsertReadOnlyPermissionsForUser 'HannahL';
EXEC usp_InsertReadOnlyPermissionsForUser 'HarshS';
EXEC usp_InsertReadOnlyPermissionsForUser 'HeadOffice';
EXEC usp_InsertReadOnlyPermissionsForUser 'HunterJ';
EXEC usp_InsertReadOnlyPermissionsForUser 'IngridV';
EXEC usp_InsertReadOnlyPermissionsForUser 'JasonC';
EXEC usp_InsertReadOnlyPermissionsForUser 'JustinP';
EXEC usp_InsertReadOnlyPermissionsForUser 'KevinG';
EXEC usp_InsertReadOnlyPermissionsForUser 'LeiZ';
EXEC usp_InsertReadOnlyPermissionsForUser 'MarkC';
EXEC usp_InsertReadOnlyPermissionsForUser 'MathewB';
EXEC usp_InsertReadOnlyPermissionsForUser 'MattH';
EXEC usp_InsertReadOnlyPermissionsForUser 'MichaelP';
EXEC usp_InsertReadOnlyPermissionsForUser 'Mikeu';
EXEC usp_InsertReadOnlyPermissionsForUser 'MinhP';
EXEC usp_InsertReadOnlyPermissionsForUser 'Monicac';
EXEC usp_InsertReadOnlyPermissionsForUser 'RichardB';
EXEC usp_InsertReadOnlyPermissionsForUser 'SalesMoor';
EXEC usp_InsertReadOnlyPermissionsForUser 'SalesWeth';
EXEC usp_InsertReadOnlyPermissionsForUser 'SalesZill';
EXEC usp_InsertReadOnlyPermissionsForUser 'StockMan';
EXEC usp_InsertReadOnlyPermissionsForUser 'StoreNSW';
EXEC usp_InsertReadOnlyPermissionsForUser 'StoreQLD';
EXEC usp_InsertReadOnlyPermissionsForUser 'StoreVIC';
EXEC usp_InsertReadOnlyPermissionsForUser 'TempFBR';
EXEC usp_InsertReadOnlyPermissionsForUser 'TempFME';
EXEC usp_InsertReadOnlyPermissionsForUser 'WarrenD';
EXEC usp_InsertReadOnlyPermissionsForUser 'YashC';

E-Steel exec statements

-- below covers users != inactive as of 2024-01-31, feel free to add/remove users as desired
EXEC usp_InsertReadOnlyPermissionsForUser 'AlvinA';
EXEC usp_InsertReadOnlyPermissionsForUser 'AmariEME';
EXEC usp_InsertReadOnlyPermissionsForUser 'Benf';
EXEC usp_InsertReadOnlyPermissionsForUser 'BennyA';
EXEC usp_InsertReadOnlyPermissionsForUser 'BradynC';
EXEC usp_InsertReadOnlyPermissionsForUser 'CameronW';
EXEC usp_InsertReadOnlyPermissionsForUser 'Cherylc';
EXEC usp_InsertReadOnlyPermissionsForUser 'ConradP';
EXEC usp_InsertReadOnlyPermissionsForUser 'DavidB';
EXEC usp_InsertReadOnlyPermissionsForUser 'HannahL';
EXEC usp_InsertReadOnlyPermissionsForUser 'HarshS';
EXEC usp_InsertReadOnlyPermissionsForUser 'IngridV';
EXEC usp_InsertReadOnlyPermissionsForUser 'LeiZ';
EXEC usp_InsertReadOnlyPermissionsForUser 'MarkT';
EXEC usp_InsertReadOnlyPermissionsForUser 'MathewB';
EXEC usp_InsertReadOnlyPermissionsForUser 'Mikeu';
EXEC usp_InsertReadOnlyPermissionsForUser 'MonicaC';
EXEC usp_InsertReadOnlyPermissionsForUser 'RichardB';
EXEC usp_InsertReadOnlyPermissionsForUser 'SalesDand';
EXEC usp_InsertReadOnlyPermissionsForUser 'StoreVIC';

Script

DELETE FROM tblPermissions WHERE [user] NOT IN ('SandyB','JohnC','StockMan','fagerstasystem','esteelsystem');

IF (OBJECT_ID('usp_InsertReadOnlyPermissionsForUser') IS NOT NULL)
BEGIN
    DROP PROCEDURE usp_InsertReadOnlyPermissionsForUser
END

CREATE PROCEDURE usp_InsertReadOnlyPermissionsForUser @usern varchar(100) AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO tblPermissions ([user],[functionname])
    VALUES
        (@usern,'DumpToExcel'),
        (@usern,'nav1'),
        (@usern,'nav13'),
        (@usern,'nav28'),
        (@usern,'nav31'),
        (@usern,'nav34'),
        (@usern,'nav62'),
        (@usern,'nav65'),
        (@usern,'nav67'),
        (@usern,'ProductSettings'),
        (@usern,'viewBranchSaleScores'),
        (@usern,'ViewCompanyBookmarksManagedList'),
        (@usern,'viewCompanySaleScores'),
        (@usern,'ViewMillCycle'),
        (@usern,'ViewRequest'),
        (@usern,'viewSaleScores'),
        (@usern,'ViewStockTurnReport');
    SET NOCOUNT OFF;
END

-- PASTE EXEC STATEMENTS HERE FOR WHICHEVER COMPANY DATABASE YOU'RE RUNNING AGAINST (EITHER FAGERSTA OR ESTEEL)

DROP PROCEDURE usp_InsertReadOnlyPermissionsForUser;

packtestcerts.sql

with s2b as (
    SELECT tblstockonhand.packnoid, tblstockonhand.ultimateparent, tblstockonhand.heatnumber, tblstockonhand.comments, tblstockonhand.attcomments1, tblbookedincost.jobno
    FROM tblstockonhand left outer join tblbookedincost on tblstockonhand.packnoid=tblbookedincost.packnoid
),
s2b1 as (
    SELECT tblstockonhand.packnoid, tblstockonhand.ultimateparent, tblstockonhand.heatnumber, tblstockonhand.comments, tblstockonhand.attcomments1, tblbookedincost.jobno
    FROM tblstockonhand left outer join tblbookedincost on tblstockonhand.packnoid=tblbookedincost.packnoid
)
select s.packnoid, s.ultimateparent, s.parentpackid as [nearparent], s2b.packnoid, b.jobno as [PO#], s2b1.jobno as [ultiPO#], s2b.jobno as [nearPO#], s.heatnumber, s.comments, s.attcomments1
from tblstockonhand s
left join tblbookedincost b on s.packnoid=b.packnoid
left join s2b1 on s.ultimateparent=s2b1.packnoid
left join s2b on LEFT(s.parentpackid,6)=s2b.packnoid
where s.packnoid in (355129,356200);
-- where (s.attcomments1 is not null or s1.attcomments1 is not null) and s.packnoid=355129
-- order by s.packnoid desc;

with s2b as (
    SELECT tblstockonhand.packnoid, tblstockonhand.ultimateparent
    FROM tblstockonhand
)
select s.packnoid, s.ultimateparent, s.parentpackid as [nearparent], s2b.packnoid, b.jobno as [PO#], s.heatnumber, s.comments, s.attcomments1
from tblstockonhand s
left join s2b on s.parentpackid != NULL and CAST(LEFT(s.parentpackid,6) as int)=s2b.packnoid
left join tblbookedincost b on iif(len(s.ultimateparent) != 6, s2b.ultimateparent, s.ultimateparent)=b.packnoid
where s.packnoid in (355129, 356200);

ALTER FUNCTION dbo.GetUltimateParentFromParentString (@parentstring varchar(100))
RETURNS varchar(100)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @nextlink varchar(100);

    IF @parentstring IS NULL OR LEN(@parentstring) < 6
        RETURN '';

    SELECT @nextlink = IIF(len(ultimateparent) = 6, ultimateparent, LEFT(parentpackid,6))
    FROM tblstockonhand
    WHERE packnoid = CAST(REPLACE(LEFT(@parentstring, 6),';','') as int);

    RETURN IIF(@nextlink = REPLACE(LEFT(@parentstring, 6),';',''), @nextlink, dbo.GetUltimateParentFromParentString(@nextlink));
END;

select s.packnoid, s.ultimateparent, s.parentpackid, dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as [derivedultpar], b.jobno as [PO#], IIF(b.jobno IS NULL, dbo.GetWorkOrderFromPacknoid(s.packnoid), NULL) as [WO#], s.heatnumber, replace(s.comments, char(10), '') as [CommentsField], s.attcomments1
from tblstockonhand s
left join tblbookedincost b on CAST(dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as int)=b.packnoid;

SELECT s.packnoid, s.ultimateparent, s.parentpackid, dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as [derivedultpar], b.jobno as [PO#], IIF(b.jobno IS NULL, dbo.GetWorkOrderFromPacknoid(s.packnoid), NULL) as [WO#], s.heatnumber, s.attcomments1
FROM tblstockonhand s
LEFT JOIN tblbookedincost b ON CAST(dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as int)=b.packnoid;

select s.packnoid, dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as [derivedultpar]
from tblstockonhand s where len(s.parentpackid)>5 or len(s.ultimateparent)=6;

ALTER FUNCTION dbo.GetWorkOrderFromPacknoid (@packnoid int)
RETURNS int
AS
BEGIN
    DECLARE @wo int

    IF @packnoid IS NULL RETURN NULL

    SELECT @wo = workorderid FROM tblWorkOrderDetail WHERE parent=0 and packnoid = @packnoid

    RETURN @wo
END;

SELECT s.packnoid, s.ultimateparent, s.parentpackid, dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as [derivedultpar], b.jobno as [PO#], IIF(b.jobno IS NULL, dbo.GetWorkOrderFromPacknoid(s.packnoid), NULL) as [WO#], s.heatnumber, s.attcomments1
FROM tblstockonhand s
LEFT JOIN tblbookedincost b ON CAST(dbo.GetUltimateParentFromParentString(iif(len(s.ultimateparent) = 6, s.ultimateparent, s.parentpackid)) as int)=b.packnoid;