def debtor_tally(fl, company, post, user):
from apps.haul.views import xls_debtor_tally
if fl.name.endswith('.xls'):
wb = xls_to_xlsx(fl)
else:
wb = load_workbook(fl)
sheets = wb.worksheets
cnt = 0
for sheet in sheets:
rows = tuple(sheet.iter_rows())
with transaction.atomic():
for row in rows[5:]:
params = xls_debtor_tally(row)
if type(params.get('debit')) != str and type(
params.get('debit')) != str:
if 'new_party' in post:
party = Party.objects.create(
name=params.get('particulars'), company=company)
else:
party, party_created = Party.objects.get_or_create(
name=params.get('particulars'), company=company)
cnt += 1
party.customer_account.opening_cr = zero_for_none(
params.get('credit'))
party.customer_account.opening_dr = zero_for_none(
params.get('debit'))
party.customer_account.save()
party.save()
send_mail('Import complete',
str(cnt) + ' debtors imported.',
settings.DEFAULT_FROM_EMAIL, [user.email],
fail_silently=False)
def debtor_tally(fl, company, post, user):
from apps.haul.views import xls_debtor_tally
if fl.name.endswith('.xls'):
wb = xls_to_xlsx(fl)
else:
wb = load_workbook(fl)
sheets = wb.worksheets
cnt = 0
for sheet in sheets:
rows = tuple(sheet.iter_rows())
with transaction.atomic():
for row in rows[5:]:
params = xls_debtor_tally(row)
if type(params.get('debit')) != str and type(params.get('debit')) != str:
if 'new_party' in post:
party = Party.objects.create(name=params.get('particulars'), company=company)
else:
party, party_created = Party.objects.get_or_create(name=params.get('particulars'),
company=company)
cnt += 1
party.customer_account.opening_cr = zero_for_none(params.get('credit'))
party.customer_account.opening_dr = zero_for_none(params.get('debit'))
party.customer_account.save()
party.save()
send_mail('Import complete', str(cnt) + ' debtors imported.', settings.DEFAULT_FROM_EMAIL, [user.email], fail_silently=False)
def get_day_opening(self, before_date=None):
if not before_date:
before_date = datetime.date.today()
transactions = Transaction.objects.filter(account=self, journal_entry__date__lt=before_date).order_by(
'-journal_entry__id', '-journal_entry__date')[:1]
if len(transactions) > 0:
return zero_for_none(transactions[0].current_dr) - zero_for_none(transactions[0].current_cr)
return self.opening_dr - self.opening_cr
def get_day_opening(self, before_date=None):
if not before_date:
before_date = datetime.date.today()
transactions = Transaction.objects.filter(account=self, journal_entry__date__lt=before_date).order_by(
'-journal_entry__id', '-journal_entry__date')[:1]
if len(transactions) > 0:
return zero_for_none(transactions[0].current_dr) - zero_for_none(transactions[0].current_cr)
return self.opening_dr - self.opening_cr
def _transaction_delete(sender, instance, **kwargs):
transaction = instance
# cancel out existing dr_amount and cr_amount from account's current_dr and current_cr
if transaction.dr_amount:
transaction.account.current_dr -= transaction.dr_amount
if transaction.cr_amount:
transaction.account.current_cr -= transaction.cr_amount
alter(transaction.account, transaction.journal_entry.date, float(zero_for_none(transaction.dr_amount)) * -1,
float(zero_for_none(transaction.cr_amount)) * -1)
transaction.account.save()
def _transaction_delete(sender, instance, **kwargs):
transaction = instance
# cancel out existing dr_amount and cr_amount from account's current_dr and current_cr
if transaction.dr_amount:
transaction.account.current_dr = zero_for_none(transaction.account.current_dr)
transaction.account.current_dr -= transaction.dr_amount
if transaction.cr_amount:
transaction.account.current_cr = zero_for_none(transaction.account.current_cr)
transaction.account.current_cr -= transaction.cr_amount
alter(transaction.account, transaction.journal_entry.date, float(zero_for_none(transaction.dr_amount)) * -1,
float(zero_for_none(transaction.cr_amount)) * -1)
transaction.account.save()
def get_unpaid_hours(self):
total = 0
work_time_voucher_rows = WorkTimeVoucherRow.objects.filter(employee=self, paid=False)
for row in work_time_voucher_rows:
for work_day in row.work_days.all():
total += zero_for_none(work_day.work_minutes())
return round(float(total) / 60, 2)
def get_unpaid_ot_hours(self):
total = 0
attendance_vouchers = AttendanceVoucher.objects.filter(employee=self,
paid=False)
for voucher in attendance_vouchers:
total += zero_for_none(voucher.total_ot_hours)
return total
def get_unpaid_hours(self):
total = 0
work_time_voucher_rows = WorkTimeVoucherRow.objects.filter(
employee=self, paid=False)
for row in work_time_voucher_rows:
for work_day in row.work_days.all():
total += zero_for_none(work_day.work_minutes())
return round(float(total) / 60, 2)
def save_cash_receipt(request):
params = json.loads(request.body)
dct = {'rows': {}}
if params.get('voucher_no') == '':
params['voucher_no'] = None
object_values = {'party_id': params.get('party_id'), 'date': params.get('date'),
'voucher_no': params.get('voucher_no'),
'reference': params.get('reference'), 'company': request.company}
if params.get('id'):
obj = CashReceipt.objects.get(id=params.get('id'), company=request.company)
else:
obj = CashReceipt(company=request.company)
try:
obj = save_model(obj, object_values)
dct['id'] = obj.id
model = CashReceiptRow
cash_account = Account.objects.get(name='Cash', company=request.company)
if params.get('table_vm').get('rows'):
total = 0
for index, row in enumerate(params.get('table_vm').get('rows')):
if invalid(row, ['payment']):
continue
row['payment'] = zero_for_none(empty_to_none(row['payment']))
invoice = Sale.objects.get(voucher_no=row.get('voucher_no'), company=request.company)
invoice.pending_amount = row.get('pending_amount')
invoice.save()
values = {'receipt': row.get('payment'), 'cash_receipt': obj, 'invoice': invoice}
try:
old_value = model.objects.get(invoice_id=row.get('id'), cash_receipt_id=obj.id).receipt or 0
except CashReceiptRow.DoesNotExist:
old_value = 0
submodel, created = model.objects.get_or_create(invoice=invoice, cash_receipt=obj, defaults=values)
if created:
invoice.pending_amount -= float(row.get('payment'))
else:
submodel = save_model(submodel, values)
invoice.pending_amount -= float(row.get('payment')) - old_value
invoice.save()
dct['rows'][index] = submodel.id
total += float(row.get('payment'))
obj.amount = total
else:
obj.amount = params.get('amount')
set_ledger_transactions(obj, obj.date,
['dr', cash_account, obj.amount],
['cr', obj.party.account, obj.amount]
)
# obj.status = 'Unapproved'
obj.save()
except Exception as e:
dct = write_error(dct, e)
return JsonResponse(dct)
def set_transactions(model, date, *args):
args = [arg for arg in args if arg is not None]
journal_entry, created = JournalEntry.objects.get_or_create(
content_type=ContentType.objects.get_for_model(model), model_id=model.id,
defaults={
'date': date
})
for arg in args:
matches = journal_entry.transactions.filter(account=arg[1])
diff = 0
if not matches:
transaction = Transaction()
else:
transaction = matches[0]
diff = zero_for_none(transaction.cr_amount)
diff -= zero_for_none(transaction.dr_amount)
if arg[0] == 'dr':
transaction.dr_amount = float(arg[2])
transaction.cr_amount = None
diff += float(arg[2])
elif arg[0] == 'cr':
transaction.cr_amount = float(arg[2])
transaction.dr_amount = None
diff -= float(arg[2])
else:
raise Exception('Transactions can only be either "dr" or "cr".')
transaction.account = arg[1]
transaction.account.current_balance += diff
transaction.current_balance = transaction.account.current_balance
transaction.account.save()
try:
journal_entry.transactions.add(transaction, bulk=False)
except TypeError: # for Django <1.9
journal_entry.transactions.add(transaction)
alter(transaction.account, date, diff)
def set_transactions(model, date, *args):
args = [arg for arg in args if arg is not None]
journal_entry, created = JournalEntry.objects.get_or_create(
content_type=ContentType.objects.get_for_model(model), model_id=model.id,
defaults={
'date': date
})
for arg in args:
matches = journal_entry.transactions.filter(account=arg[1])
diff = 0
if not matches:
transaction = Transaction()
else:
transaction = matches[0]
diff = zero_for_none(transaction.cr_amount)
diff -= zero_for_none(transaction.dr_amount)
if arg[0] == 'dr':
transaction.dr_amount = float(arg[2])
transaction.cr_amount = None
diff += float(arg[2])
elif arg[0] == 'cr':
transaction.cr_amount = float(arg[2])
transaction.dr_amount = None
diff -= float(arg[2])
else:
raise Exception('Transactions can only be either "dr" or "cr".')
transaction.account = arg[1]
transaction.account.current_balance += diff
transaction.current_balance = transaction.account.current_balance
transaction.account.save()
try:
journal_entry.transactions.add(transaction, bulk=False)
except TypeError: # for Django <1.9
journal_entry.transactions.add(transaction)
alter(transaction.account, date, diff)
def alter(account, date, diff):
Transaction.objects.filter(journal_entry__date__gt=date, account=account).update(
current_balance=none_for_zero(zero_for_none(F('current_balance')) + zero_for_none(diff)))
def alter(account, date, diff):
Transaction.objects.filter(journal_entry__date__gt=date, account=account).update(
current_balance=none_for_zero(zero_for_none(F('current_balance')) + zero_for_none(diff)))
def balance(self):
return zero_for_none(self.customer_account.current_dr) - zero_for_none(
self.customer_account.current_cr) + zero_for_none(
self.supplier_account.current_cr) - zero_for_none(self.supplier_account.current_dr)
def set_transactions(submodel, date, *args):
if isinstance(date, unicode):
date = datetime.datetime.strptime(date, '%Y-%m-%d')
journal_entry, created = JournalEntry.objects.get_or_create(
content_type=ContentType.objects.get_for_model(submodel), object_id=submodel.id,
defaults={
'date': date
})
dr_total = 0
cr_total = 0
for arg in args:
# transaction = Transaction(account=arg[1], dr_amount=arg[2])
matches = journal_entry.transactions.filter(account=arg[1])
val = round(float(zero_for_none(arg[2])), 2)
if not matches:
transaction = Transaction()
transaction.account = arg[1]
if arg[0] == 'dr':
transaction.dr_amount = val
transaction.cr_amount = None
transaction.account.current_dr = none_for_zero(zero_for_none(transaction.account.current_dr) + val)
alter(arg[1], date, val, 0)
dr_total += val
if arg[0] == 'cr':
transaction.cr_amount = val
transaction.dr_amount = None
transaction.account.current_cr = none_for_zero(zero_for_none(transaction.account.current_cr) + val)
alter(arg[1], date, 0, float(arg[2]))
cr_total += val
transaction.current_dr = none_for_zero(
round(zero_for_none(transaction.account.get_dr_amount(date + datetime.timedelta(days=1)))
+ zero_for_none(transaction.dr_amount), 2)
)
transaction.current_cr = none_for_zero(
round(zero_for_none(transaction.account.get_cr_amount(date + datetime.timedelta(days=1)))
+ zero_for_none(transaction.cr_amount), 2)
)
else:
transaction = matches[0]
transaction.account = arg[1]
# cancel out existing dr_amount and cr_amount from current_dr and current_cr
# if transaction.dr_amount:
# transaction.current_dr -= transaction.dr_amount
# transaction.account.current_dr -= transaction.dr_amount
#
# if transaction.cr_amount:
# transaction.current_cr -= transaction.cr_amount
# transaction.account.current_cr -= transaction.cr_amount
# save new dr_amount and add it to current_dr/cr
if arg[0] == 'dr':
dr_difference = val - zero_for_none(transaction.dr_amount)
cr_difference = zero_for_none(transaction.cr_amount) * -1
alter(arg[1], transaction.journal_entry.date, dr_difference, cr_difference)
transaction.dr_amount = val
transaction.cr_amount = None
dr_total += transaction.dr_amount
else:
cr_difference = val - zero_for_none(transaction.cr_amount)
dr_difference = zero_for_none(transaction.dr_amount) * -1
alter(arg[1], transaction.journal_entry.date, dr_difference, cr_difference)
transaction.cr_amount = val
transaction.dr_amount = None
cr_total += transaction.cr_amount
transaction.current_dr = none_for_zero(zero_for_none(transaction.current_dr) + dr_difference)
transaction.current_cr = none_for_zero(zero_for_none(transaction.current_cr) + cr_difference)
transaction.account.current_dr = none_for_zero(
zero_for_none(transaction.account.current_dr) + dr_difference)
transaction.account.current_cr = none_for_zero(
zero_for_none(transaction.account.current_cr) + cr_difference)
# the following code lies outside if,else block, inside for loop
transaction.account.save()
try:
journal_entry.transactions.add(transaction, bulk=False)
except TypeError: # for Django <1.9
journal_entry.transactions.add(transaction)
if dr_total != cr_total:
mail_admins('Dr/Cr mismatch!',
'Dr/Cr mismatch from {0}, ID: {1}, Dr: {2}, Cr: {3}'.format(str(submodel), submodel.id, dr_total, cr_total))
raise RuntimeError('Dr/Cr mismatch!')
def get_balance(self):
return zero_for_none(self.current_dr) - zero_for_none(self.current_cr)
def get_unpaid_ot_hours(self):
total = 0
attendance_vouchers = AttendanceVoucher.objects.filter(employee=self, paid=False)
for voucher in attendance_vouchers:
total += zero_for_none(voucher.total_ot_hours)
return total
def get_balance(self):
return zero_for_none(self.current_dr) - zero_for_none(self.current_cr)
def set_transactions(submodel, date, *args):
if isinstance(date, unicode):
date = datetime.datetime.strptime(date, '%Y-%m-%d')
journal_entry, created = JournalEntry.objects.get_or_create(
content_type=ContentType.objects.get_for_model(submodel), object_id=submodel.id,
defaults={
'date': date
})
for arg in args:
# transaction = Transaction(account=arg[1], dr_amount=arg[2])
if arg[1] == 'cash':
arg[1] = Account.objects.get(name='Cash')
matches = journal_entry.transactions.filter(account=arg[1])
if not matches:
transaction = Transaction()
transaction.account = arg[1]
if arg[0] == 'dr':
transaction.dr_amount = float(zero_for_none(arg[2]))
transaction.cr_amount = None
transaction.account.current_dr = none_for_zero(
zero_for_none(transaction.account.current_dr) + transaction.dr_amount)
alter(arg[1], date, float(arg[2]), 0)
if arg[0] == 'cr':
transaction.cr_amount = float(zero_for_none(arg[2]))
transaction.dr_amount = None
transaction.account.current_cr = none_for_zero(
zero_for_none(transaction.account.current_cr) + transaction.cr_amount)
alter(arg[1], date, 0, float(arg[2]))
transaction.current_dr = none_for_zero(
zero_for_none(transaction.account.get_dr_amount(date + datetime.timedelta(days=1)))
+ zero_for_none(transaction.dr_amount))
transaction.current_cr = none_for_zero(
zero_for_none(transaction.account.get_cr_amount(date + datetime.timedelta(days=1)))
+ zero_for_none(transaction.cr_amount))
else:
transaction = matches[0]
transaction.account = arg[1]
# cancel out existing dr_amount and cr_amount from current_dr and current_cr
# if transaction.dr_amount:
# transaction.current_dr -= transaction.dr_amount
# transaction.account.current_dr -= transaction.dr_amount
#
# if transaction.cr_amount:
# transaction.current_cr -= transaction.cr_amount
# transaction.account.current_cr -= transaction.cr_amount
# save new dr_amount and add it to current_dr/cr
if arg[0] == 'dr':
dr_difference = float(arg[2]) - zero_for_none(transaction.dr_amount)
cr_difference = zero_for_none(transaction.cr_amount) * -1
alter(arg[1], transaction.journal_entry.date, dr_difference, cr_difference)
transaction.dr_amount = float(arg[2])
transaction.cr_amount = None
else:
cr_difference = float(arg[2]) - zero_for_none(transaction.cr_amount)
dr_difference = zero_for_none(transaction.dr_amount) * -1
alter(arg[1], transaction.journal_entry.date, dr_difference, cr_difference)
transaction.cr_amount = float(arg[2])
transaction.dr_amount = None
transaction.current_dr = none_for_zero(zero_for_none(transaction.current_dr) + dr_difference)
transaction.current_cr = none_for_zero(zero_for_none(transaction.current_cr) + cr_difference)
transaction.account.current_dr = none_for_zero(
zero_for_none(transaction.account.current_dr) + dr_difference)
transaction.account.current_cr = none_for_zero(
zero_for_none(transaction.account.current_cr) + cr_difference)
# the following code lies outside if,else block, inside for loop
transaction.account.save()
try:
journal_entry.transactions.add(transaction, bulk=False)
except TypeError: # for Django <1.9
journal_entry.transactions.add(transaction)
def alter(account, date, dr_difference, cr_difference):
Transaction.objects.filter(journal_entry__date__gt=date, account=account).update(
current_dr=none_for_zero(zero_for_none(F('current_dr')) + zero_for_none(dr_difference)),
current_cr=none_for_zero(zero_for_none(F('current_cr')) + zero_for_none(cr_difference)))
def alter(account, date, dr_difference, cr_difference):
Transaction.objects.filter(journal_entry__date__gt=date, account=account).update(
current_dr=none_for_zero(zero_for_none(F('current_dr')) + zero_for_none(dr_difference)),
current_cr=none_for_zero(zero_for_none(F('current_cr')) + zero_for_none(cr_difference)))