public IEnumerable <BaseHouseInfo> 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 <BaseHouseInfo>(); if (!refresh) { houses = redis.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 <BaseHouseInfo>(search_SQL, new { LocationCityName = cityName, KeyWord = $"%{keyword}%", PubTime = DateTime.Now.Date.AddDays(-intervalDay) }).ToList(); redis.WriteSearchCache(redisKey, houses); return(houses); } }
public void BulkInsertHouses(List <BaseHouseInfo> list) { if (list == null || list.Count == 0) { return; } var tableName = ConstConfigurationName.GetTableName(list.FirstOrDefault().Source); using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); IDbTransaction transaction = dbConnection.BeginTransaction(); var result = dbConnection.Execute("INSERT INTO " + tableName + @" (`HouseTitle`, `HouseOnlineURL`, `HouseLocation`, `DisPlayPrice`, `PubTime`, `HousePrice`, `LocationCityName`, `Source`, `HouseText`, `IsAnalyzed`, `Status`,`PicURLs`) VALUES (@HouseTitle, @HouseOnlineURL, @HouseLocation, @DisPlayPrice, @PubTime, @HousePrice, @LocationCityName, @Source, @HouseText, @IsAnalyzed, @Status,@PicURLs) ON DUPLICATE KEY UPDATE DataChange_LastTime=now();", list, transaction: transaction); transaction.Commit(); } }
public List <HouseDashboard> GetHouseDashboard() { using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); var list = new List <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()); } }
public List <BaseHouseInfo> QueryByTimeSpan(DateTime fromTime, DateTime toTime) { var houseList = new List <BaseHouseInfo>(); foreach (var key in ConstConfigurationName.HouseTableNameDic.Keys) { using (IDbConnection dbConnection = GetConnection()) { dbConnection.Open(); string search_SQL = $"SELECT * from { ConstConfigurationName.GetTableName(key)} where " + $" PubTime BETWEEN @FromTime AND @ToTime "; search_SQL = search_SQL + $" order by PubTime desc"; var houses = dbConnection.Query <BaseHouseInfo>(search_SQL, new { FromTime = fromTime, ToTime = toTime }); houseList.AddRange(houses); } } return(houseList); }