def get_data():
fiscal_year = _get_fiscal_year(nowdate())
if not fiscal_year:
return frappe._dict()
year_start_date = get_date_str(fiscal_year.get('year_start_date'))
year_end_date = get_date_str(fiscal_year.get('year_end_date'))
return frappe._dict({
"dashboards": get_dashboards(),
"charts": get_charts(fiscal_year, year_start_date, year_end_date),
"number_cards": get_number_cards(fiscal_year, year_start_date, year_end_date),
})
def make_opening_entry(pos_profile,date_time=None,execute_previous_entry=False):
check_open_entry = frappe.db.sql("""select * from `tabPOS Opening Entry` where status = 'Open' and docstatus = 1 and pos_profile = %s and posting_date = %s""", (pos_profile,nowdate()))
if not check_open_entry or execute_previous_entry:
pos_profile_user = frappe.get_list("POS Profile User", filters={"default": 1, "parent": pos_profile}, fields=["user"], limit=1)[0].user
profile_doc = frappe.get_doc("POS Profile", pos_profile)
pos_o_entry = frappe.new_doc("POS Opening Entry")
validate_open_entry_date_time = frappe.db.sql("""select * from `tabPOS Opening Entry` where docstatus = 1 and pos_profile = %s and posting_date = %s""", (pos_profile, nowdate()))
if date_time:
pos_o_entry.period_start_date = date_time
pos_o_entry.posting_date = get_date_str(str(date_time))
elif not validate_open_entry_date_time:
date_str = str(nowdate()) + " 00:00:00"
p_date = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
pos_o_entry.period_start_date = p_date
pos_o_entry.posting_date = nowdate()
else:
pos_o_entry.period_start_date = now_datetime()
pos_o_entry.posting_date = nowdate()
pos_o_entry.user = pos_profile_user
pos_o_entry.company = profile_doc.company
pos_o_entry.pos_profile = pos_profile
for account in profile_doc.payments:
pos_o_entry.append("balance_details",{
"mode_of_payment": account.mode_of_payment,
"opening_amount": 0
})
pos_o_entry.insert(ignore_permissions=True)
pos_o_entry.validate()
pos_o_entry.submit()
return pos_o_entry
def check_before_exe_time_pos_invoices(last_execution_time=None):
time = get_time(str(last_execution_time))
date = get_date_str(str(last_execution_time))
data = frappe.db.sql("""
select * from `tabPOS Invoice`
where docstatus = 1 and posting_date >= %s and posting_time >= %s and ifnull(consolidated_invoice,'') = ''
order by pos_profile,posting_date,posting_time
""", (date, time), as_dict=1)
pos_dict = {}
for res in data:
key = (res.pos_profile, res.posting_date)
pos_dict.setdefault(key, [])
pos_dict[key].append(res)
for res in pos_dict.keys():
start_date = pos_dict[res][0]['posting_date']
start_time = pos_dict[res][0]['posting_time']
start_datetime = str(start_date)+" "+str(start_time)
start_datetime = get_datetime_str(start_datetime)
start_datetime = add_to_date(start_datetime, minutes=-1) # pos opening start date
end_date = pos_dict[res][0]['posting_date']
end_time = pos_dict[res][0]['posting_time']
end_datetime = str(end_date)+" "+str(end_time)
end_datetime = get_datetime_str(end_datetime)
end_datetime = add_to_date(end_datetime, minutes=1) # pos closing end date
pos_o_entry = make_opening_entry(res[0], date_time=start_datetime, execute_previous_entry=True)
create_close_entry(pos_o_entry,end_datetime,end_date)
def _get_account_type_based_data(
filters, account_names, period_list, accumulated_values, opening_balances=0
):
if not account_names or not account_names[0] or not type(account_names[0]) == str:
# only proceed if account_names is a list of account names
return {}
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
GLEntry = frappe.qb.DocType("GL Entry")
Account = frappe.qb.DocType("Account")
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
account_subquery = (
frappe.qb.from_(Account)
.where((Account.name.isin(account_names)) | (Account.parent_account.isin(account_names)))
.select(Account.name)
.as_("account_subquery")
)
if opening_balances:
date_info = dict(date=start_date)
months_map = {"Monthly": -1, "Quarterly": -3, "Half-Yearly": -6}
years_map = {"Yearly": -1}
if months_map.get(filters.periodicity):
date_info.update(months=months_map[filters.periodicity])
else:
date_info.update(years=years_map[filters.periodicity])
if accumulated_values:
start, end = add_to_date(start_date, years=-1), add_to_date(period["to_date"], years=-1)
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
start, end = get_date_str(start), get_date_str(end)
else:
start, end = start_date if accumulated_values else period["from_date"], period["to_date"]
start, end = get_date_str(start), get_date_str(end)
result = (
frappe.qb.from_(GLEntry)
.select(Sum(GLEntry.credit) - Sum(GLEntry.debit))
.where(
(GLEntry.company == company)
& (GLEntry.posting_date >= start)
& (GLEntry.posting_date <= end)
& (GLEntry.voucher_type != "Period Closing Voucher")
& (GLEntry.account.isin(account_subquery))
)
).run()
if result and result[0]:
gl_sum = result[0][0]
else:
gl_sum = 0
total += flt(gl_sum)
data.setdefault(period["key"], flt(gl_sum))
data["total"] = total
return data
def get_number_cards(fiscal_year):
year_start_date = get_date_str(fiscal_year.get("year_start_date"))
year_end_date = get_date_str(fiscal_year.get("year_end_date"))
return [{
"doctype":
"Number Card",
"document_type":
"Payment Entry",
"name":
"Total Incoming Payment",
"filters_json":
json.dumps([['Payment Entry', 'docstatus', '=', 1],
[
'Payment Entry', 'posting_date', 'between',
[year_start_date, year_end_date]
], ['Payment Entry', 'payment_type', '=', 'Receive']]),
"label":
_("Total Incoming Payment"),
"function":
"Sum",
"aggregate_function_based_on":
"base_received_amount",
"is_public":
1,
"is_custom":
1,
"show_percentage_stats":
1,
"stats_time_interval":
"Monthly"
}, {
"doctype":
"Number Card",
"document_type":
"Payment Entry",
"name":
"Total Outgoing Payment",
"filters_json":
json.dumps([['Payment Entry', 'docstatus', '=', 1],
[
'Payment Entry', 'posting_date', 'between',
[year_start_date, year_end_date]
], ['Payment Entry', 'payment_type', '=', 'Pay']]),
"label":
_("Total Outgoing Payment"),
"function":
"Sum",
"aggregate_function_based_on":
"base_paid_amount",
"is_public":
1,
"is_custom":
1,
"show_percentage_stats":
1,
"stats_time_interval":
"Monthly"
}, {
"doctype":
"Number Card",
"document_type":
"Sales Invoice",
"name":
"Total Outgoing Bills",
"filters_json":
json.dumps([['Sales Invoice', 'docstatus', '=', 1],
[
'Sales Invoice', 'posting_date', 'between',
[year_start_date, year_end_date]
]]),
"label":
_("Total Outgoing Bills"),
"function":
"Sum",
"aggregate_function_based_on":
"base_net_total",
"is_public":
1,
"is_custom":
1,
"show_percentage_stats":
1,
"stats_time_interval":
"Monthly"
}, {
"doctype":
"Number Card",
"document_type":
"Purchase Invoice",
"name":
"Total Incoming Bills",
"filters_json":
json.dumps([['Purchase Invoice', 'docstatus', '=', 1],
[
'Purchase Invoice', 'posting_date', 'between',
[year_start_date, year_end_date]
]]),
"label":
_("Total Incoming Bills"),
"function":
"Sum",
"aggregate_function_based_on":
"base_net_total",
"is_public":
1,
"is_custom":
1,
"show_percentage_stats":
1,
"stats_time_interval":
"Monthly"
}]
def send_holiday_notification():
"""Sends an email for list of holidays falling in 7 days from today
"""
# holidays is a list of holidays which fall in 7 days from today
today_date = today()
holiday_lists = frappe.get_all(
'Holiday List',
filters={"enabled": 1},
fields=["name", "send_reminders_to", "notification_message"])
for holiday_list in holiday_lists:
end_date = get_date_str(add_days(today(), 7))
holidays = frappe.get_all(
'Holiday',
filters={
"parent": holiday_list.name,
"holiday_date": ["BETWEEN", [today_date, end_date]]
},
fields=["holiday_date", "is_weekly_off", "description"])
if holidays:
_holiday = ""
for holiday in holidays:
if not holiday.is_weekly_off:
_holiday += """
<tr>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
</tr>
""".format(formatdate(holiday.holiday_date),
frappe.utils.get_weekday(holiday.holiday_date),
holiday.description)
if _holiday:
holiday_table = """
<table>
<thead>
<tr>
<th>Date</th>
<th>Day</th>
<th>Description</th>
</tr>
</thead>
<tbody>
{0}
</tbody>
</table>
""".format(_holiday)
recipients = [
d.email
for d in frappe.get_all("Email Group Member",
filters={
"email_group":
holiday_list.send_reminders_to
},
fields=["email"])
]
message = ''
if holiday_list.notification_message:
message = holiday_list.notification_message + '<br>' + holiday_table
else:
message = holiday_table
if len(recipients):
frappe.sendmail(recipients=recipients,
subject="Holiday Notification",
message=message)