from openpyxl import load_workbook
from django import forms
from django.db.models import DateField, CharField, TextField
from dateutil.parser import parse
from collections import namedtuple
from django.core import management
import logging

logger = logging.getLogger(__name__)


class BulkImportForm(forms.Form):
    spreadsheet = forms.FileField()

ModelMapping = namedtuple('ModelMapping', ['model', 'mapping',
                          'unique_column', 'unique_field'])


class BulkImporterException(Exception):
    pass


class MissingUniqueHeaderException(BulkImporterException):
    pass

class EmptyUniqueFieldException(BulkImporterException):
    pass

class MappingProcessor:
    """Mappings required for processing one row into one model
    """
    model = None
    mapping = None
    headers = None

class BulkDataImportHandler:
    """
    Example Usage:

    bi = BulkDataImportHandler()
    bi.add_mapping(DjangoModel, {"SpreadsheetHeader": "model_field", ...})
    imported_records = bi.process_spreadsheet(spreadsheet)


    """
    def __init__(self):
        self.mappings = []
        self.linking_func = None
        self.func_mappings = []
        self.header_row = 0
        self.first_data_row = 1

    def add_mapping(self, model, mapping, unique_column=None,
                    unique_field=None):
        """
        Specify a row <-> model mapping

        `mapping` should be a dictionary with keys as the headings of columns
        in the spreadsheet to be processed, and values as the names of
        matching fields on the supplied `model`.

        Processing rows of data can take two forms, if `unique_column` and
        `unique_field` are supplied, a lookup is performed which can update
        existing records.
        Otherwise, or if no record is found a new model of the supplied type
        is created, and appropriate data fields from the spreadsheet
        are set onto the model.

        The model is then saved into the database.

        :param model: Django database model that will be populated
        :param unique_column: (optional) The name of the unique column in
                                         the spreadsheet
        :param unique_field: (optional) The name of the unique column in the
                                        DB model
        """
        if unique_column:
            unique_column = unique_column.lower()
        lowercase_mapping = dict((k.lower(), v) for k,v in mapping.items())
        self.mappings.append(ModelMapping(model, lowercase_mapping,
                                          unique_column, unique_field))

    def add_function_mapping(self, function):
        """
        Specify a function to run for each row in a spreadsheet

        Can be used for updating existing records, or some other purpose.

        Supplied function must take arguments:
            headers = list of header strings from spreadsheet
            values = list of values from a row of spreadsheet
        """
        self.func_mappings.append(function)

    def add_linking_function(self, function):
        """
        Add function called after each row

        Typically used to link models together if there
        are multiple created for each row of data

        Called with the created objects, in order based on added mappings.
        """
        self.linking_func = function

    def process_spreadsheet(self, spreadsheet, rebuild_search_index=False):
        """
        Open the spreadsheet file and process rows one at a time.

        Also flushes and rebuilds the search index.

        Processes the rows in the spreadsheet one at a time
        """
        wb = load_workbook(spreadsheet)
        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])

        data = sheet.range(sheet.calculate_dimension())
        headers = [v.value.lower() for v in data[self.header_row] if v.value]
        all_affected_records = []
        all_columns_used = set()

        for row in data[self.first_data_row:]:
            vals = [v.value for v in row]

            if vals[0] and str(vals[0]).lower() == headers[0]: # repeated header row
                continue

            try:
                affected_records, used_cols = self.process_row(headers, vals)
                all_columns_used.update(used_cols)
                all_affected_records.append(affected_records)
            except EmptyUniqueFieldException:
                logger.debug(EmptyUniqueFieldException)

        # Update Search index
        if rebuild_search_index:
            management.call_command('rebuild_index', interactive=False)

        all_columns_used = [unicode(s) for s in all_columns_used]
        unused_cols = list(set(headers) - set(all_columns_used))

        all_columns_used.sort()
        unused_cols.sort()

        stats = {
            'used_columns': all_columns_used,
            'unused_columns': unused_cols
        }

        return all_affected_records, stats


    def process_row(self, headers, vals):
        """
        Takes a list of headers and values, and turns them into a new model
        record and saves the model to the database.

        Looks up mapping data that has been added with `add_mapping`
        """
        affected_records = []
        used_columns = set()
        for model, mapping, unique_column, unique_field in self.mappings:

            instance = self._find_or_create_record(
                model,
                unique_column,
                unique_field,
                headers,
                vals
            )

            for col, field in mapping.items():
                try:
                    value = vals[headers.index(col)]
                    value = self.process_value(instance, field, value)
                    setattr(instance, field, value)
                    used_columns.add(col)
                except ValueError:
                    pass

            # Check unique field has a value
            if unique_field:
                if not getattr(instance, unique_field):
                    raise EmptyUniqueFieldException(
                        "Aborted importing row with empty '%s' column" %
                        unique_column)

            affected_records.append(instance)

            if self.linking_func and len(affected_records) > 1:
                self.linking_func(*affected_records)
            instance.save()

        for func_mapping in self.func_mappings:
            func_mapping(headers, vals)

        return affected_records, used_columns

    @staticmethod
    def _find_or_create_record(model, unique_column, field_name, headers, vals):
        """Return an instance of model.

        Either find an existing model based on unique column and field, or
        create a new one.
        """
        if unique_column:
            try:
                value = vals[headers.index(unique_column)]
            except ValueError:
                raise MissingUniqueHeaderException(
                    "Expected a unique column header '%s' to be in "
                    "the uploaded spreadsheet" % unique_column)

            try:
                instance = model.objects.get(**{field_name: value})
            except model.DoesNotExist:
                instance = model()
        else:
            instance = model()
        return instance

    @staticmethod
    def process_value(instance, field, value):
        field = instance._meta.get_field(field)
        if isinstance(field, DateField):
            try:
                value = parse(value, dayfirst=True)
            except:
                value = None
        if isinstance(field, CharField) or isinstance(field, TextField):
            if not value:
                value = ''
        return value

