def getProductID(cls, productID, companyCode):
try:
return cls.query.filter(
ProductSellersModel.CompanyCode == companyCode).filter(
ProductSellersModel.ProductID == productID).all()
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_products_on_date(cls, date):
Session = sessionmaker(bind=db.engine)
session = Session()
try:
query1 = ProductValuationsModel.query.filter(
func.DATE(ProductValuationsModel.DateOfValuation) <=
parse_iso_date(date)).with_entities(
ProductValuationsModel.ProductID,
func.max(ProductValuationsModel.DateOfValuation).label(
"MaxDate"),
ProductValuationsModel.ProductPrice).group_by(
ProductValuationsModel.ProductID)
statement1 = query1.cte("query_1") #SELECT statement
query2 = cls.query.filter(func.DATE(cls.DateEnteredInSystem) <= parse_iso_date(date), or_(parse_iso_date(date) <= func.DATE(cls.DateDeleted), cls.DateDeleted == None), cls.ProductID == ProductSellersModel.ProductID).\
with_entities(cls.ProductID, cls.ProductName, cls.DateEnteredInSystem, ProductSellersModel.CompanyCode)
statement2 = query2.cte("query_2") #SELECT statement
query3 = session.query(statement2).filter(
statement1.c.ProductID ==
statement2.c.ProductID).with_entities(
statement2.c.ProductID, statement2.c.ProductName,
statement2.c.DateEnteredInSystem, statement2.c.CompanyCode,
statement1.c.ProductPrice)
return query3
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_all(cls, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.offset(offset).limit(limit).all()
else:
return cls.query.all()
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trade_by_product(cls, productID, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
cls.ProductID.in_(productID)).offset(offset).limit(limit)
else:
return cls.query.filter(cls.ProductID.in_(productID))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def __init__(self, **kwargs):
super(Artist, self).__init__(**kwargs)
check_genres = all(genre in GENRE_CHECK for genre in self.genres)
if not check_genres:
raise exc.ProgrammingError(
"Invalid genre",
{"Genres passed": self.genres},
{"Genres allowed": GENRE_CHECK},
)
def retrieve_currency(cls, date):
try:
return cls.query.filter(
func.DATE(cls.DateOfValuation) <= parse_iso_date(date)
).with_entities(cls.CurrencyCode,
func.max(cls.DateOfValuation).label('MaxDate'),
cls.ValueInUSD).group_by(cls.CurrencyCode)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_companies_before(cls, date):
try:
return cls.query.filter(
func.DATE(CompanyModel.DateEnteredInSystem) <=
parse_iso_date(date),
or_(
parse_iso_date(date) < cls.DateDeleted,
cls.DateDeleted == None))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_all_trade_dates(cls, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.distinct(cls.DateOfTrade).group_by(
cls.DateOfTrade).offset(offset).limit(limit)
else:
return cls.query.distinct(cls.DateOfTrade).group_by(
cls.DateOfTrade)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trade_date_by_id(cls, id, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(cls.TradeID == id).with_entities(
cls.DateOfTrade).first()
else:
return cls.query.filter(cls.TradeID == id).with_entities(
cls.DateOfTrade).first()
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_bought_by(cls, buyingParty, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
DerivativeTradesModel.BuyingParty.in_(buyingParty)).offset(
offset).limit(limit)
else:
return cls.query.filter(
DerivativeTradesModel.BuyingParty.in_(buyingParty))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_by_user(cls, userID, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
DerivativeTradesModel.UserIDCreatedBy.in_(userID)).offset(
offset).limit(limit)
else:
return cls.query.filter(
DerivativeTradesModel.UserIDCreatedBy.in_(userID))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trade_with_ID(cls, tradeID, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
DerivativeTradesModel.TradeID.in_(tradeID)).offset(
offset).limit(limit)
else:
return cls.query.filter(
DerivativeTradesModel.TradeID.in_(tradeID))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_modified_before(cls, endDate, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
func.DATE(DerivativeTradesModel.LastModifiedDate) <=
parse_iso_date(endDate)).offset(offset).limit(limit)
else:
return cls.query.filter(
func.DATE(DerivativeTradesModel.LastModifiedDate) <=
parse_iso_date(endDate))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_after(cls, startDate, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
func.DATE(DerivativeTradesModel.DateOfTrade) >=
parse_iso_date(startDate)).offset(offset).limit(limit)
else:
return cls.query.filter(
func.DATE(DerivativeTradesModel.DateOfTrade) >=
parse_iso_date(startDate))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trade_by_underlying_currency(cls, underlyingCurrency, offset,
limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
DerivativeTradesModel.UnderlyingCurrency.in_(
underlyingCurrency)).offset(offset).limit(limit)
else:
return cls.query.filter(
DerivativeTradesModel.UnderlyingCurrency.in_(
underlyingCurrency))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trade_dates_before(cls, endDate, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
parse_iso_date(endDate) >= func.DATE(cls.DateOfTrade)
).distinct(cls.DateOfTrade).group_by(
cls.DateOfTrade).offset(offset).limit(limit)
else:
return cls.query.filter(
parse_iso_date(endDate) >= func.DATE(cls.DateOfTrade)
).distinct(cls.DateOfTrade).group_by(cls.DateOfTrade)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_by_notional_currency(cls, notionalCurrency, offset,
limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
DerivativeTradesModel.NotionalCurrency.in_(
notionalCurrency)).offset(offset).limit(limit)
else:
return cls.query.filter(
DerivativeTradesModel.NotionalCurrency.in_(
notionalCurrency))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_actions_by_user(cls, id):
try:
return cls.query.filter(cls.EmployeeID == id)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_product_by_id(cls, id):
try:
return cls.query.filter(cls.ProductID == id, cls.ProductID == ProductSellersModel.ProductID).\
with_entities(cls.ProductID, ProductSellersModel.CompanyCode, cls.ProductName, cls.DateEnteredInSystem)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_all_companies(cls):
try:
return cls.query.all()
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_products(cls):
try:
return cls.query.filter(cls.ProductID == ProductSellersModel.ProductID, ProductSellersModel.ProductID == ProductValuationsModel.ProductID).\
with_entities(ProductModel.ProductID, ProductModel.ProductName, ProductSellersModel.CompanyCode, ProductValuationsModel.ProductPrice, ProductModel.DateEnteredInSystem)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_company_by_name(cls, name):
try:
return cls.query.filter(cls.CompanyName == name)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_events(cls, date):
try:
return cls.query.filter(cls.DateOfEvent <= date).with_entities(
EventLogModel.DateOfEvent, EventLogModel.UserAction)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_by_user_id(cls, id):
try:
return cls.query.filter_by(EmployeeID=id).first()
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def retrieve_company_by_code(cls, code):
try:
return cls.query.filter(cls.CompanyCode == code)
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
