protected void UploadButton_Click(object sender, EventArgs e)
{
int tenantId = (int)HttpContext.Current.Session["TenantID"];
if (ExcelFileUpload.HasFile)
{
using (var stream = ExcelFileUpload.PostedFile.InputStream)
{
IExcelDataReader excelReader = null;
try
{
if (ExcelFileUpload.FileName.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (ExcelFileUpload.FileName.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
this.Log().Info("Importing uploaded Excel school crosswalk: {0}",
ExcelFileUpload.FileName);
if (excelReader == null)
{
throw new Exception("Could not parse Excel file, not .xls or .xlsx");
}
excelReader.IsFirstRowAsColumnNames = true;
var schoolMap = new Dictionary <string, int>();
var schoolTypeMap = new Dictionary <string, int>();
var districtMap = new Dictionary <string, int>();
var codeTypes = DAL.CodeType.GetAll().Tables[0];
var schoolCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School'")
.First()["CTID"];
var schoolTypeCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School Type'")
.First()["CTID"];
var districtCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School District'")
.First()["CTID"];
var codes = DAL.Codes.GetAll().Tables[0];
foreach (DataRow codeRow in codes.Rows)
{
int ctid = (int)codeRow["CTID"];
if (ctid == schoolCodeTypeId)
{
schoolMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == schoolTypeCodeTypeId)
{
schoolTypeMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == districtCodeTypeId)
{
districtMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
}
int?recordCount = null;
var problems = new List <string>();
while (excelReader.Read())
{
// skip column headings
if (recordCount == null)
{
recordCount = 0;
continue;
}
try
{
string schoolName = excelReader.GetString(0);
int? schoolId = null;
if (schoolMap.ContainsKey(schoolName.Trim().ToLower()))
{
schoolId = schoolMap[schoolName.Trim().ToLower()];
}
string schoolTypeName = excelReader.GetString(1);
int? schoolTypeId = null;
if (schoolTypeMap.ContainsKey(schoolTypeName.Trim().ToLower()))
{
schoolTypeId = schoolTypeMap[schoolTypeName.Trim().ToLower()];
}
string district = excelReader.GetString(2);
int? districtId = null;
if (districtMap.ContainsKey(district.Trim().ToLower()))
{
districtId = districtMap[district.Trim().ToLower()];
}
int?minGrade = null;
int?maxGrade = null;
int?minAge = null;
int?maxAge = null;
if (excelReader.FieldCount >= 4)
{
// minGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(3), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 5)
{
// maxGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(4), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 6)
{
// minAge
int convertedValue;
if (int.TryParse(excelReader.GetString(5), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 7)
{
// maxAge
int convertedValue;
if (int.TryParse(excelReader.GetString(6), out convertedValue))
{
maxAge = convertedValue;
}
}
if (schoolId == null)
{
// insert branch
schoolId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolCodeTypeId,
Code = schoolName.Trim(),
Description = schoolName.Trim(),
TenID = tenantId
});
schoolMap.Add(schoolName.Trim().ToLower(), (int)schoolId);
}
if (schoolTypeId == null)
{
// insert branch
schoolTypeId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolTypeCodeTypeId,
Code = schoolTypeName.Trim(),
Description = schoolTypeName.Trim(),
TenID = tenantId
});
schoolTypeMap.Add(schoolTypeName.Trim().ToLower(), (int)schoolTypeId);
}
if (districtId == null)
{
// insert district
districtId = DAL.Codes.Insert(new DAL.Codes
{
CTID = districtCodeTypeId,
Code = district.Trim(),
Description = district.Trim(),
TenID = tenantId
});
districtMap.Add(district.Trim().ToLower(), (int)districtId);
}
// insert crosswalk
var crosswalk = new DAL.SchoolCrosswalk
{
SchoolID = (int)schoolId,
SchTypeID = (int)schoolTypeId,
DistrictID = (int)districtId,
City = string.Empty,
MinGrade = minGrade ?? 0,
MaxGrade = maxGrade ?? 0,
MinAge = minAge ?? 0,
MaxAge = maxAge ?? 0
};
DAL.SchoolCrosswalk.Insert(crosswalk);
recordCount++;
}
catch (Exception ex)
{
// couldn't import this record
string problem = string.Format("Unable to import record {0}: {1}",
recordCount,
ex.Message);
this.Log().Info(problem);
problems.Add(problem);
}
}
var result = new StringBuilder(string.Format("Imported {0} records and encountered {1} errors in: {2}",
recordCount,
problems.Count,
ExcelFileUpload.FileName));
this.Log().Info(result.ToString());
foreach (var problem in problems)
{
result.Append("<br />");
result.AppendLine(problem);
}
PageMessage = result.ToString();
}
catch (Exception ex)
{
string result = string.Format("Error reading Excel file for school crosswalk: {0} - {1}",
ex.Message,
ex.StackTrace);
this.Log().Error(result);
PageError = result;
}
finally
{
if (excelReader != null)
{
if (!excelReader.IsClosed)
{
excelReader.Close();
}
excelReader.Dispose();
}
}
}
}
odsData.DataBind();
gv.DataSourceID = "odsData";
gv.DataBind();
}
protected void UploadButton_Click(object sender, EventArgs e)
{
int tenantId = (int)HttpContext.Current.Session["TenantID"];
if (ExcelFileUpload.HasFile)
{
using (var stream = ExcelFileUpload.PostedFile.InputStream)
{
IExcelDataReader excelReader = null;
try
{
if (ExcelFileUpload.FileName.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (ExcelFileUpload.FileName.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
this.Log().Info("Importing uploaded Excel school crosswalk: {0}",
ExcelFileUpload.FileName);
if (excelReader == null)
{
throw new Exception("Could not parse Excel file, not .xls or .xlsx");
}
excelReader.IsFirstRowAsColumnNames = true;
var schoolMap = new Dictionary<string, int>();
var schoolTypeMap = new Dictionary<string, int>();
var districtMap = new Dictionary<string, int>();
var codeTypes = DAL.CodeType.GetAll().Tables[0];
var schoolCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School'")
.First()["CTID"];
var schoolTypeCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School Type'")
.First()["CTID"];
var districtCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School District'")
.First()["CTID"];
var codes = DAL.Codes.GetAll().Tables[0];
foreach (DataRow codeRow in codes.Rows)
{
int ctid = (int)codeRow["CTID"];
if (ctid == schoolCodeTypeId)
{
schoolMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == schoolTypeCodeTypeId)
{
schoolTypeMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == districtCodeTypeId)
{
districtMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
}
int? recordCount = null;
var problems = new List<string>();
while (excelReader.Read())
{
// skip column headings
if (recordCount == null)
{
recordCount = 0;
continue;
}
try
{
string schoolName = excelReader.GetString(0);
int? schoolId = null;
if (schoolMap.ContainsKey(schoolName.Trim().ToLower()))
{
schoolId = schoolMap[schoolName.Trim().ToLower()];
}
string schoolTypeName = excelReader.GetString(1);
int? schoolTypeId = null;
if (schoolTypeMap.ContainsKey(schoolTypeName.Trim().ToLower()))
{
schoolTypeId = schoolTypeMap[schoolTypeName.Trim().ToLower()];
}
string district = excelReader.GetString(2);
int? districtId = null;
if (districtMap.ContainsKey(district.Trim().ToLower()))
{
districtId = districtMap[district.Trim().ToLower()];
}
int? minGrade = null;
int? maxGrade = null;
int? minAge = null;
int? maxAge = null;
if (excelReader.FieldCount >= 4)
{
// minGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(3), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 5)
{
// maxGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(4), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 6)
{
// minAge
int convertedValue;
if (int.TryParse(excelReader.GetString(5), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 7)
{
// maxAge
int convertedValue;
if (int.TryParse(excelReader.GetString(6), out convertedValue))
{
maxAge = convertedValue;
}
}
if (schoolId == null)
{
// insert branch
schoolId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolCodeTypeId,
Code = schoolName.Trim(),
Description = schoolName.Trim(),
TenID = tenantId
});
schoolMap.Add(schoolName.Trim().ToLower(), (int)schoolId);
}
if (schoolTypeId == null)
{
// insert branch
schoolTypeId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolTypeCodeTypeId,
Code = schoolTypeName.Trim(),
Description = schoolTypeName.Trim(),
TenID = tenantId
});
schoolTypeMap.Add(schoolTypeName.Trim().ToLower(), (int)schoolTypeId);
}
if (districtId == null)
{
// insert district
districtId = DAL.Codes.Insert(new DAL.Codes
{
CTID = districtCodeTypeId,
Code = district.Trim(),
Description = district.Trim(),
TenID = tenantId
});
districtMap.Add(district.Trim().ToLower(), (int)districtId);
}
// insert crosswalk
var crosswalk = new DAL.SchoolCrosswalk
{
SchoolID = (int)schoolId,
SchTypeID = (int)schoolTypeId,
DistrictID = (int)districtId,
City = string.Empty,
MinGrade = minGrade ?? 0,
MaxGrade = maxGrade ?? 0,
MinAge = minAge ?? 0,
MaxAge = maxAge ?? 0
};
DAL.SchoolCrosswalk.Insert(crosswalk);
recordCount++;
}
catch (Exception ex)
{
// couldn't import this record
string problem = string.Format("Unable to import record {0}: {1}",
recordCount,
ex.Message);
this.Log().Info(problem);
problems.Add(problem);
}
}
var result = new StringBuilder(string.Format("Imported {0} records and encountered {1} errors in: {2}",
recordCount,
problems.Count,
ExcelFileUpload.FileName));
this.Log().Info(result.ToString());
foreach (var problem in problems)
{
result.Append("<br />");
result.AppendLine(problem);
}
PageMessage = result.ToString();
}
catch (Exception ex)
{
string result = string.Format("Error reading Excel file for school crosswalk: {0} - {1}",
ex.Message,
ex.StackTrace);
this.Log().Error(result);
PageError = result;
}
finally
{
if (excelReader != null)
{
if (!excelReader.IsClosed)
{
excelReader.Close();
}
excelReader.Dispose();
}
}
}
}
odsData.DataBind();
gv.DataSourceID = "odsData";
gv.DataBind();
}
