def get(self):
schema = VendorSchema(many=True)
if request.args.get("entity"):
entity = Entity.get(uuid=request.args.get("entity"))
if entity:
query = entity.vendors.all()
else:
query = {}
elif request.args.get("vendor"):
vendor = Vendor.get(uuid=request.args.get("vendor"))
if vendor:
query = vendor.entities.all()
else:
query = {}
else:
from autoshop.commons.dbaccess import query
sql = """ c.vendor_id, v.uuid as vendor_uuid, v.name as vendor,
c.entity_id, e.uuid as entity_uuid, e.name as entity
FROM entity_vendors c INNER JOIN entity e on e.id=c.entity_id
INNER JOIN vendor v on v.id=c.vendor_id
"""
data = query(sql)
return jsonify(data)
# return [] if data is None else data
return schema.dump(query).data
def balance(self):
"""Get the account balance."""
try:
return query(" balance from account_balances where id=" +
str(self.id))[0]["balance"]
except Exception:
return 0
def quantity(self):
"""Get the item balance."""
try:
return query(
" quantity from item_balances where uuid='" + str(self.uuid) + "'"
)[0]["quantity"]
except Exception:
return 0
def credit_account(self):
sql = (
""" name FROM item_accounts where item_accounts.uuid ='"""
+ str(self.credit)
+ """'"""
)
data = query(sql)
return data if data is None else data[0]["name"]
def creator(self):
try:
sql = (""" first_name + ' ' + last_name as username
FROM users
where users.id = """ + str(self.created_by) + """
""")
data = query(sql)
return data if data is None else data[0]["username"]
except Exception:
return ""
def name(self):
"""
Get the limits per category..
this is the summation of all entries under a category
"""
sql = (""" name FROM accounts INNER JOIN account_holders on
account_holders.uuid=accounts.owner_id where accounts.id =
""" + str(self.id) + """""")
data = query(sql)
return data if data is None else data[0]["name"]
def company(self):
try:
if self.company_id == "system":
return "System"
else:
sql = (""" name
FROM account_holders
where uuid = '""" + self.company_id + """'
""")
data = query(sql)
return data[0]["name"]
except Exception:
return ""
def get(self):
sql = """ V.entry_id,account_id,B.uuid,H.name, V.reference, V.tran_type,V.tran_category,
V.date_created,amount, e.name as entity_id FROM account_ledgers AS V
inner join accounts AS B on B.id=V.account_id inner join account_holders H on
B.owner_id=H.uuid inner join entity e on e.uuid=V.entity_id """
if request.args.get("company") is not None:
where = " where entity_id='" + request.args.get("company") + "'"
sql = sql + where
order_by = " order by V.date_created desc, V.entry_id desc, V.amount desc"
sql = sql + order_by
response = query(sql)
return response, 200
def get(self, account_id):
sql = (""" 0 as entry_id,'' as tran_type,'credit' as tran_category,
(select date_created from accounts where id=""" +
str(account_id) + """)
as date_created,0 as amount,0 as balance
union
select entry_id,tran_type,tran_category,date_created,amount,
(select sum(amount) from account_ledgers b where account_id="""
+ str(account_id) + """
and b.entry_id<=a.entry_id ) as balance from account_ledgers a
where account_id=""" + str(account_id) + """
order by entry_id desc""")
response = query(sql)
return response, 200
def get(self):
sql = """ V.entry_id,account_id,B.uuid,B.name, V.reference,
V.date_created,quantity,amount, e.name as entity_id FROM item_ledger AS V
inner join item_accounts AS B on B.uuid=V.account_id
inner join item I on B.uuid=I.uuid
inner join entity e on e.uuid=I.entity_id """
if request.args.get("company") is not None:
where = " where entity_id='" + request.args.get("company") + "'"
sql = sql + where
order_by = " order by V.date_created desc, V.entry_id desc, V.amount desc"
sql = sql + order_by
response = query(sql)
return response, 200
def no_of_wallets(self):
"""
Get the limits per category..
this is the summation of all entries under a category
"""
sql = (""" accounts.id,account_ledgers.category,category.name,
COALESCE(sum(account_ledgers.amount), 0.0) as balance FROM accounts
LEFT OUTER JOIN account_ledgers ON accounts.id = account_ledgers.account_id
INNER JOIN category on category.uuid=account_ledgers.category
where accounts.id = """ + str(self.id) + """
GROUP BY accounts.id,account_ledgers.category,category.name""")
wallets = query(sql)
if wallets is None:
return 0
return len(wallets)
def get(self, item_id):
sql = (""" 0 as entry_id,'' as tran_type,'credit' as tran_category,
(select date_created from item where uuid='""" + str(item_id) +
"""')
as date_created,0 as quantity,0 as balance
union
select entry_id,'' as tran_type,tran_category,date_created,quantity,
(select sum(quantity) from item_ledger b where account_id='"""
+ str(item_id) + """'
and b.entry_id<=a.entry_id ) as balance from item_ledger a
where account_id='""" + str(item_id) + """'
order by entry_id desc""")
app.logger.info(sql)
response = query(sql)
return response, 200
def wallets(self):
"""
Get the limits per category..
this is the summation of all entries under a category
"""
sql = (
""" accounts.id,account_ledgers.category,vehicle.registration_no,
COALESCE(sum(account_ledgers.amount), 0.0) as balance FROM accounts
LEFT OUTER JOIN account_ledgers ON accounts.id = account_ledgers.account_id
INNER JOIN vehicle on vehicle.uuid=account_ledgers.category where
acc_type='customer' and accounts.id = """ + str(self.id) + """
GROUP BY accounts.id,account_ledgers.category,vehicle.registration_no """
)
wallets = query(sql)
if not wallets:
return []
return wallets
def get(self):
sql = request.args.get("sql")
response = query(sql)
return response, 200
