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