class LawEmdArea(db.Model):
"""
법정 읍면동 구역 모델 정의 클래스
"""
__bind_key__ = 'gisdb'
__tablename__ = 'law_emd_area'
emd_cd = db.Column(db.String(10), primary_key=True)
geom = db.Column(Geometry(geometry_type='MULTYPOLYGON', srid=4326))
emd_ko_nm = db.Column(db.String(16), nullable=False)
emd_en_nm = db.Column(db.String(28), nullable=False)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(LawEmdArea, self).__init__(**kwargs)
@classmethod
def find_by_identity(cls, identity):
return db.session.query(cls.emd_cd,
cls.emd_ko_nm,
func.ST_AsGeoJSON(func.ST_Centroid(cls.geom)).label('geojson')). \
filter(cls.emd_cd == identity).first()
@classmethod
def find_by_sgg_cd(cls, sgg_cd):
return db.session.query(cls.emd_cd,
cls.emd_ko_nm,
func.ST_AsGeoJSON(func.ST_Centroid(cls.geom)).label('geojson')). \
filter(cls.emd_cd.like('{0}%'.format(sgg_cd))). \
order_by(cls.emd_cd.asc()).all()
class Tag(ResourceMixin, db.Model):
__tablename__ = 'tags'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
# post_id = db.Column(db.Integer, db.ForeignKey('posts.id',
# onupdate='CASCADE',
# ondelete='CASCADE'),
# index=True, nullable=False)
name = db.Column(db.String(40))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Tag, self).__init__(**kwargs)
class Bookmark(ResourceMixin, db.Model):
__tablename__ = 'bookmarks'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
name = db.Column(db.String(200), nullable=False)
target = db.Column(db.String(2))
parameter = db.Column(db.String(2000))
def __init__(self, **kwargs):
super(Bookmark, self).__init__(**kwargs)
class AdmEmdArea(db.Model):
"""
행정 읍면동 구역 모델 정의 클래스
"""
__bind_key__ = 'gisdb'
__tablename__ = 'adm_emd_area'
emd_cd = db.Column(db.String(10), primary_key=True)
geom = db.Column(Geometry(geometry_type='MULTYPOLYGON', srid=4326))
emd_ko_nm = db.Column(db.String(16), nullable=False)
emd_en_nm = db.Column(db.String(28), nullable=False)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(AdmEmdArea, self).__init__(**kwargs)
@classmethod
def find_by_identity(cls, identity):
return cls.query. \
filter(cls.emd_cd == identity).first()
class Post(ResourceMixin, db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
# tags = db.relationship('Tag', secondary=tags,
# backref=db.backref('posts', lazy='dynamic'))
title = db.Column(db.String(280), nullable=False)
body = db.Column(db.String(2000))
coded_body = db.Column(db.String(4000))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Post, self).__init__(**kwargs)
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (Post.title.ilike(search_query),
Post.body.ilike(search_query))
return or_(*search_chain)
class Comment(ResourceMixin, db.Model):
__tablename__ = 'comments'
id = db.Column(db.Integer, primary_key=True)
# Relationshops.
post_id = db.Column(db.Integer,
db.ForeignKey('posts.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
body = db.Column(db.String(1000))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Comment, self).__init__(**kwargs)
class Code(ResourceMixin, db.Model):
"""
코드 모델 정의 클래스
"""
__bind_key__ = 'gisdb'
__tablename__ = 'codes'
code = db.Column(db.String(20), primary_key=True)
group_code = db.Column(db.String(20),
db.ForeignKey('code_groups.code'),
primary_key=True,
index=True)
is_use = db.Column(db.Boolean(), nullable=False, server_default='1')
is_display = db.Column(db.Boolean(), nullable=False, server_default='1')
display_order = db.Column(db.Integer, nullable=False, server_default='0')
name = db.Column(db.String(30), nullable=False)
definition = db.Column(db.String(300))
created_id = db.Column(db.Integer, nullable=False)
updated_id = db.Column(db.Integer, nullable=False)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Code, self).__init__(**kwargs)
@classmethod
def find_by_group_code(cls, group_code):
# 그룹코드로 가용한 코드 목록을 조회한다.
codes = cls.query.join(CodeGroup). \
filter(cls.group_code == group_code). \
filter(CodeGroup.is_use == True). \
filter(cls.is_use == True). \
filter(cls.is_display == True). \
order_by(cls.display_order).all()
return codes
class CodeGroup(ResourceMixin, db.Model):
"""
코드 그룹 모델 정의 클래스
"""
__bind_key__ = 'gisdb'
__tablename__ = 'code_groups'
code = db.Column(db.String(20), primary_key=True)
is_use = db.Column(db.Boolean(), nullable=False)
name = db.Column(db.String(30), nullable=False)
definition = db.Column(db.String(300))
created_id = db.Column(db.Integer, nullable=False)
updated_id = db.Column(db.Integer, nullable=False)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(CodeGroup, self).__init__(**kwargs)
class Bet(ResourceMixin, db.Model):
__tablename__ = 'bets'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
# Bet details.
guess = db.Column(db.Integer())
die_1 = db.Column(db.Integer())
die_2 = db.Column(db.Integer())
roll = db.Column(db.Integer())
wagered = db.Column(db.BigInteger())
payout = db.Column(db.Float())
net = db.Column(db.BigInteger())
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Bet, self).__init__(**kwargs)
@classmethod
def is_winner(cls, guess, roll):
"""
Determine if the result is a win or loss.
:param guess: Dice guess
:type guess: int
:param roll: Dice roll
:type roll: int
:return: bool
"""
if guess == roll:
return True
return False
@classmethod
def determine_payout(cls, payout, is_winner):
"""
Determine the payout.
:param payout: Dice guess
:type payout: float
:param is_winner: Was the bet won or lost
:type is_winner: bool
:return: int
"""
if is_winner:
return payout
return 1.0
@classmethod
def calculate_net(cls, wagered, payout, is_winner):
"""
Calculate the net won or lost.
:param wagered: Dice guess
:type wagered: int
:param payout: Dice roll
:type payout: float
:param is_winner: Was the bet won or lost
:type is_winner: bool
:return: int
"""
if is_winner:
return int(wagered * payout)
return -wagered
def save_and_update_user(self, user):
"""
Commit the bet and update the user's information.
:return: SQLAlchemy save result
"""
self.save()
user.coins += self.net
user.last_bet_on = tzware_datetime()
return user.save()
def to_json(self):
"""
Return JSON fields to represent a bet.
:return: dict
"""
params = {
'guess': self.guess,
'die_1': self.die_1,
'die_2': self.die_2,
'roll': self.roll,
'wagered': self.wagered,
'payout': self.payout,
'net': self.net,
'is_winner': Bet.is_winner(self.guess, self.roll)
}
return params
class TrnstnSitutn(db.Model):
"""
거래현황 모델 정의 클래스
실거래가 메뉴에서 사용하며
해마다 대량으로 적재를 하기 때문에
DB에서는 연 단위로 테이블 파티셔닝하여 사용한다.
"""
__bind_key__ = 'gisdb'
__tablename__ = 'trnstn_situtn' # 거래현황
id = db.Column(db.Integer,
db.Sequence('trnstn_situtn_id_seq'),
primary_key=True) # 시퀀스아이디
geom = db.Column(Geometry(geometry_type='POINT', srid=4326)) # 지오메트리
sid_cd = db.Column(db.String(2), nullable=False) # 시도코드
sgg_cd = db.Column(db.String(5), nullable=False) # 시군구코드
emd_cd = db.Column(db.String(10), nullable=False) # 읍면동코드
trnstn_yyyymm = db.Column(db.String(6), nullable=False) # 거래년월(yyyymm)
trnstn_clsftn_cd = db.Column(db.String(20), nullable=False) # 거래구분코드
house_clsftn_cd = db.Column(db.String(2), nullable=False) # 가구구분코드
cnstrtn_year = db.Column(db.String(4)) # 계약연도(yyyy)
floor_num = db.Column(db.Integer) # 층수
cntrct_momnt = db.Column(db.String(6)) # 계약기간
cntrct_area = db.Column(db.Float) # 계약면적
exclsv_area = db.Column(db.Float) # 전용면적
land_area = db.Column(db.Float) # 대지면적
sale_price = db.Column(db.Integer) # 매매가
deposit = db.Column(db.Integer) # 보증금
mnthly_rent = db.Column(db.Integer) # 월세
prcl_addrs = db.Column(db.String(120)) # 지번주소
road_addrs = db.Column(db.String(120)) # 도로명주소
bldng_nm = db.Column(db.String(80)) # 건물명
main_num = db.Column(db.Integer) # 본번
sub_num = db.Column(db.Integer) # 부번
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(TrnstnSitutn, self).__init__(**kwargs)
@classmethod
def find_by_identity(cls, identity):
return TrnstnSitutn.query. \
filter(TrnstnSitutn.id == identity).first()
class ResourceMixin(object):
# Keep track when records are created and updated.
created_on = db.Column(AwareDateTime(), default=tzware_datetime)
updated_on = db.Column(AwareDateTime(),
default=tzware_datetime,
onupdate=tzware_datetime)
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'created_on'
if direction not in ('asc', 'desc'):
direction = 'asc'
return field, direction
@classmethod
def get_bulk_action_ids(cls, scope, ids, omit_ids=[], query=''):
"""
Determine which IDs are to be modified.
:param scope: Affect all or only a subset of items
:type scope: str
:param ids: List of ids to be modified
:type ids: list
:param omit_ids: Remove 1 or more IDs from the list
:type omit_ids: list
:param query: Search query (if applicable)
:type query: str
:return: list
"""
omit_ids = map(str, omit_ids)
if scope == 'all_search_results':
# Change the scope to go from selected ids to all search results.
ids = cls.query.with_entities(cls.id).filter(cls.search(query))
# SQLAlchemy returns back a list of tuples, we want a list of strs.
ids = [str(item[0]) for item in ids]
# Remove 1 or more items from the list, this could be useful in spots
# where you may want to protect the current user from deleting themself
# when bulk deleting user accounts.
if omit_ids:
ids = [id for id in ids if id not in omit_ids]
return ids
@classmethod
def bulk_delete(cls, ids):
"""
Delete 1 or more model instances.
:param ids: List of ids to be deleted
:type ids: list
:return: Number of deleted instances
"""
delete_count = cls.query.filter(
cls.id.in_(ids)).delete(synchronize_session=False)
db.session.commit()
return delete_count
def save(self):
"""
Save a model instance.
:return: Model instance
"""
db.session.add(self)
db.session.commit()
return self
def delete(self):
"""
Delete a model instance.
:return: db.session.commit()'s result
"""
db.session.delete(self)
return db.session.commit()
def __str__(self):
"""
Create a human readable version of a class instance.
:return: self
"""
obj_id = hex(id(self))
columns = self.__table__.c.keys()
values = ', '.join("%s=%r" % (n, getattr(self, n)) for n in columns)
return '<%s %s(%s)>' % (obj_id, self.__class__.__name__, values)
class Subscription(ResourceMixin, db.Model):
__tablename__ = 'subscriptions'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
user_id = db.Column(db.Integer, db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False)
# Subscription details.
plan = db.Column(db.String(128))
coupon = db.Column(db.String(128))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Subscription, self).__init__(**kwargs)
@classmethod
def get_plan_by_id(cls, plan):
"""
Pick the plan based on the plan identifier.
:param plan: Plan identifier
:type plan: str
:return: dict or None
"""
for key, value in settings.STRIPE_PLANS.items():
if value.get('id') == plan:
return settings.STRIPE_PLANS[key]
return None
@classmethod
def get_new_plan(cls, keys):
"""
Pick the plan based on the plan identifier.
:param keys: Keys to look through
:type keys: list
:return: str or None
"""
for key in keys:
split_key = key.split('submit_')
if isinstance(split_key, list) and len(split_key) == 2:
if Subscription.get_plan_by_id(split_key[1]):
return split_key[1]
return None
def create(self, user=None, name=None, plan=None, coupon=None, token=None):
"""
Create a recurring subscription.
:param user: User to apply the subscription to
:type user: User instance
:param name: User's billing name
:type name: str
:param plan: Plan identifier
:type plan: str
:param coupon: Coupon code to apply
:type coupon: str
:param token: Token returned by JavaScript
:type token: str
:return: bool
"""
if token is None:
return False
if coupon:
self.coupon = coupon.upper()
customer = PaymentCustomer.create(token=token,
email=user.email,
plan=plan,
coupon=self.coupon)
# Update the user account.
user.payment_id = customer.id
user.name = name
user.previous_plan = plan
user.coins = add_subscription_coins(user.coins,
Subscription.get_plan_by_id(
user.previous_plan),
Subscription.get_plan_by_id(plan),
user.cancelled_subscription_on)
user.cancelled_subscription_on = None
# Set the subscription details.
self.user_id = user.id
self.plan = plan
# Redeem the coupon.
if coupon:
coupon = Coupon.query.filter(Coupon.code == self.coupon).first()
coupon.redeem()
# Create the credit card.
credit_card = CreditCard(user_id=user.id,
**CreditCard.extract_card_params(customer))
db.session.add(user)
db.session.add(credit_card)
db.session.add(self)
db.session.commit()
return True
def update(self, user=None, coupon=None, plan=None):
"""
Update an existing subscription.
:param user: User to apply the subscription to
:type user: User instance
:param coupon: Coupon code to apply
:type coupon: str
:param plan: Plan identifier
:type plan: str
:return: bool
"""
PaymentSubscription.update(user.payment_id, coupon, plan)
user.previous_plan = user.subscription.plan
user.subscription.plan = plan
user.coins = add_subscription_coins(user.coins,
Subscription.get_plan_by_id(
user.previous_plan),
Subscription.get_plan_by_id(plan),
user.cancelled_subscription_on)
if coupon:
user.subscription.coupon = coupon
coupon = Coupon.query.filter(Coupon.code == coupon).first()
if coupon:
coupon.redeem()
db.session.add(user.subscription)
db.session.commit()
return True
def cancel(self, user=None, discard_credit_card=True):
"""
Cancel an existing subscription.
:param user: User to apply the subscription to
:type user: User instance
:param discard_credit_card: Delete the user's credit card
:type discard_credit_card: bool
:return: bool
"""
PaymentSubscription.cancel(user.payment_id)
user.payment_id = None
user.cancelled_subscription_on = datetime.datetime.now(pytz.utc)
user.previous_plan = user.subscription.plan
db.session.add(user)
db.session.delete(user.subscription)
# Explicitly delete the credit card because the FK is on the
# user, not subscription so we can't depend on cascading deletes.
# This is for cases where you may want to keep a user's card
# on file even if they cancelled.
if discard_credit_card:
db.session.delete(user.credit_card)
db.session.commit()
return True
def update_payment_method(self, user=None, credit_card=None,
name=None, token=None):
"""
Update the subscription.
:param user: User to modify
:type user: User instance
:param credit_card: Card to modify
:type credit_card: Credit Card instance
:param name: User's billing name
:type name: str
:param token: Token returned by JavaScript
:type token: str
:return: bool
"""
if token is None:
return False
customer = PaymentCard.update(user.payment_id, token)
user.name = name
# Update the credit card.
new_card = CreditCard.extract_card_params(customer)
credit_card.brand = new_card.get('brand')
credit_card.last4 = new_card.get('last4')
credit_card.exp_date = new_card.get('exp_date')
credit_card.is_expiring = new_card.get('is_expiring')
db.session.add(user)
db.session.add(credit_card)
db.session.commit()
return True
class HsholdStats(db.Model):
"""
세대통계 모델 정의 클래스
지역현황 > 세대통계 메뉴에서 사용하며
해마다 대량으로 적재를 하기 때문에
DB에서는 연 단위로 테이블 파티셔닝하여 사용한다.
"""
__bind_key__ = 'gisdb'
__tablename__ = 'hshold_stats' # 세대통계
id = db.Column(db.Integer, primary_key=True) # 시퀀스아이디
sid_cd = db.Column(db.String(2), nullable=False) # 시도코드
sgg_cd = db.Column(db.String(5), nullable=False) # 시군구코드
srvy_year = db.Column(db.String(4), nullable=False) # 조사년도
rsdnc_clsftn_cd = db.Column(db.String(20), nullable=False) # 거처분류코드
fmly_num_cd = db.Column(db.String(20), nullable=False) # 세대원수코드
room_num_cd = db.Column(db.String(20), nullable=False) # 방개수코드
hshold_num = db.Column(db.Integer, nullable=False) # 세대수
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(HsholdStats, self).__init__(**kwargs)
@classmethod
def find_by_filter_for_map(cls, sid_cds, sgg_cds, rsdnc_clsftn_cds,
fmly_num_cds, room_num_cds, st_year, ed_yer):
# 서브 쿼리
geojson = db.session.query(func.ST_AsGeoJSON(func.ST_Centroid(LawSidArea.geom)).label('geojson')). \
filter(LawSidArea.sid_cd == cls.sid_cd).limit(1).label('geojson')
results = db.session.query(geojson,
func.sum(cls.hshold_num).label('hshold_sum')). \
filter(or_(cls.sid_cd.in_(sid_cds),
cls.sgg_cd.in_(sgg_cds))). \
filter(cls.rsdnc_clsftn_cd.in_(rsdnc_clsftn_cds)). \
filter(cls.fmly_num_cd.in_(fmly_num_cds)). \
filter(cls.room_num_cd.in_(room_num_cds)). \
filter(and_(cls.srvy_year >= st_year,
cls.srvy_year <= ed_yer)). \
group_by(cls.sid_cd)
return results
@classmethod
def find_by_filter_for_grid(cls, sid_cds, sgg_cds, rsdnc_clsftn_cds,
fmly_num_cds, room_num_cds, st_year, ed_yer):
results = db.session.query(cls.srvy_yyyy,
db.session.query(LawSidArea.sid_ko_nm).
filter(LawSidArea.sid_cd == cls.in_sid_cd).limit(1).label('sid'),
db.session.query(LawSggArea.sgg_ko_nm).
filter(LawSggArea.sgg_cd == cls.in_sgg_cd).limit(1).label('sgg'),
db.session.query(Code.name).
filter(and_(Code.code == cls.rsdnc_clsftn_cd,
Code.group_code == 'rsdnc_clsftn')).limit(1).label('rsdnc_clsftn'),
db.session.query(Code.name).
filter(and_(Code.code == cls.fmly_num_cd,
Code.group_code == 'fmly_num')).limit(1).label('fmly_num'),
cls.aplcnt_age,
db.session.query(Code.name).
filter(and_(Code.code == cls.room_num_cd,
Code.group_code == 'room_num')).limit(1).label('room_num'),
cls.hshold_num). \
filter(or_(cls.sid_cd.in_(sid_cds),
cls.sgg_cd.in_(sgg_cds))). \
filter(cls.rsdnc_clsftn_cd.in_(rsdnc_clsftn_cds)). \
filter(cls.fmly_num_cd.in_(fmly_num_cds)). \
filter(cls.room_num_cd.in_(room_num_cds)). \
filter(and_(cls.srvy_yyyy >= st_year,
cls.srvy_year <= ed_yer)). \
order_by(desc(cls.srvy_yyyy, cls.sid_cd, cls.sgg_cd))
return results
class Invoice(ResourceMixin, db.Model):
__tablename__ = 'invoices'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
# Invoice details.
plan = db.Column(db.String(128), index=True)
receipt_number = db.Column(db.String(128), index=True)
description = db.Column(db.String(128))
period_start_on = db.Column(db.Date)
period_end_on = db.Column(db.Date)
currency = db.Column(db.String(8))
tax = db.Column(db.Integer())
tax_percent = db.Column(db.Float())
total = db.Column(db.Integer())
# De-normalize the card details so we can render a user's history properly
# even if they have no active subscription or changed cards at some point.
brand = db.Column(db.String(32))
last4 = db.Column(db.Integer)
exp_date = db.Column(db.Date, index=True)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Invoice, self).__init__(**kwargs)
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
from hms.blueprints.user.models import User
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (User.email.ilike(search_query),
User.username.ilike(search_query))
return or_(*search_chain)
@classmethod
def parse_from_event(cls, payload):
"""
Parse and return the invoice information that will get saved locally.
:return: dict
"""
data = payload['data']['object']
plan_info = data['lines']['data'][0]['plan']
period_start_on = datetime.datetime.utcfromtimestamp(
data['lines']['data'][0]['period']['start']).date()
period_end_on = datetime.datetime.utcfromtimestamp(
data['lines']['data'][0]['period']['end']).date()
invoice = {
'payment_id': data['customer'],
'plan': plan_info['name'],
'receipt_number': data['receipt_number'],
'description': plan_info['statement_descriptor'],
'period_start_on': period_start_on,
'period_end_on': period_end_on,
'currency': data['currency'],
'tax': data['tax'],
'tax_percent': data['tax_percent'],
'total': data['total']
}
return invoice
@classmethod
def parse_from_api(cls, payload):
"""
Parse and return the invoice information we are interested in.
:return: dict
"""
plan_info = payload['lines']['data'][0]['plan']
date = datetime.datetime.utcfromtimestamp(payload['date'])
invoice = {
'plan': plan_info['name'],
'description': plan_info['statement_descriptor'],
'next_bill_on': date,
'amount_due': payload['amount_due'],
'interval': plan_info['interval']
}
return invoice
@classmethod
def prepare_and_save(cls, parsed_event):
"""
Potentially save the invoice after argument the event fields.
:param parsed_event: Event params to be saved
:type parsed_event: dict
:return: User instance
"""
# Avoid circular imports.
from hms.blueprints.user.models import User
# Only save the invoice if the user is valid at this point.
id = parsed_event.get('payment_id')
user = User.query.filter((User.payment_id == id)).first()
if user and user.credit_card:
parsed_event['user_id'] = user.id
parsed_event['brand'] = user.credit_card.brand
parsed_event['last4'] = user.credit_card.last4
parsed_event['exp_date'] = user.credit_card.exp_date
del parsed_event['payment_id']
invoice = Invoice(**parsed_event)
invoice.save()
return user
@classmethod
def upcoming(cls, customer_id):
"""
Return the upcoming invoice item.
:param customer_id: Stripe customer id
:type customer_id: int
:return: Stripe invoice object
"""
invoice = PaymentInvoice.upcoming(customer_id)
return Invoice.parse_from_api(invoice)
def create(self,
user=None,
currency=None,
amount=None,
coins=None,
coupon=None,
token=None):
"""
Create an invoice item.
:param user: User to apply the subscription to
:type user: User instance
:param amount: Stripe currency
:type amount: str
:param amount: Amount in cents
:type amount: int
:param coins: Amount of coins
:type coins: int
:param coupon: Coupon code to apply
:type coupon: str
:param token: Token returned by JavaScript
:type token: str
:return: bool
"""
if token is None:
return False
customer = PaymentCustomer.create(token=token, email=user.email)
if coupon:
self.coupon = coupon.upper()
coupon = Coupon.query.filter(Coupon.code == self.coupon).first()
amount = coupon.apply_discount_to(amount)
charge = PaymentCharge.create(customer.id, currency, amount)
# Redeem the coupon.
if coupon:
coupon.redeem()
# Add the coins to the user.
user.coins += coins
# Create the invoice item.
period_on = datetime.datetime.utcfromtimestamp(charge.get('created'))
card_params = CreditCard.extract_card_params(customer)
self.user_id = user.id
self.plan = '—'
self.receipt_number = charge.get('receipt_number')
self.description = charge.get('statement_descriptor')
self.period_start_on = period_on
self.period_end_on = period_on
self.currency = charge.get('currency')
self.tax = None
self.tax_percent = None
self.total = charge.get('amount')
self.brand = card_params.get('brand')
self.last4 = card_params.get('last4')
self.exp_date = card_params.get('exp_date')
db.session.add(user)
db.session.add(self)
db.session.commit()
return True
class CreditCard(ResourceMixin, db.Model):
IS_EXPIRING_THRESHOLD_MONTHS = 2
__tablename__ = 'credit_cards'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False)
# Card details.
brand = db.Column(db.String(32))
last4 = db.Column(db.Integer)
exp_date = db.Column(db.Date, index=True)
is_expiring = db.Column(db.Boolean(), nullable=False, server_default='0')
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(CreditCard, self).__init__(**kwargs)
@classmethod
def is_expiring_soon(cls, compare_date=None, exp_date=None):
"""
Determine whether or not this credit card is expiring soon.
:param compare_date: Date to compare at
:type compare_date: date
:param exp_date: Expiration date
:type exp_date: date
:return: bool
"""
return exp_date <= timedelta_months(
CreditCard.IS_EXPIRING_THRESHOLD_MONTHS, compare_date=compare_date)
@classmethod
def mark_old_credit_cards(cls, compare_date=None):
"""
Mark credit cards that are going to expire soon or have expired.
:param compare_date: Date to compare at
:type compare_date: date
:return: Result of updating the records
"""
today_with_delta = timedelta_months(
CreditCard.IS_EXPIRING_THRESHOLD_MONTHS, compare_date)
CreditCard.query.filter(CreditCard.exp_date <= today_with_delta) \
.update({CreditCard.is_expiring: True})
return db.session.commit()
@classmethod
def extract_card_params(cls, customer):
"""
Extract the credit card info from a payment customer object.
:param customer: Payment customer
:type customer: Payment customer
:return: dict
"""
card_data = customer.sources.data[0]
exp_date = datetime.date(card_data.exp_year, card_data.exp_month, 1)
card = {
'brand': card_data.brand,
'last4': card_data.last4,
'exp_date': exp_date,
'is_expiring': CreditCard.is_expiring_soon(exp_date=exp_date)
}
return card
class User(UserMixin,db.Model):
__tablename__ = 'users'
# Unique identifier
id = db.Column(db.Integer, primary_key=True, unique=True)
# Patient data
firstName = db.Column(db.String(32), nullable = False)
lastName = db.Column(db.String(32), nullable = False)
otherName = db.Column(db.String(32), nullable = True, server_default='')
date_or_birth = db.Column(db.DateTime(), nullable = True)
state_of_origin = db.Column(db.String(64), nullable=True)
place_of_birth = db.Column(db.String(64), nullable=True)
address = db.Column(db.String(120), nullable = True)
gender = db.Column(db.String(10), nullable = True)
# Patient login credentials
username = db.Column(db.String(64), nullable = True, unique = True)
email = db.Column(db.String(120), nullable=False, unique = True)
password_hash = db.Column(db.String(128))
# Account status
active = db.Column(db.Integer, server_default = '1', nullable = False)
confirmed = db.Column(db.Boolean(), default = False)
def __init__(self, **kwargs):
super().__init__(**kwargs)
@property
def password(self):
raise AttributeError("password is not a readable attribute")
@password.setter
def password(self, password):
self.password_hash = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.password_hash, password)
def is_active(self):
""" Help to check whether the account is still active or not."""
return self.active
@classmethod
def find_by_identity(cls, identity):
return User.query.filter((User.email == identity ) | (User.username == identity)).first()
def serialize_confirmation_token(self, expiration=500):
s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration)
return s.dumps({'confirm' : self.id}).decode('utf-8')
def disserialize_confirmation_token(self, token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return False
if data.get('confirm') != self.id:
return False
self.confirmed = True
db.session.commit()
return User.query.get(data.get('confirm'))
def generate_reset_password(self, expiration=500):
s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration)
return s.dumps({'confirm' : self.id}).decode('utf-8')
@staticmethod
def reset_pasword(token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token.encode('utf-8'))
except:
return False
return User.query.get(data.get('confirm'))
def generate_email_token(self, email, expiration=500):
s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration)
return s.dumps({'confirm' : self.id, 'new_email' : email})
def confirm_email_token(self, token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token.encode('utf-8'))
except:
return False
user = User.query.get(data.get('confirm'))
new_email = User.query.get(data.get('new_email'))
if data.get('confirm') != self.id:
return False
if User.query.filter_by(email = new_email ).first():
return False
if user is None:
return False
user.email = new_email
db.session.add(self)
return True
class PopltnMvmt(db.Model):
"""
인구이동 모델 정의 클래스
인구이동 메뉴에서 사용하며
해마다 대량으로 적재를 하기 때문에
DB에서는 연 단위로 테이블 파티셔닝하여 사용한다.
"""
__bind_key__ = 'gisdb'
__tablename__ = 'popltn_mvmt' # 인구이동
id = db.Column(db.Integer, primary_key=True) # 시퀀스아이디
in_sid_cd = db.Column(db.String(2), nullable=False) # 전입시도코드
in_sgg_cd = db.Column(db.String(5), nullable=False) # 전입시군구코드
in_emd_cd = db.Column(db.String(10), nullable=False) # 전입읍면동코드
in_yyyymm = db.Column(db.String(6), nullable=False) # 전입년월
out_sid_cd = db.Column(db.String(2), nullable=False) # 전출시도코드
out_sgg_cd = db.Column(db.String(5), nullable=False) # 전출시군구코드
out_emd_cd = db.Column(db.String(10), nullable=False) # 전출읍면동코드
mv_reasn_cd = db.Column(db.String(20), nullable=False) # 이동사유코드
aplcnt_clsftn_cd = db.Column(db.String(20), nullable=False) # 신청인구분코드
aplcnt_age = db.Column(db.Integer) # 신청인나이
aplcnt_sex_cd = db.Column(db.String(20), nullable=False) # 신청인성별코드
fmly_num = db.Column(db.Integer, nullable=False) # 세대수
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(PopltnMvmt, self).__init__(**kwargs)
@classmethod
def find_by_filter_for_map(cls, out_sid_cds, out_sgg_cds, out_emd_cds, in_sid_cd, in_sgg_cd, in_emd_cd,
mv_reasn_cds, aplcnt_ages, aplcnt_sex_cds, fmly_nums, st_yyyymm, ed_yyyymm):
# 서브 쿼리
geojson = db.session.query(func.ST_AsGeoJSON(func.ST_Centroid(LawSidArea.geom)).label('geojson')). \
filter(LawSidArea.sid_cd == cls.in_sid_cd).limit(1).label('geojson')
results = db.session.query(geojson,
func.count(cls.fmly_num).label('hshold_cnt'),
func.sum(cls.fmly_num).label('fmly_sum')). \
filter(or_(cls.out_sid_cd.in_(out_sid_cds),
cls.out_sgg_cd.in_(out_sgg_cds),
cls.out_emd_cd.in_(out_emd_cds))). \
filter(cls.aplcnt_age.in_(aplcnt_ages)). \
filter(cls.aplcnt_sex_cd.in_(aplcnt_sex_cds)). \
filter(cls.mv_reasn_cd.in_(mv_reasn_cds)). \
filter(cls.fmly_num.in_(fmly_nums)). \
filter(or_(cls.in_sid_cd == in_sid_cd,
cls.in_sgg_cd == in_sgg_cd,
cls.in_emd_cd == in_emd_cd)). \
filter(and_(cls.in_yyyymm >= st_yyyymm,
cls.in_yyyymm <= ed_yyyymm)). \
group_by(cls.in_sid_cd)
return results
@classmethod
def find_by_filter_for_grid(cls, out_sid_cds, out_sgg_cds, out_emd_cds, in_sid_cd, in_sgg_cd, in_emd_cd,
mv_reasn_cds, aplcnt_ages, aplcnt_sex_cds, fmly_nums, st_yyyymm, ed_yyyymm):
results = db.session.query(cls.in_yyyymm,
db.session.query(LawSidArea.sid_ko_nm).
filter(LawSidArea.sid_cd == cls.in_sid_cd).limit(1).label('in_sid'),
db.session.query(LawSggArea.sgg_ko_nm).
filter(LawSggArea.sgg_cd == cls.in_sgg_cd).limit(1).label('in_sgg'),
db.session.query(LawEmdArea.emd_ko_nm).
filter(LawEmdArea.emd_cd == cls.in_emd_cd).limit(1).label('in_emd'),
db.session.query(LawSidArea.sid_ko_nm).
filter(LawSidArea.sid_cd == cls.out_sid_cd).limit(1).label('out_sid'),
db.session.query(LawSggArea.sgg_ko_nm).
filter(LawSggArea.sgg_cd == cls.out_sgg_cd).limit(1).label('out_sgg'),
db.session.query(LawEmdArea.emd_ko_nm).
filter(LawEmdArea.emd_cd == cls.out_emd_cd).limit(1).label('out_emd'),
db.session.query(Code.name).
filter(and_(Code.code == cls.mv_reasn_cd,
Code.group_code == 'mv_reasn')).limit(1).label('mv_reasn'),
db.session.query(Code.name).
filter(and_(Code.code == cls.aplcnt_clsftn_cd,
Code.group_code == 'aplcnt_clsftn')).limit(1).label('aplcnt_clsftn'),
cls.aplcnt_age,
db.session.query(Code.name).
filter(and_(Code.code == cls.aplcnt_sex_cd,
Code.group_code == 'aplcnt_sex')).limit(1).label('aplcnt_sex'),
cls.fmly_num). \
filter(or_(cls.out_sid_cd.in_(out_sid_cds),
cls.out_sgg_cd.in_(out_sgg_cds),
cls.out_emd_cd.in_(out_emd_cds))). \
filter(cls.aplcnt_age.in_(aplcnt_ages)). \
filter(cls.aplcnt_sex_cd.in_(aplcnt_sex_cds)). \
filter(cls.mv_reasn_cd.in_(mv_reasn_cds)). \
filter(cls.fmly_num.in_(fmly_nums)). \
filter(or_(cls.in_sid_cd == in_sid_cd,
cls.in_sgg_cd == in_sgg_cd,
cls.in_emd_cd == in_emd_cd)). \
filter(and_(cls.in_yyyymm >= st_yyyymm,
cls.in_yyyymm <= ed_yyyymm)). \
order_by(desc(cls.in_yyyymm))
return results
class User(UserMixin, ResourceMixin, db.Model):
ROLE = OrderedDict([('member', 'Member'), ('admin', 'Admin')])
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
credit_card = db.relationship(CreditCard,
uselist=False,
backref='users',
passive_deletes=True)
subscription = db.relationship(Subscription,
uselist=False,
backref='users',
passive_deletes=True)
invoices = db.relationship(Invoice, backref='users', passive_deletes=True)
bets = db.relationship(Bet, backref='bets', passive_deletes=True)
posts = db.relationship(Post, backref='users', passive_deletes=True)
# Authentication.
role = db.Column(db.Enum(*ROLE, name='role_types', native_enum=False),
index=True,
nullable=False,
server_default='member')
active = db.Column('is_active',
db.Boolean(),
nullable=False,
server_default='1')
username = db.Column(db.String(24), unique=True, index=True)
email = db.Column(db.String(255),
unique=True,
index=True,
nullable=False,
server_default='')
password = db.Column(db.String(128), nullable=False, server_default='')
# Billing.
name = db.Column(db.String(128), index=True)
payment_id = db.Column(db.String(128), index=True)
cancelled_subscription_on = db.Column(AwareDateTime())
previous_plan = db.Column(db.String(128))
# Bet.
coins = db.Column(db.BigInteger())
last_bet_on = db.Column(AwareDateTime())
# Activity tracking.
sign_in_count = db.Column(db.Integer, nullable=False, default=0)
current_sign_in_on = db.Column(AwareDateTime())
current_sign_in_ip = db.Column(db.String(45))
last_sign_in_on = db.Column(AwareDateTime())
last_sign_in_ip = db.Column(db.String(45))
# Additional settings.
locale = db.Column(db.String(5), nullable=False, server_default='en')
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(User, self).__init__(**kwargs)
self.password = User.encrypt_password(kwargs.get('password', ''))
self.coins = 100
@classmethod
def find_by_identity(cls, identity):
"""
Find a user by their e-mail or username.
:param identity: Email or username
:type identity: str
:return: User instance
"""
return User.query.filter((User.email == identity)
| (User.username == identity)).first()
@classmethod
def encrypt_password(cls, plaintext_password):
"""
Hash a plaintext string using PBKDF2. This is good enough according
to the NIST (National Institute of Standards and Technology).
In other words while bcrypt might be superior in practice, if you use
PBKDF2 properly (which we are), then your passwords are safe.
:param plaintext_password: Password in plain text
:type plaintext_password: str
:return: str
"""
if plaintext_password:
return generate_password_hash(plaintext_password)
return None
@classmethod
def deserialize_token(cls, token):
"""
Obtain a user from de-serializing a signed token.
:param token: Signed token.
:type token: str
:return: User instance or None
"""
private_key = TimedJSONWebSignatureSerializer(
current_app.config['SECRET_KEY'])
try:
decoded_payload = private_key.loads(token)
return User.find_by_identity(decoded_payload.get('user_email'))
except Exception:
return None
@classmethod
def initialize_password_reset(cls, identity):
"""
Generate a token to reset the password for a specific user.
:param identity: User e-mail address or username
:type identity: str
:return: User instance
"""
u = User.find_by_identity(identity)
reset_token = u.serialize_token()
# This prevents circular imports.
from hms.blueprints.user.tasks import (deliver_password_reset_email)
deliver_password_reset_email.delay(u.id, reset_token)
return u
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (User.email.ilike(search_query),
User.username.ilike(search_query))
return or_(*search_chain)
@classmethod
def is_last_admin(cls, user, new_role, new_active):
"""
Determine whether or not this user is the last admin account.
:param user: User being tested
:type user: User
:param new_role: New role being set
:type new_role: str
:param new_active: New active status being set
:type new_active: bool
:return: bool
"""
is_changing_roles = user.role == 'admin' and new_role != 'admin'
is_changing_active = user.active is True and new_active is None
if is_changing_roles or is_changing_active:
admin_count = User.query.filter(User.role == 'admin').count()
active_count = User.query.filter(User.is_active is True).count()
if admin_count == 1 or active_count == 1:
return True
return False
@classmethod
def bulk_delete(cls, ids):
"""
Override the general bulk_delete method because we need to delete them
one at a time while also deleting them on Stripe.
:param ids: List of ids to be deleted
:type ids: list
:return: int
"""
delete_count = 0
for id in ids:
user = User.query.get(id)
if user is None:
continue
if user.payment_id is None:
user.delete()
else:
subscription = Subscription()
cancelled = subscription.cancel(user=user)
# If successful, delete it locally.
if cancelled:
user.delete()
delete_count += 1
return delete_count
def is_active(self):
"""
Return whether or not the user account is active, this satisfies
Flask-Login by overwriting the default value.
:return: bool
"""
return self.active
def get_auth_token(self):
"""
Return the user's auth token. Use their password as part of the token
because if the user changes their password we will want to invalidate
all of their logins across devices. It is completely fine to use
md5 here as nothing leaks.
This satisfies Flask-Login by providing a means to create a token.
:return: str
"""
private_key = current_app.config['SECRET_KEY']
serializer = URLSafeTimedSerializer(private_key)
data = [str(self.id), md5(self.password.encode('utf-8')).hexdigest()]
return serializer.dumps(data)
def authenticated(self, with_password=True, password=''):
"""
Ensure a user is authenticated, and optionally check their password.
:param with_password: Optionally check their password
:type with_password: bool
:param password: Optionally verify this as their password
:type password: str
:return: bool
"""
if with_password:
return check_password_hash(self.password, password)
return True
def serialize_token(self, expiration=3600):
"""
Sign and create a token that can be used for things such as resetting
a password or other tasks that involve a one off token.
:param expiration: Seconds until it expires, defaults to 1 hour
:type expiration: int
:return: JSON
"""
private_key = current_app.config['SECRET_KEY']
serializer = TimedJSONWebSignatureSerializer(private_key, expiration)
return serializer.dumps({'user_email': self.email}).decode('utf-8')
def update_activity_tracking(self, ip_address):
"""
Update various fields on the user that's related to meta data on their
account, such as the sign in count and ip address, etc..
:param ip_address: IP address
:type ip_address: str
:return: SQLAlchemy commit results
"""
self.sign_in_count += 1
self.last_sign_in_on = self.current_sign_in_on
self.last_sign_in_ip = self.current_sign_in_ip
self.current_sign_in_on = datetime.datetime.now(pytz.utc)
self.current_sign_in_ip = ip_address
return self.save()
def add_coins(self, plan):
"""
Add an amount of coins to an existing user.
:param plan: Subscription plan
:type plan: str
:return: SQLAlchemy commit results
"""
self.coins += plan['metadata']['coins']
return self.save()
class Coupon(ResourceMixin, db.Model):
DURATION = OrderedDict([
('forever', 'Forever'),
('once', 'Once'),
('repeating', 'Repeating')
])
__tablename__ = 'coupons'
id = db.Column(db.Integer, primary_key=True)
# Coupon details.
code = db.Column(db.String(128), index=True, unique=True)
duration = db.Column(db.Enum(*DURATION, name='duration_types'),
index=True, nullable=False, server_default='forever')
amount_off = db.Column(db.Integer())
percent_off = db.Column(db.Integer())
currency = db.Column(db.String(8))
duration_in_months = db.Column(db.Integer())
max_redemptions = db.Column(db.Integer(), index=True)
redeem_by = db.Column(AwareDateTime(), index=True)
times_redeemed = db.Column(db.Integer(), index=True,
nullable=False, default=0)
valid = db.Column(db.Boolean(), nullable=False, server_default='1')
def __init__(self, **kwargs):
if self.code:
self.code = self.code.upper()
else:
self.code = Coupon.random_coupon_code()
# Call Flask-SQLAlchemy's constructor.
super(Coupon, self).__init__(**kwargs)
@hybrid_property
def redeemable(self):
"""
Return coupons that are still redeemable. Coupons will become invalid
once they run out on save. We want to explicitly do a date check to
avoid having to hit Stripe's API to get back potentially valid codes.
:return: SQLAlchemy query object
"""
is_redeemable = or_(self.redeem_by.is_(None),
self.redeem_by >= datetime.datetime.now(pytz.utc))
return and_(self.valid, is_redeemable)
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
return or_(Coupon.code.ilike(search_query))
@classmethod
def random_coupon_code(cls):
"""
Create a human readable random coupon code.
:return: str
"""
charset = string.digits + string.ascii_uppercase
charset = charset.replace('B', '').replace('I', '')
charset = charset.replace('O', '').replace('S', '')
charset = charset.replace('0', '').replace('1', '')
random_chars = ''.join(choice(charset) for _ in range(14))
coupon_code = '{0}-{1}-{2}'.format(random_chars[0:4],
random_chars[5:9],
random_chars[10:14])
return coupon_code
@classmethod
def expire_old_coupons(cls, compare_datetime=None):
"""
Invalidate coupons that are past their redeem date.
:param compare_datetime: Time to compare at
:type compare_datetime: date
:return: The result of updating the records
"""
if compare_datetime is None:
compare_datetime = datetime.datetime.now(pytz.utc)
Coupon.query.filter(Coupon.redeem_by <= compare_datetime) \
.update({Coupon.valid: not Coupon.valid})
return db.session.commit()
@classmethod
def create(cls, params):
"""
Return whether or not the coupon was created successfully.
:return: bool
"""
payment_params = params
payment_params['code'] = payment_params['code'].upper()
if payment_params.get('amount_off'):
payment_params['amount_off'] = \
dollars_to_cents(payment_params['amount_off'])
PaymentCoupon.create(**payment_params)
if 'id' in payment_params:
payment_params['code'] = payment_params['id']
del payment_params['id']
if 'redeem_by' in payment_params:
if payment_params.get('redeem_by') is not None:
params['redeem_by'] = payment_params.get('redeem_by').replace(
tzinfo=pytz.UTC)
coupon = Coupon(**payment_params)
db.session.add(coupon)
db.session.commit()
return True
@classmethod
def bulk_delete(cls, ids):
"""
Override the general bulk_delete method because we need to delete them
one at a time while also deleting them on Stripe.
:param ids: List of ids to be deleted
:type ids: list
:return: int
"""
delete_count = 0
for id in ids:
coupon = Coupon.query.get(id)
if coupon is None:
continue
# Delete on Stripe.
stripe_response = PaymentCoupon.delete(coupon.code)
# If successful, delete it locally.
if stripe_response.get('deleted'):
coupon.delete()
delete_count += 1
return delete_count
@classmethod
def find_by_code(cls, code):
"""
Find a coupon by its code.
:param code: Coupon code to find
:type code: str
:return: Coupon instance
"""
formatted_code = code.upper()
coupon = Coupon.query.filter(Coupon.redeemable,
Coupon.code == formatted_code).first()
return coupon
def redeem(self):
"""
Update the redeem stats for this coupon.
:return: Result of saving the record
"""
self.times_redeemed += 1
if self.max_redemptions:
if self.times_redeemed >= self.max_redemptions:
self.valid = False
return db.session.commit()
def apply_discount_to(self, amount):
"""
Apply the discount to an amount.
:param amount: Amount in cents
:type amount: int
:return: int
"""
if self.amount_off:
amount -= self.amount_off
elif self.percent_off:
amount *= (1 - (self.percent_off * 0.01))
return int(amount)
def to_json(self):
"""
Return JSON fields to represent a coupon.
:return: dict
"""
params = {
'duration': self.duration,
'duration_in_months': self.duration_in_months,
}
if self.amount_off:
params['amount_off'] = cents_to_dollars(self.amount_off)
if self.percent_off:
params['percent_off'] = self.percent_off,
return params
class PopltnStats(db.Model):
"""
인구통계 모델 정의 클래스
인구통계 메뉴에서 사용하며
해마다 대량으로 적재를 하기 때문에
DB에서는 연 단위로 테이블 파티셔닝하여 사용한다.
"""
__bind_key__ = 'gisdb'
__tablename__ = 'popltn_stats' # 인구통계
id = db.Column(db.Integer, primary_key=True) # 시퀀스아이디
sid_cd = db.Column(db.String(2), nullable=False) # 시도코드
sgg_cd = db.Column(db.String(5), nullable=False) # 시군구코드
emd_cd = db.Column(db.String(10), nullable=False) # 읍면동코드
srvy_yyyymm = db.Column(db.String(6), nullable=False) # 조사년월(yyyymm)
age_grp_cd = db.Column(db.String(20), nullable=False) # 연령대코드
man_num = db.Column(db.Integer, nullable=False) # 남자수
woman_num = db.Column(db.Integer, nullable=False) # 여자수
total_num = db.Column(db.Integer, nullable=False) # 전체수
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(PopltnStats, self).__init__(**kwargs)
@classmethod
def find_by_identity(cls, identity):
return PopltnStats.query. \
filter(PopltnStats.id == identity).first()
@classmethod
def select_all(cls, sid_cds, sgg_cds, emd_cds, age_grp_cds, syear, smonth,
eyear, emonth):
sid_nm = db.session.query(LawSidArea.sid_ko_nm). \
filter(LawSidArea.sid_cd == cls.sid_cd). \
limit(1).label('area_nm')
sgg_nm = db.session.query(LawSggArea.sgg_ko_nm). \
filter(LawSggArea.sgg_cd == cls.sgg_cd). \
limit(1).label('area_nm')
emd_nm = db.session.query(LawEmdArea.emd_ko_nm). \
filter(LawEmdArea.emd_cd == cls.emd_cd). \
limit(1).label('area_nm')
sid_ps = db.session.query(sid_nm,
func.sum(PopltnStats.man_num).label('man_sum'),
func.sum(PopltnStats.woman_num).label('woman_sum'),
func.sum(PopltnStats.total_num).label('total_sum')). \
filter(and_(PopltnStats.sid_cd.in_(sid_cds),
PopltnStats.age_grp_cd.in_(age_grp_cds),
PopltnStats.srvy_yyyymm >= (syear + smonth),
PopltnStats.srvy_yyyymm <= (eyear + emonth))). \
group_by(PopltnStats.sid_cd). \
order_by(PopltnStats.sid_cd)
sgg_ps = db.session.query(sgg_nm,
func.sum(PopltnStats.man_num).label('man_sum'),
func.sum(PopltnStats.woman_num).label('woman_sum'),
func.sum(PopltnStats.total_num).label('total_sum')). \
filter(and_(PopltnStats.sigu_cd.in_(sgg_cds),
PopltnStats.age_grp_cd.in_(age_grp_cds),
PopltnStats.srvy_yyyymm >= (syear + smonth),
PopltnStats.srvy_yyyymm <= (eyear + emonth))). \
group_by(PopltnStats.sgg_cd). \
order_by(PopltnStats.sgg_cd)
emd_ps = db.session.query(emd_nm,
func.sum(PopltnStats.man_num).label('man_sum'),
func.sum(PopltnStats.woman_num).label('woman_sum'),
func.sum(PopltnStats.total_num).label('total_sum')). \
filter(and_(PopltnStats.emd_cd.in_(emd_cds),
PopltnStats.age_cd.in_(age_grp_cds),
PopltnStats.srvy_yyyymm >= (syear + smonth),
PopltnStats.srvy_yyyymm <= (eyear + emonth))). \
group_by(PopltnStats.emd_cd). \
order_by(PopltnStats.emd_cd)
return sid_ps.union_all(sgg_ps, emd_ps)
