public IEnumerable <DBHouseInfo> Search(string cityName, string source = "", int houseCount = 100, int intervalDay = 7, string keyword = "", bool refresh = false) { string redisKey = $"{cityName}-{source}-{intervalDay}-{houseCount}-{keyword}"; var houses = new List <DBHouseInfo>(); if (!refresh) { houses = redisService.ReadSearchCache(redisKey); if (houses != null) { return(houses); } } using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); string search_SQL = $"SELECT * from { ConstConfigurationName.GetTableName(source)} where 1=1 " + $"and LocationCityName = @LocationCityName and PubTime >= @PubTime"; if (!string.IsNullOrEmpty(keyword)) { search_SQL = search_SQL + " and (HouseText like @KeyWord or HouseLocation like @KeyWord or HouseTitle like @KeyWord) "; } search_SQL = search_SQL + $" order by PubTime desc limit {houseCount} "; houses = dbConnection.Query <DBHouseInfo>(search_SQL, new { LocationCityName = cityName, KeyWord = $"%{keyword}%", PubTime = DateTime.Now.Date.AddDays(-intervalDay) }).ToList(); redisService.WriteSearchCache(redisKey, houses); return(houses); } }
public HouseInfo GetHouseID(long houseID, string source) { using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); return(dbConnection.Query <HouseInfo>($"SELECT * FROM {ConstConfigurationName.GetTableName(source)} where ID = @ID", new { ID = houseID }).FirstOrDefault()); } }
private static List <DBHouseInfo> SearchUserCollections(long userID, string city, string source) { using (IDbConnection dbConnection = Connection) { var tableName = ConstConfigurationName.GetTableName(source); string sqlText = GetSQLText(city, tableName); var list = dbConnection.Query <DBHouseInfo>(sqlText, new { UserID = userID, HouseCity = city, Source = source }).ToList(); return(list); } }
public List <Models.HouseDashboard> GetHouseDashboard() { using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); var list = new List <Models.HouseDashboard>(); foreach (var key in ConstConfigurationName.HouseTableNameDic.Keys) { var tableName = ConstConfigurationName.GetTableName(key); var dashboards = dbConnection.Query <HouseDashboard>(@"SELECT LocationCityName AS CityName, Source, COUNT(id) AS HouseSum, MAX(PubTime) AS LastRecordPubTime FROM " + tableName + $" GROUP BY LocationCityName, Source ORDER BY HouseSum desc;"); list.AddRange(dashboards); } return(list.Where(dash => dash.LastRecordPubTime.CompareTo(DateTime.Now.AddDays(-30)) > 0) .ToList()); } }