public static List <Plans> showExistingPlans()
{
List <Plans> allPlans = new List <Plans>();
try
{
String str = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = 'F:\DDU STUFF\SEM 6\OOSE\LICPremiumPortal\LICPremiumPortal\LICPremiumPortal\LICPremiumPortalDB.mdf'; Integrated Security = True";
String query = "Select * from Plans";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
Plans plan;
while (rdr.Read())
{
plan = new Plans();
plan.planId = Convert.ToInt32(rdr["planId"]);
plan.planName = rdr["Plan_Name"].ToString();
plan.planDescription = rdr["Plan_Description"].ToString();
plan.planCategory = rdr["Plan_Category"].ToString();
plan.minimumTermSpan = Convert.ToInt32(rdr["Plan_TermSpan"]);
plan.minimumAmount = Convert.ToDouble(rdr["Plan_MinimumAmount"]);
allPlans.Add(plan);
}
}
catch (Exception es)
{
MessageBox.Show(es.Message);
}
return(allPlans);
}
public static void AddNewPlan(Plans plan)
{
try
{
String str = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = 'F:\DDU STUFF\SEM 6\OOSE\LICPremiumPortal\LICPremiumPortal\LICPremiumPortal\LICPremiumPortalDB.mdf'; Integrated Security = True";
String query = "Insert into Plans( Plan_Name , Plan_Description , Plan_Category , Plan_TermSpan , Plan_MinimumAmount ) values ('" + plan.planName + "','" + plan.planDescription + "','" + plan.planCategory + "'," + plan.minimumTermSpan + "," + plan.minimumAmount + ")";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
MessageBox.Show("Error inserting data into database !");
}
else
{
MessageBox.Show(" New Plan added successfully to the list :)");
}
}
catch (Exception m)
{
MessageBox.Show(m.Message);
}
}
//find button
private void Button1_Click(object sender, EventArgs e)
{
Plans foundplan = new Plans();
if (radioButton1.Checked == true)
{
foundplan = Plans.FindPlanById(Convert.ToInt32(textBox1.Text));
}
else if (radioButton2.Checked == true)
{
foundplan = Plans.FindPlanByName(textBox1.Text);
}
if (foundplan != null)
{
groupBox2.Visible = true;
Pid = foundplan.planId;
Pname = foundplan.planName;
label7.Text = foundplan.planId.ToString();
label8.Text = foundplan.planName;
label11.Text = foundplan.planCategory;
label12.Text = foundplan.minimumTermSpan.ToString();
label13.Text = foundplan.minimumAmount.ToString();
label14.Text = foundplan.planDescription;
}
}
public static void RevisePlanStatistics(Plans plan)
{
try
{
String str = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = 'F:\DDU STUFF\SEM 6\OOSE\LICPremiumPortal\LICPremiumPortal\LICPremiumPortal\LICPremiumPortalDB.mdf'; Integrated Security = True";
String query = "Update Plans set Plan_Name ='" + plan.planName + "', Plan_Description='" + plan.planDescription + "', Plan_Category='" + plan.planCategory + "', Plan_TermSpan=" + plan.minimumTermSpan + ", Plan_MinimumAmount=" + plan.minimumAmount + " where planId =" + plan.planId;
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
MessageBox.Show("There was some error in updating the plan details !");
}
else
{
MessageBox.Show(" Plan " + plan.planName + " updated successfully :)");
}
}
catch (Exception m)
{
MessageBox.Show(m.Message);
}
}
//Find button Clicked
private void Button1_Click(object sender, EventArgs e)
{
Plans foundplan = new Plans();
if (radioButton1.Checked == true)
{
foundplan = Plans.FindPlanById(Convert.ToInt32(textBox1.Text));
}
else if (radioButton2.Checked == true)
{
foundplan = Plans.FindPlanByName(textBox1.Text);
}
//if(foundplan != null)
// MessageBox.Show(" Found Plan = "+foundplan.planName);
if (foundplan != null)
{
groupBox2.Visible = true;
label7.Text = foundplan.planId.ToString();
label8.Text = foundplan.planName;
comboBox1.Text = foundplan.planCategory;
numericUpDown1.Value = foundplan.minimumTermSpan;
textBox2.Text = foundplan.minimumAmount.ToString();
richTextBox1.Text = foundplan.planDescription;
}
}
// Display All Plans
private void Button2_Click(object sender, EventArgs e)
{
List <Plans> show;
show = Plans.showExistingPlans();
dataGridView1.DataSource = show;
}
// Add Button Clicked
private void Button1_Click(object sender, EventArgs e)
{
Plans plan = new Plans();
plan.planName = textBox1.Text;
plan.planCategory = comboBox1.SelectedItem.ToString();
plan.minimumTermSpan = Convert.ToInt32(numericUpDown1.Value);
plan.minimumAmount = Convert.ToDouble(textBox4.Text);
plan.planDescription = richTextBox1.Text;
Plans.AddNewPlan(plan);
this.Close();
}
// Click on update button
private void Button3_Click(object sender, EventArgs e)
{
Plans updated = new Plans();
updated.planId = Convert.ToInt32(label7.Text);
updated.planName = label8.Text;
updated.planCategory = comboBox1.SelectedItem.ToString();
updated.minimumTermSpan = Convert.ToInt32(numericUpDown1.Value);
updated.minimumAmount = Convert.ToDouble(textBox2.Text);
updated.planDescription = richTextBox1.Text;
Plans.RevisePlanStatistics(updated);
this.Close();
}
public CustomerPortal()
{
InitializeComponent();
label1.Text = " Welcome " + Form1.username + " !";
dataGridView1.DataSource = Plans.showExistingPlans();
comboBox1.Text = "All Available";
if (Form1.username == "Chairman")
{
button2.Visible = true;
}
else
{
button2.Visible = false;
}
}
//next button clicked
private void Button2_Click(object sender, EventArgs e)
{
groupBox2.Visible = true;
calc = Plans.FindPlanByName(comboBox1.Text);
label7.Text = calc.planId.ToString();
label8.Text = calc.planName;
label12.Text = calc.minimumTermSpan.ToString();
label13.Text = calc.minimumAmount.ToString();
numericUpDown2.Value = calc.minimumTermSpan;
numericUpDown2.Minimum = calc.minimumTermSpan;
textBox2.Text = calc.minimumAmount.ToString();
}
//Find button clicked
private void Button1_Click(object sender, EventArgs e)
{
Plans foundplan = new Plans();
if (radioButton1.Checked == true)
{
foundplan = Plans.FindPlanById(Convert.ToInt32(textBox1.Text));
}
else if (radioButton2.Checked == true)
{
foundplan = Plans.FindPlanByName(textBox1.Text);
}
//if(foundplan != null)
// MessageBox.Show(" Found Plan = "+foundplan.planName);
if (foundplan != null)
{
dataGridView1.DataSource = new List <Plans>()
{
foundplan
}
}
;
}
public static Plans FindPlanById(int planId)
{
Plans plan = null;
try
{
String str = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = 'F:\DDU STUFF\SEM 6\OOSE\LICPremiumPortal\LICPremiumPortal\LICPremiumPortal\LICPremiumPortalDB.mdf'; Integrated Security = True";
String query = "Select * from Plans where planId=" + planId + "";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
plan = new Plans();
plan.planId = planId;
plan.planName = rdr["Plan_Name"].ToString();
plan.planDescription = rdr["Plan_Description"].ToString();
plan.planCategory = rdr["Plan_Category"].ToString();
plan.minimumAmount = Convert.ToDouble(rdr["Plan_MinimumAmount"]);
plan.minimumTermSpan = Convert.ToInt32(rdr["Plan_TermSpan"]);
//MessageBox.Show(" found : "+ rdr["Plan_Name"].ToString());
}
else
{
MessageBox.Show("No such Plan Found with such ID :(");
}
}
catch (Exception es)
{
MessageBox.Show(es.Message);
}
return(plan);
}
//Show results button clicked
private void Button4_Click(object sender, EventArgs e)
{
//do
//{
// foreach (DataGridViewRow row in dataGridView1.Rows)
// {
// try
// {
// dataGridView1.Rows.Remove(row);
// }
// catch (Exception) { }
// }
//} while (dataGridView1.Rows.Count > 1);
//dataGridView1.Rows.Clear();
//dataGridView1.Refresh();
if (comboBox1.Text == "All available")
{
List <Plans> show;
dataGridView1.DataSource = null;
show = Plans.showExistingPlans();
if (show != null)
{
dataGridView1.DataSource = show;
//dataGridView1.DataSource = null;
//dataGridView1.AllowUserToOrderColumns = true;
//dataGridView1.ColumnCount = 6;
//dataGridView1.Columns[0].Name = "Plan Id";
//dataGridView1.Columns[1].Name = "Plan Name";
//dataGridView1.Columns[2].Name = "Plan Type";
//dataGridView1.Columns[3].Name = "Plan Description";
//dataGridView1.Columns[4].Name = "Minimum Policy Term";
//dataGridView1.Columns[5].Name = "Minimum Policy Amount";
//string[] rows;
//for (int i = 0; i < show.Count; i++)
//{
// DataGridViewRow r = new DataGridViewRow();
// rows = new string[] {
// show[i].planId.ToString() ,
// show[i].planName ,
// show[i].planCategory,
// show[i].planDescription,
// show[i].minimumTermSpan.ToString(),
// show[i].minimumAmount.ToString()
// };
// r.SetValues(rows);
// dataGridView1.Rows.Add(r);
//}
//show.Clear();
}
}
else if (comboBox1.Text == "My Ongoing")
{
List <PolicyHolders> ongoing = null;
ongoing = PolicyHolders.showOngoingPolicies();
if (ongoing != null)
{
var l = (from obj in ongoing select new { obj.policyNo, obj.planId.planId, obj.planId.planName,
obj.planId.planCategory, obj.policyTerm, obj.frequency, obj.optedAmount }).ToList();
dataGridView1.DataSource = l;
//dataGridView1.AllowUserToOrderColumns = true;
//dataGridView1.ColumnCount = 7;
//dataGridView1.Columns[0].Name = "Client Policy No";
//dataGridView1.Columns[1].Name = "Plan Id";
//dataGridView1.Columns[2].Name = "Plan Name";
//dataGridView1.Columns[3].Name = "Plan Type";
//dataGridView1.Columns[4].Name = "Policy Term";
//dataGridView1.Columns[5].Name = "Premium Payment Freq.";
//dataGridView1.Columns[6].Name = "Total Policy Amount";
//string[] rows;
//for(int i=0; i<ongoing.Count; i++)
//{
// DataGridViewRow r = new DataGridViewRow();
// rows = new string[] { ongoing[i].policyNo.ToString() ,
// ongoing[i].planId.planId.ToString() ,
// ongoing[i].planId.planName,
// ongoing[i].planId.planCategory,
// ongoing[i].policyTerm.ToString(),
// ongoing[i].frequency,
// ongoing[i].optedAmount.ToString()
// };
// r.SetValues(rows);
// dataGridView1.Rows.Add(r);
//}
//ongoing.Clear();
}
else
{
MessageBox.Show(" You do not have any ongoing policies! ");
}
}
}
// BACK BUTTON CLICKED
private void Button1_Click(object sender, EventArgs e)
{
calc = null;
this.Close();
}
// Delete button clicked
private void Button3_Click(object sender, EventArgs e)
{
Plans.DeletePlan(Convert.ToInt32(label7.Text));
this.Close();
}
public ManagePlans()
{
InitializeComponent();
dataGridView1.DataSource = Plans.showExistingPlans();
}