def upload_account() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" online_cursor.execute(query) timestamp = (online_cursor.fetchall())[0][0] # Getting new data query = "select supplier_id, party_id, partial_amount, gr_amount from supplier_party_account " \ "where last_update > CAST('{}' AS DATETIME)".format(timestamp) local_cursor.execute(query) new_data = local_cursor.fetchall() sql = "INSERT INTO supplier_party_account (supplier_id, party_id, partial_amount, gr_amount)" \ "VALUES (%s, %s, %s, %s)" online_cursor.executemany(sql, new_data) online_db.commit() local_db.disconnect() online_db.disconnect()
def download_gr_settle() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" local_cursor.execute(query) timestamp = (local_cursor.fetchall())[0][0] # Getting new data query = "select supplier_id, party_id, start_date, end_date, settle_amount from gr_settle where last_update > " \ "CAST('{}' AS DATETIME)".format(timestamp) online_cursor.execute(query) new_data = online_cursor.fetchall() sql = "INSERT INTO gr_settle (supplier_id, party_id, start_date, end_date, settle_amount)" \ "VALUES (%s, %s, %s, %s, %s)" local_cursor.executemany(sql, new_data) local_db.commit() local_db.disconnect() online_db.disconnect()
def upload_individual() -> None: """ Add all the suppliers and party to the online database """ individual_list = ["supplier", "party", "bank", "Transport"] # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" online_cursor.execute(query) timestamp = (online_cursor.fetchall())[0][0] for individual in individual_list: # Getting new data query = "select id, name, address from {} where last_update > CAST('{}' AS DATETIME)".format( individual, timestamp) local_cursor.execute(query) new_data = local_cursor.fetchall() sql = "INSERT INTO {} (id, name, address) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE " \ "name = VALUES(name), address =VALUES(address)".format(individual) online_cursor.executemany(sql, new_data) online_db.commit() local_db.disconnect() online_db.disconnect()
def download_memo_payments() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" local_cursor.execute(query) timestamp = (local_cursor.fetchall())[0][0] # Getting new data query = "select id, memo_id, bank_id, cheque_number from memo_payments where last_update > CAST('{}' AS DATETIME)" \ .format(timestamp) online_cursor.execute(query) new_data = online_cursor.fetchall() sql = "INSERT INTO memo_payments (id, memo_id, bank_id, cheque_number)" \ "VALUES (%s, %s, %s, %s)" \ "ON DUPLICATE KEY UPDATE memo_id=VALUES(memo_id), bank_id=VALUES(bank_id), " \ "cheque_number=VALUES(cheque_number)" local_cursor.executemany(sql, new_data) local_db.commit() local_db.disconnect() online_db.disconnect()
def check_new_memo(memo_number: int, memo_date: str, supplier_name: str, party_name: str) -> bool: """ Check if the memo already exists. """ # Open a new connection db, cursor = db_connector.cursor() date = datetime.datetime.strptime(memo_date, "%d/%m/%Y") supplier_id = retrieve_indivijual.get_supplier_id_by_name(supplier_name) party_id = retrieve_indivijual.get_party_id_by_name(party_name) query = "select register_date, supplier_id, party_id from memo_entry where memo_number = '{}' order by 1 DESC".format( memo_number) cursor.execute(query) data = cursor.fetchall() db.disconnect() if len(data) == 0: return True if (date - data[0][0]).days >= 30: return True if int(data[0][1]) == supplier_id and int( data[0][2]) == party_id and (date - data[0][0]).days == 0: return True return False
def upload_register_entry() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" online_cursor.execute(query) timestamp = (online_cursor.fetchall())[0][0] # Getting new data query = "select id, supplier_id, party_id, register_date, amount, " \ "partial_amount, bill_number, status, d_amount," \ " d_percent, gr_amount from register_entry where last_update > CAST('{}' AS DATETIME)".format(timestamp) local_cursor.execute(query) new_data = local_cursor.fetchall() sql = "INSERT INTO register_entry (id, supplier_id, party_id, register_date, amount, " \ "partial_amount, bill_number, status, " \ "d_amount, d_percent, gr_amount) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" \ "ON DUPLICATE KEY UPDATE status=VALUES(status), partial_amount=VALUES(partial_amount)," \ " d_amount=VALUES(d_amount), d_percent=VALUES(d_percent), gr_amount=VALUES(gr_amount)" online_cursor.executemany(sql, new_data) online_db.commit() local_db.disconnect() online_db.disconnect()
def upload_memo_bills() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" online_cursor.execute(query) timestamp = (online_cursor.fetchall())[0][0] # Getting new data query = "select id, memo_id, bill_number, type, amount from memo_bills where last_update > " \ "CAST('{}' AS DATETIME)".format(timestamp) local_cursor.execute(query) new_data = local_cursor.fetchall() sql = "INSERT INTO memo_bills (id, memo_id, bill_number, type, amount)" \ "VALUES (%s, %s, %s, %s, %s)" \ "ON DUPLICATE KEY UPDATE memo_id=VALUES(memo_id), bill_number=VALUES(bill_number), type=VALUES(type), " \ "amount=VALUES(amount)" online_cursor.executemany(sql, new_data) online_db.commit() local_db.disconnect() online_db.disconnect()
def upload_memo_entry() -> None: """ Add all the new and updated register entries to the online database """ # Open a new connection local_db, local_cursor = db_connector.cursor() online_db = online_db_connector.connect() online_cursor = online_db.cursor() # get the last update timestamp query = "select updated_at from last_update" online_cursor.execute(query) timestamp = (online_cursor.fetchall())[0][0] # Getting new data query = "select id, memo_number, supplier_id, party_id, register_date" \ " from memo_entry where last_update > CAST('{}' AS DATETIME)".format(timestamp) local_cursor.execute(query) new_data = local_cursor.fetchall() sql = "INSERT INTO memo_entry (id, memo_number, supplier_id, party_id, register_date)" \ "VALUES (%s, %s, %s, %s, %s)" \ "ON DUPLICATE KEY UPDATE memo_number=VALUES(memo_number), register_date=VALUES(register_date)" online_cursor.executemany(sql, new_data) online_db.commit() local_db.disconnect() online_db.disconnect()
def get_party_name_by_id(party_id: int) -> str: """ Get party name by ID """ # Open a new connection db, cursor = db_connector.cursor() query = "select name from party where id = '{}';".format(party_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_all_supplier_id_name() -> List[Tuple]: """ Get all supplier ids and names returned in a List """ # Open a new connection db, cursor = db_connector.cursor() query = "select id, name from supplier" cursor.execute(query) data = cursor.fetchall() db.disconnect() return data
def get_bank_address_by_id(bank_id: int) -> str: """ Get the bank address """ # Open a new connection db, cursor = db_connector.cursor() query = "select address from bank where name = '{}';".format(bank_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_bank_id_by_name(bank_name: str) -> int: """ Get bank ID by name """ # Open a new connection db, cursor = db_connector.cursor() query = "select id from bank where name = '{}';".format(bank_name) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_transport_address_by_name(transport_name: str) -> str: """ Get the transport address """ # Open a new connection db, cursor = db_connector.cursor() query = "select address from transport where name = '{}';".format( transport_name) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_supplier_address_by_name(supplier_name: str) -> str: """ Get the supplier address """ # Open a new connection db, cursor = db_connector.cursor() query = "select address from supplier where name = '{}';".format( supplier_name) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_all_supplier_names() -> List[str]: """ Get all supplier names returned in a List """ # Open a new connection db, cursor = db_connector.cursor() query = "select name from supplier order by id" cursor.execute(query) data = cursor.fetchall() r_list = [x[0] for x in data] db.disconnect() return r_list
def get_all_party_id() -> List[int]: """ Get all party ids returned in a List """ # Open a new connection db, cursor = db_connector.cursor() query = "select id from party order by id" cursor.execute(query) data = cursor.fetchall() r_list = [x[0] for x in data] db.disconnect() return r_list
def supplier_exist(supplier_name: str) -> bool: """ Checks if the supplier exists in the database """ # Open a new connection db, cursor = db_connector.cursor() query = "select name from supplier where name = '{}'".format(supplier_name) cursor.execute(query) data = cursor.fetchall() db.disconnect() if len(data) == 0: return False return True
def get_id_by_memo_number(memo_number, supplier_id, party_id) -> int: """ Get the memo_id using memo_number, supplier_id and party_id """ # Open a new connection db, cursor = db_connector.cursor() query = "select id from memo_entry where memo_number = {} AND supplier_id = {} AND party_id = {} " \ "order by register_date DESC;".format( memo_number, supplier_id, party_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() return int(data[0][0])
def check_new_register(entry: RegisterEntry) -> bool: """ Check if the register_entry already exists. """ # Open a new connection db, cursor = db_connector.cursor() query = "select id from register_entry where bill_number = '{}' AND supplier_id = '{}' AND party_id = '{}'".format( entry.bill_number, entry.supplier_id, entry.party_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() if len(data) == 0: return True return False
def insert_memo_bills(entry: MemoBill) -> None: """ Insert all the bills attached to the same memo number. """ # Open a new connection db, cursor = db_connector.cursor() sql = "INSERT INTO memo_bills (memo_id, bill_number, type, amount) " \ "VALUES (%s, %s, %s, %s)" val = (entry.memo_id, entry.memo_number, entry.type, entry.amount) cursor.execute(sql, val) db_connector.add_stack_val(sql, val) db.commit() db.disconnect() db_connector.update()
def insert_partial_payment(entry: MemoEntry) -> None: """ Inserts partial payment between the account of supplier and party. """ # Open a new connection db, cursor = db_connector.cursor() sql = "INSERT INTO supplier_party_account (supplier_id, party_id, partial_amount) " \ "VALUES (%s, %s, %s)" val = (entry.supplier_id, entry.party_id, entry.amount) cursor.execute(sql, val) db_connector.add_stack_val(sql, val) db.commit() db.disconnect() db_connector.update()
def get_party_id_by_name(party_name: str) -> int: """ Get party ID by name """ # Open a new connection db, cursor = db_connector.cursor() use_name = "" for chars in party_name: if chars in ["'", '"']: use_name = use_name + "%" else: use_name = use_name + chars query = "select id from party where name LIKE '{}';".format(use_name) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_gr(supplier_id: int, party_id: int) -> int: """ Returns the gr_amount without bill between the party and supplier. """ # Open a new connection db, cursor = db_connector.cursor() query = "select gr_amount from supplier_party_account where supplier_id = '{}' AND party_id = '{}'".format( supplier_id, party_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() if len(data) == 0: return 0 return int(data[0][0])
def check_add_memo(memo_number: int, memo_date: str) -> bool: """ Check if the memo already exists. """ # Open a new connection db, cursor = db_connector.cursor() date = datetime.datetime.strptime(memo_date, "%d/%m/%Y") query = "select register_date from memo_entry where memo_number = '{}' order by 1 DESC".format( memo_number) cursor.execute(query) data = cursor.fetchall() db.disconnect() if len(data) == 0: return True if (date - data[0][0]).days >= 365: return True return False
def get_usable_gr(supplier_id: int, party_id: int) -> int: """ Gets the usable gr_amount """ # Open a new connection db, cursor = db_connector.cursor() query = "select SUM(settle_amount) from gr_settle where supplier_id = '{}' AND party_id = '{}'".format( supplier_id, party_id) cursor.execute(query) data = cursor.fetchall() db.disconnect() if data[0][0] is None: return get_gr(supplier_id, party_id) else: return get_gr(supplier_id, party_id) - int(data[0][0])
def insert_register_entry(entry: RegisterEntry) -> None: """ Insert a register_entry into the database. """ # Open a new connection db, cursor = db_connector.cursor() sql = "INSERT INTO register_entry (supplier_id, party_id, register_date, amount, bill_number, status, " \ "d_amount, d_percent) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" val = (entry.supplier_id, entry.party_id, str(entry.date), entry.amount, entry.bill_number, entry.status, entry.d_amount, entry.d_percent) cursor.execute(sql, val) db_connector.add_stack_val(sql, val) db.commit() db.disconnect() db_connector.update()
def update_register_entry_data(entry: RegisterEntry) -> None: """ Update changes made to the register entry by a memo_entry """ # Open a new connection db, cursor = db_connector.cursor() entry_id = retrieve_register_entry.get_register_entry_id( entry.supplier_id, entry.party_id, entry.bill_number) query = "UPDATE register_entry SET partial_amount = '{}', status = '{}', " \ "d_amount = '{}', d_percent = '{}', gr_amount = '{}' WHERE id = {}"\ .format(entry.part_payment, entry.status, entry.d_amount, entry.d_percent, entry.gr_amount, entry_id) cursor.execute(query) db_connector.add_stack(query) db.commit() db.disconnect() db_connector.update()
def add_partial_amount(supplier_id: int, party_id: int, amount: int) -> None: """ Add partial amount between a supplier and party """ # Open a new connection db, cursor = db_connector.cursor() partial_amount = int( retrieve_partial_payment.get_partial_payment(supplier_id, party_id)) amount += partial_amount query = "UPDATE supplier_party_account SET partial_amount = {} WHERE supplier_id = {} AND party_id = {}" \ .format(amount, supplier_id, party_id) cursor.execute(query) db.commit() db.disconnect() db_connector.update()
def get_supplier_id_by_name(supplier_name: str) -> int: """ Get supplier ID by name """ # Open a new connection db, cursor = db_connector.cursor() use_name = "" for chars in supplier_name: if chars in ["'", '"']: use_name = use_name + "%" else: use_name = use_name + chars query = "select id from supplier where name LIKE '{}'".format( str(use_name)) cursor.execute(query) data = cursor.fetchall() db.disconnect() return data[0][0]
def get_gr_between_dates(supplier_id: int, party_id: int, start_date: str, end_date: str) -> int: """ Get the gr_between dates used to settle the account """ # Open a new connection db, cursor = db_connector.cursor() start_date = str(datetime.datetime.strptime(start_date, "%d/%m/%Y")) end_date = str(datetime.datetime.strptime(end_date, "%d/%m/%Y")) query = "select SUM(settle_amount) from gr_settle where " \ "party_id = '{}' AND supplier_id = '{}' AND " \ "start_date >= '{}' AND end_date <= '{}';".format(party_id, supplier_id, start_date, end_date) cursor.execute(query) data = cursor.fetchall() db.disconnect() if data[0][0] is None or len(data) == 0 or data[0][0] == 0: return -1 return data[0][0]