public void BulkInsertHouses(List <HouseInfo> list)
{
if (list == null || list.Count == 0)
{
return;
}
var tableName = ConstConfigName.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 HouseInfo GetHouseID(long houseID, string source)
{
using (IDbConnection dbConnection = GetConnection())
{
dbConnection.Open();
return(dbConnection.Query <HouseInfo>($"SELECT * FROM {ConstConfigName.GetTableName(source)} where ID = @ID",
new
{
ID = houseID
}).FirstOrDefault());
}
}
private List <HouseInfo> SearchUserCollections(long userID, string city, string source)
{
using (IDbConnection dbConnection = GetConnection())
{
var tableName = ConstConfigName.GetTableName(source);
string sqlText = GetSQLText(city, tableName);
var list = dbConnection.Query <HouseInfo>(sqlText,
new
{
UserID = userID,
HouseCity = city,
Source = source
}).ToList();
return(list);
}
}
public List <HouseDashboard> GetHouseDashboard()
{
using (IDbConnection dbConnection = GetConnection())
{
dbConnection.Open();
var list = new List <HouseDashboard>();
foreach (var key in ConstConfigName.HouseTableNameDic.Keys)
{
var tableName = ConstConfigName.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 && dash.HouseSum > 100)
.ToList());
}
}
public List <BaseHouseInfo> QueryByTimeSpan(DateTime fromTime, DateTime toTime)
{
var houseList = new List <BaseHouseInfo>();
foreach (var key in ConstConfigName.HouseTableNameDic.Keys)
{
using (IDbConnection dbConnection = GetConnection())
{
dbConnection.Open();
string search_SQL = $"SELECT * from { ConstConfigName.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);
}
