Backend
1. Variables
Declare variables to be used later in the script.
// variable sequence
ensure var VarInvoiceSeq kind: sequence
deploy: fixedOnDeploy
value: 1
// pattern variable for invoice id identifier
ensure var VarTextPatternFormInvoiceLinkInvoiceId kind: text
deploy: fixedOnDeploy
value: "INV-${p}"
paramSet: ["p"]2. GetInvoiceComposite
💡
Every report takes one input form and provides one output form.
Let us declare one Input form to filter invoice and one output form to receive invoice.
GetInvoiceComposite report is composed of GetInvoiceCustomerInfo and GetInvoiceOrders where each report takes the same input form and provides same output form.
a. Input form
Intent of the FilterInvoice is creating a filter to find orders between from date and to date for a given office.
ensure form FilterInvoice
//****************
// Details section
//****************
ensure section: Details
// from date
ensure field FromDate kind: date
autoFocus: false
required: true
defaultValue: "startOfMonth"
// to date
ensure field ToDate kind: date
autoFocus: false
required: true
defaultValue: "endOfMonth"
// picker of Office
ensure field OfficeRef kind: ref
spreadsheet: OfficeMaster
copyFieldMap: {
'OfficeMasterRowId': '$RowId',
'Name': 'Name'
}
layoutSpreadsheet: ListLayoutb. Output form
OutputInvoice form provides structure to invoice output.
ensure form OutputInvoice
//****************
// Details section
//****************
ensure section: Details
// InvoiceId field with INV-### pattern
ensure field InvoiceId kind: identifier
setOnSend: true
textPatternVar: {
'var': 'VarTextPatternFormInvoiceLinkInvoiceId',
'paramSet': [
'${var:VarInvoiceSeq}'
]
}
// other fields
ensure field FromDate kind: date
ensure field ToDate kind: date
ensure field InvoiceDate kind: date defaultValue: "now"
ensure field TeaPrice kind: number prefix: "₹ "
ensure field CoffeePrice kind: number prefix: "₹ "
ensure field OfficeRowId kind: rowId
ensure field OfficeName kind: text
ensure field OfficeNo kind: text
ensure field Mobile kind: mobileNumber
//**************
// Grid of Items
//**************
ensure grid: Items
ensure field Date kind: date
ensure field Time kind: pickText sourceVar: VarSetOfTextTime
ensure field TotalTea kind: number
ensure field TotalCoffee kind: number
ensure field TotalPrice kind: number prefix: "₹ "
//****************
// Summary section
//****************
ensure section: Summary
ensure field TotalBill kind: number prefix: "₹ "
ensure field GST kind: number
ensure field GrandTotal kind: number prefix: "₹ "
ensure field PaymentStatus kind: paymentStatus defaultValue: pending
// QR code field
ensure field PaymentQR kind: showCode
label: "Payment QR"
codeType: qrCode
showLabel: false
ensure field PaymentLink kind: hyperlink
ensure field InvoiceLink kind: hyperlink
ensure field PaymentQR defaultField: PaymentLink
ensure field InvoicePdf kind: document fileTypeSet: ["any"]Attach formulas to the form to calculate derived values.
ensure formula TotalPrizeFormula
assignToField: TotalPrice
formula: "(${f:TotalTea} * ${f:TeaPrice}) + (${f:TotalCoffee} * ${f:CoffeePrice})"
ensure formula TotalBillFormula
assignToField: TotalBill
formula: "SUM(${f:TotalPrice})"
// hardcoded to 18% Goods and Services Tax. Alternatively this can also be
// declared through a variable
ensure formula GSTFormula
assignToField: GST
formula: "${f:TotalBill} * 0.18"
ensure formula GrandTotalFormula
assignToField: GrandTotal
formula: "${f:TotalBill} + ${f:GST}"c. GetInvoiceCustomerInfo
This report fetches customer information using NeoQL. Neome's query language.
// in the query below,
// * in: refers to the current FilterInvoice
// * out: refers to the OutputInvoice that would be generated
// * ss: refers to the SpreadSheet
// * ctx: refers to the context information available when executing the query
ensure report GetInvoiceCustomerInfo kind: query
inputForm: FilterInvoice
outputForm: OutputInvoice
fromSpreadsheets: [OfficeMaster]
neoQL: "select
${in:Details.FromDate} as ${out:Details.FromDate},
${in:Details.ToDate} as ${out:Details.ToDate},
${ctx:row.id} as ${out:Details.OfficeRowId},
${ss:OfficeMaster.Details.Name} as ${out:Details.OfficeName},
${ss:OfficeMaster.Details.OfficeNumber} as ${out:Details.OfficeNo},
${ss:OfficeMaster.Details.MobileNumber} as ${out:Details.Mobile},
${ss:OfficeMaster.Details.TeaPrice} as ${out:Details.TeaPrice},
${ss:OfficeMaster.Details.CoffeePrice} as ${out:Details.CoffeePrice},
MILLIS(CLOCK_LOCAL()) as ${out:Details.InvoiceDate}
from ${ss}
where ${ctx:row.type} = ${ss:OfficeMaster} and
${ctx:row.id} = ${in:Details.OfficeMasterRowId}"d. GetInvoiceOrders
This report fetches information of customer's order for given date range using NeoQL, Neome's query language.
// in the query below,
// * in: refers to the current FilterInvoice
// * out: refers to the OutputInvoice that would be generated
// * ss: refers to the SpreadSheet
// * ctx: refers to the context information available when executing the query
ensure report GetInvoiceOrders kind: query
inputForm: FilterInvoice
outputForm: OutputInvoice
fromSpreadsheets: [OrderBook]
neoQL: "select
${ss:OrderBook.Order.Date} as ${out:Items.Date},
${ss:OrderBook.Order.Time} as ${out:Items.Time},
${ss:OrderBook.Order.TeaCount} as ${out:Items.TotalTea},
${ss:OrderBook.Order.CoffeeCount} as ${out:Items.TotalCoffee},
${ss:OrderBook.Order.Total} as ${out:Items.TotalPrice}
from ${ss}
where ${ctx:row.type} = ${ss:OrderBook}
and ${ss:OrderBook.Order.Date} >= ${in:Details.FromDate}
and ${ss:OrderBook.Order.Date} <= ${in:Details.ToDate}
and ${ss:OrderBook.Office.OfficeMasterRowId} = ${in:Details.OfficeMasterRowId}
order by ${ss:OrderBook.Order.Date}"e. Composite report
This report merges output of GetInvoiceOrders and GetInvoiceCustomerInfo reports.
ensure report GetInvoiceComposite kind: composite
inputForm: FilterInvoice
outputForm: OutputInvoice
mergeReports: [GetInvoiceCustomerInfo, GetInvoiceOrders]3. GetInvoice
Each time GetInvoiceComposite is called, Neome recalculates the entire report. If a deep link is shared with hundreds of users, the report is repeatedly regenerated, causing a heavy load on Neome.
A better approach is to cache the report based on filter parameters and serve the saved version. Users can refresh the report manually using the refresh button in GetInvoice, which regenerates the report and updates the cache.
// InvoiceHistory spreadsheet to save OutputInvoice
ensure spreadsheet InvoiceHistory
form: OutputInvoice
readRoleSet: [Owner]
removeRoleSet: [Owner]
updateRoleSet: [Owner]
searchables: [OfficeName, OfficeNo, Mobile]
queryables : [PaymentStatus]
// bind everything to GetInvoice report
ensure report GetInvoice kind: mapper
label: "Get Invoice"
inputForm: FilterInvoice
outputForm: OutputInvoice
mappedReport: GetInvoiceComposite
saveToSpreadsheet: InvoiceHistory