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)