ProfitAndLossReport:

Introduction

The ERP server software (ERP Utilities Service) includes a HTTP Web API module. This module can be enabled and configured using the ERP Service Menu application installed on the ERP Server.

The Web API uses http or https get and post commands as a means to retrieve or update various objects within ERP pasing the data as JSON structures.

Every request to the API must include a User Name and Password as part of the standard http authorization header. The User Name and Password must be set up in ERP as a user with appropriate access levels and this User Name must be specified in the Web API configuration.

Every request must also include the name of the ERP Database that you wish to access. The Database Name is passed as name/value pair in the http request header eg database: sample_company

General

GET and POST url's have the following general format:
http[s]://(server)[:port]/erpapi/(ERP Object)/[parameters]

eg To retrieve a list of customers:
https://myserver.com/erpapi/TCustomer

* Note: that most ERP object names a prefixed with the letter T for type, eg:
Supplier – TSupplier
Product/Part - TProductWeb
Invoice - TInvoice
Sales Order – TSalesOrder

Successful operations will return a http response code of 200;
Operations that fail will return an appropriate http code and a "errormessage" value in the response http header.

Lists

List Filtering:

Lists also support basic filtering operations eg to retrieve a list of invoices that are not paid and have a Sales Category of General:

https://myserver,com/erpapi/TInvoice?select=[TotalPaid] = 0 and [SalesCategory] = "General"

Rules:
1) Object properties are surrounded by square backets ie [propertyname]
2) supports >, <, <=, >=, +, -, (, )
3) string constants should be in quotes

List Limiting:

If lists are large it is advisable to retrieve them in chunks:

To return the first 5 customers:
https://localhost/erpapi/TCustomer?LimitCount=5

To return the next 5 customers:
https://localhost/erpapi/TCustomer?LimitCount=5&LimitFrom=5

* Note: List Limiting and List Filtering can not be combined.

Retrieving ERP Objects

Objects can be retrieved using their Id, GlobalRef or Key Value (if applicable) eg:
https://localhost/erpapi/TCustomer/23

https://localhost/erpapi/TCustomer?GlobalRef=DEF3

https://localhost/erpapi/TCustomer?KeyValue="Apple Corp"

This also works with multi-field key strings:
https://localhost/erpapi/TPreference?KeyValue="Admin .","GuiPrefs","TRemindersListGUI"

Data is returned as a JSON structure eg for a customer (formatting allied):
http://localhost:88/erpapi/TCustomer/3

