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; }