public List <FeedbackQuestionDTO> GetAllQuestions()
{
try
{
FeedBackQuestionRepository feedBackQuestionRepository = new FeedBackQuestionRepository();
List <FeedbackQuestion> questions = feedBackQuestionRepository.GetQuestions();
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <FeedbackQuestionDTO, FeedbackQuestion>();
});
IMapper iMapper = config.CreateMapper();
return(iMapper.Map <List <FeedbackQuestion>, List <FeedbackQuestionDTO> >(questions));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "Question",
ActionrName = "GetAllQuestions()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public UnRegisteredVolunteerFeedbackDTO GetUnregisterVolunteerFeedback(string eventId, string employeeId)
{
try
{
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap<UnRegisteredVolunteerFeedbackDTO, UnRegisteredVolunteerFeedback>();
});
IMapper iMapper = config.CreateMapper();
return iMapper.Map<UnRegisteredVolunteerFeedback, UnRegisteredVolunteerFeedbackDTO>(unRegisteredVolunteerFeedbackRepository.GetUnRegisteredVolunteerFeedback(eventId, employeeId));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "Unregistered",
ActionrName = "GetUnregisterVolunteerFeedback()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public void SaveRegisterVolunteerFeedback(List <RegisteredVolunteerFeedbackDTO> feedbackList)
{
foreach (RegisteredVolunteerFeedbackDTO registeredVolunteerFeedbackDTO in feedbackList)
{
try
{
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <RegisteredVolunteerFeedbackDTO, RegisteredVolunteerFeedback>().ForMember(dest => dest.Event, opt => opt.AllowNull());
});
IMapper iMapper = config.CreateMapper();
RegisteredVolunteerFeedbackRepository.SaveRegisteredVolunteerFeedback(iMapper.Map <RegisteredVolunteerFeedbackDTO, RegisteredVolunteerFeedback>(registeredVolunteerFeedbackDTO));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "Registered",
ActionrName = "SaveRegisterVolunteerFeedback()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
}
public List <UserDTO> GetUserListByAssociateID(string AssociateID)
{
try
{
List <UserDTO> userDTO = userRepository.GetUserListByAssociateID(AssociateID).Select(x => new UserDTO
{
AssociateID = x.AssociateID,
AssociateName = x.AssociateName,
RoleID = x.RoleID,
EventId = x.EventId
}).ToList();
return(userDTO);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "UserAndRole",
ActionrName = "GetUserListByAssociateID()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public List <UserMenuDTO> GetUserMenu(int RoleId)
{
try
{
List <UserMenuDTO> _menus = RoleMenuRepository.GetMenuListByRoleId(RoleId).Select(x => new UserMenuDTO
{
MainMenuId = x.SubMenu.MainMenu.MainMenuId,
MainMenuName = x.SubMenu.MainMenu.MainMenuName,
SubMenuId = x.Id,
SubMenuName = x.SubMenu.SubMenuName,
ControllerName = x.SubMenu.ControllerName,
ActionName = x.SubMenu.ActionrName,
RoleID = x.RoleID,
RoleName = x.Role.RoleName
}).ToList();
return(_menus);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "UserAndRole",
ActionrName = "GetUserMenu()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public NotAttendedVolunteerFeedbackDTO GetNotAttendedFeedback(string eventId, string employeeId)
{
try
{
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <NotAttendedVolunteerFeedback, NotAttendedVolunteerFeedbackDTO>().ForMember(mem => mem.EventDate, opt => opt.Ignore());
});
IMapper iMapper = config.CreateMapper();
return(iMapper.Map <NotAttendedVolunteerFeedback, NotAttendedVolunteerFeedbackDTO>(notAttendedVolunteerFeedbackRepository.GetNotAttendedVolunteerFeedback(eventId, employeeId)));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "NotParticipated",
ActionrName = "GetNotAttendedFeedback()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public void SaveNotAttendedVolunteerFeedback(NotAttendedVolunteerFeedbackDTO notAttendedVolunteerFeedbackDTO)
{
try
{
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <NotAttendedVolunteerFeedbackDTO, NotAttendedVolunteerFeedback>().ForMember(dest => dest.Event, opt => opt.AllowNull());
});
IMapper iMapper = config.CreateMapper();
notAttendedVolunteerFeedbackRepository.SaveNotAttendedVolunteerFeedback(iMapper.Map <NotAttendedVolunteerFeedbackDTO, NotAttendedVolunteerFeedback>(notAttendedVolunteerFeedbackDTO));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "NotParticipated",
ActionrName = "SaveNotAttendedVolunteerFeedback()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public EventDTO GetEventById(string EventID)
{
try
{
EventRepository eventRepository = new EventRepository();
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <EventDTO, Event>();
});
IMapper iMapper = config.CreateMapper();
return(iMapper.Map <Event, EventDTO>(eventRepository.FindEvent(EventID)));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "OutReachEvent",
ActionrName = "GetEventById()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public List <EventDTO> GetAllEvents()
{
try
{
EventRepository eventRepository = new EventRepository();
List <Event> events = eventRepository.GetEventList();
var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap <EventDTO, Event>();
});
IMapper iMapper = config.CreateMapper();
return(iMapper.Map <List <Event>, List <EventDTO> >(events));
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "OutReachEvent",
ActionrName = "GetAllEvents()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public void ReadExcelIntoDatatable(string excelPath)
{
try
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelPath, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows)
{
DataRow tempRow = dt.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i));
}
dt.Rows.Add(tempRow);
}
}
dt.Rows.RemoveAt(0);
ExcelToDB excelToDB = new ExcelToDB();
int q = excelPath.LastIndexOf('\\') + 1;
excelToDB.FindTableName(excelPath.Substring(q), dt);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "ExportExcel",
ActionrName = "ReadExcelIntoDatatable",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
private void InsertUnregisteredUserDetails(DataTable unregistereduserTable)
{
try
{
foreach (DataRow row in unregistereduserTable.Rows)
{
UnRegisteredVolunteer unRegisteredVolunteer = new UnRegisteredVolunteer();
unRegisteredVolunteer.EventId = row["Event ID"].ToString();
if (!row.IsNull("EmployeeID"))
{
unRegisteredVolunteer.EmployeeID = row["EmployeeID"].ToString();
}
if (!row.IsNull("Event Name"))
{
unRegisteredVolunteer.EventName = row["Event Name"].ToString();
}
if (!row.IsNull("Beneficiary Name"))
{
unRegisteredVolunteer.BeneficaryName = row["Beneficiary Name"].ToString();
}
if (!row.IsNull("Base Location"))
{
unRegisteredVolunteer.Location = row["Base Location"].ToString();
}
if (!row.IsNull("Event Date (DD-MM-YY)"))
{
String[] date = row["Event Date (DD-MM-YY)"].ToString().Split(new[] { '-' });
int day = Convert.ToInt32(date[0]);
int month = Convert.ToInt32(date[1]);
int a = 20;
int year = int.Parse(a.ToString() + date[2].ToString());
DateTime eventDate = new DateTime(year, month, day);
unRegisteredVolunteer.EventDate = eventDate;
}
UnRegisteredVolunteerRepository unRegisteredVolunteerRepository = new UnRegisteredVolunteerRepository();
unRegisteredVolunteerRepository.AddUnRegisteredVolunteer(unRegisteredVolunteer);
}
outreachMail.SendMailToUnregisteredUser(unregistereduserTable);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "ExcelToDB",
ActionrName = "InsertUnregisteredUserDetails",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
}
}
public void SendMailToParticipatedUser(DataTable particaipateduserTable)
{
try
{
RegisteredVolunteerRepository registeredVolunteerRepository = new RegisteredVolunteerRepository();
foreach (DataRow row in particaipateduserTable.Rows)
{
string body = "";
string subject = "";
if (!row.IsNull("Employee ID") && !row.IsNull("Event ID"))
{
RegisteredVolunteer registeredVolunteer = registeredVolunteerRepository.GetRegisteredVolunteerByAssociateID(row["Employee ID"].ToString());
Event outreachEvent = eventRepository.FindEvent(row["Event ID"].ToString());
if (registeredVolunteer != null && outreachEvent != null)
{
string Creds = outreachEvent.EventId + "\n" + registeredVolunteer.EmployeeID + "\n" + ConstantValues.Participated;
string encrypt = AESCrypt.EncryptString(Creds);
string url = ConfigurationManager.AppSettings["URL"].ToString() + "Feedback?FeedbackValue=" + encrypt + "";
subject = "Feedback for {0} Dated {1}";
subject = string.Format(subject, outreachEvent.EventName, outreachEvent.EventDate.Value.ToShortDateString());
body = new System.IO.StreamReader(HttpContext.Current.Server.MapPath("~/MailTemplate/Participate_Mail.html")).ReadToEnd();
body = body.Replace("{UserName}", string.IsNullOrEmpty(registeredVolunteer.EmployeeName) ? "-" : registeredVolunteer.EmployeeName)
.Replace("{EventName}", string.IsNullOrEmpty(outreachEvent.EventName) ? "-" : outreachEvent.EventName)
.Replace("{EventDate}", string.IsNullOrEmpty(outreachEvent.EventDate.Value.ToShortDateString()) ? "-" : outreachEvent.EventDate.Value.ToShortDateString())
.Replace("{Url}", string.IsNullOrEmpty(url) ? "-" : url);
SendMail(subject, body, ConfigurationManager.AppSettings["CommonMail"].ToString());
}
}
}
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "OutreachMail",
ActionrName = "SendMailToParticipatedUser",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
}
}
// Method to check all the Tasks for their submit date.
public static void CheckTask()
{
SqlConnection constr = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
TaskRepository taskrepo = new TaskRepository();
ExceptionRepository exceptionrepo = new ExceptionRepository();
HomeController hc = new HomeController();
try
{
List <Task> tasks = taskrepo.Read();
for (int i = 0; i < tasks.Count; i++)
{
if (!tasks[i].isDeleted)
{
int done = DateTime.Compare(tasks[i].SubmitBy, DateTime.Now);
if (done < 0)
{
hc.Trainer_Notification(tasks[i].Id, false);
tasks[i].StatusId = 4;
tasks[i].isDeleted = true;
taskrepo.Update(tasks[i]);
}
}
}
}
catch (Exception ex)
{
MethodBase method = MethodBase.GetCurrentMethod();
Exceptions exception = new Exceptions
{
Number = ex.HResult.ToString(),
Message = ex.Message,
Method = method.Name,
Url = System.Web.HttpContext.Current.Request.Url.AbsoluteUri
};
int r = exceptionrepo.Exception_Create(exception);
if (r == 0)
{
exceptionrepo.Exception_InsertInLogFile(exception);
}
if (constr.State != ConnectionState.Open)
{
constr.Close();
constr.Open();
}
}
}
public void OnException(ExceptionContext filterContext)
{
MyOrganizationEntities db = new MyOrganizationEntities();
ExceptionRepository rep = new ExceptionRepository(db);
DataAccesLayer.Exception entity = new DataAccesLayer.Exception();
entity.LogTime = DateTime.Now;
entity.Message = filterContext.Exception.Message;
entity.StackTrace = filterContext.Exception.StackTrace;
entity.ControllerName = filterContext.RouteData.Values["controller"].ToString();
entity.ActionName = filterContext.RouteData.Values["action"].ToString();
rep.Insert(entity);
filterContext.ExceptionHandled = true;
filterContext.RequestContext.HttpContext.Response.Redirect("/Home/Error");
}
private void InsertParticipatedUserDetails(DataTable particaipateduserTable)
{
try
{
foreach (DataRow row in particaipateduserTable.Rows)
{
RegisteredVolunteer registeredVolunteer = new RegisteredVolunteer();
registeredVolunteer.EventId = row["Event ID"].ToString();
if (!row.IsNull("Employee ID"))
{
registeredVolunteer.EmployeeID = row["Employee ID"].ToString();
}
if (!row.IsNull("Employee Name"))
{
registeredVolunteer.EmployeeName = row["Employee Name"].ToString();
}
if (!row.IsNull("Business Unit"))
{
registeredVolunteer.BusinessUnit = row["Business Unit"].ToString();
}
if (!row.IsNull("Volunteer Hours"))
{
registeredVolunteer.VolunteerHours = Convert.ToInt32(row["Volunteer Hours"]);
}
if (!row.IsNull("Travel Hours"))
{
registeredVolunteer.TravelHours = Convert.ToInt32(row["Travel Hours"]);
}
RegisteredVolunteerRepository registeredVolunteerRepository = new RegisteredVolunteerRepository();
registeredVolunteerRepository.AddRegisteredVolunteer(registeredVolunteer);
}
outreachMail.SendMailToParticipatedUser(particaipateduserTable);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "ExcelToDB",
ActionrName = "InsertParticipatedUserDetails",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
}
}
public void ExceptionSavesToDatabase()
{
var config = new SystemDataLayerConfig
{
SurveillanceAuroraConnectionString =
"server=127.0.0.1; port=3306;uid=root;pwd='drunkrabbit101';database=dev_surveillance; Allow User Variables=True"
};
var repository = new ExceptionRepository(new ConnectionStringFactory(config), this._logger);
var dtos = new ExceptionDto
{
ExceptionMessage = "hello world",
InnerExceptionMessage = "Goodbye world",
StackTrace = "a/b/c",
SystemProcessId = "0",
SystemProcessOperationId = 1
};
repository.Save(dtos);
}
public string GetReportDataByEventId(string EventId)
{
try
{
ReportDTO reportDTO = new ReportDTO
{
RegisteredReportDTOs = new List <RegisteredReportDTO>(),
NotAttendedReportDTOs = new List <NotAttendedReportDTO>(),
UnregisteredReportDTOs = new List <UnregisteredReportDTO>()
};
List <FeedbackQuestion> questions = feedBackQuestionRepository.GetQuestions();
string reporttemplatePath = ConfigurationManager.AppSettings["ReportTemplate"].ToString();
string exportPath = ConfigurationManager.AppSettings["ExportPath"].ToString();
Event evt = eventRepository.FindEvent(EventId);
string url = "";
if (evt != null)
{
if (questions != null && questions.Count > 0)
{
List <RegisteredVolunteerFeedback> registeredVolunteerFeedbackList = RegisteredVolunteerFeedbackRepository.GetRegisteredFeeackListByEvent(EventId);
if (registeredVolunteerFeedbackList != null && registeredVolunteerFeedbackList.Count > 0)
{
int loopCount = registeredVolunteerFeedbackList.Count / questions.Count;
int ansCount = 0;
for (int i = 0; i < loopCount; i++)
{
RegisteredReportDTO registeredReportDTO = new RegisteredReportDTO
{
EventName = evt.EventName,
BeneficaryName = evt.BeneficaryName,
EventDate = Convert.ToDateTime(evt.EventDate).ToShortDateString(),
Location = evt.Location,
EmployeeID = registeredVolunteerFeedbackList[ansCount].EmployeeID,
FeedbackTextNumber1 = registeredVolunteerFeedbackList[ansCount].FeedbackText,
FeedbackTextNumber2 = registeredVolunteerFeedbackList[ansCount + 1].FeedbackText,
FeedbackTextNumber3 = registeredVolunteerFeedbackList[ansCount + 2].FeedbackText
};
reportDTO.RegisteredReportDTOs.Add(registeredReportDTO);
ansCount += questions.Count;
}
}
}
List <NotAttendedVolunteerFeedback> notAttendedVolunteerFeedbackList = notAttendedVolunteerFeedbackRepository.GetNotAttendedVolunteerFeedbackList(EventId);
if (notAttendedVolunteerFeedbackList != null && notAttendedVolunteerFeedbackList.Count > 0)
{
foreach (NotAttendedVolunteerFeedback notAttendedVolunteerFeedback in notAttendedVolunteerFeedbackList)
{
NotAttendedReportDTO notAttendedReportDTO = new NotAttendedReportDTO();
notAttendedReportDTO.EventName = evt.EventName;
notAttendedReportDTO.BeneficaryName = evt.BeneficaryName;
notAttendedReportDTO.EventDate = Convert.ToDateTime(evt.EventDate).ToShortDateString();
notAttendedReportDTO.Location = evt.Location;
notAttendedReportDTO.EmployeeID = notAttendedVolunteerFeedback.EmployeeID;
notAttendedReportDTO.FeedbackText = notAttendedVolunteerFeedback.FeedbackText;
reportDTO.NotAttendedReportDTOs.Add(notAttendedReportDTO);
}
}
List <UnRegisteredVolunteerFeedback> unRegisteredVolunteerFeedbackList = unRegisteredVolunteerFeedbackRepository.GetUnRegisteredVolunteerFeedbackList(EventId);
if (unRegisteredVolunteerFeedbackList != null && unRegisteredVolunteerFeedbackList.Count > 0)
{
foreach (UnRegisteredVolunteerFeedback unRegisteredVolunteerFeedback in unRegisteredVolunteerFeedbackList)
{
UnregisteredReportDTO unregisteredReportDTO = new UnregisteredReportDTO();
unregisteredReportDTO.EventName = evt.EventName;
unregisteredReportDTO.BeneficaryName = evt.BeneficaryName;
unregisteredReportDTO.EventDate = Convert.ToDateTime(evt.EventDate).ToShortDateString();
unregisteredReportDTO.Location = evt.Location;
unregisteredReportDTO.EmployeeID = unRegisteredVolunteerFeedback.EmployeeID;
unregisteredReportDTO.FeedbackText = unRegisteredVolunteerFeedback.FeedbackText;
reportDTO.UnregisteredReportDTOs.Add(unregisteredReportDTO);
}
}
if (reportDTO.RegisteredReportDTOs.Count > 0 || reportDTO.NotAttendedReportDTOs.Count > 0 || reportDTO.UnregisteredReportDTOs.Count > 0)
{
CreateExcel createExcel = new CreateExcel();
url = createExcel.WriteToExcel(reportDTO, reporttemplatePath, exportPath, evt.EventName);
}
}
return(url);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "ExcelReport",
ActionrName = "GetReportDataByEventId",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public OutReachException()
{
ExceptionRepository = new ExceptionRepository();
}
private void InsertEventDetails(DataTable eventTable)
{
try
{
RoleRepository roleRepository = new RoleRepository();
Role role = roleRepository.FindRoleId(ConstantValues.POC);
foreach (DataRow row in eventTable.Rows)
{
string eventID = row["Event ID"].ToString();
EventRepository eventRepository = new EventRepository();
Event Event = eventRepository.FindEvent(eventID);
if (Event == null)
{
Event = new Event();
Event.EventId = eventID;
if (!row.IsNull("Month"))
{
Event.Month = row["Month"].ToString();
}
if (!row.IsNull("Base Location"))
{
Event.Location = row["Base Location"].ToString();
}
if (!row.IsNull("Beneficiary Name"))
{
Event.BeneficaryName = row["Beneficiary Name"].ToString();
}
if (!row.IsNull("Venue Address"))
{
Event.Address = row["Venue Address"].ToString();
}
if (!row.IsNull("Council Name"))
{
Event.CouncilName = row["Council Name"].ToString();
}
if (!row.IsNull("Project"))
{
Event.Project = row["Project"].ToString();
}
if (!row.IsNull("Category"))
{
Event.Category = row["Category"].ToString();
}
if (!row.IsNull("Event Name"))
{
Event.EventName = row["Event Name"].ToString();
}
if (!row.IsNull("Event Description"))
{
Event.EventDescription = row["Event Description"].ToString();
}
if (!row.IsNull("Event Date (DD-MM-YY)"))
{
String[] date = row["Event Date (DD-MM-YY)"].ToString().Split(new[] { '-' });
int day = Convert.ToInt32(date[0]);
int month = Convert.ToInt32(date[1]);
int a = 20;
int year = int.Parse(a.ToString() + date[2].ToString());
DateTime eventDate = new DateTime(year, month, day);
Event.EventDate = eventDate;
}
if (!row.IsNull("Total no. of volunteers"))
{
Event.VolunteerCount = Convert.ToInt32(row["Total no. of volunteers"]);
}
if (!row.IsNull("Total Volunteer Hours"))
{
Event.VolunteerHours = Convert.ToInt32(row["Total Volunteer Hours"]);
}
if (!row.IsNull("Total Travel Hours"))
{
Event.TravelHours = Convert.ToInt32(row["Total Travel Hours"]);
}
if (!row.IsNull("Overall Volunteering Hours"))
{
Event.TotalVolunteeringHours = Convert.ToInt32(row["Overall Volunteering Hours"]);
}
if (!row.IsNull("Lives Impacted"))
{
Event.LivesImpacted = Convert.ToInt32(row["Lives Impacted"]);
}
if (!row.IsNull("Activity Type"))
{
Event.ActivityType = Convert.ToInt32(row["Activity Type"]);
}
if (!row.IsNull("Status"))
{
Event.Status = row["Status"].ToString();
}
eventRepository.AddEvent(Event);
// Add POC User
String[] pocID = null; String[] pocName = null;
if (role != null)
{
if (!row.IsNull("POC ID"))
{
pocID = row["POC ID"].ToString().Split(new[] { ';' });
}
if (!row.IsNull("POC Name"))
{
pocName = row["POC Name"].ToString().Split(new[] { ';' });
}
for (int i = 0; i < pocID.Length; i++)
{
User user = new User
{
AssociateID = pocID[i],
AssociateName = pocName[i],
RoleID = role.RoleID,
EventId = eventID
};
UserRepository userRepository = new UserRepository();
userRepository.AddUser(user);
}
}
}
}
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "ExcelToDB",
ActionrName = "InsertEventDetails",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
// Method to Send a E-mail from the queue of unsend E-mails in the database.
public static void SendEmailFromQueue()
{
SqlConnection constr = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
EmailQueueRepository emailrepo = new EmailQueueRepository();
MailLogRepository mailrepo = new MailLogRepository();
ExceptionRepository exceptionrepo = new ExceptionRepository();
try
{
Emails email = new Emails();
List <EmailQueue> emails = emailrepo.Read();
for (int i = 0; i < emails.Count; i++)
{
if (!emails[i].isDeleted && emails[i].Tries < 6)
{
emailrepo.Delete(emails[i].Id);
while (emails[i].Tries < 6)
{
if (email.Send_Mail(emails[i].ToAddress, emails[i].FromAddress, emails[i].Subject, emails[i].Body))
{
MailLog mail = new MailLog()
{
ToAddress = emails[i].ToAddress,
FromAddress = emails[i].FromAddress,
Subject = emails[i].Subject,
Body = emails[i].Body,
EmailStatus = true
};
mailrepo.Insert(mail);
break;
}
emails[i].Tries++;
emailrepo.Undelete(emails[i].Id);
}
emailrepo.Update(emails[i]);
}
}
}
catch (Exception ex)
{
MethodBase method = MethodBase.GetCurrentMethod();
Exceptions exception = new Exceptions
{
Number = ex.HResult.ToString(),
Message = ex.Message,
Method = method.Name,
Url = System.Web.HttpContext.Current.Request.Url.AbsoluteUri
};
int r = exceptionrepo.Exception_Create(exception);
if (r == 0)
{
exceptionrepo.Exception_InsertInLogFile(exception);
}
if (constr.State != ConnectionState.Open)
{
constr.Close();
constr.Open();
}
}
}
public DashboardDTO GetDashboardList(string associateID)
{
try
{
DashboardDTO dashboardDTO = new DashboardDTO();
List <UserDTO> userDTOList = GetUserListByAssociateID(associateID);
if (userDTOList != null && userDTOList.Count > 0)
{
RoleRepository roleRepository = new RoleRepository();
Role role = roleRepository.FindRoleName(userDTOList[0].RoleID);
if (role != null)
{
dashboardDTO.LocationList = new List <string>();
dashboardDTO.EventwithID = new Dictionary <string, string>();
if (role.RoleName.Equals(ConstantValues.Admin) || role.RoleName.Equals(ConstantValues.PMO))
{
EventRepository eventRepository = new EventRepository();
List <Event> outreachEvents = eventRepository.GetEventList();
if (outreachEvents != null && outreachEvents.Count > 0)
{
dashboardDTO.LocationList = outreachEvents.Select(env => env.Location).Distinct().ToList();
foreach (Event evt in outreachEvents)
{
if (!dashboardDTO.EventwithID.ContainsKey(evt.EventId))
{
dashboardDTO.EventwithID.Add(evt.EventId, evt.EventName);
}
}
}
}
else
{
foreach (UserDTO user in userDTOList)
{
EventRepository eventRepository = new EventRepository();
Event evt = eventRepository.FindEvent(user.EventId);
if (evt != null)
{
dashboardDTO.LocationList.Add(evt.Location);
dashboardDTO.EventwithID.Add(evt.EventId, evt.EventName);
}
}
}
}
}
return(dashboardDTO);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "UserAndRole",
ActionrName = "GetDashboardList()",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}
public string WriteToExcel(ReportDTO reportDTO, string templatePath, string exportpath, string EventName)
{
try
{
string UrlPath = "/ExcelReports/";
string reportName = "EventSummary_" + EventName + ".xlsx";
string sheetName = "";
UInt32 counter = 0;
File.Copy(templatePath, (exportpath + reportName), true);
using (SpreadsheetDocument document = SpreadsheetDocument.Open((exportpath + reportName), true))
{
wbPart = document.WorkbookPart;
counter = 2;
foreach (RegisteredReportDTO registeredReportDTO in reportDTO.RegisteredReportDTOs)
{
sheetName = ConstantValues.Participated;
UpdateValue(sheetName, "A", counter, registeredReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, registeredReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, registeredReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, registeredReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, registeredReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, registeredReportDTO.FeedbackTextNumber1, false);
UpdateValue(sheetName, "G", counter, registeredReportDTO.FeedbackTextNumber2, true);
UpdateValue(sheetName, "H", counter, registeredReportDTO.FeedbackTextNumber3, true);
counter++;
}
counter = 2;
foreach (NotAttendedReportDTO notAttendedReportDTO in reportDTO.NotAttendedReportDTOs)
{
sheetName = ConstantValues.NotParticipated;
UpdateValue(sheetName, "A", counter, notAttendedReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, notAttendedReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, notAttendedReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, notAttendedReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, notAttendedReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, notAttendedReportDTO.FeedbackText, true);
counter++;
}
counter = 2;
foreach (UnregisteredReportDTO unregisteredReportDTO in reportDTO.UnregisteredReportDTOs)
{
sheetName = ConstantValues.UnRegistered;
UpdateValue(sheetName, "A", counter, unregisteredReportDTO.EventName, true);
UpdateValue(sheetName, "B", counter, unregisteredReportDTO.BeneficaryName, true);
UpdateValue(sheetName, "C", counter, unregisteredReportDTO.EventDate, true);
UpdateValue(sheetName, "D", counter, unregisteredReportDTO.Location, true);
UpdateValue(sheetName, "E", counter, unregisteredReportDTO.EmployeeID, false);
UpdateValue(sheetName, "F", counter, unregisteredReportDTO.FeedbackText, true);
counter++;
}
document.Close();
}
UrlPath = (UrlPath + reportName);
return(UrlPath);
}
catch (Exception ex)
{
ExceptionLogger logger = new ExceptionLogger()
{
ControllerName = "CreateExcel",
ActionrName = "WriteToExcel",
ExceptionMessage = ex.Message,
ExceptionStackTrace = ex.StackTrace,
LogDateTime = DateTime.Now
};
ExceptionRepository exceptionRepository = new ExceptionRepository();
exceptionRepository.AddException(logger);
throw ex;
}
}