{
"type":"TCustomer",
"fields":
{
"ABN":"",
"Active":true,
"Altcontact":"",
"AltPhone":"",
"ARBalance":126798.71,
"Area":"0",
"AssessorsName":"",
"BailmentAmountEx":0,
"BailmentNumber":"",
"Balance":126798.71,
"BankAccountBSB":"",
"BankAccountName":"",
"BankAccountNo":"",
"BankCode":"",
"Billcountry":"",
"BillingAddress":"Hunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"BillingAddressWithName":"Apple Corp\r\nHunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"BillPostcode":"4113",
"BillState":"Qld",
"BillStreet":"Hunters Shack",
"BillStreet2":"2 Sherwood Forest",
"BillStreet3":"",
"Billsuburb":"8 MILE PLAINS",
"BodyType":"",
"CallPriority":5,
"CardNumber":"",
"ClaimNumber":"",
"ClientAddress":"Apple Corp\r\nHunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"ClientCustomFieldValues":null,
"ClientDetails":"Mr William Tell\r\nApple Corp\r\nHunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"ClientName":"Apple Corp",
"ClientNo":"",
"ClientTaxExceptionProducts":null,
"ClientTypeName":"Trade",
"Colour":"",
"Contacts":[
{
"type":"TContact",
"fields":
{
"AccountNo":"",
"Active":true,
"CardNumber":"",
"ClientID":3,
"ClientName":"Apple Corp",
"Company":"Apple Corp",
"ContactAddress":"Hunters Shack",
"ContactAddress2":"2 Sherwood Forest",
"ContactAddress3":"",
"ContactAltPH":"",
"ContactCity":"8 MILE PLAINS",
"ContactCountry":"",
"ContactEmail":"bill@applecorp.com.au",
"ContactFax":"",
"ContactFirstName":"William",
"ContactMOB":"",
"ContactPcode":"4113",
"ContactPH":"0755555555",
"ContactState":"Qld",
"ContactSurName":"Tell",
"ContactTitle":"Mr",
"GlobalRef":"DEF19",
"ID":19,
"IsPrimaryContact":true,
"JobTitle":"",
"KeyValue":"\"Apple Corp\",\"Apple Corp\"",
"MsTimeStamp":"2012-03-30 09:22:08",
"MsUpdateSiteCode":"DEF",
"Notes":"",
"TeamviewerID":"",
"UseOnRun":false
}
}],
"Country":"",
"CreditBalance":130344.46,
"CreditLimit":2000,
"CUSTDATE1":"",
"CUSTFLD3":"",
"CUSTFLD4":"",
"CustomerEquipment":[
{
"type":"TCustomerEquipment",
"fields":
{
"Active":true,
"ClientName":"Apple Corp",
"CustFld1":"",
"CustFld10":"",
"CustFld2":"",
"CustFld3":"",
"CustFld4":"",
"CustFld5":"",
"CustFld6":"",
"CustFld7":"",
"CustFld8":"",
"CustFld9":"",
"Description":"",
"EquipmentID":34,
"EquipName":"Tractor",
"GlobalRef":"DEF9",
"ID":9,
"Manufacture":"",
"Model":"",
"MsTimeStamp":"2012-03-05 14:24:55",
"MsUpdateSiteCode":"DEF",
"Notes":"",
"Quantity":0,
"Registration":"",
"SerialNo":"",
"UOM":"Units",
"UOMMultiplier":1,
"UOMNameProductKey":"Units:",
"UOMQty":0,
"WarantyFinishDate":"",
"WarantyPeriod":0,
"WarantyStartDate":"2009-11-16 00:00:00"
}
},
{
"type":"TCustomerEquipment",
"fields":
{
"Active":true,
"ClientName":"Apple Corp",
"CustFld1":"",
"CustFld10":"",
"CustFld2":"",
"CustFld3":"",
"CustFld4":"",
"CustFld5":"",
"CustFld6":"",
"CustFld7":"",
"CustFld8":"",
"CustFld9":"",
"Description":"John Deer Tractor",
"EquipmentID":34,
"EquipName":"Tractor",
"GlobalRef":"DEF6",
"ID":6,
"Manufacture":"John Deer",
"Model":"JUD785",
"MsTimeStamp":"2012-03-05 14:24:55",
"MsUpdateSiteCode":"DEF",
"Notes":"",
"Quantity":1,
"Registration":"",
"SerialNo":"",
"UOM":"Units",
"UOMMultiplier":1,
"UOMNameProductKey":"Units:",
"UOMQty":1,
"WarantyFinishDate":"",
"WarantyPeriod":0,
"WarantyStartDate":"2009-11-10 00:00:00"
}
}],
"CustomerNextInvoiceNo":1,
"DefaultClassName":"",
"DefaultContactMethod":"",
"DeliveryNotes":"",
"DischargeDate":"",
"Discount":0,
"DontContact":false,
"Email":"bill@applecorp.com.au",
"EmailXML":"",
"ExcessAmount":0,
"Faxnumber":"",
"FirstName":"William",
"ForcePONumOnInvoice":false,
"ForcePOOnBooking":false,
"ForcePOOnRepair":false,
"ForeignExchangeCode":"AUD",
"GlobalRef":"DEF3",
"GracePeriod":5,
"GroupDiscountOverridesAll":false,
"hours":0,
"ID":3,
"IncoPlace":"",
"InvoiceComment":"",
"InvoiceCommentPopUp":false,
"IsCustomer":true,
"IsInternal":false,
"IsOtherContact":true,
"IsSupplier":false,
"KeyValue":"Apple Corp",
"LastName":"Tell",
"ManufactureName":"",
"Mobile":"",
"Model":null,
"ModelNumber":"",
"MsTimeStamp":"2013-06-18 09:05:40",
"MsUpdateSiteCode":"DEF",
"NewOrUsed":"",
"Notes":"",
"OtherFollowUps":null,
"ParentClientName":"",
"ParentRelatedClientName":"",
"PaymentMethodName":"",
"Phone":"0755555555",
"PhonesupportTill":"",
"PickingPriority":5,
"POComment":"",
"POCommentPopup":false,
"PortOfDischarge":"",
"PortOfLanding":"",
"Postcode":"4113",
"ProductGroupDiscount":[
{
"type":"TProductGroupDiscount",
"fields":
{
"Col1":"",
"Col2":"Head Gear",
"Col3":"",
"CustomerName":"Apple Corp",
"Discount":10,
"GlobalRef":"DEF3",
"ID":3,
"KeyValue":"",
"MsTimeStamp":"2012-03-05 14:25:43",
"MsUpdateSiteCode":"DEF"
}
}],
"RelatedCustomers":[
{
"type":"TRelatedCustomer",
"fields":
{
"ChildCompany":"Big Burtha",
"GlobalRef":"DEF1",
"ID":1,
"KeyValue":"",
"MasterCompany":"Apple Corp",
"MsTimeStamp":"2012-03-05 14:25:49",
"MsUpdateSiteCode":"DEF"
}
},
{
"type":"TRelatedCustomer",
"fields":
{
"ChildCompany":"Burns",
"GlobalRef":"DEF2",
"ID":2,
"KeyValue":"",
"MasterCompany":"Apple Corp",
"MsTimeStamp":"2012-03-05 14:25:49",
"MsUpdateSiteCode":"DEF"
}
}],
"RepName":"Dene Mills",
"RewardPointsExpireDate":"",
"RewardPointsOpeningBalance":0,
"RewardPointsOpeningDate":"",
"SalesOrderBalance":3545.75,
"ShippingAgentCode":"",
"ShippingMethodName":"",
"ShipToAddress":"Apple Corp\r\nHunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"SkypeName":"",
"SourceName":"Referral",
"SpecialDiscount":0,
"SpecialProductPriceOverridesAll":false,
"SpecialProducts":[
{
"type":"TSpecialProducts",
"fields":
{
"CustomerName":"Apple Corp",
"Description":"17 Monitor IBM",
"GlobalRef":"DEF74",
"ID":74,
"IncludeInRun":false,
"KeyValue":"",
"LinePrice":95,
"MsTimeStamp":"2012-03-05 14:24:56",
"MsUpdateSiteCode":"DEF",
"Name":"Mntr 607",
"OrigPrice":107.9545
}
},
{
"type":"TSpecialProducts",
"fields":
{
"CustomerName":"Apple Corp",
"Description":"Wagon Set with replacement wheels",
"GlobalRef":"DEF75",
"ID":75,
"IncludeInRun":false,
"KeyValue":"",
"LinePrice":2000,
"MsTimeStamp":"2012-03-05 14:24:56",
"MsUpdateSiteCode":"DEF",
"Name":"Wagon Set",
"OrigPrice":0
}
}],
"State":"Qld",
"StockReceivedDate":0,
"StopCredit":false,
"StormDate":"",
"StormLocation":"",
"Street":"Hunters Shack",
"Street2":"2 Sherwood Forest",
"Street3":"",
"StreetAddress":"Hunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"StreetAddressWithName":"Apple Corp\r\nHunters Shack\r\n2 Sherwood Forest\r\n8 MILE PLAINS Qld 4113",
"Suburb":"8 MILE PLAINS",
"TaxCodeName":"",
"TeamviewerID":"",
"TermsName":"COD",
"Title":"Mr",
"URL":"",
"UseCustomerInvoiceNo":false,
"Year":""
}
}

