Sales Order

Sales Order's Database

UI Name	Database	Type	Remarks
S/O No	docNo	String	Avoid using the same S/O No to prevent overwrite. E.g.: SO-123456
S/O Key	docKey	Number	E.g.: 123456
Debtor	debtorCode	String	Debtor must match existing debtor code, and they must be active state. E.g.: 400-D001
Name	debtorName	String	E.g.: JOHN DOE
Address [Line 1]	invAddr1	String	E.g.: 123 MAIN ST,
Address [Line 2]	invAddr2	String	E.g.: APT 4B,
Address [Line 3]	invAddr3	String	E.g.: NEW YORK, NY 10001
Delivery Address [Line 1]	DeliverAddr1	String	E.g.: 456 SECOND ST,
Delivery Address [Line 2]	DeliverAddr2	String	E.g.: SUITE 5A,
Delivery Address [Line 3]	DeliverAddr3	String	E.g.: LOS ANGELES, CA 90001
Sales Location	salesLocation	String	Must have existing Sales Location in the accounting system. E.g.: HQ
Date	docDate	Date	E.g.: 2024-08-12T00:00:00+08:00
Credit Term	creditTerm	String	Credit Term must match the same in the accounting system. E.g.: C.O.D.
Agent	agent	String	E.g.: JANE SMITH
Ref	ref	String	E.g.: REF-123456
Remark 2	Remark2	String	E.g.: PENDING
Multi Pricing	multiPricing	String	E.g.: Price 1
Description	description	String	E.g.: ORDER DESCRIPTION
Expiry Date	SalesExemptionExpiryDate	Date	E.g.: 2024-10-17T00:00:00+08:00
Phone	phone1	String	E.g.: [phone]
Fax	fax1	String	E.g.: +0987654321
Inclusive	inclusiveTax	Boolean	E.g.: true
Credit Term	displayTerm	String	Credit Term must match the same in the accounting system. E.g.: C.O.D.
UDF	udf	Object	Refer Below
Table Item	details	Array	Refer Below

Sales Order's UDF

UI Name	Database	Type	Remarks
Venue	VENUE	String	E.g.: CONFERENCE CENTER
Gmail	Gmail	String	E.g.: [e-mail]
IC	IC	String	E.g.: S1234567D
Balance	BALANCE	Number	E.g.: 1000
Payment	PAYEMENT	String	E.g.: CREDIT CARD
Branding	BRANDING	String	E.g.: BRAND X
Note	Note	String	E.g.: SPECIAL INSTRUCTIONS
PDate	PDate	Date	E.g.: 2024-10-05T00:00:00+08:00

Sales Order's Table Item

UI Name	Database	Type	Remarks
Item No	ItemCode	String	E.g.: ITEM-123456
Description	description	String	E.g.: ITEM DESCRIPTION
Description 2	desc2	String	E.g.: ADDITIONAL INFO
Delivery Date	deliveryDate	Date	E.g.: 2024-10-17T00:00:00+08:00
Location	location	String	E.g.: WAREHOUSE 1
Quantity	qty	Number	Value must be number, and value cannot be negative. E.g.: 10
UOM	uom	String	E.g.: PCS
Unit Price	unitPrice	Number	Value must be number, and value cannot be negative. E.g.: 100
Subtotal	subtotal	Number	Value must be number, and value cannot be negative. E.g.: 1000
Total	total	Number	Value must be number, and value cannot be negative. E.g.: 1100
UDF	udf	Object	E.g.: "POSTTOPO": "T"
Description 2	description2	String	E.g.: ADDITIONAL DESCRIPTION

Stock Transfer's Sample Code

Remember to change any { } to the correct field before proceeding to run the script.

Logic Script

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

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("{YOUR-MODULE-NAME}").findAll(
    {
        "$or": [
            {
                "Audit.UpdatedDate": {"$gt": Inputs["lastSync"]},
                "Audit.UpdatedBy": {"$ne": "External"},
                "Dynamic.Extension.{fieldName}": {"$in": [None, ""]},
            },
            {
                "Audit.UpdatedDate": {"$gt": Inputs["lastSync"]},
                "Dynamic.Extension.{fieldName}": True,
                "Dynamic.Extension.{fieldName}": {"$in": [None, ""]},
            },
        ]
    }
)
debug("documentList", documentList)

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


# added
def prepareRow(data, x):
    row = JObject()
    # row["ItemCode"] = x['Model']
    row["ItemCode"] = (
        str(x["{YOUR-ITEM-MODEL}"])[:30] if x["{YOUR-ITEM-ID}"] > 0 else None
    )
    row["description"] = x["{YOUR-ITEM-DESCRIPTION}"]
    row["desc2"] = (
        str(x["{YOUR-REMARKS}"])[:100] if x["{YOUR-REMARKS}"] is not None else None
    )
    row["deliveryDate"] = x["{YOUR-DELIVERY-DATE}"]
    row["location"] = data["{YOUR-WAREHOUSE-NAME}"]
    row["qty"] = x["{YOUR-QUANTITY}"]
    row["uom"] = x["{YOUR-ITEM-UOM}"] if x["{YOUR-ITEM-ID}"] > 0 else None
    row["unitPrice"] = x["{YOUR-UNIT-PRICE}"]
    row["subtotal"] = x["{YOUR-AMOUNT}"]
    row["total"] = x["{YOUR-AMOUNT}"]
    row["taxAdjustment"] = None
    row["udf"] = JObject()
    row["udf"]["POSTTOPO"] = x["{YOUR-POST-TO-PO}"]

    row["description2"] = x["{YOUR-REMARKS}"]

    if x["{YOUR-TAX-CODE-ID}"] > 0:
        row["taxType"] = x["{YOUR-TAX-CODE}"]["{YOUR-CODE}"]
        row["taxRate"] = x["{YOUR-TAX-CODE}"]["{YOUR-RATE}"].ToString()
    row["deliveryDate"] = x["{YOUR-DELIVERY-DATE}"]

    if (
        x["{YOUR-PHOTO}"] is not None
        and x["{YOUR-PHOTO}"]["{YOUR-PATH}"] is not None
        and x["{YOUR-PATH}"] != ""
    ):
        url = FilePathTranslator.GetPath(str(x["{YOUR-PHOTO}"]["{YOUR-PATH}"]))
        # debug('path', url)
        result = image_to_hex(url)
        # debug('pic', result)
        row["furtherDescription"] = result
    return row


