public List <HouseOwnerInfo> GetList()
{
string cmdText = @"select Id,HouseOwnerName,MobilePhone,TelPhone,PropertyCompanyId,ResidenceCommunityId,ResidentialBuildingId,ResidentialUnitId,HouseId,LastUpdatedDate
from HouseOwner
order by LastUpdatedDate desc " ;
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerName = reader.GetString(1);
model.MobilePhone = reader.GetString(2);
model.TelPhone = reader.GetString(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.ResidentialBuildingId = reader.GetGuid(6);
model.ResidentialUnitId = reader.GetGuid(7);
model.HouseId = reader.GetGuid(8);
model.LastUpdatedDate = reader.GetDateTime(9);
list.Add(model);
}
}
}
return(list);
}
public HouseOwnerInfo GetModel(object Id)
{
HouseOwnerInfo model = null;
string cmdText = @"select top 1 Id,HouseOwnerName,MobilePhone,TelPhone,PropertyCompanyId,ResidenceCommunityId,ResidentialBuildingId,ResidentialUnitId,HouseId,LastUpdatedDate
from HouseOwner
where Id = @Id " ;
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new HouseOwnerInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerName = reader.GetString(1);
model.MobilePhone = reader.GetString(2);
model.TelPhone = reader.GetString(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.ResidentialBuildingId = reader.GetGuid(6);
model.ResidentialUnitId = reader.GetGuid(7);
model.HouseId = reader.GetGuid(8);
model.LastUpdatedDate = reader.GetDateTime(9);
}
}
}
return(model);
}
public List <HouseOwnerInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select count(*) from UserHouseOwner uho
join HouseOwner ho on ho.Id = uho.HouseOwnerId
join CollectLifeAspnetDb.dbo.aspnet_Users u on u.UserId = uho.UserId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by ho.LastUpdatedDate desc) as RowNumber,
ho.Id,ho.HouseOwnerName,ho.MobilePhone,ho.TelPhone
from UserHouseOwner uho
join HouseOwner ho on ho.Id = uho.HouseOwnerId
join CollectLifeAspnetDb.dbo.aspnet_Users u on u.UserId = uho.UserId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerName = reader.GetString(2);
model.MobilePhone = reader.GetString(3);
model.TelPhone = reader.GetString(4);
list.Add(model);
}
}
}
return(list);
}
public List <HouseOwnerInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select count(*) from HouseOwner ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,HouseOwnerName,MobilePhone,TelPhone,PropertyCompanyId,ResidenceCommunityId,ResidentialBuildingId,ResidentialUnitId,HouseId,LastUpdatedDate
from HouseOwner " ;
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerName = reader.GetString(2);
model.MobilePhone = reader.GetString(3);
model.TelPhone = reader.GetString(4);
model.PropertyCompanyId = reader.GetGuid(5);
model.ResidenceCommunityId = reader.GetGuid(6);
model.ResidentialBuildingId = reader.GetGuid(7);
model.ResidentialUnitId = reader.GetGuid(8);
model.HouseId = reader.GetGuid(9);
model.LastUpdatedDate = reader.GetDateTime(10);
list.Add(model);
}
}
}
return(list);
}
public HouseOwnerInfo GetModelByJoin(object Id)
{
//left join CollectLifeAspnetDb.dbo.aspnet_Users u on u.UserId = ho.UserId
HouseOwnerInfo model = null;
string cmdText = @"select top 1 ho.Id,ho.HouseOwnerName,ho.MobilePhone,ho.TelPhone,ho.PropertyCompanyId,ho.ResidenceCommunityId,ho.ResidentialBuildingId,ho.ResidentialUnitId,ho.HouseId,ho.LastUpdatedDate,
pc.CompanyName,rc.CommunityName,rb.BuildingCode,ru.UnitCode,h.HouseCode
from HouseOwner ho
left join PropertyCompany pc on ho.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on ho.ResidenceCommunityId = rc.Id
left join ResidentialBuilding rb on ho.ResidentialBuildingId = rb.Id
left join ResidentialUnit ru on ho.ResidentialUnitId = ru.Id
left join House h on ho.HouseId = h.Id
where ho.Id = @Id " ;
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new HouseOwnerInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerName = reader.GetString(1);
model.MobilePhone = reader.GetString(2);
model.TelPhone = reader.GetString(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.ResidentialBuildingId = reader.GetGuid(6);
model.ResidentialUnitId = reader.GetGuid(7);
model.HouseId = reader.GetGuid(8);
model.LastUpdatedDate = reader.GetDateTime(9);
model.CompanyName = reader.IsDBNull(10) ? "" : reader.GetString(10);
model.CommunityName = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.BuildingCode = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.UnitCode = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.HouseCode = reader.IsDBNull(14) ? "" : reader.GetString(14);
//model.UserName = reader.IsDBNull(15) ? "" : reader.GetString(15);
}
}
}
return(model);
}
private void Bind(ref string myDataAppend)
{
if (!Id.Equals(Guid.Empty))
{
Page.Title = "编辑楼单元信息";
HouseOwner bll = new HouseOwner();
HouseOwnerInfo model = bll.GetModelByJoin(Id);
if (model != null)
{
txtName.Value = model.HouseOwnerName.Trim();
txtMobilePhone.Value = model.MobilePhone.Trim();
txtTelPhone.Value = model.TelPhone.Trim();
myDataAppend += "{ \"Id\":\"" + model.Id + "\",\"PropertyCompanyId\":\"" + model.PropertyCompanyId + "\",\"ResidenceCommunityId\":\"" + model.ResidenceCommunityId + "\",\"ResidentialBuildingId\":\"" + model.ResidentialBuildingId + "\",\"ResidentialUnitId\":\"" + model.ResidentialUnitId + "\",\"HouseId\":\"" + model.HouseId + "\",\"CompanyName\":\"" + model.CompanyName + "\",\"CommunityName\":\"" + model.CommunityName + "\",\"BuildingCode\":\"" + model.BuildingCode + "\",\"UnitCode\":\"" + model.UnitCode + "\",\"HouseCode\":\"" + model.HouseCode + "\"}";
}
}
}
public IActionResult Index()
{
//return View();
var userNID = User.Claims.ElementAt(1).Value;
var hoInfo = new HouseOwnerInfo();
hoInfo = _db.HouseOwners
.Where(p => p.NID == userNID)
.Select(p => new HouseOwnerInfo()
{
Name = p.Name,
Email = p.Email,
Phone = p.Mobile,
NID = p.NID,
Image = p.Image
}
).SingleOrDefault();
return(View());
}
public List <HouseOwnerInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select Id,HouseOwnerName,MobilePhone,TelPhone,PropertyCompanyId,ResidenceCommunityId,ResidentialBuildingId,ResidentialUnitId,HouseId,LastUpdatedDate
from HouseOwner";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerName = reader.GetString(1);
model.MobilePhone = reader.GetString(2);
model.TelPhone = reader.GetString(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.ResidentialBuildingId = reader.GetGuid(6);
model.ResidentialUnitId = reader.GetGuid(7);
model.HouseId = reader.GetGuid(8);
model.LastUpdatedDate = reader.GetDateTime(9);
list.Add(model);
}
}
}
return(list);
}
public int Update(HouseOwnerInfo model)
{
if (IsExist(model.HouseOwnerName, model.PropertyCompanyId, model.ResidenceCommunityId, model.ResidentialBuildingId, model.ResidentialUnitId, model.HouseId, model.Id))
{
return(110);
}
string cmdText = @"update HouseOwner set HouseOwnerName = @HouseOwnerName,MobilePhone = @MobilePhone,TelPhone = @TelPhone,PropertyCompanyId = @PropertyCompanyId,ResidenceCommunityId = @ResidenceCommunityId,ResidentialBuildingId = @ResidentialBuildingId,ResidentialUnitId = @ResidentialUnitId,HouseId = @HouseId,LastUpdatedDate = @LastUpdatedDate
where Id = @Id" ;
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@HouseOwnerName", SqlDbType.NVarChar, 30),
new SqlParameter("@MobilePhone", SqlDbType.Char, 15),
new SqlParameter("@TelPhone", SqlDbType.Char, 15),
new SqlParameter("@PropertyCompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@ResidenceCommunityId", SqlDbType.UniqueIdentifier),
new SqlParameter("@ResidentialBuildingId", SqlDbType.UniqueIdentifier),
new SqlParameter("@ResidentialUnitId", SqlDbType.UniqueIdentifier),
new SqlParameter("@HouseId", SqlDbType.UniqueIdentifier),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.HouseOwnerName;
parms[2].Value = model.MobilePhone;
parms[3].Value = model.TelPhone;
parms[4].Value = model.PropertyCompanyId;
parms[5].Value = model.ResidenceCommunityId;
parms[6].Value = model.ResidentialBuildingId;
parms[7].Value = model.ResidentialUnitId;
parms[8].Value = model.HouseId;
parms[9].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
public List <HouseOwnerInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = string.Format(@"select count(*)
from HouseOwner ho
left join PropertyCompany pc on ho.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on ho.ResidenceCommunityId = rc.Id
left join ResidentialBuilding rb on ho.ResidentialBuildingId = rb.Id
left join ResidentialUnit ru on ho.ResidentialUnitId = ru.Id
left join House h on ho.HouseId = h.Id
left join UserHouseOwner uho on uho.HouseOwnerId = ho.Id
left join {0}aspnet_Users u on u.UserId = uho.UserId
", ConfigHelper.AspnetDbDbo);
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = string.Format(@"select * from(select row_number() over(order by ho.LastUpdatedDate desc) as RowNumber,
ho.Id,ho.HouseOwnerName,ho.MobilePhone,ho.TelPhone,ho.PropertyCompanyId,ho.ResidenceCommunityId,ho.ResidentialBuildingId,ho.ResidentialUnitId,ho.HouseId,ho.LastUpdatedDate,
pc.CompanyName,rc.CommunityName,rb.BuildingCode,ru.UnitCode,h.HouseCode,u.UserName,uho.Password
from HouseOwner ho
left join PropertyCompany pc on ho.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on ho.ResidenceCommunityId = rc.Id
left join ResidentialBuilding rb on ho.ResidentialBuildingId = rb.Id
left join ResidentialUnit ru on ho.ResidentialUnitId = ru.Id
left join House h on ho.HouseId = h.Id
left join UserHouseOwner uho on uho.HouseOwnerId = ho.Id
left join {0}aspnet_Users u on u.UserId = uho.UserId
", ConfigHelper.AspnetDbDbo);
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerName = reader.GetString(2);
model.MobilePhone = reader.GetString(3);
model.TelPhone = reader.GetString(4);
model.PropertyCompanyId = reader.GetGuid(5);
model.ResidenceCommunityId = reader.GetGuid(6);
model.ResidentialBuildingId = reader.GetGuid(7);
model.ResidentialUnitId = reader.GetGuid(8);
model.HouseId = reader.GetGuid(9);
model.LastUpdatedDate = reader.GetDateTime(10);
model.CompanyName = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.CommunityName = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.BuildingCode = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.UnitCode = reader.IsDBNull(14) ? "" : reader.GetString(14);
model.HouseCode = reader.IsDBNull(15) ? "" : reader.GetString(15);
model.UserName = reader.IsDBNull(16) ? "" : reader.GetString(16);
model.Password = reader.IsDBNull(17) ? "" : reader.GetString(17);
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(HouseOwnerInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(HouseOwnerInfo model)
{
return(dal.Insert(model));
}
