def total(self): from .transaction import Transaction total = Transaction.objects.filter(category=self).aggregate( total_value=aggregates.Sum('value'))['total_value'] if total is None: return 0 return round(total, 2)
def percent(self): if not self.votes: return 0.0 result = PollChoice.objects.filter(poll=self.poll).aggregate( aggregates.Sum("votes")) votes_sum = result["votes__sum"] return float(self.votes) / votes_sum * 100
def get_balance(self, start_date): from .transaction import Transaction if start_date.month+1 > 12: end_date = dt.date(year=start_date.year+1, month=1, day=5) else: end_date = dt.date(year=start_date.year, month=start_date.month + 1, day=5) total = Transaction.objects\ .filter(account=self, date__gte=start_date, date__lt=end_date)\ .aggregate(total_value=aggregates.Sum('value'))['total_value'] if total is None: total = 0 return round(total, 2)
def __init__(self): super().__init__( fields={ # example: model field # Order by order creation date 'created': 'created', # example: expression on related model # Order by user full name 'user': functions.Concat( models.F('user__first_name'), models.Value(' '), models.F('user__last_name'), ), # example: aggregate expression 'total_quantity': aggregates.Sum(models.F('order_lines__quantity')), })
def aggregate_timeseries(self, param, **kwargs): """Get timeseries data for SMS, calls, data or tower stats. Args: param: the "kind" of UsageEvent to filter for or the key of a TimeseriesStat. See the KINDS and KEYS constants for valid values. Keyword Args: start_time_epoch: start of the timespan in seconds since epoch (default is the start of epoch) end_time_epoch: end of the timespan in seonds since epoch (default is -1 which gets translated into the current time) interval: the interval on which to count, valid values are years, months, weeks, days, hours or minutes aggregation: controls the aggregation method. May be one of 'count' or 'duration' (the default is 'count'). Returns: a list of (epoch timestamp, value) tuples Raises: qsstats.InvalidInterval if the interval is unknown """ start_time_epoch = kwargs.pop('start_time_epoch', 0) end_time_epoch = kwargs.pop('end_time_epoch', -1) interval = kwargs.pop('interval', 'months') aggregation = kwargs.pop('aggregation', 'count') # Turn the start and end epoch timestamps into datetimes. start = datetime.fromtimestamp(start_time_epoch, pytz.utc) if end_time_epoch != -1: end = datetime.fromtimestamp(end_time_epoch, pytz.utc) else: end = datetime.fromtimestamp(time.time(), pytz.utc) # Build the queryset -- first determine if we're dealing with # UsageEvents or TimeseriesStats. filters = None if param in USAGE_EVENT_KINDS: objects = models.UsageEvent.objects filters = Q(kind=param) elif param in TIMESERIES_STAT_KEYS: objects = models.TimeseriesStat.objects filters = Q(key=param) # Filter by infrastructure level. if self.level == 'tower': filters = filters & Q(bts__id=self.level_id) elif self.level == 'network': filters = filters & Q(network__id=self.level_id) elif self.level == 'global': pass # Create the queryset itself. queryset = objects.filter(filters) # Use qsstats to aggregate the queryset data on an interval. if aggregation == 'duration': queryset_stats = qsstats.QuerySetStats( queryset, 'date', aggregate=aggregates.Sum('billsec')) elif aggregation == 'up_byte_count': queryset_stats = qsstats.QuerySetStats( queryset, 'date', aggregate=aggregates.Sum('uploaded_bytes')) elif aggregation == 'down_byte_count': queryset_stats = qsstats.QuerySetStats( queryset, 'date', aggregate=aggregates.Sum('downloaded_bytes')) elif aggregation == 'average_value': queryset_stats = qsstats.QuerySetStats( queryset, 'date', aggregate=aggregates.Avg('value')) else: queryset_stats = qsstats.QuerySetStats(queryset, 'date') timeseries = queryset_stats.time_series(start, end, interval=interval) # The timeseries results is a list of (datetime, value) pairs. We need # to convert the datetimes to timestamps with millisecond precision and # then zip the pairs back together. datetimes, values = zip(*timeseries) timestamps = [ int(time.mktime(dt.timetuple()) * 1e3 + dt.microsecond / 1e3) for dt in datetimes ] # Round the stats values when necessary. rounded_values = [] for value in values: if round(value) != round(value, 2): rounded_values.append(round(value, 2)) else: rounded_values.append(value) return zip(timestamps, rounded_values)
def supplierPO_form(request): quantity = 0 delivery_date = None item = None supplier = None inventory = Inventory.objects.all() suggested = [] ldpe = Inventory.objects.filter(rm_type='LDPE').aggregate( Sum('quantity'))['quantity__sum'] lldpe = Inventory.objects.filter(rm_type='LLDPE').aggregate( Sum('quantity'))['quantity__sum'] hdpe = Inventory.objects.filter(rm_type='HDPE').aggregate( Sum('quantity'))['quantity__sum'] pe = Inventory.objects.filter(rm_type='Pelletized PE').aggregate( Sum('quantity'))['quantity__sum'] pet = Inventory.objects.filter(rm_type='PET').aggregate( Sum('quantity'))['quantity__sum'] pp = Inventory.objects.filter(rm_type='PP').aggregate( Sum('quantity'))['quantity__sum'] hd = Inventory.objects.filter(rm_type='Pelletized HD').aggregate( Sum('quantity'))['quantity__sum'] quantities = { 'LDPE': ldpe, 'LLDPE': lldpe, 'HDPE': hdpe, 'Pelletized PE': pe, 'PET': pet, 'PP': pp, 'Pelletized HD': hd, } suggested.append(min(quantities, key=quantities.get)) del quantities[min(quantities, key=quantities.get)] suggested.append(min(quantities, key=quantities.get)) del quantities[min(quantities, key=quantities.get)] suggested.append(min(quantities, key=quantities.get)) del quantities[min(quantities, key=quantities.get)] replenish = [] replenish.extend(Inventory.objects.filter(rm_type=suggested[0])) replenish.extend(Inventory.objects.filter(rm_type=suggested[1])) replenish.extend(Inventory.objects.filter(rm_type=suggested[2])) if request.META['HTTP_REFERER'].startswith('http://127.0.0.1:8000/sales/'): print('sales:confirm_order/rush_order_assessment') quantity = request.session.get('matreq_quantity') delivery_date = datetime.now().date() if request.session.get('matreq_mat'): try: item = Inventory.objects.filter( rm_type=request.session.get('matreq_mat')).first() supplier = Supplier.objects.filter(id=item.supplier_id) item = item.id except Inventory.DoesNotExist: item = None elif request.session.get('matreq_ink'): try: item = Inventory.objects.get( item=request.session.get('matreq_ink')) supplier = Supplier.objects.filter(id=item.supplier_id) item = item.id except Inventory.DoesNotExist: item = None elif request.META['HTTP_REFERER'].startswith( 'http://127.0.0.1:8000/inventory/inventory-forecast-details/'): print('inventory:forecast') item = request.session.get('item') inv = Inventory.objects.get(id=item) supplier = Supplier.objects.filter(id=inv.supplier_id) if request.session['forecast'] == 'SES': quantity = request.session['forecast_ses'][1] date = request.session['forecast_ses'][0] delivery_date = date[:10] elif request.session['forecast'] == 'HWES': quantity = request.session['forecast_hwes'][1] date = request.session['forecast_hwes'][0] delivery_date = date[:10] elif request.session['forecast'] == 'MOVING': quantity = request.session['forecast_moving_average'][1] date = request.session['forecast_moving_average'][0] delivery_date = date[:10] else: delivery_date = datetime.now().date() supplier = Supplier.objects.all() formset = inlineformset_factory( SupplierPO, SupplierPOItems, form=SupplierPOItemsForm, extra=1, can_delete=True, fields=['quantity', 'item'], widgets={ 'quantity': forms.NumberInput(attrs={'value': quantity}), #FIXME Item queryset }) form = SupplierPOForm(request.POST) if request.method == "POST": if form.is_valid(): new_form = form.save() new_form = new_form.pk form_instance = SupplierPO.objects.get(id=new_form) formset = formset(request.POST, instance=form_instance) print(formset.errors) if formset.is_valid(): print('formset valid') for form in formset: form.save() formset_items = SupplierPOItems.objects.filter( supplier_po_id=new_form) #formset_items_rm = Inventory.objects.filter(id = id) #formset_items.price = formset_items_rm.price formset_item_total = formset_items.aggregate( sum=aggregates.Sum('total_price'))['sum'] or 0.00 #totalled_supplierpo = SupplierPO.objects.get(id=new_form) form_instance.total_amount = formset_item_total form_instance.save() return redirect('inventory:supplierPO_list') form.fields["supplier"].queryset = supplier form.fields["delivery_date"] = forms.DateField( label='delivery_date', widget=forms.DateInput(attrs={'value': delivery_date})) form.fields["client_po"].queryset = JobOrder.objects.filter( ~Q(status='Ready for delivery') & ~Q(status='Delivered')) # for each in formset: # each.fields["quantity"] = forms.FloatField(label='quantity', # widget=forms.NumberInput(attrs={'value': quantity})) # each.fields["item"].queryset = Inventory.objects.filter(id=item) return render( request, 'inventory/supplierPO_form.html', { 'form': SupplierPOForm, 'formset': formset, 'replenish': replenish, 'EOQ_ldpe': eoq().get('EOQ_ldpe'), 'EOQ_lldpe': eoq().get('EOQ_lldpe'), 'EOQ_hdpe': eoq().get('EOQ_hdpe'), 'EOQ_pe': eoq().get('EOQ_pe'), 'EOQ_pet': eoq().get('EOQ_pet'), 'EOQ_pp': eoq().get('EOQ_pp'), 'EOQ_hd': eoq().get('EOQ_hd') })
def volume(self): return utils.addDollarSign(models.Order.objects.aggregate(agg.Sum('price'))['price__sum'])
def create_client_po(request): #note:instance should be an object clientpo_item_formset = inlineformset_factory(ClientPO, ClientItem, form=ClientPOFormItems, extra=1, can_delete=True) if request.method == "POST": form = ClientPOForm(request.POST) #Get session user id client_id = request.session['session_userid'] current_client = Client.objects.get(id=client_id) ''' #check if client has overdue balance credit_status = ClientCreditStatus.objects.get(client_id = current_client) if (credit_status.outstanding_balance < 0): credit_status = 1 ''' message = "" print(form) if form.is_valid(): #Save PO form then use newly saved ClientPO as instance for ClientPOItems new_form = form.save() new_form = new_form.pk form_instance = ClientPO.objects.get(id=new_form) # Set ClientPO.client from session user form_instance.client = current_client form_instance.save() #TODO invoice should no be saved if PO is disapproved #Create JO object with ClientPO as a field jo = JobOrder(client_po=form_instance) jo.save() #Use PO form instance for PO items formset = clientpo_item_formset(request.POST, instance=form_instance) #print(formset) if formset.is_valid(): for form in formset: form.save() formset_items = ClientItem.objects.filter( client_po_id=new_form) formset_item_total = formset_items.aggregate( sum=aggregates.Sum('item_price'))['sum'] or 0 totalled_clientpo = ClientPO.objects.get(id=new_form) totalled_clientpo.total_amount = formset_item_total totalled_clientpo.save() # Create Invoice invoice = SalesInvoice(client=current_client, client_po=form_instance, total_amount=formset_item_total, amount_due=0) invoice.save() invoice = invoice.pk #TODO Invoice should not be issued unless JO is complete invoice = SalesInvoice.objects.get(id=invoice) invoice.amount_due = invoice.total_amount_computed credit_status = ClientCreditStatus.objects.get( client_id=current_client) outstanding_balance = credit_status.outstanding_balance outstanding_balance += invoice.amount_due credit_status.outstanding_balance = outstanding_balance invoice.save() credit_status.save() message = "PO successfully created" else: message += "Formset error" else: message = "Form is not valid" #todo change index.html. page should be redirected after successful submission return render(request, 'accounts/client_page.html', {'message': message}) else: return render(request, 'sales/clientPO_form.html', { 'formset': clientpo_item_formset(), 'form': ClientPOForm })
def current_balance(self): from .transaction import Transaction return round(self.balance_offset + Transaction.objects .filter(account=self) .aggregate(total_value=aggregates.Sum('value'))['total_value'], 2)