from shares.serializers import * from shares.models import * import csv import io from django.db.models import Q from savings.models import SavingAccount, SavingAccountTransactions from ledgers.ledgers_helper import * from reports.reports_helper import get_period_earnings from exservices.exservices_helper import send_customer_sms from ledgers.serializers import SystemTransactionsSerializer from reports.reports_helper import generate_sacco_report_file import pandas as pd import os def list_shares_purchase(search,organisation_branch_id,start_date,end_date): share_filter = {} if start_date: share_filter['date_added__gte'] = start_date if end_date: if len(end_date) == 10 and ' ' not in end_date: end_date = f"{end_date} 23:59:59" share_filter['date_added__lte'] = end_date if search: list_purchases = SharesTransaction.objects.filter( Q(shareholder__customer__old_member_number__icontains=search) | Q(shareholder__customer__member_number__icontains=search) | Q(shareholder__customer__name__icontains=search), transaction_type='purchase', **share_filter ).order_by('-id') else: list_purchases = SharesTransaction.objects.filter( transaction_type='purchase', organisation_branch__id=organisation_branch_id, **share_filter ).order_by('id') serializer = SharesTransactionSerializer(list_purchases, many=True) response = serializer.data return response def list_share_holders(search,organisation_branch_id,start_date,end_date): share_filter = {} if start_date: share_filter['date_added__gte'] = start_date if end_date: if len(end_date) == 10 and ' ' not in end_date: end_date = f"{end_date} 23:59:59" share_filter['date_added__lte'] = end_date if search: list_holders = ShareHolders.objects.filter( Q(customer__old_member_number__icontains=search) | Q(customer__member_number__icontains=search) | Q(customer__name__icontains=search), **share_filter ).order_by('-id') else: list_holders = ShareHolders.objects.filter( customer__customer_branch__id=organisation_branch_id, **share_filter ).order_by('id') serializer = ShareHoldersSerializer(list_holders, many=True) response = serializer.data return response def list_shares_transfer(search,organisation_branch_id,start_date,end_date): share_filter = {} if start_date: share_filter['date_added__gte'] = start_date if end_date: if len(end_date) == 10 and ' ' not in end_date: end_date = f"{end_date} 23:59:59" share_filter['date_added__lte'] = end_date if search: list_transfers = SharesTransaction.objects.filter( Q(shareholder__customer__old_member_number__icontains=search) | Q(shareholder__customer__member_number__icontains=search) | Q(shareholder__customer__name__icontains=search), transaction_type='transfer-out', **share_filter ).order_by('-id') else: list_transfers = SharesTransaction.objects.filter( organisation_branch__id=organisation_branch_id, transaction_type='transfer-out', **share_filter ).order_by('id') serializer = SharesTransactionSerializer(list_transfers, many=True) response = serializer.data return response def list_shares_withdrawal(search,organisation_branch_id,start_date,end_date): share_filter = {} if start_date: share_filter['date_added__gte'] = start_date if end_date: if len(end_date) == 10 and ' ' not in end_date: end_date = f"{end_date} 23:59:59" share_filter['date_added__lte'] = end_date if search: list_transfers = SharesTransaction.objects.filter( Q(shareholder__customer__old_member_number__icontains=search) | Q(shareholder__customer__member_number__icontains=search) | Q(shareholder__customer__name__icontains=search), transaction_type='withdrawal', **share_filter ).order_by('-id') else: list_transfers = SharesTransaction.objects.filter( organisation_branch__id=organisation_branch_id, transaction_type='withdrawal', **share_filter ).order_by('id') serializer = SharesTransactionSerializer(list_transfers, many=True) response = serializer.data return response def upload_dividends_thread(user, transaction_date, file_obj, share_dividends, organisation_id): try: file_obj.seek(0) reader = csv.DictReader(io.StringIO(file_obj.read().decode('utf-8'))) total_share = 0 for row in reader: member_number = list(row.values())[1] amount_to_shares = list(row.values())[2] amount_to_savings = list(row.values())[3] total_dividends = list(row.values())[4] if list(row.values())[4] else 0 if member_number: customer = Customer.objects.filter((Q(member_number=member_number) | Q(old_member_number=member_number)), customer_branch__branch_organisation__id=organisation_id).first() if customer: total_share += float(total_dividends) data = {"customer":customer, "share_dividend":share_dividends, "date_added":transaction_date, "total_dividends":total_dividends, "share_dividend_customer_added_by":user, "converted_to_savings":amount_to_savings, "converted_to_shares":amount_to_shares} ShareDividendCustomers.objects.create(**data) share_dividends.profits_shared = total_share share_dividends.save() except Exception as e: print(e) def process_share_dividends(id, user, organisation_id): print("*********************** starting at beginning ********************") try: # adding withholding tax withholding_tax=0 fd_withholding_chart = OrganisationSubAccount.objects.filter(account_code='sys-2122', account_organisation=organisation_id) withholding_tax_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id,setting_key='withholding_tax').first() if withholding_tax_setting: withholding_tax = float(withholding_tax_setting.setting_value) if withholding_tax_setting.setting_value else 0 dividend = ShareDividends.objects.get(pk=id) print("################################################# starting ") print(dividend) print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++") if dividend: start_date = dividend.start_date.strftime("%Y-%m-%d") end_date = dividend.end_date.strftime("%Y-%m-%d") share_setting = SharesSettings.objects.filter(organisation=dividend.organisation_branch.branch_organisation).first() if not share_setting: return False print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++ inside if statement") sharing_customers = ShareDividendCustomers.objects.filter(share_dividend=dividend, status='pending').order_by('id') for sharing_customer in sharing_customers: branch = sharing_customer.customer.customer_branch save_trans = None shares_trans = None print("***************************************** 1") print(sharing_customer) print(branch) print(sharing_customers) print("*************************** 2") # push the savings customer_account = SavingAccount.objects.filter(account_customer=sharing_customer.customer, status='active', deleted=False).order_by('id').first() print("*************************** 3") if customer_account and sharing_customer.converted_to_savings > 0: print("*************************** 4") selected_account = share_setting.share_dividends_chart reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, dividend.organisation_branch.branch_organisation.id, 'dvsa') heading = "Dividend Sharing for (MEM NO: "+ str(sharing_customer.customer.member_number) +") period ("+ str(start_date) +" To "+ str(end_date) +")" # withholding tax amount # dividend_saving_tax = (withholding_tax / 100)* sharing_customer.converted_to_savings # tax_transaction = { # "heading":"Withholding tax for dividend sharing (MEM No.: "+str(sharing_customer.customer.member_number)+") period ("+ str(start_date) +" To "+ str(end_date) +")", # "amount": float(dividend_saving_tax), # "record_date": timezone.now(), # "debit_chart_id":selected_account.id, # "credit_chart_id":fd_withholding_chart.id, # "payment_method": 'offset', # "voucher_no": "", # "ref_no_prefix": 'dividend-py-t', # "organisation_id": organisation_id, # "branch_id": branch.id, # "user_id": None # } # save_tax_transaction = post_transaction(tax_transaction) # account_transaction = SavingAccountTransactions(customer_account=customer_account.id, transaction=save_tax_transaction, transaction_type='deposit_charge') # account_transaction.save() # end of dividend tax for savings transaction = SystemTransactions.objects.create(amount=sharing_customer.converted_to_savings, heading=heading, reference_no=reference_no, payment_method='offset', voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=customer_account.account_product.accounts_chart.id, branch_id=branch.id, added_by=user) print("*************************** 5") if transaction: print("*************************** 6") save_trans = transaction saved_transaction_fields = { "transaction_type":'deposit', "customer_account_id":customer_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) sharing_customer.status = 'processed' sharing_customer.saving_account = customer_account sharing_customer.system_transaction_savings = transaction sharing_customer.save() # remove withholding tax for sopag [33] print("*************************** 7") withholding_chart_code = 'sys-2122' if int(dividend.organisation_branch.branch_organisation.id) == 33: withholding_tax = 15 withholding_chart_code = '2174' print("*************************** 8") # withholding_chart_code = '2174' credit_chart = get_chart_of_account_by_code(withholding_chart_code, dividend.organisation_branch.branch_organisation) withhold_reference_no = generate_reference_no(credit_chart.account_line, dividend.organisation_branch.branch_organisation.id, 'lbt') withholding_heading = "Dividend Sharing Withholding Tax( "+str(withholding_tax)+"%)" withhold_amount = round((withholding_tax/100) * sharing_customer.converted_to_savings, 0) withholding_transaction = SystemTransactions.objects.create(amount=withhold_amount, heading=withholding_heading, reference_no=withhold_reference_no, payment_method='offset', voucher_no='', debit_chart_id=customer_account.account_product.accounts_chart.id, credit_chart_id=credit_chart.id, branch_id=branch.id, added_by=user) print("*************************** 9") if withholding_transaction: print("*************************** 10") withhold_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":customer_account.id, "transaction_id":withholding_transaction.id } SavingAccountTransactions.objects.create(**withhold_transaction_fields) sharing_customer.system_transaction_tax = withholding_transaction sharing_customer.save() # push the shares print("*************************** 11") share_holder = ShareHolders.objects.filter(customer=sharing_customer.customer).first() print("*************************** 12") if not share_holder: print("*************************** 13") share_holder = ShareHolders.objects.create(customer=sharing_customer.customer, share_holder_added_by=user) if sharing_customer.converted_to_shares > 0 and share_holder: print("*************************** 14") selected_account = share_setting.share_dividends_chart reference_no = generate_reference_no(selected_account.account_line, dividend.organisation_branch.branch_organisation.id, 'dvsa') heading = "Dividend Sharing for (MEM NO: "+ str(sharing_customer.customer.member_number) +") period ("+ str(start_date) +" To "+ str(end_date) +")" credit_chart = get_chart_of_account_by_code('sys-311', dividend.organisation_branch.branch_organisation) transaction = SystemTransactions.objects.create(amount=sharing_customer.converted_to_shares, heading=heading, reference_no=reference_no, payment_method='offset', voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=branch.id, added_by=user) print("*************************** 15") if transaction: print("*************************** 16") shares_trans = transaction share_value = 0 number_of_shares = 0 share_setting = SharesSettings.objects.filter(organisation=dividend.organisation_branch.branch_organisation).first() print("*************************** 17") if share_setting: print("*************************** 18") share_value = share_setting.share_value number_of_shares = round(sharing_customer.converted_to_shares / share_value, 2) share_transaction = { "shareholder":share_holder, "share_holder_trans_added_by":user, "no_of_shares":number_of_shares, "current_share_value":share_value, "transaction_type":"purchase", "organisation_branch":branch, "system_transaction":transaction} share_tran = SharesTransaction.objects.create(**share_transaction) sharing_customer.status = 'processed' sharing_customer.system_transaction_shares = transaction sharing_customer.shares_transaction = share_tran sharing_customer.save() # send sms print("*************************** 19") if (sharing_customer.converted_to_savings > 0 or sharing_customer.converted_to_shares > 0) and customer_account and (shares_trans or save_trans): print("*************************** 20") sms_msg = '' if sharing_customer.converted_to_savings > 0 and int(sharing_customer.converted_to_shares) == 0: sms_msg = 'You have recieved dividends worth UGX:'+ f"{round(sharing_customer.converted_to_savings, 2):,} converted to savings" elif sharing_customer.converted_to_shares > 0 and int(sharing_customer.converted_to_savings) == 0: sms_msg = 'You have recieved dividends worth UGX:'+ f"{round(sharing_customer.converted_to_shares, 2):,} converted to shares" elif sharing_customer.converted_to_shares > 0 and sharing_customer.converted_to_savings > 0: sms_msg = 'You have recieved dividends worth UGX:'+ f"{round(sharing_customer.converted_to_shares, 2):,} converted to shares and UGX " + f"{round(sharing_customer.converted_to_savings, 2):,} converted to savings" data = {"sms_key": "share_dividends_sms", "save_trans":save_trans, "customer_account": customer_account, "user": user, "branch_id": branch.id, "sms_msg": sms_msg,"customer":customer_account.account_customer} send_customer_sms(data) dividend.status = 'processed' dividend.save() print("*************************** 21") except Exception as e: print(e) def dividends_sharing_customers(share_dividends, user, start_date, end_date): as_at = share_dividends.bought_shares_by share_holders = get_client_shares_holders(share_dividends.organisation_branch.branch_organisation.id, None, None, None, as_at) profits_shared = 0 if float(share_dividends.share_flat_amount) > 0: for share_holder in share_holders['results']: total_shares = share_holder['total_shares'] if total_shares == 0: continue if share_dividends.with_full_share_value == 'yes': total_shares = int(total_shares) if float(share_dividends.shares_cap_shares) > 0 and float(share_dividends.shares_cap_shares) > total_shares: continue customer_share = total_shares * float(share_dividends.share_flat_amount) profits_shared += customer_share # divide customer profits savings = round((float(share_dividends.dividends_to_savings)/100)*customer_share, 2) shares = round((float(share_dividends.dividends_to_shares)/100)*customer_share, 2) holder_customer = Customer.objects.filter( ( Q(old_member_number=share_holder['member_number']) | Q(member_number=share_holder['member_number']) ), customer_branch__branch_organisation__id=share_dividends.organisation_branch.branch_organisation.id).first() if holder_customer: data = {"customer":holder_customer, "share_dividend":share_dividends, "converted_to_savings":savings, "converted_to_shares":shares, "total_dividends":customer_share, "share_dividend_customer_added_by":user} ShareDividendCustomers.objects.create(**data) if profits_shared > 0: share_dividends.profits_shared = profits_shared share_dividends.save() else: # get profits to be shared period_earnings = get_period_earnings(share_dividends.organisation_branch.branch_organisation.id, share_dividends.organisation_branch.id, start_date.split('T')[0], end_date.split('T')[0]) if period_earnings['forward'] + period_earnings['btn'] > 0: profits = period_earnings['btn'] share_dividends.profits_shared = period_earnings['btn'] share_dividends.save() if profits > 0: total_shares = 0 for share_holder in share_holders['results']: customer = Customer.objects.select_related('customer_branch').filter( Q(old_member_number=share_holder['member_number']) | Q(member_number=share_holder['member_number']), customer_branch__branch_organisation__id=share_dividends.organisation_branch.branch_organisation.id ).first() customer_balance = get_client_shares_balance(customer) total_shares += (customer_balance['all_total_no_of_shares'] if customer_balance else 0 ) for share_holder in share_holders['results']: customer = Customer.objects.select_related('customer_branch').filter( Q(old_member_number=share_holder['member_number']) | Q(member_number=share_holder['member_number']), customer_branch__branch_organisation__id=share_dividends.organisation_branch.branch_organisation.id ).first() # customer_balance = get_client_shares_balance(share_holder.customer) customer_balance = get_client_shares_balance(customer) amount = customer_balance['all_total_no_of_shares'] if customer_balance else 0 share_percentage = amount/total_shares shareholder_profit = share_percentage * profits # divide customer profits savings = round((float(share_dividends.dividends_to_savings)/100)*shareholder_profit, 2) shares = round((float(share_dividends.dividends_to_shares)/100)*shareholder_profit, 2) data = {"customer":customer, "share_dividend":share_dividends, "converted_to_savings":savings, "converted_to_shares":shares, "total_dividends":shareholder_profit, "share_dividend_customer_added_by":user} ShareDividendCustomers.objects.create(**data) return True def process_group_share_purchases(user,request,branch_id): inter_branch_trans = None purchases = request.data.get('purchases') payment_method = request.data.get('payment_method') send_sms = request.data.get('send_sms') selected_account = request.data.get('account') customer_id = request.data.get('customer_id') transaction_date = request.data.get('transaction_date') current_share_value = request.data.get('current_share_value') account_id = request.data.get('account_id') response_status = False success_count = 0 customer = Customer.objects.get(pk=customer_id) if customer: if purchases: if len(purchases) > 0: for purchase in purchases: amount = float(purchase['shares']) member_number = purchase['memberNumber'] number_of_shares = float(purchase['count']) member = Customer.objects.filter(member_number=member_number).first() if member: # process shares purchase save_trans = None # InterBranch chart interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), customer.customer_branch) # Begin validation debit_chart = OrganisationSubAccount.objects.get(pk=selected_account) # end of validation heading = 'Share purchase by '+ member.name+' ('+ member.member_number +')' share_holder = ShareHolders.objects.filter(customer=customer).first() if not share_holder: share_holder = ShareHolders.objects.create(customer=customer, share_holder_added_by=user) organisation_branch = OrganisationBranch.objects.get(pk=branch_id) # credit manual member shares chart of account credit_chart_code = "sys-311" credit_chart = OrganisationSubAccount.objects.filter(account_code=credit_chart_code, account_organisation=organisation_branch.branch_organisation.id).first() # Generate reference number reference_no = generate_reference_no(debit_chart.account_line, organisation_branch.branch_organisation.id, 'p-sh') # make the transaction to update the ledger if str(branch_id) != str(customer.customer_branch.id) and payment_method != 'offset': # Handle inter-branch transactions -> soure branch heading = 'Inter-branch share purchase by '+ member.name+' ('+ member.member_number +')' source_trans = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method=payment_method, voucher_no='', debit_chart=debit_chart, credit_chart=interbranch_chart, branch_id=branch_id, added_by=user, record_date=transaction_date) # Handle inter-branch transactions -> destination branch inter_branch_trans = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method='settlement', voucher_no='', debit_chart=interbranch_chart, credit_chart=credit_chart, branch_id=customer.customer_branch.id, added_by=user, record_date=transaction_date) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction":source_trans, "destination_transaction":inter_branch_trans, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) share_transaction = { "shareholder":share_holder, "share_holder_trans_added_by":user, "no_of_shares":number_of_shares, "current_share_value":current_share_value, "transaction_type":"purchase", "organisation_branch":customer.customer_branch, "system_transaction":inter_branch_trans} save_trans = SharesTransaction.objects.create(**share_transaction) else: # Handle single branch transactions source_trans = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method=payment_method, voucher_no='', debit_chart=debit_chart, credit_chart=credit_chart, branch_id=branch_id, added_by=user, record_date=transaction_date) share_transaction = { "shareholder":share_holder, "share_holder_trans_added_by":user, "no_of_shares":number_of_shares, "current_share_value":current_share_value, "transaction_type":"purchase", "organisation_branch":organisation_branch, "system_transaction":source_trans} save_trans = SharesTransaction.objects.create(**share_transaction) if save_trans: # Register group withdraw mapping. i.e a user withdraw money from group account response_status = True success_count += 1 membership = GroupMembership.objects.filter(member=member,group=customer, active=True).first() if membership: group_trans_field = { "membership": membership, "shares": save_trans } GroupShareTransaction.objects.create(**group_trans_field) # save saving account if offset if payment_method == 'offset' and source_trans: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":source_trans.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) if send_sms: data = {"sms_key":"purchase_share_sms","customer":customer,"user":user,"branch_id":branch_id,"save_trans":save_trans} send_customer_sms(data) return {"response_status":response_status,"success_count":success_count} def process_group_share_withraws(user,request,branch_id): withdraws = request.data.get('withdraws') payment_method = request.data.get('payment_method') send_sms = request.data.get('send_sms') selected_account = request.data.get('account') customer_id = request.data.get('customer_id') transaction_date = request.data.get('transaction_date') current_share_value = request.data.get('current_share_value') account_id = request.data.get('account_id') response_status = False success_count = 0 customer = Customer.objects.get(pk=customer_id) if customer: if withdraws: if len(withdraws) > 0: for withdraw in withdraws: amount = float(withdraw['shares']) member_number = withdraw['memberNumber'] number_of_shares = float(withdraw['count']) member = Customer.objects.filter(member_number=member_number).first() if member: # process shares withdraw save_trans = None # Begin validation # validate debiting chart of account credit_chart = OrganisationSubAccount.objects.get(pk=selected_account) heading = 'Share withdraw by '+ member.name+' ('+ member.member_number +')' share_holder = ShareHolders.objects.filter(customer=customer).first() if not share_holder: share_holder = ShareHolders.objects.create(customer=customer, share_holder_added_by=request.user) organisation_branch = OrganisationBranch.objects.get(pk=branch_id) # debit manual member shares chart of account debit_chart_code = "sys-311" debit_chart = OrganisationSubAccount.objects.filter(account_code=debit_chart_code, account_organisation=organisation_branch.branch_organisation).first() # Generate reference number reference_no = generate_reference_no(debit_chart.account_line, organisation_branch.branch_organisation.id, 'w-sh') # make the transaction to update the ledger transaction = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method=payment_method, voucher_no='', debit_chart=debit_chart, credit_chart=credit_chart, branch_id=branch_id, added_by=request.user, record_date=transaction_date) if transaction: share_transaction = { "shareholder":share_holder, "share_holder_trans_added_by":request.user, "no_of_shares":number_of_shares, "current_share_value":current_share_value, "transaction_type":"withdrawal", "organisation_branch":organisation_branch, "system_transaction":transaction} save_trans = SharesTransaction.objects.create(**share_transaction) if save_trans: # Register group share mapping. i.e a user withdraw share from group account response_status = True success_count += 1 membership = GroupMembership.objects.filter(member=member,group=customer, active=True).first() if membership: group_trans_field = { "membership": membership, "shares": save_trans } GroupShareTransaction.objects.create(**group_trans_field) saved_transaction_fields = { "transaction_type":'deposit', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) if send_sms and save_trans: data = {"sms_key":"share_withraw_sms","customer":customer,"user":user,"branch_id":branch_id,"save_trans":save_trans} send_customer_sms(data) return {"response_status":response_status,"success_count":success_count} def process_reverse_share_dividends(id, user, branch): from django.utils import timezone from users.audit_log_helper import add_system_audit_trail now = timezone.now() df = dict(deleted=True, deleted_at=now, deleted_by=user) share_dividend = ShareDividends.objects.filter(id=id).first() if share_dividend: add_system_audit_trail( 'share_management', 'reverse_share_dividends', f'Reversed Share Dividends for {share_dividend.organisation_branch.name}', '', {}, {}, user, branch ) customers = ShareDividendCustomers.objects.filter(share_dividend=share_dividend) for sharing_customer in customers: if sharing_customer.system_transaction_savings: tid = sharing_customer.system_transaction_savings.id sharing_customer.system_transaction_savings = None sharing_customer.save() SystemTransactions.objects.filter(id=tid).update(**df) sharing_customer.status = 'reversed' sharing_customer.save() if sharing_customer.system_transaction_tax: tid = sharing_customer.system_transaction_tax.id sharing_customer.system_transaction_tax = None sharing_customer.save() SystemTransactions.objects.filter(id=tid).update(**df) sharing_customer.status = 'reversed' sharing_customer.save() if sharing_customer.converted_to_shares > 0: if sharing_customer.system_transaction_shares: tid = sharing_customer.system_transaction_shares.id sharing_customer.system_transaction_shares = None sharing_customer.save() SystemTransactions.objects.filter(id=tid).update(**df) sharing_customer.status = 'reversed' sharing_customer.save() share_dividend.status = 'reversed' share_dividend.save() return True def get_client_shares_holders(organisation_id, order_by, branch_id, customer_type, as_at): results = {"count": 0, "results": []} csv_file_name = f'{settings.STATIC_ROOT}/reports/{organisation_id}/shares/shares.csv' if os.path.exists(csv_file_name): # shares = pd.read_csv(csv_file_name) shares = pd.read_csv(csv_file_name, dtype={'telephone': str}) else: generate_sacco_report_file(organisation_id, as_at) shares = pd.read_csv(csv_file_name, dtype={'telephone': str}) # filter transactions per date. shares['record_date'] = pd.to_datetime(shares['record_date'], utc=True) shares = shares[shares['record_date'] <= pd.Timestamp(as_at, tz='UTC')] # compute totals # Share amounts shares['total_in'] = shares.apply(lambda row: row['share_amount'] if 'in' == row['transaction_type'] else 0, axis=1) shares['total_out'] = shares.apply(lambda row: row['share_amount'] if 'out' == row['transaction_type'] else 0, axis=1) #share counts shares['shares_in'] = shares.apply(lambda row: row['no_of_shares'] if 'in' == row['transaction_type'] else 0, axis=1) shares['shares_out'] = shares.apply(lambda row: row['no_of_shares'] if 'out' == row['transaction_type'] else 0, axis=1) # Group by shareholder and sum total in and total out shareholder_totals = shares.groupby('shareholder').agg({'total_in':'sum', 'total_out':'sum'}).reset_index() shareholder_totals['total_share_amount'] = shareholder_totals['total_in'] - shareholder_totals['total_out'] # Share counts shareholder_count = shares.groupby('shareholder').agg({'shares_in':'sum', 'shares_out':'sum'}).reset_index() shareholder_count['total_shares'] = shareholder_count['shares_in'] - shareholder_count['shares_out'] # Merge this result back into the original DataFrame to add the 'total_share_amount' column shares = shares.merge(shareholder_totals[['shareholder', 'total_share_amount']], on='shareholder', how='left') shares = shares.merge(shareholder_count[['shareholder', 'total_shares']], on='shareholder', how='left') aggregations = { 'customer_names': 'first', 'member_number': 'first', 'telephone':'first', 'new_member_number': 'first', 'customer_type': 'first', 'customer_type_id': 'first', 'customer_branch': 'first', 'total_share_amount': 'first', 'total_shares': 'first' } shares = shares.groupby('shareholder').agg(aggregations).reset_index() # Sort results # sort_values = ['customer_names'] # if order_by and order_by in ['customer_names', 'member_number', 'customer_type']: # sort_values = [order_by, 'customer_names'] sort_values = ['total_shares'] ascending_order = [False] shares = shares.sort_values(by=sort_values, axis=0, ascending=ascending_order) if branch_id: shares = shares[shares['customer_branch'] == int(branch_id)] if customer_type: shares = shares[shares['customer_type_id'] == int(customer_type)] results['count'] = len(shares) results['total_share_value'] = shares['total_share_amount'].sum() results['total_shares'] = shares['total_shares'].sum() shares['share_percentage'] = (shares['total_shares'] / results['total_shares']) * 100 # Fetch the records for the page #shares = shares.iloc[start_index:end_index] results['results'] = shares.to_dict(orient='records') return results