# Customer Invoice

## Customer Invoice's Database

<table><thead><tr><th>UI Name</th><th>Database</th><th>Type</th><th>Remarks</th></tr></thead><tbody><tr><td>Doc Date</td><td><pre><code>iv.DOCDATE
</code></pre></td><td><strong><code>Date</code></strong></td><td>E.g.: <code>2024-11-15</code></td></tr><tr><td>Inv No</td><td><pre><code>iv.DOCNO
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>INV12345</code></td></tr><tr><td>Currency Code</td><td><pre><code>iv.CURRENCYCODE
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>USD</code></td></tr><tr><td>Agent</td><td><pre><code>iv.AGENT
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Agent X</code></td></tr><tr><td>Terms</td><td><pre><code>iv.TERMS
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Net 30</code></td></tr><tr><td>Ext. No</td><td><pre><code>iv.DOCNOEX
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>EXT123</code></td></tr><tr><td>Customer Code</td><td><pre><code>iv.CODE
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>CUST123</code></td></tr><tr><td>Area</td><td><pre><code>iv.AREA
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Area 51</code></td></tr><tr><td>Bill To</td><td><pre><code>ga.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Customer ABC</code></td></tr><tr><td>Invoice Description</td><td><pre><code>iv.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Payment for services</code></td></tr><tr><td>Cancelled</td><td><pre><code>iv.CANCELLED
</code></pre></td><td><strong><code>Boolean</code></strong></td><td>E.g.: <code>true</code></td></tr><tr><td>Local Net Total</td><td><pre><code>iv.LOCALDOCAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1000.00</code></td></tr><tr><td>Net Total</td><td><pre><code>iv.DOCAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1000.00</code></td></tr><tr><td>Seq</td><td><pre><code>dtl.SEQ
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>1</code></td></tr><tr><td>Sales A/C</td><td><pre><code>dtl.ACCOUNT
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>SA123</code></td></tr><tr><td>Description (Detail)</td><td><pre><code>dtl.DESCRIPTION
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Payment for services</code></td></tr><tr><td>Project (Detail)</td><td><pre><code>dtl.PROJECT
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Project B</code></td></tr><tr><td>Amount</td><td><pre><code>dtl.AMOUNT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>500.00</code></td></tr><tr><td>Tax</td><td><pre><code>dtl.TAX
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>50.00</code></td></tr><tr><td>Tax Rate</td><td><pre><code>dtl.TAXRATE
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>10%</code></td></tr><tr><td>Tax Inclusive</td><td><pre><code>dtl.TAXINCLUSIVE
</code></pre></td><td><strong><code>Boolean</code></strong></td><td>E.g.: <code>true</code></td></tr><tr><td>Tax Amount</td><td><pre><code>dtl.TAXAMT
</code></pre></td><td><strong><code>Number</code></strong></td><td>E.g.: <code>50.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 Invoice's Sample Code

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

Inistate To SQL Accounting

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

```json
"SQLSync": [
    {
      "name": "SyncInvoice",
      "enabled": true,
      "server": {
        "db": "{DATABASE_FILENAME}",
        "dcf": "C:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF",
        "username": "ADMIN",
        "password": "ADMIN"
      },
      "to": "sqlaccounting",
      "debug": false,
      "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "errorHook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "lastSync": "2025-10-08T12:07:08.302+08:00",
      "interval": 5,
      "start": null
    }
]
```

