def search(request):
company = request.user.account.company
results = OrderTransferItem.objects.filter(Q(order__order__supplier=company) | Q(order__order__customer=company))
contact_id = request.GET.get('contact_id', None)
if contact_id:
results = results.filter(Q(order__order__customer__id=contact_id) | Q(order__order__supplier__id=contact_id))
customer_id = request.GET.get('customer_id', None)
if customer_id:
results = results.filter(order__order__customer__id=customer_id)
supplier_id = request.GET.get('supplier_id', None)
if supplier_id:
results = results.filter(order__order__supplier__id=supplier_id)
product_id = request.GET.get('product_id', None)
if product_id:
results = results.filter(order__info_id=product_id, order__info_type=Product.content_type())
date = request.GET.get('date', None)
if date:
date = datetime.strptime(date, settings.DATE_FORMAT)
sort = request.GET.get('sort', 'dsc')
if sort == 'dsc':
if date: results = results.filter(transfer__date__lte=date)
results = results.order_by('-transfer__date')
else:
if date: results = results.filter(transfer__date__gte=date)
results = results.order_by('transfer__date')
return results
def handle_noargs(self, **options):
admin = User.objects.get(username='******')
primary = admin.account.company
# list of zero stock products
no_stock_ids = set(
Product.objects.exclude(stocks__quantity__gt=0).values_list(
'id', flat=True))
# list of products with sales
sale_ids = set(
OrderItem.objects.filter(
info_type=Product.content_type).values_list('info_id',
flat=True))
excluded_ids = no_stock_ids.difference(sale_ids)
print excluded_ids
raw_input("Press to continue...")
items = ItemAccount.objects.filter(
cost=0, owner=primary,
item_type=Product.content_type()).exclude(item_id__in=excluded_ids)
fname = "{} {}".format('for_costing', '.csv')
with open(fname, 'wb') as f:
writer = csv.writer(f)
writer.writerow(['ID', 'Name', 'Summary', 'Cost'])
for i in items:
print '{} {} {} {}'.format(i.id, i.item.name(), i.item.summary,
i.cost)
writer.writerow([i.id, i.item.name(), i.item.summary, i.cost])
def bookkeep_adjustments(self):
physicals = Physical.objects.filter(
date__lte=self.end_date,
date__gte=self.start_date,
stock__location__owner=self.primary)
adjustments = AdjustmentItem.objects.filter(
adjustment__date__lte=self.end_date,
adjustment__date__gte=self.start_date,
adjustment__location__owner=self.primary,
adjustment__labels__name=Adjustment.VALID)
delta_map = {}
count = len(physicals)
for i, p in enumerate(physicals):
sys.stdout.write("Calculating physicals... {} of {}\r".format(
i + 1, count))
sys.stdout.flush()
delta_map[p.stock.product.id] = delta_map.get(
p.stock.product.id, 0) + p.delta
count = len(adjustments)
for i, a in enumerate(adjustments):
sys.stdout.write("Calculating adjustments... {} of {}\r".format(
i + 1, count))
sys.stdout.flush()
delta_map[a.product.id] = delta_map.get(a.product.id, 0) + a.delta
total = 0
accounts = ItemAccount.objects.filter(item_type=Product.content_type(),
owner=self.primary)
count = len(accounts)
for i, account in enumerate(accounts):
sys.stdout.write("Writing out adjustments... {} of {}\r".format(
i + 1, count))
sys.stdout.flush()
key = account.item_id
adjustment = delta_map.get(key, 0) * self.context.estimate(
account.item)
total += adjustment
account.data(ItemAccount.YEAR_ADJUSTMENTS, self.cutoff, adjustment)
self.primary.account.data(CompanyAccount.YEAR_ADJUSTMENTS, self.cutoff,
total)
# Calculate bad debts
writeoffs = Bill.objects.filter(supplier=self.primary,
labels__date__gte=self.start_date,
labels__date__lte=self.end_date,
labels__name=Bill.BAD)
bad_debts = 0
for w in writeoffs:
bad_debts += w.outstanding()
self.primary.account.data(CompanyAccount.YEAR_BAD_DEBTS, self.cutoff,
bad_debts)
print "\nDone."
def handle(self, *args, **options):
admin = User.objects.get(username__exact='admin') # hack
primary = admin.account.company
cutoff = primary.account.current_cutoff_date()
terms = ' '.join(args)
product_ids = SearchQuerySet().auto_query(terms).models(
Product).values_list('pk', flat=True)
items = ItemAccount.objects.filter(item_id__in=product_ids,
item_type=Product.content_type(),
owner=primary)
today = datetime.today()
last_year = today - timedelta(days=365)
transfers = OrderTransferItem.objects.filter(
order__info_id__in=product_ids,
order__info_type=Product.content_type(),
transfer__date__lte=today,
transfer__date__gte=last_year)
sales = {}
for t in transfers:
if not t.transfer.labeled(OrderTransfer.CANCELED):
sales[t.order.info_id] = sales.get(t.order.info_id,
0) + t.net_quantity
fname = "{}{}".format(terms, '.csv')
with open(fname, 'wb') as f:
writer = csv.writer(f)
writer.writerow([
'Brand', 'Model', 'Summary', 'On Hand', '1 Year Sales',
'Average Monthly Rate'
])
for i in items:
#data = i.data(ItemAccount.YEAR_SALES_QUANTITY, cutoff)
item_sales = sales.get(i.item.id, 0)
print '{} {} {} {} {}'.format(i.item.brand, i.item.model,
i.item.summary, i.stock,
item_sales, item_sales / 12)
writer.writerow([
i.item.brand, i.item.model, i.item.summary, i.stock,
item_sales, item_sales / 12
])
def order(request):
items = []
terms = request.GET.get('terms', None)
months = decimal.Decimal(request.GET.get('months', 6))
if terms:
terms = terms.strip()
results = SearchQuerySet().auto_query(terms)
results = results.models(Product)
product_ids = results.values_list('pk', flat=True)
primary = request.user.account.company
accounts = ItemAccount.objects.filter(owner=primary,
item_id__in=product_ids,
item_type=Product.content_type())
today = datetime.today()
last_year = today - timedelta(days=365)
transfers = OrderTransferItem.objects.filter(
order__order__supplier=primary,
order__info_id__in=product_ids,
order__info_type=Product.content_type(),
transfer__date__lte=today,
transfer__date__gte=last_year,
transfer__labels__name=OrderTransfer.VALID)
sales = {}
for t in transfers:
sales[t.order.info_id] = sales.get(t.order.info_id,
0) + t.net_quantity
for a in accounts:
a.sales = sales.get(a.item_id, 0)
a.rate = a.sales / 12
a.quantity = a.rate * months - a.stock
if a.quantity < 0: a.quantity = 0
items.append(a)
return render_to_response('task/purchasing/order.html',
dict(items=items, terms=terms, months=months),
context_instance=RequestContext(request))
def preload(self):
purchases = OrderTransferItem.objects.filter(transfer__labels__name=OrderTransfer.VALID,
transfer__date__lte=self.end_date,
transfer__date__gte=self.start_date,
transfer__order__customer__id=self.primary_id).order_by('transfer__date')
quantities = {}
values = {}
for item in purchases:
key = (item.order.info_id, item.order.info_type.id)
if item.net_quantity > 0:
quantities[key] = quantities.get(key, 0) + item.net_quantity
values[key] = values.get(key, 0) + (item.order.price * item.net_quantity)
for key in quantities.keys():
self.cache[key] = values[key] / quantities[key]
orders = OrderItem.objects.filter(order__labels__name=Order.CLOSED,
order__customer__id=self.primary_id,
info_type=Product.content_type(),
order__date__lte=self.end_date).order_by('order__date')
for o in orders:
self.last_known_cache[o.info.id] = o.price
self.default_cache = dict(ItemAccount.objects.filter(item_type=Product.content_type(),
owner__id=self.primary_id).values_list('item_id', 'cost'))
def urgent(request):
primary = request.user.account.company
# get all sale and purchase items with non-zero balance
order_items = OrderItem.objects.pending().filter(
info_type=Product.content_type())
products = {}
for i in order_items:
pid = i.info.id
if pid not in products:
products[pid] = i.info
products[pid].stock = 0
products[pid].incoming = 0
products[pid].outgoing = 0
products[pid].required = 0
if i.order.supplier == primary:
products[pid].outgoing = products[pid].outgoing + i.balance
elif i.order.customer == primary:
products[pid].incoming = products[pid].incoming + i.balance
accounts = ItemAccount.objects.filter(owner=primary,
item_type=Product.content_type(),
item_id__in=products.keys())
for account in accounts:
products[account.item_id].stock = account.stock
# stocks = Stock.objects.filter(product__id__in=products.keys())
# for s in stocks:
# pid = s.product.id
# products[pid].stock = products[pid].stock + s.quantity
urgent = []
for pid, p in products.items():
p.required = p.outgoing - p.stock - p.incoming
if p.required > 0:
urgent.append(p)
return paginate(request, urgent, 'task/purchasing/urgent_results.html')
def search(request):
company = request.user.account.company
orders = Order.objects.filter(Q(supplier=company) | Q(customer=company))
contact_id = request.GET.get('contact_id', None)
if contact_id:
orders = orders.filter(
Q(customer__id=contact_id) | Q(supplier__id=contact_id))
customer_id = request.GET.get('customer_id', None)
if customer_id:
orders = orders.filter(customer__id=customer_id)
supplier_id = request.GET.get('supplier_id', None)
if supplier_id:
orders = orders.filter(supplier__id=supplier_id)
terms = request.GET.get('terms', None)
if terms:
terms = terms.split(' ')
products = Product.objects.all()
services = Service.objects.all()
for t in terms:
products = products.filter(
Q(model__icontains=t) | Q(brand__icontains=t)).values_list(
'id', flat=True)
services = services.filter(name__icontains=t).values_list(
'id', flat=True)
product_type = Product.content_type()
service_type = Service.content_type()
orders = orders.filter(
Q(items__info_id__in=products,
items__info_type__id=product_type.id)
| Q(items__info_id__in=services,
items__info_type__id=service_type.id) | Q(code__icontains=t))
order_status = request.GET.get('status', None)
if order_status == 'OPEN':
orders = orders.filter(labels__name=Order.OPEN)
elif order_status == 'CLOSED':
orders = orders.filter(labels__name=Order.CLOSED)
elif order_status == 'CANCELED':
orders = orders.filter(labels__name=Order.CANCELED)
return orders
def search(request):
company = request.user.account.company
transfers = OrderTransfer.objects.filter(
Q(order__supplier=company) | Q(order__customer=company))
contact_id = request.GET.get('contact_id', None)
if contact_id:
transfers = transfers.filter(
Q(order__customer__id=contact_id)
| Q(order__supplier__id=contact_id))
customer_id = request.GET.get('customer_id', None)
if customer_id:
transfers = transfers.filter(order__customer__id=customer_id)
supplier_id = request.GET.get('supplier_id', None)
if supplier_id:
transfers = transfers.filter(order__supplier__id=supplier_id)
terms = request.GET.get('terms', None)
if terms:
terms = terms.split(' ')
products = Product.objects.all()
services = Service.objects.all()
for t in terms:
products = products.filter(
Q(model__icontains=t) | Q(brand__icontains=t)).values_list(
'id', flat=True)
services = services.filter(name__icontains=t).values_list(
'id', flat=True)
transfers = transfers.filter(
Q(order__items__info_id__in=products,
order__items__info_type__id=Product.content_type().id)
| Q(order__items__info_id__in=services,
order__items__info_type__id=Service.content_type().id))
# receipts, releases, returns
status = request.GET.get('status', None)
if status == 'CANCELED':
transfers = transfers.filter(labels__name=OrderTransfer.CANCELED)
elif status == 'RETURN':
transfers = transfers.filter(labels__name=OrderTransfer.RETURN)
return transfers
def merge_products(p1, p2):
# Order items
items = OrderItem.objects.filter(info_id=p1.id,
info_type=Product.content_type())
for i in items:
i.info_id = p2.id
i.save()
# Adjustment item
items = AdjustmentItem.objects.filter(product=p1)
for i in items:
i.product = p2
i.save()
# Stock transfer item
items = StockTransferItem.objects.filter(product=p1)
for i in items:
i.product = p2
i.save()
# Stock
stocks = Stock.objects.filter(product=p1)
for s1 in stocks:
s2, _ = Stock.objects.get_or_create(product=p2, location=s1.location)
merge_stocks(s1, s2)
# ItemAccount
accounts = ItemAccount.objects.filter(item_id=p1.id,
item_type=p1.content_type())
for a in accounts:
p2_account, _ = ItemAccount.objects.get_or_create(
item_id=p2.id, item_type=p2.content_type(), owner=a.owner)
p2_account.stock += a.stock
if p2_account.cost == 0: p2_account.cost = a.cost
if p2_account.price == 0: p2_account.price = a.price
p2_account.save()
a.delete()
p1.delete()
def products(self):
return self.get_query_set().filter(
order__info_type=Product.content_type())
def bookkeep_accounts(self):
# Sales, Purchases, Cost, Profit
logger.info("Calculating Sales and Purchases")
transfers = OrderTransferItem.objects.filter(transfer__labels__name=OrderTransfer.VALID,
transfer__date__lte=self.end_date,
transfer__date__gte=self.start_date)
item_sales = {}
item_sales_qty = {}
item_purchases = {}
item_purchases_qty = {}
item_cogs = {}
item_profits = {}
acct_sales = {}
acct_sales_qty = {}
acct_purchases = {}
acct_purchases_qty = {}
acct_cogs = {}
acct_profits = {}
for t in transfers:
item_key = (t.order.info_id, t.order.info_type.id, t.date.month)
quantity = t.net_quantity
value = t.net_quantity * t.order.price
if t.order.order.customer == self.primary: # purchase
#item stuff
plus_equal(item_purchases_qty, item_key, quantity)
plus_equal(item_purchases, item_key, value)
#trade stuff
account_key = (t.order.order.supplier.id, t.date.month)
plus_equal(acct_purchases_qty, account_key, quantity)
plus_equal(acct_purchases, account_key, value)
elif t.order.order.supplier == self.primary: # sale
cogs = t.net_quantity * self.context.estimate(t.order.info)
profit = value - cogs
#item stuff
plus_equal(item_sales_qty, item_key, quantity)
plus_equal(item_sales, item_key, value)
plus_equal(item_cogs, item_key, cogs)
plus_equal(item_profits, item_key, profit)
#trade stuff
account_key = (t.order.order.customer.id, t.date.month)
plus_equal(acct_sales_qty, account_key, quantity)
plus_equal(acct_sales, account_key, value)
plus_equal(acct_cogs, account_key, cogs)
plus_equal(acct_profits, account_key, profit)
# Payment data
logger.info("Calculating Payments")
collections = Payment.objects.filter(supplier=self.primary,
date__lte=self.end_date,
date__gte=self.start_date,
labels__name=Payment.VALID)
acct_collections = {}
for c in collections:
key = (c.customer.id, c.date.month)
plus_equal(acct_collections, key, c.total)
disbursements = Payment.objects.filter(customer=self.primary,
date__lte=self.end_date,
date__gte=self.start_date,
labels__name=Payment.VALID)
acct_disbursements = {}
for d in disbursements:
key = (d.supplier.id, d.date.month)
plus_equal(acct_disbursements, key, d.total)
# Adjustment data
logger.info("Calculating Adjustments")
physicals = Physical.objects.filter(date__lte=self.end_date,
date__gte=self.start_date,
stock__location__owner=self.primary)
adjustments = AdjustmentItem.objects.filter(adjustment__date__lte=self.end_date,
adjustment__date__gte=self.start_date,
adjustment__location__owner=self.primary,
adjustment__labels__name=Adjustment.VALID)
item_adjustments = {}
for p in physicals:
key = (p.stock.product.id, Product.content_type().id, p.date.month)
plus_equal(item_adjustments, key, p.delta * self.context.estimate(p.stock.product))
for a in adjustments:
key = (a.product.id, Product.content_type().id, a.adjustment.date.month)
plus_equal(item_adjustments, key, a.delta * self.context.estimate(a.product))
logger.info("Updating Company Stats")
# write out adjustments first
year_adjustments = self.primary.account.year_data(YearData.ADJUSTMENTS, self.cutoff.year)
year_adjustments.reset()
for key, value in item_adjustments.iteritems():
_, _, month = key
year_adjustments.add(month, value)
year_adjustments.save()
def fill_company_year_data(label, data):
year_data = self.primary.account.year_data(label, self.cutoff.year)
year_data.reset()
for key, value in data.iteritems():
_, month = key
year_data.add(month, value)
year_data.save()
logger.info("Sales")
fill_company_year_data(YearData.SALES, acct_sales)
logger.info("Purchases")
fill_company_year_data(YearData.PURCHASES, acct_purchases)
logger.info("Cogs")
fill_company_year_data(YearData.COGS, acct_cogs)
logger.info("Profits")
fill_company_year_data(YearData.PROFITS, acct_profits)
logger.info("Disbursements")
fill_company_year_data(YearData.DISBURSEMENTS, acct_disbursements)
logger.info("Collections")
fill_company_year_data(YearData.COLLECTIONS, acct_collections)
logger.info("Updating Item Stats")
logger.info("Sales")
self.update_item_data(item_sales, YearData.SALES, self.start_date.year)
logger.info("Sales Qty")
self.update_item_data(item_sales_qty, YearData.SALES_QUANTITY, self.start_date.year)
logger.info("Purchases")
self.update_item_data(item_purchases, YearData.PURCHASES, self.start_date.year)
logger.info("Purchases Qty")
self.update_item_data(item_purchases_qty, YearData.PURCHASES_QUANTITY, self.start_date.year)
logger.info("Cogs")
self.update_item_data(item_cogs, YearData.COGS, self.start_date.year)
logger.info("Profits")
self.update_item_data(item_profits, YearData.PROFITS, self.start_date.year)
logger.info("Adjustments")
self.update_item_data(item_adjustments, YearData.ADJUSTMENTS, self.start_date.year)
logger.info("Updating Supplier Stats")
self.update_supplier_data(acct_purchases, YearData.PURCHASES, self.cutoff.year)
self.update_supplier_data(acct_purchases_qty, YearData.PURCHASES_QUANTITY, self.cutoff.year)
self.update_supplier_data(acct_disbursements, YearData.DISBURSEMENTS, self.cutoff.year)
logger.info("Updating Customer Stats")
self.update_customer_data(acct_sales, YearData.SALES, self.cutoff.year)
self.update_customer_data(acct_sales_qty, YearData.SALES_QUANTITY, self.cutoff.year)
self.update_customer_data(acct_cogs, YearData.COGS, self.cutoff.year)
self.update_customer_data(acct_profits, YearData.PROFITS, self.cutoff.year)
self.update_customer_data(acct_collections, YearData.COLLECTIONS, self.cutoff.year)
transaction.commit()
def __init__(self, primary_id, early_date, later_date):
self.primary_id = primary_id
self.early_date = early_date
self.later_date = later_date
self.cache[Product.content_type().id] = {}
self.cache[Service.content_type().id] = {}
def annotate_item(item, primary, partner, location):
if item.info_type == Product.content_type():
stock = item.info.stocks.get(location=location)
item.stock = stock.quantity
def update_item_account(product, user):
item_account, _ = ItemAccount.objects.get_or_create(item_type=Product.content_type(), item_id=product.id, owner=user.account.company)
item_account.assess()
