Ejemplo n.º 1
0
def get_outstanding_invoices(party_type, party, account, condition=None):
	outstanding_invoices = []
	precision = frappe.get_precision("Sales Invoice", "outstanding_amount")

	if party_type=="Customer":
		dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
	else:
		dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"

	invoice_list = frappe.db.sql("""select
			voucher_no,	voucher_type, posting_date,
			ifnull(sum({dr_or_cr}), 0) as invoice_amount,
			(
				select
					ifnull(sum({payment_dr_or_cr}), 0)
				from `tabGL Entry` payment_gl_entry
				where
					payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
					and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
					and payment_gl_entry.party_type = invoice_gl_entry.party_type
					and payment_gl_entry.party = invoice_gl_entry.party
					and payment_gl_entry.account = invoice_gl_entry.account
					and {payment_dr_or_cr} > 0
			) as payment_amount
		from
			`tabGL Entry` invoice_gl_entry
		where
			party_type = %(party_type)s
			and party = %(party)s
			and account = %(account)s
			and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = ''
						or against_voucher is null))
				or (voucher_type != 'Journal Entry'))
		group by voucher_type, voucher_no
		having (invoice_amount - payment_amount) > 0.005""".format(
			dr_or_cr = dr_or_cr,
			payment_dr_or_cr = payment_dr_or_cr,
			condition = condition or ""
		), {
			"party_type": party_type,
			"party": party,
			"account": account,
		}, as_dict=True)

	for d in invoice_list:
		outstanding_invoices.append({
			'voucher_no': d.voucher_no,
			'voucher_type': d.voucher_type,
			'posting_date': d.posting_date,
			'invoice_amount': flt(d.invoice_amount),
			'payment_amount': flt(d.payment_amount),
			'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision)
		})

	return outstanding_invoices
Ejemplo n.º 2
0
	def make_payment_entry(self):
		self.check_permission('write')
		total_salary_amount = self.get_total_salary_amount()
		default_payroll_payable_account = self.get_default_payroll_payable_account()
		precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")

		if total_salary_amount and total_salary_amount.rounded_total:
			journal_entry = frappe.new_doc('Journal Entry')
			journal_entry.voucher_type = 'Bank Entry'
			journal_entry.user_remark = _('Payment of salary from {0} to {1}')\
				.format(self.start_date, self.end_date)
			journal_entry.company = self.company
			journal_entry.posting_date = self.posting_date

			payment_amount = flt(total_salary_amount.rounded_total, precision)

			journal_entry.set("accounts", [
				{
					"account": self.payment_account,
					"credit_in_account_currency": payment_amount
				},
				{
					"account": default_payroll_payable_account,
					"debit_in_account_currency": payment_amount,
					"reference_type": self.doctype,
					"reference_name": self.name
				}
			])
			return journal_entry.as_dict()
		else:
			frappe.msgprint(
				_("There are no submitted Salary Slips to process."),
				title="Error", indicator="red"
			)
Ejemplo n.º 3
0
	def create_journal_entry(self, je_payment_amount, user_remark):
		default_payroll_payable_account = self.get_default_payroll_payable_account()
		precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")

		journal_entry = frappe.new_doc('Journal Entry')
		journal_entry.voucher_type = 'Bank Entry'
		journal_entry.user_remark = _('Payment of {0} from {1} to {2}')\
			.format(user_remark, self.start_date, self.end_date)
		journal_entry.company = self.company
		journal_entry.posting_date = self.posting_date

		payment_amount = flt(je_payment_amount, precision)

		journal_entry.set("accounts", [
			{
				"account": self.payment_account,
				"credit_in_account_currency": payment_amount
			},
			{
				"account": default_payroll_payable_account,
				"debit_in_account_currency": payment_amount,
				"reference_type": self.doctype,
				"reference_name": self.name
			}
		])
		journal_entry.save(ignore_permissions = True)
Ejemplo n.º 4
0
def get_outstanding_invoices(amount_query, account, party_type, party):
	all_outstanding_vouchers = []
	outstanding_voucher_list = frappe.db.sql("""
		select
			voucher_no, voucher_type, posting_date,
			ifnull(sum({amount_query}), 0) as invoice_amount
		from
			`tabGL Entry`
		where
			account = %s and party_type=%s and party=%s and {amount_query} > 0
			and (CASE
					WHEN voucher_type = 'Journal Entry'
					THEN ifnull(against_voucher, '') = ''
					ELSE 1=1
				END)
		group by voucher_type, voucher_no
		""".format(amount_query = amount_query), (account, party_type, party), as_dict = True)

	for d in outstanding_voucher_list:
		payment_amount = frappe.db.sql("""
			select ifnull(sum({amount_query}), 0)
			from
				`tabGL Entry`
			where
				account = %s and party_type=%s and party=%s and {amount_query} < 0
				and against_voucher_type = %s and ifnull(against_voucher, '') = %s
			""".format(**{
			"amount_query": amount_query
			}), (account, party_type, party, d.voucher_type, d.voucher_no))

		payment_amount = -1*payment_amount[0][0] if payment_amount else 0
		precision = frappe.get_precision("Sales Invoice", "outstanding_amount")

		if d.invoice_amount > payment_amount:

			all_outstanding_vouchers.append({
				'voucher_no': d.voucher_no,
				'voucher_type': d.voucher_type,
				'posting_date': d.posting_date,
				'invoice_amount': flt(d.invoice_amount, precision),
				'outstanding_amount': flt(d.invoice_amount - payment_amount, precision)
			})

	return all_outstanding_vouchers
Ejemplo n.º 5
0
    def make_payment(self):

        # default_payroll_payable_account = self.get_default_payroll_payable_account()
        precision = frappe.get_precision("Journal Entry Account",
                                         "debit_in_account_currency")
        journal_entry = frappe.new_doc('Journal Entry')
        journal_entry.voucher_type = 'Journal Entry'
        journal_entry.user_remark = ("Social insurance Renew")

        journal_entry.company = self.company
        journal_entry.posting_date = self.posting_date
        total_amount = 0.0
        for amount in self.social_insurance_date:
            total_amount += float(amount.amount_for_month)

            journal_entry.append(
                "accounts",
                {
                    "account": self.account,
                    "debit_in_account_currency": float(
                        amount.amount_for_month),
                    "party_type": "Employee",
                    "party": amount.employee,
                    "cost_center": self.cost_center
                },
            )
        journal_entry.append(
            "accounts", {
                "account": self.paid_account,
                "credit_in_account_currency": total_amount,
                "reference_type": self.doctype,
                "cost_center": self.cost_center
            })

        journal_entry.save(ignore_permissions=True)
        self.has_gl = "1"
        self.save()
        return ("done")
Ejemplo n.º 6
0
def get_stock_and_account_balance(account=None, posting_date=None, company=None):
	if not posting_date:
		posting_date = nowdate()

	warehouse_account = get_warehouse_account_map(company)

	account_balance = get_balance_on(
		account, posting_date, in_account_currency=False, ignore_account_permission=True
	)

	related_warehouses = [
		wh
		for wh, wh_details in warehouse_account.items()
		if wh_details.account == account and not wh_details.is_group
	]

	total_stock_value = 0.0
	for warehouse in related_warehouses:
		value = get_stock_value_on(warehouse, posting_date)
		total_stock_value += value

	precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
	return flt(account_balance, precision), flt(total_stock_value, precision), related_warehouses
