def annotate_properties(self):
count = 0
records = []
logger.debug('annotating properties for: {}'.format(self.__name__))
last30 = dates.get_last_month_since_api_update(self.get_dataset(),
string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
last30_subquery = Subquery(
self.objects.filter(bbl=OuterRef('bbl'),
date_added__gte=last30).values('bbl').annotate(
count=Count('bbl')).values('count'))
lastyear_subquery = Subquery(
self.objects.filter(
bbl=OuterRef('bbl'),
date_added__gte=lastyear).values('bbl').annotate(
count=Count('bbl')).values('count'))
last3years_subquery = Subquery(
self.objects.filter(
bbl=OuterRef('bbl'),
date_added__gte=last3years).values('bbl').annotate(
count=Count('bbl')).values('count'))
ds.PropertyAnnotation.objects.update(
foreclosures_last30=Coalesce(last30_subquery, 0),
foreclosures_lastyear=Coalesce(lastyear_subquery, 0),
foreclosures_last3years=Coalesce(last3years_subquery, 0),
foreclosures_lastupdated=make_aware(datetime.now()))
def annotate_properties(self):
count = 0
records = []
logger.debug('annotating properties for: {}'.format(self.__name__))
last30 = dates.get_last_month_since_api_update(
self.get_dataset(), string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
last30_subquery = Subquery(self.objects.filter(bbl=OuterRef('bbl'), documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES,
documentid__docdate__gte=last30).values('bbl').annotate(count=Count('bbl')).values('count'))
lastyear_subquery = Subquery(self.objects.filter(bbl=OuterRef(
'bbl'), documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, documentid__docdate__gte=lastyear).values('bbl').annotate(count=Count('bbl')).values('count'))
last3years_subquery = Subquery(self.objects
.filter(bbl=OuterRef('bbl'), documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, documentid__docdate__gte=last3years).values('bbl')
.annotate(count=Count('bbl'))
.values('count')
)
latestprice = Subquery(self.objects.filter(bbl=OuterRef('bbl'), documentid__docdate__isnull=False, documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES).order_by(
'-documentid__docdate').values('documentid__docamount')[:1])
latestsaledate = Subquery(self.objects.filter(bbl=OuterRef('bbl'), documentid__docdate__isnull=False,
documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES).order_by(
'-documentid__docdate').values('documentid__docdate')[:1])
ds.PropertyAnnotation.objects.update(acrisrealmasters_last30=Coalesce(last30_subquery, 0), acrisrealmasters_lastyear=Coalesce(lastyear_subquery, 0),
acrisrealmasters_last3years=Coalesce(last3years_subquery, 0), latestsaleprice=latestprice, latestsaledate=latestsaledate, acrisrealmasters_lastupdated=make_aware(datetime.now()))
def annotate_property_on_save(sender, instance, created, **kwargs):
if created == True:
try:
last30 = dates.get_last_month_since_api_update(
ds.AcrisRealLegal.get_dataset(), string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
annotation = instance.bbl.propertyannotation
annotation.acrisrealmasters_last30 = Coalesce(annotation.bbl.acrisreallegal_set.filter(
documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, documentid__docdate__gte=last30).count(), 0)
annotation.acrisrealmasters_lastyear = Coalesce(annotation.bbl.acrisreallegal_set.filter(
documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, documentid__docdate__gte=lastyear).count(), 0)
annotation.acrisrealmasters_last3years = Coalesce(annotation.bbl.acrisreallegal_set.filter(
documentid__doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, documentid__docdate__gte=last3years).count(), 0)
annotation.latestsaleprice = ds.AcrisRealMaster.objects.filter(documentid__in=annotation.bbl.acrisreallegal_set.values(
'documentid'), doctype__in=ds.AcrisRealMaster.SALE_DOC_TYPES, docdate__isnull=False).latest('docdate').docamount
annotation.save()
except Exception as e:
print(e)
return
def get_annotation_start(params, dataset=None, date_field='', dataset_class=''):
start = dates.get_default_annotation_date(dataset, dataset_class, True)
dataset_prefix = dataset_class.__name__.lower() + 's'
if dataset_prefix + '__start' in params:
start = params.get(dataset_prefix + '__start')
elif 'annotation__start' in params:
if params['annotation__start'] == 'recent':
start = dates.get_recent_dataset_start(
dataset, dataset_class, string=False)
elif params['annotation__start'] == 'lastyear':
start = dates.get_last_year(string=False)
elif params['annotation__start'] == 'last3years':
start = dates.get_last3years(string=False)
elif params['annotation__start'] == 'full':
start = dates.get_recent_dataset_start(
dataset, dataset_class, string=False)
else:
start = params['annotation__start']
elif 'q' in params:
start = get_advanced_search_value(
params, dataset_prefix, date_field, 'gte')
if start:
return start
else:
return dates.get_default_annotation_date(dataset, dataset_class, True)
def annotate_property_month_offset(self, annotation):
try:
last30 = dates.get_last_month_since_api_update(self.get_dataset(),
string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
setattr(
annotation,
self.__name__.lower() + 's_last30',
Coalesce(
getattr(annotation.bbl,
self.__name__.lower() + '_set').filter(
**{
self.QUERY_DATE_KEY + '__gte': last30
}).count(), 0))
setattr(
annotation,
self.__name__.lower() + 's_lastyear',
Coalesce(
getattr(annotation.bbl,
self.__name__.lower() + '_set').filter(
**{
self.QUERY_DATE_KEY + '__gte': lastyear
}).count(), 0))
setattr(
annotation,
self.__name__.lower() + 's_last3years',
Coalesce(
getattr(annotation.bbl,
self.__name__.lower() + '_set').filter(
**{
self.QUERY_DATE_KEY + '__gte': last3years
}).count(), 0))
return annotation
except Exception as e:
print(e)
return
def annotate_all_properties_month_offset(self):
logger.debug('annotating properties for: {}'.format(self.__name__))
last30 = dates.get_last_month_since_api_update(self.get_dataset(),
string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
last30_subquery = Subquery(
self.objects.filter(
bbl=OuterRef('bbl'), **{
self.QUERY_DATE_KEY + '__gte': last30
}).values('bbl').annotate(cnt=Count('bbl')).values('cnt'))
lastyear_subquery = Subquery(
self.objects.filter(
bbl=OuterRef('bbl'),
**{
self.QUERY_DATE_KEY + '__gte': lastyear
}).values('bbl').annotate(cnt=Count('bbl')).values('cnt'))
last3years_subquery = Subquery(
self.objects.filter(
bbl=OuterRef('bbl'),
**{
self.QUERY_DATE_KEY + '__gte': last3years
}).values('bbl').annotate(cnt=Count('bbl')).values('cnt'))
ds.PropertyAnnotation.objects.update(
**{
self.__name__.lower() + 's_last30':
Coalesce(last30_subquery, 0)
}, **{
self.__name__.lower() + 's_lastyear':
Coalesce(lastyear_subquery, 0)
}, **{
self.__name__.lower() + 's_last3years':
Coalesce(last3years_subquery, 0),
self.__name__.lower() + 's_lastupdated':
make_aware(datetime.now())
})
def annotate_property_on_save(sender, instance, created, **kwargs):
if created == True:
try:
last30 = dates.get_last_month(string=False)
lastyear = dates.get_last_year(string=False)
last3years = dates.get_last3years(string=False)
annotation = instance.bbl.propertyannotation
annotation.foreclosures_last30 = Coalesce(
annotation.bbl.foreclosure_set.filter(
date_added__gte=last30).count(), 0)
annotation.foreclosures_lastyear = Coalesce(
annotation.bbl.foreclosure_set.filter(
date_added__gte=lastyear).count(), 0)
annotation.foreclosures_last3years = Coalesce(
annotation.bbl.foreclosure_set.filter(
date_added__gte=last3years).count(), 0)
annotation.save()
except Exception as e:
print(e)
def generate_last3years_date_key(dataset, dataset_class):
dataset_prefix = dataset_class.__name__.lower() + 's'
return dataset_prefix + '_last3years__' + '-'.join([dates.get_last3years(string=True), dates.get_dataset_end_date(dataset, dataset_class, string=True)])