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 to the correct field before proceeding to run ANY 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 = []

# 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}"
  }
]