public Advisor()
{
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
try
{
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value AS Genders, Designation ,a.Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id ";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.Designation = (int)reader.GetValue(8);
stu.Designations = reader.GetString(9);
stu.Salary = (decimal)reader.GetValue(10);
students.Add(stu);
}
AdvisorDataGrid.DataSource = students;
AdvisorDataGrid.Columns[0].Visible = false;
AdvisorDataGrid.Columns[6].Visible = false;
AdvisorDataGrid.Columns[7].Visible = false;
DataGridViewButtonColumn bcol = new DataGridViewButtonColumn();
bcol.HeaderText = "";
bcol.Text = "Edit";
bcol.Name = "btnEdit";
bcol.UseColumnTextForButtonValue = true;
AdvisorDataGrid.Columns.Add(bcol);
DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn();
bcol1.HeaderText = "";
bcol1.Text = "Delete";
bcol1.Name = "btnDelete";
bcol1.UseColumnTextForButtonValue = true;
AdvisorDataGrid.Columns.Add(bcol1);
cmd = "Select FirstName, LastName, Contact, Email, Value AS Genders, Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id";
reader = DatabaseConnection.getInstance().getData(cmd);
int i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategory.Items.Contains(reader.GetName(i)))
{
SearchCategory.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategory.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Refresh()
{
try
{
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value, Designation ,a.Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id ";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.Designation = (int)reader.GetValue(8);
stu.Designations = reader.GetString(9);
stu.Salary = (decimal)reader.GetValue(10);
students.Add(stu);
}
AdvisorDataGrid.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public AdvisorEdit(int id, MainPage parent)
{
par = parent;
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
FNError.Text = "";
LNError.Text = "";
ConError.Text = "";
EmailError.Text = "";
SalaryError.Text = "";
String cmd = "Select Value from Lookup Where Category = 'GENDER'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GenderComboBox.Items.Add(reader.GetString(0));
}
cmd = "Select Value from Lookup Where Id = (SELECT Gender FROM Person where Id = " + id.ToString() + ")";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GenderComboBox.SelectedIndex = GenderComboBox.Items.IndexOf(reader.GetString(0));
}
cmd = "Select Value from Lookup Where Category = 'DESIGNATION'";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
DesComboBox.Items.Add(reader.GetString(0));
}
cmd = "Select Value from Lookup Where Id = (SELECT Gender FROM Person where Id = " + id.ToString() + ")";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
DesComboBox.SelectedIndex = GenderComboBox.Items.IndexOf(reader.GetString(0));
}
cmd = "SELECT Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Designation, Salary FROM Person JOIN Advisor ON Person.Id = Advisor.Id where Person.Id = " + id.ToString();
reader = DatabaseConnection.getInstance().getData(cmd);
stuold = new AdvisorClass();
while (reader.Read())
{
stuold.Id = (int)reader.GetValue(0);
stuold.FirstName = reader.GetString(1);
stuold.LastName = reader.GetString(2);
stuold.Contact = reader.GetString(3);
stuold.Email = reader.GetString(4);
stuold.DateOfBirth = (DateTime)reader.GetValue(5);
stuold.Gender = (int)reader.GetValue(6);
stuold.Designation = (int)reader.GetValue(7);
stuold.Salary = (decimal)reader.GetValue(8);
}
FirstNameTextBox.Text = stuold.FirstName;
LastNameTextBox.Text = stuold.LastName;
ContactTextBox.Text = stuold.Contact;
EmailTextBox.Text = stuold.Email;
SalaryTextBox.Text = stuold.Salary.ToString();
DateOfBirthPick.Value = (DateTime)stuold.DateOfBirth;
}
public ProjectGroup()
{
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
try
{
String cmd = "Select Id, Created_On, AssignmentDate from Group JOIN GroupProject";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.Designation = (int)reader.GetValue(8);
stu.Designations = reader.GetString(9);
stu.Salary = (decimal)reader.GetValue(10);
students.Add(stu);
}
AdvisorDataGrid.DataSource = students;
AdvisorDataGrid.Columns[0].Visible = false;
AdvisorDataGrid.Columns[6].Visible = false;
AdvisorDataGrid.Columns[7].Visible = false;
DataGridViewButtonColumn bcol = new DataGridViewButtonColumn();
bcol.HeaderText = "";
bcol.Text = "Edit";
bcol.Name = "btnEdit";
bcol.UseColumnTextForButtonValue = true;
AdvisorDataGrid.Columns.Add(bcol);
DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn();
bcol1.HeaderText = "";
bcol1.Text = "Delete";
bcol1.Name = "btnDelete";
bcol1.UseColumnTextForButtonValue = true;
AdvisorDataGrid.Columns.Add(bcol1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SearchButton_Click(object sender, EventArgs e)
{
try
{
if (SearchTextBox.Text == "")
{
Refresh();
return;
}
String cmd = "SELECT Person.Id, FirstName, LastName, Email, AssignmentDate, Value AS AdvisorRoles FROM ((Advisor JOIN Person ON Advisor.Id = Person.Id) JOIN ProjectAdvisor ON Advisor.Id = AdvisorId) JOIN Lookup ON AdvisorRole = Lookup.Id WHERE ProjectId = " + id.ToString() + " AND " + SearchCategory.Text + " = '" + SearchTextBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Email = reader.GetString(3);
stu.AssignmentDate = (DateTime)reader.GetValue(4);
stu.AdvisorRole = reader.GetString(5);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
for (int j = 0; j < StudentDataGrid.RowCount; j++)
{
StudentDataGrid.Rows[j].Cells[StudentDataGrid.Columns["Select"].Index].Value = false;
}
StudentDataGrid.Columns["Id"].Visible = false;
StudentDataGrid.Columns["Gender"].Visible = false;
StudentDataGrid.Columns["Designation"].Visible = false;
StudentDataGrid.Columns["Contact"].Visible = false;
StudentDataGrid.Columns["DateOfBirth"].Visible = false;
StudentDataGrid.Columns["Genders"].Visible = false;
StudentDataGrid.Columns["Designations"].Visible = false;
StudentDataGrid.Columns["Salary"].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SearchButton_Click(object sender, EventArgs e)
{
try
{
if (SearchTextBox.Text == "")
{
Refresh();
return;
}
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Genders, Designation , Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN (SELECT Id, Value As Genders FROM Lookup) b ON Person.Gender = b.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id Where " + SearchCategory.Text + " = '" + SearchTextBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.Designation = (int)reader.GetValue(8);
stu.Designations = reader.GetString(9);
stu.Salary = (decimal)reader.GetValue(10);
students.Add(stu);
}
AdvisorDataGrid.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Refresh()
{
DeleteButton.Visible = false;
StudentDataGrid.Columns.Clear();
SearchCategory.Items.Clear();
try
{
String cmd = "SELECT Person.Id, FirstName, LastName, Email, AssignmentDate, Value AS AdvisorRoles FROM ((Advisor JOIN Person ON Advisor.Id = Person.Id) JOIN ProjectAdvisor ON Advisor.Id = AdvisorId) JOIN Lookup ON AdvisorRole = Lookup.Id WHERE ProjectId = " + id.ToString();
var reader = DatabaseConnection.getInstance().getData(cmd);
DataGridViewCheckBoxColumn checkColumn = new DataGridViewCheckBoxColumn();
checkColumn.Name = "Select";
checkColumn.HeaderText = "";
checkColumn.TrueValue = true;
checkColumn.FalseValue = false;
checkColumn.Width = 20;
checkColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
checkColumn.ReadOnly = false; //if the datagridview is resized (on form resize) the checkbox won't take up too much; value is relative to the other columns' fill values
StudentDataGrid.Columns.Add(checkColumn);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Email = reader.GetString(3);
stu.AssignmentDate = (DateTime)reader.GetValue(4);
stu.AdvisorRole = reader.GetString(5);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
for (int j = 0; j < StudentDataGrid.RowCount; j++)
{
StudentDataGrid.Rows[j].Cells[StudentDataGrid.Columns["Select"].Index].Value = false;
}
StudentDataGrid.Columns["Id"].Visible = false;
StudentDataGrid.Columns["Gender"].Visible = false;
StudentDataGrid.Columns["Designation"].Visible = false;
StudentDataGrid.Columns["Contact"].Visible = false;
StudentDataGrid.Columns["DateOfBirth"].Visible = false;
StudentDataGrid.Columns["Genders"].Visible = false;
StudentDataGrid.Columns["Designations"].Visible = false;
StudentDataGrid.Columns["Salary"].Visible = false;
cmd = "SELECT FirstName, LastName, Email, Value AS AdvisorRoles FROM ((Advisor JOIN Person ON Advisor.Id = Person.Id) JOIN ProjectAdvisor ON Advisor.Id = AdvisorId) JOIN Lookup ON AdvisorRole = Lookup.Id";
reader = DatabaseConnection.getInstance().getData(cmd);
int i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategory.Items.Contains(reader.GetName(i)))
{
SearchCategory.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategory.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Add_Click(object sender, EventArgs e)
{
int flag = 0;
try
{
AdvisorClass stu = new AdvisorClass();
stu.Gender = null;
stu.LastName = null;
stu.Contact = null;
stu.DateOfBirth = null;
stu.Salary = null;
if (Regexp("^[a-zA-Z]{1,100}", FirstNameTextBox, FNError, "Only alphabets are allowed.", 100))
{
stu.FirstName = FirstNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(LastNameTextBox.Text)))
{
flag++;
}
else if (Regexp("^[a-zA-Z]{1,100}", LastNameTextBox, LNError, "Only alphabets are allowed.", 100))
{
stu.LastName = LastNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(ContactTextBox.Text)))
{
flag++;
}
else if (Regexp("^[0-9]{1,20}", ContactTextBox, ConError, "Only valid numbers are allowed.", 20))
{
stu.Contact = ContactTextBox.Text;
flag++;
}
if (Regexp("^([\\w\\.\\-]+)@([\\w\\-]+)((\\.(\\w){2,3})+)$", EmailTextBox, EmailError, "Format not correct eg. [email protected]", 30))
{
stu.Email = EmailTextBox.Text;
String cmd1 = "Select Id from Person Where Email = '" + EmailTextBox.Text + "'";
var reader1 = DatabaseConnection.getInstance().getData(cmd1);
while (reader1.Read())
{
flag--;
EmailError.Text = "Already exists";
EmailError.ForeColor = System.Drawing.Color.Red;
}
flag++;
}
stu.DateOfBirth = DateOfBirthPick.Value;
flag++;
if ((string.IsNullOrEmpty(SalaryTextBox.Text)))
{
flag++;
}
else if (Regexp("[0-9]{1,18}", SalaryTextBox, SalaryError, "Only valid numbers are allowed.", 18))
{
stu.Salary = Convert.ToDecimal(SalaryTextBox.Text);
flag++;
}
String cmd = "Select Id from Lookup Where Category = 'GENDER' AND Value = '" + GenderComboBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
stu.Gender = (int)reader.GetValue(0);
}
flag++;
cmd = "Select Id from Lookup Where Category = 'DESIGNATION' AND Value = '" + DesComboBox.Text + "'";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
stu.Designation = (int)reader.GetValue(0);
}
flag++;
if (flag == 8)
{
cmd = String.Format("INSERT INTO Person(FirstName, LastName, Contact, Email, DateOfBirth, Gender) values('{0}','{1}','{2}','{3}','{4}','{5}')", stu.FirstName, stu.LastName, stu.Contact, stu.Email, stu.DateOfBirth, stu.Gender);
DatabaseConnection.getInstance().exectuteQuery(cmd);
cmd = "Select Id from Person Where Email = '" + stu.Email + "'";
reader = DatabaseConnection.getInstance().getData(cmd);
int?id = null;
while (reader.Read())
{
id = (int)reader.GetValue(0);
}
if (id != null)
{
cmd = String.Format("Insert INTO Advisor(Id, Designation, Salary) values('{0}', '{1}', '{2}')", id, stu.Designation, stu.Salary);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
