def validate_filters(filters):
''' Validate if dates are properly set and lie in the same fiscal year'''
if filters.from_date > filters.to_date:
frappe.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:
frappe.throw(_("From Date and To Date lie in different Fiscal Year"))
filters["fiscal_year"] = from_year
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 get_party_tax_withholding_details(ref_doc, tax_withholding_category=None):
pan_no = ''
suppliers = []
if not tax_withholding_category:
tax_withholding_category, pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, ['tax_withholding_category', 'pan'])
if not tax_withholding_category:
return
if not pan_no:
pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, 'pan')
# Get others suppliers with the same PAN No
if pan_no:
suppliers = [d.name for d in frappe.get_all('Supplier', fields=['name'], filters={'pan': pan_no})]
if not suppliers:
suppliers.append(ref_doc.supplier)
fy = get_fiscal_year(ref_doc.posting_date, company=ref_doc.company)
tax_details = get_tax_withholding_details(tax_withholding_category, fy[0], ref_doc.company)
if not tax_details:
frappe.throw(_('Please set associated account in Tax Withholding Category {0} against Company {1}')
.format(tax_withholding_category, ref_doc.company))
tds_amount = get_tds_amount(suppliers, ref_doc.net_total, ref_doc.company,
tax_details, fy, ref_doc.posting_date, pan_no)
tax_row = get_tax_row(tax_details, tds_amount)
return tax_row
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 frappe._dict({'start_date': start_date, 'end_date': end_date})
def test_payroll_frequency(self):
fiscal_year = get_fiscal_year(nowdate(),
company=erpbee.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,
payroll_frequency + "_Test 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 test_monthly_budget_crossed_for_mr(self):
budget = make_budget(applicable_on_material_request=1,
applicable_on_purchase_order=1, action_if_accumulated_monthly_budget_exceeded_on_mr="Stop",
budget_against="Cost Center")
fiscal_year = get_fiscal_year(nowdate())[0]
frappe.db.set_value("Budget", budget.name, "action_if_accumulated_monthly_budget_exceeded", "Stop")
frappe.db.set_value("Budget", budget.name, "fiscal_year", fiscal_year)
mr = frappe.get_doc({
"doctype": "Material Request",
"material_request_type": "Purchase",
"transaction_date": nowdate(),
"company": budget.company,
"items": [{
'item_code': '_Test Item',
'qty': 1,
'uom': "_Test UOM",
'warehouse': '_Test Warehouse - _TC',
'schedule_date': nowdate(),
'rate': 100000,
'expense_account': '_Test Account Cost for Goods Sold - _TC',
'cost_center': '_Test Cost Center - _TC'
}]
})
mr.set_missing_values()
self.assertRaises(BudgetError, mr.submit)
budget.load_from_db()
budget.cancel()
def set_total_expense_zero(posting_date, budget_against_field=None, budget_against_CC=None):
if budget_against_field == "project":
budget_against = "_Test Project"
else:
budget_against = budget_against_CC or "_Test Cost Center - _TC"
fiscal_year = get_fiscal_year(nowdate())[0]
args = frappe._dict({
"account": "_Test Account Cost for Goods Sold - _TC",
"cost_center": "_Test Cost Center - _TC",
"monthly_end_date": posting_date,
"company": "_Test Company",
"fiscal_year": fiscal_year,
"budget_against_field": budget_against_field,
})
if not args.get(budget_against_field):
args[budget_against_field] = budget_against
existing_expense = get_actual_expense(args)
if existing_expense:
if budget_against_field == "cost_center":
make_journal_entry("_Test Account Cost for Goods Sold - _TC",
"_Test Bank - _TC", -existing_expense, "_Test Cost Center - _TC", posting_date=nowdate(), submit=True)
elif budget_against_field == "project":
make_journal_entry("_Test Account Cost for Goods Sold - _TC",
"_Test Bank - _TC", -existing_expense, "_Test Cost Center - _TC", submit=True, project="_Test Project", posting_date=nowdate())
def make_holiday(holiday_list_name):
if not frappe.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)
frappe.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 set_tax_withholding_category(company):
accounts = []
abbr = frappe.get_value("Company", company, "abbr")
tds_account = frappe.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 = frappe.get_doc(d)
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
doc.insert()
except frappe.DuplicateEntryError:
doc = frappe.get_doc("Tax Withholding Category", d.get("name"))
if accounts:
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 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 erpbee.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 make_budget(**args):
args = frappe._dict(args)
budget_against=args.budget_against
cost_center=args.cost_center
fiscal_year = get_fiscal_year(nowdate())[0]
if budget_against == "Project":
project_name = "{0}%".format("_Test Project/" + fiscal_year)
budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", project_name)})
else:
cost_center_name = "{0}%".format(cost_center or "_Test Cost Center - _TC/" + fiscal_year)
budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", cost_center_name)})
for d in budget_list:
frappe.db.sql("delete from `tabBudget` where name = %(name)s", d)
frappe.db.sql("delete from `tabBudget Account` where parent = %(name)s", d)
budget = frappe.new_doc("Budget")
if budget_against == "Project":
budget.project = "_Test Project"
else:
budget.cost_center =cost_center or "_Test Cost Center - _TC"
monthly_distribution = frappe.get_doc("Monthly Distribution", "_Test Distribution")
monthly_distribution.fiscal_year = fiscal_year
budget.fiscal_year = fiscal_year
budget.monthly_distribution = "_Test Distribution"
budget.company = "_Test Company"
budget.applicable_on_booking_actual_expenses = 1
budget.action_if_annual_budget_exceeded = "Stop"
budget.action_if_accumulated_monthly_budget_exceeded = "Ignore"
budget.budget_against = budget_against
budget.append("accounts", {
"account": "_Test Account Cost for Goods Sold - _TC",
"budget_amount": 200000
})
if args.applicable_on_material_request:
budget.applicable_on_material_request = 1
budget.action_if_annual_budget_exceeded_on_mr = args.action_if_annual_budget_exceeded_on_mr or 'Warn'
budget.action_if_accumulated_monthly_budget_exceeded_on_mr = args.action_if_accumulated_monthly_budget_exceeded_on_mr or 'Warn'
if args.applicable_on_purchase_order:
budget.applicable_on_purchase_order = 1
budget.action_if_annual_budget_exceeded_on_po = args.action_if_annual_budget_exceeded_on_po or 'Warn'
budget.action_if_accumulated_monthly_budget_exceeded_on_po = args.action_if_accumulated_monthly_budget_exceeded_on_po or 'Warn'
budget.insert()
budget.submit()
return budget
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 download_datev_csv(filters):
"""
Provide accounting entries for download in DATEV format.
Validate the filters, get the data, produce the CSV file and provide it for
download. Can be called like this:
GET /api/method/erpbee.regional.report.datev.datev.download_datev_csv
Arguments / Params:
filters -- dict of filters to be passed to the sql query
"""
if isinstance(filters, string_types):
filters = json.loads(filters)
validate(filters)
company = filters.get('company')
fiscal_year = get_fiscal_year(date=filters.get('from_date'), company=company)
filters['fiscal_year_start'] = fiscal_year[1]
# set chart of accounts used
coa = frappe.get_value('Company', company, 'chart_of_accounts')
filters['skr'] = '04' if 'SKR04' in coa else ('03' if 'SKR03' in coa else '')
filters['account_number_length'] = frappe.get_value('DATEV Settings', company, 'account_number_length')
transactions = get_transactions(filters)
account_names = get_account_names(filters)
customers = get_customers(filters)
suppliers = get_suppliers(filters)
zip_name = '{} DATEV.zip'.format(frappe.utils.datetime.date.today())
zip_and_download(zip_name, [
{
'file_name': 'EXTF_Buchungsstapel.csv',
'csv_data': get_datev_csv(transactions, filters, csv_class=Transactions)
},
{
'file_name': 'EXTF_Kontenbeschriftungen.csv',
'csv_data': get_datev_csv(account_names, filters, csv_class=AccountNames)
},
{
'file_name': 'EXTF_Kunden.csv',
'csv_data': get_datev_csv(customers, filters, csv_class=DebtorsCreditors)
},
{
'file_name': 'EXTF_Lieferanten.csv',
'csv_data': get_datev_csv(suppliers, filters, csv_class=DebtorsCreditors)
},
])
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():
fiscal_year = get_fiscal_year(nowdate(),
company=erpbee.get_default_company())
if not frappe.db.get_value("Holiday List",
"Salary Slip Test Holiday List"):
holiday_list = frappe.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 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 create_tax_with_holding_category():
fiscal_year = get_fiscal_year(today(), company="_Test Company")[0]
# Cummulative thresold
if not frappe.db.exists("Tax Withholding Category",
"Cumulative Threshold TDS"):
frappe.get_doc({
"doctype":
"Tax Withholding Category",
"name":
"Cumulative Threshold TDS",
"category_name":
"10% TDS",
"rates": [{
'fiscal_year': fiscal_year,
'tax_withholding_rate': 10,
'single_threshold': 0,
'cumulative_threshold': 30000.00
}],
"accounts": [{
'company': '_Test Company',
'account': 'TDS - _TC'
}]
}).insert()
# Single thresold
if not frappe.db.exists("Tax Withholding Category",
"Single Threshold TDS"):
frappe.get_doc({
"doctype":
"Tax Withholding Category",
"name":
"Single Threshold TDS",
"category_name":
"10% TDS",
"rates": [{
'fiscal_year': fiscal_year,
'tax_withholding_rate': 10,
'single_threshold': 20000.00,
'cumulative_threshold': 0
}],
"accounts": [{
'company': '_Test Company',
'account': 'TDS - _TC'
}]
}).insert()
def get_period(self, appointment_date):
if self.filters.range == 'Weekly':
period = 'Week ' + str(appointment_date.isocalendar()[1])
elif self.filters.range == 'Monthly':
period = str(self.months[appointment_date.month - 1])
elif self.filters.range == 'Quarterly':
period = 'Quarter ' + str(((appointment_date.month - 1) // 3) + 1)
else:
year = get_fiscal_year(appointment_date,
company=self.filters.company)
period = str(year[0])
if getdate(self.filters.from_date).year != getdate(
self.filters.to_date).year:
period += ' ' + str(appointment_date.year)
return period
def test_monthly_budget_crossed_for_po(self):
budget = make_budget(applicable_on_purchase_order=1,
action_if_accumulated_monthly_budget_exceeded_on_po="Stop", budget_against="Cost Center")
fiscal_year = get_fiscal_year(nowdate())[0]
frappe.db.set_value("Budget", budget.name, "action_if_accumulated_monthly_budget_exceeded", "Stop")
frappe.db.set_value("Budget", budget.name, "fiscal_year", fiscal_year)
po = create_purchase_order(transaction_date=nowdate(), do_not_submit=True)
po.set_missing_values()
self.assertRaises(BudgetError, po.submit)
budget.load_from_db()
budget.cancel()
po.cancel()
def validate(self):
if getdate(self.valid_upto) < getdate(self.valid_from):
frappe.throw(_("Valid Upto date cannot be before Valid From date"))
fiscal_year = get_fiscal_year(fiscal_year=self.fiscal_year,
as_dict=True)
if not (fiscal_year.year_start_date <= getdate(self.valid_from) \
<= fiscal_year.year_end_date):
frappe.throw(
_("Valid From date not in Fiscal Year {0}").format(
frappe.bold(self.fiscal_year)))
if not (fiscal_year.year_start_date <= getdate(self.valid_upto) \
<= fiscal_year.year_end_date):
frappe.throw(
_("Valid Upto date not in Fiscal Year {0}").format(
frappe.bold(self.fiscal_year)))
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_actual_data(filters, item_groups, sales_users_or_territory_data,
date_field, sales_field):
fiscal_year = get_fiscal_year(fiscal_year=filters.get("fiscal_year"),
as_dict=1)
dates = [fiscal_year.year_start_date, fiscal_year.year_end_date]
select_field = "`tab{0}`.{1}".format(filters.get("doctype"), sales_field)
child_table = "`tab{0}`".format(filters.get("doctype") + ' Item')
if sales_field == 'sales_person':
select_field = "`tabSales Team`.sales_person"
child_table = "`tab{0}`, `tabSales Team`".format(
filters.get("doctype") + ' Item')
cond = """`tabSales Team`.parent = `tab{0}`.name and
`tabSales Team`.sales_person in ({1}) """.format(
filters.get("doctype"),
','.join(['%s'] * len(sales_users_or_territory_data)))
else:
cond = "`tab{0}`.{1} in ({2})".format(
filters.get("doctype"), sales_field,
','.join(['%s'] * len(sales_users_or_territory_data)))
return frappe.db.sql(""" SELECT `tab{child_doc}`.item_group,
`tab{child_doc}`.stock_qty, `tab{child_doc}`.base_net_amount,
{select_field}, `tab{parent_doc}`.{date_field}
FROM `tab{parent_doc}`, {child_table}
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name
and `tab{parent_doc}`.docstatus = 1 and {cond}
and `tab{child_doc}`.item_group in ({item_groups})
and `tab{parent_doc}`.{date_field} between %s and %s""".format(
cond=cond,
date_field=date_field,
select_field=select_field,
child_table=child_table,
parent_doc=filters.get("doctype"),
child_doc=filters.get("doctype") + ' Item',
item_groups=','.join(['%s'] * len(item_groups))),
tuple(sales_users_or_territory_data + item_groups +
dates),
as_dict=1)
def make_period_closing_voucher(self):
pcv = frappe.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 validate_posting_date(self):
from erpbee.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 = frappe.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]:
frappe.throw(
_("Another Period Closing Entry {0} has been made after {1}").
format(pce[0][0], self.posting_date))
def execute(filters=None):
filters = filters if isinstance(filters, _dict) else _dict(filters)
if not filters:
filters.setdefault('fiscal_year', get_fiscal_year(nowdate())[0])
filters.setdefault('company', frappe.db.get_default("company"))
region = frappe.db.get_value("Company",
fieldname=["country"],
filters={"name": filters.company})
if region != 'United States':
return [], []
data = []
columns = get_columns()
data = frappe.db.sql("""
SELECT
s.supplier_group as "supplier_group",
gl.party AS "supplier",
s.tax_id as "tax_id",
SUM(gl.debit_in_account_currency) AS "payments"
FROM
`tabGL Entry` gl INNER JOIN `tabSupplier` s
WHERE
s.name = gl.party
AND s.irs_1099 = 1
AND gl.fiscal_year = %(fiscal_year)s
AND gl.party_type = "Supplier"
GROUP BY
gl.party
ORDER BY
gl.party DESC""", {
"fiscal_year": filters.fiscal_year,
"supplier_group": filters.supplier_group,
"company": filters.company
},
as_dict=True)
return columns, data
def get_sl_entries(self, d, args):
sl_dict = frappe._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": frappe.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": 1 if self.docstatus==2 else 0
})
sl_dict.update(args)
return sl_dict
def validate_fiscal_year(from_date, to_date, company):
from_fiscal_year = get_fiscal_year(date=from_date, company=company)
to_fiscal_year = get_fiscal_year(date=to_date, company=company)
if from_fiscal_year != to_fiscal_year:
frappe.throw(_('Dates {} and {} are not in the same fiscal year.').format(from_date, to_date))
def validate_expense_against_budget(args):
args = frappe._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]
frappe.flags.exception_approver_role = frappe.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.account:
return
for budget_against in ['project', 'cost_center'
] + get_accounting_dimensions():
if (args.get(budget_against) and args.account
and frappe.db.get_value("Account", {
"name": args.account,
"root_type": "Expense"
})):
doctype = frappe.unscrub(budget_against)
if frappe.get_cached_value('DocType', doctype, 'is_tree'):
lft, rgt = frappe.db.get_value(doctype,
args.get(budget_against),
["lft", "rgt"])
condition = """and exists(select name from `tab%s`
where lft<=%s and rgt>=%s and name=b.%s)""" % (
doctype, lft, rgt, budget_against) #nosec
args.is_tree = True
else:
condition = "and b.%s=%s" % (
budget_against, frappe.db.escape(args.get(budget_against)))
args.is_tree = False
args.budget_against_field = budget_against
args.budget_against_doctype = doctype
budget_records = frappe.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=budget_against),
(args.fiscal_year, args.account),
as_dict=True) #nosec
if budget_records:
validate_budget_records(args, budget_records)
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 = frappe.get_all(doctype,
filters={
'docstatus': 1,
party_type.lower(): party
},
distinct=1,
fields=['company'])
company_wise_info = []
company_wise_grand_total = frappe.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 = frappe._dict(
frappe.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 = frappe._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 = frappe._dict(
frappe.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 = frappe.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
