SQL Server
SQL Server
Create ITIDA Signed JSON and Send to ETA (Egypt Tax Authority) Portal
See more Egypt ITIDA Examples
Demonstrates how to create a .p7s signature that fits Egypt's ITIDA requirements where Chilkat automatically does the ITIDA JSON canonicalization. Also shows the code to 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.)
-- {
-- "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,
-- }
-- Build the above JSON..
-- Use this online tool to generate code from sample JSON:
-- Generate Code to Create JSON
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.branchID', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.country', 'EG'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.regionCity', 'Cairo'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.postalCode', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.buildingNumber', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.street', '123rd Street'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.address.governate', 'GOVERNATE'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.type', 'B'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.id', '209999899'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'issuer.name', 'Xyz SAE'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.country', 'EG'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.regionCity', 'CAIRO'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.postalCode', '11435'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.buildingNumber', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.street', 'Autostrad Road Abc'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.address.governate', 'GOVERNATE'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.type', 'B'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.id', '999999999'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'receiver.name', 'XYZ EGYPT FOR TRADE'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documentType', 'I'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documentTypeVersion', '1.0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'dateTimeIssued', '2020-11-15T11:04:53Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'taxpayerActivityCode', '1073'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'internalID', 'ZZZZ999'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'purchaseOrderReference', '2009199918'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'salesOrderReference', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.bankName', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.bankAddress', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.bankAccountNo', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.bankAccountIBAN', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.swiftCode', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'payment.terms', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.approach', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.packaging', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.dateValidity', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.exportPort', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.countryOfOrigin', 'EG'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'delivery.grossWeight', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'delivery.netWeight', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'delivery.terms', ''
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].description', 'CDM Widget 48GX99X12BA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].itemType', 'GS1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].itemCode', '7622213335056'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].unitType', 'CS'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].quantity', '1.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].unitValue.currencySold', 'EGP'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].unitValue.amountEGP', '588.67'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoiceLines[0].unitValue.amountSold', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoiceLines[0].unitValue.currencyExchangeRate', 0
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].salesTotal', '588.67'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].total', '603.97'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoiceLines[0].valueDifference', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoiceLines[0].totalTaxableFees', 0
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].netTotal', '529.8'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoiceLines[0].itemsDiscount', 0
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].discount.rate', '10.00'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].discount.amount', '58.87'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].taxableItems[0].taxType', 'T1'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].taxableItems[0].amount', '74.17'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].taxableItems[0].subType', 'No sub'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'invoiceLines[0].taxableItems[0].rate', '14.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoiceLines[0].internalCode', '9099994'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'totalSales', '588.67'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'totalSalesAmount', '588.67'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'totalDiscountAmount', '58.87'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'netAmount', '529.80'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'taxTotals[0].taxType', 'T1'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'taxTotals[0].amount', '74.17'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'extraDiscountAmount', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'totalItemsDiscountAmount', 0
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'totalAmount', '603.97'
EXEC sp_OASetProperty @json, 'EmitCompact', 1
DECLARE @jsonToSign nvarchar(4000)
EXEC sp_OAMethod @json, 'Emit', @jsonToSign OUT
-- 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 @sigBase64 nvarchar(4000)
EXEC sp_OAMethod @crypt, 'SignStringENC', @sigBase64 OUT, @jsonToSign
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
EXEC @hr = sp_OADestroy @json
RETURN
END
PRINT 'Base64 signature:'
PRINT @sigBase64
-- Insert the base64 signature into the JSON to be sent
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].signatureType', 'I'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].value', @sigBase64
-- 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 @jsonStr nvarchar(4000)
EXEC sp_OAMethod @sbToSend, 'GetAsString', @jsonStr OUT
DECLARE @url nvarchar(4000)
SELECT @url = 'https://api.preprod.invoicing.eta.gov.eg/api/v1/documentsubmissions'
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