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 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