Details of Asprova's COM Interface and Plugin Extensions

The production scheduler Asprova provides a COM interface, allowing functionality to be extended with EXE files and plugins (DLL). This enables the retrieval of project objects deployed in memory and access to Asprova's lower-level table objects.

The Asprova main body is equipped with hooks to execute plugins at the timing of event occurrences. The plugin key, which serves as an access point representing this event, corresponds to the do_action or apply_filters hooks in WordPress.

What is the COM Interface

Asprova utilizes COM (Component Object Model) to expose its internal data and processing content. This allows for the addition of functions from plugins (ActiveX DLL files) and automation clients (Standard EXE files). Plugins operate by calling functions from Asprova, while automation clients access Asprova from external programs.

DLL files are placed in the same folder as Asprova's EXE and are automatically loaded at startup. DLLs are registered as internal commands of Asprova and can be incorporated into user-defined menus, allowing them to be called from Asprova. On the other hand, for EXE files, the active project can be obtained by specifying and opening the project file name.

DLL Placement Location

DLLs need to be placed in a specific location relative to the EXE that launches them. This ensures that the EXE correctly recognizes the DLL and can execute its functions properly. Below are the specific steps for placing DLLs.

Case: If a specific application cannot find the DLL, it may be due to insufficient settings in the Path environment variable. Proper configuration can resolve this issue.

Takeaway: Correctly setting the DLL placement location allows the EXE to smoothly recognize and operate with the DLL.

Reference Settings for Development Environment (Using Asprova Library from Within a Plugin)

Reference settings are saved within the project file (.vbp file) to utilize the type library of an ActiveX object without a GUI in the project. This allows access to Asprova's library through the COM interface from the plugin (DLL compiled from the project file).

By setting references, you can use the type of that object when declaring Dim variables or create instances with the new operator.

When referencing Asprova's library from a plugin, register the following two Asprova class libraries from the development environment's reference settings.

Structure of Plugin Project Files

For plugins, functions called from Asprova are registered in the AutoRegistration method of the ASDefault class. If registered as a plugin in Asprova's "Plugin Information," the AutoRegistration function is automatically called upon Asprova startup and implemented within the project object.

Plugins are registered by passing four arguments to the AddASPlugIn method of Asprova's Plugin Manager (plugin registration function). This indicates, "I am a project called Factory, with a method called AddOverTime in the Overtime class (entry function), so please call it at the timing of the plugin key KeyHookGeneric."

Mapping of Hooks and Functions Corresponding to Plugin Keys (Access Points)

Asprova provides hooks to execute plugins for specific events. These hooks function as access points representing each event and are called plugin keys. For example, KeyHookGeneric indicates "Add Command," and executes the plugin when the added command is triggered.

Option Explicit

Public Function AutoRegistration(plugInManager As ASPPlugInManager, module As ASPModule) As Boolean

    'Set comments for this module
    module.Comment = "APS Extended Functionality PlugIns (VB)"

    'Define a variable to hold the plugin object
    Dim plugIn As ASPPlugIn

    ' ---------------------------------------------------------------------------------------------

    'Call the plugin manager to register a new plugin. The method name is AddASPlugIn

    ' 1st Param: Plugin display name
    ' 2nd Param: ProgID representing the module and class where the plugin is implemented
    ' 3rd Param: Entry function name (call name) of the plugin
    ' 4th Param: Key name representing the context in which the plugin is used

    '(1) Automatically calculate overtime and weekend work and set it in the calendar table
    Set plugIn = plugInManager.AddASPlugIn("Incorporate Overtime into Weekend and Saturday Work", "Factory1.OverTime", "AddOverTime", ASPlugInKeyName.KeyHookGeneric)

    'Set comments, etc., for this plugin.

    plugIn.Comment = "Allocate overtime to 2 hours of weekday overtime and 2 shifts of Saturday work."

    'Plugin call order (in case there are multiple plugins with the same plugin key)
    plugIn.Order = 1

    'Return True indicating that auto-registration was successful.
    AutoRegistration = True

End Function

Asprova automatically loads plugins at startup and displays the "display name of the entry function" in the internal command plugins. By calling Asprova's plugin manager from within the plugin and passing four arguments, the plugin is registered.

