Bank Intrest Calculation for Loans and Savings in C#.net

.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


namespace Intrest_Calculator
{
public partial class LoanCalc : Form
{
public LoanCalc()
{
InitializeComponent();
}


private void Calc_Loan_EMI(double Principal_Amount, double Term_Months, double Intrest_Rate)
{
Intrest_Rate = Intrest_Rate / 1200;
double Years = Term_Months / 12;
double Payable_Amount = Principal_Amount * Intrest_Rate / (1 - (Math.Pow(1 / (1 + Intrest_Rate), Term_Months)));
double Total_Amount = Payable_Amount * Term_Months;
double Total_Interest = Total_Amount - Principal_Amount;
double Yearly_Interest = Total_Interest / Years;
double Interest_PA = Yearly_Interest / Principal_Amount * 100;
double Interest_PM = (Yearly_Interest / Principal_Amount * 100) / 12;


labelmonthlyemi.Text = Payable_Amount.ToString("N3");
labeltotamountwithinterset.Text = Total_Amount.ToString("N3");
labelinterestpa.Text = Interest_PA.ToString("N3");
labelinterestpm.Text = Interest_PM.ToString("N3");
labeltotinterest.Text = Total_Interest.ToString("N3");
labelyearlyintersest.Text = Yearly_Interest.ToString("N3");
}


private void Calc_Amortization(double loanAmt, double Term_Months, double interestRate, double Installment_Number, double monthValue, double yearValue)
{
double interestRateForMonth = interestRate / 12; // (Monthly Rate of Interest in %)
double interestRateForMonthFraction = interestRateForMonth / 100; // (Monthly Interest Rate expressed as a fraction)
double emi = calculateEMI(loanAmt, interestRate, Term_Months);


var loanOustanding = loanAmt;
double totalPayment = 0;
double totalInterestPortion = 0;
double totalPrincipal = 0;
string installmentDate = string.Empty;
double interestPortion = 0, principal = 0;


List<CLS_AMORTIZATION> listamort = new List<CLS_AMORTIZATION>();
double month = 0, year = 0;


if (Installment_Number > Term_Months || Installment_Number == 0)
{
//The Installment must be less than or equal to the Tenure
}
else
{
for (int i = 1; i <= Term_Months; i++)
{
CLS_AMORTIZATION obj = new CLS_AMORTIZATION();


if (monthValue != 0)
{
month = monthValue + i - 1;
}
else
{
month = month + 1;
}


if (month > 12)
{
year = yearValue + 1;
yearValue = year;
monthValue = 0;
month = monthValue + 1;
}
else
{
year = yearValue;
}


if (month < 10)
{
installmentDate = "0" + month + "/" + year;
}
else
{
installmentDate = month + "/" + year;
}


if (loanOustanding == loanAmt)
{
loanOustanding = loanAmt;


obj.INSTALLMENTNO = i.ToString();
obj.INSTALLMENTDATE = installmentDate;
obj.OPENINGBALANCE = loanOustanding.ToString();
obj.EMI = emi.ToString();


totalPayment = totalPayment + emi;
interestPortion = loanOustanding * interestRateForMonthFraction;
interestPortion = roundDecimals(interestPortion, 0);
}
else
{
obj.INSTALLMENTNO = i.ToString();
obj.INSTALLMENTDATE = installmentDate;
obj.OPENINGBALANCE = loanOustanding.ToString();
obj.EMI = emi.ToString();


totalPayment = totalPayment + emi;
interestPortion = loanOustanding * interestRateForMonthFraction;
interestPortion = roundDecimals(interestPortion, 0);
}


loanOustanding = loanOustanding + interestPortion - emi;
loanOustanding = roundDecimals(loanOustanding, 0);


obj.LOANOUTSTANDING = loanOustanding.ToString();
obj.INTEREST = interestPortion.ToString();


totalInterestPortion = totalInterestPortion + interestPortion;
principal = roundDecimals(emi - interestPortion, 0);


obj.PRINCIPAL = principal.ToString();


totalPrincipal = totalPrincipal + principal;


listamort.Add(obj);
}


dataGridView1.DataSource = listamort;
}
}


private double calculateEMI(double loanAmt, double interestRate, double tenure)
{
if (interestRate != 0)
{
double interestRateForMonth = interestRate / 12; // (Monthly Rate of Interest in %)
double interestRateForMonthFraction = interestRateForMonth / 100; // (Monthly Interest Rate expressed as a fraction)
double emi = 1 / Math.Pow((1 + interestRateForMonthFraction), tenure);
double emiPerLakh = (loanAmt * interestRateForMonthFraction) / (1 - emi); // (EMI per lakh borrowed)
emiPerLakh = roundDecimals(emiPerLakh, 0);
return emiPerLakh;
}
else
{
double emi = loanAmt / tenure;
double emiPerLakh = roundDecimals(emi, 0);
return emiPerLakh;
}
}


private double roundDecimals(double original_number, int decimals)
{
double result1 = original_number * Math.Pow(10, decimals);
double result2 = Math.Round(result1);
double result3 = result2 / Math.Pow(10, decimals);


return (result3);
}


public class CLS_AMORTIZATION
{
public string INSTALLMENTNO { get; set; }
public string INSTALLMENTDATE { get; set; }
public string OPENINGBALANCE { get; set; }
public string EMI { get; set; }
public string LOANOUTSTANDING { get; set; }
public string INTEREST { get; set; }
public string PRINCIPAL { get; set; }
}


private void eMIToolStripMenuItem_Click(object sender, EventArgs e)
{
groupBoxcalcemi.Visible = true;
}


private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}


private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
{


}


private void buttonCalculateemi_Click(object sender, EventArgs e)
{
try
{
double Loan_Amt = Convert.ToDouble(textBoxloanamount.Text), Tenture = Convert.ToDouble(textBoxtenture.Text), Interest = Convert.ToDouble(textBoxInterestRate.Text);
Calc_Loan_EMI(Loan_Amt, Tenture, Interest);
Calc_Amortization(Loan_Amt, Tenture, Interest, 1, DateTime.Now.Month, DateTime.Now.Year);
groupBoxLoandetails.Visible = true;
groupBoxrepaydetails.Visible = true;
}
catch (Exception ex)
{
groupBoxLoandetails.Visible = false;
groupBoxrepaydetails.Visible = false;
MessageBox.Show("Sorry! there is a error: " + ex.Message);
}
}
}
}

