Extending Asprova Functionality Through the COM Interface

The production scheduler Asprova exposes a COM interface, allowing functionality to be extended through EXE files or plugins (DLLs). It retrieves project objects deployed in memory from EXE or DLL files, accessing lower-level table objects in Asprova.
The Asprova main body provides Hooks to execute plugins at specific event timings. These access points, represented by plugin keys, correspond to WordPress’s do_action hook or apply_filters hook.

What is a COM Interface?

Asprova has a mechanism to expose internal data and processing via COM (Component Object Model). Functionality can be added through plugins (ActiveX DLL files: function calls from Asprova) or automation clients (Standard EXE files: accessing Asprova from external programs).
DLL files are placed in the same folder as Asprova’s EXE and are automatically loaded by Asprova at startup.
DLLs are registered as internal commands in Asprova, so they can be attached to user-defined menus and called from Asprova. For EXE files, an active project is obtained by specifying and opening a project file name.

DLL Placement Location

DLLs must be placed in the following locations relative to the launching EXE:

Reference Settings in the Development Environment (Using Asprova Libraries from Within Plugins)

Reference settings are stored in the project file (.vbp file) to use the type library of an ActiveX object without a GUI in the project. Plugins (DLLs compiled from project files) access Asprova’s libraries via the COM interface.
With reference settings, object types can be used in Dim variable declarations, and instances can be created with the new operator.

When referencing Asprova libraries from a plugin, register the following two Asprova class libraries in the development environment’s reference settings:

Structure of a Plugin Project File

For plugins, functions called by 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 at startup and implemented in the project object.

For Standard EXE (automation clients), the ASDefault class is not needed.

Plugins are registered by passing four arguments to the AddASPlugIn method of Asprova’s plugin manager (plugin registration function). This essentially says, “I’m in a project called Factory, with a method AddOverTime in the Overtime class, so call it at the KeyHookGeneric plugin key timing.”

Mapping Hooks and Functions to Plugin Keys (Access Points)

Asprova provides hooks to execute plugins at specific events.
These access points, called plugin keys, represent each event. For example, KeyHookGeneric is the “command addition” access point—executing a command triggers the plugin.

Option Explicit
Public Function AutoRegistration(plugInManager As ASPPlugInManager, module As ASPModule) As Boolean
    'Set the comment 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: Plugin entry function name (call name)
    ' 4th Param: Key name representing the context in which the plugin is used

    '(1) Automatically calculate overtime or weekend work and set it in the calendar table
    Set plugIn = plugInManager.AddASPlugIn("Incorporate Overtime into Regular Overtime and Saturday Shifts", "Factory1.OverTime", "AddOverTime", ASPlugInKeyName.KeyHookGeneric)
    'Set comments, etc., for this plugin

    plugIn.Comment = "Distribute overtime into 2 hours of weekday overtime and 2 Saturday shifts."
    'Plugin call order (for cases with multiple plugins for the same plugin key)
    plugIn.Order = 1
    'Return True to indicate successful auto-registration
    AutoRegistration = True
End Function

When Asprova loads plugins at startup, it displays the “entry function display name” (the first argument in the plugin manager call) in the internal command plugins.
Plugins register themselves by calling Asprova’s plugin manager from within and passing four arguments.

Same Concept as COM Port (Communication Port)

By the way, COM in COM ports and COM interfaces are different, but both are standard interfaces for external communication, making them conceptually similar.
Software and hardware have standardized interfaces (ports) for exchanging data with external entities. For instance, POP and SMTP server ports or database connection ports are TCP/IP ports.
Standard PCs feature interfaces like PS/2 ports, USB ports, and LAN ports (Ethernet ports) for connecting peripherals such as keyboards.
In software, OSes provide standardized methods to uniformly handle data transmission with external devices or networks, using ports to identify communication partners.
Windows offers “COM ports” to standardize I/O methods, allowing applications to access modems, printers, or scanners uniformly. Apps only need to know which COM port their device is connected to for I/O handling.
For example, connecting a USB barcode assigns COM13, but since PCs don’t physically have 13 interfaces, COM13 is virtual.
In TCP/IP network communication, “port numbers” from 0 to 65,535 serve as sub-addresses under a device’s or computer’s IP address, enabling multiple services or simultaneous communication with multiple computers. “Port” alone often refers to these TCP/IP port numbers.

