public string[] createTeacherTemperatureData(CreateTeacherSystem 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 TeacherTemperature ([TeacherID],[TeacherTemperature],[CheckContent],[CreateFileBy],[CreateFileDate],[UpFileBy],[UpFileDate] " +
" ) VALUES " +
"(@TeacherID, @TeacherTemperature, @CheckContent, @CreateFileBy, @CreateFileDate , @UpFileBy, (getDate()))";
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.TeacherTemp);
cmd.Parameters.Add("@CheckContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.CheckContent);
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("@CreateFileDate", SqlDbType.Date).Value = (string.IsNullOrEmpty(temperatureDataSystem.Year) ? Chk.CheckStringFunction(now.Year.ToString()) : Chk.CheckStringFunction(temperatureDataSystem.Year)) + "/" + (string.IsNullOrEmpty(temperatureDataSystem.Month) ? Chk.CheckStringFunction(now.Month.ToString()) : Chk.CheckStringFunction(temperatureDataSystem.Month)) + "/" + (string.IsNullOrEmpty(temperatureDataSystem.Day) ? Chk.CheckStringFunction(now.Day.ToString()) : 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 List<CreateTeacherSystem> getTeacherTemperatureData(string StaffID, string Year, string Month)
{
List<CreateTeacherSystem> returnValue = new List<CreateTeacherSystem>();
CreateTeacherSystem addValue = new CreateTeacherSystem();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select a.*,DAY(a.CreateFileDate) as Day ,MONTH(a.CreateFileDate) as Month,YEAR(a.CreateFileDate) as Year ,b.staffname from teacherTemperature a left join StaffDatabase b on a.TeacherID=b.StaffID " +
// " where( 1=1 and " +
" where( a.teacherid = @StaffID and " +
" YEAR(a.CreateFileDate)=@Year and MONTH(a.CreateFileDate)=@Month )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = StaffID;
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["TeacherID"].ToString();
addValue.TeacherName = dr["staffname"].ToString();
addValue.TeacherTemp = dr["TeacherTemperature"].ToString();
addValue.CheckContent = dr["CheckContent"].ToString();
addValue.Year = dr["Year"].ToString();
addValue.Day = dr["Day"].ToString();
addValue.Month = dr["Month"].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 StaffName from StaffDatabase where StaffID=@StaffID";
SqlCommand cmd2 = new SqlCommand(sql, Sqlconn2);
cmd2.Parameters.Add("@StaffID", SqlDbType.Int).Value = StaffID;
SqlDataReader dr2 = cmd2.ExecuteReader();
string StaffName = "";
while (dr2.Read())
{
StaffName = dr2["StaffName"].ToString();
}
addValue.TeacherName = StaffName;
addValue.txtpeopleID = StaffID;
returnValue.Add(addValue);
dr2.Close();
}
}
return returnValue;
}