def calc(request):
req = request.REQUEST
if param_exist("date",req):
date = DateService.parse(req['date'])
else:
date = DateService.today()
fromDate = DateService.firstDateOfMonth(date)
toDate = DateService.lastDateOfMonth(date)
q = Expense.objects.filter(date__gte=fromDate, date__lte=toDate)
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'])
sum=0
for exp in q:
sum+=exp.amount
today = DateService.today()
if today.tm_year==date.tm_year and today.tm_mon==date.tm_mon:
days=today.tm_mday
else:
days=DateService.lastDayOfMonth(date)
avg = sum / days
data = '{"data":{"total":%s,"avg":%s}}' % (sum,avg)
return data
def handle(self, *args, **options):
list = Debits.objects.filter(Q(since__lte=datetime.now()) & (Q(last__isnull=True) | Q(last__lt=DateService.firstDateOfMonth(DateService.today()))))
for debit in list:
Expense.objects.create(text=debit.text,
amount=debit.amount,
subCategory=debit.subCategory,
paymentType=debit.paymentType,
date=DateService.invert(DateService.parse("%d/%d/%d" % (debit.day,
DateService.today().tm_mon,
DateService.today().tm_year))))
debit.last = datetime.now()
debit.save()
self.stdout.write("Processed %d\n" % list.count())
def monthCalc(request):
req = request.REQUEST
if param_exist("date",req):
date = DateService.parse(req['date'])
else:
date = DateService.today()
fromDate = DateService.firstDateOfMonth(date)
toDate = DateService.lastDateOfMonth(date)
q = Expense.objects.extra(select={'sum': 'sum(amount)'}).values('sum','date')
#q = Expense.objects.filter(date__gte=fromDate, date__lte=toDate)
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 = ['date']
list = []
for exp in q:
list.append('[%d,%s]' % (int(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
