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