Find a api to get a salesorder by using invoice no

[kranil7701]

I am looking for the api end point to get the sales order by invoice no only, or get all sales order end point .
please let me know if any api endpoint exist in Jiwa.
Thanks

[SBarnes]

Try /Queries/SO_Main, which queries the main table for sales orders(SO_Main), directly.

This is what under ServiceStack is refered to as an Auto Query for more information on this see the documentation https://docs.servicestack.net/autoquery/rdbms.

Basically all the tables that are in the database are generated into the Service Model name space(JiwaFinancials.Jiwa.JiwaServiceModel.Tables) and whilst not all of them are registered as routes you can add them with one line of code like this

Code: Select all

AppHost.Routes.Add(typeof(SO_MainQuery), "/Queries/SO_Main", "GET", "Retrieves a list of sales orders.", "");


Where basically in this case we want access to the SO_Main table, the class with all the qurey parameters is called SO_MainQuery(convention of table name and Query on the end), /Queries/SO_Main(the route) can be whatever you like but by convention they are placed under Query, be aware if you add any routes the Http Verb must be all upper case.

[Mike.Sheen]

I am looking for the api end point to get the sales order by invoice no only, or get all sales order end point .
please let me know if any api endpoint exist in Jiwa.
Thanks

Either /Queries/SalesOrderList, or as Stuart mentioned /Queries/SO_Main can be used to locate the InvoiceID needed to then perform a full sales order read.

SalesOrderList is a bit more of a beautified list in that it joins in the debtor table to bring the debtor name, for instance.

For example - this api call:

https://api.jiwa.com.au/Queries/SalesOrderList?InvoiceNo=100179&Fields=InvoiceID&apikey=9vjoF4M5-gYVBWjoFyW9nlQieB9FhxpJkAsRJznB_Ok&format=json

produces this result:

Code: Select all

{
    "Results": [
        {
            "InvoiceID": "000000002L000000004C"
        }
    ],
    "Meta": {}
}

Now that you have obtained the InvoiceID you can use /SalesOrders/{InvoiceID} to get the full order

[SBarnes]

Of course, the thing both Mike and I should probably mention is that the api will paginate the results, usual default result in one go is 100 although this is a setting that can be changed, so you need to use skip and take and include the total like this, if you want a complete listing

Code: Select all

https://api.jiwa.com.au/Queries/SalesOrderList?Include=Total&Skip=200&Take=100&Fields=InvoiceID&apikey=9vjoF4M5-gYVBWjoFyW9nlQieB9FhxpJkAsRJznB_Ok&format=json


Which will give you a result like this

Code: Select all

