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