public Dictionary<DateTime, long> GetUserTotalCount(SearchMarketStatCriteria c) { var result = new Dictionary<DateTime, long>(); if (c != null) { var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var startDate = c.StartDate; var endDate = c.EndDate; var currentData = startDate; int i = DateTime.Compare(currentData, endDate); while (i <= 0) { StringBuilder comm = new StringBuilder(); comm.AppendFormat(@"select sum(count) totalcount from YLTEST.{0} where user_type = 0 AND STAT_DATE <=:currDate", StatisticConsts.STAT_MKT31_SUMMARY); var queryCommand = new QueryCommand(comm.ToString(), provider); queryCommand.AddParameter("currDate", currentData.ToString("yyyyMMdd").ToInt32(), DbType.Int32); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { result.Add(currentData, string.IsNullOrEmpty(dt.Rows[0]["totalcount"].ToString()) ? 0 : long.Parse(dt.Rows[0]["totalcount"].ToString())); } } else { result.Add(currentData, 0); } currentData = currentData.AddDays(1); i = DateTime.Compare(currentData, endDate); } } return result; }
public Dictionary<DateTime, long> GetMarketUserStatData(string table, SearchMarketStatCriteria c) { Dictionary<DateTime, long> result = new Dictionary<DateTime, long>(); if (table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ACTIVE_UV) || table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ADD_UV) || table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_PV)) { StringBuilder comm = new StringBuilder(); comm.AppendFormat(@"select STAT_DATE, sum(count) count from yltest.{0} where STAT_DATE >=:startDate AND STAT_DATE <=:endDate ", table);//, table, c.StartDate.ToString("yyyyMMdd"), c.EndDate.ToString("yyyyMMdd")); if (!string.IsNullOrEmpty(c.Firmware)) { comm.AppendFormat(" and FIRMWARE_MODE = :firmware");//, c.Firmware); } if (!string.IsNullOrEmpty(c.Manufacturer)) { comm.AppendFormat(" and MANUFACTURER = :pf");//, c.Manufacturer); } comm.Append(" group by STAT_DATE order by STAT_DATE"); var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var queryCommand = new QueryCommand(comm.ToString(), provider); queryCommand.AddParameter("startDate", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32); queryCommand.AddParameter("endDate", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32); if (!string.IsNullOrEmpty(c.Firmware)) { queryCommand.AddParameter("firmware", c.Firmware, DbType.String); } if (!string.IsNullOrEmpty(c.Manufacturer)) { queryCommand.AddParameter("pf", c.Manufacturer, DbType.String); } var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { result.Add(DateTime.ParseExact(row["STAT_DATE"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : long.Parse(row["COUNT"].ToString())); } } } } return result; }