in my mongodb database when employee marked attendance it will create document in the database collection but if employee is not marking attendance no records is creating i want to find monthly attendance of employee on which days he is not marking the attendance and which days he marked attendance basically i want to create a monthly timesheet of employee

here is my mongoose model

const mongoose = require("mongoose");

const Schema = mongoose.Schema;

const attendanceSchema = mongoose.Schema(
  {
    date: {
      type: Date,
    },
    check_in: {
      type: Date,
    },
    check_in_remarks: {
      type: String,
    },
    late_time: {
      type: String,
    },
    check_out: {
      type: Date,
    },
    check_out_remarks: {
      type: String,
    },
    early_off: {
      type: String,
    },
    over_time: {
      type: String,
    },
    totalHours: {
      type: Number,
    },
    status: {
      type: String,
      enum: ["In","late", "half-day", "absent", "present"],
    },
    user: {
      type: Schema.Types.ObjectId,
      ref: "Employee",
    },
  },
  {
    timestamps: true,
  }
);

module.exports = mongoose.model("Attendance", attendanceSchema);

and here is my document details

{
        "_id": "63904934763bb94e677e6692",
        "date": "Wed Dec 07 2022 05:00:00 GMT 0500 (Pakistan Standard Time)",
        "check_in": "2022-12-07T10:07:00.000Z",
        "check_in_remarks": "",
        "late_time": "00:07:00",
        "status": "present",
        "user": "638869649c443988469c151f",
        "createdAt": "2022-12-07T08:05:08.992Z",
        "updatedAt": "2022-12-07T12:03:53.457Z",
        "__v": 0,
        "check_out": "2022-12-07T18:07:00.000Z",
        "check_out_remarks": "",
        "early_off": "00:53:00",
        "over_time": "",
        "totalHours": 28800
    }

CodePudding user response：

Keeping date in string maybe not the best option , but if you dont have option to change the schema this may give you some ideas:

db.collection.aggregate([
{
 $match: {
  user: "testUser",
  check_in: {
    $regex: "^2022-12"
   }
  }
  },
 {
  "$project": {
  user: 1,
  "date": {
    "$toInt": {
      "$substrCP": [
        "$check_in",
        8,
        2
      ]
     }
   }
  }
 },
{
 $group: {
  _id: "$user",
  dates: {
    $addToSet: {
      day: "$date"
    }
   }
 }
},
{
 $project: {
  dates: {
    $map: {
      input: {
        $range: [
          1,
          31
        ]
      },
      as: "day",
      in: {
        $let: {
          vars: {
            dayIndex: {
              "$indexOfArray": [
                "$dates.day",
                "$$day"
              ]
            }
          },
          in: {
            $cond: {
              if: {
                $ne: [
                  "$$dayIndex",
                  -1
                ]
              },
              then: {
                day: "$$day",
                presence: "Present"
              },
              else: {
                day: "$$day",
                presence: "Abs"
              }
            }
          }
        }
      }
    }
  }
 }
}
])

Explained:

1. Match the user month
2. Extract date of month from the check_in string and convert to Int
3. Group/addToSet(to form unique array with days of presence) the dates where user checked in
4. $map present days with $range generated days list , $range list can be updated to range based on month ...

Playground