Ejemplo n.º 7
0
	def get_raw_materials(self, bom_dict,non_stock_item=0):
		""" Get raw materials considering sub-assembly items
			{
				"item_code": [qty_required, description, stock_uom, min_order_qty]
			}
		"""
		item_list = []
		precision = frappe.get_precision("BOM Item", "stock_qty")

		for bom, so_wise_qty in bom_dict.items():
			bom_wise_item_details = {}
			if self.use_multi_level_bom and self.only_raw_materials and self.include_subcontracted:
				# get all raw materials with sub assembly childs
				# Did not use qty_consumed_per_unit in the query, as it leads to rounding loss
				for d in frappe.db.sql("""select fb.item_code,
					ifnull(sum(fb.stock_qty/ifnull(bom.quantity, 1)), 0) as qty,
					fb.description, fb.stock_uom, item.min_order_qty
					from `tabBOM Explosion Item` fb, `tabBOM` bom, `tabItem` item
					where bom.name = fb.parent and item.name = fb.item_code
					and (item.is_sub_contracted_item = 0 or ifnull(item.default_bom, "")="")
					""" + ("and item.is_stock_item = 1","")[non_stock_item] + """
					and fb.docstatus<2 and bom.name=%(bom)s
					group by fb.item_code, fb.stock_uom""", {"bom":bom}, as_dict=1):
						bom_wise_item_details.setdefault(d.item_code, d)
			else:
				# Get all raw materials considering SA items as raw materials,
				# so no childs of SA items
				bom_wise_item_details = self.get_subitems(bom_wise_item_details, bom,1, \
					self.use_multi_level_bom,self.only_raw_materials, self.include_subcontracted,non_stock_item)

			for item, item_details in bom_wise_item_details.items():
				for so_qty in so_wise_qty:
					item_list.append([item, flt(flt(item_details.qty) * so_qty[1], precision),
						item_details.description, item_details.stock_uom, item_details.min_order_qty,
						so_qty[0]])

		self.make_items_dict(item_list)
Ejemplo n.º 8
0
def create_disbursement_journal_entry(doc, method):
	'''
	This function is to be used to create disbursement journal. Primarily developed for creating disbursement journal for Loan
	'''
	#frappe.msgprint("Method fired: " + method)
	precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")

	journal_entry = frappe.new_doc('Journal Entry')
	journal_entry.voucher_type = 'Cash Entry'
	journal_entry.user_remark = _('Payment of {0} disbursed on {1} starting from {2}')\
		.format(doc.name, doc.disbursement_date, doc.repayment_start_date)
	journal_entry.company = doc.company
	# If loan is from application than disbursement date is not set the use posting date.
	if (doc.disbursement_date):
		journal_entry.posting_date = doc.disbursement_date
	else:
		journal_entry.posting_date = doc.posting_date

	payment_amount = flt(doc.loan_amount, precision)

	journal_entry.set("accounts", [
		{
			"account": doc.payment_account,
			"credit_in_account_currency": payment_amount,
			"reference_type": doc.doctype,
			"reference_name": doc.name
		},
		{
			"account": doc.loan_account,
			"debit_in_account_currency": payment_amount,
			"reference_type": doc.doctype,
			"reference_name": doc.name
		}
	])
	journal_entry.save(ignore_permissions = True)
	frappe.msgprint("Disbursement Journal: " + journal_entry.name + " has been created.")
Ejemplo n.º 9
0
    def get_gl_entries(self,
                       warehouse_account=None,
                       default_expense_account=None,
                       default_cost_center=None):

        if not warehouse_account:
            warehouse_account = get_warehouse_account_map(self.company)

        sle_map = self.get_stock_ledger_details()
        voucher_details = self.get_voucher_details(default_expense_account,
                                                   default_cost_center,
                                                   sle_map)

        gl_list = []
        warehouse_with_no_account = []
        precision = frappe.get_precision("GL Entry",
                                         "debit_in_account_currency")
        for item_row in voucher_details:

            sle_list = sle_map.get(item_row.name)
            if sle_list:
                for sle in sle_list:
                    if warehouse_account.get(sle.warehouse):
                        # from warehouse account

                        self.check_expense_account(item_row)

                        # If the item does not have the allow zero valuation rate flag set
                        # and ( valuation rate not mentioned in an incoming entry
                        # or incoming entry not found while delivering the item),
                        # try to pick valuation rate from previous sle or Item master and update in SLE
                        # Otherwise, throw an exception

                        if not sle.stock_value_difference and self.doctype != "Stock Reconciliation" \
                         and not item_row.get("allow_zero_valuation_rate"):

                            sle = self.update_stock_ledger_entries(sle)

                        # expense account/ target_warehouse / source_warehouse
                        if item_row.get('target_warehouse'):
                            warehouse = item_row.get('target_warehouse')
                            expense_account = warehouse_account[warehouse][
                                "account"]
                        else:
                            expense_account = item_row.expense_account

                        gl_list.append(
                            self.get_gl_dict(
                                {
                                    "account":
                                    warehouse_account[
                                        sle.warehouse]["account"],
                                    "against":
                                    expense_account,
                                    "cost_center":
                                    item_row.cost_center,
                                    "project":
                                    item_row.project or self.get('project'),
                                    "remarks":
                                    self.get("remarks")
                                    or "Accounting Entry for Stock",
                                    "debit":
                                    flt(sle.stock_value_difference, precision),
                                    "is_opening":
                                    item_row.get("is_opening")
                                    or self.get("is_opening") or "No",
                                },
                                warehouse_account[sle.warehouse]
                                ["account_currency"],
                                item=item_row))

                        gl_list.append(
                            self.get_gl_dict(
                                {
                                    "account":
                                    expense_account,
                                    "against":
                                    warehouse_account[
                                        sle.warehouse]["account"],
                                    "cost_center":
                                    item_row.cost_center,
                                    "project":
                                    item_row.project or self.get('project'),
                                    "remarks":
                                    self.get("remarks")
                                    or "Accounting Entry for Stock",
                                    "credit":
                                    flt(sle.stock_value_difference, precision),
                                    "project":
                                    item_row.get("project")
                                    or self.get("project"),
                                    "is_opening":
                                    item_row.get("is_opening")
                                    or self.get("is_opening") or "No"
                                },
                                item=item_row))
                    elif sle.warehouse not in warehouse_with_no_account:
                        warehouse_with_no_account.append(sle.warehouse)

        if warehouse_with_no_account:
            for wh in warehouse_with_no_account:
                if frappe.db.get_value("Warehouse", wh, "company"):
                    frappe.throw(
                        _("Warehouse {0} is not linked to any account, please mention the account in the warehouse record or set default inventory account in company {1}."
                          ).format(wh, self.company))

        return process_gl_map(gl_list)
