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 ])