Dyncraft_Barcode

Text fields on the label: Date of programming, Truck Plant, Chassis #, Sequence number, Firmware Version, test result

Label should be triggered by DAVIE flashing process.

Label dimensions must fit within X by X.

DMB can transmit Chassis information.
Requirement on the drawing to have a removable label.

Notes:

Truck plant is the second character in the chassis number prefix.  DAVIE has the two-character prefix in the chassis.

Spike needed with Joonbo- Options for triggering a label from DAVIE

Dependency - DAVIE backoffice needed before this story can be worked.

There will only be one printer and one flash station.

Order Type: ZCAS

 

USE [EWI_Admin]

GO

/****** Object:  StoredProcedure [dbo].[DynaSequencerFuelTank_PrintLabelK1]    Script Date: 7/25/2024 4:32:46 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Brad Schmidt

-- Create date: 11/15/2019

-- Modified: 2.3.20 added import and print date cols, update print col

-- 2.5.20 move all changes from the testing script,  

-- 10.28.20 split fuel side right was showing hydraulic for engraver field

-- -- IR749285 - Printer machine is changed from DYNMCKZ015 to DYNMCKZ017

--

-- Description: Prints single fuel tank label from EWI sequencer screen

-- NOTE: for split tanks if "A" postion is 1, Hydraulic tanks is 1st(left) and Fuel tanks is 2nd(right)

--   if "A" position is 2, its reversed, fuel 1st/left and hydro 2nd/right

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

ALTER PROCEDURE [dbo].[DynaSequencerFuelTank_PrintLabelK1]

@id INT

AS

BEGIN

    SET NOCOUNT ON;

DECLARE @result BIT,

@Salvagnini VARCHAR(20),

@SalvagniniL2 VARCHAR(20),

@Seam VARCHAR(20),

@SeamL2 VARCHAR(20),

@LongSeam INT,

@Raw VARCHAR(20),

@RawL2 VARCHAR(20),

@Shell VARCHAR(20),

@ShellL2 VARCHAR(20),

@Assembly VARCHAR(25),

@Chassis VARCHAR(10),

@Sequence VARCHAR(10),

@Motoman VARCHAR(35),

@MotomanL2 VARCHAR(35),

@Engraver VARCHAR(40),

@EngraverL2 VARCHAR(40),

@TankType CHAR(1),

@Title VARCHAR(40),

@TitleL2 VARCHAR(40),

@EXP VARCHAR(200)

--@K2Fitting VARCHAR(40);

--SELECT @Salvagnini = seq.PartNumber

-- ,@SalvagniniL2 = seq.PartNumber

SELECT @Salvagnini = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(seq.PartNumber, '_H')

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN seq.PartNumber

ELSE 'NA'

END

,@SalvagniniL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(seq.PartNumber, '_H')

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN seq.PartNumber

ELSE 'NA'

END

,@Seam = CASE ---check with tim if keep it the same...

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN CONCAT(kf.TankDiameter, '/L', kf.TankLength) 

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN CONCAT(kh.TankDiameter, '/L', kh.TankLength)

ELSE 'NA'

END

,@LongSeam = CASE ---check with tim if keep it the same...

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN     

--CONCAT(kf.TankDiameter, '/L', kf.TankLength) 

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=kf.TankDiameter AND [Length]=kf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 

--CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=ksh.TankDiameter AND [Length]=ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 

--CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=ksf.TankDiameter AND [Length]=ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 

--CONCAT(kh.TankDiameter, '/L', kh.TankLength)

(SELECT [LSWid] FROM [EWI_Admin].[dbo].[FuelTankLongSeam]  WHERE  [Diameter]=kh.TankDiameter AND [Length]=kh.TankLength)

ELSE 'NA'

END

,@SeamL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksf.TankDiameter, '/L', ksf.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksh.TankDiameter, '/L', ksh.TankLength)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@Raw = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN kf.RawFamily 

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN ksh.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN ksf.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN kh.RawFamily

ELSE 'NA'

END

,@RawL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN ksf.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN ksh.RawFamily

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@Shell = 'NA'

,@Assembly = seq.PartNumber

,@Sequence = seq.SequenceNumber

,@Chassis = seq.ChassisNumber

--,@Motoman = seq.PartNumber ---confirm just want assembly instead of: CONCAT(RIGHT('   ' + CAST(ld.TankCapacity AS VARCHAR(3)), 3), '.F.', seq.PartNumber)

,@Motoman = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'N/A'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'N/A'

ELSE 'NA'

END

,@MotomanL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN seq.PartNumber

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'N/A'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'N/A'

ELSE 'NA'

END

,@Engraver = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN CONCAT(LEFT(CAST(kf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(ksh.TankCapacity, '.H .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(LEFT(CAST(ksf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN CONCAT(kh.TankCapacity, '.H .',seq.PartNumber)

ELSE 'NA'

END

,@EngraverL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN CONCAT(LEFT(CAST(ksf.TankCapacity AS VARCHAR(3)) + '   ', 3), '.F .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN CONCAT(ksh.TankCapacity, '.H .',seq.PartNumber)

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

,@TankType = SUBSTRING(seq.PartNumber,4,1)

,@Title = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN 'FUEL'

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'SPLIT-HYDR(Left)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'SPLIT-FUEL(Left)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN 'HYDRAULIC'

ELSE 'NA'

END

,@TitleL2 = CASE 

WHEN SUBSTRING(seq.PartNumber,4,1) = '0' THEN NULL

WHEN SUBSTRING(seq.PartNumber,4,1) = '1' THEN 'SPLIT-FUEL(Right)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '2' THEN 'SPLIT-HYDR(Right)'

WHEN SUBSTRING(seq.PartNumber,4,1) = '3' THEN NULL

ELSE 'NA'

END

--EXP barcode for chassis, sequence, K1 part, and K2 fitting

,@EXP = CONCAT(seq.ChassisNumber,'.', seq.SequenceNumber,'.', seq.PartNumber,'.',ftpm.K2Part)

--,@K2Fitting = etaf.FittingPN

  

--,ksh.*

--,ksf.*

--,seq.*

  FROM [EWI_Admin].[dbo].DynaSequencerFuelTank seq

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef kf

ON SUBSTRING(seq.PartNumber,4,1) = '0'

   AND SUBSTRING(seq.PartNumber,5,1) = kf.Map

   AND kf.[Option] = 'B'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef ksh

ON SUBSTRING(seq.PartNumber,4,1) IN('1','2')

   AND SUBSTRING(seq.PartNumber,12,1) = ksh.Map

   AND ksh.[Option] = 'J'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef ksf

ON SUBSTRING(seq.PartNumber,4,1) IN('1','2')

   AND SUBSTRING(seq.PartNumber,5,1) = ksf.Map

   AND ksf.[Option] = 'B'

  LEFT JOIN [EWI_Admin].[dbo].DynaK1FuelTankRef kh

ON SUBSTRING(seq.PartNumber,4,1) = '3'

   AND SUBSTRING(seq.PartNumber,5,1) = kh.Map

   AND kh.[Option] = 'B'

   --Tanks and fittings join for K2 part number

   LEFT JOIN EWI_DataWarehouse.dbo.EXP_FuelTankPlumbingMatch AS ftpm

ON ftpm.K1Part = seq.PartNumber AND ftpm.MatchedLineSeqNbr = seq.SequenceNumber

WHERE seq.DynaSequencerFuelTankID = @id

IF (@Sequence IS NOT NULL)

BEGIN

--SELECT @Salvagnini Salvagnini,

-- @Seam Seam,

-- @Raw Raw,

-- @Shell Shell,

-- @Assembly [Assembly],

-- @Sequence [Sequence],

-- @Chassis Chassis,

-- @Motoman Motoman,

-- @Engraver Engraver,

-- @TankType TankType,

-- @Title Title,

-- @EXP [EXP]

DECLARE @FileName VARCHAR(MAX),

@FileText VARCHAR(MAX)

SET @FileName = CONCAT('\\DYNMCKMVPP1801\Bartender\Fuel_Tank\', @Chassis,'-',@Sequence,'-',@Assembly,'.ztt')

-- IR749285 - Printer machine is changed from DYNMCKZ015 to DYNMCKZ017

SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks_DEV.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ017" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z017 is fuel by Mo

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ015" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z015 is fuel by Mo

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ023" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z023 is fab, far corner

@Salvagnini,'|',@Seam,'|',@LongSeam,'|',@Raw,'|',@Shell,'|',@Assembly,'|',@Sequence,'|',@Chassis,'|',@Motoman,'|',@Engraver,'|',@Title,'|',@EXP)

--SELECT @filename [filename], @filetext filetext

------------------------------------------------

EXEC dbo.WriteToFile @FileName, @FileText;

------------------------------------------------

IF @TankType IN('1','2')

BEGIN

--SELECT @SalvagniniL2 SalvagniniL2,

-- @SeamL2 SeamL2,

-- @RawL2 RawL2,

-- @ShellL2 ShellL2,

-- @Assembly [Assembly],

-- @Sequence [Sequence],

-- @Chassis Chassis,

-- @MotomanL2 MotomanL2,

-- @EngraverL2 EngraverL2,

-- @TankType TankType,

-- @TitleL2 TitleL2,

-- @EXP [EXP]

SET @FileName = CONCAT('\\DYNMCKMVPP1801\Bartender\Fuel_Tank\', @Chassis,'-',@Sequence,'-',@Assembly,'-L2.ztt')

SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks_DEV.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ017" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z017 is by brandon, 15 is the one by fuel...

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ015" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z022 is by brandon, 15 is the one by fuel...

--SET @FileText = CONCAT('%BTW% /AF=C:\Labels\AllTanks.btw /D=%Trigger File Name% /PRN="\\DYNMCKMVPP1801\DYNMCKZ023" /C=1 /R=003 /P',CHAR(13),CHAR(10),'%END%',CHAR(13),CHAR(10),---z023 is fab, far corner

@SalvagniniL2,'|',@SeamL2,'|','|',@LongSeam,'|',@RawL2,'|',@ShellL2,'|',@Assembly,'|',@Sequence,'|',@Chassis,'|',@MotomanL2,'|',@EngraverL2,'|',@TitleL2,'|',@EXP)

--SELECT @filename [filename], @filetext filetext

------------------------------------------------

EXEC dbo.WriteToFile @FileName, @FileText;

------------------------------------------------

END 

        UPDATE dbo.DynaSequencerFuelTank

        SET IsLabelPrinted = 1,

PrintDate = GETDATE()

        WHERE DynaSequencerFuelTankID = @id;

        SET @result = 1;

    END;

    ELSE

    BEGIN

        SET @result = 0;

    END;

    --SELECT @result;

    RETURN @result;

END;