<#
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2015 v4.2.95
Created on: 03/08/2015 18:55
Created by: Phil Sharpe
Organization: Norbar Torque Tools Ltd.
Filename: Print-BoxLabel.ps1
===========================================================================
.DESCRIPTION
Print Kiaro box labels from Works Order details.
Currently runs on Windows 7 SP1 / Windows 10 (PowerShell 4 or above)
- untested on anything else.
PrintServer PC is hardcoded to NBW001124.
It is not advisable to run from any other PC without checking the label stock loaded in each printer.
SQL Server is Microsoft SQL Server 2008 Standard Edition (64-bit)
Program Inputs:
1. Works order number (scanned from barcode)
The inventory code and req'd qty are retrieved from the
Norbar_Production database using the W/O number.
Function: Read-WO
2. Printer number - currently 1, 2 or 3.
Function: Read-Printer
Used to construct the printer name
For an audible warning of invalid input data, the Windows system
sound "Question" (not normally used) should be set to a suitable
WAV file. Currently set to a WWII submarine dive alarm.
#>
Add-Type -AssemblyName System.Data
function Show-ErrorBox ($errTitle, $errMsg)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
[Windows.Forms.MessageBox]::Show($errMsg, $errTitle, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Error, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null
}
function Read-WO
{
# Enter Works Order number
# This must start with 3 followed by 6 digits
$iWarning = 'Enter Works Order number (x to end)'
$badInput = $false
Do
{
if ($badInput)
{
[System.Media.SystemSounds]::Question.Play() | Out-Null
}
[string]$worksOrder = (Read-Host -Prompt $iWarning).Replace(" ", "").ToUpper()
if ($worksOrder -eq "X")
{
Return "X"
}
$iWarning = 'Invalid Works Order. Please re-enter Works Order (x to end)'
$badInput = $true
}
while ($worksOrder -notmatch '^3\d{6}$')
Return $worksOrder
}
function Read-Printer
{
# Enter Printer number
# Currently this must be 1, 2 or 3
$iWarning = 'Enter Kiaro Printer number (1, 2 or 3)'
$badInput = $false
Do
{
if ($badInput)
{
[System.Media.SystemSounds]::Question.Play() | Out-Null
}
[string]$iPrinter = (Read-Host -Prompt $iWarning).Replace(" ", "")
$iWarning = 'Invalid Printer number. Please enter 1, 2 or 3'
$badInput = $true
}
while ($iPrinter -notin '1', '2', '3')
$iPS = "PRINTER=QuickLabel Kiaro ;Kiaro!" #Base printer name
switch ($iPrinter)
{
1 { $iPS = $iPS.Replace(" ;", " 1;") }
2 { $iPS = $iPS.Replace(" ;", " 2;") }
3 { $iPS = $iPS.Replace(" ;", " 3;") }
}
Return $iPS
}
# Processing starts here
#region Initialize
#The QuickCommand process on the print server monitors the Monitor$ share for new print command files
$printSrvr = 'NBW001124' #PC with QuickCommand & Kiaros connected
$quickCommand = 'C:\Program Files (x86)\Custom QuickLabel\QuickCommand.exe'
if ($env:COMPUTERNAME -ine $printSrvr)
{
#Firing up QuickCommand remotely - Hope you know what you're doing!
$printing = "Remote"
$quickCommand += ' /S'
if ((Get-CimInstance -Query 'Select * from Win32_Process where name like "quickcommand%"' -ComputerName $printSrvr | Measure-Object).Count -eq 0)
{
Invoke-CimMethod –ClassName Win32_Process -Computer $printSrvr –Method "Create" –Arg @{ CommandLine = $quickCommand }
}
$monitor = '\\NBW001124\Monitor$' #This is a pre-created invisible share
}
else
{
#We're running locally - Much safer (in terms of fewer scrapped labels)
$printing = "Local"
if ((Get-Process | Where-Object Name -eq quickcommand).Count -eq 0)
{
Start-Process -FilePath $quickCommand -ArgumentList '/S'
}
$monitor = 'C:\Users\Public\Documents\Custom QuickLabel\QuickCommand\Monitor'
}
$lblRoot = "\\FS1\AssyLabels\ALF Files\Kiaro" #Root folder for our label files
if ((Test-Path -Path $lblRoot -PathType Container) -eq $false)
{
$lblRoot = "\\FS2\AssyLabels\ALF Files\Kiaro"
if ((Test-Path -Path $lblRoot -PathType Container) -eq $false)
{
throw "Fatal error - Cannot access label folder"
}
}
$lblDblRow = @('26771', '26773', '26981', '266105') #Label stock with 2 labels per row
$crlf = [char]13 + [char]10
#Set up database access
$dbUser = "UID=*******"
$dbPass = "Pwd=**************"
$server = "Server=dbserv"
$database = "Database=NORBAR_PRODUCTION"
$conn = New-Object System.Data.SqlClient.SqlConnection("$server;$database;$dbUser;$dbPass")
$conn.Open() | Out-Null
#endregion
do
{
$worksOrder = Read-WO #Get works order number
if ($worksOrder -eq 'X')
{
continue
}
#SQL query
$query = @'
SELECT [WOR_ORDER]
,[WOR_ITEM_ONLY]
,[WOR_SALORD]
,[WOR_CUSTOMER]
,[WOR_ORIG_QTY]
,[WOR_DONE_DATE]
,[WOR_STATUS]
FROM [NORBAR_PRODUCTION].[dbo].[vwASC_WORTBL]
WHERE WOR_ORDER = {0}
'@ -f "'" + $worksOrder + "'"
$command = New-Object System.Data.Sqlclient.SqlCommand($query)
$command.Connection = $conn
$DR = $command.Executereader()
$DR.Read() | Out-Null
$wo = $DR.GetValue(0) # Works order number
$part = $DR.GetValue(1) # Part number
$so = $DR.GetValue(2) # Sales order number
$cust = $DR.GetValue(3) # Customer code
$origQty = $DR.GetValue(4) # Qty reqd
$doneDate = $DR.GetValue(5) # Date the W/O was closed (should be empty)
$status = $DR.GetValue(6) # Open/Closed status
$DR.Close()
<# Commented out - feedback not currently required
'' # A bit of feedback
'----------------------------------------------------------------------------------------'
"Works Order : $wo Part Number: $part Sales Order: $so Customer: $cust"
"Qty : $origqty"
"Done Date : $donedate"
"Status : $status"
''
#>
# Dealing with our "special" way of numbering parts
# and our "unique" way of naming labels, e.g. "26671_13070_NOR Iss1 Apr09.alf"
$part = $part.Trim().ToUpper() #Convert to uppercase without spaces
if ($part -match '^.{4,6}R$|.+R\.\d{2}') #Remove 'R' from the end of service replacement part codes
{
$part = $part.Replace('R', '')
}
if ($part.EndsWith('.01') -or $part -notmatch '.{4,6}\.[A-Z]{3}$') #Add _NOR to non own brand part codes
{
$part += '_NOR'
}
$part = $part.Replace(".", "_") #Replace dots with underscores (blame Marketing)
$lblFilter = -join ('*_', $part, '*') #Throw in some super stars (wildcard it) to make a file name filter
# Search for label files matching our part number from the label root folder down
# excluding anything in folders named 'Obsolete'
$lblFound = Get-ChildItem -File -Path $lblRoot -Recurse -Include *.alf -Filter $lblFilter | Where-Object fullname -NotMatch Obsolete
switch ($lblFound.Count) #Action depends on how many label files we matched
{
0 #Did someone steal my label?
{
'No labels found for this part - Cannot print'
}
1 #Perfect
{
"Printing label $lblFound"
$lblStock = ($lblFound.Name -split "_", 2)[0] #1st part of filename before the "_"
$lblRows = 1
if ($lblStock -in $lblDblRow) #How many labels per row?
{
$lblRows = 2
}
switch ($lblRows) #Calculate the number of rows of labels to print
{
1 { $printCount = [int16]$origQty }
2 { $printCount = [int16][math]::Truncate(($origQty/2) + 0.5) }
}
$printString = Read-Printer #Select a printer
$pfContent = 'LABELNAME=' + $lblFound.FullName + $crlf
$pfContent += 'LABELQUANTITY=' + $printCount + $crlf
$pfContent += $printString + $crlf
$pfName = $monitor + '\' + $worksOrder + '_' + [int16]$origQty + ' x ' + $part + '.acf'
if (Test-Path -Path $pfName)
{
throw "Fatal error - Another copy of the print instruction file already exists"
}
# Stream label to destination file
$stream = New-Object -ComObject ADODB.Stream
$stream.Charset = "utf-8"
$stream.Open()
$stream.WriteText($pfContent)
$stream.SaveToFile($pfName)
$stream.Close()
<# User wants less on-screen feedback
''
"Label $pfName printed to $printing $printString"
#>
}
default #Oops. More than 1 label found
{
"Found $($lblFound.Count) labels:"
$lblFound
'Cannot print - There can be only one! (label per part number)'
}
}
''
''
}
until ($worksOrder -eq 'X')
$conn.Close | Out-Null