Customer Payment

Customer Payment's Database

UI Name	Database	Type	Remarks
Date	docDate	Date	E.g.: 2024-11-15
O/R No	orNo	String	E.g.: OR12345
Agent	agentCode	String	E.g.: AGT001
Area	areaCode	String	E.g.: AREA01
Paid Amount	paidAmount	Number	E.g.: 1500.00
Customer Code	customerCode	String	E.g.: CUST001
Project	projectCode	String	E.g.: PROJ001
Cancelled	isCancelled	Boolean	E.g.: true
Non-Refundable	isNonRefundable	Boolean	E.g.: true
Paid By	paidBy	String	E.g.: Company Name
Received In	receivedIn	String	E.g.: Bank Account
Bank Charge	bankCharge	Number	E.g.: 50.00
Cheque No	chequeNo	String	E.g.: CHQ123456
Description	pmDescription	String	E.g.: Payment for invoice
Type	documentType	String	E.g.: Invoice
Date (Details)	knockOffDate	Date	E.g.: 2024-11-16
Doc No	documentNo	String	E.g.: DOC12345
Description (Details)	knockOffDescription	String	E.g.: Payment applied to invoice
Amount	amount	Number	E.g.: 1500.00
Pay	payAmount	Number	E.g.: 1500.00
Sync From	'ABC COMPANY'	String	E.g.: ABC COMPANY
Last Modified	lastModified	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.

SQL 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}'

jobs.json

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"
  }
]

Logic Script

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)