//顯示學生姓名(有點多餘)
public List<SearchStudentResult> ShowStudent(int SID)
{
List<SearchStudentResult> returnvalue = new List<SearchStudentResult>();
SearchStudentResult temp = new SearchStudentResult();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select StudentName from StudentDatabase where ID=@StudentID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
//temp.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
temp.txtstudentName = dr["StudentName"].ToString();
//temp.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
returnvalue.Add(temp);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
}
}
return returnvalue;
}
public List<SearchStudentResult> SearchStudent(int indexpage, SearchStudent SearchStructure,int type)
{
List<SearchStudentResult> returnValue = new List<SearchStudentResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentConditionReturn(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentDatabase.StudentID DESC) " +
"AS RowNum, StudentDatabase.* " +
"FROM StudentDatabase WHERE isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@sGuaranteeDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindaystart);
cmd.Parameters.Add("@sGuaranteeDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindayend);
cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
cmd.Parameters.Add("@CompletedType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtendReasonType);
cmd.Parameters.Add("@NomembershipType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtnomembershipType);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.ID = int.Parse(dr["ID"].ToString());
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtstudentStatu = dr["CaseStatu"].ToString();
addValue.txtLegalRepresentativeRelation = dr["ContactRelation2"].ToString();
addValue.txtLegalRepresentative = dr["ContactName2"].ToString();
addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString();
addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString();
addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchStudentResult> SearchTeachISP(int indexpage, SearchStudentISP searchISPData)
{
List<SearchStudentResult> returnValue = new List<SearchStudentResult>();
string DateBase = "1900-01-01";
DataBase Base = new DataBase();
int Count = 0;
string LimitsID = "";
if (searchISPData.txtstudentID != null)
{
LimitsID = " AND StudentDatabase.StudentID=(@StudentID) ";
}
string LimitsName = "";
if (searchISPData.txtstudentName != null)
{
LimitsName = " AND StudentDatabase.StudentName like (@StudentName) ";
}
string LimitsSex = "";
int sSex = 0;
if (searchISPData.txtstudentSex != null && searchISPData.txtstudentSex != "0")
{
LimitsSex = " AND StudentDatabase.StudentSex=(@StudentSex) ";
sSex = int.Parse(searchISPData.txtstudentSex);
}
string LimitsBirthday = "";
DateTime sBirthdaystart = new DateTime(1900, 01, 01);
DateTime sBirthdayend = new DateTime(1900, 01, 01);
if (searchISPData.txtbirthdaystart != null && searchISPData.txtbirthdayend != null && searchISPData.txtbirthdaystart != DateBase && searchISPData.txtbirthdayend != DateBase)
{
LimitsBirthday = " AND StudentDatabase.StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
sBirthdaystart = DateTime.Parse(searchISPData.txtbirthdaystart);
sBirthdayend = DateTime.Parse(searchISPData.txtbirthdayend);
}
string LimitsTeachName = "";
if (searchISPData.txtteachername != null)
{
LimitsTeachName = " AND CaseStatu like (@CaseStatu) ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CaseISPstate.ID DESC) " +
"AS RowNum, CaseISPstate.*, StudentDatabase.StudentName, StudentDatabase.StudentSex, StudentDatabase.StudentBirthday, StudentDatabase.ContactName2, StudentDatabase.ContactPhone2, StudentDatabase.ContactTel_home2 " +
"FROM CaseISPstate INNER JOIN StudentDatabase ON CaseISPstate.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
LimitsID + LimitsName + LimitsSex + LimitsBirthday + LimitsTeachName + " WHERE CaseISPstate.isDeleted=0 AND StudentDatabase.isDeleted=0 ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchISPData.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = searchISPData.txtstudentName + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchISPData.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = sBirthdaystart;
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = sBirthdayend;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.ID = Int64.Parse(dr["ID"].ToString());
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtLegalRepresentative = dr["ContactName2"].ToString();
addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString();
addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString();
addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
string item = e.Message.ToString();
//returnValue = -1;
}
}
return returnValue;
}
public List<SearchStudentResult> getAllStudentDataList(int item)
{
List<SearchStudentResult> returnValue = new List<SearchStudentResult>();
DataBase Base = new DataBase();
string ConditionReturn = "";
if (item != 0)
{
ConditionReturn = " AND CaseStatu=(@CaseStatu) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM StudentDatabase WHERE isDeleted=0 AND Unit=@Unit" + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = item;
cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[2].ToString());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.ID = int.Parse(dr["ID"].ToString());
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtstudentStatu = dr["CaseStatu"].ToString();
/*addValue.txtLegalRepresentative = dr["ContactName2"].ToString();
addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString();
addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString();
addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());*/
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
