Incoming CSV File Parsing - Populating the Axeda Enterprise

    These code snippets illustrate parsing CSV files and populating the Axeda Enterprise with data, locations and organizations.  These files are incoming to the Axeda Platform.

     

    Note:  These snippets do NOT handle null values in the CSV due to the lack of a CSV parsing library.  Workaround is to populate empty values with an empty or null signifier (such as a blank space) and test for these on the Groovy side.

     

    Code Snippets:


    CSV file to Data Items
    CSV file to Location Organization


    Script Name: CSV file to Data Items


    Description: Executed from an expression rule with file hint "datainsert", takes a CSV file and adds data based on values.


    Parameters:

    OPTIONAL - only needed for debugging

    1. modelName - (OPTIONAL) Str - name of the model
    2. serialNumber - (OPTIONAL) Str - name of the serial number


    import com.axeda.drm.sdk.Context
    import com.axeda.drm.sdk.device.DeviceFinder
    import com.axeda.drm.sdk.device.ModelFinder
    import com.axeda.drm.sdk.device.DataItemFinder
    import com.axeda.drm.sdk.device.DataItem
    import com.axeda.drm.sdk.data.DataValueEntry
    import java.util.regex.Pattern
    import groovy.json.*
    import com.axeda.drm.services.device.DataItemType
    import net.sf.json.JSONObject
    
    /**
    * CSVToData.groovy
    * -----------------------
    *
    * Executed from an expression rule with file hint "datainsert", takes a CSV file and adds data based on values.
    *
    * @note  There must be a column with "model" and one with "serial".  The rest of the columns should be data item names with values
    * in the rows. DOES NOT handle null values in CSV.  Workaround is to insert blank spaces in null values and test for those on the Groovy side.
    * Solution would be to add a library for CSV parsing such as open csv.
    *
    * @params - only needed if NOT executed from expression rule - primarily for debugging
    * modelName - (OPTIONAL) Str - name of the model
    * serialNumber - (OPTIONAL) Str - name of the serial number
    *
    *
    */
    
    /**
    * initialize our global variables
    * json = the contents of our response
    * infoString = a stringBuilder used to collect debug information during the script
    * contentType = the content type we will return
    * scriptname = The name of this Script, used in multiple places
    */
    def json = new groovy.json.JsonBuilder()
    def infoString = new StringBuilder()
    def contentType = "application/json"
    def scriptName = "CSVToData.groovy"
    def root = ["result":["items":[]]]
    
    def columns = []
    
    try {
    
      Context CONTEXT = Context.getSDKContext()
    
      def modelIndex
      def serialIndex
    
      // initialize Model and Device Finders
      ModelFinder modelFinder = new ModelFinder(CONTEXT)
      DeviceFinder deviceFinder = new DeviceFinder(CONTEXT)
    
      // implicit object compressedFile
      File file = compressedFile.getFiles()[0].extractFile()
    
    /* //begin non-expression rule code, useful for debugging
        File file
        modelFinder.setName(Request.parameters.modelname)
                  def model1 = modelFinder.find()
        deviceFinder.setSerialNumber(Request.parameters.serialNumber)
        deviceFinder.setModel(model1)
        def d = deviceFinder.find()
         UploadedFileFinder uff = new UploadedFileFinder(CONTEXT)
        uff.device = d
        def ufiles = uff.findAll()
        UploadedFile ufile
        if (ufiles.size() > 0) {
            ufile = ufiles[0]
            file = ufile.extractFile()
        }
              
    */ //end non-expression rule code
    
      file.eachLine {line ->
          def row = line.tokenize(',')
        
          // set the column headings
          if (columns.size() == 0){
            columns = row
          
            // find model and serial index, assumes there's a column that has "model" and "serial", otherwise take columns 0 and 1
            def modelpatt = Pattern.compile(/[A-Za-z_\-]{0,}model[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)
            def serialpatt = Pattern.compile(/[A-Za-z_\-]{0,}serial[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)
            modelIndex = columns.findIndexOf{ it ==~ modelpatt } > -1 ? columns.findIndexOf{ it ==~ modelpatt } : 0
            serialIndex = columns.findIndexOf{ it ==~ serialpatt } > -1 ? columns.findIndexOf{ it ==~ serialpatt } : 1
          
          }
          // otherwise populate data
          else {
            
              modelFinder.setName(row.get(modelIndex))
              def model = modelFinder.find()
            
              deviceFinder.setModel(model)
              deviceFinder.setSerialNumber(row.get(serialIndex))
            
              def device = deviceFinder.find()
            
              def assetInfo = [
                        "model": model.name,
                        "serial": device.serialNumber,
                        "data":[]
                        ]
            
              row.eachWithIndex{ item, index ->
                  if (index != modelIndex && index != serialIndex){
                    def dataItemName = columns[index].replace(" ","")
                    DataItemFinder dif = new DataItemFinder(CONTEXT);
                    dif.setDataItemName(dataItemName);
                    dif.setModel(model);
                    DataItem dataItem = dif.find();
                  
                    if (dataItem){
                        if (item.isNumber()){
                           item = Double.valueOf(item)
                        }
                        DataValueEntry dve = new DataValueEntry(CONTEXT, device, dataItem, item)
                        dve.store()
                    }
                    else {
                        DataItem newDataItem
                        if (item.isNumber()){
                            newDataItem = new DataItem(CONTEXT, model,DataItemType.ANALOG, dataItemName)
                            item = Double.valueOf(item)
                        }
                        else {
                           newDataItem = new DataItem(CONTEXT, model,DataItemType.STRING, dataItemName)
                        }
                       newDataItem.store()
                       DataValueEntry dve = new DataValueEntry(CONTEXT, device, newDataItem, item)
                        dve.store()
                    }
                    assetInfo.data << [
                            "name": dataItemName,
                            "value": item
                        ]
                  
                  }
                  root.result.items << assetInfo
              }
            
          }
      }
      logger.info(JSONObject.fromObject(root).toString(2))
    
    } catch (Exception e) {
    
        processException(scriptName,json,e)
    }
    
    //return ['Content-Type': 'application/json', 'Content': JSONObject.fromObject(root).toString(2)]
    
    
    
    /*
        Processes the contents of an Exception and add it to the Errors collection
        @param json The markup builder
    */
    private def processException(String scriptName, JsonBuilder json, Exception e) {
        // catch the exception output
        def logStringWriter = new StringWriter()
        e.printStackTrace(new PrintWriter(logStringWriter))
        logger.error("Exception occurred in ${scriptName}: ${logStringWriter.toString()}")
    
        /*
            Construct the error response
            - errorCode Will be an element from an agreed upon enum
            - errorMessage The text of the exception
         */
        json.errors  {
            error {
                message     "[${scriptName}]: " + e.getMessage()
                timestamp   "${System.currentTimeMillis()}"
            }
        }
    
        return json
    }
    

     

    Script Name: CSV file to Location Organization


    Description: Executed from an expression rule with file hint "locorginsert", takes a CSV file and adds orgs and locations based on values.


    Parameters:

    OPTIONAL - only needed for debugging

    1. modelName - (OPTIONAL) Str - name of the model
    2. serialNumber - (OPTIONAL) Str - name of the serial number

     

    import com.axeda.drm.sdk.Context
    import com.axeda.drm.sdk.device.DeviceFinder
    import com.axeda.drm.sdk.device.ModelFinder
    import com.axeda.drm.sdk.device.DataItemFinder
    import com.axeda.drm.sdk.device.DataItem
    import com.axeda.drm.sdk.data.DataValueEntry
    import java.util.regex.Pattern
    import groovy.json.*
    import com.axeda.drm.services.device.DataItemType
    import net.sf.json.JSONObject
    import com.axeda.drm.sdk.contact.Organization
    import com.axeda.drm.sdk.contact.Location
    import com.axeda.drm.sdk.contact.OrganizationFinder
    import com.axeda.drm.sdk.contact.LocationFinder
    import com.axeda.drm.sdk.data.UploadedFile
    import com.axeda.drm.sdk.data.UploadedFileFinder
    
    /**
    * CSVToLocOrg.groovy
    * -----------------------
    *
    * Executed from an expression rule with file hint "locorginsert", takes a CSV file and adds orgs and locations based on values.
    *
    * @note  There must be a column with "model" and one with "serial".  The rest of the columns should be either parts of a
    * location or an organization.  The location parts columns should be prefixed with the org# that they correspond to.
    * DOES NOT handle null values in CSV.  Workaround is to insert blank spaces in null values and test for those on the Groovy side.
    * Solution would be to add a library for CSV parsing such as open csv.
    *
    * @params - only needed if NOT executed from expression rule - primarily for debugging
    * modelName - (OPTIONAL) Str - name of the model
    * serialNumber - (OPTIONAL) Str - name of the serial number
    *
    *
    *
    */
    
    /**
    * initialize our global variables
    * json = the contents of our response
    * infoString = a stringBuilder used to collect debug information during the script
    * contentType = the content type we will return
    * scriptname = The name of this Script, used in multiple places
    */
    def json = new groovy.json.JsonBuilder()
    def infoString = new StringBuilder()
    def contentType = "application/json"
    def scriptName = "CSVToLocOrg.groovy"
    def root = ["result":["items":[]]]
    
    def columns = []
    
    try {
    
      Context CONTEXT = Context.getSDKContext()
    
      def modelIndex
      def serialIndex
    
      def locIndices = [:]
      def locKeys = ["line1","line2", "address1", "address2", "city","state","zip","country", "org"]
    
      // initialize Finders
      ModelFinder modelFinder = new ModelFinder(CONTEXT)
      DeviceFinder deviceFinder = new DeviceFinder(CONTEXT)
      LocationFinder locationFinder = new LocationFinder(CONTEXT)
      OrganizationFinder organizationFinder = new OrganizationFinder(CONTEXT)
    
      // implicit object compressedFile
      File file = compressedFile.getFiles()[0].extractFile()
    
      /* //begin non-expression rule code, useful for debugging
        File file
        modelFinder.setName(Request.parameters.modelname)
                  def model1 = modelFinder.find()
        deviceFinder.setSerialNumber(Request.parameters.serialNumber)
        deviceFinder.setModel(model1)
        def d = deviceFinder.find()
         UploadedFileFinder uff = new UploadedFileFinder(CONTEXT)
        uff.device = d
        def ufiles = uff.findAll()
        UploadedFile ufile
        if (ufiles.size() > 0) {
            ufile = ufiles[0]
            file = ufile.extractFile()
        } 
               
    */ //end non-expression rule code
    
      file.eachLine {line ->
          def row = line.tokenize(',')
    
          // set the column headings
          if (columns.size() == 0){
            columns = row
    
            // find model and serial index, assumes there's a column that has "model" and "serial", otherwise take columns 0 and 1
            def modelpatt = Pattern.compile(/[A-Za-z_\-]{0,}model[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)
            def serialpatt = Pattern.compile(/[A-Za-z_\-]{0,}serial[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)
            modelIndex = columns.findIndexOf{ it ==~ modelpatt } > -1 ? columns.findIndexOf{ it ==~ modelpatt } : 0
            serialIndex = columns.findIndexOf{ it ==~ serialpatt } > -1 ? columns.findIndexOf{ it ==~ serialpatt } : 1
           
            locKeys.each{ key ->
                // construct a regex for each key and create a map for finding/creating
                def locPatt = Pattern.compile(/[A-Za-z0-9_\-]{0,}${key}[A-Za-z0-9_\-]{0,}/, Pattern.CASE_INSENSITIVE)
                def colIndex = columns.findIndexOf{
                        def match = it =~ locPatt
                        if (match){
                            return match?.getAt(0)
                        }
                    }
               
                if (colIndex > -1){
                    locIndices[colIndex] = key
                }
            }
    
          }
          // otherwise populate data
          else {
    
              modelFinder.setName(row.get(modelIndex))
              def model = modelFinder.find()
    
              deviceFinder.setModel(model)
              deviceFinder.setSerialNumber(row.get(serialIndex))
    
              def device = deviceFinder.find()
    
              def assetInfo = [
                        "model": model.name,
                        "serial": device.serialNumber,
                        "locs":[]
                        ]
             
              def locMap = [:]
              def orgName
              def locKey
              def locBool = false // make sure we get some criteria
    
              row.eachWithIndex{ item, index ->
                  
                  if (index != modelIndex && index != serialIndex && item && item != ""){
                      locKey = locIndices[index]
                     
                      if (locKey){
                          locBool = true
                          if (locKey == "address1"){
                            locKey = "line1"  
                          }
                          if (locKey == "address2"){
                            locKey = "line2"  
                          }
                          if (locKey == "org"){
                                orgName = item  
                          }
                          // don't execute if we've got an organization key
                          else {
                              // for finding
                              locationFinder[locKey] = item
                              // for creating (if needed)
                              locMap[locKey] = item
                          }
                      }
                     
                  }  
              }
             
              assetInfo.org
              Organization org
             
              if (orgName){
                  organizationFinder.setName(orgName)
                  org = organizationFinder.find()
                 
                  if (!org){
                    org = new Organization(CONTEXT, orgName)
                    org.store()
        
                  }
                 
              }
            
              Location loc
              if (locBool){
                  logger.info("with bool")
                loc = locationFinder.find()
                logger.info(loc?.name)
              }
             
              if (!loc){
                
                  def line1 = locMap["line1"]
                 
                  def name = line1?.replace(" ","")?.replace(/\./,"")?.replace("_","") + "_Loc"
                 
                  def line2 = locMap["line2"]
                  def city = locMap["city"]
                  def state = locMap["state"]
                  def zip = locMap["zip"]
                  def country = locMap["country"]
                 
                  if (line1 && city){
                   loc = new Location(CONTEXT,name,line1,line2,city,state,zip,country)
                   loc.store()
                  
                  
                  }
                 
                  if (loc && org){
                      org.addLocation(loc)
                      org.store()
                  }
                 
              }
             
              assetInfo.locs << [
                       "name": loc.name,
                        "line1": loc.line1,
                        "line2": loc.line2,
                        "city": loc.city,
                        "state": loc.state,
                        "zip": loc.zip,
                        "country": loc.country
                       
                       ]
                       assetInfo.org = [
                            "name": org.name
                       
                        ]
              root.result.items << assetInfo
          }
      }
      logger.info(JSONObject.fromObject(root).toString(2))
    
    } catch (Exception e) {
    
        processException(scriptName,json,e)
    }
    
    //return ['Content-Type': 'application/json', 'Content': JSONObject.fromObject(root).toString(2)]
    
    
    
    /*
        Processes the contents of an Exception and add it to the Errors collection
        @param json The markup builder
    */
    private def processException(String scriptName, JsonBuilder json, Exception e) {
        // catch the exception output
        def logStringWriter = new StringWriter()
        e.printStackTrace(new PrintWriter(logStringWriter))
        logger.error("Exception occurred in ${scriptName}: ${logStringWriter.toString()}")
    
        /*
            Construct the error response
            - errorCode Will be an element from an agreed upon enum
            - errorMessage The text of the exception
         */
        json.errors  {
            error {
                message     "[${scriptName}]: " + e.getMessage()
                timestamp   "${System.currentTimeMillis()}"
            }
        }
    
        return json
    }