def update_opening_balance(type_):
owner_ = cf.prompt_("Enter Owner Name: ", cf.get_completer_list("nickname", type_.lower()), unique_="existing")
balance_ = cf.prompt_("Enter amount: ", [])
with conn() as cursor:
cursor.execute("update {} set opening_balance = %s where nickname = %s returning name, opening_balance".format("master."+type_), (balance_, owner_))
result = cursor.fetchall()
cf.pretty_(['name', 'balance'], result)
def get_gst_customer_balance(**kwargs):
place = kwargs.get('place', '')
if place:
with conn() as cursor:
cursor.execute(
"select name, place, sum(coalesce(invoice_amount, 0)) - sum(coalesce(money_amount,0)) + customer.gst_opening_balance as balance from gst_sale_ledger_view join customer on customer.id = gst_sale_ledger_view.id_owner where customer.place = %s group by name, place, customer.gst_opening_balance order by balance desc",
(place, ))
result = cursor.fetchall()
else:
owner_ = get_owner("customer") # owner object
id_owner = owner_.id
with conn() as cursor:
cursor.execute(
"select name, place, sum(invoice_amount) - sum(money_amount) + master.customer.opening_balance as balance from master.sale_ledger_view join master.customer on master.customer.id = master.sale_ledger_view.id_owner where master.customer.id = %s group by name, place, customer.opening_balance order by balance desc",
(id_owner, ))
result = cursor.fetchall()
columns = ['name', 'place', 'balance']
cf.pretty_(columns, result, align_right=['balance'])
right_align_columns = ['balance']
# left_align_columns = ['name', 'place']
pt = PrettyTable(columns)
# print(result)
for a in result:
if a[2] is None:
a2 = ''
else:
a2 = a[2]
pt.add_row([a[0], a[1], a2])
pt.align = 'l'
for r in right_align_columns:
pt.align[r] = 'r'
def view_summary():
with conn() as cursor:
cursor.execute(
"select name, place, sum(invoice_amount)-sum(money_amount)+master.customer.opening_balance as balance from master.sale_ledger_view join master.customer on master.customer.id = master.sale_ledger_view.id_owner group by name, place, customer.opening_balance order by balance desc"
)
result = cursor.fetchall()
columns = ['name', 'place', 'balance']
cf.pretty_(columns, result, right_align=['balance'])
# ob_list = [r[3] for r in result]
# print(ob_list)
# print("Frist OB: {}".format(result[3]))
# t = 0
# for a in ob_list:
# t = t + a
# print(t)
# import csv
# with open('total_check.csv', 'wt') as csv_file:
# writer = csv.writer(csv_file, delimiter = ',')
# for a in result:
# # csv_file.write(str(a))
# writer.writerow(a)
right_align_columns = ['balance']
# left_align_columns = ['name', 'place']
pt = PrettyTable(columns)
for a in result:
if a[2] is None:
a2 = ''
else:
a2 = a[2]
pt.add_row([a[0], a[1], a2])
pt.align = 'l'
for r in right_align_columns:
pt.align[r] = 'r'
print(pt)
def print_ledger(result, owner_type, opening_balance):
if owner_type == "customer": money = 'Receipt'
if owner_type == "vendor": money = 'Payment'
columns = ['Date', 'id_', 'Invoice', money, 'Balance']
# right_align_columns = ['id', 'Invoice', money, 'Balance']
# left_align_columns=['Date']
# pt = PrettyTable(columns)
print('OB is {}'.format(opening_balance))
new_list = []
new_list.append(['Opening', '', '', '', str(opening_balance)])
# pt.add_row(['Opening', '','','',str(opening_balance)])
# pt.set_style(PLAIN_COLUMNS)
if result:
for a in result:
a0 = cf.reverse_date(str(a[0]))
if a[1] is None:
a1 = ''
else:
a1 = a[1]
if a[2] is None:
a2 = ''
else:
a2 = a[2]
if a[3] is None:
a3 = ''
else:
a3 = a[3]
new_list.append([a0, a1, a2, a3, a[4]])
cf.pretty_(columns, new_list, align_right=range(1, 5))
def view_print(result, self=None):
# left_align = ["name"]
# right_align = ["qty", "unit", "rate","discount", "sub_total"]
# result = [a[:-2] for a in result]
# cf.pretty_(invoice_detail.detail_columns[:-1], result)
# pt = PrettyTable(invoice_detail.detail_columns)
new_list = []
for a in result:
packed_ = a[-1]
# result = a[:-2] # remove pack and print_name
a0 = a[0] if not packed_ else colored.stylize(a[0], colored.fg("11"))
a4 = '' if a[4] is None else a[4]
new_list.append([a0, a[1], a[2], a[3], a4, a[5]])
# pt.add_row([a0, a[1], a[2], a[3], a4, a[5], a[6]])
col_ = ['Name', 'Qty', 'Unit', 'Rate', 'Disc', 'Total']
# col_ = invoice_detail.detail_columns[:-1]
col_ = [colored.stylize(a, colored.fg("138")) for a in col_]
# cf.pretty_(['Date', 'No', 'Name'],((cf.reverse_date(str(self.date_)),str(self.no_), self.owner.name+" ("+self.owner.place+")"), ))
if self is not None:
header_ = (cf.reverse_date(str(self.date_)), self.owner.name + " (" +
self.owner.place + "): " + str(self.amount_after_freight))
else:
header_ = '*'
cf.pretty_(col_,
new_list,
align_right=range(1, 6),
header_=header_,
footer_=True)
def set_product_cost(a):
cost_before_discount = cf.prompt_("Enter cost for {}: ".format(a[1]), [], default_=str(a[2]), empty_="yes")
if cost_before_discount == "None":
cost_before_discount = None
if cost_before_discount:
discount = cf.prompt_("Enter discount for {}: ".format(a[1]), [], default_=str(0), empty_="yes")
if discount:
discount = float(discount)
cost = (Decimal(cost_before_discount) * Decimal(1 - discount/100)).quantize(Decimal("1.00"))
else:
cost = cost_before_discount
transport_cost = cf.prompt_("Enter Tranport Cost for {}: ".format(a[1]), [], default_=str(0), empty_="yes")
timestamp_ = cf.get_current_timestamp()
final_cost = (Decimal(cost) + Decimal(transport_cost)).quantize(Decimal("1.00"))
with conn() as cursor:
cursor.execute("update product set (purchase_cost, cost, timestamp_) = (%s, %s, %s) where id = %s returning name, cost, timestamp_", (cost, timestamp_, a[0]))
result = cursor.fetchall()
cf.pretty_(['name', 'cost', 'final_cost', 'timestamp_'], result)
#! /usr/bin/env python3
from database import Database, CursorFromConnectionFromPool as conn
import common_functions as cf
if __name__ == "__main__":
Database.initialise(database='chip', host='localhost', user='******')
while True:
place = cf.prompt_("Enter place: ",
cf.get_completer_list("place", "customer"))
if place == "q":
break
if place == "a":
with conn() as cursor:
cursor.execute("select date_, place from tour")
all_ = cursor.fetchall()
cf.pretty_(['Date', 'Place'], all_)
continue
date_ = cf.prompt_("Enter date: ", [], default_="2018-")
if date_:
with conn() as cursor:
cursor.execute(
"insert into tour (place, date_) values (%s, %s) returning date_, place",
(place, date_))
result = cursor.fetchall()
cf.pretty_(['Date', 'Place'], result)
else:
print("No changes were made")
print("Bye!")
from prompt_toolkit.styles import Style
from psycopg2 import sql
from reportlab.lib import pagesizes
from prettytable import PrettyTable
from fuzzyfinder import fuzzyfinder as ff
import common_functions as cf
import owner
import product
import sys
import os
import master
from decimal import Decimal
import required.custom_data as custom_data
a_t = ((1, 2000, 3000, 4, None, 9), (2, 3, 65, 4, 5000, 600000))
cf.pretty_(['a', 'b', 'c', 'd', 'e', 'f'], a_t, right_align=['c', 'e'])
# confirm_ = cf.prompt_("This", ['abc','acb', 'b'])
Database.initialise(database='chip', host='localhost', user='******')
with conn() as cursor:
cursor.execute("select * from {}".format("master." + "customer"))
result = cursor.fetchall()
print(result)
# invoice_type = "sale_invoice"
# saved_id_table_tuple = (24793,)
# sq = "insert into stock (id_si_detail, id_product, product_name, product_unit, qty_sale, date_) select id, id_product, product_name, product_unit, product_qty, date_ from si_detail where si_detail.id_invoice in %s"
# with conn() as cursor:
# cursor.execute(sq, (saved_id_table_tuple, ))
# master.backup(drop_=True)
# a = Decimal(5.50)
def print_data(report_headers, report_result):
cf.pretty_(report_headers, report_result)
def display_header(self):
print("{}".format(self.invoice_type))
cf.pretty_(['Date', 'No', 'Name'],
((cf.reverse_date(str(self.date_)), str(self.no_),
self.owner.name + " (" + self.owner.place + ")"), ))