Ejemplo n.º 10
0
	def create_journal_entry2(self ,salary_slip_name_list):
		default_payroll_payable_account = self.get_default_payroll_payable_account()
		precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
		journal_entry = frappe.new_doc('Journal Entry')
		journal_entry.voucher_type = 'Journal Entry'
		journal_entry.user_remark = _('Payment of {0} from {1} to {2}')\
			.format("salary", self.start_date, self.end_date)
		journal_entry.company = self.company
		journal_entry.posting_date = self.posting_date
		total_amount = 0
		for salary_slip_name in salary_slip_name_list:
			salary_slip_total = 0
			salary_slip = frappe.get_doc("Salary Slip", salary_slip_name[0])

			for sal_detail in salary_slip.earnings:
				employee = salary_slip.employee
				is_flexible_benefit, only_tax_impact, creat_separate_je, statistical_component = frappe.db.get_value("Salary Component", sal_detail.salary_component,
					['is_flexible_benefit', 'only_tax_impact', 'create_separate_payment_entry_against_benefit_claim', 'statistical_component'])
				if only_tax_impact != 1 and statistical_component != 1:
					if is_flexible_benefit == 1 and creat_separate_je == 1:
						pass
						# self.create_journal_entry(sal_detail.amount, sal_detail.salary_component ,employee)
					else:
						salary_slip_total += sal_detail.amount
			for sal_detail in salary_slip.deductions:
				statistical_component = frappe.db.get_value("Salary Component", sal_detail.salary_component, 'statistical_component')
				if statistical_component != 1:
					salary_slip_total -= sal_detail.amount
			total_amount += salary_slip_total
			# frappe.msgprint(str(salary_slip_total))
			# frappe.throw(total_amount)
			# journal_entry.set
			# doc.append
			journal_entry.append("accounts",
				{
				"account": self.resevable_acoount,
				"party_type" : "Employee",
				"party" : employee ,
				"credit_in_account_currency":salary_slip_total,
				"cost_center":self.cost_center

				},

			)
		journal_entry.append("accounts",
			{
			"account": default_payroll_payable_account,
			"debit_in_account_currency": total_amount,
			"reference_type": self.doctype,
			"reference_name": self.name,
			"cost_center":self.cost_center
			})




		# if salary_slip_total > 0:
		# 	self.create_journal_entry(salary_slip_total, "salary" ,employee)
		# journal_entry.set("accounts", [
		# 	{
		# 	"account": default_payroll_payable_account,
		# 	"debit_in_account_currency": total_amount,
		# 	"reference_type": self.doctype,
		# 	"reference_name": self.name
		# 	}
		# ])
		journal_entry.save(ignore_permissions = True)
		self.has_gl =1
		self.save()
Ejemplo n.º 11
0
    def make_accrual_jv_entry(self):
        self.check_permission('write')
        earnings = self.get_salary_component_total(
            component_type="earnings") or {}
        deductions = self.get_salary_component_total(
            component_type="deductions") or {}
        default_payroll_payable_account = self.get_default_payroll_payable_account(
        )
        loan_details = self.get_loan_details()
        jv_name = ""
        precision = frappe.get_precision("Journal Entry Account",
                                         "debit_in_account_currency")

        if earnings or deductions:
            journal_entry = frappe.new_doc('Journal Entry')
            journal_entry.voucher_type = 'Journal Entry'
            journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
             .format(self.start_date, self.end_date)
            journal_entry.company = self.company
            journal_entry.posting_date = self.posting_date

            accounts = []
            payable_amount = 0

            # Earnings
            for acc, amount in earnings.items():
                payable_amount += flt(amount, precision)
                accounts.append({
                    "account":
                    acc,
                    "debit_in_account_currency":
                    flt(amount, precision),
                    "cost_center":
                    self.cost_center,
                    "project":
                    self.project
                })

            # Deductions
            for acc, amount in deductions.items():
                payable_amount -= flt(amount, precision)
                accounts.append({
                    "account":
                    acc,
                    "credit_in_account_currency":
                    flt(amount, precision),
                    "cost_center":
                    self.cost_center,
                    "project":
                    self.project
                })

            # Loan
            for data in loan_details:
                accounts.append({
                    "account":
                    data.loan_account,
                    "credit_in_account_currency":
                    data.principal_amount
                })

                if data.interest_amount and not data.interest_income_account:
                    frappe.throw(
                        _("Select interest income account in loan {0}").format(
                            data.loan))

                if data.interest_income_account and data.interest_amount:
                    accounts.append({
                        "account": data.interest_income_account,
                        "credit_in_account_currency": data.interest_amount,
                        "cost_center": self.cost_center,
                        "project": self.project
                    })
                payable_amount -= flt(data.total_payment, precision)

            # Payable amount
            accounts.append({
                "account":
                default_payroll_payable_account,
                "credit_in_account_currency":
                flt(payable_amount, precision)
            })

            journal_entry.set("accounts", accounts)
            journal_entry.title = default_payroll_payable_account
            journal_entry.save()

            try:
                journal_entry.submit()
                jv_name = journal_entry.name
                self.update_salary_slip_status(jv_name=jv_name)
            except Exception as e:
                frappe.msgprint(e)

        return jv_name
Ejemplo n.º 12
0
def get_outstanding_invoices(party_type,
                             party,
                             account,
                             condition=None,
                             include_negative_outstanding=False):
    outstanding_invoices = []
    precision = frappe.get_precision("Sales Invoice",
                                     "outstanding_amount") or 2

    if erpnext.get_party_account_type(party_type) == 'Receivable':
        dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
        payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
    else:
        dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
        payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"

    held_invoices = get_held_invoices(party_type, party)

    invoice_list = frappe.db.sql("""
		select
			voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
		from
			`tabGL Entry`
		where
			party_type = %(party_type)s and party = %(party)s and account = %(account)s
			and (against_voucher = '' or against_voucher is null) and voucher_type != 'Payment Entry'
			{condition}
		group by voucher_type, voucher_no
		order by posting_date, name
	""".format(dr_or_cr=dr_or_cr, condition=condition or ""), {
        "party_type": party_type,
        "party": party,
        "account": account
    },
                                 as_dict=True)

    payment_entries = frappe.db.sql("""
		select
			against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
		from
			`tabGL Entry`
		where
			party_type = %(party_type)s and party = %(party)s and account = %(account)s
			and against_voucher is not null and against_voucher != ''
		group by against_voucher_type, against_voucher
	""".format(payment_dr_or_cr=payment_dr_or_cr), {
        "party_type": party_type,
        "party": party,
        "account": account,
    },
                                    as_dict=True)

    pe_map = frappe._dict()
    for d in payment_entries:
        pe_map.setdefault((d.against_voucher_type, d.against_voucher),
                          d.payment_amount)

    for d in invoice_list:
        payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
        outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
        diff = abs(outstanding_amount
                   ) if include_negative_outstanding else outstanding_amount
        if diff > 0.5 / (10**precision):
            if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
                due_date = frappe.db.get_value(
                    d.voucher_type, d.voucher_no,
                    "posting_date" if party_type == "Employee" else "due_date")

                outstanding_invoices.append(
                    frappe._dict({
                        'voucher_no': d.voucher_no,
                        'voucher_type': d.voucher_type,
                        'posting_date': d.posting_date,
                        'invoice_amount': flt(d.invoice_amount),
                        'payment_amount': payment_amount,
                        'outstanding_amount': outstanding_amount,
                        'due_date': due_date
                    }))

    outstanding_invoices = sorted(
        outstanding_invoices,
        key=lambda k:
        (k['outstanding_amount'] > 0, k['due_date'] or getdate(nowdate())))
    return outstanding_invoices
Ejemplo n.º 13
0
 def get():
     return frappe.get_precision(dt, field)
