In addition to parcel by parcel inserts using the scanner it is possible to perform bulk inserts into GramChain.
The preferred method to do this is to format the data into a large Json string in the body of a “post” submission (or via a file) by utilizing the following API: /api/secured/GramChainBulkInsert
Which requires the following parameters:
- JsonObject – This the Json object (see below)
- languageid – e.g. “en”, determines potential error message language
- entityid – if applicable / if data is to be separate from main data
- callerid – The Account ID of the submitter
The JsonObject must have the following parameters which are nearly identical to the GramChainInsertViaScanner API parameters with exception of the image-url:
- imageurl: this is the primary deviation from GramChainInsertViaScanner. Instead of requiring the Base64 serialization of a file – which would make the string huge - the public URL of the associated image file is required instead. The system will retrieve the image and convert it into a Base64 automatically as it processes each insert.
- rfid: A parcel specific RFID is required. Data cannot be insert without an RFID as we need to track the parcel physically. Note that some RFIDs have a programmable “friendly” ID along with their original RFID code. As long as the RFID is scannable with the scanner these friendly IDs would be fine.
- eventtypecode: assuming this is a new entry always use event “scan”
- scannerid: must be custom assigned ahead of bulk insert as it will determine vault location and other details
- itemquantity: self-explanatory, must be an integer
- brandcode: must be one of the existing brandcodes, for a list of valid brandcodes view API\Dropdownvalues\BrandCodes
- itemtypecode: must be one of the existing ItemtypeCodes, for a list of valid ItemtypeCodes view API\Dropdownvalues\ItemtypeCodes
- serialnr: this is either the serial number of the bar or, if multiple items or the item does not have a code, then it is the serial number of a tamper evident bag
- metalcode: must be one of the existing metalcodes, for a list of valid metalcodes view API\Dropdownvalues\metalcodes
- measurementcode: normally “g” for grams or “ozt” for troy oz. For a list of valid measurementcodes view API\Dropdownvalues\measurementcodes
- measurementweight: the amount of mass as expressed by the measurementcode chosen
- purity: must be between 0 and 1 with up to 4 digits. e.g. “0.9999”
Sample Bulk-Insert Json Generation
The JsonObject can be generated in many ways. The example below illustrates how SQL Server 2017 can generate a valid JsonObject string using T-SQL:
declare @jsonOutput NVARCHAR(MAX)
SET @jsonOutput = (
select * from
select top 100
'https://www.silverbullion.com.sg/uploads/parcelphotos/'+ SerialNr + '.jpg' as imageurl,
serialnr as rfid,
'scan' as eventtypecode,
'00:04:3E:94:E0:41' as scannerid,
productqty as itemquantity,
isnull(Minter,'') as brandcode,
productsubgroup as itemtypecode,
serialnr as serialnr,
metalcode as metalcode,
'g' as measurementcode,
(MeasuredRawGram/productqty) as measurementweight,
purity/100 as purity
from [dbo].[fn_StorageStatementByDate] ('SBSG','','','','2019-1-1','','',0,0,'sgd') as g
join tbh_products on g.productid = tbh_products.productid
where metalcode = 'AU'
) as myTable FOR JSON AUTO)
- The imageurl follows the current system image naming convention / path.
- RFIDs and Serial nr. Are the same in our system. We use friendly RFID values.
- ScannerID must be assigned for the import
- Purity is divided by 100 because the storage convention is different
- For JSON AUTO generates the JSON string which is assigned to @jsonOutput
Before being returned @jsonOutput is “escaped” so that the string can be pasted into the body of the post field. “Escaping essentially means that the string is made URL Friendly. In T-SQL this is don with the STRNG_ESCAPE function as follows:
The resulting string can then be pasted in into the post body using a REST client to call the API.