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