Hi,
You can try out Mifos with the Branch Progress Report, using the following
war file:
http://rapidshare.com/files/97735907/mifos.war
(sha1sum: 986ea0877c58ddbfdf28c00d6dcb87c6c0160812)
The new tables have not yet been added into an Upgrade script, So please
run these
queries manually on your mifos database.
DROP TABLE IF EXISTS BATCH_CLIENT_SUMMARY;
DROP TABLE IF EXISTS BATCH_STAFF_SUMMARY;
DROP TABLE IF EXISTS BATCH_LOAN_ARREARS_AGING;
DROP TABLE IF EXISTS BATCH_STAFFING_LEVEL_SUMMARY;
DROP TABLE IF EXISTS BATCH_LOAN_DETAILS;
DROP TABLE IF EXISTS BATCH_LOAN_ARREARS_PROFILE;
DROP TABLE IF EXISTS BATCH_BRANCH_REPORT;
CREATE TABLE BATCH_BRANCH_REPORT(
BRANCH_REPORT_ID INTEGER AUTO_INCREMENT NOT NULL,
BRANCH_ID SMALLINT NOT NULL,
RUN_DATE DATE NOT NULL,
PRIMARY KEY(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_CLIENT_SUMMARY(
CLIENT_SUMMARY_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
BRANCH_REPORT_ID INTEGER NOT NULL,
FIELD_NAME VARCHAR(50) NOT NULL,
TOTAL VARCHAR(50),
VPOOR_TOTAL VARCHAR(50),
FOREIGN KEY(BRANCH_REPORT_ID) REFERENCES
BATCH_BRANCH_REPORT(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_LOAN_ARREARS_AGING (
LOAN_ARREARS_AGING_ID INTEGER NOT NULL AUTO_INCREMENT,
AGING_PERIOD_ID INTEGER UNSIGNED NOT NULL,
BRANCH_REPORT_ID INTEGER NOT NULL,
CLIENTS_AGING INTEGER NOT NULL,
LOANS_AGING INTEGER NOT NULL,
AMOUNT_AGING DECIMAL(20,3) NOT NULL,
AMOUNT_AGING_CURRENCY_ID SMALLINT NOT NULL,
AMOUNT_OUTSTANDING_AGING DECIMAL(20,3) NOT NULL,
AMOUNT_OUTSTANDING_AGING_CURRENCY_ID SMALLINT NOT NULL,
PRIMARY KEY (LOAN_ARREARS_AGING_ID),
FOREIGN KEY (BRANCH_REPORT_ID)
REFERENCES BATCH_BRANCH_REPORT (BRANCH_REPORT_ID)
) ENGINE = InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_STAFF_SUMMARY(
STAFF_SUMMARY_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
BRANCH_REPORT_ID INTEGER NOT NULL,
PERSONNEL_ID SMALLINT NOT NULL,
PERSONNEL_NAME VARCHAR(50) NOT NULL,
JOINING_DATE DATE,
ACTIVE_BORROWERS INTEGER NOT NULL,
ACTIVE_LOANS INTEGER NOT NULL,
CENTER_COUNT INTEGER NOT NULL,
CLIENT_COUNT INTEGER NOT NULL,
NEW_GROUP_COUNT INTEGER NOT NULL,
LOAN_AMOUNT_OUTSTANDING DECIMAL(20,3) NOT NULL,
LOAN_AMOUNT_OUTSTANDING_CURRENCY_ID SMALLINT NOT NULL,
INTEREST_FEES_OUTSTANDING DECIMAL(20,3) NOT NULL,
INTEREST_FEES_OUTSTANDING_CURRENCY_ID SMALLINT NOT NULL,
PORTFOLIO_AT_RISK DECIMAL(20,3) NOT NULL,
FOREIGN KEY(BRANCH_REPORT_ID) REFERENCES
BATCH_BRANCH_REPORT(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_STAFFING_LEVEL_SUMMARY(
STAFFING_LEVEL_SUMMARY_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
BRANCH_REPORT_ID INTEGER NOT NULL,
ROLE_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(50) NOT NULL,
PERSONNEL_COUNT INTEGER NOT NULL,
FOREIGN KEY(BRANCH_REPORT_ID) REFERENCES
BATCH_BRANCH_REPORT(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_LOAN_DETAILS(
LOAN_DETAILS_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
BRANCH_REPORT_ID INTEGER NOT NULL,
PRODUCT_NAME VARCHAR(50) NOT NULL,
NUMBER_OF_LOANS_ISSUED INTEGER NOT NULL,
LOAN_AMOUNT_ISSUED DECIMAL(20,3) NOT NULL,
LOAN_AMOUNT_ISSUED_CURRENCY_ID SMALLINT NOT NULL,
LOAN_INTEREST_ISSUED DECIMAL(20,3) NOT NULL,
LOAN_INTEREST_ISSUED_CURRENCY_ID SMALLINT NOT NULL,
NUMBER_OF_LOANS_OUTSTANDING INTEGER NOT NULL,
LOAN_OUTSTANDING_AMOUNT DECIMAL(20,3) NOT NULL,
LOAN_OUTSTANDING_AMOUNT_CURRENCY_ID SMALLINT NOT NULL,
LOAN_OUTSTANDING_INTEREST DECIMAL(20,3) NOT NULL,
LOAN_OUTSTANDING_INTEREST_CURRENCY_ID SMALLINT NOT NULL,
FOREIGN KEY(BRANCH_REPORT_ID) REFERENCES
BATCH_BRANCH_REPORT(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
CREATE TABLE BATCH_LOAN_ARREARS_PROFILE(
LOAN_ARREARS_PROFILE_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
BRANCH_REPORT_ID INTEGER NOT NULL,
LOANS_IN_ARREARS INTEGER NOT NULL,
CLIENTS_IN_ARREARS INTEGER NOT NULL,
OVERDUE_BALANCE DECIMAL(20,3) NOT NULL,
OVERDUE_BALANCE_CURRENCY_ID SMALLINT NOT NULL,
UNPAID_BALANCE DECIMAL(20,3) NOT NULL,
UNPAID_BALANCE_CURRENCY_ID SMALLINT NOT NULL,
LOANS_AT_RISK INTEGER NOT NULL,
OUTSTANDING_AMOUNT_AT_RISK DECIMAL(20,3) NOT NULL,
OUTSTANDING_AMOUNT_AT_RISK_CURRENCY_ID SMALLINT NOT NULL,
OVERDUE_AMOUNT_AT_RISK DECIMAL(20,3) NOT NULL,
OVERDUE_AMOUNT_AT_RISK_CURRENCY_ID SMALLINT NOT NULL,
CLIENTS_AT_RISK INTEGER NOT NULL,
FOREIGN KEY(BRANCH_REPORT_ID) REFERENCES
BATCH_BRANCH_REPORT(BRANCH_REPORT_ID)
)ENGINE=InnoDB CHARACTER SET utf8;
Follow these instructions to configure the batch job and Branch Manager
role name:
1) Put the war file in tomcat\webapps and start Tomcat so that mifos.war
gets unzipped
2) Stop tomcat
3) Open the file task.xml in
mifos\WEB-INF\classes\org\mifos\framework\util\resources\batchjobs\
Uncomment the last task in that file, called BranchReportTask. Currently
it is set to run at
10 in the morning. If you want the batch job to run as soon as the server
starts next time, set it to a time
of the day before the current time.
4) To configure the role name for the Branch Manager, open
applicationConfiguration.default.properties
in the folder mifos\WEB-INF\classes\org\mifos\config\resources. Set the
property
RolesAndPermissions.BranchManager.RoleName to the correct role name as
documented
there.
5) Restart Tomcat. The batch job will run immediately. Login after it
finishes (keep refreshing login page every
few minutes).
6) Go to Admin tab and click on Upload report template in the Reports
section. On the subsequent page,
upload the file report\BranchReport.rptdesign.
7) You will see the new report listed in the Reports tab. Click on its
name. Select a suitable Branch office and
enter today's date (the date when batch job was run) to generate the
report.
Pramod Biligiri,
ThoughtWorks-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
