public List<List<MarketStat30StataDetailItem>> GetCommonStatNewUserDetail(string date, string detailTableName) { var start = date.ToInt32().ToString(); var end = start; if (date.ToInt32() > 0) { end = start.ToExactDateTime(DateTimeFormat.yyyyMMdd).AddDays(1).ToString(DateTimeFormat.yyyyMMdd); } const string SQL_PATTERN = @"SELECT COUNT({2}) AS COUNT, {2} FROM YLTEST.{3} WHERE CREATE_DATE_TIME BETWEEN TO_DATE('{0}', 'YYYYMMDD') AND TO_DATE('{1}', 'YYYYMMDD') GROUP BY {2} ORDER BY COUNT"; var ret = new List<List<MarketStat30StataDetailItem>>(); var items = new List<string> { "MODEL", "CHANNEL" }; var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); foreach (var col in items) { var sql = string.Format(SQL_PATTERN, start, end, col, detailTableName); var itemResult = new List<MarketStat30StataDetailItem>(); var queryCommand = new QueryCommand(sql, provider); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count > 0) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { if (row[col] == null) continue; var array = new MarketStat30StataDetailItem(); array.Count = row["COUNT"].ToString().ToInt32(); array.Column = row[col].ToString(); itemResult.Add(array); } } } ret.Add(itemResult); } return ret; }
public List<List<MarketStat30StataDetailItem>> GetCommonStatActiveUserDetail(string date, string sumTableName) { date = date.ToInt32().ToString(); const string SQL_PATTERN = @"SELECT SUM(TIMES) AS COUNT, {1} FROM YLTEST.{2} WHERE STAT_DATE={0} GROUP BY {1} ORDER BY COUNT"; var ret = new List<List<MarketStat30StataDetailItem>>(); var items = new List<string> { "MODEL", "CHANNEL" }; var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); foreach (var col in items) { var sql = string.Format(SQL_PATTERN, date, col, sumTableName); var itemResult = new List<MarketStat30StataDetailItem>(); var queryCommand = new QueryCommand(sql, provider); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count > 0) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { if (row[col] == null) continue; var array = new MarketStat30StataDetailItem(); array.Count = row["COUNT"].ToString().ToInt32(); array.Column = row[col].ToString(); itemResult.Add(array); } } } ret.Add(itemResult); } return ret; }