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