def get_report_data(self):
from_date_year = self.get_year(add_days(self.doc.from_date, -1))
to_date_year = self.get_year(self.doc.to_date)
# result initiatlization
header = 'Ledger Balances Between ' + getdate(self.doc.from_date).strftime('%d-%m-%Y') + ' and ' + getdate(self.doc.to_date).strftime('%d-%m-%Y')
res = [[header], ['Account', 'Posting Date', 'Voucher Type', 'Voucher No', 'Debit', 'Credit', 'Remarks']]
glc = get_obj('GL Control')
for d in getlist(self.doclist, 'ledger_details'):
# Fetch acc details
acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt from tabAccount where name = '%s'" % d.account, as_dict=1)[0]
# Opening
opening = glc.get_as_on_balance(d.account, from_date_year, add_days(self.doc.from_date, -1), acc_det['debit_or_credit'], acc_det['lft'], acc_det['rgt'])[2]
if acc_det['debit_or_credit'] == 'Credit':
opening = -1*opening
# GL Entries
gle, debit, credit = self.get_gl_entries(acc_det['lft'], acc_det['rgt'])
# Closing
closing = opening + debit - credit
# Append to result
res.append([d.account])
res += gle
res.append(['', '', '', 'Total Debit/Credit', debit, credit])
res.append(['', '', '', 'Opening Balance', opening])
res.append(['', '', '', 'Closing Balance', closing])
return res
def get_weekly_off_date_list(self, yr_start_date, yr_end_date):
days_dict, dt_list, lst_st = {
'Monday': 0,
'Tuesday': 1,
'Wednesday': 2,
'Thursday': 3,
'Friday': 4,
'Saturday': 5,
'Sunday': 6
}, [], ''
w = cint(days_dict[self.doc.weekly_off]) # Weekly Off Day No.
st_dt_weekday = getdate(
yr_start_date).weekday() # Year Start Date weekday()
if w == st_dt_weekday: # Get Start Date
lst_st = yr_start_date
dt_list.append(lst_st)
elif w > st_dt_weekday:
lst_st = add_days(yr_start_date, w - st_dt_weekday)
dt_list.append(lst_st)
else:
lst_st = add_days(yr_start_date, 6 - st_dt_weekday + 1)
dt_list.append(lst_st)
while getdate(lst_st) < getdate(yr_end_date): # Get list of dates
lst_st = add_days(lst_st, 7)
if getdate(lst_st) > getdate(yr_end_date):
break
dt_list.append(lst_st)
return dt_list
def get_weekly_off_date_list(self, yr_start_date, yr_end_date):
days_dict, dt_list, lst_st = (
{"Monday": 0, "Tuesday": 1, "Wednesday": 2, "Thursday": 3, "Friday": 4, "Saturday": 5, "Sunday": 6},
[],
"",
)
w = cint(days_dict[self.doc.weekly_off]) # Weekly Off Day No.
st_dt_weekday = getdate(yr_start_date).weekday() # Year Start Date weekday()
if w == st_dt_weekday: # Get Start Date
lst_st = yr_start_date
dt_list.append(lst_st)
elif w > st_dt_weekday:
lst_st = add_days(yr_start_date, w - st_dt_weekday)
dt_list.append(lst_st)
else:
lst_st = add_days(yr_start_date, 6 - st_dt_weekday + 1)
dt_list.append(lst_st)
while getdate(lst_st) < getdate(yr_end_date): # Get list of dates
lst_st = add_days(lst_st, 7)
if getdate(lst_st) > getdate(yr_end_date):
break
dt_list.append(lst_st)
return dt_list
def get_quarterly_trend_details(self):
first_qsd, second_qsd, third_qsd, fourth_qsd = self.year_start_date, add_months(
self.year_start_date,
3), add_months(self.year_start_date,
6), add_months(self.year_start_date, 9)
first_qed, second_qed, third_qed, fourth_qed = add_days(
add_months(first_qsd, 3),
-1), add_days(add_months(second_qsd, 3), -1), add_days(
add_months(third_qsd, 3),
-1), add_days(add_months(fourth_qsd, 3), -1)
col_names = [
'Q1 (Qty)', 'Q1 (Amt)', 'Q2 (Qty)', 'Q2 (Amt)', 'Q3 (Qty)',
'Q3 (Amt)', 'Q4 (Qty)', 'Q4 (Amt)'
]
query_val = ''
bet_dates = [[first_qsd, first_qed], [second_qsd, second_qed],
[third_qsd, third_qed], [fourth_qsd, fourth_qed]]
for d in bet_dates:
query_val += 'SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + d[
0] + '" AND "' + d[
1] + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + d[
0] + '" AND "' + d[
1] + '" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_default_schedule_date( self, obj):
for d in getlist( obj.doclist, obj.fname):
item = sql("select lead_time_days from `tabItem` where name = '%s' and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now())" % cstr(d.item_code) , as_dict = 1)
ltd = item and cint(item[0]['lead_time_days']) or 0
if ltd and obj.doc.transaction_date:
if d.fields.has_key('lead_time_date') or obj.doc.doctype == 'Purchase Request':
d.lead_time_date = cstr(add_days( obj.doc.transaction_date, cint(ltd)))
if not d.fields.has_key('prevdoc_docname') or (d.fields.has_key('prevdoc_docname') and not d.prevdoc_docname):
d.schedule_date = cstr( add_days( obj.doc.transaction_date, cint(ltd)))
def get_default_schedule_date( self, obj):
for d in getlist( obj.doclist, obj.fname):
item = sql("select lead_time_days from `tabItem` where name = '%s' and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now())" % cstr(d.item_code) , as_dict = 1)
ltd = item and cint(item[0]['lead_time_days']) or 0
if ltd and obj.doc.transaction_date:
if d.fields.has_key('lead_time_date') or obj.doc.doctype == 'Purchase Request':
d.lead_time_date = cstr(add_days( obj.doc.transaction_date, cint(ltd)))
if not d.fields.has_key('prevdoc_docname') or (d.fields.has_key('prevdoc_docname') and not d.prevdoc_docname):
d.schedule_date = cstr( add_days( obj.doc.transaction_date, cint(ltd)))
def get_half_yearly_trend_details(self):
first_half_start = self.year_start_date
first_half_end = add_days(add_months(first_half_start,6),-1)
second_half_start = add_days(first_half_end,1)
second_half_end = add_days(add_months(second_half_start,6),-1)
col_names = ['First Half (Qty)', 'First Half (Amt)', 'Second Half (Qty)', 'Second Half (Amt)']
query_val = 'SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+first_half_start+'" AND "'+first_half_end+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+first_half_start+'" AND "'+first_half_end+'" THEN t2.amount ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+second_half_start+'" AND "'+second_half_end+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+second_half_start+'" AND "'+second_half_end+'" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_half_yearly_trend_details(self):
first_half_start = self.year_start_date
first_half_end = add_days(add_months(first_half_start, 6), -1)
second_half_start = add_days(first_half_end, 1)
second_half_end = add_days(add_months(second_half_start, 6), -1)
col_names = [
'First Half (Qty)', 'First Half (Amt)', 'Second Half (Qty)',
'Second Half (Amt)'
]
query_val = 'SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + first_half_start + '" AND "' + first_half_end + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + first_half_start + '" AND "' + first_half_end + '" THEN t2.amount ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + second_half_start + '" AND "' + second_half_end + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + second_half_start + '" AND "' + second_half_end + '" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def test_save_journal_voucher(self):
expense_ac_balance = get_balance_on(get_name("Test Expense"), nowdate())
supplier_ac_balance = get_balance_on(get_name("Test Supplier"), nowdate())
dl = webnotes.model.insert(data["journal_voucher"])
dl.submit()
dl.load_from_db()
# test submitted jv
self.assertTrue(webnotes.conn.exists("Journal Voucher", dl.doclist[0].name))
for d in dl.doclist[1:]:
self.assertEquals(webnotes.conn.get_value("Journal Voucher Detail",
d.name, "parent"), dl.doclist[0].name)
# test gl entry
gle = webnotes.conn.sql("""select account, debit, credit
from `tabGL Entry` where voucher_no = %s order by account""",
dl.doclist[0].name)
self.assertEquals((gle[0][0], flt(gle[0][1]), flt(gle[0][2])),
('Test Expense - %s' % abbr, 5000.0, 0.0))
self.assertEquals((gle[1][0], flt(gle[1][1]), flt(gle[1][2])),
('Test Supplier - %s' % abbr, 0.0, 5000.0))
# check balance as on today
self.assertEqual(get_balance_on(get_name("Test Expense"), nowdate()),
expense_ac_balance + 5000)
self.assertEqual(get_balance_on(get_name("Test Supplier"), nowdate()),
supplier_ac_balance + 5000)
# check previous balance
self.assertEqual(get_balance_on(get_name("Test Expense"), add_days(nowdate(), -1)), 0)
def send_daily_summary(for_date=None, event_date=None):
if not for_date:
for_date = add_days(today(), days=-1)
if not event_date:
event_date = today()
formatted_date = getdate(for_date).strftime("%a, %d %B, %Y")
formatted_event_date = getdate(event_date).strftime("%a, %d %B, %Y")
subject = "[AAP Ka Manch] Updates for {formatted_date}".format(formatted_date=formatted_date)
unit_post_map = get_unit_post_map(for_date, event_date)
if not unit_post_map:
# no updates!
return
for user in webnotes.conn.sql_list("""select name from `tabProfile`
where user_type='Website User' and enabled=1 and name not in ('Administrator', 'Guest')"""):
summary = prepare_daily_summary(user, unit_post_map, {"subject": subject, "formatted_date": formatted_date,
"formatted_event_date": formatted_event_date})
if not summary:
# no access!
continue
send(recipients=[user],
subject=subject,
message=summary,
# to allow unsubscribe
doctype='Profile',
email_field='name',
# for tracking sent status
ref_doctype="Profile", ref_docname=user)
def get_as_on_balance(self, account_name, fiscal_year, as_on, credit_or_debit, lft, rgt):
# initialization
det = webnotes.conn.sql("select start_date, opening from `tabAccount Balance` where period = %s and account = %s", (fiscal_year, account_name))
from_date, opening, debit_bal, credit_bal, closing_bal = det and det[0][0] or getdate(nowdate()), det and flt(det[0][1]) or 0, 0, 0, det and flt(det[0][1]) or 0
# prev month closing
prev_month_det = webnotes.conn.sql("select end_date, debit, credit, balance from `tabAccount Balance` where account = %s and end_date <= %s and fiscal_year = %s order by end_date desc limit 1", (account_name, as_on, fiscal_year))
if prev_month_det:
from_date = getdate(add_days(prev_month_det[0][0].strftime('%Y-%m-%d'), 1))
opening = 0
debit_bal = flt(prev_month_det[0][1])
credit_bal = flt(prev_month_det[0][2])
closing_bal = flt(prev_month_det[0][3])
# curr month transaction
if getdate(as_on) >= from_date:
curr_month_bal = webnotes.conn.sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s and ifnull(t1.is_opening, 'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_cancelled, 'No') = 'No'", (from_date, as_on, lft, rgt))
curr_debit_amt, curr_credit_amt = flt(curr_month_bal[0][0]), flt(curr_month_bal[0][1])
debit_bal = curr_month_bal and debit_bal + curr_debit_amt or debit_bal
credit_bal = curr_month_bal and credit_bal + curr_credit_amt or credit_bal
if credit_or_debit == 'Credit':
curr_debit_amt, curr_credit_amt = -1*flt(curr_month_bal[0][0]), -1*flt(curr_month_bal[0][1])
closing_bal = closing_bal + curr_debit_amt - curr_credit_amt
return flt(debit_bal), flt(credit_bal), flt(closing_bal)
def set_sales_details(self):
delivery_sle = webnotes.conn.sql(
"""select * from `tabStock Ledger Entry`
where (serial_no like %s or serial_no like %s or serial_no=%s)
and item_code=%s and actual_qty<0
and voucher_type in ('Delivery Note', 'Sales Invoice')
and ifnull(is_cancelled, 'No')='No' order by name desc limit 1""",
("%%%s%%" % (self.doc.name + "\n"), "%%%s%%" %
("\n" + self.doc.name), self.doc.name, self.doc.item_code),
as_dict=1)
if delivery_sle:
self.doc.delivery_document_type = delivery_sle[0].voucher_type
self.doc.delivery_document_no = delivery_sle[0].voucher_no
self.doc.delivery_date = delivery_sle[0].posting_date
self.doc.delivery_time = delivery_sle[0].posting_time
self.doc.customer, self.doc.customer_name = \
webnotes.conn.get_value(delivery_sle[0].voucher_type, delivery_sle[0].voucher_no,
["customer", "customer_name"])
if self.doc.warranty_period:
self.doc.warranty_expiry_date = add_days(
cstr(delivery_sle[0].posting_date),
cint(self.doc.warranty_period))
else:
for fieldname in ("delivery_document_type", "delivery_document_no",
"delivery_date", "delivery_time", "customer",
"customer_name", "warranty_expiry_date"):
self.doc.fields[fieldname] = None
def get_fy_start_end_dates(self):
st_date = sql(
"select year_start_date from `tabFiscal Year` where name = '%s'" %
(self.doc.fiscal_year))
st_date = st_date and st_date[0][0].strftime('%Y-%m-%d') or ''
ed_date = add_days(add_years(st_date, 1), -1)
return st_date, ed_date
def calculate_lwp(self, m):
holidays = sql(
"select t1.holiday_date from `tabHoliday` t1, tabEmployee t2 where t1.parent = t2.holiday_list and t2.name = '%s' and t1.holiday_date between '%s' and '%s'"
% (self.doc.employee, m["month_start_date"], m["month_end_date"])
)
if not holidays:
holidays = sql(
"select t1.holiday_date from `tabHoliday` t1, `tabHoliday List` t2 where t1.parent = t2.name and ifnull(t2.is_default, 0) = 1 and t2.fiscal_year = '%s'"
% self.doc.fiscal_year
)
holidays = [cstr(i[0]) for i in holidays]
lwp = 0
for d in range(m["month_days"]):
dt = add_days(cstr(m["month_start_date"]), d)
if dt not in holidays:
leave = sql(
"""
select t1.name, t1.half_day
from `tabLeave Application` t1, `tabLeave Type` t2
where t2.name = t1.leave_type
and ifnull(t2.is_lwp, 0) = 1
and t1.docstatus = 1
and t1.employee = '%s'
and '%s' between from_date and to_date
"""
% (self.doc.employee, dt)
)
if leave:
lwp = cint(leave[0][1]) and lwp + 0.5 or lwp + 1
return lwp
def get_acc_summary(self, glc, acc_det):
from_date_year = self.get_year(add_days(self.doc.from_date, -1))
to_date_year = self.get_year(self.doc.to_date)
acc = acc_det['account'].strip()
if from_date_year == to_date_year:
debit_on_fromdate, credit_on_fromdate, opening = glc.get_as_on_balance(acc, from_date_year, add_days(self.doc.from_date, -1), acc_det['dr_or_cr'], acc_det['lft'], acc_det['rgt']) # opening = closing of prev_date
elif acc_det['is_pl_account'] == 'No': # if there is no previous year in system and not pl account
opening = sql("select opening from `tabAccount Balance` where account = %s and period = %s", (acc, to_date_year))
debit_on_fromdate, credit_on_fromdate, opening = 0, 0, flt(opening[0][0])
else: # if pl account and there is no previous year in system
debit_on_fromdate, credit_on_fromdate, opening = 0,0,0
# closing balance
#--------------------------------
debit_on_todate, credit_on_todate, closing = glc.get_as_on_balance(acc, to_date_year, self.doc.to_date, acc_det['dr_or_cr'], acc_det['lft'], acc_det['rgt'])
# transaction betn the period
#----------------------------------------
debit = flt(debit_on_todate) - flt(debit_on_fromdate)
credit = flt(credit_on_todate) - flt(credit_on_fromdate)
# Debit / Credit
if acc_det['dr_or_cr'] == 'Credit':
opening, closing = -1*opening, -1*closing
return flt(opening>0 and opening or 0), flt(opening<0 and -opening or 0), \
debit, credit, flt(closing>0.01 and closing or 0), flt(closing<-0.01 and -closing or 0)
def create_new_invoice(prev_rv):
# clone rv
new_rv = clone(prev_rv)
mdict = {'Monthly': 1, 'Quarterly': 3, 'Half-yearly': 6, 'Yearly': 12}
mcount = mdict[prev_rv.doc.recurring_type]
# update new rv
new_rv.doc.posting_date = new_rv.doc.next_date
new_rv.doc.aging_date = new_rv.doc.next_date
new_rv.doc.due_date = add_days(
new_rv.doc.next_date,
cint(date_diff(prev_rv.doc.due_date, prev_rv.doc.posting_date)))
new_rv.doc.invoice_period_from_date = get_next_date(
new_rv.doc.invoice_period_from_date, mcount)
new_rv.doc.invoice_period_to_date = get_next_date(
new_rv.doc.invoice_period_to_date, mcount)
new_rv.doc.owner = prev_rv.doc.owner
new_rv.doc.save()
# submit and after submit
new_rv.submit()
new_rv.update_after_submit()
return new_rv
def reorder_indent(self,i,item_reorder_level,doc_type,doc_name,email_notify=1):
indent = Document('Indent')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Indent. It was raised because the projected quantity has fallen below the minimum re-order level when %s %s was created"%(doc_type,doc_name)
indent.save(1)
indent_obj = get_obj('Indent',indent.name,with_children=1)
indent_details_child = addchild(indent_obj.doc,'indent_details','Indent Detail',0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date= add_days(nowdate(),cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
if (i['min_order_qty'] < ( flt(item_reorder_level)-flt(self.doc.projected_qty) )):
indent_details_child.qty =flt(flt(item_reorder_level)-flt(self.doc.projected_qty))
else:
indent_details_child.qty = i['min_order_qty']
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Indent',indent.name,with_children=1)
indent_obj.validate()
set(indent_obj.doc,'docstatus',1)
indent_obj.on_submit()
msgprint("Item: " + self.doc.item_code + " is to be re-ordered. Indent %s raised.Was generated from %s %s"%(indent.name,doc_type, doc_name ))
if(email_notify):
send_email_notification(doc_type,doc_name)
def validate_fiscal_year(self):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'"%self.doc.fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(self.doc.transaction_date) < str(ysd) or str(self.doc.transaction_date) > str(yed):
msgprint("Enquiry Date is not within the Fiscal Year selected")
raise Exception
def make_new_invoice(ref_wrapper, posting_date):
from webnotes.model.bean import clone
from accounts.utils import get_fiscal_year
new_invoice = clone(ref_wrapper)
mcount = month_map[ref_wrapper.doc.recurring_type]
invoice_period_from_date = get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.doc.invoice_period_from_date)) == \
cstr(ref_wrapper.doc.invoice_period_from_date)) and \
(cstr(get_last_day(ref_wrapper.doc.invoice_period_to_date)) == \
cstr(ref_wrapper.doc.invoice_period_to_date)):
invoice_period_to_date = get_last_day(get_next_date(ref_wrapper.doc.invoice_period_to_date,
mcount))
else:
invoice_period_to_date = get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount)
new_invoice.doc.fields.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.doc.due_date,
ref_wrapper.doc.posting_date))),
"invoice_period_from_date": invoice_period_from_date,
"invoice_period_to_date": invoice_period_to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.doc.owner,
})
new_invoice.submit()
return new_invoice
def get_activity_list(self):
out = {}
import webnotes
rt = webnotes.user.can_read
dt_list = [
d[0] for d in
sql("select distinct t2.name from tabDocField t1, tabDocType t2 where t1.fieldname='status' and t1.docstatus=0 and (t2.istable is null or t2.istable = 0) and t1.parent = t2.name"
)
]
if not dt_list:
return out
# get list of activity dt
for dt in dt_list:
if dt in rt:
out[dt] = {}
# get status list
sl = sql("select distinct status from `tab%s`" % dt)
for s in sl:
if s[0]:
# get count
cnt = sql(
"select count(*) from `tab%s` where status = '%s' and modified > '%s'"
% (dt, s[0], add_days(nowdate(), -7)))[0][0]
out[dt][s[0]] = cint(cnt)
return out
def make_new_invoice(ref_wrapper, posting_date):
from webnotes.model.wrapper import clone
from accounts.utils import get_fiscal_year
new_invoice = clone(ref_wrapper)
mcount = month_map[ref_wrapper.doc.recurring_type]
new_invoice.doc.fields.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.doc.due_date,
ref_wrapper.doc.posting_date))),
"invoice_period_from_date": \
get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount),
"invoice_period_to_date": \
get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount),
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.doc.owner,
})
new_invoice.submit()
return new_invoice
def make_new_invoice(ref_wrapper, posting_date):
from webnotes.model.bean import clone
from accounts.utils import get_fiscal_year
new_invoice = clone(ref_wrapper)
mcount = month_map[ref_wrapper.doc.recurring_type]
invoice_period_from_date = get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.doc.invoice_period_from_date)) == \
cstr(ref_wrapper.doc.invoice_period_from_date)) and \
(cstr(get_last_day(ref_wrapper.doc.invoice_period_to_date)) == \
cstr(ref_wrapper.doc.invoice_period_to_date)):
invoice_period_to_date = get_last_day(get_next_date(ref_wrapper.doc.invoice_period_to_date,
mcount))
else:
invoice_period_to_date = get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount)
new_invoice.doc.fields.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.doc.due_date,
ref_wrapper.doc.posting_date))),
"invoice_period_from_date": invoice_period_from_date,
"invoice_period_to_date": invoice_period_to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.doc.owner,
})
new_invoice.submit()
return new_invoice
def validate_fiscal_year(self):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'"%self.doc.fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(self.doc.posting_date) < str(ysd) or str(self.doc.posting_date) > str(yed):
msgprint("Posting Date is not within the Fiscal Year selected")
raise Exception
def get_error_report(from_date=None, to_date=None, limit=10):
from webnotes.utils import get_url, now_datetime, add_days
if not from_date:
from_date = add_days(now_datetime().date(), -1)
if not to_date:
to_date = add_days(now_datetime().date(), -1)
errors = get_errors(from_date, to_date, limit)
if errors:
return 1, """<h4>Scheduler Failed Events (max {limit}):</h4>
<p>URL: <a href="{url}" target="_blank">{url}</a></p><hr>{errors}""".format(
limit=limit, url=get_url(), errors="<hr>".join(errors))
else:
return 0, "<p>Scheduler didn't encounter any problems.</p>"
def get_as_on_balance(self, account_name, fiscal_year, as_on, credit_or_debit, lft, rgt):
# initialization
det = webnotes.conn.sql("select start_date, opening from `tabAccount Balance` where period = %s and account = %s", (fiscal_year, account_name))
from_date, opening, debit_bal, credit_bal, closing_bal = det and det[0][0] or getdate(nowdate()), det and flt(det[0][1]) or 0, 0, 0, det and flt(det[0][1]) or 0
# prev month closing
prev_month_det = webnotes.conn.sql("select end_date, debit, credit, balance from `tabAccount Balance` where account = %s and end_date <= %s and fiscal_year = %s order by end_date desc limit 1", (account_name, as_on, fiscal_year))
if prev_month_det:
from_date = getdate(add_days(prev_month_det[0][0].strftime('%Y-%m-%d'), 1))
opening = 0
debit_bal = flt(prev_month_det[0][1])
credit_bal = flt(prev_month_det[0][2])
closing_bal = flt(prev_month_det[0][3])
# curr month transaction
if getdate(as_on) >= from_date:
curr_month_bal = webnotes.conn.sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s and ifnull(t1.is_opening, 'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_cancelled, 'No') = 'No'", (from_date, as_on, lft, rgt))
curr_debit_amt, curr_credit_amt = flt(curr_month_bal[0][0]), flt(curr_month_bal[0][1])
debit_bal = curr_month_bal and debit_bal + curr_debit_amt or debit_bal
credit_bal = curr_month_bal and credit_bal + curr_credit_amt or credit_bal
if credit_or_debit == 'Credit':
curr_debit_amt, curr_credit_amt = -1*flt(curr_month_bal[0][0]), -1*flt(curr_month_bal[0][1])
closing_bal = closing_bal + curr_debit_amt - curr_credit_amt
return flt(debit_bal), flt(credit_bal), flt(closing_bal)
def make_new_invoice(ref_wrapper, posting_date):
from webnotes.model.wrapper import clone
from accounts.utils import get_fiscal_year
new_invoice = clone(ref_wrapper)
mcount = month_map[ref_wrapper.doc.recurring_type]
new_invoice.doc.fields.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.doc.due_date,
ref_wrapper.doc.posting_date))),
"invoice_period_from_date": \
get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount),
"invoice_period_to_date": \
get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount),
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.doc.owner,
})
new_invoice.submit()
return new_invoice
def validate_fiscal_year(self):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'"% self.doc.fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(self.doc.att_date) < str(ysd) or str(self.doc.att_date) > str(yed):
msgprint("'%s' Not Within The Fiscal Year selected"%(self.doc.att_date))
raise Exception
def create_auto_indent(self, i, doc_type, doc_name):
""" Create indent on reaching reorder level """
indent = Document('Indent')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Indent. It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when %s %s was created" % (
doc_type, doc_name)
indent.save(1)
indent_obj = get_obj('Indent', indent.name, with_children=1)
indent_details_child = addchild(indent_obj.doc, 'indent_details',
'Indent Detail', 0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date = add_days(
nowdate(), cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
indent_details_child.qty = i['re_order_qty']
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Indent', indent.name, with_children=1)
indent_obj.validate()
set(indent_obj.doc, 'docstatus', 1)
indent_obj.on_submit()
msgprint(
"Item: " + self.doc.item_code +
" is to be re-ordered. Indent %s raised. It was generated from %s %s"
% (indent.name, doc_type, doc_name))
if (i['email_notify']):
send_email_notification(doc_type, doc_name)
def calculate_lwp(self, m):
holidays = sql(
"select t1.holiday_date from `tabHoliday` t1, tabEmployee t2 where t1.parent = t2.holiday_list and t2.name = '%s' and t1.holiday_date between '%s' and '%s'"
% (self.doc.employee, m['month_start_date'], m['month_end_date']))
if not holidays:
holidays = sql(
"select t1.holiday_date from `tabHoliday` t1, `tabHoliday List` t2 where t1.parent = t2.name and ifnull(t2.is_default, 0) = 1 and t2.fiscal_year = '%s'"
% self.doc.fiscal_year)
holidays = [cstr(i[0]) for i in holidays]
lwp = 0
for d in range(m['month_days']):
dt = add_days(cstr(m['month_start_date']), d)
if dt not in holidays:
leave = sql("""
select t1.name, t1.half_day
from `tabLeave Application` t1, `tabLeave Type` t2
where t2.name = t1.leave_type
and ifnull(t2.is_lwp, 0) = 1
and t1.docstatus = 1
and t1.employee = '%s'
and '%s' between from_date and to_date
""" % (self.doc.employee, dt))
if leave:
lwp = cint(leave[0][1]) and lwp + 0.5 or lwp + 1
return lwp
def validate_fiscal_year(self, fiscal_year, transaction_date, dn):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'"%fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(transaction_date) < str(ysd) or str(transaction_date) > str(yed):
msgprint("'%s' Not Within The Fiscal Year"%(dn))
raise Exception
def update_serial_nos(self, cancel=False):
from stock.doctype.stock_ledger_entry.stock_ledger_entry import update_serial_nos_after_submit, get_serial_nos
update_serial_nos_after_submit(self, "Delivery Note", "delivery_note_details")
update_serial_nos_after_submit(self, "Delivery Note", "packing_details")
for table_fieldname in ("delivery_note_details", "packing_details"):
for d in self.doclist.get({"parentfield": table_fieldname}):
for serial_no in get_serial_nos(d.serial_no):
sr = webnotes.bean("Serial No", serial_no)
if cancel:
sr.doc.status = "Available"
for fieldname in ("warranty_expiry_date", "delivery_document_type",
"delivery_document_no", "delivery_date", "delivery_time", "customer",
"customer_name"):
sr.doc.fields[fieldname] = None
else:
sr.doc.delivery_document_type = "Delivery Note"
sr.doc.delivery_document_no = self.doc.name
sr.doc.delivery_date = self.doc.posting_date
sr.doc.delivery_time = self.doc.posting_time
sr.doc.customer = self.doc.customer
sr.doc.customer_name = self.doc.customer_name
if sr.doc.warranty_period:
sr.doc.warranty_expiry_date = add_days(cstr(self.doc.posting_date),
cint(sr.doc.warranty_period))
sr.doc.status = 'Delivered'
sr.save()
def update_serial_nos(self, cancel=False):
from stock.doctype.stock_ledger_entry.stock_ledger_entry import update_serial_nos_after_submit, get_serial_nos
update_serial_nos_after_submit(self, self.doc.doctype, self.fname)
update_serial_nos_after_submit(self, self.doc.doctype, "packing_details")
for table_fieldname in (self.fname, "packing_details"):
for d in self.doclist.get({"parentfield": table_fieldname}):
for serial_no in get_serial_nos(d.serial_no):
sr = webnotes.bean("Serial No", serial_no)
if cancel:
sr.doc.status = "Available"
for fieldname in ("warranty_expiry_date", "delivery_document_type",
"delivery_document_no", "delivery_date", "delivery_time", "customer",
"customer_name"):
sr.doc.fields[fieldname] = None
else:
sr.doc.delivery_document_type = self.doc.doctype
sr.doc.delivery_document_no = self.doc.name
sr.doc.delivery_date = self.doc.posting_date
sr.doc.delivery_time = self.doc.posting_time
sr.doc.customer = self.doc.customer
sr.doc.customer_name = self.doc.customer_name
if sr.doc.warranty_period:
sr.doc.warranty_expiry_date = add_days(cstr(self.doc.posting_date),
cint(sr.doc.warranty_period))
sr.doc.status = 'Delivered'
sr.save()
def create_auto_indent(self, i , doc_type, doc_name, cur_qty):
""" Create indent on reaching reorder level """
indent = Document('Purchase Request')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Purchase Request. It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when %s %s was created"%(doc_type,doc_name)
indent.save(1)
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_details_child = addchild(indent_obj.doc,'indent_details','Purchase Request Item',0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date= add_days(nowdate(),cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
indent_details_child.qty = i['re_order_qty'] or (flt(i['re_order_level']) - flt(cur_qty))
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_obj.validate()
set(indent_obj.doc,'docstatus',1)
indent_obj.on_submit()
msgprint("Item: " + self.doc.item_code + " is to be re-ordered. Purchase Request %s raised. It was generated from %s %s"%(indent.name,doc_type, doc_name ))
if(i['email_notify']):
send_email_notification(doc_type,doc_name)
def validate_fiscal_year(self, fiscal_year, transaction_date, dn):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'"%fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(transaction_date) < str(ysd) or str(transaction_date) > str(yed):
msgprint("'%s' Not Within The Fiscal Year"%(dn))
raise Exception
def make_new_invoice(ref_wrapper):
from webnotes.model.wrapper import clone
new_invoice = clone(ref_wrapper)
mcount = month_map[ref_wrapper.doc.recurring_type]
today = nowdate()
new_invoice.doc.fields.update({
"posting_date": today,
"aging_date": today,
"due_date": add_days(today, cint(date_diff(ref_wrapper.doc.due_date,
ref_wrapper.doc.posting_date))),
"invoice_period_from_date": \
get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount),
"invoice_period_to_date": \
get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount),
"owner": ref_wrapper.doc.owner,
})
new_invoice.submit()
return new_invoice
def get_item_details( self, obj, arg =''):
arg = eval(arg)
item = sql("select item_name,item_group, brand, description, min_order_qty, stock_uom, default_warehouse,lead_time_days, last_purchase_rate from `tabItem` where name = %s and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now())", (arg['item_code']), as_dict = 1)
tax = sql("select tax_type, tax_rate from `tabItem Tax` where parent = %s" , arg['item_code'])
t = {}
for x in tax: t[x[0]] = flt(x[1])
# get warehouse
if arg['warehouse']:
wh = arg['warehouse']
else:
wh = item and item[0]['default_warehouse'] or ''
ret = {
'item_name' : item and item[0]['item_name'] or '',
'item_group' : item and item[0]['item_group'] or '',
'brand' : item and item[0]['brand'] or '',
'description' : item and item[0]['description'] or '',
'qty' : 0,
'uom' : item and item[0]['stock_uom'] or '',
'stock_uom' : item and item[0]['stock_uom'] or '',
'conversion_factor' : '1',
'warehouse' : wh,
'item_tax_rate' : str(t),
'batch_no' : ''
}
# get min_order_qty from item
if obj.doc.doctype == 'Indent':
ret['min_order_qty'] = item and flt(item[0]['min_order_qty']) or 0
# get projected qty from bin
if ret['warehouse']:
bin = sql("select projected_qty from `tabBin` where item_code = %s and warehouse = %s", (arg['item_code'], ret['warehouse']), as_dict=1)
ret['projected_qty'] = bin and flt(bin[0]['projected_qty']) or 0
# get schedule date, lead time date
if obj.doc.transaction_date and item and item[0]['lead_time_days']:
ret['schedule_date'] = cstr(add_days(obj.doc.transaction_date, cint(item[0]['lead_time_days'])))
ret['lead_time_date'] = cstr(add_days(obj.doc.transaction_date, cint(item[0]['lead_time_days'])))
# get last purchase rate as per stock uom and default currency for following list of doctypes
if obj.doc.doctype in ['Supplier Quotation', 'Purchase Order', 'Purchase Receipt']:
ret['purchase_rate'] = item and flt(item[0]['last_purchase_rate']) or 0
ret['import_rate'] = flt(item and flt(item[0]['last_purchase_rate']) or 0) / flt(obj.doc.fields.has_key('conversion_rate') and flt(obj.doc.conversion_rate) or 1)
return ret
def send_notification(new_rv):
"""Notify concerned persons about recurring invoice generation"""
subject = "Invoice : " + new_rv.doc.name
com = new_rv.doc.company # webnotes.conn.get_value('Control Panel', '', 'letter_head')
hd = '''<div><h2>%s</h2></div>
<div><h3>Invoice: %s</h3></div>
<table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"><b>Customer</b><br>%s<br>%s</td>
<td width = "50%%">Invoice Date : %s<br>Invoice Period : %s to %s <br>Due Date : %s</td>
</tr>
</table>
''' % (com, new_rv.doc.name, new_rv.doc.customer, new_rv.doc.address_display, getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"), \
getdate(add_days(add_months(new_rv.doc.posting_date, -1), 1)).strftime("%d-%m-%Y"), getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"),\
getdate(new_rv.doc.due_date).strftime("%d-%m-%Y"))
tbl = '''<table border="1px solid #CCC" width="100%%" cellpadding="0px" cellspacing="0px">
<tr>
<td width = "15%%" bgcolor="#CCC" align="left"><b>Item</b></td>
<td width = "40%%" bgcolor="#CCC" align="left"><b>Description</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Qty</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Rate</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Amount</b></td>
</tr>
'''
for d in getlist(new_rv.doclist, 'entries'):
tbl += '<tr><td>' + d.item_code +'</td><td>' + d.description+'</td><td>' + cstr(d.qty) +'</td><td>' + cstr(d.basic_rate) +'</td><td>' + cstr(d.amount) +'</td></tr>'
tbl += '</table>'
totals =''' <table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"></td>
<td width = "50%%">
<table width = "100%%">
<tr>
<td width = "50%%">Net Total: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Total Tax: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Grand Total: </td><td>%s</td>
</tr><tr>
<td width = "50%%">In Words: </td><td>%s</td>
</tr>
</table>
</td>
</tr>
<tr><td>Terms and Conditions:</td></tr>
<tr><td>%s</td></tr>
</table>
''' % (new_rv.doc.net_total, new_rv.doc.other_charges_total,new_rv.doc.grand_total, new_rv.doc.in_words,new_rv.doc.terms)
msg = hd + tbl + totals
from webnotes.utils.email_lib import sendmail
sendmail(recipients = new_rv.doc.notification_email_address.split(", "), \
sender=new_rv.doc.owner, subject=subject, parts=[['text/plain', msg]])
def create_material_request(material_requests):
""" Create indent on reaching reorder level """
mr_list = []
defaults = webnotes.defaults.get_defaults()
exceptions_list = []
for request_type in material_requests:
for company in material_requests[request_type]:
try:
items = material_requests[request_type][company]
if not items:
continue
mr = [{
"doctype": "Material Request",
"company": company,
"fiscal_year": defaults.fiscal_year,
"transaction_date": nowdate(),
"material_request_type": request_type
}]
for d in items:
item = webnotes.doc("Item", d.item_code)
mr.append({
"doctype": "Material Request Item",
"parenttype": "Material Request",
"parentfield": "indent_details",
"item_code": d.item_code,
"schedule_date": add_days(nowdate(),cint(item.lead_time_days)),
"uom": item.stock_uom,
"warehouse": d.warehouse,
"item_name": item.item_name,
"description": item.description,
"item_group": item.item_group,
"qty": d.reorder_qty,
"brand": item.brand,
})
mr_bean = webnotes.bean(mr)
mr_bean.insert()
mr_bean.submit()
mr_list.append(mr_bean)
except:
if webnotes.local.message_log:
exceptions_list.append([] + webnotes.local.message_log)
webnotes.local.message_log = []
else:
exceptions_list.append(webnotes.getTraceback())
if mr_list:
if getattr(webnotes.local, "reorder_email_notify", None) is None:
webnotes.local.reorder_email_notify = cint(webnotes.conn.get_value('Stock Settings', None,
'reorder_email_notify'))
if(webnotes.local.reorder_email_notify):
send_email_notification(mr_list)
if exceptions_list:
notify_errors(exceptions_list)
def get_cust(self):
ret = {}
if self.doc.credit_to:
acc = webnotes.conn.get_value('Account',self.doc.credit_to,['master_name', 'credit_days'])
ret['supplier'] = acc[0]
ret['due_date'] = add_days(cstr(self.doc.posting_date), acc and cint(acc[1]) or 0)
return ret
def get_cust(self):
ret = {}
if self.doc.credit_to:
acc = webnotes.conn.get_value('Account',self.doc.credit_to,['master_name', 'credit_days'])
ret['supplier'] = acc[0]
ret['due_date'] = add_days(cstr(self.doc.posting_date), acc and cint(acc[1]) or 0)
return ret
def validate_fiscal_year(self):
fy=sql("select year_start_date from `tabFiscal Year` where name='%s'" % \
self.doc.fiscal_year)
ysd=fy and fy[0][0] or ""
yed=add_days(str(ysd),365)
if str(self.doc.att_date) < str(ysd) or str(self.doc.att_date) > str(yed):
msgprint("'%s' Not Within The Fiscal Year selected"%(self.doc.att_date))
raise Exception
def insert_purchase_request(self, items_to_be_requested, fiscal_year):
purchase_request_list = []
if items_to_be_requested:
for item in items_to_be_requested:
item_wrapper = webnotes.bean("Item", item)
pr_doclist = [{
"doctype": "Material Request",
"__islocal": 1,
"naming_series": "IDT",
"transaction_date": nowdate(),
"status": "Draft",
"company": self.doc.company,
"fiscal_year": fiscal_year,
"requested_by": webnotes.session.user,
"remark":
"Automatically raised from Production Planning Tool",
"material_request_type": "Purchase"
}, {
"doctype":
"Material Request Item",
"__islocal":
1,
"parentfield":
"indent_details",
"item_code":
item,
"item_name":
item_wrapper.doc.item_name,
"description":
item_wrapper.doc.description,
"uom":
item_wrapper.doc.stock_uom,
"item_group":
item_wrapper.doc.item_group,
"brand":
item_wrapper.doc.brand,
"qty":
items_to_be_requested[item],
"schedule_date":
add_days(nowdate(), cint(item_wrapper.doc.lead_time_days)),
"warehouse":
self.doc.purchase_request_for_warehouse
}]
pr_wrapper = webnotes.bean(pr_doclist)
pr_wrapper.ignore_permissions = 1
pr_wrapper.submit()
purchase_request_list.append(pr_wrapper.doc.name)
if purchase_request_list:
pur_req = ["""<a href="#Form/Material Request/%s" target="_blank">%s</a>""" % \
(p, p) for p in purchase_request_list]
webnotes.msgprint(
"Following Material Request created successfully: \n%s" %
"\n".join(pur_req))
else:
webnotes.msgprint("Nothing to request")
def create_schedule_list(self, start_date, end_date, no_of_visit):
schedule_list = []
start_date1 = start_date
date_diff = (getdate(end_date) - getdate(start_date)).days
add_by = date_diff / no_of_visit
#schedule_list.append(start_date1)
while (getdate(start_date1) < getdate(end_date)):
start_date1 = add_days(start_date1, add_by)
if len(schedule_list) < no_of_visit:
schedule_list.append(getdate(start_date1))
return schedule_list
def get_credit_to(self):
acc_head = sql("select name, credit_days from `tabAccount` where (name = %s or (master_name = %s and master_type = 'supplier')) and docstatus != 2", (cstr(self.doc.supplier) + " - " + self.get_company_abbr(),self.doc.supplier))
ret = {}
if acc_head and acc_head[0][0]:
ret['credit_to'] = acc_head[0][0]
if not self.doc.due_date:
ret['due_date'] = add_days(cstr(self.doc.posting_date), acc_head and cint(acc_head[0][1]) or 0)
elif not acc_head:
msgprint("%s does not have an Account Head in %s. You must first create it from the Supplier Master" % (self.doc.supplier, self.doc.company))
return ret
def create_schedule_list(self, start_date, end_date, no_of_visit):
schedule_list = []
start_date1 = start_date
date_diff = (getdate(end_date) - getdate(start_date)).days
add_by = date_diff/no_of_visit
#schedule_list.append(start_date1)
while(getdate(start_date1) < getdate(end_date)):
start_date1 = add_days(start_date1, add_by)
if len(schedule_list) < no_of_visit:
schedule_list.append(getdate(start_date1))
return schedule_list
def get_credit_to(self):
acc_head = sql("select name, credit_days from `tabAccount` where (name = %s or (master_name = %s and master_type = 'supplier')) and docstatus != 2", (cstr(self.doc.supplier) + " - " + self.get_company_abbr(),self.doc.supplier))
ret = {}
if acc_head and acc_head[0][0]:
ret['credit_to'] = acc_head[0][0]
if not self.doc.due_date:
ret['due_date'] = add_days(cstr(self.doc.posting_date), acc_head and cint(acc_head[0][1]) or 0)
elif not acc_head:
msgprint("%s does not have an Account Head in %s. You must first create it from the Supplier Master" % (self.doc.supplier, self.doc.company))
return ret
def calculate_lwp(self, m):
holidays = sql("select t1.holiday_date from `tabHoliday List Detail` t1, tabEmployee t2 where t1.parent = t2.holiday_list and t2.name = '%s' and t1.holiday_date between '%s' and '%s'" % (self.doc.employee, m[1], m[2]))
if not holidays:
holidays = sql("select t1.holiday_date from `tabHoliday List Detail` t1, `tabHoliday List` t2 where t1.parent = t2.name and ifnull(t2.is_default, 0) = 1 and t2.fiscal_year = '%s'" % self.doc.fiscal_year)
holidays = [cstr(i[0]) for i in holidays]
lwp = 0
for d in range(m[3]):
dt = add_days(cstr(m[1]), d)
if dt not in holidays:
if sql("select t1.name from `tabLeave Application` t1, `tabLeave Type` t2 where t2.name = t1.leave_type and ifnull(t2.is_lwp, 0) = 1 and t1.docstatus = 1 and t1.employee = '%s' and '%s' between from_date and to_date"%(self.doc.employee, dt)):
lwp += 1
return lwp
def check_stock_frozen_date(self):
stock_frozen_upto = webnotes.conn.get_value('Stock Settings', None, 'stock_frozen_upto') or ''
if stock_frozen_upto:
stock_auth_role = webnotes.conn.get_value('Stock Settings', None,'stock_auth_role')
if getdate(self.doc.posting_date) <= getdate(stock_frozen_upto) and not stock_auth_role in webnotes.user.get_roles():
msgprint("You are not authorized to do / modify back dated stock entries before %s" % getdate(stock_frozen_upto).strftime('%d-%m-%Y'), raise_exception=StockFreezeError)
stock_frozen_upto_days = int(webnotes.conn.get_value('Stock Settings', None, 'stock_frozen_upto_days') or 0)
if stock_frozen_upto_days:
stock_auth_role = webnotes.conn.get_value('Stock Settings', None,'stock_auth_role')
older_than_x_days_ago = (add_days(getdate(self.doc.posting_date), stock_frozen_upto_days) <= date.today())
if older_than_x_days_ago and not stock_auth_role in webnotes.user.get_roles():
msgprint("You are not authorized to do / modify back dated stock entries older than %d days ago" %stock_frozen_upto_days, raise_exception=StockFreezeError)
def get_quarterly_trend_details(self):
first_qsd, second_qsd, third_qsd, fourth_qsd = self.year_start_date, add_months(self.year_start_date,3), add_months(self.year_start_date,6), add_months(self.year_start_date,9)
first_qed, second_qed, third_qed, fourth_qed = add_days(add_months(first_qsd,3),-1), add_days(add_months(second_qsd,3),-1), add_days(add_months(third_qsd,3),-1), add_days(add_months(fourth_qsd,3),-1)
col_names = ['Q1 (Qty)','Q1 (Amt)','Q2 (Qty)','Q2 (Amt)','Q3 (Qty)','Q3 (Amt)','Q4 (Qty)','Q4 (Amt)']
query_val = ''
bet_dates = [[first_qsd,first_qed],[second_qsd,second_qed],[third_qsd,third_qed],[fourth_qsd,fourth_qed]]
for d in bet_dates:
query_val += 'SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+d[0]+'" AND "'+d[1]+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+d[0]+'" AND "'+d[1]+'" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_item_details(args):
"""
args = {
"doctype": "",
"docname": "",
"item_code": "",
"warehouse": None,
"supplier": None,
"transaction_date": None,
"conversion_rate": 1.0,
"buying_price_list": None,
"price_list_currency": None,
"plc_conversion_rate": 1.0,
"is_subcontracted": "Yes" / "No"
}
"""
if isinstance(args, basestring):
args = json.loads(args)
args = webnotes._dict(args)
item_bean = webnotes.bean("Item", args.item_code)
item = item_bean.doc
_validate_item_details(args, item)
out = _get_basic_details(args, item_bean)
out.supplier_part_no = _get_supplier_part_no(args, item_bean)
if not out.warehouse:
out.warehouse = item_bean.doc.default_warehouse
if out.warehouse:
out.projected_qty = get_projected_qty(item.name, out.warehouse)
if args.transaction_date and item.lead_time_days:
out.schedule_date = out.lead_time_date = add_days(args.transaction_date,
item.lead_time_days)
meta = webnotes.get_doctype(args.doctype)
if meta.get_field("currency"):
out.purchase_ref_rate = out.discount_rate = out.purchase_rate = \
out.import_ref_rate = out.import_rate = 0.0
out.update(_get_price_list_rate(args, item_bean, meta))
if args.doctype == "Material Request":
out.min_order_qty = flt(item.min_order_qty)
return out
def get_cust_and_due_date(self):
credit_days = 0
if self.doc.debit_to:
credit_days = webnotes.conn.sql("select credit_days from `tabAccount` where name='%s' and docstatus != 2" % self.doc.debit_to)
credit_days = credit_days and cint(credit_days[0][0]) or 0
if self.doc.company and not credit_days:
credit_days = webnotes.conn.sql("select credit_days from `tabCompany` where name='%s'" % self.doc.company)
credit_days = credit_days and cint(credit_days[0][0]) or 0
# Customer has higher priority than company
# i.e.if not entered in customer will take credit days from company
self.doc.due_date = add_days(cstr(self.doc.posting_date), credit_days)
if self.doc.debit_to:
self.doc.customer = webnotes.conn.get_value('Account',self.doc.debit_to,'master_name')
def insert_purchase_request(self):
items_to_be_requested = self.get_requested_items()
from accounts.utils import get_fiscal_year
fiscal_year = get_fiscal_year(nowdate())[0]
purchase_request_list = []
if items_to_be_requested:
for item in items_to_be_requested:
item_wrapper = webnotes.bean("Item", item)
pr_doclist = [{
"doctype": "Material Request",
"__islocal": 1,
"naming_series": "IDT",
"transaction_date": nowdate(),
"status": "Draft",
"company": self.doc.company,
"fiscal_year": fiscal_year,
"requested_by": webnotes.session.user,
"material_request_type": "Purchase"
}]
for sales_order, requested_qty in items_to_be_requested[item].items():
pr_doclist.append({
"doctype": "Material Request Item",
"__islocal": 1,
"parentfield": "indent_details",
"item_code": item,
"item_name": item_wrapper.doc.item_name,
"description": item_wrapper.doc.description,
"uom": item_wrapper.doc.stock_uom,
"item_group": item_wrapper.doc.item_group,
"brand": item_wrapper.doc.brand,
"qty": requested_qty,
"schedule_date": add_days(nowdate(), cint(item_wrapper.doc.lead_time_days)),
"warehouse": self.doc.purchase_request_for_warehouse,
"sales_order_no": sales_order if sales_order!="No Sales Order" else None
})
pr_wrapper = webnotes.bean(pr_doclist)
pr_wrapper.ignore_permissions = 1
pr_wrapper.submit()
purchase_request_list.append(pr_wrapper.doc.name)
if purchase_request_list:
pur_req = ["""<a href="#Form/Material Request/%s" target="_blank">%s</a>""" % \
(p, p) for p in purchase_request_list]
msgprint("Material Request(s) created: \n%s" %
"\n".join(pur_req))
else:
msgprint(_("Nothing to request"))
def create_material_request(material_requests):
""" Create indent on reaching reorder level """
mr_list = []
defaults = webnotes.defaults.get_defaults()
for request_type in material_requests:
for company in material_requests[request_type]:
items = material_requests[request_type][company]
if items:
mr = [{
"doctype": "Material Request",
"company": company,
"fiscal_year": defaults.fiscal_year,
"transaction_date": nowdate(),
"material_request_type": request_type,
"remark": _("This is an auto generated Material Request.") + \
_("""It was raised because the (actual + ordered + indented - reserved)
quantity reaches re-order level when the following record was created""")
}]
for d in items:
item = webnotes.doc("Item", d.item_code)
mr.append({
"doctype": "Material Request Item",
"parenttype": "Material Request",
"parentfield": "indent_details",
"item_code": d.item_code,
"schedule_date": add_days(nowdate(),cint(item.lead_time_days)),
"uom": item.stock_uom,
"warehouse": d.warehouse,
"item_name": item.item_name,
"description": item.description,
"item_group": item.item_group,
"qty": d.reorder_qty,
"brand": item.brand,
})
mr_bean = webnotes.bean(mr)
mr_bean.insert()
mr_bean.submit()
mr_list.append(mr_bean)
if mr_list:
if not hasattr(webnotes, "reorder_email_notify"):
webnotes.reorder_email_notify = webnotes.conn.get_value('Global Defaults', None,
'reorder_email_notify')
if(webnotes.reorder_email_notify):
send_email_notification(mr_list)
def get_cust_and_due_date(self):
"""Set Due Date = Posting Date + Credit Days"""
if self.doc.posting_date:
credit_days = 0
if self.doc.debit_to:
credit_days = webnotes.conn.get_value("Account", self.doc.debit_to, "credit_days")
if self.doc.company and not credit_days:
credit_days = webnotes.conn.get_value("Company", self.doc.company, "credit_days")
if credit_days:
self.doc.due_date = add_days(self.doc.posting_date, credit_days)
else:
self.doc.due_date = self.doc.posting_date
if self.doc.debit_to:
self.doc.customer = webnotes.conn.get_value('Account',self.doc.debit_to,'master_name')
def set_delivery_serial_no_values(self, obj, serial_no):
s = Document('Serial No', serial_no)
s.delivery_document_type = obj.doc.doctype
s.delivery_document_no = obj.doc.name
s.delivery_date = obj.doc.posting_date
s.delivery_time = obj.doc.posting_time
s.customer = obj.doc.customer
s.customer_name = obj.doc.customer_name
s.delivery_address = obj.doc.address_display
s.territory = obj.doc.territory
s.warranty_expiry_date = s.warranty_period and add_days(cstr(obj.doc.posting_date), s.warranty_period) or ''
s.docstatus = 1
s.status = 'Delivered'
s.modified = nowdate()
s.modified_by = session['user']
s.save()
