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")