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]
