public PartialViewResult UploadProgress(HttpPostedFileBase itemExcelFile)
{
var data = (new ExcelReader()).ReadExcel(itemExcelFile);
var model = new List <TraCafProgress>();
var mainData = new TraCafDto();
var sirsNumber = "";
if (data != null)
{
foreach (var dataRow in data.DataRows)
{
if (dataRow[0] == "")
{
continue;
}
var item = new TraCafProgress();
sirsNumber = dataRow[0];
mainData = _cafBLL.GetCafBySirs(sirsNumber);
if (mainData == null)
{
item.Message = "SRIS number not found on FMS.";
}
else
{
item.TraCafId = mainData.TraCafId;
item.CreatedBy = CurrentUser.USER_ID;
item.CreatedDate = DateTime.Now;
double d = double.Parse(dataRow[2]);
DateTime conv = DateTime.FromOADate(d);
item.Estimation = conv;
item.ProgressDate = DateTime.Now;
item.StatusId = (int)EnumHelper.GetValueFromDescription <Enums.DocumentStatus>(dataRow[1]);
item.Remark = dataRow[3];
if (item.StatusId == 0)
{
item.Message = "Status Not recognized.";
}
}
model.Add(item);
}
}
TraCafItemViewModel modelData = new TraCafItemViewModel();
modelData.Detail = new TraCafItemDetails();
modelData.Detail.SirsNumber = sirsNumber;
modelData.Detail.ProgressDetails = model;
return(PartialView("_UploadDetailList", modelData));
}
private void SendEmailWorkflow(TraCafDto crfData, Enums.ActionType action)
{
//var csfData = Mapper.Map<TraCsfDto>(_CsfService.GetCsfById(input.DocumentId));
var mailProcess = ProsesMailNotificationBody(crfData, action);
//distinct double To email
List <string> ListTo = mailProcess.To.Distinct().ToList();
if (mailProcess.IsCCExist)
{
//Send email with CC
_messageService.SendEmailToListWithCC(ListTo, mailProcess.CC, mailProcess.Subject, mailProcess.Body, true);
}
else
{
_messageService.SendEmailToList(ListTo, mailProcess.Subject, mailProcess.Body, true);
}
}
private FMSMailNotification ProsesMailNotificationBody(TraCafDto crfData, Enums.ActionType action)
{
var bodyMail = new StringBuilder();
var rc = new FMSMailNotification();
//var vehTypeBenefit = _settingService.GetSetting().Where(x => x.SETTING_GROUP == "VEHICLE_TYPE" && x.SETTING_NAME == "BENEFIT").FirstOrDefault().MST_SETTING_ID;
//var isBenefit = crfData.VEHICLE_TYPE.ToUpper().Contains("BENEFIT");
string creatorDataEmail = "";
var webRootUrl = ConfigurationManager.AppSettings["WebRootUrl"];
var typeEnv = ConfigurationManager.AppSettings["Environment"];
var serverIntranet = ConfigurationManager.AppSettings["ServerIntranet"];
var employeeData = _employeeService.GetEmployeeById(crfData.EmployeeId);
var hrList = string.Empty;
var fleetList = string.Empty;
var hrEmailList = new List <string>();
var fleetEmailList = new List <string>();
var hrRole = _settingService.GetSetting().Where(x => x.SETTING_GROUP == EnumHelper.GetDescription(Enums.SettingGroup.UserRole) &&
x.SETTING_VALUE.Contains("HR")).FirstOrDefault().SETTING_VALUE;
var fleetRole = _settingService.GetSetting().Where(x => x.SETTING_GROUP == EnumHelper.GetDescription(Enums.SettingGroup.UserRole) &&
x.SETTING_VALUE.Contains("FLEET")).FirstOrDefault().SETTING_VALUE;
var hrQuery = "SELECT 'PMI\\' + sAMAccountName AS sAMAccountName FROM OPENQUERY(ADSI, 'SELECT employeeID, sAMAccountName, displayName, name, givenName, whenCreated, whenChanged, SN, manager, distinguishedName, info FROM ''LDAP://DC=PMINTL,DC=NET'' WHERE memberOf = ''CN = " + hrRole + ", OU = ID, OU = Security, OU = IMDL Managed Groups, OU = Global, OU = Users & Workstations, DC = PMINTL, DC = NET''') ";
var fleetQuery = "SELECT 'PMI\\' + sAMAccountName AS sAMAccountName FROM OPENQUERY(ADSI, 'SELECT employeeID, sAMAccountName, displayName, name, givenName, whenCreated, whenChanged, SN, manager, distinguishedName, info FROM ''LDAP://DC=PMINTL,DC=NET'' WHERE memberOf = ''CN = " + fleetRole + ", OU = ID, OU = Security, OU = IMDL Managed Groups, OU = Global, OU = Users & Workstations, DC = PMINTL, DC = NET''') ";
if (typeEnv == "VTI")
{
hrQuery = "SELECT 'PMI\\' + LOGIN AS LOGIN FROM LOGIN_FOR_VTI WHERE AD_GROUP = '" + hrRole + "'";
fleetQuery = "SELECT 'PMI\\' + LOGIN AS LOGIN FROM LOGIN_FOR_VTI WHERE AD_GROUP = '" + fleetRole + "'";
}
EntityConnectionStringBuilder e = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["FMSEntities"].ConnectionString);
string connectionString = e.ProviderConnectionString;
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand query = new SqlCommand(hrQuery, con);
SqlDataReader reader = query.ExecuteReader();
while (reader.Read())
{
var hrLogin = "******" + reader[0].ToString() + "',";
hrList += hrLogin;
}
hrList = hrList.TrimEnd(',');
query = new SqlCommand(fleetQuery, con);
reader = query.ExecuteReader();
while (reader.Read())
{
var fleetLogin = "******" + reader[0].ToString() + "',";
fleetList += fleetLogin;
}
fleetList = fleetList.TrimEnd(',');
var hrQueryEmail = "SELECT EMAIL FROM " + serverIntranet + ".[dbo].[tbl_ADSI_User] WHERE FULL_NAME IN (" + hrList + ")";
var fleetQueryEmail = "SELECT EMAIL FROM " + serverIntranet + ".[dbo].[tbl_ADSI_User] WHERE FULL_NAME IN (" + fleetList + ")";
var creatorQuery =
"SELECT EMAIL from " + serverIntranet + ".[dbo].[tbl_ADSI_User] where FULL_NAME like 'PMI\\" +
crfData.CreatedBy + "'";
if (typeEnv == "VTI")
{
hrQueryEmail = "SELECT EMAIL FROM EMAIL_FOR_VTI WHERE FULL_NAME IN (" + hrList + ")";
fleetQueryEmail = "SELECT EMAIL FROM EMAIL_FOR_VTI WHERE FULL_NAME IN (" + fleetList + ")";
creatorQuery = "SELECT EMAIL FROM EMAIL_FOR_VTI WHERE FULL_NAME like 'PMI\\" + crfData.CreatedBy + "'";
}
query = new SqlCommand(hrQueryEmail, con);
reader = query.ExecuteReader();
while (reader.Read())
{
hrEmailList.Add(reader[0].ToString());
}
query = new SqlCommand(fleetQueryEmail, con);
reader = query.ExecuteReader();
while (reader.Read())
{
fleetEmailList.Add(reader[0].ToString());
}
query = new SqlCommand(creatorQuery, con);
reader = query.ExecuteReader();
while (reader.Read())
{
creatorDataEmail = reader["EMAIL"].ToString();
}
reader.Close();
con.Close();
rc.Subject = "CAF - Car Accident Report Progress";
bodyMail.Append("Dear " + crfData.EmployeeId + ",<br /><br />");
bodyMail.AppendLine();
bodyMail.Append("Your filed Car accident report has updated.<br />");
bodyMail.AppendLine();
bodyMail.AppendLine();
bodyMail.Append("SIRS Number : " + crfData.SirsNumber + "<br />");
bodyMail.AppendLine();
bodyMail.Append("Current status of your report : " + crfData.DocumentStatusString + "<br />");
bodyMail.AppendLine();
bodyMail.Append("You can monitor the progress by clicking <a href='" + webRootUrl + "/TraCaf/Details/" + crfData.TraCafId + "?isPersonalDashboard=True'>Here</a><br />");
bodyMail.AppendLine();
//bodyMail.Append("<a href='" + webRootUrl + "/TraCaf/Details/" + crfData.TraCafId + "?isPersonalDashboard=True'>" +
// "CAF Number : "+ crfData.DocumentNumber + "</a> requested by " + crfData.EmployeeName +
// "<br /><br />");
bodyMail.AppendLine();
bodyMail.Append("Thanks<br /><br />");
bodyMail.AppendLine();
bodyMail.Append("Regards,<br />");
bodyMail.AppendLine();
bodyMail.Append("Fleet Team");
bodyMail.AppendLine();
rc.To.Add(employeeData.EMAIL_ADDRESS);
rc.CC.Add(creatorDataEmail);
foreach (var item in fleetEmailList)
{
rc.CC.Add(item);
}
rc.Body = bodyMail.ToString();
if (rc.CC.Count > 0)
{
rc.IsCCExist = true;
}
return(rc);
}
public void ValidateCaf(TraCafDto dataTovalidate, out string message)
{
List <string> validation = new List <string>();
message = "";
if (string.IsNullOrEmpty(dataTovalidate.SirsNumber))
{
validation.Add("Sirs number cannot be empty.");
}
if (string.IsNullOrEmpty(dataTovalidate.PoliceNumber))
{
validation.Add("Police number cannot be empty.");
}
var index = 0;
if (validation.Count > 0)
{
foreach (var vld in validation)
{
if (index > 0)
{
message += ", " + vld;
}
else
{
message += vld;
}
}
return;
}
var dbData = _CafService.GetCafByNumber(dataTovalidate.SirsNumber);
if (dbData != null)
{
validation.Add("Sirs Number already registered in FMS.");
}
var isAnyCaf = _CafService.IsCafExist(dataTovalidate.PoliceNumber, dataTovalidate.IncidentDate);
if (isAnyCaf)
{
validation.Add(string.Format("CAF For police number : {0} and Incident Date : {1} already registered in FMS.", dataTovalidate.PoliceNumber, dataTovalidate.IncidentDate.ToString("dd-MMM-yyyy")));
}
if (validation.Count > 0)
{
foreach (var vld in validation)
{
if (index > 0)
{
message += ", " + vld;
}
else
{
message += vld;
}
}
}
}
