def create_user_checklist_items(apps, schema_editor):
# prerequisites:
# - convert checklist_item / checklist_item_event entries into into
# checklistitem.* vocabularies (migration 0003)
# - create the user_checklist_item table (0002)
ChecklistItem = apps.get_model('db','ChecklistItem')
UserChecklistItem = apps.get_model('db','UserChecklistItem')
ci_group_map = {}
for obj in ChecklistItem.objects.all().distinct('checklist_item_group'):
key = default_converter(obj.checklist_item_group)
ci_group_map[obj.checklist_item_group] = key
ci_name_map = {}
for obj in ChecklistItem.objects.all().distinct('item_name'):
key = default_converter(obj.item_name)
ci_name_map[obj.item_name] = key
# create entries in the user_checklist_item table
# note: status values are hard-coded to correspond to the vocabulary
# keys (created in migration 0002)
sql_keys = [
'suid','cigroup','ciname',
'su_username','admin_username','admin_suid','admin_upid',
'date_performed', 'date_created','status','is_notified'
]
sql = '''
select
screening_room_user_id,
ci.checklist_item_group,
ci.item_name,
su.username su_username,
admin.username admin_username,
admin.screensaver_user_id admin_suid,
up.id admin_upid,
cie.date_performed,
cie.date_created,
case when cie.is_not_applicable then 'n_a'
when ci.is_expirable and cie.date_performed is not null then
case when cie.is_expiration then 'deactivated' else 'activated' end
when cie.date_performed is not null then 'completed'
else 'not_completed'
end as status,
(
select 1 from screening_room_user sru
where sru.last_notified_smua_checklist_item_event_id = cie.checklist_item_event_id
UNION
select 1 from screening_room_user sru
where sru.last_notified_rnaiua_checklist_item_event_id = cie.checklist_item_event_id
) as is_notified
from checklist_item ci
join checklist_item_event cie using(checklist_item_id)
join screensaver_user su on screening_room_user_id=su.screensaver_user_id
join screensaver_user admin on cie.created_by_id=admin.screensaver_user_id
left join reports_userprofile up on up.id=admin.user_id
order by screening_room_user_id, checklist_item_group, item_name, cie.date_performed asc;
'''
connection = schema_editor.connection
cursor = connection.cursor()
log_ref_resource_name = 'userchecklistitem'
_dict = None
log = None
uci_hash = {}
unique_log_keys = set()
try:
cursor.execute(sql)
i = 0
for row in cursor:
_dict = dict(zip(sql_keys,row))
key = '/'.join([str(_dict['suid']),_dict['cigroup'],_dict['ciname']])
previous_dict = uci_hash.get(key)
logger.debug('previous_dict: %s:%s' % (key,previous_dict))
date_time = pytz.timezone('US/Eastern').localize(_dict['date_created'])
if date_time.date() != _dict['date_performed']:
# only use the less accurate date_performed date if that date
# is not equal to the date_created date
date_time = pytz.timezone('US/Eastern').localize(
datetime.datetime.combine(
_dict['date_performed'],
datetime.datetime.min.time()))
if previous_dict:
uci = previous_dict['obj']
uci.admin_user_id = int(_dict['admin_suid'])
uci.status = _dict['status']
uci.previous_status = previous_dict['status']
if(previous_dict['is_notified']):
# notified date will be this event - 60 days (smua/rnaiua)
uci.status_notified_date = (
_dict['date_performed'] - datetime.timedelta(days=60))
uci.status_date = _dict['date_performed']
logger.debug('saving, dict: %s, prev_dict: %s, status date %s, status_notified: %s',
_dict, previous_dict, uci.status_date, uci.status_notified_date)
uci.save()
logger.debug('update uci: %s,%s,%s,%s',
uci.status,uci.status_date,uci.previous_status,uci.status_notified_date)
else:
uci_hash[key] = _dict
logger.debug(str(('create user checklist item', _dict,
_dict['date_performed'].isoformat())))
uci = UserChecklistItem.objects.create(
screensaver_user_id = int(_dict['suid']),
admin_user_id = int(_dict['admin_suid']),
item_group = ci_group_map[_dict['cigroup']],
item_name = ci_name_map[_dict['ciname']],
status = _dict['status'],
status_date = _dict['date_performed'])
uci.save()
_dict['obj'] = uci
logger.debug('created uci: %s,%s,%s', uci.status, uci.status_date)
i += 1
# create the apilog for this item
log = ApiLog()
log.ref_resource_name = log_ref_resource_name
log.key = '/'.join([_dict['su_username'],uci.item_group,uci.item_name])
log.username = _dict['admin_username']
log.user_id = _dict['admin_upid']
log.date_time = date_time
log.api_action = 'PATCH'
log.uri = '/'.join([log.ref_resource_name,log.key])
log.comment = 'status=%s' % _dict['status']
# is the key (date_time, actually) unique?
full_key = '/'.join([log.ref_resource_name,log.key,str(log.date_time)])
while full_key in unique_log_keys:
# add a second to make it unique; because date performed is a date,
logger.info(str(('time collision for: ',full_key)))
log.date_time = log.date_time + datetime.timedelta(0,1)
full_key = '/'.join([log.ref_resource_name,log.key,str(log.date_time)])
unique_log_keys.add(full_key)
if previous_dict:
diff_keys = ['status']
diffs = {}
logger.debug(str(('found previous_dict', previous_dict)))
diff_keys.append('admin_username')
diffs['admin_username'] = [previous_dict['admin_username'], _dict['admin_username']]
diff_keys.append('status_date')
diffs['status_date'] = [
previous_dict['date_performed'].isoformat(),
_dict['date_performed'].isoformat()]
diffs['status'] = [previous_dict['status'],_dict['status']]
diff_keys.append('previous_status')
diffs['previous_status'] = [ None, previous_dict['status']]
log.diff_keys = json.dumps(diff_keys)
log.diffs = json.dumps(diffs)
logger.debug('create log: %s', log)
log.save()
log = None
if i%1000 == 0:
logger.info(str(('created', i, 'logs')))
except Exception, e:
logger.exception('migration exc')
raise e
def create_user_checklist_items(apps, schema_editor):
# prerequisites:
# - convert checklist_item / checklist_item_event entries into into
# checklistitem.* vocabularies (migration 0003)
# - create the user_checklist_item table (0002)
ChecklistItem = apps.get_model('db', 'ChecklistItem')
UserChecklistItem = apps.get_model('db', 'UserChecklistItem')
ci_group_map = {}
for obj in ChecklistItem.objects.all().distinct('checklist_item_group'):
key = default_converter(obj.checklist_item_group)
ci_group_map[obj.checklist_item_group] = key
ci_name_map = {}
for obj in ChecklistItem.objects.all().distinct('item_name'):
key = default_converter(obj.item_name)
ci_name_map[obj.item_name] = key
# create entries in the user_checklist_item table
# note: status values are hard-coded to correspond to the vocabulary
# keys (created in migration 0002)
sql_keys = [
'suid', 'cigroup', 'ciname', 'su_username', 'admin_username',
'admin_suid', 'admin_upid', 'date_performed', 'date_created', 'status',
'is_notified'
]
sql = '''
select
screening_room_user_id,
ci.checklist_item_group,
ci.item_name,
su.username su_username,
admin.username admin_username,
admin.screensaver_user_id admin_suid,
up.id admin_upid,
cie.date_performed,
cie.date_created,
case when cie.is_not_applicable then 'n_a'
when ci.is_expirable and cie.date_performed is not null then
case when cie.is_expiration then 'deactivated' else 'activated' end
when cie.date_performed is not null then 'completed'
else 'not_completed'
end as status,
(
select 1 from screening_room_user sru
where sru.last_notified_smua_checklist_item_event_id = cie.checklist_item_event_id
UNION
select 1 from screening_room_user sru
where sru.last_notified_rnaiua_checklist_item_event_id = cie.checklist_item_event_id
) as is_notified
from checklist_item ci
join checklist_item_event cie using(checklist_item_id)
join screensaver_user su on screening_room_user_id=su.screensaver_user_id
join screensaver_user admin on cie.created_by_id=admin.screensaver_user_id
left join reports_userprofile up on up.id=admin.user_id
order by screening_room_user_id, checklist_item_group, item_name, cie.date_performed asc;
'''
connection = schema_editor.connection
cursor = connection.cursor()
log_ref_resource_name = 'userchecklistitem'
_dict = None
log = None
uci_hash = {}
unique_log_keys = set()
try:
cursor.execute(sql)
i = 0
for row in cursor:
_dict = dict(zip(sql_keys, row))
key = '/'.join(
[str(_dict['suid']), _dict['cigroup'], _dict['ciname']])
previous_dict = uci_hash.get(key)
logger.debug('previous_dict: %s:%s' % (key, previous_dict))
date_time = pytz.timezone('US/Eastern').localize(
_dict['date_created'])
if date_time.date() != _dict['date_performed']:
# only use the less accurate date_performed date if that date
# is not equal to the date_created date
date_time = pytz.timezone('US/Eastern').localize(
datetime.datetime.combine(_dict['date_performed'],
datetime.datetime.min.time()))
if previous_dict:
uci = previous_dict['obj']
uci.admin_user_id = int(_dict['admin_suid'])
uci.status = _dict['status']
uci.previous_status = previous_dict['status']
if (previous_dict['is_notified']):
# notified date will be this event - 60 days (smua/rnaiua)
uci.status_notified_date = (_dict['date_performed'] -
datetime.timedelta(days=60))
uci.status_date = _dict['date_performed']
logger.debug(
'saving, dict: %s, prev_dict: %s, status date %s, status_notified: %s',
_dict, previous_dict, uci.status_date,
uci.status_notified_date)
uci.save()
logger.debug('update uci: %s,%s,%s,%s', uci.status,
uci.status_date, uci.previous_status,
uci.status_notified_date)
else:
uci_hash[key] = _dict
logger.debug(
str(('create user checklist item', _dict,
_dict['date_performed'].isoformat())))
uci = UserChecklistItem.objects.create(
screensaver_user_id=int(_dict['suid']),
admin_user_id=int(_dict['admin_suid']),
item_group=ci_group_map[_dict['cigroup']],
item_name=ci_name_map[_dict['ciname']],
status=_dict['status'],
status_date=_dict['date_performed'])
uci.save()
_dict['obj'] = uci
logger.debug('created uci: %s,%s,%s', uci.status,
uci.status_date)
i += 1
# create the apilog for this item
log = ApiLog()
log.ref_resource_name = log_ref_resource_name
log.key = '/'.join(
[_dict['su_username'], uci.item_group, uci.item_name])
log.username = _dict['admin_username']
log.user_id = _dict['admin_upid']
log.date_time = date_time
log.api_action = 'PATCH'
log.uri = '/'.join([log.ref_resource_name, log.key])
log.comment = 'status=%s' % _dict['status']
# is the key (date_time, actually) unique?
full_key = '/'.join(
[log.ref_resource_name, log.key,
str(log.date_time)])
while full_key in unique_log_keys:
# add a second to make it unique; because date performed is a date,
logger.info(str(('time collision for: ', full_key)))
log.date_time = log.date_time + datetime.timedelta(0, 1)
full_key = '/'.join(
[log.ref_resource_name, log.key,
str(log.date_time)])
unique_log_keys.add(full_key)
if previous_dict:
diff_keys = ['status']
diffs = {}
logger.debug(str(('found previous_dict', previous_dict)))
diff_keys.append('admin_username')
diffs['admin_username'] = [
previous_dict['admin_username'], _dict['admin_username']
]
diff_keys.append('status_date')
diffs['status_date'] = [
previous_dict['date_performed'].isoformat(),
_dict['date_performed'].isoformat()
]
diffs['status'] = [previous_dict['status'], _dict['status']]
diff_keys.append('previous_status')
diffs['previous_status'] = [None, previous_dict['status']]
log.diff_keys = json.dumps(diff_keys)
log.diffs = json.dumps(diffs)
logger.debug('create log: %s', log)
log.save()
log = None
if i % 1000 == 0:
logger.info(str(('created', i, 'logs')))
except Exception, e:
logger.exception('migration exc')
raise e
