Customer Payment

Customer Payment's Database

UI Name

Database

Type

Remarks

Date

pm.DOCDATE

Date

E.g.: 2024-11-15

O/R No

pm.DOCNO

String

E.g.: OR12345

Agent

pm.AGENT

String

E.g.: AGT001

Area

pm.AREA

String

E.g.: AREA01

Paid Amount

pm.DOCAMT

Number

E.g.: 1500.00

Customer Code

pm.CODE

String

E.g.: CUST001

Project

pm.PROJECT

String

E.g.: PROJ001

Cancelled

pm.CANCELLED

Boolean

E.g.: true

Non-Refundable

pm.NONREFUNDABLE

Boolean

E.g.: true

Paid By

ac.COMPANYNAME

String

E.g.: Company Name

Received In

ba.DESCRIPTION

String

E.g.: Bank Account

Bank Charge

pm.BANKCHARGE

Number

E.g.: 50.00

Cheque No

pm.CHEQUENUMBER

String

E.g.: CHQ123456

Description

pm.DESCRIPTION

String

E.g.: Payment for invoice

Type

ko.TODOCTYPE

String

E.g.: Invoice

Date (Details)

iv.DOCDATE

Date

E.g.: 2024-11-16

Doc No

iv.DOCNO

String

E.g.: DOC12345

Description (Details)

iv.DESCRIPTION

String

E.g.: Payment applied to invoice

Amount

iv.DOCAMT

Number

E.g.: 1500.00

Pay

ko.KOAMT

Number

E.g.: 1500.00

Sync From

'ABC COMPANY'

String

E.g.: ABC COMPANY

Last Modified

a.DOCDATETIME

Timestamp

E.g.: 2024-11-15 07:48:46.563

Customer Payment's Sample Code

Remember to change to the correct field before proceeding to run ANY script.

This is a sample SQL, may not be as same as your database.

WITH AuditData AS (
    SELECT 
        SUBSTRING(a.REF FROM POSITION(', ' IN a.REF) + 2) AS AUDITKEY,
        a.REF,
        MAX(a.DOCDATETIME) AS DOCDATETIME
    FROM AUDIT a
    WHERE a.REF LIKE 'AR_PM, %'
    GROUP BY a.REF
)
SELECT FIRST 3
    pm.DOCDATE docDate,
    pm.DOCNO orNo,
    pm.AGENT agentCode,
    pm.AREA areaCode,
    pm.DOCAMT paidAmount,
    pm.CODE customerCode,
    pm.PROJECT projectCode,
    pm.CANCELLED isCancelled,
    pm.NONREFUNDABLE isNonRefundable,
    ac.COMPANYNAME paidBy,
    ba.DESCRIPTION receivedIn,
    pm.BANKCHARGE bankCharge,
    pm.CHEQUENUMBER chequeNo,
    pm.DESCRIPTION pmDescription,
    ko.TODOCTYPE documentType,
    iv.DOCDATE knockOffDate,
    iv.DOCNO documentNo,
    iv.DESCRIPTION knockOffDescription,
    iv.DOCAMT amount,
    ko.KOAMT payAmount,
    '{YOUR_COMPANY}' syncFrom,
    a.DOCDATETIME lastModified
FROM AuditData a
JOIN AR_PM pm ON a.AUDITKEY = pm.DOCKEY
JOIN AR_CUSTOMER ac ON pm.CODE = ac.CODE
JOIN GL_ACC ba ON pm.PAYMENTMETHOD = ba.CODE
JOIN AR_KNOCKOFF ko ON pm.DOCKEY = ko.FROMDOCKEY AND ko.FROMDOCTYPE = 'PM'
JOIN AR_IV iv ON ko.TODOCKEY = iv.DOCKEY
WHERE a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}'

In order to sync, you must include LastModified in SELECT

