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.

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 NextJobs json

Last updated 5 months ago