class Course(models.Model):
course_name = models.CharField("课程名称", max_length=200, primary_key=True)
day_slot = models.CharField("上课日期", max_length=5, choices=DAYSLOT)
time_slot = models.CharField("上课时间", max_length=5, choices=TIMESLOT)
course_cat = models.CharField("课程类别", max_length=20, choices=CAT)
gender = models.CharField("性别要求", max_length=5, choices=CLS_GENDER)
special_req = models.ForeignKey(
SpecialReq,
default=None,
blank=True,
null=True,
on_delete=models.SET_NULL,
verbose_name="特殊要求",
)
cur_number = models.IntegerField("已注册人数", default=0)
max_number = models.IntegerField("最大容量", default=100)
add_number = models.IntegerField("排课新增人数", default=0)
auto_match = models.BooleanField("参与自动排课", default=True)
objects = BulkUpdateManager()
def __str__(self):
return self.course_name
class Meta:
verbose_name = '课程'
verbose_name_plural = '课程'
class OfficerHistory(TimeStampsModel):
officer = models.ForeignKey('data.Officer',
on_delete=models.CASCADE,
null=True)
unit = models.ForeignKey('data.PoliceUnit',
on_delete=models.CASCADE,
null=True)
effective_date = models.DateField(null=True)
end_date = models.DateField(null=True)
objects = BulkUpdateManager()
class Meta:
indexes = [
models.Index(fields=['end_date']),
models.Index(fields=['effective_date']),
]
@property
def unit_name(self):
return self.unit.unit_name
@property
def unit_description(self):
return self.unit.description
def __new__(cls, name, bases, attrs):
global current_dataset
primary_base = bases[0]
mixins = bases[1:]
# Don't modify base models (i.e. the ones in this file)
if current_dataset is None:
# Don't create a table for base models
class Meta:
abstract = True
attrs['Meta'] = Meta
attrs['objects'] = BulkUpdateManager()
return super(DatasetMeta, cls).__new__(cls, name, bases, attrs)
# Model-specific fields
if primary_base is Frame:
attrs['video'] = ForeignKey(current_dataset.Video)
class Meta:
unique_together = ('video', 'number')
attrs['Meta'] = Meta
if primary_base is Noun:
attrs['frame'] = ForeignKey(current_dataset.Frame)
elif primary_base is Attribute:
attrs['labeler'] = ForeignKey(current_dataset.Labeler)
elif primary_base is Track:
attrs['video'] = ForeignKey(current_dataset.Video)
attrs['labeler'] = ForeignKey(current_dataset.Labeler)
# Add mixins
for mixin in mixins:
for k, v in mixin.__dict__.iteritems():
if not hasattr(super(mixin), k) and k not in [
'__dict__', '__weakref__', '__module__'
]:
attrs[k] = v
# Initialize foreign keys with class name
for key, val in attrs.iteritems():
if isinstance(val, ForeignKey) or isinstance(val, ManyToManyField):
attrs[key] = val._make_key(name)
# Prefix class name with dataset name to avoid clashes across datasets
namespaced_name = '{}_{}'.format(current_dataset.name, name)
# Call out to Django ModelBase to create the class/table
new_cls = super(DatasetMeta, cls).__new__(cls, namespaced_name,
tuple(bases), attrs)
# Remember the created class on our Dataset object
current_dataset.register_model(name, new_cls)
return new_cls
class AllegationCategory(TimeStampsModel):
category_code = models.CharField(max_length=255)
category = models.CharField(max_length=255, blank=True)
allegation_name = models.CharField(max_length=255, blank=True)
on_duty = models.BooleanField(default=False)
citizen_dept = models.CharField(max_length=50,
default=CITIZEN_CHOICE,
choices=CITIZEN_DEPTS)
objects = BulkUpdateManager()
class ProgressVideo(models.Model):
minutes_daily_play = models.IntegerField()
course = models.CharField(max_length=50)
user = models.CharField(max_length=50)
dia = models.DateField()
category = models.CharField(max_length=50)
id_inscription = models.ForeignKey(Inscription, on_delete=models.CASCADE)
id_category = models.ForeignKey(Category, on_delete=models.CASCADE, null=True)
objects = BulkUpdateManager()
class InvestigatorAllegation(TimeStampsModel):
investigator = models.ForeignKey('data.Investigator',
on_delete=models.CASCADE)
allegation = models.ForeignKey('data.Allegation', on_delete=models.CASCADE)
current_star = models.CharField(max_length=10, null=True)
current_rank = models.CharField(max_length=100, null=True)
current_unit = models.ForeignKey('data.PoliceUnit',
on_delete=models.SET_NULL,
null=True)
investigator_type = models.CharField(max_length=32, null=True)
objects = BulkUpdateManager()
class TRRAttachmentRequest(TimeStampsModel):
trr = models.ForeignKey('trr.TRR', on_delete=models.CASCADE)
email = models.EmailField(max_length=255)
status = models.BooleanField(default=False)
airtable_id = models.CharField(max_length=255, blank=True, default='')
objects = BulkUpdateManager()
class Meta:
unique_together = (('trr', 'email'),)
def __str__(self):
return f'{self.email} - {self.trr.id}'
class OfficerBadgeNumber(TimeStampsModel):
officer = models.ForeignKey('data.Officer',
on_delete=models.CASCADE,
null=True)
star = models.CharField(max_length=10)
current = models.BooleanField(default=False)
objects = BulkUpdateManager()
class Meta:
indexes = [
models.Index(fields=['current']),
]
def __str__(self):
return f'{self.officer} - {self.star}'
class Person(models.Model):
"""
test model
"""
role = models.ForeignKey(Role, null=True, on_delete=models.CASCADE)
big_age = models.BigIntegerField()
#comma_separated_age = models.CommaSeparatedIntegerField(max_length=255)
age = models.IntegerField()
positive_age = models.PositiveIntegerField()
positive_small_age = models.PositiveSmallIntegerField()
small_age = models.SmallIntegerField()
height = models.DecimalField(max_digits=3, decimal_places=2)
float_height = models.FloatField()
certified = models.BooleanField(default=False)
null_certified = models.NullBooleanField()
name = models.CharField(max_length=140, blank=True, null=True)
email = models.EmailField()
file_path = models.FilePathField()
slug = models.SlugField()
text = models.TextField()
url = models.URLField()
auto_now = models.DateTimeField(null=True, blank=True, auto_now=True)
date_time = models.DateTimeField(null=True, blank=True)
date = models.DateField(null=True, blank=True)
time = models.TimeField(null=True, blank=True)
remote_addr = models.GenericIPAddressField(null=True, blank=True)
my_file = models.FileField(upload_to='some/path/', null=True, blank=True)
image = models.ImageField(upload_to='some/path/', null=True, blank=True)
data = JSONField(null=True, blank=True)
default = models.IntegerField(null=True,
blank=True,
help_text="A reserved keyword")
jobs = models.ManyToManyField('Company',
blank=True,
related_name='workers')
objects = BulkUpdateManager()
class Victim(TimeStampsModel):
allegation = models.ForeignKey('data.Allegation',
on_delete=models.CASCADE,
related_name='victims')
gender = models.CharField(max_length=1, blank=True)
race = models.CharField(max_length=50,
default='Unknown',
validators=[validate_race])
age = models.IntegerField(null=True)
birth_year = models.IntegerField(null=True)
objects = BulkUpdateManager()
@property
def gender_display(self):
try:
return GENDER_DICT[self.gender]
except KeyError:
return self.gender
class Question(models.Model):
title = models.CharField(max_length=128)
text = HTMLField()
created_at = models.DateTimeField(auto_now_add=True)
author = models.ForeignKey(User, on_delete=models.CASCADE)
answer_count = models.DecimalField(max_digits=3,
decimal_places=0,
default=0)
votes = GenericRelation(Vote, related_query_name='questions')
rate = models.IntegerField(default=0)
tags = models.ManyToManyField(to='Tag', related_name='questions')
objects = QuestionsManager()
bulk_objects = BulkUpdateManager()
def __str__(self):
return '[pk={}] {}'.format(self.pk, self.title)
def get_absolute_url(self):
return reverse("question", kwargs={"pk": self.pk})
class DeletionCheckItem(models.Model):
objects = BulkUpdateManager()
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
op = models.PositiveSmallIntegerField(verbose_name=u'操作类型', default=OpStatus.DELETED)
x = models.PositiveSmallIntegerField(verbose_name=u'X坐标', default=0)
y = models.PositiveSmallIntegerField(verbose_name=u'Y坐标', default=0)
w = models.PositiveSmallIntegerField(verbose_name=u'宽度', default=1)
h = models.PositiveSmallIntegerField(verbose_name=u'高度', default=1)
ocolumn_uri = models.CharField(verbose_name='行图片路径', max_length=128, blank=False)
ocolumn_x = models.PositiveSmallIntegerField(verbose_name=u'行图X坐标', default=0)
ocolumn_y = models.PositiveSmallIntegerField(verbose_name=u'行图Y坐标', default=0)
ch = models.CharField(null=True, blank=True, verbose_name=u'文字', max_length=2, default='')
rect_id = models.CharField(verbose_name='字块CID', max_length=128, blank=False)
modifier = models.ForeignKey(Staff, null=True, blank=True, related_name='modify_deletions', verbose_name=u'修改人')
verifier = models.ForeignKey(Staff, null=True, blank=True, related_name='verify_deletions', verbose_name=u'审定人')
result = models.PositiveSmallIntegerField(null=True, blank=True, verbose_name=u'审定意见', default=ReviewResult.INITIAL) # 1 同意 2 不同意
del_task = models.ForeignKey(DelTask, null=True, blank=True, related_name='del_task_items', on_delete=models.SET_NULL,
verbose_name=u'删框任务')
created_at = models.DateTimeField(verbose_name='删框时间', auto_now_add=True)
updated_at = models.DateTimeField(null=True, blank=True, verbose_name=u'更新时间', auto_now=True)
@classmethod
def create_from_rect(cls, rects, t):
rect_ids = [rect ['id'] for rect in filter(lambda x: x['op'] == 3, rects)]
for r in Rect.objects.filter(id__in=rect_ids):
DeletionCheckItem(x=r.x, y=r.y, w=r.w, h=r.h, ocolumn_uri=r.column_uri(),
ocolumn_x=r.column_set['x'], ocolumn_y=r.column_set['y'], ch=r.ch,
rect_id=r.id, modifier=t.owner).save()
def undo(self):
Rect.objects.filter(pk=self.rect_id).update(op=2)
def confirm(self):
Rect.objects.filter(pk=self.rect_id).all().delete()
class Meta:
verbose_name = u"删框记录"
verbose_name_plural = u"删框记录管理"
def __new__(cls, name, bases, attrs):
global current_dataset
base = bases[0]
is_base_class = base is models.Model or base is object
if is_base_class:
if not 'Meta' in attrs:
class Meta:
abstract = True
attrs['Meta'] = Meta
attrs['objects'] = BulkUpdateManager()
child_name = name
else:
if base.__name__ == 'Frame':
attrs['video'] = ForeignKey(current_dataset.Video, name)
class Meta:
unique_together = ('video', 'number')
attrs['Meta'] = Meta
child_name = '{}_{}'.format(current_dataset.name, name)
if base.__name__ != 'Concept':
new_cls = super(BaseMeta, cls).__new__(cls, child_name, bases, attrs)
if not is_base_class:
if base.__name__ == 'Concept':
new_cls = register_concept(name, attrs)
elif base.__name__ == 'Model':
current_dataset.other.append(name)
setattr(current_dataset, name, new_cls)
return new_cls
class Investigator(TimeStampsModel):
first_name = models.CharField(max_length=255, db_index=True, null=True)
last_name = models.CharField(max_length=255, db_index=True, null=True)
middle_initial = models.CharField(max_length=5, null=True)
suffix_name = models.CharField(max_length=5, null=True)
appointed_date = models.DateField(null=True)
officer = models.ForeignKey('data.Officer', on_delete=models.SET_NULL, null=True)
gender = models.CharField(max_length=1, blank=True)
race = models.CharField(max_length=50, default='Unknown', validators=[validate_race])
objects = BulkUpdateManager()
@property
def num_cases(self):
return self.investigatorallegation_set.count()
@property
def full_name(self):
return f'{self.first_name} {self.last_name}'
@property
def abbr_name(self):
return f'{self.first_name[0].upper()}. {self.last_name}'
class AttachmentRequest(TimeStampsModel):
allegation = models.ForeignKey('data.Allegation', on_delete=models.CASCADE)
email = models.EmailField(max_length=255)
status = models.BooleanField(default=False)
airtable_id = models.CharField(max_length=255, blank=True, default='')
noti_email_sent = models.BooleanField(
default=False, verbose_name='Notification email sent')
bulk_objects = BulkUpdateManager()
objects = AttachmentRequestManager()
class Meta:
unique_together = (('allegation', 'email'), )
def __str__(self):
return f'{self.email} - {self.allegation.crid}'
@property
def crid(self):
return self.allegation.crid
def save(self, *args, **kwargs):
self.full_clean()
super(AttachmentRequest, self).save(*args, **kwargs)
def investigator_names(self):
investigatorallegation_set = self.allegation.investigatorallegation_set.select_related(
'investigator')
investigators = [
ia.investigator.full_name
for ia in investigatorallegation_set.all()
]
return ', '.join(investigators)
investigator_names.short_description = 'Investigators'
class Allegation(TimeStampsModel):
crid = models.CharField(max_length=30, primary_key=True)
summary = models.TextField(blank=True)
location = models.CharField(max_length=64, blank=True)
add1 = models.CharField(max_length=16, blank=True)
add2 = models.CharField(max_length=255, blank=True)
city = models.CharField(max_length=255, blank=True)
incident_date = models.DateTimeField(null=True)
areas = models.ManyToManyField('data.Area')
line_areas = models.ManyToManyField('data.LineArea')
point = models.PointField(srid=4326, null=True)
beat = models.ForeignKey('data.Area', on_delete=models.SET_NULL, null=True, related_name='beats')
source = models.CharField(blank=True, max_length=20)
is_officer_complaint = models.BooleanField(default=False)
old_complaint_address = models.CharField(max_length=255, null=True)
police_witnesses = models.ManyToManyField('data.Officer', through='PoliceWitness')
subjects = ArrayField(models.CharField(max_length=255), default=list)
is_extracted_summary = models.BooleanField(default=False)
# CACHED COLUMNS
most_common_category = models.ForeignKey('data.AllegationCategory', on_delete=models.SET_NULL, null=True)
first_start_date = models.DateField(null=True)
first_end_date = models.DateField(null=True)
coaccused_count = models.IntegerField(default=0, null=True)
objects = BulkUpdateManager()
def __str__(self):
return f'CRID {self.crid}'
@property
def category_names(self):
query = self.officer_allegations.annotate(
name=models.Case(
models.When(allegation_category__isnull=True, then=models.Value('Unknown')),
default='allegation_category__category',
output_field=models.CharField()))
query = query.values('name').distinct()
results = sorted([result['name'] for result in query])
return results if results else ['Unknown']
@property
def address(self):
if self.old_complaint_address:
return self.old_complaint_address
result = ''
add1 = self.add1.strip()
add2 = self.add2.strip()
city = self.city.strip()
if add1:
result = add1
if add2:
result = ' '.join(filter(None, [result, add2]))
if city:
result = ', '.join(filter(None, [result, city]))
return result
@property
def officer_allegations(self):
return self.officerallegation_set.all()
@property
def complainants(self):
return self.complainant_set.all()
@property
def complainant_races(self):
query = self.complainant_set.annotate(
name=models.Case(
models.When(race__in=['n/a', 'n/a ', 'nan', ''], then=models.Value('Unknown')),
default='race',
output_field=models.CharField()))
query = query.values('name').distinct()
results = sorted([result['name'] for result in query])
return results if results else ['Unknown']
@property
def complainant_age_groups(self):
results = self.complainant_set.annotate(name=get_num_range_case('age', [0, 20, 30, 40, 50]))
results = results.values('name').distinct()
results = [result['name'] for result in results]
return results if results else ['Unknown']
@property
def complainant_genders(self):
query = self.complainant_set.annotate(
name=models.Case(
models.When(gender='', then=models.Value('Unknown')),
default='gender',
output_field=models.CharField()))
query = query.values('name').distinct()
results = [GENDER_DICT.get(result['name'], 'Unknown') for result in query]
return results if results else ['Unknown']
@property
def filtered_attachment_files(self):
# Due to the privacy issue with the data that was posted on the IPRA / COPA data portal
# We need to hide those documents
return filter_attachments(self.attachment_files)
@property
def v2_to(self):
return f'/complaint/{self.crid}/'
def get_absolute_url(self):
return self.v2_to
class AttachmentFile(TimeStampsModel, TaggableModel):
external_id = models.CharField(max_length=255, db_index=True)
file_type = models.CharField(max_length=10,
choices=MEDIA_TYPE_CHOICES,
db_index=True)
title = models.CharField(max_length=255, null=True, blank=True)
url = models.CharField(max_length=255, db_index=True)
additional_info = JSONField(null=True)
tag = models.CharField(max_length=50)
original_url = models.CharField(max_length=255, db_index=True)
allegation = models.ForeignKey('data.Allegation',
on_delete=models.CASCADE,
related_name='attachment_files')
source_type = models.CharField(max_length=255, db_index=True)
views_count = models.IntegerField(default=0)
downloads_count = models.IntegerField(default=0)
notifications_count = models.IntegerField(default=0)
show = models.BooleanField(default=True)
is_external_ocr = models.BooleanField(default=False)
manually_updated = models.BooleanField(default=False)
last_updated_by = models.ForeignKey(settings.AUTH_USER_MODEL,
on_delete=models.CASCADE,
null=True)
# Document cloud information
preview_image_url = models.CharField(max_length=255, null=True)
external_created_at = models.DateTimeField(null=True)
external_last_updated = models.DateTimeField(null=True)
text_content = models.TextField(blank=True)
reprocess_text_count = models.IntegerField(default=0)
pages = models.IntegerField(default=0)
pending_documentcloud_id = models.CharField(max_length=255, null=True)
upload_fail_attempts = models.IntegerField(default=0)
objects = BulkUpdateManager()
showing = ShownAttachmentManager()
class Meta:
unique_together = (('allegation', 'external_id', 'source_type'), )
def __str__(self):
return self.title
@property
def s3_key(self):
return f'{settings.S3_BUCKET_PDF_DIRECTORY}/{self.external_id}'
def upload_to_s3(self):
aws.lambda_client.invoke_async(
FunctionName=settings.LAMBDA_FUNCTION_UPLOAD_PDF,
InvokeArgs=json.dumps({
'url': self.url,
'bucket': settings.S3_BUCKET_OFFICER_CONTENT,
'key': self.s3_key
}))
@property
def linked_documents(self):
return AttachmentFile.showing.filter(
allegation_id=self.allegation_id,
file_type=MEDIA_TYPE_DOCUMENT,
).exclude(id=self.id)
def update_to_documentcloud(self, field, value):
if self.source_type not in AttachmentSourceType.DOCUMENTCLOUD_SOURCE_TYPES:
return
client = DocumentCloud(settings.DOCUMENTCLOUD_USER,
settings.DOCUMENTCLOUD_PASSWORD)
try:
doc = client.documents.get(self.external_id)
except DoesNotExistError:
logger.error(
f'Cannot find document with external id {self.external_id} on DocumentCloud'
)
return
if getattr(doc, field, None) == value:
return
setattr(doc, field, value)
try:
doc.save()
except HTTPError:
logger.error(
f'Cannot save document with external id {self.external_id} on DocumentCloud'
)
def get_absolute_url(self):
return f'/document/{self.pk}/'
def update_allegation_summary(self):
if self.source_type == AttachmentSourceType.SUMMARY_REPORTS_COPA_DOCUMENTCLOUD \
and self.text_content and not self.allegation.summary:
summary = extract_copa_executive_summary(self.text_content)
if summary:
self.allegation.summary = summary
self.allegation.is_extracted_summary = True
self.allegation.save()
return True
class Brand(models.Model):
name = models.CharField(max_length=128, unique=True, db_index=True)
if settings.DATABASES['default']['USER'] == 'postgres':
codes = ArrayField(models.CharField(max_length=64), default=['code_1'])
objects = BulkUpdateManager()
class Rect(models.Model):
# https://github.com/aykut/django-bulk-update
objects = BulkUpdateManager()
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
cid = models.CharField(verbose_name=u'经字号', max_length=28, db_index=True)
reel = models.ForeignKey(Reel, null=True, blank=True, related_name='rects')
page_code = models.CharField(max_length=23, blank=False, verbose_name=u'关联源页CODE', db_index = True)
column_set = JSONField(default=list, verbose_name=u'切字块所在切列JSON数据集')
char_no = models.PositiveSmallIntegerField(null=True, blank=True, verbose_name=u'字号', default=0)
line_no = models.PositiveSmallIntegerField(null=True, blank=True, verbose_name=u'行号', default=0) # 对应图片的一列
op = models.PositiveSmallIntegerField(verbose_name=u'操作类型', default=OpStatus.NORMAL)
x = models.PositiveSmallIntegerField(verbose_name=u'X坐标', default=0)
y = models.PositiveSmallIntegerField(verbose_name=u'Y坐标', default=0)
w = models.IntegerField(verbose_name=u'宽度', default=1)
h = models.IntegerField(verbose_name=u'高度', default=1)
cc = models.FloatField(null=True, blank=True, verbose_name=u'切分置信度', db_index=True, default=1)
ch = models.CharField(null=True, blank=True, verbose_name=u'文字', max_length=2, default='', db_index=True)
wcc = models.FloatField(null=True, blank=True, verbose_name=u'识别置信度', default=1, db_index=True)
ts = models.CharField(null=True, blank=True, verbose_name=u'标字', max_length=2, default='')
s3_inset = models.FileField(max_length=256, blank=True, null=True, verbose_name=u's3地址', upload_to='tripitaka/hans',
storage='storages.backends.s3boto.S3BotoStorage')
updated_at = models.DateTimeField(verbose_name='更新时间1', auto_now=True)
class DefaultDict(dict):
def __missing__(self, key):
return None
@property
def rect_sn(self):
return "%s%02dn%02d" % (self.page_code, self.line_no, self.char_no)
def __str__(self):
return self.ch
def column_uri(self):
col_id = self.column_set['col_id']
return 'https://s3.cn-north-1.amazonaws.com.cn/lqcharacters-images/%s/%s/%s/%s.jpg' % (col_id[0:2], col_id[2:8], col_id[8:12], col_id)
@staticmethod
def canonicalise_uuid(uuid):
import re
uuid = str(uuid)
_uuid_re = re.compile(r'^[0-9A-Fa-f]{8}-(?:[0-9A-Fa-f]{4}-){3}[0-9A-Fa-f]{12}$')
_hex_re = re.compile(r'^[0-9A-Fa-f]{32}$')
if _uuid_re.match(uuid):
return uuid.upper()
if _hex_re.match(uuid):
return '-'.join([uuid[0:8], uuid[8:12], uuid[12:16],
uuid[16:20], uuid[20:]]).upper()
return None
@property
def serialize_set(self):
return dict((k, v) for k, v in self.__dict__.items() if not k.startswith("_"))
@property
def cncode(self):
return "%s%02d" % (self.page_code, self.line_no)
@staticmethod
def generate(rect_dict={}, exist_rects=[]):
_dict = Rect.DefaultDict()
for k, v in rect_dict.items():
_dict[k] = v
if type(_dict['id']).__name__ == "UUID":
_dict['id'] = _dict['id'].hex
try:
el = list(filter(lambda x: x.id.hex == _dict['id'].replace('-', ''), exist_rects))
rect = el[0]
except:
rect = Rect()
valid_keys = rect.serialize_set.keys()-['id']
key_set = set(valid_keys).intersection(_dict.keys())
for key in key_set:
if key in valid_keys:
setattr(rect, key, _dict[key])
rect.updated_at = localtime(now())
rect.cid = rect.rect_sn
rect = Rect.normalize(rect)
return rect
@staticmethod
def bulk_insert_or_replace(rects):
updates = []
news = []
ids = [r['id'] for r in filter(lambda x: Rect.canonicalise_uuid(DotMap(x).id), rects)]
exists = Rect.objects.filter(id__in=ids)
for r in rects:
rect = Rect.generate(r, exists)
if (rect._state.adding):
news.append(rect)
else:
updates.append(rect)
Rect.objects.bulk_create(news)
Rect.objects.bulk_update(updates)
@staticmethod
def normalize(r):
if (r.w < 0):
r.x = r.x + r.w
r.w = abs(r.w)
if (r.h < 0):
r.y = r.y + r.h
r.h = abs(r.h)
if (r.w == 0):
r.w = 1
if (r.h == 0):
r.h = 1
return r
class Meta:
verbose_name = u"源-切字块"
verbose_name_plural = u"源-切字块管理"
ordering = ('-cc',)
class ColRect(models.Model):
# https://github.com/aykut/django-bulk-update
objects = BulkUpdateManager()
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
cid = models.CharField(verbose_name=u'经字号', max_length=32, db_index=True) # 字ID YB000860_001_01_0_01_01
page = models.ForeignKey(PrePage, null=True, blank=True, on_delete=models.SET_NULL)
char_no = models.PositiveSmallIntegerField(null=True, blank=True, verbose_name=u'字号', default=0)
line_no = models.PositiveSmallIntegerField(null=True, blank=True, verbose_name=u'行号', default=0) # 对应图片的一列
op = models.PositiveSmallIntegerField(verbose_name=u'操作类型', default=OpStatus.NORMAL)
x = models.PositiveSmallIntegerField(verbose_name=u'X坐标', default=0)
y = models.PositiveSmallIntegerField(verbose_name=u'Y坐标', default=0)
w = models.IntegerField(verbose_name=u'宽度', default=1)
h = models.IntegerField(verbose_name=u'高度', default=1)
cc = models.FloatField(null=True, blank=True, verbose_name=u'切分置信度', default=1)
ch = models.CharField(null=True, blank=True, verbose_name=u'文字', max_length=16, default='')
wcc = models.FloatField(null=True, blank=True, verbose_name=u'识别置信度', default=1)
updated_at = models.DateTimeField(verbose_name='更新时间', auto_now=True)
class DefaultDict(dict):
def __missing__(self, key):
return None
@property
def rect_sn(self):
return "%s_L%02d" % (self.page_pid, self.line_no)
def __str__(self):
return self.ch
@staticmethod
def canonicalise_uuid(uuid):
import re
uuid = str(uuid)
_uuid_re = re.compile(r'^[0-9A-Fa-f]{8}-(?:[0-9A-Fa-f]{4}-){3}[0-9A-Fa-f]{12}$')
_hex_re = re.compile(r'^[0-9A-Fa-f]{32}$')
if _uuid_re.match(uuid):
return uuid.upper()
if _hex_re.match(uuid):
return '-'.join([uuid[0:8], uuid[8:12], uuid[12:16],
uuid[16:20], uuid[20:]]).upper()
return None
@property
def serialize_set(self):
return dict((k, v) for k, v in self.__dict__.items() if not k.startswith("_"))
@staticmethod
def generate(rect_dict={}, exist_rects=[]):
_dict = ColRect.DefaultDict()
for k, v in rect_dict.items():
_dict[k] = v
## 此处处理重复添加，对于已有的exist_rects，将更新旧的Rect，不再生成新的Rect实例
if type(_dict['id']).__name__ == "UUID":
_dict['id'] = _dict['id'].hex
try:
el = list(filter(lambda x: x.id.hex == _dict['id'].replace('-', ''), exist_rects))
rect = el[0]
except:
rect = ColRect()
## 此处处理外部API提供的字典数据，过滤掉非model定义交集的部分。
valid_keys = rect.serialize_set.keys()-['id']
key_set = set(valid_keys).intersection(_dict.keys())
for key in key_set:
if key in valid_keys:
setattr(rect, key, _dict[key])
rect.updated_at = localtime(now())
## 此处根据已有的page_pid, line_no, char_no,生成新的cid
rect.cid = rect.rect_sn
rect = ColRect.normalize(rect)
return rect
@staticmethod
def bulk_insert_or_replace(rects):
updates = []
news = []
ids = [r['id'] for r in filter(lambda x: ColRect.canonicalise_uuid(DotMap(x).id), rects)]
exists = ColRect.objects.filter(id__in=ids)
for r in rects:
rect = ColRect.generate(r, exists)
if (rect._state.adding):
news.append(rect)
else:
updates.append(rect)
ColRect.objects.bulk_create(news)
ColRect.objects.bulk_update(updates)
@staticmethod
def _normalize(r):
if (not r.w):
r.w = 1
if (not r.h):
r.h = 1
if (r.w < 0):
r.x = r.x + r.w
r.w = abs(r.w)
if (r.h < 0):
r.y = r.y + r.h
r.h = abs(r.h)
return r
@staticmethod
def normalize(r):
if isinstance(r, dict):
r = DotMap(r)
r = ColRect._normalize(r)
if isinstance(r, DotMap):
r = r.toDict()
return r
@staticmethod
def direct_delete_rects(rects, t):
rect_ids = [rect ['id'] for rect in filter(lambda x: x['op'] == 3, rects)]
ColRect.objects.filter(id__in=rect_ids).delete()
class Meta:
verbose_name = u"源-切字列区"
verbose_name_plural = u"源-切字列区管理"
ordering = ('-cc',)
class Source(models.Model):
id = models.AutoField(primary_key=True,
unique=True,
editable=False,
verbose_name='自增id',
db_column='id')
tid = models.CharField(max_length=30,
unique=True,
null=True,
verbose_name='帖子id',
default='',
db_column='tid')
pid = models.CharField(max_length=50,
null=True,
verbose_name='外站帖子id',
default='',
db_column='pid')
title = models.CharField(max_length=255,
null=True,
verbose_name='帖子标题',
default='',
db_column='title')
mod_title = models.CharField(max_length=255,
null=True,
verbose_name='帖子副标题',
default='',
db_column='mod_title')
type = models.IntegerField(null=True,
verbose_name='帖子类型',
db_column='type')
topic_id = models.IntegerField(null=True,
verbose_name='帖子话题ID',
default=0,
db_column='topic_id')
channel_names = models.CharField(max_length=300,
verbose_name="分类cate集合 科技 体育 影视 数码",
default='',
db_column='channel_names')
team = models.IntegerField(null=True,
verbose_name='团队 1: 篮球 2: 足球 3: 步行街 4: 游戏',
default=3,
db_column='team')
account = models.CharField(max_length=100,
null=True,
verbose_name='帖子采集账号',
default='',
db_column='account')
account_name = models.CharField(max_length=100,
null=True,
verbose_name='帖子采集账号名',
default='',
db_column='account_name')
account_type = models.IntegerField(
null=True,
verbose_name='账号类别 1:微博2,:即刻,3:抖音,4:B站,5:公众号,6:新浪,7:搜狐,8:皮皮虾,9:,'
'10:ins,11:西瓜,12:头条,13:it之家,14:鲜知,15:YouTube',
db_column='account_type')
scheme = models.CharField(max_length=200,
null=True,
verbose_name='外站URL',
default='',
db_column='scheme')
status = models.IntegerField(null=True,
verbose_name='帖子使用状态 0: 未使用 2: 已使用',
default=0,
db_column='status')
deleted = models.IntegerField(null=True,
verbose_name='帖子删除状态',
default=0,
db_column='deleted')
is_usable = models.IntegerField(null=True,
verbose_name='是否可用',
default=0,
db_column='is_usable')
jieba_title = models.CharField(max_length=200,
null=True,
verbose_name='帖子标题分词',
default='',
db_column='jieba_title')
pred_topic = models.CharField(max_length=30,
null=True,
verbose_name='帖子话题名',
db_column='pred_topic')
pred_prob = models.CharField(max_length=30,
null=True,
verbose_name='帖子预测话题')
create_dt = models.DateTimeField(auto_now=True,
verbose_name="入库时间",
db_column='create_dt')
update_dt = models.DateTimeField(auto_now=True,
verbose_name='更新时间',
db_column='update_dt')
objects = BulkUpdateManager()
class Meta:
db_table = 'hupu_posts'
verbose_name = '帖子全量表'
verbose_name_plural = verbose_name
ordering = ['-create_dt']
class RaspberryPi(models.Model):
"""
Stores a single RaspberryPi device entry, related to :model:`adsrental.Lead`. It does not have direct connection to
:model:`adsrental.EC2Instance`, but it always can be obtained from related Lead.
It is created automatically when you use *Mark as Qualified, Assign RPi, create Shipstation order* action in Lead admin.
**How to test RaspberryPi device**
It does not matter if it is inital testing or reshipment, actions are the same:
1. Use *Prepare for testing* action for this lead. On this form you can specify extra RPIDs to prepare for testing. Paste any data to textarea
and values like *RP<numbers>* will be prepared for testing as well. Make sure lead status is Qualified.
2. Download latest firmware if you do not have it: `https://s3-us-west-2.amazonaws.com/mvp-store/pi_1.0.26.zip`
3. Flash Firmware to SD card using Etcher `https://etcher.io/`
4. Download `pi.conf` file for this device by clicking *Config file* link in admin for this lead
5. Copy `pi.conf` to SD card root folder.If you are using MacOS/Linux you will see two partitions, use *boot* one.
6. Safe eject SD card to prevent dataloss.
7. Insert SD card to RaspberryPi device. If everything is okay, in 10 seconds RaspbeerPi green LED on device should start blinking.
8. Device can reboot up to 2 times (partition table fix and update to latest patch), so give it at least 3 minutes.
9. Check `Tested` mark in admin.
10. Device is ready to be shipped to the end user
If anything goes wrong, report to @Vlad in Slack.
"""
online_minutes_ttl = 10
first_tested_hours_ttl = 12
last_offline_reported_hours_ttl = 2 * 24
TUNNEL_HOST = '178.128.1.68'
TUNNEL_USER = '******'
TUNNEL_PASSWORD = '******'
TUNNEL_PORT_START = 20000
TUNNEL_PORT_END = 65000
PROXY_HOSTNAME_CHOICES = (
('178.128.1.68', 'Proxykeeper', ),
('138.197.219.240', 'Proxykeeper2', ),
('138.197.197.65', 'Proxykeeper3', ),
('157.230.146.152', 'Proxykeeper4', ),
('157.230.155.97', 'Proxykeeper5', ),
('134.209.52.3', 'Proxykeeper6', ),
('68.183.163.172', 'Proxykeeper7', ),
)
# lead = models.OneToOneField('adsrental.Lead', blank=True, null=True, help_text='Corresponding lead', on_delete=models.SET_NULL, related_name='raspberry_pis', related_query_name='raspberry_pi')
rpid = models.CharField(primary_key=True, max_length=255, unique=True)
rpid_numeric = models.PositiveIntegerField(null=True, blank=True)
leadid = models.CharField(max_length=255, blank=True, null=True)
first_seen = models.DateTimeField(blank=True, null=True)
first_tested = models.DateTimeField(blank=True, null=True)
ip_address = models.CharField(max_length=20, blank=True, null=True)
last_seen = models.DateTimeField(blank=True, null=True, db_index=True)
tunnel_last_tested = models.DateTimeField(blank=True, null=True)
online_since_date = models.DateTimeField(blank=True, null=True)
last_offline_reported = models.DateTimeField(blank=True, null=True, default=timezone.now)
is_proxy_tunnel = models.BooleanField(default=False, help_text='If True - RPi works as an HTTP proxy')
is_beta = models.BooleanField(default=False, help_text='If True - RPi gets beta firmwares')
tunnel_port = models.PositiveIntegerField(null=True, blank=True, unique=True, help_text='Port to create a tunnel to proxykeeper')
rtunnel_port = models.PositiveIntegerField(null=True, blank=True, unique=True, help_text='Port to create a reverse tunnel from proxykeeper')
proxy_hostname = models.CharField(choices=PROXY_HOSTNAME_CHOICES, max_length=50, default=TUNNEL_HOST, help_text='Hostname tunnel to proxykeeper')
proxy_password = models.CharField(max_length=50, default=TUNNEL_PASSWORD, help_text='Hostname password for proxykeeper user')
proxy_delay = models.FloatField(null=True, blank=True, default=None, help_text='Proxy response from tunnel')
proxy_delay_datetime = models.DateTimeField(blank=True, null=True, help_text='Date of tunnel last check')
restart_required = models.BooleanField(default=False)
new_config_required = models.BooleanField(default=False)
version = models.CharField(max_length=20, blank=True, null=True)
created = models.DateTimeField(default=timezone.now)
updated = models.DateTimeField(auto_now=True)
objects = BulkUpdateManager()
@classmethod
def get_free_or_create(cls) -> RaspberryPi:
free_item = cls.objects.filter(lead__isnull=True, rpid__startswith='RP', first_seen__isnull=True).order_by('rpid').first()
if free_item:
return free_item
return cls.create_with_rpid()
@classmethod
def create_with_rpid(cls) -> RaspberryPi:
next_rpid_numeric = 1
last_rpi = RaspberryPi.objects.filter(rpid_numeric__isnull=False).order_by('-created').first()
if last_rpi:
next_rpid_numeric = last_rpi.rpid_numeric + 1
next_rpid = 'RP%08d' % next_rpid_numeric
item = cls(
rpid=next_rpid,
rpid_numeric=next_rpid_numeric,
)
item.save()
item.is_proxy_tunnel = True
item.assign_proxy_hostname()
item.assign_tunnel_ports()
item.save()
return item
def get_lead(self) -> typing.Optional[Lead]:
'Get linked Lead object'
try:
return self.lead # pylint: disable=E1101
except RaspberryPi.lead.RelatedObjectDoesNotExist: # pylint: disable=E1101
return None
def get_proxy_delay(self) -> float:
try:
response = self.check_proxy_tunnel()
except requests.ConnectionError:
return 999.0
except requests.exceptions.RequestException:
return 899.0
return response.elapsed.total_seconds()
def get_ec2_instance(self) -> typing.Optional[EC2Instance]:
lead = self.get_lead()
if not lead:
return None
return lead.get_ec2_instance()
def find_tunnel_ports(self) -> typing.Tuple[int, int]:
tunnel_ports = [i[0] for i in RaspberryPi.objects.filter(tunnel_port__isnull=False).values_list('tunnel_port')]
tunnel_port = random.randint(RaspberryPi.TUNNEL_PORT_START, RaspberryPi.TUNNEL_PORT_END) // 2 * 2
while tunnel_port in tunnel_ports:
tunnel_port = random.randint(RaspberryPi.TUNNEL_PORT_START, RaspberryPi.TUNNEL_PORT_END) // 2 * 2
return (tunnel_port, tunnel_port + 1)
def assign_proxy_hostname(self) -> str:
hostname_count = RaspberryPi.objects.filter(lead__status='In-Progress', is_proxy_tunnel=True).exclude(proxy_hostname='').values('proxy_hostname').annotate(count=Count('rpid')).order_by('count')
hostnames = []
for i in hostname_count:
hostnames.append(i['proxy_hostname'])
for proxy_hostname, _ in RaspberryPi.PROXY_HOSTNAME_CHOICES:
if proxy_hostname not in hostnames:
self.proxy_hostname = proxy_hostname
return self.proxy_hostname
self.proxy_hostname = hostname_count.first()['proxy_hostname']
return self.proxy_hostname
def assign_tunnel_ports(self) -> None:
self.tunnel_port, self.rtunnel_port = self.find_tunnel_ports()
def unassign_tunnel_ports(self) -> None:
self.tunnel_port, self.rtunnel_port = None, None
def is_in_testing(self) -> bool:
if self.first_seen:
return False
if not self.first_tested:
return False
return True
def report_offline(self) -> None:
now = timezone.localtime(timezone.now())
self.last_offline_reported = now
if self.online_since_date:
self.online_since_date = None
self.save()
RaspberryPiSession.end(self)
def _update_ping(self, ping_datetime: datetime.datetime) -> bool:
if not self.first_tested:
self.first_tested = ping_datetime
lead = self.get_lead()
return True
if self.first_tested + datetime.timedelta(hours=self.first_tested_hours_ttl) > ping_datetime:
return False
if self.online_since_date is None:
self.online_since_date = ping_datetime
RaspberryPiSession.start(self)
lead = self.get_lead()
ping_user = User.objects.get(email=settings.PING_USER_EMAIL)
if lead:
if lead.status == lead.STATUS_QUALIFIED:
lead.set_status(lead.STATUS_IN_PROGRESS, edited_by=ping_user)
lead.save()
for lead_account in lead.lead_accounts.filter(status='Qualified'):
if lead_account.is_approval_needed():
lead_account.set_status(lead_account.STATUS_NEEDS_APPROVAL, edited_by=ping_user)
lead_account.save()
lead.set_status(lead.STATUS_NEEDS_APPROVAL, edited_by=ping_user)
lead.save()
continue
lead_account.set_status(lead_account.STATUS_IN_PROGRESS, edited_by=ping_user)
if not lead_account.in_progress_date:
lead_account.in_progress_date = ping_datetime
lead_account.add_comment('Set to in-progress after first ping', ping_user)
# lead_account.insert_note('Set to in-progress after first ping')
# lead_account.save()
if not self.first_seen:
self.first_seen = ping_datetime
self.last_seen = ping_datetime
return True
self.last_seen = ping_datetime
return True
def online(self) -> bool:
last_seen = self.get_last_seen()
if last_seen is None:
return False
return (timezone.now() - last_seen).total_seconds() < self.online_minutes_ttl * 60
@classmethod
def get_objects_online(cls) -> models.query.QuerySet:
now = timezone.localtime(timezone.now())
if settings.LOCAL:
now = now - datetime.timedelta(days=180)
return cls.objects.filter(last_seen__gte=now - datetime.timedelta(minutes=cls.online_minutes_ttl))
@classmethod
def get_objects_offline(cls) -> models.query.QuerySet:
now = timezone.localtime(timezone.now())
return cls.objects.all().exclude(last_seen__gte=now - datetime.timedelta(minutes=cls.online_minutes_ttl))
@classmethod
def get_last_seen_online_dt(cls, now=None) -> datetime.datetime:
if not now:
now = timezone.now()
if settings.LOCAL:
return now - datetime.timedelta(days=180)
return now - datetime.timedelta(minutes=cls.online_minutes_ttl)
def get_first_seen(self) -> typing.Optional[datetime.datetime]:
if self.first_seen is None:
return None
return self.first_seen
def get_last_log(self, tail: int = 1) -> str:
log_dir = os.path.join(settings.RASPBERRY_PI_LOG_PATH, self.rpid)
if not os.path.exists(log_dir):
return ''
log_files = os.listdir(log_dir)
if not log_files:
return ''
last_log = sorted(log_files)[-1]
last_log_path = os.path.join(log_dir, last_log)
lines = open(last_log_path).readlines()[-tail:]
return '\n'.join([i.rstrip('\n') for i in lines])
def get_last_seen(self) -> typing.Optional[datetime.datetime]:
if self.last_seen is None or self.first_seen is None:
return None
return self.last_seen
def __str__(self) -> str:
return self.rpid
@staticmethod
def get_max_datetime(date1: typing.Optional[datetime.datetime], date2: typing.Optional[datetime.datetime]) -> typing.Optional[datetime.datetime]:
if not date1:
return date2
if not date2:
return date1
if date1 > date2:
return date1
return date2
def reset_cache(self) -> None:
ping_cache_helper = PingCacheHelper()
ping_data = ping_cache_helper.get(self.rpid)
if ping_data:
self.process_ping_data(ping_data)
self.save()
ping_cache_helper.delete(self.rpid)
def get_cache(self) -> typing.Optional[PingDataType]:
ping_cache_helper = PingCacheHelper()
ping_data = ping_cache_helper.get(self.rpid)
return ping_data
def process_ping_data(self, ping_data: PingDataType) -> None:
ip_address = ping_data['ip_address']
version = ping_data['raspberry_pi_version']
last_ping = ping_data.get('last_ping') or timezone.localtime(timezone.now())
lead = self.get_lead()
if lead and lead.is_active():
self._update_ping(last_ping)
if self.ip_address != ip_address:
self.ip_address = ip_address
if version and self.version != version:
self.version = version
self.restart_required = False
self.new_config_required = False
self.version = version
def get_proxy_connection_string(self) -> str:
return f'socks5://{self.TUNNEL_USER}:{self.TUNNEL_PASSWORD}@{self.proxy_hostname}:{self.rtunnel_port}'
def check_proxy_tunnel(self) -> requests.Response:
return requests.get(
'https://google.com',
proxies=dict(
http=self.get_proxy_connection_string(),
https=self.get_proxy_connection_string(),
),
timeout=5,
)
def reassign_proxy(self) -> None:
self.reset_cache()
self.assign_proxy_hostname()
self.assign_tunnel_ports()
self.new_config_required = True
self.proxy_delay = None
self.proxy_delay_datetime = None
def get_unique_ips(self) -> typing.List[str]:
last_log = self.get_last_log(tail=1000)
ips = list(set(re.findall(r'\d+\.\d+\.\d+\.\d+', last_log)))
ips = [i for i in ips if i != self.proxy_hostname]
return ips
class Meta:
db_table = 'raspberry_pi'
class PoliceWitness(TimeStampsModel):
allegation = models.ForeignKey('data.Allegation', on_delete=models.CASCADE, null=True)
officer = models.ForeignKey('data.Officer', on_delete=models.CASCADE, null=True)
objects = BulkUpdateManager()
class LeadHistory(models.Model):
'''
Aggregated daily stats for :model:`adsrental.Lead`.
Used to calculate payments to leads.
'''
ONLINE_CHECKS_MIN = 3
WRONG_PASSWORD_CHECKS_MIN = 21
SEC_CHECKPOINT_CHECKS_MIN = 21
MAX_PAYMENT = decimal.Decimal('25.00')
NEW_MAX_PAYMENT = decimal.Decimal('15.00')
AMAZON_MAX_PAYMENT = decimal.Decimal('10.00')
NEW_FACEBOOK_MAX_PAYMENT_DATE = datetime.datetime(2018, 3, 19, tzinfo=timezone.get_default_timezone())
NEW_GOOGLE_MAX_PAYMENT_DATE = datetime.datetime(2018, 3, 29, tzinfo=timezone.get_default_timezone())
class Meta:
verbose_name = 'Lead Timestamp'
verbose_name_plural = 'Lead Timestamps'
lead = models.ForeignKey(Lead, on_delete=models.CASCADE)
date = models.DateField(db_index=True)
checks_offline = models.IntegerField(default=0)
checks_online = models.IntegerField(default=0)
checks_wrong_password = models.IntegerField(default=0)
checks_wrong_password_facebook = models.IntegerField(default=0)
checks_wrong_password_google = models.IntegerField(default=0)
checks_wrong_password_amazon = models.IntegerField(default=0)
checks_sec_checkpoint_facebook = models.IntegerField(default=0)
checks_sec_checkpoint_google = models.IntegerField(default=0)
checks_sec_checkpoint_amazon = models.IntegerField(default=0)
amount = models.DecimalField(default=decimal.Decimal('0.00'), max_digits=8, decimal_places=4, help_text='Sum to be paid to lead')
note = models.TextField(blank=True, null=True, help_text='Note about payment calc')
created = models.DateTimeField(auto_now_add=True)
updated = models.DateTimeField(auto_now=True)
objects = BulkUpdateManager()
def check_lead(self) -> None:
'Update stats for this entry'
if not self.lead.is_active():
self.checks_offline += 1
return
if self.lead.raspberry_pi.online():
self.checks_online += 1
else:
self.checks_offline += 1
for lead_account in self.lead.lead_accounts.all():
if lead_account.wrong_password_date:
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_GOOGLE:
self.checks_wrong_password_google += 1
self.checks_wrong_password += 1
if lead_account.account_type in LeadAccount.ACCOUNT_TYPES_FACEBOOK:
self.checks_wrong_password_facebook += 1
self.checks_wrong_password += 1
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_AMAZON:
self.checks_wrong_password_amazon += 1
self.checks_wrong_password += 1
if lead_account.security_checkpoint_date:
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_GOOGLE:
self.checks_sec_checkpoint_google += 1
if lead_account.account_type in LeadAccount.ACCOUNT_TYPES_FACEBOOK:
self.checks_sec_checkpoint_facebook += 1
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_AMAZON:
self.checks_sec_checkpoint_amazon += 1
@classmethod
def upsert_for_lead(cls, lead: Lead) -> None:
'Create or update stats for this entry'
today = datetime.date.today()
lead_history = cls.objects.filter(lead=lead, date=today).first()
if not lead_history:
lead_history = cls(lead=lead, date=today)
lead_history.check_lead()
lead_history.save()
def is_online(self) -> bool:
'Check iff device was online for more than 12 checks.'
return self.checks_online > self.ONLINE_CHECKS_MIN
def is_active(self) -> bool:
'Check password was not reported as wrong and device was online'
return not self.is_wrong_password() and self.is_online()
def is_wrong_password_facebook(self) -> bool:
return self.checks_wrong_password_facebook >= self.WRONG_PASSWORD_CHECKS_MIN
def is_wrong_password_google(self) -> bool:
return self.checks_wrong_password_facebook >= self.WRONG_PASSWORD_CHECKS_MIN
def is_wrong_password_amazon(self) -> bool:
return self.checks_wrong_password_facebook >= self.WRONG_PASSWORD_CHECKS_MIN
def is_wrong_password(self) -> bool:
'Check if password for this day was reported wrong at least 3 times.'
if self.is_wrong_password_facebook():
return True
if self.is_wrong_password_google():
return True
if self.is_wrong_password_amazon():
return True
return False
def is_sec_checkpoint(self) -> bool:
'Check if security checkpoint for this day was reported at least 3 times.'
if self.checks_sec_checkpoint_facebook >= self.SEC_CHECKPOINT_CHECKS_MIN:
return True
if self.checks_sec_checkpoint_google >= self.SEC_CHECKPOINT_CHECKS_MIN:
return True
if self.checks_sec_checkpoint_amazon >= self.SEC_CHECKPOINT_CHECKS_MIN:
return True
return False
@classmethod
def get_queryset_for_month(cls, year: int, month: int, lead_ids: typing.Optional[typing.List[str]] = None) -> models.query.QuerySet:
'Get all entries for given year and month'
date__gte = datetime.date(year, month, 1)
date__lt = datetime.date(year, month, 1) + relativedelta(months=1)
result = cls.objects.filter(date__gte=date__gte, date__lt=date__lt)
if lead_ids:
result = result.filter(lead_id__in=lead_ids)
return result
def get_last_day(self) -> datetime.date:
next_month = self.date.replace(day=28) + datetime.timedelta(days=4)
return next_month - datetime.timedelta(days=next_month.day)
def get_first_day(self) -> datetime.date:
return self.date.replace(day=1)
def get_amount_with_note(self) -> typing.Tuple[decimal.Decimal, str]:
result = decimal.Decimal('0.00')
if not self.is_online():
return result, 'Account is offline (${})'.format(result)
raspberry_pi = self.lead.raspberry_pi
if not raspberry_pi:
return result, 'RaspberryPi does not exist (${})'.format(result)
days_in_month = (self.get_last_day() - self.get_first_day()).days + 1
note = []
for lead_account in self.lead.lead_accounts.filter(active=True):
if not lead_account.in_progress_date:
note.append('{type} account is not in-progress yet ($0.00)'.format(
type=lead_account.get_account_type_display(),
))
continue
in_progress_date = lead_account.in_progress_date.date()
if in_progress_date > self.date:
note.append('{type} account became in progress only on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date=lead_account.in_progress_date.strftime(settings.HUMAN_DATE_FORMAT),
))
continue
if lead_account.is_banned():
if not lead_account.banned_date:
note.append('{type} account was banned on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date='unknown date',
))
continue
if lead_account.ban_reason in lead_account.PRORATED_BAN_REASONS and lead_account.banned_date.date() <= self.date:
note.append('{type} account was banned on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date=lead_account.banned_date.strftime(settings.HUMAN_DATE_FORMAT),
))
continue
if lead_account.ban_reason not in lead_account.PRORATED_BAN_REASONS:
note.append('{type} account was banned on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date=lead_account.banned_date.strftime(settings.HUMAN_DATE_FORMAT),
))
continue
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_GOOGLE:
checks_wrong_password = self.checks_wrong_password_google
checks_sec_checkpoint = self.checks_sec_checkpoint_google
if lead_account.account_type in LeadAccount.ACCOUNT_TYPES_FACEBOOK:
checks_wrong_password = self.checks_wrong_password_facebook
checks_sec_checkpoint = self.checks_sec_checkpoint_facebook
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_AMAZON:
checks_wrong_password = self.checks_wrong_password_amazon
checks_sec_checkpoint = self.checks_sec_checkpoint_amazon
if checks_wrong_password >= self.WRONG_PASSWORD_CHECKS_MIN:
note.append('{type} account has wrong PW ($0.00)'.format(
type=lead_account.get_account_type_display(),
))
continue
if checks_sec_checkpoint >= self.SEC_CHECKPOINT_CHECKS_MIN:
note.append('{type} account has security checkpoint issue ($0.00)'.format(
type=lead_account.get_account_type_display(),
))
continue
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_GOOGLE:
if lead_account.created > self.NEW_GOOGLE_MAX_PAYMENT_DATE:
month_payment = self.NEW_MAX_PAYMENT
else:
month_payment = self.MAX_PAYMENT
if lead_account.account_type in LeadAccount.ACCOUNT_TYPES_FACEBOOK:
if lead_account.created > self.NEW_FACEBOOK_MAX_PAYMENT_DATE:
month_payment = self.NEW_MAX_PAYMENT
else:
month_payment = self.MAX_PAYMENT
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_AMAZON:
month_payment = self.AMAZON_MAX_PAYMENT
day_payment = round(month_payment / days_in_month, 4)
note.append('{type} account in-progress from {date} (${result})'.format(
type=lead_account.get_account_type_display(),
date=lead_account.in_progress_date.strftime(settings.HUMAN_DATE_FORMAT),
result=day_payment,
))
result += day_payment
note.append('Total: ${result}'.format(
result=result,
))
return result, '\n'.join(note)
class PersonUUID(models.Model):
uuid = models.UUIDField(primary_key=True, default=uuid4)
age = models.IntegerField()
objects = BulkUpdateManager()
class SSUser(models.Model):
@classmethod
def userTodyChecked(cls):
'''返回今日签到人数'''
return len([o for o in cls.objects.all() if o.get_check_in()])
@classmethod
def userNeverChecked(cls):
'''返回从未签到过人数'''
return len([
o for o in cls.objects.all() if o.last_check_in_time.year == 1970
])
@classmethod
def userNeverUsed(cls):
'''返回从未使用过的人数'''
return len([o for o in cls.objects.all() if o.last_use_time == 0])
@classmethod
def coreUser(cls):
'''返回流量用的最多的前十名用户'''
rec = {}
for u in cls.objects.filter(download_traffic__gt=0):
rec[u] = u.upload_traffic + u.download_traffic
# 按照流量倒序排序，切片取出前十名
rec = sorted(rec.items(), key=lambda rec: rec[1], reverse=True)[:10]
return [(r[0], r[0].get_traffic()) for r in rec]
@classmethod
def randomPord(cls):
'''随机端口'''
users = cls.objects.all()
port_list = []
for user in users:
port_list.append(user.port)
all_ports = [i for i in range(1025, max(port_list) + 1)]
try:
return choice(list(set(all_ports).difference(set(port_list))))
except:
return max(port_list) + 1
@classmethod
def get_vaild_user(cls, level):
'''返回指大于等于指定等级的所有合法用户'''
users = SSUser.objects.filter(level__gte=level, transfer_enable__gte=0)
ret = []
for u in users:
if (u.transfer_enable - u.upload_traffic - u.download_traffic) > 0:
ret.append(u)
return ret
objects = BulkUpdateManager()
user = models.OneToOneField(settings.AUTH_USER_MODEL,
on_delete=models.CASCADE,
related_name='ss_user',
verbose_name='用户名')
last_check_in_time = models.DateTimeField(
verbose_name='最后签到时间',
null=True,
default=datetime.datetime.fromtimestamp(0),
editable=False)
password = models.CharField(verbose_name='sspanel密码',
max_length=32,
default=get_short_random_string,
db_column='passwd',
validators=[
validators.MinLengthValidator(6),
])
port = models.IntegerField(
verbose_name='端口',
db_column='port',
unique=True,
)
last_use_time = models.IntegerField(verbose_name='最后使用时间',
default=0,
editable=False,
help_text='时间戳',
db_column='t')
upload_traffic = models.BigIntegerField(verbose_name='上传流量',
default=0,
db_column='u')
download_traffic = models.BigIntegerField(verbose_name='下载流量',
default=0,
db_column='d')
transfer_enable = models.BigIntegerField(verbose_name='总流量',
default=settings.DEFAULT_TRAFFIC,
db_column='transfer_enable')
switch = models.BooleanField(verbose_name='保留字段switch',
default=True,
db_column='switch')
enable = models.BooleanField(verbose_name='开启与否',
default=True,
db_column='enable')
method = models.CharField(
verbose_name='加密类型',
default=settings.DEFAULT_METHOD,
max_length=32,
choices=METHOD_CHOICES,
)
protocol = models.CharField(verbose_name='协议',
default=settings.DEFAULT_PROTOCOL,
max_length=32,
choices=PROTOCOL_CHOICES)
protocol_param = models.CharField(verbose_name='协议参数',
max_length=128,
null=True,
blank=True)
obfs = models.CharField(verbose_name='混淆',
default=settings.DEFAULT_OBFS,
max_length=32,
choices=OBFS_CHOICES)
obfs_param = models.CharField(verbose_name='混淆参数',
max_length=128,
null=True,
blank=True)
level = models.PositiveIntegerField(
verbose_name='用户等级',
default=0,
)
def __str__(self):
return self.user.username
def get_last_use_time(self):
'''返回上一次的使用到时间'''
return timezone.datetime.fromtimestamp(self.last_use_time)
def get_traffic(self):
'''返回用户使用的总流量'''
return traffic_format(self.download_traffic + self.upload_traffic)
def get_transfer(self):
'''返回用户的总流量'''
return traffic_format(self.transfer_enable)
def get_unused_traffic(self):
'''返回用户的剩余流量'''
return traffic_format(self.transfer_enable - self.upload_traffic -
self.download_traffic)
def get_used_percentage(self):
'''返回用户的为使用流量百分比'''
try:
return '{:.2f}'.format(
(self.download_traffic + self.upload_traffic) /
self.transfer_enable * 100)
except ZeroDivisionError:
return '100'
def get_check_in(self):
'''返回当天是否签到'''
# 获取当天日期
check_day = self.last_check_in_time.day
now_day = datetime.datetime.now().day
return check_day == now_day
def clean(self):
'''保证端口在1024<50000之间'''
if self.port:
if not 1024 < self.port < 50000:
raise ValidationError('端口必须在1024和50000之间')
# 重写一下save函数，保证user与ss_user的level字段同步
def save(self, *args, **kwargs):
self.level = self.user.level
super(SSUser, self).save(*args, **kwargs)
class Meta:
verbose_name_plural = 'SS用户'
ordering = ('-last_check_in_time', )
db_table = 'user'
class LeadHistoryMonth(models.Model, FulltextSearchMixin):
'''
Aggregated monthly stats for :model:`adsrental.Lead`.
Used to calculate payments to leads.
'''
class Meta:
verbose_name = 'Lead History Month'
verbose_name_plural = 'Lead Histories Month'
MAX_PAYMENT = decimal.Decimal('25.00')
NEW_MAX_PAYMENT = decimal.Decimal('15.00')
AMAZON_MAX_PAYMENT = decimal.Decimal('10.00')
MOVE_AMOUNT = decimal.Decimal('5.00')
NEW_FACEBOOK_MAX_PAYMENT_DATE = datetime.datetime(2018, 3, 19, tzinfo=timezone.get_default_timezone())
NEW_GOOGLE_MAX_PAYMENT_DATE = datetime.datetime(2018, 3, 29, tzinfo=timezone.get_default_timezone())
lead = models.ForeignKey(Lead, help_text='Linked lead.', on_delete=models.CASCADE)
date = models.DateField(db_index=True)
days_offline = models.IntegerField(default=0, help_text='Days when device had been online less than 3 hours.')
days_online = models.IntegerField(default=0, help_text='Days when device had been online more than 3 hours.')
days_wrong_password = models.IntegerField(default=0, help_text='Days when wrong password was reported at least 3 hours.')
days_sec_checkpoint = models.IntegerField(default=0, help_text='Days when security checkpoint was reported at least 3 hours.')
max_payment = models.DecimalField(null=True, blank=True, max_digits=6, decimal_places=2, help_text='Max payment to lead, depends on qualified date and his accounts.')
amount = models.DecimalField(default=decimal.Decimal('0.00'), max_digits=6, decimal_places=2, help_text='Sum to be paid to lead')
amount_moved = models.DecimalField(default=decimal.Decimal('0.00'), max_digits=6, decimal_places=2, help_text='Amount moved from next month')
amount_paid = models.DecimalField(default=decimal.Decimal('0.00'), max_digits=6, decimal_places=2, help_text='Sum paid tot lead')
note = models.TextField(blank=True, null=True, help_text='Note about payment calc')
move_to_next_month = models.BooleanField(default=False)
check_number = models.IntegerField(default=None, null=True, blank=True)
created = models.DateTimeField(auto_now_add=True)
updated = models.DateTimeField(auto_now=True)
objects = BulkUpdateManager()
def aggregate(self) -> None:
self.days_offline = 0
self.days_online = 0
self.days_wrong_password = 0
self.days_sec_checkpoint = 0
lead_histories = LeadHistory.objects.filter(
lead=self.lead,
date__gte=self.get_first_day(),
date__lte=self.get_last_day(),
).prefetch_related(
'lead',
'lead__raspberry_pi',
'lead__lead_accounts',
)
total_amount = decimal.Decimal('0.00')
for lead_history in lead_histories:
amount, note = lead_history.get_amount_with_note()
lead_history.amount = amount
lead_history.note = note
total_amount += amount
if lead_history.is_wrong_password():
self.days_wrong_password += 1
if lead_history.is_sec_checkpoint():
self.days_sec_checkpoint += 1
if lead_history.is_online():
self.days_online += 1
else:
self.days_offline += 1
bulk_update(lead_histories, update_fields=['amount', 'note'])
self.amount = total_amount
prev_history = LeadHistoryMonth.objects.filter(lead=self.lead, date__lt=self.get_first_day()).order_by('-date').first()
if prev_history and prev_history.move_to_next_month:
self.amount += prev_history.amount
self.amount_moved = prev_history.amount
if self.amount < self.MOVE_AMOUNT and self.amount != self.amount_moved:
self.move_to_next_month = True
else:
self.move_to_next_month = False
@classmethod
def get_or_create(cls, lead: Lead, date: datetime.date) -> LeadHistoryMonth:
date_month = date.replace(day=1)
item = cls.objects.filter(date=date_month, lead=lead).first()
if item:
return item
return cls(lead=lead, date=date_month)
def get_max_payment_with_note(self) -> typing.Tuple[decimal.Decimal, str]:
result = decimal.Decimal('0.00')
raspberry_pi = self.lead.raspberry_pi
note = []
if not raspberry_pi:
return result, 'RaspberryPi does not exist ($0.00)'
for lead_account in self.lead.lead_accounts.filter(qualified_date__isnull=False, active=True):
created_date = lead_account.created
if lead_account.is_banned():
note.append('{type} account was banned on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date=lead_account.banned_date.strftime(settings.HUMAN_DATE_FORMAT) if lead_account.banned_date else 'unknown date',
))
continue
if created_date.date() > self.get_last_day():
note.append('{type} account was created only on {date} ($0.00)'.format(
type=lead_account.get_account_type_display(),
date=lead_account.created.strftime(settings.HUMAN_DATE_FORMAT),
))
continue
coef = decimal.Decimal('1.00')
if created_date.date() > self.get_first_day():
days_in_month = (self.get_last_day() - self.get_first_day()).days + 1
registered_days = (self.get_last_day() - created_date.date()).days + 1
coef = decimal.Decimal(registered_days / days_in_month)
base_payment = decimal.Decimal('0.00')
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_GOOGLE:
if created_date > self.NEW_GOOGLE_MAX_PAYMENT_DATE:
base_payment = self.NEW_MAX_PAYMENT
else:
base_payment = self.MAX_PAYMENT
if lead_account.account_type in LeadAccount.ACCOUNT_TYPES_FACEBOOK:
if created_date > self.NEW_FACEBOOK_MAX_PAYMENT_DATE:
base_payment = self.NEW_MAX_PAYMENT
else:
base_payment = self.MAX_PAYMENT
if lead_account.account_type == LeadAccount.ACCOUNT_TYPE_AMAZON:
base_payment = self.AMAZON_MAX_PAYMENT
result += base_payment * coef
note.append('{type} account created on {date} (${base} * {coef} = ${result})'.format(
type=lead_account.get_account_type_display(),
date=lead_account.created.strftime(settings.HUMAN_DATE_FORMAT),
base=base_payment,
coef=round(coef, 2),
result=round(base_payment * coef, 2),
))
note.append('Total: ${result}'.format(
result=round(result, 2),
))
return result, '\n'.join(note)
def get_last_day(self) -> datetime.date:
next_month = self.date.replace(day=28) + datetime.timedelta(days=4)
return next_month - datetime.timedelta(days=next_month.day)
def get_first_day(self) -> datetime.date:
return self.date.replace(day=1)
def get_amount(self) -> decimal.Decimal:
if not self.days_online:
return decimal.Decimal('0.00')
days_total = self.days_online + self.days_offline
days_online_valid = max(self.days_online - self.days_wrong_password, 0)
return round(self.max_payment * days_online_valid / days_total, 2)
def get_remaining_amount(self) -> decimal.Decimal:
return self.amount - self.amount_paid
class OfficerAllegation(TimeStampsModel):
allegation = models.ForeignKey('data.Allegation',
on_delete=models.CASCADE,
null=True)
allegation_category = models.ForeignKey('data.AllegationCategory',
on_delete=models.SET_NULL,
to_field='id',
null=True)
officer = models.ForeignKey('data.Officer',
on_delete=models.CASCADE,
null=True)
start_date = models.DateField(null=True)
end_date = models.DateField(null=True)
officer_age = models.IntegerField(null=True)
recc_finding = models.CharField(choices=FINDINGS, max_length=2, blank=True)
recc_outcome = models.CharField(max_length=32, blank=True)
final_finding = models.CharField(choices=FINDINGS,
max_length=2,
blank=True)
final_outcome = models.CharField(max_length=32, blank=True)
final_outcome_class = models.CharField(max_length=20, blank=True)
disciplined = models.NullBooleanField()
objects = BulkUpdateManager()
class Meta:
indexes = [
models.Index(fields=['start_date']),
]
@property
def crid(self):
return self.allegation.crid
@property
def category(self):
try:
return self.allegation_category.category
except AttributeError:
return None
@property
def subcategory(self):
try:
return self.allegation_category.allegation_name
except AttributeError:
return None
@property
def coaccused_count(self):
return self.allegation.coaccused_count
@property
def final_finding_display(self):
try:
return FINDINGS_DICT[self.final_finding]
except KeyError:
return 'Unknown'
@property
def recc_finding_display(self):
try:
return FINDINGS_DICT[self.recc_finding]
except KeyError:
return 'Unknown'
@property
def victims(self):
return self.allegation.victims.all()
@property
def attachments(self):
return self.allegation.attachment_files.all()
class Officer(TimeStampsModel, TaggableModel):
first_name = models.CharField(max_length=255, db_index=True)
last_name = models.CharField(max_length=255, db_index=True)
middle_initial = models.CharField(max_length=5, null=True)
middle_initial2 = models.CharField(max_length=5, null=True)
suffix_name = models.CharField(max_length=5, null=True)
gender = models.CharField(max_length=1, blank=True)
race = models.CharField(max_length=50,
default='Unknown',
validators=[validate_race])
appointed_date = models.DateField(null=True)
resignation_date = models.DateField(null=True)
rank = models.CharField(max_length=100, blank=True)
birth_year = models.IntegerField(null=True)
active = models.CharField(choices=ACTIVE_CHOICES,
max_length=10,
default=ACTIVE_UNKNOWN_CHOICE)
# CACHED COLUMNS
complaint_percentile = models.DecimalField(max_digits=6,
decimal_places=4,
null=True)
civilian_allegation_percentile = models.DecimalField(max_digits=6,
decimal_places=4,
null=True)
internal_allegation_percentile = models.DecimalField(max_digits=6,
decimal_places=4,
null=True)
trr_percentile = models.DecimalField(max_digits=6,
decimal_places=4,
null=True)
honorable_mention_percentile = models.DecimalField(max_digits=6,
decimal_places=4,
null=True)
allegation_count = models.IntegerField(default=0, null=True)
sustained_count = models.IntegerField(default=0, null=True)
honorable_mention_count = models.IntegerField(default=0, null=True)
unsustained_count = models.IntegerField(default=0, null=True)
discipline_count = models.IntegerField(default=0, null=True)
civilian_compliment_count = models.IntegerField(default=0, null=True)
trr_count = models.IntegerField(default=0, null=True)
major_award_count = models.IntegerField(default=0, null=True)
current_badge = models.CharField(max_length=10, null=True)
last_unit = models.ForeignKey('data.PoliceUnit',
on_delete=models.SET_NULL,
null=True)
current_salary = models.PositiveIntegerField(null=True)
has_unique_name = models.BooleanField(default=False)
objects = BulkUpdateManager()
def __str__(self):
return self.full_name
@property
def full_name(self):
return f'{self.first_name} {self.last_name}'
@property
def historic_badges(self):
# old not current badge
return self.officerbadgenumber_set.exclude(current=True).values_list(
'star', flat=True)
@property
def historic_units(self):
return [
o.unit for o in self.officerhistory_set.all().select_related(
'unit').order_by('-effective_date')
]
@property
def gender_display(self):
try:
return GENDER_DICT[self.gender]
except KeyError:
return self.gender
@property
def v1_url(self):
return f'{settings.V1_URL}/officer/{slugify(self.full_name)}/{self.pk}'
@property
def current_age(self):
return timezone.localtime(timezone.now()).year - self.birth_year
@property
def v2_to(self):
return f'/officer/{self.pk}/{slugify(self.full_name)}/'
def get_absolute_url(self):
return self.v2_to
@property
def abbr_name(self):
return f'{self.first_name[0].upper()}. {self.last_name}'
@property
def visual_token_background_color(self):
cr_scale = ScaleThreshold(domain=[1, 5, 10, 25, 40],
target_range=range(6))
cr_threshold = cr_scale.interpolate(self.allegation_count)
return BACKGROUND_COLOR_SCHEME[f'{cr_threshold}0']
def get_unit_by_date(self, query_date):
try:
officer_history = self.officerhistory_set.filter(
Q(effective_date__lte=query_date)
| Q(effective_date__isnull=True),
Q(end_date__gte=query_date) | Q(end_date__isnull=True))[0]
return officer_history.unit
except IndexError:
return None
@staticmethod
def _group_and_sort_aggregations(data, key_name='name'):
"""
Helper to group by name, aggregate count & sustained_counts.
Also makes sure 'Unknown' group is always the last item.
"""
groups = []
unknown_group = None
for k, g in groupby(data, lambda x: x[key_name]):
group = {'name': k, 'count': 0, 'sustained_count': 0, 'items': []}
unknown_year = None
for item in g:
if item['year']:
group['items'].append(item)
group['count'] += item['count']
group['sustained_count'] += item['sustained_count']
else:
unknown_year = item
if unknown_year:
group['count'] += item['count']
group['sustained_count'] += item['sustained_count']
if k != 'Unknown':
groups.append(group)
else:
unknown_group = group
if unknown_group is not None:
groups.append(unknown_group)
return groups
@property
def complaint_category_aggregation(self):
query = self.officerallegation_set.all()
query = query.annotate(name=models.Case(
models.When(allegation_category__category__isnull=True,
then=models.Value('Unknown')),
default='allegation_category__category',
output_field=models.CharField()),
year=ExtractYear('start_date'))
query = query.values('name', 'year').order_by('name', 'year').annotate(
count=models.Count('name'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
return Officer._group_and_sort_aggregations(list(query))
@property
def complainant_race_aggregation(self):
query = self.officerallegation_set.all()
query = query.annotate(
name=models.Case(models.When(allegation__complainant__isnull=True,
then=models.Value('Unknown')),
models.When(allegation__complainant__race__in=[
'n/a', 'n/a ', 'nan', ''
],
then=models.Value('Unknown')),
default='allegation__complainant__race',
output_field=models.CharField()),
year=ExtractYear('start_date'),
)
query = query.values('name', 'year').order_by('name', 'year').annotate(
count=models.Count('name'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
return Officer._group_and_sort_aggregations(list(query))
@property
def complainant_age_aggregation(self):
query = self.officerallegation_set.all()
query = query.annotate(name=get_num_range_case(
'allegation__complainant__age', [0, 20, 30, 40, 50]),
year=ExtractYear('start_date'))
query = query.values('name', 'year').order_by('name', 'year').annotate(
count=models.Count('name'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
return Officer._group_and_sort_aggregations(list(query))
@property
def complainant_gender_aggregation(self):
query = self.officerallegation_set.all()
query = query.values('allegation__complainant__gender').annotate(
complainant_gender=models.Case(
models.When(allegation__complainant__gender='',
then=models.Value('Unknown')),
models.When(allegation__complainant__isnull=True,
then=models.Value('Unknown')),
default='allegation__complainant__gender',
output_field=models.CharField()),
year=ExtractYear('start_date'))
query = query.values('complainant_gender', 'year').order_by(
'complainant_gender', 'year').annotate(
count=models.Count('complainant_gender'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
data = [{
'name': GENDER_DICT.get(obj['complainant_gender'], 'Unknown'),
'sustained_count': obj['sustained_count'],
'count': obj['count'],
'year': obj['year']
} for obj in query if obj['count'] > 0]
return Officer._group_and_sort_aggregations(data)
@property
def coaccusals(self):
return Officer.objects.filter(
officerallegation__allegation__officerallegation__officer=self
).distinct().exclude(id=self.id).annotate(
coaccusal_count=Count('id')).order_by('-coaccusal_count')
@property
def rank_histories(self):
salaries = self.salary_set.exclude(
spp_date__isnull=True).order_by('year')
try:
first_salary = salaries[0]
except IndexError:
return []
current_rank = first_salary.rank
rank_histories = [{
'date': first_salary.spp_date,
'rank': first_salary.rank
}]
for salary in salaries:
if salary.rank != current_rank:
rank_histories.append({
'date': salary.spp_date,
'rank': salary.rank
})
current_rank = salary.rank
return rank_histories
def get_rank_by_date(self, query_date):
if query_date is None:
return None
if type(query_date) is datetime:
query_date = query_date.date()
rank_histories = self.rank_histories
try:
first_history = rank_histories[0]
except IndexError:
return None
last_history = rank_histories[len(rank_histories) - 1]
if query_date < first_history['date']:
return None
if query_date >= last_history['date']:
return last_history['rank']
for i in range(len(rank_histories)):
if query_date < rank_histories[i]['date']:
return rank_histories[i - 1]['rank']
if query_date == rank_histories[i]['date']:
return rank_histories[i]['rank']
@classmethod
def get_active_officers(cls, rank):
return cls.objects.filter(rank=rank, active=ACTIVE_YES_CHOICE)
@classmethod
def get_officers_most_complaints(cls, rank):
return cls.objects.filter(rank=rank).exclude(
allegation_count=0).order_by('-allegation_count')[:3]
@property
def allegation_attachments(self):
AttachmentFile = apps.get_app_config('data').get_model(
'AttachmentFile')
return AttachmentFile.showing.filter(
allegation__officerallegation__officer=self,
source_type__in=AttachmentSourceType.DOCUMENTCLOUD_SOURCE_TYPES,
).distinct('id')
@property
def investigator_attachments(self):
AttachmentFile = apps.get_app_config('data').get_model(
'AttachmentFile')
return AttachmentFile.showing.filter(
allegation__investigatorallegation__investigator__officer=self,
source_type__in=AttachmentSourceType.DOCUMENTCLOUD_SOURCE_TYPES,
).distinct('id')
def get_zip_filename(self, with_docs):
if with_docs:
return f'{settings.S3_BUCKET_ZIP_DIRECTORY}_with_docs/{self.first_name}_{self.last_name}_with_docs.zip'
return f'{settings.S3_BUCKET_ZIP_DIRECTORY}/{self.first_name}_{self.last_name}.zip'
def check_zip_file_exist(self, with_docs):
try:
aws.s3.get_object(Bucket=settings.S3_BUCKET_OFFICER_CONTENT,
Key=self.get_zip_filename(with_docs))
except botocore.exceptions.ClientError as e:
if e.response['Error']['Code'] == 'NoSuchKey':
return False
raise e
else:
return True
def invoke_create_zip(self, with_docs):
if not self.check_zip_file_exist(with_docs):
zip_key = self.get_zip_filename(with_docs=with_docs)
if with_docs:
allegation_attachments_map = {
f'{settings.S3_BUCKET_PDF_DIRECTORY}/{attachment.external_id}':
f'documents/{attachment.title}.pdf'
for attachment in self.allegation_attachments
}
investigator_attachments_dict = {
f'{settings.S3_BUCKET_PDF_DIRECTORY}/{attachment.external_id}':
f'investigators/{attachment.title}.pdf'
for attachment in self.investigator_attachments
}
else:
allegation_attachments_map = {}
investigator_attachments_dict = {}
xlsx_map = {
f'{settings.S3_BUCKET_XLSX_DIRECTORY}/{self.id}/{file_name}':
file_name
for file_name in XLSX_FILE_NAMES
}
aws.lambda_client.invoke_async(
FunctionName=settings.LAMBDA_FUNCTION_CREATE_OFFICER_ZIP_FILE,
InvokeArgs=json.dumps({
'key': zip_key,
'bucket': settings.S3_BUCKET_OFFICER_CONTENT,
'file_map': {
**xlsx_map,
**allegation_attachments_map,
**investigator_attachments_dict
}
}))
def generate_presigned_zip_url(self, with_docs):
zip_key = self.get_zip_filename(with_docs=with_docs)
return aws.s3.generate_presigned_url(
ClientMethod='get_object',
Params={
'Bucket': settings.S3_BUCKET_OFFICER_CONTENT,
'Key': zip_key,
})
class VultrInstance(models.Model):
"""
Stores a single Vultr Instance entry.
Currently only instances with tag Web are allowed
"""
USERNAME = '******'
TAGS = ('Web', )
RDP_PORT = 3389
id = models.AutoField(primary_key=True)
instance_id = models.PositiveIntegerField(blank=True,
null=True,
db_index=True,
help_text='Vultr ID.')
label = models.CharField(max_length=255,
help_text='Label field from Vultr')
os = models.CharField(max_length=255, help_text='OS field from Vultr')
ip_address = models.CharField(max_length=50,
help_text='Main IP field from Vultr')
status = models.CharField(max_length=20,
help_text='Power status field from Vultr')
password = models.CharField(max_length=255,
help_text='Default password field from Vultr')
tag = models.CharField(max_length=255, help_text='Tag field from Vultr')
data = models.TextField(help_text='Full data from Vultr')
objects = BulkUpdateManager()
def is_running(self) -> bool:
return self.status == 'running'
@classmethod
def update_all_from_vultr(cls,
instance_id: typing.Optional[int] = None
) -> None:
vultr_client = vultr.Vultr(settings.VULTR_API_KEY)
for tag in cls.TAGS:
for new_instance_id, data in vultr_client.server.list(params=dict(
tag=tag)).items():
new_instance_id = int(new_instance_id)
if instance_id and instance_id != new_instance_id:
continue
cls.objects.update_or_create(
instance_id=new_instance_id,
defaults=dict(
label=data['label'],
os=data['os'],
status=data['power_status'],
ip_address=data['main_ip'],
password=data['default_password'],
tag=data['tag'],
data=json.dumps(data),
))
def update_from_vultr(self) -> None:
VultrInstance.update_all_from_vultr(self.instance_id)
def get_windows_rdp_uri(self) -> str:
return 'rdp://{}:{}:{}:{}'.format(self.ip_address, self.RDP_PORT,
self.USERNAME, self.password)
def get_rdp_uri(self) -> str:
return 'rdp://full%20address=s:{}:{}&username=s:{}'.format(
self.ip_address, self.RDP_PORT, self.USERNAME)
def get_web_rdp_link(self) -> str:
return 'http://{host}:{rdp_client_port}/#host={hostname}&port={port}&user={user}&password={password}&rpid={rpid}&connect=true'.format(
host=settings.HOSTNAME,
rdp_client_port=9999,
port=self.RDP_PORT,
hostname=self.ip_address,
user=self.USERNAME,
password=self.password,
rpid='Vultr{}'.format(self.instance_id),
)
