public List<SearchApplyPropertyResult> SearchApplyPropertyData(int indexpage, SearchApplyProperty searchApplyData)
{
List<SearchApplyPropertyResult> returnValue = new List<SearchApplyPropertyResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchApplyPropertyConditionReturn(searchApplyData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PropertyBuyRepair.BuyRepairID DESC) " +
"AS RowNum, PropertyBuyRepair.* , StaffDatabase.StaffName FROM PropertyBuyRepair " +
"INNER JOIN StaffDatabase ON PropertyBuyRepair.Recipients=StaffDatabase.StaffID " +
"WHERE PropertyBuyRepair.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("@ApplyType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyType);
cmd.Parameters.Add("@State", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyStatus);
cmd.Parameters.Add("@ApplyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyID);
cmd.Parameters.Add("@FileDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(searchApplyData.txtapplyDateStart);
cmd.Parameters.Add("@FileDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(searchApplyData.txtapplyDateEnd);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchApplyPropertyResult addValue = new SearchApplyPropertyResult();
addValue.ID = dr["BuyRepairID"].ToString();
addValue.txtapplyDate = DateTime.Parse(dr["FileDate"].ToString()).ToString("yyyy-MM-dd");
addValue.txtapplyID = dr["ApplyID"].ToString();
addValue.txtapplyType = dr["ApplyType"].ToString();
addValue.txtapplyPay = dr["Payment"].ToString();
addValue.txtapplyByID = dr["Recipients"].ToString();
addValue.txtapplyBy = dr["StaffName"].ToString();
addValue.txtapplyStatus = dr["State"].ToString();
addValue.txtapplySum = dr["TotalPrice"].ToString();
addValue.checkNo = "1";
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchApplyPropertyResult addValue = new SearchApplyPropertyResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchApplyPropertyDataCount(SearchApplyProperty searchApplyData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchApplyPropertyConditionReturn(searchApplyData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM PropertyBuyRepair "+
"INNER JOIN StaffDatabase ON PropertyBuyRepair.Recipients=StaffDatabase.StaffID " +
"WHERE PropertyBuyRepair.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ApplyType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyType);
cmd.Parameters.Add("@State", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyStatus);
cmd.Parameters.Add("@ApplyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchApplyData.txtapplyID);
cmd.Parameters.Add("@FileDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(searchApplyData.txtapplyDateStart);
cmd.Parameters.Add("@FileDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(searchApplyData.txtapplyDateEnd);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
private string SearchApplyPropertyConditionReturn(SearchApplyProperty searchApplyData)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (searchApplyData.txtapplyType != null)
{
ConditionReturn += " AND PropertyBuyRepair.ApplyType=(@ApplyType) ";
}
if (searchApplyData.txtapplyStatus != null)
{
ConditionReturn += " AND PropertyBuyRepair.State=(@State) ";
}
if (searchApplyData.txtapplyID != null)
{
ConditionReturn += " AND PropertyBuyRepair.ApplyID=(@ApplyID) ";
}
if (searchApplyData.txtapplyDateStart != null && searchApplyData.txtapplyDateEnd != null && searchApplyData.txtapplyDateStart != DateBase && searchApplyData.txtapplyDateEnd != DateBase)
{
ConditionReturn += " AND PropertyBuyRepair.FileDate BETWEEN (@FileDateStart) AND (@FileDateEnd) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
this.applyFunction();
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND PropertyBuyRepair.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public string[] searchApplyPropertyDataBaseCount2(SearchApplyProperty searchApplyData)
{
//SearchApplyProperty searchApplyData = new SearchApplyProperty();
searchApplyData.txtapplyType = "1";
searchApplyData.txtapplyStatus = "1";
PropertyDataBase pDB = new PropertyDataBase();
return pDB.SearchApplyPropertyDataCount(searchApplyData);
}
public string[] searchApplyPropertyDataBaseCount(SearchApplyProperty searchApplyData)
{
PropertyDataBase pDB = new PropertyDataBase();
pDB.applyFunction();
if (int.Parse(pDB._StaffhaveRoles[3]) == 1)
{
return pDB.SearchApplyPropertyDataCount(searchApplyData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchApplyPropertyResult> searchApplyPropertyDataBase2(int index, SearchApplyProperty searchApplyData)
{
//SearchApplyProperty searchApplyData = new SearchApplyProperty();
searchApplyData.txtapplyType = "1";
searchApplyData.txtapplyStatus = "1";
PropertyDataBase pDB = new PropertyDataBase();
return pDB.SearchApplyPropertyData(index, searchApplyData);
}
public List<SearchApplyPropertyResult> searchApplyPropertyDataBase(int index, SearchApplyProperty searchApplyData)
{
PropertyDataBase pDB = new PropertyDataBase();
return pDB.SearchApplyPropertyData(index, searchApplyData);
}