Mapping Recordset Field Values to Crystal Report Field Definitions via TTX Files

Export Asprova’s operation table to Access to issue production tags.
Though outdated, Crystal Report 9 has Parameter Fields. I thought VB would pass parameters, but tossing a Recordset auto-fetches field values, displaying reports in the free Crystal Report Viewer ActiveX component.

    CrRep.Database.SetDataSource rs, 3, 1
    CRViewer91.ReportSource = CrRep
    CRViewer91.ViewReport

Here, the third argument, 1, represents the Table Number.

    'Get ORDERCOD from 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

    'Get the recordset for 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, apps using non-standard Win7 components like Crystal Report Viewer can cause issues in XP. Since crviewer9.dll causes frequent problems on older PCs, I downgraded to CR8.5 and adjusted the code as follows.
CR8.5 limits itself to form layout, defining fields in a TTX file. VB throws a recordset, mapping it to the form per the TTX file’s field definitions.

Dim result As String

    'Get ORDERCOD from the selected combobox
    result = Combo1.List(Combo1.ListIndex)

    'Get the recordset for 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

Specifying the TTX file (rpt.ttx) in CR’s Report Expert maps the recordset’s field values to CR file fields automatically.

Defining field names, attributes, and lengths in a tab-delimited TTX file may be a classic approach in Japan, but it’s 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

Add “Microsoft ActiveX Data Objects 2.1 Library” from 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 for using the Jet Database Engine (MDB).
The MDB file in MS-Access format is a Jet Database Engine file (a relational database engine by Microsoft)—a mere data container, separate from the Microsoft Access GUI application.
This means MDB files can be used without MS-Access installed.
On PCs without MS-Access, you can manipulate tables via Excel VBA with ADO or download the Runtime from Microsoft’s site to open it—without violating licensing.
Runtime is “a program stripped of development functions from software with both development and execution capabilities, leaving only execution functions”—an “execution environment without a development environment.”

Notepad is a utility for editing text files, but it’s not the only app that can—just the same logic.
DB (RDB) connections from systems are mostly ADO or ODBC, differing as:

OLE DB includes an ODBC provider for RDB connections, but developers access OLE DB via ADO’s interface.
Going from Cikarang via “Developer ⇒ ODBC ⇒ OLE (RDB, Excel, etc.)” (backroads) takes 3 hours, but “Developer ⇒ ADO ⇒ OLE (RDB, Excel, etc.)” (toll road) takes 1.5 hours—same idea lol.
For generating connection and recordset objects, any method works.
Using CreateObject creates a new object variable, assigning the reference with Set:

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

Using New assigns an object reference with Set and New:

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 with a 64-bit Windows PC, VB6 (development environment) is 32-bit, so use the 32-bit MySQL ODBC Connector.
This doesn’t matter for ADODB connections, but for DSN connections, installing a 32-bit ODBC driver on 64-bit Windows won’t show the MySQL 5.1 driver in the Control Panel’s ODBC settings—panic! Use this command in Command Prompt to open the 32-bit ODBC Data Source Administrator for DSN setup:

For DSN connection:

'Create 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 connection:

'Create 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

