def get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = epaas.get_company_currency(self.company)
precision = get_field_precision(dataent.get_meta("Exchange Rate Revaluation Account")
.get_field("new_balance_in_base_currency"), company_currency)
account_details = self.get_accounts_from_gle()
for d in account_details:
current_exchange_rate = d.balance / d.balance_in_account_currency \
if d.balance_in_account_currency else 0
new_exchange_rate = get_exchange_rate(d.account_currency, company_currency, self.posting_date)
new_balance_in_base_currency = flt(d.balance_in_account_currency * new_exchange_rate)
gain_loss = flt(new_balance_in_base_currency, precision) - flt(d.balance, precision)
if gain_loss:
accounts.append({
"account": d.account,
"party_type": d.party_type,
"party": d.party,
"account_currency": d.account_currency,
"balance_in_base_currency": d.balance,
"balance_in_account_currency": d.balance_in_account_currency,
"current_exchange_rate": current_exchange_rate,
"new_exchange_rate": new_exchange_rate,
"new_balance_in_base_currency": new_balance_in_base_currency
})
if not accounts:
self.throw_invalid_response_message(account_details)
return accounts
def validate(self):
self.status = self.get_status()
self.validate_dates()
self.check_existing()
if not self.salary_slip_based_on_timesheet:
self.get_date_details()
if not (len(self.get("earnings")) or len(self.get("deductions"))):
# get details from salary structure
self.get_emp_and_leave_details()
else:
self.get_leave_details(lwp=self.leave_without_pay)
self.calculate_net_pay()
company_currency = epaas.get_company_currency(self.company)
self.total_in_words = money_in_words(self.rounded_total,
company_currency)
if dataent.db.get_single_value("HR Settings",
"max_working_hours_against_timesheet"):
max_working_hours = dataent.db.get_single_value(
"HR Settings", "max_working_hours_against_timesheet")
if self.salary_slip_based_on_timesheet and (
self.total_working_hours > int(max_working_hours)):
dataent.msgprint(_(
"Total working hours should not be greater than max working hours {0}"
).format(max_working_hours),
alert=True)
def get_accounts_from_gle(self):
company_currency = epaas.get_company_currency(self.company)
accounts = dataent.db.sql_list("""
select name
from tabAccount
where is_group = 0
and report_type = 'Balance Sheet'
and root_type in ('Asset', 'Liability', 'Equity')
and account_type != 'Stock'
and company=%s
and account_currency != %s
order by name""",(self.company, company_currency))
account_details = []
if accounts:
account_details = dataent.db.sql("""
select
account, party_type, party, account_currency,
sum(debit_in_account_currency) - sum(credit_in_account_currency) as balance_in_account_currency,
sum(debit) - sum(credit) as balance
from `tabGL Entry`
where account in (%s)
group by account, party_type, party
having sum(debit) != sum(credit)
order by account
""" % ', '.join(['%s']*len(accounts)), tuple(accounts), as_dict=1)
return account_details
def get_currency(filters):
"""
Returns a dictionary containing currency information. The keys of the dict are
- company: The company for which we are fetching currency information. if no
company is specified, it will fallback to the default company.
- company currency: The functional currency of the said company.
- presentation currency: The presentation currency to use. Only currencies that
have been used for transactions will be allowed.
- report date: The report date.
:param filters: Report filters
:type filters: dict
:return: str - Currency
"""
company = get_appropriate_company(filters)
company_currency = get_company_currency(company)
presentation_currency = filters['presentation_currency'] if filters.get(
'presentation_currency') else company_currency
report_date = filters.get('to_date')
if not report_date:
fiscal_year_to_date = get_from_and_to_date(
filters.get('to_fiscal_year'))["to_date"]
report_date = formatdate(get_datetime_str(fiscal_year_to_date),
"dd-MM-yyyy")
currency_map = dict(company=company,
company_currency=company_currency,
presentation_currency=presentation_currency,
report_date=report_date)
return currency_map
def get_data(filters):
accounts = dataent.db.sql("""select name, account_number, parent_account, account_name, root_type, report_type, lft, rgt
from `tabAccount` where company=%s order by lft""", filters.company, as_dict=True)
company_currency = epaas.get_company_currency(filters.company)
if not accounts:
return None
accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)
min_lft, max_rgt = dataent.db.sql("""select min(lft), max(rgt) from `tabAccount`
where company=%s""", (filters.company,))[0]
gl_entries_by_account = {}
opening_balances = get_opening_balances(filters)
set_gl_entries_by_account(filters.company, filters.from_date,
filters.to_date, min_lft, max_rgt, filters, gl_entries_by_account, ignore_closing_entries=not flt(filters.with_period_closing_entry))
total_row = calculate_values(accounts, gl_entries_by_account, opening_balances, filters, company_currency)
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row, parent_children_map, company_currency)
data = filter_out_zero_value_rows(data, parent_children_map,
show_zero_values=filters.get("show_zero_values"))
return data
def get_price_list_currency_and_exchange_rate(args):
if not args.price_list:
return {}
if args.doctype in ['Quotation', 'Sales Order', 'Delivery Note', 'Sales Invoice']:
args.update({"exchange_rate": "for_selling"})
elif args.doctype in ['Purchase Order', 'Purchase Receipt', 'Purchase Invoice']:
args.update({"exchange_rate": "for_buying"})
price_list_currency = get_price_list_currency(args.price_list)
price_list_uom_dependant = get_price_list_uom_dependant(args.price_list)
plc_conversion_rate = args.plc_conversion_rate
company_currency = get_company_currency(args.company)
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
# cksgb 19/09/2016: added args.transaction_date as posting_date argument for get_exchange_rate
plc_conversion_rate = get_exchange_rate(price_list_currency, company_currency,
args.transaction_date, args.exchange_rate) or plc_conversion_rate
return dataent._dict({
"price_list_currency": price_list_currency,
"price_list_uom_dependant": price_list_uom_dependant,
"plc_conversion_rate": plc_conversion_rate
})
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
entries = get_entries(filters)
item_details = get_item_details()
data = []
company_currency = get_company_currency(filters.get("company"))
for d in entries:
if d.stock_qty > 0 or filters.get('show_return_entries', 0):
data.append([
d.name, d.customer, d.territory, d.warehouse, d.posting_date,
d.item_code,
item_details.get(d.item_code, {}).get("item_group"),
item_details.get(d.item_code, {}).get("brand"), d.stock_qty,
d.base_net_amount, d.sales_person, d.allocated_percentage,
d.contribution_amt, company_currency
])
if data:
total_row = [""] * len(data[0])
data.append(total_row)
return columns, data
def get_incoming_rate(args, raise_error_if_no_rate=True):
"""Get Incoming Rate based on valuation method"""
from epaas.stock.stock_ledger import get_previous_sle, get_valuation_rate
if isinstance(args, string_types):
args = json.loads(args)
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if previous_sle:
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
if not in_rate:
voucher_no = args.get('voucher_no') or args.get('name')
in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'),
args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'),
currency=epaas.get_company_currency(args.get('company')), company=args.get('company'),
raise_error_if_no_rate=True)
return in_rate
def _execute(filters=None):
if not filters: filters = {}
columns = get_columns()
company_currency = epaas.get_company_currency(filters.company)
item_list = get_items(filters)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
data = []
for d in item_list:
row = [d.gst_hsn_code, d.description, d.stock_uom, d.stock_qty]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
total_tax += flt(item_tax.get("tax_amount"))
row += [d.base_net_amount + total_tax]
row += [d.base_net_amount]
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_amount", 0)]
data.append(row)
if data:
data = get_merged_data(columns, data) # merge same hsn code data
return columns, data
def validate_conversion_rate(self):
# validate conversion rate
company_currency = epaas.get_company_currency(self.doc.company)
if not self.doc.currency or self.doc.currency == company_currency:
self.doc.currency = company_currency
self.doc.conversion_rate = 1.0
else:
validate_conversion_rate(
self.doc.currency, self.doc.conversion_rate,
self.doc.meta.get_label("conversion_rate"), self.doc.company)
self.doc.conversion_rate = flt(self.doc.conversion_rate)
def test_purchase_order_creation(self):
bo = make_blanket_order(blanket_order_type="Purchasing")
po = make_purchase_order(bo.name)
po.currency = get_company_currency(po.company)
po.schedule_date = today()
po.items[0].qty = 10
po.submit()
self.assertEqual(po.doctype, "Purchase Order")
self.assertEqual(len(po.get("items")), len(bo.get("items")))
# check the rate, quantity and updation for the ordered quantity
self.assertEqual(po.items[0].rate, po.items[0].rate)
bo = dataent.get_doc("Blanket Order", bo.name)
self.assertEqual(po.items[0].qty, bo.items[0].ordered_qty)
# test the quantity
po1 = make_sales_order(bo.name)
po1.currency = get_company_currency(po1.company)
self.assertEqual(po1.items[0].qty,
(bo.items[0].qty - bo.items[0].ordered_qty))
def test_sales_order_creation(self):
bo = make_blanket_order(blanket_order_type="Selling")
so = make_sales_order(bo.name)
so.currency = get_company_currency(so.company)
so.delivery_date = today()
so.items[0].qty = 10
so.submit()
self.assertEqual(so.doctype, "Sales Order")
self.assertEqual(len(so.get("items")), len(bo.get("items")))
# check the rate, quantity and updation for the ordered quantity
self.assertEqual(so.items[0].rate, bo.items[0].rate)
bo = dataent.get_doc("Blanket Order", bo.name)
self.assertEqual(so.items[0].qty, bo.items[0].ordered_qty)
# test the quantity
so1 = make_sales_order(bo.name)
so1.currency = get_company_currency(so1.company)
self.assertEqual(so1.items[0].qty,
(bo.items[0].qty - bo.items[0].ordered_qty))
def validate_currency(self):
company_currency = epaas.get_company_currency(self.company)
account_currency = get_account_currency(self.account)
if not self.account_currency:
self.account_currency = company_currency
if account_currency != self.account_currency:
dataent.throw(_("{0} {1}: Accounting Entry for {2} can only be made in currency: {3}")
.format(self.voucher_type, self.voucher_no, self.account,
(account_currency or company_currency)), InvalidAccountCurrency)
if self.party_type and self.party:
validate_party_gle_currency(self.party_type, self.party, self.company, self.account_currency)
def get_dashboard_info(self):
info = {
"total_paid": 0,
"total_unpaid": 0,
"currency": epaas.get_company_currency(self.company)
}
fees_amount = dataent.db.sql(
"""select sum(grand_total), sum(outstanding_amount) from tabFees
where fee_schedule=%s and docstatus=1""", (self.name))
if fees_amount:
info["total_paid"] = flt(fees_amount[0][0]) - flt(
fees_amount[0][1])
info["total_unpaid"] = flt(fees_amount[0][1])
return info
def get_serialized_values(self, sle):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
serial_no = cstr(sle.serial_no).split("\n")
if incoming_rate < 0:
# wrong incoming rate
incoming_rate = self.valuation_rate
stock_value_change = 0
if incoming_rate:
stock_value_change = actual_qty * incoming_rate
elif actual_qty < 0:
# In case of delivery/stock issue, get average purchase rate
# of serial nos of current entry
stock_value_change = -1 * flt(
dataent.db.sql(
"""select sum(purchase_rate)
from `tabSerial No` where name in (%s)""" %
(", ".join(["%s"] * len(serial_no))),
tuple(serial_no))[0][0])
new_stock_qty = self.qty_after_transaction + actual_qty
if new_stock_qty > 0:
new_stock_value = (self.qty_after_transaction *
self.valuation_rate) + stock_value_change
if new_stock_value >= 0:
# calculate new valuation rate only if stock value is positive
# else it remains the same as that of previous entry
self.valuation_rate = new_stock_value / new_stock_qty
if not self.valuation_rate and sle.voucher_detail_no:
allow_zero_rate = self.check_if_allow_zero_valuation_rate(
sle.voucher_type, sle.voucher_detail_no)
if not allow_zero_rate:
self.valuation_rate = get_valuation_rate(
sle.item_code,
sle.warehouse,
sle.voucher_type,
sle.voucher_no,
self.allow_zero_rate,
currency=epaas.get_company_currency(sle.company))
def validate_quantity(doc, args, ref, valid_items, already_returned_items):
fields = ['stock_qty']
if doc.doctype in ['Purchase Receipt', 'Purchase Invoice']:
fields.extend(['received_qty', 'rejected_qty'])
already_returned_data = already_returned_items.get(args.item_code) or {}
company_currency = epaas.get_company_currency(doc.company)
stock_qty_precision = get_field_precision(
dataent.get_meta(doc.doctype + " Item").get_field("stock_qty"),
company_currency)
for column in fields:
returned_qty = flt(already_returned_data.get(
column, 0)) if len(already_returned_data) > 0 else 0
if column == 'stock_qty':
reference_qty = ref.get(column)
current_stock_qty = args.get(column)
else:
reference_qty = ref.get(column) * ref.get("conversion_factor", 1.0)
current_stock_qty = args.get(column) * args.get(
"conversion_factor", 1.0)
max_returnable_qty = flt(reference_qty,
stock_qty_precision) - returned_qty
label = column.replace('_', ' ').title()
if reference_qty:
if flt(args.get(column)) > 0:
dataent.throw(
_("{0} must be negative in return document").format(label))
elif returned_qty >= reference_qty and args.get(column):
dataent.throw(
_("Item {0} has already been returned").format(
args.item_code), StockOverReturnError)
elif abs(flt(current_stock_qty,
stock_qty_precision)) > max_returnable_qty:
dataent.throw(
_("Row # {0}: Cannot return more than {1} for Item {2}"
).format(args.idx, max_returnable_qty, args.item_code),
StockOverReturnError)
def get_moving_average_values(self, sle):
actual_qty = flt(sle.actual_qty)
new_stock_qty = flt(self.qty_after_transaction) + actual_qty
if new_stock_qty >= 0:
if actual_qty > 0:
if flt(self.qty_after_transaction) <= 0:
self.valuation_rate = sle.incoming_rate
else:
new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \
(actual_qty * sle.incoming_rate)
self.valuation_rate = new_stock_value / new_stock_qty
elif sle.outgoing_rate:
if new_stock_qty:
new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \
(actual_qty * sle.outgoing_rate)
self.valuation_rate = new_stock_value / new_stock_qty
else:
self.valuation_rate = sle.outgoing_rate
else:
if flt(self.qty_after_transaction) >= 0 and sle.outgoing_rate:
self.valuation_rate = sle.outgoing_rate
if not self.valuation_rate and actual_qty > 0:
self.valuation_rate = sle.incoming_rate
# Get valuation rate from previous SLE or Item master, if item does not have the
# allow zero valuration rate flag set
if not self.valuation_rate and sle.voucher_detail_no:
allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate(
sle.voucher_type, sle.voucher_detail_no)
if not allow_zero_valuation_rate:
self.valuation_rate = get_valuation_rate(
sle.item_code,
sle.warehouse,
sle.voucher_type,
sle.voucher_no,
self.allow_zero_rate,
currency=epaas.get_company_currency(sle.company))
def set_missing_accounts_and_fields(self):
if not self.company:
self.company = dataent.defaults.get_defaults().company
if not self.currency:
self.currency = epaas.get_company_currency(self.company)
if not (self.receivable_account and self.income_account
and self.cost_center):
accounts_details = dataent.get_all(
"Company",
fields=[
"default_receivable_account", "default_income_account",
"cost_center"
],
filters={"name": self.company})[0]
if not self.receivable_account:
self.receivable_account = accounts_details.default_receivable_account
if not self.income_account:
self.income_account = accounts_details.default_income_account
if not self.cost_center:
self.cost_center = accounts_details.cost_center
if not self.student_email:
self.student_email = self.get_student_emails()
def validate_overlapping_shipping_rule_conditions(self):
def overlap_exists_between(num_range1, num_range2):
"""
num_range1 and num_range2 are two ranges
ranges are represented as a tuple e.g. range 100 to 300 is represented as (100, 300)
if condition num_range1 = 100 to 300
then condition num_range2 can only be like 50 to 99 or 301 to 400
hence, non-overlapping condition = (x1 <= x2 < y1 <= y2) or (y1 <= y2 < x1 <= x2)
"""
(x1, x2), (y1, y2) = num_range1, num_range2
separate = (x1 <= x2 <= y1 <= y2) or (y1 <= y2 <= x1 <= x2)
return (not separate)
overlaps = []
for i in range(0, len(self.conditions)):
for j in range(i + 1, len(self.conditions)):
d1, d2 = self.conditions[i], self.conditions[j]
if d1.as_dict() != d2.as_dict():
# in our case, to_value can be zero, hence pass the from_value if so
range_a = (d1.from_value, d1.to_value or d1.from_value)
range_b = (d2.from_value, d2.to_value or d2.from_value)
if overlap_exists_between(range_a, range_b):
overlaps.append([d1, d2])
if overlaps:
company_currency = epaas.get_company_currency(self.company)
msgprint(_("Overlapping conditions found between:"))
messages = []
for d1, d2 in overlaps:
messages.append(
"%s-%s = %s " %
(d1.from_value, d1.to_value,
fmt_money(d1.shipping_amount, currency=company_currency))
+ _("and") + " %s-%s = %s" %
(d2.from_value, d2.to_value,
fmt_money(d2.shipping_amount, currency=company_currency)))
msgprint("\n".join(messages),
raise_exception=OverlappingConditionError)
def update_against_account(voucher_type, voucher_no):
entries = dataent.db.get_all("GL Entry",
filters={"voucher_type": voucher_type, "voucher_no": voucher_no},
fields=["name", "party", "against", "debit", "credit", "account", "company"])
if not entries:
return
company_currency = epaas.get_company_currency(entries[0].company)
precision = get_field_precision(dataent.get_meta("GL Entry")
.get_field("debit"), company_currency)
accounts_debited, accounts_credited = [], []
for d in entries:
if flt(d.debit, precision) > 0: accounts_debited.append(d.party or d.account)
if flt(d.credit, precision) > 0: accounts_credited.append(d.party or d.account)
for d in entries:
if flt(d.debit, precision) > 0:
new_against = ", ".join(list(set(accounts_credited)))
if flt(d.credit, precision) > 0:
new_against = ", ".join(list(set(accounts_debited)))
if d.against != new_against:
dataent.db.set_value("GL Entry", d.name, "against", new_against)
def get_account_details(account, company, posting_date, party_type=None, party=None):
account_currency, account_type = dataent.db.get_value("Account", account,
["account_currency", "account_type"])
if account_type in ["Receivable", "Payable"] and not (party_type and party):
dataent.throw(_("Party Type and Party is mandatory for {0} account").format(account_type))
account_details = {}
company_currency = epaas.get_company_currency(company)
balance = get_balance_on(account, party_type=party_type, party=party, in_account_currency=False)
if balance:
balance_in_account_currency = get_balance_on(account, party_type=party_type, party=party)
current_exchange_rate = balance / balance_in_account_currency if balance_in_account_currency else 0
new_exchange_rate = get_exchange_rate(account_currency, company_currency, posting_date)
new_balance_in_base_currency = balance_in_account_currency * new_exchange_rate
account_details = {
"account_currency": account_currency,
"balance_in_base_currency": balance,
"balance_in_account_currency": balance_in_account_currency,
"current_exchange_rate": current_exchange_rate,
"new_exchange_rate": new_exchange_rate,
"new_balance_in_base_currency": new_balance_in_base_currency
}
return account_details
def check_conversion_rate(self):
default_currency = epaas.get_company_currency(self.company)
if not default_currency:
throw(_('Please enter default currency in Company Master'))
if (self.currency == default_currency and flt(self.conversion_rate) != 1.00) or not self.conversion_rate or (self.currency != default_currency and flt(self.conversion_rate) == 1.00):
throw(_("Conversion rate cannot be 0 or 1"))
def get_columns(filters):
if filters.get("presentation_currency"):
currency = filters["presentation_currency"]
else:
if filters.get("company"):
currency = get_company_currency(filters["company"])
else:
company = get_default_company()
currency = get_company_currency(company)
columns = [{
"label": _("Posting Date"),
"fieldname": "posting_date",
"fieldtype": "Date",
"width": 90
}, {
"label": _("Account"),
"fieldname": "account",
"fieldtype": "Link",
"options": "Account",
"width": 180
}, {
"label": _("Debit ({0})".format(currency)),
"fieldname": "debit",
"fieldtype": "Float",
"width": 100
}, {
"label": _("Credit ({0})".format(currency)),
"fieldname": "credit",
"fieldtype": "Float",
"width": 100
}, {
"label": _("Balance ({0})".format(currency)),
"fieldname": "balance",
"fieldtype": "Float",
"width": 130
}]
columns.extend([{
"label": _("Voucher Type"),
"fieldname": "voucher_type",
"width": 120
}, {
"label": _("Voucher No"),
"fieldname": "voucher_no",
"fieldtype": "Dynamic Link",
"options": "voucher_type",
"width": 180
}, {
"label": _("Against Account"),
"fieldname": "against",
"width": 120
}, {
"label": _("Party Type"),
"fieldname": "party_type",
"width": 100
}, {
"label": _("Party"),
"fieldname": "party",
"width": 100
}, {
"label": _("Project"),
"options": "Project",
"fieldname": "project",
"width": 100
}, {
"label": _("Cost Center"),
"options": "Cost Center",
"fieldname": "cost_center",
"width": 100
}, {
"label": _("Against Voucher Type"),
"fieldname": "against_voucher_type",
"width": 100
}, {
"label": _("Against Voucher"),
"fieldname": "against_voucher",
"fieldtype": "Dynamic Link",
"options": "against_voucher_type",
"width": 100
}, {
"label": _("Supplier Invoice No"),
"fieldname": "bill_no",
"fieldtype": "Data",
"width": 100
}, {
"label": _("Remarks"),
"fieldname": "remarks",
"width": 400
}])
return columns
def company_currency(self): return epaas.get_company_currency(self.company)
def check_matching_amount(bank_account, company, transaction):
payments = []
amount = transaction.credit if transaction.credit > 0 else transaction.debit
payment_type = "Receive" if transaction.credit > 0 else "Pay"
account_from_to = "paid_to" if transaction.credit > 0 else "paid_from"
currency_field = "paid_to_account_currency as currency" if transaction.credit > 0 else "paid_from_account_currency as currency"
payment_entries = dataent.get_all(
"Payment Entry",
fields=[
"'Payment Entry' as doctype", "name", "paid_amount",
"payment_type", "reference_no", "reference_date", "party",
"party_type", "posting_date", "{0}".format(currency_field)
],
filters=[["paid_amount", "like", "{0}%".format(amount)],
["docstatus", "=", "1"],
["payment_type", "=", [payment_type, "Internal Transfer"]],
["ifnull(clearance_date, '')", "=", ""],
[
"{0}".format(account_from_to), "=",
"{0}".format(bank_account)
]])
if transaction.credit > 0:
journal_entries = dataent.db.sql("""
SELECT
'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
je.pay_to_recd_from as party, je.cheque_date as reference_date, jea.debit_in_account_currency as paid_amount
FROM
`tabJournal Entry Account` as jea
JOIN
`tabJournal Entry` as je
ON
jea.parent = je.name
WHERE
(je.clearance_date is null or je.clearance_date='0000-00-00')
AND
jea.account = %s
AND
jea.debit_in_account_currency like %s
AND
je.docstatus = 1
""", (bank_account, amount),
as_dict=True)
else:
journal_entries = dataent.db.sql("""
SELECT
'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
jea.account_currency as currency, je.pay_to_recd_from as party, je.cheque_date as reference_date,
jea.credit_in_account_currency as paid_amount
FROM
`tabJournal Entry Account` as jea
JOIN
`tabJournal Entry` as je
ON
jea.parent = je.name
WHERE
(je.clearance_date is null or je.clearance_date='0000-00-00')
AND
jea.account = %(bank_account)s
AND
jea.credit_in_account_currency like %(txt)s
AND
je.docstatus = 1
""", {
'bank_account': bank_account,
'txt': '%%%s%%' % amount
},
as_dict=True)
dataent.errprint(journal_entries)
if transaction.credit > 0:
sales_invoices = dataent.db.sql("""
SELECT
'Sales Invoice' as doctype, si.name, si.customer as party,
si.posting_date, sip.amount as paid_amount
FROM
`tabSales Invoice Payment` as sip
JOIN
`tabSales Invoice` as si
ON
sip.parent = si.name
WHERE
(sip.clearance_date is null or sip.clearance_date='0000-00-00')
AND
sip.account = %s
AND
sip.amount like %s
AND
si.docstatus = 1
""", (bank_account, amount),
as_dict=True)
else:
sales_invoices = []
if transaction.debit > 0:
purchase_invoices = dataent.get_all(
"Purchase Invoice",
fields=[
"'Purchase Invoice' as doctype", "name", "paid_amount",
"supplier as party", "posting_date", "currency"
],
filters=[["paid_amount", "like", "{0}%".format(amount)],
["docstatus", "=", "1"], ["is_paid", "=", "1"],
["ifnull(clearance_date, '')", "=", ""],
["cash_bank_account", "=", "{0}".format(bank_account)]])
mode_of_payments = [
x["parent"] for x in dataent.db.get_list(
"Mode of Payment Account",
filters={"default_account": bank_account},
fields=["parent"])
]
company_currency = get_company_currency(company)
expense_claims = dataent.get_all(
"Expense Claim",
fields=[
"'Expense Claim' as doctype", "name",
"total_sanctioned_amount as paid_amount", "employee as party",
"posting_date", "'{0}' as currency".format(company_currency)
],
filters=[[
"total_sanctioned_amount", "like", "{0}%".format(amount)
], ["docstatus", "=", "1"], ["is_paid", "=", "1"],
["ifnull(clearance_date, '')", "=", ""],
[
"mode_of_payment", "in",
"{0}".format(tuple(mode_of_payments))
]])
else:
purchase_invoices = expense_claims = []
for data in [
payment_entries, journal_entries, sales_invoices,
purchase_invoices, expense_claims
]:
if data:
payments.extend(data)
return payments
def get_matching_descriptions_data(company, transaction):
if not transaction.description:
return []
bank_transactions = dataent.db.sql("""
SELECT
bt.name, bt.description, bt.date, btp.payment_document, btp.payment_entry
FROM
`tabBank Transaction` as bt
LEFT JOIN
`tabBank Transaction Payments` as btp
ON
bt.name = btp.parent
WHERE
bt.allocated_amount > 0
AND
bt.docstatus = 1
""",
as_dict=True)
selection = []
for bank_transaction in bank_transactions:
if bank_transaction.description:
seq = difflib.SequenceMatcher(lambda x: x == " ",
transaction.description,
bank_transaction.description)
if seq.ratio() > 0.6:
bank_transaction["ratio"] = seq.ratio()
selection.append(bank_transaction)
document_types = set([x["payment_document"] for x in selection])
links = {}
for document_type in document_types:
links[document_type] = [
x["payment_entry"] for x in selection
if x["payment_document"] == document_type
]
data = []
company_currency = get_company_currency(company)
for key, value in iteritems(links):
if key == "Payment Entry":
data.extend(
dataent.get_all("Payment Entry",
filters=[["name", "in", value]],
fields=[
"'Payment Entry' as doctype",
"posting_date", "party", "reference_no",
"reference_date", "paid_amount",
"paid_to_account_currency as currency",
"clearance_date"
]))
if key == "Journal Entry":
journal_entries = dataent.get_all(
"Journal Entry",
filters=[["name", "in", value]],
fields=[
"name", "'Journal Entry' as doctype", "posting_date",
"pay_to_recd_from as party", "cheque_no as reference_no",
"cheque_date as reference_date",
"total_credit as paid_amount", "clearance_date"
])
for journal_entry in journal_entries:
journal_entry_accounts = dataent.get_all(
"Journal Entry Account",
filters={
"parenttype": journal_entry["doctype"],
"parent": journal_entry["name"]
},
fields=["account_currency"])
journal_entry["currency"] = journal_entry_accounts[0][
"account_currency"] if journal_entry_accounts else company_currency
data.extend(journal_entries)
if key == "Sales Invoice":
data.extend(
dataent.get_all("Sales Invoice",
filters=[["name", "in", value]],
fields=[
"'Sales Invoice' as doctype",
"posting_date", "customer_name as party",
"paid_amount", "currency"
]))
if key == "Purchase Invoice":
data.extend(
dataent.get_all("Purchase Invoice",
filters=[["name", "in", value]],
fields=[
"'Purchase Invoice' as doctype",
"posting_date", "supplier_name as party",
"paid_amount", "currency"
]))
if key == "Expense Claim":
expense_claims = dataent.get_all(
"Expense Claim",
filters=[["name", "in", value]],
fields=[
"'Expense Claim' as doctype", "posting_date",
"employee_name as party",
"total_amount_reimbursed as paid_amount"
])
data.extend([
dict(x, **{"currency": company_currency})
for x in expense_claims
])
return data
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
filters.update({
"from_date":
filters.get("date_range") and filters.get("date_range")[0],
"to_date":
filters.get("date_range") and filters.get("date_range")[1]
})
columns = get_columns(additional_table_columns)
company_currency = epaas.get_company_currency(filters.get('company'))
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [
d.item_code, d.item_name, d.item_group, d.description, d.parent,
d.posting_date, d.customer, d.customer_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to,
", ".join(mode_of_payments.get(d.parent, [])), d.territory,
d.project, d.company, d.sales_order, delivery_note,
d.income_account, d.cost_center, d.stock_qty, d.stock_uom
]
if d.stock_uom != d.uom and d.stock_qty:
row += [(d.base_net_rate * d.qty) / d.stock_qty, d.base_net_amount]
else:
row += [d.base_net_rate, d.base_net_amount]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def _get_party_details(party=None,
account=None,
party_type="Customer",
company=None,
posting_date=None,
bill_date=None,
price_list=None,
currency=None,
doctype=None,
ignore_permissions=False,
fetch_payment_terms_template=True,
party_address=None,
shipping_address=None,
pos_profile=None):
out = dataent._dict(
set_account_and_due_date(party, account, party_type, company,
posting_date, bill_date, doctype))
party = out[party_type.lower()]
if not ignore_permissions and not dataent.has_permission(
party_type, "read", party):
dataent.throw(
_("Not permitted for {0}").format(party), dataent.PermissionError)
party = dataent.get_doc(party_type, party)
currency = party.default_currency if party.get(
"default_currency") else get_company_currency(company)
out["taxes_and_charges"] = set_taxes(party.name, party_type, posting_date,
company, out.customer_group,
out.supplier_group)
out["payment_terms_template"] = get_pyt_term_template(
party.name, party_type, company)
set_address_details(out, party, party_type, doctype, company,
party_address, shipping_address)
set_contact_details(out, party, party_type)
set_other_values(out, party, party_type)
set_price_list(out, party, party_type, price_list, pos_profile)
out["taxes_and_charges"] = set_taxes(party.name, party_type, posting_date,
company, out.customer_group,
out.supplier_type)
if fetch_payment_terms_template:
out["payment_terms_template"] = get_pyt_term_template(
party.name, party_type, company)
if not out.get("currency"):
out["currency"] = currency
# sales team
if party_type == "Customer":
out["sales_team"] = [{
"sales_person":
d.sales_person,
"allocated_percentage":
d.allocated_percentage or None
} for d in party.get("sales_team")]
# supplier tax withholding category
if party_type == "Supplier" and party:
out["supplier_tds"] = dataent.get_value(party_type, party.name,
"tax_withholding_category")
return out
def get_fifo_values(self, sle):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
outgoing_rate = flt(sle.outgoing_rate)
if actual_qty > 0:
if not self.stock_queue:
self.stock_queue.append([0, 0])
# last row has the same rate, just updated the qty
if self.stock_queue[-1][1] == incoming_rate:
self.stock_queue[-1][0] += actual_qty
else:
if self.stock_queue[-1][0] > 0:
self.stock_queue.append([actual_qty, incoming_rate])
else:
qty = self.stock_queue[-1][0] + actual_qty
self.stock_queue[-1] = [qty, incoming_rate]
else:
qty_to_pop = abs(actual_qty)
while qty_to_pop:
if not self.stock_queue:
# Get valuation rate from last sle if exists or from valuation rate field in item master
allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate(
sle.voucher_type, sle.voucher_detail_no)
if not allow_zero_valuation_rate:
_rate = get_valuation_rate(
sle.item_code,
sle.warehouse,
sle.voucher_type,
sle.voucher_no,
self.allow_zero_rate,
currency=epaas.get_company_currency(sle.company))
else:
_rate = 0
self.stock_queue.append([0, _rate])
index = None
if outgoing_rate > 0:
# Find the entry where rate matched with outgoing rate
for i, v in enumerate(self.stock_queue):
if v[1] == outgoing_rate:
index = i
break
# If no entry found with outgoing rate, collapse stack
if index == None:
new_stock_value = sum(
(d[0] * d[1] for d in self.stock_queue
)) - qty_to_pop * outgoing_rate
new_stock_qty = sum(
(d[0] for d in self.stock_queue)) - qty_to_pop
self.stock_queue = [[
new_stock_qty, new_stock_value / new_stock_qty
if new_stock_qty > 0 else outgoing_rate
]]
break
else:
index = 0
# select first batch or the batch with same rate
batch = self.stock_queue[index]
if qty_to_pop >= batch[0]:
# consume current batch
qty_to_pop = qty_to_pop - batch[0]
self.stock_queue.pop(index)
if not self.stock_queue and qty_to_pop:
# stock finished, qty still remains to be withdrawn
# negative stock, keep in as a negative batch
self.stock_queue.append(
[-qty_to_pop, outgoing_rate or batch[1]])
break
else:
# qty found in current batch
# consume it and exit
batch[0] = batch[0] - qty_to_pop
qty_to_pop = 0
stock_value = sum(
(flt(batch[0]) * flt(batch[1]) for batch in self.stock_queue))
stock_qty = sum((flt(batch[0]) for batch in self.stock_queue))
if stock_qty:
self.valuation_rate = stock_value / flt(stock_qty)
if not self.stock_queue:
self.stock_queue.append([
0, sle.incoming_rate or sle.outgoing_rate
or self.valuation_rate
])
