E-Invoice QR Code TLV Base64 string in SQL Server | ZATCA | Tax Invoice | KSA

Question

#e-Invoice #Tax-Invoice #KSA-Invoice #SQL-Server-E-Invoice #ZATCA

how do I generate TLV based64 string in SQL Server? below code works well in C# but in my case, I need to generate the same string in SQL Server.

there is a way around that I can use DLL/COM in SQL Procedure Run a DLL-based COM object outside the SQL Server process but avoiding this due to security issues and other concerns.

Basically, two methods to be converted in SQL

1. public String ConvertBase64(String sellername, String vatregistration, String timestamp, string invoiceamount,String vatamoun)

2. public byte[] ConvertTLV(String tagnums, String tagvalue)

   public partial class Form1 : Form
   {
       public Form1()
       {
           InitializeComponent();
       }
   
       private void Form1_Load(object sender, EventArgs e)
       {
   
       }
   
       public String GenerateAndGetString()
       {
   
           var sellername = " حسيب احمد";
           var vatregistration = "810866391234567";
           var timestamp = "2020-03-22 10:16:14";
           var invoiceamount = "1000.00";
           var vatamoun = "150.00";
   
           return ConvertBase64(sellername, vatregistration, timestamp, invoiceamount, vatamoun);
       }
   
       public String ConvertBase64(String sellername, String vatregistration, String timestamp, String invoiceamount,
           String vatamoun)
       {
           string ltr = ((char)0x200E).ToString();
           var seller = ConvertTLV("1", sellername);
           var vatno = ConvertTLV("2", vatregistration);
           var time = ConvertTLV("3", timestamp);
           var invamt = ConvertTLV("4", invoiceamount);
           var vatamt = ConvertTLV("5", vatamoun);
           var result = seller.Concat(vatno).Concat(time).Concat(invamt).Concat(vatamt).ToArray();
           Console.WriteLine(result);
           Console.WriteLine(result.ToString());
           var output = Convert.ToBase64String(result);
           Console.WriteLine(output);
           return output;
       }
   
   
   
       public byte[] ConvertTLV(String tagnums, String tagvalue)
       {
           string[] tagnums_array = { tagnums };
           var tagvalue1 = tagvalue;
           var tagnum = tagnums_array.Select(s => Byte.Parse(s)).ToArray();
           var tagvalueb = System.Text.Encoding.UTF8.GetBytes(tagvalue1);
           string[] taglengths = { tagvalueb.Length.ToString() };
           var tagvaluelengths = taglengths.Select(s => Byte.Parse(s)).ToArray();
           var tlvVAlue = tagnum.Concat(tagvaluelengths).Concat(tagvalueb).ToArray();
   
   
           return tlvVAlue;
       }
   
       private void button1_Click(object sender, EventArgs e)
       {
           //GENEARING AND GETTING OUTPUT STRING HERE
           textBox1.Text=GenerateAndGetString();
       }
   }
   

Answer 1

i have made this solution in SQL Server : -- ************************************************************************************

Begin Try  Drop FUNCTION StringToBase64_Func End Try Begin Catch End Catch 
Go --xxxxx

CREATE FUNCTION StringToBase64_Func(@InputString VARCHAR(MAX)) 
RETURNS VARCHAR(MAX) AS
BEGIN
  RETURN (
    SELECT CAST(@InputString as varbinary(max)) FOR XML PATH(''), BINARY BASE64 
  ) 
END

Go --xxxxx
-- ************************************************************************************

IF exists (select [name] from sysobjects Where [name] = 'SalesTaxVio') BEGIN  Drop View SalesTaxVio END 
Go --xxxxx

Create View SalesTaxVio As 

Select H.*, CONVERT(varchar(250), CN.SValue) As CompanyName, CONVERT(varchar(250), TN.SValue) As CompanyTaxNumber, 
       CONVERT(varchar(50), CONVERT(datetime, CONVERT(date, H.TrxDate)) + CONVERT(datetime, CONVERT(time, IsNull(H.TrxTime, 0))), 127) As TrxDateTime, 
       CONVERT(varchar(50), H.TrxAmount) As TrxAmountStr, CONVERT(varchar(50), H.TaxValue) As TaxValueStr 
From   saTrxHeader H 
  LEFT OUTER JOIN Settings CN on CN.SName = 'CompanyName' 
  LEFT OUTER JOIN Settings TN on TN.SName = 'CompanyTaxNumber' 

Go --xxxxx
-- ************************************************************************************

IF exists (select [name] from sysobjects Where [name] = 'saTrxHeaderTaxVio') BEGIN  Drop View saTrxHeaderTaxVio END 
Go --xxxxx

Create View saTrxHeaderTaxVio As 

Select H.*, 
       dbo.StringToBase64_Func(CONVERT(varbinary(1564), 
       '01' + convert(nvarchar, convert(varbinary(1), Len(H.CompanyName       )), 2) + convert(nvarchar(1000), convert(varbinary(500), H.CompanyName       ), 2) + 
       '02' + convert(nvarchar, convert(varbinary(1), Len(H.CompanyTaxNumber  )), 2) + convert(nvarchar(1000), convert(varbinary(500), H.CompanyTaxNumber  ), 2) + 
       '03' + convert(nvarchar, convert(varbinary(1), Len(H.TrxDateTime       )), 2) + convert(nvarchar(1000), convert(varbinary(500), H.TrxDateTime       ), 2) + 
       '04' + convert(nvarchar, convert(varbinary(1), Len(H.TrxAmountStr      )), 2) + convert(nvarchar(1000), convert(varbinary(500), H.TrxAmountStr      ), 2) + 
       '05' + convert(nvarchar, convert(varbinary(1), Len(H.TaxValueStr       )), 2) + convert(nvarchar(1000), convert(varbinary(500), H.TaxValueStr       ), 2) 
       , 2)) As TaxQRCode 
From   SalesTaxVio H 

Go --xxxxx
-- ************************************************************************************

but only works with Latin characters in company name. if company name contains Arabic characters it does not work.