def validate_filters(filters): ''' Validate if dates are properly set and lie in the same fiscal year''' if filters.from_date > filters.to_date: dataent.throw(_("From Date must be before To Date")) from_year = get_fiscal_year(filters.from_date)[0] to_year = get_fiscal_year(filters.to_date)[0] if from_year != to_year: dataent.throw(_("From Date and To Date lie in different Fiscal Year")) filters["fiscal_year"] = from_year
def set_tax_withholding_category(company): accounts = [] abbr = dataent.get_value("Company", company, "abbr") tds_account = dataent.get_value("Account", 'TDS Payable - {0}'.format(abbr), 'name') if company and tds_account: accounts = [dict(company=company, account=tds_account)] fiscal_year = get_fiscal_year(today(), company=company)[0] docs = get_tds_details(accounts, fiscal_year) for d in docs: try: doc = dataent.get_doc(d) doc.flags.ignore_permissions = True doc.flags.ignore_mandatory = True doc.insert() except dataent.DuplicateEntryError: doc = dataent.get_doc("Tax Withholding Category", d.get("name")) doc.append("accounts", accounts[0]) # if fiscal year don't match with any of the already entered data, append rate row fy_exist = [k for k in doc.get('rates') if k.get('fiscal_year')==fiscal_year] if not fy_exist: doc.append("rates", d.get('rates')[0]) doc.save()
def make_holiday(holiday_list_name): if not dataent.db.exists('Holiday List', holiday_list_name): current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True) dt = getdate(nowdate()) new_year = dt + relativedelta(month=1, day=1, year=dt.year) republic_day = dt + relativedelta(month=1, day=26, year=dt.year) test_holiday = dt + relativedelta(month=2, day=2, year=dt.year) dataent.get_doc({ 'doctype': 'Holiday List', 'from_date': current_fiscal_year.year_start_date, 'to_date': current_fiscal_year.year_end_date, 'holiday_list_name': holiday_list_name, 'holidays': [{ 'holiday_date': new_year, 'description': 'New Year' }, { 'holiday_date': republic_day, 'description': 'Republic Day' }, { 'holiday_date': test_holiday, 'description': 'Test Holiday' }] }).insert() return holiday_list_name
def get_start_end_dates(payroll_frequency, start_date=None, company=None): '''Returns dict of start and end dates for given payroll frequency based on start_date''' if payroll_frequency == "Monthly" or payroll_frequency == "Bimonthly" or payroll_frequency == "": fiscal_year = get_fiscal_year(start_date, company=company)[0] month = "%02d" % getdate(start_date).month m = get_month_details(fiscal_year, month) if payroll_frequency == "Bimonthly": if getdate(start_date).day <= 15: start_date = m['month_start_date'] end_date = m['month_mid_end_date'] else: start_date = m['month_mid_start_date'] end_date = m['month_end_date'] else: start_date = m['month_start_date'] end_date = m['month_end_date'] if payroll_frequency == "Weekly": end_date = add_days(start_date, 6) if payroll_frequency == "Fortnightly": end_date = add_days(start_date, 13) if payroll_frequency == "Daily": end_date = start_date return dataent._dict({'start_date': start_date, 'end_date': end_date})
def get_achieved_details(filters, territory, item_groups): start_date, end_date = get_fiscal_year( fiscal_year=filters["fiscal_year"])[1:] lft, rgt = dataent.db.get_value("Territory", territory, ["lft", "rgt"]) item_details = dataent.db.sql(""" select soi.item_code, sum(soi.stock_qty) as qty, sum(soi.base_net_amount) as amount, MONTHNAME(so.transaction_date) as month_name from `tabSales Order Item` soi, `tabSales Order` so where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and so.transaction_date<=%s and exists(select name from `tabTerritory` where lft >=%s and rgt <= %s and name=so.territory) group by month_name, item_code """, (start_date, end_date, lft, rgt), as_dict=1) item_actual_details = {} for d in item_details: item_group = item_groups[d.item_code] item_actual_details.setdefault(item_group, dataent._dict())\ .setdefault(d.month_name, dataent._dict({ "quantity": 0, "amount": 0 })) value_dict = item_actual_details[item_group][d.month_name] value_dict.quantity += flt(d.qty) value_dict.amount += flt(d.amount) return item_actual_details
def validate_supplier_invoice(self): if self.bill_date: if getdate(self.bill_date) > getdate(self.posting_date): dataent.throw(_("Supplier Invoice Date cannot be greater than Posting Date")) if self.bill_no: if cint(dataent.db.get_single_value("Accounts Settings", "check_supplier_invoice_uniqueness")): fiscal_year = get_fiscal_year(self.posting_date, company=self.company, as_dict=True) pi = dataent.db.sql('''select name from `tabPurchase Invoice` where bill_no = %(bill_no)s and supplier = %(supplier)s and name != %(name)s and docstatus < 2 and posting_date between %(year_start_date)s and %(year_end_date)s''', { "bill_no": self.bill_no, "supplier": self.supplier, "name": self.name, "year_start_date": fiscal_year.year_start_date, "year_end_date": fiscal_year.year_end_date }) if pi: pi = pi[0][0] dataent.throw(_("Supplier Invoice No exists in Purchase Invoice {0}".format(pi)))
def test_payroll_frequency(self): fiscal_year = get_fiscal_year(nowdate(), company=epaas.get_default_company())[0] month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) for payroll_frequency in [ "Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily" ]: make_employee(payroll_frequency + "*****@*****.**") ss = make_employee_salary_slip( payroll_frequency + "*****@*****.**", payroll_frequency) if payroll_frequency == "Monthly": self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Bimonthly": if getdate(ss.start_date).day <= 15: self.assertEqual(ss.end_date, m['month_mid_end_date']) else: self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Fortnightly": self.assertEqual(ss.end_date, add_days(nowdate(), 13)) elif payroll_frequency == "Weekly": self.assertEqual(ss.end_date, add_days(nowdate(), 6)) elif payroll_frequency == "Daily": self.assertEqual(ss.end_date, nowdate())
def get_period_date_ranges(self): from dateutil.relativedelta import relativedelta, MO from_date, to_date = getdate(self.filters.from_date), getdate(self.filters.to_date) increment = { "Monthly": 1, "Quarterly": 3, "Half-Yearly": 6, "Yearly": 12 }.get(self.filters.range, 1) if self.filters.range in ['Monthly', 'Quarterly']: from_date = from_date.replace(day = 1) elif self.filters.range == "Yearly": from_date = get_fiscal_year(from_date)[1] else: from_date = from_date + relativedelta(from_date, weekday=MO(-1)) self.periodic_daterange = [] for dummy in range(1, 53): if self.filters.range == "Weekly": period_end_date = add_days(from_date, 6) else: period_end_date = add_to_date(from_date, months=increment, days=-1) if period_end_date > to_date: period_end_date = to_date self.periodic_daterange.append(period_end_date) from_date = add_days(period_end_date, 1) if period_end_date == to_date: break
def validate_and_set_fiscal_year(self): if not self.fiscal_year: self.fiscal_year = get_fiscal_year(self.posting_date, company=self.company)[0] else: from epaas.accounts.utils import validate_fiscal_year validate_fiscal_year(self.posting_date, self.fiscal_year, self.company, self.meta.get_label("posting_date"), self)
def get_start_date(period, accumulated_values, company): if not accumulated_values and period.get('from_date'): return period['from_date'] start_date = period["year_start_date"] if accumulated_values: start_date = get_fiscal_year(period.to_date, company=company)[1] return start_date
def get_period(self, posting_date): if self.filters.range == 'Weekly': period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(posting_date.year) elif self.filters.range == 'Monthly': period = str(self.months[posting_date.month - 1]) + " " + str(posting_date.year) elif self.filters.range == 'Quarterly': period = "Quarter " + str(((posting_date.month-1)//3)+1) +" " + str(posting_date.year) else: year = get_fiscal_year(posting_date, company=self.filters.company) period = str(year[0]) return period
def test_closing_entry(self): year_start_date = get_fiscal_year(today(), company="_Test Company")[1] make_journal_entry("_Test Bank - _TC", "Sales - _TC", 400, "_Test Cost Center - _TC", posting_date=now(), submit=True) make_journal_entry("_Test Account Cost for Goods Sold - _TC", "_Test Bank - _TC", 600, "_Test Cost Center - _TC", posting_date=now(), submit=True) random_expense_account = dataent.db.sql(""" select t1.account, sum(t1.debit) - sum(t1.credit) as balance, sum(t1.debit_in_account_currency) - sum(t1.credit_in_account_currency) \ as balance_in_account_currency from `tabGL Entry` t1, `tabAccount` t2 where t1.account = t2.name and t2.root_type = 'Expense' and t2.docstatus < 2 and t2.company = '_Test Company' and t1.posting_date between %s and %s group by t1.account having sum(t1.debit) > sum(t1.credit) limit 1""", (year_start_date, today()), as_dict=True) profit_or_loss = dataent.db.sql("""select sum(t1.debit) - sum(t1.credit) as balance from `tabGL Entry` t1, `tabAccount` t2 where t1.account = t2.name and t2.report_type = 'Profit and Loss' and t2.docstatus < 2 and t2.company = '_Test Company' and t1.posting_date between %s and %s""", (year_start_date, today())) profit_or_loss = flt(profit_or_loss[0][0]) if profit_or_loss else 0 pcv = self.make_period_closing_voucher() # Check value for closing account gle_amount_for_closing_account = dataent.db.sql("""select debit - credit from `tabGL Entry` where voucher_type='Period Closing Voucher' and voucher_no=%s and account = '_Test Account Reserves and Surplus - _TC'""", pcv.name) gle_amount_for_closing_account = flt(gle_amount_for_closing_account[0][0]) \ if gle_amount_for_closing_account else 0 self.assertEqual(gle_amount_for_closing_account, profit_or_loss) if random_expense_account: # Check posted value for teh above random_expense_account gle_for_random_expense_account = dataent.db.sql(""" select sum(debit - credit) as amount, sum(debit_in_account_currency - credit_in_account_currency) as amount_in_account_currency from `tabGL Entry` where voucher_type='Period Closing Voucher' and voucher_no=%s and account =%s""", (pcv.name, random_expense_account[0].account), as_dict=True) self.assertEqual(gle_for_random_expense_account[0].amount, -1*random_expense_account[0].balance) self.assertEqual(gle_for_random_expense_account[0].amount_in_account_currency, -1*random_expense_account[0].balance_in_account_currency)
def get_period(posting_date, filters): months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"] if filters.range == 'Weekly': period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(posting_date.year) elif filters.range == 'Monthly': period = str(months[posting_date.month - 1]) + " " + str(posting_date.year) elif filters.range == 'Quarterly': period = "Quarter " + str(((posting_date.month-1)//3)+1) +" " + str(posting_date.year) else: year = get_fiscal_year(posting_date, company=filters.company) period = str(year[2]) return period
def make_holiday_list(self): fiscal_year = get_fiscal_year(nowdate(), company=epaas.get_default_company()) if not dataent.db.get_value("Holiday List", "Salary Slip Test Holiday List"): holiday_list = dataent.get_doc({ "doctype": "Holiday List", "holiday_list_name": "Salary Slip Test Holiday List", "from_date": fiscal_year[1], "to_date": fiscal_year[2], "weekly_off": "Sunday" }).insert() holiday_list.get_weekly_off_dates() holiday_list.save()
def make_period_closing_voucher(self): pcv = dataent.get_doc({ "doctype": "Period Closing Voucher", "closing_account_head": "_Test Account Reserves and Surplus - _TC", "company": "_Test Company", "fiscal_year": get_fiscal_year(today(), company="_Test Company")[0], "posting_date": today(), "cost_center": "_Test Cost Center - _TC", "remarks": "test" }) pcv.insert() pcv.submit() return pcv
def get_count_for_period(account, fieldname, from_date, to_date): count = 0.0 count_on_to_date = get_count_on(account, fieldname, to_date) count_before_from_date = get_count_on(account, fieldname, from_date - timedelta(days=1)) fy_start_date = get_fiscal_year(to_date)[1] if from_date == fy_start_date: count = count_on_to_date elif from_date > fy_start_date: count = count_on_to_date - count_before_from_date else: last_year_closing_count = get_count_on( account, fieldname, fy_start_date - timedelta(days=1)) count = count_on_to_date + (last_year_closing_count - count_before_from_date) return count
def execute(): try: year_start_date = get_fiscal_year(today())[1] except: return if year_start_date: items = dataent.db.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry` where ifnull(serial_no, '') != '' and actual_qty > 0 and incoming_rate=0""", as_dict=1) for d in items: try: update_entries_after({ "item_code": d.item_code, "warehouse": d.warehouse, "posting_date": year_start_date }, allow_zero_rate=True) except: pass
def validate_posting_date(self): from epaas.accounts.utils import get_fiscal_year, validate_fiscal_year validate_fiscal_year(self.posting_date, self.fiscal_year, self.company, label=_("Posting Date"), doc=self) self.year_start_date = get_fiscal_year(self.posting_date, self.fiscal_year, company=self.company)[1] pce = dataent.db.sql( """select name from `tabPeriod Closing Voucher` where posting_date > %s and fiscal_year = %s and docstatus = 1""", (self.posting_date, self.fiscal_year)) if pce and pce[0][0]: dataent.throw( _("Another Period Closing Entry {0} has been made after {1}"). format(pce[0][0], self.posting_date))
def get_sl_entries(self, d, args): sl_dict = dataent._dict({ "item_code": d.get("item_code", None), "warehouse": d.get("warehouse", None), "posting_date": self.posting_date, "posting_time": self.posting_time, 'fiscal_year': get_fiscal_year(self.posting_date, company=self.company)[0], "voucher_type": self.doctype, "voucher_no": self.name, "voucher_detail_no": d.name, "actual_qty": (self.docstatus == 1 and 1 or -1) * flt(d.get("stock_qty")), "stock_uom": dataent.db.get_value("Item", args.get("item_code") or d.get("item_code"), "stock_uom"), "incoming_rate": 0, "company": self.company, "batch_no": cstr(d.get("batch_no")).strip(), "serial_no": d.get("serial_no"), "project": d.get("project") or self.get('project'), "is_cancelled": self.docstatus == 2 and "Yes" or "No" }) sl_dict.update(args) return sl_dict
def get_achieved_details(filters, sales_person, all_sales_persons, target_item_group, item_groups): start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:] item_details = dataent.db.sql(""" SELECT st.sales_person, MONTHNAME(so.transaction_date) as month_name, CASE WHEN so.status = "Closed" THEN sum(soi.delivered_qty * soi.conversion_factor * (st.allocated_percentage/100)) ELSE sum(soi.stock_qty * (st.allocated_percentage/100)) END as qty, CASE WHEN so.status = "Closed" THEN sum(soi.delivered_qty * soi.conversion_factor * soi.base_net_rate * (st.allocated_percentage/100)) ELSE sum(soi.base_net_amount * (st.allocated_percentage/100)) END as amount from `tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st where soi.parent=so.name and so.docstatus=1 and st.parent=so.name and so.transaction_date>=%s and so.transaction_date<=%s and exists(SELECT name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person) and exists(SELECT name from `tabItem Group` where lft >= %s and rgt <= %s and name=soi.item_group) group by sales_person, month_name """, (start_date, end_date, all_sales_persons[sales_person].lft, all_sales_persons[sales_person].rgt, item_groups[target_item_group].lft, item_groups[target_item_group].rgt), as_dict=1) actual_details = {} for d in item_details: actual_details.setdefault(d.month_name, dataent._dict({ "quantity" : 0, "amount" : 0 })) value_dict = actual_details[d.month_name] value_dict.quantity += flt(d.qty) value_dict.amount += flt(d.amount) return actual_details
def get_incomes_expenses_for_period(account, from_date, to_date): """Get amounts for current and past periods""" val = 0.0 balance_on_to_date = get_balance_on(account, date=to_date) balance_before_from_date = get_balance_on(account, date=from_date - timedelta(days=1)) fy_start_date = get_fiscal_year(to_date)[1] if from_date == fy_start_date: val = balance_on_to_date elif from_date > fy_start_date: val = balance_on_to_date - balance_before_from_date else: last_year_closing_balance = get_balance_on(account, date=fy_start_date - timedelta(days=1)) print(fy_start_date - timedelta(days=1), last_year_closing_balance) val = balance_on_to_date + (last_year_closing_balance - balance_before_from_date) return val
def get_dashboard_info(party_type, party, loyalty_program=None): current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True) doctype = "Sales Invoice" if party_type == "Customer" else "Purchase Invoice" companies = dataent.get_all(doctype, filters={ 'docstatus': 1, party_type.lower(): party }, distinct=1, fields=['company']) company_wise_info = [] company_wise_grand_total = dataent.get_all( doctype, filters={ 'docstatus': 1, party_type.lower(): party, 'posting_date': ('between', [ current_fiscal_year.year_start_date, current_fiscal_year.year_end_date ]) }, group_by="company", fields=[ "company", "sum(grand_total) as grand_total", "sum(base_grand_total) as base_grand_total" ]) loyalty_point_details = [] if party_type == "Customer": loyalty_point_details = dataent._dict( dataent.get_all( "Loyalty Point Entry", filters={ 'customer': party, 'expiry_date': ('>=', getdate()), }, group_by="company", fields=["company", "sum(loyalty_points) as loyalty_points"], as_list=1)) company_wise_billing_this_year = dataent._dict() for d in company_wise_grand_total: company_wise_billing_this_year.setdefault( d.company, { "grand_total": d.grand_total, "base_grand_total": d.base_grand_total }) company_wise_total_unpaid = dataent._dict( dataent.db.sql( """ select company, sum(debit_in_account_currency) - sum(credit_in_account_currency) from `tabGL Entry` where party_type = %s and party=%s group by company""", (party_type, party))) for d in companies: company_default_currency = dataent.db.get_value( "Company", d.company, 'default_currency') party_account_currency = get_party_account_currency( party_type, party, d.company) if party_account_currency == company_default_currency: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("base_grand_total")) else: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("grand_total")) total_unpaid = flt(company_wise_total_unpaid.get(d.company)) if loyalty_point_details: loyalty_points = loyalty_point_details.get(d.company) info = {} info["billing_this_year"] = flt( billing_this_year) if billing_this_year else 0 info["currency"] = party_account_currency info["total_unpaid"] = flt(total_unpaid) if total_unpaid else 0 info["company"] = d.company if party_type == "Customer" and loyalty_point_details: info["loyalty_points"] = loyalty_points if party_type == "Supplier": info["total_unpaid"] = -1 * info["total_unpaid"] company_wise_info.append(info) return company_wise_info
def validate_and_set_fiscal_year(self): if not self.fiscal_year: self.fiscal_year = get_fiscal_year(self.posting_date, company=self.company)[0]
def validate_expense_against_budget(args): args = dataent._dict(args) if args.get('company') and not args.fiscal_year: args.fiscal_year = get_fiscal_year(args.get('posting_date'), company=args.get('company'))[0] dataent.flags.exception_approver_role = dataent.get_cached_value( 'Company', args.get('company'), 'exception_budget_approver_role') if not args.account: args.account = args.get("expense_account") if not (args.get('account') and args.get('cost_center')) and args.item_code: args.cost_center, args.account = get_item_details(args) if not (args.cost_center or args.project) and not args.account: return for budget_against in ['project', 'cost_center']: if (args.get(budget_against) and args.account and dataent.db.get_value("Account", { "name": args.account, "root_type": "Expense" })): if args.project and budget_against == 'project': condition = "and b.project='%s'" % dataent.db.escape( args.project) args.budget_against_field = "Project" elif args.cost_center and budget_against == 'cost_center': cc_lft, cc_rgt = dataent.db.get_value("Cost Center", args.cost_center, ["lft", "rgt"]) condition = """and exists(select name from `tabCost Center` where lft<=%s and rgt>=%s and name=b.cost_center)""" % (cc_lft, cc_rgt) args.budget_against_field = "Cost Center" args.budget_against = args.get(budget_against) budget_records = dataent.db.sql(""" select b.{budget_against_field} as budget_against, ba.budget_amount, b.monthly_distribution, ifnull(b.applicable_on_material_request, 0) as for_material_request, ifnull(applicable_on_purchase_order,0) as for_purchase_order, ifnull(applicable_on_booking_actual_expenses,0) as for_actual_expenses, b.action_if_annual_budget_exceeded, b.action_if_accumulated_monthly_budget_exceeded, b.action_if_annual_budget_exceeded_on_mr, b.action_if_accumulated_monthly_budget_exceeded_on_mr, b.action_if_annual_budget_exceeded_on_po, b.action_if_accumulated_monthly_budget_exceeded_on_po from `tabBudget` b, `tabBudget Account` ba where b.name=ba.parent and b.fiscal_year=%s and ba.account=%s and b.docstatus=1 {condition} """.format(condition=condition, budget_against_field=dataent.scrub( args.get("budget_against_field"))), (args.fiscal_year, args.account), as_dict=True) if budget_records: validate_budget_records(args, budget_records)
def get_period_list(from_fiscal_year, to_fiscal_year, periodicity, accumulated_values=False, company=None, reset_period_on_fy_change=True): """Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label} Periodicity can be (Yearly, Quarterly, Monthly)""" fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year) validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year) # start with first day, so as to avoid year to_dates like 2-April if ever they occur] year_start_date = getdate(fiscal_year.year_start_date) year_end_date = getdate(fiscal_year.year_end_date) months_to_add = { "Yearly": 12, "Half-Yearly": 6, "Quarterly": 3, "Monthly": 1 }[periodicity] period_list = [] start_date = year_start_date months = get_months(year_start_date, year_end_date) for i in range(months // months_to_add): period = dataent._dict({"from_date": start_date}) to_date = add_months(start_date, months_to_add) start_date = to_date if to_date == get_first_day(to_date): # if to_date is the first day, get the last day of previous month to_date = add_days(to_date, -1) if to_date <= year_end_date: # the normal case period.to_date = to_date else: # if a fiscal year ends before a 12 month period period.to_date = year_end_date period.to_date_fiscal_year = get_fiscal_year(period.to_date, company=company)[0] period.from_date_fiscal_year_start_date = get_fiscal_year( period.from_date, company=company)[1] period_list.append(period) if period.to_date == year_end_date: break # common processing for opts in period_list: key = opts["to_date"].strftime("%b_%Y").lower() if periodicity == "Monthly" and not accumulated_values: label = formatdate(opts["to_date"], "MMM YYYY") else: if not accumulated_values: label = get_label(periodicity, opts["from_date"], opts["to_date"]) else: if reset_period_on_fy_change: label = get_label(periodicity, opts.from_date_fiscal_year_start_date, opts["to_date"]) else: label = get_label(periodicity, period_list[0].from_date, opts["to_date"]) opts.update({ "key": key.replace(" ", "_").replace("-", "_"), "label": label, "year_start_date": year_start_date, "year_end_date": year_end_date }) return period_list