Same Concept as COM Port (communication port)

Although COM in COM port and COM interface are different, they share the commonality of being standard specifications for external interfaces. Both software and hardware have standardized interfaces (ports) for inputting and outputting data externally. For example, the ports for POP and SMTP servers or the ports for DB connections are TCP/IP ports.

Standard PCs are equipped with PS/2 ports or USB ports for connecting peripherals like keyboards, as well as LAN ports (Ethernet ports).

In software, methods for standard handling of data transmission and reception with external devices or communication networks are provided by the OS, and ports are used to identify communication partners.

In Windows, a unified input/output method called "COM port" is provided so that application software can uniformly access modems, printers, image scanners, etc. Application software can handle input and output simply by knowing which COM port the target device is connected to.

For example, when connecting a USB barcode, COM13 is assigned, but since there are not physically 13 interfaces on the PC, COM13 is virtual. In network communication using TCP/IP, "port numbers" from 0 to 65535 are used as auxiliary addresses under the IP address of communication devices or computers.

This allows a single computer to provide multiple services or communicate with multiple computers simultaneously. When simply referring to a "port," it often means a TCP/IP port number.

Mapping Record Set Field Values to Crystal Report Field Definitions via ttx File

Export Asprova's work table to Access to issue a work ticket. Crystal report 9 has a Parameter Field, allowing Passing Parameter from VB. However, when passing a Record Set, it automatically retrieves values from the fields and displays the report in a free ActiveX component viewer called Crystal Report Viewer.

    CrRep.Database.SetDataSource rs, 3, 1

    CRViewer91.ReportSource = CrRep

    CRViewer91.ViewReport

The third argument 1 in this code represents the Table Number.

    'Retrieve ORDERCOD of the selected combo

    result = Combo1.List(Combo1.ListIndex)


    'Passing parameter from VB6 to CR using CRAXDRT

    Dim CrApp As CRAXDRT.Application

    Dim CrRep As CRAXDRT.Report


    'Retrieve record set of ORDERCOD from ORDERTBL

    Dim conn As New ADODB.Connection

    Dim rs As ADODB.Recordset


    Dim mySQL As String


    cn.Open _

        "Provider = Microsoft.Jet.OLEDB.4.0; " & _

            "Data Source = " & CurDir & "\NNA.mdb"


    Set CrApp = New CRAXDRT.Application

    Set CrRep = CrApp.OpenReport(App.Path & "\reports\TravelSheet.rpt", & _

                    crOpenReportByTempCopy)


    CrRep.DiscardSavedData


    If result <> "ALL" Then

        mySQL = "SELECT * FROM OPERATIONTBL WHERE ORDERCOD='" & _

            Combo1.Text & "' ORDER BY ORDERCOD"

    Else

        mySQL = "SELECT * FROM OPERATIONTBL ORDER BY ORDERCOD"


    End If

    With rs

        .ActiveConnection = cn

        .CursorLocation = adUseServer

        .CursorType = adOpenKeyset

        .LockType = adLockOptimistic

        .Properties("IRowsetIdentity") = True


        .Open mySQL

    End With

    CrRep.Database.SetDataSource rs, 3, 1

    CRViewer91.ReportSource = CrRep

    CRViewer91.ViewReport

However, development apps using non-standard components like Crystal Report Viewer on Win7 are likely to cause issues on XP, and crviewer9.dll may cause problems on older PCs. Therefore, downgrade to CR8.5 and modify the code as follows.

