public DataTable CaculateReport(DateTime reportDate, DayReportType dayReportType)
{
string sql = "Select [ChannelID],[ClientID],[CodeID],Sum([Count]) as RecordCount from SPRecord with(nolock) where CreateDate >= @startDate and CreateDate < @endDate";
sql += BuildDayReportTypeFilterSql(dayReportType);
sql += " group by [ChannelID],[ClientID],[CodeID]";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("startDate", reportDate.Date);
dbParameters.AddWithValue("endDate", reportDate.Date.AddDays(1));
return(this.ExecuteDataSet(sql, CommandType.Text, dbParameters).Tables[0]);
}
private static void SetReportTypeFilter(NHibernateDynamicQueryGenerator<SPRecordEntity> queryBuilder, DayReportType reportType)
{
switch (reportType)
{
case DayReportType.AllUp:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
break;
case DayReportType.AllUpSuccess:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
break;
case DayReportType.Intercept:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(true));
break;
case DayReportType.Down:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
break;
case DayReportType.DownNotSycn:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(false));
break;
case DayReportType.DownSycnSuccess:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNSUCCESSED.Eq(true));
break;
case DayReportType.DownSycnFailed:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNSUCCESSED.Eq(false));
break;
default:
break;
}
}
private NHibernateDynamicQueryGenerator <SPRecordEntity> BuilderQueryGenerator(SPChannelEntity channel, SPCodeEntity code,
SPSClientEntity client, string dataType,
DateTime?startDate, DateTime?endDate, List <QueryFilter> filters,
string orderByColumnName, bool isDesc)
{
var queryBuilder = new NHibernateDynamicQueryGenerator <SPRecordEntity>();
if (channel != null)
{
queryBuilder.AddWhereClause(PROPERTY_CHANNELID.Eq(channel));
}
if (code != null)
{
queryBuilder.AddWhereClause(PROPERTY_CODEID.Eq(code));
}
if (client != null)
{
queryBuilder.AddWhereClause(PROPERTY_CLIENTID.Eq(client));
}
DayReportType reportType = (DayReportType)Enum.Parse(typeof(DayReportType), dataType);
SetReportTypeFilter(queryBuilder, reportType);
if (startDate != null)
{
queryBuilder.AddWhereClause(PROPERTY_CREATEDATE.Ge(startDate.Value.Date));
}
if (endDate != null)
{
queryBuilder.AddWhereClause(PROPERTY_CREATEDATE.Lt(endDate.Value.Date.AddDays(1)));
}
AddQueryFiltersToQueryGenerator(filters, queryBuilder);
AddDefaultOrderByToQueryGenerator(orderByColumnName, isDesc, queryBuilder);
return(queryBuilder);
}
private static string BuildDayReportTypeFilterSql(DayReportType dayReportType)
{
string filterSql = "";
switch (dayReportType)
{
case DayReportType.AllUp:
filterSql += " AND IsReport =0";
break;
case DayReportType.AllUpSuccess:
filterSql += " AND IsReport =0 AND IsStatOK = 1 ";
break;
case DayReportType.Intercept:
filterSql += " AND IsReport =0 AND IsStatOK = 1 AND IsIntercept = 1 ";
break;
case DayReportType.Down:
filterSql += " AND IsReport =0 AND IsStatOK = 1 AND IsIntercept = 0 ";
break;
case DayReportType.DownNotSycn:
filterSql += " AND IsReport =0 AND IsStatOK = 1 AND IsIntercept = 0 AND IsSycnToClient = 0 ";
break;
case DayReportType.DownSycnSuccess:
filterSql += " AND IsReport =0 AND IsStatOK = 1 AND IsIntercept = 0 AND IsSycnToClient = 1 AND IsSycnSuccessed = 1 ";
break;
case DayReportType.DownSycnFailed:
filterSql += " AND IsReport =0 AND IsStatOK = 1 AND IsIntercept = 0 AND IsSycnToClient = 1 AND IsSycnSuccessed = 0 ";
break;
}
return(filterSql);
}
public DataSet QueryRecordProvine(DateTime?startDate, DateTime?endDate, DayReportType dayReportType, int?channelId, int?codeID, int?clientID)
{
return(AdoNetDb.QueryRecordProvine(startDate, endDate, dayReportType, channelId, codeID, clientID));
}
public DataSet QueryRecordProvine(DateTime?startDate, DateTime?endDate, DayReportType dayReportType, int?channelId, int?codeID, int?clientID)
{
string sql = "SELECT [Province] ,count(*) as ProvinceCount FROM [dbo].[SPRecord] with(nolock) {0} group by [Province]";
string where = " Where 1=1 ";
where += BuildDayReportTypeFilterSql(dayReportType);
if (startDate.HasValue)
{
where += " and CreateDate>=@startDate ";
}
if (endDate.HasValue)
{
where += " and CreateDate<@endDate ";
}
if (channelId.HasValue)
{
where += " and ChannelID=@ChannelId ";
}
if (codeID.HasValue)
{
where += " and CodeID=@CodeID ";
}
if (clientID.HasValue)
{
where += " and ClientID=@ClientID ";
}
DbParameters dbParameters = this.CreateNewDbParameters();
if (startDate.HasValue)
{
dbParameters.AddWithValue("startDate", startDate.Value.Date);
}
if (endDate.HasValue)
{
dbParameters.AddWithValue("endDate", endDate.Value.Date.AddDays(1));
}
if (channelId.HasValue)
{
dbParameters.AddWithValue("ChannelId", channelId.Value);
}
if (codeID.HasValue)
{
dbParameters.AddWithValue("CodeID", codeID.Value);
}
if (clientID.HasValue)
{
dbParameters.AddWithValue("ClientID", clientID.Value);
}
return(this.ExecuteDataSet(string.Format(sql, where), CommandType.Text, dbParameters));
}
private static void SetReportTypeFilter(NHibernateDynamicQueryGenerator <SPRecordEntity> queryBuilder, DayReportType reportType)
{
switch (reportType)
{
case DayReportType.AllUp:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
break;
case DayReportType.AllUpSuccess:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
break;
case DayReportType.Intercept:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(true));
break;
case DayReportType.Down:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
break;
case DayReportType.DownNotSycn:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(false));
break;
case DayReportType.DownSycnSuccess:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNSUCCESSED.Eq(true));
break;
case DayReportType.DownSycnFailed:
//queryBuilder.AddWhereClause(PROPERTY_ISREPORT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSTATOK.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISINTERCEPT.Eq(false));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNTOCLIENT.Eq(true));
queryBuilder.AddWhereClause(PROPERTY_ISSYCNSUCCESSED.Eq(false));
break;
default:
break;
}
}
public static DataSet QueryRecordProvine(DateTime?startDate, DateTime?endDate, string dayReportType, int?channelId, int?codeID, int?clientID)
{
DayReportType reportType = (DayReportType)Enum.Parse(typeof(DayReportType), dayReportType);
return(businessProxy.QueryRecordProvine(startDate, endDate, reportType, channelId, codeID, clientID));
}