Adding ERP Objects

For example to add a new Sales Order to ERP a POST would be made to the following URI (assuming the Web API was configured to use port 88):
http://localhost:88/erpapi/TSalesOrder

The body of the request would contain the JSON Data:

{
"type":"TSalesOrder",
"fields":
{
"GLAccountName":"Accounts Receivable",
"CustomerName":"4X4 World",
"TermsName":"COD",
"EmployeeName":"Admin .",
"SaleClassName":"Default",
"SaleDate":"2013-09-23",
"ShipToDesc":"XYZ Pty Ltd\r\n12 Mud Drive\r\nROTTNEST ISLAND WA 6161\r\nAustralia",
"Lines":[
{
"type":"TSalesOrderLine",
"fields":
{
"ProductName":"Mse/Pad",
"UnitOfMeasure":"Units",
"UOMQtySold":2,
"LinePrice":20.45455,
"LineTaxCode":"GST",
"LinePriceInc":22.50001,
"TotalLineAmount":40.91,
"TotalLineAmountInc":45
}
},
{
"type":"TSalesOrderLine",
"fields":
{
"ProductName":"Mntr 607",
"UnitOfMeasure":"Units",
"UOMQtySold":1,
"LinePrice":150,
"LineTaxCode":"GST",
"LinePriceInc":165,
"TotalLineAmount":150,
"TotalLineAmountInc":165
}
}],
"Comments":"This is comment line one\r\nand this is comment line two.",
"TotalAmount":190.91,
"TotalAmountInc":210
}
}

If the Sales Order was successfuly added the response would contain the Id of the newly created item:

{
"type":"TSalesOrder",
"fields":
{
"ID":2207
}
}

* Note: for this operation to be successful the Customer and Products must already exist in ERP.

Updating ERP Objects

To update an object in ERP the JSON must include the GlobalRef (or ID) of the instance to be updated along with the changes.

For example to replace the "Comments" field in the Sales Order created above:

http://localhost:88/erpapi/TSalesOrder

{
"type":"TSalesOrder",
"fields":
{
"GlobalRef":"DEF2207",
"Comments":"This is a new comment."
}
}

The following is returned if successful:

{
"type":"TSalesOrder",
"fields":
{
"ID":2207
}
}

System Info

A special object is provided to supply basic system info for the current ERP system:

http://localhost/erpapi/TErpSysInfo

{
"type":"TErpSysInfo",
"fields":
{
"CompanyName":"Sample Company",
"DatabaseName":"sample_company",
"DbUTCOffset":0.416666666,
"ModuleVersion":"11.3.6.0",
"MySQLUTCOffset":0.4167,
"SiteCode":"DEF",
"UTCOffset":0.416666666666667,
"Version":"11.3.6.0"
}
}

TrueERP - Web API setup

The below instructions is to setup your TrueERP Web API to receive and process any requests through another source/program/web site/phone or tablet.

---

If you can now connect, ignore the port segment.

If you still can't connect after adjusting the firewall we will now adjust the Port number that the Web API will use to communicate with your TrueERP company.

---

TrueERP Web API Report Export To Excel

How to import data into Microsoft Excel from the TrueERP Web API.

Parameters

Each request sent to the ERP API can have one or more parameters passed as part of the URL. For example the the Balance Sheet request can include a parameter to include all Departments in the report as follows: http://192.168.1.103:88/erpapi/BalanceSheetReport?AllDepartments=true

The general format for passing parameters to the API is as follows:
http://[server name of IP]:[port]/erpapi/[report name]?[parm1]=[val1]&[parm2]=[val2]

The following section describes the available reports and there optional parameters.

TrueERP Web API Reports / Data Available

BalanceSheetReport

Parameter Name	Description	Type	Default
AllDepartments	If true, all departments are included in the report	true/false	true
SelectedDepartments	A comma separated list of Department names to include in the report.	text	blank
DateTo	As of date for report.	Date string eg “2018-10-30”	Current date
ReportOnForeignCurrency	Include foreign currency on report	true/false	false

---

ProfitAndLossReport

Parameter Name	Description	Type	Default
IncludePriorClosingDate	If true, include data prior to the current closing date	true/false	true
DateFrom	Beginning of data date range	Date string eg “2018-10-30”	Prior to first database data date.
DateTo	End of data date range	Date string eg “2018-10-30”	Current date
DetailReport	If true, include detail data	true/false	true
AllAccounts	If true, include all Accounts in the report. If false, only include Accounts with balances.	true/false	true

---

ProcessProgressAllocReport
This report shows Build Progress Info
Example: http://192.168.1.103:88/erpapi/ProcessProgressAllocReport

Parameter Name	Description	Type	Default
DateFrom	Beginning of data date range	Date string eg “2018-10-30”	Prior to first database data date.
DateTo	End of data date range	Date string eg “2018-10-30”	Current date
SaleID	Invoice ID or Sales Order ID	Numeric Value eg. 2227	All Invoices / Sales Orders
SaleLineID	Invoice Line ID or Sales Order Line ID	Numeric Value eg. 2227	All Invoices / Sales Orders

Example: http://192.168.1.103:88/erpapi/ProcessProgressAllocReport?SaleID=2227&DateFrom="2018-01-01"&DateTo="2018-07-31"

---

SNBatchUsageReport
This reports on sales (Invoices) of serial number products and the batch numbers and quantities that have been used in the manufacture of these serial number products.

Parameter Name	Description	Type	Default
DateFrom	Beginning of data date range	Date string eg “2018-10-30”	Prior to first database data date.
DateTo	End of data date range	Date string eg “2018-10-30”	Current date
UseDateRange	If true, limit data to DateFrom to DateTo range.	true/false	false

---

SerialNumberSalesReport

Parameter Name	Description	Type	Default
DateFrom	Start date for Sale Date range	Date string eg “2018-10-30”	By default there is no date filtering, ie all dates are included.
DateTo	End date for Sale Date range	Date string eg “2018-10-30”	By default there is no date filtering, ie all dates are included.

---

TSerialNumberListCurrentReport
Example: http://192.168.1.103:88/erpapi/TSerialNumberListCurrentReport