Ejemplo n.º 14
0
def get_outstanding_invoices(party_type, party, account, condition=None):
    outstanding_invoices = []
    precision = frappe.get_precision("Sales Invoice", "outstanding_amount")

    if party_type in ("Customer", "Student"):
        dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
        payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
    else:
        dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
        payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"

    invoice = 'Sales Invoice' if party_type == 'Customer' else 'Purchase Invoice'
    invoice_list = frappe.db.sql("""
		select
			voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
			(
				select ifnull(sum({payment_dr_or_cr}), 0)
				from `tabGL Entry` payment_gl_entry
				where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
					and if(invoice_gl_entry.voucher_type='Journal Entry',
						payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no,
						payment_gl_entry.against_voucher = invoice_gl_entry.against_voucher)
					and payment_gl_entry.party_type = invoice_gl_entry.party_type
					and payment_gl_entry.party = invoice_gl_entry.party
					and payment_gl_entry.account = invoice_gl_entry.account
					and {payment_dr_or_cr} > 0
			) as payment_amount
		from
			`tabGL Entry` invoice_gl_entry
		where
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		having (invoice_amount - payment_amount) > 0.005
		order by posting_date, name""".format(dr_or_cr=dr_or_cr,
                                        invoice=invoice,
                                        payment_dr_or_cr=payment_dr_or_cr,
                                        condition=condition or ""), {
                                            "party_type": party_type,
                                            "party": party,
                                            "account": account,
                                        },
                                 as_dict=True)

    for d in invoice_list:
        due_date = frappe.db.get_value(
            d.voucher_type, d.voucher_no,
            "posting_date" if party_type == "Employee" else "due_date")

        outstanding_invoices.append(
            frappe._dict({
                'voucher_no':
                d.voucher_no,
                'voucher_type':
                d.voucher_type,
                'posting_date':
                d.posting_date,
                'invoice_amount':
                flt(d.invoice_amount),
                'payment_amount':
                flt(d.payment_amount),
                'outstanding_amount':
                flt(d.invoice_amount - d.payment_amount, precision),
                'due_date':
                due_date
            }))

    outstanding_invoices = sorted(
        outstanding_invoices,
        key=lambda k: k['due_date'] or getdate(nowdate()))

    return outstanding_invoices
Ejemplo n.º 15
0
def create_additional_salary_journal(doc, method):
    #frappe.msgprint("Method fired is: " + str(method))
    if (frappe.get_value("Salary Component", doc.salary_component,
                         "create_cash_journal")):
        salary_component = frappe.get_doc("Salary Component",
                                          doc.salary_component)
        cash_account = frappe.db.get_single_value(
            "Payware Settings",
            "default_account_for_additional_component_cash_journal")
        component_account = None
        try:
            component_account = frappe.db.get_value(
                "Salary Component Account", {
                    "parent": doc.salary_component,
                    "company": doc.company
                }, "default_account")
            if not component_account:
                frappe.throw("Ther is no account")
        except Exception as e:
            component_account = frappe.db.get_value(
                "Salary Component Account", {
                    "parent": doc.salary_component,
                    "company": doc.company
                }, "account")
        #frappe.msgprint("Expense account is: " + str(component_account))
        if method == "on_submit":
            dr_account = component_account
            cr_account = cash_account
        elif method == "on_cancel":
            dr_account = cash_account
            cr_account = component_account
        else:
            frappe.msgprint(
                "Unknown method on create_additional_salary_journal")
            return

        #frappe.msgprint("Method fired: " + method)
        precision = frappe.get_precision("Journal Entry Account",
                                         "debit_in_account_currency")
        journal_entry = frappe.new_doc('Journal Entry')
        journal_entry.voucher_type = 'Cash Entry'
        journal_entry.user_remark = _('{2} by {1} for {3}').format(
            doc.doctype, doc.name, doc.salary_component, doc.employee_name)
        journal_entry.company = doc.company
        journal_entry.posting_date = doc.payroll_date

        payment_amount = flt(doc.amount, precision)

        journal_entry.set("accounts",
                          [{
                              "account": dr_account,
                              "debit_in_account_currency": payment_amount
                          }, {
                              "account": cr_account,
                              "credit_in_account_currency": payment_amount
                          }])
        journal_entry.save(ignore_permissions=True)

        if method == "on_submit":
            frappe.set_value(doc.doctype, doc.name, "journal_name",
                             journal_entry.name)
            msg_to_print = doc.doctype + " journal " + journal_entry.name + " has been created."
        elif method == "on_cancel":
            msg_to_print = doc.doctype + " reverse journal " + journal_entry.name + " has been created."
        frappe.msgprint(msg_to_print)
    if (doc.auto_created_based_on):
        frappe.set_value("Additional Salary", doc.auto_created_based_on,
                         "last_transaction_amount", doc.amount)
Ejemplo n.º 16
0
def create_loan_repayment_jv(doc, method):
    loan = frappe.get_doc("Loan", doc.loan)
    if method == "on_submit":
        dr_account = loan.payment_account
        cr_account = loan.loan_account
    elif method == "on_cancel":
        dr_account = loan.loan_account
        cr_account = loan.payment_account
    else:
        frappe.msgprint("Unknown method on create_loan_repayment_jv")
        return
    #frappe.msgprint("Method fired: " + method)
    precision = frappe.get_precision("Journal Entry Account",
                                     "debit_in_account_currency")

    journal_entry = frappe.new_doc('Journal Entry')
    journal_entry.voucher_type = 'Cash Entry'
    journal_entry.user_remark = _('{0} - {1} on {2}').format(
        doc.doctype, doc.name, doc.payment_date)
    journal_entry.company = doc.company
    journal_entry.posting_date = doc.payment_date

    payment_amount = flt(doc.payment_amount, precision)

    journal_entry.set("accounts", [{
        "account": dr_account,
        "debit_in_account_currency": payment_amount,
        "reference_type": loan.doctype,
        "reference_name": loan.name
    }, {
        "account": cr_account,
        "credit_in_account_currency": payment_amount,
        "reference_type": loan.doctype,
        "reference_name": loan.name
    }])
    journal_entry.save(ignore_permissions=True)

    # Create records in NFS child doctype of Loan doctype
    if method == "on_submit":
        #frappe.msgprint("loan nfs repayment appending...")
        loan_nfs_row = loan.append("loan_repayments_not_from_salary")
        loan_nfs_row.nfs_loan_repayment = doc.name
        loan_nfs_row.company = doc.company
        loan_nfs_row.payment_date = doc.payment_date
        loan_nfs_row.payment_amount = doc.payment_amount
        loan.save()
    elif method == "on_cancel":
        # Delete record from loan related to this repayment
        for repayment in frappe.get_all("Loan NFS Repayments", "name",
                                        {"nfs_loan_repayment": doc.name}):
            #frappe.msgprint("doc.name: " + str(repayment.name) + " for loan repayment: " + doc.name)
            frappe.db.sql(
                """update `tabLoan NFS Repayments` set docstatus = 0 where name = %s""",
                repayment.name)
            frappe.delete_doc("Loan NFS Repayments", repayment.name)

    # Update loan of Repayment Schedule child doctype of Loan doctype and set the balances right as per date
    redo_repayment_schedule(loan.name)
    set_repayment_period(loan.name)
    calculate_totals(loan.name)

    if method == "on_submit":
        frappe.set_value(doc.doctype, doc.name, "journal_name",
                         journal_entry.name)
        msg_to_print = doc.doctype + " journal " + journal_entry.name + " has been created."
    elif method == "on_cancel":
        msg_to_print = doc.doctype + " reverse journal " + journal_entry.name + " has been created."
    frappe.msgprint(msg_to_print)
