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