from openpyxl import load_workbook
from django import forms
from django.db.models import DateField, CharField, TextField
from dateutil.parser import parse
from collections import namedtuple
from django.core import management
import logging

logger = logging.getLogger(__name__)


class BulkImportForm(forms.Form):
    spreadsheet = forms.FileField()

ModelMapping = namedtuple('ModelMapping', ['model', 'mapping',
                          'unique_column', 'unique_field'])


class BulkImporterException(Exception):
    pass


class MissingUniqueHeaderException(BulkImporterException):
    pass

class EmptyUniqueFieldException(BulkImporterException):
    pass

class MappingProcessor:
    """Mappings required for processing one row into one model
    """
    model = None
    mapping = None
    headers = None

class BulkDataImportHandler:
    """
    Example Usage:

    bi = BulkDataImportHandler()
    bi.add_mapping(DjangoModel, {"SpreadsheetHeader": "model_field", ...})
    imported_records = bi.process_spreadsheet(spreadsheet)


    """
    def __init__(self):
        self.mappings = []
        self.linking_func = None
        self.func_mappings = []
        self.header_row = 0
        self.first_data_row = 1

    def add_mapping(self, model, mapping, unique_column=None,
                    unique_field=None):
        """
        Specify a row <-> model mapping

        `mapping` should be a dictionary with keys as the headings of columns
        in the spreadsheet to be processed, and values as the names of
        matching fields on the supplied `model`.

        Processing rows of data can take two forms, if `unique_column` and
        `unique_field` are supplied, a lookup is performed which can update
        existing records.
        Otherwise, or if no record is found a new model of the supplied type
        is created, and appropriate data fields from the spreadsheet
        are set onto the model.

        The model is then saved into the database.

        :param model: Django database model that will be populated
        :param unique_column: (optional) The name of the unique column in
                                         the spreadsheet
        :param unique_field: (optional) The name of the unique column in the
                                        DB model
        """
        if unique_column:
            unique_column = unique_column.lower()
        lowercase_mapping = dict((k.lower(), v) for k,v in mapping.items())
        self.mappings.append(ModelMapping(model, lowercase_mapping,
                                          unique_column, unique_field))

    def add_function_mapping(self, function):
        """
        Specify a function to run for each row in a spreadsheet

        Can be used for updating existing records, or some other purpose.

        Supplied function must take arguments:
            headers = list of header strings from spreadsheet
            values = list of values from a row of spreadsheet
        """
        self.func_mappings.append(function)

    def add_linking_function(self, function):
        """
        Add function called after each row

        Typically used to link models together if there
        are multiple created for each row of data

        Called with the created objects, in order based on added mappings.
        """
        self.linking_func = function

    def process_spreadsheet(self, spreadsheet, rebuild_search_index=False):
        """
        Open the spreadsheet file and process rows one at a time.

        Also flushes and rebuilds the search index.

        Processes the rows in the spreadsheet one at a time
        """
        wb = load_workbook(spreadsheet)
        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])

        data = sheet.range(sheet.calculate_dimension())
        headers = [v.value.lower() for v in data[self.header_row] if v.value]
        all_affected_records = []
        all_columns_used = set()

        for row in data[self.first_data_row:]:
            vals = [v.value for v in row]

            if vals[0] and str(vals[0]).lower() == headers[0]: # repeated header row
                continue

            try:
                affected_records, used_cols = self.process_row(headers, vals)
                all_columns_used.update(used_cols)
                all_affected_records.append(affected_records)
            except EmptyUniqueFieldException:
                logger.debug(EmptyUniqueFieldException)

        # Update Search index
        if rebuild_search_index:
            management.call_command('rebuild_index', interactive=False)

        all_columns_used = [unicode(s) for s in all_columns_used]
        unused_cols = list(set(headers) - set(all_columns_used))

        all_columns_used.sort()
        unused_cols.sort()

        stats = {
            'used_columns': all_columns_used,
            'unused_columns': unused_cols
        }

        return all_affected_records, stats


    def process_row(self, headers, vals):
        """
        Takes a list of headers and values, and turns them into a new model
        record and saves the model to the database.

        Looks up mapping data that has been added with `add_mapping`
        """
        affected_records = []
        used_columns = set()
        for model, mapping, unique_column, unique_field in self.mappings:

            instance = self._find_or_create_record(
                model,
                unique_column,
                unique_field,
                headers,
                vals
            )

            for col, field in mapping.items():
                try:
                    value = vals[headers.index(col)]
                    value = self.process_value(instance, field, value)
                    setattr(instance, field, value)
                    used_columns.add(col)
                except ValueError:
                    pass

            # Check unique field has a value
            if unique_field:
                if not getattr(instance, unique_field):
                    raise EmptyUniqueFieldException(
                        "Aborted importing row with empty '%s' column" %
                        unique_column)

            affected_records.append(instance)

            if self.linking_func and len(affected_records) > 1:
                self.linking_func(*affected_records)
            instance.save()

        for func_mapping in self.func_mappings:
            func_mapping(headers, vals)

        return affected_records, used_columns

    @staticmethod
    def _find_or_create_record(model, unique_column, field_name, headers, vals):
        """Return an instance of model.

        Either find an existing model based on unique column and field, or
        create a new one.
        """
        if unique_column:
            try:
                value = vals[headers.index(unique_column)]
            except ValueError:
                raise MissingUniqueHeaderException(
                    "Expected a unique column header '%s' to be in "
                    "the uploaded spreadsheet" % unique_column)

            try:
                instance = model.objects.get(**{field_name: value})
            except model.DoesNotExist:
                instance = model()
        else:
            instance = model()
        return instance

    @staticmethod
    def process_value(instance, field, value):
        field = instance._meta.get_field(field)
        if isinstance(field, DateField):
            try:
                value = parse(value, dayfirst=True)
            except:
                value = None
        if isinstance(field, CharField) or isinstance(field, TextField):
            if not value:
                value = ''
        return value

