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) })); }