# Customer Payment

## Customer Payment's Database

<table><thead><tr><th>UI Name</th><th>Database</th><th>Type</th><th>Remarks</th></tr></thead><tbody><tr><td>Date</td><td><pre><code>pm.DOCDATE
</code></pre></td><td><strong><code>Date</code></strong></td><td>E.g.: <code>2024-11-15</code></td></tr><tr><td>O/R No</td><td><pre><code>pm.DOCNO
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>OR12345</code></td></tr><tr><td>Agent</td><td><pre><code>pm.AGENT
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>AGT001</code></td></tr><tr><td>Area</td><td><pre><code>pm.AREA
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>AREA01</code></td></tr><tr><td>Paid Amount</td><td><pre><code>pm.DOCAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1500.00</code></td></tr><tr><td>Customer Code</td><td><pre><code>pm.CODE
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>CUST001</code></td></tr><tr><td>Project</td><td><pre><code>pm.PROJECT
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>PROJ001</code></td></tr><tr><td>Cancelled</td><td><pre><code>pm.CANCELLED
</code></pre></td><td><strong><code>Boolean</code></strong></td><td>E.g.: <code>true</code></td></tr><tr><td>Non-Refundable</td><td><pre><code>pm.NONREFUNDABLE
</code></pre></td><td><strong><code>Boolean</code></strong></td><td>E.g.: <code>true</code></td></tr><tr><td>Paid By</td><td><pre><code>ac.COMPANYNAME
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Company Name</code></td></tr><tr><td>Received In</td><td><pre><code>ba.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Bank Account</code></td></tr><tr><td>Bank Charge</td><td><pre><code>pm.BANKCHARGE
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>50.00</code></td></tr><tr><td>Cheque No</td><td><pre><code>pm.CHEQUENUMBER
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>CHQ123456</code></td></tr><tr><td>Description</td><td><pre><code>pm.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Payment for invoice</code></td></tr><tr><td>Type</td><td><pre><code>ko.TODOCTYPE
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Invoice</code></td></tr><tr><td>Date (Details)</td><td><pre><code>iv.DOCDATE
</code></pre></td><td><strong><code>Date</code></strong></td><td>E.g.: <code>2024-11-16</code></td></tr><tr><td>Doc No</td><td><pre><code>iv.DOCNO
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>DOC12345</code></td></tr><tr><td>Description (Details)</td><td><pre><code>iv.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Payment applied to invoice</code></td></tr><tr><td>Amount</td><td><pre><code>iv.DOCAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1500.00</code></td></tr><tr><td>Pay</td><td><pre><code>ko.KOAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1500.00</code></td></tr><tr><td>Sync From</td><td><pre><code>'ABC COMPANY'
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>ABC COMPANY</code></td></tr><tr><td>Last Modified</td><td><pre><code>a.DOCDATETIME 
</code></pre></td><td><strong><code>Timestamp</code></strong></td><td>E.g.: <code>2024-11-15 07:48:46.563</code></td></tr></tbody></table>

## Customer Payment's Sample Code

{% hint style="warning" %}
Remember to change to the **correct field** before proceeding to run **ANY** script.
{% endhint %}

{% tabs %}
{% tab title="SQL Script" %}
{% hint style="info" %}
This is a sample SQL, may not be as same as your database.
{% endhint %}

```sql
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}'
```

{% endtab %}

{% tab title="jobs.json" %}
{% hint style="warning" %}
In order to sync, you must include **`LastModified`** in **`SELECT`**
{% endhint %}

<pre class="language-json"><code class="lang-json">"SQLSync": [
<strong>  {
</strong>    "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"
  }
]
</code></pre>

{% endtab %}

{% tab title="Logic Script" %}
{% hint style="info" %}
To find `{fieldName}`, you are required to have access to **Alpha** or use **Network** in Developer Tools `[F12]`.
{% endhint %}

{% code fullWidth="true" %}

```python
#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)
```

{% endcode %}
{% endtab %}
{% endtabs %}