Exemplo n.º 1
0
 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());
     }
 }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        public void BulkInsertHouses(List <BaseHouseInfo> 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();
            }
            LogHelper.RunActionTaskNotThrowEx(() =>
            {
                elasticsearchService.SaveHousesToES(list);
            }, "SaveHousesToES");
        }