def import_questionnaire(self,
merge=None,
mode=EXACT_MATCH,
percentage=0.75,
infer_function=None):
_debug = False
qNumber = QuestionNumber()
slugs = []
wb = load_workbook(filename=self.file_path, data_only=True)
ws = wb.get_active_sheet()
log = ''
# Cell B1: Name of questionnaire
name = ws.cell('B1').value
slugQ = convert_text_to_slug(ws.cell('B1').value)
disable = False
questionnaire = None
if merge != None:
try:
questionnaire = Questionnaire.objects.get(id=merge)
except Questionnaire.DoesNotExist:
raise Exception(
'Questionnaire does not exist, so cant merge against it.')
else:
questionnaire = Questionnaire(name=name,
disable=disable,
slug=slugQ,
redirect_url='/')
log += '\nQuestionnaire created %s ' % questionnaire
if not _debug:
questionnaire.save()
log += '\nQuestionnaire saved %s ' % questionnaire
try:
_choices_array = {}
_questions_rows = {}
#############################
# TIPS:
# Type of Row: QuestionSet, Category, Question
# Columns: Type, Text/Question, Level/Number, Data type, Value list, Help text/Description, Tooltip, Slug, Stats
#############################
for row in ws.rows[2:]:
if len(row) > 0 and row[0].value != None:
type_Column = row[0]
text_question_Column = row[1]
if (text_question_Column.value != None):
text_question_Column.value = text_question_Column.value.encode(
'ascii', 'ignore')
level_number_column = row[2]
_checks = ''
# Type = QUESTIONSET
# Columns required: Type, Text/Question
# Columns optional: Help text/Description, Tooltip
if str(type_Column.value) == "QuestionSet":
sortid = str(level_number_column.value)
try:
qNumber.getNumber('h0', sortid)
except:
self.writeLog(log)
raise
text_en = 'h1. %s' % text_question_Column.value
slug_qs = None
if row[7].value:
slug_qs = row[7].value
else:
slug_qs = str(slugQ) + "_" + convert_text_to_slug(
str(text_question_Column.value))
if row[5].value:
helpText = row[5].value
else:
helpText = ""
tooltip = False
if row[6].value:
if str(row[6].value).lower() == 'yes':
tooltip = True
questionset = None
created = False
try:
questionset = QuestionSet.objects.get(
questionnaire=questionnaire,
sortid=sortid,
heading=slug_qs)
except QuestionSet.DoesNotExist:
questionset = QuestionSet(
questionnaire=questionnaire,
sortid=sortid,
heading=slug_qs,
checks='required',
text_en=text_en,
help_text=helpText,
tooltip=tooltip)
created = True
if created:
log += '\n%s - QuestionSet created %s - %s ' % (
type_Column.row, sortid, text_en)
else:
log += '\n%s - QuestionSet retrieved %s - %s ' % (
type_Column.row, sortid, text_en)
try:
if not _debug:
questionset.save()
log += '\n%s - QuestionSet saved %s - %s ' % (
type_Column.row, sortid, text_en)
except:
log += "\n%s - Error to save questionset %s - %s" % (
type_Column.row, sortid, text_en)
self.writeLog(log)
raise
#if not created:
# last_question = Question.objects.filter(questionset=questionset).order_by('-id')[0]
# qNumber.setState(last_question.number)
# Type = CATEGORY
# Columns required: Type, Text/Question, Level/Number, Category
# Columns optional: Help text/Description, Slug, Tooltip, Dependencies
elif str(type_Column.value) == "Category":
self.__handleQuestion(self.CATEGORY,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=mode,
percentage=percentage,
infer_function=infer_function)
# Type = QUESTION
# Columns required: Type, Text/Question, Level/Number, Data Type, Category, Stats
# Columns optional: Value List, Help text/Description, Tooltip, Dependencies
else:
self.__handleQuestion(self.QUESTION,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=mode,
percentage=percentage,
infer_function=infer_function)
except:
log += '\nError to save questionsets and questions of the questionnaire %s ' % questionnaire
self.writeLog(log)
raise
log += '\nQuestionnaire %s, questionsets, questions and choices created with success!! ' % questionnaire
self.writeLog(log)
#raise Exception('Dont commit me dude')
return True
def import_questionnaire(self, merge=None, mode=EXACT_MATCH, percentage=0.75, infer_function=None):
_debug = False
qNumber = QuestionNumber()
slugs = []
wb = load_workbook(filename=self.file_path, data_only=True)
ws = wb.get_active_sheet()
log = ""
# Cell B1: Name of questionnaire
name = ws.cell("B1").value
slugQ = convert_text_to_slug(ws.cell("B1").value)
disable = False
questionnaire = None
if merge != None:
try:
questionnaire = Questionnaire.objects.get(id=merge)
except Questionnaire.DoesNotExist:
raise Exception("Questionnaire does not exist, so cant merge against it.")
else:
questionnaire = Questionnaire(name=name, disable=disable, slug=slugQ, redirect_url="/")
log += "\nQuestionnaire created %s " % questionnaire
if not _debug:
questionnaire.save()
log += "\nQuestionnaire saved %s " % questionnaire
try:
_choices_array = {}
_questions_rows = {}
#############################
# TIPS:
# Type of Row: QuestionSet, Category, Question
# Columns: Type, Text/Question, Level/Number, Data type, Value list, Help text/Description, Tooltip, Slug, Stats
#############################
for row in ws.rows[2:]:
if len(row) > 0 and row[0].value != None:
type_Column = row[0]
text_question_Column = row[1]
if text_question_Column.value != None:
text_question_Column.value = text_question_Column.value.encode("ascii", "ignore")
level_number_column = row[2]
_checks = ""
# Type = QUESTIONSET
# Columns required: Type, Text/Question
# Columns optional: Help text/Description, Tooltip
if str(type_Column.value) == "QuestionSet":
sortid = str(level_number_column.value)
try:
qNumber.getNumber("h0", sortid)
except:
self.writeLog(log)
raise
text_en = "h1. %s" % text_question_Column.value
slug_qs = None
if row[7].value:
slug_qs = row[7].value
else:
slug_qs = str(slugQ) + "_" + convert_text_to_slug(str(text_question_Column.value))
if row[5].value:
helpText = row[5].value
else:
helpText = ""
tooltip = False
if row[6].value:
if str(row[6].value).lower() == "yes":
tooltip = True
questionset = None
created = False
try:
questionset = QuestionSet.objects.get(
questionnaire=questionnaire, sortid=sortid, heading=slug_qs
)
except QuestionSet.DoesNotExist:
questionset = QuestionSet(
questionnaire=questionnaire,
sortid=sortid,
heading=slug_qs,
checks="required",
text_en=text_en,
help_text=helpText,
tooltip=tooltip,
)
created = True
if created:
log += "\n%s - QuestionSet created %s - %s " % (type_Column.row, sortid, text_en)
else:
log += "\n%s - QuestionSet retrieved %s - %s " % (type_Column.row, sortid, text_en)
try:
if not _debug:
questionset.save()
log += "\n%s - QuestionSet saved %s - %s " % (type_Column.row, sortid, text_en)
except:
log += "\n%s - Error to save questionset %s - %s" % (type_Column.row, sortid, text_en)
self.writeLog(log)
raise
# if not created:
# last_question = Question.objects.filter(questionset=questionset).order_by('-id')[0]
# qNumber.setState(last_question.number)
# Type = CATEGORY
# Columns required: Type, Text/Question, Level/Number, Category
# Columns optional: Help text/Description, Slug, Tooltip, Dependencies
elif str(type_Column.value) == "Category":
self.__handleQuestion(
self.CATEGORY,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=mode,
percentage=percentage,
infer_function=infer_function,
)
# Type = QUESTION
# Columns required: Type, Text/Question, Level/Number, Data Type, Category, Stats
# Columns optional: Value List, Help text/Description, Tooltip, Dependencies
else:
self.__handleQuestion(
self.QUESTION,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=mode,
percentage=percentage,
infer_function=infer_function,
)
except:
log += "\nError to save questionsets and questions of the questionnaire %s " % questionnaire
self.writeLog(log)
raise
log += "\nQuestionnaire %s, questionsets, questions and choices created with success!! " % questionnaire
self.writeLog(log)
# raise Exception('Dont commit me dude')
return True
def __handleQuestion(self,
type,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=EXACT_MATCH,
percentage=0.75,
infer_function=None):
try:
slug = None
text_en = None
if level_number_column.value.startswith('h'):
text_en = str(level_number_column.value) + '. ' + str(
text_question_Column.value)
else:
level = len(level_number_column.value.split('.')) - 1
text_en = 'h%s. %s' % (str(level),
str(text_question_Column.value))
dataType_column = None
if type == self.CATEGORY:
dataType_column = CommentPlaceholder()
else:
dataType_column = row[3]
if row[7].value:
slug = row[7].value
else:
slug = convert_text_to_slug(str(row[1].value)[:50])
slug = self.get_slug(slug, questionnaire)
if row[5].value:
helpText = row[5].value
else:
helpText = ''
_tooltip = False
if row[6].value:
if str(row[6].value).lower() == 'yes':
_tooltip = True
#If has dependencies
if row[8].value:
try:
dependencies_list = row[8]
list_dep_aux = dependencies_list.value.split('|')
question_num_parent = None
try:
question_num_parent = _questions_rows.get(
list_dep_aux[0]).number
except AttributeError:
''' If this is a merge, the dependant question can already be on the questionset,
lets try looking for it
'''
try:
question = Question.objects.get(
slug_fk__slug1=list_dep_aux[0],
questionset=questionset)
_questions_rows[
list_dep_aux[0]] = question_num_parent
question_num_parent = question.number
_choices_array[
list_dep_aux[0]] = self.__getChoices(question)
except Question.DoesNotExist:
raise Exception(
'The dependant with slug %s does not exist.' %
(list_dep_aux[0]))
index_aux = int(str(list_dep_aux[1])) - 1
choice_parent_list = _choices_array.get(list_dep_aux[0])
choice_parent = choice_parent_list[index_aux]
_checks = 'dependent=\"%s,%s\"' % (
str(question_num_parent), str(choice_parent))
except:
raise
try:
questionNumber = self.__handleQuestionNumber(
level_number_column.value, qNumber, questionset)
except:
if type == self.QUESTION:
log += "\n%s - Error to create question number %s" % (
type_Column.row, text_en)
elif type == self.CATEGORY:
log += "\n%s - Error to create Category number %s" % (
type_Column.row, text_en)
self.writeLog(log)
raise
#print slug
#Create or load slug
slugs = Slugs.objects.filter(slug1=slug, description=text_en)
if len(slugs) <= 0:
slug_db = Slugs(slug1=slug, description=text_en)
slug_db.save()
else:
slug_db = slugs[0]
visible_default = False
if row[10].value:
if str(row[10].value).lower() == 'visible':
visible_default = True
is_category = None
is_stats = None
if type == self.QUESTION:
is_stats = True
is_category = False
elif type == self.CATEGORY:
is_stats = False
is_category = True
try:
question = Question.objects.get(slug_fk__slug1=slug_db.slug1,
questionset=questionset)
question.text_en = text_en
question.number = str(questionNumber)
question.type = dataType_column.value
question.help_text = helpText
question.stats = is_stats
question.category = is_category
question.tooltip = _tooltip
question.checks = _checks
question.visible_default = visible_default
except Question.DoesNotExist:
question = Question(questionset=questionset,
text_en=text_en,
number=str(questionNumber),
type=dataType_column.value,
help_text=helpText,
slug=slug,
slug_fk=slug_db,
stats=is_stats,
category=is_category,
tooltip=_tooltip,
checks=_checks,
visible_default=visible_default,
disposition=self.__processDisposition(
row[11].value.lower()))
if dataType_column.value in ['open-validated']:
ardict = {}
if row[4].value:
# some basic types dont need regex
known_validations = {
"integer": "[+-]?\d+",
"decimal": "[+-]?\d*([.]\d*)?",
"scientific": "[+-]?\d*([.]\d*)?e[+-]?\d*([.]\d*)?",
"range": "[+\-]?\d*([.]\d*);[+\-]?\d*([.]\d*)",
"date": "\d{2}/\d{2}/\d{4}",
"time": "\d{2}:\d{2}:\d{2}",
"datetime": "\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2}",
"text": ".*"
}
try:
ardict['regex'] = known_validations[row[4].value]
ardict['base'] = row[4].value
except KeyError:
# If this is not known, try to validate it as a regex
try:
re.compile(row[4].value)
ardict['regex'] = row[4].value
except re.error:
raise Exception(
"--ERROR: The regex on row %d, column 4 is not valid"
% (type_Column.row))
if row[5].value:
split = row[5].value.split('|')
lensplit = len(split)
if lensplit == 1:
ardict['unit'] = split[0]
question.help_text = ""
elif lensplit == 2:
ardict['unit'] = split[0]
ardict['unit_desc'] = split[1]
question.help_text = ""
elif lensplit == 3:
ardict['unit'] = split[0]
ardict['unit_desc'] = split[1]
question.help_text = split[2]
else:
raise Exception(
"-- ERROR: Invalid number of segments on help text row %d, column 5. Max syntax is unit|desc|help_text"
% (type_Column.row))
question.metadata = json.dumps(ardict)
if not _debug:
question.save()
if type == self.QUESTION:
log += '\n%s - Question created %s ' % (type_Column.row,
question)
elif type == self.CATEGORY:
log += '\n%s - Category created %s ' % (type_Column.row,
question)
_questions_rows[slug] = question
if type == self.QUESTION:
if dataType_column.value in [
'choice', 'choice-freeform', 'choice-multiple',
'choice-multiple-freeform'
]:
_choices_array_aux = []
# Parse of values list
values_list = row[4]
if (values_list != None and values_list.value != None):
list_aux = values_list.value.split('|')
_choices_array[slug] = self.__processChoices(
type_Column.row,
question,
list_aux,
log,
debug=_debug,
mode=mode,
match_percentage=percentage,
infer_function=infer_function)
if dataType_column.value in [
'choice-yesno', 'choice-yesnodontknow'
]:
_choices_array[slug] = ['yes', 'no', 'dontknow']
except:
log += "\n%s - Error to save question %s" % (type_Column.row,
text_en)
self.writeLog(log)
raise
def __handleQuestion(
self,
type,
row,
type_Column,
level_number_column,
text_question_Column,
_questions_rows,
_choices_array,
qNumber,
questionset,
log,
_checks,
_debug,
questionnaire,
mode=EXACT_MATCH,
percentage=0.75,
infer_function=None,
):
try:
slug = None
text_en = None
if level_number_column.value.startswith("h"):
text_en = str(level_number_column.value) + ". " + str(text_question_Column.value)
else:
level = len(level_number_column.value.split(".")) - 1
text_en = "h%s. %s" % (str(level), str(text_question_Column.value))
dataType_column = None
if type == self.CATEGORY:
dataType_column = CommentPlaceholder()
else:
dataType_column = row[3]
if row[7].value:
slug = row[7].value
else:
slug = convert_text_to_slug(str(row[1].value)[:50])
slug = self.get_slug(slug, questionnaire)
if row[5].value:
helpText = row[5].value
else:
helpText = ""
_tooltip = False
if row[6].value:
if str(row[6].value).lower() == "yes":
_tooltip = True
# If has dependencies
if row[8].value:
try:
dependencies_list = row[8]
list_dep_aux = dependencies_list.value.split("|")
question_num_parent = None
try:
question_num_parent = _questions_rows.get(list_dep_aux[0]).number
except AttributeError:
""" If this is a merge, the dependant question can already be on the questionset,
lets try looking for it
"""
try:
question = Question.objects.get(slug_fk__slug1=list_dep_aux[0], questionset=questionset)
_questions_rows[list_dep_aux[0]] = question_num_parent
question_num_parent = question.number
_choices_array[list_dep_aux[0]] = self.__getChoices(question)
except Question.DoesNotExist:
raise Exception("The dependant with slug %s does not exist." % (list_dep_aux[0]))
index_aux = int(str(list_dep_aux[1])) - 1
choice_parent_list = _choices_array.get(list_dep_aux[0])
choice_parent = choice_parent_list[index_aux]
_checks = 'dependent="%s,%s"' % (str(question_num_parent), str(choice_parent))
except:
raise
try:
questionNumber = self.__handleQuestionNumber(level_number_column.value, qNumber, questionset)
except:
if type == self.QUESTION:
log += "\n%s - Error to create question number %s" % (type_Column.row, text_en)
elif type == self.CATEGORY:
log += "\n%s - Error to create Category number %s" % (type_Column.row, text_en)
self.writeLog(log)
raise
# print slug
# Create or load slug
slugs = Slugs.objects.filter(slug1=slug, description=text_en)
if len(slugs) <= 0:
slug_db = Slugs(slug1=slug, description=text_en)
slug_db.save()
else:
slug_db = slugs[0]
visible_default = False
if row[10].value:
if str(row[10].value).lower() == "visible":
visible_default = True
is_category = None
is_stats = None
if type == self.QUESTION:
is_stats = True
is_category = False
elif type == self.CATEGORY:
is_stats = False
is_category = True
try:
question = Question.objects.get(slug_fk__slug1=slug_db.slug1, questionset=questionset)
question.text_en = text_en
question.number = str(questionNumber)
question.type = dataType_column.value
question.help_text = helpText
question.stats = is_stats
question.category = is_category
question.tooltip = _tooltip
question.checks = _checks
question.visible_default = visible_default
except Question.DoesNotExist:
question = Question(
questionset=questionset,
text_en=text_en,
number=str(questionNumber),
type=dataType_column.value,
help_text=helpText,
slug=slug,
slug_fk=slug_db,
stats=is_stats,
category=is_category,
tooltip=_tooltip,
checks=_checks,
visible_default=visible_default,
disposition=self.__processDisposition(row[11].value.lower()),
)
if dataType_column.value in ["open-validated"]:
ardict = {}
if row[4].value:
# some basic types dont need regex
known_validations = {
"integer": "[+-]?\d+",
"decimal": "[+-]?\d*([.]\d*)?",
"scientific": "[+-]?\d*([.]\d*)?e[+-]?\d*([.]\d*)?",
"range": "[+\-]?\d*([.]\d*);[+\-]?\d*([.]\d*)",
"date": "\d{2}/\d{2}/\d{4}",
"time": "\d{2}:\d{2}:\d{2}",
"datetime": "\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2}",
"text": ".*",
}
try:
ardict["regex"] = known_validations[row[4].value]
ardict["base"] = row[4].value
except KeyError:
# If this is not known, try to validate it as a regex
try:
re.compile(row[4].value)
ardict["regex"] = row[4].value
except re.error:
raise Exception("--ERROR: The regex on row %d, column 4 is not valid" % (type_Column.row))
if row[5].value:
split = row[5].value.split("|")
lensplit = len(split)
if lensplit == 1:
ardict["unit"] = split[0]
question.help_text = ""
elif lensplit == 2:
ardict["unit"] = split[0]
ardict["unit_desc"] = split[1]
question.help_text = ""
elif lensplit == 3:
ardict["unit"] = split[0]
ardict["unit_desc"] = split[1]
question.help_text = split[2]
else:
raise Exception(
"-- ERROR: Invalid number of segments on help text row %d, column 5. Max syntax is unit|desc|help_text"
% (type_Column.row)
)
question.metadata = json.dumps(ardict)
if not _debug:
question.save()
if type == self.QUESTION:
log += "\n%s - Question created %s " % (type_Column.row, question)
elif type == self.CATEGORY:
log += "\n%s - Category created %s " % (type_Column.row, question)
_questions_rows[slug] = question
if type == self.QUESTION:
if dataType_column.value in [
"choice",
"choice-freeform",
"choice-multiple",
"choice-multiple-freeform",
]:
_choices_array_aux = []
# Parse of values list
values_list = row[4]
if values_list != None and values_list.value != None:
list_aux = values_list.value.split("|")
_choices_array[slug] = self.__processChoices(
type_Column.row,
question,
list_aux,
log,
debug=_debug,
mode=mode,
match_percentage=percentage,
infer_function=infer_function,
)
if dataType_column.value in ["choice-yesno", "choice-yesnocomment", "choice-yesnodontknow"]:
_choices_array[slug] = ["yes", "no", "dontknow"]
except:
log += "\n%s - Error to save question %s" % (type_Column.row, text_en)
self.writeLog(log)
raise
