(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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    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..
    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, ''
    IF @success <> 1
      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 <> 1
      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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    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 sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://id.preprod.eta.gov.eg/connect/token', @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 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
        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 @resp

        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
        RETURN
      END

    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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

    EXEC @hr = sp_OADestroy @resp

    -- ------------------------------------------------------------------------
    -- 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

    EXEC sp_OAMethod @sbToSend, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, 'https://api.preprod.invoicing.eta.gov.eg/api/v1/documentsubmissions', 'application/json; charset=utf-8', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 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 @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 @jsonToken


END
GO