public void FillChartList()
{
conn = DBconnection.GetConnection();
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
try
{
SqlCommand classListCmd = new SqlCommand("SELECT * FROM [sms].[dbo].[class]", conn);
SqlDataReader cldr = classListCmd.ExecuteReader();
{
if (cldr.HasRows)
{
while (cldr.Read())
{
for (int term = 1; term <= 3; term++)
{
int IndexClassID = cldr.GetOrdinal("class_id");
string classID = cldr.GetString(IndexClassID);
SqlCommand ResultCmd = new SqlCommand("SELECT * FROM [sms].[dbo].[result] as re INNER JOIN [sms].[dbo].[class] as cls ON re.class_id = cls.class_id WHERE re.class_id='" + classID + "' AND re.exam_id=" + term, conn);
SqlDataReader rdr = ResultCmd.ExecuteReader();
if (rdr.HasRows)
{
int TotalPercentage = 0;
while (rdr.Read())
{
CalculateProgress cp = new CalculateProgress();
var digit = findDigit(classID);
string v = digit.ToString();
string c = "Nothing";
if (v == "01" || v == "02" || v == "03" || v == "04" || v == "05" || v == "06" || v == "07" || v == "08" || v == "09")
{
c = v.Replace("0", " ");
}
else if (v == "10")
{
c = v;
}
int intDigit = Convert.ToInt32(c);
SqlCommand noOfSubject = new SqlCommand("SELECT count(subject_id) FROM [sms].[dbo].[subject] WHERE class_id=" + digit, conn);
noOfSubject.CommandType = CommandType.Text;
int countSubject = (Int32)noOfSubject.ExecuteScalar();
int[] receiveProgressValue = new int[countSubject];
int IndexTotalStudent = rdr.GetOrdinal("total_student");
int totalStd = rdr.GetInt32(IndexTotalStudent);
for (int i = 1, j = 0; i <= countSubject; i++, j++)
{
SqlCommand noOfFirstStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa = 5", conn);
noOfFirstStd.CommandType = CommandType.Text;
int firstStd = (Int32)noOfFirstStd.ExecuteScalar();
SqlCommand noOfSecondStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa = 4", conn);
noOfSecondStd.CommandType = CommandType.Text;
int secondStd = (Int32)noOfSecondStd.ExecuteScalar();
SqlCommand noThirdStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa < 4 AND gpa >= 3", conn);
noThirdStd.CommandType = CommandType.Text;
int thirdStd = (Int32)noThirdStd.ExecuteScalar();
SqlCommand noOtherStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa <= 2", conn);
noOtherStd.CommandType = CommandType.Text;
int otherStd = (Int32)noOtherStd.ExecuteScalar();
var subjectValue = cp.SingleSubjectCalculation(totalStd, firstStd, secondStd, thirdStd, otherStd);
receiveProgressValue[j] = subjectValue;
}
int Tvalue = 0;
for (int k = 0; k < receiveProgressValue.Length; k++)
{
Tvalue = Tvalue + receiveProgressValue[k];
}
Array.Clear(receiveProgressValue, 0, receiveProgressValue.Length);
TotalPercentage = cp.AllSubjectCalculation(countSubject, Tvalue);
//var Class = TeachSelectCmbox.SelectedValue;
//var Term = term;
//var Progress = TotalPercentage;
//SqlCommand insertValueCmd = new SqlCommand("INSERT INTO [sms].[dbo].[progress](class, term, progress) " +
// "VALUES (@class, @term, @progress)", conn);
//insertValueCmd.Parameters.AddWithValue("@class", Class);
//insertValueCmd.Parameters.AddWithValue("@term", Term);
//insertValueCmd.Parameters.AddWithValue("@progress", Progress);
//insertValueCmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (NullReferenceException)
{ }
}
public void FillChartList()
{
conn = DBconnection.GetConnection();
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
try
{
SqlCommand classListCmd = new SqlCommand("SELECT * FROM [sms].[dbo].[class]", conn);
SqlDataReader cldr = classListCmd.ExecuteReader();
{
if (cldr.HasRows)
{
while (cldr.Read())
{
for (int term = 1; term <= 3; term++)
{
int IndexClassID = cldr.GetOrdinal("class_id");
string classID = cldr.GetString(IndexClassID);
SqlCommand ResultCmd = new SqlCommand("SELECT * FROM [sms].[dbo].[result] as re INNER JOIN [sms].[dbo].[class] as cls ON re.class_id = cls.class_id WHERE re.class_id='" + classID + "' AND re.exam_id=" + term, conn);
SqlDataReader rdr = ResultCmd.ExecuteReader();
if (rdr.HasRows)
{
int TotalPercentage = 0;
while (rdr.Read())
{
CalculateProgress cp = new CalculateProgress();
var digit = findDigit(classID);
string v = digit.ToString();
string c = "Nothing";
if (v == "01" || v == "02" || v == "03" || v == "04" || v == "05" || v == "06" || v == "07" || v == "08" || v == "09")
{
c = v.Replace("0", " ");
}
else if (v == "10")
{
c = v;
}
int intDigit = Convert.ToInt32(c);
SqlCommand noOfSubject = new SqlCommand("SELECT count(subject_id) FROM [sms].[dbo].[subject] WHERE class_id=" + digit, conn);
noOfSubject.CommandType = CommandType.Text;
int countSubject = (Int32)noOfSubject.ExecuteScalar();
int[] receiveProgressValue = new int[countSubject];
int IndexTotalStudent = rdr.GetOrdinal("total_student");
int totalStd = rdr.GetInt32(IndexTotalStudent);
for (int i = 1, j = 0; i <= countSubject; i++, j++)
{
SqlCommand noOfFirstStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa = 5", conn);
noOfFirstStd.CommandType = CommandType.Text;
int firstStd = (Int32)noOfFirstStd.ExecuteScalar();
SqlCommand noOfSecondStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa = 4", conn);
noOfSecondStd.CommandType = CommandType.Text;
int secondStd = (Int32)noOfSecondStd.ExecuteScalar();
SqlCommand noThirdStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa < 4 AND gpa >= 3", conn);
noThirdStd.CommandType = CommandType.Text;
int thirdStd = (Int32)noThirdStd.ExecuteScalar();
SqlCommand noOtherStd = new SqlCommand("SELECT count(*) FROM [sms].[dbo].[result] WHERE class_id='" + classID + "' AND subject_id='c" + intDigit + "s" + i + "' AND exam_id=" + term + " AND gpa <= 2", conn);
noOtherStd.CommandType = CommandType.Text;
int otherStd = (Int32)noOtherStd.ExecuteScalar();
var subjectValue = cp.SingleSubjectCalculation(totalStd, firstStd, secondStd, thirdStd, otherStd);
receiveProgressValue[j] = subjectValue;
}
int Tvalue = 0;
for (int k = 0; k < receiveProgressValue.Length; k++)
{
Tvalue = Tvalue + receiveProgressValue[k];
}
Array.Clear(receiveProgressValue, 0, receiveProgressValue.Length);
TotalPercentage = cp.AllSubjectCalculation(countSubject, Tvalue);
//var Class = TeachSelectCmbox.SelectedValue;
//var Term = term;
//var Progress = TotalPercentage;
//SqlCommand insertValueCmd = new SqlCommand("INSERT INTO [sms].[dbo].[progress](class, term, progress) " +
// "VALUES (@class, @term, @progress)", conn);
//insertValueCmd.Parameters.AddWithValue("@class", Class);
//insertValueCmd.Parameters.AddWithValue("@term", Term);
//insertValueCmd.Parameters.AddWithValue("@progress", Progress);
//insertValueCmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (NullReferenceException)
{ }
}
