def set_current_invoice_end(self):
"""
This sets the date of the end of the current billing period.
If the subscription is in trial period, it will be set as the end of the
trial period.
If is not in a trial period, it will be `x` days from the beginning of the
current billing period where `x` is the billing interval from the
`Subscription Plan` in the `Subscription`.
"""
if self.is_trialling() and getdate(
self.current_invoice_start) < getdate(self.trial_period_end):
self.current_invoice_end = self.trial_period_end
else:
billing_cycle_info = self.get_billing_cycle_data()
if billing_cycle_info:
if self.is_new_subscription() and getdate(
self.start_date) < getdate(self.current_invoice_start):
self.current_invoice_end = add_to_date(
self.start_date, **billing_cycle_info)
# For cases where trial period is for an entire billing interval
if getdate(self.current_invoice_end) < getdate(
self.current_invoice_start):
self.current_invoice_end = add_to_date(
self.current_invoice_start, **billing_cycle_info)
else:
self.current_invoice_end = add_to_date(
self.current_invoice_start, **billing_cycle_info)
else:
self.current_invoice_end = get_last_day(
self.current_invoice_start)
if self.follow_calendar_months:
billing_info = self.get_billing_cycle_and_interval()
billing_interval_count = billing_info[0][
'billing_interval_count']
calendar_months = get_calendar_months(billing_interval_count)
calendar_month = 0
current_invoice_end_month = getdate(
self.current_invoice_end).month
current_invoice_end_year = getdate(
self.current_invoice_end).year
for month in calendar_months:
if month <= current_invoice_end_month:
calendar_month = month
if cint(calendar_month - billing_interval_count) <= 0 and \
getdate(self.current_invoice_start).month != 1:
calendar_month = 12
current_invoice_end_year -= 1
self.current_invoice_end = get_last_day(cstr(current_invoice_end_year) + '-' \
+ cstr(calendar_month) + '-01')
if self.end_date and getdate(self.current_invoice_end) > getdate(
self.end_date):
self.current_invoice_end = self.end_date
def get_current_documents(self, doctype):
events = frappe.get_all("Subscription Event",
filters={
"subscription": self.name,
"document_type": doctype,
"period_start": self.current_invoice_start,
"period_end": self.current_invoice_end,
"event_type":
f"{doctype.capitalize()} created"
},
fields=["document_name"])
if events:
return [x.document_name for x in events]
else:
period_documents = []
transaction_date = "posting_date" if doctype == "Sales Invoice" else "transaction_date"
billing_cycle_info = self.get_billing_cycle_data()
documents = frappe.get_all(doctype, filters={"subscription": self.name}, \
fields=[transaction_date, "name", "docstatus"])
for document in documents:
if billing_cycle_info:
calculated_end = add_to_date(
document.get(transaction_date), **billing_cycle_info)
else:
calculated_end = get_last_day(
document.get(transaction_date))
if calculated_end >= getdate(self.current_invoice_end):
period_documents.append(document)
return period_documents
def _get_tenant_dues(filters):
"""
Get due invoices during the day
:return:
"""
clauses = _get_clauses(filters)
return frappe.db.sql(
"""
SELECT
rci.name,
rci.invoice_date,
rci.description,
rci.parent,
rc.rental_amount,
rc.advance_paid_amount,
rc.tenant,
rc.property
FROM `tabRental Contract Item` rci
INNER JOIN `tabRental Contract` rc
ON rci.parent = rc.name
WHERE rc.docstatus = 1
AND rci.is_invoice_created = 0
{clauses}
""".format(
clauses="AND " + clauses if clauses else ""
),
{**filters, "now": get_last_day(today())},
as_dict=True,
)
def subscription_headline(name):
subscription = frappe.get_doc('Subscription', name)
billing_cycle_info = subscription.get_billing_cycle_data()
if not subscription.has_invoice_for_period():
if subscription.generate_invoice_at_period_start:
if subscription.is_trial():
next_invoice_date = add_days(subscription.trial_period_end, 1)
else:
next_invoice_date = subscription.current_invoice_start
else:
if subscription.is_trial():
if billing_cycle_info:
next_invoice_date = add_to_date(add_days(subscription.trial_period_end, 1), \
**billing_cycle_info)
else:
next_invoice_date = get_last_day(
add_days(subscription.trial_period_end, 1))
else:
next_invoice_date = add_days(subscription.current_invoice_end,
1)
else:
if subscription.generate_invoice_at_period_start:
next_invoice_date = add_days(subscription.current_invoice_end, 1)
else:
if billing_cycle_info:
next_invoice_date = add_to_date(add_days(subscription.current_invoice_end, 1), \
**billing_cycle_info)
else:
next_invoice_date = get_last_day(
add_days(subscription.current_invoice_end, 1))
if subscription.cancellation_date and getdate(
subscription.cancellation_date) > getdate(nowdate()):
return _("This subscription will be cancelled on {0}").format(
global_date_format(subscription.cancellation_date))
elif subscription.cancellation_date and subscription.cancellation_date <= getdate(
nowdate()):
return _("This subscription has been cancelled on {0}").format(
global_date_format(subscription.cancellation_date))
return _("The next invoice will be generated on {0}").format(
global_date_format(next_invoice_date))
def get_period_ending(date, timegrain):
date = getdate(date)
if timegrain == "Daily":
return date
else:
return getdate({
"Daily": date,
"Weekly": get_last_day_of_week(date),
"Monthly": get_last_day(date),
"Quarterly": get_quarter_ending(date),
"Yearly": get_year_ending(date),
}[timegrain])
def set_current_invoice_end(self):
if self.trial_period_start and getdate(
self.trial_period_end) > getdate(self.current_invoice_start):
self.current_invoice_end = self.trial_period_end
else:
billing_cycle_info = self.get_billing_cycle_data()
if billing_cycle_info:
self.current_invoice_end = add_to_date(
self.current_invoice_start, **billing_cycle_info)
else:
self.current_invoice_end = get_last_day(
self.current_invoice_start)
def get_period_ending(date, timegrain):
date = getdate(date)
if timegrain == 'Daily':
return date
else:
return getdate({
'Daily': date,
'Weekly': get_last_day_of_week(date),
'Monthly': get_last_day(date),
'Quarterly': get_quarter_ending(date),
'Yearly': get_year_ending(date)
}[timegrain])
def get_periods(period_date, no_of_periods=3):
if not isinstance(period_date, date):
period_date = getdate(period_date)
periods = []
for x in range(-no_of_periods + 1, 1):
pd = add_months(period_date, x)
periods.append(
frappe._dict({
'start_date': get_first_day(pd),
'end_date': get_last_day(pd),
'label': pd.strftime("%b %Y"),
'key': pd.strftime("%b_%Y").lower(),
}))
return periods
def get_quarter_start_end(fiscal_year, quarter):
from frappe.utils.data import add_months, get_last_day
fiscal_start_date = frappe.db.get_value('Fiscal Year', fiscal_year, 'year_start_date')
if quarter == 'I':
offset = 0
elif quarter == 'II':
offset = 1
elif quarter == 'III':
offset = 2
elif quarter == 'IV':
offset = 3
start_d = add_months(fiscal_start_date, offset * 3)
end_d = get_last_day(add_months(fiscal_start_date, (offset * 3) + 2))
return start_d, end_d.strftime('%Y-%m-%d')
def get_diff_days(double_NO, target_date):
Calculation_option = frappe.db.get_single_value("HR Settings",
"day_calculation")
days_diff = date_diff(get_last_day(target_date), target_date) + 1
if Calculation_option == "Calendar":
days_diff = days_diff / get_month_days(target_date)
elif Calculation_option == "360":
days_diff = (days_diff / 360) * 12
elif Calculation_option == "365":
days_diff = (days_diff / 365) * 12
double_NO = (double_NO - 1) + days_diff
return double_NO
def post_earned_leaves():
date = add_days(frappe.utils.nowdate(), -5)
start = get_first_day(date);
end = get_last_day(date);
employees = frappe.db.sql("select name, employee_name from `tabEmployee` where status = 'Active' and employment_type in (\'Regular employees\', \'Contract\')", as_dict=True)
for e in employees:
la = frappe.new_doc("Leave Allocation")
la.employee = e.name
la.employee_name = e.employee_name
la.leave_type = "Earned Leave"
la.from_date = str(start)
la.to_date = str(end)
la.carry_forward = cint(1)
la.new_leaves_allocated = flt(2.5)
la.submit()
def get_vermietete_tage(jahr, monat, wohnung):
#case 1: start & ende innerhalb des monats
c1_buchungen = frappe.db.sql(
"""SELECT * FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` <= '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
c1_tage = 0
for buchung in c1_buchungen:
c1_tage += date_diff(buchung.end_date, buchung.start_date) + 1
#case 2: start < monat & ende innerhalb des monats
c2_buchungen = frappe.db.sql(
"""SELECT * FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` <= '{ende}' AND `end_date` >= '{monats_erster}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
c2_tage = 0
for buchung in c2_buchungen:
c2_tage += date_diff(buchung.end_date, jahr + "-" + monat + "-01") + 1
#case 3: start innerhalb des monats & ende > monat
c3_buchungen = frappe.db.sql(
"""SELECT * FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` > '{ende}' AND `start_date` <= '{monats_letzter}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
c3_tage = 0
for buchung in c3_buchungen:
c3_tage += date_diff(get_last_day(jahr + "-" + monat + "-15"),
buchung.start_date) + 1
#case 4: start < monat & ende > monat
c4_buchungen = frappe.db.sql(
"""SELECT * FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` > '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
c4_tage = 0
for buchung in c4_buchungen:
c4_tage += date_diff(get_last_day(jahr + "-" + monat + "-15"),
jahr + "-" + monat + "-01") + 1
return c1_tage + c2_tage + c3_tage + c4_tage
def post_earned_leaves():
date = add_years(frappe.utils.nowdate(), 1)
start = get_first_day(date)
end = get_last_day(date)
employees = frappe.db.sql(
"select name, employee_name from `tabEmployee` where status = 'Active' and employment_type in (\'Regular employees\', \'Contract\')",
as_dict=True)
for e in employees:
la = frappe.new_doc("Leave Allocation")
la.employee = e.name
la.employee_name = e.employee_name
la.leave_type = "Casual Leave"
la.from_date = str(start)
la.to_date = str(end)
la.carry_forward = cint(0)
la.new_leaves_allocated = flt(10)
la.submit()
def get_interval(day_of_month, date_obj):
'''Returns start and end date of the interval'''
if not isinstance(date_obj, date):
date_obj = getdate(date_obj)
try:
start_date = date_obj.replace(day=day_of_month)
except ValueError:
start_date = add_months(date_obj, -1).replace(day=day_of_month)
if date_diff(date_obj, start_date) < 0:
start_date = add_months(start_date, -1)
try:
end_date = date_obj.replace(day=day_of_month)
except ValueError:
end_date = get_last_day(date_obj)
if date_diff(end_date, date_obj) <= 0:
end_date = add_months(end_date, 1)
end_date = add_days(end_date, -1)
as_text = '{} - {}'.format(start_date, end_date)
return start_date, end_date, as_text
def set_current_invoice_end(self): """ This sets the date of the end of the current billing period. If the subscription is in trial period, it will be set as the end of the trial period. If is not in a trial period, it will be `x` days from the beginning of the current billing period where `x` is the billing interval from the `Subscription Plan` in the `Subscription`. """ if self.is_trialling(): self.current_invoice_end = self.trial_period_end else: billing_cycle_info = self.get_billing_cycle_data() if billing_cycle_info: self.current_invoice_end = add_to_date(self.current_invoice_start, **billing_cycle_info) else: self.current_invoice_end = get_last_day(self.current_invoice_start)
def set_current_invoice_end(self): """ This sets the date of the end of the current billing period. If the subscription is in trial period, it will be set as the end of the trial period. If is not in a trial period, it will be `x` days from the beginning of the current billing period where `x` is the billing interval from the `Subscription Plan` in the `Subscription`. """ if self.is_trialling(): self.current_invoice_end = self.trial_period_end else: billing_cycle_info = self.get_billing_cycle_data() if billing_cycle_info: self.current_invoice_end = add_to_date(self.current_invoice_start, **billing_cycle_info) else: self.current_invoice_end = get_last_day(self.current_invoice_start)
def get_month_date_range(posting_date):
return {
"from_date": get_first_day(posting_date),
"to_date": get_last_day(posting_date),
}
def get_month_date_range(posting_date):
return {
'from_date': get_first_day(posting_date),
'to_date': get_last_day(posting_date)
}
def get_effektive_verrechnung(jahr, monat, wohnung):
effektiv_verrechnet = 0.00
#case 1: start & ende innerhalb des monats
c1_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` <= '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c1_buchungen:
totalbetrag = frappe.db.sql(
"""SELECT SUM(`grand_total`) FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'"""
.format(buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 2: start < monat & ende innerhalb des monats
c2_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` <= '{ende}' AND `end_date` >= '{monats_erster}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c2_buchungen:
totalbetrag = frappe.db.sql(
"""SELECT SUM(`grand_total`) FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'"""
.format(buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 3: start innerhalb des monats & ende > monat
c3_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` > '{ende}' AND `start_date` <= '{monats_letzter}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c3_buchungen:
totalbetrag = frappe.db.sql(
"""SELECT SUM(`grand_total`) FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'"""
.format(buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 4: start < monat & ende > monat
c4_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` > '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c4_buchungen:
totalbetrag = frappe.db.sql(
"""SELECT SUM(`grand_total`) FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'"""
.format(buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
return effektiv_verrechnet
def get_effektive_verrechnung(jahr, monat, wohnung):
effektiv_verrechnet = 0.00
item_liste = []
_item_liste = frappe.get_single('Statistik Einstellungen').item
for item in _item_liste:
item_liste.append(item.item)
item_liste = str(item_liste).replace('[', '').replace(']', '')
#case 1: start & ende innerhalb des monats
c1_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` <= '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c1_buchungen:
alle_rechnungen_query = """SELECT `name` FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'""".format(
buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15"))
totalbetrag = frappe.db.sql(
"""SELECT SUM(`amount`) FROM `tabSales Invoice Item` WHERE `item_code` IN ({item_liste}) AND `parent` IN ({alle_rechnungen_query})"""
.format(item_liste=item_liste,
alle_rechnungen_query=alle_rechnungen_query),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 2: start < monat & ende innerhalb des monats
c2_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` <= '{ende}' AND `end_date` >= '{monats_erster}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c2_buchungen:
alle_rechnungen_query = """SELECT `name` FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'""".format(
buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15"))
totalbetrag = frappe.db.sql(
"""SELECT SUM(`amount`) FROM `tabSales Invoice Item` WHERE `item_code` IN ({item_liste}) AND `parent` IN ({alle_rechnungen_query})"""
.format(item_liste=item_liste,
alle_rechnungen_query=alle_rechnungen_query),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 3: start innerhalb des monats & ende > monat
c3_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` >= '{start}' AND `end_date` > '{ende}' AND `start_date` <= '{monats_letzter}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c3_buchungen:
alle_rechnungen_query = """SELECT `name` FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'""".format(
buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15"))
totalbetrag = frappe.db.sql(
"""SELECT SUM(`amount`) FROM `tabSales Invoice Item` WHERE `item_code` IN ({item_liste}) AND `parent` IN ({alle_rechnungen_query})"""
.format(item_liste=item_liste,
alle_rechnungen_query=alle_rechnungen_query),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
#case 4: start < monat & ende > monat
c4_buchungen = frappe.db.sql(
"""SELECT `name` FROM `tabBooking` WHERE `booking_status` = 'Booked' AND `appartment` = '{wohnung}' AND `start_date` < '{start}' AND `end_date` > '{ende}'"""
.format(wohnung=wohnung,
start=get_first_day(jahr + "-" + monat + "-15"),
ende=get_last_day(jahr + "-" + monat + "-15")),
as_dict=True)
for buchung in c4_buchungen:
alle_rechnungen_query = """SELECT SUM(`grand_total`) FROM `tabSales Invoice` WHERE `booking` = '{buchung}' AND `docstatus` = 1 AND `posting_date` >= '{monats_erster}' AND `posting_date` <= '{monats_letzter}'""".format(
buchung=buchung.name,
monats_erster=get_first_day(jahr + "-" + monat + "-15"),
monats_letzter=get_last_day(jahr + "-" + monat + "-15"))
totalbetrag = frappe.db.sql(
"""SELECT SUM(`amount`) FROM `tabSales Invoice Item` WHERE `item_code` IN ({item_liste}) AND `parent` IN ({alle_rechnungen_query})"""
.format(item_liste=item_liste,
alle_rechnungen_query=alle_rechnungen_query),
as_list=True)[0][0]
if totalbetrag:
effektiv_verrechnet += float(totalbetrag)
return effektiv_verrechnet
def max_tage(jahr, monat):
return date_diff(get_last_day(jahr + "-" + monat + "-15"),
get_first_day(jahr + "-" + monat + "-15")) + 1
