def execute(): frappe.reload_doctype("Sales Order Item") repost_for = frappe.db.sql(""" select distinct item_code, warehouse from ( ( select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 ) UNION ( select distinct item_code, warehouse from `tabPacked Item` where docstatus=1 and parenttype='Sales Order' ) ) so_item where exists(select name from tabItem where name=so_item.item_code and ifnull(is_stock_item, 0)=1) """) for item_code, warehouse in repost_for: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) }) frappe.db.sql("""delete from tabBin where exists( select name from tabItem where name=tabBin.item_code and ifnull(is_stock_item, 0) = 0 ) """)
def execute(): for doctype in ("Sales Order Item", "Bin"): frappe.reload_doctype(doctype) repost_for = frappe.db.sql("""select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 and uom != stock_uom and exists(select name from tabItem where name=`tabSales Order Item`.item_code and ifnull(is_stock_item, 0)=1)""") for item_code, warehouse in repost_for: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def update_reserved_qty(self, so_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] def _valid_for_reserve(item_code, warehouse): if item_code and warehouse and [item_code, warehouse] not in item_wh_list \ and frappe.db.get_value("Item", item_code, "is_stock_item"): item_wh_list.append([item_code, warehouse]) for d in self.get("items"): if (not so_item_rows or d.name in so_item_rows): _valid_for_reserve(d.item_code, d.warehouse) if self.has_product_bundle(d.item_code): for p in self.get("packed_items"): if p.parent_detail_docname == d.name and p.parent_item == d.item_code: _valid_for_reserve(p.item_code, p.warehouse) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def update_reserved_qty(self, so_item_rows=None): """update requested qty (before ordered_qty is updated)""" item_wh_list = [] def _valid_for_reserve(item_code, warehouse): if item_code and warehouse and [item_code, warehouse] not in item_wh_list \ and frappe.db.get_value("Item", item_code, "is_stock_item"): item_wh_list.append([item_code, warehouse]) for d in self.get("items"): if (not so_item_rows or d.name in so_item_rows) and not d.delivered_by_supplier: if self.has_product_bundle(d.item_code): for p in self.get("packed_items"): if p.parent_detail_docname == d.name and p.parent_item == d.item_code: _valid_for_reserve(p.item_code, p.warehouse) else: _valid_for_reserve(d.item_code, d.warehouse) for item_code, warehouse in item_wh_list: update_bin_qty(item_code, warehouse, { "reserved_qty": get_reserved_qty(item_code, warehouse) })
def gecko_orders(page=1, replace=0, order_number="", skip_orders=[]): access_token = "6daee46c0b4dbca8baac12dbb0e8b68e93934608c510bb41a770bbbd8c8a7ca5" refresh_token = "76098f0a7f66233fe97f160980eae15a9a7007a5f5b7b641f211748d58e583ea" # tg = TradeGeckoRestClient(access_token, refresh_token) tg = TradeGeckoRestClient(access_token) # print tg.company.all()['companies'][0] if order_number == "": page_limit = 25 else: page_limit = 2 start_page = page while start_page < page_limit: print "########################### PAGE ", start_page, " ###########################" if not order_number: orders = tg.order.all(page=start_page, limit=250)['orders'] else: orders = tg.order.filter(order_number=order_number)['orders'] start_page += 1 # print orders income_accounts = "5111 - Cost of Goods Sold - DCL" # income_accounts = "Sales - J" cost_centers = "Main - DCL" # cost_centers = "Main - J" for i, o in enumerate(orders): so_inv = None # if i+1 == 50: # print "waiting..." # time.sleep(20) # if o['invoices'] == []: # print "No Invoice" # continue # if o["status"] == "draft" or o['status'] == 'fulfilled': #draft,received,finalized,fulfilled # continue # if o['payment_status'] == 'unpaid': # continue skip = 0 # if replace == 0: print "########################### PAGE ", start_page - 1, " ###########################" print o total_discount_amt = 0.0 total_tax_amt = 0.0 exists_po = frappe.db.sql( """SELECT Count(*) FROM `tabSales Order` WHERE name=%s""", (o['order_number'])) if exists_po[0][0] > 0: skip = 1 if replace == 1: skip = 0 if o['invoices'] != []: for _inv in o['invoices']: print "checking ", _inv['invoice_number'] exists_inv = frappe.db.sql( """SELECT name FROM `tabSales Invoice` WHERE name=%s""", (_inv['invoice_number'])) if exists_inv == (): # check if the inv no. is correct skip = 0 print "no inv or wrong name inv", _inv[ 'invoice_number'] else: # check if discounts where applied exists_inv = frappe.get_doc( "Sales Invoice", exists_inv[0][0]) total_discount_amt = 0.0 total_tax_amt = 0.0 xero_inv = test_xero(_inv['invoice_number']) so_inv = xero_inv # time.sleep(5) for x in xero_inv[0]['LineItems']: total_discount_amt += x['DiscountAmount'] total_tax_amt += x['TaxAmount'] if exists_inv.discount_amount < total_discount_amt: skip = 0 print "no discount" if total_tax_amt > 0.0: skip = 0 print "no tax" elif exists_po[0][0] == 0: skip = 0 #dont skip print "SO not found" # # if o['order_number'] in skip_orders: # continue if skip == 1: continue else: print "re-creating SO..." remove_imported_data(o["order_number"]) sales_person_name = "" if o['assignee_id']: time.sleep(1) _user = tg.user.get(o['assignee_id']) if _user: user = _user['user'] # print user emp = frappe.db.sql( """SELECT name FROM `tabEmployee` WHERE first_name=%s and last_name=%s""", (user['first_name'], user['last_name'])) emp_name = "" if emp != (): emp_name = emp[0][0] else: #create emp emp_doc = frappe.get_doc({ "doctype": "Employee", "first_name": user['first_name'], "last_name": user['last_name'], "gender": "Other", "employee_number": user['first_name'] + user['last_name'], "date_of_birth": frappe.utils.get_datetime().date(), "date_of_joining": (frappe.utils.get_datetime() + timedelta(days=1)).date() }) emp_doc.insert(ignore_permissions=True) emp_name = emp_doc.name sales_person = frappe.db.sql( """SELECT name FROM `tabSales Person` WHERE name=%s""", (user['first_name'] + ' ' + user['last_name'])) sales_person_name = "" if sales_person != (): sales_person_name = sales_person[0][0] else: sales_person_doc = frappe.get_doc({ "doctype": "Sales Person", "sales_person_name": user['first_name'] + ' ' + user['last_name'], "employee": emp_name, "parent_sales_person": "Sales Team" }) sales_person_doc.insert(ignore_permissions=True) sales_person_name = sales_person_doc.name time.sleep(1) currency = tg.currency.get(o['currency_id'])['currency'] created_at = parser.parse(o["created_at"]) # received_at = parser.parse(o["received_at"]) # due_at = parser.parse(o["due_at"]) from dcl.tradegecko.fixerio import Fixerio fxrio = Fixerio(access_key='88581fe5b1c9f21dbb6f90ba6722d11c', base=currency['iso']) currency_rate = fxrio.historical_rates(created_at.date())['rates'] # print currency_rate['EUR'],currency['iso'] # currency_rate = currency_rate[currency['iso']] currency_rate = currency_rate['GHS'] # print o["order_number"] # # break SI_items = [] time.sleep(1) to_warehouse = tg.location.get(o['stock_location_id'])['location'] exists_warehouse = frappe.db.sql( """SELECT Count(*) FROM `tabWarehouse` WHERE warehouse_name=%s""", (to_warehouse['label'])) if exists_warehouse[0][0] == 0: frappe.get_doc({ "doctype": "Warehouse", "warehouse_name": to_warehouse['label'] }).insert(ignore_permissions=True) frappe.db.commit() current_order = o["order_number"] tax_amount = 0.0 for i in o['order_line_item_ids']: time.sleep(1) line_item = tg.order_line_item.get(i)['order_line_item'] print line_item if line_item["tax_rate"]: tax_amount += (round(float(line_item["price"])) * float(line_item["quantity"])) \ * (float(line_item["tax_rate"]) / 100.00) print( round(float(line_item["price"])) * float(line_item["quantity"])) print(float(line_item["tax_rate"]) / 100.00) print "Tax Amount", tax_amount # print line_item exists_cat = frappe.db.sql( """SELECT Count(*),item_code,item_name,description FROM `tabItem` WHERE variant_id=%s""", (line_item['variant_id'])) item_code = "" item_name = "" item_description = "" if exists_cat[0][0] == 0: time.sleep(1) variant = tg.variant.get(line_item['variant_id']) # print variant,line_item['variant_id'] # print line_item if not variant: variant = { 'product_name': line_item['label'], 'sku': line_item['label'], 'description': line_item['label'] } else: variant = variant["variant"] # print variant import re clean_name = re.sub(r"[^a-zA-Z0-9]+", ' ', variant["product_name"]) if variant["sku"]: item_code = re.sub(r"[^a-zA-Z0-9]+", ' ', variant["sku"]) or clean_name else: item_code = clean_name item_name = clean_name if "X960 Pipettor tip Thermo Scientific Finntip Flex Filter sterile, free from DNA, " \ "DNase and RNasein vacuum sealed sterilized tip racks polypropylene tip," in item_code: item_code = "X960 Pipettor tip Thermo Scientific Finntip Flex Filter" if "X960 Pipettor tip Thermo Scientific Finntip Flex Filter sterile, free from DNA, " \ "DNase and RNasein vacuum sealed sterilized tip racks polypropylene tip," in item_name: item_name = "X960 Pipettor tip Thermo Scientific Finntip Flex Filter" if "X960 Pipettor tip Thermo Scientific Finntip Flex Filter sterile free from DNA DNase " \ "and RNasein vacuum sealed sterilized tip racks polypropylene tip polyethylene matrix " in item_name: item_name = "X960 Pipettor tip Thermo Scientific Finntip Flex Filter" if "Stericup-GV, 0.22 " in item_code: item_code = "Stericup-GV" if "Stericup-GV, 0.22 " in item_name: item_name = "Stericup-GV" item_description = variant["description"] find_item = frappe.db.sql( """SELECT Count(*),item_code,item_name,description FROM `tabItem` WHERE item_code=%s""", (item_code)) if find_item[0][0] == 0: item_dict = { "doctype": "Item", "item_code": item_code, "item_name": item_name, "description": variant["description"] or variant["product_name"], "item_group": "All Item Groups", "variant_id": line_item['variant_id'] } # print item_dict create_item = frappe.get_doc(item_dict) create_item.insert(ignore_permissions=True) frappe.db.commit() else: item_code = find_item[0][1] item_name = find_item[0][2] item_description = find_item[0][3] else: item_code = exists_cat[0][1] item_name = exists_cat[0][2] item_description = exists_cat[0][3] # print variant SI_item = { "description": item_description, "item_name": item_name, "item_code": item_code, "rate": round(float(line_item["price"])), "price_list_rate": round(float(line_item["price"])), "conversion_factor": 1, "uom": "Nos", "expense_account": income_accounts, "cost_center": cost_centers, "qty": float(line_item["quantity"]), "warehouse": to_warehouse['label'] + " - DCL", "order_line_item_id": line_item["id"], "variant_id": line_item['variant_id'] # "OrderDate": row["OrderDate"] } SI_items.append(SI_item) # print SI_items print SI_items if SI_items: time.sleep(1) supplier_company = tg.company.get(o['company_id'])['company'] # print supplier_company # CREATE SUPPLIER IF NOT EXISTS exists_supplier = frappe.db.sql( """SELECT Count(*) FROM `tabCustomer` WHERE name=%s""", (supplier_company['name'])) if exists_supplier[0][0] == 0: frappe.get_doc({ "doctype": "Customer", "customer_name": supplier_company['name'], "customer_group": "All Customer Groups", "customer_type": "Company", "account_manager": "Dummy" }).insert() frappe.db.commit() sales_team = [] if sales_person_name: sales_team = [{ "sales_person": sales_person_name, "allocated_percentage": 100.00 }] taxes = [] if tax_amount or total_tax_amt: tax_amount = tax_amount or total_tax_amt taxes.append({ "charge_type": "Actual", "tax_amount": tax_amount, "account_head": "VAT - DCL", "description": "tax" }) SI_dict = { "doctype": "Sales Order", "title": supplier_company['name'], "customer": supplier_company['name'], "posting_date": created_at.date(), "schedule_date": created_at.date(), # TODO + 30 days "transaction_date": created_at.date(), "due_date": created_at.date(), "delivery_date": created_at.date(), "items": SI_items, "taxes": taxes, "docstatus": status_map[o["status"]], "inflow_file": current_order, "currency": currency['iso'], "conversion_rate": currency_rate, "sales_team": sales_team, "disable_rounded_total": 1 } if o['status'] != "draft" and o['status'] != "active": if o['invoices']: for item in SI_items: # check stocks first # get_balance_qty_from_sle # /home/jvfiel/frappe-v11/apps/erpnext/erpnext/stock/stock_balance.py from erpnext.stock.stock_balance import get_balance_qty_from_sle, get_reserved_qty get_bal = get_balance_qty_from_sle( item["item_code"], to_warehouse['label'] + " - DCL") reserved_qty = get_reserved_qty( item["item_code"], to_warehouse['label'] + " - DCL") # print item["item_code"], to_warehouse['label'] + " - DCL" # print "rsvd qty ", reserved_qty # print "bal", (float(get_bal)) # print "need", item['qty'] net_bal = (float(get_bal) - float(reserved_qty) - item['qty']) # print "net bal", net_bal reqd_qty = 0 if net_bal < 0: # reqd_qty = float(item['qty']) - abs(float(item['qty'])) # print "itm qty", float(item['qty']) reqd_qty = abs(net_bal) # reqd_qty = abs(net_bal)+float(item['qty']) # print "req qty", reqd_qty make_stock_entry( item_code=item["item_code"], qty=reqd_qty, to_warehouse=to_warehouse['label'] + " - DCL", valuation_rate=1, remarks="This is affected by data import. ", posting_date=created_at.date(), posting_time=str(created_at.time()), set_posting_time=1, inflow_file=current_order) frappe.db.commit() # print "qty after stock ent", get_balance_qty_from_sle(item["item_code"], # to_warehouse['label'] + " - DCL") elif net_bal == 0: reqd_qty = float(item['qty']) SI = frappe.get_doc(SI_dict) SI_created = SI.insert(ignore_permissions=True) frappe.db.commit() rename_doc("Sales Order", SI_created.name, o['order_number'], force=True) frappe.db.commit() print "done submitting and renaming." if o['status'] != "draft" and o['status'] != "active": if o['invoices']: i = o['invoices'][0] if so_inv == None: inv = test_xero(i['invoice_number']) else: inv = so_inv print SI_items pi = make_invoice(o["order_number"], created_at, inv) # print inv frappe.db.commit() rename_doc("Sales Invoice", pi.name, i['invoice_number'], force=True) frappe.db.commit() if inv[0]['AmountPaid']: # print "paid", inv[0]['AmountPaid'] payment_request = make_payment_request( dt="Sales Invoice", dn=i['invoice_number'], recipient_id="", submit_doc=True, mute_email=True, use_dummy_message=True, grand_total=float(inv[0]['AmountPaid']), posting_date=created_at.date(), posting_time=str(created_at.time()), inflow_file=current_order) # if SI_dict["PaymentStatus"] != "Invoiced": payment_entry = frappe.get_doc( make_payment_entry(payment_request.name)) payment_entry.posting_date = created_at.date() payment_entry.posting_time = str(created_at.time()) payment_entry.set_posting_time = 1 # print " ",pi.rounded_total,payment_entry.paid_amount # if SI_dict["PaymentStatus"] == "Paid": payment_entry.paid_amount = inv[0]['AmountPaid'] # else: # payment_entry.paid_amount = float(SI_dict["AmountPaid"]) payment_entry.inflow_file = current_order payment_entry.submit() # frappe.db.commit() else: print "unpaid" for i in o['fulfillment_ids']: time.sleep(1) fills = tg.fulfillment_line_item.filter( fulfillment_id=i) # print fills # print SI_items fill_items = fills['fulfillment_line_items'] for j in fill_items: # print j for item in SI_items: if j['variant_id'] == item['variant_id']: j.update(item) # print fill_items # print " making delivery. " # print " making delivery. " # print " making delivery. " # print " making delivery. " # print " making delivery. " # print fill_items if fill_items: make_delivery(fill_items, current_order, created_at) frappe.db.commit() # break # time.sleep(5) # time.sleep(20) print "DONE DONE DONE DONE DONE"
def check_stock(): from erpnext.stock.stock_balance import get_balance_qty_from_sle, get_reserved_qty get_bal = get_balance_qty_from_sle('1100-1090', 'Primary Location - DCL') reserved_qty = get_reserved_qty('1100-1090', 'Primary Location - DCL')