Attendance sheet

ensure var VarDurationAttendance kind: duration
  deploy: fixedOnDeploy
  unit: hours
  value: 8

ensure spreadsheet: AttendanceSheet
  withFields: ["InTime", "InTimeImage", "OutTime", "OutTimeImage", "Partition"]
  ofTypes: [bool, camera, bool, camera, text]
  removeRoleSet: [Owner]
  insertRoleSet: [Owner]
  updateRoleSet: [Employee]
  readAfterDurationVar: VarDurationAttendance

ensure partition DailyPartition
  assignPartitionField: Details.Partition
  formula: "
    let date = new Date();
    return date.toISOString().split('T')[0];"

ensure form EntityAttendance
  label: "Attendance"
ensure section: Details
ensure field InTime
  label: "In"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  captureLocation: true
  captureTime: true
  captureUser: false
  showAsCheckbox: true
  showCapturedValuesOnAside: ["captureTime", "captureLocation"]
ensure field InTimeImage
  label: "Image"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  placeHolder: "Image"
  showLabel: false
  captureLocation: true
  captureTime: true
ensure field OutTime
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  captureLocation: true
  captureTime: true
  captureUser: false
  showAsCheckbox: true
  showCapturedValuesOnAside: ["captureTime", "captureLocation"]
ensure field OutTimeImage
  label: "Image"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  placeHolder: "Image"
  showLabel: false
  captureLocation: true
  captureTime: true
ensure field Partition
  permissionMatrix: {
      'defaultPermission': 'invisible'
    }

ensure visibilityRule InTime
  condition: "<root>
      <stmt>InTime == ${d:InTime.true}</stmt>
    </root>"

ensure actionMapIfTrue Show
  comp: InTimeImage
  visibilityAction: visible
  visibilityActionOn: field

ensure actionMapIfTrue Enable
  comp: OutTime
  visibilityAction: enable
  visibilityActionOn: field

ensure actionMapIfFalse Hide
  comp: InTimeImage
  visibilityAction: invisible
  visibilityActionOn: field

ensure actionMapIfFalse Disable
  comp: OutTime
  visibilityAction: disable
  visibilityActionOn: field

ensure visibilityRule OutTime
  condition: "<root>
      <stmt>OutTime == ${d:OutTime.true}</stmt>
    </root>"

ensure actionMapIfTrue Show
  comp: OutTimeImage
  visibilityAction: visible
  visibilityActionOn: field

ensure actionMapIfFalse Hide
  comp: OutTimeImage
  visibilityAction: invisible
  visibilityActionOn: field

ensure form  OutputAbsentEmployee
ensure grid: EmployeeSet
ensure field Name kind: pickUser
  roleDataSource: [Employee]

ensure layoutGrid TableLayout kind: table
  showComps: [Name]

ensure layout ContentLayout kind: content
  direction: vertical
  contentPadding: thick
  flexCenter.gridLayouts: [EmployeeSet.TableLayout]

ensure report ReportAbsentEmployee kind: query
  outputForm: OutputAbsentEmployee
  fromSpreadsheets: [AttendanceSheet]

  // compound query
  neoQL: "WITH

    q1 AS (
      SELECT entUserId AS `employeeId`
      FROM `DevWorldDb`.`neome`.`ent_user`
      WHERE type = #{GetEntTable(${ctx:ent.id})} AND userId IS NOT NULL
    ),

    q2 AS (
        SELECT ${ctx:row.createdBy} AS `employeeId`
        FROM ${ss}
        WHERE ${ctx:row.type} = ${ss:AttendanceSheet}
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'year') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'year')
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'month') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'month')
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'day') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'day')
    )

    SELECT q1.employeeId AS ${out:EmployeeSet.Name}
    FROM q1
    EXCEPT
      SELECT q2.employeeId AS ${out:EmployeeSet.Name}
      FROM q2"

ensure form FilterVisit
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 field Divider kind: divider
ensure field LabelFilterEmployee kind: label
  label: "Filter Employee"
  bold: true
  textPattern: "Filter Employee"
ensure field Employee kind: pickUser
  roleDataSource: [Employee]

