///
/// <param name="year"></param>
public void ShowSpecialityMaxScore(int year)
{
try
{
string sqlQuery = "SELECT SNAME FROM 'SUBJECT' INNER JOIN 'SPECIALITY' " +
"ON SPECIALID=SPECID WHERE PASSINGSCORE = " +
"(SELECT max(PASSINGSCORE) FROM 'SUBJECT' WHERE SUBJNAME=\"mathematics\" AND YEAR = " + year +
") AND YEAR = " + year + ";";
SQLiteDataReader reader = GetInfo.ExecuteSql(sqlQuery);
if (reader.HasRows)
{
while (reader.Read())
{
labelSpec.Text = reader.GetValue(0).ToString();
}
}
else
{
labelSpec.Text = "No such speciality";
}
}
catch (SQLiteException ex)
{
MessageBox.Show("Oops...Something went wrong:(" + ex.ToString());
}
}
///
/// <param name="year"></param>
/// <param name="form"></param>
public void ShowPlanOfAdmission(int year, FormOfEducation form)
{
try
{
string sqlQuery = "SELECT SNAME,AMOUNT FROM 'PLANOFADMISSION'" +
" INNER JOIN 'SPECIALITY' ON SPECIID=SPECID " +
" WHERE PLAN_YEAR = " + year + " AND FORMID = " + (int)form + ";";
SQLiteDataReader reader = GetInfo.ExecuteSql(sqlQuery);
if (reader.HasRows)
{
while (reader.Read())
{
dataGridPlan.Rows.Add(new object[] {
reader.GetValue(0).ToString(),
reader.GetValue(1).ToString(),
});
}
}
else
{
dataGridPlan.Visible = false;
label2.Text = "Such information is not found!";
}
}
catch (SQLiteException ex)
{
MessageBox.Show("Oops...Something went wrong:(" + ex.ToString());
}
}
///
/// <param name="phone">Phone number of university</param>
/// <param name="site">Website of university</param>
/// <param name="address">Address of university</param>
public static bool AddContactInfo(string phone, string site, string address, Speciality spec){
try
{
string sql = String.Format("INSERT INTO 'CONTACTINFO' (ADDRESS, PHONENUMBER, WEBSITE, SPECID) VALUES ('{0}', '{1}', '{2}', {3});",
address, phone, site, spec.Code);
if (!GetInfo.ExecuteReadSql(sql))
return false;
}
catch (SQLiteException ex)
{
return false;
}
return true;
}
///
/// <param name="item">Head of the university</param>
/// <param name="dep">Department of university</param>
/// <param name="fac">Faculty of university</param>
/// <param name="spec">Speciality of university</param>
public static bool AddHead(Head item, Department dep, Faculty fac, Speciality spec, int year)
{
try
{
string sql = String.Format("INSERT INTO 'LEADER' (NAME, SCDEGREE, SCRANK, STARTDATE,SID,DPID,FCID) VALUES ('{0}', '{1}', '{2}', {3}, {4}, {5},{6});",
item.Name, item.ScDegree, item.ScRank, year, spec.Code, dep.Code, fac.Code);
if (!GetInfo.ExecuteReadSql(sql))
return false;
}
catch (SQLiteException ex)
{
return false;
}
return true;
}
///
/// <param name="dep">Department of university</param>
/// <param name="fac">Faculty of university</param>
/// <param name="spec">Speciality of university</param>
/// <param name="form">Form of education</param>
public static bool AddEducationalUnit(Department dep, Faculty fac, Speciality spec, FormOfEducation form){
try
{
string sql = String.Format("INSERT INTO 'DEPARTMENT' (DEPCODE, DEPNAME, DEPSHORTNAME) VALUES ({0}, '{1}', '{2}');",
dep.Code, dep.Name, dep.ShortName);
if (!GetInfo.ExecuteReadSql(sql))
return false;
sql = String.Format("INSERT INTO 'FACULTY' (FACCODE, FACNAME, FACSHORTNAME, DEPRID) VALUES ({0}, '{1}', '{2}', {3});",
fac.Code, fac.Name, fac.ShortName, dep.Code);
if (!GetInfo.ExecuteReadSql(sql))
return false;
sql = String.Format("INSERT INTO 'SPECIALITY' (SCODE, SNAME, SSHORTNAME, FAID, FORMID, DEPARID) VALUES ({0}, '{1}', '{2}', {3}, {4}, {5});",
spec.Code, spec.Name, spec.ShortName, fac.Code, (int)form, dep.Code);
if (!GetInfo.ExecuteReadSql(sql))
return false;
}
catch(SQLiteException ex)
{
return false;
}
return true;
}
///
/// <param name="year"></param>
public void ShowListOfLeaders(int year)
{
try
{
string sqlQuery = "SELECT NAME,SCDEGREE,SCRANK,STARTDATE,SNAME,FACNAME,DEPNAME FROM 'LEADER'" +
" INNER JOIN 'SPECIALITY' ON SID=SPECID INNER JOIN 'FACULTY' ON FAID=FACID " +
"INNER JOIN 'DEPARTMENT' ON DEPRID=DEPID" +
" WHERE STARTDATE <= " + year + " AND SCDEGREE = \"doctor\" AND SCRANK=\"professor\";";
SQLiteDataReader reader = GetInfo.ExecuteSql(sqlQuery);
if (reader.HasRows)
{
while (reader.Read())
{
dataGridLeaders.Rows.Add(new object[] {
reader.GetValue(0).ToString(),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
reader.GetValue(4).ToString(),
reader.GetValue(5).ToString(),
reader.GetValue(6).ToString()
});
}
}
else
{
dataGridLeaders.Visible = false;
label2.Text = "Such information is not found!";
}
}
catch (SQLiteException ex)
{
MessageBox.Show("Oops...Something went wrong:(" + ex.ToString());
}
}
///
/// <param name="amount">Amount of people in plan</param>
/// <param name="name">Name of speciality</param>
public static bool UpdatePlanOfAdmission(string name, int amount){
string sql = String.Format("UPDATE 'PLANOFADMISSION' SET AMOUNT= {0} WHERE SPECIID= " +
"(SELECT SPECID FROM 'SPECIALITY' WHERE SNAME = \"{1}\") ;", amount, name);
return (GetInfo.ExecuteReadSql(sql));
}
///
/// <param name="score">Passing score on subject</param>
/// <param name="name">Name of subject</param>
public static bool UpdateMaxScore(string name, int score){
string sql = String.Format("UPDATE 'SUBJECT' SET PASSINGSCORE= {0} WHERE SUBJNAME= \"{1}\";", score, name);
return (GetInfo.ExecuteReadSql(sql));
}
///
/// <param name="name">Name of leader</param>
/// <param name="year">Start date of working</param>
public static bool UpdateHistory(string name, int year){
string sql = String.Format("UPDATE 'LEADER' SET STARTDATE= {0} WHERE NAME= \"{1}\";", year, name);
return (GetInfo.ExecuteReadSql(sql));
}
private void ButtonAdd_Click(object sender, EventArgs e)
{
Department dep = null;
Faculty fac = null;
Speciality spec = null;
Head head = null;
int year = 0;
string address = "";
string phone = "";
string site = "";
FormOfEducation edForm = FormOfEducation.intramuralForm;
if (textBoxDep.Text.Length != 0)
{
dep = new Department(GetInfo.DepMaxCode() + 1, textBoxDep.Text);
}
if (textBoxFac.Text.Length != 0)
{
fac = new Faculty(GetInfo.FacMaxCode() + 1, textBoxFac.Text);
}
if (textBoxSpec.Text.Length != 0)
{
spec = new Speciality(GetInfo.SpecMaxCode() + 1, textBoxFac.Text);
}
if (textBoxYear.Text.Length != 0 && CheckingInfo.CheckCorectness(textBoxYear.Text))
{
year = Int32.Parse(textBoxYear.Text);
}
if (textBoxName.Text.Length != 0 && textBoxRank.Text.Length != 0 && textBoxDegree.Text.Length != 0)
{
head = new Head(year, textBoxName.Text, textBoxDegree.Text, textBoxRank.Text);
}
if (textBoxAddress.Text.Length != 0)
{
address = textBoxAddress.Text;
}
if (textBoxPhone.Text.Length != 0)
{
phone = textBoxPhone.Text;
}
if (textBoxSite.Text.Length != 0)
{
site = textBoxSite.Text;
}
if (RBExtramural.Checked)
{
edForm = FormOfEducation.extramuralForm;
}
if (RBPart.Checked)
{
edForm = FormOfEducation.partTimeForm;
}
if (AddingInformation.AddInfo(dep, spec, fac, head, address, phone, site, year, edForm))
{
MessageBox.Show("Successfully added!");
}
else
{
MessageBox.Show("Error! Try again");
}
}
