Accessing External Data Source Using iReporter and ConMas Gateway

iReporter can be used as the main tool for reduced the manual filling of reporting and inspection form in many fields. i-Reporter has various function to support the field workers to input the data with easily without mistakes. This Data can be acquired from existing or the external sources. This article describe how to access external data source in Excel format using iReporter and ConMas Gateway.

What is ConMas Gateway?

ConMas Gateway is software that enables real-time data acquisition from external data sources. ConMas Gateway can get any external data source in real time to i-Reporter reports. Can also send the form input data to external source.

The following example can be realized by using ConMas Gateway :

1. Use the current real information of other systems without automatic form creation.

2. Get the master data directly from the various master DBs of the business system in real time without using a custom master.

3. Acquire business data directly in real time from an external system such as a core system, production management system, or cloud.

4. Get the data of IoT device directly from the device in real time.

5. Acquire the past input contents (including images) of other forms.

6. Using multiple cluster values entered in the form (Generate a graph, Generate QR code, Generate a one-dimensional barcode).

7. Perform complex calculation using multiple cluster values entered in the form and obtain the calculation results. (Support for unsupported EXCEL functions.)

Starting ConMas Gateway

Go to the folder where ConMas Gateway is installed. If the installed folder is [\ConMas\gateway], use the following command to move the folder.

＞cd \ConMas\gateway

After that execute the following command

＞node index.js

Business Case

This example of performing inspecton work by using ConMas Gateway to accquire the data from Excel Check sheet and display it to iReporter iPad.

Product inspection is the process of checking goods for compliance based on specifications and requirements. The product inspection process primarily focuses on checking the appearance, construction and basic function of a product.

One of the targets is the Nikon MM-400 measurement data which is used as a reference for the inspection process and is currently not integrated with iReporter. The integration of the Nikon MM-400 measurement data with the iReporter will provide advantages in terms of accuracy and speed so as to increase overall performance.

The details of the above diagram as follows:

1. Nikon MM-400 PC a. A part is measured by Nikon MM-400. b. Excel file will be generated by Nikon MM-400 located in device PC and shared folder located in ConMas Gateway server

2. ConMas Gateway Server a. ConMas gateway server running in port 3000 to provide web service. b. The web service use Python to read the excel check sheet. c. Python script will read Excel check sheet that generated by Nikon MM-400. d. Python script mapped to each check sheet excel template. e. The result will be displayed in iReporter iPad.

3. iReporter iPad a. Create new document based on available template after MM-400 measuring the part. b. When document opened, iReporter iPad will access web service in Conmas Gateway to load the data from the Excel check sheet.

ConMas Gateway Settings

There are the following subfolders and files in the folder where you have installed ConMas Gateway. The structure of the configuration file is described below.

Python Code

getdata.py

import sys
import os
import traceback
import logging
import json
import pandas as pd
import numpy as np
from pandas import ExcelFile, Series, DataFrame


logging.basicConfig(filename="./logs/python.log", level=logging.DEBUG, format="%(asctime)s - %(levelname)s - %(message)s")
source_filepath = "C:/ConMas/gateway/ex/Sample2.xlsx"
source_sheetname = "MASTER ASLI"

mappings = {}

def main(args):
  # parse file
  dataFrame = ExcelFile(source_filepath).parse(
    sheet_name = source_sheetname,
    index_col=None,
    header=None
  )
 
  mappings = [
    { 
      "item": "", 
      "sheet": 1, 
      "cluster": 6 ,
      "type": "string",
      "value": str(dataFrame.iloc[12,7])
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 7 ,
      "type": "string",
      "value": str(dataFrame.iloc[12,8])
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 8 ,
      "type": "string",
      "value": str(dataFrame.iloc[12,9])
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 9 ,
      "type": "string",
      "value": str(dataFrame.iloc[12,10])
     },
     #ROW 2
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 10 ,
      "type": "string",
      "value": str(dataFrame.iloc[13,7])
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 11 ,
      "type": "string",
      "value": str(dataFrame.iloc[13,8])
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 12 ,
      "type": "string",
      "value": str(dataFrame.iloc[13,9]
     },
     { 
      "item": "", 
      "sheet": 1, 
      "cluster": 13 ,
      "type": "string",
      "value": str(dataFrame.iloc[13,10])
     },
     
    ]
 
 
  logging.debug(json.dumps(mappings))
  
  return mappings
  
try:
  result_arr = []
  if sys.stdin.isatty():
    mappings = {"error": "", "mappings": main(())}
  else:
    payload = json.loads(sys.stdin.readline())
    logging.debug(payload['data'])
    mappings = {"error": "", "mappings": main(payload['data'])}
except Exception as e:
  logging.error(traceback.format_exc())
  mappings = {"error": str(e), "mappings": ""}
  logging.debug(json.dumps(mappings, indent=2, ensure_ascii=False))
finally:
  print(json.dumps(mappings, ensure_ascii=False))
  #os.remove(source_filepath)

Preparing Sample Definition

Read the sample definition .xml in Designer and prepare it for use, and then start Designer.

Action Cluster Setting

Double Click the button in sample document, and fill the property below.

The Result

Create new document based on available template after MM-400 measuring the part. When document opened,iReporter iPad will access web service in Conmas Gateway to load the data from the Excel check sheet.