Dim result As String


    'Retrieve ORDERCOD of the selected combo box

    result = Combo1.List(Combo1.ListIndex)


    'Retrieve record set of ORDERCOD from ORDERTBL

    Dim conn As New ADODB.Connection

    Dim rs As ADODB.Recordset


    Dim mySQL As String


    cn.Open _

        "Provider = Microsoft.Jet.OLEDB.4.0; " & _

            "Data Source = " & CurDir & "\DNN.mdb"


    If result <> "ALL" Then

        mySQL = "SELECT * FROM OPERATIONTBL WHERE ORDERCOD='" & _

                    Combo1.Text & "' ORDER BY ORDERCOD"


    Else

        mySQL = "SELECT * FROM OPERATIONTBL ORDER BY ORDERCOD"


    End If


    rs.Open mySQL, cn, adOpenKeyset, adLockOptimistic


    With rptControl

        .WindowTitle = "Travel Sheet"

        .ReportFileName = App.Path & "\Reports\TravelSheet.rpt"


        .SetTablePrivateData 0, 3, rs

        .WindowShowPrintBtn = True

        .WindowShowPrintSetupBtn = True


        .RetrieveDataFiles

        .WindowState = crptMaximized

        .Destination = crptToWindow

        .Action = 1

    End With

By specifying a ttx file (rpt.ttx) in the Report Expert of the CR file, the field values of the record set in the code are automatically mapped to the fields of the CR file.

Defining field names, attributes, and lengths in a tab-delimited ttx file is a classic method in Japan but still active in Indonesia.

ORDERCOD	string	100

PROCNO	number

RESOURCE	string	100

ITEM	string	100

QTY	number

STARTDATE	string	100

ENDDATE	string	100

WORKTIME	string	100

PARENTITEM	string	100

CLASS	number

Accessing Jet Database Engine from VB

To access the Jet Database Engine from VB, first add the "Microsoft ActiveX Data Objects 2.1 Library" in the reference settings.

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Users\Toshiba\Desktop\ERP\VB\LinkWithERP.mdb"

'DELETE
Dim query1 As String
query1 = "DELETE * FROM ORDER_SOURCE"
rs.Open query1, cn, adOpenKeyset, adLockOptimistic

'SELECT
Dim query2 As String
query2 = "SELECT * FROM ORDER_SOURCE"
rs.Open query2, cn, adOpenKeyset, adLockOptimistic

Do Until rs.EOF
    X1 = rs!Order_Code
    X2 = rs!Order_Item
    X3 = rs!Order_Qty

    rs.MoveNext
Loop

ADO and ODBC are middleware used to utilize the Jet Database Engine (MDB). The MS-Access format MDB file is a file of the relational database engine developed by Microsoft, known as the Jet Database Engine, and serves as a container for storing data.

This is a separate file independent of the Microsoft Access application and can be used even in environments where MS-Access is not installed.

Even on a PC without MS-Access installed, it is possible to manipulate tables using ADO in Excel VBA, and you can download and open the Runtime from Microsoft's site. It does not violate license restrictions. Runtime is a program that extracts only the execution function, omitting the application development function.

Just as Notepad is not the only application to edit text files, connecting to a DB (RDB) from a system is generally done via either ADO connection or ODBC connection. The differences between the two are as follows:

Within OLEDB, there is an ODBC provider that connects to RDB, but developers can access OLEDB through the ADO interface. The method for generating connection objects and recordset objects involves using the CreateObject function to generate a new object variable and assigning a reference to the object to the object variable with Set.

Dim conn As Variant
Dim rs As Variant
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Assigning object references using New involves creating a new object with New and assigning the reference of the object to the object variable with Set.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

Accessing MySQL from VB

Even on a Windows 64-bit PC, since the development environment VB6 operates in 32-bit, you need to use the 32-bit version of the MySQL ODBC Connector.

There is no issue with ADODB connections, but when performing a DSN connection, if you install the 32-bit version of the ODBC driver on a 64-bit version of Windows, the installed MySQL 5.1 driver may not appear when you launch the ODBC settings screen from the control panel.

In this case, execute the following command from the command prompt to display the 32-bit ODBC Data Source Administrator screen and configure the DSN settings.

For DSN connections, use the following code to generate a connection object.

'Generate connection object for MySQL 5.1 driver DSN

Dim conn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim dsn As String
dsn = "dsn=xxx;uid=yyy;pwd=zzz"
conn.Open dsn
conn.CursorLocation = 3

For ADODB connections, use the following code to generate a connection object.

'Generate connection object for MySQL 5.1 driver ADO

Dim conn As New ADODB.Connection

Dim rs As ADODB.Recordset

Dim strConn, xServer, xDatabase, xUID, xPass As String

