Local - Fees - Aged Debtor Export

We have been asked to show how to automate the collection of fees transactions from SIMS using the Fees Aged Debtor Report. This report enables the capture of balances which is likely to be useful when interfacing with a payment system for example.

An alternative approach to obtaining similar data is available via the reporting engine see Local - Fees Balances | ESS Portal (sims-partners.com) for details.

Additional DLLs referenced

• Fees7Entities.Dll
• Fees7Processes.Dll

A SIMS login is required before these calls can be made typically with a fees manager permission level.

Calling model

SIMSInterface.TPAAgedDebtor tpad = new SIMSInterface.TPAAgedDebtor();
string JSon = tpad.GetInfo();
           

Implementation

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SIMS.Entities;
using Newtonsoft.Json;

namespace SIMSInterface
{
    public class TPAAgedDebtor
    {
        SIMS.Processes.FeesAgedDebtorsDetail fadd = new SIMS.Processes.FeesAgedDebtorsDetail();
        SIMS.Processes.FeesBillPayerBrowse payerBrowseProcess = new SIMS.Processes.FeesBillPayerBrowse();
        SIMS.Entities.FeesBillPayerBrowse feesBillPayerBrowse = new FeesBillPayerBrowse();

        public TPAAgedDebtor()
        {
        }
        public string GetInfo()
        {
            fadd.PayerSummarys.Value = this.getPayersSummary().Value;
            fadd.Address = true;
            fadd.FromDate1Attribute.Value = DateTime.Now.AddMonths(-3);
            fadd.FromDate2Attribute.Value = DateTime.Now.AddMonths(-6);
            fadd.FromDate3Attribute.Value = DateTime.Now.AddMonths(-12);
            fadd.ReportAsOfDateAttribute.Value = DateTime.Now;
            fadd.PupilDetailsAttribute.Value = true;
            fadd.TelephoneNoAttribute.Value = true;
            fadd.AddressAttribute.Value = true;
            fadd.ZeroBalanceAttribute.Value = true;
            fadd.SortOrderDirectionAttribute.Value = false;
            fadd.ThenByDirectionAttribute.Value = false;
            fadd.SpecialArrangementsAttribute.Value = true;
            fadd.GroupByAttribute.Value = true;


            fadd.PaymentTypesParamter = SIMS.Entities.FeesCache.PaymentTypes;
            fadd.SortOrderAttribute.Value = SIMS.Entities.FeesCache.GetSortOrderAgedDebtors().ItemByDescription("Payer Ref");

            FeesPupilStatus objFeesPupiltatus = new FeesPupilStatus(-1, "<Any>", "<Any>", true);
            fadd.PupilStatusAttribute.Value = objFeesPupiltatus;

            fadd.SpecialArrangementsAttribute.Value = true;

            fadd.Populate();
            System.Data.DataSet result = fadd.Preview();
            string json = JsonConvert.SerializeObject(result, Formatting.Indented);
            return json;

        }

