Reports

ensure form FilterMonthly
ensure section: Details
ensure field LabelFilterDate kind: label
  label: "Filter Date"
  bold: true
  textPattern: "Filter Date"
ensure field FromDate kind: date
  defaultValue: "startOfMonth"
ensure field ToDate kind: date
  defaultValue: "endOfMonth"

ensure form OutputMonthlySales

// details grid
ensure grid: Details
ensure field CustomerName kind: text label: "Customer Name"
ensure field NumberOfOrder kind: number label: "No. of Order"
ensure field TotalBill kind: number label: "Total Bill" prefix: "₹ "

ensure layoutGrid MonthlySalesTable kind: table
  columnSizeSet: ["Flex", "AutoSize"]
  showComps: [CustomerName, NumberOfOrder, TotalBill]

// summary section
ensure section: Summary
ensure field TotalSale kind: number prefix: "₹ "
ensure field TotalOrders kind: number
ensure field OrdersCancelled kind: number
ensure field NewCustomers kind: number
ensure field LastMonthSale kind: number prefix: "₹ "
ensure field TotalAmount kind: decimal

// formula for total amount
ensure formula TotalAmount
  assignToField: Summary.TotalAmount
  formula: "SUM(${f:Details.TotalBill})"

// report layout
ensure layout ReportLayout kind: content
  direction: vertical
  contentPadding: thick
  showPaddingSet: ["bottom"]
  flexCenter.showPaddingSet: ["top", "bottom", "left", "right"]
  flexCenter.padding: thick
  end.fields: [
    TotalSale,
    TotalOrders,
    OrdersCancelled,
    NewCustomers,
    LastMonthSale,
    TotalAmount
  ]
  end.showBorderSet: ["top"]
  end.showPaddingSet: ["top", "bottom", "left", "right"]
  end.padding: thick
  flexCenter.gridLayouts: [Details.MonthlySalesTable]

// fill relevant columns of OutputMonthlySales
ensure report ReportMonthlyOrders kind: query
  label: "Monthly Order Report"
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "SELECT
      ${ss:OrderBook.Details.CustomerName} AS ${out:Details.CustomerName},
      COUNT(*) as ${out:Details.NumberOfOrder},
      SUM(${ss:OrderBook.Summary.TotalBill}) AS ${out:Details.TotalBill}
    FROM ${ss}
    WHERE ${ctx:row.type} = ${ss:OrderBook}
      AND ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
      AND ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}
    GROUP BY ${ss:OrderBook.Details.CustomerName}"

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesLastMonthSale kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
      COALESCE(sum(${ss:OrderBook.Summary.TotalBill}), 0)  as ${out:Summary.LastMonthSale}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook}
      and ${ctx:row.createdOn} >= DATE_ADD_MILLIS(${in:Details.FromDate} , -30, 'day')
      and ${ctx:row.createdOn} < ${in:Details.FromDate}"

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesNewCustomers kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [VisitBook, OrderBook]
  neoQL: "Select
      count(*) as ${out:Summary.NewCustomers}
    from ${ss} as main
    where ${ctx:row.type} = ${ss:OrderBook}
      and ${ctx:row.createdOn} >= ${in:Details.FromDate}
      and ${ctx:row.createdOn} <= ${in:Details.ToDate}
      and ${ss:OrderBook.Details.VisitRowId} NOT IN  (select distinct ${ctx:row.id}
    from ${ss} as sub
    where ${ctx:row.type} = ${ss:VisitBook}
      and ${ctx:row.createdOn} <= ${in:Details.ToDate})
    group by ${ss:OrderBook.Details.VisitRowId}"

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesOrderCancelled kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
            count(*) as ${out:Summary.OrdersCancelled}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook}
    and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
    and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}
    and ${ss:OrderBook.Summary.OrderStatus.optionId} == '5'"

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesTotalOrders kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
            count(*) as ${out:Summary.TotalOrders}
        from ${ss}
        where ${ctx:row.type} = ${ss:OrderBook} and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
        and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}"

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesTotalAmount kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
            sum(${ss:OrderBook.Summary.TotalBill}) as ${out:Summary.TotalSale}
        from ${ss}
        where ${ctx:row.type} = ${ss:OrderBook} and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
        and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}"

// combine all the reports into OutputMonthlySales
ensure report MonthlySales kind: composite
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  mergeReports: [
    MonthlySalesTotalAmount,
    MonthlySalesTotalOrders,
    MonthlySalesOrderCancelled,
    MonthlySalesNewCustomers,
    MonthlySalesLastMonthSale,
    ReportMonthlyOrders
  ]

ensure action ReportMonthlySales kind: report
  label: "Report Monthly Sales"
  icon: "ListAltRounded"
  report: MonthlySales
  outputFormContentLayout: ReportLayout

ensure group MyVisits
  pinnedActions: [AddVisit, AddOrder, EditOrderBook, ReportVisit, ReportMonthlySales]
  pinnedActionSetMobile: [AddVisit, AddOrder]
  actionPermission: {
      'AddVisit': {
        'menuGroup': '1',
        'roles': [
          'Salesman'
        ]
      },
      'AddOrder': {
        'menuGroup': '1',
        'roles': [
          'Salesman'
        ]
      },
      'EditVisitSheet': {
        'menuGroup': '2',
        'roles': [
          'Owner'
        ]
      },
      'EditOrderBook': {
        'menuGroup': '2',
        'roles': [
          'Owner'
        ]
      },
      'ReportVisit': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      },
      'ReportMonthlySales': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      }
    }