def import_cases_and_attributes(self):
""" Import from a csv file with the cases and any attributes.
The csv file must have a header row which details the attribute names.
The csv file must be comma delimited. The first column must have the case ids.
The attribute types are calculated from the data.
"""
if self.cases != []:
logger.warning(_("Cases have already been created."))
filename = QtWidgets.QFileDialog.getOpenFileName(
None, _('Select attributes file'), self.app.settings['directory'],
"(*.csv)")[0]
if filename == "":
return
if filename[-4:].lower() != ".csv":
msg = filename + "\n" + _("is not a .csv file. File not imported")
Message(self.app, _("Warning"), msg, "warning").exec_()
self.parent_textEdit.append(msg)
return
values = []
with open(filename, 'r', newline='') as f:
reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_MINIMAL)
try:
for row in reader:
values.append(row)
except csv.Error as e:
logger.warning(
('file %s, line %d: %s' % (filename, reader.line_num, e)))
if len(values) <= 1:
logger.info(_("Cannot import from csv, only one row in file"))
return
now_date = str(
datetime.datetime.now().astimezone().strftime("%Y-%m-%d %H:%M:%S"))
header = values[0]
values = values[1:]
# insert cases
cur = self.app.conn.cursor()
for v in values:
item = {
'name': v[0],
'memo': "",
'owner': self.app.settings['codername'],
'date': now_date
}
try:
cur.execute(
"insert into cases (name,memo,owner,date) values(?,?,?,?)",
(item['name'], item['memo'], item['owner'], item['date']))
self.app.conn.commit()
cur.execute("select last_insert_rowid()")
item['caseid'] = cur.fetchone()[0]
self.cases.append(item)
except Exception as e:
logger.error("item:" + str(item) + ", " + str(e))
# determine attribute type
attribute_value_type = ["character"] * len(header)
for col, att_name in enumerate(header):
numeric = True
for val in values:
try:
float(val[col])
except ValueError:
numeric = False
if numeric:
attribute_value_type[col] = "numeric"
# insert attribute types
for col, att_name in enumerate(header):
if col > 0:
try:
cur.execute(
"insert into attribute_type (name,date,owner,memo, \
valueType, caseOrFile) values(?,?,?,?,?,?)",
(att_name, now_date, self.app.settings['codername'],
"", attribute_value_type[col], 'case'))
self.app.conn.commit()
except Exception as e:
logger.error(_("attribute:") + att_name + ", " + str(e))
# insert attributes
sql = "select name, caseid from cases"
cur.execute(sql)
name_and_ids = cur.fetchall()
for n_i in name_and_ids:
for v in values:
if n_i[0] == v[0]:
for col in range(1, len(v)):
sql = "insert into attribute (name, value, id, attr_type, date, owner) values (?,?,?,?,?,?)"
cur.execute(sql,
(header[col], v[col], n_i[1], 'case',
now_date, self.app.settings['codername']))
self.app.conn.commit()
self.load_cases_and_attributes()
self.fill_tableWidget()
msg = _("Cases and attributes imported from: ") + filename
self.app.delete_backup = False
self.parent_textEdit.append(msg)
logger.info(msg)
def insert_data(self):
""" Insert case, attributes, attribute values and qualitative text. """
now_date = str(
datetime.datetime.now().astimezone().strftime("%Y-%m-%d %H:%M:%S"))
cur = self.app.conn.cursor()
name_and_caseids = []
for i, c in enumerate(self.data):
try:
self.ui.label_msg.setText(_("Inserting cases: " + str(i)))
cur.execute(
"insert into cases (name,memo,owner,date) values(?,?,?,?)",
(c[0], "", self.app.settings['codername'], now_date))
self.app.conn.commit()
cur.execute("select last_insert_rowid()")
name_and_caseids.append([c[0], cur.fetchone()[0]])
QtWidgets.QApplication.processEvents()
except sqlite3.IntegrityError as e:
self.fail_msg = str(e) + _(
" - Duplicate case names, either in the file, or duplicates with existing cases in the project"
)
logger.error(_("Survey not loaded: ") + self.fail_msg)
mb = QtWidgets.QMessageBox()
mb.setIcon(QtWidgets.QMessageBox.Warning)
mb.setStyleSheet("* {font-size:" +
str(self.app.settings['fontsize']) + "pt} ")
mb.setWindowTitle(_('Survey not loaded'))
mb.setText(self.fail_msg)
mb.exec_()
self.parent_textEdit.append(
_("Survey not loaded: ") + self.fail_msg)
return
# insert non-qualitative attribute types, except if they are already present
sql = "select name from attribute_type where caseOrFile='case'"
cur.execute(sql)
result = cur.fetchall()
existing_attr_names = []
for r in result:
existing_attr_names.append(r[0])
sql = "insert into attribute_type (name,date,owner,memo, valueType, caseOrFile) values(?,?,?,?,?,?)"
for col, name in enumerate(self.fields):
if self.fields_type[
col] != "qualitative" and col > 0: # col==0 is the case identifier
if name not in existing_attr_names:
logger.debug(name +
" is not in case attribute_types. Adding.")
cur.execute(
sql, (name, now_date, self.app.settings['codername'],
"", self.fields_type[col], 'case'))
self.app.conn.commit()
# Look for pre-existing attributes that are not in the survey and insert blank value rows if present
survey_field_names = []
for col, fld_name in enumerate(self.fields):
if self.fields_type[col] != "qualitative" and col > 0:
survey_field_names.append(fld_name)
for name in existing_attr_names:
if name not in survey_field_names:
for name_id in name_and_caseids:
sql = "insert into attribute (name, value, id, attr_type, date, owner) values (?,'',?,?,?,?)"
cur.execute(sql, (name, name_id[1], 'case', now_date,
self.app.settings['codername']))
self.app.conn.commit()
# insert non-qualitative values to each case using caseids
sql = "insert into attribute (name, value, id, attr_type, date, owner) values (?,?,?,?,?,?)"
for i, name_id in enumerate(name_and_caseids):
self.ui.label_msg.setText(
_("Inserting attributes to cases: ") + str(i))
for val in self.data:
if name_id[0] == val[0]:
for col in range(1, len(val)):
if self.fields_type[col] != "qualitative":
cur.execute(sql, (self.fields[col], val[col],
name_id[1], 'case', now_date,
self.app.settings['codername']))
QtWidgets.QApplication.processEvents()
self.app.conn.commit()
# insert qualitative data into source table
self.ui.label_msg.setText(_("Creating qualitative text file"))
source_sql = "insert into source(name,fulltext,memo,owner,date, mediapath) values(?,?,?,?,?, Null)"
for field in range(1, len(self.fields)): # column 0 is for identifiers
case_text_list = []
if self.fields_type[field] == "qualitative":
self.fields[field]
# create one text file combining each row, prefix [case identifier] to each row.
pos0 = 0
pos1 = 0
fulltext = ""
for row in range(0, len(self.data)):
if self.data[row][field] != "":
fulltext += "[" + str(self.data[row][0]) + "] "
pos0 = len(fulltext) - 1
fulltext += str(self.data[row][field]) + "\n\n"
pos1 = len(fulltext) - 2
case_text = [
self.app.settings['codername'], now_date, "", pos0,
pos1, name_and_caseids[row][1]
]
case_text_list.append(case_text)
# add the current time to the file name to ensure uniqueness and to
# prevent sqlite Integrity Error. Do not use now_date which contains colons
now = str(datetime.datetime.now().astimezone().strftime(
"%Y-%m-%d %H-%M-%S"))
cur.execute(source_sql,
(self.fields[field] + "_" + now, fulltext, "",
self.app.settings['codername'], now_date))
self.app.conn.commit()
cur.execute("select last_insert_rowid()")
fid = cur.fetchone()[0]
case_text_sql = "insert into case_text (owner, date, memo, pos0, pos1, caseid, fid) values(?,?,?,?,?,?,?)"
for case_text in case_text_list:
case_text.append(fid)
cur.execute(case_text_sql, case_text)
self.app.conn.commit()
logger.info(_("Survey imported"))
self.parent_textEdit.append(_("Survey imported."))
Message(self.app, _("Survey imported"), _("Survey imported")).exec_()
self.app.delete_backup = False
