public JsonResult SelfDescTags()
{
string sql = "select * from admin inner join selfDesc on admin.id = selfDesc.selfId where admin.id = " +
GetAdminId();
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetContractListOfAdmin()
{
string sql = "select * from contract inner join house on house.id = contract.houseId inner join admin on admin.id = contract.adminId where admin.id=" +
GetAdminId() + " order by startAt desc";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult SelfDescTags()
{
string sql = "select * from user inner join selfDesc on user.id = selfDesc.selfId where user.id = " +
GetUserId();
return(Success(DataOperate.FindAll(sql)));
}
public bool DeleteOwnerToTenant(DeleteOwnerToTenant deleteOwnerToTenant)
{
var selSql = "select * from contract where userId=" + deleteOwnerToTenant.Id;
string[] sqlT = new string[2];
int i = 0;
sqlT[i++] = "update house set houseStatus = 'unactivated' where userId=" + deleteOwnerToTenant.Id;
sqlT[i] = "update user SET type = 'tenant' where id= " + deleteOwnerToTenant.Id;
DataSet allContract = DataOperate.FindAll(selSql);
if (allContract.Tables.Count == 1 && allContract.Tables[0].Rows.Count == 0)
{
DataOperate.Update(sqlT[i]);
return(true);
}
else if (GetContractBool(allContract))
{
return(DataOperate.ExecTransaction(sqlT));
}
else
{
return(false);
}
}
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 GetHouseListOfAdmin()
{
string sql =
"select * from house inner join contract on house.id = contract.houseId where contract.type = 'withOwner' and contract.adminId=" +
GetAdminId();
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetOrderList()
{
string sql =
"select * from houseOrdered inner join house on houseOrdered.houseId = house.id inner join contract on houseOrdered.houseId = contract.houseId inner join admin on admin.id = contract.adminId where contract.type='withOwner' and houseOrdered.userId=" +
GetUserId();
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetApplyRenewalList(string type)
{
var sql =
"select * from renewalContractApply inner join house on renewalContractApply.houseId = house.id where type='" +
type + "' order by renewalContractApply.id desc";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetHouseByContractId(int contractId)
{
var sql = "select * from contract where id=" + contractId;
var contract = DataOperate.FindOne(sql);
var houseId = contract["houseId"].ToString();
var houseSql = "select * from house where id=" + houseId;
return(Success(DataOperate.FindAll(houseSql)));
}
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 GetCollectList()
{
var sql =
"select * from houseCollected inner join house on house.id = houseCollected.houseId where houseCollected.userId=" +
GetUserId();
var addSql =
"select count(*) as count,houseId from houseCollected where houseId in (select houseId from houseCollected where userId=" +
GetUserId() + ") group by houseId";
return(Success(new
{
self = DataOperate.FindAll(sql),
addNum = DataOperate.FindAll(addSql)
}));
}
public bool DeleteTenant(TenantToDelete tenantToDelete)
{
string selSql = "select * from contract where userId=" + tenantToDelete.Id;
string delSql = "update user set type='cancelled' where id=" + tenantToDelete.Id;
DataSet allContract = DataOperate.FindAll(selSql);
if ((allContract.Tables.Count == 1 && allContract.Tables[0].Rows.Count == 0) ||
GetContractBool(allContract))
{
DataOperate.Update(delSql);
return(true);
}
return(false);
}
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 GetOwnerContractList()
{
var sql = "select * from contract where type='withOwner' and contractStatus != 'invalid'";
DataSet allContract = DataOperate.FindAll(sql);
// 检查合同状态并修改
foreach (DataRow row in allContract.Tables[0].Rows)
{
var expired = (DateTime.Parse(row["endAt"].ToString()) <
DateTime.Parse(DateTime.Now.ToString()));
var contractStatus = expired ? "fallDue" : "undue";
var newSql = "update contract set contractStatus='" + contractStatus + "' where id=" +
row["id"].ToString();
DataOperate.Update(newSql);
}
var sqlParent = "select * from contract where type = 'withOwner' and parentNum is null and contractStatus != 'invalid'";
var sqlChildren = "select * from contract where type = 'withOwner' and parentNum is not null and contractStatus != 'invalid'";
var parentList = new List <OwnerContract>();
DataSet parentRows = DataOperate.FindAll(sqlParent);
DataSet childrenRows = DataOperate.FindAll(sqlChildren);
foreach (DataRow row in parentRows.Tables[0].Rows)
{
var ownerContract = new OwnerContract();
foreach (var dataColumn in row.Table.Columns)
{
ownerContract.SetAttribute(dataColumn.ToString(), row[dataColumn.ToString()].ToString());
}
ownerContract.Children = GetChildrenByParentId(childrenRows, ownerContract.Id.ToString());
parentList.Add(ownerContract);
}
return(Success(parentList));
}
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 JsonResult GetContractCountByType()
{
var startMonth = DateTime.Now.AddMonths(+1).Month; // 开始月份
var monthList = new List <int>();
for (int i = 0; i < 12; i++, startMonth++)
{
if (startMonth <= 12)
{
monthList.Add(startMonth);
}
else
{
monthList.Add(startMonth - 12);
}
}
var monthArray = monthList.ToArray();
var tenantData =
DataOperate.FindAll("select MONTH(startAt) as month, count(startAt) as count from contract where type = 'withTenant' and DATE_FORMAT(contract.startAt,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') group by MONTH(startAt)");
var ownerData =
DataOperate.FindAll("select MONTH(startAt) as month, count(startAt) as count from contract where type = 'withOwner' and DATE_FORMAT(contract.startAt,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') group by MONTH(startAt)");
return(Success(new { tenantData, ownerData, monthArray }));
}
public JsonResult GetFinanceCountByType()
{
var startMonth = DateTime.Now.AddMonths(+1).Month; // 开始月份
var monthList = new List <int>();
for (int i = 0; i < 12; i++, startMonth++)
{
if (startMonth <= 12)
{
monthList.Add(startMonth);
}
else
{
monthList.Add(startMonth - 12);
}
}
var monthArray = monthList.ToArray();
var payData =
DataOperate.FindAll("select MONTH(tranDate) as month, Sum(account) as account from transactions where tranType = 'withOwner' and DATE_FORMAT(transactions.tranDate,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') group by MONTH(tranDate)");
var incomeData =
DataOperate.FindAll("select MONTH(tranDate) as month, Sum(account) as account from transactions where tranType = 'withTenant' and DATE_FORMAT(transactions.tranDate,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') group by MONTH(tranDate)");
return(Success(new { payData, incomeData, monthArray }));
}
public JsonResult GetUnactivatedHouseList()
{
string sql = "select * from house where rentStatus='unactivated'";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetResOfHouse(int houseId, string resType)
{
string sql = "select * from resOfHouse where resType ='" + resType + "' and houseId=" + houseId;
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetUserIdByHouseId(int houseId)
{
return(Success(DataOperate.FindAll("select * from house where id=" + houseId)));
}
public JsonResult GetAllUser()
{
string sql = "select * from user";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetEmptyHouseList()
{
var sql = "select * from house where rentStatus='empty'";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetHouseDetailInfo(int houseId)
{
string sql = "select * from house inner join contract on house.id = contract.houseId where house.id=" + houseId;
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetAdminInfo()
{
string sql = "select * from admin where id = " + GetAdminId();
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetPriceByHouseId(int houseId)
{
string sql = "select * from house where id=" + houseId;
return(Success(DataOperate.FindAll(sql)));
}
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) }));
}
public JsonResult GetOrderList()
{
var sql = "select * from houseOrdered inner join house on house.id = houseOrdered.houseId order by houseOrdered.id desc";
return(Success(DataOperate.FindAll(sql)));
}
public JsonResult GetHouseListByOwnerId(string id)
{
string sql = "select * from house where rentStatus in ('empty', 'rented') and userId=" + id;
return(Success(DataOperate.FindAll(sql)));
}