private string SearchAudiometryAppointmentConditionReturn(SearchAudiometryAppointment SearchStructure)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentID != null)
{
ConditionReturn += " AND s1.StudentID = (@txtstudentID) ";
}
if (SearchStructure.txtstudentSex != null)
{
ConditionReturn += " AND s1.StudentSex = (@txtstudentSex) ";
}
if (SearchStructure.txtstudentDateStart != null && SearchStructure.txtstudentDateEnd != null && SearchStructure.txtstudentDateStart != DateBase && SearchStructure.txtstudentDateEnd != DateBase)
{
ConditionReturn += " AND s1.StudentBirthday BETWEEN (@txtstudentDateStart) AND (@txtstudentDateEnd) ";
}
if (SearchStructure.txtcheckDateStart != null && SearchStructure.txtcheckDateEnd != null && SearchStructure.txtcheckDateStart != DateBase && SearchStructure.txtcheckDateEnd != DateBase)
{
ConditionReturn += " AND AudiometryAppointment.ReserveDate BETWEEN (@txtcheckDateStart) AND (@txtcheckDateEnd) ";
}
return ConditionReturn;
}
public string[] SearchAudiometryAppointmentDataBaseCount(SearchAudiometryAppointment SearchAudiometryAppointmentData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchAudiometryAppointmentConditionReturn(SearchAudiometryAppointmentData);
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 = "SELECT COUNT(*) FROM AudiometryAppointment " +
"RIGHT JOIN StudentDatabase AS s1 ON AudiometryAppointment.StudentID=s1.StudentID AND s1.isDeleted=0 " +
"RIGHT JOIN StaffDatabase AS s2 ON AudiometryAppointment.ReserveID=s2.StaffID AND s2.isDeleted=0 " +
"WHERE AudiometryAppointment.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtstudentName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchAudiometryAppointmentData.txtstudentName) + "%";
cmd.Parameters.Add("@txtstudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchAudiometryAppointmentData.txtstudentID);
cmd.Parameters.Add("@txtstudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchAudiometryAppointmentData.txtstudentSex);
cmd.Parameters.Add("@txtstudentDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtstudentDateStart);
cmd.Parameters.Add("@txtstudentDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtstudentDateEnd);
cmd.Parameters.Add("@txtcheckDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtcheckDateStart);
cmd.Parameters.Add("@txtcheckDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtcheckDateEnd);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchAudiometryAppointmentDataBaseCount(SearchAudiometryAppointment SearchAudiometryAppointmentData)
{
Audiometry aDB = new Audiometry();
if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
{
return aDB.SearchAudiometryAppointmentDataBaseCount(SearchAudiometryAppointmentData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<AADataList> SearchAudiometryAppointmentDataBase(int indexpage, SearchAudiometryAppointment SearchAudiometryAppointmentData)
{
List<AADataList> returnValue = new List<AADataList>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchAudiometryAppointmentConditionReturn(SearchAudiometryAppointmentData);
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 = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY AudiometryAppointment.ID DESC) " +
"AS RowNum, AudiometryAppointment.*,s1.StudentName,s1.CaseStatu,s2.StaffName AS ReserveName,"+
"s3.StaffName AS AssessName1,s4.StaffName AS AssessName2 FROM AudiometryAppointment " +
"RIGHT JOIN StudentDatabase AS s1 ON AudiometryAppointment.StudentID=s1.StudentID AND s1.isDeleted=0 " +
"RIGHT JOIN StaffDatabase AS s2 ON AudiometryAppointment.ReserveID=s2.StaffID AND s2.isDeleted=0 " +
"LEFT JOIN StaffDatabase AS s3 ON AudiometryAppointment.AssessWho1=s3.StaffID AND s3.isDeleted=0 " +
"LEFT JOIN StaffDatabase AS s4 ON AudiometryAppointment.AssessWho2=s4.StaffID AND s4.isDeleted=0 " +
"WHERE AudiometryAppointment.isDeleted=0 AND AudiometryAppointment.State=1 " + 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("@txtstudentName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchAudiometryAppointmentData.txtstudentName) + "%";
cmd.Parameters.Add("@txtstudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchAudiometryAppointmentData.txtstudentID);
cmd.Parameters.Add("@txtstudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchAudiometryAppointmentData.txtstudentSex);
cmd.Parameters.Add("@txtstudentDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtstudentDateStart);
cmd.Parameters.Add("@txtstudentDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtstudentDateEnd);
cmd.Parameters.Add("@txtcheckDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtcheckDateStart);
cmd.Parameters.Add("@txtcheckDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchAudiometryAppointmentData.txtcheckDateEnd);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
AADataList addValue = new AADataList();
addValue.aRowNum = int.Parse(dr["RowNum"].ToString());
addValue.aID = Int64.Parse(dr["ID"].ToString());
addValue.aDate = DateTime.Parse(dr["ReserveDate"].ToString()).ToString("yyyy-MM-dd");
addValue.aStartTime = dr["StartTime"].ToString();
addValue.aEndTime = dr["EndTime"].ToString();
addValue.aPeopleID = int.Parse(dr["ReserveID"].ToString());
addValue.aPeopleName = dr["ReserveName"].ToString();
addValue.aStudentID = int.Parse(dr["StudentID"].ToString());
addValue.aStudentName = dr["StudentName"].ToString();
addValue.aStudentState = dr["CaseStatu"].ToString();
addValue.aItem = dr["CheckItem"].ToString();
addValue.aItemExplain = dr["ItemOtherExplain"].ToString();
addValue.aContent = dr["CheckContent"].ToString();
addValue.aState = int.Parse(dr["State"].ToString());
addValue.aAssess1 = int.Parse(dr["AssessWho1"].ToString());
addValue.aAssess2 = int.Parse(dr["AssessWho2"].ToString());
addValue.aAssessName1 = dr["AssessName1"].ToString();
addValue.aAssessName2 = dr["AssessName2"].ToString();
addValue.aUnit = int.Parse(dr["Unit"].ToString());
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
AADataList addValue = new AADataList();
addValue.aID = -1;
addValue.aContent = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<AADataList> SearchAudiometryAppointmentDataBase(int index, SearchAudiometryAppointment SearchAudiometryAppointmentData)
{
Audiometry aDB = new Audiometry();
return aDB.SearchAudiometryAppointmentDataBase(index, SearchAudiometryAppointmentData);
}
