def set_gst_number(self):
self.gst_number = cf.prompt_("Enter GST Number for {}: ".format(
self.nickname), [],
default_="27")
cf.psql_("update {} set gst_number = %s where id = %s".format(
self.owner_type),
arg_=(self.gst_number, self.id))
def update_cost_in_si_detail(invoice_detail_, product_cost):
cost_sub_total = (Decimal(product_cost) *
Decimal(invoice_detail_.product_qty)).quantize(
Decimal("1.00"))
print('cost_sub_total: {}'.format(cost_sub_total))
cf.psql_(
"update si_detail set (product_cost, cost_sub_total) = (%s, %s) where id = %s",
arg_=(product_cost, cost_sub_total, invoice_detail_.id))
def get_all_unsaved_invoices(invoice_type):
owner_type = cf.owner_type_d[invoice_type]
if invoice_type == "sale_invoice":
return cf.psql_(
"select s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where s.gst_invoice_no is null and s.id not in (select id_invoice from sale_transaction where id_invoice is not null) order by s.id desc"
.format(invoice_type, owner_type))
elif invoice_type == "purchase_invoice":
return cf.psql_(
"select s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where s.gst_invoice_no is null and s.id not in (select id_invoice from purchase_transaction where id_invoice is not null) order by s.id desc"
.format(invoice_type, owner_type))
def set_gst_name(self):
self.gst_name = cf.prompt_("Enter GST Owner Name for {}: ".format(
self.nickname), [],
default_=self.name,
empty_="yes")
if self.gst_name:
cf.psql_("update {} set gst_name = %s where id = %s".format(
self.owner_type),
arg_=(self.gst_name, self.id))
return self.gst_name
else:
return None
def makegst(self):
if self.gst_invoice_no is not None:
print('This is already a GST Invoice')
return
try:
self.gst_invoice_no = get_invoice_no(self.invoice_type, gst_=True)
cf.psql_(
"update {} set gst_invoice_no = %s where id = %s returning id".
format(self.invoice_type),
arg_=(self.gst_invoice_no, self.id))
self.gst_save()
# gst_invoice = sql.SQL("gst.") + sql.Identifier(self.invoice_type)
# public_invoice = sql.SQL("public.") + sql.Identifier(self.invoice_type)
# joined = sql.SQL(', ').join(sql.Identifier(n) for n in sq_properties)
# excl_joined = sql.SQL(',').join(sql.SQL('excluded.')+sql.Identifier(n) for n in sq_properties)
# sq = sql.SQL("insert into {} select * from {} where id = %s on conflict(id) do update set ({}) = ({})").format(gst_invoice, public_invoice, joined, excl_joined)
# cf.psql_(sq, arg_=(self.id, ))
except Exception as e:
print(e)
def update_cost_in_product(id_product, cost, final_cost):
timestamp_ = cf.get_current_timestamp()
cf.psql_(
"update product set (purchase_cost, cost, timestamp_) = (%s, %s,%s) where id = %s",
arg_=(cost, final_cost, timestamp_, id_product))
def get_previous_cost(id_product):
return cf.psql_("select cost from product where id = %s",
arg_=(id_product, ))
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 get_all_gst_invoices(invoice_type):
owner_type = cf.owner_type_d[invoice_type]
transaction_type = cf.transaction_type_d[invoice_type]
return cf.psql_(
"select s.gst_invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where s.gst_invoice_no is not null and gst_invoice_no not in (select gst_invoice_no from {} where gst_invoice_no is not null) order by s.gst_invoice_no desc nulls last"
.format(invoice_type, owner_type, transaction_type))
def set_memo_type(self, type_):
assert type_ in ["cash", "credit"]
self.memo_type = type_
cf.psql_("update {} set memo_type = %s where id = %s".format(
self.invoice_type),
arg_=(self.memo_type, self.id))
def set_gst_invoice_number(self):
self.gst_invoice_no = cf.prompt_("Enter GST Invoice No: ", [])
cf.psql_(
"update {} set gst_invoice_no = %s where id = %s returning id".
format(self.invoice_type),
arg_=(self.gst_invoice_no, self.id))