From c1a94d4465f9ca0aa460d4a78322125ef863795b Mon Sep 17 00:00:00 2001 From: Emilie Zawadzki Date: Fri, 13 Jan 2017 19:05:18 +0100 Subject: [PATCH] [Timesheet] : export timesheet [WIP] --- app/organization/network/admin.py | 14 +- app/organization/network/api.py | 49 +++- .../commands/export-ircam-timesheet-xls.py | 230 ++++++++++++++++ app/organization/network/tests.py | 254 +++++++++++++++++- app/organization/network/urls.py | 1 + app/organization/network/utils.py | 193 +++++++++++++ app/organization/network/views.py | 12 + requirements.txt | 1 + 8 files changed, 750 insertions(+), 4 deletions(-) create mode 100644 app/organization/network/management/commands/export-ircam-timesheet-xls.py diff --git a/app/organization/network/admin.py b/app/organization/network/admin.py index 85e08a71..4da1d9a3 100644 --- a/app/organization/network/admin.py +++ b/app/organization/network/admin.py @@ -32,7 +32,7 @@ from organization.pages.models import * from organization.core.admin import * from organization.pages.admin import PageImageInline, PageBlockInline, PagePlaylistInline, DynamicContentPageInline, PageRelatedTitleAdmin from organization.shop.models import PageProductList - +from django.http import HttpResponse class OrganizationAdminInline(StackedDynamicInlineAdmin): @@ -263,6 +263,7 @@ class TrainingLevelAdmin(BaseTranslationModelAdmin): model = TrainingLevel + class TrainingSpecialityAdmin(BaseTranslationModelAdmin): model = TrainingSpeciality @@ -273,10 +274,14 @@ class TrainingTopicAdmin(BaseTranslationModelAdmin): model = TrainingTopic + + class PersonActivityTimeSheetAdmin(BaseTranslationModelAdmin): model = PersonActivityTimeSheet list_display = ['person', 'activity', 'year', 'month', 'project', 'work_package', 'percentage', 'accounting', 'validation'] list_filter = ['activity__person', 'year', 'project'] + actions = ['export_xls',] + def person(self, instance): return instance.activity.person @@ -284,6 +289,13 @@ class PersonActivityTimeSheetAdmin(BaseTranslationModelAdmin): wk_list = [str(wk.number) for wk in instance.work_packages.all()] return ",".join(wk_list) + def export_xls(self, request, queryset): + xls = TimesheetXLS(queryset) + return xls.write() + export_xls.short_description = "Export person timesheets" + + + admin.site.register(OrganizationLinked, OrganizationLinkedAdmin) admin.site.register(Organization, OrganizationAdmin) diff --git a/app/organization/network/api.py b/app/organization/network/api.py index efcc2ac5..268b5c8b 100644 --- a/app/organization/network/api.py +++ b/app/organization/network/api.py @@ -34,7 +34,7 @@ def get_inactive_persons(): def get_leave_periods(date_from, date_to, person_external_id): - leave_periods = figgo_request('api/leaves?date=between,'+date_from+','+date_to+'&fields=owner.name,owner.login,owner.mail,owner.matricule,duration,name,date,status,leaveScope&owner.id='+str(person_external_id)) + leave_periods = figgo_request('api/leaves?date=between,'+str(date_from)+','+str(date_to)+'&fields=owner.name,owner.login,owner.mail,owner.matricule,duration,name,date,status,leaveScope&owner.id='+str(person_external_id)) leave_periods = leave_periods.json() return leave_periods['data'] @@ -69,9 +69,54 @@ def get_leave_days(date_from, date_to, person_external_id): days_dict = increment_day(key_pm, days_dict) return days_dict + +def get_leave_days_per_month(date_from, date_to, person_external_id): + """Calculate the number of validated leaving days. + It takes in account half days. + Return a dictionary of half days not worked in a week. + Example : + { + "wednesday_am": 2, + "monday_pm": 1, + "friday_am": 2, + "thursday_am": 3, + } + """ + leave_periods = get_leave_periods(date_from, date_to, person_external_id) + days_dict = {} + for leave_period in leave_periods: + leave_date = dateutil.parser.parse(leave_period['date']) + month_key = leave_date.month + # if leave period has been validated + if leave_period['status'] == 1: + day_week = dateutil.parser.parse(leave_period['date']).isoweekday() + # morning or evening + if leave_period['leaveScope'] == 'PM' or leave_period['leaveScope'] == 'AM': + day_key = WEEK_DAYS[day_week]+"_"+leave_period['leaveScope'].lower() + days_dict = increment_day_per_month(month_key, day_key, days_dict) + # whole day = morning + evening + if leave_period['leaveScope'] == 'ALL': + key_am = WEEK_DAYS[day_week]+"_am" + days_dict = increment_day_per_month(month_key, key_am, days_dict) + key_pm = WEEK_DAYS[day_week]+"_pm" + days_dict = increment_day_per_month(month_key, key_pm, days_dict) + return days_dict + + +def increment_day_per_month(month_key, day_key, dt): + if month_key in dt: + if day_key in dt[month_key]: + dt[month_key][day_key] += 1 + else : + dt[month_key][day_key] = 1 + else : + dt[month_key] = {} + + return dt + + def increment_day(key, dt): if key in dt: dt[key] += 1 else : dt[key] = 1 - return dt diff --git a/app/organization/network/management/commands/export-ircam-timesheet-xls.py b/app/organization/network/management/commands/export-ircam-timesheet-xls.py new file mode 100644 index 00000000..44231eff --- /dev/null +++ b/app/organization/network/management/commands/export-ircam-timesheet-xls.py @@ -0,0 +1,230 @@ +# -*- coding: utf-8 -*- +# +# Copyright (c) 2016-2017 Ircam +# Copyright (c) 2016-2017 Guillaume Pellerin +# Copyright (c) 2016-2017 Emilie Zawadzki + +# This file is part of mezzanine-organization. + +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. + +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. + +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see . + +import os +import sys +import csv +import logging +import datetime +from optparse import make_option +import xlrd +from itertools import takewhile +from re import findall +import dateutil.parser +# from string import split +from django.conf import settings +from django.core.management.base import BaseCommand, CommandError +from django.contrib.auth.models import User +from django.db.models import Q + +from organization.core.models import * +from organization.network.models import * +from organization.projects.models import * + + +class Logger: + + def __init__(self, file): + self.logger = logging.getLogger('myapp') + self.hdlr = logging.FileHandler(file) + self.formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s') + self.hdlr.setFormatter(self.formatter) + self.logger.addHandler(self.hdlr) + self.logger.setLevel(logging.INFO) + + def info(self, prefix, message): + self.logger.info(' ' + prefix + ' : ' + message) + + def error(self, prefix, message): + self.logger.error(prefix + ' : ' + message) + + +class IrcamXLS: + + def __init__(self, file): + self.book = xlrd.open_workbook(file) + self.sheets = self.book.sheets() + + +class IrcamTimeSheet(object): + + def __init__(self, person, date_from, date_to): + self.person = person + self.date_from = date_from + self.date_to = date_to + + def set_person_activity_timesheet(self, + activity, + project, + percentage, + month, + year): + """ Set for a year percentage worked by month + on a project + """ + pats = PersonActivityTimeSheet.objects.get_or_create(activity = activity, + project = project, + percentage = percentage, + month = month, + year = year + ) + + + def set_work_package(self, person_activity_timesheet): + """ set contract id of the project """ + +class Command(BaseCommand): + help = """Import Person data from IRCAM's legacy XLS management file. + python manage.py import-ircam-timesheet-xls -s /srv/backup/TemplateInputTimeSheet2015-16.xlsx + """ + + option_list = BaseCommand.option_list + ( + make_option('-d', '--dry-run', + action='store_true', + dest='dry-run', + help='Do NOT write anything'), + make_option('-f', '--force', + action='store_true', + dest='force', + help='Force overwrite data'), + make_option('-s', '--source', + dest='source_file', + help='define the XLS source file'), + make_option('-l', '--log', + dest='log', + help='define log file'), + ) + + def handle(self, *args, **kwargs): + self.logger = Logger(kwargs.get('log')) + self.pattern = kwargs.get('pattern') + self.source_file = os.path.abspath(kwargs.get('source_file')) + self.dry_run = kwargs.get('dry-run') + self.force = kwargs.get('force') + + xls = IrcamXLS(self.source_file) + for sheet in xls.sheets: + person_register_id = sheet.cell_value(xls.register_id_row, xls.register_id_col) + persons = Person.objects.filter(register_id=int(person_register_id)) + processing_counter = 0 + # database not enough clear, possible multiple entries for some persons + # iterating over one person + for person in persons: + period_str = sheet.cell_value(xls.period_row, xls.period_col) + periods = findall(r'\d{1,2}/\d{1,2}/\d{4}', period_str) + date_from = dateutil.parser.parse(periods[0]) + date_to = dateutil.parser.parse(periods[1]) + curr_year = date_to.year + + self.logger.info('Processing', '******************* PERSON : ' + str(person.id) + ' | '+person.title + " *******************" ) + its = IrcamTimeSheet(person, date_from, date_to) + + # iterating on each month + for col_index in range(xls.first_percent_col, xls.first_percent_col + xls.nb_of_month): + + # condition to determine the end of projects list + end_project_list_row = 0 + + # get month + month = int(sheet.cell_value(xls.first_month_row, col_index)) + self.logger.info('Processing', 'year : ' + str(curr_year) + " | month : " + str(month)) + # calculate the current date + curr_date = datetime.date(curr_year, month, 1) + + # find the right activities corresponding to the current month / year + activities = person.activities.filter(Q(date_from__lte=curr_date) & Q(date_to__gte=curr_date)) + + # for each activities + for activity in activities : + # iterating over projects cells + self.logger.info('Processing', 'activity : ' + str(activity.id) + ' | ' + activity.__str__()) + project_row_index = xls.first_project_row + while sheet.cell_value(project_row_index, xls.first_project_col) != "Total final": + + # get percent + percent = sheet.cell_value(project_row_index, col_index) if sheet.cell_value(project_row_index, col_index) else 0 + + # try to find project + project_id_str = sheet.cell_value(project_row_index, xls.first_project_col - 1) + if isinstance(project_id_str, float) : + # by default, numbers are retrived as float + project_id_str = str(int(project_id_str)) + + # processing projects + if end_project_list_row == 0: + # check if project exists + project = Project.objects.filter(external_id__icontains=project_id_str).first() + if project : + # save timesheet without work packages + its.set_person_activity_timesheet(activity, project, percent, month, curr_year) + processing_counter += 1 + else : + self.logger.info('Not Found', 'project : ' + project_external_id) + + # increment index + project_row_index += 1 + + # processing work package + work_package_row_index = project_row_index + 1 + while sheet.cell_value(work_package_row_index, xls.first_project_col) != "Date entrée": + + # get project + project_external_id = int(sheet.cell_value(work_package_row_index, xls.first_project_col - 1)) + project = Project.objects.get(external_id__icontains=str(project_external_id)) + + # check if project exists + if project: + self.logger.info('Processing', 'project : ' + str(project.id) + " | " + project.__str__()) + + # list all work package + wk_p_str = sheet.cell_value(work_package_row_index, col_index) + wk_p_list = wk_p_str.split(",") + + # link work packages to timesheet + for wk_p_num in wk_p_list: + wk_p_num = str(wk_p_num) + + # create or get ProjectWorkPackage + wk_obj, wk_created = ProjectWorkPackage.objects.get_or_create(title="wk_"+wk_p_num, number=wk_p_num, project=project) + pat = PersonActivityTimeSheet.objects.filter(activity=activity, project=project, month=month, year=curr_year) + + # for each PersonActivityTimeSheet link work package + for timesheet in pat: + timesheet.work_packages.add(wk_obj) + timesheet.save() + + + # increment index + work_package_row_index += 1 + + # processing accounting and validation date + dates_row_index = work_package_row_index + date_accounting = xlrd.xldate.xldate_as_datetime(sheet.cell_value(dates_row_index, col_index), 1) + date_validation = xlrd.xldate.xldate_as_datetime(sheet.cell_value(dates_row_index + 1, col_index), 1) + + # get all timesheets, function of the activity, month and year + pats = PersonActivityTimeSheet.objects.filter(activity=activity, month=month, year=curr_year) + for pat in pats : + pat.accounting = date_accounting + pat.validation = date_validation + pat.save() + + self.logger.info('Processing', '_________________________ Number of record : ' + str(processing_counter) + ' _________________________') diff --git a/app/organization/network/tests.py b/app/organization/network/tests.py index 43ddb800..d14f7a7f 100644 --- a/app/organization/network/tests.py +++ b/app/organization/network/tests.py @@ -21,7 +21,13 @@ from django.test import SimpleTestCase import datetime -from organization.network.utils import get_nb_half_days_by_period +from organization.network.utils import get_nb_half_days_by_period, get_nb_half_days_by_period_per_month +from organization.network.api import get_leave_days_per_month + +# +# To run tests without database : +# python manage.py test organization.network.tests.[method_name] --settings='organization.core.no_db_settings' +# class NbOfHalfDaysInPeriodTestCase(SimpleTestCase): @@ -46,3 +52,249 @@ class NbOfHalfDaysInPeriodTestCase(SimpleTestCase): result = get_nb_half_days_by_period(self.date_from, self.date_to) self.assertEquals(result, expected) + + +class NbOfHalfDaysInPeriodPerMonthTestCase(SimpleTestCase): + + def setUp(self): + self.date_from = datetime.date(2015,1,1) + self.date_to = datetime.date(2015,12,31) + + def test_nbhalf_half_days(self): + + expected = { + 1:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 2:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 3:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 4:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 5:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 6:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 7:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 8:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 9:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 10:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 11:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + }, + 12:{ + 'friday_pm':52, + 'tuesday_am':52, + 'thursday_pm':53, + 'monday_pm':52, + 'tuesday_pm':52, + 'wednesday_am':52, + 'thursday_am':53, + 'wednesday_pm':52, + 'friday_am':52, + 'monday_am':52 + } + } + + result = get_nb_half_days_by_period_per_month(self.date_from, self.date_to) + self.assertEquals(result, expected) + + +class NbOfLeaveDaysPerMonthTestCase(SimpleTestCase): + + def setUp(self): + self.date_from = datetime.date(2015,1,1) + self.date_to = datetime.date(2015,12,31) + self.external_id = 97 + + def test_nb_leave_days(self): + + expected = { + 1:{ + 'wednesday_am':1, + 'monday_am':2, + 'friday_am':1, + 'thursday_am':1, + 'tuesday_pm':1, + 'wednesday_pm':1, + 'friday_pm':2, + 'tuesday_am':1, + 'monday_pm':2, + 'thursday_pm':1 + }, + 6:{ + 'monday_pm':1 + }, + 7:{ + 'wednesday_am':3, + 'monday_am':2, + 'friday_am':2, + 'thursday_am':3, + 'tuesday_pm':2, + 'wednesday_pm':3, + 'friday_pm':2, + 'tuesday_am':2, + 'monday_pm':2, + 'thursday_pm':3 + }, + 8:{ + 'wednesday_am':2, + 'monday_am':3, + 'friday_am':2, + 'thursday_am':2, + 'tuesday_pm':3, + 'wednesday_pm':2, + 'friday_pm':2, + 'tuesday_am':3, + 'monday_pm':4, + 'thursday_pm':2 + }, + 9:{ + 'wednesday_am':1, + 'friday_am':1, + 'thursday_am':1, + 'tuesday_pm':1, + 'wednesday_pm':1, + 'tuesday_am':1, + 'friday_pm':1, + 'thursday_pm':1 + }, + 10:{ + 'thursday_pm':1 + }, + 11:{ + 'wednesday_am':1, + 'monday_am':1, + 'tuesday_pm':1, + 'wednesday_pm':1, + 'friday_pm':1, + 'tuesday_am':1, + 'monday_pm':1 + }, + 12:{ + 'wednesday_am':2, + 'monday_am':1, + 'thursday_am':2, + 'tuesday_pm':2, + 'wednesday_pm':2, + 'tuesday_am':2, + 'monday_pm':2, + 'thursday_pm':2 + } + } + + result = get_leave_days_per_month(self.date_from, self.date_to, self.external_id) + self.assertEquals(result, expected) diff --git a/app/organization/network/urls.py b/app/organization/network/urls.py index 19ab3264..01dbb171 100644 --- a/app/organization/network/urls.py +++ b/app/organization/network/urls.py @@ -31,6 +31,7 @@ from mezzanine.conf import settings from organization.network.views import * urlpatterns = [ + url(r'^person/(?P.*)/timesheet/(?P[0-9]{4})/(?P[0-9]{1,2})/export_xls/$', PersonActivityTimeSheetExportView.as_view(), name="organization-network-timesheet-export-xls-view"), url(r'^person/(?P.*)/timesheet/(?P[0-9]{4})/(?P[0-9]{1,2})/create/$', TimeSheetCreateView.as_view(), name="organization-network-timesheet-create-view"), url(r'^person/(?P.*)/timesheet/dashboard/$', PersonActivityTimeSheetListView.as_view(), name="organization-network-timesheet-list-view" ), url(r'^person/(?P.*)/$', PersonDetailView.as_view(), name="organization-network-person-detail"), diff --git a/app/organization/network/utils.py b/app/organization/network/utils.py index ec47083f..7b7eb32b 100644 --- a/app/organization/network/utils.py +++ b/app/organization/network/utils.py @@ -1,5 +1,13 @@ # -*- coding: utf-8 -*- import pandas as pd +import csv +from django.http import HttpResponse +from xlwt import Workbook +import calendar +from organization.network.api import * +import datetime + + def get_nb_half_days_by_period(date_from, date_to): day_list = pd.date_range(date_from, date_to).tolist() @@ -33,3 +41,188 @@ def get_nb_half_days_by_period(date_from, date_to): day_dict['friday_pm'] += 1 return day_dict + + +def get_nb_half_days_by_period_per_month(date_from, date_to): + day_list = pd.date_range(date_from, date_to).tolist() + day_dict = { + "monday_am": 0, + "monday_pm": 0, + "tuesday_am": 0, + "tuesday_pm": 0, + "wednesday_am": 0, + "wednesday_pm": 0, + "thursday_am": 0, + "thursday_pm": 0, + "friday_am": 0, + "friday_pm": 0, + } + + md_dict = {} + for i in range(1,13): + md_dict[i] = day_dict + for day in day_list : + if day.dayofweek == 0: + md_dict[day.month]['monday_am'] += 1 + md_dict[day.month]['monday_pm'] += 1 + if day.dayofweek == 1: + md_dict[day.month]['tuesday_am'] += 1 + md_dict[day.month]['tuesday_pm'] += 1 + if day.dayofweek == 2: + md_dict[day.month]['wednesday_am'] += 1 + md_dict[day.month]['wednesday_pm'] += 1 + if day.dayofweek == 3: + md_dict[day.month]['thursday_am'] += 1 + md_dict[day.month]['thursday_pm'] += 1 + if day.dayofweek == 4: + md_dict[day.month]['friday_am'] += 1 + md_dict[day.month]['friday_pm'] += 1 + return md_dict + + + +class TimesheetXLS(object): + + first_month_row = 5 + first_month_col = 4 + last_month_col = first_month_col + 13 + project_margin_row = 6 + project_first_row = 6 + project_first_col = 0 + percent_margin = 1 + percent_label = "Percentage of time worked on project" + percent_label_row = 7 + percent_label_col = 0 + hours_margin = 2 + hours_label = "Productive hours worked on project" + hours_label_row = 8 + hours_label_col = 0 + wk_label = "Workpackages to which the person has contributed" + wk_label_row = 9 + wk_label_col = 0 + accounting_label = "Date of accounting by person working on the action" + accounting_label_col = 0 + validation_label_row = 0 + validation_label = "Date of validation by the superior" + validation_label_col = 0 + title_col = 0 + title_row = 0 + title_action_col = 0 + title_action_row = 1 + beneficiary_col = 0 + beneficiary_row = 2 + name_person_col = 0 + name_person_row = 3 + grant_col = 2 + grant_row = 1 + type_personal_col = 2 + type_personal_row = 3 + work_package_margin = 3 + accounting_margin = 3 + validation_margin = 4 + + def __init__(self, timesheets): + self.timesheets = timesheets.order_by('activity','project', 'month') + self.book = Workbook() + + def init_layout(self, sheet, year): + sheet.write(self.title_row, self.title_col, "TIME RECORDING FOR A HORIZON 2020 ACTION") + sheet.write(self.title_action_row, self.title_action_col, "Title of the action :") + sheet.write(self.beneficiary_row, self.beneficiary_col, "Beneficiary's / linked third part's name :") + sheet.write(self.name_person_row, self.name_person_col, "Name of the person working on the action :") + sheet.write(self.grant_row, self.grant_col, "Grant Agreement No : ") + sheet.write(self.type_personal_row, self.type_personal_col, "Type of personnel :") + row = sheet.row(self.first_month_row) + for i in range(self.first_month_col, self.last_month_col): + row.write(i, calendar.month_name[i - self.first_month_col] +"-"+ str(year % 100)) + + def export(self): + curr_project = '' + project_row_index = self.project_first_row + percent_label_row_index = self.percent_label_row + hours_label_row_index = self.hours_label_row + wk_label_row_index = self.wk_label_row + + for timesheet in self.timesheets: + print("TIME SHEET", timesheet.id) + try : + self.sheet = self.book.add_sheet(timesheet.activity.person.slug) + self.init_layout(self.sheet, timesheet.year) + + # calculate nb of worked hours + date_from = datetime.date(timesheet.year, 1, 1) + date_to = datetime.date(timesheet.year, 12, 31) + nb_half_days = get_nb_half_days_by_period_per_month(date_from, date_to) + leave_days = get_leave_days_per_month(date_from, date_to, timesheet.activity.person.external_id) + # substract none worked half days + # print(nb_half_days) + # print("****************************") + # print(leave_days) + # print("****************************") + for m_key, m_val in nb_half_days.items(): + if m_key in leave_days : + for nhd_k, nhd_v in m_val.items(): + if nhd_k in leave_days[m_key]: + print(m_key, nhd_k, leave_days[m_key][nhd_k], nb_half_days[m_key][nhd_k]) + nb_half_days[m_key][nhd_k] = nhd_v - leave_days[m_key][nhd_k] + print(m_key, nhd_k, nb_half_days[m_key][nhd_k]) + print(nb_half_days) + except: + pass + + if curr_project == '': + curr_project = timesheet.project + elif curr_project != timesheet.project: + curr_project = timesheet.project + project_row_index += self.project_margin_row + percent_label_row_index += self.project_margin_row + hours_label_row_index += self.project_margin_row + wk_label_row_index += self.project_margin_row + + # percent + self.sheet.write(project_row_index + self.percent_margin, timesheet.month + self.first_month_col, timesheet.percentage) + + + + # multiplying by nb of theoretical worked hours + # + # theo_worked_hours = + # timesheet.activity.monday_am = + + # multiplying by percent + + # work packages + work_packages = [str(wk.number) for wk in timesheet.work_packages.all()] + work_packages = ",".join(work_packages) + self.sheet.write(project_row_index + self.work_package_margin, timesheet.month + self.first_month_col, work_packages) + + try : + self.sheet.write(project_row_index, self.project_first_col, timesheet.project.__str__()) + self.sheet.write(project_row_index, self.project_first_col + 1, timesheet.project.external_id) + self.sheet.write(percent_label_row_index, self.percent_label_col, self.percent_label) + self.sheet.write(hours_label_row_index, self.hours_label_col, self.hours_label) + self.sheet.write(wk_label_row_index, self.wk_label_col, self.wk_label) + except: + pass + + # check were is the lower cell + if wk_label_row_index > self.validation_label_row: + self.validation_label_row = wk_label_row_index + elif wk_label_row_index == self.validation_label_row: + try : + # accounting date + self.sheet.write(self.validation_label_row + 1, timesheet.month + self.first_month_col, timesheet.accounting) + + # validation date + self.sheet.write(self.validation_label_row + 2, timesheet.month + self.first_month_col, timesheet.validation) + except: + pass + + + + def write(self): + self.export() + response = HttpResponse(content_type="application/vnd.ms-excel") + response['Content-Disposition'] = 'attachment; filename=users.xls' + self.book.save(response) + return response diff --git a/app/organization/network/views.py b/app/organization/network/views.py index b2000160..45e2e7e5 100644 --- a/app/organization/network/views.py +++ b/app/organization/network/views.py @@ -22,6 +22,8 @@ from django.shortcuts import render from django.views.generic.edit import CreateView from django.contrib.auth.mixins import LoginRequiredMixin +from django.views.generic.base import TemplateView +from django.views.generic import View from mezzanine.conf import settings from django.core.urlresolvers import reverse from dal import autocomplete @@ -29,6 +31,8 @@ from organization.network.models import * from organization.core.views import * from datetime import date from organization.network.forms import * +from organization.network.utils import TimesheetXLS + class PersonListView(ListView): @@ -164,3 +168,11 @@ class PersonActivityTimeSheetListView(TimesheetAbstractView, ListView): context['current_year'] = date.today().year context.update(self.kwargs) return context + + +class PersonActivityTimeSheetExportView(TimesheetAbstractView, View): + + def get(self, *args, **kwargs): + timesheets = PersonActivityTimeSheet.objects.filter(activity__person__slug__exact=kwargs['slug'], year=kwargs['year']) + xls = TimesheetXLS(timesheets) + return xls.write() diff --git a/requirements.txt b/requirements.txt index a11fb46b..1f265494 100644 --- a/requirements.txt +++ b/requirements.txt @@ -22,3 +22,4 @@ gitpython pygraphviz sphinx_rtd_theme pandas==0.19.2 +xlwt==1.2.0 -- 2.39.5