Ejemplo n.º 17
0
def make_accural_jv_entry(self):
	#self.check_permission('write')
	earnings = get_salary_component_total(self,component_type = "earnings") or {}
	deductions = get_salary_component_total(self,component_type = "deductions") or {}
	default_payroll_payable_account = get_default_payroll_payable_account(self)
	loan_details = get_loan_details(self)
	jv_name = ""
	precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")

	if earnings or deductions:
		journal_entry = frappe.new_doc('Journal Entry')
		journal_entry.voucher_type = 'Journal Entry'
		journal_entry.user_remark = _('Accural Journal Entry for salaries from {0} to {1}')\
			.format(self.start_date, self.end_date)
		journal_entry.company = self.company
		journal_entry.posting_date = nowdate()

		accounts = []
		payable_amount = 0

		#HELKYDS
		contasal = 0
		empresa = frappe.get_doc('Company',self.company)

		contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '7252%%'  """,(empresa.name),as_dict=False)

		print contaseg_soc

		if (contaseg_soc == ()):
			print "VAZIO"
			print "VAZIO"
			print "VAZIO"
			print "VAZIO"
			contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '5.10.80.10.10.20.90%%'  """,(empresa.name),as_dict=False)

			print contaseg_soc

	
		ss_list = get_sal_slip_list(self,ss_status=1)
		print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
		print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
		print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
		print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
		print ss_list
		saliliquido = 0
		for x in ss_list:
			print x
			ss_obj = frappe.get_doc("Salary Slip",x[0])
			print ss_obj.salario_iliquido				
			saliliquido = saliliquido + flt(ss_obj.salario_iliquido)

		# =============

		# Earnings
		for acc, amount in earnings.items():
			payable_amount += flt(amount, precision)
			accounts.append({
					"account": acc,
					"debit_in_account_currency": flt(amount, precision),
					"cost_center": self.cost_center,
					"project": contasal_prj 
				})

			#HELKYDS
			conta = acc;
			# 72210000 or 5.10.80.10.10.20.90 
			print "VEvervegtertasdfasfdsafsadf"
			print "VEvervegtertasdfasfdsafsadf"
			print "EARNINGS"
			print acc
			print conta.find('72210000')
			print conta.find('5.10.80.10.10.20.10') 

			if (conta.find('72210000') !=-1):
				contasal = acc
				contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)
#				contasal_prj = self['project'] 
				print "CONTA 72210000"
				print contaseg_soc[0][0]
				print amount
				print contasal_amt
				print payable_amount

			elif (conta.find('5.10.80.10.10.20.10') !=-1):
				contasal = acc
				contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)

				print "CONTA 5.10.80.10.10.20.10"
				print contaseg_soc[0][0]
				print amount
				print contasal_amt
				print payable_amount

		#Acrescenta a conta da Seguranca Social

		print "CENTRO CUSTO SEG. SOCIAL "
		segsocial = frappe.db.sql(""" SELECT name from `tabCost Center` where company = %s and cost_center_name = 'seguranca social'  """,(empresa.name),as_dict=False)

		print "Seg. social"
		print segsocial[0][0]


		if (contasal != 0):
			print "ADIICINAEIIII o DEBITO "
			print  contasal_amt
			accounts.append({
					"account": contaseg_soc[0][0],
					"debit_in_account_currency": contasal_amt,
					"cost_center": segsocial[0][0], #contasal_cent, # segsocial[0][0],
					"project": contasal_prj
				})
			payable_amount = payable_amount+contasal_amt
			#contasal = 0			
	

		print payable_amount
		
		# ============

		# Deductions
		for acc, amount in deductions.items():
			payable_amount -= flt(amount, precision)
			accounts.append({
					"account": acc,
					"credit_in_account_currency": flt(amount, precision),
					"cost_center": self.cost_center,
					"project": contasal_prj 
				})
		#HELKYDS

			conta = acc;
			# 34610000 or 2.80.40.20 2.80.20.20.20
			if (conta.find('34610000') !=-1):
				contasal = acc
				#contasal_amt = round(amt * 0.08)
#				contasal_cent = self['cost_center'] 
#				contasal_prj = self['project'] 

				print "CONTA 34610000"
				print acc
				print amount
				print contasal_amt
				print payable_amount

			elif (conta.find('2.80.20.20.20') !=-1):
				contasal = acc
				#contasal_amt = round(amt * 0.08)
#				contasal_cent = self['cost_center'] 
#				contasal_prj = self['project'] 

				print "CONTA 2.80.20.20.20"
				print acc
				print amount
				print contasal_amt
				print payable_amount

		#Acrescenta a conta do DEBITO da Seguranca Social
		if (contasal != 0):

			accounts.append({
					"account": contasal,
					"credit_in_account_currency": contasal_amt,
					"cost_center": self.cost_center,
					"project": contasal_prj
				})
			payable_amount = payable_amount-contasal_amt		
			contasal = 0	
			print "ADIICINAEIIII o CREDITO "
			print  contasal_amt
			print payable_amount

		# ==========

		# Employee loan
		for data in loan_details:
			accounts.append({
					"account": data.employee_loan_account,
					"credit_in_account_currency": data.principal_amount
				})
			accounts.append({
					"account": data.interest_income_account,
					"credit_in_account_currency": data.interest_amount,
					"cost_center": contasal_cent,
					"project": contasal_prj 
				})
			payable_amount -= flt(data.total_payment, precision)

		# Payable amount
		accounts.append({
			"account": default_payroll_payable_account,
			"credit_in_account_currency": flt(payable_amount, precision)
		})

		journal_entry.set("accounts", accounts)
		journal_entry.save()

		try:
			journal_entry.submit()
			jv_name = journal_entry.name
			update_salary_slip_status(self,jv_name = jv_name)
		except Exception as e:
			frappe.msgprint(e)

	return jv_name
Ejemplo n.º 18
0
def make_accrual_jv_entry(self):
    self.check_permission('write')

    journal_entry = frappe.new_doc('Journal Entry')
    journal_entry.voucher_type = 'Journal Entry'
    journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
        .format(self.start_date, self.end_date)
    journal_entry.company = self.company
    journal_entry.posting_date = self.posting_date

    accounts = []
    payable_amount = 0

    jv_name = ""
    precision = frappe.get_precision("Journal Entry Account",
                                     "debit_in_account_currency")
    default_payroll_payable_account = self.get_default_payroll_payable_account(
    )
    old_branch = self.branch
    if not self.branch:
        branchs = frappe.get_all('Branch', fields=["name", "cost_center"])
        bulk_mode = True
    else:
        branchs = [self.branch]
        bulk_mode = False
    for branch in branchs:

        if bulk_mode:
            cost_center = branch.cost_center
            self.branch = branch.name
        else:
            cost_center = self.cost_center
            self.branch = branch

        earnings = self.get_salary_component_total(
            component_type="earnings") or {}
        deductions = self.get_salary_component_total(
            component_type="deductions") or {}

        if earnings or deductions:

            # Earnings
            for acc, amount in earnings.items():
                payable_amount += flt(amount, precision)
                accounts.append({
                    "account":
                    acc,
                    "debit_in_account_currency":
                    flt(amount, precision),
                    "party_type":
                    '',
                    "cost_center":
                    cost_center,
                    "project":
                    self.project,
                    "user_remark":
                    _('Accrual Journal Entry for salaries from {0} to {1} branch: {2}'
                      ).format(self.start_date, self.end_date, self.branch)
                })

            # Deductions
            for acc, amount in deductions.items():
                payable_amount -= flt(amount, precision)
                accounts.append({
                    "account":
                    acc,
                    "credit_in_account_currency":
                    flt(amount, precision),
                    "cost_center":
                    cost_center,
                    "party_type":
                    '',
                    "project":
                    self.project
                })

    self.branch = old_branch
    # Payable amount
    accounts.append({
        "account":
        default_payroll_payable_account,
        "credit_in_account_currency":
        flt(payable_amount, precision),
        "party_type":
        '',
    })

    journal_entry.set("accounts", accounts)
    journal_entry.title = default_payroll_payable_account
    journal_entry.save()

    try:
        journal_entry.submit()
        jv_name = journal_entry.name
        self.update_salary_slip_status(jv_name=jv_name)
    except Exception as e:
        frappe.msgprint(e)

    return jv_name
