def get_product_id(self, name, unit):
try:
result = cf.execute_(
"insert into {}(name, unit) values (%s, %s) returning id",
["product"],
arg_=(name, unit),
fetch_="yes")
return result[0]
except:
result = cf.execute_("select id from {} where name = %s",
["product"],
arg_=(name, ),
fetch_="yes")
return result[0]
def update_owner_product(self):
rate_date = str(datetime.datetime.today())
properties = ['id_owner', 'id_product', 'rate', 'timestamp_']
if self.owner_product == "customer_product":
cf.execute_(
"insert into customer_product ({}) values (%s, %s, %s, %s) returning id",
properties,
arg_=(self.id_owner, self.id_product, self.rate, rate_date))
elif self.owner_product == "vendor_product":
print("Updating vendor_product...")
cf.execute_(
"insert into vendor_product ({}) values (%s, %s, %s, %s) returning id",
properties,
arg_=(self.id_owner, self.id_product, self.rate, rate_date))
def get_properties_by_id(owner_type, id_):
return cf.execute_("select {} from {} where {} = %s",
sq_properties[1:],
table_=owner_type,
where_="id",
arg_=(id_, ),
fetch_="y")
def get_properties_by_nickname(owner_type, nickname):
return cf.execute_("select {} from {} where {} = %s",
sq_properties[:-1],
table_=owner_type,
where_="nickname",
arg_=(nickname, ),
fetch_="y")
def create_new_invoice(self):
''' insert a new record in invoice table and return id '''
sq = "insert into {} (date_, id_owner, amount, recipient, detail, medium, gst_invoice_no) values (%s,%s, %s, %s, %s, %s, %s) returning id"
return cf.execute_(sq, [self.invoice_type],
arg_=(self.date_, self.id_owner, self.amount,
self.recipient, self.detail, self.medium,
self.gst_invoice_no),
fetch_="y")[0]
def get_product_details(name):
''' return product details from name, if unsuccessful then try abbr, if still unsuccessful return None (default) '''
for check_ in check_list:
result = cf.execute_(check_,
properties,
arg_=(name.lower(), ),
fetch_='yes')
if result:
return result
def edit_product_property(self, property_):
if property_ == "id":
cf.log_("You cannot change 'id' of the product")
return None
old_value = getattr(self, property_)
new_value = cf.prompt_("Enter new {} for {}: ".format(
property_, self.name),
cf.get_completer_list(property_, "product"),
default_=old_value,
empty_="yes")
if old_value == new_value: return None
setattr(self, property_, new_value)
try:
cf.execute_(
"update product set {} = %s where id = %s returning id",
[property_],
arg_=(new_value, self.id))
except Exception as e:
print(e)
def set_property(property_, by_name=False):
if by_name:
name = cf.prompt_("Enter product name: ",
cf.get_completer_list("name", "product"),
unique_="existing")
if name in ["quit", "back"]: return "back"
cf.log_(name)
old_value = cf.execute_("select {} from {} where lower({})= %s",
[property_],
table_="product",
where_="name",
arg_=(name.lower(), ),
fetch_="yes")
old_value = old_value[0]
if old_value == None: old_value = ""
new_value = cf.prompt_("{} for {}: ".format(property_, name), [],
default_=old_value)
if new_value == "quit": return "back"
if new_value == "back": return "back"
if new_value == "s":
return "back" # only for consistency with bulk abbreviate
if old_value == new_value: return "back"
cf.log_(
cf.execute_(
"update product set {} = %s where name = %s returning id",
[property_],
arg_=(new_value, name)))
return "back"
result = cf.cursor_(
sql.SQL("select name from product where {} is null order by id desc").
format(sql.Identifier(property_)))
name_list = [element for tupl in result for element in tupl]
for name in name_list:
new_value = cf.prompt_("{} for {}: ".format(property_, name), [])
if new_value == "quit": return "quit"
if new_value == "back": return "back"
if new_value == "s": continue
cf.log_(
cf.cursor_(sql.SQL(
"update product set {} = %s where name = %s returning id").
format(sql.Identifier(property_)),
arguments=(new_value, name)))
def init_by_name(cls, name):
for check_ in check_list:
result = cf.execute_(check_,
properties,
arg_=(name.lower(), ),
fetch_='yes')
if result:
return cls(result[1], result[2])
name, unit = create_product(name)
if name == "quit": return "quit"
return cls(name, unit)
def get_previous_rate(self, **kwargs):
gst_ = kwargs.get('gst_')
gst_result = cf.execute_(
"select gst_rate from {} where id_product = %s and id_owner = %s order by timestamp_ desc",
[self.owner_product],
arg_=(self.id_product, self.id_owner),
fetch_="yes")
print('gst_rate: {}'.format(gst_result))
non_gst_result = cf.execute_(
"select rate from {} where id_product = %s and id_owner = %s order by timestamp_ desc",
[self.owner_product],
arg_=(self.id_product, self.id_owner),
fetch_="yes")
print('non-gst_rate: {}'.format(non_gst_result))
if gst_:
if gst_result:
return gst_result[0]
else:
if non_gst_result:
return non_gst_result[0]
def get_id_from_nickname(owner_type, nickname, **kwargs):
no_create = kwargs.get('no_create', '')
sq = "select {} from {} where {} = %s"
result = cf.execute_(sq, ["id"],
table_=owner_type,
where_="nickname",
arg_=[
nickname,
],
fetch_="y")
if result: return result[0]
if no_create: return None
owner_ = get_new_owner(owner_type, nickname=nickname)
#result = Owner.create_owner(owner_type, nickname=nickname)
if owner_: return owner_.id
def create_product(name):
unit = cf.prompt_("Enter {} Unit: ".format(name),
cf.get_completer_list("unit", "product"),
history_file=None,
default_="Nos")
if unit == "quit": return "quit", "quit"
if unit == "back": return "back", "back"
abbr_name = cf.prompt_("Enter {} abbr: ".format(name),
cf.get_completer_list("abbr_name", "product"),
history_file=None,
unique_="y",
empty_="y")
if abbr_name == "quit": return "quit", "quit"
if abbr_name == "back": return "back", "back"
print_name = cf.prompt_("Enter {} print_name: ".format(name),
cf.get_completer_list("print_name", "product"),
history_file=None,
unique_="y",
empty_="y",
default_=name)
if print_name == "quit": return "quit", "quit"
if print_name == "back": return "back", "back"
result = cf.execute_(
"insert into {} (name, unit, abbr_name, print_name) values (%s, %s, %s, %s) returning name, unit, id",
["product"],
arg_=(name, unit, abbr_name, print_name),
fetch_="yes")
id_ = result[2]
cf.log_("New Product ({}) was created".format(result[0]))
pricelist = cf.prompt_("Enter {} price_list: ".format(name),
cf.get_completer_list("name", "pricelist"),
empty_="y")
if pricelist == "quit": return "quit", "quit"
if pricelist == "back": return "back", "back"
if pricelist:
id_pricelist = plf.get_id_pricelist_by_name(pricelist)
pricelist_value = cf.prompt_("Enter pricelist value: ", [])
if pricelist_value == "quit": return "quit"
if pricelist_value == "back": return "back"
with conn() as cursor:
cursor.execute(
"insert into product_pricelist (id_product, value, id_pricelist) values (%s, %s, %s)",
(id_, pricelist_value, id_pricelist))
return [result[0], result[1]]
def get_owner_invoice(self, invoice_type, **kwargs):
fetch_ = kwargs.get('fetch_', '') # if empty, fetchall()
if fetch_:
result = cf.execute_(
"select {} from {} where {} = %s order by id desc", ["id"],
table_=invoice_type,
where_="id_owner",
arg_=(self.id, ),
fetch_=fetch_)
if result:
cf.log_("result is {}".format(result))
return result[0]
else:
filter_type = "All Owner Invoices"
filter_result = get_filter_result(filter_type,
invoice_type,
nickname=self.nickname)
selected_invoice = get_selected_invoice(filter_result,
invoice_type)
if selected_invoice in ["back"]: return "back"
return selected_invoice
def starting_command(input_, invoice_):
cf.log_("inside starting command")
# possible return values:
# 1. ["continue", "continue"]
# 2. [invoice_command, invoice_]
if input_ == "pack_n":
if invoice_.invoice_type == "sale_invoice":
detail_table = "si_detail"
owner_type = "customer"
if invoice_.invoice_type == "purchase_invoice":
detail_table = "pi_detail"
owner_type = "vendor"
new_invoice = invoice.get_new_invoice(
invoice_.invoice_type,
nickname=owner.get_nickname_from_id(owner_type, invoice_.owner.id))
sq = "update {} set id_invoice = %s where id_invoice = %s and packed is not null returning id"
with conn() as cursor:
cursor.execute(sq.format(detail_table), (
new_invoice.id,
invoice_.id,
))
# delete_list = cursor.fetchall()
new_invoice.update_invoice_with_sub_total()
invoice_.update_invoice_with_sub_total()
if input_ in ["makegst"]:
invoice_.makegst()
if input_ in ["cash_r", "invoice_r"]:
if invoice_.invoice_type == "sale_invoice":
money_type = "receipt"
medium = input_
with conn() as cursor:
cursor.execute(
"select id from {} where detail = %s".format(money_type),
(str(invoice_.id), ))
result = cursor.fetchall()
print(result)
if len(result) == 1:
with conn() as cursor:
cursor.execute(
"update {} set amount = %s where detail = %s returning id"
.format(money_type),
(invoice_.amount_after_freight, str(invoice_.id)))
result = cursor.fetchall()
cf.log_("Receipt Entry updated with id {}".format(result))
print("Receipt entry updated")
# money_ = money.Money('receipt', id_= result[0][0])
# money_.save()
elif len(result) > 1:
print("Multiple Entries present")
else:
date_ = money.get_date()
sq = "insert into {} (date_, id_owner, amount, recipient, detail, medium) values (%s,%s, %s, %s, %s, %s) returning id"
result = cf.execute_(sq, [money_type],
arg_=(date_, invoice_.owner.id,
invoice_.amount_after_freight,
"self", invoice_.id, medium),
fetch_="y")[0]
print("Receipt entry created")
cf.log_("Receipt Entry created with id {}".format(result))
# money_ = money.Money('receipt', id_= result[0])
# money_.save()
if input_ in ["pack", "unpack"]:
property_ = "packed"
if invoice_.invoice_type == "sale_invoice":
detail_table = "si_detail"
if invoice_.invoice_type == "purchase_invoice":
detail_table = "pi_detail"
sq_pack = "select product_name, packed, product_qty from {} where id_invoice = %s and packed is null order by id"
sq_unpack = "select product_name, packed, product_qty from {} where id_invoice = %s and packed is not null order by id"
if input_ == "pack":
sq = sq_pack
elif input_ == "unpack":
sq = sq_unpack
while True:
with conn() as cursor:
cursor.execute(sq.format(detail_table), (invoice_.id, ))
result = cursor.fetchall()
invoice_product_name_list = [element[0] for element in result]
invoice_product_name_qty_dict = {
element[0]: str(element[2])
for element in result
}
product_name = cf.prompt_dict("Edit Product: ",
invoice_product_name_qty_dict)
if product_name in invoice_product_name_list:
if input_ == "pack":
detail_table_id = invoice_.get_detail_table_id(
product_name, packed="yes"
) # returns id of items which are not packed
elif input_ == "unpack":
detail_table_id = invoice_.get_detail_table_id(
product_name, unpacked="yes")
i_detail_ = invoice_detail.get_existing_invoice_detail_by_id(
invoice_, detail_table_id)
i_detail_.edit_property(property_)
else:
break
if input_ in ["packed", "unpacked"]:
if invoice_.invoice_type == "sale_invoice":
detail_table = "si_detail"
if invoice_.invoice_type == "purchase_invoice":
detail_table = "pi_detail"
sq_packed = "select product_name from {} where id_invoice = %s and packed is not null order by id"
sq_unpacked = "select product_name from {} where id_invoice = %s and packed is null order by id"
if input_ == "packed":
sq = sq_packed
elif input_ == "unpacked":
sq = sq_unpacked
with conn() as cursor:
cursor.execute(sq.format(detail_table), (invoice_.id, ))
result = cursor.fetchall()
pt = PrettyTable([input_])
for a in result:
a0 = colored.stylize(
a[0], colored.fg('green')) if input_ == "packed" else a[0]
pt.add_row([a0])
print(pt)
if input_ == 'vp':
return {"arg1": "ivp", "arg2": invoice_}
if input_ == "save":
print("invoice_.gst_invoice_no: {}".format(invoice_.gst_invoice_no))
if not invoice_.gst_invoice_no or invoice_.gst_invoice_no == 'None':
invoice_.save()
print('saved')
else:
print('This is a GST Invoice.')
invoice_.gst_save()
print('saved')
return {"arg1": "continue"}
if input_ == "delete":
confirm_ = cf.prompt_(
"Are you sure you want to delete this invoice? (y/n):", ['y', 'n'],
unique_="existing")
if confirm_ == "y":
invoice_.delete_()
else:
print("You canceled. The invoice was not deleted")
return {"arg1": "back"}
if input_ == "set_ex_rates":
get_product_id = cf.psql_(
"select id from product where name = 'Extension'")
id_product = get_product_id[0][0]
owner_product = cf.owner_product_from_invoice_type_d[
invoice_.invoice_type]
id_owner = invoice_.owner.id
# sq = "select rate, gst_rate from {} where id_owner = %s and id_product in (3593, 3595, 3598, 3600, 3628, 3639, 3636, 3578, 3663)".format(owner_product)
sq = "select rate, gst_rate from {} where id_owner = %s and id_product = %s".format(
owner_product)
result = cf.psql_(sq, arg_=(invoice_.owner.id, id_product))
print(result)
confirm_ = cf.prompt_("Select type of rate to edit: ",
["gst", "non_gst"],
unique_="existing")
get_rate = cf.prompt_("Enter rate for Extension: ", [])
if confirm_ == "gst":
gst_arg = True
else:
gst_arg = False
extension.set_owner_product_rate(owner_product,
id_product,
id_owner,
get_rate,
gst_=gst_arg)
if input_ == "set_bn_rates":
get_product_id = cf.psql_(
"select id from product where name in ('Barrel Nipple 15', 'Barrel Nipple 20', 'Barrel Nipple 25', 'Barrel Nipple 32', 'Barrel Nipple 40', 'Barrel Nipple 50', 'Barrel Nipple 65', 'Barrel Nipple 80', 'Barrel Nipple 100' )"
)
print(get_product_id)
owner_product = cf.owner_product_from_invoice_type_d[
invoice_.invoice_type]
id_owner = invoice_.owner.id
get_product_id = tuple(get_product_id)
# sq = "select rate, gst_rate from {} where id_owner = %s and id_product in (3593, 3595, 3598, 3600, 3628, 3639, 3636, 3578, 3663)".format(owner_product)
sq = "select rate, gst_rate from {} where id_owner = %s and id_product in %s".format(
owner_product)
result = cf.psql_(sq, arg_=(invoice_.owner.id, get_product_id))
print(result)
product_name = "Barrel Nipple " + cf.prompt_(
"Enter Barrel Nipple Size to edit rate of: ",
['15', '20', '25', '32', '40', '50', '65', '80', '100'],
unique_="existing")
confirm_ = cf.prompt_("Select type of rate to edit: ",
["gst", "non_gst"],
unique_="existing")
get_rate = cf.prompt_("Enter rate for {}: ".format(product_name), [])
id_product = cf.psql_("select id from product where name = %s",
arg_=(product_name, ))
id_product = id_product[0][0]
if confirm_ == "gst":
gst_arg = True
else:
gst_arg = False
barrel_nipple.set_owner_product_rate(owner_product,
id_product,
id_owner,
get_rate,
gst_=gst_arg)
if input_ == "del":
result = invoice_.fetch_invoice_details()
invoice_product_name_list = [element[0] for element in result]
product_name = cf.prompt_("Edit Product: ",
invoice_product_name_list,
unique_="existing")
detail_table_id = invoice_.get_detail_table_id(product_name)
print(invoice_.invoice_type)
i_detail_ = invoice_detail.get_existing_invoice_detail_by_id(
invoice_, detail_table_id)
i_detail_.delete_()
invoice_.update_invoice_with_sub_total()
result = invoice_.fetch_invoice_details()
invoice_.view_invoice_details(result)
if input_ == "v":
# view all invoice detail items
result = invoice_.fetch_invoice_details()
invoice_.view_invoice_details(result)
if input_ == "va":
# view all invoice detail items
result = invoice_.fetch_invoice_details()
invoice_.view_invoice_details(result, all_="yes")
if input_ == "p":
invoice_.update_invoice_with_sub_total()
# create pdf of invoice
sale_report.create_(invoice_, 'A6')
if input_ == "pg":
gst_report.create_(invoice_, 'A5')
if input_ == "tg":
sale_report.create_(invoice_, 'A6', tg='Rounak')
if input_ == "tgm":
sale_report.create_(invoice_, 'A6', tg='Madan')
if input_ == "tgdetails":
with conn() as cursor:
cursor.execute(
"select product_print_name, product_qty from si_detail where id_invoice = %s",
(invoice_.id, ))
result = cursor.fetchall()
print(result)
s = ""
for a in result:
s = s + a[0] + "-> " + str(a[1]) + "\\n"
# dict_ = dict((str(x),str(y)) for x,y in result)
# print(dict_)
# for a in dict
# dict_ = 'x' + '\\n' + 'y'
cf.send_msg_telegram(s, me=True)
if input_ == "date":
invoice_.edit_property("date_")
result = invoice_.fetch_invoice_details()
invoice_.view_invoice_details(result)
if input_ == "set_owner_gst_number":
invoice_.owner.set_gst_number()
if input_ == "set_gst_name":
invoice_.owner.set_gst_name()
if input_ == "set_gst_invoice_number":
invoice_.set_gst_invoice_number()
if input_ == "cash_memo":
invoice_.set_memo_type("cash")
if input_ == "credit_memo":
invoice_.set_memo_type("credit")
if input_.startswith("e"):
result = invoice_.fetch_invoice_details()
editable_properties = {
"q": ["product_qty", "Quantity"],
"r": ["product_rate", "Rate"],
"d": ["product_discount", "Discount"],
"gn": ["product_gst_name", "GST Name"]
}
property_ = editable_properties[input_[1:]][0]
invoice_product_name_list = [element[0] for element in result]
product_name = cf.prompt_("Edit Product: ", invoice_product_name_list)
if product_name in invoice_product_name_list:
detail_table_id = invoice_.get_detail_table_id(product_name)
i_detail_ = invoice_detail.get_existing_invoice_detail_by_id(
invoice_, detail_table_id)
if invoice_.gst_invoice_no is None:
gst_arg = False
else:
gst_arg = True
i_detail_.edit_property(property_, gst_=gst_arg)
else:
cf.log_("Product Name is not in this invoice. No edits were made")
return {"arg1": "continue"}
def create_new_owner_in_db(owner_):
cf.log_("db: create_new_owner_in_db")
sq = "insert into {} (name, place, nickname, opening_balance) values (%s, %s, %s, %s) returning id"
return cf.execute_(sq, [owner_.owner_type],
arg_=[owner_.name, owner_.place, owner_.nickname, 0],
fetch_="yes")
def set_properties(self):
self.name, self.place, self.email_address, self.preferred_transport, self.note, self.address_line_one, self.address_line_two, self.address_line_three, self.contact_one, self.contact_two, self.contact_three, self.gst_number, self.nickname, self.gst_name = cf.execute_(
"select {} from {} where {} = %s",
sq_properties,
table_=self.owner_type,
where_="id",
arg_=(self.id, ),
fetch_="y")
