def journal_entry(self,cacc_name): accounts = [{'account': self.baccount.e_account, 'cost_center': self.company.cost_center, 'debit': self.credit, 'debit_in_account_currency': self.credit, 'credit': self.debit, 'credit_in_account_currency': self.debit }, {'account': cacc_name, 'cost_center': self.company.cost_center, 'debit': self.debit, 'debit_in_account_currency': self.debit, 'credit': self.credit, 'credit_in_account_currency': self.credit}] entry = {'doctype' : 'Journal Entry', 'title': self.description[0:140], 'voucher_type': 'Journal Entry', 'company': self.company_name, 'posting_date': self.date, 'user_remark': self.description, 'accounts':accounts} #print(entry) j = gui_api_wrapper(Api.api.insert,entry) #print(j) print("Buchungssatz {} erstellt".format(j['name'])) if j: self.doc['status'] = 'Reconciled' self.doc['payment_entries'] = \ [{'payment_document': 'Journal Entry', 'payment_entry': j['name'], 'allocated_amount': abs(self.amount)}] gui_api_wrapper(Api.api.update,self.doc)
def process_file(cls, infile): b = BankStatement.read_statement(infile) if not b: return None b.transactions = [] for be in b.entries: bt = be.bank_transaction() bt1 = bt.copy() del bt1['doctype'] del bt1['unallocated_amount'] bt1['status'] = ['!=', 'Cancelled'] #todo: relax the filter wrt the date (which sometimes is adapted by the bank) bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters=bt1) if not bts: gui_api_wrapper(Api.api.insert, bt) b.transactions.append(bt) doc = b.baccount.doc doc['last_integration_date'] = datetime.today().strftime('%Y-%m-%d') b.baccount.doc = gui_api_wrapper(Api.api.update_with_doctype, doc, "Bank Account") if b.ebal: b.baccount.statement_balance = b.ebal b.baccount.get_balance() return b
def read_pdf(self,infile,update_stock): if not self.parse_invoice(infile,update_stock): return None if self.check_if_present(): return None if update_stock: yesterd = utils.yesterday(self.date) self.e_items = list(map(lambda item: \ item.process_item(self.supplier,yesterd), self.items)) if None in self.e_items: easygui.msgbox("Nicht alle Artikel wurden eingetragen.\n Deshalb kann keine Einkaufsrechnung in ERPNext erstellt werden.") return None if not ask_if_to_continue(self.check_total(),"Fortsetzen?"): return None if not ask_if_to_continue(self.check_duplicates()): return None self.create_e_invoice(update_stock) #print(self.e_invoice) self.doc = gui_api_wrapper(Api.api.insert,self.e_invoice) #print(self.doc) upload = gui_api_wrapper(Api.api.read_and_attach_file, "Purchase Invoice",self.doc['name'], infile,True) self.doc['supplier_invoice'] = upload['file_url'] self.doc = gui_api_wrapper(Api.api.update,self.doc) #doc = gui_api_wrapper(Api.api.get_doc,'Purchase Invoice',self.doc['name']) if easygui.buttonbox("Einkaufsrechnung {0} als Entwurf an ERPNext übertragen.\n\nSoll die Rechnung auch gleich gebucht werden oder nicht?".format(self.e_invoice['title']),"Sofort buchen?",["Sofort buchen","Später buchen"]) == "Sofort buchen": self.doc = gui_api_wrapper(Api.api.submit,self.doc) return self
def add_item_price(self, e_item, rate, uom, date): docs = gui_api_wrapper(Api.api.get_list, 'Item Price', filters={'item_code': e_item['item_code']}) if docs: doc = gui_api_wrapper(Api.api.get_doc, 'Item Price', docs[0]['name']) if abs(float(doc['price_list_rate']) - rate) > 0.0005: title = "Preis anpassen?" msg = "Artikel: {0}\nAlter Preis: {1}\nNeuer Preis: {2:.2f}".\ format(e_item['description'],doc['price_list_rate'],rate) msg += "\n\nPreis anpassen?" if easygui.ccbox(msg, title): doc['price_list_rate'] = rate gui_api_wrapper(Api.api.update, doc) else: price = { 'doctype': 'Item Price', 'item_code': e_item['item_code'], 'selling': True, 'buying': True, 'price_list': STANDARD_PRICE_LIST, 'valid_from': date, 'uom': uom, 'price_list_rate': rate } #print(price,e_item) doc = gui_api_wrapper(Api.api.insert, price)
def payment(self,inv): references = \ [{'reference_doctype' : 'Sales Invoice' if inv.is_sales else 'Purchase Invoice', 'reference_name' : inv.name, 'allocated_amount' : abs(self.amount)}] entry = {'doctype' : 'Payment Entry', 'title' : inv.party+" "+inv.reference, 'payment_type': 'Receive' if inv.is_sales else 'Pay', 'posting_date': self.date, 'reference_no': inv.reference, 'reference_date': self.date, 'party' : inv.party, 'party_type' : inv.party_type, 'company': self.company_name, 'paid_from' : settings.DEBIT_TO_ACCOUNT if inv.is_sales else self.baccount.e_account, 'paid_to': self.baccount.e_account if inv.is_sales else settings.CREDIT_TO_ACCOUNT, 'paid_amount' : abs(self.amount), 'received_amount' : abs(self.amount), 'source_exchange_rate': 1.0, 'target_exchange_rate': 1.0, 'exchange_rate': 1.0, 'references' : references} p = gui_api_wrapper(Api.api.insert,entry) if p: self.doc['status'] = 'Reconciled' self.doc['payment_entries'] = \ [{'payment_document': 'Payment Entry', 'payment_entry': p['name'], 'allocated_amount': abs(self.amount)}] gui_api_wrapper(Api.api.update,self.doc)
def load_item_data(cls): if not Api.items_by_code: Api.items_by_code = {} Api.item_code_translation = defaultdict(lambda: {}) company_name = sg.UserSettings()['-company-'] items = Api.api.get_list('Item', limit_page_length=LIMIT) print("Lese alle {} ERPNext-Artikel ein".format(len(items)), end="") for item in items: print(".", end="") item_code = item["item_code"] doc = Api.api.get_doc('Item', item_code) Api.items_by_code[item_code] = doc if not doc['item_defaults']: doc['item_defaults'] = [{ 'company': company_name, 'default_warehouse': WAREHOUSE }] gui_api_wrapper(Api.api.update, doc) for supplier_items in doc['supplier_items']: if 'supplier_part_no' in supplier_items: supplier = supplier_items['supplier'] supplier_part_no = supplier_items['supplier_part_no'] Api.item_code_translation[supplier][supplier_part_no] =\ item_code print()
def payment(self, inv, is_adv=False): if is_adv: party = inv['party'] party_type = inv['party_type'] is_recv = inv['is_recv'] allocated = abs(self.doc['unallocated_amount']) references = [] ref = utils.find_ref(self.description) else: is_recv = inv.is_sales party = inv.party party_type = inv.party_type allocated = min( [abs(self.doc['unallocated_amount']), inv.outstanding]) references = \ [{'reference_doctype' : 'Sales Invoice' if inv.is_sales else 'Purchase Invoice', 'reference_name' : inv.name, 'allocated_amount' : allocated}] ref = inv.reference if inv.reference else "" if is_recv: paid_from = self.company.receivable_account paid_to = self.baccount.e_account payment_type = 'Receive' else: paid_from = self.baccount.e_account paid_to = self.company.payable_account payment_type = 'Pay' entry = { 'doctype': 'Payment Entry', 'title': party + " " + ref, 'payment_type': payment_type, 'posting_date': self.date, 'reference_no': ref, 'reference_date': self.date, 'party': party, 'party_type': party_type, 'company': self.company_name, 'finance_book': self.company.default_finance_book, 'paid_from': paid_from, 'paid_to': paid_to, 'paid_amount': allocated, 'received_amount': allocated, 'source_exchange_rate': 1.0, 'target_exchange_rate': 1.0, 'exchange_rate': 1.0, 'references': references } p = gui_api_wrapper(Api.api.insert, entry) if p: print("Zahlung {} erstelltt".format(p['name'])) if sg.UserSettings()['-buchen-']: gui_api_wrapper(Api.submit_doc, "Payment Entry", p['name']) print("Zahlung {} gebucht".format(p['name'])) self.doc['doctype'] = 'Bank Transaction' self.link_to('Payment Entry', p['name'], allocated) self.update() return p else: return None
def search_item(self,supplier): if self.item_code: if supplier in Api.item_code_translation: trans_table_supplier = Api.item_code_translation[supplier] if self.item_code in trans_table_supplier: e_item_code = trans_table_supplier[self.item_code] e_item = Api.items_by_code[e_item_code] return e_item # look for most similar e_items sim_items = [] for e_code, e_item in Api.items_by_code.items(): sim_items.append((utils.similar(e_item['item_name'], self.description), e_item)) top_items = sorted(sim_items,reverse=True,key=lambda x:x[0])[0:20] #print(top_items) texts = ['Neuen Artikel anlegen'] texts += [i[1]['item_code']+' '+i[1]['item_name'] for i in top_items] title = "Artikel wählen" msg = "Artikel in Rechnung:\n{0}\n\n".format(self.long_description) msg += "Bitte passenden Artikel in ERPNext auswählen:" choice = easygui.choicebox(msg, title, texts) if choice: choice = texts.index(choice) if choice: e_item = top_items[choice-1][1] if self.item_code: doc = gui_api_wrapper(Api.api.get_doc,'Item', e_item['item_code']) doc['supplier_items'].append(\ { 'supplier': supplier, 'supplier_part_no' : self.item_code}) #print(doc['supplier_items']) gui_api_wrapper(Api.api.update,doc) return e_item else: title = "Neuen Artikel in ERPNext eintragen" msg = self.long_description+"\n" if self.item_code: msg += "Code Lieferant: "+self.item_code+"\n" msg += "Einzelpreis: {0:.2f}€".format(self.rate) msg += "\n\nDiesen Artikel eintragen?" if easygui.ccbox(msg, title): item_code = "new"+''.join(random.choices(\ string.ascii_uppercase + string.digits, k=8)) company_name = self.purchase_invoice.company_name e_item = {'doctype' : 'Item', 'item_code' : item_code, 'item_name' : self.description, 'description' : self.long_description, 'item_group' : STANDARD_ITEM_GROUP, 'item_defaults': [{'company': company_name, 'default_warehouse': WAREHOUSE}], 'stock_uom' : self.qty_unit} e_item = gui_api_wrapper(Api.api.insert,e_item) return e_item return None
def send_to_erpnext(self): print("Stelle ERPNext-Rechnung zusammen") self.create_doc() Api.create_supplier(self.supplier) #print(self.doc) print("Übertrage ERPNext-Rechnung") if not self.insert(): return None # now we have a doc and can init class Invoice super().__init__(self.doc,False) #print(self.doc) self.company.purchase_invoices[self.doc['supplier']].append(self.doc) print("Übertrage PDF der Rechnung") upload = None for infile in self.infiles: upload = gui_api_wrapper(Api.api.read_and_attach_file, "Purchase Invoice",self.doc['name'], infile,True) # currently, we can only link to the last PDF self.doc['supplier_invoice'] = upload['file_url'] self.update() choices = ["Sofort buchen","Später buchen"] msg = "Einkaufsrechnung {0} wurde als Entwurf an ERPNext übertragen:\n{1}\n\n".format(self.doc['title'],self.summary()) title = "Rechnung {}".format(self.no) filters={'company':self.company_name, 'party':self.supplier, 'docstatus':1, 'paid_amount':self.gross_total} py = gui_api_wrapper(Api.api.get_list, "Payment Entry", filters=filters, limit_page_length=1) if py: py = doc.Doc(name=py[0]['name'],doctype='Payment Entry') bt = None else: bt = bank.BankTransaction.find_bank_transaction(self.company_name, -self.gross_total) if py: msg += "\n\nZugehörige Anzahlung gefunden: {}\n".\ format(py.name) choices[0] = "Sofort buchen und zahlen" if bt: msg += "\n\nZugehörige Bank-Transaktion gefunden: {}\n".\ format(bt.description) choices[0] = "Sofort buchen und zahlen" if easygui.buttonbox(msg,title,choices) in \ ["Sofort buchen","Sofort buchen und zahlen"]: print("Buche Rechnung") if py: self.use_advance_payment(py) self.submit() if bt: self.payment(bt) return self
def create_supplier(cls, supplier): supps = gui_api_wrapper(Api.api.get_list, "Supplier", filters={'name': supplier}) if not supps: doc = { 'doctype': 'Supplier', 'supplier_name': supplier, 'supplier_group': DEFAULT_SUPPLIER_GROUP } gui_api_wrapper(Api.api.insert, doc)
def journal_entry(self, cacc_or_bt, is_bt): amount = self.doc['unallocated_amount'] withdrawal = min([amount, self.withdrawal]) deposit = min([amount, self.deposit]) if is_bt: bt = BankTransaction(cacc_or_bt) bt.load() against_account = bt.baccount.e_account else: against_account = cacc_or_bt accounts = [{ 'account': self.baccount.e_account, 'cost_center': self.company.cost_center, 'debit': deposit, 'debit_in_account_currency': deposit, 'credit': withdrawal, 'credit_in_account_currency': withdrawal }, { 'account': against_account, 'cost_center': self.company.cost_center, 'debit': withdrawal, 'debit_in_account_currency': withdrawal, 'credit': deposit, 'credit_in_account_currency': deposit }] entry = { 'doctype': 'Journal Entry', 'title': self.description[0:140], 'voucher_type': 'Journal Entry', 'company': self.company_name, 'finance_book': self.company.default_finance_book, 'posting_date': self.date, 'user_remark': self.description, 'accounts': accounts } #print(entry) j = gui_api_wrapper(Api.api.insert, entry) #print(j) print("Buchungssatz {} erstellt".format(j['name'])) if j: j['account'] = against_account self.company.journal.append(j) if sg.UserSettings()['-buchen-']: gui_api_wrapper(Api.submit_doc, "Journal Entry", j['name']) print("Buchungssatz {} gebucht".format(j['name'])) self.link_to('Journal Entry', j['name'], amount) self.update() if is_bt: bt.link_to('Journal Entry', j['name'], amount) bt.update()
def reconciliate_all(self): Api.load_account_data() sinvs = self.get_open_sales_invoices() pinvs = self.get_open_purchase_invoices() bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters={ 'company': self.name, 'status': 'Pending' }) for bt in bts: bt = gui_api_wrapper(Api.api.get_doc, 'Bank Transaction', bt['name']) if (not 'payment_entries' in bt) or (not bt['payment_entries']): bank.BankTransaction(bt).transfer(sinvs, pinvs)
def submit_entry(cls, doc_name, is_journal=True): doctype = "Journal Entry" if is_journal else "Payment Entry" doctype_name = "Buchungssatz" if is_journal else "Zahlung" bts = gui_api_wrapper(Api.api.get_list, "Bank Transaction", filters=[[ "Bank Transaction Payments", "payment_entry", "=", doc_name ]]) for bt in bts: bt_name = bt['name'] gui_api_wrapper(Api.submit_doc, "Bank Transaction", bt_name) print("Banktransaktion {} gebucht".format(bt_name)) gui_api_wrapper(Api.submit_doc, doctype, doc_name) print("{} {} gebucht".format(doctype_name, doc_name))
def check_if_present(self): invs = gui_api_wrapper(Api.api.get_list,"Purchase Invoice", {'bill_no': self.no, 'status': ['!=','Cancelled']}) if invs: easygui.msgbox("Einkaufsrechnung {} ist schon als {} in ERPNext eingetragen worden".format(self.no,invs[0]['name'])) return True return False
def init_companies(cls): if not Company.companies_by_name: print("Lade Firmendaten", end="") for comp in gui_api_wrapper(Api.api.get_list, 'Company'): print(".", end="") Company(comp) print()
def init_baccounts(cls): if not BankAccount.baccounts_by_iban: print("Lade Kontodaten", end="") for bacc in gui_api_wrapper(Api.api.get_list, 'Bank Account'): print(".", end="") BankAccount(bacc) print()
def open_payment_entries(self): return gui_api_wrapper(Api.api.get_list, 'Payment Entry', filters={ 'company': self.name, 'docstatus': 0 }, limit_page_length=LIMIT)
def open_purchase_invoices(self): return gui_api_wrapper(Api.api.get_list, 'Purchase Invoice', filters={ 'company': self.name, 'docstatus': 0 }, limit_page_length=LIMIT)
def get_open_invoices_of_type(self, inv_type): is_sales = (inv_type == 'Sales Invoice') invs = gui_api_wrapper(\ Api.api.get_list,inv_type, filters={'status':['in',['Unpaid','Overdue']], 'company':self.name}, limit_page_length=LIMIT) return list(map(lambda inv: Invoice(inv, is_sales), invs))
def get_balance(self): bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters={ 'bank_account': self.name, 'status': ['!=', 'Cancelled'] }, limit_page_length=LIMIT) self.balance = sum([bt['deposit'] - bt['withdrawal'] for bt in bts])
def open_bank_transactions(self): bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters={ 'company': self.name, 'status': 'Pending', 'unallocated_amount': ['>', 0] }, limit_page_length=LIMIT) return bts
def open_bank_transactions(self): bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters={ 'company': self.name, 'status': 'Pending' }, limit_page_length=LIMIT) return list(filter(lambda bt: \ (not 'payment_entries' in bt) or (not bt['payment_entries']),bts))
def get_invoices_of_type_and_status(self, inv_type, status): is_sales = (inv_type == 'Sales Invoice') invs = gui_api_wrapper(Api.api.get_list, inv_type, filters={ 'status': status, 'company': self.name }, limit_page_length=LIMIT) return list(map(lambda inv: Invoice(inv, is_sales), invs))
def load_item_data(cls): if not Api.items_by_code: Api.items_by_code = {} Api.item_code_translation = defaultdict(lambda: {}) for item in Api.api.get_list('Item', limit_page_length=LIMIT): item_code = item["item_code"] doc = Api.api.get_doc('Item', item_code) Api.items_by_code[item_code] = doc if not doc['item_defaults']: doc['item_defaults'] = [{ 'company': COMPANY, 'default_warehouse': WAREHOUSE }] gui_api_wrapper(Api.api.update, doc) for supplier_items in doc['supplier_items']: if 'supplier_part_no' in supplier_items: supplier = supplier_items['supplier'] supplier_part_no = supplier_items['supplier_part_no'] Api.item_code_translation[supplier][supplier_part_no] =\ item_code
def __init__(self, doc): self.doc = doc self.name = doc['name'] self.cost_center = doc['cost_center'] Api.load_account_data() self.accounts = Api.accounts_by_company[self.name] self.leaf_accounts = list( filter(lambda acc: acc['is_group'] == 0, self.accounts)) self.leaf_accounts.sort(key=lambda acc: acc['root_type']) self.leaf_accounts_by_root_type = {} for rt, accs in itertools.groupby(self.leaf_accounts, lambda acc: acc['root_type']): self.leaf_accounts_by_root_type[rt] = list(accs) self.doc = gui_api_wrapper(Api.api.get_doc, 'Company', self.name) Company.companies_by_name[self.name] = self self.leaf_accounts_for_debit = self.leaf_accounts_starting_with_root_type( "Income") self.leaf_accounts_for_credit = self.leaf_accounts_starting_with_root_type( "Expense")
def find_bank_transaction(cls, comp_name, total, text=""): key = 'deposit' if total < 0: key = 'withdrawal' total = -total bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters={ 'company': comp_name, key: total, 'status': 'Pending' }) bts = [BankTransaction(bt) for bt in bts] l = len(bts) if l == 0: return None if l > 1 and text: bts1 = [(bt,utils.similar(bt.description,text)) \ for bt in bts] bts1.sort(key=lambda x: x[1], reverse=True) bt = bts1[0][0] else: bt = bts[0] return bt
def delete_entry(cls, doc_name, is_journal=True): doctype = "Journal Entry" if is_journal else "Payment Entry" doctype_name = "Buchungssatz" if is_journal else "Zahlung" bts = gui_api_wrapper(Api.api.get_list, "Bank Transaction", filters=[[ "Bank Transaction Payments", "payment_entry", "=", doc_name ]]) if bts: bt = gui_api_wrapper(Api.api.get_doc, "Bank Transaction", bts[0]['name']) bt['payment_entries'] = list( filter(lambda pe: pe['payment_entry'] != doc_name, bt['payment_entries'])) bt['status'] = 'Pending' gui_api_wrapper(Api.api.update, bt) print("Banktransaktion {} angepasst".format(bt['name'])) else: print("Keine Banktransaktion angepasst: "+\ "{} {} nicht in Banktransaktionen gefunden".\ format(doctype_name,doc_name)) gui_api_wrapper(Api.api.delete, doctype, doc_name) print("{} {} gelöscht".format(doctype_name, doc_name))
def init_baccounts(cls): for bacc in gui_api_wrapper(Api.api.get_list,'Bank Account'): BankAccount(bacc)
def transfer(self, sinvs, pinvs): if self.deposit: accounts = self.company.leaf_accounts_for_debit invs = sinvs side = "withdrawal" else: accounts = self.company.leaf_accounts_for_credit invs = pinvs side = "deposit" # find accounts that could match, using previous journal entries account_names = list(map(lambda acc: acc['name'], accounts)) jaccs = [(je['account'], utils.similar(self.description,je['user_remark'])) \ for je in self.company.journal if 'user_remark' in je and je['user_remark']] jaccs.sort(key=lambda x: x[1], reverse=True) jaccs = list(set([j for (j, desc) in jaccs[0:5]])) for j in jaccs: try: account_names.remove(j) except Exception: pass account_names = jaccs + account_names # find invoices that could match invs.sort(key=lambda inv: abs(inv.outstanding - abs(self.amount))) inv_texts = list( map( lambda inv: utils.showlist( [inv.name, inv.party, inv.reference, inv.outstanding]), invs)) # find bank transactions in other bank accounts that could match filters = { 'company': self.company.name, 'status': 'Pending', 'bank_account': ['!=', self.bank_account], side: ['>', 0], 'unallocated_amount': abs(self.amount) } bts = gui_api_wrapper(Api.api.get_list, 'Bank Transaction', filters=filters, limit_page_length=LIMIT) bt_texts = list( map( lambda bt: utils.showlist([ bt['name'], bt['deposit'] if bt['deposit'] else -bt['withdrawal'], bt['description'], bt['unallocated_amount'] ]), bts)) # let the user choose between all these title = "Rechnung, Banktransaktion oder Buchungskonto wählen" msg = "Bankbuchung:\n" + self.show() + "\n\n" + title + "\n" options = ["Anzahlung"] + bt_texts + inv_texts + account_names choice = easygui.choicebox(msg, title, options) # and process the choice if choice == "Anzahlung": if self.deposit: party_type = 'Customer' party_descr = 'Kunden' is_recv = True else: party_type = 'Supplier' party_descr = 'Lieferanten' is_recv = False parties = gui_api_wrapper(Api.api.get_list, party_type) party_names = list(map(lambda p: p['name'], parties)) party_names.sort(key=str.casefold) title = party_descr + " wählen" msg = title choice = easygui.choicebox(msg, title, party_names) if choice: self.payment( { 'party_type': party_type, 'party': choice, 'is_recv': is_recv }, True) elif choice in inv_texts: inv = invs[inv_texts.index(choice)] self.payment(inv) elif choice: is_bt = choice in bt_texts if is_bt: choice = bts[bt_texts.index(choice)] self.journal_entry(choice, is_bt)
def submit_doc(cls, doctype, docname): doc = gui_api_wrapper(Api.api.get_doc, doctype, docname) gui_api_wrapper(Api.api.submit, doc)