public Dictionary<string, long> SearchAppStatCountSorted(SearchAppStatCriteria c, string action = "PV", string orderByAction = "DOWN", int startNum = 1, int pageNum = 20)
{
Dictionary<string, long> result = new Dictionary<string, long>();
if ((action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
&& (orderByAction.EqualsOrdinalIgnoreCase("PV")
|| orderByAction.EqualsOrdinalIgnoreCase("DOWN")
|| orderByAction.EqualsOrdinalIgnoreCase("DOWN_OK")))
{
StringBuilder commPV = new StringBuilder();
commPV.AppendFormat(@"SELECT APP_NO, SUM(T.COUNT) PVCOUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate AND T.STATISTIC_DATE <=:endDate ",
action);
StringBuilder commDown = new StringBuilder();
commDown.AppendFormat(@"SELECT APP_NO, SUM(T.COUNT) DOWNCOUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate2 AND T.STATISTIC_DATE <=:endDate2 ",
orderByAction);
if (!string.IsNullOrEmpty(c.Firmware))
{
commPV.AppendFormat(" and T.FIRMWARE_MODE = :firmware ");
commDown.AppendFormat(" and T.FIRMWARE_MODE = :firmware2 ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
commPV.AppendFormat(" and T.MANUFACTURER = :pf");
commDown.AppendFormat(" and T.MANUFACTURER = :pf2");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
commPV.AppendFormat(" and T.COME_FROM = :from1");
commDown.AppendFormat(" and T.COME_FROM = :from2");
}
commPV.AppendFormat(" group by app_no ");
commDown.AppendFormat(" group by app_no ");
//comm.AppendFormat("group by app_no ) )where {0}<=rn and rn<{1}", startNum, startNum + pageNum);
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select APPNO,PVCOUNTB from (select rownum rn,APPNO,PVCOUNTB,DOWNCOUNTB from(select * from (select nvl(a.APP_NO,b.APP_NO) APPNO,nvl(a.PVCOUNT,0) PVCOUNTB,nvl(b.DOWNCOUNT,0) DOWNCOUNTB from ({0}) a full join ({1}) b on A.APP_NO = b.APP_NO) order by DOWNCOUNTB desc))where rn>=:startNum and rn <:endNum", commPV.ToString(), commDown.ToString());
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);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
queryCommand.AddParameter("startDate2", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate2", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware2", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf2", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from2", c.From, DbType.String);
}
if (pageNum != 0)
{
queryCommand.AddParameter("startNum", startNum, DbType.Int32);
queryCommand.AddParameter("endNum", startNum + pageNum, DbType.Int32);
}
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)
{
long count = 0;
long.TryParse(row["PVCOUNTB"].ToString(), out count);
result.Add(row["APPNO"].ToString(), count);
}
}
}
}
return result;
}
public Dictionary<string, long> SearchForAllAppStatCount(SearchAppStatCriteria c, string action = "PV", int startNum = 1, int pageNum = 20)
{
Dictionary<string, long> result = new Dictionary<string, long>();
if (action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@" select App_No,Count from (select rownum rn,app_no,count from ( SELECT APP_NO, SUM(T.COUNT) COUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate AND T.STATISTIC_DATE <=:endDate ", action);
if (!string.IsNullOrEmpty(c.Firmware))
{
comm.AppendFormat(" and T.FIRMWARE_MODE = :firmware ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
comm.AppendFormat(" and T.MANUFACTURER = :pf ");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
comm.AppendFormat(" and T.COME_FROM = :from1 ");
}
if (pageNum == 0)
{
comm.AppendFormat("group by app_no ) )");
}
else
{
comm.AppendFormat("group by app_no ) ) where rn>=:startNum and rn<:endNum");
}
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);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
if (pageNum != 0)
{
queryCommand.AddParameter("startNum", startNum, DbType.Int32);
queryCommand.AddParameter("endNum", startNum + pageNum, DbType.Int32);
}
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)
{
long count = 0;
long.TryParse(row["COUNT"].ToString(), out count);
result.Add(row["APP_NO"].ToString(), count);
}
}
}
}
return result;
}
public int GetAllCountForPage(SearchAppStatCriteria c, string action = "PV", string orderByAction = "DOWN")
{
int count = 0;
if (action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
{
Dictionary<string, long> result = new Dictionary<string, long>();
//StringBuilder comm = new StringBuilder();
//comm.AppendFormat(@" SELECT COUNT (*) FROM ( SELECT app_no, SUM (COUNT) FROM yltest.stat_app_{0} GROUP BY app_no) a FULL ( SELECT app_no, SUM (COUNT) FROM yltest.stat_app_{1} GROUP BY app_no) b on a.app_no=b.app_no ",
// action,orderbyAction);
StringBuilder commPV = new StringBuilder();
StringBuilder commDown = new StringBuilder();
commPV.AppendFormat(@"SELECT APP_NO FROM yltest.stat_app_{0} WHERE STATISTIC_DATE >=:startDate2 AND STATISTIC_DATE <=:endDate2", action);
commDown.AppendFormat(@"SELECT APP_NO FROM YLTEST.STAT_APP_{0} WHERE STATISTIC_DATE >=:startDate2 AND STATISTIC_DATE <=:endDate2 ",
orderByAction);
if (!string.IsNullOrEmpty(c.Firmware))
{
commPV.AppendFormat(" and FIRMWARE_MODE = :firmware ");
commDown.AppendFormat(" and FIRMWARE_MODE = :firmware2 ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
commPV.AppendFormat(" and MANUFACTURER = :pf");
commDown.AppendFormat(" and MANUFACTURER = :pf2");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
commPV.AppendFormat(" and COME_FROM = :from1");
commDown.AppendFormat(" and COME_FROM = :from2");
}
commPV.AppendFormat(" group by APP_NO ");
commDown.AppendFormat(" group by APP_NO ");
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select count(app_no) count from ( ({0}) union ({1}) )", commPV.ToString(), commDown.ToString());
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);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
queryCommand.AddParameter("startDate2", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate2", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware2", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf2", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from2", c.From, DbType.String);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
int.TryParse(dt.Rows[0]["COUNT"].ToString(), out count);
}
}
}
return count;
}