"SQLSync": [
  {
    "name": "Customer Payment",
    "enabled": true,
    "server": {
      "db": "{DATABASE_FILENAME}",
      "dcf": "C:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF",
      "username": "ADMIN",
      "password": "ADMIN"
    },
    "from": "sqlaccounting",
    "hook": "https://api.inistate.com/api/automationHook/hook/32039-SyncCustomerPayment",
    "sql": "WITH AuditData AS (\nSELECT SUBSTRING(a.REF FROM POSITION(', ' IN a.REF) + 2) AS AUDITKEY, a.REF, MAX(a.DOCDATETIME) AS DOCDATETIME \nFROM AUDIT a \nWHERE a.REF LIKE 'AR_PM, %' \nGROUP BY a.REF) \nSELECT pm.DOCDATE docDate, pm.DOCNO orNo, pm.AGENT agentCode, pm.AREA areaCode, pm.DOCAMT paidAmount, pm.CODE customerCode, pm.PROJECT project, pm.CANCELLED isCancelled, pm.NONREFUNDABLE isNonRefundable, ac.COMPANYNAME paidBy, ba.DESCRIPTION receivedIn, pm.BANKCHARGE bankCharge, pm.CHEQUENUMBER chequeNo, pm.DESCRIPTION pmDescription, ko.TODOCTYPE documentType, iv.DOCDATE knockOffDate, iv.DOCNO documentNo, iv.DESCRIPTION knockOffDescription, iv.DOCAMT amount, ko.KOAMT payAmount, '{YOUR_COMPANY}' syncFrom, a.DOCDATETIME lastModified \nFROM AuditData a \nJOIN AR_PM pm ON a.AUDITKEY = pm.DOCKEY \nJOIN AR_CUSTOMER ac ON pm.CODE = ac.CODE \nJOIN GL_ACC ba ON pm.PAYMENTMETHOD = ba.CODE \nJOIN AR_KNOCKOFF ko ON pm.DOCKEY = ko.FROMDOCKEY AND ko.FROMDOCTYPE = 'PM' \nJOIN AR_IV iv ON ko.TODOCKEY = iv.DOCKEY \nWHERE a.DOCDATETIME > @date",
    "lastSync": "2025-01-01T00:00:00.000"
  }
]

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

#debug('test', inputs)
moduleName = "Customer Payment"
inistateKeyField = "DocumentId"
keyField = 'ORNO'
modifiedDateField = 'LastModified'
fields = [['DocumentId', 'Doc'],['LastModified', 'LASTMODIFIED']]
inistateFields = []
tableField = 'Items'
tableColumns = ["DOCUMENTTYPE", "KNOCKOFFDATE", "DOCUMENTNO", "KNOCKOFFDESCRIPTION", "AMOUNT", "PAYAMOUNT"]

def getFlatten(list, table):
    details = [dict(Line=y, Document=x) for x in list for y in x[table]]
    return details

def createLine(x):
    item = JObject()
    main = JObject.FromObject(x[0])
    
    for field in main:
        if not tableColumns.Contains(field.Name):
            item[field.Name] = main[field.Name]
        
    item['items'] = JArray()
    for itemRow in x:
        rowJSON = JObject()
        for field in tableColumns:
            rowJSON[field] = itemRow[field]
        item['items'].Add(rowJSON)
    return item

result = list(inputs["list"]).GroupBy(lambda x: x[keyField].ToString()).Select(lambda x: createLine(x)).ToList()

for item in result:
    entry = Module(moduleName).find({ 'Dynamic.Extension.{fieldName}': item["ORNO"] })
    
    form = Module(moduleName).execute("sync", entry)
    form['Transaction Date'] = item["DOCDATE"]
    form['O/R No'] = item["ORNO"]
    form['Agent'] = item["AGENTCODE"]
    form['Area'] = item["AREACODE"]
    form['Paid Amount (RM)'] = item["PAIDAMOUNT"]
    form['Customer Code'] = item["CUSTOMERCODE"]
    form['Project'] = item["PROJECT"]
    if item["ISCANCELLED"] == 'T':
        form['Cancelled'] = True
    else:
        form['Cancelled'] = False
    form['Non-Refundable'] = item["ISNONREFUNDABLE"]
    form['Paid By'] = item["PAIDBY"]
    form['Received In'] = item["RECEIVEDIN"]
    form['Bank Charge'] = item["BANKCHARGE"]
    form['Cheque No'] = item["CHEQUENO"]
    form['Description'] = item["PMDESCRIPTION"]
    form['Sync From'] = item["SYNCFROM"]
    form['Sync Datetime'] = DateTime.Now
    
    # handle table
    lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['SEQ'])).ToList()
    
    while len(form['Details']) > len(lines) :
        last = form['Details'].Last()
        formLine = form.remove('Details', last)

    for line in lines:
        # create if none
        if len(form['Details']) <= lines.IndexOf(line):
            formLine = form.add('Details')
        else :
            formLine = form['Details'][lines.IndexOf(line)]
            
        # assign value to table row
        formLine['Type'] = line["DOCUMENTTYPE"]
        formLine['Date'] = line["KNOCKOFFDATE"]
        formLine['Doc No'] = line["DOCUMENTNO"]
        formLine['Description'] = line["KNOCKOFFDESCRIPTION"]
        formLine['Amount'] = line["AMOUNT"]
        formLine['Pay'] = line["PAYAMOUNT"]
        
    debug('form', form)
    Module(moduleName).submit(entry, form)
    
debug('result', result)

PreviousCustomer Invoice NextE-Invoice

Last updated 1 month ago