/**
* Convert Term + Year to TermID. If Term + Year did not exist in databse. Will be create in database.
* @para: Term, Year
* @return: TermID
*/
public bool ConvertTermYearToTermId(int Term, int Year, ref int TermID)
{
/*
* Read TermTable from SQL to local variable for process
* TODO: Need enhanced work directly with database
*/
var m_TermDatabase = new List<TermDatabase>();
cmd.CommandText = @"SELECT * FROM TermTable ORDER BY TermID";
try
{
connection.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
m_TermDatabase.Add(new TermDatabase((int) reader[0],(int) reader[1], (int) reader[2]));
}
}
connection.Close();
}
catch (Exception exception)
{
connection.Close();
MessageBox.Show(@"Message: " + exception.ToString(), @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return false;
throw;
}
/*
* - Check in list and add to database if need.
*/
var TermFind = (m_TermDatabase.FindAll(x => x.Year == Year)).Find(y => y.Term == Term);
if (TermFind != null)
{
TermID = TermFind.TermID;
return true;
}
/*
* Term && Year did not exist in database. Add new Term to database. Begin from last Term in databas
* Example: In database contain Term 2 Year 2015. So new Term is 1 Year 2016. We should add Term from 3 Year 2015 to Term 1 Year 2016
* Add: (3,2015), (4,2015), (5,2015), (6,2015), (1,2016).
* If there is the first Term on database. We should add Term from Term 1 in this Year
* TermID have format: YearTerm. For Example: 20161 is Term 1 in 2016
*/
if (m_TermDatabase.IsEmpty())
{
connection.Open();
for (var i = 1; i <= Term; ++i)
{
cmd.CommandText = string.Format(@"INSERT INTO TermTable VALUES({0}, {1}, {2})", Year*10 + i, i, Year);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
connection.Close();
MessageBox.Show(@"Message: " + exception.ToString(), @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
throw;
}
}
connection.Close();
}
else
{
connection.Open();
var lastYear = Convert.ToInt32(m_TermDatabase.Last().Year);
var firstYear = Convert.ToInt32(m_TermDatabase.First().Year);
var firstTermFirstYear = m_TermDatabase.First().Term;
var lastTermLastYear = m_TermDatabase.Last().Term + 1;
if (lastYear <= Year)
{
for (; lastYear <= Year; ++lastYear)
{
if (lastTermLastYear > 6)
lastTermLastYear = 1;
var tempTerm = lastYear < Year ? 6 : Term;
for (; lastTermLastYear <= tempTerm; ++lastTermLastYear)
{
if (Equals(lastYear, Year) && Equals(lastTermLastYear == Term))
TermID = lastYear * 10 + lastTermLastYear;
cmd.CommandText = string.Format(@"INSERT INTO TermTable VALUES({0}, {1}, {2})", lastYear * 10 + lastTermLastYear, lastTermLastYear,
lastYear);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
connection.Close();
MessageBox.Show(@"Message: " + exception.ToString(), @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
throw;
}
}
}
}
if (firstYear > Year)
{
/*Add from Term 1 of FirstYear to Term 6 of Year - 1*/
for (var i = 1; i <= 6; ++i)
{
cmd.CommandText = string.Format(@"INSERT INTO TermTable VALUES({0}, {1}, {2})", Year * 10 + i, i, Year);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
connection.Close();
MessageBox.Show(@"Message: " + exception.ToString(), @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
throw;
}
}
}
connection.Close();
}
//TermID = (m_TermDatabase.Find(x => (x.Term == Term && x.Year == Year))).TermID;
return true;
}