Как добавить штрих - код в шестнадцатеричном формате?
Всем Привет. Моя текущая хранимая процедура и функция печатают штрих-код на основе состояния и типа приложения. Как показано ниже:-
ServiceTypeID|BarCodeID|DataMatrixCode2|DataMatrixCode3|DataMatrixCode4|DataMatrixCode5|DataMatrixCode6|AppType
M|TX300000000421865141|TX300000000421865142|TX300000000421865143|TX300000000421865144|||300|
Но теперь размер приложения растет до 13 или 14 страниц. Поэтому нам нужно реализовать штрих-код datamatrix в шестнадцатеричном формате. Поэтому нам нужно напечатать либо 13, либо 14 штрих-кодов datamatrix.
State(2)AppType(3)AppID(12)Version(1)TotalPages(1)CurrentPage(1)
Где для общего количества страниц >9 будет следовать шестнадцатеричному формату, например A = 10, B = 11, C = 12, D = 13, E = 14…
например, пользовательский штрих-код приложения на английском языке теперь должен выглядеть следующим образом:-
AL3000000000001231E1, 1E2,....., 1EB, 1EC, 1ED, 1EE
Пожалуйста, помогите с некоторыми предложениями относительно того, как я могу реализовать новый шестнадцатеричный формат?
Вот мой сценарий хранимой процедуры:-
ALTER PROCEDURE [outboundprocess].[usp_GetStagedDailyFulfillmentRecords_Mobility]
WITH
EXECUTE AS CALLER
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
SELECT
AT.ServiceTypeID
,[loadfilename]
,[BarCodeID]
,STUFF ( [BarCodeID] , 20 , 1 , 2 )AS DataMatrixCode2
,STUFF ( [BarCodeID] , 20 , 1 , 3 )AS DataMatrixCode3
,STUFF ( [BarCodeID] , 20 , 1 , 4 )AS DataMatrixCode4
,CASE WHEN F.primaryState IN ('AK','AR','KY','MI', 'ND', 'WA', 'WV','LA','MS','AL', 'ID', 'OR') AND f.apptypecode IN (300,301,302,303,310,311,312,313,320,321,330,331)
THEN STUFF ( [BarCodeID] , 20 , 1 , 5 ) ELSE
CASE WHEN F.primaryState = 'SD' AND f.AppTypeCode IN (301,303,311,313,321,331) THEN STUFF ([BarCodeID] , 20 , 1 , 5)
ELSE '' END END AS DataMatrixCode5
,CASE WHEN F.primaryState IN ('AL', 'ID', 'OR') AND f.apptypecode IN (300,301,302,303,310,311,312,313,320,321,330,331)
THEN STUFF ( [BarCodeID] , 20 , 1 , 6) ELSE
CASE WHEN F.primaryState IN ('LA','MS') AND f.AppTypeCode IN (301,303,311,313,321,331) THEN STUFF ([BarCodeID] , 20 , 1 , 6)
ELSE '' END END AS DataMatrixCode6
,ISNULL(f.[AppTypeCode],'') AS [AppTypeCode]
,ISNULL([LanguageCode],'') AS [LanguageCode]
,ISNULL(f.[FirstName],'') AS [FirstName]
,ISNULL(f.[LastName],'') AS [LastName]
,ISNULL([primaryAddress1],'') AS [ServiceAddress1]
,ISNULL([primaryAddress2],'') AS [ServiceAddress2]
,ISNULL([primaryApartmentNumber],'') AS [ServiceApartmentNumber]
,ISNULL([primaryCity],'') AS [ServiceCity]
,ISNULL([primaryState],'') AS [ServiceState]
,ISNULL([primaryZipCode5],'') AS [ServiceZipCode5]
,ISNULL([primaryZipCode4],'') AS [ServiceZipCode4]
,ISNULL(f.[mailingAddress1],'') AS [BillingAddress1]
,ISNULL(f.[mailingAddress2],'') AS [BillingAddress2]
,ISNULL([mailingApartmentNumber],'') AS [BillingApartmentNumber]
,ISNULL([mailingCity],'') AS [BillingCity]
,ISNULL([mailingState],'') AS [BillingState]
,ISNULL([mailingZipCode5],'') AS [BillingZip5]
,ISNULL([mailingZipCode4],'') AS [BillingZip4]
,ISNULL([ReasonCodes],'') AS [ReasonCodes]
,ISNULL(f.[ContactPhoneNumber],'') AS [PhoneNumber]
,ISNULL([ContactEmailAddress],'') AS [EmailAddress]
,CASE WHEN at.servicetypeid='M' THEN ISNULL([CustomerAccountNumber],'') ELSE
ISNULL(InternetAcctNum,'') END AS [CustomerAccountNumber]
,ISNULL([CustomerServiceNumber],'') AS [CustomerServiceNumber]
FROM [outboundprocess].[DailyFulfillmentFile] F
JOIN dbo.AppTypes AT ON AT.AppTypeID=f.AppTypeCode
JOIN dbo.Customers c ON c.CustomerID=f.CustomerID
WHERE f.loadrowstatus = 'Loaded' AND AT.servicetypeid='M' AND c.ServiceAddressState<>'NV' <pre>
И вот эта функция используется:-
ALTER FUNCTION [dbo].[udf_GetBarCodeID] (
@StateCode char(2),
@ObjectTypeID int,
@ApplicationID bigint
)
RETURNS varchar(20)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @LanguageID INT,@CustomerID bigint
DECLARE @ObjectTypeIDString varchar(3) = IsNull(@ObjectTypeID, 0)
DECLARE @ApplicationIDString varchar(12) = ''
DECLARE @BarCodeID char(20) = '',@ServiceTypeID CHAR(10),@appType INT
DECLARE @VerType CHAR(1)
DECLARE @ServiceType CHAR(1)
SELECT @ServiceType=at.ServiceTypeID
FROM dbo.Applications a
JOIN dbo.AppTypes at ON at.AppTypeID=a.AppType
WHERE ApplicationID=@ApplicationID
SET @ApplicationIDString = Replicate('0', 12- Len(@ApplicationID)) + Convert(varchar(12), @ApplicationID)
SELECT @appType=AppType,@CustomerID=CustomerID,@ServiceTypeID=(CASE WHEN AppType IN (300,301,302,303,310,311,312,313,320,321,330,331)
THEN 'M' ELSE 'N' END) ,@VerType=(CASE WHEN AppType IN (300,301,302,303,310,311,312,313,320,321,330,331)
THEN '1' ELSE '2' END) FROM dbo.Applications WHERE ApplicationID=@ApplicationID
SELECT TOP 1 @LanguageID=ISNULL(LanguageID,1) FROM dbo.Customers WHERE @customerID=CustomerID ORDER BY CustomerID desc
IF (@objectTypeID Is Null) OR (@objectTypeID ='')
or (@StateCode IS NULL) OR (@StateCode ='')
OR (@ApplicationID is Null)OR (@ApplicationID < 1) --or (@BarCodeSuffix Is Null)
BEGIN
SET @BarCodeID = 'ErrorCreatingBarCode'
END
ELSE
BEGIN
IF ( @StateCode IN ( 'AL', 'ID', 'OR' )
AND @ServiceTypeID = 'M'
)
OR ( @StateCode IN ( 'LA', 'MS' )
AND @ServiceTypeID = 'M'
AND @LanguageID = 2
)
BEGIN
SET @BarCodeID = @StateCode + @ObjectTypeIDString
+ ISNULL(@ApplicationIDString, '') + @VerType+'61';
END;
ELSE
IF ( @StateCode IN ( 'AK', 'AR','WA', 'WV', 'MI', 'KY', 'ND' )
AND @ServiceTypeID = 'M'
)
OR ( @StateCode IN ( 'LA', 'MS' )
AND @ServiceTypeID = 'M'
AND @LanguageID = 1
)
OR ( @StateCode IN ( 'SD' )
AND @ServiceTypeID = 'M'
AND @LanguageID = 2
)
BEGIN
SET @BarCodeID = @StateCode + @ObjectTypeIDString
+ ISNULL(@ApplicationIDString, '') + @VerType+ '51';
END;
ELSE
IF @StateCode IN ('WI','IL') AND @ServiceType='V' AND @ServiceTypeID = 'N'
OR ( @StateCode IN ( 'SD' )
AND @ServiceTypeID = 'M'
AND @LanguageID = 1
)
OR ( @StateCode IN ( 'TX' )
AND @ServiceTypeID = 'M'
)
BEGIN
SET @BarCodeID = @StateCode + @ObjectTypeIDString
+ ISNULL(@ApplicationIDString, '') + @VerType+ '41';
END;
ELSE
SET @BarCodeID = @StateCode + @ObjectTypeIDString
+ ISNULL(@ApplicationIDString, '') + @VerType + '31';
END
RETURN @BarCodeID
END
Что я уже пробовал:
Я устал гуглить некоторые предложения, но не пришел с большой помощью.