public string[] SearchPropertyRecordDataCount(SearchPropertyRecord searchRecordData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchPropertyRecordConditionReturn(searchRecordData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM PropertyRecord WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@PropertyID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(searchRecordData.txtpropertyID) + "%";
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(searchRecordData.txtpropertyName) + "%";
cmd.Parameters.Add("@Code", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringtoIntFunction(searchRecordData.txtpropertyCode)+"%";
cmd.Parameters.Add("@ApplyID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringtoIntFunction(searchRecordData.txtapplyID) + "%";
cmd.Parameters.Add("@Location", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchRecordData.txtlocation);
cmd.Parameters.Add("@Custody", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchRecordData.txtcustody);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<SearchPropertyRecordResult> SearchPropertyRecordData(int indexpage, SearchPropertyRecord searchRecordData)
{
List<SearchPropertyRecordResult> returnValue = new List<SearchPropertyRecordResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchPropertyRecordConditionReturn(searchRecordData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PropertyRecord.Code DESC) " +
"AS RowNum, PropertyRecord.*, PropertyLocation.LocationName, PropertyCustody.CustodyName FROM PropertyRecord " +
"LEFT JOIN PropertyLocation ON PropertyRecord.Location=PropertyLocation.ID " +
"LEFT JOIN PropertyCustody ON PropertyRecord.Custody=PropertyCustody.ID " +
"WHERE PropertyRecord.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("@PropertyID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(searchRecordData.txtpropertyID) + "%";
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(searchRecordData.txtpropertyName) + "%";
cmd.Parameters.Add("@Code", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringtoIntFunction(searchRecordData.txtpropertyCode) + "%";
cmd.Parameters.Add("@ApplyID", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringtoIntFunction(searchRecordData.txtapplyID) + "%";
cmd.Parameters.Add("@Location", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchRecordData.txtlocation);
cmd.Parameters.Add("@Custody", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchRecordData.txtcustody);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchPropertyRecordResult addValue = new SearchPropertyRecordResult();
addValue.ID = dr["RecordID"].ToString();
addValue.txtwriteDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
addValue.txtbuyDate = DateTime.Parse(dr["PurchaseDate"].ToString()).ToString("yyyy-MM-dd");
addValue.txtpropertyPrice = dr["Price"].ToString();
addValue.txtpropertyID = dr["PropertyID"].ToString();
addValue.txtcode = dr["Code"].ToString();
addValue.txtapplyID = dr["ApplyID"].ToString();
addValue.txtpropertyName = dr["PropertyName"].ToString();
addValue.txtpropertyState = dr["PropertyState"].ToString();
addValue.txtlocation = dr["LocationName"].ToString();
addValue.txtcustody = dr["CustodyName"].ToString();
addValue.txtUnit = dr["Unit"].ToString();
addValue.checkNo = "1";
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchPropertyRecordResult addValue = new SearchPropertyRecordResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] searchPropertyRecordDataBaseCount(SearchPropertyRecord searchRecordData)
{
PropertyDataBase pDB = new PropertyDataBase();
pDB.propertyFunction();
if (int.Parse(pDB._StaffhaveRoles[3]) == 1)
{
return pDB.SearchPropertyRecordDataCount(searchRecordData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
private string SearchPropertyRecordConditionReturn(SearchPropertyRecord searchRecordData)
{
string ConditionReturn = "";
if (searchRecordData.txtpropertyCode != null)
{
ConditionReturn += " AND PropertyRecord.Code LIKE @Code ";
}
if (searchRecordData.txtpropertyID != null)
{
ConditionReturn += " AND PropertyRecord.PropertyID LIKE @PropertyID ";
}
if (searchRecordData.txtapplyID != null)
{
ConditionReturn += " AND PropertyRecord.ApplyID LIKE @ApplyID ";
}
if (searchRecordData.txtpropertyName != null)
{
ConditionReturn += " AND PropertyRecord.PropertyName like @PropertyName ";
}
if (searchRecordData.txtlocation != null)
{
ConditionReturn += " AND PropertyRecord.Location=(@Location) ";
}
if (searchRecordData.txtcustody != null)
{
ConditionReturn += " AND PropertyRecord.Custody=(@Custody) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
this.propertyFunction();
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND PropertyRecord.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<SearchPropertyRecordResult> searchPropertyRecordDataBase(int index, SearchPropertyRecord searchRecordData)
{
PropertyDataBase pDB = new PropertyDataBase();
return pDB.SearchPropertyRecordData(index, searchRecordData);
}
