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),
}