def preparePayload(data):
    payloadData = JObject()
    payloadData["type"] = "SalesOrder"
    payloadData["cancelled"] = False
    payloadData["payload"] = JObject()
    payloadData["payload"]["docNo"] = data["{YOUR-DOCUMENT-ID}"]
    if data["{YOUR-CODE}"] != None and data["{YOUR-CODE}"] != 0:
        payloadData["payload"]["docKey"] = data["{YOUR-CODE}"]
    payloadData["payload"]["debtorCode"] = data["{YOUR-DEBTOR-CODE}"]
    payloadData["payload"]["debtorName"] = data["{YOUR-NAME}"]

    if data["{YOUR-ADDRESS}"] is not None:
        lines = data["{YOUR-ADDRESS}"].split("\n")
        for i, line in enumerate(lines):
            if i < 4:
                line = str(line.rstrip("\r"))[:40] if line is not None else ""
                payloadData["payload"]["invAddr{0}".format(i + 1)] = line
                payloadData["payload"]["DeliverAddr{0}".format(i + 1)] = line

    payloadData["payload"]["salesLocation"] = data["{YOUR-WAREHOUSE-NAME}"]
    # payloadData["payload"]["docNo"] = None if data['{YOUR-DOCUMENT-ID}'] == "" or data['{YOUR-DOCUMENT-ID}'] is None else data['{YOUR-DOCUMENT-ID}']
    payloadData["payload"]["docDate"] = data["{YOUR-ORDER-DATE}"]
    payloadData["payload"]["creditTerm"] = data["{YOUR-CREDIT-TERM}"]
    payloadData["payload"]["agent"] = data["{YOUR-SALESPERSON-CODE}"]
    payloadData["payload"]["ref"] = data["{YOUR-REFERENCE}"]
    payloadData["payload"]["Remark2"] = data["{YOUR-STATUS-2}"]

    payloadData["payload"]["udf"] = JObject()
    payloadData["payload"]["udf"]["VENUE"] = data["{YOUR-VENUE-NAME}"]
    payloadData["payload"]["udf"]["Gmail"] = data["{YOUR-EMAIL}"]
    payloadData["payload"]["udf"]["IC"] = data["{YOUR-IC-NO}"]
    payloadData["payload"]["udf"]["BALANCE"] = data["{YOUR-BALANCE}"]
    payloadData["payload"]["udf"]["PAYEMENT"] = (
        str(data["{YOUR-DEPOSIT-REFERENCE-NO}"])[:50]
        if data["{YOUR-DEPOSIT-REFERENCE-NO}"] is not None
        else None
    )

    payloadData["payload"]["udf"]["BRANDING"] = data["{YOUR-BRANDING}"]
    if data["{YOUR-BRANDING}"] is None or data["{YOUR-BRANDING}"] == "":
        payloadData["payload"]["udf"]["BRANDING"] = "NONE"

    payloadData["payload"]["udf"]["Note"] = (
        str(data["{YOUR-REMARKS}"])[:200]
        if data["{YOUR-REMARKS}"] is not None
        else None
    )
    payloadData["payload"]["udf"]["PDate"] = data["{YOUR-PROCESSING-DATE}"]

    if data["{YOUR-DELIVERY-DATE}"] is not None:
        payloadData["payload"]["SalesExemptionExpiryDate"] = data[
            "{YOUR-DELIVERY-DATE}"
        ]

    payloadData["payload"]["multiPricing"] = "{YOUR-MULTI-PRICING}"
    payloadData["payload"]["description"] = "{YOUR-DESCRIPTION}"
    payloadData["payload"]["phone1"] = data["{YOUR-CONTACT-NO}"]
    payloadData["payload"]["fax1"] = data["{YOUR-CONTACT-NO-2}"]

    # payloadData["payload"]["currencyRate"] = data['Exchange Rate'] // not use
    payloadData["payload"]["inclusiveTax"] = True
    payloadData["payload"]["displayTerm"] = data["{YOUR-CREDIT-TERM}"]
    payloadData["payload"]["details"] = JArray.FromObject(
        data["{YOUR-ITEMS}"].Select(lambda x: prepareRow(data, x))
    )
    payloadData["LastModified"] = data["{YOUR-UPDATED-DATE}"]
    return payloadData


# payload = JObject.FromObject(payloadData)
# data['Code'] = result['docKey']
# data['Sales Order No'] = result['docNo']

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

debug("done", resultList)

# debug('resultList','done')
# test = JArray.FromObject(resultList)
# debug('here', 'here4')
outputs["results"] = JArray.FromObject(resultList)
# debug('result', resultList)

jobs.json

"Logic": [
  {
    "name": "{NAME}",
    "enabled": false,
    "server": {
      "server": "{SERVER}",
      "database": "{DATABASE}",
      "dbUsername": "{DB_USERNAME}",
      "dbPassword": "{DB_PASSWORD}",
      "user": "{USERNAME}",
      "password": "{PASSWORD}"
    },
    "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": "{LAST_SYNC_DATE_TIME}"
  }
]