//private Int64 createTISPLong(Int64 CaseISPID)
//{
// Int64 returnValue = 0;
// DataBase Base = new DataBase();
// SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
// using (Sqlconn)
// {
// try
// {
// Sqlconn.Open();
// string sql = "INSERT INTO TeacherISPLong(Domain, CaseISPID, TargetContentLong) VALUES (@Domain, @CaseISPID, @TargetContentLong)";
// SqlCommand cmd = new SqlCommand(sql, Sqlconn);
// cmd.Parameters.Add("@Domain", SqlDbType.TinyInt).Value = "";
// cmd.Parameters.Add("@CaseISPID", SqlDbType.BigInt).Value = "";
// cmd.Parameters.Add("@TargetContentLong", SqlDbType.NVarChar).Value = "";
// returnValue = cmd.ExecuteNonQuery();
// if (returnValue != 0)
// {
// sql = "select IDENT_CURRENT('TeacherISPLong') AS ISPLong";
// cmd = new SqlCommand(sql, Sqlconn);
// SqlDataReader dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// returnValue = Int64.Parse(dr["ISPLong"].ToString());
// }
// dr.Close();
// }
// Sqlconn.Close();
// }
// catch (Exception e)
// {
// string error = e.Message;
// returnValue = -1;
// }
// }
// return returnValue;
//}
//private int createTISPShort(Int64 LID)
//{
// int returnValue = 0;
// DataBase Base = new DataBase();
// SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
// using (Sqlconn)
// {
// try
// {
// Sqlconn.Open();
// string sql = "INSERT INTO TeacherISPShort(TISPLID, TargetContentShort, DateStart, DateEnd, EffectiveDate, EffectiveMode, EffectiveResult, Decide) " +
// "VALUES (@TISPLID,@TargetContentShort,@DateStart,@DateEnd,@EffectiveDate,@EffectiveMode,@EffectiveResult,@Decide)";
// SqlCommand cmd = new SqlCommand(sql, Sqlconn);
// cmd.Parameters.Add("@TISPLID", SqlDbType.BigInt).Value = LID;
// cmd.Parameters.Add("@TargetContentShort", SqlDbType.Date).Value = "";
// cmd.Parameters.Add("@DateStart", SqlDbType.Date).Value = "";
// cmd.Parameters.Add("@DateEnd", SqlDbType.Date).Value = "";
// cmd.Parameters.Add("@EffectiveDate", SqlDbType.Date).Value = "";
// cmd.Parameters.Add("@EffectiveMode", SqlDbType.Int).Value = "";
// cmd.Parameters.Add("@EffectiveResult", SqlDbType.Int).Value = "";
// cmd.Parameters.Add("@Decide", SqlDbType.Int).Value = "";
// returnValue = cmd.ExecuteNonQuery();
// Sqlconn.Close();
// }
// catch (Exception e)
// {
// string error = e.Message;
// returnValue = -1;
// }
// }
// return returnValue;
//}
public int SearchTeachISPCount(SearchStudentISP searchISPData)
{
int returnValue = 0;
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 COUNT(*) AS QCOUNT 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";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
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;
returnValue = (int)cmd.ExecuteScalar();
Sqlconn.Close();
}
catch (Exception e)
{
string item = e.Message.ToString();
returnValue=-1;
}
}
return returnValue;
}
public int SearchTeachISPDateCount(SearchStudentISP searchData)
{
TeachDataBase sDB = new TeachDataBase();
return sDB.SearchTeachISPCount(searchData);
}
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> SearchTeachISPData(int index, SearchStudentISP searchData)
{
TeachDataBase sDB = new TeachDataBase();
return sDB.SearchTeachISP(index, searchData);
}