{
    "Offset": 200,
    "Total": 496,
    "Results": [
        {
            "InvoiceID": "0DD2B28456DE4B3B8884"
        },
        {
            "InvoiceID": "0ECC3AF4C6FA4D1E954D"
        },
        {
            "InvoiceID": "0F3198BF062245C2B1AB"
        },
        {
            "InvoiceID": "103B964B8E8B4B999FCE"
        },
        {
            "InvoiceID": "1080EBA08D984A17B37B"
        },
        {
            "InvoiceID": "10F552AAD81640D79C1D"
        },
        {
            "InvoiceID": "11CE2CB624A8488E9341"
        },
        {
            "InvoiceID": "13A03E0390AF495CB39F"
        },
        {
            "InvoiceID": "1446AF6C1BFA4ACF8AB1"
        },
        {
            "InvoiceID": "14CF9F98DCBD44ABB5C0"
        },
        {
            "InvoiceID": "1533D693492947A6A869"
        },
        {
            "InvoiceID": "16123B59BA5D46E1A00A"
        },
        {
            "InvoiceID": "1617B3F5C8E54E349C26"
        },
        {
            "InvoiceID": "179705C9A4A4427E94A1"
        },
        {
            "InvoiceID": "187041E13D2C481FAF60"
        },
        {
            "InvoiceID": "18F7BFBB1EF94994B93D"
        },
        {
            "InvoiceID": "191e840c266c410cb241"
        },
        {
            "InvoiceID": "19BC5F9213D348829AEB"
        },
        {
            "InvoiceID": "1A87FD4540EA40A6BA28"
        },
        {
            "InvoiceID": "1AA2A90E76814C398A09"
        },
        {
            "InvoiceID": "1C164C7F6CB348ACB2F6"
        },
        {
            "InvoiceID": "1C27C735B0EB4EF888EB"
        },
        {
            "InvoiceID": "1dc29f76142a42c298fe"
        },
        {
            "InvoiceID": "1DCDD6E198944AF29A84"
        },
        {
            "InvoiceID": "1E60DFF6E34543DA8765"
        },
        {
            "InvoiceID": "226FA52053E3404A90C7"
        },
        {
            "InvoiceID": "228041DEC7B540B1B35A"
        },
        {
            "InvoiceID": "232A129CD2C6425F915E"
        },
        {
            "InvoiceID": "2457A424C12B45D5899A"
        },
        {
            "InvoiceID": "2570277DFACC4F1180D4"
        },
        {
            "InvoiceID": "25F0A66578EF4B4D8319"
        },
        {
            "InvoiceID": "2604CE6BEEA146A5A3E8"
        },
        {
            "InvoiceID": "2629E3229C544B82A2FF"
        },
        {
            "InvoiceID": "26815559C79F4B3481B3"
        },
        {
            "InvoiceID": "268249522A71489ABC74"
        },
        {
            "InvoiceID": "26995FEC2FA14C048A98"
        },
        {
            "InvoiceID": "274BFA583BE04BA6A137"
        },
        {
            "InvoiceID": "288CCF62520A445D8843"
        },
        {
            "InvoiceID": "2A814485FAE645849755"
        },
        {
            "InvoiceID": "2AA47C878F654F13B15C"
        },
        {
            "InvoiceID": "2C4C9CCAA5724631A2CF"
        },
        {
            "InvoiceID": "2C972446997143D8A481"
        },
        {
            "InvoiceID": "2E18D27155574D5B90DC"
        },
        {
            "InvoiceID": "2EFBAE9400A342EE92DA"
        },
        {
            "InvoiceID": "2EFF043512C44EAEAC25"
        },
        {
            "InvoiceID": "310DE96E22FF4E6DAA66"
        },
        {
            "InvoiceID": "31C033802F3743979B4F"
        },
        {
            "InvoiceID": "32204517ABC248B1B659"
        },
        {
            "InvoiceID": "330A31F9BDFE421C9419"
        },
        {
            "InvoiceID": "338FC490F42F4EFD8BF9"
        },
        {
            "InvoiceID": "34068A3E410E4094B841"
        },
        {
            "InvoiceID": "34B2ED32259B46ABAE51"
        },
        {
            "InvoiceID": "34C7149A363941579828"
        },
        {
            "InvoiceID": "372F638FAEEB413182B3"
        },
        {
            "InvoiceID": "37A74BA6DECE471E9D04"
        },
        {
            "InvoiceID": "37E3AB267BA44392B923"
        },
        {
            "InvoiceID": "382ea53414fa4923b2ef"
        },
        {
            "InvoiceID": "3B4BF612AA3A44218D8B"
        },
        {
            "InvoiceID": "3CE373F15D1A4EFE9A40"
        },
        {
            "InvoiceID": "3D7376D583E14508AB00"
        },
        {
            "InvoiceID": "3F23CE008DC64C0599EE"
        },
        {
            "InvoiceID": "3FDE1082C8E2480EB90B"
        },
        {
            "InvoiceID": "40DA95AE144042C88C62"
        },
        {
            "InvoiceID": "411D7CFCFC294A4F9B1F"
        },
        {
            "InvoiceID": "418FC6740D36452FA885"
        },
        {
            "InvoiceID": "4225B703D2FC44808F0A"
        },
        {
            "InvoiceID": "44F3DDB277A44CCBA6F8"
        },
        {
            "InvoiceID": "4544F8E44D214C50BCDB"
        },
        {
            "InvoiceID": "45838EDACF204D01B7BF"
        },
        {
            "InvoiceID": "4623FF86729F4E5A94A8"
        },
        {
            "InvoiceID": "476679B8397343BD825A"
        },
        {
            "InvoiceID": "47C8136D7C19433DB6FA"
        },
        {
            "InvoiceID": "49CAD8D0E8864B58BAA2"
        },
        {
            "InvoiceID": "4B5AA0EB6A09424B9C59"
        },
        {
            "InvoiceID": "4C2EB909C87244F6A983"
        },
        {
            "InvoiceID": "4C3DEDB44A704A10AC05"
        },
        {
            "InvoiceID": "4CD34C33A5DE49938ABE"
        },
        {
            "InvoiceID": "4cd4cde3d3b045188876"
        },
        {
            "InvoiceID": "4D26BB2C31C348F392E3"
        },
        {
            "InvoiceID": "4E2592207FAB4F048556"
        },
        {
            "InvoiceID": "4EBD7107626641DD9D2B"
        },
        {
            "InvoiceID": "4F15A7F3BEE4435F9B69"
        },
        {
            "InvoiceID": "4FE504F64D5244D19E75"
        },
        {
            "InvoiceID": "50DBDC7C35FB4F74A5CC"
        },
        {
            "InvoiceID": "51E1D20619CD4B9B82BB"
        },
        {
            "InvoiceID": "522514AC877D4B28A3B1"
        },
        {
            "InvoiceID": "524A1A9C63794FFE8D12"
        },
        {
            "InvoiceID": "5296769AFCDD411A8D22"
        },
        {
            "InvoiceID": "52B8DB0010F84F0C951D"
        },
        {
            "InvoiceID": "52D06341D2884256B27E"
        },
        {
            "InvoiceID": "530C9AD166464478B4F2"
        },
        {
            "InvoiceID": "532D2D2EE1564C40B0F0"
        },
        {
            "InvoiceID": "543F60DE8DDE498B85FD"
        },
        {
            "InvoiceID": "5555A9096912444E91B8"
        },
        {
            "InvoiceID": "5714562EFAA24A1A9EDC"
        },
        {
            "InvoiceID": "571CDDC079DD459EB186"
        },
        {
            "InvoiceID": "5870F2BDDB9647439B37"
        },
        {
            "InvoiceID": "58CD0B23D14244DF8AC5"
        },
        {
            "InvoiceID": "58E6502F2B2048BBAF7B"
        },
        {
            "InvoiceID": "59CE0C6CF2824EA89763"
        }
    ]
}


