Customer Invoice

Customer Invoice's Database

UI Name	Database	Type	Remarks
Doc Date	iv.DOCDATE	Date	E.g.: 2024-11-15
Inv No	iv.DOCNO	String	E.g.: INV12345
Currency Code	iv.CURRENCYCODE	String	E.g.: USD
Agent	iv.AGENT	String	E.g.: Agent X
Terms	iv.TERMS	String	E.g.: Net 30
Ext. No	iv.DOCNOEX	String	E.g.: EXT123
Customer Code	iv.CODE	String	E.g.: CUST123
Area	iv.AREA	String	E.g.: Area 51
Bill To	ga.DESCRIPTION	String	E.g.: Customer ABC
Invoice Description	iv.DESCRIPTION	String	E.g.: Payment for services
Cancelled	iv.CANCELLED	Boolean	E.g.: true
Local Net Total	iv.LOCALDOCAMT	Number	E.g.: 1000.00
Net Total	iv.DOCAMT	Number	E.g.: 1000.00
Seq	dtl.SEQ	Number	E.g.: 1
Sales A/C	dtl.ACCOUNT	String	E.g.: SA123
Description (Detail)	dtl.DESCRIPTION	String	E.g.: Payment for services
Project (Detail)	dtl.PROJECT	String	E.g.: Project B
Amount	dtl.AMOUNT	Number	E.g.: 500.00
Tax	dtl.TAX	Number	E.g.: 50.00
Tax Rate	dtl.TAXRATE	String	E.g.: 10%
Tax Inclusive	dtl.TAXINCLUSIVE	Boolean	E.g.: true
Tax Amount	dtl.TAXAMT	Number	E.g.: 50.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 Invoice'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_IV, %'
    GROUP BY a.REF
)
SELECT FIRST 3
    iv.DOCDATE docDate,
    iv.DOCNO docNo,
    iv.CURRENCYCODE currencyCode,
    iv.AGENT agent,
    iv.TERMS terms,
    iv.DOCNOEX docNoExt,
    iv.CODE customerCode,
    iv.AREA area,
    ga.DESCRIPTION billTo,
    iv.DESCRIPTION invoiceDescription,
    iv.CANCELLED cancelled,
    iv.LOCALDOCAMT localNetTotal,
    iv.DOCAMT netTotalRm,
    dtl.SEQ seq,
    dtl.ACCOUNT salesAccount,
    dtl.DESCRIPTION dtlDescription,
    dtl.PROJECT project,
    dtl.AMOUNT amount,
    dtl.TAX tax,
    dtl.TAXRATE taxRate,
    dtl.TAXINCLUSIVE taxInclusive,
    dtl.TAXAMT taxAmount,
    '{YOUR_COMPANY}' syncFrom,
    a.DOCDATETIME lastModified
FROM AuditData a
JOIN AR_IV iv ON a.AUDITKEY = iv.DOCKEY
JOIN AR_IVDTL dtl ON iv.DOCKEY = dtl.DOCKEY
JOIN GL_ACC ga ON iv.CODE = ga.CODE
WHERE a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}'

jobs.json

In order to sync, you must include LastModified in SELECT

"SQLSync": [
  {
    "name": "Customer Invoice",
    "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/{MODULE-ID}-{LOGIC-NAME}",
    "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_IV, %' \nGROUP BY a.REF) \nSELECT iv.DOCDATE docDate, iv.DOCNO docNo, iv.CURRENCYCODE currencyCode, iv.AGENT agent, iv.TERMS terms, iv.DOCNOEX docNoExt, iv.CODE customerCode, iv.AREA area, ga.DESCRIPTION billTo, iv.DESCRIPTION invoiceDescription, iv.CANCELLED cancelled, iv.LOCALDOCAMT localNetTotal, iv.DOCAMT netTotalRm, dtl.SEQ seq, dtl.ACCOUNT salesAccount, dtl.DESCRIPTION dtlDescription, dtl.PROJECT project, dtl.AMOUNT amount, dtl.TAX tax, dtl.TAXRATE taxRate, dtl.TAXINCLUSIVE taxInclusive, dtl.TAXAMT taxAmount, '{YOUR_COMPANY}' syncFrom, a.DOCDATETIME lastModified \nFROM AuditData a \nJOIN AR_IV iv ON a.AUDITKEY = iv.DOCKEY \nJOIN AR_IVDTL dtl ON iv.DOCKEY = dtl.DOCKEY \nJOIN GL_ACC ga ON iv.CODE = ga.CODE \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 Invoice'
inistateKeyField = 'DocumentId'
keyField = 'DOCNO'
modifiedDateField = 'LastModified'
fields = [['DocumentId', 'Doc'],['LastModified', 'LASTMODIFIED']]
inistateFields = []
tableField = 'Items'
tableColumns = ['SALESACCOUNT','DTLDESCRIPTION','PROJECT','AMOUNT','TAX','TAXRATE','TAXINCLUSIVE','TAXAMOUNT']

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['DOCNO'] })
    
    form = Module('Customer Invoice').execute('sync', entry)
    form['Transaction Date'] = item['DOCDATE']
    form['Inv No'] = item['DOCNO']
    form['Currency'] = item['CURRENCYCODE']
    form['Agent'] = item['AGENT']
    form['Terms'] = item['TERMS']
    form['Ext No'] = item['DOCNOEXT']
    form['Customer Code'] = item['CUSTOMERCODE']
    form['Area'] = item['AREA']
    form['Bill To'] = item['BILLTO']
    form['Invoice Description'] = item['INVOICEDESCRIPTION']
    if item["CANCELLED"] == 'T':
        form['Cancelled'] = True
    else:
        form['Cancelled'] = False
    form['Local Net Total'] = item['LOCALNETTOTAL']
    form['Net Total (RM)'] = item['NETTOTALRM']
    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)]
        
        formLine['Sales A/C'] = line['SALESACCOUNT']
        formLine['Description'] = line['DTLDESCRIPTION']
        formLine['Project'] = line['PROJECT']
        formLine['Amount'] = line['AMOUNT']
        formLine['Tax'] = line['TAX']
        formLine['Tax Rate (%)'] = line['TAXRATE']
        formLine['Tax Inclusive'] = line['TAXINCLUSIVE']
        formLine['Tax Amt'] = line['TAXAMOUNT']
        
    debug('form', form)
    Module(moduleName).submit(entry, form)

debug('result', result)