{% 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
import clr
clr.AddReference("Newtonsoft.Json")
clr.AddReference("System.Net")
clr.AddReference("System.IO")

from System.Text import *
from Newtonsoft.Json import *
from Newtonsoft.Json.Linq import *
from System import *

clr.AddReference("System.Drawing")
from System.Drawing import Bitmap,Image
from System.Drawing.Imaging import ImageFormat
from System.IO import MemoryStream, StreamWriter
from System.Net import WebClient
import binascii
from System import BitConverter

#search
documentList = Module('Invoice').findAll({
    'Audit.UpdatedDate': { '$gt': Inputs['lastSync'] },
    'Audit.UpdatedBy': { '$ne': 'External' }
    #Put Business logic here 
})

resultList = []

def image_to_hex(image_url):
    web_client = WebClient()
    image_data = web_client.DownloadData(image_url)
    
    with MemoryStream(image_data) as image_stream:
        bitmap = Bitmap(image_stream)
        # Resize the image
        original_width = bitmap.Width
        original_height = bitmap.Height
        max_width = 240.0
        max_height = 240.0
        # Calculate new dimensions preserving the aspect ratio
        ratio = min(max_width / original_width, max_height / original_height)
        new_width = int(original_width * ratio)
        new_height = int(original_height * ratio)
        debug('new_width', new_width)
        
        resized_bitmap = bitmap.GetThumbnailImage(new_width, new_height, None, IntPtr.Zero)
        
        # Convert the image to PNG (or other supported formats)
        png_stream = MemoryStream()
        #resized_bitmap.Save(png_stream, ImageFormat.Png)
        png_bytes = png_stream.ToArray()
        
        # Convert PNG byte array to hexadecimal string
        png_hex = ''.join('{:02x}'.format(b) for b in png_bytes)

        # Create RTF content with the embedded PNG image
        rtf = []
        rtf.append(r"{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}}")
        rtf.append(r"\viewkind4\uc1")
        
        #image start
        stream = MemoryStream()
        resized_bitmap.Save(stream, ImageFormat.Bmp)  # Save as BMP
        stream.Position = 0  # Reset stream position

        # Read bytes and convert to hex
        img_bytes = stream.ToArray()
        #debug('img_bytes', img_bytes)
        #debug('here', 'here1')
        #hex_string = binascii.hexlify(img_bytes).decode('ascii')
        hex_string = BitConverter.ToString(img_bytes).Replace("-", "").ToLower()

        # Write the RTF content to a file
        return hex_string

def prepareRow(x):
    row = JObject()
    row["ACCOUNT"] = x['Item Codes']['Item Code']
    row["DESCRIPTION"] = str(x['Description'])[:80]
    row["QTY"] = x['Quantity']
    row["UNITPRICE"] = x['Unit Price'] * x['Exchange Rate']
    row["IRBM_CLASSIFICATION"] = str(x['Classification'])
    if x["Tax Code"].Id != 0:
        row["TAX"] = x['Tax Code']['Tax Code'].ToString()
        row["TAXRATE"] = x['Tax Code']['Percentage'].ToString() + "%"
        row["TAXAMT"] = x['SST']
    else:
        row["TAX"] = ""
        row["TAXRATE"] = ""
        row["TAXAMT"] = ""     
    return row
    
def preparePayload(data):
    payloadData = JObject()
    payloadData["type"] = "SL_IV"
    payloadData["payload"] = JObject()
    
    if data['Code'] != None and data['Code'] != 0:
        payloadData["payload"]["DOCKEY"] = data['Code']
    
    payloadData["payload"]["CODE"] = data['Company Code'] if data['Company Code'] is not None else ""

    payloadData["payload"]["DOCNO"] = None if data['Running Number'] == "" or data['Running Number'] is None else data['Running Number']
    payloadData["payload"]["DOCDATE"] = data['Invoice Date']
    payloadData["payload"]["DESCRIPTION"] = str(data['Transaction Description'])[:80]
    payloadData["payload"]["AGENT"] = data['Prepared By'].ToString()
    payloadData["payload"]["TERMS"] = data['Term'].ToString()
    payloadData["payload"]["CANCELLED"] = data['Cancelled']
    payloadData["payload"]["cdsDocDetail"] = JArray.FromObject(data['Details'].Select(lambda x: prepareRow(x)))
    
    payloadData["LastModified"] = data.UpdatedDate
    return payloadData

#end of added
for entry in documentList:
    payloadData = preparePayload(entry)
    if payloadData["payload"]["CODE"] != None and payloadData["payload"]["CODE"] != "":
        resultList.append(payloadData)
    
debug('done', resultList)

outputs['results'] = JArray.FromObject(resultList)
```

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

SQL Accounting to Inistate&#x20;

{% 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_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}'
```

{% endtab %}

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

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

{% 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 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)
```

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