Ejemplo n.º 19
0
def get_outstanding_invoices(party_type, party, account, condition=None):
	outstanding_invoices = []
	precision = frappe.get_precision("Sales Invoice", "outstanding_amount")

	if party_type=="Customer":
		dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
	else:
		dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"

	invoice_list = frappe.db.sql("""select
			voucher_no,	voucher_type, posting_date,
			ifnull(sum({dr_or_cr}), 0) as invoice_amount,
			(
				select
					ifnull(sum({payment_dr_or_cr}), 0)
				from `tabGL Entry` payment_gl_entry
				where
					payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
					and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
					and payment_gl_entry.party_type = invoice_gl_entry.party_type
					and payment_gl_entry.party = invoice_gl_entry.party
					and payment_gl_entry.account = invoice_gl_entry.account
					and {payment_dr_or_cr} > 0
			) as payment_amount
		from
			`tabGL Entry` invoice_gl_entry
		where
			party_type = %(party_type)s
			and party = %(party)s
			and account = %(account)s
			and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = ''
						or against_voucher is null))
				or (voucher_type != 'Journal Entry'))
		group by voucher_type, voucher_no
		having (invoice_amount - payment_amount) > 0.005""".format(
			dr_or_cr = dr_or_cr,
			payment_dr_or_cr = payment_dr_or_cr,
			condition = condition or ""
		), {
			"party_type": party_type,
			"party": party,
			"account": account,
		}, as_dict=True)

	for d in invoice_list:
		outstanding_invoices.append({
			'voucher_no': d.voucher_no,
			'voucher_type': d.voucher_type,
			'posting_date': d.posting_date,
			'invoice_amount': flt(d.invoice_amount),
			'payment_amount': flt(d.payment_amount),
			'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision),
			'due_date': frappe.db.get_value(d.voucher_type, d.voucher_no, "due_date")
		})

	return outstanding_invoices
Ejemplo n.º 20
0
def get_outstanding_invoices(party_type, party, account, condition=None, filters=None):
	outstanding_invoices = []
	precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2

	if account:
		root_type, account_type = frappe.get_cached_value(
			"Account", account, ["root_type", "account_type"]
		)
		party_account_type = "Receivable" if root_type == "Asset" else "Payable"
		party_account_type = account_type or party_account_type
	else:
		party_account_type = erpnext.get_party_account_type(party_type)

	if party_account_type == "Receivable":
		dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
		payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
	else:
		dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
		payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"

	held_invoices = get_held_invoices(party_type, party)

	invoice_list = frappe.db.sql(
		"""
		select
			voucher_no, voucher_type, posting_date, due_date,
			ifnull(sum({dr_or_cr}), 0) as invoice_amount,
			account_currency as currency
		from
			`tabGL Entry`
		where
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			and is_cancelled=0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		order by posting_date, name""".format(
			dr_or_cr=dr_or_cr, condition=condition or ""
		),
		{
			"party_type": party_type,
			"party": party,
			"account": account,
		},
		as_dict=True,
	)

	payment_entries = frappe.db.sql(
		"""
		select against_voucher_type, against_voucher,
			ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
		from `tabGL Entry`
		where party_type = %(party_type)s and party = %(party)s
			and account = %(account)s
			and {payment_dr_or_cr} > 0
			and against_voucher is not null and against_voucher != ''
			and is_cancelled=0
		group by against_voucher_type, against_voucher
	""".format(
			payment_dr_or_cr=payment_dr_or_cr
		),
		{"party_type": party_type, "party": party, "account": account},
		as_dict=True,
	)

	pe_map = frappe._dict()
	for d in payment_entries:
		pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount)

	for d in invoice_list:
		payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
		outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
		if outstanding_amount > 0.5 / (10**precision):
			if (
				filters
				and filters.get("outstanding_amt_greater_than")
				and not (
					outstanding_amount >= filters.get("outstanding_amt_greater_than")
					and outstanding_amount <= filters.get("outstanding_amt_less_than")
				)
			):
				continue

			if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
				outstanding_invoices.append(
					frappe._dict(
						{
							"voucher_no": d.voucher_no,
							"voucher_type": d.voucher_type,
							"posting_date": d.posting_date,
							"invoice_amount": flt(d.invoice_amount),
							"payment_amount": payment_amount,
							"outstanding_amount": outstanding_amount,
							"due_date": d.due_date,
							"currency": d.currency,
						}
					)
				)

	outstanding_invoices = sorted(
		outstanding_invoices, key=lambda k: k["due_date"] or getdate(nowdate())
	)
	return outstanding_invoices
Ejemplo n.º 21
0
	def make_accrual_jv_entry(self):
		self.check_permission('write')
		earnings = self.get_salary_component_total(component_type = "earnings") or {}
		deductions = self.get_salary_component_total(component_type = "deductions") or {}
		default_payroll_payable_account = self.get_default_payroll_payable_account()
		loan_details = self.get_loan_details()
		jv_name = ""
		precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")

		if earnings or deductions:
			journal_entry = frappe.new_doc('Journal Entry')
			journal_entry.voucher_type = 'Journal Entry'
			journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
				.format(self.start_date, self.end_date)
			journal_entry.company = self.company
			journal_entry.posting_date = self.posting_date

			accounts = []
			payable_amount = 0

			# Earnings
			for acc, amount in earnings.items():
				payable_amount += flt(amount, precision)
				accounts.append({
						"account": acc,
						"debit_in_account_currency": flt(amount, precision),
						"cost_center": self.cost_center,
						"project": self.project
					})

			# Deductions
			for acc, amount in deductions.items():
				payable_amount -= flt(amount, precision)
				accounts.append({
						"account": acc,
						"credit_in_account_currency": flt(amount, precision),
						"cost_center": self.cost_center,
						"project": self.project
					})

			# Loan
			for data in loan_details:
				accounts.append({
						"account": data.loan_account,
						"credit_in_account_currency": data.principal_amount
					})

				if data.interest_amount and not data.interest_income_account:
					frappe.throw(_("Select interest income account in loan {0}").format(data.loan))

				if data.interest_income_account and data.interest_amount:
					accounts.append({
						"account": data.interest_income_account,
						"credit_in_account_currency": data.interest_amount,
						"cost_center": self.cost_center,
						"project": self.project
					})
				payable_amount -= flt(data.total_payment, precision)

			# Payable amount
			accounts.append({
				"account": default_payroll_payable_account,
				"credit_in_account_currency": flt(payable_amount, precision)
			})

			journal_entry.set("accounts", accounts)
			journal_entry.title = default_payroll_payable_account
			journal_entry.save()

			try:
				journal_entry.submit()
				jv_name = journal_entry.name
				self.update_salary_slip_status(jv_name = jv_name)
			except Exception as e:
				frappe.msgprint(e)

		return jv_name
