SQL Server
SQL Server
Sign ITIDA JSON and Send to ETA (Egypt Tax Authority) Portal
See more Egypt ITIDA Examples
Demonstrates how to ITIDA canonicalize JSON, create signature, and send to the ETA Portal.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @crypt, 'VerboseLogging', 1
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OASetProperty @cert, 'VerboseLogging', 1
-- Set the smart card PIN, which will be needed for signing.
EXEC sp_OASetProperty @cert, 'SmartCardPin', '12345678'
-- There are many ways to load the certificate.
-- This example was created for a customer using an ePass2003 USB token.
-- Assuming the USB token is the only source of a hardware-based private key..
EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, ''
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- Tell the crypt class to use this cert.
EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @cmsOptions int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @cmsOptions OUT
-- Setting "DigestData" causes OID 1.2.840.113549.1.7.5 (digestData) to be used.
EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'DigestData', 1
EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'OmitAlgorithmIdNull', 1
-- Indicate that we are passing normal JSON and we want Chilkat do automatically
-- do the ITIDA JSON canonicalization:
EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'CanonicalizeITIDA', 1
EXEC sp_OAMethod @cmsOptions, 'Emit', @sTmp0 OUT
EXEC sp_OASetProperty @crypt, 'CmsOptions', @sTmp0
-- The CadesEnabled property applies to all methods that create CMS/PKCS7 signatures.
-- To create a CAdES-BES signature, set this property equal to true.
EXEC sp_OASetProperty @crypt, 'CadesEnabled', 1
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
DECLARE @jsonSigningAttrs int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonSigningAttrs OUT
EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'contentType', 1
EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'signingTime', 1
EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'messageDigest', 1
EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'signingCertificateV2', 1
EXEC sp_OAMethod @jsonSigningAttrs, 'Emit', @sTmp0 OUT
EXEC sp_OASetProperty @crypt, 'SigningAttributes', @sTmp0
-- By default, all the certs in the chain of authentication are included in the signature.
-- If desired, we can choose to only include the signing certificate:
EXEC sp_OASetProperty @crypt, 'IncludeCertChain', 0
-- Pass a JSON document such as the following. Chilkat will do the ITIDA canonicalization.
-- (It is the canonicalized JSON that gets signed.)
-- Note: The JSON should NOT begin with "{ "documents" : [ ..."
-- {
-- "issuer":{
-- "address":{
-- "branchID":"0",
-- "country":"EG",
-- "regionCity":"Cairo",
-- "postalCode":"",
-- "buildingNumber":"0",
-- "street":"123rd Street",
-- "governate":"GOVERNATE"
-- },
-- "type":"B",
-- "id":"209999899",
-- "name":"Xyz SAE"
-- },
-- "receiver":{
-- "address":{
-- "country":"EG",
-- "regionCity":"CAIRO",
-- "postalCode":"11435",
-- "buildingNumber":"0",
-- "street":"Autostrad Road Abc",
-- "governate":"GOVERNATE"
-- },
-- "type":"B",
-- "id":"999999999",
-- "name":"XYZ EGYPT FOR TRADE"
-- },
-- "documentType":"I",
-- "documentTypeVersion":"1.0",
-- "dateTimeIssued":"2020-11-15T11:04:53Z",
-- "taxpayerActivityCode":"1073",
-- "internalID":"ZZZZ999",
-- "purchaseOrderReference":"2009199918",
-- "salesOrderReference":"",
-- "payment":{
-- "bankName":"",
-- "bankAddress":"",
-- "bankAccountNo":"",
-- "bankAccountIBAN":"",
-- "swiftCode":"",
-- "terms":""
-- },
-- "delivery":{
-- "approach":"",
-- "packaging":"",
-- "dateValidity":"",
-- "exportPort":"",
-- "countryOfOrigin":"EG",
-- "grossWeight":0,
-- "netWeight":0,
-- "terms":""
-- },
-- "invoiceLines":[
-- {
-- "description":"CDM Widget 48GX99X12BA",
-- "itemType":"GS1",
-- "itemCode":"7622213335056",
-- "unitType":"CS",
-- "quantity":1.00,
-- "unitValue":{
-- "currencySold":"EGP",
-- "amountEGP":588.67,
-- "amountSold":0,
-- "currencyExchangeRate":0
-- },
-- "salesTotal":588.67,
-- "total":603.97,
-- "valueDifference":0,
-- "totalTaxableFees":0,
-- "netTotal":529.8,
-- "itemsDiscount":0,
-- "discount":{
-- "rate":10.00,
-- "amount":58.87
-- },
-- "taxableItems":[
-- {
-- "taxType":"T1",
-- "amount":74.17,
-- "subType":"No sub",
-- "rate":14.00
-- }
-- ],
-- "internalCode":"9099994"
-- }
-- ],
-- "totalSales":588.67,
-- "totalSalesAmount":588.67,
-- "totalDiscountAmount":58.87,
-- "netAmount":529.80,
-- "taxTotals":[
-- {
-- "taxType":"T1",
-- "amount":74.17
-- }
-- ],
-- "extraDiscountAmount":0,
-- "totalItemsDiscountAmount":0,
-- "totalAmount":603.97,
-- }
-- Create the CAdES-BES signature.
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
-- Make sure we sign the utf-8 byte representation of the JSON string
EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
DECLARE @jsonInvoice nvarchar(4000)
SELECT @jsonInvoice = '{ ... }'
DECLARE @sigBase64 nvarchar(4000)
EXEC sp_OAMethod @crypt, 'SignStringENC', @sigBase64 OUT, @jsonInvoice
EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
RETURN
END
PRINT 'Base64 signature:'
PRINT @sigBase64
-- Insert the base64 signature into the JSON to be sent
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonInvoice
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].signatureType', 'I'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].value', @sigBase64
EXEC sp_OASetProperty @json, 'EmitCompact', 1
-- Wrap the JSON in {"documents":[ ... ]}
DECLARE @sbToSend int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbToSend OUT
EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, '{"documents":['
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, ']}'
-- ------------------------------------------------------------------------
-- Get an access token using our client ID and client secret key
DECLARE @clientId nvarchar(4000)
SELECT @clientId = 'abc999ff-1234'
DECLARE @clientSecretKey nvarchar(4000)
SELECT @clientSecretKey = '123fff22-1234-abcd'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- Causes the Authorization: Basic header to be added..
EXEC sp_OASetProperty @http, 'Login', @clientId
EXEC sp_OASetProperty @http, 'Password', @clientSecretKey
EXEC sp_OASetProperty @http, 'BasicAuth', 1
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'Path', '/connect/token'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Connection', 'close'
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://id.preprod.eta.gov.eg/connect/token', @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbToSend
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAMethod @http, 'CloseAllConnections', @success OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code: ' + @iTmp0
PRINT 'Response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbToSend
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonToken, 'Load', @success OUT, @sTmp0
DECLARE @accessToken nvarchar(4000)
EXEC sp_OAMethod @jsonToken, 'StringOf', @accessToken OUT, 'access_token'
PRINT 'access_token = ' + @accessToken
-- ------------------------------------------------------------------------
-- Submit the signed JSON to the ETA (Egypt Tax Authority) Portal
-- No longer sending basic authentication...
EXEC sp_OASetProperty @http, 'Login', ''
EXEC sp_OASetProperty @http, 'Password', ''
EXEC sp_OASetProperty @http, 'BasicAuth', 0
-- Setting the AuthToken property causes the "Authorization: Bearer <token>" header to be added to each request.
EXEC sp_OASetProperty @http, 'AuthToken', @accessToken
DECLARE @url nvarchar(4000)
SELECT @url = 'https://api.preprod.invoicing.eta.gov.eg/api/v1/documentsubmissions'
DECLARE @jsonStr nvarchar(4000)
EXEC sp_OAMethod @sbToSend, 'GetAsString', @jsonStr OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @url, @jsonStr, 'utf-8', 'application/json; charset=utf-8', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbToSend
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code: ' + @iTmp0
PRINT 'Response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbToSend
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonToken
END
GO