public string[] createStudentTemperatureData(CreateTemperatureSystem temperatureDataSystem)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DateTime now = DateTime.Now;
//deleteStudentTemperatureData(temperatureDataSystem.txtpeopleID, now.Year.ToString(), now.Month.ToString(), now.Day.ToString());
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO StudentTemperature (StudentID, StudentTemperature, ParentTemperature, Year,Month,Day ,LeaveItem,LeaveState," +
"CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " +
"(@StudentID, @StudentTemperature, @ParentTemperature, @Year,@Month,@Day ,@LeaveItem,@LeaveState, " +
"@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.txtpeopleID);
cmd.Parameters.Add("@StudentTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.peopleTemp);
cmd.Parameters.Add("@ParentTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.parentsTemp);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@LeaveItem", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.leaveItem);
cmd.Parameters.Add("@LeaveState", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.leaveStatus);
if (string.IsNullOrEmpty(temperatureDataSystem.Year))
{ cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Year.ToString()); }
else
{ cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Year); }
if (string.IsNullOrEmpty(temperatureDataSystem.Month))
{ cmd.Parameters.Add("@Month", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Month.ToString()); }
else
{ cmd.Parameters.Add("@Month", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Month); }
if (string.IsNullOrEmpty(temperatureDataSystem.Day))
{ cmd.Parameters.Add("@Day", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Day.ToString()); }
else
{ cmd.Parameters.Add("@Day", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Day); }
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] createTeacherTemperatureData(CreateTemperatureSystem temperatureDataSystem)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO TeacherTemperature (TeacherID, TeacherTemperature, " +
"CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " +
"(@TeacherID, @TeacherTemperature, " +
"@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@TeacherID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.txtpeopleID);
cmd.Parameters.Add("@TeacherTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.peopleTemp);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<CreateTemperatureSystem> getStudentTemperatureData(string studentID, string Year, string Month)
{
List<CreateTemperatureSystem> returnValue = new List<CreateTemperatureSystem>();
CreateTemperatureSystem addValue = new CreateTemperatureSystem();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select a.*,b.StudentName from StudentTemperature a left join StudentDatabase b on a.StudentID=b.StudentID where( a.StudentID=@StudentID and " +
"Year=@Year and Month=@Month)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = studentID;
cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = int.Parse(Year) + 1911;
cmd.Parameters.Add("@Month", SqlDbType.NVarChar).Value = Month;
/*
string DateStart = Month + "/1/" + (int.Parse(Year) + 1911);
string DateEnd = "";
cmd.Parameters.Add("@CreateFileDatestart", SqlDbType.DateTime).Value = DateStart;
if (Month=="12")
DateEnd = "1/1/" + (int.Parse(Year) + 1 + 1911);
else
DateEnd = (int.Parse(Month) + 1) + "/1/" + (int.Parse(Year) + 1911);
cmd.Parameters.Add("@CreateFileDateend", SqlDbType.DateTime).Value =DateEnd;
*/
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
addValue.tID = dr["ID"].ToString();
addValue.txtpeopleID = dr["StudentID"].ToString();
addValue.StudentName = dr["StudentName"].ToString();
addValue.peopleTemp = dr["StudentTemperature"].ToString();
addValue.parentsTemp = dr["ParentTemperature"].ToString();
addValue.Year = dr["Year"].ToString();
addValue.Month = dr["Month"].ToString();
addValue.Day = dr["Day"].ToString();
addValue.leaveItem = dr["LeaveItem"].ToString();
addValue.leaveStatus = dr["LeaveState"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
//Temperature addValue = new Temperature();
//addValue.checkNo = "-1";
//addValue.errorMsg = e.Message.ToString();
//returnValue.Add(addValue);
}
}
if (returnValue.Count == 0)
{
using (SqlConnection Sqlconn2 = new SqlConnection(Base.GetConnString()))
{
Sqlconn2.Open();
string sql = "select StudentName from StudentDatabase where StudentID=@StudentID";
SqlCommand cmd2 = new SqlCommand(sql, Sqlconn2);
cmd2.Parameters.Add("@StudentID", SqlDbType.Int).Value = studentID;
SqlDataReader dr2 = cmd2.ExecuteReader();
string StudentName = "";
while (dr2.Read())
{
StudentName = dr2["StudentName"].ToString();
}
addValue.StudentName = StudentName;
addValue.txtpeopleID = studentID;
returnValue.Add(addValue);
dr2.Close();
}
}
return returnValue;
}