Ejemplo n.º 22
0
def get_outstanding_invoices(party_type, party, account, condition=None):
	outstanding_invoices = []
	precision = frappe.get_precision("Sales Invoice", "outstanding_amount")

	if party_type=="Customer":
		dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
	else:
		dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
		payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"

	invoice = 'Sales Invoice' if party_type == 'Customer' else 'Purchase Invoice'
	invoice_list = frappe.db.sql("""
		select
			voucher_no,	voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
			(
				case when (voucher_type = 'Sales Invoice' or voucher_type = 'Purchase Invoice')
					then (select due_date from `tab{invoice}` where name = voucher_no)
				else posting_date end
			) as due_date,
			(
				select ifnull(sum({payment_dr_or_cr}), 0)
				from `tabGL Entry` payment_gl_entry
				where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
					and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
					and payment_gl_entry.party_type = invoice_gl_entry.party_type
					and payment_gl_entry.party = invoice_gl_entry.party
					and payment_gl_entry.account = invoice_gl_entry.account
					and {payment_dr_or_cr} > 0
			) as payment_amount
		from
			`tabGL Entry` invoice_gl_entry
		where
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		having (invoice_amount - payment_amount) > 0.005
		order by posting_date, name""".format(
			dr_or_cr = dr_or_cr,
			invoice = invoice,
			payment_dr_or_cr = payment_dr_or_cr,
			condition = condition or ""
		), {
			"party_type": party_type,
			"party": party,
			"account": account,
		}, as_dict=True)

	for d in invoice_list:
		outstanding_invoices.append(frappe._dict({
			'voucher_no': d.voucher_no,
			'voucher_type': d.voucher_type,
			'due_date': d.due_date,
			'posting_date': d.posting_date,
			'invoice_amount': flt(d.invoice_amount),
			'payment_amount': flt(d.payment_amount),
			'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision),
			'due_date': frappe.db.get_value(d.voucher_type, d.voucher_no, 
				"posting_date" if party_type=="Employee" else "due_date"),
		}))

	outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))

	return outstanding_invoices
Ejemplo n.º 23
0
def get_outstanding_invoices(party_type, party, account, condition=None, limit=None):
	outstanding_invoices = []
	precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2

	if erpnext.get_party_account_type(party_type) == 'Receivable':
		dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
		payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
	else:
		dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
		payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"

	invoice = 'Sales Invoice' if erpnext.get_party_account_type(party_type) == 'Receivable' else 'Purchase Invoice'
	held_invoices = get_held_invoices(party_type, party)
	limit_cond = "limit %s" % limit if limit else ""

	invoice_list = frappe.db.sql("""
		select
			voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
		from
			`tabGL Entry`
		where
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		order by posting_date, name {limit_cond}""".format(
			dr_or_cr=dr_or_cr,
			invoice = invoice,
			condition=condition or "",
			limit_cond = limit_cond
		), {
			"party_type": party_type,
			"party": party,
			"account": account,
		}, as_dict=True)

	payment_entries = frappe.db.sql("""
		select against_voucher_type, against_voucher,
			ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
		from `tabGL Entry`
		where party_type = %(party_type)s and party = %(party)s
			and account = %(account)s
			and {payment_dr_or_cr} > 0
			and against_voucher is not null and against_voucher != ''
		group by against_voucher_type, against_voucher
	""".format(payment_dr_or_cr=payment_dr_or_cr), {
		"party_type": party_type,
		"party": party,
		"account": account,
	}, as_dict=True)

	pe_map = frappe._dict()
	for d in payment_entries:
		pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount)

	for d in invoice_list:
		payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
		outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
		if outstanding_amount > 0.5 / (10**precision):
			if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
				due_date = frappe.db.get_value(
					d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date")

				outstanding_invoices.append(
					frappe._dict({
						'voucher_no': d.voucher_no,
						'voucher_type': d.voucher_type,
						'posting_date': d.posting_date,
						'invoice_amount': flt(d.invoice_amount),
						'payment_amount': payment_amount,
						'outstanding_amount': outstanding_amount,
						'due_date': due_date
					})
				)

	outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))
	return outstanding_invoices
Ejemplo n.º 24
0
    def get_debit_field_precision(self):
        if not frappe.flags.debit_field_precision:
            frappe.flags.debit_field_precision = frappe.get_precision(
                "GL Entry", "debit_in_account_currency")

        return frappe.flags.debit_field_precision
Ejemplo n.º 25
0
    def make_accrual_jv_entry(self):
        self.check_permission('write')
        earnings = self.get_salary_component_total(
            component_type="earnings") or {}
        deductions = self.get_salary_component_total(
            component_type="deductions") or {}
        payroll_payable_account = self.payroll_payable_account
        jv_name = ""
        precision = frappe.get_precision("Journal Entry Account",
                                         "debit_in_account_currency")

        if earnings or deductions:
            journal_entry = frappe.new_doc('Journal Entry')
            journal_entry.voucher_type = 'Journal Entry'
            journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
             .format(self.start_date, self.end_date)
            journal_entry.company = self.company
            journal_entry.posting_date = self.posting_date

            accounts = []
            currencies = []
            payable_amount = 0
            multi_currency = 0
            company_currency = erpnext.get_company_currency(self.company)

            # Earnings
            for acc_cc, amount in earnings.items():
                exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
                    acc_cc[0], amount, company_currency, currencies)
                payable_amount += flt(amount, precision)
                accounts.append({
                    "account":
                    acc_cc[0],
                    "debit_in_account_currency":
                    flt(amt, precision),
                    "exchange_rate":
                    flt(exchange_rate),
                    "party_type":
                    '',
                    "cost_center":
                    acc_cc[1] or self.cost_center,
                    "project":
                    self.project
                })

            # Deductions
            for acc_cc, amount in deductions.items():
                exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
                    acc_cc[0], amount, company_currency, currencies)
                payable_amount -= flt(amount, precision)
                accounts.append({
                    "account":
                    acc_cc[0],
                    "credit_in_account_currency":
                    flt(amt, precision),
                    "exchange_rate":
                    flt(exchange_rate),
                    "cost_center":
                    acc_cc[1] or self.cost_center,
                    "party_type":
                    '',
                    "project":
                    self.project
                })

            # Payable amount
            exchange_rate, payable_amt = self.get_amount_and_exchange_rate_for_journal_entry(
                payroll_payable_account, payable_amount, company_currency,
                currencies)
            accounts.append({
                "account":
                payroll_payable_account,
                "credit_in_account_currency":
                flt(payable_amt, precision),
                "exchange_rate":
                flt(exchange_rate),
                "party_type":
                '',
                "cost_center":
                self.cost_center
            })

            journal_entry.set("accounts", accounts)
            if len(currencies) > 1:
                multi_currency = 1
            journal_entry.multi_currency = multi_currency
            journal_entry.title = payroll_payable_account
            journal_entry.save()

            try:
                journal_entry.submit()
                jv_name = journal_entry.name
                self.update_salary_slip_status(jv_name=jv_name)
            except Exception as e:
                if type(e) in (str, list, tuple):
                    frappe.msgprint(e)
                raise

        return jv_name
Ejemplo n.º 26
0
def get_outstanding_invoices(party_type,
                             party,
                             account,
                             condition=None,
                             limit=1000):
    outstanding_invoices = []
    precision = frappe.get_precision("Sales Invoice",
                                     "outstanding_amount") or 2

    if erpnext.get_party_account_type(party_type) == 'Receivable':
        dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
        payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
    else:
        dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
        payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"

    invoice = 'Sales Invoice' if erpnext.get_party_account_type(
        party_type) == 'Receivable' else 'Purchase Invoice'
    held_invoices = get_held_invoices(party_type, party)
    limit_cond = "limit %s" % (limit or 1000)

    invoice_list = frappe.db.sql("""
		select
			voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
		from
			`tabGL Entry`
		where
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			{condition}
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		order by posting_date, name {limit_cond}""".format(dr_or_cr=dr_or_cr,
                                                     invoice=invoice,
                                                     condition=condition or "",
                                                     limit_cond=limit_cond),
                                 {
                                     "party_type": party_type,
                                     "party": party,
                                     "account": account,
                                 },
                                 as_dict=True)

    payment_entries = frappe.db.sql("""
		select against_voucher_type, against_voucher,
			ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
		from `tabGL Entry`
		where party_type = %(party_type)s and party = %(party)s
			and account = %(account)s
			and {payment_dr_or_cr} > 0
			and against_voucher is not null and against_voucher != ''
		group by against_voucher_type, against_voucher
	""".format(payment_dr_or_cr=payment_dr_or_cr), {
        "party_type": party_type,
        "party": party,
        "account": account,
    },
                                    as_dict=True)

    pe_map = frappe._dict()
    for d in payment_entries:
        pe_map.setdefault((d.against_voucher_type, d.against_voucher),
                          d.payment_amount)

    for d in invoice_list:
        payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
        outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
        if outstanding_amount > 0.5 / (10**precision):
            if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
                due_date = frappe.db.get_value(
                    d.voucher_type, d.voucher_no,
                    "posting_date" if party_type == "Employee" else "due_date")

                outstanding_invoices.append(
                    frappe._dict({
                        'voucher_no': d.voucher_no,
                        'voucher_type': d.voucher_type,
                        'posting_date': d.posting_date,
                        'invoice_amount': flt(d.invoice_amount),
                        'payment_amount': payment_amount,
                        'outstanding_amount': outstanding_amount,
                        'due_date': due_date
                    }))

    outstanding_invoices = sorted(
        outstanding_invoices,
        key=lambda k: k['due_date'] or getdate(nowdate()))
    return outstanding_invoices