Parameters	Default
None	All

---

TSupplierPayment
This shows payments made to suppliers
Example: http://192.168.1.103:88/erpapi/TSupplierPayment

Parameter Name	Description	Type	Default
PaymentDate	Date suppleir payment was made	Date string eg “2018-10-30”	Current date
AccountName	Name of Account that payment was made out of	Text	blank
POID	The ID from a PO, Bill or Credit	Numeric Value eg. 2227	All Purchases Orders, Bills and Credits

* Note: The line can either have a PrepaymentId (the ID of a Supplier Prepayment) or a POID (the ID from a PO, Bill or Credit).

Example: http://192.168.1.103:88/erpapi/TSupplierPayment

{
"type":"TSupplierPayment",
"fields":
{
"AccountName":"Bank",
"Amount":350.00,
"CompanyName":"ABC",
"DeptClassName":"Default",
"EmployeeName":"Admin .",
"Lines":
{
"type":"TSupplierPaymentLine",
"fields":
{
"Payment":350.00,
"POID":1110
}
},
"Notes":"Test API Supplier Payment for Bill # 1110",
"PaymentDate":"2018-10-05",
"PaymentMethodName":"Cheque",
"ReferenceNo":"123165"
}
}

In the above example the "Notes" is optional.
The example is for a cheque and the "ReferenceNo" is the cheque number.

---

TrialBalanceReport

Parameter Name	Description	Type	Default
IncludePriorClosingDate	If true, include data prior to the current closing date	true/false	false
AllDepartments	If true, all departments are included in the report	true/false	true
AllOtherDepartments	If true, Add combined totals for all departments.	true/false	true
DateFrom	Beginning of data date range	Date string eg “2018-10-30”	Prior to first database data date.
DateTo	End of data date range	Date string eg “2018-10-30”	Current date
DetailReport	If true, include detail data	true/false	true
AllAccounts	If true, include all Accounts in the report. If false, only include Accounts with balances.	true/false	true
UseDateRange	If true, limit data to DateFrom to DateTo range.	true/false	false
SelectedDepartments	A comma separated list of Department names to include in the report.	text	blank

---

TrueERP Web API Objects List

TrueERP PHP Library

TrueERP provides the file erpwebapi.php which contains the PHP class ERPWebAPI to allow PHP applications to comunicate with the ERP Web API.

The following is a list of the the functions provided by the ERPWebAPI class, if any of these functions fail the error message can be obtained from the LastError property (eg $api->LastError).

function ObjectByID ($ObjectName, $ID);

---

function ObjectByGlobalRef ($ObjectName, $GlobalRef);

---

function ObjectByKeyValue ($ObjectName, $KeyValue);

---

function GetList ($ObjectName, $OnRecordFunc, $BlockSize = 0, $PropertyList = '');

---

function GetListRange ($ObjectName, $OnRecordFunc, $RangeFrom, $RangeCount, $PropertyList = '');

This function functions just like GetList except it only retreives a range of objects starting from $RangeFrom and a count of $RangeFrom.

* Note: that items in ERP are stored in a cronilogical order (ie the order in which they where created) and list numdering starts from 0, so to retreive the first 5 items in a list you would use 0,5 as the values for $RangeFrom, $RangeCount.

---

function LastUpdateTime($ObjectName)

This function if sucessful returns a DateTime object containing the date and time of the most recently update object of the type specified by $ObjectName. If it fails the result = null.

---

function Count($ObjectName)

This function if sucessful returns a total count of objects of the type specified by $ObjectName. If it fails the result = -1.

---

function SendObject($Obj)

This function sends the object $Obj to ERP and returns true if successful. If $Obj contains an Id or GlobalRef ERP will locate the matching object and update it, if not a new object will be inserted.

* Note: that when updating objects it is only neccessary to send the fields that you want to change (other than the required type and ID field). If you are updating an object that contains sub-objects such as Lines on an Invoice you will need to include an object for each line with at least an ID to ensure that line is not deleted.

---

function SendObjectJson($JsonStr)

This function is the same as SendObject but its input parameter is a string containing JSON rather than an object.

---

The erpwebapi.php file also contains the following utility functions.

function NewERPObj($ObjClass, $ID = 0)
This function creates a new object vairable and sets its ERP type to $ObjClass and optionally its ID to $ID.

function WebFormatJson($json);
This function formats (indents) the JSON string passed in $json to improve human readability.

function IterateEchoObject($obj, $indent = "")
This function will iterate through all levels of $obj and display its properties.