public JsonResult GetHouseDetailInfo(int houseId)
{
bool liked;
var sqlExits = "select count(*) from houseCollected where houseId=" + houseId + " and userId=" +
GetUserId();
var count = Convert.ToInt32(DataOperate.Sele(sqlExits));
var sql = "select * from house inner join contract on house.id = contract.houseId where house.id=" +
houseId;
if (count == 0)
{
return(Success(new
{
self = DataOperate.FindAll(sql),
liked = false
}));
}
else
{
return(Success(new
{
self = DataOperate.FindAll(sql),
liked = true
}));
}
}
public JsonResult GetHouseListToTable(GetHouseListToTable getHouseListToTable)
{
var selSql1 = "select * from house ";
DataSet allHouse = DataOperate.FindAll(selSql1);
string[] signed = { "activated", "empty" };
// 检查房主合同状态 修改房源状态
foreach (DataRow row in allHouse.Tables[0].Rows)
{
if (signed.Contains(row["rentStatus"].ToString()))
{
var selSql2 = "select * from contract where type='withOwner' and contractStatus = 'undue' and houseId=" + row["id"].ToString();
DataSet allHouseContract = DataOperate.FindAll(selSql2);
if (allHouseContract.Tables.Count == 1 && allHouseContract.Tables[0].Rows.Count == 0)
{
DataOperate.Update("update house set rentStatus='invalid' where id=" + row["id"]);
}
}
}
var countSql = "select count(*) from house";
var sql = "select * from house";
if (getHouseListToTable.Status.Length > 0)
{
countSql += " where rentStatus in ('" + string.Join("','", getHouseListToTable.Status) + "')";
sql += " where rentStatus in ('" + string.Join("','", getHouseListToTable.Status) + "')";
}
sql += " order by id desc limit " + (getHouseListToTable.PageNum - 1) * getHouseListToTable.PageSize + "," + getHouseListToTable.PageSize;
return(Success(new { totalCount = DataOperate.Sele(countSql), data = DataOperate.FindAll(sql) }));
}
public JsonResult CheckOrder(CheckOrder checkOrder)
{
var sqlExits = "select count(*) from houseOrdered where houseId=" + checkOrder.HouseId + " and userId=" +
GetUserId() + " and orderStatus='todo'";
var count = Convert.ToInt32(DataOperate.Sele(sqlExits));
return(Success(count != 0));
}
public JsonResult GetUserCountByType()
{
var ownerCount = DataOperate.Sele("select count(*) from user where type='owner'");
var tenantCount = DataOperate.Sele("select count(*) from user where type='tenant'") + ownerCount;
var countList = new List <int> {
tenantCount, ownerCount
};
var countArr = countList.ToArray();
return(Success(new { countArr }));
}
public JsonResult GetHouseCountByType()
{
var activatedCount = DataOperate.Sele("select count(*) from house where rentStatus = 'activated'");
var emptyCount = DataOperate.Sele("select count(*) from house where rentStatus = 'empty'");
var rentedCount = DataOperate.Sele("select count(*) from house where rentStatus = 'rented'");
var countList = new List <int> {
activatedCount, emptyCount, rentedCount
};
var countArr = countList.ToArray();
return(Success(new { countArr }));
}
public JsonResult GetTobeOwnerApplyList(int pageNum, int pageSize)
{
int totalCount;
pageSize = pageSize == 0 ? 1 : pageSize;
pageNum = pageNum == 0 ? 1 : pageNum;
totalCount = DataOperate.Sele("select count(*) from applyTobeowner");
string sql = "select * from applyTobeowner order by id desc limit " + (pageNum - 1) * pageSize + "," + pageSize;
return(Success(new { totalCount = totalCount, data = DataOperate.FindAll(sql) }));
}
public JsonResult GetFinanceList(string tranType, int pageNum, int pageSize)
{
pageSize = pageSize == 0 ? 1 : pageSize;
pageNum = pageNum == 0 ? 1 : pageNum;
var totalCount = DataOperate.Sele("select count(*) from transactions where tranType = '" + tranType + "'");
DataSet ds = DataOperate.FindAll("select count(contractId) as rowCount from transactions where tranType = '" + tranType + "' group by contractId");
string[] countArray = new string[ds.Tables[0].Rows.Count]; //把DataSet中的数据转换为一维数组
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
countArray[row] = ds.Tables[0].Rows[row]["rowCount"].ToString();
}
var sql = "select * from transactions where tranType = '" + tranType + "' order by id desc limit " + (pageNum - 1) * pageSize +
"," + pageSize;
return(Success(new { totalCount = totalCount, countArray = countArray, data = DataOperate.FindAll(sql) }));
}
public JsonResult DissolveHouseContract(DissolveHouseContract dissolveHouseContract)
{
string sql =
"select count(*) from contract where type='withTenant' and contractStatus='undue' and houseId=" +
dissolveHouseContract.HouseId;
string[] sqlT = new string[4];
int i = 0;
sqlT[i++] = "update contract set contractStatus='invalid' where id=" + dissolveHouseContract.ContractId;
sqlT[i++] = "update transactions set tranStatus='invalid' where tranStatus='unpaid' and contractId=" + dissolveHouseContract.ContractId;
sqlT[i++] = "update house set rentStatus='invalid' where id=" + dissolveHouseContract.HouseId;
sqlT[i] = "delete from resOfHouse where houseId=" + dissolveHouseContract.HouseId;
if (DataOperate.Sele(sql) == 0)
{
return(Success(DataOperate.ExecTransaction(sqlT)));
}
else
{
return(Fail("该合同关联房源已被租用，不可解约..", 3001));
}
}
public JsonResult GetUserList(int id, int pageNum, int pageSize, string type)
{
int totalCount;
pageSize = pageSize == 0 ? 1 : pageSize;
pageNum = pageNum == 0 ? 1 : pageNum;
string sql = "";
if (id == 0)
{
totalCount = DataOperate.Sele("select count(*) from user where type = '" + type + "'");
sql = "select * from user where type = '" + type + "' order by id desc limit " + (pageNum - 1) * pageSize + "," +
pageSize;
}
else
{
totalCount = DataOperate.Sele("select count(*) from user where type ='" + type + "' and id=" + id);
sql = "select * from user where type = '" + type + "' and id=" + id + " order by id desc limit " +
(pageNum - 1) * pageSize + "," + pageSize;
}
return(Success(new { totalCount = totalCount, data = DataOperate.FindAll(sql) }));
}
public JsonResult GetFinanceList(string tranType, string contractId, int pageNum, int pageSize)
{
pageSize = pageSize == 0 ? 1 : pageSize;
pageNum = pageNum == 0 ? 1 : pageNum;
var where = new List <WhereItem>();
if (contractId != null)
{
where.Add(new WhereItem("transactions.contractId", "=", contractId));
}
var countSql = "select * from transactions where tranType ='" + tranType + "' and userId=" + GetUserId();
var sql = "select * from transactions where tranType ='" + tranType + "' and userId=" + GetUserId();
foreach (var whereItem in where)
{
countSql = countSql + " and " + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value + "'";
sql = sql + " and " + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value + "'";
}
var totalCount = DataOperate.Sele(countSql);
sql = sql + " order by id desc limit " + (pageNum - 1) * pageSize + "," + pageSize;
return(Success(new { totalCount = totalCount, data = DataOperate.FindAll(sql) }));
}
public bool CheckRenewal(int contractId)
{
var selSql = "select count(*) from renewalContractApply where applyStatus='todo' and contractId=" + contractId + " and userId=" + GetUserId();
return(DataOperate.Sele(selSql) == 0);
}
public JsonResult GetHouseListByOptions(string atCity, string rentType, string priceStart, string priceEnd, string areaStart,
string areaEnd, string payForm, string inputCommunity)
{
var where = new List <UserWhereItem>();
if (atCity != null)
{
where.Add(new UserWhereItem("house.atCity", "=", atCity));
}
if (rentType != null)
{
where.Add(new UserWhereItem("house.rentType", "=", rentType));
}
if (priceStart != null)
{
where.Add(new UserWhereItem("house.rentPrice", ">=", priceStart));
}
if (priceEnd != null)
{
where.Add(new UserWhereItem("house.rentPrice", "<=", priceEnd));
}
if (areaStart != null)
{
where.Add(new UserWhereItem("house.area", ">=", areaStart));
}
if (areaEnd != null)
{
where.Add(new UserWhereItem("house.area", "<=", areaEnd));
}
if (payForm != null)
{
where.Add(new UserWhereItem("house.payForm", "=", payForm));
}
if (inputCommunity != null)
{
where.Add(new UserWhereItem("house.community", "like", '%' + inputCommunity + '%'));
}
string sql = "";
string countSql = "";
if (where.Count > 0)
{
countSql =
"select count(*) from house inner join user on house.userId = user.id inner join resOfHouse on house.id = resOfHouse.houseId where house.rentStatus = 'empty' and ";
sql =
"select * from house inner join user on house.userId = user.id where house.rentStatus = 'empty' and ";
foreach (var whereItem in where)
{
countSql = countSql + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value +
"' and ";
sql = sql + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value + "' and ";
}
countSql = countSql.TrimEnd('a', 'n', 'd', ' ');
sql = sql.TrimEnd('a', 'n', 'd', ' ');
}
else
{
countSql =
"select count(*) from house where rentStatus = 'empty'";
sql =
"select * from house where rentStatus = 'empty'";
}
return(Success(new { totalCount = DataOperate.Sele(countSql), data = DataOperate.FindAll(sql) }));
}
public JsonResult GetFinanceCount()
{
return(Success(DataOperate.Sele("select sum(account) from transactions where tranStatus='paid'"))); // 所有已完成的交易paid
}
public JsonResult GetContractCount()
{
return(Success(DataOperate.Sele("select count(*) from contract"))); // 所有签办的合同
}
public JsonResult GetHouseCount()
{
return(Success(DataOperate.Sele("select count(*) from house where rentStatus in ('activated','empty','rented')"))); // 所有有效房源
}
public JsonResult GetUserCount()
{
return(Success(DataOperate.Sele("select count(*) from user"))); // 所有用户
}
public JsonResult GetHouseListByOptions(string userId, string userName, string atCity, string rentType, string rentStatus,
string priceStart, string priceEnd, int pageNum, int pageSize)
{
pageSize = pageSize == 0 ? 1 : pageSize;
pageNum = pageNum == 0 ? 1 : pageNum;
var where = new List <WhereItem>();
if (userId != null)
{
where.Add(new WhereItem("user.id", "=", userId));
}
if (userName != null)
{
where.Add(new WhereItem("user.name", "=", userName));
}
if (atCity != null)
{
where.Add(new WhereItem("house.atCity", "=", atCity));
}
if (rentType != null)
{
where.Add(new WhereItem("house.rentType", "=", rentType));
}
if (rentStatus != null)
{
where.Add(new WhereItem("house.rentStatus", "=", rentStatus));
}
if (priceStart != null)
{
where.Add(new WhereItem("house.rentPrice", ">=", priceStart));
}
if (priceEnd != null)
{
where.Add(new WhereItem("house.rentPrice", "<=", priceEnd));
}
string sql = "";
string countSql = "";
if (where.Count > 0)
{
countSql = "select count(*) from house inner join user on house.userId = user.id inner join resOfHouse on house.id = resOfHouse.houseId where house.rentStatus in ('activated', 'empty', 'rented') and ";
sql = "select * from house inner join user on house.userId = user.id where house.rentStatus in ('activated', 'empty', 'rented') and ";
foreach (var whereItem in where)
{
countSql = countSql + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value + "' and ";
sql = sql + whereItem.Column + " " + whereItem.Condition + " '" + whereItem.Value + "' and ";
}
countSql = countSql.TrimEnd('a', 'n', 'd', ' ') + " limit " + (pageNum - 1) * pageSize + "," +
pageSize;
sql = sql.TrimEnd('a', 'n', 'd', ' ') + " order by id desc limit " + (pageNum - 1) * pageSize + "," +
pageSize;
}
else
{
countSql = "select count(*) from house where rentStatus in ('activated', 'empty', 'rented')";
sql = "select * from house where rentStatus in ('activated', 'empty', 'rented') order by id desc limit " + (pageNum - 1) * pageSize + "," + pageSize;
}
return(Success(new { totalCount = DataOperate.Sele(countSql), data = DataOperate.FindAll(sql) }));
}
