class Student(db.Model):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
contextual_id = column_property(
func.md5(
bindparam('context', value='', type_=String) +
func.cast(id, String)))
name = Column(String(64), index=True, nullable=False)
address = Column(String(128), index=False, nullable=True)
phone = Column(String(35), nullable=True)
home_phone = Column(String(35), nullable=True)
email = Column(CIText(64, True), nullable=True)
created = Column(DateTime, server_default='now()')
guardian_number = MapColumn('home_phone')
phone_numbers = MapColumn(['phone', 'home_phone'])
contact_info = MapColumn({
'phone': 'phone',
'home_phone': 'home_phone',
'email': 'email'
})
teachers = relationship(
"Teacher",
secondary='teacher_to_student',
primaryjoin="Student.id == teacher_to_student.c.student_id",
secondaryjoin="teacher_to_student.c.teacher_id == Teacher.id")
first_name = column_property(func.split_part(func.trim(name), " ", 1))
def create_village_venues():
for village in Village.query.all():
venue = Venue.query.filter_by(village_id=village.id).first()
if venue:
if venue.name in EMF_VENUES:
app.logger.info(f"Not updating EMF venue {venue.name}")
elif venue.name != village.name:
app.logger.info(
f"Updating village venue name from {venue.name} to {village.name}"
)
venue.name = village.name
db.session.commit()
continue
if Venue.query.filter(
func.lower(Venue.name) == func.trim(func.lower(
village.name))).count():
app.logger.warning(
f"Not creating village venue with colliding name {village.name}"
)
continue
venue = Venue(name=village.name,
village_id=village.id,
scheduled_content_only=False)
db.session.add(venue)
db.session.commit()
def update_ego_grid_ehv_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
conn = engine.connect()
#alembic
ctx = MigrationContext.configure(conn)
op = Operations(ctx)
#add column
op.add_column('ego_grid_ehv_substation',
Column('otg_id', BigInteger),
schema='model_draft')
# load the tables
meta = MetaData()
conn.execute("SET search_path TO model_draft, grid, public")
ego_grid_hvmv_substation = Table('ego_grid_hvmv_substation',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
ego_grid_ehv_substation = Table('ego_grid_ehv_substation',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
otg_ehvhv_bus_data = Table('otg_ehvhv_bus_data',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
#operations with "ego_grid_ehv_substation" table
#update
ehv_substation_update = ego_grid_ehv_substation.update().values(
otg_id=otg_ehvhv_bus_data.c.bus_i).where(
and_(
otg_ehvhv_bus_data.c.base_kv > 110,
otg_ehvhv_bus_data.c.osm_substation_id == cast(
func.trim(ego_grid_ehv_substation.c.osm_id, 'nwr'),
BigInteger)))
#delete
ehv_substation_delete = ego_grid_ehv_substation.delete().where(
ego_grid_ehv_substation.c.otg_id.is_(None))
#execution
conn.execute(ehv_substation_update)
conn.execute(ehv_substation_delete)
def update_desa_from_code():
desa_alias = aliased(SdAllDesa)
kecamatan_alias = aliased(SdAllDesa)
kabupaten_alias = aliased(SdAllDesa)
propinsi_alias = aliased(SdAllDesa)
query = update(SdDesa).values(desa=desa_alias.region_name,
kecamatan=kecamatan_alias.region_name,
kabupaten=kabupaten_alias.region_name,
propinsi=propinsi_alias.region_name)
query = query.where(SdDesa.kode == desa_alias.region_code)
query = query.where(desa_alias.parent_code == kecamatan_alias.region_code)
query = query.where(
kecamatan_alias.parent_code == kabupaten_alias.region_code)
query = query.where(
kabupaten_alias.parent_code == propinsi_alias.region_code)
query = query.where(func.trim(func.coalesce(SdDesa.kode, '')) != '')
db.session.execute(query)
db.session.commit()
return jsonify({'success': True})
def update_ego_grid_ehv_substation():
global execution_time
start_time = time.monotonic()
# create_engine
engine = create_engine('postgresql://*****:*****@localhost:5432/oedb')
conn = engine.connect()
#alembic
ctx = MigrationContext.configure(conn)
op = Operations(ctx)
#add column
op.add_column('ego_grid_ehv_substation',
Column('otg_id', BigInteger),
schema = 'model_draft'
)
# load the tables
meta = MetaData()
conn.execute("SET search_path TO model_draft, grid, public")
ego_grid_hvmv_substation = Table('ego_grid_hvmv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True)
ego_grid_ehv_substation = Table('ego_grid_ehv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True)
otg_ehvhv_bus_data = Table('otg_ehvhv_bus_data', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True)
#operations with "ego_grid_hvmv_substation" table
#update
ehv_substation_update = ego_grid_ehv_substation.update().values(otg_id=otg_ehvhv_bus_data.c.bus_i).where(
and_(
otg_ehvhv_bus_data.c.base_kv>110,
otg_ehvhv_bus_data.c.osm_substation_id==cast(func.trim(ego_grid_ehv_substation.c.osm_id, 'nwr'), BigInteger)
)
)
#delete
ehv_substation_delete = ego_grid_ehv_substation.delete().where(
ego_grid_ehv_substation.c.otg_id.is_(None)
)
#execution
conn.execute(ehv_substation_update)
conn.execute(ehv_substation_delete)
execution_time = time.monotonic() - start_time
def expnum(cls):
return func.trim(cls._expnum)
class PromoCodeWord(MagModel):
"""
Words used to generate promo codes.
Attributes:
word (str): The text of this promo code word.
normalized_word (str): A normalized version of `word`, suitable for
database queries.
part_of_speech (int): The part of speech that `word` is.
Valid values are:
* 0 `_ADJECTIVE`: `word` is an adjective
* 1 `_NOUN`: `word` is a noun
* 2 `_VERB`: `word` is a verb
* 3 `_ADVERB`: `word` is an adverb
part_of_speech_str (str): A human readable description of
`part_of_speech`.
"""
_ADJECTIVE = 0
_NOUN = 1
_VERB = 2
_ADVERB = 3
_PART_OF_SPEECH_OPTS = [(_ADJECTIVE, 'adjective'), (_NOUN, 'noun'),
(_VERB, 'verb'), (_ADVERB, 'adverb')]
_PARTS_OF_SPEECH = dict(_PART_OF_SPEECH_OPTS)
word = Column(UnicodeText)
part_of_speech = Column(Choice(_PART_OF_SPEECH_OPTS), default=_ADJECTIVE)
__table_args__ = (Index(
'uq_promo_code_word_normalized_word_part_of_speech',
func.lower(func.trim(word)),
part_of_speech,
unique=True),
CheckConstraint(
func.trim(word) != '',
name='ck_promo_code_word_non_empty_word'))
_repr_attr_names = ('word', )
@hybrid_property
def normalized_word(self):
return self.normalize_word(self.word)
@normalized_word.expression
def normalized_word(cls):
return func.lower(func.trim(cls.word))
@property
def part_of_speech_str(self):
return self._PARTS_OF_SPEECH[self.part_of_speech].title()
@presave_adjustment
def _attribute_adjustments(self):
# Replace multiple whitespace characters with a single space
self.word = re.sub(r'\s+', ' ', self.word.strip())
@classmethod
def group_by_parts_of_speech(cls, words):
"""
Groups a list of words by their part_of_speech.
Arguments:
words (list): List of `PromoCodeWord`.
Returns:
OrderedDict: A dictionary of words mapped to their part of speech,
like this::
OrderedDict([
(0, ['adjective1', 'adjective2']),
(1, ['noun1', 'noun2']),
(2, ['verb1', 'verb2']),
(3, ['adverb1', 'adverb2'])
])
"""
parts_of_speech = OrderedDict([
(i, []) for (i, _) in PromoCodeWord._PART_OF_SPEECH_OPTS
])
for word in words:
parts_of_speech[word.part_of_speech].append(word.word)
return parts_of_speech
@classmethod
def normalize_word(cls, word):
"""
Normalizes a word.
Arguments:
word (str): A word as typed by an admin.
Returns:
str: A copy of `word` converted to all lowercase, and multiple
whitespace characters replaced by a single space.
"""
return re.sub(r'\s+', ' ', word.strip().lower())
def updateRow(self, sampleRow):
for i in range(len(sampleRow)):
#sampleRow[i]=sampleRow[i].lower()
if sampleRow[i].strip()=='':
sampleRow[i]=None
plecval =sampleRow[4]
try:
pslval=str(sampleRow[22]).strip()
except:
pslval = sampleRow[22]
pimval=sampleRow[7].encode('windows-1250')
dimval= sampleRow[8]
nzwval=sampleRow[5].encode('windows-1250')
if sampleRow[9]:
oimval=sampleRow[9].encode('windows-1250')
else:
oimval=sampleRow[9]
if sampleRow[10]:
mimval=sampleRow[10].encode('windows-1250')
else:
mimval=sampleRow[10]
try:
kodval = sampleRow[12].replace(' ', '')
except:
kodval=sampleRow[12]
if sampleRow[13] <> None:
nazval = sampleRow[13].upper().encode('windows-1250')
nazval = '%'+nazval+'%'
else:
nazval = ''
try:
nraval = re.sub('"', '', sampleRow[15])
nraval = str(nraval)
except:
nraval=sampleRow[15]
if nraval is not None and re.search('0,', nraval):
nraval= None
############################
# test czy pesel juz jest w bazie!!!!
############################
if self.session.query(Osoby).filter(Osoby.psl == pslval.strip()).count()>=1:
return 'jest_w_bazie'
rawquery = self.session.query(Osoby.uid).join(Adresy).\
filter(Osoby.plec==plecval,\
Osoby.pim==pimval,\
Osoby.dim==dimval,\
Osoby.nzw==nzwval,\
Osoby.oim==oimval,\
Osoby.mim ==mimval,\
func.upper(Adresy.naz).like(nazval),\
Adresy.kod==kodval,\
func.trim(Adresy.nra)==nraval,\
)
q=rawquery.subquery()
rowcunt = 0
rowcunt = rawquery.count()
if rowcunt == 0:
return 'nie_znalazl'#
elif rowcunt == 1:
self.session.query(Osoby).filter(Osoby.uid.in_(q)).\
update({Osoby.psl: pslval}, synchronize_session='fetch')
self.session.commit()
return rowcunt
elif rowcunt >1:
return 'dubel'
def db_compare(col, text):
text_ = text.lower().strip()
col_ = func.trim(func.lower(col))
return col_ == text_
def query_id(cls, id):
return cls.query().filter(func.trim(cls.subjek_pajak_id) == id)
def available_usernames(self):
return self.usercollection.query()\
.with_entities(User.username, User.realname)\
.filter(func.trim(func.coalesce(User.realname, "")) != "")\
.filter(User.active == True)\
.order_by(func.unaccent(func.lower(User.realname)))
def updateRow(self, sampleRow):
for i in range(len(sampleRow)):
#sampleRow[i]=sampleRow[i].lower()
if sampleRow[i] == '':
sampleRow[i] = None
plecval = sampleRow[4]
try:
pslval = str(sampleRow[22]).strip('\r')
except:
pslval = sampleRow[22]
pimval = sampleRow[7].encode('windows-1250')
dimval = sampleRow[8]
nzwval = sampleRow[5].encode('windows-1250')
if sampleRow[9]:
oimval = sampleRow[9].encode('windows-1250')
else:
oimval = sampleRow[9]
if sampleRow[10]:
mimval = sampleRow[10].encode('windows-1250')
else:
mimval = sampleRow[10]
try:
kodval = sampleRow[12].replace(' ', '')
except:
kodval = sampleRow[12]
if sampleRow[13] <> None:
nazval = sampleRow[13].upper().encode('windows-1250')
nazval = '%' + nazval + '%'
else:
nazval = ''
try:
nraval = str(sampleRow[15])
except:
nraval = sampleRow[15]
if re.search('0,', nraval):
nraval = None
rawquery = self.session.query(Osoby.uid).join(Adresy).\
filter(Osoby.plec==plecval,\
Osoby.pim==pimval,\
Osoby.dim==dimval,\
Osoby.nzw==nzwval,\
Osoby.oim==oimval,\
Osoby.mim ==mimval,\
func.upper(Adresy.naz).like(nazval),\
Adresy.kod==kodval,\
func.trim(Adresy.nra)==nraval,\
)
q = rawquery.subquery()
rowcunt = 0
rowcunt = rawquery.count()
if rowcunt == 0:
return rowcunt
elif rowcunt <> 0:
self.session.query(Osoby).filter(Osoby.uid.in_(q)).\
update({Osoby.psl: pslval}, synchronize_session='fetch')
self.session.commit()
return rowcunt
class User(db.Model):
__tablename__ = 'users'
__updateable_fields__ = {
'firstname': str,
'lastname': str,
'password': bytes,
'is_admin': bool,
'rank_id': int,
'imagename': dict
}
from .rank import Rank
from .rank_update import RankUpdate
from .user_verification import UserVerification
from .purchase import Purchase
from .deposit import Deposit
from .replenishment import ReplenishmentCollection
id = db.Column(db.Integer, primary_key=True)
creation_date = db.Column(db.DateTime, default=func.now(), nullable=False)
firstname = db.Column(db.String(32), unique=False, nullable=True)
lastname = db.Column(db.String(32), unique=False, nullable=False)
password = db.Column(db.String(256), unique=False, nullable=True)
is_verified = db.Column(db.Boolean, nullable=False, default=False)
image_upload_id = db.Column(db.Integer,
db.ForeignKey('uploads.id'),
nullable=True)
# Column property for the full name
fullname = column_property(
func.trim(func.coalesce(firstname, "") + " " + lastname))
# Column property for the active state
active = column_property(
select([Rank.active]).where(
and_(RankUpdate.user_id == id,
Rank.id == RankUpdate.rank_id)).order_by(
RankUpdate.id.desc()).limit(1).as_scalar())
# Column property for the is system user property
is_system_user = column_property(
select([Rank.is_system_user]).where(
and_(RankUpdate.user_id == id,
Rank.id == RankUpdate.rank_id)).order_by(
RankUpdate.id.desc()).limit(1).as_scalar())
# Column property for the verification_date
verification_date = column_property(
select([UserVerification.timestamp
]).where(UserVerification.user_id == id).limit(1).as_scalar())
# Column property for the rank_id
rank_id = column_property(
select([Rank.id]).where(
and_(RankUpdate.user_id == id,
Rank.id == RankUpdate.rank_id)).order_by(
RankUpdate.id.desc()).limit(1).as_scalar())
# Select statement for the sum of all non revoked purchases referring this user.
# NOTE: func.coalesce(a, b) returns the first non-null value of (a, b). If there aren't any purchases
# (or deposits, ...) yet, the purchase (deposit, ...) sum is NULL. In this case, 0 gets returned.
_purchase_sum = column_property(
select([func.coalesce(func.sum(Purchase.price),
0)]).where(Purchase.user_id == id).where(
Purchase.revoked.is_(False)).as_scalar())
# Select statement for the sum of all non revoked deposits referring this user.
_deposit_sum = column_property(
select([func.coalesce(func.sum(Deposit.amount),
0)]).where(Deposit.user_id == id).where(
Deposit.revoked.is_(False)).as_scalar())
# Select statement for the sum of all non revoked refunds referring this user.
_replenishmentcollection_sum = column_property(
select([func.coalesce(func.sum(ReplenishmentCollection.price), 0)
]).where(ReplenishmentCollection.seller_id == id).where(
ReplenishmentCollection.revoked.is_(False)).as_scalar())
# A users credit is the sum of all amounts that increase his credit (Deposits, ReplenishmentCollections)
# and all amounts that decrease it (Purchases)
credit = column_property(_replenishmentcollection_sum.expression +
_deposit_sum.expression -
_purchase_sum.expression)
# Link to all purchases of a user.
purchases = db.relationship('Purchase',
lazy='dynamic',
foreign_keys='Purchase.user_id')
# Link to all deposits of a user.
deposits = db.relationship('Deposit',
lazy='dynamic',
foreign_keys='Deposit.user_id')
# Link to all deposits of a user.
replenishmentcollections = db.relationship(
'ReplenishmentCollection',
lazy='dynamic',
foreign_keys='ReplenishmentCollection.seller_id')
def __repr__(self):
return f'<User {self.id}: {self.lastname}, {self.firstname}>'
@hybrid_property
def imagename(self):
from .upload import Upload
upload = Upload.query.filter_by(id=self.image_upload_id).first()
if upload:
return upload.filename
return None
@hybrid_method
def set_imagename(self, image, admin_id):
filename = insert_image(image)
# Create an upload
try:
from .upload import Upload
u = Upload(filename=filename, admin_id=admin_id)
db.session.add(u)
db.session.flush()
self.image_upload_id = u.id
except IntegrityError:
raise CouldNotCreateEntry()
@hybrid_property
def is_admin(self):
from .admin_update import AdminUpdate
au = (AdminUpdate.query.filter_by(user_id=self.id).order_by(
AdminUpdate.id.desc()).first())
if au is None:
return False
return au.is_admin
@hybrid_method
def set_admin(self, is_admin, admin_id):
from .admin_update import AdminUpdate
if is_admin and self.password is None:
raise UserNeedsPassword()
if self.is_admin == is_admin:
raise NothingHasChanged()
au = AdminUpdate(is_admin=is_admin, admin_id=admin_id, user_id=self.id)
db.session.add(au)
@hybrid_method
def verify(self, admin_id, rank_id):
from .user_verification import UserVerification
if self.is_verified:
raise UserAlreadyVerified()
self.is_verified = True
uv = UserVerification(user_id=self.id, admin_id=admin_id)
self.set_rank_id(rank_id, admin_id)
db.session.add(uv)
@hybrid_method
def set_rank_id(self, rank_id, admin_id):
from .rank_update import RankUpdate
if self.is_verified:
ru = RankUpdate(rank_id=rank_id,
admin_id=admin_id,
user_id=self.id)
db.session.add(ru)
else:
self.verify(admin_id=admin_id, rank_id=rank_id)
@hybrid_method
def set_is_admin(self, is_admin, admin_id):
self.set_admin(is_admin=is_admin, admin_id=admin_id)
if not self.is_admin:
users = User.query.all()
admins = list(filter(lambda x: x.is_admin, users))
if not admins:
raise NoRemainingAdmin()
@hybrid_property
def rank(self):
from .rank import Rank
if self.rank_id:
rank = Rank.query.filter(Rank.id == self.rank_id).first()
if rank:
return rank
return None
@hybrid_property
def favorites(self):
"""
Returns the product ids of the user's favorite products in
descending order of number. Inactive products those who are
not for sale are ignored.
Args:
self: self
Returns:
ids: A list of the favorite product ids in descending order.
"""
from .tag import Tag
from .product_tag_assignment import product_tag_assignments
from .purchase import Purchase
from .product import Product
# Get a list of all invalid tag ids (as SQL subquery)
invalid_tag_ids = db.session.query(Tag.id).filter(
Tag.is_for_sale.is_(False)).subquery()
# Get a list of all products to which this tag is assigned
invalid_product_ids = (db.session.query(
product_tag_assignments.c.product_id).filter(
product_tag_assignments.c.tag_id.in_(
invalid_tag_ids)).subquery())
# Get a list of all inactive product ids
inactive_product_ids = db.session.query(Product.id).filter(
Product.active.is_(False)).subquery()
result = (
db.session.query(Purchase.product_id).filter(
Purchase.user_id == self.id) # Get only user purchases
.group_by(Purchase.product_id) # Group by products
.filter(Purchase.product_id.notin_(
invalid_product_ids)) # Get only products which are for sale
.filter(Purchase.product_id.notin_(
inactive_product_ids)) # Get only products which are active
.filter(
Purchase.revoked.is_(False)) # Get only non revoked purchases
.order_by(func.sum(
Purchase.amount).desc()) # Order by the sum of purchase amount
.all())
return [item.product_id for item in result]
def updateRow(self, sampleRow):
for i in range(len(sampleRow)):
# sampleRow[i]=sampleRow[i].lower()
if sampleRow[i] == "":
sampleRow[i] = None
plecval = sampleRow[4]
try:
pslval = str(sampleRow[22]).strip("\r")
except:
pslval = sampleRow[22]
pimval = sampleRow[7].encode("windows-1250")
dimval = sampleRow[8]
nzwval = sampleRow[5].encode("windows-1250")
if sampleRow[9]:
oimval = sampleRow[9].encode("windows-1250")
else:
oimval = sampleRow[9]
if sampleRow[10]:
mimval = sampleRow[10].encode("windows-1250")
else:
mimval = sampleRow[10]
try:
kodval = sampleRow[12].replace(" ", "")
except:
kodval = sampleRow[12]
if sampleRow[13] <> None:
nazval = sampleRow[13].upper().encode("windows-1250")
nazval = "%" + nazval + "%"
else:
nazval = ""
try:
nraval = str(sampleRow[15])
except:
nraval = sampleRow[15]
if re.search("0,", nraval):
nraval = None
rawquery = (
self.session.query(Osoby.uid)
.join(Adresy)
.filter(
Osoby.plec == plecval,
Osoby.pim == pimval,
Osoby.dim == dimval,
Osoby.nzw == nzwval,
Osoby.oim == oimval,
Osoby.mim == mimval,
func.upper(Adresy.naz).like(nazval),
Adresy.kod == kodval,
func.trim(Adresy.nra) == nraval,
)
)
q = rawquery.subquery()
rowcunt = 0
rowcunt = rawquery.count()
if rowcunt == 0:
return rowcunt
elif rowcunt <> 0:
self.session.query(Osoby).filter(Osoby.uid.in_(q)).update({Osoby.psl: pslval}, synchronize_session="fetch")
self.session.commit()
return rowcunt
def expnum(self):
return func.trim(self._expnum)
def db_norm(col):
return func.trim(func.lower(col))
def insert_organizations(db: Session):
'''
This method will insert all osm objects into organization
if the osm_id is not yet inserted
'''
# sub-request for select osm_id,name,name_normalized,importance
# name_normalized is for removing trailling space and replace white-space by dash
query_normalized_name = db.query(
OSMName.osm_id.label('osm_id'), OSMName.name.label('name'),
func.replace(func.trim(OSMName.name, ' '), ' ',
'-').label("name_normalized"),
OSMName.importance.label('importance'))
query_normalized_name = query_normalized_name.subquery('normalized_name')
# sub-request for select osm_id, name, name_normalized, row_number
# row_number will be used for generate the slug in the next request
# the row number is partition_by name_normalized
# and order by "importance" DESC
# src: https://osmnames.readthedocs.io/en/latest/introduction.html
# "importance" :Importance of the feature, ranging [0.0-1.0], 1.0 being the most important.
query_unique_slug = db.query(
query_normalized_name.c.osm_id.label('osm_id'),
query_normalized_name.c.name.label('name'),
query_normalized_name.c.name_normalized.label('name_normalized'),
func.row_number().over(
partition_by=query_normalized_name.c.name_normalized,
order_by=desc(
query_normalized_name.c.importance)).label("row_number"))
query_unique_slug = query_unique_slug.subquery('unique_slug')
# request for filtering what we insert
where_query = db.query(Organization.osm_id.label('osm_id'))
# final request for build the slug
# example
# we could have many name_normalized
# name | name_normalized | osm_id | row_number
# new york | new-york | 1254 | 1
# new-york | new-york | 215486 | 2
# will generate
# name | osm_id | slug
# new york | 1254 | new-york
# new-york | 215486 | new-york-215486
query = db.query(
query_unique_slug.c.name.label('name'),
query_unique_slug.c.osm_id.label('osm_id'),
case([(query_unique_slug.c.row_number
== 1, query_unique_slug.c.name_normalized)],
else_=func.concat(query_unique_slug.c.name_normalized, '-',
query_unique_slug.c.osm_id)).label('slug'))
query = query.filter(query_unique_slug.c.osm_id.notin_(where_query))
insert_query = insert(Organization)
insert_query = insert_query.from_select(
(Organization.name, Organization.osm_id, Organization.slug), query)
try:
db.execute(insert_query)
db.commit()
db.close()
finally:
pass
def _select_training_set_data_from_database(label_columns,
filter_args=None,
filter_func=None,
limit=None,
**kwargs):
label_columns = list(label_columns)
label_names = [column.key for column in label_columns]
L = len(label_names)
if filter_func is None:
filter_func = lambda *_, **__: True
# Get the label names.
log.info(f"Querying for label names {label_names} from {label_columns}")
# Figure out what other columns we will need to identify the input file.
for column in label_columns:
try:
primary_parent = column.class_
except AttributeError:
continue
else:
break
else:
raise ValueError(
"Can't get primary parent. are you labelling every column?")
log.debug(f"Identified primary parent table as {primary_parent}")
if primary_parent == catalogdb.SDSSApogeeAllStarMergeR13:
log.debug(
f"Adding columns and setting data_model_func for {primary_parent}")
additional_columns = [
catalogdb.SDSSDR16ApogeeStar.apstar_version.label("apstar"),
catalogdb.SDSSDR16ApogeeStar.field,
catalogdb.SDSSDR16ApogeeStar.apogee_id.label("obj"),
catalogdb.SDSSDR16ApogeeStar.file,
catalogdb.SDSSDR16ApogeeStar.telescope,
# Things that we might want for filtering on.
catalogdb.SDSSDR16ApogeeStar.snr
]
columns = label_columns + additional_columns
q = session.query(*columns).join(
catalogdb.SDSSApogeeAllStarMergeR13,
func.trim(catalogdb.SDSSApogeeAllStarMergeR13.apstar_ids) ==
catalogdb.SDSSDR16ApogeeStar.apstar_id)
data_model_func = lambda apstar, field, obj, filename, telescope, *_, : {
"release": "DR16",
"filetype": "apStar",
"apstar": apstar,
"field": field,
"obj": obj,
"prefix": filename[:2],
"telescope": telescope,
"apred": filename.split("-")[1]
}
else:
raise NotImplementedError(
f"Cannot intelligently figure out what data model keywords will be necessary."
)
if filter_args is not None:
q = q.filter(*filter_args)
if limit is not None:
q = q.limit(limit)
log.debug(f"Querying {q}")
data_model_identifiers = []
labels = {label_name: [] for label_name in label_names}
for i, row in enumerate(tqdm(q.yield_per(1), total=q.count())):
if not filter_func(*row): continue
for label_name, value in zip(label_names, row[:L]):
if not np.isfinite(value) or value is None:
log.warning(
f"Label {label_name} in {i} row is not finite: {value}!")
labels[label_name].append(value)
data_model_identifiers.append(data_model_func(*row[L:]))
return (labels, data_model_identifiers)
class PromoCode(MagModel):
"""
Promo codes used by attendees to purchase badges at discounted prices.
Attributes:
code (str): The actual textual representation of the promo code. This
is what the attendee would have to type in during registration to
receive a discount. `code` may not be an empty string or a string
consisting entirely of whitespace.
discount (int): The discount amount that should be applied to the
purchase price of a badge. The interpretation of this value
depends on the value of `discount_type`. In any case, a value of
0 equates to a full discount, i.e. a free badge.
discount_str (str): A human readable description of the discount.
discount_type (int): The type of discount this promo code will apply.
Valid values are:
* 0 `_FIXED_DISCOUNT`: `discount` is interpreted as a fixed
dollar amount by which the badge price should be reduced. If
`discount` is 49 and the badge price is normally $100, then
the discounted badge price would be $51.
* 1 `_FIXED_PRICE`: `discount` is interpreted as the actual badge
price. If `discount` is 49, then the discounted badge price
would be $49.
* 2 `_PERCENT_DISCOUNT`: `discount` is interpreted as a percentage
by which the badge price should be reduced. If `discount` is
20 and the badge price is normally $50, then the discounted
badge price would $40 ($50 reduced by 20%). If `discount` is
100, then the price would be 100% off, i.e. a free badge.
group (relationship): An optional relationship to a PromoCodeGroup
object, which groups sets of promo codes to make attendee-facing
"groups"
cost (int): The cost of this promo code if and when it was bought
as part of a PromoCodeGroup.
expiration_date (datetime): The date & time upon which this promo code
expires. An expired promo code may no longer be used to receive
discounted badges.
is_free (bool): True if this promo code will always cause a badge to
be free. False if this promo code may not cause a badge to be free.
Note:
It's possible for this value to be False for a promo code that
still reduces a badge's price to zero. If there are some other
discounts that also reduce a badge price (like an age discount)
then the price may be pushed down to zero.
is_expired (bool): True if this promo code is expired, False otherwise.
is_unlimited (bool): True if this promo code may be used an unlimited
number of times, False otherwise.
is_valid (bool): True if this promo code is still valid and may be
used again, False otherwise.
normalized_code (str): A normalized version of `code` suitable for
database queries. Normalization converts `code` to all lowercase
and removes dashes ("-").
used_by (list): List of attendees that have used this promo code.
Note:
This property is declared as a backref in the Attendee class.
uses_allowed (int): The total number of times this promo code may be
used. A value of None means this promo code may be used an
unlimited number of times.
uses_allowed_str (str): A human readable description of
uses_allowed.
uses_count (int): The number of times this promo code has already
been used.
uses_count_str (str): A human readable description of uses_count.
uses_remaining (int): Remaining number of times this promo code may
be used.
uses_remaining_str (str): A human readable description of
uses_remaining.
"""
_FIXED_DISCOUNT = 0
_FIXED_PRICE = 1
_PERCENT_DISCOUNT = 2
_DISCOUNT_TYPE_OPTS = [(_FIXED_DISCOUNT, 'Fixed Discount'),
(_FIXED_PRICE, 'Fixed Price'),
(_PERCENT_DISCOUNT, 'Percent Discount')]
_AMBIGUOUS_CHARS = {
'0': 'OQD',
'1': 'IL',
'2': 'Z',
'5': 'S',
'6': 'G',
'8': 'B'
}
_UNAMBIGUOUS_CHARS = string.digits + string.ascii_uppercase
for _, s in _AMBIGUOUS_CHARS.items():
_UNAMBIGUOUS_CHARS = re.sub('[{}]'.format(s), '', _UNAMBIGUOUS_CHARS)
code = Column(UnicodeText)
discount = Column(Integer, nullable=True, default=None)
discount_type = Column(Choice(_DISCOUNT_TYPE_OPTS),
default=_FIXED_DISCOUNT)
expiration_date = Column(UTCDateTime, default=c.ESCHATON)
uses_allowed = Column(Integer, nullable=True, default=None)
cost = Column(Integer, nullable=True, default=None)
group_id = Column(UUID,
ForeignKey('promo_code_group.id', ondelete='SET NULL'),
nullable=True)
group = relationship(PromoCodeGroup,
backref='promo_codes',
foreign_keys=group_id,
cascade='save-update,merge,refresh-expire,expunge')
__table_args__ = (Index('uq_promo_code_normalized_code',
func.replace(
func.replace(func.lower(code), '-', ''), ' ',
''),
unique=True),
CheckConstraint(func.trim(code) != '',
name='ck_promo_code_non_empty_code'))
_repr_attr_names = ('code', )
@classmethod
def normalize_expiration_date(cls, dt):
"""
Converts the given datetime to 11:59pm local in the event timezone.
"""
if isinstance(dt, six.string_types):
if dt.strip():
dt = dateparser.parse(dt)
else:
dt = c.ESCHATON
if dt.tzinfo:
dt = dt.astimezone(c.EVENT_TIMEZONE)
return c.EVENT_TIMEZONE.localize(
dt.replace(hour=23, minute=59, second=59, tzinfo=None))
@property
def discount_str(self):
if self.discount_type == self._FIXED_DISCOUNT and self.discount == 0:
# This is done to account for Art Show Agent codes, which use the PromoCode class
return 'No discount'
elif not self.discount:
return 'Free badge'
if self.discount_type == self._FIXED_DISCOUNT:
return '${} discount'.format(self.discount)
elif self.discount_type == self._FIXED_PRICE:
return '${} badge'.format(self.discount)
else:
return '%{} discount'.format(self.discount)
@hybrid_property
def is_expired(self):
return self.expiration_date < localized_now()
@is_expired.expression
def is_expired(cls):
return cls.expiration_date < localized_now()
@property
def is_free(self):
return not self.discount or (
self.discount_type == self._PERCENT_DISCOUNT
and self.discount >= 100) or (self.discount_type
== self._FIXED_DISCOUNT
and self.discount >= c.BADGE_PRICE)
@hybrid_property
def is_unlimited(self):
return not self.uses_allowed
@is_unlimited.expression
def is_unlimited(cls):
return cls.uses_allowed == None # noqa: E711
@hybrid_property
def is_valid(self):
return not self.is_expired and (self.is_unlimited
or self.uses_remaining > 0)
@is_valid.expression
def is_valid(cls):
return (cls.expiration_date >= localized_now()) \
& ((cls.uses_allowed == None) | (cls.uses_remaining > 0)) # noqa: E711
@hybrid_property
def normalized_code(self):
return self.normalize_code(self.code)
@normalized_code.expression
def normalized_code(cls):
return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ',
'')
@property
def uses_allowed_str(self):
uses = self.uses_allowed
return 'Unlimited uses' if uses is None else '{} use{} allowed'.format(
uses, '' if uses == 1 else 's')
@hybrid_property
def uses_count(self):
return len(self.used_by)
@uses_count.expression
def uses_count(cls):
from uber.models.attendee import Attendee
return select([
func.count(Attendee.id)
]).where(Attendee.promo_code_id == cls.id).label('uses_count')
@property
def uses_count_str(self):
uses = self.uses_count
return 'Used by {} attendee{}'.format(uses, '' if uses == 1 else 's')
@hybrid_property
def uses_remaining(self):
return None if self.is_unlimited else self.uses_allowed - self.uses_count
@uses_remaining.expression
def uses_remaining(cls):
return cls.uses_allowed - cls.uses_count
@property
def uses_remaining_str(self):
uses = self.uses_remaining
return 'Unlimited uses' if uses is None else '{} use{} remaining'.format(
uses, '' if uses == 1 else 's')
@presave_adjustment
def _attribute_adjustments(self):
# If 'uses_allowed' is empty, then this is an unlimited use code
if not self.uses_allowed:
self.uses_allowed = None
# If 'discount' is empty, then this is a full discount, free badge
if self.discount == '':
self.discount = None
self.code = self.code.strip() if self.code else ''
if not self.code:
# If 'code' is empty, then generate a random code
self.code = self.generate_random_code()
else:
# Replace multiple whitespace characters with a single space
self.code = re.sub(r'\s+', ' ', self.code)
# Always make expiration_date 11:59pm of the given date
self.expiration_date = self.normalize_expiration_date(
self.expiration_date)
def calculate_discounted_price(self, price):
"""
Returns the discounted price based on the promo code's `discount_type`.
Args:
price (int): The badge price in whole dollars.
Returns:
int: The discounted price. The returned number will never be
less than zero or greater than `price`. If `price` is None
or a negative number, then the return value will always be 0.
"""
if not self.discount or not price or price < 0:
return 0
discounted_price = price
if self.discount_type == self._FIXED_DISCOUNT:
discounted_price = price - self.discount
elif self.discount_type == self._FIXED_PRICE:
discounted_price = self.discount
elif self.discount_type == self._PERCENT_DISCOUNT:
discounted_price = int(price * ((100.0 - self.discount) / 100.0))
return min(max(discounted_price, 0), price)
@classmethod
def _generate_code(cls, generator, count=None):
"""
Helper method to limit collisions for the other generate() methods.
Arguments:
generator (callable): Function that returns a newly generated code.
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
# Kind of inefficient, but doing one big query for all the existing
# codes will be faster than a separate query for each new code.
old_codes = set(s for (s, ) in session.query(cls.code).all())
# Set an upper limit on the number of collisions we'll allow,
# otherwise this loop could potentially run forever.
max_collisions = 100
collisions = 0
codes = set()
while len(codes) < (1 if count is None else count):
code = generator().strip()
if not code:
break
if code in codes or code in old_codes:
collisions += 1
if collisions >= max_collisions:
break
else:
codes.add(code)
return (codes.pop() if codes else None) if count is None else codes
@classmethod
def generate_random_code(cls, count=None, length=9, segment_length=3):
"""
Generates a random promo code.
With `length` = 12 and `segment_length` = 3::
XXX-XXX-XXX-XXX
With `length` = 6 and `segment_length` = 2::
XX-XX-XX
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
length (int): The number of characters to use for the code.
segment_length (int): The length of each segment within the code.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
# The actual generator function, called repeatedly by `_generate_code`
def _generate_random_code():
letters = ''.join(
random.choice(cls._UNAMBIGUOUS_CHARS) for _ in range(length))
return '-'.join(textwrap.wrap(letters, segment_length))
return cls._generate_code(_generate_random_code, count=count)
@classmethod
def generate_word_code(cls, count=None):
"""
Generates a promo code consisting of words from `PromoCodeWord`.
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
words = PromoCodeWord.group_by_parts_of_speech(
session.query(PromoCodeWord).order_by(
PromoCodeWord.normalized_word).all())
# The actual generator function, called repeatedly by `_generate_code`
def _generate_word_code():
code_words = []
for part_of_speech, _ in PromoCodeWord._PART_OF_SPEECH_OPTS:
if words[part_of_speech]:
code_words.append(random.choice(words[part_of_speech]))
return ' '.join(code_words)
return cls._generate_code(_generate_word_code, count=count)
@classmethod
def disambiguate_code(cls, code):
"""
Removes ambiguous characters in a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` with all ambiguous characters replaced by
their unambiguous equivalent.
"""
code = cls.normalize_code(code)
if not code:
return ''
for unambiguous, ambiguous in cls._AMBIGUOUS_CHARS.items():
ambiguous_pattern = '[{}]'.format(ambiguous.lower())
code = re.sub(ambiguous_pattern, unambiguous.lower(), code)
return code
@classmethod
def normalize_code(cls, code):
"""
Normalizes a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` converted to all lowercase, with dashes ("-")
and whitespace characters removed.
"""
if not code:
return ''
return re.sub(r'[\s\-]+', '', code.lower())
def db_norm(col):
return func.trim(func.lower(col))
def normalized_word(cls):
return func.lower(func.trim(cls.word))
def normalized_word(cls):
return func.lower(func.trim(cls.word))
