Backend

// this form will be used for filter out the dashboard information

ensure form FilterAggregated
ensure composite Details kind: section
ensure field FromDate kind: date
  autoFocus: false
  required: true
  defaultValue: "startOfMonth"
ensure field ToDate kind: date
  autoFocus: false
  required: true
  defaultValue: "endOfMonth"

ensure var VarParagraphAggregatedOrderChatPattern kind: paragraph
  deploy: fixedOnDeploy
  value: "Hello ${p1}, Aggregated order amount: ₹${p2}, Dated ${p3}."
  paramSet: ["p1", "p2", "p3"]

ensure form OutputAggregated
  allowToPrintForm: true
ensure grid: SalesByCustomer
ensure field Office kind: text
ensure field TotalTea kind: number
ensure field TotalCoffee kind: number
ensure field TotalAmount kind: number
  prefix: "₹"

ensure section: Summary
ensure field Date kind: date
  defaultValue: "now"
ensure field FromDate kind: date
ensure field ToDate kind: date
ensure field TotalSales kind: number
  prefix: "₹"
ensure field TotalTea kind: number
ensure field TotalCoffee kind: number

ensure form OutputAggregated
  chatPatternVar: {
      'var': 'VarParagraphAggregatedOrderChatPattern',
      'paramSet': [
        '${f:SalesByCustomer.Office}',
        '${f:SalesByCustomer.TotalAmount}',
        '${f:Summary.Date}'
      ]
    }

ensure formula TotalAmountFormula
  assignToField: TotalSales
  formula: "SUM(${f:SalesByCustomer.TotalAmount})"

ensure formula TotalCoffeeFormula
  assignToField: Summary.TotalCoffee
  formula: "SUM(${f:SalesByCustomer.TotalCoffee})"

ensure formula TotalTeaFormula
  assignToField: Summary.TotalTea
  formula: "SUM(${f:SalesByCustomer.TotalTea})"

// this reports fetches aggregated summary for OrderBook for given date range

ensure report AggregatedSummary kind: query
  inputForm: FilterAggregated
  outputForm: OutputAggregated
  fromSpreadsheets: [OrderBook]
  neoQL: "select
          ${in:Details.FromDate} as ${out:Summary.FromDate},
          ${in:Details.ToDate} as ${out:Summary.ToDate},
          MILLIS(CLOCK_LOCAL()) as ${out:Summary.Date}
      from ${ss}
      where ${ctx:row.type} = ${ss:s2}
      and ${ss:s2.Order.Date} >= ${in:Details.FromDate}
      and ${ss:s2.Order.Date} <= ${in:Details.ToDate}
      group by ${ss:s2.Office.Name}"

// this report fetches aggregated sales group by on customers from OrderBook

ensure report AggregatedSalesByCustomer kind: query
  inputForm: FilterAggregated
  outputForm: OutputAggregated
  fromSpreadsheets: [OrderBook]
  neoQL: "select
        ${ss:s2.Office.Name} as ${out:SalesByCustomer.Office},
        sum(${ss:s2.Order.TeaCount}) as ${out:SalesByCustomer.TotalTea},
        sum(${ss:s2.Order.CoffeeCount}) as ${out:SalesByCustomer.TotalCoffee},
        sum(${ss:s2.Order.Total}) as ${out:SalesByCustomer.TotalAmount}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    and ${ss:s2.Order.Date} >= ${in:Details.FromDate}
    and ${ss:s2.Order.Date} <= ${in:Details.ToDate}
    group by ${ss:s2.Office.Name}"

// this reports combines AggregatedSalesByCustomer and AggregatedSummary reports

ensure report AggregatedReport kind: composite
  inputForm: FilterAggregated
  outputForm: OutputAggregated
  mergeReports: [AggregatedSalesByCustomer, AggregatedSummary]

ensure var VarFunctionGetMonthName kind: function
  deploy: fixedOnDeploy
  value: "function(MonthInNumber : number) : text
    {
        const monthNames = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
        return monthNames[MonthInNumber - 1];
    }"