[kranil7701]

Of course, the thing both Mike and I should probably mention is that the api will paginate the results, usual default result in one go is 100 although this is a setting that can be changed, so you need to use skip and take and include the total like this, if you want a complete listing

Code: Select all

https://api.jiwa.com.au/Queries/SalesOrderList?Include=Total&Skip=200&Take=100&Fields=InvoiceID&apikey=9vjoF4M5-gYVBWjoFyW9nlQieB9FhxpJkAsRJznB_Ok&format=json


Which will give you a result like this

Code: Select all

{
    "Offset": 200,
    "Total": 496,
    "Results": [
        {
            "InvoiceID": "0DD2B28456DE4B3B8884"
        },
        {
            "InvoiceID": "0ECC3AF4C6FA4D1E954D"
        },
        {
            "InvoiceID": "0F3198BF062245C2B1AB"
        },
        {
            "InvoiceID": "103B964B8E8B4B999FCE"
        },
        {
            "InvoiceID": "1080EBA08D984A17B37B"
        },
        {
            "InvoiceID": "10F552AAD81640D79C1D"
        },
        {
            "InvoiceID": "11CE2CB624A8488E9341"
        },
        {
            "InvoiceID": "13A03E0390AF495CB39F"
        },
        {
            "InvoiceID": "1446AF6C1BFA4ACF8AB1"
        },
        {
            "InvoiceID": "14CF9F98DCBD44ABB5C0"
        },
        {
            "InvoiceID": "1533D693492947A6A869"
        },
        {
            "InvoiceID": "16123B59BA5D46E1A00A"
        },
        {
            "InvoiceID": "1617B3F5C8E54E349C26"
        },
        {
            "InvoiceID": "179705C9A4A4427E94A1"
        },
        {
            "InvoiceID": "187041E13D2C481FAF60"
        },
        {
            "InvoiceID": "18F7BFBB1EF94994B93D"
        },
        {
            "InvoiceID": "191e840c266c410cb241"
        },
        {
            "InvoiceID": "19BC5F9213D348829AEB"
        },
        {
            "InvoiceID": "1A87FD4540EA40A6BA28"
        },
        {
            "InvoiceID": "1AA2A90E76814C398A09"
        },
        {
            "InvoiceID": "1C164C7F6CB348ACB2F6"
        },
        {
            "InvoiceID": "1C27C735B0EB4EF888EB"
        },
        {
            "InvoiceID": "1dc29f76142a42c298fe"
        },
        {
            "InvoiceID": "1DCDD6E198944AF29A84"
        },
        {
            "InvoiceID": "1E60DFF6E34543DA8765"
        },
        {
            "InvoiceID": "226FA52053E3404A90C7"
        },
        {
            "InvoiceID": "228041DEC7B540B1B35A"
        },
        {
            "InvoiceID": "232A129CD2C6425F915E"
        },
        {
            "InvoiceID": "2457A424C12B45D5899A"
        },
        {
            "InvoiceID": "2570277DFACC4F1180D4"
        },
        {
            "InvoiceID": "25F0A66578EF4B4D8319"
        },
        {
            "InvoiceID": "2604CE6BEEA146A5A3E8"
        },
        {
            "InvoiceID": "2629E3229C544B82A2FF"
        },
        {
            "InvoiceID": "26815559C79F4B3481B3"
        },
        {
            "InvoiceID": "268249522A71489ABC74"
        },
        {
            "InvoiceID": "26995FEC2FA14C048A98"
        },
        {
            "InvoiceID": "274BFA583BE04BA6A137"
        },
        {
            "InvoiceID": "288CCF62520A445D8843"
        },
        {
            "InvoiceID": "2A814485FAE645849755"
        },
        {
            "InvoiceID": "2AA47C878F654F13B15C"
        },
        {
            "InvoiceID": "2C4C9CCAA5724631A2CF"
        },
        {
            "InvoiceID": "2C972446997143D8A481"
        },
        {
            "InvoiceID": "2E18D27155574D5B90DC"
        },
        {
            "InvoiceID": "2EFBAE9400A342EE92DA"
        },
        {
            "InvoiceID": "2EFF043512C44EAEAC25"
        },
        {
            "InvoiceID": "310DE96E22FF4E6DAA66"
        },
        {
            "InvoiceID": "31C033802F3743979B4F"
        },
        {
            "InvoiceID": "32204517ABC248B1B659"
        },
        {
            "InvoiceID": "330A31F9BDFE421C9419"
        },
        {
            "InvoiceID": "338FC490F42F4EFD8BF9"
        },
        {
            "InvoiceID": "34068A3E410E4094B841"
        },
        {
            "InvoiceID": "34B2ED32259B46ABAE51"
        },
        {
            "InvoiceID": "34C7149A363941579828"
        },
        {
            "InvoiceID": "372F638FAEEB413182B3"
        },
        {
            "InvoiceID": "37A74BA6DECE471E9D04"
        },
        {
            "InvoiceID": "37E3AB267BA44392B923"
        },
        {
            "InvoiceID": "382ea53414fa4923b2ef"
        },
        {
            "InvoiceID": "3B4BF612AA3A44218D8B"
        },
        {
            "InvoiceID": "3CE373F15D1A4EFE9A40"
        },
        {
            "InvoiceID": "3D7376D583E14508AB00"
        },
        {
            "InvoiceID": "3F23CE008DC64C0599EE"
        },
        {
            "InvoiceID": "3FDE1082C8E2480EB90B"
        },
        {
            "InvoiceID": "40DA95AE144042C88C62"
        },
        {
            "InvoiceID": "411D7CFCFC294A4F9B1F"
        },
        {
            "InvoiceID": "418FC6740D36452FA885"
        },
        {
            "InvoiceID": "4225B703D2FC44808F0A"
        },
        {
            "InvoiceID": "44F3DDB277A44CCBA6F8"
        },
        {
            "InvoiceID": "4544F8E44D214C50BCDB"
        },
        {
            "InvoiceID": "45838EDACF204D01B7BF"
        },
        {
            "InvoiceID": "4623FF86729F4E5A94A8"
        },
        {
            "InvoiceID": "476679B8397343BD825A"
        },
        {
            "InvoiceID": "47C8136D7C19433DB6FA"
        },
        {
            "InvoiceID": "49CAD8D0E8864B58BAA2"
        },
        {
            "InvoiceID": "4B5AA0EB6A09424B9C59"
        },
        {
            "InvoiceID": "4C2EB909C87244F6A983"
        },
        {
            "InvoiceID": "4C3DEDB44A704A10AC05"
        },
        {
            "InvoiceID": "4CD34C33A5DE49938ABE"
        },
        {
            "InvoiceID": "4cd4cde3d3b045188876"
        },
        {
            "InvoiceID": "4D26BB2C31C348F392E3"
        },
        {
            "InvoiceID": "4E2592207FAB4F048556"
        },
        {
            "InvoiceID": "4EBD7107626641DD9D2B"
        },
        {
            "InvoiceID": "4F15A7F3BEE4435F9B69"
        },
        {
            "InvoiceID": "4FE504F64D5244D19E75"
        },
        {
            "InvoiceID": "50DBDC7C35FB4F74A5CC"
        },
        {
            "InvoiceID": "51E1D20619CD4B9B82BB"
        },
        {
            "InvoiceID": "522514AC877D4B28A3B1"
        },
        {
            "InvoiceID": "524A1A9C63794FFE8D12"
        },
        {
            "InvoiceID": "5296769AFCDD411A8D22"
        },
        {
            "InvoiceID": "52B8DB0010F84F0C951D"
        },
        {
            "InvoiceID": "52D06341D2884256B27E"
        },
        {
            "InvoiceID": "530C9AD166464478B4F2"
        },
        {
            "InvoiceID": "532D2D2EE1564C40B0F0"
        },
        {
            "InvoiceID": "543F60DE8DDE498B85FD"
        },
        {
            "InvoiceID": "5555A9096912444E91B8"
        },
        {
            "InvoiceID": "5714562EFAA24A1A9EDC"
        },
        {
            "InvoiceID": "571CDDC079DD459EB186"
        },
        {
            "InvoiceID": "5870F2BDDB9647439B37"
        },
        {
            "InvoiceID": "58CD0B23D14244DF8AC5"
        },
        {
            "InvoiceID": "58E6502F2B2048BBAF7B"
        },
        {
            "InvoiceID": "59CE0C6CF2824EA89763"
        }
    ]
}


