def find_by_name(site, name, cached=True):
if cached:
return Session.query(Content)\
.options(FromCache('Content.find_by_name', "%s/%s" % (site.site_id, name)))\
.filter(and_(Content.site == site,
Content.name == name,
Content.delete_dt == None)).first()
else:
return Session.query(Content)\
.filter(and_(Content.site == site,
Content.name == name,
Content.delete_dt == None)).first()
def invalidate(obj, region, cache_key=None):
""" KB: [2011-02-07]:
def invalidate_caches(self, **kwargs):
invalidate(self, 'Product.find_all', BaseModel.get_enterprise_id())
invalidate(self, 'ProductChild.find_children', self.product_id)
if kwargs and 'campaign_id' in kwargs:
campaign_id = kwargs['campaign_id']
invalidate(self, 'Product.Campaign_Featured', campaign_id)
invalidate(self, 'Product.Campaign_Specials', campaign_id)
invalidate(self, 'Product.Campaign', campaign_id)
"""
from pvscore.model.meta import Session
Session.query(obj.__class__).options(FromCache(region, cache_key)).invalidate()
def find_payments_by_order(order):
return Session.query(Journal).filter(and_(Journal.customer==order.customer,
Journal.order==order,
Journal.delete_dt == None,
or_(Journal.type=='PartialPayment',
Journal.type=='FullPayment')))\
.order_by(Journal.create_dt.desc()).all()
def find_by_user(user):
return (
Session.query(Appointment)
.filter(or_(Appointment.creator == user, Appointment.assigned == user))
.order_by(Appointment.start_dt.asc(), Appointment.start_time.asc())
.all()
)
def find_by_customer(customer):
return (
Session.query(Appointment)
.filter(Appointment.customer == customer)
.order_by(Appointment.start_dt.desc(), Appointment.start_time.asc())
.all()
)
def find(attr, fk_id):
#pylint: disable-msg=E1101
#TODO: Fix attribute value caching: .options(FromCache('AttributeValue.find.%s.%s' % (attr.attr_id, fk_id)))\
return Session.query(AttributeValue)\
.filter(and_(AttributeValue.fk_type == attr.fk_type,
AttributeValue.fk_id == fk_id,
AttributeValue.attr_id == attr.attr_id)).first()
def find_all(enterprise_id):
#pylint: disable-msg=E1101
return Session.query(Campaign) \
.options(FromCache('Campaign.find_all', enterprise_id)) \
.join((Company, Campaign.company_id == Company.company_id)).filter(and_(Campaign.delete_dt == None,
Company.enterprise_id == enterprise_id)) \
.order_by(Company.default_campaign_id.desc(), Campaign.name).all()
def find_all(enterprise_id):
return Session.query(Vendor).options(FromCache('Vendor.find_all', enterprise_id)) \
.filter(and_(Vendor.delete_dt == None,
Vendor.enterprise_id == enterprise_id
)) \
.order_by(Vendor.name) \
.all()
def find(email, campaign):
""" KB: [2010-12-15]: Find another customer that is in the same company. """
from pvscore.model.crm.campaign import Campaign
return Session.query(Customer).join((Campaign, Campaign.campaign_id == Customer.campaign_id)) \
.filter(and_(Customer.delete_dt == None,
Campaign.company_id == campaign.company_id,
Customer.email.ilike(email))).first()
def find_by_customer(customer, order_id): # =None, start_dt=None, end_dt=None):
if order_id:
return (
Session.query(CustomerOrder)
.filter(and_(CustomerOrder.customer == customer, CustomerOrder.order_id == order_id))
.first()
)
def find_all_applicable(enterprise_id, obj):
# KB: [2010-11-29]: Eventually this will get more complex and base its
# behavior off the current state of the customer.
return Session.query(StatusEvent)\
.filter(and_(or_(StatusEvent.enterprise_id == enterprise_id, StatusEvent.enterprise_id == None),
StatusEvent.is_system == False,
StatusEvent.event_type == type(obj).__name__))\
.order_by(StatusEvent.short_name, StatusEvent.event_type).all()
def find_for_object(obj):
fk_type = type(obj).__name__
fk_id = getattr(obj, obj.__pk__)
#pylint: disable-msg=E1101
return Session.query(Asset) \
.options(FromCache('Asset.find_for_object', '%s/%s' % (fk_type, fk_id))) \
.filter(and_(Asset.fk_type == fk_type,
Asset.fk_id == fk_id)).all()
def find_all_active(enterprise_id, web_enabled=True):
return Session.query(Discount) \
.filter(and_(Discount.delete_dt == None,
Discount.enterprise_id == enterprise_id,
Discount.web_enabled == web_enabled,
or_(Discount.end_dt == None,
Discount.end_dt >= util.now())))\
.order_by(Discount.name) \
.all()
def find_by_code(enterprise_id, code):
return Session.query(Discount)\
.filter(and_(Discount.enterprise_id == enterprise_id,
Discount.delete_dt == None,
Discount.code.ilike(code),
or_(Discount.end_dt == None,
Discount.end_dt >= util.now())))\
.order_by(Discount.create_dt) \
.first()
def find_by_product(product):
return Session.query(Discount)\
.join((DiscountProduct, DiscountProduct.discount_id == Discount.discount_id))\
.filter(and_(DiscountProduct.product == product,
Discount.delete_dt == None,
or_(Discount.end_dt == None,
Discount.end_dt > util.today())))\
.order_by(Discount.create_dt.desc())\
.first()
def find_by_product(product):
return Session.query(PurchaseOrderItem)\
.join((PurchaseOrder, PurchaseOrder.purchase_order_id == PurchaseOrderItem.purchase_order_id)) \
.filter(and_(PurchaseOrder.delete_dt == None,
PurchaseOrderItem.delete_dt == None,
PurchaseOrderItem.product == product
)) \
.order_by(PurchaseOrder.create_dt.desc()) \
.all()
def find_all_open(enterprise_id):
return Session.query(PurchaseOrder)\
.options(FromCache('PurchaseOrder.find_all_open', enterprise_id)) \
.join((Company, PurchaseOrder.company_id == Company.company_id)) \
.filter(and_(PurchaseOrder.delete_dt == None,
Company.enterprise_id == enterprise_id
)) \
.order_by(PurchaseOrder.purchase_order_id.desc()) \
.all()
def search(name):
n_clause = ''
if name:
n_clause = "and com.name like '%s%%'" % name
sql = """SELECT com.* FROM crm_company com
where 1=1
{n}
""".format(n=n_clause)
return Session.query(Company).from_statement(sql).all()
def find_products(discount_id):
from pvscore.model.crm.product import Product
#.options(FromCache('Product.find_children', parent_id))
return Session.query(DiscountProduct) \
.join((Product, DiscountProduct.product_id == Product.product_id)) \
.filter(and_(DiscountProduct.discount_id == discount_id,
Product.delete_dt == None,
Product.enabled == True,
Product.type != 'Attr')) \
.order_by(Product.name) \
.all()
def find_all_automatic_cart_discounts(enterprise_id, web_enabled=True): #pylint: disable-msg=C0103
return Session.query(Discount) \
.filter(and_(Discount.delete_dt == None,
Discount.enterprise_id == enterprise_id,
Discount.cart_discount == True,
Discount.automatic == True,
Discount.web_enabled == web_enabled,
or_(Discount.end_dt == None,
Discount.end_dt >= util.now())))\
.order_by(Discount.name) \
.all()
def find_future_by_user(user):
return (
Session.query(Appointment)
.filter(
and_(
Appointment.start_dt > util.yesterday(),
or_(Appointment.creator == user, Appointment.assigned == user),
)
)
.order_by(Appointment.start_dt.asc(), Appointment.start_time.asc())
.all()
)
def find_by_company(name, company):
# if user_sendable_only:
# return Session.query(Communication) \
# .filter(and_(Communication.delete_dt == None,
# Communication.name == name,
# Communication.user_sendable == True,
# Communication.enterprise_id == company.enterprise_id)).order_by(Communication.name).first()
# else:
return Session.query(Communication) \
.filter(and_(Communication.delete_dt == None,
Communication.name == name,
Communication.enterprise_id == str(company.enterprise_id))).order_by(Communication.name).first()
def authenticate(username, pwd, company):
""" KB: [2010-10-05]: See if there is a user that matches the UID and password supplied """
val = Session.query('cnt')\
.from_statement("""select count(0) cnt
from crm_customer c, crm_campaign cmp, crm_company comp
where lower(c.email) = lower(:email)
and c.password = :pwd
and c.campaign_id = cmp.campaign_id
and cmp.company_id = comp.company_id
and comp.company_id = :company_id""")\
.params(email=username, pwd=pwd, company_id=company.company_id).first()
return val and len(val) == 1 and val[0] == 1
def find_all(obj):
#pylint: disable-msg=E1101
fk_type = type(obj).__name__
fk_id = getattr(obj, obj.__pk__)
if fk_id:
# TODO: Fix attribute value caching: .options(FromCache('AttributeValue.%s.%s' % (fk_type, fk_id))) \
return Session.query(AttributeValue).join((Attribute, AttributeValue.attr_id == Attribute.attr_id)) \
.options(joinedload('attribute')) \
.filter(and_(AttributeValue.fk_type == fk_type,
AttributeValue.fk_id == fk_id)) \
.order_by(AttributeValue.attr_value_id).all()
return []
def search(enterprise_id, name, company_id):
n_clause = cid_clause = ''
if name:
n_clause = "and cam.name like '%s%%'" % name
if company_id:
cid_clause = "and cam.company_id = '%s'" % company_id
sql = """SELECT cam.* FROM crm_campaign cam, crm_company com
where cam.company_id = com.company_id
and com.enterprise_id = '{ent_id}'
{n} {cid}
""".format(n=n_clause, cid=cid_clause, ent_id=enterprise_id)
return Session.query(Campaign).from_statement(sql).all() #pylint: disable-msg=E1101
def find_by_month(year, month, user):
return (
Session.query(Appointment)
.from_statement(
"""select * from crm_appointment where
(user_created = :creator or user_assigned = :creator)
and date_part('month', start_dt) = :month
and date_part('year', start_dt) = :year
order by start_time asc"""
)
.params(creator=user.user_id, year=year, month=month)
.all()
)
def search(enterprise_id, title, description):
t_clause = d_clause = ""
if title:
t_clause = "and appt.title like '%%%s%%'" % title
if description:
d_clause = "and appt.description like '%%%s%%'" % description
sql = """SELECT appt.* FROM crm_appointment appt, core_user u where
u.user_id = appt.user_created
and (u.enterprise_id = '{entid}' or u.enterprise_id is null)
{title} {descr}""".format(
entid=enterprise_id, title=t_clause, descr=d_clause
)
return Session.query(Appointment).from_statement(sql).all()
def find_public(enterprise_id):
return (
Session.query(Appointment)
.join((Users, Appointment.user_created == Users.user_id))
.filter(
and_(
Appointment.public == True,
Appointment.delete_dt == None,
Users.enterprise_id == enterprise_id,
Appointment.start_dt >= util.now(),
)
)
.order_by(Appointment.start_dt.desc(), Appointment.start_time.asc())
.all()
)
def search(enterprise_id, vendor_id, from_dt, to_dt):
v_clause = f_clause = t_clause = ''
if vendor_id:
v_clause = "and po.vendor_id = '%s'" % vendor_id
if from_dt:
f_clause = "and po.create_dt >= '%s'" % from_dt
if to_dt:
t_clause = "and po.create_dt <= '%s'" % to_dt
sql = """SELECT po.* FROM crm_purchase_order po, crm_company com
where po.company_id = com.company_id
and com.enterprise_id = '{ent_id}'
{v} {f} {t} order by po.create_dt""".format(v=v_clause, f=f_clause,
t=t_clause, ent_id=enterprise_id)
return Session.query(PurchaseOrder).from_statement(sql).all()
def find_by_host(host):
www_host = (host if host.startswith('www.') else 'www.%s' % host).replace(':80', '')
short_host = host.replace('www.', '').replace(':80', '')
return Session.query(Site) \
.options(FromCache('Site.find_by_host', host)) \
.filter(or_(
Site.domain==host,
Site.domain==www_host,
Site.domain==short_host,
Site.domain_alias0==host,
Site.domain_alias0==www_host,
Site.domain_alias0==short_host,
Site.domain_alias1==host,
Site.domain_alias1==www_host,
Site.domain_alias1==short_host,
Site.domain_alias2==host,
Site.domain_alias2==www_host,
Site.domain_alias2==short_host)).first()