[Authorize] /* For Testing Prefilled Forms */
public ActionResult Index()
{
ViewBag.Title = "Embassy Consular Appointment Form (Admin)";
ConsularApptVM ca = CATestData.GetNewAppt();
return(View(ca));
}
public ActionResult Index([Bind(Include = "RequesterType,TotalNumberOfApplications,AppointmentDate,AppointmentType,Name,Gender,DateOfBirth,PlaceOfBirth,Nationality,NRIC_No,PassportNumber,PassportIssuedDate,ContactAddr1,ContactAddr2,ContactPhone,ContactEmail,Note")] ConsularApptVM consularApptVM)
{
if (!ModelState.IsValid)
{
this.LogModelStateError();
}
ViewBag.Title = _formTile + " (Admin)";
consularApptVM.Note = "[ADMIN]" + consularApptVM.Note;
DpWebAppDb.AddConsularAppt(consularApptVM, DpWebAppConfig.QueueNumberInitial);
DateTime? confirmedApptDate = null;
int? confirmedQueNumber = 0;
ConsularApptVM consularApptVM2 = DpWebAppDb.ConfirmConsularAppt(consularApptVM.ID, consularApptVM.ActivationCode, ref confirmedApptDate, ref confirmedQueNumber);
consularApptVM.QueueNumber = confirmedQueNumber.GetValueOrDefault();
ViewBag.PartialHtml = "_MsgApptConfirmedByAdmin";
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
ViewBag.AppointmentType = appointmentType.Description;
return(View(consularApptVM));
}
public static string GetConfirmationLetter(this Controller cntlr, ConsularApptVM consularApptVM)
{
ViewRenderer vr = new ViewRenderer(cntlr.ControllerContext);
SetViewBagForConfirmationLetter(cntlr, consularApptVM);
return(vr.RenderPartialView(Template_ConfirmationLetter, consularApptVM));
}
public ActionResult Index([Bind(Include = "AppointmentDate,AppointmentType,Name,Gender,DateOfBirth,PlaceOfBirth,Nationality,NRIC_No,PassportNumber,PassportIssuedDate,ConsulateLocation,StayType,StayPermitNumber,EmployerName,Occupation,ContactAddr1,ContactAddr2,ContactPhone,ContactEmail,HomeAddr1,HomeAddr2,HomePhone,Note")] ConsularApptVM consularApptVM)
{
if (!ModelState.IsValid)
{
this.LogModelStateError();
}
ViewBag.Title = "Embassy Consular Appointment Form (Admin)";
consularApptVM.Note = "[ADMIN]" + consularApptVM.Note;
EmbassyAppDb.AddConsularAppt(consularApptVM, EmWebAppConfig.QueueNumberInitial);
DateTime? confirmedApptDate = null;
int? confirmedQueNumber = 0;
ConsularApptVM consularApptVM2 = EmbassyAppDb.ConfirmConsularAppt(consularApptVM.Id, consularApptVM.ActivationCode, ref confirmedApptDate, ref confirmedQueNumber);
consularApptVM.QueueNumber = confirmedQueNumber.GetValueOrDefault();
ViewBag.PartialHtml = "_MsgApptConfirmedByAdmin";
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
ViewBag.AppointmentType = appointmentType.Description;
return(View(consularApptVM));
}
// GET: ConsularAppt/Details/5
public ActionResult Details(int id)
{
ConsularApptVM model = EmbassyAppDb.GetConsularApptById(id);
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(model.AppointmentType);
ViewBag.AppointmentType = appointmentType.Description;
return(View(model));
}
[Authorize] /* For Testing Prefilled Forms */
public ActionResult Index()
{
ViewBag.Title = _formTile + " (Admin)";
ConsularApptVM model = CATestData.GetNewAppt(0);
ViewBag.PartialHtml = this.GetPartialHtmlViewName(model.RequesterType);
return(View(model));
}
public static MemoryStream GetAppointmentLetterStream(string pdfTemplate, ConsularApptVM consularAppt)
{
PdfReader pdfReader = new PdfReader(pdfTemplate);
MemoryStream memStream = new MemoryStream();
using (PdfStamper pdfStamper = new PdfStamper(pdfReader, memStream, '\0', true))
{
FillFormFields(pdfStamper, consularAppt);
pdfStamper.Writer.CloseStream = false;
}
memStream.Position = 0;
return(memStream);
}
public static string GetQrCodeString(ConsularApptVM consularApptVM)
{
return(String.Format(@"{0}$${1}$${2}$${3}$${4}$${5}$$JOB",
consularApptVM.Name,
string.Format("{0:dd-MM-yyyy}", consularApptVM.DateOfBirth),
consularApptVM.Gender == "F" ? "Female" : "Male",
consularApptVM.PlaceOfBirth,
consularApptVM.PassportNumber,
string.Format("{0:dd-MM-yyyy}", consularApptVM.PassportIssuedDate)
));
}
// GET: Admin/PrintApptLtr/5
public ActionResult PrintApptLtr(int id)
{
ConsularApptVM model = EmbassyAppDb.GetConsularApptById(id);
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(model.AppointmentType);
string pdfTemplateFile = ConfirmationLetterPdf.GetPdfTemplateFileName(appointmentType, model.StayType);
MemoryStream pdfStream = ConfirmationLetterPdf.GetAppointmentLetterStream(pdfTemplateFile, model);
//string confirmationLetter = this.GetConfirmationLetter(id);
//MemoryStream pdfStream = new MemoryStream();
//Pdf.WriteHtmlToPdfStream(confirmationLetter, pdfStream);
return(new FileStreamResult(pdfStream, "application/pdf"));
}
// GET: Admin/PrintApptLtr/5
public ActionResult PrintApptLtr(int id)
{
ConsularApptVM model = DpWebAppDb.GetConsularApptById(id);
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(model.AppointmentType);
string pdfTemplateFile = DpWebAppConfig.CnslrLtrPdfTmplPath + appointmentType.ConsularLtrPdfTmplFilename;
MemoryStream pdfStream = ConfirmationLetterPdf.GetAppointmentLetterStream(pdfTemplateFile, model);
//string confirmationLetter = this.GetConfirmationLetter(id);
//MemoryStream pdfStream = new MemoryStream();
//Pdf.WriteHtmlToPdfStream(confirmationLetter, pdfStream);
return(new FileStreamResult(pdfStream, "application/pdf"));
}
public static Email GetConfirmationRequestEmail(this Controller cntlr, ConsularApptVM consularApptVM)
{
string emailBody = cntlr.GetConfirmationRequestEmailBody(consularApptVM);
Email email = new Email
{
From = DpWebAppConfig.EmailAddress,
DisplayName = DpWebAppConfig.EmailUser,
To = consularApptVM.ContactEmail,
Subject = DpWebAppConfig.EmailSubj_VerificationRequest,
Body = emailBody,
IsHtml = true
};
return(email);
}
private static void SetViewBagForConfirmationLetter(this Controller cntlr, ConsularApptVM consularApptVM)
{
if (cntlr.ViewBag.Logo == null)
{
string basePath = AppDomain.CurrentDomain.BaseDirectory;
cntlr.ViewBag.Logo = Graphics.GetImgSrcForPdf(basePath + DpWebAppConfig.Emblem_Logo);
}
// Get Service Type look up
if (cntlr.ViewBag.AppointmentType == null)
{
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
cntlr.ViewBag.AppointmentType = appointmentType.Description;
}
// Get QR code
if (cntlr.ViewBag.QR == null)
{
cntlr.ViewBag.QR = Graphics.GenerateRelayQrCode(ConfirmationLetterPdf.GetQrCodeString(consularApptVM));
}
}
// GET: ConsularAppt/Create
public ActionResult Create(int Id)
{
ViewBag.Title = _formTile;
if (Request.IsAuthenticated)
{
ViewBag.Title = _formTile + " (Admin)";
}
var model = new ConsularApptVM();
model.RequesterType = Id;
if (DpWebAppConfig.PrefilledFormTest)
{
model = CATestData.GetNewAppt(Id);
}
ViewBag.PartialHtml = this.GetPartialHtmlViewName(model.RequesterType);
return(View(model));
}
private static string GetConfirmationRequestEmailBody(this Controller cntlr, ConsularApptVM consularApptVM)
{
String routeName = String.Empty;
var controllerName = cntlr.RouteData.Values["controller"].ToString();
var actionName = "Confirmed";
cntlr.ViewBag.Applicant = consularApptVM.Name;
cntlr.ViewBag.EmailConfirmationLink = cntlr.Url.RouteUrl(routeName,
new // route values, add more if needed
{
controller = controllerName,
action = actionName,
id = consularApptVM.ID,
code = consularApptVM.ActivationCode
}, cntlr.Request.Url.Scheme);
// Render Html Email
ViewRenderer vr = new ViewRenderer(cntlr.ControllerContext);
return(vr.RenderPartialView(Template_ConfirmationRequestEmail, null));
}
public static MemoryStream GetAppointmentLetterStream2(string pdfTemplate, ConsularApptVM consularAppt)
{
PdfReader pdfReader = new PdfReader(pdfTemplate);
using (var tempFileColl = new TempFileCollection())
{
string tempFile = tempFileColl.AddExtension("pdf");
using (PdfStamper pdfStamper = new PdfStamper(pdfReader, new FileStream(tempFile, FileMode.OpenOrCreate)))
{
FillFormFields(pdfStamper, consularAppt);
}
using (FileStream fileStream = File.OpenRead(tempFile))
{
MemoryStream memStream = new MemoryStream();
memStream.SetLength(fileStream.Length);
fileStream.Read(memStream.GetBuffer(), 0, (int)fileStream.Length);
memStream.Position = 0;
return(memStream);
}
}
}
public static ConsularApptVM ConfirmConsularAppt(int appointmentId, string activationCode,
ref DateTime?confirmedApptDate, ref int?confirmedQueNumber)
{
ConsularApptVM consularAppt = null;
confirmedQueNumber = 0;
using (SqlConnection conn = GetDBConnection())
{
conn.Open();
SqlCommand cmd = new SqlCommand("ConfirmConsularAppointment", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ApplicationID", appointmentId));
cmd.Parameters.Add(new SqlParameter("@ActivationCode", activationCode));
cmd.Parameters.Add(new SqlParameter("@Note", "Confirmation Note"));
SqlParameter sp = new SqlParameter("@ConfirmedApptDate", SqlDbType.DateTime);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@ConfirmedQueueNumber", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
// cmd.ExecuteNonQuery();
SqlDataAdapter sqlAdptr = new SqlDataAdapter();
sqlAdptr.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlAdptr.Fill(ds);
if (ds.Tables[0].Rows.Count == 1)
{
confirmedApptDate = Convert.ToDateTime(cmd.Parameters["@ConfirmedApptDate"].Value);
confirmedQueNumber = Convert.ToInt32(cmd.Parameters["@ConfirmedQueueNumber"].Value);
consularAppt = GetConsularApptVM(ds.Tables[0].Rows[0]);
}
}
return(consularAppt);
}
private static void FillFormFields(PdfStamper pdfStamper, ConsularApptVM consularAppt)
{
AcroFields pdfFormFields = pdfStamper.AcroFields;
pdfFormFields.SetField("Name", consularAppt.Name);
pdfFormFields.SetField("PassportNumber", consularAppt.PassportNumber);
pdfFormFields.SetField("AppointmentDate", String.Format("{0:dd MMM, yyyy [dddd]}", consularAppt.AppointmentDate));
pdfFormFields.SetField("QueueNumber", consularAppt.QueueNumber.ToString());
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularAppt.AppointmentType);
pdfFormFields.SetField("ServiceType", appointmentType.Description);
pdfFormFields.SetField("Name2", consularAppt.Name);
pdfFormFields.SetField("PassportNumber2", consularAppt.PassportNumber);
pdfFormFields.SetField("PhoneNumber", consularAppt.ContactPhone);
pdfFormFields.SetField("Email", consularAppt.ContactEmail);
iTextSharp.text.Image txtImage = null;
using (var memStream = Graphics.GenerateQrCodeStream(GetQrCodeString(consularAppt)))
{
memStream.Position = 0;
txtImage = iTextSharp.text.Image.GetInstance(memStream);
}
var fp = pdfFormFields.GetFieldPositions("QRCode");
float right = fp[0].position.Right;
float left = fp[0].position.Left;
float top = fp[0].position.Top;
float bottom = fp[0].position.Bottom;
txtImage.ScaleToFit(115, 115);
txtImage.SetAbsolutePosition(left, bottom);
int pageNum = 1;
PdfContentByte contentByte = pdfStamper.GetOverContent(pageNum);
contentByte.AddImage(txtImage);
pdfStamper.FormFlattening = false;
}
public static string GetConfirmedEmailBody(this Controller cntlr, ConsularApptVM consularApptVM)
{
string actionName = cntlr.ControllerContext.RouteData.Values["action"].ToString();
string controllerName = cntlr.ControllerContext.RouteData.Values["controller"].ToString();
// logo path ( iis express doesn't have project root in Url
var baseUrl = cntlr.Request.Url.GetLeftPart(UriPartial.Authority);
string logoPng = String.Format("{0}{1}{2}{3}"
, baseUrl
, cntlr.Request.Url.Segments[0]
, cntlr.Request.Url.Segments[1].IndexOf(controllerName) == 0 ? String.Empty : cntlr.Request.Url.Segments[1]
, DpWebAppConfig.Emblem_Logo);
cntlr.ViewBag.Logo = Graphics.GetImgTag(logoPng, "logo");
// Service Type
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
cntlr.ViewBag.AppointmentType = appointmentType.Description;
ViewRenderer vr = new ViewRenderer(cntlr.ControllerContext);
return(vr.RenderPartialView(Template_ConfirmedEmail, consularApptVM));
}
public async Task <ActionResult> Create([Bind(Include = "AppointmentDate,AppointmentType,Name,Gender,DateOfBirth,PlaceOfBirth,Nationality,NRIC_No,PassportNumber,PassportIssuedDate,ConsulateLocation,StayType,StayPermitNumber,EmployerName,Occupation,ContactAddr1,ContactAddr2,ContactPhone,ContactEmail,HomeAddr1,HomeAddr2,HomePhone,Note")] ConsularApptVM consularApptVM)
{
if (!Request.IsAuthenticated)
{
ViewBag.Title = "Embassy Consular Appointment Form";
EmbassyAppDb.AddConsularAppt(consularApptVM, EmWebAppConfig.QueueNumberInitial);
Email email = this.GetConfirmationRequestEmail(consularApptVM);
using (var smtp = new SmtpClient())
{
smtp.Prep();
await smtp.SendMailAsync(email.Message);
}
ViewBag.PartialHtml = "_MsgApptReceived";
}
else
{
ViewBag.Title = "Embassy Consular Appointment Form (Admin)";
consularApptVM.Note = "[ADMIN]" + consularApptVM.Note;
EmbassyAppDb.AddConsularAppt(consularApptVM, EmWebAppConfig.QueueNumberInitial);
DateTime? confirmedApptDate = null;
int? confirmedQueNumber = 0;
ConsularApptVM consularApptVM2 = EmbassyAppDb.ConfirmConsularAppt(consularApptVM.Id, consularApptVM.ActivationCode, ref confirmedApptDate, ref confirmedQueNumber);
consularApptVM.QueueNumber = confirmedQueNumber.GetValueOrDefault();
ViewBag.PartialHtml = "_MsgApptConfirmedByAdmin";
}
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
ViewBag.AppointmentType = appointmentType.Description;
return(View(consularApptVM));
}
public static bool AddConsularAppt(ConsularApptVM ca, int initalQueueNumber)
{
ca.QueueNumber = initalQueueNumber;
SqlConnection conn = GetDBConnection();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("AddConsularAppointment", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@RequesterType", ca.RequesterType));
cmd.Parameters.Add(new SqlParameter("@TotalNumberOfApplications", ca.TotalNumberOfApplications));
cmd.Parameters.Add(new SqlParameter("@AppointmentDate", ca.AppointmentDate));
cmd.Parameters.Add(new SqlParameter("@AppointmentType", ca.AppointmentType));
cmd.Parameters.Add(new SqlParameter("@QueueNumber", ca.QueueNumber));
cmd.Parameters.Add(new SqlParameter("@Name", ca.Name));
cmd.Parameters.Add(new SqlParameter("@Gender", ca.Gender));
cmd.Parameters.Add(new SqlParameter("@DateOfBirth", ca.DateOfBirth));
cmd.Parameters.Add(new SqlParameter("@PlaceOfBirth", ca.PlaceOfBirth));
cmd.Parameters.Add(new SqlParameter("@Nationality", ca.Nationality));
cmd.Parameters.Add(new SqlParameter("@NRIC_No", ca.NRIC_No));
cmd.Parameters.Add(new SqlParameter("@PassportNumber", ca.PassportNumber));
cmd.Parameters.Add(new SqlParameter("@PassportIssuedDate", ca.PassportIssuedDate));
cmd.Parameters.Add(new SqlParameter("@ConsulateLocation", ca.ConsulateLocation));
cmd.Parameters.Add(new SqlParameter("@StayType", ca.StayType));
cmd.Parameters.Add(new SqlParameter("@StayPermitNumber", ca.StayPermitNumber));
cmd.Parameters.Add(new SqlParameter("@EmployerName", ca.EmployerName));
cmd.Parameters.Add(new SqlParameter("@Occupation", ca.Occupation));
cmd.Parameters.Add(new SqlParameter("@ContactAddr1", ca.ContactAddr1));
cmd.Parameters.Add(new SqlParameter("@ContactAddr2", ca.ContactAddr2));
cmd.Parameters.Add(new SqlParameter("@ContactPhone", ca.ContactPhone));
cmd.Parameters.Add(new SqlParameter("@ContactEmail", ca.ContactEmail));
cmd.Parameters.Add(new SqlParameter("@HomeAddr1", ca.HomeAddr1));
cmd.Parameters.Add(new SqlParameter("@HomeAddr2", ca.HomeAddr2));
cmd.Parameters.Add(new SqlParameter("@HomePhone", ca.HomePhone));
cmd.Parameters.Add(new SqlParameter("@Note", ca.Note));
// Output Parameters
SqlParameter sp = new SqlParameter("@ApplicationId", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@ActivationCode", SqlDbType.NVarChar, 256);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
cmd.ExecuteNonQuery();
ca.ID = Convert.ToInt32(cmd.Parameters["@ApplicationId"].Value);
ca.ActivationCode = Convert.ToString(cmd.Parameters["@ActivationCode"].Value);
}
catch (Exception ex)
{
// log
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(true);
}
public async Task <ActionResult> ConfirmPosted(string confirmedId, string confirmedCode)
{
if (!Request.IsAjaxRequest())
{
return(Content("Request Error."));
}
int id = int.Parse(confirmedId);
string code = confirmedCode;
DateTime? confirmedApptDate = null;
int? confirmedQueNumber = 0;
ConsularApptVM consularApptVM = EmbassyAppDb.ConfirmConsularAppt(id, code, ref confirmedApptDate, ref confirmedQueNumber);
if (consularApptVM == null)
{
return(RedirectToAction("Error"));
}
string confirmedEmailBody = this.GetConfirmedEmailBody(consularApptVM);
string confirmationLetter = this.GetConfirmationLetter(consularApptVM);
// To do: Maybe convert the confirmationLetter to PDF, or get fillable PDF letter
// Get Required Form Attachment List
AppointmentType appointmentType = ConsularAppointmentTypes.GetAppointmentType(consularApptVM.AppointmentType);
List <BLL.Attachment> formAttachments = appointmentType.Attachments?.List;
// Start preparing for Email
Email embassyMail = new Email
{
From = EmWebAppConfig.EmailAddress,
DisplayName = EmWebAppConfig.EmailUser,
To = consularApptVM.ContactEmail,
Subject = EmWebAppConfig.EmailSubj_Confirmed,
Body = confirmedEmailBody,
IsHtml = true
};
// using (MemoryStream ms = new MemoryStream())
string pdfTemplateFile = ConfirmationLetterPdf.GetPdfTemplateFileName(appointmentType, consularApptVM.StayType);
using (MemoryStream ms = ConfirmationLetterPdf.GetAppointmentLetterStream(pdfTemplateFile, consularApptVM))
using (MailMessage mailMsg = embassyMail.Message)
using (var smtp = new SmtpClient())
{
System.Net.Mail.Attachment coverLetterPdf = Email.GetPdfAttachmentFromPdfStream(ms, "AppointmentLetter.pdf");
// System.Net.Mail.Attachment coverLetterPdf = Email.GetPdfAttachmentFromHtmlString(confirmationLetter, ms, "AppointmentLetter.pdf");
mailMsg.Attachments.Add(coverLetterPdf);
if (formAttachments != null)
{
foreach (var attachment in formAttachments)
{
if (System.IO.File.Exists(attachment.FullPath))
{
var emailAttachment = new System.Net.Mail.Attachment(attachment.FullPath, new ContentType(MediaTypeNames.Application.Pdf));
mailMsg.Attachments.Add(emailAttachment);
}
}
}
smtp.Prep();
await smtp.SendMailAsync(mailMsg);
// return View(consularApptVM);
return(PartialView("_MsgApptConfirmed", consularApptVM));
}
}
