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