Is their any way to get the list of bills and credit notes

[kranil7701]

Thanks Mike Sheen and Stuart Barnes
this information helps me. please let me know Is their any way to get the list of bills and credit notes.

[SBarnes]

Not sure what you mean by bills but credit notes would be

Code: Select all

https://api.jiwa.com.au/Queries/SO_Main?CreditNote=true&Include=Total&Fields=InvoiceID,CreditNote&apikey=9vjoF4M5-gYVBWjoFyW9nlQieB9FhxpJkAsRJznB_Ok&format=json


or

Code: Select all

https://api.jiwa.com.au/Queries/SalesOrderList?CreditNote=true&Include=Total&Fields=InvoiceID,CreditNote&apikey=9vjoF4M5-gYVBWjoFyW9nlQieB9FhxpJkAsRJznB_Ok&format=json


Which give

Code: Select all

{
    "Total": 7,
    "Results": [
        {
            "InvoiceID": "000000002400000002SF",
            "CreditNote": true
        },
        {
            "InvoiceID": "14CF9F98DCBD44ABB5C0",
            "CreditNote": true
        },
        {
            "InvoiceID": "274BFA583BE04BA6A137",
            "CreditNote": true
        },
        {
            "InvoiceID": "5714562EFAA24A1A9EDC",
            "CreditNote": true
        },
        {
            "InvoiceID": "86C37C974A3C4155A47A",
            "CreditNote": true
        },
        {
            "InvoiceID": "D6333A2B92A645A9BF30",
            "CreditNote": true
        },
        {
            "InvoiceID": "F1149BC9C9A24FD4A0B4",
            "CreditNote": true
        }
    ]
}


[kranil7701]

[quote="SBarnes"]Not sure what you mean by bills but credit notes would be

I mean is their any api end-point to retrieves the Bills records . I want all records not get by single Bills/BillID

[SBarnes]

/Queries/BM_Main for Bills

[Mike.Sheen]

I want all records not get by single Bills/BillID

We do not have a single route to get complete DTO's for any document, as even reading a single DTO for a document in full is expensive.

You can get a list of the ID's using the Queries routes which offer filtering, sorting and pagination, and then you need to use the ID to perform a single read.

It concerns me deeply that you seem to be wanting to read ALL sales orders and credit notes in full - this will be a major performance issue.