Customer Payment
Customer Payment's Database
UI Name
Database
Type
Remarks
Date
Date
E.g.: 2024-11-15
O/R No
String
E.g.: OR12345
Agent
String
E.g.: AGT001
Area
String
E.g.: AREA01
Paid Amount
Number
E.g.: 1500.00
Customer Code
String
E.g.: CUST001
Project
String
E.g.: PROJ001
Cancelled
Boolean
E.g.: true
Non-Refundable
Boolean
E.g.: true
Paid By
String
E.g.: Company Name
Received In
String
E.g.: Bank Account
Bank Charge
Number
E.g.: 50.00
Cheque No
String
E.g.: CHQ123456
Description
String
E.g.: Payment for invoice
Type
String
E.g.: Invoice
Date (Details)
Date
E.g.: 2024-11-16
Doc No
String
E.g.: DOC12345
Description (Details)
String
E.g.: Payment applied to invoice
Amount
Number
E.g.: 1500.00
Pay
Number
E.g.: 1500.00
Sync From
String
E.g.: ABC COMPANY
Last Modified
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)Last updated