private bool UploadExcelFile(string strUploadCourseID, string strCourseCodeType)
{
try
{
string fileLocation = "";
fileLocation = AppDomain.CurrentDomain.BaseDirectory + "xls\\MultipleCourseUpload\\" + System.DateTime.Now.Ticks + uiFUPSpreadsheet.FileName;
//get the course id from query string
List <Course> courseList = new List <Course>();
//create the file loacton
FileStream oFileStream = File.Create(fileLocation);
foreach (byte b in uiFUPSpreadsheet.FileBytes)
{
oFileStream.WriteByte(b);
}
oFileStream.Close();
Course oCourse = null;
DataTable oDataTable = LACESUtilities.ReadXLFile(fileLocation, "Courses");
bool useNext = false;
ApprovedProvider provider = (ApprovedProvider)Session[LACESConstant.SessionKeys.LOGEDIN_APPROVED_PROVIDER]; // get approved provider information from Session
System.Text.StringBuilder sbResult = new System.Text.StringBuilder();
sbResult.Append("<br />");
int CourseUploadSuccess = 0;
int CourseUploadMoreInfo = 0;
int CourseUploadFailed = 0;
int currentRow = 0;
if (oDataTable != null)
{
foreach (DataRow dr in oDataTable.Rows)
{
oCourse = new Course();
string strCol1 = dr[0].ToString().Trim();
if (useNext)
{
string strCourseTitle = dr[0].ToString().Trim();
if (strCourseTitle.Length > 0)
{
oCourse.Title = strCourseTitle;
string strCourseRegUrl = dr[1].ToString().Trim();
oCourse.Hyperlink = strCourseRegUrl;
string strRegEligibility = dr[2].ToString().Trim();
oCourse.RegistrationEligibility = strRegEligibility;
string strStartDate = dr[3].ToString().Trim();
DateTime dtStart;
if (DateTime.TryParse(strStartDate, out dtStart))
{
oCourse.StartDate = dtStart;
}
else
{
oCourse.StartDate = DateTime.Parse("1/1/2010");
}
string strEndDate = dr[4].ToString().Trim();
DateTime dtEndDate;
if (DateTime.TryParse(strEndDate, out dtEndDate))
{
oCourse.EndDate = dtEndDate;
}
else
{
oCourse.EndDate = DateTime.Parse("1/1/2010");
}
string strDescription = dr[5].ToString().Trim();
oCourse.Description = strDescription;
string strCity = dr[6].ToString().Trim();
oCourse.City = strCity;
string strState = dr[7].ToString().Trim();
oCourse.StateProvince = strState;
string strDistanceEd = dr[8].ToString();
if (strDistanceEd.Length == 0)
{
oCourse.DistanceEducation = " ";
}
else
{
oCourse.DistanceEducation = strDistanceEd;
}
string strCourseEquivalency = dr[9].ToString();
if (strCourseEquivalency.Length == 0)
{
oCourse.CourseEquivalency = " ";
}
else
{
oCourse.CourseEquivalency = strCourseEquivalency;
}
string strSubjects = dr[10].ToString().Trim();
string strSubjects2 = dr[11].ToString().Trim();
if (strSubjects2.Length > 0)
{
strSubjects += ", " + strSubjects2;
}
oCourse.Subjects = strSubjects;
string strHealthSafety = dr[12].ToString().Trim();
oCourse.Healths = strHealthSafety;
string strPersonalDevelopmentHours = dr[13].ToString().Trim();
if (strPersonalDevelopmentHours.IndexOf(".") < 0)
{
strPersonalDevelopmentHours = strPersonalDevelopmentHours + ".00";
}
oCourse.Hours = strPersonalDevelopmentHours;
string strLearningOutcomes = dr[14].ToString().Trim();
oCourse.LearningOutcomes = strLearningOutcomes;
string strInstructors = dr[15].ToString().Trim();
oCourse.Instructors = strInstructors;
string strNoProprietary = dr[16].ToString().ToLower().Trim() == "yes" ? "Y" : "N";
oCourse.NoProprietaryInfo = strNoProprietary;
string strCourseCode = dr[17].ToString().Trim();
oCourse.ProviderID = provider.ID;
string strCourseErrors = LACESUtilities.CheckCourseValid(oCourse);
//oCourse.OrgStateCourseIDNumber = strCourseCode;
if (strCourseErrors.Length == 0)
{
oCourse.Status = "NP";
}
else
{
oCourse.Status = "IC";
}
oCourse.ProviderCustomCourseCode = strCourseCode;
CourseDataAccess cd = new CourseDataAccess();
long lngCourseID = -1;
try
{
if (long.TryParse(cd.AddCourseDetails(oCourse).ToString(), out lngCourseID))
{
try
{
SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["LACESConnectionString"].ToString());
conn2.Open();
DataSet ds = new DataSet();
string sqlStatement = "LACES_InsertMultiCourse_Courses";
SqlCommand cmd = new SqlCommand(sqlStatement, conn2);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MultiCourseID", strUploadCourseID);
cmd.Parameters.AddWithValue("@CourseID", lngCourseID.ToString());
cmd.ExecuteNonQuery();
conn2.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
Response.End();
}
// if (strCourseCode.Trim().Length > 0)
// {
// CourseCodeDataAccess codeDAL = new CourseCodeDataAccess();
// CourseCode code = new CourseCode();
// long CodeID = GetCodeIDfromName(strCourseCodeType.Trim());
// if (CodeID > 0)
// {
// //code.Description = descList[i];
// code.CodeID = Convert.ToInt32(CodeID);
// code.CourseID = lngCourseID;
// code.Description = strCourseCode.Trim();
// try
// {
// codeDAL.AddCourseCode(code); // Add course Code
// }
// catch (Exception ex)
// {
// uiLblResults.Text = ex.Message;
// Response.End();
// }
// }
// else
// {
// CourseTypeDataAccess courseTypeDAL = new CourseTypeDataAccess();
// int codeID = courseTypeDAL.AddCodeType(strCourseCodeType.Trim());
// code.CodeID = codeID;
// code.CourseID = lngCourseID;
// code.Description = strCourseCode.Trim();
// try
// {
// codeDAL.AddCourseCode(code); // Add course Code
// }
// catch (Exception ex)
// {
// uiLblResults.Text = ex.Message;
// Response.End();
// }
// }
// }
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
Response.End();
}
if (strCourseErrors.Length == 0)
{
CourseUploadSuccess++;
sbResult.Append("<span style=\"color:green\">SUCCESS </span>" + oCourse.Title + "<hr style=\"background-image: url(http://laces.asla.org/images/dotted.png); background-repeat: repeat-x; border: 0; height: 1px;\" />");
}
else
{
CourseUploadMoreInfo++;
sbResult.Append("<span style=\"color:orange\">ACTION REQUIRED</span> <a href=\"http://laces.asla.org/provider/CourseDetails.aspx?CourseID=" + lngCourseID.ToString() + "\" class=\"nostyle\">" + oCourse.Title + "</a><br />" + strCourseErrors + "<hr style=\"background-image: url(http://laces.asla.org/images/dotted.png); background-repeat: repeat-x; border: 0; height: 1px;\"/>");
}
}
else
{
DataRow drN = oDataTable.Rows[currentRow];
bool endprocess = true;
for (int i = 0; i < 17; i++)
{
if (drN[i].ToString().Trim().Length > 0)
{
CourseUploadFailed++;
sbResult.Append("<span style=\"color:red\"> ERROR: Excel Row " + (currentRow + 2).ToString() + " was missing a title which is required to begin processing</span><br /><hr style=\"background-image: url(http://laces.asla.org/images/dotted.png); background-repeat: repeat-x; border: 0; height: 1px;\"/>");
endprocess = false;
break;
}
}
if (endprocess)
{
DataRow drN1 = oDataTable.Rows[currentRow + 1];
if (drN1[0].ToString().Trim().Length == 0)
{
break;
}
}
}
}
if (strCol1 == "*Course Title")
{
useNext = true;
}
currentRow++;
//long newCourse = cd.AddCourseDetails(oCourse);
}
if (useNext && (CourseUploadMoreInfo + CourseUploadSuccess > 0))
{
string strMultiName = uiTxtMultiName.Text;
string strConfStartDate = txtStartDate.Text;
string strConfEndDate = txtEndDate.Text;
System.Text.StringBuilder Summary = new System.Text.StringBuilder();
Summary.Append("<br /><br /><br /><br /><strong>Thank you for your submission.</strong><br />");
Summary.Append("<br /><strong>Event Title: </strong> " + strMultiName + "<br />");
Summary.Append("<br /><strong>Start Date: </strong> " + strConfStartDate + "<br />");
Summary.Append("<br /><strong>End Date: </strong>" + strConfEndDate + "<br />");
Summary.Append("<br />" + (CourseUploadMoreInfo + CourseUploadSuccess).ToString() + " courses have been received.<br />");
Summary.Append(CourseUploadSuccess.ToString() + " courses are complete and pending approval by LA CES administrators.<br />");
Summary.Append(CourseUploadMoreInfo.ToString() + " courses are incomplete and require immediate attention.<br />");
if (CourseUploadFailed > 0)
{
Summary.Append(CourseUploadFailed.ToString() + " courses failed to upload and need to be resubmitted.<br />");
}
Summary.Append("<br />Please review the list below or go to <a href=\"http://laces.asla.org/provider/CourseList.aspx?status=NP\">Pending Courses</a> to review your individual course details. All pending courses that require additional information will be listed with a status of \"Action Required\".");
Summary.Append("<br /><br /><br />An email with this information will be sent to the primary contact for your account.");
Summary.Append("<br /><br /><br />Sincerely,");
Summary.Append("<br /><br /><br />LA CES Administrator");
uiLblResults.Text = Summary.ToString() + "<hr style=\"background-image: url(http://laces.asla.org/images/images/dotted.png); background-repeat: repeat-x; border: 0; height: 2px;\" />Upload Status – Course Title<br />" + sbResult.ToString();
SendEmail(Summary.ToString() + "<hr style=\"background-image: url(http://laces.asla.org/images/dotted.png); background-repeat: repeat-x; border: 0; height: 2px;\" />Upload Status– Course Title<br />" + sbResult.ToString(), provider.ApplicantEmail);
}
else
{
BuildExcelError();
SendEmail(uiLblResults.Text, provider.ApplicantEmail);
}
}
else
{
BuildExcelError();
}
}
catch (Exception ex)
{
BuildExcelError();
}
return(false);
}
private bool UploadExcelFile()
{
try
{
//get the course id from query string
List <Course> courseList = new List <Course>();
//create the file loacton
FileStream oFileStream = File.Create(fileLocation);
foreach (byte b in FileUpload1.FileBytes)
{
oFileStream.WriteByte(b);
}
oFileStream.Close();
Course oCourse = null;
DataTable oDataTable = LACESUtilities.ReadXLFile(fileLocation, "Courses");
bool useNext = false;
ApprovedProvider provider = (ApprovedProvider)Session[LACESConstant.SessionKeys.LOGEDIN_APPROVED_PROVIDER]; // get approved provider information from Session
System.Text.StringBuilder sbResult = new System.Text.StringBuilder();
foreach (DataRow dr in oDataTable.Rows)
{
oCourse = new Course();
string strCol1 = dr[0].ToString().Trim();
if (useNext)
{
bool isAllRequiredFilled = true;
string strCourseTitle = dr[0].ToString().Trim();
oCourse.Title = strCourseTitle;
string strCourseRegUrl = dr[1].ToString().Trim();
oCourse.Hyperlink = strCourseRegUrl;
string strRegEligibility = dr[2].ToString().Trim();
oCourse.RegistrationEligibility = strRegEligibility;
string strStartDate = dr[3].ToString().Trim();
DateTime dtStart;
if (DateTime.TryParse(strStartDate, out dtStart))
{
oCourse.StartDate = dtStart;
}
else
{
oCourse.StartDate = (DateTime)SqlDateTime.MinValue;
}
string strEndDate = dr[4].ToString().Trim();
DateTime dtEndDate;
if (DateTime.TryParse(strEndDate, out dtEndDate))
{
oCourse.EndDate = dtStart;
}
else
{
oCourse.EndDate = (DateTime)SqlDateTime.MinValue;
}
string strDescription = dr[5].ToString().Trim();
oCourse.Description = strDescription;
string strCity = dr[6].ToString().Trim();
oCourse.City = strCity;
string strState = dr[7].ToString().Trim();
oCourse.StateProvince = strStartDate;
string strDistanceEd = dr[8].ToString();
if (strDistanceEd.Length == 0)
{
oCourse.DistanceEducation = " ";
}
else
{
oCourse.DistanceEducation = strDistanceEd;
}
string strCourseEquivalency = dr[9].ToString();
if (strCourseEquivalency.Length == 0)
{
oCourse.CourseEquivalency = " ";
}
else
{
oCourse.CourseEquivalency = strCourseEquivalency;
}
string strSubjects = dr[10].ToString().Trim();
oCourse.Subjects = strSubjects;
string strHealthSafety = dr[11].ToString().Trim();
oCourse.Healths = strHealthSafety;
string strPersonalDevelopmentHours = dr[12].ToString().Trim();
oCourse.Hours = strPersonalDevelopmentHours;
string strLearningOutcomes = dr[13].ToString().Trim();
oCourse.LearningOutcomes = strLearningOutcomes;
string strInstructors = dr[14].ToString().Trim();
oCourse.Instructors = strInstructors;
string strNoProprietary = dr[15].ToString().Trim();
string strCourseCode = dr[16].ToString().Trim();
oCourse.ProviderID = provider.ID;
string strCourseErrors = LACESUtilities.CheckCourseValid(oCourse);
//oCourse.OrgStateCourseIDNumber = strCourseCode;
if (strCourseErrors.Length == 0)
{
oCourse.Status = "NP";
}
else
{
oCourse.Status = "IC";
}
CourseDataAccess cd = new CourseDataAccess();
long lngCourseID;
if (long.TryParse(cd.AddCourseDetails(oCourse).ToString(), out lngCourseID))
{
if (strCourseCode.Trim().Length > 0)
{
CourseCodeDataAccess codeDAL = new CourseCodeDataAccess();
CourseCode code = new CourseCode();
//code.Description = descList[i];
code.CodeID = Convert.ToInt32(strCourseCode);
code.CourseID = lngCourseID;
try
{
codeDAL.AddCourseCode(code); // Add course Code
}
catch (Exception ex)
{
uiLblResults.Text = ex.Message;
Response.End();
}
}
}
if (strCourseErrors.Length == 0)
{
sbResult.Append("SUCCESS" + lngCourseID.ToString() + " - " + oCourse.Title);
}
else
{
sbResult.Append(lngCourseID.ToString() + " - " + oCourse.Title + "<br />" + strCourseErrors);
}
}
if (strCol1 == "*Course Title")
{
useNext = true;
}
//long newCourse = cd.AddCourseDetails(oCourse);
}
uiLblResults.Text = sbResult.ToString();
}
catch (Exception ex)
{
Response.Write(ex.Message);
Response.End();
}
return(false);
}