Mappings required for processing one row into one model Example Usage:      bi = BulkDataImportHandler()     bi.add_mapping(DjangoModel, {"SpreadsheetHeader": "model_field", ...})     imported_records = bi.process_spreadsheet(spreadsheet) Specify a row <-> model mapping          `mapping` should be a dictionary with keys as the headings of columns         in the spreadsheet to be processed, and values as the names of         matching fields on the supplied `model`.          Processing rows of data can take two forms, if `unique_column` and         `unique_field` are supplied, a lookup is performed which can update         existing records.         Otherwise, or if no record is found a new model of the supplied type         is created, and appropriate data fields from the spreadsheet         are set onto the model.          The model is then saved into the database.          :param model: Django database model that will be populated         :param unique_column: (optional) The name of the unique column in                                          the spreadsheet         :param unique_field: (optional) The name of the unique column in the                                         DB model Specify a function to run for each row in a spreadsheet          Can be used for updating existing records, or some other purpose.          Supplied function must take arguments:             headers = list of header strings from spreadsheet             values = list of values from a row of spreadsheet Add function called after each row          Typically used to link models together if there         are multiple created for each row of data          Called with the created objects, in order based on added mappings. Open the spreadsheet file and process rows one at a time.          Also flushes and rebuilds the search index.          Processes the rows in the spreadsheet one at a time # repeated header row # Update Search index Takes a list of headers and values, and turns them into a new model         record and saves the model to the database.          Looks up mapping data that has been added with `add_mapping` # Check unique field has a value Return an instance of model.          Either find an existing model based on unique column and field, or         create a new one.