Attendance sheet

// duration variable for attendance module

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

// this spreadsheet stores information of employees attendance

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();
    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

// output form for ReportAbsentEmployee report

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]

// report to get data about absent employees
 
ensure report ReportAbsentEmployee kind: query 
  outputForm: OutputAbsentEmployee 
  fromSpreadsheets: [AttendanceSheet] 
  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"

// input form for ReportAttendance report

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]

// output form for ReportAttendance report

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]

// report to get data about employees attendece
 
ensure report ReportAttendance kind: query 
  inputForm: FilterVisit 
  outputForm: OutputAttendance 
  fromSpreadsheets: [AttendanceSheet] 
  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;"

// this action allows employee to enter attendance

ensure action AddAttendance kind: rowInsert 
  icon: "LibraryAddRounded" 
  spreadsheet: AttendanceSheet 
  chatBubbleHeader.subTitle: "Submit your todays Attendance before expiry" 
  chatBubbleHeader.title: "Todays Attendance" 
  sendMessageToInbox: true

// owner can check the attendance report for given date range

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

// owner can view and edit attendance sheet

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

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