private string getFilterQuery(CampusFilter campusFilter)
{
bool first = true;
string separator = ",";
string subjectGrades = string.Empty;
string level = string.Empty;
if (campusFilter.Level.Length > 0)
{
level = string.Join(",", campusFilter.Level);
level = separator + level;
}
if (campusFilter.Grades.Length > 0)
{
subjectGrades = " AND (";
foreach (var item in campusFilter.Grades)
{
if (!first)
{
subjectGrades = subjectGrades + " OR ";
}
subjectGrades = subjectGrades + " ( " + " SUBJECT = " + "'" + item.Subject + "'" + " AND GRADE = " + "'" + item.Grade + "'" + " ) ";
first = false;
}
subjectGrades = subjectGrades + " ) ";
}
string query = "SELECT DNAME,CNAME,YEAR, DEMO, SUBJECT, GRADE, LANGUAGE " + level + " FROM [dbo].[staar_campus] WHERE CAMPUS IN (@campus) AND YEAR IN (@year) AND DEMO IN (@demo) " + subjectGrades;
return query;
}
public string ExportFilterData(CampusFilter campusFilter)
{
DataTable dt = new DataTable();
string currentConnection = ConfigurationManager.ConnectionStrings["staar"].ConnectionString;
using (SqlConnection connection = new SqlConnection(currentConnection))
{
string query = getFilterQuery(campusFilter);
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
command.AddArrayParameters(campusFilter.Demographic, "demo");
command.AddArrayParameters(campusFilter.Campus, "campus");
command.AddArrayParameters(campusFilter.Year, "year");
SqlDataReader reader = command.ExecuteReader();
dt.Load(reader);
connection.Close();
}
dt.TableName = "Table1Name";
var fileName = getFileName();
ExcelExportHelper.GenerteExcel(dt, getHeaderMapping(), Path.GetTempPath() + fileName);
return fileName;
}
public List<Dictionary<string, object>> GetCampusReport(CampusFilter campusFilter)
{
List<Dictionary<string, object>> campusResult;
string currentConnection = ConfigurationManager.ConnectionStrings["staar"].ConnectionString;
using (SqlConnection connection = new SqlConnection(currentConnection))
{
string query = getFilterQuery(campusFilter);
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
command.AddArrayParameters(campusFilter.Demographic, "demo");
command.AddArrayParameters(campusFilter.Campus, "campus");
command.AddArrayParameters(campusFilter.Year, "year");
SqlDataReader reader = command.ExecuteReader();
campusResult = reader.GetAllRecords();
connection.Close();
}
return campusResult;
}