        private void createAndAssignDummyAttributes()
        {
            FeesPupilStatus objFeesPupiltatus = new FeesPupilStatus(-1, "<Any>", "<Any>", true);
            feesBillPayerBrowse.PupilStatusAttribute.Value = objFeesPupiltatus;
            feesBillPayerBrowse.PayerRefAttribute.Value = "";

            FeesPaymentType paymentTypeAny = new FeesPaymentType();
            paymentTypeAny.Description = "<Any>";
            paymentTypeAny.codeAttribute.Value = Cache.CurrentDatabase.WildcardAny;
            paymentTypeAny.PaymentTypeIDAttribute.Value = -1;
            feesBillPayerBrowse.PaymentTypeAttribute.Value = paymentTypeAny;

            YearGroup yearGroupAny = new YearGroup();
            yearGroupAny.ID = -1;
            yearGroupAny.Description = "<Any>";
            yearGroupAny.Code = Cache.CurrentDatabase.WildcardAny;
            yearGroupAny.Active = "A";
            feesBillPayerBrowse.YearGroupAttribute.Value = yearGroupAny;

            // Extra reg group entities
            RegistrationGroup registrationGroupAny = new SIMS.Entities.RegistrationGroup();
            registrationGroupAny.ID = -1;
            registrationGroupAny.Description = "<Any>";
            registrationGroupAny.Code = Cache.CurrentDatabase.WildcardAny;
            registrationGroupAny.Active = "A";
            feesBillPayerBrowse.RegGroupAttribute.Value = registrationGroupAny;


            Tier objTier = new Tier();
            objTier.ID = -1;
            objTier.Description = "<Any>";
            objTier.Code = Cache.CurrentDatabase.WildcardAny;
            objTier.Active = "A";
            feesBillPayerBrowse.TierAttribute.Value = objTier;

            House objHouse = new House();
            objHouse.ID = -1;
            objHouse.Description = "<Any>";
            objHouse.Code = Cache.CurrentDatabase.WildcardAny;
            objHouse.Active = "A";
            feesBillPayerBrowse.HouseAttribute.Value = objHouse;

            Boarder objBoarder = new Boarder();
            objBoarder.ID = -1;
            objBoarder.Description = "<Any>";
            objBoarder.Code = Cache.CurrentDatabase.WildcardAny;
            objBoarder.Active = "A";
            feesBillPayerBrowse.BoarderStatusAttribute.Value = objBoarder;

            EntityAttribute isACompanyPayer = new EntityAttribute(InformationDomainEnum.None, new AbstractCollectionReference(GetCompany));
            feesBillPayerBrowse.IsACompanyPayerAttribute.Value = GetCompany().ItemByDescription("<Any>");



        }

        private FeesBillPayerSummarys getPayersSummary()
        {
            createAndAssignDummyAttributes();
            payerBrowseProcess.BillPayerBrowse = feesBillPayerBrowse;
            payerBrowseProcess.Search(false);//this will populate BillPayers which is Payers Summery
            return payerBrowseProcess.BillPayerBrowse.BillPayers;
        }

        private static AbstractCollection GetCompany()
        {
            AbstractCollection coll = new AbstractCollection();
            AbstractGroup any = new AbstractGroup(null, -1, "<Any>", "<Any>");
            coll.Add(any);
            AbstractGroup yes = new AbstractGroup(null, 1, "Yes", "Yes");
            coll.Add(yes);
            AbstractGroup no = new AbstractGroup(null, 0, "No", "No");
            coll.Add(no);
            return coll;
        }

    }
}

Example Output

{
  "Table": [
    {
      "payer_id": 14437,
      "payer_student_link_id": 1437,
      "payer_transaction_id": 1569,
      "payer_transaction_batch_id": 3,
      "payer_ref": "A1OIL",
      "payer_name": "A1 Oil Industries",
      "forename": ".",
      "surname": "A1 Oil Industries",
      "payment_type": "Cash",
      "charge_ref": null,
      "pupil_ref": "DORJ7767",
      "pupil_name": "Jon Dorian",
      "transaction_type": "1",
      "outstandingAmount": 12907.50,
      "period1_amount": 6453.75,
      "period2_amount": 6453.75,
      "period3_amount": 0.00,
      "period4_amount": 0.00,
      "unallocated_amount": 0.00,
      "next_bill": 6453.75,
      "not_due": 0.00,
      "telephoneNo": null,
      "address": " P.O. Box 45  Road 2856    ",
      "transaction_date": "2012-09-08T00:00:00",
      "house_number": "",
      "house_name": "P.O. Box 45",
      "apartment": "",
      "street_description": "Road 2856",
      "district": "",
      "town": "",
      "county": "",
      "country": "",
      "postcode": "",
      "spl_arrangements": null,
      "TotalUnprocessed": "0.00",
      "TotalFutureInstalments": "0.00",
      "TotalPreviousUnprocessedInstalments": "0.00",
      "LastReceiptDate": "30-Jul-2018",
      "LastReceiptAmount": 12607.50
    },