private string SearchAidsManageDataConditionReturn(SearchAidsManageResult SearchStructure)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtaID != null)
{
ConditionReturn += " AND HearingAidsID like (@HearingAidsID) ";
}
if (SearchStructure.txtassistmanage != null && SearchStructure.txtassistmanage != "0")
{
ConditionReturn += " AND AidsType=(@AidsType) ";
}
if (SearchStructure.txtbrand != null && SearchStructure.txtbrand != "0")
{
ConditionReturn += " AND AidsBrand =(@AidsBrand) ";
}
if (SearchStructure.txtmodel != null)
{
ConditionReturn += " AND AidsModel like (@AidsModel) ";
}
if (SearchStructure.txtaNo != null)
{
ConditionReturn += " AND HearingAidsNo like(@HearingAidsNo) ";
}
if (SearchStructure.txtaSource != null)
{
ConditionReturn += " AND AidsSource like (@AidsSource) ";
}
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 Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public string[] SearchAidsManageDataCount(SearchAidsManageResult SearchData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
string ConditionReturn = this.SearchAidsManageDataConditionReturn(SearchData);
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "SELECT COUNT(*) FROM HearingAidsManage WHERE isDeleted=0" + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@AidsStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtaStatu);
cmd.Parameters.Add("@HearingAidsID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaID) + "%";
cmd.Parameters.Add("@AidsType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtassistmanage);
cmd.Parameters.Add("@AidsBrand", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtbrand);
cmd.Parameters.Add("@AidsModel", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtmodel) + "%";
cmd.Parameters.Add("@HearingAidsNo", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaNo) + "%";
cmd.Parameters.Add("@AidsSource", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaSource) + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchAidsManageDataBaseCount(SearchAidsManageResult SearchData)
{
Audiometry aDB = new Audiometry();
if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
{
return aDB.SearchAidsManageDataCount(SearchData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchAidsManageResult> SearchAidsManageData(int indexpage, SearchAidsManageResult SearchData)
{
List<SearchAidsManageResult> returnValue = new List<SearchAidsManageResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchAidsManageDataConditionReturn(SearchData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) " +
"AS RowNum, * FROM HearingAidsManage 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("@AidsStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtaStatu);
cmd.Parameters.Add("@HearingAidsID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaID) + "%";
cmd.Parameters.Add("@AidsType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtassistmanage);
cmd.Parameters.Add("@AidsBrand", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchData.txtbrand);
cmd.Parameters.Add("@AidsModel", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtmodel) + "%";
cmd.Parameters.Add("@HearingAidsNo", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaNo) + "%";
cmd.Parameters.Add("@AidsSource", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchData.txtaSource) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchAidsManageResult addValue = new SearchAidsManageResult();
addValue.ID = dr["ID"].ToString();
addValue.txtaStatu = dr["AidsStatu"].ToString();
addValue.txtaID = dr["HearingAidsID"].ToString();
addValue.txtassistmanage = dr["AidsType"].ToString();
addValue.txtbrand = dr["AidsBrand"].ToString();
addValue.txtmodel = dr["AidsModel"].ToString();
addValue.txtaNo = dr["HearingAidsNo"].ToString();
addValue.txtaSource = dr["AidsSource"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
SearchAidsManageResult addValue = new SearchAidsManageResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchAidsManageResult> SearchAidsManageDataBase(int index, SearchAidsManageResult SearchData)
{
Audiometry aDB = new Audiometry();
return aDB.SearchAidsManageData(index, SearchData);
}
