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);
}
/// <summary>
/// Uplaod an Excel file
/// </summary>
private bool UploadExcelFile()
{
string staticErrorMessage = "There has been an error with your upload. Please ensure you use our template to upload attendees.<br/> ";
lb_error_message.Style["color"] = "red";
bool blnShowGetNewExcel = false;
//check the file extension, if not .xls file then show error
if (!FileUpload1.FileName.EndsWith(".xls") && !FileUpload1.FileName.EndsWith(".xlsx"))
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=1";// "Your provided file type is not supported.";
return(false);
}
//prepare the file path
fileLocation = AppDomain.CurrentDomain.BaseDirectory + "xls\\" + System.DateTime.Now.Ticks + FileUpload1.FileName;
try
{
//get the course id from query string
long courseID = 0;
List <Participant> participantList = new List <Participant>();
//create the file loacton
FileStream oFileStream = File.Create(fileLocation);
foreach (byte b in FileUpload1.FileBytes)
{
oFileStream.WriteByte(b);
}
oFileStream.Close();
Participant oParticipant = null;
//Debugger.Break();
//now load it to UI
if (Request.QueryString["courseid"] != null)
{
try
{
courseID = Convert.ToInt32(Request.QueryString["courseid"]);
}
catch
{
courseID = 0;
}
//no course found
if (courseID == 0)
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=2";//"Course ID is invalid/not exists.";
return(false);
}
//get the current login provider
ApprovedProvider provider = (ApprovedProvider)Session[LACESConstant.SessionKeys.LOGEDIN_APPROVED_PROVIDER];
//check is the course available or not
CourseDataAccess oCourseDataAccess = new CourseDataAccess();
if (oCourseDataAccess.GetCoursesDetailsByIDandProviderID(courseID, provider.ID) == null)
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=3";// "Course ID is invalid/not exists.";
return(false);
}
}
else
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=4";//"Course ID is invalid.";
return(false);
}
DataTable oDataTable = LACESUtilities.ReadXLFile(fileLocation, "Attendees");
// Response.Write(fileLocation);
// Response.End();
if (oDataTable == null)
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=5";// "Attendees' information does not match with the template.";
return(false);
}
foreach (DataRow dr in oDataTable.Rows)
{
oParticipant = new Participant();
string last_name = dr["Last"].ToString().Trim();
string first_name = dr["First"].ToString().Trim();
string middle_name = dr["Middle"].ToString().Trim();
string asla_number = dr["ASLA"].ToString().Trim();
string clarb_number = dr["CLARB"].ToString().Trim();
string csla_name = dr["CSLA"].ToString().Trim();
string florida_number = dr["FL"].ToString().Trim();
string email = "";
if (dr.Table.Columns.Contains("email"))
{
email = dr["email"].ToString().Trim();
}
else
{
blnShowGetNewExcel = true;
}
//if (last_name == "" && first_name == "")
//{
// lb_error_message.InnerHtml = staticErrorMessage + "<br />location=6";//"In your uploaded file, one or more first name/last name are //empty.";
// return false;
//}
//if any field is empty then exit the process
if (last_name != "" && first_name != "")// && asla_number != "" && clarb_number != "" && florida_number != "")
{
oParticipant.LastName = last_name;
oParticipant.FirstName = first_name;
oParticipant.MiddleInitial = middle_name;
oParticipant.ASLANumber = asla_number;
oParticipant.CLARBNumber = clarb_number;
oParticipant.CSLANumber = csla_name;
oParticipant.FloridaStateNumber = florida_number;
oParticipant.Email = email;
//add in to the participant list
participantList.Add(oParticipant);
}
else if (csla_name == "" && middle_name == "" && last_name == "" && first_name == "" && asla_number == "" && clarb_number == "" && florida_number == "")
{
//if empty row found then discard the row
}
else
{
//some empty field found. so inform the error
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=7";// "Attendees' information does not match with the template.";
return(false);
}
}
if (participantList.Count > 0)
{
//finally add the participant in to the database
ParticipantDataAccess oParticipantDataAccess = new ParticipantDataAccess();
oParticipantDataAccess.AddParticipantByCourse(courseID, participantList);
}
//finally remove the excel file
LACESUtilities.RemoveCreatedFileFromDisk(fileLocation);
if (blnShowGetNewExcel)
{
lb_error_message.InnerHtml = "ALERT: Please download new Excel Template that now captures attendee email. This did not prevent your current attendee file from being successfully uploaded.<br/> ";
lb_error_message.Style["color"] = "red";
}
else
{
lb_error_message.InnerHtml = "Thank you. Your attendee file has been successfully uploaded.<br/> ";
lb_error_message.Style["color"] = "green";
}
//Response.Redirect("../Provider/ManageAttendees.aspx?courseid=" + courseID);
}
catch (Exception ex)
{
lb_error_message.InnerHtml = staticErrorMessage + "<br />location=1" + "<br />" + ex.Message;//"There has been an error with your upload. Please ensure you use our template to upload attendees.";
if (File.Exists(fileLocation))
{
File.Delete(fileLocation);
}
}
return(true);
}
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);
}
