public virtual MvcMailMessage SBF(SBFEmailViewModel sbf,HttpPostedFileBase file,string subject) { string receviermail = WebConfigurationManager.AppSettings["ToEmail"]; ViewData.Model = sbf; if (file != null && file.ContentLength > 0) { var attachment = new Attachment(file.InputStream,sbf.Invoice.SupportingDocuments); return Populate(x => { x.ViewName = "sbf"; x.To.Add("*****@*****.**"); x.CC.Add(receviermail); x.Subject = subject; x.Attachments.Add(attachment); }); } else { return Populate(x => { x.ViewName = "sbf"; x.To.Add("*****@*****.**"); x.CC.Add(receviermail); x.Subject = subject; }); } //return PopulateBody(mailMessage, "SBF"); }
public string CreatExcel(SBFEmailViewModel sbfemail, string fileName) { string path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads/xlsx")); DirectoryInfo d = new DirectoryInfo(@path); if (d.Exists) { FileInfo newFile = new FileInfo(d.FullName + @"\" + fileName + ".xlsx"); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(d.FullName + @"\" + fileName + ".xlsx"); } using (ExcelPackage package = new ExcelPackage(newFile)) { // add a new worksheet to the empty workbook string titleHeader = string.Empty; if (fileName.IndexOf("SBF") > -1) { titleHeader = "SINGLE-BILLING REQUEST FORM"; } else if (fileName.IndexOf("GBF") > -1) { titleHeader = "GRAND-BILLING FORM"; } ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(titleHeader); string imagepath = Path.Combine(Server.MapPath("~/Images/"), "bcbs-excel.png"); Image logo = Image.FromFile(imagepath); int a = 1; //worksheet.Row(a * 5).Height = 39.00D; var picture = worksheet.Drawings.AddPicture(a.ToString(), logo); picture.From.Column = 6; picture.From.Row = a; picture.SetSize(130, 100); //Add the headers worksheet.Column(1).Width = 5; worksheet.Column(2).Width = 10; worksheet.Column(3).Width = 10; worksheet.Column(4).Width = 10; worksheet.Column(5).Width = 10; worksheet.Column(6).Width = 10; worksheet.Cells["B1"].Value = titleHeader; worksheet.Cells["B3"].Value = "Invoice Nuber"; worksheet.Cells["c3"].Value = sbfemail.Invoice.InvoiceNumber; worksheet.Cells["C3"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); //worksheet.Cells["F4"].Value = "(will be provided by Finance)"; worksheet.Cells["B5"].Value = "Invoice Date"; worksheet.Cells["C5"].Value = sbfemail.Invoice.InvoiceDate.ToString("MM/dd/yyyy"); worksheet.Cells["C5"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B7"].Value = "Prepared By"; worksheet.Cells["C7"].Value = sbfemail.Invoice.PrepareBy; worksheet.Cells["C7"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["D7"].Value = "EXT"; worksheet.Cells["E7"].Value = sbfemail.Invoice.PrepareByExt; worksheet.Cells["E7"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B8"].Value = "Authorized By"; worksheet.Cells["C8"].Value = sbfemail.Invoice.AuthorizedBy; worksheet.Cells["C8"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["D8"].Value = "EXT"; worksheet.Cells["E8"].Value = sbfemail.Invoice.AuthorizedByExt; worksheet.Cells["E8"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B9"].Value = "Division"; worksheet.Cells["C9"].Value = sbfemail.Invoice.Division; worksheet.Cells["C9"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B11"].Value = "Billning Information"; worksheet.Cells["B11"].Style.Font.Bold = true; worksheet.Cells["B12"].Value = sbfemail.Customer.Name; worksheet.Cells["B12"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["D12"].Value = sbfemail.Customer.ChargeCode; worksheet.Cells["B13"].Value = "Plan/Customer Name"; worksheet.Cells["D13"].Value = "Plan/Customer #"; worksheet.Cells["B14"].Value = sbfemail.Customer.CustomerAddress; ; worksheet.Cells["B14"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["D14"].Value = sbfemail.Customer.City; ; worksheet.Cells["D14"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B15"].Value = "Address"; worksheet.Cells["D15"].Value = "City"; worksheet.Cells["B16"].Value = sbfemail.Customer.State; worksheet.Cells["B16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["d16"].Value = sbfemail.Customer.PostalCode; worksheet.Cells["d16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["f16"].Value = ""; worksheet.Cells["f16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B17"].Value = "State"; worksheet.Cells["D17"].Value = "Zip Code"; worksheet.Cells["f17"].Value = "Customer PO#"; worksheet.Cells["B18"].Value = sbfemail.Customer.FirstName + " " + sbfemail.Customer.LastName; worksheet.Cells["B18"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["d18"].Value = sbfemail.Customer.Phone; worksheet.Cells["d18"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["B19"].Value = "Customer Contact Name"; worksheet.Cells["d19"].Value = "Telephone #"; worksheet.Cells["B20"].Value = "When multiple products/services are billed and/or different charge codes are to be used, please list each."; worksheet.Cells["B21"].Value = "Product/Service"; worksheet.Cells["C21"].Value = "GL Acct"; worksheet.Cells["D21"].Value = "RC"; worksheet.Cells["E21"].Value = "Project"; worksheet.Cells["F21"].Value = "Amount"; int last = 21; if (sbfemail.ActivityList != null) { int tableinit = 21; int tableend = tableinit + sbfemail.ActivityList.Count(); for (int i = 0; i < sbfemail.ActivityList.Count(); i++) { last++; if (sbfemail.ActivityList[i].FeesType == "Transaction") { worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + " (" + sbfemail.ActivityList[i].RateVolume + " " + sbfemail.ActivityList[i].Service + " @ $" + sbfemail.ActivityList[i].Rate + " )"; } else if (sbfemail.ActivityList[i].FeesType == "Hourly") { worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + "-" + sbfemail.ActivityList[i].Service + " (" + sbfemail.ActivityList[i].RateVolume + " hours @ $" + sbfemail.ActivityList[i].Rate + " )"; } else { worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + "-" + sbfemail.ActivityList[i].Service; } worksheet.Cells["C" + last].Value = sbfemail.ActivityList[i].GLAccount; worksheet.Cells["D" + last].Value = sbfemail.ActivityList[i].RC; worksheet.Cells["E" + last].Value = sbfemail.ActivityList[i].ProjectCode; worksheet.Cells["F" + last].Value = sbfemail.ActivityList[i].Amount.ToString("C"); } using (var range = worksheet.Cells[tableinit, 2, tableend, 6]) { range.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); } } last++; worksheet.Cells["E" + last].Value = "Total"; worksheet.Cells["F" + last].Value = sbfemail.Invoice.TotalAmount.ToString("C"); last++; worksheet.Cells["B" + last].Value = "Deffered?"; worksheet.Cells["C" + last].Value = sbfemail.Invoice.IsDeffered ? "Yes" : "No"; worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); last++; worksheet.Cells["B" + last].Value = "Deffered Acct"; worksheet.Cells["C" + last].Value = sbfemail.Invoice.DefferedAccount; worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["e" + last].Value = "From date"; worksheet.Cells["f" + last].Value = sbfemail.Invoice.FromDate.ToString("MM/dd/yyyy"); worksheet.Cells["f" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); worksheet.Cells["h" + last].Value = "End date"; worksheet.Cells["i" + last].Value = sbfemail.Invoice.ToDate.ToString("MM/dd/yyyy"); worksheet.Cells["i" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); last++; worksheet.Cells["B" + last].Value = "Special Instruction"; worksheet.Cells["C" + last].Value = sbfemail.Invoice.SpecialInstuction; worksheet.Cells["C" + last].Style.Font.Color.SetColor(Color.Red); worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); last++; worksheet.Cells["B" + last].Value = "Supporting document"; worksheet.Cells["C" + last].Value = ""; //worksheet.Cells["C29"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); last++; worksheet.Cells["b" + last].Value = "Please send the completed form and all supporting documents to Finance via email at [email protected] or via "; worksheet.Cells["b" + last].Style.Font.Size = 10; //worksheet.Cells["b30"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); ////Add a formula for the value-column //worksheet.Cells["E2:E4"].Formula = "C2*D2"; ////Ok now format the table range ; worksheet.Cells["B11:F11"].Merge = true; worksheet.Cells["B20:f20"].Merge = true; worksheet.Cells["B" + last + ":h" + last].Merge = true; worksheet.Cells["B" + last + ":h" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); last--; worksheet.Cells["B" + last + ":f" + last].Merge = true; worksheet.Cells["B" + last + ":f" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black); package.Save(); } return newFile.Name; } else { return null; } }
public ActionResult Invoice(InvoiceViewModel invoiceModel, HttpPostedFileBase file, FormCollection fc) { BCBSClient client = new BCBSClient(); //ModelState.Where(m => m.Key == "CustomerCode").FirstOrDefault().Value.Errors.Clear(); //if (ModelState.IsValid) //{ long Id = 0; string fname = string.Empty; if (file != null && file.ContentLength > 0) { // extract only the fielname var fileName = Path.GetFileName(file.FileName); string ext = Path.GetExtension(file.FileName); Guid g = Guid.NewGuid(); fname = g.ToString() + ext; // store the file inside ~/UploadDocuments/uploads folder var path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads"), fname); file.SaveAs(path); invoiceModel.SupportingDocuments = fname; } //string csv = string.Empty; Id = client.InsertCustomerInvoice(invoiceModel.InvoiceNumber, invoiceModel.CustomerId, invoiceModel.InvoiceDate, invoiceModel.PrepareBy, invoiceModel.PrepareByExt, invoiceModel.AuthorizedBy, invoiceModel.AuthorizedByExt, invoiceModel.Division, invoiceModel.IsDeffered, invoiceModel.DefferedAccount, invoiceModel.FromDate, invoiceModel.ToDate, invoiceModel.SpecialInstuction, invoiceModel.SupportingDocuments, invoiceModel.TotalAmount); if (Id > 0) { //csv += "Invoice Number," + invoiceModel.InvoiceNumber + "\n"; //csv += "Invoice Date," + invoiceModel.InvoiceDate.ToString("MM/dd/yyyy") + "\n"; //csv += "Prepared by," + invoiceModel.PrepareBy + ", Authorized By," + invoiceModel.AuthorizedBy + "\n"; //csv += "Devision," + invoiceModel.Division + "\n\n"; //csv += "Customer Name," + invoiceModel.Customer.Name + "\n"; //csv += "Address\n"; //csv += invoiceModel.Customer.CustomerAddress + " " + invoiceModel.Customer.City + " " + invoiceModel.Customer.State + " " + invoiceModel.Customer.PostalCode + "\n"; //csv += "Contact Name," + invoiceModel.Customer.FirstName + " " + invoiceModel.Customer.LastName + "\n"; //csv += "Phone," + invoiceModel.Customer.Phone + "\n\n"; SBFEmailViewModel sbfemail = new SBFEmailViewModel(); //mailer.SBF(); string fcActivities = fc["Activities"].ToString(); List<string> activities = new List<string>(); if (!string.IsNullOrEmpty(fcActivities)) { bool isbilled = client.SetActivityBilled(fcActivities); if (isbilled) { var activityIds = fcActivities.Split(','); foreach (var x in activityIds) { if (!string.IsNullOrEmpty(x)) { long activityId = Convert.ToInt64(x); long sbfActivityId = client.InsertSBFActivity(Id, activityId); if (sbfActivityId > 0) { activities.Add(activityId.ToString()); continue; } else { break; } } } } } if (activities != null && activities.Count > 0) { string ids = string.Join(",", activities.ToArray()); string result = client.GetActivitiesByActivityIds(ids); List<ActivityListModel> activity = JsonConvert.DeserializeObject<List<ActivityListModel>>(result); if (sbfemail.ActivityList == null) { //csv += "Contract -Activity\n\n"; //csv += "Project Name,Service Type,From Date,End Date,Charges,Value,Amount\n"; sbfemail.ActivityList = new List<ActivityListModel>(); } if (activity != null) { foreach (ActivityListModel x in activity) { string charge = string.Empty; if (x.Charges == true) { charge = "Expense"; } else { charge = "Revenue"; } string estimate = string.Empty; if (x.Estimate == true) { estimate = "Real"; } else { estimate = "Estimate"; } //csv += x.ProjectName + "," + x.Service + "," + x.FromDate.ToString("MM/dd/yyyy") + "," + x.EndDate.ToString("MM/dd/yyyy") + "," + charge + "," + estimate + "," + x.Amount + "\n"; } //csv += ",,,,,Total," + invoiceModel.TotalAmount + "\n\n"; //if (invoiceModel.IsDeffered == true) //{ //csv += "Deffered Account," + invoiceModel.DefferedAccount + "\n"; //} //csv += "From Date," + invoiceModel.FromDate.ToString("MM/dd/yyyy") + ",,End Date," + invoiceModel.ToDate.ToString("MM/dd/yyyy") + "\n"; sbfemail.ActivityList = activity; } } IUserMailer mailer = new UserMailer(); sbfemail.Invoice = invoiceModel; string CustomerData = client.GetcustomerById(invoiceModel.CustomerId); if (!string.IsNullOrEmpty(CustomerData)) { sbfemail.Customer = JsonConvert.DeserializeObject<CustomerModel>(CustomerData); } string ContractData = client.GetcontractById(invoiceModel.ContractId); ContractModel contract = new ContractModel(); if (!string.IsNullOrEmpty(ContractData)) { contract = JsonConvert.DeserializeObject<ContractModel>(ContractData); sbfemail.Contract = contract; } string fileName = "SBF_" + sbfemail.Invoice.InvoiceNumber + "_" + DateTime.Now.ToString("MM_dd_yyyy_HH_mm_ss"); string excelFileName = CreatExcel(sbfemail, fileName); mailer.SBF(sbfemail, file, "SBF Invoice - BCBS Acc Sys").Send(); if (!string.IsNullOrEmpty(excelFileName)) { string path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads/xlsx"), excelFileName); return File(path, "text/csv", excelFileName); } else { TempData["Message"] = "Customer Invoice generated successfully..!"; //string fileName = "SBF_" + invoiceModel.InvoiceNumber + "_" + DateTime.Now.ToString("MM-dd-yyyy"); // return RedirectToAction("Index", "Customer"); } } else { string fullPath = Request.MapPath("~/UploadDocuments/uploads/" + fname); if (System.IO.File.Exists(fullPath)) { System.IO.File.Delete(fullPath); } TempData["Error"] = "Customer Invoice generation failed..!"; return View(invoiceModel); } ModelState.Clear(); //} //else //{ // string customerlist = client.GetcustomerList(); // if (!string.IsNullOrEmpty(customerlist)) // { // ViewBag.Customers = JsonConvert.DeserializeObject<List<CustomerModel>>(customerlist).Select(x => new { x.Id, x.Name }); // } // else // { // ViewBag.Customers = ""; // } // return View(invoiceModel); //} }
public ActionResult SBFEmailView(SBFEmailViewModel sbf) { BCBSClient client = new BCBSClient(); sbf.ActivityList = new List<ActivityListModel>(); string result = client.GetActivitiesByContractIds("23,24"); List<ActivityListModel> activity = JsonConvert.DeserializeObject<List<ActivityListModel>>(result); sbf.ActivityList = activity; string CustomerData = client.GetcustomerById(4); if (!string.IsNullOrEmpty(CustomerData)) { sbf.Customer = JsonConvert.DeserializeObject<CustomerModel>(CustomerData); } InvoiceViewModel ivm = new InvoiceViewModel(); ivm.AuthorizedBy = "Paatrick Egan"; ivm.AuthorizedByExt = "5497"; ivm.ContractId = 12; ivm.CustomerId = 4; ivm.FromDate = DateTime.Now; ivm.ToDate = DateTime.Now.AddDays(2); ivm.TotalAmount = 350.00; sbf.Invoice = ivm; string ContractData = client.GetcontractById(24); ContractModel contract = new ContractModel(); if (!string.IsNullOrEmpty(ContractData)) { contract = JsonConvert.DeserializeObject<ContractModel>(ContractData); sbf.Contract = contract; } return View(sbf); }