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 make_data(item_data): return { "customer": customer, "due_date": item_data.invoice_date, "posting_date": get_first_day(item_data.invoice_date), "debit_to": debit_to, "set_posting_time": 1, "posting_time": 0, "pm_rental_contract": renting.name, "items": [{ "item_code": rental_item, "description": make_item_description({ "property": renting.property, "posting_date": item_data.invoice_date, }), "rate": renting.rental_amount, "qty": 1, }], }
def execute(**kwargs): rental_contract = kwargs.get('rental_contract', None) rental_contract_items = kwargs.pop('rental_contract_items', None) tenant_dues = _get_tenant_dues(kwargs) rental_item = frappe.db.get_single_value('Facility Management Settings', 'rental_item') submit_si = frappe.db.get_single_value('Facility Management Settings', 'submit_si') if not tenant_dues and rental_contract_items: tenant_dues = rental_contract_items for tenant_due in tenant_dues: if not isinstance(tenant_due, _dict): tenant_due = tenant_due.as_dict() tenant = tenant_due.get('tenant') description = tenant_due.get('description') rental_amount = tenant_due.get('rental_amount') advance_paid_amount = tenant_due.get('advance_paid_amount') customer = frappe.db.get_value('Tenant Master', tenant, 'customer') parent_rc = tenant_due.get('parent') if not parent_rc: parent_rc = rental_contract amount = advance_paid_amount if description == 'Advance Payment' else rental_amount invoice = frappe.new_doc('Sales Invoice') invoice.update({ 'customer': customer, 'posting_date': get_first_day(tenant_due.get('invoice_date')), 'posting_time': 0, 'due_date': tenant_due.get('invoice_date'), 'debit_to': frappe.db.get_value('Company', invoice.company, 'default_receivable_account'), 'set_posting_time': 1, 'pm_rental_contract': parent_rc }) invoice.append('items', { 'item_code': rental_item, 'rate': amount, 'qty': 1.0, }) invoice.set_missing_values() invoice.save() if submit_si: invoice.submit() set_invoice_created(tenant_due.get('name'), invoice.name)
def get_period_beginning(date, timegrain, as_str=True): return getdate({ "Daily": date, "Weekly": get_first_day_of_week(date), "Monthly": get_first_day(date), "Quarterly": get_quarter_start(date), "Yearly": get_year_start(date), }[timegrain])
def get_period_beginning(date, timegrain, as_str=True): return getdate({ 'Daily': date, 'Weekly': get_first_day_of_week(date), 'Monthly': get_first_day(date), 'Quarterly': get_quarter_start(date), 'Yearly': get_year_start(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 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 make_data(item_data): return { 'customer': customer, 'due_date': item_data.invoice_date, 'posting_date': get_first_day(item_data.invoice_date), 'debit_to': debit_to, 'set_posting_time': 1, 'posting_time': 0, 'pm_rental_contract': renting.name, 'items': [ { 'item_code': rental_item, 'rate': renting.rental_amount, 'qty': 1 } ] }
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_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 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
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 execute(**kwargs): rental_contract = kwargs.get("rental_contract", None) rental_contract_items = kwargs.pop("rental_contract_items", None) tenant_dues = _get_tenant_dues(kwargs) rental_item = frappe.db.get_single_value( "Facility Management Settings", "rental_item" ) submit_si = frappe.db.get_single_value("Facility Management Settings", "submit_si") if not tenant_dues and rental_contract_items: tenant_dues = rental_contract_items for tenant_due in tenant_dues: if not isinstance(tenant_due, _dict): tenant_due = tenant_due.as_dict() tenant = tenant_due.get("tenant") description = tenant_due.get("description") rental_amount = tenant_due.get("rental_amount") advance_paid_amount = tenant_due.get("advance_paid_amount") customer = frappe.db.get_value("Tenant Master", tenant, "customer") parent_rc = tenant_due.get("parent") if not parent_rc: parent_rc = rental_contract amount = ( advance_paid_amount if description == "Advance Payment" else rental_amount ) invoice = frappe.new_doc("Sales Invoice") invoice.update( { "customer": customer, "posting_date": get_first_day(tenant_due.get("invoice_date")), "posting_time": 0, "due_date": tenant_due.get("invoice_date"), "debit_to": frappe.db.get_value( "Company", invoice.company, "default_receivable_account" ), "set_posting_time": 1, "pm_rental_contract": parent_rc, } ) name = tenant_due.get("name") tenant_property = tenant_due.get("property") invoice.remarks = make_description({ "posting_date": invoice.posting_date, "property": tenant_property, "rental_contract": name }) invoice.append( "items", { "item_code": rental_item, "description": make_item_description({ 'property': tenant_property, 'posting_date': invoice.posting_date, }), "rate": amount, "qty": 1.0, }, ) invoice.set_missing_values() invoice.save() if submit_si: invoice.submit() set_invoice_created(name, invoice.name)
def get_month_date_range(posting_date): return { "from_date": get_first_day(posting_date), "to_date": get_last_day(posting_date), }