Add Microsoft Excel 12.0 Object Library from 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"
'Generate Application
Set xlApp = CreateObject("Excel.Application")
'Open EXCEL
'xlApp.Workbooks.Open FileName:=strFilename, UpdateLinks:=0
'Generate Workbook object
'Set xlBook = xlApp.Workbooks(Dir(strFilename))
Set xlBook = xlApp.Workbooks.Open(strFilename)
'Generate Worksheet object
Set xlSheet = xlBook.Worksheets(strSheetName)
'Get Worksheet data
Dim cnt As Integer
cnt = 1
Do
    'ORDCOD = Worksheets("Delivery Schedule").Cells(cnt, 1).Text works too
    ORDCOD = xlSheet.Cells(cnt, 1).Text
    ORDITM = xlSheet.Cells(cnt, 2).Text
    ORDQTY = xlSheet.Cells(cnt, 3).Text

cnt = cnt + 1

Loop While ORDCOD <> ""

Note: When assigning a file name (full path) to a workbook object from a variable, remove the extension, or it won’t work for some reason. Fixed paths as strings (like above) are fine.
Use InStrRev to find the period and exclude the extension:

strFilename = CurDir & "\data\" & strFilename
'Remove extension (required to avoid errors)
strFilename = Left(strFilename, InStrRev(strFilename, ".") - 1)

Set xlBook = xlApp.Workbooks.Open(strFilename)

Worksheet name retrieval and addition:

'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

Add Microsoft Scripting Runtime from reference settings. FileSystemObject is a component for easily handling the file system in Windows.

'Generate DB connection and recordset objects
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 empty recordset with SELECT
query1 = "SELECT * FROM ORDER_SOURCE"
rs.Open query1, conn, adOpenStatic, adLockOptimistic
'Load CSV file data into the 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

OLE DB-Related Terminology

The relationship between middleware and components for DB access via ADO resembles shipping cargo from a Shipper to a Consignee via sea transport.

To access an RDB, Excel, or text file (Consignee) from a development application (Shipper), you request an OLE DB data provider (Forwarder) to handle it. Direct dealings with OLE DB involve cumbersome paperwork, so ADO (cargo handler) takes over.

Plugin DLLs are developed for adding functions, while extended interface DLLs modify existing logic.
The difference lies in defining plugin keys (e.g., KeyHookGeneric) or interfaces (e.g., IEIIFilterCalc, ReplenishOrderLotSizing) in the AutoRegistration method of the AsDefault class.

Difference Between Plugin Keys and Interfaces

Defining a plugin key (e.g., KeyHookGeneric) in the entry function call within the AutoRegistration method of a plugin’s AsDefault class triggers the entry function when the corresponding event (e.g., added command execution) occurs in Asprova.
Similarly, defining an interface (e.g., IEIIFilterCalc, ReplenishOrderLotSizing) in the entry function call within the AutoRegistration method of an EII (Extension Interface Implementation) triggers it when the corresponding event (e.g., auto-replenishment) occurs.
However, interfaces are customized to have less overhead than plugin keys, making them suitable for frequent logic changes like order explosion, auto-replenishment, or lot sizing.
Plugin DLLs add functions, while extended interface DLLs modify existing logic.

Extended interfaces have less overhead than plugin keys, ideal for adding entry functions called frequently during rescheduling events.

Extended Interface (DLL) to Modify Replenishment Order Lot Sizes

Replenishment order lot sizes are determined by “Max Manufacturing Lot Size,” “Min Manufacturing Lot Size,” and “Unit Manufacturing Lot Size.” FilterCalcReplenishOrderLotSizing is called during linkage in the “Order Explosion” command, retrieving three objects to modify (override) replenishment lot sizes and passing results to ArgList:

Lot Sizes

Order Explosion Process

Example of Batching Preceding Processes for 7 Manufacturing Order Completions

To access Asprova’s tables, you must go through the project object deployed in memory. The method to obtain this differs between DLLs and EXEs. For DLLs, load a list of objects related to the plugin key (ASPArgList) from the args argument to get the project object. For EXEs, load a specific project file into a document object to retrieve the active project.

For Extended Plugins (DLLs)

