def AddWorksheet(user, year, month, workbook):
# Get all timesheets for the user
timesheets = Context.SelectWithParams({
"Query":
"From T In Timesheet Where T.UserDetail.Username = @Username And :Year(T.BeginTime) = @YearFilter And :Month(T.BeginTime) = @MonthFilter Order By T.BeginTime Select T",
"@Username": user.Username,
"@YearFilter": Decimal(year),
"@MonthFilter": Decimal(month)
})
# Get holidays for the corresponding month
holidays = Context.SelectWithParams({
"Query":
"From H In LegalHoliday Where :Year(H.LegalHolidayDate) = @YearFilter And :Month(H.LegalHolidayDate) = @MonthFilter Order By H.LegalHolidayDate Select H",
"@YearFilter": Decimal(year),
"@MonthFilter": Decimal(month)
})
# Add sheet for user
workbook.Worksheets["Tabelle1"].Copy(Before=workbook.Worksheets[1])
ws = workbook.Worksheets[1]
ws.Name = user.Lastname + ", " + user.Firstname
ws.Activate()
rowIndex = 2
totalWorkingHours = 0
hoursPerDay = user.WeeklyHoursOfWork / 5
prevDay = 0
# Loop over all days of the month
for currentDay in range(
1,
DateTime(year, month, 1).AddMonths(1).AddDays(-1).Day + 1):
currentDayDate = DateTime(year, month, currentDay)
dailyTimesheets = Enumerable.Where(
timesheets, lambda t: t.BeginTime.Day == currentDay)
if Enumerable.Count(dailyTimesheets) > 0:
# There are timesheets for this day
for timesheet in dailyTimesheets:
# Add row for timesheet
HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1],
holidays)
if prevDay != timesheet.BeginTime.Day:
# Don't repeat day index for mulitple rows of the same day
ws.Cells[rowIndex, 1].Value2 = timesheet.BeginTime.Day
ws.Cells[rowIndex, 2].Value2 = timesheet.BeginTime
ws.Cells[rowIndex, 3].Value2 = timesheet.EndTime
ws.Cells[rowIndex,
4].Value2 = timesheet.DurationInHours / 24
ws.Cells[rowIndex, 5].Value2 = timesheet.Description
totalWorkingHours = totalWorkingHours + timesheet.DurationInHours
prevDay = timesheet.BeginTime.Day
rowIndex = rowIndex + 1
else:
# There are no timesheets for this day
HandleRowColor(currentDayDate, ws.Cells[rowIndex, 1], holidays)
ws.Cells[rowIndex, 1].Value2 = currentDay
rowIndex = rowIndex + 1
# Add sums
ws.Cells[rowIndex, 2].Value2 = 'Gesamt:'
ws.Cells[rowIndex, 4].Value2 = (totalWorkingHours) / 24
rowIndex = rowIndex + 1
def IsDayOff(day, holidays):
return day.DayOfWeek == DayOfWeek.Saturday or day.DayOfWeek == DayOfWeek.Sunday \
or Enumerable.Count(holidays, lambda h: h.LegalHolidayDate.Day == day.Day) > 0