def __init__(self, filters=None):
self.filters = webnotes._dict(filters or {})
self.filters.report_date = getdate(self.filters.report_date
or nowdate())
self.age_as_on = getdate(nowdate()) \
if self.filters.report_date > getdate(nowdate()) \
else self.filters.report_date
def create_feed_and_todo(self):
"""update activty feed and create todo for creation of item, customer, vendor"""
import home
home.make_feed(
'Comment', 'ToDo', '', webnotes.session['user'],
'<i>"' + 'Setup Complete. Please check your <a href="#!todo">\
To Do List</a>' + '"</i>', '#6B24B3')
d = Document('ToDo')
d.description = 'Create your first Customer'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Customer'
d.save(1)
d = Document('ToDo')
d.description = 'Create your first Item'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Item'
d.save(1)
d = Document('ToDo')
d.description = 'Create your first Supplier'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Supplier'
d.save(1)
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 execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
account_supplier = dict(
webnotes.conn.sql("""select account.name, supplier.supplier_name
from `tabAccount` account, `tabSupplier` supplier
where account.master_type="Supplier" and supplier.name=account.master_name"""
))
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
outstanding_amount = get_outstanding_amount(
gle,
filters.get("report_date") or nowdate())
if abs(flt(outstanding_amount)) > 0.01:
paid_amount = invoiced_amount - outstanding_amount
row = [
gle.posting_date, gle.account,
account_supplier.get(gle.account, ""), gle.voucher_type,
gle.voucher_no, gle.remarks,
account_supplier_type_map.get(gle.account), due_date,
bill_no, bill_date, invoiced_amount, paid_amount,
outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(age_on, ageing_based_on_date,
outstanding_amount)
data.append(row)
return columns, data
def manage_recurring_invoices():
"""
Create recurring invoices on specific date by copying the original one
and notify the concerned people
"""
rv = webnotes.conn.sql("""select name, recurring_id from `tabSales Invoice` \
where ifnull(convert_into_recurring_invoice, 0) = 1 and next_date = %s \
and next_date <= ifnull(end_date, '2199-12-31') and docstatus=1""", nowdate())
exception_list = []
for d in rv:
if not webnotes.conn.sql("""select name from `tabSales Invoice` \
where posting_date = %s and recurring_id = %s and docstatus=1""", (nowdate(), d[1])):
try:
prev_rv = get_obj('Sales Invoice', d[0], with_children=1)
new_rv = create_new_invoice(prev_rv)
send_notification(new_rv)
webnotes.conn.commit()
except Exception, e:
webnotes.conn.rollback()
webnotes.conn.begin()
webnotes.conn.sql("update `tabSales Invoice` set \
convert_into_recurring_invoice = 0 where name = %s", d[0])
notify_errors(d[0], prev_rv.doc.owner)
webnotes.conn.commit()
exception_list.append(e)
finally:
def manage_recurring_invoices():
"""
Create recurring invoices on specific date by copying the original one
and notify the concerned people
"""
rv = webnotes.conn.sql(
"""select name, recurring_id from `tabSales Invoice` \
where ifnull(convert_into_recurring_invoice, 0) = 1 and next_date = %s \
and next_date <= ifnull(end_date, '2199-12-31') and docstatus=1""",
nowdate())
exception_list = []
for d in rv:
if not webnotes.conn.sql(
"""select name from `tabSales Invoice` \
where posting_date = %s and recurring_id = %s and docstatus=1""",
(nowdate(), d[1])):
try:
prev_rv = get_obj('Sales Invoice', d[0], with_children=1)
new_rv = create_new_invoice(prev_rv)
send_notification(new_rv)
webnotes.conn.commit()
except Exception, e:
webnotes.conn.rollback()
webnotes.conn.begin()
webnotes.conn.sql(
"update `tabSales Invoice` set \
convert_into_recurring_invoice = 0 where name = %s", d[0])
notify_errors(d[0], prev_rv.doc.owner)
webnotes.conn.commit()
exception_list.append(e)
finally:
def make_stock_ledger_entry(self, qty):
from webnotes.model.code import get_obj
values = [
{
"item_code": self.doc.item_code,
"warehouse": self.doc.warehouse,
"transaction_date": nowdate(),
"posting_date": self.doc.purchase_date
or (self.doc.creation and self.doc.creation.split(" ")[0])
or nowdate(),
"posting_time": self.doc.purchase_time or "00:00",
"voucher_type": "Serial No",
"voucher_no": self.doc.name,
"voucher_detail_no": "",
"actual_qty": qty,
"stock_uom": webnotes.conn.get_value("Item", self.doc.item_code, "stock_uom"),
"incoming_rate": self.doc.purchase_rate,
"company": self.doc.company,
"fiscal_year": self.doc.fiscal_year,
"is_cancelled": "No", # is_cancelled is always 'No' because while deleted it can not find creation entry if it not created directly, voucher no != serial no
"batch_no": "",
"serial_no": self.doc.name,
}
]
get_obj("Stock Ledger", "Stock Ledger").update_stock(values)
def get_valuation_rate(self, args):
""" Get average valuation rate of relevant warehouses
as per valuation method (MAR/FIFO)
as on costing date
"""
from stock.utils import get_incoming_rate
dt = self.doc.costing_date or nowdate()
time = self.doc.costing_date == nowdate() and now().split(
)[1] or '23:59'
warehouse = webnotes.conn.sql(
"select warehouse from `tabBin` where item_code = %s",
args['item_code'])
rate = []
for wh in warehouse:
r = get_incoming_rate({
"item_code": args.get("item_code"),
"warehouse": wh[0],
"posting_date": dt,
"posting_time": time,
"qty": args.get("qty") or 0
})
if r:
rate.append(r)
return rate and flt(sum(rate)) / len(rate) or 0
def get_balance_on(account=None, date=None):
if not account and webnotes.form_dict.get("account"):
account = webnotes.form_dict.get("account")
date = webnotes.form_dict.get("date")
cond = []
if date:
cond.append("posting_date <= '%s'" % date)
else:
# get balance of all entries that exist
date = nowdate()
try:
year_start_date = get_fiscal_year(date, verbose=0)[1]
except FiscalYearError, e:
from webnotes.utils import getdate
if getdate(date) > getdate(nowdate()):
# if fiscal year not found and the date is greater than today
# get fiscal year for today's date and its corresponding year start date
year_start_date = get_fiscal_year(nowdate(), verbose=1)[1]
else:
# this indicates that it is a date older than any existing fiscal year.
# hence, assuming balance as 0.0
return 0.0
def make_stock_ledger_entry(self, update_stock):
from webnotes.model.code import get_obj
values = [
{
"item_code": self.doc.item_code,
"warehouse": self.doc.warehouse,
"transaction_date": nowdate(),
"posting_date": self.doc.purchase_date
or (self.doc.creation and self.doc.creation.split(" ")[0])
or nowdate(),
"posting_time": self.doc.purchase_time or "00:00",
"voucher_type": "Serial No",
"voucher_no": self.doc.name,
"voucher_detail_no": "",
"actual_qty": 1,
"stock_uom": webnotes.conn.get_value("Item", self.doc.item_code, "stock_uom"),
"incoming_rate": self.doc.purchase_rate,
"company": self.doc.company,
"fiscal_year": self.doc.fiscal_year,
"is_cancelled": update_stock and "No" or "Yes",
"batch_no": "",
"serial_no": self.doc.name,
}
]
get_obj("Stock Ledger", "Stock Ledger").update_stock(values)
def create_feed_and_todo(self):
"""update activty feed and create todo for creation of item, customer, vendor"""
import home
home.make_feed('Comment', 'ToDo', '', webnotes.session['user'],
'<i>"' + 'Setup Complete. Please check your <a href="#!todo">\
To Do List</a>' + '"</i>', '#6B24B3')
d = Document('ToDo')
d.description = 'Create your first Customer'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Customer'
d.save(1)
d = Document('ToDo')
d.description = 'Create your first Item'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Item'
d.save(1)
d = Document('ToDo')
d.description = 'Create your first Supplier'
d.priority = 'High'
d.date = nowdate()
d.reference_type = 'Supplier'
d.save(1)
def test_get_value(self):
webnotes.conn.sql(
"""delete from `tabProfile` where name not in ('Administrator', 'Guest')"""
)
make_test_records("Profile")
self.assertEquals(
webnotes.conn.get_value("Profile",
{"name": ["=", "Administrator"]}),
"Administrator")
self.assertEquals(
webnotes.conn.get_value("Profile", {"name": ["like", "Admin%"]}),
"Administrator")
self.assertEquals(
webnotes.conn.get_value("Profile", {"name": ["!=", "Guest"]}),
"Administrator")
from webnotes.utils import nowdate
self.assertEquals(
webnotes.conn.get_value("Profile",
{"modified": ["<", nowdate()]}),
"Administrator")
self.assertEquals(
webnotes.conn.get_value("Profile",
{"modified": ["<=", nowdate()]}),
"Administrator")
self.assertEquals(
webnotes.conn.get_value("Profile",
{"modified": [">", nowdate()]}),
"*****@*****.**")
self.assertEquals(
webnotes.conn.get_value("Profile",
{"modified": [">=", nowdate()]}),
"*****@*****.**")
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 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 manage_recurring_invoices():
"""
Create recurring invoices on specific date by copying the original one
and notify the concerned people
"""
recurring_invoices = webnotes.conn.sql("""select name, recurring_id
from `tabSales Invoice` where ifnull(convert_into_recurring_invoice, 0)=1
and docstatus=1 and next_date=%s
and next_date <= ifnull(end_date, '2199-12-31')""", nowdate())
exception_list = []
for ref_invoice, recurring_id in recurring_invoices:
if not webnotes.conn.sql("""select name from `tabSales Invoice`
where posting_date=%s and recurring_id=%s and docstatus=1""",
(nowdate(), recurring_id)):
try:
ref_wrapper = webnotes.model_wrapper('Sales Invoice', ref_invoice)
new_invoice_wrapper = make_new_invoice(ref_wrapper)
send_notification(new_invoice_wrapper)
webnotes.conn.commit()
except Exception, e:
webnotes.conn.rollback()
webnotes.conn.begin()
webnotes.conn.sql("update `tabSales Invoice` set \
convert_into_recurring_invoice = 0 where name = %s", ref_invoice)
notify_errors(ref_invoice, ref_wrapper.doc.owner)
webnotes.conn.commit()
exception_list.append(webnotes.getTraceback())
finally:
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 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 execute():
item_map = {}
for item in webnotes.conn.sql("""select * from tabItem""", as_dict=1):
item_map.setdefault(item.name, item)
warehouse_map = get_warehosue_map()
naming_series = "STE/13/"
for company in webnotes.conn.sql("select name from tabCompany"):
stock_entry = [{
"doctype": "Stock Entry",
"naming_series": naming_series,
"posting_date": nowdate(),
"posting_time": nowtime(),
"purpose": "Material Transfer",
"company": company[0],
"remarks": "Material Transfer to activate perpetual inventory",
"fiscal_year": get_fiscal_year(nowdate())[0]
}]
expense_account = "Cost of Goods Sold - NISL"
cost_center = "Default CC Ledger - NISL"
for bin in webnotes.conn.sql("""select * from tabBin bin where ifnull(item_code, '')!=''
and ifnull(warehouse, '') in (%s) and ifnull(actual_qty, 0) != 0
and (select company from tabWarehouse where name=bin.warehouse)=%s""" %
(', '.join(['%s']*len(warehouse_map)), '%s'),
(warehouse_map.keys() + [company[0]]), as_dict=1):
item_details = item_map[bin.item_code]
new_warehouse = warehouse_map[bin.warehouse].get("fixed_asset_warehouse") \
if cstr(item_details.is_asset_item) == "Yes" \
else warehouse_map[bin.warehouse].get("current_asset_warehouse")
if item_details.has_serial_no == "Yes":
serial_no = "\n".join([d[0] for d in webnotes.conn.sql("""select name
from `tabSerial No` where item_code = %s and warehouse = %s
and status in ('Available', 'Sales Returned')""",
(bin.item_code, bin.warehouse))])
else:
serial_no = None
stock_entry.append({
"doctype": "Stock Entry Detail",
"parentfield": "mtn_details",
"s_warehouse": bin.warehouse,
"t_warehouse": new_warehouse,
"item_code": bin.item_code,
"description": item_details.description,
"qty": bin.actual_qty,
"transfer_qty": bin.actual_qty,
"uom": item_details.stock_uom,
"stock_uom": item_details.stock_uom,
"conversion_factor": 1,
"expense_account": expense_account,
"cost_center": cost_center,
"serial_no": serial_no
})
webnotes.bean(stock_entry).insert()
def execute(filters=None):
if not filters: filters = {}
supplier_naming_by = webnotes.conn.get_value("Buying Settings", None, "supp_master_name")
columns = get_columns(supplier_naming_by)
entries = get_gl_entries(filters)
account_map = dict(((r.name, r) for r in webnotes.conn.sql("""select acc.name,
supp.supplier_name, supp.name as supplier
from `tabAccount` acc, `tabSupplier` supp
where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1)))
entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
voucher_detail_map = get_voucher_details()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {})
invoiced_amount = gle.credit > 0 and gle.credit or 0
outstanding_amount = get_outstanding_amount(gle,
filters.get("report_date") or nowdate())
if abs(flt(outstanding_amount)) > 0.01:
paid_amount = invoiced_amount - outstanding_amount
row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no,
voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""),
voucher_details.get("bill_date", ""), invoiced_amount,
paid_amount, outstanding_amount]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = voucher_details.get("due_date", "")
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \
[account_map.get(gle.account).get("supplier") or ""]
if supplier_naming_by == "Naming Series":
row += [account_map.get(gle.account).get("supplier_name") or ""]
row += [account_supplier_type_map.get(gle.account), gle.remarks]
data.append(row)
for i in range(0, len(data)):
data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \
% ("/".join(["#Form", data[i][2], data[i][3]]),))
return columns, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
total_invoiced_amount = total_paid = total_outstanding = 0
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
paid_amount = get_paid_amount(gle, filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - paid_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no,
gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no,
bill_date, invoiced_amount, paid_amount, outstanding_amount]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount)
# Add to total
total_invoiced_amount += flt(invoiced_amount)
total_paid += flt(paid_amount)
total_outstanding += flt(outstanding_amount)
data.append(row)
if data:
data.append(["", "", "", "", "", "", "", "Total", "", total_invoiced_amount, total_paid,
total_outstanding, "", "", "", ""])
return columns, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
paid_amount = get_paid_amount(
gle,
filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - paid_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [
gle.posting_date, gle.account, gle.voucher_type,
gle.voucher_no, gle.remarks,
account_supplier_type_map.get(gle.account), due_date,
bill_no, bill_date, invoiced_amount, paid_amount,
outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
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 execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
account_customer = dict(
webnotes.conn.sql("""select account.name, customer.customer_name
from `tabAccount` account, `tabCustomer` customer
where account.master_type="Customer" and customer.name=account.master_name"""
))
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, upto_report_date=False)]
account_territory_map = get_account_territory_map()
si_due_date_map = get_si_due_date_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
due_date = (gle.voucher_type == "Sales Invoice") \
and si_due_date_map.get(gle.voucher_no) or ""
invoiced_amount = gle.debit > 0 and gle.debit or 0
outstanding_amount = get_outstanding_amount(
gle,
filters.get("report_date") or nowdate())
if abs(flt(outstanding_amount)) > 0.01:
payment_amount = invoiced_amount - outstanding_amount
row = [
gle.posting_date, gle.account,
account_customer.get(gle.account, ""), gle.voucher_type,
gle.voucher_no, gle.remarks, due_date,
account_territory_map.get(gle.account), invoiced_amount,
payment_amount, outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
def update_acc_bal(self,args):
args = eval(args)
self.doc.credit_balance = cint(self.doc.credit_balance) + cint(args.get('net_cr'))
self.doc.total_users = cint(self.doc.total_users) + cint(args.get('total_users'))
if cint(self.doc.is_trial_account) == 1:
if not self.doc.account_start_date:
self.doc.account_start_date = nowdate()
self.doc.is_trial_account = 0
self.doc.billing_cycle_date = nowdate()
self.doc.last_deduction_date = nowdate()
self.doc.save()
def declare_completed(self):
if self.doc.status == 'Open':
self.validate_for_pending_review()
self.doc.review_date = nowdate()
else:
self.validate_with_timesheet_dates()
self.validate_for_closed()
self.doc.closing_date = nowdate()
set(self.doc, 'status', 'Closed')
set(self.doc, 'docstatus', 1)
self.doc.save()
return cstr('true')
def get_fy_details(self, fy_start):
st = {'1st Jan':'01-01','1st Apr':'04-01','1st Jul':'07-01', '1st Oct': '10-01'}
curr_year = getdate(nowdate()).year
if cint(getdate(nowdate()).month) < cint((st[fy_start].split('-'))[0]):
curr_year = getdate(nowdate()).year - 1
stdt = cstr(curr_year)+'-'+cstr(st[fy_start])
#eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0]))
if(fy_start == '1st Jan'):
fy = cstr(getdate(nowdate()).year)
else:
fy = cstr(curr_year) + '-' + cstr(curr_year+1)
return fy,stdt
def get_fy_details(self, fy_start):
st = {"1st Jan": "01-01", "1st Apr": "04-01", "1st Jul": "07-01", "1st Oct": "10-01"}
curr_year = getdate(nowdate()).year
if cint(getdate(nowdate()).month) < cint((st[fy_start].split("-"))[0]):
curr_year = getdate(nowdate()).year - 1
stdt = cstr(curr_year) + "-" + cstr(st[fy_start])
# eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0]))
if fy_start == "1st Jan":
fy = cstr(getdate(nowdate()).year)
else:
fy = cstr(curr_year) + "-" + cstr(curr_year + 1)
return fy, stdt
def check_late_submission(self):
current_month = nowdate().split('-')[1]
current_day = nowdate().split('-')[2]
to_date_month = self.doc.end_date.split('-')[1]
if flt(current_day) > 5 and flt(current_month) != flt(to_date_month):
self.doc.status = 'Late'
msgprint("Late Submission")
if not self.doc.reason_for_late_submission:
msgprint("Please Enter the reason for LATE Submission")
raise Exception
else:
self.doc.status = 'On Time'
def calc_days(self):
if self.doc.billing_cycle_date:
next_bill_month = cint(nowdate().split('-')[1])
if cint(nowdate().split('-')[2]) > cint(self.doc.billing_cycle_date.split('-')[2]):
next_bill_month = cint(nowdate().split('-')[1]) + 1
next_bill_year = nowdate().split('-')[0]
if next_bill_month > 12:
next_bill_month = next_bill_month % 12
next_bill_year += 1
self.next_bill_sdate = cstr(next_bill_year)+'-'+cstr(next_bill_month)+'-'+(self.calc_next_day(next_bill_year,next_bill_month))
#msgprint("next_bill_month :::" + self.next_bill_sdate)
return date_diff(self.next_bill_sdate, nowdate())
def update_serial_no_warranty_amc_status(self, serial_no_list = []):
if serial_no_list:
sr_list = serial_no_list
else:
sr_list = [s[0] for s in sql("select name from `tabSerial No` where docstatus = 1")]
for s in sr_list:
sr = Document('Serial No', s)
if sr.amc_expiry_date:
sr.warranty_amc_status = (sr.amc_expiry_date < nowdate()) and 'Out of AMC' or 'Under AMC'
elif sr.warranty_expiry_date:
sr.warranty_amc_status = (sr.warranty_expiry_date < nowdate()) and 'Out of Warranty' or 'Under Warranty'
sr.save()
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
for gle in get_gl_entries(filters, upto_report_date=False)]
account_territory_map = get_account_territory_map()
si_due_date_map = get_si_due_date_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
total_invoiced_amount = total_payment = total_outstanding = 0
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
due_date = (gle.voucher_type == "Sales Invoice") \
and si_due_date_map.get(gle.voucher_no) or ""
invoiced_amount = gle.debit > 0 and gle.debit or 0
payment_amount = get_payment_amount(gle, filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - payment_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no,
gle.remarks, due_date, account_territory_map.get(gle.account),
invoiced_amount, payment_amount, outstanding_amount]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount)
# Add to total
total_invoiced_amount += flt(invoiced_amount)
total_payment += flt(payment_amount)
total_outstanding += flt(outstanding_amount)
data.append(row)
if data:
data.append(["", "", "", "", "", "", "Total", total_invoiced_amount, total_payment,
total_outstanding, "", "", "", ""])
return columns, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
account_supplier = dict(webnotes.conn.sql("""select account.name, supplier.supplier_name
from `tabAccount` account, `tabSupplier` supplier
where account.master_type="Supplier" and supplier.name=account.master_name"""))
entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
for gle in get_gl_entries(filters, before_report_date=False)]
account_supplier_type_map = get_account_supplier_type_map()
pi_map = get_pi_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
if gle.voucher_type == "Purchase Invoice":
pi_info = pi_map.get(gle.voucher_no)
due_date = pi_info.get("due_date")
bill_no = pi_info.get("bill_no")
bill_date = pi_info.get("bill_date")
else:
due_date = bill_no = bill_date = ""
invoiced_amount = gle.credit > 0 and gle.credit or 0
outstanding_amount = get_outstanding_amount(gle,
filters.get("report_date") or nowdate())
if abs(flt(outstanding_amount)) > 0.01:
paid_amount = invoiced_amount - outstanding_amount
row = [gle.posting_date, gle.account, account_supplier.get(gle.account, ""),
gle.voucher_type, gle.voucher_no,
gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no,
bill_date, invoiced_amount, paid_amount, outstanding_amount]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount)
data.append(row)
return columns, data
def get_fy_details(self, fy_start):
st = {'1st Jan':'01-01','1st Apr':'04-01','1st Jul':'07-01', '1st Oct': '10-01'}
curr_year = getdate(nowdate()).year
if cint(getdate(nowdate()).month) < cint((st[fy_start].split('-'))[0]):
curr_year = getdate(nowdate()).year - 1
stdt = cstr(curr_year)+'-'+cstr(st[fy_start])
#eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0]))
if(fy_start == '1st Jan'):
fy = cstr(getdate(nowdate()).year)
abbr = cstr(fy)[-2:]
else:
fy = cstr(curr_year) + '-' + cstr(curr_year+1)
abbr = cstr(curr_year)[-2:] + '-' + cstr(curr_year+1)[-2:]
return fy, stdt, abbr
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 execute(filters=None):
if not filters: filters = {}
columns = get_columns()
entries = get_gl_entries(filters)
entries_after_report_date = [[
gle.voucher_type, gle.voucher_no
] for gle in get_gl_entries(filters, upto_report_date=False)]
account_territory_map = get_account_territory_map()
si_due_date_map = get_si_due_date_map()
# Age of the invoice on this date
age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
and nowdate() or filters.get("report_date")
data = []
for gle in entries:
if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
due_date = (gle.voucher_type == "Sales Invoice") \
and si_due_date_map.get(gle.voucher_no) or ""
invoiced_amount = gle.debit > 0 and gle.debit or 0
payment_amount = get_payment_amount(
gle,
filters.get("report_date") or nowdate(),
entries_after_report_date)
outstanding_amount = invoiced_amount - payment_amount
if abs(flt(outstanding_amount)) > 0.01:
row = [
gle.posting_date, gle.account, gle.voucher_type,
gle.voucher_no, gle.remarks, due_date,
account_territory_map.get(gle.account), invoiced_amount,
payment_amount, outstanding_amount
]
# Ageing
if filters.get("ageing_based_on") == "Due Date":
ageing_based_on_date = due_date
else:
ageing_based_on_date = gle.posting_date
row += get_ageing_data(ageing_based_on_date, age_on,
outstanding_amount)
data.append(row)
return columns, data
def declare_completed(self):
if self.doc.status == 'Open':
self.validate_for_pending_review()
self.doc.review_date = nowdate()
else:
self.validate_with_timesheet_dates()
self.validate_for_closed()
self.doc.closing_date = nowdate()
self.doc.status = 'Closed'
self.doc.docstatus = 1
self.doc.save()
self.remove_event_from_calender()
return cstr('true')
def set_maintenance_status(self):
if not self.doc.warranty_expiry_date and not self.doc.amc_expiry_date:
self.doc.maintenance_status = None
if self.doc.warranty_expiry_date and self.doc.warranty_expiry_date < nowdate():
self.doc.maintenance_status = "Out of Warranty"
if self.doc.amc_expiry_date and self.doc.amc_expiry_date < nowdate():
self.doc.maintenance_status = "Out of AMC"
if self.doc.amc_expiry_date and self.doc.amc_expiry_date >= nowdate():
self.doc.maintenance_status = "Under AMC"
if self.doc.warranty_expiry_date and self.doc.warranty_expiry_date >= nowdate():
self.doc.maintenance_status = "Under Warranty"
def declare_completed(self):
if self.doc.status == 'Open':
self.validate_for_pending_review()
self.doc.review_date = nowdate()
else:
self.validate_with_timesheet_dates()
self.validate_for_closed()
self.doc.closing_date = nowdate()
self.doc.status = 'Closed'
self.doc.docstatus = 1
self.doc.save()
self.remove_event_from_calender()
return cstr('true')
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 send_event_digest():
today = nowdate()
for user in webnotes.conn.sql("""select name, email, language
from tabProfile where ifnull(enabled,0)=1
and user_type='System User' and name not in ('Guest', 'Administrator')""",
as_dict=1):
events = get_events(today, today, user.name, for_reminder=True)
if events:
text = ""
webnotes.set_user_lang(user.name, user.language)
webnotes.load_translations("core", "doctype", "event")
text = "<h3>" + webnotes._("Events In Today's Calendar") + "</h3>"
for e in events:
if e.all_day:
e.starts_on = "All Day"
text += "<h4>%(starts_on)s: %(subject)s</h4><p>%(description)s</p>" % e
text += '<p style="color: #888; font-size: 80%; margin-top: 20px; padding-top: 10px; border-top: 1px solid #eee;">'\
+ webnotes._("Daily Event Digest is sent for Calendar Events where reminders are set.")+'</p>'
from webnotes.utils.email_lib import sendmail
sendmail(recipients=user.email,
subject=webnotes._("Upcoming Events for Today"),
msg=text)
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 get_valuation_rate(self, arg):
""" Get average valuation rate of relevant warehouses
as per valuation method (MAR/FIFO)
as on costing date
"""
dt = self.doc.costing_date or nowdate()
time = self.doc.costing_date == nowdate() and now().split()[1] or '23:59'
warehouse = sql("select warehouse from `tabBin` where item_code = %s", arg['item_code'])
rate = []
for wh in warehouse:
r = get_obj('Valuation Control').get_incoming_rate(dt, time, arg['item_code'], wh[0], qty = arg.get('qty', 0))
if r:
rate.append(r)
return rate and flt(sum(rate))/len(rate) or 0
def create_production_order(self, company, pp_items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date': nowdate(),
'origin': 'MRP',
'wip_warehouse': '',
'fg_warehouse': '',
'status': 'Draft',
'company': company,
'fiscal_year': get_defaults()['fiscal_year']
}
pro_list = []
for d in pp_items:
pro_doc = Document('Production Order')
for key in d.keys():
pro_doc.fields[key] = d[key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new=1)
pro_list.append(pro_doc.name)
return pro_list
def get_valuation_rate(self, arg):
""" Get average valuation rate of relevant warehouses
as per valuation method (MAR/FIFO)
as on costing date
"""
dt = self.doc.costing_date or nowdate()
time = self.doc.costing_date == nowdate() and now().split()[1] or "23:59"
warehouse = sql("select warehouse from `tabBin` where item_code = %s", arg["item_code"])
rate = []
for wh in warehouse:
r = get_obj("Valuation Control").get_incoming_rate(dt, time, arg["item_code"], wh[0], qty=arg.get("qty", 0))
if r:
rate.append(r)
return rate and flt(sum(rate)) / len(rate) or 0
def sent_reminder_task():
task_list = sql("""
select subject, allocated_to, project, exp_start_date, exp_end_date,
priority, status, name, senders_name, opening_date, review_date, description
from tabTask
where task_email_notify=1
and sent_reminder=0
and status='Open'
and exp_start_date is not null""",
as_dict=1)
for i in task_list:
if date_diff(i['exp_start_date'], nowdate()) == 2:
msg2 = """<h2>Two days to complete: %(name)s</h2>
<p>This is a reminder for the task %(name)s has been assigned to you
by %(senders_name)s on %(opening_date)s</p>
<p><b>Subject:</b> %(subject)s </p>
<p><b>Project:</b> %(project)s</p>
<p><b>Expected Start Date:</b> %(exp_start_date)s</p>
<p><b>Expected End Date:</b> %(exp_end_date)s</p>
<p><b>Review Date:</b> %(review_date)s</p>
<p><b>Details:</b> %(description)s</p>
<p>If you have already completed this task, please update the system</p>
<p>Good Luck!</p>
<p>(This notification is autogenerated)</p>""" % i
sendmail(i['allocated_to'], sender='*****@*****.**', msg=msg2,send_now=1, \
subject='A task has been assigned')
sql("update `tabTask` set sent_reminder='1' where name='%(name)s' and allocated_to= '%(allocated_to)s'"
% i)
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_leave_details(self, lwp=None):
if not self.doc.fiscal_year:
self.doc.fiscal_year = webnotes.get_default("fiscal_year")
if not self.doc.month:
self.doc.month = "%02d" % getdate(nowdate()).month
m = get_obj('Salary Manager').get_month_details(self.doc.fiscal_year, self.doc.month)
holidays = self.get_holidays_for_employee(m)
if not cint(webnotes.conn.get_value("HR Settings", "HR Settings",
"include_holidays_in_total_working_days")):
m["month_days"] -= len(holidays)
if m["month_days"] < 0:
msgprint(_("Bummer! There are more holidays than working days this month."),
raise_exception=True)
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.doc.total_days_in_month = m['month_days']
self.doc.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m)) - flt(lwp)
self.doc.payment_days = payment_days > 0 and payment_days or 0
ss="select DATEDIFF(NOW(),(select date_of_joining from tabEmployee where name='"+self.doc.employee+"'))/365 from tabEmployee where name='"+self.doc.employee+"' and status='Left' and DATEDIFF(CURDATE(), (select relieving_date from tabEmployee where name='"+self.doc.employee+"')) <=31 and DATEDIFF(NOW(),(select date_of_joining from tabEmployee where name='"+self.doc.employee+"'))/365 >5"
# webnotes.errprint(ss)
res=webnotes.conn.sql(ss)
# webnotes.errprint(res)
if res:
lst_sal_qry="select net_pay from `tabSalary Slip` where employee='"+self.doc.employee+"' order by creation desc limit 1"
# webnotes.errprint(lst_sal_qry)
lst_sal=webnotes.conn.sql(lst_sal_qry)
grp_amt=(cint(lst_sal[0][0])* cint(res[0][0]))/27
# webnotes.errprint(grp_amt)
self.doc.grauity_amount=grp_amt
def has_pwd_expired(self):
if session['user'] != 'Administrator' and session['user'].lower() != 'demo':
last_pwd_date = None
try:
last_pwd_date = sql("select password_last_updated from tabProfile where name=%s",session['user'])[0][0] or ''
except:
return 'No'
if cstr(last_pwd_date) == '':
sql("update tabProfile set password_last_updated = '%s' where name='%s'"% (nowdate(),session['user']))
return 'No'
else:
date_diff = (getdate(nowdate()) - last_pwd_date).days
expiry_period = sql("select value from tabSingles where doctype='Control Panel' and field='password_expiry_days'")
if expiry_period and cint(expiry_period[0][0]) and cint(expiry_period[0][0]) < date_diff:
return 'Yes'
return 'No'
def validate(self):
if getdate(self.doc.timesheet_date) > getdate(nowdate()):
msgprint("You can not prepare timesheet for future date")
raise Exception
chk = sql(
"select name from `tabTimesheet` where timesheet_date=%s and owner=%s and status!='Cancelled' and name!=%s",
(self.doc.timesheet_date, self.doc.owner, self.doc.name))
if chk:
msgprint("You have already created timesheet " +
cstr(chk and chk[0][0] or '') + " for this date.")
raise Exception
import time
for d in getlist(self.doclist, 'timesheet_details'):
if d.act_start_time and d.act_end_time:
d1 = time.strptime(d.act_start_time, "%H:%M")
d2 = time.strptime(d.act_end_time, "%H:%M")
if d1 > d2:
msgprint(
"Start time can not be greater than end time. Check for Task Id : "
+ cstr(d.task_id))
raise Exception
elif d1 == d2:
msgprint(
"Start time and end time can not be same. Check for Task Id : "
+ cstr(d.task_id))
raise Exception
def raise_purchase_request(self):
"""
Raise Material Request if projected qty is less than qty required
Requested qty should be shortage qty considering minimum order qty
"""
if not self.doc.purchase_request_for_warehouse:
webnotes.msgprint(
"Please enter Warehouse for which Material Request will be raised",
raise_exception=1)
bom_dict = self.get_distinct_items_and_boms()[0]
self.get_raw_materials(bom_dict)
item_projected_qty = self.get_projected_qty()
from accounts.utils import get_fiscal_year
fiscal_year = get_fiscal_year(nowdate())[0]
items_to_be_requested = webnotes._dict()
for item in self.item_dict:
if flt(self.item_dict[item][0]) > item_projected_qty[item]:
# shortage
requested_qty = flt(
self.item_dict[item][0]) - item_projected_qty[item]
# comsider minimum order qty
requested_qty = requested_qty > flt(self.item_dict[item][3]) and \
requested_qty or flt(self.item_dict[item][3])
items_to_be_requested[item] = requested_qty
self.insert_purchase_request(items_to_be_requested, fiscal_year)
def set_pur_serial_no_values(self, obj, serial_no, d, s, new_rec):
item_details = sql("select item_group, warranty_period from `tabItem` where name = '%s' and \
(ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now()) " %(d.item_code), as_dict=1)
s.purchase_document_type = obj.doc.doctype
s.purchase_document_no = obj.doc.name
s.purchase_date = obj.doc.posting_date
s.purchase_time = obj.doc.posting_time
s.purchase_rate = d.valuation_rate or d.incoming_rate
s.item_code = d.item_code
s.item_name = d.item_name
s.brand = d.brand
s.description = d.description
s.item_group = item_details and item_details[0]['item_group'] or ''
s.warranty_period = item_details and item_details[0]['warranty_period'] or 0
s.supplier = obj.doc.supplier
s.supplier_name = obj.doc.supplier_name
s.address_display = obj.doc.address_display or obj.doc.supplier_address
s.warehouse = d.warehouse or d.t_warehouse
s.docstatus = 0
s.status = 'In Store'
s.modified = nowdate()
s.modified_by = session['user']
s.serial_no = serial_no
s.sle_exists = 1
s.fiscal_year = obj.doc.fiscal_year
s.company = obj.doc.company
s.save(new_rec)
def item_query(doctype, txt, searchfield, start, page_len, filters):
from webnotes.utils import nowdate
conditions = []
return webnotes.conn.sql(
"""select tabItem.name,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where tabItem.docstatus < 2
and (ifnull(tabItem.end_of_life, '') = '' or tabItem.end_of_life > %(today)s)
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype, searchfield)), {
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
})
def create_production_order(self, items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date': nowdate(),
'origin': 'MRP',
'wip_warehouse': '',
'fg_warehouse': '',
'status': 'Draft',
'fiscal_year': get_defaults()['fiscal_year']
}
pro_list = []
for item_so in items:
if item_so[1]:
self.validate_production_order_against_so(
item_so[0], item_so[1], items[item_so].get("qty"))
pro_doc = Document('Production Order')
pro_doc.production_item = item_so[0]
pro_doc.sales_order = item_so[1]
for key in items[item_so]:
pro_doc.fields[key] = items[item_so][key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new=1)
pro_list.append(pro_doc.name)
return pro_list
def make_bank_voucher(self):
self.set_flag()
"""
get default bank account,default salary acount from company
"""
#amt = self.get_total_salary()
com = webnotes.conn.sql("""select default_bank_account, default_expense_account from `tabCompany`
where name = '%s'""" % self.doc.company,as_list=1)
if not com[0][0] or not com[0][1]:
msgprint("You can set Default Bank Account in Company master.")
if not self.doc.jv:
jv = Document('Journal Voucher')
jv.voucher_type = 'Bank Voucher'
jv.user_remark = 'Referrals Payment'
jv.fiscal_year = '2013-14'
jv.total_credit = jv.total_debit = self.doc.total_amount
jv.company = self.doc.company
jv.posting_date = nowdate()
jv.save()
jvd = Document('Journal Voucher Detail')
jvd.account = com and com[0][0] or ''
jvd.credit = self.doc.total_amount
jvd.parent = jv.name
jvd.save()
jvd1 = Document('Journal Voucher Detail')
jvd1.account = com and com[0][1] or ''
jvd1.debit = self.doc.total_amount
jvd1.parent = jv.name
jvd1.save()
self.doc.jv = jv.name
self.doc.save()
def get_barcode_details(self, barcode):
item = webnotes.conn.sql(
"select name, end_of_life, is_sales_item, is_service_item \
from `tabItem` where barcode = %s",
barcode,
as_dict=1)
ret = {}
if not item:
msgprint("""No item found for this barcode: %s.
May be barcode not updated in item master. Please check""" % barcode)
elif item[0]['end_of_life'] and getdate(cstr(
item[0]['end_of_life'])) < nowdate():
msgprint(
"Item: %s has been expired. Please check 'End of Life' field in item master"
% item[0]['name'])
elif item[0]['is_sales_item'] == 'No' and item[0][
'is_service_item'] == 'No':
msgprint("Item: %s is not a sales or service item" %
item[0]['name'])
elif len(item) > 1:
msgprint(
"There are multiple item for this barcode. \nPlease select item code manually"
)
else:
ret = {'item_code': item and item[0]['name'] or ''}
return ret
def get_leave_details(self, lwp=None):
if not self.doc.fiscal_year:
self.doc.fiscal_year = webnotes.get_default("fiscal_year")
if not self.doc.month:
self.doc.month = "%02d" % getdate(nowdate()).month
m = get_obj('Salary Manager').get_month_details(
self.doc.fiscal_year, self.doc.month)
holidays = self.get_holidays_for_employee(m)
if not cint(
webnotes.conn.get_value(
"HR Settings", "HR Settings",
"include_holidays_in_total_working_days")):
m["month_days"] -= len(holidays)
if m["month_days"] < 0:
msgprint(_(
"Bummer! There are more holidays than working days this month."
),
raise_exception=True)
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.doc.total_days_in_month = m['month_days']
self.doc.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m)) - flt(lwp)
self.doc.payment_days = payment_days > 0 and payment_days or 0
