def test_update_qty(self):
po = create_purchase_order()
make_pr_against_po(po.name, 6)
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 6)
# Check received_qty after make_purchase_invoice without update_stock checked
pi1 = make_purchase_invoice(po.name)
pi1.get("items")[0].qty = 6
pi1.insert()
pi1.submit()
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 6)
# Check received_qty after make_purchase_invoice with update_stock checked
pi2 = make_purchase_invoice(po.name)
pi2.set("update_stock", 1)
pi2.get("items")[0].qty = 3
pi2.insert()
pi2.submit()
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 9)
def test_update_child_qty_rate(self):
mr = make_material_request(qty=10)
po = make_purchase_order(mr.name)
po.supplier = "_Test Supplier"
po.items[0].qty = 4
po.save()
po.submit()
create_pr_against_po(po.name)
make_purchase_invoice(po.name)
existing_ordered_qty = get_ordered_qty()
existing_requested_qty = get_requested_qty()
trans_item = json.dumps([{'item_code' : '_Test Item', 'rate' : 200, 'qty' : 7, 'docname': po.items[0].name}])
update_child_qty_rate('Purchase Order', trans_item, po.name)
mr.reload()
self.assertEqual(mr.items[0].ordered_qty, 7)
self.assertEqual(mr.per_ordered, 70)
self.assertEqual(get_requested_qty(), existing_requested_qty - 3)
po.reload()
self.assertEqual(po.get("items")[0].rate, 200)
self.assertEqual(po.get("items")[0].qty, 7)
self.assertEqual(po.get("items")[0].amount, 1400)
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 3)
def test_update_child_qty_rate(self):
mr = make_material_request(qty=10)
po = make_purchase_order(mr.name)
po.supplier = "_Test Supplier"
po.items[0].qty = 4
po.save()
po.submit()
create_pr_against_po(po.name)
make_purchase_invoice(po.name)
existing_ordered_qty = get_ordered_qty()
existing_requested_qty = get_requested_qty()
trans_item = json.dumps([{
'item_code': '_Test Item',
'rate': 200,
'qty': 7,
'docname': po.items[0].name
}])
update_child_qty_rate('Purchase Order', trans_item, po.name)
mr.reload()
self.assertEqual(mr.items[0].ordered_qty, 7)
self.assertEqual(mr.per_ordered, 70)
self.assertEqual(get_requested_qty(), existing_requested_qty - 3)
po.reload()
self.assertEqual(po.get("items")[0].rate, 200)
self.assertEqual(po.get("items")[0].qty, 7)
self.assertEqual(po.get("items")[0].amount, 1400)
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 3)
def test_make_purchase_invoice_with_terms(self):
po = create_purchase_order(do_not_save=True)
self.assertRaises(frappe.ValidationError, make_purchase_invoice, po.name)
po.update(
{"payment_terms_template": "_Test Payment Term Template"}
)
po.save()
po.submit()
self.assertEqual(po.payment_schedule[0].payment_amount, 2500.0)
self.assertEqual(getdate(po.payment_schedule[0].due_date), getdate(po.transaction_date))
self.assertEqual(po.payment_schedule[1].payment_amount, 2500.0)
self.assertEqual(getdate(po.payment_schedule[1].due_date), add_days(getdate(po.transaction_date), 30))
pi = make_purchase_invoice(po.name)
pi.save()
self.assertEqual(pi.doctype, "Purchase Invoice")
self.assertEqual(len(pi.get("items", [])), 1)
self.assertEqual(pi.payment_schedule[0].payment_amount, 2500.0)
self.assertEqual(getdate(pi.payment_schedule[0].due_date), getdate(po.transaction_date))
self.assertEqual(pi.payment_schedule[1].payment_amount, 2500.0)
self.assertEqual(getdate(pi.payment_schedule[1].due_date), add_days(getdate(po.transaction_date), 30))
def test_ordered_qty_against_pi_with_update_stock(self):
existing_ordered_qty = get_ordered_qty()
po = create_purchase_order()
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 10)
frappe.db.set_value('Item', '_Test Item', 'tolerance', 50)
pi = make_purchase_invoice(po.name)
pi.update_stock = 1
pi.items[0].qty = 12
pi.insert()
pi.submit()
self.assertEqual(get_ordered_qty(), existing_ordered_qty)
po.load_from_db()
self.assertEquals(po.get("items")[0].received_qty, 12)
pi.cancel()
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 10)
po.load_from_db()
self.assertEquals(po.get("items")[0].received_qty, 0)
def test_po_to_pi_and_po_to_pr_worflow_partial(self): """Test following behaviour: - Create PO - Create partial PI from PO and submit - Create PR from PO and submit """ from erpnext.buying.doctype.purchase_order import test_purchase_order from erpnext.buying.doctype.purchase_order import purchase_order po = test_purchase_order.create_purchase_order() pi = purchase_order.make_purchase_invoice(po.name) pi.items[0].qty /= 2 # roughly 50%, ^ this function only creates PI with 1 item. pi.submit() pr = purchase_order.make_purchase_receipt(po.name) pr.save() # per_billed is only updated after submission. self.assertEqual(flt(pr.per_billed), 0) pr.submit() pi.load_from_db() pr.load_from_db() self.assertEqual(pr.status, "To Bill") self.assertAlmostEqual(pr.per_billed, 50.0, places=2)
def test_make_purchase_invoice_with_terms(self):
po = create_purchase_order(do_not_save=True)
self.assertRaises(frappe.ValidationError, make_purchase_invoice,
po.name)
po.update({"payment_terms_template": "_Test Payment Term Template"})
po.save()
po.submit()
self.assertEqual(po.payment_schedule[0].payment_amount, 2500.0)
self.assertEqual(po.payment_schedule[0].due_date, po.transaction_date)
self.assertEqual(po.payment_schedule[1].payment_amount, 2500.0)
self.assertEqual(po.payment_schedule[1].due_date,
add_days(po.transaction_date, 30))
pi = make_purchase_invoice(po.name)
pi.save()
self.assertEqual(pi.doctype, "Purchase Invoice")
self.assertEqual(len(pi.get("items", [])), 1)
self.assertEqual(pi.payment_schedule[0].payment_amount, 2500.0)
self.assertEqual(pi.payment_schedule[0].due_date, po.transaction_date)
self.assertEqual(pi.payment_schedule[1].payment_amount, 2500.0)
self.assertEqual(pi.payment_schedule[1].due_date,
add_days(po.transaction_date, 30))
def test_ordered_qty_against_pi_with_update_stock(self):
existing_ordered_qty = get_ordered_qty()
po = create_purchase_order()
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 10)
frappe.db.set_value('Item', '_Test Item', 'tolerance', 50)
pi = make_purchase_invoice(po.name)
pi.update_stock = 1
pi.items[0].qty = 12
pi.insert()
pi.submit()
self.assertEqual(get_ordered_qty(), existing_ordered_qty)
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 12)
pi.cancel()
self.assertEqual(get_ordered_qty(), existing_ordered_qty + 10)
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 0)
def test_terms_does_not_copy(self):
po = create_purchase_order()
self.assertTrue(po.get('payment_schedule'))
pi = make_purchase_invoice(po.name)
self.assertFalse(pi.get('payment_schedule'))
def test_terms_does_not_copy(self):
po = create_purchase_order()
self.assertTrue(po.get('payment_schedule'))
pi = make_purchase_invoice(po.name)
self.assertFalse(pi.get('payment_schedule'))
def test_terms_copied(self):
po = create_purchase_order(do_not_save=1)
po.payment_terms_template = '_Test Payment Term Template'
po.insert()
po.submit()
self.assertTrue(po.get('payment_schedule'))
pi = make_purchase_invoice(po.name)
pi.insert()
self.assertTrue(pi.get('payment_schedule'))
def create_pi_for_po(wbtname, po):
from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_invoice
pi = make_purchase_invoice(po.name)
pi.weighbridge_ticket = wbtname
pi.save()
frappe.db.commit()
frappe.msgprint(_("Purchase Invoice %s created successfully." % (pi.name)))
return pi
def create_pi_for_po(wbtname, po):
from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_invoice
pi = make_purchase_invoice(po.name)
pi.weighbridge_ticket = wbtname
pi.save()
frappe.db.commit()
frappe.msgprint(_("Purchase Invoice %s created successfully." % (pi.name)))
return pi
def test_make_purchase_invoice(self):
po = create_purchase_order(do_not_submit=True)
self.assertRaises(frappe.ValidationError, make_purchase_invoice, po.name)
po.submit()
pi = make_purchase_invoice(po.name)
self.assertEquals(pi.doctype, "Purchase Invoice")
self.assertEquals(len(pi.get("items", [])), 1)
def test_terms_copied(self):
po = create_purchase_order(do_not_save=1)
po.payment_terms_template = '_Test Payment Term Template'
po.insert()
po.submit()
self.assertTrue(po.get('payment_schedule'))
pi = make_purchase_invoice(po.name)
pi.insert()
self.assertTrue(pi.get('payment_schedule'))
def test_make_purchase_invoice(self):
from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_invoice
po = frappe.copy_doc(test_records[0]).insert()
self.assertRaises(frappe.ValidationError, make_purchase_invoice, po.name)
po = frappe.get_doc("Purchase Order", po.name)
po.submit()
pi = make_purchase_invoice(po.name)
self.assertEquals(pi.doctype, "Purchase Invoice")
self.assertEquals(len(pi.get("entries", [])), len(test_records[0]["po_details"]))
pi.posting_date = "2013-05-12"
pi.bill_no = "NA"
frappe.get_doc(pi).insert()
def test_make_purchase_invoice(self):
from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_invoice
po = frappe.bean(copy=test_records[0]).insert()
self.assertRaises(frappe.ValidationError, make_purchase_invoice,
po.doc.name)
po = frappe.bean("Purchase Order", po.doc.name)
po.submit()
pi = make_purchase_invoice(po.doc.name)
self.assertEquals(pi[0]["doctype"], "Purchase Invoice")
self.assertEquals(len(pi), len(test_records[0]))
pi[0]["posting_date"] = "2013-05-12"
pi[0].bill_no = "NA"
frappe.bean(pi).insert()
def test_make_purchase_invoice(self):
from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_invoice
po = frappe.copy_doc(test_records[0]).insert()
self.assertRaises(frappe.ValidationError, make_purchase_invoice,
po.name)
po = frappe.get_doc("Purchase Order", po.name)
po.submit()
pi = make_purchase_invoice(po.name)
self.assertEquals(pi.doctype, "Purchase Invoice")
self.assertEquals(len(pi.get("entries", [])),
len(test_records[0]["po_details"]))
pi.posting_date = "2013-05-12"
pi.bill_no = "NA"
frappe.get_doc(pi).insert()
def make_invoice(sales_order_name, datepaid, paid=0):
# print SI_dict["inflow_file"]
pi = make_purchase_invoice(sales_order_name)
pi.inflow_file = sales_order_name
pi.set_posting_time = 1
# datepaid = SI_dict['DatePaid']
# if not datepaid:
# datepaid = SI_dict["OrderDate"]
# else:
# datepaid = parser.parse(datepaid)
pi.posting_date = datepaid.date()
pi.posting_time = str(datepaid.time())
pi.save()
# if sales_order_name:
if paid == 1:
so = frappe.get_doc("Purchase Order", sales_order_name)
# print " Making Payment request. Per billed",so.per_billed
# if flt(so.per_billed) != 100:
payment_request = make_payment_request(dt="Purchase Order",
dn=so.name,
recipient_id="",
submit_doc=True,
mute_email=True,
use_dummy_message=True,
inflow_file=sales_order_name,
posting_date=datepaid.date(),
posting_time=str(
datepaid.time()))
payment_entry = frappe.get_doc(make_payment_entry(
payment_request.name))
# payment_entry.posting_date = frappe.flags.current_date
payment_entry.posting_date = datepaid.date()
payment_entry.posting_time = str(datepaid.time())
payment_entry.set_posting_time = 1
payment_entry.inflow_file = sales_order_name
payment_entry.submit()
pi.submit()
frappe.db.commit()
def test_return_against_purchase_order(self):
po = create_purchase_order()
pr = make_pr_against_po(po.name, 6)
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 6)
pi2 = make_purchase_invoice(po.name)
pi2.set("update_stock", 1)
pi2.get("items")[0].qty = 3
pi2.insert()
pi2.submit()
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 9)
# Make return purchase receipt, purchase invoice and check quantity
from erpnext.stock.doctype.purchase_receipt.test_purchase_receipt \
import make_purchase_receipt as make_purchase_receipt_return
from erpnext.accounts.doctype.purchase_invoice.test_purchase_invoice \
import make_purchase_invoice as make_purchase_invoice_return
pr1 = make_purchase_receipt_return(is_return=1,
return_against=pr.name,
qty=-3,
do_not_submit=True)
pr1.items[0].purchase_order = po.name
pr1.items[0].purchase_order_item = po.items[0].name
pr1.submit()
pi1 = make_purchase_invoice_return(is_return=1,
return_against=pi2.name,
qty=-1,
update_stock=1,
do_not_submit=True)
pi1.items[0].purchase_order = po.name
pi1.items[0].po_detail = po.items[0].name
pi1.submit()
po.load_from_db()
self.assertEqual(po.get("items")[0].received_qty, 5)
def test_po_to_pi_and_po_to_pr_worflow_full(self): """Test following behaviour: - Create PO - Create PI from PO and submit - Create PR from PO and submit """ from erpnext.buying.doctype.purchase_order import test_purchase_order from erpnext.buying.doctype.purchase_order import purchase_order po = test_purchase_order.create_purchase_order() pi = purchase_order.make_purchase_invoice(po.name) pi.submit() pr = purchase_order.make_purchase_receipt(po.name) pr.submit() pr.load_from_db() self.assertEqual(pr.status, "Completed") self.assertEqual(pr.per_billed, 100)
def send_to_company(name):
doc = frappe.get_doc("Sales Invoice", name)
po = lookup_po(doc)
if flt(po.per_billed, 2) < 100:
pi = make_purchase_invoice(po.name)
pi.bill_no = doc.name
pi.bill_date = today()
items = []
for item in pi.items:
for source_item in doc.items:
if source_item.item_code == item.item_code:
item.update({"qty": source_item.qty})
items.append(item)
pi.set("items", items)
pi.save()
frappe.db.commit()
return pi
def test_partial_transfer_serial_no_components_based_on_material_transfer_for_purchase_invoice(
self, ):
"""
- Set backflush based on Material Transferred for Subcontract
- Create subcontracted PO for the item Subcontracted Item SA2.
- Transfer the partial components from Stores to Supplier warehouse with serial nos.
- Create partial purchase receipt against the PO and change the qty manually.
- Transfer the remaining components from Stores to Supplier warehouse with serial nos.
- Create purchase receipt for remaining qty against the PO and change the qty manually.
"""
set_backflush_based_on("Material Transferred for Subcontract")
item_code = "Subcontracted Item SA2"
items = [{
"warehouse": "_Test Warehouse - _TC",
"item_code": item_code,
"qty": 10,
"rate": 100
}]
rm_items = [{"item_code": "Subcontracted SRM Item 2", "qty": 5}]
itemwise_details = make_stock_in_entry(rm_items=rm_items)
po = create_purchase_order(
rm_items=items,
is_subcontracted=1,
supplier_warehouse="_Test Warehouse 1 - _TC")
for d in rm_items:
d["po_detail"] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details),
)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 5
pr1.items[0].expense_account = "Stock Adjustment - _TC"
pr1.save()
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, 3)
self.assertEqual(sorted(value.serial_no),
sorted(details.serial_no[0:3]))
pr1.load_from_db()
pr1.supplied_items[0].consumed_qty = 5
pr1.supplied_items[0].serial_no = "\n".join(
itemwise_details[pr1.supplied_items[0].rm_item_code]["serial_no"])
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, details.qty)
self.assertEqual(sorted(value.serial_no),
sorted(details.serial_no))
itemwise_details = make_stock_in_entry(rm_items=rm_items)
for d in rm_items:
d["po_detail"] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details),
)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].expense_account = "Stock Adjustment - _TC"
pr1.submit()
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, details.qty)
self.assertEqual(sorted(value.serial_no),
sorted(details.serial_no))
def test_reserved_qty_subcontract_po(self):
# Make stock available for raw materials
make_stock_entry(target="_Test Warehouse - _TC",
qty=10,
basic_rate=100)
make_stock_entry(target="_Test Warehouse - _TC",
item_code="_Test Item Home Desktop 100",
qty=20,
basic_rate=100)
bin1 = frappe.db.get_value(
"Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname=["reserved_qty_for_sub_contract", "projected_qty"],
as_dict=1)
# Submit PO
po = create_purchase_order(item_code="_Test FG Item",
is_subcontracted="Yes")
bin2 = frappe.db.get_value(
"Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname=["reserved_qty_for_sub_contract", "projected_qty"],
as_dict=1)
self.assertEquals(bin2.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract + 10)
self.assertEquals(bin2.projected_qty, bin1.projected_qty - 10)
# Create stock transfer
rm_item = [{
"item_code": "_Test FG Item",
"rm_item_code": "_Test Item",
"item_name": "_Test Item",
"qty": 6,
"warehouse": "_Test Warehouse - _TC",
"rate": 100,
"amount": 600,
"stock_uom": "Nos"
}]
rm_item_string = json.dumps(rm_item)
se = frappe.get_doc(
make_subcontract_transfer_entry(po.name, rm_item_string))
se.to_warehouse = "_Test Warehouse 1 - _TC"
se.save()
se.submit()
bin3 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin3.reserved_qty_for_sub_contract,
bin2.reserved_qty_for_sub_contract - 6)
# close PO
po.update_status("Closed")
bin4 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin4.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract)
# Re-open PO
po.update_status("Submitted")
bin5 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin5.reserved_qty_for_sub_contract,
bin2.reserved_qty_for_sub_contract - 6)
# make Purchase Receipt against PO
pr = make_purchase_receipt(po.name)
pr.supplier_warehouse = "_Test Warehouse 1 - _TC"
pr.save()
pr.submit()
bin6 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin6.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract)
# Cancel PR
pr.cancel()
bin7 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin7.reserved_qty_for_sub_contract,
bin2.reserved_qty_for_sub_contract - 6)
# Make Purchase Invoice
pi = make_purchase_invoice(po.name)
pi.update_stock = 1
pi.supplier_warehouse = "_Test Warehouse 1 - _TC"
pi.insert()
pi.submit()
bin8 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin8.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract)
# Cancel PR
pi.cancel()
bin9 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin9.reserved_qty_for_sub_contract,
bin2.reserved_qty_for_sub_contract - 6)
# Cancel Stock Entry
se.cancel()
bin10 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin10.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract + 10)
# Cancel PO
po.reload()
po.cancel()
bin11 = frappe.db.get_value("Bin",
filters={
"warehouse": "_Test Warehouse - _TC",
"item_code": "_Test Item"
},
fieldname="reserved_qty_for_sub_contract",
as_dict=1)
self.assertEquals(bin11.reserved_qty_for_sub_contract,
bin1.reserved_qty_for_sub_contract)
def start_import(file):
import csv
import os
current_customer = ""
current_order = ""
SI_dict = {}
last_single_SI_dict = {}
SI_items = []
last_single_SI_items = []
paid_and_fulfilled_items = []
last_single_paid_and_fulfilled_items = []
fulfilled_items = []
last_single_fulfilled_items = []
paid_items = []
last_single_paid_items = []
paid_pi = {}
# input_file = csv.DictReader(open(os.path.dirname(os.path.abspath(__file__))+'/data/inFlow_PurchaseOrder_test.csv'))
input_file = csv.DictReader(open(os.path.dirname(os.path.abspath(__file__))+'/data/'+file))
# current_customer = input_file[0]["Customer"]
income_accounts = "5111 - Cost of Goods Sold - DCL"
# income_accounts = "Sales - J"
cost_centers = "Main - DCL"
# cost_centers = "Main - J"
rows = list(input_file)
total_paid = 0.0
last_single_total_paid = 0.0
# print rows
totalrows = len(rows) - 1
for i,row in enumerate(rows):
# print row
if row["Location"].strip():
if row["Location"].strip() == "DCL House, Plot 1299 Fumilayo Ransome Kuti Way, Area 3, PMB 690 Garki, Abuja":
to_warehouse = "DCLWarehouse - Abuja - DCL"
elif row[
"Location"].strip() == "DCL Laboratory Products Ltd, Plot 5 Block 4 Etal Avenue off Kudirat Abiola Way by NNPC Lagos NG - DCL":
to_warehouse = "Lagos Warehouse - DCL"
else:
to_warehouse = row["Location"].strip() + " - DCL"
else:
to_warehouse = ""
#make item non stock
item_code1 = row["ItemName"].strip()
frappe.db.sql("""UPDATE `tabItem` SET is_stock_item=1 WHERE item_code=%s""", (item_code1))
frappe.db.commit()
to_warehouse = "DCLWarehouse - Abuja - DCL"
if row["Location"].strip():
exists_cat = frappe.db.sql("""SELECT Count(*) FROM `tabWarehouse` WHERE warehouse_name=%s""", (row["Location"].strip()))
# print exists_cat, row["Location"]
if exists_cat[0][0] == 0:
item_code = row["Location"]
SI = frappe.get_doc({"doctype": "Warehouse",
"warehouse_name": item_code.strip()
})
SI_created = SI.insert(ignore_permissions=True)
frappe.db.commit()
item_code1 = row["ItemName"].strip()
# if row[
# "ItemName"] == "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser.\nSupplied specifically without top plate (ring) for use only with the autoclave / steam sterilizer.":
if "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser." in item_code1:
item_code1 = "Kerosene Stove"
exists_cat = frappe.db.sql("""SELECT Count(*) FROM `tabItem` WHERE item_code=%s""", (item_code1))
# print exists_cat
if exists_cat[0][0] == 0:
SI = frappe.get_doc({"doctype": "Item",
"item_code": item_code1,
"description": row["ItemDescription"],
# "item_group": row["Category"].strip() + " Category"
"item_group": "All Item Groups"
})
SI_created = SI.insert(ignore_permissions=True)
frappe.db.commit()
#CREATE SUPPLIER IF NOT EXISTS
exists_supplier = frappe.db.sql("""SELECT Count(*) FROM `tabSupplier` WHERE name=%s""",(row["Vendor"].strip()))
if exists_supplier[0][0] == 0:
frappe.get_doc({"doctype":"Supplier","supplier_name":row["Vendor"].strip(),
"supplier_group":"All Supplier Groups","supplier_type":"Company"}).insert()
frappe.db.commit()
if i==0:
current_customer = row["Vendor"].strip()
current_order = row["OrderNumber"]
dt = parser.parse(row["OrderDate"])
currency = ""
conversion_rate = 0.0
if float(row["ExchangeRate"]) != 0.0 and float(row["ExchangeRate"]) != 1.0:
currency = row["CurrencyCode"]
conversion_rate = float(row["ExchangeRate"])
elif float(row["ExchangeRate"]) == 0.0 or float(row["ExchangeRate"]) == 1.0:
currency = "NGN"
conversion_rate = 0.0
po_status = ""
if row["InventoryStatus"] == "Fulfilled" and row["PaymentStatus"] == "Paid":
po_status = "Completed"
elif row["InventoryStatus"] == "Unfulfilled" and row["PaymentStatus"] == "Paid":
po_status = "To Receive"
elif row["InventoryStatus"] == "Fulfilled" and row["PaymentStatus"] == "Unpaid":
po_status = "To Bill"
SI_dict = {"doctype": "Purchase Order",
"title": current_customer,
"supplier": current_customer,
"posting_date": dt.date(),
"schedule_date": dt.date(), # TODO + 30 days
"transaction_date": dt.date(),
# "due_date": row["DueDate"],
"po_status":po_status,
"due_date": dt.date(),
"items": SI_items,
# "docstatus": 1,
"outstanding_amount": total_paid,
"name": row["OrderNumber"],
"OrderDate":dt,
"inflow_remarks":row["OrderRemarks"],
"inflow_file":file,
"currency": currency,
"conversion_rate":conversion_rate
}
# print(current_customer,row["Vendor"],totalrows)
print " ",totalrows,i
if current_customer != row["Vendor"].strip() or current_customer != row["Vendor"].strip() \
or current_order!= row["OrderNumber"] or totalrows == i:
if totalrows == i and current_customer == row["Vendor"]:
print "LAST ROW!"
item_code1 = row["ItemName"].strip()
# if row[
# "ItemName"] == "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser.\nSupplied specifically without top plate (ring) for use only with the autoclave / steam sterilizer.":
if "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser." in item_code1:
item_code1 = "Kerosene Stove"
print row["ItemName"]
SI_item = {
# "item_code": installment.item, # test
"description": row["ItemDescription"].strip() or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]),2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": float(row["ItemQuantity"]),
"received_qty": float(row["ItemQuantity"]),
# "warehouse":row["Location"].strip() +" - DCL",
"warehouse":to_warehouse,
"InventoryStatus":row["InventoryStatus"],
"PaymentStatus":row["PaymentStatus"],
"OrderDate":row["OrderDate"]
}
SI_items.append(SI_item)
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] == "Fulfilled":
paid_and_fulfilled_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] != "Fulfilled":
paid_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] != "Paid" and row["InventoryStatus"] == "Fulfilled":
fulfilled_items.append({
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
total_paid += float(row["ItemSubtotal"])
elif totalrows == i:
print "LAST SINGLE ROW!"
item_code1 = row["ItemName"].strip()
# if row[
# "ItemName"] == "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser.\nSupplied specifically without top plate (ring) for use only with the autoclave / steam sterilizer.":
if "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser." in item_code1:
item_code1 = "Kerosene Stove"
last_single_SI_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"].strip() or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse":row["Location"].strip() +" - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"],
"OrderDate": row["OrderDate"]
})
print last_single_SI_items
last_single_SI_dict = {"doctype": "Purchase Order",
"title": current_customer,
"supplier": current_customer,
"posting_date": dt.date(),
"schedule_date": dt.date(), # TODO + 30 days
"transaction_date": dt.date(),
# "due_date": row["DueDate"],
"due_date": dt.date(),
"items": last_single_SI_items,
# "docstatus": 1,
"outstanding_amount": total_paid,
"name": row["OrderNumber"],
"OrderDate": dt,
"inflow_remarks": row["OrderRemarks"],
"currency": currency,
"conversion_rate": conversion_rate,
"inflow_file":file
}
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] == "Fulfilled":
last_single_paid_and_fulfilled_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] != "Fulfilled":
last_single_paid_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] != "Paid" and row["InventoryStatus"] == "Fulfilled":
last_single_fulfilled_items.append({
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
last_single_total_paid += float(row["ItemSubtotal"])
SI_dict.update({"outstanding_amount":total_paid,
"inflow_file":file,
"per_received":100.0,
"per_billed":100.0
})
print SI_dict["items"]
SI = frappe.get_doc(SI_dict)
# print SI_dict
print(" CURRENT:",current_order,SI_dict["po_status"])
SI_created = SI.insert(ignore_permissions=True)
SI_created.submit()
"""
To Receive and Bill
To Bill
To Receive
Completed
"""
# print " PO Status: ",SI_dict["po_status"]
# if SI_dict["po_status"] == "To Receive and Bill":
# print "To Receive and Bill"
# SI_created.db_set("per_received", 100, update_modified=False)
# SI_created.db_set("per_billed", 100, update_modified=False)
# elif SI_dict["po_status"] == "To Receive":
# print "To Receive"
# SI_created.db_set("per_billed", 100, update_modified=False)
# if SI_dict["po_status"] == "To Bill":
# print "To Bill"
# SI_created.db_set("per_received", 100, update_modified=False)
# SI_created.status = SI_dict["po_status"]
frappe.db.commit()
#/home/jvfiel/frappe-v11/apps/erpnext/erpnext/buying/doctype/purchase_order/purchase_order.py
from erpnext.buying.doctype.purchase_order.purchase_order import update_status
#/home/jvfiel/frappe-v11/apps/frappe/frappe/model/rename_doc.py
rename_doc("Purchase Order",SI_created.name,current_order,force=True)
frappe.db.commit()
# update_status(SI_dict["po_status"], current_order)
# SI_created.set_status(update=True, status=SI_dict["po_status"])
#self.db_set('status', self.status, update_modified = update_modified)
# SI_created.db_set(fieldname='status',value=SI_dict['po_status'])
# frappe.db.sql("""UPDATE `tabPurchase Order` SET status=%s WHERE name=%s""",(SI_dict["po_status"],current_order),debug=1)
#self.db_set("per_received", flt(received_qty / total_qty) * 100, update_modified=False)
# frappe.db.commit()
print paid_and_fulfilled_items
if paid_and_fulfilled_items:
pi = make_purchase_invoice(current_order)
if to_warehouse:
pi.update_stock = 1
pi.is_paid = 1
pi.items = []
pi.posting_date = SI_dict['OrderDate'].date()
pi.posting_time = str(SI_dict['OrderDate'].time())
pi_total = 0.0
if float(SI_dict["conversion_rate"]) != 0.0 and float(SI_dict["conversion_rate"]) != 1.0:
pi.currency = SI_dict["currency"]
pi.conversion_rate = float(SI_dict["conversion_rate"])
elif float(SI_dict["conversion_rate"]) == 0.0 or float(SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in paid_and_fulfilled_items:
# if float(item["rate"]) < 0:
# zeros.append(item)
# else:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
# nl.base_rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += float(nl.rate) * float(nl.qty)
print(nl.rate)
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
print " ", paid_and_fulfilled_items
print " Paid and Fulfilled PI Total", pi_total,current_order,pi.currency
# print " ", pi.as_dict()["items"]
if pi_total:
pi.mode_of_payment = "Cash"
# if pi.conversion_rate:
# print "<<<<",pi.grand_total,">>>>"
# print "<<<<",pi.conversion_rate,">>>>"
# print "<<<<",pi.grand_total * pi.conversion_rate,">>>>"
pi.paid_amount = pi.grand_total
pi.base_paid_amount = pi.outstanding_amount
pi.insert()
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
for item in zeros:
make_stock_entry(item_code=item["item_code"], qty=item['qty'],
to_warehouse=item["warehouse"],
valuation_rate=1, remarks="This is affected by data import. " + file,
posting_date=pi.posting_date,
posting_time=pi.posting_time,
set_posting_time=1, inflow_file=file)
frappe.db.commit()
print "Stock entry created."
if paid_items:
pi = make_purchase_invoice(current_order)
# pi.update_stock = 1
pi.is_paid = 1
pi.items = []
pi.posting_date = SI_dict['OrderDate'].date()
pi.posting_time = str(SI_dict['OrderDate'].time())
pi_total = 0.0
if float(SI_dict["conversion_rate"]) != 0.0 and float(SI_dict["conversion_rate"]) != 1.0:
pi.currency = SI_dict["currency"]
pi.conversion_rate = float(SI_dict["conversion_rate"])
elif float(SI_dict["conversion_rate"]) == 0.0 or float(SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in paid_items:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += float(nl.rate) * float(nl.qty)
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
print " Paid Items:", paid_items
print " Paid Items Only PI Total", pi_total,current_order,pi.currency
# print " ", pi.as_dict()["items"]
if pi_total:
pi.mode_of_payment = "Cash"
pi.insert()
frappe.db.commit()
if pi.currency != "NGN":
pi.paid_amount = pi.grand_total
pi.base_paid_amount = pi.outstanding_amount
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
pass
if fulfilled_items:
pi = make_purchase_invoice(current_order)
if to_warehouse:
pi.update_stock = 1
# pi.is_paid = 1
pi.items = []
pi.posting_date = SI_dict['OrderDate'].date()
pi.posting_time = str(SI_dict['OrderDate'].time())
pi_total = 0.0
if float(SI_dict["conversion_rate"]) != 0.0 and float(
SI_dict["conversion_rate"]) != 1.0:
pi.currency = SI_dict["currency"]
pi.conversion_rate = float(SI_dict["conversion_rate"])
elif float(SI_dict["conversion_rate"]) == 0.0 or float(
SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in fulfilled_items:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.received_qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += abs(float(nl.rate) * float(nl.qty))
# print nl.rate
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
print " ", fulfilled_items
print " Fulfilled Items Only PI Total", pi_total, current_order, pi.currency
print " conversion rate", pi.conversion_rate
if pi_total:
pi.mode_of_payment = "Cash"
pi.insert()
frappe.db.commit()
if pi.currency != "NGN":
# pi.paid_amount = pi.grand_total
# pi.base_paid_amount = pi.outstanding_amount
pi.rounding_adjustment = 0.0
pi.disable_rounded_total = 1
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
pass
current_customer = row["Vendor"].strip()
current_order = row["OrderNumber"]
dt = parser.parse(row["OrderDate"])
SI_items = []
currency = ""
conversion_rate = 0.0
if float(row["ExchangeRate"]) != 0.0 and float(row["ExchangeRate"]) != 1.0:
currency = row["CurrencyCode"]
conversion_rate = float(row["ExchangeRate"])
elif float(row["ExchangeRate"]) == 0.0 or float(row["ExchangeRate"]) == 1.0:
currency = "NGN"
conversion_rate = 0.0
po_status = ""
if row["InventoryStatus"] == "Fulfilled" and row["PaymentStatus"] == "Paid":
po_status = "Completed"
elif row["InventoryStatus"] == "Unfulfilled" and row["PaymentStatus"] == "Paid":
po_status = "To Receive"
elif row["InventoryStatus"] == "Fulfilled" and row["PaymentStatus"] == "Unpaid":
po_status = "To Bill"
SI_dict = {"doctype": "Purchase Order",
"title": current_customer,
"supplier": current_customer,
"posting_date": dt.date(),
"schedule_date": dt.date(), # TODO + 30 days
"transaction_date": dt.date(),
# "due_date": row["DueDate"],
"po_status":po_status,
"due_date": dt.date(),
"items": SI_items,
# "docstatus": 1,
"outstanding_amount": total_paid,
"name": row["OrderNumber"],
"OrderDate":dt,
"inflow_remarks": row["OrderRemarks"],
"inflow_file": file,
"currency": currency,
"conversion_rate": conversion_rate
}
paid_items = []
fulfilled_items = []
paid_and_fulfilled_items = []
# else:
item_code1 = row["ItemName"].strip()
# if row[
# "ItemName"] == "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser.\nSupplied specifically without top plate (ring) for use only with the autoclave / steam sterilizer.":
if "Kerosene stove, four burner pressure type for use with 39L autoclave / steriliser." in item_code1:
item_code1 = "Kerosene Stove"
SI_item = {
# "item_code": installment.item, # test
"description": row["ItemDescription"].strip() or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "warehouse": row["Location"].strip() +" - DCL",
"warehouse": to_warehouse,
"rate": float(row["ItemUnitPrice"]),
"conversion_factor":1,
"uom":"Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": float(row["ItemQuantity"]),
"received_qty": float(row["ItemQuantity"]),
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"],
"OrderDate":row["OrderDate"]
}
SI_items.append(SI_item)
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] == "Fulfilled":
paid_and_fulfilled_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] == "Paid" and row["InventoryStatus"] != "Fulfilled":
paid_items.append({
# "item_code": installment.item, # test
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
# "rate": truncate(float(row["ItemSubtotal"]),2),
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
if row["PaymentStatus"] != "Paid" and row["InventoryStatus"] == "Fulfilled":
fulfilled_items.append({
"description": row["ItemDescription"] or row["ItemName"],
"item_name": item_code1,
"item_code": item_code1,
"rate": truncate(float(row["ItemUnitPrice"]), 2),
"conversion_factor": 1,
"uom": "Nos",
"expense_account": income_accounts,
"cost_center": cost_centers,
"qty": row["ItemQuantity"],
# "warehouse": row["Location"].strip() + " - DCL",
"warehouse": to_warehouse,
"InventoryStatus": row["InventoryStatus"],
"PaymentStatus": row["PaymentStatus"]
})
total_paid +=float(row["ItemSubtotal"])
if last_single_SI_dict != {}:
print "* END *", current_order
print last_single_SI_dict["items"]
SI = frappe.get_doc(last_single_SI_dict)
# print SI_dict
SI_created = SI.insert(ignore_permissions=True)
frappe.db.commit()
SI_created.submit()
frappe.db.commit()
rename_doc("Purchase Order", SI_created.name, current_order, force=True)
frappe.db.commit()
if last_single_paid_and_fulfilled_items:
pi = make_purchase_invoice(current_order)
pi.update_stock = 1
pi.is_paid = 1
pi.items = []
pi.posting_date = SI_dict['OrderDate'].date()
pi.posting_time = str(SI_dict['OrderDate'].time())
pi_total = 0.0
if float(last_single_SI_dict["conversion_rate"]) != 0.0 and float(last_single_SI_dict["conversion_rate"]) != 1.0:
pi.currency = SI_dict["currency"]
pi.conversion_rate = float(SI_dict["conversion_rate"])
elif float(last_single_SI_dict["conversion_rate"]) == 0.0 or float(last_single_SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in last_single_paid_and_fulfilled_items:
# if float(item["rate"]) < 0:
# zeros.append(item)
# else:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
# nl.base_rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += float(nl.rate) * float(nl.qty)
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
# print " ", paid_and_fulfilled_items
print " Paid and Fulfilled PI Total", pi_total, current_order, pi.currency
# print " ", pi.as_dict()["items"]
if pi_total:
pi.mode_of_payment = "Cash"
# if pi.conversion_rate:
# print "<<<<",pi.grand_total,">>>>"
# print "<<<<",pi.conversion_rate,">>>>"
# print "<<<<",pi.grand_total * pi.conversion_rate,">>>>"
pi.paid_amount = pi.grand_total
pi.base_paid_amount = pi.outstanding_amount
pi.insert()
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
for item in zeros:
make_stock_entry(item_code=item["item_code"], qty=item['qty'],
to_warehouse=item["warehouse"],
valuation_rate=1, remarks="This is affected by data import. " + file,
posting_date=pi.posting_date,
posting_time=pi.posting_time,
set_posting_time=1, inflow_file=file)
frappe.db.commit()
print "Stock entry created."
if last_single_paid_items:
pi = make_purchase_invoice(current_order)
# pi.update_stock = 1
pi.is_paid = 1
pi.items = []
pi.posting_date = last_single_SI_dict['OrderDate'].date()
pi.posting_time = str(last_single_SI_dict['OrderDate'].time())
pi_total = 0.0
if float(last_single_SI_dict["conversion_rate"]) != 0.0 and float(last_single_SI_dict["conversion_rate"]) != 1.0:
pi.currency = last_single_SI_dict["currency"]
pi.conversion_rate = float(last_single_SI_dict["conversion_rate"])
elif float(last_single_SI_dict["conversion_rate"]) == 0.0 or float(last_single_SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in last_single_paid_items:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += float(nl.rate) * float(nl.qty)
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
# print " ", paid_items
print " Paid Items Only PI Total", pi_total, current_order, pi.currency
# print " ", pi.as_dict()["items"]
if pi_total:
pi.mode_of_payment = "Cash"
pi.insert()
frappe.db.commit()
if pi.currency != "NGN":
pi.paid_amount = pi.grand_total
pi.base_paid_amount = pi.outstanding_amount
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
pass
if last_single_fulfilled_items:
pi = make_purchase_invoice(current_order)
pi.update_stock = 1
# pi.is_paid = 1
pi.items = []
pi.posting_date = last_single_SI_dict['OrderDate'].date()
pi.posting_time = str(last_single_SI_dict['OrderDate'].time())
pi_total = 0.0
if float(last_single_SI_dict["conversion_rate"]) != 0.0 and float(
last_single_SI_dict["conversion_rate"]) != 1.0:
pi.currency = last_single_SI_dict["currency"]
pi.conversion_rate = float(last_single_SI_dict["conversion_rate"])
elif float(last_single_SI_dict["conversion_rate"]) == 0.0 or float(
last_single_SI_dict["conversion_rate"]) == 1.0:
pi.currency = "NGN"
pi.conversion_rate = None
zeros = []
for item in last_single_fulfilled_items:
nl = pi.append('items', {})
nl.description = item["description"]
nl.item_name = item["item_name"]
nl.item_code = item["item_name"]
nl.rate = float(item["rate"])
nl.conversion_factor = item["conversion_factor"]
nl.uom = item["uom"]
nl.expense_account = item["expense_account"]
nl.cost_center = item["cost_center"]
nl.qty = float(item["qty"])
nl.warehouse = item["warehouse"]
nl.purchase_order = current_order
pi_total += float(nl.rate) * float(nl.qty)
# if pi.items:
pi.set_posting_time = 1
pi.cash_bank_account = "Access Bank - DCL"
pi.taxes_and_charges = ""
pi.taxes = []
pi.inflow_file = file
# print " ", paid_items
print " Paid Items Only PI Total", pi_total, current_order, pi.currency
# print " ", pi.as_dict()["items"]
if pi_total:
pi.mode_of_payment = "Cash"
pi.insert()
frappe.db.commit()
if pi.currency != "NGN":
pi.paid_amount = pi.grand_total
pi.base_paid_amount = pi.outstanding_amount
pi.save()
frappe.db.commit()
pi.submit()
frappe.db.commit()
else:
pass
None
def test_partial_transfer_batch_based_on_material_transfer_for_purchase_invoice(
self):
'''
- Set backflush based on Material Transferred for Subcontract
- Create subcontracted PO for the item Subcontracted Item SA6.
- Transfer the partial components from Stores to Supplier warehouse with batch.
- Create partial purchase receipt against the PO and change the qty manually.
- Transfer the remaining components from Stores to Supplier warehouse with batch.
- Create purchase receipt for remaining qty against the PO and change the qty manually.
'''
set_backflush_based_on('Material Transferred for Subcontract')
item_code = 'Subcontracted Item SA6'
items = [{
'warehouse': '_Test Warehouse - _TC',
'item_code': item_code,
'qty': 10,
'rate': 100
}]
rm_items = [{'item_code': 'Subcontracted SRM Item 3', 'qty': 5}]
itemwise_details = make_stock_in_entry(rm_items=rm_items)
po = create_purchase_order(
rm_items=items,
is_subcontracted="Yes",
supplier_warehouse="_Test Warehouse 1 - _TC")
for d in rm_items:
d['po_detail'] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details))
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 5
pr1.items[0].expense_account = 'Stock Adjustment - _TC'
pr1.save()
transferred_batch_no = ''
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, 3)
transferred_batch_no = details.batch_no
self.assertEqual(value.batch_no, details.batch_no)
pr1.load_from_db()
pr1.supplied_items[0].consumed_qty = 5
pr1.supplied_items[0].batch_no = list(transferred_batch_no.keys())[0]
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, details.qty)
self.assertEqual(value.batch_no, details.batch_no)
itemwise_details = make_stock_in_entry(rm_items=rm_items)
for d in rm_items:
d['po_detail'] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details))
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].expense_account = 'Stock Adjustment - _TC'
pr1.submit()
for key, value in get_supplied_items(pr1).items():
details = itemwise_details.get(key)
self.assertEqual(value.qty, details.qty)
self.assertEqual(value.batch_no, details.batch_no)
def test_item_with_batch_based_on_bom_for_purchase_invoice(self):
'''
- Set backflush based on BOM
- Create subcontracted PO for the item Subcontracted Item SA4 (has batch no).
- Transfer the components from Stores to Supplier warehouse with batch no and serial nos.
- Transfer the components in multiple batches.
- Create the 3 purchase receipt against the PO and split Subcontracted Items into two batches.
- Keep the qty as 2 for Subcontracted Item in the purchase receipt.
'''
set_backflush_based_on('BOM')
item_code = 'Subcontracted Item SA4'
items = [{
'warehouse': '_Test Warehouse - _TC',
'item_code': item_code,
'qty': 10,
'rate': 100
}]
rm_items = [{
'item_code': 'Subcontracted SRM Item 1',
'qty': 10
}, {
'item_code': 'Subcontracted SRM Item 2',
'qty': 10
}, {
'item_code': 'Subcontracted SRM Item 3',
'qty': 3
}, {
'item_code': 'Subcontracted SRM Item 3',
'qty': 3
}, {
'item_code': 'Subcontracted SRM Item 3',
'qty': 3
}, {
'item_code': 'Subcontracted SRM Item 3',
'qty': 1
}]
itemwise_details = make_stock_in_entry(rm_items=rm_items)
po = create_purchase_order(
rm_items=items,
is_subcontracted="Yes",
supplier_warehouse="_Test Warehouse 1 - _TC")
for d in rm_items:
d['po_detail'] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details))
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = 'Stock Adjustment - _TC'
add_second_row_in_pr(pr1)
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 4)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = 'Stock Adjustment - _TC'
add_second_row_in_pr(pr1)
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 4)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = 'Stock Adjustment - _TC'
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 2)
def test_item_with_batch_based_on_bom_for_purchase_invoice(self):
"""
- Set backflush based on BOM
- Create subcontracted PO for the item Subcontracted Item SA4 (has batch no).
- Transfer the components from Stores to Supplier warehouse with batch no and serial nos.
- Transfer the components in multiple batches.
- Create the 3 purchase receipt against the PO and split Subcontracted Items into two batches.
- Keep the qty as 2 for Subcontracted Item in the purchase receipt.
"""
set_backflush_based_on("BOM")
item_code = "Subcontracted Item SA4"
items = [{
"warehouse": "_Test Warehouse - _TC",
"item_code": item_code,
"qty": 10,
"rate": 100
}]
rm_items = [
{
"item_code": "Subcontracted SRM Item 1",
"qty": 10
},
{
"item_code": "Subcontracted SRM Item 2",
"qty": 10
},
{
"item_code": "Subcontracted SRM Item 3",
"qty": 3
},
{
"item_code": "Subcontracted SRM Item 3",
"qty": 3
},
{
"item_code": "Subcontracted SRM Item 3",
"qty": 3
},
{
"item_code": "Subcontracted SRM Item 3",
"qty": 1
},
]
itemwise_details = make_stock_in_entry(rm_items=rm_items)
po = create_purchase_order(
rm_items=items,
is_subcontracted=1,
supplier_warehouse="_Test Warehouse 1 - _TC")
for d in rm_items:
d["po_detail"] = po.items[0].name
make_stock_transfer_entry(
po_no=po.name,
main_item_code=item_code,
rm_items=rm_items,
itemwise_details=copy.deepcopy(itemwise_details),
)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = "Stock Adjustment - _TC"
add_second_row_in_pr(pr1)
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 4)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = "Stock Adjustment - _TC"
add_second_row_in_pr(pr1)
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 4)
pr1 = make_purchase_invoice(po.name)
pr1.update_stock = 1
pr1.items[0].qty = 2
pr1.items[0].expense_account = "Stock Adjustment - _TC"
pr1.save()
pr1.submit()
for key, value in get_supplied_items(pr1).items():
self.assertEqual(value.qty, 2)
def test_reserved_qty_subcontract_po(self):
# Make stock available for raw materials
make_stock_entry(target="_Test Warehouse - _TC", qty=10, basic_rate=100)
make_stock_entry(target="_Test Warehouse - _TC", item_code="_Test Item Home Desktop 100",
qty=20, basic_rate=100)
make_stock_entry(target="_Test Warehouse 1 - _TC", item_code="_Test Item",
qty=30, basic_rate=100)
make_stock_entry(target="_Test Warehouse 1 - _TC", item_code="_Test Item Home Desktop 100",
qty=30, basic_rate=100)
bin1 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname=["reserved_qty_for_sub_contract", "projected_qty"], as_dict=1)
# Submit PO
po = create_purchase_order(item_code="_Test FG Item", is_subcontracted="Yes")
bin2 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname=["reserved_qty_for_sub_contract", "projected_qty"], as_dict=1)
self.assertEquals(bin2.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract + 10)
self.assertEquals(bin2.projected_qty, bin1.projected_qty - 10)
# Create stock transfer
rm_item = [{"item_code":"_Test FG Item","rm_item_code":"_Test Item","item_name":"_Test Item",
"qty":6,"warehouse":"_Test Warehouse - _TC","rate":100,"amount":600,"stock_uom":"Nos"}]
rm_item_string = json.dumps(rm_item)
se = frappe.get_doc(make_subcontract_transfer_entry(po.name, rm_item_string))
se.to_warehouse = "_Test Warehouse 1 - _TC"
se.save()
se.submit()
bin3 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin3.reserved_qty_for_sub_contract, bin2.reserved_qty_for_sub_contract - 6)
# close PO
po.update_status("Closed")
bin4 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin4.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract)
# Re-open PO
po.update_status("Submitted")
bin5 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin5.reserved_qty_for_sub_contract, bin2.reserved_qty_for_sub_contract - 6)
# make Purchase Receipt against PO
pr = make_purchase_receipt(po.name)
pr.supplier_warehouse = "_Test Warehouse 1 - _TC"
pr.save()
pr.submit()
bin6 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin6.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract)
# Cancel PR
pr.cancel()
bin7 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin7.reserved_qty_for_sub_contract, bin2.reserved_qty_for_sub_contract - 6)
# Make Purchase Invoice
pi = make_purchase_invoice(po.name)
pi.update_stock = 1
pi.supplier_warehouse = "_Test Warehouse 1 - _TC"
pi.insert()
pi.submit()
bin8 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin8.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract)
# Cancel PR
pi.cancel()
bin9 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin9.reserved_qty_for_sub_contract, bin2.reserved_qty_for_sub_contract - 6)
# Cancel Stock Entry
se.cancel()
bin10 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin10.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract + 10)
# Cancel PO
po.reload()
po.cancel()
bin11 = frappe.db.get_value("Bin",
filters={"warehouse": "_Test Warehouse - _TC", "item_code": "_Test Item"},
fieldname="reserved_qty_for_sub_contract", as_dict=1)
self.assertEquals(bin11.reserved_qty_for_sub_contract, bin1.reserved_qty_for_sub_contract)