You Can Download the Working Code From here.

Posted by
Labels:

MySql Bulk Loader in C#.net


Create the table in your DataBase.

CREATE TABLE TEST_PK
(
ID_MAIN BIGINT NOT NULL,
ID_SUB BIGINT NOT NULL,/* Dont Use: AUTO_INCREMENT This will not correctly incremented with Bulkloader*/
T_ID BIGINT,
T_TEXT VARCHAR(50),
T_CREATEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
STATUS INT DEFAULT 1,
PRIMARY KEY (ID_MAIN,ID_SUB)
);
SELECT * FROM TEST_PK;

Here we are going load bulk data from specified source.First we will load the data into a text file then
loads the data in to database from text file using MySqlBulkLoader.Here table consists two primary keys so it will check for existing records based on two primary keys (like select * from TEST_PK where ID_MAIN =input1 and ID_SUB=input2),if they are exists base on ConflictOption(Replace i.e update or None/Ignore) it process the data.

Program.cs:
using System;
using System.Configuration;
using System.Data;
using System.Text;

namespace MySqlBulkLoader_CSharp
{
internal class Program
{
private static void Main(string[] args)
{
Console.WriteLine("Program Started.Please wait...");
Do_work obj = new Do_work();
obj.Update_Data(MyFunction.Get_Dump());
Console.WriteLine("Program Completed.");
}
}

internal static class MyFunction
{
private static string Message = string.Empty;

public static DataTable Get_Dump()
{
DataTable dt = new DataTable();

DataColumn dc1 = new DataColumn();
dc1.ColumnName = "ID_MAIN";
dc1.DataType = System.Type.GetType("System.Int32");
dt.Columns.Add(dc1);

DataColumn dc2 = new DataColumn();
dc2.ColumnName = "ID_SUB";
dc2.DataType = System.Type.GetType("System.Int32");
dt.Columns.Add(dc2);

DataColumn dc3 = new DataColumn();
dc3.ColumnName = "T_ID";
dc3.DataType = System.Type.GetType("System.Int32");
dt.Columns.Add(dc3);

DataColumn dc4 = new DataColumn();
dc4.ColumnName = "T_TEXT";
dc4.DataType = System.Type.GetType("System.String");
dt.Columns.Add(dc4);

for (int i = 1; i <= 100; i++)
{
DataRow dr = dt.NewRow();
dr["ID_MAIN"] = i;
dr["ID_SUB"] = i + 2;
dr["T_ID"] = i + 10000;
dr["T_TEXT"] = "This is" + (i);

dt.Rows.Add(dr);
}

dt.AcceptChanges();

return dt;
}

public static void Bulk_Update(string FileName, string query, string TableName)
{
System.IO.File.WriteAllText(FileName, query);
bool retval = MySqlCon.MySqlBULK(FileName, TableName);

if (retval)
{
Message = string.Format("==========> Successfully MySqlBulkLoader executed for the TableName: {0},FileName: {1}<==========", TableName, FileName);
Console.WriteLine(Message);
}
else
{
Message = string.Format("==========> Failed to execute MySqlBulkLoader for the TableName: {0},FileName: {1}<==========", TableName, FileName);
Console.WriteLine(Message);
}
}
}

internal class Do_work
{
private const int Row_Split_Count = 500;

public void Update_Data(DataTable dt)
{
if (dt.Rows.Count > 0)
{
string MysqlCreatedDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string RecordStatus = "1";
StringBuilder query = new StringBuilder();
string TableName = "TEST_PK";
string FileName = string.Empty;
FileName = ConfigurationManager.AppSettings["FILEPATH"] + TableName + ".txt";

int No_of_times = dt.Rows.Count / Row_Split_Count;
int reminder = dt.Rows.Count % Row_Split_Count;

for (int KU = 0; KU < No_of_times; KU++)
{
query.Clear();
DataRow dr = dt.Rows[KU * Row_Split_Count];

query.Append(Form_Query(dr, MysqlCreatedDate, RecordStatus));

for (int PK = (KU * Row_Split_Count) + 1; PK < (KU + 1) * Row_Split_Count; PK++)
{
dr = dt.Rows[PK];
query.Append(@"{;:}" + Form_Query(dr, MysqlCreatedDate, RecordStatus));
}

MyFunction.Bulk_Update(FileName, query.ToString(), TableName);
query.Clear();
}

if (reminder != 0)
{
DataRow dr = dt.Rows[Row_Split_Count * No_of_times];
query.Clear();
query.Append(Form_Query(dr, MysqlCreatedDate, RecordStatus));

for (int PK = (Row_Split_Count * No_of_times) + 1; PK < dt.Rows.Count; PK++)
{
dr = dt.Rows[PK];
query.Append(@"{;:}" + Form_Query(dr, MysqlCreatedDate, RecordStatus));
}

MyFunction.Bulk_Update(FileName, query.ToString(), TableName);
query.Clear();
}
}
}

private string Form_Query(DataRow dr, string MysqlCreatedDate, string RecordStatus)
{
string Query = string.Empty;

Query = dr["ID_MAIN"].ToString() + "(:;)" + dr["ID_SUB"].ToString() + "(:;)" + dr["T_ID"].ToString() + "(:;)" + dr["T_TEXT"].ToString() + "(:;)" + MysqlCreatedDate + "(:;)" + RecordStatus + "(:;)";

return Query;
}
}
}


