Update SWOT Strings
After a Transaction update a basic SWOT update routine is automatically called. This updates the periods, and the customer ranking. For updates to other SWOT results, the appropriate Update routines should be called as follows:
REST Post: /api/SWOT/18/?Token= [Co.Token]& Para1={updateType}
Where
updateType = 0 = Basic Update
updateType = 1 = Regular Update
updateType = 2 = Weekly Update
updateType = 3 = Monthly Update
Data Loading:
There are 4 data loading APIs for uploading data and two utility calls for updating Categories:
- Customers
- Products
- Transactions
- Contacts
- Categories
- Category Headers
Each can be uploaded with a Rest call with a Json data body.
NOTE: The Token in the URL can be also passed as part of a basic authorised header.
User:Token
Password: {Token Value}
These are the structures to POST data into the SWOT dataset:
1. Customers
REST Post: /api/SWOT/30/?Token=[companyToken]
Fields
[cid]
|
[nvarchar](36)
|
NOT NULL
|
Unique ID of customer from Accounting system
|
[cname]
|
[nvarchar](60)
|
NOT NULL
|
Name of customer. Should also be unique
|
[ccat1]
|
[nvarchar](22)
|
NULL
|
Category 1 of customer
|
[ccat2]
|
[nvarchar](22)
|
NULL
|
Category 2 of customer
|
[ccat3]
|
[nvarchar](22)
|
NULL
|
Category 3 of customer
|
[ccat4]
|
[nvarchar](22)
|
NULL
|
Category 4 of customer
|
[ccat5]
|
[nvarchar](22)
|
NULL
|
Category 5 of customer
|
[nvalue1]
|
[numeric] (12, 3)
|
NULL
|
Free for customer value eg Balance outstanding
|
[nvalue2]
|
[numeric] (12, 3)
|
NULL
|
Ditto 2
|
[nvalue3]
|
[numeric] (12, 3)
|
NULL
|
Ditto 3
|
[nvalue4]
|
[numeric] (12, 3)
|
NULL
|
Ditto 4
|
[nvalue5]
|
[numeric] (12, 3)
|
NULL
|
Ditto 5
|
[ccontact]
|
[nvarchar] (100)
|
NULL
|
Main FullName of Contact from Customer Record
|
[cemail]
|
[nvarchar] (100)
|
NULL
|
Main Email from customer record
|
[cphone1]
|
[nvarchar] (20)
|
NULL
|
Phone from Customer Record
|
[cphone2]
|
[nvarchar] (20)
|
NULL
|
Phone2 from Customer Record
|
[ExternalRef1]
|
[nvarchar] (36)
|
NULL
|
Additional Unique External Reference 1
|
[ExternalRef2]
|
[nvarchar] (36)
|
NULL
|
Additional Unique External Reference 2
|
Json Input Example:
[{"ccat1":"C1GV","ccat2":"C2REP_3","ccat3":"C3087","ccat4":"C4___RD","ccat5":"C5___ED","ccontact":"Neale De Malmanche","cemail":"neale@go.com.au","cid":"C1008","cname":"Go Vita Werribee Pla","cphone1":"8944777RYE03","cphone2":"0412317947", "irank":0,"nvalue1":2,"nvalue2":0,"nvalue3":0,"nvalue4":3,"nvalue5":3}]
Return String:
"{\r\n \"Table\": [\r\n {\r\n \"RecordsInput\": 3673\r\n }\r\n ]\r\n}"
2. Products
REST Post: /api/SWOT/31/?Token=[companyToken]
Fields
[cid]
|
[nvarchar](36)
|
NOT NULL
|
Unique identifier of the Product
|
[cname]
|
[nvarchar](60)
|
NOT NULL
|
(Should be) Unique Name of Product
|
[pcat1]
|
[nvarchar](22)
|
NULL
|
Product Category 1
|
[pcat2]
|
[nvarchar](22)
|
NULL
|
Product Category 2
|
[pcat3]
|
[nvarchar](22)
|
NULL
|
Product Category 3
|
[pcat4]
|
[nvarchar](22)
|
NULL
|
Product Category 4
|
[pcat5]
|
[nvarchar](22)
|
NULL
|
Product Category 5
|
[nvalue1]
|
[numeric] (12, 3)
|
NULL
|
Free for Stock on Hand, UOM etc
|
[nvalue2]
|
[numeric] (12, 3)
|
NULL
|
Ditto
|
[nvalue3]
|
[numeric] (12, 3)
|
NULL
|
Ditto
|
[nvalue4]
|
[numeric] (12, 3)
|
NULL
|
Ditto
|
[nvalue5]
|
[numeric] (12, 3)
|
NULL
|
Ditto
|
[nunitprice]
|
[numeric] (12, 3)
|
NULL
|
Latest Unitprice for Product
|
[nunitcost]
|
[numeric] (12, 3)
|
NULL
|
Latest Unit Cost for Product
|
[cmanager]
|
[nvarchar](22)
|
NULL
|
Manager reference
|
[ExternalRef1]
|
[nvarchar] (36)
|
NULL
|
Additional Unique External Reference 1
|
[ExternalRef2]
|
[nvarchar] (36)
|
NULL
|
Additional Unique External Reference 1
|
Json Input example:
[{"cid":"C00102","cmanager":"","cname":"Oat Bran 5kg","irank":0,"nunitcost":0,"nunitprice":0,"pcat1":"P1LOW","pcat2":"P20111","pcat3":"P301","pcat4":"P401","pcat5":"P5___ED"}]
Return String:
"{\r\n \"Table\": [\r\n {\r\n \"RecordsInput\": 3673\r\n }\r\n ]\r\n}"
3.Transactions
REST Post: /api/SWOT/33/?Token=[companyToken]
Fields
[cprodid]
|
[nvarchar](36)
|
NOT NULL
|
Unique identifier of the Product
|
[ccustid]
|
[nvarchar](36)
|
NOT NULL
|
Unique identifier of the Customer
|
[cperiodid]
|
[nvarchar](4)
|
NULL
|
Period Identifer if special period Ids are used.
|
[csmanid]
|
[nvarchar](36)
|
NULL
|
Unique identifier of the Rep
|
[cperiod2id]
|
[nvarchar](4)
|
NULL
|
Period Identifer 2 if special period Ids are used.
|
[nunits]
|
[numeric] (18, 3)
|
NOT NULL
|
Units of the transaction
|
[nvalue]
|
[numeric] (18, 3)
|
NOT NULL
|
Value of the Transaction
|
[ncost]
|
[numeric] (18, 3)
|
NULL
|
Cost of the transaction
|
[dtrandate]
|
[date]
|
NOT NULL
|
Date of the transaction
|
[dtrantime]
|
[datetime]
|
NULL
|
Time of Transaction (if available)
|
[Ltag]
|
[int]
|
NULL
|
|
[ExternalRef1]
|
[nvarchar] (36)
|
NULL
|
Additional External Reference 1. Could be used for Invoice Number
|
[ExternalRef2]
|
[nvarchar] (36)
|
NULL
|
Additional Unique External Reference
|
Json Input example:
[{"ccustid":"C1024","cprodid":"C0070","csmanid":"11","dtrandate":"2014-1-29","ncost":6.920,"nunits":1,"nvalue":10 }]
Return String:
"{\r\n \"Table\": [\r\n {\r\n \"RecordsInput\": 3673\r\n }\r\n ]\r\n}"
4. Contacts
REST Post: /api/SWOT/32/?Token=[companyToken]
Fields
[contactid]
|
[nvarchar](36)
|
|
NOT NULL
|
[contactGUID]
|
[nvarchar](36)
|
|
NULL
|
[customerid]
|
[nvarchar](36)
|
|
NOT NULL
|
[contacttype]
|
[nvarchar](30)
|
|
NULL
|
[FirstName]
|
[nvarchar](30)
|
|
NULL
|
[MiddleNames]
|
[nvarchar](30)
|
|
NULL
|
[Surname]
|
[nvarchar](30)
|
|
NULL
|
[Fullname]
|
[nvarchar](96)
|
|
NOT NULL
|
[JobTitle]
|
[nvarchar](30)
|
|
NULL
|
[Email1]
|
[nvarchar](64)
|
|
NULL
|
[Email2]
|
[nvarchar](64)
|
|
NULL
|
[Phone1]
|
[nvarchar](24)
|
|
NULL
|
[Phone2]
|
[nvarchar](24)
|
|
NULL
|
[Address1]
|
[nvarchar] (128)
|
|
NULL
|
[Address2]
|
[nvarchar] (128)
|
|
NULL
|
[Suburb]
|
[nvarchar] (64)
|
|
NULL
|
[City]
|
[nvarchar] (64)
|
|
NULL
|
[PostCode]
|
[nvarchar] (32)
|
|
NULL
|
[Country]
|
[nvarchar] (64)
|
|
NULL
|
Json Input example:
[{“contactid”:”JSMIT01”,"contactGUID":" 0871FEF4-53C6-4864-ACD3-793F5C9DF577","customerid":" 98711234-5256-4984-ABD5-793F5BCDF589","contacttype":"1","FullName":"John Smithers","Phone1":”90 21456673458”,"email1":"nvalue@hoppers.com.au" }]
Return String:
(to be completed)
5. Categories
REST Post: /api/SWOT/38/?Token=[companyToken]
This routine does an update to the category tables based on the current data in the category fields of the Customer and Product Tables
Return String:
"{\r\n \"Table\": [\r\n {\r\n \"Updated\": 1,\r\n \"Error\": null\r\n }\r\n ]\r\n}"
or if there are errors
"{\r\n \"Table\": [\r\n {\r\n \"Updated\": 0,\r\n \"Error\":Arithmetic overflow error converting numeric to data type numeric. \r\n }\r\n ]\r\n}"
6. Category Headers
REST Post: /api/SWOT/37/?Token=[companyToken]& Para1={P1,P2,P3,P4,P5,C1,C2,C3,C4,C5}& Para2=[HeaderDescriptionText]
Example Call:
/api/SWOT/37?Token=451194A8-70A9-4B4F-ABE4-259E0831D96D&Para1=P1&Para2=dogegory
Return String if correctly executed:
"{\r\n \"Table\": [\r\n {\r\n \"Updated\": 1,\r\n \"Error\": null\r\n }\r\n ]\r\n}"
or if there are errors
"{\r\n \"Table\": [\r\n {\r\n \"Updated\": 0,\r\n \"Error\":Arithmetic overflow error converting numeric to data type numeric. \r\n }\r\n ]\r\n}"
Basic Database Diagram
Utilities for checking existing data:
Get Maximum and minimum dates in Dataset
REST Get: /api/SWOT/18/?Token=[companyToken]
Return String Json format table:
"{\r\n \"Table\": [\r\n {\r\n \"MaxDate\": \"2013-12-22T00:00:00\",\r\n \"MinDate\": \"2012-01-05T00:00:00\"\r\n }\r\n ]\r\n}"
Get Transaction Totals Information
REST Get: /api/SWOT/19/?Token=[companyToken]&Para1=[yyyy-mm-dd]&Para2=[yyyy-mm-dd]
Where Para1 = From date and Para2 = to Date
Example:
/api/SWOT/19/?Token=0871FEF4-53C6-4864-ACD3-793F5C9DF588&Para1=2012-12-01&Para2=2013-02-02
Return String Json format Table
"{\r\n \"Table\": [\r\n {\r\n \"TotalValue\": 302023.890,\r\n \"TotalCost\": 249597.818,\r\n \"TotalUnits\": 23880.000,\r\n \"TotalTrans\": 8528\r\n }\r\n ]\r\n}"