public ReportHierarchy GetGrowUpReports(DateTime date) { var db = DatabaseFactory.CreateDatabase(); DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(@" SELECT gradeName, className, stuNumber, recorderName, [type], recordTime, category, item, content, picUrl, stuName FROM GrowUpReportHistory INNER JOIN GrowUpReportDetail ON idx = reportHistoryID WHERE DATEDIFF(mm, @date, recordTime) = 0 ORDER BY stuNumber, category, recordTime"); dbCommandWrapper.AddInParameter("@date", DbType.DateTime, date); var reader = db.ExecuteReader(dbCommandWrapper); var hierarchy = ReportHierarchy.Create(); while (reader.Read()) { hierarchy.AddReport(new Report { GradeName = reader.GetString(0), ClassName = reader.GetString(1), StuNumber = reader.GetInt32(2), RecorderName = reader.IsDBNull(3) ? string.Empty : reader.GetString(3), EditBy = reader.GetInt32(4) == 2 ? EditBy.Parent : EditBy.Teacher, Date = new DateTime(reader.GetDateTime(5).Year, reader.GetDateTime(5).Month, 1), Category = reader.GetString(6), Item = reader.IsDBNull(7) ? string.Empty : reader.GetString(7), Content = reader.IsDBNull(8) ? string.Empty : reader.GetString(8), PicUrl = reader.IsDBNull(9) ? string.Empty : reader.GetString(9), StuName = reader.GetString(10) }); } return(hierarchy); }
public ReportHierarchy GetGrowUpReports(DateTime[] dates) { var db = DatabaseFactory.CreateDatabase(); var dateWhere = string.Empty; foreach (var date in dates) { dateWhere += string.Format(" DATEDIFF(mm, recordTime, '{0}') = 0 OR", date.ToString("yyyy-MM-dd")); } dateWhere = dateWhere.TrimEnd(new char[] { 'O', 'R' }); DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(@" SELECT gradeName, className, stuNumber, recorderName, [type], recordTime, category, item, content, picUrl, stuName, rawUrl FROM GrowUpReportHistory INNER JOIN GrowUpReportDetail ON idx = reportHistoryID WHERE " + dateWhere + "ORDER BY stuNumber, category, recordTime"); var reader = db.ExecuteReader(dbCommandWrapper); var hierarchy = ReportHierarchy.Create(); while (reader.Read()) { hierarchy.AddReport(new Report { GradeName = reader.GetString(0), ClassName = reader.GetString(1), StuNumber = reader.GetInt32(2), RecorderName = reader.IsDBNull(3) ? string.Empty : reader.GetString(3), EditBy = reader.GetInt32(4) == 2 ? EditBy.Parent : EditBy.Teacher, Date = new DateTime(reader.GetDateTime(5).Year, reader.GetDateTime(5).Month, 1), Category = reader.GetString(6), Item = reader.IsDBNull(7) ? string.Empty : reader.GetString(7), Content = reader.IsDBNull(8) ? string.Empty : reader.GetString(8), PicUrl = reader.IsDBNull(9) ? string.Empty : reader.GetString(9), StuName = reader.GetString(10), RawUrl = reader.IsDBNull(11) ? string.Empty : reader.GetString(11) }); } return(hierarchy); }