def __init__(self, invoice_type, **kwargs): assert invoice_type in ["receipt", "payment"] self.invoice_type = invoice_type # receipt | payment self.owner_type = cf.owner_type_d[ self.invoice_type] # customer | vendor self.id = kwargs.get('id_', '') if self.id: self.get_invoice_properties(**kwargs) print("Invoice exists and id is {}".format(self.id)) else: self.gst_invoice_no = None owner_nickname = kwargs.get('nickname', '') if not owner_nickname: owner_nickname = self.ask_owner_nickname() if owner_nickname: id_ = owner.get_id_from_nickname(self.owner_type, owner_nickname) self.owner = owner.get_existing_owner_by_id(self.owner_type, id_) if self.owner: self.id_owner = self.owner.id self.amount = self.ask_amount() if self.amount: self.date_ = get_date() self.recipient, self.medium, self.detail = self.get_detail( ) if self.recipient: self.id = self.create_new_invoice() make_gst_confirm = cf.prompt_("Make: ", ['y', 'n'], unique_="existing", empty_="yes") if make_gst_confirm == "y": self.makegst() # self.save() cf.log_("Finished Money __init__")
def edit_property(self, property_, **kwargs): if property_ in ["packed", "unpack"]: old_value = getattr(self, property_) if old_value: new_value = None setattr(self, property_, new_value) else: new_value = "yes" setattr(self, property_, new_value) cf.cursor_(sql.SQL( "update {} set {} = %s where id = %s returning id").format( sql.Identifier(self.invoice_detail_type), sql.Identifier(property_)), arguments=(new_value, self.id)) return if property_ in ["id", "product_gst_rate"]: cf.log_("You cannot change 'id' value") return None old_value = getattr(self, property_) new_value = cf.prompt_( "Enter new {} [{}] for {}: ".format(property_, old_value, self.product_name), cf.get_completer_list(property_, self.invoice_detail_type)) if new_value == "quit": return "quit" setattr(self, property_, new_value) cf.cursor_( sql.SQL("update {} set {} = %s where id = %s returning id").format( sql.Identifier(self.invoice_detail_type), sql.Identifier(property_)), arguments=(new_value, self.id)) if property_ in ['product_gst_name']: confirm_ = cf.prompt_( "Do you want to update the name in Product table?: ", ['y', 'n'], default_='y', unique_='existing') if confirm_ == 'y': with conn() as cursor: cursor.execute( "update product set gst_name = %s where id = %s", (new_value, self.product_id)) if property_ in ["product_rate", "product_qty", "product_discount"]: sub_total = self.get_sub_total(property_=property_, property_value=Decimal(new_value)) gst_amount = (Decimal(sub_total) * Decimal(self.product_gst_rate) * Decimal(0.01)).quantize(Decimal("1.00")) with conn() as cursor: cursor.execute( sql.SQL( "update {} set ({}, sub_total, gst_amount) = (%s, %s, %s) where id = %s" ).format(sql.Identifier(self.invoice_detail_type), sql.Identifier(property_)), (new_value, sub_total, gst_amount, self.id)) setattr(self, "sub_total", sub_total) owner_product = cf.owner_product_from_invoice_type_d[ self.invoice_.invoice_type] self.invoice_.update_invoice_with_sub_total() self.update_owner_product(owner_product, self.product_rate, **kwargs) self.view_()
def update_owner_product(self, owner_product, rate, **kwargs): gst_ = kwargs.get('gst_', '') previous_rate = get_previous_rate(self.invoice_.owner.id, owner_product, self.product_id, **kwargs) rate_date = str(datetime.datetime.today()) if previous_rate == rate: with conn() as cursor: cursor.execute( sql.SQL( "update {} set (timestamp_) = (%s) where id_owner = %s and id_product = %s and rate = %s" ).format(sql.Identifier(owner_product)), (rate_date, self.invoice_.owner.id, self.product_id, rate)) cf.log_("Updated customer product timestamp_") else: if gst_: with conn() as cursor: cursor.execute( sql.SQL( "insert into {} (gst_rate, timestamp_, id_owner, id_product) values (%s, %s, %s, %s)" ).format(sql.Identifier(owner_product)), (rate, rate_date, self.invoice_.owner.id, self.product_id)) else: with conn() as cursor: cursor.execute( sql.SQL( "insert into {} (rate, timestamp_, id_owner, id_product) values (%s, %s, %s, %s)" ).format(sql.Identifier(owner_product)), (rate, rate_date, self.invoice_.owner.id, self.product_id)) cf.log_("Inserted customer product rate and timestamp_")
def get_invoice_properties(self, **kwargs): master_ = kwargs.get("master_", '') invoice_type = sql.Identifier(self.invoice_type) if master_: cf.log_("reached master_") if self.invoice_type == "receipt": invoice_type = sql.SQL("master.") + sql.Identifier("receipt") if self.invoice_type == "payment": invoice_type = sql.SQL("master.") + sql.Identifier("payment") with conn() as cursor: cursor.execute( sql.SQL("select {} from {} where id = %s").format( sql.SQL(', ').join(map(sql.Identifier, sq_properties)), invoice_type), (self.id, )) self.invoice_properties = cursor.fetchone() # self.invoice_properties = cf.execute_("select {} from {} where {} = %s", sq_properties, table_=invoice_type, where_="id", arg_=(self.id, ), fetch_="y") #print("invoice_properties: {}".format(self.invoice_properties)) self.id_owner = self.invoice_properties[0] self.date_ = self.invoice_properties[1] self.amount = self.invoice_properties[2] self.type = self.invoice_properties[3] self.recipient = self.invoice_properties[4] self.detail = self.invoice_properties[5] self.place = self.invoice_properties[6] self.invoice_no = self.invoice_properties[7] self.id_transaction = self.invoice_properties[8] self.gst_invoice_no = self.invoice_properties[9] self.owner = owner.get_existing_owner_by_id(self.owner_type, self.id_owner) cf.log_("Finished Money.get_invoice_properties()")
def create_new_invoice_detail_in_db(invoice_detail_): invoice_detail_.gst_amount = (Decimal(invoice_detail_.sub_total) * Decimal(invoice_detail_.product_gst_rate) * Decimal(0.01)).quantize(Decimal("1.00")) cf.log_("db: create_new_invoice_detail_in_db") sq = "insert into {} (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_discount, product_hsn, product_gst_rate, sub_total, product_print_name, packed, gst_amount, product_gst_name) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) returning id".format( invoice_detail_.invoice_detail_type) # sq = sql.SQL("insert into {} ({}) values ({}) returning id").format(sql.Identifier(invoice_detail_.invoice_detail_type), sql.SQL(', ').join(map(sql.Identifier, properties)), sql.SQL(', ').join(sql.Placeholder() * len(properties))) with conn() as cursor: cursor.execute( sq, ( invoice_detail_.invoice_.id, # 0 invoice_detail_.product_id, # 1 invoice_detail_.product_name, # 2 invoice_detail_.product_qty, # 3 invoice_detail_.product_unit, # 4 invoice_detail_.product_rate, # 5 invoice_detail_.product_discount, # 6 invoice_detail_.product_hsn, # 7 invoice_detail_.product_gst_rate, #8 invoice_detail_.sub_total, # 9 invoice_detail_.product_print_name, # 10 invoice_detail_.packed, # 11 invoice_detail_.gst_amount, invoice_detail_.product_gst_name)) return cursor.fetchall()[0]
def delete_(self): with conn() as cursor: sq = sql.SQL( "with deleted as (delete from {} where id = %s returning *) select count(*) from deleted" ).format(sql.Identifier(self.invoice_detail_type)) cursor.execute(sq, (self.id, )) result = cursor.fetchone() cf.log_("No of deletions: {}".format(result[0]))
def get_last_invoice_id(invoice_type): with conn() as cursor: cursor.execute( sql.SQL("select max(id) from {}").format( sql.Identifier(invoice_type)), ()) last_invoice_id = cursor.fetchone()[0] cf.log_("last invoice id is {}".format(last_invoice_id)) return last_invoice_id
def get_invoice_properties(invoice_type, id_, **kwargs): cf.log_('get_invoice_properties') master_ = kwargs.get('master_', '') if master_: invoice_table = sql.SQL("master.") + sql.Identifier(invoice_type) else: invoice_table = sql.Identifier(invoice_type) return get_invoice_properties_from_db(invoice_table, id_)
def get_invoice_detail_properties_from_db(invoice_detail_): cf.log_("db: get_invoice_detail_properties_from_db") with conn() as cursor: cursor.execute( sql.SQL("select {} from {} where id = %s").format( sql.SQL(', ').join(map(sql.Identifier, properties)), sql.Identifier(invoice_detail_.invoice_detail_type)), (invoice_detail_.id, )) return cursor.fetchone()
def view(invoice_type, **kwargs): filter_type = kwargs.get('filter_type', '') if not filter_type: filter_type = get_invoice_filter_type() filter_result = get_filter_result(filter_type, invoice_type) cf.log_(filter_result) selected_invoice = get_selected_invoice(filter_result, invoice_type) if selected_invoice in ["back"]: return "back" return selected_invoice
def create_new_invoice_in_db(invoice_): cf.log_("db: create_new_invoice_in_db") sq = "insert into {} (invoice_no, id_owner, owner_name, owner_place, date_, gst_owner_name) values (%s, %s, %s, %s, %s, %s) returning id".format( invoice_.invoice_type) with conn() as cursor: cursor.execute( sq, (invoice_.no_, invoice_.owner.id, invoice_.owner.name, invoice_.owner.place, invoice_.date_, invoice_.gst_owner_name)) return cursor.fetchone()[0]
def get_invoice_properties_from_db(invoice_table, id_): cf.log_('get_invoice_properties_from_db') sq = "select {} from {} where {} = %s" csq = sql.SQL(sq).format( sql.SQL(', ').join(map(sql.Identifier, sq_properties[1:][:-1])), invoice_table, sql.Identifier("id")) cf.log_("db: get_invoice_properties_from_db") with conn() as cursor: cursor.execute(csq, (id_, )) return cursor.fetchone()
def set_amount_before_freight(self): self.amount_before_freight = self.get_amount_before_freight() try: with conn() as cursor: cursor.execute( sql.SQL( "update {} set (amount_before_freight) = (%s) where id = %s" ).format(sql.Identifier(self.invoice_type)), (self.amount_before_freight, self.id)) except Exception as e: cf.log_(e)
def get_amount_before_freight(self): with conn() as cursor: cursor.execute( sql.SQL( "select Round(sum(sub_total),2) from {} where id_invoice = %s" ).format(sql.Identifier(self.detail_table)), (self.id, )) result = cursor.fetchone() cf.log_(result) if not result: return 0 return Decimal(result[0]).quantize(Decimal("1.00"))
def set_properties(self): try: self.id, self.name, self.unit, self.print_name, self.abbr_name, self.product_group, self.hsn, self.gst_rate, self.gst_name = get_product_details( self.name) except TypeError: result = create_product(self.name) if result[0] == "quit": cf.log_("No new product was created") return self.id, self.name, self.unit, self.print_name, self.abbr_name, self.product_group, self.hsn, self.gst_rate, self.gst_name = get_product_details( name)
def get_date(invoice_type): if invoice_type == "purchase_invoice": date_ = cf.prompt_date("Enter Date: ", default_=cf.get_current_date_two()) if date_ in ["quit", "back"]: print("Current date has been set as invoice date") return cf.get_current_date_two() return date_ elif invoice_type == "sale_invoice": cf.log_("Current date has been set as invoice date") return cf.get_current_date_two()
def get_command(invoice_): # invoice_result = owner.get_filter_result("Unsaved Invoices", "sale_invoice") if invoice_.invoice_type == "sale_invoice": invoice_list = owner.get_all_gst_invoices("sale_invoice") estimate_list = owner.get_all_unsaved_invoices("sale_invoice") elif invoice_.invoice_type == "purchase_invoice": invoice_list = owner.get_all_gst_invoices("purchase_invoice") estimate_list = owner.get_all_unsaved_invoices("purchase_invoice") invoice_dict = {} estimate_dict = {} for a in invoice_list: invoice_dict[str(a[4])] = "{}, {}, {}, {}".format( str(a[0]), str(a[2]), str(a[3]), str(a[1])) for a in estimate_list: estimate_dict[str(a[3])] = "{}, {}, {}".format(str(a[1]), str(a[2]), str(a[0])) invoice_list = [ str(a[4]) for a in invoice_list ] # do not unpack dict with * to get this list because dictionary is always unsorted estimate_list = [ str(a[3]) for a in estimate_list ] # do not unpack dict with * to get this list because dictionary is always unsorted # invoice_result = owner.get_all_unsaved_invoices(invoice_.invoice_type) # invoice_list = [str(a[3]) for a in invoice_result] # invoice_dict = {} # for a in invoice_result: # invoice_dict[str(a[3])] = "{}, {}, {}".format(str(a[0]), str(a[1]), str(a[2])) cf.log_("inside get command") owner_product = cf.owner_product_from_invoice_type_d[invoice_.invoice_type] owner_product_list, owner_product_dict = product.get_owner_product_dict( owner_product, invoice_.owner.id) # owner_product_abbr_list, owner_product_abbr_dict = product.get_owner_product_abbr_dict(owner_product, invoice_.owner.id) owner_nickname_list = cf.get_completer_list("nickname", invoice_.owner_type) # invoice_command_list = owner_product_list + owner_product_abbr_list + owner_nickname_list invoice_command_list = owner_product_list + owner_nickname_list + starting_list + startswith_list + [ 'b', 'q' ] # creating dict with None values owner_nickname_dict = dict.fromkeys(owner_nickname_list) # invoice_command_dict = {**owner_product_dict, **owner_product_abbr_dict, **owner_nickname_dict} invoice_command_dict = {**owner_product_dict, **owner_nickname_dict} return cf.prompt_dict("Enter {} command:".format(invoice_.invoice_type), invoice_command_dict, list_=invoice_command_list, invoice_list=invoice_list, invoice_dict=invoice_dict, estimate_list=estimate_list, estimate_dict=estimate_dict)
def get_owner_last_invoice_id(invoice_type, id_owner, **kwargs): master_ = kwargs.get('master_', '') if master_: invoice_type = sql.SQL("master.") + sql.Identifier(invoice_type) else: invoice_type = sql.Identifier(invoice_type) with conn() as cursor: cursor.execute( sql.SQL("select max(id) from {} where id_owner = %s").format( invoice_type), (id_owner, )) last_invoice_id = cursor.fetchone()[0] cf.log_("owner last invoice id is {}".format(last_invoice_id)) return last_invoice_id
def fetch_old_value(self, property_, product_name): sq = sql.SQL( "select id, {}, qty from {} where product_name = %s and id_invoice = %s" ).format(sql.Identifier(property_), sql.Identifier(self.detail_table)) with conn() as cursor: cursor.execute(sq, (product_name, self.id)) result = cursor.fetchall() if len(result) > 1: cf.log_( "There are multiple entries of {} in the current invoice". format(product_name)) else: return result[0][0], result[0][1]
def edit_property(self, property_): if property_ == "id": cf.log_("You cannot change 'id' value") return None old_value = getattr(self, property_) new_value = cf.prompt_("Enter new {} [{}] : ".format( property_, old_value), [], default_=str(old_value)) setattr(self, property_, new_value) cf.cursor_( sql.SQL("update {} set {} = %s where id = %s returning id").format( sql.Identifier(self.invoice_type), sql.Identifier(property_)), arguments=(new_value, self.id))
def gst_save(self): with conn() as cursor: # excl_amount not required now since this method is only called before export. It was required earlier because it was called during receipt/payment creation excl_amount = sql.Composed( [sql.SQL('excluded.'), sql.Identifier('amount')]) sq = sql.SQL( "insert into {} (type, id_voucher,id_owner, date_, amount, gst_invoice_no) values (%s, %s, %s, %s, %s, %s) on conflict (id_voucher) do update set amount = {} returning id" ).format(sql.Identifier(transaction_type[self.invoice_type]), excl_amount) cursor.execute(sq, (self.invoice_type, self.id, self.id_owner, self.date_, self.amount, self.gst_invoice_no)) result = cursor.fetchone() cf.log_("Money Transaction saved")
def get_gst_amount(self, gst_rate): with conn() as cursor: cursor.execute( sql.SQL( "select sum(gst_amount) from {} where id_invoice = %s and product_gst_rate = %s" ).format(sql.Identifier(self.detail_table)), (self.id, gst_rate)) result = cursor.fetchone() if not result[0]: # print('there is no result') cf.log_('get_gst_amount_result is {}'.format(result)) return 0 return Decimal(result[0]).quantize(Decimal("1.00"))
def fetch_invoice_details(self, **kwargs): master_ = kwargs.get("master_", '') if master_: cf.log_("reached master_") invoice_type = sql.SQL("master.") + sql.Identifier( self.invoice_type) else: invoice_type = sql.Identifier(self.invoice_type) with conn() as cursor: cursor.execute( sql.SQL( "select r.date_, c.name, r.amount, r.medium, r.recipient, r.detail from {} as r join {} as c on c.id = r.id_owner where r.id = %s" ).format(invoice_type, sql.Identifier(self.owner_type)), (self.id, )) result = cursor.fetchall() return result
def sandbox(id_owner, owner_product): # TODO add feature to modify or add new rates result = cf.cursor_(sql.SQL( "select distinct p.name from product as p join {} as op on op.id_product = p.id where op.id_owner = %s" ).format(sql.Identifier(owner_product)), arguments=(id_owner, )) product_custom_owner_list = [element[0] for element in result] while True: product_name = cf.prompt_("Enter Product Name: ", product_custom_owner_list, unique_="existing") if product_name == "quit": break if product_name == "back": break product_id = product.get_id_from_name(product_name) rate, discount = invoice_detail.get_previous_rate_discount( id_owner, owner_product, product_id) cf.log_("Rate: {}\nDiscount: {}".format(rate, discount))
def get_pricelist_discount(invoice_, id_pricelist, id_product, **kwargs): big_pricelist_id = get_id_pricelist_by_name("GI Fitting Big") reducing_pricelist_id = get_id_pricelist_by_name("GI Fitting Reducing") elbow_id = get_id_pricelist_by_name("GI Fitting Elbow") id_owner = invoice_.owner.id invoice_type = invoice_.invoice_type owner_pricelist = cf.owner_pricelist_from_invoice_type_d[invoice_type] condition = get_old_pricelist_condition(owner_pricelist, id_owner, id_pricelist) cf.log_("finished get_old_pricelist_condition.\nCondition is {}".format( condition)) pricelist_discount = get_old_pricelist_discount(owner_pricelist, id_owner, id_pricelist, **kwargs) if not pricelist_discount: pricelist_discount = cf.prompt_("Enter Discount: ", []) set_pricelist_discount(owner_pricelist, id_owner, id_pricelist, pricelist_discount, **kwargs) if id_pricelist == reducing_pricelist_id: print("Also updating discount of GI Fitting Big pricelist...") set_pricelist_discount(owner_pricelist, id_owner, big_pricelist_id, pricelist_discount, **kwargs) if id_pricelist == big_pricelist_id: print("Also updating discount of GI Fitting Reducing pricelist...") condition = get_old_pricelist_condition(owner_pricelist, id_owner, reducing_pricelist_id, **kwargs) set_pricelist_condition(owner_pricelist, id_owner, id_pricelist, condition) set_pricelist_discount(owner_pricelist, id_owner, reducing_pricelist_id, pricelist_discount, **kwargs) if condition: if condition == "non_reducing": product_name = product.get_product_name_from_id(id_product) product_name_for_condition = ( product_name.split("Red. ")[1]).split(" X ")[0] id_product = product.get_product_details( product_name_for_condition)[0] print("Product Name For Condition is {} and its id is {}".format( product_name_for_condition, id_product)) pricelist_value = get_pricelist_value(id_product) if id_pricelist == elbow_id and pricelist_discount < 15: pricelist_value = pricelist_discount pricelist_discount = 0 return pricelist_value, pricelist_discount
def save(self): self.validate_before_save() transaction_type = { "sale_invoice": "sale_transaction", "purchase_invoice": "purchase_transaction" } with conn() as cursor: joined = sql.Composed( [sql.SQL('excluded.'), sql.Identifier('amount')]) sq = sql.SQL( "insert into {} (type, id_invoice,id_owner, date_, amount) values (%s, %s, %s, %s, %s) on conflict (id_invoice) do update set amount = {} returning id" ).format(sql.Identifier(transaction_type[self.invoice_type]), joined) cursor.execute(sq, (self.invoice_type, self.id, self.owner.id, self.date_, self.amount_after_freight)) result = cursor.fetchone() cf.log_(result)
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 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 get_old_pricelist_condition(owner_pricelist, id_owner, id_pricelist): cf.log_("inside get_old_pricelist_condition") reducing_pricelist_id = get_id_pricelist_by_name("GI Fitting Reducing") if id_pricelist == reducing_pricelist_id: result = cf.cursor_(sql.SQL( "select condition from {} where id_owner = %s and id_pricelist = %s" ).format(sql.Identifier(owner_pricelist)), arguments=(id_owner, id_pricelist)) if result: if result[0][0] in ['reducing', 'non_reducing']: return result[0][0] condition = cf.prompt_("Enter Condition: ", ['reducing', 'non_reducing'], unique_="existing") if condition: set_pricelist_condition(owner_pricelist, id_owner, id_pricelist, condition) return condition return None
def fetch_invoice_details_gst(self, **kwargs): master_ = kwargs.get("master_", "") if master_: cf.log_("reached master_") if self.invoice_type == "sale_invoice": detail_table = sql.SQL("master.") + sql.Identifier("si_detail") elif self.invoice_type == "purchase_invoice": detail_table = sql.SQL("master.") + sql.Identifier("pi_detail") else: # self.set_amount_before_freight() # self.set_amount_after_freight() detail_table = sql.Identifier(self.detail_table) with conn() as cursor: cursor.execute( sql.SQL( "select product_name, product_qty, product_unit, product_rate, product_discount, sub_total, product_print_name, packed, product_hsn, product_gst_rate, gst_amount, product_gst_name from {} where id_invoice = %s order by id" ).format(detail_table), (self.id, )) result = cursor.fetchall() cf.log_("fetch_invoice_details completed") return result