ensure form OutputAttendance
ensure grid: Attendance
ensure field Date kind: date
ensure field Employee kind: pickUser
  roleDataSource: [Employee]
ensure field In kind: bool
ensure field InTime kind: dateTime
ensure field InLocation kind: location
ensure field Out kind: bool
ensure field OutTime kind: dateTime
ensure field OutLocation kind: location
  captureMode: manual
ensure field TotalHours kind: decimal
  numberOfDigitsAfterPeriod: 2
ensure field OnLeave kind: bool
ensure field LeaveReason kind: paragraph

ensure layoutGrid TableLayout kind: table
  columnSizeSet: ["AutoSize"]
  showComps: [
      Date,
      Employee,
      In,
      InTime,
      InLocation,
      Out,
      OutTime,
      OutLocation,
      TotalHours,
      OnLeave,
      LeaveReason
    ]

ensure layout ReportLayout kind: content
  direction: vertical
  renderingMode: fullScreen
  contentPadding: thick
  flexCenter.gridLayouts: [Attendance.TableLayout]

ensure report ReportAttendance kind: query
  inputForm: FilterVisit
  outputForm: OutputAttendance
  fromSpreadsheets: [AttendanceSheet]

  // embedding JavaScript with NeoQL
  neoQL: "SELECT
        ${ctx:row.createdOn} AS ${out:Attendance.Date},
        ${ctx:row.createdBy} AS ${out:Attendance.Employee},
        ${ss:AttendanceSheet.Details.InTime} AS ${out:Attendance.In},
        ${ss:AttendanceSheet.Details.OutTime} AS ${out:Attendance.Out},
        IFMISSINGORNULL(((${ss:AttendanceSheet.Details.OutTime.captureTime} - ${ss:AttendanceSheet.Details.InTime.captureTime})/3600000),0) AS ${out:Attendance.TotalHours},
        ${ss:AttendanceSheet.Details.InTime.captureTime} AS ${out:Attendance.InTime},
        ${ss:AttendanceSheet.Details.OutTime.captureTime} AS ${out:Attendance.OutTime},
        ${ss:AttendanceSheet.Details.InTime.captureLocation} AS ${out:Attendance.InLocation},
        ${ss:AttendanceSheet.Details.OutTime.captureLocation} AS ${out:Attendance.OutLocation}
    FROM ${ss}
    WHERE
      ${ctx:row.type} = ${ss:AttendanceSheet}
      AND ${ctx:row.createdOn} >= ${in:Details.FromDate}
      AND ${ctx:row.createdOn} <= ${in:Details.ToDate}
      #{
        HAS_VALUE(${in:Details.Employee})
          ? 'AND ${ctx:row.createdBy} = ${in:Details.Employee}'
          : null}
    ORDER BY ${ctx:row.createdBy} ASC;"

ensure action AddAttendance kind: rowInsert
  icon: "LibraryAddRounded"
  spreadsheet: AttendanceSheet
  chatBubbleHeader.subTitle: "Submit your today's Attendance before expiry"
  chatBubbleHeader.title: "Today's Attendance"
  sendMessageToInbox: true

ensure action ReportAttendance kind: report
  icon: "ListAltRounded"
  report: ReportAttendance
  outputFormContentLayout: ReportLayout
  sendMessageToInbox: false

ensure spreadsheet AttendanceSheet
ensure layoutSpreadsheet TableLayout kind: table
  columnSizeSet: ["AutoSize"]
  showComps: [$CreatedBy, InTime, InTimeImage, OutTime, OutTimeImage]

ensure action EditAttendanceSheet kind: spreadsheetEditor
  icon: "ViewListRounded"
  spreadsheet: AttendanceSheet
  layoutSpreadsheet: TableLayout

ensure group MyPortal
  pinnedActions: [AddAttendance, ReportAttendance]
  pinnedActionSetMobile: [AddAttendance]
  actionPermission: {
      'AddAttendance': {
        'menuGroup': '1',
        'roles': [
          'Owner'
        ]
      },
      'EditAttendanceSheet': {
        'menuGroup': '2',
        'roles': [
          'Owner'
        ]
      },
      'ReportAttendance': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      }
    }