def test_create(self):
"""
Tests the ShareRepository.create method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
self.assertEqual(member1.num_shares, 12 + 23)
share_id = ShareRepository.create('member1', 25, date(2016, 4, 23))
self.assertEqual(member1.num_shares, 12 + 23 + 25)
self.assertEqual(len(member1.shares), 3)
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == share_id).first()
self.assertEqual(shares.number, 25)
self.assertEqual(shares.date_of_acquisition, date(2016, 4, 23))
self.assertEqual(shares.reference_code, None)
self.assertEqual(shares.signature_received, False)
self.assertEqual(shares.signature_received_date, date(1970, 1, 1))
self.assertEqual(shares.signature_confirmed, False)
self.assertEqual(shares.signature_confirmed_date, date(1970, 1, 1))
self.assertEqual(shares.payment_received, False)
self.assertEqual(shares.payment_received_date, date(1970, 1, 1))
self.assertEqual(shares.payment_confirmed, False)
self.assertEqual(shares.payment_confirmed_date, date(1970, 1, 1))
self.assertEqual(shares.accountant_comment, None)
def test_get(self):
"""
Tests the ShareRepository.get method.
"""
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share1').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share2').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share3').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share4').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
def test_get(self):
"""
Tests the ShareRepository.get method.
"""
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share1').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share2').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share3').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share4').first()
get_share = ShareRepository.get(share.id)
self.assertEqual(get_share.id, share.id)
def test_create(self):
"""
Tests the ShareRepository.create method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
self.assertEqual(member1.num_shares, 12 + 23)
share_id = ShareRepository.create('member1', 25, date(2016, 4, 23))
self.assertEqual(member1.num_shares, 12 + 23 + 25)
self.assertEqual(len(member1.shares), 3)
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == share_id).first()
self.assertEqual(shares.number, 25)
self.assertEqual(shares.date_of_acquisition, date(2016, 4, 23))
self.assertEqual(shares.reference_code, None)
self.assertEqual(shares.signature_received, False)
self.assertEqual(shares.signature_received_date, date(1970, 1, 1))
self.assertEqual(shares.signature_confirmed, False)
self.assertEqual(shares.signature_confirmed_date, date(1970, 1, 1))
self.assertEqual(shares.payment_received, False)
self.assertEqual(shares.payment_received_date, date(1970, 1, 1))
self.assertEqual(shares.payment_confirmed, False)
self.assertEqual(shares.payment_confirmed_date, date(1970, 1, 1))
self.assertEqual(shares.accountant_comment, None)
def delete_by_id(cls, staff_id):
"""
Delete one C3sStaff object by id.
"""
row = DBSession.query(cls).filter(cls.id == staff_id).first()
row.groups = []
DBSession.query(cls).filter(cls.id == staff_id).delete()
def delete(cls, shares_id):
"""
Deletes the shares package of the specified shares id.
Args:
shares_id: The technical primary key of the shares package to be
deleted.
"""
# pylint: disable=no-member
DBSession.query(Shares).filter(Shares.id == shares_id).delete()
def delete(cls, shares_id):
"""
Deletes the shares package of the specified shares id.
Args:
shares_id: The technical primary key of the shares package to be
deleted.
"""
# pylint: disable=no-member
DBSession.query(Shares).filter(Shares.id == shares_id).delete()
def create(cls, membership_number, shares_quantity,
board_confirmation=None):
"""
Create a shares package.
Args:
membership_number: The membership number of the member for which
the shares package is created.
shares_quantity: The number of shares of the package to be created.
board_confirmation: Optional. The date on which the board of
directors confirmed the acquisition of the shares package.
Returns:
The technical primary key of the created shares package.
"""
shares = Shares(
number=shares_quantity,
date_of_acquisition=board_confirmation,
)
# pylint: disable=no-member
member = DBSession.query(C3sMember).filter(
C3sMember.membership_number == membership_number).first()
# pylint: disable=no-member
DBSession.add(shares)
member.shares.append(shares)
member.num_shares += shares_quantity
DBSession.flush()
return shares.id
def create(cls, membership_number, shares_quantity,
board_confirmation=None):
"""
Create a shares package.
Args:
membership_number: The membership number of the member for which
the shares package is created.
shares_quantity: The number of shares of the package to be created.
board_confirmation: Optional. The date on which the board of
directors confirmed the acquisition of the shares package.
Returns:
The technical primary key of the created shares package.
"""
shares = Shares(
number=shares_quantity,
date_of_acquisition=board_confirmation,
)
# pylint: disable=no-member
member = DBSession.query(C3sMember).filter(
C3sMember.membership_number == membership_number).first()
# pylint: disable=no-member
DBSession.add(shares)
member.shares.append(shares)
member.num_shares += shares_quantity
DBSession.flush()
return shares.id
def get_all(cls):
"""
Get all C3sStaff objects from the database.
Returns:
list: list of C3sStaff objects.
"""
return DBSession.query(cls).all()
def get_invitees(cls, general_assembly_number, invitees_count):
"""
Gets a number of members which have not yet been invited to the general
assembly.
Queries the database for members, where:
- members are members
- members have not received their invitation email yet
Args:
general_assembly_number: Integer. The number of the general
assembly for which the invitees are returned.
invitees_count: Integer. Number of invitees returned at maximum.
Returns:
A list member objects.
"""
# pylint: disable=no-member
# In SqlAlchemy the True comparison must be done as "a == True" and not
# in the python default way "a is True". Therefore:
# pylint: disable=singleton-comparison
return (
# Get members
DBSession.query(C3sMember)
# combine with the general assembly requested as a cross join with
# the one general assembly row
.join(
GeneralAssembly,
GeneralAssembly.number == general_assembly_number)
# combine them with invitations for this member to this general
# assembly if any
.outerjoin(
GeneralAssemblyInvitation,
and_(
C3sMember.id == GeneralAssemblyInvitation.member_id,
GeneralAssemblyInvitation.general_assembly_id ==
GeneralAssembly.id
)
)
# but only
.filter(
and_(
# if no invitation has been sent
GeneralAssemblyInvitation.id == None,
# and the member has membership at the assmebly date
C3sMember.is_member_filter(GeneralAssembly.date),
)
)
# get as many as requested
.slice(0, invitees_count)
# and get all of the actual records
.all()
)
def get_by_login(cls, login):
"""
Get C3sStaff object by login.
Args:
login: the login of the C3sStaff object to be returned.
Returns:
* **object**: C3sStaff object with relevant login, if exists.
* **None**: if login can't be found.
"""
return DBSession.query(cls).filter(cls.login == login).first()
def get_by_id(cls, staff_id):
"""
Get C3sStaff object by id.
Args:
id: the id of the C3sStaff object to be returned.
Returns:
* **object**: C3sStaff object with relevant id, if exists.
* **None**: if id can't be found.
"""
return DBSession.query(cls).filter(cls.id == staff_id).first()
def set_reference_code(cls, shares_id, reference_code):
"""
Sets the reference code of the shares package.
Args:
shares_id: The technical primary key of the shares package for
which the payment confirmation is set.
reference_code: The reference code which is set.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.reference_code = reference_code
def set_reference_code(cls, shares_id, reference_code):
"""
Sets the reference code of the shares package.
Args:
shares_id: The technical primary key of the shares package for
which the payment confirmation is set.
reference_code: The reference code which is set.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.reference_code = reference_code
def get(cls, shares_id):
"""
Gets the shares package for the specified shares id.
Args:
shares_id: The technical primary key of the shares package for
which the payment confirmation is set.
Returns:
The shares package for the specified shares id.
"""
# pylint: disable=no-member
return DBSession.query(Shares).filter(Shares.id == shares_id).first()
def get(cls, shares_id):
"""
Gets the shares package for the specified shares id.
Args:
shares_id: The technical primary key of the shares package for
which the payment confirmation is set.
Returns:
The shares package for the specified shares id.
"""
# pylint: disable=no-member
return DBSession.query(Shares).filter(Shares.id == shares_id).first()
def test_set_reference_code(self):
"""
Tests the ShareRepository.set_reference_code method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
shares = member1.shares[0]
ShareRepository.set_reference_code(shares.id, u'test_reference_code')
self.assertEqual(shares.reference_code, u'test_reference_code')
ShareRepository.set_reference_code(shares.id, None)
self.assertEqual(shares.reference_code, None)
def get_member(cls, membership_number):
"""
Gets the member of the specified membership number.
Args:
membership_number: The membership number of the member which is
returned.
Returns:
The membership of the specified membership number.
"""
# pylint: disable=no-member
return DBSession.query(C3sMember).filter(
C3sMember.membership_number == membership_number).first()
def get_member(cls, membership_number):
"""
Gets the member of the specified membership number.
Args:
membership_number: The membership number of the member which is
returned.
Returns:
The membership of the specified membership number.
"""
# pylint: disable=no-member
return DBSession.query(C3sMember).filter(
C3sMember.membership_number == membership_number).first()
def test_set_reference_code(self):
"""
Tests the ShareRepository.set_reference_code method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
shares = member1.shares[0]
ShareRepository.set_reference_code(shares.id, u'test_reference_code')
self.assertEqual(shares.reference_code, u'test_reference_code')
ShareRepository.set_reference_code(shares.id, None)
self.assertEqual(shares.reference_code, None)
def get_member_shares(cls, membership_number):
"""
Gets the share of a members.
Args:
membership_number: The membership number of the member of which the
shares are returned.
Returns:
The shares of the member.
"""
# pylint: disable=no-member
return DBSession.query(Shares) \
.join(members_shares) \
.join(C3sMember) \
.filter(C3sMember.membership_number == membership_number) \
.all()
def _members_query(cls, effective_date=None):
"""
Gets the query to retrieve members accepted until and including the
specified effective date.
Args:
effective_date: Optional. The date on which the membership has been
accepted. If not specified system date is used as effective
date.
Returns:
The query to retrieve members accepted until and including the
specified effective date.
"""
# pylint: disable=no-member
return DBSession.query(C3sMember) \
.filter(cls._is_member_filter(effective_date))
def get_member_by_id(cls, member_id):
"""
Gets the member of the specified member ID.
TODO: The member ID is a database internal ID and must not be exposed
from the data layer. Therefore, the implementation must be adjusted to
use the get_member method using the membership number.
Args:
member_id: The technical ID of the member which is returned.
Returns:
The membership of the specified member id.
"""
# pylint: disable=no-member
return DBSession.query(C3sMember).filter(
C3sMember.id == member_id).first()
def get_member_by_token(cls, token):
"""
Find a member by token used for GA and BarCamp
This is needed when a user returns from reading her email
and clicking on a link containing the token.
Returns:
object: C3sMember object
"""
# pylint: disable=no-member
return (
DBSession.query(C3sMember)
.join(GeneralAssemblyInvitation)
.filter(GeneralAssemblyInvitation.token == token)
.first()
)
def get_latest_general_assembly(cls):
"""
Get details of the latest general assembly
The latest general assembly is the one with the date later than all
other general assembly dates.
In case there are two general assemblies at the same ĺatest date an
unpredictable one of them in returned depending on factors like
creation date and implicit database ordering.
"""
# pylint: disable=no-member
latest_date = DBSession.query(func.max(GeneralAssembly.date)).scalar()
return DBSession \
.query(GeneralAssembly) \
.filter(GeneralAssembly.date == latest_date) \
.first()
def get_member_by_id(cls, member_id):
"""
Gets the member of the specified member ID.
TODO: The member ID is a database internal ID and must not be exposed
from the data layer. Therefore, the implementation must be adjusted to
use the get_member method using the membership number.
Args:
member_id: The technical ID of the member which is returned.
Returns:
The membership of the specified member id.
"""
# pylint: disable=no-member
return DBSession.query(C3sMember).filter(
C3sMember.id == member_id).first()
def get_member_shares(cls, membership_number):
"""
Gets the share of a members.
Args:
membership_number: The membership number of the member of which the
shares are returned.
Returns:
The shares of the member.
"""
# pylint: disable=no-member
return DBSession.query(Shares) \
.join(members_shares) \
.join(C3sMember) \
.filter(C3sMember.membership_number == membership_number) \
.all()
def set_payment_confirmation(cls, shares_id, confirmation_date=None):
"""
Sets the payment confirmation of the shares package.
Args:
shares_id: The technical ID of the shares package for which the
payment confirmation is set.
confirmation_date: Optional. The payment confirmation date which is
set. If not specified the payment confirmation date is unset.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.payment_confirmed_date = confirmation_date
shares.payment_confirmed = \
confirmation_date is not None \
and \
confirmation_date > date(1970, 1, 1)
def get_approved(cls, start_date, end_date):
"""
Gets all shares approved between and including both start date and end
date.
Args:
start_date: The first date for which approved shares are returned.
end_date: The last date for which approved shares are returned.
Returns:
All shares approved between and including both start date and end
date.
"""
# pylint: disable=no-member
return DBSession.query(Shares).filter(
expression.and_(
Shares.date_of_acquisition >= start_date,
Shares.date_of_acquisition <= end_date)).all()
def set_payment_confirmation(cls, shares_id, confirmation_date=None):
"""
Sets the payment confirmation of the shares package.
Args:
shares_id: The technical ID of the shares package for which the
payment confirmation is set.
confirmation_date: Optional. The payment confirmation date which is
set. If not specified the payment confirmation date is unset.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.payment_confirmed_date = confirmation_date
shares.payment_confirmed = \
confirmation_date is not None \
and \
confirmation_date > date(1970, 1, 1)
def get_accepted_members_count(cls, effective_date=None):
"""
Gets the number of members which have been accpeted until and including
the specified effective date.
Args:
effective_date: Optional. The date on which the membership has been
accepted. If not specified system date is used as effective
date.
Returns:
The number of members which have been accpeted until and including
the specified effective date.
"""
# pylint: disable=no-member
return DBSession.query(func.count(C3sMember.id)) \
.filter(cls._is_member_filter(effective_date)) \
.scalar()
def get_approved(cls, start_date, end_date):
"""
Gets all shares approved between and including both start date and end
date.
Args:
start_date: The first date for which approved shares are returned.
end_date: The last date for which approved shares are returned.
Returns:
All shares approved between and including both start date and end
date.
"""
# pylint: disable=no-member
return DBSession.query(Shares).filter(
expression.and_(
Shares.date_of_acquisition >= start_date,
Shares.date_of_acquisition <= end_date)).all()
def set_signature_confirmation(cls, shares_id, confirmation_date=None):
"""
Sets the signature reception date.
Args:
shares_id: The technical ID of the shares package for which the
signature reception date is set.
confirmation_date: Optional. The signature confirmation date to be
set to the share process. If not specified the signature
confirmation date is unset.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.signature_confirmed_date = confirmation_date
shares.signature_confirmed = (
confirmation_date is not None
and
confirmation_date > date(1970, 1, 1)
)
def set_signature_confirmation(cls, shares_id, confirmation_date=None):
"""
Sets the signature reception date.
Args:
shares_id: The technical ID of the shares package for which the
signature reception date is set.
confirmation_date: Optional. The signature confirmation date to be
set to the share process. If not specified the signature
confirmation date is unset.
"""
# pylint: disable=no-member
shares = DBSession.query(Shares).filter(Shares.id == shares_id).first()
shares.signature_confirmed_date = confirmation_date
shares.signature_confirmed = (
confirmation_date is not None
and
confirmation_date > date(1970, 1, 1)
)
def _accepted_members_query(cls, effective_date=None):
"""
Gets the query to retrieve members accepted until and including the
specified effective date.
Args:
effective_date: Optional. The date on which the membership has been
accepted. If not specified system date is used as effective
date.
Returns:
The query to retrieve members accepted until and including the
specified effective date.
"""
# pylint: disable=no-member
all_members_query = DBSession.query(C3sMember)
accepted_members_query = cls._filter_accepted_member(
all_members_query, effective_date)
return accepted_members_query
def get_accepted_members_count(cls, effective_date=None):
"""
Gets the number of members which have been accpeted until and including
the specified effective date.
Args:
effective_date: Optional. The date on which the membership has been
accepted. If not specified system date is used as effective
date.
Returns:
The number of members which have been accpeted until and including
the specified effective date.
"""
# pylint: disable=no-member
all_members_count_query = DBSession.query(func.count(C3sMember.id))
accepted_members_count_query = cls._filter_accepted_member(
all_members_count_query, effective_date)
return accepted_members_count_query.scalar()
def test_set_payment_confirmation(self):
"""
Tests the ShareRepository.set_payment_confirmation method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
shares = member1.shares[0]
ShareRepository.set_payment_confirmation(shares.id, date(2017, 4, 23))
self.assertEqual(shares.payment_confirmed_date, date(2017, 4, 23))
self.assertEqual(shares.payment_confirmed, True)
ShareRepository.set_payment_confirmation(shares.id)
self.assertEqual(shares.payment_confirmed_date, None)
self.assertEqual(shares.payment_confirmed, False)
ShareRepository.set_payment_confirmation(shares.id, date(2017, 4, 22))
self.assertEqual(shares.payment_confirmed_date, date(2017, 4, 22))
self.assertEqual(shares.payment_confirmed, True)
def test_set_payment_confirmation(self):
"""
Tests the ShareRepository.set_payment_confirmation method.
"""
# pylint: disable=no-member
member1 = DBSession.query(C3sMember).filter(
C3sMember.membership_number == 'member1').first()
shares = member1.shares[0]
ShareRepository.set_payment_confirmation(shares.id, date(2017, 4, 23))
self.assertEqual(shares.payment_confirmed_date, date(2017, 4, 23))
self.assertEqual(shares.payment_confirmed, True)
ShareRepository.set_payment_confirmation(shares.id)
self.assertEqual(shares.payment_confirmed_date, None)
self.assertEqual(shares.payment_confirmed, False)
ShareRepository.set_payment_confirmation(shares.id, date(2017, 4, 22))
self.assertEqual(shares.payment_confirmed_date, date(2017, 4, 22))
self.assertEqual(shares.payment_confirmed, True)
def get_approved_count(cls, start_date, end_date):
"""
Gets the number of all shares approved between and including both start
date and end date.
Args:
start_date: The first date for which approved shares are counted.
end_date: The last date for which approved shares are counted.
Returns:
The number of all shares approved between and including both start
date and end date.
"""
# pylint: disable=no-member
count = DBSession.query(func.sum(Shares.number)).filter(
expression.and_(
Shares.date_of_acquisition >= start_date,
Shares.date_of_acquisition <= end_date)).scalar()
if count is None:
count = 0
return count
def get_approved_count(cls, start_date, end_date):
"""
Gets the number of all shares approved between and including both start
date and end date.
Args:
start_date: The first date for which approved shares are counted.
end_date: The last date for which approved shares are counted.
Returns:
The number of all shares approved between and including both start
date and end date.
"""
# pylint: disable=no-member
count = DBSession.query(func.sum(Shares.number)).filter(
expression.and_(
Shares.date_of_acquisition >= start_date,
Shares.date_of_acquisition <= end_date)).scalar()
if count is None:
count = 0
return count
def get_all(cls, years=None):
"""
Get dues invoices
If years is not specified then all available years are returned.
Args:
years (array): Defaults to None. An array of ints representing
years, e.g. 2019.
Returns:
An array of dues invoices for the years specified.
Example:
dues_invoices = DuesInvoiceRepository.get_all([2015, 2018])
"""
result = []
db_session = DBSession()
year_classes = cls._get_year_classes(years)
for year_class in year_classes:
result = result + db_session.query(year_class).all()
return result
def get_member_invitations(
cls, membership_number, earliest=None, latest=None):
"""
Get all general assembly invitations of the member
Args:
membership_number: The membership number of the member of which
the general assembly invitations are returned.
earliest: Optional. The earliest date for which general assemblies
are returned.
latest: Optional. The latest date for which general assemblies
are returned.
Returns:
All general assemblies not earlier than earliest and not later than
latest with number, name, date, invited flag, sent date and token.
"""
# pylint: disable=no-member
assembly_date_filter = True
if earliest is not None:
assembly_date_filter = and_(
assembly_date_filter,
GeneralAssembly.date >= earliest
)
if latest is not None:
assembly_date_filter = and_(
assembly_date_filter,
GeneralAssembly.date <= latest
)
result = []
assemblies = (
# Get number, name and date of general assembly with invitation
# sent date and token
DBSession.query(
GeneralAssembly.number,
GeneralAssembly.name,
GeneralAssembly.date,
GeneralAssemblyInvitation.sent,
GeneralAssemblyInvitation.token)
# combine with the member as a cross join with the one member
# requested
.outerjoin(
C3sMember,
C3sMember.membership_number == membership_number
)
# combine with the invitations to for this member to the general
# assemblies
.outerjoin(
GeneralAssemblyInvitation,
and_(
GeneralAssemblyInvitation.member_id == C3sMember.id,
GeneralAssemblyInvitation.general_assembly_id ==
GeneralAssembly.id
)
)
# filter for earliest and latest
.filter(assembly_date_filter)
# and get all of the actual records
.all()
)
for assembly in assemblies:
result.append({
'number': assembly.number,
'name': assembly.name,
'date': assembly.date,
'flag': (assembly.sent is not None),
'sent': assembly.sent,
'token': assembly.token,
})
return result
def _get_paid_not_approved_query(cls, query_type, start_date, end_date):
"""
Gets the query for paid but not approved shares between and including
start and end date.
Args:
query_type: The type of the query to be build. 'data' for
retrieving rows and 'shares_count' for an aggregate count
query.
start_date: The first date of which paid and not approved shares
are considered.
end_date: The last date of which paid and not approved shares are
considered.
Returns:
A query according to the specified query_type.
For 'data' the query is build to retrieve rows with attributes 'id'
for member id, 'lastname' for the member's lastname, 'firstname'
for the member's firstname, 'shares_count' for the number of shares
and 'payment_received_date' for the date on which the payment was
received
For 'shares_count' an aggregate count query is returned to retrieve
the number of shares of all relevant shares packages.
"""
# Shares which of the day of the request have not been approved are not
# yet stored in Shares but only available on the C3sMember.
shares_count = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.num_shares)],
else_=Shares.number
)
payment_received_date = expression.case(
[(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
Shares.id == None,
# C3sMember.payment_received_date has the data type DateTime
# but Date is required as it is used in
# Shares.payment_received_date. As CAST on DateTime '2017-01-02
# 12:23:34.456789' returns '2017' in SQLite and therefore
# cannot be used substring is used instead and then SQLAlchemy
# is forced by type_coerce to parse it as a Date column.
expression.type_coerce(
func.substr(C3sMember.payment_received_date, 1, 10), Date)
)],
else_=Shares.payment_received_date
)
# SqlAlchemy equality to None must be used as "== None" instead of
# Python "is not None".
date_of_acquisition = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.membership_date)],
else_=Shares.date_of_acquisition
)
if query_type == 'data':
# pylint: disable=no-member
query = DBSession.query(
C3sMember.id,
C3sMember.lastname,
C3sMember.firstname,
shares_count.label('shares_count'),
payment_received_date.label('payment_received_date'),
)
if query_type == 'shares_count':
# pylint: disable=no-member
query = DBSession.query(
func.sum(shares_count)
)
# Use outer joins as Shares do not have to exist yet.
return query.select_from(C3sMember) \
.outerjoin(members_shares) \
.outerjoin(Shares) \
.filter(
expression.and_(
# membership not approved in time period
expression.or_(
# membership or share approved later than end date
date_of_acquisition > end_date,
# or membership or share not approved yet (default
# date)
date_of_acquisition == date(1970, 1, 1),
),
# payment received in time period
payment_received_date >= start_date,
payment_received_date <= end_date,
)
)
def test_delete(self):
"""
Tests the ShareRepository.delete method.
"""
# pylint: disable=no-member
count = DBSession.query(Shares).count()
self.assertEqual(count, 4)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' in reference_codes)
self.assertTrue('share2' in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share1').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 3)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share2').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 2)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' not in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share3').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 1)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' not in reference_codes)
self.assertTrue('share3' not in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share4').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 0)
def get_monthly_stats(cls, year):
"""
Gets monthly statistics for the specified year
Args:
year (int): The year to which the invoice number belongs, e.g.
2019.
Returns:
Sums of the normale and reversal invoices per calendar month based
on the invoice date.
"""
year_class = cls._get_year_class(year)
if year_class is None:
return None
db_session = DBSession()
result = []
# SQLite specific: substring for SQLite as it does not support
# date_trunc.
# invoice_date_month = func.date_trunc(
# 'month',
# invoice_date)
paid_date = cls._PAYMENT_FIELDS[year]['paid_date']
amount_paid = cls._PAYMENT_FIELDS[year]['amount_paid']
invoice_date_month = func.substr(year_class.invoice_date, 1, 7)
payment_date_month = func.substr(paid_date, 1, 7)
# collect the invoice amounts per month
invoice_amounts_query = db_session.query(
invoice_date_month.label('month'),
func.sum(expression.case(
[(
expression.not_(year_class.is_reversal),
year_class.invoice_amount)],
else_=Decimal('0.0'))).label('amount_invoiced_normal'),
func.sum(expression.case(
[(
year_class.is_reversal,
year_class.invoice_amount)],
else_=Decimal('0.0'))).label('amount_invoiced_reversal'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal).label('amount_paid')
).group_by(invoice_date_month)
# collect the payments per month
member_payments_query = db_session.query(
payment_date_month.label('month'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal).label('amount_invoiced_normal'),
expression.literal_column(
'\'0.0\'', DatabaseDecimal
).label('amount_invoiced_reversal'),
func.sum(amount_paid).label('amount_paid')
).filter(paid_date.isnot(None)) \
.group_by(payment_date_month)
# union invoice amounts and payments
union_all_query = expression.union_all(
member_payments_query, invoice_amounts_query)
# aggregate invoice amounts and payments by month
result_query = db_session.query(
union_all_query.c.month.label('month'),
func.sum(union_all_query.c.amount_invoiced_normal).label(
'amount_invoiced_normal'),
func.sum(union_all_query.c.amount_invoiced_reversal).label(
'amount_invoiced_reversal'),
func.sum(union_all_query.c.amount_paid).label('amount_paid')
) \
.group_by(union_all_query.c.month) \
.order_by(union_all_query.c.month)
for month_stat in result_query.all():
result.append(
{
'month': datetime(
int(month_stat[0][0:4]),
int(month_stat[0][5:7]),
1),
'amount_invoiced_normal': month_stat[1],
'amount_invoiced_reversal': month_stat[2],
'amount_paid': month_stat[3]
})
return result
def general_assembly_max_number(cls):
"""
Get the maximum number assigned to a general assembly.
"""
# pylint: disable=no-member
return DBSession.query(func.max(GeneralAssembly.number)).scalar()
def _get_paid_not_approved_query(cls, query_type, start_date, end_date):
"""
Gets the query for paid but not approved shares between and including
start and end date.
Args:
query_type: The type of the query to be build. 'data' for
retrieving rows and 'shares_count' for an aggregate count
query.
start_date: The first date of which paid and not approved shares
are considered.
end_date: The last date of which paid and not approved shares are
considered.
Returns:
A query according to the specified query_type.
For 'data' the query is build to retrieve rows with attributes 'id'
for member id, 'lastname' for the member's lastname, 'firstname'
for the member's firstname, 'shares_count' for the number of shares
and 'payment_received_date' for the date on which the payment was
received
For 'shares_count' an aggregate count query is returned to retrieve
the number of shares of all relevant shares packages.
"""
# Shares which of the day of the request have not been approved are not
# yet stored in Shares but only available on the C3sMember.
shares_count = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.num_shares)],
else_=Shares.number
)
payment_received_date = expression.case(
[(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
Shares.id == None,
# C3sMember.payment_received_date has the data type DateTime
# but Date is required as it is used in
# Shares.payment_received_date. As CAST on DateTime '2017-01-02
# 12:23:34.456789' returns '2017' in SQLite and therefore
# cannot be used substring is used instead and then SQLAlchemy
# is forced by type_coerce to parse it as a Date column.
expression.type_coerce(
func.substr(C3sMember.payment_received_date, 1, 10), Date)
)],
else_=Shares.payment_received_date
)
# SqlAlchemy equality to None must be used as "== None" instead of
# Python "is not None".
date_of_acquisition = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.membership_date)],
else_=Shares.date_of_acquisition
)
if query_type == 'data':
# pylint: disable=no-member
query = DBSession.query(
C3sMember.id,
C3sMember.lastname,
C3sMember.firstname,
shares_count.label('shares_count'),
payment_received_date.label('payment_received_date'),
)
if query_type == 'shares_count':
# pylint: disable=no-member
query = DBSession.query(
func.sum(shares_count)
)
# Use outer joins as Shares do not have to exist yet.
return query.select_from(C3sMember) \
.outerjoin(members_shares) \
.outerjoin(Shares) \
.filter(
expression.and_(
# membership not approved in time period
expression.or_(
# membership or share approved later than end date
date_of_acquisition > end_date,
# or membership or share not approved yet (default
# date)
date_of_acquisition == date(1970, 1, 1),
),
# payment received in time period
payment_received_date >= start_date,
payment_received_date <= end_date,
)
)
def test_delete(self):
"""
Tests the ShareRepository.delete method.
"""
# pylint: disable=no-member
count = DBSession.query(Shares).count()
self.assertEqual(count, 4)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' in reference_codes)
self.assertTrue('share2' in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share1').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 3)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share2').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 2)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' not in reference_codes)
self.assertTrue('share3' in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share3').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 1)
shares = DBSession.query(Shares).all()
reference_codes = []
for share in shares:
reference_codes.append(share.reference_code)
self.assertTrue('share1' not in reference_codes)
self.assertTrue('share2' not in reference_codes)
self.assertTrue('share3' not in reference_codes)
self.assertTrue('share4' in reference_codes)
# pylint: disable=no-member
share = DBSession.query(Shares).filter(
Shares.reference_code == u'share4').first()
ShareRepository.delete(share.id)
count = DBSession.query(Shares).count()
self.assertEqual(count, 0)
def test_membership_application(self):
"""
Test the membership application process.
1. Enter applicant data to application form
2. Verify entered data and confirm
3. Verify sent confirmation email
4. Confirm email address via confirmation link
5. Login to backend
6. Verify applicant's detail page
7. Set payment received
8. Set signature received
9. Make member
10. Verify member details
"""
self.testapp.reset()
# 1. Enter applicant data to application form
res = self.testapp.get('/', status=200)
properties = {
'firstname': u'Sönke',
'lastname': u'Blømqvist',
'email': u'*****@*****.**',
'address1': u'℅ Big Boss',
'address2': u'Håkanvägen 12',
'postcode': u'ABC1234',
'city': u'Stockholm',
'year': u'1980',
'month': u'01',
'day': u'02',
'name_of_colsoc': u'Svenska Tonsättares Internationella Musikbyrå',
'num_shares': u'15',
'password': u'worst password ever chosen',
'password-confirm': u'worst password ever chosen',
}
for key, value in properties.iteritems():
res.form[key] = value
res.form['country'].select(text=u'Sweden')
res.form['membership_type'].value__set(u'normal')
res.form['other_colsoc'].value__set(u'yes')
res.form['got_statute'].checked = True
res.form['got_dues_regulations'].checked = True
res = res.form.submit(u'submit', status=302)
res = res.follow()
# 2. Verify entered data and confirm
body = self._response_to_bare_text(res)
self.assertTrue('First Name: Sönke' in body)
self.assertTrue('Last Name: Blømqvist' in body)
self.assertTrue('Email Address: [email protected]' in body)
self.assertTrue('Address Line 1: ℅ Big Boss' in body)
self.assertTrue('Address Line 2: Håkanvägen 12' in body)
self.assertTrue('Postal Code: ABC1234' in body)
self.assertTrue('City: Stockholm' in body)
self.assertTrue('Country: SE' in body)
self.assertTrue('Date of Birth: 1980-01-02' in body)
self.assertTrue('Type of Membership:normal' in body)
self.assertTrue('Member of other Collecting Society: yes' in body)
self.assertTrue(
'Membership(s): Svenska Tonsättares Internationella Musikbyrå' in
body)
self.assertTrue('Number of Shares: 15' in body)
self.assertTrue('Cost of Shares (50 € each): 750 €' in body)
res = res.forms[1].submit(status=200)
# 3. Verify sent confirmation email
mailer = self.get_mailer(None)
email = mailer.get_email()
self.assertEqual(email.recipients, ['*****@*****.**'])
self.assertEqual(email.subject,
'C3S: confirm your email address and load your PDF')
# 4. Confirm email address via confirmation link
match = re.search('localhost(?P<url>[^\s]+)', email.body)
self.assertTrue(match is not None)
res = self.testapp.get(match.group('url'), status=200)
self.assertTrue(u'password in order to verify your email' in res.body)
res.form['password'] = '******'
res = res.form.submit(u'submit', status=200)
# 5. Login to backend
self.testapp.reset()
self._login()
# 6. Verify applicant's detail page
member_id = DBSession.query(func.max(C3sMember.id)).scalar()
res = self.testapp.get('/detail/{0}'.format(member_id), status=200)
body = self._response_to_bare_text(res)
self.assertTrue('firstname Sönke' in body)
self.assertTrue('lastname Blømqvist' in body)
self.assertTrue('email [email protected]' in body)
self.assertTrue('email confirmed? Yes' in body)
self.assertTrue('address1 ℅ Big Boss' in body)
self.assertTrue('address2 Håkanvägen 12' in body)
self.assertTrue('postcode ABC1234' in body)
self.assertTrue('city Stockholm' in body)
self.assertTrue('country SE' in body)
self.assertTrue('date_of_birth 1980-01-02' in body)
self.assertTrue('membership_accepted No' in body)
self.assertTrue('entity type Person' in body)
self.assertTrue('membership type normal' in body)
self.assertTrue('member_of_colsoc Yes' in body)
self.assertTrue(
'name_of_colsoc Svenska Tonsättares Internationella Musikbyrå' in
body)
self.assertTrue('date_of_submission ' in body)
self.assertTrue('signature received? Nein' in body)
self.assertTrue('signature confirmed (mail sent)?No' in body)
self.assertTrue('payment received? Nein' in body)
self.assertTrue('payment confirmed?No' in body)
self.assertTrue('# shares total: 15' in body)
# TODO:
# - code
# - locale, set explicitly and test both German and English
# - date of submission
# 7. Set payment received
res = self.testapp.get('/switch_pay/{0}'.format(member_id),
headers={'Referer': 'asdf'},
status=302)
res = res.follow()
body = self._response_to_bare_text(res)
self.assertTrue('payment received? Ja' in body)
self.assertTrue('payment reception date 2018-04-26 12:23:34' in body)
# 8. Set signature received
res = self.testapp.get('/switch_sig/{0}'.format(member_id),
headers={'Referer': 'asdf'},
status=302)
res = res.follow()
body = self._response_to_bare_text(res)
self.assertTrue('signature received? Ja' in body)
self.assertTrue('signature reception date2018-04-26 12:23:34' in body)
# 9. Make member
res = self.testapp.get('/make_member/{0}'.format(member_id),
headers={'Referer': 'asdf'},
status=200)
res.form['membership_date'] = '2018-04-27'
res = res.form.submit('submit', status=302)
res = res.follow()
# 10. Verify member details
membership_number = C3sMember.get_next_free_membership_number() - 1
body = self._response_to_bare_text(res)
self.assertTrue('membership_accepted Yes' in body)
self.assertTrue(
'membership_number {0}'.format(membership_number) in body)
self.assertTrue('membership_date 2018-04-27' in body)
self.assertTrue('# shares total: 15' in body)
self.assertTrue('1 package(s)' in body)
self.assertTrue('15 shares (2018-04-27)' in body)
