Reports

// input form for report ReportMonthlyOrders

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"

// output form for report ReportMonthlyOrders

ensure form OutputMonthlySales 
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]

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 

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

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]

// report for monthly order data

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}"

// report for last month sales

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}"

// report for new customer sales

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}"

// report for cancelled orders

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'"

// report for total orders

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}"

// report for total amount for monthly sales

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}"

// report for monthly sale

ensure report MonthlySales kind: composite 
  inputForm: FilterMonthly 
  outputForm: OutputMonthlySales
  mergeReports: [MonthlySalesTotalAmount, MonthlySalesTotalOrders, MonthlySalesOrderCancelled, MonthlySalesNewCustomers, MonthlySalesLastMonthSale, ReportMonthlyOrders]

// this action will call monthly sale report

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

// this group has all actions those can be performed to get monthly sales

ensure group MyVisits 
  pinnedActions: [AddVisit, AddOrder, ViewVisitSheet, ViewOrderBook, CallReportVisit, CallReportMonthlySales] 
  pinnedActionSetMobile: [AddVisit, AddOrder] 
  actionPermission: {
      'CallReportMonthlySales': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      }
    }