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