private string SearchStudentVisitRecordConditionReturn(SearchVisitRecord SearchStructure)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentID != null)
{
ConditionReturn += " AND CaseVisitRecord.StudentID=(@StudentID) ";
}
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentDatabase.StudentName like (@StudentName) ";
}
if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0")
{
ConditionReturn += " AND StudentDatabase.StudentSex=(@StudentSex) ";
}
if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase)
{
ConditionReturn += " AND StudentDatabase.StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
}
if (SearchStructure.txtvisitType != null && SearchStructure.txtvisitType != "0")
{
ConditionReturn += " AND CaseVisitRecord.VisitType =(@VisitType) ";
}
if (SearchStructure.txtvisitDatestart != null && SearchStructure.txtvisitDateend != null && SearchStructure.txtvisitDatestart != DateBase && SearchStructure.txtvisitDateend != DateBase)
{
ConditionReturn += " AND CaseVisitRecord.VisitDateTime BETWEEN (@sVisitDateStart) AND (@sVisitDateEnd) ";
}
if (SearchStructure.txtvisitSocial != null )
{
ConditionReturn += " AND StaffDatabase.StaffName like @StaffName ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND CaseVisitRecord.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public string[] SearchStudentVisitRecordCount(SearchVisitRecord SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentVisitRecordConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM CaseVisitRecord INNER JOIN StudentDatabase ON CaseVisitRecord.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"INNER JOIN StaffDatabase ON CaseVisitRecord.CreateFileBy=StaffDatabase.StaffID " +
"WHERE CaseVisitRecord.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = SearchStructure.txtstudentName + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
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("@sVisitDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtvisitDatestart);
cmd.Parameters.Add("@sVisitDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtvisitDateend);
cmd.Parameters.Add("@VisitType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvisitType);
cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = SearchStructure.txtvisitSocial + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchStudentVisitDataBaseCount(SearchVisitRecord SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchStudentVisitRecordCount(SearchStructure);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchVisitRecordResult> SearchStudentVisitRecord(int indexpage, SearchVisitRecord SearchStructure)
{
List<SearchVisitRecordResult> returnValue = new List<SearchVisitRecordResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentVisitRecordConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CaseVisitRecord.ID DESC) " +
"AS RowNum, StudentDatabase.StudentName, CaseVisitRecord.* , StaffDatabase.StaffName " +
"FROM CaseVisitRecord INNER JOIN StudentDatabase ON CaseVisitRecord.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0" +
"INNER JOIN StaffDatabase ON CaseVisitRecord.CreateFileBy=StaffDatabase.StaffID " +
"WHERE CaseVisitRecord.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 = SearchStructure.txtstudentName + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
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("@sVisitDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtvisitDatestart);
cmd.Parameters.Add("@sVisitDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtvisitDateend);
cmd.Parameters.Add("@VisitType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvisitType);
cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = SearchStructure.txtvisitSocial + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchVisitRecordResult addValue = new SearchVisitRecordResult();
addValue.ID = dr["ID"].ToString();
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtvisitType = dr["VisitType"].ToString();
addValue.txtvisitDate = DateTime.Parse(dr["VisitDateTime"].ToString()).ToString("yyyy-MM-dd");
addValue.txtvisitSocial = dr["StaffName"].ToString();
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchVisitRecordResult addValue = new SearchVisitRecordResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchVisitRecordResult> SearchStudentVisitDataBase(int index, SearchVisitRecord SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchStudentVisitRecord(index, SearchStructure);
}
