(SQL Server) Generate an E-way Bill

Demonstrates how to send an HTTP POST request to generate an e-way bill.

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)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example uses the previously obtained access token that was retrieved
    -- in this example:  Get EWAY Auth Token using Gstin, username, password, and app_key

    DECLARE @jsonAuth int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonAuth OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonAuth, 'LoadFile', @success OUT, 'qa_data/tokens/ewayAuth.json'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @jsonAuth, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonAuth
        RETURN
      END

    -- The jsonAuth contains something like this:
    -- {
    --   "authToken": "IBTeFtxNfVurg71LTzZ2r0xK7",
    --   "decryptedSek": "5g1TyTie7yoslU3DrbYATa7mWyPazlODE7cEh5Vy4Ho="
    -- }

    -- Generate the JSON data for the e-way bill.
    -- The following code can be generated by pasting representative JSON into this online tool:
    -- Generate JSON Code
    DECLARE @jsonData int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonData OUT

    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'supplyType', 'O'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'subSupplyType', '1'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docType', 'INV'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docNo', 'AW1234-2'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docDate', '05/04/2018'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromGstin', '09ABDC24212B1FK'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromTrdName', 'willy'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromAddr1', '3RD CROSS NO 200  19  A'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromAddr2', 'GROUND FLOOR OZZY ROAD'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromPlace', 'BUSY TOWN'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'fromPincode', '640033'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'actFromStateCode', '05'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'fromStateCode', '05'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toGstin', '01AAAASCC10BBBB'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toTrdName', 'mthustra'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toAddr1', 'Shrek Ogre'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toAddr2', 'Basadronsil'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toPlace', 'Grifl Blagar'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'toPincode', '699988'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'actToStateCode', '29'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'toStateCode', '02'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'totalValue', '5609889'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'cgstValue', '0'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'sgstValue', '0'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'igstValue', '168296.67'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'cessValue', '224395.56'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transporterId', '09ABDC24212B1FK'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transporterName', ''
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDocNo', '12332'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'transMode', '1'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDistance', '656'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDocDate', '10/04/2018'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'vehicleNo', 'PBN4567'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'vehicleType', 'R'
    EXEC sp_OASetProperty @jsonData, 'I', 0
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].productName', 'Wheat'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].productDesc', 'Wheat'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].hsnCode', '1001'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].quantity', '4'
    EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].qtyUnit', 'BOX'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cgstRate', '0'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].sgstRate', '0'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].igstRate', '3'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cessRate', '4'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cessAdvol', '0'
    EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].taxableAmount', '5609889'

    -- The body of the HTTP POST will contain JSON that looks like this:
    -- 	{
    -- 	"action":"GENEWAYBILL",
    -- 	"data": " iJiJGXq ... oUZp/25Y "
    -- 	}

    -- The "data" is the encrypted jsonData using our previously agreed-upon symmetric encryption key.
    -- Let's begin build the JSON request body..
    DECLARE @jsonRequestBody int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRequestBody OUT

    EXEC sp_OAMethod @jsonRequestBody, 'UpdateString', @success OUT, 'action', 'GENEWAYBILL'

    -- Setup the encryptor using the decryptedSek from the jsonAuth
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'ecb'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OAMethod @jsonAuth, 'StringOf', @sTmp0 OUT, 'decryptedSek'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    -- Encrypt the jsonData and add it to our JSON request body
    EXEC sp_OAMethod @jsonData, 'Emit', @sTmp1 OUT
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @sTmp1
    EXEC sp_OAMethod @jsonRequestBody, 'UpdateString', @success OUT, 'data', @sTmp0

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Add the authtoken to the request header.
    -- Be careful to be precise with uppercase/lowercase ("authtoken" vs "authToken")
    EXEC sp_OAMethod @jsonAuth, 'StringOf', @sTmp0 OUT, 'authToken'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'authtoken', @sTmp0
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Gstin', '09ABDC24212B1FK'
    EXEC sp_OASetProperty @http, 'Accept', 'application/json'

    -- POST the request to generate an e-way bill:
    DECLARE @resp int
    EXEC sp_OAMethod @jsonRequestBody, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, 'http://ewb.wepgst.com/api/EWayBill', 'application/json', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonAuth
        EXEC @hr = sp_OADestroy @jsonData
        EXEC @hr = sp_OADestroy @jsonRequestBody
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT

    PRINT 'response status code =' + @respStatusCode

    PRINT 'response body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    IF @respStatusCode <> 200
      BEGIN
        EXEC @hr = sp_OADestroy @resp


        PRINT 'Failed in some unknown way.'
        EXEC @hr = sp_OADestroy @jsonAuth
        EXEC @hr = sp_OADestroy @jsonData
        EXEC @hr = sp_OADestroy @jsonRequestBody
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- When the response status code = 200, we'll have either
    -- success response like this:
    --  {"status":"1","data":"..."}
    -- 
    -- or a failed response like this:
    -- 
    -- {"status":"0","error":"eyJlcnJvckNvZGVzIjoiMTA4In0="}

    -- Load the response body into a JSON object.
    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_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC @hr = sp_OADestroy @resp

    DECLARE @status int
    EXEC sp_OAMethod @json, 'IntOf', @status OUT, 'status'

    PRINT 'status = ' + @status

    IF @status <> 1
      BEGIN
        -- Failed.  Base64 decode the error
        -- {"status":"0","error":"eyJlcnJvckNvZGVzIjoiMTA4In0="}
        -- For an invalid password, the error is: {"errorCodes":"108"}
        DECLARE @sbError int
        -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbError OUT

        EXEC sp_OAMethod @json, 'StringOfSb', @success OUT, 'error', @sbError
        EXEC sp_OAMethod @sbError, 'Decode', @success OUT, 'base64', 'utf-8'

        EXEC sp_OAMethod @sbError, 'GetAsString', @sTmp0 OUT
        PRINT 'error: ' + @sTmp0
        EXEC @hr = sp_OADestroy @jsonAuth
        EXEC @hr = sp_OADestroy @jsonData
        EXEC @hr = sp_OADestroy @jsonRequestBody
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbError
        RETURN
      END

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    PRINT 'JSON response:'
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @bdData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdData OUT

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'data'
    EXEC sp_OAMethod @bdData, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bdData

    -- Decrypts to 
    -- {"ewayBillNo":331001121234,"ewayBillDate":"24/05/2018 04:38:00 PM","validUpto":"31/05/2018 11:59:00 PM"}

    DECLARE @jsonBill int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonBill OUT

    EXEC sp_OAMethod @bdData, 'GetString', @sTmp0 OUT, 'utf-8'
    EXEC sp_OAMethod @jsonBill, 'Load', @success OUT, @sTmp0

    DECLARE @ewayBillNo int
    EXEC sp_OAMethod @jsonBill, 'IntOf', @ewayBillNo OUT, 'ewayBillNo'

    PRINT 'ewayBillNo = ' + @ewayBillNo

    DECLARE @ewayBillDate nvarchar(4000)
    EXEC sp_OAMethod @jsonBill, 'StringOf', @ewayBillDate OUT, 'ewayBillDate'

    PRINT 'ewayBillDate = ' + @ewayBillDate

    DECLARE @validUpto nvarchar(4000)
    EXEC sp_OAMethod @jsonBill, 'StringOf', @validUpto OUT, 'validUpto'

    PRINT 'validUpto = ' + @validUpto

    -- Sample output:

    -- ewayBillNo = 331001121234
    -- ewayBillDate = 24/05/2018 04:55:00 PM
    -- validUpto = 31/05/2018 11:59:00 PM

    EXEC @hr = sp_OADestroy @jsonAuth
    EXEC @hr = sp_OADestroy @jsonData
    EXEC @hr = sp_OADestroy @jsonRequestBody
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbError
    EXEC @hr = sp_OADestroy @bdData
    EXEC @hr = sp_OADestroy @jsonBill


END
GO