MySqlCon.cs:

using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;

namespace MySqlBulkLoader_CSharp
{
public static class MySqlCon
{
private static MySqlCommand cmd;
private static MySqlConnection con;

static MySqlCon()
{
}

public static void Closeconnection()
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}

public static bool MySqlBULK(string FileName, string TableName)
{
bool retval = false;
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString();
MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
MySqlBulkLoader bl = new MySql.Data.MySqlClient.MySqlBulkLoader(conn);

bl.TableName = TableName;
bl.FieldTerminator = "(:;)";
bl.LineTerminator = "{;:}";
bl.FileName = FileName;
bl.NumberOfLinesToSkip = 0;
bl.ConflictOption = MySql.Data.MySqlClient.MySqlBulkLoaderConflictOption.Replace;
//Replace: use this option if you want to update the record if already existing.This will check only primary key,if exists updates all columns of the row.
//Ignore: use this option if you want to leave the record if already existing(This will check only primary key).Other wise updated all columns of the row.
//None: use this option if you wnat to do nothing if already existing.This will check only primary key.This will not update anything.

try
{
conn.Open();
bl.Load();
conn.Close();
retval = true;
}
catch (Exception ex)
{
retval = false;
}

return retval;
}

private static void CreateConnection()
{
try
{
con = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString);
cmd = new MySqlCommand();
cmd.Connection = con;
}
catch (Exception ex)
{
}
}
}
}

You Can Download the Working Code From here.

Posted by
Labels:
Subscribe to: Posts (Atom)