If conn.State = 0 Then

    xServer = "localhost"

    xDatabase = "xxx"

    xUID = "yyy"

    xPass = "zzz"

    strConn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & xServer & _
               ";PORT=3306" & _
               ";DATABASE=" & xDatabase & _
               ";USER=" & xUID & _
               ";PASSWORD=" & xPass & _
               ";OPTION=3;"
    conn.Open strConn

End If

'DELETE

Dim query1 As String

query1 = "DELETE * FROM ORDER_SOURCE"

rs.Open query1, strConn

'SELECT

Dim query2 As String

query2 = "SELECT * FROM ORDER_SOURCE"

rs.Open query2, strConn

Do Until rs.EOF

    X1 = rs!Order_Code

    X2 = rs!Order_Item

    X3 = rs!Order_Qty

    rs.MoveNext

Loop

Accessing Excel from VB

To access Excel from VB, first add the Microsoft Excel 12.0 Object Library from the reference settings.

'Excel related objects

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet



'File name (full path)

Dim strFilename As String

strFilename = "C:\SO\Delivery Schedule.xls"



'Sheet name

Dim strSheetName As String

strSheetName = "Delivery Schedule"



'Create Application

Set xlApp = CreateObject("Excel.Application")



'Open EXCEL

'xlApp.Workbooks.Open FileName:=strFilename, UpdateLinks:=0



'Create Workbook object

'Set xlBook = xlApp.Workbooks(Dir(strFilename))

Set xlBook = xlApp.Workbooks.Open(strFilename)



'Create Worksheet object

Set xlSheet = xlBook.Worksheets(strSheetName)



'Get Worksheet data

Dim cnt As Integer

cnt = 1



Do

    'ORDCOD can also be retrieved with Worksheets("Delivery Schedule").Cells(cnt, 1).Text

    ORDCOD = xlSheet.Cells(cnt, 1).Text

    ORDITM = xlSheet.Cells(cnt, 2).Text

    ORDQTY = xlSheet.Cells(cnt, 3).Text



    cnt = cnt + 1

Loop While ORDCOD <> ""

When creating a book object, if you assign the file name from a variable, you must remove the extension to retrieve it. However, if you assign a fixed path as a string, there is no problem. Use InStrRev to get the position of the period and exclude the extension.

strFilename = CurDir & "\data\" & strFilename

'Remove the extension (if not done, it will cause an error)

strFilename = Left(strFilename, InStrRev(strFilename, ".") - 1)



Set xlBook = xlApp.Workbooks.Open(strFilename)

The retrieval and addition of worksheet names are as follows.

'Retrieve worksheet names

j = 0

For Each ws In xlBook.Worksheets

    sheetArry(j) = ws.Name

    j = j + 1

Next



'Add worksheet 6

Set xlSheet = xlBook.Worksheets.Add  'Add worksheet

xlSheet.Name = "New sheet"

Accessing Text Files (CSV) from VB

To access CSV files in VB, first add Microsoft Scripting Runtime from the reference settings. FileSystemObject is a component that makes it easy to handle the file system in Windows.

'Generate DB connection object and recordset object

Dim conn As New ADODB.Connection

Dim rs As New ADODB.Recordset



'Generate component object for handling CSV files

Dim objFSO As New Scripting.FileSystemObject



'Connect to DB

conn.Open _

    "Provider = Microsoft.Jet.OLEDB.4.0; " & _

    "Data Source = " & CurDir & "\SO.mdb"



'Generate recordset with SELECT (empty)

query1 = "SELECT * FROM ORDER_SOURCE"

rs.Open query1, conn, adOpenStatic, adLockOptimistic



'Load data from CSV file into object

