def work(domain="Manufacturing"):
frappe.set_user(frappe.db.get_global("demo_sales_user_2"))
for i in range(random.randint(1, 7)):
if random.random() < 0.5:
make_opportunity(domain)
for i in range(random.randint(1, 3)):
if random.random() < 0.5:
make_quotation(domain)
try:
lost_reason = frappe.get_doc({
"doctype": "Opportunity Lost Reason",
"lost_reason": "Did not ask"
})
lost_reason.save(ignore_permissions=True)
except frappe.exceptions.DuplicateEntryError:
pass
# lost quotations / inquiries
if random.random() < 0.3:
for i in range(random.randint(1, 3)):
quotation = get_random("Quotation", doc=True)
if quotation and quotation.status == "Submitted":
quotation.declare_order_lost([{"lost_reason": "Did not ask"}])
for i in range(random.randint(1, 3)):
opportunity = get_random("Opportunity", doc=True)
if opportunity and opportunity.status in ("Open", "Replied"):
opportunity.declare_enquiry_lost([{
"lost_reason": "Did not ask"
}])
for i in range(random.randint(1, 3)):
if random.random() < 0.6:
make_sales_order()
if random.random() < 0.5:
# make payment request against Sales Order
sales_order_name = get_random("Sales Order", filters={"docstatus": 1})
try:
if sales_order_name:
so = frappe.get_doc("Sales Order", sales_order_name)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(
dt="Sales Order",
dn=so.name,
recipient_id=so.contact_email,
submit_doc=True,
mute_email=True,
use_dummy_message=True,
)
payment_entry = frappe.get_doc(
make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
except Exception:
pass
def test_xero(id="INV4093"):
# from xero import Xero
# from xero.auth import PublicCredentials
consumer_key = "06RRGPYM4SJXFEMRODT6F0GYJ42UKA"
# consumer_secret = "COMNDKTM2AU54WADYU1I1YVBBRE4ZL"
# credentials = PublicCredentials(consumer_key, consumer_secret)
# print credentials.url
from xero import Xero
from xero.auth import PrivateCredentials
import os
file = "privatekey.pem"
with open(os.path.dirname(os.path.abspath(__file__)) + '/data/' + file) as keyfile:
rsa_key = keyfile.read()
# print rsa_key
credentials = PrivateCredentials(consumer_key, rsa_key)
xero = Xero(credentials)
invoices = xero.invoices.filter(raw='AmountDue > 0')
# invoices = xero.invoices.filter(InvoiceNumber="INV4093")
for inv in invoices:
print inv
inv = xero.invoices.get(inv['InvoiceNumber'])[0]
inv_name = frappe.db.sql("""SELECT name FROM `tabSales Invoice`
WHERE docstatus=1 AND outstanding_amount = 0.0
and name=%s""",(inv['InvoiceNumber']))
exists_so = frappe.db.sql("""SELECT Count(*) FROM `tabSales Order` WHERE name=%s""",(inv['InvoiceNumber']))
print ">>>>>>>>>>>", inv_name
if inv_name != () and exists_so[0][0] != 0:
# created_at = parser.parse(inv["Date"])
created_at = inv["Date"]
# remove_imported_data(inv['InvoiceNumber'])
remove_imported_data(inv['Reference'])
pi = make_invoice(inv['Reference'], created_at, inv)
# print inv
frappe.db.commit()
rename_doc("Sales Invoice", pi.name, inv['InvoiceNumber'], force=True)
frappe.db.commit()
if inv['AmountPaid']:
payment_request = make_payment_request(dt="Sales Invoice", dn=inv['InvoiceNumber'], recipient_id="",
submit_doc=True, mute_email=True, use_dummy_message=True,
grand_total=float(inv['AmountPaid']),
posting_date=created_at.date(),
posting_time=str(created_at.time()),
inflow_file=inv['Reference'])
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
payment_entry.paid_amount = inv['AmountPaid']
payment_entry.inflow_file = inv['Reference']
payment_entry.submit()
def work():
frappe.set_user(frappe.db.get_global('demo_accounts_user'))
if random.random() <= 0.6:
report = "Ordered Items to be Billed"
for so in list(set([r[0] for r in query_report.run(report)["result"]
if r[0] != "Total"]))[:random.randint(1, 5)]:
try:
si = frappe.get_doc(make_sales_invoice(so))
si.posting_date = frappe.flags.current_date
for d in si.get("items"):
if not d.income_account:
d.income_account = "Sales - {}".format(
frappe.get_cached_value('Company', si.company, 'abbr'))
si.insert()
si.submit()
frappe.db.commit()
except frappe.ValidationError:
pass
if random.random() <= 0.6:
report = "Received Items to be Billed"
for pr in list(set([r[0] for r in query_report.run(report)["result"]
if r[0] != "Total"]))[:random.randint(1, 5)]:
try:
pi = frappe.get_doc(make_purchase_invoice(pr))
pi.posting_date = frappe.flags.current_date
pi.bill_no = random_string(6)
pi.insert()
pi.submit()
frappe.db.commit()
except frappe.ValidationError:
pass
if random.random() < 0.5:
make_payment_entries("Sales Invoice", "Accounts Receivable")
if random.random() < 0.5:
make_payment_entries("Purchase Invoice", "Accounts Payable")
if random.random() < 0.4:
# make payment request against sales invoice
sales_invoice_name = get_random(
"Sales Invoice", filters={"docstatus": 1})
if sales_invoice_name:
si = frappe.get_doc("Sales Invoice", sales_invoice_name)
if si.outstanding_amount > 0:
payment_request = make_payment_request(dt="Sales Invoice", dn=si.name, recipient_id=si.contact_email,
submit_doc=True, mute_email=True, use_dummy_message=True)
payment_entry = frappe.get_doc(
make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
make_pos_invoice()
def work():
frappe.set_user(frappe.db.get_global('demo_accounts_user'))
if random.random() <= 0.6:
report = "Ordered Items to be Billed"
for so in list(set([r[0] for r in query_report.run(report)["result"]
if r[0]!="Total"]))[:random.randint(1, 5)]:
try:
si = frappe.get_doc(make_sales_invoice(so))
si.posting_date = frappe.flags.current_date
for d in si.get("items"):
if not d.income_account:
d.income_account = "Sales - {}".format(frappe.db.get_value('Company', si.company, 'abbr'))
si.insert()
si.submit()
frappe.db.commit()
except frappe.ValidationError:
pass
if random.random() <= 0.6:
report = "Received Items to be Billed"
for pr in list(set([r[0] for r in query_report.run(report)["result"]
if r[0]!="Total"]))[:random.randint(1, 5)]:
try:
pi = frappe.get_doc(make_purchase_invoice(pr))
pi.posting_date = frappe.flags.current_date
pi.bill_no = random_string(6)
pi.insert()
pi.submit()
frappe.db.commit()
except frappe.ValidationError:
pass
if random.random() < 0.5:
make_payment_entries("Sales Invoice", "Accounts Receivable")
if random.random() < 0.5:
make_payment_entries("Purchase Invoice", "Accounts Payable")
if random.random() < 0.1:
#make payment request against sales invoice
sales_invoice_name = get_random("Sales Invoice", filters={"docstatus": 1})
if sales_invoice_name:
si = frappe.get_doc("Sales Invoice", sales_invoice_name)
if si.outstanding_amount > 0:
payment_request = make_payment_request(dt="Sales Invoice", dn=si.name, recipient_id=si.contact_email,
submit_doc=True, mute_email=True, use_dummy_message=True)
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
make_pos_invoice()
def make_sales_order(sale_factor):
qty_factor = random.randint(1,(random.randint(1, sale_factor)))
no_of_items_sold= random.randint(1, 10)
customer = random.choice(CUSTOMERS)
sales_order_items = []
for item in xrange(no_of_items_sold):
sales_order_items.append({
"item_code": random.choice(ITEMS),
"qty": random.randint(1, qty_factor),
"warehouse": WAREHOUSE
})
sales_order = frappe.get_doc({
"doctype": "Sales Order",
"creation": frappe.flags.current_date,
"customer": customer,
"transaction_date": frappe.flags.current_date,
"order_type": "Shopping Cart",
"items": sales_order_items
})
taxes = get_default_taxes_and_charges("Sales Taxes and Charges Template", company=COMPANY)
if taxes.get('taxes'):
sales_order.update(taxes)
sales_order.save()
sales_order.submit()
so = sales_order
for item in so.items:
if item.projected_qty<10:
reorder_stock(item.item_code)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(dt="Sales Order", dn=so.name, recipient_id=so.contact_email,
submit_doc=True, mute_email=True, use_dummy_message=True)
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
si = frappe.get_doc(make_sales_invoice(so.name))
si.set_posting_time=True
si.posting_date = frappe.flags.current_date
for d in si.get("items"):
if not d.income_account:
d.income_account = "Sales - {}".format(frappe.db.get_value('Company', si.company, 'abbr'))
si.set_missing_values()
make_payment_entries_for_pos_invoice(si)
si.insert()
si.submit()
dn = make_delivery_note(so.name)
dn.set_posting_time=True
dn.posting_date = frappe.flags.current_date
dn.insert()
dn.submit()
def make_invoice(paid_items,sales_order_name,SI_dict):
datepaid = SI_dict['DatePaid']
if not datepaid:
datepaid = SI_dict["OrderDate"]
else:
datepaid = parser.parse(datepaid)
# print SI_dict["inflow_file"]
exists_si = frappe.db.sql("""SELECT Count(*) FROM `tabSales Invoice` INNER JOIN
`tabSales Invoice Item`
ON `tabSales Invoice`.name=`tabSales Invoice Item`.parent
WHERE `tabSales Invoice`.docstatus =1
AND `tabSales Invoice Item`.sales_order=%s""",(sales_order_name))
print "exists si", exists_si,sales_order_name
if exists_si[0][0] > 0:
return None
pi = make_purchase_invoice(sales_order_name)
print sales_order_name
pi.inflow_file = SI_dict["inflow_file"]
pi.posting_date = datepaid.date()
pi.posting_time = str(datepaid.time())
pi.save()
pi.submit()
frappe.db.commit()
# if status == "Paid":
# if sales_order_name:
if pi.grand_total > 0.0:
so = frappe.get_doc("Sales 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="Sales Invoice", dn=pi.name, recipient_id="",
submit_doc=True, mute_email=True, use_dummy_message=True,
inflow_file=SI_dict["inflow_file"],grand_total=pi.rounded_total,
posting_date=datepaid.date(), posting_time=str(datepaid.time()))
if SI_dict["PaymentStatus"] != "Invoiced":
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = datepaid.date()
payment_entry.posting_time = str(datepaid.time())
payment_entry.set_posting_time = 1
# print " ",pi.rounded_total,payment_entry.paid_amount
if SI_dict["PaymentStatus"] == "Paid":
payment_entry.paid_amount = pi.rounded_total
else:
payment_entry.paid_amount = float(SI_dict["AmountPaid"])
payment_entry.inflow_file = SI_dict["inflow_file"]
payment_entry.submit()
frappe.db.commit()
def work(domain="Manufacturing"):
frappe.set_user(frappe.db.get_global('demo_sales_user_2'))
for i in range(random.randint(1, 7)):
if random.random() < 0.5:
make_opportunity(domain)
for i in range(random.randint(1, 3)):
if random.random() < 0.5:
make_quotation(domain)
# lost quotations / inquiries
if random.random() < 0.3:
for i in range(random.randint(1, 3)):
quotation = get_random('Quotation', doc=True)
if quotation and quotation.status == 'Submitted':
quotation.declare_order_lost('Did not ask')
for i in range(random.randint(1, 3)):
opportunity = get_random('Opportunity', doc=True)
if opportunity and opportunity.status in ('Open', 'Replied'):
opportunity.declare_enquiry_lost('Did not ask')
for i in range(random.randint(1, 3)):
if random.random() < 0.6:
make_sales_order()
if random.random() < 0.5:
#make payment request against Sales Order
sales_order_name = get_random("Sales Order", filters={"docstatus": 1})
try:
if sales_order_name:
so = frappe.get_doc("Sales Order", sales_order_name)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(
dt="Sales Order",
dn=so.name,
recipient_id=so.contact_email,
submit_doc=True,
mute_email=True,
use_dummy_message=True)
payment_entry = frappe.get_doc(
make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
except Exception:
pass
def make_invoice(paid_items, sales_order_name, SI_dict):
datepaid = SI_dict['DatePaid']
if not datepaid:
datepaid = SI_dict["OrderDate"]
else:
datepaid = parser.parse(datepaid)
# print SI_dict["inflow_file"]
pi = make_purchase_invoice(sales_order_name)
pi.inflow_file = SI_dict["inflow_file"]
pi.posting_date = datepaid.date()
pi.posting_time = str(datepaid.time())
pi.save()
pi.submit()
frappe.db.commit()
# if status == "Paid":
# if sales_order_name:
if pi.grand_total > 0.0:
so = frappe.get_doc("Sales 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="Sales Invoice",
dn=pi.name,
recipient_id="",
submit_doc=True,
mute_email=True,
use_dummy_message=True,
inflow_file=SI_dict["inflow_file"],
grand_total=pi.rounded_total,
posting_date=datepaid.date(),
posting_time=str(datepaid.time()))
if SI_dict["PaymentStatus"] != "Invoiced":
payment_entry = frappe.get_doc(
make_payment_entry(payment_request.name))
payment_entry.posting_date = datepaid.date()
payment_entry.posting_time = str(datepaid.time())
payment_entry.set_posting_time = 1
# print " ",pi.rounded_total,payment_entry.paid_amount
if SI_dict["PaymentStatus"] == "Paid":
payment_entry.paid_amount = pi.rounded_total
else:
payment_entry.paid_amount = float(SI_dict["AmountPaid"])
payment_entry.inflow_file = SI_dict["inflow_file"]
payment_entry.submit()
frappe.db.commit()
def work():
frappe.set_user(frappe.db.get_global("demo_sales_user_2"))
if random.random() < 0.5:
for i in xrange(random.randint(1, 7)):
make_opportunity()
if random.random() < 0.5:
for i in xrange(random.randint(1, 3)):
make_quotation()
# lost quotations / inquiries
if random.random() < 0.3:
for i in xrange(random.randint(1, 3)):
quotation = get_random("Quotation", doc=True)
if quotation and quotation.status == "Submitted":
quotation.declare_order_lost("Did not ask")
for i in xrange(random.randint(1, 3)):
opportunity = get_random("Opportunity", doc=True)
if opportunity and opportunity.status in ("Open", "Replied"):
opportunity.declare_enquiry_lost("Did not ask")
if random.random() < 0.3:
for i in xrange(random.randint(1, 3)):
make_sales_order()
if random.random() < 0.1:
# make payment request against Sales Order
sales_order_name = get_random("Sales Order", filters={"docstatus": 1})
if sales_order_name:
so = frappe.get_doc("Sales Order", sales_order_name)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(
dt="Sales Order",
dn=so.name,
recipient_id=so.contact_email,
submit_doc=True,
mute_email=True,
use_dummy_message=True,
)
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
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 work(domain="Manufacturing"):
frappe.set_user(frappe.db.get_global('demo_sales_user_2'))
for i in range(random.randint(1,7)):
if random.random() < 0.5:
make_opportunity(domain)
for i in range(random.randint(1,3)):
if random.random() < 0.5:
make_quotation(domain)
# lost quotations / inquiries
if random.random() < 0.3:
for i in range(random.randint(1,3)):
quotation = get_random('Quotation', doc=True)
if quotation and quotation.status == 'Submitted':
quotation.declare_order_lost('Did not ask')
for i in range(random.randint(1,3)):
opportunity = get_random('Opportunity', doc=True)
if opportunity and opportunity.status in ('Open', 'Replied'):
opportunity.declare_enquiry_lost('Did not ask')
for i in range(random.randint(1,3)):
if random.random() < 0.6:
make_sales_order()
if random.random() < 0.5:
#make payment request against Sales Order
sales_order_name = get_random("Sales Order", filters={"docstatus": 1})
try:
if sales_order_name:
so = frappe.get_doc("Sales Order", sales_order_name)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(dt="Sales Order", dn=so.name, recipient_id=so.contact_email,
submit_doc=True, mute_email=True, use_dummy_message=True)
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
except Exception:
pass
def make_invoice(paid_items,sales_order_name,SI_dict):
datepaid = SI_dict['DatePaid']
if not datepaid:
datepaid = SI_dict["OrderDate"]
else:
datepaid = parser.parse(datepaid)
# print SI_dict["inflow_file"]
exists_si = frappe.db.sql("""SELECT DISTINCT `tabSales Invoice`.name FROM `tabSales Invoice` INNER JOIN
`tabSales Invoice Item`
ON `tabSales Invoice`.name=`tabSales Invoice Item`.parent
WHERE `tabSales Invoice`.docstatus =1
AND `tabSales Invoice Item`.sales_order=%s""",(sales_order_name))
print "exists si", exists_si,sales_order_name
if exists_si != ():
#check if partial
if SI_dict["PaymentStatus"] == "Partial" or SI_dict["Freight"]:
print "Cancel Sales Invoice", exists_si
#get payment request
exists_pr = frappe.db.sql("""SELECT name FROM `tabPayment Request`
WHERE reference_name =%s""",(exists_si[0][0]))
if exists_pr != ():
pr = frappe.get_doc("Payment Request",exists_pr[0][0])
exists_pr = frappe.db.sql("""SELECT name FROM `tabPayment Entry`
WHERE reference_no =%s""",(pr.name))
if exists_pr != ():
pe = frappe.get_doc("Payment Entry",exists_pr[0][0])
if pe.docstatus == 1:
pe.cancel()
pe.delete()
frappe.db.commit()
pr.cancel()
pr.delete()
frappe.db.commit()
pi = frappe.get_doc("Sales Invoice", exists_si[0][0])
pi.cancel()
pi.delete()
frappe.db.commit()
else:
return None
pi = make_purchase_invoice(sales_order_name)
print sales_order_name
pi.inflow_file = SI_dict["inflow_file"]
pi.posting_date = datepaid.date()
pi.posting_time = str(datepaid.time())
if SI_dict["Freight"].strip():
# pi.taxes_and_charges = "Freight Charges - DCL"
freight = pi.append('taxes', {})
freight.charge_type = "Actual"
freight.account_head = "5205 - Freight and Forwarding Charges - DCL"
# freight.item_name = "Freight"
freight.description = "5205"
freight.tax_amount = float(SI_dict["Freight"])
# freight.qty = 1
pi.save()
pi.submit()
frappe.db.commit()
# if status == "Paid":
# if sales_order_name:
if pi.grand_total > 0.0:
so = frappe.get_doc("Sales 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="Sales Invoice", dn=pi.name, recipient_id="",
submit_doc=True, mute_email=True, use_dummy_message=True,
inflow_file=SI_dict["inflow_file"],
grand_total=float(SI_dict["AmountPaid"]),
posting_date=datepaid.date(), posting_time=str(datepaid.time()))
if SI_dict["PaymentStatus"] != "Invoiced":
payment_entry = frappe.get_doc(make_payment_entry(payment_request.name))
payment_entry.posting_date = datepaid.date()
payment_entry.posting_time = str(datepaid.time())
payment_entry.set_posting_time = 1
# print " ",pi.rounded_total,payment_entry.paid_amount
if SI_dict["PaymentStatus"] == "Paid":
payment_entry.paid_amount = pi.rounded_total
else:
print "Partial: ",float(SI_dict["AmountPaid"])
payment_entry.total_allocated_amount = float(SI_dict["AmountPaid"])
# payment_entry.paid_amount = float(SI_dict["AmountPaid"])
payment_entry.references[0].allocated_amount = float(SI_dict["AmountPaid"])
payment_entry.save()
frappe.db.commit()
print payment_entry.paid_amount,payment_entry.total_allocated_amount,payment_entry.references[0].allocated_amount
print payment_entry.difference_amount
# frappe.db.commit()
payment_entry.inflow_file = SI_dict["inflow_file"]
payment_entry.submit()
frappe.db.commit()
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 run():
frappe.set_user("*****@*****.**")
frappe.set_user_lang("fr")
for i in range(random.randint(1, 7)):
if random.random() < 0.5:
make_opportunity()
for i in range(random.randint(1, 7)):
if random.random() < 0.5:
make_quotation()
try:
lost_reason = frappe.get_doc({
"doctype": "Opportunity Lost Reason",
"lost_reason": _("Did not ask")
})
lost_reason.save(ignore_permissions=True)
except frappe.exceptions.DuplicateEntryError:
pass
# lost quotations / inquiries
if random.random() < 0.3:
for i in range(random.randint(1, 3)):
quotation = get_random('Quotation', doc=True)
if quotation and quotation.status == 'Submitted':
quotation.declare_order_lost([{
'lost_reason': _('Did not ask')
}])
for i in range(random.randint(1, 3)):
opportunity = get_random('Opportunity', doc=True)
if opportunity and opportunity.status in ('Open', 'Replied'):
opportunity.declare_enquiry_lost([{
'lost_reason':
_('Did not ask')
}])
for i in range(random.randint(1, 7)):
if random.random() < 0.6:
make_sales_order()
if random.random() < 0.5:
#make payment request against Sales Order
sales_order_name = get_random("Sales Order", filters={"docstatus": 1})
try:
if sales_order_name:
so = frappe.get_doc("Sales Order", sales_order_name)
if flt(so.per_billed) != 100:
payment_request = make_payment_request(
dt="Sales Order",
dn=so.name,
recipient_id=so.contact_email,
submit_doc=True,
mute_email=True,
use_dummy_message=True)
payment_entry = frappe.get_doc(
make_payment_entry(payment_request.name))
payment_entry.posting_date = frappe.flags.current_date
payment_entry.submit()
except Exception:
pass
