WALDEMAR KARWOWSKI SGGW Warszawa
Summary
In the paper payments module of integrated software system, dedicated for the Bogdan Jaski Academy, is described. The main aim is to show how payments mod-ule of the system was designed and implemented using only open source tools, and how module was integrated with commercial bank system. The first section describes importance of settlement of students accounts at non public university. In the next section general view of the Students Service System is presented with particular fo-cus on module for management of student payments. Next technological issues are presented, chosen software tools for system development are listed. Moreover inte-gration with Trans-Collect bank system is described and general equipment re-quirements. The fourth section includes description of user interface. At the end conclusions and remarks from system realization are presented.
Keywords: information technology, information system, PostgreSQL 1. Introduction
During the past decade scholarisation level has substantially grown in Poland. Students of non public universities have to pay for their study. Payment management is relatively simple when the number of students does not exceed a few hundreds what was exactly the case at the beginning of Bogdan Janski Academy. All ambiguities related to individual payments could directly be ex-plained by involved students during simple visit in any accounts department. As the Academy grew up increasing the number of the external branches each branch had to posses its own account de-partment. Such a situation was quite inconvenient generating additional costs. Now, thanks to a new information system, all external branches of the Janski Academy make use of one central accounts department. Students do not have to personally visit accounts department and do not have to provide any additional information to obtain, e.g. a certificate for military service, because all necessary information is already incorporated into the system. This dramatically shortened the time spent by students on removing burocratical obstacles and save a lot of work of administrative staff of the Academy. Before introducing the information system developed by one of the authors of the present paper the Academy actually purchased another system called Socrates. However, for some reasons it was not successfully implemented. We suspect that the reason for that was the failure of training process. It seems to us that the subsequent success of the system we described in the pre-sent article could be attributed to the multiple-stage methodology. The system was built in stages. After each phase any particular fragment of the system was implemented and discussed with the most interested staff of the Academy. The concentration on the most important features of the sys-tem enabled us to spend enough time to thoroughly consult the involved staff and prospective users
of the system. Thanks to such an interaction we could achieve desirable look and content of win-dows with menu commands and space for inserting data. Simplicity of winwin-dows reduce the neces-sity of training the staff. Picking up the browser as the software on the client side (well known to the average user) minimized time spent by professionals on helping users.
2. Students Service System
Student Service System is a client-sever internet application with centralized database. It sup-ports everyday work of BJA Dean Offices in 6 cities (Warszawa, Chełm, Kraków, Opole, Zabrze and Elblg), but open architecture makes possible to deal with unlimited number of offices. Cur-rently more than 8000 students data are managed trough the system, this number is limited only by capacity of hard drives. Server is located in Warszawa - BJA headquarter, client software consists of standard web browser, and as we mentioned earlier, access to the Internet is necessary. Whole system is built using technologies with open specification and free license. There are Java language together with servlets and Java Server Pages technology (JSP) used in Eclipse IDE. For presenta-tion layer were used HTML, XML, XSLT, Apache Cocoon and XSP. As a database PostgreSQL was utilized with SQL language and its procedural extension plpgsql. On the server side Linux op-erating system is installed and database together with Cocoon engine run on it. Such tools are con-nected with limited financial resources for implementation but they turned out to be a very good and stable solution. Relational database supporting most popular standard – SQL, makes system open for integration with other systems and for future developing. Any authorized person can con-nect with database for reading and writing, adding new tables is easy. Internet browser is the sim-plest and cheapest user interface software, and in practice do not need upgrading if new versions of the system appear. Such solution with commonly used Internet connection, without dedicated pri-vate network and specialized client application, saves money but causes data security problem. Stored data are personal data and cannot be disclosure, additionally there are information con-nected with finances, unauthorized access could destroy whole academy activity. To solve prob-lem, system supports users roles with assigned access to limited parts of the system, and strong data encoding are implemented. Authorization is performed with SSL protocol, all information about users like: name, position, role and level of privileges are stored in the database; user login and password are stored in encoded form. After user is logged on, new session is started which supports user identification during work with the system. If user finishes activity she/he should log out, but if after a defined time no action is performed log out is made automatically. Information about time of logging and user computer are registered in log file. Similarly most important opera-tions like entering student, adding payments etc. are registered too, and they are possible to review only by administrator. We have to mention that students itself cannot use the system which is dedi-cated only to BJA staff, it is less comfortable for students but safety level is higher.
Now we present software and hardware specification of Student Service System which is cur-rently in use in BJA. We start with software.
• Operating system: Linux Fedora Core 4. • Database: PostgreSQL version 8.0.7
• Servlet container - application server for Cocoon: Jetty 4.2.23 • Application platform: Cocoon 2.1.9
• Programming language: Java version 1.5.0
payment. Because Java, PostgreSQL and Jetty can be installed not only on Linux platform it is possible to use other operation systems for example Microsoft Windows. Every software element can be installed on separate machine if bigger computing power will be needed.
Current server configuration is the following (all mentioned parameters are recommended). • Processor – two processors Opteron 246.
• Mainboard – TYAN Thunder K8WE
• RAM – two pieces of 1GB 400MHz dual channnel DDR Kingstone • Hard drive - two 160GB Samsung SATAII (HD160JJ NCQ) • Recorder - DVD RAM
• Power supplier - Termaltake W0049 PurePower 680W
Recommended configuration of client Workstation - any working computer with internet browser (Firefox version 1.5.0 or above is recommended), Acrobat Reader (version 5.05) for view-ing PDF files or MS Office package or OpenOffice – 2.0.
General parameters of Student Service System. Number of system users: unlimited.
Quantity of data stored in the database: limited only by hard drive capacity.
Operation speed: almost all queries to the database in above configuration are performed in time less than 1 second, in practice operation speed depends on internet transfer.
3. Modules of Students Service System
Student Service System offers set of functions important for effective Dean Office functional-ity. System has modularized architecture, it consists of many separate and relatively independent modules, such as Dean Office management, Documents, Reports, Support of financial accounting plan, Student data, Payments management, Student grants, Groups, Lecturers, Plans of study, and Schedule. On the figure 1. is presented general view of modules and dependency between them.
Fig.1. System modules Source: Own preparation
We shortly characterize particular modules with special focus on module functionality. System was developing during three years, module after module. Dean office management is kind of su-pervising module which coordinates using of many other modules. First of them is student data module, it deals with student identification number, personal data, address and information about bank accounts. User using this module enters student data into system. Next we have Payments management, it is very important and historically whole system started from them. BJA is a non-public university and strongly depends on students fees, need of managing and clearing student accounts, lays at the beginning of application. At academy’s bank account, every student has its own subaccount for fee payments. Payments management module is integrated with bank system, and can stores dues and fees brings up to date every day. Module can export all student balances into accounting program. This module makes available presentation of delayed fees, additionally some corrections are possible to be done by hand. Following module is Student grants, it gives possibility to register grant proposals and after confirmation generates documents with grant pay-ments for accounting department. Module is integrated with banking program Multicash which generates money transfers. Next group of modules are connected with didactic process. They were added to the system relatively late, when dean office workers realized that computer system can support their work not only with financial issues but can help in student groups management and lectures planning. Groups module gives possibility to attach students into particular group, and generates appropriate lists, protocols etc. Lecturers module consists professors and teaching assis-tants data together with connected information. Scheduling module enables planning courses time-table using defined courses template and is strictly connected with study plans. It makes possible verification if number of hours filled in schedule corresponds to number of hours from study plan. Plan of study includes a list of “study plan positions” that is a list of all courses with defined num-ber of lectures and exercises hours for every semester together with additional information about exams (it may happen that course is continued trough two semesters with one final exam). Finally we have three independent modules. All reports are gathered in one module, there are defined sev-eral dozen of them, mainly for financial operations. Among others it includes reports for Govern-ment Statistical Office. DocuGovern-ments module makes possible for example generation of calls for payment. Support for financial accounting has functions for checking realization of didactic tasks by lecturers and many other functions.
4. Design and implementation of payments management module
As we mentioned in the previous section Payments management module is most important and whole idea of the system started from it. Payments module is integrated with bank system interface called Trans-Collect. Payments data are imported from bank system and stored in the database. Process of checking fees is easy. System can trace automatically all student dues and fees then pre-pare reports and generate demands for delayed payments etc. Every document is prepre-pared in Adobe Reader, Open Office or Microsoft Office format depending on user need. It is easy to read or print it. Payments management gives possibility to supervise fee payment process and export financial data to accounting application.
Of course every module is strictly connected with a set of database tables. Tables connected with payments are presented on the figure 2.
.
Fig. 2. Database tables connected with payments
We have to note that every student has its own subaccount in BJA bank accounts which in-cludes number of student book registration and digits connected with year of first registration, de-partment and type of fee. Student subaccount number is generated according to NRB standard, al-gorithm was adopted to needs of BJA.. Parts of students subaccount number are presented on the figure 3. Such construction of subaccount number easies payment attachment to particular student, because part of the number is student identifier.
Generation process is implemented in plpgsql language and presented below:
CREATE FUNCTION generuj_konto_bankowe(character varying, character varying, character varying, character varying) RETURNS character varying
AS $_$ DECLARE numer varchar; id_o varchar; id_w varchar; rok varchar; indeks varchar; typ varchar; koncowka varchar; konto varchar; konto_ck varchar; wynik int4; a1 int8; a2 int8; a3 int8; a4 int8; ck int8; BEGIN konto='10600018'; typ='9'; koncowka='252100'; rok=$2; indeks=$1;
select into id_o idc_oddzial from id_rachunku where oddzial=$3 and wydzial=$4;
select into id_w idc_wydzial from id_rachunku where oddzial=$3 and wydzial=$4; konto_ck=id_o||id_w||rok||indeks||typ; if length(konto_ck)<>15 then return null; end if; wynik=0; for i in 1..15 loop
if i%4=1 then wynik=wynik+int4(substring(konto_ck,i,1)); end if; if i%4=2 then wynik=wynik+int4(substring(konto_ck,i,1))*3; end if; if i%4=3 then wynik=wynik+int4(substring(konto_ck,i,1))*7; end if; if i%4=0 then wynik=wynik+int4(substring(konto_ck,i,1))*9; end if; end loop; wynik=wynik%10; numer=konto||konto_ck||wynik||koncowka; a1=int8(substring(numer,1,10)); a2=int8(substring(numer,11,8)); a3=int8(substring(numer,19,8)); a4=int8(substring(numer,27,4)); ck=98-(((((((a1%97)*100000000+a2)%97)*100000000+a3)%97)*10000+a4)%97); if ck<10 then
numer='0'||ck||' '||konto||' '||id_o||' '||id_w||rok||indeks||typ||wynik; else
numer=ck||' '||konto||' '||id_o||' '||id_w||rok||indeks||typ||wynik; end if; return numer; END; $_$ LANGUAGE plpgsql;
bo-oked during previous day. To import file, dedicated user has to log on at bank web page using standard browser, and downloads it. This process is separated from the System, only dedicated user knows login and password for bank web page. Process is not done automatically from security rea-sons. Finally dedicated user imports file into Student Service System. Name of imported file is not precisely defined, but is unique, and consists of 8 digits and txt extension. First two digits are con-nected with department, next six represent date in YYMMDD format. System stores names of all previously downloaded files, it means that duplication is not allowed. Data received from bank are in text format with comma separated values, first line includes information about total sum of all payments and is used as verification checksum. Following lines contain individual payments. Dur-ing importDur-ing mentioned checksum is verified. List of all payments files are available to review and data can be exported in MS Excel format if user needs it.
5. User Interface for Payments Module
From the main window menu user can chose Payments module. After than user is moved to Students payments window. On that window it is possible searching of particular student, accord-ing to family name, PESEL number, BJA identification number or group. If wanted student is found, window with proper student information is displayed (see figure 4). User can generate pro-per document if needed or view plan of payments.
In the window current balance is presented. Student balance is generated automatically when window is opened and it is one of the most important information for dean office staff. If student for example wants to stamp identity card or receive any document, non negative balance is neces-sary. Lack of positive balance causes sending demand for payment or ultimately removing perma-nent debtor from BJA.. By default only financial operations occurred during current academic year are displayed, but it is possible to display all historical financial data if needed. Of course if student wants information in paper form, dean office staff can easily print list of operations.
System administrator generates every month debit list (figure 5.) because typically payments are made monthly. I everything is correct process is performed automatically, staff activity is ne-eded only if payments are made yearly, one time at the beginning of the semester or if some in-compatibility appears. Debit lists are exported to accounting system after 20 day of each month..
Fig.5. Window for debit generation 6. Concluding remarks
The Students Service System is fully functional information system. Payments modules are first part of the system and have been working for 3 years. Whole system is in use one year. During that time, there were not bigger problems. More than 8000 students are registered in the system. Every day 51 registered staff members use system to perform their tasks. Functionality of the module has been changed several times to meet changing users expectations. As a result of payments module success, other modules were developed around it. Although system works perfectly there are still many new user demands. Module architecture is very helpful in regular updating of the system.. Tools and technologies used for system implementation proved to be very useful and flexible. Independent database makes data available to other applications through JDBC and ODBC what gives chance for other programmers to integrate with other systems. To
summarize, experience gained during the payments module of Students Service System project shows, that open source tools with proper system design, are possible to respond to user requirements.
7. Literature
1. Budzyski R., Budowa i wdroenie Systemu Obsługi Studenta w Szkole Wyszej im. Bogdana Jaskiego. Master thesis (in Polish), Warszawa 2007.
2. Cocoon website: http://cocoon.apache.org/
3. Ellis, S. (2006). Fedora Core 5 Installation Guide. From http://docs.fedoraproject.org /fedora-install-guide-en/fc5/
4. Fortis Bank Polska. (2007). IBAN. From http://www.fortisbank.com.pl/services/ planet/ PlanetFAQIBANPL.html
5. PostgreSQL Global Development Group. (2007). PL/pgSQL - SQL Procedural Language. From http://www.postgresql.org/docs/7.4/interactive/plpgsql.html
Robert Budzyski Arkadiusz Orłowski Waldemar Karwowski Katedra Informatyki
Wydział Zastosowa Informatyki i Matematyki Szkoła Główna Gospodarstwa Wiejskiego 02-776 Warszawa, ul. Nowoursynowska 159 e-mail: robert_budzynski@sggw.pl
e-mail: arkadiusz_orlowski@sggw.pl e-mail: waldemar_karwowski@sggw.pl