Set objFile = objFSO.OpenTextFile("C:\APS_ERP\VB\" & CSVNAME)



'Read line by line and store in recordset

cnt = 0

Do Until objFile.AtEndOfStream

    strMastflow = objFile.ReadLine

    arrMastflow = Split(strMastflow, ",")



    rs.AddNew

    rs("Order_Code") = arrMastflow(0)

    rs("Order_Item") = arrMastflow(1)

    rs("Order_Qty") = arrMastflow(2)

    rs.Update



    cnt = cnt + 1

Loop

Case: By using this method, it is possible to directly read CSV files from VB and store them in the database. In particular, using ADODB.Connection and ADODB.Recordset allows for smooth integration with the database.

Takeaway: When handling CSV files in VB, it is important to utilize FileSystemObject and efficiently integrate with the database.

OLE DB Related Terms

The relationship between middleware and components until accessing the database through ADO is similar to the process of shipping cargo from a Shipper to a Consignee by sea.

To access RDB, Excel, or Texfile (Consignee) from a development application program (Shipper), you request procedures from an intermediary (Forwarder) called the OLE DB data provider. However, since directly dealing with OLE DB is cumbersome, it is entrusted to ADO (Cargo Handler).

Plugin DLLs are developed for adding functions, while extended interface DLLs are for modifying existing logic. The difference lies in whether you define a plugin key (KeyHookGeneric) in the AutoRegistration method of the AsDefault class or define an interface (IEIIFilterCalc ReplenishOrderLotSizing).

Differences Between Plugin Keys and Interfaces

When using plugins in Asprova, if you define a plugin key (KeyHookGeneric) within the AutoRegistration method of the AsDefault class, the entry function is called and executed when the corresponding event occurs.

Similarly, if you define an interface (IEIIFilterCalc ReplenishOrderLotSizing) within the AutoRegistration method of the AsDefault class for EII (Extension Interface Implementation), the entry function is called when the corresponding event occurs.

Interfaces are customized to have less overhead in calling than plugin keys, making them suitable for changes in logic that are frequently called, such as order expansion, automatic replenishment, and lot sizing. Plugin DLLs are developed to add functions, while extension interface DLLs are developed to modify existing logic.

Using an extension interface is more suitable than using a plugin key when you want to add an entry function called from events that frequently occur during rescheduling, due to the lower overhead in calling.

Extended Interface (DLL) for Changing Replenishment Order Lot Size

The lot size of a replenishment order is determined by "Manufacturing Lot Size MAX", "Manufacturing Lot Size MIN", and "Manufacturing Lot Size UNIT". However, FilterCalcReplenishOrderLotSizing is called during the linking in the "Order Expansion" command, retrieves the following three objects to change (overwrite) the lot size of the replenishment order, and passes the result to ArgList.

Lot Size

Order Expansion Process

Example of Consolidating Pre-Processes in 7 Manufacturing Order Completions

To access each table in Asprova, it is necessary to go through the Asprova project object deployed in memory. The method of obtaining this project object differs between DLL and EXE. In the case of DLL, a list of objects related to the plugin key ASPArgList is loaded into the argument args, and the project object is obtained from here, whereas in the case of EXE, the active project is obtained from the document object that has loaded the specified project file.

In the Case of an Extension Plugin (DLL)

' (Definition and retrieval of objects within ASPArgList)

'------------------------------------------------------------------

Public Function AddPersonPlan(args As ASPArgList) As TReturnType



    ' Access objects of each APS table from the project object.

    ' Retrieve the project object from the args interface.

    ' Receive arguments by assigning the project object from the ASPAgrList object to a variable of type ASBProject.



    Dim project As aslib.ASBProject

    Set project = args.ArgAsObject(kArgProject)



    ' Assign the RootObject from the project object to a variable of type ASORootObject.

    Dim root As aslib.ASORootObject

    Set root = project.RootObject



    ' Assign the RootCalendar object from the project object to a variable of type ASBCalendar.

    Dim calendarRoot As aslib.ASBCalendar

    Set calendarRoot = project.RootCalendar



    ' Assign the RootResource object from the project object to a variable of type ASBResource.

    Dim resourceRoot As aslib.ASBResource

    Set resourceRoot = project.RootResource



    ' Assign the orderRoot object from the project object to a variable of type ASBOrder.

    Dim orderRoot As aslib.ASBOrder

    Set orderRoot = project.orderRoot



    ' Assign the itemRoot object from the project object to a variable of type ASBItem.

    Dim itemRoot As aslib.ASBItem

    Set itemRoot = project.itemRoot



    ' Define a variable for storing the property ID.

    Dim propID1 As Long



' (Access to the retrieved objects)

'------------------------------------------------------------------

    ' Number of children (records) in the calendar object.

    calendarCount1 = calendarRoot.ChildCount



    ' Use the root object to retrieve the property ID of the resource code.

    propID1 = root.LookupPropID("Cal_Resource")



    ' Loop until the records end.

    ' The argument for ChildAsCalendar starts from 1 (0 represents the last data).

    For i = 1 To calendarCount1

        ' Access the records.

        Set calendar = calendarRoot.ChildAsCalendar(i)



        ' Access the property value using the GetAsStr method. If the property is not an array value, set the second argument to 1.

        res1 = calendar.GetAsStr(propID1, 1)

    Next



End Function

In the Case of Standard EXE

When obtaining Asprova objects from an external EXE program, you need to load the project file into the document object and then retrieve the active project from there. The following steps illustrate this process.

Private Sub Command1_Click()

    '■■■Asprova Related Objects■■■

    'Define Asprova Project Object

    Dim m_pProject As ASBProject

    Dim m_pDoc As ASFDocument

    'Obtain Asprova Application

    Dim app As ASFApplication

    Set app = New ASFApplication

    'Open Asprova Document by Specifying Name

    Set m_pDoc = app.Deserialize(CurDir & "\data.aru")

    'Retrieve Active Project from Document

    Set m_pProject = m_pDoc.ActiveProject

    '■■■Matching with End Order Items from Work Table■■■

    'Obtain Work Object

    Dim operation As ASBOperation

    Dim operationList As ASOObjectList

    Set operationList = m_pProject.GetOperationList(False)

    'Obtain Root Object

    Dim root As ASORootObject

    Set root = m_pProject.RootObject

    'Obtain propID (from root object)

    propID1 = root.LookupPropID("Work_OperationOutMainItemQty")

    'Retrieve All Project Work for One-Time Matching

    operationList.FilterByExprStr ("WorkUser_MostRightOrderItem=='" & Left(Combo1.Text, 7) & "')

    'Sort in Ascending Order of Production Start Date

    operationList.SortByExprStr ("Work_OperationProductionStartTime,a")

    Do

        'Retrieve Data from List

        Set operation = operationList.Object(i)

        'Obtain Value

        PrdQty = operation.GetAsStr(propID1, 1)

        'Save Value

        operation.SetAsStr propID1, 1, Text1.Text

        i = i + 1

    Loop While i <= operationList.ObjectCount

    '■■■Check Each Record in Resource Table■■■

    'Define Resource Object

    Dim RootResource As AsLib.ASBResource

    Dim resource As AsLib.ASBResource

    'Obtain Resource Root Object

    Set RootResource = m_pProject.RootResource

    'Obtain propID (from root object)

    propID2 = root.LookupPropID("Res_Code")

    'Number of Records in Resource Table

    ResourceCount = RootResource.ChildCount

    For i = 1 To ResourceCount

        Set resource = RootResource.ChildAsResource(i)

        ResCode = resource.GetAsStr(propID2, 1)

    Next i

    '■■■Matching by Item Code from Item Table■■■

    'Define Item Object

    Dim RootItem As ASBItem

    Dim Item As ASBItem

    'Obtain Item Root Object

    Set RootItem = m_pProject.RootItem

    'Obtain Item Object

    Set Item = RootItem.FindChild(Left(Combo1.Text, 7))

    'Number of Matched Records in Item Table

    ItemCount=Item.ChildCount
End Sub

Frequently Asked Questions | Asprova's COM Interface

This article organizes frequently asked questions concisely according to its content.

What is Asprova's COM Interface?

Asprova's COM Interface is a mechanism that exposes internal data and processing content. This allows for the addition of functions from plugins and automation clients.

How are Asprova plugins registered?

Asprova plugins are registered using the AddASPlugIn method of the Plugin Manager. By passing four arguments to this method, the plugin is set to execute at specific events.

Where should DLL files be placed?

DLL files need to be placed in the same folder as Asprova's EXE. This ensures that the EXE correctly recognizes the DLL and executes functions properly.