Ejemplo n.º 27
0
	def make_accrual_jv_entry(self):
		scc=1 if self.currency==frappe.db.get_value("Company", {'name':self.company}, "default_currency") else 0
		self.check_permission('write')
		deductions = self.get_salary_component_total(component_type = "deductions") or {}
		default_payroll_payable_account = self.get_default_payroll_payable_account()
		jv_name = ""
		precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
		##precision = 20
		##frappe.msgprint(_("precision= {0}")
		##	.format(precision))

		ss_list = self.get_sal_slip_list(ss_status=1)
		journal_entry = frappe.new_doc('Journal Entry')
		journal_entry.voucher_type = 'Journal Entry'
		journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
			.format(self.start_date, self.end_date)
		journal_entry.company = self.company
		journal_entry.posting_date = self.posting_date
		accounts = []
		earn=0.0
		ded=0.0
		loa=0.0
		pay=0.0
		paycc=0.0

		for ss in ss_list:
			designation=frappe.get_cached_value('Employee',{"name": ss[1]},  "designation")
			earnings = self.get_salary_component_total(component_type = "earnings",employee=ss[1]) or {}
			# Earnings
			for ear in sorted(earnings):
				accounts.append({
						"account": earnings[ear].get('account'),
						"user_remark": _('{0} - {1}').format(designation,earnings[ear].get('scd')),
						"debit_in_account_currency": flt(earnings[ear].get('amount'), precision),
						"debit": flt(((flt(earnings[ear].get('amount'), precision))*(1 if scc else self.conversion_rate)), precision),
						"conversion_rate": 1 if scc else self.conversion_rate,
						"cost_center": earnings[ear].get('cost_center',self.cost_center),
						"project": earnings[ear].get('project',self.project),
						
						"project_activities": earnings[ear].get('project_activities',self.project_activities),
						"customer": earnings[ear].get('customer',self.customer)
					})
				earn+=flt(((flt(earnings[ear].get('amount'), precision))*(1 if scc else self.conversion_rate)), precision)
			# Loan
			loan_details = self.get_loan_details(employee=ss[1])
			for data in loan_details:
				accounts.append({
					"account": data.loan_account,
					"credit_in_account_currency": data.principal_amount,
					#"credit": flt(data.principal_amount if scc else (data.principal_amount*self.conversion_rate),precision),
					"conversion_rate": 1 if scc else self.conversion_rate,
					"party_type": "Employee",
					"party": data.employee,
					"cost_center": self.cost_center,
					"project": self.project,
					
					"project_activities": self.project_activities,
					"customer": self.customer

				})
				loa+=flt(data.principal_amount if scc else (data.principal_amount*self.cr),precision)

				if data.interest_amount and not data.interest_income_account:
					frappe.throw(_("Select interest income account in loan {0}").format(data.loan))

				if data.interest_income_account and data.interest_amount:
					accounts.append({
						"account": data.interest_income_account,
						"credit_in_account_currency": data.interest_amount,
						"party_type": "Employee",
						"party": data.employee
					})
			# Payroll Payable amount
			if self.is_payable==1:
				accounts.append({
					"account": default_payroll_payable_account,
					"credit_in_account_currency": ss[2],
					"credit": flt(ss[2] if scc else (ss[2]*self.conversion_rate),precision),
					"conversion_rate": 1 if scc else self.conversion_rate,
					"party_type": "Employee",
					"party": ss[1],
					"cost_center": self.cost_center,
					"project": self.project,
					
					"project_activities": self.project_activities,
					"customer": self.customer
				})
			pay+=flt(ss[2], precision)
			paycc+=flt(ss[2] if scc else (ss[2]*self.conversion_rate),precision)


		# Deductions
		if deductions:
			for dede in deductions:
				accounts.append({
						"account": deductions[dede].get('account'),
						"user_remark": _('{0}').format(deductions[dede].get('scd')),
						"credit_in_account_currency": flt(deductions[dede].get('amount'), precision),
						"credit": flt((flt(deductions[dede].get('amount'), precision)*(1 if scc else self.conversion_rate)),precision),
						"conversion_rate": 1 if scc else self.conversion_rate,
						"cost_center": self.cost_center,
						"project": self.project,
						
						"project_activities": self.project_activities,
						"customer": self.customer
					})
				##ded+=flt(deductions[dede].get('amount') if scc else (deductions[dede].get('amount')*self.conversion_rate),precision)
				ded+=flt((flt(deductions[dede].get('amount'), precision)*(1 if scc else self.conversion_rate)),precision)


		# Payroll amount
		if self.is_payable==0:
			accounts.append({
				"account": default_payroll_payable_account,
				"credit_in_account_currency": flt(pay,precision),
				"credit": flt((flt(pay,precision)*(1 if scc else self.conversion_rate)),precision),
				"conversion_rate": 1 if scc else self.conversion_rate,
				"cost_center": self.cost_center,
				"project": self.project,
				
				"project_activities": self.project_activities,
				"customer": self.customer
			})
			paycc=flt((flt(pay,precision)*(1 if scc else self.conversion_rate)),precision)
		# Writeoff
		if earn!=(loa+paycc+ded):
			##frappe.msgprint(_("Not Equal = {0}").format(flt((earn-(loa+paycc+ded)), precision)))
			accounts.append({
				"account": self.get_default_round_off_account(),
				"credit_in_account_currency": flt(self.difference, precision),
				#"credit": (earn-(loa+paycc+ded)),
				"conversion_rate": 1,
				"cost_center": self.cost_center,
				"project": self.project,
				
				"project_activities": self.project_activities,
				"customer": self.customer
			})

		if not accounts:
			frappe.msgprint(_("There is no Submitted Salary Slip or may be its Acrrualed")
				.format(earn,ded,loa,pay))
		#frappe.msgprint(_("earn={0},ded={1},loa={2},pay={3},diff={4}")
		#	.format(flt(earn, precision),flt(ded, precision),flt(loa, precision),flt(paycc, precision),flt((earn-(loa+paycc+ded)), precision)))

		journal_entry.set("accounts", accounts)
		journal_entry.title = default_payroll_payable_account

		journal_entry.save()
		self.accrual_jv=journal_entry.name
		self.save()
		try:
			journal_entry.submit()
			jv_name = journal_entry.name
			self.update_salary_slip_status(jv_name = jv_name)

		except Exception as e:
			frappe.msgprint(e)

		frappe.msgprint(_("Journal Entry submitted for Payroll Entry period from {0} to {1}")
			.format(self.start_date, self.end_date))