// this form will be used for show the dashboard information
ensure form OutputDashboard
ensure grid: TeaVsCoffee
ensure field Date kind: date
ensure field MonthInNumber kind: number
ensure field Month kind: text
ensure field TotalTea kind: number
  label: "Tea"
  prefix: "₹"
ensure field TotalCoffee kind: number
  label: "Coffee"
  prefix: "₹"
ensure field TotalSales kind: number
  label: "Total"
  prefix: "₹"

ensure grid: PendingPayments
ensure field InvoiceId kind: text
ensure field InvoiceDate kind: date
  defaultValue: "now"
ensure field OfficeName kind: text
ensure field OfficeNo kind: text
ensure field Amount kind: number
  prefix: "₹"

ensure grid: TopCustomerSet
ensure field Name kind: text
ensure field Amount kind: number
  prefix: "₹"

ensure section: Details
ensure field ShowAggregatedReport kind: button
  buttonKind: normal
  targetType: triggerAction

ensure formula FormulatUon29
  assignToField: TeaVsCoffee.Month
  formula: "${var:VarFunctionGetMonthName}(${f:MonthInNumber})"

// this report fetches the top customer information

ensure report DashboardTopCustomers kind: query
  outputForm: OutputDashboard
  fromSpreadsheets: [OrderBook]
  neoQL: "select
        ${ss:s2.Office.Name} as ${out:TopCustomerSet.Name},
        sum(${ss:s2.Order.Total}) as ${out:TopCustomerSet.Amount}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    group by ${ss:s2.Office.Name}
    order by sum(${ss:s2.Order.Total}) DESC
    LIMIT 5"

// this report fetches the monthly sale data
ensure report DashboardMonthlySale kind: query
  outputForm: OutputDashboard
  fromSpreadsheets: [OrderBook]
  neoQL: "select
      ${ss:s2.Order.Date} as ${out:TeaVsCoffee.Date},
      ${ss:s2.Order.Total} as ${out:TeaVsCoffee.TotalSales},
      (${ss:s2.Office.TeaPrice} * ${ss:s2.Order.TeaCount}) as ${out:TeaVsCoffee.TotalTea},
      (${ss:s2.Office.CoffeePrice} * ${ss:s2.Order.CoffeeCount}) as ${out:TeaVsCoffee.TotalCoffee},
      DATE_PART_MILLIS(${ss:s2.Order.Date}, 'month') as ${out:TeaVsCoffee.MonthInNumber}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    order by ${ss:s2.Order.Date} ASC;"

ensure var VarConditionPendingPaymentReport kind: condition
  deploy: fixedOnDeploy
  sourceForm: OutputInvoice
  value: "<root>
      <stmt>${f:Summary.PaymentStatus} != ${d:PaymentStatus.paid}</stmt>
    </root>"

ensure var VarMappingPendingPayment kind: mapping
  deploy: fixedOnDeploy
  fromForm: OutputInvoice
  toForm: OutputDashboard
  toGrid: PendingPayments
  fieldMappingMap: {
      'map': {
        '${f:Details.InvoiceDate}': 'PendingPayments.InvoiceDate',
        '${f:Details.OfficeName}': 'PendingPayments.OfficeName',
        '${f:Details.OfficeNo}': 'PendingPayments.OfficeNo',
        '${f:Summary.GrandTotal}': 'PendingPayments.Amount',
        '${f:Details.InvoiceId}': 'PendingPayments.InvoiceId'
      }
    }

ensure report DashboardPendingPayments kind: spreadsheet
  outputForm: OutputDashboard
  fromSpreadsheet: InvoiceHistory
  filterConditionVar: VarConditionPendingPaymentReport
  outputFormMappingVar: VarMappingPendingPayment

ensure report Dashboard kind: composite
  outputForm: OutputDashboard
  mergeReports: [DashboardMonthlySale, DashboardPendingPayments, DashboardTopCustomers]