def sixMonthCalc(request):
req = request.REQUEST
if param_exist("date",req):
date = DateService.parse(req['date'])
else:
date = DateService.today()
fromDate = DateService.addMonth(DateService.firstDateOfMonth(date),-6)
toDate = DateService.lastDateOfMonth(date)
q = Expense.objects.extra(select={'sum': 'sum(amount)'}).values('sum','date')
q = q.filter(date__gte=fromDate, date__lte=toDate).order_by('date')
if param_exist("subC",req):
q = q.filter(subCategory=req['subC'])
elif param_exist("cat",req):
c = SubCategory.objects.filter(category=req['cat'])
q = q.filter(subCategory__in=c)
if param_exist("payT",req):
q = q.filter(paymentType=req['payT'])
q.query.group_by = ['month(date)']
list = []
for exp in q:
list.append('[%d,%s]' % (DateService.toLong(exp['date']),exp['sum']))
data = "[" + ",".join(list) + "]"
return data
def stats(request):
req = request.REQUEST
toDate = DateService.today()
fromDate = datetime.date(toDate.tm_year, toDate.tm_mon, 1)
fromDate = DateService.addMonth(fromDate,-12)
toDate = datetime.date(toDate.tm_year, toDate.tm_mon, 1)
q = Income.objects.values('period').annotate(sum=Sum('amount'))
q = q.filter(period__gte=fromDate, period__lte=toDate).order_by('period')
# q = Income.objects.extra(select={'sum': 'sum(amount)'}).values('sum','period')
# q = q.filter(period__gte=fromDate, period__lte=toDate).order_by('period')
# q.query.group_by = ['period']
#q = Income.objects.filter(period__gte=fromDate, period__lte=toDate).order_by('period')
list = []
for exp in q:
#list.append('[%d,%s]' % (int(DateService.toLong(exp.period)),exp.amount))
list.append('[%d,%s]' % (int(DateService.toLong(exp['period'])),exp['sum']))
data = "[" + ",".join(list) + "]"
return data
def sixMonthCalc(request):
req = request.REQUEST
if param_exist("date",req):
date = DateService.parse(req['date'])
else:
date = DateService.today()
fromDate = DateService.addMonth(DateService.firstDateOfMonth(date),-6)
toDate = DateService.lastDateOfMonth(date)
query = "SELECT sum(amount), date FROM expense "\
"WHERE date between '%s' and '%s'" % (str(fromDate), str(toDate))
if param_exist("subC",req):
query += " AND sub_category_id = '%s'" % req['subC']
elif param_exist("cat",req):
c = SubCategory.objects.filter(category=req['cat'])
query += " AND sub_category_id in (%s)" % ",".join(["'"+str(s.id)+"'" for s in c])
if param_exist("payT",req):
query += " AND payment_type_id = '%s'" % req['payT']
query += " group by month(date)"
cursor = connection.cursor()
cursor.execute(query)
list = []
for exp in cursor.fetchall():
sum, date = exp
i = Income.objects.extra(select={'sum': 'sum(amount)'}).values('sum')
i = i.filter(period = DateService.firstDateOfMonth(date.timetuple()))
val = i[0]['sum']
if val is None:
val = 0
list.append({'date': date, 'expense': sum, 'income': val})
data = '{"rows": %s}' % (JsonParser.parse(list))
return data
