From bf5bae553f3c21d767228699ec5a5b5bd76d3a14 Mon Sep 17 00:00:00 2001 From: Emilie Zawadzki Date: Mon, 16 Jan 2017 17:16:56 +0100 Subject: [PATCH] [Timesheet] : optimizing import --- app/organization/network/admin.py | 3 +- .../commands/import-ircam-matricule.py | 11 ++- .../commands/import-ircam-timesheet-xls.py | 93 +++++++++++++------ .../migrations/0083_auto_20170116_1235.py | 27 ++++++ app/organization/network/models.py | 4 +- requirements.txt | 1 + 6 files changed, 107 insertions(+), 32 deletions(-) create mode 100644 app/organization/network/migrations/0083_auto_20170116_1235.py diff --git a/app/organization/network/admin.py b/app/organization/network/admin.py index 4da1d9a3..39c89dd4 100644 --- a/app/organization/network/admin.py +++ b/app/organization/network/admin.py @@ -276,8 +276,9 @@ class TrainingTopicAdmin(BaseTranslationModelAdmin): -class PersonActivityTimeSheetAdmin(BaseTranslationModelAdmin): +class PersonActivityTimeSheetAdmin(BaseTranslationOrderedModelAdmin): model = PersonActivityTimeSheet + search_fields = ['year','activity__person__last_name', "project__title"] list_display = ['person', 'activity', 'year', 'month', 'project', 'work_package', 'percentage', 'accounting', 'validation'] list_filter = ['activity__person', 'year', 'project'] actions = ['export_xls',] diff --git a/app/organization/network/management/commands/import-ircam-matricule.py b/app/organization/network/management/commands/import-ircam-matricule.py index 2e105b35..7e650c9c 100644 --- a/app/organization/network/management/commands/import-ircam-matricule.py +++ b/app/organization/network/management/commands/import-ircam-matricule.py @@ -39,6 +39,7 @@ class Command(BaseCommand): help='define the XLS source file'), ) number_of_person = 0 + number_of_person_non_processed = 0 def handle(self, *args, **kwargs): @@ -48,7 +49,12 @@ class Command(BaseCommand): self.first_row = self.sheet.row(0) num_cols = self.sheet.ncols for row_idx in range(0, self.sheet.nrows): # Iterate through rows - cell_id = self.sheet.cell(row_idx, 0).value.strip() + cell_id = self.sheet.cell(row_idx, 0).value + print("cell_id", type(cell_id), cell_id) + if isinstance(cell_id, float): + cell_id = str(int(cell_id)) + if isinstance(cell_id, str): + cell_id = cell_id.strip() cell_last_name = self.sheet.cell(row_idx, 1).value cell_first_name = self.sheet.cell(row_idx, 2).value self.update_register_id(cell_id, cell_last_name, cell_first_name) @@ -56,6 +62,8 @@ class Command(BaseCommand): print('***************************************************') print("Number of person processed : "+str(self.number_of_person)) print('***************************************************') + print("Number of person NON processed : "+str(self.number_of_person_non_processed)) + print('***************************************************') def update_register_id(self, id, last_name, first_name): slug = slugify(first_name+'-'+last_name) @@ -68,3 +76,4 @@ class Command(BaseCommand): p.save() else : print("Person not found: "+last_name+' '+first_name+' | manual slug : '+ slug) + self.number_of_person_non_processed += 1 diff --git a/app/organization/network/management/commands/import-ircam-timesheet-xls.py b/app/organization/network/management/commands/import-ircam-timesheet-xls.py index 7e5a44e3..50394f3c 100644 --- a/app/organization/network/management/commands/import-ircam-timesheet-xls.py +++ b/app/organization/network/management/commands/import-ircam-timesheet-xls.py @@ -24,6 +24,8 @@ import sys import csv import logging import datetime +import math +import datetimerange from optparse import make_option import xlrd from itertools import takewhile @@ -71,11 +73,11 @@ class IrcamXLS: register_id_row = 4 register_id_col = 3 - period_row = 5 + period_row = 6 period_col = 2 - first_project_row = 13 + first_project_row = 14 first_project_col = 2 - first_month_row = 10 + first_month_row = 11 first_month_col = 3 first_percent_row = 13 first_percent_col = 3 @@ -153,8 +155,11 @@ class Command(BaseCommand): 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) + print("periods", periods, person, period_str) date_from = dateutil.parser.parse(periods[0]) date_to = dateutil.parser.parse(periods[1]) + print("date_from", type(date_from), "date_to", type(date_to)) + curr_range_date = datetimerange.DateTimeRange(date_from, date_to) curr_year = date_to.year self.logger.info('Processing', '******************* PERSON : ' + str(person.id) + ' | '+person.title + " *******************" ) @@ -168,15 +173,29 @@ class Command(BaseCommand): # 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)) + self.logger.info('Processing', 'year : ' + str(curr_year) + " | month : " + str(month) + " | " + str(curr_date)) - # for each activities - for activity in activities : + # find the right activities corresponding to the current month / year + activities = person.activities.all() + activity = None + if not activities: + self.logger.info('Not Found', 'activity for person : ' + str(person.id) + " - " + person.title + " for period : " + period_str) + else : + # find the right + for curr_activity in activities : + activity_date_range = datetimerange.DateTimeRange(datetime.datetime.combine(curr_activity.date_from, datetime.datetime.min.time()), datetime.datetime.combine(curr_activity.date_to, datetime.datetime.min.time())) + if curr_range_date.is_intersection(activity_date_range): + activity = curr_activity + + if not activity: + self.logger.info('Not Found', 'No activity correponds to period : '+ str(curr_year) +" "+str(month)) + else : + # 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 @@ -194,45 +213,61 @@ class Command(BaseCommand): # processing projects if end_project_list_row == 0: # check if project exists - project = Project.objects.filter(external_id__icontains=project_id_str).first() + print("project_id_str", project_id_str, project_row_index, xls.first_project_col - 1) + project, is_created = Project.objects.get_or_create(external_id=str(project_id_str)) + if is_created: + project.title = sheet.cell_value(project_row_index, xls.first_project_col) + project.external_id = project_id_str + project.save() + # 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 + self.logger.info('Processing', 'project : ' + str(project.id) + " | " + str(project.external_id) + " | " + project.title + " | percent : " + str(percent)) else : - self.logger.info('Not Found', 'project : ' + project_external_id) + self.logger.info('Not Found', 'project : ' + project_id_str) # increment index project_row_index += 1 # processing work package work_package_row_index = project_row_index + 1 + print("WK", sheet.cell_value(work_package_row_index, xls.first_project_col)) 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)) + print("project_external_id", project_external_id) + project = Project.objects.get(external_id=str(project_external_id)) # check if project exists if project: - self.logger.info('Processing', 'project : ' + str(project.id) + " | " + project.__str__()) # list all work package + print(work_package_row_index, col_index) 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() + if wk_p_str : + self.logger.info('Processing', 'work packages : ' + str(wk_p_str) + " | project" + project.external_id + " - " + project.title) + wk_p_list = "" + if isinstance(wk_p_str, str): + wk_p_list = wk_p_str.split(",") + elif isinstance(wk_p_str, float): + i, d = divmod(wk_p_str, 1) + wk_p_list = (int(i), int(d)) + print("wk_p_list", wk_p_list, wk_p_str) + # 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 @@ -240,8 +275,10 @@ class Command(BaseCommand): # 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) + date_accounting_str = sheet.cell_value(dates_row_index, col_index) + date_accounting = xlrd.xldate.xldate_as_datetime(date_accounting_str, 1) if date_accounting_str else None + date_validation_str = sheet.cell_value(dates_row_index + 1, col_index) + date_validation = xlrd.xldate.xldate_as_datetime(date_validation_str, 1) if date_validation_str else None # get all timesheets, function of the activity, month and year pats = PersonActivityTimeSheet.objects.filter(activity=activity, month=month, year=curr_year) diff --git a/app/organization/network/migrations/0083_auto_20170116_1235.py b/app/organization/network/migrations/0083_auto_20170116_1235.py new file mode 100644 index 00000000..3ed84338 --- /dev/null +++ b/app/organization/network/migrations/0083_auto_20170116_1235.py @@ -0,0 +1,27 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.9.11 on 2017-01-16 11:35 +from __future__ import unicode_literals + +import datetime +from django.db import migrations, models +from django.utils.timezone import utc + + +class Migration(migrations.Migration): + + dependencies = [ + ('organization-network', '0082_merge'), + ] + + operations = [ + migrations.AlterField( + model_name='personactivitytimesheet', + name='accounting', + field=models.DateField(blank=True, default=datetime.datetime(2017, 1, 16, 11, 33, 2, 772761, tzinfo=utc), null=True), + ), + migrations.AlterField( + model_name='personactivitytimesheet', + name='validation', + field=models.DateField(blank=True, default=datetime.datetime(2017, 1, 16, 11, 33, 2, 772794, tzinfo=utc), null=True), + ), + ] diff --git a/app/organization/network/models.py b/app/organization/network/models.py index f8dcfcce..7cbb9ccd 100644 --- a/app/organization/network/models.py +++ b/app/organization/network/models.py @@ -563,8 +563,8 @@ class PersonActivityTimeSheet(models.Model): percentage = models.FloatField(_('% of work time on the project'), validators=[validate_positive]) month = models.IntegerField(_('month')) year = models.IntegerField(_('year')) - accounting = models.DateField(default=timezone.now(), blank=True) - validation = models.DateField(default=timezone.now(), blank=True) + accounting = models.DateField(default=timezone.now(), blank=True, null=True) + validation = models.DateField(default=timezone.now(), blank=True, null=True) @property def date(self): diff --git a/requirements.txt b/requirements.txt index 1f265494..0bb9877d 100644 --- a/requirements.txt +++ b/requirements.txt @@ -23,3 +23,4 @@ pygraphviz sphinx_rtd_theme pandas==0.19.2 xlwt==1.2.0 +DateTimeRange==0.2.8 -- 2.39.5