def json(self):
month = request.POST['month']
year = request.POST['year']
type = request.POST['type']
search = request.POST['search']
filter = request.POST['filter']
datePeriod = request.POST['datePeriod']
page = request.POST['page']
initLoad = request.POST['initLoad']
periodClick = request.POST['periodClick']
updateBalance = request.POST['updateBalance']
property = request.POST['property']
companyId = request.environ.get("COMPANY_ID")
if not valid.search(search):
return json.dumps({'errors':[{'selector':'', "message":"Search only accepts letters and numbers. Please try again."}]})
startSlice = (int(page) - 1) * 20;
endSlice = startSlice + 20;
query = meta.Session.query(Transaction).filter(Transaction.companyid==companyId).filter(Transaction.deleted==None)
if property != 'all':
query = query.filter(Transaction.propertyid==property)
incomeTotal = 0
expenseTotal = 0
if initLoad == '1' or updateBalance == '1':
for record in query:
if record.amount > 0:
incomeTotal = incomeTotal + record.amount
else:
expenseTotal = expenseTotal + record.amount
expenseTotal = expenseTotal * -1
if incomeTotal:
incomeTotal = '%.2f' % incomeTotal
if expenseTotal:
expenseTotal = '%.2f' % expenseTotal
if search != 'all':
query = query.filter(Transaction.name.like('%'+search+'%'))
if periodClick == '1' and datePeriod != 'all':
datePeriodList = Transaction.get_date_period(datePeriod)
startDate = datePeriodList[0]
endDate = datePeriodList[1]
startDate = datetime.datetime(startDate[0],startDate[1],startDate[2])
endDate = datetime.datetime(endDate[0],endDate[1],endDate[2])
query = query.filter(and_(Transaction.date>=startDate,Transaction.date<=endDate))
for record in query:
if record.amount > 0:
incomeTotal = incomeTotal + record.amount
else:
expenseTotal = expenseTotal + record.amount
expenseTotal = expenseTotal * -1
if month != 'all':
month = int(month)
year = int(year)
lastDay = calendar.monthrange(year,month)
lastDay = lastDay[1]
firstDate = datetime.datetime(year,month,1)
lastDate = datetime.datetime(year,month,lastDay)
query = query.filter(and_(Transaction.date>=firstDate,Transaction.date<=lastDate))
if type != 'all':
if type == 'Rent':
query = query.filter(Transaction.type=='Rent')
elif type == 'Deposit':
query = query.filter(Transaction.type=='Deposit')
else:
typeList = ['Rent','Deposit']
query = query.filter(~Transaction.type.in_(typeList))
if filter != 'all':
if filter == 'income':
query = query.filter(Transaction.amount>0)
else:
query = query.filter(Transaction.amount<0)
all_records = query
records = query.order_by(Transaction.date.desc()).all()
allTransList = []
for record in records:
contact = record.name
transDate = record.date
d = str(transDate.day)
m = str(transDate.month)
y = str(transDate.year)
hr = str(transDate.hour)
min = str(transDate.minute)
sec = str(transDate.second)
date = y+'-'+m+'-'+d+'-'+hr+'-'+min+'-'+sec
obj = {
'transId': record.id,
'transAmount': record.amount,
'transDate': date,
'transType': record.type,
'transContact': contact,
'transPropertyId': record.propertyid
}
allTransList.append(obj)
slicedRecords = []
counter = 0
has_prev_records = 0
for item in allTransList:
if startSlice <= counter < endSlice:
slicedRecords.append(item)
if counter == endSlice:
has_prev_records = 1
break
counter = counter + 1
transList = slicedRecords
backwards = lambda l: (backwards (l[1:]) + l[:1] if l else [])
transList = backwards(transList)
prevTotal = 0
if has_prev_records:
firstDate = transList[0]
firstDate = firstDate['transDate'].split('-')
year = int(firstDate[0])
mo = int(firstDate[1])
day = int(firstDate[2])
hr = int(firstDate[3])
min = int(firstDate[4])
sec = int(firstDate[5])
firstDate = datetime.datetime(year,mo,day,hr,min,sec)
prev_query = meta.Session.query(func.sum(Transaction.amount)).filter(and_(Transaction.companyid==companyId,\
Transaction.date<firstDate,\
Transaction.deleted==None))
if property != 'all':
prev_query = prev_query.filter(Transaction.propertyid==property)
if search != 'all':
prev_query = prev_query.filter(Transaction.name.like('%'+search+'%'))
if periodClick == '1' and datePeriod != 'all':
datePeriodList = Transaction.get_date_period(datePeriod)
startDate = datePeriodList[0]
endDate = datePeriodList[1]
startDate = datetime.datetime(startDate[0],startDate[1],startDate[2])
endDate = datetime.datetime(endDate[0],endDate[1],endDate[2])
prev_query = prev_query.filter(and_(Transaction.date>=startDate,Transaction.date<=endDate))
if month != 'all':
month = int(month)
year = int(year)
lastDay = calendar.monthrange(year,month)
lastDay = lastDay[1]
firstDate = datetime.datetime(year,month,1)
lastDate = datetime.datetime(year,month,lastDay)
prev_query = prev_query.filter(and_(Transaction.date>=firstDate,Transaction.date<=lastDate))
if type != 'all':
if type == 'Rent':
prev_query = prev_query.filter(Transaction.type=='Rent')
elif type == 'Deposit':
prev_query = prev_query.filter(Transaction.type=='Deposit')
else:
typeList = ['Rent','Deposit']
prev_query = prev_query.filter(~Transaction.type.in_(typeList))
if filter != 'all':
if filter == 'income':
prev_query = prev_query.filter(Transaction.amount>0)
else:
prev_query = prev_query.filter(Transaction.amount<0)
prev_query = prev_query.first()
prevTotal = prev_query[0]
if not prevTotal:
prevTotal = 0
transRecords = {
'transactions': transList,
'totalRecords': len(records),
'incomeTotal': incomeTotal,
'expenseTotal': expenseTotal,
'initLoad': initLoad,
'prevTotal': prevTotal
}
return json.dumps(transRecords)
def json(self):
month = request.POST['month']
year = request.POST['year']
type = request.POST['type']
search = request.POST['search']
filter = request.POST['filter']
datePeriod = request.POST['datePeriod']
page = request.POST['page']
initLoad = request.POST['initLoad']
periodClick = request.POST['periodClick']
updateBalance = request.POST['updateBalance']
property = request.POST['property']
companyId = request.environ.get("COMPANY_ID")
if not valid.search(search):
return json.dumps({'errors':[{'selector':'', "message":"Search only accepts letters and numbers. Please try again."}]})
startSlice = (int(page) - 1) * 20;
endSlice = startSlice + 20;
query = meta.Session.query(Transaction).filter(Transaction.companyid==companyId).filter(Transaction.deleted==None)
if property != 'all':
query = query.filter(Transaction.propertyid==property)
incomeTotal = 0
expenseTotal = 0
if initLoad == '1' or updateBalance == '1':
for record in query:
if record.income:
incomeTotal = incomeTotal + record.amount
else:
expenseTotal = expenseTotal + record.amount
if incomeTotal:
incomeTotal = '%.2f' % incomeTotal
if expenseTotal:
expenseTotal = '%.2f' % expenseTotal
if search != 'all':
query = query.filter(Transaction.name.like('%'+search+'%'))
if periodClick == '1' and datePeriod != 'all':
now = datetime.date.today()
if datePeriod == 'thisMonth':
m = now.month
y = now.year
lastDay = calendar.monthrange(y,m)
lastDay = lastDay[1]
startDate = datetime.date(y,m,1)
endDate = datetime.date(y,m,lastDay)
elif datePeriod == 'lastMonth':
m = now.month
y = now.year
if m == 1:
m = 12
y = y - 1
else:
m = m - 1
lastDay = calendar.monthrange(y,m)
lastDay = lastDay[1]
startDate = datetime.date(y,m,1)
endDate = datetime.date(y,m,lastDay)
elif datePeriod == 'lastQuarter':
quarterList = [[(1,1),(3,31)],[(4,1),(6,30)],[(7,1),(9,30)],[(10,1),(12,31)]]
m = now.month
y = now.year
num = m - 1
i = num / 3
q = quarterList[i - 1]
if m in [1,2,3]:
y = y - 1
startDate = datetime.date(y,q[0][0],q[0][1])
endDate = datetime.date(y,q[1][0],q[1][1])
elif datePeriod == 'thisYear':
y = now.year
startDate = datetime.date(y,1,1)
endDate = datetime.date(y,12,31)
else:
y = now.year - 1
startDate = datetime.datetime(y,1,1)
endDate = datetime.datetime(y,12,31)
query = query.filter(and_(Transaction.date>=startDate,Transaction.date<=endDate))
for record in query:
if record.income:
incomeTotal = incomeTotal + record.amount
else:
expenseTotal = expenseTotal + record.amount
if month != 'all':
month = int(month)
year = int(year)
lastDay = calendar.monthrange(year,month)
lastDay = lastDay[1]
firstDate = datetime.datetime(year,month,1)
lastDate = datetime.datetime(year,month,lastDay)
query = query.filter(and_(Transaction.date>=firstDate,Transaction.date<=lastDate))
if type != 'all':
if type == 'Rent':
query = query.filter(Transaction.type=='Rent')
elif type == 'Deposit':
query = query.filter(Transaction.type=='Deposit')
else:
typeList = ['Rent','Deposit']
query = query.filter(~Transaction.type.in_(typeList))
if filter != 'all':
if filter == 'income':
query = query.filter(Transaction.income==1)
else:
query = query.filter(Transaction.income==0)
records = query.order_by(Transaction.date.desc()).all()
transList = []
for record in records:
contact = record.name
transDate = record.date
d = str(transDate.day)
m = str(transDate.month)
y = str(transDate.year)
date = y+'-'+m+'-'+d
obj = {
'transId': record.id,
'transAmount': record.amount,
'transDate': date,
'transType': record.type,
'transIncome': record.income,
'transContact': contact,
'transPropertyId': record.propertyid
}
transList.append(obj)
slicedRecords = []
counter = 0
for item in transList:
if startSlice <= counter < endSlice:
slicedRecords.append(item)
counter = counter + 1
transList = slicedRecords
backwards = lambda l: (backwards (l[1:]) + l[:1] if l else [])
transList = backwards(transList)
transRecords = {
'transactions': transList,
'totalRecords': len(records),
'incomeTotal': incomeTotal,
'expenseTotal': expenseTotal,
'initLoad': initLoad
}
return json.dumps(transRecords)
"""
def find(self):
q = request.GET['q']
if not valid.search(q):
return json.dumps({'errors':[{'selector':'', "message":"Search only accepts letters and numbers. Please try again."}]})
page = int(request.GET['page'])
limit = int(request.GET['limit'])
filter = str(request.GET['filter'])
username = request.environ.get('REMOTE_USER')
property_access_list = Property.get_property_ids_of_username(username)
s = Search(q)
s.filter('company', request.environ.get('COMPANY_ID'))
s.filter('property', property_access_list)
s.filter('deleted', '0')
if filter != 'all': s.filter('type', filter)
matches = s.find(page, limit)
partial_results = matches['results']
results = []
for result in partial_results:
type = result['type']
uuid = result['uuid']
try:
if type == 'property':
property = Property.get_property(uuid)
results.append({'type': type,
'id': uuid,
'title': property.name,
'link': '/property/view/{0}'.format(property.id),
'description': '{0}, {1}, {2} {3}'.format(property.address,
property.city,
property.state,
property.zip)})
elif type == 'unit':
unit = Unit.get_unit(uuid)
property = Property.get_property(unit.propertyid)
results.append({'type': type,
'id': uuid,
'title': '{0} #{1}'.format(property.name, unit.label),
'link': '/unit/view/{0}'.format(unit.id),
'description': '{0} #{1}, {2}, {3} {4}'.format(property.address,
unit.label,
property.city,
property.state,
property.zip)})
elif type == 'tenant':
tenant = Tenant.get(uuid)
details = []
if tenant.email: details.append(tenant.email)
if tenant.phone: details.append(tenant.phone)
contact_info = '<br />'.join(details)
results.append({'type': type,
'id': uuid,
'title': '{0} {1}'.format(tenant.first_name, tenant.last_name),
'link': '/tenant/view/{0}'.format(tenant.id),
'description': contact_info})
elif type == 'contact':
contact = Contact.get(uuid)
details = []
if contact.email: details.append(contact.email)
if contact.phone: details.append(contact.phone)
contact_info = '<br />'.join(details)
address = '<br />{0}, {1}'.format(contact.address, contact.city) if contact.address and contact.city else ''
description = '<br />{0}'.format(contact.description) if contact.description else ''
results.append({'type': type,
'id': uuid,
'title': contact.label,
'description': '{0}{1}{2}'.format(contact_info,
address,
description)})
except:
pass
return json.dumps({'results':results,
'start':matches['start'],
'end':matches['end'],
'page':matches['page'],
'total':matches['total']})
def json(self):
companyid = request.environ.get("COMPANY_ID")
propertyId = request.POST['propertyId']
unit = request.POST['unit']
sort = request.POST['sort']
sortOrder = request.POST['sortOrder']
status = request.POST['status']
curPage = request.POST['curPage']
if not valid.search(unit):
return json.dumps({'errors':[{'selector':'', "message":"Unit filter only accepts letters and numbers. Please try again."}]})
if status == 'vacant':
status = 'Vacant'
elif status == 'underContract':
status = 'Under Contract'
elif status == 'movingIn':
status = 'Moving In'
elif status == 'movingOut':
status = 'Moving Out'
elif status == 'm2m':
status = 'Month-to-month'
now = datetime.date.today()
startSlice = (int(curPage) - 1) * 20;
endSlice = startSlice + 20;
propIdList = []
if propertyId == 'all':
user_id = Manager.get_id_of_username(request.environ.get("REMOTE_USER"))
properties = Property.get_propertyList_of_userid(user_id)
for item in properties:
propIdList.append(item.id)
else:
propIdList.append(propertyId)
if sort == 'rent':
if sortOrder == 'asc':
order = Lease.rent.asc()
else:
order = Lease.rent.desc()
elif sort == 'due':
if sortOrder == 'asc':
order = Lease.due.asc()
else:
order = Lease.due.desc()
elif sort == 'latePay':
order = Property.name.asc() # ignore latePay for time being
else:
order = Unit.label.asc()
records = meta.Session.query(Unit.label, Unit.id, Lease.rent, Lease.due,\
Lease.startdate, Lease.enddate, Lease.outdate, Lease.id,\
Property.id, Property.name).outerjoin(Lease,Property).\
filter(Unit.deleted==None).\
filter(Lease.deleted==None).\
filter(Unit.propertyid.in_(propIdList))
# records at this point includes multiple records of same unit with multiple lease records
allRecords = []
for record in records:
allRecords.append((record[1], record[4], record[6]))
no_lease_ever = []
has_old_lease = []
cur_active_lease = []
future_lease = []
for item in allRecords:
unitid = item[0]
startdate = item[1]
outdate = item[2]
if not startdate:
no_lease_ever.append(unitid)
elif now >= startdate and (not outdate or now <= outdate):
cur_active_lease.append(unitid)
elif now > startdate and (now > outdate):
has_old_lease.append(unitid)
elif now < startdate:
future_lease.append(unitid)
records = records.filter(or_(Unit.id.in_(no_lease_ever),\
(and_(Unit.id.in_(cur_active_lease),\
now>=Lease.startdate,\
(or_(Lease.outdate==None,now<Lease.outdate)))),\
(and_(Unit.id.in_(has_old_lease),\
~Unit.id.in_(cur_active_lease),\
~Unit.id.in_(future_lease))),\
(and_(Unit.id.in_(future_lease),\
~Unit.id.in_(cur_active_lease),\
(or_(Lease.outdate==None,\
now<Lease.outdate))))))
if unit != '0':
labelLike = '%'+unit+'%'
records = records.filter(Unit.label.like('%'+labelLike+'%'))
records = records.order_by(order,Unit.label).all()
unsortedRecords = []
duplicateUnit = []
for label, unitid, rent, due, start, end, out, leaseid, propid, propname in records:
if unitid in duplicateUnit and unitid in has_old_lease and not unitid in cur_active_lease and not unitid in future_lease:
continue
obj = {
'label': label,
'unitid': unitid,
'rent': rent,
'due': due,
'start': start,
'end': end,
'out': out,
'leaseid': leaseid,
'propid': propid,
'propname': propname
}
unsortedRecords.append(obj)
duplicateUnit.append(unitid)
labelUnitIdList = []
recordsList = []
for record in unsortedRecords:
label = record['label']
unitid = record['unitid']
rent = record['rent']
due = record['due']
start = record['start']
end = record['end']
out = record['out']
leaseid = record['leaseid']
propid = record['propid']
propname = record['propname']
labelId = label+'*'+unitid
labelUnitIdList.append(labelId)
obj = {
'label': label,
'unitid': unitid,
'rent': rent,
'due': due,
'start': start,
'end': end,
'out': out,
'leaseid': leaseid,
'propid': propid,
'propname': propname
}
recordsList.append(obj)
sortedRecords = []
if sort == "0":
labelUnitIdList = sorted(labelUnitIdList, key=self.keynat)
for labelId in labelUnitIdList:
labelId = labelId.split('*')
label = labelId[0]
id = labelId[1]
sortedRecords.extend([d for d in recordsList if d['label'] == label and d['unitid'] == id])
recordsList = sortedRecords
unitRecords = []
vacantList = []
vacant = 0
m2m = 0
underContract = 0
movingIn = 0
movingOut = 0
totalStatusRecords = 0
for record in recordsList:
label = record['label']
unitid = record['unitid']
rent = record['rent']
due = record['due']
start = record['start']
end = record['end']
out = record['out']
leaseid = record['leaseid']
propid = record['propid']
propname = record['propname']
unit_status = Lease.unit_status(start, end, out)
if unit_status == "Vacant":
rent = '-'
due = '-'
vacant = vacant + 1
elif unit_status == 'Month-to-month':
m2m = m2m + 1
elif unit_status == 'Under Contract':
underContract = underContract + 1
elif unit_status == 'Moving In':
movingIn = movingIn + 1
elif unit_status == 'Moving Out':
movingOut = movingOut + 1
if status == "all" or unit_status == status:
totalStatusRecords = totalStatusRecords + 1
obj = {
'propertyName': propname,
'propertyId': propid,
'leaseid': leaseid,
'unitId': unitid,
'label': label,
'rent': rent,
'due': due,
'latepay': '-',
'status': unit_status
}
if unit_status == 'Vacant':
vacantList.append(obj)
else:
unitRecords.append(obj)
unitRecords.extend(vacantList)
slicedRecords = []
counter = 0
for record in unitRecords:
if startSlice <= counter < endSlice:
slicedRecords.append(record)
counter = counter + 1
slicedRecords = Transaction.calLatefee(slicedRecords)
unitRecords = slicedRecords
propertyUnit = {
'unit': unitRecords,
'totalStatusRecords': totalStatusRecords,
'vacant': vacant,
'm2m': m2m,
'underContract': underContract,
'movingIn': movingIn,
'movingOut': movingOut
}
return json.dumps(propertyUnit)
"""