> For the complete documentation index, see [llms.txt](https://inistate.gitbook.io/home/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://inistate.gitbook.io/home/advanced/integration/accounting-2/debtor.md).

# Debtor

## Debtor's Database

<table data-full-width="false"><thead><tr><th>UI Name</th><th>Database</th><th>Type</th><th>Remarks</th></tr></thead><tbody><tr><td>Debtor Account</td><td><pre><code>AccNo
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>400-A001</code></td></tr><tr><td>Company Name</td><td><pre><code>CompanyName
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Gney Software Sdn Bhd</code></td></tr><tr><td>Billing Address 1</td><td><pre><code>Address1
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>1, Jalan Melati Indah,</code></td></tr><tr><td>Billing Address 2</td><td><pre><code>Address2
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Taman Bukit Mewah,</code></td></tr><tr><td>Billing Address 3</td><td><pre><code>Address3
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>43000 Kajang,</code></td></tr><tr><td>Billing Address 4</td><td><pre><code>Address4
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>Selangor.</code></td></tr><tr><td>Phone</td><td><pre><code>Phone1
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>012 - 4567890</code></td></tr><tr><td>Credit Term</td><td><pre><code>DisplayTerm
</code></pre></td><td><strong><code>String</code></strong></td><td><strong><code>Credit Term</code></strong> must match the same in the Inistate system.<br><br>E.g.: <code>C.O.D.</code></td></tr><tr><td>Email Address</td><td><pre><code>EmailAddress
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>example@test.com</code></td></tr><tr><td>Agent</td><td><pre><code>SalesAgent
</code></pre></td><td><strong><code>String</code></strong></td><td>E.g.: <code>ADMIN</code></td></tr></tbody></table>

## Debtor'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": "SyncCustomer",
      "enabled": true,
      "server": {
        "db": "ACC-0020.FDB",
        "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-07T09:18:55+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('Company Profile').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 preparePayload(data):
    
    payloadData = JObject()
    payloadData["type"] = "AR_CUSTOMER"
    payloadData["payload"] = JObject()
    if data['Customer Code'] != None and data['Customer Code'] != "":
        payloadData["payload"]["CODE"] = data['Customer Code'] 
   
    payloadData["payload"]["CONTROLACCOUNT"] = '300-000'
    payloadData["payload"]["COMPANYNAME"] = data['Company Name']
    payloadData["payload"]["BRN2"] = data['Reg No.'] if data['Reg No.'] is not None else ""
    payloadData["payload"]["CURRENCYCODE"] = "----"
    payloadData["payload"]["CREDITTERM"] = str(data['Payment Term'])
    payloadData["payload"]["AGENT"] = str(data['Sales Staff']) if data['Sales Staff'] is not None else ""

    
    payloadData["payload"]["cdsBranch"] = JArray.FromObject([{
        "CODE": data['Customer Code'],
        "BRANCHTYPE": "B",
        "BRANCHNAME": str(data['Branch']) if data['Branch'] is not None else "",
        "PHONE1": data['Tel 1'] if data['Tel 1'] is not None else "",
        "PHONE2": data['Tel 2'] if data['Tel 2'] is not None else "",
        "FAX1": data['Fax 1'] if data['Fax 1'] is not None else "",
        "FAX2": data['Fax 2'] if data['Fax 2'] is not None else "",
        "EMAIL": data['Email 1'] if data['Email 1'] is not None else " ",
        "ADDRESS1": str(data['Address 1'])[:40] if data['Address 1'] is not None else "",
        "ADDRESS2": str(data['Address 2'])[:40] if data['Address 2'] is not None else "",
        "ADDRESS3": str(data['Address 3'])[:40] if data['Address 3'] is not None else "",
        "ADDRESS4": str(data['Address 4'])[:40] if data['Address 4'] is not None else "",
        "ATTENTION": str(data['Person In Charge']) if data['Person In Charge'] is not None else ""
    }])

    payloadData["LastModified"] = data.UpdatedDate
    
    return payloadData

#end of added
for entry in documentList:
    payloadData = preparePayload(entry)
    resultList.append(payloadData)

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

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

SQL Accounting to Inistate

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

```sql
SELECT TOP (10)
    AccNo,
    CompanyName,
    Address1,
    Address2,
    Address3,
    Address4,
    Phone1,
    DisplayTerm,
    EmailAddress,
    SalesAgent,
    LastModified
FROM Debtor
WHERE LastModified > "{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": "AutoCount to Inistate",
    "enabled": false,
    "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};",
    "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
    "sql": "SELECT AccNo, CompanyName, Address1, Address2, Address3, Address4, Phone1, DisplayTerm, EmailAddress, SalesAgent, LastModified \nFROM Debtor \nWHERE LastModified > @date",
    "lastSync": "{LAST-SYNC-DATE-TIME}",
    "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
# debug('test', inputs)
debug("test")
moduleName = "{YOUR-MODULE-NAME}"
inistateKeyField = "DocumentId"
keyField = "Doc"
modifiedDateField = "LastModified"
fields = [["DocumentId", "Doc"], ["LastModified", "LastModified"]]
inistateFields = []

result = list(inputs["list"]).ToList()
for item in result:
    entry = Module(moduleName).find({"Dynamic.Extension.{fieldName}": item["AccNo"]})

    form = Module(moduleName).execute("sync", entry)
    form["{CODE}"] = item["AccNo"]
    form["{COMPANY_NAME}"] = item["CompanyName"]
    form["{ADDRESS_1}"] = item["Address1"]
    form["{ADDRESS_2}"] = item["Address2"]
    form["{ADDRESS_3}"] = item["Address3"]
    form["{ADDRESS_4}"] = item["Address4"]
    form["{PHONE_NO}"] = item["Phone1"]
    form["{CREDITS_TERM}"] = item["DisplayTerm"]
    form["{EMAIL_ADDRESS}"] = item["EmailAddress"]
    form["{AGENT}"] = item["SalesAgent"]

    debug("form", form)
    Module(moduleName).submit(entry, form)
```

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

a