'(Definition and retrieval of objects within ASPArgList)
'------------------------------------------------------------------
Public Function AddPersonPlan(args As ASPArgList) As TReturnType
'Access APS table objects via the project object
'Obtain the project object from the args interface
'Assign the project object from ASPArgList to an ASBProject-type variable to receive arguments

Dim project As aslib.ASBProject
Set project = args.ArgAsObject(kArgProject)

'Assign the RootObject from the project object to an ASORootObject-type variable
Dim root As aslib.ASORootObject
Set root = project.RootObject

'Assign the RootCalendar object from the project object to an ASBCalendar-type variable
Dim calendarRoot As aslib.ASBCalendar
Set calendarRoot = project.RootCalendar

'Assign the RootResource object from the project object to an ASBResource-type variable
Dim resourceRoot As aslib.ASBResource
Set resourceRoot = project.RootResource

'Assign the orderRoot object from the project object to an ASBOrder-type variable
Dim orderRoot As aslib.ASBOrder
Set orderRoot = project.orderRoot

'Assign the itemRoot object from the project object to an ASBItem-type variable
Dim itemRoot As aslib.ASBItem
Set itemRoot = project.itemRoot

'Define a variable for storing property IDs
Dim propID1 As Long

'(Accessing retrieved objects)
'------------------------------------------------------------------
    'Number of children (records) in the calendar object
    calendarCount1 = calendarRoot.ChildCount

'Get the property ID for resource code using the root object
propID1 = root.LookupPropID("Cal_Resource")

'Loop until records end
'ChildAsCalendar argument starts from 1 (0 represents the last data)
For i = 1 To calendarCount1
    'Access the record
    Set calendar = calendarRoot.ChildAsCalendar(i)

    'Access property values with GetAsStr method. Set the second argument to 1 if the property isn’t an array value
    res1 = calendar.GetAsStr(propID1, 1)
Next

End Function

For Standard EXE

Unlike plugins or extended interfaces, EXE programs outside Asprova must retrieve Asprova objects. Load a specific project file into a document object and get the active project from it.

Private Sub Command1_Click()
'■■■Asprova-related objects■■■
'Define Asprova project object
Dim m_pProject As ASBProject
Dim m_pDoc As ASFDocument

'Get Asprova application
Dim app As ASFApplication
Set app = New ASFApplication

'Open Asprova document by specifying the name
Set m_pDoc = app.Deserialize(CurDir & "\data.aru")

'Get the active project from the document
Set m_pProject = m_pDoc.ActiveProject

'■■■Matching by end-order items from the operation table■■■
'Get operation object
Dim operation As ASBOperation
Dim operationList As ASOObjectList
Set operationList = m_pProject.GetOperationList(False)

'Get root object
Dim root As ASORootObject
Set root = m_pProject.RootObject

'Get propID (from root object)
propID1 = root.LookupPropID("Work_OperationOutMainItemQty")

'Get all project operations and match once
operationList.FilterByExprStr ("WorkUser_MostRightOrderItem=='" & Left(Combo1.Text, 7) & "')

'Sort by production start time in ascending order
operationList.SortByExprStr ("Work_OperationProductionStartTime,a")

Do
    'Get list data
    Set operation = operationList.Object(i)
    'Get value
    PrdQty = operation.GetAsStr(propID1, 1)
    'Save value
    operation.SetAsStr propID1, 1, Text1.Text

    i = i + 1

Loop While i <= operationList.ObjectCount

'■■■Check resource table data one-by-one■■■
'Define resource object
Dim RootResource As AsLib.ASBResource
Dim resource As AsLib.ASBResource

'Get resource root object
Set RootResource = m_pProject.RootResource

'Get 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 in the item table■■■
'Define item object
Dim RootItem As ASBItem
Dim Item As ASBItem

'Get item root object
Set RootItem = m_pProject.RootItem

'Get item object
Set Item = RootItem.FindChild(Left(Combo1.Text, 7))

'Number of matched records in item table
ItemCount=Item.ChildCount

End Sub