def check_project_value(self,date_list,months,due_amount,final_date): if self.pro_per == '30' or self.pro_per == '31': months-=1 frappe.errprint(self.pro_per) frappe.errprint(months) if flt(self.p_value)%cint(months) == 0: due_amount = due_amount if months == 1: self.create_child_record(due_amount,date_list) else: for i in range(1,months): date=add_months(final_date,1) date_list.append(date) final_date=date self.create_child_record(due_amount,date_list) else: modulo_value = flt(self.p_value)%cint(months) monthly_amount = flt(flt(self.p_value - modulo_value)/cint(months)) amount_list = [] for i in range(0,months): if i == months-1: amount_list.append(flt(monthly_amount + modulo_value)) else: amount_list.append(monthly_amount) if months == 1: self.create_child_record(due_amount,date_list) else: for i in range(1,months): date=add_months(final_date,1) date_list.append(date) final_date=date self.create_child1_record(amount_list,date_list)
def _test(i):
obj.assertEquals(i+1, frappe.db.sql("""select count(*) from `tab%s`
where recurring_id=%s and (docstatus=1 or docstatus=0)""" % (base_doc.doctype, '%s'),
(base_doc.recurring_id))[0][0])
next_date = get_next_date(base_doc.get(date_field), no_of_months,
base_doc.repeat_on_day_of_month)
manage_recurring_documents(base_doc.doctype, next_date=next_date, commit=False)
recurred_documents = frappe.db.sql("""select name from `tab%s`
where recurring_id=%s and (docstatus=1 or docstatus=0) order by name desc"""
% (base_doc.doctype, '%s'), (base_doc.recurring_id))
obj.assertEquals(i+2, len(recurred_documents))
new_doc = frappe.get_doc(base_doc.doctype, recurred_documents[0][0])
for fieldname in ["is_recurring", "recurring_type",
"repeat_on_day_of_month", "notification_email_address"]:
obj.assertEquals(base_doc.get(fieldname),
new_doc.get(fieldname))
obj.assertEquals(new_doc.get(date_field), getdate(next_date))
obj.assertEquals(new_doc.from_date, getdate(add_months(base_doc.from_date, no_of_months)))
if first_and_last_day:
obj.assertEquals(new_doc.to_date, getdate(get_last_day(add_months(base_doc.to_date, no_of_months))))
else:
obj.assertEquals(new_doc.to_date, getdate(add_months(base_doc.to_date, no_of_months)))
return new_doc
def get_next_dep_date(doc, dep_freq, tot_dep):
#Next depreciation date shoud be last date of the purchase date if monthly or last date
#of period of depreciation
#if monthly depreciation then last day or month, if bi-monthly then last day of month+1
#if 3 monthly then last day of quarter and not 3 months
#if 4 monthly then last day of third and not 4 months and so on and so forth
fy_doc = get_fy_doc(doc)
r = relativedelta.relativedelta(add_days(fy_doc.year_end_date,1), fy_doc.year_start_date)
fy_months = r.years*12 + r.months
dep_in_fy = cint(fy_months)/flt(dep_freq)
booked_deps_months = (cint(doc.number_of_depreciations_booked)*cint(dep_freq))
last_day = add_months(get_last_day(doc.purchase_date), booked_deps_months)
base_last_day = get_last_day(doc.purchase_date)
base_dep_date = None
if dep_in_fy >= 1 and dep_in_fy % 1 == 0:
for i in range(0, cint(tot_dep)):
dep_date = get_last_day(add_months(fy_doc.year_start_date, (i*dep_freq -1)))
if base_last_day <= dep_date and base_dep_date is None:
base_dep_date = dep_date
if last_day <= dep_date:
doc.next_depreciation_date = dep_date
break
else:
doc.next_depreciation_date = fy_doc.year_end_date
elif dep_in_fy % 1 != 0:
frappe.throw('Frequency Causing Depreciations not to be posted equally in FY, \
please change the frequency of depreciation')
else:
frappe.throw('Months between depreciation cannot be less than 1')
return base_dep_date
def get_employee_details():
return [{"employee": frappe.get_value("Employee", {"employee_name":"*****@*****.**"}, "name"),
"base": 25000,
"variable": 5000,
"from_date": add_months(nowdate(),-1),
"idx": 1
},
{"employee": frappe.get_value("Employee", {"employee_name":"*****@*****.**"}, "name"),
"base": 15000,
"variable": 100,
"from_date": add_months(nowdate(),-1),
"idx": 2
}
]
def make_depreciation_schedule(self):
self.schedules = []
done = ""
if not self.get("schedules") and self.next_depreciation_date:
accumulated_depreciation = flt(self.opening_accumulated_depreciation)
income_accumulated_depreciation = flt(self.income_tax_opening_depreciation_amount)
value_after_depreciation = flt(self.value_after_depreciation)
current_value_income_tax = flt(self.value_after_depreciation) - flt(self.expected_value_after_useful_life)
number_of_pending_depreciations = cint(self.total_number_of_depreciations) - \
cint(self.number_of_depreciations_booked)
if number_of_pending_depreciations:
for n in xrange(number_of_pending_depreciations):
schedule_date = get_last_day(add_months(self.next_depreciation_date,
n * cint(self.frequency_of_depreciation)))
last_schedule_date = add_months(self.next_depreciation_date,
(n - 1) * cint(self.frequency_of_depreciation))
if n == 0:
num_of_days = get_number_of_days(self.purchase_date, schedule_date) + 1
else:
num_of_days = get_number_of_days(last_schedule_date, schedule_date)
depreciation_amount = self.get_depreciation_amount(value_after_depreciation, num_of_days)
income_tax_amount = self.get_income_tax_depreciation_amount(current_value_income_tax, flt(self.asset_depreciation_percent), num_of_days)
accumulated_depreciation += flt(depreciation_amount)
value_after_depreciation -= flt(depreciation_amount)
income_accumulated_depreciation += income_tax_amount
if accumulated_depreciation < self.gross_purchase_amount:
self.append("schedules", {
"schedule_date": schedule_date,
"depreciation_amount": depreciation_amount,
"depreciation_income_tax": income_tax_amount,
"accumulated_depreciation_amount": accumulated_depreciation,
"accumulated_depreciation_income_tax": income_accumulated_depreciation
})
else:
self.append("schedules", {
"schedule_date": schedule_date,
"depreciation_amount": flt(self.gross_purchase_amount) - flt(accumulated_depreciation) + flt(self.expected_value_after_useful_life) + flt(depreciation_amount),
"depreciation_income_tax": income_tax_amount,
"accumulated_depreciation_amount": flt(self.gross_purchase_amount) - flt(self.expected_value_after_useful_life),
"accumulated_depreciation_income_tax": income_accumulated_depreciation
})
break
def calculate_service_end_date(args, item=None):
args = process_args(args)
if not item:
item = frappe.get_cached_doc("Item", args.item_code)
doctype = args.get("parenttype") or args.get("doctype")
if doctype == "Sales Invoice":
enable_deferred = "enable_deferred_revenue"
no_of_months = "no_of_months"
account = "deferred_revenue_account"
else:
enable_deferred = "enable_deferred_expense"
no_of_months = "no_of_months_exp"
account = "deferred_expense_account"
service_start_date = args.service_start_date if args.service_start_date else args.transaction_date
service_end_date = add_months(service_start_date, item.get(no_of_months))
deferred_detail = {
"service_start_date": service_start_date,
"service_end_date": service_end_date
}
deferred_detail[enable_deferred] = item.get(enable_deferred)
deferred_detail[account] = get_default_deferred_account(args, item, fieldname=account)
return deferred_detail
def create_quotation(self):
quotation = frappe.new_doc("Quotation")
values = {
"doctype": "Quotation",
"quotation_to": "Customer",
"order_type": "Shopping Cart",
"party_name": get_party(frappe.session.user).name,
"docstatus": 0,
"contact_email": frappe.session.user,
"selling_price_list": "_Test Price List Rest of the World",
"currency": "USD",
"taxes_and_charges" : "_Test Tax 1 - _TC",
"conversion_rate":1,
"transaction_date" : nowdate(),
"valid_till" : add_months(nowdate(), 1),
"items": [{
"item_code": "_Test Item",
"qty": 1
}],
"taxes": frappe.get_doc("Sales Taxes and Charges Template", "_Test Tax 1 - _TC").taxes,
"company": "_Test Company"
}
quotation.update(values)
quotation.insert(ignore_permissions=True)
return quotation
def get_alerts_based_on_last_request(request_data, email):
"""
Generate search query from last search request of that user &
check for properties which are posted from last month only.
"""
try:
search_query = {
"sort": [{ "posted_datetime": { "order": "desc" }}],
"query":{ "bool":{ "must":[ {"match":{ "user_id":request_data.get("user_id") } } ] } }
}
es = ElasticSearchController()
response_data, total_records = es.search_document(["request"], search_query, 1, 1)
if response_data:
last_month_date = add_months(datetime.datetime.now() ,-1).strftime("%Y-%m-%d %H:%M:%S")
property_search_query = response_data[0].get("search_query")
property_search_query = ast.literal_eval(property_search_query.encode("utf8"))
new_query = property_search_query.get("query").get("bool").get("must")
new_query.append({
"range" : {
"modified_datetime" : {
"gte":last_month_date,
}
}
})
property_search_query["query"]["bool"]["must"] = new_query
uom = response_data[0].get("unit_of_area")
uom = "Sq.Ft." if uom not in ["Sq.Ft.", "Acres", "Hectares"] else uom
return property_search_query, uom
else:
raise OperationFailed("No Alerts and Request Id found against User {0}".format(email))
except elasticsearch.ElasticsearchException,e:
raise ElasticSearchException(e.error)
def get_employee_details(employee):
return [{"employee": employee,
"base": 50000,
"variable": 5000,
"from_date": add_months(nowdate(),-1)
}
]
def make_depreciation_schedule(self):
self.schedules = []
if not self.get("schedules") and self.next_depreciation_date:
accumulated_depreciation = flt(self.opening_accumulated_depreciation)
value_after_depreciation = flt(self.value_after_depreciation)
number_of_pending_depreciations = cint(self.total_number_of_depreciations) - cint(
self.number_of_depreciations_booked
)
if number_of_pending_depreciations:
for n in xrange(number_of_pending_depreciations):
schedule_date = add_months(self.next_depreciation_date, n * cint(self.frequency_of_depreciation))
depreciation_amount = self.get_depreciation_amount(value_after_depreciation)
accumulated_depreciation += flt(depreciation_amount)
value_after_depreciation -= flt(depreciation_amount)
self.append(
"schedules",
{
"schedule_date": schedule_date,
"depreciation_amount": depreciation_amount,
"accumulated_depreciation_amount": accumulated_depreciation,
},
)
def make_repayment_schedule(self):
self.repayment_schedule = []
payment_date = self.disbursement_date
balance_amount = self.loan_amount
while(balance_amount > 0):
interest_amount = rounded(balance_amount * flt(self.rate_of_interest) / (12*100))
principal_amount = self.monthly_repayment_amount - interest_amount
balance_amount = rounded(balance_amount + interest_amount - self.monthly_repayment_amount)
if balance_amount < 0:
principal_amount += balance_amount
balance_amount = 0.0
total_payment = principal_amount + interest_amount
self.append("repayment_schedule", {
"payment_date": payment_date,
"principal_amount": principal_amount,
"interest_amount": interest_amount,
"total_payment": total_payment,
"balance_loan_amount": balance_amount
})
next_payment_date = add_months(payment_date, 1)
payment_date = next_payment_date
def check_project_value_for_fix_varialble(self,date_list,final_date,months,due_amount):
if self.pro_per == '30' or self.pro_per == '31':
months-=1
if flt(self.fix_val)%cint(months) == 0:
due_amount = due_amount
if months == 1:
self.create_child_record(due_amount,date_list)
if self.var_val:
ch = self.append('table_17', {})
ch.f_type='Variable'
ch.amount = self.var_val
else:
for i in range(1,months):
date=add_months(final_date,1)
date_list.append(date)
final_date=date
self.create_child_record(due_amount,date_list)
if self.var_val:
ch = self.append('table_17', {})
ch.f_type='Variable'
ch.amount = self.var_val
else:
modulo_value = flt(self.fix_val)%cint(months)
monthly_amount = flt(flt(self.fix_val - modulo_value)/cint(months))
amount_list = []
for i in range(0,months):
if i == months-1:
amount_list.append(flt(monthly_amount + modulo_value))
else:
amount_list.append(monthly_amount)
if months == 1:
self.create_child_record(due_amount,date_list)
if self.var_val:
ch = self.append('table_17', {})
ch.f_type='Variable'
ch.amount = self.var_val
else:
for i in range(1,months):
date=add_months(final_date,1)
date_list.append(date)
final_date=date
self.create_child1_record(amount_list,date_list)
if self.var_val:
ch = self.append('table_17', {})
ch.f_type='Variable'
ch.amount = self.var_val
def create_benefit_claim(employee, payroll_period, amount, component):
claim_date = add_months(payroll_period.start_date, random.randint(0, 11))
frappe.get_doc({
"doctype": "Employee Benefit Claim",
"employee": employee,
"claimed_amount": amount,
"claim_date": claim_date,
"earning_component": component
}).submit()
return claim_date
def get_due_date(posting_date, term): due_date = None if term.due_date_based_on == "Day(s) after invoice date": due_date = add_days(posting_date, term.credit_days) elif term.due_date_based_on == "Day(s) after the end of the invoice month": due_date = add_days(get_last_day(posting_date), term.credit_days) elif term.due_date_based_on == "Month(s) after the end of the invoice month": due_date = add_months(get_last_day(posting_date), term.credit_months) return due_date
def processRenewals(self):
count = 0
for d in self.results:
if d.ucheck == True:
count+=1
if(count==0):
frappe.throw(_("No Records to process"))
for d in self.results:
if d.ucheck == True:
trans = frappe.new_doc("Renewal Invoices")
invoice = frappe.get_doc("Tenant Invoice Details",cstr(d.invoice))
if not invoice:
frappe.throw(_("The invoice does not exist..."))
else:
trans.inv_no = cstr(d.invoice)
trans.tenant = invoice.tenant
trans.trans_date = nowdate
trans.eff_from = invoice.ren_date
rendate = invoice.ren_date
if(invoice.pay_frequency == "MONTHLY"):
trans.eff_to = add_months(invoice.ren_date,1)
rendate = add_months(invoice.ren_date,1)
elif(invoice.pay_frequency == "QUARTELY"):
trans.eff_to = add_months(invoice.ren_date,3)
rendate = add_months(invoice.ren_date,3)
elif(invoice.pay_frequency == "SEMI-ANNUALLY"):
trans.eff_to = add_months(invoice.ren_date,6)
rendate = add_months(invoice.ren_date,6)
elif(invoice.pay_frequency == "ANNUALLY"):
trans.eff_to = add_months(invoice.ren_date,12)
rendate = add_months(invoice.ren_date,12)
trans.ren_date = add_days(rendate,1)
trans.pay_freq = invoice.pay_frequency
trans.pay_mode = invoice.pay_mode
trans.currency = invoice.currency
tenant = frappe.get_doc("Tenant Details", invoice.tenant)
if not tenant:
frappe.throw(_("The tenant for invoice {0} does not exist in the system").format(cstr(d.invoice)))
else:
trans.ten_name = "{0} {1}".format(tenant.f_name,tenant.other_names)
trans.address = tenant.address
trans.house_alloc = invoice.house_alloc
trans.unit_inv = invoice.unit_invoiced
trans.branch_inv = invoice.inv_branch
trans.inv_items = invoice.inv_items
trans.amount = invoice.inv_amount
trans.taxes = invoice.total_taxes
trans.net_amount = invoice.net_amount
trans.insert()
self.set('results',[])
frappe.msgprint(_("Renewals processed successfully {0}").format(count))
def validate_validity(doc, method):
frappe.errprint("validate validity")
from frappe.utils import get_url, cstr
frappe.errprint(get_url())
frappe.errprint("validate validity")
if doc.get("__islocal") and get_url()!='http://tailorpad.com':
frappe.errprint("is local and not smarttailor")
res = frappe.db.sql("select name from `tabUser` where name='Administrator' and no_of_users >0")
frappe.errprint(res)
if res:
frappe.errprint("in res if")
frappe.db.sql("update `tabUser`set no_of_users=no_of_users-1 where name='Administrator'")
from frappe.utils import nowdate,add_months,cint
else:
res1 = frappe.db.sql("select count(name) from `tabUser`")
frappe.errprint("else res1 ")
frappe.errprint(res1)
if res1 and res1[0][0]==2:
frappe.errprint("else if")
#frappe.db.sql("update `tabUser`set no_of_users=no_of_users-1 where name='Administrator'")
from frappe.utils import nowdate,add_months,cint
doc.validity_start_date=nowdate()
doc.validity_end_date=add_months(nowdate(),1)
else:
frappe.throw(_("Your User Creation limit is exceeded . Please contact administrator"))
elif(get_url()!='http://tailorpad.com'):
frappe.errprint("updating existing user not smarttailor")
if doc.add_validity:
frappe.errprint("updating existing user not smarttailor")
res1 = frappe.db.sql("select validity from `tabUser Validity` where user_name>0 and name=%s",doc.add_validity)
frappe.errprint(res1)
if res1:
frappe.errprint("else res1 ")
frappe.errprint("update user validity")
from frappe.utils import nowdate,add_months,cint
doc.add_validity=''
frappe.errprint("user validity")
frappe.errprint(doc.add_validity)
frappe.errprint("user validity1")
doc.validity_start_date=nowdate()
doc.validity_end_date=add_months(nowdate(),cint(res1[0][0]))
def set_status(self):
self.time_stamp = now()
self.reporting_month = getdate(self.end_time).strftime("%B")
d = add_months(getdate(self.end_time), 1)
deadline = cstr(getdate(d).strftime("%Y")) + "-" + cstr(getdate(d).strftime("%m")) + "-10"
if getdate(self.time_stamp) > getdate(deadline):
self.project_status = "Late"
elif getdate(self.time_stamp) < getdate(add_days(getdate(self.end_time), 10)):
self.project_status = "Early"
else:
self.project_status = "On Time"
def add_validity():
frappe.errprint("in add validity function")
import requests
import json
from frappe.utils import nowdate, cstr,cint, flt, now, getdate, add_months
pr1 = frappe.db.sql("""select site_name from `tabSite Master` """)
for pr in pr1:
if pr[0].find('.')!= -1:
db=pr[0].split('.')[0][:16]
else:
db=pr[0][:16]
qry="select validity from `"+cstr(db)+"`.`tabUser` where name='administrator' and validity>0 "
#print qry
frappe.errprint(qry)
pp1 = frappe.db.sql(qry)
if pp1 :
headers = {'content-type': 'application/x-www-form-urlencoded'}
sup={'usr':'******','pwd':'admin'}
url = 'http://'+pr[0]+'/api/method/login'
response = requests.get(url, data=sup, headers=headers)
qry1="select name from `"+cstr(db)+"`.`tabUser` where validity_end_date <CURDATE()"
pp2 = frappe.db.sql(qry1)
for pp in pp2:
dt=add_months(getdate(nowdate()), cint(pp1[0][0]))
vldt={}
vldt['validity_start_date']=cstr(nowdate())
vldt['validity_end_date']=cstr(dt)
url = 'http://'+pr[0]+'/api/resource/User/'+cstr(name)
response = requests.put(url, data='data='+json.dumps(vldt), headers=headers)
qry2="select name,validity_end_date from `"+cstr(db)+"`.`tabUser` where validity_end_date >=CURDATE()"
pp3 = frappe.db.sql(qry2)
for name,validity_end_date in pp3:
dt=add_months(getdate(validity_end_date), cint(pp1[0][0]))
vldt={}
vldt['validity_end_date']=cstr(dt)
url = 'http://'+pr[0]+'/api/resource/User/'+cstr(name)
response = requests.put(url, data='data='+json.dumps(vldt), headers=headers)
vldt={}
vldt['validity']='0'
url = 'http://'+pr[0]+'/api/resource/User/administrator'
response = requests.put(url, data='data='+json.dumps(vldt), headers=headers)
def create_additional_salary(employee, payroll_period, amount):
salary_date = add_months(payroll_period.start_date, random.randint(0, 11))
frappe.get_doc({
"doctype": "Additional Salary",
"employee": employee,
"company": erpnext.get_default_company(),
"salary_component": "Perfomance Bonus",
"payroll_date": salary_date,
"amount": amount,
"type": "Earning"
}).submit()
return salary_date
def test_make_sales_order_terms_not_copied(self):
from erpnext.selling.doctype.quotation.quotation import make_sales_order
quotation = frappe.copy_doc(test_records[0])
quotation.transaction_date = nowdate()
quotation.valid_till = add_months(quotation.transaction_date, 1)
quotation.insert()
quotation.submit()
sales_order = make_sales_order(quotation.name)
self.assertFalse(sales_order.get('payment_schedule'))
def create_salary_structure_assignment(employee, salary_structure, from_date=None):
if frappe.db.exists("Salary Structure Assignment", {"employee": employee}):
frappe.db.sql("""delete from `tabSalary Structure Assignment` where employee=%s""",(employee))
salary_structure_assignment = frappe.new_doc("Salary Structure Assignment")
salary_structure_assignment.employee = employee
salary_structure_assignment.base = 50000
salary_structure_assignment.variable = 5000
salary_structure_assignment.from_date = from_date or add_months(nowdate(), -1)
salary_structure_assignment.salary_structure = salary_structure
salary_structure_assignment.company = erpnext.get_default_company()
salary_structure_assignment.save(ignore_permissions=True)
salary_structure_assignment.submit()
return salary_structure_assignment
def add_age_to_filter(filters, doctype, field, date):
from frappe.utils import now_datetime, add_days, add_months, add_years, get_datetime_str
if date == "All Time":
return filters
today = now_datetime()
selected_dates = {
'Last 7 Days': [add_days(today,-6)],
'Last 30 Days': [add_days(today,-29)],
'This Month': [add_days(today, -today.day)],
'Last Month': [add_months(add_days(today, -today.day),-1), add_days(today, -today.day-1)],
'Last 3 Months': [add_months(add_days(today, -today.day),-3)],
'This Financial Year': [frappe.db.get_default("year_start_date"),frappe.db.get_default("year_end_date")],
'Last Financial Year': [add_years(frappe.db.get_default("year_start_date"), -1),
add_years(frappe.db.get_default("year_end_date"), -1)]
}[date]
if len(selected_dates)==2:
return filters + [[ doctype, field,">", get_datetime_str(selected_dates[0]) ],
[ doctype, field, "<", get_datetime_str(selected_dates[1]) ]]
else:
return filters + [[ doctype, field, ">", get_datetime_str(selected_dates[0]) ]]
def test_leave_balance_value_and_amount(self):
employee = "*****@*****.**"
leave_type = "_Test Leave Type Encashment"
# create the leave policy
leave_policy = frappe.get_doc({
"doctype": "Leave Policy",
"leave_policy_details": [{
"leave_type": leave_type,
"annual_allocation": 10
}]
}).insert()
leave_policy.submit()
# create employee, salary structure and assignment
employee = make_employee(employee)
frappe.db.set_value("Employee", employee, "leave_policy", leave_policy.name)
salary_structure = make_salary_structure("Salary Structure for Encashment", "Monthly", employee,
other_details={"leave_encashment_amount_per_day": 50})
# create the leave period and assign the leaves
leave_period = create_leave_period(add_months(today(), -3), add_months(today(), 3))
leave_period.grant_leave_allocation(employee=employee)
leave_encashment = frappe.get_doc(dict(
doctype = 'Leave Encashment',
employee = employee,
leave_type = leave_type,
leave_period = leave_period.name,
payroll_date = today()
)).insert()
self.assertEqual(leave_encashment.leave_balance, 10)
self.assertEqual(leave_encashment.encashable_days, 5)
self.assertEqual(leave_encashment.encashment_amount, 250)
leave_encashment.submit()
self.assertTrue(frappe.db.get_value("Leave Encashment", leave_encashment.name, "additional_salary"))
def validate_dates(self):
if self.number_of_months > 12:
frappe.throw("Maximum Allowed Security Charges are for 12 Months Only")
self.address = frappe.get_value("Contact", self.received_from, "address")
month = getdate(self.posting_date).month
year = getdate(self.posting_date).year
new_date = str(year) + str(month) + str(01)
self.posting_date = datetime.strptime(new_date, "%Y%m%d").date()
date = getdate(self.posting_date)
upto_date = add_months(date,self.number_of_months-1)
last_date = calendar.monthrange(upto_date.year, upto_date.month)[1]
upto_date = datetime.strptime(str(upto_date.year)+str(upto_date.month)+str(last_date), "%Y%m%d").date()
self.period_to = upto_date
self.total_amount = self.number_of_months * self.rate_per_month
def create_point_transaction(ref_link, ref_name, inv, tran_type, points, rule_details=None):
if points != 0:
tran = frappe.new_doc("Point Transaction")
tran.ref_link = ref_link
tran.ref_name = ref_name
tran.date = today()
tran.type = tran_type
tran.points = cint(points) * 1 if tran_type == 'Earned' else -1 * cint(points)
if rule_details:
tran.valied_upto = add_months(nowdate(), cint(rule_details.get('valid_upto')))
tran.invoice_number = inv
tran.rule_details = cstr(rule_details)
tran.docstatus = 1
tran.insert()
def _test(i):
self.assertEquals(i+1, frappe.db.sql("""select count(*) from `tabSales Invoice`
where recurring_id=%s and docstatus=1""", base_si.recurring_id)[0][0])
next_date = get_next_date(base_si.posting_date, no_of_months,
base_si.repeat_on_day_of_month)
manage_recurring_invoices(next_date=next_date, commit=False)
recurred_invoices = frappe.db.sql("""select name from `tabSales Invoice`
where recurring_id=%s and docstatus=1 order by name desc""",
base_si.recurring_id)
self.assertEquals(i+2, len(recurred_invoices))
new_si = frappe.get_doc("Sales Invoice", recurred_invoices[0][0])
for fieldname in ["convert_into_recurring_invoice", "recurring_type",
"repeat_on_day_of_month", "notification_email_address"]:
self.assertEquals(base_si.get(fieldname),
new_si.get(fieldname))
self.assertEquals(new_si.posting_date, unicode(next_date))
self.assertEquals(new_si.invoice_period_from_date,
unicode(add_months(base_si.invoice_period_from_date, no_of_months)))
if first_and_last_day:
self.assertEquals(new_si.invoice_period_to_date,
unicode(get_last_day(add_months(base_si.invoice_period_to_date,
no_of_months))))
else:
self.assertEquals(new_si.invoice_period_to_date,
unicode(add_months(base_si.invoice_period_to_date, no_of_months)))
return new_si
def get_alerts(request_data):
if request_data:
request_data = json.loads(request_data)
email = putil.validate_for_user_id_exists(request_data.get("user_id"))
alert = frappe.db.sql("select * from `tabAlerts` where user_id='{0}' order by creation desc limit 1".format(request_data.get("user_id")),as_dict=1)
try:
if alert:
property_search_query = putil.generate_search_query(alert[0])
new_query = property_search_query.get("query").get("bool").get("must")
new_query.append({
"range" : {
"posted_datetime" : {
"gte": alert[0].get("creation").strftime("%Y-%m-%d %H:%M:%S"),
}
}
})
property_search_query["query"]["bool"]["must"] = new_query
else:
search_query = {
"sort": [{ "posted_datetime": { "order": "desc" }}],
"query":{ "bool":{ "must":[ {"match":{ "user_id":request_data.get("user_id") } } ] } }
}
es = ElasticSearchController()
response_data, total_records = es.search_document(["request"], search_query, 1, 1)
if response_data:
last_month_date = add_months(datetime.datetime.now() ,-1).strftime("%Y-%m-%d %H:%M:%S")
property_search_query = response_data[0].get("search_query")
property_search_query = ast.literal_eval(property_search_query.encode("utf8"))
new_query = property_search_query.get("query").get("bool").get("must")
new_query.append({
"range" : {
"posted_datetime" : {
"gte":last_month_date,
}
}
})
property_search_query["query"]["bool"]["must"] = new_query
else:
raise OperationFailed("No Alerts and Request Id found against User {0}".format(email))
es = ElasticSearchController()
response_data, total_records = es.search_document(["property"], property_search_query, request_data.get("page_number",1), request_data.get("records_per_page",40))
from_record = ((request_data.get("page_number",1) - 1) * cint(request_data.get("records_per_page",40)) + 1 )
response_msg = "Property Found" if response_data else "Property not found"
no_of_pages = math.ceil(flt(total_records)/request_data.get("records_per_page",40))
return {"operation":"Search", "message":response_msg ,"total_records":total_records,"records_per_page":request_data.get("records_per_page",40),"from_record":from_record ,"to_record": from_record + len(response_data) - 1 if response_data else from_record + request_data.get("records_per_page",40) - 1 ,"data":response_data, "user_id":request_data.get("user_id"), "no_of_pages":no_of_pages}
except elasticsearch.ElasticsearchException,e:
raise ElasticSearchException(e.error)
except Exception,e:
raise e
def get_label(periodicity,to_date):
if periodicity=="Yearly":
months_to_add=-11
elif periodicity=="Half-yearly":
months_to_add=-5
elif periodicity=="Quarterly":
months_to_add=-2
from_date = add_months(to_date, months_to_add)
if periodicity=="Yearly":
label = formatdate(from_date, "YYYY")+"-"+formatdate(to_date, "YYYY")
else:
label = from_date.strftime("%b")+"-"+formatdate(to_date, "MMM YYYY")
return label
def create_proof_submission(employee, payroll_period, amount):
submission_date = add_months(payroll_period.start_date, random.randint(0, 11))
proof_submission = frappe.get_doc({
"doctype": "Employee Tax Exemption Proof Submission",
"employee": employee,
"payroll_period": payroll_period.name,
"submission_date": submission_date
})
proof_submission.append("tax_exemption_proofs", {
"exemption_sub_category": "_Test Sub Category",
"exemption_category": "_Test Category",
"type_of_proof": "Test", "amount": amount
})
proof_submission.submit()
return submission_date
def convert_deferred_revenue_to_income(start_date=None, end_date=None):
# book the expense/income on the last day, but it will be trigger on the 1st of month at 12:00 AM
if not start_date:
start_date = add_months(today(), -1)
if not end_date:
end_date = add_days(today(), -1)
# check for the sales invoice for which GL entries has to be done
invoices = frappe.db.sql_list('''
select distinct parent from `tabSales Invoice Item`
where service_start_date<=%s and service_end_date>=%s
and enable_deferred_revenue = 1 and docstatus = 1 and ifnull(amount, 0) > 0
''', (end_date, start_date))
for invoice in invoices:
doc = frappe.get_doc("Sales Invoice", invoice)
book_deferred_income_or_expense(doc, end_date)
def _get_next_start(d):
x = 1
while True:
yield add_months(d, months=x), x
x += 1
def leaseInvoiceAutoCreate():
"""Prepare data to create sales invoice from lease invoice schedule. This is called from form button as well as daily schedule"""
try:
# frappe.msgprint("Started")
lease_invoice = frappe.get_all(
"Lease Invoice Schedule",
filters={
"date_to_invoice": ['between', ("2020-01-01", today())],
"invoice_number": ""
},
fields=[
"name", "date_to_invoice", "invoice_number", "parent",
"parent", "invoice_item_group", "lease_item", "paid_by",
"currency"
],
order_by=
"parent, paid_by, invoice_item_group, date_to_invoice, currency, lease_item"
)
#frappe.msgprint("Lease being generated for " + str(lease_invoice))
row_num = 1 # to identify the 1st line of the list
prev_parent = ""
prev_customer = ""
prev_invoice_item_group = ""
prev_date_to_invoice = ""
lease_invoice_schedule_name = ""
prev_currency = ""
lease_invoice_schedule_list = []
item_dict = []
item_json = {}
# frappe.msgprint(str(lease_invoice))
for row in lease_invoice:
# frappe.msgprint(str(invoice_item.name) + " " + str(invoice_item.lease_item))
# Check if same lease, customer, invoice_item_group and date_to_invoice.
# Also should not be 1st row of the list
# frappe.msgprint(row.parent + " -- " + prev_parent + " -- " + row.paid_by + " -- " + prev_customer + " -- " + row.invoice_item_group + " -- " + prev_invoice_item_group + " -- " + str(row.date_to_invoice) + " -- " + str(prev_date_to_invoice) + " -- " + row.currency + " -- " + prev_currency)
if (not (row.parent == prev_parent and row.paid_by == prev_customer
and row.invoice_item_group == prev_invoice_item_group
and row.date_to_invoice == prev_date_to_invoice
and row.currency == prev_currency) and row_num != 1):
# frappe.msgprint("Creating invoice for: " + str(item_dict))
res = makeInvoice(invoice_item.date_to_invoice,
invoice_item.paid_by, json.dumps(item_dict),
invoice_item.currency, invoice_item.parent,
invoice_item.lease_item, invoice_item.qty,
invoice_item.schedule_start_date)
if res:
# Loop through all list invoice names that were created and update them with same invoice number
for lease_invoice_schedule_name in lease_invoice_schedule_list:
# frappe.msgprint("---")
# frappe.msgprint("The lease invoice schedule " + str(lease_invoice_schedule_name) + " would be updated with invoice number " + str(res.name))
frappe.db.set_value("Lease Invoice Schedule",
lease_invoice_schedule_name,
"invoice_number", res.name)
frappe.msgprint("Lease Invoice generated with number: " +
str(res.name))
item_dict = []
lease_invoice_schedule_list = [
] # reset the list of names of lease_invoice_schedule
item_json = {}
# Now that the invoice would be created if required, load the record for preparing item_dict
invoice_item = frappe.get_doc("Lease Invoice Schedule", row.name)
if not (invoice_item.schedule_start_date):
invoice_item.schedule_start_date = invoice_item.date_to_invoice
lease_end_date = frappe.get_value("Lease", invoice_item.parent,
"end_date")
item_json["item_code"] = invoice_item.lease_item
item_json["qty"] = invoice_item.qty
item_json["rate"] = invoice_item.rate
item_json["cost_center"] = getCostCenter(invoice_item.parent)
item_json["withholding_tax_rate"] = invoice_item.tax
# item_json["enable_deferred_revenue"] = 1 # Set it to true
item_json["service_start_date"] = str(
invoice_item.schedule_start_date)
if invoice_item.qty != int(invoice_item.qty):
#it means the last invoice for the lease that may have fraction of months
subs_end_date = lease_end_date
else:
#month qty is not fractional
subs_end_date = add_days(
add_months(invoice_item.schedule_start_date,
invoice_item.qty), -1)
item_json["service_end_date"] = str(subs_end_date)
# Append to the dictionary as a dict() so that the values for the new row can be set
item_dict.append(dict(item_json))
lease_invoice_schedule_list.append(invoice_item.name)
# Remember the values for the next round
prev_parent = invoice_item.parent
prev_customer = invoice_item.paid_by
prev_invoice_item_group = invoice_item.invoice_item_group
prev_date_to_invoice = invoice_item.date_to_invoice
prev_currency = invoice_item.currency
row_num += 1 # increment by 1
# Create the last invoice
res = makeInvoice(invoice_item.date_to_invoice, invoice_item.paid_by,
json.dumps(item_dict), invoice_item.currency,
invoice_item.parent, invoice_item.lease_item,
invoice_item.qty, invoice_item.schedule_start_date)
if res:
# Loop through all list invoice names that were created and update them with same invoice number
for lease_invoice_schedule_name in lease_invoice_schedule_list:
# frappe.msgprint("The lease invoice schedule " + str(lease_invoice_schedule_name) + " would be updated with invoice number " + str(res.name))
frappe.db.set_value("Lease Invoice Schedule",
lease_invoice_schedule_name,
"invoice_number", res.name)
frappe.msgprint("Lease Invoice generated with number: " +
str(res.name))
except Exception as e:
app_error_log(frappe.session.user, str(e))
def get_data(self):
data = []
if self.filters['based_on'] == 'Return Period':
if 'return_period' in self.filters and self.filters['return_period']:
gstr2b_conditions = [['cf_return_period','=',self.filters['return_period']]]
month_threshold = -(frappe.db.get_single_value('CD GSTR 2B Settings', 'month_threshold'))
return_period_year = int(self.filters['return_period'][-4::])
return_period_month = int(self.filters['return_period'][:2])
to_date = last_day_of_month(return_period_year, return_period_month)
if not to_date:
frappe.throw(_(f'To date not found for the PR filters'))
from_date = add_months(to_date, month_threshold)
else:
frappe.throw(_("Please select return period"))
else:
if not self.filters['from_date']:
frappe.throw(_("Please select from date"))
if not self.filters['to_date']:
frappe.throw(_("Please select to date"))
from_date = self.filters['from_date']
to_date = self.filters['to_date']
gstr2b_conditions = [['cf_document_date' ,'>=',self.filters['from_date']],
['cf_document_date' ,'<=',self.filters['to_date']]]
pr_conditions = [['bill_date' ,'>=',self.filters['from_date']],
['bill_date' ,'<=',self.filters['to_date']]]
if self.filters['view_type'] == 'Supplier View':
gstr2b_conditions.extend([
['cf_company_gstin', '=', self.filters['company_gstin']]])
if 'transaction_type' in self.filters:
gstr2b_conditions.append(['cf_transaction_type' ,'=', self.filters['transaction_type']])
gstr2b_entries = frappe.db.get_all('CD GSTR 2B Entry', filters= gstr2b_conditions, fields =['cf_party_gstin','cf_party', 'cf_tax_amount', 'cf_purchase_invoice', 'cf_status'])
if not self.filters['based_on'] == 'Return Period':
pr_conditions.extend([
['docstatus' ,'=', 1],
['company_gstin', '=', self.filters['company_gstin']]])
pr_entries = frappe.db.get_all('Purchase Invoice', filters=pr_conditions, fields =['supplier_gstin', 'supplier', 'name'])
else:
pr_entries = []
for entry in gstr2b_entries:
if entry['cf_purchase_invoice']:
doc = frappe.get_doc('Purchase Invoice', entry['cf_purchase_invoice'])
pr_entries.append({'supplier_gstin':doc.supplier_gstin,
'supplier':doc.supplier,
'name':doc.name})
gstin_wise_data = {}
for entry in gstr2b_entries:
if not entry['cf_party_gstin'] in gstin_wise_data:
gstin_wise_data[entry['cf_party_gstin']] = [entry['cf_party'], entry['cf_tax_amount'], 0]
else:
gstin_wise_data[entry['cf_party_gstin']][1] += entry['cf_tax_amount']
if not 'transaction_type' in self.filters or \
self.filters['transaction_type'] == 'Invoice':
for entry in pr_entries:
if not entry['supplier_gstin'] in gstin_wise_data:
gstin_wise_data[entry['supplier_gstin']] = [entry['supplier'], 0, get_tax_details(entry['name'])['total_tax_amount']]
else:
gstin_wise_data[entry['supplier_gstin']][2] += get_tax_details(entry['name'])['total_tax_amount']
for key in gstin_wise_data.keys():
total_2b = len([entry for entry in gstr2b_entries if entry['cf_party_gstin'] == key])
total_pr = len([entry for entry in pr_entries if entry['supplier_gstin'] == key])
total_pending = len([entry for entry in gstr2b_entries if entry['cf_party_gstin'] == key and entry['cf_status'] == 'Pending'])
if total_pr > total_2b:
total_pending += total_pr - total_2b
row = { 'supplier': gstin_wise_data[key][0],
'gstin': key,
'tax_difference': round(abs(gstin_wise_data[key][1]- gstin_wise_data[key][2]), 2),
'total_2b': total_2b,
'total_pr': total_pr,
'total_pending_documents': total_pending}
data.append(row)
else:
match_status = ["Exact Match", "Partial Match", "Probable Match", "Mismatch", "Missing in PR", "Missing in 2B"]
document_status = ['Pending', 'Accepted']
if 'match_status' in self.filters:
match_status = [self.filters['match_status']]
if 'document_status' in self.filters:
document_status = [self.filters['document_status']]
if 'supplier' in self.filters:
suppliers = [self.filters['supplier']]
gstr2b_conditions.extend([
['cf_status', 'in', document_status],
['cf_match_status','in', match_status],
['cf_company_gstin', '=', self.filters['company_gstin']]])
if 'transaction_type' in self.filters:
gstr2b_conditions.append(['cf_transaction_type' ,'=', self.filters['transaction_type']])
if 'supplier' in self.filters and not 'supplier_gstin' in self.filters:
gstr2b_conditions.append(['cf_party', 'in', suppliers])
if not 'supplier' in self.filters and not 'supplier_gstin' in self.filters:
self.columns +=[{
"label": "Supplier",
"fieldname": "supplier",
"fieldtype": "Link",
"options": "Supplier",
"width": 200
},
{
"label": "GSTIN",
"fieldname": "gstin",
"fieldtype": "Link",
"options": "Supplier",
"width": 140
}]
if 'supplier_gstin' in self.filters:
gstr2b_conditions.append(['cf_party_gstin', '=', self.filters['supplier_gstin']])
self.columns += [{
"label": "2B Inv No",
"fieldname": "2b_invoice_no",
"fieldtype": "Data",
"width": 150
},
{
"label": "PR Inv No",
"fieldname": "pr_invoice_no",
"fieldtype": "Data",
"width": 100
},
{
"label": "2B Inv Date",
"fieldname": "2b_invoice_date",
"fieldtype": "Data",
"width": 90
},
{
"label": "PR Inv Date",
"fieldname": "pr_invoice_date",
"fieldtype": "Data",
"width": 90
},
{
"label": "2B Taxable Amt",
"fieldname": "2b_taxable_value",
"fieldtype": "Float",
"width": 110
},
{
"label": "PR Taxable Amt",
"fieldname": "pr_taxable_value",
"fieldtype": "Float",
"width": 110
},
{
"label": "Tax Diff",
"fieldname": "tax_difference",
"fieldtype": "Float",
"width": 70
},
{
"label": "Match Status",
"fieldname": "match_status",
"fieldtype": "Data",
"width": 95
},
{
"label": "Reason",
"fieldname": "reason",
"fieldtype": "Data",
"width": 150
},
{
"label": "Status",
"fieldname": "status",
"fieldtype": "Data",
"width": 70
},
{
"label": "PR Actions",
"fieldname": "pr_actions",
"fieldtype": "HTML",
"width": 100
}]
gstr2b_entries = frappe.db.get_all('CD GSTR 2B Entry', filters= gstr2b_conditions, fields =['cf_document_number','cf_document_date', 'cf_party_gstin',
'cf_purchase_invoice', 'cf_match_status', 'cf_reason', 'cf_status', 'cf_tax_amount','cf_taxable_amount', 'name', 'cf_party'])
for entry in gstr2b_entries:
bill_details = frappe.db.get_value("Purchase Invoice", {'name':entry['cf_purchase_invoice']}, ['bill_no', 'bill_date', 'total'])
button = f"""
<div>
<Button class="btn btn-primary btn-xs left" style="margin: 2px;" gstr2b = {entry["name"]} purchase_inv ={entry["cf_purchase_invoice"]} onClick='update_status(this.getAttribute("gstr2b"), this.getAttribute("purchase_inv"))'>View</a>
<Button class="btn btn-primary btn-xs right" style="margin: 2px;" gstr2b = {entry["name"]} status = {entry['cf_status']} onClick='unlink_pr(this.getAttribute("gstr2b"), this.getAttribute("status"))'>Unlink</a>
</div>"""
if 'Missing in PR' == entry['cf_match_status']:
button = f"""<div><Button class="btn btn-primary btn-xs left" style="margin: 2px;" gstr2b = {entry["name"]} purchase_inv ={entry["cf_purchase_invoice"]} onClick='create_purchase_inv(this.getAttribute("gstr2b"), this.getAttribute("purchase_inv"))'>View</a>
<Button class="btn btn-primary btn-xs right" style="margin: 2px;" gstr2b = {entry["name"]} from_date = {from_date} to_date = {to_date} onClick='get_unlinked_pr_list(this.getAttribute("gstr2b"), this.getAttribute("from_date"), this.getAttribute("to_date"))'>Link</a>
</div>"""
tax_diff = entry['cf_tax_amount']
if entry['cf_purchase_invoice']:
tax_diff = round(abs(entry['cf_tax_amount']- get_tax_details(entry['cf_purchase_invoice'])['total_tax_amount']), 2)
data.append({
'supplier': entry['cf_party'],
'gstin': entry['cf_party_gstin'],
'2b_invoice_no': entry['cf_document_number'],
'2b_invoice_date': entry['cf_document_date'],
'pr_invoice_no': bill_details[0] if bill_details and bill_details[0] else None,
'pr_invoice_date': bill_details[1] if bill_details and bill_details[1] else None,
'tax_difference': tax_diff,
'2b_taxable_value': entry['cf_taxable_amount'],
'pr_taxable_value': bill_details[2] if bill_details and bill_details[2] else None,
'match_status': entry['cf_match_status'],
'reason':entry['cf_reason'],
'status': entry['cf_status'],
'pr_actions': button,
'gstr_2b': entry['name']})
if len(document_status) != 1 and 'Missing in 2B' in match_status and self.filters['based_on'] == 'Date':
if not 'transaction_type' in self.filters or \
self.filters['transaction_type'] == 'Invoice':
pr_conditions.extend([
['docstatus' ,'=', 1],
['company_gstin', '=', self.filters['company_gstin']]])
if 'supplier' in self.filters and not 'supplier_gstin' in self.filters:
pr_conditions.append(['supplier' ,'in', suppliers])
if 'supplier_gstin' in self.filters:
pr_conditions.append(['supplier_gstin' ,'=', self.filters['supplier_gstin']])
pr_entries = frappe.db.get_all('Purchase Invoice', filters=pr_conditions, fields =['name', 'bill_no', 'bill_date', 'total', 'supplier_gstin', 'supplier'])
for inv in pr_entries:
is_linked = frappe.db.get_value('CD GSTR 2B Entry', {'cf_purchase_invoice': inv['name']}, 'name')
if not is_linked:
tax_diff = get_tax_details(inv['name'])['total_tax_amount']
button = f"""<Button class="btn btn-primary btn-xs center" gstr2b = '' purchase_inv ={inv["name"]} onClick='render_summary(this.getAttribute("gstr2b"), this.getAttribute("purchase_inv"))'>View</a>"""
data.append({
'supplier': inv['supplier'],
'gstin': inv['supplier_gstin'],
'2b_invoice_no': None,
'2b_invoice_date': None,
'pr_invoice_no': inv['bill_no'],
'pr_invoice_date': inv['bill_date'],
'tax_difference': tax_diff,
'2b_taxable_value': None,
'pr_taxable_value': inv['total'],
'match_status': 'Missing in 2B',
'reason':None,
'status': None,
'pr_actions': button})
self.data = data
def get_total_pi(filters):
filters = json.loads(filters)
if (filters["period_type"] == "Month"):
#convert from_date
from_date = get_first_day(filters["period_num"] + '-' +
filters["year"])
filters["to_date"] = get_last_day(filters["period_num"] + '-' +
filters["year"])
#frappe.msgprint(filters["from_date)
if (filters["period_type"] == "Quarter"):
#convert from_date
period_num = "1"
if (filters["period_num"] == "2"):
period_num = "4"
if (filters["period_num"] == "3"):
period_num = "7"
if (filters["period_num"] == "4"):
period_num = "10"
filters["from_date"] = get_first_day(period_num + '-' +
filters["year"])
filters["to_date"] = add_months(filters["from_date"], 3)
filters["to_date"] = add_days(filters["to_date"], -1)
#Purchase Invoice
query = """ SELECT name, tax_rate FROM `tabAccount`
WHERE `account_type`='Tax'
AND `freeze_account`='No'
AND `is_group` = 0
AND `name` LIKE '133%'
ORDER BY name """
listAcount = frappe.db.sql(query, as_list=1)
arrAcount = []
arrTaxRate = []
for i in range(0, len(listAcount)):
arrAcount.append(listAcount[i][0])
arrTaxRate.append(listAcount[i][1])
conditions = get_conditions(filters)
data = []
total_pi = 0
for i in range(0, len(arrAcount)):
rate_name = arrAcount[i]
tax_rate = arrTaxRate[i]
query = """SELECT si_tax.account_head,
sum(si.base_net_total), %d, sum(si_tax.base_tax_amount)
FROM `tabPurchase Invoice` si, `tabPurchase Taxes and Charges` si_tax
WHERE si.docstatus = 1 AND si_tax.parent = si.name AND si_tax.account_head = '%s'
%s
GROUP BY si_tax.account_head
""" % (tax_rate, rate_name, conditions)
row = frappe.db.sql(query, as_list=1)
if (row):
data.append(row[0])
total_pi = total_pi + row[0][1]
else:
data.append([rate_name, 0, tax_rate, 0])
#Sales Invoice
query = """ SELECT name, tax_rate FROM `tabAccount`
WHERE `account_type`='Tax'
AND `freeze_account`='No'
AND `is_group` = 0
AND `name` LIKE '3331%'
ORDER BY name """
listAcount = frappe.db.sql(query, as_list=1)
arrAcount = []
arrTaxRate = []
for i in range(0, len(listAcount)):
arrAcount.append(listAcount[i][0])
arrTaxRate.append(listAcount[i][1])
for i in range(0, len(arrAcount)):
rate_name = arrAcount[i]
tax_rate = arrTaxRate[i]
query = """SELECT si_tax.account_head,
sum(si.base_net_total), %d, sum(si_tax.base_tax_amount)
FROM `tabSales Invoice` si, `tabSales Taxes and Charges` si_tax
WHERE si.docstatus = 1 AND si_tax.parent = si.name AND si_tax.account_head = '%s'
%s
GROUP BY si_tax.account_head
""" % (tax_rate, rate_name, conditions)
row = frappe.db.sql(query, as_list=1)
if (row):
data.append(row[0])
else:
data.append([rate_name, 0, tax_rate, 0])
result = {
'total_pi': total_pi,
'total_tax_pi': data[2][3] + data[3][3],
'data': data
}
return result
def test_loan(self):
branch = "Test Employee Branch"
applicant = make_employee("*****@*****.**",
company="_Test Company")
company = "_Test Company"
holiday_list = make_holiday("test holiday for loan")
company_doc = frappe.get_doc('Company', company)
if not company_doc.default_payroll_payable_account:
company_doc.default_payroll_payable_account = frappe.db.get_value(
'Account', {
'company': company,
'root_type': 'Liability',
'account_type': ''
}, 'name')
company_doc.save()
if not frappe.db.exists('Branch', branch):
frappe.get_doc({'doctype': 'Branch', 'branch': branch}).insert()
employee_doc = frappe.get_doc('Employee', applicant)
employee_doc.branch = branch
employee_doc.holiday_list = holiday_list
employee_doc.save()
salary_structure = "Test Salary Structure for Loan"
make_salary_structure(salary_structure,
"Monthly",
employee=employee_doc.name,
company="_Test Company",
currency=company_doc.default_currency)
loan = create_loan(applicant,
"Car Loan",
280000,
"Repay Over Number of Periods",
20,
posting_date=add_months(nowdate(), -1))
loan.repay_from_salary = 1
loan.submit()
make_loan_disbursement_entry(loan.name,
loan.loan_amount,
disbursement_date=add_months(
nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
dates = get_start_end_dates('Monthly', nowdate())
make_payroll_entry(
company="_Test Company",
start_date=dates.start_date,
payable_account=company_doc.default_payroll_payable_account,
currency=company_doc.default_currency,
end_date=dates.end_date,
branch=branch,
cost_center="Main - _TC",
payment_account="Cash - _TC")
name = frappe.db.get_value('Salary Slip', {
'posting_date': nowdate(),
'employee': applicant
}, 'name')
salary_slip = frappe.get_doc('Salary Slip', name)
for row in salary_slip.loans:
if row.loan == loan.name:
interest_amount = (280000 * 8.4) / (12 * 100)
principal_amount = loan.monthly_repayment_amount - interest_amount
self.assertEqual(row.interest_amount, interest_amount)
self.assertEqual(row.principal_amount, principal_amount)
self.assertEqual(row.total_payment,
interest_amount + principal_amount)
if salary_slip.docstatus == 0:
frappe.delete_doc('Salary Slip', name)
def create_billing_from_events(self):
sub_events = frappe.db.sql(
'''select name, event_date, qty
from `tabSubscription Billing Events`
where subscription_billing = %s and event_date<=%s and event_date>=%s
and billed_amount = 0
order by event_date asc''',
(self.name, today(), add_months(today(), -1)),
as_dict=1)
booking_start_date = getdate(add_months(today(), -1))
booking_start_date = booking_start_date if booking_start_date > self.start_date else self.start_date
booking_end_date = getdate(add_days(today(), -1))
if booking_end_date >= self.end_date:
booking_end_date = self.end_date
total_days = date_diff(self.end_date, self.start_date)
max_count = len(sub_events)
count = 0
base_amount = 0
#validation in events doc type that new event date being entered is greater than previous end date
events = []
event_name = ''
for sub_dict in sub_events:
event_name = sub_dict["name"]
count += 1
if count == 1:
calc_amount = 0
total_booking_days = date_diff(sub_dict["event_date"],
booking_start_date)
calc_amount = flt(self.rate * self.qty * total_booking_days /
flt(total_days)) * 12
base_amount += calc_amount
events.append({
'start_date': booking_start_date,
'end_date': sub_dict["event_date"],
'qty': self.qty,
'rate': calc_amount / self.qty
})
else:
calc_amount = 0
total_booking_days = date_diff(sub_dict["event_date"],
last_event_end_date)
calc_amount = flt(self.rate * last_event_qty *
total_booking_days / flt(total_days)) * 12
base_amount += calc_amount
events.append({
'start_date': last_event_end_date,
'end_date': sub_dict["event_date"],
'qty': last_event_qty,
'rate': calc_amount / last_event_qty
})
last_event_end_date = sub_dict["event_date"]
last_event_qty = sub_dict["qty"]
last_event_amount = 0
count = 0
for sub_dict in sub_events:
count += 1
if count == max_count:
calc_amount = 0
total_booking_days = date_diff(booking_end_date,
sub_dict["event_date"])
calc_amount = flt(self.rate * sub_dict["qty"] *
total_booking_days / flt(total_days)) * 12
last_event_amount += calc_amount
events.append({
'start_date': sub_dict["event_date"],
'end_date': booking_end_date,
'qty': sub_dict["qty"],
'rate': calc_amount / sub_dict["qty"]
})
if base_amount > 0:
si_doc = frappe.new_doc('Sales Invoice')
si_doc.company = self.company
si_doc.currency = frappe.db.get_value("Company", self.company,
"default_currency")
si_doc.customer = self.customer
si_doc.posting_date = booking_end_date
si_doc.due_date = booking_end_date
for event in events:
si_doc.append(
"items", {
"item_code": self.item,
"qty": event['qty'],
"rate": event['rate'],
"service_start_date": event['start_date'],
"service_end_date": event['end_date']
})
si_doc.set_missing_values()
si_doc.flags.ignore_mandatory = True
si_doc.insert(ignore_permissions=True)
event_doc = frappe.get_doc("Subscription Billing Events",
event_name)
event_doc.billed_amount = base_amount
event_doc.save()
def get_repeat_events(e):
if e.repeat_this_event:
e.starts_on = get_datetime_str(e.starts_on)
if e.ends_on:
e.ends_on = get_datetime_str(e.ends_on)
event_start, time_str = get_datetime_str(
e.starts_on).split(" ")
if cstr(e.repeat_till) == "":
repeat = max_repeat_till
else:
repeat = e.repeat_till
if e.repeat_on == "Every Year":
start_year = cint(start.split("-")[0])
end_year = cint(end.split("-")[0])
event_start = "-".join(event_start.split("-")[1:])
# repeat for all years in period
for year in range(start_year, end_year + 1):
date = str(year) + "-" + event_start
if getdate(date) >= getdate(start) and getdate(
date) <= getdate(end) and getdate(
date) <= getdate(repeat):
add_event(e, date)
remove_events.append(e)
if e.repeat_on == "Every Month":
date = start.split("-")[0] + "-" + start.split(
"-")[1] + "-" + event_start.split("-")[2]
# last day of month issue, start from prev month!
try:
getdate(date)
except ValueError:
date = date.split("-")
date = date[0] + "-" + str(cint(date[1]) -
1) + "-" + date[2]
start_from = date
for i in range(int(date_diff(end, start) / 30) + 3):
if getdate(date) >= getdate(start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and getdate(date) >= getdate(event_start):
add_event(e, date)
date = add_months(start_from, i + 1)
remove_events.append(e)
if e.repeat_on == "Every Week":
weekday = getdate(event_start).weekday()
# monday is 0
start_weekday = getdate(start).weekday()
# start from nearest weeday after last monday
date = add_days(start, weekday - start_weekday)
for cnt in range(int(date_diff(end, start) / 7) + 3):
if getdate(date) >= getdate(start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and getdate(date) >= getdate(event_start):
add_event(e, date)
date = add_days(date, 7)
remove_events.append(e)
if e.repeat_on == "Every Day":
for cnt in range(date_diff(end, start) + 1):
date = add_days(start, cnt)
if getdate(date) >= getdate(event_start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and e[weekdays[getdate(date).weekday()]]:
add_event(e, date)
remove_events.append(e)
